[CalendarServer-changes] [8424] CalendarServer/trunk/txdav/common/datastore/sql_schema

[source_changes at macosforge.org]

Revision: 8424
          http://trac.macosforge.org/projects/calendarserver/changeset/8424
Author:   cdaboo at apple.com
Date:     2011-12-11 17:22:15 -0800 (Sun, 11 Dec 2011)
Log Message:
-----------
Add some indexes on the revisions tables and tweak apn table.

Modified Paths:
--------------
    CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
    CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_6_to_7.sql
    CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_6_to_7.sql

Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql	2011-12-11 22:01:28 UTC (rev 8423)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/current.sql	2011-12-12 01:22:15 UTC (rev 8424)
@@ -272,7 +272,7 @@
   USER_ID                     varchar(255) not null,
   TRANSPARENT                 boolean      not null
 );
-
+  
 create index TRANSPARENCY_TIME_RANGE_INSTANCE_ID on
   TRANSPARENCY(TIME_RANGE_INSTANCE_ID);
 
@@ -409,6 +409,9 @@
 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 --
 -------------------------------
@@ -428,6 +431,9 @@
 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 --
 -----------------------------------
@@ -441,6 +447,8 @@
   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 --
@@ -451,7 +459,8 @@
   RESOURCE_KEY                  varchar(255) not null,
   MODIFIED                      integer not null,
   SUBSCRIBER_GUID               varchar(255) not null,
-  unique(TOKEN, RESOURCE_KEY) -- implicit index
+
+  primary key(TOKEN, RESOURCE_KEY) -- implicit index
 );
 
 create index APN_SUBSCRIPTIONS_RESOURCE_KEY

Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_6_to_7.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_6_to_7.sql	2011-12-11 22:01:28 UTC (rev 8423)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_6_to_7.sql	2011-12-12 01:22:15 UTC (rev 8424)
@@ -42,6 +42,24 @@
 alter table ADDRESSBOOK_HOME_METADATA
  add ("MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC');
 
+-- Add an index
+create index CALENDAR_OBJECT_REVIS_265c8acf
+  on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, REVISION);
+
+-- Add an index
+create index ADDRESSBOOK_OBJECT_RE_cb101e6b
+  on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, REVISION);
+
+-- Add an index
+create index NOTIFICATION_OBJECT_R_036a9cee
+  on NOTIFICATION_OBJECT_REVISIONS(NOTIFICATION_HOME_RESOURCE_ID, REVISION);
+
+-- Change a constraint
+alter table APN_SUBSCRIPTIONS
+ drop unique(TOKEN, RESOURCE_KEY);
+alter table APN_SUBSCRIPTIONS
+ add primary key(TOKEN, RESOURCE_KEY);
+
 -- Now update the version
 update CALENDARSERVER set VALUE = '7' where NAME = 'VERSION';
 

Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_6_to_7.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_6_to_7.sql	2011-12-11 22:01:28 UTC (rev 8423)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_6_to_7.sql	2011-12-12 01:22:15 UTC (rev 8424)
@@ -39,7 +39,24 @@
 alter table ADDRESSBOOK_HOME_METADATA
  add column CREATED  timestamp  default timezone('UTC', CURRENT_TIMESTAMP),
  add column MODIFIED timestamp  default timezone('UTC', CURRENT_TIMESTAMP);
- 
+
+-- Add an index
+create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+  on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, REVISION);
+
+-- Add an index
+create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+  on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, REVISION);
+
+-- Add an index
+create index NOTIFICATION_OBJECT_REVISIONS_RESOURCE_ID_REVISION
+  on NOTIFICATION_OBJECT_REVISIONS(NOTIFICATION_HOME_RESOURCE_ID, REVISION);
+
+-- Change a constraint
+alter table APN_SUBSCRIPTIONS
+ drop constraint APN_SUBSCRIPTIONS_TOKEN_RESOURCE_KEY_KEY,
+ add primary key(TOKEN, RESOURCE_KEY);
+
 -- Now update the version
 update CALENDARSERVER set VALUE = '7' where NAME = 'VERSION';
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20111211/6a330129/attachment.html>