[CalendarServer-changes] [7140] CalendarServer/branches/users/glyph/oracle
source_changes at macosforge.org
source_changes at macosforge.org
Mon Mar 7 19:05:04 PST 2011
Revision: 7140
http://trac.macosforge.org/projects/calendarserver/changeset/7140
Author: glyph at apple.com
Date: 2011-03-07 19:05:04 -0800 (Mon, 07 Mar 2011)
Log Message:
-----------
push oracle out-parameter approach to Return= through all necessary layers of abstraction. Still only supports one value per result, but with callback support it's at least hypothetically possible that this approach might be extended later.
Modified Paths:
--------------
CalendarServer/branches/users/glyph/oracle/twext/enterprise/adbapi2.py
CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/syntax.py
CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/test/test_sqlsyntax.py
CalendarServer/branches/users/glyph/oracle/twext/enterprise/ienterprise.py
CalendarServer/branches/users/glyph/oracle/twext/enterprise/test/test_adbapi2.py
CalendarServer/branches/users/glyph/oracle/txdav/base/datastore/dbapiclient.py
Modified: CalendarServer/branches/users/glyph/oracle/twext/enterprise/adbapi2.py
===================================================================
--- CalendarServer/branches/users/glyph/oracle/twext/enterprise/adbapi2.py 2011-03-08 03:04:48 UTC (rev 7139)
+++ CalendarServer/branches/users/glyph/oracle/twext/enterprise/adbapi2.py 2011-03-08 03:05:04 UTC (rev 7140)
@@ -1,3 +1,4 @@
+from twext.enterprise.ienterprise import IDerivedParameter
# -*- test-case-name: twext.enterprise.test.test_adbapi2 -*-
##
# Copyright (c) 2010 Apple Inc. All rights reserved.
@@ -99,6 +100,7 @@
The paramstyle attribute is mirrored from the connection pool.
"""
+
@_forward
def dialect(self):
"""
@@ -109,7 +111,18 @@
def _reallyExecSQL(self, sql, args=None, raiseOnZeroRowCount=None):
if args is None:
args = []
+ derived = None
+ for arg in args:
+ if IDerivedParameter.providedBy(arg):
+ if derived is None:
+ # Be sparing with allocations, as this usually isn't needed.
+ derived = []
+ derived.append(arg)
+ arg.preQuery(self._cursor)
self._cursor.execute(sql, args)
+ if derived is not None:
+ for arg in derived:
+ arg.postQuery(self._cursor)
if raiseOnZeroRowCount is not None and self._cursor.rowcount == 0:
raise raiseOnZeroRowCount()
if self._cursor.description:
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:48 UTC (rev 7139)
+++ CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/syntax.py 2011-03-08 03:05:04 UTC (rev 7140)
@@ -21,10 +21,18 @@
import itertools
+from zope.interface import implements
+
from twext.enterprise.ienterprise import POSTGRES_DIALECT, ORACLE_DIALECT
+from twext.enterprise.ienterprise import IDerivedParameter
+from twext.enterprise.util import mapOracleOutputType
from twext.enterprise.dal.model import Schema, Table, Column, Sequence
+try:
+ import cx_Oracle
+except ImportError:
+ cx_Oracle = None
class ConnectionMetadata(object):
"""
@@ -109,15 +117,26 @@
return self._toSQL(metadata)
+ def _extraVars(self, txn, metadata):
+ return {}
+
+
+ def _extraResult(self, result, outvars):
+ return result
+
+
def on(self, txn, raiseOnZeroRowCount=None, **kw):
"""
Execute this statement on a given L{IAsyncTransaction} and return the
resulting L{Deferred}.
"""
metadata = self._paramstyles[txn.paramstyle](txn.dialect)
+ outvars = self._extraVars(txn, metadata)
+ kw.update(outvars)
fragment = self.toSQL(metadata).bind(**kw)
- return txn.execSQL(fragment.text, fragment.parameters,
- raiseOnZeroRowCount)
+ result = txn.execSQL(fragment.text, fragment.parameters,
+ raiseOnZeroRowCount)
+ return self._extraResult(result, outvars)
@@ -760,11 +779,8 @@
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 = []
+ retvals = self._returnAsList()
for n, v in enumerate(retvals):
params.append(
Constant(Parameter("oracle_out_" + str(n)))
@@ -774,7 +790,51 @@
return stmt
+ def _returnAsList(self):
+ if not isinstance(self.Return, (tuple, list)):
+ return [self.Return]
+ else:
+ return self.Return
+
+ def _extraVars(self, txn, metadata):
+ result = []
+ rvars = self._returnAsList()
+ if metadata.dialect == ORACLE_DIALECT:
+ for n, v in enumerate(rvars):
+ result.append(("oracle_out_" + str(n), _OracleOutParam(v)))
+ return result
+
+
+ def _extraResult(self, result, outvars):
+ def processIt(shouldBeNone):
+ return [[v.value for k, v in outvars]]
+ return result.addCallback(processIt)
+
+
+
+class _OracleOutParam(object):
+ implements(IDerivedParameter)
+
+ def __init__(self, columnSyntax):
+ self.columnSyntax = columnSyntax
+
+
+ def preQuery(self, cursor):
+ self.columnSyntax
+ typeMap = {'integer': cx_Oracle.NUMBER,
+ 'text': cx_Oracle.CLOB,
+ 'varchar': cx_Oracle.STRING,
+ 'timestamp': cx_Oracle.TIMESTAMP}
+ typeID = self.columnSyntax.model.type.name.lower()
+ self.var = cursor.var(typeMap[typeID])
+
+
+ def postQuery(self, cursor):
+ self.value = mapOracleOutputType(self.var.getvalue())
+
+
+
class Insert(_DMLStatement):
"""
'insert' statement.
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:48 UTC (rev 7139)
+++ CalendarServer/branches/users/glyph/oracle/twext/enterprise/dal/test/test_sqlsyntax.py 2011-03-08 03:05:04 UTC (rev 7140)
@@ -20,6 +20,7 @@
from twext.enterprise.dal.model import Schema
from twext.enterprise.dal.parseschema import addSQLToSchema
+from twext.enterprise.dal import syntax
from twext.enterprise.dal.syntax import (
SchemaSyntax, Select, Insert, Update, Delete, Lock, SQLFragment,
TableMismatch, Parameter, Max, Len, NotEnoughValues
@@ -29,6 +30,10 @@
from twext.enterprise.dal.syntax import FixedPlaceholder, NumericPlaceholder
from twext.enterprise.ienterprise import POSTGRES_DIALECT, ORACLE_DIALECT
+from twext.enterprise.test.test_adbapi2 import ConnectionFactory
+from twext.enterprise.adbapi2 import ConnectionPool
+from twext.enterprise.test.test_adbapi2 import resultOf
+from twext.enterprise.test.test_adbapi2 import FakeThreadHolder
from twisted.trial.unittest import TestCase
@@ -53,7 +58,7 @@
s = Schema(self.id())
addSQLToSchema(schema=s, schemaData="""
create sequence A_SEQ;
- create table FOO (BAR integer, BAZ integer);
+ create table FOO (BAR integer, BAZ varchar(255));
create table BOZ (QUX integer, QUUX integer);
create table OTHER (BAR integer,
FOO_BAR integer not null);
@@ -504,6 +509,39 @@
)
+ def test_insertMultiReturnOnOracleTxn(self):
+ """
+ As described in L{test_insertMultiReturnOracle}, Oracle deals with
+ 'returning' clauses by using out parameters. However, this is not quite
+ enough, as the code needs to actually retrieve the values from the out
+ parameters.
+ """
+ class FakeCXOracleModule(object):
+ NUMBER = 'the NUMBER type'
+ STRING = 'a string type (for varchars)'
+ CLOB = 'the clob type. (for text)'
+ TIMESTAMP = 'for timestamps!'
+ self.patch(syntax, 'cx_Oracle', FakeCXOracleModule)
+ factory = ConnectionFactory()
+ pool = ConnectionPool(factory.connect, maxConnections=2,
+ dialect=ORACLE_DIALECT,
+ paramstyle='numeric')
+ self.paused = False
+ pool._createHolder = lambda : FakeThreadHolder(self)
+ pool.startService()
+ conn = pool.connection()
+ i = Insert({self.schema.FOO.BAR: 40,
+ self.schema.FOO.BAZ: 50},
+ Return=(self.schema.FOO.BAR, self.schema.FOO.BAZ))
+ # See fake result generation in test_adbapi2.py.
+ result = resultOf(i.on(conn))
+ self.assertEquals(result, [[[300, 301]]])
+ curvars = factory.connections[0].cursors[0].variables
+ self.assertEquals(len(curvars), 2)
+ self.assertEquals(curvars[0].type, FakeCXOracleModule.NUMBER)
+ self.assertEquals(curvars[1].type, FakeCXOracleModule.STRING)
+
+
def test_insertMismatch(self):
"""
L{Insert} raises L{TableMismatch} if the columns specified aren't all
Modified: CalendarServer/branches/users/glyph/oracle/twext/enterprise/ienterprise.py
===================================================================
--- CalendarServer/branches/users/glyph/oracle/twext/enterprise/ienterprise.py 2011-03-08 03:04:48 UTC (rev 7139)
+++ CalendarServer/branches/users/glyph/oracle/twext/enterprise/ienterprise.py 2011-03-08 03:05:04 UTC (rev 7140)
@@ -103,3 +103,40 @@
rollback of this transaction.
"""
+
+
+class IDerivedParameter(Interface):
+ """
+ A parameter which needs to be derived from the underlying DB-API cursor;
+ implicitly, meaning that this must also interact with the actual thread
+ manipulating said cursor. If a provider of this interface is passed in the
+ C{args} argument to L{IAsyncTransaction.execSQL}, it will have its
+ C{prequery} and C{postquery} methods invoked on it before and after
+ executing the SQL query in question, respectively.
+ """
+
+ def preQuery(cursor):
+ """
+ Before running a query, invoke this method with the cursor that the
+ query will be run on.
+
+ (This can be used, for example, to allocate a special database-specific
+ variable based on the cursor, like an out parameter.)
+
+ @param cursor: the DB-API cursor.
+
+ @return: the concrete value which should be passed to the DB-API layer.
+ """
+
+
+ def postQuery(cursor):
+ """
+ After running a query, invoke this method in the DB-API thread.
+
+ (This can be used, for example, to manipulate any state created in the
+ preQuery method.)
+
+ @param cursor: the DB-API cursor.
+
+ @return: C{None}
+ """
Modified: CalendarServer/branches/users/glyph/oracle/twext/enterprise/test/test_adbapi2.py
===================================================================
--- CalendarServer/branches/users/glyph/oracle/twext/enterprise/test/test_adbapi2.py 2011-03-08 03:04:48 UTC (rev 7139)
+++ CalendarServer/branches/users/glyph/oracle/twext/enterprise/test/test_adbapi2.py 2011-03-08 03:05:04 UTC (rev 7140)
@@ -131,6 +131,7 @@
self.rowcount = 0
# not entirely correct, but all we care about is its truth value.
self.description = False
+ self.variables = []
@property
@@ -146,6 +147,15 @@
return
+ def var(self, type, *args):
+ """
+ Return a database variable in the style of the cx_Oracle bindings.
+ """
+ v = FakeVariable(self, type, args)
+ self.variables.append(v)
+ return v
+
+
def fetchall(self):
"""
Just echo the SQL that was executed in the last query.
@@ -154,6 +164,18 @@
+class FakeVariable(object):
+ def __init__(self, cursor, type, args):
+ self.cursor = cursor
+ self.type = type
+ self.args = args
+
+
+ def getvalue(self):
+ return self.cursor.variables.index(self) + 300
+
+
+
class ConnectionFactory(Parent):
rollbackFail = False
Modified: CalendarServer/branches/users/glyph/oracle/txdav/base/datastore/dbapiclient.py
===================================================================
--- CalendarServer/branches/users/glyph/oracle/txdav/base/datastore/dbapiclient.py 2011-03-08 03:04:48 UTC (rev 7139)
+++ CalendarServer/branches/users/glyph/oracle/txdav/base/datastore/dbapiclient.py 2011-03-08 03:05:04 UTC (rev 7140)
@@ -85,6 +85,16 @@
return accum
+ def var(self, *args):
+ """
+ Create a cx_Oracle variable bound to this cursor. (Forwarded in
+ addition to the standard methods so that implementors of
+ L{IDerivedParameter} do not need to be specifically aware of this
+ layer.)
+ """
+ return self.realCursor.var(*args)
+
+
def execute(self, sql, args=()):
realArgs = []
for arg in args:
@@ -96,7 +106,7 @@
# sure why cx_Oracle itself doesn't just do the following hack
# automatically and internally for larger values too, but, here
# it is:
- v = self.realCursor.var(cx_Oracle.CLOB, len(arg) + 1)
+ v = self.var(cx_Oracle.CLOB, len(arg) + 1)
v.setvalue(0, arg)
realArgs.append(v)
return super(OracleCursorWrapper, self).execute(sql, realArgs)
@@ -180,7 +190,7 @@
def __init__(self, dsn):
super(OracleConnector, self).__init__(
- cx_Oracle, lambda whatever: None, dsn)
+ cx_Oracle, lambda whatever: None, dsn, threaded=True)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20110307/982a79ae/attachment-0001.html>
More information about the calendarserver-changes
mailing list