[Biojava-dev] DeleteStyle in BioSQL using mysql

[S. Foote]

Hi Len,

The on delete cascade allows you to delete a row from a table and any other rows in other tables that
are referenced to that row.

Hence, "delete from location using location, seqfeature where
location.seqfeature_id=seqfeature_seqfeature_id
and seqfeature.bioentry_id=?" means:

delete rows from location by finding the relevant rows using the seqfeature table id which is
referenced in the location table having the given bioentry_id

Thus, it automatically determines the list of seqfeature ids for the given bioentry_id that need
deleting and in turn finds all the locations that relate to those features.

So, with mysql < 4.0.0, you will need to find each seqfeature_id and remove it individually.
Luckily, this had popped up before and someone had submitted a hack that does just this.  You could
probably find it in the list archives somewhere, but I just found a copy.  I'll put it below.

Cheers,
Simon


private void _removeSequence(String id)
      throws BioException, IllegalIDException, ChangeVetoException
  {
    Sequence seq = (Sequence) sequencesByName.get(id);
    if (seq != null) {
      seq = null;  // Don't want to be holding the reference ourselves!
      try {
        Thread.sleep(100L);
        System.gc();
      } catch (Exception ex) {
        ex.printStackTrace();
      }
      seq = (Sequence) sequencesByName.get(id);
      if (seq != null) {
        throw new BioException("There are still references to sequence with ID " 
						+ id + " from this database.");
      }
    }

    Connection conn = null;
    try {
      conn = pool.takeConnection();
      conn.setAutoCommit(false);

      int bioentry_id = -1;
      int biosequence_id = -1;
      ArrayList featureIdList = null;

      PreparedStatement get_sequence = 
				conn.prepareStatement("select bioentry.bioentry_id,
biosequence.biosequence_id " +
          "from bioentry, biosequence " +
          "where bioentry.accession = ? and " +
          "      biosequence.bioentry_id = bioentry.bioentry_id");
      get_sequence.setString(1, id);
      ResultSet rs = get_sequence.executeQuery();
      boolean exists;
      if ((exists = rs.next())) {
        bioentry_id = rs.getInt(1);
        biosequence_id = rs.getInt(2);
      }
			System.out.println("Simon: " + bioentry_id);
      get_sequence.close();
      if ( bioentry_id != -1) {
        // Now get all the seqfeature_ids
        PreparedStatement getFeatureId = 
					conn.prepareStatement("select seqfeature_id from seqfeature
where bioentry_id = ?");
        getFeatureId.setInt(1, bioentry_id);
        ResultSet rs1 = getFeatureId.executeQuery();
        while ( rs1.next() ) {
          if ( featureIdList == null ) featureIdList = new ArrayList();
          Integer anInt = new Integer(rs1.getInt(1));
          featureIdList.add(anInt);
        }
        getFeatureId.close();

        PreparedStatement delete_taxa = 
					conn.prepareStatement("delete from bioentry_taxa where
bioentry_id = ?");
        delete_taxa.setInt(1, bioentry_id);
        delete_taxa.executeUpdate();
        delete_taxa.close();

        PreparedStatement delete_reference =
					conn.prepareStatement("delete from bioentry_reference where
bioentry_id = ?");
        delete_reference.setInt(1, bioentry_id);
        delete_reference.executeUpdate();
        delete_reference.close();

        PreparedStatement delete_comment = 
					conn.prepareStatement("delete from comment where bioentry_id =
?");

        // REMOVED -- Oracle does not like the name "Comment" for a
				// table, so re-name this table to Biocomment
        PreparedStatement delete_comment =
					conn.prepareStatement("delete from comment where bioentry_id =
?");
        delete_comment.setInt(1, bioentry_id);
        delete_comment.executeUpdate();
        delete_comment.close();

        PreparedStatement delete_qv = 
					conn.prepareStatement("delete from bioentry_qualifier_value
where bioentry_id = ?");
        delete_qv.setInt(1, bioentry_id);
        delete_qv.executeUpdate();
        delete_qv.close();

        if ( featureIdList != null ) {
          Iterator iter = featureIdList.iterator();
          while ( iter.hasNext() ) {
            Integer anInt = (Integer) iter.next();
            int sf_id = anInt.intValue();
            PreparedStatement delete_locs =
							conn.prepareStatement("delete from
seqfeature_location where seqfeature_id = ? ");
            delete_locs.setInt(1, sf_id);
            delete_locs.executeUpdate();
            delete_locs.close();

            PreparedStatement delete_fqv = 
							conn.prepareStatement("delete from
seqfeature_qualifier_value where seqfeature_id = ?");
            delete_fqv.setInt(1, sf_id);
            delete_fqv.executeUpdate();
            delete_fqv.close();

            PreparedStatement delete_rel = 
							conn.prepareStatement("delete from
seqfeature_relationship where parent_seqfeature_id = ? ");
            delete_rel.setInt(1, sf_id);
            delete_rel.executeUpdate();
            delete_rel.close();
          }
        }
        PreparedStatement delete_features =
					conn.prepareStatement("delete from seqfeature " +
            " where bioentry_id = ?");
        delete_features.setInt(1, bioentry_id);
        delete_features.executeUpdate();
        delete_features.close();

        if ( biosequence_id != -1 ) {
          PreparedStatement delete_biosequence =
						conn.prepareStatement("delete from biosequence where
biosequence_id = ?");
          delete_biosequence.setInt(1, biosequence_id);
          delete_biosequence.executeUpdate();
          delete_biosequence.close();
        }
        PreparedStatement delete_entry = 
					conn.prepareStatement("delete from bioentry where bioentry_id =
?");
        delete_entry.setInt(1, bioentry_id);
        delete_entry.executeUpdate();
        delete_entry.close();
      }
      // }  CHECK THIS -- WHY COMMENTED OUT ???
      // get_sequence.close();

      conn.commit();
      pool.putConnection(conn);

      if (!exists) {
        throw new IllegalIDException("Sequence " + id + " didn't exist");
      }
    } catch (SQLException ex) {
      System.err.println( ex.toString());
      boolean rolledback = false;
      if (conn != null) {
        try {
          conn.rollback();
          rolledback = true;
          } catch (SQLException ex2) {}
      }
      throw new BioException(ex, "Error removing from BioSQL tables" 
					+ (rolledback ? " (rolled back successfully)" : ""));
    }
  }


According to Len Trigg:
> Simon Foote wrote:
> > The "using" syntax is valid as it pertains to cascading deletes. 
> > The deletes function correctly with MySQL version 4.0.14 using InnoDb 
> > tables in my test cases.
> 
> I put in the version checking, and both mysql and hsqldb fail on the
> deletion statement with (hsqldb):
> 
> Caused by: java.sql.SQLException: Column not found: SEQFEATURE_ID in
> statement [delete from location  where location.seqfeature_id =
> seqfeature.seqfeature_id and    seqfeature.bioentry_id = 19]
> 
> or (mysql 3.23.56):
> 
> Caused by: java.sql.SQLException: General error,  message from server:
> "Unknown table 'seqfeature' in where clause"
>  
> 
> Does this mean that the DELETE_GENERIC implementation is going to have
> to lookup the relevant seqfeature_id's and remove each location
> separately?  My SQL knowledge is pretty basic, so I'm learning as I go
> along... (A quick two line explanation of what the "on delete cascade"
> stuff means also wouldn't go astray :-)).
> 
> 
> Cheers,
> Len.
>