sql/schéma.sql
author František Kučera <franta-hg@frantovo.cz>
Sun, 14 Mar 2010 01:37:40 +0100
changeset 68 df5aedafb826
parent 62 d9df3f7b67dd
child 71 3829d619369c
permissions -rw-r--r--
Fotky se zobrazují ty, které jsou v databázi
(jen záznamy – soubory s fotkami jsou na disku).
     1 --
     2 -- PostgreSQL database dump
     3 --
     4 
     5 -- Started on 2010-03-09 20:48:44 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 369 (class 1247 OID 35086)
    28 -- Dependencies: 370 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 1881 (class 0 OID 0)
    40 -- Dependencies: 369
    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 392
    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 1882 (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 SET default_tablespace = '';
    76 
    77 SET default_with_oids = true;
    78 
    79 --
    80 -- TOC entry 1584 (class 1259 OID 35068)
    81 -- Dependencies: 1861 10
    82 -- Name: hlasovani; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
    83 --
    84 
    85 CREATE TABLE hlasovani (
    86     podnik integer NOT NULL,
    87     hlas boolean NOT NULL,
    88     datum date DEFAULT now() NOT NULL,
    89     ip_adresa character varying(255)
    90 );
    91 
    92 
    93 ALTER TABLE nekurak.hlasovani OWNER TO nekurak;
    94 
    95 --
    96 -- TOC entry 1883 (class 0 OID 0)
    97 -- Dependencies: 1584
    98 -- Name: COLUMN hlasovani.hlas; Type: COMMENT; Schema: nekurak; Owner: nekurak
    99 --
   100 
   101 COMMENT ON COLUMN hlasovani.hlas IS 'Má být tento podnik nekuřácký?
   102 true = nekuřácký
   103 false = kouření povoleno';
   104 
   105 
   106 --
   107 -- TOC entry 1582 (class 1259 OID 35019)
   108 -- Dependencies: 10
   109 -- Name: podnik_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
   110 --
   111 
   112 CREATE SEQUENCE podnik_seq
   113     INCREMENT BY 1
   114     NO MAXVALUE
   115     NO MINVALUE
   116     CACHE 1;
   117 
   118 
   119 ALTER TABLE nekurak.podnik_seq OWNER TO nekurak;
   120 
   121 SET default_with_oids = false;
   122 
   123 --
   124 -- TOC entry 1583 (class 1259 OID 35049)
   125 -- Dependencies: 1859 1860 10
   126 -- Name: podnik; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   127 --
   128 
   129 CREATE TABLE podnik (
   130     id integer DEFAULT nextval('podnik_seq'::regclass) NOT NULL,
   131     nazev character varying(255) NOT NULL,
   132     popis character varying(255),
   133     url character varying(255),
   134     datum timestamp with time zone DEFAULT now(),
   135     ulice character varying(64),
   136     cislo_popisne integer,
   137     mesto character varying(64)
   138 );
   139 
   140 
   141 ALTER TABLE nekurak.podnik OWNER TO nekurak;
   142 
   143 --
   144 -- TOC entry 1587 (class 1259 OID 35101)
   145 -- Dependencies: 10
   146 -- Name: role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   147 --
   148 
   149 CREATE TABLE role (
   150     kod character varying(16) NOT NULL,
   151     popis character varying(255)
   152 );
   153 
   154 
   155 ALTER TABLE nekurak.role OWNER TO nekurak;
   156 
   157 --
   158 -- TOC entry 1887 (class 0 OID 0)
   159 -- Dependencies: 1587
   160 -- Name: TABLE role; Type: COMMENT; Schema: nekurak; Owner: nekurak
   161 --
   162 
   163 COMMENT ON TABLE role IS 'Role (skupiny) uživatelů ~ oprávnění';
   164 
   165 
   166 --
   167 -- TOC entry 1585 (class 1259 OID 35080)
   168 -- Dependencies: 10
   169 -- Name: uzivatel_seq; Type: SEQUENCE; Schema: nekurak; Owner: nekurak
   170 --
   171 
   172 CREATE SEQUENCE uzivatel_seq
   173     INCREMENT BY 1
   174     NO MAXVALUE
   175     NO MINVALUE
   176     CACHE 1;
   177 
   178 
   179 ALTER TABLE nekurak.uzivatel_seq OWNER TO nekurak;
   180 
   181 --
   182 -- TOC entry 1586 (class 1259 OID 35091)
   183 -- Dependencies: 1862 1863 10
   184 -- Name: uzivatel; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   185 --
   186 
   187 CREATE TABLE uzivatel (
   188     id integer DEFAULT nextval('uzivatel_seq'::regclass) NOT NULL,
   189     prezdivka character varying(64) NOT NULL,
   190     heslo character varying(512) NOT NULL,
   191     jmeno character varying(64),
   192     prijmeni character varying(64),
   193     email character varying(255),
   194     datum timestamp with time zone DEFAULT now() NOT NULL
   195 );
   196 
   197 
   198 ALTER TABLE nekurak.uzivatel OWNER TO nekurak;
   199 
   200 --
   201 -- TOC entry 1890 (class 0 OID 0)
   202 -- Dependencies: 1586
   203 -- Name: COLUMN uzivatel.prezdivka; Type: COMMENT; Schema: nekurak; Owner: nekurak
   204 --
   205 
   206 COMMENT ON COLUMN uzivatel.prezdivka IS 'Uživatelské jméno';
   207 
   208 
   209 --
   210 -- TOC entry 1588 (class 1259 OID 35106)
   211 -- Dependencies: 10
   212 -- Name: uzivatel_role; Type: TABLE; Schema: nekurak; Owner: nekurak; Tablespace: 
   213 --
   214 
   215 CREATE TABLE uzivatel_role (
   216     role character varying(16) NOT NULL,
   217     prezdivka character varying(64) NOT NULL
   218 );
   219 
   220 
   221 ALTER TABLE nekurak.uzivatel_role OWNER TO nekurak;
   222 
   223 --
   224 -- TOC entry 1892 (class 0 OID 0)
   225 -- Dependencies: 1588
   226 -- Name: TABLE uzivatel_role; Type: COMMENT; Schema: nekurak; Owner: nekurak
   227 --
   228 
   229 COMMENT ON TABLE uzivatel_role IS 'Přiřazení uživatelských rolí (skupin).';
   230 
   231 
   232 --
   233 -- TOC entry 1865 (class 2606 OID 35058)
   234 -- Dependencies: 1583 1583
   235 -- Name: podnik_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   236 --
   237 
   238 ALTER TABLE ONLY podnik
   239     ADD CONSTRAINT podnik_pk PRIMARY KEY (id);
   240 
   241 
   242 --
   243 -- TOC entry 1871 (class 2606 OID 35105)
   244 -- Dependencies: 1587 1587
   245 -- Name: skupina_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   246 --
   247 
   248 ALTER TABLE ONLY role
   249     ADD CONSTRAINT skupina_pk PRIMARY KEY (kod);
   250 
   251 
   252 --
   253 -- TOC entry 1867 (class 2606 OID 35098)
   254 -- Dependencies: 1586 1586
   255 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   256 --
   257 
   258 ALTER TABLE ONLY uzivatel
   259     ADD CONSTRAINT uzivatel_pk PRIMARY KEY (id);
   260 
   261 
   262 --
   263 -- TOC entry 1869 (class 2606 OID 35100)
   264 -- Dependencies: 1586 1586
   265 -- Name: uzivatel_prezdivka_uq; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   266 --
   267 
   268 ALTER TABLE ONLY uzivatel
   269     ADD CONSTRAINT uzivatel_prezdivka_uq UNIQUE (prezdivka);
   270 
   271 
   272 --
   273 -- TOC entry 1873 (class 2606 OID 35137)
   274 -- Dependencies: 1588 1588 1588
   275 -- Name: uzivatel_role_pk; Type: CONSTRAINT; Schema: nekurak; Owner: nekurak; Tablespace: 
   276 --
   277 
   278 ALTER TABLE ONLY uzivatel_role
   279     ADD CONSTRAINT uzivatel_role_pk PRIMARY KEY (role, prezdivka);
   280 
   281 
   282 --
   283 -- TOC entry 1877 (class 2620 OID 35125)
   284 -- Dependencies: 65 1586
   285 -- Name: uzivatel_insert; Type: TRIGGER; Schema: nekurak; Owner: nekurak
   286 --
   287 
   288 CREATE TRIGGER uzivatel_insert
   289     AFTER INSERT ON uzivatel
   290     FOR EACH ROW
   291     EXECUTE PROCEDURE bezny_uzivatel();
   292 
   293 
   294 --
   295 -- TOC entry 1874 (class 2606 OID 35072)
   296 -- Dependencies: 1864 1583 1584
   297 -- Name: hlasovani_podnik_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   298 --
   299 
   300 ALTER TABLE ONLY hlasovani
   301     ADD CONSTRAINT hlasovani_podnik_fk FOREIGN KEY (podnik) REFERENCES podnik(id) ON DELETE CASCADE;
   302 
   303 
   304 --
   305 -- TOC entry 1875 (class 2606 OID 35138)
   306 -- Dependencies: 1870 1587 1588
   307 -- Name: uzivatel_role_role_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   308 --
   309 
   310 ALTER TABLE ONLY uzivatel_role
   311     ADD CONSTRAINT uzivatel_role_role_fk FOREIGN KEY (role) REFERENCES role(kod) ON UPDATE CASCADE ON DELETE CASCADE;
   312 
   313 
   314 --
   315 -- TOC entry 1876 (class 2606 OID 35143)
   316 -- Dependencies: 1868 1586 1588
   317 -- Name: uzivatel_role_uzivatel_fk; Type: FK CONSTRAINT; Schema: nekurak; Owner: nekurak
   318 --
   319 
   320 ALTER TABLE ONLY uzivatel_role
   321     ADD CONSTRAINT uzivatel_role_uzivatel_fk FOREIGN KEY (prezdivka) REFERENCES uzivatel(prezdivka) ON UPDATE CASCADE ON DELETE CASCADE;
   322 
   323 
   324 --
   325 -- TOC entry 1880 (class 0 OID 0)
   326 -- Dependencies: 10
   327 -- Name: nekurak; Type: ACL; Schema: -; Owner: nekurak
   328 --
   329 
   330 REVOKE ALL ON SCHEMA nekurak FROM PUBLIC;
   331 REVOKE ALL ON SCHEMA nekurak FROM nekurak;
   332 GRANT ALL ON SCHEMA nekurak TO nekurak;
   333 GRANT USAGE ON SCHEMA nekurak TO nekurak_web;
   334 
   335 
   336 --
   337 -- TOC entry 1884 (class 0 OID 0)
   338 -- Dependencies: 1584
   339 -- Name: hlasovani; Type: ACL; Schema: nekurak; Owner: nekurak
   340 --
   341 
   342 REVOKE ALL ON TABLE hlasovani FROM PUBLIC;
   343 REVOKE ALL ON TABLE hlasovani FROM nekurak;
   344 GRANT ALL ON TABLE hlasovani TO nekurak;
   345 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE hlasovani TO nekurak_web;
   346 
   347 
   348 --
   349 -- TOC entry 1885 (class 0 OID 0)
   350 -- Dependencies: 1582
   351 -- Name: podnik_seq; Type: ACL; Schema: nekurak; Owner: nekurak
   352 --
   353 
   354 REVOKE ALL ON SEQUENCE podnik_seq FROM PUBLIC;
   355 REVOKE ALL ON SEQUENCE podnik_seq FROM nekurak;
   356 GRANT ALL ON SEQUENCE podnik_seq TO nekurak;
   357 GRANT USAGE ON SEQUENCE podnik_seq TO nekurak_web;
   358 
   359 
   360 --
   361 -- TOC entry 1886 (class 0 OID 0)
   362 -- Dependencies: 1583
   363 -- Name: podnik; Type: ACL; Schema: nekurak; Owner: nekurak
   364 --
   365 
   366 REVOKE ALL ON TABLE podnik FROM PUBLIC;
   367 REVOKE ALL ON TABLE podnik FROM nekurak;
   368 GRANT ALL ON TABLE podnik TO nekurak;
   369 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE podnik TO nekurak_web;
   370 
   371 
   372 --
   373 -- TOC entry 1888 (class 0 OID 0)
   374 -- Dependencies: 1587
   375 -- Name: role; Type: ACL; Schema: nekurak; Owner: nekurak
   376 --
   377 
   378 REVOKE ALL ON TABLE role FROM PUBLIC;
   379 REVOKE ALL ON TABLE role FROM nekurak;
   380 GRANT ALL ON TABLE role TO nekurak;
   381 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE role TO nekurak_web;
   382 
   383 
   384 --
   385 -- TOC entry 1889 (class 0 OID 0)
   386 -- Dependencies: 1585
   387 -- Name: uzivatel_seq; Type: ACL; Schema: nekurak; Owner: nekurak
   388 --
   389 
   390 REVOKE ALL ON SEQUENCE uzivatel_seq FROM PUBLIC;
   391 REVOKE ALL ON SEQUENCE uzivatel_seq FROM nekurak;
   392 GRANT ALL ON SEQUENCE uzivatel_seq TO nekurak;
   393 GRANT USAGE ON SEQUENCE uzivatel_seq TO nekurak_web;
   394 
   395 
   396 --
   397 -- TOC entry 1891 (class 0 OID 0)
   398 -- Dependencies: 1586
   399 -- Name: uzivatel; Type: ACL; Schema: nekurak; Owner: nekurak
   400 --
   401 
   402 REVOKE ALL ON TABLE uzivatel FROM PUBLIC;
   403 REVOKE ALL ON TABLE uzivatel FROM nekurak;
   404 GRANT ALL ON TABLE uzivatel TO nekurak;
   405 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel TO nekurak_web;
   406 
   407 
   408 --
   409 -- TOC entry 1893 (class 0 OID 0)
   410 -- Dependencies: 1588
   411 -- Name: uzivatel_role; Type: ACL; Schema: nekurak; Owner: nekurak
   412 --
   413 
   414 REVOKE ALL ON TABLE uzivatel_role FROM PUBLIC;
   415 REVOKE ALL ON TABLE uzivatel_role FROM nekurak;
   416 GRANT ALL ON TABLE uzivatel_role TO nekurak;
   417 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE uzivatel_role TO nekurak_web;
   418 
   419 
   420 -- Completed on 2010-03-09 20:48:46 CET
   421 
   422 --
   423 -- PostgreSQL database dump complete
   424 --
   425