[38] - Geniusql - Trac

trunk/geniusql/adapters.py

r37	r38
208	208	coerce_int_to_any = str
209	209	coerce_list_to_any = do_pickle
210		coerce_~~dejavu~~_logic_Expression_to_any = do_pickle
	210	coerce_geniusql_logic_Expression_to_any = do_pickle
211	211	coerce_long_to_any = str
212	212
…	…
326	326	coerce_any_to_int = int
327	327	coerce_any_to_list = do_pickle
328		coerce_any_to_~~dejavu~~_logic_Expression = do_pickle
	328	coerce_any_to_geniusql_logic_Expression = do_pickle
329	329	coerce_any_to_long = long
330	330
…	…
454	454	return False
455	455
456		def coerce(self, ~~col~~, pytype):
	456	def coerce(self, hints, pytype):
457	457	"""Return a database type for the given column object and Python type."""
458	458	xform = "coerce_" + getCoerceName(pytype)
…	…
462	462	raise TypeError("'%s' is not handled by %s." %
463	463	(pytype, self.__class__))
464		return xform(~~col~~)
	464	return xform(hints)
465	465
466	466	def float_type(self, precision):
…	…
471	471	return "DOUBLE PRECISION"
472	472
473		def coerce_float(self, ~~col~~):
	473	def coerce_float(self, hints):
474	474	# Note that 'precision' is binary digits, not decimal.
475		precision = int(~~col.~~hints.get('precision', maxfloat_digits))
	475	precision = int(hints.get('precision', maxfloat_digits))
476	476	if precision > self.float_max_precision:
477	477	return self.numeric_text_type
478	478	return self.float_type(precision)
479	479
480		def coerce_str(self, ~~col~~):
	480	def coerce_str(self, hints):
481	481	# The bytes hint shall not reflect the usual 4-byte base for varchar.
482		bytes = int(~~col.~~hints.get('bytes', 255))
	482	bytes = int(hints.get('bytes', 255))
483	483	if bytes and bytes <= 255:
484	484	return "VARCHAR(%s)" % bytes
485	485	return "TEXT"
486	486
487		def coerce_dict(self, ~~col~~):
488		return self.coerce_str(~~col~~)
489		def coerce_list(self, ~~col~~):
490		return self.coerce_str(~~col~~)
491		def coerce_tuple(self, ~~col~~):
492		return self.coerce_str(~~col~~)
493		def coerce_unicode(self, ~~col~~):
494		return self.coerce_str(~~col~~)
495
496		def coerce_~~dejavu_logic_Expression(self, col~~):
497		return self.coerce_str(~~col~~)
498
499		def coerce_bool(self, ~~col~~): return "BOOLEAN"
500
501		def coerce_datetime_datetime(self, ~~col~~): return "TIMESTAMP"
502		def coerce_datetime_date(self, ~~col~~): return "DATE"
503		def coerce_datetime_time(self, ~~col~~): return "TIME"
	487	def coerce_dict(self, hints):
	488	return self.coerce_str(hints)
	489	def coerce_list(self, hints):
	490	return self.coerce_str(hints)
	491	def coerce_tuple(self, hints):
	492	return self.coerce_str(hints)
	493	def coerce_unicode(self, hints):
	494	return self.coerce_str(hints)
	495
	496	def coerce_geniusql_logic_Expression(self, hints):
	497	return self.coerce_str(hints)
	498
	499	def coerce_bool(self, hints): return "BOOLEAN"
	500
	501	def coerce_datetime_datetime(self, hints): return "TIMESTAMP"
	502	def coerce_datetime_date(self, hints): return "DATE"
	503	def coerce_datetime_time(self, hints): return "TIME"
504	504
505	505	# Use decimal instead of float to avoid rounding errors.
506		def coerce_datetime_timedelta(self, ~~col~~):
	506	def coerce_datetime_timedelta(self, hints):
507	507	return self.int_type(self.numeric_max_bytes)
508	508
509		def decimal_type(self, ~~colname,~~ precision, scale):
	509	def decimal_type(self, precision, scale):
