21 |
21 |
-- used to serve queries from api tsv exporter
|
22 |
22 |
DROP TABLE projects_tsv;
|
23 |
23 |
CREATE TABLE projects_tsv AS (
|
24 |
|
SELECT
|
25 |
|
p.id,
|
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 |
|
(array_agg(f.id))[1] AS fundingpathid,
|
36 |
|
(array_agg(f.description))[1] AS description,
|
37 |
|
(array_agg(org.legalname))[1] AS legalname,
|
38 |
|
(array_agg(org.countryclass))[1] AS country,
|
39 |
|
(array_agg(CASE WHEN po.participantnumber = 1
|
40 |
|
THEN 'coordinator'
|
41 |
|
ELSE '' END))[1] AS role,
|
42 |
|
(array_agg(pe.firstname))[1] AS firstname,
|
43 |
|
(array_agg(pe.secondnames))[1] AS secondnames,
|
44 |
|
(array_agg(pe.email))[1] AS email
|
45 |
|
FROM projects p
|
46 |
|
LEFT OUTER JOIN project_organization po ON (p.id = po.project)
|
47 |
|
LEFT OUTER JOIN persons pe ON (po.contactperson = pe.id)
|
48 |
|
LEFT OUTER JOIN organizations org ON (po.resporganization = org.id)
|
49 |
|
LEFT OUTER JOIN project_fundingpath pf ON (p.id = pf.project)
|
50 |
|
LEFT OUTER JOIN fundingpaths f ON (pf.funding = f.id)
|
51 |
|
GROUP BY p.id
|
52 |
|
ORDER BY p.acronym
|
|
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
|
53 |
65 |
);
|
54 |
66 |
|
55 |
67 |
CREATE INDEX projects_tsv_fundingpathid_idx ON projects_tsv (fundingpathid);
|
... | ... | |
271 |
283 |
|
272 |
284 |
GRANT SELECT ON dashboard_ds TO dnetapi;
|
273 |
285 |
GRANT SELECT ON fundingpaths TO dnetapi;
|
|
286 |
|
|
287 |
-- used to query from dsm_organizations UNION organizations when loading data to HBase
|
|
288 |
CREATE OR REPLACE VIEW orgs AS (
|
|
289 |
SELECT
|
|
290 |
o.id AS organizationid,
|
|
291 |
o.legalshortname AS legalshortname,
|
|
292 |
o.legalname AS legalname,
|
|
293 |
o.websiteurl AS websiteurl,
|
|
294 |
o.logourl AS logourl,
|
|
295 |
o.ec_legalbody AS eclegalbody,
|
|
296 |
o.ec_legalperson AS eclegalperson,
|
|
297 |
o.ec_nonprofit AS ecnonprofit,
|
|
298 |
o.ec_researchorganization AS ecresearchorganization,
|
|
299 |
o.ec_highereducation AS echighereducation,
|
|
300 |
o.ec_internationalorganizationeurinterests AS ecinternationalorganizationeurinterests,
|
|
301 |
o.ec_internationalorganization AS ecinternationalorganization,
|
|
302 |
o.ec_enterprise AS ecenterprise,
|
|
303 |
o.ec_smevalidated AS ecsmevalidated,
|
|
304 |
o.ec_nutscode AS ecnutscode,
|
|
305 |
o.dateofcollection AS dateofcollection,
|
|
306 |
o.inferred AS inferred,
|
|
307 |
o.deletedbyinference AS deletedbyinference,
|
|
308 |
o.trust AS trust,
|
|
309 |
o.inferenceprovenance AS inferenceprovenance,
|
|
310 |
dc.id AS collectedfromid,
|
|
311 |
dc.officialname AS collectedfromname,
|
|
312 |
|
|
313 |
cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name AS country,
|
|
314 |
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction,
|
|
315 |
|
|
316 |
array_agg(DISTINCT i.pid || '###' || i.issuertypeclass) AS pid
|
|
317 |
|
|
318 |
FROM organizations o
|
|
319 |
LEFT OUTER JOIN class cc ON (cc.code = o.countryclass)
|
|
320 |
LEFT OUTER JOIN scheme cs ON (cs.code = o.countryscheme)
|
|
321 |
|
|
322 |
LEFT OUTER JOIN class pac ON (pac.code = o.provenanceactionclass)
|
|
323 |
LEFT OUTER JOIN scheme pas ON (pas.code = o.provenanceactionscheme)
|
|
324 |
|
|
325 |
LEFT OUTER JOIN organizationpids op ON (op.organization = o.id)
|
|
326 |
LEFT OUTER JOIN identities i ON (i.pid = op.pid)
|
|
327 |
|
|
328 |
LEFT OUTER JOIN datasources dc ON (dc.id = o.collectedfrom)
|
|
329 |
|
|
330 |
GROUP BY
|
|
331 |
o.id,
|
|
332 |
o.legalshortname,
|
|
333 |
o.legalname,
|
|
334 |
o.websiteurl,
|
|
335 |
o.logourl,
|
|
336 |
o.ec_legalbody,
|
|
337 |
o.ec_legalperson,
|
|
338 |
o.ec_nonprofit,
|
|
339 |
o.ec_researchorganization,
|
|
340 |
o.ec_highereducation,
|
|
341 |
o.ec_internationalorganizationeurinterests,
|
|
342 |
o.ec_internationalorganization,
|
|
343 |
o.ec_enterprise,
|
|
344 |
o.ec_smevalidated,
|
|
345 |
o.ec_nutscode,
|
|
346 |
o.dateofcollection,
|
|
347 |
o.inferred,
|
|
348 |
o.deletedbyinference,
|
|
349 |
o.trust,
|
|
350 |
o.inferenceprovenance,
|
|
351 |
dc.id,
|
|
352 |
dc.officialname,
|
|
353 |
cc.code, cc.name, cs.code, cs.name,
|
|
354 |
pac.code, pac.name, pas.code, pas.name
|
|
355 |
);
|
|
356 |
|
assume the view orgs is already there