|
0
|
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');
|
