Project

General

Profile

1 2211 aaronmk
# SQL code generation
2
3 2276 aaronmk
import operator
4 2568 aaronmk
import re
5 2653 aaronmk
import UserDict
6 2276 aaronmk
7 2667 aaronmk
import dicts
8 2360 aaronmk
import objects
9 2222 aaronmk
import strings
10 2227 aaronmk
import util
11 2211 aaronmk
12 2587 aaronmk
##### Names
13 2499 aaronmk
14 2608 aaronmk
identifier_max_len = 63 # works for both PostgreSQL and MySQL
15 2587 aaronmk
16
def add_suffix(str_, suffix):
17 2609 aaronmk
    '''Preserves version so that it won't be truncated off the string, leading
18
    to collisions.'''
19 2613 aaronmk
    # Preserve version
20
    before, sep, version = str_.rpartition('#')
21
    if sep != '': # found match
22
        str_ = before
23 2609 aaronmk
        suffix = sep+version+suffix
24 2613 aaronmk
25 2587 aaronmk
    return strings.add_suffix(str_, suffix, identifier_max_len)
26
27 2575 aaronmk
def is_safe_name(name):
28 2583 aaronmk
    '''A name is safe *and unambiguous* if it:
29
    * contains only *lowercase* word (\w) characters
30
    * doesn't start with a digit
31
    * contains "_", so that it's not a keyword
32
    '''
33
    return re.match(r'^(?=.*_)(?!\d)[^\WA-Z]+$', name)
34 2568 aaronmk
35 2499 aaronmk
def esc_name(name, quote='"'):
36
    return quote + name.replace(quote, quote+quote) + quote
37
        # doubling an embedded quote escapes it in both PostgreSQL and MySQL
38
39 2513 aaronmk
def clean_name(name): return name.replace('"', '').replace('`', '')
40
41 2659 aaronmk
##### General SQL code objects
42 2219 aaronmk
43 2349 aaronmk
class MockDb:
44 2503 aaronmk
    def esc_value(self, value): return strings.repr_no_u(value)
45 2349 aaronmk
46 2499 aaronmk
    def esc_name(self, name): return esc_name(name)
47 2349 aaronmk
mockDb = MockDb()
48
49 2514 aaronmk
class BasicObject(objects.BasicObject):
50
    def __init__(self, value): self.value = value
51
52
    def __str__(self): return clean_name(strings.repr_no_u(self))
53
54 2659 aaronmk
##### Unparameterized code objects
55
56 2514 aaronmk
class Code(BasicObject):
57 2658 aaronmk
    def to_str(self, db): raise NotImplementedError()
58 2349 aaronmk
59 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb)
60 2211 aaronmk
61 2269 aaronmk
class CustomCode(Code):
62 2256 aaronmk
    def __init__(self, str_): self.str_ = str_
63
64
    def to_str(self, db): return self.str_
65
66 2659 aaronmk
def as_Code(value):
67
    if util.is_str(value): return CustomCode(value)
68
    else: return Literal(value)
69
70 2540 aaronmk
class Expr(Code):
71
    def __init__(self, expr): self.expr = expr
72
73
    def to_str(self, db): return '('+self.expr.to_str(db)+')'
74
75 2335 aaronmk
##### Literal values
76
77 2216 aaronmk
class Literal(Code):
78 2211 aaronmk
    def __init__(self, value): self.value = value
79 2213 aaronmk
80
    def to_str(self, db): return db.esc_value(self.value)
81 2211 aaronmk
82 2400 aaronmk
def as_Value(value):
83
    if isinstance(value, Code): return value
84
    else: return Literal(value)
85
86 2216 aaronmk
def is_null(value): return isinstance(value, Literal) and value.value == None
87
88 2335 aaronmk
##### Tables
89
90 2211 aaronmk
class Table(Code):
91
    def __init__(self, name, schema=None):
92
        '''
93
        @param schema str|None (for no schema)
94
        '''
95
        self.name = name
96
        self.schema = schema
97
98 2348 aaronmk
    def to_str(self, db):
99
        str_ = ''
100
        if self.schema != None: str_ += db.esc_name(self.schema)+'.'
101
        str_ += db.esc_name(self.name)
102
        return str_
