2 -- PostgreSQL database dump
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
;
12 -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
15 CREATE EXTENSION
IF NOT EXISTS plpgsql
WITH SCHEMA pg_catalog
;
19 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
22 COMMENT ON EXTENSION plpgsql
IS 'PL/pgSQL procedural language';
25 SET search_path
= public, pg_catalog
;
27 SET default_tablespace
= '';
29 SET default_with_oids
= false;
32 -- Name: cache; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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,
43 ALTER TABLE public.
cache OWNER TO postgres
;
45 SET default_with_oids
= false;
48 -- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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,
60 ALTER TABLE public.cache_index
OWNER TO postgres
;
63 -- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
66 CREATE TABLE cache_messages (
67 user_id
integer NOT NULL,
68 mailbox
character varying(255) NOT NULL,
70 changed
timestamp with time zone DEFAULT now() NOT NULL,
72 flags
integer DEFAULT 0 NOT NULL
76 ALTER TABLE public.cache_messages
OWNER TO postgres
;
79 -- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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,
90 ALTER TABLE public.cache_thread
OWNER TO postgres
;
93 -- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres
96 CREATE SEQUENCE contact_ids
104 ALTER TABLE public.contact_ids
OWNER TO postgres
;
107 -- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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
117 ALTER TABLE public.contactgroupmembers
OWNER TO postgres
;
120 -- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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
132 ALTER TABLE public.contactgroups
OWNER TO postgres
;
135 -- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres
138 CREATE SEQUENCE contactgroups_ids
146 ALTER TABLE public.contactgroups_ids
OWNER TO postgres
;
148 SET default_with_oids
= false;
151 -- Name: contacts; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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,
168 ALTER TABLE public.contacts
OWNER TO postgres
;
170 SET default_with_oids
= false;
173 -- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
176 CREATE TABLE dictionary (
178 language character varying(5) NOT NULL,
183 ALTER TABLE public.
dictionary OWNER TO postgres
;
185 SET default_with_oids
= false;
188 -- Name: identities; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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),
202 html_signature
integer DEFAULT 0 NOT NULL,
203 changed
timestamp with time zone DEFAULT now() NOT NULL
207 ALTER TABLE public.identities
OWNER TO postgres
;
210 -- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres
213 CREATE SEQUENCE identity_ids
221 ALTER TABLE public.identity_ids
OWNER TO postgres
;
224 -- Name: search_ids; Type: SEQUENCE; Schema: public; Owner: postgres
227 CREATE SEQUENCE search_ids
235 ALTER TABLE public.search_ids
OWNER TO postgres
;
237 SET default_with_oids
= false;
240 -- Name: searches; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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,
252 ALTER TABLE public.searches
OWNER TO postgres
;
254 SET default_with_oids
= false;
257 -- Name: session; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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,
269 ALTER TABLE public.
session OWNER TO postgres
;
271 SET default_with_oids
= false;
274 -- Name: system; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
277 CREATE TABLE system (
278 name character varying(64) NOT NULL,
283 ALTER TABLE public.
system OWNER TO postgres
;
286 -- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres
289 CREATE SEQUENCE user_ids
297 ALTER TABLE public.user_ids
OWNER TO postgres
;
299 SET default_with_oids
= false;
302 -- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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
316 ALTER TABLE public.users
OWNER TO postgres
;
319 -- Name: cache_index_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
322 ALTER TABLE ONLY cache_index
323 ADD CONSTRAINT cache_index_pkey
PRIMARY KEY (user_id
, mailbox
);
327 -- Name: cache_messages_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
330 ALTER TABLE ONLY cache_messages
331 ADD CONSTRAINT cache_messages_pkey
PRIMARY KEY (user_id
, mailbox
, uid
);
335 -- Name: cache_thread_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
338 ALTER TABLE ONLY cache_thread
339 ADD CONSTRAINT cache_thread_pkey
PRIMARY KEY (user_id
, mailbox
);
343 -- Name: contactgroupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
346 ALTER TABLE ONLY contactgroupmembers
347 ADD CONSTRAINT contactgroupmembers_pkey
PRIMARY KEY (contactgroup_id
, contact_id
);
351 -- Name: contactgroups_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
354 ALTER TABLE ONLY contactgroups
355 ADD CONSTRAINT contactgroups_pkey
PRIMARY KEY (contactgroup_id
);
359 -- Name: contacts_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
362 ALTER TABLE ONLY contacts
363 ADD CONSTRAINT contacts_pkey
PRIMARY KEY (contact_id
);
367 -- Name: dictionary_user_id_language_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
370 ALTER TABLE ONLY dictionary
371 ADD CONSTRAINT dictionary_user_id_language_key
UNIQUE (user_id
, language);
375 -- Name: identities_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
378 ALTER TABLE ONLY identities
379 ADD CONSTRAINT identities_pkey
PRIMARY KEY (identity_id
);
383 -- Name: searches_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
386 ALTER TABLE ONLY searches
387 ADD CONSTRAINT searches_pkey
PRIMARY KEY (search_id
);
391 -- Name: searches_user_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
394 ALTER TABLE ONLY searches
395 ADD CONSTRAINT searches_user_id_key
UNIQUE (user_id
, type, name);
399 -- Name: session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
402 ALTER TABLE ONLY session
403 ADD CONSTRAINT session_pkey
PRIMARY KEY (sess_id
);
407 -- Name: system_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
410 ALTER TABLE ONLY system
411 ADD CONSTRAINT system_pkey
PRIMARY KEY (name);
415 -- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
418 ALTER TABLE ONLY users
419 ADD CONSTRAINT users_pkey
PRIMARY KEY (user_id
);
423 -- Name: users_username_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
426 ALTER TABLE ONLY users
427 ADD CONSTRAINT users_username_key
UNIQUE (username
, mail_host
);
431 -- Name: cache_created_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
434 CREATE INDEX cache_created_idx
ON cache USING btree (created
);
438 -- Name: cache_index_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
441 CREATE INDEX cache_index_changed_idx
ON cache_index
USING btree (changed
);
445 -- Name: cache_messages_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
448 CREATE INDEX cache_messages_changed_idx
ON cache_messages
USING btree (changed
);
452 -- Name: cache_thread_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
455 CREATE INDEX cache_thread_changed_idx
ON cache_thread
USING btree (changed
);
459 -- Name: cache_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
462 CREATE INDEX cache_user_id_idx
ON cache USING btree (user_id
, cache_key
);
466 -- Name: contactgroupmembers_contact_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
469 CREATE INDEX contactgroupmembers_contact_id_idx
ON contactgroupmembers
USING btree (contact_id
);
473 -- Name: contactgroups_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
476 CREATE INDEX contactgroups_user_id_idx
ON contactgroups
USING btree (user_id
, del
);
480 -- Name: contacts_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
483 CREATE INDEX contacts_user_id_idx
ON contacts
USING btree (user_id
, del
);
487 -- Name: identities_email_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
490 CREATE INDEX identities_email_idx
ON identities
USING btree (email
, del
);
494 -- Name: identities_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
497 CREATE INDEX identities_user_id_idx
ON identities
USING btree (user_id
, del
);
501 -- Name: session_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
504 CREATE INDEX session_changed_idx
ON session USING btree (changed
);
508 -- Name: cache_index_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
516 -- Name: cache_messages_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
524 -- Name: cache_thread_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
532 -- Name: cache_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
540 -- Name: contactgroupmembers_contact_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
548 -- Name: contactgroupmembers_contactgroup_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
556 -- Name: contactgroups_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
564 -- Name: contacts_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
572 -- Name: dictionary_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
580 -- Name: identities_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
588 -- Name: searches_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
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;
596 -- Name: public; Type: ACL; Schema: -; Owner: postgres
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;
606 -- PostgreSQL database dump complete