OwlCyberSecurity - MANAGER

Edit File: v19.sql

-- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-

----
-- Copyright (c) 2010-2017 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.
----

-----------------
-- Resource ID --
-----------------

create sequence RESOURCE_ID_SEQ;

-------------------------
-- Cluster Bookkeeping --
-------------------------

-- Information about a process connected to this database.

-- Note that this must match the node info schema in twext.enterprise.queue.
create table NODE_INFO (
  HOSTNAME  varchar(255) not null,
  PID       integer      not null,
  PORT      integer      not null,
  TIME      timestamp    not null default timezone('UTC', CURRENT_TIMESTAMP),

primary key (HOSTNAME, PORT)
);

-- Unique named locks.  This table should always be empty, but rows are
-- temporarily created in order to prevent undesirable concurrency.
create table NAMED_LOCK (
    LOCK_NAME varchar(255) primary key
);

-------------------
-- Calendar Home --
-------------------

create table CALENDAR_HOME (
  RESOURCE_ID      integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  OWNER_UID        varchar(255) not null unique,                                 -- implicit index
  DATAVERSION      integer      default 0 not null
);

--------------
-- Calendar --
--------------

create table CALENDAR (
  RESOURCE_ID integer   primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
);

----------------------------
-- Calendar Home Metadata --
----------------------------

create table CALENDAR_HOME_METADATA (
  RESOURCE_ID              integer     primary key references CALENDAR_HOME on delete cascade, -- implicit index
  QUOTA_USED_BYTES         integer     default 0 not null,
  DEFAULT_EVENTS           integer     default null references CALENDAR on delete set null,
  DEFAULT_TASKS            integer     default null references CALENDAR on delete set null,
  ALARM_VEVENT_TIMED       text        default null,
  ALARM_VEVENT_ALLDAY      text        default null,
  ALARM_VTODO_TIMED        text        default null,
  ALARM_VTODO_ALLDAY       text        default null,
  CREATED                  timestamp   default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                 timestamp   default timezone('UTC', CURRENT_TIMESTAMP)
);

-----------------------
-- Calendar Metadata --
-----------------------

create table CALENDAR_METADATA (
  RESOURCE_ID           integer      primary key references CALENDAR on delete cascade, -- implicit index
  SUPPORTED_COMPONENTS  varchar(255) default null,
  CREATED               timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED              timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

---------------------------
-- Sharing Notifications --
---------------------------

create table NOTIFICATION_HOME (
  RESOURCE_ID integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  OWNER_UID   varchar(255) not null unique                                 -- implicit index
);

create table NOTIFICATION (
  RESOURCE_ID                   integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  NOTIFICATION_HOME_RESOURCE_ID integer      not null references NOTIFICATION_HOME,
  NOTIFICATION_UID              varchar(255) not null,
  XML_TYPE                      varchar(255) not null,
  XML_DATA                      text         not null,
  MD5                           char(32)     not null,
  CREATED                       timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                      timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

unique (NOTIFICATION_UID, NOTIFICATION_HOME_RESOURCE_ID) -- implicit index
);

create index NOTIFICATION_NOTIFICATION_HOME_RESOURCE_ID on
  NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);

-------------------
-- Calendar Bind --
-------------------

-- Joins CALENDAR_HOME and CALENDAR

create table CALENDAR_BIND (
  CALENDAR_HOME_RESOURCE_ID integer      not null references CALENDAR_HOME,
  CALENDAR_RESOURCE_ID      integer      not null references CALENDAR on delete cascade,
  CALENDAR_RESOURCE_NAME    varchar(255) not null,
  BIND_MODE                 integer      not null, -- enum CALENDAR_BIND_MODE
  BIND_STATUS               integer      not null, -- enum CALENDAR_BIND_STATUS
  BIND_REVISION				integer      default 0 not null,
  MESSAGE                   text,
  TRANSP                    integer      default 0 not null, -- enum CALENDAR_TRANSP
  ALARM_VEVENT_TIMED        text         default null,
  ALARM_VEVENT_ALLDAY       text         default null,
  ALARM_VTODO_TIMED         text         default null,
  ALARM_VTODO_ALLDAY        text         default null,

primary key (CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID), -- implicit index
  unique (CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_NAME)     -- implicit index
);

create index CALENDAR_BIND_RESOURCE_ID on CALENDAR_BIND(CALENDAR_RESOURCE_ID);

-- Enumeration of calendar bind modes

