convertSqliteToMySql.pl

#!/usr/bin/perl
use strict;
use warnings;

while ( <> )
{
    next if m'
        ^PRAGMA             |
        sqlite_sequence     |
        CREATE UNIQUE INDEX
    'x;
    
    if ( /^CREATE TABLE/i )
    {
        s/^CREATE TABLE \b([0-9A-Za-z_]*)\b/CREATE TABLE IF NOT EXISTS `$1`/i;
        s/^CREATE TABLE \"([0-9A-Za-z_]*)\"/CREATE TABLE IF NOT EXISTS `$1`/i;
        s/^CREATE TABLE \'([0-9A-Za-z_]*)\'/CREATE TABLE IF NOT EXISTS `$1`/i;
        s/\bSTRING\b/TEXT/gi;
        s/\bAUTOINCREMENT\b/AUTO_INCREMENT/gi;
        #s/(?<=[(,])\s*([0-9a-z_]*)\s+([0-9a-z_]*)/`$1` $2/gi;
    }
    elsif ( /^BEGIN TRANSACTION/i )
    {
        s/^BEGIN TRANSACTION/START TRANSACTION/i;
    }
    elsif ( /^INSERT INTO/i )
    {
        s/^INSERT INTO [\'\"]([0-9a-z_]*)[\'\"]/INSERT INTO `$1`/i;
        while ( !/;$/ )
        {
            my $line = $_;
            my $next = <>;
            $_ = "$line$next";
        }
        s/\"/\\\'/g; # " => \'
    }
    elsif ( /^CREATE TRIGGER/i )
    {
        s/AFTER UPDATE OF [0-9a-z_]* ON/AFTER UPDATE ON/i;
    }
    elsif ( /^CREATE INDEX/i )
    {
        s/^CREATE INDEX \b([0-9a-z_]*)\b ON \b([0-9A-Za-z_]*)\b/CREATE INDEX `$1` ON `$2`/i;
        #s/(?<=[(,])\s*([0-9a-z_]*)\b/`$1`/gi;
    }
    else
    {
        s/\'\'/\\\'/g; # '' => \'
    }
    
    print;
}