Project

General

Profile

1 2211 aaronmk
# SQL code generation
2
3 2748 aaronmk
import copy
4 2276 aaronmk
import operator
5 2568 aaronmk
import re
6 2653 aaronmk
import UserDict
7 2953 aaronmk
import warnings
8 2276 aaronmk
9 2667 aaronmk
import dicts
10 2953 aaronmk
import exc
11 2701 aaronmk
import iters
12
import lists
13 2360 aaronmk
import objects
14 2222 aaronmk
import strings
15 2227 aaronmk
import util
16 2211 aaronmk
17 2587 aaronmk
##### Names
18 2499 aaronmk
19 2608 aaronmk
identifier_max_len = 63 # works for both PostgreSQL and MySQL
20 2587 aaronmk
21 2932 aaronmk
def concat(str_, suffix):
22 2609 aaronmk
    '''Preserves version so that it won't be truncated off the string, leading
23
    to collisions.'''
24 2613 aaronmk
    # Preserve version
25 2995 aaronmk
    match = re.match(r'^(.*?)((?:(?:#\d+)?\)?)*(?:\.\w+)?(?:::[\w ]+)*)$', str_)
26 2985 aaronmk
    if match:
27
        str_, old_suffix = match.groups()
28
        suffix = old_suffix+suffix
29 2613 aaronmk
30 2932 aaronmk
    return strings.concat(str_, suffix, identifier_max_len)
31 2587 aaronmk
32 2932 aaronmk
def truncate(str_): return concat(str_, '')
33 2842 aaronmk
34 2575 aaronmk
def is_safe_name(name):
35 2583 aaronmk
    '''A name is safe *and unambiguous* if it:
36
    * contains only *lowercase* word (\w) characters
37
    * doesn't start with a digit
38
    * contains "_", so that it's not a keyword
39 2984 aaronmk
    '''
40
    return re.match(r'^(?=.*_)(?!\d)[^\WA-Z]+$', name)
41 2568 aaronmk
42 2499 aaronmk
def esc_name(name, quote='"'):
43
    return quote + name.replace(quote, quote+quote) + quote
44
        # doubling an embedded quote escapes it in both PostgreSQL and MySQL
45
46 2513 aaronmk
def clean_name(name): return name.replace('"', '').replace('`', '')
47
48 2659 aaronmk
##### General SQL code objects
49 2219 aaronmk
50 2349 aaronmk
class MockDb:
51 2503 aaronmk
    def esc_value(self, value): return strings.repr_no_u(value)
52 2349 aaronmk
53 2499 aaronmk
    def esc_name(self, name): return esc_name(name)
54 2859 aaronmk
55
    def col_info(self, col):
56
        return TypedCol(col.name, '<type>', CustomCode('<default>'), True)
57
58 2349 aaronmk
mockDb = MockDb()
59
60 2514 aaronmk
class BasicObject(objects.BasicObject):
61
    def __init__(self, value): self.value = value
62
63
    def __str__(self): return clean_name(strings.repr_no_u(self))
64
65 2659 aaronmk
##### Unparameterized code objects
66
67 2514 aaronmk
class Code(BasicObject):
68 2658 aaronmk
    def to_str(self, db): raise NotImplementedError()
69 2349 aaronmk
70 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb)
71 2211 aaronmk
72 2269 aaronmk
class CustomCode(Code):
73 2256 aaronmk
    def __init__(self, str_): self.str_ = str_
74
75
    def to_str(self, db): return self.str_
76
77 2815 aaronmk
def as_Code(value, db=None):
78
    '''
79
    @param db If set, runs db.std_code() on the value.
80
    '''
81
    if util.is_str(value):
82
        if db != None: value = db.std_code(value)
83
        return CustomCode(value)
84 2659 aaronmk
    else: return Literal(value)
85
86 2540 aaronmk
class Expr(Code):
87
    def __init__(self, expr): self.expr = expr
88
89
    def to_str(self, db): return '('+self.expr.to_str(db)+')'
90
91 2335 aaronmk
##### Literal values
92
93 2216 aaronmk
class Literal(Code):
94 2211 aaronmk
    def __init__(self, value): self.value = value
95 2213 aaronmk
96
    def to_str(self, db): return db.esc_value(self.value)
