Project

General

Profile

1 2211 aaronmk
# SQL code generation
2
3 2276 aaronmk
import operator
4 2568 aaronmk
import re
5 2276 aaronmk
6 2360 aaronmk
import objects
7 2222 aaronmk
import strings
8 2227 aaronmk
import util
9 2211 aaronmk
10 2587 aaronmk
##### Names
11 2499 aaronmk
12 2608 aaronmk
identifier_max_len = 63 # works for both PostgreSQL and MySQL
13 2587 aaronmk
14
def add_suffix(str_, suffix):
15 2609 aaronmk
    '''Preserves version so that it won't be truncated off the string, leading
16
    to collisions.'''
17 2613 aaronmk
    # Preserve version
18
    before, sep, version = str_.rpartition('#')
19
    if sep != '': # found match
20
        str_ = before
21 2609 aaronmk
        suffix = sep+version+suffix
22 2613 aaronmk
23 2587 aaronmk
    return strings.add_suffix(str_, suffix, identifier_max_len)
24
25 2575 aaronmk
def is_safe_name(name):
26 2583 aaronmk
    '''A name is safe *and unambiguous* if it:
27
    * contains only *lowercase* word (\w) characters
28
    * doesn't start with a digit
29
    * contains "_", so that it's not a keyword
30
    '''
31
    return re.match(r'^(?=.*_)(?!\d)[^\WA-Z]+$', name)
32 2568 aaronmk
33 2499 aaronmk
def esc_name(name, quote='"'):
34
    return quote + name.replace(quote, quote+quote) + quote
35
        # doubling an embedded quote escapes it in both PostgreSQL and MySQL
36
37 2513 aaronmk
def clean_name(name): return name.replace('"', '').replace('`', '')
38
39 2219 aaronmk
##### SQL code objects
40
41 2349 aaronmk
class MockDb:
42 2503 aaronmk
    def esc_value(self, value): return strings.repr_no_u(value)
43 2349 aaronmk
44 2499 aaronmk
    def esc_name(self, name): return esc_name(name)
45 2349 aaronmk
mockDb = MockDb()
46
47 2514 aaronmk
class BasicObject(objects.BasicObject):
48
    def __init__(self, value): self.value = value
49
50
    def __str__(self): return clean_name(strings.repr_no_u(self))
51
52
class Code(BasicObject):
53 2211 aaronmk
    def to_str(self, db): raise NotImplemented()
54 2349 aaronmk
55 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb)
56 2211 aaronmk
57 2269 aaronmk
class CustomCode(Code):
58 2256 aaronmk
    def __init__(self, str_): self.str_ = str_
59
60
    def to_str(self, db): return self.str_
61
62 2540 aaronmk
class Expr(Code):
63
    def __init__(self, expr): self.expr = expr
64
65
    def to_str(self, db): return '('+self.expr.to_str(db)+')'
66
67 2335 aaronmk
##### Literal values
68
69 2216 aaronmk
class Literal(Code):
70 2211 aaronmk
    def __init__(self, value): self.value = value
71 2213 aaronmk
72
    def to_str(self, db): return db.esc_value(self.value)
73 2211 aaronmk
74 2400 aaronmk
def as_Value(value):
75
    if isinstance(value, Code): return value
76
    else: return Literal(value)
77
78 2216 aaronmk
def is_null(value): return isinstance(value, Literal) and value.value == None
79
80 2335 aaronmk
##### Tables
81
82 2211 aaronmk
class Table(Code):
83
    def __init__(self, name, schema=None):
84
        '''
85
        @param schema str|None (for no schema)
86
        '''
87
        self.name = name
88
        self.schema = schema
89
90 2348 aaronmk
    def to_str(self, db):
91
        str_ = ''
92
        if self.schema != None: str_ += db.esc_name(self.schema)+'.'
93
        str_ += db.esc_name(self.name)
94
        return str_
