scripts/biosql/del-assocs-sql.pl

   1 # $Id$
   2 #
   3 # This is a closure that may be used as an argument to the --mergeobjs
   4 # option of load-seqdatabase.pl.
   5 #
   6 # This scriptlet will remove all annotation and features associated
   7 # with the old entry using direct SQL queries. It is therefore specific
   8 # to the schema; in fact it is presently specific to the Oracle version
   9 # of the biosql schema. However, it is easily adapted to the mysql/Pg
  10 # versions by adapting the primary key/foreign key names.
  11 #
  12 # The reason to issue direct SQL queries is purely efficiency when
  13 # updating the data content.
  14 #
  15 # Other than that, the way of removing annotation association for a
  16 # found entry is identical to the idea behind freshen-annot.pl.
  17 #
  18 sub {
  19     my ($old,$new,$db) = @_;
  20
  21     # the found object is a persistent object, so we have access to
  22     # its adaptor for caching statements and getting the database handle
  23     my $adp = $old->adaptor();
  24
  25     # remove cluster members if this is a cluster - this is not dependent
  26     # on the members having actually been loaded, so is compatible with
  27     # --flatlookup mode
  28     if($old->isa("Bio::ClusterI")) {
  29         $adp->remove_members($old);
  30     }
  31
  32     # the tables where we delete by simple foreign key matching
  33     my @del_by_fk_tables = ("bioentry_qualifier_value",
  34                             "anncomment",
  35                             "bioentry_reference",
  36                             "bioentry_dbxref");
  37     # add seqfeature only if the entry is a feature holder
  38     push(@del_by_fk_tables, "seqfeature") if $new->isa("Bio::FeatureHolderI");
  39
  40     # delete for each table by foreign key
  41     foreach my $tbl (@del_by_fk_tables) {
  42         # build the sql statement
  43         my $sql = "DELETE FROM $tbl WHERE ent_oid = ?";
  44         if ($tbl eq "bioentry_qualifier_value") {
  45             # if the new entry comes with type already attached, then we
  46             # need to delete the old one but otherwise add a constraint that
  47             # preserves the old type (as its really unlikely to change)
  48             my ($bioentrytype) =
  49                 $new->isa("Bio::AnnotatableI")
  50                 ? $new->annotation->get_Annotations("Bioentry Type Ontology")
  51                 : (undef);
  52             if (!$bioentrytype) {
  53                 $sql .= " AND trm_oid NOT IN "
  54                     . "(SELECT t.Oid FROM Term t, Ontology o "
  55                     . "WHERE t.Ont_Oid = o.Oid "
  56                     . "AND o.name = 'Bioentry Type Ontology')";
  57             }
  58         }
  59         # we use DBI's facility for statement caching here
  60         my $sth = $adp->dbh->prepare_cached($sql);
  61         # and execute with the primary key
  62         if (! $sth->execute($old->primary_key())) {
  63             $old->warn("failed to execute sql statement ($sql): "
  64                        . $sth->errstr);
  65             $sth->finish();
  66         }
  67     }
  68     # done
  69     return $new;
  70 }