Phly, boy, phly
the weblog and site of Matthew Weier O'Phinney

The fixes hinges on three things:

• PDO_SQLITE will create the database file if it doesn't exist.
• Sqlite has a facility for dumping all SQL for generating and populating existing tables in a database file.
• PHP_Shell allows you to interact with PHP at the command line.

So, here goes. First, create a SQL dump of your existing file. The sqlite command accepts two arguments: the database file to use, and either SQL or sqlite metacommands. In this case, we'll pass the command '.dump' (note the '.' prefix), and redirect output to a file:

Now, we need to delete the existing database file, or back it up somewhere. Once done, we'll fire up PHP_Shell, using the php-shell.sh command (or php-shell.bat for Windows users). PHP_Shell is a handy utility that provides an interactive PHP shell, complete with history and completion. We'll use it to create our sqlite database file:

% phpshell 
PHP-Shell - Version 0.3.0, with readline() support
(c) 2006, Jan Kneschke 

>> use '?' to open the inline help 

>> $db = new PDO('sqlite:/path/to/mydata.db');
PDO::__set_state(array(
))
>> quit

With the database file created, we'll now load up that schema and data we dumped earlier. The nice part here is that the sqlite utility tends to be more tolerant of version differences, so we can load the data into the new database file using it, and PHP will be none the wiser:

All done!