franta-hg@18: --
franta-hg@18: -- PostgreSQL database dump
franta-hg@18: --
franta-hg@18: 
franta-hg@62: -- Started on 2010-03-09 20:48:44 CET
franta-hg@18: 
franta-hg@18: SET statement_timeout = 0;
franta-hg@18: SET client_encoding = 'UTF8';
franta-hg@18: SET standard_conforming_strings = off;
franta-hg@18: SET check_function_bodies = false;
franta-hg@18: SET client_min_messages = warning;
franta-hg@18: SET escape_string_warning = off;
franta-hg@18: 
franta-hg@18: --
franta-hg@18: -- TOC entry 10 (class 2615 OID 35015)
franta-hg@18: -- Name: nekurak; Type: SCHEMA; Schema: -; Owner: nekurak
franta-hg@18: --
franta-hg@18: 
franta-hg@18: CREATE SCHEMA nekurak;
franta-hg@18: 
franta-hg@18: 
franta-hg@18: ALTER SCHEMA nekurak OWNER TO nekurak;
franta-hg@18: 
franta-hg@18: SET search_path = nekurak, pg_catalog;
franta-hg@18: 
franta-hg@18: --
franta-hg@62: -- TOC entry 369 (class 1247 OID 35086)
franta-hg@62: -- Dependencies: 370 10
franta-hg@62: -- Name: email; Type: DOMAIN; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: CREATE DOMAIN email AS character varying(255)
franta-hg@62: 	CONSTRAINT email_regexp CHECK (((VALUE)::text ~ '^[_a-zA-Z0-9\\.\\-]+@[_a-zA-Z0-9\\.\\-]+\\.[a-zA-Z]{2,4}$'::text));
franta-hg@62: 
franta-hg@62: 
franta-hg@62: ALTER DOMAIN nekurak.email OWNER TO nekurak;
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1881 (class 0 OID 0)
franta-hg@62: -- Dependencies: 369
franta-hg@62: -- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: COMMENT ON DOMAIN email IS 'e-mailová adresa';
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 65 (class 1255 OID 35124)
franta-hg@62: -- Dependencies: 10 392
franta-hg@62: -- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: CREATE FUNCTION bezny_uzivatel() RETURNS trigger
franta-hg@62:     LANGUAGE plpgsql
franta-hg@62:     AS $$BEGIN
franta-hg@62: INSERT INTO uzivatel_role 
franta-hg@62: (role, prezdivka) 
franta-hg@62: VALUES
franta-hg@62: ('bezny', new.prezdivka);
franta-hg@62: RETURN new;
franta-hg@62: END;$$;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak;
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1882 (class 0 OID 0)
franta-hg@62: -- Dependencies: 65
franta-hg@62: -- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: COMMENT ON FUNCTION bezny_uzivatel() IS 'Každému novému uživateli dáme automaticky roli „bezny“.';
franta-hg@62: 
franta-hg@62: 
franta-hg@62: SET default_tablespace = '';
franta-hg@62: 
franta-hg@62: SET default_with_oids = true;
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1584 (class 1259 OID 35068)
franta-hg@62: -- Dependencies: 1861 10
franta-hg@62: -- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@62: --
franta-hg@62: 
franta-hg@62: CREATE TABLE hlasovani (
franta-hg@62:     podnik integer NOT NULL,
franta-hg@62:     hlas boolean NOT NULL,
franta-hg@62:     datum date DEFAULT now() NOT NULL,
franta-hg@62:     ip_adresa character varying(255)
franta-hg@62: );
franta-hg@62: 
franta-hg@62: 
franta-hg@62: ALTER TABLE nekurak.hlasovani OWNER TO nekurak;
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1883 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1584
franta-hg@62: -- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: COMMENT ON COLUMN hlasovani.hlas IS 'Má být tento podnik nekuřácký?
franta-hg@62: true = nekuřácký
franta-hg@62: false = kouření povoleno';
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1582 (class 1259 OID 35019)
franta-hg@18: -- Dependencies: 10
franta-hg@18: -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
franta-hg@18: --
franta-hg@18: 
franta-hg@18: CREATE SEQUENCE podnik_seq
franta-hg@18:     INCREMENT BY 1
franta-hg@18:     NO MAXVALUE
franta-hg@18:     NO MINVALUE
franta-hg@18:     CACHE 1;
franta-hg@18: 
franta-hg@18: 
franta-hg@18: ALTER TABLE nekurak.podnik_seq OWNER TO nekurak;
franta-hg@18: 
franta-hg@18: SET default_with_oids = false;
franta-hg@18: 
franta-hg@18: --
franta-hg@62: -- TOC entry 1583 (class 1259 OID 35049)
franta-hg@62: -- Dependencies: 1859 1860 10
franta-hg@18: -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@18: --
franta-hg@18: 
franta-hg@18: CREATE TABLE podnik (
franta-hg@18:     id integer DEFAULT nextval('podnik_seq'::regclass) NOT NULL,
franta-hg@18:     nazev character varying(255) NOT NULL,
franta-hg@62:     popis character varying(255),
franta-hg@62:     url character varying(255),
franta-hg@62:     datum timestamp with time zone DEFAULT now(),
franta-hg@62:     ulice character varying(64),
franta-hg@62:     cislo_popisne integer,
franta-hg@62:     mesto character varying(64)
franta-hg@18: );
franta-hg@18: 
franta-hg@18: 
franta-hg@18: ALTER TABLE nekurak.podnik OWNER TO nekurak;
franta-hg@18: 
franta-hg@18: --
franta-hg@62: -- TOC entry 1587 (class 1259 OID 35101)
franta-hg@62: -- Dependencies: 10
franta-hg@62: -- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@18: --
franta-hg@18: 
franta-hg@62: CREATE TABLE role (
franta-hg@62:     kod character varying(16) NOT NULL,
franta-hg@62:     popis character varying(255)
franta-hg@62: );
franta-hg@18: 
franta-hg@18: 
franta-hg@62: ALTER TABLE nekurak.role OWNER TO nekurak;
franta-hg@62: 
franta-hg@18: --
franta-hg@62: -- TOC entry 1887 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1587
franta-hg@62: -- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: COMMENT ON TABLE role IS 'Role (skupiny) uživatelů ~ oprávnění';
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1585 (class 1259 OID 35080)
franta-hg@62: -- Dependencies: 10
franta-hg@62: -- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: CREATE SEQUENCE uzivatel_seq
franta-hg@62:     INCREMENT BY 1
franta-hg@62:     NO MAXVALUE
franta-hg@62:     NO MINVALUE
franta-hg@62:     CACHE 1;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak;
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1586 (class 1259 OID 35091)
franta-hg@62: -- Dependencies: 1862 1863 10
franta-hg@62: -- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@62: --
franta-hg@62: 
franta-hg@62: CREATE TABLE uzivatel (
franta-hg@62:     id integer DEFAULT nextval('uzivatel_seq'::regclass) NOT NULL,
franta-hg@62:     prezdivka character varying(64) NOT NULL,
franta-hg@62:     heslo character varying(512) NOT NULL,
franta-hg@62:     jmeno character varying(64),
franta-hg@62:     prijmeni character varying(64),
franta-hg@62:     email character varying(255),
franta-hg@62:     datum timestamp with time zone DEFAULT now() NOT NULL
franta-hg@62: );
franta-hg@62: 
franta-hg@62: 
franta-hg@62: ALTER TABLE nekurak.uzivatel OWNER TO nekurak;
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1890 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1586
franta-hg@62: -- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: COMMENT ON COLUMN uzivatel.prezdivka IS 'Uživatelské jméno';
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1588 (class 1259 OID 35106)
franta-hg@62: -- Dependencies: 10
franta-hg@62: -- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@62: --
franta-hg@62: 
franta-hg@62: CREATE TABLE uzivatel_role (
franta-hg@62:     role character varying(16) NOT NULL,
franta-hg@62:     prezdivka character varying(64) NOT NULL
franta-hg@62: );
franta-hg@62: 
franta-hg@62: 
franta-hg@62: ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak;
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1892 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1588
franta-hg@62: -- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: COMMENT ON TABLE uzivatel_role IS 'Přiřazení uživatelských rolí (skupin).';
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1865 (class 2606 OID 35058)
franta-hg@62: -- Dependencies: 1583 1583
franta-hg@18: -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@18: --
franta-hg@18: 
franta-hg@18: ALTER TABLE ONLY podnik
franta-hg@18:     ADD CONSTRAINT podnik_pk PRIMARY KEY (id);
franta-hg@18: 
franta-hg@18: 
franta-hg@19: --
franta-hg@62: -- TOC entry 1871 (class 2606 OID 35105)
franta-hg@62: -- Dependencies: 1587 1587
franta-hg@62: -- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@62: --
franta-hg@62: 
franta-hg@62: ALTER TABLE ONLY role
franta-hg@62:     ADD CONSTRAINT skupina_pk PRIMARY KEY (kod);
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1867 (class 2606 OID 35098)
franta-hg@62: -- Dependencies: 1586 1586
franta-hg@62: -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@62: --
franta-hg@62: 
franta-hg@62: ALTER TABLE ONLY uzivatel
franta-hg@62:     ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id);
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1869 (class 2606 OID 35100)
franta-hg@62: -- Dependencies: 1586 1586
franta-hg@62: -- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@62: --
franta-hg@62: 
franta-hg@62: ALTER TABLE ONLY uzivatel
franta-hg@62:     ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka);
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1873 (class 2606 OID 35137)
franta-hg@62: -- Dependencies: 1588 1588 1588
franta-hg@62: -- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
franta-hg@62: --
franta-hg@62: 
franta-hg@62: ALTER TABLE ONLY uzivatel_role
franta-hg@62:     ADD CONSTRAINT uzivatel_role_pk PRIMARY KEY (role, prezdivka);
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1877 (class 2620 OID 35125)
franta-hg@62: -- Dependencies: 65 1586
franta-hg@62: -- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: CREATE TRIGGER uzivatel_insert
franta-hg@62:     AFTER INSERT ON uzivatel
franta-hg@62:     FOR EACH ROW
franta-hg@62:     EXECUTE PROCEDURE bezny_uzivatel();
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1874 (class 2606 OID 35072)
franta-hg@62: -- Dependencies: 1864 1583 1584
franta-hg@62: -- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: ALTER TABLE ONLY hlasovani
franta-hg@62:     ADD CONSTRAINT hlasovani_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1875 (class 2606 OID 35138)
franta-hg@62: -- Dependencies: 1870 1587 1588
franta-hg@62: -- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: ALTER TABLE ONLY uzivatel_role
franta-hg@62:     ADD CONSTRAINT uzivatel_role_role_fk FOREIGN KEY (role) REFERENCES role(kod) ON UPDATE CASCADE ON DELETE CASCADE;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1876 (class 2606 OID 35143)
franta-hg@62: -- Dependencies: 1868 1586 1588
franta-hg@62: -- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: ALTER TABLE ONLY uzivatel_role
franta-hg@62:     ADD CONSTRAINT uzivatel_role_uzivatel_fk FOREIGN KEY (prezdivka) REFERENCES uzivatel(prezdivka) ON UPDATE CASCADE ON DELETE CASCADE;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1880 (class 0 OID 0)
franta-hg@19: -- Dependencies: 10
franta-hg@19: -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
franta-hg@19: --
franta-hg@19: 
franta-hg@19: REVOKE ALL ON SCHEMA nekurak FROM PUBLIC;
franta-hg@19: REVOKE ALL ON SCHEMA nekurak FROM nekurak;
franta-hg@19: GRANT ALL ON SCHEMA nekurak TO nekurak;
franta-hg@19: GRANT USAGE ON SCHEMA nekurak TO nekurak_web;
franta-hg@19: 
franta-hg@19: 
franta-hg@19: --
franta-hg@62: -- TOC entry 1884 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1584
franta-hg@62: -- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: REVOKE ALL ON TABLE hlasovani FROM PUBLIC;
franta-hg@62: REVOKE ALL ON TABLE hlasovani FROM nekurak;
franta-hg@62: GRANT ALL ON TABLE hlasovani TO nekurak;
franta-hg@62: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE hlasovani TO nekurak_web;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1885 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1582
franta-hg@19: -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
franta-hg@19: --
franta-hg@19: 
franta-hg@19: REVOKE ALL ON SEQUENCE podnik_seq FROM PUBLIC;
franta-hg@19: REVOKE ALL ON SEQUENCE podnik_seq FROM nekurak;
franta-hg@19: GRANT ALL ON SEQUENCE podnik_seq TO nekurak;
franta-hg@19: GRANT USAGE ON SEQUENCE podnik_seq TO nekurak_web;
franta-hg@19: 
franta-hg@19: 
franta-hg@19: --
franta-hg@62: -- TOC entry 1886 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1583
franta-hg@19: -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
franta-hg@19: --
franta-hg@19: 
franta-hg@19: REVOKE ALL ON TABLE podnik FROM PUBLIC;
franta-hg@19: REVOKE ALL ON TABLE podnik FROM nekurak;
franta-hg@19: GRANT ALL ON TABLE podnik TO nekurak;
franta-hg@19: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE podnik TO nekurak_web;
franta-hg@19: 
franta-hg@19: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1888 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1587
franta-hg@62: -- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: REVOKE ALL ON TABLE role FROM PUBLIC;
franta-hg@62: REVOKE ALL ON TABLE role FROM nekurak;
franta-hg@62: GRANT ALL ON TABLE role TO nekurak;
franta-hg@62: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE role TO nekurak_web;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1889 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1585
franta-hg@62: -- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: REVOKE ALL ON SEQUENCE uzivatel_seq FROM PUBLIC;
franta-hg@62: REVOKE ALL ON SEQUENCE uzivatel_seq FROM nekurak;
franta-hg@62: GRANT ALL ON SEQUENCE uzivatel_seq TO nekurak;
franta-hg@62: GRANT USAGE ON SEQUENCE uzivatel_seq TO nekurak_web;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1891 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1586
franta-hg@62: -- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
franta-hg@62: REVOKE ALL ON TABLE uzivatel FROM nekurak;
franta-hg@62: GRANT ALL ON TABLE uzivatel TO nekurak;
franta-hg@62: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel TO nekurak_web;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: --
franta-hg@62: -- TOC entry 1893 (class 0 OID 0)
franta-hg@62: -- Dependencies: 1588
franta-hg@62: -- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak
franta-hg@62: --
franta-hg@62: 
franta-hg@62: REVOKE ALL ON TABLE uzivatel_role FROM PUBLIC;
franta-hg@62: REVOKE ALL ON TABLE uzivatel_role FROM nekurak;
franta-hg@62: GRANT ALL ON TABLE uzivatel_role TO nekurak;
franta-hg@62: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web;
franta-hg@62: 
franta-hg@62: 
franta-hg@62: -- Completed on 2010-03-09 20:48:46 CET
franta-hg@18: 
franta-hg@18: --
franta-hg@18: -- PostgreSQL database dump complete
franta-hg@18: --
franta-hg@18: