[CalendarServer-changes] [8429] CalendarServer/trunk

[source_changes at macosforge.org]

Revision: 8429
          http://trac.macosforge.org/projects/calendarserver/changeset/8429
Author:   glyph at apple.com
Date:     2011-12-12 14:33:20 -0800 (Mon, 12 Dec 2011)
Log Message:
-----------
Implement Select(..., Limit=...) for Oracle.

Modified Paths:
--------------
    CalendarServer/trunk/twext/enterprise/dal/syntax.py
    CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py

Property Changed:
----------------
    CalendarServer/trunk/

Modified: CalendarServer/trunk/twext/enterprise/dal/syntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/syntax.py	2011-12-12 22:31:22 UTC (rev 8428)
+++ CalendarServer/trunk/twext/enterprise/dal/syntax.py	2011-12-12 22:33:20 UTC (rev 8429)
@@ -840,8 +840,15 @@
         if self.ForUpdate:
             stmt.text += " for update"
         if self.Limit is not None:
-            stmt.text += " limit "
-            stmt.append(Constant(self.Limit).subSQL(metadata, allTables))
+            limitConst = Constant(self.Limit).subSQL(metadata, allTables)
+            if metadata.dialect == ORACLE_DIALECT:
+                wrapper = SQLFragment("select * from (")
+                wrapper.append(stmt)
+                wrapper.append(SQLFragment(") where ROWNUM <= "))
+                stmt = wrapper
+            else:
+                stmt.text += " limit "
+            stmt.append(limitConst)
         return stmt
 
 

Modified: CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py	2011-12-12 22:31:22 UTC (rev 8428)
+++ CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py	2011-12-12 22:33:20 UTC (rev 8429)
@@ -802,12 +802,14 @@
         self.assertEquals(ReleaseSavepoint("test").toSQL(),
                           SQLFragment("release savepoint test"))
 
+
     def test_savepointaction(self):
         """
         L{SavepointAction} generates a ('savepoint') statement.
         """
         self.assertEquals(SavepointAction("test")._name, "test")
 
+
     def test_limit(self):
         """
         A L{Select} object with a 'Limit' keyword parameter will generate
@@ -821,6 +823,25 @@
                 "select BAR from FOO limit ?", [123]))
 
 
+    def test_limitOracle(self):
+        """
+        A L{Select} object with a 'Limit' keyword parameter will generate a SQL
+        statement using a ROWNUM subquery for Oracle.
+
+        See U{this "ask tom" article from 2006 for more
+        information
+        <http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html>}.
+        """
+        self.assertEquals(
+            Select([self.schema.FOO.BAR],
+                   From=self.schema.FOO,
+                   Limit=123).toSQL(FixedPlaceholder(ORACLE_DIALECT, "?")),
+            SQLFragment(
+                "select * from (select BAR from FOO) "
+                "where ROWNUM <= ?", [123])
+        )
+
+
     def test_having(self):
         """
         A L{Select} object with a 'Having' keyword parameter will generate
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20111212/f26bf4a2/attachment.html>