Project

General

Profile

« Previous | Next » 

Revision 48518

Added by Alessia Bardi almost 7 years ago

Updated migration sql statements

View differences:

modules/dnet-openaireplus-workflows/trunk/src/main/resources/eu/dnetlib/patch-db/migrate_dsm.sql
18 18 
-- import the dsm table schema to the existing openaire database:
19 19 
--    psql -h localhost -U dnet dnet_openaireplus -f dnet_dsm.sql
20 20 

  		




  
  21
  
    
ALTER TABLE dsm_datasources ALTER COLUMN officialname TYPE VARCHAR(512);


  		




  
  22
  
    
ALTER TABLE dsm_datasources ALTER COLUMN englishname TYPE VARCHAR(512);


  		




  
  23
  
    
ALTER TABLE dsm_datasources ALTER COLUMN missionstatementurl TYPE VARCHAR(512);


  		




  
  24
  
    
ALTER TABLE dsm_datasources ALTER COLUMN citationguidelineurl TYPE VARCHAR(512);


  		




  
  25
  
    
ALTER TABLE dsm_datasources ALTER COLUMN languages TYPE text;


  		




  
  26
  
    
ALTER TABLE dsm_datasources ALTER COLUMN od_contenttypes TYPE text;


  		




  
  27
  
    
ALTER TABLE dsm_datasources ALTER COLUMN subjects TYPE text;


  		




  
  28
  
    
ALTER TABLE dsm_datasources ALTER COLUMN description TYPE text;


  		




  
  29
  
    
ALTER TABLE dsm_datasources ALTER COLUMN certificates TYPE text;


  		




  
  30
  
    
ALTER TABLE dsm_datasources ALTER COLUMN pidsystems TYPE text;


  		




  
  31
  
    

  		




  21
  32
  
    
-- insert into the new dsm_* tables picking the information from the old tables


  		




  22
  
  
    
insert into dsm_datasources 


  		




  
  33
  
    
insert into dsm_datasources (id, officialname, englishname, websiteurl, logourl, contactemail, latitude, longitude, timezone, namespaceprefix, languages, od_contenttypes, collectedfrom, dateofvalidation,


  		




  
  34
  
    
                             typology, provenanceaction, dateofcollection, platform, activationid, description,releasestartdate, releaseenddate, missionstatementurl, dataprovider, serviceprovider,


  		




  
  35
  
    
                             databaseaccesstype, datauploadtype,databaseaccessrestriction, datauploadrestriction, versioning, citationguidelineurl, qualitymanagementkind,pidsystems, certificates,


  		




  
  36
  
    
                             aggregator, issn, eissn, lissn, registeredby, subjects)


  		




  23
  37
  
    
select d.id, 


  		




  24
  38
  
    
	d.officialname, 


  		




  25
  39
  
    
	d.englishname, 


  		




  ......




  29
  43
  
    
	d.latitude, 


  		




  30
  44
  
    
	d.longitude, 


  		




  31
  45
  
    
	d.timezone, 


  		




  32
  
  
    
	d.namespaceprefix,  


  		




  
  46
  
    
	d.namespaceprefix as namespaceprefix,


  		




  33
  47
  
    
	ARRAY(select trim(s) from unnest(string_to_array(d.od_languages, ',')) as s) as languages,


  		




  34
  48
  
    
	ARRAY(select trim(s) from unnest(string_to_array(d.od_contenttypes, '-')) as s) as od_contenttypes,


  		




  35
  49
  
    
	d.collectedfrom,


  		




  36
  50
  
    
	d.dateofvalidation,


  		




  37
  
  
    
	d.optional1,


  		




  38
  
  
    
	d.optional2,


  		




  39
  51
  
    
	d.datasourceclass as typology,


  		




  40
  52
  
    
	d.provenanceactionclass as provenanceaction,


  		




  41
  53
  
    
	d.dateofcollection,	


  		




  ......




  45
  57
  
    
	d.releasestartdate,


  		




  46
  58
  
    
	d.releaseenddate,


  		




  47
  59
  
    
	d.missionstatementurl,


  		




  48
  
  
    
	d.dataprovider,


  		




  49
  
  
    
	d.serviceprovider,


  		




  
  60
  
    
	d.dataprovider as dataprovider,


  		




  
  61
  
    
	d.serviceprovider as serviceprovider,


  		




  50
  62
  
    
	d.databaseaccesstype,


  		




  51
  63
  
    
	d.datauploadtype,


  		




  52
  64
  
    
	d.databaseaccessrestriction,


  		




  ......




  68
  80
  
    
