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 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', )