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

UPDATE apicollections SET param = 'set' WHERE param = 'SET';
UPDATE apicollections SET param = 'format' WHERE param = 'FORMAT';

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

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%';
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_________::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;)'', ''&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, 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' ;
UPDATE apicollections SET _dnet_resource_identifier_ = api||'@@'||param;

