[CalendarServer-changes] [11108] CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common
source_changes at macosforge.org
source_changes at macosforge.org
Fri Apr 26 19:33:05 PDT 2013
Revision: 11108
http://trac.calendarserver.org//changeset/11108
Author: cdaboo at apple.com
Date: 2013-04-26 19:33:05 -0700 (Fri, 26 Apr 2013)
Log Message:
-----------
Schema and data upgrade.
Modified Paths:
--------------
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/__init__.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/current-oracle-dialect.sql
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/current.sql
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/test/test_sql.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/test/test_sql_tables.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/file/__init__.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/test/__init__.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/test/test_upgrade.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_1_to_2.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_2_to_3.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/util.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/icommondatastore.py
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/inotifications.py
Added Paths:
-----------
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/old/oracle-dialect/v18.sql
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/old/postgres-dialect/v18.sql
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_18_to_19.sql
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_18_to_19.sql
CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_3_to_4.py
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/__init__.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/__init__.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/__init__.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -13,4 +13,3 @@
# See the License for the specific language governing permissions and
# limitations under the License.
##
-
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/current-oracle-dialect.sql
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/current-oracle-dialect.sql 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/current-oracle-dialect.sql 2013-04-27 02:33:05 UTC (rev 11108)
@@ -24,6 +24,8 @@
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,
+ "DEFAULT_TASKS" integer default null,
"CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
"MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
);
@@ -310,8 +312,8 @@
"VALUE" nvarchar2(255)
);
-insert into CALENDARSERVER (NAME, VALUE) values ('VERSION', '18');
-insert into CALENDARSERVER (NAME, VALUE) values ('CALENDAR-DATAVERSION', '3');
+insert into CALENDARSERVER (NAME, VALUE) values ('VERSION', '19');
+insert into CALENDARSERVER (NAME, VALUE) values ('CALENDAR-DATAVERSION', '4');
insert into CALENDARSERVER (NAME, VALUE) values ('ADDRESSBOOK-DATAVERSION', '1');
create index NOTIFICATION_NOTIFICA_f891f5f9 on NOTIFICATION (
NOTIFICATION_HOME_RESOURCE_ID
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/current.sql
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/current.sql 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/current.sql 2013-04-27 02:33:05 UTC (rev 11108)
@@ -578,6 +578,6 @@
VALUE varchar(255)
);
-insert into CALENDARSERVER values ('VERSION', '18');
-insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '3');
+insert into CALENDARSERVER values ('VERSION', '19');
+insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '4');
insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '1');
Added: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/old/oracle-dialect/v18.sql
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/old/oracle-dialect/v18.sql (rev 0)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/old/oracle-dialect/v18.sql 2013-04-27 02:33:05 UTC (rev 11108)
@@ -0,0 +1,404 @@
+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,
+ "DATAVERSION" integer default 0 not null
+);
+
+create table CALENDAR_HOME_METADATA (
+ "RESOURCE_ID" integer primary key references CALENDAR_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 CALENDAR (
+ "RESOURCE_ID" integer primary key
+);
+
+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
+);
+
+create table NOTIFICATION (
+ "RESOURCE_ID" integer primary key,
+ "NOTIFICATION_HOME_RESOURCE_ID" integer not null references NOTIFICATION_HOME,
+ "NOTIFICATION_UID" nvarchar2(255),
+ "XML_TYPE" nvarchar2(255),
+ "XML_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,
+ "CALENDAR_RESOURCE_NAME" nvarchar2(255),
+ "BIND_MODE" integer not null,
+ "BIND_STATUS" integer not null,
+ "MESSAGE" nclob,
+ 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);
+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);
+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,
+ "OWNER_UID" nvarchar2(255) unique,
+ "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 ADDRESSBOOK (
+ "RESOURCE_ID" integer primary key
+);
+
+create table ADDRESSBOOK_METADATA (
+ "RESOURCE_ID" integer primary key references ADDRESSBOOK on delete cascade,
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC'
+);
+
+create table ADDRESSBOOK_BIND (
+ "ADDRESSBOOK_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME,
+ "ADDRESSBOOK_RESOURCE_ID" integer not null references ADDRESSBOOK on delete cascade,
+ "ADDRESSBOOK_RESOURCE_NAME" nvarchar2(255),
+ "BIND_MODE" integer not null,
+ "BIND_STATUS" integer not null,
+ "MESSAGE" nclob,
+ primary key("ADDRESSBOOK_HOME_RESOURCE_ID", "ADDRESSBOOK_RESOURCE_ID"),
+ unique("ADDRESSBOOK_HOME_RESOURCE_ID", "ADDRESSBOOK_RESOURCE_NAME")
+);
+
+create table ADDRESSBOOK_OBJECT (
+ "RESOURCE_ID" integer primary key,
+ "ADDRESSBOOK_RESOURCE_ID" integer not null references ADDRESSBOOK on delete cascade,
+ "RESOURCE_NAME" nvarchar2(255),
+ "VCARD_TEXT" nclob,
+ "VCARD_UID" nvarchar2(255),
+ "MD5" nchar(32),
+ "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC',
+ unique("ADDRESSBOOK_RESOURCE_ID", "RESOURCE_NAME"),
+ unique("ADDRESSBOOK_RESOURCE_ID", "VCARD_UID")
+);
+
+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
+);
+
+create table ADDRESSBOOK_OBJECT_REVISIONS (
+ "ADDRESSBOOK_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME,
+ "ADDRESSBOOK_RESOURCE_ID" integer references ADDRESSBOOK,
+ "ADDRESSBOOK_NAME" nvarchar2(255) default null,
+ "RESOURCE_NAME" nvarchar2(255),
+ "REVISION" integer not null,
+ "DELETED" integer not null
+);
+
+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,
+ 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)
+);
+
+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 CALENDARSERVER (
+ "NAME" nvarchar2(255) primary key,
+ "VALUE" nvarchar2(255)
+);
+
+insert into CALENDARSERVER (NAME, VALUE) values ('VERSION', '18');
+insert into CALENDARSERVER (NAME, VALUE) values ('CALENDAR-DATAVERSION', '3');
+insert into CALENDARSERVER (NAME, VALUE) values ('ADDRESSBOOK-DATAVERSION', '1');
+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 ADDRESSBOOK_BIND_RESO_205aa75c on ADDRESSBOOK_BIND (
+ ADDRESSBOOK_RESOURCE_ID
+);
+
+create index CALENDAR_OBJECT_REVIS_3a3956c4 on CALENDAR_OBJECT_REVISIONS (
+ CALENDAR_HOME_RESOURCE_ID,
+ CALENDAR_RESOURCE_ID
+);
+
+create index CALENDAR_OBJECT_REVIS_2643d556 on CALENDAR_OBJECT_REVISIONS (
+ CALENDAR_RESOURCE_ID,
+ RESOURCE_NAME
+);
+
+create index CALENDAR_OBJECT_REVIS_265c8acf on CALENDAR_OBJECT_REVISIONS (
+ CALENDAR_RESOURCE_ID,
+ REVISION
+);
+
+create index ADDRESSBOOK_OBJECT_RE_f460d62d on ADDRESSBOOK_OBJECT_REVISIONS (
+ ADDRESSBOOK_HOME_RESOURCE_ID,
+ ADDRESSBOOK_RESOURCE_ID
+);
+
+create index ADDRESSBOOK_OBJECT_RE_9a848f39 on ADDRESSBOOK_OBJECT_REVISIONS (
+ ADDRESSBOOK_RESOURCE_ID,
+ RESOURCE_NAME
+);
+
+create index ADDRESSBOOK_OBJECT_RE_cb101e6b on ADDRESSBOOK_OBJECT_REVISIONS (
+ ADDRESSBOOK_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
+);
+
Added: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/old/postgres-dialect/v18.sql
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/old/postgres-dialect/v18.sql (rev 0)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/old/postgres-dialect/v18.sql 2013-04-27 02:33:05 UTC (rev 11108)
@@ -0,0 +1,581 @@
+-- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-
+
+----
+-- Copyright (c) 2010-2013 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
+ DATAVERSION integer default 0 not null
+);
+
+----------------------------
+-- Calendar Home Metadata --
+----------------------------
+
+create table CALENDAR_HOME_METADATA (
+ RESOURCE_ID integer primary key references CALENDAR_HOME on delete cascade, -- implicit index
+ QUOTA_USED_BYTES integer default 0 not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+--------------
+-- Calendar --
+--------------
+
+create table CALENDAR (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
+);
+
+
+-----------------------
+-- 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
+);
+
+create table NOTIFICATION (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME,
+ NOTIFICATION_UID varchar(255) not null,
+ XML_TYPE varchar(255) not null,
+ XML_DATA text not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ unique(NOTIFICATION_UID, NOTIFICATION_HOME_RESOURCE_ID) -- implicit index
+);
+
+create index NOTIFICATION_NOTIFICATION_HOME_RESOURCE_ID on
+ NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);
+
+-------------------
+-- Calendar Bind --
+-------------------
+
+-- Joins CALENDAR_HOME and CALENDAR
+
+create table CALENDAR_BIND (
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
+ 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
+ MESSAGE text,
+
+ primary key(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID), -- implicit index
+ unique(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_NAME) -- implicit index
+);
+
+create index CALENDAR_BIND_RESOURCE_ID on CALENDAR_BIND(CALENDAR_RESOURCE_ID);
+
+-- Enumeration of calendar bind modes
+
+create table CALENDAR_BIND_MODE (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into CALENDAR_BIND_MODE values (0, 'own' );
+insert into CALENDAR_BIND_MODE values (1, 'read' );
+insert into CALENDAR_BIND_MODE values (2, 'write');
+insert into CALENDAR_BIND_MODE values (3, 'direct');
+
+-- Enumeration of statuses
+
+create table CALENDAR_BIND_STATUS (
+ ID integer primary key,
+ DESCRIPTION varchar(16) not null unique
+);
+
+insert into CALENDAR_BIND_STATUS values (0, 'invited' );
+insert into CALENDAR_BIND_STATUS values (1, 'accepted');
+insert into CALENDAR_BIND_STATUS values (2, 'declined');
+insert into CALENDAR_BIND_STATUS values (3, 'invalid');
+
+
+---------------------
+-- Calendar Object --
+---------------------
+
+create table CALENDAR_OBJECT (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ CALENDAR_RESOURCE_ID integer not null references CALENDAR on delete cascade,
+ RESOURCE_NAME varchar(255) not null,
+ ICALENDAR_TEXT text not null,
+ ICALENDAR_UID varchar(255) not null,
+ ICALENDAR_TYPE varchar(255) not null,
+ ATTACHMENTS_MODE integer default 0 not null, -- enum CALENDAR_OBJECT_ATTACHMENTS_MODE
+ DROPBOX_ID varchar(255),
+ ORGANIZER varchar(255),
+ 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);
+
+-- 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
+);
+
+
+-----------------------
+-- Resource Property --
+-----------------------
+
+create table RESOURCE_PROPERTY (
+ RESOURCE_ID integer not null, -- foreign key: *.RESOURCE_ID
+ NAME varchar(255) not null,
+ VALUE text not null, -- FIXME: xml?
+ VIEWER_UID varchar(255),
+
+ primary key (RESOURCE_ID, NAME, VIEWER_UID) -- implicit index
+);
+
+
+----------------------
+-- AddressBook Home --
+----------------------
+
+create table ADDRESSBOOK_HOME (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ OWNER_UID varchar(255) not null unique, -- implicit index
+ DATAVERSION integer default 0 not null
+);
+
+-------------------------------
+-- AddressBook Home 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)
+);
+
+-----------------
+-- AddressBook --
+-----------------
+
+create table ADDRESSBOOK (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
+);
+
+
+--------------------------
+-- AddressBook Metadata --
+--------------------------
+
+create table ADDRESSBOOK_METADATA (
+ RESOURCE_ID integer primary key references ADDRESSBOOK on delete cascade, -- implicit index
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+
+----------------------
+-- AddressBook Bind --
+----------------------
+
+-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK
+
+create table ADDRESSBOOK_BIND (
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
+ 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
+ MESSAGE text, -- FIXME: xml?
+
+ primary key (ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_ID), -- implicit index
+ unique (ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME) -- implicit index
+);
+
+create index ADDRESSBOOK_BIND_RESOURCE_ID on
+ ADDRESSBOOK_BIND(ADDRESSBOOK_RESOURCE_ID);
+
+create table ADDRESSBOOK_OBJECT (
+ RESOURCE_ID integer primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
+ ADDRESSBOOK_RESOURCE_ID integer not null references ADDRESSBOOK on delete cascade,
+ RESOURCE_NAME varchar(255) not null,
+ VCARD_TEXT text not null,
+ VCARD_UID varchar(255) not null,
+ MD5 char(32) not null,
+ CREATED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ MODIFIED timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+
+ unique (ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME), -- implicit index
+ unique (ADDRESSBOOK_RESOURCE_ID, VCARD_UID) -- implicit index
+);
+
+---------------
+-- Revisions --
+---------------
+
+create sequence REVISION_SEQ;
+
+
+---------------
+-- Revisions --
+---------------
+
+create table CALENDAR_OBJECT_REVISIONS (
+ CALENDAR_HOME_RESOURCE_ID integer not null references CALENDAR_HOME,
+ CALENDAR_RESOURCE_ID integer references CALENDAR,
+ CALENDAR_NAME varchar(255) default null,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null
+);
+
+create index CALENDAR_OBJECT_REVISIONS_HOME_RESOURCE_ID_CALENDAR_RESOURCE_ID
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID);
+
+create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, RESOURCE_NAME);
+
+create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+ on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, REVISION);
+
+-------------------------------
+-- AddressBook Object Revisions --
+-------------------------------
+
+create table ADDRESSBOOK_OBJECT_REVISIONS (
+ ADDRESSBOOK_HOME_RESOURCE_ID integer not null references ADDRESSBOOK_HOME,
+ ADDRESSBOOK_RESOURCE_ID integer references ADDRESSBOOK,
+ ADDRESSBOOK_NAME varchar(255) default null,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null
+);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID_ADDRESSBOOK_RESOURCE_ID
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_ID);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME);
+
+create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+ on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, REVISION);
+
+-----------------------------------
+-- Notification Object Revisions --
+-----------------------------------
+
+create table NOTIFICATION_OBJECT_REVISIONS (
+ NOTIFICATION_HOME_RESOURCE_ID integer not null references NOTIFICATION_HOME on delete cascade,
+ RESOURCE_NAME varchar(255),
+ REVISION integer default nextval('REVISION_SEQ') not null,
+ DELETED boolean not null,
+
+ unique(NOTIFICATION_HOME_RESOURCE_ID, RESOURCE_NAME) -- implicit index
+);
+
+create index NOTIFICATION_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+ on NOTIFICATION_OBJECT_REVISIONS(NOTIFICATION_HOME_RESOURCE_ID, REVISION);
+
+-------------------------------------------
+-- Apple Push Notification Subscriptions --
+-------------------------------------------
+
+create table APN_SUBSCRIPTIONS (
+ TOKEN varchar(255) not null,
+ RESOURCE_KEY varchar(255) not null,
+ MODIFIED integer not null,
+ SUBSCRIBER_GUID varchar(255) not null,
+ 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,
+ 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,
+ 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,
+ 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,
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP),
+ PUSH_ID varchar(255) not null
+);
+
+-----------------
+-- GroupCacher --
+-----------------
+
+create table GROUP_CACHER_POLLING_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null,
+ NOT_BEFORE timestamp default timezone('UTC', CURRENT_TIMESTAMP)
+);
+
+
+--------------------
+-- Schema Version --
+--------------------
+
+create table CALENDARSERVER (
+ NAME varchar(255) primary key, -- implicit index
+ VALUE varchar(255)
+);
+
+insert into CALENDARSERVER values ('VERSION', '18');
+insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '3');
+insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '1');
Added: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_18_to_19.sql
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_18_to_19.sql (rev 0)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_18_to_19.sql 2013-04-27 02:33:05 UTC (rev 11108)
@@ -0,0 +1,30 @@
+----
+-- Copyright (c) 2012-2013 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 18 to 19 --
+---------------------------------------------------
+
+-- Calendar home related updates
+
+alter table ATTACHMENT
+ add ("DEFAULT_EVENTS" integer default null,
+ "DEFAULT_TASKS" integer default null);
+
+
+-- Now update the version
+-- No data upgrades
+update CALENDARSERVER set VALUE = '19' where NAME = 'VERSION';
Added: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_18_to_19.sql
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_18_to_19.sql (rev 0)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_18_to_19.sql 2013-04-27 02:33:05 UTC (rev 11108)
@@ -0,0 +1,31 @@
+----
+-- Copyright (c) 2012-2013 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 18 to 19 --
+---------------------------------------------------
+
+
+-- Calendar home related updates
+
+alter table CALENDAR_HOME_METADATA
+ add column DEFAULT_EVENTS integer default null,
+ add column DEFAULT_TASKS integer default null;
+
+
+-- Now update the version
+-- No data upgrades
+update CALENDARSERVER set VALUE = '19' where NAME = 'VERSION';
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/test/test_sql.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/test/test_sql.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/test/test_sql.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -26,9 +26,9 @@
from twisted.trial.unittest import TestCase
from twisted.internet.defer import Deferred
-from txdav.common.datastore.sql import log, CommonStoreTransactionMonitor,\
+from txdav.common.datastore.sql import log, CommonStoreTransactionMonitor, \
CommonHome, CommonHomeChild, ECALENDARTYPE
-from txdav.common.datastore.sql_tables import schema, CALENDAR_BIND_TABLE,\
+from txdav.common.datastore.sql_tables import schema, CALENDAR_BIND_TABLE, \
CALENDAR_OBJECT_REVISIONS_TABLE
from txdav.common.datastore.test.util import CommonCommonTests, buildStore
from txdav.common.icommondatastore import AllRetriesFailed
@@ -61,7 +61,7 @@
"""
txn.execSQL works with all logging options on.
"""
-
+
# Patch config to turn on logging then rebuild the store
self.patch(self._sqlStore, "logLabels", True)
self.patch(self._sqlStore, "logStats", True)
@@ -70,7 +70,7 @@
txn = self.transactionUnderTest()
cs = schema.CALENDARSERVER
version = (yield Select(
- [cs.VALUE,],
+ [cs.VALUE, ],
From=cs,
Where=cs.NAME == 'VERSION',
).on(txn))
@@ -78,24 +78,25 @@
self.assertEqual(len(version), 1)
self.assertEqual(len(version[0]), 1)
+
def test_logWaits(self):
"""
CommonStoreTransactionMonitor logs waiting transactions.
"""
-
+
c = Clock()
self.patch(CommonStoreTransactionMonitor, "callLater", c.callLater)
# Patch config to turn on log waits then rebuild the store
self.patch(self._sqlStore, "logTransactionWaits", 1)
-
+
ctr = [0]
def counter(_ignore):
ctr[0] += 1
self.patch(log, "error", counter)
- txn = self.transactionUnderTest()
-
+ txn = self.transactionUnderTest()
+
c.advance(2)
self.assertNotEqual(ctr[0], 0)
txn.abort()
@@ -105,13 +106,13 @@
"""
CommonStoreTransactionMonitor terminates long transactions.
"""
-
+
c = Clock()
self.patch(CommonStoreTransactionMonitor, "callLater", c.callLater)
# Patch config to turn on transaction timeouts then rebuild the store
self.patch(self._sqlStore, "timeoutTransactions", 1)
-
+
ctr = [0]
def counter(_ignore):
ctr[0] += 1
@@ -128,14 +129,14 @@
"""
CommonStoreTransactionMonitor logs waiting transactions and terminates long transactions.
"""
-
+
c = Clock()
self.patch(CommonStoreTransactionMonitor, "callLater", c.callLater)
# Patch config to turn on log waits then rebuild the store
self.patch(self._sqlStore, "logTransactionWaits", 1)
self.patch(self._sqlStore, "timeoutTransactions", 2)
-
+
ctr = [0, 0]
def counter(logStr):
if "wait" in logStr:
@@ -145,18 +146,19 @@
self.patch(log, "error", counter)
txn = self.transactionUnderTest()
-
+
c.advance(2)
self.assertNotEqual(ctr[0], 0)
self.assertNotEqual(ctr[1], 0)
self.assertTrue(txn._sqlTxn._completed)
+
@inlineCallbacks
def test_subtransactionOK(self):
"""
txn.subtransaction runs loop once.
"""
-
+
txn = self.transactionUnderTest()
ctr = [0]
@@ -164,11 +166,11 @@
ctr[0] += 1
cs = schema.CALENDARSERVER
return Select(
- [cs.VALUE,],
+ [cs.VALUE, ],
From=cs,
Where=cs.NAME == 'VERSION',
).on(subtxn)
-
+
(yield txn.subtransaction(_test, retries=0))[0][0]
self.assertEqual(ctr[0], 1)
@@ -178,7 +180,7 @@
"""
txn.subtransaction runs loop twice when one failure.
"""
-
+
txn = self.transactionUnderTest()
ctr = [0]
@@ -188,11 +190,11 @@
raise ValueError
cs = schema.CALENDARSERVER
return Select(
- [cs.VALUE,],
+ [cs.VALUE, ],
From=cs,
Where=cs.NAME == 'VERSION',
).on(subtxn)
-
+
(yield txn.subtransaction(_test, retries=1))[0][0]
self.assertEqual(ctr[0], 2)
@@ -202,7 +204,7 @@
"""
txn.subtransaction runs loop once when one failure and no retries.
"""
-
+
txn = self.transactionUnderTest()
ctr = [0]
@@ -211,11 +213,11 @@
raise ValueError
cs = schema.CALENDARSERVER
return Select(
- [cs.VALUE,],
+ [cs.VALUE, ],
From=cs,
Where=cs.NAME == 'VERSION',
).on(subtxn)
-
+
try:
(yield txn.subtransaction(_test, retries=0))[0][0]
except AllRetriesFailed:
@@ -231,7 +233,7 @@
txn.subtransaction runs loop three times when all fail and two retries
requested.
"""
-
+
txn = self.transactionUnderTest()
ctr = [0]
@@ -240,11 +242,11 @@
raise ValueError
cs = schema.CALENDARSERVER
return Select(
- [cs.VALUE,],
+ [cs.VALUE, ],
From=cs,
Where=cs.NAME == 'VERSION',
).on(subtxn)
-
+
try:
(yield txn.subtransaction(_test, retries=2))[0][0]
except AllRetriesFailed:
@@ -286,11 +288,11 @@
"""
CommonHomeChild._changeRevision actions.
"""
-
+
class TestCommonHome(CommonHome):
_bindTable = CALENDAR_BIND_TABLE
_revisionsTable = CALENDAR_OBJECT_REVISIONS_TABLE
-
+
class TestCommonHomeChild(CommonHomeChild):
_homeChildSchema = schema.CALENDAR
_homeChildMetaDataSchema = schema.CALENDAR_METADATA
@@ -298,14 +300,14 @@
_revisionsSchema = schema.CALENDAR_OBJECT_REVISIONS
_bindTable = CALENDAR_BIND_TABLE
_revisionsTable = CALENDAR_OBJECT_REVISIONS_TABLE
-
+
def resourceType(self):
return davxml.ResourceType.calendar
-
+
txn = self.transactionUnderTest()
home = yield txn.homeWithUID(ECALENDARTYPE, "uid", create=True)
homeChild = yield TestCommonHomeChild.create(home, "B")
-
+
# insert test
token = yield homeChild.syncToken()
yield homeChild._changeRevision("insert", "C")
@@ -427,7 +429,6 @@
from uuid import UUID
-exampleUID = UUID("a"*32)
+exampleUID = UUID("a" * 32)
denormalizedUID = str(exampleUID)
normalizedUID = denormalizedUID.upper()
-
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/test/test_sql_tables.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/test/test_sql_tables.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/test/test_sql_tables.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -87,7 +87,7 @@
)
schema = pathObj.getContent()
pos = schema.find("('VERSION', '")
- version = int(schema[pos+13:pos+15])
+ version = int(schema[pos + 13:pos + 15])
self.assertIn("insert into CALENDARSERVER (NAME, VALUE) "
"values ('VERSION', '%s');" % version,
self.translated())
@@ -296,8 +296,8 @@
r2 = result.next()
self.assertEquals(r2, "select bang from boop")
self.assertRaises(StopIteration, result.next)
-
+
def test_returnOneComplexStatement(self):
"""
One complex sql statement yields a single string
@@ -312,7 +312,7 @@
WHERE
CL.CODE = I.CODE AND
CL.CATEGORY = I.CATEGORY AND
- CL.UP_DATE =
+ CL.UP_DATE =
(SELECT
MAX(CL2.UP_DATE)
FROM
@@ -325,7 +325,7 @@
I.EMISSION BETWEEN DATE1 AND DATE2;''')
result = splitSQLString(bigSQL)
r1 = result.next()
- self.assertEquals(r1, bigSQL.rstrip(";"))
+ self.assertEquals(r1, bigSQL.rstrip(";"))
self.assertRaises(StopIteration, result.next)
@@ -354,8 +354,8 @@
"""
sql = dedent(
'''SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
- FROM EGM
- INNER JOIN BioEntity
+ FROM EGM
+ INNER JOIN BioEntity
ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId
OPTION (MERGE JOIN);''')
plsql = dedent(
@@ -371,13 +371,14 @@
COMMIT;
END;''')
s2 = "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+plsql)
+ result = splitSQLString(sql + plsql)
r1 = result.next()
self.assertEquals(r1, sql.rstrip(";"))
r2 = result.next()
self.assertEquals(r2, s2)
self.assertRaises(StopIteration, result.next)
+
def test_actualSchemaUpgrade(self):
"""
A real-world schema upgrade is split into the expected number of statements,
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/file/__init__.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/file/__init__.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/file/__init__.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -13,4 +13,3 @@
# See the License for the specific language governing permissions and
# limitations under the License.
##
-
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/test/__init__.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/test/__init__.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/test/__init__.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -13,4 +13,3 @@
# See the License for the specific language governing permissions and
# limitations under the License.
##
-
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/test/test_upgrade.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/test/test_upgrade.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/test/test_upgrade.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -32,15 +32,21 @@
Tests for L{UpgradeDatabaseSchemaService}.
"""
- def _getSchemaVersion(self, fp, versionKey):
+ @staticmethod
+ def _getRawSchemaVersion(fp, versionKey):
schema = fp.getContent()
found = re.search("insert into CALENDARSERVER (\(NAME, VALUE\) )?values \('%s', '(\d+)'\);" % (versionKey,), schema)
+ return int(found.group(2)) if found else None
+
+
+ def _getSchemaVersion(self, fp, versionKey):
+ found = SchemaUpgradeTests._getRawSchemaVersion(fp, versionKey)
if found is None:
if versionKey == "VERSION":
self.fail("Could not determine schema version for: %s" % (fp,))
else:
return 1
- return int(found.group(2))
+ return found
def test_scanUpgradeFiles(self):
@@ -129,7 +135,7 @@
@inlineCallbacks
- def test_dbSchemaUpgrades(self):
+ def _dbSchemaUpgrades(self, child):
"""
This does a full DB test of all possible upgrade paths. For each old schema, it loads it into the DB
then runs the upgrade service. This ensures all the upgrade.sql files work correctly - at least for
@@ -177,37 +183,36 @@
test_upgrader = UpgradeDatabaseSchemaService(None, None)
expected_version = self._getSchemaVersion(test_upgrader.schemaLocation.child("current.sql"), "VERSION")
- for child in test_upgrader.schemaLocation.child("old").child(POSTGRES_DIALECT).globChildren("*.sql"):
- # Upgrade allowed
- upgrader = UpgradeDatabaseSchemaService(store, None)
- yield _loadOldSchema(child)
- yield upgrader.databaseUpgrade()
- new_version = yield _loadVersion()
- yield _unloadOldSchema()
+ # Upgrade allowed
+ upgrader = UpgradeDatabaseSchemaService(store, None)
+ yield _loadOldSchema(child)
+ yield upgrader.databaseUpgrade()
+ new_version = yield _loadVersion()
+ yield _unloadOldSchema()
- self.assertEqual(new_version, expected_version)
+ self.assertEqual(new_version, expected_version)
- # Upgrade disallowed
- upgrader = UpgradeDatabaseSchemaService(store, None, failIfUpgradeNeeded=True, stopOnFail=False)
- yield _loadOldSchema(child)
- old_version = yield _loadVersion()
- try:
- yield upgrader.databaseUpgrade()
- except RuntimeError:
- pass
- except Exception:
- self.fail("RuntimeError not raised")
- else:
- self.fail("RuntimeError not raised")
- new_version = yield _loadVersion()
- yield _unloadOldSchema()
+ # Upgrade disallowed
+ upgrader = UpgradeDatabaseSchemaService(store, None, failIfUpgradeNeeded=True, stopOnFail=False)
+ yield _loadOldSchema(child)
+ old_version = yield _loadVersion()
+ try:
+ yield upgrader.databaseUpgrade()
+ except RuntimeError:
+ pass
+ except Exception:
+ self.fail("RuntimeError not raised")
+ else:
+ self.fail("RuntimeError not raised")
+ new_version = yield _loadVersion()
+ yield _unloadOldSchema()
- self.assertEqual(old_version, new_version)
+ self.assertEqual(old_version, new_version)
@inlineCallbacks
- def test_dbDataUpgrades(self):
+ def _dbDataUpgrades(self, version):
"""
This does a full DB test of all possible data upgrade paths. For each old schema, it loads it into the DB
then runs the data upgrade service. This ensures all the upgrade_XX.py files work correctly - at least for
@@ -259,15 +264,31 @@
test_upgrader = UpgradeDatabaseSchemaService(None, None)
expected_version = self._getSchemaVersion(test_upgrader.schemaLocation.child("current.sql"), "CALENDAR-DATAVERSION")
- versions = set()
- for child in test_upgrader.schemaLocation.child("old").child(POSTGRES_DIALECT).globChildren("*.sql"):
- versions.add(self._getSchemaVersion(child, "CALENDAR-DATAVERSION"))
- for oldVersion in sorted(versions):
- upgrader = UpgradeDatabaseDataService(store, None)
- yield _loadOldData(test_upgrader.schemaLocation.child("current.sql"), oldVersion)
- yield upgrader.databaseUpgrade()
- new_version = yield _loadVersion()
- yield _unloadOldData()
+ oldVersion = version
+ upgrader = UpgradeDatabaseDataService(store, None)
+ yield _loadOldData(test_upgrader.schemaLocation.child("current.sql"), oldVersion)
+ yield upgrader.databaseUpgrade()
+ new_version = yield _loadVersion()
+ yield _unloadOldData()
- self.assertEqual(new_version, expected_version)
+ self.assertEqual(new_version, expected_version)
+
+
+test_upgrader = UpgradeDatabaseSchemaService(None, None)
+
+# Bind test methods for each schema version
+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)
+
+# Bind test methods for each calendar data version
+versions = set()
+for child in test_upgrader.schemaLocation.child("old").child(POSTGRES_DIALECT).globChildren("*.sql"):
+ version = SchemaUpgradeTests._getRawSchemaVersion(child, "CALENDAR-DATAVERSION")
+ versions.add(version if version else 1)
+for version in sorted(versions):
+ def f(self, lversion=version):
+ return self._dbDataUpgrades(lversion)
+ setattr(SchemaUpgradeTests, "test_dbDataUpgrades_%s" % (version,), f)
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_1_to_2.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_1_to_2.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_1_to_2.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -84,6 +84,6 @@
Split each regular calendar in the home.
"""
yield home.splitCalendars()
-
+
# Do this to each calendar home not already at version 2
yield doToEachCalendarHomeNotAtVersion(sqlStore, UPGRADE_TO_VERSION, doIt)
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_2_to_3.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_2_to_3.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_2_to_3.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -39,4 +39,3 @@
yield updateDataVersion(
sqlStore, "CALENDAR-DATAVERSION", UPGRADE_TO_VERSION
)
-
Added: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_3_to_4.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_3_to_4.py (rev 0)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/upgrade_from_3_to_4.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -0,0 +1,123 @@
+# -*- test-case-name: txdav.common.datastore.upgrade.sql.test -*-
+##
+# Copyright (c) 2011-2013 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 twext.enterprise.dal.syntax import Update, Select, Delete, Parameter
+
+from twisted.internet.defer import inlineCallbacks
+
+from twistedcaldav import caldavxml, customxml
+
+from txdav.base.propertystore.base import PropertyName
+from txdav.common.datastore.sql_tables import schema
+from txdav.common.datastore.upgrade.sql.upgrades.util import rowsForProperty, updateDataVersion
+from txdav.xml.parser import WebDAVDocument
+
+"""
+Data upgrade from database version 3 to 4
+"""
+
+UPGRADE_TO_VERSION = 4
+BATCH_SIZE = 100
+
+ at inlineCallbacks
+def doUpgrade(sqlStore):
+ """
+ Do the required upgrade steps.
+ """
+ yield moveDefaultCalendarProperties(sqlStore)
+
+ # Always bump the DB value
+ yield updateDataVersion(sqlStore, "CALENDAR-DATAVERSION", UPGRADE_TO_VERSION)
+
+
+
+ at inlineCallbacks
+def moveDefaultCalendarProperties(sqlStore):
+ """
+ Need to move all the CalDAV:default-calendar and CS:default-tasks properties in the
+ RESOURCE_PROPERTY table to the new CALENDAR_HOME_METADATA table columns, extracting
+ the new value from the XML property.
+ """
+
+ meta = schema.CALENDAR_HOME_METADATA
+ yield _processProperty(sqlStore, caldavxml.ScheduleDefaultCalendarURL, meta.DEFAULT_EVENTS)
+ yield _processProperty(sqlStore, customxml.ScheduleDefaultTasksURL, meta.DEFAULT_TASKS)
+
+
+
+ at inlineCallbacks
+def _processProperty(sqlStore, propname, colname):
+ """
+ Move the specified property value to the matching CALENDAR_HOME_METADATA table column.
+
+ Since the number of calendar homes may well be large, we need to do this in batches.
+ """
+
+ meta = schema.CALENDAR_HOME_METADATA
+ cb = schema.CALENDAR_BIND
+ rp = schema.RESOURCE_PROPERTY
+
+ try:
+ while True:
+ sqlTxn = sqlStore.newTransaction()
+ rows = (yield rowsForProperty(sqlTxn, propname, batch=BATCH_SIZE))
+ if len(rows) == 0:
+ yield sqlTxn.commit()
+ break
+ delete_ids = []
+ for inbox_rid, value in rows:
+ delete_ids.append(inbox_rid)
+ ids = yield Select(
+ [cb.CALENDAR_HOME_RESOURCE_ID, ],
+ From=cb,
+ Where=cb.CALENDAR_RESOURCE_ID == inbox_rid,
+ ).on(sqlTxn)
+ if len(ids) > 0:
+
+ calendarHome = (yield sqlTxn.calendarHomeWithResourceID(ids[0][0]))
+ if calendarHome is not None:
+
+ prop = WebDAVDocument.fromString(value).root_element
+ defaultCalendar = str(prop.children[0])
+ parts = defaultCalendar.split("/")
+ if len(parts) == 5:
+
+ calendarName = parts[-1]
+ calendarHomeUID = parts[-2]
+ expectedHome = (yield sqlTxn.calendarHomeWithUID(calendarHomeUID))
+ if expectedHome is not None and expectedHome.id() == calendarHome.id():
+
+ calendar = (yield calendarHome.calendarWithName(calendarName))
+ if calendar is not None:
+
+ yield Update(
+ {colname : calendar.id(), },
+ Where=(meta.RESOURCE_ID == calendarHome.id())
+ ).on(sqlTxn)
+
+ # Always delete the row so that batch processing works correctly
+ yield Delete(
+ From=rp,
+ Where=(rp.RESOURCE_ID.In(Parameter("ids", len(delete_ids)))).And
+ (rp.NAME == PropertyName.fromElement(propname).toString()),
+ ).on(sqlTxn, ids=delete_ids)
+
+ yield sqlTxn.commit()
+
+ except RuntimeError:
+ yield sqlTxn.abort()
+ raise
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/util.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/util.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/datastore/upgrade/sql/upgrades/util.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -20,18 +20,21 @@
from txdav.common.datastore.sql_tables import schema
@inlineCallbacks
-def rowsForProperty(txn, propelement):
+def rowsForProperty(txn, propelement, batch=None):
pname = PropertyName.fromElement(propelement)
rp = schema.RESOURCE_PROPERTY
rows = yield Select(
- [rp.RESOURCE_ID, rp.VALUE,],
+ [rp.RESOURCE_ID, rp.VALUE, ],
From=rp,
Where=rp.NAME == pname.toString(),
+ Limit=batch,
).on(txn)
-
+
returnValue(rows)
+
+
@inlineCallbacks
def removeProperty(txn, propelement):
pname = PropertyName.fromElement(propelement)
@@ -42,10 +45,12 @@
Where=rp.NAME == pname.toString(),
).on(txn)
+
+
@inlineCallbacks
def updateDataVersion(store, key, version):
- txn = store.newTransaction("updateDataVersion")
+ txn = store.newTransaction("updateDataVersion")
cs = schema.CALENDARSERVER
yield Update(
{cs.VALUE: version},
@@ -53,12 +58,18 @@
).on(txn)
yield txn.commit()
+
+
def updateCalendarDataVersion(store, version):
return updateDataVersion(store, "CALENDAR-DATAVERSION", version)
+
+
def updateAddressBookDataVersion(store, version):
return updateDataVersion(store, "ADDRESSBOOK-DATAVERSION", version)
+
+
@inlineCallbacks
def doToEachCalendarHomeNotAtVersion(store, version, doIt):
"""
@@ -67,28 +78,28 @@
"""
while True:
-
+
# Get the next home with an old version
- txn = store.newTransaction("updateDataVersion")
- try:
+ txn = store.newTransaction("updateDataVersion")
+ try:
ch = schema.CALENDAR_HOME
rows = yield Select(
- [ch.RESOURCE_ID, ch.OWNER_UID,],
+ [ch.RESOURCE_ID, ch.OWNER_UID, ],
From=ch,
Where=ch.DATAVERSION < version,
OrderBy=ch.OWNER_UID,
Limit=1,
).on(txn)
-
+
if len(rows) == 0:
yield txn.commit()
returnValue(None)
-
+
# Apply to the home
resource_id, _ignore_owner_uid = rows[0]
home = yield txn.calendarHomeWithResourceID(resource_id)
yield doIt(home)
-
+
# Update the home to the current version
yield Update(
{ch.DATAVERSION: version},
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/icommondatastore.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/icommondatastore.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/icommondatastore.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -258,7 +258,7 @@
stores.
"""
- def notificationsWithUID(uid):
+ def notificationsWithUID(uid): #@NoSelf
"""
Retrieve the notification collection for the principal with the given
C{uid}.
@@ -267,7 +267,7 @@
notification collection exists.
"""
- def addAPNSubscription(token, key, timestamp, subscriber, userAgent, ipAddr):
+ def addAPNSubscription(token, key, timestamp, subscriber, userAgent, ipAddr): #@NoSelf
"""
Add (or update) a subscription entry in the database.
@@ -290,7 +290,7 @@
@type ipAddr: C{str}
"""
- def removeAPNSubscription(token, key):
+ def removeAPNSubscription(token, key): #@NoSelf
"""
Remove a subscription entry from the database.
@@ -301,7 +301,7 @@
@type key: C{str}
"""
- def purgeOldAPNSubscriptions(olderThan):
+ def purgeOldAPNSubscriptions(olderThan): #@NoSelf
"""
Remove all subscription entries whose modified timestamp
is older than the provided timestamp.
@@ -310,7 +310,7 @@
@type token: C{int}
"""
- def apnSubscriptionsByToken(token):
+ def apnSubscriptionsByToken(token): #@NoSelf
"""
Retrieve all subscription entries for the token.
@@ -320,7 +320,7 @@
@return: tuples of (key, timestamp, guid)
"""
- def apnSubscriptionsByKey(key):
+ def apnSubscriptionsByKey(key): #@NoSelf
"""
Retrieve all subscription entries for the key.
@@ -330,7 +330,7 @@
@return: tuples of (token, guid)
"""
- def apnSubscriptionsBySubscriber(guid):
+ def apnSubscriptionsBySubscriber(guid): #@NoSelf
"""
Retrieve all subscription entries for the subscriber.
@@ -340,7 +340,7 @@
@return: tuples of (token, key, timestamp, userAgent, ipAddr)
"""
- def imipCreateToken(organizer, attendee, icaluid, token=None):
+ def imipCreateToken(organizer, attendee, icaluid, token=None): #@NoSelf
"""
Add an entry in the database; if no token is provided, one will be
generated.
@@ -355,7 +355,7 @@
@type token: C{str}
"""
- def imipLookupByToken(token):
+ def imipLookupByToken(token): #@NoSelf
"""
Returns the organizer, attendee, and icaluid corresponding to the token
@@ -363,7 +363,7 @@
@type token: C{str}
"""
- def imipGetToken(organizer, attendee, icaluid):
+ def imipGetToken(organizer, attendee, icaluid): #@NoSelf
"""
Returns the token (if any) corresponding to the given organizer, attendee,
and icaluid combination
@@ -376,7 +376,7 @@
@type organizer: C{str}
"""
- def imipRemoveToken(token):
+ def imipRemoveToken(token): #@NoSelf
"""
Removes the entry for the given token.
@@ -384,7 +384,7 @@
@type token: C{str}
"""
- def purgeOldIMIPTokens(olderThan):
+ def purgeOldIMIPTokens(olderThan): #@NoSelf
"""
Removes all tokens whose access time is before olderThan
"""
Modified: CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/inotifications.py
===================================================================
--- CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/inotifications.py 2013-04-27 01:36:48 UTC (rev 11107)
+++ CalendarServer/branches/users/cdaboo/store-scheduling/txdav/common/inotifications.py 2013-04-27 02:33:05 UTC (rev 11108)
@@ -35,7 +35,7 @@
A notification collection belongs to a specific principal.
"""
- def name():
+ def name(): #@NoSelf
"""
Identify this notification collection.
@@ -43,7 +43,7 @@
@rtype: C{str}
"""
- def notificationObjects():
+ def notificationObjects(): #@NoSelf
"""
Retrieve the notification objects contained in this notification
collection with the given C{componentType}.
@@ -52,7 +52,7 @@
@return: an iterable of L{INotificationObject}s.
"""
- def notificationObjectWithName(name):
+ def notificationObjectWithName(name): #@NoSelf
"""
Retrieve the notification object with the given C{name} contained
in this notification collection.
@@ -62,7 +62,7 @@
object exists.
"""
- def notificationObjectWithUID(uid):
+ def notificationObjectWithUID(uid): #@NoSelf
"""
Retrieve the notification object with the given C{uid} contained
in this notification collection.
@@ -72,7 +72,7 @@
object exists.
"""
- def writeNotificationObject(uid, xmltype, xmldata):
+ def writeNotificationObject(uid, xmltype, xmldata): #@NoSelf
"""
Write a notification with the given C{uid} in this notification
collection from the given C{xmldata} with given C{xmltype}. If a
@@ -86,7 +86,7 @@
@param xmltype: the node within the notification payload, emptied of
its children, to indicate the type of notification and fill out the
C{CS:notificationtype} property.
-
+
@type xmltype: an instance of
L{txdav.xml.base.WebDAVElement},
most likely a subclass like L{twistedcaldav.customxml.InviteReply},
@@ -97,7 +97,7 @@
@type xmldata: C{str}
"""
- def removeNotificationObjectWithName(name):
+ def removeNotificationObjectWithName(name): #@NoSelf
"""
Remove the notification object with the given C{name} from this
notification collection. If C{deleteOnly} is C{True} then do not
@@ -109,7 +109,7 @@
object exists.
"""
- def removeNotificationObjectWithUID(uid):
+ def removeNotificationObjectWithUID(uid): #@NoSelf
"""
Remove the notification object with the given C{uid} from this
notification collection.
@@ -119,14 +119,14 @@
not exist.
"""
- def syncToken():
+ def syncToken(): #@NoSelf
"""
Retrieve the current sync token for this notification.
@return: a string containing a sync token.
"""
- def properties():
+ def properties(): #@NoSelf
"""
Retrieve the property store for this notification.
@@ -134,6 +134,7 @@
"""
+
class INotificationObject(IDataStoreObject):
"""
Notification object
@@ -141,7 +142,7 @@
An notification object describes an XML notification.
"""
- def setData(uid, xmltype, xmldata, inserting=False):
+ def setData(uid, xmltype, xmldata, inserting=False): #@NoSelf
"""
Rewrite this notification object to match the given C{xmltype} and
C{xmldata}. C{xmldata} must have the same UID as this notification object.
@@ -153,26 +154,24 @@
an notification object.
"""
- def xmldata():
+ def xmldata(): #@NoSelf
"""
Retrieve the notification data for this notification object.
@return: a string.
"""
- def uid():
+ def uid(): #@NoSelf
"""
Retrieve the UID for this notification object.
@return: a string containing a UID.
"""
-
- def notificationCollection():
+ def notificationCollection(): #@NoSelf
"""
@return: the parent L{INotificationCollection} which this
L{INotificationObject} was retrieved from.
@rtype: L{INotificationCollection}
"""
-
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20130426/aa88a9df/attachment-0001.html>
More information about the calendarserver-changes
mailing list