--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/scripts/migration/migrate_permissions.sql	Tue Sep 08 17:00:21 2015 +0200
@@ -0,0 +1,122 @@
+-- reference queries for duplicate roles in vdc_role
+-- (created when user accounts where merged)
+-- NOTE: may need to run multiple times
+
+/*
+select * from _dvn3_vdcrole
+where vdcuser_id || '|' || vdc_id || '|' || role_id in 
+(
+select vdcuser_id || '|' || vdc_id || '|' || role_id from _dvn3_vdcrole
+group by vdcuser_id, vdc_id, role_id
+having count(*) > 1
+)
+order by vdcuser_id, vdc_id, role_id
+
+
+delete from _dvn3_vdcrole where id in 
+( 
+select max(id) from _dvn3_vdcrole
+group by vdcuser_id, vdc_id, role_id
+having count(*) >1
+order by max(id)
+)
+*/
+
+-----------------------
+-- dataverses role assignments
+-----------------------
+
+-- admin (from the vdcnetwork creator)
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'@'|| useridentifier, vdcn.id, dr.id
+	from _dvn3_vdcnetwork vdcn, authenticateduser, dataverserole dr
+	where vdcn.creator_id = authenticateduser.id
+	and dr.alias='admin';
+
+-- admin
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'@'|| useridentifier, vdc_id, dr.id
+	from _dvn3_vdcrole, authenticateduser, dataverserole dr
+	where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
+	and role_id=3 and dr.alias='admin';
+-- curator
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'@'|| useridentifier, vdc_id, dr.id
+	from _dvn3_vdcrole, authenticateduser, dataverserole dr
+	where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
+	and role_id=2 and dr.alias='curator';
+-- contributor
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'@'|| useridentifier, vdc_id, dr.id
+	from _dvn3_vdcrole, authenticateduser, dataverserole dr
+	where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
+	and role_id=1 and dr.alias='dsContributor';
+-- member
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'@'|| useridentifier, vdc_id, dr.id
+	from _dvn3_vdcrole, authenticateduser, dataverserole dr
+	where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
+	and role_id=4 and dr.alias='member';
+
+-- groups (as members)
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'&'|| groupalias, vdcs_id, dr.id
+	from _dvn3_vdc_usergroup, explicitgroup, dataverserole dr
+	where _dvn3_vdc_usergroup.allowedgroups_id = explicitgroup.id
+	and dr.alias='member';
+
+-----------------------
+-- dataset role assignments
+-----------------------
+
+-- contributor (from the study creator)
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'@'|| useridentifier, ds.id, dr.id
+	from _dvn3_study s, authenticateduser, dataverserole dr, dataset ds
+	where s.creator_id = authenticateduser.id
+        and ds.authority = s.authority
+        and ds.protocol = s.protocol
+        and ds.identifier = s.studyid 
+	and dr.alias='editor';
+
+-- member
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'@'|| useridentifier, ds.id, dr.id
+	from _dvn3_study_vdcuser, _dvn3_study s, authenticateduser, dataverserole dr, dataset ds
+	where _dvn3_study_vdcuser.allowedusers_id = authenticateduser.id
+        and _dvn3_study_vdcuser.studies_id = s.id
+        and ds.authority = s.authority
+        and ds.protocol = s.protocol
+        and ds.identifier = s.studyid 
+	and dr.alias='member';
+
+-- groups (as members)
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'&'|| groupalias, ds.id, dr.id
+	from _dvn3_study_usergroup, _dvn3_study s, explicitgroup, dataverserole dr, dataset ds
+	where _dvn3_study_usergroup.allowedgroups_id = explicitgroup.id
+        and _dvn3_study_usergroup.studies_id = s.id
+        and ds.authority = s.authority
+        and ds.protocol = s.protocol
+        and ds.identifier = s.studyid 
+	and dr.alias='member';
+
+-----------------------
+-- file role assignments
+-----------------------
+
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'@'|| useridentifier, studyfiles_id, dr.id
+	from _dvn3_studyfile_vdcuser, authenticateduser, dataverserole dr
+	where _dvn3_studyfile_vdcuser.allowedusers_id = authenticateduser.id
+	and _dvn3_studyfile_vdcuser.studyfiles_id in (select id from datafile)
+	and dr.alias='fileDownloader';
+
+insert into roleassignment (	assigneeidentifier, definitionpoint_id, role_id)
+	select 			'&'|| groupalias, studyfiles_id, dr.id
+	from _dvn3_studyfile_usergroup, explicitgroup, dataverserole dr
+	where _dvn3_studyfile_usergroup.allowedgroups_id = explicitgroup.id
+	and _dvn3_studyfile_usergroup.studyfiles_id in (select id from datafile)
+	and dr.alias='fileDownloader';
+
+