Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --
- -- PostgreSQL database dump
- --
- -- Dumped from database version 9.6.6
- -- Dumped by pg_dump version 9.6.6
- SET statement_timeout = 0;
- SET lock_timeout = 0;
- SET idle_in_transaction_session_timeout = 0;
- SET client_encoding = 'UTF8';
- SET standard_conforming_strings = on;
- SET check_function_bodies = false;
- SET client_min_messages = warning;
- SET row_security = off;
- --
- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
- --
- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
- --
- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
- --
- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
- SET search_path = public, pg_catalog;
- --
- -- Name: inbox_popis; Type: TYPE; Schema: public; Owner: vjezbefoi
- --
- CREATE TYPE inbox_popis AS (
- ime text,
- prezime text,
- sifra integer,
- naslov text,
- vrijeme timestamp without time zone
- );
- ALTER TYPE inbox_popis OWNER TO vjezbefoi;
- --
- -- Name: inbox(text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION inbox(email text) RETURNS SETOF inbox_popis
- LANGUAGE sql
- 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$_$;
- ALTER FUNCTION public.inbox(email text) OWNER TO vjezbefoi;
- --
- -- Name: ispisi_iz_grupe(text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION ispisi_iz_grupe(email text, naziv_grupe text) RETURNS void
- LANGUAGE sql
- AS $_$DELETE FROM clanstvo WHERE clan = $1 and grupa = ( SELECT sifra FROM grupa WHERE naziv = $2 )$_$;
- ALTER FUNCTION public.ispisi_iz_grupe(email text, naziv_grupe text) OWNER TO vjezbefoi;
- --
- -- Name: moje_grupe(text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION moje_grupe(email text) RETURNS SETOF text
- LANGUAGE sql
- AS $_$SELECT naziv FROM grupa WHERE sifra IN ( SELECT grupa FROM clanstvo WHERE clan = $1 ) $_$;
- ALTER FUNCTION public.moje_grupe(email text) OWNER TO vjezbefoi;
- SET default_tablespace = '';
- SET default_with_oids = false;
- --
- -- Name: grupa; Type: TABLE; Schema: public; Owner: vjezbefoi
- --
- CREATE TABLE grupa (
- sifra integer NOT NULL,
- naziv character varying(40) NOT NULL
- );
- ALTER TABLE grupa OWNER TO vjezbefoi;
- --
- -- Name: moje_grupe_2(text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION moje_grupe_2(email text) RETURNS SETOF grupa
- LANGUAGE sql
- AS $_$SELECT * FROM grupa WHERE sifra IN ( SELECT grupa FROM clanstvo WHERE clan = $1 )$_$;
- ALTER FUNCTION public.moje_grupe_2(email text) OWNER TO vjezbefoi;
- --
- -- Name: nova_grupa(text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION nova_grupa(kreator text, naziv_grupe text) RETURNS void
- LANGUAGE plpgsql
- 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;$$;
- ALTER FUNCTION public.nova_grupa(kreator text, naziv_grupe text) OWNER TO vjezbefoi;
- --
- -- Name: nova_zamolba(); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION nova_zamolba() RETURNS trigger
- LANGUAGE plpgsql
- 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; $$;
- ALTER FUNCTION public.nova_zamolba() OWNER TO vjezbefoi;
- --
- -- Name: posalji_poruku(text, text, text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION posalji_poruku(naslov text, sadrzaj text, email_primatelja text, email_posiljatelja text) RETURNS void
- LANGUAGE sql
- AS $_$INSERT INTO poruka(naslov, sadrzaj, posiljatelj, primatelj) VALUES ($1, $2, $4, $3)$_$;
- ALTER FUNCTION public.posalji_poruku(naslov text, sadrzaj text, email_primatelja text, email_posiljatelja text) OWNER TO vjezbefoi;
- --
- -- Name: provjeri_email(); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION provjeri_email() RETURNS trigger
- LANGUAGE plpgsql
- AS $$BEGIN IF NEW.email LIKE '%@%' THEN RETURN NEW; ELSE RAISE EXCEPTION '%', 'E-mail adresa ne sadrzi znak ''@'''; END IF; END;$$;
- ALTER FUNCTION public.provjeri_email() OWNER TO vjezbefoi;
- --
- -- Name: upisi_u_grupu(text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION upisi_u_grupu(email text, naziv_grupe text) RETURNS void
- LANGUAGE sql
- AS $_$INSERT INTO clanstvo SELECT $1, sifra FROM grupa WHERE naziv = $2$_$;
- ALTER FUNCTION public.upisi_u_grupu(email text, naziv_grupe text) OWNER TO vjezbefoi;
- --
- -- Name: zamolba(text, text, text); Type: FUNCTION; Schema: public; Owner: vjezbefoi
- --
- CREATE FUNCTION zamolba(posiljatelj text, primatelj text, naziv_vrste text) RETURNS void
- LANGUAGE plpgsql
- 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; $$;
- ALTER FUNCTION public.zamolba(posiljatelj text, primatelj text, naziv_vrste text) OWNER TO vjezbefoi;
- --
- -- Name: clanstvo; Type: TABLE; Schema: public; Owner: vjezbefoi
- --
- CREATE TABLE clanstvo (
- clan text NOT NULL,
- grupa integer NOT NULL
- );
- ALTER TABLE clanstvo OWNER TO vjezbefoi;
- --
- -- Name: grupa_sifra_seq; Type: SEQUENCE; Schema: public; Owner: vjezbefoi
- --
- CREATE SEQUENCE grupa_sifra_seq
- START WITH 1
- INCREMENT BY 1
- NO MINVALUE
- NO MAXVALUE
- CACHE 1;
- ALTER TABLE grupa_sifra_seq OWNER TO vjezbefoi;
- --
- -- Name: grupa_sifra_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: vjezbefoi
- --
- ALTER SEQUENCE grupa_sifra_seq OWNED BY grupa.sifra;
- --
- -- Name: osoba; Type: TABLE; Schema: public; Owner: vjezbefoi
- --
- CREATE TABLE osoba (
- email text NOT NULL,
- ime text NOT NULL,
- prezime text NOT NULL,
- drzava text,
- grad text,
- spol character varying(6),
- CONSTRAINT osoba_spol_check CHECK ((((spol)::text = 'musko'::text) OR ((spol)::text = 'zensko'::text)))
- );
- ALTER TABLE osoba OWNER TO vjezbefoi;
- --
- -- Name: poruka; Type: TABLE; Schema: public; Owner: vjezbefoi
- --
- CREATE TABLE poruka (
- sifra integer NOT NULL,
- posiljatelj text,
- primatelj text,
- naslov text,
- sadrzaj text,
- vrijeme timestamp without time zone DEFAULT now()
- );
- ALTER TABLE poruka OWNER TO vjezbefoi;
- --
- -- Name: poruka_sifra_seq; Type: SEQUENCE; Schema: public; Owner: vjezbefoi
- --
- CREATE SEQUENCE poruka_sifra_seq
- START WITH 1
- INCREMENT BY 1
- NO MINVALUE
- NO MAXVALUE
- CACHE 1;
- ALTER TABLE poruka_sifra_seq OWNER TO vjezbefoi;
- --
- -- Name: poruka_sifra_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: vjezbefoi
- --
- ALTER SEQUENCE poruka_sifra_seq OWNED BY poruka.sifra;
- --
- -- Name: veza; Type: TABLE; Schema: public; Owner: vjezbefoi
- --
- CREATE TABLE veza (
- poslao text NOT NULL,
- prihvatio text NOT NULL,
- vrsta integer NOT NULL,
- status character varying(10),
- CONSTRAINT veza_status_check CHECK ((((status)::text = 'poslano'::text) OR ((status)::text = 'prihvaceno'::text)))
- );
- ALTER TABLE veza OWNER TO vjezbefoi;
- --
- -- Name: vrsta_veze; Type: TABLE; Schema: public; Owner: vjezbefoi
- --
- CREATE TABLE vrsta_veze (
- sifra integer NOT NULL,
- naziv character varying NOT NULL
- );
- ALTER TABLE vrsta_veze OWNER TO vjezbefoi;
- --
- -- Name: vrsta_veze_sifra_seq; Type: SEQUENCE; Schema: public; Owner: vjezbefoi
- --
- CREATE SEQUENCE vrsta_veze_sifra_seq
- START WITH 1
- INCREMENT BY 1
- NO MINVALUE
- NO MAXVALUE
- CACHE 1;
- ALTER TABLE vrsta_veze_sifra_seq OWNER TO vjezbefoi;
- --
- -- Name: vrsta_veze_sifra_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: vjezbefoi
- --
- ALTER SEQUENCE vrsta_veze_sifra_seq OWNED BY vrsta_veze.sifra;
- --
- -- Name: grupa sifra; Type: DEFAULT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY grupa ALTER COLUMN sifra SET DEFAULT nextval('grupa_sifra_seq'::regclass);
- --
- -- Name: poruka sifra; Type: DEFAULT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY poruka ALTER COLUMN sifra SET DEFAULT nextval('poruka_sifra_seq'::regclass);
- --
- -- Name: vrsta_veze sifra; Type: DEFAULT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY vrsta_veze ALTER COLUMN sifra SET DEFAULT nextval('vrsta_veze_sifra_seq'::regclass);
- --
- -- Data for Name: clanstvo; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
- --
- COPY clanstvo (clan, grupa) FROM stdin;
- markus.schatten@foi.hr 1
- markus.schatten@foi.hr 3
- bara@gmail.com 2
- mirko.malekovic@foi.hr 4
- markus.schatten@foi.hr 6
- \.
- --
- -- Data for Name: grupa; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
- --
- COPY grupa (sifra, naziv) FROM stdin;
- 1 nastavnici
- 2 programiranje
- 3 linux
- 4 baze podataka
- 6 test
- \.
- --
- -- Name: grupa_sifra_seq; Type: SEQUENCE SET; Schema: public; Owner: vjezbefoi
- --
- SELECT pg_catalog.setval('grupa_sifra_seq', 6, true);
- --
- -- Data for Name: osoba; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
- --
- COPY osoba (email, ime, prezime, drzava, grad, spol) FROM stdin;
- markus.schatten@foi.hr Markus Schatten Hrvatska Varaždin musko
- mirko.malekovic@foi.hr Mirko Maleković Hrvatska Zagreb musko
- bara@gmail.com Barica Prikratki Hrvatska Varaždin zensko
- joza@gmail.com Joža Presvetli Hrvatska Varaždin musko
- \.
- --
- -- Data for Name: poruka; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
- --
- COPY poruka (sifra, posiljatelj, primatelj, naslov, sadrzaj, vrijeme) FROM stdin;
- 1 mirko.malekovic@foi.hr markus.schatten@foi.hr Pozdrav Kako ste? 2018-10-19 17:18:30.860893
- 3 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:31.269032
- 4 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:44.515735
- 5 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:45.916185
- 6 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:46.091819
- 7 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:46.235783
- 8 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:46.371803
- 9 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:30:46.523966
- 10 joza@gmail.com bara@gmail.com Moj naslov Moj sadrzaj 2018-11-02 19:31:37.97974
- \.
- --
- -- Name: poruka_sifra_seq; Type: SEQUENCE SET; Schema: public; Owner: vjezbefoi
- --
- SELECT pg_catalog.setval('poruka_sifra_seq', 10, true);
- --
- -- Data for Name: veza; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
- --
- COPY veza (poslao, prihvatio, vrsta, status) FROM stdin;
- mirko.malekovic@foi.hr markus.schatten@foi.hr 4 prihvaceno
- bara@gmail.com joza@gmail.com 3 poslano
- markus.schatten@foi.hr mirko.malekovic@foi.hr 4 poslano
- \.
- --
- -- Data for Name: vrsta_veze; Type: TABLE DATA; Schema: public; Owner: vjezbefoi
- --
- COPY vrsta_veze (sifra, naziv) FROM stdin;
- 1 prijateljstvo
- 2 rodbina
- 3 ljubavna veza
- 4 suradnik
- \.
- --
- -- Name: vrsta_veze_sifra_seq; Type: SEQUENCE SET; Schema: public; Owner: vjezbefoi
- --
- SELECT pg_catalog.setval('vrsta_veze_sifra_seq', 4, true);
- --
- -- Name: clanstvo clanstvo_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY clanstvo
- ADD CONSTRAINT clanstvo_pkey PRIMARY KEY (clan, grupa);
- --
- -- Name: grupa grupa_naziv_key; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY grupa
- ADD CONSTRAINT grupa_naziv_key UNIQUE (naziv);
- --
- -- Name: grupa grupa_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY grupa
- ADD CONSTRAINT grupa_pkey PRIMARY KEY (sifra);
- --
- -- Name: osoba osoba_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY osoba
- ADD CONSTRAINT osoba_pkey PRIMARY KEY (email);
- --
- -- Name: poruka poruka_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY poruka
- ADD CONSTRAINT poruka_pkey PRIMARY KEY (sifra);
- --
- -- Name: veza veza_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY veza
- ADD CONSTRAINT veza_pkey PRIMARY KEY (poslao, prihvatio, vrsta);
- --
- -- Name: vrsta_veze vrsta_veze_naziv_key; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY vrsta_veze
- ADD CONSTRAINT vrsta_veze_naziv_key UNIQUE (naziv);
- --
- -- Name: vrsta_veze vrsta_veze_pkey; Type: CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY vrsta_veze
- ADD CONSTRAINT vrsta_veze_pkey PRIMARY KEY (sifra);
- --
- -- Name: osoba email_provjera; Type: TRIGGER; Schema: public; Owner: vjezbefoi
- --
- CREATE TRIGGER email_provjera BEFORE INSERT OR UPDATE ON osoba FOR EACH ROW EXECUTE PROCEDURE provjeri_email();
- --
- -- Name: veza poruka_uz_zamolbu; Type: TRIGGER; Schema: public; Owner: vjezbefoi
- --
- CREATE TRIGGER poruka_uz_zamolbu BEFORE INSERT ON veza FOR EACH ROW EXECUTE PROCEDURE nova_zamolba();
- --
- -- Name: clanstvo clanstvo_clan_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY clanstvo
- ADD CONSTRAINT clanstvo_clan_fkey FOREIGN KEY (clan) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
- --
- -- Name: clanstvo clanstvo_grupa_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY clanstvo
- ADD CONSTRAINT clanstvo_grupa_fkey FOREIGN KEY (grupa) REFERENCES grupa(sifra) ON UPDATE CASCADE ON DELETE RESTRICT;
- --
- -- Name: poruka poruka_posiljatelj_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY poruka
- ADD CONSTRAINT poruka_posiljatelj_fkey FOREIGN KEY (posiljatelj) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
- --
- -- Name: poruka poruka_primatelj_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY poruka
- ADD CONSTRAINT poruka_primatelj_fkey FOREIGN KEY (primatelj) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
- --
- -- Name: veza veza_poslao_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY veza
- ADD CONSTRAINT veza_poslao_fkey FOREIGN KEY (poslao) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
- --
- -- Name: veza veza_prihvatio_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY veza
- ADD CONSTRAINT veza_prihvatio_fkey FOREIGN KEY (prihvatio) REFERENCES osoba(email) ON UPDATE CASCADE ON DELETE RESTRICT;
- --
- -- Name: veza veza_vrsta_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vjezbefoi
- --
- ALTER TABLE ONLY veza
- ADD CONSTRAINT veza_vrsta_fkey FOREIGN KEY (vrsta) REFERENCES vrsta_veze(sifra) ON UPDATE CASCADE ON DELETE RESTRICT;
- --
- -- PostgreSQL database dump complete
- --
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement