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 truncate(str_): return add_suffix(str_, '')
31

    
32
def is_safe_name(name):
33
    '''A name is safe *and unambiguous* if it:
34
    * contains only *lowercase* word (\w) characters
35
    * doesn't start with a digit
36
    * contains "_", so that it's not a keyword
37
    ''' 
38
    return re.match(r'^(?=.*_)(?!\d)[^\WA-Z]+$', name) 
39

    
40
def esc_name(name, quote='"'):
41
    return quote + name.replace(quote, quote+quote) + quote
42
        # doubling an embedded quote escapes it in both PostgreSQL and MySQL
43

    
44
def clean_name(name): return name.replace('"', '').replace('`', '')
45

    
46
##### General SQL code objects
47

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

    
54
class BasicObject(objects.BasicObject):
55
    def __init__(self, value): self.value = value
56
    
57
    def __str__(self): return clean_name(strings.repr_no_u(self))
58

    
59
##### Unparameterized code objects
60

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

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

    
71
def as_Code(value, db=None):
72
    '''
73
    @param db If set, runs db.std_code() on the value.
74
    '''
75
    if util.is_str(value):
76
        if db != None: value = db.std_code(value)
77
        return CustomCode(value)
78
    else: return Literal(value)
79

    
80
class Expr(Code):
81
    def __init__(self, expr): self.expr = expr
82
    
83
    def to_str(self, db): return '('+self.expr.to_str(db)+')'
84

    
85
##### Literal values
86

    
87
class Literal(Code):
88
    def __init__(self, value): self.value = value
89
    
90
    def to_str(self, db): return db.esc_value(self.value)
91

    
92
def as_Value(value):
93
    if isinstance(value, Code): return value
94
    else: return Literal(value)
95

    
96
def is_null(value): return isinstance(value, Literal) and value.value == None
97

    
98
##### Derived elements
99

    
100
src_self = object() # tells Col that it is its own source column
101

    
102
class Derived(Code):
103
    def __init__(self, srcs):
104
        '''An element which was derived from some other element(s).
105
        @param srcs See self.set_srcs()
106
        '''
107
        self.set_srcs(srcs)
108
    
109
    def set_srcs(self, srcs, overwrite=True):
110
        '''
111
        @param srcs (self_type...)|src_self The element(s) this is derived from
112
        '''
113
        if not overwrite and self.srcs != (): return # already set
114
        
115
        if srcs == src_self: srcs = (self,)
116
        srcs = tuple(srcs) # make Col hashable
117
        self.srcs = srcs
118
    
119
    def _compare_on(self):
120
        compare_on = self.__dict__.copy()
121
        del compare_on['srcs'] # ignore
122
        return compare_on
123

    
124
def cols_srcs(cols): return lists.uniqify(iters.flatten((v.srcs for v in cols)))
125

    
126
##### Tables
127

    
128
class Table(Derived):
129
    def __init__(self, name, schema=None, srcs=()):
130
        '''
131
        @param schema str|None (for no schema)
132
        @param srcs (Table...)|src_self See Derived.set_srcs()
133
        '''
134
        Derived.__init__(self, srcs)
135
        
136
        name = truncate(name)
137
        
138
        self.name = name
139
        self.schema = schema
140
    
141
    def to_str(self, db):
142
        str_ = ''
143
        if self.schema != None: str_ += db.esc_name(self.schema)+'.'
144
        str_ += db.esc_name(self.name)
145
        return str_
146
    
147
    def to_Table(self): return self
148

    
149
def is_underlying_table(table):
150
    return isinstance(table, Table) and table.to_Table() is table
151

    
152
def as_Table(table, schema=None):
153
    if table == None or isinstance(table, Code): return table
154
    else: return Table(table, schema)
155

    
156
def suffixed_table(table, suffix): return Table(table.name+suffix, table.schema)
157

    
158
class NamedTable(Table):
159
    def __init__(self, name, code, cols=None):
160
        Table.__init__(self, name)
161
        
162
        if not isinstance(code, Code): code = Table(code)
163
        if not isinstance(code, (Table, FunctionCall, Expr)): code = Expr(code)
164
        if cols != None: cols = map(to_name_only_col, cols)
165
        
166
        self.code = code
167
        self.cols = cols
168
    
169
    def to_str(self, db):
170
        str_ = self.code.to_str(db)+'\nAS '+Table.to_str(self, db)
171
        if self.cols != None:
172
            str_ += ' ('+(', '.join((c.to_str(db) for c in self.cols)))+')'
173
        return str_
174
    
175
    def to_Table(self): return Table(self.name)
176

    
177
def remove_table_rename(table):
178
    if isinstance(table, NamedTable): table = table.code
179
    return table
