|
1
|
-- query performed by IIS to bulk load project details
|
|
2
|
DROP TABLE project_details;
|
|
3
|
CREATE TABLE project_details AS (
|
|
4
|
SELECT
|
|
5
|
p.id AS projectid,
|
|
6
|
p.acronym AS acronym,
|
|
7
|
p.code AS code,
|
|
8
|
p.optional1 AS optional1,
|
|
9
|
p.optional2 AS optional2,
|
|
10
|
p.jsonextrainfo AS jsonextrainfo,
|
|
11
|
array_agg(fp.path) AS fundingpath
|
|
12
|
FROM projects p
|
|
13
|
LEFT OUTER JOIN project_fundingpath pf ON (pf.project = p.id)
|
|
14
|
LEFT OUTER JOIN fundingpaths fp ON (fp.id = pf.funding)
|
|
15
|
GROUP BY p.id
|
|
16
|
);
|
|
17
|
|
|
18
|
GRANT SELECT ON project_details TO dnetapi;
|
|
19
|
|
|
20
|
|
|
21
|
-- used to serve queries from api tsv exporter
|
|
22
|
DROP TABLE projects_tsv;
|
|
23
|
CREATE TABLE projects_tsv AS (
|
|
24
|
SELECT
|
|
25
|
nextval('projects_tsv_ids') AS rowid,
|
|
26
|
p.code,
|
|
27
|
p.acronym,
|
|
28
|
p.title,
|
|
29
|
p.call_identifier,
|
|
30
|
p.startdate,
|
|
31
|
p.enddate,
|
|
32
|
p.ec_sc39,
|
|
33
|
p.oa_mandate_for_publications,
|
|
34
|
p.ec_article29_3 AS oa_mandate_for_datasets,
|
|
35
|
p.contactfullname AS contactfullname,
|
|
36
|
p.contactemail AS contactemail,
|
|
37
|
org.legalname AS legalname,
|
|
38
|
org.countryclass AS country,
|
|
39
|
(array_agg(f.id))[1] AS fundingpathid,
|
|
40
|
(array_agg(f.description))[1] AS description,
|
|
41
|
(array_agg(CASE WHEN po.participantnumber = 1
|
|
42
|
THEN 'coordinator'
|
|
43
|
ELSE '' END))[1] AS role
|
|
44
|
|
|
45
|
FROM projects p
|
|
46
|
LEFT OUTER JOIN project_organization po ON (p.id = po.project)
|
|
47
|
LEFT OUTER JOIN organizations org ON (po.resporganization = org.id)
|
|
48
|
LEFT OUTER JOIN project_fundingpath pf ON (p.id = pf.project)
|
|
49
|
LEFT OUTER JOIN fundingpaths f ON (pf.funding = f.id)
|
|
50
|
GROUP BY
|
|
51
|
p.code,
|
|
52
|
p.acronym,
|
|
53
|
p.title,
|
|
54
|
p.call_identifier,
|
|
55
|
p.startdate,
|
|
56
|
p.enddate,
|
|
57
|
p.ec_sc39,
|
|
58
|
p.oa_mandate_for_publications,
|
|
59
|
p.ec_article29_3,
|
|
60
|
p.contactfullname,
|
|
61
|
p.contactemail,
|
|
62
|
org.legalname,
|
|
63
|
org.countryclass
|
|
64
|
ORDER BY p.acronym
|
|
65
|
);
|
|
66
|
|
|
67
|
CREATE INDEX projects_tsv_fundingpathid_idx ON projects_tsv (fundingpathid);
|
|
68
|
CREATE INDEX projects_tsv_oa_mandate_for_datasets_idx ON projects_tsv (oa_mandate_for_datasets);
|
|
69
|
GRANT SELECT ON projects_tsv TO dnetapi;
|
|
70
|
|
|
71
|
-- used to serve queries from dspace and eprints exporter
|
|
72
|
DROP TABLE projects_api;
|
|
73
|
CREATE TABLE projects_api AS (
|
|
74
|
SELECT
|
|
75
|
p.id,
|
|
76
|
p.acronym AS acronym,
|
|
77
|
p.title AS title,
|
|
78
|
p.code AS code,
|
|
79
|
p.startdate AS startdate,
|
|
80
|
p.enddate AS enddate,
|
|
81
|
(array_agg(org.legalshortname))[1] AS funder,
|
|
82
|
(array_agg(fp.jurisdiction))[1] AS jurisdiction,
|
|
83
|
(array_agg(fp.id))[1] AS fundingpathid
|
|
84
|
FROM projects p
|
|
85
|
LEFT OUTER JOIN project_fundingpath pfp ON (p.id = pfp.project)
|
|
86
|
LEFT OUTER JOIN fundingpaths fp ON (pfp.funding = fp.id)
|
|
87
|
LEFT OUTER JOIN organizations org ON (org.id = fp.funder)
|
|
88
|
GROUP BY p.id);
|
|
89
|
|
|
90
|
CREATE INDEX projects_api_fundingpathid_idx ON projects_api (fundingpathid);
|
|
91
|
CREATE INDEX projects_api_startdate_idx ON projects_api (startdate);
|
|
92
|
CREATE INDEX projects_api_enddate_idx ON projects_api (enddate);
|
|
93
|
GRANT SELECT ON projects_api TO dnetapi;
|
|
94
|
|
|
95
|
-- used to serve queries from the repo admin dashboard
|
|
96
|
DROP MATERIALIZED VIEW dashboard_ds;
|
|
97
|
CREATE MATERIALIZED VIEW dashboard_ds AS (
|
|
98
|
SELECT
|
|
99
|
ds.id,
|
|
100
|
ds.officialname,
|
|
101
|
ds.englishname,
|
|
102
|
ds.websiteurl,
|
|
103
|
ds.logourl,
|
|
104
|
ds.contactemail,
|
|
105
|
ds.latitude,
|
|
106
|
ds.longitude,
|
|
107
|
ds.timezone,
|
|
108
|
ds.namespaceprefix,
|
|
109
|
ds.collectedfrom,
|
|
110
|
ds.dateofvalidation,
|
|
111
|
ds.registeredby,
|
|
112
|
ds.datasourceclass,
|
|
113
|
ds.provenanceactionclass,
|
|
114
|
ds.dateofcollection,
|
|
115
|
ds.typology,
|
|
116
|
ds.activationid,
|
|
117
|
ds.description,
|
|
118
|
ds.missionstatementurl,
|
|
119
|
ds.aggregator,
|
|
120
|
ds.issn,
|
|
121
|
ds.eissn,
|
|
122
|
ds.lissn,
|
|
123
|
(array_agg(o.legalname)) [1] AS organization,
|
|
124
|
(array_agg(ccl.code)) [1] AS countrycode,
|
|
125
|
(array_agg(ccl.name)) [1] AS countryname,
|
|
126
|
array_agg(DISTINCT ag.accessinfopackage) AS accessinfopackage,
|
|
127
|
to_char(now(), 'YYYY-MM-DDThh24:mi:ssZ') AS now
|
|
128
|
|
|
129
|
FROM datasources ds
|
|
130
|
LEFT OUTER JOIN datasource_organization dso ON ds.id = dso.datasource
|
|
131
|
LEFT OUTER JOIN organizations o ON dso.organization = o.id
|
|
132
|
LEFT OUTER JOIN class ccl ON ccl.code = o.countryclass
|
|
133
|
LEFT OUTER JOIN (
|
|
134
|
SELECT
|
|
135
|
api.datasource,
|
|
136
|
api.contentdescriptionclass,
|
|
137
|
api.compatibilityclass,
|
|
138
|
COALESCE(api.id, '') || '<==1==>' ||
|
|
139
|
COALESCE(api.typologyclass, '') || '<==2==>' ||
|
|
140
|
COALESCE(api.compatibilityclass, '') || '<==3==>' ||
|
|
141
|
COALESCE(api.contentdescriptionclass, '') || '<==4==>' ||
|
|
142
|
COALESCE(api.protocolclass, '') || '<==5==>' ||
|
|
143
|
COALESCE(api.active, FALSE) || '<==6==>' ||
|
|
144
|
COALESCE(api.removable, FALSE) || '<==7==>' ||
|
|
145
|
array_to_string(
|
|
146
|
array_agg(DISTINCT
|
|
147
|
COALESCE(ac.accessparam, TRUE) || '###' ||
|
|
148
|
COALESCE(ac.param, '') || '###' ||
|
|
149
|
COALESCE(ac.value, '') || '###'), '@@@') AS accessinfopackage
|
|
150
|
FROM api
|
|
151
|
LEFT OUTER JOIN (
|
|
152
|
SELECT
|
|
153
|
api,
|
|
154
|
param,
|
|
155
|
accessparam,
|
|
156
|
CASE WHEN edited IS NULL
|
|
157
|
THEN original
|
|
158
|
ELSE edited END AS value
|
|
159
|
FROM apicollections
|
|
160
|
) AS ac ON (ac.api = api.id)
|
|
161
|
GROUP BY
|
|
162
|
api.id,
|
|
163
|
api.datasource,
|
|
164
|
api.contentdescriptionclass,
|
|
165
|
api.typologyclass,
|
|
166
|
api.compatibilityclass,
|
|
167
|
api.protocolclass,
|
|
168
|
api.active,
|
|
169
|
api.removable
|
|
170
|
) AS ag ON (ag.datasource = ds.id)
|
|
171
|
GROUP BY
|
|
172
|
ds.id,
|
|
173
|
ds.officialname,
|
|
174
|
ds.englishname,
|
|
175
|
ds.websiteurl,
|
|
176
|
ds.logourl,
|
|
177
|
ds.contactemail,
|
|
178
|
ds.latitude,
|
|
179
|
ds.longitude,
|
|
180
|
ds.timezone,
|
|
181
|
ds.namespaceprefix,
|
|
182
|
ds.collectedfrom,
|
|
183
|
ds.dateofvalidation,
|
|
184
|
ds.registeredby,
|
|
185
|
ds.datasourceclass,
|
|
186
|
ds.provenanceactionclass,
|
|
187
|
ds.dateofcollection,
|
|
188
|
ds.typology,
|
|
189
|
ds.activationid,
|
|
190
|
ds.description,
|
|
191
|
ds.aggregator,
|
|
192
|
ds.issn,
|
|
193
|
ds.eissn,
|
|
194
|
ds.lissn
|
|
195
|
);
|
|
196
|
CREATE INDEX dashboard_ds_id_idx ON dashboard_ds (id);
|
|
197
|
|
|
198
|
DROP MATERIALIZED VIEW projects_mv;
|
|
199
|
CREATE MATERIALIZED VIEW projects_mv AS (
|
|
200
|
SELECT
|
|
201
|
p.id AS projectid,
|
|
202
|
p.code AS code,
|
|
203
|
p.websiteurl AS websiteurl,
|
|
204
|
p.acronym AS acronym,
|
|
205
|
p.title AS title,
|
|
206
|
p.startdate AS startdate,
|
|
207
|
p.enddate AS enddate,
|
|
208
|
p.call_identifier AS callidentifier,
|
|
209
|
p.keywords AS keywords,
|
|
210
|
p.duration AS duration,
|
|
211
|
p.ec_sc39 AS ecsc39,
|
|
212
|
p.oa_mandate_for_publications AS oamandatepublications,
|
|
213
|
p.ec_article29_3 AS ecarticle29_3,
|
|
214
|
p.dateofcollection AS dateofcollection,
|
|
215
|
p.lastupdate AS dateoftransformation,
|
|
216
|
p.inferred AS inferred,
|
|
217
|
p.deletedbyinference AS deletedbyinference,
|
|
218
|
p.trust AS trust,
|
|
219
|
p.inferenceprovenance AS inferenceprovenance,
|
|
220
|
p.optional1 AS optional1,
|
|
221
|
p.optional2 AS optional2,
|
|
222
|
p.jsonextrainfo AS jsonextrainfo,
|
|
223
|
p.contactfullname AS contactfullname,
|
|
224
|
p.contactfax AS contactfax,
|
|
225
|
p.contactphone AS contactphone,
|
|
226
|
p.contactemail AS contactemail,
|
|
227
|
dc.id AS collectedfromid,
|
|
228
|
dc.officialname AS collectedfromname,
|
|
229
|
cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name AS contracttype,
|
|
230
|
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction,
|
|
231
|
array_agg(DISTINCT i.pid || '###' || i.issuertypeclass) AS pid,
|
|
232
|
array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) AS subjects,
|
|
233
|
array_agg(DISTINCT fp.path) AS fundingtree
|
|
234
|
FROM projects p
|
|
235
|
LEFT OUTER JOIN class cc ON (cc.code = p.contracttypeclass)
|
|
236
|
LEFT OUTER JOIN scheme cs ON (cs.code = p.contracttypescheme)
|
|
237
|
|
|
238
|
LEFT OUTER JOIN class pac ON (pac.code = p.provenanceactionclass)
|
|
239
|
LEFT OUTER JOIN scheme pas ON (pas.code = p.provenanceactionscheme)
|
|
240
|
|
|
241
|
LEFT OUTER JOIN projectpids pp ON (pp.project = p.id)
|
|
242
|
LEFT OUTER JOIN identities i ON (i.pid = pp.pid)
|
|
243
|
|
|
244
|
LEFT OUTER JOIN dsm_datasources dc ON (dc.id = p.collectedfrom)
|
|
245
|
|
|
246
|
LEFT OUTER JOIN project_fundingpath pf ON (pf.project = p.id)
|
|
247
|
LEFT OUTER JOIN fundingpaths fp ON (fp.id = pf.funding)
|
|
248
|
|
|
249
|
LEFT OUTER JOIN project_subject ps ON (ps.project = p.id)
|
|
250
|
LEFT OUTER JOIN subjects s ON (s.id = ps.subject)
|
|
251
|
|
|
252
|
LEFT OUTER JOIN class sc ON (sc.code = s.semanticclass)
|
|
253
|
LEFT OUTER JOIN scheme ss ON (ss.code = s.semanticscheme)
|
|
254
|
|
|
255
|
GROUP BY
|
|
256
|
p.id,
|
|
257
|
p.code,
|
|
258
|
p.websiteurl,
|
|
259
|
p.acronym,
|
|
260
|
p.title,
|
|
261
|
p.startdate,
|
|
262
|
p.enddate,
|
|
263
|
p.call_identifier,
|
|
264
|
p.keywords,
|
|
265
|
p.duration,
|
|
266
|
p.ec_sc39,
|
|
267
|
p.oa_mandate_for_publications,
|
|
268
|
p.ec_article29_3,
|
|
269
|
p.dateofcollection,
|
|
270
|
p.inferred,
|
|
271
|
p.deletedbyinference,
|
|
272
|
p.trust,
|
|
273
|
p.inferenceprovenance,
|
|
274
|
p.contactfullname,
|
|
275
|
p.contactfax,
|
|
276
|
p.contactphone,
|
|
277
|
p.contactemail,
|
|
278
|
dc.id,
|
|
279
|
dc.officialname,
|
|
280
|
cc.code, cc.name, cs.code, cs.name,
|
|
281
|
pac.code, pac.name, pas.code, pas.name
|
|
282
|
);
|
|
283
|
CREATE INDEX projects_mv_id_idx ON projects_mv (projectid);
|
|
284
|
|
|
285
|
GRANT SELECT ON dashboard_ds TO dnetapi;
|
|
286
|
GRANT SELECT ON fundingpaths TO dnetapi;
|
|
287
|
|
|
288
|
-- used to query from dsm_organizations UNION organizations when loading data to HBase
|
|
289
|
CREATE OR REPLACE VIEW orgs AS (
|
|
290
|
SELECT
|
|
291
|
o.id AS organizationid,
|
|
292
|
o.legalshortname AS legalshortname,
|
|
293
|
o.legalname AS legalname,
|
|
294
|
o.websiteurl AS websiteurl,
|
|
295
|
o.logourl AS logourl,
|
|
296
|
o.ec_legalbody AS eclegalbody,
|
|
297
|
o.ec_legalperson AS eclegalperson,
|
|
298
|
o.ec_nonprofit AS ecnonprofit,
|
|
299
|
o.ec_researchorganization AS ecresearchorganization,
|
|
300
|
o.ec_highereducation AS echighereducation,
|
|
301
|
o.ec_internationalorganizationeurinterests AS ecinternationalorganizationeurinterests,
|
|
302
|
o.ec_internationalorganization AS ecinternationalorganization,
|
|
303
|
o.ec_enterprise AS ecenterprise,
|
|
304
|
o.ec_smevalidated AS ecsmevalidated,
|
|
305
|
o.ec_nutscode AS ecnutscode,
|
|
306
|
o.dateofcollection AS dateofcollection,
|
|
307
|
o.inferred AS inferred,
|
|
308
|
o.deletedbyinference AS deletedbyinference,
|
|
309
|
o.trust AS trust,
|
|
310
|
o.inferenceprovenance AS inferenceprovenance,
|
|
311
|
dc.id AS collectedfromid,
|
|
312
|
dc.officialname AS collectedfromname,
|
|
313
|
|
|
314
|
cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name AS country,
|
|
315
|
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction,
|
|
316
|
|
|
317
|
array_agg(DISTINCT i.pid || '###' || i.issuertypeclass) AS pid
|
|
318
|
|
|
319
|
FROM organizations o
|
|
320
|
LEFT OUTER JOIN class cc ON (cc.code = o.countryclass)
|
|
321
|
LEFT OUTER JOIN scheme cs ON (cs.code = o.countryscheme)
|
|
322
|
|
|
323
|
LEFT OUTER JOIN class pac ON (pac.code = o.provenanceactionclass)
|
|
324
|
LEFT OUTER JOIN scheme pas ON (pas.code = o.provenanceactionscheme)
|
|
325
|
|
|
326
|
LEFT OUTER JOIN organizationpids op ON (op.organization = o.id)
|
|
327
|
LEFT OUTER JOIN identities i ON (i.pid = op.pid)
|
|
328
|
|
|
329
|
LEFT OUTER JOIN datasources dc ON (dc.id = o.collectedfrom)
|
|
330
|
|
|
331
|
GROUP BY
|
|
332
|
o.id,
|
|
333
|
o.legalshortname,
|
|
334
|
o.legalname,
|
|
335
|
o.websiteurl,
|
|
336
|
o.logourl,
|
|
337
|
o.ec_legalbody,
|
|
338
|
o.ec_legalperson,
|
|
339
|
o.ec_nonprofit,
|
|
340
|
o.ec_researchorganization,
|
|
341
|
o.ec_highereducation,
|
|
342
|
o.ec_internationalorganizationeurinterests,
|
|
343
|
o.ec_internationalorganization,
|
|
344
|
o.ec_enterprise,
|
|
345
|
o.ec_smevalidated,
|
|
346
|
o.ec_nutscode,
|
|
347
|
o.dateofcollection,
|
|
348
|
o.inferred,
|
|
349
|
o.deletedbyinference,
|
|
350
|
o.trust,
|
|
351
|
o.inferenceprovenance,
|
|
352
|
dc.id,
|
|
353
|
dc.officialname,
|
|
354
|
cc.code, cc.name, cs.code, cs.name,
|
|
355
|
pac.code, pac.name, pas.code, pas.name
|
|
356
|
);
|
|
357
|
|