n o t e 1 9 . c o m

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.

This entry was posted on May 9, 2010 at 7:52 pm and is filed under iPhone. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.