Revision 53133
Added by Claudio Atzori over 5 years ago
| materialised_views.sql | ||
|---|---|---|
| 35 | 35 |
p.contactfullname AS contactfullname, |
| 36 | 36 |
p.contactemail AS contactemail, |
| 37 | 37 |
org.legalname AS legalname, |
| 38 |
org.countryclass AS country,
|
|
| 38 |
org.country AS country, |
|
| 39 | 39 |
(array_agg(f.id))[1] AS fundingpathid, |
| 40 | 40 |
(array_agg(f.description))[1] AS description, |
| 41 | 41 |
(array_agg(CASE WHEN po.participantnumber = 1 |
| ... | ... | |
| 44 | 44 |
|
| 45 | 45 |
FROM projects p |
| 46 | 46 |
LEFT OUTER JOIN project_organization po ON (p.id = po.project) |
| 47 |
LEFT OUTER JOIN organizations org ON (po.resporganization = org.id) |
|
| 47 |
LEFT OUTER JOIN dsm_organizations org ON (po.resporganization = org.id)
|
|
| 48 | 48 |
LEFT OUTER JOIN project_fundingpath pf ON (p.id = pf.project) |
| 49 | 49 |
LEFT OUTER JOIN fundingpaths f ON (pf.funding = f.id) |
| 50 | 50 |
GROUP BY |
| ... | ... | |
| 60 | 60 |
p.contactfullname, |
| 61 | 61 |
p.contactemail, |
| 62 | 62 |
org.legalname, |
| 63 |
org.countryclass
|
|
| 63 |
org.country |
|
| 64 | 64 |
ORDER BY p.acronym |
| 65 | 65 |
); |
| 66 | 66 |
|
| ... | ... | |
| 84 | 84 |
FROM projects p |
| 85 | 85 |
LEFT OUTER JOIN project_fundingpath pfp ON (p.id = pfp.project) |
| 86 | 86 |
LEFT OUTER JOIN fundingpaths fp ON (pfp.funding = fp.id) |
| 87 |
LEFT OUTER JOIN organizations org ON (org.id = fp.funder) |
|
| 87 |
LEFT OUTER JOIN dsm_organizations org ON (org.id = fp.funder)
|
|
| 88 | 88 |
GROUP BY p.id); |
| 89 | 89 |
|
| 90 | 90 |
CREATE INDEX projects_api_fundingpathid_idx ON projects_api (fundingpathid); |
| ... | ... | |
| 92 | 92 |
CREATE INDEX projects_api_enddate_idx ON projects_api (enddate); |
| 93 | 93 |
GRANT SELECT ON projects_api TO dnetapi; |
| 94 | 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 | 95 |
|
| 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 | 96 |
DROP MATERIALIZED VIEW projects_mv; |
| 199 | 97 |
CREATE MATERIALIZED VIEW projects_mv AS ( |
| 200 | 98 |
SELECT |
| ... | ... | |
| 282 | 180 |
); |
| 283 | 181 |
CREATE INDEX projects_mv_id_idx ON projects_mv (projectid); |
| 284 | 182 |
|
| 285 |
GRANT SELECT ON dashboard_ds TO dnetapi; |
|
| 286 | 183 |
GRANT SELECT ON fundingpaths TO dnetapi; |
| 287 | 184 |
|
| 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 | 185 |
|
| 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 |
|
|
Also available in: Unified diff
updated materialised view update script. Adjusted db mgration procedure.