Quickly recreate SQLite database structure

Here you’ll find an example (and some tips) of an extremely easy PHP code for recreating SQLite database. It can also be used to execute any SQLite query (or serie of queries) stored in a external file.

Since SQLiteDatabase::queryExec() can execute more then one statement, if all statements all followed by semicolon, then a function to recreate entire database, table etc. or to run serie of queries stored in an external file is as simple as:

[code language=”sql”]
$db = new SQLiteDatabase(‘data.db’);
$SQLs = file_get_contents(dirname(__FILE__).’default.sql’);
$result = $db->queryExec($SQLs);

Where default.sql is a file containing a set of SQL statements to rebuild database, table or to do whatever you want to do. It should be stored in the same folder as .php file acquiring it or you must modify path to point to existing, readable file.

For some reasons, even such simple piece of code may fail from time to time, with error saying that PHP is unable to open database file. I don’t know, why this happens. I’ve been getting this error sometimes even on as simple SQLs like running one DELETE and some INSERTs. The very same code, copied out of default.sql and executed in some SQLite editor (i.e. in SQLite Studio) were executed without any problems always.

My only idea is, that PHP’s SQLite library is poor and that is why, developers has such unusual problems with it.

Leave a Reply