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); }