--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/scripts/migration/migrate_datasets.sql	Tue Sep 08 17:00:21 2015 +0200
@@ -0,0 +1,69 @@
+--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;
+