D-Net

-- noinspection SqlNoDataSourceInspectionForFile

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

WHERE ns.nspname = 'public'

ORDER BY proname;

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

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

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;

                             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)

	d.officialname,

	d.englishname,

	d.websiteurl,

	d.logourl,

	d.contactemail,

	d.latitude,

	d.longitude,

	d.timezone,

	d.od_contenttypes,

	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.serviceprovider as serviceprovider,

	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;

	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 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_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_download_date = ac.original::date from apicollections ac where da.id = ac.api and ac.param = 'last_download_date' ;

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

	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)

		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;

		dorg.datasource,

		dorg.organization

	FROM datasource_organization dorg;

		i.pid,

		i.issuertypeclass AS issuertype

	FROM identities i;

		dp.datasource,

		dp.pid

	FROM datasourcepids dp;

INSERT INTO dsm_organizationpids (organization, pid)

		op.organization,

		op.pid

	FROM organizationpids op;

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

delete from organizations where id like 're3data%' ;

delete from organizations where collectedfrom is null ;

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