D-Net

ALTER TABLE datasources

	ADD COLUMN issn CHARACTER VARYING(20);

ALTER TABLE datasources

	ADD COLUMN eissn CHARACTER VARYING(20);

ALTER TABLE datasources

	ADD COLUMN lissn CHARACTER VARYING(20);

ALTER TABLE datasources

	ALTER COLUMN databaseaccesstype TYPE VARCHAR(32);

ALTER TABLE datasources

	ALTER COLUMN datauploadtype TYPE VARCHAR(32);

ALTER TABLE datasources

	ALTER COLUMN databaseaccessrestriction TYPE VARCHAR(32);

ALTER TABLE datasources

	ALTER COLUMN datauploadrestriction TYPE VARCHAR(32);

ALTER TABLE datasources

	ALTER COLUMN qualitymanagementkind TYPE VARCHAR(32);

ALTER TABLE datasources

	ADD COLUMN registeredby CHARACTER VARYING(255);

DELETE FROM apicollections

WHERE api NOT LIKE 'api_________::%';

DELETE FROM api

WHERE id NOT LIKE 'api_________::%';

ALTER TABLE apicollections

	DROP COLUMN name;

ALTER TABLE apicollections

	RENAME COLUMN accessparam TO param;

ALTER TABLE apicollections

	RENAME COLUMN accessvalue TO original;

ALTER TABLE apicollections

	ADD COLUMN edited VARCHAR(255) DEFAULT NULL;

ALTER TABLE apicollections

	ADD COLUMN accessparam BOOLEAN DEFAULT TRUE;

ALTER TABLE apicollections

	DROP CONSTRAINT apicollection_pkey;

ALTER TABLE apicollections

	ALTER COLUMN original SET DEFAULT '';

UPDATE apicollections

SET param = 'set'

WHERE param = 'SET';

UPDATE apicollections

SET param = 'format'

WHERE param = 'FORMAT';

UPDATE apicollections

SET param = 'baseUrl'

WHERE param = 'BASEURL';

-- FIX A PROBLEM WITH MULTIPLE SET

DELETE FROM apicollections

WHERE api = 'api_________::opendoar____::1560::1' AND param = 'set';

INSERT INTO apicollections (param, original, api) VALUES

	('set', 'hdl_10251_3829, hdl_10251_321, hdl_10251_11256, hdl_10251_11206, hdl_10251_8702, hdl_10251_3026, hdl_10251_11076',

	 'api_________::opendoar____::1560::1');

DELETE FROM apicollections

WHERE api = 'api_________::driver______::d0b68287-678d-4efc-b354-c47cbacaed3c::0' AND param = 'set';

INSERT INTO apicollections (param, original, api) VALUES

	('set', 'pub-type:article, pub-type:conf-proceeding, pub-type:lecture, pub-type:paper, pub-type:report',

	 'api_________::driver______::d0b68287-678d-4efc-b354-c47cbacaed3c::0');

DELETE FROM apicollections

WHERE api = 'api_________::driver______::79476713-c428-4f23-bf97-8f27500f754a::0' AND param = 'set';

INSERT INTO apicollections (param, original, api)

VALUES ('set', 'dad:SIQG, jhs,sal:CNF+PA', 'api_________::driver______::79476713-c428-4f23-bf97-8f27500f754a::0');

DELETE FROM apicollections

WHERE api = 'api_________::opendoar____::202::0' AND param = 'set';

INSERT INTO apicollections (param, original, api) VALUES ('set', 'hdl_1887_4540, hdl_1887_4951', 'api_________::opendoar____::202::0');

DELETE FROM apicollections

WHERE api = 'api_________::driver______::c75b9f23-25a8-4e45-aadd-ba449211e2a8::0' AND param = 'set';

INSERT INTO apicollections (param, original, api) VALUES ('set', 'ijdc:ART, ijdc:PAP', 'api_________::driver______::c75b9f23-25a8-4e45-aadd-ba449211e2a8::0');

-- AND FIX

ALTER TABLE apicollections

	ADD CONSTRAINT apicollection_pkey PRIMARY KEY (api, param);

INSERT INTO apicollections (param, original, edited, api) SELECT

	                                                          'baseUrl',

	                                                          COALESCE(original, ''),

	                                                          COALESCE(edited, ''),

	                                                          id

                                                          FROM api

                                                          WHERE originalprotocolclass = editedprotocolclass;

