[CalendarServer-changes] [7113] CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal
source_changes at macosforge.org
source_changes at macosforge.org
Mon Mar 7 18:59:32 PST 2011
Revision: 7113
http://trac.macosforge.org/projects/calendarserver/changeset/7113
Author: glyph at apple.com
Date: 2011-03-07 18:59:31 -0800 (Mon, 07 Mar 2011)
Log Message:
-----------
basic support for where-clause subselects.
Modified Paths:
--------------
CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/syntax.py
CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/test/test_sqlsyntax.py
Modified: CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/syntax.py
===================================================================
--- CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/syntax.py 2011-03-08 02:59:20 UTC (rev 7112)
+++ CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/syntax.py 2011-03-08 02:59:31 UTC (rev 7113)
@@ -89,6 +89,8 @@
def __(self, other):
if other is None:
return NullComparison(self, comparator)
+ if isinstance(other, Select):
+ return NotImplemented
if isinstance(other, ColumnSyntax):
return ColumnComparison(self, comparator, other)
else:
@@ -491,7 +493,23 @@
return True
+class Tuple(object):
+ def __init__(self, columns):
+ self.columns = columns
+
+
+ def subSQL(self, placeholder, quote, allTables):
+ return _inParens(_commaJoined(c.subSQL(placeholder, quote, allTables)
+ for c in self.columns))
+
+
+ def allColumns(self):
+ return self.columns
+
+
+
+
class Select(_Statement):
"""
'select' statement.
@@ -522,6 +540,15 @@
self.Ascending = Ascending
+ def __eq__(self, other):
+ """
+ Create a comparison.
+ """
+ if isinstance(other, (list, tuple)):
+ other = Tuple(other)
+ return CompoundComparison(other, '=', self)
+
+
def toSQL(self, placeholder="?", quote=lambda x: x):
"""
@return: a 'select' statement with placeholders and arguments
Modified: CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/test/test_sqlsyntax.py
===================================================================
--- CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/test/test_sqlsyntax.py 2011-03-08 02:59:20 UTC (rev 7112)
+++ CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/test/test_sqlsyntax.py 2011-03-08 02:59:31 UTC (rev 7113)
@@ -38,7 +38,7 @@
addSQLToSchema(schema=s, schemaData="""
create sequence A_SEQ;
create table FOO (BAR integer, BAZ integer);
- create table BOZ (QUX integer);
+ create table BOZ (QUX integer, QUUX integer);
create table OTHER (BAR integer,
FOO_BAR integer not null);
create table TEXTUAL (MYTEXT varchar(255));
@@ -664,10 +664,12 @@
self.schema.A_SEQ}).toSQL(),
SQLFragment("insert into BOZ (QUX) values (nextval('A_SEQ'))", []))
+
def test_nestedLogicalExpressions(self):
"""
- Make sure that logical operator precedence inserts proper parenthesis when needed.
- e.g. 'a.And(b.Or(c))' needs to be 'a and (b or c)' not 'a and b or c'.
+ Make sure that logical operator precedence inserts proper parenthesis
+ when needed. e.g. 'a.And(b.Or(c))' needs to be 'a and (b or c)' not 'a
+ and b or c'.
"""
self.assertEquals(
Select(
@@ -676,7 +678,8 @@
And(self.schema.FOO.BAZ != 8).
And((self.schema.FOO.BAR == 8).Or(self.schema.FOO.BAZ == 0))
).toSQL(),
- SQLFragment("select * from FOO where BAR != ? and BAZ != ? and (BAR = ? or BAZ = ?)", [7, 8, 8, 0]))
+ SQLFragment("select * from FOO where BAR != ? and BAZ != ? and "
+ "(BAR = ? or BAZ = ?)", [7, 8, 8, 0]))
self.assertEquals(
Select(
@@ -685,7 +688,8 @@
Or(self.schema.FOO.BAZ != 8).
Or((self.schema.FOO.BAR == 8).And(self.schema.FOO.BAZ == 0))
).toSQL(),
- SQLFragment("select * from FOO where BAR != ? or BAZ != ? or BAR = ? and BAZ = ?", [7, 8, 8, 0]))
+ SQLFragment("select * from FOO where BAR != ? or BAZ != ? or "
+ "BAR = ? and BAZ = ?", [7, 8, 8, 0]))
self.assertEquals(
Select(
@@ -694,4 +698,53 @@
Or(self.schema.FOO.BAZ != 8).
And((self.schema.FOO.BAR == 8).Or(self.schema.FOO.BAZ == 0))
).toSQL(),
- SQLFragment("select * from FOO where (BAR != ? or BAZ != ?) and (BAR = ? or BAZ = ?)", [7, 8, 8, 0]))
+ SQLFragment("select * from FOO where (BAR != ? or BAZ != ?) and "
+ "(BAR = ? or BAZ = ?)", [7, 8, 8, 0]))
+
+
+ def test_subSelectComparison(self):
+ """
+ A comparison of a column to a sub-select in a where clause will result
+ in a parenthetical 'Where' clause.
+ """
+ self.assertEquals(
+ Update(
+ {self.schema.BOZ.QUX: 9},
+ Where=self.schema.BOZ.QUX ==
+ Select([self.schema.FOO.BAR], From=self.schema.FOO,
+ Where=self.schema.FOO.BAZ == 12)).toSQL(),
+ SQLFragment(
+ # NOTE: it's very important that the comparison _always_ go in
+ # this order (column from the UPDATE first, inner SELECT second)
+ # as the other order will be considered a syntax error.
+ "update BOZ set QUX = ? where QUX = ("
+ "select BAR from FOO where BAZ = ?)", [9, 12]
+ )
+ )
+
+
+ def test_tupleComparison(self):
+ """
+ A L{Tuple} allows for simultaneous comparison of multiple values in a
+ C{Where} clause. This feature is particularly useful when issuing an
+ L{Update} or L{Delete}, where the comparison is with values from a
+ subselect. (A L{Tuple} will be automatically generated upon comparison
+ to a C{tuple} or C{list}.)
+ """
+ self.assertEquals(
+ Update(
+ {self.schema.BOZ.QUX: 1},
+ Where=(self.schema.BOZ.QUX, self.schema.BOZ.QUUX) ==
+ Select([self.schema.FOO.BAR, self.schema.FOO.BAZ],
+ From=self.schema.FOO,
+ Where=self.schema.FOO.BAZ == 2)).toSQL(),
+ SQLFragment(
+ # NOTE: it's very important that the comparison _always_ go in
+ # this order (tuple of columns from the UPDATE first, inner
+ # SELECT second) as the other order will be considered a syntax
+ # error.
+ "update BOZ set QUX = ? where (QUX, QUUX) = ("
+ "select BAR, BAZ from FOO where BAZ = ?)", [1, 2]
+ )
+ )
+
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20110307/cce82118/attachment-0001.html>
More information about the calendarserver-changes
mailing list