D-Net

SELECT d.id 						as datasourceid,

		d.officialname				as officialname,

		d.englishname				as englishname,

		CASE

			ELSE

				'UNKNOWN@@@not available@@@dnet:datasourceCompatibilityLevel@@@dnet:datasourceCompatibilityLevel'

		END as openairecompatibility,

		d.logourl					as logourl,

		d.contactemail				as contactemail,

			WHEN apc.param='baseUrl' THEN

				CASE WHEN COALESCE(apc.edited, '') = ''  THEN apc.original ELSE apc.edited END

			ELSE

				NULL

		END) as accessinfopackage, -- 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.

		d.longitude					as longitude,

		d.namespaceprefix			as namespaceprefix,

		d.od_numberofitems			as odnumberofitems,

		d.od_numberofitemsdate		as odnumberofitemsdate,

		array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) as subjects,

		d.description				as description,

		d.od_policies				as odpolicies,

		ARRAY(select trim(s) from unnest(string_to_array(d.od_languages, ',')) as s)  as odlanguages,

		ARRAY(select trim(s) from unnest(string_to_array(d.od_contenttypes, '-')) as s) as odcontenttypes,

		d.inferred					as inferred,

		d.deletedbyinference		as deletedbyinference,

		d.trust						as trust,

		d.inferenceprovenance		as inferenceprovenance,

		d.dateofcollection			as dateofcollection,

		d.dateofvalidation			as dateofvalidation,

		-- re3data fields

		d.releasestartdate			as releasestartdate,

		d.releaseenddate			as releaseenddate,

		d.missionstatementurl		as missionstatementurl,

		d.dataprovider				as dataprovider,

		d.serviceprovider			as serviceprovider,

		d.databaseaccesstype		as databaseaccesstype,

		d.datauploadtype			as datauploadtype,

		d.databaseaccessrestriction	as databaseaccessrestriction,

		d.datauploadrestriction		as datauploadrestriction,

		d.versioning				as versioning,

		d.citationguidelineurl		as citationguidelineurl,

		d.qualitymanagementkind		as qualitymanagementkind,

		d.pidsystems				as pidsystems,

		d.certificates				as certificates,

		array_agg(DISTINCT p.name || '&&&' || p.url) as policies,

		-- end of re3data fields

		dc.id					as collectedfromid,

		dc.officialname			as collectedfromname,

		tc.code || '@@@' || tc.name || '@@@' || ts.code || '@@@' || ts.name	as datasourcetype,

		pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name	as provenanceaction,

FROM datasources d

	left outer join class tc on (tc.code = d.datasourceclass)

	left outer join scheme ts on (ts.code = d.datasourcescheme)

	left outer join class pac on (pac.code = d.provenanceactionclass)

	left outer join scheme pas on (pas.code = d.provenanceactionscheme)

	left outer join datasourcepids dps on (dps.datasource = d.id)

	left outer join identities i on (i.pid = dps.pid)

	left outer join class idc on (idc.code = i.issuertypeclass)

	left outer join scheme ids on (ids.code = i.issuertypescheme)

	left outer join datasources dc on (dc.id = d.collectedfrom)

	left outer join api on (api.datasource = d.id)

	left outer join apicollections apc on (apc.api = api.id)

	left outer join datasource_subject ds on (ds.datasource = d.id)

	left outer join subjects s on (s.id = ds.subject)

	left outer join class sc on (sc.code = s.semanticclass)

	left outer join scheme ss on (ss.code = s.semanticscheme)

	left outer join datasource_policy dp on (dp.datasource = d.id)

	left outer join policies p on (p.id = dp.policy)

--  These clauses have been commented to allow the import of all the datasource on the index

-- WHERE d.datasourceclass != 'entityregistry'

--	AND d.openairecompatibilityclass IS NOT null

--	AND d.openairecompatibilityclass != 'UNKNOWN'

--	AND d.openairecompatibilityclass != 'notCompatible'

GROUP BY

		d.id,

		d.officialname,

		d.englishname,

		d.websiteurl,

		d.logourl,

		d.contactemail,

		d.namespaceprefix,

		d.description,

		d.od_numberofitems,

		d.od_numberofitemsdate,

		d.od_policies,

		d.od_languages,

		d.od_contenttypes,

		d.latitude,

		d.longitude,

		d.inferred,

		d.deletedbyinference,

		d.trust,

		d.inferenceprovenance,

		d.dateofcollection,

		d.dateofvalidation,

		dc.id,

		d.releasestartdate,

		d.releaseenddate,

		d.missionstatementurl,

		d.dataprovider,

		d.serviceprovider,

		d.databaseaccesstype,

		d.datauploadtype,

		d.databaseaccessrestriction,

		d.datauploadrestriction,

		d.versioning,

		d.citationguidelineurl,

		d.qualitymanagementkind,

		d.pidsystems,

		d.certificates,

		dc.officialname,

		tc.code, tc.name, ts.code, ts.name,

		pac.code, pac.name, pas.code, pas.name,

		sc.code, sc.name, ss.code, ss.name