1.1 --- a/sql/schéma.sql Mon Jan 25 17:44:00 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