lib/CXGN/BreederSearch.pm

   1 =head1 NAME
   2
   3 CXGN::BreederSearch - class for retrieving breeder information for the breeder search wizard
   4
   5 =head1 AUTHORS
   6
   7 Lukas Mueller <lam87@cornell.edu>
   8 Aimin Yan <ay247@cornell.edu>
   9
  10 =head1 METHODS
  11
  12 =cut
  13
  14 package CXGN::BreederSearch;
  15
  16 use Moose;
  17 use Data::Dumper;
  18 use Try::Tiny;
  19
  20 has 'dbh' => (
  21     is  => 'rw',
  22     required => 1,
  23     );
  24 has 'dbname' => (
  25     is => 'rw',
  26     isa => 'Str',
  27     );
  28
  29 =head2 metadata_query
  30
  31  Usage:        my $results_ref = $bs->metadata_query($criteria_list, $dataref, $queryref);
  32
  33  Ret:          returns a hash with a list of ids and names that were matched.
  34
  35  Args:         criteria_list: a comma separated string of criteria categories. Possible
  36                criteria include accessions, breeding programs, genotyping protocols,
  37                locations, plots, plants, trials, trial_designs, traits, and years. The last
  38                criteria in the list is the return type.
  39
  40                dataref: The dataref is a hashref of hashrefs. The first key
  41                is the target of the transformation, and the second is the
  42                source type of the transformation, containing comma separated
  43                values of the source type.
  44
  45                queryref: same structure as dataref, but instead of storing ids it stores a
  46                1 if user to retrieve an intersection of matches, or 0 for the default union,
  47                or a fractional number (between 0 and 1) to filter a miniumum match percentage
  48
  49  Side Effects: none
  50  Example:   retrieving all the trials from location 'test_location' (location_id = 23) and year '2014' in the fixture db:
  51
  52  my $bs = CXGN::BreederSearch->new( { dbh=>$dbh } );
  53  my $criteria_list = [
  54                 'years',
  55                 'locations',
  56                 'trials'
  57               ];
  58  my $dataref = {
  59                 'trials' => {
  60                             'locations' => '\'23\'',
  61                             'years' => '\'2014\''
  62                           }
  63               };
  64  my $queryref = {
  65                 'trials' => {
  66                             'locations' => 0,
  67                             'years' => 0
  68                           }
  69               };
  70
  71  my $results_ref = $bs ->metadata_query($criteria_list, $dataref, $queryref);
  72
  73  print Dumper($results);
  74  will give you:
  75
  76             {
  77                 'results' => [
  78                                [
  79                                  139,
  80                                  'Kasese solgs trial'
  81                                ],
  82                                [
  83                                  137,
  84                                  'test_trial'
  85                                ],
  86                                [
  87                                  141,
  88                                  'trial2 NaCRRI'
  89                                ]
  90                              ]
  91               },
  92 =cut
  93
  94 sub metadata_query {
  95   my $self = shift;
  96   my $criteria_list = shift;
  97   my $dataref = shift;
  98   my $queryref = shift;
  99   my $h;
 100   # print STDERR "criteria_list=" . Dumper($criteria_list);
 101   # print STDERR "dataref=" . Dumper($dataref);
 102   # print STDERR "queryref=" . Dumper($queryref);
 103
 104   my $target_table = $criteria_list->[-1];
 105   # print STDERR "target_table=". $target_table . "\n";
 106   my $target = $target_table;
 107   $target =~ s/s$//;
 108   # print STDERR "target=$target\n";
 109
 110   my $select = "SELECT ".$target."_id, ".$target."_name ";
 111   my $group = "GROUP BY ".$target."_id, ".$target."_name ";
 112
 113   my $full_query;
 114   if (!$dataref->{"$target_table"}) {
 115     my $from = "FROM public.". $target_table;
 116     my $where = " WHERE ".$target."_id IS NOT NULL";
 117           $full_query = $select . $from . $where;
 118   }
 119   else {
 120           my @queries;
 121           foreach my $category (@$criteria_list) {
 122
 123       # print STDERR "==> BUILDING QUERY FOR CATEGORY: $category\n";
 124
 125       if ($dataref->{$criteria_list->[-1]}->{$category}) {
 126         my $query;
 127                     my @categories = ($target_table, $category);
 128               @categories = sort @categories;
 129               my $from = "FROM public.". $categories[0] ."x". $categories[1] . " JOIN public." . $target_table . " USING(" . $target."_id) ";
 130         my $criterion = $category;
 131         $criterion =~ s/s$//;
 132         my $match = $queryref->{$criteria_list->[-1]}->{$category};
 133         if ( !$match ) {
 134           $match = 0;
 135         }
 136
 137         # print STDERR "... Match: $match\n";
 138
 139         my $total = scalar(split(',', $dataref->{$criteria_list->[-1]}->{$category}));
 140         my $inner_select = $select . ", COUNT(" . $criterion . "_id)/" . $total . "::decimal AS match ";
 141         my $where = "WHERE ". $criterion. "_id IN (" . $dataref->{$criteria_list->[-1]}->{$category} . ") ";
 142         $query = $inner_select . $from . $where . $group;
 143
 144         my $outer_query = "SELECT i." . $target . "_id, i." . $target . "_name, i.match ";
 145         $outer_query .= "FROM ($query) AS i ";
 146         $outer_query .= "WHERE i.match >= " . $match;
 147
 148         my $outest_query = "SELECT j." . $target . "_id, j." . $target . "_name ";
 149         $outest_query .= "FROM ($outer_query) AS j ";
 150
 151         # print STDERR "... Query: $outest_query\n";
 152         push @queries, $outest_query;
 153       }
 154     }
 155     $full_query = join (" INTERSECT ", @queries);
 156   }
 157   $full_query .= " ORDER BY 2";
 158   # print STDERR "FULL QUERY: $full_query\n";
 159   $h = $self->dbh->prepare($full_query);
 160   $h->execute();
 161
 162   my @results;
 163   while (my ($id, $name) = $h->fetchrow_array()) {
 164     push @results, [ $id, $name ];
 165   }
 166
 167   return { results => \@results };
 168
 169 }
 170
 171 =head2 avg_phenotypes_query
 172
 173 parameters: trait_id, trial_id, allow_missing
 174
 175 returns: values, the avg pheno value of each accession in a trial for the given trait, and column_names, an array of the trait names
 176
 177 Side Effects: none
 178
 179 =cut
 180
 181 sub avg_phenotypes_query {
 182   my $self = shift;
 183   my $trial_id = shift;
 184   my $trait_ids = shift;
 185   my $weights = shift;
 186   my $controls = shift;
 187   my @trait_ids = @$trait_ids;
 188   my @weights = @$weights;
 189   my @controls = @$controls;
 190   my $allow_missing = shift;
 191
 192   my $select = "SELECT table0.accession_id, table0.accession_name";
 193   my $from = " FROM (SELECT accession_id, accession_name FROM materialized_phenoview JOIN accessions USING (accession_id) WHERE trial_id = $trial_id GROUP BY 1,2) AS table0";
 194   for (my $i = 1; $i <= scalar @trait_ids; $i++) {
 195     $select .= ",  ROUND( CAST(table$i.trait$i AS NUMERIC), 2)";
 196     $from .= " JOIN (SELECT accession_id, accession_name, AVG(value::REAL) AS trait$i FROM materialized_phenoview JOIN accessions USING (accession_id) JOIN phenotype USING (phenotype_id) WHERE trial_id = $trial_id AND trait_id = ? GROUP BY 1,2) AS table$i USING (accession_id)";
 197   }
 198   my $query = $select . $from . " ORDER BY 2";
 199   if ($allow_missing eq 'true') { $query =~ s/JOIN/FULL OUTER JOIN/g; }
 200
 201   print STDERR "QUERY: $query\n";
 202
 203   my $h = $self->dbh->prepare($query);
 204   $h->execute(@$trait_ids);
 205
 206   my (@raw_avg_values, @reference_values, @rows_to_scale, @weighted_values);
 207
 208   if (grep { defined($_) } @controls) {
 209   while (my @row = $h->fetchrow_array()) {
 210     push @rows_to_scale, @row;
 211     my ($id, $name, @avg_values) = @row;
 212     for my $i (0..$#controls) {
 213       my $control = $controls[$i] || 0;
 214       if ($id == $control) {
 215         #print STDERR "Matched control accession $name with values @avg_values\n";
 216         if (!defined($avg_values[$i])) {
 217           return { error => "Can't scale values using control $name, it has a zero or undefined value for trait with id @$trait_ids[$i] in this trial. Please select a different control for this trait." };
 218         }
 219         $reference_values[$i] = $avg_values[$i];
 220       }
 221     }
 222   }
 223     for my $i (0..$#trait_ids) {
 224         $reference_values[$i] = 1 unless defined $reference_values[$i];
 225     }
 226
 227     #print STDERR "reference values = @reference_values\n";
 228     $h->execute(@$trait_ids);
 229     while (my ($id, $name, @avg_values) = $h->fetchrow_array()) {
 230
 231       my @scaled_values = map {sprintf("%.2f", $avg_values[$_] / $reference_values[$_])} 0..$#avg_values;
 232       my @scaled_and_weighted = map {sprintf("%.2f", $scaled_values[$_] * $weights[$_])} 0..$#scaled_values;
 233       unshift @scaled_values, '<a href="/stock/'.$id.'/view">'.$name.'</a>';
 234       push @raw_avg_values, [@scaled_values];
 235
 236       my $sum;
 237       map { $sum += $_ } @scaled_and_weighted;
 238       my $rounded_sum = sprintf("%.2f", $sum);
 239       push @scaled_and_weighted, $rounded_sum;
 240       unshift @scaled_and_weighted, '<a href="/stock/'.$id.'/view">'.$name.'</a>';
 241       push @weighted_values, [@scaled_and_weighted];
 242     }
 243
 244   } else {
 245
 246   while (my ($id, $name, @avg_values) = $h->fetchrow_array()) {
 247
 248     my @values_to_weight = @avg_values;
 249     unshift @avg_values, '<a href="/stock/'.$id.'/view">'.$name.'</a>';
 250     push @raw_avg_values, [@avg_values];
 251
 252     @values_to_weight = map {$values_to_weight[$_] * $weights[$_]} 0..$#values_to_weight;
 253     my $sum;
 254     map { $sum += $_ } @values_to_weight;
 255     unshift @values_to_weight, '<a href="/stock/'.$id.'/view">'.$name.'</a>';
 256     my $rounded_sum = sprintf("%.2f", $sum);
 257     push @values_to_weight, $rounded_sum;
 258     push @weighted_values, [@values_to_weight];
 259   }
 260
 261 }
 262
 263   my @weighted_values2 = sort { $b->[-1] <=> $a->[-1] } @weighted_values;
 264   my @weighted_values3;
 265   for (my $i = 0; $i < scalar @weighted_values2; $i++ ) {
 266     my $temp_array = $weighted_values2[$i];
 267     my @temp_array = @$temp_array;
 268     push @temp_array, $i+1;
 269     push @weighted_values3, [@temp_array];
 270   }
 271
 272   #print STDERR "avg_phenotypes: ".Dumper(@raw_avg_values);
 273   #print STDERR "avg_phenotypes: ".Dumper(@weighted_values3);
 274
 275   return {
 276     raw_avg_values => \@raw_avg_values,
 277     weighted_values => \@weighted_values3
 278   };
 279
 280 }
 281
 282 =head2 test_matviews
 283
 284 parameters: db parameters
 285
 286 returns: message detailing matview status
 287
 288 Side Effects: If they are unavailable, it will use the refresh_matviews method to populate the materialized views
 289
 290 =cut
 291
 292
 293 sub test_matviews {
 294
 295   my $self = shift;
 296   my $dbhost = shift;
 297   my $dbname = shift;
 298   my $dbuser = shift;
 299   my $dbpass = shift;
 300   my ($status, %response_hash);
 301
 302   try {
 303     my $populated_query = "select * from materialized_phenoview limit 1";
 304     my $sth = $self->dbh->prepare($populated_query);
 305     $sth->execute();
 306   } catch { #if test query fails because views aren't populated
 307     print STDERR "Using basic refresh to populate views . . .\n";
 308     $status = $self->refresh_matviews($dbhost, $dbname, $dbuser, $dbpass, 'basic');
 309     %response_hash = %$status;
 310   };
 311
 312   if (%response_hash && $response_hash{'message'} eq 'Wizard update completed!') {
 313     print STDERR "Populated views, now proceeding with query . . . .\n";
 314     return { status => "Populated views, query can proceed." };
 315   } elsif (%response_hash && $response_hash{'message'} eq 'Wizard update initiated.') {
 316     return { error => "The search wizard is temporarily unavailable while database indexes are being repopulated. Please try again later." };
 317   } elsif (%response_hash && $response_hash{'error'}) {
 318     return { error => $response_hash{'error'} };
 319   } else {
 320     return { success => "Test successful, query can proceed." };
 321   }
 322 }
 323
 324 =head2 refresh_matviews
 325
 326 parameters: db parameters, and a string to specify desired refresh type, basic or concurrent. defaults to concurrent
 327
 328 returns: message detailing success or error
 329
 330 Side Effects: Refreshes materialized views
 331
 332 =cut
 333
 334 sub refresh_matviews {
 335     my $self = shift;
 336     my $dbhost = shift;
 337     my $dbname = shift;
 338     my $dbuser = shift;
 339     my $dbpass = shift;
 340     my $materialized_view = shift || 'fullview'; #Can be 'fullview' or 'stockprop'
 341     my $refresh_type = shift || 'concurrent';
 342     my $basepath = shift;
 343     my $async = shift;
 344
 345     if (!defined($async)) {
 346         $async = 1;
 347     }
 348
 349     my $refresh_finished = 0;
 350     my $async_refresh;
 351
 352     my $q = "SELECT currently_refreshing FROM public.matviews WHERE mv_id=?";
 353     my $h = $self->dbh->prepare($q);
 354     $h->execute(1);
 355
 356     my $refreshing = $h->fetchrow_array();
 357
 358     if ($refreshing) {
 359         return { error => $materialized_view.' update already in progress . . . ' };
 360     }
 361     else {
 362         try {
 363             my $refresh_command = "perl $basepath/bin/refresh_matviews.pl -H $dbhost -D $dbname -U $dbuser -P $dbpass -m $materialized_view";
 364             $async_refresh = CXGN::Tools::Run->new();
 365             if ($refresh_type eq 'concurrent') {
 366                 print STDERR "Using CXGN::Tools::Run to ".($async ? "asynchronously" : "synchronously")." run $refresh_command -c\n";
 367                 if ($async) {
 368                     $async_refresh->run_async($refresh_command." -c");
 369                 } else {
 370                     $async_refresh->run($refresh_command." -c");
 371                 }
 372             } else {
 373                 print STDERR "Using CXGN::Tools::Run to ".($async ? "asynchronously" : "synchronously")." run $refresh_command\n";
 374                 if ($async) {
 375                     $async_refresh->run_async($refresh_command);
 376                 } else {
 377                     $async_refresh->run($refresh_command);
 378                 }
 379             }
 380
 381             for (my $i = 1; $i < 10; $i++) {
 382                 sleep($i/5);
 383                 if ($async_refresh->alive) {
 384                     next;
 385                 } else {
 386                     $refresh_finished = 1;
 387                 }
 388             }
 389
 390             if ($refresh_finished) {
 391                 return { message => $materialized_view . ' update completed!', connection => $async_refresh };
 392             } else {
 393                 return { message => $materialized_view.' update initiated.', connection => $async_refresh  };
 394             }
 395         } catch {
 396             print STDERR 'Error initiating '.$materialized_view.' update.' . $@ . "\n";
 397             return { error => 'Error initiating '.$materialized_view.' update.' . $@ };
 398         }
 399     }
 400 }
 401
 402 =head2 matviews_status
 403
 404 Desc: checks tracking table to see if materialized views are updating, and if not, when they were last updated.
 405
 406 parameters: None.
 407
 408 returns: refreshing message or timestamp
 409
 410 Side Effects: none
 411
 412 =cut
 413
 414 sub matviews_status {
 415   my $self = shift;
 416   my $q = "SELECT currently_refreshing, last_refresh FROM public.matviews WHERE mv_id=?";
 417   my $h = $self->dbh->prepare($q);
 418   $h->execute(1);
 419
 420   my ($refreshing, $timestamp) = $h->fetchrow_array();
 421
 422   if ($refreshing) {
 423     print STDERR "Wizard is already refreshing, current status: $refreshing \n";
 424     return { refreshing => "<p id='wizard_status'>Wizard update in progress . . . </p>"};
 425   }
 426   else {
 427     print STDERR "materialized views last updated $timestamp\n";
 428     return { timestamp => "<p id='wizard_status'>Wizard last updated: $timestamp</p>" };
 429   }
 430 }
 431
 432
 433
 434 1;