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
    
53
    def col_info(self, col):
54
        return TypedCol(col.name, '<type>', CustomCode('<default>'), True)
55

    
56
mockDb = MockDb()
57

    
58
class BasicObject(objects.BasicObject):
59
    def __init__(self, value): self.value = value
60
    
61
    def __str__(self): return clean_name(strings.repr_no_u(self))
62

    
63
##### Unparameterized code objects
64

    
65
class Code(BasicObject):
66
    def to_str(self, db): raise NotImplementedError()
67
    
68
    def __repr__(self): return self.to_str(mockDb)
69

    
70
class CustomCode(Code):
71
    def __init__(self, str_): self.str_ = str_
72
    
73
    def to_str(self, db): return self.str_
74

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

    
84
class Expr(Code):
85
    def __init__(self, expr): self.expr = expr
86
    
87
    def to_str(self, db): return '('+self.expr.to_str(db)+')'
88

    
89
##### Literal values
90

    
91
class Literal(Code):
92
    def __init__(self, value): self.value = value
93
    
94
    def to_str(self, db): return db.esc_value(self.value)
95

    
96
def as_Value(value):
97
    if isinstance(value, Code): return value
98
    else: return Literal(value)
99

    
100
def is_null(value): return isinstance(value, Literal) and value.value == None
101

    
102
##### Derived elements
103

    
104
src_self = object() # tells Col that it is its own source column
105

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

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

    
130
##### Tables
131

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

    
153
def is_underlying_table(table):
154
    return isinstance(table, Table) and table.to_Table() is table
155

    
156
def as_Table(table, schema=None):
157
    if table == None or isinstance(table, Code): return table
158
    else: return Table(table, schema)
159

    
160
def suffixed_table(table, suffix): return Table(table.name+suffix, table.schema)
161

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

    
181
def remove_table_rename(table):
182
    if isinstance(table, NamedTable): table = table.code
183
    return table
184

    
185
##### Columns
186

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

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

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

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

    
230
def set_cols_table(table, cols):
231
    table = as_Table(table)
232
    
233
    for i, col in enumerate(cols):
234
        col = cols[i] = as_Col(col)
235
        col.table = table
236

    
237
def to_name_only_col(col, check_table=None):
238
    col = as_Col(col)
239
    if not isinstance(col, Col): return col
240
    
241
    if check_table != None:
242
        table = col.table
243
        assert table == None or table == check_table
244
    return Col(col.name)
245

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

    
259
def remove_col_rename(col):
260
    if isinstance(col, NamedCol): col = col.code
261
    return col
262

    
263
class NoUnderlyingTableException(Exception): pass
264

    
265
def underlying_col(col):
266
    col = remove_col_rename(col)
267
    if not isinstance(col, Col): raise NoUnderlyingTableException
268
    
269
    table = remove_table_rename(col.table)
270
    if not is_underlying_table(table): raise NoUnderlyingTableException
271
    
272
    return Col(col.name, table, col.srcs)
273

    
274
def wrap(wrap_func, value):
275
    '''Wraps a value, propagating any column renaming to the returned value.'''
276
    if isinstance(value, NamedCol):
277
        return NamedCol(value.name, wrap_func(value.code))
278
    else: return wrap_func(value)
279

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

    
304
##### Functions
305

    
306
class Function(Table): pass
307

    
308
def TempFunction(name, autocommit):
309
    schema = None
310
    if not autocommit: schema = 'pg_temp'
311
    return Function(name, schema)
312

    
313
class InternalFunction(CustomCode): pass
314

    
315
class FunctionCall(Code):
316
    def __init__(self, function, *args):
317
        '''
318
        @param args [Code|literal-value...] The function's arguments
319
        '''
320
        if not isinstance(function, Code): function = Function(function)
321
        args = map(remove_col_rename, map(as_Value, args))
322
        
323
        self.function = function
324
        self.args = args
325
    
326
    def to_str(self, db):
327
        args_str = ', '.join((v.to_str(db) for v in self.args))
328
        return self.function.to_str(db)+'('+args_str+')'
329

    
330
def wrap_in_func(function, value):
331
    '''Wraps a value inside a function call.
332
    Propagates any column renaming to the returned value.
333
    '''
334
    return wrap(lambda v: FunctionCall(function, v), value)
335

    
336
def unwrap_func_call(func_call, check_name=None):
337
    '''Unwraps any function call to its first argument.
338
    Also removes any column renaming.
339
    '''
340
    func_call = remove_col_rename(func_call)
341
    if not isinstance(func_call, FunctionCall): return func_call
342
    
343
    if check_name != None:
344
        name = func_call.function.name
345
        assert name == None or name == check_name
346
    return func_call.args[0]
