sql/schéma.sql
author František Kučera <franta-hg@frantovo.cz>
Tue, 23 Mar 2010 00:54:44 +0100
changeset 76 40b228296815
parent 72 81c64c582a26
child 115 b278b6b2d552
permissions -rw-r--r--
ZIP se zdrojáky – nebalit přebytečné věci z REST modulu.
     1 --
     2 -- PostgreSQL database dump
     3 --
     4 
     5 -- Started on 2010-03-16 14:32:22 CET
     6 
     7 SET statement_timeout = 0;
     8 SET client_encoding = 'UTF8';
     9 SET standard_conforming_strings = off;
    10 SET check_function_bodies = false;
    11 SET client_min_messages = warning;
    12 SET escape_string_warning = off;
    13 
    14 --
    15 -- TOC entry 10 (class 2615 OID 35015)
    16 -- Name: nekurak; Type: SCHEMA; Schema: -; Owner: nekurak
    17 --
    18 
    19 CREATE SCHEMA nekurak;
    20 
    21 
    22 ALTER SCHEMA nekurak OWNER TO nekurak;
    23 
    24 SET search_path = nekurak, pg_catalog;
    25 
    26 --
    27 -- TOC entry 368 (class 1247 OID 35086)
    28 -- Dependencies: 369 10
    29 -- Name: email; Type: DOMAIN; Schema: nekurak; Owner: nekurak
    30 --
    31 
    32 CREATE DOMAIN email AS character varying(255)
    33 	CONSTRAINT email_regexp CHECK (((VALUE)::text ~ '^[_a-zA-Z0-9\\.\\-]+@[_a-zA-Z0-9\\.\\-]+\\.[a-zA-Z]{2,4}$'::text));
    34 
    35 
    36 ALTER DOMAIN nekurak.email OWNER TO nekurak;
    37 
    38 --
    39 -- TOC entry 1894 (class 0 OID 0)
    40 -- Dependencies: 368
    41 -- Name: DOMAIN email; Type: COMMENT; Schema: nekurak; Owner: nekurak
    42 --
    43 
    44 COMMENT ON DOMAIN email IS 'e-mailová adresa';
    45 
    46 
    47 --
    48 -- TOC entry 65 (class 1255 OID 35124)
    49 -- Dependencies: 10 395
    50 -- Name: bezny_uzivatel(); Type: FUNCTION; Schema: nekurak; Owner: nekurak
    51 --
    52 
    53 CREATE FUNCTION bezny_uzivatel() RETURNS trigger
    54     LANGUAGE plpgsql
    55     AS $$BEGIN
    56 INSERT INTO uzivatel_role 
    57 (role, prezdivka) 
    58 VALUES
    59 ('bezny', new.prezdivka);
    60 RETURN new;
    61 END;$$;
    62 
    63 
    64 ALTER FUNCTION nekurak.bezny_uzivatel() OWNER TO nekurak;
    65 
    66 --
    67 -- TOC entry 1895 (class 0 OID 0)
    68 -- Dependencies: 65
    69 -- Name: FUNCTION bezny_uzivatel(); Type: COMMENT; Schema: nekurak; Owner: nekurak
    70 --
    71 
    72 COMMENT ON FUNCTION bezny_uzivatel() IS 'Každému novému uživateli dáme automaticky roli „bezny“.';
    73 
    74 
    75 --
    76 -- TOC entry 1592 (class 1259 OID 35183)
    77 -- Dependencies: 10
    78 -- Name: fotka_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
    79 --
    80 
    81 CREATE SEQUENCE fotka_seq
    82     START WITH 1
    83     INCREMENT BY 1
    84     NO MAXVALUE
    85     NO MINVALUE
    86     CACHE 1;
    87 
    88 
    89 ALTER TABLE nekurak.fotka_seq OWNER TO nekurak;
    90 
    91 SET default_tablespace = '';
    92 
    93 SET default_with_oids = false;
    94 
    95 --
    96 -- TOC entry 1593 (class 1259 OID 35185)
    97 -- Dependencies: 1869 1870 10
    98 -- Name: fotka; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
    99 --
   100 
   101 CREATE TABLE fotka (
   102     id integer DEFAULT nextval('fotka_seq'::regclass) NOT NULL,
   103     podnik integer NOT NULL,
   104     popis character varying(255),
   105     poradi integer DEFAULT 0 NOT NULL
   106 );
   107 
   108 
   109 ALTER TABLE nekurak.fotka OWNER TO nekurak;
   110 
   111 --
   112 -- TOC entry 1897 (class 0 OID 0)
   113 -- Dependencies: 1593
   114 -- Name: TABLE fotka; Type: COMMENT; Schema: nekurak; Owner: nekurak
   115 --
   116 
   117 COMMENT ON TABLE fotka IS 'Fotka podniku.';
   118 
   119 
   120 --
   121 -- TOC entry 1898 (class 0 OID 0)
   122 -- Dependencies: 1593
   123 -- Name: COLUMN fotka.poradi; Type: COMMENT; Schema: nekurak; Owner: nekurak
   124 --
   125 
   126 COMMENT ON COLUMN fotka.poradi IS 'Pořadí fotky v rámci daného podniku.';
   127 
   128 
   129 SET default_with_oids = true;
   130 
   131 --
   132 -- TOC entry 1587 (class 1259 OID 35068)
   133 -- Dependencies: 1866 10
   134 -- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   135 --
   136 
   137 CREATE TABLE hlasovani (
   138     podnik integer NOT NULL,
   139     hlas boolean NOT NULL,
   140     datum date DEFAULT now() NOT NULL,
   141     ip_adresa character varying(255)
   142 );
   143 
   144 
   145 ALTER TABLE nekurak.hlasovani OWNER TO nekurak;
   146 
   147 --
   148 -- TOC entry 1900 (class 0 OID 0)
   149 -- Dependencies: 1587
   150 -- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak
   151 --
   152 
   153 COMMENT ON COLUMN hlasovani.hlas IS 'Má být tento podnik nekuřácký?
   154 true = nekuřácký
   155 false = kouření povoleno';
   156 
   157 
   158 --
   159 -- TOC entry 1585 (class 1259 OID 35019)
   160 -- Dependencies: 10
   161 -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
   162 --
   163 
   164 CREATE SEQUENCE podnik_seq
   165     INCREMENT BY 1
   166     NO MAXVALUE
   167     NO MINVALUE
   168     CACHE 1;
   169 
   170 
   171 ALTER TABLE nekurak.podnik_seq OWNER TO nekurak;
   172 
   173 SET default_with_oids = false;
   174 
   175 --
   176 -- TOC entry 1586 (class 1259 OID 35049)
   177 -- Dependencies: 1864 1865 10
   178 -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   179 --
   180 
   181 CREATE TABLE podnik (
   182     id integer DEFAULT nextval('podnik_seq'::regclass) NOT NULL,
   183     nazev character varying(255) NOT NULL,
   184     popis character varying(255),
   185     url character varying(255),
   186     datum timestamp with time zone DEFAULT now(),
   187     ulice character varying(64),
   188     cislo_popisne integer,
   189     mesto character varying(64),
   190     spravce character varying(64)
   191 );
   192 
   193 
   194 ALTER TABLE nekurak.podnik OWNER TO nekurak;
   195 
   196 --
   197 -- TOC entry 1903 (class 0 OID 0)
   198 -- Dependencies: 1586
   199 -- Name: COLUMN podnik.spravce; Type: COMMENT; Schema: nekurak; Owner: nekurak
   200 --
   201 
   202 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)';
   203 
   204 
   205 --
   206 -- TOC entry 1590 (class 1259 OID 35101)
   207 -- Dependencies: 10
   208 -- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   209 --
   210 
   211 CREATE TABLE role (
   212     kod character varying(16) NOT NULL,
   213     popis character varying(255)
   214 );
   215 
   216 
   217 ALTER TABLE nekurak.role OWNER TO nekurak;
   218 
   219 --
   220 -- TOC entry 1905 (class 0 OID 0)
   221 -- Dependencies: 1590
   222 -- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
   223 --
   224 
   225 COMMENT ON TABLE role IS 'Role (skupiny) uživatelů ~ oprávnění';
   226 
   227 
   228 --
   229 -- TOC entry 1588 (class 1259 OID 35080)
   230 -- Dependencies: 10
   231 -- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
   232 --
   233 
   234 CREATE SEQUENCE uzivatel_seq
   235     INCREMENT BY 1
   236     NO MAXVALUE
   237     NO MINVALUE
   238     CACHE 1;
   239 
   240 
   241 ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak;
   242 
   243 --
   244 -- TOC entry 1589 (class 1259 OID 35091)
   245 -- Dependencies: 1867 1868 10
   246 -- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   247 --
   248 
   249 CREATE TABLE uzivatel (
   250     id integer DEFAULT nextval('uzivatel_seq'::regclass) NOT NULL,
   251     prezdivka character varying(64) NOT NULL,
   252     heslo character varying(512) NOT NULL,
   253     jmeno character varying(64),
   254     prijmeni character varying(64),
   255     email character varying(255),
   256     datum timestamp with time zone DEFAULT now() NOT NULL
   257 );
   258 
   259 
   260 ALTER TABLE nekurak.uzivatel OWNER TO nekurak;
   261 
   262 --
   263 -- TOC entry 1908 (class 0 OID 0)
   264 -- Dependencies: 1589
   265 -- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
   266 --
   267 
   268 COMMENT ON COLUMN uzivatel.prezdivka IS 'Uživatelské jméno';
   269 
   270 
   271 --
   272 -- TOC entry 1591 (class 1259 OID 35106)
   273 -- Dependencies: 10
   274 -- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   275 --
   276 
   277 CREATE TABLE uzivatel_role (
   278     role character varying(16) NOT NULL,
   279     prezdivka character varying(64) NOT NULL
   280 );
   281 
   282 
   283 ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak;
   284 
   285 --
   286 -- TOC entry 1910 (class 0 OID 0)
   287 -- Dependencies: 1591
   288 -- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak
   289 --
   290 
   291 COMMENT ON TABLE uzivatel_role IS 'Přiřazení uživatelských rolí (skupin).';
   292 
   293 
   294 --
   295 -- TOC entry 1882 (class 2606 OID 35190)
   296 -- Dependencies: 1593 1593
   297 -- Name: fotka_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   298 --
   299 
   300 ALTER TABLE ONLY fotka
   301     ADD CONSTRAINT fotka_pk PRIMARY KEY (id);
   302 
   303 
   304 --
   305 -- TOC entry 1884 (class 2606 OID 35204)
   306 -- Dependencies: 1593 1593 1593
   307 -- Name: fotka_poradi_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   308 --
   309 
   310 ALTER TABLE ONLY fotka
   311     ADD CONSTRAINT fotka_poradi_uq UNIQUE (popis, poradi);
   312 
   313 
   314 --
   315 -- TOC entry 1872 (class 2606 OID 35058)
   316 -- Dependencies: 1586 1586
   317 -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   318 --
   319 
   320 ALTER TABLE ONLY podnik
   321     ADD CONSTRAINT podnik_pk PRIMARY KEY (id);
   322 
   323 
   324 --
   325 -- TOC entry 1878 (class 2606 OID 35105)
   326 -- Dependencies: 1590 1590
   327 -- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   328 --
   329 
   330 ALTER TABLE ONLY role
   331     ADD CONSTRAINT skupina_pk PRIMARY KEY (kod);
   332 
   333 
   334 --
   335 -- TOC entry 1874 (class 2606 OID 35098)
   336 -- Dependencies: 1589 1589
   337 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   338 --
   339 
   340 ALTER TABLE ONLY uzivatel
   341     ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id);
   342 
   343 
   344 --
   345 -- TOC entry 1876 (class 2606 OID 35100)
   346 -- Dependencies: 1589 1589
   347 -- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   348 --
   349 
   350 ALTER TABLE ONLY uzivatel
   351     ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka);
   352 
   353 
   354 --
   355 -- TOC entry 1880 (class 2606 OID 35137)
   356 -- Dependencies: 1591 1591 1591
   357 -- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   358 --
   359 
   360 ALTER TABLE ONLY uzivatel_role
   361     ADD CONSTRAINT uzivatel_role_pk PRIMARY KEY (role, prezdivka);
   362 
   363 
   364 --
   365 -- TOC entry 1890 (class 2620 OID 35125)
   366 -- Dependencies: 1589 65
   367 -- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak
   368 --
   369 
   370 CREATE TRIGGER uzivatel_insert
   371     AFTER INSERT ON uzivatel
   372     FOR EACH ROW
   373     EXECUTE PROCEDURE bezny_uzivatel();
   374 
   375 
   376 --
   377 -- TOC entry 1889 (class 2606 OID 35191)
   378 -- Dependencies: 1871 1586 1593
   379 -- Name: fotka_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   380 --
   381 
   382 ALTER TABLE ONLY fotka
   383     ADD CONSTRAINT fotka_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE;
   384 
   385 
   386 --
   387 -- TOC entry 1886 (class 2606 OID 35072)
   388 -- Dependencies: 1587 1871 1586
   389 -- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   390 --
   391 
   392 ALTER TABLE ONLY hlasovani
   393     ADD CONSTRAINT hlasovani_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE;
   394 
   395 
   396 --
   397 -- TOC entry 1885 (class 2606 OID 35178)
   398 -- Dependencies: 1875 1589 1586
   399 -- Name: podnik_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   400 --
   401 
   402 ALTER TABLE ONLY podnik
   403     ADD CONSTRAINT podnik_uzivatel_fk FOREIGN KEY (spravce) REFERENCES uzivatel(prezdivka) ON DELETE SET NULL;
   404 
   405 
   406 --
   407 -- TOC entry 1887 (class 2606 OID 35138)
   408 -- Dependencies: 1590 1591 1877
   409 -- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   410 --
   411 
   412 ALTER TABLE ONLY uzivatel_role
   413     ADD CONSTRAINT uzivatel_role_role_fk FOREIGN KEY (role) REFERENCES role(kod) ON UPDATE CASCADE ON DELETE CASCADE;
   414 
   415 
   416 --
   417 -- TOC entry 1888 (class 2606 OID 35143)
   418 -- Dependencies: 1591 1589 1875
   419 -- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   420 --
   421 
   422 ALTER TABLE ONLY uzivatel_role
   423     ADD CONSTRAINT uzivatel_role_uzivatel_fk FOREIGN KEY (prezdivka) REFERENCES uzivatel(prezdivka) ON UPDATE CASCADE ON DELETE CASCADE;
   424 
   425 
   426 --
   427 -- TOC entry 1893 (class 0 OID 0)
   428 -- Dependencies: 10
   429 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
   430 --
   431 
   432 REVOKE ALL ON SCHEMA nekurak FROM PUBLIC;
   433 REVOKE ALL ON SCHEMA nekurak FROM nekurak;
   434 GRANT ALL ON SCHEMA nekurak TO nekurak;
   435 GRANT USAGE ON SCHEMA nekurak TO nekurak_web;
   436 
   437 
   438 --
   439 -- TOC entry 1896 (class 0 OID 0)
   440 -- Dependencies: 1592
   441 -- Name: fotka_seq; Type: ACL; Schema: nekurak; Owner: nekurak
   442 --
   443 
   444 REVOKE ALL ON SEQUENCE fotka_seq FROM PUBLIC;
   445 REVOKE ALL ON SEQUENCE fotka_seq FROM nekurak;
   446 GRANT ALL ON SEQUENCE fotka_seq TO nekurak;
   447 GRANT USAGE ON SEQUENCE fotka_seq TO nekurak_web;
   448 
   449 
   450 --
   451 -- TOC entry 1899 (class 0 OID 0)
   452 -- Dependencies: 1593
   453 -- Name: fotka; Type: ACL; Schema: nekurak; Owner: nekurak
   454 --
   455 
   456 REVOKE ALL ON TABLE fotka FROM PUBLIC;
   457 REVOKE ALL ON TABLE fotka FROM nekurak;
   458 GRANT ALL ON TABLE fotka TO nekurak;
   459 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE fotka TO nekurak_web;
   460 
   461 
   462 --
   463 -- TOC entry 1901 (class 0 OID 0)
   464 -- Dependencies: 1587
   465 -- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak
   466 --
   467 
   468 REVOKE ALL ON TABLE hlasovani FROM PUBLIC;
   469 REVOKE ALL ON TABLE hlasovani FROM nekurak;
   470 GRANT ALL ON TABLE hlasovani TO nekurak;
   471 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE hlasovani TO nekurak_web;
   472 
   473 
   474 --
   475 -- TOC entry 1902 (class 0 OID 0)
   476 -- Dependencies: 1585
   477 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
   478 --
   479 
   480 REVOKE ALL ON SEQUENCE podnik_seq FROM PUBLIC;
   481 REVOKE ALL ON SEQUENCE podnik_seq FROM nekurak;
   482 GRANT ALL ON SEQUENCE podnik_seq TO nekurak;
   483 GRANT USAGE ON SEQUENCE podnik_seq TO nekurak_web;
   484 
   485 
   486 --
   487 -- TOC entry 1904 (class 0 OID 0)
   488 -- Dependencies: 1586
   489 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
   490 --
   491 
   492 REVOKE ALL ON TABLE podnik FROM PUBLIC;
   493 REVOKE ALL ON TABLE podnik FROM nekurak;
   494 GRANT ALL ON TABLE podnik TO nekurak;
   495 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE podnik TO nekurak_web;
   496 
   497 
   498 --
   499 -- TOC entry 1906 (class 0 OID 0)
   500 -- Dependencies: 1590
   501 -- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak
   502 --
   503 
   504 REVOKE ALL ON TABLE role FROM PUBLIC;
   505 REVOKE ALL ON TABLE role FROM nekurak;
   506 GRANT ALL ON TABLE role TO nekurak;
   507 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE role TO nekurak_web;
   508 
   509 
   510 --
   511 -- TOC entry 1907 (class 0 OID 0)
   512 -- Dependencies: 1588
   513 -- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak
   514 --
   515 
   516 REVOKE ALL ON SEQUENCE uzivatel_seq FROM PUBLIC;
   517 REVOKE ALL ON SEQUENCE uzivatel_seq FROM nekurak;
   518 GRANT ALL ON SEQUENCE uzivatel_seq TO nekurak;
   519 GRANT USAGE ON SEQUENCE uzivatel_seq TO nekurak_web;
   520 
   521 
   522 --
   523 -- TOC entry 1909 (class 0 OID 0)
   524 -- Dependencies: 1589
   525 -- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak
   526 --
   527 
   528 REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
   529 REVOKE ALL ON TABLE uzivatel FROM nekurak;
   530 GRANT ALL ON TABLE uzivatel TO nekurak;
   531 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel TO nekurak_web;
   532 
   533 
   534 --
   535 -- TOC entry 1911 (class 0 OID 0)
   536 -- Dependencies: 1591
   537 -- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak
   538 --
   539 
   540 REVOKE ALL ON TABLE uzivatel_role FROM PUBLIC;
   541 REVOKE ALL ON TABLE uzivatel_role FROM nekurak;
   542 GRANT ALL ON TABLE uzivatel_role TO nekurak;
   543 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web;
   544 
   545 
   546 -- Completed on 2010-03-16 14:32:24 CET
   547 
   548 --
   549 -- PostgreSQL database dump complete
   550 --
   551