create table CALENDAR_BIND_MODE (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_BIND_MODE values (0, 'own'  );
insert into CALENDAR_BIND_MODE values (1, 'read' );
insert into CALENDAR_BIND_MODE values (2, 'write');
insert into CALENDAR_BIND_MODE values (3, 'direct');

-- Enumeration of statuses

create table CALENDAR_BIND_STATUS (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_BIND_STATUS values (0, 'invited' );
insert into CALENDAR_BIND_STATUS values (1, 'accepted');
insert into CALENDAR_BIND_STATUS values (2, 'declined');
insert into CALENDAR_BIND_STATUS values (3, 'invalid');

-- Enumeration of transparency

create table CALENDAR_TRANSP (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_TRANSP values (0, 'opaque' );
insert into CALENDAR_TRANSP values (1, 'transparent');

---------------------
-- Calendar Object --
---------------------

create table CALENDAR_OBJECT (
  RESOURCE_ID          integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  CALENDAR_RESOURCE_ID integer      not null references CALENDAR on delete cascade,
  RESOURCE_NAME        varchar(255) not null,
  ICALENDAR_TEXT       text         not null,
  ICALENDAR_UID        varchar(255) not null,
  ICALENDAR_TYPE       varchar(255) not null,
  ATTACHMENTS_MODE     integer      default 0 not null, -- enum CALENDAR_OBJECT_ATTACHMENTS_MODE
  DROPBOX_ID           varchar(255),
  ORGANIZER            varchar(255),
  RECURRANCE_MIN       date,        -- minimum date that recurrences have been expanded to.
  RECURRANCE_MAX       date,        -- maximum date that recurrences have been expanded to.
  ACCESS               integer      default 0 not null,
  SCHEDULE_OBJECT      boolean      default false,
  SCHEDULE_TAG         varchar(36)  default null,
  SCHEDULE_ETAGS       text         default null,
  PRIVATE_COMMENTS     boolean      default false not null,
  MD5                  char(32)     not null,
  CREATED              timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED             timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

unique (CALENDAR_RESOURCE_ID, RESOURCE_NAME) -- implicit index

-- since the 'inbox' is a 'calendar resource' for the purpose of storing
  -- calendar objects, this constraint has to be selectively enforced by the
  -- application layer.

-- unique (CALENDAR_RESOURCE_ID, ICALENDAR_UID)
);

create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_AND_ICALENDAR_UID on
  CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, ICALENDAR_UID);

create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_RECURRANCE_MAX on
  CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, RECURRANCE_MAX);

create index CALENDAR_OBJECT_ICALENDAR_UID on
  CALENDAR_OBJECT(ICALENDAR_UID);

create index CALENDAR_OBJECT_DROPBOX_ID on
  CALENDAR_OBJECT(DROPBOX_ID);

-- Enumeration of attachment modes

create table CALENDAR_OBJECT_ATTACHMENTS_MODE (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (0, 'none' );
insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (1, 'read' );
insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (2, 'write');

-- Enumeration of calendar access types

create table CALENDAR_ACCESS_TYPE (
  ID          integer     primary key,
  DESCRIPTION varchar(32) not null unique
);

insert into CALENDAR_ACCESS_TYPE values (0, ''             );
insert into CALENDAR_ACCESS_TYPE values (1, 'public'       );
insert into CALENDAR_ACCESS_TYPE values (2, 'private'      );
insert into CALENDAR_ACCESS_TYPE values (3, 'confidential' );
insert into CALENDAR_ACCESS_TYPE values (4, 'restricted'   );

-----------------
-- Instance ID --
-----------------

create sequence INSTANCE_ID_SEQ;

----------------
-- Time Range --
----------------

create table TIME_RANGE (
  INSTANCE_ID                 integer        primary key default nextval('INSTANCE_ID_SEQ'), -- implicit index
  CALENDAR_RESOURCE_ID        integer        not null references CALENDAR on delete cascade,
  CALENDAR_OBJECT_RESOURCE_ID integer        not null references CALENDAR_OBJECT on delete cascade,
  FLOATING                    boolean        not null,
  START_DATE                  timestamp      not null,
  END_DATE                    timestamp      not null,
  FBTYPE                      integer        not null,
  TRANSPARENT                 boolean        not null
);

create index TIME_RANGE_CALENDAR_RESOURCE_ID on
  TIME_RANGE(CALENDAR_RESOURCE_ID);
create index TIME_RANGE_CALENDAR_OBJECT_RESOURCE_ID on
  TIME_RANGE(CALENDAR_OBJECT_RESOURCE_ID);

-- Enumeration of free/busy types

create table FREE_BUSY_TYPE (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into FREE_BUSY_TYPE values (0, 'unknown'         );
insert into FREE_BUSY_TYPE values (1, 'free'            );
insert into FREE_BUSY_TYPE values (2, 'busy'            );
insert into FREE_BUSY_TYPE values (3, 'busy-unavailable');
insert into FREE_BUSY_TYPE values (4, 'busy-tentative'  );

------------------
-- Transparency --
------------------

create table TRANSPARENCY (
  TIME_RANGE_INSTANCE_ID      integer      not null references TIME_RANGE on delete cascade,
  USER_ID                     varchar(255) not null,
  TRANSPARENT                 boolean      not null
);

create index TRANSPARENCY_TIME_RANGE_INSTANCE_ID on
  TRANSPARENCY(TIME_RANGE_INSTANCE_ID);

----------------
-- Attachment --
----------------

create sequence ATTACHMENT_ID_SEQ;

create table ATTACHMENT (
  ATTACHMENT_ID               integer           primary key default nextval('ATTACHMENT_ID_SEQ'), -- implicit index
  CALENDAR_HOME_RESOURCE_ID   integer           not null references CALENDAR_HOME,
  DROPBOX_ID                  varchar(255),
  CONTENT_TYPE                varchar(255)      not null,
  SIZE                        integer           not null,
  MD5                         char(32)          not null,
  CREATED                     timestamp default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                    timestamp default timezone('UTC', CURRENT_TIMESTAMP),
  PATH                        varchar(1024)     not null
);

create index ATTACHMENT_CALENDAR_HOME_RESOURCE_ID on
  ATTACHMENT(CALENDAR_HOME_RESOURCE_ID);

-- Many-to-many relationship between attachments and calendar objects
create table ATTACHMENT_CALENDAR_OBJECT (
  ATTACHMENT_ID                  integer      not null references ATTACHMENT on delete cascade,
  MANAGED_ID                     varchar(255) not null,
  CALENDAR_OBJECT_RESOURCE_ID    integer      not null references CALENDAR_OBJECT on delete cascade,

primary key (ATTACHMENT_ID, CALENDAR_OBJECT_RESOURCE_ID), -- implicit index
  unique (MANAGED_ID, CALENDAR_OBJECT_RESOURCE_ID) --implicit index
);

-----------------------
-- Resource Property --
-----------------------

create table RESOURCE_PROPERTY (
  RESOURCE_ID integer      not null, -- foreign key: *.RESOURCE_ID
  NAME        varchar(255) not null,
  VALUE       text         not null, -- FIXME: xml?
  VIEWER_UID  varchar(255),

primary key (RESOURCE_ID, NAME, VIEWER_UID) -- implicit index
);

----------------------
-- AddressBook Home --
----------------------

create table ADDRESSBOOK_HOME (
  RESOURCE_ID      integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  OWNER_UID        varchar(255) not null unique,                                -- implicit index
  DATAVERSION      integer      default 0 not null
);

-------------------------------
-- AddressBook Home Metadata --
-------------------------------

create table ADDRESSBOOK_HOME_METADATA (
  RESOURCE_ID      integer      primary key references ADDRESSBOOK_HOME on delete cascade, -- implicit index
  QUOTA_USED_BYTES integer      default 0 not null,
  CREATED          timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED         timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

-----------------
-- AddressBook --
-----------------

create table ADDRESSBOOK (
  RESOURCE_ID integer   primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
);

--------------------------
-- AddressBook Metadata --
--------------------------

create table ADDRESSBOOK_METADATA (
  RESOURCE_ID integer   primary key references ADDRESSBOOK on delete cascade, -- implicit index
  CREATED     timestamp default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED    timestamp default timezone('UTC', CURRENT_TIMESTAMP)
);

----------------------
-- AddressBook Bind --
----------------------

-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK

create table ADDRESSBOOK_BIND (
  ADDRESSBOOK_HOME_RESOURCE_ID integer      not null references ADDRESSBOOK_HOME,
  ADDRESSBOOK_RESOURCE_ID      integer      not null references ADDRESSBOOK on delete cascade,
  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
  BIND_REVISION				   integer      default 0 not null,
  MESSAGE                      text,                  -- FIXME: xml?

primary key (ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_ID), -- implicit index
  unique (ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME)     -- implicit index
);

create index ADDRESSBOOK_BIND_RESOURCE_ID on
  ADDRESSBOOK_BIND(ADDRESSBOOK_RESOURCE_ID);

create table ADDRESSBOOK_OBJECT (
  RESOURCE_ID             integer      primary key default nextval('RESOURCE_ID_SEQ'),    -- implicit index
  ADDRESSBOOK_RESOURCE_ID integer      not null references ADDRESSBOOK on delete cascade,
  RESOURCE_NAME           varchar(255) not null,
  VCARD_TEXT              text         not null,
  VCARD_UID               varchar(255) not null,
  MD5                     char(32)     not null,
  CREATED                 timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

unique (ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME), -- implicit index
  unique (ADDRESSBOOK_RESOURCE_ID, VCARD_UID)      -- implicit index
);

---------------
-- Revisions --
---------------

create sequence REVISION_SEQ;

---------------
-- Revisions --
---------------

create table CALENDAR_OBJECT_REVISIONS (
  CALENDAR_HOME_RESOURCE_ID integer      not null references CALENDAR_HOME,
  CALENDAR_RESOURCE_ID      integer      references CALENDAR,
  CALENDAR_NAME             varchar(255) default null,
  RESOURCE_NAME             varchar(255),
  REVISION                  integer      default nextval('REVISION_SEQ') not null,
  DELETED                   boolean      not null
);

create index CALENDAR_OBJECT_REVISIONS_HOME_RESOURCE_ID_CALENDAR_RESOURCE_ID
  on CALENDAR_OBJECT_REVISIONS(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID);

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 --
-------------------------------

create table ADDRESSBOOK_OBJECT_REVISIONS (
  ADDRESSBOOK_HOME_RESOURCE_ID integer      not null references ADDRESSBOOK_HOME,
  ADDRESSBOOK_RESOURCE_ID      integer      references ADDRESSBOOK,
  ADDRESSBOOK_NAME             varchar(255) default null,
  RESOURCE_NAME                varchar(255),
  REVISION                     integer      default nextval('REVISION_SEQ') not null,
  DELETED                      boolean      not null
);

create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID_ADDRESSBOOK_RESOURCE_ID
  on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_ID);

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 --
-----------------------------------

create table NOTIFICATION_OBJECT_REVISIONS (
  NOTIFICATION_HOME_RESOURCE_ID integer      not null references NOTIFICATION_HOME on delete cascade,
  RESOURCE_NAME                 varchar(255),
  REVISION                      integer      default nextval('REVISION_SEQ') not null,
  DELETED                       boolean      not null,

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 --
-------------------------------------------

create table APN_SUBSCRIPTIONS (
  TOKEN                         varchar(255) not null,
  RESOURCE_KEY                  varchar(255) not null,
  MODIFIED                      integer      not null,
  SUBSCRIBER_GUID               varchar(255) not null,
  USER_AGENT                    varchar(255) default null,
  IP_ADDR                       varchar(255) default null,

primary key (TOKEN, RESOURCE_KEY) -- implicit index
);

create index APN_SUBSCRIPTIONS_RESOURCE_KEY
   on APN_SUBSCRIPTIONS(RESOURCE_KEY);

-----------------
-- IMIP Tokens --
-----------------

create table IMIP_TOKENS (
  TOKEN                         varchar(255) not null,
  ORGANIZER                     varchar(255) not null,
  ATTENDEE                      varchar(255) not null,
  ICALUID                       varchar(255) not null,
  ACCESSED                      timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

primary key (ORGANIZER, ATTENDEE, ICALUID) -- implicit index
);

create index IMIP_TOKENS_TOKEN
   on IMIP_TOKENS(TOKEN);

----------------
-- Work Items --
----------------

create sequence WORKITEM_SEQ;

---------------------------
-- IMIP Inivitation Work --
---------------------------

create table IMIP_INVITATION_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null,
  NOT_BEFORE                    timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  FROM_ADDR                     varchar(255) not null,
  TO_ADDR                       varchar(255) not null,
  ICALENDAR_TEXT                text         not null
);

-----------------------
-- IMIP Polling Work --
-----------------------

create table IMIP_POLLING_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null,
  NOT_BEFORE                    timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

---------------------
-- IMIP Reply Work --
---------------------

create table IMIP_REPLY_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null,
  NOT_BEFORE                    timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  ORGANIZER                     varchar(255) not null,
  ATTENDEE                      varchar(255) not null,
  ICALENDAR_TEXT                text         not null
);

------------------------
-- Push Notifications --
------------------------

create table PUSH_NOTIFICATION_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null,
  NOT_BEFORE                    timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  PUSH_ID                       varchar(255) not null
);

-----------------
-- GroupCacher --
-----------------

create table GROUP_CACHER_POLLING_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null,
  NOT_BEFORE                    timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

--------------------
-- Schema Version --
--------------------

create table CALENDARSERVER (
  NAME                          varchar(255) primary key, -- implicit index
  VALUE                         varchar(255)
);

insert into CALENDARSERVER values ('VERSION', '19');
insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '4');
insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '1');