Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 880 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
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