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	mglavica	1270	drop view if exists parovi;
2			drop view if exists rpcou;
3			drop view if exists citingu;
4			drop view if exists citiraniu;
5	mglavica	1320	drop view if exists radoviu;
6	mglavica	1270
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	mglavica	1320	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	mglavica	1270
59	mglavica	1320	drop table if exists radoviau;
60			create table radoviau (
61			ut text,
62			au text
63			);
64
65	mglavica	1270	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	mglavica	1320	de text,
76			id text,
77	mglavica	1270	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	mglavica	1320	catc integer,
105			cr_full text,
106			isi text,
107			camb integer
108	mglavica	1270	);
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	mglavica	1320	create view radoviu as select distinct ut,pt,au,so,la,dt,nr,tc,pi,py,di,sc,rp from radovi ;
113
114	mglavica	1270	create view rpcou as select distinct * from rpco ;
115
116			create view parovi as select distinct citirani.ca,
117	mglavica	1320	citirani.cr_full as cr,
118	mglavica	1270	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	mglavica	1320	left join cited on citirani.cr_full = cited.cr_full
133	mglavica	1270	left join citingu on cited.ut = citingu.ut
134			left join rpcou on cited.ut = rpcou.ut
135			;
136
137	mglavica	1320	create view citiraniu as select distinct cr_auth,cr_ref,cr_year,cr_vol,cr_page,catc,cr_full,isi from citirani ;
138	mglavica	1270
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	mglavica	1274	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	mglavica	1270
166	mglavica	1274	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	mglavica	1270	-- 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