scripts/biosql/clean_ontology.pl

   1 #!/usr/bin/perl
   2
   3 # This script will remove specific records in ontology table, as well as in
   4 # term, term_relationship, term_path, term_dbxref, term_synonym, which are
   5 # related to ontology table.
   6 #
   7 # Tested with mysql.
   8 #
   9 # $Id$
  10 #
  11 # Cared for by Juguang Xiao, juguang@tll.org.sg
  12 #
  13 # Copyright Juguang Xiao
  14 #
  15 # You may distribute this script under the same terms as perl itself
  16
  17 #
  18 # Note from the "editor" (Hilmar): 99% of this should not be necessary with
  19 # enabled foreign key constraints. If you want to delete an entire ontology,
  20 # delete the record from the ontology table, and everything else should be
  21 # taken care of by the database server. Likewise, to get rid of an ontology
  22 # term, delete the record from the term table. Dependent records in other
  23 # tables should be deleted for you automatically through cascading deletes
  24 # on the foreign key constraints. (This is one reason why properly supported
  25 # foreign key constraints are extremely useful, not fluff as MySql used to
  26 # claim.)
  27 #
  28 # Send email to biosql-l@open-bio.org if you have questions about this.
  29 #
  30
  31 use strict;
  32 use Getopt::Long;
  33 use Bio::DB::EasyArgv;
  34
  35 my $db = get_biosql_db_from_argv;
  36 my $ontology_name;
  37 GetOptions('ontology_name=s' => \$ontology_name);
  38
  39 my $dbh = $db->dbcontext->dbi->get_connection;
  40
  41 my $sql="SELECT * FROM ontology WHERE name = ?";
  42
  43 my $sth = $dbh->prepare($sql);
  44 $sth->execute($ontology_name);
  45 my $hashref = $sth->fetchrow_hashref;
  46 unless(defined $hashref){
  47     die "Cannot find '$ontology_name' in the database\n";
  48 }
  49 my $ontology_id = $hashref->{ontology_id};
  50 $sth=$dbh->prepare("SELECT term_id FROM term WHERE ontology_id=?");
  51 $sth->execute($ontology_id);
  52 my @term_ids;
  53 while(my $term = $sth->fetch){
  54     push @term_ids, $term->[0];
  55 }
  56
  57 # Now start to delete. The order of deleting should be with the
  58 # consideration of table relationship. Usually the tables which are
  59 # not refered should be removed before the referring tables.
  60 # The order is (term_synonym, term_relationship, term_path, term_dbxref) -
  61 # term - ontology
  62
  63 my $sth_term_synonym=$dbh->prepare("DELETE FROM term_synonym WHERE term_id =?");
  64 my $sth_term_dbxref=$dbh->prepare('DELETE FROM term_dbxref WHERE term_id=?');
  65 foreach my $term_id (@term_ids){
  66     $sth_term_synonym->execute($term_id);
  67     $sth_term_dbxref->execute($term_id);
  68 }
  69
  70 my $sth_term_path=$dbh->prepare('DELETE FROM term_path WHERE ontology_id =?');
  71 my $sth_term_relationship=
  72     $dbh->prepare('DELETE FROM term_relationship WHERE ontology_id =?');
  73 my $sth_term=$dbh->prepare('DELETE FROM term WHERE ontology_id=?');
  74 $sth_term_path->execute($ontology_id);
  75 $sth_term_relationship->execute($ontology_id);
  76 $sth_term->execute($ontology_id);
  77
  78 $sth = $dbh->prepare("DELETE FROM ontology WHERE ontology_id =?");
  79 $sth->execute($ontology_id);
  80
  81
  82