I hope to save some other poor saps from suffering the same ill experience I just went through trying to use SQLite3 with CodeIgniter 1.7.2. I’m going to contribute to the wiki page on this topic as well, but it’s a mess and my contribution may become lost in the noise.
So I’ve made the move from laboriously creating a custom codebase for HYDRAtweet (backstory) to implementing it on top of CodeIgniter. I’d already started with SQLite3 via PDO and intended to just carry on using it by defining the models based on the existing schemata.
This didn’t go so well because, as reported in the wiki, SQLite3 in PHP requires the use of PDO, and since CodeIgniter is backwards-compatible with PHP 4.x, in which PDO is not bundled (though it can be added) the provided SQLite support in CodeIgniter makes use of the sqlite_* functions in PHP, which again cannot be used with SQLite3.
The wiki described altering/replacing CodeIgniter’s SQLite driver to use PDO instead of the sqlite_* function calls, but it was unclear if that would break support for SQLite 2, and I found that a bit shabby all-around, so I made a copy of the SQLite driver directory (system/database/drivers/sqlite), naming the new copy sqlite3 and adjusting the names of all the subclasses defined within to include the “3”.
Then I was further confounded, receiving the following message:
The resolution turned out to be quite simple. Never having used SQLite (of any vintage) without PDO, I did not realize the connection string would be different. Therefore my database.php file had to be modified from:
$db['default']['database'] = APPPATH.'db/hydratweet.sqlite3';
to
$db['default']['database'] = 'sqlite:'.APPPATH.'db/hydratweet.sqlite3';
…which was just the inclusion of the “sqlite:” prefix.
And now all is well.
Incidentally, the sqlite_driver.php file contains a method stub called _list_columns() and returns false, with a comment stating that it’s not supported. I immediately said to myself, “that can’t be right!” …but a few minutes later after I’d unsuccessfully tried using “pragma table_info(tablename)” in nested queries and done a little Google-walking, I realized it was true and had to move on. It’s always sad to find your pet technologies have awkward drawbacks.
Regardless, the driver bit works just fine, and if it’ll save anyone some time, here’s the zip. download