db/0002_remove_genbank_taxon_id.pl

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  0002_remove_genbank_taxon_id.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 no need for genbank_taxon_id column in the organism table anymore.
  34
  35
  36 =head1 AUTHOR
  37
  38 Naama Menda
  39 nm249@cornell.edu
  40
  41 =head1 COPYRIGHT & LICENSE
  42
  43 Copyright 2010 Boyce Thompson Institute for Plant Research
  44
  45 This program is free software; you can redistribute it and/or modify
  46 it under the same terms as Perl itself.
  47
  48 =cut
  49
  50
  51 use strict;
  52 use warnings;
  53
  54 use Pod::Usage;
  55 use Getopt::Std;
  56 use CXGN::DB::InsertDBH;
  57 use CXGN::Metadata::Dbversion;   ### Module to interact with the metadata.md_dbversion table
  58
  59
  60 ## Declaration of the parameters used to run the script
  61
  62 our ($opt_H, $opt_D, $opt_p, $opt_F, $opt_h);
  63 getopts("H:D:p:Fh");
  64
  65 ## If is used -h <help> or none parameters is detailed print pod
  66
  67 if (!$opt_H && !$opt_D && !$opt_p && !$opt_F && !$opt_h) {
  68     print STDOUT "No optionas passed. Printing help\n\n";
  69     pod2usage(1);
  70 }
  71 elsif ($opt_h) {
  72     pod2usage(1);
  73 }
  74
  75
  76 ## Declaration of the name of the script and the description
  77
  78 my $patch_name = '0002_remove_genbank_taxon.pl';
  79 my $patch_descr = 'This script removes the genbank_taxon_id column from the public organism table.';
  80
  81 print STDOUT "\n+--------------------------------------------------------------------------------------------------+\n";
  82 print STDOUT "Executing the patch:\n   $patch_name.\n\nDescription:\n  $patch_descr.\n\nExecuted by:\n  $opt_p.";
  83 print STDOUT "\n+--------------------------------------------------------------------------------------------------+\n\n";
  84
  85 ## And the requeriments if you want not use all
  86 ##
  87 my @previous_requested_patches = (   ## ADD HERE
  88    );
  89
  90 ## Specify the mandatory parameters
  91
  92 if (!$opt_H || !$opt_D) {
  93     print STDOUT "\nMANDATORY PARAMETER ERROR: -D <db_name> or/and -H <db_host> parameters has not been specified for $patch_name.\n";
  94 }
  95
  96 if (!$opt_p) {
  97     print STDOUT "\nMANDATORY PARAMETER ERROR: -p <script_executor_user> parameter has not been specified for $patch_name.\n";
  98 }
  99
 100 ## Create the $schema object for the db_version object
 101 ## This should be replace for CXGN::DB::DBICFactory as soon as it can use CXGN::DB::InsertDBH
 102
 103 my $dbh =  CXGN::DB::InsertDBH->new(
 104                                      {
 105                                          dbname => $opt_D,
 106                                          dbhost => $opt_H
 107                                      }
 108                                    )->get_actual_dbh();
 109
 110 print STDOUT "\nCreating the Metadata Schema object.\n";
 111
 112 my $metadata_schema = CXGN::Metadata::Schema->connect(
 113                                                        sub { $dbh },
 114                                                       { on_connect_do => ['SET search_path TO metadata;'] },
 115                                                       );
 116
 117 print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
 118
 119 ### Now it will check if you have runned this patch or the previous patches
 120
 121 my $dbversion = CXGN::Metadata::Dbversion->new($metadata_schema)
 122                                          ->complete_checking( {
 123                                                                  patch_name  => $patch_name,
 124                                                                  patch_descr => $patch_descr,
 125                                                                  prepatch_req => \@previous_requested_patches,
 126                                                                  force => $opt_F
 127                                                               }
 128                                                              );
 129
 130
 131 ### CREATE AN METADATA OBJECT and a new metadata_id in the database for this data
 132
 133 my $metadata = CXGN::Metadata::Metadbdata->new($metadata_schema, $opt_p);
 134
 135 ### Get a new metadata_id (if you are using store function you only need to supply $metadbdata object)
 136
 137 my $metadata_id = $metadata->store()
 138                            ->get_metadata_id();
 139
 140 ### Now you can insert the data using different options:
 141 ##
 142 ##  1- By sql queryes using $dbh->do(<<EOSQL); and detailing in the tag the queries
 143 ##
 144 ##  2- Using objects with the store function
 145 ##
 146 ##  3- Using DBIx::Class first level objects
 147 ##
 148
 149 ## In this case we will use the SQL tag
 150
 151 print STDERR "\nExecuting the SQL commands.\n";
 152
 153 $dbh->do(<<EOSQL);
 154
 155 ALTER TABLE public.organism DROP COLUMN genbank_taxon_id;
 156
 157
 158 EOSQL
 159
 160 ## Now it will add this new patch information to the md_version table.  It did the dbversion object before and
 161 ## set the patch_name and the patch_description, so it only need to store it.
 162
 163
 164 $dbversion->store($metadata);
 165
 166 print STDOUT "DONE!\n";
 167
 168 $dbh->commit;
 169
 170 __END__
 171