[CalendarServer-changes] [7052] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Fri Feb 18 14:44:46 PST 2011
Revision: 7052
http://trac.macosforge.org/projects/calendarserver/changeset/7052
Author: sagen at apple.com
Date: 2011-02-18 14:44:44 -0800 (Fri, 18 Feb 2011)
Log Message:
-----------
Adds "Having" to DAL syntax and replaces the "events-older-than" SQL with DAL.
Modified Paths:
--------------
CalendarServer/trunk/twext/enterprise/dal/syntax.py
CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py
CalendarServer/trunk/txdav/common/datastore/sql.py
Modified: CalendarServer/trunk/twext/enterprise/dal/syntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/syntax.py 2011-02-18 21:47:13 UTC (rev 7051)
+++ CalendarServer/trunk/twext/enterprise/dal/syntax.py 2011-02-18 22:44:44 UTC (rev 7052)
@@ -490,7 +490,8 @@
"""
def __init__(self, columns=None, Where=None, From=None, OrderBy=None,
- GroupBy=None, Limit=None, ForUpdate=False, Ascending=None):
+ GroupBy=None, Limit=None, ForUpdate=False, Ascending=None,
+ Having=None):
self.From = From
self.Where = Where
if not isinstance(OrderBy, (list, tuple, type(None))):
@@ -500,6 +501,7 @@
GroupBy = [GroupBy]
self.GroupBy = GroupBy
self.Limit = Limit
+ self.Having = Having
if columns is None:
columns = ALL_COLUMNS
else:
@@ -527,31 +529,40 @@
wherestmt = self.Where.subSQL(placeholder, quote, allTables)
stmt.text += quote(" where ")
stmt.append(wherestmt)
- for bywhat, expr in [('group', self.GroupBy),
- ('order', self.OrderBy)]:
- if expr is not None:
- stmt.text += quote(" " + bywhat + " by ")
- fst = True
- for subthing in expr:
- if fst:
- fst = False
- else:
- stmt.text += ', '
- stmt.append(subthing.subSQL(placeholder, quote, allTables))
- if bywhat == 'order':
- if self.Ascending is not None:
- if self.Ascending:
- kw = " asc"
- else:
- kw = " desc"
- stmt.append(SQLFragment(kw))
-
+ if self.GroupBy is not None:
+ stmt.text += quote(" group by ")
+ fst = True
+ for subthing in self.GroupBy:
+ if fst:
+ fst = False
+ else:
+ stmt.text += ', '
+ stmt.append(subthing.subSQL(placeholder, quote, allTables))
+ if self.Having is not None:
+ havingstmt = self.Having.subSQL(placeholder, quote, allTables)
+ stmt.text += quote(" having ")
+ stmt.append(havingstmt)
+ if self.OrderBy is not None:
+ stmt.text += quote(" order by ")
+ fst = True
+ for subthing in self.OrderBy:
+ if fst:
+ fst = False
+ else:
+ stmt.text += ', '
+ stmt.append(subthing.subSQL(placeholder, quote, allTables))
+ if self.Ascending is not None:
+ if self.Ascending:
+ kw = " asc"
+ else:
+ kw = " desc"
+ stmt.append(SQLFragment(kw))
+ if self.ForUpdate:
+ stmt.text += quote(" for update")
if self.Limit is not None:
stmt.text += quote(" limit ")
stmt.append(Constant(self.Limit).subSQL(placeholder, quote,
allTables))
- if self.ForUpdate:
- stmt.text += quote(" for update")
return stmt
Modified: CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py 2011-02-18 21:47:13 UTC (rev 7051)
+++ CalendarServer/trunk/twext/enterprise/dal/test/test_sqlsyntax.py 2011-02-18 22:44:44 UTC (rev 7052)
@@ -596,6 +596,19 @@
"select BAR from FOO limit ?", [123]))
+ def test_having(self):
+ """
+ A L{Select} object with a 'Having' keyword parameter will generate
+ a SQL statement with a 'having' expression.
+ """
+ self.assertEquals(
+ Select([self.schema.FOO.BAR],
+ From=self.schema.FOO,
+ Having=Max(self.schema.FOO.BAZ) < 7).toSQL(),
+ SQLFragment("select BAR from FOO having max(BAZ) < ?", [7])
+ )
+
+
def test_nextSequenceValue(self):
"""
When a sequence is used as a value in an expression, it renders as the
@@ -605,6 +618,3 @@
Insert({self.schema.BOZ.QUX:
self.schema.A_SEQ}).toSQL(),
SQLFragment("insert into BOZ (QUX) values (nextval('A_SEQ'))", []))
-
-
-
Modified: CalendarServer/trunk/txdav/common/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql.py 2011-02-18 21:47:13 UTC (rev 7051)
+++ CalendarServer/trunk/txdav/common/datastore/sql.py 2011-02-18 22:44:44 UTC (rev 7052)
@@ -58,7 +58,7 @@
from txdav.common.inotifications import INotificationCollection, \
INotificationObject
-from twext.enterprise.dal.syntax import Parameter
+from twext.enterprise.dal.syntax import Parameter, Max
from twext.python.clsprop import classproperty
from twext.enterprise.dal.syntax import Select
from twext.enterprise.dal.syntax import Lock
@@ -263,6 +263,43 @@
return self._sqlTxn.abort()
+ def _oldEventsBase(limited):
+ ch = schema.CALENDAR_HOME
+ co = schema.CALENDAR_OBJECT
+ cb = schema.CALENDAR_BIND
+ tr = schema.TIME_RANGE
+ kwds = { }
+ if limited:
+ kwds["Limit"] = Parameter("batchSize")
+ return Select(
+ [
+ ch.OWNER_UID,
+ cb.CALENDAR_RESOURCE_NAME,
+ co.RESOURCE_NAME,
+ Max(tr.END_DATE)
+ ],
+ From=ch.join(co).join(cb).join(tr),
+ Where=(
+ ch.RESOURCE_ID == cb.CALENDAR_HOME_RESOURCE_ID ).And(
+ tr.CALENDAR_OBJECT_RESOURCE_ID == co.RESOURCE_ID ).And(
+ cb.CALENDAR_RESOURCE_ID == tr.CALENDAR_RESOURCE_ID ).And(
+ cb.BIND_MODE == _BIND_MODE_OWN
+ ),
+ GroupBy=(
+ ch.OWNER_UID,
+ cb.CALENDAR_RESOURCE_NAME,
+ co.RESOURCE_NAME
+ ),
+ Having=Max(tr.END_DATE) < Parameter("CutOff"),
+ OrderBy=Max(tr.END_DATE),
+ **kwds
+ )
+
+ _oldEventsLimited = _oldEventsBase(True)
+ _oldEventsUnlimited = _oldEventsBase(False)
+ del _oldEventsBase
+
+
def eventsOlderThan(self, cutoff, batchSize=None):
"""
Return up to the oldest batchSize events which exist completely earlier
@@ -271,39 +308,15 @@
Returns a deferred to a list of (uid, calendarName, eventName, maxDate)
tuples.
"""
-
- query = """
- select
- ch.OWNER_UID,
- cb.CALENDAR_RESOURCE_NAME,
- co.RESOURCE_NAME,
- max(tr.END_DATE)
- from
- TIME_RANGE tr,
- CALENDAR_BIND cb,
- CALENDAR_OBJECT co,
- CALENDAR_HOME ch
- where
- cb.BIND_MODE=%s AND
- cb.CALENDAR_RESOURCE_ID=tr.CALENDAR_RESOURCE_ID AND
- tr.CALENDAR_OBJECT_RESOURCE_ID=co.RESOURCE_ID AND
- ch.RESOURCE_ID=cb.CALENDAR_HOME_RESOURCE_ID
- group by
- ch.OWNER_UID,
- cb.CALENDAR_RESOURCE_NAME,
- co.RESOURCE_NAME
- having
- max(tr.END_DATE) < %s
- order by max(tr.END_DATE)
- """
- args = [_BIND_MODE_OWN, cutoff]
+ kwds = { "CutOff" : cutoff }
if batchSize is not None:
- query += "limit %s"
- args.append(batchSize)
+ kwds["batchSize"] = batchSize
+ query = self._oldEventsLimited
+ else:
+ query = self._oldEventsUnlimited
+ return query.on(self, **kwds)
- return self.execSQL(query, args)
-
@inlineCallbacks
def removeOldEvents(self, cutoff, batchSize=None):
"""
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20110218/33a7ed52/attachment.html>
More information about the calendarserver-changes
mailing list