D-Net

CREATE TABLE IF NOT EXISTS fundingpaths (

	_dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT 'temp_' || md5(clock_timestamp() :: TEXT) || '_' || md5(random() :: TEXT),

	id                         CHARACTER VARYING(512) PRIMARY KEY,

	path                       TEXT NOT NULL,

	funder                     CHARACTER VARYING(512) REFERENCES organizations (id),

	jurisdiction               CHARACTER VARYING(16),

	description                CHARACTER VARYING(512),

	optional1                  CHARACTER VARYING(255),

	optional2                  CHARACTER VARYING(255)

);

CREATE TABLE IF NOT EXISTS project_fundingpath (

	_dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT 'temp_' || md5(clock_timestamp() :: TEXT) || '_' || md5(random() :: TEXT),

	funding                    CHARACTER VARYING(255) NOT NULL REFERENCES fundingpaths (id),

	project                    CHARACTER VARYING(255) NOT NULL REFERENCES projects (id),

	startdate                  DATE,

	enddate                    DATE,

	semanticclass              CHARACTER VARYING(255) REFERENCES class (code),

	semanticscheme             CHARACTER VARYING(255) REFERENCES scheme (code),

	optional1                  CHARACTER VARYING(255),

	optional2                  CHARACTER VARYING(255),

	PRIMARY KEY (funding, project)

);

UPDATE datasources

SET namespaceprefix = 'wt__________'

WHERE id = 'openaire____::wellcometrust';

-- European Commission FP7

delete from project_fundingpath where funding ='ec__________::EC::FP7::UNKNOWN::UNKNOWN';

delete from fundingpaths where id = 'ec__________::EC::FP7::UNKNOWN::UNKNOWN';

-- FCT

-- copy the good ones from the other db

\copy  (select * from project_fundingpath where project like 'fct\_\_\_\_\_\_\_\_\_%') TO '/tmp/fct_project_fundingpath-prod.sql' CSV;

-- do the following on the bad ones

delete from project_fundingpath where project like 'fct\_\_\_\_\_\_\_\_\_%';

CREATE TEMP TABLE fct (

_dnet_resource_identifier_ VARCHAR(2048),

funding varchar(255),

project varchar(255),

startdate date,

enddate date,

semanticclass varchar(255),

semanticscheme varchar(255),

optional1 varchar(255),

optional2 varchar(255)

);

 \copy fct from '/tmp/fct_project_fundingpath-prod.sql' CSV;

 INSERT INTO project_fundingpath select * from fct;

 -- fct is a temp table and will be dropped when you log out

-- SFI

delete from project_fundingpath where funding = 'sfi_________::SFI::SFI US Ireland R&D Partnership - Planning Grant';

delete from project_fundingpath where funding ='sfi_________::SFI::SFI Stokes Professorship & Lectureship Programme';

delete from project_fundingpath where funding ='sfi_________::SFI::SFI US Ireland R&D Partnership';

delete from project_fundingpath where funding = 'sfi_________::SFI::China / Ireland Science & Technology Collaboration Research Fund�';

delete from fundingpaths  where id = 'sfi_________::SFI::China / Ireland Science & Technology Collaboration Research Fund�';

delete from fundingpaths  where id ='sfi_________::SFI::SFI US Ireland R&D Partnership';

delete from fundingpaths  where id = 'sfi_________::SFI::SFI Stokes Professorship & Lectureship Programme';

delete from fundingpaths  where id = 'sfi_________::SFI::SFI US Ireland R&D Partnership - Planning Grant';

select count(distinct project) from project_fundingpath where project like 'sfi%';

select count(distinct id) from projects where id like 'sfi%';

-- WT

delete from project_fundingpath where project like 'wt%';

delete from fundingpaths where id like 'wt%';

-- SNSF

delete from project_fundingpath where project like 'snsf%';

delete from fundingpaths where id like 'snsf%';

-- ARC

delete from project_fundingpath where project like 'arc%';

delete from fundingpaths where id like 'arc%';

-- RCUK

delete from project_fundingpath where project like 'rcuk%';

delete from fundingpaths where id like 'rcuk%';