|
1
|
SELECT
|
|
2
|
d.id AS datasourceid,
|
|
3
|
--regexp_replace(d.id, '^.*::', '') AS originalid,
|
|
4
|
-- array_agg(DISTINCT (d.id, i.pid)) as identities,
|
|
5
|
ARRAY [d.id, i.pid] AS identities,
|
|
6
|
--SELECT ARRAY[dd.id, ii.pid] as identities from datasources dd left outer join datasourcepids dps2 on (dps2.datasource = dd.id)
|
|
7
|
-- left outer join identities ii on (ii.pid = dps2.pid)
|
|
8
|
|
|
9
|
-- d.id AS originalid,
|
|
10
|
d.officialname AS officialname,
|
|
11
|
d.englishname AS englishname,
|
|
12
|
|
|
13
|
CASE
|
|
14
|
WHEN (array_agg(DISTINCT tcc.compliance :: TEXT) @> ARRAY ['driver', 'openaire2.0'])
|
|
15
|
THEN
|
|
16
|
'driver-openaire2.0@@@OpenAIRE 2.0+ (DRIVER OA, EC funding)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
17
|
WHEN (array_agg(DISTINCT tcc.compliance :: TEXT) @> ARRAY ['driver'])
|
|
18
|
THEN
|
|
19
|
'driver@@@OpenAIRE Basic (DRIVER OA)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
20
|
WHEN (array_agg(DISTINCT tcc.compliance :: TEXT) @> ARRAY ['openaire2.0'])
|
|
21
|
THEN
|
|
22
|
'openaire2.0@@@OpenAIRE 2.0 (EC funding)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
23
|
WHEN (array_agg(DISTINCT tcc.compliance :: TEXT) @> ARRAY ['openaire3.0'])
|
|
24
|
THEN
|
|
25
|
'openaire3.0@@@OpenAIRE 3.0 (OA, funding)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
26
|
WHEN (array_agg(DISTINCT tcc.compliance :: TEXT) @> ARRAY ['openaire2.0_data'])
|
|
27
|
THEN
|
|
28
|
'openaire2.0_data@@@OpenAIRE Data (funded, referenced datasets)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
29
|
WHEN (array_agg(DISTINCT tcc.compliance :: TEXT) @> ARRAY ['native'])
|
|
30
|
THEN
|
|
31
|
'native@@@proprietary@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
32
|
WHEN (array_agg(DISTINCT tcc.compliance :: TEXT) @> ARRAY ['hostedBy'])
|
|
33
|
THEN
|
|
34
|
'hostedBy@@@collected from a compatible aggregator@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
35
|
WHEN (array_agg(DISTINCT tcc.compliance :: TEXT) @> ARRAY ['notCompatible'])
|
|
36
|
THEN
|
|
37
|
'notCompatible@@@under validation@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
38
|
ELSE
|
|
39
|
'UNKNOWN@@@not available@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
40
|
END AS openairecompatibility,
|
|
41
|
|
|
42
|
d.websiteurl AS websiteurl,
|
|
43
|
d.logourl AS logourl,
|
|
44
|
d.contactemail AS contactemail,
|
|
45
|
array_agg(DISTINCT CASE
|
|
46
|
WHEN apc.param = 'baseUrl' AND api.protocolclass = 'oai'
|
|
47
|
THEN
|
|
48
|
CASE WHEN COALESCE(apc.edited, '') = ''
|
|
49
|
THEN apc.original
|
|
50
|
ELSE apc.edited END
|
|
51
|
ELSE
|
|
52
|
NULL
|
|
53
|
END) AS accessinfopackage,
|
|
54
|
-- we need baseUrl param only -when available-, but we can not use WHERE clause on apc.param, otherwise we exclude datasources without a baseUrl param and the returned rows are less than we expect.
|
|
55
|
d.latitude AS latitude,
|
|
56
|
d.longitude AS longitude,
|
|
57
|
d.namespaceprefix AS namespaceprefix,
|
|
58
|
d.od_numberofitems AS odnumberofitems,
|
|
59
|
d.od_numberofitemsdate AS odnumberofitemsdate,
|
|
60
|
array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) AS subjects,
|
|
61
|
d.description AS description,
|
|
62
|
d.od_policies AS odpolicies,
|
|
63
|
ARRAY(SELECT trim(s)
|
|
64
|
FROM unnest(string_to_array(d.od_languages, ',')) AS s) AS odlanguages,
|
|
65
|
ARRAY(SELECT trim(s)
|
|
66
|
FROM unnest(string_to_array(d.od_contenttypes, '-')) AS s) AS odcontenttypes,
|
|
67
|
d.inferred AS inferred,
|
|
68
|
d.deletedbyinference AS deletedbyinference,
|
|
69
|
d.trust AS trust,
|
|
70
|
d.inferenceprovenance AS inferenceprovenance,
|
|
71
|
d.dateofcollection AS dateofcollection,
|
|
72
|
d.dateofvalidation AS dateofvalidation,
|
|
73
|
|
|
74
|
-- re3data fields
|
|
75
|
d.releasestartdate AS releasestartdate,
|
|
76
|
d.releaseenddate AS releaseenddate,
|
|
77
|
d.missionstatementurl AS missionstatementurl,
|
|
78
|
d.dataprovider AS dataprovider,
|
|
79
|
d.serviceprovider AS serviceprovider,
|
|
80
|
d.databaseaccesstype AS databaseaccesstype,
|
|
81
|
d.datauploadtype AS datauploadtype,
|
|
82
|
d.databaseaccessrestriction AS databaseaccessrestriction,
|
|
83
|
d.datauploadrestriction AS datauploadrestriction,
|
|
84
|
d.versioning AS versioning,
|
|
85
|
d.citationguidelineurl AS citationguidelineurl,
|
|
86
|
d.qualitymanagementkind AS qualitymanagementkind,
|
|
87
|
d.pidsystems AS pidsystems,
|
|
88
|
d.certificates AS certificates,
|
|
89
|
array_agg(DISTINCT p.name || '&&&' || p.url) AS policies,
|
|
90
|
-- end of re3data fields
|
|
91
|
|
|
92
|
dc.id AS collectedfromid,
|
|
93
|
dc.officialname AS collectedfromname,
|
|
94
|
|
|
95
|
tc.code || '@@@' || tc.name || '@@@' || ts.code || '@@@' || ts.name AS datasourcetype,
|
|
96
|
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction
|
|
97
|
|
|
98
|
-- merged into identities above
|
|
99
|
-- array_agg(DISTINCT i.pid || '###' || i.issuertypeclass) as pid
|
|
100
|
|
|
101
|
|
|
102
|
FROM datasources d
|
|
103
|
LEFT OUTER JOIN class tc ON (tc.code = d.datasourceclass)
|
|
104
|
LEFT OUTER JOIN scheme ts ON (ts.code = d.datasourcescheme)
|
|
105
|
|
|
106
|
LEFT OUTER JOIN class pac ON (pac.code = d.provenanceactionclass)
|
|
107
|
LEFT OUTER JOIN scheme pas ON (pas.code = d.provenanceactionscheme)
|
|
108
|
|
|
109
|
LEFT OUTER JOIN datasourcepids dps ON (dps.datasource = d.id)
|
|
110
|
LEFT OUTER JOIN identities i ON (i.pid = dps.pid)
|
|
111
|
|
|
112
|
LEFT OUTER JOIN datasources dc ON (dc.id = d.collectedfrom)
|
|
113
|
LEFT OUTER JOIN api ON (api.datasource = d.id)
|
|
114
|
LEFT OUTER JOIN apicollections apc ON (apc.api = api.id)
|
|
115
|
|
|
116
|
LEFT OUTER JOIN temp_compliances tcc ON (tcc.api = api.id)
|
|
117
|
|
|
118
|
LEFT OUTER JOIN datasource_subject ds ON (ds.datasource = d.id)
|
|
119
|
LEFT OUTER JOIN subjects s ON (s.id = ds.subject)
|
|
120
|
|
|
121
|
LEFT OUTER JOIN class sc ON (sc.code = s.semanticclass)
|
|
122
|
LEFT OUTER JOIN scheme ss ON (ss.code = s.semanticscheme)
|
|
123
|
|
|
124
|
LEFT OUTER JOIN datasource_policy dp ON (dp.datasource = d.id)
|
|
125
|
LEFT OUTER JOIN policies p ON (p.id = dp.policy)
|
|
126
|
|
|
127
|
-- These clauses have been commented to allow the import of all the datasource on the index
|
|
128
|
-- WHERE d.datasourceclass != 'entityregistry'
|
|
129
|
-- AND d.openairecompatibilityclass IS NOT null
|
|
130
|
-- AND d.openairecompatibilityclass != 'UNKNOWN'
|
|
131
|
-- AND d.openairecompatibilityclass != 'notCompatible'
|
|
132
|
|
|
133
|
GROUP BY
|
|
134
|
d.id,
|
|
135
|
d.officialname,
|
|
136
|
d.englishname,
|
|
137
|
d.websiteurl,
|
|
138
|
d.logourl,
|
|
139
|
d.contactemail,
|
|
140
|
d.namespaceprefix,
|
|
141
|
d.description,
|
|
142
|
d.od_numberofitems,
|
|
143
|
d.od_numberofitemsdate,
|
|
144
|
d.od_policies,
|
|
145
|
d.od_languages,
|
|
146
|
d.od_contenttypes,
|
|
147
|
d.latitude,
|
|
148
|
d.longitude,
|
|
149
|
d.inferred,
|
|
150
|
d.deletedbyinference,
|
|
151
|
d.trust,
|
|
152
|
d.inferenceprovenance,
|
|
153
|
d.dateofcollection,
|
|
154
|
d.dateofvalidation,
|
|
155
|
dc.id,
|
|
156
|
d.releasestartdate,
|
|
157
|
d.releaseenddate,
|
|
158
|
d.missionstatementurl,
|
|
159
|
d.dataprovider,
|
|
160
|
d.serviceprovider,
|
|
161
|
d.databaseaccesstype,
|
|
162
|
d.datauploadtype,
|
|
163
|
d.databaseaccessrestriction,
|
|
164
|
d.datauploadrestriction,
|
|
165
|
d.versioning,
|
|
166
|
d.citationguidelineurl,
|
|
167
|
d.qualitymanagementkind,
|
|
168
|
d.pidsystems,
|
|
169
|
d.certificates,
|
|
170
|
dc.officialname,
|
|
171
|
tc.code, tc.name, ts.code, ts.name,
|
|
172
|
pac.code, pac.name, pas.code, pas.name,
|
|
173
|
sc.code, sc.name, ss.code, ss.name,
|
|
174
|
i.pid
|