[CalendarServer-changes] [12782] CalendarServer/trunk
source_changes at macosforge.org
source_changes at macosforge.org
Sat Mar 1 08:21:26 PST 2014
Revision: 12782
http://trac.calendarserver.org//changeset/12782
Author: cdaboo at apple.com
Date: 2014-03-01 08:21:25 -0800 (Sat, 01 Mar 2014)
Log Message:
-----------
Support the new job queue rather than work items.
Modified Paths:
--------------
CalendarServer/trunk/calendarserver/push/notifier.py
CalendarServer/trunk/calendarserver/push/test/test_notifier.py
CalendarServer/trunk/calendarserver/tap/caldav.py
CalendarServer/trunk/calendarserver/tap/test/test_caldav.py
CalendarServer/trunk/calendarserver/tools/cmdline.py
CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/delivery.py
CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/inbound.py
CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/outbound.py
CalendarServer/trunk/txdav/caldav/datastore/scheduling/work.py
CalendarServer/trunk/txdav/caldav/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql_dump.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_tables.py
CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py
CalendarServer/trunk/txdav/common/datastore/upgrade/sql/test/test_upgrade.py
CalendarServer/trunk/txdav/common/datastore/work/inbox_cleanup.py
CalendarServer/trunk/txdav/common/datastore/work/revision_cleanup.py
CalendarServer/trunk/txdav/common/datastore/work/test/test_inbox_cleanup.py
CalendarServer/trunk/txdav/common/datastore/work/test/test_revision_cleanup.py
CalendarServer/trunk/txdav/who/groups.py
Added Paths:
-----------
CalendarServer/trunk/bin/calendarserver_monitor_jobs
CalendarServer/trunk/calendarserver/tools/jobitems.py
CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect-extras.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/old/oracle-dialect/v36.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/old/postgres-dialect/v36.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_36_to_37.sql
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_36_to_37.sql
Added: CalendarServer/trunk/bin/calendarserver_monitor_jobs
===================================================================
--- CalendarServer/trunk/bin/calendarserver_monitor_jobs (rev 0)
+++ CalendarServer/trunk/bin/calendarserver_monitor_jobs 2014-03-01 16:21:25 UTC (rev 12782)
@@ -0,0 +1,38 @@
+#!/usr/bin/env python
+
+##
+# Copyright (c) 2006-2014 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.
+##
+
+import os
+import sys
+
+# In OS X Server context, add to PATH to find Postgres utilities (initdb, pg_ctl)
+if "Server.app" in sys.argv[0]:
+ os.environ["PATH"] += ":" + os.path.join(os.path.dirname(os.path.dirname(sys.argv[0])), "bin")
+
+#PYTHONPATH
+
+if __name__ == "__main__":
+ if "PYTHONPATH" in globals():
+ sys.path.insert(0, PYTHONPATH)
+ else:
+ try:
+ import _calendarserver_preamble
+ except ImportError:
+ sys.exc_clear()
+
+ from calendarserver.tools.jobitems import main
+ main()
Property changes on: CalendarServer/trunk/bin/calendarserver_monitor_jobs
___________________________________________________________________
Added: svn:executable
+ *
Modified: CalendarServer/trunk/calendarserver/push/notifier.py
===================================================================
--- CalendarServer/trunk/calendarserver/push/notifier.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/calendarserver/push/notifier.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -20,7 +20,7 @@
from twext.enterprise.dal.record import fromTable
from twext.enterprise.dal.syntax import Delete, Select, Parameter
-from twext.enterprise.queue import WorkItem
+from twext.enterprise.jobqueue import WorkItem
from twext.python.log import Logger
from twisted.internet.defer import inlineCallbacks
@@ -47,11 +47,11 @@
# Find all work items with the same push ID and find the highest
# priority. Delete matching work items.
- results = (yield Select([self.table.WORK_ID, self.table.PRIORITY],
+ results = (yield Select([self.table.WORK_ID, self.table.PUSH_PRIORITY],
From=self.table, Where=self.table.PUSH_ID == self.pushID).on(
self.transaction))
- maxPriority = self.priority
+ maxPriority = self.pushPriority
# If there are other enqueued work items for this push ID, find the
# highest priority one and use that value
@@ -63,17 +63,16 @@
workIDs.append(workID)
# Delete the work items we selected
- yield Delete(From=self.table,
- Where=self.table.WORK_ID.In(
- Parameter("workIDs", len(workIDs)))
- ).on(self.transaction, workIDs=workIDs)
+ yield Delete(
+ From=self.table,
+ Where=self.table.WORK_ID.In(Parameter("workIDs", len(workIDs)))
+ ).on(self.transaction, workIDs=workIDs)
pushDistributor = self.transaction._pushDistributor
if pushDistributor is not None:
# Convert the integer priority value back into a constant
priority = PushPriority.lookupByValue(maxPriority)
- yield pushDistributor.enqueue(self.transaction, self.pushID,
- priority=priority)
+ yield pushDistributor.enqueue(self.transaction, self.pushID, priority=priority)
@@ -182,10 +181,12 @@
"""
Enqueue a push notification work item on the provided transaction.
"""
- notBefore = datetime.datetime.utcnow() + datetime.timedelta(seconds=self.coalesceSeconds)
- yield txn.enqueue(PushNotificationWork,
- pushID=self.pushKeyForId(prefix, id), notBefore=notBefore,
- priority=priority.value)
+ yield txn.enqueue(
+ PushNotificationWork,
+ pushID=self.pushKeyForId(prefix, id),
+ notBefore=datetime.datetime.utcnow() + datetime.timedelta(seconds=self.coalesceSeconds),
+ pushPriority=priority.value
+ )
def newNotifier(self, storeObject):
Modified: CalendarServer/trunk/calendarserver/push/test/test_notifier.py
===================================================================
--- CalendarServer/trunk/calendarserver/push/test/test_notifier.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/calendarserver/push/test/test_notifier.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -115,7 +115,7 @@
txn = self._sqlCalendarStore.newTransaction()
wp = (yield txn.enqueue(PushNotificationWork,
pushID="/CalDAV/localhost/foo/",
- priority=PushPriority.high.value
+ pushPriority=PushPriority.high.value
))
yield txn.commit()
yield wp.whenExecuted()
@@ -125,26 +125,26 @@
pushDistributor.reset()
txn = self._sqlCalendarStore.newTransaction()
proposals = []
- wp = txn.enqueue(PushNotificationWork,
+ wp = yield txn.enqueue(PushNotificationWork,
pushID="/CalDAV/localhost/bar/",
- priority=PushPriority.high.value
+ pushPriority=PushPriority.high.value
)
proposals.append(wp.whenExecuted())
- wp = txn.enqueue(PushNotificationWork,
+ wp = yield txn.enqueue(PushNotificationWork,
pushID="/CalDAV/localhost/bar/",
- priority=PushPriority.high.value
+ pushPriority=PushPriority.high.value
)
proposals.append(wp.whenExecuted())
- wp = txn.enqueue(PushNotificationWork,
+ wp = yield txn.enqueue(PushNotificationWork,
pushID="/CalDAV/localhost/bar/",
- priority=PushPriority.high.value
+ pushPriority=PushPriority.high.value
)
proposals.append(wp.whenExecuted())
# Enqueue a different pushID to ensure those are not grouped with
# the others:
- wp = txn.enqueue(PushNotificationWork,
+ wp = yield txn.enqueue(PushNotificationWork,
pushID="/CalDAV/localhost/baz/",
- priority=PushPriority.high.value
+ pushPriority=PushPriority.high.value
)
proposals.append(wp.whenExecuted())
@@ -159,19 +159,19 @@
pushDistributor.reset()
txn = self._sqlCalendarStore.newTransaction()
proposals = []
- wp = txn.enqueue(PushNotificationWork,
+ wp = yield txn.enqueue(PushNotificationWork,
pushID="/CalDAV/localhost/bar/",
- priority=PushPriority.low.value
+ pushPriority=PushPriority.low.value
)
proposals.append(wp.whenExecuted())
- wp = txn.enqueue(PushNotificationWork,
+ wp = yield txn.enqueue(PushNotificationWork,
pushID="/CalDAV/localhost/bar/",
- priority=PushPriority.high.value
+ pushPriority=PushPriority.high.value
)
proposals.append(wp.whenExecuted())
- wp = txn.enqueue(PushNotificationWork,
+ wp = yield txn.enqueue(PushNotificationWork,
pushID="/CalDAV/localhost/bar/",
- priority=PushPriority.medium.value
+ pushPriority=PushPriority.medium.value
)
proposals.append(wp.whenExecuted())
yield txn.commit()
Modified: CalendarServer/trunk/calendarserver/tap/caldav.py
===================================================================
--- CalendarServer/trunk/calendarserver/tap/caldav.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/calendarserver/tap/caldav.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -55,9 +55,9 @@
from twext.enterprise.adbapi2 import ConnectionPool
from twext.enterprise.ienterprise import ORACLE_DIALECT
from twext.enterprise.ienterprise import POSTGRES_DIALECT
-from twext.enterprise.queue import NonPerformingQueuer
-from twext.enterprise.queue import PeerConnectionPool
-from twext.enterprise.queue import WorkerFactory as QueueWorkerFactory
+from twext.enterprise.jobqueue import NonPerformingQueuer
+from twext.enterprise.jobqueue import PeerConnectionPool
+from twext.enterprise.jobqueue import WorkerFactory as QueueWorkerFactory
from twext.internet.fswatch import DirectoryChangeListener, IDirectoryChangeListenee
from twext.internet.ssl import ChainingOpenSSLContextFactory
from twext.internet.tcp import MaxAcceptTCPServer, MaxAcceptSSLServer
@@ -527,26 +527,27 @@
if (
config.DirectoryProxy.Enabled and
config.DirectoryProxy.SocketPath != ""
- ):
- log.info("Adding directory proxy service")
+ ):
+ log.info("Adding directory proxy service")
- dpsArgv = [
- sys.executable,
- sys.argv[0],
- ]
- if config.UserName:
- dpsArgv.extend(("-u", config.UserName))
- if config.GroupName:
- dpsArgv.extend(("-g", config.GroupName))
- dpsArgv.extend((
- "--reactor=%s" % (config.Twisted.reactor,),
- "-n", "caldav_directoryproxy",
- "-f", self.configPath,
- ))
- self.monitor.addProcess("directoryproxy", dpsArgv,
- env=PARENT_ENVIRONMENT)
+ dpsArgv = [
+ sys.executable,
+ sys.argv[0],
+ ]
+ if config.UserName:
+ dpsArgv.extend(("-u", config.UserName))
+ if config.GroupName:
+ dpsArgv.extend(("-g", config.GroupName))
+ dpsArgv.extend((
+ "--reactor=%s" % (config.Twisted.reactor,),
+ "-n", "caldav_directoryproxy",
+ "-f", self.configPath,
+ ))
+ self.monitor.addProcess("directoryproxy", dpsArgv,
+ env=PARENT_ENVIRONMENT)
+
class WorkSchedulingService(Service):
"""
A Service to kick off the initial scheduling of periodic work items.
@@ -1727,7 +1728,7 @@
raise StoreNotAvailable()
from twisted.internet import reactor
- pool = PeerConnectionPool(reactor, store.newTransaction, config.WorkQueue.ampPort, schema)
+ pool = PeerConnectionPool(reactor, store.newTransaction, config.WorkQueue.ampPort)
store.queuer = store.queuer.transferProposalCallbacks(pool)
controlSocket.addFactory(_QUEUE_ROUTE, pool.workerListenerFactory())
# TODO: now that we have the shared control socket, we should get
Modified: CalendarServer/trunk/calendarserver/tap/test/test_caldav.py
===================================================================
--- CalendarServer/trunk/calendarserver/tap/test/test_caldav.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/calendarserver/tap/test/test_caldav.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -64,7 +64,7 @@
QuitAfterUpgradeStep, DataStoreMonitor
)
from calendarserver.provision.root import RootResource
-from twext.enterprise.queue import PeerConnectionPool, LocalQueuer
+from twext.enterprise.jobqueue import PeerConnectionPool, LocalQueuer
from StringIO import StringIO
log = Logger()
Modified: CalendarServer/trunk/calendarserver/tools/cmdline.py
===================================================================
--- CalendarServer/trunk/calendarserver/tools/cmdline.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/calendarserver/tools/cmdline.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -31,7 +31,7 @@
import sys
from calendarserver.tap.util import getRootResource
from errno import ENOENT, EACCES
-from twext.enterprise.queue import NonPerformingQueuer
+from twext.enterprise.jobqueue import NonPerformingQueuer
# TODO: direct unit tests for these functions.
Added: CalendarServer/trunk/calendarserver/tools/jobitems.py
===================================================================
--- CalendarServer/trunk/calendarserver/tools/jobitems.py (rev 0)
+++ CalendarServer/trunk/calendarserver/tools/jobitems.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -0,0 +1,199 @@
+#!/usr/bin/env python
+
+##
+# Copyright (c) 2006-2014 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.
+##
+from __future__ import print_function
+
+from getopt import getopt, GetoptError
+import os
+import sys
+import curses
+
+from twisted.internet.defer import inlineCallbacks, succeed
+from calendarserver.tools.cmdline import utilityMain, WorkerService
+from twext.enterprise.jobqueue import JobItem
+
+useCurses = True
+
+def usage(e=None):
+
+ name = os.path.basename(sys.argv[0])
+ print("usage: %s [options]" % (name,))
+ print("")
+ print(" TODO: describe usage")
+ print("")
+ print("options:")
+ print(" -h --help: print this help and exit")
+ print(" -e --error: send stderr to stdout")
+ print(" -f --config <path>: specify caldavd.plist configuration path")
+ print(" -t: text output, not curses")
+ print("")
+
+ if e:
+ sys.exit(64)
+ else:
+ sys.exit(0)
+
+
+BOX_WIDTH = 52
+
+def main():
+
+ try:
+ (optargs, _ignore_args) = getopt(
+ sys.argv[1:], "hef:t", [
+ "help",
+ "error",
+ "config=",
+ ],
+ )
+ except GetoptError, e:
+ usage(e)
+
+ #
+ # Get configuration
+ #
+ global useCurses
+ configFileName = None
+ debug = False
+
+ for opt, arg in optargs:
+ if opt in ("-h", "--help"):
+ usage()
+
+ if opt in ("-e", "--error"):
+ debug = True
+
+ elif opt in ("-f", "--config"):
+ configFileName = arg
+
+ elif opt in ("-t"):
+ useCurses = False
+
+ else:
+ raise NotImplementedError(opt)
+
+ utilityMain(configFileName, JobItemMonitorService, verbose=debug)
+
+
+
+class JobItemMonitorService(WorkerService, object):
+
+ def __init__(self, store):
+ super(JobItemMonitorService, self).__init__(store)
+ from twisted.internet import reactor
+ self.reactor = reactor
+
+
+ def doWork(self):
+ if useCurses:
+ self.screen = curses.initscr()
+ curses.curs_set(0)
+ else:
+ self.screen = None
+ self.windows = []
+ self.updateScreenGeometry()
+ self.reactor.callLater(0, self.updateDisplay)
+ return succeed(None)
+
+
+ def postStartService(self):
+ """
+ Don't quit right away
+ """
+ pass
+
+
+ def updateScreenGeometry(self):
+ for win in self.windows:
+ del win
+ window = WorkWindow(JobItem.numberOfWorkTypes() + 5, BOX_WIDTH, 0, 0, self.store, "Jobs")
+ self.windows.append(window)
+
+
+ @inlineCallbacks
+ def updateDisplay(self):
+ for window in self.windows:
+ try:
+ yield window.update()
+ except Exception as e:
+ print(str(e))
+ if not useCurses:
+ print("-------------")
+
+ self.reactor.callLater(0.1, self.updateDisplay)
+
+
+
+class WorkWindow(object):
+ def __init__(self, nlines, ncols, begin_y, begin_x, store, title):
+ self.window = curses.newwin(nlines, ncols, begin_y, begin_x) if useCurses else None
+ self.ncols = ncols
+ self.store = store
+ self.title = title
+ self.iter = 0
+ self.lastResult = {}
+
+
+ @inlineCallbacks
+ def update(self):
+ txn = self.store.newTransaction()
+ records = (yield JobItem.histogram(txn))
+ self.iter += 1
+
+ if useCurses:
+ self.window.erase()
+ self.window.border()
+ self.window.addstr(0, 2, self.title + " {} ({})".format(len(records), self.iter,))
+
+ x = 1
+ y = 1
+ s = " {:<40}{:>8} ".format("Work Type", "Count")
+ if useCurses:
+ self.window.addstr(y, x, s, curses.A_REVERSE)
+ else:
+ print(s)
+ y += 1
+ for work_type, count in sorted(records.items(), key=lambda x: x[0]):
+ changed = work_type in self.lastResult and self.lastResult[work_type] != count
+ s = "{}{:<40}{:>8} ".format(">" if count else " ", work_type, count)
+ try:
+ if useCurses:
+ self.window.addstr(y, x, s, curses.A_REVERSE if changed else (curses.A_BOLD if count else curses.A_NORMAL))
+ else:
+ print(s)
+ except curses.error:
+ pass
+ y += 1
+
+ s = " {:<40}{:>8} ".format("Total:", sum(records.values()))
+ if useCurses:
+ self.window.hline(y, x, "-", BOX_WIDTH - 2)
+ y += 1
+ self.window.addstr(y, x, s)
+ else:
+ print(s)
+ y += 1
+
+ if useCurses:
+ self.window.refresh()
+
+ self.lastResult = records
+ yield txn.commit()
+
+
+if __name__ == "__main__":
+ main()
Property changes on: CalendarServer/trunk/calendarserver/tools/jobitems.py
___________________________________________________________________
Added: svn:executable
+ *
Modified: CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/delivery.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/delivery.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/delivery.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -19,7 +19,7 @@
Handles the sending of scheduling messages via iMIP (mail gateway).
"""
-from twext.enterprise.queue import inTransaction
+from twext.enterprise.jobqueue import inTransaction
from twext.python.log import Logger
from txweb2 import responsecode
from txweb2.dav.http import ErrorResponse
Modified: CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/inbound.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/inbound.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/inbound.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -20,7 +20,7 @@
from twext.enterprise.dal.record import fromTable
from twext.enterprise.dal.syntax import Delete
-from twext.enterprise.queue import WorkItem
+from twext.enterprise.jobqueue import WorkItem
from twext.internet.gaiendpoint import GAIEndpoint
from twext.python.log import Logger, LegacyLogger
Modified: CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/outbound.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/outbound.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/caldav/datastore/scheduling/imip/outbound.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -29,7 +29,7 @@
from pycalendar.datetime import DateTime
from pycalendar.duration import Duration
from twext.enterprise.dal.record import fromTable
-from twext.enterprise.queue import WorkItem
+from twext.enterprise.jobqueue import WorkItem
from twext.python.log import Logger
from twisted.internet.defer import inlineCallbacks, returnValue
from twisted.mail.smtp import messageid, rfc822date
Modified: CalendarServer/trunk/txdav/caldav/datastore/scheduling/work.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/scheduling/work.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/caldav/datastore/scheduling/work.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -17,7 +17,7 @@
from twext.enterprise.dal.record import fromTable
from twext.enterprise.dal.syntax import Select, Insert, Delete, Parameter
from twext.enterprise.locking import NamedLock
-from twext.enterprise.queue import WorkItem
+from twext.enterprise.jobqueue import WorkItem
from twext.python.log import Logger
from twisted.internet.defer import inlineCallbacks, returnValue, Deferred
Modified: CalendarServer/trunk/txdav/caldav/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/caldav/datastore/sql.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/caldav/datastore/sql.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -35,7 +35,7 @@
from twext.enterprise.dal.syntax import Update
from twext.enterprise.dal.syntax import utcNowSQL
from twext.enterprise.locking import NamedLock
-from twext.enterprise.queue import WorkItem
+from twext.enterprise.jobqueue import WorkItem
from twext.enterprise.util import parseSQLTimestamp
from twext.python.clsprop import classproperty
from twext.python.filepath import CachingFilePath
Modified: CalendarServer/trunk/txdav/common/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/sql.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -34,7 +34,7 @@
Select, Update, ColumnSyntax, TableSyntax, Upper, Count, ALL_COLUMNS, Sum,
DatabaseLock, DatabaseUnlock)
from twext.enterprise.ienterprise import AlreadyFinishedError
-from twext.enterprise.queue import LocalQueuer
+from twext.enterprise.jobqueue import LocalQueuer
from twext.enterprise.util import parseSQLTimestamp
from twext.internet.decorate import memoizedKey, Memoizable
from twext.python.clsprop import classproperty
@@ -138,7 +138,7 @@
@type quota: C{int} or C{NoneType}
@ivar queuer: An object with an C{enqueueWork} method, from
- L{twext.enterprise.queue}. Initially, this is a L{LocalQueuer}, so it
+ L{twext.enterprise.jobqueue}. Initially, this is a L{LocalQueuer}, so it
is always usable, but in a properly configured environment it will be
upgraded to a more capable object that can distribute work throughout a
cluster.
@@ -544,7 +544,7 @@
def enqueue(self, workItem, **kw):
"""
- Enqueue a L{twext.enterprise.queue.WorkItem} for later execution.
+ Enqueue a L{twext.enterprise.jobqueue.WorkItem} for later execution.
For example::
@@ -553,7 +553,7 @@
@return: a work proposal describing various events in the work's
life-cycle.
- @rtype: L{twext.enterprise.queue.WorkProposal}
+ @rtype: L{twext.enterprise.jobqueue.WorkProposal}
"""
return self._store.queuer.enqueueWork(self, workItem, **kw)
Modified: CalendarServer/trunk/txdav/common/datastore/sql_dump.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_dump.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/sql_dump.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -16,7 +16,7 @@
##
from twisted.internet.defer import inlineCallbacks, returnValue
-from twext.enterprise.dal.model import Schema, Table, Column, Sequence
+from twext.enterprise.dal.model import Schema, Table, Column, Sequence, Function
from twext.enterprise.dal.parseschema import addSQLToSchema
"""
@@ -61,4 +61,10 @@
name = row[0]
Sequence(schema, name)
+ # Functions
+ rows = yield txn.execSQL("select routine_name from information_schema.routines where routine_schema = '%s';" % (schemaname,))
+ for row in rows:
+ name = row[0]
+ Function(schema, name)
+
returnValue(schema)
Added: CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect-extras.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect-extras.sql (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect-extras.sql 2014-03-01 16:21:25 UTC (rev 12782)
@@ -0,0 +1,10 @@
+create or replace function next_job return integer is
+ cursor c1 is select JOB_ID from JOB for update skip locked;
+ result integer;
+begin
+ open c1;
+ fetch c1 into result;
+ select JOB_ID from JOB where ID = result for update;
+ return result;
+end;
+/
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect.sql 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/current-oracle-dialect.sql 2014-03-01 16:21:25 UTC (rev 12782)
@@ -1,4 +1,5 @@
create sequence RESOURCE_ID_SEQ;
+create sequence JOB_SEQ;
create sequence INSTANCE_ID_SEQ;
create sequence ATTACHMENT_ID_SEQ;
create sequence REVISION_SEQ;
@@ -15,6 +16,15 @@
"LOCK_NAME" nvarchar2(255) primary key
);
+create table JOB (
+ "JOB_ID" integer primary key not null,
+ "WORK_TYPE" nvarchar2(255),
+ "PRIORITY" integer default 0,
+ "WEIGHT" integer default 0,
+ "NOT_BEFORE" timestamp default null,
+ "NOT_AFTER" timestamp default null
+);
+
create table CALENDAR_HOME (
"RESOURCE_ID" integer primary key,
"OWNER_UID" nvarchar2(255) unique,
@@ -348,7 +358,7 @@
create table IMIP_INVITATION_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"FROM_ADDR" nvarchar2(255),
"TO_ADDR" nvarchar2(255),
"ICALENDAR_TEXT" nclob
@@ -356,12 +366,12 @@
create table IMIP_POLLING_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+ "JOB_ID" integer not null references JOB
);
create table IMIP_REPLY_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"ORGANIZER" nvarchar2(255),
"ATTENDEE" nvarchar2(255),
"ICALENDAR_TEXT" nclob
@@ -369,25 +379,25 @@
create table PUSH_NOTIFICATION_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"PUSH_ID" nvarchar2(255),
- "PRIORITY" integer not null
+ "PUSH_PRIORITY" integer not null
);
create table GROUP_CACHER_POLLING_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+ "JOB_ID" integer not null references JOB
);
create table GROUP_REFRESH_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"GROUP_GUID" nvarchar2(255)
);
create table GROUP_ATTENDEE_RECONCILIATION_ (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"RESOURCE_ID" integer,
"GROUP_ID" integer
);
@@ -434,34 +444,34 @@
create table CALENDAR_OBJECT_SPLITTER_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade
);
create table FIND_MIN_VALID_REVISION_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+ "JOB_ID" integer not null references JOB
);
create table REVISION_CLEANUP_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+ "JOB_ID" integer not null references JOB
);
create table INBOX_CLEANUP_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+ "JOB_ID" integer not null references JOB
);
create table CLEANUP_ONE_INBOX_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"HOME_ID" integer not null unique references CALENDAR_HOME on delete cascade
);
create table SCHEDULE_REFRESH_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"ICALENDAR_UID" nvarchar2(255),
"HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
"RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade,
@@ -475,7 +485,7 @@
create table SCHEDULE_AUTO_REPLY_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"ICALENDAR_UID" nvarchar2(255),
"HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
"RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade,
@@ -484,7 +494,7 @@
create table SCHEDULE_ORGANIZER_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"ICALENDAR_UID" nvarchar2(255),
"SCHEDULE_ACTION" integer not null,
"HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
@@ -506,7 +516,7 @@
insert into SCHEDULE_ACTION (DESCRIPTION, ID) values ('remove', 3);
create table SCHEDULE_REPLY_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"ICALENDAR_UID" nvarchar2(255),
"HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
"RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade,
@@ -515,7 +525,7 @@
create table SCHEDULE_REPLY_CANCEL_WORK (
"WORK_ID" integer primary key not null,
- "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "JOB_ID" integer not null references JOB,
"ICALENDAR_UID" nvarchar2(255),
"HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
"ICALENDAR_TEXT" nclob
@@ -526,7 +536,7 @@
"VALUE" nvarchar2(255)
);
-insert into CALENDARSERVER (NAME, VALUE) values ('VERSION', '36');
+insert into CALENDARSERVER (NAME, VALUE) values ('VERSION', '37');
insert into CALENDARSERVER (NAME, VALUE) values ('CALENDAR-DATAVERSION', '5');
insert into CALENDARSERVER (NAME, VALUE) values ('ADDRESSBOOK-DATAVERSION', '2');
insert into CALENDARSERVER (NAME, VALUE) values ('NOTIFICATION-DATAVERSION', '1');
@@ -660,6 +670,34 @@
TOKEN
);
+create index IMIP_INVITATION_WORK__586d064c on IMIP_INVITATION_WORK (
+ JOB_ID
+);
+
+create index IMIP_POLLING_WORK_JOB_d5535891 on IMIP_POLLING_WORK (
+ JOB_ID
+);
+
+create index IMIP_REPLY_WORK_JOB_I_bf4ae73e on IMIP_REPLY_WORK (
+ JOB_ID
+);
+
+create index PUSH_NOTIFICATION_WOR_8bbab117 on PUSH_NOTIFICATION_WORK (
+ JOB_ID
+);
+
+create index GROUP_CACHER_POLLING__6eb3151c on GROUP_CACHER_POLLING_WORK (
+ JOB_ID
+);
+
+create index GROUP_REFRESH_WORK_JO_717ede20 on GROUP_REFRESH_WORK (
+ JOB_ID
+);
+
+create index GROUP_ATTENDEE_RECONC_cd2d61b9 on GROUP_ATTENDEE_RECONCILIATION_ (
+ JOB_ID
+);
+
create index GROUPS_GROUP_GUID_ebf7a1d4 on GROUPS (
GROUP_GUID
);
@@ -676,6 +714,26 @@
RESOURCE_ID
);
+create index CALENDAR_OBJECT_SPLIT_33603b72 on CALENDAR_OBJECT_SPLITTER_WORK (
+ JOB_ID
+);
+
+create index FIND_MIN_VALID_REVISI_78d17400 on FIND_MIN_VALID_REVISION_WORK (
+ JOB_ID
+);
+
+create index REVISION_CLEANUP_WORK_eb062686 on REVISION_CLEANUP_WORK (
+ JOB_ID
+);
+
+create index INBOX_CLEANUP_WORK_JO_799132bd on INBOX_CLEANUP_WORK (
+ JOB_ID
+);
+
+create index CLEANUP_ONE_INBOX_WOR_375dac36 on CLEANUP_ONE_INBOX_WORK (
+ JOB_ID
+);
+
create index SCHEDULE_REFRESH_WORK_26084c7b on SCHEDULE_REFRESH_WORK (
HOME_RESOURCE_ID
);
@@ -684,6 +742,10 @@
RESOURCE_ID
);
+create index SCHEDULE_REFRESH_WORK_3ffa2718 on SCHEDULE_REFRESH_WORK (
+ JOB_ID
+);
+
create index SCHEDULE_REFRESH_ATTE_83053b91 on SCHEDULE_REFRESH_ATTENDEES (
RESOURCE_ID,
ATTENDEE
@@ -697,6 +759,10 @@
RESOURCE_ID
);
+create index SCHEDULE_AUTO_REPLY_W_4d7bb5a8 on SCHEDULE_AUTO_REPLY_WORK (
+ JOB_ID
+);
+
create index SCHEDULE_ORGANIZER_WO_18ce4edd on SCHEDULE_ORGANIZER_WORK (
HOME_RESOURCE_ID
);
@@ -705,6 +771,10 @@
RESOURCE_ID
);
+create index SCHEDULE_ORGANIZER_WO_1e9f246d on SCHEDULE_ORGANIZER_WORK (
+ JOB_ID
+);
+
create index SCHEDULE_REPLY_WORK_H_745af8cf on SCHEDULE_REPLY_WORK (
HOME_RESOURCE_ID
);
@@ -713,7 +783,29 @@
RESOURCE_ID
);
+create index SCHEDULE_REPLY_WORK_J_5913b4a4 on SCHEDULE_REPLY_WORK (
+ JOB_ID
+);
+
create index SCHEDULE_REPLY_CANCEL_dab513ef on SCHEDULE_REPLY_CANCEL_WORK (
HOME_RESOURCE_ID
);
+create index SCHEDULE_REPLY_CANCEL_94a0c766 on SCHEDULE_REPLY_CANCEL_WORK (
+ JOB_ID
+);
+
+-- Skipped Function next_job
+
+-- Extras
+
+create or replace function next_job return integer is
+ cursor c1 is select ID from JOB for update skip locked;
+ result integer;
+begin
+ open c1;
+ fetch c1 into result;
+ select ID from JOB where ID = result for update;
+ return result;
+end;
+/
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql 2014-03-01 16:21:25 UTC (rev 12782)
@@ -47,6 +47,30 @@
);
+--------------------
+-- Jobs --
+--------------------
+
+create sequence JOB_SEQ;
+
+create table JOB (
+ JOB_ID integer primary key default nextval('JOB_SEQ') not null, --implicit index
+ WORK_TYPE varchar(255) not null,
+ PRIORITY integer default 0,
+ WEIGHT integer default 0,
+ NOT_BEFORE timestamp default null,
+ NOT_AFTER timestamp default null
+);
+
+create or replace function next_job() returns integer as $$
+declare
+ result integer;
+begin
+ select JOB_ID into result from JOB where pg_try_advisory_xact_lock(JOB_ID) limit 1 for update;
+ return result;
+end
+$$ LANGUAGE plpgsql;
+
-------------------
-- Calendar Home --
-------------------
@@ -98,11 +122,11 @@
);
create index CALENDAR_HOME_METADATA_DEFAULT_EVENTS on
- CALENDAR_HOME_METADATA(DEFAULT_EVENTS);
+ CALENDAR_HOME_METADATA(DEFAULT_EVENTS);
create index CALENDAR_HOME_METADATA_DEFAULT_TASKS on
- CALENDAR_HOME_METADATA(DEFAULT_TASKS);
+ CALENDAR_HOME_METADATA(DEFAULT_TASKS);
create index CALENDAR_HOME_METADATA_DEFAULT_POLLS on
- CALENDAR_HOME_METADATA(DEFAULT_POLLS);
+ CALENDAR_HOME_METADATA(DEFAULT_POLLS);
-----------------------
@@ -142,7 +166,7 @@
);
create index NOTIFICATION_NOTIFICATION_HOME_RESOURCE_ID on
- NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);
+ NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);
-------------------
@@ -154,11 +178,11 @@
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,
- EXTERNAL_ID integer default null,
+ EXTERNAL_ID integer default null,
CALENDAR_RESOURCE_NAME varchar(255) not null,
BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
- BIND_REVISION integer default 0 not null,
+ BIND_REVISION integer default 0 not null,
MESSAGE text,
TRANSP integer default 0 not null, -- enum CALENDAR_TRANSP
ALARM_VEVENT_TIMED text default null,
@@ -172,7 +196,7 @@
);
create index CALENDAR_BIND_RESOURCE_ID on
- CALENDAR_BIND(CALENDAR_RESOURCE_ID);
+ CALENDAR_BIND(CALENDAR_RESOURCE_ID);
-- Enumeration of calendar bind modes
@@ -375,7 +399,7 @@
);
create index ATTACHMENT_CALENDAR_OBJECT_CALENDAR_OBJECT_RESOURCE_ID on
- ATTACHMENT_CALENDAR_OBJECT(CALENDAR_OBJECT_RESOURCE_ID);
+ ATTACHMENT_CALENDAR_OBJECT(CALENDAR_OBJECT_RESOURCE_ID);
-----------------------
-- Resource Property --
@@ -396,11 +420,11 @@
----------------------
create table ADDRESSBOOK_HOME (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- ADDRESSBOOK_PROPERTY_STORE_ID integer default nextval('RESOURCE_ID_SEQ') not null, -- implicit index
- OWNER_UID varchar(255) not null unique, -- implicit index
- STATUS integer default 0 not null, -- enum HOME_STATUS
- DATAVERSION integer default 0 not null
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ ADDRESSBOOK_PROPERTY_STORE_ID integer default nextval('RESOURCE_ID_SEQ') not null, -- implicit index
+ OWNER_UID varchar(255) not null unique, -- implicit index
+ STATUS integer default 0 not null, -- enum HOME_STATUS
+ DATAVERSION integer default 0 not null
);
@@ -423,16 +447,16 @@
-- Joins sharee ADDRESSBOOK_HOME and owner ADDRESSBOOK_HOME
create table SHARED_ADDRESSBOOK_BIND (
- ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
- OWNER_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
- EXTERNAL_ID integer default null,
- ADDRESSBOOK_RESOURCE_NAME varchar(255) not null,
- BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
- BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
- BIND_REVISION integer default 0 not null,
- MESSAGE text, -- FIXME: xml?
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ OWNER_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
+ EXTERNAL_ID integer default null,
+ ADDRESSBOOK_RESOURCE_NAME varchar(255) not null,
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ BIND_REVISION integer default 0 not null,
+ MESSAGE text, -- FIXME: xml?
- primary key (ADDRESSBOOK_HOME_RESOURCE_ID, OWNER_HOME_RESOURCE_ID), -- implicit index
+ primary key (ADDRESSBOOK_HOME_RESOURCE_ID, OWNER_HOME_RESOURCE_ID), -- implicit index
unique (ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME) -- implicit index
);
@@ -445,15 +469,15 @@
------------------------
create table ADDRESSBOOK_OBJECT (
- RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
- ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
- RESOURCE_NAME varchar(255) not null,
- VCARD_TEXT text not null,
- VCARD_UID varchar(255) not null,
- KIND integer not null, -- enum ADDRESSBOOK_OBJECT_KIND
- MD5 char(32) not null,
- CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
+ RESOURCE_NAME varchar(255) not null,
+ VCARD_TEXT text not null,
+ VCARD_UID varchar(255) not null,
+ KIND integer not null, -- enum ADDRESSBOOK_OBJECT_KIND
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
unique (ADDRESSBOOK_HOME_RESOURCE_ID, RESOURCE_NAME), -- implicit index
unique (ADDRESSBOOK_HOME_RESOURCE_ID, VCARD_UID) -- implicit index
@@ -486,35 +510,35 @@
---------------------------------
create table ABO_MEMBERS (
- GROUP_ID integer not null, -- references ADDRESSBOOK_OBJECT on delete cascade, -- AddressBook Object's (kind=='group') RESOURCE_ID
- ADDRESSBOOK_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
- MEMBER_ID integer not null, -- references ADDRESSBOOK_OBJECT, -- member AddressBook Object's RESOURCE_ID
- REVISION integer default nextval('REVISION_SEQ') not null,
- REMOVED boolean default false not null,
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ GROUP_ID integer not null, -- references ADDRESSBOOK_OBJECT on delete cascade, -- AddressBook Object's (kind=='group') RESOURCE_ID
+ ADDRESSBOOK_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
+ MEMBER_ID integer not null, -- references ADDRESSBOOK_OBJECT, -- member AddressBook Object's RESOURCE_ID
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ REMOVED boolean default false not null,
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
primary key (GROUP_ID, MEMBER_ID, REVISION) -- implicit index
);
create index ABO_MEMBERS_ADDRESSBOOK_ID on
- ABO_MEMBERS(ADDRESSBOOK_ID);
+ ABO_MEMBERS(ADDRESSBOOK_ID);
create index ABO_MEMBERS_MEMBER_ID on
- ABO_MEMBERS(MEMBER_ID);
+ ABO_MEMBERS(MEMBER_ID);
------------------------------------------
-- Address Book Object Foreign Members --
------------------------------------------
create table ABO_FOREIGN_MEMBERS (
- GROUP_ID integer not null references ADDRESSBOOK_OBJECT on delete cascade, -- AddressBook Object's (kind=='group') RESOURCE_ID
- ADDRESSBOOK_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
- MEMBER_ADDRESS varchar(255) not null, -- member AddressBook Object's 'calendar' address
+ GROUP_ID integer not null references ADDRESSBOOK_OBJECT on delete cascade, -- AddressBook Object's (kind=='group') RESOURCE_ID
+ ADDRESSBOOK_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
+ MEMBER_ADDRESS varchar(255) not null, -- member AddressBook Object's 'calendar' address
- primary key (GROUP_ID, MEMBER_ADDRESS) -- implicit index
+ primary key (GROUP_ID, MEMBER_ADDRESS) -- implicit index
);
create index ABO_FOREIGN_MEMBERS_ADDRESSBOOK_ID on
- ABO_FOREIGN_MEMBERS(ADDRESSBOOK_ID);
+ ABO_FOREIGN_MEMBERS(ADDRESSBOOK_ID);
-----------------------
-- Shared Group Bind --
@@ -523,14 +547,14 @@
-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK_OBJECT (kind == group)
create table SHARED_GROUP_BIND (
- ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
- GROUP_RESOURCE_ID integer not null references ADDRESSBOOK_OBJECT on delete cascade,
- EXTERNAL_ID integer default null,
- GROUP_ADDRESSBOOK_NAME varchar(255) not null,
- BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
- BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
- BIND_REVISION integer default 0 not null,
- MESSAGE text, -- FIXME: xml?
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ GROUP_RESOURCE_ID integer not null references ADDRESSBOOK_OBJECT on delete cascade,
+ EXTERNAL_ID integer default null,
+ GROUP_ADDRESSBOOK_NAME varchar(255) not null,
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ BIND_REVISION integer default 0 not null,
+ MESSAGE text, -- FIXME: xml?
primary key (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_RESOURCE_ID), -- implicit index
unique (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_ADDRESSBOOK_NAME) -- implicit index
@@ -576,14 +600,14 @@
----------------------------------
create table ADDRESSBOOK_OBJECT_REVISIONS (
- ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
- OWNER_HOME_RESOURCE_ID integer references ADDRESSBOOK_HOME,
- ADDRESSBOOK_NAME varchar(255) default null,
- OBJECT_RESOURCE_ID integer default 0,
- RESOURCE_NAME varchar(255),
- REVISION integer default nextval('REVISION_SEQ') not null,
- DELETED boolean not null,
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ OWNER_HOME_RESOURCE_ID integer references ADDRESSBOOK_HOME,
+ ADDRESSBOOK_NAME varchar(255) default null,
+ OBJECT_RESOURCE_ID integer default 0,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null,
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
);
create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID_OWNER_HOME_RESOURCE_ID
@@ -605,7 +629,7 @@
RESOURCE_NAME varchar(255),
REVISION integer default nextval('REVISION_SEQ') not null,
DELETED boolean not null,
- MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
unique(NOTIFICATION_HOME_RESOURCE_ID, RESOURCE_NAME) -- implicit index
);
@@ -630,7 +654,7 @@
);
create index APN_SUBSCRIPTIONS_RESOURCE_KEY
- on APN_SUBSCRIPTIONS(RESOURCE_KEY);
+ on APN_SUBSCRIPTIONS(RESOURCE_KEY);
-----------------
@@ -648,7 +672,7 @@
);
create index IMIP_TOKENS_TOKEN
- on IMIP_TOKENS(TOKEN);
+ on IMIP_TOKENS(TOKEN);
----------------
@@ -664,12 +688,14 @@
create table IMIP_INVITATION_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ JOB_ID integer references JOB not null,
FROM_ADDR varchar(255) not null,
TO_ADDR varchar(255) not null,
ICALENDAR_TEXT text not null
);
+create index IMIP_INVITATION_WORK_JOB_ID on
+ IMIP_INVITATION_WORK(JOB_ID);
-----------------------
-- IMIP Polling Work --
@@ -677,57 +703,75 @@
create table IMIP_POLLING_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+ JOB_ID integer references JOB not null
);
+create index IMIP_POLLING_WORK_JOB_ID on
+ IMIP_POLLING_WORK(JOB_ID);
+
---------------------
-- IMIP Reply Work --
---------------------
create table IMIP_REPLY_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ JOB_ID integer references JOB not null,
ORGANIZER varchar(255) not null,
ATTENDEE varchar(255) not null,
ICALENDAR_TEXT text not null
);
+create index IMIP_REPLY_WORK_JOB_ID on
+ IMIP_REPLY_WORK(JOB_ID);
+
------------------------
-- Push Notifications --
------------------------
create table PUSH_NOTIFICATION_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ JOB_ID integer references JOB not null,
PUSH_ID varchar(255) not null,
- PRIORITY integer not null -- 1:low 5:medium 10:high
+ PUSH_PRIORITY integer not null -- 1:low 5:medium 10:high
);
+create index PUSH_NOTIFICATION_WORK_JOB_ID on
+ PUSH_NOTIFICATION_WORK(JOB_ID);
+
-----------------
-- GroupCacher --
-----------------
create table GROUP_CACHER_POLLING_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+ JOB_ID integer references JOB not null
);
+create index GROUP_CACHER_POLLING_WORK_JOB_ID on
+ GROUP_CACHER_POLLING_WORK(JOB_ID);
+
create table GROUP_REFRESH_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ JOB_ID integer references JOB not null,
GROUP_GUID varchar(255) not null
);
+create index GROUP_REFRESH_WORK_JOB_ID on
+ GROUP_REFRESH_WORK(JOB_ID);
+
create table GROUP_ATTENDEE_RECONCILIATION_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ JOB_ID integer references JOB not null,
RESOURCE_ID integer,
GROUP_ID integer
);
+create index GROUP_ATTENDEE_RECONCILIATION_WORK_JOB_ID on
+ GROUP_ATTENDEE_RECONCILIATION_WORK(JOB_ID);
+
create table GROUPS (
GROUP_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
NAME varchar(255) not null,
@@ -737,14 +781,17 @@
CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
);
-create index GROUPS_GROUP_GUID on GROUPS(GROUP_GUID);
+create index GROUPS_GROUP_GUID on
+ GROUPS(GROUP_GUID);
create table GROUP_MEMBERSHIP (
GROUP_ID integer,
MEMBER_GUID varchar(255) not null
);
-create index GROUP_MEMBERSHIP_GROUP on GROUP_MEMBERSHIP(GROUP_ID);
-create index GROUP_MEMBERSHIP_MEMBER on GROUP_MEMBERSHIP(MEMBER_GUID);
+create index GROUP_MEMBERSHIP_GROUP on
+ GROUP_MEMBERSHIP(GROUP_ID);
+create index GROUP_MEMBERSHIP_MEMBER on
+ GROUP_MEMBERSHIP(MEMBER_GUID);
create table GROUP_ATTENDEE (
GROUP_ID integer,
@@ -781,12 +828,14 @@
create table CALENDAR_OBJECT_SPLITTER_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ JOB_ID integer references JOB not null,
RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade
);
create index CALENDAR_OBJECT_SPLITTER_WORK_RESOURCE_ID on
- CALENDAR_OBJECT_SPLITTER_WORK(RESOURCE_ID);
+ CALENDAR_OBJECT_SPLITTER_WORK(RESOURCE_ID);
+create index CALENDAR_OBJECT_SPLITTER_WORK_JOB_ID on
+ CALENDAR_OBJECT_SPLITTER_WORK(JOB_ID);
---------------------------
-- Revision Cleanup Work --
@@ -794,54 +843,68 @@
create table FIND_MIN_VALID_REVISION_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+ JOB_ID integer references JOB not null
);
+create index FIND_MIN_VALID_REVISION_WORK_JOB_ID on
+ FIND_MIN_VALID_REVISION_WORK(JOB_ID);
+
create table REVISION_CLEANUP_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+ JOB_ID integer references JOB not null
);
+create index REVISION_CLEANUP_WORK_JOB_ID on
+ REVISION_CLEANUP_WORK(JOB_ID);
+
------------------------
-- Inbox Cleanup Work --
------------------------
create table INBOX_CLEANUP_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+ JOB_ID integer references JOB not null
);
+create index INBOX_CLEANUP_WORK_JOB_ID on
+ INBOX_CLEANUP_WORK(JOB_ID);
+
create table CLEANUP_ONE_INBOX_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- HOME_ID integer not null unique references CALENDAR_HOME on delete cascade
+ JOB_ID integer references JOB not null,
+ HOME_ID integer not null unique references CALENDAR_HOME on delete cascade
);
+create index CLEANUP_ONE_INBOX_WORK_JOB_ID on
+ CLEANUP_ONE_INBOX_WORK(JOB_ID);
+
---------------------------
-- Schedule Refresh Work --
---------------------------
create table SCHEDULE_REFRESH_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- ICALENDAR_UID varchar(255) not null,
+ JOB_ID integer references JOB not null,
+ ICALENDAR_UID varchar(255) not null,
HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
- ATTENDEE_COUNT integer
+ ATTENDEE_COUNT integer
);
create index SCHEDULE_REFRESH_WORK_HOME_RESOURCE_ID on
- SCHEDULE_REFRESH_WORK(HOME_RESOURCE_ID);
+ SCHEDULE_REFRESH_WORK(HOME_RESOURCE_ID);
create index SCHEDULE_REFRESH_WORK_RESOURCE_ID on
- SCHEDULE_REFRESH_WORK(RESOURCE_ID);
+ SCHEDULE_REFRESH_WORK(RESOURCE_ID);
+create index SCHEDULE_REFRESH_WORK_JOB_ID on
+ SCHEDULE_REFRESH_WORK(JOB_ID);
create table SCHEDULE_REFRESH_ATTENDEES (
RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
- ATTENDEE varchar(255) not null
+ ATTENDEE varchar(255) not null
);
create index SCHEDULE_REFRESH_ATTENDEES_RESOURCE_ID_ATTENDEE on
- SCHEDULE_REFRESH_ATTENDEES(RESOURCE_ID, ATTENDEE);
+ SCHEDULE_REFRESH_ATTENDEES(RESOURCE_ID, ATTENDEE);
------------------------------
-- Schedule Auto Reply Work --
@@ -849,17 +912,19 @@
create table SCHEDULE_AUTO_REPLY_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- ICALENDAR_UID varchar(255) not null,
+ JOB_ID integer references JOB not null,
+ ICALENDAR_UID varchar(255) not null,
HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
- PARTSTAT varchar(255) not null
+ PARTSTAT varchar(255) not null
);
create index SCHEDULE_AUTO_REPLY_WORK_HOME_RESOURCE_ID on
- SCHEDULE_AUTO_REPLY_WORK(HOME_RESOURCE_ID);
+ SCHEDULE_AUTO_REPLY_WORK(HOME_RESOURCE_ID);
create index SCHEDULE_AUTO_REPLY_WORK_RESOURCE_ID on
- SCHEDULE_AUTO_REPLY_WORK(RESOURCE_ID);
+ SCHEDULE_AUTO_REPLY_WORK(RESOURCE_ID);
+create index SCHEDULE_AUTO_REPLY_WORK_JOB_ID on
+ SCHEDULE_AUTO_REPLY_WORK(JOB_ID);
-----------------------------
-- Schedule Organizer Work --
@@ -867,21 +932,23 @@
create table SCHEDULE_ORGANIZER_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- ICALENDAR_UID varchar(255) not null,
- SCHEDULE_ACTION integer not null, -- Enum SCHEDULE_ACTION
+ JOB_ID integer references JOB not null,
+ ICALENDAR_UID varchar(255) not null,
+ SCHEDULE_ACTION integer not null, -- Enum SCHEDULE_ACTION
HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
- RESOURCE_ID integer, -- this references a possibly non-existent CALENDR_OBJECT
- ICALENDAR_TEXT_OLD text,
- ICALENDAR_TEXT_NEW text,
- ATTENDEE_COUNT integer,
- SMART_MERGE boolean
+ RESOURCE_ID integer, -- this references a possibly non-existent CALENDR_OBJECT
+ ICALENDAR_TEXT_OLD text,
+ ICALENDAR_TEXT_NEW text,
+ ATTENDEE_COUNT integer,
+ SMART_MERGE boolean
);
create index SCHEDULE_ORGANIZER_WORK_HOME_RESOURCE_ID on
- SCHEDULE_ORGANIZER_WORK(HOME_RESOURCE_ID);
+ SCHEDULE_ORGANIZER_WORK(HOME_RESOURCE_ID);
create index SCHEDULE_ORGANIZER_WORK_RESOURCE_ID on
- SCHEDULE_ORGANIZER_WORK(RESOURCE_ID);
+ SCHEDULE_ORGANIZER_WORK(RESOURCE_ID);
+create index SCHEDULE_ORGANIZER_WORK_JOB_ID on
+ SCHEDULE_ORGANIZER_WORK(JOB_ID);
-- Enumeration of schedule actions
@@ -901,17 +968,19 @@
create table SCHEDULE_REPLY_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- ICALENDAR_UID varchar(255) not null,
+ JOB_ID integer references JOB not null,
+ ICALENDAR_UID varchar(255) not null,
HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
- CHANGED_RIDS text
+ CHANGED_RIDS text
);
create index SCHEDULE_REPLY_WORK_HOME_RESOURCE_ID on
- SCHEDULE_REPLY_WORK(HOME_RESOURCE_ID);
+ SCHEDULE_REPLY_WORK(HOME_RESOURCE_ID);
create index SCHEDULE_REPLY_WORK_RESOURCE_ID on
- SCHEDULE_REPLY_WORK(RESOURCE_ID);
+ SCHEDULE_REPLY_WORK(RESOURCE_ID);
+create index SCHEDULE_REPLY_WORK_JOB_ID on
+ SCHEDULE_REPLY_WORK(JOB_ID);
--------------------------------
-- Schedule Reply Cancel Work --
@@ -919,14 +988,16 @@
create table SCHEDULE_REPLY_CANCEL_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
- NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
- ICALENDAR_UID varchar(255) not null,
+ JOB_ID integer references JOB not null,
+ ICALENDAR_UID varchar(255) not null,
HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
- ICALENDAR_TEXT text not null
+ ICALENDAR_TEXT text not null
);
create index SCHEDULE_REPLY_CANCEL_WORK_HOME_RESOURCE_ID on
- SCHEDULE_REPLY_CANCEL_WORK(HOME_RESOURCE_ID);
+ SCHEDULE_REPLY_CANCEL_WORK(HOME_RESOURCE_ID);
+create index SCHEDULE_REPLY_CANCEL_WORK_JOB_ID on
+ SCHEDULE_REPLY_CANCEL_WORK(JOB_ID);
--------------------
-- Schema Version --
@@ -937,7 +1008,7 @@
VALUE varchar(255)
);
-insert into CALENDARSERVER values ('VERSION', '36');
+insert into CALENDARSERVER values ('VERSION', '37');
insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '5');
insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '2');
insert into CALENDARSERVER values ('NOTIFICATION-DATAVERSION', '1');
Added: CalendarServer/trunk/txdav/common/datastore/sql_schema/old/oracle-dialect/v36.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/old/oracle-dialect/v36.sql (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/old/oracle-dialect/v36.sql 2014-03-01 16:21:25 UTC (rev 12782)
@@ -0,0 +1,719 @@
+create sequence RESOURCE_ID_SEQ;
+create sequence INSTANCE_ID_SEQ;
+create sequence ATTACHMENT_ID_SEQ;
+create sequence REVISION_SEQ;
+create sequence WORKITEM_SEQ;
+create table NODE_INFO (
+ "HOSTNAME" nvarchar2(255),
+ "PID" integer not null,
+ "PORT" integer not null,
+ "TIME" timestamp default CURRENT_TIMESTAMP at time zone 'UTC' not null,
+ primary key("HOSTNAME", "PORT")
+);
+
+create table NAMED_LOCK (
+ "LOCK_NAME" nvarchar2(255) primary key
+);
+
+create table CALENDAR_HOME (
+ "RESOURCE_ID" integer primary key,
+ "OWNER_UID" nvarchar2(255) unique,
+ "STATUS" integer default 0 not null,
+ "DATAVERSION" integer default 0 not null
+);
+
+create table HOME_STATUS (
+ "ID" integer primary key,
+ "DESCRIPTION" nvarchar2(16) unique
+);
+
+insert into HOME_STATUS (DESCRIPTION, ID) values ('normal', 0);
+insert into HOME_STATUS (DESCRIPTION, ID) values ('external', 1);
+create table CALENDAR (
+ "RESOURCE_ID" integer primary key
+);
+
+create table CALENDAR_HOME_METADATA (
+ "RESOURCE_ID" integer primary key references CALENDAR_HOME on delete cascade,
+ "QUOTA_USED_BYTES" integer default 0 not null,
+ "DEFAULT_EVENTS" integer default null references CALENDAR on delete set null,
+ "DEFAULT_TASKS" integer default null references CALENDAR on delete set null,
+ "DEFAULT_POLLS" integer default null references CALENDAR on delete set null,
+ "ALARM_VEVENT_TIMED" nclob default null,
+ "ALARM_VEVENT_ALLDAY" nclob default null,
+ "ALARM_VTODO_TIMED" nclob default null,
+ "ALARM_VTODO_ALLDAY" nclob default null,
+ "AVAILABILITY" nclob default null,
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table CALENDAR_METADATA (
+ "RESOURCE_ID" integer primary key references CALENDAR on delete cascade,
+ "SUPPORTED_COMPONENTS" nvarchar2(255) default null,
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table NOTIFICATION_HOME (
+ "RESOURCE_ID" integer primary key,
+ "OWNER_UID" nvarchar2(255) unique,
+ "STATUS" integer default 0 not null,
+ "DATAVERSION" integer default 0 not null
+);
+
+create table NOTIFICATION (
+ "RESOURCE_ID" integer primary key,
+ "NOTIFICATION_HOME_RESOURCE_ID" integer not null references NOTIFICATION_HOME,
+ "NOTIFICATION_UID" nvarchar2(255),
+ "NOTIFICATION_TYPE" nvarchar2(255),
+ "NOTIFICATION_DATA" nclob,
+ "MD5" nchar(32),
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ unique("NOTIFICATION_UID", "NOTIFICATION_HOME_RESOURCE_ID")
+);
+
+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,
+ "EXTERNAL_ID" integer default null,
+ "CALENDAR_RESOURCE_NAME" nvarchar2(255),
+ "BIND_MODE" integer not null,
+ "BIND_STATUS" integer not null,
+ "BIND_REVISION" integer default 0 not null,
+ "MESSAGE" nclob,
+ "TRANSP" integer default 0 not null,
+ "ALARM_VEVENT_TIMED" nclob default null,
+ "ALARM_VEVENT_ALLDAY" nclob default null,
+ "ALARM_VTODO_TIMED" nclob default null,
+ "ALARM_VTODO_ALLDAY" nclob default null,
+ "TIMEZONE" nclob default null,
+ primary key("CALENDAR_HOME_RESOURCE_ID", "CALENDAR_RESOURCE_ID"),
+ unique("CALENDAR_HOME_RESOURCE_ID", "CALENDAR_RESOURCE_NAME")
+);
+
+create table CALENDAR_BIND_MODE (
+ "ID" integer primary key,
+ "DESCRIPTION" nvarchar2(16) unique
+);
+
+insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('own', 0);
+insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('read', 1);
+insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('write', 2);
+insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('direct', 3);
+insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('indirect', 4);
+create table CALENDAR_BIND_STATUS (
+ "ID" integer primary key,
+ "DESCRIPTION" nvarchar2(16) unique
+);
+
+insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('invited', 0);
+insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('accepted', 1);
+insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('declined', 2);
+insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('invalid', 3);
+insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('deleted', 4);
+create table CALENDAR_TRANSP (
+ "ID" integer primary key,
+ "DESCRIPTION" nvarchar2(16) unique
+);
+
+insert into CALENDAR_TRANSP (DESCRIPTION, ID) values ('opaque', 0);
+insert into CALENDAR_TRANSP (DESCRIPTION, ID) values ('transparent', 1);
+create table CALENDAR_OBJECT (
+ "RESOURCE_ID" integer primary key,
+ "CALENDAR_RESOURCE_ID" integer not null references CALENDAR on delete cascade,
+ "RESOURCE_NAME" nvarchar2(255),
+ "ICALENDAR_TEXT" nclob,
+ "ICALENDAR_UID" nvarchar2(255),
+ "ICALENDAR_TYPE" nvarchar2(255),
+ "ATTACHMENTS_MODE" integer default 0 not null,
+ "DROPBOX_ID" nvarchar2(255),
+ "ORGANIZER" nvarchar2(255),
+ "RECURRANCE_MIN" date,
+ "RECURRANCE_MAX" date,
+ "ACCESS" integer default 0 not null,
+ "SCHEDULE_OBJECT" integer default 0,
+ "SCHEDULE_TAG" nvarchar2(36) default null,
+ "SCHEDULE_ETAGS" nclob default null,
+ "PRIVATE_COMMENTS" integer default 0 not null,
+ "MD5" nchar(32),
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ unique("CALENDAR_RESOURCE_ID", "RESOURCE_NAME")
+);
+
+create table CALENDAR_OBJECT_ATTACHMENTS_MO (
+ "ID" integer primary key,
+ "DESCRIPTION" nvarchar2(16) unique
+);
+
+insert into CALENDAR_OBJECT_ATTACHMENTS_MO (DESCRIPTION, ID) values ('none', 0);
+insert into CALENDAR_OBJECT_ATTACHMENTS_MO (DESCRIPTION, ID) values ('read', 1);
+insert into CALENDAR_OBJECT_ATTACHMENTS_MO (DESCRIPTION, ID) values ('write', 2);
+create table CALENDAR_ACCESS_TYPE (
+ "ID" integer primary key,
+ "DESCRIPTION" nvarchar2(32) unique
+);
+
+insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('', 0);
+insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('public', 1);
+insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('private', 2);
+insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('confidential', 3);
+insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('restricted', 4);
+create table TIME_RANGE (
+ "INSTANCE_ID" integer primary key,
+ "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" integer not null,
+ "START_DATE" timestamp not null,
+ "END_DATE" timestamp not null,
+ "FBTYPE" integer not null,
+ "TRANSPARENT" integer not null
+);
+
+create table FREE_BUSY_TYPE (
+ "ID" integer primary key,
+ "DESCRIPTION" nvarchar2(16) unique
+);
+
+insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('unknown', 0);
+insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('free', 1);
+insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('busy', 2);
+insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('busy-unavailable', 3);
+insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('busy-tentative', 4);
+create table TRANSPARENCY (
+ "TIME_RANGE_INSTANCE_ID" integer not null references TIME_RANGE on delete cascade,
+ "USER_ID" nvarchar2(255),
+ "TRANSPARENT" integer not null
+);
+
+create table ATTACHMENT (
+ "ATTACHMENT_ID" integer primary key,
+ "CALENDAR_HOME_RESOURCE_ID" integer not null references CALENDAR_HOME,
+ "DROPBOX_ID" nvarchar2(255),
+ "CONTENT_TYPE" nvarchar2(255),
+ "SIZE" integer not null,
+ "MD5" nchar(32),
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "PATH" nvarchar2(1024)
+);
+
+create table ATTACHMENT_CALENDAR_OBJECT (
+ "ATTACHMENT_ID" integer not null references ATTACHMENT on delete cascade,
+ "MANAGED_ID" nvarchar2(255),
+ "CALENDAR_OBJECT_RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade,
+ primary key("ATTACHMENT_ID", "CALENDAR_OBJECT_RESOURCE_ID"),
+ unique("MANAGED_ID", "CALENDAR_OBJECT_RESOURCE_ID")
+);
+
+create table RESOURCE_PROPERTY (
+ "RESOURCE_ID" integer not null,
+ "NAME" nvarchar2(255),
+ "VALUE" nclob,
+ "VIEWER_UID" nvarchar2(255),
+ primary key("RESOURCE_ID", "NAME", "VIEWER_UID")
+);
+
+create table ADDRESSBOOK_HOME (
+ "RESOURCE_ID" integer primary key,
+ "ADDRESSBOOK_PROPERTY_STORE_ID" integer not null,
+ "OWNER_UID" nvarchar2(255) unique,
+ "STATUS" integer default 0 not null,
+ "DATAVERSION" integer default 0 not null
+);
+
+create table ADDRESSBOOK_HOME_METADATA (
+ "RESOURCE_ID" integer primary key references ADDRESSBOOK_HOME on delete cascade,
+ "QUOTA_USED_BYTES" integer default 0 not null,
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table SHARED_ADDRESSBOOK_BIND (
+ "ADDRESSBOOK_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME,
+ "OWNER_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME on delete cascade,
+ "EXTERNAL_ID" integer default null,
+ "ADDRESSBOOK_RESOURCE_NAME" nvarchar2(255),
+ "BIND_MODE" integer not null,
+ "BIND_STATUS" integer not null,
+ "BIND_REVISION" integer default 0 not null,
+ "MESSAGE" nclob,
+ primary key("ADDRESSBOOK_HOME_RESOURCE_ID", "OWNER_HOME_RESOURCE_ID"),
+ unique("ADDRESSBOOK_HOME_RESOURCE_ID", "ADDRESSBOOK_RESOURCE_NAME")
+);
+
+create table ADDRESSBOOK_OBJECT (
+ "RESOURCE_ID" integer primary key,
+ "ADDRESSBOOK_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME on delete cascade,
+ "RESOURCE_NAME" nvarchar2(255),
+ "VCARD_TEXT" nclob,
+ "VCARD_UID" nvarchar2(255),
+ "KIND" integer not null,
+ "MD5" nchar(32),
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ unique("ADDRESSBOOK_HOME_RESOURCE_ID", "RESOURCE_NAME"),
+ unique("ADDRESSBOOK_HOME_RESOURCE_ID", "VCARD_UID")
+);
+
+create table ADDRESSBOOK_OBJECT_KIND (
+ "ID" integer primary key,
+ "DESCRIPTION" nvarchar2(16) unique
+);
+
+insert into ADDRESSBOOK_OBJECT_KIND (DESCRIPTION, ID) values ('person', 0);
+insert into ADDRESSBOOK_OBJECT_KIND (DESCRIPTION, ID) values ('group', 1);
+insert into ADDRESSBOOK_OBJECT_KIND (DESCRIPTION, ID) values ('resource', 2);
+insert into ADDRESSBOOK_OBJECT_KIND (DESCRIPTION, ID) values ('location', 3);
+create table ABO_MEMBERS (
+ "GROUP_ID" integer not null,
+ "ADDRESSBOOK_ID" integer not null references ADDRESSBOOK_HOME on delete cascade,
+ "MEMBER_ID" integer not null,
+ "REVISION" integer not null,
+ "REMOVED" integer default 0 not null,
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ primary key("GROUP_ID", "MEMBER_ID", "REVISION")
+);
+
+create table ABO_FOREIGN_MEMBERS (
+ "GROUP_ID" integer not null references ADDRESSBOOK_OBJECT on delete cascade,
+ "ADDRESSBOOK_ID" integer not null references ADDRESSBOOK_HOME on delete cascade,
+ "MEMBER_ADDRESS" nvarchar2(255),
+ primary key("GROUP_ID", "MEMBER_ADDRESS")
+);
+
+create table SHARED_GROUP_BIND (
+ "ADDRESSBOOK_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME,
+ "GROUP_RESOURCE_ID" integer not null references ADDRESSBOOK_OBJECT on delete cascade,
+ "EXTERNAL_ID" integer default null,
+ "GROUP_ADDRESSBOOK_NAME" nvarchar2(255),
+ "BIND_MODE" integer not null,
+ "BIND_STATUS" integer not null,
+ "BIND_REVISION" integer default 0 not null,
+ "MESSAGE" nclob,
+ primary key("ADDRESSBOOK_HOME_RESOURCE_ID", "GROUP_RESOURCE_ID"),
+ unique("ADDRESSBOOK_HOME_RESOURCE_ID", "GROUP_ADDRESSBOOK_NAME")
+);
+
+create table CALENDAR_OBJECT_REVISIONS (
+ "CALENDAR_HOME_RESOURCE_ID" integer not null references CALENDAR_HOME,
+ "CALENDAR_RESOURCE_ID" integer references CALENDAR,
+ "CALENDAR_NAME" nvarchar2(255) default null,
+ "RESOURCE_NAME" nvarchar2(255),
+ "REVISION" integer not null,
+ "DELETED" integer not null,
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table ADDRESSBOOK_OBJECT_REVISIONS (
+ "ADDRESSBOOK_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME,
+ "OWNER_HOME_RESOURCE_ID" integer references ADDRESSBOOK_HOME,
+ "ADDRESSBOOK_NAME" nvarchar2(255) default null,
+ "OBJECT_RESOURCE_ID" integer default 0,
+ "RESOURCE_NAME" nvarchar2(255),
+ "REVISION" integer not null,
+ "DELETED" integer not null,
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table NOTIFICATION_OBJECT_REVISIONS (
+ "NOTIFICATION_HOME_RESOURCE_ID" integer not null references NOTIFICATION_HOME on delete cascade,
+ "RESOURCE_NAME" nvarchar2(255),
+ "REVISION" integer not null,
+ "DELETED" integer not null,
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ unique("NOTIFICATION_HOME_RESOURCE_ID", "RESOURCE_NAME")
+);
+
+create table APN_SUBSCRIPTIONS (
+ "TOKEN" nvarchar2(255),
+ "RESOURCE_KEY" nvarchar2(255),
+ "MODIFIED" integer not null,
+ "SUBSCRIBER_GUID" nvarchar2(255),
+ "USER_AGENT" nvarchar2(255) default null,
+ "IP_ADDR" nvarchar2(255) default null,
+ primary key("TOKEN", "RESOURCE_KEY")
+);
+
+create table IMIP_TOKENS (
+ "TOKEN" nvarchar2(255),
+ "ORGANIZER" nvarchar2(255),
+ "ATTENDEE" nvarchar2(255),
+ "ICALUID" nvarchar2(255),
+ "ACCESSED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ primary key("ORGANIZER", "ATTENDEE", "ICALUID")
+);
+
+create table IMIP_INVITATION_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "FROM_ADDR" nvarchar2(255),
+ "TO_ADDR" nvarchar2(255),
+ "ICALENDAR_TEXT" nclob
+);
+
+create table IMIP_POLLING_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table IMIP_REPLY_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "ORGANIZER" nvarchar2(255),
+ "ATTENDEE" nvarchar2(255),
+ "ICALENDAR_TEXT" nclob
+);
+
+create table PUSH_NOTIFICATION_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "PUSH_ID" nvarchar2(255),
+ "PRIORITY" integer not null
+);
+
+create table GROUP_CACHER_POLLING_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table GROUP_REFRESH_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "GROUP_GUID" nvarchar2(255)
+);
+
+create table GROUP_ATTENDEE_RECONCILIATION_ (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "RESOURCE_ID" integer,
+ "GROUP_ID" integer
+);
+
+create table GROUPS (
+ "GROUP_ID" integer primary key,
+ "NAME" nvarchar2(255),
+ "GROUP_GUID" nvarchar2(255),
+ "MEMBERSHIP_HASH" nvarchar2(255),
+ "EXTANT" integer default 1,
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table GROUP_MEMBERSHIP (
+ "GROUP_ID" integer,
+ "MEMBER_GUID" nvarchar2(255)
+);
+
+create table GROUP_ATTENDEE (
+ "GROUP_ID" integer,
+ "RESOURCE_ID" integer,
+ "MEMBERSHIP_HASH" nvarchar2(255)
+);
+
+create table DELEGATES (
+ "DELEGATOR" nvarchar2(255),
+ "DELEGATE" nvarchar2(255),
+ "READ_WRITE" integer not null
+);
+
+create table DELEGATE_GROUPS (
+ "DELEGATOR" nvarchar2(255),
+ "GROUP_ID" integer not null,
+ "READ_WRITE" integer not null,
+ "IS_EXTERNAL" integer not null
+);
+
+create table EXTERNAL_DELEGATE_GROUPS (
+ "DELEGATOR" nvarchar2(255),
+ "GROUP_GUID_READ" nvarchar2(255),
+ "GROUP_GUID_WRITE" nvarchar2(255)
+);
+
+create table CALENDAR_OBJECT_SPLITTER_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade
+);
+
+create table FIND_MIN_VALID_REVISION_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table REVISION_CLEANUP_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table INBOX_CLEANUP_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table CLEANUP_ONE_INBOX_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "HOME_ID" integer not null unique references CALENDAR_HOME on delete cascade
+);
+
+create table SCHEDULE_REFRESH_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "ICALENDAR_UID" nvarchar2(255),
+ "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
+ "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade,
+ "ATTENDEE_COUNT" integer
+);
+
+create table SCHEDULE_REFRESH_ATTENDEES (
+ "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade,
+ "ATTENDEE" nvarchar2(255)
+);
+
+create table SCHEDULE_AUTO_REPLY_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "ICALENDAR_UID" nvarchar2(255),
+ "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
+ "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade,
+ "PARTSTAT" nvarchar2(255)
+);
+
+create table SCHEDULE_ORGANIZER_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "ICALENDAR_UID" nvarchar2(255),
+ "SCHEDULE_ACTION" integer not null,
+ "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
+ "RESOURCE_ID" integer,
+ "ICALENDAR_TEXT_OLD" nclob,
+ "ICALENDAR_TEXT_NEW" nclob,
+ "ATTENDEE_COUNT" integer,
+ "SMART_MERGE" integer
+);
+
+create table SCHEDULE_ACTION (
+ "ID" integer primary key,
+ "DESCRIPTION" nvarchar2(16) unique
+);
+
+insert into SCHEDULE_ACTION (DESCRIPTION, ID) values ('create', 0);
+insert into SCHEDULE_ACTION (DESCRIPTION, ID) values ('modify', 1);
+insert into SCHEDULE_ACTION (DESCRIPTION, ID) values ('modify-cancelled', 2);
+insert into SCHEDULE_ACTION (DESCRIPTION, ID) values ('remove', 3);
+create table SCHEDULE_REPLY_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "ICALENDAR_UID" nvarchar2(255),
+ "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
+ "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade,
+ "CHANGED_RIDS" nclob
+);
+
+create table SCHEDULE_REPLY_CANCEL_WORK (
+ "WORK_ID" integer primary key not null,
+ "NOT_BEFORE" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "ICALENDAR_UID" nvarchar2(255),
+ "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade,
+ "ICALENDAR_TEXT" nclob
+);
+
+create table CALENDARSERVER (
+ "NAME" nvarchar2(255) primary key,
+ "VALUE" nvarchar2(255)
+);
+
+insert into CALENDARSERVER (NAME, VALUE) values ('VERSION', '36');
+insert into CALENDARSERVER (NAME, VALUE) values ('CALENDAR-DATAVERSION', '5');
+insert into CALENDARSERVER (NAME, VALUE) values ('ADDRESSBOOK-DATAVERSION', '2');
+insert into CALENDARSERVER (NAME, VALUE) values ('NOTIFICATION-DATAVERSION', '1');
+insert into CALENDARSERVER (NAME, VALUE) values ('MIN-VALID-REVISION', '1');
+create index CALENDAR_HOME_METADAT_3cb9049e on CALENDAR_HOME_METADATA (
+ DEFAULT_EVENTS
+);
+
+create index CALENDAR_HOME_METADAT_d55e5548 on CALENDAR_HOME_METADATA (
+ DEFAULT_TASKS
+);
+
+create index CALENDAR_HOME_METADAT_910264ce on CALENDAR_HOME_METADATA (
+ DEFAULT_POLLS
+);
+
+create index NOTIFICATION_NOTIFICA_f891f5f9 on NOTIFICATION (
+ NOTIFICATION_HOME_RESOURCE_ID
+);
+
+create index CALENDAR_BIND_RESOURC_e57964d4 on CALENDAR_BIND (
+ CALENDAR_RESOURCE_ID
+);
+
+create index CALENDAR_OBJECT_CALEN_a9a453a9 on CALENDAR_OBJECT (
+ CALENDAR_RESOURCE_ID,
+ ICALENDAR_UID
+);
+
+create index CALENDAR_OBJECT_CALEN_96e83b73 on CALENDAR_OBJECT (
+ CALENDAR_RESOURCE_ID,
+ RECURRANCE_MAX
+);
+
+create index CALENDAR_OBJECT_ICALE_82e731d5 on CALENDAR_OBJECT (
+ ICALENDAR_UID
+);
+
+create index CALENDAR_OBJECT_DROPB_de041d80 on CALENDAR_OBJECT (
+ DROPBOX_ID
+);
+
+create index TIME_RANGE_CALENDAR_R_beb6e7eb on TIME_RANGE (
+ CALENDAR_RESOURCE_ID
+);
+
+create index TIME_RANGE_CALENDAR_O_acf37bd1 on TIME_RANGE (
+ CALENDAR_OBJECT_RESOURCE_ID
+);
+
+create index TRANSPARENCY_TIME_RAN_5f34467f on TRANSPARENCY (
+ TIME_RANGE_INSTANCE_ID
+);
+
+create index ATTACHMENT_CALENDAR_H_0078845c on ATTACHMENT (
+ CALENDAR_HOME_RESOURCE_ID
+);
+
+create index ATTACHMENT_DROPBOX_ID_5073cf23 on ATTACHMENT (
+ DROPBOX_ID
+);
+
+create index ATTACHMENT_CALENDAR_O_81508484 on ATTACHMENT_CALENDAR_OBJECT (
+ CALENDAR_OBJECT_RESOURCE_ID
+);
+
+create index SHARED_ADDRESSBOOK_BI_e9a2e6d4 on SHARED_ADDRESSBOOK_BIND (
+ OWNER_HOME_RESOURCE_ID
+);
+
+create index ABO_MEMBERS_ADDRESSBO_4effa879 on ABO_MEMBERS (
+ ADDRESSBOOK_ID
+);
+
+create index ABO_MEMBERS_MEMBER_ID_8d66adcf on ABO_MEMBERS (
+ MEMBER_ID
+);
+
+create index ABO_FOREIGN_MEMBERS_A_1fd2c5e9 on ABO_FOREIGN_MEMBERS (
+ ADDRESSBOOK_ID
+);
+
+create index SHARED_GROUP_BIND_RES_cf52f95d on SHARED_GROUP_BIND (
+ GROUP_RESOURCE_ID
+);
+
+create index CALENDAR_OBJECT_REVIS_3a3956c4 on CALENDAR_OBJECT_REVISIONS (
+ CALENDAR_HOME_RESOURCE_ID,
+ CALENDAR_RESOURCE_ID
+);
+
+create index CALENDAR_OBJECT_REVIS_6d9d929c on CALENDAR_OBJECT_REVISIONS (
+ CALENDAR_RESOURCE_ID,
+ RESOURCE_NAME,
+ DELETED,
+ REVISION
+);
+
+create index CALENDAR_OBJECT_REVIS_265c8acf on CALENDAR_OBJECT_REVISIONS (
+ CALENDAR_RESOURCE_ID,
+ REVISION
+);
+
+create index ADDRESSBOOK_OBJECT_RE_2bfcf757 on ADDRESSBOOK_OBJECT_REVISIONS (
+ ADDRESSBOOK_HOME_RESOURCE_ID,
+ OWNER_HOME_RESOURCE_ID
+);
+
+create index ADDRESSBOOK_OBJECT_RE_00fe8288 on ADDRESSBOOK_OBJECT_REVISIONS (
+ OWNER_HOME_RESOURCE_ID,
+ RESOURCE_NAME,
+ DELETED,
+ REVISION
+);
+
+create index ADDRESSBOOK_OBJECT_RE_45004780 on ADDRESSBOOK_OBJECT_REVISIONS (
+ OWNER_HOME_RESOURCE_ID,
+ REVISION
+);
+
+create index NOTIFICATION_OBJECT_R_036a9cee on NOTIFICATION_OBJECT_REVISIONS (
+ NOTIFICATION_HOME_RESOURCE_ID,
+ REVISION
+);
+
+create index APN_SUBSCRIPTIONS_RES_9610d78e on APN_SUBSCRIPTIONS (
+ RESOURCE_KEY
+);
+
+create index IMIP_TOKENS_TOKEN_e94b918f on IMIP_TOKENS (
+ TOKEN
+);
+
+create index GROUPS_GROUP_GUID_ebf7a1d4 on GROUPS (
+ GROUP_GUID
+);
+
+create index GROUP_MEMBERSHIP_GROU_9560a5e6 on GROUP_MEMBERSHIP (
+ GROUP_ID
+);
+
+create index GROUP_MEMBERSHIP_MEMB_0ca508e8 on GROUP_MEMBERSHIP (
+ MEMBER_GUID
+);
+
+create index CALENDAR_OBJECT_SPLIT_af71dcda on CALENDAR_OBJECT_SPLITTER_WORK (
+ RESOURCE_ID
+);
+
+create index SCHEDULE_REFRESH_WORK_26084c7b on SCHEDULE_REFRESH_WORK (
+ HOME_RESOURCE_ID
+);
+
+create index SCHEDULE_REFRESH_WORK_989efe54 on SCHEDULE_REFRESH_WORK (
+ RESOURCE_ID
+);
+
+create index SCHEDULE_REFRESH_ATTE_83053b91 on SCHEDULE_REFRESH_ATTENDEES (
+ RESOURCE_ID,
+ ATTENDEE
+);
+
+create index SCHEDULE_AUTO_REPLY_W_0256478d on SCHEDULE_AUTO_REPLY_WORK (
+ HOME_RESOURCE_ID
+);
+
+create index SCHEDULE_AUTO_REPLY_W_0755e754 on SCHEDULE_AUTO_REPLY_WORK (
+ RESOURCE_ID
+);
+
+create index SCHEDULE_ORGANIZER_WO_18ce4edd on SCHEDULE_ORGANIZER_WORK (
+ HOME_RESOURCE_ID
+);
+
+create index SCHEDULE_ORGANIZER_WO_14702035 on SCHEDULE_ORGANIZER_WORK (
+ RESOURCE_ID
+);
+
+create index SCHEDULE_REPLY_WORK_H_745af8cf on SCHEDULE_REPLY_WORK (
+ HOME_RESOURCE_ID
+);
+
+create index SCHEDULE_REPLY_WORK_R_11bd3fbb on SCHEDULE_REPLY_WORK (
+ RESOURCE_ID
+);
+
+create index SCHEDULE_REPLY_CANCEL_dab513ef on SCHEDULE_REPLY_CANCEL_WORK (
+ HOME_RESOURCE_ID
+);
+
Added: CalendarServer/trunk/txdav/common/datastore/sql_schema/old/postgres-dialect/v36.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/old/postgres-dialect/v36.sql (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/old/postgres-dialect/v36.sql 2014-03-01 16:21:25 UTC (rev 12782)
@@ -0,0 +1,944 @@
+-- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-
+
+----
+-- Copyright (c) 2010-2014 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;
+
+
+-------------------------
+-- Cluster Bookkeeping --
+-------------------------
+
+-- Information about a process connected to this database.
+
+-- Note that this must match the node info schema in twext.enterprise.queue.
+create table NODE_INFO (
+ HOSTNAME varchar(255) not null,
+ PID integer not null,
+ PORT integer not null,
+ TIME timestamp not null default timezone('UTC', CURRENT_TIMESTAMP),
+
+ primary key (HOSTNAME, PORT)
+);
+
+-- Unique named locks. This table should always be empty, but rows are
+-- temporarily created in order to prevent undesirable concurrency.
+create table NAMED_LOCK (
+ LOCK_NAME varchar(255) primary key
+);
+
+
+-------------------
+-- 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
+ STATUS integer default 0 not null, -- enum HOME_STATUS
+ DATAVERSION integer default 0 not null
+);
+
+-- Enumeration of statuses
+
+create table HOME_STATUS (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into HOME_STATUS values (0, 'normal' );
+insert into HOME_STATUS values (1, 'external');
+
+
+--------------
+-- Calendar --
+--------------
+
+create table CALENDAR (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
+);
+
+
+----------------------------
+-- 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,
+ DEFAULT_EVENTS integer default null references CALENDAR on delete set null,
+ DEFAULT_TASKS integer default null references CALENDAR on delete set null,
+ DEFAULT_POLLS integer default null references CALENDAR on delete set null,
+ ALARM_VEVENT_TIMED text default null,
+ ALARM_VEVENT_ALLDAY text default null,
+ ALARM_VTODO_TIMED text default null,
+ ALARM_VTODO_ALLDAY text default null,
+ AVAILABILITY text default null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+create index CALENDAR_HOME_METADATA_DEFAULT_EVENTS on
+ CALENDAR_HOME_METADATA(DEFAULT_EVENTS);
+create index CALENDAR_HOME_METADATA_DEFAULT_TASKS on
+ CALENDAR_HOME_METADATA(DEFAULT_TASKS);
+create index CALENDAR_HOME_METADATA_DEFAULT_POLLS on
+ CALENDAR_HOME_METADATA(DEFAULT_POLLS);
+
+
+-----------------------
+-- 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)
+);
+
+
+---------------------------
+-- 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
+ STATUS integer default 0 not null, -- enum HOME_STATUS
+ DATAVERSION integer default 0 not null
+);
+
+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,
+ NOTIFICATION_TYPE varchar(255) not null,
+ NOTIFICATION_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,
+ EXTERNAL_ID integer default null,
+ CALENDAR_RESOURCE_NAME varchar(255) not null,
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ BIND_REVISION integer default 0 not null,
+ MESSAGE text,
+ TRANSP integer default 0 not null, -- enum CALENDAR_TRANSP
+ ALARM_VEVENT_TIMED text default null,
+ ALARM_VEVENT_ALLDAY text default null,
+ ALARM_VTODO_TIMED text default null,
+ ALARM_VTODO_ALLDAY text default null,
+ TIMEZONE text default null,
+
+ 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');
+insert into CALENDAR_BIND_MODE values (4, 'indirect');
+
+-- 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');
+insert into CALENDAR_BIND_STATUS values (4, 'deleted');
+
+
+-- Enumeration of transparency
+
+create table CALENDAR_TRANSP (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into CALENDAR_TRANSP values (0, 'opaque' );
+insert into CALENDAR_TRANSP values (1, 'transparent');
+
+
+---------------------
+-- 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),
+ RECURRANCE_MIN date, -- minimum date that recurrences have been expanded to.
+ 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_ICALENDAR_UID on
+ CALENDAR_OBJECT(ICALENDAR_UID);
+
+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 sequence ATTACHMENT_ID_SEQ;
+
+create table ATTACHMENT (
+ ATTACHMENT_ID integer primary key default nextval('ATTACHMENT_ID_SEQ'), -- implicit index
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ DROPBOX_ID varchar(255),
+ 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
+);
+
+create index ATTACHMENT_CALENDAR_HOME_RESOURCE_ID on
+ ATTACHMENT(CALENDAR_HOME_RESOURCE_ID);
+
+create index ATTACHMENT_DROPBOX_ID on
+ ATTACHMENT(DROPBOX_ID);
+
+-- Many-to-many relationship between attachments and calendar objects
+create table ATTACHMENT_CALENDAR_OBJECT (
+ ATTACHMENT_ID integer not null references ATTACHMENT on delete cascade,
+ MANAGED_ID varchar(255) not null,
+ CALENDAR_OBJECT_RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+
+ primary key (ATTACHMENT_ID, CALENDAR_OBJECT_RESOURCE_ID), -- implicit index
+ unique (MANAGED_ID, CALENDAR_OBJECT_RESOURCE_ID) --implicit index
+);
+
+create index ATTACHMENT_CALENDAR_OBJECT_CALENDAR_OBJECT_RESOURCE_ID on
+ ATTACHMENT_CALENDAR_OBJECT(CALENDAR_OBJECT_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
+ ADDRESSBOOK_PROPERTY_STORE_ID integer default nextval('RESOURCE_ID_SEQ') not null, -- implicit index
+ OWNER_UID varchar(255) not null unique, -- implicit index
+ STATUS integer default 0 not null, -- enum HOME_STATUS
+ DATAVERSION integer default 0 not null
+);
+
+
+-------------------------------
+-- AddressBook Home Metadata --
+-------------------------------
+
+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)
+);
+
+
+-----------------------------
+-- Shared AddressBook Bind --
+-----------------------------
+
+-- Joins sharee ADDRESSBOOK_HOME and owner ADDRESSBOOK_HOME
+
+create table SHARED_ADDRESSBOOK_BIND (
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ OWNER_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
+ EXTERNAL_ID integer default null,
+ ADDRESSBOOK_RESOURCE_NAME varchar(255) not null,
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ BIND_REVISION integer default 0 not null,
+ MESSAGE text, -- FIXME: xml?
+
+ primary key (ADDRESSBOOK_HOME_RESOURCE_ID, OWNER_HOME_RESOURCE_ID), -- implicit index
+ unique (ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME) -- implicit index
+);
+
+create index SHARED_ADDRESSBOOK_BIND_RESOURCE_ID on
+ SHARED_ADDRESSBOOK_BIND(OWNER_HOME_RESOURCE_ID);
+
+
+------------------------
+-- AddressBook Object --
+------------------------
+
+create table ADDRESSBOOK_OBJECT (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
+ RESOURCE_NAME varchar(255) not null,
+ VCARD_TEXT text not null,
+ VCARD_UID varchar(255) not null,
+ KIND integer not null, -- enum ADDRESSBOOK_OBJECT_KIND
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ unique (ADDRESSBOOK_HOME_RESOURCE_ID, RESOURCE_NAME), -- implicit index
+ unique (ADDRESSBOOK_HOME_RESOURCE_ID, VCARD_UID) -- implicit index
+);
+
+
+-----------------------------
+-- AddressBook Object kind --
+-----------------------------
+
+create table ADDRESSBOOK_OBJECT_KIND (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into ADDRESSBOOK_OBJECT_KIND values (0, 'person');
+insert into ADDRESSBOOK_OBJECT_KIND values (1, 'group' );
+insert into ADDRESSBOOK_OBJECT_KIND values (2, 'resource');
+insert into ADDRESSBOOK_OBJECT_KIND values (3, 'location');
+
+
+----------------------------------
+-- Revisions, forward reference --
+----------------------------------
+
+create sequence REVISION_SEQ;
+
+---------------------------------
+-- Address Book Object Members --
+---------------------------------
+
+create table ABO_MEMBERS (
+ GROUP_ID integer not null, -- references ADDRESSBOOK_OBJECT on delete cascade, -- AddressBook Object's (kind=='group') RESOURCE_ID
+ ADDRESSBOOK_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
+ MEMBER_ID integer not null, -- references ADDRESSBOOK_OBJECT, -- member AddressBook Object's RESOURCE_ID
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ REMOVED boolean default false not null,
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ primary key (GROUP_ID, MEMBER_ID, REVISION) -- implicit index
+);
+
+create index ABO_MEMBERS_ADDRESSBOOK_ID on
+ ABO_MEMBERS(ADDRESSBOOK_ID);
+create index ABO_MEMBERS_MEMBER_ID on
+ ABO_MEMBERS(MEMBER_ID);
+
+------------------------------------------
+-- Address Book Object Foreign Members --
+------------------------------------------
+
+create table ABO_FOREIGN_MEMBERS (
+ GROUP_ID integer not null references ADDRESSBOOK_OBJECT on delete cascade, -- AddressBook Object's (kind=='group') RESOURCE_ID
+ ADDRESSBOOK_ID integer not null references ADDRESSBOOK_HOME on delete cascade,
+ MEMBER_ADDRESS varchar(255) not null, -- member AddressBook Object's 'calendar' address
+
+ primary key (GROUP_ID, MEMBER_ADDRESS) -- implicit index
+);
+
+create index ABO_FOREIGN_MEMBERS_ADDRESSBOOK_ID on
+ ABO_FOREIGN_MEMBERS(ADDRESSBOOK_ID);
+
+-----------------------
+-- Shared Group Bind --
+-----------------------
+
+-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK_OBJECT (kind == group)
+
+create table SHARED_GROUP_BIND (
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ GROUP_RESOURCE_ID integer not null references ADDRESSBOOK_OBJECT on delete cascade,
+ EXTERNAL_ID integer default null,
+ GROUP_ADDRESSBOOK_NAME varchar(255) not null,
+ BIND_MODE integer not null, -- enum CALENDAR_BIND_MODE
+ BIND_STATUS integer not null, -- enum CALENDAR_BIND_STATUS
+ BIND_REVISION integer default 0 not null,
+ MESSAGE text, -- FIXME: xml?
+
+ primary key (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_RESOURCE_ID), -- implicit index
+ unique (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_ADDRESSBOOK_NAME) -- implicit index
+);
+
+create index SHARED_GROUP_BIND_RESOURCE_ID on
+ SHARED_GROUP_BIND(GROUP_RESOURCE_ID);
+
+
+---------------
+-- Revisions --
+---------------
+
+-- create sequence REVISION_SEQ;
+
+
+-------------------------------
+-- Calendar Object 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,
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+create index CALENDAR_OBJECT_REVISIONS_HOME_RESOURCE_ID_CALENDAR_RESOURCE_ID
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID);
+
+create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME_DELETED_REVISION
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, RESOURCE_NAME, DELETED, REVISION);
+
+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,
+ OWNER_HOME_RESOURCE_ID integer references ADDRESSBOOK_HOME,
+ ADDRESSBOOK_NAME varchar(255) default null,
+ OBJECT_RESOURCE_ID integer default 0,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null,
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID_OWNER_HOME_RESOURCE_ID
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID, OWNER_HOME_RESOURCE_ID);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_OWNER_HOME_RESOURCE_ID_RESOURCE_NAME_DELETED_REVISION
+ on ADDRESSBOOK_OBJECT_REVISIONS(OWNER_HOME_RESOURCE_ID, RESOURCE_NAME, DELETED, REVISION);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_OWNER_HOME_RESOURCE_ID_REVISION
+ on ADDRESSBOOK_OBJECT_REVISIONS(OWNER_HOME_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,
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ 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,
+ USER_AGENT varchar(255) default null,
+ IP_ADDR varchar(255) default null,
+
+ primary key (TOKEN, RESOURCE_KEY) -- implicit index
+);
+
+create index APN_SUBSCRIPTIONS_RESOURCE_KEY
+ on APN_SUBSCRIPTIONS(RESOURCE_KEY);
+
+
+-----------------
+-- IMIP Tokens --
+-----------------
+
+create table IMIP_TOKENS (
+ TOKEN varchar(255) not null,
+ ORGANIZER varchar(255) not null,
+ ATTENDEE varchar(255) not null,
+ ICALUID varchar(255) not null,
+ ACCESSED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ primary key (ORGANIZER, ATTENDEE, ICALUID) -- implicit index
+);
+
+create index IMIP_TOKENS_TOKEN
+ on IMIP_TOKENS(TOKEN);
+
+
+----------------
+-- Work Items --
+----------------
+
+create sequence WORKITEM_SEQ;
+
+
+---------------------------
+-- IMIP Inivitation Work --
+---------------------------
+
+create table IMIP_INVITATION_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ FROM_ADDR varchar(255) not null,
+ TO_ADDR varchar(255) not null,
+ ICALENDAR_TEXT text not null
+);
+
+
+-----------------------
+-- IMIP Polling Work --
+-----------------------
+
+create table IMIP_POLLING_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+
+---------------------
+-- IMIP Reply Work --
+---------------------
+
+create table IMIP_REPLY_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ ORGANIZER varchar(255) not null,
+ ATTENDEE varchar(255) not null,
+ ICALENDAR_TEXT text not null
+);
+
+
+------------------------
+-- Push Notifications --
+------------------------
+
+create table PUSH_NOTIFICATION_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ PUSH_ID varchar(255) not null,
+ PRIORITY integer not null -- 1:low 5:medium 10:high
+);
+
+-----------------
+-- GroupCacher --
+-----------------
+
+create table GROUP_CACHER_POLLING_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+create table GROUP_REFRESH_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ GROUP_GUID varchar(255) not null
+);
+
+create table GROUP_ATTENDEE_RECONCILIATION_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ RESOURCE_ID integer,
+ GROUP_ID integer
+);
+
+
+create table GROUPS (
+ GROUP_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ NAME varchar(255) not null,
+ GROUP_GUID varchar(255) not null,
+ MEMBERSHIP_HASH varchar(255) not null,
+ EXTANT integer default 1,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+create index GROUPS_GROUP_GUID on GROUPS(GROUP_GUID);
+
+create table GROUP_MEMBERSHIP (
+ GROUP_ID integer,
+ MEMBER_GUID varchar(255) not null
+);
+create index GROUP_MEMBERSHIP_GROUP on GROUP_MEMBERSHIP(GROUP_ID);
+create index GROUP_MEMBERSHIP_MEMBER on GROUP_MEMBERSHIP(MEMBER_GUID);
+
+create table GROUP_ATTENDEE (
+ GROUP_ID integer,
+ RESOURCE_ID integer,
+ MEMBERSHIP_HASH varchar(255) not null
+);
+
+---------------
+-- Delegates --
+---------------
+
+create table DELEGATES (
+ DELEGATOR varchar(255) not null,
+ DELEGATE varchar(255) not null,
+ READ_WRITE integer not null -- 1 = ReadWrite, 0 = ReadOnly
+);
+
+create table DELEGATE_GROUPS (
+ DELEGATOR varchar(255) not null,
+ GROUP_ID integer not null,
+ READ_WRITE integer not null, -- 1 = ReadWrite, 0 = ReadOnly
+ IS_EXTERNAL integer not null -- 1 = ReadWrite, 0 = ReadOnly
+);
+
+create table EXTERNAL_DELEGATE_GROUPS (
+ DELEGATOR varchar(255) not null,
+ GROUP_GUID_READ varchar(255),
+ GROUP_GUID_WRITE varchar(255)
+);
+
+--------------------------
+-- Object Splitter Work --
+--------------------------
+
+create table CALENDAR_OBJECT_SPLITTER_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade
+);
+
+create index CALENDAR_OBJECT_SPLITTER_WORK_RESOURCE_ID on
+ CALENDAR_OBJECT_SPLITTER_WORK(RESOURCE_ID);
+
+---------------------------
+-- Revision Cleanup Work --
+---------------------------
+
+create table FIND_MIN_VALID_REVISION_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+create table REVISION_CLEANUP_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+------------------------
+-- Inbox Cleanup Work --
+------------------------
+
+create table INBOX_CLEANUP_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+create table CLEANUP_ONE_INBOX_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ HOME_ID integer not null unique references CALENDAR_HOME on delete cascade
+);
+
+---------------------------
+-- Schedule Refresh Work --
+---------------------------
+
+create table SCHEDULE_REFRESH_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ ICALENDAR_UID varchar(255) not null,
+ HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
+ RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ ATTENDEE_COUNT integer
+);
+
+create index SCHEDULE_REFRESH_WORK_HOME_RESOURCE_ID on
+ SCHEDULE_REFRESH_WORK(HOME_RESOURCE_ID);
+create index SCHEDULE_REFRESH_WORK_RESOURCE_ID on
+ SCHEDULE_REFRESH_WORK(RESOURCE_ID);
+
+create table SCHEDULE_REFRESH_ATTENDEES (
+ RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ ATTENDEE varchar(255) not null
+);
+
+create index SCHEDULE_REFRESH_ATTENDEES_RESOURCE_ID_ATTENDEE on
+ SCHEDULE_REFRESH_ATTENDEES(RESOURCE_ID, ATTENDEE);
+
+------------------------------
+-- Schedule Auto Reply Work --
+------------------------------
+
+create table SCHEDULE_AUTO_REPLY_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ ICALENDAR_UID varchar(255) not null,
+ HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
+ RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ PARTSTAT varchar(255) not null
+);
+
+create index SCHEDULE_AUTO_REPLY_WORK_HOME_RESOURCE_ID on
+ SCHEDULE_AUTO_REPLY_WORK(HOME_RESOURCE_ID);
+create index SCHEDULE_AUTO_REPLY_WORK_RESOURCE_ID on
+ SCHEDULE_AUTO_REPLY_WORK(RESOURCE_ID);
+
+-----------------------------
+-- Schedule Organizer Work --
+-----------------------------
+
+create table SCHEDULE_ORGANIZER_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ ICALENDAR_UID varchar(255) not null,
+ SCHEDULE_ACTION integer not null, -- Enum SCHEDULE_ACTION
+ HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
+ RESOURCE_ID integer, -- this references a possibly non-existent CALENDR_OBJECT
+ ICALENDAR_TEXT_OLD text,
+ ICALENDAR_TEXT_NEW text,
+ ATTENDEE_COUNT integer,
+ SMART_MERGE boolean
+);
+
+create index SCHEDULE_ORGANIZER_WORK_HOME_RESOURCE_ID on
+ SCHEDULE_ORGANIZER_WORK(HOME_RESOURCE_ID);
+create index SCHEDULE_ORGANIZER_WORK_RESOURCE_ID on
+ SCHEDULE_ORGANIZER_WORK(RESOURCE_ID);
+
+-- Enumeration of schedule actions
+
+create table SCHEDULE_ACTION (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into SCHEDULE_ACTION values (0, 'create');
+insert into SCHEDULE_ACTION values (1, 'modify');
+insert into SCHEDULE_ACTION values (2, 'modify-cancelled');
+insert into SCHEDULE_ACTION values (3, 'remove');
+
+-------------------------
+-- Schedule Reply Work --
+-------------------------
+
+create table SCHEDULE_REPLY_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ ICALENDAR_UID varchar(255) not null,
+ HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
+ RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ CHANGED_RIDS text
+);
+
+create index SCHEDULE_REPLY_WORK_HOME_RESOURCE_ID on
+ SCHEDULE_REPLY_WORK(HOME_RESOURCE_ID);
+create index SCHEDULE_REPLY_WORK_RESOURCE_ID on
+ SCHEDULE_REPLY_WORK(RESOURCE_ID);
+
+--------------------------------
+-- Schedule Reply Cancel Work --
+--------------------------------
+
+create table SCHEDULE_REPLY_CANCEL_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ ICALENDAR_UID varchar(255) not null,
+ HOME_RESOURCE_ID integer not null references CALENDAR_HOME on delete cascade,
+ ICALENDAR_TEXT text not null
+);
+
+create index SCHEDULE_REPLY_CANCEL_WORK_HOME_RESOURCE_ID on
+ SCHEDULE_REPLY_CANCEL_WORK(HOME_RESOURCE_ID);
+
+--------------------
+-- Schema Version --
+--------------------
+
+create table CALENDARSERVER (
+ NAME varchar(255) primary key, -- implicit index
+ VALUE varchar(255)
+);
+
+insert into CALENDARSERVER values ('VERSION', '36');
+insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '5');
+insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '2');
+insert into CALENDARSERVER values ('NOTIFICATION-DATAVERSION', '1');
+insert into CALENDARSERVER values ('MIN-VALID-REVISION', '1');
Added: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_36_to_37.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_36_to_37.sql (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_36_to_37.sql 2014-03-01 16:21:25 UTC (rev 12782)
@@ -0,0 +1,372 @@
+----
+-- Copyright (c) 2012-2014 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 36 to 37 --
+---------------------------------------------------
+
+------------------------
+-- Inbox Cleanup Work --
+------------------------
+
+create sequence JOB_SEQ;
+
+create table JOB (
+ "JOB_ID" integer primary key not null,
+ "WORK_TYPE" nvarchar2(255),
+ "PRIORITY" integer default 0,
+ "WEIGHT" integer default 0,
+ "NOT_BEFORE" timestamp default null,
+ "NOT_AFTER" timestamp default null
+);
+
+create or replace function next_job return integer is
+ cursor c1 is select ID from JOB for update skip locked;
+ result integer;
+begin
+ open c1;
+ fetch c1 into result;
+ select ID from JOB where ID = result for update;
+ return result;
+end;
+/
+
+
+-- IMIP_INVITATION_WORK --
+alter table IMIP_INVITATION_WORK
+ add ("JOB_ID" integer default 0 not null);
+update IMIP_INVITATION_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'IMIP_INVITATION_WORK', NOT_BEFORE from IMIP_INVITATION_WORK);
+
+alter table IMIP_INVITATION_WORK
+ drop column NOT_BEFORE;
+alter table IMIP_INVITATION_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index IMIP_INVITATION_WORK__586d064c on IMIP_INVITATION_WORK (
+ JOB_ID
+);
+
+
+-- IMIP_POLLING_WORK --
+alter table IMIP_POLLING_WORK
+ add ("JOB_ID" integer default 0 not null);
+update IMIP_POLLING_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'IMIP_POLLING_WORK', NOT_BEFORE from IMIP_POLLING_WORK);
+
+alter table IMIP_POLLING_WORK
+ drop column NOT_BEFORE;
+alter table IMIP_POLLING_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index IMIP_POLLING_WORK_JOB_d5535891 on IMIP_POLLING_WORK (
+ JOB_ID
+);
+
+
+-- IMIP_REPLY_WORK --
+alter table IMIP_REPLY_WORK
+ add ("JOB_ID" integer default 0 not null);
+update IMIP_REPLY_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'IMIP_REPLY_WORK', NOT_BEFORE from IMIP_REPLY_WORK);
+
+alter table IMIP_REPLY_WORK
+ drop column NOT_BEFORE;
+alter table IMIP_REPLY_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index IMIP_REPLY_WORK_JOB_I_bf4ae73e on IMIP_REPLY_WORK (
+ JOB_ID
+);
+
+
+-- PUSH_NOTIFICATION_WORK --
+alter table PUSH_NOTIFICATION_WORK
+ add ("JOB_ID" integer default 0 not null);
+update PUSH_NOTIFICATION_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'PUSH_NOTIFICATION_WORK', NOT_BEFORE from PUSH_NOTIFICATION_WORK);
+
+alter table PUSH_NOTIFICATION_WORK
+ drop column NOT_BEFORE;
+alter table PUSH_NOTIFICATION_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index PUSH_NOTIFICATION_WOR_8bbab117 on PUSH_NOTIFICATION_WORK (
+ JOB_ID
+);
+
+
+-- GROUP_CACHER_POLLING_WORK --
+alter table GROUP_CACHER_POLLING_WORK
+ add ("JOB_ID" integer default 0 not null);
+update GROUP_CACHER_POLLING_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'GROUP_CACHER_POLLING_WORK', NOT_BEFORE from GROUP_CACHER_POLLING_WORK);
+
+alter table GROUP_CACHER_POLLING_WORK
+ drop column NOT_BEFORE;
+alter table GROUP_CACHER_POLLING_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index GROUP_CACHER_POLLING__6eb3151c on GROUP_CACHER_POLLING_WORK (
+ JOB_ID
+);
+
+
+-- GROUP_REFRESH_WORK --
+alter table GROUP_REFRESH_WORK
+ add ("JOB_ID" integer default 0 not null);
+update GROUP_REFRESH_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'GROUP_REFRESH_WORK', NOT_BEFORE from GROUP_REFRESH_WORK);
+
+alter table GROUP_REFRESH_WORK
+ drop column NOT_BEFORE;
+alter table GROUP_REFRESH_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index GROUP_REFRESH_WORK_JO_717ede20 on GROUP_REFRESH_WORK (
+ JOB_ID
+);
+
+
+-- GROUP_ATTENDEE_RECONCILIATION_WORK --
+alter table GROUP_ATTENDEE_RECONCILIATION_WORK
+ add ("JOB_ID" integer default 0 not null);
+update GROUP_ATTENDEE_RECONCILIATION_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'GROUP_ATTENDEE_RECONCILIATION_WORK', NOT_BEFORE from GROUP_ATTENDEE_RECONCILIATION_WORK);
+
+alter table GROUP_ATTENDEE_RECONCILIATION_WORK
+ drop column NOT_BEFORE;
+alter table GROUP_ATTENDEE_RECONCILIATION_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index GROUP_ATTENDEE_RECONC_cd2d61b9 on GROUP_ATTENDEE_RECONCILIATION_WORK (
+ JOB_ID
+);
+
+
+-- CALENDAR_OBJECT_SPLITTER_WORK --
+alter table CALENDAR_OBJECT_SPLITTER_WORK
+ add ("JOB_ID" integer default 0 not null);
+update CALENDAR_OBJECT_SPLITTER_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'CALENDAR_OBJECT_SPLITTER_WORK', NOT_BEFORE from CALENDAR_OBJECT_SPLITTER_WORK);
+
+alter table CALENDAR_OBJECT_SPLITTER_WORK
+ drop column NOT_BEFORE;
+alter table CALENDAR_OBJECT_SPLITTER_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index CALENDAR_OBJECT_SPLIT_33603b72 on CALENDAR_OBJECT_SPLITTER_WORK (
+ JOB_ID
+);
+
+
+-- FIND_MIN_VALID_REVISION_WORK --
+alter table FIND_MIN_VALID_REVISION_WORK
+ add ("JOB_ID" integer default 0 not null);
+update FIND_MIN_VALID_REVISION_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'FIND_MIN_VALID_REVISION_WORK', NOT_BEFORE from FIND_MIN_VALID_REVISION_WORK);
+
+alter table FIND_MIN_VALID_REVISION_WORK
+ drop column NOT_BEFORE;
+alter table FIND_MIN_VALID_REVISION_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index FIND_MIN_VALID_REVISI_78d17400 on FIND_MIN_VALID_REVISION_WORK (
+ JOB_ID
+);
+
+
+-- REVISION_CLEANUP_WORK --
+alter table REVISION_CLEANUP_WORK
+ add ("JOB_ID" integer default 0 not null);
+update REVISION_CLEANUP_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'REVISION_CLEANUP_WORK', NOT_BEFORE from REVISION_CLEANUP_WORK);
+
+alter table REVISION_CLEANUP_WORK
+ drop column NOT_BEFORE;
+alter table REVISION_CLEANUP_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index REVISION_CLEANUP_WORK_eb062686 on REVISION_CLEANUP_WORK (
+ JOB_ID
+);
+
+
+-- INBOX_CLEANUP_WORK --
+alter table INBOX_CLEANUP_WORK
+ add ("JOB_ID" integer default 0 not null);
+update INBOX_CLEANUP_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'INBOX_CLEANUP_WORK', NOT_BEFORE from INBOX_CLEANUP_WORK);
+
+alter table INBOX_CLEANUP_WORK
+ drop column NOT_BEFORE;
+alter table INBOX_CLEANUP_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index INBOX_CLEANUP_WORK_JO_799132bd on INBOX_CLEANUP_WORK (
+ JOB_ID
+);
+
+
+-- CLEANUP_ONE_INBOX_WORK --
+alter table CLEANUP_ONE_INBOX_WORK
+ add ("JOB_ID" integer default 0 not null);
+update CLEANUP_ONE_INBOX_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'CLEANUP_ONE_INBOX_WORK', NOT_BEFORE from CLEANUP_ONE_INBOX_WORK);
+
+alter table CLEANUP_ONE_INBOX_WORK
+ drop column NOT_BEFORE;
+alter table CLEANUP_ONE_INBOX_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index CLEANUP_ONE_INBOX_WOR_375dac36 on CLEANUP_ONE_INBOX_WORK (
+ JOB_ID
+);
+
+
+-- SCHEDULE_REFRESH_WORK --
+alter table SCHEDULE_REFRESH_WORK
+ add ("JOB_ID" integer default 0 not null);
+update SCHEDULE_REFRESH_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_REFRESH_WORK', NOT_BEFORE from SCHEDULE_REFRESH_WORK);
+
+alter table SCHEDULE_REFRESH_WORK
+ drop column NOT_BEFORE;
+alter table SCHEDULE_REFRESH_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_REFRESH_WORK_3ffa2718 on SCHEDULE_REFRESH_WORK (
+ JOB_ID
+);
+
+
+-- SCHEDULE_AUTO_REPLY_WORK --
+alter table SCHEDULE_AUTO_REPLY_WORK
+ add ("JOB_ID" integer default 0 not null);
+update SCHEDULE_AUTO_REPLY_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_AUTO_REPLY_WORK', NOT_BEFORE from SCHEDULE_AUTO_REPLY_WORK);
+
+alter table SCHEDULE_AUTO_REPLY_WORK
+ drop column NOT_BEFORE;
+alter table SCHEDULE_AUTO_REPLY_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_AUTO_REPLY_W_4d7bb5a8 on SCHEDULE_AUTO_REPLY_WORK (
+ JOB_ID
+);
+
+
+-- SCHEDULE_ORGANIZER_WORK --
+alter table SCHEDULE_ORGANIZER_WORK
+ add ("JOB_ID" integer default 0 not null);
+update SCHEDULE_ORGANIZER_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_ORGANIZER_WORK', NOT_BEFORE from SCHEDULE_ORGANIZER_WORK);
+
+alter table SCHEDULE_ORGANIZER_WORK
+ drop column NOT_BEFORE;
+alter table SCHEDULE_ORGANIZER_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_ORGANIZER_WO_1e9f246d on SCHEDULE_ORGANIZER_WORK (
+ JOB_ID
+);
+
+
+-- SCHEDULE_REPLY_WORK --
+alter table SCHEDULE_REPLY_WORK
+ add ("JOB_ID" integer default 0 not null);
+update SCHEDULE_REPLY_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_REPLY_WORK', NOT_BEFORE from SCHEDULE_REPLY_WORK);
+
+alter table SCHEDULE_REPLY_WORK
+ drop column NOT_BEFORE;
+alter table SCHEDULE_REPLY_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_REPLY_WORK_J_5913b4a4 on SCHEDULE_REPLY_WORK (
+ JOB_ID
+);
+
+
+-- SCHEDULE_REPLY_CANCEL_WORK --
+alter table SCHEDULE_REPLY_CANCEL_WORK
+ add ("JOB_ID" integer default 0 not null);
+update SCHEDULE_REPLY_CANCEL_WORK set JOB_ID = JOB_SEQ.nextval
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_REPLY_CANCEL_WORK', NOT_BEFORE from SCHEDULE_REPLY_CANCEL_WORK);
+
+alter table SCHEDULE_REPLY_CANCEL_WORK
+ drop column NOT_BEFORE;
+alter table SCHEDULE_REPLY_CANCEL_WORK
+ add constraint foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_REPLY_CANCEL_94a0c766 on SCHEDULE_REPLY_CANCEL_WORK (
+ JOB_ID
+);
+
+
+-- update the version
+update CALENDARSERVER set VALUE = '37' where NAME = 'VERSION';
Added: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_36_to_37.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_36_to_37.sql (rev 0)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_36_to_37.sql 2014-03-01 16:21:25 UTC (rev 12782)
@@ -0,0 +1,321 @@
+----
+-- Copyright (c) 2012-2014 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 36 to 37 --
+---------------------------------------------------
+
+------------------------
+-- Inbox Cleanup Work --
+------------------------
+
+create sequence JOB_SEQ;
+
+create table JOB (
+ JOB_ID integer primary key default nextval('JOB_SEQ') not null, --implicit index
+ WORK_TYPE varchar(255) not null,
+ PRIORITY integer default 0,
+ WEIGHT integer default 0,
+ NOT_BEFORE timestamp default null,
+ NOT_AFTER timestamp default null
+);
+
+create or replace function next_job() returns integer as $$
+declare
+ result integer;
+begin
+ select ID into result from JOB where pg_try_advisory_xact_lock(ID) limit 1 for update;
+ return result;
+end
+$$ LANGUAGE plpgsql;
+
+
+-- IMIP_INVITATION_WORK --
+alter table IMIP_INVITATION_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'IMIP_INVITATION_WORK', NOT_BEFORE from IMIP_INVITATION_WORK);
+
+alter table IMIP_INVITATION_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index IMIP_INVITATION_WORK_JOB_ID on
+ IMIP_INVITATION_WORK(JOB_ID);
+
+
+-- IMIP_POLLING_WORK --
+alter table IMIP_POLLING_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'IMIP_POLLING_WORK', NOT_BEFORE from IMIP_POLLING_WORK);
+
+alter table IMIP_POLLING_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index IMIP_POLLING_WORK_JOB_ID on
+ IMIP_POLLING_WORK(JOB_ID);
+
+
+-- IMIP_REPLY_WORK --
+alter table IMIP_REPLY_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'IMIP_REPLY_WORK', NOT_BEFORE from IMIP_REPLY_WORK);
+
+alter table IMIP_REPLY_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index IMIP_REPLY_WORK_JOB_ID on
+ IMIP_REPLY_WORK(JOB_ID);
+
+
+-- PUSH_NOTIFICATION_WORK --
+alter table PUSH_NOTIFICATION_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+alter table PUSH_NOTIFICATION_WORK
+ rename PRIORITY to PUSH_PRIORITY;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'PUSH_NOTIFICATION_WORK', NOT_BEFORE from PUSH_NOTIFICATION_WORK);
+
+alter table PUSH_NOTIFICATION_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index PUSH_NOTIFICATION_WORK_JOB_ID on
+ PUSH_NOTIFICATION_WORK(JOB_ID);
+
+
+-- GROUP_CACHER_POLLING_WORK --
+alter table GROUP_CACHER_POLLING_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'GROUP_CACHER_POLLING_WORK', NOT_BEFORE from GROUP_CACHER_POLLING_WORK);
+
+alter table GROUP_CACHER_POLLING_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index GROUP_CACHER_POLLING_WORK_JOB_ID on
+ GROUP_CACHER_POLLING_WORK(JOB_ID);
+
+
+-- GROUP_REFRESH_WORK --
+alter table GROUP_REFRESH_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'GROUP_REFRESH_WORK', NOT_BEFORE from GROUP_REFRESH_WORK);
+
+alter table GROUP_REFRESH_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index GROUP_REFRESH_WORK_JOB_ID on
+ GROUP_REFRESH_WORK(JOB_ID);
+
+
+-- GROUP_ATTENDEE_RECONCILIATION_WORK --
+alter table GROUP_ATTENDEE_RECONCILIATION_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'GROUP_ATTENDEE_RECONCILIATION_WORK', NOT_BEFORE from GROUP_ATTENDEE_RECONCILIATION_WORK);
+
+alter table GROUP_ATTENDEE_RECONCILIATION_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index GROUP_ATTENDEE_RECONCILIATION_WORK_JOB_ID on
+ GROUP_ATTENDEE_RECONCILIATION_WORK(JOB_ID);
+
+
+-- CALENDAR_OBJECT_SPLITTER_WORK --
+alter table CALENDAR_OBJECT_SPLITTER_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'CALENDAR_OBJECT_SPLITTER_WORK', NOT_BEFORE from CALENDAR_OBJECT_SPLITTER_WORK);
+
+alter table CALENDAR_OBJECT_SPLITTER_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index CALENDAR_OBJECT_SPLITTER_WORK_JOB_ID on
+ CALENDAR_OBJECT_SPLITTER_WORK(JOB_ID);
+
+
+-- FIND_MIN_VALID_REVISION_WORK --
+alter table FIND_MIN_VALID_REVISION_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'FIND_MIN_VALID_REVISION_WORK', NOT_BEFORE from FIND_MIN_VALID_REVISION_WORK);
+
+alter table FIND_MIN_VALID_REVISION_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index FIND_MIN_VALID_REVISION_WORK_JOB_ID on
+ FIND_MIN_VALID_REVISION_WORK(JOB_ID);
+
+
+-- REVISION_CLEANUP_WORK --
+alter table REVISION_CLEANUP_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'REVISION_CLEANUP_WORK', NOT_BEFORE from REVISION_CLEANUP_WORK);
+
+alter table REVISION_CLEANUP_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index REVISION_CLEANUP_WORK_JOB_ID on
+ REVISION_CLEANUP_WORK(JOB_ID);
+
+
+-- INBOX_CLEANUP_WORK --
+alter table INBOX_CLEANUP_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'INBOX_CLEANUP_WORK', NOT_BEFORE from INBOX_CLEANUP_WORK);
+
+alter table INBOX_CLEANUP_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index INBOX_CLEANUP_WORK_JOB_ID on
+ INBOX_CLEANUP_WORK(JOB_ID);
+
+
+-- CLEANUP_ONE_INBOX_WORK --
+alter table CLEANUP_ONE_INBOX_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'CLEANUP_ONE_INBOX_WORK', NOT_BEFORE from CLEANUP_ONE_INBOX_WORK);
+
+alter table CLEANUP_ONE_INBOX_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index CLEANUP_ONE_INBOX_WORK_JOB_ID on
+ CLEANUP_ONE_INBOX_WORK(JOB_ID);
+
+
+-- SCHEDULE_REFRESH_WORK --
+alter table SCHEDULE_REFRESH_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_REFRESH_WORK', NOT_BEFORE from SCHEDULE_REFRESH_WORK);
+
+alter table SCHEDULE_REFRESH_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_REFRESH_WORK_JOB_ID on
+ SCHEDULE_REFRESH_WORK(JOB_ID);
+
+
+-- SCHEDULE_AUTO_REPLY_WORK --
+alter table SCHEDULE_AUTO_REPLY_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_AUTO_REPLY_WORK', NOT_BEFORE from SCHEDULE_AUTO_REPLY_WORK);
+
+alter table SCHEDULE_AUTO_REPLY_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_AUTO_REPLY_WORK_JOB_ID on
+ SCHEDULE_AUTO_REPLY_WORK(JOB_ID);
+
+
+-- SCHEDULE_ORGANIZER_WORK --
+alter table SCHEDULE_ORGANIZER_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_ORGANIZER_WORK', NOT_BEFORE from SCHEDULE_ORGANIZER_WORK);
+
+alter table SCHEDULE_ORGANIZER_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_ORGANIZER_WORK_JOB_ID on
+ SCHEDULE_ORGANIZER_WORK(JOB_ID);
+
+
+-- SCHEDULE_REPLY_WORK --
+alter table SCHEDULE_REPLY_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_REPLY_WORK', NOT_BEFORE from SCHEDULE_REPLY_WORK);
+
+alter table SCHEDULE_REPLY_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_REPLY_WORK_JOB_ID on
+ SCHEDULE_REPLY_WORK(JOB_ID);
+
+
+-- SCHEDULE_REPLY_CANCEL_WORK --
+alter table SCHEDULE_REPLY_CANCEL_WORK
+ add JOB_ID integer default nextval('JOB_SEQ') not null;
+
+insert into JOB
+ (JOB_ID, WORK_TYPE, NOT_BEFORE)
+ (select JOB_ID, 'SCHEDULE_REPLY_CANCEL_WORK', NOT_BEFORE from SCHEDULE_REPLY_CANCEL_WORK);
+
+alter table SCHEDULE_REPLY_CANCEL_WORK
+ drop column NOT_BEFORE,
+ add foreign key (JOB_ID) references JOB;
+
+create index SCHEDULE_REPLY_CANCEL_WORK_JOB_ID on
+ SCHEDULE_REPLY_CANCEL_WORK(JOB_ID);
+
+
+-- update the version
+update CALENDARSERVER set VALUE = '37' where NAME = 'VERSION';
Modified: CalendarServer/trunk/txdav/common/datastore/sql_tables.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_tables.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/sql_tables.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -32,20 +32,49 @@
from re import compile
import hashlib
+def _schemaFiles(version=None):
+ """
+ Find the set of files to process, either the current set if C{version} is L{None}, otherwise look
+ for the specific version.
-def _populateSchema(version=None):
+ @param version: version identifier (e.g., "v1", "v2" etc)
+ @type version: L{str}
"""
+ if version is None:
+ currentObj = getModule(__name__).filePath.sibling("sql_schema").child("current.sql")
+ extrasObj = getModule(__name__).filePath.sibling("sql_schema").child("current-{}-extras.sql".format(ORACLE_DIALECT))
+ outObj = getModule(__name__).filePath.sibling("sql_schema").child("current-{}.sql".format(ORACLE_DIALECT))
+ else:
+ currentObj = getModule(__name__).filePath.sibling("sql_schema").child("old").child(POSTGRES_DIALECT).child("%s.sql" % (version,))
+ extrasObj = getModule(__name__).filePath.sibling("sql_schema").child("old").child(ORACLE_DIALECT).child("{}-extras.sql" % (version,))
+ outObj = getModule(__name__).filePath.sibling("sql_schema").child("old").child(ORACLE_DIALECT).child("{}.sql" % (version,))
+
+ return currentObj, extrasObj, outObj
+
+
+
+def _populateSchema(pathObj=None):
+ """
Generate the global L{SchemaSyntax}.
"""
- if version is None:
- pathObj = getModule(__name__).filePath.sibling("sql_schema").child("current.sql")
- else:
- pathObj = getModule(__name__).filePath.sibling("sql_schema").child("old").child(POSTGRES_DIALECT).child("%s.sql" % (version,))
+ if pathObj is None:
+ pathObj = _schemaFiles()[0]
return SchemaSyntax(schemaFromPath(pathObj))
+def _schemaExtras(out, extras):
+ """
+ If an extras file exists, add its entire content to the output stream..
+ """
+
+ if extras.exists():
+ out.write("\n-- Extras\n\n")
+ out.write(extras.getContent())
+
+
+
schema = _populateSchema()
@@ -397,8 +426,14 @@
out.write(',\n '.join([column.name for column in index.columns]))
out.write('\n);\n\n')
+ # Functions are skipped as they likely use dialect specific syntax. Instead, functions
+ # for other dialects need to be written in an "extras" file which will be appended to
+ # the output.
+ for function in schema.model.functions:
+ out.write("-- Skipped Function {}\n".format(function.name))
+
def splitSQLString(sqlString):
"""
Strings which mix zero or more sql statements with zero or more pl/sql
@@ -427,7 +462,7 @@
continue
if inPlSQL is None:
#if 'begin'.lower() in str(stmt).split()[0].lower():
- if 'begin'.lower() in str(stmt).lower():
+ if str(stmt).lower().strip().startswith('begin'):
inPlSQL = True
aggregated += str(stmt)
continue
@@ -438,9 +473,16 @@
if __name__ == '__main__':
import sys
- if len(sys.argv) == 2:
- # Argument is the name of a old/postgres-dialect file (without the .sql suffix), e.g. "v4"
- schema = _populateSchema(sys.argv[1])
- else:
- schema = _populateSchema()
- _translateSchema(sys.stdout, schema=schema)
+ version = sys.argv[1] if len(sys.argv) == 2 else None
+ current, extras, out = _schemaFiles(version)
+
+ print "Reading from {}".format(current.path)
+ print "Extras from {}".format(extras.path) if extras.exists() else "No extras"
+ print "Writing to {}".format(out.path)
+
+ with out.open("w") as outfd:
+ schema = _populateSchema(current)
+ _translateSchema(outfd, schema=schema)
+ _schemaExtras(outfd, extras)
+
+ print "Done"
Modified: CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -379,6 +379,48 @@
self.assertRaises(StopIteration, result.next)
+ def test_returnOnePlSQLAndOneSQLAndOneFunction(self):
+ """
+ One sql statement and one pl/sql statement yields two separate strings
+ """
+ sql = dedent(
+ '''SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
+ FROM EGM
+ INNER JOIN BioEntity
+ ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId
+ OPTION (MERGE JOIN);''')
+ sqlfn = dedent(
+ '''CREATE or REPLACE FUNCTION foobar() RETURNS integer as $$
+ DECLARE
+ result integer;
+ BEGIN
+ SELECT ID into result from JOB;
+ RETURN result;
+ END
+ $$ LANGUAGE plpgsql;''')
+ plsql = dedent(
+ '''BEGIN
+ FOR i IN 1..10 LOOP
+ IF MOD(i,2) = 0 THEN
+ INSERT INTO temp VALUES (i, x, 'i is even');
+ ELSE
+ INSERT INTO temp VALUES (i, x, 'i is odd');
+ END IF;
+ x := x + 100;
+ END LOOP;
+ COMMIT;
+ END;''')
+ s3 = "BEGIN\nFOR i IN 1..10 LOOP\nIF MOD(i,2) = 0 THEN\nINSERT INTO temp VALUES (i, x, 'i is even');ELSE\nINSERT INTO temp VALUES (i, x, 'i is odd');END IF;x := x + 100;END LOOP;COMMIT;END;"
+ result = splitSQLString(sql + sqlfn + plsql)
+ r1 = result.next()
+ self.assertEquals(r1, sql.rstrip(";"))
+ r2 = result.next()
+ self.assertEquals(r2, sqlfn.rstrip(";"))
+ r3 = result.next()
+ self.assertEquals(r3, s3)
+ self.assertRaises(StopIteration, result.next)
+
+
def test_actualSchemaUpgrade(self):
"""
A real-world schema upgrade is split into the expected number of statements,
Modified: CalendarServer/trunk/txdav/common/datastore/upgrade/sql/test/test_upgrade.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/upgrade/sql/test/test_upgrade.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/upgrade/sql/test/test_upgrade.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -297,7 +297,7 @@
for child in test_upgrader.schemaLocation.child("old").child(POSTGRES_DIALECT).globChildren("*.sql"):
def f(self, lchild=child):
return self._dbSchemaUpgrades(lchild)
- setattr(SchemaUpgradeTests, "test_dbSchemaUpgrades_%s" % (child.basename().split(".", 1)[0],), f)
+ setattr(SchemaUpgradeTests, "test_dbSchemaUpgrades_%s" % (child.basename().split(".", 1)[0],), f)
# Bind test methods for each addressbook data version
versions = set()
Modified: CalendarServer/trunk/txdav/common/datastore/work/inbox_cleanup.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/work/inbox_cleanup.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/work/inbox_cleanup.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -21,7 +21,7 @@
from twext.enterprise.dal.record import fromTable
from twext.enterprise.dal.syntax import Delete, Select, Count
-from twext.enterprise.queue import WorkItem
+from twext.enterprise.jobqueue import WorkItem
from twext.python.log import Logger
from twisted.internet.defer import inlineCallbacks, returnValue
from twistedcaldav.config import config
Modified: CalendarServer/trunk/txdav/common/datastore/work/revision_cleanup.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/work/revision_cleanup.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/work/revision_cleanup.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -21,7 +21,7 @@
from twext.enterprise.dal.record import fromTable
from twext.enterprise.dal.syntax import Delete, Select, Max
-from twext.enterprise.queue import WorkItem
+from twext.enterprise.jobqueue import WorkItem
from twext.python.log import Logger
from twisted.internet.defer import inlineCallbacks, returnValue
from twistedcaldav.config import config
Modified: CalendarServer/trunk/txdav/common/datastore/work/test/test_inbox_cleanup.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/work/test/test_inbox_cleanup.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/work/test/test_inbox_cleanup.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -20,7 +20,7 @@
from twext.enterprise.dal.syntax import Select, Update, Parameter
-from twext.enterprise.queue import WorkItem
+from twext.enterprise.jobqueue import WorkItem
from twext.python.clsprop import classproperty
from twisted.internet.defer import inlineCallbacks
from twisted.trial.unittest import TestCase
Modified: CalendarServer/trunk/txdav/common/datastore/work/test/test_revision_cleanup.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/work/test/test_revision_cleanup.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/common/datastore/work/test/test_revision_cleanup.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -17,7 +17,7 @@
from twext.enterprise.dal.syntax import Select
-from twext.enterprise.queue import WorkItem
+from twext.enterprise.jobqueue import WorkItem
from twext.python.clsprop import classproperty
from twisted.internet.defer import inlineCallbacks, returnValue
from twisted.trial.unittest import TestCase
Modified: CalendarServer/trunk/txdav/who/groups.py
===================================================================
--- CalendarServer/trunk/txdav/who/groups.py 2014-03-01 16:17:48 UTC (rev 12781)
+++ CalendarServer/trunk/txdav/who/groups.py 2014-03-01 16:21:25 UTC (rev 12782)
@@ -21,7 +21,7 @@
from twext.enterprise.dal.record import fromTable
from twext.enterprise.dal.syntax import Delete, Select
-from twext.enterprise.queue import WorkItem, PeerConnectionPool
+from twext.enterprise.jobqueue import WorkItem, PeerConnectionPool
from txdav.who.delegates import allGroupDelegates
from twext.who.idirectory import RecordType
from twisted.internet.defer import inlineCallbacks, returnValue
@@ -185,6 +185,7 @@
# TODO: Pull this over from groupcacher branch
+
@inlineCallbacks
def expandedMembers(record, members=None, records=None):
"""
@@ -211,6 +212,7 @@
returnValue(members)
+
def diffAssignments(old, new):
"""
Compare two proxy assignment lists and return their differences in the form
@@ -266,7 +268,6 @@
# externalAssignments = (yield self.externalProxiesSource())
# yield self.applyExternalAssignments(txn, externalAssignments)
-
# Figure out which groups matter
groupGUIDs = yield self.groupsToRefresh(txn)
self.log.debug(
@@ -297,13 +298,13 @@
for (
delegatorGUID, (readDelegateGUID, writeDelegateGUID)
) in changed:
- readDelegateGroupID = writeDelegateGroupID = None
+ readDelegateGroupID = writeDelegateGroupID = None
if readDelegateGUID:
- readDelegateGroupID, name, hash = (
+ readDelegateGroupID, _ignore_name, hash = (
yield txn.groupByGUID(readDelegateGUID)
)
if writeDelegateGUID:
- writeDelegateGroupID, name, hash = (
+ writeDelegateGroupID, _ignore_name, hash = (
yield txn.groupByGUID(writeDelegateGUID)
)
yield txn.assignExternalDelegates(
@@ -317,7 +318,6 @@
)
-
@inlineCallbacks
def refreshGroup(self, txn, groupGUID):
# Does the work of a per-group refresh work item
@@ -331,7 +331,7 @@
for member in members:
membershipHashContent.update(str(member.guid))
membershipHash = membershipHashContent.hexdigest()
- groupID, cachedName, cachedMembershipHash = (
+ groupID, _ignore_cachedName, cachedMembershipHash = (
yield txn.groupByGUID(groupGUID)
)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20140301/3b11f353/attachment-0001.html>
More information about the calendarserver-changes
mailing list