97 2211 aaronmk
98 2400 aaronmk
def as_Value(value):
99
    if isinstance(value, Code): return value
100
    else: return Literal(value)
101
102 2216 aaronmk
def is_null(value): return isinstance(value, Literal) and value.value == None
103
104 2711 aaronmk
##### Derived elements
105
106
src_self = object() # tells Col that it is its own source column
107
108
class Derived(Code):
109
    def __init__(self, srcs):
110 2712 aaronmk
        '''An element which was derived from some other element(s).
111 2711 aaronmk
        @param srcs See self.set_srcs()
112
        '''
113
        self.set_srcs(srcs)
114
115 2713 aaronmk
    def set_srcs(self, srcs, overwrite=True):
116 2711 aaronmk
        '''
117
        @param srcs (self_type...)|src_self The element(s) this is derived from
118
        '''
119 2713 aaronmk
        if not overwrite and self.srcs != (): return # already set
120
121 2711 aaronmk
        if srcs == src_self: srcs = (self,)
122
        srcs = tuple(srcs) # make Col hashable
123
        self.srcs = srcs
124
125
    def _compare_on(self):
126
        compare_on = self.__dict__.copy()
127
        del compare_on['srcs'] # ignore
128
        return compare_on
129
130
def cols_srcs(cols): return lists.uniqify(iters.flatten((v.srcs for v in cols)))
131
132 2335 aaronmk
##### Tables
133
134 2712 aaronmk
class Table(Derived):
135 2991 aaronmk
    def __init__(self, name, schema=None, srcs=(), is_temp=False):
136 2211 aaronmk
        '''
137
        @param schema str|None (for no schema)
138 2712 aaronmk
        @param srcs (Table...)|src_self See Derived.set_srcs()
139 2211 aaronmk
        '''
140 2712 aaronmk
        Derived.__init__(self, srcs)
141
142 2843 aaronmk
        name = truncate(name)
143
144 2211 aaronmk
        self.name = name
145
        self.schema = schema
146 2991 aaronmk
        self.is_temp = is_temp
147 3000 aaronmk
        self.index_cols = {}
148 2211 aaronmk
149 2348 aaronmk
    def to_str(self, db):
150
        str_ = ''
151
        if self.schema != None: str_ += db.esc_name(self.schema)+'.'
152
        str_ += db.esc_name(self.name)
153
        return str_
154 2336 aaronmk
155
    def to_Table(self): return self
156 3000 aaronmk
157
    def _compare_on(self):
158
        compare_on = Derived._compare_on(self)
159
        del compare_on['index_cols'] # ignore
160
        return compare_on
161 2211 aaronmk
162 2835 aaronmk
def is_underlying_table(table):
163
    return isinstance(table, Table) and table.to_Table() is table
164 2832 aaronmk
165 2902 aaronmk
class NoUnderlyingTableException(Exception): pass
166
167
def underlying_table(table):
168
    table = remove_table_rename(table)
169
    if not is_underlying_table(table): raise NoUnderlyingTableException
170
    return table
171
172 2776 aaronmk
def as_Table(table, schema=None):
173 2270 aaronmk
    if table == None or isinstance(table, Code): return table
174 2776 aaronmk
    else: return Table(table, schema)
175 2219 aaronmk
176 2707 aaronmk
def suffixed_table(table, suffix): return Table(table.name+suffix, table.schema)
177
178 2336 aaronmk
class NamedTable(Table):
179
    def __init__(self, name, code, cols=None):
180
        Table.__init__(self, name)
181
182 3016 aaronmk
        code = as_Table(code)
183 2741 aaronmk
        if not isinstance(code, (Table, FunctionCall, Expr)): code = Expr(code)
184 3020 aaronmk
        if cols != None: cols = [to_name_only_col(c).to_Col() for c in cols]
185 2336 aaronmk
186
        self.code = code
187
        self.cols = cols
188
189
    def to_str(self, db):
190 2467 aaronmk
        str_ = self.code.to_str(db)+'\nAS '+Table.to_str(self, db)
191 2742 aaronmk
        if self.cols != None:
192
            str_ += ' ('+(', '.join((c.to_str(db) for c in self.cols)))+')'
193 2336 aaronmk
        return str_
