text: -- -*- 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;
  
  
  -------------------
  -- 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
  );
  
  ----------------------------
  -- 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
  );
  
  --------------
  -- Calendar --
  --------------
  
  create table CALENDAR (
    RESOURCE_ID integer   primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
    CREATED     timestamp default timezone('UTC', CURRENT_TIMESTAMP),
    MODIFIED    timestamp default timezone('UTC', CURRENT_TIMESTAMP)
  );
  
  
  ------------------------
  -- Sharing Invitation --
  ------------------------
  
  create table INVITE (
      INVITE_UID         varchar(255) not null,
      NAME               varchar(255) not null,
      RECIPIENT_ADDRESS  varchar(255) not null,
      HOME_RESOURCE_ID   integer      not null,
      RESOURCE_ID        integer      not null
      
      -- Need primary key on (INVITE_UID, NAME, RECIPIENT_ADDRESS)?
  );
  
  create index INVITE_INVITE_UID on INVITE(INVITE_UID);
  create index INVITE_RESOURCE_ID on INVITE(RESOURCE_ID);
  create index INVITE_HOME_RESOURCE_ID on INVITE(HOME_RESOURCE_ID);
  
  ---------------------------
  -- 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,
    
    -- An invitation which hasn't been accepted yet will not yet have a resource
    -- name, so this field may be null.
    
    CALENDAR_RESOURCE_NAME    varchar(255),
    BIND_MODE                 integer      not null, -- enum CALENDAR_BIND_MODE
    BIND_STATUS               integer      not null, -- enum CALENDAR_BIND_STATUS
    SEEN_BY_OWNER             boolean      not null,
    SEEN_BY_SHAREE            boolean      not null,
    MESSAGE                   text,
  
    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');
  
  
  ---------------------
  -- 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),
    ORGANIZER_OBJECT     integer      references CALENDAR_OBJECT,
    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_ORGANIZER_OBJECT on
    CALENDAR_OBJECT(ORGANIZER_OBJECT);
  
  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 table ATTACHMENT (
    CALENDAR_HOME_RESOURCE_ID   integer       not null references CALENDAR_HOME,
    DROPBOX_ID                  varchar(255)  not null,
    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,
  
    primary key (DROPBOX_ID, PATH) --implicit index
  );
  
  create index ATTACHMENT_CALENDAR_HOME_RESOURCE_ID on
    ATTACHMENT(CALENDAR_HOME_RESOURCE_ID);
  
  -----------------------
  -- 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
  );
  
  --------------------------------
  -- AddressBook Home Meta-data --
  --------------------------------
  
  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
  );
  
  -----------------
  -- AddressBook --
  -----------------
  
  create table ADDRESSBOOK (
    RESOURCE_ID integer   primary key default nextval('RESOURCE_ID_SEQ'), -- 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,
  
    -- An invitation which hasn't been accepted yet will not yet have a resource
    -- name, so this field may be null.
  
    ADDRESSBOOK_RESOURCE_NAME    varchar(255),
    BIND_MODE                    integer      not null, -- enum CALENDAR_BIND_MODE
    BIND_STATUS                  integer      not null, -- enum CALENDAR_BIND_STATUS
    SEEN_BY_OWNER                boolean      not null,
    SEEN_BY_SHAREE               boolean      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
    on CALENDAR_OBJECT_REVISIONS(CALENDAR_HOME_RESOURCE_ID);
  
  create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
    on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, RESOURCE_NAME);
  
  -------------------------------
  -- 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
    on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID);
  
  create index ADDRESSBOOK_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME
    on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_RESOURCE_ID, RESOURCE_NAME);
  
  -----------------------------------
  -- 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
  );
  
  
  --------------------
  -- Schema Version --
  --------------------
  
  create table CALENDARSERVER (
    NAME                          varchar(255) primary key, -- implicit index
    VALUE                         varchar(255)
  );
  
  insert into CALENDARSERVER values ('VERSION', '5');
  
  