95 2336 aaronmk
96
    def to_Table(self): return self
97 2211 aaronmk
98 2219 aaronmk
def as_Table(table):
99 2270 aaronmk
    if table == None or isinstance(table, Code): return table
100 2219 aaronmk
    else: return Table(table)
101
102 2336 aaronmk
class NamedTable(Table):
103
    def __init__(self, name, code, cols=None):
104
        Table.__init__(self, name)
105
106
        if not isinstance(code, Code): code = Table(code)
107
108
        self.code = code
109
        self.cols = cols
110
111
    def to_str(self, db):
112 2467 aaronmk
        str_ = self.code.to_str(db)+'\nAS '+Table.to_str(self, db)
113 2336 aaronmk
        if self.cols != None: str_ += ' ('+(', '.join(self.cols))+')'
114
        return str_
115
116
    def to_Table(self): return Table(self.name)
117
118 2335 aaronmk
##### Columns
119
120 2211 aaronmk
class Col(Code):
121
    def __init__(self, name, table=None):
122
        '''
123
        @param table Table|None (for no table)
124
        '''
125 2241 aaronmk
        if util.is_str(table): table = Table(table)
126 2211 aaronmk
        assert table == None or isinstance(table, Table)
127
128
        self.name = name
129
        self.table = table
130
131
    def to_str(self, db):
132
        str_ = ''
133
        if self.table != None: str_ += self.table.to_str(db)+'.'
134 2348 aaronmk
        str_ += db.esc_name(self.name)
135 2211 aaronmk
        return str_
136 2314 aaronmk
137
    def to_Col(self): return self
138 2211 aaronmk
139 2393 aaronmk
def is_table_col(col): return col.table != None
140
141 2563 aaronmk
def as_Col(col, table=None, name=None):
142
    '''
143
    @param name If not None, any non-Col input will be renamed using NamedCol.
144
    '''
145
    if name != None:
146
        col = as_Value(col)
147
        if not isinstance(col, Col): col = NamedCol(name, col)
148 2333 aaronmk
149
    if isinstance(col, Code): return col
150 2260 aaronmk
    else: return Col(col, table)
151
152 2401 aaronmk
def to_name_only_col(col, check_table=None):
153
    col = as_Col(col)
154 2579 aaronmk
    if not isinstance(col, Col): return col
155 2401 aaronmk
156
    if check_table != None:
157
        table = col.table
158
        assert table == None or table == check_table
159
    return Col(col.name)
160
161 2323 aaronmk
class NamedCol(Col):
162 2229 aaronmk
    def __init__(self, name, code):
163 2310 aaronmk
        Col.__init__(self, name)
164
165 2229 aaronmk
        if not isinstance(code, Code): code = Literal(code)
166
167
        self.code = code
168
169
    def to_str(self, db):
170 2310 aaronmk
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
171 2314 aaronmk
172
    def to_Col(self): return Col(self.name)
173 2229 aaronmk
174 2462 aaronmk
def remove_col_rename(col):
175
    if isinstance(col, NamedCol): col = col.code
176
    return col
177
178 2564 aaronmk
class ColDict(dict):
179
    '''A dict that automatically makes inserted entries Col objects'''
180
181
    def __setitem__(self, key, value):
182
        return dict.__setitem__(self, key, as_Col(value, name=key))
183
184
    def update(self, dict_):
185
        for key, value in dict_.iteritems(): self[key] = value
186
187 2524 aaronmk
##### Functions
188
189
class Function(Table): pass
190
191
class FunctionCall(Code):
192
    def __init__(self, function, *args):
193
        '''
194
        @param args [Code...] The function's arguments
195
        '''
196
        if not isinstance(function, Code): function = Function(function)
197 2532 aaronmk
        args = map(remove_col_rename, args)
198 2524 aaronmk
199
        self.function = function
200
        self.args = args
201
202
    def to_str(self, db):
203
        args_str = ', '.join((v.to_str(db) for v in self.args))