194
195
    def to_Table(self): return Table(self.name)
196
197 2753 aaronmk
def remove_table_rename(table):
198
    if isinstance(table, NamedTable): table = table.code
199
    return table
200
201 2335 aaronmk
##### Columns
202
203 2711 aaronmk
class Col(Derived):
204 2701 aaronmk
    def __init__(self, name, table=None, srcs=()):
205 2211 aaronmk
        '''
206
        @param table Table|None (for no table)
207 2711 aaronmk
        @param srcs (Col...)|src_self See Derived.set_srcs()
208 2211 aaronmk
        '''
209 2711 aaronmk
        Derived.__init__(self, srcs)
210
211 2843 aaronmk
        name = truncate(name)
212 2241 aaronmk
        if util.is_str(table): table = Table(table)
213 2211 aaronmk
        assert table == None or isinstance(table, Table)
214
215
        self.name = name
216
        self.table = table
217
218 2989 aaronmk
    def to_str(self, db, for_str=False):
219 2933 aaronmk
        str_ = db.esc_name(self.name)
220 2989 aaronmk
        if for_str: str_ = clean_name(str_)
221 2933 aaronmk
        if self.table != None:
222 2989 aaronmk
            table = self.table.to_Table()
223
            if for_str: str_ = concat(str(table), '.'+str_)
224
            else: str_ = table.to_str(db)+'.'+str_
225 2211 aaronmk
        return str_
226 2314 aaronmk
227 2989 aaronmk
    def __str__(self): return self.to_str(mockDb, for_str=True)
228 2933 aaronmk
229 2314 aaronmk
    def to_Col(self): return self
230 2211 aaronmk
231 2767 aaronmk
def is_table_col(col): return isinstance(col, Col) and col.table != None
232 2393 aaronmk
233 3000 aaronmk
def index_col(col):
234
    if not is_table_col(col): return None
235
    return col.table.index_cols.get(col.name, None)
236 2999 aaronmk
237 2996 aaronmk
def is_temp_col(col): return col.table != None and col.table.is_temp
238
239 2563 aaronmk
def as_Col(col, table=None, name=None):
240
    '''
241
    @param name If not None, any non-Col input will be renamed using NamedCol.
242
    '''
243
    if name != None:
244
        col = as_Value(col)
245
        if not isinstance(col, Col): col = NamedCol(name, col)
246 2333 aaronmk
247
    if isinstance(col, Code): return col
248 2260 aaronmk
    else: return Col(col, table)
249
250 2750 aaronmk
def with_default_table(col, table, overwrite=False):
251 2747 aaronmk
    col = as_Col(col)
252 2750 aaronmk
    if not isinstance(col, NamedCol) and (overwrite or col.table == None):
253 2748 aaronmk
        col = copy.copy(col) # don't modify input!
254
        col.table = table
255 2747 aaronmk
    return col
256
257 2744 aaronmk
def set_cols_table(table, cols):
258
    table = as_Table(table)
259
260
    for i, col in enumerate(cols):
261
        col = cols[i] = as_Col(col)
262
        col.table = table
263
264 2401 aaronmk
def to_name_only_col(col, check_table=None):
265
    col = as_Col(col)
266 3020 aaronmk
    if not is_table_col(col): return col
267 2401 aaronmk
268
    if check_table != None:
269
        table = col.table
270
        assert table == None or table == check_table
271
    return Col(col.name)
272
273 2993 aaronmk
def suffixed_col(col, suffix):
274
    return Col(concat(col.name, suffix), col.table, col.srcs)
275
276 2323 aaronmk
class NamedCol(Col):
277 2229 aaronmk
    def __init__(self, name, code):
278 2310 aaronmk
        Col.__init__(self, name)
279
280 3016 aaronmk
        code = as_Value(code)
281 2229 aaronmk
282
        self.code = code
283
284
    def to_str(self, db):
285 2310 aaronmk
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
286 2314 aaronmk
287
    def to_Col(self): return Col(self.name)
288 2229 aaronmk
289 2462 aaronmk
def remove_col_rename(col):
290
    if isinstance(col, NamedCol): col = col.code
291
    return col
292
293 2830 aaronmk
def underlying_col(col):
294
    col = remove_col_rename(col)
