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 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 citing;
create table citing (
        id serial,
        ut text,
        pt text,
        au text,
        af text,
        ti text,
        so text,
        la text,
        dt 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,
        ttc integer,
        cr text,
        can text,
        isi text
);

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

create view rpcou as select distinct * from rpco ;

create view parovi as select distinct citirani.ca,
        citirani.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 = 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,ttc,cr,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
6			drop table if exists utca;
7			create table utca (
8			id serial,
9			ut text,
10			ca text
11			);
12
13			drop table if exists cited;
14			create table cited (
15			id serial,
16			ca text,
17			cr_auth text,
18			au text,
19			ut text,
20			cr_full text,
21			cr_year text,
22			cr_ref text,
23			cr_doi text
24			);
25
26			drop table if exists authors;
27			create table authors (
28			id serial,
29			ut text,
30			au text,
31			af text,
32			ca text
33			);
34
35
36			drop table if exists citing;
37			create table citing (
38			id serial,
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			c1 text,
48			rp text,
49			nr integer,
50			tc integer,
51			pi text,
52			py integer,
53			di text,
54			sc text
55			);
56
57			drop table if exists rpco;
58			create table rpco (
59			ut text,
60			rp text,
61			rpco text
62			);
63
64
65			drop table if exists citirani;
66			create table citirani (
67			id serial,
68			ca text,
69			cr_auth text,
70			cr_ref text,
71			cr_year text,
72			cr_vol text,
73			cr_page text,
74			ttc integer,
75			cr text,
76	mglavica	1279	can text,
77			isi text
78	mglavica	1270	);
79
80			create view citingu as select distinct ut,pt,au,so,la,dt,nr,tc,pi,py,di,sc,rp from citing ;
81
82			create view rpcou as select distinct * from rpco ;
83
84			create view parovi as select distinct citirani.ca,
85			citirani.cr,
86			cited.cr_full,
87			cited.ut,
88			citingu.pt,
89			citingu.au,
90			citingu.so,
91			citingu.la,
92			citingu.dt,
93			citingu.nr,
94			citingu.tc,
95			citingu.pi,
96			citingu.py,
97			citingu.sc,
98			citingu.rp
99			from citirani
100			left join cited on citirani.cr = cited.cr_full
101			left join citingu on cited.ut = citingu.ut
102			left join rpcou on cited.ut = rpcou.ut
103			;
104
105	mglavica	1279	create view citiraniu as select distinct cr_auth,cr_ref,cr_year,cr_vol,cr_page,ttc,cr,isi from citirani ;
106	mglavica	1270
107
108			drop table if exists cropsy;
109			create table cropsy (
110			id serial,
111			ut text,
112			au text,
113			c1 text,
114			rp text,
115			tc integer,
116			py integer
117			);
118
119	mglavica	1274	drop table if exists jcr;
120			create table jcr (
121			id serial,
122			abbt text,
123			issn text,
124			tc text,
125			impf text,
126			impf5 text,
127			ii text,
128			noart text,
129			hlife text,
130			eigen text,
131			infl text
132			);
133	mglavica	1270
134	mglavica	1274	drop table if exists izbori;
135			create table izbori (
136			id serial,
137			autor text,
138			casopis text,
139			godina text,
140			sd numeric
141			);
142
143	mglavica	1270	-- CREATE AGGREGATE array_accum (anyelement)
144			-- (
145			-- sfunc = array_append,
146			-- stype = anyarray,
147			-- initcond = '{}'
148			-- );
149
150			-- select d, count(*),
151			-- array_to_string(array_accum('+'::text),'') as graph
152			-- from hits group by 1 order by 1 asc;
153
154
155			-- create index cited_au on cited(au);
156			-- create index cited_cited on cited(cited);
157