Mercurial > hg > rc2

comparison SQL/oracle.initial.sql @ 0:4681f974d28b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
vanilla 1.3.3 distro, I hope
author Charlie Root
date Thu, 04 Jan 2018 15:52:31 -0500
parents
children bf99236cc5cd
comparison
equal deleted inserted replaced
-1:000000000000 0:4681f974d28b
1 -- Roundcube Webmail initial database structure
2 -- This was tested with Oracle 11g
3
4 CREATE TABLE "users" (
5 "user_id" integer PRIMARY KEY,
6 "username" varchar(128) NOT NULL,
7 "mail_host" varchar(128) NOT NULL,
8 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
9 "last_login" timestamp with time zone DEFAULT NULL,
10 "language" varchar(5),
11 "preferences" long DEFAULT NULL,
12 CONSTRAINT "users_username_key" UNIQUE ("username", "mail_host")
13 );
14
15 CREATE SEQUENCE "users_seq"
16 START WITH 1 INCREMENT BY 1 NOMAXVALUE;
17
18 CREATE TRIGGER "users_seq_trig"
19 BEFORE INSERT ON "users" FOR EACH ROW
20 BEGIN
21 :NEW."user_id" := "users_seq".nextval;
22 END;
23 /
24
25 CREATE TABLE "session" (
26 "sess_id" varchar(128) NOT NULL PRIMARY KEY,
27 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
28 "changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
29 "ip" varchar(41) NOT NULL,
30 "vars" long NOT NULL
31 );
32
33 CREATE INDEX "session_changed_idx" ON "session" ("changed");
34
35
36 CREATE TABLE "identities" (
37 "identity_id" integer PRIMARY KEY,
38 "user_id" integer NOT NULL
39 REFERENCES "users" ("user_id") ON DELETE CASCADE,
40 "changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
41 "del" smallint DEFAULT 0 NOT NULL,
42 "standard" smallint DEFAULT 0 NOT NULL,
43 "name" varchar(128) NOT NULL,
44 "organization" varchar(128),
45 "email" varchar(128) NOT NULL,
46 "reply-to" varchar(128),
47 "bcc" varchar(128),
48 "signature" long,
49 "html_signature" integer DEFAULT 0 NOT NULL
50 );
51
52 CREATE INDEX "identities_user_id_idx" ON "identities" ("user_id", "del");
53 CREATE INDEX "identities_email_idx" ON "identities" ("email", "del");
54
55 CREATE SEQUENCE "identities_seq"
56 START WITH 1 INCREMENT BY 1 NOMAXVALUE;
57
58 CREATE TRIGGER "identities_seq_trig"
59 BEFORE INSERT ON "identities" FOR EACH ROW
60 BEGIN
61 :NEW."identity_id" := "identities_seq".nextval;
62 END;
63 /
64
65 CREATE TABLE "contacts" (
66 "contact_id" integer PRIMARY KEY,
67 "user_id" integer NOT NULL
68 REFERENCES "users" ("user_id") ON DELETE CASCADE,
69 "changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
70 "del" smallint DEFAULT 0 NOT NULL,
71 "name" varchar(128) DEFAULT NULL,
72 "email" varchar(4000) DEFAULT NULL,
73 "firstname" varchar(128) DEFAULT NULL,
74 "surname" varchar(128) DEFAULT NULL,
75 "vcard" long,
76 "words" varchar(4000)
77 );
78
79 CREATE INDEX "contacts_user_id_idx" ON "contacts" ("user_id", "del");
80
81 CREATE SEQUENCE "contacts_seq"
82 START WITH 1 INCREMENT BY 1 NOMAXVALUE;
83
84 CREATE TRIGGER "contacts_seq_trig"
85 BEFORE INSERT ON "contacts" FOR EACH ROW
86 BEGIN
87 :NEW."contact_id" := "contacts_seq".nextval;
88 END;
89 /
90
91 CREATE TABLE "contactgroups" (
92 "contactgroup_id" integer PRIMARY KEY,
93 "user_id" integer NOT NULL
94 REFERENCES "users" ("user_id") ON DELETE CASCADE,
95 "changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
96 "del" smallint DEFAULT 0 NOT NULL,
97 "name" varchar(128) NOT NULL
98 );
99
100 CREATE INDEX "contactgroups_user_id_idx" ON "contactgroups" ("user_id", "del");
101
102 CREATE SEQUENCE "contactgroups_seq"
103 START WITH 1 INCREMENT BY 1 NOMAXVALUE;
104
105 CREATE TRIGGER "contactgroups_seq_trig"
106 BEFORE INSERT ON "contactgroups" FOR EACH ROW
107 BEGIN
108 :NEW."contactgroup_id" := "contactgroups_seq".nextval;
109 END;
110 /
111
112 CREATE TABLE "contactgroupmembers" (
113 "contactgroup_id" integer NOT NULL
114 REFERENCES "contactgroups" ("contactgroup_id") ON DELETE CASCADE,
115 "contact_id" integer NOT NULL
116 REFERENCES "contacts" ("contact_id") ON DELETE CASCADE,
117 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
118 PRIMARY KEY ("contactgroup_id", "contact_id")
119 );
120
121 CREATE INDEX "contactgroupmembers_idx" ON "contactgroupmembers" ("contact_id");
122
123
124 CREATE TABLE "cache" (
125 "user_id" integer NOT NULL
126 REFERENCES "users" ("user_id") ON DELETE CASCADE,
127 "cache_key" varchar(128) NOT NULL,
128 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
129 "expires" timestamp with time zone DEFAULT NULL,
130 "data" long NOT NULL
131 );
132
133 CREATE INDEX "cache_user_id_idx" ON "cache" ("user_id", "cache_key");
134 CREATE INDEX "cache_expires_idx" ON "cache" ("expires");
135
136
137 CREATE TABLE "cache_shared" (
138 "cache_key" varchar(255) NOT NULL,
139 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
140 "expires" timestamp with time zone DEFAULT NULL,
141 "data" long NOT NULL
142 );
143
144 CREATE INDEX "cache_shared_cache_key_idx" ON "cache_shared" ("cache_key");
145 CREATE INDEX "cache_shared_expires_idx" ON "cache_shared" ("expires");
146
147
148 CREATE TABLE "cache_index" (
149 "user_id" integer NOT NULL
150 REFERENCES "users" ("user_id") ON DELETE CASCADE,
151 "mailbox" varchar(255) NOT NULL,
152 "expires" timestamp with time zone DEFAULT NULL,
153 "valid" smallint DEFAULT 0 NOT NULL,
154 "data" long NOT NULL,
155 PRIMARY KEY ("user_id", "mailbox")
156 );
157
158 CREATE INDEX "cache_index_expires_idx" ON "cache_index" ("expires");
159
160
161 CREATE TABLE "cache_thread" (
162 "user_id" integer NOT NULL
163 REFERENCES "users" ("user_id") ON DELETE CASCADE,
164 "mailbox" varchar(255) NOT NULL,
165 "expires" timestamp with time zone DEFAULT NULL,
166 "data" long NOT NULL,
167 PRIMARY KEY ("user_id", "mailbox")
168 );
169
170 CREATE INDEX "cache_thread_expires_idx" ON "cache_thread" ("expires");
171
172
173 CREATE TABLE "cache_messages" (
174 "user_id" integer NOT NULL
175 REFERENCES "users" ("user_id") ON DELETE CASCADE,
176 "mailbox" varchar(255) NOT NULL,
177 "uid" integer NOT NULL,
178 "expires" timestamp with time zone DEFAULT NULL,
179 "data" long NOT NULL,
180 "flags" integer DEFAULT 0 NOT NULL,
181 PRIMARY KEY ("user_id", "mailbox", "uid")
182 );
183
184 CREATE INDEX "cache_messages_expires_idx" ON "cache_messages" ("expires");
185
186
187 CREATE TABLE "dictionary" (
188 "user_id" integer DEFAULT NULL
189 REFERENCES "users" ("user_id") ON DELETE CASCADE,
190 "language" varchar(5) NOT NULL,
191 "data" long DEFAULT NULL,
192 CONSTRAINT "dictionary_user_id_lang_key" UNIQUE ("user_id", "language")
193 );
194
195
196 CREATE TABLE "searches" (
197 "search_id" integer PRIMARY KEY,
198 "user_id" integer NOT NULL
199 REFERENCES "users" ("user_id") ON DELETE CASCADE,
200 "type" smallint DEFAULT 0 NOT NULL,
201 "name" varchar(128) NOT NULL,
202 "data" long NOT NULL,
203 CONSTRAINT "searches_user_id_key" UNIQUE ("user_id", "type", "name")
204 );
205
206 CREATE SEQUENCE "searches_seq"
207 START WITH 1 INCREMENT BY 1 NOMAXVALUE;
208
209 CREATE TRIGGER "searches_seq_trig"
210 BEFORE INSERT ON "searches" FOR EACH ROW
211 BEGIN
212 :NEW."search_id" := "searches_seq".nextval;
213 END;
214 /
215
216 CREATE TABLE "system" (
217 "name" varchar(64) NOT NULL PRIMARY KEY,
218 "value" long
219 );
220
221 INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2015030800');