[CalendarServer-changes] [6643] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Tue Nov 16 13:56:58 PST 2010
Revision: 6643
http://trac.macosforge.org/projects/calendarserver/changeset/6643
Author: cdaboo at apple.com
Date: 2010-11-16 13:56:55 -0800 (Tue, 16 Nov 2010)
Log Message:
-----------
Make depth:1 on a calendar/address book collection be more efficient wrt SQL use via a load of all child data in one go.
Modified Paths:
--------------
CalendarServer/trunk/twistedcaldav/storebridge.py
CalendarServer/trunk/txdav/base/propertystore/sql.py
CalendarServer/trunk/txdav/caldav/datastore/sql.py
CalendarServer/trunk/txdav/caldav/datastore/test/common.py
CalendarServer/trunk/txdav/carddav/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql.py
Modified: CalendarServer/trunk/twistedcaldav/storebridge.py
===================================================================
--- CalendarServer/trunk/twistedcaldav/storebridge.py 2010-11-16 20:07:16 UTC (rev 6642)
+++ CalendarServer/trunk/twistedcaldav/storebridge.py 2010-11-16 21:56:55 UTC (rev 6643)
@@ -294,6 +294,24 @@
return self._newStoreObject.syncToken() if self._newStoreObject else None
@inlineCallbacks
+ def findChildrenFaster(
+ self, depth, request, okcallback, badcallback,
+ names, privileges, inherited_aces
+ ):
+ """
+ Override to pre-load children in certain collection types for better performance.
+ """
+
+ if depth == "1":
+ yield self._newStoreObject.objectResources()
+
+ result = (yield super(_CommonHomeChildCollectionMixin, self).findChildrenFaster(
+ depth, request, okcallback, badcallback, names, privileges, inherited_aces
+ ))
+
+ returnValue(result)
+
+ @inlineCallbacks
def createCollection(self):
"""
Override C{createCollection} to actually do the work.
Modified: CalendarServer/trunk/txdav/base/propertystore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/base/propertystore/sql.py 2010-11-16 20:07:16 UTC (rev 6642)
+++ CalendarServer/trunk/txdav/base/propertystore/sql.py 2010-11-16 21:56:55 UTC (rev 6643)
@@ -64,8 +64,13 @@
"""
rows = yield txn.execSQL(
"""
- select RESOURCE_ID, NAME, VIEWER_UID, VALUE from RESOURCE_PROPERTY
- left join %s on (RESOURCE_ID = %s)
+ select
+ RESOURCE_PROPERTY.RESOURCE_ID,
+ RESOURCE_PROPERTY.NAME,
+ RESOURCE_PROPERTY.VIEWER_UID,
+ RESOURCE_PROPERTY.VALUE
+ from RESOURCE_PROPERTY
+ left join %s on (RESOURCE_PROPERTY.RESOURCE_ID = %s)
where %s = %%s
""" % (joinTable, joinColumn, parentIDColumn),
[parentID]
Modified: CalendarServer/trunk/txdav/caldav/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/sql.py 2010-11-16 20:07:16 UTC (rev 6642)
+++ CalendarServer/trunk/txdav/caldav/datastore/sql.py 2010-11-16 21:56:55 UTC (rev 6643)
@@ -243,10 +243,11 @@
class CalendarObject(CommonObjectResource):
implements(ICalendarObject)
+ _objectTable = CALENDAR_OBJECT_TABLE
+
def __init__(self, calendar, name, uid, metadata=None):
super(CalendarObject, self).__init__(calendar, name, uid)
- self._objectTable = CALENDAR_OBJECT_TABLE
if metadata is None:
metadata = {}
@@ -257,74 +258,46 @@
self.hasPrivateComment = metadata.get("hasPrivateComment", False)
- @inlineCallbacks
- def initFromStore(self):
+ @classmethod
+ def _selectAllColumns(cls):
"""
- Initialise this object from the store. We read in and cache all the extra metadata
- from the DB to avoid having to do DB queries for those individually later. Either the
- name or uid is present, so we have to tweak the query accordingly.
-
- @return: L{self} if object exists in the DB, else C{None}
+ Full set of columns in the object table that need to be loaded to
+ initialize the object resource state.
"""
-
- if self._name:
- rows = yield self._txn.execSQL("""
- select
- %(column_RESOURCE_ID)s,
- %(column_RESOURCE_NAME)s,
- %(column_UID)s,
- %(column_MD5)s,
- character_length(%(column_TEXT)s),
- %(column_ACCESS)s,
- %(column_SCHEDULE_OBJECT)s,
- %(column_SCHEDULE_TAG)s,
- %(column_SCHEDULE_ETAGS)s,
- %(column_PRIVATE_COMMENTS)s,
- %(column_CREATED)s,
- %(column_MODIFIED)s
- from %(name)s
- where %(column_RESOURCE_NAME)s = %%s and %(column_PARENT_RESOURCE_ID)s = %%s
- """ % self._objectTable,
- [self._name, self._parentCollection._resourceID]
- )
- else:
- rows = yield self._txn.execSQL("""
- select
- %(column_RESOURCE_ID)s,
- %(column_RESOURCE_NAME)s,
- %(column_UID)s,
- %(column_MD5)s,
- character_length(%(column_TEXT)s),
- %(column_ACCESS)s,
- %(column_SCHEDULE_OBJECT)s,
- %(column_SCHEDULE_TAG)s,
- %(column_SCHEDULE_ETAGS)s,
- %(column_PRIVATE_COMMENTS)s,
- %(column_CREATED)s,
- %(column_MODIFIED)s
- from %(name)s
- where %(column_UID)s = %%s and %(column_PARENT_RESOURCE_ID)s = %%s
- """ % self._objectTable,
- [self._uid, self._parentCollection._resourceID]
- )
- if rows:
- (self._resourceID,
- self._name,
- self._uid,
- self._md5,
- self._size,
- self._access,
- self._schedule_object,
- self._schedule_tag,
- self._schedule_etags,
- self._private_comments,
- self._created,
- self._modified,) = tuple(rows[0])
- yield self._loadPropertyStore()
- returnValue(self)
- else:
- returnValue(None)
+ return """
+ select
+ %(column_RESOURCE_ID)s,
+ %(column_RESOURCE_NAME)s,
+ %(column_UID)s,
+ %(column_MD5)s,
+ character_length(%(column_TEXT)s),
+ %(column_ACCESS)s,
+ %(column_SCHEDULE_OBJECT)s,
+ %(column_SCHEDULE_TAG)s,
+ %(column_SCHEDULE_ETAGS)s,
+ %(column_PRIVATE_COMMENTS)s,
+ %(column_CREATED)s,
+ %(column_MODIFIED)s
+ """ % cls._objectTable
+ def _initFromRow(self, row):
+ """
+ Given a select result using the columns from L{_selectAllColumns}, initialize
+ the object resource state.
+ """
+ (self._resourceID,
+ self._name,
+ self._uid,
+ self._md5,
+ self._size,
+ self._access,
+ self._schedule_object,
+ self._schedule_tag,
+ self._schedule_etags,
+ self._private_comments,
+ self._created,
+ self._modified,) = tuple(row)
+
@property
def _calendar(self):
return self._parentCollection
Modified: CalendarServer/trunk/txdav/caldav/datastore/test/common.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/test/common.py 2010-11-16 20:07:16 UTC (rev 6642)
+++ CalendarServer/trunk/txdav/caldav/datastore/test/common.py 2010-11-16 21:56:55 UTC (rev 6643)
@@ -531,7 +531,7 @@
transaction, even if it has not yet been committed.
"""
calendar1 = yield self.calendarUnderTest()
- calendar1.removeCalendarObjectWithName("2.ics")
+ yield calendar1.removeCalendarObjectWithName("2.ics")
calendarObjects = list((yield calendar1.calendarObjects()))
self.assertEquals(set(o.name() for o in calendarObjects),
set(calendar1_objectNames) - set(["2.ics"]))
Modified: CalendarServer/trunk/txdav/carddav/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/carddav/datastore/sql.py 2010-11-16 20:07:16 UTC (rev 6642)
+++ CalendarServer/trunk/txdav/carddav/datastore/sql.py 2010-11-16 21:56:55 UTC (rev 6643)
@@ -170,10 +170,11 @@
implements(IAddressBookObject)
+ _objectTable = ADDRESSBOOK_OBJECT_TABLE
+
def __init__(self, addressbook, name, uid, metadata=None):
super(AddressBookObject, self).__init__(addressbook, name, uid)
- self._objectTable = ADDRESSBOOK_OBJECT_TABLE
@property
Modified: CalendarServer/trunk/txdav/common/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql.py 2010-11-16 20:07:16 UTC (rev 6642)
+++ CalendarServer/trunk/txdav/common/datastore/sql.py 2010-11-16 21:56:55 UTC (rev 6643)
@@ -367,10 +367,10 @@
"""
Load and cache all children - Depth:1 optimization
"""
- results1 = (yield self._childClass.loadAllChildren(self, owned=True))
+ results1 = (yield self._childClass.loadAllObjects(self, owned=True))
for result in results1:
self._children[result.name()] = result
- results2 = (yield self._childClass.loadAllChildren(self, owned=False))
+ results2 = (yield self._childClass.loadAllObjects(self, owned=False))
for result in results2:
self._sharedChildren[result.name()] = result
self._childrenLoaded = True
@@ -689,6 +689,7 @@
self._created = None
self._modified = None
self._objects = {}
+ self._objectNames = None
self._syncTokenRevision = None
if home._notifier:
@@ -736,10 +737,10 @@
@classmethod
@inlineCallbacks
- def loadAllChildren(cls, home, owned):
+ def loadAllObjects(cls, home, owned):
"""
Load all child objects and return a list of them. This must create the child classes
- and initialize them using "batched" SQL operations to keep this contacts wrt the number of
+ and initialize them using "batched" SQL operations to keep this constant wrt the number of
children. This is an optimization for Depth:1 operations on the home.
"""
@@ -992,28 +993,41 @@
@inlineCallbacks
def objectResources(self):
- x = []
- r = x.append
- for name in (yield self.listObjectResources()):
- r((yield self.objectResourceWithName(name)))
- returnValue(x)
+ """
+ Load and cache all children - Depth:1 optimization
+ """
+ results = (yield self._objectResourceClass.loadAllObjects(self))
+ for result in results:
+ self._objects[result.name()] = result
+ self._objects[result.uid()] = result
+ self._objectNames = sorted([result.name() for result in results])
+ returnValue(results)
@inlineCallbacks
def listObjectResources(self):
- rows = yield self._txn.execSQL(
- "select %(column_RESOURCE_NAME)s from %(name)s "
- "where %(column_PARENT_RESOURCE_ID)s = %%s" % self._objectTable,
- [self._resourceID])
- returnValue(sorted([row[0] for row in rows]))
+ if self._objectNames is None:
+ rows = yield self._txn.execSQL(
+ "select %(column_RESOURCE_NAME)s from %(name)s "
+ "where %(column_PARENT_RESOURCE_ID)s = %%s" % self._objectTable,
+ [self._resourceID])
+ self._objectNames = sorted([row[0] for row in rows])
+ returnValue(self._objectNames)
+
def objectResourceWithName(self, name):
- return self._makeObjectResource(name, None)
+ if name in self._objects:
+ return succeed(self._objects[name])
+ else:
+ return self._makeObjectResource(name, None)
def objectResourceWithUID(self, uid):
- return self._makeObjectResource(None, uid)
+ if uid in self._objects:
+ return succeed(self._objects[uid])
+ else:
+ return self._makeObjectResource(None, uid)
@inlineCallbacks
@@ -1403,6 +1417,45 @@
@classmethod
+ @inlineCallbacks
+ def loadAllObjects(cls, parent):
+ """
+ Load all child objects and return a list of them. This must create the child classes
+ and initialize them using "batched" SQL operations to keep this constant wrt the number of
+ children. This is an optimization for Depth:1 operations on the collection.
+ """
+
+ results = []
+
+ # Load from the main table first
+ dataRows = yield parent._txn.execSQL(cls._selectAllColumns() + """
+ from %(name)s
+ where %(column_PARENT_RESOURCE_ID)s = %%s
+ """ % cls._objectTable,
+ [parent._resourceID,]
+ )
+
+ if dataRows:
+ # Get property stores for all these child resources (if any found)
+ propertyStores =(yield PropertyStore.loadAll(
+ parent._home.uid(),
+ parent._txn,
+ cls._objectTable["name"],
+ "%s.%s" % (cls._objectTable["name"], cls._objectTable["column_RESOURCE_ID"],),
+ "%s.%s" % (cls._objectTable["name"], cls._objectTable["column_PARENT_RESOURCE_ID"]),
+ parent._resourceID,
+ ))
+
+ # Create the actual objects merging in properties
+ for row in dataRows:
+ child = cls(parent, "", None)
+ child._initFromRow(tuple(row))
+ child._loadPropertyStore(propertyStores.get(child._resourceID, None))
+ results.append(child)
+
+ returnValue(results)
+
+ @classmethod
def objectWithName(cls, parent, name, uid):
objectResource = cls(parent, name, uid)
return objectResource.initFromStore()
@@ -1438,60 +1491,68 @@
"""
if self._name:
- rows = yield self._txn.execSQL("""
- select
- %(column_RESOURCE_ID)s,
- %(column_RESOURCE_NAME)s,
- %(column_UID)s,
- %(column_MD5)s,
- character_length(%(column_TEXT)s),
- %(column_CREATED)s,
- %(column_MODIFIED)s
+ rows = yield self._txn.execSQL(self._selectAllColumns() + """
from %(name)s
where %(column_RESOURCE_NAME)s = %%s and %(column_PARENT_RESOURCE_ID)s = %%s
""" % self._objectTable,
[self._name, self._parentCollection._resourceID]
)
else:
- rows = yield self._txn.execSQL("""
- select
- %(column_RESOURCE_ID)s,
- %(column_RESOURCE_NAME)s,
- %(column_UID)s,
- %(column_MD5)s,
- character_length(%(column_TEXT)s),
- %(column_CREATED)s,
- %(column_MODIFIED)s
+ rows = yield self._txn.execSQL(self._selectAllColumns() + """
from %(name)s
where %(column_UID)s = %%s and %(column_PARENT_RESOURCE_ID)s = %%s
""" % self._objectTable,
[self._uid, self._parentCollection._resourceID]
)
if rows:
- (self._resourceID,
- self._name,
- self._uid,
- self._md5,
- self._size,
- self._created,
- self._modified,) = tuple(rows[0])
+ self._initFromRow(tuple(rows[0]))
yield self._loadPropertyStore()
returnValue(self)
else:
returnValue(None)
+ @classmethod
+ def _selectAllColumns(cls):
+ """
+ Full set of columns in the object table that need to be loaded to
+ initialize the object resource state.
+ """
+ return """
+ select
+ %(column_RESOURCE_ID)s,
+ %(column_RESOURCE_NAME)s,
+ %(column_UID)s,
+ %(column_MD5)s,
+ character_length(%(column_TEXT)s),
+ %(column_CREATED)s,
+ %(column_MODIFIED)s
+ """ % cls._objectTable
+ def _initFromRow(self, row):
+ """
+ Given a select result using the columns from L{_selectAllColumns}, initialize
+ the object resource state.
+ """
+ (self._resourceID,
+ self._name,
+ self._uid,
+ self._md5,
+ self._size,
+ self._created,
+ self._modified,) = tuple(row)
+
@inlineCallbacks
- def _loadPropertyStore(self):
- props = yield PropertyStore.load(
- self._parentCollection.ownerHome().uid(),
- self._txn,
- self._resourceID
- )
+ def _loadPropertyStore(self, props=None):
+ if props is None:
+ props = yield PropertyStore.load(
+ self._parentCollection.ownerHome().uid(),
+ self._txn,
+ self._resourceID
+ )
self.initPropertyStore(props)
self._propertyStore = props
-
+
def properties(self):
return self._propertyStore
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20101116/6c26fd0b/attachment-0001.html>
More information about the calendarserver-changes
mailing list