204
        return self.function.to_str(db)+'('+args_str+')'
205
206 2533 aaronmk
def wrap_in_func(function, value):
207
    '''Wraps a value inside a function call.
208
    Propagates any column renaming to the returned value.
209
    '''
210
    name = None
211
    if isinstance(value, NamedCol): name = value.name
212
    value = FunctionCall(function, value)
213
    if name != None: value = NamedCol(name, value)
214
    return value
215
216 2561 aaronmk
def unwrap_func_call(func_call, check_name=None):
217
    '''Unwraps any function call to its first argument.
218
    Also removes any column renaming.
219
    '''
220
    func_call = remove_col_rename(func_call)
221
    if not isinstance(func_call, FunctionCall): return func_call
222
223
    if check_name != None:
224
        name = func_call.function.name
225
        assert name == None or name == check_name
226
    return func_call.args[0]
227
228 2335 aaronmk
##### Conditions
229 2259 aaronmk
230 2398 aaronmk
class ColValueCond(Code):
231
    def __init__(self, col, value):
232
        value = as_ValueCond(value)
233
234
        self.col = col
235
        self.value = value
236
237
    def to_str(self, db): return self.value.to_str(db, self.col)
238
239 2577 aaronmk
def combine_conds(conds, keyword=None):
240
    '''
241
    @param keyword The keyword to add before the conditions, if any
242
    '''
243
    str_ = ''
244
    if keyword != None:
245
        if conds == []: whitespace = ''
246
        elif len(conds) == 1: whitespace = ' '
247
        else: whitespace = '\n'
248
        str_ += keyword+whitespace
249
250
    str_ += '\nAND '.join(conds)
251
    return str_
252
253 2398 aaronmk
##### Condition column comparisons
254
255 2514 aaronmk
class ValueCond(BasicObject):
256 2213 aaronmk
    def __init__(self, value):
257 2225 aaronmk
        if not isinstance(value, Code): value = Literal(value)
258 2462 aaronmk
        value = remove_col_rename(value)
259 2213 aaronmk
260
        self.value = value
261 2214 aaronmk
262 2216 aaronmk
    def to_str(self, db, left_value):
263 2214 aaronmk
        '''
264 2216 aaronmk
        @param left_value The Code object that the condition is being applied on
265 2214 aaronmk
        '''
266
        raise NotImplemented()
267 2228 aaronmk
268 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb, '<left_value>')
269 2211 aaronmk
270
class CompareCond(ValueCond):
271
    def __init__(self, value, operator='='):
272 2222 aaronmk
        '''
273
        @param operator By default, compares NULL values literally. Use '~=' or
274
            '~!=' to pass NULLs through.
275
        '''
276 2211 aaronmk
        ValueCond.__init__(self, value)
277
        self.operator = operator
278
279 2216 aaronmk
    def to_str(self, db, left_value):
280
        if not isinstance(left_value, Code): left_value = Col(left_value)
281 2462 aaronmk
        left_value = remove_col_rename(left_value)
282 2216 aaronmk
283 2222 aaronmk
        right_value = self.value
284
        left = left_value.to_str(db)
285
        right = right_value.to_str(db)
286
287
        # Parse operator
288 2216 aaronmk
        operator = self.operator
289 2222 aaronmk
        passthru_null_ref = [False]
290
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
291
        neg_ref = [False]
292
        operator = strings.remove_prefix('!', operator, neg_ref)
293
        equals = operator.endswith('=')
294
        if equals and is_null(self.value): operator = 'IS'
295
296
        # Create str
297
        str_ = left+' '+operator+' '+right
298
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
299 2578 aaronmk
            str_ = '('+str_+' OR ('+left+' IS NULL AND '+right+' IS NULL))'
300
        if neg_ref[0]: str_ = 'NOT '+str_
301 2222 aaronmk
        return str_
