db/00002/AddUserRoles.pm

   1 package AddUserRoles;
   2
   3 use Moose;
   4 use 5.010;
   5 extends 'CXGN::Metadata::Dbpatch';
   6
   7 sub init_patch {
   8     my $self=shift;
   9     my $name = __PACKAGE__;
  10     say "dbpatch name is $name";
  11     my $description = 'Add User Roles';
  12     my @previous_requested_patches = (); #ADD HERE
  13
  14     $self->name($name);
  15     $self->description($description);
  16     $self->prereq(\@previous_requested_patches);
  17
  18 }
  19
  20 sub patch {
  21     my $self=shift;
  22
  23     print "Executing the patch:\n " . $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  24
  25     print "\nExecuting the SQL commands.\n";
  26
  27     $self->dbh->do(<<EOSQL);
  28
  29 CREATE TABLE sgn_people.sp_roles (
  30     sp_role_id serial primary key,
  31     name varchar(20)
  32   );
  33
  34 CREATE TABLE sgn_people.sp_person_roles (
  35     sp_person_role_id serial primary key,
  36     sp_person_id bigint references sgn_people.sp_person on delete cascade,
  37     sp_role_id bigint references sgn_people.sp_roles on delete cascade
  38   );
  39
  40     INSERT INTO sgn_people.sp_roles(name) VALUES ('curator');
  41     INSERT INTO sgn_people.sp_roles(name) VALUES ('sequencer');
  42     INSERT INTO sgn_people.sp_roles(name) VALUES ('submitter');
  43     INSERT INTO sgn_people.sp_roles(name) VALUES ('user');
  44
  45     INSERT INTO sgn_people.sp_person_roles (sp_role_id, sp_person_id)
  46        SELECT sp_role_id, sp_person_id
  47        FROM sgn_people.sp_person
  48        JOIN sgn_people.sp_roles ON (user_type=name);
  49
  50     GRANT select, update, insert, delete  ON sgn_people.sp_roles to postgres, web_usr;
  51     GRANT select, update, insert, delete  ON sgn_people.sp_person_roles to postgres, web_usr;
  52
  53     GRANT select, update, usage  ON sgn_people.sp_roles_sp_role_id_seq to postgres, web_usr;
  54
  55     GRANT select, update, usage ON sgn_people.sp_person_roles_sp_person_role_id_seq to postgres, web_usr;
  56
  57 EOSQL
  58
  59     say "You're done!";
  60
  61 }
  62
  63 1;