1.1 --- a/java/nekurak.net-web/web/WEB-INF/tags/nekurak/prepinaniJazyku.tag	Tue Mar 09 20:46:54 2010 +0100
     1.2 +++ b/java/nekurak.net-web/web/WEB-INF/tags/nekurak/prepinaniJazyku.tag	Tue Mar 09 20:54:58 2010 +0100
     1.3 @@ -12,7 +12,7 @@
     1.4  	<fmt:message key="prepinaniJazyku.slovenstina.tip" var="prepinaniJazykuSlovenstinaTip"/>
     1.5  	<fmt:message key="prepinaniJazyku.anglictina" var="prepinaniJazykuAnglictina"/>
     1.6  	<fmt:message key="prepinaniJazyku.anglictina.tip" var="prepinaniJazykuAnglictinaTip"/>
     1.7 -	<a href="?jazyk=cs"><img src="grafika/jazyk_cs.png" alt="${prepinaniJazykuCestina}" title="XXX:${prepinaniJazykuCestinaTip}"/></a>
     1.8 +	<a href="?jazyk=cs"><img src="grafika/jazyk_cs.png" alt="${prepinaniJazykuCestina}" title="${prepinaniJazykuCestinaTip}"/></a>
     1.9  	<a href="?jazyk=sk"><img src="grafika/jazyk_sk.png" alt="${prepinaniJazykuSlovenstina}" title="${prepinaniJazykuSlovenstinaTip}"/></a>
    1.10  	<a href="?jazyk=en"><img src="grafika/jazyk_en.png" alt="${prepinaniJazykuAnglictina}" title="${prepinaniJazykuAnglictinaTip}"/></a>
    1.11      </div>

     2.1 --- a/sql/schéma.sql	Tue Mar 09 20:46:54 2010 +0100
     2.2 +++ b/sql/schéma.sql	Tue Mar 09 20:54:58 2010 +0100
     2.3 @@ -2,7 +2,7 @@
     2.4  -- PostgreSQL database dump
     2.5  --
     2.6  
     2.7 --- Started on 2010-01-25 17:42:09 CET
     2.8 +-- Started on 2010-03-09 20:48:44 CET
     2.9  
    2.10  SET statement_timeout = 0;
    2.11  SET client_encoding = 'UTF8';
    2.12 @@ -24,7 +24,87 @@
    2.13  SET search_path = nekurak, pg_catalog;
    2.14  
    2.15  --
    2.16 --- TOC entry 1568 (class 1259 OID 35019)
    2.17 +-- TOC entry 369 (class 1247 OID 35086)
    2.18 +-- Dependencies: 370 10
    2.19 +-- Name: email; Type: DOMAIN; Schema: nekurak; Owner: nekurak
    2.20 +--
    2.21 +
    2.22 +CREATE DOMAIN email AS character varying(255)
    2.23 +	CONSTRAINT email_regexp CHECK (((VALUE)::text ~ '^[_a-zA-Z0-9\\.\\-]+@[_a-zA-Z0-9\\.\\-]+\\.[a-zA-Z]{2,4}$'::text));
    2.24 +
    2.25 +
    2.26 +ALTER DOMAIN nekurak.email OWNER TO nekurak;
    2.27 +
    2.28 +--
    2.29 +-- TOC entry 1881 (class 0 OID 0)
    2.30 +-- Dependencies: 369
    2.31 +-- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak
    2.32 +--
    2.33 +
    2.34 +COMMENT ON DOMAIN email IS 'e-mailová adresa';
    2.35 +
    2.36 +
    2.37 +--
    2.38 +-- TOC entry 65 (class 1255 OID 35124)
    2.39 +-- Dependencies: 10 392
    2.40 +-- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak
    2.41 +--
    2.42 +
    2.43 +CREATE FUNCTION bezny_uzivatel() RETURNS trigger
    2.44 +    LANGUAGE plpgsql
    2.45 +    AS $$BEGIN
    2.46 +INSERT INTO uzivatel_role 
    2.47 +(role, prezdivka) 
    2.48 +VALUES
    2.49 +('bezny', new.prezdivka);
    2.50 +RETURN new;
    2.51 +END;$$;
    2.52 +
    2.53 +
    2.54 +ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak;
    2.55 +
    2.56 +--
    2.57 +-- TOC entry 1882 (class 0 OID 0)
    2.58 +-- Dependencies: 65
    2.59 +-- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak
    2.60 +--
    2.61 +
    2.62 +COMMENT ON FUNCTION bezny_uzivatel() IS 'Každému novému uživateli dáme automaticky roli „bezny“.';
    2.63 +
    2.64 +
    2.65 +SET default_tablespace = '';
    2.66 +
    2.67 +SET default_with_oids = true;
    2.68 +
    2.69 +--
    2.70 +-- TOC entry 1584 (class 1259 OID 35068)
    2.71 +-- Dependencies: 1861 10
    2.72 +-- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
    2.73 +--
    2.74 +
    2.75 +CREATE TABLE hlasovani (
    2.76 +    podnik integer NOT NULL,
    2.77 +    hlas boolean NOT NULL,
    2.78 +    datum date DEFAULT now() NOT NULL,
    2.79 +    ip_adresa character varying(255)
    2.80 +);
    2.81 +
    2.82 +
    2.83 +ALTER TABLE nekurak.hlasovani OWNER TO nekurak;
    2.84 +
    2.85 +--
    2.86 +-- TOC entry 1883 (class 0 OID 0)
    2.87 +-- Dependencies: 1584
    2.88 +-- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak
    2.89 +--
    2.90 +
    2.91 +COMMENT ON COLUMN hlasovani.hlas IS 'Má být tento podnik nekuřácký?
    2.92 +true = nekuřácký
    2.93 +false = kouření povoleno';
    2.94 +
    2.95 +
    2.96 +--
    2.97 +-- TOC entry 1582 (class 1259 OID 35019)
    2.98  -- Dependencies: 10
    2.99  -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
   2.100  --
   2.101 @@ -38,50 +118,120 @@
   2.102  
   2.103  ALTER TABLE nekurak.podnik_seq OWNER TO nekurak;
   2.104  
   2.105 ---
   2.106 --- TOC entry 1848 (class 0 OID 0)
   2.107 --- Dependencies: 1568
   2.108 --- Name: podnik_seq; Type: SEQUENCE SET; Schema: nekurak; Owner: nekurak
   2.109 ---
   2.110 -
   2.111 -SELECT pg_catalog.setval('podnik_seq', 3, true);
   2.112 -
   2.113 -
   2.114 -SET default_tablespace = '';
   2.115 -
   2.116  SET default_with_oids = false;
   2.117  
   2.118  --
   2.119 --- TOC entry 1569 (class 1259 OID 35021)
   2.120 --- Dependencies: 1840 1841 10
   2.121 +-- TOC entry 1583 (class 1259 OID 35049)
   2.122 +-- Dependencies: 1859 1860 10
   2.123  -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   2.124  --
   2.125  
   2.126  CREATE TABLE podnik (
   2.127      id integer DEFAULT nextval('podnik_seq'::regclass) NOT NULL,
   2.128      nazev character varying(255) NOT NULL,
   2.129 -    datum timestamp with time zone DEFAULT now()
   2.130 +    popis character varying(255),
   2.131 +    url character varying(255),
   2.132 +    datum timestamp with time zone DEFAULT now(),
   2.133 +    ulice character varying(64),
   2.134 +    cislo_popisne integer,
   2.135 +    mesto character varying(64)
   2.136  );
   2.137  
   2.138  
   2.139  ALTER TABLE nekurak.podnik OWNER TO nekurak;
   2.140  
   2.141  --
   2.142 --- TOC entry 1844 (class 0 OID 35021)
   2.143 --- Dependencies: 1569
   2.144 --- Data for Name: podnik; Type: TABLE DATA; Schema: nekurak; Owner: nekurak
   2.145 +-- TOC entry 1587 (class 1259 OID 35101)
   2.146 +-- Dependencies: 10
   2.147 +-- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   2.148  --
   2.149  
   2.150 -COPY podnik (id, nazev, datum) FROM stdin;
   2.151 -1	Na Kovárně	2010-01-16 02:40:12.271326+01
   2.152 -2	U přátelského potkana	2010-01-16 02:40:43.326281+01
   2.153 -3	Na Růžku	2010-01-16 02:40:48.104577+01
   2.154 -\.
   2.155 +CREATE TABLE role (
   2.156 +    kod character varying(16) NOT NULL,
   2.157 +    popis character varying(255)
   2.158 +);
   2.159  
   2.160  
   2.161 +ALTER TABLE nekurak.role OWNER TO nekurak;
   2.162 +
   2.163  --
   2.164 --- TOC entry 1843 (class 2606 OID 35027)
   2.165 --- Dependencies: 1569 1569
   2.166 +-- TOC entry 1887 (class 0 OID 0)
   2.167 +-- Dependencies: 1587
   2.168 +-- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
   2.169 +--
   2.170 +
   2.171 +COMMENT ON TABLE role IS 'Role (skupiny) uživatelů ~ oprávnění';
   2.172 +
   2.173 +
   2.174 +--
   2.175 +-- TOC entry 1585 (class 1259 OID 35080)
   2.176 +-- Dependencies: 10
   2.177 +-- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
   2.178 +--
   2.179 +
   2.180 +CREATE SEQUENCE uzivatel_seq
   2.181 +    INCREMENT BY 1
   2.182 +    NO MAXVALUE
   2.183 +    NO MINVALUE
   2.184 +    CACHE 1;
   2.185 +
   2.186 +
   2.187 +ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak;
   2.188 +
   2.189 +--
   2.190 +-- TOC entry 1586 (class 1259 OID 35091)
   2.191 +-- Dependencies: 1862 1863 10
   2.192 +-- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   2.193 +--
   2.194 +
   2.195 +CREATE TABLE uzivatel (
   2.196 +    id integer DEFAULT nextval('uzivatel_seq'::regclass) NOT NULL,
   2.197 +    prezdivka character varying(64) NOT NULL,
   2.198 +    heslo character varying(512) NOT NULL,
   2.199 +    jmeno character varying(64),
   2.200 +    prijmeni character varying(64),
   2.201 +    email character varying(255),
   2.202 +    datum timestamp with time zone DEFAULT now() NOT NULL
   2.203 +);
   2.204 +
   2.205 +
   2.206 +ALTER TABLE nekurak.uzivatel OWNER TO nekurak;
   2.207 +
   2.208 +--
   2.209 +-- TOC entry 1890 (class 0 OID 0)
   2.210 +-- Dependencies: 1586
   2.211 +-- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
   2.212 +--
   2.213 +
   2.214 +COMMENT ON COLUMN uzivatel.prezdivka IS 'Uživatelské jméno';
   2.215 +
   2.216 +
   2.217 +--
   2.218 +-- TOC entry 1588 (class 1259 OID 35106)
   2.219 +-- Dependencies: 10
   2.220 +-- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   2.221 +--
   2.222 +
   2.223 +CREATE TABLE uzivatel_role (
   2.224 +    role character varying(16) NOT NULL,
   2.225 +    prezdivka character varying(64) NOT NULL
   2.226 +);
   2.227 +
   2.228 +
   2.229 +ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak;
   2.230 +
   2.231 +--
   2.232 +-- TOC entry 1892 (class 0 OID 0)
   2.233 +-- Dependencies: 1588
   2.234 +-- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak
   2.235 +--
   2.236 +
   2.237 +COMMENT ON TABLE uzivatel_role IS 'Přiřazení uživatelských rolí (skupin).';
   2.238 +
   2.239 +
   2.240 +--
   2.241 +-- TOC entry 1865 (class 2606 OID 35058)
   2.242 +-- Dependencies: 1583 1583
   2.243  -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   2.244  --
   2.245  
   2.246 @@ -90,7 +240,89 @@
   2.247  
   2.248  
   2.249  --
   2.250 --- TOC entry 1847 (class 0 OID 0)
   2.251 +-- TOC entry 1871 (class 2606 OID 35105)
   2.252 +-- Dependencies: 1587 1587
   2.253 +-- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   2.254 +--
   2.255 +
   2.256 +ALTER TABLE ONLY role
   2.257 +    ADD CONSTRAINT skupina_pk PRIMARY KEY (kod);
   2.258 +
   2.259 +
   2.260 +--
   2.261 +-- TOC entry 1867 (class 2606 OID 35098)
   2.262 +-- Dependencies: 1586 1586
   2.263 +-- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   2.264 +--
   2.265 +
   2.266 +ALTER TABLE ONLY uzivatel
   2.267 +    ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id);
   2.268 +
   2.269 +
   2.270 +--
   2.271 +-- TOC entry 1869 (class 2606 OID 35100)
   2.272 +-- Dependencies: 1586 1586
   2.273 +-- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   2.274 +--
   2.275 +
   2.276 +ALTER TABLE ONLY uzivatel
   2.277 +    ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka);
   2.278 +
   2.279 +
   2.280 +--
   2.281 +-- TOC entry 1873 (class 2606 OID 35137)
   2.282 +-- Dependencies: 1588 1588 1588
   2.283 +-- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   2.284 +--
   2.285 +
   2.286 +ALTER TABLE ONLY uzivatel_role
   2.287 +    ADD CONSTRAINT uzivatel_role_pk PRIMARY KEY (role, prezdivka);
   2.288 +
   2.289 +
   2.290 +--
   2.291 +-- TOC entry 1877 (class 2620 OID 35125)
   2.292 +-- Dependencies: 65 1586
   2.293 +-- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak
   2.294 +--
   2.295 +
   2.296 +CREATE TRIGGER uzivatel_insert
   2.297 +    AFTER INSERT ON uzivatel
   2.298 +    FOR EACH ROW
   2.299 +    EXECUTE PROCEDURE bezny_uzivatel();
   2.300 +
   2.301 +
   2.302 +--
   2.303 +-- TOC entry 1874 (class 2606 OID 35072)
   2.304 +-- Dependencies: 1864 1583 1584
   2.305 +-- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   2.306 +--
   2.307 +
   2.308 +ALTER TABLE ONLY hlasovani
   2.309 +    ADD CONSTRAINT hlasovani_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE;
   2.310 +
   2.311 +
   2.312 +--
   2.313 +-- TOC entry 1875 (class 2606 OID 35138)
   2.314 +-- Dependencies: 1870 1587 1588
   2.315 +-- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   2.316 +--
   2.317 +
   2.318 +ALTER TABLE ONLY uzivatel_role
   2.319 +    ADD CONSTRAINT uzivatel_role_role_fk FOREIGN KEY (role) REFERENCES role(kod) ON UPDATE CASCADE ON DELETE CASCADE;
   2.320 +
   2.321 +
   2.322 +--
   2.323 +-- TOC entry 1876 (class 2606 OID 35143)
   2.324 +-- Dependencies: 1868 1586 1588
   2.325 +-- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   2.326 +--
   2.327 +
   2.328 +ALTER TABLE ONLY uzivatel_role
   2.329 +    ADD CONSTRAINT uzivatel_role_uzivatel_fk FOREIGN KEY (prezdivka) REFERENCES uzivatel(prezdivka) ON UPDATE CASCADE ON DELETE CASCADE;
   2.330 +
   2.331 +
   2.332 +--
   2.333 +-- TOC entry 1880 (class 0 OID 0)
   2.334  -- Dependencies: 10
   2.335  -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
   2.336  --
   2.337 @@ -102,8 +334,20 @@
   2.338  
   2.339  
   2.340  --
   2.341 --- TOC entry 1849 (class 0 OID 0)
   2.342 --- Dependencies: 1568
   2.343 +-- TOC entry 1884 (class 0 OID 0)
   2.344 +-- Dependencies: 1584
   2.345 +-- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak
   2.346 +--
   2.347 +
   2.348 +REVOKE ALL ON TABLE hlasovani FROM PUBLIC;
   2.349 +REVOKE ALL ON TABLE hlasovani FROM nekurak;
   2.350 +GRANT ALL ON TABLE hlasovani TO nekurak;
   2.351 +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE hlasovani TO nekurak_web;
   2.352 +
   2.353 +
   2.354 +--
   2.355 +-- TOC entry 1885 (class 0 OID 0)
   2.356 +-- Dependencies: 1582
   2.357  -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
   2.358  --
   2.359  
   2.360 @@ -114,8 +358,8 @@
   2.361  
   2.362  
   2.363  --
   2.364 --- TOC entry 1850 (class 0 OID 0)
   2.365 --- Dependencies: 1569
   2.366 +-- TOC entry 1886 (class 0 OID 0)
   2.367 +-- Dependencies: 1583
   2.368  -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
   2.369  --
   2.370  
   2.371 @@ -125,7 +369,55 @@
   2.372  GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE podnik TO nekurak_web;
   2.373  
   2.374  
   2.375 --- Completed on 2010-01-25 17:42:10 CET
   2.376 +--
   2.377 +-- TOC entry 1888 (class 0 OID 0)
   2.378 +-- Dependencies: 1587
   2.379 +-- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak
   2.380 +--
   2.381 +
   2.382 +REVOKE ALL ON TABLE role FROM PUBLIC;
   2.383 +REVOKE ALL ON TABLE role FROM nekurak;
   2.384 +GRANT ALL ON TABLE role TO nekurak;
   2.385 +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE role TO nekurak_web;
   2.386 +
   2.387 +
   2.388 +--
   2.389 +-- TOC entry 1889 (class 0 OID 0)
   2.390 +-- Dependencies: 1585
   2.391 +-- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak
   2.392 +--
   2.393 +
   2.394 +REVOKE ALL ON SEQUENCE uzivatel_seq FROM PUBLIC;
   2.395 +REVOKE ALL ON SEQUENCE uzivatel_seq FROM nekurak;
   2.396 +GRANT ALL ON SEQUENCE uzivatel_seq TO nekurak;
   2.397 +GRANT USAGE ON SEQUENCE uzivatel_seq TO nekurak_web;
   2.398 +
   2.399 +
   2.400 +--
   2.401 +-- TOC entry 1891 (class 0 OID 0)
   2.402 +-- Dependencies: 1586
   2.403 +-- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak
   2.404 +--
   2.405 +
   2.406 +REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
   2.407 +REVOKE ALL ON TABLE uzivatel FROM nekurak;
   2.408 +GRANT ALL ON TABLE uzivatel TO nekurak;
   2.409 +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel TO nekurak_web;
   2.410 +
   2.411 +
   2.412 +--
   2.413 +-- TOC entry 1893 (class 0 OID 0)
   2.414 +-- Dependencies: 1588
   2.415 +-- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak
   2.416 +--
   2.417 +
   2.418 +REVOKE ALL ON TABLE uzivatel_role FROM PUBLIC;
   2.419 +REVOKE ALL ON TABLE uzivatel_role FROM nekurak;
   2.420 +GRANT ALL ON TABLE uzivatel_role TO nekurak;
   2.421 +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web;
   2.422 +
   2.423 +
   2.424 +-- Completed on 2010-03-09 20:48:46 CET
   2.425  
   2.426  --
   2.427  -- PostgreSQL database dump complete