lib/CXGN/Onto.pm

   1 package CXGN::Onto;
   2
   3 use Moose;
   4 use Moose::Util::TypeConstraints;
   5 use Data::Dumper;
   6 use JSON::Any;
   7 use Try::Tiny;
   8 use Bio::Chado::Schema;
   9 use SGN::Model::Cvterm;
  10
  11 has 'schema' => (
  12     isa => 'Bio::Chado::Schema',
  13     is => 'rw',
  14     required => 1
  15     );
  16
  17 =head2 get_terms
  18
  19 parameters: namespace
  20
  21 returns: terms in namespace
  22
  23 Side Effects: none
  24
  25 =cut
  26
  27 sub get_terms {
  28       my $self = shift;
  29       my $cv_id = shift;
  30
  31       my $query = "SELECT cvterm_id, (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS name
  32                   FROM cvterm
  33                   JOIN dbxref USING(dbxref_id)
  34                   JOIN db USING(db_id)
  35                   LEFT JOIN cvterm_relationship is_subject ON cvterm.cvterm_id = is_subject.subject_id
  36                   LEFT JOIN cvterm_relationship is_object ON cvterm.cvterm_id = is_object.object_id
  37                   WHERE cv_id = ? AND is_object.object_id IS NULL AND is_subject.subject_id IS NOT NULL
  38                   GROUP BY 1,2
  39                   ORDER BY 2,1";
  40
  41       my $h = $self->schema->storage->dbh->prepare($query);
  42       $h->execute($cv_id);
  43
  44       my @results;
  45       while (my ($id, $name) = $h->fetchrow_array()) {
  46         push @results, [$id, $name];
  47       }
  48
  49       return @results;
  50 }
  51
  52 sub get_root_nodes {
  53       my $self = shift;
  54       my $cv_type = shift;
  55
  56       my $query = "SELECT cv.cv_id, (((db.name::text || ':'::text) || dbxref.accession::text) || ' '::text) || cvterm.name AS name
  57                     FROM cv
  58                     JOIN cvprop ON(cv.cv_id = cvprop.cv_id AND cvprop.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = ?))
  59                     JOIN cvterm on(cvprop.cv_id = cvterm.cv_id)
  60                     JOIN dbxref USING(dbxref_id)
  61                     JOIN db USING(db_id)
  62                     LEFT JOIN cvterm_relationship ON(cvterm.cvterm_id=cvterm_relationship.subject_id)
  63                     WHERE cvterm_relationship.subject_id IS NULL AND cvterm.is_obsolete= 0 AND cvterm.is_relationshiptype = 0";
  64
  65       my $h = $self->schema->storage->dbh->prepare($query);
  66       $h->execute($cv_type);
  67
  68       my @results;
  69       while (my ($id, $name) = $h->fetchrow_array()) {
  70         push @results, [$id, $name];
  71       }
  72
  73       return @results;
  74 }
  75
  76
  77 sub store_composed_term {
  78     my $self = shift;
  79     my $new_trait_names = shift;
  80     #print STDERR Dumper $new_trait_names;
  81
  82     my $schema = $self->schema();
  83     my $dbh = $schema->storage->dbh;
  84
  85     my $contains_relationship = $schema->resultset("Cv::Cvterm")->find({ name => 'contains' });
  86     my $variable_relationship = $schema->resultset("Cv::Cvterm")->find({ name => 'VARIABLE_OF' });
  87
  88     my @new_terms;
  89     foreach my $name (sort keys %$new_trait_names){
  90         my $ids = $new_trait_names->{$name};
  91         my @component_ids = split ',', $ids;
  92
  93         if (scalar(@component_ids)<2){
  94             die "Should not save postcomposed term with less than 2 components\n";
  95         }
  96
  97         my $existing_trait_id = SGN::Model::Cvterm->get_trait_from_exact_components($schema, \@component_ids);
  98         if ($existing_trait_id) {
  99             print STDERR "Skipping: This trait already exists $name with the following component_ids".Dumper(\@component_ids)."\n";
 100             next;
 101         }
 102
 103         my $db = $schema->resultset("General::Db")->find_or_create({ name => 'COMP' });
 104         my $cv= $schema->resultset('Cv::Cv')->find_or_create( { name => 'composed_trait' });
 105
 106         my $accession_query = "SELECT nextval('composed_trait_ids')";
 107         my $h = $dbh->prepare($accession_query);
 108         $h->execute();
 109         my $accession = $h->fetchrow_array();
 110
 111       my $new_term_dbxref =  $schema->resultset("General::Dbxref")->create(
 112       {   db_id     => $db->get_column('db_id'),
 113                       accession => sprintf("%07d",$accession)
 114                   });
 115
 116       my $parent_term= $schema->resultset("Cv::Cvterm")->find(
 117         { cv_id  =>$cv->cv_id(),
 118           name   => 'Composed traits',
 119       });
 120
 121     #print STDERR "Parent cvterm_id = " . $parent_term->cvterm_id();
 122
 123     my $new_term = $schema->resultset('Cv::Cvterm')->find({ name=>$name });
 124     if ($new_term){
 125         print STDERR "Cvterm with name $name already exists... so components must be new\n";
 126     } else {
 127         $new_term= $schema->resultset("Cv::Cvterm")->create({
 128             cv_id  =>$cv->cv_id(),
 129             name   => $name,
 130             dbxref_id  => $new_term_dbxref-> dbxref_id()
 131         });
 132     }
 133
 134
 135     #print STDERR "New term cvterm_id = " . $new_term->cvterm_id();
 136
 137         my $variable_rel = $schema->resultset('Cv::CvtermRelationship')->find_or_create({
 138             subject_id => $new_term->cvterm_id(),
 139             object_id  => $parent_term->cvterm_id(),
 140             type_id    => $variable_relationship->cvterm_id()
 141         });
 142
 143         foreach my $component_id (@component_ids) {
 144             my $contains_rel = $schema->resultset('Cv::CvtermRelationship')->find_or_create({
 145                 subject_id => $component_id,
 146                 object_id  => $new_term->cvterm_id(),
 147                 type_id    => $contains_relationship->cvterm_id()
 148             });
 149         }
 150
 151         push @new_terms, [$new_term->cvterm_id, $new_term->name().'|COMP:'.sprintf("%07d",$accession)];
 152     }
 153
 154     #Takes long on cassavabase.. instead the materialized view is refreshed automatically in a background ajax process.
 155     #my $refresh1 = "REFRESH MATERIALIZED VIEW traits";
 156     #my $h = $dbh->prepare($refresh1);
 157     #$h->execute();
 158
 159     #my $refresh2 = "REFRESH MATERIALIZED VIEW trait_componentsXtraits";
 160     #$h = $dbh->prepare($refresh2);
 161     #$h->execute();
 162
 163     return \@new_terms;
 164 }
 165
 166 sub store_ontology_identifier {
 167     my $self = shift;
 168     my $ontology_name = shift;
 169     my $ontology_description = shift;
 170     my $ontology_identifier = shift;
 171     my $ontology_type = shift;
 172     my $schema = $self->schema();
 173     my $dbh = $schema->storage->dbh;
 174
 175     my $cv_check = $schema->resultset("Cv::Cv")->find({name=>$ontology_name});
 176     if ($cv_check) {
 177         return {
 178             error => "The ontology name $ontology_name has already been used!"
 179         };
 180     }
 181
 182     my $db_check = $schema->resultset("General::Db")->find({name=>$ontology_name});
 183     if ($db_check) {
 184         return {
 185             error => "The ontology identifier $ontology_identifier has already been used!"
 186         };
 187     }
 188
 189     my $cv_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, $ontology_type, 'composable_cvtypes')->cvterm_id();
 190
 191     my $coderef = sub {
 192         my $cv_rs = $schema->resultset("Cv::Cv")->create({
 193             name => $ontology_name,
 194             definition => $ontology_description
 195         });
 196         my $new_cv_id = $cv_rs->cv_id();
 197
 198         my $new_ontology_cvprop = $schema->resultset("Cv::Cvprop")->create({
 199             cv_id   => $new_cv_id,
 200             type_id => $cv_type_id
 201         });
 202
 203         my $db_rs = $schema->resultset("General::Db")->create({
 204             name => $ontology_identifier
 205         });
 206         my $new_db_id = $db_rs->db_id();
 207
 208         my $dbxref_rs = $schema->resultset("General::Dbxref")->create({
 209             db_id => $new_db_id,
 210             accession => "0000000"
 211         });
 212         my $new_dbxref_id = $dbxref_rs->dbxref_id();
 213
 214         my $cvterm_rs = $schema->resultset("Cv::Cvterm")->create({
 215             name => $ontology_name,
 216             definition => $ontology_description,
 217             dbxref_id => $new_dbxref_id,
 218             cv_id => $new_cv_id
 219         });
 220
 221         return {
 222             success => 1,
 223             new_term => [$cvterm_rs->cvterm_id(), $cvterm_rs->name()]
 224         };
 225     };
 226
 227     try {
 228         $schema->txn_do($coderef);
 229     } catch {
 230         return {
 231             error => $@
 232         };
 233     };
 234 }
 235
 236 sub store_observation_variable_trait_method_scale {
 237     my $self = shift;
 238     my $selected_observation_variable_db_id = shift;
 239     my $new_observation_variable_name = shift;
 240     my $new_observation_variable_definition = shift;
 241     my $selected_trait_db_id = shift;
 242     my $selected_trait_cvterm_id = shift;
 243     my $new_trait_name = shift;
 244     my $new_trait_definition = shift;
 245     my $selected_method_db_id = shift;
 246     my $selected_method_cvterm_id = shift;
 247     my $new_method_name = shift;
 248     my $new_method_definition = shift;
 249     my $selected_scale_db_id = shift;
 250     my $selected_scale_cvterm_id = shift;
 251     my $new_scale_name = shift;
 252     my $new_scale_definition = shift;
 253     my $new_scale_format = shift;
 254     my $new_scale_minumum = shift;
 255     my $new_scale_maximum = shift;
 256     my $new_scale_default = shift;
 257     my $new_scale_categories = shift;
 258
 259     my $schema = $self->schema();
 260     my $dbh = $schema->storage->dbh;
 261     my $numeric_regex = '^-?[0-9]+([,.][0-9]+)?$';
 262
 263     my $new_observation_variable_cvterm_check = $schema->resultset("Cv::Cvterm")->search({
 264         name => $new_observation_variable_name,
 265     });
 266     if ($new_observation_variable_cvterm_check->count() > 0) {
 267         return { error => "The observation variable $new_observation_variable_name already exists in the database!" };
 268     }
 269
 270     if (!$selected_trait_cvterm_id) {
 271         my $new_trait_cvterm_check = $schema->resultset("Cv::Cvterm")->search({
 272             name => $new_trait_name,
 273         });
 274         if ($new_trait_cvterm_check->count() > 0) {
 275             return { error => "The trait $new_trait_name already exists in the database!" };
 276         }
 277     }
 278
 279     if (!$selected_method_cvterm_id) {
 280         my $new_method_cvterm_check = $schema->resultset("Cv::Cvterm")->search({
 281             name => $new_method_name,
 282         });
 283         if ($new_method_cvterm_check->count() > 0) {
 284             return { error => "The method $new_method_name already exists in the database!" };
 285         }
 286     }
 287
 288     if (!$selected_scale_cvterm_id) {
 289         my $new_scale_cvterm_check = $schema->resultset("Cv::Cvterm")->search({
 290             name => $new_scale_name,
 291         });
 292         if ($new_scale_cvterm_check->count() > 0) {
 293             return { error => "The scale $new_scale_name already exists in the database!" };
 294         }
 295     }
 296
 297     my $coderef = sub {
 298         my $observation_variable_db_q = "SELECT db.name, dbxref.accession, cv.name, cv.cv_id FROM dbxref JOIN db USING(db_id) JOIN cvterm USING(dbxref_id) JOIN cv USING(cv_id) WHERE db_id=$selected_observation_variable_db_id AND dbxref.accession ~ '$numeric_regex' ORDER BY dbxref.accession::int DESC LIMIT 1;";
 299         my $observation_variable_db_sth = $dbh->prepare($observation_variable_db_q);
 300         $observation_variable_db_sth->execute();
 301         my ($observation_variable_db_name, $observation_variable_last_accession, $observation_variable_cv_name, $observation_variable_cv_id) = $observation_variable_db_sth->fetchrow_array();
 302         my $observation_variable_new_accession = sprintf("%07d", $observation_variable_last_accession + 1);
 303
 304         my $parent_observation_variable_cvterm_q = "SELECT cvterm.cvterm_id, cvterm.name FROM dbxref JOIN cvterm USING(dbxref_id) JOIN cv USING(cv_id) WHERE db_id=$selected_observation_variable_db_id AND dbxref.accession ~ '$numeric_regex' ORDER BY dbxref.accession::int ASC LIMIT 1;";
 305         my $parent_observation_variable_cvterm_sth = $dbh->prepare($parent_observation_variable_cvterm_q);
 306         $parent_observation_variable_cvterm_sth->execute();
 307         my ($parent_observation_variable_cvterm_id, $parent_observation_variable_cvterm_name) = $parent_observation_variable_cvterm_sth->fetchrow_array();
 308
 309         my $new_term_observation_variable_dbxref = $schema->resultset("General::Dbxref")->create({
 310             db_id => $selected_observation_variable_db_id,
 311             accession => $observation_variable_new_accession
 312         });
 313
 314         my $new_observation_variable_cvterm = $schema->resultset("Cv::Cvterm")->create({
 315             cv_id => $observation_variable_cv_id,
 316             name => $new_observation_variable_name,
 317             definition => $new_observation_variable_definition,
 318             dbxref_id => $new_term_observation_variable_dbxref->dbxref_id()
 319         });
 320
 321         my $is_a_relationship = $schema->resultset("Cv::Cvterm")->search({ name => 'is_a' })->first();
 322         my $contains_relationship = $schema->resultset("Cv::Cvterm")->search({ name => 'contains' })->first();
 323         my $variable_relationship = $schema->resultset("Cv::Cvterm")->search({ name => 'VARIABLE_OF' })->first();
 324
 325         my $variable_rel = $schema->resultset('Cv::CvtermRelationship')->create({
 326             subject_id => $new_observation_variable_cvterm->cvterm_id(),
 327             object_id  => $parent_observation_variable_cvterm_id,
 328             type_id    => $variable_relationship->cvterm_id()
 329         });
 330
 331         if (!$selected_trait_cvterm_id) {
 332
 333             my $trait_db_q = "SELECT db.name, dbxref.accession, cv.name, cv.cv_id FROM dbxref JOIN db USING(db_id) JOIN cvterm USING(dbxref_id) JOIN cv USING(cv_id) WHERE db_id=$selected_trait_db_id AND dbxref.accession ~ '$numeric_regex' ORDER BY dbxref.accession::int DESC LIMIT 1;";
 334             my $trait_db_sth = $dbh->prepare($trait_db_q);
 335             $trait_db_sth->execute();
 336             my ($trait_db_name, $trait_last_accession, $trait_cv_name, $trait_cv_id) = $trait_db_sth->fetchrow_array();
 337             my $trait_new_accession = sprintf("%07d", $trait_last_accession + 1);
 338
 339             my $parent_trait_cvterm_q = "SELECT cvterm.cvterm_id, cvterm.name FROM dbxref JOIN cvterm USING(dbxref_id) JOIN cv USING(cv_id) WHERE db_id=$selected_trait_db_id AND dbxref.accession ~ '$numeric_regex' ORDER BY dbxref.accession::int ASC LIMIT 1;";
 340             my $parent_trait_cvterm_sth = $dbh->prepare($parent_trait_cvterm_q);
 341             $parent_trait_cvterm_sth->execute();
 342             my ($parent_trait_cvterm_id, $parent_trait_cvterm_name) = $parent_trait_cvterm_sth->fetchrow_array();
 343
 344             my $new_term_trait_dbxref = $schema->resultset("General::Dbxref")->create({
 345                 db_id => $selected_trait_db_id,
 346                 accession => $trait_new_accession
 347             });
 348
 349             my $new_trait_cvterm = $schema->resultset("Cv::Cvterm")->create({
 350                 cv_id => $trait_cv_id,
 351                 name => $new_trait_name,
 352                 definition => $new_trait_definition,
 353                 dbxref_id => $new_term_trait_dbxref->dbxref_id()
 354             });
 355             $selected_trait_cvterm_id = $new_trait_cvterm->cvterm_id();
 356
 357             my $trait_rel = $schema->resultset('Cv::CvtermRelationship')->create({
 358                 subject_id => $new_trait_cvterm->cvterm_id(),
 359                 object_id  => $parent_trait_cvterm_id,
 360                 type_id    => $is_a_relationship->cvterm_id()
 361             });
 362         }
 363
 364         my $observation_variable_to_trait_contains_rel = $schema->resultset('Cv::CvtermRelationship')->create({
 365             subject_id => $selected_trait_cvterm_id,
 366             object_id  => $new_observation_variable_cvterm->cvterm_id(),
 367             type_id    => $contains_relationship->cvterm_id()
 368         });
 369
 370         if (!$selected_method_cvterm_id) {
 371             my $method_db_q = "SELECT db.name, dbxref.accession, cv.name, cv.cv_id FROM dbxref JOIN db USING(db_id) JOIN cvterm USING(dbxref_id) JOIN cv USING(cv_id) WHERE db_id=$selected_method_db_id AND dbxref.accession ~ '$numeric_regex' ORDER BY dbxref.accession::int DESC LIMIT 1;";
 372             my $method_db_sth = $dbh->prepare($method_db_q);
 373             $method_db_sth->execute();
 374             my ($method_db_name, $method_last_accession, $method_cv_name, $method_cv_id) = $method_db_sth->fetchrow_array();
 375             my $method_new_accession = sprintf("%07d", $method_last_accession + 1);
 376
 377             my $parent_method_cvterm_q = "SELECT cvterm.cvterm_id, cvterm.name FROM dbxref JOIN cvterm USING(dbxref_id) JOIN cv USING(cv_id) WHERE db_id=$selected_method_db_id AND dbxref.accession ~ '$numeric_regex' ORDER BY dbxref.accession::int ASC LIMIT 1;";
 378             my $parent_method_cvterm_sth = $dbh->prepare($parent_method_cvterm_q);
 379             $parent_method_cvterm_sth->execute();
 380             my ($parent_method_cvterm_id, $parent_method_cvterm_name) = $parent_method_cvterm_sth->fetchrow_array();
 381
 382             my $new_term_method_dbxref = $schema->resultset("General::Dbxref")->create({
 383                 db_id => $selected_method_db_id,
 384                 accession => $method_new_accession
 385             });
 386
 387             my $new_method_cvterm = $schema->resultset("Cv::Cvterm")->create({
 388                 cv_id => $method_cv_id,
 389                 name => $new_method_name,
 390                 definition => $new_method_definition,
 391                 dbxref_id => $new_term_method_dbxref->dbxref_id()
 392             });
 393             $selected_method_cvterm_id = $new_method_cvterm->cvterm_id();
 394
 395             my $method_rel = $schema->resultset('Cv::CvtermRelationship')->create({
 396                 subject_id => $new_method_cvterm->cvterm_id(),
 397                 object_id  => $parent_method_cvterm_id,
 398                 type_id    => $is_a_relationship->cvterm_id()
 399             });
 400         }
 401
 402         my $observation_variable_to_method_contains_rel = $schema->resultset('Cv::CvtermRelationship')->create({
 403             subject_id => $selected_method_cvterm_id,
 404             object_id  => $new_observation_variable_cvterm->cvterm_id(),
 405             type_id    => $contains_relationship->cvterm_id()
 406         });
 407
 408         if (!$selected_scale_cvterm_id) {
 409             my $scale_db_q = "SELECT db.name, dbxref.accession, cv.name, cv.cv_id FROM dbxref JOIN db USING(db_id) JOIN cvterm USING(dbxref_id) JOIN cv USING(cv_id) WHERE db_id=$selected_scale_db_id AND dbxref.accession ~ '$numeric_regex' ORDER BY dbxref.accession::int DESC LIMIT 1;";
 410             my $scale_db_sth = $dbh->prepare($scale_db_q);
 411             $scale_db_sth->execute();
 412             my ($scale_db_name, $scale_last_accession, $scale_cv_name, $scale_cv_id) = $scale_db_sth->fetchrow_array();
 413             my $scale_new_accession = sprintf("%07d", $scale_last_accession + 1);
 414
 415             my $parent_scale_cvterm_q = "SELECT cvterm.cvterm_id, cvterm.name FROM dbxref JOIN cvterm USING(dbxref_id) JOIN cv USING(cv_id) WHERE db_id=$selected_scale_db_id AND dbxref.accession ~ '$numeric_regex' ORDER BY dbxref.accession::int ASC LIMIT 1;";
 416             my $parent_scale_cvterm_sth = $dbh->prepare($parent_scale_cvterm_q);
 417             $parent_scale_cvterm_sth->execute();
 418             my ($parent_scale_cvterm_id, $parent_scale_cvterm_name) = $parent_scale_cvterm_sth->fetchrow_array();
 419
 420             my $new_term_scale_dbxref = $schema->resultset("General::Dbxref")->create({
 421                 db_id => $selected_scale_db_id,
 422                 accession => $scale_new_accession
 423             });
 424
 425             my $scale_categories_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'trait_categories', 'trait_property')->cvterm_id();
 426             my $scale_default_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'trait_default_value', 'trait_property')->cvterm_id();
 427             my $scale_format_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'trait_format', 'trait_property')->cvterm_id();
 428             my $scale_maximum_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'trait_maximum', 'trait_property')->cvterm_id();
 429             my $scale_minimum_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'trait_minimum', 'trait_property')->cvterm_id();
 430
 431             my @cvtermprops;
 432             if ($new_scale_format) {
 433                 push @cvtermprops, {type_id => $scale_format_cvterm_id, value => $new_scale_format};
 434             }
 435             if ($new_scale_minumum) {
 436                 push @cvtermprops, {type_id => $scale_minimum_cvterm_id, value => $new_scale_minumum};
 437             }
 438             if ($new_scale_maximum) {
 439                 push @cvtermprops, {type_id => $scale_maximum_cvterm_id, value => $new_scale_maximum};
 440             }
 441             if ($new_scale_default) {
 442                 push @cvtermprops, {type_id => $scale_default_cvterm_id, value => $new_scale_default};
 443             }
 444             if ($new_scale_categories) {
 445                 push @cvtermprops, {type_id => $scale_categories_cvterm_id, value => $new_scale_categories};
 446             }
 447
 448             my $new_scale_cvterm = $schema->resultset("Cv::Cvterm")->create({
 449                 cv_id => $scale_cv_id,
 450                 name => $new_scale_name,
 451                 definition => $new_scale_definition,
 452                 dbxref_id => $new_term_scale_dbxref->dbxref_id(),
 453                 cvtermprops => \@cvtermprops
 454             });
 455             $selected_scale_cvterm_id = $new_scale_cvterm->cvterm_id();
 456
 457             my $scale_rel = $schema->resultset('Cv::CvtermRelationship')->create({
 458                 subject_id => $new_scale_cvterm->cvterm_id(),
 459                 object_id  => $parent_scale_cvterm_id,
 460                 type_id    => $is_a_relationship->cvterm_id()
 461             });
 462         }
 463
 464         my $observation_variable_to_scale_contains_rel = $schema->resultset('Cv::CvtermRelationship')->create({
 465             subject_id => $selected_scale_cvterm_id,
 466             object_id  => $new_observation_variable_cvterm->cvterm_id(),
 467             type_id    => $contains_relationship->cvterm_id()
 468         });
 469
 470         return {
 471             success => 1,
 472             new_term => [$new_observation_variable_cvterm->cvterm_id(), $new_observation_variable_cvterm->name()]
 473         };
 474     };
 475
 476     try {
 477         $schema->txn_do($coderef);
 478     } catch {
 479         return {
 480             error => $@
 481         };
 482     };
 483 }
 484
 485 1;