Project

General

Profile

1 2211 aaronmk
# SQL code generation
2
3 2748 aaronmk
import copy
4 2276 aaronmk
import operator
5 2568 aaronmk
import re
6 2653 aaronmk
import UserDict
7 2276 aaronmk
8 2667 aaronmk
import dicts
9 2701 aaronmk
import iters
10
import lists
11 2360 aaronmk
import objects
12 2222 aaronmk
import strings
13 2227 aaronmk
import util
14 2211 aaronmk
15 2587 aaronmk
##### Names
16 2499 aaronmk
17 2608 aaronmk
identifier_max_len = 63 # works for both PostgreSQL and MySQL
18 2587 aaronmk
19
def add_suffix(str_, suffix):
20 2609 aaronmk
    '''Preserves version so that it won't be truncated off the string, leading
21
    to collisions.'''
22 2613 aaronmk
    # Preserve version
23
    before, sep, version = str_.rpartition('#')
24
    if sep != '': # found match
25
        str_ = before
26 2609 aaronmk
        suffix = sep+version+suffix
27 2613 aaronmk
28 2587 aaronmk
    return strings.add_suffix(str_, suffix, identifier_max_len)
29
30 2575 aaronmk
def is_safe_name(name):
31 2583 aaronmk
    '''A name is safe *and unambiguous* if it:
32
    * contains only *lowercase* word (\w) characters
33
    * doesn't start with a digit
34
    * contains "_", so that it's not a keyword
35
    '''
36
    return re.match(r'^(?=.*_)(?!\d)[^\WA-Z]+$', name)
37 2568 aaronmk
38 2499 aaronmk
def esc_name(name, quote='"'):
39
    return quote + name.replace(quote, quote+quote) + quote
40
        # doubling an embedded quote escapes it in both PostgreSQL and MySQL
41
42 2513 aaronmk
def clean_name(name): return name.replace('"', '').replace('`', '')
43
44 2659 aaronmk
##### General SQL code objects
45 2219 aaronmk
46 2349 aaronmk
class MockDb:
47 2503 aaronmk
    def esc_value(self, value): return strings.repr_no_u(value)
48 2349 aaronmk
49 2499 aaronmk
    def esc_name(self, name): return esc_name(name)
50 2349 aaronmk
mockDb = MockDb()
51
52 2514 aaronmk
class BasicObject(objects.BasicObject):
53
    def __init__(self, value): self.value = value
54
55
    def __str__(self): return clean_name(strings.repr_no_u(self))
56
57 2659 aaronmk
##### Unparameterized code objects
58
59 2514 aaronmk
class Code(BasicObject):
60 2658 aaronmk
    def to_str(self, db): raise NotImplementedError()
61 2349 aaronmk
62 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb)
63 2211 aaronmk
64 2269 aaronmk
class CustomCode(Code):
65 2256 aaronmk
    def __init__(self, str_): self.str_ = str_
66
67
    def to_str(self, db): return self.str_
68
69 2659 aaronmk
def as_Code(value):
70
    if util.is_str(value): return CustomCode(value)
71
    else: return Literal(value)
72
73 2540 aaronmk
class Expr(Code):
74
    def __init__(self, expr): self.expr = expr
75
76
    def to_str(self, db): return '('+self.expr.to_str(db)+')'
77
78 2335 aaronmk
##### Literal values
79
80 2216 aaronmk
class Literal(Code):
81 2211 aaronmk
    def __init__(self, value): self.value = value
82 2213 aaronmk
83
    def to_str(self, db): return db.esc_value(self.value)
84 2211 aaronmk
85 2400 aaronmk
def as_Value(value):
86
    if isinstance(value, Code): return value
87
    else: return Literal(value)
88
89 2216 aaronmk
def is_null(value): return isinstance(value, Literal) and value.value == None
90
91 2711 aaronmk
##### Derived elements
92
93
src_self = object() # tells Col that it is its own source column
94
95
class Derived(Code):
96
    def __init__(self, srcs):
97 2712 aaronmk
        '''An element which was derived from some other element(s).
98 2711 aaronmk
        @param srcs See self.set_srcs()
99
        '''
100
        self.set_srcs(srcs)
101
102 2713 aaronmk
    def set_srcs(self, srcs, overwrite=True):
103 2711 aaronmk
        '''
104
        @param srcs (self_type...)|src_self The element(s) this is derived from
105
        '''
106 2713 aaronmk
        if not overwrite and self.srcs != (): return # already set
107
108 2711 aaronmk
        if srcs == src_self: srcs = (self,)
109
        srcs = tuple(srcs) # make Col hashable
110
        self.srcs = srcs
111
112
    def _compare_on(self):
113
        compare_on = self.__dict__.copy()
114
        del compare_on['srcs'] # ignore
115
        return compare_on
116
117
def cols_srcs(cols): return lists.uniqify(iters.flatten((v.srcs for v in cols)))
118
119 2335 aaronmk
##### Tables
120
121 2712 aaronmk
class Table(Derived):
122
    def __init__(self, name, schema=None, srcs=()):
123 2211 aaronmk
        '''
124
        @param schema str|None (for no schema)
125 2712 aaronmk
        @param srcs (Table...)|src_self See Derived.set_srcs()
126 2211 aaronmk
        '''
127 2712 aaronmk
        Derived.__init__(self, srcs)
128
129 2211 aaronmk
        self.name = name
130
        self.schema = schema
131
132 2348 aaronmk
    def to_str(self, db):
133
        str_ = ''
134
        if self.schema != None: str_ += db.esc_name(self.schema)+'.'
135
        str_ += db.esc_name(self.name)
136
        return str_
137 2336 aaronmk
138
    def to_Table(self): return self
139 2211 aaronmk
140 2219 aaronmk
def as_Table(table):
141 2270 aaronmk
    if table == None or isinstance(table, Code): return table
142 2219 aaronmk
    else: return Table(table)
143
144 2707 aaronmk
def suffixed_table(table, suffix): return Table(table.name+suffix, table.schema)
145
146 2336 aaronmk
class NamedTable(Table):
147
    def __init__(self, name, code, cols=None):
148
        Table.__init__(self, name)
149
150
        if not isinstance(code, Code): code = Table(code)
151 2741 aaronmk
        if not isinstance(code, (Table, FunctionCall, Expr)): code = Expr(code)
152 2742 aaronmk
        if cols != None: cols = map(to_name_only_col, cols)
153 2336 aaronmk
154
        self.code = code
155
        self.cols = cols
156
157
    def to_str(self, db):
158 2467 aaronmk
        str_ = self.code.to_str(db)+'\nAS '+Table.to_str(self, db)
159 2742 aaronmk
        if self.cols != None:
160
            str_ += ' ('+(', '.join((c.to_str(db) for c in self.cols)))+')'
161 2336 aaronmk
        return str_
162
163
    def to_Table(self): return Table(self.name)
164
165 2335 aaronmk
##### Columns
166
167 2711 aaronmk
class Col(Derived):
168 2701 aaronmk
    def __init__(self, name, table=None, srcs=()):
169 2211 aaronmk
        '''
170
        @param table Table|None (for no table)
171 2711 aaronmk
        @param srcs (Col...)|src_self See Derived.set_srcs()
172 2211 aaronmk
        '''
173 2711 aaronmk
        Derived.__init__(self, srcs)
174
175 2241 aaronmk
        if util.is_str(table): table = Table(table)
176 2211 aaronmk
        assert table == None or isinstance(table, Table)
177
178
        self.name = name
179
        self.table = table
180
181
    def to_str(self, db):
182
        str_ = ''
183
        if self.table != None: str_ += self.table.to_str(db)+'.'
184 2348 aaronmk
        str_ += db.esc_name(self.name)
185 2211 aaronmk
        return str_
186 2314 aaronmk
187
    def to_Col(self): return self
188 2211 aaronmk
189 2393 aaronmk
def is_table_col(col): return col.table != None
190
191 2563 aaronmk
def as_Col(col, table=None, name=None):
192
    '''
193
    @param name If not None, any non-Col input will be renamed using NamedCol.
194
    '''
195
    if name != None:
196
        col = as_Value(col)
197
        if not isinstance(col, Col): col = NamedCol(name, col)
198 2333 aaronmk
199
    if isinstance(col, Code): return col
200 2260 aaronmk
    else: return Col(col, table)
201
202 2750 aaronmk
def with_default_table(col, table, overwrite=False):
203 2747 aaronmk
    col = as_Col(col)
204 2750 aaronmk
    if not isinstance(col, NamedCol) and (overwrite or col.table == None):
205 2748 aaronmk
        col = copy.copy(col) # don't modify input!
206
        col.table = table
207 2747 aaronmk
    return col
208
209 2744 aaronmk
def set_cols_table(table, cols):
210
    table = as_Table(table)
211
212
    for i, col in enumerate(cols):
213
        col = cols[i] = as_Col(col)
214
        col.table = table
