Home > Code Snippets > Convert MySQL Database to SQLite3 Database

Convert MySQL Database to SQLite3 Database

December 3rd, 2009 Leave a comment Go to comments

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 (2169)

Categories: Code Snippets Tags: ,
  1. Thorisoka
    January 4th, 2010 at 18:42 | #1

    The file seems to be corrupted, could you do something about it?

  2. Ruud Altenburg
    January 29th, 2010 at 19:51 | #2

    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.

  3. January 31st, 2010 at 19:37 | #3

    Thanks for the suggestion, I have updated the script with the suggested correction. Glad that some one is using my scripts

  4. Sven
    February 17th, 2010 at 18:59 | #4

    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…


  5. February 18th, 2010 at 22:54 | #5

    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.

  6. May 19th, 2010 at 20:49 | #6

    The file seems to be corrupted, could you do something about it?

  1. No trackbacks yet.

thirty one + = thirty six