[CalendarServer-changes] [13966] CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common /datastore/sql_schema/manual_upgrades

[source_changes at macosforge.org]

Revision: 13966
          http://trac.calendarserver.org//changeset/13966
Author:   cdaboo at apple.com
Date:     2014-09-15 19:00:47 -0700 (Mon, 15 Sep 2014)
Log Message:
-----------
Additional indexes for performance. Hash partitioning upgrade/downgrade scripts.

Modified Paths:
--------------
    CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_upgrade_2.sql

Added Paths:
-----------
    CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_downgrade_3.sql
    CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_upgrade_3.sql

Added: CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_downgrade_3.sql
===================================================================
--- CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_downgrade_3.sql	                        (rev 0)
+++ CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_downgrade_3.sql	2014-09-16 02:00:47 UTC (rev 13966)
@@ -0,0 +1,54 @@
+----
+-- 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 XX to YY --
+---------------------------------------------------
+
+
+-- Downgrade from hash partitioned indexes
+
+-- CALENDAR_OBJECT Table
+
+-- Disable the pkey and foreign key constraints
+ALTER TABLE CALENDAR_OBJECT DISABLE CONSTRAINT SYS_C004279 CASCADE;
+
+-- Hash partition the primary key index
+ALTER TABLE CALENDAR_OBJECT ENABLE CONSTRAINT SYS_C004279 USING INDEX TABLESPACE DATA_TS;
+
+-- Enable the foreign key constraints
+ALTER TABLE TIME_RANGE ENABLE CONSTRAINT SYS_C004296;
+ALTER TABLE ATTACHMENT_CALENDAR_OBJECT ENABLE CONSTRAINT SYS_C0065636;
+ALTER TABLE CALENDAR_OBJECT_SPLITTER_WORK ENABLE CONSTRAINT SYS_C0089711;
+
+
+-- TIME_RANGE Table
+
+-- Disable the pkey and foreign key constraints
+ALTER TABLE TIME_RANGE DISABLE CONSTRAINT SYS_C004294 CASCADE;
+
+-- Hash partition the primary key index
+ALTER TABLE TIME_RANGE ENABLE CONSTRAINT SYS_C004294 USING INDEX TABLESPACE DATA_TS;
+
+-- Enable the foreign key constraints
+ALTER TABLE TRANSPARENCY ENABLE CONSTRAINT SYS_C004301;
+
+
+-- PUSH_NOTIFICATION_WORK Table
+
+-- Hash partition the primary key index
+ALTER TABLE PUSH_NOTIFICATION_WORK DISABLE CONSTRAINT SYS_C0013546 CASCADE;
+ALTER TABLE PUSH_NOTIFICATION_WORK ENABLE CONSTRAINT SYS_C0013546 USING INDEX TABLESPACE DATA_TS;

Modified: CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_upgrade_2.sql
===================================================================
--- CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_upgrade_2.sql	2014-09-16 01:38:20 UTC (rev 13965)
+++ CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_upgrade_2.sql	2014-09-16 02:00:47 UTC (rev 13966)
@@ -26,3 +26,14 @@
     RECURRANCE_MAX,
     RECURRANCE_MIN
 );
+
+-- New indexes
+
+create index CALENDAR_OBJECT_REVIS_550b1c56 on CALENDAR_OBJECT_REVISIONS (
+    CALENDAR_HOME_RESOURCE_ID,
+    REVISION
+);
+
+create index PUSH_NOTIFICATION_WOR_3a3ee588 on PUSH_NOTIFICATION_WORK (
+    PUSH_ID
+);

Added: CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_upgrade_3.sql
===================================================================
--- CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_upgrade_3.sql	                        (rev 0)
+++ CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_upgrade_3.sql	2014-09-16 02:00:47 UTC (rev 13966)
@@ -0,0 +1,56 @@
+----
+-- 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 XX to YY --
+---------------------------------------------------
+
+
+-- Update to hash partitioned indexes
+
+-- CALENDAR_OBJECT Table
+
+-- Disable the pkey and foreign key constraints
+ALTER TABLE CALENDAR_OBJECT DISABLE CONSTRAINT SYS_C004279 CASCADE;
+
+-- Hash partition the primary key index
+ALTER TABLE CALENDAR_OBJECT ENABLE CONSTRAINT SYS_C004279 USING INDEX TABLESPACE DATA_TS GLOBAL PARTITION BY HASH (RESOURCE_ID) PARTITIONS 32;
+
+-- Enable the foreign key constraints
+ALTER TABLE TIME_RANGE ENABLE CONSTRAINT SYS_C004296;
+ALTER TABLE ATTACHMENT_CALENDAR_OBJECT ENABLE CONSTRAINT SYS_C0065636;
+ALTER TABLE CALENDAR_OBJECT_SPLITTER_WORK ENABLE CONSTRAINT SYS_C0089711;
+
+
+-- TIME_RANGE Table
+
+-- Disable the pkey and foreign key constraints
+ALTER TABLE TIME_RANGE DISABLE CONSTRAINT SYS_C004294 DISABLE CASCADE;
+
+-- Hash partition the primary key index
+ALTER TABLE TIME_RANGE ENABLE CONSTRAINT SYS_C004294 USING INDEX TABLESPACE DATA_TS GLOBAL PARTITION BY HASH (INSTANCE_ID) PARTITIONS 32;
+
+-- Enable the foreign key constraints
+ALTER TABLE TRANSPARENCY ENABLE CONSTRAINT SYS_C004301;
+
+
+-- PUSH_NOTIFICATION_WORK Table
+
+-- Disable the pkey and foreign key constraints
+ALTER TABLE PUSH_NOTIFICATION_WORK DISABLE CONSTRAINT SYS_C0013546 CASCADE;
+
+-- Hash partition the primary key index
+ALTER TABLE PUSH_NOTIFICATION_WORK ENABLE CONSTRAINT SYS_C0013546 USING INDEX TABLESPACE DATA_TS GLOBAL PARTITION BY HASH (WORK_ID) PARTITIONS 32;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20140915/5053c08e/attachment.html>