Hlasování: DAO a EJB vrstva pro ukládání hlasů, kostra pro načítání výsledků hlasování.
2 -- PostgreSQL database dump
5 -- Started on 2010-03-16 14:32:22 CET
7 SET statement_timeout = 0;
8 SET client_encoding = 'UTF8';
9 SET standard_conforming_strings = off;
10 SET check_function_bodies = false;
11 SET client_min_messages = warning;
12 SET escape_string_warning = off;
15 -- TOC entry 10 (class 2615 OID 35015)
16 -- Name: nekurak; Type: SCHEMA; Schema: -; Owner: nekurak
19 CREATE SCHEMA nekurak;
22 ALTER SCHEMA nekurak OWNER TO nekurak;
24 SET search_path = nekurak, pg_catalog;
27 -- TOC entry 368 (class 1247 OID 35086)
28 -- Dependencies: 369 10
29 -- Name: email; Type: DOMAIN; Schema: nekurak; Owner: nekurak
32 CREATE DOMAIN email AS character varying(255)
33 CONSTRAINT email_regexp CHECK (((VALUE)::text ~ '^[_a-zA-Z0-9\\.\\-]+@[_a-zA-Z0-9\\.\\-]+\\.[a-zA-Z]{2,4}$'::text));
36 ALTER DOMAIN nekurak.email OWNER TO nekurak;
39 -- TOC entry 1894 (class 0 OID 0)
41 -- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak
44 COMMENT ON DOMAIN email IS 'e-mailová adresa';
48 -- TOC entry 65 (class 1255 OID 35124)
49 -- Dependencies: 10 395
50 -- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak
53 CREATE FUNCTION bezny_uzivatel() RETURNS trigger
56 INSERT INTO uzivatel_role
59 ('bezny', new.prezdivka);
64 ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak;
67 -- TOC entry 1895 (class 0 OID 0)
69 -- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak
72 COMMENT ON FUNCTION bezny_uzivatel() IS 'Každému novému uživateli dáme automaticky roli „bezny“.';
76 -- TOC entry 1592 (class 1259 OID 35183)
78 -- Name: fotka_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
81 CREATE SEQUENCE fotka_seq
89 ALTER TABLE nekurak.fotka_seq OWNER TO nekurak;
91 SET default_tablespace = '';
93 SET default_with_oids = false;
96 -- TOC entry 1593 (class 1259 OID 35185)
97 -- Dependencies: 1869 1870 10
98 -- Name: fotka; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace:
102 id integer DEFAULT nextval('fotka_seq'::regclass) NOT NULL,
103 podnik integer NOT NULL,
104 popis character varying(255),
105 poradi integer DEFAULT 0 NOT NULL
109 ALTER TABLE nekurak.fotka OWNER TO nekurak;
112 -- TOC entry 1897 (class 0 OID 0)
113 -- Dependencies: 1593
114 -- Name: TABLE fotka; Type: COMMENT; Schema: nekurak; Owner: nekurak
117 COMMENT ON TABLE fotka IS 'Fotka podniku.';
121 -- TOC entry 1898 (class 0 OID 0)
122 -- Dependencies: 1593
123 -- Name: COLUMN fotka.poradi; Type: COMMENT; Schema: nekurak; Owner: nekurak
126 COMMENT ON COLUMN fotka.poradi IS 'Pořadí fotky v rámci daného podniku.';
129 SET default_with_oids = true;
132 -- TOC entry 1587 (class 1259 OID 35068)
133 -- Dependencies: 1866 10
134 -- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace:
137 CREATE TABLE hlasovani (
138 podnik integer NOT NULL,
139 hlas boolean NOT NULL,
140 datum date DEFAULT now() NOT NULL,
141 ip_adresa character varying(255)
145 ALTER TABLE nekurak.hlasovani OWNER TO nekurak;
148 -- TOC entry 1900 (class 0 OID 0)
149 -- Dependencies: 1587
150 -- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak
153 COMMENT ON COLUMN hlasovani.hlas IS 'Má být tento podnik nekuřácký?
155 false = kouření povoleno';
159 -- TOC entry 1585 (class 1259 OID 35019)
161 -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
164 CREATE SEQUENCE podnik_seq
171 ALTER TABLE nekurak.podnik_seq OWNER TO nekurak;
173 SET default_with_oids = false;
176 -- TOC entry 1586 (class 1259 OID 35049)
177 -- Dependencies: 1864 1865 10
178 -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace:
181 CREATE TABLE podnik (
182 id integer DEFAULT nextval('podnik_seq'::regclass) NOT NULL,
183 nazev character varying(255) NOT NULL,
184 popis character varying(255),
185 url character varying(255),
186 datum timestamp with time zone DEFAULT now(),
187 ulice character varying(64),
188 cislo_popisne integer,
189 mesto character varying(64),
190 spravce character varying(64)
194 ALTER TABLE nekurak.podnik OWNER TO nekurak;
197 -- TOC entry 1903 (class 0 OID 0)
198 -- Dependencies: 1586
199 -- Name: COLUMN podnik.spravce; Type: COMMENT; Schema: nekurak; Owner: nekurak
202 COMMENT ON COLUMN podnik.spravce IS 'Přezdívka uživatele, který je správcem tohoto podniku (zadal ho do systému nebo správcovství později dostal)';
206 -- TOC entry 1590 (class 1259 OID 35101)
208 -- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace:
212 kod character varying(16) NOT NULL,
213 popis character varying(255)
217 ALTER TABLE nekurak.role OWNER TO nekurak;
220 -- TOC entry 1905 (class 0 OID 0)
221 -- Dependencies: 1590
222 -- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
225 COMMENT ON TABLE role IS 'Role (skupiny) uživatelů ~ oprávnění';
229 -- TOC entry 1588 (class 1259 OID 35080)
231 -- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
234 CREATE SEQUENCE uzivatel_seq
241 ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak;
244 -- TOC entry 1589 (class 1259 OID 35091)
245 -- Dependencies: 1867 1868 10
246 -- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace:
249 CREATE TABLE uzivatel (
250 id integer DEFAULT nextval('uzivatel_seq'::regclass) NOT NULL,
251 prezdivka character varying(64) NOT NULL,
252 heslo character varying(512) NOT NULL,
253 jmeno character varying(64),
254 prijmeni character varying(64),
255 email character varying(255),
256 datum timestamp with time zone DEFAULT now() NOT NULL
260 ALTER TABLE nekurak.uzivatel OWNER TO nekurak;
263 -- TOC entry 1908 (class 0 OID 0)
264 -- Dependencies: 1589
265 -- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
268 COMMENT ON COLUMN uzivatel.prezdivka IS 'Uživatelské jméno';
272 -- TOC entry 1591 (class 1259 OID 35106)
274 -- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace:
277 CREATE TABLE uzivatel_role (
278 role character varying(16) NOT NULL,
279 prezdivka character varying(64) NOT NULL
283 ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak;
286 -- TOC entry 1910 (class 0 OID 0)
287 -- Dependencies: 1591
288 -- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak
291 COMMENT ON TABLE uzivatel_role IS 'Přiřazení uživatelských rolí (skupin).';
295 -- TOC entry 1882 (class 2606 OID 35190)
296 -- Dependencies: 1593 1593
297 -- Name: fotka_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace:
300 ALTER TABLE ONLY fotka
301 ADD CONSTRAINT fotka_pk PRIMARY KEY (id);
305 -- TOC entry 1884 (class 2606 OID 35204)
306 -- Dependencies: 1593 1593 1593
307 -- Name: fotka_poradi_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace:
310 ALTER TABLE ONLY fotka
311 ADD CONSTRAINT fotka_poradi_uq UNIQUE (popis, poradi);
315 -- TOC entry 1872 (class 2606 OID 35058)
316 -- Dependencies: 1586 1586
317 -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace:
320 ALTER TABLE ONLY podnik
321 ADD CONSTRAINT podnik_pk PRIMARY KEY (id);
325 -- TOC entry 1878 (class 2606 OID 35105)
326 -- Dependencies: 1590 1590
327 -- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace:
330 ALTER TABLE ONLY role
331 ADD CONSTRAINT skupina_pk PRIMARY KEY (kod);
335 -- TOC entry 1874 (class 2606 OID 35098)
336 -- Dependencies: 1589 1589
337 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace:
340 ALTER TABLE ONLY uzivatel
341 ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id);
345 -- TOC entry 1876 (class 2606 OID 35100)
346 -- Dependencies: 1589 1589
347 -- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace:
350 ALTER TABLE ONLY uzivatel
351 ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka);
355 -- TOC entry 1880 (class 2606 OID 35137)
356 -- Dependencies: 1591 1591 1591
357 -- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace:
360 ALTER TABLE ONLY uzivatel_role
361 ADD CONSTRAINT uzivatel_role_pk PRIMARY KEY (role, prezdivka);
365 -- TOC entry 1890 (class 2620 OID 35125)
366 -- Dependencies: 1589 65
367 -- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak
370 CREATE TRIGGER uzivatel_insert
371 AFTER INSERT ON uzivatel
373 EXECUTE PROCEDURE bezny_uzivatel();
377 -- TOC entry 1889 (class 2606 OID 35191)
378 -- Dependencies: 1871 1586 1593
379 -- Name: fotka_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
382 ALTER TABLE ONLY fotka
383 ADD CONSTRAINT fotka_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE;
387 -- TOC entry 1886 (class 2606 OID 35072)
388 -- Dependencies: 1587 1871 1586
389 -- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
392 ALTER TABLE ONLY hlasovani
393 ADD CONSTRAINT hlasovani_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE;
397 -- TOC entry 1885 (class 2606 OID 35178)
398 -- Dependencies: 1875 1589 1586
399 -- Name: podnik_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
402 ALTER TABLE ONLY podnik
403 ADD CONSTRAINT podnik_uzivatel_fk FOREIGN KEY (spravce) REFERENCES uzivatel(prezdivka) ON DELETE SET NULL;
407 -- TOC entry 1887 (class 2606 OID 35138)
408 -- Dependencies: 1590 1591 1877
409 -- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
412 ALTER TABLE ONLY uzivatel_role
413 ADD CONSTRAINT uzivatel_role_role_fk FOREIGN KEY (role) REFERENCES role(kod) ON UPDATE CASCADE ON DELETE CASCADE;
417 -- TOC entry 1888 (class 2606 OID 35143)
418 -- Dependencies: 1591 1589 1875
419 -- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
422 ALTER TABLE ONLY uzivatel_role
423 ADD CONSTRAINT uzivatel_role_uzivatel_fk FOREIGN KEY (prezdivka) REFERENCES uzivatel(prezdivka) ON UPDATE CASCADE ON DELETE CASCADE;
427 -- TOC entry 1893 (class 0 OID 0)
429 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
432 REVOKE ALL ON SCHEMA nekurak FROM PUBLIC;
433 REVOKE ALL ON SCHEMA nekurak FROM nekurak;
434 GRANT ALL ON SCHEMA nekurak TO nekurak;
435 GRANT USAGE ON SCHEMA nekurak TO nekurak_web;
439 -- TOC entry 1896 (class 0 OID 0)
440 -- Dependencies: 1592
441 -- Name: fotka_seq; Type: ACL; Schema: nekurak; Owner: nekurak
444 REVOKE ALL ON SEQUENCE fotka_seq FROM PUBLIC;
445 REVOKE ALL ON SEQUENCE fotka_seq FROM nekurak;
446 GRANT ALL ON SEQUENCE fotka_seq TO nekurak;
447 GRANT USAGE ON SEQUENCE fotka_seq TO nekurak_web;
451 -- TOC entry 1899 (class 0 OID 0)
452 -- Dependencies: 1593
453 -- Name: fotka; Type: ACL; Schema: nekurak; Owner: nekurak
456 REVOKE ALL ON TABLE fotka FROM PUBLIC;
457 REVOKE ALL ON TABLE fotka FROM nekurak;
458 GRANT ALL ON TABLE fotka TO nekurak;
459 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE fotka TO nekurak_web;
463 -- TOC entry 1901 (class 0 OID 0)
464 -- Dependencies: 1587
465 -- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak
468 REVOKE ALL ON TABLE hlasovani FROM PUBLIC;
469 REVOKE ALL ON TABLE hlasovani FROM nekurak;
470 GRANT ALL ON TABLE hlasovani TO nekurak;
471 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE hlasovani TO nekurak_web;
475 -- TOC entry 1902 (class 0 OID 0)
476 -- Dependencies: 1585
477 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
480 REVOKE ALL ON SEQUENCE podnik_seq FROM PUBLIC;
481 REVOKE ALL ON SEQUENCE podnik_seq FROM nekurak;
482 GRANT ALL ON SEQUENCE podnik_seq TO nekurak;
483 GRANT USAGE ON SEQUENCE podnik_seq TO nekurak_web;
487 -- TOC entry 1904 (class 0 OID 0)
488 -- Dependencies: 1586
489 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
492 REVOKE ALL ON TABLE podnik FROM PUBLIC;
493 REVOKE ALL ON TABLE podnik FROM nekurak;
494 GRANT ALL ON TABLE podnik TO nekurak;
495 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE podnik TO nekurak_web;
499 -- TOC entry 1906 (class 0 OID 0)
500 -- Dependencies: 1590
501 -- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak
504 REVOKE ALL ON TABLE role FROM PUBLIC;
505 REVOKE ALL ON TABLE role FROM nekurak;
506 GRANT ALL ON TABLE role TO nekurak;
507 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE role TO nekurak_web;
511 -- TOC entry 1907 (class 0 OID 0)
512 -- Dependencies: 1588
513 -- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak
516 REVOKE ALL ON SEQUENCE uzivatel_seq FROM PUBLIC;
517 REVOKE ALL ON SEQUENCE uzivatel_seq FROM nekurak;
518 GRANT ALL ON SEQUENCE uzivatel_seq TO nekurak;
519 GRANT USAGE ON SEQUENCE uzivatel_seq TO nekurak_web;
523 -- TOC entry 1909 (class 0 OID 0)
524 -- Dependencies: 1589
525 -- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak
528 REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
529 REVOKE ALL ON TABLE uzivatel FROM nekurak;
530 GRANT ALL ON TABLE uzivatel TO nekurak;
531 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel TO nekurak_web;
535 -- TOC entry 1911 (class 0 OID 0)
536 -- Dependencies: 1591
537 -- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak
540 REVOKE ALL ON TABLE uzivatel_role FROM PUBLIC;
541 REVOKE ALL ON TABLE uzivatel_role FROM nekurak;
542 GRANT ALL ON TABLE uzivatel_role TO nekurak;
543 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web;
546 -- Completed on 2010-03-16 14:32:24 CET
549 -- PostgreSQL database dump complete