--
-- PostgreSQL database dump
--

-- Started on 2010-03-16 14:32:22 CET

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 10 (class 2615 OID 35015)
-- Name: nekurak; Type: SCHEMA; Schema: -; Owner: nekurak
--

CREATE SCHEMA nekurak;


ALTER SCHEMA nekurak OWNER TO nekurak;

SET search_path = nekurak, pg_catalog;

--
-- TOC entry 368 (class 1247 OID 35086)
-- Dependencies: 369 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 1894 (class 0 OID 0)
-- Dependencies: 368
-- 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 395
-- 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 1895 (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“.';


--
-- 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 1897 (class 0 OID 0)
-- Dependencies: 1593
-- Name: TABLE fotka; Type: COMMENT; Schema: nekurak; Owner: nekurak
--

COMMENT ON TABLE fotka IS 'Fotka podniku.';


--
-- TOC entry 1898 (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 1587 (class 1259 OID 35068)
-- Dependencies: 1866 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 1900 (class 0 OID 0)
-- Dependencies: 1587
-- 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 1585 (class 1259 OID 35019)
-- Dependencies: 10
-- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
--

CREATE SEQUENCE podnik_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE nekurak.podnik_seq OWNER TO nekurak;

SET default_with_oids = false;

--
-- TOC entry 1586 (class 1259 OID 35049)
-- Dependencies: 1864 1865 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,
    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),
    spravce character varying(64)
);


ALTER TABLE nekurak.podnik OWNER TO nekurak;

--
-- TOC entry 1903 (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: 
--

CREATE TABLE role (
    kod character varying(16) NOT NULL,
    popis character varying(255)
);


ALTER TABLE nekurak.role OWNER TO nekurak;

--
-- TOC entry 1905 (class 0 OID 0)
-- Dependencies: 1590
-- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
--

COMMENT ON TABLE role IS 'Role (skupiny) uživatelů ~ oprávnění';


--
-- TOC entry 1588 (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 1589 (class 1259 OID 35091)
-- Dependencies: 1867 1868 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 1908 (class 0 OID 0)
-- Dependencies: 1589
-- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
--

COMMENT ON COLUMN uzivatel.prezdivka IS 'Uživatelské jméno';


--
-- TOC entry 1591 (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 1910 (class 0 OID 0)
-- Dependencies: 1591
-- 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 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 1884 (class 2606 OID 35204)
-- Dependencies: 1593 1593 1593
-- Name: fotka_poradi_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
--

ALTER TABLE ONLY fotka
    ADD CONSTRAINT fotka_poradi_uq UNIQUE (popis, poradi);


--
-- TOC entry 1872 (class 2606 OID 35058)
-- Dependencies: 1586 1586
-- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
--

ALTER TABLE ONLY podnik
    ADD CONSTRAINT podnik_pk PRIMARY KEY (id);


--
-- TOC entry 1878 (class 2606 OID 35105)
-- Dependencies: 1590 1590
-- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
--

ALTER TABLE ONLY role
    ADD CONSTRAINT skupina_pk PRIMARY KEY (kod);


--
-- TOC entry 1874 (class 2606 OID 35098)
-- Dependencies: 1589 1589
-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
--

ALTER TABLE ONLY uzivatel
    ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id);


--
-- TOC entry 1876 (class 2606 OID 35100)
-- Dependencies: 1589 1589
-- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
--

ALTER TABLE ONLY uzivatel
    ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka);


--
-- TOC entry 1880 (class 2606 OID 35137)
-- Dependencies: 1591 1591 1591
-- 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 1890 (class 2620 OID 35125)
-- Dependencies: 1589 65
-- 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 1889 (class 2606 OID 35191)
-- Dependencies: 1871 1586 1593
-- 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 1886 (class 2606 OID 35072)
-- Dependencies: 1587 1871 1586
-- 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 1885 (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 1887 (class 2606 OID 35138)
-- Dependencies: 1590 1591 1877
-- 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 1888 (class 2606 OID 35143)
-- Dependencies: 1591 1589 1875
-- 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 1893 (class 0 OID 0)
-- Dependencies: 10
-- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
--

REVOKE ALL ON SCHEMA nekurak FROM PUBLIC;
REVOKE ALL ON SCHEMA nekurak FROM nekurak;
GRANT ALL ON SCHEMA nekurak TO nekurak;
GRANT USAGE ON SCHEMA nekurak TO nekurak_web;


--
-- TOC entry 1896 (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 1899 (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 1901 (class 0 OID 0)
-- Dependencies: 1587
-- 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 1902 (class 0 OID 0)
-- Dependencies: 1585
-- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
--

REVOKE ALL ON SEQUENCE podnik_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE podnik_seq FROM nekurak;
GRANT ALL ON SEQUENCE podnik_seq TO nekurak;
GRANT USAGE ON SEQUENCE podnik_seq TO nekurak_web;


--
-- TOC entry 1904 (class 0 OID 0)
-- Dependencies: 1586
-- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
--

REVOKE ALL ON TABLE podnik FROM PUBLIC;
REVOKE ALL ON TABLE podnik FROM nekurak;
GRANT ALL ON TABLE podnik TO nekurak;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE podnik TO nekurak_web;


--
-- TOC entry 1906 (class 0 OID 0)
-- Dependencies: 1590
-- 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 1907 (class 0 OID 0)
-- Dependencies: 1588
-- 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 1909 (class 0 OID 0)
-- Dependencies: 1589
-- 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 1911 (class 0 OID 0)
-- Dependencies: 1591
-- 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-16 14:32:24 CET

--
-- PostgreSQL database dump complete
--