Project

General

Profile

1
-- query performed by IIS to bulk load project details
2
DROP TABLE project_details;
3
CREATE TABLE project_details AS (
4
	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
);
15

    
16
GRANT SELECT ON project_details TO dnetapi;
17

    
18

    
19
-- used to serve queries from api tsv exporter
20
DROP TABLE projects_tsv;
21
CREATE TABLE projects_tsv AS (
22
  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
    org.country AS country,
37
    (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
    LEFT OUTER JOIN dsm_organizations org ON (po.resporganization = org.id)
46
    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
    org.country
62
  ORDER BY p.acronym
63
);
64

    
65
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
-- used to serve queries from dspace and eprints exporter
70
DROP TABLE projects_api;
71
CREATE TABLE projects_api AS (
72
	SELECT
73
		p.id,
74
		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
		(array_agg(org.legalshortname))[1] AS funder,
80
		(array_agg(fp.jurisdiction))[1]    AS jurisdiction,
81
		(array_agg(fp.id))[1]              AS fundingpathid
82
	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
		LEFT OUTER JOIN dsm_organizations org ON (org.id = fp.funder)
86
	GROUP BY p.id);
87

    
88
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

    
94
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
		p.lastupdate                                                                                               AS dateoftransformation,
112
		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
		p.contactfullname                                                                                          AS contactfullname,
120
		p.contactfax                                                                                               AS contactfax,
121
		p.contactphone                                                                                             AS contactphone,
122
		p.contactemail                                                                                             AS contactemail,
123
		p.summary                                                                                                  AS summary,
124
		p.currency                                                                                                 AS currency,
125
		p.totalcost                                                                                                AS totalcost,
126
        p.fundedamount                                                                                             AS fundedamount,
127
		dc.id                                                                                                      AS collectedfromid,
128
		dc.officialname                                                                                            AS collectedfromname,
129
		p.contracttype || '@@@' || p.contracttypename || '@@@' || p.contracttypescheme || '@@@' || p.contracttypescheme     AS contracttype,
130
		pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name                                    AS provenanceaction,
131
		array_agg(DISTINCT i.pid || '###' || i.issuertype)                                                         AS pid,
132
		array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) AS subjects,
133
		array_agg(DISTINCT fp.path)                                                                                AS fundingtree
134
	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
		LEFT OUTER JOIN dsm_identities i ON (i.pid = pp.pid)
140

    
141
		LEFT OUTER JOIN dsm_datasources dc ON (dc.id = p.collectedfrom)
142

    
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
		p.contactfullname,
172
		p.contactfax,
173
		p.contactphone,
174
		p.contactemail,
175
		p.contracttype,
176
		p.summary,
177
		p.currency,
178
		p.totalcost,
179
		p.fundedamount,
180
		dc.id,
181
		dc.officialname,
182
		pac.code, pac.name, pas.code, pas.name
183
);
184
CREATE INDEX projects_mv_id_idx	ON projects_mv (projectid);
185

    
186
GRANT SELECT ON fundingpaths TO dnetapi;
187

    
188

    
(3-3/24)