D-Net

	SELECT

		p.id               AS projectid,

		p.acronym          AS acronym,

		p.code             AS code,

		p.optional1        AS optional1,

		p.optional2        AS optional2,

		p.jsonextrainfo    AS jsonextrainfo,

		array_agg(fp.path) AS fundingpath

	FROM projects p

		LEFT OUTER JOIN project_fundingpath pf ON (pf.project = p.id)

		LEFT OUTER JOIN fundingpaths fp ON (fp.id = pf.funding)

	GROUP BY p.id

	SELECT

		pr.code,

		pr.acronym,

		pr.title,

		pr.call_identifier,

		pr.startdate,

		pr.enddate,

		pr.ec_sc39,

		pr.oa_mandate_for_publications,

		pr.ec_article29_3,

		f.id        AS fundingpathid,

		f.description,

		org.legalname,

		org.countryclass,

		CASE WHEN po.participantnumber = 1

			THEN 'coordinator'

		ELSE '' END AS role,

		pe.firstname,

		pe.secondnames,

		pe.email

	FROM projects pr

		LEFT OUTER JOIN project_organization po ON (pr.id = po.project)

		LEFT OUTER JOIN persons pe ON (po.contactperson = pe.id)

		LEFT OUTER JOIN organizations org ON (po.resporganization = org.id)

		LEFT OUTER JOIN project_fundingpath pf ON (pr.id = pf.project)

		LEFT OUTER JOIN fundingpaths f ON (pf.funding = f.id)

	ORDER BY pr.acronym);

CREATE INDEX projects_tsv_fundingpathid_idx

	ON projects_tsv (fundingpathid);

CREATE INDEX projects_tsv_ec_article29_3_idx

	ON projects_tsv (ec_article29_3);

	SELECT

		org.legalshortname AS funder,

		fp.jurisdiction    AS jurisdiction,

		p.acronym          AS acronym,

		p.title            AS title,

		p.code             AS code,

		p.startdate        AS startdate,

		p.enddate          AS enddate,

		fp.id              AS fundingpathid

	FROM projects p

		LEFT OUTER JOIN project_fundingpath pfp ON (p.id = pfp.project)

		LEFT OUTER JOIN fundingpaths fp ON (pfp.funding = fp.id)

		LEFT OUTER JOIN organizations org ON (org.id = fp.funder));

CREATE INDEX projects_api_fundingpathid_idx

	ON projects_api (fundingpathid);

CREATE INDEX projects_api_startdate_idx

	ON projects_api (startdate);

CREATE INDEX projects_api_enddate_idx

	ON projects_api (enddate);

		(array_agg(o.legalname)) [1]             AS organization,

		(array_agg(ccl.code)) [1]                AS countrycode,

		(array_agg(ccl.name)) [1]                AS countryname,

		array_agg(DISTINCT ag.accessinfopackage) AS accessinfopackage,

			                SELECT

				                api.datasource,

				                api.contentdescriptionclass,

				                api.compatibilityclass,

				                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

			                FROM api

				                LEFT OUTER JOIN (

					                                SELECT

						                                api,

						                                param,

						                                accessparam,

						                                CASE WHEN edited IS NULL

							                                THEN original

						                                ELSE edited END AS value

					                                FROM apicollections

				                                ) AS ac ON (ac.api = api.id)

			                GROUP BY

				                api.id,

				                api.datasource,

				                api.contentdescriptionclass,

				                api.typologyclass,

				                api.compatibilityclass,

				                api.protocolclass,

				                api.active,

				                api.removable

		                ) AS ag ON (ag.datasource = ds.id)

	GROUP BY

		ds.id,

		ds.officialname,

		ds.englishname,

		ds.websiteurl,

		ds.logourl,

		ds.contactemail,

		ds.latitude,

		ds.longitude,

		ds.timezone,

		ds.namespaceprefix,

		ds.collectedfrom,

		ds.dateofvalidation,

		ds.registeredby,

		ds.datasourceclass,

		ds.provenanceactionclass,

		ds.dateofcollection,

		ds.typology,

		ds.activationid,

		ds.description,

		ds.aggregator,

		ds.issn,

		ds.eissn,

		ds.lissn

CREATE INDEX dashboard_ds_id_idx

	ON dashboard_ds (id);

DROP MATERIALIZED VIEW projects_mv;

CREATE MATERIALIZED VIEW projects_mv AS (

	SELECT

		p.id                                                                                                       AS projectid,

		p.code                                                                                                     AS code,

		p.websiteurl                                                                                               AS websiteurl,

		p.acronym                                                                                                  AS acronym,

		p.title                                                                                                    AS title,

		p.startdate                                                                                                AS startdate,

		p.enddate                                                                                                  AS enddate,

		p.call_identifier                                                                                          AS callidentifier,

		p.keywords                                                                                                 AS keywords,

		p.duration                                                                                                 AS duration,

		p.ec_sc39                                                                                                  AS ecsc39,

		p.oa_mandate_for_publications                                                                              AS oamandatepublications,

		p.ec_article29_3                                                                                           AS ecarticle29_3,

		p.dateofcollection                                                                                         AS dateofcollection,

		p.inferred                                                                                                 AS inferred,

		p.deletedbyinference                                                                                       AS deletedbyinference,

		p.trust                                                                                                    AS trust,

		p.inferenceprovenance                                                                                      AS inferenceprovenance,

		p.optional1                                                                                                AS optional1,

		p.optional2                                                                                                AS optional2,

		p.jsonextrainfo                                                                                            AS jsonextrainfo,

		dc.id                                                                                                      AS collectedfromid,

		dc.officialname                                                                                            AS collectedfromname,

		cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name                                        AS contracttype,

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

		array_agg(DISTINCT i.pid || '###' || i.issuertypeclass)                                                    AS pid,

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

		array_agg(fp.path)                                                                                         AS fundingtree

	FROM projects p

		LEFT OUTER JOIN class cc ON (cc.code = p.contracttypeclass)

		LEFT OUTER JOIN scheme cs ON (cs.code = p.contracttypescheme)

		LEFT OUTER JOIN class pac ON (pac.code = p.provenanceactionclass)

		LEFT OUTER JOIN scheme pas ON (pas.code = p.provenanceactionscheme)

		LEFT OUTER JOIN projectpids pp ON (pp.project = p.id)

		LEFT OUTER JOIN identities i ON (i.pid = pp.pid)

		LEFT OUTER JOIN datasources dc ON (dc.id = p.collectedfrom)

		LEFT OUTER JOIN project_fundingpath pf ON (pf.project = p.id)

		LEFT OUTER JOIN fundingpaths fp ON (fp.id = pf.funding)

		LEFT OUTER JOIN project_subject ps ON (ps.project = p.id)

		LEFT OUTER JOIN subjects s ON (s.id = ps.subject)

		LEFT OUTER JOIN class sc ON (sc.code = s.semanticclass)

		LEFT OUTER JOIN scheme ss ON (ss.code = s.semanticscheme)

	GROUP BY

		p.id,

		p.code,

		p.websiteurl,

		p.acronym,

		p.title,

		p.startdate,

		p.enddate,

		p.call_identifier,

		p.keywords,

		p.duration,

		p.ec_sc39,

		p.oa_mandate_for_publications,

		p.ec_article29_3,

		p.dateofcollection,

		p.inferred,

		p.deletedbyinference,

		p.trust,

		p.inferenceprovenance,

		dc.id,

		dc.officialname,

		cc.code, cc.name, cs.code, cs.name,

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

);

CREATE INDEX projects_mv_id_idx

	ON projects_mv (projectid);