text: -- 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';
  
  
  