D-Net

-- cleanup the unnecessary functions: the following SQL produces the deletion statement list

SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ');'

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

WHERE ns.nspname = 'public'  order by proname;

-- export the new dnet_dsm schema using

-- pg_dump --schema-only -h localhost -U dnet dnet_dsm | gzip -c > /tmp/dnet_dsm.sql.gz

-- rename tables and index names in order to include prefix "dsm_"

-- import the dsm table schema to the existing openaire database:

-- psql -h localhost -U dnet dnet_openaireplus -f dnet_dsm.sql

-- insert into the new dsm_* tables picking the information from the old tables

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;