group by


  		




  69
  81
  
    
	d.id;


  		




  70
  82
  
    

  		




  71
  
  
    
insert into dsm_api 


  		




  
  83
  
    
insert into dsm_api (id, protocol, datasource, contentdescription, active, removable, typology, compatibility)


  		




  72
  84
  
    
select a.id,


  		




  73
  85
  
    
	a.protocolclass as protocol,


  		




  74
  86
  
    
	a.datasource,


  		




  ......




  92
  104
  
    
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
  105
  
    
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
  106
  
    

  		




  95
  
  
    
insert into dsm_apiparams


  		




  
  107
  
    
insert into dsm_apiparams (param,value, api)


  		




  96
  108
  
    
select


  		




  97
  109
  
    
	a.param,


  		




  98
  110
  
    
	CASE


  		




  ......




  102
  114
  
    
from apicollections a


  		




  103
  115
  
    
where	param != 'baseUrl' AND accessparam is true;


  		




  104
  116
  
    

  		




  105
  
  
    
INSERT INTO dsm_organizations


  		




  
  117
  
    

  		




  
  118
  
    
ALTER TABLE dsm_organizations ALTER COLUMN websiteurl TYPE text;


  		




  
  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)


  		




  106
  122
  
    
	SELECT


  		




  107
  123
  
    
		o.id,


  		




  108
  124
  
    
		o.legalshortname,


  		




  ......




  121
  137
  
    
		o.ec_nutscode,


  		




  122
  138
  
    
		o.countryclass          AS country,


  		




  123
  139
  
    
		o.collectedfrom,


  		




  124
  
  
    
		o.optional1,


  		




  125
  
  
    
		o.optional2,


  		




  126
  140
  
    
		o.dateofcollection,


  		




  127
  141
  
    
		o.provenanceactionclass AS provenanceaction


  		




  128
  142
  
    
	FROM organizations o;


  		




  129
  143
  
    

  		




  130
  
  
    
INSERT INTO dsm_datasource_organization


  		




  
  144
  
    
INSERT INTO dsm_datasource_organization (datasource, organization)


  		




  131
  145
  
    
	SELECT


  		




  132
  146
  
    
		dorg.datasource,


  		




  133
  147
  
    
		dorg.organization


  		




  134
  148
  
    
	FROM datasource_organization dorg;


  		




  135
  149
  
    

  		




  136
  150
  
    

  		




  137
  
  
    
INSERT INTO dsm_identities


  		




  
  151
  
    
INSERT INTO dsm_identities (pid, issuertype)


  		




  138
  152
  
    
	SELECT


  		




  139
  153
  
    
		i.pid,


  		




  140
  154
  
    
		i.issuertypeclass AS issuertype


  		




  141
  155
  
    
	FROM identities i;


  		




  142
  156
  
    

  		




  143
  
  
    
INSERT INTO dsm_datasourcepids


  		




  
  157
  
    
INSERT INTO dsm_datasourcepids (datasource, pid)


  		




  144
  158
  
    
	SELECT


  		




  145
  159
  
    
		dp.datasource,


  		




  146
  160
  
    
		dp.pid


  		




  147
  161
  
    
	FROM datasourcepids dp;


  		




  148
  162
  
    

  		




  149
  
  
    

  		




  150
  
  
    
INSERT INTO dsm_organizationpids


  		




  
  163
  
    
-- is this table needed? --


  		




  
  164
  
    
INSERT INTO dsm_organizationpids (organization, pid)


  		




  151
  165
  
    
	SELECT


  		




  152
  166
  
    
		op.organization,


  		




  153
  167
  
    
		op.pid


  		












Also available in:  Unified diff