# HG changeset patch
# User František Kučera <franta-hg@frantovo.cz>
# Date 1241533652 -7200
# Node ID c83d559ca3624896e15dcfb12516a2b187cf7b47

Schéma databáze – obsahuje seznamy domén, aliasů a uživatelů

diff -r 000000000000 -r c83d559ca362 sql/schéma.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/schéma.sql	Tue May 05 16:27:32 2009 +0200
@@ -0,0 +1,278 @@
+--
+-- PostgreSQL database dump
+--
+
+-- Started on 2009-05-05 16:25:08 CEST
+
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = off;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+SET escape_string_warning = off;
+
+--
+-- TOC entry 6 (class 2615 OID 16387)
+-- Name: posta; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA posta;
+
+
+SET search_path = posta, pg_catalog;
+
+--
+-- TOC entry 22 (class 1255 OID 16646)
+-- Dependencies: 6
+-- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: -
+--
+
+CREATE FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) RETURNS void
+    AS $_$UPDATE uzivatel
+ SET heslo = md5($4)
+ WHERE 	login = $1
+	AND domena = $2
+	AND heslo = md5($3);$_$
+    LANGUAGE sql SECURITY DEFINER;
+
+
+--
+-- TOC entry 1801 (class 0 OID 0)
+-- Dependencies: 22
+-- Name: FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying); Type: COMMENT; Schema: posta; Owner: -
+--
+
+COMMENT ON FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) IS 'Změní heslo uživatele.';
+
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- TOC entry 1515 (class 1259 OID 16550)
+-- Dependencies: 6
+-- Name: alias; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
+--
+
+CREATE TABLE alias (
+    login character varying(255) NOT NULL,
+    domena character varying(255) NOT NULL,
+    cilova_adresa character varying(255) NOT NULL
+);
+
+
+--
+-- TOC entry 1502 (class 1259 OID 16396)
+-- Dependencies: 6
+-- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
+--
+
+CREATE TABLE domena (
+    domena character varying(255) NOT NULL
+);
+
+
+--
+-- TOC entry 1503 (class 1259 OID 16409)
+-- Dependencies: 6
+-- Name: uzivatel; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
+--
+
+CREATE TABLE uzivatel (
+    login character varying(255) NOT NULL,
+    heslo character varying(512),
+    domena character varying(255) NOT NULL
+);
+
+
+--
+-- TOC entry 1517 (class 1259 OID 16602)
+-- Dependencies: 1596 6
+-- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: -
+--
+
+CREATE VIEW dovecot_heslo AS
+    SELECT (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS "user", uzivatel.heslo AS password, uzivatel.login, uzivatel.domena FROM uzivatel;
+
+
+--
+-- TOC entry 1519 (class 1259 OID 16631)
+-- Dependencies: 1598 6
+-- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: -
+--
+
+CREATE VIEW dovecot_uzivatel AS
+    SELECT 5000 AS uid, 5000 AS gid, ((('/var/mail/virtualniposta/'::text || (uzivatel.domena)::text) || '/'::text) || (uzivatel.login)::text) AS home, (((('maildir:/var/mail/virtualniposta/'::text || (uzivatel.domena)::text) || '/'::text) || (uzivatel.login)::text) || '/Maildir'::text) AS mail, 'maildir:storage=524288'::text AS quota, uzivatel.login, uzivatel.domena FROM uzivatel;
+
+
+--
+-- TOC entry 1516 (class 1259 OID 16563)
+-- Dependencies: 1595 6
+-- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: -
+--
+
+CREATE VIEW postfix_alias AS
+    SELECT (((alias.login)::text || '@'::text) || (alias.domena)::text) AS zdroj, alias.cilova_adresa AS cil FROM alias;
+
+
+--
+-- TOC entry 1518 (class 1259 OID 16607)
+-- Dependencies: 1597 6
+-- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: -
+--
+
+CREATE VIEW postfix_uzivatel AS
+    SELECT ((((uzivatel.domena)::text || '/'::text) || (uzivatel.login)::text) || '/Maildir/'::text) AS cesta, uzivatel.login, uzivatel.domena FROM uzivatel;
+
+
+--
+-- TOC entry 1795 (class 2606 OID 16557)
+-- Dependencies: 1515 1515 1515 1515
+-- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY alias
+    ADD CONSTRAINT aliasy_pk PRIMARY KEY (login, domena, cilova_adresa);
+
+
+--
+-- TOC entry 1791 (class 2606 OID 16400)
+-- Dependencies: 1502 1502
+-- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY domena
+    ADD CONSTRAINT domena_pk PRIMARY KEY (domena);
+
+
+--
+-- TOC entry 1793 (class 2606 OID 16416)
+-- Dependencies: 1503 1503 1503
+-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY uzivatel
+    ADD CONSTRAINT uzivatel_pk PRIMARY KEY (login, domena);
+
+
+--
+-- TOC entry 1797 (class 2606 OID 16558)
+-- Dependencies: 1790 1515 1502
+-- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
+--
+
+ALTER TABLE ONLY alias
+    ADD CONSTRAINT alias_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
+
+
+--
+-- TOC entry 1796 (class 2606 OID 16417)
+-- Dependencies: 1503 1502 1790
+-- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
+--
+
+ALTER TABLE ONLY uzivatel
+    ADD CONSTRAINT uzivatel_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
+
+
+--
+-- TOC entry 1800 (class 0 OID 0)
+-- Dependencies: 6
+-- Name: posta; Type: ACL; Schema: -; Owner: -
+--
+
+REVOKE ALL ON SCHEMA posta FROM PUBLIC;
+REVOKE ALL ON SCHEMA posta FROM posta;
+GRANT ALL ON SCHEMA posta TO posta;
+GRANT USAGE ON SCHEMA posta TO posta_cteni;
+
+
+--
+-- TOC entry 1802 (class 0 OID 0)
+-- Dependencies: 1515
+-- Name: alias; Type: ACL; Schema: posta; Owner: -
+--
+
+REVOKE ALL ON TABLE alias FROM PUBLIC;
+REVOKE ALL ON TABLE alias FROM posta;
+GRANT ALL ON TABLE alias TO posta;
+
+
+--
+-- TOC entry 1803 (class 0 OID 0)
+-- Dependencies: 1502
+-- Name: domena; Type: ACL; Schema: posta; Owner: -
+--
+
+REVOKE ALL ON TABLE domena FROM PUBLIC;
+REVOKE ALL ON TABLE domena FROM posta;
+GRANT ALL ON TABLE domena TO posta;
+GRANT SELECT ON TABLE domena TO posta_cteni;
+
+
+--
+-- TOC entry 1804 (class 0 OID 0)
+-- Dependencies: 1503
+-- Name: uzivatel; Type: ACL; Schema: posta; Owner: -
+--
+
+REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
+REVOKE ALL ON TABLE uzivatel FROM posta;
+GRANT ALL ON TABLE uzivatel TO posta;
+GRANT SELECT ON TABLE uzivatel TO posta_cteni;
+
+
+--
+-- TOC entry 1805 (class 0 OID 0)
+-- Dependencies: 1517
+-- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: -
+--
+
+REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;
+REVOKE ALL ON TABLE dovecot_heslo FROM posta;
+GRANT ALL ON TABLE dovecot_heslo TO posta;
+GRANT SELECT ON TABLE dovecot_heslo TO posta_cteni;
+
+
+--
+-- TOC entry 1806 (class 0 OID 0)
+-- Dependencies: 1519
+-- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: -
+--
+
+REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;
+REVOKE ALL ON TABLE dovecot_uzivatel FROM posta;
+GRANT ALL ON TABLE dovecot_uzivatel TO posta;
+GRANT SELECT ON TABLE dovecot_uzivatel TO posta_cteni;
+
+
+--
+-- TOC entry 1807 (class 0 OID 0)
+-- Dependencies: 1516
+-- Name: postfix_alias; Type: ACL; Schema: posta; Owner: -
+--
+
+REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;
+REVOKE ALL ON TABLE postfix_alias FROM posta;
+GRANT ALL ON TABLE postfix_alias TO posta;
+GRANT SELECT ON TABLE postfix_alias TO posta_cteni;
+
+
+--
+-- TOC entry 1808 (class 0 OID 0)
+-- Dependencies: 1518
+-- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: -
+--
+
+REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;
+REVOKE ALL ON TABLE postfix_uzivatel FROM posta;
+GRANT ALL ON TABLE postfix_uzivatel TO posta;
+GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
+
+
+-- Completed on 2009-05-05 16:25:18 CEST
+
+--
+-- PostgreSQL database dump complete
+--
+