sql/schéma.sql
changeset 62 d9df3f7b67dd
parent 19 5d93bf1c8093
child 71 3829d619369c
     1.1 --- a/sql/schéma.sql	Tue Mar 09 20:46:54 2010 +0100
     1.2 +++ b/sql/schéma.sql	Tue Mar 09 20:54:58 2010 +0100
     1.3 @@ -2,7 +2,7 @@
     1.4  -- PostgreSQL database dump
     1.5  --
     1.6  
     1.7 --- Started on 2010-01-25 17:42:09 CET
     1.8 +-- Started on 2010-03-09 20:48:44 CET
     1.9  
    1.10  SET statement_timeout = 0;
    1.11  SET client_encoding = 'UTF8';
    1.12 @@ -24,7 +24,87 @@
    1.13  SET search_path = nekurak, pg_catalog;
    1.14  
    1.15  --
    1.16 --- TOC entry 1568 (class 1259 OID 35019)
    1.17 +-- TOC entry 369 (class 1247 OID 35086)
    1.18 +-- Dependencies: 370 10
    1.19 +-- Name: email; Type: DOMAIN; Schema: nekurak; Owner: nekurak
    1.20 +--
    1.21 +
    1.22 +CREATE DOMAIN email AS character varying(255)
    1.23 +	CONSTRAINT email_regexp CHECK (((VALUE)::text ~ '^[_a-zA-Z0-9\\.\\-]+@[_a-zA-Z0-9\\.\\-]+\\.[a-zA-Z]{2,4}$'::text));
    1.24 +
    1.25 +
    1.26 +ALTER DOMAIN nekurak.email OWNER TO nekurak;
    1.27 +
    1.28 +--
    1.29 +-- TOC entry 1881 (class 0 OID 0)
    1.30 +-- Dependencies: 369
    1.31 +-- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak
    1.32 +--
    1.33 +
    1.34 +COMMENT ON DOMAIN email IS 'e-mailová adresa';
    1.35 +
    1.36 +
    1.37 +--
    1.38 +-- TOC entry 65 (class 1255 OID 35124)
    1.39 +-- Dependencies: 10 392
    1.40 +-- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak
    1.41 +--
    1.42 +
    1.43 +CREATE FUNCTION bezny_uzivatel() RETURNS trigger
    1.44 +    LANGUAGE plpgsql
    1.45 +    AS $$BEGIN
    1.46 +INSERT INTO uzivatel_role 
    1.47 +(role, prezdivka) 
    1.48 +VALUES
    1.49 +('bezny', new.prezdivka);
    1.50 +RETURN new;
    1.51 +END;$$;
    1.52 +
    1.53 +
    1.54 +ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak;
    1.55 +
    1.56 +--
    1.57 +-- TOC entry 1882 (class 0 OID 0)
    1.58 +-- Dependencies: 65
    1.59 +-- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak
    1.60 +--
    1.61 +
    1.62 +COMMENT ON FUNCTION bezny_uzivatel() IS 'Každému novému uživateli dáme automaticky roli „bezny“.';
    1.63 +
    1.64 +
    1.65 +SET default_tablespace = '';
    1.66 +
    1.67 +SET default_with_oids = true;
    1.68 +
    1.69 +--
    1.70 +-- TOC entry 1584 (class 1259 OID 35068)
    1.71 +-- Dependencies: 1861 10
    1.72 +-- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
    1.73 +--
    1.74 +
    1.75 +CREATE TABLE hlasovani (
    1.76 +    podnik integer NOT NULL,
    1.77 +    hlas boolean NOT NULL,
    1.78 +    datum date DEFAULT now() NOT NULL,
    1.79 +    ip_adresa character varying(255)
    1.80 +);
    1.81 +
    1.82 +
    1.83 +ALTER TABLE nekurak.hlasovani OWNER TO nekurak;
    1.84 +
    1.85 +--
    1.86 +-- TOC entry 1883 (class 0 OID 0)
    1.87 +-- Dependencies: 1584
    1.88 +-- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak
    1.89 +--
    1.90 +
    1.91 +COMMENT ON COLUMN hlasovani.hlas IS 'Má být tento podnik nekuřácký?
    1.92 +true = nekuřácký
    1.93 +false = kouření povoleno';
    1.94 +
    1.95 +
    1.96 +--
    1.97 +-- TOC entry 1582 (class 1259 OID 35019)
    1.98  -- Dependencies: 10
    1.99  -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
   1.100  --
   1.101 @@ -38,50 +118,120 @@
   1.102  
   1.103  ALTER TABLE nekurak.podnik_seq OWNER TO nekurak;
   1.104  
   1.105 ---
   1.106 --- TOC entry 1848 (class 0 OID 0)
   1.107 --- Dependencies: 1568
   1.108 --- Name: podnik_seq; Type: SEQUENCE SET; Schema: nekurak; Owner: nekurak
   1.109 ---
   1.110 -
   1.111 -SELECT pg_catalog.setval('podnik_seq', 3, true);
   1.112 -
   1.113 -
   1.114 -SET default_tablespace = '';
   1.115 -
   1.116  SET default_with_oids = false;
   1.117  
   1.118  --
   1.119 --- TOC entry 1569 (class 1259 OID 35021)
   1.120 --- Dependencies: 1840 1841 10
   1.121 +-- TOC entry 1583 (class 1259 OID 35049)
   1.122 +-- Dependencies: 1859 1860 10
   1.123  -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   1.124  --
   1.125  
   1.126  CREATE TABLE podnik (
   1.127      id integer DEFAULT nextval('podnik_seq'::regclass) NOT NULL,
   1.128      nazev character varying(255) NOT NULL,
   1.129 -    datum timestamp with time zone DEFAULT now()
   1.130 +    popis character varying(255),
   1.131 +    url character varying(255),
   1.132 +    datum timestamp with time zone DEFAULT now(),
   1.133 +    ulice character varying(64),
   1.134 +    cislo_popisne integer,
   1.135 +    mesto character varying(64)
   1.136  );
   1.137  
   1.138  
   1.139  ALTER TABLE nekurak.podnik OWNER TO nekurak;
   1.140  
   1.141  --
   1.142 --- TOC entry 1844 (class 0 OID 35021)
   1.143 --- Dependencies: 1569
   1.144 --- Data for Name: podnik; Type: TABLE DATA; Schema: nekurak; Owner: nekurak
   1.145 +-- TOC entry 1587 (class 1259 OID 35101)
   1.146 +-- Dependencies: 10
   1.147 +-- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   1.148  --
   1.149  
   1.150 -COPY podnik (id, nazev, datum) FROM stdin;
   1.151 -1	Na Kovárně	2010-01-16 02:40:12.271326+01
   1.152 -2	U přátelského potkana	2010-01-16 02:40:43.326281+01
   1.153 -3	Na Růžku	2010-01-16 02:40:48.104577+01
   1.154 -\.
   1.155 +CREATE TABLE role (
   1.156 +    kod character varying(16) NOT NULL,
   1.157 +    popis character varying(255)
   1.158 +);
   1.159  
   1.160  
   1.161 +ALTER TABLE nekurak.role OWNER TO nekurak;
   1.162 +
   1.163  --
   1.164 --- TOC entry 1843 (class 2606 OID 35027)
   1.165 --- Dependencies: 1569 1569
   1.166 +-- TOC entry 1887 (class 0 OID 0)
   1.167 +-- Dependencies: 1587
   1.168 +-- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
   1.169 +--
   1.170 +
   1.171 +COMMENT ON TABLE role IS 'Role (skupiny) uživatelů ~ oprávnění';
   1.172 +
   1.173 +
   1.174 +--
   1.175 +-- TOC entry 1585 (class 1259 OID 35080)
   1.176 +-- Dependencies: 10
   1.177 +-- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
   1.178 +--
   1.179 +
   1.180 +CREATE SEQUENCE uzivatel_seq
   1.181 +    INCREMENT BY 1
   1.182 +    NO MAXVALUE
   1.183 +    NO MINVALUE
   1.184 +    CACHE 1;
   1.185 +
   1.186 +
   1.187 +ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak;
   1.188 +
   1.189 +--
   1.190 +-- TOC entry 1586 (class 1259 OID 35091)
   1.191 +-- Dependencies: 1862 1863 10
   1.192 +-- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   1.193 +--
   1.194 +
   1.195 +CREATE TABLE uzivatel (
   1.196 +    id integer DEFAULT nextval('uzivatel_seq'::regclass) NOT NULL,
   1.197 +    prezdivka character varying(64) NOT NULL,
   1.198 +    heslo character varying(512) NOT NULL,
   1.199 +    jmeno character varying(64),
   1.200 +    prijmeni character varying(64),
   1.201 +    email character varying(255),
   1.202 +    datum timestamp with time zone DEFAULT now() NOT NULL
   1.203 +);
   1.204 +
   1.205 +
   1.206 +ALTER TABLE nekurak.uzivatel OWNER TO nekurak;
   1.207 +
   1.208 +--
   1.209 +-- TOC entry 1890 (class 0 OID 0)
   1.210 +-- Dependencies: 1586
   1.211 +-- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
   1.212 +--
   1.213 +
   1.214 +COMMENT ON COLUMN uzivatel.prezdivka IS 'Uživatelské jméno';
   1.215 +
   1.216 +
   1.217 +--
   1.218 +-- TOC entry 1588 (class 1259 OID 35106)
   1.219 +-- Dependencies: 10
   1.220 +-- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   1.221 +--
   1.222 +
   1.223 +CREATE TABLE uzivatel_role (
   1.224 +    role character varying(16) NOT NULL,
   1.225 +    prezdivka character varying(64) NOT NULL
   1.226 +);
   1.227 +
   1.228 +
   1.229 +ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak;
   1.230 +
   1.231 +--
   1.232 +-- TOC entry 1892 (class 0 OID 0)
   1.233 +-- Dependencies: 1588
   1.234 +-- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak
   1.235 +--
   1.236 +
   1.237 +COMMENT ON TABLE uzivatel_role IS 'Přiřazení uživatelských rolí (skupin).';
   1.238 +
   1.239 +
   1.240 +--
   1.241 +-- TOC entry 1865 (class 2606 OID 35058)
   1.242 +-- Dependencies: 1583 1583
   1.243  -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   1.244  --
   1.245  
   1.246 @@ -90,7 +240,89 @@
   1.247  
   1.248  
   1.249  --
   1.250 --- TOC entry 1847 (class 0 OID 0)
   1.251 +-- TOC entry 1871 (class 2606 OID 35105)
   1.252 +-- Dependencies: 1587 1587
   1.253 +-- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   1.254 +--
   1.255 +
   1.256 +ALTER TABLE ONLY role
   1.257 +    ADD CONSTRAINT skupina_pk PRIMARY KEY (kod);
   1.258 +
   1.259 +
   1.260 +--
   1.261 +-- TOC entry 1867 (class 2606 OID 35098)
   1.262 +-- Dependencies: 1586 1586
   1.263 +-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   1.264 +--
   1.265 +
   1.266 +ALTER TABLE ONLY uzivatel
   1.267 +    ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id);
   1.268 +
   1.269 +
   1.270 +--
   1.271 +-- TOC entry 1869 (class 2606 OID 35100)
   1.272 +-- Dependencies: 1586 1586
   1.273 +-- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   1.274 +--
   1.275 +
   1.276 +ALTER TABLE ONLY uzivatel
   1.277 +    ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka);
   1.278 +
   1.279 +
   1.280 +--
   1.281 +-- TOC entry 1873 (class 2606 OID 35137)
   1.282 +-- Dependencies: 1588 1588 1588
   1.283 +-- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   1.284 +--
   1.285 +
   1.286 +ALTER TABLE ONLY uzivatel_role
   1.287 +    ADD CONSTRAINT uzivatel_role_pk PRIMARY KEY (role, prezdivka);
   1.288 +
   1.289 +
   1.290 +--
   1.291 +-- TOC entry 1877 (class 2620 OID 35125)
   1.292 +-- Dependencies: 65 1586
   1.293 +-- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak
   1.294 +--
   1.295 +
   1.296 +CREATE TRIGGER uzivatel_insert
   1.297 +    AFTER INSERT ON uzivatel
   1.298 +    FOR EACH ROW
   1.299 +    EXECUTE PROCEDURE bezny_uzivatel();
   1.300 +
   1.301 +
   1.302 +--
   1.303 +-- TOC entry 1874 (class 2606 OID 35072)
   1.304 +-- Dependencies: 1864 1583 1584
   1.305 +-- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   1.306 +--
   1.307 +
   1.308 +ALTER TABLE ONLY hlasovani
   1.309 +    ADD CONSTRAINT hlasovani_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE;
   1.310 +
   1.311 +
   1.312 +--
   1.313 +-- TOC entry 1875 (class 2606 OID 35138)
   1.314 +-- Dependencies: 1870 1587 1588
   1.315 +-- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   1.316 +--
   1.317 +
   1.318 +ALTER TABLE ONLY uzivatel_role
   1.319 +    ADD CONSTRAINT uzivatel_role_role_fk FOREIGN KEY (role) REFERENCES role(kod) ON UPDATE CASCADE ON DELETE CASCADE;
   1.320 +
   1.321 +
   1.322 +--
   1.323 +-- TOC entry 1876 (class 2606 OID 35143)
   1.324 +-- Dependencies: 1868 1586 1588
   1.325 +-- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   1.326 +--
   1.327 +
   1.328 +ALTER TABLE ONLY uzivatel_role
   1.329 +    ADD CONSTRAINT uzivatel_role_uzivatel_fk FOREIGN KEY (prezdivka) REFERENCES uzivatel(prezdivka) ON UPDATE CASCADE ON DELETE CASCADE;
   1.330 +
   1.331 +
   1.332 +--
   1.333 +-- TOC entry 1880 (class 0 OID 0)
   1.334  -- Dependencies: 10
   1.335  -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
   1.336  --
   1.337 @@ -102,8 +334,20 @@
   1.338  
   1.339  
   1.340  --
   1.341 --- TOC entry 1849 (class 0 OID 0)
   1.342 --- Dependencies: 1568
   1.343 +-- TOC entry 1884 (class 0 OID 0)
   1.344 +-- Dependencies: 1584
   1.345 +-- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak
   1.346 +--
   1.347 +
   1.348 +REVOKE ALL ON TABLE hlasovani FROM PUBLIC;
   1.349 +REVOKE ALL ON TABLE hlasovani FROM nekurak;
   1.350 +GRANT ALL ON TABLE hlasovani TO nekurak;
   1.351 +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE hlasovani TO nekurak_web;
   1.352 +
   1.353 +
   1.354 +--
   1.355 +-- TOC entry 1885 (class 0 OID 0)
   1.356 +-- Dependencies: 1582
   1.357  -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
   1.358  --
   1.359  
   1.360 @@ -114,8 +358,8 @@
   1.361  
   1.362  
   1.363  --
   1.364 --- TOC entry 1850 (class 0 OID 0)
   1.365 --- Dependencies: 1569
   1.366 +-- TOC entry 1886 (class 0 OID 0)
   1.367 +-- Dependencies: 1583
   1.368  -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
   1.369  --
   1.370  
   1.371 @@ -125,7 +369,55 @@
   1.372  GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE podnik TO nekurak_web;
   1.373  
   1.374  
   1.375 --- Completed on 2010-01-25 17:42:10 CET
   1.376 +--
   1.377 +-- TOC entry 1888 (class 0 OID 0)
   1.378 +-- Dependencies: 1587
   1.379 +-- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak
   1.380 +--
   1.381 +
   1.382 +REVOKE ALL ON TABLE role FROM PUBLIC;
   1.383 +REVOKE ALL ON TABLE role FROM nekurak;
   1.384 +GRANT ALL ON TABLE role TO nekurak;
   1.385 +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE role TO nekurak_web;
   1.386 +
   1.387 +
   1.388 +--
   1.389 +-- TOC entry 1889 (class 0 OID 0)
   1.390 +-- Dependencies: 1585
   1.391 +-- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak
   1.392 +--
   1.393 +
   1.394 +REVOKE ALL ON SEQUENCE uzivatel_seq FROM PUBLIC;
   1.395 +REVOKE ALL ON SEQUENCE uzivatel_seq FROM nekurak;
   1.396 +GRANT ALL ON SEQUENCE uzivatel_seq TO nekurak;
   1.397 +GRANT USAGE ON SEQUENCE uzivatel_seq TO nekurak_web;
   1.398 +
   1.399 +
   1.400 +--
   1.401 +-- TOC entry 1891 (class 0 OID 0)
   1.402 +-- Dependencies: 1586
   1.403 +-- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak
   1.404 +--
   1.405 +
   1.406 +REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
   1.407 +REVOKE ALL ON TABLE uzivatel FROM nekurak;
   1.408 +GRANT ALL ON TABLE uzivatel TO nekurak;
   1.409 +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel TO nekurak_web;
   1.410 +
   1.411 +
   1.412 +--
   1.413 +-- TOC entry 1893 (class 0 OID 0)
   1.414 +-- Dependencies: 1588
   1.415 +-- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak
   1.416 +--
   1.417 +
   1.418 +REVOKE ALL ON TABLE uzivatel_role FROM PUBLIC;
   1.419 +REVOKE ALL ON TABLE uzivatel_role FROM nekurak;
   1.420 +GRANT ALL ON TABLE uzivatel_role TO nekurak;
   1.421 +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web;
   1.422 +
   1.423 +
   1.424 +-- Completed on 2010-03-09 20:48:46 CET
   1.425  
   1.426  --
   1.427  -- PostgreSQL database dump complete