[CalendarServer-changes] [7156] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Mon Mar 7 19:32:37 PST 2011
Revision: 7156
http://trac.macosforge.org/projects/calendarserver/changeset/7156
Author: glyph at apple.com
Date: 2011-03-07 19:32:37 -0800 (Mon, 07 Mar 2011)
Log Message:
-----------
Initial Oracle database support.
Modified Paths:
--------------
CalendarServer/trunk/calendarserver/tap/caldav.py
CalendarServer/trunk/calendarserver/tap/util.py
CalendarServer/trunk/twext/enterprise/adbapi2.py
CalendarServer/trunk/twext/enterprise/dal/model.py
CalendarServer/trunk/twext/enterprise/dal/parseschema.py
CalendarServer/trunk/twext/enterprise/dal/syntax.py
CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py
CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py
CalendarServer/trunk/twext/enterprise/ienterprise.py
CalendarServer/trunk/twext/enterprise/test/test_adbapi2.py
CalendarServer/trunk/twistedcaldav/dateops.py
CalendarServer/trunk/twistedcaldav/query/calendarquery.py
CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py
CalendarServer/trunk/txdav/base/datastore/dbapiclient.py
CalendarServer/trunk/txdav/caldav/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql_legacy.py
CalendarServer/trunk/txdav/common/datastore/sql_tables.py
Added Paths:
-----------
CalendarServer/trunk/twext/enterprise/util.py
Property Changed:
----------------
CalendarServer/trunk/
CalendarServer/trunk/txdav/caldav/datastore/index_file.py
CalendarServer/trunk/txdav/caldav/datastore/test/test_index_file.py
CalendarServer/trunk/txdav/carddav/datastore/index_file.py
CalendarServer/trunk/txdav/carddav/datastore/test/test_index_file.py
Property changes on: CalendarServer/trunk
___________________________________________________________________
Modified: svn:mergeinfo
- /CalendarServer/branches/config-separation:4379-4443
/CalendarServer/branches/egg-info-351:4589-4625
/CalendarServer/branches/generic-sqlstore:6167-6191
/CalendarServer/branches/new-store:5594-5934
/CalendarServer/branches/new-store-no-caldavfile:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187:5188-5440
/CalendarServer/branches/users/glyph/conn-limit:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge:4971-5080
/CalendarServer/branches/users/glyph/dalify:6932-7023
/CalendarServer/branches/users/glyph/db-reconnect:6824-6876
/CalendarServer/branches/users/glyph/dont-start-postgres:6592-6614
/CalendarServer/branches/users/glyph/linux-tests:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7:6369-6445
/CalendarServer/branches/users/glyph/sendfdport:5388-5424
/CalendarServer/branches/users/glyph/sharedpool:6490-6550
/CalendarServer/branches/users/glyph/sql-store:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted:5084-5149
/CalendarServer/branches/users/sagen/locations-resources:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066:4068-4075
/CalendarServer/branches/users/sagen/resources-2:5084-5093
/CalendarServer/branches/users/wsanchez/transations:5515-5593
+ /CalendarServer/branches/config-separation:4379-4443
/CalendarServer/branches/egg-info-351:4589-4625
/CalendarServer/branches/generic-sqlstore:6167-6191
/CalendarServer/branches/new-store:5594-5934
/CalendarServer/branches/new-store-no-caldavfile:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187:5188-5440
/CalendarServer/branches/users/glyph/conn-limit:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge:4971-5080
/CalendarServer/branches/users/glyph/dalify:6932-7023
/CalendarServer/branches/users/glyph/db-reconnect:6824-6876
/CalendarServer/branches/users/glyph/dont-start-postgres:6592-6614
/CalendarServer/branches/users/glyph/linux-tests:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7:6369-6445
/CalendarServer/branches/users/glyph/oracle:7106-7155
/CalendarServer/branches/users/glyph/sendfdport:5388-5424
/CalendarServer/branches/users/glyph/sharedpool:6490-6550
/CalendarServer/branches/users/glyph/sql-store:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted:5084-5149
/CalendarServer/branches/users/sagen/locations-resources:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066:4068-4075
/CalendarServer/branches/users/sagen/resources-2:5084-5093
/CalendarServer/branches/users/wsanchez/transations:5515-5593
Modified: CalendarServer/trunk/calendarserver/tap/caldav.py
===================================================================
--- CalendarServer/trunk/calendarserver/tap/caldav.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/calendarserver/tap/caldav.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -73,6 +73,8 @@
from calendarserver.tap.util import pgServiceFromConfig
+from twext.enterprise.ienterprise import POSTGRES_DIALECT
+from twext.enterprise.ienterprise import ORACLE_DIALECT
from twext.enterprise.adbapi2 import ConnectionPool
from twext.enterprise.adbapi2 import ConnectionPoolConnection
@@ -90,6 +92,7 @@
from calendarserver.tap.util import storeFromConfig
from calendarserver.tap.util import transactionFactoryFromFD
from calendarserver.tap.util import pgConnectorFromConfig
+from calendarserver.tap.util import oracleConnectorFromConfig
from calendarserver.tools.util import checkDirectory
try:
@@ -641,6 +644,8 @@
elif not config.UseDatabase:
txnFactory = None
elif not config.SharedConnectionPool:
+ dialect = POSTGRES_DIALECT
+ paramstyle = 'pyformat'
if config.DBType == '':
# get a PostgresService to tell us what the local connection
# info is, but *don't* start it (that would start one postgres
@@ -649,9 +654,14 @@
config, None).produceConnection
elif config.DBType == 'postgres':
connectionFactory = pgConnectorFromConfig(config)
+ elif config.DBType == 'oracle':
+ dialect = ORACLE_DIALECT
+ paramstyle = 'numeric'
+ connectionFactory = oracleConnectorFromConfig(config)
else:
raise UsageError("unknown DB type: %r" % (config.DBType,))
- pool = ConnectionPool(connectionFactory)
+ pool = ConnectionPool(connectionFactory, dialect=dialect,
+ paramstyle=paramstyle)
txnFactory = pool.connection
else:
raise UsageError(
@@ -885,6 +895,7 @@
return self.storageService(toolServiceCreator)
+
def storageService(self, createMainService, uid=None, gid=None):
"""
If necessary, create a service to be started used for storage; for
@@ -939,6 +950,12 @@
return self.subServiceFactoryFactory(createMainService,
uid=overrideUID, gid=overrideGID)(
pgConnectorFromConfig(config))
+ elif config.DBType == 'oracle':
+ # Connect to an Oracle database that is already running.
+ return self.subServiceFactoryFactory(createMainService,
+ uid=overrideUID, gid=overrideGID,
+ dialect=ORACLE_DIALECT, paramstyle='numeric')(
+ oracleConnectorFromConfig(config))
else:
raise UsageError("Unknown database type %r" (config.DBType,))
else:
@@ -946,11 +963,13 @@
return createMainService(None, store)
- def subServiceFactoryFactory(self, createMainService,
- uid=None, gid=None):
+ def subServiceFactoryFactory(self, createMainService, uid=None, gid=None,
+ dialect=POSTGRES_DIALECT,
+ paramstyle='pyformat'):
def subServiceFactory(connectionFactory):
ms = MultiService()
- cp = ConnectionPool(connectionFactory)
+ cp = ConnectionPool(connectionFactory, dialect=dialect,
+ paramstyle=paramstyle)
cp.setServiceParent(ms)
store = storeFromConfig(config, cp.connection)
mainService = createMainService(cp, store)
Modified: CalendarServer/trunk/calendarserver/tap/util.py
===================================================================
--- CalendarServer/trunk/calendarserver/tap/util.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/calendarserver/tap/util.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -69,7 +69,7 @@
NegotiateCredentialFactory = None
from twext.enterprise.adbapi2 import ConnectionPoolClient
-from txdav.base.datastore.dbapiclient import DBAPIConnector
+from txdav.base.datastore.dbapiclient import DBAPIConnector, OracleConnector
from txdav.base.datastore.dbapiclient import postgresPreflight
from txdav.base.datastore.subpostgres import PostgresService
@@ -132,6 +132,14 @@
+def oracleConnectorFromConfig(config):
+ """
+ Create a postgres DB-API connector from the given configuration.
+ """
+ return OracleConnector(config.DSN).connect
+
+
+
class ConnectionWithPeer(Connection):
connected = True
@@ -139,10 +147,12 @@
def getPeer(self):
return "<peer: %r %r>" % (self.socket.fileno(), id(self))
+
def getHost(self):
return "<host: %r %r>" % (self.socket.fileno(), id(self))
+
def transactionFactoryFromFD(dbampfd):
"""
Create a transaction factory from an inherited file descriptor.
Modified: CalendarServer/trunk/twext/enterprise/adbapi2.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/adbapi2.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twext/enterprise/adbapi2.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -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.
@@ -53,15 +54,31 @@
from twisted.internet.defer import succeed
from twext.enterprise.ienterprise import ConnectionError
from twisted.internet.defer import fail
-from twext.enterprise.ienterprise import AlreadyFinishedError, IAsyncTransaction
+from twext.enterprise.ienterprise import (
+ AlreadyFinishedError, IAsyncTransaction, POSTGRES_DIALECT
+)
-# FIXME: there should be no default for DEFAULT_PARAM_STYLE, it should be
-# discovered dynamically everywhere. Right now we're only using pgdb so we only
-# support that.
+# FIXME: there should be no defaults for connection metadata, it should be
+# discovered dynamically everywhere. Right now it's specified as an explicit
+# argument to the ConnectionPool but it should probably be determined
+# automatically from the database binding.
DEFAULT_PARAM_STYLE = 'pyformat'
+DEFAULT_DIALECT = POSTGRES_DIALECT
+
+def _forward(thunk):
+ """
+ Forward an attribute to the connection pool.
+ """
+ @property
+ def getter(self):
+ return getattr(self._pool, thunk.func_name)
+ return getter
+
+
+
class _ConnectedTxn(object):
"""
L{IAsyncTransaction} implementation based on a L{ThreadHolder} in the
@@ -69,9 +86,6 @@
"""
implements(IAsyncTransaction)
- # See DEFAULT_PARAM_STYLE FIXME above.
- paramstyle = DEFAULT_PARAM_STYLE
-
def __init__(self, pool, threadHolder, connection, cursor):
self._pool = pool
self._completed = True
@@ -80,10 +94,36 @@
self._holder = threadHolder
+ @_forward
+ def paramstyle(self):
+ """
+ The paramstyle attribute is mirrored from the connection pool.
+ """
+
+
+ @_forward
+ def dialect(self):
+ """
+ The dialect attribute is mirrored from the connection pool.
+ """
+
+
def _reallyExecSQL(self, sql, args=None, raiseOnZeroRowCount=None):
if args is None:
args = []
+ derived = None
+ for n, arg in enumerate(args):
+ if IDerivedParameter.providedBy(arg):
+ if derived is None:
+ # Be sparing with extra allocations, as this usually isn't
+ # needed, and we're doing a ton of extra work to support it.
+ derived = []
+ derived.append(arg)
+ args[n] = 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:
@@ -171,6 +211,7 @@
return holder.stop()
+
class _NoTxn(object):
"""
An L{IAsyncTransaction} that indicates a local failure before we could even
@@ -179,12 +220,18 @@
"""
implements(IAsyncTransaction)
+ def __init__(self, pool):
+ self.paramstyle = pool.paramstyle
+ self.dialect = pool.dialect
+
+
def _everything(self, *a, **kw):
"""
Everything fails with a L{ConnectionError}.
"""
return fail(ConnectionError())
+
execSQL = _everything
commit = _everything
abort = _everything
@@ -201,11 +248,10 @@
implements(IAsyncTransaction)
- # See DEFAULT_PARAM_STYLE FIXME above.
- paramstyle = DEFAULT_PARAM_STYLE
-
- def __init__(self):
+ def __init__(self, pool):
self._spool = []
+ self.paramstyle = pool.paramstyle
+ self.dialect = pool.dialect
def _enspool(self, cmd, a=(), kw={}):
@@ -257,7 +303,7 @@
class _SingleTxn(proxyForInterface(iface=IAsyncTransaction,
- originalAttribute='_baseTxn')):
+ originalAttribute='_baseTxn')):
"""
A L{_SingleTxn} is a single-use wrapper for the longer-lived
L{_ConnectedTxn}, so that if a badly-behaved API client accidentally hangs
@@ -320,7 +366,7 @@
Stop waiting for a free transaction and fail.
"""
self._pool._waiting.remove(self)
- self._unspoolOnto(_NoTxn())
+ self._unspoolOnto(_NoTxn(self._pool))
def _checkComplete(self):
@@ -420,11 +466,14 @@
RETRY_TIMEOUT = 10.0
- def __init__(self, connectionFactory, maxConnections=10):
+ def __init__(self, connectionFactory, maxConnections=10,
+ paramstyle=DEFAULT_PARAM_STYLE, dialect=DEFAULT_DIALECT):
super(ConnectionPool, self).__init__()
self.connectionFactory = connectionFactory
self.maxConnections = maxConnections
+ self.paramstyle = paramstyle
+ self.dialect = dialect
self._free = []
self._busy = []
@@ -495,13 +544,13 @@
@return: an L{IAsyncTransaction}
"""
if self._stopping:
- return _NoTxn()
+ return _NoTxn(self)
if self._free:
basetxn = self._free.pop(0)
self._busy.append(basetxn)
txn = _SingleTxn(self, basetxn)
else:
- txn = _SingleTxn(self, _WaitingTxn())
+ txn = _SingleTxn(self, _WaitingTxn(self))
self._waiting.append(txn)
# FIXME/TESTME: should be len(self._busy) + len(self._finishing)
# (free doesn't need to be considered, as it's tested above)
Modified: CalendarServer/trunk/twext/enterprise/dal/model.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/model.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twext/enterprise/dal/model.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -18,6 +18,7 @@
"""
Model classes for SQL.
"""
+from twisted.python.util import FancyEqMixin
class SQLType(object):
"""
@@ -123,7 +124,7 @@
-class Column(object):
+class Column(FancyEqMixin, object):
"""
A column from a table.
@@ -140,8 +141,15 @@
@ivar references: If this column references a foreign key on another table,
this will be a reference to that table; otherwise (normally) C{None}.
@type references: L{Table} or C{NoneType}
+
+ @ivar cascade: If this column references another table, will this column's
+ row be deleted when the matching row in that other table is deleted?
+ (In other words, the SQL feature 'on delete cascade'.)
+ @type cascade: C{bool}
"""
+ compareAttributes = 'table name'.split()
+
def __init__(self, table, name, type):
_checkstr(name)
self.table = table
@@ -149,6 +157,7 @@
self.type = type
self.default = NO_DEFAULT
self.references = None
+ self.cascade = False
def __repr__(self):
@@ -204,7 +213,7 @@
-class Table(object):
+class Table(FancyEqMixin, object):
"""
A set of columns.
@@ -216,6 +225,8 @@
@ivar schema: a reference to the L{Schema} to which this table belongs.
"""
+ compareAttributes = 'schema name'.split()
+
def __init__(self, schema, name):
_checkstr(name)
self.descriptiveComment = ''
@@ -318,11 +329,13 @@
-class Sequence(object):
+class Sequence(FancyEqMixin, object):
"""
A sequence object.
"""
+ compareAttributes = 'name'.split()
+
def __init__(self, name):
_checkstr(name)
self.name = name
Modified: CalendarServer/trunk/twext/enterprise/dal/parseschema.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/parseschema.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twext/enterprise/dal/parseschema.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -215,6 +215,20 @@
return self.parseConstraint(maybeIdent)
+ def namesInParens(self, parens):
+ parens = iterSignificant(parens)
+ expect(parens, ttype=Punctuation, value="(")
+ idorids = parens.next()
+ if isinstance(idorids, Identifier):
+ idnames = [idorids.get_name()]
+ elif isinstance(idorids, IdentifierList):
+ idnames = [x.get_name() for x in idorids.get_identifiers()]
+ else:
+ raise ViolatedExpectation("identifier or list", repr(idorids))
+ expect(parens, ttype=Punctuation, value=")")
+ return idnames
+
+
def parseConstraint(self, constraintType):
"""
Parse a 'free' constraint, described explicitly in the table as opposed
@@ -223,20 +237,12 @@
# only know about PRIMARY KEY and UNIQUE for now
if constraintType.match(Keyword, 'PRIMARY'):
expect(self, ttype=Keyword, value='KEY')
- expect(self, cls=Parenthesis)
- self.primaryKey = 'MULTI-VALUE-KEY'
+ names = self.namesInParens(expect(self, cls=Parenthesis))
+ self.table.primaryKey = tuple(self.table.columnNamed(n)
+ for n in names)
elif constraintType.match(Keyword, 'UNIQUE'):
- parens = iterSignificant(expect(self, cls=Parenthesis))
- expect(parens, ttype=Punctuation, value="(")
- idorids = parens.next()
- if isinstance(idorids, Identifier):
- idnames = [idorids.get_name()]
- elif isinstance(idorids, IdentifierList):
- idnames = [x.get_name() for x in idorids.get_identifiers()]
- else:
- raise ViolatedExpectation("identifier or list", repr(idorids))
- expect(parens, ttype=Punctuation, value=")")
- self.table.tableConstraint(Constraint.UNIQUE, idnames)
+ names = self.namesInParens(expect(self, cls=Parenthesis))
+ self.table.tableConstraint(Constraint.UNIQUE, names)
else:
raise ViolatedExpectation('PRIMARY or UNIQUE', constraintType)
return self.checkEnd(self.next())
@@ -347,6 +353,7 @@
elif val.match(Keyword, 'ON'):
expect(self, ttype=Keyword.DML, value='DELETE')
expect(self, ttype=Keyword, value='CASCADE')
+ theColumn.cascade = True
else:
expected = False
if not expected:
Modified: CalendarServer/trunk/twext/enterprise/dal/syntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/syntax.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twext/enterprise/dal/syntax.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -19,9 +19,75 @@
Syntax wrappers and generators for SQL.
"""
+import itertools
+
+from zope.interface import implements
+
+from twisted.internet.defer import succeed
+
+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
+ cx_Oracle
+except ImportError:
+ cx_Oracle = None
+class ConnectionMetadata(object):
+ """
+ Representation of the metadata about the database connection required to
+ generate some SQL, for a single statement. Contains information necessary
+ to generate placeholder strings and determine the database dialect.
+ """
+
+ def __init__(self, dialect):
+ self.dialect = dialect
+
+
+ def placeholder(self):
+ raise NotImplementedError("See subclasses.")
+
+
+
+class FixedPlaceholder(ConnectionMetadata):
+ """
+ Metadata about a connection which uses a fixed string as its placeholder.
+ """
+
+ def __init__(self, dialect, placeholder):
+ super(FixedPlaceholder, self).__init__(dialect)
+ self._placeholder = placeholder
+
+
+ def placeholder(self):
+ return self._placeholder
+
+
+
+class NumericPlaceholder(ConnectionMetadata):
+
+ def __init__(self, dialect):
+ super(NumericPlaceholder, self).__init__(dialect)
+ self._next = itertools.count(1).next
+
+
+ def placeholder(self):
+ return ':' + str(self._next())
+
+
+
+def defaultMetadata():
+ """
+ Generate a default L{ConnectionMetadata}
+ """
+ return FixedPlaceholder(POSTGRES_DIALECT, '?')
+
+
+
class TableMismatch(Exception):
"""
A table in a statement did not match with a column.
@@ -43,18 +109,37 @@
"""
_paramstyles = {
- 'pyformat': ('%s', lambda s: s.replace("%", "%%"))
+ 'pyformat': lambda dialect: FixedPlaceholder(dialect, "%s"),
+ 'numeric': NumericPlaceholder
}
+
+ def toSQL(self, metadata=None):
+ if metadata is None:
+ metadata = defaultMetadata()
+ return self._toSQL(metadata)
+
+
+ def _extraVars(self, txn, metadata):
+ return {}
+
+
+ def _extraResult(self, result, outvars, metadata):
+ return result
+
+
def on(self, txn, raiseOnZeroRowCount=None, **kw):
"""
Execute this statement on a given L{IAsyncTransaction} and return the
resulting L{Deferred}.
"""
- placeholder, quote = self._paramstyles[txn.paramstyle]
- fragment = self.toSQL(placeholder, quote).bind(**kw)
- return txn.execSQL(fragment.text, fragment.parameters,
- raiseOnZeroRowCount)
+ metadata = self._paramstyles[txn.paramstyle](txn.dialect)
+ outvars = self._extraVars(txn, metadata)
+ kw.update(outvars)
+ fragment = self.toSQL(metadata).bind(**kw)
+ result = txn.execSQL(fragment.text, fragment.parameters,
+ raiseOnZeroRowCount)
+ return self._extraResult(result, outvars, metadata)
@@ -89,6 +174,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:
@@ -123,8 +210,8 @@
class FunctionInvocation(ExpressionSyntax):
- def __init__(self, name, *args):
- self.name = name
+ def __init__(self, function, *args):
+ self.function = function
self.args = args
@@ -139,10 +226,10 @@
return list(ac())
- def subSQL(self, placeholder, quote, allTables):
- result = SQLFragment(self.name)
+ def subSQL(self, metadata, allTables):
+ result = SQLFragment(self.function.nameFor(metadata))
result.append(_inParens(
- _commaJoined(_convert(arg).subSQL(placeholder, quote, allTables)
+ _commaJoined(_convert(arg).subSQL(metadata, allTables)
for arg in self.args)))
return result
@@ -157,8 +244,8 @@
return []
- def subSQL(self, placeholder, quote, allTables):
- return SQLFragment(placeholder, [self.value])
+ def subSQL(self, metadata, allTables):
+ return SQLFragment(metadata.placeholder(), [self.value])
@@ -171,7 +258,7 @@
self.name = name
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
return SQLFragment(self.name)
@@ -181,19 +268,27 @@
An L{Function} is a representation of an SQL Function function.
"""
- def __init__(self, name):
+ def __init__(self, name, oracleName=None):
self.name = name
+ self.oracleName = oracleName
+ def nameFor(self, metadata):
+ if metadata.dialect == ORACLE_DIALECT and self.oracleName is not None:
+ return self.oracleName
+ return self.name
+
+
def __call__(self, *args):
"""
Produce an L{FunctionInvocation}
"""
- return FunctionInvocation(self.name, *args)
+ return FunctionInvocation(self, *args)
+
Max = Function("max")
-Len = Function("character_length")
+Len = Function("character_length", "length")
@@ -234,11 +329,15 @@
modelType = Sequence
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
"""
Convert to an SQL fragment.
"""
- return SQLFragment("nextval('%s')" % (self.model.name,))
+ if metadata.dialect == ORACLE_DIALECT:
+ fmt = "%s.nextval"
+ else:
+ fmt = "nextval('%s')"
+ return SQLFragment(fmt % (self.model.name,))
@@ -255,7 +354,7 @@
return Join(self, type, otherTableSyntax, on)
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
"""
For use in a 'from' clause.
"""
@@ -317,18 +416,18 @@
self.on = on
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
stmt = SQLFragment()
- stmt.append(self.leftSide.subSQL(placeholder, quote, allTables))
+ stmt.append(self.leftSide.subSQL(metadata, allTables))
stmt.text += ' '
if self.type:
stmt.text += self.type
stmt.text += ' '
stmt.text += 'join '
- stmt.append(self.rightSide.subSQL(placeholder, quote, allTables))
+ stmt.append(self.rightSide.subSQL(metadata, allTables))
if self.type != 'cross':
stmt.text += ' on '
- stmt.append(self.on.subSQL(placeholder, quote, allTables))
+ stmt.append(self.on.subSQL(metadata, allTables))
return stmt
@@ -342,6 +441,15 @@
return Join(self, type, otherTable, on)
+_KEYWORDS = ["access",
+ # SQL keyword, but we have a column with this name
+ "path",
+ # Not actually a standard keyword, but a function in oracle, and we
+ # have a column with this name.
+ "size",
+ # not actually sure what this is; only experimentally determined
+ # that not quoting it causes an issue.
+ ]
class ColumnSyntax(ExpressionSyntax):
@@ -356,16 +464,19 @@
return [self]
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
# XXX This, and 'model', could in principle conflict with column names.
# Maybe do something about that.
+ name = self.model.name
+ if metadata.dialect == ORACLE_DIALECT and name.lower() in _KEYWORDS:
+ name = '"%s"' % (name,)
+
for tableSyntax in allTables:
if self.model.table is not tableSyntax.model:
if self.model.name in (c.name for c in
tableSyntax.model.columns):
- return SQLFragment((self.model.table.name + '.' +
- self.model.name))
- return SQLFragment(self.model.name)
+ return SQLFragment((self.model.table.name + '.' + name))
+ return SQLFragment(name)
@@ -377,8 +488,8 @@
self.b = b
- def _subexpression(self, expr, placeholder, quote, allTables):
- result = expr.subSQL(placeholder, quote, allTables)
+ def _subexpression(self, expr, metadata, allTables):
+ result = expr.subSQL(metadata, allTables)
if self.op not in ('and', 'or') and isinstance(expr, Comparison):
result = _inParens(result)
return result
@@ -406,9 +517,9 @@
super(NullComparison, self).__init__(a, op, None)
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
sqls = SQLFragment()
- sqls.append(self.a.subSQL(placeholder, quote, allTables))
+ sqls.append(self.a.subSQL(metadata, allTables))
sqls.text += " is "
if self.op != "=":
sqls.text += "not "
@@ -427,16 +538,16 @@
return self.a.allColumns() + self.b.allColumns()
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
stmt = SQLFragment()
- result = self._subexpression(self.a, placeholder, quote, allTables)
+ result = self._subexpression(self.a, metadata, allTables)
if isinstance(self.a, CompoundComparison) and self.a.op == 'or' and self.op == 'and':
result = _inParens(result)
stmt.append(result)
stmt.text += ' %s ' % (self.op,)
- result = self._subexpression(self.b, placeholder, quote, allTables)
+ result = self._subexpression(self.b, metadata, allTables)
if isinstance(self.b, CompoundComparison) and self.b.op == 'or' and self.op == 'and':
result = _inParens(result)
stmt.append(result)
@@ -454,8 +565,8 @@
class _AllColumns(object):
- def subSQL(self, placeholder, quote, allTables):
- return SQLFragment(quote('*'))
+ def subSQL(self, metadata, allTables):
+ return SQLFragment('*')
ALL_COLUMNS = _AllColumns()
@@ -467,7 +578,7 @@
self.columns = columns
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
first = True
cstatement = SQLFragment()
for column in self.columns:
@@ -475,7 +586,7 @@
first = False
else:
cstatement.append(SQLFragment(", "))
- cstatement.append(column.subSQL(placeholder, quote, allTables))
+ cstatement.append(column.subSQL(metadata, allTables))
return cstatement
@@ -491,7 +602,23 @@
return True
+class Tuple(object):
+ def __init__(self, columns):
+ self.columns = columns
+
+
+ def subSQL(self, metadata, allTables):
+ return _inParens(_commaJoined(c.subSQL(metadata, allTables)
+ for c in self.columns))
+
+
+ def allColumns(self):
+ return self.columns
+
+
+
+
class Select(_Statement):
"""
'select' statement.
@@ -499,9 +626,10 @@
def __init__(self, columns=None, Where=None, From=None, OrderBy=None,
GroupBy=None, Limit=None, ForUpdate=False, Ascending=None,
- Having=None):
+ Having=None, Distinct=False):
self.From = From
self.Where = Where
+ self.Distinct = Distinct
if not isinstance(OrderBy, (list, tuple, type(None))):
OrderBy = [OrderBy]
self.OrderBy = OrderBy
@@ -522,43 +650,54 @@
self.Ascending = Ascending
- def toSQL(self, placeholder="?", quote=lambda x: x):
+ def __eq__(self, other):
"""
+ Create a comparison.
+ """
+ if isinstance(other, (list, tuple)):
+ other = Tuple(other)
+ return CompoundComparison(other, '=', self)
+
+
+ def _toSQL(self, metadata):
+ """
@return: a 'select' statement with placeholders and arguments
@rtype: L{SQLFragment}
"""
- stmt = SQLFragment(quote("select "))
+ stmt = SQLFragment("select ")
+ if self.Distinct:
+ stmt.text += "distinct "
allTables = self.From.tables()
- stmt.append(self.columns.subSQL(placeholder, quote, allTables))
- stmt.text += quote(" from ")
- stmt.append(self.From.subSQL(placeholder, quote, allTables))
+ stmt.append(self.columns.subSQL(metadata, allTables))
+ stmt.text += " from "
+ stmt.append(self.From.subSQL(metadata, allTables))
if self.Where is not None:
- wherestmt = self.Where.subSQL(placeholder, quote, allTables)
- stmt.text += quote(" where ")
+ wherestmt = self.Where.subSQL(metadata, allTables)
+ stmt.text += " where "
stmt.append(wherestmt)
if self.GroupBy is not None:
- stmt.text += quote(" group by ")
+ stmt.text += " group by "
fst = True
for subthing in self.GroupBy:
if fst:
fst = False
else:
stmt.text += ', '
- stmt.append(subthing.subSQL(placeholder, quote, allTables))
+ stmt.append(subthing.subSQL(metadata, allTables))
if self.Having is not None:
- havingstmt = self.Having.subSQL(placeholder, quote, allTables)
- stmt.text += quote(" having ")
+ havingstmt = self.Having.subSQL(metadata, allTables)
+ stmt.text += " having "
stmt.append(havingstmt)
if self.OrderBy is not None:
- stmt.text += quote(" order by ")
+ stmt.text += " order by "
fst = True
for subthing in self.OrderBy:
if fst:
fst = False
else:
stmt.text += ', '
- stmt.append(subthing.subSQL(placeholder, quote, allTables))
+ stmt.append(subthing.subSQL(metadata, allTables))
if self.Ascending is not None:
if self.Ascending:
kw = " asc"
@@ -566,17 +705,16 @@
kw = " desc"
stmt.append(SQLFragment(kw))
if self.ForUpdate:
- stmt.text += quote(" for update")
+ stmt.text += " for update"
if self.Limit is not None:
- stmt.text += quote(" limit ")
- stmt.append(Constant(self.Limit).subSQL(placeholder, quote,
- allTables))
+ stmt.text += " limit "
+ stmt.append(Constant(self.Limit).subSQL(metadata, allTables))
return stmt
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
result = SQLFragment("(")
- result.append(self.toSQL(placeholder, quote))
+ result.append(self.toSQL(metadata))
result.append(SQLFragment(")"))
return result
@@ -629,21 +767,109 @@
self.subfragments = subfragments
- def subSQL(self, placeholder, quote, allTables):
- return _commaJoined(f.subSQL(placeholder, quote, allTables)
+ def subSQL(self, metadata, allTables):
+ return _commaJoined(f.subSQL(metadata, allTables)
for f in self.subfragments)
-class Insert(_Statement):
+class _DMLStatement(_Statement):
"""
+ Common functionality of Insert/Update/Delete statements.
+ """
+
+ def _returningClause(self, metadata, stmt, allTables):
+ """
+ Add a dialect-appropriate 'returning' clause to the end of the given SQL
+ statement.
+
+ @param metadata: describes the database we are generating the statement for.
+ @type metadata: L{ConnectionMetadata}
+
+ @param stmt: the SQL fragment generated without the 'returning' clause
+ @type stmt: L{SQLFragment}
+
+ @param allTables: all tables involved in the query; see any C{subSQL}
+ method.
+
+ @return: the C{stmt} parameter.
+ """
+ retclause = self.Return
+ if isinstance(retclause, (tuple, list)):
+ retclause = _CommaList(retclause)
+ if retclause is not None:
+ stmt.text += ' returning '
+ stmt.append(retclause.subSQL(metadata, allTables))
+ if metadata.dialect == ORACLE_DIALECT:
+ stmt.text += ' into '
+ params = []
+ retvals = self._returnAsList()
+ for n, v in enumerate(retvals):
+ params.append(
+ Constant(Parameter("oracle_out_" + str(n)))
+ .subSQL(metadata, allTables)
+ )
+ stmt.append(_commaJoined(params))
+ 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, metadata):
+ if metadata.dialect == ORACLE_DIALECT and self.Return is not None:
+ def processIt(shouldBeNone):
+ result = [[v.value for k, v in outvars]]
+ return result
+ return result.addCallback(processIt)
+ else:
+ return result
+
+
+
+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])
+ return self.var
+
+
+ def postQuery(self, cursor):
+ self.value = mapOracleOutputType(self.var.getvalue())
+
+
+
+class Insert(_DMLStatement):
+ """
'insert' statement.
"""
def __init__(self, columnMap, Return=None):
self.columnMap = columnMap
- if isinstance(Return, (tuple, list)):
- Return = _CommaList(Return)
self.Return = Return
columns = _modelsFromMap(columnMap)
table = _fromSameTable(columns)
@@ -656,31 +882,38 @@
(', '.join([c.name for c in unspecified])))
- def toSQL(self, placeholder="?", quote=lambda x: x):
+ def _toSQL(self, metadata):
"""
@return: a 'insert' statement with placeholders and arguments
@rtype: L{SQLFragment}
"""
- sortedColumns = sorted(self.columnMap.items(),
+ columnsAndValues = self.columnMap.items()
+ tableModel = columnsAndValues[0][0].model.table
+ specifiedColumnModels = [x.model for x in self.columnMap.keys()]
+ if metadata.dialect == ORACLE_DIALECT:
+ # See test_nextSequenceDefaultImplicitExplicitOracle.
+ for column in tableModel.columns:
+ if isinstance(column.default, Sequence):
+ columnSyntax = ColumnSyntax(column)
+ if column not in specifiedColumnModels:
+ columnsAndValues.append(
+ (columnSyntax, SequenceSyntax(column.default))
+ )
+ sortedColumns = sorted(columnsAndValues,
key=lambda (c, v): c.model.name)
allTables = []
stmt = SQLFragment('insert into ')
- stmt.append(
- TableSyntax(sortedColumns[0][0].model.table)
- .subSQL(placeholder, quote, allTables))
+ stmt.append(TableSyntax(tableModel).subSQL(metadata, allTables))
stmt.append(SQLFragment(" "))
stmt.append(_inParens(_commaJoined(
- [c.subSQL(placeholder, quote, allTables) for (c, v) in
+ [c.subSQL(metadata, allTables) for (c, v) in
sortedColumns])))
stmt.append(SQLFragment(" values "))
stmt.append(_inParens(_commaJoined(
- [_convert(v).subSQL(placeholder, quote, allTables)
+ [_convert(v).subSQL(metadata, allTables)
for (c, v) in sortedColumns])))
- if self.Return is not None:
- stmt.text += ' returning '
- stmt.append(self.Return.subSQL(placeholder, quote, allTables))
- return stmt
+ return self._returningClause(metadata, stmt, allTables)
@@ -696,7 +929,7 @@
-class Update(_Statement):
+class Update(_DMLStatement):
"""
'update' statement
"""
@@ -706,12 +939,10 @@
_fromSameTable(_modelsFromMap(columnMap))
self.columnMap = columnMap
self.Where = Where
- if isinstance(Return, (tuple, list)):
- Return = _CommaList(Return)
self.Return = Return
- def toSQL(self, placeholder="?", quote=lambda x: x):
+ def _toSQL(self, metadata):
"""
@return: a 'insert' statement with placeholders and arguments
@@ -723,27 +954,24 @@
result = SQLFragment('update ')
result.append(
TableSyntax(sortedColumns[0][0].model.table).subSQL(
- placeholder, quote, allTables)
+ metadata, allTables)
)
result.text += ' set '
result.append(
_commaJoined(
- [c.subSQL(placeholder, quote, allTables).append(
- SQLFragment(" = ").subSQL(placeholder, quote, allTables)
- ).append(_convert(v).subSQL(placeholder, quote, allTables))
+ [c.subSQL(metadata, allTables).append(
+ SQLFragment(" = ").subSQL(metadata, allTables)
+ ).append(_convert(v).subSQL(metadata, allTables))
for (c, v) in sortedColumns]
)
)
result.append(SQLFragment( ' where '))
- result.append(self.Where.subSQL(placeholder, quote, allTables))
- if self.Return is not None:
- result.append(SQLFragment(' returning '))
- result.append(self.Return.subSQL(placeholder, quote, allTables))
- return result
+ result.append(self.Where.subSQL(metadata, allTables))
+ return self._returningClause(metadata, result, allTables)
-class Delete(_Statement):
+class Delete(_DMLStatement):
"""
'delete' statement.
"""
@@ -754,17 +982,14 @@
self.Return = Return
- def toSQL(self, placeholder="?", quote=lambda x: x):
+ def _toSQL(self, metadata):
result = SQLFragment()
allTables = self.From.tables()
- result.text += quote('delete from ')
- result.append(self.From.subSQL(placeholder, quote, allTables))
- result.text += quote(' where ')
- result.append(self.Where.subSQL(placeholder, quote, allTables))
- if self.Return is not None:
- result.append(SQLFragment(' returning '))
- result.append(self.Return.subSQL(placeholder, quote, allTables))
- return result
+ result.text += 'delete from '
+ result.append(self.From.subSQL(metadata, allTables))
+ result.text += ' where '
+ result.append(self.Where.subSQL(metadata, allTables))
+ return self._returningClause(metadata, result, allTables)
@@ -783,11 +1008,13 @@
return cls(table, 'exclusive')
- def toSQL(self, placeholder="?", quote=lambda x: x):
+ def _toSQL(self, metadata):
return SQLFragment('lock table ').append(
- self.table.subSQL(placeholder, quote, [self.table])).append(
+ self.table.subSQL(metadata, [self.table])).append(
SQLFragment(' in %s mode' % (self.mode,)))
+
+
class Savepoint(_Statement):
"""
An SQL 'savepoint' statement.
@@ -797,7 +1024,7 @@
self.name = name
- def toSQL(self, placeholder="?", quote=lambda x: x):
+ def _toSQL(self, metadata):
return SQLFragment('savepoint %s' % (self.name,))
@@ -810,7 +1037,7 @@
self.name = name
- def toSQL(self, placeholder="?", quote=lambda x: x):
+ def _toSQL(self, metadata):
return SQLFragment('rollback to savepoint %s' % (self.name,))
@@ -823,24 +1050,42 @@
self.name = name
- def toSQL(self, placeholder="?", quote=lambda x: x):
+ def _toSQL(self, metadata):
return SQLFragment('release savepoint %s' % (self.name,))
+
class SavepointAction(object):
-
+
def __init__(self, name):
self._name = name
-
+
+
def acquire(self, txn):
return Savepoint(self._name).on(txn)
+
def rollback(self, txn):
return RollbackToSavepoint(self._name).on(txn)
+
def release(self, txn):
- return ReleaseSavepoint(self._name).on(txn)
+ if txn.dialect == ORACLE_DIALECT:
+ # There is no 'release savepoint' statement in oracle, but then, we
+ # don't need it because there's no resource to manage. Just don't
+ # do anything.
+ return NoOp()
+ else:
+ return ReleaseSavepoint(self._name).on(txn)
+
+
+class NoOp(object):
+ def on(self, *a, **kw):
+ return succeed(None)
+
+
+
class SQLFragment(object):
"""
Combination of SQL text and arguments; a statement which may be executed
@@ -886,7 +1131,7 @@
return self.__class__.__name__ + repr((self.text, self.parameters))
- def subSQL(self, placeholder, quote, allTables):
+ def subSQL(self, metadata, allTables):
return self
@@ -897,14 +1142,27 @@
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,)
# Common helpers:
-# current timestamp in UTC format.
-utcNowSQL = Function('timezone')('UTC', NamedValue('CURRENT_TIMESTAMP'))
+# current timestamp in UTC format. Hack to support standard syntax for this,
+# rather than the compatibility procedure found in various databases.
+utcNowSQL = NamedValue("CURRENT_TIMESTAMP at time zone 'UTC'")
# You can't insert a column with no rows. In SQL that just isn't valid syntax,
# and in this DAL you need at least one key or we can't tell what table you're
Modified: CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twext/enterprise/dal/test/test_parseschema.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -215,3 +215,32 @@
[set([b, c]), set([c])])
+ def test_multiPrimaryKey(self):
+ """
+ A table with a multi-column PRIMARY KEY clause will be parsed as a tuple
+ primaryKey attribute on the Table object.
+ """
+ s = Schema()
+ addSQLToSchema(
+ s, "create table a (b integer, c integer, primary key(b, c))")
+ a = s.tableNamed("a")
+ self.assertEquals(a.primaryKey,
+ (a.columnNamed("b"), a.columnNamed("c")))
+
+
+ def test_cascade(self):
+ """
+ A column with an 'on delete cascade' constraint will have its C{cascade}
+ attribute set to True.
+ """
+ s = Schema()
+ addSQLToSchema(
+ s,
+ """
+ create table a (b integer primary key);
+ create table c (d integer references a on delete cascade);
+ """)
+ self.assertEquals(s.tableNamed("a").columnNamed("b").cascade, False)
+ self.assertEquals(s.tableNamed("c").columnNamed("d").cascade, True)
+
+
Modified: CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -20,14 +20,35 @@
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
, Savepoint, RollbackToSavepoint, ReleaseSavepoint, SavepointAction)
-from twext.enterprise.dal.syntax import FunctionInvocation
+from twext.enterprise.dal.syntax import Function
+
+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
+
+
+class _FakeTransaction(object):
+ """
+ An L{IAsyncTransaction} that provides the relevant metadata for SQL
+ generation.
+ """
+
+ def __init__(self, paramstyle):
+ self.paramstyle = 'qmark'
+
+
+
class GenerationTests(TestCase):
"""
Tests for syntactic helpers to generate SQL queries.
@@ -37,11 +58,12 @@
s = Schema(self.id())
addSQLToSchema(schema=s, schemaData="""
create sequence A_SEQ;
- create table FOO (BAR integer, BAZ integer);
- create table BOZ (QUX 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);
create table TEXTUAL (MYTEXT varchar(255));
+ create table LEVELS (ACCESS integer, USERNAME varchar(255));
""")
self.schema = SchemaSyntax(s)
@@ -65,16 +87,16 @@
SQLFragment("select * from FOO where BAR = ?", [1]))
- def test_quotingAndPlaceholder(self):
+ def test_alternateMetadata(self):
"""
L{Select} generates a 'select' statement with the specified placeholder
- syntax and quoting function.
+ syntax when explicitly given L{ConnectionMetadata} which specifies a
+ placeholder.
"""
self.assertEquals(Select(From=self.schema.FOO,
Where=self.schema.FOO.BAR == 1).toSQL(
- placeholder="*",
- quote=lambda partial: partial.replace("*", "**")),
- SQLFragment("select ** from FOO where BAR = *", [1]))
+ FixedPlaceholder(POSTGRES_DIALECT, "$$")),
+ SQLFragment("select * from FOO where BAR = $$", [1]))
def test_columnComparison(self):
@@ -467,6 +489,60 @@
)
+ 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_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
@@ -480,6 +556,31 @@
)
+ def test_quotingOnKeywordConflict(self):
+ """
+ 'access' is a keyword, so although our schema parser will leniently
+ accept it, it must be quoted in any outgoing SQL. (This is only done in
+ the Oracle dialect, because it isn't necessary in postgres, and
+ idiosyncratic case-folding rules make it challenging to do it in both.)
+ """
+ self.assertEquals(
+ Insert({self.schema.LEVELS.ACCESS: 1,
+ self.schema.LEVELS.USERNAME:
+ "hi"}).toSQL(FixedPlaceholder(ORACLE_DIALECT, "?")),
+ SQLFragment(
+ 'insert into LEVELS ("ACCESS", USERNAME) values (?, ?)',
+ [1, "hi"])
+ )
+ self.assertEquals(
+ Insert({self.schema.LEVELS.ACCESS: 1,
+ self.schema.LEVELS.USERNAME:
+ "hi"}).toSQL(FixedPlaceholder(POSTGRES_DIALECT, "?")),
+ SQLFragment(
+ 'insert into LEVELS (ACCESS, USERNAME) values (?, ?)',
+ [1, "hi"])
+ )
+
+
def test_updateReturning(self):
"""
L{update}'s C{Return} argument will update an SQL 'returning' clause.
@@ -513,10 +614,11 @@
L{Update} values may be L{FunctionInvocation}s, to update to computed
values in the database.
"""
+ sqlfunc = Function("hello")
self.assertEquals(
Update(
{self.schema.FOO.BAR: 23,
- self.schema.FOO.BAZ: FunctionInvocation("hello")},
+ self.schema.FOO.BAZ: sqlfunc()},
Where=self.schema.FOO.BAZ == 9
).toSQL(),
SQLFragment("update FOO set BAR = ?, BAZ = hello() "
@@ -529,10 +631,11 @@
L{Update} values may be L{FunctionInvocation}s, to update to computed
values in the database.
"""
+ sqlfunc = Function("hello")
self.assertEquals(
Insert(
{self.schema.FOO.BAR: 23,
- self.schema.FOO.BAZ: FunctionInvocation("hello")},
+ self.schema.FOO.BAZ: sqlfunc()},
).toSQL(),
SQLFragment("insert into FOO (BAR, BAZ) "
"values (?, hello())", [23])
@@ -636,6 +739,19 @@
)
+ def test_distinct(self):
+ """
+ A L{Select} object with a 'Disinct' keyword parameter with a value of
+ C{True} will generate a SQL statement with a 'distinct' keyword
+ preceding its list of columns.
+ """
+ self.assertEquals(
+ Select([self.schema.FOO.BAR], From=self.schema.FOO,
+ Distinct=True).toSQL(),
+ SQLFragment("select distinct BAR from FOO")
+ )
+
+
def test_nextSequenceValue(self):
"""
When a sequence is used as a value in an expression, it renders as the
@@ -646,10 +762,77 @@
self.schema.A_SEQ}).toSQL(),
SQLFragment("insert into BOZ (QUX) values (nextval('A_SEQ'))", []))
+
+ def test_nextSequenceValueOracle(self):
+ """
+ When a sequence is used as a value in an expression in the Oracle
+ dialect, it renders as the 'nextval' attribute of the appropriate
+ sequence.
+ """
+ self.assertEquals(
+ Insert({self.schema.BOZ.QUX:
+ self.schema.A_SEQ}).toSQL(
+ FixedPlaceholder(ORACLE_DIALECT, "?")),
+ SQLFragment("insert into BOZ (QUX) values (A_SEQ.nextval)", []))
+
+
+ def test_nextSequenceDefaultImplicitExplicitOracle(self):
+ """
+ In Oracle's dialect, sequence defaults can't be implemented without
+ using triggers, so instead we just explicitly always include the
+ sequence default value.
+ """
+ addSQLToSchema(
+ schema=self.schema.model, schemaData=
+ "create table DFLTR (a varchar(255), "
+ "b integer default nextval('A_SEQ'));"
+ )
+ self.assertEquals(
+ Insert({self.schema.DFLTR.a: 'hello'}).toSQL(
+ FixedPlaceholder(ORACLE_DIALECT, "?")
+ ),
+ SQLFragment("insert into DFLTR (a, b) values "
+ "(?, A_SEQ.nextval)", ['hello']),
+ )
+ # Should be the same if it's explicitly specified.
+ self.assertEquals(
+ Insert({self.schema.DFLTR.a: 'hello',
+ self.schema.DFLTR.b: self.schema.A_SEQ}).toSQL(
+ FixedPlaceholder(ORACLE_DIALECT, "?")
+ ),
+ SQLFragment("insert into DFLTR (a, b) values "
+ "(?, A_SEQ.nextval)", ['hello']),
+ )
+
+
+ def test_numericParams(self):
+ """
+ An L{IAsyncTransaction} with the 'numeric' paramstyle attribute will
+ cause statements to be generated with parameters in the style of :1 :2
+ :3, as per the DB-API.
+ """
+ stmts = []
+ class FakeOracleTxn(object):
+ def execSQL(self, text, params, exc):
+ stmts.append((text, params))
+ dialect = ORACLE_DIALECT
+ paramstyle = 'numeric'
+ Select([self.schema.FOO.BAR],
+ From=self.schema.FOO,
+ Where=(self.schema.FOO.BAR == 7).And(
+ self.schema.FOO.BAZ == 9)
+ ).on(FakeOracleTxn())
+ self.assertEquals(
+ stmts, [("select BAR from FOO where BAR = :1 and BAZ = :2",
+ [7, 9])]
+ )
+
+
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(
@@ -658,7 +841,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(
@@ -667,7 +851,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(
@@ -676,4 +861,67 @@
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_updateWithNULL(self):
+ """
+ As per the DB-API specification, "SQL NULL values are represented by the
+ Python None singleton on input and output." When a C{None} is provided
+ as a value to an L{Update}, it will be relayed to the database as a
+ parameter.
+ """
+ self.assertEquals(
+ Update({self.schema.BOZ.QUX: None},
+ Where=self.schema.BOZ.QUX == 7).toSQL(),
+ SQLFragment("update BOZ set QUX = ? where QUX = ?", [None, 7])
+ )
+
+
+ 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]
+ )
+ )
+
Modified: CalendarServer/trunk/twext/enterprise/ienterprise.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/ienterprise.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twext/enterprise/ienterprise.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -40,6 +40,11 @@
+POSTGRES_DIALECT = 'postgres-dialect'
+ORACLE_DIALECT = 'oracle-dialect'
+
+
+
class IAsyncTransaction(Interface):
"""
Asynchronous execution of SQL.
@@ -54,6 +59,13 @@
""")
+ dialect = Attribute(
+ """
+ A copy of the 'dialect' attribute from the connection pool. One of the
+ C{*_DIALECT} constants in this module, such as C{POSTGRES_DIALECT}.
+ """)
+
+
def execSQL(sql, args=(), raiseOnZeroRowCount=None):
"""
Execute some SQL.
@@ -91,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/trunk/twext/enterprise/test/test_adbapi2.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/test/test_adbapi2.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twext/enterprise/test/test_adbapi2.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -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
@@ -657,4 +679,42 @@
self.assertEquals(len(self.factory.connections), 2)
+ def test_propagateParamstyle(self):
+ """
+ Each different type of L{IAsyncTransaction} relays the C{paramstyle}
+ attribute from the L{ConnectionPool}.
+ """
+ TEST_PARAMSTYLE = "justtesting"
+ self.pool.paramstyle = TEST_PARAMSTYLE
+ normaltxn = self.pool.connection()
+ self.assertEquals(normaltxn.paramstyle, TEST_PARAMSTYLE)
+ self.pauseHolders()
+ extra = []
+ extra.append(self.pool.connection())
+ waitingtxn = self.pool.connection()
+ self.assertEquals(waitingtxn.paramstyle, TEST_PARAMSTYLE)
+ self.flushHolders()
+ self.pool.stopService()
+ notxn = self.pool.connection()
+ self.assertEquals(notxn.paramstyle, TEST_PARAMSTYLE)
+
+ def test_propagateDialect(self):
+ """
+ Each different type of L{IAsyncTransaction} relays the C{dialect}
+ attribute from the L{ConnectionPool}.
+ """
+ TEST_DIALECT = "otherdialect"
+ self.pool.dialect = TEST_DIALECT
+ normaltxn = self.pool.connection()
+ self.assertEquals(normaltxn.dialect, TEST_DIALECT)
+ self.pauseHolders()
+ extra = []
+ extra.append(self.pool.connection())
+ waitingtxn = self.pool.connection()
+ self.assertEquals(waitingtxn.dialect, TEST_DIALECT)
+ self.flushHolders()
+ self.pool.stopService()
+ notxn = self.pool.connection()
+ self.assertEquals(notxn.dialect, TEST_DIALECT)
+
Copied: CalendarServer/trunk/twext/enterprise/util.py (from rev 7155, CalendarServer/branches/users/glyph/oracle/twext/enterprise/util.py)
===================================================================
--- CalendarServer/trunk/twext/enterprise/util.py (rev 0)
+++ CalendarServer/trunk/twext/enterprise/util.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -0,0 +1,61 @@
+
+##
+# Copyright (c) 2010 Apple Inc. All rights reserved.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+##
+
+"""
+Utilities for dealing with different databases.
+"""
+
+from datetime import datetime
+from twistedcaldav.dateops import SQL_TIMESTAMP_FORMAT
+
+def mapOracleOutputType(column):
+ """
+ Map a single output value from cx_Oracle based on some rules and
+ expectations that we have based on the pgdb bindings.
+
+ @param column: a single value from a column.
+
+ @return: a converted value based on the type of the input; oracle CLOBs and
+ datetime timestamps will be converted to strings, all other types will
+ be left alone.
+ """
+ if hasattr(column, 'read'):
+ # Try to detect large objects and format convert them to
+ # strings on the fly. We need to do this as we read each
+ # row, due to the issue described here -
+ # http://cx-oracle.sourceforge.net/html/lob.html - in
+ # particular, the part where it says "In particular, do not
+ # use the fetchall() method".
+ return column.read()
+ elif isinstance(column, datetime):
+ # cx_Oracle properly maps the type of timestamps to datetime
+ # objects. However, our code is mostly written against
+ # PyGreSQL, which just emits strings as results and expects
+ # to have to convert them itself.. Since it's easier to
+ # just detect the datetimes and stringify them, for now
+ # we'll do that.
+ return column.strftime(SQL_TIMESTAMP_FORMAT)
+ elif isinstance(column, float):
+ if int(column) == column:
+ return int(column)
+ else:
+ return column
+ else:
+ return column
+
+
+
Modified: CalendarServer/trunk/twistedcaldav/dateops.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/dateops.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twistedcaldav/dateops.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -239,18 +239,19 @@
else:
return (start, end)
+SQL_TIMESTAMP_FORMAT = "%Y-%m-%d %H:%M:%S.%f"
+
def parseSQLTimestamp(ts):
-
# Handle case where fraction seconds may not be present
if len(ts) < 20:
ts += ".0"
- return datetime.datetime.strptime(ts, "%Y-%m-%d %H:%M:%S.%f")
+ return datetime.datetime.strptime(ts, SQL_TIMESTAMP_FORMAT)
def datetimeMktime(dt):
assert isinstance(dt, datetime.date)
-
+
if dt.tzinfo is None:
dt.replace(tzinfo=utc)
return calendar.timegm(dt.utctimetuple())
-
\ No newline at end of file
+
Modified: CalendarServer/trunk/twistedcaldav/query/calendarquery.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/query/calendarquery.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twistedcaldav/query/calendarquery.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -197,10 +197,10 @@
endfloat = floatoffset(end, tzinfo) if end else None
return (
- str(start) if start else None,
- str(end) if end else None,
- str(startfloat) if startfloat else None,
- str(endfloat) if endfloat else None,
+ (start) if start else None,
+ (end) if end else None,
+ (startfloat) if startfloat else None,
+ (endfloat) if endfloat else None,
)
def sqlcalendarquery(filter, calendarid=None, userid=None, generator=sqlgenerator.sqlgenerator):
Modified: CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -266,13 +266,13 @@
e3 = expression.notcontainsExpression("SUMMARY", "help", True)
e5 = expression.andExpression([e1, e2, e3])
print e5
- sql = sqlgenerator(e5)
- print sql.generate()
+ #sql = sqlgenerator(e5, 'dummy-cal', 'dummy-user')
+ #print sql.generate()
e6 = expression.inExpression("TYPE", ("VEVENT", "VTODO",), False)
print e6
- sql = sqlgenerator(e6)
+ sql = sqlgenerator(e6, 'dummy-cal', 'dummy-user')
print sql.generate()
e7 = expression.notinExpression("TYPE", ("VEVENT", "VTODO",), False)
print e7
- sql = sqlgenerator(e7)
+ sql = sqlgenerator(e7, 'dummy-cal', 'dummy-user')
print sql.generate()
Modified: CalendarServer/trunk/txdav/base/datastore/dbapiclient.py
===================================================================
--- CalendarServer/trunk/txdav/base/datastore/dbapiclient.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/txdav/base/datastore/dbapiclient.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -17,7 +17,14 @@
"""
General utility client code for interfacing with DB-API 2.0 modules.
"""
+from twext.enterprise.util import mapOracleOutputType
+try:
+ import cx_Oracle
+except ImportError:
+ cx_Oracle = None
+
+
class DiagnosticCursorWrapper(object):
"""
Diagnostic wrapper around a DB-API 2.0 cursor for debugging connection
@@ -41,11 +48,11 @@
def execute(self, sql, args=()):
self.connectionWrapper.state = 'executing %r' % (sql,)
-# Use log.debug
-# sys.stdout.write(
-# "Really executing SQL %r in thread %r\n" %
-# ((sql % tuple(args)), thread.get_ident())
-# )
+ # Use log.debug
+ # sys.stdout.write(
+ # "Really executing SQL %r in thread %r\n" %
+ # ((sql % tuple(args)), thread.get_ident())
+ # )
self.realCursor.execute(sql, args)
@@ -55,21 +62,68 @@
def fetchall(self):
results = self.realCursor.fetchall()
-# Use log.debug
-# sys.stdout.write(
-# "Really fetching results %r thread %r\n" %
-# (results, thread.get_ident())
-# )
+ # Use log.debug
+ # sys.stdout.write(
+ # "Really fetching results %r thread %r\n" %
+ # (results, thread.get_ident())
+ # )
return results
+class OracleCursorWrapper(DiagnosticCursorWrapper):
+ """
+ Wrapper for cx_Oracle DB-API connections which implements fetchall() to read
+ all CLOB objects into strings.
+ """
+ def fetchall(self):
+ accum = []
+ for row in self.realCursor:
+ newRow = []
+ for column in row:
+ newRow.append(mapOracleOutputType(column))
+ accum.append(newRow)
+ 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:
+ if isinstance(arg, (str, unicode)) and len(arg) > 1024:
+ # This *may* cause a type mismatch, but none of the non-CLOB
+ # strings that we're passing would allow a value this large
+ # anyway. Smaller strings will be automatically converted by
+ # the bindings; larger ones will generate an error. I'm not
+ # 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.var(cx_Oracle.CLOB, len(arg) + 1)
+ v.setvalue(0, arg)
+ else:
+ v = arg
+ realArgs.append(v)
+ return super(OracleCursorWrapper, self).execute(sql, realArgs)
+
+
+
class DiagnosticConnectionWrapper(object):
"""
Diagnostic wrapper around a DB-API 2.0 connection for debugging connection
status.
"""
+ wrapper = DiagnosticCursorWrapper
+
def __init__(self, realConnection, label):
self.realConnection = realConnection
self.label = label
@@ -77,7 +131,7 @@
def cursor(self):
- return DiagnosticCursorWrapper(self.realConnection.cursor(), self)
+ return self.wrapper(self.realConnection.cursor(), self)
def close(self):
@@ -103,6 +157,8 @@
@ivar dbModule: the DB-API module to use.
"""
+ wrapper = DiagnosticConnectionWrapper
+
def __init__(self, dbModule, preflight, *connectArgs, **connectKw):
self.dbModule = dbModule
self.connectArgs = connectArgs
@@ -112,12 +168,53 @@
def connect(self, label="<unlabeled>"):
connection = self.dbModule.connect(*self.connectArgs, **self.connectKw)
- w = DiagnosticConnectionWrapper(connection, label)
+ w = self.wrapper(connection, label)
self.preflight(w)
return w
+class OracleConnectionWrapper(DiagnosticConnectionWrapper):
+
+ wrapper = OracleCursorWrapper
+
+
+
+class OracleConnector(DBAPIConnector):
+ """
+ A connector for cx_Oracle connections, with some special-cased behavior to
+ make it work more like other DB-API bindings.
+
+ Note: this is currently necessary to make our usage of twext.enterprise.dal
+ work with cx_Oracle, and should be factored somewhere higher-level.
+ """
+
+ wrapper = OracleConnectionWrapper
+
+ def __init__(self, dsn):
+ super(OracleConnector, self).__init__(
+ cx_Oracle, oraclePreflight, dsn, threaded=True)
+
+
+
+def oraclePreflight(connection):
+ """
+ Pre-flight function for Oracle connections: set the timestamp format to be
+ something closely resembling our default assumption from Postgres.
+ """
+ c = connection.cursor()
+ c.execute(
+ "alter session set NLS_TIMESTAMP_FORMAT = "
+ "'YYYY-MM-DD HH24:MI:SS.FF'"
+ )
+ c.execute(
+ "alter session set NLS_TIMESTAMP_TZ_FORMAT = "
+ "'YYYY-MM-DD HH:MI:SS.FF+TZH:TZM'"
+ )
+ connection.commit()
+ c.close()
+
+
def postgresPreflight(connection):
"""
Pre-flight function for PostgreSQL connections: enable standard conforming
Property changes on: CalendarServer/trunk/txdav/caldav/datastore/index_file.py
___________________________________________________________________
Modified: svn:mergeinfo
- /CalendarServer/branches/config-separation/txdav/caldav/datastore/index_file.py:4379-4443
/CalendarServer/branches/egg-info-351/txdav/caldav/datastore/index_file.py:4589-4625
/CalendarServer/branches/generic-sqlstore/txdav/caldav/datastore/index_file.py:6167-6191
/CalendarServer/branches/new-store/txdav/caldav/datastore/index_file.py:5594-5934
/CalendarServer/branches/new-store-no-caldavfile/txdav/caldav/datastore/index_file.py:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2/txdav/caldav/datastore/index_file.py:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692/txdav/caldav/datastore/index_file.py:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627/txdav/caldav/datastore/index_file.py:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591/txdav/caldav/datastore/index_file.py:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464/txdav/caldav/datastore/index_file.py:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070/txdav/caldav/datastore/index_file.py:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187/txdav/caldav/datastore/index_file.py:5188-5440
/CalendarServer/branches/users/glyph/conn-limit/txdav/caldav/datastore/index_file.py:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge/txdav/caldav/datastore/index_file.py:4971-5080
/CalendarServer/branches/users/glyph/dalify/txdav/caldav/datastore/index_file.py:6932-7023
/CalendarServer/branches/users/glyph/dont-start-postgres/txdav/caldav/datastore/index_file.py:6592-6614
/CalendarServer/branches/users/glyph/linux-tests/txdav/caldav/datastore/index_file.py:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6/txdav/caldav/datastore/index_file.py:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7/txdav/caldav/datastore/index_file.py:6369-6445
/CalendarServer/branches/users/glyph/sendfdport/txdav/caldav/datastore/index_file.py:5388-5424
/CalendarServer/branches/users/glyph/sharedpool/txdav/caldav/datastore/index_file.py:6490-6550
/CalendarServer/branches/users/glyph/sql-store/txdav/caldav/datastore/index_file.py:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted/txdav/caldav/datastore/index_file.py:5084-5149
/CalendarServer/branches/users/sagen/locations-resources/txdav/caldav/datastore/index_file.py:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2/txdav/caldav/datastore/index_file.py:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events/txdav/caldav/datastore/index_file.py:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038/txdav/caldav/datastore/index_file.py:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066/txdav/caldav/datastore/index_file.py:4068-4075
/CalendarServer/branches/users/sagen/resources-2/txdav/caldav/datastore/index_file.py:5084-5093
/CalendarServer/branches/users/wsanchez/transations/txdav/caldav/datastore/index_file.py:5515-5593
/CalendarServer/trunk/twistedcaldav/index.py:6322-6394
+ /CalendarServer/branches/config-separation/txdav/caldav/datastore/index_file.py:4379-4443
/CalendarServer/branches/egg-info-351/txdav/caldav/datastore/index_file.py:4589-4625
/CalendarServer/branches/generic-sqlstore/txdav/caldav/datastore/index_file.py:6167-6191
/CalendarServer/branches/new-store/txdav/caldav/datastore/index_file.py:5594-5934
/CalendarServer/branches/new-store-no-caldavfile/txdav/caldav/datastore/index_file.py:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2/txdav/caldav/datastore/index_file.py:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692/txdav/caldav/datastore/index_file.py:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627/txdav/caldav/datastore/index_file.py:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591/txdav/caldav/datastore/index_file.py:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464/txdav/caldav/datastore/index_file.py:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070/txdav/caldav/datastore/index_file.py:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187/txdav/caldav/datastore/index_file.py:5188-5440
/CalendarServer/branches/users/glyph/conn-limit/txdav/caldav/datastore/index_file.py:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge/txdav/caldav/datastore/index_file.py:4971-5080
/CalendarServer/branches/users/glyph/dalify/txdav/caldav/datastore/index_file.py:6932-7023
/CalendarServer/branches/users/glyph/dont-start-postgres/txdav/caldav/datastore/index_file.py:6592-6614
/CalendarServer/branches/users/glyph/linux-tests/txdav/caldav/datastore/index_file.py:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6/txdav/caldav/datastore/index_file.py:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7/txdav/caldav/datastore/index_file.py:6369-6445
/CalendarServer/branches/users/glyph/oracle/txdav/caldav/datastore/index_file.py:7106-7155
/CalendarServer/branches/users/glyph/sendfdport/txdav/caldav/datastore/index_file.py:5388-5424
/CalendarServer/branches/users/glyph/sharedpool/txdav/caldav/datastore/index_file.py:6490-6550
/CalendarServer/branches/users/glyph/sql-store/txdav/caldav/datastore/index_file.py:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted/txdav/caldav/datastore/index_file.py:5084-5149
/CalendarServer/branches/users/sagen/locations-resources/txdav/caldav/datastore/index_file.py:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2/txdav/caldav/datastore/index_file.py:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events/txdav/caldav/datastore/index_file.py:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038/txdav/caldav/datastore/index_file.py:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066/txdav/caldav/datastore/index_file.py:4068-4075
/CalendarServer/branches/users/sagen/resources-2/txdav/caldav/datastore/index_file.py:5084-5093
/CalendarServer/branches/users/wsanchez/transations/txdav/caldav/datastore/index_file.py:5515-5593
/CalendarServer/trunk/twistedcaldav/index.py:6322-6394
Modified: CalendarServer/trunk/txdav/caldav/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/sql.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/txdav/caldav/datastore/sql.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -324,27 +324,6 @@
self.hasPrivateComment = metadata.get("hasPrivateComment", False)
- @classmethod
- def _selectAllColumns(cls):
- return """
- select
- %(column_RESOURCE_ID)s,
- %(column_RESOURCE_NAME)s,
- %(column_UID)s,
- %(column_MD5)s,
- character_length(%(column_TEXT)s),
- %(column_ATTACHMENTS_MODE)s,
- %(column_DROPBOX_ID)s,
- %(column_ACCESS)s,
- %(column_SCHEDULE_OBJECT)s,
- %(column_SCHEDULE_TAG)s,
- %(column_SCHEDULE_ETAGS)s,
- %(column_PRIVATE_COMMENTS)s,
- %(column_CREATED)s,
- %(column_MODIFIED)s
- """ % cls._objectTable
-
-
_allColumns = [
_objectSchema.RESOURCE_ID,
_objectSchema.RESOURCE_NAME,
Property changes on: CalendarServer/trunk/txdav/caldav/datastore/test/test_index_file.py
___________________________________________________________________
Modified: svn:mergeinfo
- /CalendarServer/branches/config-separation/txdav/caldav/datastore/test/test_index_file.py:4379-4443
/CalendarServer/branches/egg-info-351/txdav/caldav/datastore/test/test_index_file.py:4589-4625
/CalendarServer/branches/generic-sqlstore/txdav/caldav/datastore/test/test_index_file.py:6167-6191
/CalendarServer/branches/new-store/txdav/caldav/datastore/test/test_index_file.py:5594-5934
/CalendarServer/branches/new-store-no-caldavfile/txdav/caldav/datastore/test/test_index_file.py:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2/txdav/caldav/datastore/test/test_index_file.py:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692/txdav/caldav/datastore/test/test_index_file.py:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627/txdav/caldav/datastore/test/test_index_file.py:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591/txdav/caldav/datastore/test/test_index_file.py:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464/txdav/caldav/datastore/test/test_index_file.py:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070/txdav/caldav/datastore/test/test_index_file.py:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187/txdav/caldav/datastore/test/test_index_file.py:5188-5440
/CalendarServer/branches/users/glyph/conn-limit/txdav/caldav/datastore/test/test_index_file.py:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge/txdav/caldav/datastore/test/test_index_file.py:4971-5080
/CalendarServer/branches/users/glyph/dalify/txdav/caldav/datastore/test/test_index_file.py:6932-7023
/CalendarServer/branches/users/glyph/dont-start-postgres/txdav/caldav/datastore/test/test_index_file.py:6592-6614
/CalendarServer/branches/users/glyph/linux-tests/txdav/caldav/datastore/test/test_index_file.py:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6/txdav/caldav/datastore/test/test_index_file.py:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7/txdav/caldav/datastore/test/test_index_file.py:6369-6445
/CalendarServer/branches/users/glyph/sendfdport/txdav/caldav/datastore/test/test_index_file.py:5388-5424
/CalendarServer/branches/users/glyph/sharedpool/txdav/caldav/datastore/test/test_index_file.py:6490-6550
/CalendarServer/branches/users/glyph/sql-store/txdav/caldav/datastore/test/test_index_file.py:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted/txdav/caldav/datastore/test/test_index_file.py:5084-5149
/CalendarServer/branches/users/sagen/locations-resources/txdav/caldav/datastore/test/test_index_file.py:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2/txdav/caldav/datastore/test/test_index_file.py:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events/txdav/caldav/datastore/test/test_index_file.py:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038/txdav/caldav/datastore/test/test_index_file.py:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066/txdav/caldav/datastore/test/test_index_file.py:4068-4075
/CalendarServer/branches/users/sagen/resources-2/txdav/caldav/datastore/test/test_index_file.py:5084-5093
/CalendarServer/branches/users/wsanchez/transations/txdav/caldav/datastore/test/test_index_file.py:5515-5593
/CalendarServer/trunk/twistedcaldav/test/test_index.py:6322-6394
+ /CalendarServer/branches/config-separation/txdav/caldav/datastore/test/test_index_file.py:4379-4443
/CalendarServer/branches/egg-info-351/txdav/caldav/datastore/test/test_index_file.py:4589-4625
/CalendarServer/branches/generic-sqlstore/txdav/caldav/datastore/test/test_index_file.py:6167-6191
/CalendarServer/branches/new-store/txdav/caldav/datastore/test/test_index_file.py:5594-5934
/CalendarServer/branches/new-store-no-caldavfile/txdav/caldav/datastore/test/test_index_file.py:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2/txdav/caldav/datastore/test/test_index_file.py:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692/txdav/caldav/datastore/test/test_index_file.py:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627/txdav/caldav/datastore/test/test_index_file.py:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591/txdav/caldav/datastore/test/test_index_file.py:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464/txdav/caldav/datastore/test/test_index_file.py:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070/txdav/caldav/datastore/test/test_index_file.py:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187/txdav/caldav/datastore/test/test_index_file.py:5188-5440
/CalendarServer/branches/users/glyph/conn-limit/txdav/caldav/datastore/test/test_index_file.py:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge/txdav/caldav/datastore/test/test_index_file.py:4971-5080
/CalendarServer/branches/users/glyph/dalify/txdav/caldav/datastore/test/test_index_file.py:6932-7023
/CalendarServer/branches/users/glyph/dont-start-postgres/txdav/caldav/datastore/test/test_index_file.py:6592-6614
/CalendarServer/branches/users/glyph/linux-tests/txdav/caldav/datastore/test/test_index_file.py:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6/txdav/caldav/datastore/test/test_index_file.py:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7/txdav/caldav/datastore/test/test_index_file.py:6369-6445
/CalendarServer/branches/users/glyph/oracle/txdav/caldav/datastore/test/test_index_file.py:7106-7155
/CalendarServer/branches/users/glyph/sendfdport/txdav/caldav/datastore/test/test_index_file.py:5388-5424
/CalendarServer/branches/users/glyph/sharedpool/txdav/caldav/datastore/test/test_index_file.py:6490-6550
/CalendarServer/branches/users/glyph/sql-store/txdav/caldav/datastore/test/test_index_file.py:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted/txdav/caldav/datastore/test/test_index_file.py:5084-5149
/CalendarServer/branches/users/sagen/locations-resources/txdav/caldav/datastore/test/test_index_file.py:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2/txdav/caldav/datastore/test/test_index_file.py:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events/txdav/caldav/datastore/test/test_index_file.py:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038/txdav/caldav/datastore/test/test_index_file.py:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066/txdav/caldav/datastore/test/test_index_file.py:4068-4075
/CalendarServer/branches/users/sagen/resources-2/txdav/caldav/datastore/test/test_index_file.py:5084-5093
/CalendarServer/branches/users/wsanchez/transations/txdav/caldav/datastore/test/test_index_file.py:5515-5593
/CalendarServer/trunk/twistedcaldav/test/test_index.py:6322-6394
Property changes on: CalendarServer/trunk/txdav/carddav/datastore/index_file.py
___________________________________________________________________
Modified: svn:mergeinfo
- /CalendarServer/branches/config-separation/txdav/carddav/datastore/index_file.py:4379-4443
/CalendarServer/branches/egg-info-351/txdav/carddav/datastore/index_file.py:4589-4625
/CalendarServer/branches/generic-sqlstore/txdav/carddav/datastore/index_file.py:6167-6191
/CalendarServer/branches/new-store/txdav/carddav/datastore/index_file.py:5594-5934
/CalendarServer/branches/new-store-no-caldavfile/txdav/carddav/datastore/index_file.py:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2/txdav/carddav/datastore/index_file.py:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692/txdav/carddav/datastore/index_file.py:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627/txdav/carddav/datastore/index_file.py:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591/txdav/carddav/datastore/index_file.py:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464/txdav/carddav/datastore/index_file.py:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070/txdav/carddav/datastore/index_file.py:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187/txdav/carddav/datastore/index_file.py:5188-5440
/CalendarServer/branches/users/glyph/conn-limit/txdav/carddav/datastore/index_file.py:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge/txdav/carddav/datastore/index_file.py:4971-5080
/CalendarServer/branches/users/glyph/dalify/txdav/carddav/datastore/index_file.py:6932-7023
/CalendarServer/branches/users/glyph/dont-start-postgres/txdav/carddav/datastore/index_file.py:6592-6614
/CalendarServer/branches/users/glyph/linux-tests/txdav/carddav/datastore/index_file.py:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6/txdav/carddav/datastore/index_file.py:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7/txdav/carddav/datastore/index_file.py:6369-6445
/CalendarServer/branches/users/glyph/sendfdport/txdav/carddav/datastore/index_file.py:5388-5424
/CalendarServer/branches/users/glyph/sharedpool/txdav/carddav/datastore/index_file.py:6490-6550
/CalendarServer/branches/users/glyph/sql-store/txdav/carddav/datastore/index_file.py:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted/txdav/carddav/datastore/index_file.py:5084-5149
/CalendarServer/branches/users/sagen/locations-resources/txdav/carddav/datastore/index_file.py:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2/txdav/carddav/datastore/index_file.py:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events/txdav/carddav/datastore/index_file.py:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038/txdav/carddav/datastore/index_file.py:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066/txdav/carddav/datastore/index_file.py:4068-4075
/CalendarServer/branches/users/sagen/resources-2/txdav/carddav/datastore/index_file.py:5084-5093
/CalendarServer/branches/users/wsanchez/transations/txdav/carddav/datastore/index_file.py:5515-5593
/CalendarServer/trunk/twistedcaldav/vcardindex.py:6322-6394
+ /CalendarServer/branches/config-separation/txdav/carddav/datastore/index_file.py:4379-4443
/CalendarServer/branches/egg-info-351/txdav/carddav/datastore/index_file.py:4589-4625
/CalendarServer/branches/generic-sqlstore/txdav/carddav/datastore/index_file.py:6167-6191
/CalendarServer/branches/new-store/txdav/carddav/datastore/index_file.py:5594-5934
/CalendarServer/branches/new-store-no-caldavfile/txdav/carddav/datastore/index_file.py:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2/txdav/carddav/datastore/index_file.py:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692/txdav/carddav/datastore/index_file.py:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627/txdav/carddav/datastore/index_file.py:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591/txdav/carddav/datastore/index_file.py:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464/txdav/carddav/datastore/index_file.py:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070/txdav/carddav/datastore/index_file.py:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187/txdav/carddav/datastore/index_file.py:5188-5440
/CalendarServer/branches/users/glyph/conn-limit/txdav/carddav/datastore/index_file.py:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge/txdav/carddav/datastore/index_file.py:4971-5080
/CalendarServer/branches/users/glyph/dalify/txdav/carddav/datastore/index_file.py:6932-7023
/CalendarServer/branches/users/glyph/dont-start-postgres/txdav/carddav/datastore/index_file.py:6592-6614
/CalendarServer/branches/users/glyph/linux-tests/txdav/carddav/datastore/index_file.py:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6/txdav/carddav/datastore/index_file.py:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7/txdav/carddav/datastore/index_file.py:6369-6445
/CalendarServer/branches/users/glyph/oracle/txdav/carddav/datastore/index_file.py:7106-7155
/CalendarServer/branches/users/glyph/sendfdport/txdav/carddav/datastore/index_file.py:5388-5424
/CalendarServer/branches/users/glyph/sharedpool/txdav/carddav/datastore/index_file.py:6490-6550
/CalendarServer/branches/users/glyph/sql-store/txdav/carddav/datastore/index_file.py:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted/txdav/carddav/datastore/index_file.py:5084-5149
/CalendarServer/branches/users/sagen/locations-resources/txdav/carddav/datastore/index_file.py:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2/txdav/carddav/datastore/index_file.py:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events/txdav/carddav/datastore/index_file.py:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038/txdav/carddav/datastore/index_file.py:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066/txdav/carddav/datastore/index_file.py:4068-4075
/CalendarServer/branches/users/sagen/resources-2/txdav/carddav/datastore/index_file.py:5084-5093
/CalendarServer/branches/users/wsanchez/transations/txdav/carddav/datastore/index_file.py:5515-5593
/CalendarServer/trunk/twistedcaldav/vcardindex.py:6322-6394
Property changes on: CalendarServer/trunk/txdav/carddav/datastore/test/test_index_file.py
___________________________________________________________________
Modified: svn:mergeinfo
- /CalendarServer/branches/config-separation/txdav/carddav/datastore/test/test_index_file.py:4379-4443
/CalendarServer/branches/egg-info-351/txdav/carddav/datastore/test/test_index_file.py:4589-4625
/CalendarServer/branches/generic-sqlstore/txdav/carddav/datastore/test/test_index_file.py:6167-6191
/CalendarServer/branches/new-store/txdav/carddav/datastore/test/test_index_file.py:5594-5934
/CalendarServer/branches/new-store-no-caldavfile/txdav/carddav/datastore/test/test_index_file.py:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2/txdav/carddav/datastore/test/test_index_file.py:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692/txdav/carddav/datastore/test/test_index_file.py:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627/txdav/carddav/datastore/test/test_index_file.py:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591/txdav/carddav/datastore/test/test_index_file.py:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464/txdav/carddav/datastore/test/test_index_file.py:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070/txdav/carddav/datastore/test/test_index_file.py:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187/txdav/carddav/datastore/test/test_index_file.py:5188-5440
/CalendarServer/branches/users/glyph/conn-limit/txdav/carddav/datastore/test/test_index_file.py:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge/txdav/carddav/datastore/test/test_index_file.py:4971-5080
/CalendarServer/branches/users/glyph/dalify/txdav/carddav/datastore/test/test_index_file.py:6932-7023
/CalendarServer/branches/users/glyph/dont-start-postgres/txdav/carddav/datastore/test/test_index_file.py:6592-6614
/CalendarServer/branches/users/glyph/linux-tests/txdav/carddav/datastore/test/test_index_file.py:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6/txdav/carddav/datastore/test/test_index_file.py:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7/txdav/carddav/datastore/test/test_index_file.py:6369-6445
/CalendarServer/branches/users/glyph/sendfdport/txdav/carddav/datastore/test/test_index_file.py:5388-5424
/CalendarServer/branches/users/glyph/sharedpool/txdav/carddav/datastore/test/test_index_file.py:6490-6550
/CalendarServer/branches/users/glyph/sql-store/txdav/carddav/datastore/test/test_index_file.py:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted/txdav/carddav/datastore/test/test_index_file.py:5084-5149
/CalendarServer/branches/users/sagen/locations-resources/txdav/carddav/datastore/test/test_index_file.py:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2/txdav/carddav/datastore/test/test_index_file.py:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events/txdav/carddav/datastore/test/test_index_file.py:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038/txdav/carddav/datastore/test/test_index_file.py:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066/txdav/carddav/datastore/test/test_index_file.py:4068-4075
/CalendarServer/branches/users/sagen/resources-2/txdav/carddav/datastore/test/test_index_file.py:5084-5093
/CalendarServer/branches/users/wsanchez/transations/txdav/carddav/datastore/test/test_index_file.py:5515-5593
/CalendarServer/trunk/twistedcaldav/test/test_vcardindex.py:6322-6394
+ /CalendarServer/branches/config-separation/txdav/carddav/datastore/test/test_index_file.py:4379-4443
/CalendarServer/branches/egg-info-351/txdav/carddav/datastore/test/test_index_file.py:4589-4625
/CalendarServer/branches/generic-sqlstore/txdav/carddav/datastore/test/test_index_file.py:6167-6191
/CalendarServer/branches/new-store/txdav/carddav/datastore/test/test_index_file.py:5594-5934
/CalendarServer/branches/new-store-no-caldavfile/txdav/carddav/datastore/test/test_index_file.py:5911-5935
/CalendarServer/branches/new-store-no-caldavfile-2/txdav/carddav/datastore/test/test_index_file.py:5936-5981
/CalendarServer/branches/users/cdaboo/cached-subscription-calendars-5692/txdav/carddav/datastore/test/test_index_file.py:5693-5702
/CalendarServer/branches/users/cdaboo/directory-cache-on-demand-3627/txdav/carddav/datastore/test/test_index_file.py:3628-3644
/CalendarServer/branches/users/cdaboo/more-sharing-5591/txdav/carddav/datastore/test/test_index_file.py:5592-5601
/CalendarServer/branches/users/cdaboo/partition-4464/txdav/carddav/datastore/test/test_index_file.py:4465-4957
/CalendarServer/branches/users/cdaboo/relative-config-paths-5070/txdav/carddav/datastore/test/test_index_file.py:5071-5105
/CalendarServer/branches/users/cdaboo/shared-calendars-5187/txdav/carddav/datastore/test/test_index_file.py:5188-5440
/CalendarServer/branches/users/glyph/conn-limit/txdav/carddav/datastore/test/test_index_file.py:6574-6577
/CalendarServer/branches/users/glyph/contacts-server-merge/txdav/carddav/datastore/test/test_index_file.py:4971-5080
/CalendarServer/branches/users/glyph/dalify/txdav/carddav/datastore/test/test_index_file.py:6932-7023
/CalendarServer/branches/users/glyph/dont-start-postgres/txdav/carddav/datastore/test/test_index_file.py:6592-6614
/CalendarServer/branches/users/glyph/linux-tests/txdav/carddav/datastore/test/test_index_file.py:6893-6900
/CalendarServer/branches/users/glyph/more-deferreds-6/txdav/carddav/datastore/test/test_index_file.py:6322-6368
/CalendarServer/branches/users/glyph/more-deferreds-7/txdav/carddav/datastore/test/test_index_file.py:6369-6445
/CalendarServer/branches/users/glyph/oracle/txdav/carddav/datastore/test/test_index_file.py:7106-7155
/CalendarServer/branches/users/glyph/sendfdport/txdav/carddav/datastore/test/test_index_file.py:5388-5424
/CalendarServer/branches/users/glyph/sharedpool/txdav/carddav/datastore/test/test_index_file.py:6490-6550
/CalendarServer/branches/users/glyph/sql-store/txdav/carddav/datastore/test/test_index_file.py:5929-6073
/CalendarServer/branches/users/glyph/use-system-twisted/txdav/carddav/datastore/test/test_index_file.py:5084-5149
/CalendarServer/branches/users/sagen/locations-resources/txdav/carddav/datastore/test/test_index_file.py:5032-5051
/CalendarServer/branches/users/sagen/locations-resources-2/txdav/carddav/datastore/test/test_index_file.py:5052-5061
/CalendarServer/branches/users/sagen/purge_old_events/txdav/carddav/datastore/test/test_index_file.py:6735-6746
/CalendarServer/branches/users/sagen/resource-delegates-4038/txdav/carddav/datastore/test/test_index_file.py:4040-4067
/CalendarServer/branches/users/sagen/resource-delegates-4066/txdav/carddav/datastore/test/test_index_file.py:4068-4075
/CalendarServer/branches/users/sagen/resources-2/txdav/carddav/datastore/test/test_index_file.py:5084-5093
/CalendarServer/branches/users/wsanchez/transations/txdav/carddav/datastore/test/test_index_file.py:5515-5593
/CalendarServer/trunk/twistedcaldav/test/test_vcardindex.py:6322-6394
Modified: CalendarServer/trunk/txdav/common/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/txdav/common/datastore/sql.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -62,13 +62,11 @@
from twext.enterprise.dal.syntax import Delete
from twext.enterprise.dal.syntax import Insert
from twext.enterprise.dal.syntax import Len
-from twext.enterprise.dal.syntax import Lock
from twext.enterprise.dal.syntax import Max
from twext.enterprise.dal.syntax import Parameter
from twext.enterprise.dal.syntax import SavepointAction
from twext.enterprise.dal.syntax import Select
from twext.enterprise.dal.syntax import Update
-from twext.enterprise.dal.syntax import default
from txdav.base.propertystore.base import PropertyName
from txdav.base.propertystore.none import PropertyStore as NonePropertyStore
@@ -179,6 +177,7 @@
CommonStoreTransaction._homeClass[EADDRESSBOOKTYPE] = AddressBookHome
self._sqlTxn = sqlTxn
self.paramstyle = sqlTxn.paramstyle
+ self.dialect = sqlTxn.dialect
def store(self):
@@ -484,7 +483,7 @@
{cls._homeMetaDataSchema.RESOURCE_ID: resourceid}).on(txn)
except Exception: # FIXME: Really want to trap the pg.DatabaseError but in a non-DB specific manner
yield savepoint.rollback(txn)
-
+
# Retry the query - row may exist now, if not re-raise
homeObject = cls(txn, uid, notifiers)
homeObject = (yield homeObject.initFromStore())
@@ -1081,8 +1080,7 @@
rev.DELETED: True},
Where=(rev.HOME_RESOURCE_ID == Parameter("homeID")).And(
rev.RESOURCE_ID == Parameter("resourceID")).And(
- rev.RESOURCE_NAME == None),
- #Return=rev.REVISION
+ rev.RESOURCE_NAME == None)
)
@@ -1097,7 +1095,6 @@
rev.DELETED: True},
Where=(rev.RESOURCE_ID == Parameter("resourceID")).And(
rev.RESOURCE_NAME == None),
- # Return=rev.REVISION,
)
@@ -1501,7 +1498,7 @@
DAL statement to create a home child with all default values.
"""
child = cls._homeChildSchema
- return Insert({child.RESOURCE_ID: default},
+ return Insert({child.RESOURCE_ID: schema.RESOURCE_ID_SEQ},
Return=(child.RESOURCE_ID, child.CREATED, child.MODIFIED))
@@ -2164,25 +2161,6 @@
returnValue(None)
- @classmethod
- def _selectAllColumns(cls):
- """
- Full set of columns in the object table that need to be loaded to
- initialize the object resource state. (XXX: remove me, old string-based
- version, see _allColumns)
- """
- return """
- select
- %(column_RESOURCE_ID)s,
- %(column_RESOURCE_NAME)s,
- %(column_UID)s,
- %(column_MD5)s,
- character_length(%(column_TEXT)s),
- %(column_CREATED)s,
- %(column_MODIFIED)s
- """ % cls._objectTable
-
-
@classproperty
def _allColumns(cls):
"""
Modified: CalendarServer/trunk/txdav/common/datastore/sql_legacy.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_legacy.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/txdav/common/datastore/sql_legacy.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -1,4 +1,4 @@
-# -*- test-case-name: txdav.caldav.datastore.test.test_sql -*-
+# -*- test-case-name: twistedcaldav.test.test_sharing,twistedcaldav.test.test_calendarquery -*-
##
# Copyright (c) 2010 Apple Inc. All rights reserved.
#
@@ -28,6 +28,7 @@
from twisted.python import hashlib
from twisted.internet.defer import succeed, inlineCallbacks, returnValue
+from twext.python.clsprop import classproperty
from twext.python.log import Logger, LoggingMixIn
from twistedcaldav import carddavxml
@@ -35,19 +36,26 @@
from twistedcaldav.dateops import normalizeForIndex
from twistedcaldav.memcachepool import CachePoolUserMixIn
from twistedcaldav.notifications import NotificationRecord
-from twistedcaldav.query import calendarqueryfilter, calendarquery, \
- addressbookquery
+from twistedcaldav.query import (
+ calendarqueryfilter, calendarquery, addressbookquery)
from twistedcaldav.query.sqlgenerator import sqlgenerator
from twistedcaldav.sharing import Invite
-from txdav.common.icommondatastore import IndexedSearchException, \
- ReservationError
-from txdav.common.datastore.sql_tables import \
- _BIND_MODE_OWN, _BIND_MODE_READ, _BIND_MODE_WRITE, _BIND_MODE_DIRECT, \
- _BIND_STATUS_INVITED, _BIND_STATUS_ACCEPTED, _BIND_STATUS_DECLINED, _BIND_STATUS_INVALID, \
- CALENDAR_BIND_TABLE, CALENDAR_HOME_TABLE, ADDRESSBOOK_HOME_TABLE, \
- ADDRESSBOOK_BIND_TABLE
+from txdav.common.icommondatastore import (
+ IndexedSearchException, ReservationError)
+from twext.enterprise.dal.syntax import Update
+from twext.enterprise.dal.syntax import Insert
+from twext.enterprise.dal.syntax import Select
+from twext.enterprise.dal.syntax import Delete
+from twext.enterprise.dal.syntax import Parameter
+from txdav.common.datastore.sql_tables import (
+ _BIND_MODE_OWN, _BIND_MODE_READ, _BIND_MODE_WRITE, _BIND_MODE_DIRECT,
+ _BIND_STATUS_INVITED, _BIND_STATUS_ACCEPTED, _BIND_STATUS_DECLINED,
+ _BIND_STATUS_INVALID, CALENDAR_BIND_TABLE, CALENDAR_HOME_TABLE,
+ ADDRESSBOOK_HOME_TABLE, ADDRESSBOOK_BIND_TABLE, schema)
+
+
log = Logger()
indexfbtype_to_icalfbtype = {
@@ -108,6 +116,9 @@
_homeTable = None
_bindTable = None
+ _homeSchema = None
+ _bindSchema = None
+
def __init__(self, collection):
self._collection = collection
@@ -136,56 +147,63 @@
"No-op, because the index implicitly always exists in the database."
+ @classmethod
+ def _allColumnsQuery(cls, condition):
+ inv = schema.INVITE
+ home = cls._homeSchema
+ bind = cls._bindSchema
+ return Select(
+ [inv.INVITE_UID,
+ inv.NAME,
+ inv.RECIPIENT_ADDRESS,
+ home.OWNER_UID,
+ bind.BIND_MODE,
+ bind.BIND_STATUS,
+ bind.MESSAGE],
+ From=inv.join(home).join(bind),
+ Where=(
+ condition
+ .And(inv.RESOURCE_ID == bind.RESOURCE_ID)
+ .And(inv.HOME_RESOURCE_ID == home.RESOURCE_ID)
+ .And(inv.HOME_RESOURCE_ID == bind.HOME_RESOURCE_ID)),
+ OrderBy=inv.NAME, Ascending=True
+ )
+
+
+ @classproperty
+ def _allRecordsQuery(cls):
+ """
+ DAL query for all invite records with a given resource ID.
+ """
+ inv = schema.INVITE
+ return cls._allColumnsQuery(inv.RESOURCE_ID == Parameter("resourceID"))
+
+
@inlineCallbacks
def allRecords(self):
values = []
- for row in (yield self._txn.execSQL(
- """
- select
- INVITE.INVITE_UID,
- INVITE.NAME,
- INVITE.RECIPIENT_ADDRESS,
- %(HOME:name)s.%(HOME:column_OWNER_UID)s,
- %(BIND:name)s.%(BIND:column_BIND_MODE)s,
- %(BIND:name)s.%(BIND:column_BIND_STATUS)s,
- %(BIND:name)s.%(BIND:column_MESSAGE)s
- from
- INVITE, %(HOME:name)s, %(BIND:name)s
- where
- INVITE.RESOURCE_ID = %%s
- and INVITE.HOME_RESOURCE_ID = %(HOME:name)s.%(HOME:column_RESOURCE_ID)s
- and %(BIND:name)s.%(BIND:column_RESOURCE_ID)s = INVITE.RESOURCE_ID
- and %(BIND:name)s.%(BIND:column_HOME_RESOURCE_ID)s = INVITE.HOME_RESOURCE_ID
- order by
- INVITE.NAME asc
- """ % self._combinedTable,
- [self._collection._resourceID]
- )):
+ rows = yield self._allRecordsQuery.on(
+ self._txn, resourceID=self._collection._resourceID
+ )
+ for row in rows:
values.append(self._makeInvite(row))
returnValue(values)
+ @classproperty
+ def _inviteForRecipientQuery(cls):
+ """
+ DAL query to retrieve an invite record for a given recipient address.
+ """
+ inv = schema.INVITE
+ return cls._allColumnsQuery(
+ inv.RECIPIENT_ADDRESS == Parameter("recipient"))
+
+
@inlineCallbacks
def recordForUserID(self, userid):
- rows = yield self._txn.execSQL(
- """
- select
- INVITE.INVITE_UID,
- INVITE.NAME,
- INVITE.RECIPIENT_ADDRESS,
- %(HOME:name)s.%(HOME:column_OWNER_UID)s,
- %(BIND:name)s.%(BIND:column_BIND_MODE)s,
- %(BIND:name)s.%(BIND:column_BIND_STATUS)s,
- %(BIND:name)s.%(BIND:column_MESSAGE)s
- from
- INVITE, %(HOME:name)s, %(BIND:name)s
- where INVITE.RECIPIENT_ADDRESS = %%s
- and INVITE.HOME_RESOURCE_ID = %(HOME:name)s.%(HOME:column_RESOURCE_ID)s
- and %(BIND:name)s.%(BIND:column_RESOURCE_ID)s = INVITE.RESOURCE_ID
- and %(BIND:name)s.%(BIND:column_HOME_RESOURCE_ID)s = INVITE.HOME_RESOURCE_ID
- """ % self._combinedTable,
- [userid]
- )
+ rows = yield self._inviteForRecipientQuery.on(self._txn,
+ recipient=userid)
returnValue(self._makeInvite(rows[0]) if rows else None)
@@ -196,27 +214,18 @@
returnValue(record)
+ @classproperty
+ def _inviteForUIDQuery(cls):
+ """
+ DAL query to retrieve an invite record for a given recipient address.
+ """
+ inv = schema.INVITE
+ return cls._allColumnsQuery(inv.INVITE_UID == Parameter("uid"))
+
+
@inlineCallbacks
def recordForInviteUID(self, inviteUID):
- rows = yield self._txn.execSQL(
- """
- select
- INVITE.INVITE_UID,
- INVITE.NAME,
- INVITE.RECIPIENT_ADDRESS,
- %(HOME:name)s.%(HOME:column_OWNER_UID)s,
- %(BIND:name)s.%(BIND:column_BIND_MODE)s,
- %(BIND:name)s.%(BIND:column_BIND_STATUS)s,
- %(BIND:name)s.%(BIND:column_MESSAGE)s
- from
- INVITE, %(HOME:name)s, %(BIND:name)s
- where INVITE.INVITE_UID = %%s
- and INVITE.HOME_RESOURCE_ID = %(HOME:name)s.%(HOME:column_RESOURCE_ID)s
- and %(BIND:name)s.%(BIND:column_RESOURCE_ID)s = INVITE.RESOURCE_ID
- and %(BIND:name)s.%(BIND:column_HOME_RESOURCE_ID)s = INVITE.HOME_RESOURCE_ID
- """ % self._combinedTable,
- [inviteUID]
- )
+ rows = yield self._inviteForUIDQuery.on(self._txn, uid=inviteUID)
returnValue(self._makeInvite(rows[0]) if rows else None)
@@ -242,6 +251,71 @@
)
+ @classproperty
+ def _updateBindQuery(cls):
+ bind = cls._bindSchema
+
+ return Update({bind.BIND_MODE: Parameter("mode"),
+ bind.BIND_STATUS: Parameter("status"),
+ bind.MESSAGE: Parameter("message")},
+ Where=
+ (bind.RESOURCE_ID == Parameter("resourceID"))
+ .And(bind.HOME_RESOURCE_ID == Parameter("homeID")))
+
+
+ @classproperty
+ def _idsForRecipient(cls):
+ inv = schema.INVITE
+ return Select([inv.RESOURCE_ID, inv.HOME_RESOURCE_ID],
+ From=inv,
+ Where=inv.RECIPIENT_ADDRESS == Parameter("recipient"))
+
+
+ @classproperty
+ def _updateInviteQuery(cls):
+ """
+ DAL query to update an invitation for a given recipient.
+ """
+ inv = schema.INVITE
+ return Update({inv.NAME: Parameter("name"),
+ inv.INVITE_UID: Parameter("uid")},
+ Where=inv.RECIPIENT_ADDRESS == Parameter("recipient"))
+
+
+ @classproperty
+ def _insertBindQuery(cls):
+ bind = cls._bindSchema
+ return Insert(
+ {
+ bind.HOME_RESOURCE_ID: Parameter("homeID"),
+ bind.RESOURCE_ID: Parameter("resourceID"),
+ bind.BIND_MODE: Parameter("mode"),
+ bind.BIND_STATUS: Parameter("status"),
+ bind.MESSAGE: Parameter("message"),
+
+ # name is NULL because the resource is not bound yet, just
+ # invited; let's be explicit about that.
+ bind.RESOURCE_NAME: None,
+ bind.SEEN_BY_OWNER: False,
+ bind.SEEN_BY_SHAREE: False,
+ }
+ )
+
+
+ @classproperty
+ def _insertInviteQuery(cls):
+ inv = schema.INVITE
+ return Insert(
+ {
+ inv.INVITE_UID: Parameter("uid"),
+ inv.NAME: Parameter("name"),
+ inv.HOME_RESOURCE_ID: Parameter("homeID"),
+ inv.RESOURCE_ID: Parameter("resourceID"),
+ inv.RECIPIENT_ADDRESS: Parameter("recipient")
+ }
+ )
+
+
@inlineCallbacks
def addOrUpdateRecord(self, record):
bindMode = {'read-only': _BIND_MODE_READ,
@@ -257,108 +331,88 @@
# (and may contain a trailing slash).
principalUID = record.principalURL.split("/")[3]
shareeHome = yield self._getHomeWithUID(principalUID)
- rows = yield self._txn.execSQL(
- "select RESOURCE_ID, HOME_RESOURCE_ID from INVITE where RECIPIENT_ADDRESS = %s",
- [record.userid]
- )
+ rows = yield self._idsForRecipient.on(self._txn,
+ recipient=record.userid)
if rows:
[[resourceID, homeResourceID]] = rows
- # Invite(inviteuid, userid, principalURL, common_name, access, state, summary)
- yield self._txn.execSQL(
- """
- update %(BIND:name)s
- set %(BIND:column_BIND_MODE)s = %%s,
- %(BIND:column_BIND_STATUS)s = %%s,
- %(BIND:column_MESSAGE)s = %%s
- where %(BIND:column_RESOURCE_ID)s = %%s
- and %(BIND:column_HOME_RESOURCE_ID)s = %%s
- """ % self._combinedTable,
- [bindMode, bindStatus, record.summary, resourceID, homeResourceID]
+ yield self._updateBindQuery.on(
+ self._txn,
+ mode=bindMode, status=bindStatus, message=record.summary,
+ resourceID=resourceID, homeID=homeResourceID
)
- yield self._txn.execSQL("""
- update INVITE
- set NAME = %s, INVITE_UID = %s
- where RECIPIENT_ADDRESS = %s
- """,
- [record.name, record.inviteuid, record.userid]
+ yield self._updateInviteQuery.on(
+ self._txn, name=record.name, uid=record.inviteuid,
+ recipient=record.userid
)
else:
- yield self._txn.execSQL(
- """
- insert into INVITE
- (
- INVITE_UID, NAME,
- HOME_RESOURCE_ID, RESOURCE_ID,
- RECIPIENT_ADDRESS
- )
- values (%s, %s, %s, %s, %s)
- """,
- [
- record.inviteuid, record.name,
- shareeHome._resourceID, self._collection._resourceID,
- record.userid
- ])
- yield self._txn.execSQL(
- """
- insert into %(BIND:name)s
- (
- %(BIND:column_HOME_RESOURCE_ID)s,
- %(BIND:column_RESOURCE_ID)s,
- %(BIND:column_RESOURCE_NAME)s,
- %(BIND:column_BIND_MODE)s,
- %(BIND:column_BIND_STATUS)s,
- %(BIND:column_SEEN_BY_OWNER)s,
- %(BIND:column_SEEN_BY_SHAREE)s,
- %(BIND:column_MESSAGE)s
- )
- values (%%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s)
- """ % self._combinedTable,
- [
- shareeHome._resourceID,
- self._collection._resourceID,
- None, # this is NULL because it is not bound yet, let's be
- # explicit about that.
- bindMode,
- bindStatus,
- False,
- False,
- record.summary
- ]
+ yield self._insertInviteQuery.on(
+ self._txn, uid=record.inviteuid, name=record.name,
+ homeID=shareeHome._resourceID,
+ resourceID=self._collection._resourceID,
+ recipient=record.userid
)
+ yield self._insertBindQuery.on(
+ self._txn,
+ homeID=shareeHome._resourceID,
+ resourceID=self._collection._resourceID,
+ mode=bindMode,
+ status=bindStatus,
+ message=record.summary
+ )
+ @classmethod
+ def _deleteOneBindQuery(cls, constraint):
+ inv = schema.INVITE
+ bind = cls._bindSchema
+ return Delete(
+ From=bind, Where=(bind.HOME_RESOURCE_ID, bind.RESOURCE_ID) ==
+ Select([inv.HOME_RESOURCE_ID, inv.RESOURCE_ID],
+ From=inv, Where=constraint))
+
+
+ @classmethod
+ def _deleteOneInviteQuery(cls, constraint):
+ inv = schema.INVITE
+ return Delete(From=inv, Where=constraint)
+
+
+ @classproperty
+ def _deleteBindByRecipient(cls):
+ inv = schema.INVITE
+ return cls._deleteOneBindQuery(
+ inv.RECIPIENT_ADDRESS == Parameter("recipient"))
+
+
+ @classproperty
+ def _deleteInviteByRecipient(cls):
+ inv = schema.INVITE
+ return cls._deleteOneInviteQuery(
+ inv.RECIPIENT_ADDRESS == Parameter("recipient"))
+
+
+ @classproperty
+ def _deleteBindByUID(cls):
+ inv = schema.INVITE
+ return cls._deleteOneBindQuery(inv.INVITE_UID == Parameter("uid"))
+
+
+ @classproperty
+ def _deleteInviteByUID(cls):
+ inv = schema.INVITE
+ return cls._deleteOneInviteQuery(inv.INVITE_UID == Parameter("uid"))
+
+
@inlineCallbacks
def removeRecordForUserID(self, userid):
- yield self._txn.execSQL(
- """
- delete from %(BIND:name)s using INVITE
- where INVITE.RECIPIENT_ADDRESS = %%s
- and %(BIND:name)s.%(BIND:column_HOME_RESOURCE_ID)s = INVITE.HOME_RESOURCE_ID
- and %(BIND:name)s.%(BIND:column_RESOURCE_ID)s = INVITE.RESOURCE_ID
- """ % self._combinedTable,
- [userid]
- )
- yield self._txn.execSQL(
- "delete from INVITE where RECIPIENT_ADDRESS = %s",
- [userid]
- )
+ yield self._deleteBindByRecipient.on(self._txn, recipient=userid)
+ yield self._deleteInviteByRecipient.on(self._txn, recipient=userid)
@inlineCallbacks
def removeRecordForInviteUID(self, inviteUID):
- yield self._txn.execSQL(
- """
- delete from %(BIND:name)s using INVITE
- where INVITE.INVITE_UID = %s
- and %(BIND:name)s.%(BIND:column_HOME_RESOURCE_ID)s = INVITE.HOME_RESOURCE_ID
- and %(BIND:name)s.%(BIND:column_RESOURCE_ID)s = INVITE.RESOURCE_ID
- """ % self._combinedTable,
- [inviteUID]
- )
- yield self._txn.execSQL(
- "delete from INVITE where INVITE_UID = %s",
- [inviteUID]
- )
+ yield self._deleteBindByUID.on(self._txn, uid=inviteUID)
+ yield self._deleteInviteByUID.on(self._txn, uid=inviteUID)
@@ -368,11 +422,11 @@
L{twistedcaldav.sharing.InvitesDatabase}.
"""
- def __init__(self, calendar):
- self._homeTable = CALENDAR_HOME_TABLE
- self._bindTable = CALENDAR_BIND_TABLE
- super(SQLLegacyCalendarInvites, self).__init__(calendar)
+ _homeTable = CALENDAR_HOME_TABLE
+ _bindTable = CALENDAR_BIND_TABLE
+ _homeSchema = schema.CALENDAR_HOME
+ _bindSchema = schema.CALENDAR_BIND
def _getHomeWithUID(self, uid):
return self._txn.calendarHomeWithUID(uid, create=True)
@@ -385,11 +439,11 @@
L{twistedcaldav.sharing.InvitesDatabase}.
"""
- def __init__(self, addressbook):
- self._homeTable = ADDRESSBOOK_HOME_TABLE
- self._bindTable = ADDRESSBOOK_BIND_TABLE
- super(SQLLegacyAddressBookInvites, self).__init__(addressbook)
+ _homeTable = ADDRESSBOOK_HOME_TABLE
+ _bindTable = ADDRESSBOOK_BIND_TABLE
+ _homeSchema = schema.ADDRESSBOOK_HOME
+ _bindSchema = schema.ADDRESSBOOK_BIND
def _getHomeWithUID(self, uid):
return self._txn.addressbookHomeWithUID(uid, create=True)
@@ -402,9 +456,13 @@
_bindTable = None
_urlTopSegment = None
+ _homeSchema = None
+ _bindSchema = None
+
def __init__(self, home):
self._home = home
+
@property
def _txn(self):
return self._home._txn
@@ -422,6 +480,50 @@
pass
+ @classproperty
+ def _allSharedToQuery(cls):
+ bind = cls._bindSchema
+ return Select(
+ [bind.RESOURCE_ID, bind.RESOURCE_NAME,
+ bind.BIND_MODE, bind.MESSAGE],
+ From=bind,
+ Where=(bind.HOME_RESOURCE_ID == Parameter("homeID"))
+ .And(bind.BIND_MODE != _BIND_MODE_OWN)
+ .And(bind.RESOURCE_NAME != None)
+ )
+
+
+ @classproperty
+ def _inviteUIDByResourceIDsQuery(cls):
+ inv = schema.INVITE
+ return Select(
+ [inv.INVITE_UID], From=inv, Where=
+ (inv.RESOURCE_ID == Parameter("resourceID"))
+ .And(inv.HOME_RESOURCE_ID == Parameter("homeID"))
+ )
+
+
+ @classproperty
+ def _ownerHomeIDAndName(cls):
+ bind = cls._bindSchema
+ return Select(
+ [bind.HOME_RESOURCE_ID, bind.RESOURCE_NAME], From=bind, Where=
+ (bind.RESOURCE_ID == Parameter("resourceID"))
+ .And(bind.BIND_MODE == _BIND_MODE_OWN)
+ )
+
+
+ @classproperty
+ def _ownerUIDFromHomeID(cls):
+ home = cls._homeSchema
+ return Select(
+ [home.OWNER_UID], From=home,
+ Where=home.RESOURCE_ID == Parameter("homeID")
+ )
+
+
+
+
@inlineCallbacks
def allRecords(self):
# This should have been a smart join that got all these columns at
@@ -429,78 +531,31 @@
# _want_ to do (just look for binds in a particular homes) is
# much simpler anyway; we should just do that.
all = []
- shareRows = yield self._txn.execSQL(
- """
- select %(column_RESOURCE_ID)s, %(column_RESOURCE_NAME)s, %(column_BIND_MODE)s, %(column_MESSAGE)s
- from %(name)s
- where %(column_HOME_RESOURCE_ID)s = %%s
- and %(column_BIND_MODE)s != %%s
- and %(column_RESOURCE_NAME)s is not null
- """ % self._bindTable,
- [self._home._resourceID, _BIND_MODE_OWN]
- )
+ shareRows = yield self._allSharedToQuery.on(
+ self._txn, homeID=self._home._resourceID)
for resourceID, resourceName, bindMode, summary in shareRows:
+ [[ownerHomeID, ownerResourceName]] = yield (
+ self._ownerHomeIDAndName.on(self._txn,
+ resourceID=resourceID))
+ [[ownerUID]] = yield self._ownerUIDFromHomeID.on(
+ self._txn, homeID=ownerHomeID)
+ hosturl = '/%s/__uids__/%s/%s' % (
+ self._urlTopSegment, ownerUID, ownerResourceName
+ )
+ localname = resourceName
if bindMode != _BIND_MODE_DIRECT:
- [[shareuid]] = yield self._txn.execSQL(
- """
- select INVITE_UID
- from INVITE
- where RESOURCE_ID = %s and HOME_RESOURCE_ID = %s
- """,
- [resourceID, self._home._resourceID]
- )
sharetype = 'I'
- [[ownerHomeID, ownerResourceName]] = yield self._txn.execSQL(
- """
- select %(column_HOME_RESOURCE_ID)s, %(column_RESOURCE_NAME)s
- from %(name)s
- where %(column_RESOURCE_ID)s = %%s
- and %(column_BIND_MODE)s = %%s
- """ % self._bindTable,
- [resourceID, _BIND_MODE_OWN]
+ [[shareuid]] = yield self._inviteUIDByResourceIDsQuery.on(
+ self._txn, resourceID=resourceID,
+ homeID=self._home._resourceID
)
- [[ownerUID]] = yield self._txn.execSQL(
- """
- select %(column_OWNER_UID)s from %(name)s
- where %(column_RESOURCE_ID)s = %%s
- """ % self._homeTable,
- [ownerHomeID]
- )
- hosturl = '/%s/__uids__/%s/%s' % (
- self._urlTopSegment, ownerUID, ownerResourceName
- )
- localname = resourceName
- record = SharedCollectionRecord(
- shareuid, sharetype, hosturl, localname, summary
- )
- all.append(record)
else:
sharetype = 'D'
- [[ownerHomeID, ownerResourceName]] = yield self._txn.execSQL(
- """
- select %(column_HOME_RESOURCE_ID)s, %(column_RESOURCE_NAME)s
- from %(name)s
- where %(column_RESOURCE_ID)s = %%s
- and %(column_BIND_MODE)s = %%s
- """ % self._bindTable,
- [resourceID, _BIND_MODE_OWN]
- )
- [[ownerUID]] = yield self._txn.execSQL(
- """
- select %(column_OWNER_UID)s from %(name)s
- where %(column_RESOURCE_ID)s = %%s
- """ % self._homeTable,
- [ownerHomeID]
- )
- hosturl = '/%s/__uids__/%s/%s' % (
- self._urlTopSegment, ownerUID, ownerResourceName
- )
- localname = resourceName
- synthesisedUID = "Direct-%s-%s" % (self._home._resourceID, resourceID,)
- record = SharedCollectionRecord(
- synthesisedUID, sharetype, hosturl, localname, summary
- )
- all.append(record)
+ shareuid = "Direct-%s-%s" % (self._home._resourceID, resourceID,)
+ record = SharedCollectionRecord(
+ shareuid, sharetype, hosturl, localname, summary
+ )
+ all.append(record)
returnValue(all)
@@ -516,15 +571,39 @@
return self._search(shareuid=shareUID)
+ @classproperty
+ def _updateBindName(cls):
+ bind = cls._bindSchema
+ return Update({bind.RESOURCE_NAME: Parameter("localname")},
+ Where=(bind.HOME_RESOURCE_ID == Parameter("homeID"))
+ .And(bind.RESOURCE_ID == Parameter('resourceID')))
+
+
+ @classproperty
+ def _acceptDirectShareQuery(cls):
+ bind = cls._bindSchema
+ return Insert({
+ bind.HOME_RESOURCE_ID: Parameter("homeID"),
+ bind.RESOURCE_ID: Parameter("resourceID"),
+ bind.RESOURCE_NAME: Parameter("name"),
+ bind.MESSAGE: Parameter("message"),
+ bind.BIND_MODE: _BIND_MODE_DIRECT,
+ bind.BIND_STATUS: _BIND_STATUS_ACCEPTED,
+ bind.SEEN_BY_OWNER: True,
+ bind.SEEN_BY_SHAREE: True,
+ })
+
+
@inlineCallbacks
def addOrUpdateRecord(self, record):
# record.hosturl -> /.../__uids__/<uid>/<name>
splithost = record.hosturl.split('/')
-
+
# Double-check the path
if splithost[2] != "__uids__":
- raise ValueError("Sharing URL must be a __uids__ path: %s" % (record.hosturl,))
-
+ raise ValueError(
+ "Sharing URL must be a __uids__ path: %s" % (record.hosturl,))
+
ownerUID = splithost[3]
ownerCollectionName = splithost[4]
ownerHome = yield self._getHomeWithUID(ownerUID)
@@ -532,71 +611,73 @@
collectionResourceID = ownerCollection._resourceID
if record.sharetype == 'I':
-
# There needs to be a bind already, one that corresponds to the
# invitation. The invitation's UID is the same as the share UID. I
# just need to update its 'localname', i.e.
# XXX_BIND.XXX_RESOURCE_NAME.
- yield self._txn.execSQL(
- """
- update %(name)s
- set %(column_RESOURCE_NAME)s = %%s
- where %(column_HOME_RESOURCE_ID)s = %%s
- and %(column_RESOURCE_ID)s = %%s
- """ % self._bindTable,
- [record.localname, self._home._resourceID, collectionResourceID]
+ yield self._updateBindName.on(
+ self._txn, localname=record.localname,
+ homeID=self._home._resourceID, resourceID=collectionResourceID
)
elif record.sharetype == 'D':
# There is no bind entry already so add one.
+ yield self._acceptDirectShareQuery.on(
+ self._txn, homeID=self._home._resourceID,
+ resourceID=collectionResourceID, name=record.localname,
+ message=record.summary
+ )
- yield self._txn.execSQL(
- """
- insert into %(name)s
- (
- %(column_HOME_RESOURCE_ID)s,
- %(column_RESOURCE_ID)s,
- %(column_RESOURCE_NAME)s,
- %(column_BIND_MODE)s,
- %(column_BIND_STATUS)s,
- %(column_SEEN_BY_OWNER)s,
- %(column_SEEN_BY_SHAREE)s,
- %(column_MESSAGE)s
- )
- values (%%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s)
- """ % self._bindTable,
- [
- self._home._resourceID,
- collectionResourceID,
- record.localname,
- _BIND_MODE_DIRECT,
- _BIND_STATUS_ACCEPTED,
- True,
- True,
- record.summary,
- ])
-
- shareeCollection = yield self._home.sharedChildWithName(record.localname)
+ shareeCollection = yield self._home.sharedChildWithName(
+ record.localname)
yield shareeCollection._initSyncToken()
+ @classproperty
+ def _unbindShareQuery(cls):
+ bind = cls._bindSchema
+ return Update({
+ bind.RESOURCE_NAME: None
+ }, Where=(bind.RESOURCE_NAME == Parameter("name"))
+ .And(bind.HOME_RESOURCE_ID == Parameter("homeID")))
+
+
@inlineCallbacks
def removeRecordForLocalName(self, localname):
record = yield self.recordForLocalName(localname)
shareeCollection = yield self._home.sharedChildWithName(record.localname)
yield shareeCollection._deletedSyncToken(sharedRemoval=True)
- returnValue((yield self._txn.execSQL(
- """
- update %(name)s
- set %(column_RESOURCE_NAME)s = NULL
- where %(column_RESOURCE_NAME)s = %%s
- and %(column_HOME_RESOURCE_ID)s = %%s
- """ % self._bindTable,
- [localname, self._home._resourceID]
- )))
+ result = yield self._unbindShareQuery.on(self._txn, name=localname,
+ homeID=self._home._resourceID)
+ returnValue(result)
+ @classproperty
+ def _removeInviteShareQuery(cls):
+ """
+ DAL query to remove a non-direct share by invite UID.
+ """
+ bind = cls._bindSchema
+ inv = schema.INVITE
+ return Update(
+ {bind.RESOURCE_NAME: None},
+ Where=(bind.HOME_RESOURCE_ID, bind.RESOURCE_ID) ==
+ Select([inv.HOME_RESOURCE_ID, inv.RESOURCE_ID],
+ From=inv, Where=inv.INVITE_UID == Parameter("uid")))
+
+
+ @classproperty
+ def _removeDirectShareQuery(cls):
+ """
+ DAL query to remove a direct share by its homeID and resourceID.
+ """
+ bind = cls._bindSchema
+ return Delete(From=bind,
+ Where=(bind.HOME_RESOURCE_ID == Parameter("homeID"))
+ .And(bind.RESOURCE_ID == Parameter("resourceID")))
+
+
@inlineCallbacks
def removeRecordForShareUID(self, shareUID):
@@ -605,47 +686,28 @@
yield shareeCollection._deletedSyncToken(sharedRemoval=True)
if not shareUID.startswith("Direct"):
- yield self._txn.execSQL(
- """
- update %(name)s
- set %(column_RESOURCE_NAME)s = NULL
- from INVITE
- where INVITE.INVITE_UID = %%s
- and %(name)s.%(column_HOME_RESOURCE_ID)s = INVITE.HOME_RESOURCE_ID
- and %(name)s.%(column_RESOURCE_ID)s = INVITE.RESOURCE_ID
- """ % self._bindTable,
- [shareUID, ]
- )
+ yield self._removeInviteShareQuery.on(self._txn, uid=shareUID)
else:
# Extract pieces from synthesised UID
homeID, resourceID = shareUID[len("Direct-"):].split("-")
-
# Now remove the binding for the direct share
- yield self._txn.execSQL(
- """
- delete from %(name)s
- where %(column_HOME_RESOURCE_ID)s = %%s
- and %(column_RESOURCE_ID)s = %%s
- """ % self._bindTable,
- [homeID, resourceID, ]
- )
+ yield self._removeDirectShareQuery.on(
+ self._txn, homeID=homeID, resourceID=resourceID)
-
class SQLLegacyCalendarShares(SQLLegacyShares):
"""
Emulator for the implicit interface specified by
L{twistedcaldav.sharing.InvitesDatabase}.
"""
- def __init__(self, home):
- self._homeTable = CALENDAR_HOME_TABLE
- self._bindTable = CALENDAR_BIND_TABLE
- self._urlTopSegment = "calendars"
+ _homeTable = CALENDAR_HOME_TABLE
+ _bindTable = CALENDAR_BIND_TABLE
+ _homeSchema = schema.CALENDAR_HOME
+ _bindSchema = schema.CALENDAR_BIND
+ _urlTopSegment = "calendars"
- super(SQLLegacyCalendarShares, self).__init__(home)
-
def _getHomeWithUID(self, uid):
return self._txn.calendarHomeWithUID(uid, create=True)
@@ -657,17 +719,18 @@
L{twistedcaldav.sharing.InvitesDatabase}.
"""
- def __init__(self, home):
- self._homeTable = ADDRESSBOOK_HOME_TABLE
- self._bindTable = ADDRESSBOOK_BIND_TABLE
- self._urlTopSegment = "addressbooks"
+ _homeTable = ADDRESSBOOK_HOME_TABLE
+ _bindTable = ADDRESSBOOK_BIND_TABLE
+ _homeSchema = schema.ADDRESSBOOK_HOME
+ _bindSchema = schema.ADDRESSBOOK_BIND
+ _urlTopSegment = "addressbooks"
- super(SQLLegacyAddressBookShares, self).__init__(home)
def _getHomeWithUID(self, uid):
return self._txn.addressbookHomeWithUID(uid, create=True)
+
class MemcachedUIDReserver(CachePoolUserMixIn, LoggingMixIn):
def __init__(self, index, cachePool=None):
self.index = index
@@ -779,10 +842,14 @@
key = self._key(uid)
return succeed(key in self.reservations)
-class postgresqlgenerator(sqlgenerator):
+
+
+class RealSQLBehaviorMixin(object):
"""
- Query generator for postgreSQL indexed searches. (Currently unused: work
- in progress.)
+ Class attributes for 'real' SQL behavior; avoid idiosyncracies of SQLite,
+ use standard SQL constructions, and depend on the full schema in
+ sql_schema_vX.sql rather than the partial one in twistedcaldav which depends
+ on the placement of the database in the filesystem for some information.
"""
ISOP = " = "
@@ -792,24 +859,20 @@
"TYPE": "CALENDAR_OBJECT.ICALENDAR_TYPE",
"UID": "CALENDAR_OBJECT.ICALENDAR_UID",
}
+ RESOURCEDB = "CALENDAR_OBJECT"
+ TIMESPANDB = "TIME_RANGE"
- def __init__(self, expr, calendarid, userid):
- self.RESOURCEDB = "CALENDAR_OBJECT"
- self.TIMESPANDB = "TIME_RANGE"
- self.TIMESPANTEST = "((TIME_RANGE.FLOATING = FALSE AND TIME_RANGE.START_DATE < %s AND TIME_RANGE.END_DATE > %s) OR (TIME_RANGE.FLOATING = TRUE AND TIME_RANGE.START_DATE < %s AND TIME_RANGE.END_DATE > %s))"
- self.TIMESPANTEST_NOEND = "((TIME_RANGE.FLOATING = FALSE AND TIME_RANGE.END_DATE > %s) OR (TIME_RANGE.FLOATING = TRUE AND TIME_RANGE.END_DATE > %s))"
- self.TIMESPANTEST_NOSTART = "((TIME_RANGE.FLOATING = FALSE AND TIME_RANGE.START_DATE < %s) OR (TIME_RANGE.FLOATING = TRUE AND TIME_RANGE.START_DATE < %s))"
- self.TIMESPANTEST_TAIL_PIECE = " AND TIME_RANGE.CALENDAR_OBJECT_RESOURCE_ID = CALENDAR_OBJECT.RESOURCE_ID AND TIME_RANGE.CALENDAR_RESOURCE_ID = %s"
- self.TIMESPANTEST_JOIN_ON_PIECE = "TIME_RANGE.INSTANCE_ID = TRANSPARENCY.TIME_RANGE_INSTANCE_ID AND TRANSPARENCY.USER_ID = %s"
+ TIMESPANTEST = "((TIME_RANGE.FLOATING = FALSE AND TIME_RANGE.START_DATE < %s AND TIME_RANGE.END_DATE > %s) OR (TIME_RANGE.FLOATING = TRUE AND TIME_RANGE.START_DATE < %s AND TIME_RANGE.END_DATE > %s))"
+ TIMESPANTEST_NOEND = "((TIME_RANGE.FLOATING = FALSE AND TIME_RANGE.END_DATE > %s) OR (TIME_RANGE.FLOATING = TRUE AND TIME_RANGE.END_DATE > %s))"
+ TIMESPANTEST_NOSTART = "((TIME_RANGE.FLOATING = FALSE AND TIME_RANGE.START_DATE < %s) OR (TIME_RANGE.FLOATING = TRUE AND TIME_RANGE.START_DATE < %s))"
+ TIMESPANTEST_TAIL_PIECE = " AND TIME_RANGE.CALENDAR_OBJECT_RESOURCE_ID = CALENDAR_OBJECT.RESOURCE_ID AND TIME_RANGE.CALENDAR_RESOURCE_ID = %s"
+ TIMESPANTEST_JOIN_ON_PIECE = "TIME_RANGE.INSTANCE_ID = TRANSPARENCY.TIME_RANGE_INSTANCE_ID AND TRANSPARENCY.USER_ID = %s"
- super(postgresqlgenerator, self).__init__(expr, calendarid, userid)
-
-
def generate(self):
"""
Generate the actual SQL 'where ...' expression from the passed in
expression tree.
-
+
@return: a C{tuple} of (C{str}, C{list}), where the C{str} is the
partial SQL statement, and the C{list} is the list of argument
substitutions to use with the SQL API execute method.
@@ -841,23 +904,61 @@
return select, self.arguments
+ def containsArgument(self, arg):
+ return "%%%s%%" % (arg,)
+
+
+
+class FormatParamStyleMixin(object):
+ """
+ Mixin for overriding methods on sqlgenerator that generate arguments
+ according to format/pyformat rules rather than the base class's 'numeric'
+ rules.
+ """
+
def addArgument(self, arg):
self.arguments.append(arg)
self.substitutions.append("%s")
self.sout.write("%s")
+
def setArgument(self, arg):
self.arguments.append(arg)
self.substitutions.append("%s")
+
def frontArgument(self, arg):
self.arguments.insert(0, arg)
self.substitutions.insert(0, "%s")
- def containsArgument(self, arg):
- return "%%%s%%" % (arg,)
+class postgresqlgenerator(FormatParamStyleMixin, RealSQLBehaviorMixin,
+ sqlgenerator):
+ """
+ Query generator for PostgreSQL indexed searches.
+ """
+
+
+def fixbools(sqltext):
+ return sqltext.replace("TRUE", "1").replace("FALSE", "0")
+
+
+
+class oraclesqlgenerator(RealSQLBehaviorMixin, sqlgenerator):
+ """
+ Query generator for Oracle indexed searches.
+ """
+ TIMESPANTEST = fixbools(RealSQLBehaviorMixin.TIMESPANTEST)
+ TIMESPANTEST_NOEND = fixbools(RealSQLBehaviorMixin.TIMESPANTEST_NOEND)
+ TIMESPANTEST_NOSTART = fixbools(RealSQLBehaviorMixin.TIMESPANTEST_NOSTART)
+ TIMESPANTEST_TAIL_PIECE = fixbools(
+ RealSQLBehaviorMixin.TIMESPANTEST_TAIL_PIECE)
+ TIMESPANTEST_JOIN_ON_PIECE = fixbools(
+ RealSQLBehaviorMixin.TIMESPANTEST_JOIN_ON_PIECE)
+
+
+
class LegacyIndexHelper(LoggingMixIn, object):
@inlineCallbacks
@@ -906,6 +1007,8 @@
# This is only used with unit tests
self.reserver = DummyUIDReserver(self)
+ _objectSchema = schema.CALENDAR_OBJECT
+
@property
def _txn(self):
return self.calendar._txn
@@ -939,17 +1042,28 @@
returnValue(name)
+ @classproperty
+ def _notExpandedBeyondQuery(self):
+ """
+ DAL query to satisfy L{PostgresLegacyIndexEmulator.notExpandedBeyond}.
+ """
+ co = schema.CALENDAR_OBJECT
+ return Select([co.RESOURCE_NAME], From=co,
+ Where=(co.RECURRANCE_MAX < Parameter("minDate"))
+ .And(co.CALENDAR_RESOURCE_ID == Parameter("resourceID")))
+
+
@inlineCallbacks
def notExpandedBeyond(self, minDate):
"""
Gives all resources which have not been expanded beyond a given date
in the database. (Unused; see above L{postgresqlgenerator}.
"""
- returnValue([row[0] for row in (yield self._txn.execSQL(
- "select RESOURCE_NAME from CALENDAR_OBJECT "
- "where RECURRANCE_MAX < %s and CALENDAR_RESOURCE_ID = %s",
- [normalizeForIndex(minDate), self.calendar._resourceID]
- ))])
+ returnValue([row[0] for row in (
+ yield self._notExpandedBeyondQuery.on(
+ self._txn, minDate=normalizeForIndex(minDate),
+ resourceID=self.calendar._resourceID))]
+ )
@inlineCallbacks
@@ -971,7 +1085,8 @@
# Actually expand recurrence max
for name in names:
- self.log_info("Search falls outside range of index for %s %s" % (name, minDate))
+ self.log_info("Search falls outside range of index for %s %s" %
+ (name, minDate))
yield self.reExpandResource(name, minDate)
@@ -979,20 +1094,29 @@
def indexedSearch(self, filter, useruid='', fbtype=False):
"""
Finds resources matching the given qualifiers.
+
@param filter: the L{Filter} for the calendar-query to execute.
@return: a L{Deferred} which fires with an iterable of tuples for each
resource matching the given C{qualifiers}. The tuples are C{(name,
uid, type)}, where C{name} is the resource name, C{uid} is the
- resource UID, and C{type} is the resource iCalendar component
- type.
+ resource UID, and C{type} is the resource iCalendar component type.
"""
+ # Detect which style of parameter-generation we're using. Naming is a
+ # little off here, because the reason we're using the numeric one is
+ # that it happens to be used by the oracle binding that we're using,
+ # whereas the postgres binding happens to use the 'pyformat' (e.g. %s)
+ # parameter style.
+ if self.calendar._txn.paramstyle == 'numeric':
+ generator = oraclesqlgenerator
+ else:
+ generator = postgresqlgenerator
# Make sure we have a proper Filter element and get the partial SQL
# statement to use.
if isinstance(filter, calendarqueryfilter.Filter):
qualifiers = calendarquery.sqlcalendarquery(
filter, self.calendar._resourceID, useruid,
- generator=postgresqlgenerator
+ generator=generator
)
if qualifiers is not None:
# Determine how far we need to extend the current expansion of
@@ -1014,13 +1138,7 @@
# Perform the search
if qualifiers is None:
- rowiter = yield self._txn.execSQL(
- """
- select RESOURCE_NAME, ICALENDAR_UID, ICALENDAR_TYPE
- from CALENDAR_OBJECT where CALENDAR_RESOURCE_ID = %s
- """,
- [self.calendar._resourceID],
- )
+ rowiter = yield self.bruteForceSearch()
else:
if fbtype:
# For a free-busy time-range query we return all instances
@@ -1064,28 +1182,49 @@
returnValue(results)
- def bruteForceSearch(self):
- return self._txn.execSQL(
- "select RESOURCE_NAME, ICALENDAR_UID, ICALENDAR_TYPE from "
- "CALENDAR_OBJECT where CALENDAR_RESOURCE_ID = %s",
- [self.calendar._resourceID]
+ @classproperty
+ def _bruteForceQuery(cls):
+ """
+ DAL query for all C{CALENDAR_OBJECT} rows in the calendar represented by
+ this index.
+ """
+ obj = cls._objectSchema
+ return Select(
+ [obj.RESOURCE_NAME, obj.ICALENDAR_UID, obj.ICALENDAR_TYPE],
+ From=obj, Where=obj.PARENT_RESOURCE_ID == Parameter("resourceID")
)
+ def bruteForceSearch(self):
+ return self._bruteForceQuery.on(
+ self._txn, resourceID=self.resource._resourceID)
+
+
@inlineCallbacks
def resourcesExist(self, names):
returnValue(list(set(names).intersection(
set((yield self.calendar.listCalendarObjects())))))
+ @classproperty
+ def _resourceExistsQuery(cls):
+ """
+ DAL query to determine whether a calendar object exists in the
+ collection represented by this index.
+ """
+ obj = cls._objectSchema
+ return Select(
+ [obj.RESOURCE_NAME], From=obj,
+ Where=(obj.RESOURCE_NAME == Parameter("name"))
+ .And(obj.PARENT_RESOURCE_ID == Parameter("resourceID"))
+ )
+
+
@inlineCallbacks
def resourceExists(self, name):
returnValue((bool(
- (yield self._txn.execSQL(
- "select RESOURCE_NAME from CALENDAR_OBJECT where "
- "RESOURCE_NAME = %s and CALENDAR_RESOURCE_ID = %s",
- [name, self.calendar._resourceID]
- ))
+ (yield self._resourceExistsQuery.on(
+ self._txn, name=name, resourceID=self.resource._resourceID))
)))
@@ -1112,10 +1251,9 @@
# CARDDAV
-class postgresqladbkgenerator(sqlgenerator):
+class oraclesqladbkgenerator(sqlgenerator):
"""
- Query generator for postgreSQL indexed searches. (Currently unused: work
- in progress.)
+ Query generator for Oracle indexed searches.
"""
ISOP = " = "
@@ -1124,18 +1262,17 @@
FIELDS = {
"UID": "ADDRESSBOOK_OBJECT.VCARD_UID",
}
+ RESOURCEDB = "ADDRESSBOOK_OBJECT"
- def __init__(self, expr, addressbookid):
- self.RESOURCEDB = "ADDRESSBOOK_OBJECT"
+ def containsArgument(self, arg):
+ return "%%%s%%" % (arg,)
- super(postgresqladbkgenerator, self).__init__(expr, addressbookid)
-
def generate(self):
"""
Generate the actual SQL 'where ...' expression from the passed in
expression tree.
-
+
@return: a C{tuple} of (C{str}, C{list}), where the C{str} is the
partial SQL statement, and the C{list} is the list of argument
substitutions to use with the SQL API execute method.
@@ -1159,29 +1296,24 @@
return select, self.arguments
- def addArgument(self, arg):
- self.arguments.append(arg)
- self.substitutions.append("%s")
- self.sout.write("%s")
- def setArgument(self, arg):
- self.arguments.append(arg)
- self.substitutions.append("%s")
+class postgresqladbkgenerator(FormatParamStyleMixin, oraclesqladbkgenerator):
+ """
+ Query generator for PostgreSQL indexed searches. Inherit 'real' database
+ behavior from L{oracleadbkgenerator}, and %s-style formatting from
+ L{FormatParamStyleMixin}.
+ """
- def frontArgument(self, arg):
- self.arguments.insert(0, arg)
- self.substitutions.insert(0, "%s")
- def containsArgument(self, arg):
- return "%%%s%%" % (arg,)
-
class PostgresLegacyABIndexEmulator(LegacyIndexHelper):
"""
Emulator for L{twistedcaldv.index.Index} and
L{twistedcaldv.index.IndexSchedule}.
"""
+ _objectSchema = schema.ADDRESSBOOK_OBJECT
+
def __init__(self, addressbook):
self.resource = self.addressbook = addressbook
if (
@@ -1234,10 +1366,14 @@
C{name} is the resource name, C{uid} is the resource UID, and
C{type} is the resource iCalendar component type.x
"""
-
+ if self.addressbook._txn.paramstyle == 'numeric':
+ generator = oraclesqladbkgenerator
+ else:
+ generator = postgresqladbkgenerator
# Make sure we have a proper Filter element and get the partial SQL statement to use.
if isinstance(filter, carddavxml.Filter):
- qualifiers = addressbookquery.sqladdressbookquery(filter, self.addressbook._resourceID, generator=postgresqladbkgenerator)
+ qualifiers = addressbookquery.sqladdressbookquery(
+ filter, self.addressbook._resourceID, generator=generator)
else:
qualifiers = None
if qualifiers is not None:
@@ -1247,10 +1383,13 @@
qualifiers[1]
)
else:
- rowiter = yield self._txn.execSQL(
- "select RESOURCE_NAME, VCARD_UID from ADDRESSBOOK_OBJECT where ADDRESSBOOK_RESOURCE_ID = %s",
- [self.addressbook._resourceID, ],
- )
+ rowiter = yield Select(
+ [schema.ADDRESSBOOK_OBJECT.RESOURCE_NAME,
+ schema.ADDRESSBOOK_OBJECT.VCARD_UID],
+ From=schema.ADDRESSBOOK_OBJECT,
+ Where=schema.ADDRESSBOOK_OBJECT.ADDRESSBOOK_RESOURCE_ID ==
+ self.addressbook._resourceID
+ ).on(self.addressbook._txn)
returnValue(list(rowiter))
@@ -1263,26 +1402,8 @@
raise IndexedSearchException()
- def bruteForceSearch(self):
- return self._txn.execSQL(
- "select RESOURCE_NAME, VCARD_UID from "
- "ADDRESSBOOK_OBJECT where ADDRESSBOOK_RESOURCE_ID = %s",
- [self.addressbook._resourceID]
- )
-
-
@inlineCallbacks
def resourcesExist(self, names):
returnValue(list(set(names).intersection(
set((yield self.addressbook.listAddressbookObjects())))))
-
- @inlineCallbacks
- def resourceExists(self, name):
- returnValue(bool(
- (yield self._txn.execSQL(
- "select RESOURCE_NAME from ADDRESSBOOK_OBJECT where "
- "RESOURCE_NAME = %s and ADDRESSBOOK_RESOURCE_ID = %s",
- [name, self.addressbook._resourceID]
- ))
- ))
Modified: CalendarServer/trunk/txdav/common/datastore/sql_tables.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_tables.py 2011-03-08 03:08:14 UTC (rev 7155)
+++ CalendarServer/trunk/txdav/common/datastore/sql_tables.py 2011-03-08 03:32:37 UTC (rev 7156)
@@ -21,6 +21,8 @@
from twisted.python.modules import getModule
from twext.enterprise.dal.syntax import SchemaSyntax
+from twext.enterprise.dal.model import NO_DEFAULT
+from twext.enterprise.dal.model import Sequence, ProcedureCall
from twext.enterprise.dal.parseschema import schemaFromPath
@@ -195,3 +197,72 @@
REV=ADDRESSBOOK_OBJECT_REVISIONS_TABLE,
BIND=ADDRESSBOOK_BIND_TABLE)
+
+
+def _translateSchema(out):
+ """
+ When run as a script, translate the schema to another dialect. Currently
+ only postgres and oracle are supported, and native format is postgres, so
+ emit in oracle format.
+ """
+ for sequence in schema.model.sequences:
+ out.write('drop sequence %s; create sequence %s;\n' % (
+ sequence.name, sequence.name))
+ for table in schema:
+ # The only table name which actually exceeds the length limit right now
+ # is CALENDAR_OBJECT_ATTACHMENTS_MODE, which isn't actually _used_
+ # anywhere, so we can fake it for now.
+ out.write('drop table %s; create table %s (\n' % (
+ table.model.name[:30], table.model.name[:30],))
+ first = True
+ for column in table:
+ if first:
+ first = False
+ else:
+ out.write(",\n")
+ typeName = column.model.type.name
+ if typeName == 'text':
+ typeName = 'clob'
+ if typeName == 'boolean':
+ typeName = 'integer'
+ out.write(' "%s" %s' % (column.model.name, typeName))
+ if column.model.type.length:
+ out.write("(%s)" % (column.model.type.length,))
+ if column.model is table.model.primaryKey:
+ out.write(' primary key')
+ default = column.model.default
+ if default is not NO_DEFAULT:
+ # Can't do default sequence types in Oracle, so don't bother.
+ if not isinstance(default, Sequence):
+ out.write(' default')
+ if default is None:
+ out.write(' null')
+ elif isinstance(default, ProcedureCall):
+ # Cheating, because there are currently no other
+ # functions being used.
+ out.write(" CURRENT_TIMESTAMP at time zone 'UTC'")
+ else:
+ if default is True:
+ default = 1
+ elif default is False:
+ default = 0
+ out.write(" " + repr(default))
+ if not column.model.canBeNull():
+ out.write(' not null')
+ if set([column.model]) in list(table.model.uniques()):
+ out.write(' unique')
+ if column.model.references is not None:
+ out.write(" references %s" % (column.model.references.name,))
+ if column.model.cascade:
+ out.write(" on delete cascade")
+
+ out.write('\n);\n\n')
+
+
+
+if __name__ == '__main__':
+ import sys
+ _translateSchema(sys.stdout)
+
+
+
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20110307/4cdb941d/attachment-0001.html>
More information about the calendarserver-changes
mailing list