Migrate data from Sqlite3 to Mysql

Khi làm việc, mình tìm thấy một file .db có dữ liệu mà mình cần. Đây là một file dữ liệu của Sqlite3. Tuy nhiên mình lại không biết làm thế nào để sử dụng nó do chỉ quen làm việc với Mysql. Vậy bài toán đặt ra là tìm cách nào để convert dữ liệu này từ định dạng của Sqlite3 sang định dạng file dump của Mysql, hoặc là bằng cách nào đó có thể import dữ liệu trong file .db này vào Mysql.

Sau một hồi tìm hiểu thì mình tìm thấy một số cách như sau.

Một số tool được gợi ý

SQLite Data Wizard 11.1

http://www.sqlmaestro.com/products/sqlite/datawizard/ Đây là một tool khá mạnh có Window GUI và cung cấp nhiều tính năng tiện ích. Mặc dù giá của nó là $79 nhưng lại có Trial 30 ngày, vì vậy có thể dùng được.

Các tính năng chính gồm có :

  • Data Pump: transfer any schema and data to SQLite
  • Data export to as many as 18 file formats
  • Data import from Excel, CSV, text files and more
  • ASP.NET Generator: create full set of ASP.NET scripts
  • Flexible Task Scheduler
  • The Agent application to execute tasks in background mode
  • Powerful command-line interface

DB converter from SQLite to MySQL

https://dbconvert.com/sqlite/mysql/?DB=10

  • Cung cấp tính năng đồng bộ và chuyển đổi từ SQLite sang MySQL server và ngược lại.
  • Có thể chuyển toàn bộ DB hoặc chuyển từng phần của DB
  • Bao gồm cả GUI và command line mode
  • Có thể chuyển dữ liệu từ SQlite file đến local hoặc tới remote host.

Tool này cũng có giá là $79, nhưng không có phiên bản trial (orz)

ESF Database Migration Toolkit

http://www.easyfrom.net/download/ Cho phép migrate data giữa SQLite và các database/file systems khác, ví dụ như SQLite, SQL Server, Oracle, MySQL, PostgreSQL, MS-Access, MS-Excel, Foxpro and so on.

Tool này có giá là $219 và có Free trial là 30 ngày

Sử dụng MySQL Workbench

MySQL Workbench có môt tính năng đó là Migration Wizard.

Convert tay, sử dụng script tự viết

Đây là một list các phần khác nhau giữa SQL syntax của file .db của Sqlite3 và file .dump của Mysql (có thể vẫn chưa đầy đủ)

  • Các dòng bắt đầu với
    • BEGIN TRANSACTION
    • COMMIT
    • sqlite_sequence
    • CREATE UNIQUE INDEX sẽ không được sử dụng trong MySQL
  • Sqlite sử dụng CREATE TABLE/INSERT INTO "table_name" và MySQL sử dụng CREATE TABLE/INSERT INTO table_name
  • MySQL không sử dụng dấu ngoặc kép trong schema definition
  • MySQL sử dụng dấu ngoặc đơn cho các string bên trong câu lệnh INSERT INTO
  • SQLite và MySQL sử dụng các cách khác nhau để escape string bên trong câu lệnh INSERT INTO
  • SQLite sử dụng 't' và 'f' cho booleans, MySQL sử dụng 1 và 0
  • SQLite sử dụng AUTOINCREMENT, MySQL sử dụng AUTO_INCREMENT

Từ những sự khác nhau này, ta có thể viết một perl script nhỏ để convert cấu trúc giữa hai file. Script này tất nhiên sẽ không thể áp dụng cho tất cả các trường hợp, tuỳ vào cấu trúc dữ liệu của bạn, có thể tuỳ biến nó.

#! /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;
    }
}

Sử dụng ORM

Một phương pháp khác hoạt động khá hiệu quả nhưng lại ít được đề cập đến, đó là sử dụng một ORM class và liên kết tới dữ liệu theo nhiều cách khác nhau. Ví dụ bạn có thể sử dụng: PHP (RedBean), Python (Django's ORM layer, Storm, SqlAlchemy), Ruby on Rails (ActiveRecord), Cocoa (CoreData)

Việc phải làm đó là:

  • Load dữ liệu từ database của SQLite sử dụng ORM class
  • Lưu dữ liệu trong memory hoặc serialize sang ổ đĩa
  • Lưu lại dữ liệu và MySQL sử dụng ORM class

Tham khảo

http://www.sqlite.org/cvstrac/wiki?p=ConverterTools

http://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql