Convert MySQL Database to SQLite3 Database
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 Convert MySQL Database to SQLite3 Database (129)
The file seems to be corrupted, could you do something about it?
Thanks for this script, it seems to be one of the very few to actually take indexes into account! I had a few problems with two indexes though, which I managed to track down. There a typo in this line:
case (stripos($line, ‘enum’) !== false):
which should read
case (stripos($line, ‘enum(’) !== false):
If the bracket is missing, any key or field name containing ‘enum’ (in my case pagenumber) throws a warning as the regex does not match. In my case this resulted in an invalid sql file.
Thanks for the suggestion, I have updated the script with the suggested correction. Glad that some one is using my scripts
Hi jijutm,
The download doesn’t seem to be working. If I try to unzip, I get a message that the archive is corrupt.
Could you please check the file?
Would love to use your script…
Thanks!
Sven
Yea, there was some sort of file type error, and the whole zipped content was being misinterpreted by most of the browsers. I just redone one fresh upload and tested on the same.