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:
- SQLlite and MySQL have diverse methods to evading complications insideINSERTINTOclauses
- SQLlite 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:
- dump the sqlite database with the utility sqlite3 via statement
$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.
- if MySQL database does not exist, it can be created using the command
$echo “CREATE DATABASE dbtest”| mysql -u root -p
- finally, the instructions from file sql can be loaded into MySQL database as follows
$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 TABLEIFEXISTS $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 produced by Intelligent Converters software company.
This 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. This program can export data into local MySQL script file containing SQL statements, and create tables, also filling all of them. The subsequent option must be use din case thetargetMySQLserverdoesn’tacceptremoteconnections.