INSERT INTO apicollections (param, original, api) SELECT

	                                                  'baseUrl',

	                                                  COALESCE(original, ''),

	                                                  id

                                                  FROM api

                                                  WHERE originalprotocolclass != editedprotocolclass OR editedprotocolclass IS NULL;

UPDATE apicollections

SET edited = NULL

WHERE edited = '';

UPDATE datasources

SET datasourceclass = 'websource'

WHERE id = 'openaire____::webcrawl';

INSERT INTO apicollections (param, original, api) VALUES ('splitOnElement', 'repository', 'api_________::opendoar::0');

INSERT INTO apicollections (param, original, api) VALUES ('splitOnElement', 'ROW', 'api_________::re3data::0');

INSERT INTO apicollections (param, original, api) VALUES ('splitOnElement', 'Record', 'api_________::wellcometrust::0');

INSERT INTO apicollections (param, original, api) VALUES ('splitOnElement', 'ROW', 'api_________::corda::0');

UPDATE apicollections

SET _dnet_resource_identifier_ = api || '@@' || param;

INSERT INTO scheme (_dnet_resource_identifier_, code, name)

VALUES ('dnet:content_description_typologies', 'dnet:content_description_typologies', 'D-Net Content Description Typologies');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('UNKNOWN', 'UNKNOWN', 'UNKNOWN');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('native', 'native', 'native');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme) VALUES ('native@@dnet:compatibilityLevel', 'native', 'dnet:compatibilityLevel');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('files', 'files', 'files');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme) VALUES ('files@@dnet:compatibilityLevel', 'files', 'dnet:compatibilityLevel');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('metadata', 'metadata', 'metadata');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('file::WoS', 'file::WoS', 'file::WoS');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('file::PDF', 'file::PDF', 'file::PDF');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('file::EuropePMC', 'file::EuropePMC', 'file::EuropePMC');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme)

VALUES ('metadata@@dnet:content_description_typologies', 'metadata', 'dnet:content_description_typologies');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme)

VALUES ('file::WoS@@dnet:content_description_typologies', 'file::WoS', 'dnet:content_description_typologies');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme)

VALUES ('file::PDF@@dnet:content_description_typologies', 'file::PDF', 'dnet:content_description_typologies');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme)

VALUES ('file::EuropePMC@@dnet:content_description_typologies', 'file::EuropePMC', 'dnet:content_description_typologies');

ALTER TABLE api

	ADD COLUMN contentdescriptionclass VARCHAR(255) DEFAULT 'metadata';

ALTER TABLE api

	ADD COLUMN contentdescriptionscheme VARCHAR(255) DEFAULT 'dnet:content_description_typologies';

ALTER TABLE api

	DROP COLUMN editedprotocolclass;

ALTER TABLE api

	DROP COLUMN editedprotocolscheme;

ALTER TABLE api

	DROP COLUMN original;

ALTER TABLE api

	DROP COLUMN edited;

ALTER TABLE api

	DROP COLUMN useedited;

ALTER TABLE api

	RENAME COLUMN originalprotocolclass TO protocolclass;

ALTER TABLE api

	RENAME COLUMN originalprotocolscheme TO protocolscheme;

ALTER TABLE api

	ADD COLUMN active BOOLEAN DEFAULT FALSE;

ALTER TABLE api

	ADD COLUMN removable BOOLEAN DEFAULT FALSE;

ALTER TABLE api

	ADD COLUMN typologyclass VARCHAR(255) DEFAULT 'UNKNOWN';

ALTER TABLE api

	ADD COLUMN typologyscheme VARCHAR(255) DEFAULT 'dnet:datasource_typologies';

ALTER TABLE api

	ADD CONSTRAINT api_typologyclass_fkey FOREIGN KEY (typologyclass) REFERENCES class (code);

ALTER TABLE api

	ADD CONSTRAINT api_typologyscheme_fkey FOREIGN KEY (typologyscheme) REFERENCES scheme (code);

ALTER TABLE api

	ADD COLUMN compatibilityclass VARCHAR(255) DEFAULT 'UNKNOWN';

ALTER TABLE api

	ADD COLUMN compatibilityscheme VARCHAR(255) DEFAULT 'dnet:compatibilityLevel';

ALTER TABLE api

	ADD CONSTRAINT api_compatibilityclass_fkey FOREIGN KEY (compatibilityclass) REFERENCES class (code);

ALTER TABLE api

	ADD CONSTRAINT api_compatibilityscheme_fkey FOREIGN KEY (compatibilityscheme) REFERENCES scheme (code);

