conf/dipl/schema.sql

drop view if exists parovi;
drop view if exists rpcou;
drop view if exists citingu;
drop view if exists citiraniu;
drop view if exists radoviu;

drop table if exists utca;
create table utca (
        id serial,
        ut text,
        ca text
);

drop table if exists cited;
create table cited (
        id serial,
        ca text,
        cr_auth text,
        au text,
        ut text,
        cr_full text,
        cr_year text,
        cr_ref text, 
        cr_doi text
);

drop table if exists authors;
create table authors (
        id serial,
        ut text,
        au text,
        af text,
        ca text
);

drop table if exists radovi;
create table radovi (
        ca text,
        ut text,
        pt text,
        au text,
        af text,
        ti text,
        so text,
        la text,
        dt text,
        de text,
        id text,
        c1 text,
        rp text,
        nr integer,
        tc integer,
        pi text,
        py integer,
        di text,
        sc text
);

drop table if exists radoviau;
create table radoviau (
        ut text,
        au text
);

drop table if exists citing;
create table citing (
        ut text,
        pt text,
        au text,
        af text,
        ti text,
        so text,
        la text,
        dt text,
        de text,
        id text,
        c1 text,
        rp text,
        nr integer,
        tc integer,
        pi text,
        py integer,
        di text,
        sc text
);

drop table if exists rpco;
create table rpco (
        ut text,
        rp text,
        rpco text
);


drop table if exists citirani;
create table citirani (
        id serial,
        ca text,
        cr_auth text,
        cr_ref text,
        cr_year text,
        cr_vol text,
        cr_page text,
        catc integer,
        cr_full text,
        isi text,
        camb integer
);

create view citingu as select distinct ut,pt,au,so,la,dt,nr,tc,pi,py,di,sc,rp from citing ;

create view radoviu as select distinct ut,pt,au,so,la,dt,nr,tc,pi,py,di,sc,rp from radovi ;

create view rpcou as select distinct * from rpco ;

create view parovi as select distinct citirani.ca,
        citirani.cr_full as cr,
        cited.cr_full,
        cited.ut,
        citingu.pt,
        citingu.au,
        citingu.so,
        citingu.la,
        citingu.dt,
        citingu.nr,
        citingu.tc,
        citingu.pi,
        citingu.py,
        citingu.sc,
        citingu.rp
from citirani
left join cited on citirani.cr_full = cited.cr_full
left join citingu on cited.ut = citingu.ut
left join rpcou on cited.ut = rpcou.ut
;

create view citiraniu as select distinct cr_auth,cr_ref,cr_year,cr_vol,cr_page,catc,cr_full,isi from citirani ;


drop table if exists cropsy;
create table cropsy (
        id serial,
        ut text,
        au text,
        c1 text,
        rp text,
        tc integer,
        py integer
);

drop table if exists jcr;
create table jcr (
        id serial,
        abbt text,
        issn text,
        tc text,
        impf text,
        impf5 text,
        ii text,
        noart text,
        hlife text,
        eigen text,
        infl text
);      

drop table if exists izbori;
create table izbori (
        id serial,
        autor text,
        casopis text,
        godina text,
        sd numeric
);

-- CREATE AGGREGATE array_accum (anyelement)
-- (
--     sfunc = array_append,
--     stype = anyarray,
--     initcond = '{}'
-- );

-- select d, count(*),
--      array_to_string(array_accum('+'::text),'') as graph
--      from hits group by 1 order by 1 asc;


-- create index cited_au on cited(au);
-- create index cited_cited on cited(cited);

1	drop view if exists parovi;
2	drop view if exists rpcou;
3	drop view if exists citingu;
4	drop view if exists citiraniu;
5	drop view if exists radoviu;
6
7	drop table if exists utca;
8	create table utca (
9	id serial,
10	ut text,
11	ca text
12	);
13
14	drop table if exists cited;
15	create table cited (
16	id serial,
17	ca text,
18	cr_auth text,
19	au text,
20	ut text,
21	cr_full text,
22	cr_year text,
23	cr_ref text,
24	cr_doi text
25	);
26
27	drop table if exists authors;
28	create table authors (
29	id serial,
30	ut text,
31	au text,
32	af text,
33	ca text
34	);
35
36	drop table if exists radovi;
37	create table radovi (
38	ca text,
39	ut text,
40	pt text,
41	au text,
42	af text,
43	ti text,
44	so text,
45	la text,
46	dt text,
47	de text,
48	id text,
49	c1 text,
50	rp text,
51	nr integer,
52	tc integer,
53	pi text,
54	py integer,
55	di text,
56	sc text
57	);
58
59	drop table if exists radoviau;
60	create table radoviau (
61	ut text,
62	au text
63	);
64
65	drop table if exists citing;
66	create table citing (
67	ut text,
68	pt text,
69	au text,
70	af text,
71	ti text,
72	so text,
73	la text,
74	dt text,
75	de text,
76	id text,
77	c1 text,
78	rp text,
79	nr integer,
80	tc integer,
81	pi text,
82	py integer,
83	di text,
84	sc text
85	);
86
87	drop table if exists rpco;
88	create table rpco (
89	ut text,
90	rp text,
91	rpco text
92	);
93
94
95	drop table if exists citirani;
96	create table citirani (
97	id serial,
98	ca text,
99	cr_auth text,
100	cr_ref text,
101	cr_year text,
102	cr_vol text,
103	cr_page text,
104	catc integer,
105	cr_full text,
106	isi text,
107	camb integer
108	);
109
110	create view citingu as select distinct ut,pt,au,so,la,dt,nr,tc,pi,py,di,sc,rp from citing ;
111
112	create view radoviu as select distinct ut,pt,au,so,la,dt,nr,tc,pi,py,di,sc,rp from radovi ;
113
114	create view rpcou as select distinct * from rpco ;
115
116	create view parovi as select distinct citirani.ca,
117	citirani.cr_full as cr,
118	cited.cr_full,
119	cited.ut,
120	citingu.pt,
121	citingu.au,
122	citingu.so,
123	citingu.la,
124	citingu.dt,
125	citingu.nr,
126	citingu.tc,
127	citingu.pi,
128	citingu.py,
129	citingu.sc,
130	citingu.rp
131	from citirani
132	left join cited on citirani.cr_full = cited.cr_full
133	left join citingu on cited.ut = citingu.ut
134	left join rpcou on cited.ut = rpcou.ut
135	;
136
137	create view citiraniu as select distinct cr_auth,cr_ref,cr_year,cr_vol,cr_page,catc,cr_full,isi from citirani ;
138
139
140	drop table if exists cropsy;
141	create table cropsy (
142	id serial,
143	ut text,
144	au text,
145	c1 text,
146	rp text,
147	tc integer,
148	py integer
149	);
150
151	drop table if exists jcr;
152	create table jcr (
153	id serial,
154	abbt text,
155	issn text,
156	tc text,
157	impf text,
158	impf5 text,
159	ii text,
160	noart text,
161	hlife text,
162	eigen text,
163	infl text
164	);
165
166	drop table if exists izbori;
167	create table izbori (
168	id serial,
169	autor text,
170	casopis text,
171	godina text,
172	sd numeric
173	);
174
175	-- CREATE AGGREGATE array_accum (anyelement)
176	-- (
177	-- sfunc = array_append,
178	-- stype = anyarray,
179	-- initcond = '{}'
180	-- );
181
182	-- select d, count(*),
183	-- array_to_string(array_accum('+'::text),'') as graph
184	-- from hits group by 1 order by 1 asc;
185
186
187	-- create index cited_au on cited(au);
188	-- create index cited_cited on cited(cited);
189