215
216 2401 aaronmk
def to_name_only_col(col, check_table=None):
217
    col = as_Col(col)
218 2579 aaronmk
    if not isinstance(col, Col): return col
219 2401 aaronmk
220
    if check_table != None:
221
        table = col.table
222
        assert table == None or table == check_table
223
    return Col(col.name)
224
225 2323 aaronmk
class NamedCol(Col):
226 2229 aaronmk
    def __init__(self, name, code):
227 2310 aaronmk
        Col.__init__(self, name)
228
229 2229 aaronmk
        if not isinstance(code, Code): code = Literal(code)
230
231
        self.code = code
232
233
    def to_str(self, db):
234 2310 aaronmk
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
235 2314 aaronmk
236
    def to_Col(self): return Col(self.name)
237 2229 aaronmk
238 2462 aaronmk
def remove_col_rename(col):
239
    if isinstance(col, NamedCol): col = col.code
240
    return col
241
242 2703 aaronmk
def wrap(wrap_func, value):
243
    '''Wraps a value, propagating any column renaming to the returned value.'''
244
    if isinstance(value, NamedCol):
245
        return NamedCol(value.name, wrap_func(value.code))
246
    else: return wrap_func(value)
247
248 2667 aaronmk
class ColDict(dicts.DictProxy):
249 2564 aaronmk
    '''A dict that automatically makes inserted entries Col objects'''
250
251 2645 aaronmk
    def __init__(self, db, keys_table, dict_={}):
252 2667 aaronmk
        dicts.DictProxy.__init__(self, {})
253
254 2645 aaronmk
        keys_table = as_Table(keys_table)
255
256 2642 aaronmk
        self.db = db
257 2641 aaronmk
        self.table = keys_table
258 2653 aaronmk
        self.update(dict_) # after setting vars because __setitem__() needs them
259 2641 aaronmk
260 2667 aaronmk
    def copy(self): return ColDict(self.db, self.table, self.inner.copy())
261 2655 aaronmk
262 2667 aaronmk
    def __getitem__(self, key):
263
        return dicts.DictProxy.__getitem__(self, self._key(key))
264 2653 aaronmk
265 2564 aaronmk
    def __setitem__(self, key, value):
266 2642 aaronmk
        key = self._key(key)
267 2661 aaronmk
        if value == None: value = self.db.col_default(key)
268 2667 aaronmk
        dicts.DictProxy.__setitem__(self, key, as_Col(value, name=key.name))
269 2564 aaronmk
270 2641 aaronmk
    def _key(self, key): return as_Col(key, self.table)
271 2564 aaronmk
272 2524 aaronmk
##### Functions
273
274
class Function(Table): pass
275
276 2697 aaronmk
def TempFunction(name, autocommit):
277
    schema = None
278
    if not autocommit: schema = 'pg_temp'
279
    return Function(name, schema)
280
281 2691 aaronmk
class InternalFunction(CustomCode): pass
282
283 2524 aaronmk
class FunctionCall(Code):
284
    def __init__(self, function, *args):
285
        '''
286 2690 aaronmk
        @param args [Code|literal-value...] The function's arguments
287 2524 aaronmk
        '''
288
        if not isinstance(function, Code): function = Function(function)
289 2690 aaronmk
        args = map(remove_col_rename, map(as_Value, args))
290 2524 aaronmk
291
        self.function = function
292
        self.args = args
293
294
    def to_str(self, db):
295
        args_str = ', '.join((v.to_str(db) for v in self.args))
296
        return self.function.to_str(db)+'('+args_str+')'
297
298 2533 aaronmk
def wrap_in_func(function, value):
299
    '''Wraps a value inside a function call.
300
    Propagates any column renaming to the returned value.
301
    '''
302 2703 aaronmk
    return wrap(lambda v: FunctionCall(function, v), value)
303 2533 aaronmk
304 2561 aaronmk
def unwrap_func_call(func_call, check_name=None):
305
    '''Unwraps any function call to its first argument.
306
    Also removes any column renaming.
307
    '''
308
    func_call = remove_col_rename(func_call)
309
    if not isinstance(func_call, FunctionCall): return func_call
310
311
    if check_name != None:
312
        name = func_call.function.name
313
        assert name == None or name == check_name
314
    return func_call.args[0]
315
316 2335 aaronmk
##### Conditions
317 2259 aaronmk
318 2398 aaronmk
class ColValueCond(Code):
319
    def __init__(self, col, value):
320
        value = as_ValueCond(value)
321
322
        self.col = col