180

    
181
##### Columns
182

    
183
class Col(Derived):
184
    def __init__(self, name, table=None, srcs=()):
185
        '''
186
        @param table Table|None (for no table)
187
        @param srcs (Col...)|src_self See Derived.set_srcs()
188
        '''
189
        Derived.__init__(self, srcs)
190
        
191
        name = truncate(name)
192
        if util.is_str(table): table = Table(table)
193
        assert table == None or isinstance(table, Table)
194
        
195
        self.name = name
196
        self.table = table
197
    
198
    def to_str(self, db):
199
        str_ = ''
200
        if self.table != None: str_ += self.table.to_Table().to_str(db)+'.'
201
        str_ += db.esc_name(self.name)
202
        return str_
203
    
204
    def to_Col(self): return self
205

    
206
def is_table_col(col): return isinstance(col, Col) and col.table != None
207

    
208
def as_Col(col, table=None, name=None):
209
    '''
210
    @param name If not None, any non-Col input will be renamed using NamedCol.
211
    '''
212
    if name != None:
213
        col = as_Value(col)
214
        if not isinstance(col, Col): col = NamedCol(name, col)
215
    
216
    if isinstance(col, Code): return col
217
    else: return Col(col, table)
218

    
219
def with_default_table(col, table, overwrite=False):
220
    col = as_Col(col)
221
    if not isinstance(col, NamedCol) and (overwrite or col.table == None):
222
        col = copy.copy(col) # don't modify input!
223
        col.table = table
224
    return col
225

    
226
def set_cols_table(table, cols):
227
    table = as_Table(table)
228
    
229
    for i, col in enumerate(cols):
230
        col = cols[i] = as_Col(col)
231
        col.table = table
232

    
233
def to_name_only_col(col, check_table=None):
234
    col = as_Col(col)
235
    if not isinstance(col, Col): return col
236
    
237
    if check_table != None:
238
        table = col.table
239
        assert table == None or table == check_table
240
    return Col(col.name)
241

    
242
class NamedCol(Col):
243
    def __init__(self, name, code):
244
        Col.__init__(self, name)
245
        
246
        if not isinstance(code, Code): code = Literal(code)
247
        
248
        self.code = code
249
    
250
    def to_str(self, db):
251
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
252
    
253
    def to_Col(self): return Col(self.name)
254

    
255
def remove_col_rename(col):
256
    if isinstance(col, NamedCol): col = col.code
257
    return col
258

    
259
class NoUnderlyingTableException(Exception): pass
260

    
261
def underlying_col(col):
262
    col = remove_col_rename(col)
263
    table = remove_table_rename(col.table)
264
    if not is_underlying_table(table): raise NoUnderlyingTableException
265
    return Col(col.name, table, col.srcs)
266

    
267
def wrap(wrap_func, value):
268
    '''Wraps a value, propagating any column renaming to the returned value.'''
269
    if isinstance(value, NamedCol):
270
        return NamedCol(value.name, wrap_func(value.code))
271
    else: return wrap_func(value)
272

    
273
class ColDict(dicts.DictProxy):
274
    '''A dict that automatically makes inserted entries Col objects'''
275
    
276
    def __init__(self, db, keys_table, dict_={}):
277
        dicts.DictProxy.__init__(self, {})
278
        
279
        keys_table = as_Table(keys_table)
280
        
281
        self.db = db
282
        self.table = keys_table
283
        self.update(dict_) # after setting vars because __setitem__() needs them
284
    
285
    def copy(self): return ColDict(self.db, self.table, self.inner.copy())
286
    
287
    def __getitem__(self, key):
288
        return dicts.DictProxy.__getitem__(self, self._key(key))
289
    
290
    def __setitem__(self, key, value):
291
        key = self._key(key)
292
        if value == None: value = self.db.col_info(key).default
293
        dicts.DictProxy.__setitem__(self, key, as_Col(value, name=key.name))
294
    
295
    def _key(self, key): return as_Col(key, self.table)
296

    
297
##### Functions
298

    
299
class Function(Table): pass
300

    
301
def TempFunction(name, autocommit):
302
    schema = None
303
    if not autocommit: schema = 'pg_temp'
304
    return Function(name, schema)
305

    
306
class InternalFunction(CustomCode): pass
307

    
308
class FunctionCall(Code):
309
    def __init__(self, function, *args):
310
        '''
311
        @param args [Code|literal-value...] The function's arguments
312
        '''
313
        if not isinstance(function, Code): function = Function(function)
314
        args = map(remove_col_rename, map(as_Value, args))
315
        
316
        self.function = function
317
        self.args = args
318
    
319
    def to_str(self, db):
320
        args_str = ', '.join((v.to_str(db) for v in self.args))