103 2336 aaronmk
104
    def to_Table(self): return self
105 2211 aaronmk
106 2219 aaronmk
def as_Table(table):
107 2270 aaronmk
    if table == None or isinstance(table, Code): return table
108 2219 aaronmk
    else: return Table(table)
109
110 2336 aaronmk
class NamedTable(Table):
111
    def __init__(self, name, code, cols=None):
112
        Table.__init__(self, name)
113
114
        if not isinstance(code, Code): code = Table(code)
115
116
        self.code = code
117
        self.cols = cols
118
119
    def to_str(self, db):
120 2467 aaronmk
        str_ = self.code.to_str(db)+'\nAS '+Table.to_str(self, db)
121 2336 aaronmk
        if self.cols != None: str_ += ' ('+(', '.join(self.cols))+')'
122
        return str_
123
124
    def to_Table(self): return Table(self.name)
125
126 2335 aaronmk
##### Columns
127
128 2211 aaronmk
class Col(Code):
129
    def __init__(self, name, table=None):
130
        '''
131
        @param table Table|None (for no table)
132
        '''
133 2241 aaronmk
        if util.is_str(table): table = Table(table)
134 2211 aaronmk
        assert table == None or isinstance(table, Table)
135
136
        self.name = name
137
        self.table = table
138
139
    def to_str(self, db):
140
        str_ = ''
141
        if self.table != None: str_ += self.table.to_str(db)+'.'
142 2348 aaronmk
        str_ += db.esc_name(self.name)
143 2211 aaronmk
        return str_
144 2314 aaronmk
145
    def to_Col(self): return self
146 2211 aaronmk
147 2393 aaronmk
def is_table_col(col): return col.table != None
148
149 2563 aaronmk
def as_Col(col, table=None, name=None):
150
    '''
151
    @param name If not None, any non-Col input will be renamed using NamedCol.
152
    '''
153
    if name != None:
154
        col = as_Value(col)
155
        if not isinstance(col, Col): col = NamedCol(name, col)
156 2333 aaronmk
157
    if isinstance(col, Code): return col
158 2260 aaronmk
    else: return Col(col, table)
159
160 2401 aaronmk
def to_name_only_col(col, check_table=None):
161
    col = as_Col(col)
162 2579 aaronmk
    if not isinstance(col, Col): return col
163 2401 aaronmk
164
    if check_table != None:
165
        table = col.table
166
        assert table == None or table == check_table
167
    return Col(col.name)
168
169 2323 aaronmk
class NamedCol(Col):
170 2229 aaronmk
    def __init__(self, name, code):
171 2310 aaronmk
        Col.__init__(self, name)
172
173 2229 aaronmk
        if not isinstance(code, Code): code = Literal(code)
174
175
        self.code = code
176
177
    def to_str(self, db):
178 2310 aaronmk
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
179 2314 aaronmk
180
    def to_Col(self): return Col(self.name)
181 2229 aaronmk
182 2462 aaronmk
def remove_col_rename(col):
183
    if isinstance(col, NamedCol): col = col.code
184
    return col
185
186 2667 aaronmk
class ColDict(dicts.DictProxy):
187 2564 aaronmk
    '''A dict that automatically makes inserted entries Col objects'''
188
189 2645 aaronmk
    def __init__(self, db, keys_table, dict_={}):
190 2667 aaronmk
        dicts.DictProxy.__init__(self, {})
191
192 2645 aaronmk
        keys_table = as_Table(keys_table)
193
194 2642 aaronmk
        self.db = db
195 2641 aaronmk
        self.table = keys_table
196 2653 aaronmk
        self.update(dict_) # after setting vars because __setitem__() needs them
197 2641 aaronmk
198 2667 aaronmk
    def copy(self): return ColDict(self.db, self.table, self.inner.copy())
199 2655 aaronmk
200 2667 aaronmk
    def __getitem__(self, key):
201
        return dicts.DictProxy.__getitem__(self, self._key(key))
202 2653 aaronmk
203 2564 aaronmk
    def __setitem__(self, key, value):
204 2642 aaronmk
        key = self._key(key)
205 2661 aaronmk
        if value == None: value = self.db.col_default(key)
206 2667 aaronmk
        dicts.DictProxy.__setitem__(self, key, as_Col(value, name=key.name))
