def getTimerangeArguments(timerange):
"""
Get start/end and floating start/end (adjusted for timezone offset) values from the
supplied time-range test.
@param timerange: the L{TimeRange} used in the query.
@return: C{tuple} of C{str} for start, end, startfloat, endfloat
"""
# Start/end in UTC
start = timerange.start
end = timerange.end
# Get timezone
tzinfo = timerange.tzinfo
# Now force to floating UTC
startfloat = floatoffset(start, tzinfo) if start else None
endfloat = floatoffset(end, tzinfo) if end else None
return (
pyCalendarToSQLTimestamp(start) if start else None,
pyCalendarToSQLTimestamp(end) if end else None,
pyCalendarToSQLTimestamp(startfloat) if startfloat else None,
pyCalendarToSQLTimestamp(endfloat) if endfloat else None,
)
def getTimerangeArguments(timerange):
"""
Get start/end and floating start/end (adjusted for timezone offset) values from the
supplied time-range test.
@param timerange: the L{TimeRange} used in the query.
@return: C{tuple} of C{str} for start, end, startfloat, endfloat
"""
# Start/end in UTC
start = timerange.start
end = timerange.end
# Get timezone
tzinfo = timerange.tzinfo
# Now force to floating UTC
startfloat = floatoffset(start, tzinfo) if start else None
endfloat = floatoffset(end, tzinfo) if end else None
return (
pyCalendarToSQLTimestamp(start) if start else None,
pyCalendarToSQLTimestamp(end) if end else None,
pyCalendarToSQLTimestamp(startfloat) if startfloat else None,
pyCalendarToSQLTimestamp(endfloat) if endfloat else None,
)
def notExpandedBeyond(self, minDate):
"""
Gives all resources which have not been expanded beyond a given date
in the index
"""
return self._db_values_for_sql(
"select NAME from RESOURCE where RECURRANCE_MAX < :1",
pyCalendarToSQLTimestamp(minDate))
def notExpandedBeyond(self, minDate):
"""
Gives all resources which have not been expanded beyond a given date
in the index
"""
return self._db_values_for_sql(
"select NAME from RESOURCE where RECURRANCE_MAX < :1",
pyCalendarToSQLTimestamp(minDate))
def test_pyCalendarToSQLTimestamp(self):
"""
dateops.pyCalendarToSQLTimestamp
"""
tests = (
(DateTime(2012, 4, 4, 12, 34, 56), datetime(2012, 4, 4, 12, 34, 56, tzinfo=None)),
(DateTime(2012, 12, 31), date(2012, 12, 31)),
)
for pycal, result in tests:
self.assertEqual(pyCalendarToSQLTimestamp(pycal), result)
def test_pyCalendarToSQLTimestamp(self):
"""
dateops.pyCalendarToSQLTimestamp
"""
tests = (
(DateTime(2012, 4, 4, 12, 34, 56), datetime(2012, 4, 4, 12, 34, 56, tzinfo=None)),
(DateTime(2012, 12, 31), date(2012, 12, 31)),
)
for pycal, result in tests:
self.assertEqual(pyCalendarToSQLTimestamp(pycal), result)
def getResourceIDsToPurge(self, home_id, calendar_id, calendar_name):
"""
For the given calendar find which calendar objects are older than the cut-off and return the
resource-ids of those.
@param home_id: resource-id of calendar home
@type home_id: L{int}
@param calendar_id: resource-id of the calendar to check
@type calendar_id: L{int}
@param calendar_name: name of the calendar to check
@type calendar_name: L{str}
"""
log.debug(" Checking calendar: {id} '{name}'", id=calendar_id, name=calendar_name)
purge = set()
txn = self.store.newTransaction(label="Find matching resources")
co = schema.CALENDAR_OBJECT
tr = schema.TIME_RANGE
kwds = {"calendar_id": calendar_id}
rows = (yield Select(
[co.RESOURCE_ID, co.RECURRANCE_MAX, co.RECURRANCE_MIN, Max(tr.END_DATE)],
From=co.join(tr, on=(co.RESOURCE_ID == tr.CALENDAR_OBJECT_RESOURCE_ID)),
Where=(co.CALENDAR_RESOURCE_ID == Parameter("calendar_id")).And(
co.ICALENDAR_TYPE == "VEVENT"
),
GroupBy=(co.RESOURCE_ID, co.RECURRANCE_MAX, co.RECURRANCE_MIN,),
Having=(
(co.RECURRANCE_MAX == None).And(Max(tr.END_DATE) < pyCalendarToSQLTimestamp(self.cutoff))
).Or(
(co.RECURRANCE_MAX != None).And(co.RECURRANCE_MAX < pyCalendarToSQLTimestamp(self.cutoff))
),
).on(txn, **kwds))
log.debug(" Found {len} resources to check", len=len(rows))
for resource_id, recurrence_max, recurrence_min, max_end_date in rows:
recurrence_max = parseSQLDateToPyCalendar(recurrence_max) if recurrence_max else None
recurrence_min = parseSQLDateToPyCalendar(recurrence_min) if recurrence_min else None
max_end_date = parseSQLDateToPyCalendar(max_end_date) if max_end_date else None
# Find events where we know the max(end_date) represents a valid,
# untruncated expansion
if recurrence_min is None or recurrence_min < self.cutoff:
if recurrence_max is None:
# Here we know max_end_date is the fully expand final instance
if max_end_date < self.cutoff:
purge.add(self.PurgeEvent(home_id, calendar_id, resource_id,))
continue
elif recurrence_max > self.cutoff:
# Here we know that there are instances newer than the cut-off
# but they have not yet been indexed out that far
continue
# Manually detect the max_end_date from the actual calendar data
calendar = yield self.getCalendar(txn, resource_id)
if calendar is not None:
if self.checkLastInstance(calendar):
purge.add(self.PurgeEvent(home_id, calendar_id, resource_id,))
yield txn.commit()
log.debug(" Found {len} resources to purge", len=len(purge))
returnValue(purge)
def getResourceIDsToPurge(self, home_id, calendar_id, calendar_name):
"""
For the given calendar find which calendar objects are older than the cut-off and return the
resource-ids of those.
@param home_id: resource-id of calendar home
@type home_id: L{int}
@param calendar_id: resource-id of the calendar to check
@type calendar_id: L{int}
@param calendar_name: name of the calendar to check
@type calendar_name: L{str}
"""
log.debug(" Checking calendar: {} '{}'".format(calendar_id, calendar_name))
purge = set()
txn = self.store.newTransaction(label="Find matching resources")
co = schema.CALENDAR_OBJECT
tr = schema.TIME_RANGE
kwds = {"calendar_id": calendar_id}
rows = (yield Select(
[co.RESOURCE_ID, co.RECURRANCE_MAX, co.RECURRANCE_MIN, Max(tr.END_DATE)],
From=co.join(tr, on=(co.RESOURCE_ID == tr.CALENDAR_OBJECT_RESOURCE_ID)),
Where=(co.CALENDAR_RESOURCE_ID == Parameter("calendar_id")).And(
co.ICALENDAR_TYPE == "VEVENT"
),
GroupBy=(co.RESOURCE_ID, co.RECURRANCE_MAX, co.RECURRANCE_MIN,),
Having=(
(co.RECURRANCE_MAX == None).And(Max(tr.END_DATE) < pyCalendarToSQLTimestamp(self.cutoff))
).Or(
(co.RECURRANCE_MAX != None).And(co.RECURRANCE_MAX < pyCalendarToSQLTimestamp(self.cutoff))
),
).on(txn, **kwds))
log.debug(" Found {} resources to check".format(len(rows)))
for resource_id, recurrence_max, recurrence_min, max_end_date in rows:
recurrence_max = parseSQLDateToPyCalendar(recurrence_max) if recurrence_max else None
recurrence_min = parseSQLDateToPyCalendar(recurrence_min) if recurrence_min else None
max_end_date = parseSQLDateToPyCalendar(max_end_date) if max_end_date else None
# Find events where we know the max(end_date) represents a valid,
# untruncated expansion
if recurrence_min is None or recurrence_min < self.cutoff:
if recurrence_max is None:
# Here we know max_end_date is the fully expand final instance
if max_end_date < self.cutoff:
purge.add(self.PurgeEvent(home_id, calendar_id, resource_id,))
continue
elif recurrence_max > self.cutoff:
# Here we know that there are instances newer than the cut-off
# but they have not yet been indexed out that far
continue
# Manually detect the max_end_date from the actual calendar data
calendar = yield self.getCalendar(txn, resource_id)
if calendar is not None:
if self.checkLastInstance(calendar):
purge.add(self.PurgeEvent(home_id, calendar_id, resource_id,))
yield txn.commit()
log.debug(" Found {} resources to purge".format(len(purge)))
returnValue(purge)
class CalendarIndex(AbstractCalendarIndex):
"""
Calendar index - abstract class for indexer that indexes calendar objects in a collection.
"""
def __init__(self, resource):
"""
@param resource: the L{CalDAVResource} resource to
index.
"""
super(CalendarIndex, self).__init__(resource)
def _db_init_data_tables_base(self, q, uidunique):
"""
Initialise the underlying database tables.
@param q: a database cursor to use.
"""
#
# RESOURCE table is the primary index table
# NAME: Last URI component (eg. <uid>.ics, RESOURCE primary key)
# UID: iCalendar UID (may or may not be unique)
# TYPE: iCalendar component type
# RECURRANCE_MAX: Highest date of recurrence expansion
# ORGANIZER: cu-address of the Organizer of the event
#
q.execute("""
create table RESOURCE (
RESOURCEID integer primary key autoincrement,
NAME text unique,
UID text%s,
TYPE text,
RECURRANCE_MAX date,
ORGANIZER text
)
""" % (" unique" if uidunique else "", ))
#
# TIMESPAN table tracks (expanded) time spans for resources
# NAME: Related resource (RESOURCE foreign key)
# FLOAT: 'Y' if start/end are floating, 'N' otherwise
# START: Start date
# END: End date
# FBTYPE: FBTYPE value:
# '?' - unknown
# 'F' - free
# 'B' - busy
# 'U' - busy-unavailable
# 'T' - busy-tentative
# TRANSPARENT: Y if transparent, N if opaque (default non-per-user value)
#
q.execute("""
create table TIMESPAN (
INSTANCEID integer primary key autoincrement,
RESOURCEID integer,
FLOAT text(1),
START date,
END date,
FBTYPE text(1),
TRANSPARENT text(1)
)
""")
q.execute("""
create index STARTENDFLOAT on TIMESPAN (START, END, FLOAT)
""")
#
# PERUSER table tracks per-user ids
# PERUSERID: autoincrement primary key
# UID: User ID used in calendar data
#
q.execute("""
create table PERUSER (
PERUSERID integer primary key autoincrement,
USERUID text
)
""")
q.execute("""
create index PERUSER_UID on PERUSER (USERUID)
""")
#
# TRANSPARENCY table tracks per-user per-instance transparency
# PERUSERID: user id key
# INSTANCEID: instance id key
# TRANSPARENT: Y if transparent, N if opaque
#
q.execute("""
create table TRANSPARENCY (
PERUSERID integer,
INSTANCEID integer,
TRANSPARENT text(1)
)
""")
#
# REVISIONS table tracks changes
# NAME: Last URI component (eg. <uid>.ics, RESOURCE primary key)
# REVISION: revision number
# WASDELETED: Y if revision deleted, N if added or changed
#
q.execute("""
create table REVISION_SEQUENCE (
REVISION integer
)
""")
q.execute("""
insert into REVISION_SEQUENCE (REVISION) values (0)
""")
q.execute("""
create table REVISIONS (
NAME text unique,
REVISION integer,
DELETED text(1)
)
""")
q.execute("""
create index REVISION on REVISIONS (REVISION)
""")
if uidunique:
#
# RESERVED table tracks reserved UIDs
# UID: The UID being reserved
# TIME: When the reservation was made
#
q.execute("""
create table RESERVED (
UID text unique,
TIME date
)
""")
# Cascading triggers to help on delete
q.execute("""
create trigger resourceDelete after delete on RESOURCE
for each row
begin
delete from TIMESPAN where TIMESPAN.RESOURCEID = OLD.RESOURCEID;
end
""")
q.execute("""
create trigger timespanDelete after delete on TIMESPAN
for each row
begin
delete from TRANSPARENCY where INSTANCEID = OLD.INSTANCEID;
end
""")
def _db_can_upgrade(self, old_version):
"""
Can we do an in-place upgrade
"""
# v10 is a big change - no upgrade possible
return False
def _db_upgrade_data_tables(self, q, old_version):
"""
Upgrade the data from an older version of the DB.
"""
# v10 is a big change - no upgrade possible
pass
def notExpandedBeyond(self, minDate):
"""
Gives all resources which have not been expanded beyond a given date
in the index
"""
return self._db_values_for_sql(
"select NAME from RESOURCE where RECURRANCE_MAX < :1",
pyCalendarToSQLTimestamp(minDate))
def reExpandResource(self, name, expand_until):
"""
Given a resource name, remove it from the database and re-add it
with a longer expansion.
"""
calendar = self.resource.getChild(name).iCalendar()
self._add_to_db(name,
calendar,
expand_until=expand_until,
reCreate=True)
self._db_commit()
def _add_to_db(self,
name,
calendar,
cursor=None,
expand_until=None,
reCreate=False):
"""
Records the given calendar resource in the index with the given name.
Resource names and UIDs must both be unique; only one resource name may
be associated with any given UID and vice versa.
NB This method does not commit the changes to the db - the caller
MUST take care of that
@param name: the name of the resource to add.
@param calendar: a L{Calendar} object representing the resource
contents.
"""
uid = calendar.resourceUID()
organizer = calendar.getOrganizer()
if not organizer:
organizer = ""
# Decide how far to expand based on the component
doInstanceIndexing = False
master = calendar.masterComponent()
if master is None or not calendar.isRecurring():
# When there is no master we have a set of overridden components - index them all.
# When there is one instance - index it.
expand = DateTime(2100, 1, 1, 0, 0, 0, tzid=Timezone.UTCTimezone)
doInstanceIndexing = True
else:
# If migrating or re-creating or config option for delayed indexing is off, always index
if reCreate or not config.FreeBusyIndexDelayedExpand:
doInstanceIndexing = True
# 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.
expand = (DateTime.getToday() +
Duration(days=config.FreeBusyIndexExpandAheadDays))
if expand_until and expand_until > expand:
expand = expand_until
# 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.
if expand > (DateTime.getToday() +
Duration(days=config.FreeBusyIndexExpandMaxDays)):
raise IndexedSearchException()
# Always do recurrence expansion even if we do not intend to index - we need this to double-check the
# validity of the iCalendar recurrence data.
try:
instances = calendar.expandTimeRanges(
expand, ignoreInvalidInstances=reCreate)
recurrenceLimit = instances.limit
except InvalidOverriddenInstanceError, e:
log.error(
"Invalid instance {rid} when indexing {name} in {rsrc!r}",
rid=e.rid,
name=name,
rsrc=self.resource)
raise
# Now coerce indexing to off if needed
if not doInstanceIndexing:
instances = None
recurrenceLimit = DateTime(1900,
1,
1,
0,
0,
0,
tzid=Timezone.UTCTimezone)
self._delete_from_db(name, uid, False)
# Add RESOURCE item
self._db_execute(
"""
insert into RESOURCE (NAME, UID, TYPE, RECURRANCE_MAX, ORGANIZER)
values (:1, :2, :3, :4, :5)
""", name, uid, calendar.resourceType(),
pyCalendarToSQLTimestamp(recurrenceLimit)
if recurrenceLimit else None, organizer)
resourceid = self.lastrowid
# Get a set of all referenced per-user UIDs and map those to entries already
# in the DB and add new ones as needed
useruids = calendar.allPerUserUIDs()
useruids.add("")
useruidmap = {}
for useruid in useruids:
peruserid = self._db_value_for_sql(
"select PERUSERID from PERUSER where USERUID = :1", useruid)
if peruserid is None:
self._db_execute(
"""
insert into PERUSER (USERUID)
values (:1)
""", useruid)
peruserid = self.lastrowid
useruidmap[useruid] = peruserid
if doInstanceIndexing:
for key in instances:
instance = instances[key]
start = instance.start
end = instance.end
float = 'Y' if instance.start.floating() else 'N'
transp = 'T' if instance.component.propertyValue(
"TRANSP") == "TRANSPARENT" else 'F'
self._db_execute(
"""
insert into TIMESPAN (RESOURCEID, FLOAT, START, END, FBTYPE, TRANSPARENT)
values (:1, :2, :3, :4, :5, :6)
""", resourceid, float, pyCalendarToSQLTimestamp(start),
pyCalendarToSQLTimestamp(end),
icalfbtype_to_indexfbtype.get(
instance.component.getFBType(), 'F'), transp)
instanceid = self.lastrowid
peruserdata = calendar.perUserData(instance.rid)
for useruid, (transp, _ignore_adjusted_start,
_ignore_adjusted_end) in peruserdata:
peruserid = useruidmap[useruid]
self._db_execute(
"""
insert into TRANSPARENCY (PERUSERID, INSTANCEID, TRANSPARENT)
values (:1, :2, :3)
""", peruserid, instanceid, 'T' if transp else 'F')
# Special - for unbounded recurrence we insert a value for "infinity"
# that will allow an open-ended time-range to always match it.
if calendar.isRecurringUnbounded():
start = DateTime(2100,
1,
1,
0,
0,
0,
tzid=Timezone.UTCTimezone)
end = DateTime(2100, 1, 1, 1, 0, 0, tzid=Timezone.UTCTimezone)
float = 'N'
self._db_execute(
"""
insert into TIMESPAN (RESOURCEID, FLOAT, START, END, FBTYPE, TRANSPARENT)
values (:1, :2, :3, :4, :5, :6)
""", resourceid, float, pyCalendarToSQLTimestamp(start),
pyCalendarToSQLTimestamp(end), '?', '?')
instanceid = self.lastrowid
peruserdata = calendar.perUserData(None)
for useruid, (transp, _ignore_adjusted_start,
_ignore_adjusted_end) in peruserdata:
peruserid = useruidmap[useruid]
self._db_execute(
"""
insert into TRANSPARENCY (PERUSERID, INSTANCEID, TRANSPARENT)
values (:1, :2, :3)
""", peruserid, instanceid, 'T' if transp else 'F')
self._db_execute(
"""
insert or replace into REVISIONS (NAME, REVISION, DELETED)
values (:1, :2, :3)
""",
name,
self.bumpRevision(fast=True),
'N',
)