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