Mercurial > hg > LGDataverses

comparison scripts/migration/migrate_permissions.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 -- reference queries for duplicate roles in vdc_role
2 -- (created when user accounts where merged)
3 -- NOTE: may need to run multiple times
4
5 /*
6 select * from _dvn3_vdcrole
7 where vdcuser_id || '|' || vdc_id || '|' || role_id in
8 (
9 select vdcuser_id || '|' || vdc_id || '|' || role_id from _dvn3_vdcrole
10 group by vdcuser_id, vdc_id, role_id
11 having count(*) > 1
12 )
13 order by vdcuser_id, vdc_id, role_id
14
15
16 delete from _dvn3_vdcrole where id in
17 (
18 select max(id) from _dvn3_vdcrole
19 group by vdcuser_id, vdc_id, role_id
20 having count(*) >1
21 order by max(id)
22 )
23 */
24
25 -----------------------
26 -- dataverses role assignments
27 -----------------------
28
29 -- admin (from the vdcnetwork creator)
30 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
31 select '@'|| useridentifier, vdcn.id, dr.id
32 from _dvn3_vdcnetwork vdcn, authenticateduser, dataverserole dr
33 where vdcn.creator_id = authenticateduser.id
34 and dr.alias='admin';
35
36 -- admin
37 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
38 select '@'|| useridentifier, vdc_id, dr.id
39 from _dvn3_vdcrole, authenticateduser, dataverserole dr
40 where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
41 and role_id=3 and dr.alias='admin';
42 -- curator
43 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
44 select '@'|| useridentifier, vdc_id, dr.id
45 from _dvn3_vdcrole, authenticateduser, dataverserole dr
46 where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
47 and role_id=2 and dr.alias='curator';
48 -- contributor
49 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
50 select '@'|| useridentifier, vdc_id, dr.id
51 from _dvn3_vdcrole, authenticateduser, dataverserole dr
52 where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
53 and role_id=1 and dr.alias='dsContributor';
54 -- member
55 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
56 select '@'|| useridentifier, vdc_id, dr.id
57 from _dvn3_vdcrole, authenticateduser, dataverserole dr
58 where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
59 and role_id=4 and dr.alias='member';
60
61 -- groups (as members)
62 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
63 select '&'|| groupalias, vdcs_id, dr.id
64 from _dvn3_vdc_usergroup, explicitgroup, dataverserole dr
65 where _dvn3_vdc_usergroup.allowedgroups_id = explicitgroup.id
66 and dr.alias='member';
67
68 -----------------------
69 -- dataset role assignments
70 -----------------------
71
72 -- contributor (from the study creator)
73 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
74 select '@'|| useridentifier, ds.id, dr.id
75 from _dvn3_study s, authenticateduser, dataverserole dr, dataset ds
76 where s.creator_id = authenticateduser.id
77 and ds.authority = s.authority
78 and ds.protocol = s.protocol
79 and ds.identifier = s.studyid
80 and dr.alias='editor';
81
82 -- member
83 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
84 select '@'|| useridentifier, ds.id, dr.id
85 from _dvn3_study_vdcuser, _dvn3_study s, authenticateduser, dataverserole dr, dataset ds
86 where _dvn3_study_vdcuser.allowedusers_id = authenticateduser.id
87 and _dvn3_study_vdcuser.studies_id = s.id
88 and ds.authority = s.authority
89 and ds.protocol = s.protocol
90 and ds.identifier = s.studyid
91 and dr.alias='member';
92
93 -- groups (as members)
94 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
95 select '&'|| groupalias, ds.id, dr.id
96 from _dvn3_study_usergroup, _dvn3_study s, explicitgroup, dataverserole dr, dataset ds
97 where _dvn3_study_usergroup.allowedgroups_id = explicitgroup.id
98 and _dvn3_study_usergroup.studies_id = s.id
99 and ds.authority = s.authority
100 and ds.protocol = s.protocol
101 and ds.identifier = s.studyid
102 and dr.alias='member';
103
104 -----------------------
105 -- file role assignments
106 -----------------------
107
108 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
109 select '@'|| useridentifier, studyfiles_id, dr.id
110 from _dvn3_studyfile_vdcuser, authenticateduser, dataverserole dr
111 where _dvn3_studyfile_vdcuser.allowedusers_id = authenticateduser.id
112 and _dvn3_studyfile_vdcuser.studyfiles_id in (select id from datafile)
113 and dr.alias='fileDownloader';
114
115 insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
116 select '&'|| groupalias, studyfiles_id, dr.id
117 from _dvn3_studyfile_usergroup, explicitgroup, dataverserole dr
118 where _dvn3_studyfile_usergroup.allowedgroups_id = explicitgroup.id
119 and _dvn3_studyfile_usergroup.studyfiles_id in (select id from datafile)
120 and dr.alias='fileDownloader';
121
122