From abc00f27c5d923a02c946a634e158dfe9f20974e Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Mon, 15 Sep 2014 12:20:20 +0200 Subject: Initial DDL for Oracle --- SQL/oracle.initial.sql | 221 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 221 insertions(+) create mode 100644 SQL/oracle.initial.sql (limited to 'SQL') diff --git a/SQL/oracle.initial.sql b/SQL/oracle.initial.sql new file mode 100644 index 000000000..84f7a1f16 --- /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'); -- cgit v1.2.3