# HG changeset patch # User František Kučera # Date 1276091632 -7200 # Node ID 5a7a24ac3a1540129bc3f6a6b7b49e537cf77a27 # Parent 230bd05dcfecfd3de52d970612b527d009712063 SQL model – komentáře diff -r 230bd05dcfec -r 5a7a24ac3a15 sql/schéma.sql --- a/sql/schéma.sql Wed Jun 09 15:51:41 2010 +0200 +++ b/sql/schéma.sql Wed Jun 09 15:53:52 2010 +0200 @@ -2,7 +2,7 @@ -- PostgreSQL database dump -- --- Started on 2010-04-29 14:41:40 CEST +-- Started on 2010-06-09 15:52:26 CEST SET statement_timeout = 0; SET client_encoding = 'UTF8'; @@ -36,7 +36,7 @@ ALTER DOMAIN nekurak.email OWNER TO nekurak; -- --- TOC entry 1914 (class 0 OID 0) +-- TOC entry 1926 (class 0 OID 0) -- Dependencies: 366 -- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -46,7 +46,7 @@ -- -- TOC entry 65 (class 1255 OID 35124) --- Dependencies: 10 402 +-- Dependencies: 10 406 -- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak -- @@ -64,7 +64,7 @@ ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak; -- --- TOC entry 1915 (class 0 OID 0) +-- TOC entry 1927 (class 0 OID 0) -- Dependencies: 65 -- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -73,7 +73,7 @@ -- --- TOC entry 1601 (class 1259 OID 35223) +-- TOC entry 1605 (class 1259 OID 35223) -- Dependencies: 10 -- Name: clanek_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -92,8 +92,8 @@ SET default_with_oids = false; -- --- TOC entry 1600 (class 1259 OID 35205) --- Dependencies: 1882 10 +-- TOC entry 1604 (class 1259 OID 35205) +-- Dependencies: 1888 10 -- Name: clanek; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -107,8 +107,8 @@ ALTER TABLE nekurak.clanek OWNER TO nekurak; -- --- TOC entry 1917 (class 0 OID 0) --- Dependencies: 1600 +-- TOC entry 1929 (class 0 OID 0) +-- Dependencies: 1604 -- Name: TABLE clanek; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -116,7 +116,7 @@ -- --- TOC entry 1598 (class 1259 OID 35183) +-- TOC entry 1602 (class 1259 OID 35183) -- Dependencies: 10 -- Name: fotka_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -132,8 +132,8 @@ ALTER TABLE nekurak.fotka_seq OWNER TO nekurak; -- --- TOC entry 1599 (class 1259 OID 35185) --- Dependencies: 1880 1881 10 +-- TOC entry 1603 (class 1259 OID 35185) +-- Dependencies: 1886 1887 10 -- Name: fotka; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -148,8 +148,8 @@ ALTER TABLE nekurak.fotka OWNER TO nekurak; -- --- TOC entry 1920 (class 0 OID 0) --- Dependencies: 1599 +-- TOC entry 1932 (class 0 OID 0) +-- Dependencies: 1603 -- Name: TABLE fotka; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -157,8 +157,8 @@ -- --- TOC entry 1921 (class 0 OID 0) --- Dependencies: 1599 +-- TOC entry 1933 (class 0 OID 0) +-- Dependencies: 1603 -- Name: COLUMN fotka.poradi; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -166,7 +166,7 @@ -- --- TOC entry 1603 (class 1259 OID 35250) +-- TOC entry 1607 (class 1259 OID 35250) -- Dependencies: 10 -- Name: hlasovani_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -181,8 +181,8 @@ ALTER TABLE nekurak.hlasovani_seq OWNER TO nekurak; -- --- TOC entry 1604 (class 1259 OID 35278) --- Dependencies: 1883 1884 10 +-- TOC entry 1608 (class 1259 OID 35278) +-- Dependencies: 1889 1890 10 -- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -198,8 +198,8 @@ ALTER TABLE nekurak.hlasovani OWNER TO nekurak; -- --- TOC entry 1924 (class 0 OID 0) --- Dependencies: 1604 +-- TOC entry 1936 (class 0 OID 0) +-- Dependencies: 1608 -- Name: COLUMN hlasovani.id; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -209,8 +209,8 @@ -- --- TOC entry 1925 (class 0 OID 0) --- Dependencies: 1604 +-- TOC entry 1937 (class 0 OID 0) +-- Dependencies: 1608 -- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -220,7 +220,68 @@ -- --- TOC entry 1592 (class 1259 OID 35019) +-- TOC entry 1609 (class 1259 OID 35333) +-- Dependencies: 10 +-- Name: komentar_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak +-- + +CREATE SEQUENCE komentar_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + + +ALTER TABLE nekurak.komentar_seq OWNER TO nekurak; + +-- +-- TOC entry 1610 (class 1259 OID 35376) +-- Dependencies: 1891 1892 10 +-- Name: komentar; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +CREATE TABLE komentar ( + id integer DEFAULT nextval('komentar_seq'::regclass) NOT NULL, + podnik integer NOT NULL, + uzivatel character varying(64) NOT NULL, + datum timestamp with time zone DEFAULT now() NOT NULL, + nadpis character varying(255), + komentar text NOT NULL +); + + +ALTER TABLE nekurak.komentar OWNER TO nekurak; + +-- +-- TOC entry 1940 (class 0 OID 0) +-- Dependencies: 1610 +-- Name: TABLE komentar; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON TABLE komentar IS 'Komentář k podniku'; + + +-- +-- TOC entry 1941 (class 0 OID 0) +-- Dependencies: 1610 +-- Name: COLUMN komentar.nadpis; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON COLUMN komentar.nadpis IS 'vždy jen prostý text'; + + +-- +-- TOC entry 1942 (class 0 OID 0) +-- Dependencies: 1610 +-- Name: COLUMN komentar.komentar; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON COLUMN komentar.komentar IS 'Vlastní komentář. Vždy jen XHTML – převádíme na něj jak prostý text, tak Texy nebo cokoli jiného. +Jedná se o kus validního XML – má kořenový element
'; + + +-- +-- TOC entry 1596 (class 1259 OID 35019) -- Dependencies: 10 -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -235,8 +296,8 @@ ALTER TABLE nekurak.podnik_seq OWNER TO nekurak; -- --- TOC entry 1593 (class 1259 OID 35049) --- Dependencies: 1876 1877 10 +-- TOC entry 1597 (class 1259 OID 35049) +-- Dependencies: 1882 1883 10 -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -258,8 +319,8 @@ ALTER TABLE nekurak.podnik OWNER TO nekurak; -- --- TOC entry 1928 (class 0 OID 0) --- Dependencies: 1593 +-- TOC entry 1945 (class 0 OID 0) +-- Dependencies: 1597 -- Name: COLUMN podnik.spravce; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -267,7 +328,7 @@ -- --- TOC entry 1596 (class 1259 OID 35101) +-- TOC entry 1600 (class 1259 OID 35101) -- Dependencies: 10 -- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -281,8 +342,8 @@ ALTER TABLE nekurak.role OWNER TO nekurak; -- --- TOC entry 1930 (class 0 OID 0) --- Dependencies: 1596 +-- TOC entry 1947 (class 0 OID 0) +-- Dependencies: 1600 -- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -290,7 +351,7 @@ -- --- TOC entry 1594 (class 1259 OID 35080) +-- TOC entry 1598 (class 1259 OID 35080) -- Dependencies: 10 -- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -305,8 +366,8 @@ ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak; -- --- TOC entry 1595 (class 1259 OID 35091) --- Dependencies: 1878 1879 10 +-- TOC entry 1599 (class 1259 OID 35091) +-- Dependencies: 1884 1885 10 -- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -324,8 +385,8 @@ ALTER TABLE nekurak.uzivatel OWNER TO nekurak; -- --- TOC entry 1933 (class 0 OID 0) --- Dependencies: 1595 +-- TOC entry 1950 (class 0 OID 0) +-- Dependencies: 1599 -- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -333,7 +394,7 @@ -- --- TOC entry 1597 (class 1259 OID 35106) +-- TOC entry 1601 (class 1259 OID 35106) -- Dependencies: 10 -- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -347,8 +408,8 @@ ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak; -- --- TOC entry 1935 (class 0 OID 0) --- Dependencies: 1597 +-- TOC entry 1952 (class 0 OID 0) +-- Dependencies: 1601 -- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -356,8 +417,8 @@ -- --- TOC entry 1900 (class 2606 OID 35227) --- Dependencies: 1600 1600 +-- TOC entry 1908 (class 2606 OID 35227) +-- Dependencies: 1604 1604 -- Name: clanek_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -366,8 +427,8 @@ -- --- TOC entry 1896 (class 2606 OID 35190) --- Dependencies: 1599 1599 +-- TOC entry 1904 (class 2606 OID 35190) +-- Dependencies: 1603 1603 -- Name: fotka_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -376,8 +437,8 @@ -- --- TOC entry 1898 (class 2606 OID 35204) --- Dependencies: 1599 1599 1599 +-- TOC entry 1906 (class 2606 OID 35204) +-- Dependencies: 1603 1603 1603 -- Name: fotka_poradi_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -386,8 +447,8 @@ -- --- TOC entry 1903 (class 2606 OID 35284) --- Dependencies: 1604 1604 +-- TOC entry 1911 (class 2606 OID 35284) +-- Dependencies: 1608 1608 -- Name: hlasovani_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -396,8 +457,18 @@ -- --- TOC entry 1886 (class 2606 OID 35058) --- Dependencies: 1593 1593 +-- TOC entry 1914 (class 2606 OID 35385) +-- Dependencies: 1610 1610 +-- Name: komentar_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +ALTER TABLE ONLY komentar + ADD CONSTRAINT komentar_pk PRIMARY KEY (id); + + +-- +-- TOC entry 1894 (class 2606 OID 35058) +-- Dependencies: 1597 1597 -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -406,8 +477,8 @@ -- --- TOC entry 1892 (class 2606 OID 35105) --- Dependencies: 1596 1596 +-- TOC entry 1900 (class 2606 OID 35105) +-- Dependencies: 1600 1600 -- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -416,8 +487,8 @@ -- --- TOC entry 1888 (class 2606 OID 35098) --- Dependencies: 1595 1595 +-- TOC entry 1896 (class 2606 OID 35098) +-- Dependencies: 1599 1599 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -426,8 +497,8 @@ -- --- TOC entry 1890 (class 2606 OID 35100) --- Dependencies: 1595 1595 +-- TOC entry 1898 (class 2606 OID 35100) +-- Dependencies: 1599 1599 -- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -436,8 +507,8 @@ -- --- TOC entry 1894 (class 2606 OID 35137) --- Dependencies: 1597 1597 1597 +-- TOC entry 1902 (class 2606 OID 35137) +-- Dependencies: 1601 1601 1601 -- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -446,8 +517,8 @@ -- --- TOC entry 1901 (class 1259 OID 35291) --- Dependencies: 1604 +-- TOC entry 1909 (class 1259 OID 35291) +-- Dependencies: 1608 -- Name: hlasovani_ip_idx; Type: INDEX; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -455,8 +526,8 @@ -- --- TOC entry 1904 (class 1259 OID 35309) --- Dependencies: 1604 +-- TOC entry 1912 (class 1259 OID 35309) +-- Dependencies: 1608 -- Name: hlsovani_datum_idx; Type: INDEX; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -464,8 +535,8 @@ -- --- TOC entry 1910 (class 2620 OID 35125) --- Dependencies: 1595 65 +-- TOC entry 1922 (class 2620 OID 35125) +-- Dependencies: 1599 65 -- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak -- @@ -476,8 +547,8 @@ -- --- TOC entry 1908 (class 2606 OID 35191) --- Dependencies: 1599 1885 1593 +-- TOC entry 1918 (class 2606 OID 35191) +-- Dependencies: 1893 1597 1603 -- Name: fotka_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -486,8 +557,8 @@ -- --- TOC entry 1909 (class 2606 OID 35285) --- Dependencies: 1885 1604 1593 +-- TOC entry 1919 (class 2606 OID 35285) +-- Dependencies: 1597 1608 1893 -- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -496,8 +567,28 @@ -- --- TOC entry 1905 (class 2606 OID 35178) --- Dependencies: 1889 1595 1593 +-- TOC entry 1920 (class 2606 OID 35386) +-- Dependencies: 1610 1893 1597 +-- Name: komentar_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak +-- + +ALTER TABLE ONLY komentar + ADD CONSTRAINT komentar_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id); + + +-- +-- TOC entry 1921 (class 2606 OID 35391) +-- Dependencies: 1599 1897 1610 +-- Name: komentar_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak +-- + +ALTER TABLE ONLY komentar + ADD CONSTRAINT komentar_uzivatel_fk FOREIGN KEY (uzivatel) REFERENCES uzivatel(prezdivka); + + +-- +-- TOC entry 1915 (class 2606 OID 35178) +-- Dependencies: 1599 1897 1597 -- Name: podnik_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -506,8 +597,8 @@ -- --- TOC entry 1906 (class 2606 OID 35138) --- Dependencies: 1597 1596 1891 +-- TOC entry 1916 (class 2606 OID 35138) +-- Dependencies: 1600 1601 1899 -- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -516,8 +607,8 @@ -- --- TOC entry 1907 (class 2606 OID 35143) --- Dependencies: 1597 1889 1595 +-- TOC entry 1917 (class 2606 OID 35143) +-- Dependencies: 1599 1897 1601 -- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -526,7 +617,7 @@ -- --- TOC entry 1913 (class 0 OID 0) +-- TOC entry 1925 (class 0 OID 0) -- Dependencies: 10 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak -- @@ -538,8 +629,8 @@ -- --- TOC entry 1916 (class 0 OID 0) --- Dependencies: 1601 +-- TOC entry 1928 (class 0 OID 0) +-- Dependencies: 1605 -- Name: clanek_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -550,8 +641,8 @@ -- --- TOC entry 1918 (class 0 OID 0) --- Dependencies: 1600 +-- TOC entry 1930 (class 0 OID 0) +-- Dependencies: 1604 -- Name: clanek; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -562,8 +653,8 @@ -- --- TOC entry 1919 (class 0 OID 0) --- Dependencies: 1598 +-- TOC entry 1931 (class 0 OID 0) +-- Dependencies: 1602 -- Name: fotka_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -574,8 +665,8 @@ -- --- TOC entry 1922 (class 0 OID 0) --- Dependencies: 1599 +-- TOC entry 1934 (class 0 OID 0) +-- Dependencies: 1603 -- Name: fotka; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -586,8 +677,8 @@ -- --- TOC entry 1923 (class 0 OID 0) --- Dependencies: 1603 +-- TOC entry 1935 (class 0 OID 0) +-- Dependencies: 1607 -- Name: hlasovani_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -598,20 +689,44 @@ -- --- TOC entry 1926 (class 0 OID 0) --- Dependencies: 1604 +-- TOC entry 1938 (class 0 OID 0) +-- Dependencies: 1608 -- 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; +GRANT SELECT,INSERT ON TABLE hlasovani TO nekurak_web; -- --- TOC entry 1927 (class 0 OID 0) --- Dependencies: 1592 +-- TOC entry 1939 (class 0 OID 0) +-- Dependencies: 1609 +-- Name: komentar_seq; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON SEQUENCE komentar_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE komentar_seq FROM nekurak; +GRANT ALL ON SEQUENCE komentar_seq TO nekurak; +GRANT USAGE ON SEQUENCE komentar_seq TO nekurak_web; + + +-- +-- TOC entry 1943 (class 0 OID 0) +-- Dependencies: 1610 +-- Name: komentar; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON TABLE komentar FROM PUBLIC; +REVOKE ALL ON TABLE komentar FROM nekurak; +GRANT ALL ON TABLE komentar TO nekurak; +GRANT SELECT,INSERT ON TABLE komentar TO nekurak_web; + + +-- +-- TOC entry 1944 (class 0 OID 0) +-- Dependencies: 1596 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -622,8 +737,8 @@ -- --- TOC entry 1929 (class 0 OID 0) --- Dependencies: 1593 +-- TOC entry 1946 (class 0 OID 0) +-- Dependencies: 1597 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -634,8 +749,8 @@ -- --- TOC entry 1931 (class 0 OID 0) --- Dependencies: 1596 +-- TOC entry 1948 (class 0 OID 0) +-- Dependencies: 1600 -- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -646,8 +761,8 @@ -- --- TOC entry 1932 (class 0 OID 0) --- Dependencies: 1594 +-- TOC entry 1949 (class 0 OID 0) +-- Dependencies: 1598 -- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -658,8 +773,8 @@ -- --- TOC entry 1934 (class 0 OID 0) --- Dependencies: 1595 +-- TOC entry 1951 (class 0 OID 0) +-- Dependencies: 1599 -- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -670,8 +785,8 @@ -- --- TOC entry 1936 (class 0 OID 0) --- Dependencies: 1597 +-- TOC entry 1953 (class 0 OID 0) +-- Dependencies: 1601 -- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -681,7 +796,7 @@ GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web; --- Completed on 2010-04-29 14:41:42 CEST +-- Completed on 2010-06-09 15:52:28 CEST -- -- PostgreSQL database dump complete