conf/isi/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
);


-- 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
119	-- CREATE AGGREGATE array_accum (anyelement)
120	-- (
121	-- sfunc = array_append,
122	-- stype = anyarray,
123	-- initcond = '{}'
124	-- );
125
126	-- select d, count(*),
127	-- array_to_string(array_accum('+'::text),'') as graph
128	-- from hits group by 1 order by 1 asc;
129
130
131	-- create index cited_au on cited(au);
132	-- create index cited_cited on cited(cited);
133