#! /usr/bin/perl
#
# based
on
https://stackoverflow.com/a/87531/5742651
# usage: sqlite3 .dump database_name.sqlite3 | perl sqlite2mysql.pl | mysql -u root -p $import_database_name
#
#
ignore
follow lines:
#
BEGIN
TRANSACTION
#
COMMIT
# sqlite_sequence
#
CREATE
UNIQUE
INDEX
# PRAGMA foreign_keys=
OFF
#
"tablename/field"
=> `tablename/field`
# booleans
't'
and
'f'
=> 1
and
0
# AUTOINCREMENT => AUTO_INCREMENT
#
varchar
=>
varchar
(255)
#
CREATE
TABLE
table
... =>
DROP
TABLE
table
;
CREATE
TABLE
table
...
# Merge
insert
sqls
into
multiple
insert
to
speed up
#
INSERT
INTO
table
VALUES
(
'val1'
);
#
INSERT
INTO
table
VALUES
(
'val2'
); =>
INSERT
INTO
table
VALUES
(
'val1'
), (
'val2'
), (
'val3'
);
#
INSERT
INTO
table
VALUES
(
'val3'
);
my $
open
=0;
my $line_cache =
''
;
#
For
speed up
print
"SET GLOBAL max_allowed_packet=209715200;\n"
;
#print
"SET AUTOCOMMIT=0;\n"
;
while ($line = <>){
if (($line !~ /PRAGMA foreign_keys=
OFF
/) && ($line !~ /
BEGIN
TRANSACTION
/) && ($line !~ /
COMMIT
/) && ($line !~ /sqlite_sequence/) && ($line !~ /
CREATE
UNIQUE
INDEX
/)){
if ($line =~ /
CREATE
TABLE
\
"([a-z_0-9]*)\"(.*)/){
$name = "
\`$1\`
";
$sub = $2;
$sub =~ s/varchar([^(])/varchar(255)$1/g;
$line = "
DROP
TABLE
IF EXISTS $
name
;\nCREATE
TABLE
$
name
$sub\n
";
}
elsif ($line =~ /CREATE VIEW ([a-z_0-9]*)(.*)/){
$name = "
\`$1\`
";
$sub = $2;
$line = "
DROP
VIEW
IF EXISTS $
name
;\nCREATE
VIEW
$
name
$sub\n
";
}
elsif ($line =~ /INSERT INTO \"([a-z_]*)\" VALUES(.*);/){
if ($open == 0) {
$open = 1;
$line_cache .= "
INSERT
INTO
\`$1\`
VALUES
$2
";
} else {
$line_cache .= "
, $2
";
}
next;
}else{
$line =~ s/\'\'/\\\'/g;
}
if ($open == 1) {
$open = 0;
$line = $line_cache."
;\n
".$line;
$line_cache = '';
}
$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;
}
}
#print "
SET
AUTOCOMMIT=1;\n";