commit | author | age
|
e019f2
|
1 |
-- Roundcube Webmail initial database structure |
798ad5
|
2 |
|
977a29
|
3 |
-- |
399db1
|
4 |
-- Sequence "users_seq" |
AM |
5 |
-- Name: users_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
977a29
|
6 |
-- |
T |
7 |
|
399db1
|
8 |
CREATE SEQUENCE users_seq |
977a29
|
9 |
INCREMENT BY 1 |
T |
10 |
NO MAXVALUE |
|
11 |
NO MINVALUE |
|
12 |
CACHE 1; |
|
13 |
|
f5dc2a
|
14 |
-- |
1cded8
|
15 |
-- Table "users" |
f5dc2a
|
16 |
-- Name: users; Type: TABLE; Schema: public; Owner: postgres |
S |
17 |
-- |
|
18 |
|
|
19 |
CREATE TABLE users ( |
399db1
|
20 |
user_id integer DEFAULT nextval('users_seq'::text) PRIMARY KEY, |
22d6b5
|
21 |
username varchar(128) DEFAULT '' NOT NULL, |
A |
22 |
mail_host varchar(128) DEFAULT '' NOT NULL, |
f5dc2a
|
23 |
created timestamp with time zone DEFAULT now() NOT NULL, |
e2402e
|
24 |
last_login timestamp with time zone DEFAULT NULL, |
22d6b5
|
25 |
"language" varchar(5), |
ace511
|
26 |
preferences text DEFAULT ''::text NOT NULL, |
8381ec
|
27 |
CONSTRAINT users_username_key UNIQUE (username, mail_host) |
f5dc2a
|
28 |
); |
S |
29 |
|
|
30 |
|
|
31 |
-- |
1cded8
|
32 |
-- Table "session" |
T |
33 |
-- Name: session; Type: TABLE; Schema: public; Owner: postgres |
|
34 |
-- |
|
35 |
|
|
36 |
CREATE TABLE "session" ( |
b8ae0e
|
37 |
sess_id varchar(128) DEFAULT '' PRIMARY KEY, |
1cded8
|
38 |
created timestamp with time zone DEFAULT now() NOT NULL, |
T |
39 |
changed timestamp with time zone DEFAULT now() NOT NULL, |
22d6b5
|
40 |
ip varchar(41) NOT NULL, |
1cded8
|
41 |
vars text NOT NULL |
T |
42 |
); |
|
43 |
|
3e48d2
|
44 |
CREATE INDEX session_changed_idx ON session (changed); |
1cded8
|
45 |
|
b59474
|
46 |
|
T |
47 |
-- |
399db1
|
48 |
-- Sequence "identities_seq" |
AM |
49 |
-- Name: identities_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
b59474
|
50 |
-- |
T |
51 |
|
399db1
|
52 |
CREATE SEQUENCE identities_seq |
b59474
|
53 |
START WITH 1 |
T |
54 |
INCREMENT BY 1 |
|
55 |
NO MAXVALUE |
|
56 |
NO MINVALUE |
|
57 |
CACHE 1; |
1cded8
|
58 |
|
T |
59 |
-- |
|
60 |
-- Table "identities" |
|
61 |
-- Name: identities; Type: TABLE; Schema: public; Owner: postgres |
|
62 |
-- |
|
63 |
|
|
64 |
CREATE TABLE identities ( |
399db1
|
65 |
identity_id integer DEFAULT nextval('identities_seq'::text) PRIMARY KEY, |
22d6b5
|
66 |
user_id integer NOT NULL |
80152b
|
67 |
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
a35062
|
68 |
changed timestamp with time zone DEFAULT now() NOT NULL, |
a493ea
|
69 |
del smallint DEFAULT 0 NOT NULL, |
A |
70 |
standard smallint DEFAULT 0 NOT NULL, |
22d6b5
|
71 |
name varchar(128) NOT NULL, |
A |
72 |
organization varchar(128), |
|
73 |
email varchar(128) NOT NULL, |
|
74 |
"reply-to" varchar(128), |
|
75 |
bcc varchar(128), |
a0109c
|
76 |
signature text, |
S |
77 |
html_signature integer DEFAULT 0 NOT NULL |
1cded8
|
78 |
); |
T |
79 |
|
94fe9c
|
80 |
CREATE INDEX identities_user_id_idx ON identities (user_id, del); |
565c47
|
81 |
CREATE INDEX identities_email_idx ON identities (email, del); |
b59474
|
82 |
|
T |
83 |
|
|
84 |
-- |
399db1
|
85 |
-- Sequence "contacts_seq" |
AM |
86 |
-- Name: contacts_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
b59474
|
87 |
-- |
T |
88 |
|
399db1
|
89 |
CREATE SEQUENCE contacts_seq |
b59474
|
90 |
START WITH 1 |
T |
91 |
INCREMENT BY 1 |
|
92 |
NO MAXVALUE |
|
93 |
NO MINVALUE |
|
94 |
CACHE 1; |
1cded8
|
95 |
|
T |
96 |
-- |
|
97 |
-- Table "contacts" |
|
98 |
-- Name: contacts; Type: TABLE; Schema: public; Owner: postgres |
|
99 |
-- |
|
100 |
|
|
101 |
CREATE TABLE contacts ( |
399db1
|
102 |
contact_id integer DEFAULT nextval('contacts_seq'::text) PRIMARY KEY, |
22d6b5
|
103 |
user_id integer NOT NULL |
3e2637
|
104 |
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
1cded8
|
105 |
changed timestamp with time zone DEFAULT now() NOT NULL, |
a493ea
|
106 |
del smallint DEFAULT 0 NOT NULL, |
22d6b5
|
107 |
name varchar(128) DEFAULT '' NOT NULL, |
48be8f
|
108 |
email text DEFAULT '' NOT NULL, |
22d6b5
|
109 |
firstname varchar(128) DEFAULT '' NOT NULL, |
A |
110 |
surname varchar(128) DEFAULT '' NOT NULL, |
3e2637
|
111 |
vcard text, |
T |
112 |
words text |
1cded8
|
113 |
); |
T |
114 |
|
48be8f
|
115 |
CREATE INDEX contacts_user_id_idx ON contacts (user_id, del); |
22d6b5
|
116 |
|
A |
117 |
-- |
399db1
|
118 |
-- Sequence "contactgroups_seq" |
AM |
119 |
-- Name: contactgroups_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
22d6b5
|
120 |
-- |
A |
121 |
|
399db1
|
122 |
CREATE SEQUENCE contactgroups_seq |
22d6b5
|
123 |
INCREMENT BY 1 |
A |
124 |
NO MAXVALUE |
|
125 |
NO MINVALUE |
|
126 |
CACHE 1; |
|
127 |
|
|
128 |
-- |
|
129 |
-- Table "contactgroups" |
|
130 |
-- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres |
|
131 |
-- |
|
132 |
|
|
133 |
CREATE TABLE contactgroups ( |
399db1
|
134 |
contactgroup_id integer DEFAULT nextval('contactgroups_seq'::text) PRIMARY KEY, |
22d6b5
|
135 |
user_id integer NOT NULL |
A |
136 |
REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
|
137 |
changed timestamp with time zone DEFAULT now() NOT NULL, |
|
138 |
del smallint NOT NULL DEFAULT 0, |
|
139 |
name varchar(128) NOT NULL DEFAULT '' |
|
140 |
); |
|
141 |
|
|
142 |
CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del); |
|
143 |
|
|
144 |
-- |
|
145 |
-- Table "contactgroupmembers" |
|
146 |
-- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres |
|
147 |
-- |
ace511
|
148 |
|
22d6b5
|
149 |
CREATE TABLE contactgroupmembers ( |
A |
150 |
contactgroup_id integer NOT NULL |
|
151 |
REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE, |
|
152 |
contact_id integer NOT NULL |
|
153 |
REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE, |
|
154 |
created timestamp with time zone DEFAULT now() NOT NULL, |
|
155 |
PRIMARY KEY (contactgroup_id, contact_id) |
|
156 |
); |
1cded8
|
157 |
|
3a5476
|
158 |
CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id); |
A |
159 |
|
1cded8
|
160 |
-- |
T |
161 |
-- Table "cache" |
|
162 |
-- Name: cache; Type: TABLE; Schema: public; Owner: postgres |
|
163 |
-- |
|
164 |
|
|
165 |
CREATE TABLE "cache" ( |
22d6b5
|
166 |
user_id integer NOT NULL |
60b6d7
|
167 |
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
22d6b5
|
168 |
cache_key varchar(128) DEFAULT '' NOT NULL, |
1cded8
|
169 |
created timestamp with time zone DEFAULT now() NOT NULL, |
60b6d7
|
170 |
expires timestamp with time zone DEFAULT NULL, |
1cded8
|
171 |
data text NOT NULL |
T |
172 |
); |
|
173 |
|
edc63c
|
174 |
CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key); |
60b6d7
|
175 |
CREATE INDEX cache_expires_idx ON "cache" (expires); |
1cded8
|
176 |
|
T |
177 |
-- |
50abd5
|
178 |
-- Table "cache_shared" |
AM |
179 |
-- Name: cache_shared; Type: TABLE; Schema: public; Owner: postgres |
|
180 |
-- |
|
181 |
|
|
182 |
CREATE TABLE "cache_shared" ( |
|
183 |
cache_key varchar(255) NOT NULL, |
|
184 |
created timestamp with time zone DEFAULT now() NOT NULL, |
60b6d7
|
185 |
expires timestamp with time zone DEFAULT NULL, |
50abd5
|
186 |
data text NOT NULL |
AM |
187 |
); |
|
188 |
|
|
189 |
CREATE INDEX cache_shared_cache_key_idx ON "cache_shared" (cache_key); |
60b6d7
|
190 |
CREATE INDEX cache_shared_expires_idx ON "cache_shared" (expires); |
50abd5
|
191 |
|
AM |
192 |
-- |
80152b
|
193 |
-- Table "cache_index" |
A |
194 |
-- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres |
b59474
|
195 |
-- |
T |
196 |
|
80152b
|
197 |
CREATE TABLE cache_index ( |
22d6b5
|
198 |
user_id integer NOT NULL |
60b6d7
|
199 |
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
80152b
|
200 |
mailbox varchar(255) NOT NULL, |
60b6d7
|
201 |
expires timestamp with time zone DEFAULT NULL, |
609d39
|
202 |
valid smallint NOT NULL DEFAULT 0, |
80152b
|
203 |
data text NOT NULL, |
A |
204 |
PRIMARY KEY (user_id, mailbox) |
1cded8
|
205 |
); |
T |
206 |
|
60b6d7
|
207 |
CREATE INDEX cache_index_expires_idx ON cache_index (expires); |
80152b
|
208 |
|
A |
209 |
-- |
|
210 |
-- Table "cache_thread" |
|
211 |
-- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres |
|
212 |
-- |
|
213 |
|
|
214 |
CREATE TABLE cache_thread ( |
|
215 |
user_id integer NOT NULL |
60b6d7
|
216 |
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
80152b
|
217 |
mailbox varchar(255) NOT NULL, |
60b6d7
|
218 |
expires timestamp with time zone DEFAULT NULL, |
80152b
|
219 |
data text NOT NULL, |
A |
220 |
PRIMARY KEY (user_id, mailbox) |
|
221 |
); |
|
222 |
|
60b6d7
|
223 |
CREATE INDEX cache_thread_expires_idx ON cache_thread (expires); |
80152b
|
224 |
|
A |
225 |
-- |
|
226 |
-- Table "cache_messages" |
|
227 |
-- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres |
|
228 |
-- |
|
229 |
|
|
230 |
CREATE TABLE cache_messages ( |
|
231 |
user_id integer NOT NULL |
60b6d7
|
232 |
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
80152b
|
233 |
mailbox varchar(255) NOT NULL, |
A |
234 |
uid integer NOT NULL, |
60b6d7
|
235 |
expires timestamp with time zone DEFAULT NULL, |
80152b
|
236 |
data text NOT NULL, |
609d39
|
237 |
flags integer NOT NULL DEFAULT 0, |
80152b
|
238 |
PRIMARY KEY (user_id, mailbox, uid) |
A |
239 |
); |
|
240 |
|
60b6d7
|
241 |
CREATE INDEX cache_messages_expires_idx ON cache_messages (expires); |
66df08
|
242 |
|
A |
243 |
-- |
|
244 |
-- Table "dictionary" |
|
245 |
-- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres |
|
246 |
-- |
|
247 |
|
|
248 |
CREATE TABLE dictionary ( |
|
249 |
user_id integer DEFAULT NULL |
60b6d7
|
250 |
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
66df08
|
251 |
"language" varchar(5) NOT NULL, |
A |
252 |
data text NOT NULL, |
|
253 |
CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language") |
|
254 |
); |
f8e48d
|
255 |
|
A |
256 |
-- |
399db1
|
257 |
-- Sequence "searches_seq" |
AM |
258 |
-- Name: searches_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
f8e48d
|
259 |
-- |
A |
260 |
|
399db1
|
261 |
CREATE SEQUENCE searches_seq |
f8e48d
|
262 |
INCREMENT BY 1 |
A |
263 |
NO MAXVALUE |
|
264 |
NO MINVALUE |
|
265 |
CACHE 1; |
|
266 |
|
|
267 |
-- |
|
268 |
-- Table "searches" |
|
269 |
-- Name: searches; Type: TABLE; Schema: public; Owner: postgres |
|
270 |
-- |
|
271 |
|
|
272 |
CREATE TABLE searches ( |
399db1
|
273 |
search_id integer DEFAULT nextval('searches_seq'::text) PRIMARY KEY, |
f8e48d
|
274 |
user_id integer NOT NULL |
A |
275 |
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
|
276 |
"type" smallint DEFAULT 0 NOT NULL, |
|
277 |
name varchar(128) NOT NULL, |
|
278 |
data text NOT NULL, |
|
279 |
CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) |
|
280 |
); |
b7e7c8
|
281 |
|
AM |
282 |
|
|
283 |
-- |
|
284 |
-- Table "system" |
|
285 |
-- Name: system; Type: TABLE; Schema: public; Owner: postgres |
|
286 |
-- |
|
287 |
|
|
288 |
CREATE TABLE "system" ( |
|
289 |
name varchar(64) NOT NULL PRIMARY KEY, |
|
290 |
value text |
|
291 |
); |
|
292 |
|
6d876a
|
293 |
INSERT INTO system (name, value) VALUES ('roundcube-version', '2015030800'); |