Mercurial > hg > LGDataverses

comparison scripts/migration/migrate_datasets.sql @ 10:a50cf11e5178

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rewrite LGDataverse completely upgrading to dataverse4.0
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Tue, 08 Sep 2015 17:00:21 +0200
parents
children
comparison
equal deleted inserted replaced
9:5926d6419569 10:a50cf11e5178
1 --copy studyversion fields to datasetversion
2 update datasetversion
3 set createtime = sv.createtime,
4 lastupdatetime = sv.lastupdatetime,
5 archivetime= sv.archivetime,
6 archivenote = sv.archivenote,
7 deaccessionlink = sv.deaccessionlink,
8 versionnote = sv.versionnote
9 from _dvn3_studyversion sv, dataset d, _dvn3_study s
10 where d.authority = s.authority
11 and d.protocol = s.protocol
12 and d.identifier = s.studyid
13 and datasetversion.dataset_id = d.id
14 and datasetversion.versionnumber = sv.versionnumber
15 and sv.study_id = s.id;
16
17 -- set dataset.publication date to the releasetime of the earliest released studyversion
18 update dvobject
19 set publicationdate = m.releasetime
20 from (select dvobject.id, sv.study_id, min(sv.releasetime) as releasetime
21 from _dvn3_studyversion sv, dataset d, _dvn3_study s, dvobject
22 where d.authority = s.authority
23 and d.protocol = s.protocol
24 and d.identifier = s.studyid
25 and dvobject.id = d.id
26 and sv.study_id = s.id
27 and sv.versionstate!='DRAFT' group by sv.study_id, dvobject.id) m where m.id = dvobject.id;
28
29 -- set dvobject creator_id for each dataset to study.creator_id
30 update dvobject
31 set creator_id = s.creator_id, createdate = s.createtime
32 from _dvn3_study s, dataset d
33 where d.authority = s.authority
34 and d.protocol = s.protocol
35 and d.identifier = s.studyid
36 and dvobject.id = d.id;
37
38 -- migrate data from _dvn3_versioncontributor to datasetversionuser
39 insert into datasetversionuser ( lastupdatedate, authenticateduser_id, datasetversion_id ) (
40 select vc.lastupdatetime, vc.contributor_id, dv.id
41 from _dvn3_versioncontributor vc,
42 _dvn3_studyversion sv,
43 _dvn3_study s,
44 dataset d,
45 datasetversion dv,
46 authenticateduser au
47 where vc.studyversion_id = sv.id
48 and sv.study_id = s.id
49 and d.authority = s.authority
50 and d.protocol = s.protocol
51 and d.identifier = s.studyid
52 and dv.dataset_id = d.id
53 and dv.versionnumber = sv.versionnumber
54 and au.id = vc.contributor_id);
55
56 -- modify versionstate for older versions of deaccessioned studies
57 update datasetversion
58 set versionstate = 'DEACCESSIONED'
59 where id in (
60 select dv1.id from datasetversion dv1, datasetversion dv2
61 where dv1.dataset_id = dv2.dataset_id
62 and dv1.versionnumber < dv2.versionnumber
63 and dv2.versionstate = 'DEACCESSIONED');
64
65 -- update the globalidcreatetime to be equal to the publicationdate
66 update dataset set globalidcreatetime = publicationdate
67 from dvobject dvo
68 where dataset.id = dvo.id;
69