The procedure of moving SQLite databases to MySQL server is not quite difficult, compared to migration between another DBMS. The reason behind this relative simplicity is really because SQLite doesn’t have such sophisticated database objects as stored procedures. What this means is, SQLite databases are simply used as storages while all data handling logicis place din external application(s). For this reason, it’s basically required in transferring the data from SQLite to MySQL database.
Despite this considerations, database migration from SQLite to MySQL can be a hard task due to the following challenges:
- SQLlite and MySQL have different methods to getting out of strings inside INSERTINTO clauses
- SQLlite uses ‘t’ and ‘f’ for booleans, MySQL uses 1and 0
There is also a wide variety of techniques to SQLite to MySQL migration, let’s assess the most popular options. One of the best and easy strategy is, migration using sqlite3 and mysql commands:
- dump the sqlite database with the utility sqlite3 via statement
$echo”.dump archive” | sqlite3 mydb.sdb>mydb.sql
Adjustthe processto the sqlite3 databaseasnecessary. Keep in mind thatyou have toinstall sqlite3 first.
- create the database in MySQLif it is necessary via statement
$echo “CREATE DATABASEmydb”| mysql -u root -p
- restore with the items in thefilesql, massaged lightly as the following
$sed -e ‘/PRAGMA/d’ -e’s/BEGIN/START/’ -e ‘s/”archive”/archive/’ <mydb.sql| mysql -u root -p –database=mydb
This would develop a database db test in MySQL, simply by using a single table “archive” holding those items in yours quiet archive file. Keep in mind that this process may not work together with large complex SQLite databases due to essential alteration in formats of DDL and INSERT statements between SQLite and MySQL.
Last method is to apply one among conversion scripts on Perl or Python that can automate conversion of SQLite script file into MySQL format. Here is a good instance of Perl script that handles most vital 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 TABLEIFEXISTS $name;\nCREATETABLEIF NOTEXISTS $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;
}
}
Finally, the best commercial software will allow you to fully auto mate the conversion of SQLite databases into MySQL format. An example of such tools is SQLite to MySQL available through Intelligent Converters.
This software provides an excellent opportunity to customize every available parameter in the conversion process. There’s option to customize resulting table structure that allows you to enhance the name and type of each column or exclude some columns from conversion. Further more, the software can migrate SQLite database to MySQL server automatically or export the data in to local MySQL script file containing SQL statements to create tables and fill all the data. The subsequent option can be used should the target MySQL server doesn’t accept remote connections.
