Oprava primárního klíče (přidání domény). Tento soubor získáme tímto příkazem:
authorFrantišek Kučera <franta-hg@frantovo.cz>
Fri, 28 Aug 2009 17:24:23 +0200
changeset 17693f2b2587aa
parent 16 3461cdf271d0
child 18 7eb66061cac8
Oprava primárního klíče (přidání domény). Tento soubor získáme tímto příkazem:
pg_dump -h veverka.ch -p 5432 -U posta -F p -s -s -O -v -f "schéma.sql" -n 'posta' veverka
sql/schéma.sql
     1.1 --- a/sql/schéma.sql	Fri May 29 21:59:34 2009 +0200
     1.2 +++ b/sql/schéma.sql	Fri Aug 28 17:24:23 2009 +0200
     1.3 @@ -2,7 +2,7 @@
     1.4  -- PostgreSQL database dump
     1.5  --
     1.6  
     1.7 --- Started on 2009-05-29 21:13:11 CEST
     1.8 +-- Started on 2009-08-28 17:21:16 CEST
     1.9  
    1.10  SET client_encoding = 'UTF8';
    1.11  SET standard_conforming_strings = off;
    1.12 @@ -21,13 +21,13 @@
    1.13  SET search_path = posta, pg_catalog;
    1.14  
    1.15  --
    1.16 --- TOC entry 55 (class 1255 OID 16694)
    1.17 --- Dependencies: 364 8
    1.18 +-- TOC entry 57 (class 1255 OID 16694)
    1.19 +-- Dependencies: 472 8
    1.20  -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: -
    1.21  --
    1.22  
    1.23  CREATE FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying) RETURNS text
    1.24 -    AS $_$DECLARE pocet integer;
    1.25 +    AS $$DECLARE pocet integer;
    1.26  BEGIN
    1.27  UPDATE uzivatel
    1.28   SET heslo = md5("Nove_heslo")
    1.29 @@ -36,18 +36,18 @@
    1.30  	AND uzivatel.heslo = md5("Stare_heslo");
    1.31  	GET DIAGNOSTICS pocet = ROW_COUNT;
    1.32  	IF pocet = 1 THEN
    1.33 -		RETURN 'Změna hesla pro ' || $1 || '@' || $2 || ' proběhla úspěšně.';
    1.34 +		RETURN 'Změna hesla pro ' || "Login" || '@' || "Domena" || ' proběhla úspěšně.';
    1.35  	ELSE
    1.36 -		RAISE EXCEPTION 'Nesprávné heslo, nebo uživatel % neexistuje', $1 || '@' || $2;
    1.37 +		RAISE EXCEPTION 'Nesprávné heslo, nebo uživatel % neexistuje', "Login" || '@' || "Domena";
    1.38  	END IF;
    1.39  END;
    1.40 -	$_$
    1.41 +	$$
    1.42      LANGUAGE plpgsql SECURITY DEFINER;
    1.43  
    1.44  
    1.45  --
    1.46 --- TOC entry 1835 (class 0 OID 0)
    1.47 --- Dependencies: 55
    1.48 +-- TOC entry 1981 (class 0 OID 0)
    1.49 +-- Dependencies: 57
    1.50  -- Name: FUNCTION zmen_heslo("Login" character varying, "Domena" character varying, "Stare_heslo" character varying, "Nove_heslo" character varying); Type: COMMENT; Schema: posta; Owner: -
    1.51  --
    1.52  
    1.53 @@ -59,7 +59,7 @@
    1.54  SET default_with_oids = false;
    1.55  
    1.56  --
    1.57 --- TOC entry 1549 (class 1259 OID 16550)
    1.58 +-- TOC entry 1657 (class 1259 OID 16550)
    1.59  -- Dependencies: 8
    1.60  -- Name: alias; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
    1.61  --
    1.62 @@ -68,13 +68,13 @@
    1.63      zdroj_jmeno character varying(255) NOT NULL,
    1.64      zdroj_domena character varying(255) NOT NULL,
    1.65      cil_jmeno character varying(255) NOT NULL,
    1.66 -    cil_domena character varying(255)
    1.67 +    cil_domena character varying(255) NOT NULL
    1.68  );
    1.69  
    1.70  
    1.71  --
    1.72 --- TOC entry 1837 (class 0 OID 0)
    1.73 --- Dependencies: 1549
    1.74 +-- TOC entry 1983 (class 0 OID 0)
    1.75 +-- Dependencies: 1657
    1.76  -- Name: COLUMN alias.zdroj_jmeno; Type: COMMENT; Schema: posta; Owner: -
    1.77  --
    1.78  
    1.79 @@ -83,8 +83,8 @@
    1.80  
    1.81  
    1.82  --
    1.83 --- TOC entry 1838 (class 0 OID 0)
    1.84 --- Dependencies: 1549
    1.85 +-- TOC entry 1984 (class 0 OID 0)
    1.86 +-- Dependencies: 1657
    1.87  -- Name: COLUMN alias.zdroj_domena; Type: COMMENT; Schema: posta; Owner: -
    1.88  --
    1.89  
    1.90 @@ -93,8 +93,8 @@
    1.91  
    1.92  
    1.93  --
    1.94 --- TOC entry 1839 (class 0 OID 0)
    1.95 --- Dependencies: 1549
    1.96 +-- TOC entry 1985 (class 0 OID 0)
    1.97 +-- Dependencies: 1657
    1.98  -- Name: COLUMN alias.cil_jmeno; Type: COMMENT; Schema: posta; Owner: -
    1.99  --
   1.100  
   1.101 @@ -103,8 +103,8 @@
   1.102  
   1.103  
   1.104  --
   1.105 --- TOC entry 1840 (class 0 OID 0)
   1.106 --- Dependencies: 1549
   1.107 +-- TOC entry 1986 (class 0 OID 0)
   1.108 +-- Dependencies: 1657
   1.109  -- Name: COLUMN alias.cil_domena; Type: COMMENT; Schema: posta; Owner: -
   1.110  --
   1.111  
   1.112 @@ -113,7 +113,7 @@
   1.113  
   1.114  
   1.115  --
   1.116 --- TOC entry 1536 (class 1259 OID 16396)
   1.117 +-- TOC entry 1644 (class 1259 OID 16396)
   1.118  -- Dependencies: 8
   1.119  -- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
   1.120  --
   1.121 @@ -124,7 +124,7 @@
   1.122  
   1.123  
   1.124  --
   1.125 --- TOC entry 1537 (class 1259 OID 16409)
   1.126 +-- TOC entry 1645 (class 1259 OID 16409)
   1.127  -- Dependencies: 8
   1.128  -- Name: uzivatel; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
   1.129  --
   1.130 @@ -137,8 +137,8 @@
   1.131  
   1.132  
   1.133  --
   1.134 --- TOC entry 1550 (class 1259 OID 16602)
   1.135 --- Dependencies: 1629 8
   1.136 +-- TOC entry 1658 (class 1259 OID 16602)
   1.137 +-- Dependencies: 1775 8
   1.138  -- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: -
   1.139  --
   1.140  
   1.141 @@ -147,8 +147,8 @@
   1.142  
   1.143  
   1.144  --
   1.145 --- TOC entry 1552 (class 1259 OID 16631)
   1.146 --- Dependencies: 1631 8
   1.147 +-- TOC entry 1660 (class 1259 OID 16631)
   1.148 +-- Dependencies: 1777 8
   1.149  -- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: -
   1.150  --
   1.151  
   1.152 @@ -157,8 +157,8 @@
   1.153  
   1.154  
   1.155  --
   1.156 --- TOC entry 1553 (class 1259 OID 16695)
   1.157 --- Dependencies: 1632 8
   1.158 +-- TOC entry 1661 (class 1259 OID 16695)
   1.159 +-- Dependencies: 1778 8
   1.160  -- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: -
   1.161  --
   1.162  
   1.163 @@ -167,8 +167,8 @@
   1.164  
   1.165  
   1.166  --
   1.167 --- TOC entry 1551 (class 1259 OID 16607)
   1.168 --- Dependencies: 1630 8
   1.169 +-- TOC entry 1659 (class 1259 OID 16607)
   1.170 +-- Dependencies: 1776 8
   1.171  -- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: -
   1.172  --
   1.173  
   1.174 @@ -177,18 +177,18 @@
   1.175  
   1.176  
   1.177  --
   1.178 --- TOC entry 1829 (class 2606 OID 16557)
   1.179 --- Dependencies: 1549 1549 1549 1549
   1.180 +-- TOC entry 1975 (class 2606 OID 17071)
   1.181 +-- Dependencies: 1657 1657 1657 1657 1657
   1.182  -- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
   1.183  --
   1.184  
   1.185  ALTER TABLE ONLY alias
   1.186 -    ADD CONSTRAINT aliasy_pk PRIMARY KEY (zdroj_jmeno, zdroj_domena, cil_jmeno);
   1.187 +    ADD CONSTRAINT aliasy_pk PRIMARY KEY (zdroj_jmeno, zdroj_domena, cil_jmeno, cil_domena);
   1.188  
   1.189  
   1.190  --
   1.191 --- TOC entry 1825 (class 2606 OID 16400)
   1.192 --- Dependencies: 1536 1536
   1.193 +-- TOC entry 1971 (class 2606 OID 16400)
   1.194 +-- Dependencies: 1644 1644
   1.195  -- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
   1.196  --
   1.197  
   1.198 @@ -197,8 +197,8 @@
   1.199  
   1.200  
   1.201  --
   1.202 --- TOC entry 1827 (class 2606 OID 16416)
   1.203 --- Dependencies: 1537 1537 1537
   1.204 +-- TOC entry 1973 (class 2606 OID 16416)
   1.205 +-- Dependencies: 1645 1645 1645
   1.206  -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
   1.207  --
   1.208  
   1.209 @@ -207,8 +207,8 @@
   1.210  
   1.211  
   1.212  --
   1.213 --- TOC entry 1831 (class 2606 OID 16558)
   1.214 --- Dependencies: 1549 1824 1536
   1.215 +-- TOC entry 1977 (class 2606 OID 16558)
   1.216 +-- Dependencies: 1657 1970 1644
   1.217  -- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
   1.218  --
   1.219  
   1.220 @@ -217,8 +217,8 @@
   1.221  
   1.222  
   1.223  --
   1.224 --- TOC entry 1830 (class 2606 OID 16417)
   1.225 --- Dependencies: 1536 1824 1537
   1.226 +-- TOC entry 1976 (class 2606 OID 16417)
   1.227 +-- Dependencies: 1644 1645 1970
   1.228  -- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
   1.229  --
   1.230  
   1.231 @@ -227,7 +227,7 @@
   1.232  
   1.233  
   1.234  --
   1.235 --- TOC entry 1834 (class 0 OID 0)
   1.236 +-- TOC entry 1980 (class 0 OID 0)
   1.237  -- Dependencies: 8
   1.238  -- Name: posta; Type: ACL; Schema: -; Owner: -
   1.239  --
   1.240 @@ -240,8 +240,8 @@
   1.241  
   1.242  
   1.243  --
   1.244 --- TOC entry 1836 (class 0 OID 0)
   1.245 --- Dependencies: 55
   1.246 +-- TOC entry 1982 (class 0 OID 0)
   1.247 +-- Dependencies: 57
   1.248  -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: ACL; Schema: posta; Owner: -
   1.249  --
   1.250  
   1.251 @@ -253,8 +253,8 @@
   1.252  
   1.253  
   1.254  --
   1.255 --- TOC entry 1841 (class 0 OID 0)
   1.256 --- Dependencies: 1549
   1.257 +-- TOC entry 1987 (class 0 OID 0)
   1.258 +-- Dependencies: 1657
   1.259  -- Name: alias; Type: ACL; Schema: posta; Owner: -
   1.260  --
   1.261  
   1.262 @@ -264,8 +264,8 @@
   1.263  
   1.264  
   1.265  --
   1.266 --- TOC entry 1842 (class 0 OID 0)
   1.267 --- Dependencies: 1536
   1.268 +-- TOC entry 1988 (class 0 OID 0)
   1.269 +-- Dependencies: 1644
   1.270  -- Name: domena; Type: ACL; Schema: posta; Owner: -
   1.271  --
   1.272  
   1.273 @@ -276,8 +276,8 @@
   1.274  
   1.275  
   1.276  --
   1.277 --- TOC entry 1843 (class 0 OID 0)
   1.278 --- Dependencies: 1537
   1.279 +-- TOC entry 1989 (class 0 OID 0)
   1.280 +-- Dependencies: 1645
   1.281  -- Name: uzivatel; Type: ACL; Schema: posta; Owner: -
   1.282  --
   1.283  
   1.284 @@ -288,8 +288,8 @@
   1.285  
   1.286  
   1.287  --
   1.288 --- TOC entry 1844 (class 0 OID 0)
   1.289 --- Dependencies: 1550
   1.290 +-- TOC entry 1990 (class 0 OID 0)
   1.291 +-- Dependencies: 1658
   1.292  -- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: -
   1.293  --
   1.294  
   1.295 @@ -300,8 +300,8 @@
   1.296  
   1.297  
   1.298  --
   1.299 --- TOC entry 1845 (class 0 OID 0)
   1.300 --- Dependencies: 1552
   1.301 +-- TOC entry 1991 (class 0 OID 0)
   1.302 +-- Dependencies: 1660
   1.303  -- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: -
   1.304  --
   1.305  
   1.306 @@ -312,8 +312,8 @@
   1.307  
   1.308  
   1.309  --
   1.310 --- TOC entry 1846 (class 0 OID 0)
   1.311 --- Dependencies: 1553
   1.312 +-- TOC entry 1992 (class 0 OID 0)
   1.313 +-- Dependencies: 1661
   1.314  -- Name: postfix_alias; Type: ACL; Schema: posta; Owner: -
   1.315  --
   1.316  
   1.317 @@ -324,8 +324,8 @@
   1.318  
   1.319  
   1.320  --
   1.321 --- TOC entry 1847 (class 0 OID 0)
   1.322 --- Dependencies: 1551
   1.323 +-- TOC entry 1993 (class 0 OID 0)
   1.324 +-- Dependencies: 1659
   1.325  -- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: -
   1.326  --
   1.327  
   1.328 @@ -335,7 +335,7 @@
   1.329  GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
   1.330  
   1.331  
   1.332 --- Completed on 2009-05-29 21:13:23 CEST
   1.333 +-- Completed on 2009-08-28 17:21:17 CEST
   1.334  
   1.335  --
   1.336  -- PostgreSQL database dump complete