Mercurial > hg > rc1

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