295 2849 aaronmk
    if not isinstance(col, Col): raise NoUnderlyingTableException
296
297 2902 aaronmk
    return Col(col.name, underlying_table(col.table), col.srcs)
298 2830 aaronmk
299 2703 aaronmk
def wrap(wrap_func, value):
300
    '''Wraps a value, propagating any column renaming to the returned value.'''
301
    if isinstance(value, NamedCol):
302
        return NamedCol(value.name, wrap_func(value.code))
303
    else: return wrap_func(value)
304
305 2667 aaronmk
class ColDict(dicts.DictProxy):
306 2564 aaronmk
    '''A dict that automatically makes inserted entries Col objects'''
307
308 2645 aaronmk
    def __init__(self, db, keys_table, dict_={}):
309 2667 aaronmk
        dicts.DictProxy.__init__(self, {})
310
311 2645 aaronmk
        keys_table = as_Table(keys_table)
312
313 2642 aaronmk
        self.db = db
314 2641 aaronmk
        self.table = keys_table
315 2653 aaronmk
        self.update(dict_) # after setting vars because __setitem__() needs them
316 2641 aaronmk
317 2667 aaronmk
    def copy(self): return ColDict(self.db, self.table, self.inner.copy())
318 2655 aaronmk
319 2667 aaronmk
    def __getitem__(self, key):
320
        return dicts.DictProxy.__getitem__(self, self._key(key))
321 2653 aaronmk
322 2564 aaronmk
    def __setitem__(self, key, value):
323 2642 aaronmk
        key = self._key(key)
324 2819 aaronmk
        if value == None: value = self.db.col_info(key).default
325 2667 aaronmk
        dicts.DictProxy.__setitem__(self, key, as_Col(value, name=key.name))
326 2564 aaronmk
327 2641 aaronmk
    def _key(self, key): return as_Col(key, self.table)
328 2564 aaronmk
329 2524 aaronmk
##### Functions
330
331 2912 aaronmk
Function = Table
332 2911 aaronmk
as_Function = as_Table
333
334 2691 aaronmk
class InternalFunction(CustomCode): pass
335
336 2941 aaronmk
class NamedArg(NamedCol):
337
    def __init__(self, name, value):
338
        NamedCol.__init__(self, name, value)
339
340
    def to_str(self, db):
341
        return Col.to_str(self, db)+' := '+self.code.to_str(db)
342
343 2524 aaronmk
class FunctionCall(Code):
344 2941 aaronmk
    def __init__(self, function, *args, **kw_args):
345 2524 aaronmk
        '''
346 2690 aaronmk
        @param args [Code|literal-value...] The function's arguments
347 2524 aaronmk
        '''
348 3016 aaronmk
        function = as_Function(function)
349 2941 aaronmk
        def filter_(arg): return remove_col_rename(as_Value(arg))
350
        args = map(filter_, args)
351
        args += [NamedArg(k, filter_(v)) for k, v in kw_args.iteritems()]
352 2524 aaronmk
353
        self.function = function
354
        self.args = args
355
356
    def to_str(self, db):
357
        args_str = ', '.join((v.to_str(db) for v in self.args))
358
        return self.function.to_str(db)+'('+args_str+')'
359
360 2533 aaronmk
def wrap_in_func(function, value):
361
    '''Wraps a value inside a function call.
362
    Propagates any column renaming to the returned value.
363
    '''
364 2703 aaronmk
    return wrap(lambda v: FunctionCall(function, v), value)
365 2533 aaronmk
366 2561 aaronmk
def unwrap_func_call(func_call, check_name=None):
367
    '''Unwraps any function call to its first argument.
368
    Also removes any column renaming.
369
    '''
370
    func_call = remove_col_rename(func_call)
371
    if not isinstance(func_call, FunctionCall): return func_call
372
373
    if check_name != None:
374
        name = func_call.function.name
375
        assert name == None or name == check_name
376
    return func_call.args[0]
377
378 2986 aaronmk
##### Casts
379
380
class Cast(FunctionCall):
381
    def __init__(self, type_, value):
382
        value = as_Value(value)
383
384
        self.type_ = type_
385
        self.value = value
386
387
    def to_str(self, db):
