X-Git-Url: https://gitweb.michael.orlitzky.com/?a=blobdiff_plain;f=test%2Fsql%2Froundcube.sql;fp=test%2Fsql%2Froundcube.sql;h=36e0a6c8def9e3e73a9d8d6784b03e938036504c;hb=0a0b9b0feb369d53af4ca7ffdd183db786f1127d;hp=0000000000000000000000000000000000000000;hpb=365d681f1ef45bd3f5f2c9dded07108bdd75a1ff;p=mailshears.git diff --git a/test/sql/roundcube.sql b/test/sql/roundcube.sql new file mode 100644 index 0000000..36e0a6c --- /dev/null +++ b/test/sql/roundcube.sql @@ -0,0 +1,608 @@ +-- +-- PostgreSQL database dump +-- + +SET statement_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SET check_function_bodies = false; +SET client_min_messages = warning; + +-- +-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: +-- + +CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; + + +-- +-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: +-- + +COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; + + +SET search_path = public, pg_catalog; + +SET default_tablespace = ''; + +SET default_with_oids = true; + +-- +-- Name: cache; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE cache ( + user_id integer NOT NULL, + cache_key character varying(128) DEFAULT ''::character varying NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.cache OWNER TO postgres; + +SET default_with_oids = false; + +-- +-- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE cache_index ( + user_id integer NOT NULL, + mailbox character varying(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + valid smallint DEFAULT 0 NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.cache_index OWNER TO postgres; + +-- +-- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE cache_messages ( + user_id integer NOT NULL, + mailbox character varying(255) NOT NULL, + uid integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + flags integer DEFAULT 0 NOT NULL +); + + +ALTER TABLE public.cache_messages OWNER TO postgres; + +-- +-- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE cache_thread ( + user_id integer NOT NULL, + mailbox character varying(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.cache_thread OWNER TO postgres; + +-- +-- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE contact_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.contact_ids OWNER TO postgres; + +-- +-- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE contactgroupmembers ( + contactgroup_id integer NOT NULL, + contact_id integer NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE public.contactgroupmembers OWNER TO postgres; + +-- +-- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE contactgroups ( + contactgroup_id integer DEFAULT nextval(('contactgroups_ids'::text)::regclass) NOT NULL, + user_id integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + del smallint DEFAULT 0 NOT NULL, + name character varying(128) DEFAULT ''::character varying NOT NULL +); + + +ALTER TABLE public.contactgroups OWNER TO postgres; + +-- +-- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE contactgroups_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.contactgroups_ids OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: contacts; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE contacts ( + contact_id integer DEFAULT nextval(('contact_ids'::text)::regclass) NOT NULL, + user_id integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + del smallint DEFAULT 0 NOT NULL, + name character varying(128) DEFAULT ''::character varying NOT NULL, + email text DEFAULT ''::character varying NOT NULL, + firstname character varying(128) DEFAULT ''::character varying NOT NULL, + surname character varying(128) DEFAULT ''::character varying NOT NULL, + vcard text, + words text +); + + +ALTER TABLE public.contacts OWNER TO postgres; + +SET default_with_oids = false; + +-- +-- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE dictionary ( + user_id integer, + language character varying(5) NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.dictionary OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: identities; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE identities ( + identity_id integer DEFAULT nextval(('identity_ids'::text)::regclass) NOT NULL, + user_id integer NOT NULL, + del smallint DEFAULT 0 NOT NULL, + standard smallint DEFAULT 0 NOT NULL, + name character varying(128) NOT NULL, + organization character varying(128), + email character varying(128) NOT NULL, + "reply-to" character varying(128), + bcc character varying(128), + signature text, + html_signature integer DEFAULT 0 NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE public.identities OWNER TO postgres; + +-- +-- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE identity_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.identity_ids OWNER TO postgres; + +-- +-- Name: search_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE search_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.search_ids OWNER TO postgres; + +SET default_with_oids = false; + +-- +-- Name: searches; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE searches ( + search_id integer DEFAULT nextval(('search_ids'::text)::regclass) NOT NULL, + user_id integer NOT NULL, + type smallint DEFAULT 0 NOT NULL, + name character varying(128) NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.searches OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: session; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE session ( + sess_id character varying(128) DEFAULT ''::character varying NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + ip character varying(41) NOT NULL, + vars text NOT NULL +); + + +ALTER TABLE public.session OWNER TO postgres; + +SET default_with_oids = false; + +-- +-- Name: system; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE system ( + name character varying(64) NOT NULL, + value text +); + + +ALTER TABLE public.system OWNER TO postgres; + +-- +-- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE user_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.user_ids OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE users ( + user_id integer DEFAULT nextval(('user_ids'::text)::regclass) NOT NULL, + username character varying(128) DEFAULT ''::character varying NOT NULL, + mail_host character varying(128) DEFAULT ''::character varying NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL, + last_login timestamp with time zone, + language character varying(5), + preferences text DEFAULT ''::text NOT NULL +); + + +ALTER TABLE public.users OWNER TO postgres; + +-- +-- Name: cache_index_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY cache_index + ADD CONSTRAINT cache_index_pkey PRIMARY KEY (user_id, mailbox); + + +-- +-- Name: cache_messages_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY cache_messages + ADD CONSTRAINT cache_messages_pkey PRIMARY KEY (user_id, mailbox, uid); + + +-- +-- Name: cache_thread_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY cache_thread + ADD CONSTRAINT cache_thread_pkey PRIMARY KEY (user_id, mailbox); + + +-- +-- Name: contactgroupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY contactgroupmembers + ADD CONSTRAINT contactgroupmembers_pkey PRIMARY KEY (contactgroup_id, contact_id); + + +-- +-- Name: contactgroups_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY contactgroups + ADD CONSTRAINT contactgroups_pkey PRIMARY KEY (contactgroup_id); + + +-- +-- Name: contacts_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY contacts + ADD CONSTRAINT contacts_pkey PRIMARY KEY (contact_id); + + +-- +-- Name: dictionary_user_id_language_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY dictionary + ADD CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, language); + + +-- +-- Name: identities_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY identities + ADD CONSTRAINT identities_pkey PRIMARY KEY (identity_id); + + +-- +-- Name: searches_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY searches + ADD CONSTRAINT searches_pkey PRIMARY KEY (search_id); + + +-- +-- Name: searches_user_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY searches + ADD CONSTRAINT searches_user_id_key UNIQUE (user_id, type, name); + + +-- +-- Name: session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY session + ADD CONSTRAINT session_pkey PRIMARY KEY (sess_id); + + +-- +-- Name: system_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY system + ADD CONSTRAINT system_pkey PRIMARY KEY (name); + + +-- +-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY users + ADD CONSTRAINT users_pkey PRIMARY KEY (user_id); + + +-- +-- Name: users_username_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY users + ADD CONSTRAINT users_username_key UNIQUE (username, mail_host); + + +-- +-- Name: cache_created_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_created_idx ON cache USING btree (created); + + +-- +-- Name: cache_index_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_index_changed_idx ON cache_index USING btree (changed); + + +-- +-- Name: cache_messages_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_messages_changed_idx ON cache_messages USING btree (changed); + + +-- +-- Name: cache_thread_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_thread_changed_idx ON cache_thread USING btree (changed); + + +-- +-- Name: cache_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_user_id_idx ON cache USING btree (user_id, cache_key); + + +-- +-- Name: contactgroupmembers_contact_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers USING btree (contact_id); + + +-- +-- Name: contactgroups_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX contactgroups_user_id_idx ON contactgroups USING btree (user_id, del); + + +-- +-- Name: contacts_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del); + + +-- +-- Name: identities_email_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX identities_email_idx ON identities USING btree (email, del); + + +-- +-- Name: identities_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX identities_user_id_idx ON identities USING btree (user_id, del); + + +-- +-- Name: session_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX session_changed_idx ON session USING btree (changed); + + +-- +-- Name: cache_index_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY cache_index + ADD CONSTRAINT cache_index_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: cache_messages_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY cache_messages + ADD CONSTRAINT cache_messages_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: cache_thread_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY cache_thread + ADD CONSTRAINT cache_thread_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: cache_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY cache + ADD CONSTRAINT cache_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: contactgroupmembers_contact_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY contactgroupmembers + ADD CONSTRAINT contactgroupmembers_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES contacts(contact_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: contactgroupmembers_contactgroup_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY contactgroupmembers + ADD CONSTRAINT contactgroupmembers_contactgroup_id_fkey FOREIGN KEY (contactgroup_id) REFERENCES contactgroups(contactgroup_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: contactgroups_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY contactgroups + ADD CONSTRAINT contactgroups_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: contacts_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY contacts + ADD CONSTRAINT contacts_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: dictionary_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY dictionary + ADD CONSTRAINT dictionary_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: identities_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY identities + ADD CONSTRAINT identities_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: searches_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY searches + ADD CONSTRAINT searches_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: public; Type: ACL; Schema: -; Owner: postgres +-- + +REVOKE ALL ON SCHEMA public FROM PUBLIC; +REVOKE ALL ON SCHEMA public FROM postgres; +GRANT ALL ON SCHEMA public TO postgres; +GRANT ALL ON SCHEMA public TO PUBLIC; + + +-- +-- PostgreSQL database dump complete +-- +