db/0005_load_sol100_species.pl

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  0005_load_sol100_species.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 Need to add 'sol100' organismprop to all species provided by Sandy Knaap and Rene
  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 Bio::Chado::Schema;
  58
  59 use CXGN::Metadata::Dbversion;   ### Module to interact with the metadata.md_dbversion table
  60
  61
  62 ## Declaration of the parameters used to run the script
  63
  64 our ($opt_H, $opt_D, $opt_p, $opt_F, $opt_h);
  65 getopts("H:D:p:Fh");
  66
  67 ## If is used -h <help> or none parameters is detailed print pod
  68
  69 if (!$opt_H && !$opt_D && !$opt_p && !$opt_F && !$opt_h) {
  70     print STDOUT "No optionas passed. Printing help\n\n";
  71     pod2usage(1);
  72 }
  73 elsif ($opt_h) {
  74     pod2usage(1);
  75 }
  76
  77
  78 ## Declaration of the name of the script and the description
  79
  80 my $patch_name = '0005load_sol100_species.pl';
  81 my $patch_descr = 'This patch adds a sol100 organism prop to each species in the list provided';
  82
  83 print STDOUT "\n+--------------------------------------------------------------------------------------------------+\n";
  84 print STDOUT "Executing the patch:\n   $patch_name.\n\nDescription:\n  $patch_descr.\n\nExecuted by:\n  $opt_p.";
  85 print STDOUT "\n+--------------------------------------------------------------------------------------------------+\n\n";
  86
  87 ## And the requeriments if you want not use all
  88 ##
  89 my @previous_requested_patches = (   ## ADD HERE
  90    );
  91
  92 ## Specify the mandatory parameters
  93
  94 if (!$opt_H || !$opt_D) {
  95     print STDOUT "\nMANDATORY PARAMETER ERROR: -D <db_name> or/and -H <db_host> parameters has not been specified for $patch_name.\n";
  96 }
  97
  98 if (!$opt_p) {
  99     print STDOUT "\nMANDATORY PARAMETER ERROR: -p <script_executor_user> parameter has not been specified for $patch_name.\n";
 100 }
 101
 102 ## Create the $schema object for the db_version object
 103 ## This should be replace for CXGN::DB::DBICFactory as soon as it can use CXGN::DB::InsertDBH
 104
 105 my $dbh =  CXGN::DB::InsertDBH->new(
 106                                      {
 107                                          dbname => $opt_D,
 108                                          dbhost => $opt_H
 109                                      }
 110                                    )->get_actual_dbh();
 111
 112 print STDOUT "\nCreating the Metadata Schema object.\n";
 113
 114 my $metadata_schema = CXGN::Metadata::Schema->connect(
 115                                                        sub { $dbh },
 116                                                       { on_connect_do => ['SET search_path TO metadata;'] },
 117                                                       );
 118
 119 print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
 120
 121 ### Now it will check if you have runned this patch or the previous patches
 122
 123 my $dbversion = CXGN::Metadata::Dbversion->new($metadata_schema)
 124                                          ->complete_checking( {
 125                                                                  patch_name  => $patch_name,
 126                                                                  patch_descr => $patch_descr,
 127                                                                  prepatch_req => \@previous_requested_patches,
 128                                                                  force => $opt_F
 129                                                               }
 130                                                              );
 131
 132
 133 ### CREATE AN METADATA OBJECT and a new metadata_id in the database for this data
 134
 135 my $metadata = CXGN::Metadata::Metadbdata->new($metadata_schema, $opt_p);
 136
 137 ### Get a new metadata_id (if you are using store function you only need to supply $metadbdata object)
 138
 139 my $metadata_id = $metadata->store()
 140                            ->get_metadata_id();
 141
 142 ### Now you can insert the data using different options:
 143 ##
 144 ##  1- By sql queryes using $dbh->do(<<EOSQL); and detailing in the tag the queries
 145 ##
 146 ##  2- Using objects with the store function
 147 ##
 148 ##  3- Using DBIx::Class first level objects
 149 ##
 150
 151 ## In this case we will use Bio::Chado::Schema DBIC
 152
 153 print STDERR "\nExecuting the SQL commands.\n";
 154
 155
 156 my $schema = Bio::Chado::Schema->connect(
 157     sub { $dbh },
 158     { on_connect_do => ['SET search_path TO public, metadata;'] },
 159     );
 160
 161 while ( my $s =  <DATA> )  {
 162     chomp($s);
 163     my $organism = $schema->resultset("Organism::Organism")->find(
 164         { species => $s }
 165         );
 166     warn "No organism found for species '$s' ! Check your database!\n "if !$organism;
 167     $organism->create_organismprops( { sol100 => '1' } , {autocreate => 1 }  );
 168 }
 169 ## Now it will add this new patch information to the md_version table.  It did the dbversion object before and
 170 ## set the patch_name and the patch_description, so it only need to store it.
 171
 172
 173 $dbversion->store($metadata);
 174
 175 print STDOUT "DONE!\n";
 176
 177 $dbh->commit;
 178
 179
 180
 181 # Benthamiella sp.
 182
 183 # Solanum scabrum * NEED TO UPDATE TAXONOMY FOR THIS!
 184
 185
 186
 187 __DATA__
 188 Schizanthus pinnatus
 189 Duckeodendron cestroides
 190 Goetzea elegans
 191 Metternichia principis
 192 Tsoala tubiflora
 193 Petunia x hybrida
 194 Petunia axillaris
 195 Brunfelsia uniflora
 196 Brunfelsia americana
 197 Fabiana imbricata
 198 Nierembergia scoparia
 199 Calibrachoa parviflora
 200 Schwenckia americana
 201 Browallia americana
 202 Streptosolen jamesonii
 203 Combera paradoxa
 204 Pantacantha ameghinoi
 205 Protoschwenkia mandonii
 206 Cestrum elegans
 207 Cestrum nocturnum
 208 Vestia foetida
 209 Salpiglossis sinuata
 210 Nicotiana tabacum
 211 Nicotiana sylvestris
 212 Nicotiana longiflora
 213 Duboisia hopwoodii
 214 Anthocercis littorea
 215 Grammosolen dixonii
 216 Symonanthus bancroftii
 217 Lycium barbarum
 218 Lycium horridum
 219 Grabowskia boerhaviifolia
 220 Phrodus microphyllus
 221 Nolana humifusa
 222 Nolana galapagensis
 223 Jaborosa integrifolia
 224 Jaborosa sativa
 225 Latua pubiflora
 226 Atropa belladonna
 227 Hyoscyamus niger
 228 Anisodus luridus
 229 Scopolia carniolica
 230 Przewalskia tangutica
 231 Physochlaina orientalis
 232 Nicandra physalodes
 233 Brugmansia candida
 234 Datura metel
 235 Datura stramonium
 236 Dyssochroma viridiflora
 237 Juanulloa mexicana
 238 Solandra brachycalyx
 239 Schultesianthus leucanthus
 240 Markea ulei
 241 Mandragora officinarum
 242 Mandragora caulescens
 243 Discopodium penninervium
 244 Jaltomata procumbens
 245 Solanum nigrum
 246 Solanum americanum
 247 Solanum laxum
 248 Solanum dulcamara
 249 Solanum aethiopicum
 250 Solanum melongena
 251 Solanum tuberosum
 252 Solanum lycopersicum
 253 Solanum torvum
 254 Solanum erianthum
 255 Solanum pennellii
 256 Iochroma fuchsioides
 257 Iochroma australe
 258 Acnistus arborescens
 259 Dunalia solanacea
 260 Saracha punctata
 261 Physalis peruviana
 262 Physalis ixocarpa
 263 Witheringia solanacea
 264 Physalis alkekengi
 265 Cuatresia riparia
 266 Chamaesaracha coronopus
 267 Withania somnifera
 268 Withania frutescens
 269 Tubocapsicum anomalum
 270 Salpichroa origanifolia
 271 Capsicum annuum
 272 Capsicum pubescens
 273 Lycianthes biflora
 274 Lycianthes multiflora
 275 Lycianthes moziniana
 276 Sessea corymbiflora
 277 Nicotiana benthamiana
 278 Anthotroche pannosa
 279 Lycium americanum
 280 Sclerophylax sp. Nee and Bohs 50857
 281 Exodeconus miersii
 282 Nothocestrum latifolium
 283 Aureliana fasciculata
 284 Athenaea sp. Bohs 91