SQL: změny v databázovém schématu (role, uživatelé, rozšíření… zapomenuté změny).
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