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 = true;
  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 = true;
 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 = true;
 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 = true;
 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 = true;
 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