|
1
|
SELECT
|
|
2
|
ds.id,
|
|
3
|
ds.officialname,
|
|
4
|
ds.englishname,
|
|
5
|
ds.websiteurl,
|
|
6
|
ds.logourl,
|
|
7
|
ds.contactemail,
|
|
8
|
ds.latitude,
|
|
9
|
ds.longitude,
|
|
10
|
ds.timezone,
|
|
11
|
ds.namespaceprefix,
|
|
12
|
ds.od_numberofitems,
|
|
13
|
ds.od_numberofitemsdate,
|
|
14
|
ds.od_policies,
|
|
15
|
ds.od_languages,
|
|
16
|
ds.od_contenttypes,
|
|
17
|
ds.collectedfrom,
|
|
18
|
ds.inferred,
|
|
19
|
ds.deletedbyinference,
|
|
20
|
ds.trust,
|
|
21
|
ds.inferenceprovenance,
|
|
22
|
ds.dateofvalidation,
|
|
23
|
ds.registeredby,
|
|
24
|
ds.optional1,
|
|
25
|
ds.optional2,
|
|
26
|
ds.datasourceclass,
|
|
27
|
ds.provenanceactionclass,
|
|
28
|
ds.dateofcollection,
|
|
29
|
ds.typology,
|
|
30
|
ds.activationid,
|
|
31
|
ds.mergehomonyms,
|
|
32
|
ds.description,
|
|
33
|
ds.releasestartdate,
|
|
34
|
ds.releaseenddate,
|
|
35
|
ds.missionstatementurl,
|
|
36
|
ds.dataprovider,
|
|
37
|
ds.serviceprovider,
|
|
38
|
ds.databaseaccesstype,
|
|
39
|
ds.datauploadtype,
|
|
40
|
ds.databaseaccessrestriction,
|
|
41
|
ds.datauploadrestriction,
|
|
42
|
ds.versioning,
|
|
43
|
ds.citationguidelineurl,
|
|
44
|
ds.qualitymanagementkind,
|
|
45
|
ds.pidsystems,
|
|
46
|
ds.certificates,
|
|
47
|
ds.aggregator,
|
|
48
|
ds.issn,
|
|
49
|
ds.eissn,
|
|
50
|
ds.lissn,
|
|
51
|
(array_agg(o.legalname))[1] as organization,
|
|
52
|
(array_agg(ccl.code))[1] as countrycode,
|
|
53
|
(array_agg(ccl.name))[1] as countryname,
|
|
54
|
array_agg(distinct ag.accessinfopackage) as accessinfopackage,
|
|
55
|
to_char(now(), 'YYYY-MM-DDThh24:mi:ssZ') AS now
|
|
56
|
|
|
57
|
FROM datasources ds
|
|
58
|
LEFT OUTER JOIN datasource_organization dso ON ds.id = dso.datasource
|
|
59
|
LEFT OUTER JOIN organizations o ON dso.organization = o.id
|
|
60
|
LEFT OUTER JOIN class ccl ON ccl.code = o.countryclass
|
|
61
|
LEFT OUTER JOIN (
|
|
62
|
SELECT api.datasource, api.contentdescriptionclass, api.compatibilityclass,
|
|
63
|
COALESCE(api.id, '')||'<==1==>'||COALESCE(api.typologyclass, '')||'<==2==>'||COALESCE(api.compatibilityclass, '')||'<==3==>'||COALESCE(api.contentdescriptionclass,'')||'<==4==>'||COALESCE(api.protocolclass,'')||'<==5==>'||COALESCE(api.active,false)||'<==6==>'||COALESCE(api.removable,false)||'<==7==>'||array_to_string(array_agg(distinct COALESCE(ac.accessparam, true)||'###'||COALESCE(ac.param,'')||'###'||COALESCE(ac.value,'')||'###'), '@@@') AS accessinfopackage
|
|
64
|
FROM api
|
|
65
|
LEFT OUTER JOIN (
|
|
66
|
SELECT api, param, accessparam, CASE WHEN edited IS NULL THEN original ELSE edited END as value FROM apicollections
|
|
67
|
) AS ac ON (ac.api = api.id)
|
|
68
|
GROUP BY
|
|
69
|
api.id,
|
|
70
|
api.datasource,
|
|
71
|
api.contentdescriptionclass,
|
|
72
|
api.typologyclass,
|
|
73
|
api.compatibilityclass,
|
|
74
|
api.protocolclass,
|
|
75
|
api.active,
|
|
76
|
api.removable
|
|
77
|
) AS ag on (ag.datasource = ds.id)
|
|
78
|
|
|
79
|
$if(condition)$
|
|
80
|
WHERE $condition$
|
|
81
|
$endif$
|
|
82
|
|
|
83
|
GROUP BY
|
|
84
|
ds.id,
|
|
85
|
ds.officialname,
|
|
86
|
ds.englishname,
|
|
87
|
ds.websiteurl,
|
|
88
|
ds.logourl,
|
|
89
|
ds.contactemail,
|
|
90
|
ds.latitude,
|
|
91
|
ds.longitude,
|
|
92
|
ds.timezone,
|
|
93
|
ds.namespaceprefix,
|
|
94
|
ds.od_numberofitems,
|
|
95
|
ds.od_numberofitemsdate,
|
|
96
|
ds.od_policies,
|
|
97
|
ds.od_languages,
|
|
98
|
ds.od_contenttypes,
|
|
99
|
ds.collectedfrom,
|
|
100
|
ds.inferred,
|
|
101
|
ds.deletedbyinference,
|
|
102
|
ds.trust,
|
|
103
|
ds.inferenceprovenance,
|
|
104
|
ds.dateofvalidation,
|
|
105
|
ds.registeredby,
|
|
106
|
ds.optional1,
|
|
107
|
ds.optional2,
|
|
108
|
ds.datasourceclass,
|
|
109
|
ds.provenanceactionclass,
|
|
110
|
ds.dateofcollection,
|
|
111
|
ds.typology,
|
|
112
|
ds.activationid,
|
|
113
|
ds.mergehomonyms,
|
|
114
|
ds.description,
|
|
115
|
ds.releasestartdate,
|
|
116
|
ds.releaseenddate,
|
|
117
|
ds.missionstatementurl,
|
|
118
|
ds.dataprovider,
|
|
119
|
ds.serviceprovider,
|
|
120
|
ds.databaseaccesstype,
|
|
121
|
ds.datauploadtype,
|
|
122
|
ds.databaseaccessrestriction,
|
|
123
|
ds.datauploadrestriction,
|
|
124
|
ds.versioning,
|
|
125
|
ds.citationguidelineurl,
|
|
126
|
ds.qualitymanagementkind,
|
|
127
|
ds.pidsystems,
|
|
128
|
ds.certificates,
|
|
129
|
ds.aggregator,
|
|
130
|
ds.issn,
|
|
131
|
ds.eissn,
|
|
132
|
ds.lissn
|