diff -r 81c64c582a26 -r b278b6b2d552 sql/schéma.sql --- a/sql/schéma.sql Tue Mar 16 14:33:05 2010 +0100 +++ b/sql/schéma.sql Wed Apr 28 00:36:21 2010 +0200 @@ -2,7 +2,7 @@ -- PostgreSQL database dump -- --- Started on 2010-03-16 14:32:22 CET +-- Started on 2010-04-28 00:33:47 CEST SET statement_timeout = 0; SET client_encoding = 'UTF8'; @@ -24,8 +24,8 @@ SET search_path = nekurak, pg_catalog; -- --- TOC entry 368 (class 1247 OID 35086) --- Dependencies: 369 10 +-- TOC entry 366 (class 1247 OID 35086) +-- Dependencies: 367 10 -- Name: email; Type: DOMAIN; Schema: nekurak; Owner: nekurak -- @@ -36,8 +36,8 @@ ALTER DOMAIN nekurak.email OWNER TO nekurak; -- --- TOC entry 1894 (class 0 OID 0) --- Dependencies: 368 +-- TOC entry 1913 (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 395 +-- Dependencies: 402 10 -- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak -- @@ -64,7 +64,7 @@ ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak; -- --- TOC entry 1895 (class 0 OID 0) +-- TOC entry 1914 (class 0 OID 0) -- Dependencies: 65 -- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -73,7 +73,50 @@ -- --- TOC entry 1592 (class 1259 OID 35183) +-- TOC entry 1601 (class 1259 OID 35223) +-- Dependencies: 10 +-- Name: clanek_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak +-- + +CREATE SEQUENCE clanek_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + + +ALTER TABLE nekurak.clanek_seq OWNER TO nekurak; + +SET default_tablespace = ''; + +SET default_with_oids = false; + +-- +-- TOC entry 1600 (class 1259 OID 35205) +-- Dependencies: 1882 10 +-- Name: clanek; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +CREATE TABLE clanek ( + id integer DEFAULT nextval('clanek_seq'::regclass) NOT NULL, + nadpis character varying(255) NOT NULL, + text text +); + + +ALTER TABLE nekurak.clanek OWNER TO nekurak; + +-- +-- TOC entry 1916 (class 0 OID 0) +-- Dependencies: 1600 +-- Name: TABLE clanek; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON TABLE clanek IS 'Článek potažmo stránka – nějaký textový obsah, (XúHTML.'; + + +-- +-- TOC entry 1598 (class 1259 OID 35183) -- Dependencies: 10 -- Name: fotka_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -88,13 +131,9 @@ 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 +-- TOC entry 1599 (class 1259 OID 35185) +-- Dependencies: 1880 1881 10 -- Name: fotka; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -109,8 +148,8 @@ ALTER TABLE nekurak.fotka OWNER TO nekurak; -- --- TOC entry 1897 (class 0 OID 0) --- Dependencies: 1593 +-- TOC entry 1919 (class 0 OID 0) +-- Dependencies: 1599 -- Name: TABLE fotka; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -118,35 +157,60 @@ -- --- TOC entry 1898 (class 0 OID 0) --- Dependencies: 1593 +-- TOC entry 1920 (class 0 OID 0) +-- Dependencies: 1599 -- 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 1603 (class 1259 OID 35250) +-- Dependencies: 10 +-- Name: hlasovani_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak +-- + +CREATE SEQUENCE hlasovani_seq + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + + +ALTER TABLE nekurak.hlasovani_seq OWNER TO nekurak; -- --- TOC entry 1587 (class 1259 OID 35068) --- Dependencies: 1866 10 +-- TOC entry 1604 (class 1259 OID 35278) +-- Dependencies: 1883 1884 10 -- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- CREATE TABLE hlasovani ( + id integer DEFAULT nextval('hlasovani_seq'::regclass) NOT NULL, podnik integer NOT NULL, hlas boolean NOT NULL, datum date DEFAULT now() NOT NULL, - ip_adresa character varying(255) + ip_adresa character varying(255) NOT NULL ); ALTER TABLE nekurak.hlasovani OWNER TO nekurak; -- --- TOC entry 1900 (class 0 OID 0) --- Dependencies: 1587 +-- TOC entry 1923 (class 0 OID 0) +-- Dependencies: 1604 +-- Name: COLUMN hlasovani.id; Type: COMMENT; Schema: nekurak; Owner: nekurak +-- + +COMMENT ON COLUMN hlasovani.id IS 'V této tabulce v princupu ID nepotřebujeme, +protože nebudeme adresovat konkrétní záznamy (a i kdyby, můžeme použít OID). +Ale číselné ID se nám bude hodit pro rychlejší nalezení posledního hlasu od dané IP adresy.'; + + +-- +-- TOC entry 1924 (class 0 OID 0) +-- Dependencies: 1604 -- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -156,7 +220,7 @@ -- --- TOC entry 1585 (class 1259 OID 35019) +-- TOC entry 1592 (class 1259 OID 35019) -- Dependencies: 10 -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -170,11 +234,9 @@ ALTER TABLE nekurak.podnik_seq OWNER TO nekurak; -SET default_with_oids = false; - -- --- TOC entry 1586 (class 1259 OID 35049) --- Dependencies: 1864 1865 10 +-- TOC entry 1593 (class 1259 OID 35049) +-- Dependencies: 1876 1877 10 -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -187,15 +249,17 @@ ulice character varying(64), cislo_popisne integer, mesto character varying(64), - spravce character varying(64) + spravce character varying(64), + sirka double precision, + delka double precision ); ALTER TABLE nekurak.podnik OWNER TO nekurak; -- --- TOC entry 1903 (class 0 OID 0) --- Dependencies: 1586 +-- TOC entry 1927 (class 0 OID 0) +-- Dependencies: 1593 -- Name: COLUMN podnik.spravce; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -203,7 +267,7 @@ -- --- TOC entry 1590 (class 1259 OID 35101) +-- TOC entry 1596 (class 1259 OID 35101) -- Dependencies: 10 -- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -217,8 +281,8 @@ ALTER TABLE nekurak.role OWNER TO nekurak; -- --- TOC entry 1905 (class 0 OID 0) --- Dependencies: 1590 +-- TOC entry 1929 (class 0 OID 0) +-- Dependencies: 1596 -- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -226,7 +290,7 @@ -- --- TOC entry 1588 (class 1259 OID 35080) +-- TOC entry 1594 (class 1259 OID 35080) -- Dependencies: 10 -- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak -- @@ -241,8 +305,8 @@ ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak; -- --- TOC entry 1589 (class 1259 OID 35091) --- Dependencies: 1867 1868 10 +-- TOC entry 1595 (class 1259 OID 35091) +-- Dependencies: 1878 1879 10 -- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -260,8 +324,8 @@ ALTER TABLE nekurak.uzivatel OWNER TO nekurak; -- --- TOC entry 1908 (class 0 OID 0) --- Dependencies: 1589 +-- TOC entry 1932 (class 0 OID 0) +-- Dependencies: 1595 -- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -269,7 +333,7 @@ -- --- TOC entry 1591 (class 1259 OID 35106) +-- TOC entry 1597 (class 1259 OID 35106) -- Dependencies: 10 -- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -283,8 +347,8 @@ ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak; -- --- TOC entry 1910 (class 0 OID 0) --- Dependencies: 1591 +-- TOC entry 1934 (class 0 OID 0) +-- Dependencies: 1597 -- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak -- @@ -292,8 +356,18 @@ -- --- TOC entry 1882 (class 2606 OID 35190) --- Dependencies: 1593 1593 +-- TOC entry 1900 (class 2606 OID 35227) +-- Dependencies: 1600 1600 +-- Name: clanek_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +ALTER TABLE ONLY clanek + ADD CONSTRAINT clanek_pk PRIMARY KEY (id); + + +-- +-- TOC entry 1896 (class 2606 OID 35190) +-- Dependencies: 1599 1599 -- Name: fotka_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -302,8 +376,8 @@ -- --- TOC entry 1884 (class 2606 OID 35204) --- Dependencies: 1593 1593 1593 +-- TOC entry 1898 (class 2606 OID 35204) +-- Dependencies: 1599 1599 1599 -- Name: fotka_poradi_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -312,8 +386,18 @@ -- --- TOC entry 1872 (class 2606 OID 35058) --- Dependencies: 1586 1586 +-- TOC entry 1903 (class 2606 OID 35284) +-- Dependencies: 1604 1604 +-- Name: hlasovani_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +ALTER TABLE ONLY hlasovani + ADD CONSTRAINT hlasovani_pk PRIMARY KEY (id); + + +-- +-- TOC entry 1886 (class 2606 OID 35058) +-- Dependencies: 1593 1593 -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -322,8 +406,8 @@ -- --- TOC entry 1878 (class 2606 OID 35105) --- Dependencies: 1590 1590 +-- TOC entry 1892 (class 2606 OID 35105) +-- Dependencies: 1596 1596 -- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -332,8 +416,8 @@ -- --- TOC entry 1874 (class 2606 OID 35098) --- Dependencies: 1589 1589 +-- TOC entry 1888 (class 2606 OID 35098) +-- Dependencies: 1595 1595 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -342,8 +426,8 @@ -- --- TOC entry 1876 (class 2606 OID 35100) --- Dependencies: 1589 1589 +-- TOC entry 1890 (class 2606 OID 35100) +-- Dependencies: 1595 1595 -- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -352,8 +436,8 @@ -- --- TOC entry 1880 (class 2606 OID 35137) --- Dependencies: 1591 1591 1591 +-- TOC entry 1894 (class 2606 OID 35137) +-- Dependencies: 1597 1597 1597 -- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: -- @@ -362,8 +446,17 @@ -- --- TOC entry 1890 (class 2620 OID 35125) --- Dependencies: 1589 65 +-- TOC entry 1901 (class 1259 OID 35291) +-- Dependencies: 1604 +-- Name: hlasovani_ip_idx; Type: INDEX; Schema: nekurak; Owner: nekurak; Tablespace: +-- + +CREATE INDEX hlasovani_ip_idx ON hlasovani USING btree (ip_adresa); + + +-- +-- TOC entry 1909 (class 2620 OID 35125) +-- Dependencies: 65 1595 -- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak -- @@ -374,8 +467,8 @@ -- --- TOC entry 1889 (class 2606 OID 35191) --- Dependencies: 1871 1586 1593 +-- TOC entry 1907 (class 2606 OID 35191) +-- Dependencies: 1599 1593 1885 -- Name: fotka_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -384,8 +477,8 @@ -- --- TOC entry 1886 (class 2606 OID 35072) --- Dependencies: 1587 1871 1586 +-- TOC entry 1908 (class 2606 OID 35285) +-- Dependencies: 1885 1604 1593 -- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -394,8 +487,8 @@ -- --- TOC entry 1885 (class 2606 OID 35178) --- Dependencies: 1875 1589 1586 +-- TOC entry 1904 (class 2606 OID 35178) +-- Dependencies: 1593 1889 1595 -- Name: podnik_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -404,8 +497,8 @@ -- --- TOC entry 1887 (class 2606 OID 35138) --- Dependencies: 1590 1591 1877 +-- TOC entry 1905 (class 2606 OID 35138) +-- Dependencies: 1597 1891 1596 -- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -414,8 +507,8 @@ -- --- TOC entry 1888 (class 2606 OID 35143) --- Dependencies: 1591 1589 1875 +-- TOC entry 1906 (class 2606 OID 35143) +-- Dependencies: 1889 1595 1597 -- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak -- @@ -424,7 +517,7 @@ -- --- TOC entry 1893 (class 0 OID 0) +-- TOC entry 1912 (class 0 OID 0) -- Dependencies: 10 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak -- @@ -436,8 +529,32 @@ -- --- TOC entry 1896 (class 0 OID 0) --- Dependencies: 1592 +-- TOC entry 1915 (class 0 OID 0) +-- Dependencies: 1601 +-- Name: clanek_seq; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON SEQUENCE clanek_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE clanek_seq FROM nekurak; +GRANT ALL ON SEQUENCE clanek_seq TO nekurak; +GRANT USAGE ON SEQUENCE clanek_seq TO nekurak_web; + + +-- +-- TOC entry 1917 (class 0 OID 0) +-- Dependencies: 1600 +-- Name: clanek; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON TABLE clanek FROM PUBLIC; +REVOKE ALL ON TABLE clanek FROM nekurak; +GRANT ALL ON TABLE clanek TO nekurak; +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE clanek TO nekurak_web; + + +-- +-- TOC entry 1918 (class 0 OID 0) +-- Dependencies: 1598 -- Name: fotka_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -448,8 +565,8 @@ -- --- TOC entry 1899 (class 0 OID 0) --- Dependencies: 1593 +-- TOC entry 1921 (class 0 OID 0) +-- Dependencies: 1599 -- Name: fotka; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -460,8 +577,20 @@ -- --- TOC entry 1901 (class 0 OID 0) --- Dependencies: 1587 +-- TOC entry 1922 (class 0 OID 0) +-- Dependencies: 1603 +-- Name: hlasovani_seq; Type: ACL; Schema: nekurak; Owner: nekurak +-- + +REVOKE ALL ON SEQUENCE hlasovani_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE hlasovani_seq FROM nekurak; +GRANT ALL ON SEQUENCE hlasovani_seq TO nekurak; +GRANT USAGE ON SEQUENCE hlasovani_seq TO nekurak_web; + + +-- +-- TOC entry 1925 (class 0 OID 0) +-- Dependencies: 1604 -- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -472,8 +601,8 @@ -- --- TOC entry 1902 (class 0 OID 0) --- Dependencies: 1585 +-- TOC entry 1926 (class 0 OID 0) +-- Dependencies: 1592 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -484,8 +613,8 @@ -- --- TOC entry 1904 (class 0 OID 0) --- Dependencies: 1586 +-- TOC entry 1928 (class 0 OID 0) +-- Dependencies: 1593 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -496,8 +625,8 @@ -- --- TOC entry 1906 (class 0 OID 0) --- Dependencies: 1590 +-- TOC entry 1930 (class 0 OID 0) +-- Dependencies: 1596 -- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -508,8 +637,8 @@ -- --- TOC entry 1907 (class 0 OID 0) --- Dependencies: 1588 +-- TOC entry 1931 (class 0 OID 0) +-- Dependencies: 1594 -- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -520,8 +649,8 @@ -- --- TOC entry 1909 (class 0 OID 0) --- Dependencies: 1589 +-- TOC entry 1933 (class 0 OID 0) +-- Dependencies: 1595 -- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -532,8 +661,8 @@ -- --- TOC entry 1911 (class 0 OID 0) --- Dependencies: 1591 +-- TOC entry 1935 (class 0 OID 0) +-- Dependencies: 1597 -- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak -- @@ -543,7 +672,7 @@ GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web; --- Completed on 2010-03-16 14:32:24 CET +-- Completed on 2010-04-28 00:33:49 CEST -- -- PostgreSQL database dump complete