sql/schéma.sql
author František Kučera <franta-hg@frantovo.cz>
Tue Apr 28 22:26:46 2009 +0200 (2009-04-28)
changeset 1 616aeab8d3c0
permissions -rw-r--r--
Analýza: přidání konceptu
     1 --
     2 -- PostgreSQL database dump
     3 --
     4 
     5 -- Started on 2009-04-28 21:06:29 CEST
     6 
     7 SET client_encoding = 'UTF8';
     8 SET standard_conforming_strings = off;
     9 SET check_function_bodies = false;
    10 SET client_min_messages = warning;
    11 SET escape_string_warning = off;
    12 
    13 --
    14 -- TOC entry 6 (class 2615 OID 16386)
    15 -- Name: miniobchod; Type: SCHEMA; Schema: -; Owner: miniobchod
    16 --
    17 
    18 CREATE SCHEMA miniobchod;
    19 
    20 
    21 ALTER SCHEMA miniobchod OWNER TO miniobchod;
    22 
    23 SET search_path = miniobchod, pg_catalog;
    24 
    25 --
    26 -- TOC entry 332 (class 1247 OID 16398)
    27 -- Dependencies: 333 6
    28 -- Name: email; Type: DOMAIN; Schema: miniobchod; Owner: miniobchod
    29 --
    30 
    31 CREATE DOMAIN email AS character varying(255) NOT NULL
    32 	CONSTRAINT email_regexp CHECK (((VALUE)::text ~ '^[_a-zA-Z0-9\\.\\-]+@[_a-zA-Z0-9\\.\\-]+\\.[a-zA-Z]{2,4}$'::text));
    33 
    34 
    35 ALTER DOMAIN miniobchod.email OWNER TO miniobchod;
    36 
    37 --
    38 -- TOC entry 1821 (class 0 OID 0)
    39 -- Dependencies: 332
    40 -- Name: DOMAIN email; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
    41 --
    42 
    43 COMMENT ON DOMAIN email IS 'e-mailová adresa';
    44 
    45 
    46 --
    47 -- TOC entry 344 (class 1247 OID 16566)
    48 -- Dependencies: 345 6
    49 -- Name: psc; Type: DOMAIN; Schema: miniobchod; Owner: miniobchod
    50 --
    51 
    52 CREATE DOMAIN psc AS character varying(255) NOT NULL
    53 	CONSTRAINT psc_regexp CHECK (((VALUE)::text ~ '^\\d{3} ?\\d{2}$'::text));
    54 
    55 
    56 ALTER DOMAIN miniobchod.psc OWNER TO miniobchod;
    57 
    58 --
    59 -- TOC entry 1822 (class 0 OID 0)
    60 -- Dependencies: 344
    61 -- Name: DOMAIN psc; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
    62 --
    63 
    64 COMMENT ON DOMAIN psc IS 'Poštovní směrovací číslo';
    65 
    66 
    67 --
    68 -- TOC entry 55 (class 1255 OID 16459)
    69 -- Dependencies: 6
    70 -- Name: nahodne_heslo(); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
    71 --
    72 
    73 CREATE FUNCTION nahodne_heslo() RETURNS character
    74     AS $$select sha512(random()::text || random()::text || random()::text);$$
    75     LANGUAGE sql;
    76 
    77 
    78 ALTER FUNCTION miniobchod.nahodne_heslo() OWNER TO miniobchod;
    79 
    80 --
    81 -- TOC entry 56 (class 1255 OID 16460)
    82 -- Dependencies: 6
    83 -- Name: sha1(text); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
    84 --
    85 
    86 CREATE FUNCTION sha1(text) RETURNS text
    87     AS $_$
    88 SELECT encode(digest(decode($1,'escape'::text),'sha1'::text),'hex');
    89 $_$
    90     LANGUAGE sql IMMUTABLE STRICT;
    91 
    92 
    93 ALTER FUNCTION miniobchod.sha1(text) OWNER TO miniobchod;
    94 
    95 --
    96 -- TOC entry 57 (class 1255 OID 16461)
    97 -- Dependencies: 6
    98 -- Name: sha256(text); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
    99 --
   100 
   101 CREATE FUNCTION sha256(text) RETURNS text
   102     AS $_$
   103 SELECT encode(digest(decode($1,'escape'::text),'sha256'::text),'hex');
   104 $_$
   105     LANGUAGE sql IMMUTABLE STRICT;
   106 
   107 
   108 ALTER FUNCTION miniobchod.sha256(text) OWNER TO miniobchod;
   109 
   110 --
   111 -- TOC entry 54 (class 1255 OID 16458)
   112 -- Dependencies: 6
   113 -- Name: sha512(text); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
   114 --
   115 
   116 CREATE FUNCTION sha512(text) RETURNS text
   117     AS $_$
   118 SELECT encode(digest(decode($1,'escape'::text),'sha512'::text),'hex');
   119 $_$
   120     LANGUAGE sql IMMUTABLE STRICT;
   121 
   122 
   123 ALTER FUNCTION miniobchod.sha512(text) OWNER TO miniobchod;
   124 
   125 --
   126 -- TOC entry 58 (class 1255 OID 16608)
   127 -- Dependencies: 350 6
   128 -- Name: vyjimka(character varying); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
   129 --
   130 
   131 CREATE FUNCTION vyjimka(chyba character varying) RETURNS void
   132     AS $$BEGIN
   133 RAISE EXCEPTION '%', chyba;
   134 END;$$
   135     LANGUAGE plpgsql;
   136 
   137 
   138 ALTER FUNCTION miniobchod.vyjimka(chyba character varying) OWNER TO miniobchod;
   139 
   140 --
   141 -- TOC entry 1823 (class 0 OID 0)
   142 -- Dependencies: 58
   143 -- Name: FUNCTION vyjimka(chyba character varying); Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   144 --
   145 
   146 COMMENT ON FUNCTION vyjimka(chyba character varying) IS 'Vyhodí výjimku';
   147 
   148 
   149 --
   150 -- TOC entry 1528 (class 1259 OID 16575)
   151 -- Dependencies: 6
   152 -- Name: adresa_seq; Type: SEQUENCE; Schema: miniobchod; Owner: miniobchod
   153 --
   154 
   155 CREATE SEQUENCE adresa_seq
   156     INCREMENT BY 1
   157     NO MAXVALUE
   158     NO MINVALUE
   159     CACHE 1;
   160 
   161 
   162 ALTER TABLE miniobchod.adresa_seq OWNER TO miniobchod;
   163 
   164 SET default_tablespace = '';
   165 
   166 SET default_with_oids = false;
   167 
   168 --
   169 -- TOC entry 1529 (class 1259 OID 16578)
   170 -- Dependencies: 1804 332 344 6
   171 -- Name: adresa; Type: TABLE; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   172 --
   173 
   174 CREATE TABLE adresa (
   175     id bigint DEFAULT nextval('adresa_seq'::regclass) NOT NULL,
   176     uzivatel email,
   177     jmeno character varying(255),
   178     ulice character varying(255),
   179     cp smallint,
   180     mesto character varying(255),
   181     psc psc
   182 );
   183 
   184 
   185 ALTER TABLE miniobchod.adresa OWNER TO miniobchod;
   186 
   187 --
   188 -- TOC entry 1824 (class 0 OID 0)
   189 -- Dependencies: 1529
   190 -- Name: TABLE adresa; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   191 --
   192 
   193 COMMENT ON TABLE adresa IS 'Poštovní adresa';
   194 
   195 
   196 --
   197 -- TOC entry 1825 (class 0 OID 0)
   198 -- Dependencies: 1529
   199 -- Name: COLUMN adresa.jmeno; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   200 --
   201 
   202 COMMENT ON COLUMN adresa.jmeno IS 'Jméno příjemce';
   203 
   204 
   205 --
   206 -- TOC entry 1826 (class 0 OID 0)
   207 -- Dependencies: 1529
   208 -- Name: COLUMN adresa.cp; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   209 --
   210 
   211 COMMENT ON COLUMN adresa.cp IS 'Číslo popisné';
   212 
   213 
   214 --
   215 -- TOC entry 1827 (class 0 OID 0)
   216 -- Dependencies: 1529
   217 -- Name: COLUMN adresa.psc; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   218 --
   219 
   220 COMMENT ON COLUMN adresa.psc IS 'Poštovní směrovací číslo';
   221 
   222 
   223 --
   224 -- TOC entry 1527 (class 1259 OID 16540)
   225 -- Dependencies: 1803 6
   226 -- Name: objednane_zbozi; Type: TABLE; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   227 --
   228 
   229 CREATE TABLE objednane_zbozi (
   230     objednavka bigint NOT NULL,
   231     zbozi bit varying NOT NULL,
   232     mnozstvi smallint DEFAULT 1 NOT NULL
   233 );
   234 
   235 
   236 ALTER TABLE miniobchod.objednane_zbozi OWNER TO miniobchod;
   237 
   238 --
   239 -- TOC entry 1828 (class 0 OID 0)
   240 -- Dependencies: 1527
   241 -- Name: TABLE objednane_zbozi; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   242 --
   243 
   244 COMMENT ON TABLE objednane_zbozi IS 'Vazební tabulka, která definuje obsah objednávky – objednané zboží';
   245 
   246 
   247 --
   248 -- TOC entry 1525 (class 1259 OID 16408)
   249 -- Dependencies: 6
   250 -- Name: objednavka_seq; Type: SEQUENCE; Schema: miniobchod; Owner: miniobchod
   251 --
   252 
   253 CREATE SEQUENCE objednavka_seq
   254     INCREMENT BY 1
   255     NO MAXVALUE
   256     NO MINVALUE
   257     CACHE 1;
   258 
   259 
   260 ALTER TABLE miniobchod.objednavka_seq OWNER TO miniobchod;
   261 
   262 --
   263 -- TOC entry 1526 (class 1259 OID 16410)
   264 -- Dependencies: 1800 1801 1802 6 332
   265 -- Name: objednavka; Type: TABLE; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   266 --
   267 
   268 CREATE TABLE objednavka (
   269     id bigint DEFAULT nextval('objednavka_seq'::regclass) NOT NULL,
   270     kod character varying DEFAULT nahodne_heslo() NOT NULL,
   271     uzivatel email,
   272     podano timestamp with time zone DEFAULT now() NOT NULL,
   273     adresa bigint
   274 );
   275 
   276 
   277 ALTER TABLE miniobchod.objednavka OWNER TO miniobchod;
   278 
   279 --
   280 -- TOC entry 1829 (class 0 OID 0)
   281 -- Dependencies: 1526
   282 -- Name: TABLE objednavka; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   283 --
   284 
   285 COMMENT ON TABLE objednavka IS 'Objednávka zboží';
   286 
   287 
   288 --
   289 -- TOC entry 1830 (class 0 OID 0)
   290 -- Dependencies: 1526
   291 -- Name: COLUMN objednavka.id; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   292 --
   293 
   294 COMMENT ON COLUMN objednavka.id IS 'Číslo objednávky je zároveň variabilním symbolem';
   295 
   296 
   297 --
   298 -- TOC entry 1831 (class 0 OID 0)
   299 -- Dependencies: 1526
   300 -- Name: COLUMN objednavka.kod; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   301 --
   302 
   303 COMMENT ON COLUMN objednavka.kod IS 'Potvrzovací kód – pošle se zákazníkovi e-mailem a ten pomocí něj objednávku potvrdí.';
   304 
   305 
   306 --
   307 -- TOC entry 1832 (class 0 OID 0)
   308 -- Dependencies: 1526
   309 -- Name: COLUMN objednavka.uzivatel; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   310 --
   311 
   312 COMMENT ON COLUMN objednavka.uzivatel IS 'Kdo si objednal';
   313 
   314 
   315 --
   316 -- TOC entry 1833 (class 0 OID 0)
   317 -- Dependencies: 1526
   318 -- Name: COLUMN objednavka.podano; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   319 --
   320 
   321 COMMENT ON COLUMN objednavka.podano IS 'Datum a čas podání objednávky';
   322 
   323 
   324 --
   325 -- TOC entry 1834 (class 0 OID 0)
   326 -- Dependencies: 1526
   327 -- Name: COLUMN objednavka.adresa; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   328 --
   329 
   330 COMMENT ON COLUMN objednavka.adresa IS 'Kam se má objednávka doručit';
   331 
   332 
   333 --
   334 -- TOC entry 1524 (class 1259 OID 16400)
   335 -- Dependencies: 1798 1799 6 332
   336 -- Name: uzivatel; Type: TABLE; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   337 --
   338 
   339 CREATE TABLE uzivatel (
   340     email email NOT NULL,
   341     heslo character varying DEFAULT nahodne_heslo() NOT NULL,
   342     zalozen timestamp with time zone DEFAULT now()
   343 );
   344 
   345 
   346 ALTER TABLE miniobchod.uzivatel OWNER TO miniobchod;
   347 
   348 --
   349 -- TOC entry 1835 (class 0 OID 0)
   350 -- Dependencies: 1524
   351 -- Name: TABLE uzivatel; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   352 --
   353 
   354 COMMENT ON TABLE uzivatel IS 'Zákazník';
   355 
   356 
   357 --
   358 -- TOC entry 1836 (class 0 OID 0)
   359 -- Dependencies: 1524
   360 -- Name: COLUMN uzivatel.email; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   361 --
   362 
   363 COMMENT ON COLUMN uzivatel.email IS 'Uživatel je identifikovaný pomocí své e-mailové adresy';
   364 
   365 
   366 --
   367 -- TOC entry 1837 (class 0 OID 0)
   368 -- Dependencies: 1524
   369 -- Name: COLUMN uzivatel.heslo; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   370 --
   371 
   372 COMMENT ON COLUMN uzivatel.heslo IS 'Vygenerované heslo, kterým se uživatel prokazuje (pošle se mu na e-mail)';
   373 
   374 
   375 --
   376 -- TOC entry 1838 (class 0 OID 0)
   377 -- Dependencies: 1524
   378 -- Name: COLUMN uzivatel.zalozen; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   379 --
   380 
   381 COMMENT ON COLUMN uzivatel.zalozen IS 'Datum a čas registrace zákazníka';
   382 
   383 
   384 --
   385 -- TOC entry 1522 (class 1259 OID 16387)
   386 -- Dependencies: 6
   387 -- Name: zbozi_seq; Type: SEQUENCE; Schema: miniobchod; Owner: miniobchod
   388 --
   389 
   390 CREATE SEQUENCE zbozi_seq
   391     INCREMENT BY 1
   392     NO MAXVALUE
   393     NO MINVALUE
   394     CACHE 1;
   395 
   396 
   397 ALTER TABLE miniobchod.zbozi_seq OWNER TO miniobchod;
   398 
   399 --
   400 -- TOC entry 1523 (class 1259 OID 16389)
   401 -- Dependencies: 1797 6
   402 -- Name: zbozi; Type: TABLE; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   403 --
   404 
   405 CREATE TABLE zbozi (
   406     id bigint DEFAULT nextval('zbozi_seq'::regclass) NOT NULL,
   407     nazev character varying(255),
   408     popis text
   409 );
   410 
   411 
   412 ALTER TABLE miniobchod.zbozi OWNER TO miniobchod;
   413 
   414 --
   415 -- TOC entry 1839 (class 0 OID 0)
   416 -- Dependencies: 1523
   417 -- Name: TABLE zbozi; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   418 --
   419 
   420 COMMENT ON TABLE zbozi IS 'Položka katalogu';
   421 
   422 
   423 --
   424 -- TOC entry 1840 (class 0 OID 0)
   425 -- Dependencies: 1523
   426 -- Name: COLUMN zbozi.id; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   427 --
   428 
   429 COMMENT ON COLUMN zbozi.id IS 'Číslo zboží';
   430 
   431 
   432 --
   433 -- TOC entry 1841 (class 0 OID 0)
   434 -- Dependencies: 1523
   435 -- Name: COLUMN zbozi.nazev; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   436 --
   437 
   438 COMMENT ON COLUMN zbozi.nazev IS 'Název zboží';
   439 
   440 
   441 --
   442 -- TOC entry 1842 (class 0 OID 0)
   443 -- Dependencies: 1523
   444 -- Name: COLUMN zbozi.popis; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   445 --
   446 
   447 COMMENT ON COLUMN zbozi.popis IS 'Popis zboží';
   448 
   449 
   450 --
   451 -- TOC entry 1815 (class 2606 OID 16586)
   452 -- Dependencies: 1529 1529
   453 -- Name: adresa_pk; Type: CONSTRAINT; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   454 --
   455 
   456 ALTER TABLE ONLY adresa
   457     ADD CONSTRAINT adresa_pk PRIMARY KEY (id);
   458 
   459 
   460 --
   461 -- TOC entry 1813 (class 2606 OID 16548)
   462 -- Dependencies: 1527 1527 1527
   463 -- Name: objednane_zbozi_pk; Type: CONSTRAINT; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   464 --
   465 
   466 ALTER TABLE ONLY objednane_zbozi
   467     ADD CONSTRAINT objednane_zbozi_pk PRIMARY KEY (objednavka, zbozi);
   468 
   469 
   470 --
   471 -- TOC entry 1811 (class 2606 OID 16415)
   472 -- Dependencies: 1526 1526
   473 -- Name: objednavka_pk; Type: CONSTRAINT; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   474 --
   475 
   476 ALTER TABLE ONLY objednavka
   477     ADD CONSTRAINT objednavka_pk PRIMARY KEY (id);
   478 
   479 
   480 --
   481 -- TOC entry 1808 (class 2606 OID 16407)
   482 -- Dependencies: 1524 1524
   483 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   484 --
   485 
   486 ALTER TABLE ONLY uzivatel
   487     ADD CONSTRAINT uzivatel_pk PRIMARY KEY (email);
   488 
   489 
   490 --
   491 -- TOC entry 1806 (class 2606 OID 16397)
   492 -- Dependencies: 1523 1523
   493 -- Name: zbozi_pk; Type: CONSTRAINT; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   494 --
   495 
   496 ALTER TABLE ONLY zbozi
   497     ADD CONSTRAINT zbozi_pk PRIMARY KEY (id);
   498 
   499 
   500 --
   501 -- TOC entry 1809 (class 1259 OID 16597)
   502 -- Dependencies: 1526
   503 -- Name: fki_objednavka_adresa_fk; Type: INDEX; Schema: miniobchod; Owner: miniobchod; Tablespace: 
   504 --
   505 
   506 CREATE INDEX fki_objednavka_adresa_fk ON objednavka USING btree (adresa);
   507 
   508 
   509 --
   510 -- TOC entry 1605 (class 2618 OID 16598)
   511 -- Dependencies: 1526 1529 1526 58 1526 1526 1526 1529
   512 -- Name: objednavka_adresa_uzivatel; Type: RULE; Schema: miniobchod; Owner: miniobchod
   513 --
   514 
   515 CREATE RULE objednavka_adresa_uzivatel AS ON INSERT TO objednavka WHERE ((new.uzivatel)::text <> ((SELECT adresa.uzivatel FROM adresa WHERE (adresa.id = new.adresa)))::text) DO SELECT vyjimka('Uživatel může objednat zboží jen na svoji adresu'::character varying) AS vyjimka;
   516 
   517 
   518 --
   519 -- TOC entry 1843 (class 0 OID 0)
   520 -- Dependencies: 1605
   521 -- Name: RULE objednavka_adresa_uzivatel ON objednavka; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
   522 --
   523 
   524 COMMENT ON RULE objednavka_adresa_uzivatel ON objednavka IS 'Uživatel může objednat zboží jen na svoji adresu';
   525 
   526 
   527 --
   528 -- TOC entry 1818 (class 2606 OID 16587)
   529 -- Dependencies: 1807 1529 1524
   530 -- Name: adresa_uzivatel_fk; Type: FK CONSTRAINT; Schema: miniobchod; Owner: miniobchod
   531 --
   532 
   533 ALTER TABLE ONLY adresa
   534     ADD CONSTRAINT adresa_uzivatel_fk FOREIGN KEY (uzivatel) REFERENCES uzivatel(email);
   535 
   536 
   537 --
   538 -- TOC entry 1817 (class 2606 OID 16592)
   539 -- Dependencies: 1814 1529 1526
   540 -- Name: objednavka_adresa_fk; Type: FK CONSTRAINT; Schema: miniobchod; Owner: miniobchod
   541 --
   542 
   543 ALTER TABLE ONLY objednavka
   544     ADD CONSTRAINT objednavka_adresa_fk FOREIGN KEY (adresa) REFERENCES adresa(id);
   545 
   546 
   547 --
   548 -- TOC entry 1816 (class 2606 OID 16493)
   549 -- Dependencies: 1807 1524 1526
   550 -- Name: objednavka_uzivatel_fk; Type: FK CONSTRAINT; Schema: miniobchod; Owner: miniobchod
   551 --
   552 
   553 ALTER TABLE ONLY objednavka
   554     ADD CONSTRAINT objednavka_uzivatel_fk FOREIGN KEY (uzivatel) REFERENCES uzivatel(email);
   555 
   556 
   557 -- Completed on 2009-04-28 21:06:38 CEST
   558 
   559 --
   560 -- PostgreSQL database dump complete
   561 --
   562