[CalendarServer-changes] [7139] CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal

[source_changes at macosforge.org]

Revision: 7139
          http://trac.macosforge.org/projects/calendarserver/changeset/7139
Author:   glyph at apple.com
Date:     2011-03-07 19:04:48 -0800 (Mon, 07 Mar 2011)
Log Message:
-----------
generate the actual SQL statement for oracle dialect with holes for output parameters

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 03:04:37 UTC (rev 7138)
+++ CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/syntax.py	2011-03-08 03:04:48 UTC (rev 7139)
@@ -758,6 +758,19 @@
         if retclause is not None:
             stmt.text += ' returning '
             stmt.append(retclause.subSQL(metadata, allTables))
+            if metadata.dialect == ORACLE_DIALECT:
+                stmt.text += ' into '
+                if not isinstance(self.Return, (tuple, list)):
+                    retvals = [self.Return]
+                else:
+                    retvals = self.Return
+                params = []
+                for n, v in enumerate(retvals):
+                    params.append(
+                        Constant(Parameter("oracle_out_" + str(n)))
+                        .subSQL(metadata, allTables)
+                    )
+                stmt.append(_commaJoined(params))
         return stmt
 
 
@@ -1028,6 +1041,18 @@
         self.name = name
 
 
+    def __eq__(self, param):
+        if not isinstance(param, Parameter):
+            return NotImplemented
+        return self.name == param.name
+
+
+    def __ne__(self, param):
+        if not isinstance(param, Parameter):
+            return NotImplemented
+        return not self.__eq__(param)
+
+
     def __repr__(self):
         return 'Parameter(%r)' % (self.name,)
 

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 03:04:37 UTC (rev 7138)
+++ CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/test/test_sqlsyntax.py	2011-03-08 03:04:48 UTC (rev 7139)
@@ -27,7 +27,7 @@
 
 from twext.enterprise.dal.syntax import FunctionInvocation
 
-from twext.enterprise.dal.syntax import FixedPlaceholder
+from twext.enterprise.dal.syntax import FixedPlaceholder, NumericPlaceholder
 from twext.enterprise.ienterprise import POSTGRES_DIALECT, ORACLE_DIALECT
 from twisted.trial.unittest import TestCase
 
@@ -483,6 +483,27 @@
         )
 
 
+    def test_insertMultiReturnOracle(self):
+        """
+        In Oracle's SQL dialect, the 'returning' clause requires an 'into'
+        clause indicating where to put the results, as they can't be simply
+        relayed to the cursor.  Further, additional bound variables are required
+        to capture the output parameters.
+        """
+        self.assertEquals(
+            Insert({self.schema.FOO.BAR: 40,
+                    self.schema.FOO.BAZ: 50},
+                   Return=(self.schema.FOO.BAR, self.schema.FOO.BAZ)).toSQL(
+                       NumericPlaceholder(ORACLE_DIALECT)
+                   ),
+            SQLFragment(
+                "insert into FOO (BAR, BAZ) values (:1, :2) returning BAR, BAZ"
+                " into :3, :4",
+                [40, 50, Parameter("oracle_out_0"), Parameter("oracle_out_1")]
+            )
+        )
+
+
     def test_insertMismatch(self):
         """
         L{Insert} raises L{TableMismatch} if the columns specified aren't all
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20110307/a0dfa871/attachment.html>