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
def remove_table_rename(table):
166
    if isinstance(table, NamedTable): table = table.code
167
    return table
168

    
169
##### Columns
170

    
171
class Col(Derived):
172
    def __init__(self, name, table=None, srcs=()):
173
        '''
174
        @param table Table|None (for no table)
175
        @param srcs (Col...)|src_self See Derived.set_srcs()
176
        '''
177
        Derived.__init__(self, srcs)
178
        
179
        if util.is_str(table): table = Table(table)
180
        assert table == None or isinstance(table, Table)
181
        if table != None: table = table.to_Table()
182
        
183
        self.name = name
184
        self.table = table
185
    
186
    def to_str(self, db):
187
        str_ = ''
188
        if self.table != None: str_ += self.table.to_str(db)+'.'
189
        str_ += db.esc_name(self.name)
190
        return str_
191
    
192
    def to_Col(self): return self
193

    
194
def is_table_col(col): return col.table != None
195

    
196
def as_Col(col, table=None, name=None):
197
    '''
198
    @param name If not None, any non-Col input will be renamed using NamedCol.
199
    '''
200
    if name != None:
201
        col = as_Value(col)
202
        if not isinstance(col, Col): col = NamedCol(name, col)
203
    
204
    if isinstance(col, Code): return col
205
    else: return Col(col, table)
206

    
207
def with_default_table(col, table, overwrite=False):
208
    col = as_Col(col)
209
    if not isinstance(col, NamedCol) and (overwrite or col.table == None):
210
        col = copy.copy(col) # don't modify input!
211
        col.table = table
212
    return col
213

    
214
def set_cols_table(table, cols):
215
    table = as_Table(table)
216
    
217
    for i, col in enumerate(cols):
218
        col = cols[i] = as_Col(col)
219
        col.table = table
220

    
221
def to_name_only_col(col, check_table=None):
222
    col = as_Col(col)
223
    if not isinstance(col, Col): return col
224
    
225
    if check_table != None:
226
        table = col.table
227
        assert table == None or table == check_table
228
    return Col(col.name)
229

    
230
class NamedCol(Col):
231
    def __init__(self, name, code):
232
        Col.__init__(self, name)
233
        
234
        if not isinstance(code, Code): code = Literal(code)
235
        
236
        self.code = code
237
    
238
    def to_str(self, db):
239
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
240
    
241
    def to_Col(self): return Col(self.name)
242

    
243
def remove_col_rename(col):
244
    if isinstance(col, NamedCol): col = col.code
245
    return col
246

    
247
def wrap(wrap_func, value):
248
    '''Wraps a value, propagating any column renaming to the returned value.'''
249
    if isinstance(value, NamedCol):
250
        return NamedCol(value.name, wrap_func(value.code))
251
    else: return wrap_func(value)
252

    
253
class ColDict(dicts.DictProxy):
254
    '''A dict that automatically makes inserted entries Col objects'''
255
    
256
    def __init__(self, db, keys_table, dict_={}):
257
        dicts.DictProxy.__init__(self, {})
258
        
259
        keys_table = as_Table(keys_table)
260
        
261
        self.db = db
262
        self.table = keys_table
263
        self.update(dict_) # after setting vars because __setitem__() needs them
264
    
265
    def copy(self): return ColDict(self.db, self.table, self.inner.copy())
266
    
267
    def __getitem__(self, key):
268
        return dicts.DictProxy.__getitem__(self, self._key(key))
269
    
270
    def __setitem__(self, key, value):
271
        key = self._key(key)
272
        if value == None: value = self.db.col_default(key)
273
        dicts.DictProxy.__setitem__(self, key, as_Col(value, name=key.name))
274
    
275
    def _key(self, key): return as_Col(key, self.table)
276

    
277
##### Functions
278

    
279
class Function(Table): pass
280

    
281
def TempFunction(name, autocommit):
282
    schema = None
283
    if not autocommit: schema = 'pg_temp'
284
    return Function(name, schema)
285

    
286
class InternalFunction(CustomCode): pass
287

    
288
class FunctionCall(Code):
289
    def __init__(self, function, *args):