ALTER TABLE api

	ADD CONSTRAINT api_contentdescriptionclass_fkey FOREIGN KEY (contentdescriptionclass) REFERENCES class (code);

ALTER TABLE api

	ADD CONSTRAINT api_contentdescriptionscheme_fkey FOREIGN KEY (contentdescriptionscheme) REFERENCES scheme (code);

UPDATE api

SET typologyclass = d.datasourceclass FROM datasources d

WHERE datasource = d.id;

--UPDATE api SET compatibilityclass = 'driver' where typology = 'driver';

--UPDATE api SET compatibilityclass = 'openaire2.0' FROM apicollections ac WHERE typology = 'openaire' AND id = ac.api AND ac.param = 'set' AND ac.original like '%ec_fundedresources%';

--UPDATE api SET compatibilityclass = 'openaire3.0' FROM apicollections ac WHERE typology = 'openaire' AND id = ac.api AND ac.param = 'set' AND ac.original like '%openaire%';

-- Jochen version

UPDATE api

SET compatibilityclass = ds.openairecompatibilityclass FROM datasources ds

WHERE ds.id = api.datasource;

UPDATE api

SET compatibilityclass = 'driver'

WHERE compatibilityclass = 'driver-openaire2.0' AND typology = 'driver';

UPDATE api

SET compatibilityclass = 'openaire2.0'

WHERE compatibilityclass = 'driver-openaire2.0' AND typology = 'openaire';

UPDATE api

SET compatibilityclass = 'UNKNOWN'

WHERE compatibilityclass = 'openaire2.0-openaire3.0'

-- END Jochen

UPDATE api

SET (typologyclass, compatibilityclass) = ('entityregistry', 'native')

WHERE id = 'api_________::opendoar::0';

UPDATE api

SET (typologyclass, compatibilityclass) = ('entityregistry', 'native')

WHERE id = 'api_________::re3data::0';

UPDATE api

SET (typologyclass, compatibilityclass) = ('entityregistry', 'native')

WHERE id = 'api_________::wellcometrust::0';

UPDATE api

SET (typologyclass, compatibilityclass) = ('entityregistry', 'native')

WHERE id = 'api_________::corda::0';

ALTER TABLE api

	DROP COLUMN typology;

ALTER TABLE datasources

	DROP COLUMN openairecompatibilityclass;

ALTER TABLE datasources

	DROP COLUMN openairecompatibilityscheme;

DELETE FROM class_scheme

WHERE scheme = 'dnet:compatibilityLevel' AND class = 'driver-openaire2.0';

DELETE FROM class_scheme

WHERE scheme = 'dnet:compatibilityLevel' AND class = 'driver-openaire2.0-openaire3.0';

DELETE FROM class_scheme

WHERE scheme = 'dnet:compatibilityLevel' AND class = 'driver-openaire3.0';

DELETE FROM class_scheme

WHERE scheme = 'dnet:compatibilityLevel' AND class = 'openaire2.0-openaire3.0';

DELETE FROM class

WHERE code = 'driver-openaire2.0';

DELETE FROM class

WHERE code = 'driver-openaire2.0-openaire3.0';

DELETE FROM class

WHERE code = 'driver-openaire3.0';

DELETE FROM class

WHERE code = 'openaire2.0-openaire3.0';

DELETE FROM apicollections

WHERE api = 'api_________::openaire____::webcrawl::0';

DELETE FROM api

WHERE id = 'api_________::openaire____::webcrawl::0';

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('fileCSV', 'fileCSV', 'fileCSV');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme) VALUES ('fileCSV@@dnet:protocols', 'fileCSV', 'dnet:protocols');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('files_from_mdstore', 'files_from_mdstore', 'files_from_mdstore');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme) VALUES ('files_from_mdstore@@dnet:protocols', 'files_from_mdstore', 'dnet:protocols');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('files_from_metadata', 'files_from_metadata', 'files_from_metadata');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme) VALUES ('files_from_metadata@@dnet:protocols', 'files_from_metadata', 'dnet:protocols');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('files_by_rpc', 'files_by_rpc', 'files_by_rpc');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme) VALUES ('files_by_rpc@@dnet:protocols', 'files_by_rpc', 'dnet:protocols');

INSERT INTO class (_dnet_resource_identifier_, code, name) VALUES ('filesystem', 'filesystem', 'filesystem');

