The process of transferring SQLite databases to MySQL server isn’t very difficult, as opposed to migration between another DBMS. The reason for this relative simplicity is because SQLite does not have such advanced database objects as stored procedures. This means, SQLite databases are basically utilized as storages while all data handling logic is set in external application(s). Because of this, it is merely required in transferring the data from SQLite to MySQL database.
In spite of this, migration of SQLite databases to MySQL server could be a complicated job as a result of following challenges:
- SQLlite and MySQL have different approaches to escaping strings inside INSERT INTO clauses
- SQLlite uses ‘t’ and ‘f’ for booleans, MySQL uses 1 and 0
You will find a variety of methods to SQLite to MySQL migration, let’s evaluate the most desired options. One of the most easy technique is migration using sqlite3 and mysql commands:
- dump the sqlite database with the utility sqlite3 via statement
$ echo “.dump archive” | sqlite3 dbtest.sdb > dbtest.sql
Adjust the method to the sqlite3 database as necessary. Remember that you need to install sqlite3 first.
- create the database in MySQL if it’s necessary via statement
$ echo “CREATE DATABASE dbtest ” | mysql -u root -p
- restore with the items in the file dbtest.sql, massaged slightly as the following
$ sed -e ‘/PRAGMA/d’ -e’s/BEGIN/START/’ -e ‘s/”archive”/archive/’ < dbtest.sql | mysql -u root -p –database=dbtest
This would create a database dbtest in MySQL, by using a single table “archive” holding the items in your sqlite archive file. Remember that this method might not work with large complex SQLite databases as a result of essential difference in formats of DDL and INSERT statements between SQLite and MySQL.
Last option is by using certainly one of conversion scripts on Perl or Python that could automate conversion of SQLite script file into MySQL format. Below is a good example of Perl script that handles most significant variations between SQLite and MySQL while converting the script file:
#! /usr/bin/perl
while ($line = <>){
if (($line !~ /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){
if ($line =~ /CREATE TABLE \”([a-z_]*)\”(.*)/){
$name = $1;
$sub = $2;
$sub =~ s/\”//g;
$line = “DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n”;
}
elsif ($line =~ /INSERT INTO \”([a-z_]*)\”(.*)/){
$line = “INSERT INTO $1$2\n”;
$line =~ s/\”/\\\”/g;
$line =~ s/\”/\’/g;
}else{
$line =~ s/\’\’/\\\’/g;
}
$line =~ s/([^\\’])\’t\'(.)/$1THIS_IS_TRUE$2/g;
$line =~ s/THIS_IS_TRUE/1/g;
$line =~ s/([^\\’])\’f\'(.)/$1THIS_IS_FALSE$2/g;
$line =~ s/THIS_IS_FALSE/0/g;
$line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;
print $line;
}
}
Lastly, the suitable commercial software allows to completely automate the conversion of SQLite databases into MySQL format. One example of these tools is SQLite to MySQL via Intelligent Converters. It offers a superior opportunity to customize every available parameter in the conversion process. There’s choice to customize resulting table structure that enables to improve name and type of each column or exclude some columns from conversion. In addition, the program can migrate SQLite database to MySQL server instantly or export the data into local MySQL script file containing SQL statements to create tables and fill all of them data. The subsequent option must be used in case the target MySQL server doesn’t accept remote connections.
