diff -r ddb7b4415234 -r d9df3f7b67dd sql/schéma.sql --- a/sql/schéma.sql Tue Mar 09 20:46:54 2010 +0100 +++ b/sql/schéma.sql Tue Mar 09 20:54:58 2010 +0100 @@ -2,7 +2,7 @@ -- PostgreSQL database dump -- --- Started on 2010-01-25 17:42:09 CET +-- Started on 2010-03-09 20:48:44 CET SET statement_timeout = 0; SET client_encoding = 'UTF8'; @@ -24,7 +24,87 @@ SET search_path = nekurak, pg_catalog; -- --- TOC entry 1568 (class 1259 OID 35019) +-- TOC entry 369 (class 1247 OID 35086) +-- Dependencies: 370 10 +-- Name: email; Type: DOMAIN; Schema: nekurak; Owner: nekurak +-- + +CREATE DOMAIN email AS character varying(255) + CONSTRAINT email_regexp CHECK (((VALUE)::text ~ '^[_a-zA-Z0-9\\.\\-]+@[_a-zA-Z0-9\\.\\-]+\\.[a-zA-Z]{2,4}$'::text)); + + +ALTER DOMAIN nekurak.email OWNER TO nekurak; + +-- +-- TOC entry 1881 (class 0 OID 0) +-- Dependencies: 369 +-- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON DOMAIN email IS 'e-mailová adresa'; + + +-- +-- TOC entry 65 (class 1255 OID 35124) +-- Dependencies: 10 392 +-- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak +-- + +CREATE FUNCTION bezny_uzivatel() RETURNS trigger + LANGUAGE plpgsql + AS $$BEGIN +INSERT INTO uzivatel_role +(role, prezdivka) +VALUES +('bezny', new.prezdivka); +RETURN new; +END;$$; + + +ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak; + +-- +-- TOC entry 1882 (class 0 OID 0) +-- Dependencies: 65 +-- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON FUNCTION bezny_uzivatel() IS 'Každému novému uživateli dáme automaticky roli „bezny“.'; + + +SET default_tablespace = ''; + +SET default_with_oids = true; + +-- +-- TOC entry 1584 (class 1259 OID 35068) +-- Dependencies: 1861 10 +-- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +CREATE TABLE hlasovani ( + podnik integer NOT NULL, + hlas boolean NOT NULL, + datum date DEFAULT now() NOT NULL, + ip_adresa character varying(255) +); + + +ALTER TABLE nekurak.hlasovani OWNER TO nekurak; + +-- +-- TOC entry 1883 (class 0 OID 0) +-- Dependencies: 1584 +-- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON COLUMN hlasovani.hlas IS 'Má být tento podnik nekuřácký? +true = nekuřácký +false = kouření povoleno'; + + +-- +-- TOC entry 1582 (class 1259 OID 35019) -- Dependencies: 10 -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -38,50 +118,120 @@ ALTER TABLE nekurak.podnik_seq OWNER TO nekurak; --- --- TOC entry 1848 (class 0 OID 0) --- Dependencies: 1568 --- Name: podnik_seq; Type: SEQUENCE SET; Schema: nekurak; Owner: nekurak --- - -SELECT pg_catalog.setval('podnik_seq', 3, true); - - -SET default_tablespace = ''; - SET default_with_oids = false; -- --- TOC entry 1569 (class 1259 OID 35021) --- Dependencies: 1840 1841 10 +-- TOC entry 1583 (class 1259 OID 35049) +-- Dependencies: 1859 1860 10 -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- CREATE TABLE podnik ( id integer DEFAULT nextval('podnik_seq'::regclass) NOT NULL, nazev character varying(255) NOT NULL, - datum timestamp with time zone DEFAULT now() + popis character varying(255), + url character varying(255), + datum timestamp with time zone DEFAULT now(), + ulice character varying(64), + cislo_popisne integer, + mesto character varying(64) ); ALTER TABLE nekurak.podnik OWNER TO nekurak; -- --- TOC entry 1844 (class 0 OID 35021) --- Dependencies: 1569 --- Data for Name: podnik; Type: TABLE DATA; Schema: nekurak; Owner: nekurak +-- TOC entry 1587 (class 1259 OID 35101) +-- Dependencies: 10 +-- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- -COPY podnik (id, nazev, datum) FROM stdin; -1 Na Kovárně 2010-01-16 02:40:12.271326+01 -2 U přátelského potkana 2010-01-16 02:40:43.326281+01 -3 Na Růžku 2010-01-16 02:40:48.104577+01 -\. +CREATE TABLE role ( + kod character varying(16) NOT NULL, + popis character varying(255) +); +ALTER TABLE nekurak.role OWNER TO nekurak; + -- --- TOC entry 1843 (class 2606 OID 35027) --- Dependencies: 1569 1569 +-- TOC entry 1887 (class 0 OID 0) +-- Dependencies: 1587 +-- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON TABLE role IS 'Role (skupiny) uživatelů ~ oprávnění'; + + +-- +-- TOC entry 1585 (class 1259 OID 35080) +-- Dependencies: 10 +-- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak +-- + +CREATE SEQUENCE uzivatel_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + + +ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak; + +-- +-- TOC entry 1586 (class 1259 OID 35091) +-- Dependencies: 1862 1863 10 +-- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +CREATE TABLE uzivatel ( + id integer DEFAULT nextval('uzivatel_seq'::regclass) NOT NULL, + prezdivka character varying(64) NOT NULL, + heslo character varying(512) NOT NULL, + jmeno character varying(64), + prijmeni character varying(64), + email character varying(255), + datum timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE nekurak.uzivatel OWNER TO nekurak; + +-- +-- TOC entry 1890 (class 0 OID 0) +-- Dependencies: 1586 +-- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON COLUMN uzivatel.prezdivka IS 'Uživatelské jméno'; + + +-- +-- TOC entry 1588 (class 1259 OID 35106) +-- Dependencies: 10 +-- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +CREATE TABLE uzivatel_role ( + role character varying(16) NOT NULL, + prezdivka character varying(64) NOT NULL +); + + +ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak; + +-- +-- TOC entry 1892 (class 0 OID 0) +-- Dependencies: 1588 +-- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON TABLE uzivatel_role IS 'Přiřazení uživatelských rolí (skupin).'; + + +-- +-- TOC entry 1865 (class 2606 OID 35058) +-- Dependencies: 1583 1583 -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -90,7 +240,89 @@ -- --- TOC entry 1847 (class 0 OID 0) +-- TOC entry 1871 (class 2606 OID 35105) +-- Dependencies: 1587 1587 +-- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +ALTER TABLE ONLY role + ADD CONSTRAINT skupina_pk PRIMARY KEY (kod); + + +-- +-- TOC entry 1867 (class 2606 OID 35098) +-- Dependencies: 1586 1586 +-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +ALTER TABLE ONLY uzivatel + ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id); + + +-- +-- TOC entry 1869 (class 2606 OID 35100) +-- Dependencies: 1586 1586 +-- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +ALTER TABLE ONLY uzivatel + ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka); + + +-- +-- TOC entry 1873 (class 2606 OID 35137) +-- Dependencies: 1588 1588 1588 +-- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +ALTER TABLE ONLY uzivatel_role + ADD CONSTRAINT uzivatel_role_pk PRIMARY KEY (role, prezdivka); + + +-- +-- TOC entry 1877 (class 2620 OID 35125) +-- Dependencies: 65 1586 +-- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak +-- + +CREATE TRIGGER uzivatel_insert + AFTER INSERT ON uzivatel + FOR EACH ROW + EXECUTE PROCEDURE bezny_uzivatel(); + + +-- +-- TOC entry 1874 (class 2606 OID 35072) +-- Dependencies: 1864 1583 1584 +-- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak +-- + +ALTER TABLE ONLY hlasovani + ADD CONSTRAINT hlasovani_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE; + + +-- +-- TOC entry 1875 (class 2606 OID 35138) +-- Dependencies: 1870 1587 1588 +-- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak +-- + +ALTER TABLE ONLY uzivatel_role + ADD CONSTRAINT uzivatel_role_role_fk FOREIGN KEY (role) REFERENCES role(kod) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- TOC entry 1876 (class 2606 OID 35143) +-- Dependencies: 1868 1586 1588 +-- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak +-- + +ALTER TABLE ONLY uzivatel_role + ADD CONSTRAINT uzivatel_role_uzivatel_fk FOREIGN KEY (prezdivka) REFERENCES uzivatel(prezdivka) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- TOC entry 1880 (class 0 OID 0) -- Dependencies: 10 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak -- @@ -102,8 +334,20 @@ -- --- TOC entry 1849 (class 0 OID 0) --- Dependencies: 1568 +-- TOC entry 1884 (class 0 OID 0) +-- Dependencies: 1584 +-- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON TABLE hlasovani FROM PUBLIC; +REVOKE ALL ON TABLE hlasovani FROM nekurak; +GRANT ALL ON TABLE hlasovani TO nekurak; +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE hlasovani TO nekurak_web; + + +-- +-- TOC entry 1885 (class 0 OID 0) +-- Dependencies: 1582 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -114,8 +358,8 @@ -- --- TOC entry 1850 (class 0 OID 0) --- Dependencies: 1569 +-- TOC entry 1886 (class 0 OID 0) +-- Dependencies: 1583 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -125,7 +369,55 @@ GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE podnik TO nekurak_web; --- Completed on 2010-01-25 17:42:10 CET +-- +-- TOC entry 1888 (class 0 OID 0) +-- Dependencies: 1587 +-- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON TABLE role FROM PUBLIC; +REVOKE ALL ON TABLE role FROM nekurak; +GRANT ALL ON TABLE role TO nekurak; +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE role TO nekurak_web; + + +-- +-- TOC entry 1889 (class 0 OID 0) +-- Dependencies: 1585 +-- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON SEQUENCE uzivatel_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE uzivatel_seq FROM nekurak; +GRANT ALL ON SEQUENCE uzivatel_seq TO nekurak; +GRANT USAGE ON SEQUENCE uzivatel_seq TO nekurak_web; + + +-- +-- TOC entry 1891 (class 0 OID 0) +-- Dependencies: 1586 +-- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON TABLE uzivatel FROM PUBLIC; +REVOKE ALL ON TABLE uzivatel FROM nekurak; +GRANT ALL ON TABLE uzivatel TO nekurak; +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel TO nekurak_web; + + +-- +-- TOC entry 1893 (class 0 OID 0) +-- Dependencies: 1588 +-- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON TABLE uzivatel_role FROM PUBLIC; +REVOKE ALL ON TABLE uzivatel_role FROM nekurak; +GRANT ALL ON TABLE uzivatel_role TO nekurak; +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web; + + +-- Completed on 2010-03-09 20:48:46 CET -- -- PostgreSQL database dump complete