510	510	if precision > self.numeric_max_precision:
511		errors.warn("The ~~precision of '%s'~~ (%s) is greater than the "
	511	errors.warn("The given precision (%s) is greater than the "
512	512	"maximum numeric precision (%s). Using %s instead."
513		% (~~colname,~~ precision, self.numeric_max_precision,
	513	% (precision, self.numeric_max_precision,
514	514	self.numeric_text_type))
515	515	return self.numeric_text_type
…	…
518	518	return "NUMERIC(%s, %s)" % (precision, scale)
519	519
520		def coerce_decimal_Decimal(self, ~~col~~):
521		precision = int(~~col.~~hints.get('precision', self.numeric_max_precision))
	520	def coerce_decimal_Decimal(self, hints):
	521	precision = int(hints.get('precision', self.numeric_max_precision))
522	522	# Assume most people use decimal for money; default scale = 2.
523		scale = int(~~col.~~hints.get('scale', 2))
524		return self.decimal_type(~~col.name,~~ precision, scale)
525
526		def coerce_decimal(self, ~~col~~):
	523	scale = int(hints.get('scale', 2))
	524	return self.decimal_type(precision, scale)
	525
	526	def coerce_decimal(self, hints):
527	527	# If decimal ever becomes a builtin. Python 2.5?
528		return self.coerce_decimal_Decimal(~~col~~)
529
530		def coerce_fixedpoint_FixedPoint(self, ~~col~~):
	528	return self.coerce_decimal_Decimal(hints)
	529
	530	def coerce_fixedpoint_FixedPoint(self, hints):
531	531	# Note that fixedpoint has no theoretical precision limit.
532		precision = int(~~col.~~hints.get('precision', self.numeric_max_precision))
	532	precision = int(hints.get('precision', self.numeric_max_precision))
533	533	# Assume most people use fixedpoint for money; default scale = 2.
534		scale = int(~~col.~~hints.get('scale', 2))
535		return self.decimal_type(~~col.name,~~ precision, scale)
	534	scale = int(hints.get('scale', 2))
	535	return self.decimal_type(precision, scale)
536	536
537	537	def int_type(self, bytes):
…	…
551	551	return "NUMERIC(%s, 0)" % (bytes * 2)
552	552
553		def coerce_long(self, ~~col~~):
554		bytes = int(~~col.~~hints.get('bytes', self.numeric_max_bytes))
	553	def coerce_long(self, hints):
	554	bytes = int(hints.get('bytes', self.numeric_max_bytes))
555	555	if bytes > self.numeric_max_bytes:
556	556	return self.numeric_text_type
557	557	return self.int_type(bytes)
558	558
559		def coerce_int(self, ~~col~~):
560		bytes = int(~~col.~~hints.get('bytes', maxint_bytes))
	559	def coerce_int(self, hints):
	560	bytes = int(hints.get('bytes', maxint_bytes))
561	561	if bytes > maxint_bytes:
562		return self.coerce_long(~~col~~)
	562	return self.coerce_long(hints)
563	563	return self.int_type(bytes)
564	564

trunk/geniusql/codewalk.py

r37	r38
443	443
444	444	class EarlyBinder(Rewriter):
445		"""EarlyBinder(func, reduce_getattr=True, bind_late=[])
446
447		Deep-evaluate function, replacing free vars with constants.
	445	"""Deep-evaluate a function, replacing free vars with constants.
448	446
449	447	reduce_getattr: If True (the default), getattr(x, y) will be
450	448	replaced with x.y where possible.
451	449
452		bind_late: a list of ~~name~~s (globals, freevars, or attributes)
	450	bind_late: a list of objects (globals, freevars, or attributes)
453	451	which should not be early-bound. For example, if you want
454		datetime.date.today() to be bound late, include 'today'
455		in the bind_late.
	452	datetime.date.today() to be bound late, include it in bind_late.
456	453
457	454	Example: k = lambda x: x.Date == datetime.date(2004, 1, 1)
…	…
485	482	"""
486	483
487		def __init__(self, func, reduce_getattr=True, bind_late=[]):
	484	def __init__(self, func, reduce_getattr=True, bind_late=None):
488	485	Rewriter.__init__(self, func)
489	486	self.reduce_getattr = reduce_getattr
490	487
	488	# self.env will be used to make consts out of globals and builtins.
491	489	import __builtin__
492	490	self.env = vars(__builtin__).copy()
…	…
500	498	# This stack is not passed out of this class in any way.
501	499	self.stack = TaintableStack()
	500
	501	if bind_late is None:
	502	bind_late = []
502	503	self.bind_late = bind_late
503
	504
504	505	def code_object(self):
505	506	"""Walk self and produce a new code object."""
…	…
636	637	def visit_LOAD_DEREF(self, lo, hi):
637	638	if hasattr(self, '_func'):
638		name = self.co_freevars[lo + (hi << 8)]
	639	# name = self.co_freevars[lo + (hi << 8)]
639	640	value = self._func.func_closure[lo + (hi << 8)]
640	641	value = deref_cell(value)
…	…
707	708
708	709	Produce decompiled Python code (as a string) from a supplied lambda."""
	710
	711	def __init__(self, func, env=None):
	712	Visitor.__init__(self, func)
	713	if env is None:
	714	self.env = {}
	715	else:
	716	self.env = env.copy()
	717	import __builtin__
	718	self.env.update(vars(__builtin__))
	719	self.env.update(func.func_globals)
709	720
710	721	def code(self, include_func_header=True):
…	…
839	850	def visit_LOAD_CONST(self, lo, hi):
840	851	val = self.co_consts[lo + (hi << 8)]
	852	mod = getattr(val, "__module__", None)
841	853	if isinstance(val, (FunctionType, type)):
842		# The const in question is a factory function.
843		self.stack.append(val.__module__ + "." + val.__name__)
	854	# The const in question is a factory function, like int or date.
	855	name = val.__name__
	856	if name in self.env:
	857	term = name
	858	else:
	859	term = mod + "." + name
844	860	else:
845	861	term = repr(val)
846		if ~~hasattr(val, "__module~~__"):
847		if not term.startswith(~~val.__module__~~ + "."):
848		term = ~~val.__module__~~ + "." + term
849		self.stack.append(term)
850		if self.verbose:
851		self.debug(term)
	862	if mod and not mod.startswith("__"):
	863	if not term.startswith(mod + "."):
	864	term = mod + "." + term
	865	self.stack.append(term)
	866	if self.verbose:
	867	self.debug(term)
852	868
853	869	def visit_LOAD_FAST(self, lo, hi):

trunk/geniusql/decompile.py

r37	r38
	1	import datetime
1	2	from types import FunctionType
2		from ~~dejavu import~~ codewalk
	3	from geniusql import logic, codewalk
3	4
4	5
5	6	__all__ = [
6		'~~ConstWrapper', 'ColumnWrapper~~', 'Sentinel',
	7	'SQLExpression', 'Sentinel',
7	8	'cannot_represent', 'kw_arg', 'SQLDecompiler',
8		~~'AttributeDecompiler',~~
9	9	]
10	10
11	11
12		class ConstWrapper(str):
13		"""Wraps a constant for use in SQLDecompiler's stack.
14
15		When we hit LOAD_CONST while decompiling, we occasionally need to keep
16		both the base and the coerced value around (see COMPARE_OP for use
17		of ConstWrapper.basevalue).
	12	class SQLExpression(object):
	13	"""Wraps a column or other expression for use in SQLDecompiler's stack.
	14
	15	sql: the expression to be placed in the SQL for this expression.
	16	This should not contain an alias ("AS" clause); that will be
	17	provided by the consumer, usually via the 'name' attribute.
	18	name: the name of the expression; may be used as an alias (with "AS").
	19	value: If not None, the expression is a "constant"; that is, we already
	20	know its defined Python value (and that it does not have any basis
	21	in column values).
18	22	"""
19		def __new__(self, basevalue, coerced_value):
20		newobj = str.__new__(ConstWrapper, coerced_value)
21		newobj.basevalue = basevalue
22		return newobj
23
24
25		class ColumnWrapper(str):
26		"""Wraps a column for use in SQLDecompiler's stack.
27
28		When we hit LOAD_ATTR while decompiling, we occasionally need to keep
29		the column type around (see COMPARE_OP for use of ColumnWrapper.type).
30		"""
31		def __new__(self, value, col):
32		newobj = str.__new__(ColumnWrapper, value)
33		newobj.col = col
34		return newobj
	23
	24	def __init__(self, sql, name, dbtype, pytype, value=None):
	25	self.sql = sql
	26	self.name = name
	27
	28	self.dbtype = dbtype
	29	self.pytype = pytype
	30	self.imperfect_type = False
	31
	32	self.value = value
	33	self.aggregate = False
	34
	35	def __cmp__(self, other):
	36	if isinstance(other, SQLExpression):
	37	return cmp(self.sql, other.sql)
	38	raise TypeError("can't compare %s to %s" % (type(self), type(other)),
	39	other)
	40
	41	def __repr__(self):
	42	return "%s.%s(%r)" % (self.__module__, self.__class__.__name__,
	43	self.sql)
35	44
36	45
…	…
53	62
54	63	class SQLDecompiler(codewalk.LambdaDecompiler):
55		"""Produce SQL from a supplied Expression object.
56
57		Attributes of each argument in the ~~signature will be mapped to tabl~~e
58		~~columns. Keyword arguments should be bound using Expression.bind_args~~
59		before calling this decompiler.
	64	"""Produce SQL from a supplied logic.Expression object.
	65
	66	Attributes of each argument in the Expression's function signature
	67	will be mapped to table columns. Keyword arguments should be bound
	68	using Expression.bind_args before calling this decompiler.
60	69	"""
	70
	71	imperfect = False
61	72
62	73	# Some constants are function or class objects,
…	…
69	80	sql_cmp_op = ('<', '<=', '=', '!=', '>', '>=', 'in', 'not in')
70	81
71		def __init__(self, tables, expr, adapter):
	82	def __init__(self, tables, expr, adapter, typeadapter):
72	83	self.tables = tables
73	84	self.expr = expr
74	85	self.adapter = adapter
	86	self.typeadapter = typeadapter
	87
	88	self.groups = []
	89
75	90	# Cache coerced booleans
76		self.T = adapter.coerce_bool_to_any(True)
77		self.F = adapter.coerce_bool_to_any(False)
78		obj = expr.func
79		codewalk.LambdaDecompiler.__init__(self, obj)
	91	self.true_expr = self.const(True, self.adapter.bool_true)
	92	self.false_expr = self.const(False, self.adapter.bool_false)
	93	self.none_expr = SQLExpression("NULL", "expr0", None, type(None))
	94	self.T = self.const(True, adapter.coerce_bool_to_any(True))
	95	self.F = self.const(False, adapter.coerce_bool_to_any(False))
	96
	97	codewalk.LambdaDecompiler.__init__(self, expr.func)
	98
	99	exprcount = 0
	100
	101	def get_expr(self, sql, pytype):
	102	"""Return an SQLExpression for the given sql of the given pytype."""
	103	typer = self.typeadapter
	104	dbtype = typer.coerce({}, pytype)
	105
	106	self.exprcount += 1
	107	name = "expr%s" % self.exprcount
	108	e = SQLExpression(sql, name, dbtype, pytype)
	109
	110	reverse_type = typer.python_type(dbtype)
	111	e.imperfect_type = not typer.related(pytype, reverse_type)
	112
	113	return e
	114
	115	def const(self, value, sql=None):
	116	"""Return an SQLExpression for the given constant value."""
	117	if value is None:
	118	return self.none_expr
	119	if sql is None:
	120	sql = self.adapter.coerce(value)
	121	pytype = type(value)
	122
	123	e = self.get_expr(sql, pytype)
	124	e.value = value
	125	return e
80	126
81	127	def code(self):
	128	"""Walk self and return a suitable WHERE clause."""
82	129	self.imperfect = False
83	130	self.walk()
…	…
87	134	if result is cannot_represent:
88	135	# The entire expression could not be evaluated.
89		result = self.adapter.bool_true
90		if result == self.T:
91		result = self.adapter.bool_true
92		if result == self.F:
93		result = self.adapter.bool_false
94		return result
	136	result = self.true_expr
	137	elif result == self.T:
	138	result = self.true_expr
	139	elif result == self.F:
	140	result = self.false_expr
	141	return result.sql
	142
	143	_ignore_final_build = False
	144
	145	def field_list(self):
	146	"""Walk self and return a list of field objects."""
	147	self._ignore_final_build = True
	148	self.walk()
	149	return self.stack
95	150
96	151	def visit_instruction(self, op, lo=None, hi=None):
…	…
104	159	terms = self.targets.get(ip)
105	160	if terms:
106		~~trueval = self.adapter.bool_true~~
107		~~falseval = self.adapter.bool_false~~
108	161	clause = self.stack[-1]
109	162	while terms:
…	…
111	164	if term is cannot_represent:
112	165	# Use TRUE for the term, so all records are returned.
113		term = ~~trueval~~
	166	term = self.true_expr
114	167	if clause is cannot_represent:
115	168	# Use TRUE for the clause, so all records are returned.
116		clause = ~~trueval~~
	169	clause = self.true_expr
117	170
118	171	# Blurg. SQL Server is so picky.
119	172	if term == self.T:
120		term = ~~trueval~~
	173	term = self.true_expr
121	174	elif term == self.F:
122		term = ~~falseval~~
	175	term = self.false_expr
123	176	if clause == self.T:
124		clause = ~~trueval~~
	177	clause = self.true_expr
125	178	elif clause == self.F:
126		clause = ~~falseval~~
	179	clause = self.false_expr
127	180
128		clause = "(%s) %s (%s)" % (term, oper.upper(), clause)
	181	clause = self.get_expr("(%s) %s (%s)" %
	182	(term.sql, oper.upper(), clause.sql),
	183	bool)
129	184
130	185	# Replace TOS with the new clause, so that further
…	…
132	187	self.stack[-1] = clause
133	188	if self.verbose:
134		self.debug("clause:", clause, "\n")
	189	self.debug("clause:", clause.sql, "\n")
135	190
136	191	if op == 1:
…	…
170	225	alias, table = tos
171	226	col = table[name]
172		atom = ColumnWrapper('%s.%s' % (alias, col.qname), col)
	227	atom = SQLExpression('%s.%s' % (alias, col.qname),
	228	name, col.dbtype, col.pytype)
	229	atom.imperfect_type = col.imperfect_type
173	230	else:
174	231	# 'tos.name' will reference an attribute of the tos object.
…	…
181	238	val = self.co_consts[lo + (hi << 8)]
182	239	if not isinstance(val, self.no_coerce):
183		val = ~~ConstWrapper(val, self.adapter.coerce(val)~~)
	240	val = self.const(val)
184	241	self.stack.append(val)
185	242
186	243	def visit_BUILD_TUPLE(self, lo, hi):
187		terms = ", ".join([self.stack.pop() for i in range(lo + hi << 8)])
188		self.stack.append("(" + terms + ")")
	244	if self.cursor == len(self._bytecode) - 1 and self._ignore_final_build:
	245	# When building a field list, ignore the last BUILD_TUPLE.
	246	return
	247
	248	terms = ", ".join([self.stack.pop().sql
	249	for i in range(lo + (hi << 8))])
	250	self.stack.append(SQLExpression("(" + terms + ")", "tuple",
	251	None, None, True))
189	252
190	253	visit_BUILD_LIST = visit_BUILD_TUPLE
…	…
196	259	key = self.stack.pop()
197	260	kwargs[key] = val
198		kwargs = [k ~~+ "=" + v~~ for k, v in kwargs.iteritems()]
	261	kwargs = [k.sql + "=" + v.sql for k, v in kwargs.iteritems()]
199	262
200	263	args = []
…	…
215	278	if dispatch:
216	279	self.stack.append(dispatch(tos, *args))
	280	return
	281	elif logic.builtins.get(func.__name__, None) is func:
	282	dispatch = getattr(self, "builtins_" + func.__name__, None)
	283	if dispatch:
	284	self.stack.append(dispatch(*args))
217	285	return
218	286	else:
…	…
239	307	value = self.containedby(op1, op2)
240	308	if op == 7:
241		value ~~= "NOT " + value~~
	309	value.sql = "NOT " + value.sql
242	310	self.stack.append(value)
243		elif op1 == 'NULL':
	311	elif op1.sql == 'NULL':
244	312	if op in (2, 8): # '==', is
245		self.stack.append(~~op2 + " IS NULL"~~)
	313	self.stack.append(self.get_expr(op2.sql + " IS NULL", bool))
246	314	elif op in (3, 9): # '!=', 'is not'
247		self.stack.append(~~op2 + " IS NOT NULL"~~)
	315	self.stack.append(self.get_expr(op2.sql + " IS NOT NULL", bool))
248	316	else:
249	317	raise ValueError("Non-equality Null comparisons not allowed.")
250		elif op2 == 'NULL':
	318	elif op2.sql == 'NULL':
251	319	if op in (2, 8): # '==', 'is'
252		self.stack.append(~~op1 + " IS NULL"~~)
	320	self.stack.append(self.get_expr(op1.sql + " IS NULL", bool))
253	321	elif op in (3, 9): # '!=', 'is not'
254		self.stack.append(~~op1 + " IS NOT NULL"~~)
	322	self.stack.append(self.get_expr(op1.sql + " IS NOT NULL", bool))
255	323	else:
256	324	raise ValueError("Non-equality Null comparisons not allowed.")
…	…
263	331	return
264	332	# Comparison operators for strings are case-sensitive in PG et al.
265		self.stack.append(op1 + " " + self.sql_cmp_op[op] + " " + op2)
	333	e = op1.sql + " " + self.sql_cmp_op[op] + " " + op2.sql
	334	self.stack.append(self.get_expr(e, bool))
266	335
267	336	def _compare_constants(self, op1, op2):
…	…
271	340	adapter function is available, a TypeError is raised.
272	341	"""
273		~~col = getattr(op1, "col", None)~~
274		~~if col~~:
275		~~if isinstance(op2, ConstWrapper):~~
276		if ~~col~~.imperfect_type:
	342	if op1.value is None:
	343	if op2.value is not None:
	344	# op2 is a constant
	345	if op1.imperfect_type:
277	346	# Try to cast the column to op2's type
278		op1 = self.adapter.cast(op1, col.dbtype,
279		type(op2.basevalue))
	347	op1.sql = self.adapter.cast(op1, op1.dbtype, op2.pytype)
280	348	else:
281	349	# Try to coerce op2 to the column's type
282		op2 = self.adapter.coerce(op2.basevalue, col.dbtype)
283		else:
284		col = getattr(op2, "col", None)
285		if col and isinstance(op1, ConstWrapper):
286		if col.imperfect_type:
287		# Try to cast the column to op1's type
288		op2 = self.adapter.cast(op2, col.dbtype,
289		type(op1.basevalue))
290		else:
291		# Try to coerce op1 to the column's type
292		op1 = self.adapter.coerce(op1.basevalue, col.dbtype)
	350	op2.sql = self.adapter.coerce(op2.value, op1.dbtype)
	351	else:
	352	if op2.imperfect_type:
	353	# Try to cast the column to op1's type
	354	op2.sql = self.adapter.cast(op2, op2.dbtype, op1.pytype)
	355	else:
	356	# Try to coerce op1 to the column's type
	357	op1.sql = self.adapter.coerce(op1.value, op2.dbtype)
293	358	return op1, op2
294
295		~~def binary_op(self, op):~~
296		~~op2, op1 = self.stack.pop(), self.stack.pop()~~
297		~~self.stack.append(op1 + " " + op + " " + op2)~~
298	359
299	360	def visit_BINARY_SUBSCR(self):
…	…
305	366	% (name, tos))
306	367	# name, since formed in LOAD_CONST, may have extraneous quotes.
307		name = name.strip("'\"")
	368	name = name.sql.strip("'\"")
308	369	value = self.expr.kwargs[name]
309	370	if not isinstance(value, self.no_coerce):
310		value = ~~ConstWrapper(value, self.adapter.coerce(value)~~)
	371	value = self.const(value)
311	372	self.stack.append(value)
312	373
…	…
316	377	self.stack.append(cannot_represent)
317	378	else:
318		self.stack.append(~~"NOT (" + op + ")"~~)
	379	self.stack.append(self.get_expr("NOT (" + op.sql + ")", bool))
319	380
320	381	# --------------------------- Dispatchees --------------------------- #
321	382
322	383	def attr_startswith(self, tos, arg):
323		return ~~tos + " LIKE '" + self.adapter.escape_like(arg) + "%'"~~
	384	return self.get_expr(tos.sql + " LIKE '" + self.adapter.escape_like(arg.sql) + "%'", bool)
324	385
325	386	def attr_endswith(self, tos, arg):
326		return ~~tos + " LIKE '%" + self.adapter.escape_like(arg) + "'"~~
	387	return self.get_expr(tos.sql + " LIKE '%" + self.adapter.escape_like(arg.sql) + "'", bool)
327	388
328	389	def containedby(self, op1, op2):
329		if ~~isinstance(op1, ConstWrapper)~~:
	390	if op1.value is not None:
330	391	# Looking for text in a field. Use Like (reverse terms).
331		return op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%'"
	392	like = self.adapter.escape_like(op1.sql)
	393	return self.get_expr(op2.sql + " LIKE '%" + like + "%'", bool)
332	394	else:
333	395	# Looking for field in (a, b, c)
334		atoms = [self.adapter.coerce(x) for x in op2.~~base~~value]
	396	atoms = [self.adapter.coerce(x) for x in op2.value]
335	397	if atoms:
336		return ~~op1 + " IN (" + ", ".join(atoms) + ")"~~
	398	return self.get_expr(op1.sql + " IN (" + ", ".join(atoms) + ")", bool)
337	399	else:
338	400	# Nothing will match the empty list, so return none.
339		return self.~~adapter.bool_false~~
340
341		def ~~dejavu~~_icontainedby(self, op1, op2):
342		if ~~isinstance(op1, ConstWrapper)~~:
	401	return self.false_expr
	402
	403	def builtins_icontainedby(self, op1, op2):
	404	if op1.value is not None:
343	405	# Looking for text in a field. Use Like (reverse terms).
344		return ("LOWER(" + op2 + ") LIKE '%" +
345		self.adapter.escape_like(op1).lower() + "%'")
	406	return self.get_expr("LOWER(" + op2.sql + ") LIKE '%" +
	407	self.adapter.escape_like(op1.sql).lower()
	408	+ "%'", bool)
346	409	else:
347	410	# Looking for field in (a, b, c).
348	411	# Force all args to lowercase for case-insensitive comparison.
349		atoms = [self.adapter.coerce(x).lower() for x in op2.basevalue]
350		return "LOWER(%s) IN (%s)" % (op1, ", ".join(atoms))
351
352		def dejavu_icontains(self, x, y):
353		return self.dejavu_icontainedby(y, x)
354
355		def dejavu_istartswith(self, x, y):
356		return "LOWER(" + x + ") LIKE '" + self.adapter.escape_like(y) + "%'"
357
358		def dejavu_iendswith(self, x, y):
359		return "LOWER(" + x + ") LIKE '%" + self.adapter.escape_like(y) + "'"
360
361		def dejavu_ieq(self, x, y):
362		return "LOWER(" + x + ") = LOWER(" + y + ")"
363
364		def dejavu_now(self):
365		return "NOW()"
366
367		def dejavu_today(self):
368		return "CURRENT_DATE"
369
370		def dejavu_year(self, x):
371		return "YEAR(" + x + ")"
372
373		def dejavu_month(self, x):
374		return "MONTH(" + x + ")"
375
376		def dejavu_day(self, x):
377		return "DAY(" + x + ")"
	412	atoms = [self.adapter.coerce(x).lower() for x in op2.value]
	413	return self.get_expr("LOWER(%s) IN (%s)" %
	414	(op1.sql, ", ".join(atoms)), bool)
	415
	416	def builtins_icontains(self, x, y):
	417	return self.builtins_icontainedby(y, x)
	418
	419	def builtins_istartswith(self, x, y):
	420	return self.get_expr("LOWER(" + x.sql + ") LIKE '" +
	421	self.adapter.escape_like(y.sql) + "%'", bool)
	422
	423	def builtins_iendswith(self, x, y):
	424	return self.get_expr("LOWER(" + x.sql + ") LIKE '%" +
	425	self.adapter.escape_like(y.sql) + "'", bool)
	426
	427	def builtins_ieq(self, x, y):
	428	return self.get_expr("LOWER(" + x.sql + ") = LOWER(" + y.sql + ")", bool)
	429
	430	def builtins_now(self):
	431	"""Return a datetime.datetime for the current time in the local TZ."""
	432	return self.get_expr("NOW()", datetime.datetime)
	433
	434	def builtins_utcnow(self):
	435	"""Return a datetime.datetime for the current time in the UTC TZ."""
	436	return self.get_expr("NOW()", datetime.datetime)
	437
	438	def builtins_today(self):
	439	return self.get_expr("CURRENT_DATE", datetime.date)
	440
	441	def builtins_year(self, x):
	442	return self.get_expr("YEAR(" + x.sql + ")", int)
	443
	444	def builtins_month(self, x):
	445	return self.get_expr("MONTH(" + x.sql + ")", int)
	446
	447	def builtins_day(self, x):
	448	return self.get_expr("DAY(" + x.sql + ")", int)
378	449
379	450	def func__builtin___len(self, x):
380		return "LENGTH(" + x + ")"
381
382
383		# ----------------------- Attribute Decompilation ----------------------- #
384
385
386		class ColumnExpr(object):
387		"""Wraps a column for use in AttributeDecompiler's stack.
388
389		When we hit LOAD_ATTR while decompiling, we need to keep the column
390		metadata around.
391
392		expr: the expression to be used for this column in the SELECT clause.
393		This should not contain an alias ("AS" clause); that will be
394		provided by the consumer, usually via the 'key' attribute.
395		"""
396
397		def __init__(self, table, col, key, expr):
398		self.table = table
399		self.col = col
400		self.key = key
401		self.alias = key
402		self.expr = expr
403		self.aggregate = False
404
405
406		class AttributeDecompiler(codewalk.Visitor):
407		"""Produce an SQL source list from a supplied Expression object.
408
409		Attributes of each argument in the signature will be mapped to table
410		columns. Keyword arguments should be bound using Expression.bind_args
411		before calling this decompiler.
412
413		After walk(), self.stack should be reduced to a list of ColumnExpr's.
414		"""
415
416		# Some constants are function or class objects,
417		# which should not be coerced.
418		no_coerce = (FunctionType,
419		type,
420		type(len), # <type 'builtin_function_or_method'>
421		)
422
423		sql_cmp_op = ('<', '<=', '=', '!=', '>', '>=', 'in', 'not in')
424
425		def __init__(self, tables, expr, adapter):
426		self.tables = tables
427		self.expr = expr
428		self.adapter = adapter
429		self.groups = []
430		codewalk.Visitor.__init__(self, expr.func)
431
432		def walk(self):
433		self.stack = []
434		codewalk.Visitor.walk(self)
435
436		def visit_LOAD_DEREF(self, lo, hi):
437		raise ValueError("Illegal reference found in %s." % self.expr)
438
439		def visit_LOAD_GLOBAL(self, lo, hi):
440		raise ValueError("Illegal global found in %s." % self.expr)
441
442		def visit_LOAD_FAST(self, lo, hi):
443		arg_index = lo + (hi << 8)
444		if arg_index < self.co_argcount:
445		# We've hit a reference to a positional arg, which in our case
446		# implies a reference to a DB table. Append the (qname, table)
447		# tuple for later unpacking inside visit_LOAD_ATTR.
448		self.stack.append(self.tables[arg_index])
449		else:
450		# Since lambdas don't support local bindings,
451		# any remaining local name must be a keyword arg.
452		raise ValueError("Illegal keyword arg found in %s." % self.expr)
453
454		def visit_LOAD_ATTR(self, lo, hi):
455		name = self.co_names[lo + (hi << 8)]
456		tos = self.stack.pop()
457		if isinstance(tos, tuple):
458		# The name in question refers to a DB column (see visit_LOAD_FAST).
459		alias, table = tos
460		col = table[name]
461		atom = ColumnExpr(table, col, name, '%s.%s' % (alias, col.qname))
462		else:
463		# 'tos.name' will reference an attribute of the tos object.
464		# Stick the tos and name in a tuple for later processing
465		# (for example, in visit_CALL_FUNCTION).
466		atom = (tos, name)
467		self.stack.append(atom)
468
469		def visit_LOAD_CONST(self, lo, hi):
470		val = self.co_consts[lo + (hi << 8)]
471		if not isinstance(val, self.no_coerce):
472		val = ConstWrapper(val, self.adapter.coerce(val))
473		self.stack.append(val)
474
475		def visit_CALL_FUNCTION(self, lo, hi):
476		kwargs = {}
477		for i in xrange(hi):
478		val = self.stack.pop()
479		key = self.stack.pop()
480		kwargs[key] = val
481		kwargs = [k + "=" + v for k, v in kwargs.iteritems()]
482
483		args = []
484		for i in xrange(lo):
485		arg = self.stack.pop()
486		args.append(arg)
487		args.reverse()
488
489		if kwargs:
490		args += kwargs
491
492		func = self.stack.pop()
493
494		# Handle function objects.
495		funcname = func.__module__ + "_" + func.__name__
496		funcname = funcname.replace(".", "_")
497		if funcname.startswith("_"):
498		funcname = "func" + funcname
499		dispatch = getattr(self, funcname)
500		self.stack.append(dispatch(*args))
501
502		def visit_UNARY_NOT(self):
503		op = self.stack.pop()
504		self.stack.append("NOT (" + op + ")")
505
506		# --------------------------- Dispatchees --------------------------- #
507
508		def dejavu_day(self, x):
509		x.alias = "day_%s" % x.alias
510		x.expr = "DAY(" + x.expr + ")"
511		return x
	451	return self.get_expr("LENGTH(" + x.sql + ")", int)
512	452
513	453	def func__builtin___min(self, x):
514	454	x.aggregate = True
515		x.~~alias = "min_%s" % x.alias~~
516		x.~~expr = "MIN(" + x.expr~~ + ")"
	455	x.name = "min_%s" % x.name
	456	x.sql = "MIN(" + x.sql + ")"
517	457	return x
518	458
519	459	def func__builtin___max(self, x):
520	460	x.aggregate = True
521		x.~~alias = "max_%s" % x.alias~~
522		x.~~expr = "MAX(" + x.expr~~ + ")"
	461	x.name = "max_%s" % x.name
	462	x.sql = "MAX(" + x.sql + ")"
523	463	return x
524	464
525		def geniusql_decompile_count(self, x):
526		x.aggregate = True
527		x.alias = "count_%s" % x.alias
528		x.expr = "COUNT(" + x.expr + ")"
529		return x
530
	465	def builtins_count(self, x):
	466	e = self.get_expr("COUNT(" + x.sql + ")", int)
	467	e.aggregate = True
	468	return e
	469
	470	# Binary operations #
	471
	472	# So, the question is, do we really want to support this level of detail
	473	# in attribute expressions or do we want to tell users to move all that
	474	# to Python? I think we do want it in SQL, because that's the only way
	475	# to efficiently support INSERT INTO. But it means a lot of work and
	476	# a lot of database-specific adaptation to deal with implicit typing. :(
	477
	478	# Resultant type for a binary operation between two types.
	479	result_type = {}
	480
	481	def binary_op(self, op):
	482	op2, op1 = self.stack.pop(), self.stack.pop()
	483
	484	# re-use op1
	485	op1.pytype = self.result_type[(op1.pytype, op, op2.pytype)]
	486	op1.sql = "%s %s %s" % (op1.sql, op, op2.sql)
	487	if not op1.name.startswith("expr_"):
	488	op1.name = "expr_%s" % op1.name
	489	self.stack.append(op1)
	490
	491	# Add visit_BINARY_* methods.
	492	for k, v in codewalk.binary_repr.iteritems():
	493	setattr(SQLDecompiler, "visit_" + k,
	494	lambda self, op=v: self.binary_op(op))
	495	del k, v
	496
	497	def _binary_operation_result_types():
	498	"""Return a dict of (type(A), op, type(B)): type(op(A, B)) for known types."""
	499	results = {}
	500
	501	knowntypes = [3, 3L, 3.0, 'a', u'b']
	502	try:
	503	import datetime
	504	knowntypes.extend([datetime.date(2004, 1, 1),
	505	datetime.datetime(2004, 1, 31),
	506	datetime.timedelta(3)])
	507	except ImportError:
	508	pass
	509
	510	try:
	511	import decimal
	512	knowntypes.append(decimal.Decimal(3))
	513	except ImportError:
	514	pass
	515
	516	ops = [(symbol, codewalk.binary_operators[name])
	517	for name, symbol in codewalk.binary_repr.iteritems()]
	518
	519	for A in knowntypes:
	520	for B in knowntypes:
	521	for symbol, op in ops:
	522	try:
	523	result = op(A, B)
	524	except TypeError:
	525	pass
	526	else:
	527	results[(type(A), symbol, type(B))] = type(result)
	528
	529	return results
	530	SQLDecompiler.result_type = _binary_operation_result_types()
	531

trunk/geniusql/logic.py

r37	r38
143	143
144	144	Therefore, code which uses this module must determine which objects
145		will be referenced as Expressions are unpickled. Any that are neither
146		builtins nor in this module's globals() need to be injected into this
147		module, so they can be referenced in eval() when the Expression is
148		unpickled.
149
	145	will be referenced as Expressions are unpickled. Any that are not
	146	builtins need to be added to this module's "builtins" dict, so they
	147	can be referenced in eval() when the Expression is unpickled.
150	148	"""
151	149
…	…
154	152	from types import CodeType, FunctionType
155	153
156		# Globals which assist in unpickling. If they're not present (can't be
157		# imported), that's OK--someone might want to build an app which
158		# doesn't use fixedpoints, for example.
159		import datetime
160
161		try:
162		import fixedpoint
163		from fixedpoint import FixedPoint
164		except ImportError:
165		pass
166
167		try:
168		import decimal
169		from decimal import Decimal
170		except ImportError:
171		pass
172
173		from dejavu import codewalk
	154	builtins = {}
	155
	156	def _init():
	157	"""Add standard builtins to assist in unpickling.
	158
	159	If they're not present (can't be imported), that's OK--someone might
	160	want to build an app which doesn't use fixedpoints, for example.
	161	"""
	162	try:
	163	import datetime
	164	builtins['datetime'] = datetime
	165	except ImportError:
	166	pass
	167
	168	try:
	169	import fixedpoint
	170	from fixedpoint import FixedPoint
	171	builtins['fixedpoint'] = fixedpoint
	172	builtins['FixedPoint'] = FixedPoint
	173	except ImportError:
	174	pass
	175
	176	try:
	177	import decimal
	178	from decimal import Decimal
	179	builtins['decimal'] = decimal
	180	builtins['Decimal'] = Decimal
	181	except ImportError:
	182	pass
	183	_init()
	184
	185
	186	from geniusql import codewalk
174	187
175	188
…	…
254	267	self.func = func
255	268
	269	# I can't believe this actually works (knock on wood).
	270	self.func.func_globals.update(builtins)
	271
256	272	if kwtypes is None:
257	273	kwtypes = {}
…	…
261	277	def _load_func(self, func):
262	278	# Early-bind as much as possible.
263		binder = codewalk.EarlyBinder(func, bind_late=[datetime.datetime.now,
264		datetime.date.today])
	279	binder = codewalk.EarlyBinder(func)
265	280	self.func = binder.function()
266	281
…	…
268	283	"""Return source code for self.func."""
269	284	if hasattr(self, 'func'):
270		return codewalk.LambdaDecompiler(self.func).code()
	285	decom = codewalk.LambdaDecompiler(self.func, env=builtins)
	286	return decom.code()
271	287	else:
272	288	return 'function not yet loaded'
…	…
323	339	# Any func_globals at the time of pickling are lost, so any
324	340	# late-bound objects must be available at this point. Any
325		# such objects need to be injected into logic~~'s globals()~~
	341	# such objects need to be injected into logic.builtins
326	342	# if you want them to be available here.
327		f = eval(func)
	343	f = eval(func, builtins)
328	344	self._load_func(f)
329	345

trunk/geniusql/objects.py

r37	r38
341	341	tpair = [(self.qname, self)]
342	342	decom = self.schema.db.decompiler(tpair, logic.filter(**inputs),
343		self.schema.db.adaptertosql)
	343	self.schema.db.adaptertosql,
	344	self.schema.db.typeadapter)
344	345	code = decom.code()
345	346	if decom.imperfect:
…	…
640	641	col.autoincrement = autoincrement
641	642
	643	typer = self.db.typeadapter
642	644	if dbtype is None:
643		col.dbtype = ~~self.db.typeadapter.coerce(col~~, pytype)
644		pytype2 = ~~self.db.typeadapt~~er.python_type(col.dbtype)
645		col.imperfect_type = not ~~self.db.typeadapt~~er.related(pytype, pytype2)
	645	col.dbtype = typer.coerce(col.hints, pytype)
	646	pytype2 = typer.python_type(col.dbtype)
	647	col.imperfect_type = not typer.related(pytype, pytype2)
646	648
647	649	return col
…	…
772	774	typeadapter = adapters.TypeAdapter()
773	775	decompiler = decompile.SQLDecompiler
774		~~attributedecompiler = decompile.AttributeDecompiler~~
775	776	joinwrapper = select.TableWrapper
776	777	selectwriter = select.SelectWriter

trunk/geniusql/providers/ado.py

r37	r38
25	25	import pywintypes
26	26	import datetime
	27	datetypes = (datetime.date, datetime.datetime)
	28
27	29	import time
28	30
…	…
136	138	encoding = 'ISO-8859-1'
137	139
	140	def coerce_any_to_datetime_timedelta(self, value):
	141	if isinstance(value, pywintypes.TimeType):
	142	# For some reason, we need both float and int.
	143	return datetime.timedelta(int(float(value)))
	144
	145	return adapters.AdapterFromDB.coerce_any_to_datetime_timedelta(self, value)
	146
138	147	def coerce_any_to_datetime_datetime(self, value):
139	148	# Illegal Date/Time values will crash the
…	…
245	254	# doesn't seem to be a way around it). Use icontainedby
246	255	# and just mark imperfect.
247		value = self.~~dejavu~~_icontainedby(op1, op2)
	256	value = self.builtins_icontainedby(op1, op2)
248	257	if op == 7:
249		value ~~= "NOT " + value~~
	258	value.sql = "NOT " + value.sql
250	259	self.stack.append(value)
251	260	self.imperfect = True
252		elif op1 == 'NULL':
	261	elif op1.sql == 'NULL':
253	262	if op in (2, 8): # '==', is
254		self.stack.append(~~op2 + " IS NULL"~~)
	263	self.stack.append(self.get_expr(op2.sql + " IS NULL", bool))
255	264	elif op in (3, 9): # '!=', 'is not'
256		self.stack.append(~~op2 + " IS NOT NULL"~~)
	265	self.stack.append(self.get_expr(op2.sql + " IS NOT NULL", bool))
257	266	else:
258	267	raise ValueError("Non-equality Null comparisons not allowed.")
259		elif op2 == 'NULL':
	268	elif op2.sql == 'NULL':
260	269	if op in (2, 8): # '==', 'is'
261		self.stack.append(~~op1 + " IS NULL"~~)
	270	self.stack.append(self.get_expr(op1.sql + " IS NULL", bool))
262	271	elif op in (3, 9): # '!=', 'is not'
263		self.stack.append(~~op1 + " IS NOT NULL"~~)
	272	self.stack.append(self.get_expr(op1.sql + " IS NOT NULL", bool))
264	273	else:
265	274	raise ValueError("Non-equality Null comparisons not allowed.")
…	…
272	281	return
273	282
274		if (isinstance(op2, decompile.~~ConstWrapper~~)
275		and is~~instance(op2.basevalu~~e, basestring)):
	283	if (isinstance(op2, decompile.SQLExpression)
	284	and issubclass(op2.pytype, basestring)):
276	285	atom = self._compare_strings(op1, op, op2)
277		if atom:
	286	if atom is not None:
278	287	self.stack.append(atom)
279	288	return
280		self.stack.append(op1 + " " + self.sql_cmp_op[op] + " " + op2)
	289
	290	e = op1.sql + " " + self.sql_cmp_op[op] + " " + op2.sql
	291	self.stack.append(self.get_expr(e, bool))
281	292
282	293	def _compare_strings(self, op1, op, op2):
…	…
293	304	def attr_startswith(self, tos, arg):
294	305	self.imperfect = True
295		return ~~tos + " LIKE '" + self.adapter.escape_like(arg) + "%'"~~
	306	return self.get_expr(tos.sql + " LIKE '" + self.adapter.escape_like(arg.sql) + "%'", bool)
296	307
297	308	def attr_endswith(self, tos, arg):
298	309	self.imperfect = True
299		return ~~tos + " LIKE '%" + self.adapter.escape_like(arg) + "'"~~
	310	return self.get_expr(tos.sql + " LIKE '%" + self.adapter.escape_like(arg.sql) + "'", bool)
300	311
301	312	def containedby(self, op1, op2):
302	313	self.imperfect = True
303		if isinstance(op1, decompile.ConstWrapper):
	314	return decompile.SQLDecompiler.containedby(self, op1, op2)
	315
	316	def builtins_icontainedby(self, op1, op2):
	317	# LIKE is already case-insensitive in MS SQL Server;
	318	# so don't use LOWER().
	319	if op1.value is not None:
304	320	# Looking for text in a field. Use Like (reverse terms).
305		return op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%'"
	321	return self.get_expr(op2.sql + " LIKE '%" +
	322	self.adapter.escape_like(op1.sql)
	323	+ "%'", bool)
306	324	else:
307	325	# Looking for field in (a, b, c)
308		atoms = [self.adapter.coerce(x) for x in op2.~~base~~value]
	326	atoms = [self.adapter.coerce(x) for x in op2.value]
309	327	if atoms:
310		return op1 + " IN (" + ", ".join(atoms) + ")"
	328	return self.get_expr("%s IN (%s)" %
	329	(op1.sql, ", ".join(atoms)), bool)
311	330	else:
312	331	# Nothing will match the empty list, so return none.
313		return self.adapter.bool_false
314
315		def dejavu_icontainedby(self, op1, op2):
316		if isinstance(op1, decompile.ConstWrapper):
317		# Looking for text in a field. Use Like (reverse terms).
318		# LIKE is already case-insensitive in MS SQL Server;
319		# so don't use LOWER().
320		value = op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%'"
321		else:
322		# Looking for field in (a, b, c)
323		atoms = [self.adapter.coerce(x) for x in op2.basevalue]
324		if atoms:
325		return op1 + " IN (" + ", ".join(atoms) + ")"
326		else:
327		# Nothing will match the empty list, so return none.
328		return self.adapter.bool_false
	332	return self.adapter.false_expr
329	333	return value
330	334
331		def ~~dejavu~~_istartswith(self, x, y):
	335	def builtins_istartswith(self, x, y):
332	336	# Like is already case-insensitive in ADO; so don't use LOWER().
333		return ~~x + " LIKE '" + self.adapter.escape_like(y) + "%'"~~
334
335		def ~~dejavu~~_iendswith(self, x, y):
	337	return self.get_expr(x.sql + " LIKE '" + self.adapter.escape_like(y.sql) + "%'", bool)
	338
	339	def builtins_iendswith(self, x, y):
336	340	# Like is already case-insensitive in ADO; so don't use LOWER().
337		return ~~x + " LIKE '%" + self.adapter.escape_like(y) + "'"~~
338
339		def ~~dejavu~~_ieq(self, x, y):
	341	return self.get_expr(x.sql + " LIKE '%" + self.adapter.escape_like(y.sql) + "'", bool)
	342
	343	def builtins_ieq(self, x, y):
340	344	# = is already case-insensitive in ADO.
341		return ~~x + " = " + y~~
342
343		def ~~dejavu~~_now(self):
344		return ~~"getdate()"~~
345
346		def ~~dejavu~~_today(self):
347		return ~~"DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)"~~
348
349		def ~~dejavu~~_year(self, x):
350		return ~~"DATEPART(year, " + x + ")"~~
351
352		def ~~dejavu~~_month(self, x):
353		return ~~"DATEPART(month, " + x + ")"~~
354
355		def ~~dejavu~~_day(self, x):
356		return ~~"DATEPART(day, " + x + ")"~~
	345	return self.get_expr(x.sql + " = " + y.sql, bool)
	346
	347	def builtins_now(self):
	348	return self.get_expr("getdate()", datetime.datetime)
	349
	350	def builtins_today(self):
	351	return self.get_expr("DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)", datetime.date)
	352
	353	def builtins_year(self, x):
	354	return self.get_expr("DATEPART(year, " + x.sql + ")", int)
	355
	356	def builtins_month(self, x):
	357	return self.get_expr("DATEPART(month, " + x.sql + ")", int)
	358
	359	def builtins_day(self, x):
	360	return self.get_expr("DATEPART(day, " + x.sql + ")", int)
357	361
358	362	def func__builtin___len(self, x):
359		return ~~"Len(" + x + ")"~~
	363	return self.get_expr("Len(" + x.sql + ")", int)
360	364
361	365
…	…
784	788	# Some operations on strings can be emulated with the
785	789	# Convert function.
786		return ("Convert(binary, %s) %s Convert(binary, %s)" %
787		(op1, self.sql_cmp_op[op], op2))
	790	return self.get_expr("Convert(binary, %s) %s Convert(binary, %s)"
	791	% (op1.sql, self.sql_cmp_op[op], op2.sql),
	792	bool)
788	793	else:
789	794	return ADOSQLDecompiler._compare_strings(self, op1, op, op2)
	795
	796	def binary_op(self, op):
	797	op2, op1 = self.stack.pop(), self.stack.pop()
	798	t1, t2 = op1.pytype, op2.pytype
	799
	800	# re-use op1
	801	op1.pytype = self.result_type[(t1, op, t2)]
	802	if t1 in datetypes and t2 in datetypes:
	803	if op == "-":
	804	op1.sql = "CAST(DATEDIFF(ms, %s, %s) AS DOUBLE)" % (op2.sql, op1.sql)
	805	elif op == "+":
	806	op1.sql = "DATEADD(ms, %s, %s)" % (op1.sql, op2.sql)
	807	else:
	808	raise TypeError("unsupported operand type(s) for %s: "
	809	"%r and %r" % (op, t1, t2))
	810	else:
	811	op1.sql = "%s %s %s" % (op1.sql, op, op2.sql)
	812	if not op1.name.startswith("expr_"):
	813	op1.name = "expr_%s" % op1.name
	814	self.stack.append(op1)
	815
	816	def builtins_day(self, x):
	817	return self.get_expr("DATEPART(day, " + x.sql + ")", int)
790	818
791	819
…	…
821	849	numeric_max_bytes = 6
822	850
823		def coerce_bool(self, ~~col~~):
	851	def coerce_bool(self, hints):
824	852	return "BIT"
825	853
826		def coerce_datetime_datetime(self, ~~col~~):
	854	def coerce_datetime_datetime(self, hints):
827	855	return "DATETIME"
828	856
829		def coerce_datetime_date(self, ~~col~~):
	857	def coerce_datetime_date(self, hints):
830	858	return "DATETIME"
831	859
832		def coerce_datetime_time(self, ~~col~~):
	860	def coerce_datetime_time(self, hints):
833	861	return "DATETIME"
834	862
…	…
846	874	return "NUMERIC(%s, 0)" % (bytes * 2)
847	875
848		def coerce_str(self, ~~col~~):
	876	def coerce_str(self, hints):
849	877	# The bytes hint does not reflect the usual 4-byte base for varchar.
850		bytes = int(~~col.~~hints.get('bytes', 255))
	878	bytes = int(hints.get('bytes', 255))
851	879
852	880	if bytes == 0 or bytes > 8000:
…	…
987	1015	# StrComp function. Oddly enough, "StrComp(x, y) op 0"
988	1016	# is the same as "x op y" in most cases.
989		return "StrComp(%s, %s) %s 0" % (op1, op2, self.sql_cmp_op[op])
	1017	return self.get_expr("StrComp(%s, %s) %s 0" %
	1018	(op1.sql, op2.sql, self.sql_cmp_op[op]),
	1019	bool)
990	1020	else:
991	1021	return ADOSQLDecompiler._compare_strings(self, op1, op, op2)
992	1022
993		def ~~dejavu~~_now(self):
994		return ~~"Now()"~~
995
996		def ~~dejavu~~_today(self):
997		return ~~"DateValue(Now())"~~
998
999		def ~~dejavu~~_year(self, x):
1000		return ~~"Year(" + x + ")"~~
1001
1002		def ~~dejavu~~_month(self, x):
1003		return ~~"Month(" + x + ")"~~
1004
1005		def ~~dejavu~~_day(self, x):
1006		return ~~"Day(" + x + ")"~~
	1023	def builtins_now(self):
	1024	return self.get_expr("Now()", datetime.datetime)
	1025
	1026	def builtins_today(self):
	1027	return self.get_expr("DateValue(Now())", datetime.date)
	1028
	1029	def builtins_year(self, x):
	1030	return self.get_expr("Year(" + x.sql + ")", int)
	1031
	1032	def builtins_month(self, x):
	1033	return self.get_expr("Month(" + x.sql + ")", int)
	1034
	1035	def builtins_day(self, x):
	1036	return self.get_expr("Day(" + x.sql + ")", int)
1007	1037
1008	1038
…	…
1021	1051	})
1022	1052
1023		def coerce_bool(self, ~~col~~): return "BIT"
1024
1025		def coerce_datetime_datetime(self, ~~col~~): return "DATETIME"
1026		def coerce_datetime_date(self, ~~col~~): return "DATETIME"
1027		def coerce_datetime_time(self, ~~col~~): return "DATETIME"
	1053	def coerce_bool(self, hints): return "BIT"
	1054
	1055	def coerce_datetime_datetime(self, hints): return "DATETIME"
	1056	def coerce_datetime_date(self, hints): return "DATETIME"
	1057	def coerce_datetime_time(self, hints): return "DATETIME"
