Project

General

Profile

1
# SQL code generation
2

    
3
import copy
4
import operator
5
import re
6
import UserDict
7

    
8
import dicts
9
import iters
10
import lists
11
import objects
12
import strings
13
import util
14

    
15
##### Names
16

    
17
identifier_max_len = 63 # works for both PostgreSQL and MySQL
18

    
19
def add_suffix(str_, suffix):
20
    '''Preserves version so that it won't be truncated off the string, leading
21
    to collisions.'''
22
    # Preserve version
23
    before, sep, version = str_.rpartition('#')
24
    if sep != '': # found match
25
        str_ = before
26
        suffix = sep+version+suffix
27
    
28
    return strings.add_suffix(str_, suffix, identifier_max_len)
29

    
30
def is_safe_name(name):
31
    '''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

    
38
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
def clean_name(name): return name.replace('"', '').replace('`', '')
43

    
44
##### General SQL code objects
45

    
46
class MockDb:
47
    def esc_value(self, value): return strings.repr_no_u(value)
48
    
49
    def esc_name(self, name): return esc_name(name)
50
mockDb = MockDb()
51

    
52
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
##### Unparameterized code objects
58

    
59
class Code(BasicObject):
60
    def to_str(self, db): raise NotImplementedError()
61
    
62
    def __repr__(self): return self.to_str(mockDb)
63

    
64
class CustomCode(Code):
65
    def __init__(self, str_): self.str_ = str_
66
    
67
    def to_str(self, db): return self.str_
68

    
69
def as_Code(value):
70
    if util.is_str(value): return CustomCode(value)
71
    else: return Literal(value)
72

    
73
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
##### Literal values
79

    
80
class Literal(Code):
81
    def __init__(self, value): self.value = value
82
    
83
    def to_str(self, db): return db.esc_value(self.value)
84

    
85
def as_Value(value):
86
    if isinstance(value, Code): return value
87
    else: return Literal(value)
88

    
89
def is_null(value): return isinstance(value, Literal) and value.value == None
90

    
91
##### 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
        '''An element which was derived from some other element(s).
98
        @param srcs See self.set_srcs()
99
        '''
100
        self.set_srcs(srcs)
101
    
102
    def set_srcs(self, srcs, overwrite=True):
103
        '''
104
        @param srcs (self_type...)|src_self The element(s) this is derived from
105
        '''
106
        if not overwrite and self.srcs != (): return # already set
107
        
108
        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
##### Tables
120

    
121
class Table(Derived):
122
    def __init__(self, name, schema=None, srcs=()):
123
        '''
124
        @param schema str|None (for no schema)
125
        @param srcs (Table...)|src_self See Derived.set_srcs()
126
        '''
127
        Derived.__init__(self, srcs)
128
        
129
        self.name = name
130
        self.schema = schema
131
    
132
    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
    
138
    def to_Table(self): return self
139

    
140
def as_Table(table):
141
    if table == None or isinstance(table, Code): return table
142
    else: return Table(table)
143

    
144
def suffixed_table(table, suffix): return Table(table.name+suffix, table.schema)
145

    
146
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
        if not isinstance(code, (Table, FunctionCall, Expr)): code = Expr(code)
152
        if cols != None: cols = map(to_name_only_col, cols)
153
        
154
        self.code = code
155
        self.cols = cols
156
    
157
    def to_str(self, db):
158
        str_ = self.code.to_str(db)+'\nAS '+Table.to_str(self, db)
159
        if self.cols != None:
160
            str_ += ' ('+(', '.join((c.to_str(db) for c in self.cols)))+')'
161
        return str_
162
    
163
    def to_Table(self): return Table(self.name)
164

    
165
##### Columns
166

    
167
class Col(Derived):
168
    def __init__(self, name, table=None, srcs=()):
169
        '''
170
        @param table Table|None (for no table)
171
        @param srcs (Col...)|src_self See Derived.set_srcs()
