Project

General

Profile

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

    
(5-5/24)