1 |
43877
|
claudio.at
|
-- query performed by IIS to bulk load project details
|
2 |
48460
|
claudio.at
|
DROP TABLE project_details;
|
3 |
|
|
CREATE TABLE project_details AS (
|
4 |
48139
|
alessia.ba
|
SELECT
|
5 |
|
|
p.id AS projectid,
|
6 |
|
|
p.acronym AS acronym,
|
7 |
|
|
p.code AS code,
|
8 |
|
|
p.jsonextrainfo AS jsonextrainfo,
|
9 |
|
|
array_agg(fp.path) AS fundingpath
|
10 |
|
|
FROM projects p
|
11 |
|
|
LEFT OUTER JOIN project_fundingpath pf ON (pf.project = p.id)
|
12 |
|
|
LEFT OUTER JOIN fundingpaths fp ON (fp.id = pf.funding)
|
13 |
|
|
GROUP BY p.id
|
14 |
43877
|
claudio.at
|
);
|
15 |
|
|
|
16 |
48460
|
claudio.at
|
GRANT SELECT ON project_details TO dnetapi;
|
17 |
|
|
|
18 |
|
|
|
19 |
43877
|
claudio.at
|
-- used to serve queries from api tsv exporter
|
20 |
48460
|
claudio.at
|
DROP TABLE projects_tsv;
|
21 |
|
|
CREATE TABLE projects_tsv AS (
|
22 |
51175
|
claudio.at
|
SELECT
|
23 |
|
|
nextval('projects_tsv_ids') AS rowid,
|
24 |
|
|
p.code,
|
25 |
|
|
p.acronym,
|
26 |
|
|
p.title,
|
27 |
|
|
p.call_identifier,
|
28 |
|
|
p.startdate,
|
29 |
|
|
p.enddate,
|
30 |
|
|
p.ec_sc39,
|
31 |
|
|
p.oa_mandate_for_publications,
|
32 |
|
|
p.ec_article29_3 AS oa_mandate_for_datasets,
|
33 |
|
|
p.contactfullname AS contactfullname,
|
34 |
|
|
p.contactemail AS contactemail,
|
35 |
|
|
org.legalname AS legalname,
|
36 |
53133
|
claudio.at
|
org.country AS country,
|
37 |
51175
|
claudio.at
|
(array_agg(f.id))[1] AS fundingpathid,
|
38 |
|
|
(array_agg(f.description))[1] AS description,
|
39 |
|
|
(array_agg(CASE WHEN po.participantnumber = 1
|
40 |
|
|
THEN 'coordinator'
|
41 |
|
|
ELSE '' END))[1] AS role
|
42 |
|
|
|
43 |
|
|
FROM projects p
|
44 |
|
|
LEFT OUTER JOIN project_organization po ON (p.id = po.project)
|
45 |
53133
|
claudio.at
|
LEFT OUTER JOIN dsm_organizations org ON (po.resporganization = org.id)
|
46 |
51175
|
claudio.at
|
LEFT OUTER JOIN project_fundingpath pf ON (p.id = pf.project)
|
47 |
|
|
LEFT OUTER JOIN fundingpaths f ON (pf.funding = f.id)
|
48 |
|
|
GROUP BY
|
49 |
|
|
p.code,
|
50 |
|
|
p.acronym,
|
51 |
|
|
p.title,
|
52 |
|
|
p.call_identifier,
|
53 |
|
|
p.startdate,
|
54 |
|
|
p.enddate,
|
55 |
|
|
p.ec_sc39,
|
56 |
|
|
p.oa_mandate_for_publications,
|
57 |
|
|
p.ec_article29_3,
|
58 |
|
|
p.contactfullname,
|
59 |
|
|
p.contactemail,
|
60 |
|
|
org.legalname,
|
61 |
53133
|
claudio.at
|
org.country
|
62 |
51175
|
claudio.at
|
ORDER BY p.acronym
|
63 |
48460
|
claudio.at
|
);
|
64 |
43877
|
claudio.at
|
|
65 |
48460
|
claudio.at
|
CREATE INDEX projects_tsv_fundingpathid_idx ON projects_tsv (fundingpathid);
|
66 |
|
|
CREATE INDEX projects_tsv_oa_mandate_for_datasets_idx ON projects_tsv (oa_mandate_for_datasets);
|
67 |
|
|
GRANT SELECT ON projects_tsv TO dnetapi;
|
68 |
|
|
|
69 |
43877
|
claudio.at
|
-- used to serve queries from dspace and eprints exporter
|
70 |
48460
|
claudio.at
|
DROP TABLE projects_api;
|
71 |
|
|
CREATE TABLE projects_api AS (
|
72 |
48139
|
alessia.ba
|
SELECT
|
73 |
48460
|
claudio.at
|
p.id,
|
74 |
48139
|
alessia.ba
|
p.acronym AS acronym,
|
75 |
|
|
p.title AS title,
|
76 |
|
|
p.code AS code,
|
77 |
|
|
p.startdate AS startdate,
|
78 |
|
|
p.enddate AS enddate,
|
79 |
48460
|
claudio.at
|
(array_agg(org.legalshortname))[1] AS funder,
|
80 |
|
|
(array_agg(fp.jurisdiction))[1] AS jurisdiction,
|
81 |
|
|
(array_agg(fp.id))[1] AS fundingpathid
|
82 |
48139
|
alessia.ba
|
FROM projects p
|
83 |
|
|
LEFT OUTER JOIN project_fundingpath pfp ON (p.id = pfp.project)
|
84 |
|
|
LEFT OUTER JOIN fundingpaths fp ON (pfp.funding = fp.id)
|
85 |
53133
|
claudio.at
|
LEFT OUTER JOIN dsm_organizations org ON (org.id = fp.funder)
|
86 |
48460
|
claudio.at
|
GROUP BY p.id);
|
87 |
43877
|
claudio.at
|
|
88 |
48460
|
claudio.at
|
CREATE INDEX projects_api_fundingpathid_idx ON projects_api (fundingpathid);
|
89 |
|
|
CREATE INDEX projects_api_startdate_idx ON projects_api (startdate);
|
90 |
|
|
CREATE INDEX projects_api_enddate_idx ON projects_api (enddate);
|
91 |
|
|
GRANT SELECT ON projects_api TO dnetapi;
|
92 |
|
|
|
93 |
45280
|
claudio.at
|
|
94 |
48139
|
alessia.ba
|
DROP MATERIALIZED VIEW projects_mv;
|
95 |
|
|
CREATE MATERIALIZED VIEW projects_mv AS (
|
96 |
|
|
SELECT
|
97 |
|
|
p.id AS projectid,
|
98 |
|
|
p.code AS code,
|
99 |
|
|
p.websiteurl AS websiteurl,
|
100 |
|
|
p.acronym AS acronym,
|
101 |
|
|
p.title AS title,
|
102 |
|
|
p.startdate AS startdate,
|
103 |
|
|
p.enddate AS enddate,
|
104 |
|
|
p.call_identifier AS callidentifier,
|
105 |
|
|
p.keywords AS keywords,
|
106 |
|
|
p.duration AS duration,
|
107 |
|
|
p.ec_sc39 AS ecsc39,
|
108 |
|
|
p.oa_mandate_for_publications AS oamandatepublications,
|
109 |
|
|
p.ec_article29_3 AS ecarticle29_3,
|
110 |
|
|
p.dateofcollection AS dateofcollection,
|
111 |
52338
|
alessia.ba
|
p.lastupdate AS dateoftransformation,
|
112 |
48139
|
alessia.ba
|
p.inferred AS inferred,
|
113 |
|
|
p.deletedbyinference AS deletedbyinference,
|
114 |
|
|
p.trust AS trust,
|
115 |
|
|
p.inferenceprovenance AS inferenceprovenance,
|
116 |
|
|
p.optional1 AS optional1,
|
117 |
|
|
p.optional2 AS optional2,
|
118 |
|
|
p.jsonextrainfo AS jsonextrainfo,
|
119 |
48962
|
claudio.at
|
p.contactfullname AS contactfullname,
|
120 |
|
|
p.contactfax AS contactfax,
|
121 |
|
|
p.contactphone AS contactphone,
|
122 |
|
|
p.contactemail AS contactemail,
|
123 |
57353
|
claudio.at
|
p.summary AS summary,
|
124 |
|
|
p.currency AS currency,
|
125 |
|
|
p.totalcost AS totalcost,
|
126 |
|
|
p.fundedamount AS fundedamount,
|
127 |
48139
|
alessia.ba
|
dc.id AS collectedfromid,
|
128 |
|
|
dc.officialname AS collectedfromname,
|
129 |
57300
|
alessia.ba
|
p.contracttype || '@@@' || p.contracttypename || '@@@' || p.contracttypescheme || '@@@' || p.contracttypescheme AS contracttype,
|
130 |
48139
|
alessia.ba
|
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction,
|
131 |
53708
|
claudio.at
|
array_agg(DISTINCT i.pid || '###' || i.issuertype) AS pid,
|
132 |
48139
|
alessia.ba
|
array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) AS subjects,
|
133 |
52191
|
claudio.at
|
array_agg(DISTINCT fp.path) AS fundingtree
|
134 |
48139
|
alessia.ba
|
FROM projects p
|
135 |
|
|
LEFT OUTER JOIN class pac ON (pac.code = p.provenanceactionclass)
|
136 |
|
|
LEFT OUTER JOIN scheme pas ON (pas.code = p.provenanceactionscheme)
|
137 |
|
|
|
138 |
|
|
LEFT OUTER JOIN projectpids pp ON (pp.project = p.id)
|
139 |
53708
|
claudio.at
|
LEFT OUTER JOIN dsm_identities i ON (i.pid = pp.pid)
|
140 |
48139
|
alessia.ba
|
|
141 |
52325
|
claudio.at
|
LEFT OUTER JOIN dsm_datasources dc ON (dc.id = p.collectedfrom)
|
142 |
48139
|
alessia.ba
|
|
143 |
|
|
LEFT OUTER JOIN project_fundingpath pf ON (pf.project = p.id)
|
144 |
|
|
LEFT OUTER JOIN fundingpaths fp ON (fp.id = pf.funding)
|
145 |
|
|
|
146 |
|
|
LEFT OUTER JOIN project_subject ps ON (ps.project = p.id)
|
147 |
|
|
LEFT OUTER JOIN subjects s ON (s.id = ps.subject)
|
148 |
|
|
|
149 |
|
|
LEFT OUTER JOIN class sc ON (sc.code = s.semanticclass)
|
150 |
|
|
LEFT OUTER JOIN scheme ss ON (ss.code = s.semanticscheme)
|
151 |
|
|
|
152 |
|
|
GROUP BY
|
153 |
|
|
p.id,
|
154 |
|
|
p.code,
|
155 |
|
|
p.websiteurl,
|
156 |
|
|
p.acronym,
|
157 |
|
|
p.title,
|
158 |
|
|
p.startdate,
|
159 |
|
|
p.enddate,
|
160 |
|
|
p.call_identifier,
|
161 |
|
|
p.keywords,
|
162 |
|
|
p.duration,
|
163 |
|
|
p.ec_sc39,
|
164 |
|
|
p.oa_mandate_for_publications,
|
165 |
|
|
p.ec_article29_3,
|
166 |
|
|
p.dateofcollection,
|
167 |
|
|
p.inferred,
|
168 |
|
|
p.deletedbyinference,
|
169 |
|
|
p.trust,
|
170 |
|
|
p.inferenceprovenance,
|
171 |
48962
|
claudio.at
|
p.contactfullname,
|
172 |
|
|
p.contactfax,
|
173 |
|
|
p.contactphone,
|
174 |
|
|
p.contactemail,
|
175 |
57300
|
alessia.ba
|
p.contracttype,
|
176 |
57353
|
claudio.at
|
p.summary,
|
177 |
|
|
p.currency,
|
178 |
|
|
p.totalcost,
|
179 |
|
|
p.fundedamount,
|
180 |
48139
|
alessia.ba
|
dc.id,
|
181 |
|
|
dc.officialname,
|
182 |
|
|
pac.code, pac.name, pas.code, pas.name
|
183 |
|
|
);
|
184 |
48962
|
claudio.at
|
CREATE INDEX projects_mv_id_idx ON projects_mv (projectid);
|
185 |
48139
|
alessia.ba
|
|
186 |
48460
|
claudio.at
|
GRANT SELECT ON fundingpaths TO dnetapi;
|
187 |
51175
|
claudio.at
|
|