lib/CXGN/JSONProp.pm

   1
   2 =head1 NAME
   3
   4 CXGN::JSONProp - an abstract class for implementing different prop objects that store data in json format
   5
   6 =head1 DESCRIPTION
   7
   8 A subclass needs to implement accessors for each property that is stored as a json key. A BUILD function needs to be implemented that defines some metadata about tables and namespaces that the object has to access.
   9
  10 Example implementation of a subclass:
  11
  12
  13   package TestProp;
  14
  15   use Moose;
  16
  17   use Data::Dumper;
  18
  19   BEGIN { extends 'CXGN::JSONProp'; }
  20
  21   has 'info_field1' => (isa => 'Str', is => 'rw');
  22
  23   has 'info_field2' => (isa => 'Str', is => 'rw');
  24
  25   sub BUILD {
  26       my $self = shift;
  27       my $args = shift;
  28
  29       $self->prop_table('projectprop');
  30       $self->prop_namespace('Project::Projectprop');
  31       $self->prop_primary_key('projectprop_id');
  32       $self->prop_type('analysis_metadata_json');
  33       $self->cv_name('project_property');
  34       $self->allowed_fields( [ qw | info_field1 info_field2 | ] );
  35       $self->parent_table('project');
  36       $self->parent_primary_key('project_id');
  37
  38       $self->load();
  39    }
  40
  41 =head1 AUTHOR
  42
  43 Lukas Mueller <lam87@cornell.edu>
  44
  45 =cut
  46
  47 1;
  48
  49
  50
  51 package CXGN::JSONProp;
  52
  53 use Moose;
  54 use POSIX;
  55
  56 use Data::Dumper;
  57 use Bio::Chado::Schema;
  58 use CXGN::People::Schema;
  59 use JSON::Any;
  60 use Try::Tiny;
  61 use SGN::Model::Cvterm;
  62
  63 has 'bcs_schema' => ( isa => 'Bio::Chado::Schema', is => 'rw');
  64
  65 has 'people_schema' => ( isa => 'CXGN::People::Schema', is => 'rw');
  66
  67 has 'prop_table' => (isa => 'Str', is => 'rw', default => 'Set_in_subclass!'); # for example, 'stockprop'
  68
  69 has 'prop_namespace' => (isa => 'Str', is => 'rw', default => 'Set_in_subclass!'); # for example 'Stock::Stockprop'
  70
  71 has '_prop_type_id' => (isa => 'Int', is => 'rw');
  72
  73 has 'prop_primary_key' => (isa => 'Str', is => 'rw'); # set in subclass
  74
  75 has 'prop_type' => (isa => 'Str', is => 'rw'); # the type given by the type_id, set in subclass
  76
  77 has 'cv_name' => (isa => 'Str', is => 'rw');  # set in subclass
  78
  79 has 'allowed_fields' => (isa => 'Ref', is => 'rw', default =>  sub {  [ qw | | ] } );  # override in subclass
  80
  81 has 'prop_id' => (isa => 'Maybe[Int]', is => 'rw');
  82
  83 has 'parent_table' => (isa => 'Str', is => 'rw');  # set in subclass
  84
  85 has 'parent_primary_key' => (isa => 'Str', is => 'rw'); # set in subclass
  86
  87 has 'parent_id' => (isa => 'Maybe[Int]', is => 'rw');
  88
  89
  90 sub load {  # must be called from BUILD in subclass
  91     my $self = shift;
  92
  93     select(STDERR);
  94     $|=1;
  95     print STDERR "prop_type ".$self->prop_type()." cv_name ".$self->cv_name()."\n";
  96
  97     my $cvterm_row = SGN::Model::Cvterm->get_cvterm_row($self->bcs_schema(), $self->prop_type(), $self->cv_name());
  98
  99     if (!$cvterm_row) { die "Can't find term ".$self->prop_type()." in cv ".$self->cv_name()."!!!!"; }
 100
 101     $self->_prop_type_id($cvterm_row->cvterm_id());
 102
 103     print STDERR "LOAD PROP ID = ".$self->prop_id()."\n";
 104
 105     if ($self->prop_id()) {
 106         my $rs;
 107         if ($self->prop_table eq 'sp_orderprop') {
 108             $rs = $self->people_schema()->resultset($self->prop_namespace())->search( { $self->prop_primary_key() => $self->prop_id() });
 109         } else {
 110             $rs = $self->bcs_schema()->resultset($self->prop_namespace())->search( { $self->prop_primary_key() => $self->prop_id() });
 111         }
 112             if (my $row = $rs->next()) {
 113                 if ($row->type_id() == $self->_prop_type_id()) {
 114                     #print STDERR "ROW VALUE = ".$row->value().", TYPEID=".$row->type_id()." TYPE = ".$self->prop_type()."\n";
 115                 my $parent_primary_key = $self->parent_primary_key();
 116                 my $parent_id = $row->$parent_primary_key;
 117                 $self->parent_id($parent_id);
 118                 $self->from_json($row->value());
 119             } else {
 120                 print STDERR "Skipping property unrelated to metadata...\n";
 121             }
 122         } else {
 123             die "The object with id ".$self->prop_id()." does not exist. Sorry!";
 124         }
 125     }
 126 }
 127
 128 sub from_json {
 129     my $self = shift;
 130     my $json = shift;
 131
 132     my $data;
 133     eval {
 134         $data = JSON::Any->decode($json);
 135     };
 136     if ($@) {
 137         print STDERR "JSON not valid ($json) - ignoring.\n";
 138     }
 139
 140     $self->from_hash($data);
 141
 142 }
 143
 144 sub from_hash {
 145     my $self = shift;
 146     my $hash = shift;
 147
 148     my $allowed_fields = $self->allowed_fields();
 149
 150     #print STDERR Dumper($hash);
 151
 152     foreach my $f (@$allowed_fields) {
 153         if (exists($hash->{$f})) {
 154             #print STDERR "Processing $f ($hash->{$f})...\n";
 155             $self->$f($hash->{$f});
 156         }
 157     }
 158 }
 159
 160 sub to_json {
 161     my $self = shift;
 162
 163     my $allowed_fields = $self->allowed_fields();
 164
 165     #print STDERR Dumper($allowed_fields);
 166     my $data;
 167
 168     foreach my $f (@$allowed_fields) {
 169         if (defined($self->$f())) {
 170             $data->{$f} = $self->$f();
 171         }
 172     }
 173
 174     my $json;
 175     eval { $json = JSON::Any->encode($data); };
 176     if ($@) { print STDERR "Warning! Data is not valid json ($json)\n"; }
 177     return $json;
 178 }
 179
 180 sub to_hashref {
 181     my $self = shift;
 182     my $allowed_fields = $self->allowed_fields();
 183     my $data;
 184
 185     foreach my $f (@$allowed_fields) {
 186         if (defined($self->$f())) {
 187             $data->{$f} = $self->$f();
 188         }
 189     }
 190     return $data;
 191 }
 192
 193 sub validate {   # override in subclass
 194     my $self = shift;
 195
 196     my @errors = ();
 197     my @warnings = ();
 198
 199     # check keys in the info hash...
 200
 201     if (@errors) {
 202         die join("\n", @errors);
 203     }
 204 }
 205
 206 =head2 Class methods
 207
 208
 209 =head2 get_props($schema, $prop_id)
 210
 211  Usage:        my @seq_projects = $stock->get_props($schema, $stock_id);
 212  Desc:
 213  Ret:
 214  Args:
 215  Side Effects:
 216  Example:
 217
 218 =cut
 219
 220 sub get_props {
 221     my $class = shift;
 222     my $schema = shift;
 223     my $parent_id = shift;
 224
 225     print STDERR "get_props(): creating object; using parent_id $parent_id\n";
 226     my $obj = $class->new( { bcs_schema => $schema });
 227
 228     my @props = $obj->_retrieve_props($schema, $parent_id);
 229     print STDERR "Props = ".Dumper(\@props);
 230     my @hashes = ();
 231     foreach my $sp (@props) {
 232         my $json = $sp->[2];
 233         my $hash;
 234
 235         eval {
 236             $hash = JSON::Any->jsonToObj($json);
 237             $hash->{prop_id} = $sp->[0];
 238             $hash->{parent_id} = $sp->[1]
 239         };
 240
 241         if ($@) {
 242             print STDERR "Warning: $json is not valid json in prop ".$sp->[0].".!\n";
 243         }
 244         push @hashes, $hash;
 245     }
 246
 247     return \@hashes;
 248 }
 249
 250
 251 =head2 filter_props()
 252
 253  Usage:     my $filtered_props = $JSONPropClass->filter_props({ schema=> $schema, conditions => \%conditions });
 254  Desc:      This class method can be used to get props that match the provided search criteria
 255  Ret:       a hash with the results metadata and the matching props:
 256                 page: current page number
 257                 maxPage: the number of the last page
 258                 pageSize: (max) number of results per page
 259                 total: total number of results
 260                 results: an arrayref of hashes containing the parent_id, prop_id, all of the prop values, and any specified parent fields
 261  Args:      schema = Bio::Chado::Schema
 262             conditions = (optional, default=unfiltered/all props) a hashref of DBIx where conditions to filter the props by.
 263                 If you're filtering by a prop value, you should use the form: "value::json->>'prop_name' => 'prop value'"
 264             parent_fields = (optional, default=none) an arrayref of the names of fields from the parent table to include in the results
 265                 NOTE: if a parent field is used in the search conditions, it should also be included here
 266             order_by = (optional) the field to sort the results by:
 267                 order_by => "stockprop_id"                               // sort by ascending stockprop_id
 268                 order_by => { "-desc" => "value::json->'timestamp'" }    // sort by descending timestamp in the json value
 269             page = (optional, default=1) the page number of results to return
 270             pageSize = (optional, default=1000) the number of results to return per page
 271  Example:   my $conditions = {
 272                 '-and' => [
 273                     { 'stock.uniquename' => [ 'TEST_SEEDLOT_1', 'TEST_SEEDLOT_2' ] },
 274                     { 'value::json->>\'timestamp\'' => { '>=' => '2021-06-01 00:00:00' } },
 275                     { 'value::json->>\'timestamp\'' => { '<=' => '2021-06-30 24:00:00' } },
 276                     { 'value::json->>\'operator\'' => [ 'dwaring87' ] }
 277                 ],
 278                 '-or' => [
 279                     {
 280                         '-and' => [
 281                             { 'value::json->>\'cvterm_id\'' => '78094' },
 282                             { 'value::json->>\'value\'' => [ 'Successful' ] }
 283                         ]
 284                     },
 285                     {
 286                         '-and' => [
 287                             { 'value::json->>\'cvterm_id\'' => '78085' },
 288                             { 'value::json->>\'value\'' => [ 'High', 'Medium' ] }
 289                         ]
 290                     },
 291                     { 'value::json->>\'cvterm_id\'' => '78090' }
 292                 ]
 293             };
 294             my $filtered_props = $JSONPropClass->filter_props({
 295                 schema => $schema,
 296                 conditions => $conditions,
 297                 parent_fields => ["uniquename"],
 298                 order_by => { "-desc" => "value::json->>'timestamp'" }
 299             });
 300
 301 =cut
 302
 303 sub filter_props {
 304     my $class = shift;
 305     my $args = shift;
 306     my $schema = $args->{schema};
 307     my $conditions = $args->{conditions};
 308     my $parent_fields = $args->{parent_fields};
 309     my $order_by = $args->{order_by};
 310     my $page = $args->{page} || 1;
 311     my $pageSize = $args->{pageSize} || 1000;
 312     my $type_id = $class->_prop_type_id();
 313
 314     # Build the search conditions
 315     my @all_conditions = ();
 316     push(@all_conditions, { 'me.type_id' => $class->_prop_type_id() });
 317     if ( $conditions ) {
 318         push(@all_conditions, $conditions);
 319     }
 320
 321     # Build the filter query using a ResultSet
 322     my @s = ();
 323     my @a = ();
 324     foreach my $f (@{$class->allowed_fields()}) {
 325         push(@s, "value::json->>'$f'");
 326         push(@a, $f);
 327     }
 328     my $props = $schema->resultset($class->prop_namespace())->search(
 329         { '-and' => \@all_conditions },
 330         {
 331             'prefetch' => defined $parent_fields ? $class->parent_table() : undef,
 332             '+select' => \@s,
 333             '+as' => \@a,
 334             'order_by' => $order_by,
 335             'page' => $page,
 336             'rows' => $pageSize
 337         }
 338     );
 339     my $pager = $props->pager();
 340     my $total = $pager->total_entries();
 341
 342     # Parse the results
 343     my @filtered_props = ();
 344     while (my $r = $props->next) {
 345         my %p = (
 346             $class->prop_primary_key() => $r->get_column($class->prop_primary_key()),
 347             $class->parent_primary_key() => $r->get_column($class->parent_primary_key())
 348         );
 349         foreach my $f (@a) {
 350             $p{$f} = $r->get_column($f);
 351         }
 352         if ( defined $parent_fields ) {
 353             my $pt = $class->parent_table();
 354             foreach my $pf (@$parent_fields) {
 355                 $p{$pf} = $r->$pt->$pf;
 356             }
 357         }
 358         push(@filtered_props, \%p);
 359     }
 360
 361     # Return the results and page info
 362     my %results = (
 363         page => $page,
 364         maxPage => int(ceil($total/$pageSize)),
 365         total => $total,
 366         pageSize => $pageSize,
 367         results => \@filtered_props
 368     );
 369     return \%results;
 370 }
 371
 372
 373 =head2 OBJECT METHODS
 374
 375 =head2 method store()
 376
 377  Usage:         $s->store();
 378  Desc:          creates a sequencing project info in the stockprop
 379  Ret:
 380  Args:
 381
 382
 383  Side Effects:
 384  Example:
 385
 386 =cut
 387
 388 sub store {
 389     my $self = shift;
 390
 391     ## TO DO: need to check for rank
 392
 393
 394     if ($self->prop_id()) {
 395         # update
 396         print STDERR "UPDATING JSONPROP ".$self->to_json()."\n";
 397         my $row = $self->bcs_schema()->resultset($self->prop_namespace())->find( { $self->prop_primary_key() => $self->prop_id() } );
 398         if ($row) {
 399             $row->value($self->to_json());
 400             $row->update();
 401         }
 402     }
 403     else {
 404         # insert
 405         $self->store_by_rank();
 406     }
 407
 408 }
 409
 410
 411 =head2 method store_by_rank()
 412
 413 Usage:         $prop->store_by_rank();
 414 Desc:          store multiple props with the same type_id
 415 Ret:
 416 Args:
 417
 418
 419 Side Effects:
 420 Example:
 421
 422 =cut
 423
 424
 425 sub store_by_rank {
 426     my $self = shift;
 427
 428     my $rs = $self->bcs_schema()->resultset($self->prop_namespace())->search( { $self->parent_primary_key() => $self->parent_id(), type_id => $self->_prop_type_id() });
 429
 430     my $rank;
 431     if ($rs->count() > 0) {
 432         $rank = $rs->get_column("rank")->max();
 433     }
 434     $rank++;
 435
 436         my $row = $self->bcs_schema()->resultset($self->prop_namespace())->create( { $self->parent_primary_key()=> $self->parent_id(), value => $self->to_json(), type_id => $self->_prop_type_id(), rank => $rank });
 437         my $prop_primary_key = $self->prop_primary_key();
 438         $self->prop_id($row->$prop_primary_key);
 439
 440 }
 441
 442
 443 =head2 method store_sp_orderprop()
 444
 445 Usage:         $prop->store_sp_orderprop();
 446 Desc:
 447 Ret:
 448 Args:
 449
 450
 451 Side Effects:
 452 Example:
 453
 454 =cut
 455
 456 sub store_sp_orderprop {
 457     my $self = shift;
 458     print STDERR "PROP ID =".Dumper($self->prop_id())."\n";
 459     if ($self->prop_id()) {
 460         print STDERR "UPDATING JSONPROP ".$self->to_json()."\n";
 461         my $row = $self->people_schema()->resultset($self->prop_namespace())->find( { $self->prop_primary_key() => $self->prop_id() } );
 462         if ($row) {
 463             $row->value($self->to_json());
 464             $row->update();
 465         }
 466     } else {
 467         my $row = $self->people_schema()->resultset($self->prop_namespace())->create( { $self->parent_primary_key()=> $self->parent_id(), value => $self->to_json(), type_id => $self->_prop_type_id(), rank => 1});
 468         my $prop_primary_key = $self->prop_primary_key();
 469         $self->prop_id($row->$prop_primary_key);
 470     }
 471 }
 472
 473
 474 =head2 method delete()
 475
 476  Usage:
 477  Desc:
 478  Ret:
 479  Args:
 480  Side Effects:
 481  Example:
 482
 483 =cut
 484
 485 sub delete {
 486     my $self = shift;
 487
 488     print STDERR "Prop type id = ".$self->_prop_type_id()."\n";
 489     print STDERR "Parent primary key: ".$self->parent_primary_key()."\n";
 490     print STDERR "Parent id: ".$self->parent_id()."\n";
 491     print STDERR "Prop primary key: ".$self->prop_primary_key()."\n";
 492     print STDERR "Prop ID : ".$self->prop_id()."\n";
 493
 494     my $prop = $self->bcs_schema()->resultset($self->prop_namespace())->find({ type_id=>$self->_prop_type_id(), $self->parent_primary_key() => $self->parent_id(), $self->prop_primary_key() => $self->prop_id() });
 495
 496     if (!$prop) {
 497         print STDERR "Prop not found!\n";
 498         return 0;
 499     }
 500     else {
 501         $prop->delete();
 502         return 1;
 503     }
 504 }
 505
 506 =head2 _retrieve_props
 507
 508   Usage:
 509   Desc:         Retrieves prop as a list of [prop_id, value]
 510   Ret:
 511   Args:         schema, parent_id, prop_type
 512   Side Effects:
 513   Example:
 514
 515 =cut
 516
 517 sub _retrieve_props {
 518     my $self = shift;
 519     my $schema = shift;
 520     my $parent_id = shift;
 521
 522     print STDERR "Getting props for parent_id $parent_id, ".join(",", $self->parent_primary_key(), $self->_prop_type_id(), $self->prop_primary_key())."\n";
 523     my @results;
 524
 525     my $prop_primary_key = $self->prop_primary_key();
 526     my $parent_primary_key = $self->parent_primary_key();
 527
 528     if ($parent_id) {
 529         eval {
 530             my $rs = $schema->resultset($self->prop_namespace())->search({ $self->parent_primary_key() => $parent_id, type_id => $self->_prop_type_id() }, { order_by => {-asc => $self->prop_primary_key() } });
 531
 532             while (my $r = $rs->next()){
 533                 push @results, [ $r->$prop_primary_key, $r->$parent_primary_key, $r->value() ];
 534             }
 535         };
 536     }
 537     else {
 538         eval {
 539
 540             print STDERR "Searching all ".$self->_prop_type_id()." in namespace ".$self->prop_namespace()." (primary key is ".$prop_primary_key."...\n";
 541
 542             my $rs = $schema->resultset($self->prop_namespace())->search({ type_id => $self->_prop_type_id() }, { order_by => {-asc => $prop_primary_key } });
 543
 544             while (my $r = $rs->next()){
 545                 push @results, [ $r->$prop_primary_key(), $r->$parent_primary_key(), $r->value() ];
 546             }
 547         };
 548     }
 549
 550     if ($@) {
 551         print STDERR "ERROR $@\n";
 552     }
 553
 554     return @results;
 555 }
 556
 557 "CXGN::JSONProp";