Negosentro| SQLite to MySQL: Database migration process |When compared to other DBMS, the migration of database from SQLite to MySQL is not a hard task, it only involves expertise and time. Due to the simplicity in the database objects of SQLite, the process of migration is somewhat similar as well. An example of such advanced database object that is absent in SQLite is stored procedures.
Thus, they can be likened to storages whereas an external application handles data logic. Thus, transfer of data from SQLite to MySQL database is usually simple and quick.
Nevertheless, the simplicity of the migration process comes with a few challenges as well. The process of conversion is often complicated due to:
Method of escaping strings INSERT INTO clauses in both SQLite and MySQL databases.
While MySQL uses ‘1’ and ‘0’ for BOOLEAN type, SQLite uses values,‘t’ and ‘f’.
There are several methods used in the process of migration from SQLite to MySQL. We will explore different options in this text. But we will start with the easiest of all the techniques used to migrate between SQLite 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.
2. Create the database in MySQL if it’s necessary via statement:
$ echo “CREATE DATABASE dbtest ” | mysql -u root -p
3. Restore with the items in the file dbtest.sql, messaged slightly as the following
$ sed -e ‘/PRAGMA/d’ -e’s/BEGIN/START/’ -e ‘s/”archive”/archive/’ < dbtest.sql | mysql -u root -p –database=dbtest
A database called ‘dbtest’ is created in MySQL when a single table ‘archive’ which holds items in the SQLite archive file is used. Due to the differences between DDL and INSERT statements between both databases, the method may fail with large complex SQLite databases.
Another option is to use one of the conversion scripts on Python or Perl which automates the conversion of SQLite script file into the format of MySQL. There are several examples of Perl scripts which handle significant variations between both databases 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;
}
}
Due to the complexities that may be experienced during the conversion process, Intelligent Converters company created a special tool which mitigates the risks involved. With this tool, there is a possibility of customizing parameters that are present in the process of conversion. Also, the table structure can be customized which improves the name and type of every column, or excludes some columns from conversion. Learn more about SQLite to MySQL converter at: https://www.convert-in.com/slt2sql.htm
