# HG changeset patch
# User František Kučera <franta-hg@frantovo.cz>
# Date 1322931240 -3600
# Node ID 571d9fd2d336d3a781591418b854faeaafe97242
# Parent  c78f71bda3406e633f6accd30c62ecf95da7d582
SQL: aktualizace + SOGo: jména v GALu

diff -r c78f71bda340 -r 571d9fd2d336 doručenky/dorucenky.pl
--- a/doručenky/dorucenky.pl	Wed Nov 30 17:13:26 2011 +0100
+++ b/doručenky/dorucenky.pl	Sat Dec 03 17:54:00 2011 +0100
@@ -13,6 +13,8 @@
 # viz http://search.cpan.org/~dskoll/MIME-tools-5.502/lib/MIME/Head.pm
 # viz http://perldoc.perl.org/Net/SMTP.html
 
+# aptitude install libmime-tools-perl libmailtools-perl
+
 
 # Nastavení --------------------------------------------------------------------
 my $komu = 'noreply@example.com'; # adresa správce – sem pošleme zprávy, pokud nevíme, kam je poslat (chybí Delivered-To), nebo máme podezření na zacyklení
@@ -88,3 +90,4 @@
 $dorucenka->print($smtp);
 $smtp->dataend();
 $smtp->quit;
+
diff -r c78f71bda340 -r 571d9fd2d336 sql/schéma.sql
--- a/sql/schéma.sql	Wed Nov 30 17:13:26 2011 +0100
+++ b/sql/schéma.sql	Sat Dec 03 17:54:00 2011 +0100
@@ -2,8 +2,9 @@
 -- PostgreSQL database dump
 --
 
--- Started on 2009-08-28 17:21:16 CEST
+-- Started on 2011-12-03 17:50:08 CET
 
+SET statement_timeout = 0;
 SET client_encoding = 'UTF8';
 SET standard_conforming_strings = off;
 SET check_function_bodies = false;
@@ -11,22 +12,25 @@
 SET escape_string_warning = off;
 
 --
--- TOC entry 8 (class 2615 OID 16387)
--- Name: posta; Type: SCHEMA; Schema: -; Owner: -
+-- TOC entry 7 (class 2615 OID 16397)
+-- Name: posta; Type: SCHEMA; Schema: -; Owner: posta
 --
 
 CREATE SCHEMA posta;
 
 
+ALTER SCHEMA posta OWNER TO posta;
+
 SET search_path = posta, pg_catalog;
 
 --
--- TOC entry 57 (class 1255 OID 16694)
--- Dependencies: 472 8
--- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: -
+-- TOC entry 189 (class 1255 OID 16398)
+-- Dependencies: 7 551
+-- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: posta
 --
 
 CREATE FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) RETURNS text
+    LANGUAGE plpgsql SECURITY DEFINER
     AS $$DECLARE pocet integer;
 BEGIN
 UPDATE uzivatel
@@ -41,14 +45,15 @@
 		RAISE EXCEPTION 'Nesprávné heslo, nebo uživatel % neexistuje', "Login" || '@' || "Domena";
 	END IF;
 END;
-	$$
-    LANGUAGE plpgsql SECURITY DEFINER;
+	$$;
 
 
+ALTER FUNCTION posta.zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) OWNER TO posta;
+
 --
--- TOC entry 1981 (class 0 OID 0)
--- Dependencies: 57
--- Name: FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying); Type: COMMENT; Schema: posta; Owner: -
+-- TOC entry 1900 (class 0 OID 0)
+-- Dependencies: 189
+-- Name: FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying); Type: COMMENT; Schema: posta; Owner: posta
 --
 
 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.';
@@ -59,9 +64,9 @@
 SET default_with_oids = false;
 
 --
--- TOC entry 1657 (class 1259 OID 16550)
--- Dependencies: 8
--- Name: alias; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
+-- TOC entry 143 (class 1259 OID 16399)
+-- Dependencies: 7
+-- Name: alias; Type: TABLE; Schema: posta; Owner: posta; Tablespace: 
 --
 
 CREATE TABLE alias (
@@ -72,10 +77,12 @@
 );
 
 
+ALTER TABLE posta.alias OWNER TO posta;
+
 --
--- TOC entry 1983 (class 0 OID 0)
--- Dependencies: 1657
--- Name: COLUMN alias.zdroj_jmeno; Type: COMMENT; Schema: posta; Owner: -
+-- TOC entry 1902 (class 0 OID 0)
+-- Dependencies: 143
+-- Name: COLUMN alias.zdroj_jmeno; Type: COMMENT; Schema: posta; Owner: posta
 --
 
 COMMENT ON COLUMN alias.zdroj_jmeno IS 'Část zdrojové adresy před zavináčem.