INSERT INTO class_scheme (_dnet_resource_identifier_, class, scheme) VALUES ('filesystem@@dnet:protocols', 'filesystem', 'dnet:protocols');

INSERT INTO api (_dnet_resource_identifier_, id, protocolclass, datasource, typologyclass, compatibilityclass)

VALUES ('api_________::openaire____::webcrawl::csv', 'api_________::openaire____::webcrawl::csv', 'fileCSV', 'openaire____::webcrawl', 'websource', 'native');

INSERT INTO api (_dnet_resource_identifier_, id, protocolclass, datasource, typologyclass, compatibilityclass, contentdescriptionclass) VALUES

	('api_________::openaire____::webcrawl::files', 'api_________::openaire____::webcrawl::files', 'files_from_mdstore', 'openaire____::webcrawl', 'websource',

	 'files', 'file::WoS');

INSERT INTO api (_dnet_resource_identifier_, id, protocolclass, datasource, typologyclass, compatibilityclass, contentdescriptionclass) VALUES

	('api_________::opendoar____::18::files', 'api_________::opendoar____::18::files', 'files_from_metadata', 'opendoar____::18', 'pubsrepository::unknown',

	 'files', 'file::PDF');

INSERT INTO api (_dnet_resource_identifier_, id, protocolclass, datasource, typologyclass, compatibilityclass, contentdescriptionclass) VALUES

	('api_________::opendoar____::908::files', 'api_________::opendoar____::908::files', 'files_from_metadata', 'opendoar____::908', 'pubsrepository::unknown',

	 'files', 'file::PDF');

INSERT INTO api (_dnet_resource_identifier_, id, protocolclass, datasource, typologyclass, compatibilityclass, contentdescriptionclass) VALUES

	('api_________::opendoar____::2367::files', 'api_________::opendoar____::2367::files', 'files_from_metadata', 'opendoar____::2367', 'pubsrepository::unknown',

	 'files', 'file::PDF');

INSERT INTO api (_dnet_resource_identifier_, id, protocolclass, datasource, typologyclass, compatibilityclass, contentdescriptionclass) VALUES

	('api_________::opendoar____::165::files', 'api_________::opendoar____::165::files', 'files_from_metadata', 'opendoar____::165', 'pubsrepository::unknown',

	 'files', 'file::PDF');

INSERT INTO api (_dnet_resource_identifier_, id, protocolclass, datasource, typologyclass, compatibilityclass, contentdescriptionclass) VALUES

	('api_________::infrastruct_::openaire::jdbc', 'api_________::infrastruct_::openaire::jdbc', 'jdbc', 'infrastruct_::openaire', 'scholarcomminfra',

	 'openaire2.0', 'metadata');

INSERT INTO apicollections (param, original, api) VALUES ('baseUrl', 'jdbc:postgresql://localhost:5432', 'api_________::infrastruct_::openaire::jdbc');

INSERT INTO apicollections (param, original, api) VALUES ('dbName', 'dnet_openaireplus', 'api_________::infrastruct_::openaire::jdbc');

INSERT INTO apicollections (param, original, api) VALUES

	('sql', 'SELECT regexp_replace(xml, ''&(?!amp;)'', ''&'', ''g'') as xml FROM claims WHERE type = ''dmf2actions'' and set = ''userclaim_dmf''',

	 'api_________::infrastruct_::openaire::jdbc');

INSERT INTO apicollections (param, original, api) VALUES ('format', 'OAF', 'api_________::infrastruct_::openaire::jdbc');

INSERT INTO apicollections (param, original, api) VALUES ('baseUrl', 'file:///var/lib/dnet/wos/metadata.tsv', 'api_________::openaire____::webcrawl::csv');

INSERT INTO apicollections (param, original, api) VALUES ('separator', E'\\t', 'api_________::openaire____::webcrawl::csv');

INSERT INTO apicollections (param, original, api) VALUES ('identifier', '56', 'api_________::openaire____::webcrawl::csv');

INSERT INTO apicollections (param, original, api) VALUES ('header', 'true', 'api_________::openaire____::webcrawl::csv');

INSERT INTO apicollections (param, original, api) VALUES ('baseUrl', '', 'api_________::openaire____::webcrawl::files');

INSERT INTO apicollections (param, original, api) VALUES ('mdstoreId', '', 'api_________::openaire____::webcrawl::files');

INSERT INTO apicollections (param, original, api) VALUES ('xpath', '//column[./@isID=''true'']', 'api_________::openaire____::webcrawl::files');

