db/0004_grant_cvterm_permissions.pl

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6 [ this script name ].pl
   7
   8 =head1 SYNOPSIS
   9
  10   this_script.pl [options]
  11
  12   Options:
  13
  14     -D <dbname> (mandatory)
  15       dbname to load into
  16
  17     -H <dbhost> (mandatory)
  18       dbhost to load into
  19
  20     -p <script_executor_user> (mandatory)
  21       username to run the script
  22
  23     -F force to run this script and don't stop it by
  24        missing previous db_patches
  25
  26   Note: If the first time that you run this script, obviously
  27         you have no previous dbversion row in the md_dbversion
  28         table, so you need to force the execution of this script
  29         using -F
  30
  31 =head1 DESCRIPTION
  32
  33 we now allow submitters to load new organismprops (such as 'sol100' for organisms to be included in the sequencing project)
  34
  35  This db patch will grant web_usr with insert permissions to the organismprop table to allow calling $organism->create_organismprops (L<Bio::Chado::Schema::Organism::Orgnaism> ) from the web site (sol100.pl now allows this, and soon sybmitters will be able to add more props)
  36
  37 =head1 AUTHOR
  38
  39 Naama Menda
  40 nm249@cornell.edu
  41
  42 =head1 COPYRIGHT & LICENSE
  43
  44 Copyright 2010 Boyce Thompson Institute for Plant Research
  45
  46 This program is free software; you can redistribute it and/or modify
  47 it under the same terms as Perl itself.
  48
  49 =cut
  50
  51
  52 use strict;
  53 use warnings;
  54
  55 use Pod::Usage;
  56 use Getopt::Std;
  57 use CXGN::DB::InsertDBH;
  58 use CXGN::Metadata::Dbversion;   ### Module to interact with the metadata.md_dbversion table
  59
  60
  61 ## Declaration of the parameters used to run the script
  62
  63 our ($opt_H, $opt_D, $opt_p, $opt_F, $opt_h);
  64 getopts("H:D:p:Fh");
  65
  66 ## If is used -h <help> or none parameters is detailed print pod
  67
  68 if (!$opt_H && !$opt_D && !$opt_p && !$opt_F && !$opt_h) {
  69     print STDOUT "No optionas passed. Printing help\n\n";
  70     pod2usage(1);
  71 }
  72 elsif ($opt_h) {
  73     pod2usage(1);
  74 }
  75
  76
  77 ## Declaration of the name of the script and the description
  78
  79 my $patch_name = '0003_grant_organismprop_permissions.pl';
  80 my $patch_descr = 'This script grants web_user with insert permissions on cvterm ';
  81
  82 print STDOUT "\n+--------------------------------------------------------------------------------------------------+\n";
  83 print STDOUT "Executing the patch:\n   $patch_name.\n\nDescription:\n  $patch_descr.\n\nExecuted by:\n  $opt_p.";
  84 print STDOUT "\n+--------------------------------------------------------------------------------------------------+\n\n";
  85
  86 ## And the requeriments if you want not use all
  87 ##
  88 my @previous_requested_patches = (   ## ADD HERE
  89    );
  90
  91 ## Specify the mandatory parameters
  92
  93 if (!$opt_H || !$opt_D) {
  94     print STDOUT "\nMANDATORY PARAMETER ERROR: -D <db_name> or/and -H <db_host> parameters has not been specified for $patch_name.\n";
  95 }
  96
  97 if (!$opt_p) {
  98     print STDOUT "\nMANDATORY PARAMETER ERROR: -p <script_executor_user> parameter has not been specified for $patch_name.\n";
  99 }
 100
 101 ## Create the $schema object for the db_version object
 102 ## This should be replace for CXGN::DB::DBICFactory as soon as it can use CXGN::DB::InsertDBH
 103
 104 my $dbh =  CXGN::DB::InsertDBH->new(
 105                                      {
 106                                          dbname => $opt_D,
 107                                          dbhost => $opt_H
 108                                      }
 109                                    )->get_actual_dbh();
 110
 111 print STDOUT "\nCreating the Metadata Schema object.\n";
 112
 113 my $metadata_schema = CXGN::Metadata::Schema->connect(
 114                                                        sub { $dbh },
 115                                                       { on_connect_do => ['SET search_path TO metadata;'] },
 116                                                       );
 117
 118 print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
 119
 120 ### Now it will check if you have runned this patch or the previous patches
 121
 122 my $dbversion = CXGN::Metadata::Dbversion->new($metadata_schema)
 123                                          ->complete_checking( {
 124                                                                  patch_name  => $patch_name,
 125                                                                  patch_descr => $patch_descr,
 126                                                                  prepatch_req => \@previous_requested_patches,
 127                                                                  force => $opt_F
 128                                                               }
 129                                                              );
 130
 131
 132 ### CREATE AN METADATA OBJECT and a new metadata_id in the database for this data
 133
 134 my $metadata = CXGN::Metadata::Metadbdata->new($metadata_schema, $opt_p);
 135
 136 ### Get a new metadata_id (if you are using store function you only need to supply $metadbdata object)
 137
 138 my $metadata_id = $metadata->store()
 139                            ->get_metadata_id();
 140
 141 ### Now you can insert the data using different options:
 142 ##
 143 ##  1- By sql queryes using $dbh->do(<<EOSQL); and detailing in the tag the queries
 144 ##
 145 ##  2- Using objects with the store function
 146 ##
 147 ##  3- Using DBIx::Class first level objects
 148 ##
 149
 150 ## In this case we will use the SQL tag
 151
 152 print STDERR "\nExecuting the SQL commands.\n";
 153
 154 $dbh->do(<<EOSQL);
 155
 156 GRANT insert,select ON public.cvterm TO web_usr;
 157 GRANT usage,select,update ON public.cvterm_cvterm_id_seq TO web_usr;
 158
 159 EOSQL
 160
 161 ## Now it will add this new patch information to the md_version table.  It did the dbversion object before and
 162 ## set the patch_name and the patch_description, so it only need to store it.
 163
 164
 165 $dbversion->store($metadata);
 166
 167 print STDOUT "DONE!\n";
 168
 169 $dbh->commit;
 170
 171 __END__
 172