Revision 48460
Added by Claudio Atzori over 6 years ago
| modules/dnet-openaireplus-workflows/trunk/src/main/resources/eu/dnetlib/patch-db/materialised_views.sql | ||
|---|---|---|
| 1 | 1 |
-- query performed by IIS to bulk load project details |
| 2 |
DROP MATERIALIZED VIEW project_details;
|
|
| 3 |
CREATE MATERIALIZED VIEW project_details AS (
|
|
| 2 |
DROP TABLE project_details;
|
|
| 3 |
CREATE TABLE project_details AS (
|
|
| 4 | 4 |
SELECT |
| 5 | 5 |
p.id AS projectid, |
| 6 | 6 |
p.acronym AS acronym, |
| ... | ... | |
| 15 | 15 |
GROUP BY p.id |
| 16 | 16 |
); |
| 17 | 17 |
|
| 18 |
GRANT SELECT ON project_details TO dnetapi; |
|
| 19 |
|
|
| 20 |
|
|
| 18 | 21 |
-- used to serve queries from api tsv exporter |
| 19 |
DROP MATERIALIZED VIEW projects_tsv;
|
|
| 20 |
CREATE MATERIALIZED VIEW projects_tsv AS (
|
|
| 22 |
DROP TABLE projects_tsv;
|
|
| 23 |
CREATE TABLE projects_tsv AS (
|
|
| 21 | 24 |
SELECT |
| 22 |
pr.code, |
|
| 23 |
pr.acronym, |
|
| 24 |
pr.title, |
|
| 25 |
pr.call_identifier, |
|
| 26 |
pr.startdate, |
|
| 27 |
pr.enddate, |
|
| 28 |
pr.ec_sc39, |
|
| 29 |
pr.oa_mandate_for_publications, |
|
| 30 |
pr.ec_article29_3, |
|
| 31 |
f.id AS fundingpathid, |
|
| 32 |
f.description, |
|
| 33 |
org.legalname, |
|
| 34 |
org.countryclass, |
|
| 35 |
CASE WHEN po.participantnumber = 1 |
|
| 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 |
|
| 36 | 40 |
THEN 'coordinator' |
| 37 |
ELSE '' END AS role, |
|
| 38 |
pe.firstname,
|
|
| 39 |
pe.secondnames,
|
|
| 40 |
pe.email
|
|
| 41 |
FROM projects pr
|
|
| 42 |
LEFT OUTER JOIN project_organization po ON (pr.id = po.project)
|
|
| 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) |
|
| 43 | 47 |
LEFT OUTER JOIN persons pe ON (po.contactperson = pe.id) |
| 44 | 48 |
LEFT OUTER JOIN organizations org ON (po.resporganization = org.id) |
| 45 |
LEFT OUTER JOIN project_fundingpath pf ON (pr.id = pf.project)
|
|
| 49 |
LEFT OUTER JOIN project_fundingpath pf ON (p.id = pf.project) |
|
| 46 | 50 |
LEFT OUTER JOIN fundingpaths f ON (pf.funding = f.id) |
| 47 |
ORDER BY pr.acronym); |
|
| 48 |
CREATE INDEX projects_tsv_fundingpathid_idx |
|
| 49 |
ON projects_tsv (fundingpathid); |
|
| 50 |
CREATE INDEX projects_tsv_ec_article29_3_idx |
|
| 51 |
ON projects_tsv (ec_article29_3); |
|
| 51 |
GROUP BY p.id |
|
| 52 |
ORDER BY p.acronym |
|
| 53 |
); |
|
| 52 | 54 |
|
| 55 |
CREATE INDEX projects_tsv_fundingpathid_idx ON projects_tsv (fundingpathid); |
|
| 56 |
CREATE INDEX projects_tsv_oa_mandate_for_datasets_idx ON projects_tsv (oa_mandate_for_datasets); |
|
| 57 |
GRANT SELECT ON projects_tsv TO dnetapi; |
|
| 58 |
|
|
| 59 |
|
|
| 53 | 60 |
-- used to serve queries from dspace and eprints exporter |
| 54 |
DROP MATERIALIZED VIEW projects_api;
|
|
| 55 |
CREATE MATERIALIZED VIEW projects_api AS (
|
|
| 61 |
DROP TABLE projects_api;
|
|
| 62 |
CREATE TABLE projects_api AS (
|
|
| 56 | 63 |
SELECT |
| 57 |
org.legalshortname AS funder, |
|
| 58 |
fp.jurisdiction AS jurisdiction, |
|
| 64 |
p.id, |
|
| 59 | 65 |
p.acronym AS acronym, |
| 60 | 66 |
p.title AS title, |
| 61 | 67 |
p.code AS code, |
| 62 | 68 |
p.startdate AS startdate, |
| 63 | 69 |
p.enddate AS enddate, |
| 64 |
fp.id AS fundingpathid |
|
| 70 |
(array_agg(org.legalshortname))[1] AS funder, |
|
| 71 |
(array_agg(fp.jurisdiction))[1] AS jurisdiction, |
|
| 72 |
(array_agg(fp.id))[1] AS fundingpathid |
|
| 65 | 73 |
FROM projects p |
| 66 | 74 |
LEFT OUTER JOIN project_fundingpath pfp ON (p.id = pfp.project) |
| 67 | 75 |
LEFT OUTER JOIN fundingpaths fp ON (pfp.funding = fp.id) |
| 68 |
LEFT OUTER JOIN organizations org ON (org.id = fp.funder)); |
|
| 69 |
CREATE INDEX projects_api_fundingpathid_idx |
|
| 70 |
ON projects_api (fundingpathid); |
|
| 71 |
CREATE INDEX projects_api_startdate_idx |
|
| 72 |
ON projects_api (startdate); |
|
| 73 |
CREATE INDEX projects_api_enddate_idx |
|
| 74 |
ON projects_api (enddate); |
|
| 76 |
LEFT OUTER JOIN organizations org ON (org.id = fp.funder) |
|
| 77 |
GROUP BY p.id); |
|
| 75 | 78 |
|
| 79 |
CREATE INDEX projects_api_fundingpathid_idx ON projects_api (fundingpathid); |
|
| 80 |
CREATE INDEX projects_api_startdate_idx ON projects_api (startdate); |
|
| 81 |
CREATE INDEX projects_api_enddate_idx ON projects_api (enddate); |
|
| 82 |
GRANT SELECT ON projects_api TO dnetapi; |
|
| 83 |
|
|
| 76 | 84 |
-- used to serve queries from the repo admin dashboard |
| 77 | 85 |
DROP MATERIALIZED VIEW dashboard_ds; |
| 78 | 86 |
CREATE MATERIALIZED VIEW dashboard_ds AS ( |
| ... | ... | |
| 260 | 268 |
CREATE INDEX projects_mv_id_idx |
| 261 | 269 |
ON projects_mv (projectid); |
| 262 | 270 |
|
| 263 |
GRANT SELECT ON projects_api TO dnetapi; |
|
| 264 |
GRANT SELECT ON projects_tsv TO dnetapi; |
|
| 265 |
GRANT SELECT ON project_details TO dnetapi; |
|
| 271 |
|
|
| 266 | 272 |
GRANT SELECT ON dashboard_ds TO dnetapi; |
| 273 |
GRANT SELECT ON fundingpaths TO dnetapi; |
|
Also available in: Unified diff
updated views definitions