D-Net

-- noinspection SqlNoDataSourceInspectionForFile

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

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

FROM pg_proc

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

WHERE ns.nspname = 'public'

ORDER BY proname;

-- 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_%';

-- 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

ALTER TABLE dsm_datasources ALTER COLUMN officialname TYPE VARCHAR(512);

ALTER TABLE dsm_datasources ALTER COLUMN englishname TYPE VARCHAR(512);

ALTER TABLE dsm_datasources ALTER COLUMN missionstatementurl TYPE VARCHAR(512);

ALTER TABLE dsm_datasources ALTER COLUMN citationguidelineurl TYPE VARCHAR(512);

ALTER TABLE dsm_datasources ALTER COLUMN languages TYPE text;

ALTER TABLE dsm_datasources ALTER COLUMN od_contenttypes TYPE text;

ALTER TABLE dsm_datasources ALTER COLUMN subjects TYPE text;

ALTER TABLE dsm_datasources ALTER COLUMN description TYPE text;

ALTER TABLE dsm_datasources ALTER COLUMN certificates TYPE text;

ALTER TABLE dsm_datasources ALTER COLUMN pidsystems TYPE text;

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

insert into dsm_datasources (id, officialname, englishname, websiteurl, logourl, contactemail, latitude, longitude, timezone, namespaceprefix, languages, od_contenttypes, collectedfrom, dateofvalidation,

                             typology, provenanceaction, dateofcollection, platform, activationid, description,releasestartdate, releaseenddate, missionstatementurl, dataprovider, serviceprovider,

                             databaseaccesstype, datauploadtype,databaseaccessrestriction, datauploadrestriction, versioning, citationguidelineurl, qualitymanagementkind,pidsystems, certificates,

                             aggregator, issn, eissn, lissn, registeredby, subjects)

select d.id, 

	d.officialname, 

	d.englishname, 

	d.websiteurl, 

	d.logourl, 

	d.contactemail, 

	d.latitude, 

	d.longitude, 

	d.timezone, 

	d.namespaceprefix as namespaceprefix,

	d.od_languages as languages,

	d.od_contenttypes,

	d.collectedfrom,

	d.dateofvalidation,

	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 as dataprovider,

	d.serviceprovider as 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 (id, protocol, datasource, contentdescription, active, removable, typology, compatibility)

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 (param,value, api)

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;

INSERT INTO dsm_organizations (id, legalshortname,legalname,websiteurl, logourl,ec_legalbody, ec_legalperson, ec_nonprofit, ec_researchorganization, ec_highereducation, ec_internationalorganizationeurinterests,

                               ec_internationalorganization, ec_enterprise, ec_smevalidated, ec_nutscode, country, collectedfrom, dateofcollection, provenanceaction)

	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.dateofcollection,

		o.provenanceactionclass AS provenanceaction

	FROM organizations o;

INSERT INTO dsm_datasource_organization (datasource, organization)

	SELECT

		dorg.datasource,

		dorg.organization

	FROM datasource_organization dorg;

INSERT INTO dsm_identities (pid, issuertype)

	SELECT

		i.pid,

		i.issuertypeclass AS issuertype

	FROM identities i;

INSERT INTO dsm_datasourcepids (datasource, pid)

	SELECT

		dp.datasource,

		dp.pid

	FROM datasourcepids dp;

-- is this table needed? --

INSERT INTO dsm_organizationpids (organization, pid)

	SELECT

		op.organization,

		op.pid

	FROM organizationpids op;

-- Migrate the managed status

update dsm_datasources d set managed = true where d.id in (

	SELECT d.id

	FROM dsm_datasources d

	LEFT OUTER JOIN dsm_api a ON (d.id = a.datasource)

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

	WHERE a.active = TRUE

	  OR a.removable = TRUE

	  OR ap.edited IS NOT NULL

	GROUP BY d.id );

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

delete from organizations where id like 'opendoar%' ;

delete from organizations where id like 're3data%' ;

delete from organizations where collectedfrom is null ;

delete from dsm_organizations where collectedfrom = 'openaire____::corda'  ;

delete from dsm_organizations where collectedfrom = 'openaire____::snsf'  ;

delete from dsm_organizations where collectedfrom = 'openaire____::conicytf'  ;

delete from dsm_organizations where collectedfrom = 'openaire____::irb_hr' ;

delete from dsm_organizations where collectedfrom = 'openaire____::fwf'  ;

delete from dsm_organizations where collectedfrom = 'openaire____::sfi'  ;

delete from dsm_organizations where collectedfrom = 'openaire____::wellcometrust'  ;

delete from dsm_organizations where collectedfrom = 'openaire____::nsf'  ;

delete from dsm_organizations where collectedfrom = 'openaire____::corda_h2020'  ;