D-Net

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

SELECT 'DROP TRIGGER ' || trg.tgname || ' ON ' || tbl.relname || ';'

FROM pg_trigger trg

 JOIN pg_class tbl on trg.tgrelid = tbl.oid

 JOIN pg_namespace ns ON ns.oid = tbl.relnamespace

WHERE trg.tgname like 'notify_%';

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

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

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;

update dsm_api da set metadata_identifier_path = ac.original from apicollections ac where da.id = ac.api and ac.param = 'metadata_identifier_path' ;

update dsm_api da set last_collection_total = ac.original::integer from apicollections ac where da.id = ac.api and ac.param = 'last_collection_total' ;

update dsm_api da set last_collection_date = ac.original::date from apicollections ac where da.id = ac.api and ac.param = 'last_collection_date' ;

update dsm_api da set last_collection_mdid = ac.original from apicollections ac where da.id = ac.api and ac.param = 'last_collection_mdid' ;

update dsm_api da set last_aggregation_total = ac.original::integer from apicollections ac where da.id = ac.api and ac.param = 'last_aggregation_total' ;

update dsm_api da set last_aggregation_date = ac.original::date from apicollections ac where da.id = ac.api and ac.param = 'last_aggregation_date' ;

update dsm_api da set last_aggregation_mdid = ac.original from apicollections ac where da.id = ac.api and ac.param = 'last_aggregation_mdid' ;

update dsm_api da set last_download_total = ac.original::integer from apicollections ac where da.id = ac.api and ac.param = 'last_download_total' ;

update dsm_api da set last_download_date = ac.original::date from apicollections ac where da.id = ac.api and ac.param = 'last_download_date' ;

update dsm_api da set last_download_objid = ac.original from apicollections ac where da.id = ac.api and ac.param = 'last_download_objid' ;

update dsm_api da set last_validation_job = ac.original from apicollections ac where da.id = ac.api and ac.param = 'last_validation_job' ;

update dsm_api da set baseurl = CASE WHEN (ac.edited is not null and ac.edited != '') THEN ac.edited ELSE ac.original END from apicollections ac where da.id = ac.api and ac.param = 'baseUrl' ;

insert into dsm_apiparams

select

	a.param,

	CASE

		WHEN (a.edited is not null and a.edited != '') THEN a.edited ELSE a.original

	END AS value,

	a.api

from apicollections a

where	param != 'baseUrl' AND accessparam is true;

-- !!!!! data insert complete !!!!!

-- TODO add below the deletion statements for the old tables.