|
1
|
SELECT d.id as datasourceid,
|
|
2
|
d.officialname as officialname,
|
|
3
|
d.englishname as englishname,
|
|
4
|
d.websiteurl as websiteurl,
|
|
5
|
d.logourl as logourl,
|
|
6
|
d.contactemail as contactemail,
|
|
7
|
array_agg(DISTINCT CASE WHEN COALESCE(apc.edited, '') = '' THEN apc.original ELSE apc.edited END) as accessinfopackage,
|
|
8
|
d.latitude as latitude,
|
|
9
|
d.longitude as longitude,
|
|
10
|
d.namespaceprefix as namespaceprefix,
|
|
11
|
d.od_numberofitems as odnumberofitems,
|
|
12
|
d.od_numberofitemsdate as odnumberofitemsdate,
|
|
13
|
array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) as subjects,
|
|
14
|
d.description as description,
|
|
15
|
d.od_policies as odpolicies,
|
|
16
|
ARRAY(select trim(s) from unnest(string_to_array(d.od_languages, ',')) as s) as odlanguages,
|
|
17
|
ARRAY(select trim(s) from unnest(string_to_array(d.od_contenttypes, '-')) as s) as odcontenttypes,
|
|
18
|
d.inferred as inferred,
|
|
19
|
d.deletedbyinference as deletedbyinference,
|
|
20
|
d.trust as trust,
|
|
21
|
d.inferenceprovenance as inferenceprovenance,
|
|
22
|
d.dateofcollection as dateofcollection,
|
|
23
|
d.dateofvalidation as dateofvalidation,
|
|
24
|
|
|
25
|
-- re3data fields
|
|
26
|
d.releasestartdate as releasestartdate,
|
|
27
|
d.releaseenddate as releaseenddate,
|
|
28
|
d.missionstatementurl as missionstatementurl,
|
|
29
|
d.dataprovider as dataprovider,
|
|
30
|
d.serviceprovider as serviceprovider,
|
|
31
|
d.databaseaccesstype as databaseaccesstype,
|
|
32
|
d.datauploadtype as datauploadtype,
|
|
33
|
d.databaseaccessrestriction as databaseaccessrestriction,
|
|
34
|
d.datauploadrestriction as datauploadrestriction,
|
|
35
|
d.versioning as versioning,
|
|
36
|
d.citationguidelineurl as citationguidelineurl,
|
|
37
|
d.qualitymanagementkind as qualitymanagementkind,
|
|
38
|
d.pidsystems as pidsystems,
|
|
39
|
d.certificates as certificates,
|
|
40
|
array_agg(DISTINCT p.name || '&&&' || p.url) as policies,
|
|
41
|
-- end of re3data fields
|
|
42
|
|
|
43
|
dc.id as collectedfromid,
|
|
44
|
dc.officialname as collectedfromname,
|
|
45
|
|
|
46
|
tc.code || '@@@' || tc.name || '@@@' || ts.code || '@@@' || ts.name as datasourcetype,
|
|
47
|
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name as provenanceaction,
|
|
48
|
occ.code || '@@@' || occ.name || '@@@' || ocs.code || '@@@' || ocs.name as openairecompatibility,
|
|
49
|
|
|
50
|
array_agg(DISTINCT i.pid || '###' || idc.code || '@@@' || idc.name || '@@@' || ids.code || '@@@' || ids.name) as pid
|
|
51
|
|
|
52
|
|
|
53
|
FROM datasources d
|
|
54
|
left outer join class tc on (tc.code = d.datasourceclass)
|
|
55
|
left outer join scheme ts on (ts.code = d.datasourcescheme)
|
|
56
|
|
|
57
|
left outer join class pac on (pac.code = d.provenanceactionclass)
|
|
58
|
left outer join scheme pas on (pas.code = d.provenanceactionscheme)
|
|
59
|
|
|
60
|
left outer join datasourcepids dps on (dps.datasource = d.id)
|
|
61
|
left outer join identities i on (i.pid = dps.pid)
|
|
62
|
|
|
63
|
left outer join class idc on (idc.code = i.issuertypeclass)
|
|
64
|
left outer join scheme ids on (ids.code = i.issuertypescheme)
|
|
65
|
|
|
66
|
left outer join datasources dc on (dc.id = d.collectedfrom)
|
|
67
|
left outer join api on (api.datasource = d.id)
|
|
68
|
left outer join apicollections apc on (apc.api = api.id)
|
|
69
|
|
|
70
|
left outer join class occ on (occ.code = api.compatibilityclass)
|
|
71
|
left outer join scheme ocs on (ocs.code = api.compatibilityscheme)
|
|
72
|
|
|
73
|
left outer join datasource_subject ds on (ds.datasource = d.id)
|
|
74
|
left outer join subjects s on (s.id = ds.subject)
|
|
75
|
|
|
76
|
left outer join class sc on (sc.code = s.semanticclass)
|
|
77
|
left outer join scheme ss on (ss.code = s.semanticscheme)
|
|
78
|
|
|
79
|
left outer join datasource_policy dp on (dp.datasource = d.id)
|
|
80
|
left outer join policies p on (p.id = dp.policy)
|
|
81
|
|
|
82
|
|
|
83
|
WHERE apc.param = 'baseUrl'
|
|
84
|
-- These clauses have been commented to allow the import of all the datasource on the index
|
|
85
|
-- WHERE d.datasourceclass != 'entityregistry'
|
|
86
|
-- AND d.openairecompatibilityclass IS NOT null
|
|
87
|
-- AND d.openairecompatibilityclass != 'UNKNOWN'
|
|
88
|
-- AND d.openairecompatibilityclass != 'notCompatible'
|
|
89
|
|
|
90
|
GROUP BY
|
|
91
|
d.id,
|
|
92
|
d.officialname,
|
|
93
|
d.englishname,
|
|
94
|
d.websiteurl,
|
|
95
|
d.logourl,
|
|
96
|
d.contactemail,
|
|
97
|
d.namespaceprefix,
|
|
98
|
d.description,
|
|
99
|
d.od_numberofitems,
|
|
100
|
d.od_numberofitemsdate,
|
|
101
|
d.od_policies,
|
|
102
|
d.od_languages,
|
|
103
|
d.od_contenttypes,
|
|
104
|
d.latitude,
|
|
105
|
d.longitude,
|
|
106
|
d.inferred,
|
|
107
|
d.deletedbyinference,
|
|
108
|
d.trust,
|
|
109
|
d.inferenceprovenance,
|
|
110
|
d.dateofcollection,
|
|
111
|
d.dateofvalidation,
|
|
112
|
dc.id,
|
|
113
|
d.releasestartdate,
|
|
114
|
d.releaseenddate,
|
|
115
|
d.missionstatementurl,
|
|
116
|
d.dataprovider,
|
|
117
|
d.serviceprovider,
|
|
118
|
d.databaseaccesstype,
|
|
119
|
d.datauploadtype,
|
|
120
|
d.databaseaccessrestriction,
|
|
121
|
d.datauploadrestriction,
|
|
122
|
d.versioning,
|
|
123
|
d.citationguidelineurl,
|
|
124
|
d.qualitymanagementkind,
|
|
125
|
d.pidsystems,
|
|
126
|
d.certificates,
|
|
127
|
dc.officialname,
|
|
128
|
tc.code, tc.name, ts.code, ts.name,
|
|
129
|
occ.code, occ.name, ocs.code, ocs.name,
|
|
130
|
pac.code, pac.name, pas.code, pas.name,
|
|
131
|
sc.code, sc.name, ss.code, ss.name
|