321
        return self.function.to_str(db)+'('+args_str+')'
322

    
323
def wrap_in_func(function, value):
324
    '''Wraps a value inside a function call.
325
    Propagates any column renaming to the returned value.
326
    '''
327
    return wrap(lambda v: FunctionCall(function, v), value)
328

    
329
def unwrap_func_call(func_call, check_name=None):
330
    '''Unwraps any function call to its first argument.
331
    Also removes any column renaming.
332
    '''
333
    func_call = remove_col_rename(func_call)
334
    if not isinstance(func_call, FunctionCall): return func_call
335
    
336
    if check_name != None:
337
        name = func_call.function.name
338
        assert name == None or name == check_name
339
    return func_call.args[0]
340

    
341
##### Conditions
342

    
343
class ColValueCond(Code):
344
    def __init__(self, col, value):
345
        value = as_ValueCond(value)
346
        
347
        self.col = col
348
        self.value = value
349
    
350
    def to_str(self, db): return self.value.to_str(db, self.col)
351

    
352
def combine_conds(conds, keyword=None):
353
    '''
354
    @param keyword The keyword to add before the conditions, if any
355
    '''
356
    str_ = ''
357
    if keyword != None:
358
        if conds == []: whitespace = ''
359
        elif len(conds) == 1: whitespace = ' '
360
        else: whitespace = '\n'
361
        str_ += keyword+whitespace
362
    
363
    str_ += '\nAND '.join(conds)
364
    return str_
365

    
366
##### Condition column comparisons
367

    
368
class ValueCond(BasicObject):
369
    def __init__(self, value):
370
        if not isinstance(value, Code): value = Literal(value)
371
        value = remove_col_rename(value)
372
        
373
        self.value = value
374
    
375
    def to_str(self, db, left_value):
376
        '''
377
        @param left_value The Code object that the condition is being applied on
378
        '''
379
        raise NotImplemented()
380
    
381
    def __repr__(self): return self.to_str(mockDb, '<left_value>')
382

    
383
class CompareCond(ValueCond):
384
    def __init__(self, value, operator='='):
385
        '''
386
        @param operator By default, compares NULL values literally. Use '~=' or
387
            '~!=' to pass NULLs through.
388
        '''
389
        ValueCond.__init__(self, value)
390
        self.operator = operator
391
    
392
    def to_str(self, db, left_value):
393
        if not isinstance(left_value, Code): left_value = Col(left_value)
394
        left_value = remove_col_rename(left_value)
395
        
396
        right_value = self.value
397
        
398
        # Parse operator
399
        operator = self.operator
400
        passthru_null_ref = [False]
401
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
402
        neg_ref = [False]
403
        operator = strings.remove_prefix('!', operator, neg_ref)
404
        equals = operator.endswith('=') # also includes <=, >=
405
        
406
        # Handle nullable columns
407
        check_null = False
408
        if not passthru_null_ref[0]: # NULLs compare equal
409
            try: left_non_null = ensure_not_null(db, left_value)
410
            except ensure_not_null_excs: # fall back to alternate method
411
                check_null = equals and isinstance(right_value, Col)
412
            else:
413
                if left_non_null is not left_value: # wrapped, so wrap both
414
                    left_value = left_non_null
415
                    right_value = EnsureNotNull(right_value, left_value.args[1])
416
        
417
        if equals and is_null(right_value): operator = 'IS'
418
        
419
        left = left_value.to_str(db)
420
        right = right_value.to_str(db)
421
        
422
        # Create str
423
        str_ = left+' '+operator+' '+right
424
        if check_null:
425
            str_ = '('+str_+' OR ('+left+' IS NULL AND '+right+' IS NULL))'
426
        if neg_ref[0]: str_ = 'NOT '+str_
427
        return str_
428

    
429
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
430
assume_literal = object()
431

    
432
def as_ValueCond(value, default_table=assume_literal):
433
    if not isinstance(value, ValueCond):
434
        if default_table is not assume_literal:
435
            value = with_default_table(value, default_table)
436
        return CompareCond(value)
437
    else: return value
438

    
439
##### Joins
440

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

    
443
# Tells Join the left and right columns have the same name and are never NULL
444
join_same_not_null = object()
445

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

    
448
class Join(BasicObject):
449
    def __init__(self, table, mapping={}, type_=None):
450
        '''
451
        @param mapping dict(right_table_col=left_table_col, ...)
452
            * if left_table_col is join_same: left_table_col = right_table_col
453
              * Note that right_table_col must be a string
454
            * if left_table_col is join_same_not_null:
455
              left_table_col = right_table_col and both have NOT NULL constraint
456
              * Note that right_table_col must be a string
457
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
458
            * filter_out: equivalent to 'LEFT' with the query filtered by
459
              `table_pkey IS NULL` (indicating no match)
460
        '''