1028	1058
1029	1059	def int_type(self, bytes):
…	…
1036	1066	return "DECIMAL"
1037	1067
1038		def coerce_str(self, ~~col~~):
	1068	def coerce_str(self, hints):
1039	1069	# The bytes hint shall not reflect the usual 4-byte base for varchar.
1040		bytes = int(~~col.~~hints.get('bytes', 255))
	1070	bytes = int(hints.get('bytes', 255))
1041	1071
1042	1072	# 255 chars is the upper limit for TEXT / VARCHAR in MS Access.
…	…
1138	1168	try:
1139	1169	# Horrible hack to get autoincrement property
1140		query = "SELECT * FROM %s WHERE FALSE" % self.db.quote(tablename)
	1170	query = "SELECT * FROM %s WHERE FALSE;" % self.db.quote(tablename)
1141	1171	bareconn = conn
1142	1172	if hasattr(conn, 'conn'):

trunk/geniusql/providers/firebird.py

r37	r38
124	124	})
125	125
126		def coerce_str(self, ~~col~~):
	126	def coerce_str(self, hints):
127	127	# The bytes hint shall not reflect the usual 4-byte base for varchar.
128	128
129	129	# Although Firebird allows VARCHAR of 32765, 255 is usually the max
130	130	# for which an index can be created.
	131	# TODO: this needs some serious work, so that the full size can be
	132	# allowed while only allowing indices of 255.
