tengla / sqlite3-to-mysql

Make an Sqlite3 export MySQL readable.

sqlite3-to-mysql¶ ↑

Simple sed script for attempting to convert Sqlite3 sql to MySql sql.

Usage:¶ ↑

sqlite3-to-mysql filename.sql > filename.new.sql

Notes¶ ↑

Please note that sed needs to be in your $PATH

Not guaranteed to work, but can serve as a starting point.

Note that varchar will be translated to varchar(255).

Decided to make this after trying the yaml_db gem, which I found to be excruciatingly slow for any dataset of considerable size.

sqlite3-to-mysql's Issues

Apostrophes are not screened

Регистронезависимая замена

A very good converter, but in order to use it, I had to include a case-independent search for AUTOINCREMENT in sed, because my "sqlite3 database.sqlite .dump" creates a dump in lowercase.

CREATE TABLE "migrations" ("id" integer not null primary key autoincrement, "migration" varchar not null, "batch" integer not null);

Added an "I" at the end.

sed \
-e '/PRAGMA.*;/ d' \
-e '/BEGIN TRANSACTION.*/ d' \
-e '/COMMIT;/ d' \
-e '/.*sqlite_sequence.*;/d' \
-e "s/ varchar/ varchar($VARCHAR)/g" \
-e "s/ integer/ int/g" \
-e 's/"/`/g' \
-e 's/CREATE TABLE \(`\w\+`\)/DROP TABLE IF EXISTS \1;\nCREATE TABLE \1/' \
-e 's/\(CREATE TABLE.*\)\(PRIMARY KEY\) \(AUTOINCREMENT\)\(.*\)\();\)/\1AUTO_INCREMENT\4, PRIMARY KEY(id)\5/I' \
-e "s/'t'/1/g" \
-e "s/'f'/0/g" \
$1

varchar size 255 sometimes is too small

The script works fine for me, but I found some string value is exceed the varchar 255 limit. Since varchar does not consume extra space for mysql, maybe you can make it a little more bigger.

the script replace all " to ' even for the content in the db column

this script take the " in multiple line content into account.

http://community.kde.org/Kexi/Migration/sqlite3-to-mysql.py