[CalendarServer-changes] [6101] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Tue Aug 17 18:44:53 PDT 2010
Revision: 6101
http://trac.macosforge.org/projects/calendarserver/changeset/6101
Author: cdaboo at apple.com
Date: 2010-08-17 18:44:51 -0700 (Tue, 17 Aug 2010)
Log Message:
-----------
Fix calendar data indexing and querying. Also tweak the SQL schema to use integer for
the sequence columns.
Modified Paths:
--------------
CalendarServer/trunk/twistedcaldav/index.py
CalendarServer/trunk/twistedcaldav/method/report_common.py
CalendarServer/trunk/twistedcaldav/query/calendarquery.py
CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py
CalendarServer/trunk/txcaldav/calendarstore/postgres.py
CalendarServer/trunk/txcaldav/calendarstore/postgres_schema_v1.sql
Modified: CalendarServer/trunk/twistedcaldav/index.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/index.py 2010-08-18 00:35:07 UTC (rev 6100)
+++ CalendarServer/trunk/twistedcaldav/index.py 2010-08-18 01:44:51 UTC (rev 6101)
@@ -310,7 +310,16 @@
# 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)
+ 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,
+ )
+ else:
+ dbuseruid = ""
+
+ qualifiers = calendarquery.sqlcalendarquery(filter, None, dbuseruid)
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
@@ -343,7 +352,7 @@
# 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" +
- qualifiers[0] % (dbuseruid if dbuseruid else "",),
+ qualifiers[0],
*qualifiers[1]
)
else:
Modified: CalendarServer/trunk/twistedcaldav/method/report_common.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/method/report_common.py 2010-08-18 00:35:07 UTC (rev 6100)
+++ CalendarServer/trunk/twistedcaldav/method/report_common.py 2010-08-18 01:44:51 UTC (rev 6101)
@@ -499,12 +499,12 @@
continue
# Apply a timezone to any floating times
- fbstart = datetime.datetime.strptime(start[:-6], "%Y-%m-%d %H:%M:%S")
+ fbstart = datetime.datetime.strptime(start[:19], "%Y-%m-%d %H:%M:%S")
if float == 'Y':
fbstart = fbstart.replace(tzinfo=tzinfo)
else:
fbstart = fbstart.replace(tzinfo=utc)
- fbend =datetime.datetime.strptime(end[:-6], "%Y-%m-%d %H:%M:%S")
+ fbend =datetime.datetime.strptime(end[:19], "%Y-%m-%d %H:%M:%S")
if float == 'Y':
fbend = fbend.replace(tzinfo=tzinfo)
else:
Modified: CalendarServer/trunk/twistedcaldav/query/calendarquery.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/query/calendarquery.py 2010-08-18 00:35:07 UTC (rev 6100)
+++ CalendarServer/trunk/twistedcaldav/query/calendarquery.py 2010-08-18 01:44:51 UTC (rev 6101)
@@ -31,14 +31,7 @@
# SQL Index column (field) names
-FIELD_TYPE = "RESOURCE.TYPE"
-FIELD_UID = "RESOURCE.UID"
-#FIELD_RECURRENCE_ID = "RESOURCE.RECURRENCE-ID"
-#FIELD_SUMMARY = "RESOURCE.SUMMARY"
-#FIELD_DESCRIPTION = "RESOURCE.DESCRIPTION"
-#FIELD_LOCATION = "RESOURCE.LOCATION"
-
-def calendarquery(filter):
+def calendarquery(filter, fields):
"""
Convert the supplied calendar-query into an expression tree.
@@ -59,11 +52,11 @@
for _ignore in [x for x in vcalfilter.filters if not isinstance(x, calendarqueryfilter.ComponentFilter)]:
raise ValueError
- return compfilterListExpression(vcalfilter.filters)
+ return compfilterListExpression(vcalfilter.filters, fields)
else:
return expression.allExpression()
-def compfilterListExpression(compfilters):
+def compfilterListExpression(compfilters, fields):
"""
Create an expression for a list of comp-filter elements.
@@ -72,11 +65,11 @@
"""
if len(compfilters) == 1:
- return compfilterExpression(compfilters[0])
+ return compfilterExpression(compfilters[0], fields)
else:
- return expression.orExpression([compfilterExpression(c) for c in compfilters])
+ return expression.orExpression([compfilterExpression(c, fields) for c in compfilters])
-def compfilterExpression(compfilter):
+def compfilterExpression(compfilter, fields):
"""
Create an expression for a single comp-filter element.
@@ -87,13 +80,13 @@
# Handle is-not-defined case
if not compfilter.defined:
# Test for TYPE != <<component-type name>>
- return expression.isnotExpression(FIELD_TYPE, compfilter.filter_name, True)
+ return expression.isnotExpression(fields["TYPE"], compfilter.filter_name, True)
expressions = []
if isinstance(compfilter.filter_name, str):
- expressions.append(expression.isExpression(FIELD_TYPE, compfilter.filter_name, True))
+ expressions.append(expression.isExpression(fields["TYPE"], compfilter.filter_name, True))
else:
- expressions.append(expression.inExpression(FIELD_TYPE, compfilter.filter_name, True))
+ expressions.append(expression.inExpression(fields["TYPE"], compfilter.filter_name, True))
# Handle time-range
if compfilter.qualifier and isinstance(compfilter.qualifier, calendarqueryfilter.TimeRange):
@@ -103,7 +96,7 @@
# Handle properties - we can only do UID right now
props = []
for p in [x for x in compfilter.filters if isinstance(x, calendarqueryfilter.PropertyFilter)]:
- props.append(propfilterExpression(p))
+ props.append(propfilterExpression(p, fields))
if len(props) > 1:
propsExpression = expression.orExpression[props]
elif len(props) == 1:
@@ -133,7 +126,7 @@
# Now build return expression
return expression.andExpression(expressions)
-def propfilterExpression(propfilter):
+def propfilterExpression(propfilter, fields):
"""
Create an expression for a single prop-filter element.
@@ -148,7 +141,7 @@
# Handle is-not-defined case
if not propfilter.defined:
# Test for <<field>> != "*"
- return expression.isExpression(FIELD_UID, "", True)
+ return expression.isExpression(fields["UID"], "", True)
# Handle time-range - we cannot do this with our Index right now
if propfilter.qualifier and isinstance(propfilter.qualifier, calendarqueryfilter.TimeRange):
@@ -158,9 +151,9 @@
tm = None
if propfilter.qualifier and isinstance(propfilter.qualifier, calendarqueryfilter.TextMatch):
if propfilter.qualifier.negate:
- tm = expression.notcontainsExpression(propfilter.filter_name, propfilter.qualifier.text, propfilter.qualifier.caseless)
+ tm = expression.notcontainsExpression(fields[propfilter.filter_name], propfilter.qualifier.text, propfilter.qualifier.caseless)
else:
- tm = expression.containsExpression(propfilter.filter_name, propfilter.qualifier.text, propfilter.qualifier.caseless)
+ tm = expression.containsExpression(fields[propfilter.filter_name], propfilter.qualifier.text, propfilter.qualifier.caseless)
# Handle embedded parameters - we do not right now as our Index does not handle them
params = []
@@ -210,7 +203,7 @@
str(endfloat) if endfloat else None,
)
-def sqlcalendarquery(filter):
+def sqlcalendarquery(filter, calendarid=None, userid=None, generator=sqlgenerator.sqlgenerator):
"""
Convert the supplied calendar-query into a oartial SQL statement.
@@ -220,8 +213,8 @@
Or return C{None} if it is not possible to create an SQL query to fully match the calendar-query.
"""
try:
- expression = calendarquery(filter)
- sql = sqlgenerator.sqlgenerator(expression)
+ expression = calendarquery(filter, generator.FIELDS)
+ sql = generator(expression, calendarid, userid)
return sql.generate()
except ValueError:
return None
Modified: CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py 2010-08-18 00:35:07 UTC (rev 6100)
+++ CalendarServer/trunk/twistedcaldav/query/sqlgenerator.py 2010-08-18 01:44:51 UTC (rev 6101)
@@ -46,14 +46,21 @@
INOP = " IN "
NOTINOP = " NOT IN "
+ FIELDS = {
+ "TYPE": "RESOURCE.TYPE",
+ "UID": "RESOURCE.UID",
+ }
+
TIMESPANTEST = "((TIMESPAN.FLOAT == 'N' AND TIMESPAN.START < %s AND TIMESPAN.END > %s) OR (TIMESPAN.FLOAT == 'Y' AND TIMESPAN.START < %s AND TIMESPAN.END > %s))"
TIMESPANTEST_NOEND = "((TIMESPAN.FLOAT == 'N' AND TIMESPAN.END > %s) OR (TIMESPAN.FLOAT == 'Y' AND TIMESPAN.END > %s))"
TIMESPANTEST_NOSTART = "((TIMESPAN.FLOAT == 'N' AND TIMESPAN.START < %s) OR (TIMESPAN.FLOAT == 'Y' AND TIMESPAN.START < %s))"
TIMESPANTEST_TAIL_PIECE = " AND TIMESPAN.RESOURCEID == RESOURCE.RESOURCEID"
- TIMESPANTEST_JOIN_ON_PIECE = "TIMESPAN.INSTANCEID == TRANSPARENCY.INSTANCEID AND TRANSPARENCY.PERUSERID == '%s'"
+ TIMESPANTEST_JOIN_ON_PIECE = "TIMESPAN.INSTANCEID == TRANSPARENCY.INSTANCEID AND TRANSPARENCY.PERUSERID == %s"
- def __init__(self, expr):
+ def __init__(self, expr, calendarid, userid):
self.expression = expr
+ self.calendarid = calendarid
+ self.userid = userid if userid else ""
self.usedtimespan = False
def generate(self):
@@ -67,21 +74,27 @@
# Init state
self.sout = StringIO.StringIO()
self.arguments = []
+ self.substitutions = []
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)
+ self.TIMESPANTEST_JOIN_ON_PIECE
+ )
select += self.sout.getvalue()
+
+ select = select % tuple(self.substitutions)
+
return select, self.arguments
def generateExpression(self, expr):
@@ -99,6 +112,7 @@
# Wipe out the ' where ...' clause so everything is matched
self.sout.truncate(0)
self.arguments = []
+ self.substitutions = []
self.usedtimespan = False
# NOT
@@ -129,19 +143,22 @@
# time-range
elif isinstance(expr, expression.timerangeExpression):
if expr.start and expr.end:
- arg1 = self.setArgument(expr.end)
- arg2 = self.setArgument(expr.start)
- arg3 = self.setArgument(expr.endfloat)
- arg4 = self.setArgument(expr.startfloat)
- test = self.TIMESPANTEST % (arg1, arg2, arg3, arg4)
+ self.setArgument(expr.end)
+ self.setArgument(expr.start)
+ self.setArgument(expr.endfloat)
+ self.setArgument(expr.startfloat)
+ test = self.TIMESPANTEST
elif expr.start and expr.end is None:
- arg1 = self.setArgument(expr.start)
- arg2 = self.setArgument(expr.startfloat)
- test = self.TIMESPANTEST_NOEND % (arg1, arg2)
+ self.setArgument(expr.start)
+ self.setArgument(expr.startfloat)
+ test = self.TIMESPANTEST_NOEND
elif not expr.start and expr.end:
- arg1 = self.setArgument(expr.end)
- arg2 = self.setArgument(expr.endfloat)
- test = self.TIMESPANTEST_NOSTART % (arg1, arg2)
+ self.setArgument(expr.end)
+ 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
@@ -150,13 +167,13 @@
elif isinstance(expr, expression.containsExpression):
self.sout.write(expr.field)
self.sout.write(self.CONTAINSOP)
- self.addArgument(expr.text)
+ self.addArgument(self.containsArgument(expr.text))
# NOT CONTAINS
elif isinstance(expr, expression.notcontainsExpression):
self.sout.write(expr.field)
self.sout.write(self.NOTCONTAINSOP)
- self.addArgument(expr.text)
+ self.addArgument(self.containsArgument(expr.text))
# IS
elif isinstance(expr, expression.isExpression):
@@ -194,7 +211,7 @@
def generateSubExpression(self, expression):
"""
- Generate an SQL expression possibly in paranethesis if its a compound expression.
+ Generate an SQL expression possibly in parenthesis if its a compound expression.
@param expression: the L{baseExpression} to write out.
@return: C{True} if the TIMESPAN table is used, C{False} otherwise.
@@ -214,7 +231,8 @@
# Append argument to the list and add the appropriate substitution string to the output stream.
self.arguments.append(arg)
- self.sout.write(":" + str(len(self.arguments)))
+ self.substitutions.append(":" + str(len(self.arguments)))
+ self.sout.write("%s")
def setArgument(self, arg):
"""
@@ -225,9 +243,22 @@
# Append argument to the list and add the appropriate substitution string to the output stream.
self.arguments.append(arg)
- return ":" + str(len(self.arguments))
+ self.substitutions.append(":" + str(len(self.arguments)))
+ def frontArgument(self, arg):
+ """
+
+ @param arg: the C{str} of the argument to add
+ @return: C{str} for argument substitution text
+ """
+
+ # Append argument to the list and add the appropriate substitution string to the output stream.
+ self.arguments.insert(0, arg)
+ self.substitutions.append(":" + str(len(self.arguments)))
+ def containsArgument(self, arg):
+ return "*%s*" % (arg,)
+
if __name__ == "__main__":
e1 = expression.isExpression("TYPE", "VEVENT", False)
Modified: CalendarServer/trunk/txcaldav/calendarstore/postgres.py
===================================================================
--- CalendarServer/trunk/txcaldav/calendarstore/postgres.py 2010-08-18 00:35:07 UTC (rev 6100)
+++ CalendarServer/trunk/txcaldav/calendarstore/postgres.py 2010-08-18 01:44:51 UTC (rev 6101)
@@ -29,19 +29,20 @@
"PostgresAddressBookObject",
]
+import datetime
import StringIO
-from twisted.python import hashlib
from twistedcaldav.sharing import SharedCollectionRecord #@UnusedImport
from inspect import getargspec
from zope.interface.declarations import implements
-from twisted.python.modules import getModule
from twisted.application.service import Service
+from twisted.internet.error import ConnectionLost
from twisted.internet.interfaces import ITransport
-from twisted.internet.error import ConnectionLost
+from twisted.python import hashlib
from twisted.python.failure import Failure
+from twisted.python.modules import getModule
from twext.web2.dav.element.rfc2518 import ResourceType
@@ -66,17 +67,25 @@
from twext.web2.http_headers import MimeType, generateContentType
from twext.web2.dav.element.parser import WebDAVDocument
+from twext.python.log import Logger
from twext.python.vcomponent import VComponent
-from twistedcaldav.vcard import Component as VCard
-from twistedcaldav.sharing import Invite
+
+from twistedcaldav.customxml import NotificationType
+from twistedcaldav.dateops import normalizeForIndex
+from twistedcaldav.index import IndexedSearchException
+from twistedcaldav.instance import InvalidOverriddenInstanceError
from twistedcaldav.notifications import NotificationRecord
+from twistedcaldav.query import calendarqueryfilter, calendarquery
from twistedcaldav.query.sqlgenerator import sqlgenerator
-from twistedcaldav.index import IndexedSearchException
-from twistedcaldav.customxml import NotificationType
+from twistedcaldav.sharing import Invite
+from twistedcaldav.vcard import Component as VCard
+from vobject.icalendar import utc
+
v1_schema = getModule(__name__).filePath.sibling(
"postgres_schema_v1.sql").getContent()
+log = Logger()
# FIXME: these constants are in the schema, and should probably be discovered
# from there somehow.
@@ -93,7 +102,40 @@
_BIND_MODE_WRITE = 2
+#
+# Duration into the future through which recurrences are expanded in the index
+# by default. This is a caching parameter which affects the size of the index;
+# it does not affect search results beyond this period, but it may affect
+# performance of such a search.
+#
+default_future_expansion_duration = datetime.timedelta(days=365*1)
+#
+# Maximum duration into the future through which recurrences are expanded in the
+# index. This is a caching parameter which affects the size of the index; it
+# does not affect search results beyond this period, but it may affect
+# performance of such a search.
+#
+# When a search is performed on a time span that goes beyond that which is
+# expanded in the index, we have to open each resource which may have data in
+# that time period. In order to avoid doing that multiple times, we want to
+# cache those results. However, we don't necessarily want to cache all
+# occurrences into some obscenely far-in-the-future date, so we cap the caching
+# period. Searches beyond this period will always be relatively expensive for
+# resources with occurrences beyond this period.
+#
+maximum_future_expansion_duration = datetime.timedelta(days=365*5)
+
+icalfbtype_to_indexfbtype = {
+ "UNKNOWN" : 0,
+ "FREE" : 1,
+ "BUSY" : 2,
+ "BUSY-UNAVAILABLE": 3,
+ "BUSY-TENTATIVE" : 4,
+}
+indexfbtype_to_icalfbtype = dict([(v, k) for k,v in icalfbtype_to_indexfbtype.iteritems()])
+
+
def _getarg(argname, argspec, args, kw):
"""
Get an argument from some arguments.
@@ -268,18 +310,190 @@
def setComponent(self, component):
validateCalendarComponent(self, self._calendar, component)
- calendarText = str(component)
- self._txn.execSQL(
- "update CALENDAR_OBJECT set ICALENDAR_TEXT = %s "
- "where RESOURCE_ID = %s", [calendarText, self._resourceID]
- )
- self._calendarText = calendarText
+
+ self.updateDatabase(component)
+
self._calendar._updateSyncToken()
if self._calendar._notifier:
self._calendar._home._txn.postCommit(self._calendar._notifier.notify)
+ def updateDatabase(self, component, expand_until=None, reCreate=False, inserting=False):
+ """
+ Update the database tables for the new data being written.
+ @param component: calendar data to store
+ @type component: L{Component}
+ """
+
+ # Decide how far to expand based on the component
+ master = component.masterComponent()
+ if master is None or not component.isRecurring() and not component.isRecurringUnbounded():
+ # When there is no master we have a set of overridden components - index them all.
+ # When there is one instance - index it.
+ # When bounded - index all.
+ expand = datetime.datetime(2100, 1, 1, 0, 0, 0, tzinfo=utc)
+ else:
+ if expand_until:
+ expand = expand_until
+ else:
+ expand = datetime.date.today() + default_future_expansion_duration
+
+ if expand > (datetime.date.today() + maximum_future_expansion_duration):
+ raise IndexedSearchException
+
+ try:
+ instances = component.expandTimeRanges(expand, ignoreInvalidInstances=reCreate)
+ except InvalidOverriddenInstanceError, e:
+ log.err("Invalid instance %s when indexing %s in %s" % (e.rid, self._name, self.resource,))
+ raise
+
+ componentText = str(component)
+ self._calendarText = componentText
+ organizer = component.getOrganizer()
+ if not organizer:
+ organizer = ""
+
+ # CALENDAR_OBJECT table update
+ if inserting:
+ self._resourceID = self._txn.execSQL(
+ """
+ insert into CALENDAR_OBJECT
+ (CALENDAR_RESOURCE_ID, RESOURCE_NAME, ICALENDAR_TEXT, ICALENDAR_UID, ICALENDAR_TYPE, ATTACHMENTS_MODE, ORGANIZER, RECURRANCE_MAX)
+ values
+ (%s, %s, %s, %s, %s, %s, %s, %s)
+ returning RESOURCE_ID
+ """,
+ # FIXME: correct ATTACHMENTS_MODE based on X-APPLE-
+ # DROPBOX
+ [
+ self._calendar._resourceID,
+ self._name,
+ componentText,
+ component.resourceUID(),
+ component.resourceType(),
+ _ATTACHMENTS_MODE_WRITE,
+ organizer,
+ normalizeForIndex(instances.limit) if instances.limit else None,
+ ]
+ )[0][0]
+ else:
+ self._txn.execSQL(
+ """
+ update CALENDAR_OBJECT set
+ (ICALENDAR_TEXT, ICALENDAR_UID, ICALENDAR_TYPE, ATTACHMENTS_MODE, ORGANIZER, RECURRANCE_MAX)
+ =
+ (%s, %s, %s, %s, %s, %s)
+ where RESOURCE_ID = %s
+ """,
+ # should really be filling out more fields: ORGANIZER,
+ # ORGANIZER_OBJECT, a correct ATTACHMENTS_MODE based on X-APPLE-
+ # DROPBOX
+ [
+ componentText,
+ component.resourceUID(),
+ component.resourceType(),
+ _ATTACHMENTS_MODE_WRITE,
+ organizer,
+ normalizeForIndex(instances.limit) if instances.limit else None,
+ self._resourceID
+ ]
+ )
+
+ # Need to wipe the existing time-range for this and rebuild
+ self._txn.execSQL(
+ """
+ delete from TIME_RANGE where CALENDAR_OBJECT_RESOURCE_ID = %s
+ """,
+ [
+ self._resourceID,
+ ],
+ )
+
+
+ # CALENDAR_OBJECT table update
+ for key in instances:
+ instance = instances[key]
+ start = instance.start.replace(tzinfo=utc)
+ end = instance.end.replace(tzinfo=utc)
+ float = instance.start.tzinfo is None
+ transp = instance.component.propertyValue("TRANSP") == "TRANSPARENT"
+ instanceid = self._txn.execSQL(
+ """
+ insert into TIME_RANGE
+ (CALENDAR_RESOURCE_ID, CALENDAR_OBJECT_RESOURCE_ID, FLOATING, START_DATE, END_DATE, FBTYPE, TRANSPARENT)
+ values
+ (%s, %s, %s, %s, %s, %s, %s)
+ returning
+ INSTANCE_ID
+ """,
+ [
+ self._calendar._resourceID,
+ self._resourceID,
+ float,
+ start,
+ end,
+ icalfbtype_to_indexfbtype.get(instance.component.getFBType(), icalfbtype_to_indexfbtype["FREE"]),
+ transp,
+ ],
+ )[0][0]
+ peruserdata = component.perUserTransparency(instance.rid)
+ for useruid, transp in peruserdata:
+ self._txn.execSQL(
+ """
+ insert into TRANSPARENCY
+ (TIME_RANGE_INSTANCE_ID, USER_ID, TRANSPARENT)
+ values
+ (%s, %s, %s)
+ """,
+ [
+ instanceid,
+ useruid,
+ transp,
+ ],
+ )
+
+ # Special - for unbounded recurrence we insert a value for "infinity"
+ # that will allow an open-ended time-range to always match it.
+ if component.isRecurringUnbounded():
+ start = datetime.datetime(2100, 1, 1, 0, 0, 0, tzinfo=utc)
+ end = datetime.datetime(2100, 1, 1, 1, 0, 0, tzinfo=utc)
+ float = False
+ instanceid = self._txn.execSQL(
+ """
+ insert into TIME_RANGE
+ (CALENDAR_RESOURCE_ID, CALENDAR_OBJECT_RESOURCE_ID, FLOATING, START_DATE, END_DATE, FBTYPE, TRANSPARENT)
+ values
+ (%s, %s, %s, %s, %s, %s, %s)
+ returning
+ INSTANCE_ID
+ """,
+ [
+ self._calendar._resourceID,
+ self._resourceID,
+ float,
+ start,
+ end,
+ icalfbtype_to_indexfbtype["UNKNOWN"],
+ True,
+ ],
+ )[0][0]
+ peruserdata = component.perUserTransparency(None)
+ for useruid, transp in peruserdata:
+ self._txn.execSQL(
+ """
+ insert into TRANSPARENCY
+ (TIME_RANGE_INSTANCE_ID, USER_ID, TRANSPARENT)
+ values
+ (%s, %s, %s)
+ """,
+ [
+ instanceid,
+ useruid,
+ transp,
+ ],
+ )
+
def _attachmentPath(self, name):
attachmentRoot = self._calendar._home._txn._store.attachmentsPath
try:
@@ -740,15 +954,24 @@
in progress.)
"""
- def __init__(self, expr, calendar):
+ ISOP = " = "
+ CONTAINSOP = " LIKE "
+ NOTCONTAINSOP = " NOT LIKE "
+ FIELDS = {
+ "TYPE": "CALENDAR_OBJECT.ICALENDAR_TYPE",
+ "UID": "CALENDAR_OBJECT.ICALENDAR_UID",
+ }
+
+ def __init__(self, expr, calendarid, userid):
+ self.RESOURCEDB = "CALENDAR_OBJECT"
self.TIMESPANDB = "TIME_RANGE"
- self.TIMESPANTEST = "((TIME_RANGE.FLOAT == 'N' AND TIME_RANGE.START_DATE < %s AND TIME_RANGE.END_DATE > %s) OR (TIME_RANGE.FLOAT == 'Y' AND TIME_RANGE.START_DATE < %s AND TIME_RANGE.END_DATE > %s))"
- self.TIMESPANTEST_NOEND = "((TIME_RANGE.FLOAT == 'N' AND TIME_RANGE.END_DATE > %s) OR (TIME_RANGE.FLOAT == 'Y' AND TIME_RANGE.END_DATE > %s))"
- self.TIMESPANTEST_NOSTART = "((TIME_RANGE.FLOAT == 'N' AND TIME_RANGE.START_DATE < %s) OR (TIME_RANGE.FLOAT == 'Y' AND TIME_RANGE.START_DATE < %s))"
- self.TIMESPANTEST_TAIL_PIECE = " AND TIME_RANGE.CALENDAR_OBJECT_RESOURCE_ID == CALENDAR_OBJECT.RESOURCE_ID"
+ 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 CALENDAR_OBJECT.CALENDAR_RESOURCE_ID = %s"
+ self.TIMESPANTEST_JOIN_ON_PIECE = "TIME_RANGE.INSTANCE_ID = TRANSPARENCY.TIME_RANGE_INSTANCE_ID AND TRANSPARENCY.USER_ID = %s"
- super(postgresqlgenerator, self).__init__(expr)
- self.calendar = calendar
+ super(postgresqlgenerator, self).__init__(expr, calendarid, userid)
def generate(self):
@@ -764,6 +987,7 @@
# Init state
self.sout = StringIO.StringIO()
self.arguments = []
+ self.substitutions = []
self.usedtimespan = False
# Generate ' where ...' partial statement
@@ -773,23 +997,36 @@
# Prefix with ' from ...' partial statement
select = self.FROM + self.RESOURCEDB
if self.usedtimespan:
- select += ", %s, %s" % (
- self.TIMESPANDB)
+ self.frontArgument(self.userid)
+ select += ", %s LEFT OUTER JOIN %s ON (%s)" % (
+ self.TIMESPANDB,
+ self.TRANSPARENCYDB,
+ self.TIMESPANTEST_JOIN_ON_PIECE
+ )
select += self.sout.getvalue()
+
+ select = select % tuple(self.substitutions)
+
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)
- return "%s"
+ 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 PostgresLegacyIndexEmulator(object):
"""
Emulator for L{twistedcaldv.index.Index} and
@@ -842,18 +1079,85 @@
return self._txn.execSQL(
"select RESOURCE_NAME from CALENDAR_OBJECT "
"where RECURRANCE_MAX < %s and CALENDAR_RESOURCE_ID = %s",
- [minDate, self.calendar._resourceID]
+ [normalizeForIndex(minDate), self.calendar._resourceID]
)
+ def testAndUpdateIndex(self, minDate):
+ # Find out if the index is expanded far enough
+ names = self.notExpandedBeyond(minDate)
+
+ # Actually expand recurrence max
+ for name in names:
+ self.log_info("Search falls outside range of index for %s %s" % (name, minDate))
+ self.reExpandResource(name, minDate)
+
def indexedSearch(self, filter, useruid='', fbtype=False):
"""
- Always raise L{IndexedSearchException}, since these indexes are not
- fully implemented yet.
+ Finds resources matching the given qualifiers.
+ @param filter: the L{Filter} for the calendar-query to execute.
+ @return: 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.x
"""
- raise IndexedSearchException()
+ # 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)
+ 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
+ # year past the start. That should catch bounded recurrences - unbounded
+ # will have been indexed with an "infinite" value always included.
+ maxDate, isStartDate = filter.getmaxtimerange()
+ if maxDate:
+ maxDate = maxDate.date()
+ if isStartDate:
+ maxDate += datetime.timedelta(days=365)
+ self.testAndUpdateIndex(maxDate)
+ else:
+ # We cannot handler this filter in an indexed search
+ raise IndexedSearchException()
+ else:
+ qualifiers = None
+
+ # Perform the search
+ if qualifiers is None:
+ rowiter = self._txn.execSQL(
+ "select RESOURCE_NAME, ICALENDAR_UID, ICALENDAR_TYPE from CALENDAR_OBJECT where CALENDAR_RESOURCE_ID = %s",
+ [self.calendar._resourceID,],
+ )
+ else:
+ if fbtype:
+ # For a free-busy time-range query we return all instances
+ rowiter = self._txn.execSQL(
+ """select DISTINCT
+ CALENDAR_OBJECT.RESOURCE_NAME, CALENDAR_OBJECT.ICALENDAR_UID, CALENDAR_OBJECT.ICALENDAR_TYPE, CALENDAR_OBJECT.ORGANIZER,
+ TIME_RANGE.FLOATING, TIME_RANGE.START_DATE, TIME_RANGE.END_DATE, TIME_RANGE.FBTYPE, TIME_RANGE.TRANSPARENT, TRANSPARENCY.TRANSPARENT""" +
+ qualifiers[0],
+ qualifiers[1]
+ )
+ else:
+ rowiter = self._txn.execSQL(
+ "select DISTINCT CALENDAR_OBJECT.RESOURCE_NAME, CALENDAR_OBJECT.ICALENDAR_UID, CALENDAR_OBJECT.ICALENDAR_TYPE" +
+ qualifiers[0],
+ qualifiers[1]
+ )
+
+ # Check result for missing resources
+
+ for row in rowiter:
+ if fbtype:
+ row = list(row)
+ if row[9]:
+ row[8] = row[9]
+ del row[9]
+ yield row
+
+
def bruteForceSearch(self):
return self._txn.execSQL(
"select RESOURCE_NAME, ICALENDAR_UID, ICALENDAR_TYPE from "
@@ -980,28 +1284,15 @@
if rows:
raise ObjectResourceNameAlreadyExistsError()
- self._updateSyncToken()
-
calendarObject = PostgresCalendarObject(self, name, None)
calendarObject.component = lambda : component
validateCalendarComponent(calendarObject, self, component)
- componentText = str(component)
- self._txn.execSQL(
- """
- insert into CALENDAR_OBJECT
- (CALENDAR_RESOURCE_ID, RESOURCE_NAME, ICALENDAR_TEXT,
- ICALENDAR_UID, ICALENDAR_TYPE, ATTACHMENTS_MODE)
- values
- (%s, %s, %s, %s, %s, %s)
- """,
- # should really be filling out more fields: ORGANIZER,
- # ORGANIZER_OBJECT, a correct ATTACHMENTS_MODE based on X-APPLE-
- # DROPBOX
- [self._resourceID, name, componentText, component.resourceUID(),
- component.resourceType(), _ATTACHMENTS_MODE_WRITE]
- )
+ calendarObject.updateDatabase(component, inserting=True)
+
+ self._updateSyncToken()
+
if self._notifier:
self._home._txn.postCommit(self._notifier.notify)
Modified: CalendarServer/trunk/txcaldav/calendarstore/postgres_schema_v1.sql
===================================================================
--- CalendarServer/trunk/txcaldav/calendarstore/postgres_schema_v1.sql 2010-08-18 00:35:07 UTC (rev 6100)
+++ CalendarServer/trunk/txcaldav/calendarstore/postgres_schema_v1.sql 2010-08-18 01:44:51 UTC (rev 6101)
@@ -10,7 +10,7 @@
-------------------
create table CALENDAR_HOME (
- RESOURCE_ID varchar(255) primary key default nextval('RESOURCE_ID_SEQ'),
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'),
OWNER_UID varchar(255) not null unique
);
@@ -20,7 +20,7 @@
--------------
create table CALENDAR (
- RESOURCE_ID varchar(255) primary key default nextval('RESOURCE_ID_SEQ'),
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'),
SYNC_TOKEN varchar(255)
);
@@ -33,8 +33,8 @@
INVITE_UID varchar(255) not null,
NAME varchar(255) not null,
SENDER_ADDRESS varchar(255) not null,
- HOME_RESOURCE_ID varchar(255) not null,
- RESOURCE_ID varchar(255) not null
+ HOME_RESOURCE_ID integer not null,
+ RESOURCE_ID integer not null
);
@@ -43,19 +43,17 @@
---------------------------
create table NOTIFICATION_HOME (
- RESOURCE_ID varchar(255) primary key default nextval('RESOURCE_ID_SEQ'),
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'),
OWNER_UID varchar(255) not null unique
);
create table NOTIFICATION (
- RESOURCE_ID varchar(255)
- primary key default nextval('RESOURCE_ID_SEQ'),
- NOTIFICATION_HOME_RESOURCE_ID
- varchar(255) not null references NOTIFICATION_HOME,
- NOTIFICATION_UID varchar(255) not null,
- XML_TYPE varchar not null,
- XML_DATA varchar not null
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'),
+ NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME,
+ NOTIFICATION_UID varchar(255) not null,
+ XML_TYPE varchar not null,
+ XML_DATA varchar not null
);
@@ -66,8 +64,8 @@
-- Joins CALENDAR_HOME and CALENDAR
create table CALENDAR_BIND (
- CALENDAR_HOME_RESOURCE_ID varchar(255) not null references CALENDAR_HOME,
- CALENDAR_RESOURCE_ID varchar(255) not null references CALENDAR,
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR,
-- An invitation which hasn't been accepted yet will not yet have a resource
-- name, so this field may be null.
@@ -75,8 +73,8 @@
CALENDAR_RESOURCE_NAME varchar(255),
BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
- SEEN_BY_OWNER bool not null,
- SEEN_BY_SHAREE bool not null,
+ SEEN_BY_OWNER boolean not null,
+ SEEN_BY_SHAREE boolean not null,
MESSAGE text,
primary key(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID),
@@ -86,7 +84,7 @@
-- Enumeration of calendar bind modes
create table CALENDAR_BIND_MODE (
- ID int primary key,
+ ID integer primary key,
DESCRIPTION varchar(16) not null unique
);
@@ -97,7 +95,7 @@
-- Enumeration of statuses
create table CALENDAR_BIND_STATUS (
- ID int primary key,
+ ID integer primary key,
DESCRIPTION varchar(16) not null unique
);
@@ -112,16 +110,16 @@
---------------------
create table CALENDAR_OBJECT (
- RESOURCE_ID varchar(255) primary key default nextval('RESOURCE_ID_SEQ'),
- CALENDAR_RESOURCE_ID varchar(255) not null references CALENDAR,
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'),
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR,
RESOURCE_NAME varchar(255) not null,
ICALENDAR_TEXT text not null,
ICALENDAR_UID varchar(255) not null,
ICALENDAR_TYPE varchar(255) not null,
- ATTACHMENTS_MODE int not null, -- enum CALENDAR_OBJECT_ATTACHMENTS_MODE
+ ATTACHMENTS_MODE integer not null, -- enum CALENDAR_OBJECT_ATTACHMENTS_MODE
ORGANIZER varchar(255),
- ORGANIZER_OBJECT varchar(255) references CALENDAR_OBJECT,
- RECURRANCE_MAX date, -- maximum date that recurrences have been expanded to.
+ ORGANIZER_OBJECT integer references CALENDAR_OBJECT,
+ RECURRANCE_MAX date, -- maximum date that recurrences have been expanded to.
unique(CALENDAR_RESOURCE_ID, RESOURCE_NAME)
@@ -135,7 +133,7 @@
-- Enumeration of attachment modes
create table CALENDAR_OBJECT_ATTACHMENTS_MODE (
- ID int primary key,
+ ID integer primary key,
DESCRIPTION varchar(16) not null unique
);
@@ -143,24 +141,32 @@
insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (1, 'write');
+-----------------
+-- Instance ID --
+-----------------
+
+create sequence INSTANCE_ID_SEQ;
+
+
----------------
-- Time Range --
----------------
create table TIME_RANGE (
- CALENDAR_RESOURCE_ID varchar(255) not null references CALENDAR,
- CALENDAR_OBJECT_RESOURCE_ID varchar(255) not null references CALENDAR_OBJECT,
- FLOATING bool not null,
- START_DATE date not null,
- END_DATE date not null,
- FBTYPE integer not null, -- enum FREE_BUSY_TYPE
- TRANSPARENT bool not null
+ INSTANCE_ID integer primary key default nextval('INSTANCE_ID_SEQ'),
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR,
+ CALENDAR_OBJECT_RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ FLOATING boolean not null,
+ START_DATE timestamp not null,
+ END_DATE timestamp not null,
+ FBTYPE integer not null,
+ TRANSPARENT boolean not null
);
-- Enumeration of free/busy types
create table FREE_BUSY_TYPE (
- ID int primary key,
+ ID integer primary key,
DESCRIPTION varchar(16) not null unique
);
@@ -171,14 +177,25 @@
insert into FREE_BUSY_TYPE values (4, 'busy-tentative' );
+------------------
+-- Transparency --
+------------------
+
+create table TRANSPARENCY (
+ TIME_RANGE_INSTANCE_ID integer not null references TIME_RANGE on delete cascade,
+ USER_ID varchar(255) not null,
+ TRANSPARENT boolean not null
+);
+
+
----------------
-- Attachment --
----------------
create table ATTACHMENT (
- CALENDAR_OBJECT_RESOURCE_ID varchar(255) not null references CALENDAR_OBJECT,
+ CALENDAR_OBJECT_RESOURCE_ID integer not null references CALENDAR_OBJECT,
CONTENT_TYPE varchar(255) not null,
- SIZE int not null,
+ SIZE integer not null,
MD5 char(32) not null,
PATH varchar(1024) not null unique
);
@@ -189,7 +206,7 @@
------------------
create table ITIP_MESSAGE (
- CALENDAR_RESOURCE_ID varchar(255) not null references CALENDAR,
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR,
ICALENDAR_TEXT text not null,
ICALENDAR_UID varchar(255) not null,
MD5 char(32) not null,
@@ -202,7 +219,7 @@
-----------------------
create table RESOURCE_PROPERTY (
- RESOURCE_ID varchar(255) not null, -- foreign key: *.RESOURCE_ID
+ RESOURCE_ID integer not null, -- foreign key: *.RESOURCE_ID
NAME varchar(255) not null,
VALUE text not null, -- FIXME: xml?
VIEWER_UID varchar(255),
@@ -216,7 +233,7 @@
----------------------
create table ADDRESSBOOK_HOME (
- RESOURCE_ID varchar(255) primary key default nextval('RESOURCE_ID_SEQ'),
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'),
OWNER_UID varchar(255) not null unique
);
@@ -226,7 +243,7 @@
-----------------
create table ADDRESSBOOK (
- RESOURCE_ID varchar(255) primary key default nextval('RESOURCE_ID_SEQ'),
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'),
SYNC_TOKEN varchar(255)
);
@@ -238,14 +255,14 @@
-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK
create table ADDRESSBOOK_BIND (
- ADDRESSBOOK_HOME_RESOURCE_ID varchar(255) not null references ADDRESSBOOK_HOME,
- ADDRESSBOOK_RESOURCE_ID varchar(255) not null references ADDRESSBOOK,
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK,
ADDRESSBOOK_RESOURCE_NAME varchar(255) not null,
- BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
- BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
- SEEN_BY_OWNER bool not null,
- SEEN_BY_SHAREE bool not null,
- MESSAGE text, -- FIXME: xml?
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ SEEN_BY_OWNER boolean not null,
+ SEEN_BY_SHAREE boolean not null,
+ MESSAGE text, -- FIXME: xml?
primary key(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_ID),
unique(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME)
@@ -253,12 +270,12 @@
create table ADDRESSBOOK_OBJECT (
- RESOURCE_ID varchar(255) primary key default nextval('RESOURCE_ID_SEQ'),
- ADDRESSBOOK_RESOURCE_ID varchar(255) not null references ADDRESSBOOK,
- RESOURCE_NAME varchar(255) not null,
- VCARD_TEXT text not null,
- VCARD_UID varchar(255) not null,
- VCARD_TYPE varchar(255) not null,
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'),
+ ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK,
+ RESOURCE_NAME varchar(255) not null,
+ VCARD_TEXT text not null,
+ VCARD_UID varchar(255) not null,
+ VCARD_TYPE varchar(255) not null,
unique(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME),
unique(ADDRESSBOOK_RESOURCE_ID, VCARD_UID)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20100817/a2580a32/attachment-0001.html>
More information about the calendarserver-changes
mailing list