1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
import re
import fileinput
from optparse import OptionParser
import sys
IGNOREDPREFIXES = [
'PRAGMA',
'BEGIN TRANSACTION;',
'COMMIT;',
'DELETE FROM sqlite_sequence;',
'INSERT INTO "sqlite_sequence"',
]
def _replace(line):
if any(line.lower().startswith(prefix.lower()) for prefix in IGNOREDPREFIXES):
return
line = re.sub("AUTOINCREMENT", "AUTO_INCREMENT", line, flags=re.IGNORECASE)
line = re.sub("boolean", "TINYINT(1)", line, flags=re.IGNORECASE)
line = re.sub("boolean DEFAULT 't'", "TINYINT(1) DEFAULT 1", line, flags=re.IGNORECASE)
line = re.sub("boolean DEFAULT 'f'", "TINYINT(1) DEFAULT 0", line, flags=re.IGNORECASE)
line = re.sub("DEFAULT 't'", "DEFAULT 1", line, flags=re.IGNORECASE)
line = re.sub("DEFAULT 'f'", "DEFAULT 0", line, flags=re.IGNORECASE)
line = re.sub("TINYINT(1) DEFAULT 0 NOT NULL", "TINYINT(1) NOT NULL DEFAULT 0", line, flags=re.IGNORECASE)
line = re.sub("TINYINT(1) DEFAULT 1 NOT NULL", "TINYINT(1) NOT NULL DEFAULT 1", line, flags=re.IGNORECASE)
line = re.sub("varchar ", "varchar(255) ", line, flags=re.IGNORECASE)
line = re.sub("varchar,", "varchar(255),", line, flags=re.IGNORECASE)
line = re.sub("varchar\)", "varchar(255))", line, flags=re.IGNORECASE)
return line
def _backticks(line, in_string):
"""Replace double quotes by backticks outside (multiline) strings
>>> _backticks('''INSERT INTO "table" VALUES ('"string"');''', False)
('INSERT INTO `table` VALUES (\\'"string"\\');', False)
>>> _backticks('''INSERT INTO "table" VALUES ('"Heading''', False)
('INSERT INTO `table` VALUES (\\'"Heading', True)
>>> _backticks('''* "text":http://link.com''', True)
('* "text":http://link.com', True)
>>> _backticks(" ');", True)
(" ');", False)
"""
new = ''
for c in line:
if not in_string:
if c == "'":
in_string = True
elif c == '"':
new = new + '`'
continue
elif c == "'":
in_string = False
new = new + c
return new, in_string
def _process(opts, lines):
if opts.database:
yield '''\
drop database if exists {d};
create database {d} character set utf8;
grant all on {d}.* to {u}@'%' identified by '{p}';
use {d};\n'''.format(d=opts.database, u=opts.username, p=opts.password)
yield "SET sql_mode='NO_BACKSLASH_ESCAPES';\n"
in_string = False
for line in lines:
if not in_string:
line = _replace(line)
if line is None:
continue
line, in_string = _backticks(line, in_string)
yield line
def main():
op = OptionParser()
op.add_option('-d', '--database')
op.add_option('-u', '--username')
op.add_option('-p', '--password')
opts, args = op.parse_args()
lines = (l for l in fileinput.input(args))
for line in _process(opts, lines):
print line,
if __name__ == "__main__":
main()
|