Appla · June 6, 2019 02:40 · Jun 22, 2011 · Jun 20, 2011 · Jun 20, 2011 · Jun 20, 2011
diff --git a/mysql2sqlite → mysql2sqlite.sh b/mysql2sqlite → mysql2sqlite.sh
diff --git a/mysql2sqlite.sh → mysql2sqlite b/mysql2sqlite.sh → mysql2sqlite
diff --git a/mysql2sqlite → mysql2sqlite.sh b/mysql2sqlite → mysql2sqlite.sh
diff --git a/mysql2sqlite b/mysql2sqlite
@@ -1,27 +1,57 @@
 #!/bin/sh
 
+# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
+# CREATE block and create them in separate commands _after_ all the INSERTs.
+
 # Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
 # The mysqldump file is traversed only once.
 
-# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
-# CREATE block and create them in separate commands _after_ all the INSERTs.
+# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
+# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite
 
-# Usage: $ ./mysql2sqlite | sqlite3 database.sqlite
+# Thanks to and @artemyk and @gkuenning for their nice tweaks.
 
-mysqldump  --compatible=ansi --skip-extended-insert --compact -u **** -p******  datbase_name | \
+mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \
 
+awk '
 
-awk -F ",$" '
+BEGIN {
+	FS=",$"
+	print "PRAGMA synchronous = OFF;"
+	print "PRAGMA journal_mode = MEMORY;"
+	print "BEGIN TRANSACTION;"
+}
 
-BEGIN{ FS=",$" }
+# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
+/^\/\*.*CREATE.*TRIGGER/ {
+	gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
+	print
+	inTrigger = 1
+	next
+}
+
+# The end of CREATE TRIGGER has a stray comment terminator
+/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
+
+# The rest of triggers just get passed through
+inTrigger != 0 { print; next }
 
-# Skip comments
+# Skip other comments
 /^\/\*/ { next }
 
 # Print all `INSERT` lines. The single quotes are protected by another single quote.
-/INSERT/ { gsub( /\\\047/, "\047\047" ); print; next }
+/INSERT/ {
+	gsub( /\\\047/, "\047\047" )
+	gsub(/\\n/, "\n")
+	gsub(/\\r/, "\r")
+	gsub(/\\"/, "\"")
+	gsub(/\\\\/, "\\")
+	gsub(/\\\032/, "\032")
+	print
+	next
+}
 
-# Print the ´CREATE´ line as is and capture the table name.
+# Print the `CREATE` line as is and capture the table name.
 /^CREATE/ {
 	print
 	if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
@@ -30,6 +60,9 @@ BEGIN{ FS=",$" }
 # Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
 /^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }
 
+# Get rid of field lengths in KEY lines
+/ KEY/ { gsub(/\([0-9]+\)/, "") }
+
 # Print all fields definition lines except the `KEY` lines.
 /^  / && !/^(  KEY|\);)/ {
 	gsub( /AUTO_INCREMENT|auto_increment/, "" )
@@ -52,14 +85,16 @@ BEGIN{ FS=",$" }
 	if ($0 == ");"){
 		print
 	} else {
-		if ( match( $0, /\"[^\"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
-		if ( match( $0, /\([^\)]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
+		if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
+		if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
 		key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
 	}
 }
 
 # Print all `KEY` creation lines.
 END {
 	for (table in key) printf key[table]
+	print "END TRANSACTION;"
 }
-' 
+'
+exit 0
diff --git a/mysql2sqlite b/mysql2sqlite
@@ -52,8 +52,8 @@ BEGIN{ FS=",$" }
 	if ($0 == ");"){
 		print
 	} else {
-		if ( match( $0, /\"[^\"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
-		if ( match( $0, /\([^\)]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
+		if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
+		if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
 		key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
 	}
 }

diff --git a/mysql2sqlite b/mysql2sqlite
@@ -42,7 +42,6 @@ BEGIN{ FS=",$" }
 	if (prev) print prev ","
 	prev = $1
 }
-}
 
 # `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
 # in a separate `CREATE KEY` command. The index name is prefixed by the table name to 

diff --git a/mysql2sqlite b/mysql2sqlite
@@ -32,16 +32,17 @@ BEGIN{ FS=",$" }
 
 # Print all fields definition lines except the `KEY` lines.
 /^  / && !/^(  KEY|\);)/ {
-	gsub( /AUTO_INCREMENT/, "" )
-	gsub( /CHARACTER SET [^ ]+ /, "" )
-	gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP/, "" )
-	gsub( /COLLATE [^ ]+ /, "" )
-	gsub(/enum[^)]+\)/, "text ")
-	gsub(/set\([^)]+\)/, "text ")
-	gsub(/unsigned/, "")
+	gsub( /AUTO_INCREMENT|auto_increment/, "" )
+	gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
+	gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
+	gsub( /(COLLATE|collate) [^ ]+ /, "" )
+	gsub(/(ENUM|enum)[^)]+\)/, "text ")
+	gsub(/(SET|set)\([^)]+\)/, "text ")
+	gsub(/UNSIGNED|unsigned/, "")
 	if (prev) print prev ","
 	prev = $1
 }
