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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
|
# Copyright 2009-2010 Canonical Ltd. This software is licensed under the
# GNU Affero General Public License version 3 (see the file LICENSE).
"""Sort SQL dumps.
This library provides functions for the script sort_sql.py, which resides in
database/schema/.
"""
__metaclass__ = type
import re
class Parser:
r"""Parse an SQL dump into logical lines.
>>> p = Parser()
>>> p.feed("UPDATE foo SET bar='baz';\n")
>>> p.feed("\n")
>>> p.feed("INSERT INTO foo (id, x) VALUES (1, 23);\n")
>>> p.feed("INSERT INTO foo (id, x) VALUES (2, 34);\n")
>>> for line in p.lines:
... print repr(line)
(None, "UPDATE foo SET bar='baz';")
(None, '')
(1, 'INSERT INTO foo (id, x) VALUES (1, 23);')
(2, 'INSERT INTO foo (id, x) VALUES (2, 34);')
"""
def __init__(self):
self.lines = []
self.buffer = ''
self.line = ''
def parse_quoted_string(self, string):
"""Parse strings enclosed in single quote marks.
This takes a string of the form "'foo' ..." and returns a pair
containing the first quoted string and the rest of the string. The
escape sequence "''" is recognised in the middle of a quoted string as
representing a single quote, but is not unescaped.
ValueError is raised if there is no quoted string at the beginning of
the string.
>>> p = Parser()
>>> p.parse_quoted_string("'foo'")
("'foo'", '')
>>> p.parse_quoted_string("'foo' bar")
("'foo'", ' bar')
>>> p.parse_quoted_string("'foo '' bar'")
("'foo '' bar'", '')
>>> p.parse_quoted_string("foo 'bar'")
Traceback (most recent call last):
...
ValueError: Couldn't parse quoted string
"""
quoted_pattern = re.compile('''
' (?: [^'] | '' )* '
''', re.X | re.S)
match = quoted_pattern.match(string)
if match:
quoted_length = len(match.group(0))
return string[:quoted_length], string[quoted_length:]
else:
raise ValueError("Couldn't parse quoted string")
def is_complete_insert_statement(self, statement):
"""Check whether a string looks like a complete SQL INSERT
statement."""
while statement:
if statement == ');\n':
return True
elif statement[0] == "'":
string, statement = self.parse_quoted_string(statement)
else:
statement = statement[1:]
return False
def parse_line(self, line):
r"""Parse a single line of SQL.
>>> p = Parser()
Something that's not an INSERT.
>>> p.parse_line('''UPDATE foo SET bar = 42;\n''')
(None, 'UPDATE foo SET bar = 42;\n')
A simple INSERT.
>>> p.parse_line('''INSERT INTO foo (id, x) VALUES (2, 'foo');\n''')
(2, "INSERT INTO foo (id, x) VALUES (2, 'foo');\n")
Something trickier: multiple lines, and a ');' in the middle.
>>> p.parse_line('''INSERT INTO foo (id, x) VALUES (3, 'b',
... 'b
... b);
... b');
... ''')
(3, "INSERT INTO foo (id, x) VALUES (3, 'b',\n'b\nb);\nb');\n")
"""
if not line.startswith('INSERT '):
return (None, line)
if not self.is_complete_insert_statement(line):
raise ValueError("Incomplete line")
insert_pattern = re.compile('''
^INSERT \s+ INTO \s+ \S+ \s+ \([^)]+\) \s+ VALUES \s+ \((\d+)
''', re.X)
match = insert_pattern.match(line)
if match:
return int(match.group(1)), line
else:
return line, line
def feed(self, s):
"""Give the parser some text to parse."""
self.buffer += s
while '\n' in self.buffer:
line, self.buffer = self.buffer.split('\n', 1)
self.line += line + '\n'
try:
value, line = self.parse_line(self.line)
except ValueError:
pass
else:
self.lines.append((value, self.line[:-1]))
self.line = ''
def print_lines_sorted(file, lines):
r"""Print a set of (value, line) pairs in sorted order.
Sorting only occurs within blocks of statements.
>>> lines = [
... (10, "INSERT INTO foo (id, x) VALUES (10, 'data');"),
... (4, "INSERT INTO foo (id, x) VALUES (4, 'data\nmore\nmore');"),
... (7, "INSERT INTO foo (id, x) VALUES (7, 'data');"),
... (1, "INSERT INTO foo (id, x) VALUES (1, 'data');"),
... (None, ""),
... (2, "INSERT INTO baz (id, x) VALUES (2, 'data');"),
... (1, "INSERT INTO baz (id, x) VALUES (1, 'data');"),
... ]
>>> import sys
>>> print_lines_sorted(sys.stdout, lines)
INSERT INTO foo (id, x) VALUES (1, 'data');
INSERT INTO foo (id, x) VALUES (4, 'data
more
more');
INSERT INTO foo (id, x) VALUES (7, 'data');
INSERT INTO foo (id, x) VALUES (10, 'data');
<BLANKLINE>
INSERT INTO baz (id, x) VALUES (1, 'data');
INSERT INTO baz (id, x) VALUES (2, 'data');
"""
block = []
def print_block(block):
block.sort()
for line in block:
sort_value, string = line
print >>file, string
for line in lines:
sort_value, string = line
if string == '':
if block:
print_block(block)
block = []
file.write('\n')
else:
block.append(line)
if block:
print_block(block)
|