@@ -83,9 +90,9 @@
 
 
 --
--- TOC entry 1984 (class 0 OID 0)
--- Dependencies: 1657
--- Name: COLUMN alias.zdroj_domena; Type: COMMENT; Schema: posta; Owner: -
+-- TOC entry 1903 (class 0 OID 0)
+-- Dependencies: 143
+-- Name: COLUMN alias.zdroj_domena; Type: COMMENT; Schema: posta; Owner: posta
 --
 
 COMMENT ON COLUMN alias.zdroj_domena IS 'Část zdrojové adresy za zavináčem.
@@ -93,9 +100,9 @@
 
 
 --
--- TOC entry 1985 (class 0 OID 0)
--- Dependencies: 1657
--- Name: COLUMN alias.cil_jmeno; Type: COMMENT; Schema: posta; Owner: -
+-- TOC entry 1904 (class 0 OID 0)
+-- Dependencies: 143
+-- Name: COLUMN alias.cil_jmeno; Type: COMMENT; Schema: posta; Owner: posta
 --
 
 COMMENT ON COLUMN alias.cil_jmeno IS 'Část cílové adresy před zavináčem.
@@ -103,9 +110,9 @@
 
 
 --
--- TOC entry 1986 (class 0 OID 0)
--- Dependencies: 1657
--- Name: COLUMN alias.cil_domena; Type: COMMENT; Schema: posta; Owner: -
+-- TOC entry 1905 (class 0 OID 0)
+-- Dependencies: 143
+-- Name: COLUMN alias.cil_domena; Type: COMMENT; Schema: posta; Owner: posta
 --
 
 COMMENT ON COLUMN alias.cil_domena IS 'Část cílové adresy za zavináčem.
@@ -113,9 +120,9 @@
 
 
 --
--- TOC entry 1644 (class 1259 OID 16396)
--- Dependencies: 8
--- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
+-- TOC entry 144 (class 1259 OID 16405)
+-- Dependencies: 7
+-- Name: domena; Type: TABLE; Schema: posta; Owner: posta; Tablespace: 
 --
 
 CREATE TABLE domena (
@@ -123,63 +130,139 @@
 );
 
 
+ALTER TABLE posta.domena OWNER TO posta;
+
 --
--- TOC entry 1645 (class 1259 OID 16409)
--- Dependencies: 8
--- Name: uzivatel; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
+-- TOC entry 145 (class 1259 OID 16408)
+-- Dependencies: 1691 7
+-- Name: alias_externi; Type: VIEW; Schema: posta; Owner: posta
+--
+
+CREATE VIEW alias_externi AS
+    SELECT a.zdroj_jmeno, a.zdroj_domena, a.cil_jmeno, a.cil_domena FROM (alias a LEFT JOIN domena d ON (((a.cil_domena)::text = (d.domena)::text))) WHERE (d.domena IS NULL);
+
+
+ALTER TABLE posta.alias_externi OWNER TO posta;
+
+--
+-- TOC entry 1908 (class 0 OID 0)
+-- Dependencies: 145
+-- Name: VIEW alias_externi; Type: COMMENT; Schema: posta; Owner: posta
+--
+
+COMMENT ON VIEW alias_externi IS 'Poštovní alias, který vede na doménu nacházející se na jiném serveru (tzn. e-mail opouští Veverku)';
+
+
+--
+-- TOC entry 146 (class 1259 OID 16412)
+-- Dependencies: 1692 7
+-- Name: alias_interni; Type: VIEW; Schema: posta; Owner: posta
+--
+
+CREATE VIEW alias_interni AS
+    SELECT a.zdroj_jmeno, a.zdroj_domena, a.cil_jmeno, a.cil_domena FROM (alias a LEFT JOIN domena d ON (((a.cil_domena)::text = (d.domena)::text))) WHERE (d.domena IS NOT NULL);
+
+
+ALTER TABLE posta.alias_interni OWNER TO posta;
+
+--
+-- TOC entry 1909 (class 0 OID 0)
+-- Dependencies: 146
+-- Name: VIEW alias_interni; Type: COMMENT; Schema: posta; Owner: posta
+--
+
+COMMENT ON VIEW alias_interni IS 'Poštovní alias, který vede na doménu nacházející se na tomto serveru (tzn. e-mail neopouští Veverku)';
+
+
+--
+-- TOC entry 147 (class 1259 OID 16416)
+-- Dependencies: 7
+-- Name: uzivatel; Type: TABLE; Schema: posta; Owner: posta; Tablespace: 
 --
 
 CREATE TABLE uzivatel (
     login character varying(255) NOT NULL,
     heslo character varying(512),
-    domena character varying(255) NOT NULL
+    domena character varying(255) NOT NULL,
+    jmeno character varying(32)
 );
 
 
