[CalendarServer-changes] [15677] CalendarServer/trunk/txdav
source_changes at macosforge.org
source_changes at macosforge.org
Thu Jun 16 12:36:40 PDT 2016
Revision: 15677
http://trac.calendarserver.org//changeset/15677
Author: sagen at apple.com
Date: 2016-06-16 12:36:40 -0700 (Thu, 16 Jun 2016)
Log Message:
-----------
Adds a combined index on (ORIGINAL_COLLECTION, TRASHED) and breaks the single trashForCollection query into 3 that can take advantage of the new index
Modified Paths:
--------------
CalendarServer/trunk/txdav/caldav/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_62_to_63.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_62_to_63.sql
Modified: CalendarServer/trunk/txdav/caldav/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/sql.py 2016-06-15 22:32:47 UTC (rev 15676)
+++ CalendarServer/trunk/txdav/caldav/datastore/sql.py 2016-06-16 19:36:40 UTC (rev 15677)
@@ -5560,7 +5560,12 @@
returnValue(name)
+class UnsupportedQuery(Exception):
+ """
+ Indicates the particular set of query parameters passed are not supported.
+ """
+
class TrashCollection(Calendar):
_childType = _CHILD_TYPE_TRASH
@@ -5574,9 +5579,32 @@
obj = cls._objectSchema
return Select(
[obj.RESOURCE_ID], From=obj,
+ Where=(obj.ORIGINAL_COLLECTION == Parameter("resourceID")),
+ OrderBy=obj.TRASHED,
+ Ascending=False
+ )
+
+
+ @classproperty
+ def _trashForCollectionStartingQuery(cls):
+ obj = cls._objectSchema
+ return Select(
+ [obj.RESOURCE_ID], From=obj,
Where=(
obj.ORIGINAL_COLLECTION == Parameter("resourceID")).And(
- obj.TRASHED >= Parameter("start")).And(
+ obj.TRASHED >= Parameter("start")),
+ OrderBy=obj.TRASHED,
+ Ascending=False
+ )
+
+
+ @classproperty
+ def _trashForCollectionEndingQuery(cls):
+ obj = cls._objectSchema
+ return Select(
+ [obj.RESOURCE_ID], From=obj,
+ Where=(
+ obj.ORIGINAL_COLLECTION == Parameter("resourceID")).And(
obj.TRASHED <= Parameter("end")),
OrderBy=obj.TRASHED,
Ascending=False
@@ -5586,15 +5614,21 @@
@inlineCallbacks
def trashForCollection(self, resourceID, start=None, end=None):
- if start is None:
- start = datetime.datetime(datetime.MINYEAR, 1, 1)
+ if start is None and end is None:
+ results = yield self._trashForCollectionQuery.on(
+ self._txn, resourceID=resourceID
+ )
+ elif start is None and end is not None:
+ results = yield self._trashForCollectionEndingQuery.on(
+ self._txn, resourceID=resourceID, end=end
+ )
+ elif start is not None and end is None:
+ results = yield self._trashForCollectionStartingQuery.on(
+ self._txn, resourceID=resourceID, start=start
+ )
+ else:
+ raise UnsupportedQuery()
- if end is None:
- end = datetime.datetime.utcnow()
-
- results = yield self._trashForCollectionQuery.on(
- self._txn, resourceID=resourceID, start=start, end=end
- )
resources = []
for (objectResourceID,) in results:
resource = yield self.objectResourceWithID(objectResourceID)
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect.sql 2016-06-15 22:32:47 UTC (rev 15676)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect.sql 2016-06-16 19:36:40 UTC (rev 15677)
@@ -750,8 +750,9 @@
"DROPBOX_ID"
);
-create index CALENDAR_OBJECT_ORIGI_a3d15cf2 on CALENDAR_OBJECT (
- "ORIGINAL_COLLECTION"
+create index CALENDAR_OBJECT_ORIGI_53447b73 on CALENDAR_OBJECT (
+ "ORIGINAL_COLLECTION",
+ "TRASHED"
);
create index TIME_RANGE_CALENDAR_R_beb6e7eb on TIME_RANGE (
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql 2016-06-15 22:32:47 UTC (rev 15676)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql 2016-06-16 19:36:40 UTC (rev 15677)
@@ -316,8 +316,8 @@
create index CALENDAR_OBJECT_DROPBOX_ID on
CALENDAR_OBJECT(DROPBOX_ID);
-create index CALENDAR_OBJECT_ORIGINAL_COLLECTION on
- CALENDAR_OBJECT(ORIGINAL_COLLECTION);
+create index CALENDAR_OBJECT_ORIGINAL_COLLECTION_AND_TRASHED on
+ CALENDAR_OBJECT(ORIGINAL_COLLECTION, TRASHED);
-- Enumeration of attachment modes
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_62_to_63.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_62_to_63.sql 2016-06-15 22:32:47 UTC (rev 15676)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_62_to_63.sql 2016-06-16 19:36:40 UTC (rev 15677)
@@ -18,8 +18,9 @@
-- Upgrade database schema from VERSION 62 to 63 --
---------------------------------------------------
-create index CALENDAR_OBJECT_ORIGI_a3d15cf2 on CALENDAR_OBJECT (
- "ORIGINAL_COLLECTION"
+create index CALENDAR_OBJECT_ORIGI_53447b73 on CALENDAR_OBJECT (
+ "ORIGINAL_COLLECTION",
+ "TRASHED"
);
-- update the version
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_62_to_63.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_62_to_63.sql 2016-06-15 22:32:47 UTC (rev 15676)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_62_to_63.sql 2016-06-16 19:36:40 UTC (rev 15677)
@@ -18,8 +18,8 @@
-- Upgrade database schema from VERSION 62 to 63 --
---------------------------------------------------
-create index CALENDAR_OBJECT_ORIGINAL_COLLECTION on
- CALENDAR_OBJECT(ORIGINAL_COLLECTION);
+create index CALENDAR_OBJECT_ORIGINAL_COLLECTION_AND_TRASHED on
+ CALENDAR_OBJECT(ORIGINAL_COLLECTION, TRASHED);
-- update the version
update CALENDARSERVER set VALUE = '63' where NAME = 'VERSION';
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20160616/5b6a0425/attachment.html>
More information about the calendarserver-changes
mailing list