X-Git-Url: http://gitweb.michael.orlitzky.com/?p=mailshears.git;a=blobdiff_plain;f=test%2Fsql%2Fpostfixadmin.sql;fp=test%2Fsql%2Fpostfixadmin.sql;h=f139a4f77680e61f7a9e69528c1b4761ad524405;hp=0000000000000000000000000000000000000000;hb=0a0b9b0feb369d53af4ca7ffdd183db786f1127d;hpb=365d681f1ef45bd3f5f2c9dded07108bdd75a1ff diff --git a/test/sql/postfixadmin.sql b/test/sql/postfixadmin.sql new file mode 100644 index 0000000..f139a4f --- /dev/null +++ b/test/sql/postfixadmin.sql @@ -0,0 +1,737 @@ +-- +-- 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; + +-- +-- Name: merge_quota(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION merge_quota() RETURNS trigger + LANGUAGE plpgsql + AS $$ + BEGIN + UPDATE quota SET current = NEW.current + current WHERE username = NEW.username AND path = NEW.path; + IF found THEN + RETURN NULL; + ELSE + RETURN NEW; + END IF; + END; + $$; + + +ALTER FUNCTION public.merge_quota() OWNER TO postgres; + +-- +-- Name: merge_quota2(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION merge_quota2() RETURNS trigger + LANGUAGE plpgsql + AS $$ + BEGIN + IF NEW.messages < 0 OR NEW.messages IS NULL THEN + -- ugly kludge: we came here from this function, really do try to insert + IF NEW.messages IS NULL THEN + NEW.messages = 0; + ELSE + NEW.messages = -NEW.messages; + END IF; + return NEW; + END IF; + + LOOP + UPDATE quota2 SET bytes = bytes + NEW.bytes, + messages = messages + NEW.messages + WHERE username = NEW.username; + IF found THEN + RETURN NULL; + END IF; + + BEGIN + IF NEW.messages = 0 THEN + INSERT INTO quota2 (bytes, messages, username) VALUES (NEW.bytes, NULL, NEW.username); + ELSE + INSERT INTO quota2 (bytes, messages, username) VALUES (NEW.bytes, -NEW.messages, NEW.username); + END IF; + return NULL; + EXCEPTION WHEN unique_violation THEN + -- someone just inserted the record, update it + END; + END LOOP; + END; + $$; + + +ALTER FUNCTION public.merge_quota2() OWNER TO postgres; + +SET default_tablespace = ''; + +SET default_with_oids = true; + +-- +-- Name: admin; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE admin ( + username character varying(255) NOT NULL, + password character varying(255) DEFAULT ''::character varying NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.admin OWNER TO postgres; + +-- +-- Name: TABLE admin; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE admin IS 'Postfix Admin - Virtual Admins'; + + +-- +-- Name: alias; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE alias ( + address character varying(255) NOT NULL, + goto text NOT NULL, + domain character varying(255) NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.alias OWNER TO postgres; + +-- +-- Name: TABLE alias; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE alias IS 'Postfix Admin - Virtual Aliases'; + + +SET default_with_oids = false; + +-- +-- Name: alias_domain; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE alias_domain ( + alias_domain character varying(255) NOT NULL, + target_domain character varying(255) NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.alias_domain OWNER TO postgres; + +-- +-- Name: TABLE alias_domain; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE alias_domain IS 'Postfix Admin - Domain Aliases'; + + +SET default_with_oids = true; + +-- +-- Name: config; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE config ( + id integer NOT NULL, + name character varying(20) NOT NULL, + value character varying(20) NOT NULL +); + + +ALTER TABLE public.config OWNER TO postgres; + +-- +-- Name: config_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE config_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.config_id_seq OWNER TO postgres; + +-- +-- Name: config_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE config_id_seq OWNED BY config.id; + + +-- +-- Name: domain; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE domain ( + domain character varying(255) NOT NULL, + description character varying(255) DEFAULT ''::character varying NOT NULL, + aliases integer DEFAULT 0 NOT NULL, + mailboxes integer DEFAULT 0 NOT NULL, + maxquota bigint DEFAULT 0 NOT NULL, + quota bigint DEFAULT 0 NOT NULL, + transport character varying(255), + backupmx boolean DEFAULT false NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.domain OWNER TO postgres; + +-- +-- Name: TABLE domain; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE domain IS 'Postfix Admin - Virtual Domains'; + + +-- +-- Name: domain_admins; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE domain_admins ( + username character varying(255) NOT NULL, + domain character varying(255) NOT NULL, + created timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.domain_admins OWNER TO postgres; + +-- +-- Name: TABLE domain_admins; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE domain_admins IS 'Postfix Admin - Domain Admins'; + + +-- +-- Name: fetchmail; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE fetchmail ( + id integer NOT NULL, + mailbox character varying(255) DEFAULT ''::character varying NOT NULL, + src_server character varying(255) DEFAULT ''::character varying NOT NULL, + src_auth character varying(15) NOT NULL, + src_user character varying(255) DEFAULT ''::character varying NOT NULL, + src_password character varying(255) DEFAULT ''::character varying NOT NULL, + src_folder character varying(255) DEFAULT ''::character varying NOT NULL, + poll_time integer DEFAULT 10 NOT NULL, + fetchall boolean DEFAULT false NOT NULL, + keep boolean DEFAULT false NOT NULL, + protocol character varying(15) NOT NULL, + extra_options text, + returned_text text, + mda character varying(255) DEFAULT ''::character varying NOT NULL, + date timestamp with time zone DEFAULT now(), + usessl boolean DEFAULT false NOT NULL, + CONSTRAINT fetchmail_protocol_check CHECK (((((((protocol)::text = 'POP3'::text) OR ((protocol)::text = 'IMAP'::text)) OR ((protocol)::text = 'POP2'::text)) OR ((protocol)::text = 'ETRN'::text)) OR ((protocol)::text = 'AUTO'::text))), + CONSTRAINT fetchmail_src_auth_check CHECK (((((((((((((src_auth)::text = 'password'::text) OR ((src_auth)::text = 'kerberos_v5'::text)) OR ((src_auth)::text = 'kerberos'::text)) OR ((src_auth)::text = 'kerberos_v4'::text)) OR ((src_auth)::text = 'gssapi'::text)) OR ((src_auth)::text = 'cram-md5'::text)) OR ((src_auth)::text = 'otp'::text)) OR ((src_auth)::text = 'ntlm'::text)) OR ((src_auth)::text = 'msn'::text)) OR ((src_auth)::text = 'ssh'::text)) OR ((src_auth)::text = 'any'::text))) +); + + +ALTER TABLE public.fetchmail OWNER TO postgres; + +-- +-- Name: fetchmail_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE fetchmail_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.fetchmail_id_seq OWNER TO postgres; + +-- +-- Name: fetchmail_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE fetchmail_id_seq OWNED BY fetchmail.id; + + +-- +-- Name: log; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE log ( + "timestamp" timestamp with time zone DEFAULT now(), + username character varying(255) DEFAULT ''::character varying NOT NULL, + domain character varying(255) DEFAULT ''::character varying NOT NULL, + action character varying(255) DEFAULT ''::character varying NOT NULL, + data text DEFAULT ''::text NOT NULL +); + + +ALTER TABLE public.log OWNER TO postgres; + +-- +-- Name: TABLE log; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE log IS 'Postfix Admin - Log'; + + +-- +-- Name: mailbox; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE mailbox ( + username character varying(255) NOT NULL, + password character varying(255) DEFAULT ''::character varying NOT NULL, + name character varying(255) DEFAULT ''::character varying NOT NULL, + maildir character varying(255) DEFAULT ''::character varying NOT NULL, + quota bigint DEFAULT 0 NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL, + domain character varying(255), + local_part character varying(255) NOT NULL +); + + +ALTER TABLE public.mailbox OWNER TO postgres; + +-- +-- Name: TABLE mailbox; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE mailbox IS 'Postfix Admin - Virtual Mailboxes'; + + +SET default_with_oids = false; + +-- +-- Name: quota; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE quota ( + username character varying(255) NOT NULL, + path character varying(100) NOT NULL, + current bigint +); + + +ALTER TABLE public.quota OWNER TO postgres; + +-- +-- Name: quota2; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE quota2 ( + username character varying(100) NOT NULL, + bytes bigint DEFAULT 0 NOT NULL, + messages integer DEFAULT 0 NOT NULL +); + + +ALTER TABLE public.quota2 OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: vacation; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE vacation ( + email character varying(255) NOT NULL, + subject character varying(255) NOT NULL, + body text DEFAULT ''::text NOT NULL, + created timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL, + domain character varying(255) +); + + +ALTER TABLE public.vacation OWNER TO postgres; + +-- +-- Name: vacation_notification; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE vacation_notification ( + on_vacation character varying(255) NOT NULL, + notified character varying(255) NOT NULL, + notified_at timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE public.vacation_notification OWNER TO postgres; + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY config ALTER COLUMN id SET DEFAULT nextval('config_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY fetchmail ALTER COLUMN id SET DEFAULT nextval('fetchmail_id_seq'::regclass); + + +-- +-- Name: admin_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY admin + ADD CONSTRAINT admin_key PRIMARY KEY (username); + + +-- +-- Name: alias_domain_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY alias_domain + ADD CONSTRAINT alias_domain_pkey PRIMARY KEY (alias_domain); + + +-- +-- Name: alias_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY alias + ADD CONSTRAINT alias_key PRIMARY KEY (address); + + +-- +-- Name: config_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY config + ADD CONSTRAINT config_name_key UNIQUE (name); + + +-- +-- Name: config_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY config + ADD CONSTRAINT config_pkey PRIMARY KEY (id); + + +-- +-- Name: domain_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY domain + ADD CONSTRAINT domain_key PRIMARY KEY (domain); + + +-- +-- Name: fetchmail_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY fetchmail + ADD CONSTRAINT fetchmail_pkey PRIMARY KEY (id); + + +-- +-- Name: mailbox_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY mailbox + ADD CONSTRAINT mailbox_key PRIMARY KEY (username); + + +-- +-- Name: quota2_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY quota2 + ADD CONSTRAINT quota2_pkey PRIMARY KEY (username); + + +-- +-- Name: quota_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY quota + ADD CONSTRAINT quota_pkey PRIMARY KEY (username, path); + + +-- +-- Name: vacation_notification_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY vacation_notification + ADD CONSTRAINT vacation_notification_pkey PRIMARY KEY (on_vacation, notified); + + +-- +-- Name: vacation_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY vacation + ADD CONSTRAINT vacation_pkey PRIMARY KEY (email); + + +-- +-- Name: alias_address_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX alias_address_active ON alias USING btree (address, active); + + +-- +-- Name: alias_domain_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX alias_domain_active ON alias_domain USING btree (alias_domain, active); + + +-- +-- Name: alias_domain_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX alias_domain_idx ON alias USING btree (domain); + + +-- +-- Name: domain_domain_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX domain_domain_active ON domain USING btree (domain, active); + + +-- +-- Name: mailbox_domain_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX mailbox_domain_idx ON mailbox USING btree (domain); + + +-- +-- Name: mailbox_username_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX mailbox_username_active ON mailbox USING btree (username, active); + + +-- +-- Name: vacation_email_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX vacation_email_active ON vacation USING btree (email, active); + + +-- +-- Name: mergequota; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER mergequota BEFORE INSERT ON quota FOR EACH ROW EXECUTE PROCEDURE merge_quota(); + + +-- +-- Name: mergequota2; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER mergequota2 BEFORE INSERT ON quota2 FOR EACH ROW EXECUTE PROCEDURE merge_quota2(); + + +-- +-- Name: alias_domain_alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY alias_domain + ADD CONSTRAINT alias_domain_alias_domain_fkey FOREIGN KEY (alias_domain) REFERENCES domain(domain) ON DELETE CASCADE; + + +-- +-- Name: alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY alias + ADD CONSTRAINT alias_domain_fkey FOREIGN KEY (domain) REFERENCES domain(domain); + + +-- +-- Name: alias_domain_target_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY alias_domain + ADD CONSTRAINT alias_domain_target_domain_fkey FOREIGN KEY (target_domain) REFERENCES domain(domain) ON DELETE CASCADE; + + +-- +-- Name: domain_admins_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY domain_admins + ADD CONSTRAINT domain_admins_domain_fkey FOREIGN KEY (domain) REFERENCES domain(domain); + + +-- +-- Name: mailbox_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY mailbox + ADD CONSTRAINT mailbox_domain_fkey1 FOREIGN KEY (domain) REFERENCES domain(domain); + + +-- +-- Name: vacation_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY vacation + ADD CONSTRAINT vacation_domain_fkey1 FOREIGN KEY (domain) REFERENCES domain(domain); + + +-- +-- Name: vacation_notification_on_vacation_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY vacation_notification + ADD CONSTRAINT vacation_notification_on_vacation_fkey FOREIGN KEY (on_vacation) REFERENCES vacation(email) 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; + + +-- +-- Name: admin; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE admin FROM PUBLIC; +REVOKE ALL ON TABLE admin FROM postgres; +GRANT ALL ON TABLE admin TO postgres; + + +-- +-- Name: alias; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE alias FROM PUBLIC; +REVOKE ALL ON TABLE alias FROM postgres; +GRANT ALL ON TABLE alias TO postgres; + + +-- +-- Name: config; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE config FROM PUBLIC; +REVOKE ALL ON TABLE config FROM postgres; +GRANT ALL ON TABLE config TO postgres; + + +-- +-- Name: domain; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE domain FROM PUBLIC; +REVOKE ALL ON TABLE domain FROM postgres; +GRANT ALL ON TABLE domain TO postgres; + + +-- +-- Name: domain_admins; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE domain_admins FROM PUBLIC; +REVOKE ALL ON TABLE domain_admins FROM postgres; +GRANT ALL ON TABLE domain_admins TO postgres; + + +-- +-- Name: fetchmail; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE fetchmail FROM PUBLIC; +REVOKE ALL ON TABLE fetchmail FROM postgres; +GRANT ALL ON TABLE fetchmail TO postgres; + + +-- +-- Name: log; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE log FROM PUBLIC; +REVOKE ALL ON TABLE log FROM postgres; +GRANT ALL ON TABLE log TO postgres; + + +-- +-- Name: mailbox; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE mailbox FROM PUBLIC; +REVOKE ALL ON TABLE mailbox FROM postgres; +GRANT ALL ON TABLE mailbox TO postgres; + + +-- +-- Name: vacation; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE vacation FROM PUBLIC; +REVOKE ALL ON TABLE vacation FROM postgres; +GRANT ALL ON TABLE vacation TO postgres; + + +-- +-- Name: vacation_notification; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE vacation_notification FROM PUBLIC; +REVOKE ALL ON TABLE vacation_notification FROM postgres; +GRANT ALL ON TABLE vacation_notification TO postgres; + + +-- +-- PostgreSQL database dump complete +-- +