388
        return 'CAST('+self.value.to_str(db)+' AS '+self.type_+')'
389
390 2335 aaronmk
##### Conditions
391 2259 aaronmk
392 2398 aaronmk
class ColValueCond(Code):
393
    def __init__(self, col, value):
394
        value = as_ValueCond(value)
395
396
        self.col = col
397
        self.value = value
398
399
    def to_str(self, db): return self.value.to_str(db, self.col)
400
401 2577 aaronmk
def combine_conds(conds, keyword=None):
402
    '''
403
    @param keyword The keyword to add before the conditions, if any
404
    '''
405
    str_ = ''
406
    if keyword != None:
407
        if conds == []: whitespace = ''
408
        elif len(conds) == 1: whitespace = ' '
409
        else: whitespace = '\n'
410
        str_ += keyword+whitespace
411
412
    str_ += '\nAND '.join(conds)
413
    return str_
414
415 2398 aaronmk
##### Condition column comparisons
416
417 2514 aaronmk
class ValueCond(BasicObject):
418 2213 aaronmk
    def __init__(self, value):
419 2858 aaronmk
        value = remove_col_rename(as_Value(value))
420 2213 aaronmk
421
        self.value = value
422 2214 aaronmk
423 2216 aaronmk
    def to_str(self, db, left_value):
424 2214 aaronmk
        '''
425 2216 aaronmk
        @param left_value The Code object that the condition is being applied on
426 2214 aaronmk
        '''
427
        raise NotImplemented()
428 2228 aaronmk
429 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb, '<left_value>')
430 2211 aaronmk
431
class CompareCond(ValueCond):
432
    def __init__(self, value, operator='='):
433 2222 aaronmk
        '''
434
        @param operator By default, compares NULL values literally. Use '~=' or
435
            '~!=' to pass NULLs through.
436
        '''
437 2211 aaronmk
        ValueCond.__init__(self, value)
438
        self.operator = operator
439
440 2216 aaronmk
    def to_str(self, db, left_value):
441 2858 aaronmk
        left_value = remove_col_rename(as_Col(left_value))
442 2216 aaronmk
443 2222 aaronmk
        right_value = self.value
444
445
        # Parse operator
446 2216 aaronmk
        operator = self.operator
447 2222 aaronmk
        passthru_null_ref = [False]
448
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
449
        neg_ref = [False]
450
        operator = strings.remove_prefix('!', operator, neg_ref)
451 2844 aaronmk
        equals = operator.endswith('=') # also includes <=, >=
452 2222 aaronmk
453 2825 aaronmk
        # Handle nullable columns
454
        check_null = False
455 2844 aaronmk
        if not passthru_null_ref[0]: # NULLs compare equal
456 2857 aaronmk
            try: left_value = ensure_not_null(db, left_value)
457 2844 aaronmk
            except ensure_not_null_excs: # fall back to alternate method
458
                check_null = equals and isinstance(right_value, Col)
459 2837 aaronmk
            else:
460 2857 aaronmk
                if isinstance(left_value, EnsureNotNull):
461
                    right_value = ensure_not_null(db, right_value,
462
                        left_value.type) # apply same function to both sides
463 2825 aaronmk
464 2844 aaronmk
        if equals and is_null(right_value): operator = 'IS'
465
466 2825 aaronmk
        left = left_value.to_str(db)
467
        right = right_value.to_str(db)
468
469 2222 aaronmk
        # Create str
470
        str_ = left+' '+operator+' '+right
471 2825 aaronmk
        if check_null:
472 2578 aaronmk
            str_ = '('+str_+' OR ('+left+' IS NULL AND '+right+' IS NULL))'
473
        if neg_ref[0]: str_ = 'NOT '+str_
474 2222 aaronmk
        return str_
475 2216 aaronmk
476 2260 aaronmk
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
477
assume_literal = object()
478
479
def as_ValueCond(value, default_table=assume_literal):
480
    if not isinstance(value, ValueCond):
481
        if default_table is not assume_literal:
482 2748 aaronmk
            value = with_default_table(value, default_table)
483 2260 aaronmk
        return CompareCond(value)
484 2216 aaronmk
    else: return value
