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
);

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 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			can text
77			);
78
79			create view citingu as select distinct ut,pt,au,so,la,dt,nr,tc,pi,py,di,sc,rp from citing ;
80
81			create view rpcou as select distinct * from rpco ;
82
83			create view parovi as select distinct citirani.ca,
84			citirani.cr,
85			cited.cr_full,
86			cited.ut,
87			citingu.pt,
88			citingu.au,
89			citingu.so,
90			citingu.la,
91			citingu.dt,
92			citingu.nr,
93			citingu.tc,
94			citingu.pi,
95			citingu.py,
96			citingu.sc,
97			citingu.rp
98			from citirani
99			left join cited on citirani.cr = cited.cr_full
100			left join citingu on cited.ut = citingu.ut
101			left join rpcou on cited.ut = rpcou.ut
102			;
103
104			create view citiraniu as select distinct cr_auth,cr_ref,cr_year,cr_vol,cr_page,ttc,cr from citirani ;
105
106
107			drop table if exists cropsy;
108			create table cropsy (
109			id serial,
110			ut text,
111			au text,
112			c1 text,
113			rp text,
114			tc integer,
115			py integer
116			);
117
118	mglavica	1274	drop table if exists jcr;
119			create table jcr (
120			id serial,
121			abbt text,
122			issn text,
123			tc text,
124			impf text,
125			impf5 text,
126			ii text,
127			noart text,
128			hlife text,
129			eigen text,
130			infl text
131			);
132	mglavica	1270
133	mglavica	1274	drop table if exists izbori;
134			create table izbori (
135			id serial,
136			autor text,
137			casopis text,
138			godina text,
139			sd numeric
140			);
141
142	mglavica	1270	-- CREATE AGGREGATE array_accum (anyelement)
143			-- (
144			-- sfunc = array_append,
145			-- stype = anyarray,
146			-- initcond = '{}'
147			-- );
148
149			-- select d, count(*),
150			-- array_to_string(array_accum('+'::text),'') as graph
151			-- from hits group by 1 order by 1 asc;
152
153
154			-- create index cited_au on cited(au);
155			-- create index cited_cited on cited(cited);
156