[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