# HG changeset patch # User František Kučera # Date 1268745194 -3600 # Node ID 3829d619369c35c46fbd2c7a5f68b0d0659316d8 # Parent c165ec94c864e3d29edccb92f51658dd72dd9779 SLQ: aktualizace datového modelu. diff -r c165ec94c864 -r 3829d619369c sql/schéma.sql --- a/sql/schéma.sql Tue Mar 16 13:32:42 2010 +0100 +++ b/sql/schéma.sql Tue Mar 16 14:13:14 2010 +0100 @@ -2,7 +2,7 @@ -- PostgreSQL database dump -- --- Started on 2010-03-09 20:48:44 CET +-- Started on 2010-03-16 14:11:16 CET SET statement_timeout = 0; SET client_encoding = 'UTF8'; @@ -24,8 +24,8 @@ SET search_path = nekurak, pg_catalog; -- --- TOC entry 369 (class 1247 OID 35086) --- Dependencies: 370 10 +-- TOC entry 368 (class 1247 OID 35086) +-- Dependencies: 369 10 -- Name: email; Type: DOMAIN; Schema: nekurak; Owner: nekurak -- @@ -36,8 +36,8 @@ ALTER DOMAIN nekurak.email OWNER TO nekurak; -- --- TOC entry 1881 (class 0 OID 0) --- Dependencies: 369 +-- TOC entry 1892 (class 0 OID 0) +-- Dependencies: 368 -- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -46,7 +46,7 @@ -- -- TOC entry 65 (class 1255 OID 35124) --- Dependencies: 10 392 +-- Dependencies: 395 10 -- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak -- @@ -64,7 +64,7 @@ ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak; -- --- TOC entry 1882 (class 0 OID 0) +-- TOC entry 1893 (class 0 OID 0) -- Dependencies: 65 -- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -72,13 +72,65 @@ COMMENT ON FUNCTION bezny_uzivatel() IS 'Každému novému uživateli dáme automaticky roli „bezny“.'; +-- +-- TOC entry 1592 (class 1259 OID 35183) +-- Dependencies: 10 +-- Name: fotka_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak +-- + +CREATE SEQUENCE fotka_seq + START WITH 1 + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + + +ALTER TABLE nekurak.fotka_seq OWNER TO nekurak; + SET default_tablespace = ''; +SET default_with_oids = false; + +-- +-- TOC entry 1593 (class 1259 OID 35185) +-- Dependencies: 1869 1870 10 +-- Name: fotka; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +CREATE TABLE fotka ( + id integer DEFAULT nextval('fotka_seq'::regclass) NOT NULL, + podnik integer NOT NULL, + popis character varying(255), + poradi integer DEFAULT 0 NOT NULL +); + + +ALTER TABLE nekurak.fotka OWNER TO nekurak; + +-- +-- TOC entry 1895 (class 0 OID 0) +-- Dependencies: 1593 +-- Name: TABLE fotka; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON TABLE fotka IS 'Fotka podniku.'; + + +-- +-- TOC entry 1896 (class 0 OID 0) +-- Dependencies: 1593 +-- Name: COLUMN fotka.poradi; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON COLUMN fotka.poradi IS 'Pořadí fotky v rámci daného podniku.'; + + SET default_with_oids = true; -- --- TOC entry 1584 (class 1259 OID 35068) --- Dependencies: 1861 10 +-- TOC entry 1587 (class 1259 OID 35068) +-- Dependencies: 1866 10 -- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -93,8 +145,8 @@ ALTER TABLE nekurak.hlasovani OWNER TO nekurak; -- --- TOC entry 1883 (class 0 OID 0) --- Dependencies: 1584 +-- TOC entry 1898 (class 0 OID 0) +-- Dependencies: 1587 -- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -104,7 +156,7 @@ -- --- TOC entry 1582 (class 1259 OID 35019) +-- TOC entry 1585 (class 1259 OID 35019) -- Dependencies: 10 -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -121,8 +173,8 @@ SET default_with_oids = false; -- --- TOC entry 1583 (class 1259 OID 35049) --- Dependencies: 1859 1860 10 +-- TOC entry 1586 (class 1259 OID 35049) +-- Dependencies: 1864 1865 10 -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -134,14 +186,24 @@ datum timestamp with time zone DEFAULT now(), ulice character varying(64), cislo_popisne integer, - mesto character varying(64) + mesto character varying(64), + spravce character varying(64) ); ALTER TABLE nekurak.podnik OWNER TO nekurak; -- --- TOC entry 1587 (class 1259 OID 35101) +-- TOC entry 1901 (class 0 OID 0) +-- Dependencies: 1586 +-- Name: COLUMN podnik.spravce; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +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)'; + + +-- +-- TOC entry 1590 (class 1259 OID 35101) -- Dependencies: 10 -- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -155,8 +217,8 @@ ALTER TABLE nekurak.role OWNER TO nekurak; -- --- TOC entry 1887 (class 0 OID 0) --- Dependencies: 1587 +-- TOC entry 1903 (class 0 OID 0) +-- Dependencies: 1590 -- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -164,7 +226,7 @@ -- --- TOC entry 1585 (class 1259 OID 35080) +-- TOC entry 1588 (class 1259 OID 35080) -- Dependencies: 10 -- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -179,8 +241,8 @@ ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak; -- --- TOC entry 1586 (class 1259 OID 35091) --- Dependencies: 1862 1863 10 +-- TOC entry 1589 (class 1259 OID 35091) +-- Dependencies: 1867 1868 10 -- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -198,8 +260,8 @@ ALTER TABLE nekurak.uzivatel OWNER TO nekurak; -- --- TOC entry 1890 (class 0 OID 0) --- Dependencies: 1586 +-- TOC entry 1906 (class 0 OID 0) +-- Dependencies: 1589 -- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -207,7 +269,7 @@ -- --- TOC entry 1588 (class 1259 OID 35106) +-- TOC entry 1591 (class 1259 OID 35106) -- Dependencies: 10 -- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -221,8 +283,8 @@ ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak; -- --- TOC entry 1892 (class 0 OID 0) --- Dependencies: 1588 +-- TOC entry 1908 (class 0 OID 0) +-- Dependencies: 1591 -- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -230,8 +292,18 @@ -- --- TOC entry 1865 (class 2606 OID 35058) --- Dependencies: 1583 1583 +-- TOC entry 1882 (class 2606 OID 35190) +-- Dependencies: 1593 1593 +-- Name: fotka_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +ALTER TABLE ONLY fotka + ADD CONSTRAINT fotka_pk PRIMARY KEY (id); + + +-- +-- TOC entry 1872 (class 2606 OID 35058) +-- Dependencies: 1586 1586 -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -240,8 +312,8 @@ -- --- TOC entry 1871 (class 2606 OID 35105) --- Dependencies: 1587 1587 +-- TOC entry 1878 (class 2606 OID 35105) +-- Dependencies: 1590 1590 -- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -250,8 +322,8 @@ -- --- TOC entry 1867 (class 2606 OID 35098) --- Dependencies: 1586 1586 +-- TOC entry 1874 (class 2606 OID 35098) +-- Dependencies: 1589 1589 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -260,8 +332,8 @@ -- --- TOC entry 1869 (class 2606 OID 35100) --- Dependencies: 1586 1586 +-- TOC entry 1876 (class 2606 OID 35100) +-- Dependencies: 1589 1589 -- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -270,8 +342,8 @@ -- --- TOC entry 1873 (class 2606 OID 35137) --- Dependencies: 1588 1588 1588 +-- TOC entry 1880 (class 2606 OID 35137) +-- Dependencies: 1591 1591 1591 -- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -280,8 +352,8 @@ -- --- TOC entry 1877 (class 2620 OID 35125) --- Dependencies: 65 1586 +-- TOC entry 1888 (class 2620 OID 35125) +-- Dependencies: 65 1589 -- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak -- @@ -292,8 +364,18 @@ -- --- TOC entry 1874 (class 2606 OID 35072) --- Dependencies: 1864 1583 1584 +-- TOC entry 1887 (class 2606 OID 35191) +-- Dependencies: 1871 1593 1586 +-- Name: fotka_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak +-- + +ALTER TABLE ONLY fotka + ADD CONSTRAINT fotka_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE; + + +-- +-- TOC entry 1884 (class 2606 OID 35072) +-- Dependencies: 1871 1586 1587 -- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -302,8 +384,18 @@ -- --- TOC entry 1875 (class 2606 OID 35138) --- Dependencies: 1870 1587 1588 +-- TOC entry 1883 (class 2606 OID 35178) +-- Dependencies: 1875 1589 1586 +-- Name: podnik_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak +-- + +ALTER TABLE ONLY podnik + ADD CONSTRAINT podnik_uzivatel_fk FOREIGN KEY (spravce) REFERENCES uzivatel(prezdivka) ON DELETE SET NULL; + + +-- +-- TOC entry 1885 (class 2606 OID 35138) +-- Dependencies: 1590 1591 1877 -- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -312,8 +404,8 @@ -- --- TOC entry 1876 (class 2606 OID 35143) --- Dependencies: 1868 1586 1588 +-- TOC entry 1886 (class 2606 OID 35143) +-- Dependencies: 1591 1589 1875 -- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -322,7 +414,7 @@ -- --- TOC entry 1880 (class 0 OID 0) +-- TOC entry 1891 (class 0 OID 0) -- Dependencies: 10 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak -- @@ -334,8 +426,32 @@ -- --- TOC entry 1884 (class 0 OID 0) --- Dependencies: 1584 +-- TOC entry 1894 (class 0 OID 0) +-- Dependencies: 1592 +-- Name: fotka_seq; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON SEQUENCE fotka_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE fotka_seq FROM nekurak; +GRANT ALL ON SEQUENCE fotka_seq TO nekurak; +GRANT USAGE ON SEQUENCE fotka_seq TO nekurak_web; + + +-- +-- TOC entry 1897 (class 0 OID 0) +-- Dependencies: 1593 +-- Name: fotka; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON TABLE fotka FROM PUBLIC; +REVOKE ALL ON TABLE fotka FROM nekurak; +GRANT ALL ON TABLE fotka TO nekurak; +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE fotka TO nekurak_web; + + +-- +-- TOC entry 1899 (class 0 OID 0) +-- Dependencies: 1587 -- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -346,8 +462,8 @@ -- --- TOC entry 1885 (class 0 OID 0) --- Dependencies: 1582 +-- TOC entry 1900 (class 0 OID 0) +-- Dependencies: 1585 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -358,8 +474,8 @@ -- --- TOC entry 1886 (class 0 OID 0) --- Dependencies: 1583 +-- TOC entry 1902 (class 0 OID 0) +-- Dependencies: 1586 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -370,8 +486,8 @@ -- --- TOC entry 1888 (class 0 OID 0) --- Dependencies: 1587 +-- TOC entry 1904 (class 0 OID 0) +-- Dependencies: 1590 -- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -382,8 +498,8 @@ -- --- TOC entry 1889 (class 0 OID 0) --- Dependencies: 1585 +-- TOC entry 1905 (class 0 OID 0) +-- Dependencies: 1588 -- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -394,8 +510,8 @@ -- --- TOC entry 1891 (class 0 OID 0) --- Dependencies: 1586 +-- TOC entry 1907 (class 0 OID 0) +-- Dependencies: 1589 -- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -406,8 +522,8 @@ -- --- TOC entry 1893 (class 0 OID 0) --- Dependencies: 1588 +-- TOC entry 1909 (class 0 OID 0) +-- Dependencies: 1591 -- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -417,7 +533,7 @@ GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web; --- Completed on 2010-03-09 20:48:46 CET +-- Completed on 2010-03-16 14:11:18 CET -- -- PostgreSQL database dump complete