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