Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Simple MySQL: Converting ANSI SQL to SQLite3
+0 Vote Up -0 Vote Down

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

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.