[CalendarServer-changes] [7040] CalendarServer/trunk/twext/enterprise/dal

[source_changes at macosforge.org]

Revision: 7040
          http://trac.macosforge.org/projects/calendarserver/changeset/7040
Author:   glyph at apple.com
Date:     2011-02-17 16:40:17 -0800 (Thu, 17 Feb 2011)
Log Message:
-----------
Implementation of multi-table joins.

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	2011-02-17 22:46:22 UTC (rev 7039)
+++ CalendarServer/trunk/twext/enterprise/dal/syntax.py	2011-02-18 00:40:17 UTC (rev 7040)
@@ -249,7 +249,9 @@
 
     modelType = Table
 
-    def join(self, otherTableSyntax, on, type=''):
+    def join(self, otherTableSyntax, on=None, type=''):
+        if on is None:
+            type = 'cross'
         return Join(self, type, otherTableSyntax, on)
 
 
@@ -291,33 +293,57 @@
 
 
 class Join(object):
+    """
+    A DAL object representing an SQL 'join' statement.
 
-    def __init__(self, firstTable, type, secondTableOrJoin, on):
-        self.firstTable = firstTable
+    @ivar leftSide: a L{Join} or L{TableSyntax} representing the left side of
+        this join.
+
+    @ivar rightSide: a L{TableSyntax} representing the right side of this join.
+
+    @ivar type: the type of join this is.  For example, for a left outer join,
+        this would be C{'left outer'}.
+    @type type: C{str}
+
+    @ivar on: the 'on' clause of this table.
+
+    @type on: L{ExpressionSyntax}
+    """
+
+    def __init__(self, leftSide, type, rightSide, on):
+        self.leftSide = leftSide
         self.type = type
-        self.secondTableOrJoin = secondTableOrJoin
+        self.rightSide = rightSide
         self.on = on
 
 
     def subSQL(self, placeholder, quote, allTables):
         stmt = SQLFragment()
-        stmt.append(self.firstTable.subSQL(placeholder, quote, allTables))
+        stmt.append(self.leftSide.subSQL(placeholder, quote, allTables))
         stmt.text += ' '
         if self.type:
             stmt.text += self.type
             stmt.text += ' '
         stmt.text += 'join '
-        stmt.append(self.secondTableOrJoin.subSQL(placeholder, quote, allTables))
-        stmt.text += ' on '
-        stmt.append(self.on.subSQL(placeholder, quote, allTables))
+        stmt.append(self.rightSide.subSQL(placeholder, quote, allTables))
+        if self.type != 'cross':
+            stmt.text += ' on '
+            stmt.append(self.on.subSQL(placeholder, quote, allTables))
         return stmt
 
 
     def tables(self):
-        return self.firstTable.tables() + self.secondTableOrJoin.tables()
+        return self.leftSide.tables() + self.rightSide.tables()
 
 
+    def join(self, otherTable, on=None, type=None):
+        if on is None:
+            type = 'cross'
+        return Join(self, type, otherTable, on)
 
+
+
+
 class ColumnSyntax(ExpressionSyntax):
     """
     Syntactic convenience for L{Column}.

Modified: CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py	2011-02-17 22:46:22 UTC (rev 7039)
+++ CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py	2011-02-18 00:40:17 UTC (rev 7040)
@@ -186,6 +186,51 @@
         )
 
 
+    def test_crossJoin(self):
+        """
+        A join with no clause specified will generate a cross join.  (This is an
+        explicit synonym for an implicit join: i.e. 'select * from FOO, BAR'.)
+        """
+        self.assertEquals(
+            Select(From=self.schema.FOO.join(self.schema.BOZ)).toSQL(),
+            SQLFragment("select * from FOO cross join BOZ")
+        )
+
+
+    def test_joinJoin(self):
+        """
+        L{Join.join} will result in a multi-table join.
+        """
+        self.assertEquals(
+            Select([self.schema.FOO.BAR,
+                    self.schema.BOZ.QUX],
+                   From=self.schema.FOO
+                   .join(self.schema.BOZ).join(self.schema.OTHER)).toSQL(),
+            SQLFragment(
+                "select FOO.BAR, QUX from FOO "
+                "cross join BOZ cross join OTHER")
+        )
+
+
+    def test_multiJoin(self):
+        """
+        L{Join.join} has the same signature as L{TableSyntax.join} and supports
+        the same 'on' and 'type' arguments.
+        """
+
+        self.assertEquals(
+            Select([self.schema.FOO.BAR],
+                   From=self.schema.FOO.join(
+                       self.schema.BOZ).join(
+                           self.schema.OTHER,
+                           self.schema.OTHER.BAR == self.schema.FOO.BAR,
+                           'left outer')).toSQL(),
+            SQLFragment(
+                "select FOO.BAR from FOO cross join BOZ left outer join OTHER "
+                "on OTHER.BAR = FOO.BAR")
+        )
+
+
     def test_columnSelection(self):
         """
         If a column is specified by the argument to L{Select}, those will be
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20110217/a26bd80e/attachment.html>