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


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