Project

General

Profile

RE: Migrating from sqlite3 to mysql » sqlite3-to-mysql.py

Rogério Néo, 2018-02-06 15:11

 
1
#! /usr/bin/env python
2
import re, fileinput, tempfile
3
from optparse import OptionParser
4

    
5

    
6
IGNOREDPREFIXES = [
7
    'PRAGMA',
8
    'BEGIN TRANSACTION;',
9
    'COMMIT;',
10
    'DELETE FROM sqlite_sequence;',
11
    'INSERT INTO "sqlite_sequence"',
12
]
13

    
14
def _replace(line):
15
    if any(line.startswith(prefix) for prefix in IGNOREDPREFIXES):
16
        return
17
#    line = line.replace("INTEGER PRIMARY KEY", "INTEGER PRIMARY KEY")
18
    line = line.replace("AUTOINCREMENT", "AUTO_INCREMENT")
19
    line = line.replace("boolean", "TINYINT(1)")
20
    line = line.replace("boolean DEFAULT 't'", "TINYINT(1) DEFAULT 1")
21
    line = line.replace("boolean DEFAULT 'f'", "TINYINT(1) DEFAULT 0")
22
    line = line.replace("DEFAULT 't'", "DEFAULT 1")
23
    line = line.replace("DEFAULT 'f'", "DEFAULT 0")
24
    line = line.replace(",'t'", ",1")    
25
    line = line.replace(",'f'", ",0")
26
    line = line.replace("TINYINT(1) DEFAULT 0 NOT NULL", "TINYINT(1) NOT NULL DEFAULT 0")
27
    line = line.replace("TINYINT(1) DEFAULT 1 NOT NULL", "TINYINT(1) NOT NULL DEFAULT 1")
28
    line = line.replace("varchar ", "varchar(255) ")
29
    line = line.replace("varchar,", "varchar(255),")
30
    line = line.replace("varchar)", "varchar(255))")
31
    return line
32

    
33

    
34
def _backticks(line, in_string):
35
    """Replace double quotes by backticks outside (multiline) strings
36

    
37
    >>> _backticks('''INSERT INTO "table" VALUES ('"string"');''', False)
38
    ('INSERT INTO `table` VALUES (\\'"string"\\');', False)
39

    
40
    >>> _backticks('''INSERT INTO "table" VALUES ('"Heading''', False)
41
    ('INSERT INTO `table` VALUES (\\'"Heading', True)
42

    
43
    >>> _backticks('''* "text":http://link.com''', True)
44
    ('* "text":http://link.com', True)
45

    
46
    >>> _backticks(" ');", True)
47
    (" ');", False)
48

    
49
    """
50
    new = ''
51
    for c in line:
52
        if not in_string:
53
            if c == "'":
54
                in_string = True
55
            elif c == '"':
56
                new = new + '`'
57
                continue
58
        elif c == "'":
59
            in_string = False
60
        new = new + c
61
    return new, in_string
62

    
63
def _process(opts, lines):
64
    if opts.database:
65
        yield '''\
66
drop database {d};
67
create database {d} character set utf8;
68
grant all on {d}.* to {u}@'%' identified by '{p}';
69
use {d};\n'''.format(d=opts.database, u=opts.username, p=opts.password)
70
    yield "SET sql_mode='NO_BACKSLASH_ESCAPES';\n"
71

    
72
    in_string = False
73
    for line in lines:
74
        if not in_string:
75
            line = _replace(line)
76
            if line is None:
77
                continue
78
        line, in_string = _backticks(line, in_string)
79
        yield line
80

    
81
def _removeNewline(line, in_string):
82
    new = ''
83
    for c in line:
84
        if not in_string:
85
            if c == "'":
86
                in_string = True
87
        elif c == "'":
88
            in_string = False
89
        elif in_string:
90
            if c == "\n":
91
                 new = new + 'Newline333'
92
                 continue
93
            if c == "\r":
94
                 new = new + 'carriagereturn333'
95
                 continue
96
        new = new + c
97
    return new, in_string
98
	
99
def _replaceNewline(lines):
100
    for line in lines:
101
           line = line.replace("Newline333", "\n")
102
           line = line.replace("carriagereturn333", "\r")
103
           yield line
104

    
105
def _Newline(lines):
106
    in_string = False
107
    for line in lines:
108
        if line is None:
109
           continue
110
        line, in_string = _removeNewline(line, in_string)
111
        yield line
112
	
113
def main():
114
    op = OptionParser()
115
    op.add_option('-d', '--database')
116
    op.add_option('-u', '--username')
117
    op.add_option('-p', '--password')
118
    opts, args = op.parse_args()
119
    lines = (l for l in fileinput.input(args))
120
    lines = (l for l in _Newline(lines))
121
    f = tempfile.TemporaryFile()
122
    for line in lines:
123
        f.write(line)
124
    f.seek(0)
125
    lines = (l for l in f.readlines())
126
    f.close()
127
    lines = (l for l in _process(opts, lines))
128
    for line in _replaceNewline(lines):
129
       print line,
130

    
131
if __name__ == "__main__":
132
    main()
(2-2/2)