Though SQLite and MySQL are both basically for transferring databases, in other words, it’s only jobs are related to storages. SQLite has been proven easier to use due to less advanced database storage procedure, this is mostly opposite to other DBMS. Though this equally means that every other data handling procedure cannot be smoothly handled by SQLite DBMS, and is recommended to be done with external application(s). This has made is somewhat unnecessary to transfer data from SQLite to MySQL databases.
Nevertheless, transferring SQLite databases to MySQL server could be considered complicated due to certain limitations:
- SQLite and MySQL have diverse methods to evading complications inside INSERT INTO clauses
- SQLite uses ‘t’ and ‘f’ for booleans, MySQL uses 1 and 0
Methods of Migration from SQLite to MySQL
There are various methods of migrating from SQLite to MySQL, but for the purpose of this article, let’s look at the easier and mostly used options/commands:
- export the sqlite database into SQL script file via standard utility sqlite3 as follows
$ echo “.dump archive” | sqlite3 dbtest.sdb > dbtest.sql
Alter the method to the basic sqlite3 database. Though you must first of all 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 script file dbtest.sql through the following command line statement
$ sed -e ‘/PRAGMA/d’ -e’s/BEGIN/START/’ -e ‘s/”archive”/archive/’ < dbtest.sql | mysql -u root -p –database=dbtest
Using a single table “archive” that holds the items in your SQLite file would create a database in dbtest in MySQL. Though this might not work with large complex databases in SQLite because of vital differences in the DDL and INSERT formats between SQLite and MySQL.
Finally, this can also be done by converting scripts on Perl and Python that is enabled to automate SQLite script files into MySQL format. Below is an example of Perl script that carries out most important variations between SQLite and MySQL during conversion:
#! /usr/bin/perl
while ($line = <>)
{
if (($line !~ /BEGIN TRANSACTION/) &&
($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) &&
($line !~ /CREATE UNIQUE INDEX/))
{
if ($line =~ /CREATE TABLE \”([a-z_]*)\”(.*)/)
{
$schname = $1;
$tabname = $2;
$tabname =~ s/\”//g;
$line = “DROP TABLE IF EXISTS $schname$tabname;\n”;
$line .= “CREATE TABLE $schname$tabname\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;
}
}
In conclusion, a marketable software that is suitable for conversion of SQLite databases into MySQL allows complete automation, in other words, its automatic in converting. An example is SQLite to MySQL database migration tool has been produced by Intelligent Converters software company. It guarantees the high performance by using custom low-level algorithms to read SQLite and write MySQL data.
The program offers a better opportunity to users, in that they can customize every restriction and utilize same. They can choose to customize the table structure which inwardly improves the names and types of each column or excludes some columns from the entire process.
Finally, the tool can export data into local MySQL script file containing SQL statements, and create tables, also filling all of them. Then the person responsible for migration could import the script file into the target database using the standard tools. This approach might be used in case the target MySQL server doesn’t allow remote access.
Visit the official vendor’s site to learn more about SQLite to MySQL database migration and the related software.