Mercurial > hg > LGDataverses
comparison scripts/migration/migrate_links.sql @ 10:a50cf11e5178
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 | |
| 2 -- links to datasets | |
| 3 insert into datasetlinkingdataverse (linkingdataverse_id, dataset_id, linkcreatetime) | |
| 4 select c.owner_id, ds.id, now() | |
| 5 from _dvn3_coll_studies link, _dvn3_vdccollection c, _dvn3_study s, dataset ds | |
| 6 where link.vdc_collection_id=c.id | |
| 7 and link.study_id=s.id | |
| 8 and s.owner_id != c.owner_id --don't include if already part of this dataverse | |
| 9 and ds.authority = s.authority | |
| 10 and ds.protocol = s.protocol | |
| 11 and ds.identifier = s.studyid; | |
| 12 | |
| 13 | |
| 14 -- links to root collections (now linked to dataverses) | |
| 15 insert into dataverselinkingdataverse (linkingdataverse_id, dataverse_id, linkcreatetime) | |
| 16 select vdc_id, owner_id, now() | |
| 17 from _dvn3_vdc_linked_collections link, _dvn3_vdccollection c | |
| 18 where link.linked_collection_id=c.id | |
| 19 and c.parentcollection_id is null; | |
| 20 | |
| 21 -- links to other, static collections (now linked to just the studies from them) | |
| 22 insert into datasetlinkingdataverse (linkingdataverse_id, dataset_id, linkcreatetime) | |
| 23 select vdc_id, ds.id, now() | |
| 24 from _dvn3_vdc_linked_collections link, _dvn3_coll_studies contents, _dvn3_vdccollection c, _dvn3_study s, dataset ds | |
| 25 where link.linked_collection_id=c.id | |
| 26 and c.parentcollection_id is not null | |
| 27 and c.type='static' | |
| 28 and c.id = contents.vdc_collection_id | |
| 29 and contents.study_id=s.id | |
| 30 and s.owner_id != vdc_id -- don't include if already part of this dataverse | |
| 31 and ds.authority = s.authority | |
| 32 and ds.protocol = s.protocol | |
| 33 and ds.identifier = s.studyid; | |
| 34 | |
| 35 | |
| 36 | |
| 37 ----------------------- | |
| 38 -- reset sequences | |
| 39 ----------------------- | |
| 40 | |
| 41 SELECT setval('datasetlinkingdataverse_id_seq', (SELECT MAX(id) FROM datasetlinkingdataverse)); | |
| 42 SELECT setval('dataverselinkingdataverse_id_seq', (SELECT MAX(id) FROM dataverselinkingdataverse)); | |
| 43 | |
| 44 |