
diff -r 5d93bf1c8093 -r 68f2a5ab49c1 sql/schéma.sql
--- a/sql/schéma.sql	Mon Jan 25 17:44:00 2010 +0100
+++ b/sql/schéma.sql	Sat Mar 13 23:26:55 2010 +0100
@@ -2,7 +2,7 @@
 -- PostgreSQL database dump
 --
 
--- Started on 2010-01-25 17:42:09 CET
+-- Started on 2010-03-09 20:48:44 CET
 
 SET statement_timeout = 0;
 SET client_encoding = 'UTF8';
@@ -24,7 +24,87 @@
 SET search_path = nekurak, pg_catalog;
 
 --
--- TOC entry 1568 (class 1259 OID 35019)
+-- TOC entry 369 (class 1247 OID 35086)
+-- Dependencies: 370 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 1881 (class 0 OID 0)
+-- Dependencies: 369
+-- 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 392
+-- 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 1882 (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“.';
+
+
+SET default_tablespace = '';
+
+SET default_with_oids = true;
+
+--
+-- TOC entry 1584 (class 1259 OID 35068)
+-- Dependencies: 1861 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 1883 (class 0 OID 0)
+-- Dependencies: 1584
+-- 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 1582 (class 1259 OID 35019)
 -- Dependencies: 10
 -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
 --
@@ -38,50 +118,120 @@
 
 ALTER TABLE nekurak.podnik_seq OWNER TO nekurak;
 
---
--- TOC entry 1848 (class 0 OID 0)
--- Dependencies: 1568
--- Name: podnik_seq; Type: SEQUENCE SET; Schema: nekurak; Owner: nekurak
---
-
-SELECT pg_catalog.setval('podnik_seq', 3, true);
-
-
-SET default_tablespace = '';
-
 SET default_with_oids = false;
 
 --
--- TOC entry 1569 (class 1259 OID 35021)
--- Dependencies: 1840 1841 10
+-- TOC entry 1583 (class 1259 OID 35049)
+-- Dependencies: 1859 1860 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,
-    datum timestamp with time zone DEFAULT now()
+    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)
 );
 
 
 ALTER TABLE nekurak.podnik OWNER TO nekurak;
 
 --
--- TOC entry 1844 (class 0 OID 35021)
--- Dependencies: 1569
--- Data for Name: podnik; Type: TABLE DATA; Schema: nekurak; Owner: nekurak
+-- TOC entry 1587 (class 1259 OID 35101)
+-- Dependencies: 10
+-- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
 --
 
-COPY podnik (id, nazev, datum) FROM stdin;
-1	Na Kovárně	2010-01-16 02:40:12.271326+01
-2	U přátelského potkana	2010-01-16 02:40:43.326281+01
-3	Na Růžku	2010-01-16 02:40:48.104577+01
-\.
+CREATE TABLE role (
+    kod character varying(16) NOT NULL,
+    popis character varying(255)
+);
 
 
+ALTER TABLE nekurak.role OWNER TO nekurak;
+
 --
--- TOC entry 1843 (class 2606 OID 35027)
--- Dependencies: 1569 1569
+-- TOC entry 1887 (class 0 OID 0)
+-- Dependencies: 1587
+-- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
+--
+
+COMMENT ON TABLE role IS 'Role (skupiny) uživatelů ~ oprávnění';
+
+
+--
+-- TOC entry 1585 (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 1586 (class 1259 OID 35091)
+-- Dependencies: 1862 1863 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 1890 (class 0 OID 0)
+-- Dependencies: 1586
+-- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
+--
+
+COMMENT ON COLUMN uzivatel.prezdivka IS 'Uživatelské jméno';
+
+
+--
+-- TOC entry 1588 (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 1892 (class 0 OID 0)
+-- Dependencies: 1588
+-- 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 1865 (class 2606 OID 35058)
+-- Dependencies: 1583 1583
 -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
 --
 
@@ -90,7 +240,89 @@
 
 
 --
--- TOC entry 1847 (class 0 OID 0)
+-- TOC entry 1871 (class 2606 OID 35105)
+-- Dependencies: 1587 1587
+-- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
+--
+
+ALTER TABLE ONLY role
+    ADD CONSTRAINT skupina_pk PRIMARY KEY (kod);
+
+
+--
+-- TOC entry 1867 (class 2606 OID 35098)
+-- Dependencies: 1586 1586
+-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
+--
+
+ALTER TABLE ONLY uzivatel
+    ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id);
+
+
+--
+-- TOC entry 1869 (class 2606 OID 35100)
+-- Dependencies: 1586 1586
+-- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
+--
+
+ALTER TABLE ONLY uzivatel
+    ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka);
+
+
+--
+-- TOC entry 1873 (class 2606 OID 35137)
+-- Dependencies: 1588 1588 1588
+-- 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 1877 (class 2620 OID 35125)
+-- Dependencies: 65 1586
+-- 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 1874 (class 2606 OID 35072)
+-- Dependencies: 1864 1583 1584
+-- 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 1875 (class 2606 OID 35138)
+-- Dependencies: 1870 1587 1588
+-- 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 1876 (class 2606 OID 35143)
+-- Dependencies: 1868 1586 1588
+-- 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 1880 (class 0 OID 0)
 -- Dependencies: 10
 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
 --
@@ -102,8 +334,20 @@
 
 
 --
--- TOC entry 1849 (class 0 OID 0)
--- Dependencies: 1568
+-- TOC entry 1884 (class 0 OID 0)
+-- Dependencies: 1584
+-- 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 1885 (class 0 OID 0)
+-- Dependencies: 1582
 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
 --
 
@@ -114,8 +358,8 @@
 
 
 --
--- TOC entry 1850 (class 0 OID 0)
--- Dependencies: 1569
+-- TOC entry 1886 (class 0 OID 0)
+-- Dependencies: 1583
 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
 --
 
@@ -125,7 +369,55 @@
 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE podnik TO nekurak_web;
 
 
--- Completed on 2010-01-25 17:42:10 CET
+--
+-- TOC entry 1888 (class 0 OID 0)
+-- Dependencies: 1587
+-- 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 1889 (class 0 OID 0)
+-- Dependencies: 1585
+-- 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 1891 (class 0 OID 0)
+-- Dependencies: 1586
+-- 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 1893 (class 0 OID 0)
+-- Dependencies: 1588
+-- 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-09 20:48:46 CET
 
 --
 -- PostgreSQL database dump complete




