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	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	isi text
78	);
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	create view citiraniu as select distinct cr_auth,cr_ref,cr_year,cr_vol,cr_page,ttc,cr,isi from citirani ;
106
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	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
134	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	-- 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