131	133	default = 127
132	134
133		bytes = int(~~col.~~hints.get('bytes', default))
	135	bytes = int(hints.get('bytes', default))
134	136	if 1 <= bytes <= 32765:
135	137	return "VARCHAR(%s)" % bytes
136	138	return "BLOB"
137	139
138		def coerce_bool(self, ~~col~~):
	140	def coerce_bool(self, hints):
139	141	return "SMALLINT"
140	142
…	…
155	157
156	158	def attr_startswith(self, tos, arg):
157		return ~~tos + " STARTING WITH " + arg~~
	159	return self.get_expr(tos.sql + " STARTING WITH " + arg.sql, bool)
158	160
159	161	def attr_endswith(self, tos, arg):
160		return tos + " LIKE '%" + self.adapter.escape_like(arg) + "' ESCAPE '\\'"
	162	return self.get_expr(tos.sql + " LIKE '%" +
	163	self.adapter.escape_like(arg.sql) +
	164	"' ESCAPE '\\'", bool)
161	165
162	166	def containedby(self, op1, op2):
163		if ~~isinstance(op1, decompile.ConstWrapper)~~:
	167	if op1.value is not None:
164	168	# Looking for text in a field. Use Like (reverse terms).
165		return op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%' ESCAPE '\\'"
	169	like = self.adapter.escape_like(op1.sql)
	170	return self.get_expr(op2.sql + " LIKE '%" + like +
	171	"%' ESCAPE '\\'", bool)
