| 218 |
jpm |
1 |
#!/bin/sh
|
|
|
2 |
# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
|
|
|
3 |
# CREATE block and create them in separate commands _after_ all the INSERTs.
|
|
|
4 |
|
|
|
5 |
# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
|
|
|
6 |
# The mysqldump file is traversed only once.
|
|
|
7 |
|
|
|
8 |
# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
|
|
|
9 |
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite
|
|
|
10 |
|
|
|
11 |
# Thanks to and @artemyk and @gkuenning for their nice tweaks.
|
|
|
12 |
|
|
|
13 |
# AJOUT 2011-12-27 : chemin vers lampp pour le dump mysql, suppression des commentaires, insenssibilité à la casse pour tous les champs
|
|
|
14 |
|
|
|
15 |
# USAGE :
|
|
|
16 |
# ./mysql2sqlite.sh --default-character-set=utf8 -u root -p tb_eflore bdnt_meta bdnt_ontologies_v4_30 bdtfx_meta bdtfx_v1_01 \
|
|
|
17 |
# bdtfx_v1_02 fournier_meta fournier_v1_00 | \
|
|
|
18 |
# sqlite3 /home/jpm/web/eflore/eflore-projets/services/tests/0.2/bdd/tb_eflore.sqlite
|
|
|
19 |
|
|
|
20 |
/opt/lampp/bin/mysqldump --compatible=ansi --skip-extended-insert --compact "$@" | \
|
|
|
21 |
|
|
|
22 |
awk '
|
|
|
23 |
|
|
|
24 |
BEGIN {
|
|
|
25 |
FS=",$"
|
|
|
26 |
print "PRAGMA synchronous = OFF;"
|
|
|
27 |
print "PRAGMA journal_mode = MEMORY;"
|
|
|
28 |
print "BEGIN TRANSACTION;"
|
|
|
29 |
}
|
|
|
30 |
|
|
|
31 |
# CREATE TRIGGER statements have funny commenting. Remember we are in trigger.
|
|
|
32 |
/^\/\*.*CREATE.*TRIGGER/ {
|
|
|
33 |
gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
|
|
|
34 |
print
|
|
|
35 |
inTrigger = 1
|
|
|
36 |
next
|
|
|
37 |
}
|
|
|
38 |
|
|
|
39 |
# The end of CREATE TRIGGER has a stray comment terminator
|
|
|
40 |
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
|
|
|
41 |
|
|
|
42 |
# The rest of triggers just get passed through
|
|
|
43 |
inTrigger != 0 { print; next }
|
|
|
44 |
|
|
|
45 |
# Skip other comments
|
|
|
46 |
/^\/\*/ { next }
|
|
|
47 |
|
|
|
48 |
# Print all `INSERT` lines. The single quotes are protected by another single quote.
|
|
|
49 |
/INSERT/ {
|
|
|
50 |
gsub( /\\\047/, "\047\047" )
|
|
|
51 |
gsub(/\\n/, "\n")
|
|
|
52 |
gsub(/\\r/, "\r")
|
|
|
53 |
gsub(/\\"/, "\"")
|
|
|
54 |
gsub(/\\\\/, "\\")
|
|
|
55 |
gsub(/\\\032/, "\032")
|
|
|
56 |
print
|
|
|
57 |
next
|
|
|
58 |
}
|
|
|
59 |
|
|
|
60 |
# Print the `CREATE` line as is and capture the table name.
|
|
|
61 |
/^CREATE/ {
|
|
|
62 |
print
|
|
|
63 |
if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
|
|
|
64 |
}
|
|
|
65 |
|
|
|
66 |
# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
|
|
|
67 |
/^ [^"]+KEY/ && !/^ PRIMARY KEY/ { gsub( /.+KEY/, " KEY" ) }
|
|
|
68 |
|
|
|
69 |
# Get rid of field lengths in KEY lines
|
|
|
70 |
/ KEY/ { gsub(/\([0-9]+\)/, "") }
|
|
|
71 |
|
|
|
72 |
# Print all fields definition lines except the `KEY` lines.
|
|
|
73 |
/^ / && !/^( KEY|\);)/ {
|
|
|
74 |
gsub( /AUTO_INCREMENT|auto_increment/, "" )
|
|
|
75 |
gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
|
|
|
76 |
gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
|
|
|
77 |
gsub( /(COLLATE|collate) [^ ]+ /, "" )
|
|
|
78 |
gsub(/(ENUM|enum)[^)]+\)/, "text ")
|
|
|
79 |
gsub(/(SET|set)\([^)]+\)/, "text ")
|
|
|
80 |
gsub(/UNSIGNED|unsigned/, "")
|
|
|
81 |
# AJOUT : suppression des commentaires
|
|
|
82 |
gsub(/(COMMENT|comment) '"'"'.+'"'"',/, ",")
|
|
|
83 |
# AJOUT : insenssibilité à la casse pour tous les champs
|
|
|
84 |
if (prev) print prev " COLLATE NOCASE,"
|
|
|
85 |
prev = $1
|
|
|
86 |
}
|
|
|
87 |
|
|
|
88 |
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print
|
|
|
89 |
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to
|
|
|
90 |
# avoid a sqlite error for duplicate index name.
|
|
|
91 |
/^( KEY|\);)/ {
|
|
|
92 |
if (prev) print prev
|
|
|
93 |
prev=""
|
|
|
94 |
if ($0 == ");"){
|
|
|
95 |
print
|
|
|
96 |
} else {
|
|
|
97 |
if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 )
|
|
|
98 |
if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 )
|
|
|
99 |
key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
|
|
|
100 |
}
|
|
|
101 |
}
|
|
|
102 |
|
|
|
103 |
# Print all `KEY` creation lines.
|
|
|
104 |
END {
|
|
|
105 |
for (table in key) printf key[table]
|
|
|
106 |
print "END TRANSACTION;"
|
|
|
107 |
}
|
|
|
108 |
'
|
|
|
109 |
exit 0
|