-- links to datasets

insert into datasetlinkingdataverse (linkingdataverse_id, dataset_id, linkcreatetime)

select c.owner_id, ds.id, now()

from _dvn3_coll_studies link, _dvn3_vdccollection c, _dvn3_study s, dataset ds

where link.vdc_collection_id=c.id

and link.study_id=s.id

and s.owner_id != c.owner_id --don't include if already part of this dataverse

and ds.authority = s.authority

and ds.protocol = s.protocol

and ds.identifier = s.studyid;

-- links to root collections (now linked to dataverses)

insert into dataverselinkingdataverse (linkingdataverse_id, dataverse_id, linkcreatetime)

select vdc_id, owner_id, now()

from _dvn3_vdc_linked_collections link, _dvn3_vdccollection c

where link.linked_collection_id=c.id

and c.parentcollection_id is null;

-- links to other, static collections (now linked to just the studies from them)

insert into datasetlinkingdataverse (linkingdataverse_id, dataset_id, linkcreatetime)

select vdc_id, ds.id, now()

from _dvn3_vdc_linked_collections link, _dvn3_coll_studies contents, _dvn3_vdccollection c, _dvn3_study s, dataset ds

where link.linked_collection_id=c.id

and c.parentcollection_id is not null

and c.type='static'

and c.id = contents.vdc_collection_id

and contents.study_id=s.id

and s.owner_id != vdc_id -- don't include if already part of this dataverse

and ds.authority = s.authority

and ds.protocol = s.protocol

and ds.identifier = s.studyid;

-----------------------

-- reset sequences

-----------------------

SELECT setval('datasetlinkingdataverse_id_seq', (SELECT MAX(id) FROM datasetlinkingdataverse));

SELECT setval('dataverselinkingdataverse_id_seq', (SELECT MAX(id) FROM dataverselinkingdataverse));