+ALTER TABLE posta.uzivatel OWNER TO posta;
+
 --
--- TOC entry 1658 (class 1259 OID 16602)
--- Dependencies: 1775 8
--- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: -
+-- TOC entry 1910 (class 0 OID 0)
+-- Dependencies: 147
+-- Name: COLUMN uzivatel.jmeno; Type: COMMENT; Schema: posta; Owner: posta
+--
+
+COMMENT ON COLUMN uzivatel.jmeno IS 'Celé jméno uživatele';
+
+
+--
+-- TOC entry 148 (class 1259 OID 16422)
+-- Dependencies: 1693 7
+-- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: posta
 --
 
 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;
 
 
+ALTER TABLE posta.dovecot_heslo OWNER TO posta;
+
 --
--- TOC entry 1660 (class 1259 OID 16631)
--- Dependencies: 1777 8
--- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: -
+-- TOC entry 149 (class 1259 OID 16426)
+-- Dependencies: 1694 7
+-- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: posta
 --
 
 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;
+    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:Maximalni velikost schranky'::text AS quota, '*:storage=1G'::text AS quota_rule, uzivatel.login, uzivatel.domena FROM uzivatel;
 
 
+ALTER TABLE posta.dovecot_uzivatel OWNER TO posta;
+
 --
--- TOC entry 1661 (class 1259 OID 16695)
--- Dependencies: 1778 8
--- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: -
+-- TOC entry 150 (class 1259 OID 16430)
+-- Dependencies: 1695 7
+-- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: posta
 --
 
 CREATE VIEW postfix_alias AS
     SELECT (((alias.zdroj_jmeno)::text || '@'::text) || (alias.zdroj_domena)::text) AS zdroj, (((alias.cil_jmeno)::text || '@'::text) || (alias.cil_domena)::text) AS cil FROM alias;
 
 
+ALTER TABLE posta.postfix_alias OWNER TO posta;
+
 --
--- TOC entry 1659 (class 1259 OID 16607)
--- Dependencies: 1776 8
--- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: -
+-- TOC entry 151 (class 1259 OID 16434)
+-- Dependencies: 1696 7
+-- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: posta
 --
 
 CREATE VIEW postfix_uzivatel AS
     SELECT ((((uzivatel.domena)::text || '/'::text) || (uzivatel.login)::text) || '/Maildir/'::text) AS cesta, uzivatel.login, uzivatel.domena FROM uzivatel;
 
 
+ALTER TABLE posta.postfix_uzivatel OWNER TO posta;
+
 --