461
        if util.is_str(table): table = Table(table)
462
        assert type_ == None or util.is_str(type_) or type_ is filter_out
463
        
464
        self.table = table
465
        self.mapping = mapping
466
        self.type_ = type_
467
    
468
    def to_str(self, db, left_table_):
469
        def join(entry):
470
            '''Parses non-USING joins'''
471
            right_table_col, left_table_col = entry
472
            
473
            # Switch order (right_table_col is on the left in the comparison)
474
            left = right_table_col
475
            right = left_table_col
476
            left_table = self.table
477
            right_table = left_table_
478
            
479
            # Parse left side
480
            left = with_default_table(left, left_table)
481
            
482
            # Parse special values
483
            left_on_right = Col(left.name, right_table)
484
            if right is join_same: right = left_on_right
485
            elif right is join_same_not_null:
486
                right = CompareCond(left_on_right, '~=')
487
            
488
            # Parse right side
489
            right = as_ValueCond(right, right_table)
490
            
491
            return right.to_str(db, left)
492
        
493
        # Create join condition
494
        type_ = self.type_
495
        joins = self.mapping
496
        if joins == {}: join_cond = None
497
        elif type_ is not filter_out and reduce(operator.and_,
498
            (v is join_same_not_null for v in joins.itervalues())):
499
            # all cols w/ USING, so can use simpler USING syntax
500
            cols = map(to_name_only_col, joins.iterkeys())
501
            join_cond = 'USING ('+(', '.join((c.to_str(db) for c in cols)))+')'
502
        else: join_cond = combine_conds(map(join, joins.iteritems()), 'ON')
503
        
504
        if isinstance(self.table, NamedTable): whitespace = '\n'
505
        else: whitespace = ' '
506
        
507
        # Create join
508
        if type_ is filter_out: type_ = 'LEFT'
509
        str_ = ''
510
        if type_ != None: str_ += type_+' '
511
        str_ += 'JOIN'+whitespace+self.table.to_str(db)
512
        if join_cond != None: str_ += whitespace+join_cond
513
        return str_
514
    
515
    def __repr__(self): return self.to_str(mockDb, '<left_table>')
516

    
517
##### Value exprs
518

    
519
default = CustomCode('DEFAULT')
520

    
521
row_count = CustomCode('count(*)')
522

    
523
def EnsureNotNull(value, null=r'\N'):
524
    return FunctionCall(InternalFunction('coalesce'), as_Col(value), null)
525

    
526
##### Table exprs
527

    
528
class Values(Code):
529
    def __init__(self, values):
530
        '''
531
        @param values [...]|[[...], ...] Can be one or multiple rows.
532
        '''
533
        rows = values
534
        if len(values) >= 1 and not lists.is_seq(values[0]): # only one row
535
            rows = [values]
536
        for i, row in enumerate(rows):
537
            rows[i] = map(remove_col_rename, map(as_Value, row))
538
        
539
        self.rows = rows
540
    
541
    def to_str(self, db):
542
        def row_str(row):
543
            return '('+(', '.join((v.to_str(db) for v in row)))+')'
544
        return 'VALUES '+(', '.join(map(row_str, self.rows)))
545

    
546
def NamedValues(name, cols, values):
547
    '''
548
    @post `cols` will be changed to Col objects with the table set to `name`.
549
    '''
550
    table = NamedTable(name, Values(values), cols)
551
    set_cols_table(table, cols)
552
    return table
553

    
554
##### Database structure
555

    
556
class TypedCol(Col):
557
    def __init__(self, name, type_, default=None, nullable=True):
558
        assert default == None or isinstance(default, Code)
559
        
560
        Col.__init__(self, name)
561
        
562
        self.type = type_
563
        self.default = default
564
        self.nullable = nullable
565
    
566
    def to_str(self, db):
567
        str_ = Col.to_str(self, db)+' '+self.type
568
        if not self.nullable: str_ += ' NOT NULL'
569
        if self.default != None: str_ += ' DEFAULT '+self.default.to_str(db)
570
        return str_
571
    
572
    def to_Col(self): return Col(self.name)
573

    
574
null_sentinels = {'text': r'\N'}
575

    
576
ensure_not_null_excs = (NoUnderlyingTableException, KeyError)
577

    
578
def ensure_not_null(db, col):
579
    '''
580
    @param col Must have an underlying column.
581
    @return EnsureNotNull|Col
582
    @throws ensure_not_null_excs
583
    '''
584
    typed_col = db.col_info(underlying_col(col))
585
    if typed_col.nullable:
586
        col = EnsureNotNull(col, null_sentinels[typed_col.type])
587
    return col
(25-25/36)