Revision 51175
Added by Claudio Atzori about 6 years ago
| materialised_views.sql | ||
|---|---|---|
| 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 |
|
|
Also available in: Unified diff
assume the view orgs is already there