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
|