[CalendarServer-changes] [8566] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Fri Jan 20 08:42:50 PST 2012
Revision: 8566
http://trac.macosforge.org/projects/calendarserver/changeset/8566
Author: cdaboo at apple.com
Date: 2012-01-20 08:42:49 -0800 (Fri, 20 Jan 2012)
Log Message:
-----------
Remove lock contention on the CALENDAR table MODIFIED column by moving the calendar metadata to a separate table
and also use select for update nowait to not block when contention does occur.
Modified Paths:
--------------
CalendarServer/trunk/twext/enterprise/dal/syntax.py
CalendarServer/trunk/txdav/caldav/datastore/sql.py
CalendarServer/trunk/txdav/carddav/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
Added Paths:
-----------
CalendarServer/trunk/txdav/common/datastore/sql_schema/old/v7.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_7_to_8.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_7_to_8.sql
Modified: CalendarServer/trunk/twext/enterprise/dal/syntax.py
===================================================================
--- CalendarServer/trunk/twext/enterprise/dal/syntax.py 2012-01-20 16:29:21 UTC (rev 8565)
+++ CalendarServer/trunk/twext/enterprise/dal/syntax.py 2012-01-20 16:42:49 UTC (rev 8566)
@@ -1,6 +1,6 @@
# -*- test-case-name: twext.enterprise.dal.test.test_sqlsyntax -*-
##
-# Copyright (c) 2010 Apple Inc. All rights reserved.
+# Copyright (c) 2010-2012 Apple Inc. All rights reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
@@ -758,7 +758,7 @@
"""
def __init__(self, columns=None, Where=None, From=None, OrderBy=None,
- GroupBy=None, Limit=None, ForUpdate=False, Ascending=None,
+ GroupBy=None, Limit=None, ForUpdate=False, NoWait=False, Ascending=None,
Having=None, Distinct=False):
self.From = From
self.Where = Where
@@ -780,6 +780,7 @@
columns = _SomeColumns(columns)
self.columns = columns
self.ForUpdate = ForUpdate
+ self.NoWait = NoWait
self.Ascending = Ascending
@@ -839,6 +840,8 @@
stmt.append(SQLFragment(kw))
if self.ForUpdate:
stmt.text += " for update"
+ if self.NoWait:
+ stmt.text += " nowait"
if self.Limit is not None:
limitConst = Constant(self.Limit).subSQL(metadata, allTables)
if metadata.dialect == ORACLE_DIALECT:
Modified: CalendarServer/trunk/txdav/caldav/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/sql.py 2012-01-20 16:29:21 UTC (rev 8565)
+++ CalendarServer/trunk/txdav/caldav/datastore/sql.py 2012-01-20 16:42:49 UTC (rev 8566)
@@ -1,6 +1,6 @@
# -*- test-case-name: txdav.caldav.datastore.test.test_sql -*-
##
-# Copyright (c) 2010-2011 Apple Inc. All rights reserved.
+# Copyright (c) 2010-2012 Apple Inc. All rights reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
@@ -292,6 +292,7 @@
# structured tables. (new, preferred)
_bindSchema = schema.CALENDAR_BIND
_homeChildSchema = schema.CALENDAR
+ _homeChildMetaDataSchema = schema.CALENDAR_METADATA
_revisionsSchema = schema.CALENDAR_OBJECT_REVISIONS
_objectSchema = schema.CALENDAR_OBJECT
_timeRangeSchema = schema.TIME_RANGE
@@ -337,9 +338,9 @@
# Common behavior is to have created and modified
return (
- cls._homeChildSchema.CREATED,
- cls._homeChildSchema.MODIFIED,
- cls._homeChildSchema.SUPPORTED_COMPONENTS,
+ cls._homeChildMetaDataSchema.CREATED,
+ cls._homeChildMetaDataSchema.MODIFIED,
+ cls._homeChildMetaDataSchema.SUPPORTED_COMPONENTS,
)
@classmethod
@@ -397,7 +398,7 @@
on collection creation, but for migration we may need to change after the fact - hence a separate api.
"""
- cal = self._homeChildSchema
+ cal = self._homeChildMetaDataSchema
yield Update(
{
cal.SUPPORTED_COMPONENTS : supported_components
Modified: CalendarServer/trunk/txdav/carddav/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/carddav/datastore/sql.py 2012-01-20 16:29:21 UTC (rev 8565)
+++ CalendarServer/trunk/txdav/carddav/datastore/sql.py 2012-01-20 16:42:49 UTC (rev 8566)
@@ -1,6 +1,6 @@
# -*- test-case-name: txdav.carddav.datastore.test.test_sql -*-
##
-# Copyright (c) 2010-2011 Apple Inc. All rights reserved.
+# Copyright (c) 2010-2012 Apple Inc. All rights reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
@@ -145,6 +145,7 @@
# structured tables. (new, preferred)
_bindSchema = schema.ADDRESSBOOK_BIND
_homeChildSchema = schema.ADDRESSBOOK
+ _homeChildMetaDataSchema = schema.ADDRESSBOOK_METADATA
_revisionsSchema = schema.ADDRESSBOOK_OBJECT_REVISIONS
_objectSchema = schema.ADDRESSBOOK_OBJECT
Modified: CalendarServer/trunk/txdav/common/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql.py 2012-01-20 16:29:21 UTC (rev 8565)
+++ CalendarServer/trunk/txdav/common/datastore/sql.py 2012-01-20 16:42:49 UTC (rev 8566)
@@ -458,7 +458,7 @@
@inlineCallbacks
- def subtransaction(self, thunk, retries=1):
+ def subtransaction(self, thunk, retries=1, failureOK=False):
"""
Create a limited transaction object, which provides only SQL execution,
and run a function in a sub-transaction (savepoint) context, with that
@@ -470,6 +470,8 @@
@param retries: the number of times to re-try C{thunk} before deciding
that it's legitimately failed.
+ @param failureOK: it is OK if this subtransaction fails so do not log.
+
@return: a L{Deferred} which fires or fails according to the logic in
C{thunk}. If it succeeds, it will return the value that C{thunk}
returned.
@@ -490,7 +492,8 @@
try:
result = yield thunk(block)
except:
- failuresToMaybeLog.append(Failure())
+ if not failureOK:
+ failuresToMaybeLog.append(Failure())
yield sp.rollback(block)
if triesLeft:
triesLeft -= 1
@@ -1268,6 +1271,16 @@
returnValue(None)
@classproperty
+ def _lockLastModifiedQuery(cls): #@NoSelf
+ meta = cls._homeMetaDataSchema
+ return Select(
+ From=meta,
+ Where=meta.RESOURCE_ID == Parameter("resourceID"),
+ ForUpdate=True,
+ NoWait=True
+ )
+
+ @classproperty
def _changeLastModifiedQuery(cls): #@NoSelf
meta = cls._homeMetaDataSchema
return Update({meta.MODIFIED: utcNowSQL},
@@ -1280,16 +1293,20 @@
Bump the MODIFIED value. A possible deadlock could happen here if two or more
simultaneous changes are happening. In that case it is OK for the MODIFIED change
to fail so long as at least one works. We will use SAVEPOINT logic to handle
- ignoring the deadlock error.
+ ignoring the deadlock error. We use SELECT FOR UPDATE NOWAIT to ensure we do not
+ delay the transaction whilst waiting for deadlock detection to kick in.
"""
+ @inlineCallbacks
def _bumpModified(subtxn):
- return self._changeLastModifiedQuery.on(subtxn, resourceID=self._resourceID)
+ yield self._lockLastModifiedQuery.on(subtxn, resourceID=self._resourceID)
+ result = (yield self._changeLastModifiedQuery.on(subtxn, resourceID=self._resourceID))
+ returnValue(result)
try:
- self._modified = (yield self._txn.subtransaction(_bumpModified, retries=0))[0][0]
+ self._modified = (yield self._txn.subtransaction(_bumpModified, retries=0, failureOK=True))[0][0]
except AllRetriesFailed:
- pass
+ log.debug("CommonHome.bumpModified failed")
@inlineCallbacks
def notifyChanged(self):
@@ -1616,10 +1633,11 @@
_objectResourceClass = None
- _bindSchema = None
- _homeChildSchema = None
- _revisionsSchema = None
- _objectSchema = None
+ _bindSchema = None
+ _homeChildSchema = None
+ _homeChildMetaDataSchema = None
+ _revisionsSchema = None
+ _objectSchema = None
_bindTable = None
_homeChildTable = None
@@ -1672,8 +1690,8 @@
# Common behavior is to have created and modified
return (
- cls._homeChildSchema.CREATED,
- cls._homeChildSchema.MODIFIED,
+ cls._homeChildMetaDataSchema.CREATED,
+ cls._homeChildMetaDataSchema.MODIFIED,
)
@classmethod
@@ -1723,6 +1741,7 @@
def _allHomeChildrenQuery(cls, owned):
bind = cls._bindSchema
child = cls._homeChildSchema
+ childMetaData = cls._homeChildMetaDataSchema
if owned:
ownedPiece = bind.BIND_MODE == _BIND_MODE_OWN
else:
@@ -1734,6 +1753,8 @@
return Select(columns,
From=child.join(
bind, child.RESOURCE_ID == bind.RESOURCE_ID,
+ 'left outer').join(
+ childMetaData, childMetaData.RESOURCE_ID == bind.RESOURCE_ID,
'left outer'),
Where=(bind.HOME_RESOURCE_ID == Parameter("resourceID")
).And(ownedPiece))
@@ -1908,16 +1929,26 @@
@classproperty
- def _insertDefaultHomeChild(cls): #@NoSelf
+ def _insertHomeChild(cls): #@NoSelf
"""
DAL statement to create a home child with all default values.
"""
child = cls._homeChildSchema
return Insert({child.RESOURCE_ID: schema.RESOURCE_ID_SEQ},
- Return=(child.RESOURCE_ID, child.CREATED, child.MODIFIED))
+ Return=(child.RESOURCE_ID))
@classproperty
+ def _insertHomeChildMetaData(cls): #@NoSelf
+ """
+ DAL statement to create a home child with all default values.
+ """
+ child = cls._homeChildMetaDataSchema
+ return Insert({child.RESOURCE_ID: Parameter("resourceID")},
+ Return=(child.CREATED, child.MODIFIED))
+
+
+ @classproperty
def _initialOwnerBind(cls): #@NoSelf
"""
DAL statement to create a bind entry for a particular home value.
@@ -1942,10 +1973,15 @@
if name.startswith("."):
raise HomeChildNameNotAllowedError(name)
- # Create and initialize this object, similar to initFromStore
- resourceID, _created, _modified = (
- yield cls._insertDefaultHomeChild.on(home._txn))[0]
+ # Create this object
+ resourceID = (
+ yield cls._insertHomeChild.on(home._txn))[0]
+ # Initialize this object
+ _created, _modified = (
+ yield cls._insertHomeChildMetaData.on(home._txn,
+ resourceID=resourceID))[0]
+
# Bind table needs entry
yield cls._initialOwnerBind.on(home._txn, homeID=home._resourceID,
resourceID=resourceID, name=name)
@@ -1971,7 +2007,7 @@
"""
DAL query to retrieve created/modified dates based on a resource ID.
"""
- child = cls._homeChildSchema
+ child = cls._homeChildMetaDataSchema
return Select(cls.metadataColumns(),
From=child,
Where=child.RESOURCE_ID == Parameter("resourceID"))
@@ -2440,8 +2476,18 @@
returnValue(None)
@classproperty
+ def _lockLastModifiedQuery(cls): #@NoSelf
+ schema = cls._homeChildMetaDataSchema
+ return Select(
+ From=schema,
+ Where=schema.RESOURCE_ID == Parameter("resourceID"),
+ ForUpdate=True,
+ NoWait=True
+ )
+
+ @classproperty
def _changeLastModifiedQuery(cls): #@NoSelf
- schema = cls._homeChildSchema
+ schema = cls._homeChildMetaDataSchema
return Update({schema.MODIFIED: utcNowSQL},
Where=schema.RESOURCE_ID == Parameter("resourceID"),
Return=schema.MODIFIED)
@@ -2452,16 +2498,20 @@
Bump the MODIFIED value. A possible deadlock could happen here if two or more
simultaneous changes are happening. In that case it is OK for the MODIFIED change
to fail so long as at least one works. We will use SAVEPOINT logic to handle
- ignoring the deadlock error.
+ ignoring the deadlock error. We use SELECT FOR UPDATE NOWAIT to ensure we do not
+ delay the transaction whilst waiting for deadlock detection to kick in.
"""
+ @inlineCallbacks
def _bumpModified(subtxn):
- return self._changeLastModifiedQuery.on(subtxn, resourceID=self._resourceID)
+ yield self._lockLastModifiedQuery.on(subtxn, resourceID=self._resourceID)
+ result = (yield self._changeLastModifiedQuery.on(subtxn, resourceID=self._resourceID))
+ returnValue(result)
try:
- self._modified = (yield self._txn.subtransaction(_bumpModified, retries=0))[0][0]
+ self._modified = (yield self._txn.subtransaction(_bumpModified, retries=0, failureOK=True))[0][0]
except AllRetriesFailed:
- pass
+ log.debug("CommonHomeChild.bumpModified failed")
@inlineCallbacks
def notifyChanged(self):
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql 2012-01-20 16:29:21 UTC (rev 8565)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql 2012-01-20 16:42:49 UTC (rev 8566)
@@ -1,7 +1,7 @@
-- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-
----
--- Copyright (c) 2010-2011 Apple Inc. All rights reserved.
+-- Copyright (c) 2010-2012 Apple Inc. All rights reserved.
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
@@ -49,7 +49,16 @@
--------------
create table CALENDAR (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
+);
+
+
+-----------------------
+-- Calendar Metadata --
+-----------------------
+
+create table CALENDAR_METADATA (
+ RESOURCE_ID integer primary key references CALENDAR on delete cascade, -- implicit index
SUPPORTED_COMPONENTS varchar(255) default null,
CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
@@ -320,9 +329,9 @@
DATAVERSION integer default 0 not null
);
---------------------------------
--- AddressBook Home Meta-data --
---------------------------------
+-------------------------------
+-- AddressBook Home Metadata --
+-------------------------------
create table ADDRESSBOOK_HOME_METADATA (
RESOURCE_ID integer primary key references ADDRESSBOOK_HOME on delete cascade, -- implicit index
@@ -336,7 +345,16 @@
-----------------
create table ADDRESSBOOK (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
+);
+
+
+--------------------------
+-- AddressBook Metadata --
+--------------------------
+
+create table ADDRESSBOOK_METADATA (
+ RESOURCE_ID integer primary key references ADDRESSBOOK on delete cascade, -- implicit index
CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
);
@@ -476,6 +494,6 @@
VALUE varchar(255)
);
-insert into CALENDARSERVER values ('VERSION', '7');
+insert into CALENDARSERVER values ('VERSION', '8');
insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '2');
insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '1');
Added: CalendarServer/trunk/txdav/common/datastore/sql_schema/old/v7.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/old/v7.sql (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/old/v7.sql 2012-01-20 16:42:49 UTC (rev 8566)
@@ -0,0 +1,481 @@
+-- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-
+
+----
+-- Copyright (c) 2010-2011 Apple Inc. All rights reserved.
+--
+-- Licensed under the Apache License, Version 2.0 (the "License");
+-- you may not use this file except in compliance with the License.
+-- You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+----
+
+-----------------
+-- Resource ID --
+-----------------
+
+create sequence RESOURCE_ID_SEQ;
+
+
+-------------------
+-- Calendar Home --
+-------------------
+
+create table CALENDAR_HOME (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ OWNER_UID varchar(255) not null unique, -- implicit index
+ DATAVERSION integer default 0 not null
+);
+
+----------------------------
+-- Calendar Home Metadata --
+----------------------------
+
+create table CALENDAR_HOME_METADATA (
+ RESOURCE_ID integer primary key references CALENDAR_HOME on delete cascade, -- implicit index
+ QUOTA_USED_BYTES integer default 0 not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+--------------
+-- Calendar --
+--------------
+
+create table CALENDAR (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ SUPPORTED_COMPONENTS varchar(255) default null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+
+------------------------
+-- Sharing Invitation --
+------------------------
+
+create table INVITE (
+ INVITE_UID varchar(255) not null,
+ NAME varchar(255) not null,
+ RECIPIENT_ADDRESS varchar(255) not null,
+ HOME_RESOURCE_ID integer not null,
+ RESOURCE_ID integer not null
+
+ -- Need primary key on (INVITE_UID, NAME, RECIPIENT_ADDRESS)?
+);
+
+create index INVITE_INVITE_UID on INVITE(INVITE_UID);
+create index INVITE_RESOURCE_ID on INVITE(RESOURCE_ID);
+create index INVITE_HOME_RESOURCE_ID on INVITE(HOME_RESOURCE_ID);
+
+---------------------------
+-- Sharing Notifications --
+---------------------------
+
+create table NOTIFICATION_HOME (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ OWNER_UID varchar(255) not null unique -- implicit index
+);
+
+create table NOTIFICATION (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME,
+ NOTIFICATION_UID varchar(255) not null,
+ XML_TYPE varchar(255) not null,
+ XML_DATA text not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ unique(NOTIFICATION_UID, NOTIFICATION_HOME_RESOURCE_ID) -- implicit index
+);
+
+create index NOTIFICATION_NOTIFICATION_HOME_RESOURCE_ID on
+ NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);
+
+-------------------
+-- Calendar Bind --
+-------------------
+
+-- Joins CALENDAR_HOME and CALENDAR
+
+create table CALENDAR_BIND (
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
+
+ -- An invitation which hasn't been accepted yet will not yet have a resource
+ -- name, so this field may be null.
+
+ CALENDAR_RESOURCE_NAME varchar(255),
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ SEEN_BY_OWNER boolean not null,
+ SEEN_BY_SHAREE boolean not null,
+ MESSAGE text,
+
+ primary key(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID), -- implicit index
+ unique(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_NAME) -- implicit index
+);
+
+create index CALENDAR_BIND_RESOURCE_ID on CALENDAR_BIND(CALENDAR_RESOURCE_ID);
+
+-- Enumeration of calendar bind modes
+
+create table CALENDAR_BIND_MODE (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into CALENDAR_BIND_MODE values (0, 'own' );
+insert into CALENDAR_BIND_MODE values (1, 'read' );
+insert into CALENDAR_BIND_MODE values (2, 'write');
+insert into CALENDAR_BIND_MODE values (3, 'direct');
+
+-- Enumeration of statuses
+
+create table CALENDAR_BIND_STATUS (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into CALENDAR_BIND_STATUS values (0, 'invited' );
+insert into CALENDAR_BIND_STATUS values (1, 'accepted');
+insert into CALENDAR_BIND_STATUS values (2, 'declined');
+insert into CALENDAR_BIND_STATUS values (3, 'invalid');
+
+
+---------------------
+-- Calendar Object --
+---------------------
+
+create table CALENDAR_OBJECT (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
+ RESOURCE_NAME varchar(255) not null,
+ ICALENDAR_TEXT text not null,
+ ICALENDAR_UID varchar(255) not null,
+ ICALENDAR_TYPE varchar(255) not null,
+ ATTACHMENTS_MODE integer default 0 not null, -- enum CALENDAR_OBJECT_ATTACHMENTS_MODE
+ DROPBOX_ID varchar(255),
+ ORGANIZER varchar(255),
+ ORGANIZER_OBJECT integer references CALENDAR_OBJECT,
+ RECURRANCE_MAX date, -- maximum date that recurrences have been expanded to.
+ ACCESS integer default 0 not null,
+ SCHEDULE_OBJECT boolean default false,
+ SCHEDULE_TAG varchar(36) default null,
+ SCHEDULE_ETAGS text default null,
+ PRIVATE_COMMENTS boolean default false not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ unique(CALENDAR_RESOURCE_ID, RESOURCE_NAME) -- implicit index
+
+ -- since the 'inbox' is a 'calendar resource' for the purpose of storing
+ -- calendar objects, this constraint has to be selectively enforced by the
+ -- application layer.
+
+ -- unique(CALENDAR_RESOURCE_ID, ICALENDAR_UID)
+);
+
+create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_AND_ICALENDAR_UID on
+ CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, ICALENDAR_UID);
+
+create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_RECURRANCE_MAX on
+ CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, RECURRANCE_MAX);
+
+create index CALENDAR_OBJECT_ORGANIZER_OBJECT on
+ CALENDAR_OBJECT(ORGANIZER_OBJECT);
+
+create index CALENDAR_OBJECT_DROPBOX_ID on
+ CALENDAR_OBJECT(DROPBOX_ID);
+
+-- Enumeration of attachment modes
+
+create table CALENDAR_OBJECT_ATTACHMENTS_MODE (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (0, 'none' );
+insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (1, 'read' );
+insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (2, 'write');
+
+
+-- Enumeration of calendar access types
+
+create table CALENDAR_ACCESS_TYPE (
+ ID integer primary key,
+ DESCRIPTION varchar(32) not null unique
+);
+
+insert into CALENDAR_ACCESS_TYPE values (0, '' );
+insert into CALENDAR_ACCESS_TYPE values (1, 'public' );
+insert into CALENDAR_ACCESS_TYPE values (2, 'private' );
+insert into CALENDAR_ACCESS_TYPE values (3, 'confidential' );
+insert into CALENDAR_ACCESS_TYPE values (4, 'restricted' );
+
+-----------------
+-- Instance ID --
+-----------------
+
+create sequence INSTANCE_ID_SEQ;
+
+
+----------------
+-- Time Range --
+----------------
+
+create table TIME_RANGE (
+ INSTANCE_ID integer primary key default nextval('INSTANCE_ID_SEQ'), -- implicit index
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
+ CALENDAR_OBJECT_RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ FLOATING boolean not null,
+ START_DATE timestamp not null,
+ END_DATE timestamp not null,
+ FBTYPE integer not null,
+ TRANSPARENT boolean not null
+);
+
+create index TIME_RANGE_CALENDAR_RESOURCE_ID on
+ TIME_RANGE(CALENDAR_RESOURCE_ID);
+create index TIME_RANGE_CALENDAR_OBJECT_RESOURCE_ID on
+ TIME_RANGE(CALENDAR_OBJECT_RESOURCE_ID);
+
+
+-- Enumeration of free/busy types
+
+create table FREE_BUSY_TYPE (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into FREE_BUSY_TYPE values (0, 'unknown' );
+insert into FREE_BUSY_TYPE values (1, 'free' );
+insert into FREE_BUSY_TYPE values (2, 'busy' );
+insert into FREE_BUSY_TYPE values (3, 'busy-unavailable');
+insert into FREE_BUSY_TYPE values (4, 'busy-tentative' );
+
+
+------------------
+-- Transparency --
+------------------
+
+create table TRANSPARENCY (
+ TIME_RANGE_INSTANCE_ID integer not null references TIME_RANGE on delete cascade,
+ USER_ID varchar(255) not null,
+ TRANSPARENT boolean not null
+);
+
+create index TRANSPARENCY_TIME_RANGE_INSTANCE_ID on
+ TRANSPARENCY(TIME_RANGE_INSTANCE_ID);
+
+----------------
+-- Attachment --
+----------------
+
+create table ATTACHMENT (
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ DROPBOX_ID varchar(255) not null,
+ CONTENT_TYPE varchar(255) not null,
+ SIZE integer not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ PATH varchar(1024) not null,
+
+ primary key(DROPBOX_ID, PATH) --implicit index
+);
+
+create index ATTACHMENT_CALENDAR_HOME_RESOURCE_ID on
+ ATTACHMENT(CALENDAR_HOME_RESOURCE_ID);
+
+-----------------------
+-- Resource Property --
+-----------------------
+
+create table RESOURCE_PROPERTY (
+ RESOURCE_ID integer not null, -- foreign key: *.RESOURCE_ID
+ NAME varchar(255) not null,
+ VALUE text not null, -- FIXME: xml?
+ VIEWER_UID varchar(255),
+
+ primary key(RESOURCE_ID, NAME, VIEWER_UID) -- implicit index
+);
+
+
+----------------------
+-- AddressBook Home --
+----------------------
+
+create table ADDRESSBOOK_HOME (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ OWNER_UID varchar(255) not null unique, -- implicit index
+ DATAVERSION integer default 0 not null
+);
+
+--------------------------------
+-- AddressBook Home Meta-data --
+--------------------------------
+
+create table ADDRESSBOOK_HOME_METADATA (
+ RESOURCE_ID integer primary key references ADDRESSBOOK_HOME on delete cascade, -- implicit index
+ QUOTA_USED_BYTES integer default 0 not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+-----------------
+-- AddressBook --
+-----------------
+
+create table ADDRESSBOOK (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+
+----------------------
+-- AddressBook Bind --
+----------------------
+
+-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK
+
+create table ADDRESSBOOK_BIND (
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
+
+ -- An invitation which hasn't been accepted yet will not yet have a resource
+ -- name, so this field may be null.
+
+ ADDRESSBOOK_RESOURCE_NAME varchar(255),
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ SEEN_BY_OWNER boolean not null,
+ SEEN_BY_SHAREE boolean not null,
+ MESSAGE text, -- FIXME: xml?
+
+ primary key(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_ID), -- implicit index
+ unique(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME) -- implicit index
+);
+
+create index ADDRESSBOOK_BIND_RESOURCE_ID on
+ ADDRESSBOOK_BIND(ADDRESSBOOK_RESOURCE_ID);
+
+create table ADDRESSBOOK_OBJECT (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
+ RESOURCE_NAME varchar(255) not null,
+ VCARD_TEXT text not null,
+ VCARD_UID varchar(255) not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ unique(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME), -- implicit index
+ unique(ADDRESSBOOK_RESOURCE_ID, VCARD_UID) -- implicit index
+);
+
+---------------
+-- Revisions --
+---------------
+
+create sequence REVISION_SEQ;
+
+
+---------------
+-- Revisions --
+---------------
+
+create table CALENDAR_OBJECT_REVISIONS (
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ CALENDAR_RESOURCE_ID integer references CALENDAR,
+ CALENDAR_NAME varchar(255) default null,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null
+);
+
+create index CALENDAR_OBJECT_REVISIONS_HOME_RESOURCE_ID
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_HOME_RESOURCE_ID);
+
+create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, RESOURCE_NAME);
+
+create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, REVISION);
+
+-------------------------------
+-- AddressBook Object Revisions --
+-------------------------------
+
+create table ADDRESSBOOK_OBJECT_REVISIONS (
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ ADDRESSBOOK_RESOURCE_ID integer references ADDRESSBOOK,
+ ADDRESSBOOK_NAME varchar(255) default null,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null
+);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, REVISION);
+
+-----------------------------------
+-- Notification Object Revisions --
+-----------------------------------
+
+create table NOTIFICATION_OBJECT_REVISIONS (
+ NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME on delete cascade,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null,
+
+ unique(NOTIFICATION_HOME_RESOURCE_ID, RESOURCE_NAME) -- implicit index
+);
+
+create index NOTIFICATION_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+ on NOTIFICATION_OBJECT_REVISIONS(NOTIFICATION_HOME_RESOURCE_ID, REVISION);
+
+-------------------------------------------
+-- Apple Push Notification Subscriptions --
+-------------------------------------------
+
+create table APN_SUBSCRIPTIONS (
+ TOKEN varchar(255) not null,
+ RESOURCE_KEY varchar(255) not null,
+ MODIFIED integer not null,
+ SUBSCRIBER_GUID varchar(255) not null,
+
+ primary key(TOKEN, RESOURCE_KEY) -- implicit index
+);
+
+create index APN_SUBSCRIPTIONS_RESOURCE_KEY
+ on APN_SUBSCRIPTIONS(RESOURCE_KEY);
+
+
+--------------------
+-- Schema Version --
+--------------------
+
+create table CALENDARSERVER (
+ NAME varchar(255) primary key, -- implicit index
+ VALUE varchar(255)
+);
+
+insert into CALENDARSERVER values ('VERSION', '7');
+insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '2');
+insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '1');
Added: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_7_to_8.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_7_to_8.sql (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_7_to_8.sql 2012-01-20 16:42:49 UTC (rev 8566)
@@ -0,0 +1,50 @@
+----
+-- Copyright (c) 2012 Apple Inc. All rights reserved.
+--
+-- Licensed under the Apache License, Version 2.0 (the "License");
+-- you may not use this file except in compliance with the License.
+-- You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+----
+
+-------------------------------------------------
+-- Upgrade database schema from VERSION 7 to 8 --
+-------------------------------------------------
+
+-- Add new table populated from existing one
+create table CALENDAR_METADATA (
+ RESOURCE_ID integer primary key references CALENDAR on delete cascade, -- implicit index
+ SUPPORTED_COMPONENTS varchar(255) default null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+insert into CALENDAR_METADATA
+ select RESOURCE_ID, SUPPORTED_COMPONENTS, CREATED, MODIFIED from CALENDAR;
+
+-- Alter existing table to drop columns moved to new one
+alter table CALENDAR
+ drop (SUPPORTED_COMPONENTS, CREATED, MODIFIED);
+
+-- Add new table populated from existing one
+create table ADDRESSBOOK_METADATA (
+ RESOURCE_ID integer primary key references ADDRESSBOOK on delete cascade, -- implicit index
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+insert into ADDRESSBOOK_METADATA
+ select RESOURCE_ID, CREATED, MODIFIED from ADDRESSBOOK;
+
+-- Alter existing table to drop columns moved to new one
+alter table ADDRESSBOOK
+ drop (CREATED, MODIFIED);
+
+-- Now update the version
+-- No data upgrades
+update CALENDARSERVER set VALUE = '8' where NAME = 'VERSION';
Added: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_7_to_8.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_7_to_8.sql (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_7_to_8.sql 2012-01-20 16:42:49 UTC (rev 8566)
@@ -0,0 +1,53 @@
+----
+-- Copyright (c) 2012 Apple Inc. All rights reserved.
+--
+-- Licensed under the Apache License, Version 2.0 (the "License");
+-- you may not use this file except in compliance with the License.
+-- You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+----
+
+-------------------------------------------------
+-- Upgrade database schema from VERSION 7 to 8 --
+-------------------------------------------------
+
+-- Add new table populated from existing one
+create table CALENDAR_METADATA (
+ RESOURCE_ID integer primary key references CALENDAR on delete cascade, -- implicit index
+ SUPPORTED_COMPONENTS varchar(255) default null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+insert into CALENDAR_METADATA
+ select RESOURCE_ID, SUPPORTED_COMPONENTS, CREATED, MODIFIED from CALENDAR;
+
+-- Alter existing table to drop columns moved to new one
+alter table CALENDAR
+ drop column SUPPORTED_COMPONENTS,
+ drop column CREATED,
+ drop column MODIFIED;
+
+-- Add new table populated from existing one
+create table ADDRESSBOOK_METADATA (
+ RESOURCE_ID integer primary key references ADDRESSBOOK on delete cascade, -- implicit index
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+insert into ADDRESSBOOK_METADATA
+ select RESOURCE_ID, CREATED, MODIFIED from ADDRESSBOOK;
+
+-- Alter existing table to drop columns moved to new one
+alter table ADDRESSBOOK
+ drop column CREATED,
+ drop column MODIFIED;
+
+-- Now update the version
+-- No data upgrades
+update CALENDARSERVER set VALUE = '8' where NAME = 'VERSION';
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20120120/80c22a37/attachment-0001.html>
More information about the calendarserver-changes
mailing list