1
|
-- noinspection SqlNoDataSourceInspectionForFile
|
2
|
|
3
|
-- cleanup the unnecessary functions: the following SQL produces the deletion statement list
|
4
|
SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ') CASCADE;'
|
5
|
FROM pg_proc
|
6
|
INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
|
7
|
WHERE ns.nspname = 'public'
|
8
|
ORDER BY proname;
|
9
|
|
10
|
-- cleanup the unnecessary triggers: the following SQL produces the deletion statement list
|
11
|
SELECT 'DROP TRIGGER ' || trg.tgname || ' ON ' || tbl.relname || ';'
|
12
|
FROM pg_trigger trg
|
13
|
JOIN pg_class tbl on trg.tgrelid = tbl.oid
|
14
|
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
|
15
|
WHERE trg.tgname like 'notify_%';
|
16
|
|
17
|
-- export the new dnet_dsm schema using
|
18
|
-- pg_dump --schema-only -h localhost -U dnet dnet_dsm | gzip -c > /tmp/dnet_dsm.sql.gz
|
19
|
-- rename tables and index names in order to include prefix "dsm_"
|
20
|
-- import the dsm table schema to the existing openaire database:
|
21
|
-- psql -h localhost -U dnet dnet_openaireplus -f dnet_dsm.sql
|
22
|
|
23
|
ALTER TABLE dsm_datasources ALTER COLUMN officialname TYPE VARCHAR(512);
|
24
|
ALTER TABLE dsm_datasources ALTER COLUMN englishname TYPE VARCHAR(512);
|
25
|
ALTER TABLE dsm_datasources ALTER COLUMN missionstatementurl TYPE VARCHAR(512);
|
26
|
ALTER TABLE dsm_datasources ALTER COLUMN citationguidelineurl TYPE VARCHAR(512);
|
27
|
ALTER TABLE dsm_datasources ALTER COLUMN languages TYPE text;
|
28
|
ALTER TABLE dsm_datasources ALTER COLUMN od_contenttypes TYPE text;
|
29
|
ALTER TABLE dsm_datasources ALTER COLUMN subjects TYPE text;
|
30
|
ALTER TABLE dsm_datasources ALTER COLUMN description TYPE text;
|
31
|
ALTER TABLE dsm_datasources ALTER COLUMN certificates TYPE text;
|
32
|
ALTER TABLE dsm_datasources ALTER COLUMN pidsystems TYPE text;
|
33
|
|
34
|
-- insert into the new dsm_* tables picking the information from the old tables
|
35
|
insert into dsm_datasources (id, officialname, englishname, websiteurl, logourl, contactemail, latitude, longitude, timezone, namespaceprefix, languages, od_contenttypes, collectedfrom, dateofvalidation,
|
36
|
typology, provenanceaction, dateofcollection, platform, activationid, description,releasestartdate, releaseenddate, missionstatementurl, dataprovider, serviceprovider,
|
37
|
databaseaccesstype, datauploadtype,databaseaccessrestriction, datauploadrestriction, versioning, citationguidelineurl, qualitymanagementkind,pidsystems, certificates,
|
38
|
aggregator, issn, eissn, lissn, registeredby, subjects)
|
39
|
select d.id,
|
40
|
d.officialname,
|
41
|
d.englishname,
|
42
|
d.websiteurl,
|
43
|
d.logourl,
|
44
|
d.contactemail,
|
45
|
d.latitude,
|
46
|
d.longitude,
|
47
|
d.timezone,
|
48
|
d.namespaceprefix as namespaceprefix,
|
49
|
d.od_languages as languages,
|
50
|
d.od_contenttypes,
|
51
|
d.collectedfrom,
|
52
|
d.dateofvalidation,
|
53
|
d.datasourceclass as typology,
|
54
|
d.provenanceactionclass as provenanceaction,
|
55
|
d.dateofcollection,
|
56
|
d.typology as platform,
|
57
|
d.activationid,
|
58
|
d.description,
|
59
|
d.releasestartdate,
|
60
|
d.releaseenddate,
|
61
|
d.missionstatementurl,
|
62
|
d.dataprovider as dataprovider,
|
63
|
d.serviceprovider as serviceprovider,
|
64
|
d.databaseaccesstype,
|
65
|
d.datauploadtype,
|
66
|
d.databaseaccessrestriction,
|
67
|
d.datauploadrestriction,
|
68
|
d.versioning,
|
69
|
d.citationguidelineurl,
|
70
|
d.qualitymanagementkind,
|
71
|
d.pidsystems,
|
72
|
d.certificates,
|
73
|
d.aggregator,
|
74
|
d.issn,
|
75
|
d.eissn,
|
76
|
d.lissn,
|
77
|
d.registeredby,
|
78
|
ARRAY_AGG(s.name) as subjects
|
79
|
from datasources d
|
80
|
left outer join datasource_subject sd on (d.id = sd.datasource)
|
81
|
left outer join subjects s on (sd.subject = s.id)
|
82
|
group by
|
83
|
d.id;
|
84
|
|
85
|
insert into dsm_api (id, protocol, datasource, contentdescription, active, removable, typology, compatibility)
|
86
|
select a.id,
|
87
|
a.protocolclass as protocol,
|
88
|
a.datasource,
|
89
|
a.contentdescriptionclass as contentdescription,
|
90
|
a.active,
|
91
|
a.removable,
|
92
|
a.typologyclass as typology,
|
93
|
a.compatibilityclass as compatibility
|
94
|
from api a;
|
95
|
|
96
|
update dsm_api da set metadata_identifier_path = ac.original from apicollections ac where da.id = ac.api and ac.param = 'metadata_identifier_path' ;
|
97
|
update dsm_api da set last_collection_total = ac.original::integer from apicollections ac where da.id = ac.api and ac.param = 'last_collection_total' ;
|
98
|
update dsm_api da set last_collection_date = ac.original::date from apicollections ac where da.id = ac.api and ac.param = 'last_collection_date' ;
|
99
|
update dsm_api da set last_collection_mdid = ac.original from apicollections ac where da.id = ac.api and ac.param = 'last_collection_mdId' ;
|
100
|
update dsm_api da set last_aggregation_total = ac.original::integer from apicollections ac where da.id = ac.api and ac.param = 'last_aggregation_total' ;
|
101
|
update dsm_api da set last_aggregation_date = ac.original::date from apicollections ac where da.id = ac.api and ac.param = 'last_aggregation_date' ;
|
102
|
update dsm_api da set last_aggregation_mdid = ac.original from apicollections ac where da.id = ac.api and ac.param = 'last_aggregation_mdId' ;
|
103
|
update dsm_api da set last_download_total = ac.original::integer from apicollections ac where da.id = ac.api and ac.param = 'last_download_total' ;
|
104
|
update dsm_api da set last_download_date = ac.original::date from apicollections ac where da.id = ac.api and ac.param = 'last_download_date' ;
|
105
|
update dsm_api da set last_download_objid = ac.original from apicollections ac where da.id = ac.api and ac.param = 'last_download_objId' ;
|
106
|
update dsm_api da set last_validation_job = ac.original from apicollections ac where da.id = ac.api and ac.param = 'last_validation_job' ;
|
107
|
update dsm_api da set baseurl = CASE WHEN (ac.edited is not null and ac.edited != '') THEN ac.edited ELSE ac.original END from apicollections ac where da.id = ac.api and ac.param = 'baseUrl' ;
|
108
|
|
109
|
insert into dsm_apiparams (param,value, api)
|
110
|
select
|
111
|
a.param,
|
112
|
CASE
|
113
|
WHEN (a.edited is not null and a.edited != '') THEN a.edited ELSE a.original
|
114
|
END AS value,
|
115
|
a.api
|
116
|
from apicollections a
|
117
|
where param != 'baseUrl' AND accessparam is true;
|
118
|
|
119
|
|
120
|
INSERT INTO dsm_organizations (id, legalshortname,legalname,websiteurl, logourl,ec_legalbody, ec_legalperson, ec_nonprofit, ec_researchorganization, ec_highereducation, ec_internationalorganizationeurinterests,
|
121
|
ec_internationalorganization, ec_enterprise, ec_smevalidated, ec_nutscode, country, collectedfrom, dateofcollection, provenanceaction)
|
122
|
SELECT
|
123
|
o.id,
|
124
|
o.legalshortname,
|
125
|
o.legalname,
|
126
|
o.websiteurl,
|
127
|
o.logourl,
|
128
|
o.ec_legalbody,
|
129
|
o.ec_legalperson,
|
130
|
o.ec_nonprofit,
|
131
|
o.ec_researchorganization,
|
132
|
o.ec_highereducation,
|
133
|
o.ec_internationalorganizationeurinterests,
|
134
|
o.ec_internationalorganization,
|
135
|
o.ec_enterprise,
|
136
|
o.ec_smevalidated,
|
137
|
o.ec_nutscode,
|
138
|
o.countryclass AS country,
|
139
|
o.collectedfrom,
|
140
|
o.dateofcollection,
|
141
|
o.provenanceactionclass AS provenanceaction
|
142
|
FROM organizations o;
|
143
|
|
144
|
INSERT INTO dsm_datasource_organization (datasource, organization)
|
145
|
SELECT
|
146
|
dorg.datasource,
|
147
|
dorg.organization
|
148
|
FROM datasource_organization dorg;
|
149
|
|
150
|
|
151
|
INSERT INTO dsm_identities (pid, issuertype)
|
152
|
SELECT
|
153
|
i.pid,
|
154
|
i.issuertypeclass AS issuertype
|
155
|
FROM identities i;
|
156
|
|
157
|
INSERT INTO dsm_datasourcepids (datasource, pid)
|
158
|
SELECT
|
159
|
dp.datasource,
|
160
|
dp.pid
|
161
|
FROM datasourcepids dp;
|
162
|
|
163
|
-- is this table needed? --
|
164
|
INSERT INTO dsm_organizationpids (organization, pid)
|
165
|
SELECT
|
166
|
op.organization,
|
167
|
op.pid
|
168
|
FROM organizationpids op;
|
169
|
|
170
|
|
171
|
-- Migrate the managed status
|
172
|
update dsm_datasources d set managed = true where d.id in (
|
173
|
SELECT d.id
|
174
|
FROM dsm_datasources d
|
175
|
LEFT OUTER JOIN dsm_api a ON (d.id = a.datasource)
|
176
|
LEFT OUTER JOIN apicollections ap ON (ap.api = a.id)
|
177
|
WHERE a.active = TRUE
|
178
|
OR a.removable = TRUE
|
179
|
OR ap.edited IS NOT NULL
|
180
|
GROUP BY d.id );
|
181
|
|
182
|
|
183
|
-- !!!!! data insert complete !!!!!
|
184
|
|
185
|
|
186
|
delete from organizations where id like 'opendoar%' ;
|
187
|
delete from organizations where id like 're3data%' ;
|
188
|
delete from organizations where collectedfrom is null ;
|
189
|
|
190
|
delete from dsm_organizations where collectedfrom = 'openaire____::corda' ;
|
191
|
delete from dsm_organizations where collectedfrom = 'openaire____::snsf' ;
|
192
|
delete from dsm_organizations where collectedfrom = 'openaire____::conicytf' ;
|
193
|
delete from dsm_organizations where collectedfrom = 'openaire____::irb_hr' ;
|
194
|
delete from dsm_organizations where collectedfrom = 'openaire____::fwf' ;
|
195
|
delete from dsm_organizations where collectedfrom = 'openaire____::sfi' ;
|
196
|
delete from dsm_organizations where collectedfrom = 'openaire____::wellcometrust' ;
|
197
|
delete from dsm_organizations where collectedfrom = 'openaire____::nsf' ;
|
198
|
delete from dsm_organizations where collectedfrom = 'openaire____::corda_h2020' ;
|
199
|
|
200
|
|
201
|
|
202
|
|
203
|
|
204
|
|
205
|
|
206
|
|