--

-- PostgreSQL database dump

--

-- Started on 2010-04-29 14:41:40 CEST

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 366 (class 1247 OID 35086)

-- Dependencies: 367 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 1914 (class 0 OID 0)

-- Dependencies: 366

-- 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 402

-- 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 1915 (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 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 1917 (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

--

CREATE SEQUENCE fotka_seq

    START WITH 1

    INCREMENT BY 1

    NO MAXVALUE

    NO MINVALUE

    CACHE 1;

ALTER TABLE nekurak.fotka_seq OWNER TO nekurak;

--

-- TOC entry 1599 (class 1259 OID 35185)

-- Dependencies: 1880 1881 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 1920 (class 0 OID 0)

-- Dependencies: 1599

-- Name: TABLE fotka; Type: COMMENT; Schema: nekurak; Owner: nekurak

--

COMMENT ON TABLE fotka IS 'Fotka podniku.';

--

-- TOC entry 1921 (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.';

--

-- 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 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 timestamp without time zone DEFAULT now() NOT NULL,

    ip_adresa character varying(255) NOT NULL

);

ALTER TABLE nekurak.hlasovani OWNER TO nekurak;

--

-- TOC entry 1924 (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 1925 (class 0 OID 0)

-- Dependencies: 1604

-- 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 1592 (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;

--

-- TOC entry 1593 (class 1259 OID 35049)

-- Dependencies: 1876 1877 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),

    sirka double precision,

    delka double precision

);

ALTER TABLE nekurak.podnik OWNER TO nekurak;

--

-- TOC entry 1928 (class 0 OID 0)

-- Dependencies: 1593

-- 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 1596 (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 1930 (class 0 OID 0)

-- Dependencies: 1596

-- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak

--

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

--

-- TOC entry 1594 (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 1595 (class 1259 OID 35091)

-- Dependencies: 1878 1879 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 1933 (class 0 OID 0)

-- Dependencies: 1595

-- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak

--

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

--

-- TOC entry 1597 (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 1935 (class 0 OID 0)

-- Dependencies: 1597

-- 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 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: 

--

ALTER TABLE ONLY fotka

    ADD CONSTRAINT fotka_pk PRIMARY KEY (id);

--

-- TOC entry 1898 (class 2606 OID 35204)

-- Dependencies: 1599 1599 1599

-- 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 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: 

--

ALTER TABLE ONLY podnik

    ADD CONSTRAINT podnik_pk PRIMARY KEY (id);

--

-- TOC entry 1892 (class 2606 OID 35105)

-- Dependencies: 1596 1596

-- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 

--

ALTER TABLE ONLY role

    ADD CONSTRAINT skupina_pk PRIMARY KEY (kod);

--

-- TOC entry 1888 (class 2606 OID 35098)

-- Dependencies: 1595 1595

-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 

--

ALTER TABLE ONLY uzivatel

    ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id);

--

-- TOC entry 1890 (class 2606 OID 35100)

-- Dependencies: 1595 1595

-- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 

--

ALTER TABLE ONLY uzivatel

    ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka);

--

-- TOC entry 1894 (class 2606 OID 35137)

-- Dependencies: 1597 1597 1597

-- 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 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 1904 (class 1259 OID 35309)

-- Dependencies: 1604

-- Name: hlsovani_datum_idx; Type: INDEX; Schema: nekurak; Owner: nekurak; Tablespace: 

--

CREATE INDEX hlsovani_datum_idx ON hlasovani USING btree (date(datum));

--

-- TOC entry 1910 (class 2620 OID 35125)

-- Dependencies: 1595 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 1908 (class 2606 OID 35191)

-- Dependencies: 1599 1885 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 1909 (class 2606 OID 35285)

-- Dependencies: 1885 1604 1593

-- 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 1905 (class 2606 OID 35178)

-- Dependencies: 1889 1595 1593

-- 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 1906 (class 2606 OID 35138)

-- Dependencies: 1597 1596 1891

-- 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 1907 (class 2606 OID 35143)

-- Dependencies: 1597 1889 1595

-- 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 1913 (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 1916 (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 1918 (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 1919 (class 0 OID 0)

-- Dependencies: 1598

-- 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 1922 (class 0 OID 0)

-- Dependencies: 1599

-- 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 1923 (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 1926 (class 0 OID 0)

-- Dependencies: 1604

-- 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 1927 (class 0 OID 0)

-- Dependencies: 1592

-- 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 1929 (class 0 OID 0)

-- Dependencies: 1593

-- 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 1931 (class 0 OID 0)

-- Dependencies: 1596

-- 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 1932 (class 0 OID 0)

-- Dependencies: 1594

-- 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 1934 (class 0 OID 0)

-- Dependencies: 1595

-- 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 1936 (class 0 OID 0)

-- Dependencies: 1597

-- 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-04-29 14:41:42 CEST

--

-- PostgreSQL database dump complete

--