lib,test: replace connection query() method with sync_exec{,_params}.
[mailshears.git] / test / sql / roundcube.sql
1 --
2 -- PostgreSQL database dump
3 --
4
5 SET statement_timeout = 0;
6 SET client_encoding = 'UTF8';
7 SET standard_conforming_strings = on;
8 SET check_function_bodies = false;
9 SET client_min_messages = warning;
10
11 --
12 -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
13 --
14
15 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
16
17
18 --
19 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
20 --
21
22 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
23
24
25 SET search_path = public, pg_catalog;
26
27 SET default_tablespace = '';
28
29 SET default_with_oids = false;
30
31 --
32 -- Name: cache; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
33 --
34
35 CREATE TABLE cache (
36 user_id integer NOT NULL,
37 cache_key character varying(128) DEFAULT ''::character varying NOT NULL,
38 created timestamp with time zone DEFAULT now() NOT NULL,
39 data text NOT NULL
40 );
41
42
43 ALTER TABLE public.cache OWNER TO postgres;
44
45 SET default_with_oids = false;
46
47 --
48 -- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
49 --
50
51 CREATE TABLE cache_index (
52 user_id integer NOT NULL,
53 mailbox character varying(255) NOT NULL,
54 changed timestamp with time zone DEFAULT now() NOT NULL,
55 valid smallint DEFAULT 0 NOT NULL,
56 data text NOT NULL
57 );
58
59
60 ALTER TABLE public.cache_index OWNER TO postgres;
61
62 --
63 -- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
64 --
65
66 CREATE TABLE cache_messages (
67 user_id integer NOT NULL,
68 mailbox character varying(255) NOT NULL,
69 uid integer NOT NULL,
70 changed timestamp with time zone DEFAULT now() NOT NULL,
71 data text NOT NULL,
72 flags integer DEFAULT 0 NOT NULL
73 );
74
75
76 ALTER TABLE public.cache_messages OWNER TO postgres;
77
78 --
79 -- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
80 --
81
82 CREATE TABLE cache_thread (
83 user_id integer NOT NULL,
84 mailbox character varying(255) NOT NULL,
85 changed timestamp with time zone DEFAULT now() NOT NULL,
86 data text NOT NULL
87 );
88
89
90 ALTER TABLE public.cache_thread OWNER TO postgres;
91
92 --
93 -- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres
94 --
95
96 CREATE SEQUENCE contact_ids
97 START WITH 1
98 INCREMENT BY 1
99 NO MINVALUE
100 NO MAXVALUE
101 CACHE 1;
102
103
104 ALTER TABLE public.contact_ids OWNER TO postgres;
105
106 --
107 -- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
108 --
109
110 CREATE TABLE contactgroupmembers (
111 contactgroup_id integer NOT NULL,
112 contact_id integer NOT NULL,
113 created timestamp with time zone DEFAULT now() NOT NULL
114 );
115
116
117 ALTER TABLE public.contactgroupmembers OWNER TO postgres;
118
119 --
120 -- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
121 --
122
123 CREATE TABLE contactgroups (
124 contactgroup_id integer DEFAULT nextval(('contactgroups_ids'::text)::regclass) NOT NULL,
125 user_id integer NOT NULL,
126 changed timestamp with time zone DEFAULT now() NOT NULL,
127 del smallint DEFAULT 0 NOT NULL,
128 name character varying(128) DEFAULT ''::character varying NOT NULL
129 );
130
131
132 ALTER TABLE public.contactgroups OWNER TO postgres;
133
134 --
135 -- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres
136 --
137
138 CREATE SEQUENCE contactgroups_ids
139 START WITH 1
140 INCREMENT BY 1
141 NO MINVALUE
142 NO MAXVALUE
143 CACHE 1;
144
145
146 ALTER TABLE public.contactgroups_ids OWNER TO postgres;
147
148 SET default_with_oids = false;
149
150 --
151 -- Name: contacts; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
152 --
153
154 CREATE TABLE contacts (
155 contact_id integer DEFAULT nextval(('contact_ids'::text)::regclass) NOT NULL,
156 user_id integer NOT NULL,
157 changed timestamp with time zone DEFAULT now() NOT NULL,
158 del smallint DEFAULT 0 NOT NULL,
159 name character varying(128) DEFAULT ''::character varying NOT NULL,
160 email text DEFAULT ''::character varying NOT NULL,
161 firstname character varying(128) DEFAULT ''::character varying NOT NULL,
162 surname character varying(128) DEFAULT ''::character varying NOT NULL,
163 vcard text,
164 words text
165 );
166
167
168 ALTER TABLE public.contacts OWNER TO postgres;
169
170 SET default_with_oids = false;
171
172 --
173 -- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
174 --
175
176 CREATE TABLE dictionary (
177 user_id integer,
178 language character varying(5) NOT NULL,
179 data text NOT NULL
180 );
181
182
183 ALTER TABLE public.dictionary OWNER TO postgres;
184
185 SET default_with_oids = false;
186
187 --
188 -- Name: identities; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
189 --
190
191 CREATE TABLE identities (
192 identity_id integer DEFAULT nextval(('identity_ids'::text)::regclass) NOT NULL,
193 user_id integer NOT NULL,
194 del smallint DEFAULT 0 NOT NULL,
195 standard smallint DEFAULT 0 NOT NULL,
196 name character varying(128) NOT NULL,
197 organization character varying(128),
198 email character varying(128) NOT NULL,
199 "reply-to" character varying(128),
200 bcc character varying(128),
201 signature text,
202 html_signature integer DEFAULT 0 NOT NULL,
203 changed timestamp with time zone DEFAULT now() NOT NULL
204 );
205
206
207 ALTER TABLE public.identities OWNER TO postgres;
208
209 --
210 -- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres
211 --
212
213 CREATE SEQUENCE identity_ids
214 START WITH 1
215 INCREMENT BY 1
216 NO MINVALUE
217 NO MAXVALUE
218 CACHE 1;
219
220
221 ALTER TABLE public.identity_ids OWNER TO postgres;
222
223 --
224 -- Name: search_ids; Type: SEQUENCE; Schema: public; Owner: postgres
225 --
226
227 CREATE SEQUENCE search_ids
228 START WITH 1
229 INCREMENT BY 1
230 NO MINVALUE
231 NO MAXVALUE
232 CACHE 1;
233
234
235 ALTER TABLE public.search_ids OWNER TO postgres;
236
237 SET default_with_oids = false;
238
239 --
240 -- Name: searches; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
241 --
242
243 CREATE TABLE searches (
244 search_id integer DEFAULT nextval(('search_ids'::text)::regclass) NOT NULL,
245 user_id integer NOT NULL,
246 type smallint DEFAULT 0 NOT NULL,
247 name character varying(128) NOT NULL,
248 data text NOT NULL
249 );
250
251
252 ALTER TABLE public.searches OWNER TO postgres;
253
254 SET default_with_oids = false;
255
256 --
257 -- Name: session; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
258 --
259
260 CREATE TABLE session (
261 sess_id character varying(128) DEFAULT ''::character varying NOT NULL,
262 created timestamp with time zone DEFAULT now() NOT NULL,
263 changed timestamp with time zone DEFAULT now() NOT NULL,
264 ip character varying(41) NOT NULL,
265 vars text NOT NULL
266 );
267
268
269 ALTER TABLE public.session OWNER TO postgres;
270
271 SET default_with_oids = false;
272
273 --
274 -- Name: system; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
275 --
276
277 CREATE TABLE system (
278 name character varying(64) NOT NULL,
279 value text
280 );
281
282
283 ALTER TABLE public.system OWNER TO postgres;
284
285 --
286 -- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres
287 --
288
289 CREATE SEQUENCE user_ids
290 START WITH 1
291 INCREMENT BY 1
292 NO MINVALUE
293 NO MAXVALUE
294 CACHE 1;
295
296
297 ALTER TABLE public.user_ids OWNER TO postgres;
298
299 SET default_with_oids = false;
300
301 --
302 -- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
303 --
304
305 CREATE TABLE users (
306 user_id integer DEFAULT nextval(('user_ids'::text)::regclass) NOT NULL,
307 username character varying(128) DEFAULT ''::character varying NOT NULL,
308 mail_host character varying(128) DEFAULT ''::character varying NOT NULL,
309 created timestamp with time zone DEFAULT now() NOT NULL,
310 last_login timestamp with time zone,
311 language character varying(5),
312 preferences text DEFAULT ''::text NOT NULL
313 );
314
315
316 ALTER TABLE public.users OWNER TO postgres;
317
318 --
319 -- Name: cache_index_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
320 --
321
322 ALTER TABLE ONLY cache_index
323 ADD CONSTRAINT cache_index_pkey PRIMARY KEY (user_id, mailbox);
324
325
326 --
327 -- Name: cache_messages_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
328 --
329
330 ALTER TABLE ONLY cache_messages
331 ADD CONSTRAINT cache_messages_pkey PRIMARY KEY (user_id, mailbox, uid);
332
333
334 --
335 -- Name: cache_thread_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
336 --
337
338 ALTER TABLE ONLY cache_thread
339 ADD CONSTRAINT cache_thread_pkey PRIMARY KEY (user_id, mailbox);
340
341
342 --
343 -- Name: contactgroupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
344 --
345
346 ALTER TABLE ONLY contactgroupmembers
347 ADD CONSTRAINT contactgroupmembers_pkey PRIMARY KEY (contactgroup_id, contact_id);
348
349
350 --
351 -- Name: contactgroups_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
352 --
353
354 ALTER TABLE ONLY contactgroups
355 ADD CONSTRAINT contactgroups_pkey PRIMARY KEY (contactgroup_id);
356
357
358 --
359 -- Name: contacts_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
360 --
361
362 ALTER TABLE ONLY contacts
363 ADD CONSTRAINT contacts_pkey PRIMARY KEY (contact_id);
364
365
366 --
367 -- Name: dictionary_user_id_language_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
368 --
369
370 ALTER TABLE ONLY dictionary
371 ADD CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, language);
372
373
374 --
375 -- Name: identities_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
376 --
377
378 ALTER TABLE ONLY identities
379 ADD CONSTRAINT identities_pkey PRIMARY KEY (identity_id);
380
381
382 --
383 -- Name: searches_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
384 --
385
386 ALTER TABLE ONLY searches
387 ADD CONSTRAINT searches_pkey PRIMARY KEY (search_id);
388
389
390 --
391 -- Name: searches_user_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
392 --
393
394 ALTER TABLE ONLY searches
395 ADD CONSTRAINT searches_user_id_key UNIQUE (user_id, type, name);
396
397
398 --
399 -- Name: session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
400 --
401
402 ALTER TABLE ONLY session
403 ADD CONSTRAINT session_pkey PRIMARY KEY (sess_id);
404
405
406 --
407 -- Name: system_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
408 --
409
410 ALTER TABLE ONLY system
411 ADD CONSTRAINT system_pkey PRIMARY KEY (name);
412
413
414 --
415 -- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
416 --
417
418 ALTER TABLE ONLY users
419 ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);
420
421
422 --
423 -- Name: users_username_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
424 --
425
426 ALTER TABLE ONLY users
427 ADD CONSTRAINT users_username_key UNIQUE (username, mail_host);
428
429
430 --
431 -- Name: cache_created_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
432 --
433
434 CREATE INDEX cache_created_idx ON cache USING btree (created);
435
436
437 --
438 -- Name: cache_index_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
439 --
440
441 CREATE INDEX cache_index_changed_idx ON cache_index USING btree (changed);
442
443
444 --
445 -- Name: cache_messages_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
446 --
447
448 CREATE INDEX cache_messages_changed_idx ON cache_messages USING btree (changed);
449
450
451 --
452 -- Name: cache_thread_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
453 --
454
455 CREATE INDEX cache_thread_changed_idx ON cache_thread USING btree (changed);
456
457
458 --
459 -- Name: cache_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
460 --
461
462 CREATE INDEX cache_user_id_idx ON cache USING btree (user_id, cache_key);
463
464
465 --
466 -- Name: contactgroupmembers_contact_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
467 --
468
469 CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers USING btree (contact_id);
470
471
472 --
473 -- Name: contactgroups_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
474 --
475
476 CREATE INDEX contactgroups_user_id_idx ON contactgroups USING btree (user_id, del);
477
478
479 --
480 -- Name: contacts_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
481 --
482
483 CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del);
484
485
486 --
487 -- Name: identities_email_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
488 --
489
490 CREATE INDEX identities_email_idx ON identities USING btree (email, del);
491
492
493 --
494 -- Name: identities_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
495 --
496
497 CREATE INDEX identities_user_id_idx ON identities USING btree (user_id, del);
498
499
500 --
501 -- Name: session_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
502 --
503
504 CREATE INDEX session_changed_idx ON session USING btree (changed);
505
506
507 --
508 -- Name: cache_index_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
509 --
510
511 ALTER TABLE ONLY cache_index
512 ADD CONSTRAINT cache_index_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
513
514
515 --
516 -- Name: cache_messages_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
517 --
518
519 ALTER TABLE ONLY cache_messages
520 ADD CONSTRAINT cache_messages_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
521
522
523 --
524 -- Name: cache_thread_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
525 --
526
527 ALTER TABLE ONLY cache_thread
528 ADD CONSTRAINT cache_thread_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
529
530
531 --
532 -- Name: cache_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
533 --
534
535 ALTER TABLE ONLY cache
536 ADD CONSTRAINT cache_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
537
538
539 --
540 -- Name: contactgroupmembers_contact_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
541 --
542
543 ALTER TABLE ONLY contactgroupmembers
544 ADD CONSTRAINT contactgroupmembers_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES contacts(contact_id) ON UPDATE CASCADE ON DELETE CASCADE;
545
546
547 --
548 -- Name: contactgroupmembers_contactgroup_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
549 --
550
551 ALTER TABLE ONLY contactgroupmembers
552 ADD CONSTRAINT contactgroupmembers_contactgroup_id_fkey FOREIGN KEY (contactgroup_id) REFERENCES contactgroups(contactgroup_id) ON UPDATE CASCADE ON DELETE CASCADE;
553
554
555 --
556 -- Name: contactgroups_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
557 --
558
559 ALTER TABLE ONLY contactgroups
560 ADD CONSTRAINT contactgroups_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
561
562
563 --
564 -- Name: contacts_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
565 --
566
567 ALTER TABLE ONLY contacts
568 ADD CONSTRAINT contacts_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
569
570
571 --
572 -- Name: dictionary_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
573 --
574
575 ALTER TABLE ONLY dictionary
576 ADD CONSTRAINT dictionary_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
577
578
579 --
580 -- Name: identities_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
581 --
582
583 ALTER TABLE ONLY identities
584 ADD CONSTRAINT identities_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
585
586
587 --
588 -- Name: searches_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
589 --
590
591 ALTER TABLE ONLY searches
592 ADD CONSTRAINT searches_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
593
594
595 --
596 -- Name: public; Type: ACL; Schema: -; Owner: postgres
597 --
598
599 REVOKE ALL ON SCHEMA public FROM PUBLIC;
600 REVOKE ALL ON SCHEMA public FROM postgres;
601 GRANT ALL ON SCHEMA public TO postgres;
602 GRANT ALL ON SCHEMA public TO PUBLIC;
603
604
605 --
606 -- PostgreSQL database dump complete
607 --
608