I ran into an issue recently in testing a site where
PDO_SQLITE was claiming
that it could not read my PDO database files. The only recent change I'd had was
that I'd installed a new version of PHP, and hence a new version of
Searching the web (we're not supposed to say googling or googled anymore,
remember ;-)), I found that the issue was that the version of sqlite compiled
into my PHP install was not compatible with the version I used to create the
databases in the first place. Never mind that they're only a micro version or
So, I was left with a conundrum: I needed to create files compatible with my
PDO_SQLITE install, but my CLI sqlite tool was incompatible. And if I used
PDO_SQLITE to create the db file, I'd lose my data, right?
Wrong. And here's what you can do should you find yourself in the same situation sometime.
The fixes hinges on three things:
PDO_SQLITEwill 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:
$ sqlite mydata.db .dump > /tmp/mydata.sql
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
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:
$ sqlite mydata.db < /tmp/mydata.sql