290
        '''
291
        @param args [Code|literal-value...] The function's arguments
292
        '''
293
        if not isinstance(function, Code): function = Function(function)
294
        args = map(remove_col_rename, map(as_Value, args))
295
        
296
        self.function = function
297
        self.args = args
298
    
299
    def to_str(self, db):
300
        args_str = ', '.join((v.to_str(db) for v in self.args))
301
        return self.function.to_str(db)+'('+args_str+')'
302

    
303
def wrap_in_func(function, value):
304
    '''Wraps a value inside a function call.
305
    Propagates any column renaming to the returned value.
306
    '''
307
    return wrap(lambda v: FunctionCall(function, v), value)
308

    
309
def unwrap_func_call(func_call, check_name=None):
310
    '''Unwraps any function call to its first argument.
311
    Also removes any column renaming.
312
    '''
313
    func_call = remove_col_rename(func_call)
314
    if not isinstance(func_call, FunctionCall): return func_call
315
    
316
    if check_name != None:
317
        name = func_call.function.name
318
        assert name == None or name == check_name
319
    return func_call.args[0]
320

    
321
##### Conditions
322

    
323
class ColValueCond(Code):
324
    def __init__(self, col, value):
325
        value = as_ValueCond(value)
326
        
327
        self.col = col
328
        self.value = value
329
    
330
    def to_str(self, db): return self.value.to_str(db, self.col)
331

    
332
def combine_conds(conds, keyword=None):
333
    '''
334
    @param keyword The keyword to add before the conditions, if any
335
    '''
336
    str_ = ''
337
    if keyword != None:
338
        if conds == []: whitespace = ''
339
        elif len(conds) == 1: whitespace = ' '
340
        else: whitespace = '\n'
341
        str_ += keyword+whitespace
342
    
343
    str_ += '\nAND '.join(conds)
344
    return str_
345

    
346
##### Condition column comparisons
347

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

    
363
class CompareCond(ValueCond):
364
    def __init__(self, value, operator='='):
365
        '''
366
        @param operator By default, compares NULL values literally. Use '~=' or
367
            '~!=' to pass NULLs through.
368
        '''
369
        ValueCond.__init__(self, value)
370
        self.operator = operator
371
    
372
    def to_str(self, db, left_value):
373
        if not isinstance(left_value, Code): left_value = Col(left_value)
374
        left_value = remove_col_rename(left_value)
375
        
376
        right_value = self.value
377
        left = left_value.to_str(db)
378
        right = right_value.to_str(db)
379
        
380
        # Parse operator
381
        operator = self.operator
382
        passthru_null_ref = [False]
383
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
384
        neg_ref = [False]
385
        operator = strings.remove_prefix('!', operator, neg_ref)
386
        equals = operator.endswith('=')
387
        if equals and is_null(self.value): operator = 'IS'
388
        
389
        # Create str
390
        str_ = left+' '+operator+' '+right
391
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
392
            str_ = '('+str_+' OR ('+left+' IS NULL AND '+right+' IS NULL))'
393
        if neg_ref[0]: str_ = 'NOT '+str_
394
        return str_
395

    
396
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
397
assume_literal = object()
398

    
399
def as_ValueCond(value, default_table=assume_literal):
400
    if not isinstance(value, ValueCond):
401
        if default_table is not assume_literal:
402
            value = with_default_table(value, default_table)
403
        return CompareCond(value)
404
    else: return value
405

    
406
##### Joins
407

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

    
410
# Tells Join the left and right columns have the same name and are never NULL
411
join_same_not_null = object()
412

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

    
415
class Join(BasicObject):
416
    def __init__(self, table, mapping={}, type_=None):
417
        '''
418
        @param mapping dict(right_table_col=left_table_col, ...)
419
            * if left_table_col is join_same: left_table_col = right_table_col
420
              * Note that right_table_col must be a string
421
            * if left_table_col is join_same_not_null:
422
              left_table_col = right_table_col and both have NOT NULL constraint
423
              * Note that right_table_col must be a string
424
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
425
            * filter_out: equivalent to 'LEFT' with the query filtered by
426
              `table_pkey IS NULL` (indicating no match)
427
        '''
