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ě
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