D-Net

FROM pg_proc

	INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)

WHERE ns.nspname = 'public'

ORDER BY proname;

INSERT INTO dsm_datasources

	SELECT

		d.id,

		d.officialname,

		d.englishname,

		d.websiteurl,

		d.logourl,

		d.contactemail,

		d.latitude,

		d.longitude,

		d.timezone,

		d.namespaceprefix,

		ARRAY(SELECT trim(s)

		      FROM unnest(string_to_array(d.od_languages, ',')) AS s)    AS languages,

		ARRAY(SELECT trim(s)

		      FROM unnest(string_to_array(d.od_contenttypes, '-')) AS s) AS od_contenttypes,

		d.collectedfrom,

		d.dateofvalidation,

		d.optional1,

		d.optional2,

		d.datasourceclass                                                AS typology,

		d.provenanceactionclass                                          AS provenanceaction,

		d.dateofcollection,

		d.typology                                                       AS platform,

		d.activationid,

		d.description,

		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,

		d.aggregator,

		d.issn,

		d.eissn,

		d.lissn,

		d.registeredby,

		ARRAY_AGG(s.name)                                                AS subjects

	FROM datasources d LEFT OUTER JOIN datasource_subject sd ON (d.id = sd.datasource)

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

	GROUP BY

		d.id;

INSERT INTO dsm_api

	SELECT

		a.id,

		a.protocolclass           AS protocol,

		a.datasource,

		a.contentdescriptionclass AS contentdescription,

		a.active,

		a.removable,

		a.typologyclass           AS typology,

		a.compatibilityclass      AS compatibility,

...

FROM api a LEFT OUTER JOIN apicollections ac ON (a.id = ac.api)

GROUP BY a.id;

INSERT INTO dsm_apiparams

	SELECT

		a.param,

		a.edited ? a.original !,

		a.api

	FROM apicollections

	WHERE ...

INSERT INTO dsm_organizations

	SELECT

		o.id,

		o.legalshortname,

		o.legalname,

		o.websiteurl,

		o.logourl,

		o.ec_legalbody,

		o.ec_legalperson,

		o.ec_nonprofit,

		o.ec_researchorganization,

		o.ec_highereducation,

		o.ec_internationalorganizationeurinterests,

		o.ec_internationalorganization,

		o.ec_enterprise,

		o.ec_smevalidated,

		o.ec_nutscode,

		o.countryclass          AS country,

		o.collectedfrom,

		o.optional1,

		o.optional2,

		o.dateofcollection,

		o.provenanceactionclass AS provenanceaction

	FROM organizations o;

INSERT INTO dsm_datasource_organization

	SELECT

		dorg.datasource,

		dorg.organization

	FROM datasource_organization dorg;

INSERT INTO dsm_identities

	SELECT

		i.pid,

		i.issuertypeclass AS issuertype

	FROM identities i;

INSERT INTO dsm_datasourcepids

	SELECT

		dp.datasource,

		dp.pid

	FROM datasourcepids dp;

INSERT INTO dsm_organizationpids

	SELECT

		op.organization,

		op.pid

	FROM organizationpids op;