485 2219 aaronmk
486 2335 aaronmk
##### Joins
487
488 2352 aaronmk
join_same = object() # tells Join the left and right columns have the same name
489 2260 aaronmk
490 2353 aaronmk
# Tells Join the left and right columns have the same name and are never NULL
491
join_same_not_null = object()
492
493 2260 aaronmk
filter_out = object() # tells Join to filter out rows that match the join
494
495 2514 aaronmk
class Join(BasicObject):
496 2746 aaronmk
    def __init__(self, table, mapping={}, type_=None):
497 2260 aaronmk
        '''
498
        @param mapping dict(right_table_col=left_table_col, ...)
499 2352 aaronmk
            * if left_table_col is join_same: left_table_col = right_table_col
500 2353 aaronmk
              * Note that right_table_col must be a string
501
            * if left_table_col is join_same_not_null:
502
              left_table_col = right_table_col and both have NOT NULL constraint
503
              * Note that right_table_col must be a string
504 2260 aaronmk
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
505
            * filter_out: equivalent to 'LEFT' with the query filtered by
506
              `table_pkey IS NULL` (indicating no match)
507
        '''
508
        if util.is_str(table): table = Table(table)
509
        assert type_ == None or util.is_str(type_) or type_ is filter_out
510
511
        self.table = table
512
        self.mapping = mapping
513
        self.type_ = type_
514
515 2749 aaronmk
    def to_str(self, db, left_table_):
516 2260 aaronmk
        def join(entry):
517
            '''Parses non-USING joins'''
518
            right_table_col, left_table_col = entry
519
520 2353 aaronmk
            # Switch order (right_table_col is on the left in the comparison)
521
            left = right_table_col
522
            right = left_table_col
523 2749 aaronmk
            left_table = self.table
524
            right_table = left_table_
525 2353 aaronmk
526 2747 aaronmk
            # Parse left side
527 2748 aaronmk
            left = with_default_table(left, left_table)
528 2747 aaronmk
529 2260 aaronmk
            # Parse special values
530 2747 aaronmk
            left_on_right = Col(left.name, right_table)
531
            if right is join_same: right = left_on_right
532 2353 aaronmk
            elif right is join_same_not_null:
533 2747 aaronmk
                right = CompareCond(left_on_right, '~=')
534 2260 aaronmk
535 2747 aaronmk
            # Parse right side
536 2353 aaronmk
            right = as_ValueCond(right, right_table)
537 2747 aaronmk
538
            return right.to_str(db, left)
539 2260 aaronmk
540 2265 aaronmk
        # Create join condition
541
        type_ = self.type_
542 2276 aaronmk
        joins = self.mapping
543 2746 aaronmk
        if joins == {}: join_cond = None
544
        elif type_ is not filter_out and reduce(operator.and_,
545 2460 aaronmk
            (v is join_same_not_null for v in joins.itervalues())):
546 2260 aaronmk
            # all cols w/ USING, so can use simpler USING syntax
547 2747 aaronmk
            cols = map(to_name_only_col, joins.iterkeys())
548
            join_cond = 'USING ('+(', '.join((c.to_str(db) for c in cols)))+')'
549 2757 aaronmk
        else: join_cond = combine_conds(map(join, joins.iteritems()), 'ON')
550 2260 aaronmk
551 2757 aaronmk
        if isinstance(self.table, NamedTable): whitespace = '\n'
552
        else: whitespace = ' '
553
554 2260 aaronmk
        # Create join
555
        if type_ is filter_out: type_ = 'LEFT'
556 2266 aaronmk
        str_ = ''
557
        if type_ != None: str_ += type_+' '
558 2757 aaronmk
        str_ += 'JOIN'+whitespace+self.table.to_str(db)
559
        if join_cond != None: str_ += whitespace+join_cond
560 2266 aaronmk
        return str_
561 2349 aaronmk
562 2514 aaronmk
    def __repr__(self): return self.to_str(mockDb, '<left_table>')
563 2424 aaronmk
564
##### Value exprs
565
566 2737 aaronmk
default = CustomCode('DEFAULT')
567
568 2424 aaronmk
row_count = CustomCode('count(*)')
569 2674 aaronmk
570 2850 aaronmk
# See <http://www.postgresql.org/docs/8.3/static/datatype-numeric.html>
571 2958 aaronmk
null_sentinels = {
572
    'character varying': r'\N',
573
    'double precision': 'NaN',
574
    'integer': 2147483647,
575
    'text': r'\N',
576
    'timestamp with time zone': 'infinity'
577
}
578 2692 aaronmk
579 2850 aaronmk
class EnsureNotNull(FunctionCall):
580
    def __init__(self, value, type_):
