db/0001_rename_sgn_organismprop.pl

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  0001_rename_sgn_organismprop.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  For some reason I named sgn.organismprop table wrong. It should be sgn.common_nameprop
  34
  35  This db patch will rename the table. Some code refactoring is required accordingly (of course I will take care of that along with committing the db_patch)
  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 = '0001_rename_sgn_organismprop.pl';
  80 my $patch_descr = 'This script renames sgn_organismprop to sgn.common_nameprop';
  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 --rename table
 157 ALTER TABLE sgn.organismprop RENAME TO common_nameprop;
 158
 159 --rename the PK column
 160 ALTER TABLE sgn.common_nameprop rename column organismprop_id to common_nameprop_id;
 161
 162 --rename the PK
 163 alter table sgn.common_nameprop drop CONSTRAINT organismprop_pkey;
 164 alter table sgn.common_nameprop add primary key (common_nameprop_id);
 165
 166 --rename the sequence
 167 alter SEQUENCE sgn.organismprop_organismprop_id_seq rename to common_nameprop_common_nameprop_id_seq ;
 168
 169
 170
 171 EOSQL
 172
 173 ## Now it will add this new patch information to the md_version table.  It did the dbversion object before and
 174 ## set the patch_name and the patch_description, so it only need to store it.
 175
 176
 177 $dbversion->store($metadata);
 178
 179 print STDOUT "DONE!\n";
 180
 181 $dbh->commit;
 182
 183 __END__
 184