Mercurial > hg > LGDataverses

annotate scripts/migration/migrate_dataverses.sql @ 14:be7787c36e58 default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new: nofity LGSercies for deleted files
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Mon, 02 Nov 2015 16:41:23 +0100
parents a50cf11e5178
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
10
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
1 ----------------------
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
2 -- subnetworks
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
3 -----------------------
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
4
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
5 insert into dvobject ( id, owner_id, dtype, createdate, publicationdate, modificationtime, permissionmodificationtime, creator_id, releaseuser_id)
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
6 select id, 1, 'Dataverse', networkcreated, networkcreated, now(), now(), creator_id, creator_id
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
7 from _dvn3_vdcnetwork;
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
8
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
9 insert into dataverse ( id, affiliation, alias, dataversetype, description, name, defaultcontributorrole_id,
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
10 facetroot, metadatablockroot, templateroot, guestbookroot, permissionroot, themeroot )
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
11 select vdcn.id, affiliation, urlalias, 'UNCATEGORIZED', announcements, vdcn.name, dr.id,
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
12 false, false, false, false, true, true
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
13 from _dvn3_vdcnetwork vdcn, dataverserole dr
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
14 where dr.alias = 'editor';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
15
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
16 -- subnetworks use the same contact e-mails as the Dataverse 4.0 root
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
17 insert into dataversecontact ( contactemail, displayorder, dataverse_id)
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
18 select dc.contactemail, dc.displayorder, _dvn3_vdcnetwork.id from dataversecontact dc, _dvn3_vdcnetwork
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
19 where dc.dataverse_id=1;
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
20
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
21 -----------------------
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
22 -- dataverses
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
23 -----------------------
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
24
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
25
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
26 insert into dvobject ( id, owner_id, dtype, createdate, publicationdate, modificationtime, permissionmodificationtime, creator_id, releaseuser_id)
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
27 select id, vdcnetwork_id + 1, 'Dataverse', createddate, releasedate, now(), now(), creator_id, creator_id
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
28 from _dvn3_vdc;
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
29
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
30 insert into dataverse ( id, affiliation, alias, dataversetype, description, name, defaultcontributorrole_id,
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
31 facetroot, metadatablockroot, templateroot, guestbookroot, permissionroot, themeroot )
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
32 select vdc.id, affiliation, vdc.alias, 'UNCATEGORIZED', announcements, vdc.name, dr.id,
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
33 false, false, false, false, true, true
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
34 from _dvn3_vdc vdc, dataverserole dr
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
35 where dr.alias = 'editor';
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
36
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
37 -- this query splits the contact e-mail by , and trims both sides
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
38 insert into dataversecontact ( contactemail, displayorder, dataverse_id)
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
39 select trim(unnest(string_to_array(contactemail, ','))), 0, id from _dvn3_vdc;
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
40
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
41
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
42
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
43 -----------------------
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
44 -- reset sequences
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
45 -----------------------
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
46
a50cf11e5178 Rewrite LGDataverse completely upgrading to dataverse4.0
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
47 SELECT setval('dvobject_id_seq', (SELECT MAX(id) FROM dvobject));