Project

General

Profile

« Previous | Next » 

Revision 59012

SQL commands to copy projects from beta to prod

View differences:

modules/dnet-openaireplus-workflows/trunk/src/main/resources/eu/dnetlib/patch-db/migrate-projects.sql
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 project 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;

Also available in: Unified diff