From abc00f27c5d923a02c946a634e158dfe9f20974e Mon Sep 17 00:00:00 2001
From: Aleksander Machniak <alec@alec.pl>
Date: Mon, 15 Sep 2014 06:20:20 -0400
Subject: [PATCH] Initial DDL for Oracle
---
SQL/oracle.initial.sql | 221 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 221 insertions(+), 0 deletions(-)
diff --git a/SQL/oracle.initial.sql b/SQL/oracle.initial.sql
new file mode 100644
index 0000000..84f7a1f
--- /dev/null
+++ b/SQL/oracle.initial.sql
@@ -0,0 +1,221 @@
+-- Roundcube Webmail initial database structure
+-- This was tested with Oracle 11g
+
+CREATE TABLE "users" (
+ "user_id" integer PRIMARY KEY,
+ "username" varchar(128) NOT NULL,
+ "mail_host" varchar(128) NOT NULL,
+ "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
+ "last_login" timestamp with time zone DEFAULT NULL,
+ "language" varchar(5),
+ "preferences" long DEFAULT NULL,
+ CONSTRAINT "users_username_key" UNIQUE ("username", "mail_host")
+);
+
+CREATE SEQUENCE "users_seq"
+ START WITH 1 INCREMENT BY 1 NOMAXVALUE;
+
+CREATE TRIGGER "users_seq_trig"
+BEFORE INSERT ON "users" FOR EACH ROW
+BEGIN
+ :NEW."user_id" := "users_seq".nextval;
+END;
+
+
+CREATE TABLE "session" (
+ "sess_id" varchar(128) NOT NULL PRIMARY KEY,
+ "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
+ "changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
+ "ip" varchar(41) NOT NULL,
+ "vars" long NOT NULL
+);
+
+CREATE INDEX "session_changed_idx" ON "session" ("changed");
+
+
+CREATE TABLE "identities" (
+ "identity_id" integer PRIMARY KEY,
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
+ "del" smallint DEFAULT 0 NOT NULL,
+ "standard" smallint DEFAULT 0 NOT NULL,
+ "name" varchar(128) NOT NULL,
+ "organization" varchar(128),
+ "email" varchar(128) NOT NULL,
+ "reply-to" varchar(128),
+ "bcc" varchar(128),
+ "signature" long,
+ "html_signature" integer DEFAULT 0 NOT NULL
+);
+
+CREATE INDEX "identities_user_id_idx" ON "identities" ("user_id", "del");
+CREATE INDEX "identities_email_idx" ON "identities" ("email", "del");
+
+CREATE SEQUENCE "identities_seq"
+ START WITH 1 INCREMENT BY 1 NOMAXVALUE;
+
+CREATE TRIGGER "identities_seq_trig"
+BEFORE INSERT ON "identities" FOR EACH ROW
+BEGIN
+ :NEW."identity_id" := "identities_seq".nextval;
+END;
+
+
+CREATE TABLE "contacts" (
+ "contact_id" integer PRIMARY KEY,
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
+ "del" smallint DEFAULT 0 NOT NULL,
+ "name" varchar(128) DEFAULT NULL,
+ "email" varchar(4000) DEFAULT NULL,
+ "firstname" varchar(128) DEFAULT NULL,
+ "surname" varchar(128) DEFAULT NULL,
+ "vcard" long,
+ "words" varchar(4000)
+);
+
+CREATE INDEX "contacts_user_id_idx" ON "contacts" ("user_id", "del");
+
+CREATE SEQUENCE "contacts_seq"
+ START WITH 1 INCREMENT BY 1 NOMAXVALUE;
+
+CREATE TRIGGER "contacts_seq_trig"
+BEFORE INSERT ON "contacts" FOR EACH ROW
+BEGIN
+ :NEW."contact_id" := "contacts_seq".nextval;
+END;
+
+
+CREATE TABLE "contactgroups" (
+ "contactgroup_id" integer PRIMARY KEY,
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
+ "del" smallint DEFAULT 0 NOT NULL,
+ "name" varchar(128) NOT NULL
+);
+
+CREATE INDEX "contactgroups_user_id_idx" ON "contactgroups" ("user_id", "del");
+
+CREATE SEQUENCE "contactgroups_seq"
+ START WITH 1 INCREMENT BY 1 NOMAXVALUE;
+
+CREATE TRIGGER "contactgroups_seq_trig"
+BEFORE INSERT ON "contactgroups" FOR EACH ROW
+BEGIN
+ :NEW."contactgroup_id" := "contactgroups_seq".nextval;
+END;
+
+
+CREATE TABLE "contactgroupmembers" (
+ "contactgroup_id" integer NOT NULL
+ REFERENCES "contactgroups" ("contactgroup_id") ON DELETE CASCADE,
+ "contact_id" integer NOT NULL
+ REFERENCES "contacts" ("contact_id") ON DELETE CASCADE,
+ "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
+ PRIMARY KEY ("contactgroup_id", "contact_id")
+);
+
+CREATE INDEX "contactgroupmembers_idx" ON "contactgroupmembers" ("contact_id");
+
+
+CREATE TABLE "cache" (
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "cache_key" varchar(128) NOT NULL,
+ "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
+ "expires" timestamp with time zone DEFAULT NULL,
+ "data" long NOT NULL
+);
+
+CREATE INDEX "cache_user_id_idx" ON "cache" ("user_id", "cache_key");
+CREATE INDEX "cache_expires_idx" ON "cache" ("expires");
+
+
+CREATE TABLE "cache_shared" (
+ "cache_key" varchar(255) NOT NULL,
+ "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
+ "expires" timestamp with time zone DEFAULT NULL,
+ "data" long NOT NULL
+);
+
+CREATE INDEX "cache_shared_cache_key_idx" ON "cache_shared" ("cache_key");
+CREATE INDEX "cache_shared_expires_idx" ON "cache_shared" ("expires");
+
+
+CREATE TABLE "cache_index" (
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "mailbox" varchar(255) NOT NULL,
+ "expires" timestamp with time zone DEFAULT NULL,
+ "valid" smallint DEFAULT 0 NOT NULL,
+ "data" long NOT NULL,
+ PRIMARY KEY ("user_id", "mailbox")
+);
+
+CREATE INDEX "cache_index_expires_idx" ON "cache_index" ("expires");
+
+
+CREATE TABLE "cache_thread" (
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "mailbox" varchar(255) NOT NULL,
+ "expires" timestamp with time zone DEFAULT NULL,
+ "data" long NOT NULL,
+ PRIMARY KEY ("user_id", "mailbox")
+);
+
+CREATE INDEX "cache_thread_expires_idx" ON "cache_thread" ("expires");
+
+
+CREATE TABLE "cache_messages" (
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "mailbox" varchar(255) NOT NULL,
+ "uid" integer NOT NULL,
+ "expires" timestamp with time zone DEFAULT NULL,
+ "data" long NOT NULL,
+ "flags" integer DEFAULT 0 NOT NULL,
+ PRIMARY KEY ("user_id", "mailbox", "uid")
+);
+
+CREATE INDEX "cache_messages_expires_idx" ON "cache_messages" ("expires");
+
+
+CREATE TABLE "dictionary" (
+ "user_id" integer DEFAULT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "language" varchar(5) NOT NULL,
+ "data" long DEFAULT NULL,
+ CONSTRAINT "dictionary_user_id_lang_key" UNIQUE ("user_id", "language")
+);
+
+
+CREATE TABLE "searches" (
+ "search_id" integer PRIMARY KEY,
+ "user_id" integer NOT NULL
+ REFERENCES "users" ("user_id") ON DELETE CASCADE,
+ "type" smallint DEFAULT 0 NOT NULL,
+ "name" varchar(128) NOT NULL,
+ "data" long NOT NULL,
+ CONSTRAINT "searches_user_id_key" UNIQUE ("user_id", "type", "name")
+);
+
+CREATE SEQUENCE "searches_seq"
+ START WITH 1 INCREMENT BY 1 NOMAXVALUE;
+
+CREATE TRIGGER "searches_seq_trig"
+BEFORE INSERT ON "searches" FOR EACH ROW
+BEGIN
+ :NEW."search_id" := "searches_seq".nextval;
+END;
+
+
+CREATE TABLE "system" (
+ "name" varchar(64) NOT NULL PRIMARY KEY,
+ "value" long
+);
+
+INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2014042900');
--
Gitblit v1.9.1