302 2216 aaronmk
303 2260 aaronmk
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
304
assume_literal = object()
305
306
def as_ValueCond(value, default_table=assume_literal):
307
    if not isinstance(value, ValueCond):
308
        if default_table is not assume_literal:
309
            value = as_Col(value, default_table)
310
        return CompareCond(value)
311 2216 aaronmk
    else: return value
312 2219 aaronmk
313 2335 aaronmk
##### Joins
314
315 2352 aaronmk
join_same = object() # tells Join the left and right columns have the same name
316 2260 aaronmk
317 2353 aaronmk
# Tells Join the left and right columns have the same name and are never NULL
318
join_same_not_null = object()
319
320 2260 aaronmk
filter_out = object() # tells Join to filter out rows that match the join
321
322 2514 aaronmk
class Join(BasicObject):
323 2260 aaronmk
    def __init__(self, table, mapping, type_=None):
324
        '''
325
        @param mapping dict(right_table_col=left_table_col, ...)
326 2352 aaronmk
            * if left_table_col is join_same: left_table_col = right_table_col
327 2353 aaronmk
              * Note that right_table_col must be a string
328
            * if left_table_col is join_same_not_null:
329
              left_table_col = right_table_col and both have NOT NULL constraint
330
              * Note that right_table_col must be a string
331 2260 aaronmk
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
332
            * filter_out: equivalent to 'LEFT' with the query filtered by
333
              `table_pkey IS NULL` (indicating no match)
334
        '''
335
        if util.is_str(table): table = Table(table)
336
        assert type_ == None or util.is_str(type_) or type_ is filter_out
337
338
        self.table = table
339
        self.mapping = mapping
340
        self.type_ = type_
341
342
    def to_str(self, db, left_table):
343 2353 aaronmk
        # Switch order (left_table is on the right in the comparison)
344
        right_table = left_table
345
        left_table = self.table # note left_table is reassigned
346
347 2260 aaronmk
        def join(entry):
348
            '''Parses non-USING joins'''
349
            right_table_col, left_table_col = entry
350
351 2353 aaronmk
            # Switch order (right_table_col is on the left in the comparison)
352
            left = right_table_col
353
            right = left_table_col
354
355 2260 aaronmk
            # Parse special values
356 2353 aaronmk
            if right is join_same: right = left
357
            elif right is join_same_not_null:
358
                right = CompareCond(as_Col(left, right_table), '~=')
359 2260 aaronmk
360 2353 aaronmk
            right = as_ValueCond(right, right_table)
361 2578 aaronmk
            return right.to_str(db, as_Col(left, left_table))
362 2260 aaronmk
363 2265 aaronmk
        # Create join condition
364
        type_ = self.type_
365 2276 aaronmk
        joins = self.mapping
366 2265 aaronmk
        if type_ is not filter_out and reduce(operator.and_,
367 2460 aaronmk
            (v is join_same_not_null for v in joins.itervalues())):
368 2260 aaronmk
            # all cols w/ USING, so can use simpler USING syntax
369 2298 aaronmk
            cols = (as_Col(v).to_str(db) for v in joins.iterkeys())
370
            join_cond = 'USING ('+(', '.join(cols))+')'
371 2576 aaronmk
        else:
372
            if len(joins) == 1: whitespace = ' '
373
            else: whitespace = '\n'
374 2577 aaronmk
            join_cond = combine_conds(map(join, joins.iteritems()), 'ON')
375 2260 aaronmk
376
        # Create join
377
        if type_ is filter_out: type_ = 'LEFT'
378 2266 aaronmk
        str_ = ''
379
        if type_ != None: str_ += type_+' '
380 2353 aaronmk
        str_ += 'JOIN '+left_table.to_str(db)+' '+join_cond
381 2266 aaronmk
        return str_
382 2349 aaronmk
383 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb, '<left_table>')
384 2424 aaronmk
385
##### Value exprs
386
387
row_count = CustomCode('count(*)')