INSERT INTO apicollections (param, original, api) VALUES ('baseUrl', '', 'api_________::opendoar____::18::files');

INSERT INTO apicollections (param, original, api) VALUES ('mdstoreId', '', 'api_________::opendoar____::18::files');

INSERT INTO apicollections (param, original, api) VALUES ('xpath', '//dc:identifier', 'api_________::opendoar____::18::files');

INSERT INTO apicollections (param, original, api) VALUES ('baseUrl', '', 'api_________::opendoar____::908::files');

INSERT INTO apicollections (param, original, api) VALUES ('mdstoreId', '', 'api_________::opendoar____::908::files');

INSERT INTO apicollections (param, original, api) VALUES ('xpath', '//dc:identifier', 'api_________::opendoar____::908::files');

INSERT INTO apicollections (param, original, api) VALUES ('baseUrl', '', 'api_________::opendoar____::2367::files');

INSERT INTO apicollections (param, original, api) VALUES ('mdstoreId', '', 'api_________::opendoar____::2367::files');

INSERT INTO apicollections (param, original, api) VALUES ('xpath', '//dc:identifier', 'api_________::opendoar____::2367::files');

INSERT INTO apicollections (param, original, api) VALUES ('baseUrl', '', 'api_________::opendoar____::165::files');

INSERT INTO apicollections (param, original, api) VALUES ('mdstoreId', '', 'api_________::opendoar____::165::files');

INSERT INTO apicollections (param, original, api) VALUES ('xpath', '//dc:identifier', 'api_________::opendoar____::165::files');

INSERT INTO apicollections (param, original, api, accessparam) VALUES ('metadata_identifier_path', '//repository/@rID', 'api_________::opendoar::0', FALSE);

INSERT INTO apicollections (param, original, api, accessparam)

VALUES ('metadata_identifier_path', '//*[local-name()=''repository'']/*[local-name()=''identifier'']', 'api_________::re3data::0', FALSE);

INSERT INTO apicollections (param, original, api, accessparam) VALUES ('metadata_identifier_path', '//Position', 'api_________::wellcometrust::0', FALSE);

INSERT INTO apicollections (param, original, api, accessparam) VALUES ('metadata_identifier_path', '//ProjectId', 'api_________::corda::0', FALSE);

DELETE FROM apicollections

WHERE param = 'splitOnElement' AND api = 'api_________::corda::0';

INSERT INTO apicollections (param, original, api) VALUES ('filter', '*.xml', 'api_________::corda::0');

INSERT INTO apicollections (param, original, api) VALUES ('username', '***', 'api_________::corda::0');

INSERT INTO apicollections (param, original, api) VALUES ('password', '***', 'api_________::corda::0');

INSERT INTO apicollections (param, original, api) VALUES ('recursive', 'false', 'api_________::corda::0');

UPDATE apicollections

SET original = 'ftp://fts.ec.europa.eu'

WHERE api = 'api_________::corda::0' AND param = 'baseUrl';

UPDATE api

SET protocolclass = 'ftp'

WHERE id = 'api_________::corda::0';

UPDATE api

SET protocolclass = 'filesystem'

WHERE id = 'api_________::re3data::0';

UPDATE apicollections

SET original = '/var/lib/dnet/re3data'

WHERE api = 'api_________::re3data::0' AND param = 'baseUrl';

UPDATE api

SET compatibilityclass = 'native'

WHERE id = 'api_________::datacite::0';

-- metadata_identifier_path for Datacite and WoS

INSERT INTO apicollections (param, original, api, accessparam)

VALUES ('metadata_identifier_path', '//*[local-name()=''header'']/*[local-name()=''identifier'']', 'api_________::datacite::0', FALSE);

INSERT INTO apicollections (param, original, api, accessparam)

VALUES ('metadata_identifier_path', '//*[local-name()=''column'' and @isID=''true'']', 'api_________::openaire____::webcrawl::csv', FALSE);

UPDATE apicollections

SET _dnet_resource_identifier_ = api || '@@' || param;

-- get the orgs whose legalshortname is in upper case (probably good sort name, i.e. acronym)

select id, legalshortname from dsm_organizations where id like 're3data%' and legalshortname ~ '^[^a-z]*$';

-- removed legalshortnames of re3data orgs when the legalshortname is not all uppercase

update dsm_organizations set legalshortname = '' where id like 're3data%' and legalshortname !~ '^[^a-z]*$';