323
        self.value = value
324
325
    def to_str(self, db): return self.value.to_str(db, self.col)
326
327 2577 aaronmk
def combine_conds(conds, keyword=None):
328
    '''
329
    @param keyword The keyword to add before the conditions, if any
330
    '''
331
    str_ = ''
332
    if keyword != None:
333
        if conds == []: whitespace = ''
334
        elif len(conds) == 1: whitespace = ' '
335
        else: whitespace = '\n'
336
        str_ += keyword+whitespace
337
338
    str_ += '\nAND '.join(conds)
339
    return str_
340
341 2398 aaronmk
##### Condition column comparisons
342
343 2514 aaronmk
class ValueCond(BasicObject):
344 2213 aaronmk
    def __init__(self, value):
345 2225 aaronmk
        if not isinstance(value, Code): value = Literal(value)
346 2462 aaronmk
        value = remove_col_rename(value)
347 2213 aaronmk
348
        self.value = value
349 2214 aaronmk
350 2216 aaronmk
    def to_str(self, db, left_value):
351 2214 aaronmk
        '''
352 2216 aaronmk
        @param left_value The Code object that the condition is being applied on
353 2214 aaronmk
        '''
354
        raise NotImplemented()
355 2228 aaronmk
356 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb, '<left_value>')
357 2211 aaronmk
358
class CompareCond(ValueCond):
359
    def __init__(self, value, operator='='):
360 2222 aaronmk
        '''
361
        @param operator By default, compares NULL values literally. Use '~=' or
362
            '~!=' to pass NULLs through.
363
        '''
364 2211 aaronmk
        ValueCond.__init__(self, value)
365
        self.operator = operator
366
367 2216 aaronmk
    def to_str(self, db, left_value):
368
        if not isinstance(left_value, Code): left_value = Col(left_value)
369 2462 aaronmk
        left_value = remove_col_rename(left_value)
370 2216 aaronmk
371 2222 aaronmk
        right_value = self.value
372
        left = left_value.to_str(db)
373
        right = right_value.to_str(db)
374
375
        # Parse operator
376 2216 aaronmk
        operator = self.operator
377 2222 aaronmk
        passthru_null_ref = [False]
378
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
379
        neg_ref = [False]
380
        operator = strings.remove_prefix('!', operator, neg_ref)
381
        equals = operator.endswith('=')
382
        if equals and is_null(self.value): operator = 'IS'
383
384
        # Create str
385
        str_ = left+' '+operator+' '+right
386
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
387 2578 aaronmk
            str_ = '('+str_+' OR ('+left+' IS NULL AND '+right+' IS NULL))'
388
        if neg_ref[0]: str_ = 'NOT '+str_
389 2222 aaronmk
        return str_
390 2216 aaronmk
391 2260 aaronmk
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
392
assume_literal = object()
393
394
def as_ValueCond(value, default_table=assume_literal):
395
    if not isinstance(value, ValueCond):
396
        if default_table is not assume_literal:
397 2748 aaronmk
            value = with_default_table(value, default_table)
398 2260 aaronmk
        return CompareCond(value)
399 2216 aaronmk
    else: return value
400 2219 aaronmk
401 2335 aaronmk
##### Joins
402
403 2352 aaronmk
join_same = object() # tells Join the left and right columns have the same name
404 2260 aaronmk
405 2353 aaronmk
# Tells Join the left and right columns have the same name and are never NULL
406
join_same_not_null = object()
407
408 2260 aaronmk
filter_out = object() # tells Join to filter out rows that match the join
409
410 2514 aaronmk
class Join(BasicObject):
411 2746 aaronmk
    def __init__(self, table, mapping={}, type_=None):
412 2260 aaronmk
        '''
413
        @param mapping dict(right_table_col=left_table_col, ...)
414 2352 aaronmk
            * if left_table_col is join_same: left_table_col = right_table_col
415 2353 aaronmk
              * Note that right_table_col must be a string
416
            * if left_table_col is join_same_not_null:
417
              left_table_col = right_table_col and both have NOT NULL constraint
418
              * Note that right_table_col must be a string
419 2260 aaronmk
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
420
            * filter_out: equivalent to 'LEFT' with the query filtered by
421
              `table_pkey IS NULL` (indicating no match)
422
        '''
423
        if util.is_str(table): table = Table(table)
424
        assert type_ == None or util.is_str(type_) or type_ is filter_out
425
426
        self.table = table
427
        self.mapping = mapping
428
        self.type_ = type_