347

    
348
##### Conditions
349

    
350
class ColValueCond(Code):
351
    def __init__(self, col, value):
352
        value = as_ValueCond(value)
353
        
354
        self.col = col
355
        self.value = value
356
    
357
    def to_str(self, db): return self.value.to_str(db, self.col)
358

    
359
def combine_conds(conds, keyword=None):
360
    '''
361
    @param keyword The keyword to add before the conditions, if any
362
    '''
363
    str_ = ''
364
    if keyword != None:
365
        if conds == []: whitespace = ''
366
        elif len(conds) == 1: whitespace = ' '
367
        else: whitespace = '\n'
368
        str_ += keyword+whitespace
369
    
370
    str_ += '\nAND '.join(conds)
371
    return str_
372

    
373
##### Condition column comparisons
374

    
375
class ValueCond(BasicObject):
376
    def __init__(self, value):
377
        value = remove_col_rename(as_Value(value))
378
        
379
        self.value = value
380
    
381
    def to_str(self, db, left_value):
382
        '''
383
        @param left_value The Code object that the condition is being applied on
384
        '''
385
        raise NotImplemented()
386
    
387
    def __repr__(self): return self.to_str(mockDb, '<left_value>')
388

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

    
434
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
435
assume_literal = object()
436

    
437
def as_ValueCond(value, default_table=assume_literal):
438
    if not isinstance(value, ValueCond):
439
        if default_table is not assume_literal:
440
            value = with_default_table(value, default_table)
441
        return CompareCond(value)
442
    else: return value
443

    
444
##### Joins
445

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

    
448
# Tells Join the left and right columns have the same name and are never NULL
449
join_same_not_null = object()
450

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

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

    
522
##### Value exprs
523

    
524
default = CustomCode('DEFAULT')
525

    
526
row_count = CustomCode('count(*)')
527

    
528
# See <http://www.postgresql.org/docs/8.3/static/datatype-numeric.html>
529
null_sentinels = {'integer': 2147483647, 'text': r'\N',
530
    'character varying': r'\N'}
531

    
532
class EnsureNotNull(FunctionCall):
533
    def __init__(self, value, type_):
534
        FunctionCall.__init__(self, InternalFunction('coalesce'), as_Col(value),
535
            null_sentinels[type_])
536
        
537
        self.type = type_
538

    
539
##### Table exprs
540

    
541
class Values(Code):
542
    def __init__(self, values):
543
        '''
544
        @param values [...]|[[...], ...] Can be one or multiple rows.
545
        '''
546
        rows = values
547
        if len(values) >= 1 and not lists.is_seq(values[0]): # only one row
548
            rows = [values]
549
        for i, row in enumerate(rows):
550
            rows[i] = map(remove_col_rename, map(as_Value, row))
551
        
552
        self.rows = rows
553
    
554
    def to_str(self, db):
555
        def row_str(row):
556
            return '('+(', '.join((v.to_str(db) for v in row)))+')'
557
        return 'VALUES '+(', '.join(map(row_str, self.rows)))
558

    
559
def NamedValues(name, cols, values):
560
    '''
561
    @post `cols` will be changed to Col objects with the table set to `name`.
562
    '''
563
    table = NamedTable(name, Values(values), cols)
564
    set_cols_table(table, cols)
565
    return table
566

    
567
##### Database structure
568

    
569
class TypedCol(Col):
570
    def __init__(self, name, type_, default=None, nullable=True):
571
        assert default == None or isinstance(default, Code)
572
        
573
        Col.__init__(self, name)
574
        
575
        self.type = type_
576
        self.default = default
577
        self.nullable = nullable
578
    
579
    def to_str(self, db):
580
        str_ = Col.to_str(self, db)+' '+self.type
581
        if not self.nullable: str_ += ' NOT NULL'
582
        if self.default != None: str_ += ' DEFAULT '+self.default.to_str(db)
583
        return str_
584
    
585
    def to_Col(self): return Col(self.name)
586

    
587
ensure_not_null_excs = (NoUnderlyingTableException, KeyError)
588

    
589
def ensure_not_null(db, col, type_=None):
590
    '''
591
    @param col If type_ is not set, must have an underlying column.
592
    @param type_ If set, overrides the underlying column's type.
593
    @return EnsureNotNull|Col
594
    @throws ensure_not_null_excs
595
    '''
596
    nullable = True
597
    try: typed_col = db.col_info(underlying_col(col))
598
    except NoUnderlyingTableException:
599
        if type_ == None: raise
600
    else:
601
        if type_ == None: type_ = typed_col.type
602
        nullable = typed_col.nullable
603
    
604
    if nullable: col = EnsureNotNull(col, type_)
605
    return col
(25-25/36)