166	172	else:
167	173	# Looking for field in (a, b, c)
168		atoms = [self.adapter.coerce(x) for x in op2.~~base~~value]
	174	atoms = [self.adapter.coerce(x) for x in op2.value]
169	175	if atoms:
170		return ~~op1 + " IN (" + ", ".join(atoms) + ")"~~
	176	return self.get_expr(op1.sql + " IN (" + ", ".join(atoms) + ")", bool)
171	177	else:
172	178	# Nothing will match the empty list, so return none.
173		return self.adapter.bool_false
174
175		def dejavu_icontainedby(self, op1, op2):
176		if isinstance(op1, decompile.ConstWrapper):
177		return op2 + " CONTAINING " + op1
	179	return self.false_expr
	180
	181	# Firebird has no LOWER function, but it does have an UPPER. Funky.
	182
	183	def builtins_icontainedby(self, op1, op2):
	184	if op1.value is not None:
	185	# Looking for text in a field.
	186	return self.get_expr(op2.sql + " CONTAINING " + op1.sql, bool)
178	187	else:
179	188	# Looking for field in (a, b, c).
180	189	# Force all args to uppercase for case-insensitive comparison.
181		atoms = [self.adapter.coerce(x).upper() for x in op2.basevalue]
182		return "UPPER(%s) IN (%s)" % (op1, ", ".join(atoms))
183
184		def dejavu_icontains(self, x, y):
185		return self.dejavu_icontainedby(y, x)
186
187		# Firebird has no LOWER function, but it does have an UPPER. Funky.
188
189		def dejavu_istartswith(self, x, y):
190		return "UPPER(" + x + ") LIKE '" + self.adapter.escape_like(y) + "%' ESCAPE '\\'"
191
192		def dejavu_iendswith(self, x, y):
193		return "UPPER(" + x + ") LIKE '%" + self.adapter.escape_like(y) + "' ESCAPE '\\'"
194
195		def dejavu_ieq(self, x, y):
196		return "UPPER(" + x + ") = UPPER(" + y + ")"
	190	atoms = [self.adapter.coerce(x).upper() for x in op2.value]
	191	return self.get_expr("UPPER(%s) IN (%s)" %
	192	(op1.sql, ", ".join(atoms)), bool)
	193
	194	def builtins_istartswith(self, x, y):
	195	return self.get_expr("UPPER(" + x.sql + ") LIKE '" +
	196	self.adapter.escape_like(y.sql) +
	197	"%' ESCAPE '\\'", bool)
	198
	199	def builtins_iendswith(self, x, y):
	200	return self.get_expr("UPPER(" + x.sql + ") LIKE '%" +
	201	self.adapter.escape_like(y.sql) +
	202	"' ESCAPE '\\'", bool)
	203
	204	def builtins_ieq(self, x, y):
	205	return self.get_expr("UPPER(" + x.sql + ") = UPPER(" + y.sql + ")",
	206	bool)
