void TransitionPQPReader::writePQPOutput_(const char* filename, OpenMS::TargetedExperiment& targeted_exp)
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
// delete file if present
remove(filename);
// Open database
rc = sqlite3_open(filename, &db);
if ( rc )
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
}
// Create SQL structure
const char* create_sql =
// protein table
// OpenSWATH proteomics workflows
"CREATE TABLE PROTEIN(" \
"ID INT PRIMARY KEY NOT NULL," \
"PROTEIN_ACCESSION TEXT NOT NULL," \
"DECOY INT NULL);" \
// peptide_protein_mapping table
// OpenSWATH proteomics workflows
"CREATE TABLE PEPTIDE_PROTEIN_MAPPING(" \
"PEPTIDE_ID INT NOT NULL," \
"PROTEIN_ID INT NOT NULL);" \
// peptide table
// OpenSWATH proteomics workflows
"CREATE TABLE PEPTIDE(" \
"ID INT PRIMARY KEY NOT NULL," \
"UNMODIFIED_SEQUENCE TEXT NOT NULL," \
"MODIFIED_SEQUENCE TEXT NOT NULL," \
"DECOY INT NOT NULL);" \
// precursor_peptide_mapping table
// OpenSWATH proteomics workflows
"CREATE TABLE PRECURSOR_PEPTIDE_MAPPING(" \
"PRECURSOR_ID INT NOT NULL," \
"PEPTIDE_ID INT NOT NULL);" \
// compound table
// OpenSWATH metabolomics workflows
"CREATE TABLE COMPOUND(" \
"ID INT PRIMARY KEY NOT NULL," \
"COMPOUND_NAME TEXT NOT NULL," \
"SUM_FORMULA TEXT NOT NULL," \
"SMILES TEXT NOT NULL," \
"DECOY INT NOT NULL);" \
// precursor_compound_mapping table
// OpenSWATH metabolomics workflows
"CREATE TABLE PRECURSOR_COMPOUND_MAPPING(" \
"PRECURSOR_ID INT NOT NULL," \
"COMPOUND_ID INT NOT NULL);" \
// precursor table
"CREATE TABLE PRECURSOR(" \
"ID INT PRIMARY KEY NOT NULL," \
"TRAML_ID TEXT NULL," \
"GROUP_LABEL TEXT NULL," \
"PRECURSOR_MZ REAL NOT NULL," \
"CHARGE INT NULL," \
"LIBRARY_INTENSITY REAL NULL," \
"LIBRARY_RT REAL NULL," \
"DECOY INT NOT NULL);" \
// transition_precursor_mapping table
"CREATE TABLE TRANSITION_PRECURSOR_MAPPING(" \
"TRANSITION_ID INT NOT NULL," \
"PRECURSOR_ID INT NOT NULL);" \
// transition_peptide_mapping table
// IPF proteomics workflows
"CREATE TABLE TRANSITION_PEPTIDE_MAPPING(" \
"TRANSITION_ID INT NOT NULL," \
"PEPTIDE_ID INT NOT NULL);" \
// transition table
"CREATE TABLE TRANSITION(" \
"ID INT PRIMARY KEY NOT NULL," \
"TRAML_ID TEXT NULL," \
"PRODUCT_MZ REAL NOT NULL," \
"CHARGE INT NULL," \
"TYPE CHAR(1) NULL," \
"ORDINAL INT NULL," \
"DETECTING INT NOT NULL," \
"IDENTIFYING INT NOT NULL," \
"QUANTIFYING INT NOT NULL," \
"LIBRARY_INTENSITY REAL NULL," \
"DECOY INT NOT NULL);";
// Execute SQL create statement
rc = sqlite3_exec(db, create_sql, callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Prepare insert statements
// Index maps
std::vector<std::string> group_set, peptide_set, compound_set, protein_set;
std::map<int,double> precursor_mz_map;
std::map<int,bool> precursor_decoy_map;
std::stringstream insert_transition_sql, insert_transition_peptide_mapping_sql, insert_transition_precursor_mapping_sql;
insert_transition_sql.precision(11);
// OpenSWATH: Loop through TargetedExperiment to generate index maps for peptides
Size progress = 0;
startProgress(0, targeted_exp.getPeptides().size(), "Convert peptides");
for (Size i = 0; i < targeted_exp.getPeptides().size(); i++)
{
setProgress(progress++);
OpenMS::TargetedExperiment::Peptide peptide = targeted_exp.getPeptides()[i];
std::string peptide_sequence = TargetedExperimentHelper::getAASequence(peptide).toString();
peptide_set.push_back(peptide_sequence);
group_set.push_back(peptide.id);
}
endProgress();
// OpenSWATH: Loop through TargetedExperiment to generate index maps for compounds
progress = 0;
startProgress(0, targeted_exp.getCompounds().size(), "Convert compounds");
for (Size i = 0; i < targeted_exp.getCompounds().size(); i++)
{
setProgress(progress++);
OpenMS::TargetedExperiment::Compound compound = targeted_exp.getCompounds()[i];
compound_set.push_back(compound.id);
group_set.push_back(compound.id);
}
endProgress();
// OpenSWATH: Group set must be unique
boost::erase(group_set, boost::unique<boost::return_found_end>(boost::sort(group_set)));
// IPF: Loop through all transitions and generate peptidoform data structures
progress = 0;
std::vector<TransitionPQPReader::TSVTransition > transitions;
startProgress(0, targeted_exp.getTransitions().size(), "Convert peptidoforms");
for (Size i = 0; i < targeted_exp.getTransitions().size(); i++)
{
setProgress(progress++);
TransitionPQPReader::TSVTransition transition = convertTransition_(&targeted_exp.getTransitions()[i], targeted_exp);
transitions.push_back(transition);
std::copy( transition.peptidoforms.begin(), transition.peptidoforms.end(), std::inserter( peptide_set, peptide_set.end() ) );
int group_set_index = std::distance(group_set.begin(),std::find(group_set.begin(), group_set.end(), transition.group_id));
if (precursor_mz_map.find(group_set_index) == precursor_mz_map.end())
{
precursor_mz_map[group_set_index] = transition.precursor;
}
if (precursor_decoy_map.find(group_set_index) == precursor_decoy_map.end())
{
if (transition.detecting_transition == 1)
{
precursor_decoy_map[group_set_index] = transition.decoy;
}
}
}
endProgress();
// OpenSWATH: Peptide and compound sets must be unique
boost::erase(peptide_set, boost::unique<boost::return_found_end>(boost::sort(peptide_set)));
boost::erase(compound_set, boost::unique<boost::return_found_end>(boost::sort(compound_set)));
// OpenSWATH: Prepare transition inserts
progress = 0;
startProgress(0, transitions.size(), String("Prepare ") + transitions.size() + " transitions and mapping");
for (Size i = 0; i < transitions.size(); i++)
{
setProgress(progress++);
TransitionPQPReader::TSVTransition transition = transitions[i];
// IPF: Generate transition-peptide mapping tables (one identification transition can map to multiple peptidoforms)
for (Size j = 0; j < transition.peptidoforms.size(); j++)
{
insert_transition_peptide_mapping_sql << "INSERT INTO TRANSITION_PEPTIDE_MAPPING (TRANSITION_ID, PEPTIDE_ID) VALUES (" << i << "," << std::distance(peptide_set.begin(),std::find(peptide_set.begin(), peptide_set.end(), transition.peptidoforms[j])) << "); ";
}
// OpenSWATH: Associate transitions with their precursors
insert_transition_precursor_mapping_sql << "INSERT INTO TRANSITION_PRECURSOR_MAPPING (TRANSITION_ID, PRECURSOR_ID) VALUES (" << i << "," << std::distance(group_set.begin(), std::find(group_set.begin(), group_set.end(),transition.group_id)) << "); ";
std::string transition_charge = "NULL"; // workaround for compounds with missing charge
if (transition.fragment_charge != "NA")
{
transition_charge = transition.fragment_charge;
}
// OpenSWATH: Insert transition data
insert_transition_sql << "INSERT INTO TRANSITION (ID, TRAML_ID, PRODUCT_MZ, CHARGE, TYPE, ORDINAL, DETECTING, IDENTIFYING, QUANTIFYING, LIBRARY_INTENSITY, DECOY) VALUES (" << i << ",'" << transition.transition_name << "'," << transition.product << "," << transition_charge << ",'" << transition.fragment_type<< "'," << transition.fragment_nr << "," << transition.detecting_transition << "," << transition.identifying_transition << "," << transition.quantifying_transition << "," << transition.library_intensity << "," << transition.decoy << "); ";
}
endProgress();
// OpenSWATH: Prepare protein inserts
progress = 0;
startProgress(0, targeted_exp.getProteins().size(), "Prepare protein mapping");
for (Size i = 0; i < targeted_exp.getProteins().size(); i++)
{
setProgress(progress++);
OpenMS::TargetedExperiment::Protein protein = targeted_exp.getProteins()[i];
protein_set.push_back(protein.id);
}
endProgress();
boost::erase(protein_set, boost::unique<boost::return_found_end>(boost::sort(protein_set)));
std::stringstream insert_precursor_sql, insert_precursor_peptide_mapping, insert_precursor_compound_mapping;
insert_precursor_sql.precision(11);
std::vector<std::pair<int, int> > peptide_protein_map;
// OpenSWATH: Prepare peptide precursor inserts
progress = 0;
startProgress(0, targeted_exp.getPeptides().size(), "Prepare peptide precursors and mapping");
for (Size i = 0; i < targeted_exp.getPeptides().size(); i++)
{
setProgress(progress++);
OpenMS::TargetedExperiment::Peptide peptide = targeted_exp.getPeptides()[i];
std::string peptide_sequence = TargetedExperimentHelper::getAASequence(peptide).toString();
int group_set_index = std::distance(group_set.begin(),std::find(group_set.begin(), group_set.end(), peptide.id));
int peptide_set_index = std::distance(peptide_set.begin(), std::find(peptide_set.begin(), peptide_set.end(), peptide_sequence));
for (std::vector<String>::iterator it = peptide.protein_refs.begin(); it != peptide.protein_refs.end(); ++it)
{
int protein_set_index = std::distance(protein_set.begin(),std::find(protein_set.begin(), protein_set.end(), *it));
peptide_protein_map.push_back(std::make_pair(peptide_set_index,protein_set_index));
}
insert_precursor_sql << "INSERT INTO PRECURSOR (ID, TRAML_ID, GROUP_LABEL, PRECURSOR_MZ, CHARGE, LIBRARY_INTENSITY, LIBRARY_RT, DECOY) VALUES (" << group_set_index << ",'" << peptide.id << "','" << peptide.getPeptideGroupLabel() << "'," << precursor_mz_map[group_set_index] << "," << peptide.getChargeState() << ",NULL," << peptide.getRetentionTime() << "," << precursor_decoy_map[group_set_index] << "); ";
insert_precursor_peptide_mapping << "INSERT INTO PRECURSOR_PEPTIDE_MAPPING (PRECURSOR_ID, PEPTIDE_ID) VALUES (" << group_set_index << "," << peptide_set_index << "); ";
}
endProgress();
// OpenSWATH: Prepare compound precursor inserts
progress = 0;
startProgress(0, targeted_exp.getCompounds().size(), "Prepare compound precursors and mapping");
for (Size i = 0; i < targeted_exp.getCompounds().size(); i++)
{
setProgress(progress++);
OpenMS::TargetedExperiment::Compound compound = targeted_exp.getCompounds()[i];
int group_set_index = std::distance(group_set.begin(),std::find(group_set.begin(), group_set.end(), compound.id));
int compound_set_index = std::distance(compound_set.begin(), std::find(compound_set.begin(), compound_set.end(), compound.id));
std::string compound_charge = "NULL"; // workaround for compounds with missing charge
if (compound.hasCharge())
{
compound_charge = String(compound.getChargeState());
}
insert_precursor_sql << "INSERT INTO PRECURSOR (ID, TRAML_ID, GROUP_LABEL, PRECURSOR_MZ, CHARGE, LIBRARY_INTENSITY, LIBRARY_RT, DECOY) VALUES (" << group_set_index << ",'" << compound.id << "',NULL," << precursor_mz_map[group_set_index] << "," << compound_charge << ",NULL,NULL" << "," << precursor_decoy_map[group_set_index] << "); ";
insert_precursor_compound_mapping << "INSERT INTO PRECURSOR_COMPOUND_MAPPING (PRECURSOR_ID, COMPOUND_ID) VALUES (" << group_set_index << "," << compound_set_index << "); ";
}
endProgress();
boost::erase(peptide_protein_map, boost::unique<boost::return_found_end>(boost::sort(peptide_protein_map)));
// OpenSWATH: Prepare peptide-protein mapping inserts
std::stringstream insert_peptide_protein_mapping;
progress = 0;
startProgress(0, peptide_protein_map.size(), "Prepare peptide - protein mapping");
for (std::vector<std::pair<int, int> >::iterator it = peptide_protein_map.begin(); it != peptide_protein_map.end(); ++it)
{
setProgress(progress++);
insert_peptide_protein_mapping << "INSERT INTO PEPTIDE_PROTEIN_MAPPING (PEPTIDE_ID, PROTEIN_ID) VALUES (" << it->first << "," << it->second << "); ";
}
endProgress();
// OpenSWATH: Prepare protein inserts
std::stringstream insert_protein_sql;
progress = 0;
startProgress(0, protein_set.size(), String("Prepare ") + protein_set.size() + " proteins");
for (Size i = 0; i < protein_set.size(); i++)
{
setProgress(progress++);
insert_protein_sql << "INSERT INTO PROTEIN (ID, PROTEIN_ACCESSION) VALUES (" << i << ",'" << protein_set[i] << "'); ";
}
endProgress();
// OpenSWATH: Prepare peptide inserts
std::stringstream insert_peptide_sql;
progress = 0;
startProgress(0, peptide_set.size(), String("Prepare ") + peptide_set.size() + " peptides");
for (std::vector<std::string>::iterator it = peptide_set.begin(); it != peptide_set.end(); ++it)
{
setProgress(progress++);
insert_peptide_sql << "INSERT INTO PEPTIDE (ID, UNMODIFIED_SEQUENCE, MODIFIED_SEQUENCE, DECOY) VALUES (" << std::distance(peptide_set.begin(),std::find(peptide_set.begin(), peptide_set.end(),*it)) << ",'" << AASequence::fromString(*it).toUnmodifiedString() << "','" << *it << "'," << 0 <<"); ";
}
endProgress();
// OpenSWATH: Prepare compound inserts
std::stringstream insert_compound_sql;
progress = 0;
startProgress(0, compound_set.size(), String("Prepare ") + compound_set.size() + " compounds");
for (std::vector<std::string>::iterator it = compound_set.begin(); it != compound_set.end(); ++it)
{
setProgress(progress++);
OpenMS::TargetedExperiment::Compound compound = targeted_exp.getCompoundByRef(*it);
insert_compound_sql << "INSERT INTO COMPOUND (ID, COMPOUND_NAME, SUM_FORMULA, SMILES, DECOY) VALUES (" << std::distance(compound_set.begin(),std::find(compound_set.begin(), compound_set.end(),*it)) << ",'" << compound.id << "','" << compound.molecular_formula << "','" << compound.smiles_string << "'," << 0 <<"); ";
}
endProgress();
std::cout << "Write PQP file" << std::endl;
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &zErrMsg);
// Execute SQL insert statement
std::string insert_protein_sql_str = insert_protein_sql.str();
rc = sqlite3_exec(db, insert_protein_sql_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Execute SQL insert statement
std::string insert_peptide_protein_mapping_str = insert_peptide_protein_mapping.str();
rc = sqlite3_exec(db, insert_peptide_protein_mapping_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Execute SQL insert statement
std::string insert_peptide_sql_str = insert_peptide_sql.str();
rc = sqlite3_exec(db, insert_peptide_sql_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Execute SQL insert statement
std::string insert_compound_sql_str = insert_compound_sql.str();
rc = sqlite3_exec(db, insert_compound_sql_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Execute SQL insert statement
std::string insert_precursor_peptide_mapping_str = insert_precursor_peptide_mapping.str();
rc = sqlite3_exec(db, insert_precursor_peptide_mapping_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Execute SQL insert statement
std::string insert_precursor_compound_mapping_str = insert_precursor_compound_mapping.str();
rc = sqlite3_exec(db, insert_precursor_compound_mapping_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Execute SQL insert statement
std::string insert_precursor_sql_str = insert_precursor_sql.str();
rc = sqlite3_exec(db, insert_precursor_sql_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Execute SQL insert statement
std::string insert_transition_sql_str = insert_transition_sql.str();
rc = sqlite3_exec(db, insert_transition_sql_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Execute SQL insert statement
std::string insert_transition_peptide_mapping_sql_str = insert_transition_peptide_mapping_sql.str();
rc = sqlite3_exec(db, insert_transition_peptide_mapping_sql_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
// Execute SQL insert statement
std::string insert_transition_precursor_mapping_sql_str = insert_transition_precursor_mapping_sql.str();
rc = sqlite3_exec(db, insert_transition_precursor_mapping_sql_str.c_str(), callback, 0, &zErrMsg);
if ( rc != SQLITE_OK )
{
sqlite3_free(zErrMsg);
throw Exception::IllegalArgument(__FILE__, __LINE__, OPENMS_PRETTY_FUNCTION,
zErrMsg);
}
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &zErrMsg);
sqlite3_close(db);
}
