Simple MySQL: Converting ANSI SQL to SQLite3

I was digging through some old project code and found this script. Sometimes one finds oneself in an odd situation and needs to convert regular SQL, say from a MySQL database dump, into SQLite3 format. There’s not too much else to say, but here is a script that helps with the process. It can likely be improved but this handles the items that came up during conversion on initial runs.

#!/bin/sh
####
# NAME: convert-mysql-to-sqlite3.sh
# AUTHOR: Matt Reid
# DATE: 2011-03-22
# LICENSE: BSD
####
if [ "x$1" == "x" ]; then
   echo "Usage: $0 "
   exit 
fi 
cat $1 |
grep -v ' KEY "' |   
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |          
sed 's/ unsigned / /g' | 
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' | 
sed 's/ tinyint([0-9]*) / integer /g' |  
sed 's/ int([0-9]*) / integer /g' |      
sed 's/ character set [^ ]* / /g' |      
sed 's/ enum([^)]*) / varchar(255) /g' | 
sed 's/ on update [^,]*//g' |            
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; 
  }                       
  ' > $1.sql              
cat $1.sql | sqlite3 $1.db > $1.err   
ERRORS=`cat $1.err | wc -l`           
if [ $ERRORS == 0 ]; then             
  echo "Conversion completed without error. Output file: $1.db"
  rm $1.sql         
  rm $1.err         
    rm tmp          
else                
   echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi
Tagged , ,

3 thoughts on “Simple MySQL: Converting ANSI SQL to SQLite3

  1. huarong says:

    So many errors….

    SQL error: near “AUTO_INCREMENT”: syntax error
    SQL error: near “LOCK”: syntax error
    SQL error: near “UNLOCK”: syntax error
    SQL error: near “AUTO_INCREMENT”: syntax error
    SQL error: near “LOCK”: syntax error
    SQL error: no such table: phpbb_posts
    SQL error: no such table: phpbb_posts
    SQL error: no such table: phpbb_posts
    SQL error: no such table: phpbb_posts
    SQL error: no such table: phpbb_posts
    SQL error: no such table: phpbb_posts
    SQL error: no such table: phpbb_posts
    SQL error: no such table: phpbb_posts
    SQL error: no such table: phpbb_posts
    SQL error: near “UNLOCK”: syntax error
    SQL error: near “ENGINE”: syntax error
    SQL error: near “LOCK”: syntax error
    SQL error: near “UNLOCK”: syntax error

  2. admin says:

    What step of the process is this from? Is this during import to SQLite or… not much info to go off of to help solve it. Let me know, I’ll look into it.

  3. Shantanu Oak says:

    sqldump supports –tab option that will export data in csv format. This file can be easily imported in sqlite or any other database without dealing with compatibility issues.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>