EkegDHgz

1begin transaction;
2
3CREATE TABLE osoba (
4email TEXT PRIMARY KEY,
5ime TEXT NOT NULL,
6prezime TEXT NOT NULL,
7drzava TEXT,
8grad TEXT,
9spol VARCHAR( 6 ) CHECK( spol = 'zensko'
10OR spol = 'Musko' ) 
11);
12
13CREATE TABLE poruka (
14sifra SERIAL PRIMARY KEY,
15posiljatelj TEXT REFERENCES osoba( email )
16ON UPDATE CASCADE ON DELETE RESTRICT,
17primatelj TEXT REFERENCES osoba( email )
18ON UPDATE CASCADE ON DELETE RESTRICT,
19naslov TEXT,
20sadrzaj TEXT,
21vrijeme TIMESTAMP DEFAULT now()
22);
23
24CREATE TABLE grupa (
25sifra SERIAL PRIMARY KEY,
26naziv VARCHAR(40) NOT NULL UNIQUE
27);
28
29CREATE TABLE clanstvo (
30clan TEXT REFERENCES osoba( email )
31ON UPDATE CASCADE ON DELETE RESTRICT,
32grupa INT REFERENCES grupa( sifra ),
33PRIMARY KEY( clan, grupa )
34);
35
36CREATE TABLE vrsta_veze (
37sifra SERIAL PRIMARY KEY,
38naziv VARCHAR NOT NULL UNIQUE
39);
40
41CREATE TABLE veza (
42poslao TEXT REFERENCES osoba( email )
43ON UPDATE CASCADE ON DELETE RESTRICT,
44prihvatio TEXT REFERENCES osoba( email )
45ON UPDATE CASCADE ON DELETE RESTRICT,
46vrsta INT REFERENCES vrsta_veze( sifra ),
47status VARCHAR( 10 ) CHECK( status =
48'poslano' OR status = 'prihvaceno' ),
49PRIMARY KEY( poslao, prihvatio, vrsta )
50);
51
52INSERT INTO grupa(sifra, naziv) VALUES(1, 'Grupa 1');
53INSERT INTO grupa(sifra, naziv) VALUES(2, 'Grupa 2');
54INSERT INTO grupa(sifra, naziv) VALUES(3, 'Grupa 3');
55INSERT INTO grupa(sifra, naziv) VALUES(4, 'Grupa 4');
56INSERT INTO grupa(sifra, naziv) VALUES(5, 'Grupa 5');
57
58INSERT INTO osoba(email, ime, prezime, drzava, grad, spol) VALUES('dbaricevi@foi.hr', 'Darjan', 'Baričević', 'HR', 'Našice', 'Musko');
59INSERT INTO osoba(email, ime, prezime, drzava, grad, spol) VALUES('hdumancic@foi.hr', 'Hrvoje', 'Dumancic', 'HR', 'Osijek', 'Musko');
60INSERT INTO osoba(email, ime, prezime, drzava, grad, spol) VALUES('pfumic@foi.hr', 'Patrik', 'Fumić', 'HR', 'Belišće', 'Musko');
61INSERT INTO osoba(email, ime, prezime, drzava, grad, spol) VALUES('smiljak@foi.hr', 'Stipo', 'Miljak', 'HR', 'Bjelovar', 'Musko');
62INSERT INTO osoba(email, ime, prezime, drzava, grad, spol) VALUES('thorvat@foi.hr', 'Tomislav', 'Horvat', 'HR', 'Koprivnica', 'Musko');
63
64INSERT INTO vrsta_veze(sifra, naziv) VALUES(1, 'veza1');
65INSERT INTO vrsta_veze(sifra, naziv) VALUES(2, 'veza2');
66INSERT INTO vrsta_veze(sifra, naziv) VALUES(3, 'veza3');
67INSERT INTO vrsta_veze(sifra, naziv) VALUES(4, 'veza4');
68INSERT INTO vrsta_veze(sifra, naziv) VALUES(5, 'veza5');
69
70INSERT INTO clanstvo(clan, grupa) VALUES('dbaricevi@foi.hr', 1);
71INSERT INTO clanstvo(clan, grupa) VALUES('hdumancic@foi.hr', 2);
72INSERT INTO clanstvo(clan, grupa) VALUES('pfumic@foi.hr', 3);
73INSERT INTO clanstvo(clan, grupa) VALUES('smiljak@foi.hr', 4);
74INSERT INTO clanstvo(clan, grupa) VALUES('thorvat@foi.hr', 5);
75
76INSERT INTO veza(poslao, prihvatio, vrsta, status) VALUES('dbaricevi@foi.hr', 'hdumancic@foi.hr', 1, 'poslano');
77INSERT INTO veza(poslao, prihvatio, vrsta, status) VALUES('dbaricevi@foi.hr', 'pfumic@foi.hr', 2, 'poslano');
78INSERT INTO veza(poslao, prihvatio, vrsta, status) VALUES('dbaricevi@foi.hr', 'thorvat@foi.hr', 3, 'poslano');
79INSERT INTO veza(poslao, prihvatio, vrsta, status) VALUES('pfumic@foi.hr', 'hdumancic@foi.hr', 4, 'prihvaceno');
80INSERT INTO veza(poslao, prihvatio, vrsta, status) VALUES('dbaricevi@foi.hr', 'hdumancic@foi.hr', 5, 'poslano');
81
82INSERT INTO poruka(posiljatelj, primatelj, naslov, sadrzaj) VALUES('dbaricevi@foi.hr', 'hdumancic@foi.hr', 'Naslov 1', 'Sadrzaj 1');
83INSERT INTO poruka(posiljatelj, primatelj, naslov, sadrzaj) VALUES('hdumancic@foi.hr', 'dbaricevi@foi.hr', 'Naslov 2', 'Sadrzaj 2');
84INSERT INTO poruka(posiljatelj, primatelj, naslov, sadrzaj) VALUES('thorvat@foi.hr', 'dbaricevi@foi.hr', 'Naslov 3', 'Sadrzaj 3');
85INSERT INTO poruka(posiljatelj, primatelj, naslov, sadrzaj) VALUES('smiljak@foi.hr', 'dbaricevi@foi.hr', 'Naslov 4', 'Sadrzaj 4');
86INSERT INTO poruka(posiljatelj, primatelj, naslov, sadrzaj) VALUES('hdumancic@foi.hr', 'hdumancic@foi.hr', 'Naslov 5', 'Sadrzaj 5');
87
88CREATE OR REPLACE FUNCTION upisi_u_grupu( email
89TEXT, naziv_grupe TEXT )
90RETURNS VOID
91AS '
92INSERT INTO clanstvo
93SELECT $1, sifra
94FROM grupa
95WHERE naziv = $2
96'
97LANGUAGE SQL;
98
99SELECT upisi_u_grupu(
100 'dbaricevi@foi.hr',
101 'Grupa 2');
102
103
104CREATE OR REPLACE FUNCTION ispisi_iz_grupe(
105 email TEXT, naziv_grupe TEXT )
106RETURNS VOID
107AS '
108DELETE FROM clanstvo
109WHERE clan = $1
110AND grupa = (
111SELECT sifra
112FROM grupa
113WHERE naziv = $2
114)
115'
116LANGUAGE SQL;
117
118SELECT ispisi_iz_grupe(
119 'dbaricevi@foi.hr',
120 'Grupa 2');
121
122
123CREATE OR REPLACE FUNCTION posalji_poruku(
124primatelj text, posiljatel text, 
125naslov TEXT, sadrzaj TEXT )
126RETURNS VOID
127AS 
128'INSERT INTO poruka(posiljatelj, primatelj, naslov, sadrzaj) 
129VALUES($2, $1, $3, $4)'
130LANGUAGE SQL;
131
132SELECT posalji_poruku(
133'dbaricevi@foi.hr',
134'dbaricevi@foi.hr',
135'Postovanje',
136'Kako ste?' );
137
138CREATE OR REPLACE FUNCTION moje_grupe(
139email TEXT )
140RETURNS SETOF TEXT
141AS '
142SELECT naziv
143FROM grupa
144WHERE sifra IN (
145SELECT grupa
146FROM clanstvo
147WHERE clan = $1
148)
149'
150LANGUAGE SQL;
151
152SELECT moje_grupe(
153'dbaricevi@foi.hr' );
154
155CREATE OR REPLACE FUNCTION moje_grupe_2(
156email TEXT )
157RETURNS SETOF grupa
158AS '
159SELECT *
160FROM grupa
161WHERE sifra IN (
162SELECT grupa
163FROM clanstvo
164WHERE clan = $1
165)
166'
167LANGUAGE SQL;
168
169SELECT
170moje_grupe_2( 'dbaricevi@foi.hr' );
171
172CREATE TYPE inbox_popis AS (
173ime TEXT,
174prezime TEXT,
175sifra INT,
176naslov TEXT,
177vrijeme TIMESTAMP
178);
179
180CREATE OR REPLACE FUNCTION inbox( email TEXT )
181RETURNS SETOF inbox_popis
182AS '
183SELECT o.ime, o.prezime, p.sifra,
184 p.naslov, p.vrijeme
185FROM osoba o, poruka p
186WHERE o.email = p.posiljatelj
187AND p.primatelj = $1
188ORDER BY p.vrijeme
189'
190LANGUAGE SQL;
191
192CREATE OR REPLACE FUNCTION
193zamolba( posiljatelj TEXT, primatelj TEXT, naziv_vrste TEXT )
194RETURNS VOID
195AS $$
196DECLARE postoji BOOLEAN;
197DECLARE sifra_vrste INT;
198BEGIN
199sifra_vrste := (
200SELECT sifra
201FROM vrsta_veze
202WHERE naziv = naziv_vrste
203);
204postoji := EXISTS(
205SELECT veza
206FROM veza
207WHERE poslao = posiljatelj
208AND prihvatio = primatelj
209AND vrsta = sifra_vrste
210);
211IF NOT postoji THEN
212INSERT INTO veza
213VALUES ( posiljatelj, primatelj,
214 sifra_vrste, 'poslano' );
215ELSE
216RAISE EXCEPTION '%','Vec postoji
217takva veza';
218END IF;
219 END;
220$$
221LANGUAGE plpgsql;
222
223
224CREATE OR REPLACE FUNCTION
225nova_grupa( kreator TEXT, naziv_grupe
226TEXT )
227RETURNS VOID
228AS $$
229DECLARE postoji BOOLEAN;
230BEGIN
231postoji := EXISTS(
232SELECT naziv
233FROM grupa
234WHERE naziv = naziv_grupe
235);
236IF NOT postoji THEN
237INSERT INTO grupa( naziv )
238VALUES ( naziv_grupe );
239PERFORM upisi_u_grupu(
240 kreator, naziv_grupe );
241ELSE
242RAISE EXCEPTION '%', 'Vec postoji
243 grupa pod nazivom ' || naziv_grupe;
244END IF;
245END;
246$$
247LANGUAGE plpgsql;
248
249
250CREATE OR REPLACE FUNCTION registracija(
251mail TEXT, ime TEXT, prezime TEXT, drzava TEXT, grad TEXT, spol TEXT)
252RETURNS VOID
253AS $$
254DECLARE postoji BOOLEAN;
255BEGIN
256postoji := EXISTS(SELECT * FROM osoba WHERE email = mail);
257IF NOT postoji THEN
258INSERT INTO osoba(email, ime, prezime, drzava, grad, spol) VALUES(mail, ime, prezime, drzava, grad , spol);
259ELSE
260RAISE EXCEPTION '%', 'VEC POSTOJI';
261END IF;
262END;
263$$
264LANGUAGE plpgsql;
265
266
267commit transaction;