Route 19

After finally running into a few occasions where version 3 of SQLite would be preferable, I’ve decided to look into getting CodeIgniter up and running with it again using the PDO library. Specifically I’ll be using CodeIgniter 1.6.2, SQLite3 and the PDO files from the CodeIgniter Wiki. This is all running on Ubuntu 8.04 with Lighttpd 1.4.19 and PHP 5.2.4 for now.

First I’ll download the required PDO drivers and place them here:

/system/database/drivers/pdo/pdo_driver.php /system/database/drivers/pdo/pdo_result.php

Next I’ll create an SQLite3 database file and place it within reach of the CodeIgniter install. Make sure your database file and it’s directory have the proper permissions for writing. Then I’ll setup CodeIgniter to access this database file through PDO:

/config/database.php
$active_group = "sqlite"; $active_record = TRUE; $db['sqlite']['hostname'] = "localhost"; $db['sqlite']['username'] = ""; $db['sqlite']['password'] = ""; $db['sqlite']['database'] = "sqlite:path/to/database.sqlite3"; $db['sqlite']['dbdriver'] = "pdo"; $db['sqlite']['dbprefix'] = ""; $db['sqlite']['pconnect'] = FALSE; $db['sqlite']['db_debug'] = TRUE; $db['sqlite']['cache_on'] = FALSE; $db['sqlite']['cachedir'] = ""; $db['sqlite']['char_set'] = "utf8"; $db['sqlite']['dbcollat'] = "utf8_general_ci";

You can also convert your SQLite2 database to SQLite3 on the command line if you have both utilities installed:

sqlite database.sqlite2 .dump | sqlite3 database.sqlite3

One caveat to note from my initial tests is that the CI functions for database field data (e.g. $this->db->field_data()) don’t seem to work, and some are actually commented out in the pdo_result.php file. I’m going to do a little more testing before I switch this live site over to SQLite3, I’ll let you know how it works out.

UPDATE: The lack of a few CI database features — namely the field and table metadata functions — has me reconsidering the complete switch. I either need to find alternative ways to access the metadata I need, or stick with SQLite2, which is missing some, but not all.

UPDATE II: I’ve managed to cobble together a field_data method for the database class that works for the SQLite3 PDO drivers, so I’ll be moving this site to SQLite3 after all. I’ve updated the CI Wiki page as well as my zipped copy of the SQLite3 PDO drivers locally.