PDO SQLite3

Category:Core | Category:Core::Community | Category:Core::Database

The following approach worked using a previous CodeIgniter version. Current changes to CodeIgniter database classes preclude using the following class files as a CodeIgniter PDO database interface.

The approach has been 'broken', as of CodeIgniter 1.7 (or so). To see the old code, one can still open the files in an editor.

Between:

• the SQLite versions (v.2 in 2000 and v.3 in 2004) and
• three database libraries, ADOdb, PEAR DB and PDO and
• procedural vs. object-oriented (e.g. PDO) functions syntax and
• dismal SQLite.org docs, albeit a tad better at [url]http://us3.php.net/manual/en/book.sqlite3.php[/url]

... who can blame Rick Ellis & Co. for throwing up their hands and saying, 'Ya know what, I haven't got time to figure out all this SQLite confusion ... and why should CodeIgniter add to the confusion with yet another database abstraction layer?'

To get PDO and SQLite3 working, I suppose one could copy the SQLite2 driver, rename it for SQLite3, place it in a /PDO/ directory and edit the function names to get them to make SQLite3_whateverFunction() calls. Someone did that, here: [url]http://blog.trevorbramble.com/past/2009/9/20/codeigniter_sqlite3/[/url]

So, for now, there is no real CodeIgniter PDO SQLite3 support!

[h2] CodeIgniter PDO SQLite3 quick setup [/h2]

Download the CodeIgniter Wiki file: File:pdo_sqlite_driver.zip. Warning! This driver can not work with current CI versions 1.7.x.

Create a /pdo/ subdirectory, as in: [code] CodeIgniter/system/database/drivers/pdo/ [/code]

Unzip pdo_sqlite_driver.zip into the new directory [b]/pdo/[/b].

Un-comment the PHP5 PDO database interface drivers in PHP.ini [code]extension=php_pdo.dll[/code] and [code]extension=php_pdo_sqlite.dll[/code]

Re-start the web server e.g. Apache.

That's it for the setup. The rest of this article explains the derivation of the above files and includes some getting started ideas.

[h3] Introduction [/h3]

PHP5 core now includes the SQLite (file-based) database and PDO (PHP Data Objects) database interface layer.

SQLite is an open-source, embedded, relational database. SQLite is written in C and is less than 1/4 Meg in size (the whole SQLite RDBMS 'engine' fits nicely in memory). SQLite is bullet fast (yes, faster than MySQL or PostgreSQL - until queries grow complex enough to benefit from a query optimizer). A temporary SQLite database can even be used in memory and left for garbage collection with the calling script - very PHP-like.

Embedded means that SQLite is intertwined in the host application process (PHP in our case) that calls it; embedded also means that there's no networking (configuration) required. SQLite was designed for embedded use (by D Richard Hipp at General Dynamics for Navy guided missile destroyers); therefore, no user names, passwords or groups are involved, either.

SQLite has been used for fairly large applications (the Apple OS X, Sun Solaris, Mozilla, Linux Palm OS, KDE Amarok audio player, YUM package manager, smart phones, D-Link etc.); there are many forums, blogs and wikis, based on SQLite. So, don't dismiss SQLite - just because it's not mySQL or PostgreSQL. Like any tool, there's a proper time and place for it e.g. for occasions when "squirrel hunting with an elephant gun" is not called for, such as a configuration repository, simple small volume uses etc. Remember, mySQL or PostgreSQL are not ALWAYS called for (or fastest), either.

SQLite3 (the PHP default), which came along in 2004, [strong]requires the PDO database interface[/strong], rather than PHP calls, as was the case with SQLite2. SQLite3 is 25% smaller than SQLite2.

Without an in-depth investigation it appears that the objective of PDO is analogous to Microsoft's ODBC database abstraction layer from the early 1990's i.e. PDO, as a database interface layer, offers generic database programatic access, which depends upon a "driver" from each database vendor - to implement the specific features of that vendor's database offering.

The SQLite site documentation is sketchy. [strong]"The Definitive Guide to SQLite" by Michael Owens[/strong] is a 5-star Apress (hardcover) book ... MORE than worthwhile for using SQLite, expertly.

[h3] When to use (and not use) SQLite [/h3]

Communicating, directly, with a file or memory locks the entire file or memory location. Since SQLite is file or memory based, it is [strong] not [/strong] suitable for cross-network, client-server architecture; so, SQLite would not be suitable for high-volume, high-contention (e.g. transaction) sites. However, calling SQLite from server-side code (e.g. from PHP on the [strong] same [/strong] box as the Apache web server i.e. NOT across a network) works Great ... and is [strong] F A S T[/strong].

SQLite excels at SELECTs (file reading, especially small, configuration files). If your INSERTs or UPDATEs are infrequent, compared to SELECTs, SQLite may be several times [strong] faster than a full-blown RDBMS[/strong], like MySQL or PostgreSQL.

Because an SQLite in-memory database is bundled with its creation process (the PHP script that created it), the SQLite in-memory database will be dropped from memory after the PHP script is parsed.

SQLite has No user access control - other than file system permissions.

[h3]CodeIgniter PDO SQLite Implementation[/h3]

SQLite3 [strong]requires[/strong] the PHP5 PDO database interface drivers; [code]extension=php_pdo.dll[/code] and [code]extension=php_pdo_sqlite.dll[/code] must be un-commmented in PHP.ini.

[h4] A 'gotcha' to look out for - Windows Only [/h4]

This code is great, but a thing to look out for for any 'newbs' to PHP/CodeIgniter/Sqlite3/PDO(like myself)...

You may have a situation where you get an unexplainable error message of [pre]SQLSTATE[HY000] [14] unable to open database file[/pre]

If so, check in config/database.php that your [code] $db['default']['database'] [/code] setting value is enclosed in single quotes. Double quotes (the default) will cause escaping and should you (like me) have a sequence of characters like [code] c:\xa [/code] ..., etc. the \xa will be escaped into a new-line character, causing a weird database path to be provided and the database will never be found.

Thanks to all for this great article.