/** {@inheritDoc} */
@Override
public int getNumberOfPeptidesForConfidenceLevel(int confidenceLevel, MsfFile msfFile) {
int numberOfPeptides = 0;
counter = 0;
try {
Statement stat = null;
try {
stat = msfFile.getConnection().createStatement();
ResultSet rs = null;
try {
rs =
stat.executeQuery(
"select count(PeptideID) from Peptides where ConfidenceLevel = "
+ confidenceLevel);
numberOfPeptides = rs.getInt(1);
} finally {
if (rs != null) {
rs.close();
}
}
} finally {
if (stat != null) {
stat.close();
}
}
} catch (SQLException sqle) {
logger.error(sqle);
}
counter = numberOfPeptides;
return numberOfPeptides;
}
/** {@inheritDoc} */
@Override
public int returnNumberOfPeptides(MsfFile msfFile) {
int numberOfPeptides = 0;
try {
Statement stat = null;
try {
stat = msfFile.getConnection().createStatement();
ResultSet rs = null;
try {
rs = stat.executeQuery("select count(peptideid) from Peptides");
rs.next();
numberOfPeptides = rs.getInt(1);
} finally {
if (rs != null) {
rs.close();
}
}
} finally {
if (stat != null) {
stat.close();
}
}
} catch (SQLException sqle) {
logger.error(sqle);
}
return numberOfPeptides;
}
/** {@inheritDoc} */
@Override
public List getInformationForPeptide(int peptideID, MsfFile msfFile, boolean fullInfo) {
List peptideInfo = new ArrayList();
try {
Statement stat = null;
try {
stat = msfFile.getConnection().createStatement();
ResultSet rs = null;
try {
if (fullInfo) {
rs =
stat.executeQuery(
"select Peptides.Sequence, SpectrumHeaders.FirstScan,SpectrumHeaders.LastScan,Peptides.Annotations,ProcessingNodes.FriendlyName from Peptides where ConfidenceLevel >= 1 ");
} else {
rs = stat.executeQuery("");
}
while (rs.next()) {}
} finally {
if (rs != null) {
rs.close();
}
}
} finally {
if (stat != null) {
stat.close();
}
}
} catch (SQLException sqle) {
logger.error(sqle);
}
return peptideInfo;
}
/** {@inheritDoc} */
@Override
public List<PeptideLowMem> getPeptidesForAccession(String lProteinAccession, MsfFile msfFile) {
int lProteinID = 0;
String lSequence = null;
try {
Statement stat = null;
try {
stat = msfFile.getConnection().createStatement();
ResultSet rs = null;
try {
rs =
stat.executeQuery(
"select protanno.proteinID,Proteins.Sequence from Proteins, (select ProteinID from ProteinAnnotations where ProteinAnnotations.Description like ('%"
+ lProteinAccession
+ "%')) as protanno where protanno.ProteinID = Proteins.ProteinID");
rs.next();
lProteinID = rs.getInt("protanno.proteinID");
lSequence = rs.getString("Proteins.Sequence");
} finally {
if (rs != null) {
rs.close();
}
}
} finally {
if (stat != null) {
stat.close();
}
}
} catch (SQLException sqle) {
logger.error(sqle);
}
return getPeptidesForProtein(new ProteinLowMem(lSequence, lProteinID), msfFile);
}
/** {@inheritDoc} */
@Override
public void getPeptidesForProteinList(
List<ProteinLowMem> proteinLowMemList, MsfFile msfFile, int confidenceLevel) {
try {
String listOfProteinIds = "";
HashMap<Integer, ProteinLowMem> protIdToProt = new HashMap<Integer, ProteinLowMem>();
for (ProteinLowMem aProtein : proteinLowMemList) {
listOfProteinIds = "," + aProtein.getProteinID() + listOfProteinIds;
protIdToProt.put(aProtein.getProteinID(), aProtein);
}
listOfProteinIds = listOfProteinIds.replaceFirst(",", "");
PreparedStatement stat = null;
try {
if (msfFile.getVersion() == MsfVersion.VERSION1_2) {
stat =
msfFile
.getConnection()
.prepareStatement(
"select Peptides.PeptideID as PeptideID,ConfidenceLevel,Sequence,TotalIonsCount,MatchedIonsCount,Annotation,ProcessingNodeNumber, s.*, FileID as file,pp.ProteinID from spectrumheaders as s, masspeaks, Peptides,Spectra as sp, (select PeptideID,ProteinID from peptidesProteins where PeptidesProteins.ProteinID in ("
+ listOfProteinIds
+ ")) as pp where masspeaks.masspeakid = s.masspeakid and s.SpectrumID = Peptides.SpectrumID and s.UniqueSpectrumID = sp.UniqueSpectrumID and Peptides.PeptideID = pp.PeptideID and Peptides.ConfidenceLevel = "
+ confidenceLevel);
ResultSet rs = null;
try {
rs = stat.executeQuery();
while (rs.next()) {
PeptideLowMem lPeptide =
new PeptideLowMem(
rs.getInt("PeptideID"),
rs.getInt("SpectrumID"),
rs.getInt("ConfidenceLevel"),
rs.getString("Sequence"),
rs.getInt("TotalIonsCount"),
rs.getInt("MatchedIonsCount"),
rs.getString("Annotation"),
rs.getInt("ProcessingNodeNumber"),
msfFile.getAminoAcids());
lPeptide.setParentSpectrum(
new SpectrumLowMem(
rs.getInt("SpectrumID"),
rs.getInt("UniqueSpectrumID"),
rs.getInt("MassPeakID"),
rs.getInt("LastScan"),
rs.getInt("FirstScan"),
rs.getInt("ScanNumbers"),
rs.getInt("Charge"),
rs.getDouble("RetentionTime"),
rs.getDouble("Mass"),
rs.getInt("ScanEventID")));
lPeptide.getParentSpectrum().setFileId(rs.getInt("file"));
// lPeptide.getParentSpectrum().setZippedSpectrumXML(rs.getBytes("Spectrum"));
protIdToProt.get(rs.getInt("pp.ProteinID")).addPeptide(lPeptide);
}
} finally {
if (rs != null) {
rs.close();
}
}
} else {
stat =
msfFile
.getConnection()
.prepareStatement(
"select Peptides.PeptideID as peptideID,ConfidenceLevel,Sequence,TotalIonsCount,MatchedIonsCount,Annotation,ProcessingNodeNumber,MissedCleavages,UniquePeptideSequenceID, s.*, FileID as file,pp.ProteinID from spectrumheaders as s, masspeaks, Peptides,Spectra as sp, (select PeptideID,ProteinID from peptidesProteins where PeptidesProteins.ProteinID in ("
+ listOfProteinIds
+ ")) as pp where masspeaks.masspeakid = s.masspeakid and s.SpectrumID = Peptides.SpectrumID and s.UniqueSpectrumID = sp.UniqueSpectrumID and Peptides.PeptideID = pp.PeptideID and Peptides.ConfidenceLevel = ?");
stat.setInt(1, confidenceLevel);
ResultSet rs = stat.executeQuery();
try {
while (rs.next()) {
PeptideLowMem lPeptide =
new PeptideLowMem(
rs.getInt("peptideID"),
rs.getInt("SpectrumID"),
rs.getInt("ConfidenceLevel"),
rs.getString("Sequence"),
rs.getInt("TotalIonsCount"),
rs.getInt("MatchedIonsCount"),
rs.getString("Annotation"),
rs.getInt("ProcessingNodeNumber"),
msfFile.getAminoAcids());
lPeptide.setMissedCleavage(rs.getInt("MissedCleavages"));
lPeptide.setUniquePeptideSequenceId(rs.getInt("UniquePeptideSequenceID"));
lPeptide.setParentSpectrum(
new SpectrumLowMem(
rs.getInt("SpectrumID"),
rs.getInt("UniqueSpectrumID"),
rs.getInt("MassPeakID"),
rs.getInt("LastScan"),
rs.getInt("FirstScan"),
rs.getInt("ScanNumbers"),
rs.getInt("Charge"),
rs.getDouble("RetentionTime"),
rs.getDouble("Mass"),
rs.getInt("ScanEventID")));
lPeptide.getParentSpectrum().setFileId(rs.getInt("file"));
// lPeptide.getParentSpectrum().setZippedSpectrumXML(rs.getBytes("Spectrum"));
protIdToProt.get(rs.getInt("pp.ProteinID")).addPeptide(lPeptide);
}
} finally {
rs.close();
}
}
} finally {
if (stat != null) {
stat.close();
}
}
} catch (SQLException ex) {
logger.error(ex);
}
}
/**
* getPeptidesForProteinAtConfidenceLevel.
*
* @param protein a {@link com.compomics.thermo_msf_parser_API.lowmeminstance.model.ProteinLowMem}
* object.
* @param msfFile a {@link com.compomics.thermo_msf_parser_API.lowmeminstance.model.MsfFile}
* object.
* @param confidenceLevel a int.
* @return a {@link java.util.List} object.
*/
public List<PeptideLowMem> getPeptidesForProteinAtConfidenceLevel(
ProteinLowMem protein, MsfFile msfFile, int confidenceLevel) {
List<PeptideLowMem> foundPeptides = new ArrayList<PeptideLowMem>();
counter = 0;
try {
Statement stat = null;
try {
stat = msfFile.getConnection().createStatement();
if (msfFile.getVersion() == MsfVersion.VERSION1_2) {
ResultSet rs =
stat.executeQuery(
"select peptides.PeptideID as PeptideID,ConfidenceLevel,Sequence,TotalIonsCount,MatchedIonsCount,Annotation,ProcessingNodeNumber, s.*, FileID as file from spectrumheaders as s, masspeaks, Peptides,Spectra as sp,(select PeptideID as ID from PeptidesProteins where ProteinID ="
+ protein.getProteinID()
+ ") as pepid where pepid.ID = peptides.PeptideID and masspeaks.masspeakid = s.masspeakid and s.SpectrumID = peptides.SpectrumID and sp.UniqueSpectrumID = s.UniqueSpectrumID and ConfidenceLevel = "
+ confidenceLevel
+ "order by peptides.PeptideID");
while (rs.next()) {
PeptideLowMem lPeptide =
new PeptideLowMem(
rs.getInt("PeptideID"),
rs.getInt("SpectrumID"),
rs.getInt("ConfidenceLevel"),
rs.getString("Sequence"),
rs.getInt("TotalIonsCount"),
rs.getInt("MatchedIonsCount"),
rs.getString("Annotation"),
rs.getInt("ProcessingNodeNumber"),
msfFile.getAminoAcids());
// iScoreType.addScoresToPeptide(lPeptide,protein.getConnection());
lPeptide.setParentSpectrum(
new SpectrumLowMem(
rs.getInt("SpectrumID"),
rs.getInt("UniqueSpectrumID"),
rs.getInt("MassPeakID"),
rs.getInt("LastScan"),
rs.getInt("FirstScan"),
rs.getInt("ScanNumbers"),
rs.getInt("Charge"),
rs.getDouble("RetentionTime"),
rs.getDouble("Mass"),
rs.getInt("ScanEventID")));
// TODO make initializer with fileID argument)
lPeptide.getParentSpectrum().setFileId(rs.getInt("file"));
// lPeptide.getParentSpectrum().setZippedSpectrumXML(rs.getBytes("Spectra"));
// TODO check speed of this
Statement customStat = msfFile.getConnection().createStatement();
ResultSet customRs =
customStat.executeQuery(
"select * from CustomDataPeptides where PeptideID = "
+ lPeptide.getPeptideId());
while (customRs.next()) {
lPeptide.addCustomDataField(
customRs.getInt("FieldID"), customRs.getString("FieldValue"));
}
customRs.close();
customStat.close();
foundPeptides.add(lPeptide);
counter++;
}
rs.close();
} else {
ResultSet rs =
stat.executeQuery(
"select peptides.PeptideID as peptideid,ConfidenceLevel,Sequence,TotalIonsCount,MatchedIonsCount,Annotation,MissedCleavages,UniquePeptideSequenceID,ProcessingNodeNumber, s.*,FileID as file from spectrumheaders as s, masspeaks, Peptides ,Spectra as sp,(select PeptideID as ID from PeptidesProteins where ProteinID ="
+ protein.getProteinID()
+ ") as pepid where pepid.ID = peptides.PeptideID and masspeaks.masspeakid = s.masspeakid and s.SpectrumID = peptides.SpectrumID and sp.UniqueSpectrumID = s.UniqueSpectrumID and ConfidenceLevel = "
+ confidenceLevel
+ " order by peptides.PeptideID");
while (rs.next()) {
PeptideLowMem lPeptide =
new PeptideLowMem(
rs.getInt("PeptideID"),
rs.getInt("SpectrumID"),
rs.getInt("ConfidenceLevel"),
rs.getString("Sequence"),
rs.getInt("TotalIonsCount"),
rs.getInt("MatchedIonsCount"),
rs.getString("Annotation"),
rs.getInt("ProcessingNodeNumber"),
msfFile.getAminoAcids());
lPeptide.setMissedCleavage(rs.getInt("MissedCleavages"));
lPeptide.setUniquePeptideSequenceId(rs.getInt("UniquePeptideSequenceID"));
lPeptide.setParentSpectrum(
new SpectrumLowMem(
rs.getInt("SpectrumID"),
rs.getInt("UniqueSpectrumID"),
rs.getInt("MassPeakID"),
rs.getInt("LastScan"),
rs.getInt("FirstScan"),
rs.getInt("ScanNumbers"),
rs.getInt("Charge"),
rs.getDouble("RetentionTime"),
rs.getDouble("Mass"),
rs.getInt("ScanEventID")));
// TODO make initializer with fileID argument)
lPeptide.getParentSpectrum().setFileId(rs.getInt("file"));
Statement customStat = msfFile.getConnection().createStatement();
ResultSet customRs =
customStat.executeQuery(
"select * from CustomDataPeptides where PeptideID = "
+ lPeptide.getPeptideId());
while (customRs.next()) {
lPeptide.addCustomDataField(
customRs.getInt("FieldID"), customRs.getString("FieldValue"));
}
customRs.close();
customStat.close();
foundPeptides.add(lPeptide);
counter++;
}
rs.close();
}
} finally {
if (stat != null) {
stat.close();
}
}
} catch (SQLException ex) {
logger.error(ex);
}
return foundPeptides;
}
/** {@inheritDoc} */
@Override
public List<PeptideLowMem> getPeptidesWithConfidenceLevel(int confidenceLevel, MsfFile msfFile) {
List<PeptideLowMem> confidenceLevelPeptides = new ArrayList<PeptideLowMem>();
counter = 0;
int internalcounter = 0;
try {
Statement stat = null;
try {
stat = msfFile.getConnection().createStatement();
ResultSet rs = null;
try {
if (msfFile.getVersion() == MsfVersion.VERSION1_2) {
rs =
stat.executeQuery(
"select Peptides.PeptideID,ConfidenceLevel,Sequence,TotalIonsCount,MatchedIonsCount,Annotation,ProcessingNodeNumber, s.*, FileID as file, sp.Spectrum from spectrumheaders as s, masspeaks as m, Peptides, Spectra as sp where m.masspeakid = s.masspeakid and s.SpectrumID = Peptides.SpectrumID and s.UniqueSpectrumID = sp.UniqueSpectrumID and Peptides.ConfidenceLevel = "
+ confidenceLevel);
while (rs.next()) {
PeptideLowMem lPeptide =
new PeptideLowMem(
rs.getInt("PeptideID"),
rs.getInt("SpectrumID"),
rs.getInt("ConfidenceLevel"),
rs.getString("Sequence"),
rs.getInt("TotalIonsCount"),
rs.getInt("MatchedIonsCount"),
rs.getString("Annotation"),
rs.getInt("ProcessingNodeNumber"),
msfFile.getAminoAcids());
lPeptide.setParentSpectrum(
new SpectrumLowMem(
rs.getInt("SpectrumID"),
rs.getInt("UniqueSpectrumID"),
rs.getInt("MassPeakID"),
rs.getInt("LastScan"),
rs.getInt("FirstScan"),
rs.getInt("ScanNumbers"),
rs.getInt("Charge"),
rs.getDouble("RetentionTime"),
rs.getDouble("Mass"),
rs.getInt("ScanEventID")));
lPeptide.getParentSpectrum().setFileId(rs.getInt("file"));
lPeptide.getParentSpectrum().setZippedSpectrumXML(rs.getBytes("Spectrum"));
confidenceLevelPeptides.add(lPeptide);
counter++;
internalcounter++;
if (internalcounter > 30) {
internalcounter = 0;
setChanged();
notifyObservers();
}
}
scoreTypeInstance.getScoresForPeptideList(confidenceLevelPeptides, msfFile);
} else {
rs =
stat.executeQuery(
"select Peptides.PeptideID,ConfidenceLevel,Sequence,TotalIonsCount,MatchedIonsCount,Annotation,ProcessingNodeNumber,MissedCleavages,UniquePeptideSequenceID, s.*, FileID as file,sp.Spectrum from spectrumheaders as s, masspeaks as m, Peptides,Spectra as sp where m.masspeakid = s.masspeakid and s.SpectrumID = Peptides.SpectrumID and s.UniqueSpectrumID = sp.UniqueSpectrumID and Peptides.ConfidenceLevel = "
+ confidenceLevel);
while (rs.next()) {
PeptideLowMem lPeptide =
new PeptideLowMem(
rs.getInt("PeptideID"),
rs.getInt("SpectrumID"),
rs.getInt("ConfidenceLevel"),
rs.getString("Sequence"),
rs.getInt("TotalIonsCount"),
rs.getInt("MatchedIonsCount"),
rs.getString("Annotation"),
rs.getInt("ProcessingNodeNumber"),
msfFile.getAminoAcids());
lPeptide.setMissedCleavage(rs.getInt("MissedCleavages"));
lPeptide.setUniquePeptideSequenceId(rs.getInt("UniquePeptideSequenceID"));
lPeptide.setParentSpectrum(
new SpectrumLowMem(
rs.getInt("SpectrumID"),
rs.getInt("UniqueSpectrumID"),
rs.getInt("MassPeakID"),
rs.getInt("LastScan"),
rs.getInt("FirstScan"),
rs.getInt("ScanNumbers"),
rs.getInt("Charge"),
rs.getDouble("RetentionTime"),
rs.getDouble("Mass"),
rs.getInt("ScanEventID")));
// TODO make initializer with fileID argument
lPeptide.getParentSpectrum().setFileId(rs.getInt("file"));
lPeptide.getParentSpectrum().setZippedSpectrumXML(rs.getBytes("Spectrum"));
confidenceLevelPeptides.add(lPeptide);
counter++;
internalcounter++;
if (internalcounter > 30) {
internalcounter = 0;
setChanged();
notifyObservers();
}
}
rs.close();
scoreTypeInstance.getScoresForPeptideList(confidenceLevelPeptides, msfFile);
}
} finally {
if (rs != null) {
rs.close();
}
}
} finally {
if (stat != null) {
stat.close();
}
}
} catch (SQLException sqle) {
logger.error(sqle);
}
return confidenceLevelPeptides;
}