sql/schéma.sql
author František Kučera <franta-hg@frantovo.cz>
Tue, 05 May 2009 17:08:09 +0200
changeset 3 74a6a363ff9e
parent 0 c83d559ca362
child 12 38aadc7c6386
permissions -rw-r--r--
Převzatá struktura z BG
http://sorry.vse.cz/~xkucf03/skola.php
     1 --
     2 -- PostgreSQL database dump
     3 --
     4 
     5 -- Started on 2009-05-05 16:25:08 CEST
     6 
     7 SET client_encoding = 'UTF8';
     8 SET standard_conforming_strings = off;
     9 SET check_function_bodies = false;
    10 SET client_min_messages = warning;
    11 SET escape_string_warning = off;
    12 
    13 --
    14 -- TOC entry 6 (class 2615 OID 16387)
    15 -- Name: posta; Type: SCHEMA; Schema: -; Owner: -
    16 --
    17 
    18 CREATE SCHEMA posta;
    19 
    20 
    21 SET search_path = posta, pg_catalog;
    22 
    23 --
    24 -- TOC entry 22 (class 1255 OID 16646)
    25 -- Dependencies: 6
    26 -- Name: zmen_heslo(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: posta; Owner: -
    27 --
    28 
    29 CREATE FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying) RETURNS void
    30     AS $_$UPDATE uzivatel
    31  SET heslo = md5($4)
    32  WHERE 	login = $1
    33 	AND domena = $2
    34 	AND heslo = md5($3);$_$
    35     LANGUAGE sql SECURITY DEFINER;
    36 
    37 
    38 --
    39 -- TOC entry 1801 (class 0 OID 0)
    40 -- Dependencies: 22
    41 -- Name: FUNCTION zmen_heslo(login character varying, domena character varying, stare_heslo character varying, nove_heslo character varying); Type: COMMENT; Schema: posta; Owner: -
    42 --
    43 
    44 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.';
    45 
    46 
    47 SET default_tablespace = '';
    48 
    49 SET default_with_oids = false;
    50 
    51 --
    52 -- TOC entry 1515 (class 1259 OID 16550)
    53 -- Dependencies: 6
    54 -- Name: alias; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
    55 --
    56 
    57 CREATE TABLE alias (
    58     login character varying(255) NOT NULL,
    59     domena character varying(255) NOT NULL,
    60     cilova_adresa character varying(255) NOT NULL
    61 );
    62 
    63 
    64 --
    65 -- TOC entry 1502 (class 1259 OID 16396)
    66 -- Dependencies: 6
    67 -- Name: domena; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
    68 --
    69 
    70 CREATE TABLE domena (
    71     domena character varying(255) NOT NULL
    72 );
    73 
    74 
    75 --
    76 -- TOC entry 1503 (class 1259 OID 16409)
    77 -- Dependencies: 6
    78 -- Name: uzivatel; Type: TABLE; Schema: posta; Owner: -; Tablespace: 
    79 --
    80 
    81 CREATE TABLE uzivatel (
    82     login character varying(255) NOT NULL,
    83     heslo character varying(512),
    84     domena character varying(255) NOT NULL
    85 );
    86 
    87 
    88 --
    89 -- TOC entry 1517 (class 1259 OID 16602)
    90 -- Dependencies: 1596 6
    91 -- Name: dovecot_heslo; Type: VIEW; Schema: posta; Owner: -
    92 --
    93 
    94 CREATE VIEW dovecot_heslo AS
    95     SELECT (((uzivatel.login)::text || '@'::text) || (uzivatel.domena)::text) AS "user", uzivatel.heslo AS password, uzivatel.login, uzivatel.domena FROM uzivatel;
    96 
    97 
    98 --
    99 -- TOC entry 1519 (class 1259 OID 16631)
   100 -- Dependencies: 1598 6
   101 -- Name: dovecot_uzivatel; Type: VIEW; Schema: posta; Owner: -
   102 --
   103 
   104 CREATE VIEW dovecot_uzivatel AS
   105     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;
   106 
   107 
   108 --
   109 -- TOC entry 1516 (class 1259 OID 16563)
   110 -- Dependencies: 1595 6
   111 -- Name: postfix_alias; Type: VIEW; Schema: posta; Owner: -
   112 --
   113 
   114 CREATE VIEW postfix_alias AS
   115     SELECT (((alias.login)::text || '@'::text) || (alias.domena)::text) AS zdroj, alias.cilova_adresa AS cil FROM alias;
   116 
   117 
   118 --
   119 -- TOC entry 1518 (class 1259 OID 16607)
   120 -- Dependencies: 1597 6
   121 -- Name: postfix_uzivatel; Type: VIEW; Schema: posta; Owner: -
   122 --
   123 
   124 CREATE VIEW postfix_uzivatel AS
   125     SELECT ((((uzivatel.domena)::text || '/'::text) || (uzivatel.login)::text) || '/Maildir/'::text) AS cesta, uzivatel.login, uzivatel.domena FROM uzivatel;
   126 
   127 
   128 --
   129 -- TOC entry 1795 (class 2606 OID 16557)
   130 -- Dependencies: 1515 1515 1515 1515
   131 -- Name: aliasy_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
   132 --
   133 
   134 ALTER TABLE ONLY alias
   135     ADD CONSTRAINT aliasy_pk PRIMARY KEY (login, domena, cilova_adresa);
   136 
   137 
   138 --
   139 -- TOC entry 1791 (class 2606 OID 16400)
   140 -- Dependencies: 1502 1502
   141 -- Name: domena_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
   142 --
   143 
   144 ALTER TABLE ONLY domena
   145     ADD CONSTRAINT domena_pk PRIMARY KEY (domena);
   146 
   147 
   148 --
   149 -- TOC entry 1793 (class 2606 OID 16416)
   150 -- Dependencies: 1503 1503 1503
   151 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: posta; Owner: -; Tablespace: 
   152 --
   153 
   154 ALTER TABLE ONLY uzivatel
   155     ADD CONSTRAINT uzivatel_pk PRIMARY KEY (login, domena);
   156 
   157 
   158 --
   159 -- TOC entry 1797 (class 2606 OID 16558)
   160 -- Dependencies: 1790 1515 1502
   161 -- Name: alias_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
   162 --
   163 
   164 ALTER TABLE ONLY alias
   165     ADD CONSTRAINT alias_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
   166 
   167 
   168 --
   169 -- TOC entry 1796 (class 2606 OID 16417)
   170 -- Dependencies: 1503 1502 1790
   171 -- Name: uzivatel_domena_fk; Type: FK CONSTRAINT; Schema: posta; Owner: -
   172 --
   173 
   174 ALTER TABLE ONLY uzivatel
   175     ADD CONSTRAINT uzivatel_domena_fk FOREIGN KEY (domena) REFERENCES domena(domena);
   176 
   177 
   178 --
   179 -- TOC entry 1800 (class 0 OID 0)
   180 -- Dependencies: 6
   181 -- Name: posta; Type: ACL; Schema: -; Owner: -
   182 --
   183 
   184 REVOKE ALL ON SCHEMA posta FROM PUBLIC;
   185 REVOKE ALL ON SCHEMA posta FROM posta;
   186 GRANT ALL ON SCHEMA posta TO posta;
   187 GRANT USAGE ON SCHEMA posta TO posta_cteni;
   188 
   189 
   190 --
   191 -- TOC entry 1802 (class 0 OID 0)
   192 -- Dependencies: 1515
   193 -- Name: alias; Type: ACL; Schema: posta; Owner: -
   194 --
   195 
   196 REVOKE ALL ON TABLE alias FROM PUBLIC;
   197 REVOKE ALL ON TABLE alias FROM posta;
   198 GRANT ALL ON TABLE alias TO posta;
   199 
   200 
   201 --
   202 -- TOC entry 1803 (class 0 OID 0)
   203 -- Dependencies: 1502
   204 -- Name: domena; Type: ACL; Schema: posta; Owner: -
   205 --
   206 
   207 REVOKE ALL ON TABLE domena FROM PUBLIC;
   208 REVOKE ALL ON TABLE domena FROM posta;
   209 GRANT ALL ON TABLE domena TO posta;
   210 GRANT SELECT ON TABLE domena TO posta_cteni;
   211 
   212 
   213 --
   214 -- TOC entry 1804 (class 0 OID 0)
   215 -- Dependencies: 1503
   216 -- Name: uzivatel; Type: ACL; Schema: posta; Owner: -
   217 --
   218 
   219 REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
   220 REVOKE ALL ON TABLE uzivatel FROM posta;
   221 GRANT ALL ON TABLE uzivatel TO posta;
   222 GRANT SELECT ON TABLE uzivatel TO posta_cteni;
   223 
   224 
   225 --
   226 -- TOC entry 1805 (class 0 OID 0)
   227 -- Dependencies: 1517
   228 -- Name: dovecot_heslo; Type: ACL; Schema: posta; Owner: -
   229 --
   230 
   231 REVOKE ALL ON TABLE dovecot_heslo FROM PUBLIC;
   232 REVOKE ALL ON TABLE dovecot_heslo FROM posta;
   233 GRANT ALL ON TABLE dovecot_heslo TO posta;
   234 GRANT SELECT ON TABLE dovecot_heslo TO posta_cteni;
   235 
   236 
   237 --
   238 -- TOC entry 1806 (class 0 OID 0)
   239 -- Dependencies: 1519
   240 -- Name: dovecot_uzivatel; Type: ACL; Schema: posta; Owner: -
   241 --
   242 
   243 REVOKE ALL ON TABLE dovecot_uzivatel FROM PUBLIC;
   244 REVOKE ALL ON TABLE dovecot_uzivatel FROM posta;
   245 GRANT ALL ON TABLE dovecot_uzivatel TO posta;
   246 GRANT SELECT ON TABLE dovecot_uzivatel TO posta_cteni;
   247 
   248 
   249 --
   250 -- TOC entry 1807 (class 0 OID 0)
   251 -- Dependencies: 1516
   252 -- Name: postfix_alias; Type: ACL; Schema: posta; Owner: -
   253 --
   254 
   255 REVOKE ALL ON TABLE postfix_alias FROM PUBLIC;
   256 REVOKE ALL ON TABLE postfix_alias FROM posta;
   257 GRANT ALL ON TABLE postfix_alias TO posta;
   258 GRANT SELECT ON TABLE postfix_alias TO posta_cteni;
   259 
   260 
   261 --
   262 -- TOC entry 1808 (class 0 OID 0)
   263 -- Dependencies: 1518
   264 -- Name: postfix_uzivatel; Type: ACL; Schema: posta; Owner: -
   265 --
   266 
   267 REVOKE ALL ON TABLE postfix_uzivatel FROM PUBLIC;
   268 REVOKE ALL ON TABLE postfix_uzivatel FROM posta;
   269 GRANT ALL ON TABLE postfix_uzivatel TO posta;
   270 GRANT SELECT ON TABLE postfix_uzivatel TO posta_cteni;
   271 
   272 
   273 -- Completed on 2009-05-05 16:25:18 CEST
   274 
   275 --
   276 -- PostgreSQL database dump complete
   277 --
   278