[CalendarServer-changes] [13747] CalendarServer/branches/users/gaya/groupsharee2/txdav/common/ datastore/sql_schema
source_changes at macosforge.org
source_changes at macosforge.org
Thu Jul 10 16:30:58 PDT 2014
Revision: 13747
http://trac.calendarserver.org//changeset/13747
Author: gaya at apple.com
Date: 2014-07-10 16:30:57 -0700 (Thu, 10 Jul 2014)
Log Message:
-----------
add indexes for GROUP_SHAREE_RECONCILE_WORK, GROUP_SHAREE, & GROUP_ATTENDEE_RECONCILE_WORK RESOURCE_ID
Modified Paths:
--------------
CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/current-oracle-dialect.sql
CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/current.sql
CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_45_to_46.sql
CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_45_to_46.sql
Modified: CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/current-oracle-dialect.sql
===================================================================
--- CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/current-oracle-dialect.sql 2014-07-10 18:09:23 UTC (rev 13746)
+++ CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/current-oracle-dialect.sql 2014-07-10 23:30:57 UTC (rev 13747)
@@ -117,8 +117,8 @@
insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('direct', 3);
insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('indirect', 4);
insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group', 5);
-insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group,read', 6);
-insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group,write', 7);
+insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group_read', 6);
+insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group_write', 7);
create table CALENDAR_BIND_STATUS (
"ID" integer primary key,
"DESCRIPTION" nvarchar2(16) unique
@@ -424,8 +424,8 @@
create table GROUP_ATTENDEE_RECONCILE_WORK (
"WORK_ID" integer primary key not null,
"JOB_ID" integer not null references JOB,
- "RESOURCE_ID" integer,
- "GROUP_ID" integer
+ "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade,
+ "GROUP_ID" integer not null references GROUPS on delete cascade
);
create table GROUP_ATTENDEE (
@@ -767,6 +767,14 @@
JOB_ID
);
+create index GROUP_ATTENDEE_RECONC_b894ee7a on GROUP_ATTENDEE_RECONCILE_WORK (
+ RESOURCE_ID
+);
+
+create index GROUP_ATTENDEE_RECONC_5eabc549 on GROUP_ATTENDEE_RECONCILE_WORK (
+ GROUP_ID
+);
+
create index GROUP_ATTENDEE_RESOUR_855124dc on GROUP_ATTENDEE (
RESOURCE_ID
);
@@ -775,6 +783,18 @@
JOB_ID
);
+create index GROUP_SHAREE_RECONCIL_4dc60f78 on GROUP_SHAREE_RECONCILE_WORK (
+ CALENDAR_ID
+);
+
+create index GROUP_SHAREE_RECONCIL_1d14c921 on GROUP_SHAREE_RECONCILE_WORK (
+ GROUP_ID
+);
+
+create index GROUP_SHAREE_CALENDAR_a9feee17 on GROUP_SHAREE (
+ CALENDAR_HOME_ID
+);
+
create index GROUP_SHAREE_CALENDAR_28a88850 on GROUP_SHAREE (
CALENDAR_ID
);
Modified: CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/current.sql
===================================================================
--- CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/current.sql 2014-07-10 18:09:23 UTC (rev 13746)
+++ CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/current.sql 2014-07-10 23:30:57 UTC (rev 13747)
@@ -205,8 +205,8 @@
insert into CALENDAR_BIND_MODE values (3, 'direct');
insert into CALENDAR_BIND_MODE values (4, 'indirect');
insert into CALENDAR_BIND_MODE values (5, 'group');
-insert into CALENDAR_BIND_MODE values (6, 'group,read');
-insert into CALENDAR_BIND_MODE values (7, 'group,write');
+insert into CALENDAR_BIND_MODE values (6, 'group_read');
+insert into CALENDAR_BIND_MODE values (7, 'group_write');
-- Enumeration of statuses
@@ -787,12 +787,16 @@
create table GROUP_ATTENDEE_RECONCILE_WORK (
WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
JOB_ID integer not null references JOB,
- RESOURCE_ID integer, -- FIXME add: not null references CALENDAR_OBJECT on delete cascade,
- GROUP_ID integer -- FIXME add: not null references GROUPS on delete cascade
+ RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ GROUP_ID integer not null references GROUPS on delete cascade
);
create index GROUP_ATTENDEE_RECONCILE_WORK_JOB_ID on
GROUP_ATTENDEE_RECONCILE_WORK(JOB_ID);
+create index GROUP_ATTENDEE_RECONCILE_WORK_RESOURCE_ID on
+ GROUP_ATTENDEE_RECONCILE_WORK(RESOURCE_ID);
+create index GROUP_ATTENDEE_RECONCILE_WORK_GROUP_ID on
+ GROUP_ATTENDEE_RECONCILE_WORK(GROUP_ID);
create table GROUP_ATTENDEE (
@@ -816,6 +820,10 @@
create index GROUP_SHAREE_RECONCILE_WORK_JOB_ID on
GROUP_SHAREE_RECONCILE_WORK(JOB_ID);
+create index GROUP_SHAREE_RECONCILE_WORK_CALENDAR_ID on
+ GROUP_SHAREE_RECONCILE_WORK(CALENDAR_ID);
+create index GROUP_SHAREE_RECONCILE_WORK_GROUP_ID on
+ GROUP_SHAREE_RECONCILE_WORK(GROUP_ID);
create table GROUP_SHAREE (
@@ -828,6 +836,8 @@
primary key (GROUP_ID, CALENDAR_HOME_ID, CALENDAR_ID)
);
+create index GROUP_SHAREE_CALENDAR_HOME_ID on
+ GROUP_SHAREE(CALENDAR_HOME_ID);
create index GROUP_SHAREE_CALENDAR_ID on
GROUP_SHAREE(CALENDAR_ID);
Modified: CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_45_to_46.sql
===================================================================
--- CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_45_to_46.sql 2014-07-10 18:09:23 UTC (rev 13746)
+++ CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_45_to_46.sql 2014-07-10 23:30:57 UTC (rev 13747)
@@ -18,9 +18,34 @@
-- Upgrade database schema from VERSION 45 to 46 --
---------------------------------------------------
+
+-- delete data and add contraint to GROUP_ATTENDEE_RECONCILE_WORK
+
+drop table GROUP_ATTENDEE_RECONCILE_WORK;
+
+create table GROUP_ATTENDEE_RECONCILE_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ JOB_ID integer not null references JOB,
+ RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ GROUP_ID integer not null references GROUPS on delete cascade
+);
+
+create index GROUP_ATTENDEE_RECONCILE_WORK_JOB_ID on GROUP_ATTENDEE_RECONCILE_WORK(
+ JOB_ID
+);
+create index GROUP_ATTENDEE_RECONCILE_WORK_RESOURCE_ID on GROUP_ATTENDEE_RECONCILE_WORK(
+ RESOURCE_ID
+);
+create index GROUP_ATTENDEE_RECONCILE_WORK_GROUP_ID on GROUP_ATTENDEE_RECONCILE_WORK(
+ GROUP_ID
+);
+
+
+-- schema for group sharees
+
insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group', 5);
-insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group,read', 6);
-insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group,write', 7);
+insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group_read', 6);
+insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('group_write', 7);
create table GROUP_SHAREE_RECONCILE_WORK (
@@ -33,6 +58,12 @@
create index GROUP_SHAREE_RECONCILE_WORK_JOB_ID on GROUP_SHAREE_RECONCILE_WORK(
JOB_ID
);
+create index GROUP_SHAREE_RECONCILE_WORK_CALENDAR_ID on GROUP_SHAREE_RECONCILE_WORK(
+ CALENDAR_ID
+);
+create index GROUP_SHAREE_RECONCILE_WORK_GROUP_ID on GROUP_SHAREE_RECONCILE_WORK(
+ GROUP_ID
+);
create table GROUP_SHAREE (
@@ -45,9 +76,13 @@
primary key (GROUP_ID, CALENDAR_HOME_ID, CALENDAR_ID) -- implicit index
);
+create index GROUP_SHAREE_CALENDAR_HOME_ID on GROUP_SHAREE(
+ CALENDAR_HOME_ID
+);
create index GROUP_SHAREE_CALENDAR_ID on GROUP_SHAREE(
CALENDAR_ID
);
+
-- update the version
update CALENDARSERVER set VALUE = '46' where NAME = 'VERSION';
Modified: CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_45_to_46.sql
===================================================================
--- CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_45_to_46.sql 2014-07-10 18:09:23 UTC (rev 13746)
+++ CalendarServer/branches/users/gaya/groupsharee2/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_45_to_46.sql 2014-07-10 23:30:57 UTC (rev 13747)
@@ -18,9 +18,31 @@
-- Upgrade database schema from VERSION 45 to 46 --
---------------------------------------------------
+
+-- delete data and add contraint to GROUP_ATTENDEE_RECONCILE_WORK
+
+drop table GROUP_ATTENDEE_RECONCILE_WORK;
+
+create table GROUP_ATTENDEE_RECONCILE_WORK (
+ WORK_ID integer primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
+ JOB_ID integer not null references JOB,
+ RESOURCE_ID integer not null references CALENDAR_OBJECT on delete cascade,
+ GROUP_ID integer not null references GROUPS on delete cascade
+);
+
+create index GROUP_ATTENDEE_RECONCILE_WORK_JOB_ID on
+ GROUP_ATTENDEE_RECONCILE_WORK(JOB_ID);
+create index GROUP_ATTENDEE_RECONCILE_WORK_RESOURCE_ID on
+ GROUP_ATTENDEE_RECONCILE_WORK(RESOURCE_ID);
+create index GROUP_ATTENDEE_RECONCILE_WORK_GROUP_ID on
+ GROUP_ATTENDEE_RECONCILE_WORK(GROUP_ID);
+
+
+-- schema for group sharees
+
insert into CALENDAR_BIND_MODE values (5, 'group');
-insert into CALENDAR_BIND_MODE values (6, 'group,read');
-insert into CALENDAR_BIND_MODE values (7, 'group,write');
+insert into CALENDAR_BIND_MODE values (6, 'group_read');
+insert into CALENDAR_BIND_MODE values (7, 'group_write');
create table GROUP_SHAREE_RECONCILE_WORK (
@@ -32,6 +54,10 @@
create index GROUP_SHAREE_RECONCILE_WORK_JOB_ID on
GROUP_SHAREE_RECONCILE_WORK(JOB_ID);
+create index GROUP_SHAREE_RECONCILE_WORK_CALENDAR_ID on
+ GROUP_SHAREE_RECONCILE_WORK(CALENDAR_ID);
+create index GROUP_SHAREE_RECONCILE_WORK_GROUP_ID on
+ GROUP_SHAREE_RECONCILE_WORK(GROUP_ID);
create table GROUP_SHAREE (
@@ -44,8 +70,11 @@
primary key (GROUP_ID, CALENDAR_HOME_ID, CALENDAR_ID) -- implicit index
);
+create index GROUP_SHAREE_CALENDAR_HOME_ID on
+ GROUP_SHAREE(CALENDAR_HOME_ID);
create index GROUP_SHAREE_CALENDAR_ID on
GROUP_SHAREE(CALENDAR_ID);
+
-- update the version
update CALENDARSERVER set VALUE = '46' where NAME = 'VERSION';
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20140710/b5243fb4/attachment-0001.html>
More information about the calendarserver-changes
mailing list