ProPeler
Home
Console
Upload
information
Create File
Create Folder
About
Tools
:
/
opt
/
cpanel-ccs
/
txdav
/
common
/
datastore
/
sql_schema
/
Filename :
current-oracle-dialect.sql
back
Copy
create sequence RESOURCE_ID_SEQ; create sequence JOB_SEQ; create sequence INSTANCE_ID_SEQ; create sequence ATTACHMENT_ID_SEQ; create sequence REVISION_SEQ; create sequence WORKITEM_SEQ; create table NAMED_LOCK ( "LOCK_NAME" nvarchar2(255) primary key ); create table JOB ( "JOB_ID" integer primary key, "WORK_TYPE" nvarchar2(255), "PRIORITY" integer default 0 not null, "WEIGHT" integer default 0 not null, "NOT_BEFORE" timestamp not null, "IS_ASSIGNED" integer default 0 not null, "ASSIGNED" timestamp default null, "OVERDUE" timestamp default null, "FAILED" integer default 0 not null, "PAUSE" integer default 0 not null ); create table CALENDAR_HOME ( "RESOURCE_ID" integer primary key, "OWNER_UID" nvarchar2(255), "STATUS" integer default 0 not null, "DATAVERSION" integer default 0 not null, unique ("OWNER_UID", "STATUS") ); create table HOME_STATUS ( "ID" integer primary key, "DESCRIPTION" nvarchar2(16) unique ); insert into HOME_STATUS (DESCRIPTION, ID) values ('normal', 0); insert into HOME_STATUS (DESCRIPTION, ID) values ('external', 1); insert into HOME_STATUS (DESCRIPTION, ID) values ('purging', 2); insert into HOME_STATUS (DESCRIPTION, ID) values ('migrating', 3); insert into HOME_STATUS (DESCRIPTION, ID) values ('disabled', 4); create table CALENDAR ( "RESOURCE_ID" integer primary key ); create table CALENDAR_HOME_METADATA ( "RESOURCE_ID" integer primary key references CALENDAR_HOME on delete cascade, "QUOTA_USED_BYTES" integer default 0 not null, "TRASH" integer default null references CALENDAR on delete set null, "DEFAULT_EVENTS" integer default null references CALENDAR on delete set null, "DEFAULT_TASKS" integer default null references CALENDAR on delete set null, "DEFAULT_POLLS" integer default null references CALENDAR on delete set null, "ALARM_VEVENT_TIMED" nclob default null, "ALARM_VEVENT_ALLDAY" nclob default null, "ALARM_VTODO_TIMED" nclob default null, "ALARM_VTODO_ALLDAY" nclob default null, "AVAILABILITY" nclob default null, "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC' ); create table CALENDAR_METADATA ( "RESOURCE_ID" integer primary key references CALENDAR on delete cascade, "SUPPORTED_COMPONENTS" nvarchar2(255) default null, "CHILD_TYPE" integer default 0 not null, "TRASHED" timestamp default null, "IS_IN_TRASH" integer default 0 not null, "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC' ); create table CHILD_TYPE ( "ID" integer primary key, "DESCRIPTION" nvarchar2(16) unique ); insert into CHILD_TYPE (DESCRIPTION, ID) values ('normal', 0); insert into CHILD_TYPE (DESCRIPTION, ID) values ('inbox', 1); insert into CHILD_TYPE (DESCRIPTION, ID) values ('trash', 2); create table CALENDAR_MIGRATION ( "CALENDAR_HOME_RESOURCE_ID" integer references CALENDAR_HOME on delete cascade, "REMOTE_RESOURCE_ID" integer not null, "LOCAL_RESOURCE_ID" integer references CALENDAR on delete cascade, "LAST_SYNC_TOKEN" nvarchar2(255), primary key ("CALENDAR_HOME_RESOURCE_ID", "REMOTE_RESOURCE_ID") ); create table NOTIFICATION_HOME ( "RESOURCE_ID" integer primary key, "OWNER_UID" nvarchar2(255), "STATUS" integer default 0 not null, "DATAVERSION" integer default 0 not null, unique ("OWNER_UID", "STATUS") ); create table NOTIFICATION ( "RESOURCE_ID" integer primary key, "NOTIFICATION_HOME_RESOURCE_ID" integer not null references NOTIFICATION_HOME, "NOTIFICATION_UID" nvarchar2(255), "NOTIFICATION_TYPE" nvarchar2(255), "NOTIFICATION_DATA" nclob, "MD5" nchar(32), "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', unique ("NOTIFICATION_UID", "NOTIFICATION_HOME_RESOURCE_ID") ); 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" nvarchar2(255), "BIND_MODE" integer not null, "BIND_STATUS" integer not null, "BIND_REVISION" integer default 0 not null, "BIND_UID" nvarchar2(36) default null, "MESSAGE" nclob, "TRANSP" integer default 0 not null, "ALARM_VEVENT_TIMED" nclob default null, "ALARM_VEVENT_ALLDAY" nclob default null, "ALARM_VTODO_TIMED" nclob default null, "ALARM_VTODO_ALLDAY" nclob default null, "TIMEZONE" nclob default null, primary key ("CALENDAR_HOME_RESOURCE_ID", "CALENDAR_RESOURCE_ID"), unique ("CALENDAR_HOME_RESOURCE_ID", "CALENDAR_RESOURCE_NAME") ); create table CALENDAR_BIND_MODE ( "ID" integer primary key, "DESCRIPTION" nvarchar2(16) unique ); insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('own', 0); insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('read', 1); insert into CALENDAR_BIND_MODE (DESCRIPTION, ID) values ('write', 2); 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); create table CALENDAR_BIND_STATUS ( "ID" integer primary key, "DESCRIPTION" nvarchar2(16) unique ); insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('invited', 0); insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('accepted', 1); insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('declined', 2); insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('invalid', 3); insert into CALENDAR_BIND_STATUS (DESCRIPTION, ID) values ('deleted', 4); create table CALENDAR_TRANSP ( "ID" integer primary key, "DESCRIPTION" nvarchar2(16) unique ); insert into CALENDAR_TRANSP (DESCRIPTION, ID) values ('opaque', 0); insert into CALENDAR_TRANSP (DESCRIPTION, ID) values ('transparent', 1); create table CALENDAR_OBJECT ( "RESOURCE_ID" integer primary key, "CALENDAR_RESOURCE_ID" integer not null references CALENDAR on delete cascade, "RESOURCE_NAME" nvarchar2(255), "ICALENDAR_TEXT" nclob, "ICALENDAR_UID" nvarchar2(255), "ICALENDAR_TYPE" nvarchar2(255), "ATTACHMENTS_MODE" integer default 0 not null, "DROPBOX_ID" nvarchar2(255), "ORGANIZER" nvarchar2(255), "RECURRANCE_MIN" date, "RECURRANCE_MAX" date, "ACCESS" integer default 0 not null, "SCHEDULE_OBJECT" integer default 0, "SCHEDULE_TAG" nvarchar2(36) default null, "SCHEDULE_ETAGS" nclob default null, "PRIVATE_COMMENTS" integer default 0 not null, "MD5" nchar(32), "TRASHED" timestamp default null, "ORIGINAL_COLLECTION" integer default null, "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "DATAVERSION" integer default 0 not null, unique ("CALENDAR_RESOURCE_ID", "RESOURCE_NAME") ); create table CALENDAR_OBJ_ATTACHMENTS_MODE ( "ID" integer primary key, "DESCRIPTION" nvarchar2(16) unique ); insert into CALENDAR_OBJ_ATTACHMENTS_MODE (DESCRIPTION, ID) values ('none', 0); insert into CALENDAR_OBJ_ATTACHMENTS_MODE (DESCRIPTION, ID) values ('read', 1); insert into CALENDAR_OBJ_ATTACHMENTS_MODE (DESCRIPTION, ID) values ('write', 2); create table CALENDAR_ACCESS_TYPE ( "ID" integer primary key, "DESCRIPTION" nvarchar2(32) unique ); insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('', 0); insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('public', 1); insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('private', 2); insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('confidential', 3); insert into CALENDAR_ACCESS_TYPE (DESCRIPTION, ID) values ('restricted', 4); create table TIME_RANGE ( "INSTANCE_ID" integer primary key, "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" integer not null, "START_DATE" timestamp not null, "END_DATE" timestamp not null, "FBTYPE" integer not null, "TRANSPARENT" integer not null ); create table FREE_BUSY_TYPE ( "ID" integer primary key, "DESCRIPTION" nvarchar2(16) unique ); insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('unknown', 0); insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('free', 1); insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('busy', 2); insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('busy-unavailable', 3); insert into FREE_BUSY_TYPE (DESCRIPTION, ID) values ('busy-tentative', 4); create table PERUSER ( "TIME_RANGE_INSTANCE_ID" integer not null references TIME_RANGE on delete cascade, "USER_ID" nvarchar2(255), "TRANSPARENT" integer not null, "ADJUSTED_START_DATE" timestamp default null, "ADJUSTED_END_DATE" timestamp default null, primary key ("TIME_RANGE_INSTANCE_ID", "USER_ID") ); create table CALENDAR_OBJECT_MIGRATION ( "CALENDAR_HOME_RESOURCE_ID" integer references CALENDAR_HOME on delete cascade, "REMOTE_RESOURCE_ID" integer not null, "LOCAL_RESOURCE_ID" integer references CALENDAR_OBJECT on delete cascade, primary key ("CALENDAR_HOME_RESOURCE_ID", "REMOTE_RESOURCE_ID") ); create table ATTACHMENT ( "ATTACHMENT_ID" integer primary key, "CALENDAR_HOME_RESOURCE_ID" integer not null references CALENDAR_HOME, "DROPBOX_ID" nvarchar2(255), "CONTENT_TYPE" nvarchar2(255), "SIZE" integer not null, "MD5" nchar(32), "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "PATH" nvarchar2(1024) ); create table ATTACHMENT_CALENDAR_OBJECT ( "ATTACHMENT_ID" integer not null references ATTACHMENT on delete cascade, "MANAGED_ID" nvarchar2(255), "CALENDAR_OBJECT_RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade, primary key ("ATTACHMENT_ID", "CALENDAR_OBJECT_RESOURCE_ID"), unique ("MANAGED_ID", "CALENDAR_OBJECT_RESOURCE_ID") ); create table ATTACHMENT_MIGRATION ( "CALENDAR_HOME_RESOURCE_ID" integer references CALENDAR_HOME on delete cascade, "REMOTE_RESOURCE_ID" integer not null, "LOCAL_RESOURCE_ID" integer references ATTACHMENT on delete cascade, primary key ("CALENDAR_HOME_RESOURCE_ID", "REMOTE_RESOURCE_ID") ); create table RESOURCE_PROPERTY ( "RESOURCE_ID" integer not null, "NAME" nvarchar2(255), "VALUE" nclob, "VIEWER_UID" nvarchar2(255), primary key ("RESOURCE_ID", "NAME", "VIEWER_UID") ); create table ADDRESSBOOK_HOME ( "RESOURCE_ID" integer primary key, "ADDRESSBOOK_PROPERTY_STORE_ID" integer not null, "OWNER_UID" nvarchar2(255), "STATUS" integer default 0 not null, "DATAVERSION" integer default 0 not null, unique ("OWNER_UID", "STATUS") ); create table ADDRESSBOOK_HOME_METADATA ( "RESOURCE_ID" integer primary key references ADDRESSBOOK_HOME on delete cascade, "QUOTA_USED_BYTES" integer default 0 not null, "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC' ); create table SHARED_ADDRESSBOOK_BIND ( "ADDRESSBOOK_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME, "OWNER_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME on delete cascade, "ADDRESSBOOK_RESOURCE_NAME" nvarchar2(255), "BIND_MODE" integer not null, "BIND_STATUS" integer not null, "BIND_REVISION" integer default 0 not null, "BIND_UID" nvarchar2(36) default null, "MESSAGE" nclob, primary key ("ADDRESSBOOK_HOME_RESOURCE_ID", "OWNER_HOME_RESOURCE_ID"), unique ("ADDRESSBOOK_HOME_RESOURCE_ID", "ADDRESSBOOK_RESOURCE_NAME") ); create table ADDRESSBOOK_OBJECT ( "RESOURCE_ID" integer primary key, "ADDRESSBOOK_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME on delete cascade, "RESOURCE_NAME" nvarchar2(255), "VCARD_TEXT" nclob, "VCARD_UID" nvarchar2(255), "KIND" integer not null, "MD5" nchar(32), "TRASHED" timestamp default null, "IS_IN_TRASH" integer default 0 not null, "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "DATAVERSION" integer default 0 not null, unique ("ADDRESSBOOK_HOME_RESOURCE_ID", "RESOURCE_NAME"), unique ("ADDRESSBOOK_HOME_RESOURCE_ID", "VCARD_UID") ); create table ADDRESSBOOK_OBJECT_KIND ( "ID" integer primary key, "DESCRIPTION" nvarchar2(16) unique ); insert into ADDRESSBOOK_OBJECT_KIND (DESCRIPTION, ID) values ('person', 0); insert into ADDRESSBOOK_OBJECT_KIND (DESCRIPTION, ID) values ('group', 1); insert into ADDRESSBOOK_OBJECT_KIND (DESCRIPTION, ID) values ('resource', 2); insert into ADDRESSBOOK_OBJECT_KIND (DESCRIPTION, ID) values ('location', 3); create table ABO_MEMBERS ( "GROUP_ID" integer not null, "ADDRESSBOOK_ID" integer not null references ADDRESSBOOK_HOME on delete cascade, "MEMBER_ID" integer not null, "REVISION" integer not null, "REMOVED" integer default 0 not null, "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', primary key ("GROUP_ID", "MEMBER_ID", "REVISION") ); create table ABO_FOREIGN_MEMBERS ( "GROUP_ID" integer not null references ADDRESSBOOK_OBJECT on delete cascade, "ADDRESSBOOK_ID" integer not null references ADDRESSBOOK_HOME on delete cascade, "MEMBER_ADDRESS" nvarchar2(255), primary key ("GROUP_ID", "MEMBER_ADDRESS") ); create table SHARED_GROUP_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_NAME" nvarchar2(255), "BIND_MODE" integer not null, "BIND_STATUS" integer not null, "BIND_REVISION" integer default 0 not null, "BIND_UID" nvarchar2(36) default null, "MESSAGE" nclob, primary key ("ADDRESSBOOK_HOME_RESOURCE_ID", "GROUP_RESOURCE_ID"), unique ("ADDRESSBOOK_HOME_RESOURCE_ID", "GROUP_ADDRESSBOOK_NAME") ); create table CALENDAR_OBJECT_REVISIONS ( "CALENDAR_HOME_RESOURCE_ID" integer not null references CALENDAR_HOME, "CALENDAR_RESOURCE_ID" integer references CALENDAR, "CALENDAR_NAME" nvarchar2(255) default null, "RESOURCE_NAME" nvarchar2(255), "REVISION" integer not null, "DELETED" integer not null, "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC' not null, unique ("CALENDAR_HOME_RESOURCE_ID", "CALENDAR_RESOURCE_ID", "CALENDAR_NAME", "RESOURCE_NAME") ); create table ADDRESSBOOK_OBJECT_REVISIONS ( "ADDRESSBOOK_HOME_RESOURCE_ID" integer not null references ADDRESSBOOK_HOME, "OWNER_HOME_RESOURCE_ID" integer references ADDRESSBOOK_HOME, "ADDRESSBOOK_NAME" nvarchar2(255) default null, "OBJECT_RESOURCE_ID" integer default 0, "RESOURCE_NAME" nvarchar2(255), "REVISION" integer not null, "DELETED" integer not null, "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC' not null, unique ("ADDRESSBOOK_HOME_RESOURCE_ID", "OWNER_HOME_RESOURCE_ID", "ADDRESSBOOK_NAME", "RESOURCE_NAME") ); create table NOTIFICATION_OBJECT_REVISIONS ( "NOTIFICATION_HOME_RESOURCE_ID" integer not null references NOTIFICATION_HOME on delete cascade, "RESOURCE_NAME" nvarchar2(255), "REVISION" integer not null, "DELETED" integer not null, "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC' not null, unique ("NOTIFICATION_HOME_RESOURCE_ID", "RESOURCE_NAME") ); create table APN_SUBSCRIPTIONS ( "TOKEN" nvarchar2(255), "RESOURCE_KEY" nvarchar2(255), "MODIFIED" integer not null, "SUBSCRIBER_GUID" nvarchar2(255), "USER_AGENT" nvarchar2(255) default null, "IP_ADDR" nvarchar2(255) default null, primary key ("TOKEN", "RESOURCE_KEY") ); create table IMIP_TOKENS ( "TOKEN" nvarchar2(255), "ORGANIZER" nvarchar2(255), "ATTENDEE" nvarchar2(255), "ICALUID" nvarchar2(255), "ACCESSED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', primary key ("ORGANIZER", "ATTENDEE", "ICALUID") ); create table TEST_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "DELAY" integer ); create table APN_PURGING_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB ); create table IMIP_INVITATION_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "FROM_ADDR" nvarchar2(255), "TO_ADDR" nvarchar2(255), "ICALENDAR_TEXT" nclob ); create table IMIP_POLLING_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB ); create table IMIP_REPLY_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "ORGANIZER" nvarchar2(255), "ATTENDEE" nvarchar2(255), "ICALENDAR_TEXT" nclob ); create table PUSH_NOTIFICATION_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "PUSH_ID" nvarchar2(255), "PUSH_PRIORITY" integer not null ); create table GROUP_CACHER_POLLING_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB ); create table GROUP_REFRESH_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "GROUP_UID" nvarchar2(255) ); create table GROUP_DELEGATE_CHANGES_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "DELEGATOR_UID" nvarchar2(255), "READ_DELEGATE_UID" nvarchar2(255), "WRITE_DELEGATE_UID" nvarchar2(255) ); create table GROUPS ( "GROUP_ID" integer primary key, "NAME" nvarchar2(255), "GROUP_UID" nvarchar2(255) unique, "MEMBERSHIP_HASH" nvarchar2(255), "EXTANT" integer default 1, "CREATED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC', "MODIFIED" timestamp default CURRENT_TIMESTAMP at time zone 'UTC' ); create table GROUP_MEMBERSHIP ( "GROUP_ID" integer not null references GROUPS on delete cascade, "MEMBER_UID" nvarchar2(255), primary key ("GROUP_ID", "MEMBER_UID") ); create table GROUP_ATTENDEE_RECONCILE_WORK ( "WORK_ID" integer primary key, "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 table GROUP_ATTENDEE ( "GROUP_ID" integer not null references GROUPS on delete cascade, "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade, "MEMBERSHIP_HASH" nvarchar2(255), primary key ("GROUP_ID", "RESOURCE_ID") ); create table GROUP_SHAREE_RECONCILE_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "CALENDAR_ID" integer not null references CALENDAR on delete cascade, "GROUP_ID" integer not null references GROUPS on delete cascade ); create table GROUP_SHAREE ( "GROUP_ID" integer not null references GROUPS on delete cascade, "CALENDAR_ID" integer not null references CALENDAR on delete cascade, "GROUP_BIND_MODE" integer not null, "MEMBERSHIP_HASH" nvarchar2(255), primary key ("GROUP_ID", "CALENDAR_ID") ); create table DELEGATES ( "DELEGATOR" nvarchar2(255), "DELEGATE" nvarchar2(255), "READ_WRITE" integer not null, primary key ("DELEGATOR", "READ_WRITE", "DELEGATE") ); create table DELEGATE_GROUPS ( "DELEGATOR" nvarchar2(255), "GROUP_ID" integer not null references GROUPS on delete cascade, "READ_WRITE" integer not null, "IS_EXTERNAL" integer not null, primary key ("DELEGATOR", "READ_WRITE", "GROUP_ID") ); create table EXTERNAL_DELEGATE_GROUPS ( "DELEGATOR" nvarchar2(255) primary key, "GROUP_UID_READ" nvarchar2(255), "GROUP_UID_WRITE" nvarchar2(255) ); create table CALENDAR_OBJECT_SPLITTER_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade ); create table CALENDAR_OBJECT_UPGRADE_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade ); create table FIND_MIN_VALID_REVISION_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB ); create table REVISION_CLEANUP_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB ); create table INBOX_CLEANUP_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB ); create table CLEANUP_ONE_INBOX_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "HOME_ID" integer not null unique references CALENDAR_HOME on delete cascade ); create table INBOX_REMOVE_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "HOME_ID" integer not null references CALENDAR_HOME on delete cascade, "RESOURCE_NAME" nvarchar2(255), unique ("HOME_ID", "RESOURCE_NAME") ); create table SCHEDULE_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "ICALENDAR_UID" nvarchar2(255), "WORK_TYPE" nvarchar2(255) ); create table SCHEDULE_REFRESH_WORK ( "WORK_ID" integer primary key references SCHEDULE_WORK on delete cascade, "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade, "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade, "ATTENDEE_COUNT" integer ); create table SCHEDULE_REFRESH_ATTENDEES ( "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade, "ATTENDEE" nvarchar2(255), primary key ("RESOURCE_ID", "ATTENDEE") ); create table SCHEDULE_AUTO_REPLY_WORK ( "WORK_ID" integer primary key references SCHEDULE_WORK on delete cascade, "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade, "RESOURCE_ID" integer not null references CALENDAR_OBJECT on delete cascade, "PARTSTAT" nvarchar2(255) ); create table SCHEDULE_ORGANIZER_WORK ( "WORK_ID" integer primary key references SCHEDULE_WORK on delete cascade, "SCHEDULE_ACTION" integer not null, "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade, "RESOURCE_ID" integer, "ICALENDAR_TEXT_OLD" nclob, "ICALENDAR_TEXT_NEW" nclob, "ATTENDEE_COUNT" integer, "SMART_MERGE" integer ); create table SCHEDULE_ACTION ( "ID" integer primary key, "DESCRIPTION" nvarchar2(16) unique ); insert into SCHEDULE_ACTION (DESCRIPTION, ID) values ('create', 0); insert into SCHEDULE_ACTION (DESCRIPTION, ID) values ('modify', 1); insert into SCHEDULE_ACTION (DESCRIPTION, ID) values ('modify-cancelled', 2); insert into SCHEDULE_ACTION (DESCRIPTION, ID) values ('remove', 3); create table SCHEDULE_ORGANIZER_SEND_WORK ( "WORK_ID" integer primary key references SCHEDULE_WORK on delete cascade, "SCHEDULE_ACTION" integer not null, "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade, "RESOURCE_ID" integer, "ATTENDEE" nvarchar2(255), "ITIP_MSG" nclob, "NO_REFRESH" integer ); create table SCHEDULE_REPLY_WORK ( "WORK_ID" integer primary key references SCHEDULE_WORK on delete cascade, "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade, "RESOURCE_ID" integer, "ITIP_MSG" nclob ); create table PRINCIPAL_PURGE_POLLING_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB ); create table PRINCIPAL_PURGE_CHECK_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "UID" nvarchar2(255) ); create table PRINCIPAL_PURGE_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "UID" nvarchar2(255) ); create table PRINCIPAL_PURGE_HOME_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade ); create table MIGRATION_CLEANUP_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "HOME_RESOURCE_ID" integer not null references CALENDAR_HOME on delete cascade ); create table HOME_CLEANUP_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "OWNER_UID" nvarchar2(255) ); create table MIGRATED_HOME_CLEANUP_WORK ( "WORK_ID" integer primary key, "JOB_ID" integer not null references JOB, "OWNER_UID" nvarchar2(255) ); create table CALENDARSERVER ( "NAME" nvarchar2(255) primary key, "VALUE" nvarchar2(255) ); insert into CALENDARSERVER (NAME, VALUE) values ('VERSION', '66'); insert into CALENDARSERVER (NAME, VALUE) values ('CALENDAR-DATAVERSION', '6'); insert into CALENDARSERVER (NAME, VALUE) values ('ADDRESSBOOK-DATAVERSION', '2'); insert into CALENDARSERVER (NAME, VALUE) values ('NOTIFICATION-DATAVERSION', '1'); insert into CALENDARSERVER (NAME, VALUE) values ('MIN-VALID-REVISION', '1'); create index JOB_PRIORITY_IS_ASSIG_48985bfd on JOB ( "PRIORITY", "IS_ASSIGNED", "PAUSE", "NOT_BEFORE", "JOB_ID" ); create index JOB_IS_ASSIGNED_PAUSE_1769af63 on JOB ( "IS_ASSIGNED", "PAUSE", "NOT_BEFORE", "JOB_ID" ); create index JOB_IS_ASSIGNED_OVERD_4a40c3f3 on JOB ( "IS_ASSIGNED", "OVERDUE", "JOB_ID" ); create index CALENDAR_HOME_METADAT_475de898 on CALENDAR_HOME_METADATA ( "TRASH" ); create index CALENDAR_HOME_METADAT_3cb9049e on CALENDAR_HOME_METADATA ( "DEFAULT_EVENTS" ); create index CALENDAR_HOME_METADAT_d55e5548 on CALENDAR_HOME_METADATA ( "DEFAULT_TASKS" ); create index CALENDAR_HOME_METADAT_910264ce on CALENDAR_HOME_METADATA ( "DEFAULT_POLLS" ); create index CALENDAR_MIGRATION_LO_0525c72b on CALENDAR_MIGRATION ( "LOCAL_RESOURCE_ID" ); create index NOTIFICATION_NOTIFICA_f891f5f9 on NOTIFICATION ( "NOTIFICATION_HOME_RESOURCE_ID" ); create index CALENDAR_BIND_RESOURC_e57964d4 on CALENDAR_BIND ( "CALENDAR_RESOURCE_ID" ); create index CALENDAR_OBJECT_CALEN_a9a453a9 on CALENDAR_OBJECT ( "CALENDAR_RESOURCE_ID", "ICALENDAR_UID" ); create index CALENDAR_OBJECT_CALEN_c4dc619c on CALENDAR_OBJECT ( "CALENDAR_RESOURCE_ID", "RECURRANCE_MAX", "RECURRANCE_MIN" ); create index CALENDAR_OBJECT_ICALE_82e731d5 on CALENDAR_OBJECT ( "ICALENDAR_UID" ); create index CALENDAR_OBJECT_DROPB_de041d80 on CALENDAR_OBJECT ( "DROPBOX_ID" ); create index CALENDAR_OBJECT_ORIGI_53447b73 on CALENDAR_OBJECT ( "ORIGINAL_COLLECTION", "TRASHED" ); create index TIME_RANGE_CALENDAR_R_beb6e7eb on TIME_RANGE ( "CALENDAR_RESOURCE_ID" ); create index TIME_RANGE_CALENDAR_O_acf37bd1 on TIME_RANGE ( "CALENDAR_OBJECT_RESOURCE_ID" ); create index CALENDAR_OBJECT_MIGRA_0502cbef on CALENDAR_OBJECT_MIGRATION ( "CALENDAR_HOME_RESOURCE_ID", "LOCAL_RESOURCE_ID" ); create index CALENDAR_OBJECT_MIGRA_3577efd9 on CALENDAR_OBJECT_MIGRATION ( "LOCAL_RESOURCE_ID" ); create index ATTACHMENT_CALENDAR_H_0078845c on ATTACHMENT ( "CALENDAR_HOME_RESOURCE_ID" ); create index ATTACHMENT_DROPBOX_ID_5073cf23 on ATTACHMENT ( "DROPBOX_ID" ); create index ATTACHMENT_CALENDAR_O_81508484 on ATTACHMENT_CALENDAR_OBJECT ( "CALENDAR_OBJECT_RESOURCE_ID" ); create index ATTACHMENT_MIGRATION__804bf85e on ATTACHMENT_MIGRATION ( "CALENDAR_HOME_RESOURCE_ID", "LOCAL_RESOURCE_ID" ); create index ATTACHMENT_MIGRATION__816947fe on ATTACHMENT_MIGRATION ( "LOCAL_RESOURCE_ID" ); create index SHARED_ADDRESSBOOK_BI_e9a2e6d4 on SHARED_ADDRESSBOOK_BIND ( "OWNER_HOME_RESOURCE_ID" ); create index ABO_MEMBERS_ADDRESSBO_4effa879 on ABO_MEMBERS ( "ADDRESSBOOK_ID" ); create index ABO_MEMBERS_MEMBER_ID_8d66adcf on ABO_MEMBERS ( "MEMBER_ID" ); create index ABO_FOREIGN_MEMBERS_A_1fd2c5e9 on ABO_FOREIGN_MEMBERS ( "ADDRESSBOOK_ID" ); create index SHARED_GROUP_BIND_RES_cf52f95d on SHARED_GROUP_BIND ( "GROUP_RESOURCE_ID" ); create index CALENDAR_OBJECT_REVIS_6d9d929c on CALENDAR_OBJECT_REVISIONS ( "CALENDAR_RESOURCE_ID", "RESOURCE_NAME", "DELETED", "REVISION" ); create index CALENDAR_OBJECT_REVIS_265c8acf on CALENDAR_OBJECT_REVISIONS ( "CALENDAR_RESOURCE_ID", "REVISION" ); create index CALENDAR_OBJECT_REVIS_550b1c56 on CALENDAR_OBJECT_REVISIONS ( "CALENDAR_HOME_RESOURCE_ID", "REVISION" ); create index CALENDAR_OBJECT_REVIS_fa21ef83 on CALENDAR_OBJECT_REVISIONS ( "REVISION" ); create index ADDRESSBOOK_OBJECT_RE_00fe8288 on ADDRESSBOOK_OBJECT_REVISIONS ( "OWNER_HOME_RESOURCE_ID", "RESOURCE_NAME", "DELETED", "REVISION" ); create index ADDRESSBOOK_OBJECT_RE_45004780 on ADDRESSBOOK_OBJECT_REVISIONS ( "OWNER_HOME_RESOURCE_ID", "REVISION" ); create index ADDRESSBOOK_OBJECT_RE_0900cfdf on ADDRESSBOOK_OBJECT_REVISIONS ( "REVISION" ); create index NOTIFICATION_OBJECT_R_036a9cee on NOTIFICATION_OBJECT_REVISIONS ( "NOTIFICATION_HOME_RESOURCE_ID", "REVISION" ); create index NOTIFICATION_OBJECT_R_c251f0fd on NOTIFICATION_OBJECT_REVISIONS ( "REVISION" ); create index APN_SUBSCRIPTIONS_RES_9610d78e on APN_SUBSCRIPTIONS ( "RESOURCE_KEY" ); create index APN_SUBSCRIPTIONS_MOD_69027430 on APN_SUBSCRIPTIONS ( "MODIFIED" ); create index IMIP_TOKENS_TOKEN_e94b918f on IMIP_TOKENS ( "TOKEN" ); create index TEST_WORK_JOB_ID_228ede32 on TEST_WORK ( "JOB_ID" ); create index APN_PURGING_WORK_JOB__328a2e62 on APN_PURGING_WORK ( "JOB_ID" ); create index IMIP_INVITATION_WORK__586d064c on IMIP_INVITATION_WORK ( "JOB_ID" ); create index IMIP_POLLING_WORK_JOB_d5535891 on IMIP_POLLING_WORK ( "JOB_ID" ); create index IMIP_REPLY_WORK_JOB_I_bf4ae73e on IMIP_REPLY_WORK ( "JOB_ID" ); create index PUSH_NOTIFICATION_WOR_8bbab117 on PUSH_NOTIFICATION_WORK ( "JOB_ID" ); create index PUSH_NOTIFICATION_WOR_3a3ee588 on PUSH_NOTIFICATION_WORK ( "PUSH_ID" ); create index GROUP_CACHER_POLLING__6eb3151c on GROUP_CACHER_POLLING_WORK ( "JOB_ID" ); create index GROUP_REFRESH_WORK_JO_717ede20 on GROUP_REFRESH_WORK ( "JOB_ID" ); create index GROUP_REFRESH_WORK_GR_0325f3a8 on GROUP_REFRESH_WORK ( "GROUP_UID" ); create index GROUP_DELEGATE_CHANGE_8bf9e6d8 on GROUP_DELEGATE_CHANGES_WORK ( "JOB_ID" ); create index GROUP_DELEGATE_CHANGE_d8f7af69 on GROUP_DELEGATE_CHANGES_WORK ( "DELEGATOR_UID" ); create index GROUP_MEMBERSHIP_MEMB_0ca508e8 on GROUP_MEMBERSHIP ( "MEMBER_UID" ); create index GROUP_ATTENDEE_RECONC_da73d3c2 on GROUP_ATTENDEE_RECONCILE_WORK ( "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" ); create index GROUP_SHAREE_RECONCIL_9aad0858 on GROUP_SHAREE_RECONCILE_WORK ( "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_28a88850 on GROUP_SHAREE ( "CALENDAR_ID" ); create index DELEGATE_TO_DELEGATOR_5e149b11 on DELEGATES ( "DELEGATE", "READ_WRITE", "DELEGATOR" ); create index DELEGATE_GROUPS_GROUP_25117446 on DELEGATE_GROUPS ( "GROUP_ID" ); create index CALENDAR_OBJECT_SPLIT_af71dcda on CALENDAR_OBJECT_SPLITTER_WORK ( "RESOURCE_ID" ); create index CALENDAR_OBJECT_SPLIT_33603b72 on CALENDAR_OBJECT_SPLITTER_WORK ( "JOB_ID" ); create index CALENDAR_OBJECT_UPGRA_a5c181eb on CALENDAR_OBJECT_UPGRADE_WORK ( "RESOURCE_ID" ); create index CALENDAR_OBJECT_UPGRA_39d6f8f9 on CALENDAR_OBJECT_UPGRADE_WORK ( "JOB_ID" ); create index FIND_MIN_VALID_REVISI_78d17400 on FIND_MIN_VALID_REVISION_WORK ( "JOB_ID" ); create index REVISION_CLEANUP_WORK_eb062686 on REVISION_CLEANUP_WORK ( "JOB_ID" ); create index INBOX_CLEANUP_WORK_JO_799132bd on INBOX_CLEANUP_WORK ( "JOB_ID" ); create index CLEANUP_ONE_INBOX_WOR_375dac36 on CLEANUP_ONE_INBOX_WORK ( "JOB_ID" ); create index INBOX_REMOVE_WORK_JOB_4b627f1e on INBOX_REMOVE_WORK ( "JOB_ID" ); create index SCHEDULE_WORK_JOB_ID_65e810ee on SCHEDULE_WORK ( "JOB_ID" ); create index SCHEDULE_WORK_ICALEND_089f33dc on SCHEDULE_WORK ( "ICALENDAR_UID" ); create index SCHEDULE_REFRESH_WORK_26084c7b on SCHEDULE_REFRESH_WORK ( "HOME_RESOURCE_ID" ); create index SCHEDULE_REFRESH_WORK_989efe54 on SCHEDULE_REFRESH_WORK ( "RESOURCE_ID" ); create index SCHEDULE_AUTO_REPLY_W_0256478d on SCHEDULE_AUTO_REPLY_WORK ( "HOME_RESOURCE_ID" ); create index SCHEDULE_AUTO_REPLY_W_0755e754 on SCHEDULE_AUTO_REPLY_WORK ( "RESOURCE_ID" ); create index SCHEDULE_ORGANIZER_WO_18ce4edd on SCHEDULE_ORGANIZER_WORK ( "HOME_RESOURCE_ID" ); create index SCHEDULE_ORGANIZER_WO_14702035 on SCHEDULE_ORGANIZER_WORK ( "RESOURCE_ID" ); create index SCHEDULE_ORGANIZER_SE_9ec9f827 on SCHEDULE_ORGANIZER_SEND_WORK ( "HOME_RESOURCE_ID" ); create index SCHEDULE_ORGANIZER_SE_699fefc4 on SCHEDULE_ORGANIZER_SEND_WORK ( "RESOURCE_ID" ); create index SCHEDULE_REPLY_WORK_H_745af8cf on SCHEDULE_REPLY_WORK ( "HOME_RESOURCE_ID" ); create index SCHEDULE_REPLY_WORK_R_11bd3fbb on SCHEDULE_REPLY_WORK ( "RESOURCE_ID" ); create index PRINCIPAL_PURGE_POLLI_6383e68a on PRINCIPAL_PURGE_POLLING_WORK ( "JOB_ID" ); create index PRINCIPAL_PURGE_CHECK_b0c024c1 on PRINCIPAL_PURGE_CHECK_WORK ( "JOB_ID" ); create index PRINCIPAL_PURGE_CHECK_198388a5 on PRINCIPAL_PURGE_CHECK_WORK ( "UID" ); create index PRINCIPAL_PURGE_WORK__7a8141a3 on PRINCIPAL_PURGE_WORK ( "JOB_ID" ); create index PRINCIPAL_PURGE_WORK__db35cfdc on PRINCIPAL_PURGE_WORK ( "UID" ); create index PRINCIPAL_PURGE_HOME__f35eea7a on PRINCIPAL_PURGE_HOME_WORK ( "JOB_ID" ); create index PRINCIPAL_PURGE_HOME__967e4480 on PRINCIPAL_PURGE_HOME_WORK ( "HOME_RESOURCE_ID" ); create index MIGRATION_CLEANUP_WOR_8c23cc35 on MIGRATION_CLEANUP_WORK ( "JOB_ID" ); create index MIGRATION_CLEANUP_WOR_86181cb8 on MIGRATION_CLEANUP_WORK ( "HOME_RESOURCE_ID" ); create index HOME_CLEANUP_WORK_JOB_9631dfb0 on HOME_CLEANUP_WORK ( "JOB_ID" ); create index MIGRATED_HOME_CLEANUP_4c714fd4 on MIGRATED_HOME_CLEANUP_WORK ( "JOB_ID" ); -- Extra schema to add to current-oracle-dialect.sql create or replace function next_job(now in timestamp, min_priority in integer, row_limit in integer) return integer is cursor c (test_priority number) is select JOB_ID from JOB where PRIORITY = test_priority and IS_ASSIGNED = 0 and PAUSE = 0 and NOT_BEFORE <= now and ROWNUM <= row_limit for update skip locked; result integer; begin open c(2); fetch c into result; close c; if result is null and min_priority != 2 then open c(1); fetch c into result; close c; if result is null and min_priority = 0 then open c(0); fetch c into result; close c; end if; end if; return result; end; / create or replace function overdue_job(now in timestamp, row_limit in integer) return integer is cursor c is select JOB_ID from JOB where IS_ASSIGNED = 1 and OVERDUE <= now and ROWNUM <= row_limit for update skip locked; result integer; begin open c; fetch c into result; close c; return result; end; /