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	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	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
133	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	-- 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