|
1
|
SELECT
|
|
2
|
d.id AS datasourceid,
|
|
3
|
d.id || array_agg(di.pid) AS identities,
|
|
4
|
d.officialname AS officialname,
|
|
5
|
d.englishname AS englishname,
|
|
6
|
CASE
|
|
7
|
WHEN (array_agg(DISTINCT COALESCE (a.compatibility_override, a.compatibility):: TEXT) @> ARRAY ['driver', 'openaire2.0'])
|
|
8
|
THEN
|
|
9
|
'driver-openaire2.0@@@OpenAIRE 2.0+ (DRIVER OA, EC funding)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
10
|
WHEN (array_agg(DISTINCT COALESCE (a.compatibility_override, a.compatibility) :: TEXT) @> ARRAY ['driver'])
|
|
11
|
THEN
|
|
12
|
'driver@@@OpenAIRE Basic (DRIVER OA)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
13
|
WHEN (array_agg(DISTINCT COALESCE (a.compatibility_override, a.compatibility) :: TEXT) @> ARRAY ['openaire2.0'])
|
|
14
|
THEN
|
|
15
|
'openaire2.0@@@OpenAIRE 2.0 (EC funding)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
16
|
WHEN (array_agg(DISTINCT COALESCE (a.compatibility_override, a.compatibility) :: TEXT) @> ARRAY ['openaire3.0'])
|
|
17
|
THEN
|
|
18
|
'openaire3.0@@@OpenAIRE 3.0 (OA, funding)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
19
|
WHEN (array_agg(DISTINCT COALESCE (a.compatibility_override, a.compatibility) :: TEXT) @> ARRAY ['openaire2.0_data'])
|
|
20
|
THEN
|
|
21
|
'openaire2.0_data@@@OpenAIRE Data (funded, referenced datasets)@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
22
|
WHEN (array_agg(DISTINCT COALESCE (a.compatibility_override, a.compatibility) :: TEXT) @> ARRAY ['native'])
|
|
23
|
THEN
|
|
24
|
'native@@@proprietary@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
25
|
WHEN (array_agg(DISTINCT COALESCE (a.compatibility_override, a.compatibility) :: TEXT) @> ARRAY ['hostedBy'])
|
|
26
|
THEN
|
|
27
|
'hostedBy@@@collected from a compatible aggregator@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
28
|
WHEN (array_agg(DISTINCT COALESCE (a.compatibility_override, a.compatibility) :: TEXT) @> ARRAY ['notCompatible'])
|
|
29
|
THEN
|
|
30
|
'notCompatible@@@under validation@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
31
|
ELSE
|
|
32
|
'UNKNOWN@@@not available@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'
|
|
33
|
END AS openairecompatibility,
|
|
34
|
d.websiteurl AS websiteurl,
|
|
35
|
d.logourl AS logourl,
|
|
36
|
array_agg(DISTINCT CASE WHEN a.protocol = 'oai' THEN a.baseurl ELSE NULL END) AS accessinfopackage,
|
|
37
|
d.latitude AS latitude,
|
|
38
|
d.longitude AS longitude,
|
|
39
|
d.namespaceprefix AS namespaceprefix,
|
|
40
|
NULL AS odnumberofitems,
|
|
41
|
NULL AS odnumberofitemsdate,
|
|
42
|
(SELECT array_agg(s|| '###unknown@@@unknown@@@dnet:subjects@@@dnet:subjects') FROM UNNEST(d.subjects::text[]) AS s) AS subjects,
|
|
43
|
d.description AS description,
|
|
44
|
NULL AS odpolicies,
|
|
45
|
d.languages AS odlanguages,
|
|
46
|
d.od_contenttypes AS odcontenttypes,
|
|
47
|
false AS inferred,
|
|
48
|
false AS deletedbyinference,
|
|
49
|
0.9 AS trust,
|
|
50
|
NULL AS inferenceprovenance,
|
|
51
|
d.dateofcollection AS dateofcollection,
|
|
52
|
d.dateofvalidation AS dateofvalidation,
|
|
53
|
-- re3data fields
|
|
54
|
d.releasestartdate AS releasestartdate,
|
|
55
|
d.releaseenddate AS releaseenddate,
|
|
56
|
d.missionstatementurl AS missionstatementurl,
|
|
57
|
d.dataprovider AS dataprovider,
|
|
58
|
d.serviceprovider AS serviceprovider,
|
|
59
|
d.databaseaccesstype AS databaseaccesstype,
|
|
60
|
d.datauploadtype AS datauploadtype,
|
|
61
|
d.databaseaccessrestriction AS databaseaccessrestriction,
|
|
62
|
d.datauploadrestriction AS datauploadrestriction,
|
|
63
|
d.versioning AS versioning,
|
|
64
|
d.citationguidelineurl AS citationguidelineurl,
|
|
65
|
d.qualitymanagementkind AS qualitymanagementkind,
|
|
66
|
d.pidsystems AS pidsystems,
|
|
67
|
d.certificates AS certificates,
|
|
68
|
ARRAY[]::text[] AS policies,
|
|
69
|
dc.id AS collectedfromid,
|
|
70
|
dc.officialname AS collectedfromname,
|
|
71
|
d.typology || '@@@' || d.typology || '@@@dnet:datasource_typologies@@@dnet:datasource_typologies' AS datasourcetype,
|
|
72
|
'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction
|
|
73
|
|
|
74
|
FROM dsm_datasources d
|
|
75
|
|
|
76
|
LEFT OUTER JOIN dsm_datasources dc on (d.collectedfrom = dc.id)
|
|
77
|
LEFT OUTER JOIN dsm_api a ON (d.id = a.datasource)
|
|
78
|
LEFT OUTER JOIN dsm_datasourcepids di ON (d.id = di.datasource)
|
|
79
|
|
|
80
|
GROUP BY
|
|
81
|
d.id,
|
|
82
|
d.officialname,
|
|
83
|
d.englishname,
|
|
84
|
d.websiteurl,
|
|
85
|
d.logourl,
|
|
86
|
d.contactemail,
|
|
87
|
d.namespaceprefix,
|
|
88
|
d.description,
|
|
89
|
d.latitude,
|
|
90
|
d.longitude,
|
|
91
|
d.dateofcollection,
|
|
92
|
d.dateofvalidation,
|
|
93
|
d.releasestartdate,
|
|
94
|
d.releaseenddate,
|
|
95
|
d.missionstatementurl,
|
|
96
|
d.dataprovider,
|
|
97
|
d.serviceprovider,
|
|
98
|
d.databaseaccesstype,
|
|
99
|
d.datauploadtype,
|
|
100
|
d.databaseaccessrestriction,
|
|
101
|
d.datauploadrestriction,
|
|
102
|
d.versioning,
|
|
103
|
d.citationguidelineurl,
|
|
104
|
d.qualitymanagementkind,
|
|
105
|
d.pidsystems,
|
|
106
|
d.certificates,
|
|
107
|
dc.id,
|
|
108
|
dc.officialname
|