197	207
198	208	# Firebird 1.5 doesn't seem to have any date functions
199		def ~~dejavu_~~now(self):
	209	def builtins_utcnow(self):
200	210	return "CURRENT_TIMESTAMP"
201	211
202		dejavu_today = None
203		dejavu_year = None
204		dejavu_month = None
205		dejavu_day = None
	212	builtins_now = None
	213	builtins_today = None
	214	builtins_year = None
	215	builtins_month = None
	216	builtins_day = None
206	217
207	218	# Firebird 1.5 has no LENGTH function

trunk/geniusql/providers/mysql.py

r37	r38
50	50
51	51
	52	def DAY_MICROSECOND(td):
	53	"""Return 'DAYS.MICROSECONDS' from the given timedelta."""
	54	return "%s.%s" % (td.days, (td.seconds * 1000) + td.microseconds)
	55
	56
52	57	class MySQLDecompiler(decompile.SQLDecompiler):
53	58
54		def dejavu_today(self):
55		return "CURDATE()"
	59	def builtins_today(self):
	60	return self.get_expr("CURDATE()", datetime.date)
	61
	62	def binary_op(self, op):
	63	op2, op1 = self.stack.pop(), self.stack.pop()
	64	t1, t2 = op1.pytype, op2.pytype
	65
	66	# re-use op1
	67	op1.pytype = self.result_type[(t1, op, t2)]
	68	newsql = None
	69	if t1 is datetime.datetime:
	70	if t2 is datetime.datetime:
	71	if op == "-":
	72	# Assume NUMERIC (the default for datetime.timedelta).
	73	# The MySQL docs say, "TIMEDIFF() returns expr1 - expr2
	74	# expressed as a time value", but the "hours" component
	75	# can increase arbitrarily (e.g. "23165:38:16").
	76	newsql = ("TIME_TO_SEC(TIMEDIFF(%s, %s))"
	77	% (op1.sql, op2.sql))
	78	elif t2 is datetime.timedelta:
	79	if op in ("-", "+"):
	80	# I figured DAY_MICROSECOND would be best for avoiding
	81	# overflows, but I really don't know.
	82	newsql = ("%s %s INTERVAL '%s' DAY_MICROSECOND"
	83	% (op1.sql, op, DAY_MICROSECOND(op2.value)))
	84	elif t1 is datetime.date:
	85	if t2 is datetime.date:
	86	if op == "-":
	87	# Assume NUMERIC (the default for datetime.timedelta).
	88	newsql = ("TIME_TO_SEC(TIMEDIFF(%s, %s))"
	89	% (op1.sql, op2.sql))
	90	elif t2 is datetime.timedelta:
	91	if op in ("-", "+"):
	92	newsql = ("%s %s INTERVAL %s DAY" %
	93	(op1.sql, op, op2.value.days))
	94	elif t1 is datetime.timedelta:
	95	if op == "+":
	96	if t2 is datetime.datetime:
	97	# I figured DAY_MICROSECOND would be best for avoiding
	98	# overflows, but I really don't know.
	99	td = op2.value
	100	newsql = ("%s + INTERVAL '%s.%s' DAY_MICROSECOND"
	101	% (op2.sql, DAY_MICROSECOND(op1.value)))
	102	elif t2 is datetime.date:
	103	newsql = ("%s %s INTERVAL %s DAY" %
	104	(op2.sql, op, op1.value.days))
	105	else:
	106	newsql = "%s %s %s" % (op1.sql, op, op2.sql)
	107
	108	if newsql is None:
	109	raise TypeError("unsupported operand type(s) for %s: "
	110	"%r and %r" % (op, t1, t2))
	111
	112	op1.sql = newsql
	113	if not op1.name.startswith("expr_"):
	114	op1.name = "expr_%s" % op1.name
	115	self.stack.append(op1)
56	116
57	117
…	…
62	122	# encoding, like utf8.
63	123
64		def ~~dejavu~~_icontainedby(self, op1, op2):
65		if ~~isinstance(op1, decompile.ConstWrapper)~~:
	124	def builtins_icontainedby(self, op1, op2):
	125	if op1.value is not None:
66	126	# Looking for text in a field. Use Like (reverse terms).
67		return ("CONVERT("+ op2 + " USING utf8) LIKE '%" +
68		self.adapter.escape_like(op1) + "%'")
	127	return self.get_expr("CONVERT(" + op2.sql +
	128	" USING utf8) LIKE '%" +
	129	self.adapter.escape_like(op1.sql)
	130	+ "%'", bool)
69	131	else:
70	132	# Looking for field in (a, b, c).
71		atoms = [self.adapter.coerce(x) for x in op2.basevalue]
72		return "CONVERT(%s USING utf8) IN (%s)" % (op1, ", ".join(atoms))
73
74		def dejavu_istartswith(self, x, y):
75		return ("CONVERT(" + x + " USING utf8) LIKE '" +
76		self.adapter.escape_like(y) + "%'")
77
78		def dejavu_iendswith(self, x, y):
79		return ("CONVERT(" + x + " USING utf8) LIKE '%" +
80		self.adapter.escape_like(y) + "'")
81
82		def dejavu_ieq(self, x, y):
83		return "CONVERT(" + x + " USING utf8) = " + y
	133	# Force all args to lowercase for case-insensitive comparison.
	134	atoms = [self.adapter.coerce(x) for x in op2.value]
	135	return self.get_expr("CONVERT(%s USING utf8) IN (%s)" %
	136	(op1.sql, ", ".join(atoms)), bool)
	137
	138	def builtins_istartswith(self, x, y):
	139	return self.get_expr("CONVERT(" + x.sql + " USING utf8) LIKE '" +
	140	self.adapter.escape_like(y.sql) + "%'", bool)
	141
	142	def builtins_iendswith(self, x, y):
	143	return self.get_expr("CONVERT(" + x.sql + " USING utf8) LIKE '%" +
	144	self.adapter.escape_like(y.sql) + "'", bool)
	145
	146	def builtins_ieq(self, x, y):
	147	return self.get_expr("CONVERT(" + x.sql + " USING utf8) = " + y.sql,
	148	bool)
	149
	150	def builtins_utcnow(self):
	151	return self.get_expr("UTC_TIMESTAMP()", datetime.datetime)
84	152
85	153
…	…
113	181	return "FLOAT(%s)" % precision
114	182
115		def coerce_str(self, ~~col~~):
116		bytes = int(~~col.~~hints.get('bytes', 255))
	183	def coerce_str(self, hints):
	184	bytes = int(hints.get('bytes', 255))
117	185
118	186	if bytes:
…	…
127	195	return "LONGBLOB"
128	196
129		def coerce_bool(self, ~~col~~):
	197	def coerce_bool(self, hints):
130	198	# We could use BOOLEAN, but it wasn't introduced until 4.1.0.
131	199	return "BOOL"
132	200
133		def coerce_datetime_datetime(self, ~~col~~):
	201	def coerce_datetime_datetime(self, hints):
134	202	return "DATETIME"
135	203
136		def coerce_int(self, ~~col~~):
137		bytes = int(~~col.~~hints.get('bytes', '4'))
	204	def coerce_int(self, hints):
	205	bytes = int(hints.get('bytes', '4'))
138	206	if bytes <= 2:
139	207	return "SMALLINT"
…	…
154	222	class TypeAdapterMySQL41(TypeAdapterMySQL):
155	223
156		def coerce_str(self, ~~col~~):
157		dbtype = TypeAdapterMySQL.coerce_str(self, ~~col~~)
	224	def coerce_str(self, hints):
	225	dbtype = TypeAdapterMySQL.coerce_str(self, hints)
158	226	if dbtype == "BLOB":
159		dbtype = "BLOB(%s)" % ~~col.~~hints['bytes']
	227	dbtype = "BLOB(%s)" % hints['bytes']
160	228	return dbtype
161	229

trunk/geniusql/providers/postgres.py

r37	r38
79	79	else:
80	80	return str(value)
	81
	82	def coerce_any_to_datetime_timedelta(self, value):
	83	if isinstance(value, basestring):
	84	# Assume it's an interval in ISO format.
	85	# e.g. "964 days 18:29:45.4769999981"
	86	days, rem = value.split(" days ", 1)
	87	h, m, s = rem.split(":", 2)
	88	s = (int(h) * 3600) + (int(m) * 60) + float(s)
	89	return datetime.timedelta(int(days), s)
	90	else:
	91	print type(value)
	92	days, seconds = divmod(long(value), 86400)
	93	return datetime.timedelta(int(days), int(seconds))
81	94
82	95
83	96	class PgDecompiler(decompile.SQLDecompiler):
84	97
85		def ~~dejavu~~_icontainedby(self, op1, op2):
86		if ~~isinstance(op1, decompile.ConstWrapper)~~:
	98	def builtins_icontainedby(self, op1, op2):
	99	if op1.value is not None:
87	100	# Looking for text in a field. Use ILike (reverse terms).
88		return op2 + " ILIKE '%" + self.adapter.escape_like(op1) + "%'"
	101	return self.get_expr(op2.sql + " ILIKE '%" +
	102	self.adapter.escape_like(op1.sql) + "%'",
	103	bool)
89	104	else:
90	105	# Looking for field in (a, b, c).
91	106	# Force all args to lowercase for case-insensitive comparison.
92		atoms = [self.adapter.coerce(x).lower() for x in op2.basevalue]
93		return "LOWER(%s) IN (%s)" % (op1, ", ".join(atoms))
94
95		def dejavu_istartswith(self, x, y):
96		return x + " ILIKE '" + self.adapter.escape_like(y) + "%'"
97
98		def dejavu_iendswith(self, x, y):
99		return x + " ILIKE '%" + self.adapter.escape_like(y) + "'"
100
101		def dejavu_ieq(self, x, y):
	107	atoms = [self.adapter.coerce(x).lower() for x in op2.value]
	108	return self.get_expr("LOWER(%s) IN (%s)" %
	109	(op1.sql, ", ".join(atoms)), bool)
	110
	111	def builtins_istartswith(self, x, y):
	112	return self.get_expr(x.sql + " ILIKE '" +
	113	self.adapter.escape_like(y.sql) + "%'", bool)
	114
	115	def builtins_iendswith(self, x, y):
	116	return self.get_expr(x.sql + " ILIKE '%" +
	117	self.adapter.escape_like(y.sql) + "'", bool)
	118
	119	def builtins_ieq(self, x, y):
102	120	# ILIKE with no wildcards should behave like ieq.
103		return x + " ILIKE '" + self.adapter.escape_like(y) + "'"
104
105		def dejavu_year(self, x):
106		return "date_part('year', " + x + ")"
107
108		def dejavu_month(self, x):
109		return "date_part('month', " + x + ")"
110
111		def dejavu_day(self, x):
112		return "date_part('day', " + x + ")"
	121	return self.get_expr(x.sql + " ILIKE '" +
	122	self.adapter.escape_like(y.sql) + "'", bool)
	123
	124	def builtins_year(self, x):
	125	return self.get_expr("date_part('year', " + x.sql + ")", int)
	126
	127	def builtins_month(self, x):
	128	return self.get_expr("date_part('month', " + x.sql + ")", int)
	129
	130	def builtins_day(self, x):
	131	return self.get_expr("date_part('day', " + x.sql + ")", int)
	132
	133	def builtins_now(self):
	134	import time
	135	if time.daylight:
	136	offset = time.altzone
	137	else:
	138	offset = time.timezone
	139	if offset < 0:
	140	offset = abs(offset)
	141	sign = ""
	142	else:
	143	sign = "-"
	144	h, m = divmod(offset, 3600)
	145	m, s = divmod(m, 60)
	146	offset = "%s:%s" % (h, m)
	147	return self.get_expr("NOW() AT TIME ZONE INTERVAL '%s%s'"
	148	% (sign, offset), datetime.datetime)
	149
	150	def builtins_utcnow(self):
	151	return self.get_expr("NOW()", datetime.datetime)
113	152
114	153

trunk/geniusql/providers/sqlite.py