207 2564 aaronmk
208 2641 aaronmk
    def _key(self, key): return as_Col(key, self.table)
209 2564 aaronmk
210 2524 aaronmk
##### Functions
211
212
class Function(Table): pass
213
214
class FunctionCall(Code):
215
    def __init__(self, function, *args):
216
        '''
217
        @param args [Code...] The function's arguments
218
        '''
219
        if not isinstance(function, Code): function = Function(function)
220 2532 aaronmk
        args = map(remove_col_rename, args)
221 2524 aaronmk
222
        self.function = function
223
        self.args = args
224
225
    def to_str(self, db):
226
        args_str = ', '.join((v.to_str(db) for v in self.args))
227
        return self.function.to_str(db)+'('+args_str+')'
228
229 2533 aaronmk
def wrap_in_func(function, value):
230
    '''Wraps a value inside a function call.
231
    Propagates any column renaming to the returned value.
232
    '''
233
    name = None
234
    if isinstance(value, NamedCol): name = value.name
235
    value = FunctionCall(function, value)
236
    if name != None: value = NamedCol(name, value)
237
    return value
238
239 2561 aaronmk
def unwrap_func_call(func_call, check_name=None):
240
    '''Unwraps any function call to its first argument.
241
    Also removes any column renaming.
242
    '''
243
    func_call = remove_col_rename(func_call)
244
    if not isinstance(func_call, FunctionCall): return func_call
245
246
    if check_name != None:
247
        name = func_call.function.name
248
        assert name == None or name == check_name
249
    return func_call.args[0]
250
251 2335 aaronmk
##### Conditions
252 2259 aaronmk
253 2398 aaronmk
class ColValueCond(Code):
254
    def __init__(self, col, value):
255
        value = as_ValueCond(value)
256
257
        self.col = col
258
        self.value = value
259
260
    def to_str(self, db): return self.value.to_str(db, self.col)
261
262 2577 aaronmk
def combine_conds(conds, keyword=None):
263
    '''
264
    @param keyword The keyword to add before the conditions, if any
265
    '''
266
    str_ = ''
267
    if keyword != None:
268
        if conds == []: whitespace = ''
269
        elif len(conds) == 1: whitespace = ' '
270
        else: whitespace = '\n'
271
        str_ += keyword+whitespace
272
273
    str_ += '\nAND '.join(conds)
274
    return str_
275
276 2398 aaronmk
##### Condition column comparisons
277
278 2514 aaronmk
class ValueCond(BasicObject):
279 2213 aaronmk
    def __init__(self, value):
280 2225 aaronmk
        if not isinstance(value, Code): value = Literal(value)
281 2462 aaronmk
        value = remove_col_rename(value)
282 2213 aaronmk
283
        self.value = value
284 2214 aaronmk
285 2216 aaronmk
    def to_str(self, db, left_value):
286 2214 aaronmk
        '''
287 2216 aaronmk
        @param left_value The Code object that the condition is being applied on
288 2214 aaronmk
        '''
289
        raise NotImplemented()
290 2228 aaronmk
291 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb, '<left_value>')
292 2211 aaronmk
293
class CompareCond(ValueCond):
294
    def __init__(self, value, operator='='):
295 2222 aaronmk
        '''
296
        @param operator By default, compares NULL values literally. Use '~=' or
297
            '~!=' to pass NULLs through.
298
        '''
299 2211 aaronmk
        ValueCond.__init__(self, value)
300
        self.operator = operator
301
302 2216 aaronmk
    def to_str(self, db, left_value):
303
        if not isinstance(left_value, Code): left_value = Col(left_value)
304 2462 aaronmk
        left_value = remove_col_rename(left_value)
305 2216 aaronmk
306 2222 aaronmk
        right_value = self.value
307
        left = left_value.to_str(db)
308
        right = right_value.to_str(db)
309
310
        # Parse operator
311 2216 aaronmk
        operator = self.operator
312 2222 aaronmk
        passthru_null_ref = [False]
313
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
314
        neg_ref = [False]
315
        operator = strings.remove_prefix('!', operator, neg_ref)
316
        equals = operator.endswith('=')
317
        if equals and is_null(self.value): operator = 'IS'
318
319
        # Create str
