Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance. Project price only 1 $
You can buy this project and download/modify it how often you want.
/* -*- c-basic-offset: 4; indent-tabs-mode: nil -*- */
/*
* BioJava development code
*
* This code may be freely distributed and modified under the
* terms of the GNU Lesser General Public Licence. This should
* be distributed with the code. If you do not have a copy,
* see:
*
* http://www.gnu.org/copyleft/lesser.html
*
* Copyright for this code is held jointly by the individual
* authors. These should be listed in @author doc comments.
*
* For more information on the BioJava project and its aims,
* or to join the biojava-l mailing list, visit the home page
* at:
*
* http://www.biojava.org/
*
*/
package org.biojava.bio.seq.db.biosql;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.biojava.bio.BioError;
import org.biojava.bio.BioException;
import org.biojava.bio.BioRuntimeException;
import org.biojava.bio.seq.ComponentFeature;
import org.biojava.bio.seq.Feature;
import org.biojava.bio.seq.FeatureHolder;
import org.biojava.bio.seq.StrandedFeature;
import org.biojava.bio.seq.io.SeqIOListener;
import org.biojava.bio.symbol.FuzzyLocation;
import org.biojava.bio.symbol.Location;
import org.biojava.bio.symbol.LocationTools;
import org.biojava.bio.symbol.PointLocation;
import org.biojava.bio.symbol.RangeLocation;
import org.biojava.utils.ChangeVetoException;
/**
* Behind-the-scenes adaptor to the features sub-schema of BioSQL.
*
* @author Thomas Down
* @author Simon Foote
* @author Len Trigg
* @author Richard Holland
* @deprecated Use hibernate and org.biojavax.bio.db.*
* @since 1.3
*/
class FeaturesSQL {
private BioSQLSequenceDB seqDB;
private HashMap rankType;
FeaturesSQL(BioSQLSequenceDB seqDB) {
this.seqDB = seqDB;
}
//
// Feature retrieval
//
/**
* Get some features out of BioSQL and fire SeqIO events to the specified listener.
* Currently 4 modes:
*
*
*
Get all features on a bioentry (including children)
*
Get all top-level features in a region
*
Get all children of a specified parent
*
Get a particular feature by ID
*
*
* This is rather ugly, but it's well hidden. Not sure what the options would be for
* a cleaner API.
*/
public void retrieveFeatures(int bioentry_id,
SeqIOListener listener,
Location overlappingRegion,
int immediateChildrenOfParent,
int featureID)
throws SQLException, BioException
{
Connection conn = seqDB.getDataSource().getConnection();
Map fmap = new HashMap();
Map qmap = new HashMap();
Map lmap = new HashMap();
PreparedStatement get_features = null;
if (overlappingRegion == null && immediateChildrenOfParent < 0 && featureID < 0) {
get_features = conn.prepareStatement(
"select seqfeature.seqfeature_id, " +
" seqfeature.type_term_id, " +
" seqfeature.source_term_id " +
" from seqfeature " +
" where seqfeature.bioentry_id = ?"
);
get_features.setInt(1, bioentry_id);
} else if (overlappingRegion != null) {
get_features = conn.prepareStatement(
"select seqfeature.seqfeature_id, " +
" seqfeature.type_term_id, " +
" seqfeature.source_term_id " +
" from location, seqfeature " +
" where seqfeature.bioentry_id = ? and " +
" location.seqfeature_id = seqfeature.seqfeature_id and " +
" location.end_pos >= ? and " +
" location.start_pos <= ? " +
" group by seqfeature.seqfeature_id, seqfeature.type_term_id, seqfeature.source_term_id"
);
get_features.setInt(1, bioentry_id);
get_features.setInt(2, overlappingRegion.getMin());
get_features.setInt(3, overlappingRegion.getMax());
} else if (immediateChildrenOfParent >= 0) {
get_features = conn.prepareStatement(
"select seqfeature.seqfeature_id, " +
" seqfeature.type_term_id, " +
" seqfeature.source_term_id " +
" from seqfeature, seqfeature_relationship " +
" where seqfeature.seqfeature_id = seqfeature_relationship.subject_seqfeature_id and " +
" seqfeature_relationship.object_seqfeature_id = ?"
);
get_features.setInt(1, immediateChildrenOfParent);
} else if (featureID >= 0) {
get_features = conn.prepareStatement(
"select seqfeature.seqfeature_id, " +
" seqfeature.type_term_id, " +
" seqfeature.source_term_id, " +
" seqfeature.bioentry_id " +
" from seqfeature " +
" where seqfeature.seqfeature_id = ?"
);
get_features.setInt(1, featureID);
} else {
conn.close();
throw new BioException("I'm afraid you can't do that!");
}
ResultSet rs = get_features.executeQuery();
while (rs.next()) {
int feature_id = rs.getInt(1);
StrandedFeature.Template templ = new StrandedFeature.Template();
templ.type = seqDB.getOntologyTerm(rs.getInt(2));
templ.source = seqDB.getOntologyTerm(rs.getInt(3));
templ.annotation = new BioSQLFeatureAnnotation(seqDB, feature_id);
// templ.annotation = new SmallAnnotation();
fmap.put(new Integer(feature_id), templ);
if (featureID >= 0 && bioentry_id < 0) {
bioentry_id = rs.getInt(4);
listener.addSequenceProperty("_biosql_internal.bioentry_id", new Integer(bioentry_id));
}
}
rs.close();
get_features.close();
// Fetch annotations (worth a try!)
PreparedStatement get_annotations = null;
if (overlappingRegion == null && immediateChildrenOfParent < 0 && featureID < 0) {
get_annotations = conn.prepareStatement(
"select seqfeature_qualifier_value.seqfeature_id, " +
" seqfeature_qualifier_value.term_id, " +
" seqfeature_qualifier_value.value " +
" from seqfeature, seqfeature_qualifier_value " +
" where seqfeature_qualifier_value.seqfeature_id = seqfeature.seqfeature_id and " +
" seqfeature.bioentry_id = ?"
);
get_annotations.setInt(1, bioentry_id);
} else if (overlappingRegion != null) {
get_annotations = conn.prepareStatement(
"select seqfeature_qualifier_value.seqfeature_id, " +
" seqfeature_qualifier_value.term_id, " +
" seqfeature_qualifier_value.value " +
" from seqfeature, seqfeature_qualifier_value, location " +
" where seqfeature_qualifier_value.seqfeature_id = seqfeature.seqfeature_id and " +
" seqfeature.bioentry_id = ? and" +
" location.seqfeature_id = seqfeature.seqfeature_id and " +
" location.end_pos >= ? and " +
" location.start_pos <= ? " +
" group by seqfeature_qualifier_value.seqfeature_id, " +
" seqfeature_qualifier_value.term_id, " +
" seqfeature_qualifier_value.value"
);
get_annotations.setInt(1, bioentry_id);
get_annotations.setInt(2, overlappingRegion.getMin());
get_annotations.setInt(3, overlappingRegion.getMax());
} else if (immediateChildrenOfParent >= 0) {
get_annotations = conn.prepareStatement(
"select seqfeature_qualifier_value.seqfeature_id, " +
" seqfeature_qualifier_value.term_id, " +
" seqfeature_qualifier_value.value " +
" from seqfeature_qualifier_value, seqfeature_relationship " +
" where seqfeature_qualifier_value.seqfeature_id = seqfeature_relationship.subject_seqfeature_id and " +
" seqfeature_relationship.object_seqfeature_id = ?"
);
get_annotations.setInt(1, immediateChildrenOfParent);
} else if (featureID >= 0) {
get_annotations = conn.prepareStatement(
"select seqfeature_qualifier_value.seqfeature_id, " +
" seqfeature_qualifier_value.term_id, " +
" seqfeature_qualifier_value.value " +
" from seqfeature_qualifier_value " +
" where seqfeature_qualifier_value.seqfeature_id = ?"
);
get_annotations.setInt(1, featureID);
}
rs = get_annotations.executeQuery();
while (rs.next()) {
Integer fid = new Integer(rs.getInt(1));
String key = seqDB.getOntologyTerm(rs.getInt(2));
String value = rs.getString(3).trim();
Feature.Template templ = (Feature.Template) fmap.get(fid);
try {
((BioSQLFeatureAnnotation) templ.annotation).initProperty(key, value);
} catch (ChangeVetoException ex) {
try {conn.close();} catch (SQLException ex3) {}
throw new BioError("Couldn't modify hidden FeatureHolder");
}
}
rs.close();
get_annotations.close();
// Fetch those crappy location qualifiers first...
/*
if (seqDB.isLocationQualifierSupported()) {
PreparedStatement get_location_crap = conn.prepareStatement(
"select location_qualifier_value.location_id, " +
" seqfeature_qualifier.qualifier_name, " +
" location_qualifier_value.qualifier_value, " +
" location_qualifier_value.qualifier_int_value " +
" from location_qualifier_value, location, seqfeature, seqfeature_qualifier " +
" where seqfeature.bioentry_id = ? and " +
" location.seqfeature_id = seqfeature.seqfeature_id and " +
" location_qualifier_value.location_id = location.location_id and " +
" seqfeature_qualifier.seqfeature_qualifier_id = location_qualifier_value.seqfeature_qualifier_id");
get_location_crap.setInt(1, bioentry_id);
rs = get_location_crap.executeQuery();
while (rs.next()) {
LocationQualifierMemento lqm = new LocationQualifierMemento();
int location_id = rs.getInt(1);
lqm.qualifier_name = rs.getString(2).trim(); // HACK due to stupid schema change
lqm.qualifier_value = rs.getString(3).trim();
lqm.qualifier_int = rs.getInt(4);
Integer location_id_boxed = new Integer(location_id);
List l = (List) qmap.get(location_id_boxed);
if (l == null) {
l = new ArrayList();
qmap.put(location_id_boxed, l);
}
l.add(lqm);
}
}
*/
// Fetch locations
PreparedStatement get_locations = null;
if (overlappingRegion == null && immediateChildrenOfParent < 0 && featureID < 0) {
get_locations = conn.prepareStatement(
"select location.location_id, " +
" location.seqfeature_id, " +
" location.start_pos, " +
" location.end_pos, " +
" location.strand " +
" from seqfeature, location " +
" where location.seqfeature_id = seqfeature.seqfeature_id and " +
" seqfeature.bioentry_id = ?"
);
get_locations.setInt(1, bioentry_id);
} else if (overlappingRegion != null) {
get_locations = conn.prepareStatement(
"select location.location_id, " +
" location.seqfeature_id, " +
" location.start_pos, " +
" location.end_pos, " +
" location.strand " +
" from location, location as sfl2, seqfeature " +
" where location.seqfeature_id = seqfeature.seqfeature_id and " +
" seqfeature.bioentry_id = ? and " +
" sfl2.seqfeature_id = seqfeature.seqfeature_id and " +
" sfl2.end_pos >= ? and " +
" sfl2.start_pos <= ? " +
" group by location.location_id, " +
" location.seqfeature_id, " +
" location.start_pos, " +
" location.end_pos, " +
" location.strand"
);
get_locations.setInt(1, bioentry_id);
get_locations.setInt(2, overlappingRegion.getMin());
get_locations.setInt(3, overlappingRegion.getMax());
} else if (immediateChildrenOfParent >= 0) {
get_locations = conn.prepareStatement(
"select location.location_id, " +
" location.seqfeature_id, " +
" location.start_pos, " +
" location.end_pos, " +
" location.strand " +
" from location, seqfeature_relationship " +
" where location.seqfeature_id = seqfeature_relationship.subject_seqfeature_id and " +
" seqfeature_relationship.object_seqfeature_id = ?"
);
get_locations.setInt(1, immediateChildrenOfParent);
} else if (featureID >= 0) {
get_locations = conn.prepareStatement(
"select location.location_id, " +
" location.seqfeature_id, " +
" location.start_pos, " +
" location.end_pos, " +
" location.strand " +
" from location " +
" where location.seqfeature_id = ?");
get_locations.setInt(1, featureID);
}
rs = get_locations.executeQuery();
while (rs.next()) {
Integer lid = new Integer(rs.getInt(1));
Integer fid = new Integer(rs.getInt(2));
int start = rs.getInt(3);
int end = rs.getInt(4);
int istrand = rs.getInt(5);
StrandedFeature.Strand strand = StrandedFeature.UNKNOWN;
if (istrand > 0) {
strand = StrandedFeature.POSITIVE;
} else if (istrand < 0) {
strand = StrandedFeature.NEGATIVE;
}
StrandedFeature.Template templ = (StrandedFeature.Template) fmap.get(fid);
if (templ.strand != null && templ.strand != strand) {
// throw new BioRuntimeException("Feature strands don't match");
// Really don't want to support these at all, but...
templ.strand = StrandedFeature.UNKNOWN;
} else {
templ.strand = strand;
}
Location bloc;
if (start == end) {
bloc = new PointLocation(start);
} else {
bloc = new RangeLocation(start, end);
}
List locationCrap = (List) qmap.get(lid);
if (locationCrap != null) {
int min_start = -1;
int min_end = -1;
int max_start = -1;
int max_end = -1;
boolean unknown_start = false;
boolean unknown_end = false;
boolean unbounded_start = false;
boolean unbounded_end = false;
boolean isFuzzy = false;
for (Iterator i = locationCrap.iterator(); i.hasNext(); ) {
LocationQualifierMemento lqm = (LocationQualifierMemento) i.next();
String qname = lqm.qualifier_name;
if ("min_start".equals(qname)) {
min_start = lqm.qualifier_int;
isFuzzy = true;
} else if ("max_start".equals(qname)) {
max_start = lqm.qualifier_int;
isFuzzy = true;
} else if ("min_end".equals(qname)) {
min_end = lqm.qualifier_int;
isFuzzy = true;
} else if ("max_end".equals(qname)) {
max_end = lqm.qualifier_int;
isFuzzy = true;
} else if ("start_pos_type".equals(qname)) {
if ("BEFORE".equalsIgnoreCase(lqm.qualifier_value)) {
unbounded_start = true;
isFuzzy = true;
}
} if ("end_pos_type".equals(qname)) {
if ("AFTER".equalsIgnoreCase(lqm.qualifier_value)) {
unbounded_end = true;
isFuzzy = true;
}
}
}
if (isFuzzy) {
if (unknown_start) {
min_start = Integer.MIN_VALUE;
max_start = Integer.MAX_VALUE;
}
if (unbounded_start) {
min_start = Integer.MIN_VALUE;
}
if (unknown_end) {
min_end = Integer.MIN_VALUE;
max_end = Integer.MAX_VALUE;
}
if (unbounded_end) {
max_end = Integer.MAX_VALUE;
}
if (min_start == -1) {
min_start = bloc.getMin();
}
if (max_start == -1) {
max_start = bloc.getMin();
}
if (min_end == -1) {
min_end = bloc.getMax();
}
if (max_end == -1) {
max_end = bloc.getMax();
}
bloc = new FuzzyLocation(min_start,
max_end,
max_start,
min_end,
FuzzyLocation.RESOLVE_INNER);
}
}
List ll = (List) lmap.get(fid);
if (ll == null) {
ll = new ArrayList();
lmap.put(fid, ll);
}
ll.add(bloc);
}
rs.close();
get_locations.close();
// Bind location information to features
for (Iterator i = fmap.entrySet().iterator(); i.hasNext(); ) {
Map.Entry me = (Map.Entry) i.next();
Integer fid = (Integer) me.getKey();
StrandedFeature.Template templ = (StrandedFeature.Template) me.getValue();
List ll = (List) lmap.get(fid);
if (ll == null) {
templ.location = Location.empty;
// conn.close();
//throw new BioRuntimeException("BioSQL SeqFeature doesn't have any associated location spans. seqfeature_id=" + fid);
} else {
Location loc = null;
if (ll.size() == 1) {
loc = (Location) ll.get(0);
} else {
loc = LocationTools.union(ll);
}
templ.location = loc;
}
}
// Check hierarchy
Set toplevelFeatures = new HashSet(fmap.keySet());
Map featureHierarchy = new HashMap();
int specifiedParent = -1;
if (immediateChildrenOfParent < 0 && featureID < 0) {
PreparedStatement get_hierarchy;
if (overlappingRegion == null) {
get_hierarchy = conn.prepareStatement(
"select object_seqfeature_id, subject_seqfeature_id " +
" from seqfeature_relationship, seqfeature " +
" where object_seqfeature_id = seqfeature.seqfeature_id and " +
" seqfeature.bioentry_id = ?"
);
get_hierarchy.setInt(1, bioentry_id);
} else {
get_hierarchy = conn.prepareStatement(
"select object_seqfeature_id, subject_seqfeature_id " +
" from seqfeature_relationship, seqfeature, location " +
" where object_seqfeature_id = seqfeature.seqfeature_id and " +
" seqfeature.bioentry_id = ? and " +
" location.seqfeature_id = object_seqfeature_id and " +
" location.end_pos >= ? and " +
" location.start_pos <= ? " +
" group by object_seqfeature_id, subject_seqfeature_id"
);
get_hierarchy.setInt(1, bioentry_id);
get_hierarchy.setInt(2, overlappingRegion.getMin());
get_hierarchy.setInt(3, overlappingRegion.getMax());
}
rs = get_hierarchy.executeQuery();
while (rs.next()) {
Integer parent = new Integer(rs.getInt(1));
Integer child = new Integer(rs.getInt(2));
toplevelFeatures.remove(child);
List cl = (List) featureHierarchy.get(parent);
if (cl == null) {
cl = new ArrayList();
featureHierarchy.put(parent, cl);
}
cl.add(child);
}
rs.close();
get_hierarchy.close();
} else if (immediateChildrenOfParent >= 0) {
specifiedParent = immediateChildrenOfParent;
} else if (featureID >= 0) {
PreparedStatement discover_parent = conn.prepareStatement(
"select object_seqfeature_id " +
" from seqfeature_relationship " +
" where object_seqfeature_id = ?"
);
discover_parent.setInt(1, featureID);
rs = discover_parent.executeQuery();
if (rs.next()) {
specifiedParent = rs.getInt(1);
}
rs.close();
discover_parent.close();
}
conn.close();
conn = null;
for (Iterator tlfi = toplevelFeatures.iterator(); tlfi.hasNext(); ) {
Integer fid = (Integer) tlfi.next();
Feature.Template templ = (Feature.Template) fmap.get(fid);
boolean childrenFetched = (immediateChildrenOfParent < 0);
if (overlappingRegion != null) {
if (!overlappingRegion.contains(templ.location)) {
childrenFetched = false;
}
}
fireFeatureTree(listener,
fid,
fmap,
featureHierarchy,
childrenFetched,
new Integer(specifiedParent)
);
}
}
private void fireFeatureTree(SeqIOListener listener,
Integer fid,
Map fmap,
Map featureHierarchy,
boolean childrenFetched,
Integer pid)
throws BioException
{
Feature.Template templ = (Feature.Template) fmap.get(fid);
listener.startFeature(templ);
listener.addFeatureProperty("_biosql_internal.feature_id", fid);
listener.addFeatureProperty("_biosql_internal.parent_id", pid);
if (childrenFetched) {
List children = (List) featureHierarchy.get(fid);
if (children == null) {
listener.addFeatureProperty("_biosql_internal.hint_childfree", Boolean.TRUE);
} else {
for (Iterator ci = children.iterator(); ci.hasNext(); ) {
Integer childID = (Integer) ci.next();
fireFeatureTree(listener, childID, fmap, featureHierarchy, childrenFetched, fid);
}
}
}
listener.endFeature();
}
private static class LocationQualifierMemento {
public String qualifier_name;
public String qualifier_value;
public int qualifier_int;
}
//
// Feature live updates
//
void setFeatureType(int feature_id, String type)
throws SQLException
{
Connection conn = null;
try {
conn = seqDB.getDataSource().getConnection();
conn.setAutoCommit(false);
int seqfeature_key = seqDB.intern_ontology_term(conn, type);
PreparedStatement update_key = conn.prepareStatement("update seqfeature " +
" set type_term_id = ? " +
" where seqfeature_id = ?");
update_key.setInt(1, seqfeature_key);
update_key.setInt(2, feature_id);
update_key.executeUpdate();
update_key.close();
conn.commit();
conn.close();
} catch (SQLException ex) {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException ex2) {}
try {conn.close();} catch (SQLException ex3) {}
}
throw ex;
}
}
void setFeatureSource(int feature_id, String source)
throws SQLException
{
Connection conn = null;
try {
conn = seqDB.getDataSource().getConnection();
conn.setAutoCommit(false);
int seqfeature_source = seqDB.intern_ontology_term(conn, source);
PreparedStatement update_source = conn.prepareStatement("update seqfeature " +
" set source_term_id = ? " +
" where seqfeature_id = ?");
update_source.setInt(1, seqfeature_source);
update_source.setInt(2, feature_id);
update_source.executeUpdate();
update_source.close();
conn.commit();
conn.close();
} catch (SQLException ex) {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException ex2) {}
try {conn.close();} catch (SQLException ex3) {}
}
throw ex;
}
}
void setFeatureLocation(int feature_id, Location location, StrandedFeature.Strand s)
throws SQLException
{
Connection conn = null;
try {
conn = seqDB.getDataSource().getConnection();
conn.setAutoCommit(false);
PreparedStatement del_oldlocation = conn.prepareStatement(
"delete from location " +
" where seqfeature_id = ?"
);
del_oldlocation.setInt(1, feature_id);
del_oldlocation.executeUpdate();
del_oldlocation.close();
PreparedStatement add_locationspan = conn.prepareStatement(
"insert into location " +
" (seqfeature_id, start_pos, end_pos, strand, rank) " +
"values (?, ?, ?, ?, ?)"
);
int strandNum;
if (s == StrandedFeature.POSITIVE) {
strandNum = 1;
} else if (s== StrandedFeature.NEGATIVE) {
strandNum = -1;
} else {
strandNum = 0;
}
int rank = 0;
for (Iterator i = location.blockIterator(); i.hasNext(); ) {
Location bloc = (Location) i.next();
add_locationspan.setInt(1, feature_id);
add_locationspan.setInt(2, bloc.getMin());
add_locationspan.setInt(3, bloc.getMax());
add_locationspan.setInt(4, strandNum);
add_locationspan.setInt(5, ++rank);
add_locationspan.executeUpdate();
}
add_locationspan.close();
conn.commit();
conn.close();
} catch (SQLException ex) {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException ex2) {}
try {conn.close();} catch (SQLException ex3) {}
}
throw ex;
}
}
//
// Feature persistance
//
void persistFeatures(Connection conn, int bioentry_id, FeatureHolder features)
throws BioException, SQLException
{
persistFeatures(conn, bioentry_id, features, -1);
}
private void persistFeatures(Connection conn, int bioentry_id, FeatureHolder features, int parent)
throws BioException, SQLException
{
// HashMap to track rank for each feature_type
// This allows the unique key constraint in seqfeature to be valid as
// bioentries can have multiple features of same type/source (ie. Genbank CDS)
// This way is faster than just increasing the rank value each time
// Only set Map for parent, otherwise adding children of same type throws
// a duplicate key error
if (parent < 0) {
rankType = new HashMap();
}
for (Iterator fi = features.features(); fi.hasNext(); ) {
Feature f = (Feature) fi.next();
// Get next rank value for feature type
int rank = 0;
String fType = f.getType();
if (rankType.containsKey(fType)) {
rank = ((Integer) rankType.get(fType)).intValue() + 1;
rankType.put(fType, new Integer(rank));
} else {
rankType.put(fType, new Integer(0));
}
if (! (f instanceof ComponentFeature)) {
int id = persistFeature(conn, bioentry_id, f, parent, rank);
if (seqDB.isHierarchySupported()) {
persistFeatures(conn, bioentry_id, f, id);
}
}
}
}
int persistFeature(Connection conn,
int bioentry_id,
Feature f,
int parent_id,
int typeRank)
throws BioException, SQLException
{
int id = -1;
boolean locationWritten = false;
if (seqDB.isSPASupported()) {
if (f.getLocation().isContiguous()) {
Location loc = f.getLocation();
PreparedStatement add_feature = conn.prepareStatement(
"select create_seqfeature_onespan(?, ?, ?, ?, ?, ?)"
);
add_feature.setInt(1, bioentry_id);
add_feature.setString(2, f.getType());
add_feature.setString(3, f.getSource());
add_feature.setInt(4, loc.getMin());
add_feature.setInt(5, loc.getMax());
if (f instanceof StrandedFeature) {
StrandedFeature.Strand s = ((StrandedFeature) f).getStrand();
if (s == StrandedFeature.POSITIVE) {
add_feature.setInt(6, 1);
} else if (s== StrandedFeature.NEGATIVE) {
add_feature.setInt(6, -1);
} else {
add_feature.setInt(6, 0);
}
} else {
add_feature.setInt(6, 0);
}
ResultSet rs = add_feature.executeQuery();
if (rs.next()) {
id = rs.getInt(1);
}
rs.close();
add_feature.close();
locationWritten = true;
} else {
PreparedStatement add_feature = conn.prepareStatement(
"select create_seqfeature(?, ?, ?)"
);
add_feature.setInt(1, bioentry_id);
add_feature.setString(2, f.getType());
add_feature.setString(3, f.getSource());
ResultSet rs = add_feature.executeQuery();
if (rs.next()) {
id = rs.getInt(1);
}
rs.close();
add_feature.close();
}
} else {
int seqfeature_key = seqDB.intern_ontology_term(conn, f.getType());
int seqfeature_source = seqDB.intern_ontology_term(conn, f.getSource());
// Because of unique key constraints on seqfeature
// Need to select the maximum rank value for the bioentry,key,source value
// if rank is set to -1
if (typeRank < 0) {
PreparedStatement select_rank = conn.prepareStatement(
"select max(rank) from seqfeature where bioentry_id=?"
+ " and type_term_id=? and source_term_id=?");
select_rank.setInt(1, bioentry_id);
select_rank.setInt(2, seqfeature_key);
select_rank.setInt(3, seqfeature_source);
ResultSet rs = select_rank.executeQuery();
if (rs.next()) {
typeRank = rs.getInt(1) + 1;
}
rs.close();
select_rank.close();
}
PreparedStatement add_feature = conn.prepareStatement(
"insert into seqfeature "+
" (bioentry_id, type_term_id, source_term_id, rank) " +
"values (?, ?, ?, ?)"
);
add_feature.setInt(1, bioentry_id);
add_feature.setInt(2, seqfeature_key);
add_feature.setInt(3, seqfeature_source);
add_feature.setInt(4, typeRank);
add_feature.executeUpdate();
add_feature.close();
id = seqDB.getDBHelper().getInsertID(conn, "seqfeature", "seqfeature_id");
}
if (!locationWritten) {
PreparedStatement add_locationspan = conn.prepareStatement(
"insert into location " +
" (seqfeature_id, start_pos, end_pos, strand, rank) " +
"values (?, ?, ?, ?, ?)"
);
int strandNum;
if (f instanceof StrandedFeature) {
StrandedFeature.Strand s = ((StrandedFeature) f).getStrand();
if (s == StrandedFeature.POSITIVE) {
strandNum = 1;
} else if (s== StrandedFeature.NEGATIVE) {
strandNum = -1;
} else {
strandNum = 0;
}
} else {
strandNum = 0;
}
int rank = 0;
for (Iterator i = f.getLocation().blockIterator(); i.hasNext(); ) {
Location bloc = (Location) i.next();
add_locationspan.setInt(1, id);
add_locationspan.setInt(2, bloc.getMin());
add_locationspan.setInt(3, bloc.getMax());
add_locationspan.setInt(4, strandNum);
add_locationspan.setInt(5, ++rank);
add_locationspan.executeUpdate();
}
add_locationspan.close();
}
//
// Persist anything in the annotation bundle, as well.
//
for (Iterator ai = f.getAnnotation().asMap().entrySet().iterator(); ai.hasNext(); ) {
Map.Entry akv = (Map.Entry) ai.next();
persistProperty(conn, id, akv.getKey(), akv.getValue(), false);
}
//
// Persist link to parent
//
if (parent_id >= 0) {
PreparedStatement add_hierarchy = conn.prepareStatement(
"insert into seqfeature_relationship "+
" (object_seqfeature_id, subject_seqfeature_id, term_id) " +
"values (?, ?, ?)"
);
add_hierarchy.setInt(1, parent_id);
add_hierarchy.setInt(2, id);
add_hierarchy.setInt(3, seqDB.intern_ontology_term(conn, "contains"));
add_hierarchy.executeUpdate();
add_hierarchy.close();
}
return id;
}
void removeFeature(BioSQLFeature f)
throws ChangeVetoException
{
Connection conn = null;
try {
conn = seqDB.getDataSource().getConnection();
conn.setAutoCommit(false);
removeFeature(conn, f);
conn.commit();
conn.close();
} catch (SQLException ex) {
boolean rolledback = false;
if (conn != null) {
try {
conn.rollback();
rolledback = true;
} catch (SQLException ex2) {}
}
throw new BioRuntimeException("Error removing from BioSQL tables"
+ (rolledback ? " (rolled back successfully)" : ""), ex);
}
}
private void removeFeature(Connection conn, BioSQLFeature f)
throws SQLException, ChangeVetoException
{
Iterator children = ((FeatureHolder) f).features();
while (children.hasNext()) {
Feature f2 = (Feature) children.next();
if (f2 instanceof BioSQLFeature) {
removeFeature(conn, (BioSQLFeature) f2);
}
}
int feature_id = f._getInternalID();
PreparedStatement delete_locs = conn.prepareStatement("delete from location " +
" where location.seqfeature_id = ?");
delete_locs.setInt(1, feature_id);
delete_locs.executeUpdate();
delete_locs.close();
PreparedStatement delete_fqv = conn.prepareStatement("delete from seqfeature_qualifier_value " +
" where seqfeature_qualifier_value.seqfeature_id = ?");
delete_fqv.setInt(1, feature_id);
delete_fqv.executeUpdate();
delete_fqv.close();
PreparedStatement delete_rel = conn.prepareStatement("delete from seqfeature_relationship " +
" where subject_seqfeature_id = ?");
delete_rel.setInt(1, feature_id);
delete_rel.executeUpdate();
delete_rel.close();
PreparedStatement delete_feature = conn.prepareStatement("delete from seqfeature " +
" where seqfeature_id = ?");
delete_feature.setInt(1, feature_id);
delete_feature.executeUpdate();
delete_feature.close();
}
/**
* Persist a property. Nothing is written if value is void
*/
void persistProperty(Connection conn,
int feature_id,
Object key,
Object value,
boolean removeFirst)
throws SQLException
{
String keyString = key.toString();
if (removeFirst) {
int id = seqDB.intern_ontology_term(conn, keyString);
PreparedStatement remove_old_value = conn.prepareStatement("delete from seqfeature_qualifier_value " +
" where seqfeature_id = ? and term_id = ?");
remove_old_value.setInt(1, feature_id);
remove_old_value.setInt(2, id);
remove_old_value.executeUpdate();
remove_old_value.close();
}
if (value != null) {
PreparedStatement insert_new;
if (seqDB.isSPASupported()) {
insert_new = conn.prepareStatement("insert into seqfeature_qualifier_value " +
" (seqfeature_id, term_id, rank, value) " +
"values (?, intern_ontology_term( ? ), ?, ?)");
if (value instanceof Collection) {
int cnt = 0;
for (Iterator i = ((Collection) value).iterator(); i.hasNext(); ) {
insert_new.setInt(1, feature_id);
insert_new.setString(2, keyString);
insert_new.setInt(3, ++cnt);
insert_new.setString(4, i.next().toString());
insert_new.executeUpdate();
}
} else {
insert_new.setInt(1, feature_id);
insert_new.setString(2, keyString);
insert_new.setInt(3, 1);
insert_new.setString(4, value.toString());
insert_new.executeUpdate();
}
insert_new.close();
} else {
insert_new = conn.prepareStatement("insert into seqfeature_qualifier_value " +
" (seqfeature_id, term_id, rank, value) " +
"values (?, ?, ?, ?)");
int sfq = seqDB.intern_ontology_term(conn, keyString);
if (value instanceof Collection) {
int cnt = 0;
for (Iterator i = ((Collection) value).iterator(); i.hasNext(); ) {
insert_new.setInt(1, feature_id);
insert_new.setInt(2, sfq);
insert_new.setInt(3, ++cnt);
insert_new.setString(4, i.next().toString());
insert_new.executeUpdate();
}
} else {
insert_new.setInt(1, feature_id);
insert_new.setInt(2, sfq);
insert_new.setInt(3, 1);
insert_new.setString(4, value.toString());
insert_new.executeUpdate();
}
insert_new.close();
}
}
}
void persistFeature(Feature f, int parent_id, int bioentry_id)
throws BioException
{
Connection conn = null;
try {
conn = seqDB.getDataSource().getConnection();
conn.setAutoCommit(false);
// Set rank to -1, so will get looked up before feature added
int f_id = seqDB.getFeaturesSQL().persistFeature(conn, bioentry_id, f, parent_id, -1);
if (f instanceof BioSQLFeature) {
((BioSQLFeature) f)._setInternalID(f_id);
((BioSQLFeature) f)._setAnnotation(new BioSQLFeatureAnnotation(seqDB, f_id));
}
conn.commit();
conn.close();
} catch (SQLException ex) {
boolean rolledback = false;
if (conn != null) {
try {
conn.rollback();
rolledback = true;
} catch (SQLException ex2) {}
try {conn.close();} catch (SQLException ex3) {}
}
throw new BioException("Error adding to BioSQL tables"
+ (rolledback ? " (rolled back successfully)" : ""), ex);
}
}
}
