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  -o  Ontology name (from db table, e.g. "GO")
  18  -I  input file, either .xls or .xlsx format
  19
  20 =head2 DESCRIPTION
  21
  22 The input file should have the following column headers:
  23
  24   trait_name
  25   trait_format
  26   trait_default_value
  27   trait_minimum
  28   trait_maximum
  29   trait_categories
  30   trait_details
  31
  32   trait_name: the name of the variable human readable form (e.g., "plant height in cm")
  33   trait_format: can be numeric, qualitative, date or boolean
  34   trait_default_value: is the value if no value is given
  35   trait_categories: are the different possible names of the categories, separated by /, for example "1/2/3/4/5"
  36   trait_details: string describing the trait categories
  37
  38 =head2 AUTHOR
  39
  40 Jeremy D. Edwards (jde22@cornell.edu)
  41
  42 April 2014
  43
  44 =head2 TODO
  45
  46 Add support for other spreadsheet formats
  47
  48 =cut
  49
  50 use strict;
  51 use warnings;
  52
  53 use lib 'lib';
  54 use Getopt::Std;
  55 use Bio::Chado::Schema;
  56 use Spreadsheet::ParseExcel;
  57 use Spreadsheet::ParseXLSX;
  58 use CXGN::DB::InsertDBH;
  59 use CXGN::DB::Connection;
  60 use CXGN::Fieldbook::TraitProps;
  61
  62
  63 our ($opt_H, $opt_D, $opt_I, $opt_o, $opt_w, $opt_t);
  64 getopts('H:D:I:o:wt');
  65
  66
  67 sub print_help {
  68     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";
  69 }
  70
  71
  72 if (!$opt_D || !$opt_H || !$opt_I || !$opt_o) {
  73     print_help();
  74     die("Exiting: options missing\n");
  75 }
  76
  77 # Match a dot, extension .xls / .xlsx
  78 my ($extension) = $opt_I =~ /(\.[^.]+)$/;
  79 my $parser;
  80
  81 if ($extension eq '.xlsx') {
  82     $parser = Spreadsheet::ParseXLSX->new();
  83 }
  84 else {
  85     $parser = Spreadsheet::ParseExcel->new();
  86 }
  87
  88 #try to open the excel file and report any errors
  89 my $excel_obj = $parser->parse($opt_I);
  90
  91 if ( !$excel_obj ) {
  92     die "Input file error: ".$parser->error()."\n";
  93 }
  94
  95 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
  96 my ( $row_min, $row_max ) = $worksheet->row_range();
  97 my ( $col_min, $col_max ) = $worksheet->col_range();
  98
  99 if (($col_max - $col_min)  < 1 || ($row_max - $row_min) < 1 ) { #must have header and at least one row of phenotypes
 100     die "Input file error: spreadsheet is missing header\n";
 101 }
 102
 103 my $trait_name_head;
 104
 105 if ($worksheet->get_cell(0,0)) {
 106     $trait_name_head  = $worksheet->get_cell(0,0)->value();
 107 }
 108
 109 if (!$trait_name_head || $trait_name_head ne 'trait_name') {
 110     die "Input file error: no \"trait_name\" in header\n";
 111 }
 112
 113 my @trait_property_names = qw(
 114     trait_format
 115     trait_default_value
 116     trait_minimum
 117     trait_maximum
 118     trait_categories
 119     trait_details
 120     );
 121
 122 #check header for property names
 123 for (my $column_number = 1; $column_number <= scalar @trait_property_names; $column_number++) {
 124     my $property_name = $trait_property_names[$column_number-1];
 125     if ( !($worksheet->get_cell(0,$column_number)) || !($worksheet->get_cell(0,$column_number)->value() eq $property_name) ) {
 126         die "Input file error: no \"$property_name\" in header\n";
 127     }
 128 }
 129
 130 my @trait_props_data;
 131
 132
 133 for my $row ( 1 .. $row_max ) {
 134     my %trait_props;
 135     my $trait_name;
 136     my $current_row = $row+1;
 137
 138
 139     if ($worksheet->get_cell($row,0)) {
 140         $trait_name = $worksheet->get_cell($row,0)->value();
 141         $trait_props{'trait_name'}=$trait_name;
 142     } else {
 143         next; #skip blank lines
 144     }
 145
 146     my $prop_column = 1;
 147     foreach my $property_name (@trait_property_names) {
 148         if ($worksheet->get_cell($row,$prop_column)) {
 149             my $value = $worksheet->get_cell($row,$prop_column)->value();
 150             if (defined($value) && ($value ne '')) {
 151                 $trait_props{$property_name}=$worksheet->get_cell($row,$prop_column)->value();
 152             }
 153         }
 154         $prop_column++;
 155     }
 156
 157     push @trait_props_data, \%trait_props;
 158
 159 }
 160
 161 my $dbh = CXGN::DB::InsertDBH
 162     ->new({
 163         dbname => $opt_D,
 164         dbhost => $opt_H,
 165         dbargs => {AutoCommit => 1,
 166                    RaiseError => 1},
 167           });
 168
 169 my $overwrite_existing_props = 0;
 170
 171 if ($opt_w){
 172     $overwrite_existing_props = 1;
 173 }
 174
 175 my $is_test_run = 0;
 176
 177 if ($opt_t){
 178     $is_test_run = 1;
 179 }
 180
 181 my $chado_schema = Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() } );
 182
 183 my $db_name = $opt_o;
 184
 185 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});
 186
 187 print STDERR "Validating data...\t";
 188 my $validate=$trait_props->validate();
 189
 190 if (!$validate) {
 191     die("input data is not valid\n");
 192 } else {
 193     print STDERR "input data is valid\n";
 194 }
 195
 196 print STDERR "Storing data...\t\t";
 197 my $store = $trait_props->store();
 198
 199 if (!$store){
 200     if (!$is_test_run) {
 201         die("\n\nerror storing data\n");
 202     }
 203 } else {
 204     print STDERR "successfully stored data\n";
 205 }
 206
 207
 208