r37	r38
82	82	# comparison.
83	83	for i, op in enumerate((op1, op2)):
84		if (isinstance(op, decompile.ConstWrapper) and
85		isinstance(op.basevalue, (datetime.date, datetime.datetime))):
86		op1 = "julianday(" + op1 + ")"
87		op2 = "julianday(" + op2 + ")"
	84	if isinstance(op.value, (datetime.date, datetime.datetime)):
	85	op1.sql = "julianday(" + op1.sql + ")"
	86	op2.sql = "julianday(" + op2.sql + ")"
88	87	break
89		if isinstance(op~~, basestring) and "julianday" in op~~:
	88	if isinstance(op.value, basestring) and "julianday" in op.value:
90	89	if i == 0:
91		op2 ~~= "julianday(" + op2~~ + ")"
	90	op2.sql = "julianday(" + op2.sql + ")"
92	91	else:
93		op1 ~~= "julianday(" + op1~~ + ")"
	92	op1.sql = "julianday(" + op1.sql + ")"
94	93	break
95	94
…	…
103	102	def attr_startswith(self, tos, arg):
104	103	if _escape_support:
105		return tos + " LIKE '" + self.adapter.escape_like(arg) + r"%' ESCAPE '\'"
106		else:
107		if "%" in arg or "_" in arg:
	104	return self.get_expr(tos.sql + " LIKE '" +
	105	self.adapter.escape_like(arg.sql) +
	106	r"%' ESCAPE '\'", bool)
	107	else:
	108	if "%" in arg.sql or "_" in arg.sql:
108	109	raise ValueError(_escape_warning)
109	110	else:
110		return tos + " LIKE '" + arg.strip(r"'\"") + "%'"
	111	return self.get_expr(tos.sql + " LIKE '" +
	112	arg.sql.strip(r"'\"") + "%'", bool)
111	113
112	114	def attr_endswith(self, tos, arg):
113	115	if _escape_support:
114		return tos + " LIKE '%" + self.adapter.escape_like(arg) + r"' ESCAPE '\'"
115		else:
116		if "%" in arg or "_" in arg:
	116	return self.get_expr(tos.sql + " LIKE '%" +
	117	self.adapter.escape_like(arg.sql) +
	118	r"' ESCAPE '\'", bool)
	119	else:
	120	if "%" in arg.sql or "_" in arg.sql:
117	121	raise ValueError(_escape_warning)
118	122	else:
119		return tos + " LIKE '%" + arg.strip(r"'\"") + "'"
	123	return self.get_expr(tos.sql + " LIKE '%" +
	124	arg.sql.strip(r"'\"") + "'", bool)
120	125
121	126	def containedby(self, op1, op2):
122		if ~~isinstance(op1, decompile.ConstWrapper)~~:
	127	if op1.value is not None:
123	128	# Looking for text in a field. Use Like (reverse terms).
124	129	if _escape_support:
125		return op2 + " LIKE '%" + self.adapter.escape_like(op1) + r"%' ESCAPE '\'"
	130	return self.get_expr(op2.sql + " LIKE '%" +
	131	self.adapter.escape_like(op1.sql) +
	132	r"%' ESCAPE '\'", bool)
126	133	else:
127		if "%" in op1 ~~or "_" in op1~~:
	134	if "%" in op1.sql or "_" in op1.sql:
128	135	raise ValueError(_escape_warning)
129	136	else:
130		return op2 + " LIKE '%" + op1.strip(r"'\"") + r"%'"
	137	return self.get_expr(op2.sql + " LIKE '%" +
	138	op1.sql.strip(r"'\"") + r"%'", bool)
131	139	else:
132	140	# Looking for field in (a, b, c)
133		atoms = [self.adapter.coerce(x) for x in op2.basevalue]
134		return op1 + " IN (" + ", ".join(atoms) + ")"
135
136		def dejavu_icontainedby(self, op1, op2):
137		if isinstance(op1, decompile.ConstWrapper):
	141	atoms = [self.adapter.coerce(x) for x in op2.value]
	142	return self.get_expr(op1.sql + " IN (" + ", ".join(atoms) + ")",
	143	bool)
	144
	145	def builtins_icontainedby(self, op1, op2):
	146	if op1.value is not None:
138	147	# Looking for text in a field. Use Like (reverse terms).
139	148	if _escape_support:
140		return ("LOWER(" + op2 + ") LIKE '%" +
141		self.adapter.escape_like(op1).lower() + r"%' ESCAPE '\'")
	149	return self.get_expr("LOWER(" + op2.sql + ") LIKE '%" +
	150	self.adapter.escape_like(op1.sql).lower() +
	151	r"%' ESCAPE '\'", bool)
142	152	else:
143		if "%" in op1 ~~or "_" in op1~~:
	153	if "%" in op1.sql or "_" in op1.sql:
144	154	raise ValueError(_escape_warning)
145	155	else:
146		return ("LOWER(" + op2 + ") LIKE '%" +
147		op1.strip("'\"").lower() + r"%'")
	156	return self.get_expr("LOWER(" + op2.sql + ") LIKE '%" +
	157	op1.sql.strip("'\"").lower() +
	158	r"%'", bool)
148	159	else:
149	160	# Looking for field in (a, b, c).
150	161	# Force all args to lowercase for case-insensitive comparison.
151		atoms = [self.adapter.coerce(x).lower() for x in op2.basevalue]
152		return "LOWER(%s) IN (%s)" % (op1, ", ".join(atoms))
153
154		def dejavu_icontains(self, x, y):
155		return self.dejavu_icontainedby(y, x)
156
157		def dejavu_istartswith(self, x, y):
	162	atoms = [self.adapter.coerce(x).lower() for x in op2.value]
	163	return self.get_expr("LOWER(%s) IN (%s)" %
	164	(op1.sql, ", ".join(atoms)), bool)
	165
	166	def builtins_istartswith(self, x, y):
158	167	if _escape_support:
159		return ("LOWER(" + x + ") LIKE '" + self.adapter.escape_like(y)
160		+ r"%' ESCAPE '\'")
161		else:
162		if "%" in y or "_" in y:
	168	return self.get_expr("LOWER(" + x.sql + ") LIKE '" +
	169	self.adapter.escape_like(y.sql)
	170	+ r"%' ESCAPE '\'", bool)
	171	else:
	172	if "%" in y.sql or "_" in y.sql:
163	173	raise ValueError(_escape_warning)
164	174	else:
165		return "LOWER(" + x + ") LIKE '" + y.strip("'\"") + r"%'"
166
167		def dejavu_iendswith(self, x, y):
	175	return self.get_expr("LOWER(" + x.sql + ") LIKE '" +
	176	y.sql.strip("'\"") + r"%'", bool)
	177
	178	def builtins_iendswith(self, x, y):
168	179	if _escape_support:
169		return ("LOWER(" + x + ") LIKE '%" + self.adapter.escape_like(y)
170		+ r"%' ESCAPE '\'")
171		else:
172		if "%" in y or "_" in y:
	180	return self.get_expr("LOWER(" + x.sql + ") LIKE '%" +
	181	self.adapter.escape_like(y.sql)
	182	+ r"%' ESCAPE '\'", bool)
	183	else:
	184	if "%" in y.sql or "_" in y.sql:
173	185	raise ValueError(_escape_warning)
174	186	else:
175		return "LOWER(" + x + ") LIKE '%" + y.strip("'\"") + r"%'"
176
177		def dejavu_now(self):
	187	return self.get_expr("LOWER(" + x.sql + ") LIKE '%" +
	188	y.sql.strip("'\"") + r"%'", bool)
	189
	190	def builtins_utcnow(self):
178	191	if self.adapter.perfect_dates and _cast_support:
179		return ~~"julianday('now')"~~
	192	return self.get_expr("julianday('now')", datetime.datetime)
180	193	else:
181	194	self.imperfect = True
182	195	return decompile.cannot_represent
183	196
184		~~dejavu_today = dejavu_~~now
185
186		def ~~dejavu~~_year(self, x):
	197	builtins_today = builtins_utcnow
	198
	199	def builtins_year(self, x):
187	200	if self.adapter.perfect_dates and _cast_support:
188		return "CAST(strftime('%Y', " + x + ") AS NUMERIC)"
	201	return self.get_expr("CAST(strftime('%Y', " + x.sql +
	202	") AS NUMERIC)", int)
189	203	else:
190	204	self.imperfect = True
191	205	return decompile.cannot_represent
192	206
193		def ~~dejavu~~_month(self, x):
	207	def builtins_month(self, x):
194	208	if self.adapter.perfect_dates and _cast_support:
195		return "CAST(strftime('%m', " + x + ") AS NUMERIC)"
	209	return self.get_expr("CAST(strftime('%m', " + x.sql +
	210	") AS NUMERIC)", int)
196	211	else:
197	212	self.imperfect = True
198	213	return decompile.cannot_represent
199	214
200		def ~~dejavu~~_day(self, x):
	215	def builtins_day(self, x):
201	216	if self.adapter.perfect_dates and _cast_support:
202		return "CAST(strftime('%d', " + x + ") AS NUMERIC)"
	217	return self.get_expr("CAST(strftime('%d', " + x.sql +
	218	") AS NUMERIC)", int)
203	219	else:
204	220	self.imperfect = True
…	…
246	262	return str
247	263
248		def coerce_decimal_Decimal(self, ~~col~~):
	264	def coerce_decimal_Decimal(self, hints):
249	265	return "TEXT"
250	266
251		def coerce_fixedpoint_FixedPoint(self, ~~col~~):
	267	def coerce_fixedpoint_FixedPoint(self, hints):
252	268	return "TEXT"
253	269
…	…
256	272	return "REAL"
257	273
258		def coerce_str(self, ~~col~~):
	274	def coerce_str(self, hints):
259	275	# The bytes hint shall not reflect the usual 4-byte base for varchar.
260	276	return "TEXT"
261	277
262		def coerce_bool(self, ~~col~~): return "INTEGER"
263
264		def coerce_datetime_datetime(self, ~~col~~): return "TEXT"
265		def coerce_datetime_date(self, ~~col~~): return "TEXT"
266		def coerce_datetime_time(self, ~~col~~): return "TEXT"
	278	def coerce_bool(self, hints): return "INTEGER"
	279
	280	def coerce_datetime_datetime(self, hints): return "TEXT"
	281	def coerce_datetime_date(self, hints): return "TEXT"
	282	def coerce_datetime_time(self, hints): return "TEXT"
267	283
268	284	# I was seriously disinterested in writing a parser for interval.
269		def coerce_datetime_timedelta(self, ~~col~~):
270		return self.coerce_float(~~col~~)
	285	def coerce_datetime_timedelta(self, hints):
	286	return self.coerce_float(hints)
271	287
272	288	def int_type(self, bytes):

trunk/geniusql/select.py

r37	r38
2	2
3	3	from geniusql import errors
4		from ~~dejavu~~ import logic
	4	from geniusql import logic
5	5
6	6
…	…
247	247	"""Return an SQL WHERE clause, and an 'imperfect' flag."""
248	248	tpairs = [(t.alias or t.qname, t.table) for t in self.tables]
249		decom = self.db.decompiler(tpairs, self.restriction, self.db.adaptertosql)
	249	decom = self.db.decompiler(tpairs, self.restriction,
	250	self.db.adaptertosql,
	251	self.db.typeadapter)
250	252	return decom.code(), decom.imperfect
251	253
…	…
341	343	def decompile_attributes(self):
342	344	tpairs = [(t.alias or t.qname, t.table) for t in self.tables]
343		decom = self.db.~~attribute~~decompiler
344		decom = decom(tpairs, self.attributes, self.db.adaptertosql)
345		~~decom.walk(~~)
346		~~for atom in decom.stack:~~
347		~~newcol = atom.col.copy()~~
348		~~newcol.key = False~~
349		~~newcol.autoincrement = False~~
350		~~newcol.sequence_name = None~~
351		~~newcol.initial = 1~~
	345	decom = self.db.decompiler
	346	decom = decom(tpairs, self.attributes, self.db.adaptertosql,
	347	self.db.typeadapter)
	348
	349	from geniusql import objects, decompile
	350	for atom in decom.field_list():
	351	if atom is decompile.cannot_represent:
	352	raise ValueError("The supplied expression could not be "
	353	"translated to SQL: %r" % self.attributes)