172
        '''
173
        Derived.__init__(self, srcs)
174
        
175
        if util.is_str(table): table = Table(table)
176
        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
        str_ += db.esc_name(self.name)
185
        return str_
186
    
187
    def to_Col(self): return self
188

    
189
def is_table_col(col): return col.table != None
190

    
191
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
    
199
    if isinstance(col, Code): return col
200
    else: return Col(col, table)
201

    
202
def with_default_table(col, table, overwrite=False):
203
    col = as_Col(col)
204
    if not isinstance(col, NamedCol) and (overwrite or col.table == None):
205
        col = copy.copy(col) # don't modify input!
206
        col.table = table
207
    return col
208

    
209
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
def to_name_only_col(col, check_table=None):
217
    col = as_Col(col)
218
    if not isinstance(col, Col): return col
219
    
220
    if check_table != None:
221
        table = col.table
222
        assert table == None or table == check_table
223
    return Col(col.name)
224

    
225
class NamedCol(Col):
226
    def __init__(self, name, code):
227
        Col.__init__(self, name)
228
        
229
        if not isinstance(code, Code): code = Literal(code)
230
        
231
        self.code = code
232
    
233
    def to_str(self, db):
234
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
235
    
236
    def to_Col(self): return Col(self.name)
237

    
238
def remove_col_rename(col):
239
    if isinstance(col, NamedCol): col = col.code
240
    return col
241

    
242
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
class ColDict(dicts.DictProxy):
249
    '''A dict that automatically makes inserted entries Col objects'''
250
    
251
    def __init__(self, db, keys_table, dict_={}):
252
        dicts.DictProxy.__init__(self, {})
253
        
254
        keys_table = as_Table(keys_table)
255
        
256
        self.db = db
257
        self.table = keys_table
258
        self.update(dict_) # after setting vars because __setitem__() needs them
259
    
260
    def copy(self): return ColDict(self.db, self.table, self.inner.copy())
261
    
262
    def __getitem__(self, key):
263
        return dicts.DictProxy.__getitem__(self, self._key(key))
264
    
265
    def __setitem__(self, key, value):
266
        key = self._key(key)
267
        if value == None: value = self.db.col_default(key)
268
        dicts.DictProxy.__setitem__(self, key, as_Col(value, name=key.name))
269
    
270
    def _key(self, key): return as_Col(key, self.table)
271

    
272
##### Functions
273

    
274
class Function(Table): pass
275

    
276
def TempFunction(name, autocommit):
277
    schema = None
278
    if not autocommit: schema = 'pg_temp'
279
    return Function(name, schema)
280

    
281
class InternalFunction(CustomCode): pass
282

    
283
class FunctionCall(Code):
284
    def __init__(self, function, *args):
285
        '''
286
        @param args [Code|literal-value...] The function's arguments
287
        '''
288
        if not isinstance(function, Code): function = Function(function)
289
        args = map(remove_col_rename, map(as_Value, args))
290
        
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
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
    return wrap(lambda v: FunctionCall(function, v), value)
303

    
304
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
##### Conditions
317

    
318
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
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
##### Condition column comparisons
342

    
343
class ValueCond(BasicObject):
344
    def __init__(self, value):
345
        if not isinstance(value, Code): value = Literal(value)
346
        value = remove_col_rename(value)
347
        
348
        self.value = value
349
    
350
    def to_str(self, db, left_value):
351
        '''
352
        @param left_value The Code object that the condition is being applied on
353
        '''
354
        raise NotImplemented()
355
    
356
    def __repr__(self): return self.to_str(mockDb, '<left_value>')
357

    
358
class CompareCond(ValueCond):
359
    def __init__(self, value, operator='='):
360
        '''
361
        @param operator By default, compares NULL values literally. Use '~=' or
362
            '~!=' to pass NULLs through.
