bin/load_trait_props.pl

   1 #!/usr/bin/env perl
   2
   3 =head1
   4
   5 load_trait_props.pl
   6
   7 =head1 SYNOPSIS
   8
   9     $load_trait_props.pl -H [dbhost] -D [dbname] -I [input file] -o [ontology] -W
  10
  11 =head1 COMMAND-LINE OPTIONS
  12
  13  -H  host name
  14  -D  database name
  15  -w  overwrite
  16  -t  Test run . Rolling back at the end.
  17
  18 =head2 DESCRIPTION
  19
  20
  21 =head2 AUTHOR
  22
  23 Jeremy D. Edwards (jde22@cornell.edu)
  24
  25 April 2014
  26
  27 =head2 TODO
  28
  29 Add support for other spreadsheet formats
  30
  31 =cut
  32
  33 use strict;
  34 use warnings;
  35
  36 use lib 'lib';
  37 use Getopt::Std;
  38 use Bio::Chado::Schema;
  39 use Spreadsheet::ParseExcel;
  40 use CXGN::DB::InsertDBH;
  41 use CXGN::DB::Connection;
  42 use CXGN::Fieldbook::TraitProps;
  43
  44
  45 our ($opt_H, $opt_D, $opt_I, $opt_o, $opt_w, $opt_t);
  46 getopts('H:D:I:o:wt');
  47
  48
  49 sub print_help {
  50   print STDERR "A script to load trait properties\nUsage: load_trait_props.pl -D [database name] -H [database host, e.g., localhost] -I [input file] -o [ontology namespace, e.g., CO] -w\n\t-w\toverwrite existing trait properties if they exist (optional)\n\t-t\ttest run.  roll back at the end\n";
  51 }
  52
  53
  54 if (!$opt_D || !$opt_H || !$opt_I || !$opt_o) {
  55   print_help();
  56   die("Exiting: options missing\n");
  57 }
  58
  59
  60 #try to open the excel file and report any errors
  61 my $parser   = Spreadsheet::ParseExcel->new();
  62 my $excel_obj = $parser->parse($opt_I);
  63
  64 if ( !$excel_obj ) {
  65   die "Input file error: ".$parser->error()."\n";
  66 }
  67
  68 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
  69 my ( $row_min, $row_max ) = $worksheet->row_range();
  70 my ( $col_min, $col_max ) = $worksheet->col_range();
  71
  72 if (($col_max - $col_min)  < 1 || ($row_max - $row_min) < 1 ) { #must have header and at least one row of phenotypes
  73   die "Input file error: spreadsheet is missing header\n";
  74 }
  75
  76 my $trait_name_head;
  77
  78 if ($worksheet->get_cell(0,0)) {
  79   $trait_name_head  = $worksheet->get_cell(0,0)->value();
  80 }
  81
  82 if (!$trait_name_head || $trait_name_head ne 'trait_name') {
  83   die "Input file error: no \"trait_name\" in header\n";
  84 }
  85
  86 my @trait_property_names = qw(
  87                                trait_format
  88                                trait_default_value
  89                                trait_minimum
  90                                trait_maximum
  91                                trait_categories
  92                                trait_details
  93                             );
  94
  95 #check header for property names
  96 for (my $column_number = 1; $column_number <= scalar @trait_property_names; $column_number++) {
  97   my $property_name = $trait_property_names[$column_number-1];
  98   if ( !($worksheet->get_cell(0,$column_number)) || !($worksheet->get_cell(0,$column_number)->value() eq $property_name) ) {
  99     die "Input file error: no \"$property_name\" in header\n";
 100   }
 101 }
 102
 103 my @trait_props_data;
 104
 105
 106 for my $row ( 1 .. $row_max ) {
 107   my %trait_props;
 108   my $trait_name;
 109   my $current_row = $row+1;
 110
 111
 112   if ($worksheet->get_cell($row,0)) {
 113     $trait_name = $worksheet->get_cell($row,0)->value();
 114     $trait_props{'trait_name'}=$trait_name;
 115   } else {
 116     next; #skip blank lines
 117   }
 118
 119   my $prop_column = 1;
 120   foreach my $property_name (@trait_property_names) {
 121     if ($worksheet->get_cell($row,$prop_column)) {
 122       if (($worksheet->get_cell($row,$prop_column)->value()) && ($worksheet->get_cell($row,$prop_column)->value() ne '')) {
 123         $trait_props{$property_name}=$worksheet->get_cell($row,$prop_column)->value();
 124       }
 125     }
 126     $prop_column++;
 127   }
 128
 129   push @trait_props_data, \%trait_props;
 130
 131 }
 132
 133 my $dbh = CXGN::DB::InsertDBH
 134   ->new({
 135          dbname => $opt_D,
 136          dbhost => $opt_H,
 137          dbargs => {AutoCommit => 1,
 138                     RaiseError => 1},
 139         });
 140
 141 my $overwrite_existing_props = 0;
 142
 143 if ($opt_w){
 144   $overwrite_existing_props = 1;
 145 }
 146
 147 my $is_test_run = 0;
 148
 149 if ($opt_t){
 150   $is_test_run = 1;
 151 }
 152
 153 my $chado_schema = Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() } );
 154
 155 my $db_name = $opt_o;
 156
 157 my $trait_props = CXGN::Fieldbook::TraitProps->new({ chado_schema => $chado_schema, db_name => $db_name, trait_names_and_props => \@trait_props_data, overwrite => $overwrite_existing_props, is_test_run => $is_test_run});
 158
 159 print STDERR "Validating data...\t";
 160 my $validate=$trait_props->validate();
 161
 162 if (!$validate) {
 163   die("input data is not valid\n");
 164 } else {
 165   print STDERR "input data is valid\n";
 166 }
 167
 168 print STDERR "Storing data...\t\t";
 169 my $store = $trait_props->store();
 170
 171 if (!$store){
 172   if (!$is_test_run) {
 173     die("\n\nerror storing data\n");
 174   }
 175 } else {
 176   print STDERR "successfully stored data\n";
 177 }
 178
 179
 180