428
        if util.is_str(table): table = Table(table)
429
        assert type_ == None or util.is_str(type_) or type_ is filter_out
430
        
431
        self.table = table
432
        self.mapping = mapping
433
        self.type_ = type_
434
    
435
    def to_str(self, db, left_table_):
436
        def join(entry):
437
            '''Parses non-USING joins'''
438
            right_table_col, left_table_col = entry
439
            
440
            # Switch order (right_table_col is on the left in the comparison)
441
            left = right_table_col
442
            right = left_table_col
443
            left_table = self.table
444
            right_table = left_table_
445
            
446
            left_table = left_table.to_Table()
447
            right_table = right_table.to_Table()
448
            
449
            # Parse left side
450
            left = with_default_table(left, left_table)
451
            
452
            # Parse special values
453
            left_on_right = Col(left.name, right_table)
454
            if right is join_same: right = left_on_right
455
            elif right is join_same_not_null:
456
                right = CompareCond(left_on_right, '~=')
457
            
458
            # Parse right side
459
            right = as_ValueCond(right, right_table)
460
            
461
            return right.to_str(db, left)
462
        
463
        # Create join condition
464
        type_ = self.type_
465
        joins = self.mapping
466
        if joins == {}: join_cond = None
467
        elif type_ is not filter_out and reduce(operator.and_,
468
            (v is join_same_not_null for v in joins.itervalues())):
469
            # all cols w/ USING, so can use simpler USING syntax
470
            cols = map(to_name_only_col, joins.iterkeys())
471
            join_cond = 'USING ('+(', '.join((c.to_str(db) for c in cols)))+')'
472
        else: join_cond = combine_conds(map(join, joins.iteritems()), 'ON')
473
        
474
        if isinstance(self.table, NamedTable): whitespace = '\n'
475
        else: whitespace = ' '
476
        
477
        # Create join
478
        if type_ is filter_out: type_ = 'LEFT'
479
        str_ = ''
480
        if type_ != None: str_ += type_+' '
481
        str_ += 'JOIN'+whitespace+self.table.to_str(db)
482
        if join_cond != None: str_ += whitespace+join_cond
483
        return str_
484
    
485
    def __repr__(self): return self.to_str(mockDb, '<left_table>')
486

    
487
##### Value exprs
488

    
489
default = CustomCode('DEFAULT')
490

    
491
row_count = CustomCode('count(*)')
492

    
493
def EnsureNotNull(value, null=r'\N'):
494
    return FunctionCall(InternalFunction('coalesce'), as_Col(value), null)
495

    
496
##### Table exprs
497

    
498
class Values(Code):
499
    def __init__(self, values):
500
        '''
501
        @param values [...]|[[...], ...] Can be one or multiple rows.
502
        '''
503
        rows = values
504
        if len(values) >= 1 and not lists.is_seq(values[0]): # only one row
505
            rows = [values]
506
        for i, row in enumerate(rows):
507
            rows[i] = map(remove_col_rename, map(as_Value, row))
508
        
509
        self.rows = rows
510
    
511
    def to_str(self, db):
512
        def row_str(row):
513
            return '('+(', '.join((v.to_str(db) for v in row)))+')'
514
        return 'VALUES '+(', '.join(map(row_str, self.rows)))
515

    
516
def NamedValues(name, cols, values):
517
    '''
518
    @post `cols` will be changed to Col objects with the table set to `name`.
519
    '''
520
    set_cols_table(name, cols)
521
    return NamedTable(name, Values(values), cols)
522

    
523
##### Database structure
524

    
525
class TypedCol(Col):
526
    def __init__(self, name, type_):
527
        Col.__init__(self, name)
528
        
529
        self.type = type_
530
    
531
    def to_str(self, db): return Col.to_str(self, db)+' '+self.type
532
    
533
    def to_Col(self): return Col(self.name)
(25-25/36)