--copy studyversion fields to datasetversion

update datasetversion

set createtime = sv.createtime,

lastupdatetime = sv.lastupdatetime,

archivetime= sv.archivetime,

archivenote = sv.archivenote,

deaccessionlink = sv.deaccessionlink,

versionnote = sv.versionnote

from _dvn3_studyversion sv, dataset d, _dvn3_study s

where d.authority = s.authority

and d.protocol = s.protocol

and d.identifier = s.studyid

and datasetversion.dataset_id = d.id

and datasetversion.versionnumber = sv.versionnumber

and sv.study_id = s.id;

-- set dataset.publication date to the releasetime of the earliest released studyversion

update dvobject

set publicationdate = m.releasetime

from (select dvobject.id, sv.study_id, min(sv.releasetime) as releasetime

from _dvn3_studyversion sv, dataset d, _dvn3_study s, dvobject

where d.authority = s.authority

and d.protocol = s.protocol

and d.identifier = s.studyid

and dvobject.id = d.id

and sv.study_id = s.id

and sv.versionstate!='DRAFT' group by sv.study_id, dvobject.id) m where m.id = dvobject.id;

-- set dvobject creator_id for each dataset to study.creator_id

update dvobject

set creator_id = s.creator_id, createdate = s.createtime

from _dvn3_study s, dataset d

where d.authority = s.authority

and d.protocol = s.protocol

and d.identifier = s.studyid

and dvobject.id = d.id;

-- migrate data from _dvn3_versioncontributor to datasetversionuser

insert into datasetversionuser ( lastupdatedate, authenticateduser_id, datasetversion_id ) (

select vc.lastupdatetime, vc.contributor_id, dv.id

from _dvn3_versioncontributor vc,

_dvn3_studyversion sv,

_dvn3_study s,

dataset d,

datasetversion dv,

authenticateduser au

where vc.studyversion_id = sv.id

and sv.study_id = s.id

and d.authority = s.authority

and d.protocol = s.protocol

and d.identifier = s.studyid

and dv.dataset_id = d.id

and dv.versionnumber = sv.versionnumber

and au.id = vc.contributor_id);

-- modify versionstate for older versions of deaccessioned studies

update datasetversion

set versionstate = 'DEACCESSIONED'

where id in (

select dv1.id from datasetversion dv1, datasetversion dv2

where dv1.dataset_id = dv2.dataset_id

and dv1.versionnumber < dv2.versionnumber

and dv2.versionstate = 'DEACCESSIONED');

-- update the globalidcreatetime to be equal to the publicationdate

update dataset set globalidcreatetime = publicationdate

from dvobject dvo

where dataset.id = dvo.id;