[CalendarServer-changes] [9529] CalendarServer/trunk/twext/enterprise/dal
source_changes at macosforge.org
source_changes at macosforge.org
Mon Aug 6 09:48:20 PDT 2012
Revision: 9529
http://trac.macosforge.org/projects/calendarserver/changeset/9529
Author: cdaboo at apple.com
Date: 2012-08-06 09:48:20 -0700 (Mon, 06 Aug 2012)
Log Message:
-----------
Enhanced DAL syntax to allow for SQL 'IN (x, y, z, ...)' type expressions.
Modified Paths:
--------------
CalendarServer/trunk/twext/enterprise/dal/syntax.py
CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py
Modified: CalendarServer/trunk/twext/enterprise/dal/syntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/syntax.py 2012-08-06 16:47:35 UTC (rev 9528)
+++ CalendarServer/trunk/twext/enterprise/dal/syntax.py 2012-08-06 16:48:20 UTC (rev 9529)
@@ -327,7 +327,10 @@
def In(self, subselect):
# Can't be Select.__contains__ because __contains__ gets __nonzero__
# called on its result by the 'in' syntax.
- return CompoundComparison(self, 'in', subselect)
+ if isinstance(subselect, ParameterSet):
+ return CompoundComparison(self, 'in', ConstantSet(subselect))
+ else:
+ return CompoundComparison(self, 'in', subselect)
def StartsWith(self, other):
@@ -381,6 +384,23 @@
+class ConstantSet(ExpressionSyntax):
+ def __init__(self, value):
+ self.value = value
+
+
+ def allColumns(self):
+ return []
+
+
+ def subSQL(self, queryGenerator, allTables):
+
+ return _inParens(_CommaList(
+ [SQLFragment(queryGenerator.placeholder.placeholder(), [self.value] if ctr == 0 else []) for ctr in range(self.value.len)]
+ ).subSQL(queryGenerator, allTables))
+
+
+
class NamedValue(ExpressionSyntax):
"""
A constant within the database; something predefined, such as
@@ -1593,6 +1613,9 @@
for parameter in self.parameters:
if isinstance(parameter, Parameter):
params.append(kw[parameter.name])
+ elif isinstance(parameter, ParameterSet):
+ for item in kw[parameter.name]:
+ params.append(item)
else:
params.append(parameter)
return SQLFragment(self.text, params)
@@ -1647,6 +1670,31 @@
return 'Parameter(%r)' % (self.name,)
+
+class ParameterSet(object):
+
+ def __init__(self, name, items):
+ self.name = name
+ self.len = len(items)
+
+
+ def __eq__(self, param):
+ if not isinstance(param, ParameterSet):
+ return NotImplemented
+ return self.name == param.name
+
+
+ def __ne__(self, param):
+ if not isinstance(param, ParameterSet):
+ return NotImplemented
+ return not self.__eq__(param)
+
+
+ def __repr__(self):
+ return 'ParameterSet(%r)' % (self.name,)
+
+
+
# Common helpers:
# current timestamp in UTC format. Hack to support standard syntax for this,
Modified: CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py 2012-08-06 16:47:35 UTC (rev 9528)
+++ CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py 2012-08-06 16:48:20 UTC (rev 9529)
@@ -25,7 +25,7 @@
TableMismatch, Parameter, Max, Len, NotEnoughValues,
Savepoint, RollbackToSavepoint, ReleaseSavepoint, SavepointAction,
Union, Intersect, Except, SetExpression, DALError,
- ResultAliasSyntax, Count, QueryGenerator)
+ ResultAliasSyntax, Count, QueryGenerator, ParameterSet)
from twext.enterprise.dal.syntax import FixedPlaceholder, NumericPlaceholder
from twext.enterprise.dal.syntax import Function
from twext.enterprise.dal.syntax import SchemaSyntax
@@ -683,7 +683,8 @@
def test_inSubSelect(self):
"""
- L{ColumnSyntax.In} returns a sub-expression using the SQL 'in' syntax.
+ L{ColumnSyntax.In} returns a sub-expression using the SQL 'in' syntax with
+ a sub-select.
"""
wherein = (self.schema.FOO.BAR.In(
Select([self.schema.BOZ.QUX], From=self.schema.BOZ)))
@@ -693,6 +694,53 @@
"select * from FOO where BAR in (select QUX from BOZ)"))
+ def test_inParameterSet(self):
+ """
+ L{ColumnSyntax.In} returns a sub-expression using the SQL 'in' syntax with
+ parameter list.
+ """
+
+ items = set(('A', 'B'))
+ self.assertEquals(
+ Select(From=self.schema.FOO, Where=self.schema.FOO.BAR.In(ParameterSet("names", items))).toSQL().bind(names=items),
+ SQLFragment(
+ "select * from FOO where BAR in (?, ?)", ['A', 'B']))
+
+ self.assertEquals(
+ Select(
+ From=self.schema.FOO,
+ Where=(self.schema.FOO.BAZ == Parameter('P1')).And(
+ self.schema.FOO.BAR.In(ParameterSet("names", items)
+ ))
+ ).toSQL().bind(P1="P1", names=items),
+ SQLFragment(
+ "select * from FOO where BAZ = ? and BAR in (?, ?)", ['P1', 'A', 'B']),
+ )
+
+ self.assertEquals(
+ Select(
+ From=self.schema.FOO,
+ Where=(self.schema.FOO.BAR.In(ParameterSet("names", items)).And(
+ self.schema.FOO.BAZ == Parameter('P2')
+ ))
+ ).toSQL().bind(P2="P2", names=items),
+ SQLFragment(
+ "select * from FOO where BAR in (?, ?) and BAZ = ?", ['A', 'B', 'P2']),
+ )
+
+ self.assertEquals(
+ Select(
+ From=self.schema.FOO,
+ Where=(self.schema.FOO.BAZ == Parameter('P1')).Or(
+ self.schema.FOO.BAR.In(ParameterSet("names", items)).And(
+ self.schema.FOO.BAZ == Parameter('P2')
+ ))
+ ).toSQL().bind(P1="P1", P2="P2", names=items),
+ SQLFragment(
+ "select * from FOO where BAZ = ? or BAR in (?, ?) and BAZ = ?", ['P1', 'A', 'B', 'P2']),
+ )
+
+
def test_max(self):
"""
L{Max}C{(column)} produces an object in the 'columns' clause that
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20120806/67a26a7a/attachment.html>
More information about the calendarserver-changes
mailing list