BEGIN;
CREATE TABLE osoba (
email TEXT PRIMARY KEY,
ime TEXT NOT NULL,
prezime TEXT NOT NULL,
drzava TEXT,
grad TEXT,
spol VARCHAR( 6 ) CHECK( spol = 'zensko'
OR spol = 'musko' ) );
CREATE TABLE poruka (
sifra SERIAL PRIMARY KEY,
posiljatelj TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
primatelj TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
naslov TEXT,
sadrzaj TEXT,
vrijeme TIMESTAMP DEFAULT now() );
CREATE TABLE grupa (
sifra SERIAL PRIMARY KEY,
naziv VARCHAR(40) NOT NULL UNIQUE
);
INSERT INTO grupa VALUES (DEFAULT, 'naziv1');
INSERT INTO grupa VALUES (DEFAULT, 'naziv2');
INSERT INTO grupa VALUES (DEFAULT, 'naziv3');
INSERT INTO grupa VALUES (DEFAULT, 'naziv4');
INSERT INTO grupa VALUES (DEFAULT, 'naziv5');
CREATE TABLE clanstvo (
clan TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
grupa INT REFERENCES grupa( sifra ),
PRIMARY KEY( clan, grupa )
);
CREATE TABLE vrsta_veze (
sifra SERIAL PRIMARY KEY,
naziv VARCHAR NOT NULL UNIQUE
);
INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv1');
INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv2');
INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv3');
INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv4');
INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv5');
CREATE TABLE veza (
poslao TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
prihvatio TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
vrsta INT REFERENCES vrsta_veze( sifra ),
status VARCHAR( 10 ) CHECK( status =
'poslano' OR status = 'prihvaceno' ),
PRIMARY KEY( poslao, prihvatio, vrsta )
);
CREATE OR REPLACE FUNCTION upisi_u_grupu(email TEXT, naziv_grupe TEXT)
RETURNS VOID
AS 'INSERT INTO clanstvo SELECT $1, sifra FROM grupa WHERE naziv = $2'
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION ispisi_iz_grupe(email TEXT, naziv_grupe TEXT)
RETURNS VOID
AS 'DELETE FROM clanstvo WHERE clan = $1 AND grupa = (SELECT sifra FROM grupa WHERE naziv = $2)'
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION posalji_poruku(email_posiljatelja TEXT, email_primatelja TEXT, naslov TEXT, sadrzaj TEXT)
RETURNS VOID
AS 'INSERT INTO poruka values (default,$1,$2,$3,$4,default)'
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION moje_grupe(email TEXT)
RETURNS SETOF TEXT
AS 'SELECT naziv FROM grupa WHERE sifra IN (SELECT grupa FROM clanstvo WHERE clan = $1)'
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION moje_grupe_2(email TEXT)
RETURNS SETOF grupa
AS 'SELECT * FROM grupa WHERE sifra IN (SELECT grupa FROM clanstvo WHERE clan = $1)'
LANGUAGE SQL;
CREATE TYPE inbox_popis AS (ime TEXT, prezime TEXT, sifra INT, naslov TEXT, vrijeme TIMESTAMP);
CREATE OR REPLACE FUNCTION inbox( email TEXT )
RETURNS SETOF inbox_popis
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'
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION zamolba( posiljatelj TEXT, primatelj TEXT, naziv_vrste TEXT )
RETURNS VOID
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;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION
nova_grupa( kreator TEXT, naziv_grupe TEXT )
RETURNS VOID
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;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION registracija(email_novi TEXT, ime TEXT, prezime TEXT,grad TEXT,spol TEXT)
RETURNS VOID AS $$
DECLARE Postoji BOOLEAN;
BEGIN
Postoji:= EXISTS (Select email as broj from osoba where email=email_novi);
IF NOT Postoji then
insert into osoba values(email_novi,ime,prezime,grad,spol);
ELSE
RAISE EXCEPTION '%', 'Vec postoji korisnik sa emailom ' || email1;
END IF;
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION provjeri_email()
RETURNS TRIGGER
AS $$
BEGIN
IF NEW.email LIKE '%@%' THEN
RETURN NEW;
ELSE
RAISE EXCEPTION '%', 'E-mail adresa ne sadrzi znak ''@''';
END IF;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER email_provjera
BEFORE INSERT OR UPDATE
ON osoba
FOR EACH ROW
EXECUTE PROCEDURE provjeri_email();
CREATE OR REPLACE FUNCTION nova_zamolba()
RETURNS TRIGGER
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;
$$ LANGUAGE plpgsql;
CREATE TRIGGER poruka_uz_zamolbu
BEFORE INSERT
ON veza
FOR EACH ROW
EXECUTE PROCEDURE nova_zamolba();
CREATE OR REPLACE FUNCTION veza_prihvacena()
RETURNS TRIGGER
AS $$
DECLARE postoji BOOLEAN;
BEGIN
IF NEW.status = 'prihvaceno' THEN
postoji := EXISTS(
SELECT veza
FROM veza
WHERE poslao = NEW.prihvatio
AND prihvatio = NEW.poslao
AND vrsta = NEW.vrsta
);
IF NOT postoji THEN
INSERT INTO veza
VALUES ( NEW.prihvatio, NEW.poslao,
NEW.vrsta, 'prihvaceno' );
END IF;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER prihvat_veze
AFTER INSERT OR UPDATE
ON veza
FOR EACH ROW
EXECUTE PROCEDURE veza_prihvacena();
CREATE OR REPLACE FUNCTION jel_veza_prihvacena()
RETURNS TRIGGER
AS $$
DECLARE Postojiveza BOOLEAN;
BEGIN
Postojiveza:= EXISTS (SELECT * FROM veza WHERE new.posiljatelj = poslao AND new.primatelj = prihvatio AND status = 'prihvaceno');
IF NOT Postojiveza then
INSERT INTO poruka VALUES (default, '
[email protected]', new.posiljatelj, 'Obavijest', 'Ne mozes poslati poruku korisniku ' || new.primatelj || ' jer nisi s njime povezan!!', default);
RETURN OLD;
ELSE
RETURN NEW;
END IF;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER provjera_veze
BEFORE INSERT OR UPDATE
ON poruka
FOR EACH ROW
EXECUTE PROCEDURE jel_veza_prihvacena();
CREATE OR REPLACE FUNCTION novo_clanstvo()
RETURNS TRIGGER
AS $$
DECLARE prijatelj TEXT;
DECLARE naziv_grupe TEXT;
DECLARE sadrzaj_poruke TEXT;
DECLARE novi_clan osoba;
BEGIN
naziv_grupe := (
SELECT naziv
FROM grupa
WHERE sifra = NEW.grupa
);
novi_clan := (
SELECT osoba
FROM osoba
WHERE email = NEW.clan
);
sadrzaj_poruke := novi_clan.ime || ' '
|| novi_clan.prezime || ' je postao clan grupe '
|| naziv_grupe || '!';
FOR prijatelj IN SELECT DISTINCT prihvatio FROM
veza WHERE poslao = NEW.clan LOOP
PERFORM posalji_poruku( NEW.clan,
prijatelj,
'Novo clanstvo', sadrzaj_poruke );
END LOOP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER novi_clan_grupe
AFTER INSERT
ON clanstvo
FOR EACH ROW
EXECUTE PROCEDURE novo_clanstvo();
CREATE OR REPLACE FUNCTION provjera_teme()
RETURNS TRIGGER AS $$
DECLARE Postojiporuka BOOLEAN;
BEGIN
Postojiporuka:= EXISTS (SELECT * FROM poruka WHERE (new.posiljatelj = posiljatelj AND new.primatelj = primatelj AND new.naslov = naslov) OR (new.posiljatelj = primatelj AND new.primatelj = posiljatelj AND new.naslov = naslov));
IF Postojiporuka THEN
INSERT INTO poruka VALUES (default, new.posiljatelj, new.primatelj, 'Re: ' || new.naslov, new.sadrzaj, default);
RETURN OLD;
ELSE
RETURN NEW;
END IF;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER provjera_teme_okidac
BEFORE INSERT OR UPDATE
ON poruka
FOR EACH ROW
EXECUTE PROCEDURE provjera_teme();
CREATE TABLE fotoalbum(
sifra SERIAL PRIMARY KEY,
opis TEXT,
tip VARCHAR(3),
slika OID,
kljucne_rijeci VARCHAR(50)[5]
);
CREATE TEMP TABLE
evidencija_nastave(
student VARCHAR( 20 ),
dolasci BOOLEAN[]
);
CREATE OR REPLACE FUNCTION ima_kljucnu_rijec(kr VARCHAR)
RETURNS SETOF OID
AS
'SELECT slika FROM fotoalbum WHERE $1 = ANY(kljucne_rijeci)'
LANGUAGE SQL;
CREATE TYPE status AS ENUM (
'na vezi',
'nije na vezi',
'nepoznat'
);
CREATE TEMP TABLE korisnik(
kor_ime VARCHAR( 20 ),
stanje STATUS DEFAULT 'nepoznat'
);
INSERT INTO korisnik
VALUES ( 'Ivan', 'na vezi' );
INSERT INTO korisnik
VALUES ( 'Luka', 'nije na vezi' );
INSERT INTO korisnik
VALUES ( 'Luka' );
CREATE TYPE tip_adresa AS (
ulica TEXT,
broj INTEGER,
post_broj INTEGER,
grad TEXT,
drzava TEXT
);
CREATE TEMP TABLE kupac(
sifra SERIAL PRIMARY KEY,
ime TEXT,
prezime TEXT,
adresa tip_adresa
);
CREATE TABLE admin(
tel_br VARCHAR(10)
) INHERITS (osoba);
INSERT INTO admin( email, ime, prezime,
'Zovko', 'Kovac', '0984345561' );
CREATE TABLE moderator(
stanje STATUS DEFAULT 'nepoznat'
) INHERITS (osoba);
ALTER TABLE clanstvo
ADD COLUMN vrijedece_vrijeme TSRANGE
DEFAULT tsrange( NOW()::TIMESTAMP, 'infinity'::TIMESTAMP );
ALTER TABLE clanstvo
DROP CONSTRAINT clanstvo_pkey;
ALTER TABLE clanstvo
ADD CONSTRAINT clanstvo_pkey
PRIMARY KEY( clan, grupa, vrijedece_vrijeme );
CREATE OR REPLACE FUNCTION promjena_clanstva()
RETURNS TRIGGER
AS $$
BEGIN
UPDATE clanstvo
SET vrijedece_vrijeme = tsrange(
LOWER( vrijedece_vrijeme )::TIMESTAMP,
NOW()::TIMESTAMP )
WHERE OLD.clan = clan
AND OLD.grupa = grupa
AND UPPER( vrijedece_vrijeme ) = 'infinity'::TIMESTAMP;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER temp_clanstvo
BEFORE DELETE
ON clanstvo
FOR EACH ROW
EXECUTE PROCEDURE promjena_clanstva();
ALTER TABLE poruka
ADD COLUMN vrijedece_vrijeme TSRANGE
DEFAULT tsrange( NOW()::TIMESTAMP, 'infinity'::TIMESTAMP );
ALTER TABLE poruka
DROP CONSTRAINT poruka_pkey;
ALTER TABLE poruka
ADD CONSTRAINT poruka_pkey
PRIMARY KEY( sifra, vrijedece_vrijeme );
CREATE OR REPLACE FUNCTION promjena_poruke()
RETURNS TRIGGER
AS $$
BEGIN
UPDATE poruka
SET naslov = old.naslov || ' (obrisana poruka)',
vrijedece_vrijeme = tsrange(
LOWER( vrijedece_vrijeme )::TIMESTAMP,
NOW()::TIMESTAMP )
WHERE OLD.sifra = sifra
AND UPPER( vrijedece_vrijeme ) = 'infinity'::TIMESTAMP;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER temp_poruka
BEFORE DELETE
ON poruka
FOR EACH ROW
EXECUTE PROCEDURE promjena_poruke();
ALTER TABLE veza
ADD COLUMN vrijedece_vrijeme TSRANGE
DEFAULT tsrange( NOW()::TIMESTAMP, 'infinity'::TIMESTAMP );
ALTER TABLE veza
DROP CONSTRAINT veza_pkey;
ALTER TABLE veza
ADD CONSTRAINT veza_pkey
PRIMARY KEY( poslao, prihvatio, vrsta, vrijedece_vrijeme );
CREATE OR REPLACE FUNCTION promjena_veze()
RETURNS TRIGGER
AS $$
BEGIN
UPDATE veza
SET vrijedece_vrijeme = tsrange(
LOWER( vrijedece_vrijeme )::TIMESTAMP,
NOW()::TIMESTAMP )
WHERE OLD.poslao = poslao
AND OLD.prihvatio = prihvatio
AND OLD.vrsta = vrsta
AND UPPER( vrijedece_vrijeme ) = 'infinity'::TIMESTAMP;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER temp_veza
BEFORE DELETE
ON veza
FOR EACH ROW
EXECUTE PROCEDURE promjena_veze();
COMMIT;