581 2870 aaronmk
        FunctionCall.__init__(self, InternalFunction('COALESCE'), as_Col(value),
582 2988 aaronmk
            Cast(type_, null_sentinels[type_]))
583 2850 aaronmk
584
        self.type = type_
585 3001 aaronmk
586
    def to_str(self, db):
587
        col = self.args[0]
588
        index_col_ = index_col(col)
589
        if index_col_ != None: return index_col_.to_str(db)
590
        return FunctionCall.to_str(self, db)
591 2850 aaronmk
592 2737 aaronmk
##### Table exprs
593
594
class Values(Code):
595
    def __init__(self, values):
596 2739 aaronmk
        '''
597
        @param values [...]|[[...], ...] Can be one or multiple rows.
598
        '''
599
        rows = values
600
        if len(values) >= 1 and not lists.is_seq(values[0]): # only one row
601
            rows = [values]
602
        for i, row in enumerate(rows):
603
            rows[i] = map(remove_col_rename, map(as_Value, row))
604 2737 aaronmk
605 2739 aaronmk
        self.rows = rows
606 2737 aaronmk
607
    def to_str(self, db):
608 2739 aaronmk
        def row_str(row):
609
            return '('+(', '.join((v.to_str(db) for v in row)))+')'
610
        return 'VALUES '+(', '.join(map(row_str, self.rows)))
611 2737 aaronmk
612 2740 aaronmk
def NamedValues(name, cols, values):
613 2745 aaronmk
    '''
614
    @post `cols` will be changed to Col objects with the table set to `name`.
615
    '''
616 2834 aaronmk
    table = NamedTable(name, Values(values), cols)
617
    set_cols_table(table, cols)
618
    return table
619 2740 aaronmk
620 2674 aaronmk
##### Database structure
621
622
class TypedCol(Col):
623 2871 aaronmk
    def __init__(self, name, type_, default=None, nullable=True,
624
        constraints=None):
625 2818 aaronmk
        assert default == None or isinstance(default, Code)
626
627 2674 aaronmk
        Col.__init__(self, name)
628
629
        self.type = type_
630 2818 aaronmk
        self.default = default
631
        self.nullable = nullable
632 2871 aaronmk
        self.constraints = constraints
633 2674 aaronmk
634 2818 aaronmk
    def to_str(self, db):
635
        str_ = Col.to_str(self, db)+' '+self.type
636
        if not self.nullable: str_ += ' NOT NULL'
637
        if self.default != None: str_ += ' DEFAULT '+self.default.to_str(db)
638 2871 aaronmk
        if self.constraints != None: str_ += ' '+self.constraints
639 2818 aaronmk
        return str_
640 2674 aaronmk
641
    def to_Col(self): return Col(self.name)
642 2822 aaronmk
643 2840 aaronmk
ensure_not_null_excs = (NoUnderlyingTableException, KeyError)
644
645 2851 aaronmk
def ensure_not_null(db, col, type_=None):
646 2840 aaronmk
    '''
647 2855 aaronmk
    @param col If type_ is not set, must have an underlying column.
648 2851 aaronmk
    @param type_ If set, overrides the underlying column's type.
649 2840 aaronmk
    @return EnsureNotNull|Col
650
    @throws ensure_not_null_excs
651
    '''
652 2855 aaronmk
    nullable = True
653
    try: typed_col = db.col_info(underlying_col(col))
654
    except NoUnderlyingTableException:
655
        if type_ == None: raise
656
    else:
657
        if type_ == None: type_ = typed_col.type
658
        nullable = typed_col.nullable
659
660 2953 aaronmk
    if nullable:
661
        try: col = EnsureNotNull(col, type_)
662
        except KeyError, e:
663
            # Warn of no null sentinel for type, even if caller catches error
664
            warnings.warn(UserWarning(exc.str_(e)))
665
            raise
666
667 2840 aaronmk
    return col