320
        str_ = left+' '+operator+' '+right
321
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
322 2578 aaronmk
            str_ = '('+str_+' OR ('+left+' IS NULL AND '+right+' IS NULL))'
323
        if neg_ref[0]: str_ = 'NOT '+str_
324 2222 aaronmk
        return str_
325 2216 aaronmk
326 2260 aaronmk
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
327
assume_literal = object()
328
329
def as_ValueCond(value, default_table=assume_literal):
330
    if not isinstance(value, ValueCond):
331
        if default_table is not assume_literal:
332
            value = as_Col(value, default_table)
333
        return CompareCond(value)
334 2216 aaronmk
    else: return value
335 2219 aaronmk
336 2335 aaronmk
##### Joins
337
338 2352 aaronmk
join_same = object() # tells Join the left and right columns have the same name
339 2260 aaronmk
340 2353 aaronmk
# Tells Join the left and right columns have the same name and are never NULL
341
join_same_not_null = object()
342
343 2260 aaronmk
filter_out = object() # tells Join to filter out rows that match the join
344
345 2514 aaronmk
class Join(BasicObject):
346 2260 aaronmk
    def __init__(self, table, mapping, type_=None):
347
        '''
348
        @param mapping dict(right_table_col=left_table_col, ...)
349 2352 aaronmk
            * if left_table_col is join_same: left_table_col = right_table_col
350 2353 aaronmk
              * Note that right_table_col must be a string
351
            * if left_table_col is join_same_not_null:
352
              left_table_col = right_table_col and both have NOT NULL constraint
353
              * Note that right_table_col must be a string
354 2260 aaronmk
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
355
            * filter_out: equivalent to 'LEFT' with the query filtered by
356
              `table_pkey IS NULL` (indicating no match)
357
        '''
358
        if util.is_str(table): table = Table(table)
359
        assert type_ == None or util.is_str(type_) or type_ is filter_out
360
361
        self.table = table
362
        self.mapping = mapping
363
        self.type_ = type_
364
365
    def to_str(self, db, left_table):
366 2353 aaronmk
        # Switch order (left_table is on the right in the comparison)
367
        right_table = left_table
368
        left_table = self.table # note left_table is reassigned
369
370 2260 aaronmk
        def join(entry):
371
            '''Parses non-USING joins'''
372
            right_table_col, left_table_col = entry
373
374 2353 aaronmk
            # Switch order (right_table_col is on the left in the comparison)
375
            left = right_table_col
376
            right = left_table_col
377
378 2260 aaronmk
            # Parse special values
379 2353 aaronmk
            if right is join_same: right = left
380
            elif right is join_same_not_null:
381
                right = CompareCond(as_Col(left, right_table), '~=')
382 2260 aaronmk
383 2353 aaronmk
            right = as_ValueCond(right, right_table)
384 2578 aaronmk
            return right.to_str(db, as_Col(left, left_table))
385 2260 aaronmk
386 2265 aaronmk
        # Create join condition
387
        type_ = self.type_
388 2276 aaronmk
        joins = self.mapping
389 2265 aaronmk
        if type_ is not filter_out and reduce(operator.and_,
390 2460 aaronmk
            (v is join_same_not_null for v in joins.itervalues())):
391 2260 aaronmk
            # all cols w/ USING, so can use simpler USING syntax
392 2298 aaronmk
            cols = (as_Col(v).to_str(db) for v in joins.iterkeys())
393
            join_cond = 'USING ('+(', '.join(cols))+')'
394 2576 aaronmk
        else:
395
            if len(joins) == 1: whitespace = ' '
396
            else: whitespace = '\n'
397 2577 aaronmk
            join_cond = combine_conds(map(join, joins.iteritems()), 'ON')
398 2260 aaronmk
399
        # Create join
400
        if type_ is filter_out: type_ = 'LEFT'
401 2266 aaronmk
        str_ = ''
402
        if type_ != None: str_ += type_+' '
403 2353 aaronmk
        str_ += 'JOIN '+left_table.to_str(db)+' '+join_cond
404 2266 aaronmk
        return str_
405 2349 aaronmk
406 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb, '<left_table>')
407 2424 aaronmk
408
##### Value exprs
409
410
row_count = CustomCode('count(*)')