[CalendarServer-changes] [8220] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Tue Oct 25 10:47:33 PDT 2011
Revision: 8220
http://trac.macosforge.org/projects/calendarserver/changeset/8220
Author: cdaboo at apple.com
Date: 2011-10-25 10:47:32 -0700 (Tue, 25 Oct 2011)
Log Message:
-----------
Fix time-range calendar query so that join condition is applied to all query terms, even those not using
time-range. Failure to do that results in a huge row count for the other terms and very bad performance.
Modified Paths:
--------------
CalendarServer/trunk/twistedcaldav/query/calendarquery.py
CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py
CalendarServer/trunk/twistedcaldav/query/test/test_calendarquery.py
CalendarServer/trunk/txdav/caldav/datastore/index_file.py
CalendarServer/trunk/txdav/caldav/datastore/test/test_index_file.py
CalendarServer/trunk/txdav/common/datastore/sql_legacy.py
Added Paths:
-----------
CalendarServer/trunk/twistedcaldav/query/test/test_addressbookquery.py
Modified: CalendarServer/trunk/twistedcaldav/query/calendarquery.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/query/calendarquery.py 2011-10-25 17:17:05 UTC (rev 8219)
+++ CalendarServer/trunk/twistedcaldav/query/calendarquery.py 2011-10-25 17:47:32 UTC (rev 8220)
@@ -217,18 +217,18 @@
pyCalendarTodatetime(endfloat) if endfloat else None,
)
-def sqlcalendarquery(filter, calendarid=None, userid=None, generator=sqlgenerator.sqlgenerator):
+def sqlcalendarquery(filter, calendarid=None, userid=None, freebusy=False, generator=sqlgenerator.sqlgenerator):
"""
Convert the supplied calendar-query into a oartial SQL statement.
- @param filter: the L{Filter} for thw calendar-query to convert.
+ @param filter: the L{Filter} for the calendar-query to convert.
@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.
Or return C{None} if it is not possible to create an SQL query to fully match the calendar-query.
"""
try:
expression = calendarquery(filter, generator.FIELDS)
- sql = generator(expression, calendarid, userid)
+ sql = generator(expression, calendarid, userid, freebusy)
return sql.generate()
except ValueError:
return None
Modified: CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py 2011-10-25 17:17:05 UTC (rev 8219)
+++ CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py 2011-10-25 17:47:32 UTC (rev 8220)
@@ -61,10 +61,22 @@
TIMESPANTEST_TAIL_PIECE = " AND TIMESPAN.RESOURCEID == RESOURCE.RESOURCEID"
TIMESPANTEST_JOIN_ON_PIECE = "TIMESPAN.INSTANCEID == TRANSPARENCY.INSTANCEID AND TRANSPARENCY.PERUSERID == %s"
- def __init__(self, expr, calendarid, userid):
+ def __init__(self, expr, calendarid, userid, freebusy=False):
+ """
+
+ @param expr: the query expression object model
+ @type expr: L{twistedcaldav.query.calendarqueryfilter.Filter}
+ @param calendarid: resource ID - not used for file-based per-calendar indexes
+ @type calendarid: C{int}
+ @param userid: user for whom query is being done - query will be scoped to that user's privileges and their transparency
+ @type userid: C{str}
+ @param freebusy: whether or not a freebusy query is being done - if it is, additional time range and transparency information is returned
+ @type freebusy: C{bool}
+ """
self.expression = expr
self.calendarid = calendarid
self.userid = userid if userid else ""
+ self.freebusy = freebusy
self.usedtimespan = False
def generate(self):
@@ -82,21 +94,33 @@
self.usedtimespan = False
# Generate ' where ...' partial statement
- self.sout.write(self.WHERE)
self.generateExpression(self.expression)
# Prefix with ' from ...' partial statement
select = self.FROM + self.RESOURCEDB
if self.usedtimespan:
- self.frontArgument(self.userid)
- select += ", %s, %s LEFT OUTER JOIN %s ON (%s)" % (
- self.TIMESPANDB,
- self.PERUSERDB,
- self.TRANSPARENCYDB,
- self.TIMESPANTEST_JOIN_ON_PIECE
- )
+
+ # Free busy needs transparency join
+ if self.freebusy:
+ self.frontArgument(self.userid)
+ select += ", %s LEFT OUTER JOIN %s ON (%s)" % (
+ self.TIMESPANDB,
+ self.TRANSPARENCYDB,
+ self.TIMESPANTEST_JOIN_ON_PIECE
+ )
+ else:
+ select += ", %s" % (
+ self.TIMESPANDB,
+ )
+ select += self.WHERE
+ if self.usedtimespan:
+ select += "("
select += self.sout.getvalue()
-
+ if self.usedtimespan:
+ if self.calendarid:
+ self.setArgument(self.calendarid)
+ select += ")%s" % (self.TIMESPANTEST_TAIL_PIECE,)
+
select = select % tuple(self.substitutions)
return select, self.arguments
@@ -161,9 +185,6 @@
self.setArgument(expr.endfloat)
test = self.TIMESPANTEST_NOSTART
- if self.calendarid:
- self.setArgument(self.calendarid)
- test += self.TIMESPANTEST_TAIL_PIECE
self.sout.write(test)
self.usedtimespan = True
Added: CalendarServer/trunk/twistedcaldav/query/test/test_addressbookquery.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/query/test/test_addressbookquery.py (rev 0)
+++ CalendarServer/trunk/twistedcaldav/query/test/test_addressbookquery.py 2011-10-25 17:47:32 UTC (rev 8220)
@@ -0,0 +1,40 @@
+##
+# Copyright (c) 2011 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.
+##
+
+from twistedcaldav import carddavxml
+from twistedcaldav.query import addressbookqueryfilter
+import twistedcaldav.test.util
+from twistedcaldav.query.addressbookquery import sqladdressbookquery
+
+class Tests(twistedcaldav.test.util.TestCase):
+
+ def test_query(self):
+ """
+ Basic query test - single term.
+ Only UID can be queried via sql.
+ """
+
+ filter = carddavxml.Filter(
+ *[carddavxml.PropertyFilter(
+ carddavxml.TextMatch.fromString("Example"),
+ **{"name":"UID"}
+ )]
+ )
+ filter = addressbookqueryfilter.Filter(filter)
+
+ sql, args = sqladdressbookquery(filter)
+ self.assertTrue(sql.find("UID") != -1)
+ self.assertTrue("*Example*" in args)
Modified: CalendarServer/trunk/twistedcaldav/query/test/test_calendarquery.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/query/test/test_calendarquery.py 2011-10-25 17:17:05 UTC (rev 8219)
+++ CalendarServer/trunk/twistedcaldav/query/test/test_calendarquery.py 2011-10-25 17:47:32 UTC (rev 8220)
@@ -1,5 +1,5 @@
##
-# Copyright (c) 2009-2010 Apple Inc. All rights reserved.
+# Copyright (c) 2009-2011 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.
@@ -18,14 +18,40 @@
from twistedcaldav.query import calendarqueryfilter
import twistedcaldav.test.util
from pycalendar.timezone import PyCalendarTimezone
+from twistedcaldav.query.calendarquery import sqlcalendarquery
class Tests(twistedcaldav.test.util.TestCase):
def test_query(self):
+ """
+ Basic query test - no time range
+ """
filter = caldavxml.Filter(
caldavxml.ComponentFilter(
*[caldavxml.ComponentFilter(
+ **{"name":("VEVENT", "VFREEBUSY", "VAVAILABILITY")}
+ )],
+ **{"name":"VCALENDAR"}
+ )
+ )
+ filter = calendarqueryfilter.Filter(filter)
+ filter.child.settzinfo(PyCalendarTimezone(tzid="America/New_York"))
+
+ sql, args = sqlcalendarquery(filter)
+ self.assertTrue(sql.find("RESOURCE") != -1)
+ self.assertTrue(sql.find("TIMESPAN") == -1)
+ self.assertTrue(sql.find("TRANSPARENCY") == -1)
+ self.assertTrue("VEVENT" in args)
+
+ def test_query_timerange(self):
+ """
+ Basic query test - with time range
+ """
+
+ filter = caldavxml.Filter(
+ caldavxml.ComponentFilter(
+ *[caldavxml.ComponentFilter(
*[caldavxml.TimeRange(**{"start":"20060605T160000Z", "end":"20060605T170000Z"})],
**{"name":("VEVENT", "VFREEBUSY", "VAVAILABILITY")}
)],
@@ -34,4 +60,69 @@
)
filter = calendarqueryfilter.Filter(filter)
filter.child.settzinfo(PyCalendarTimezone(tzid="America/New_York"))
+
+ sql, args = sqlcalendarquery(filter)
+ self.assertTrue(sql.find("RESOURCE") != -1)
+ self.assertTrue(sql.find("TIMESPAN") != -1)
+ self.assertTrue(sql.find("TRANSPARENCY") == -1)
+ self.assertTrue("VEVENT" in args)
+
+ def test_query_not_extended(self):
+ """
+ Query test - two terms not anyof
+ """
+
+ filter = caldavxml.Filter(
+ caldavxml.ComponentFilter(
+ *[
+ caldavxml.ComponentFilter(
+ **{"name":("VEVENT")}
+ ),
+ caldavxml.ComponentFilter(
+ **{"name":("VTODO")}
+ ),
+ ],
+ **{"name":"VCALENDAR"}
+ )
+ )
+ filter = calendarqueryfilter.Filter(filter)
+ filter.child.settzinfo(PyCalendarTimezone(tzid="America/New_York"))
+
+ sql, args = sqlcalendarquery(filter)
+ self.assertTrue(sql.find("RESOURCE") != -1)
+ self.assertTrue(sql.find("TIMESPAN") == -1)
+ self.assertTrue(sql.find("TRANSPARENCY") == -1)
+ self.assertTrue(sql.find(" OR ") == -1)
+ self.assertTrue("VEVENT" in args)
+ self.assertTrue("VTODO" in args)
+
+ def test_query_extended(self):
+ """
+ Extended query test - two terms with anyof
+ """
+
+ filter = caldavxml.Filter(
+ caldavxml.ComponentFilter(
+ *[
+ caldavxml.ComponentFilter(
+ *[caldavxml.TimeRange(**{"start":"20060605T160000Z",})],
+ **{"name":("VEVENT")}
+ ),
+ caldavxml.ComponentFilter(
+ **{"name":("VTODO")}
+ ),
+ ],
+ **{"name":"VCALENDAR", "test":"anyof"}
+ )
+ )
+ filter = calendarqueryfilter.Filter(filter)
+ filter.child.settzinfo(PyCalendarTimezone(tzid="America/New_York"))
+
+ sql, args = sqlcalendarquery(filter)
+ self.assertTrue(sql.find("RESOURCE") != -1)
+ self.assertTrue(sql.find("TIMESPAN") != -1)
+ self.assertTrue(sql.find("TRANSPARENCY") == -1)
+ self.assertTrue(sql.find(" OR ") != -1)
+ self.assertTrue("VEVENT" in args)
+ self.assertTrue("VTODO" in args)
\ No newline at end of file
Modified: CalendarServer/trunk/txdav/caldav/datastore/index_file.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/index_file.py 2011-10-25 17:17:05 UTC (rev 8219)
+++ CalendarServer/trunk/txdav/caldav/datastore/index_file.py 2011-10-25 17:47:32 UTC (rev 8220)
@@ -306,7 +306,7 @@
else:
dbuseruid = ""
- qualifiers = calendarquery.sqlcalendarquery(filter, None, dbuseruid)
+ qualifiers = calendarquery.sqlcalendarquery(filter, None, dbuseruid, fbtype)
if qualifiers is not None:
# Determine how far we need to extend the current expansion of
# events. If we have an open-ended time-range we will expand one
@@ -331,12 +331,6 @@
rowiter = self._db_execute("select NAME, UID, TYPE from RESOURCE")
else:
if fbtype:
- # Lookup the useruid - try the empty (default) one if needed
- dbuseruid = self._db_value_for_sql(
- "select PERUSERID from PERUSER where USERUID == :1",
- useruid,
- )
-
# For a free-busy time-range query we return all instances
rowiter = self._db_execute(
"select DISTINCT RESOURCE.NAME, RESOURCE.UID, RESOURCE.TYPE, RESOURCE.ORGANIZER, TIMESPAN.FLOAT, TIMESPAN.START, TIMESPAN.END, TIMESPAN.FBTYPE, TIMESPAN.TRANSPARENT, TRANSPARENCY.TRANSPARENT" +
Modified: CalendarServer/trunk/txdav/caldav/datastore/test/test_index_file.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/test/test_index_file.py 2011-10-25 17:17:05 UTC (rev 8219)
+++ CalendarServer/trunk/txdav/caldav/datastore/test/test_index_file.py 2011-10-25 17:47:32 UTC (rev 8220)
@@ -319,6 +319,177 @@
@inlineCallbacks
+ def test_index_timerange(self):
+ """
+ A plain (not freebusy) time range test.
+ """
+ data = (
+ (
+ "#1.1 Simple component - busy",
+ "1.1",
+ """BEGIN:VCALENDAR
+VERSION:2.0
+PRODID:-//CALENDARSERVER.ORG//NONSGML Version 1//EN
+BEGIN:VEVENT
+UID:12345-67890-1.1
+DTSTART:20080601T120000Z
+DTEND:20080601T130000Z
+DTSTAMP:20080601T120000Z
+ORGANIZER;CN="User 01":mailto:user1 at example.com
+ATTENDEE:mailto:user1 at example.com
+ATTENDEE:mailto:user2 at example.com
+END:VEVENT
+END:VCALENDAR
+""",
+ "20080601T000000Z", "20080602T000000Z",
+ ),
+ (
+ "#1.2 Simple component - transparent",
+ "1.2",
+ """BEGIN:VCALENDAR
+VERSION:2.0
+PRODID:-//CALENDARSERVER.ORG//NONSGML Version 1//EN
+BEGIN:VEVENT
+UID:12345-67890-1.2
+DTSTART:20080602T120000Z
+DTEND:20080602T130000Z
+DTSTAMP:20080601T120000Z
+ORGANIZER;CN="User 01":mailto:user1 at example.com
+ATTENDEE:mailto:user1 at example.com
+ATTENDEE:mailto:user2 at example.com
+TRANSP:TRANSPARENT
+END:VEVENT
+END:VCALENDAR
+""",
+ "20080602T000000Z", "20080603T000000Z",
+ ),
+ (
+ "#1.3 Simple component - canceled",
+ "1.3",
+ """BEGIN:VCALENDAR
+VERSION:2.0
+PRODID:-//CALENDARSERVER.ORG//NONSGML Version 1//EN
+BEGIN:VEVENT
+UID:12345-67890-1.3
+DTSTART:20080603T120000Z
+DTEND:20080603T130000Z
+DTSTAMP:20080601T120000Z
+ORGANIZER;CN="User 01":mailto:user1 at example.com
+ATTENDEE:mailto:user1 at example.com
+ATTENDEE:mailto:user2 at example.com
+STATUS:CANCELLED
+END:VEVENT
+END:VCALENDAR
+""",
+ "20080603T000000Z", "20080604T000000Z",
+ ),
+ (
+ "#1.4 Simple component - tentative",
+ "1.4",
+ """BEGIN:VCALENDAR
+VERSION:2.0
+PRODID:-//CALENDARSERVER.ORG//NONSGML Version 1//EN
+BEGIN:VEVENT
+UID:12345-67890-1.4
+DTSTART:20080604T120000Z
+DTEND:20080604T130000Z
+DTSTAMP:20080601T120000Z
+ORGANIZER;CN="User 01":mailto:user1 at example.com
+ATTENDEE:mailto:user1 at example.com
+ATTENDEE:mailto:user2 at example.com
+STATUS:TENTATIVE
+END:VEVENT
+END:VCALENDAR
+""",
+ "20080604T000000Z", "20080605T000000Z",
+ ),
+ (
+ "#2.1 Recurring component - busy",
+ "2.1",
+ """BEGIN:VCALENDAR
+VERSION:2.0
+PRODID:-//CALENDARSERVER.ORG//NONSGML Version 1//EN
+BEGIN:VEVENT
+UID:12345-67890-2.1
+DTSTART:20080605T120000Z
+DTEND:20080605T130000Z
+DTSTAMP:20080601T120000Z
+ORGANIZER;CN="User 01":mailto:user1 at example.com
+ATTENDEE:mailto:user1 at example.com
+ATTENDEE:mailto:user2 at example.com
+RRULE:FREQ=DAILY;COUNT=2
+END:VEVENT
+END:VCALENDAR
+""",
+ "20080605T000000Z", "20080607T000000Z",
+ ),
+ (
+ "#2.2 Recurring component - busy",
+ "2.2",
+ """BEGIN:VCALENDAR
+VERSION:2.0
+PRODID:-//CALENDARSERVER.ORG//NONSGML Version 1//EN
+BEGIN:VEVENT
+UID:12345-67890-2.2
+DTSTART:20080607T120000Z
+DTEND:20080607T130000Z
+DTSTAMP:20080601T120000Z
+ORGANIZER;CN="User 01":mailto:user1 at example.com
+ATTENDEE:mailto:user1 at example.com
+ATTENDEE:mailto:user2 at example.com
+RRULE:FREQ=DAILY;COUNT=2
+END:VEVENT
+BEGIN:VEVENT
+UID:12345-67890-2.2
+RECURRENCE-ID:20080608T120000Z
+DTSTART:20080608T140000Z
+DTEND:20080608T150000Z
+DTSTAMP:20080601T120000Z
+ORGANIZER;CN="User 01":mailto:user1 at example.com
+ATTENDEE:mailto:user1 at example.com
+ATTENDEE:mailto:user2 at example.com
+TRANSP:TRANSPARENT
+END:VEVENT
+END:VCALENDAR
+""",
+ "20080607T000000Z", "20080609T000000Z",
+ ),
+ )
+
+ for description, name, calendar_txt, trstart, trend in data:
+ calendar = Component.fromString(calendar_txt)
+
+ f = open(os.path.join(self.indexDirPath.path, name), "w")
+ f.write(calendar_txt)
+ del f
+
+ self.db.addResource(name, calendar)
+ self.assertTrue(self.db.resourceExists(name), msg=description)
+
+ # Create fake filter element to match time-range
+ filter = caldavxml.Filter(
+ caldavxml.ComponentFilter(
+ caldavxml.ComponentFilter(
+ TimeRange(
+ start=trstart,
+ end=trend,
+ ),
+ name=("VEVENT", "VFREEBUSY", "VAVAILABILITY"),
+ ),
+ name="VCALENDAR",
+ )
+ )
+ filter = calendarqueryfilter.Filter(filter)
+
+ resources = yield self.db.indexedSearch(filter)
+ index_results = set()
+ for found_name, _ignore_uid, _ignore_type in resources:
+ index_results.add(found_name)
+
+ self.assertEqual(set((name,)), index_results, msg=description)
+
+
+ @inlineCallbacks
def test_index_timespan(self):
data = (
(
Modified: CalendarServer/trunk/txdav/common/datastore/sql_legacy.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_legacy.py 2011-10-25 17:17:05 UTC (rev 8219)
+++ CalendarServer/trunk/txdav/common/datastore/sql_legacy.py 2011-10-25 17:47:32 UTC (rev 8220)
@@ -933,21 +933,10 @@
hasTimerange = any([_hasTopLevelTimerange(expr) for expr in self.expression.expressions])
if hasTimerange:
- # AND each of the non-timerange expressions
- trexpressions = []
- orexpressions = []
- for expr in self.expression.expressions:
- if _hasTopLevelTimerange(expr):
- trexpressions.append(expr)
- else:
- orexpressions.append(expr)
-
- if orexpressions:
- self.expression.expressions = tuple(trexpressions) + (
- test.andWith(expression.orExpression(orexpressions)),
- )
+ # timerange expression forces a join on calendarid
+ pass
else:
- # AND the whole thing
+ # AND the whole thing with calendarid
self.expression = test.andWith(self.expression)
@@ -963,19 +952,32 @@
self.expression = test.andWith(self.expression)
# Generate ' where ...' partial statement
- self.sout.write(self.WHERE)
self.generateExpression(self.expression)
# Prefix with ' from ...' partial statement
select = self.FROM + self.RESOURCEDB
if self.usedtimespan:
- self.frontArgument(self.userid)
- select += ", %s LEFT OUTER JOIN %s ON (%s)" % (
- self.TIMESPANDB,
- self.TRANSPARENCYDB,
- self.TIMESPANTEST_JOIN_ON_PIECE
- )
+
+ # Free busy needs transparency join
+ if self.freebusy:
+ self.frontArgument(self.userid)
+ select += ", %s LEFT OUTER JOIN %s ON (%s)" % (
+ self.TIMESPANDB,
+ self.TRANSPARENCYDB,
+ self.TIMESPANTEST_JOIN_ON_PIECE
+ )
+ else:
+ select += ", %s" % (
+ self.TIMESPANDB,
+ )
+ select += self.WHERE
+ if self.usedtimespan:
+ select += "("
select += self.sout.getvalue()
+ if self.usedtimespan:
+ if self.calendarid:
+ self.setArgument(self.calendarid)
+ select += ")%s" % (self.TIMESPANTEST_TAIL_PIECE,)
select = select % tuple(self.substitutions)
@@ -1188,7 +1190,7 @@
# statement to use.
if isinstance(filter, calendarqueryfilter.Filter):
qualifiers = calendarquery.sqlcalendarquery(
- filter, self.calendar._resourceID, useruid,
+ filter, self.calendar._resourceID, useruid, fbtype,
generator=generator
)
if qualifiers is not None:
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20111025/2ba25cb1/attachment-0001.html>
More information about the calendarserver-changes
mailing list