Normalizace tabulky aliasů – zvláštní sloupeček pro jméno a doménu v cílové e-mailové adrese.
authorFrantišek Kučera <franta-hg@frantovo.cz>
Sun, 10 May 2009 18:18:04 +0200
changeset 14c830b05699ba
parent 13 119744ae4179
child 15 ad391f838b9d
Normalizace tabulky aliasů – zvláštní sloupeček pro jméno a doménu v cílové e-mailové adrese.
V pohledu používaném Postfixem se nic nemění – ten vypadá pořád stejně
sql/schéma.sql
     1.1 --- a/sql/schéma.sql	Sun May 10 01:13:12 2009 +0200
     1.2 +++ b/sql/schéma.sql	Sun May 10 18:18:04 2009 +0200
     1.3 @@ -2,7 +2,7 @@
     1.4  -- PostgreSQL database dump
     1.5  --
     1.6  
     1.7 --- Started on 2009-05-06 02:13:29 CEST
     1.8 +-- Started on 2009-05-10 17:40:57 CEST
     1.9  
    1.10  SET client_encoding = 'UTF8';
    1.11  SET standard_conforming_strings = off;
    1.12 @@ -65,13 +65,52 @@
    1.13  --
    1.14  
    1.15  CREATE TABLE alias (
    1.16 -    login character varying(255) NOT NULL,
    1.17 -    domena character varying(255) NOT NULL,
    1.18 -    cilova_adresa character varying(255) NOT NULL
    1.19 +    zdroj_jmeno character varying(255) NOT NULL,
    1.20 +    zdroj_domena character varying(255) NOT NULL,
    1.21 +    cil_jmeno character varying(255) NOT NULL,
    1.22 +    cil_domena character varying(255)
    1.23  );
    1.24  
    1.25  
    1.26  --
    1.27 +-- TOC entry 1837 (class 0 OID 0)
    1.28 +-- Dependencies: 1549
    1.29 +-- Name: COLUMN alias.zdroj_jmeno; Type: COMMENT; Schema: posta; Owner: -
    1.30 +--
    1.31 +
    1.32 +COMMENT ON COLUMN alias.zdroj_jmeno IS 'Část zdrojové adresy před zavináčem';
    1.33 +
    1.34 +
    1.35 +--
    1.36 +-- TOC entry 1838 (class 0 OID 0)
    1.37 +-- Dependencies: 1549
    1.38 +-- Name: COLUMN alias.zdroj_domena; Type: COMMENT; Schema: posta; Owner: -
    1.39 +--
    1.40 +
    1.41 +COMMENT ON COLUMN alias.zdroj_domena IS 'Část zdrojové adresy za zavináčem';
    1.42 +
    1.43 +
    1.44 +--
    1.45 +-- TOC entry 1839 (class 0 OID 0)
    1.46 +-- Dependencies: 1549
    1.47 +-- Name: COLUMN alias.cil_jmeno; Type: COMMENT; Schema: posta; Owner: -
    1.48 +--
    1.49 +
    1.50 +COMMENT ON COLUMN alias.cil_jmeno IS 'Část cílové adresy před zavináčem
    1.51 +(sem zprávu předáme)';
    1.52 +
    1.53 +
    1.54 +--
    1.55 +-- TOC entry 1840 (class 0 OID 0)
    1.56 +-- Dependencies: 1549
    1.57 +-- Name: COLUMN alias.cil_domena; Type: COMMENT; Schema: posta; Owner: -
    1.58 +--
    1.59 +
    1.60 +COMMENT ON COLUMN alias.cil_domena IS 'Část cílové adresy za zavináčem
    1.61 +(sem zprávu předáme)';
    1.62 +
    1.63 +
    1.64 +--
    1.65  -- TOC entry 1536 (class 1259 OID 16396)
    1.66  -- Dependencies: 8
    1.67  -- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
    1.68 @@ -96,8 +135,8 @@
    1.69  
    1.70  
    1.71  --
    1.72 --- TOC entry 1551 (class 1259 OID 16602)
    1.73 --- Dependencies: 1630 8
    1.74 +-- TOC entry 1550 (class 1259 OID 16602)
    1.75 +-- Dependencies: 1629 8
    1.76  -- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: -
    1.77  --
    1.78  
    1.79 @@ -106,8 +145,8 @@
    1.80  
    1.81  
    1.82  --
    1.83 --- TOC entry 1553 (class 1259 OID 16631)
    1.84 --- Dependencies: 1632 8
    1.85 +-- TOC entry 1552 (class 1259 OID 16631)
    1.86 +-- Dependencies: 1631 8
    1.87  -- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: -
    1.88  --
    1.89  
    1.90 @@ -116,18 +155,18 @@
    1.91  
    1.92  
    1.93  --
    1.94 --- TOC entry 1550 (class 1259 OID 16563)
    1.95 --- Dependencies: 1629 8
    1.96 +-- TOC entry 1553 (class 1259 OID 16695)
    1.97 +-- Dependencies: 1632 8
    1.98  -- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: -
    1.99  --
   1.100  
   1.101  CREATE VIEW postfix_alias AS
   1.102 -    SELECT (((alias.login)::text || '@'::text) || (alias.domena)::text) AS zdroj, alias.cilova_adresa AS cil FROM alias;
   1.103 +    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;
   1.104  
   1.105  
   1.106  --
   1.107 --- TOC entry 1552 (class 1259 OID 16607)
   1.108 --- Dependencies: 1631 8
   1.109 +-- TOC entry 1551 (class 1259 OID 16607)
   1.110 +-- Dependencies: 1630 8
   1.111  -- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: -
   1.112  --
   1.113  
   1.114 @@ -142,7 +181,7 @@
   1.115  --
   1.116  
   1.117  ALTER TABLE ONLY alias
   1.118 -    ADD CONSTRAINT aliasy_pk PRIMARY KEY (login, domena, cilova_adresa);
   1.119 +    ADD CONSTRAINT aliasy_pk PRIMARY KEY (zdroj_jmeno, zdroj_domena, cil_jmeno);
   1.120  
   1.121  
   1.122  --
   1.123 @@ -167,17 +206,17 @@
   1.124  
   1.125  --
   1.126  -- TOC entry 1831 (class 2606 OID 16558)
   1.127 --- Dependencies: 1824 1536 1549
   1.128 +-- Dependencies: 1536 1549 1824
   1.129  -- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
   1.130  --
   1.131  
   1.132  ALTER TABLE ONLY alias
   1.133 -    ADD CONSTRAINT alias_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
   1.134 +    ADD CONSTRAINT alias_domena_fk FOREIGN KEY (zdroj_domena) REFERENCES domena(domena);
   1.135  
   1.136  
   1.137  --
   1.138  -- TOC entry 1830 (class 2606 OID 16417)
   1.139 --- Dependencies: 1537 1536 1824
   1.140 +-- Dependencies: 1536 1824 1537
   1.141  -- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
   1.142  --
   1.143  
   1.144 @@ -212,7 +251,7 @@
   1.145  
   1.146  
   1.147  --
   1.148 --- TOC entry 1837 (class 0 OID 0)
   1.149 +-- TOC entry 1841 (class 0 OID 0)
   1.150  -- Dependencies: 1549
   1.151  -- Name: alias; Type: ACL; Schema: posta; Owner: -
   1.152  --
   1.153 @@ -223,7 +262,7 @@
   1.154  
   1.155  
   1.156  --
   1.157 --- TOC entry 1838 (class 0 OID 0)
   1.158 +-- TOC entry 1842 (class 0 OID 0)
   1.159  -- Dependencies: 1536
   1.160  -- Name: domena; Type: ACL; Schema: posta; Owner: -
   1.161  --
   1.162 @@ -235,7 +274,7 @@
   1.163  
   1.164  
   1.165  --
   1.166 --- TOC entry 1839 (class 0 OID 0)
   1.167 +-- TOC entry 1843 (class 0 OID 0)
   1.168  -- Dependencies: 1537
   1.169  -- Name: uzivatel; Type: ACL; Schema: posta; Owner: -
   1.170  --
   1.171 @@ -247,8 +286,8 @@
   1.172  
   1.173  
   1.174  --
   1.175 --- TOC entry 1840 (class 0 OID 0)
   1.176 --- Dependencies: 1551
   1.177 +-- TOC entry 1844 (class 0 OID 0)
   1.178 +-- Dependencies: 1550
   1.179  -- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: -
   1.180  --
   1.181  
   1.182 @@ -259,8 +298,8 @@
   1.183  
   1.184  
   1.185  --
   1.186 --- TOC entry 1841 (class 0 OID 0)
   1.187 --- Dependencies: 1553
   1.188 +-- TOC entry 1845 (class 0 OID 0)
   1.189 +-- Dependencies: 1552
   1.190  -- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: -
   1.191  --
   1.192  
   1.193 @@ -271,8 +310,8 @@
   1.194  
   1.195  
   1.196  --
   1.197 --- TOC entry 1842 (class 0 OID 0)
   1.198 --- Dependencies: 1550
   1.199 +-- TOC entry 1846 (class 0 OID 0)
   1.200 +-- Dependencies: 1553
   1.201  -- Name: postfix_alias; Type: ACL; Schema: posta; Owner: -
   1.202  --
   1.203  
   1.204 @@ -283,8 +322,8 @@
   1.205  
   1.206  
   1.207  --
   1.208 --- TOC entry 1843 (class 0 OID 0)
   1.209 --- Dependencies: 1552
   1.210 +-- TOC entry 1847 (class 0 OID 0)
   1.211 +-- Dependencies: 1551
   1.212  -- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: -
   1.213  --
   1.214  
   1.215 @@ -294,7 +333,7 @@
   1.216  GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
   1.217  
   1.218  
   1.219 --- Completed on 2009-05-06 02:13:36 CEST
   1.220 +-- Completed on 2009-05-10 17:41:05 CEST
   1.221  
   1.222  --
   1.223  -- PostgreSQL database dump complete