+}
 
 # `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
 # in a separate `CREATE KEY` command. The index name is prefixed by the table name to 

diff --git a/mysql2sqlite b/mysql2sqlite
@@ -1,6 +1,6 @@
 #!/bin/sh
 
-# Awk is choosen because it's fast and portable. You can use gawk, traditional awk or even the lightning fast mawk.
+# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
 # The mysqldump file is traversed only once.
 
 # Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the

diff --git a/mysql2sqlite b/mysql2sqlite
@@ -4,7 +4,7 @@
 # The mysqldump file is traversed only once.
 
 # Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
-# CREATE block and create them in a separate commands _after_ all the INSERTs.
+# CREATE block and create them in separate commands _after_ all the INSERTs.
 
 # Usage: $ ./mysql2sqlite | sqlite3 database.sqlite
 

diff --git a/mysql2sqlite b/mysql2sqlite
@@ -0,0 +1,65 @@
+#!/bin/sh
+
+# Awk is choosen because it's fast and portable. You can use gawk, traditional awk or even the lightning fast mawk.
+# The mysqldump file is traversed only once.
+
+# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
+# CREATE block and create them in a separate commands _after_ all the INSERTs.
+
+# Usage: $ ./mysql2sqlite | sqlite3 database.sqlite
+
+mysqldump  --compatible=ansi --skip-extended-insert --compact -u **** -p******  datbase_name | \
+
+
+awk -F ",$" '
+
+BEGIN{ FS=",$" }
+
+# Skip comments
+/^\/\*/ { next }
+
+# Print all `INSERT` lines. The single quotes are protected by another single quote.
+/INSERT/ { gsub( /\\\047/, "\047\047" ); print; next }
+
+# Print the ´CREATE´ line as is and capture the table name.
+/^CREATE/ {
+	print
+	if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
+}
+
+# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
+/^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }
+
+# Print all fields definition lines except the `KEY` lines.
+/^  / && !/^(  KEY|\);)/ {
+	gsub( /AUTO_INCREMENT/, "" )
+	gsub( /CHARACTER SET [^ ]+ /, "" )
+	gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP/, "" )
+	gsub( /COLLATE [^ ]+ /, "" )
+	gsub(/enum[^)]+\)/, "text ")
+	gsub(/set\([^)]+\)/, "text ")
+	gsub(/unsigned/, "")
+	if (prev) print prev ","
+	prev = $1
+}
+
+# `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
+# in a separate `CREATE KEY` command. The index name is prefixed by the table name to 
+# avoid a sqlite error for duplicate index name.
+/^(  KEY|\);)/ {
+	if (prev) print prev
+	prev=""
+	if ($0 == ");"){
+		print
+	} else {
+		if ( match( $0, /\"[^\"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
+		if ( match( $0, /\([^\)]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
+		key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
+	}
+}
+
+# Print all `KEY` creation lines.
+END {
+	for (table in key) printf key[table]
+}
+'