352	354
353		if atom.alias in self.result:
354		schema = atom.table.schema
355		atom.alias = '%s_%s' % (schema.key_for(atom.table), atom.alias)
356		newcol.name = '%s_%s' % (atom.table.name, newcol.name)
357		newcol.qname = schema.db.quote(newcol.name)
	355	if atom.name in self.result:
	356	bare_name = atom.name
	357	index = 1
	358	while atom.name in self.result:
	359	atom.name = '%s%s' % (bare_name, index)
	360	index += 1
358	361
359		self.output_list.append(newcol.qname)
360		self.output_keys.append(atom.alias)
	362	self.input_list.append('%s AS %s' % (atom.sql, atom.name))
	363	self.output_list.append(atom.name)
	364	self.output_keys.append(atom.name)
361	365	if not atom.aggregate:
362		self._groupby.append(atom.expr)
363		self.result[atom.alias] = newcol
	366	self._groupby.append(atom.sql)
364	367
365		~~expr = atom.expr~~
366		~~if atom.alias != atom.key:~~
367		~~expr = '%s AS %s' % (expr, atom.alias)~~
368		self.~~input_list.append(expr)~~
369
	368	col = objects.Column(atom.pytype, atom.dbtype, name=atom.name)
	369	col.qname = self.db.quote(col.name)
	370	col.imperfect_type = atom.imperfect_type
	371	self.result[atom.name] = col
	372

trunk/geniusql/test/test.py

r37	r38
70	70	print
71	71	print "Testing %s..." % testmod[5:]
	72
72	73	mod = __import__(testmod, globals(), locals(), [''])
73	74	if hasattr(mod, 'opts'):
74	75	mod.opts['Prefix'] = 'test'
75		mod.run()
	76
	77	if hasattr(mod, 'run'):
	78	mod.run()
	79	else:
	80	suite = unittest.TestLoader().loadTestsFromName(testmod)
	81	tools.TestRunner.run(suite)
76	82
77	83

trunk/geniusql/test/test_logic.py

r37	r38
4	4	import unittest
5	5
6		from geniusql import logic
	6	from geniusql import logic, logicfuncs
	7	# Might as well test with the geniusql custom logicfuncs
	8	logicfuncs.init()
7	9
8	10	nums = logic.codewalk.numeric_opcodes
9
10	11	lx = "logic.Expression(lambda x: "
11	12
…	…
16	17	e = logic.Expression(lambda x: icontains(x.Status, 'c'))
17	18	self.assertEqual(repr(e), lx + "icontains(x.Status, 'c'))")
	19	co_code = e.func.func_code.co_code
	20	co_code = logic.codewalk.named_opcodes(map(ord, co_code))
18	21	if sys.version_info >= (2, 5):
19	22	# Python 2.5 stopped including args in co_names,
20	23	# so the indices into co_names changed.
21		self.assertEqual(e.func.func_code.co_code,
22		'd\x02\x00\|\x00\x00i\x02\x00d\x00\x00\x83\x02\x00S')
23		else:
24		self.assertEqual(e.func.func_code.co_code,
25		'd\x03\x00\|\x00\x00i\x03\x00d\x01\x00\x83\x02\x00S')
	24	self.assertEqual(co_code,
	25	['LOAD_CONST', 2, 0,
	26	'LOAD_FAST', 0, 0,
	27	'LOAD_ATTR', 2, 0,
	28	'LOAD_CONST', 0, 0,
	29	'CALL_FUNCTION', 2, 0,
	30	'RETURN_VALUE'])
	31	else:
	32	self.assertEqual(co_code,
	33	['LOAD_CONST', 2, 0,
	34	'LOAD_FAST', 0, 0,
	35	'LOAD_ATTR', 2, 0,
	36	'LOAD_CONST', 1, 0,
	37	'CALL_FUNCTION', 2, 0,
	38	'RETURN_VALUE'])
26	39
27	40	# 4/28/04: This one failed in endue.html.nav,

trunk/geniusql/test/test_msaccess.py

r37	r38
20	20	"""Stores Decimal and FixedPoint objects as CURRENCY."""
21	21
22		def decimal_type(self, ~~colname,~~ precision, scale):
	22	def decimal_type(self, precision, scale):
23	23	if precision == 0:
24	24	precision = 19

trunk/geniusql/test/zoo_fixture.py

r37	r38
24	24	from geniusql.test import tools
25	25
26		from dejavu import logic
	26	from geniusql import logic, logicfuncs
	27	logicfuncs.init()
27	28
28	29
…	…
324	325	self.assertEqual(float(tr['Acreage']), 4)
325	326	self.assertEqual(tr['PettingAllowed'], False)
326
	327
327	328	def test_4_Expressions(self):
328	329	def matches(lam, tkey='Animal'):
…	…
353	354
354	355	# logic and other functions
355		import dejavu
356		self.assertEqual(matches(lambda x: dejavu.ieq(x.Species, 'slug')), 1)
357		self.assertEqual(matches(lambda x: dejavu.icontains(x.Species, 'PEDE')), 2)
358		self.assertEqual(matches(lambda x: dejavu.icontains(('Lion', 'Banana'), x.Species)), 1)
359		f = lambda x: dejavu.icontainedby(x.Species, ('Lion', 'Bear', 'Leopard'))
	356	self.assertEqual(matches(lambda x: ieq(x.Species, 'slug')), 1)
	357	self.assertEqual(matches(lambda x: icontains(x.Species, 'PEDE')), 2)
	358	self.assertEqual(matches(lambda x: icontains(('Lion', 'Banana'), x.Species)), 1)
	359	f = lambda x: icontainedby(x.Species, ('Lion', 'Bear', 'Leopard'))
360	360	self.assertEqual(matches(f), 3)
361	361	name = 'Lion'
…	…
367	367	# Test now(), today(), year(), month(), day()
368	368	self.assertEqual(matches(lambda x: x.Founded != None
369		and x.Founded < ~~dejavu.~~today(), 'Zoo'), 3)
370		self.assertEqual(matches(lambda x: x.LastEscape == ~~dejavu.~~now()), 0)
371		self.assertEqual(matches(lambda x: ~~dejavu.~~year(x.LastEscape) == 2004), 1)
372		self.assertEqual(matches(lambda x: ~~dejavu.~~month(x.LastEscape) == 12), 1)
373		self.assertEqual(matches(lambda x: d~~ejavu.d~~ay(x.LastEscape) == 21), 1)
	369	and x.Founded < today(), 'Zoo'), 3)
	370	self.assertEqual(matches(lambda x: x.LastEscape == now()), 0)
	371	self.assertEqual(matches(lambda x: year(x.LastEscape) == 2004), 1)
	372	self.assertEqual(matches(lambda x: month(x.LastEscape) == 12), 1)
	373	self.assertEqual(matches(lambda x: day(x.LastEscape) == 21), 1)
374	374
375	375	# Test AND, OR with cannot_represent.
…	…
404	404	Visit = schema['Visit']
405	405	Vet = schema['Vet']
	406	Zoo = schema['Zoo']
406	407
407	408	# views
…	…
429	430	self.assertAlmostEqual(expected[species], lifespan, places=5)
430	431
431		~~import dejavu~~
432	432	expected = [u'Montr\xe9al Biod\xf4me', 'Wild Animal Park']
433	433	e = (lambda x: x.Founded != None
434		and x.Founded <= ~~dejavu.~~today()
	434	and x.Founded <= today()
435	435	and x.Founded >= datetime.date(1990, 1, 1))
436	436	values = [val[0] for val in db.select(schema['Zoo'], ['Name'], e)]
…	…
478	478	)
479	479
480		# Test global agg funcs
	480	# Test implicit global funcs
	481	escapes = db.select(Animal, lambda a: day(a.LastEscape),
	482	lambda a: a.LastEscape != None)
	483	escapes = list(escapes)
	484	escapes.sort()
	485	self.assertEqual(escapes, [[21]])
	486
	487	# 'count' agg func
481	488	visits = db.select(Animal << Visit,
482	489	lambda a, v: (a.Species, count(v.Date)))
483	490	visits = list(visits)
484	491	visits.sort()
485		self.assertEqual(visits, [[]])
	492	self.assertEqual(visits,
	493	[[u'Adelie Penguin', 0],
	494	[u'Ape', 0],
	495	[u'Bear', 0],
	496	[u'Centipede', 0],
	497	[u'Emperor Penguin', 20],
	498	[u'Leopard', 0],
	499	[u'Lion', 0],
	500	[u'Millipede', 0],
	501	[u'Ostrich', 0],
	502	[u'Slug', 0],
	503	[u'Tiger', 20]]
	504	)
	505
	506	# 'now' func
	507	WAP_elapsed = (datetime.datetime.now() -
	508	Zoo.select(Name='Wild Animal Park')['LastEscape'])
	509	elapsed = db.select(Zoo, lambda z: (z.Name, now() - z.LastEscape))
	510	elapsed = list(elapsed)
	511	elapsed.sort()
	512	self.assertEqual(elapsed[:3],
	513	[[u'Montr\xe9al Biod\xf4me', None],
	514	[u'San Diego Zoo', None],
	515	[u'Sea_World', None],
	516	]
	517	)
	518	# Assert that the expected interval and actual interval are
	519	# no more than 1 second apart.
	520	diff = abs(elapsed[3][1] - WAP_elapsed)
	521	self.assert_(diff < datetime.timedelta(0, 1), diff)
	522
	523	# 'utcnow' func
	524	WAP_elapsed = (datetime.datetime.utcnow() -
	525	Zoo.select(Name='Wild Animal Park')['LastEscape'])
	526	elapsed = db.select(Zoo, lambda z: (z.Name, utcnow() - z.LastEscape))
	527	elapsed = list(elapsed)
	528	elapsed.sort()
	529	self.assertEqual(elapsed[:3],
	530	[[u'Montr\xe9al Biod\xf4me', None],
	531	[u'San Diego Zoo', None],
	532	[u'Sea_World', None],
	533	]
	534	)
	535	# Assert that the expected interval and actual interval are
	536	# no more than 1 second apart.
	537	diff = abs(elapsed[3][1] - WAP_elapsed)
	538	self.assert_(diff < datetime.timedelta(0, 1), diff)
486	539	finally:
487	540	db.connections.commit()
…	…
672	725	'ID': 9, 'Species': u'Adelie Penguin'}]
673	726	)
674
	727	##
675	728	## def test_zzz_Schema_Upgrade(self):
676	729	## # Must run last.
…	…
1095	1148
1096	1149	def _geniusqllog(message):
1097		"""~~Dejavu~~ logger (writes to error.log)."""
	1150	"""Geniusql logger (writes to error.log)."""
1098	1151	if isinstance(message, unicode):
1099	1152	message = message.encode('utf8')
…	…
1139	1192	# Each thread opens a new SQLite :memory: database,
1140	1193	# so the concept of "isolation" is pretty meaningless.
1141		if name != ':memory:':
1142		tools.TestRunner.run(loader(IsolationTests))
	1194	## if name != ':memory:':
	1195	## tools.TestRunner.run(loader(IsolationTests))
1143	1196
1144	1197	## tools.TestRunner.run(loader(DiscoveryTests))

Changeset Navigation

Changeset 38

Legend:

trunk/geniusql/adapters.py

trunk/geniusql/codewalk.py

trunk/geniusql/decompile.py

trunk/geniusql/logic.py

trunk/geniusql/objects.py

trunk/geniusql/providers/ado.py

trunk/geniusql/providers/firebird.py

trunk/geniusql/providers/mysql.py

trunk/geniusql/providers/postgres.py

trunk/geniusql/providers/sqlite.py

trunk/geniusql/select.py

trunk/geniusql/test/test.py

trunk/geniusql/test/test_logic.py

trunk/geniusql/test/test_msaccess.py

trunk/geniusql/test/zoo_fixture.py

Download in other formats: