MYSQL to SQLITE for iPhone


Today I had to migrate a MySQL table to SQLite so that I could use it in an iPhone app. The process is simple, you can export the MySQL table and then import it in SQLite. I found the following shell script in the MySQL site (I think), but the resulting file could not be imported in SQLite, since I had used the MySQL COMMENT to document each of the columns. SQLite does not understand column comments. So here is the updated script that makes the MYSQL dump work with SQLite.

#!/bin/sh 

/opt/local/lib/mysql5/bin/mysqldump --compact --compatible=ansi --default-character-set=binary -uUSERNAME -pPASSWORD DATABASE TABLE  | 
sed -e "s/COMMENT '.*',/,/" |
grep -v ' KEY "' | 
grep -v ' UNIQUE KEY "' | 
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' | 
perl -pe ' 
if (/^(INSERT.+?)\(/) { 
$a=$1; 
s/\\'\''/'\'\''/g; 
s/\\n/\n/g; 
s/\),\(/\);\n$a\(/g; 
} 
' > db.sql 

You must substitute the USERNAME, PASSWORD, DATABASE and TABLE with the values for your project.

Next, you need to load this dump file into SQLite, issue the following command:

cat db.sql | sqlite3 db.db

This will create a SQLite database in a file called db.db. Now you can add the file db.db to your iPhone project, plus add the SQLite3 iphone framework and finally you’ll be ready to use the database from Objective-C.

3 Responses to “MYSQL to SQLITE for iPhone”

  1. Chris J. Says:

    Hello. Thanks for the script. It worked perfectly to create the dump. However, when I ran the second command:

    cat db.sql | sqlite3 db.db

    I received the following error:

    SQL error: no such table: events

    Am I doing something wrong? Thanks for your help!

    • Shahram Javey Says:

      Hello. Not sure. I’m afraid you will need to debug it. Try just importing the schema (without the data) and then check to see if the tables are created or not. Good luck.

  2. Chris J. Says:

    Ok, was able to get it imported. Two things sqlite didn’t like from the original dump file….timestamp field and field comments. Once I deleted those it copied over fine.
    YAY! I have been working on getting mysql data into sqlite for two weeks now….whew!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: