|
1
|
-- COPY PROJECTS
|
|
2
|
\copy (select * from projects where id like 'xxx%') TO '/home/alessia.bardi/migrate-projects/xxx/xxx_projects-beta.sql' CSV;
|
|
3
|
-- COPY funding paths
|
|
4
|
\copy (select * from fundingpaths where id like 'xxx%') TO '/home/alessia.bardi/migrate-projects/xxx/xxx_fundingpaths-beta.sql' CSV;
|
|
5
|
-- COPY project_fundingpath
|
|
6
|
\copy (select * from project_fundingpath where project like 'xxx%') TO '/home/alessia.bardi/migrate-projects/xxx/xxx_project_fundingpath-beta.sql' CSV;
|
|
7
|
-- COPY organizations
|
|
8
|
\copy (select * from dsm_organizations where id like 'xxx%') TO '/home/alessia.bardi/migrate-projects/xxx/xxx_orgs-beta.sql' CSV;
|
|
9
|
-- COPY participants links (project_organization)
|
|
10
|
\copy (select * from project_organization where project like 'xxx%') TO '/home/alessia.bardi/migrate-projects/xxx/xxx_project_orgs-beta.sql' CSV;
|
|
11
|
|
|
12
|
CREATE TEMP TABLE xxx_projects (
|
|
13
|
id varchar(255),
|
|
14
|
code varchar(255),
|
|
15
|
websiteurl varchar(255),
|
|
16
|
acronym varchar(255),
|
|
17
|
title text,
|
|
18
|
startdate date,
|
|
19
|
enddate date,
|
|
20
|
call_identifier varchar(255),
|
|
21
|
keywords text,
|
|
22
|
duration integer,
|
|
23
|
ec_sc39 boolean,
|
|
24
|
collectedfrom varchar(255),
|
|
25
|
contracttypeclass varchar(255),
|
|
26
|
contracttypescheme varchar(255),
|
|
27
|
inferred boolean,
|
|
28
|
deletedbyinference boolean,
|
|
29
|
trust double precision,
|
|
30
|
inferenceprovenance varchar(255),
|
|
31
|
optional1 varchar(255),
|
|
32
|
optional2 varchar(255),
|
|
33
|
dateofcollection date,
|
|
34
|
provenanceactionclass varchar(255),
|
|
35
|
provenanceactionscheme varchar(255),
|
|
36
|
_dnet_resource_identifier_ varchar(2048),
|
|
37
|
oa_mandate_for_publications boolean,
|
|
38
|
ec_article29_3 boolean,
|
|
39
|
jsonextrainfo text,
|
|
40
|
contactfullname text,
|
|
41
|
contactfax text,
|
|
42
|
contactphone text,
|
|
43
|
contactemail text,
|
|
44
|
lastupdate date,
|
|
45
|
summary text,
|
|
46
|
currency text,
|
|
47
|
totalcost numeric,
|
|
48
|
fundedamount numeric,
|
|
49
|
contracttypename varchar(255)
|
|
50
|
);
|
|
51
|
|
|
52
|
-- fill it
|
|
53
|
\copy xxx_projects from '/home/alessia.bardi/migrate-projects/xxx/xxx_projects-beta.sql' CSV;
|
|
54
|
|
|
55
|
-- use it to insert into projects
|
|
56
|
|
|
57
|
INSERT INTO projects select * from xxx_projects;
|
|
58
|
|
|
59
|
CREATE TEMP TABLE xxx_fundingpaths (
|
|
60
|
_dnet_resource_identifier_ varchar(2048),
|
|
61
|
id varchar(512),
|
|
62
|
path text,
|
|
63
|
funder varchar(512),
|
|
64
|
jurisdiction varchar(16),
|
|
65
|
description varchar(512),
|
|
66
|
optional1 varchar(255),
|
|
67
|
optional2 varchar(255),
|
|
68
|
funderid varchar(255)
|
|
69
|
);
|
|
70
|
|
|
71
|
-- fill it
|
|
72
|
\copy xxx_fundingpaths from '/home/alessia.bardi/migrate-projects/xxx/xxx_fundingpaths-beta.sql' CSV;
|
|
73
|
|
|
74
|
-- use it to insert
|
|
75
|
INSERT INTO fundingpaths select * from xxx_fundingpaths;
|
|
76
|
|
|
77
|
|
|
78
|
CREATE TEMP TABLE xxx_project_fundingpath (
|
|
79
|
_dnet_resource_identifier_ varchar(2048),
|
|
80
|
funding varchar(255),
|
|
81
|
project varchar(255),
|
|
82
|
startdate date,
|
|
83
|
enddate date,
|
|
84
|
semanticclass varchar(255),
|
|
85
|
semanticscheme varchar(255),
|
|
86
|
optional1 varchar(255),
|
|
87
|
optional2 varchar(255)
|
|
88
|
);
|
|
89
|
|
|
90
|
-- fill it
|
|
91
|
\copy xxx_project_fundingpath from '/home/alessia.bardi/migrate-projects/xxx/xxx_project_fundingpath-beta.sql' CSV;
|
|
92
|
|
|
93
|
-- use it to insert
|
|
94
|
INSERT INTO project_fundingpath select * from xxx_project_fundingpath;
|
|
95
|
|
|
96
|
CREATE TEMP TABLE xxx_orgs (
|
|
97
|
id varchar(255),
|
|
98
|
legalshortname varchar(255),
|
|
99
|
legalname varchar(255),
|
|
100
|
websiteurl text,
|
|
101
|
logourl varchar(255),
|
|
102
|
ec_legalbody boolean,
|
|
103
|
ec_legalperson boolean,
|
|
104
|
ec_nonprofit boolean,
|
|
105
|
ec_researchorganization boolean,
|
|
106
|
ec_highereducation boolean,
|
|
107
|
ec_internationalorganizationeurinterests boolean,
|
|
108
|
ec_internationalorganization boolean,
|
|
109
|
ec_enterprise boolean,
|
|
110
|
ec_smevalidated boolean,
|
|
111
|
ec_nutscode boolean,
|
|
112
|
country varchar(255),
|
|
113
|
collectedfrom varchar(255),
|
|
114
|
optional1 varchar(255),
|
|
115
|
optional2 varchar(255),
|
|
116
|
dateofcollection date,
|
|
117
|
provenanceaction varchar(255),
|
|
118
|
_dnet_resource_identifier_ varchar(2048),
|
|
119
|
lastupdate date,
|
|
120
|
trust double precision
|
|
121
|
);
|
|
122
|
|
|
123
|
-- fill it
|
|
124
|
\copy xxx_orgs from '/home/alessia.bardi/migrate-projects/xxx/xxx_orgs-beta.sql' CSV;
|
|
125
|
|
|
126
|
-- use it to insert
|
|
127
|
INSERT INTO dsm_organizations select * from xxx_orgs;
|
|
128
|
|
|
129
|
CREATE TEMP TABLE xxx_project_orgs (
|
|
130
|
participantnumber int,
|
|
131
|
project varchar(255),
|
|
132
|
resporganization varchar(255),
|
|
133
|
semanticclass varchar(255),
|
|
134
|
trust double precision,
|
|
135
|
_dnet_resource_identifier_ varchar(2048),
|
|
136
|
contribution numeric,
|
|
137
|
currency text
|
|
138
|
);
|
|
139
|
|
|
140
|
-- fill it
|
|
141
|
\copy xxx_project_orgs from '/home/alessia.bardi/migrate-projects/xxx/xxx_project_orgs-beta.sql' CSV;
|
|
142
|
|
|
143
|
-- use it to insert
|
|
144
|
INSERT INTO project_organization select * from xxx_project_orgs;
|