--- TOC entry 1975 (class 2606 OID 17071)
--- Dependencies: 1657 1657 1657 1657 1657
--- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
+-- TOC entry 152 (class 1259 OID 16438)
+-- Dependencies: 1697 7
+-- Name: sogo_uzivatel; Type: VIEW; Schema: posta; Owner: posta
+--
+
+CREATE VIEW sogo_uzivatel AS
+    SELECT (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS c_uid, COALESCE((uzivatel.jmeno)::text, (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text)) AS c_cn, (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS c_name, (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS mail, uzivatel.heslo AS c_password FROM uzivatel;
+
+
+ALTER TABLE posta.sogo_uzivatel OWNER TO posta;
+
+--
+-- TOC entry 1890 (class 2606 OID 16443)
+-- Dependencies: 143 143 143 143 143
+-- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: posta; Tablespace: 
 --
 
 ALTER TABLE ONLY alias
@@ -187,9 +270,9 @@
 
 
 --
--- TOC entry 1971 (class 2606 OID 16400)
--- Dependencies: 1644 1644
--- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
+-- TOC entry 1892 (class 2606 OID 16445)
+-- Dependencies: 144 144
+-- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: posta; Tablespace: 
 --
 
 ALTER TABLE ONLY domena
@@ -197,9 +280,9 @@
 
 
 --
--- TOC entry 1973 (class 2606 OID 16416)
--- Dependencies: 1645 1645 1645
--- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
+-- TOC entry 1894 (class 2606 OID 16447)
+-- Dependencies: 147 147 147
+-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: posta; Tablespace: 
 --
 
 ALTER TABLE ONLY uzivatel
@@ -207,9 +290,9 @@
 
 
 --
--- TOC entry 1977 (class 2606 OID 16558)
--- Dependencies: 1657 1970 1644
--- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
+-- TOC entry 1895 (class 2606 OID 16448)
+-- Dependencies: 143 1891 144
+-- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: posta
 --
 
 ALTER TABLE ONLY alias
@@ -217,9 +300,9 @@
 
 
 --
--- TOC entry 1976 (class 2606 OID 16417)
--- Dependencies: 1644 1645 1970
--- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
+-- TOC entry 1896 (class 2606 OID 16453)
+-- Dependencies: 1891 144 147
+-- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: posta
 --
 
 ALTER TABLE ONLY uzivatel
@@ -227,9 +310,9 @@
 
 
 --
--- TOC entry 1980 (class 0 OID 0)
--- Dependencies: 8
--- Name: posta; Type: ACL; Schema: -; Owner: -
+-- TOC entry 1899 (class 0 OID 0)
+-- Dependencies: 7
+-- Name: posta; Type: ACL; Schema: -; Owner: posta
 --
 
 REVOKE ALL ON SCHEMA posta FROM PUBLIC;
@@ -240,9 +323,9 @@
 
 
 --
--- TOC entry 1982 (class 0 OID 0)
--- Dependencies: 57
--- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: ACL; Schema: posta; Owner: -
+-- TOC entry 1901 (class 0 OID 0)
+-- Dependencies: 189
+-- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: ACL; Schema: posta; Owner: posta
 --
 
 REVOKE ALL ON FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) FROM PUBLIC;
@@ -253,9 +336,9 @@
 
 
 --
--- TOC entry 1987 (class 0 OID 0)
--- Dependencies: 1657
--- Name: alias; Type: ACL; Schema: posta; Owner: -
+-- TOC entry 1906 (class 0 OID 0)
+-- Dependencies: 143
+-- Name: alias; Type: ACL; Schema: posta; Owner: posta
 --
 
 REVOKE ALL ON TABLE alias FROM PUBLIC;
@@ -264,9 +347,9 @@
 
 
 --
--- TOC entry 1988 (class 0 OID 0)
--- Dependencies: 1644
--- Name: domena; Type: ACL; Schema: posta; Owner: -
+-- TOC entry 1907 (class 0 OID 0)
+-- Dependencies: 144
+-- Name: domena; Type: ACL; Schema: posta; Owner: posta
 --
 
 REVOKE ALL ON TABLE domena FROM PUBLIC;
@@ -276,9 +359,9 @@
 
 
 --
--- TOC entry 1989 (class 0 OID 0)
--- Dependencies: 1645
--- Name: uzivatel; Type: ACL; Schema: posta; Owner: -
+-- TOC entry 1911 (class 0 OID 0)
+-- Dependencies: 147
+-- Name: uzivatel; Type: ACL; Schema: posta; Owner: posta
 --
 
 REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
@@ -288,9 +371,9 @@
 
 
 --
--- TOC entry 1990 (class 0 OID 0)
--- Dependencies: 1658
--- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: -
+-- TOC entry 1912 (class 0 OID 0)
+-- Dependencies: 148
+-- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: posta
 --
 
 REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;
@@ -300,9 +383,9 @@
 
 
 --
--- TOC entry 1991 (class 0 OID 0)
--- Dependencies: 1660
--- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: -
+-- TOC entry 1913 (class 0 OID 0)
+-- Dependencies: 149
+-- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: posta
 --
 
 REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;
@@ -312,9 +395,9 @@
 
 
 --
--- TOC entry 1992 (class 0 OID 0)
--- Dependencies: 1661
--- Name: postfix_alias; Type: ACL; Schema: posta; Owner: -
+-- TOC entry 1914 (class 0 OID 0)
+-- Dependencies: 150
+-- Name: postfix_alias; Type: ACL; Schema: posta; Owner: posta
 --
 
 REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;
@@ -324,9 +407,9 @@
 
 
 --
--- TOC entry 1993 (class 0 OID 0)
--- Dependencies: 1659
--- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: -
+-- TOC entry 1915 (class 0 OID 0)
+-- Dependencies: 151
+-- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: posta
 --
 
 REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;
@@ -335,7 +418,19 @@
 GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
 
 
--- Completed on 2009-08-28 17:21:17 CEST
+--
+-- TOC entry 1916 (class 0 OID 0)
+-- Dependencies: 152
+-- Name: sogo_uzivatel; Type: ACL; Schema: posta; Owner: posta
+--
+
+REVOKE ALL ON TABLE sogo_uzivatel FROM PUBLIC;
+REVOKE ALL ON TABLE sogo_uzivatel FROM posta;
+GRANT ALL ON TABLE sogo_uzivatel TO posta;
+GRANT SELECT ON TABLE sogo_uzivatel TO posta_cteni;
+
+
+-- Completed on 2011-12-03 17:50:08 CET
 
 --
 -- PostgreSQL database dump complete