429
430 2749 aaronmk
    def to_str(self, db, left_table_):
431 2260 aaronmk
        def join(entry):
432
            '''Parses non-USING joins'''
433
            right_table_col, left_table_col = entry
434
435 2353 aaronmk
            # Switch order (right_table_col is on the left in the comparison)
436
            left = right_table_col
437
            right = left_table_col
438 2749 aaronmk
            left_table = self.table
439
            right_table = left_table_
440 2353 aaronmk
441 2749 aaronmk
            left_table = left_table.to_Table()
442
            right_table = right_table.to_Table()
443
444 2747 aaronmk
            # Parse left side
445 2748 aaronmk
            left = with_default_table(left, left_table)
446 2747 aaronmk
447 2260 aaronmk
            # Parse special values
448 2747 aaronmk
            left_on_right = Col(left.name, right_table)
449
            if right is join_same: right = left_on_right
450 2353 aaronmk
            elif right is join_same_not_null:
451 2747 aaronmk
                right = CompareCond(left_on_right, '~=')
452 2260 aaronmk
453 2747 aaronmk
            # Parse right side
454 2353 aaronmk
            right = as_ValueCond(right, right_table)
455 2747 aaronmk
456
            return right.to_str(db, left)
457 2260 aaronmk
458 2265 aaronmk
        # Create join condition
459
        type_ = self.type_
460 2276 aaronmk
        joins = self.mapping
461 2746 aaronmk
        if joins == {}: join_cond = None
462
        elif type_ is not filter_out and reduce(operator.and_,
463 2460 aaronmk
            (v is join_same_not_null for v in joins.itervalues())):
464 2260 aaronmk
            # all cols w/ USING, so can use simpler USING syntax
465 2747 aaronmk
            cols = map(to_name_only_col, joins.iterkeys())
466
            join_cond = 'USING ('+(', '.join((c.to_str(db) for c in cols)))+')'
467 2576 aaronmk
        else:
468
            if len(joins) == 1: whitespace = ' '
469
            else: whitespace = '\n'
470 2577 aaronmk
            join_cond = combine_conds(map(join, joins.iteritems()), 'ON')
471 2260 aaronmk
472
        # Create join
473
        if type_ is filter_out: type_ = 'LEFT'
474 2266 aaronmk
        str_ = ''
475
        if type_ != None: str_ += type_+' '
476 2749 aaronmk
        str_ += 'JOIN '+self.table.to_str(db)
477 2746 aaronmk
        if join_cond != None: str_ += ' '+join_cond
478 2266 aaronmk
        return str_
479 2349 aaronmk
480 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb, '<left_table>')
481 2424 aaronmk
482
##### Value exprs
483
484 2737 aaronmk
default = CustomCode('DEFAULT')
485
486 2424 aaronmk
row_count = CustomCode('count(*)')
487 2674 aaronmk
488 2692 aaronmk
def EnsureNotNull(value, null=r'\N'):
489 2694 aaronmk
    return FunctionCall(InternalFunction('coalesce'), as_Col(value), null)
490 2692 aaronmk
491 2737 aaronmk
##### Table exprs
492
493
class Values(Code):
494
    def __init__(self, values):
495 2739 aaronmk
        '''
496
        @param values [...]|[[...], ...] Can be one or multiple rows.
497
        '''
498
        rows = values
499
        if len(values) >= 1 and not lists.is_seq(values[0]): # only one row
500
            rows = [values]
501
        for i, row in enumerate(rows):
502
            rows[i] = map(remove_col_rename, map(as_Value, row))
503 2737 aaronmk
504 2739 aaronmk
        self.rows = rows
505 2737 aaronmk
506
    def to_str(self, db):
507 2739 aaronmk
        def row_str(row):
508
            return '('+(', '.join((v.to_str(db) for v in row)))+')'
509
        return 'VALUES '+(', '.join(map(row_str, self.rows)))
510 2737 aaronmk
511 2740 aaronmk
def NamedValues(name, cols, values):
512 2745 aaronmk
    '''
513
    @post `cols` will be changed to Col objects with the table set to `name`.
514
    '''
515
    set_cols_table(name, cols)
516 2740 aaronmk
    return NamedTable(name, Values(values), cols)
517
518 2674 aaronmk
##### Database structure
519
520
class TypedCol(Col):
521
    def __init__(self, name, type_):
522
        Col.__init__(self, name)
523
524
        self.type = type_
525
526
    def to_str(self, db): return Col.to_str(self, db)+' '+self.type
527
528
    def to_Col(self): return Col(self.name)