363
        '''
364
        ValueCond.__init__(self, value)
365
        self.operator = operator
366
    
367
    def to_str(self, db, left_value):
368
        if not isinstance(left_value, Code): left_value = Col(left_value)
369
        left_value = remove_col_rename(left_value)
370
        
371
        right_value = self.value
372
        left = left_value.to_str(db)
373
        right = right_value.to_str(db)
374
        
375
        # Parse operator
376
        operator = self.operator
377
        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
            str_ = '('+str_+' OR ('+left+' IS NULL AND '+right+' IS NULL))'
388
        if neg_ref[0]: str_ = 'NOT '+str_
389
        return str_
390

    
391
# 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
            value = with_default_table(value, default_table)
398
        return CompareCond(value)
399
    else: return value
400

    
401
##### Joins
402

    
403
join_same = object() # tells Join the left and right columns have the same name
404

    
405
# Tells Join the left and right columns have the same name and are never NULL
406
join_same_not_null = object()
407

    
408
filter_out = object() # tells Join to filter out rows that match the join
409

    
410
class Join(BasicObject):
411
    def __init__(self, table, mapping={}, type_=None):
412
        '''
413
        @param mapping dict(right_table_col=left_table_col, ...)
414
            * if left_table_col is join_same: left_table_col = right_table_col
415
              * 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
        @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
    def to_str(self, db, left_table_):
431
        def join(entry):
432
            '''Parses non-USING joins'''
433
            right_table_col, left_table_col = entry
434
            
435
            # Switch order (right_table_col is on the left in the comparison)
436
            left = right_table_col
437
            right = left_table_col
438
            left_table = self.table
439
            right_table = left_table_
440
            
441
            left_table = left_table.to_Table()
442
            right_table = right_table.to_Table()
443
            
444
            # Parse left side
445
            left = with_default_table(left, left_table)
446
            
447
            # Parse special values
448
            left_on_right = Col(left.name, right_table)
449
            if right is join_same: right = left_on_right
450
            elif right is join_same_not_null:
451
                right = CompareCond(left_on_right, '~=')
452
            
453
            # Parse right side
454
            right = as_ValueCond(right, right_table)
455
            
456
            return right.to_str(db, left)
457
        
458
        # Create join condition
459
        type_ = self.type_
460
        joins = self.mapping
461
        if joins == {}: join_cond = None
462
        elif type_ is not filter_out and reduce(operator.and_,
463
            (v is join_same_not_null for v in joins.itervalues())):
464
            # all cols w/ USING, so can use simpler USING syntax
465
            cols = map(to_name_only_col, joins.iterkeys())
466
            join_cond = 'USING ('+(', '.join((c.to_str(db) for c in cols)))+')'
467
        else:
468
            if len(joins) == 1: whitespace = ' '
469
            else: whitespace = '\n'
470
            join_cond = combine_conds(map(join, joins.iteritems()), 'ON')
471
        
472
        # Create join
473
        if type_ is filter_out: type_ = 'LEFT'
474
        str_ = ''
475
        if type_ != None: str_ += type_+' '
476
        str_ += 'JOIN '+self.table.to_str(db)
477
        if join_cond != None: str_ += ' '+join_cond
478
        return str_
479
    
480
    def __repr__(self): return self.to_str(mockDb, '<left_table>')
481

    
482
##### Value exprs
483

    
484
default = CustomCode('DEFAULT')
485

    
486
row_count = CustomCode('count(*)')
487

    
488
def EnsureNotNull(value, null=r'\N'):
489
    return FunctionCall(InternalFunction('coalesce'), as_Col(value), null)
490

    
491
##### Table exprs
492

    
493
class Values(Code):
494
    def __init__(self, values):
495
        '''
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
        
504
        self.rows = rows
505
    
506
    def to_str(self, db):
507
        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

    
511
def NamedValues(name, cols, values):
512
    '''
513
    @post `cols` will be changed to Col objects with the table set to `name`.
514
    '''
515
    set_cols_table(name, cols)
516
    return NamedTable(name, Values(values), cols)
517

    
518
##### 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)
(25-25/36)