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