[CalendarServer-changes] [11124] CalendarServer/branches/users/gaya/sharedgroups-3/txdav/common/ datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_18_to_19.sql

[source_changes at macosforge.org]

Revision: 11124
          http://trac.calendarserver.org//changeset/11124
Author:   gaya at apple.com
Date:     2013-05-02 14:12:27 -0700 (Thu, 02 May 2013)
Log Message:
-----------
checkpoint new table creation

Modified Paths:
--------------
    CalendarServer/branches/users/gaya/sharedgroups-3/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_18_to_19.sql

Modified: CalendarServer/branches/users/gaya/sharedgroups-3/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_18_to_19.sql
===================================================================
--- CalendarServer/branches/users/gaya/sharedgroups-3/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_18_to_19.sql	2013-05-02 20:42:53 UTC (rev 11123)
+++ CalendarServer/branches/users/gaya/sharedgroups-3/txdav/common/datastore/sql_schema/upgrades/postgres-dialect/upgrade_from_18_to_19.sql	2013-05-02 21:12:27 UTC (rev 11124)
@@ -19,6 +19,82 @@
 -- Upgrade database schema from VERSION 18 to 19 --
 ---------------------------------------------------
 
+----------------
+-- New Tables --
+----------------
 
+-----------------------------
+-- AddressBook Object kind --
+-----------------------------
+
+create table ADDRESSBOOK_OBJECT_KIND (
+  ID          integer     primary key,
+  DESCRIPTION varchar(16) not null unique
+);
+
+insert into ADDRESSBOOK_OBJECT_KIND values (0, 'person');
+insert into ADDRESSBOOK_OBJECT_KIND values (1, 'group' );
+insert into ADDRESSBOOK_OBJECT_KIND values (2, 'resource');
+insert into ADDRESSBOOK_OBJECT_KIND values (3, 'location');
+
+
+---------------------------------
+-- Address Book Object Members --
+---------------------------------
+
+create table ABO_MEMBERS (
+    GROUP_ID              integer      not null references ADDRESSBOOK_OBJECT on delete cascade,	-- AddressBook Object's (kind=='group') RESOURCE_ID
+ 	ADDRESSBOOK_ID		  integer      not null references ADDRESSBOOK_HOME on delete cascade,
+    MEMBER_ID             integer      not null references ADDRESSBOOK_OBJECT,						-- member AddressBook Object's RESOURCE_ID
+    primary key (GROUP_ID, MEMBER_ID) -- implicit index
+);
+
+
+------------------------------------------
+-- Address Book Object Foreign Members  --
+------------------------------------------
+
+create table ABO_FOREIGN_MEMBERS (
+    GROUP_ID              integer      not null references ADDRESSBOOK_OBJECT on delete cascade,	-- AddressBook Object's (kind=='group') RESOURCE_ID
+ 	ADDRESSBOOK_ID		  integer      not null references ADDRESSBOOK_HOME on delete cascade,
+    MEMBER_ADDRESS  	  varchar(255) not null, 													-- member AddressBook Object's 'calendar' address
+    primary key (GROUP_ID, MEMBER_ADDRESS) -- implicit index
+);
+
+
+-----------------------------
+-- Group Address Book Bind --
+-----------------------------
+
+-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK_OBJECT (acting as Address Book)
+
+create table GROUP_ADDRESSBOOK_BIND (	
+  ADDRESSBOOK_HOME_RESOURCE_ID 		integer      not null references ADDRESSBOOK_HOME,
+  GROUP_RESOURCE_ID      			integer      not null references ADDRESSBOOK_OBJECT on delete cascade,
+  GROUP_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, GROUP_RESOURCE_ID), -- implicit index
+  unique (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_ADDRESSBOOK_RESOURCE_NAME)     -- implicit index
+);
+
+create index GROUP_ADDRESSBOOK_BIND_RESOURCE_ID on
+  GROUP_ADDRESSBOOK_BIND(GROUP_RESOURCE_ID);
+
+--------------------
+-- Simple Updates --
+--------------------
+
+alter table ADDRESSBOOK_HOME
+ add column PROPERTY_STORE_ID	integer      	default nextval('RESOURCE_ID_SEQ') not null;
+
+--------------------
+-- TODO: Finish --
+--------------------
+
+
+  
 -- Now update the version
 update CALENDARSERVER set VALUE = '19' where NAME = 'VERSION';
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20130502/fa38dc9e/attachment-0001.html>