summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAleksander Machniak <machniak@kolabsys.com>2014-09-15 10:26:03 (GMT)
committerAleksander Machniak <machniak@kolabsys.com>2014-09-15 10:26:03 (GMT)
commitd2e7c27bf4e44036def6592b772e220c13697606 (patch)
tree20d46a8be59bd8cb51c0e1954645d95615162b78
parent788635b2876b2db1e5d66046a0bdfa729a016afd (diff)
downloadroundcubemail-plugins-kolab-d2e7c27bf4e44036def6592b772e220c13697606.tar.gz
Initial DDL for Oracle
-rw-r--r--plugins/calendar/drivers/kolab/SQL/oracle.initial.sql31
-rw-r--r--plugins/libkolab/SQL/oracle.initial.sql184
2 files changed, 215 insertions, 0 deletions
diff --git a/plugins/calendar/drivers/kolab/SQL/oracle.initial.sql b/plugins/calendar/drivers/kolab/SQL/oracle.initial.sql
new file mode 100644
index 0000000..d6d882b
--- /dev/null
+++ b/plugins/calendar/drivers/kolab/SQL/oracle.initial.sql
@@ -0,0 +1,31 @@
+/**
+ * Roundcube Calendar Kolab backend
+ *
+ * @author Aleksander Machniak
+ * @licence GNU AGPL
+ **/
+
+CREATE TABLE "kolab_alarms" (
+ "alarm_id" varchar(255) NOT NULL PRIMARY KEY,
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "notifyat" timestamp DEFAULT NULL,
+ "dismissed" smallint DEFAULT 0 NOT NULL
+);
+
+CREATE INDEX "kolab_alarms_user_id_idx" ON "kolab_alarms" ("user_id");
+
+
+CREATE TABLE "itipinvitations" (
+ "token" varchar(64) NOT NULL PRIMARY KEY,
+ "event_uid" varchar(255) NOT NULL,
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "event" long NOT NULL,
+ "expires" timestamp DEFAULT NULL,
+ "cancelled" smallint DEFAULT 0 NOT NULL
+);
+
+CREATE INDEX "itipinvitations_user_id_idx" ON "itipinvitations" ("user_id", "event_uid");
+
+INSERT INTO "system" ("name", "value") VALUES ('calendar-kolab-version', '2014041700');
diff --git a/plugins/libkolab/SQL/oracle.initial.sql b/plugins/libkolab/SQL/oracle.initial.sql
new file mode 100644
index 0000000..2c078cb
--- /dev/null
+++ b/plugins/libkolab/SQL/oracle.initial.sql
@@ -0,0 +1,184 @@
+/**
+ * libkolab database schema
+ *
+ * @version 1.1
+ * @author Aleksander Machniak
+ * @licence GNU AGPL
+ **/
+
+
+CREATE TABLE "kolab_folders" (
+ "folder_id" number NOT NULL PRIMARY KEY,
+ "resource" VARCHAR(255) NOT NULL,
+ "type" VARCHAR(32) NOT NULL,
+ "synclock" integer DEFAULT 0 NOT NULL,
+ "ctag" VARCHAR(40) DEFAULT NULL
+);
+
+CREATE INDEX "kolab_folders_resource_idx" ON "kolab_folders" ("resource", "type");
+
+CREATE SEQUENCE "kolab_folders_seq"
+ START WITH 1 INCREMENT BY 1 NOMAXVALUE;
+
+CREATE TRIGGER "kolab_folders_seq_trig"
+BEFORE INSERT ON "kolab_folders" FOR EACH ROW
+BEGIN
+ :NEW."folder_id" := "kolab_folders_seq".nextval;
+END;
+
+
+CREATE TABLE "kolab_cache_contact" (
+ "folder_id" number NOT NULL
+ REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+ "msguid" number NOT NULL,
+ "uid" varchar(128) NOT NULL,
+ "created" timestamp DEFAULT NULL,
+ "changed" timestamp DEFAULT NULL,
+ "data" clob NOT NULL,
+ "xml" clob NOT NULL,
+ "tags" varchar(255) DEFAULT NULL,
+ "words" clob DEFAULT NULL,
+ "type" varchar(32) NOT NULL,
+ "name" varchar(255) DEFAULT NULL,
+ "firstname" varchar(255) DEFAULT NULL,
+ "surname" varchar(255) DEFAULT NULL,
+ "email" varchar(255) DEFAULT NULL,
+ PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_contact_type_idx" ON "kolab_cache_contact" ("folder_id", "type");
+CREATE INDEX "kolab_cache_contact_uid2msguid" ON "kolab_cache_contact" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_event" (
+ "folder_id" number NOT NULL
+ REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+ "msguid" number NOT NULL,
+ "uid" varchar(128) NOT NULL,
+ "created" timestamp DEFAULT NULL,
+ "changed" timestamp DEFAULT NULL,
+ "data" clob NOT NULL,
+ "xml" clob NOT NULL,
+ "tags" varchar(255) DEFAULT NULL,
+ "words" clob DEFAULT NULL,
+ "dtstart" timestamp DEFAULT NULL,
+ "dtend" timestamp DEFAULT NULL,
+ PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_event_uid2msguid" ON "kolab_cache_event" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_task" (
+ "folder_id" number NOT NULL
+ REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+ "msguid" number NOT NULL,
+ "uid" varchar(128) NOT NULL,
+ "created" timestamp DEFAULT NULL,
+ "changed" timestamp DEFAULT NULL,
+ "data" clob NOT NULL,
+ "xml" clob NOT NULL,
+ "tags" varchar(255) DEFAULT NULL,
+ "words" clob DEFAULT NULL,
+ "dtstart" timestamp DEFAULT NULL,
+ "dtend" timestamp DEFAULT NULL,
+ PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_task_uid2msguid" ON "kolab_cache_task" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_journal" (
+ "folder_id" number NOT NULL
+ REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+ "msguid" number NOT NULL,
+ "uid" varchar(128) NOT NULL,
+ "created" timestamp DEFAULT NULL,
+ "changed" timestamp DEFAULT NULL,
+ "data" clob NOT NULL,
+ "xml" clob NOT NULL,
+ "tags" varchar(255) DEFAULT NULL,
+ "words" clob DEFAULT NULL,
+ "dtstart" timestamp DEFAULT NULL,
+ "dtend" timestamp DEFAULT NULL,
+ PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_journal_uid2msguid" ON "kolab_cache_journal" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_note" (
+ "folder_id" number NOT NULL
+ REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+ "msguid" number NOT NULL,
+ "uid" varchar(128) NOT NULL,
+ "created" timestamp DEFAULT NULL,
+ "changed" timestamp DEFAULT NULL,
+ "data" clob NOT NULL,
+ "xml" clob NOT NULL,
+ "tags" varchar(255) DEFAULT NULL,
+ "words" clob DEFAULT NULL,
+ PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_note_uid2msguid" ON "kolab_cache_note" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_file" (
+ "folder_id" number NOT NULL
+ REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+ "msguid" number NOT NULL,
+ "uid" varchar(128) NOT NULL,
+ "created" timestamp DEFAULT NULL,
+ "changed" timestamp DEFAULT NULL,
+ "data" clob NOT NULL,
+ "xml" clob NOT NULL,
+ "tags" varchar(255) DEFAULT NULL,
+ "words" clob DEFAULT NULL,
+ "filename" varchar(255) DEFAULT NULL,
+ PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_file_filename" ON "kolab_cache_file" ("folder_id", "filename");
+CREATE INDEX "kolab_cache_file_uid2msguid" ON "kolab_cache_file" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_configuration" (
+ "folder_id" number NOT NULL
+ REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+ "msguid" number NOT NULL,
+ "uid" varchar(128) NOT NULL,
+ "created" timestamp DEFAULT NULL,
+ "changed" timestamp DEFAULT NULL,
+ "data" clob NOT NULL,
+ "xml" clob NOT NULL,
+ "tags" varchar(255) DEFAULT NULL,
+ "words" clob DEFAULT NULL,
+ "type" varchar(32) NOT NULL,
+ PRIMARY KEY ("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_config_type" ON "kolab_cache_configuration" ("folder_id", "type");
+CREATE INDEX "kolab_cache_config_uid2msguid" ON "kolab_cache_configuration" ("folder_id", "uid", "msguid");
+
+
+CREATE TABLE "kolab_cache_freebusy" (
+ "folder_id" number NOT NULL
+ REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE,
+ "msguid" number NOT NULL,
+ "uid" varchar(128) NOT NULL,
+ "created" timestamp DEFAULT NULL,
+ "changed" timestamp DEFAULT NULL,
+ "data" clob NOT NULL,
+ "xml" clob NOT NULL,
+ "tags" varchar(255) DEFAULT NULL,
+ "words" clob DEFAULT NULL,
+ "dtstart" timestamp DEFAULT NULL,
+ "dtend" timestamp DEFAULT NULL,
+ PRIMARY KEY("folder_id", "msguid")
+);
+
+CREATE INDEX "kolab_cache_fb_uid2msguid" ON "kolab_cache_freebusy" ("folder_id", "uid", "msguid");
+
+
+INSERT INTO "system" ("name", "value") VALUES ('libkolab-version', '2014021000');