Mercurial > hg > rc1

comparison SQL/oracle.initial.sql @ 0:1e000243b222

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