MySQL to Sqlite converter

mysql2sqlite.sh

#!/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.

# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

# Thanks to and @artemyk and @gkuenning for their nice tweaks.

mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \

awk '

BEGIN {
	FS=",$"
	print "PRAGMA synchronous = OFF;"
	print "PRAGMA journal_mode = MEMORY;"
	print "BEGIN TRANSACTION;"
}

# 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 other comments
/^\/\*/ { next }

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/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.
/^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" ) }

# 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/, "" )
	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 
# 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]
	print "END TRANSACTION;"
}
'
exit 0

Generalities

This small shell script should help you to convert a mysqldump file into a Sqlite3 compatible file.

Works with the original awk, gawk and mawk. If you have, like me, laaarge files to convert (millions of lines) you better off using the lightning fast mawk. For these large files you could also consider using a csv export from MySQL and the import Sqlite command.

As usual: your mileage may vary but if you have any issue with this snippet, ping me and I'll see what I can do.

Usage

Usual stuff:

copy paste this in a file, say: mysql2sqlite.sh file
make it executable: $ chmod +x mysql2sqlite.sh
run it $ ./mysql2sqlite.sh -u MyUserName -pMySecretPassWord myDbase | sqlite3 database.sqlite
If you only need to create the table structures without data:
$ ./mysql2sqlite.sh --no-data -u MyUserName -pMySecretPassWord myDbase | sqlite3 database.sqlite

Remarks

In the INSERTs, the single quote must be protected as SQLite doesn't like the Ten o'clock mysqldump format. It is changed to Ten o''clock
It isn't necessary to convert the varchar() data typa as SQLite accepts them in version 3. Read more over Data Typa affinity in the Sqlite doc.
The AUTO_INCREMENT is not necessary as it is more or less automatically activated for keys declared as PRIMARY. Read more...
The COLLATE declaration is removed to let SQLite collate by its default BINARY rule (Read more) . If BINARY is not what you are after, SQLite let you define User Defined Collation
enum and set data types are not supported in SQLite. They are therefore converted to text.
In a CREATE block, Sqlite only accepts a PRIMARY KEY declaration. Not the single KEY's that have to be removed from the block and added at the end. See example below. They are added after all the INSERT's to speed up the all process.
As far as I know, in MySQL, when you create a KEY (INDEX), it is attached in some way to its table. Not in SQLite. Therefore, all KEY declaration are prefixed by its table name to avoid SQLite error for duplicate key name.
Release notes:

https://gist.github.com/943776/afb04cb97a0977fd1422d5dff7cc02b116238cfb

Deal with key-length specifications in both primary and secondary keys (@artemyk and @gkuenning)
INSERT sanitization (@artemyk)
partial trigger support (@gkuenning). Still some issue when MySQL dumps an INSERT statement like INSERT INTO test2 SET a2 = NEW.a1; a MySQL syntax extension not supported by SQLite.
substantial speed optimization using transactions for inserts (@gkuenning).
Example

Typical mysqldump output:

/!40101 SET @saved_cs_client = @@character_set_client */;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE "clients" (
"CODCLI" varchar(6) NOT NULL DEFAULT '',
"NAME" varchar(10) NOT NULL DEFAULT '',
"STAT" char(2) NOT NULL DEFAULT '',
"VRP" char(2) NOT NULL DEFAULT '',
"TEL" varchar(15) NOT NULL DEFAULT '',
"EMAIL" varchar(100) NOT NULL,
PRIMARY KEY ("CODCLI"),
FULLTEXT KEY "NAME" ("NAME")
);
/!40101 SET character_set_client = @saved_cs_client */;
This will be converted as:

CREATE TABLE "clients" (
"CODCLI" varchar(6) NOT NULL DEFAULT '',
"NAME" varchar(10) NOT NULL DEFAULT '',
"STAT" char(2) NOT NULL DEFAULT '',
"VRP" char(2) NOT NULL DEFAULT '',
"TEL" varchar(15) NOT NULL DEFAULT '',
"EMAIL" varchar(100) NOT NULL,
PRIMARY KEY ("CODCLI")
);
CREATE INDEX "clients_NAME" ON "clients" ("NAME");