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