Zend Framework: Getting started using SQLite

Question

Sorry if this is overly simplistic.

I've decided that I want to use an SQLite database instead of a MySQL database. I'm trying to wrap my head around how simple SQLite is and would like a simple, one answer tutorial on how to use SQLite with the Zend Framework, where to put my SQLite database in my directory structure, how to create the database, etc.

Answer 1

@tuinstoel is correct, attaching to an SQLite database implicitly creates it if it does not exist.

SQLite also supports a command-line client that is more or less like MySQL's command shell, allowing you to issue ad hoc commands or run SQL scripts. See documentation here: http://www.sqlite.org/sqlite.html

Of course you need to change the Zend_Db adapter in your ZF application. ZF supports only an adapter to the PDO SQLite extension. SQLite doesn't support user/password credentials. Also since SQLite is an embedded database instead of client/server, the "host" parameter is meaningless.

$db = Zend_Db::factory("pdo_sqlite", array("dbname"=>"/path/to/mydatabase.db"));

One more caveat: when you get query results in associative-array format, some versions of SQLite insist on using "tablename.columnname" as the keys in the array, whereas other brands of database return keys as simply "columnname". There's an outstanding bug in ZF about this, to try to compensate and make SQLite behave consistently with the other adapters, but the bug is unresolved.

Answer 2

If you make a connection to a not existing database, a database is created on the fly. (You can turn this behavour off)