bin/update_trial_locations.pl

   1 #!/usr/bin/perl
   2
   3 use Getopt::Std;
   4 use CXGN::DB::InsertDBH;
   5 use Bio::Chado::Schema;
   6 use Data::Dumper;
   7 use CXGN::Trial;
   8
   9 use vars qw | $opt_H $opt_D |;
  10
  11 getopts('H:D:');
  12
  13 my $dbh = CXGN::DB::InsertDBH->new( {
  14     dbhost => $opt_H,
  15     dbname => $opt_D,
  16     } );
  17
  18
  19
  20 my $schema = Bio::Chado::Schema->connect( sub { $dbh->get_actual_dbh() } );
  21
  22 my $q = "SELECT distinct(project_id), nd_geolocation.nd_geolocation_id, nd_geolocation.description FROM  nd_experiment_project JOIN nd_experiment USING (nd_experiment_id) JOIN nd_geolocation USING (nd_geolocation_id)  ";
  23 my $h = $dbh->prepare($q);
  24 $h->execute();
  25 while (my ($project_id, $location_id, $description) = $h->fetchrow_array()) {
  26     my $trial = CXGN::Trial->new( { bcs_schema=> $schema, trial_id=>$project_id });
  27
  28     print STDERR "Adding location $description ($location_id) to trial $project_id... ";
  29
  30     my $current_location = $trial->get_location();
  31    if ( $current_location->[0]) { # whatever it is
  32         print STDERR " (already associated) ";
  33 }
  34     else {
  35         $trial->set_location($location_id);
  36     }
  37     print STDERR "Done.\n";
  38
  39 }
  40
  41 $dbh->commit();