For a cd based time limited working copy, of one of our products, I decided to port our mysql application to sqlite3. Since we had about 70 tables, and all with optimized indexes, and about 24 user defined function 3 triggers and 12 procedures, normally anyone would have thought of simply getting along with the mysql system itself. But I wanted to face the challenge. Thanks to Rob Cameron for the shell script published on his blog RidingTheClutch, Convert a MySQL database to a SQLite3 database, which sparked my thoughts.
Basically the shell script would have discarded all indexes from all the tables while converting to sqlite, but I really needed the indexes too to be converted. Also my scripting language choice is always php first. The out come is a php script which can parse and convert a dump taken from mysql using options –compact –compatible=ansi –default-character-set=binary –extended-insert=false to a lsq3 file, which can be piped to sqlite3 to create the database.
SQLite3 supports triggers, but will need those to be created manually, and triggers in mysql can call procedures, or user defined functions, which is not possible in sqlite. So the triggers migration should be taken with most care. For keeping code compatibility, we were using a wrapper class instance for mysql, which was modified to accommodate functions and procedures in the php layer itself. The wrapper will be blogged later after we successfully port the same application to MSSQL.
Please visit our the corporate site of Saturn, for commercial services in php and mysql.
Download the converter script Download