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

source_changes at macosforge.org source_changes at macosforge.org
Thu Sep 18 13:55:52 PDT 2014 

Revision: 13980
          http://trac.calendarserver.org//changeset/13980
Author:   cdaboo at apple.com
Date:     2014-09-18 13:55:52 -0700 (Thu, 18 Sep 2014)
Log Message:
-----------
Hash partitioning index PL/SQL procedure.

Modified 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

Modified: 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	2014-09-18 15:54:50 UTC (rev 13979)
+++ CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_downgrade_3.sql	2014-09-18 20:55:52 UTC (rev 13980)
@@ -21,34 +21,30 @@
 
 -- 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;
+DECLARE  
+  PROCEDURE hash_undo_partition(PKEY_TABLE_NAME in VARCHAR2) IS
+    PKEY_NAME VARCHAR(255);
+  BEGIN
+    -- Find pkey constraint names
+    select CONSTRAINT_NAME into PKEY_NAME from USER_CONSTRAINTS where table_name = PKEY_TABLE_NAME and CONSTRAINT_TYPE = 'P';
+  
+    -- Disable the pkey and foreign key constraints
+    execute immediate 'ALTER TABLE ' || PKEY_TABLE_NAME || ' DISABLE CONSTRAINT ' || PKEY_NAME || ' CASCADE';
+  
+    -- Hash partition the primary key index
+    execute immediate 'ALTER TABLE ' || PKEY_TABLE_NAME || ' ENABLE CONSTRAINT ' || PKEY_NAME || ' USING INDEX';
+  
+    -- Enable the foreign key constraints
+    FOR item in (
+      SELECT TABLE_NAME, CONSTRAINT_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME = PKEY_NAME
+    )
+    LOOP
+      execute immediate 'ALTER TABLE ' || item.TABLE_NAME || ' ENABLE CONSTRAINT ' || item.CONSTRAINT_NAME;
+    END LOOP;
+  END;
+  
+BEGIN
+  hash_undo_partition('CALENDAR_OBJECT');
+  hash_undo_partition('TIME_RANGE');
+  hash_undo_partition('PUSH_NOTIFICATION_WORK');
+END;

Modified: 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	2014-09-18 15:54:50 UTC (rev 13979)
+++ CalendarServer/branches/release/CalendarServer-5.3-dev/txdav/common/datastore/sql_schema/manual_upgrades/manual_upgrade_3.sql	2014-09-18 20:55:52 UTC (rev 13980)
@@ -18,39 +18,32 @@
 -- 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;
+DECLARE  
+  PROCEDURE hash_partition(PKEY_TABLE_NAME in VARCHAR2) IS
+    PKEY_NAME VARCHAR(255);
+    PKEY_COLUMN VARCHAR(255);
+  BEGIN
+    -- Find pkey constraint name and column name
+    select CONSTRAINT_NAME into PKEY_NAME from USER_CONSTRAINTS where table_name = PKEY_TABLE_NAME and CONSTRAINT_TYPE = 'P';
+    select COLUMN_NAME into PKEY_COLUMN from USER_IND_COLUMNS where index_name = PKEY_NAME;
+  
+    -- Disable the pkey and foreign key constraints
+    execute immediate 'ALTER TABLE ' || PKEY_TABLE_NAME || ' DISABLE CONSTRAINT ' || PKEY_NAME || ' CASCADE';
+  
+    -- Hash partition the primary key index
+    execute immediate 'ALTER TABLE ' || PKEY_TABLE_NAME || ' ENABLE CONSTRAINT ' || PKEY_NAME || ' USING INDEX GLOBAL PARTITION BY HASH (' || PKEY_COLUMN || ') PARTITIONS 32';
+  
+    -- Enable the foreign key constraints that depend on the pkey
+    FOR item in (
+      SELECT TABLE_NAME, CONSTRAINT_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME = PKEY_NAME
+    )
+    LOOP
+      execute immediate 'ALTER TABLE ' || item.TABLE_NAME || ' ENABLE CONSTRAINT ' || item.CONSTRAINT_NAME;
+    END LOOP;
+  END;
+  
+BEGIN
+  hash_partition('CALENDAR_OBJECT');
+  hash_partition('TIME_RANGE');
+  hash_partition('PUSH_NOTIFICATION_WORK');
+END;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20140918/a1d11aff/attachment.html>

More information about the calendarserver-changes mailing list