· 7 years ago · Nov 09, 2018, 06:30 PM
1--
2-- PostgreSQL database dump
3--
4
5-- Dumped from database version 9.6.6
6-- Dumped by pg_dump version 9.6.6
7
8SET statement_timeout = 0;
9SET lock_timeout = 0;
10SET idle_in_transaction_session_timeout = 0;
11SET client_encoding = 'UTF8';
12SET standard_conforming_strings = on;
13SET check_function_bodies = false;
14SET client_min_messages = warning;
15SET row_security = off;
16
17--
18-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
19--
20
21CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
22
23
24--
25-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
26--
27
28COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
29
30
31SET search_path = public, pg_catalog;
32
33--
34-- Name: inbox_popis; Type: TYPE; Schema: public; Owner: vjezbefoi
35--
36
37CREATE TYPE inbox_popis AS (
38 ime text,
39 prezime text,
40 sifra integer,
41 naslov text,
42 vrijeme timestamp without time zone
43);
44
45
46ALTER TYPE inbox_popis OWNER TO vjezbefoi;
47
48--
49-- Name: inbox(text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
50--
51
52CREATE FUNCTION inbox(email text) RETURNS SETOF inbox_popis
53 LANGUAGE sql
54 AS $_$SELECT o.ime, o.prezime, p.sifra, p.naslov, p.vrijeme FROM osoba o, poruka p WHERE o.email = p.posiljatelj AND p.primatelj = $1 ORDER BY p.vrijeme$_$;
55
56
57ALTER FUNCTION public.inbox(email text) OWNER TO vjezbefoi;
58
59--
60-- Name: ispisi_iz_grupe(text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
61--
62
63CREATE FUNCTION ispisi_iz_grupe(email text, naziv_grupe text) RETURNS void
64 LANGUAGE sql
65 AS $_$DELETE FROM clanstvo WHERE clan = $1 and grupa = ( SELECT sifra FROM grupa WHERE naziv = $2 )$_$;
66
67
68ALTER FUNCTION public.ispisi_iz_grupe(email text, naziv_grupe text) OWNER TO vjezbefoi;
69
70--
71-- Name: moje_grupe(text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
72--
73
74CREATE FUNCTION moje_grupe(email text) RETURNS SETOF text
75 LANGUAGE sql
76 AS $_$SELECT naziv FROM grupa WHERE sifra IN ( SELECT grupa FROM clanstvo WHERE clan = $1 ) $_$;
77
78
79ALTER FUNCTION public.moje_grupe(email text) OWNER TO vjezbefoi;
80
81SET default_tablespace = '';
82
83SET default_with_oids = false;
84
85--
86-- Name: grupa; Type: TABLE; Schema: public; Owner: vjezbefoi
87--
88
89CREATE TABLE grupa (
90 sifra integer NOT NULL,
91 naziv character varying(40) NOT NULL
92);
93
94
95ALTER TABLE grupa OWNER TO vjezbefoi;
96
97--
98-- Name: moje_grupe_2(text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
99--
100
101CREATE FUNCTION moje_grupe_2(email text) RETURNS SETOF grupa
102 LANGUAGE sql
103 AS $_$SELECT * FROM grupa WHERE sifra IN ( SELECT grupa FROM clanstvo WHERE clan = $1 )$_$;
104
105
106ALTER FUNCTION public.moje_grupe_2(email text) OWNER TO vjezbefoi;
107
108--
109-- Name: nova_grupa(text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
110--
111
112CREATE FUNCTION nova_grupa(kreator text, naziv_grupe text) RETURNS void
113 LANGUAGE plpgsql
114 AS $$DECLARE postoji BOOLEAN; BEGIN postoji := EXISTS( SELECT naziv FROM grupa WHERE naziv = naziv_grupe ); IF NOT postoji THEN INSERT INTO grupa(naziv) VALUES (naziv_grupe); PERFORM upisi_u_grupu(kreator, naziv_grupe); ELSE RAISE EXCEPTION '%', 'Vec postoji grupa pod nazivom ' || naziv_grupe; END IF; END;$$;
115
116
117ALTER FUNCTION public.nova_grupa(kreator text, naziv_grupe text) OWNER TO vjezbefoi;
118
119--
120-- Name: nova_zamolba(); Type: FUNCTION; Schema: public; Owner: vjezbefoi
121--
122
123CREATE FUNCTION nova_zamolba() RETURNS trigger
124 LANGUAGE plpgsql
125 AS $$DECLARE molitelj osoba; DECLARE sadrzaj_poruke TEXT; BEGIN IF NEW.status = 'poslano' THEN molitelj := ( SELECT osoba FROM osoba WHERE email = NEW.poslao ); sadrzaj_poruke := molitelj.ime || ' ' || molitelj.prezime || ' ti je poslao zamolbu za prijateljstvom!'; INSERT INTO poruka( posiljatelj,primatelj,naslov,sadrzaj) VALUES(NEW.poslao,NEW.prihvatio,'Nova zamolba',sadrzaj_poruke); END IF; RETURN NEW; END; $$;
126
127
128ALTER FUNCTION public.nova_zamolba() OWNER TO vjezbefoi;
129
130--
131-- Name: posalji_poruku(text, text, text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
132--
133
134CREATE FUNCTION posalji_poruku(naslov text, sadrzaj text, email_primatelja text, email_posiljatelja text) RETURNS void
135 LANGUAGE sql
136 AS $_$INSERT INTO poruka(naslov, sadrzaj, posiljatelj, primatelj) VALUES ($1, $2, $4, $3)$_$;
137
138
139ALTER FUNCTION public.posalji_poruku(naslov text, sadrzaj text, email_primatelja text, email_posiljatelja text) OWNER TO vjezbefoi;
140
141--
142-- Name: provjeri_email(); Type: FUNCTION; Schema: public; Owner: vjezbefoi
143--
144
145CREATE FUNCTION provjeri_email() RETURNS trigger
146 LANGUAGE plpgsql
147 AS $$BEGIN IF NEW.email LIKE '%@%' THEN RETURN NEW; ELSE RAISE EXCEPTION '%', 'E-mail adresa ne sadrzi znak ''@'''; END IF; END;$$;
148
149
150ALTER FUNCTION public.provjeri_email() OWNER TO vjezbefoi;
151
152--
153-- Name: upisi_u_grupu(text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
154--
155
156CREATE FUNCTION upisi_u_grupu(email text, naziv_grupe text) RETURNS void
157 LANGUAGE sql
158 AS $_$INSERT INTO clanstvo SELECT $1, sifra FROM grupa WHERE naziv = $2$_$;
159
160
161ALTER FUNCTION public.upisi_u_grupu(email text, naziv_grupe text) OWNER TO vjezbefoi;
162
163--
164-- Name: zamolba(text, text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
165--
166
167CREATE FUNCTION zamolba(posiljatelj text, primatelj text, naziv_vrste text) RETURNS void
168 LANGUAGE plpgsql
169 AS $$DECLARE postoji BOOLEAN; DECLARE sifra_vrste INT; BEGIN sifra_vrste := ( SELECT sifra FROM vrsta_veze WHERE naziv = naziv_vrste ); postoji := EXISTS( SELECT veza FROM veza WHERE poslao = posiljatelj AND prihvatio = primatelj AND vrsta = sifra_vrste ); IF NOT postoji THEN INSERT INTO veza VALUES (posiljatelj, primatelj, sifra_vrste, 'poslano'); ELSE RAISE EXCEPTION '%', 'Vec postoji takva veza'; END IF; END; $$;
170
171
172ALTER FUNCTION public.zamolba(posiljatelj text, primatelj text, naziv_vrste text) OWNER TO vjezbefoi;
173
174--
175-- Name: clanstvo; Type: TABLE; Schema: public; Owner: vjezbefoi
176--
177
178CREATE TABLE clanstvo (
179 clan text NOT NULL,
180 grupa integer NOT NULL
181);
182
183
184ALTER TABLE clanstvo OWNER TO vjezbefoi;
185
186--
187-- Name: grupa_sifra_seq; Type: SEQUENCE; Schema: public; Owner: vjezbefoi
188--
189
190CREATE SEQUENCE grupa_sifra_seq
191 START WITH 1
192 INCREMENT BY 1
193 NO MINVALUE
194 NO MAXVALUE
195 CACHE 1;
196
197
198ALTER TABLE grupa_sifra_seq OWNER TO vjezbefoi;
199
200--
201-- Name: grupa_sifra_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: vjezbefoi
202--
203
204ALTER SEQUENCE grupa_sifra_seq OWNED BY grupa.sifra;
205
206
207--
208-- Name: osoba; Type: TABLE; Schema: public; Owner: vjezbefoi
209--
210
211CREATE TABLE osoba (
212 email text NOT NULL,
213 ime text NOT NULL,
214 prezime text NOT NULL,
215 drzava text,
216 grad text,
217 spol character varying(6),
218 CONSTRAINT osoba_spol_check CHECK ((((spol)::text = 'musko'::text) OR ((spol)::text = 'zensko'::text)))
219);
220
221
222ALTER TABLE osoba OWNER TO vjezbefoi;
223
224--
225-- Name: poruka; Type: TABLE; Schema: public; Owner: vjezbefoi
226--
227
228CREATE TABLE poruka (
229 sifra integer NOT NULL,
230 posiljatelj text,
231 primatelj text,
232 naslov text,
233 sadrzaj text,
234 vrijeme timestamp without time zone DEFAULT now()
235);
236
237
238ALTER TABLE poruka OWNER TO vjezbefoi;
239
240--
241-- Name: poruka_sifra_seq; Type: SEQUENCE; Schema: public; Owner: vjezbefoi
242--
243
244CREATE SEQUENCE poruka_sifra_seq
245 START WITH 1
246 INCREMENT BY 1
247 NO MINVALUE
248 NO MAXVALUE
249 CACHE 1;
250
251
252ALTER TABLE poruka_sifra_seq OWNER TO vjezbefoi;
253
254--
255-- Name: poruka_sifra_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: vjezbefoi
256--
257
258ALTER SEQUENCE poruka_sifra_seq OWNED BY poruka.sifra;
259
260
261--
262-- Name: veza; Type: TABLE; Schema: public; Owner: vjezbefoi
263--
264
265CREATE TABLE veza (
266 poslao text NOT NULL,
267 prihvatio text NOT NULL,
268 vrsta integer NOT NULL,
269 status character varying(10),
270 CONSTRAINT veza_status_check CHECK ((((status)::text = 'poslano'::text) OR ((status)::text = 'prihvaceno'::text)))
271);
272
273
274ALTER TABLE veza OWNER TO vjezbefoi;
275
276--
277-- Name: vrsta_veze; Type: TABLE; Schema: public; Owner: vjezbefoi
278--
279
280CREATE TABLE vrsta_veze (
281 sifra integer NOT NULL,
282 naziv character varying NOT NULL
283);
284
285
286ALTER TABLE vrsta_veze OWNER TO vjezbefoi;
287
288--
289-- Name: vrsta_veze_sifra_seq; Type: SEQUENCE; Schema: public; Owner: vjezbefoi
290--
291
292CREATE SEQUENCE vrsta_veze_sifra_seq
293 START WITH 1
294 INCREMENT BY 1
295 NO MINVALUE
296 NO MAXVALUE
297 CACHE 1;
298
299
300ALTER TABLE vrsta_veze_sifra_seq OWNER TO vjezbefoi;
301
302--
303-- Name: vrsta_veze_sifra_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: vjezbefoi
304--
305
306ALTER SEQUENCE vrsta_veze_sifra_seq OWNED BY vrsta_veze.sifra;
307
308
309--
310-- Name: grupa sifra; Type: DEFAULT; Schema: public; Owner: vjezbefoi
311--
312
313ALTER TABLE ONLY grupa ALTER COLUMN sifra SET DEFAULT nextval('grupa_sifra_seq'::regclass);
314
315
316--
317-- Name: poruka sifra; Type: DEFAULT; Schema: public; Owner: vjezbefoi
318--
319
320ALTER TABLE ONLY poruka ALTER COLUMN sifra SET DEFAULT nextval('poruka_sifra_seq'::regclass);
321
322
323--
324-- Name: vrsta_veze sifra; Type: DEFAULT; Schema: public; Owner: vjezbefoi
325--
326
327ALTER TABLE ONLY vrsta_veze ALTER COLUMN sifra SET DEFAULT nextval('vrsta_veze_sifra_seq'::regclass);
328
329
330--
331-- Data for Name: clanstvo; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
332--
333
334COPY clanstvo (clan, grupa) FROM stdin;
335markus.schatten@foi.hr 1
336markus.schatten@foi.hr 3
337bara@gmail.com 2
338mirko.malekovic@foi.hr 4
339markus.schatten@foi.hr 6
340\.
341
342
343--
344-- Data for Name: grupa; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
345--
346
347COPY grupa (sifra, naziv) FROM stdin;
3481 nastavnici
3492 programiranje
3503 linux
3514 baze podataka
3526 test
353\.
354
355
356--
357-- Name: grupa_sifra_seq; Type: SEQUENCE SET; Schema: public; Owner: vjezbefoi
358--
359
360SELECT pg_catalog.setval('grupa_sifra_seq', 6, true);
361
362
363--
364-- Data for Name: osoba; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
365--
366
367COPY osoba (email, ime, prezime, drzava, grad, spol) FROM stdin;
368markus.schatten@foi.hr Markus Schatten Hrvatska Varaždin musko
369mirko.malekovic@foi.hr Mirko Maleković Hrvatska Zagreb musko
370bara@gmail.com Barica Prikratki Hrvatska Varaždin zensko
371joza@gmail.com Joža Presvetli Hrvatska Varaždin musko
372\.
373
374
375--
376-- Data for Name: poruka; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
377--
378
379COPY poruka (sifra, posiljatelj, primatelj, naslov, sadrzaj, vrijeme) FROM stdin;
3801 mirko.malekovic@foi.hr markus.schatten@foi.hr Pozdrav Kako ste? 2018-10-19 17:18:30.860893
3813 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:31.269032
3824 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:44.515735
3835 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:45.916185
3846 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:46.091819
3857 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:46.235783
3868 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:46.371803
3879 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:46.523966
38810 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:31:37.97974
389\.
390
391
392--
393-- Name: poruka_sifra_seq; Type: SEQUENCE SET; Schema: public; Owner: vjezbefoi
394--
395
396SELECT pg_catalog.setval('poruka_sifra_seq', 10, true);
397
398
399--
400-- Data for Name: veza; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
401--
402
403COPY veza (poslao, prihvatio, vrsta, status) FROM stdin;
404mirko.malekovic@foi.hr markus.schatten@foi.hr 4 prihvaceno
405bara@gmail.com joza@gmail.com 3 poslano
406markus.schatten@foi.hr mirko.malekovic@foi.hr 4 poslano
407\.
408
409
410--
411-- Data for Name: vrsta_veze; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
412--
413
414COPY vrsta_veze (sifra, naziv) FROM stdin;
4151 prijateljstvo
4162 rodbina
4173 ljubavna veza
4184 suradnik
419\.
420
421
422--
423-- Name: vrsta_veze_sifra_seq; Type: SEQUENCE SET; Schema: public; Owner: vjezbefoi
424--
425
426SELECT pg_catalog.setval('vrsta_veze_sifra_seq', 4, true);
427
428
429--
430-- Name: clanstvo clanstvo_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
431--
432
433ALTER TABLE ONLY clanstvo
434 ADD CONSTRAINT clanstvo_pkey PRIMARY KEY (clan, grupa);
435
436
437--
438-- Name: grupa grupa_naziv_key; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
439--
440
441ALTER TABLE ONLY grupa
442 ADD CONSTRAINT grupa_naziv_key UNIQUE (naziv);
443
444
445--
446-- Name: grupa grupa_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
447--
448
449ALTER TABLE ONLY grupa
450 ADD CONSTRAINT grupa_pkey PRIMARY KEY (sifra);
451
452
453--
454-- Name: osoba osoba_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
455--
456
457ALTER TABLE ONLY osoba
458 ADD CONSTRAINT osoba_pkey PRIMARY KEY (email);
459
460
461--
462-- Name: poruka poruka_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
463--
464
465ALTER TABLE ONLY poruka
466 ADD CONSTRAINT poruka_pkey PRIMARY KEY (sifra);
467
468
469--
470-- Name: veza veza_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
471--
472
473ALTER TABLE ONLY veza
474 ADD CONSTRAINT veza_pkey PRIMARY KEY (poslao, prihvatio, vrsta);
475
476
477--
478-- Name: vrsta_veze vrsta_veze_naziv_key; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
479--
480
481ALTER TABLE ONLY vrsta_veze
482 ADD CONSTRAINT vrsta_veze_naziv_key UNIQUE (naziv);
483
484
485--
486-- Name: vrsta_veze vrsta_veze_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
487--
488
489ALTER TABLE ONLY vrsta_veze
490 ADD CONSTRAINT vrsta_veze_pkey PRIMARY KEY (sifra);
491
492
493--
494-- Name: osoba email_provjera; Type: TRIGGER; Schema: public; Owner: vjezbefoi
495--
496
497CREATE TRIGGER email_provjera BEFORE INSERT OR UPDATE ON osoba FOR EACH ROW EXECUTE PROCEDURE provjeri_email();
498
499
500--
501-- Name: veza poruka_uz_zamolbu; Type: TRIGGER; Schema: public; Owner: vjezbefoi
502--
503
504CREATE TRIGGER poruka_uz_zamolbu BEFORE INSERT ON veza FOR EACH ROW EXECUTE PROCEDURE nova_zamolba();
505
506
507--
508-- Name: clanstvo clanstvo_clan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
509--
510
511ALTER TABLE ONLY clanstvo
512 ADD CONSTRAINT clanstvo_clan_fkey FOREIGN KEY (clan) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
513
514
515--
516-- Name: clanstvo clanstvo_grupa_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
517--
518
519ALTER TABLE ONLY clanstvo
520 ADD CONSTRAINT clanstvo_grupa_fkey FOREIGN KEY (grupa) REFERENCES grupa(sifra) ON UPDATE CASCADE ON DELETE RESTRICT;
521
522
523--
524-- Name: poruka poruka_posiljatelj_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
525--
526
527ALTER TABLE ONLY poruka
528 ADD CONSTRAINT poruka_posiljatelj_fkey FOREIGN KEY (posiljatelj) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
529
530
531--
532-- Name: poruka poruka_primatelj_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
533--
534
535ALTER TABLE ONLY poruka
536 ADD CONSTRAINT poruka_primatelj_fkey FOREIGN KEY (primatelj) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
537
538
539--
540-- Name: veza veza_poslao_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
541--
542
543ALTER TABLE ONLY veza
544 ADD CONSTRAINT veza_poslao_fkey FOREIGN KEY (poslao) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
545
546
547--
548-- Name: veza veza_prihvatio_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
549--
550
551ALTER TABLE ONLY veza
552 ADD CONSTRAINT veza_prihvatio_fkey FOREIGN KEY (prihvatio) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
553
554
555--
556-- Name: veza veza_vrsta_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
557--
558
559ALTER TABLE ONLY veza
560 ADD CONSTRAINT veza_vrsta_fkey FOREIGN KEY (vrsta) REFERENCES vrsta_veze(sifra) ON UPDATE CASCADE ON DELETE RESTRICT;
561
562
563--
564-- PostgreSQL database dump complete
565--