2 -- PostgreSQL database dump
5 -- Started on 2009-04-28 21:06:29 CEST
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;
14 -- TOC entry 6 (class 2615 OID 16386)
15 -- Name: miniobchod; Type: SCHEMA; Schema: -; Owner: miniobchod
18 CREATE SCHEMA miniobchod;
21 ALTER SCHEMA miniobchod OWNER TO miniobchod;
23 SET search_path = miniobchod, pg_catalog;
26 -- TOC entry 332 (class 1247 OID 16398)
27 -- Dependencies: 333 6
28 -- Name: email; Type: DOMAIN; Schema: miniobchod; Owner: miniobchod
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));
35 ALTER DOMAIN miniobchod.email OWNER TO miniobchod;
38 -- TOC entry 1821 (class 0 OID 0)
40 -- Name: DOMAIN email; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
43 COMMENT ON DOMAIN email IS 'e-mailová adresa';
47 -- TOC entry 344 (class 1247 OID 16566)
48 -- Dependencies: 345 6
49 -- Name: psc; Type: DOMAIN; Schema: miniobchod; Owner: miniobchod
52 CREATE DOMAIN psc AS character varying(255) NOT NULL
53 CONSTRAINT psc_regexp CHECK (((VALUE)::text ~ '^\\d{3} ?\\d{2}$'::text));
56 ALTER DOMAIN miniobchod.psc OWNER TO miniobchod;
59 -- TOC entry 1822 (class 0 OID 0)
61 -- Name: DOMAIN psc; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
64 COMMENT ON DOMAIN psc IS 'Poštovní směrovací číslo';
68 -- TOC entry 55 (class 1255 OID 16459)
70 -- Name: nahodne_heslo(); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
73 CREATE FUNCTION nahodne_heslo() RETURNS character
74 AS $$select sha512(random()::text || random()::text || random()::text);$$
78 ALTER FUNCTION miniobchod.nahodne_heslo() OWNER TO miniobchod;
81 -- TOC entry 56 (class 1255 OID 16460)
83 -- Name: sha1(text); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
86 CREATE FUNCTION sha1(text) RETURNS text
88 SELECT encode(digest(decode($1,'escape'::text),'sha1'::text),'hex');
90 LANGUAGE sql IMMUTABLE STRICT;
93 ALTER FUNCTION miniobchod.sha1(text) OWNER TO miniobchod;
96 -- TOC entry 57 (class 1255 OID 16461)
98 -- Name: sha256(text); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
101 CREATE FUNCTION sha256(text) RETURNS text
103 SELECT encode(digest(decode($1,'escape'::text),'sha256'::text),'hex');
105 LANGUAGE sql IMMUTABLE STRICT;
108 ALTER FUNCTION miniobchod.sha256(text) OWNER TO miniobchod;
111 -- TOC entry 54 (class 1255 OID 16458)
113 -- Name: sha512(text); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
116 CREATE FUNCTION sha512(text) RETURNS text
118 SELECT encode(digest(decode($1,'escape'::text),'sha512'::text),'hex');
120 LANGUAGE sql IMMUTABLE STRICT;
123 ALTER FUNCTION miniobchod.sha512(text) OWNER TO miniobchod;
126 -- TOC entry 58 (class 1255 OID 16608)
127 -- Dependencies: 350 6
128 -- Name: vyjimka(character varying); Type: FUNCTION; Schema: miniobchod; Owner: miniobchod
131 CREATE FUNCTION vyjimka(chyba character varying) RETURNS void
133 RAISE EXCEPTION '%', chyba;
138 ALTER FUNCTION miniobchod.vyjimka(chyba character varying) OWNER TO miniobchod;
141 -- TOC entry 1823 (class 0 OID 0)
143 -- Name: FUNCTION vyjimka(chyba character varying); Type: COMMENT; Schema: miniobchod; Owner: miniobchod
146 COMMENT ON FUNCTION vyjimka(chyba character varying) IS 'Vyhodí výjimku';
150 -- TOC entry 1528 (class 1259 OID 16575)
152 -- Name: adresa_seq; Type: SEQUENCE; Schema: miniobchod; Owner: miniobchod
155 CREATE SEQUENCE adresa_seq
162 ALTER TABLE miniobchod.adresa_seq OWNER TO miniobchod;
164 SET default_tablespace = '';
166 SET default_with_oids = false;
169 -- TOC entry 1529 (class 1259 OID 16578)
170 -- Dependencies: 1804 332 344 6
171 -- Name: adresa; Type: TABLE; Schema: miniobchod; Owner: miniobchod; Tablespace:
174 CREATE TABLE adresa (
175 id bigint DEFAULT nextval('adresa_seq'::regclass) NOT NULL,
177 jmeno character varying(255),
178 ulice character varying(255),
180 mesto character varying(255),
185 ALTER TABLE miniobchod.adresa OWNER TO miniobchod;
188 -- TOC entry 1824 (class 0 OID 0)
189 -- Dependencies: 1529
190 -- Name: TABLE adresa; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
193 COMMENT ON TABLE adresa IS 'Poštovní adresa';
197 -- TOC entry 1825 (class 0 OID 0)
198 -- Dependencies: 1529
199 -- Name: COLUMN adresa.jmeno; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
202 COMMENT ON COLUMN adresa.jmeno IS 'Jméno příjemce';
206 -- TOC entry 1826 (class 0 OID 0)
207 -- Dependencies: 1529
208 -- Name: COLUMN adresa.cp; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
211 COMMENT ON COLUMN adresa.cp IS 'Číslo popisné';
215 -- TOC entry 1827 (class 0 OID 0)
216 -- Dependencies: 1529
217 -- Name: COLUMN adresa.psc; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
220 COMMENT ON COLUMN adresa.psc IS 'Poštovní směrovací číslo';
224 -- TOC entry 1527 (class 1259 OID 16540)
225 -- Dependencies: 1803 6
226 -- Name: objednane_zbozi; Type: TABLE; Schema: miniobchod; Owner: miniobchod; Tablespace:
229 CREATE TABLE objednane_zbozi (
230 objednavka bigint NOT NULL,
231 zbozi bit varying NOT NULL,
232 mnozstvi smallint DEFAULT 1 NOT NULL
236 ALTER TABLE miniobchod.objednane_zbozi OWNER TO miniobchod;
239 -- TOC entry 1828 (class 0 OID 0)
240 -- Dependencies: 1527
241 -- Name: TABLE objednane_zbozi; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
244 COMMENT ON TABLE objednane_zbozi IS 'Vazební tabulka, která definuje obsah objednávky – objednané zboží';
248 -- TOC entry 1525 (class 1259 OID 16408)
250 -- Name: objednavka_seq; Type: SEQUENCE; Schema: miniobchod; Owner: miniobchod
253 CREATE SEQUENCE objednavka_seq
260 ALTER TABLE miniobchod.objednavka_seq OWNER TO miniobchod;
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:
268 CREATE TABLE objednavka (
269 id bigint DEFAULT nextval('objednavka_seq'::regclass) NOT NULL,
270 kod character varying DEFAULT nahodne_heslo() NOT NULL,
272 podano timestamp with time zone DEFAULT now() NOT NULL,
277 ALTER TABLE miniobchod.objednavka OWNER TO miniobchod;
280 -- TOC entry 1829 (class 0 OID 0)
281 -- Dependencies: 1526
282 -- Name: TABLE objednavka; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
285 COMMENT ON TABLE objednavka IS 'Objednávka zboží';
289 -- TOC entry 1830 (class 0 OID 0)
290 -- Dependencies: 1526
291 -- Name: COLUMN objednavka.id; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
294 COMMENT ON COLUMN objednavka.id IS 'Číslo objednávky je zároveň variabilním symbolem';
298 -- TOC entry 1831 (class 0 OID 0)
299 -- Dependencies: 1526
300 -- Name: COLUMN objednavka.kod; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
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í.';
307 -- TOC entry 1832 (class 0 OID 0)
308 -- Dependencies: 1526
309 -- Name: COLUMN objednavka.uzivatel; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
312 COMMENT ON COLUMN objednavka.uzivatel IS 'Kdo si objednal';
316 -- TOC entry 1833 (class 0 OID 0)
317 -- Dependencies: 1526
318 -- Name: COLUMN objednavka.podano; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
321 COMMENT ON COLUMN objednavka.podano IS 'Datum a čas podání objednávky';
325 -- TOC entry 1834 (class 0 OID 0)
326 -- Dependencies: 1526
327 -- Name: COLUMN objednavka.adresa; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
330 COMMENT ON COLUMN objednavka.adresa IS 'Kam se má objednávka doručit';
334 -- TOC entry 1524 (class 1259 OID 16400)
335 -- Dependencies: 1798 1799 6 332
336 -- Name: uzivatel; Type: TABLE; Schema: miniobchod; Owner: miniobchod; Tablespace:
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()
346 ALTER TABLE miniobchod.uzivatel OWNER TO miniobchod;
349 -- TOC entry 1835 (class 0 OID 0)
350 -- Dependencies: 1524
351 -- Name: TABLE uzivatel; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
354 COMMENT ON TABLE uzivatel IS 'Zákazník';
358 -- TOC entry 1836 (class 0 OID 0)
359 -- Dependencies: 1524
360 -- Name: COLUMN uzivatel.email; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
363 COMMENT ON COLUMN uzivatel.email IS 'Uživatel je identifikovaný pomocí své e-mailové adresy';
367 -- TOC entry 1837 (class 0 OID 0)
368 -- Dependencies: 1524
369 -- Name: COLUMN uzivatel.heslo; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
372 COMMENT ON COLUMN uzivatel.heslo IS 'Vygenerované heslo, kterým se uživatel prokazuje (pošle se mu na e-mail)';
376 -- TOC entry 1838 (class 0 OID 0)
377 -- Dependencies: 1524
378 -- Name: COLUMN uzivatel.zalozen; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
381 COMMENT ON COLUMN uzivatel.zalozen IS 'Datum a čas registrace zákazníka';
385 -- TOC entry 1522 (class 1259 OID 16387)
387 -- Name: zbozi_seq; Type: SEQUENCE; Schema: miniobchod; Owner: miniobchod
390 CREATE SEQUENCE zbozi_seq
397 ALTER TABLE miniobchod.zbozi_seq OWNER TO miniobchod;
400 -- TOC entry 1523 (class 1259 OID 16389)
401 -- Dependencies: 1797 6
402 -- Name: zbozi; Type: TABLE; Schema: miniobchod; Owner: miniobchod; Tablespace:
406 id bigint DEFAULT nextval('zbozi_seq'::regclass) NOT NULL,
407 nazev character varying(255),
412 ALTER TABLE miniobchod.zbozi OWNER TO miniobchod;
415 -- TOC entry 1839 (class 0 OID 0)
416 -- Dependencies: 1523
417 -- Name: TABLE zbozi; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
420 COMMENT ON TABLE zbozi IS 'Položka katalogu';
424 -- TOC entry 1840 (class 0 OID 0)
425 -- Dependencies: 1523
426 -- Name: COLUMN zbozi.id; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
429 COMMENT ON COLUMN zbozi.id IS 'Číslo zboží';
433 -- TOC entry 1841 (class 0 OID 0)
434 -- Dependencies: 1523
435 -- Name: COLUMN zbozi.nazev; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
438 COMMENT ON COLUMN zbozi.nazev IS 'Název zboží';
442 -- TOC entry 1842 (class 0 OID 0)
443 -- Dependencies: 1523
444 -- Name: COLUMN zbozi.popis; Type: COMMENT; Schema: miniobchod; Owner: miniobchod
447 COMMENT ON COLUMN zbozi.popis IS 'Popis zboží';
451 -- TOC entry 1815 (class 2606 OID 16586)
452 -- Dependencies: 1529 1529
453 -- Name: adresa_pk; Type: CONSTRAINT; Schema: miniobchod; Owner: miniobchod; Tablespace:
456 ALTER TABLE ONLY adresa
457 ADD CONSTRAINT adresa_pk PRIMARY KEY (id);
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:
466 ALTER TABLE ONLY objednane_zbozi
467 ADD CONSTRAINT objednane_zbozi_pk PRIMARY KEY (objednavka, zbozi);
471 -- TOC entry 1811 (class 2606 OID 16415)
472 -- Dependencies: 1526 1526
473 -- Name: objednavka_pk; Type: CONSTRAINT; Schema: miniobchod; Owner: miniobchod; Tablespace:
476 ALTER TABLE ONLY objednavka
477 ADD CONSTRAINT objednavka_pk PRIMARY KEY (id);
481 -- TOC entry 1808 (class 2606 OID 16407)
482 -- Dependencies: 1524 1524
483 -- Name: uzivatel_pk; Type: CONSTRAINT; Schema: miniobchod; Owner: miniobchod; Tablespace:
486 ALTER TABLE ONLY uzivatel
487 ADD CONSTRAINT uzivatel_pk PRIMARY KEY (email);
491 -- TOC entry 1806 (class 2606 OID 16397)
492 -- Dependencies: 1523 1523
493 -- Name: zbozi_pk; Type: CONSTRAINT; Schema: miniobchod; Owner: miniobchod; Tablespace:
496 ALTER TABLE ONLY zbozi
497 ADD CONSTRAINT zbozi_pk PRIMARY KEY (id);
501 -- TOC entry 1809 (class 1259 OID 16597)
502 -- Dependencies: 1526
503 -- Name: fki_objednavka_adresa_fk; Type: INDEX; Schema: miniobchod; Owner: miniobchod; Tablespace:
506 CREATE INDEX fki_objednavka_adresa_fk ON objednavka USING btree (adresa);
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
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;
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
524 COMMENT ON RULE objednavka_adresa_uzivatel ON objednavka IS 'Uživatel může objednat zboží jen na svoji adresu';
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
533 ALTER TABLE ONLY adresa
534 ADD CONSTRAINT adresa_uzivatel_fk FOREIGN KEY (uzivatel) REFERENCES uzivatel(email);
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
543 ALTER TABLE ONLY objednavka
544 ADD CONSTRAINT objednavka_adresa_fk FOREIGN KEY (adresa) REFERENCES adresa(id);
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
553 ALTER TABLE ONLY objednavka
554 ADD CONSTRAINT objednavka_uzivatel_fk FOREIGN KEY (uzivatel) REFERENCES uzivatel(email);
557 -- Completed on 2009-04-28 21:06:38 CEST
560 -- PostgreSQL database dump complete