| 1 |
48139
|
alessia.ba
|
SELECT
|
| 2 |
|
|
d.id AS datasourceid,
|
| 3 |
50489
|
michele.ar
|
d.id || array_agg(di.pid) AS identities,
|
| 4 |
48139
|
alessia.ba
|
d.officialname AS officialname,
|
| 5 |
|
|
d.englishname AS englishname,
|
| 6 |
|
|
CASE
|
| 7 |
50489
|
michele.ar
|
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 |
48139
|
alessia.ba
|
'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 |
50489
|
michele.ar
|
array_agg(DISTINCT CASE WHEN a.protocol = 'oai' THEN a.baseurl ELSE NULL END) AS accessinfopackage,
|
| 37 |
48139
|
alessia.ba
|
d.latitude AS latitude,
|
| 38 |
|
|
d.longitude AS longitude,
|
| 39 |
|
|
d.namespaceprefix AS namespaceprefix,
|
| 40 |
50489
|
michele.ar
|
NULL AS odnumberofitems,
|
| 41 |
|
|
NULL AS odnumberofitemsdate,
|
| 42 |
51591
|
claudio.at
|
|
| 43 |
|
|
(SELECT array_agg(s|| '###keywords@@@keywords@@@dnet:subject_classification_typologies@@@dnet:subject_classification_typologies')
|
| 44 |
|
|
FROM UNNEST(
|
| 45 |
|
|
ARRAY(
|
| 46 |
|
|
SELECT trim(s)
|
| 47 |
|
|
FROM unnest(string_to_array(d.subjects, '@@')) AS s)) AS s) AS subjects,
|
| 48 |
|
|
|
| 49 |
48139
|
alessia.ba
|
d.description AS description,
|
| 50 |
50489
|
michele.ar
|
NULL AS odpolicies,
|
| 51 |
51591
|
claudio.at
|
ARRAY(SELECT trim(s)
|
| 52 |
|
|
FROM unnest(string_to_array(d.languages, ',')) AS s) AS odlanguages,
|
| 53 |
|
|
ARRAY(SELECT trim(s)
|
| 54 |
|
|
FROM unnest(string_to_array(d.od_contenttypes, '-')) AS s) AS odcontenttypes,
|
| 55 |
50489
|
michele.ar
|
false AS inferred,
|
| 56 |
|
|
false AS deletedbyinference,
|
| 57 |
|
|
0.9 AS trust,
|
| 58 |
|
|
NULL AS inferenceprovenance,
|
| 59 |
48139
|
alessia.ba
|
d.dateofcollection AS dateofcollection,
|
| 60 |
|
|
d.dateofvalidation AS dateofvalidation,
|
| 61 |
50489
|
michele.ar
|
-- re3data fields
|
| 62 |
48139
|
alessia.ba
|
d.releasestartdate AS releasestartdate,
|
| 63 |
|
|
d.releaseenddate AS releaseenddate,
|
| 64 |
|
|
d.missionstatementurl AS missionstatementurl,
|
| 65 |
|
|
d.dataprovider AS dataprovider,
|
| 66 |
|
|
d.serviceprovider AS serviceprovider,
|
| 67 |
|
|
d.databaseaccesstype AS databaseaccesstype,
|
| 68 |
|
|
d.datauploadtype AS datauploadtype,
|
| 69 |
|
|
d.databaseaccessrestriction AS databaseaccessrestriction,
|
| 70 |
|
|
d.datauploadrestriction AS datauploadrestriction,
|
| 71 |
|
|
d.versioning AS versioning,
|
| 72 |
|
|
d.citationguidelineurl AS citationguidelineurl,
|
| 73 |
|
|
d.qualitymanagementkind AS qualitymanagementkind,
|
| 74 |
|
|
d.pidsystems AS pidsystems,
|
| 75 |
|
|
d.certificates AS certificates,
|
| 76 |
50489
|
michele.ar
|
ARRAY[]::text[] AS policies,
|
| 77 |
48139
|
alessia.ba
|
dc.id AS collectedfromid,
|
| 78 |
|
|
dc.officialname AS collectedfromname,
|
| 79 |
51596
|
claudio.at
|
d.typology || '@@@' || CASE
|
| 80 |
|
|
WHEN (d.typology = 'crissystem') THEN 'CRIS System'
|
| 81 |
|
|
WHEN (d.typology = 'datarepository::unknown') THEN 'Data Repository'
|
| 82 |
|
|
WHEN (d.typology = 'aggregator::datarepository') THEN 'Data Repository Aggregator'
|
| 83 |
|
|
WHEN (d.typology = 'infospace') THEN 'Information Space'
|
| 84 |
|
|
WHEN (d.typology = 'pubsrepository::institutional') THEN 'Institutional Repository'
|
| 85 |
|
|
WHEN (d.typology = 'aggregator::pubsrepository::institutional') THEN 'Institutional Repository Aggregator'
|
| 86 |
|
|
WHEN (d.typology = 'pubsrepository::journal') THEN 'Journal'
|
| 87 |
|
|
WHEN (d.typology = 'aggregator::pubsrepository::journals') THEN 'Journal Aggregator/Publisher'
|
| 88 |
|
|
WHEN (d.typology = 'pubsrepository::mock') THEN 'Other'
|
| 89 |
|
|
WHEN (d.typology = 'pubscatalogue::unknown') THEN 'Publication Catalogue'
|
| 90 |
|
|
WHEN (d.typology = 'pubsrepository::unknown') THEN 'Publication Repository'
|
| 91 |
|
|
WHEN (d.typology = 'aggregator::pubsrepository::unknown') THEN 'Publication Repository Aggregator'
|
| 92 |
|
|
WHEN (d.typology = 'entityregistry') THEN 'Registry'
|
| 93 |
|
|
WHEN (d.typology = 'scholarcomminfra') THEN 'Scholarly Comm. Infrastructure'
|
| 94 |
|
|
WHEN (d.typology = 'pubsrepository::thematic') THEN 'Thematic Repository'
|
| 95 |
|
|
WHEN (d.typology = 'websource') THEN 'Web Source'
|
| 96 |
|
|
WHEN (d.typology = 'entityregistry::projects') THEN 'Funder database'
|
| 97 |
|
|
WHEN (d.typology = 'entityregistry::repositories') THEN 'Registry of repositories'
|
| 98 |
51906
|
alessia.ba
|
WHEN (d.typology = 'softwarerepository') THEN 'Software Repository'
|
| 99 |
51914
|
alessia.ba
|
WHEN (d.typology = 'aggregator::softwarerepository') THEN 'Software Repository Aggregator'
|
| 100 |
51906
|
alessia.ba
|
WHEN (d.typology = 'orprepository') THEN 'Repository'
|
| 101 |
51596
|
claudio.at
|
ELSE 'Other'
|
| 102 |
|
|
END || '@@@dnet:datasource_typologies@@@dnet:datasource_typologies' AS datasourcetype,
|
| 103 |
50489
|
michele.ar
|
'sysimport:crosswalk:entityregistry@@@sysimport:crosswalk:entityregistry@@@dnet:provenance_actions@@@dnet:provenance_actions' AS provenanceaction
|
| 104 |
48139
|
alessia.ba
|
|
| 105 |
50489
|
michele.ar
|
FROM dsm_datasources d
|
| 106 |
48139
|
alessia.ba
|
|
| 107 |
50489
|
michele.ar
|
LEFT OUTER JOIN dsm_datasources dc on (d.collectedfrom = dc.id)
|
| 108 |
|
|
LEFT OUTER JOIN dsm_api a ON (d.id = a.datasource)
|
| 109 |
|
|
LEFT OUTER JOIN dsm_datasourcepids di ON (d.id = di.datasource)
|
| 110 |
48139
|
alessia.ba
|
|
| 111 |
|
|
GROUP BY
|
| 112 |
|
|
d.id,
|
| 113 |
|
|
d.officialname,
|
| 114 |
|
|
d.englishname,
|
| 115 |
|
|
d.websiteurl,
|
| 116 |
|
|
d.logourl,
|
| 117 |
|
|
d.contactemail,
|
| 118 |
|
|
d.namespaceprefix,
|
| 119 |
|
|
d.description,
|
| 120 |
|
|
d.latitude,
|
| 121 |
|
|
d.longitude,
|
| 122 |
|
|
d.dateofcollection,
|
| 123 |
|
|
d.dateofvalidation,
|
| 124 |
|
|
d.releasestartdate,
|
| 125 |
|
|
d.releaseenddate,
|
| 126 |
|
|
d.missionstatementurl,
|
| 127 |
|
|
d.dataprovider,
|
| 128 |
|
|
d.serviceprovider,
|
| 129 |
|
|
d.databaseaccesstype,
|
| 130 |
|
|
d.datauploadtype,
|
| 131 |
|
|
d.databaseaccessrestriction,
|
| 132 |
|
|
d.datauploadrestriction,
|
| 133 |
|
|
d.versioning,
|
| 134 |
|
|
d.citationguidelineurl,
|
| 135 |
|
|
d.qualitymanagementkind,
|
| 136 |
|
|
d.pidsystems,
|
| 137 |
|
|
d.certificates,
|
| 138 |
50489
|
michele.ar
|
dc.id,
|
| 139 |
|
|
dc.officialname
|