SQLite To Mysql Database Migration

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:

  1. SQLlitea nd MySQL have diverse methods to evading complications inside INSERT INTO clauses
  2. SQLliteuses ‘t’ and ‘f’ forbooleans, MySQLuses1and0

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:

  1. dump thesqlitedatabasewith theutility sqlite3 viastatement

$echo”.dump archive” | sqlite3 dbtest.sdb>dbtest.sql

Alterthe methodto the basic sqlite3 database. Though you must first of all install sqlite3 first.

  1. create thedatabaseinMySQLif it’snecessaryviastatement

$echo “CREATE DATABASEdbtest”| mysql -u root -p

  1. restore with theitems inthefilesql, messagedslightlyas thefollowing

$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 toMySQLproduced byIntelligent 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 subsequentoptionmust be usedin case thetargetMySQLserverdoesn’tacceptremoteconnections.