Home  · Classes  · Annotated Classes  · Modules  · Members  · Namespaces  · Related Pages
DBAdapter.h
Go to the documentation of this file.
1 // --------------------------------------------------------------------------
2 // OpenMS -- Open-Source Mass Spectrometry
3 // --------------------------------------------------------------------------
4 // Copyright The OpenMS Team -- Eberhard Karls University Tuebingen,
5 // ETH Zurich, and Freie Universitaet Berlin 2002-2013.
6 //
7 // This software is released under a three-clause BSD license:
8 // * Redistributions of source code must retain the above copyright
9 // notice, this list of conditions and the following disclaimer.
10 // * Redistributions in binary form must reproduce the above copyright
11 // notice, this list of conditions and the following disclaimer in the
12 // documentation and/or other materials provided with the distribution.
13 // * Neither the name of any author or any participating institution
14 // may be used to endorse or promote products derived from this software
15 // without specific prior written permission.
16 // For a full list of authors, refer to the file AUTHORS.
17 // --------------------------------------------------------------------------
18 // THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19 // AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20 // IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21 // ARE DISCLAIMED. IN NO EVENT SHALL ANY OF THE AUTHORS OR THE CONTRIBUTING
22 // INSTITUTIONS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23 // EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24 // PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
25 // OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
26 // WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
27 // OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
28 // ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29 //
30 // --------------------------------------------------------------------------
31 // $Maintainer: $
32 // $Authors: Marc Sturm $
33 // --------------------------------------------------------------------------
34 
35 #ifndef OPENMS_FORMAT_DB_DBADAPTER_H
36 #define OPENMS_FORMAT_DB_DBADAPTER_H
37 
38 //OpenMS includes
42 
43 //QT includes
44 #include <QtSql/QSqlQuery>
45 #include <QtCore/QVariant>
46 #include <QtCore/QDate>
47 
48 //std and STL includes
49 #include <string>
50 #include <map>
51 
52 namespace OpenMS
53 {
54  class DBConnection;
55 
69  class OPENMS_DLLAPI DBAdapter
70  {
71 public:
73  DBAdapter(DBConnection & db_con);
74 
76  ~DBAdapter();
77 
79  template <class ExperimentType>
80  void storeExperiment(ExperimentType & exp);
81 
83  template <class ExperimentType>
84  void loadExperiment(UID id, ExperimentType & exp);
85 
86  template <class SpectrumType>
88  void loadSpectrum(UID id, SpectrumType & spec);
89 
91  PeakFileOptions & getOptions();
92 
94  const PeakFileOptions & getOptions() const;
95 
101  bool checkDBVersion(bool warning);
102 
104  void createDB();
105 
106 private:
109 
111  DBAdapter();
112 
118  UID storeMetaInfo_(const String & parent_table, UID parent_id, const MetaInfoInterface & info);
120  UID storeMetaInfo_(const String & parent_table, UID parent_id, const Peak1D & peak);
122  UID storeMetaInfo_(const String & parent_table, UID parent_id, const RichPeak1D & peak);
124  UID storeMetaInfo_(const String & parent_table, UID parent_id, const Precursor & peak);
125 
127  void loadMetaInfo_(UID id, MetaInfoInterface & info);
129  void loadMetaInfo_(UID id, Peak1D & peak);
131  void loadMetaInfo_(UID id, RichPeak1D & peak);
133  void loadMetaInfo_(UID id, Precursor & peak);
134 
139  void deleteMetaInfo_(const String & parent_table, const String & condition);
140 
146  UID storeFile_(const String & parent_table, UID parent_id, const SourceFile & file);
147 
152  void loadFile_(UID id, SourceFile & file);
153 
159  UID storeSample_(const Sample & sample, UID exp_id, UID parent_id);
160 
165  void loadSample_(UID id, Sample & sample);
166 
168  };
169 
170 
171 //------------------------------------------- IMPLEMENTATION OF TEMPLATE METHODS ----------------------------------
172 
173  template <class ExperimentType>
174  void DBAdapter::storeExperiment(ExperimentType & exp)
175  {
176  std::stringstream query; // query to build
177  String end; // end of the query that is added afer all fields
178  String tmp; // temporary data
179  bool new_entry(false); // stores if the current object is already in the DB
180  QSqlQuery result; // place to store the query results in
181  int parent_id(-1); // stores parent_id of meta information
182  UID acquisition_info_id(0); // stores id of acquisition_info
183  UID meta_id(0); // stores MetaInfo id of meta information that was just stored
184 
185  //----------------------------------------------------------------------------------------
186  //------------------------------- CHECK DB VERSION ---------------------------------------
187  //----------------------------------------------------------------------------------------
188  if (!checkDBVersion(true))
189  return;
190 
191 
192 
193  //----------------------------------------------------------------------------------------
194  //------------------------------- store EXPERIMENT ---------------------------------------
195  //----------------------------------------------------------------------------------------
196 
197  query.str("");
198  new_entry = (exp.getPersistenceId() == 0);
199  if (new_entry)
200  {
201  query << "INSERT INTO META_MSExperiment SET ";
202  end = "";
203  }
204  else
205  {
206  query << "UPDATE META_MSExperiment SET ";
207  end = " WHERE id='" + String(exp.getPersistenceId()) + "'";
208  }
209  //date
210  //TODO make sure date and time are stored/loaded correctly
211  query << "Date='" << exp.getDateTime().get() << "'";
212  //description
213  query << ",Description='" << exp.getComment() << "'";
214  //ExperimentalSettings FractionIdentifier
215  query << ",FractionIdentifier='" << exp.getFractionIdentifier() << "'";
216 
217  query << end;
218  result = db_con_.executeQuery(query.str());
219  if (new_entry)
220  {
221  exp.setPersistenceId(db_con_.getAutoId());
222  }
223 
224  storeMetaInfo_("META_MSExperiment", exp.getPersistenceId(), exp);
225 
226  //----------------------------------------------------------------------------------------
227  //----------- store PROTEIN IDENTIFICATIONS/HITS / SEARCHPARAMETERS-----------------------
228  //----------------------------------------------------------------------------------------
229 
230  std::vector<ProteinIdentification> & pi = exp.getProteinIdentifications();
231 
232  // first delete all old values, no matter whether we're updating or not
233  // do we have to delete the MetaInfo as well?
234  query.str("");
235  query << "DELETE FROM ID_ProteinIdentification WHERE fid_MSExperiment='" << exp.getPersistenceId() << "'";
236  result = db_con_.executeQuery(query.str());
237 
238  for (std::vector<ProteinIdentification>::const_iterator pi_it = pi.begin(); pi_it != pi.end(); pi_it++)
239  {
240  query.str("");
241  query << "INSERT INTO ID_ProteinIdentification SET ";
242  query << "fid_MSExperiment='" << exp.getPersistenceId() << "'";
243  query << ",SearchEngine='" << pi_it->getSearchEngine() << "'";
244  query << ",SearchEngineVersion='" << pi_it->getSearchEngineVersion() << "'";
245  query << ",Date='" << pi_it->getDateTime().get() << "'";
246  query << ",ScoreType='" << pi_it->getScoreType() << "'";
247  query << ",HigherScoreBetter='" << pi_it->isHigherScoreBetter() << "'";
248  query << ",SignificanceThreshold='" << pi_it->getSignificanceThreshold() << "'";
249 
250  result = db_con_.executeQuery(query.str());
251  parent_id = db_con_.getAutoId();
252 
253  storeMetaInfo_("ID_ProteinIdentification", parent_id, *pi_it);
254  //needs getter and setter methods first
255  //storeFile_("ID_ProteinIdentification", parent_id, pi_it->getSourceFile());
256 
257  //save the ProteinHits
258  for (std::vector<ProteinHit>::const_iterator ph_it = pi_it->getHits().begin(); ph_it != pi_it->getHits().end(); ph_it++)
259  {
260  query.str("");
261  query << "INSERT INTO ID_ProteinHit SET ";
262  query << "fid_ProteinIdentification='" << parent_id << "'";
263  query << ",Score='" << ph_it->getScore() << "'";
264  query << ",Accession='" << ph_it->getAccession() << "'";
265  query << ",Sequence='" << ph_it->getSequence() << "'";
266  query << ",Rank='" << ph_it->getRank() << "'";
267 
268  result = db_con_.executeQuery(query.str());
269  meta_id = db_con_.getAutoId();
270 
271  storeMetaInfo_("ID_ProteinHit", meta_id, *ph_it);
272  }
273 
274  //save the searchparameters
275  query.str("");
276  query << "DELETE FROM ID_SearchParameters WHERE fid_ProteinIdentification='" << parent_id << "'";
277  result = db_con_.executeQuery(query.str());
278 
279  query.str("");
280  query << "INSERT INTO ID_SearchParameters SET ";
281  query << "fid_ProteinIdentification='" << parent_id << "'";
282  query << ",DB='" << pi_it->getSearchParameters().db << "'";
283  query << ",DBVersion='" << pi_it->getSearchParameters().db_version << "'";
284  query << ",Taxonomy='" << pi_it->getSearchParameters().taxonomy << "'";
285  query << ",Charges='" << pi_it->getSearchParameters().charges << "'";
286  query << ",MassType='" << (1u + pi_it->getSearchParameters().mass_type) << "'";
287  query << ",Enzyme='" << (1u + pi_it->getSearchParameters().enzyme) << "'";
288  query << ",MissedCleavages='" << pi_it->getSearchParameters().missed_cleavages << "'";
289  query << ",PeakMassTolerance='" << pi_it->getSearchParameters().peak_mass_tolerance << "'";
290  query << ",PrecursorTolerance='" << pi_it->getSearchParameters().precursor_tolerance << "'";
291 
292  result = db_con_.executeQuery(query.str());
293 
294  meta_id = db_con_.getAutoId();
295  storeMetaInfo_("ID_SearchParameters", meta_id, pi_it->getSearchParameters());
296 
297  //if modifications then save them
298  query.str("");
299  query << "DELETE FROM ID_FixedModifications WHERE fid_SearchParameters='" << meta_id << "'";
300  result = db_con_.executeQuery(query.str());
301 
302  for (std::vector<String>::const_iterator mod_it = pi_it->getSearchParameters().fixed_modifications.begin(); mod_it != pi_it->getSearchParameters().fixed_modifications.end(); mod_it++)
303  {
304 
305  query.str("");
306  query << "INSERT INTO ID_FixedModifications SET ";
307  query << "fid_SearchParameters='" << meta_id << "'";
308  query << ",name='" << *mod_it << "'";
309 
310  result = db_con_.executeQuery(query.str());
311  }
312  for (std::vector<String>::const_iterator mod_it = pi_it->getSearchParameters().variable_modifications.begin(); mod_it != pi_it->getSearchParameters().variable_modifications.end(); mod_it++)
313  {
314  query.str("");
315  query << "INSERT INTO ID_VariableModifications SET ";
316  query << "fid_SearchParameters='" << meta_id << "'";
317  query << ",name='" << *mod_it << "'";
318 
319  result = db_con_.executeQuery(query.str());
320  }
321  }
322 
323 
324  //----------------------------------------------------------------------------------------
325  //-------------------------------- store SAMPLE ------------------------------------------
326  //----------------------------------------------------------------------------------------
327 
328  query.str("");
329  deleteMetaInfo_("META_Sample", "fid_MSExperiment=" + String(exp.getPersistenceId()));
330  // this also deletes all references in META_SampleTreatment, META_Digestion and META_Modification by constraint
331  query << "DELETE FROM META_Sample WHERE fid_MSExperiment='" << exp.getPersistenceId() << "'";
332  storeSample_(exp.getSample(), exp.getPersistenceId(), 0);
333 
334  //----------------------------------------------------------------------------------------
335  //-------------------------------- store CONTACTPERSON -----------------------------------
336  //----------------------------------------------------------------------------------------
337 
338  const std::vector<ContactPerson> & contacts = exp.getContacts();
339 
340  query.str("");
341  deleteMetaInfo_("META_ContactPerson", "fid_MSExperiment=" + String(exp.getPersistenceId()));
342  query << "DELETE FROM META_ContactPerson WHERE fid_MSExperiment='" << exp.getPersistenceId() << "'";
343  result = db_con_.executeQuery(query.str());
344 
345  for (std::vector<ContactPerson>::const_iterator contact_it = contacts.begin(); contact_it != contacts.end(); contact_it++)
346  {
347  query.str("");
348  query << "INSERT INTO META_ContactPerson SET ";
349  query << "fid_MSExperiment='" << exp.getPersistenceId() << "'";
350  query << ",PreName='" << contact_it->getFirstName() << "'";
351  query << ",LastName='" << contact_it->getLastName() << "'";
352  query << ",Affiliation='" << contact_it->getInstitution() << "'";
353  query << ",Email='" << contact_it->getEmail() << "'";
354  query << ",Comment='" << contact_it->getContactInfo() << "'";
355 
356  result = db_con_.executeQuery(query.str());
357  parent_id = db_con_.getAutoId();
358 
359  storeMetaInfo_("META_ContactPerson", parent_id, *contact_it);
360  }
361 
362  //----------------------------------------------------------------------------------------
363  //-------------------------------------- store HPLC --------------------------------------
364  //----------------------------------------------------------------------------------------
365 
366  const HPLC & hplc = exp.getHPLC();
367  query.str("");
368 
369  if (new_entry)
370  {
371  query << "INSERT INTO META_HPLC SET ";
372  query << "fid_MSExperiment='" << exp.getPersistenceId() << "',";
373  end = "";
374  }
375  else
376  {
377  query << "SELECT id FROM META_HPLC WHERE fid_MSExperiment='" << exp.getPersistenceId() << "'";
378  result = db_con_.executeQuery(query.str(), true);
379  parent_id = result.value(0).toInt();
380 
381  query.str("");
382  query << "UPDATE META_HPLC SET ";
383  end = " WHERE fid_MSExperiment='" + String(exp.getPersistenceId()) + "'";
384  }
385 
386  query << "InstrumentName='" << hplc.getInstrument() << "'";
387  query << ",ColumnName='" << hplc.getColumn() << "'";
388  query << ",Description='" << hplc.getComment() << "'";
389  query << ",Flux=" << hplc.getFlux();
390  query << ",Pressure=" << hplc.getPressure();
391  query << ",Temperature=" << hplc.getTemperature();
392 
393  query << end;
394  result = db_con_.executeQuery(query.str());
395 
396  if (new_entry)
397  {
398  parent_id = db_con_.getAutoId();
399  }
400 
401  //----------------------------------------------------------------------------------------
402  //------------------------- store GRADIENTEluent/Time/Percentage -------------------------
403  //----------------------------------------------------------------------------------------
404 
405  const Gradient & gradient = exp.getHPLC().getGradient();
406  const std::vector<String> & eluents = gradient.getEluents();
407  const std::vector<Int> & time = gradient.getTimepoints();
408  const std::vector<std::vector<UInt> > & percentages = gradient.getPercentages();
409  std::stringstream query_eluents, query_time, query_percentages;
410  UID eluents_id(0), time_id(0);
411 
412  // this also deletes all references in META_GradientPercentage by constraint
413  query.str("");
414  query << "DELETE FROM META_GradientEluent WHERE fid_HPLC=" << parent_id;
415  result = db_con_.executeQuery(query.str());
416  query.str("");
417  query << "DELETE FROM META_GradientTime WHERE fid_HPLC=" << parent_id;
418  result = db_con_.executeQuery(query.str());
419 
420  if (!eluents.empty())
421  {
422  query_eluents.str("");
423  query_eluents << "INSERT INTO META_GradientEluent (fid_HPLC, Name) VALUES ";
424  for (std::vector<String>::const_iterator eluents_it = eluents.begin(); eluents_it != eluents.end(); eluents_it++)
425  {
426  query_eluents << "(";
427  query_eluents << parent_id;
428  query_eluents << ",'" << *eluents_it << "'";
429  query_eluents << "),";
430  }
431  // remove last ","
432  result = db_con_.executeQuery(String(query_eluents.str()).chop(1));
433  eluents_id = db_con_.getAutoId();
434  }
435 
436  if (!time.empty())
437  {
438  query_time.str("");
439  query_time << "INSERT INTO META_GradientTime (fid_HPLC, Time) VALUES ";
440  for (std::vector<Int>::const_iterator time_it = time.begin(); time_it != time.end(); time_it++)
441  {
442  query_time << "(";
443  query_time << parent_id;
444  query_time << "," << *time_it;
445  query_time << "),";
446  }
447  // remove last ","
448  result = db_con_.executeQuery(String(query_time.str()).chop(1));
449  time_id = db_con_.getAutoId();
450  }
451 
452  if (!percentages.empty() && !eluents.empty() && !time.empty())
453  {
454  query_percentages.str("");
455  query_percentages << "INSERT INTO META_GradientPercentage (fid_GradientEluent, fid_GradientTime, Percentage) VALUES ";
456  int i = 0;
457  // iterate over eluents
458  for (std::vector<std::vector<UInt> >::const_iterator percent_outer_it = percentages.begin(); percent_outer_it != percentages.end(); percent_outer_it++)
459  {
460  int j = 0;
461  // iterate over timepoints
462  for (std::vector<UInt>::const_iterator percent_inner_it = (*percent_outer_it).begin(); percent_inner_it != (*percent_outer_it).end(); percent_inner_it++)
463  {
464  query_percentages << "(";
465  query_percentages << eluents_id + i;
466  query_percentages << "," << time_id + j;
467  query_percentages << "," << *percent_inner_it;
468  query_percentages << "),";
469  j++;
470  }
471  i++;
472  }
473  // remove last ","
474  result = db_con_.executeQuery(String(query_percentages.str()).chop(1));
475  }
476 
477  //----------------------------------------------------------------------------------------
478  //--------------------------------- store INSTRUMENT -------------------------------------
479  //----------------------------------------------------------------------------------------
480 
481  const Instrument & instrument = exp.getInstrument();
482  query.str("");
483 
484  if (new_entry)
485  {
486  query << "INSERT INTO META_MSInstrument SET ";
487  query << "fid_MSExperiment='" << exp.getPersistenceId() << "',";
488  end = "";
489  }
490  else
491  {
492  query << "SELECT id FROM META_MSInstrument WHERE fid_MSExperiment='" << exp.getPersistenceId() << "'";
493  result = db_con_.executeQuery(query.str(), true);
494  parent_id = result.value(0).toInt();
495 
496  query.str("");
497  query << "UPDATE META_MSInstrument SET ";
498  end = " WHERE fid_MSExperiment='" + String(exp.getPersistenceId()) + "'";
499  }
500 
501  query << "Model='" << instrument.getModel() << "'";
502  query << ",Vendor='" << instrument.getVendor() << "'";
503  query << ",Description='" << instrument.getCustomizations() << "'";
504  query << ",IonOpticsType='" << (1u + instrument.getIonOptics()) << "'";
505 
506  query << end;
507  result = db_con_.executeQuery(query.str());
508 
509  if (new_entry)
510  {
511  parent_id = db_con_.getAutoId();
512  }
513 
514  storeMetaInfo_("META_MSInstrument", parent_id, instrument);
515 
516  deleteMetaInfo_("META_Software", "SoftwareApplicator='META_MSInstrument' AND fid_SoftwareApplicator=" + String(parent_id));
517  query.str("");
518  query << "DELETE FROM META_Software WHERE fid_SoftwareApplicator='" << parent_id << "' AND SoftwareApplicator='META_MSInstrument'";
519  result = db_con_.executeQuery(query.str());
520  query.str("");
521  query << "INSERT INTO META_Software SET ";
522  query << "fid_SoftwareApplicator='" << parent_id << "'";
523  query << ",SoftwareApplicator='META_MSInstrument'";
524  query << ",Name='" << instrument.getSoftware().getName() << "'";
525  query << ",Version='" << instrument.getSoftware().getVersion() << "'";
526  result = db_con_.executeQuery(query.str());
527 
528  UID software_id = db_con_.getAutoId();
529  storeMetaInfo_("META_Software", software_id, instrument.getSoftware());
530 
531  //----------------------------------------------------------------------------------------
532  //----------------------------------- store IONDETECTOR ----------------------------------
533  //----------------------------------------------------------------------------------------
534  const std::vector<IonDetector> & detectors = exp.getInstrument().getIonDetectors();
535  query.str("");
536 
537  deleteMetaInfo_("META_IonDetector", "fid_MSInstrument=" + String(parent_id));
538  query << "DELETE FROM META_IonDetector WHERE fid_MSInstrument='" << parent_id << "'";
539  result = db_con_.executeQuery(query.str());
540 
541  for (std::vector<IonDetector>::const_iterator detectors_it = detectors.begin(); detectors_it != detectors.end(); detectors_it++)
542  {
543  query.str("");
544  query << "INSERT INTO META_IonDetector SET ";
545  query << "fid_MSInstrument='" << parent_id << "'";
546  query << ",AcquisitionMode=" << (1u + detectors_it->getAcquisitionMode());
547  query << ",Type=" << (1u + detectors_it->getType());
548  query << ",Resolution=" << detectors_it->getResolution();
549  query << ",ADCSamplingFrequency=" << detectors_it->getADCSamplingFrequency();
550  query << ",InstrumentOrder=" << (detectors_it->getOrder());
551 
552  result = db_con_.executeQuery(query.str());
553  storeMetaInfo_("META_IonDetector", db_con_.getAutoId(), *detectors_it);
554  }
555 
556  //----------------------------------------------------------------------------------------
557  //------------------------------------- store IONSOURCE ----------------------------------
558  //----------------------------------------------------------------------------------------
559 
560  const std::vector<IonSource> & sources = exp.getInstrument().getIonSources();
561  query.str("");
562 
563  deleteMetaInfo_("META_IonSource", "fid_MSInstrument=" + String(parent_id));
564  query << "DELETE FROM META_IonSource WHERE fid_MSInstrument='" << parent_id << "'";
565  result = db_con_.executeQuery(query.str());
566 
567  for (std::vector<IonSource>::const_iterator sources_it = sources.begin(); sources_it != sources.end(); sources_it++)
568  {
569  query.str("");
570  query << "INSERT INTO META_IonSource SET ";
571  query << "fid_MSInstrument='" << parent_id << "'";
572  query << ",InletType=" << (1u + sources_it->getInletType());
573  query << ",IonizationMethod=" << (1u + sources_it->getIonizationMethod());
574  query << ",IonizationMode=" << (1u + sources_it->getPolarity());
575  query << ",InstrumentOrder=" << (sources_it->getOrder());
576 
577  result = db_con_.executeQuery(query.str());
578  storeMetaInfo_("META_IonSource", db_con_.getAutoId(), *sources_it);
579  }
580 
581  //----------------------------------------------------------------------------------------
582  //------------------------------------ store MASSANALYZER --------------------------------
583  //----------------------------------------------------------------------------------------
584 
585  const std::vector<MassAnalyzer> & analyzers = exp.getInstrument().getMassAnalyzers();
586  query.str("");
587 
588  deleteMetaInfo_("META_MassAnalyzer", "fid_MSInstrument=" + String(parent_id));
589  query << "DELETE FROM META_MassAnalyzer WHERE fid_MSInstrument='" << parent_id << "'";
590  result = db_con_.executeQuery(query.str());
591 
592  for (std::vector<MassAnalyzer>::const_iterator analyzer_it = analyzers.begin(); analyzer_it != analyzers.end(); analyzer_it++)
593  {
594  query.str("");
595  query << "INSERT INTO META_MassAnalyzer SET ";
596  query << "fid_MSInstrument='" << parent_id << "'";
597  query << ",Accuracy=" << analyzer_it->getAccuracy();
598  query << ",FinalMSExponent=" << analyzer_it->getFinalMSExponent();
599  query << ",IsolationWidth=" << analyzer_it->getIsolationWidth();
600  query << ",MagneticFieldStrength=" << analyzer_it->getMagneticFieldStrength();
601  query << ",ReflectronState=" << (1u + analyzer_it->getReflectronState());
602  query << ",Resolution=" << analyzer_it->getResolution();
603  query << ",ResolutionMethod=" << (1u + analyzer_it->getResolutionMethod());
604  query << ",ResolutionType=" << (1u + analyzer_it->getResolutionType());
605  query << ",ScanDirection=" << (1u + analyzer_it->getScanDirection());
606  query << ",ScanLaw=" << (1u + analyzer_it->getScanLaw());
607  query << ",ScanRate=" << analyzer_it->getScanRate();
608  query << ",ScanTime=" << analyzer_it->getScanTime();
609  query << ",TOFPathLength=" << analyzer_it->getTOFTotalPathLength();
610  query << ",Type=" << (1u + analyzer_it->getType());
611  query << ",InstrumentOrder=" << analyzer_it->getOrder();
612 
613  result = db_con_.executeQuery(query.str());
614  storeMetaInfo_("META_MassAnalyzer", db_con_.getAutoId(), *analyzer_it);
615  }
616 
617  //----------------------------------------------------------------------------------------
618  //------------------------------------- store SPECTRUM -----------------------------------
619  //----------------------------------------------------------------------------------------
620  for (typename ExperimentType::Iterator exp_it = exp.begin(); exp_it != exp.end(); ++exp_it)
621  {
622  query.str("");
623  new_entry = (exp_it->getPersistenceId() == 0);
624  if (new_entry)
625  {
626  query << "INSERT INTO DATA_Spectrum SET ";
627  end = "";
628  }
629  else
630  {
631  query << "UPDATE DATA_Spectrum SET ";
632  end = " WHERE id='" + String(exp_it->getPersistenceId()) + "'";
633  }
634  //FC (MSExperiment)
635  query << "fid_MSExperiment='" << exp.getPersistenceId() << "'";
636  //type
637  query << ",Type=" << (1u + exp_it->getType());
638  //RT
639  query << ",RetentionTime='" << exp_it->getRT() << "'";
640  //MS-Level
641  query << ",MSLevel='" << exp_it->getMSLevel() << "'";
642  //Description
643  query << ",Description='" << exp_it->getComment() << "'";
644  //nativeID
645  query << ",NativeID='" << exp_it->getNativeID() << "'";
646 
647  //TODO: MassType (average/monoisotopic)
648  //TODO: TIC
649 
650 
651  query << end;
652  result = db_con_.executeQuery(query.str());
653  if (new_entry)
654  {
655  exp_it->setPersistenceId(db_con_.getAutoId());
656  }
657  storeFile_("DATA_Spectrum", exp_it->getPersistenceId(), exp_it->getSourceFile());
658  meta_id = storeMetaInfo_("DATA_Spectrum", exp_it->getPersistenceId(), *exp_it);
659 
660 
661  //----------------------------------------------------------------------------------------
662  //----------------------------- store PEPTIDE IDENTIFICATIONS / HITS ---------------------
663  //----------------------------------------------------------------------------------------
664 
665  std::vector<PeptideIdentification> & pei = exp_it->getPeptideIdentifications();
666 
667  // first delete all old values, no matter whether we're updating or not
668  // do we have to delete the MetaInfo as well?
669  query.str("");
670  query << "DELETE FROM ID_PeptideIdentification WHERE fid_Spectrum='";
671  query << exp_it->getPersistenceId() << "'";
672  result = db_con_.executeQuery(query.str());
673 
674  for (std::vector<PeptideIdentification>::const_iterator pei_it = pei.begin(); pei_it != pei.end(); pei_it++)
675  {
676  query.str("");
677  query << "INSERT INTO ID_PeptideIdentification SET ";
678  query << "fid_Spectrum='" << exp_it->getPersistenceId() << "'";
679  query << ",SignificanceThreshold='" << pei_it->getSignificanceThreshold() << "'";
680  query << ",ScoreType='" << pei_it->getScoreType() << "'";
681  query << ",HigherScoreBetter='" << pei_it->isHigherScoreBetter() << "'";
682 
683  result = db_con_.executeQuery(query.str());
684  parent_id = db_con_.getAutoId();
685 
686  storeMetaInfo_("ID_PeptideIdentification", parent_id, *pei_it);
687  //needs getter and setter methods first
688  //storeFile_("ID_PeptideIdentification", parent_id, pei_it->getSourceFile());
689 
690  for (std::vector<PeptideHit>::const_iterator peh_it = pei_it->getHits().begin(); peh_it != pei_it->getHits().end(); peh_it++)
691  {
692  query.str("");
693  query << "INSERT INTO ID_PeptideHit SET ";
694  query << "fid_Identification='" << parent_id << "'";
695  query << ",Score='" << peh_it->getScore() << "'";
696  query << ",charge='" << peh_it->getCharge() << "'";
697  query << ",Sequence='" << peh_it->getSequence() << "'";
698  query << ",AABefore='" << peh_it->getAABefore() << "'";
699  query << ",AAAfter='" << peh_it->getAAAfter() << "'";
700 
701  result = db_con_.executeQuery(query.str());
702  meta_id = db_con_.getAutoId();
703 
704  storeMetaInfo_("ID_PeptideHit", meta_id, *peh_it);
705  }
706  }
707 
708  //----------------------------------------------------------------------------------------
709  //------------------------------------ store PRECURSOR -----------------------------------
710  //----------------------------------------------------------------------------------------
711 
712  // first delete all old values, no matter whether we're updating or not
713  // do we have to delete the MetaInfo as well?
714  query.str("");
715  query << "DELETE FROM DATA_Precursor WHERE fid_Spectrum='";
716  query << exp_it->getPersistenceId() << "'";
717  result = db_con_.executeQuery(query.str());
718 
719 
720  for (Size precs_it = 0; precs_it < exp_it->getPrecursors().size(); precs_it++)
721  {
722  query.str("");
723  query << "INSERT INTO DATA_Precursor SET ";
724  query << "fid_Spectrum='" + String(exp_it->getPersistenceId()) + "'";
725  //Intensity
726  query << ",Intensity='" << exp_it->getPrecursors()[precs_it].getIntensity() << "'";
727  //mz
728  query << ",WindowMz='" << exp_it->getPrecursors()[precs_it].getMZ() << "'";
729  //charge
730  query << ",Charge='" << exp_it->getPrecursors()[precs_it].getCharge() << "'";
731  //activation energy
732  query << ",ActivationEnergy='" << exp_it->getPrecursors()[precs_it].getActivationEnergy() << "'";
733  //IsolationWindowLow
734  query << ",WindowLow='" << exp_it->getPrecursors()[precs_it].getIsolationWindowLowerOffset() << "'";
735  //IsolationWindowUp
736  query << ",WindowUp='" << exp_it->getPrecursors()[precs_it].getIsolationWindowUpperOffset() << "'";
737 
738  result = db_con_.executeQuery(query.str());
739  parent_id = db_con_.getAutoId();
740  storeMetaInfo_("DATA_Precursor", parent_id, exp_it->getPrecursors()[precs_it]);
741 
742 
743  for (Size pcs_it = 0; pcs_it < exp_it->getPrecursors()[precs_it].getPossibleChargeStates().size(); ++pcs_it)
744  {
745  query.str("");
746  query << "INSERT INTO DATA_PrecursorPCS SET ";
747  query << "fid_Precursor='" + String(parent_id) + "'";
748  //PossibleChargeStates
749  query << ",PossibleChargeStates='" << (exp_it->getPrecursors()[precs_it].getPossibleChargeStates()[pcs_it]) << "'";
750  result = db_con_.executeQuery(query.str());
751  }
752 
753  for (std::set<Precursor::ActivationMethod>::iterator am_it = exp_it->getPrecursors()[precs_it].getActivationMethods().begin(); am_it != exp_it->getPrecursors()[precs_it].getActivationMethods().end(); ++am_it)
754  {
755  query.str("");
756  query << "INSERT INTO DATA_PrecursorAM SET ";
757  query << "fid_Precursor='" + String(parent_id) + "'";
758  //activation method
759  query << ",ActivationMethods=" << (1u + *(am_it));
760  result = db_con_.executeQuery(query.str());
761  }
762 
763  }
764 
765  //----------------------------------------------------------------------------------------
766  //------------------------------------- store PRODUCTS -----------------------------------
767  //----------------------------------------------------------------------------------------
768 
769  // first delete all old values, no matter whether we're updating or not
770  // do we have to delete the MetaInfo as well?
771  query.str("");
772  query << "DELETE FROM DATA_Products WHERE fid_Spectrum='";
773  query << exp_it->getPersistenceId() << "'";
774  result = db_con_.executeQuery(query.str());
775 
776  for (Size precs_it = 0; precs_it < exp_it->getProducts().size(); precs_it++)
777  {
778  query.str("");
779  query << "INSERT INTO DATA_Products SET ";
780  query << "fid_Spectrum='" + String(exp_it->getPersistenceId()) + "'";
781  //mz
782  query << ",WindowMz='" << exp_it->getProducts()[precs_it].getMZ() << "'";
783  //IsolationWindowLow
784  query << ",WindowLow='" << exp_it->getProducts()[precs_it].getIsolationWindowLowerOffset() << "'";
785  //IsolationWindowUp
786  query << ",WindowUp='" << exp_it->getProducts()[precs_it].getIsolationWindowUpperOffset() << "'";
787 
788  result = db_con_.executeQuery(query.str());
789  parent_id = db_con_.getAutoId();
790  storeMetaInfo_("DATA_Products", parent_id, exp_it->getProducts()[precs_it]);
791  }
792 
793  //----------------------------------------------------------------------------------------
794  //--------------------------------------- store PEAKS ------------------------------------
795  //----------------------------------------------------------------------------------------
796  query.str("");
797  deleteMetaInfo_("DATA_Peak", "fid_Spectrum=" + String(exp_it->getPersistenceId()));
798  result = db_con_.executeQuery("DELETE FROM DATA_Peak WHERE fid_Spectrum=" + String(exp_it->getPersistenceId()));
799  if (exp_it->size() != 0)
800  {
801  query << "INSERT INTO DATA_Peak (fid_Spectrum,Intensity,mz) VALUES ";
802  tmp = "(" + String(exp_it->getPersistenceId()) + ",'";
803  for (typename ExperimentType::SpectrumType::Iterator spec_it = exp_it->begin(); spec_it != exp_it->end(); ++spec_it)
804  {
805  //Foreign Key (Spectrum)
806  query << tmp;
807  //Intensity
808  query << spec_it->getIntensity() << "','";
809  //mz
810  query << spec_it->getPosition() << "'),";
811  }
812  // remove last ","
813  result = db_con_.executeQuery(String(query.str()).chop(1));
814  }
815  // We know that all inserted peaks have IDs beginning from last_insert_id() (= ID of first inserted entry
816  // of last insert operation), so we can insert Meta Information without actually fetching the ID
817  UID insert_id = db_con_.getAutoId();
818  for (typename ExperimentType::SpectrumType::Iterator spec_it = exp_it->begin(); spec_it != exp_it->end(); ++spec_it)
819  {
820  storeMetaInfo_("DATA_Peak", insert_id, *spec_it);
821  insert_id++;
822  }
823 
824  //----------------------------------------------------------------------------------------
825  //-------------------- store METAINFODESCRIPTION / METADATAARRAYS -----------------------
826  //----------------------------------------------------------------------------------------
827 
828  const typename ExperimentType::SpectrumType::FloatDataArrays & meta_data_arrays = exp_it->getFloatDataArrays();
829 
830  for (typename ExperimentType::SpectrumType::FloatDataArrays::const_iterator mdarrays_it = meta_data_arrays.begin(); mdarrays_it != meta_data_arrays.end(); ++mdarrays_it)
831  {
832  // first check if there is already an entry in META_MetaInfoDescription for this spectrum and this name
833  // We cannot simply delete all entries for the spectrum because this might leave unreferenced META_TNVs
834  // and META_MetaInfos in the database.
835  query.str("");
836  query << "SELECT id FROM META_MetaInfoDescription WHERE fid_Spectrum=";
837  query << exp_it->getPersistenceId();
838  query << " AND Name='" << mdarrays_it->getName() << "'";
839  result = db_con_.executeQuery(query.str());
840 
841  query.str("");
842 
843  if (result.size() > 0)
844  {
845  parent_id = result.value(0).toInt();
846  new_entry = false;
847  query << "UPDATE META_MetaInfoDescription SET ";
848  query << "Name='" + mdarrays_it->getName() + "' "; //TODO
849  end = " WHERE fid_Spectrum=" + String(exp_it->getPersistenceId());
850  end += " AND Name='" + mdarrays_it->getName() + "'";
851  }
852  else
853  {
854  new_entry = true;
855  query << "INSERT INTO META_MetaInfoDescription SET ";
856  query << "fid_Spectrum=" << exp_it->getPersistenceId() << ", ";
857  query << "Name='" << mdarrays_it->getName() << "'";
858  end = "";
859  }
860 
861  query << end;
862 
863  result = db_con_.executeQuery(query.str());
864  if (new_entry)
865  {
866  parent_id = db_con_.getAutoId();
867  }
868 
869  storeMetaInfo_("META_MetaInfoDescription", parent_id, *mdarrays_it);
870 
871  // store meta data contained in the FloatDataArrays
872  query.str("");
873  query << "DELETE FROM DATA_PeakMetaData WHERE fid_MetaInfoDescription=";
874  query << parent_id;
875  result = db_con_.executeQuery(query.str());
876 
877  query.str("");
878  query << "SELECT id FROM DATA_Peak WHERE fid_Spectrum=" << exp_it->getPersistenceId();
879  result = db_con_.executeQuery(query.str(), true);
880 
881  query.str("");
882  query << "INSERT INTO DATA_PeakMetaData (fid_Peak,fid_MetaInfoDescription,Value) VALUES ";
883  for (typename ExperimentType::SpectrumType::FloatDataArray::const_iterator meta_array_it = mdarrays_it->begin(); meta_array_it != mdarrays_it->end(); meta_array_it++)
884  {
885  if (result.isValid())
886  {
887  query << "(" << result.value(0).toInt() << "," << parent_id << "," << *meta_array_it << "),";
888  result.next();
889  }
890  else
891  {
892  break;
893  }
894  }
895  // remove last ","
896  result = db_con_.executeQuery(String(query.str()).chop(1));
897  }
898 
899 
900  //----------------------------------------------------------------------------------------
901  //------------------------------- store INSTRUMENT SETTINGS ------------------------------
902  //----------------------------------------------------------------------------------------
903 
904  const InstrumentSettings & settings = exp_it->getInstrumentSettings();
905 
906  query.str("");
907 
908  if (new_entry)
909  {
910  query << "INSERT INTO META_InstrumentSettings SET fid_Spectrum=" << exp_it->getPersistenceId() << ",";
911  end = "";
912  }
913  else
914  {
915  query << "SELECT id FROM META_InstrumentSettings WHERE fid_Spectrum='" << exp_it->getPersistenceId() << "'";
916  result = db_con_.executeQuery(query.str(), true);
917  parent_id = result.value(0).toInt();
918 
919  query.str("");
920  query << "UPDATE META_InstrumentSettings SET ";
921  end = " WHERE fid_Spectrum='" + String(exp_it->getPersistenceId()) + "'";
922  }
923 
924  query << "Polarity=" << (1u + settings.getPolarity()) << ",";
925  query << "ScanMode=" << (1u + settings.getScanMode()) << ",";
926  query << "ZoomScan=" << (settings.getZoomScan());
927  query << end;
928 
929  result = db_con_.executeQuery(query.str());
930 
931  if (new_entry)
932  parent_id = db_con_.getAutoId();
933  storeMetaInfo_("META_InstrumentSettings", parent_id, exp_it->getInstrumentSettings());
934 
935  //----------------------------------------------------------------------------------------
936  //----------------------------------- store SCANWINDOWS---- ------------------------------
937  //----------------------------------------------------------------------------------------
938 
939  //handle several scan windows
940  const std::vector<ScanWindow> & wins = settings.getScanWindows();
941 
942  // first delete all old values, no matter whether we're updating or not
943  // do we have to delete the MetaInfo as well?
944  query.str("");
945  query << "DELETE FROM META_ScanWindows WHERE fid_Spectrum='";
946  query << exp_it->getPersistenceId() << "'";
947  result = db_con_.executeQuery(query.str());
948 
949  for (Size wins_it = 0; wins_it < wins.size(); wins_it++)
950  {
951  query.str("");
952  query << "INSERT INTO META_ScanWindows SET ";
953  query << "fid_Spectrum='" + String(exp_it->getPersistenceId()) + "'";
954  query << ",MZRangeBegin=" << settings.getScanWindows()[wins_it].begin;
955  query << ",MZRangeEnd=" << settings.getScanWindows()[wins_it].end;
956  result = db_con_.executeQuery(query.str());
957  storeMetaInfo_("META_ScanWindows", parent_id, settings.getScanWindows()[wins_it]);
958  //~ parent_id = db_con_.getAutoId();
959  }
960 
961  //----------------------------------------------------------------------------------------
962  //------------------------------- store ACQUISITIONINFO ----------------------------------
963  //----------------------------------------------------------------------------------------
964 
965  const AcquisitionInfo & info = exp_it->getAcquisitionInfo();
966 
967  query.str("");
968 
969  if (new_entry)
970  {
971  query << "INSERT INTO META_AcquisitionInfo SET fid_Spectrum='" << exp_it->getPersistenceId() << "',";
972  end = "";
973  }
974  else
975  {
976  query << "SELECT id FROM META_AcquisitionInfo WHERE fid_Spectrum='" << exp_it->getPersistenceId() << "'";
977  result = db_con_.executeQuery(query.str(), true);
978  acquisition_info_id = result.value(0).toInt();
979 
980  query.str("");
981  query << "UPDATE META_AcquisitionInfo SET ";
982  end = " WHERE fid_Spectrum='" + String(exp_it->getPersistenceId()) + "'";
983  }
984 
985  query << "MethodOfCombination='" << info.getMethodOfCombination() << "'";
986  query << end;
987 
988  result = db_con_.executeQuery(query.str());
989  if (new_entry)
990  {
991  acquisition_info_id = db_con_.getAutoId();
992  }
993 
994  //----------------------------------------------------------------------------------------
995  //-----------------------------------store ACQUISITION -----------------------------------
996  //----------------------------------------------------------------------------------------
997 
998  query.str("");
999  deleteMetaInfo_("META_Acquisition", "fid_AcquisitionInfo='" + String(parent_id) + "'");
1000  query << "DELETE FROM META_Acquisition WHERE fid_AcquisitionInfo='" << parent_id << "'";
1001  result = db_con_.executeQuery(query.str());
1002 
1003  for (std::vector<Acquisition>::const_iterator info_it = info.begin(); info_it != info.end(); info_it++)
1004  {
1005  query.str("");
1006  query << "INSERT INTO META_Acquisition SET fid_AcquisitionInfo='" << acquisition_info_id << "',";
1007  query << "Number='" << info_it->getIdentifier() << "'";
1008 
1009  result = db_con_.executeQuery(query.str());
1010  parent_id = db_con_.getAutoId();
1011 
1012  storeMetaInfo_("META_Acquisition", parent_id, *info_it);
1013  }
1014 
1015 
1016  //----------------------------------------------------------------------------------------
1017  //-------------------------------------store DATAPROCESSING ----------------------------------
1018  //----------------------------------------------------------------------------------------
1019  const std::vector<DataProcessing> & processings = exp_it->getDataProcessing();
1020 
1021  deleteMetaInfo_("META_DataProcessing", "fid_Spectrum=" + String(exp_it->getPersistenceId()));
1022  query.str("");
1023  query << "DELETE FROM META_DataProcessing WHERE fid_Spectrum='" << exp_it->getPersistenceId() << "'";
1024  result = db_con_.executeQuery(query.str());
1025 
1026  for (std::vector<DataProcessing>::const_iterator processings_it = processings.begin(); processings_it != processings.end(); processings_it++)
1027  {
1028  query.str("");
1029  query << "INSERT INTO META_DataProcessing SET ";
1030  query << "fid_Spectrum='" << exp_it->getPersistenceId() << "'";
1031  query << ",CompletionTime='" << processings_it->getCompletionTime().get() << "'";
1032 
1033  result = db_con_.executeQuery(query.str());
1034 
1035  UID dataprocessing_id = db_con_.getAutoId();
1036  storeMetaInfo_("META_DataProcessing", dataprocessing_id, *processings_it);
1037 
1038  deleteMetaInfo_("META_Software", "SoftwareApplicator='META_DataProcessing' AND fid_SoftwareApplicator=" + String(dataprocessing_id));
1039  query.str("");
1040  query << "DELETE FROM META_Software WHERE fid_SoftwareApplicator='" << dataprocessing_id << "' AND SoftwareApplicator='META_DataProcessing'";
1041  result = db_con_.executeQuery(query.str());
1042  query.str("");
1043  query << "INSERT INTO META_Software SET ";
1044  query << "fid_SoftwareApplicator='" << dataprocessing_id << "'";
1045  query << ",SoftwareApplicator='META_DataProcessing'";
1046  query << ",Name='" << processings_it->getSoftware().getName() << "'";
1047  query << ",Version='" << processings_it->getSoftware().getVersion() << "'";
1048  result = db_con_.executeQuery(query.str());
1049 
1050  UID software_id = db_con_.getAutoId();
1051  storeMetaInfo_("META_Software", software_id, processings_it->getSoftware());
1052 
1053  //----------------------------------------------------------------------------------------
1054  //------------------------------------ store PROCESSINGACTIONS ---------------------------
1055  //----------------------------------------------------------------------------------------
1056  for (std::set<DataProcessing::ProcessingAction>::const_iterator acts_it = processings_it->getProcessingActions().begin(); acts_it != processings_it->getProcessingActions().end(); acts_it++)
1057  {
1058  query.str("");
1059  query << "INSERT INTO META_ProcessingActions SET ";
1060  query << "ProcessingActionType='" << (1u + (*acts_it)) << "'";
1061  query << ",fid_DataProcessing='" << dataprocessing_id << "'";
1062  result = db_con_.executeQuery(query.str());
1063  }
1064  }
1065 
1066  } //iteration over experiments' spectra ends here
1067  }
1068 
1069  template <class ExperimentType>
1070  void DBAdapter::loadExperiment(UID id, ExperimentType & exp)
1071  {
1072  //----------------------------------------------------------------------------------------
1073  //------------------------------- CHECK DB VERSION ---------------------------------------
1074  //----------------------------------------------------------------------------------------
1075  if (!checkDBVersion(true))
1076  return;
1077 
1078  std::stringstream query; // query to build
1079  String tmp; // temporary data
1080  QSqlQuery result, sub_result; // place to store the query results in
1081  UID parent_id; // holds ID of parent data set
1082 
1083  //----------------------------------------------------------------------------------------
1084  //------------------------------- load EXPERIMENTs ---------------------------------------
1085  //----------------------------------------------------------------------------------------
1086 
1087  query << "SELECT Date,fid_MetaInfo,Description,FractionIdentifier FROM META_MSExperiment WHERE id='" << id << "'";
1088  result = db_con_.executeQuery(query.str(), true);
1089 
1090  //Experiment meta info
1091  try
1092  {
1093  DateTime d;
1094  d.set(result.value(0).toDateTime().toString(Qt::ISODate));
1095  exp.setDateTime(d);
1096  }
1097  catch (Exception::ParseError &)
1098  {
1099  //no nothing, the date is simply unset
1100  }
1101  exp.setComment(result.value(2).toString());
1102  exp.setFractionIdentifier(result.value(3).toString());
1103  loadMetaInfo_(result.value(1).toInt(), exp);
1104 
1105  std::vector<ProteinIdentification> pi_vec;
1106  std::vector<ProteinHit> ph_vec;
1108  ProteinHit ph;
1109 
1110  query.str("");
1111  query << "SELECT id, SearchEngine, SearchEngineVersion, Date, ScoreType, HigherScoreBetter, SignificanceThreshold, fid_MetaInfo, fid_File FROM ID_ProteinIdentification WHERE fid_MSExperiment='"<< id << "'";
1112  result = db_con_.executeQuery(query.str());
1113  while (result.next())
1114  {
1115  parent_id = result.value(0).toInt();
1116  pi.setSearchEngine(result.value(1).toString());
1117  pi.setSearchEngineVersion(result.value(2).toString());
1118  pi.setDateTime(DateTime(result.value(3).toDateTime()));
1119  pi.setScoreType(result.value(4).toString());
1120  pi.setHigherScoreBetter(result.value(5).toInt());
1121  pi.setSignificanceThreshold(result.value(6).toDouble());
1122 
1123  loadMetaInfo_(result.value(7).toInt(), pi);
1124  //needs getter and setter methods first
1125  //loadFile_(result.value(8).toInt(), pi.getSourceFile());
1126 
1127  //load searchparameters
1128  query.str("");
1129  query << "SELECT id,DB,DBVersion,Taxonomy,Charges,MassType-1,Enzyme-1,MissedCleavages,PeakMassTolerance,PrecursorTolerance,fid_MetaInfo FROM ID_SearchParameters WHERE fid_ProteinIdentification='" << parent_id << "'";
1130  sub_result = db_con_.executeQuery(query.str(), true);
1131 
1132  UID sub_id = sub_result.value(0).toInt();
1134  params.db = sub_result.value(1).toString();
1135  params.db_version = sub_result.value(2).toString();
1136  params.taxonomy = sub_result.value(3).toString();
1137  params.charges = sub_result.value(4).toString();
1138  params.mass_type = ((ProteinIdentification::PeakMassType)sub_result.value(5).toInt());
1139  params.enzyme = ((ProteinIdentification::DigestionEnzyme)sub_result.value(6).toInt());
1140  params.missed_cleavages = sub_result.value(7).toInt();
1141  params.peak_mass_tolerance = sub_result.value(8).toDouble();
1142  params.precursor_tolerance = sub_result.value(9).toDouble();
1143  loadMetaInfo_(sub_result.value(10).toInt(), params);
1144 
1145  //fill modifications
1146  query.str("");
1147  query << "SELECT name FROM ID_VariableModifications WHERE fid_SearchParameters='" << sub_id << "'";
1148  sub_result = db_con_.executeQuery(query.str());
1149  while (sub_result.next())
1150  {
1151  params.variable_modifications.push_back(sub_result.value(0).toString());
1152  }
1153  query.str("");
1154  query << "SELECT name FROM ID_FixedModifications WHERE fid_SearchParameters='" << sub_id << "'";
1155  sub_result = db_con_.executeQuery(query.str());
1156  while (sub_result.next())
1157  {
1158  params.fixed_modifications.push_back(sub_result.value(0).toString());
1159  }
1160  pi.setSearchParameters(params);
1161 
1162 
1163  query.str("");
1164  query << "SELECT Score, Accession, Sequence, Rank, fid_MetaInfo FROM ID_ProteinHit WHERE fid_ProteinIdentification='" << parent_id << "'";
1165  sub_result = db_con_.executeQuery(query.str());
1166  while (sub_result.next())
1167  {
1168  ph.setScore(sub_result.value(0).toDouble());
1169  ph.setAccession(sub_result.value(1).toString());
1170  ph.setSequence(sub_result.value(2).toString());
1171  ph.setRank(sub_result.value(3).toInt());
1172 
1173  loadMetaInfo_(sub_result.value(4).toInt(), ph);
1174 
1175  ph_vec.push_back(ph);
1176  }
1177 
1178  pi.setHits(ph_vec);
1179 
1180  pi_vec.push_back(pi);
1181  }
1182 
1183  exp.setProteinIdentifications(pi_vec);
1184 
1185  // Sample
1186  Sample sample;
1187  query.str("");
1188  // finding root of recursive sample tree
1189  query << "SELECT id FROM META_Sample WHERE fid_MSExperiment='" << id << "' AND fid_Sample IS NULL";
1190  result = db_con_.executeQuery(query.str(), true);
1191  loadSample_(result.value(0).toInt(), sample);
1192  exp.setSample(sample);
1193 
1194  // ContactPerson
1195  ContactPerson contact;
1196  query.str("");
1197  query << "SELECT PreName,LastName,Affiliation,Email,Comment,fid_MetaInfo FROM META_ContactPerson WHERE fid_MSExperiment='" << id << "'";
1198  result = db_con_.executeQuery(query.str());
1199  while (result.next())
1200  {
1201  contact.setFirstName(result.value(0).toString());
1202  contact.setLastName(result.value(1).toString());
1203  contact.setInstitution(result.value(2).toString());
1204  contact.setEmail(result.value(3).toString());
1205  contact.setContactInfo(result.value(4).toString());
1206  loadMetaInfo_(result.value(5).toInt(), contact);
1207  exp.getContacts().push_back(contact);
1208  }
1209 
1210  // HPLC
1211  query.str("");
1212  query << "SELECT id,InstrumentName,ColumnName,Description,Flux,Pressure,Temperature FROM META_HPLC WHERE fid_MSExperiment='" << id << "'";
1213  result = db_con_.executeQuery(query.str(), true);
1214  parent_id = result.value(0).toInt();
1215  exp.getHPLC().setInstrument(result.value(1).toString());
1216  exp.getHPLC().setColumn(result.value(2).toString());
1217  exp.getHPLC().setComment(result.value(3).toString());
1218  exp.getHPLC().setFlux(result.value(4).toInt());
1219  exp.getHPLC().setPressure(result.value(5).toInt());
1220  exp.getHPLC().setTemperature(result.value(6).toInt());
1221 
1222  // Gradient*
1223  // I tried taking the big query apart in order to skip the double join, but this leads to
1224  // the problem of saving all requested keys in a vector in order to request the percentages (complex).
1225  // I'll still preserve the code in order to optimize in the future. Maybe I was just being blind. ;-)
1226 
1227  String last_name;
1228  bool timepoints_done = false;
1229  query.str("");
1230  /*
1231  query << "SELECT id,Name FROM META_GradientEluent WHERE fid_HPLC=" << parent_id;
1232  result = db_con_.executeQuery(query.str());
1233  while(result.next())
1234  {
1235  exp.getHPLC().getGradient().addEluent(result.value(0).toString());
1236  }
1237 
1238  query.str("");
1239  query << "SELECT id,Time FROM META_GradientTime WHERE fid_HPLC=" << parent_id;
1240  result = db_con_.executeQuery(query.str());
1241  while(result.next())
1242  {
1243  exp.getHPLC().getGradient().addTimepoint(result.value(0).toInt());
1244  }
1245  */
1246 
1247  query << "SELECT Name,Time,Percentage FROM META_GradientEluent, META_GradientTime, META_GradientPercentage WHERE META_GradientEluent.fid_HPLC=" << parent_id << " AND fid_GradientEluent=META_GradientEluent.id AND fid_GradientTime=META_GradientTime.id";
1248  result = db_con_.executeQuery(query.str(), true);
1249  if (result.isValid())
1250  {
1251  last_name = result.value(0).toString();
1252  exp.getHPLC().getGradient().addEluent(last_name);
1253  }
1254 
1255  while (result.isValid())
1256  {
1257  if (result.value(0).toString() != last_name.toQString())
1258  {
1259  exp.getHPLC().getGradient().addEluent(result.value(0).toString());
1260  timepoints_done = true;
1261  }
1262 
1263  if (timepoints_done == false)
1264  {
1265  exp.getHPLC().getGradient().addTimepoint(result.value(1).toInt());
1266  }
1267 
1268  exp.getHPLC().getGradient().setPercentage(result.value(0).toString(), result.value(1).toInt(), result.value(2).toInt());
1269 
1270  last_name = result.value(0).toString();
1271  result.next();
1272  }
1273 
1274  //----------------------------------------------------------------------------------------
1275  //------------------------------- load INSTRUMENT ----------------------------------------
1276  //----------------------------------------------------------------------------------------
1277 
1278  query.str("");
1279  query << "SELECT id,Model,Vendor,Description,IonOpticsType-1,fid_MetaInfo FROM META_MSInstrument WHERE fid_MSExperiment='" << id << "'";
1280  result = db_con_.executeQuery(query.str(), true);
1281 
1282  parent_id = result.value(0).toInt();
1283  exp.getInstrument().setModel(result.value(1).toString());
1284  exp.getInstrument().setVendor(result.value(2).toString());
1285  exp.getInstrument().setCustomizations(result.value(3).toString());
1286  exp.getInstrument().setIonOptics((Instrument::IonOpticsType) result.value(4).toInt());
1287  loadMetaInfo_(result.value(5).toInt(), exp.getInstrument());
1288 
1289  query.str("");
1290  query << "SELECT Name,Version,fid_MetaInfo, id FROM META_Software WHERE fid_SoftwareApplicator='" << result.value(0).toInt() << "' AND SoftwareApplicator = 'META_MSInstrument'";
1291  result = db_con_.executeQuery(query.str(), true);
1292  if (result.isValid())
1293  {
1294  Software sw;
1295  sw.setName(result.value(0).toString());
1296  sw.setVersion(result.value(1).toString());
1297  loadMetaInfo_(result.value(2).toInt(), sw);
1298 
1299  exp.getInstrument().setSoftware(sw);
1300  }
1301 
1302  //----------------------------------------------------------------------------------------
1303  //------------------------------- load IONDETECTORS --------------------------------------
1304  //----------------------------------------------------------------------------------------
1305  std::vector<IonDetector> detectors;
1306  query.str("");
1307  query << "SELECT AcquisitionMode-1,Type-1,Resolution,ADCSamplingFrequency,InstrumentOrder,fid_MetaInfo FROM META_IonDetector WHERE fid_MSInstrument='" << parent_id << "'";
1308  result = db_con_.executeQuery(query.str());
1309  while (result.next())
1310  {
1311  IonDetector detector;
1312  detector.setAcquisitionMode((IonDetector::AcquisitionMode) result.value(0).toInt());
1313  detector.setType((IonDetector::Type) result.value(1).toInt());
1314  detector.setResolution(result.value(2).toDouble());
1315  detector.setADCSamplingFrequency(result.value(3).toDouble());
1316  detector.setOrder(result.value(4).toInt());
1317  loadMetaInfo_(result.value(5).toInt(), detector);
1318 
1319  detectors.push_back(detector);
1320  }
1321  exp.getInstrument().setIonDetectors(detectors);
1322 
1323  //----------------------------------------------------------------------------------------
1324  //------------------------------- load IONSOURCES --------------------------------------
1325  //----------------------------------------------------------------------------------------
1326  std::vector<IonSource> sources;
1327  query.str("");
1328  query << "SELECT InletType-1,IonizationMethod-1,IonizationMode-1,InstrumentOrder,fid_MetaInfo FROM META_IonSource WHERE fid_MSInstrument='" << parent_id << "'";
1329  result = db_con_.executeQuery(query.str());
1330  while (result.next())
1331  {
1332  IonSource source;
1333  source.setInletType((IonSource::InletType) result.value(0).toInt());
1334  source.setIonizationMethod((IonSource::IonizationMethod) result.value(1).toInt());
1335  source.setPolarity((IonSource::Polarity)(Int) result.value(2).toDouble());
1336  source.setOrder(result.value(3).toInt());
1337  loadMetaInfo_(result.value(4).toInt(), source);
1338 
1339  sources.push_back(source);
1340  }
1341  exp.getInstrument().setIonSources(sources);
1342 
1343  //----------------------------------------------------------------------------------------
1344  //------------------------------- load MASSANALYZERS ------------------------------------
1345  //----------------------------------------------------------------------------------------
1346  std::vector<MassAnalyzer> analyzers;
1347  query.str("");
1348  query << "SELECT Accuracy,FinalMSExponent,IsolationWidth,MagneticFieldStrength,ReflectronState-1,Resolution,ResolutionMethod-1,ResolutionType-1,ScanDirection-1,ScanLaw-1,ScanRate,ScanTime,TOFPathLength,Type-1,InstrumentOrder,fid_MetaInfo FROM META_MassAnalyzer WHERE fid_MSInstrument='" << parent_id << "'";
1349  result = db_con_.executeQuery(query.str());
1350  while (result.next())
1351  {
1352  MassAnalyzer analyzer;
1353  analyzer.setAccuracy(result.value(0).toDouble());
1354  analyzer.setFinalMSExponent(result.value(1).toInt());
1355  analyzer.setIsolationWidth(result.value(2).toDouble());
1356  analyzer.setMagneticFieldStrength(result.value(3).toDouble());
1357  analyzer.setReflectronState((MassAnalyzer::ReflectronState) result.value(4).toInt());
1358  analyzer.setResolution(result.value(5).toDouble());
1359  analyzer.setResolutionMethod((MassAnalyzer::ResolutionMethod) result.value(6).toInt());
1360  analyzer.setResolutionType((MassAnalyzer::ResolutionType) result.value(7).toInt());
1361  analyzer.setScanDirection((MassAnalyzer::ScanDirection) result.value(8).toInt());
1362  analyzer.setScanLaw((MassAnalyzer::ScanLaw) result.value(9).toInt());
1363  analyzer.setScanRate(result.value(10).toDouble());
1364  analyzer.setScanTime(result.value(11).toDouble());
1365  analyzer.setTOFTotalPathLength(result.value(12).toDouble());
1366  analyzer.setType((MassAnalyzer::AnalyzerType) result.value(13).toInt());
1367  analyzer.setOrder(result.value(14).toInt());
1368  loadMetaInfo_(result.value(15).toInt(), analyzer);
1369 
1370  analyzers.push_back(analyzer);
1371  }
1372  exp.getInstrument().setMassAnalyzers(analyzers);
1373 
1374  //id
1375  exp.setPersistenceId(id);
1376 
1377  // if we don't have to load the spectra, we're already done
1378  if (options_.getMetadataOnly())
1379  {
1380  return;
1381  }
1382 
1383  //----------------------------------------------------------------------------------------
1384  //----------------------------------- load SPECTRAS --------------------------------------
1385  //----------------------------------------------------------------------------------------
1386  query.str("");
1387  query << "SELECT id FROM DATA_Spectrum WHERE fid_MSExperiment=" << id;
1388  if (options_.hasRTRange())
1389  {
1390  query << " AND RetentionTime > " << options_.getRTRange().minPosition() << " AND RetentionTime < " << options_.getRTRange().maxPosition();
1391  }
1392  if (options_.hasMSLevels())
1393  {
1394  const std::vector<int> & levels = options_.getMSLevels();
1395  query << " AND (";
1396  for (std::vector<int>::const_iterator it = levels.begin(); it != levels.end(); it++)
1397  {
1398  query << "MSLevel=" << *it;
1399  if (it + 1 != levels.end())
1400  {
1401  query << " OR ";
1402  }
1403  }
1404  query << ")";
1405  }
1406  query << " ORDER BY id ASC";
1407 
1408  result = db_con_.executeQuery(query.str());
1409  exp.resize(result.size());
1410  UInt i = 0;
1411  while (result.next())
1412  {
1413  loadSpectrum(result.value(0).toInt(), exp[i]);
1414  ++i;
1415  }
1416 
1417  }
1418 
1419  template <class SpectrumType>
1420  void DBAdapter::loadSpectrum(UID id, SpectrumType & spec)
1421  {
1422 
1423  //----------------------------------------------------------------------------------------
1424  //------------------------------- CHECK DB VERSION ---------------------------------------
1425  //----------------------------------------------------------------------------------------
1426  if (!checkDBVersion(true))
1427  return;
1428 
1429  spec = SpectrumType();
1430 
1431  std::stringstream query; // query to build
1432  QSqlQuery result, sub_result; // place to store the query results in
1433  InstrumentSettings settings; // stores settings that are read from DB
1434  UID parent_id; // stores parent_id of Acquisition
1435 
1436  query << "SELECT Type-1,NativeID, RetentionTime,MSLevel,Description,fid_MetaInfo,fid_File FROM DATA_Spectrum WHERE id='" << id << "'";
1437  result = db_con_.executeQuery(query.str(), true);
1438 
1439  //Spectrum meta info
1440  spec.setType((SpectrumSettings::SpectrumType)(result.value(0).toInt()));
1441  spec.setNativeID(result.value(1).toString());
1442  spec.setRT(result.value(2).toDouble());
1443  spec.setMSLevel(result.value(3).toInt());
1444  spec.setComment(result.value(4).toString());
1445  loadMetaInfo_(result.value(5).toInt(), spec);
1446  loadFile_(result.value(6).toInt(), spec.getSourceFile());
1447 
1448  //----------------------------------------------------------------------------------------
1449  //------------------------------- load INSTRUMENTSETTINGS --------------------------------
1450  //----------------------------------------------------------------------------------------
1451  query.str("");
1452  query << "SELECT Polarity-1, ScanMode-1, ZoomScan, fid_MetaInfo FROM META_InstrumentSettings WHERE fid_Spectrum=" << id;
1453  result = db_con_.executeQuery(query.str(), true);
1454 
1455  settings.setPolarity((IonSource::Polarity) (result.value(0).toInt()));
1456  settings.setScanMode((InstrumentSettings::ScanMode) (result.value(1).toInt()));
1457  settings.setZoomScan(result.value(2).toBool());
1458  spec.setInstrumentSettings(settings);
1459  loadMetaInfo_(result.value(3).toInt(), spec.getInstrumentSettings());
1460 
1461  //----------------------------------------------------------------------------------------
1462  //------------------------------- load SCANWINDOWS --------------------------------------
1463  //----------------------------------------------------------------------------------------
1464  query.str("");
1465  query << "SELECT MZRangeBegin,MZRangeEnd,fid_MetaInfo FROM META_ScanWindows WHERE fid_Spectrum=" << id;
1466  result = db_con_.executeQuery(query.str());
1467  while (result.next())
1468  {
1469  ScanWindow window;
1470  window.begin = result.value(0).toDouble();
1471  window.end = result.value(1).toDouble();
1472  loadMetaInfo_(result.value(2).toInt(), window);
1473  spec.getInstrumentSettings().getScanWindows().push_back(window);
1474  }
1475 
1476  //----------------------------------------------------------------------------------------
1477  //----------------- load PEPTIDEIDENTIFICATION / PEPTIDEHITS ----------------------------
1478  //----------------------------------------------------------------------------------------
1479 
1480 
1481  std::vector<PeptideIdentification> pei_vec;
1482  std::vector<PeptideHit> peh_vec;
1484  PeptideHit peh;
1485 
1486  query.str("");
1487  query << "SELECT id, SignificanceThreshold, ScoreType, HigherScoreBetter, fid_MetaInfo, fid_File FROM ID_PeptideIdentification WHERE fid_Spectrum='" << id << "'";
1488 
1489  result = db_con_.executeQuery(query.str());
1490  while (result.next())
1491  {
1492  parent_id = result.value(0).toInt();
1493  pei.setSignificanceThreshold(result.value(1).toDouble());
1494  pei.setScoreType(result.value(2).toString());
1495  pei.setHigherScoreBetter(result.value(3).toInt());
1496 
1497  loadMetaInfo_(result.value(4).toInt(), pei);
1498  //needs getter and setter methods first
1499  //loadFile_(result.value(5).toInt(), pei.getSourceFile());
1500 
1501  query.str("");
1502  query << "SELECT Score, Sequence, Charge, AABefore, AAAfter, fid_MetaInfo FROM ID_PeptideHit WHERE fid_Identification='" << parent_id << "'";
1503  sub_result = db_con_.executeQuery(query.str());
1504 
1505  while (sub_result.next())
1506  {
1507  peh.setScore(sub_result.value(0).toDouble());
1508  peh.setSequence(String(sub_result.value(1).toString()));
1509  peh.setCharge(sub_result.value(2).toInt());
1510  peh.setAABefore(sub_result.value(3).toString().toStdString()[0]);
1511  peh.setAAAfter(sub_result.value(4).toString().toStdString()[0]);
1512 
1513  loadMetaInfo_(sub_result.value(5).toInt(), peh);
1514 
1515  peh_vec.push_back(peh);
1516  }
1517 
1518  pei.setHits(peh_vec);
1519 
1520  pei_vec.push_back(pei);
1521  }
1522 
1523  spec.setPeptideIdentifications(pei_vec);
1524 
1525  //----------------------------------------------------------------------------------------
1526  //------------------------------- load AQUISITIONINFO --------------------------------------
1527  //----------------------------------------------------------------------------------------
1528 
1529  query.str("");
1530  query << "SELECT id, MethodOfCombination FROM META_AcquisitionInfo WHERE fid_Spectrum=" << id;
1531  result = db_con_.executeQuery(query.str(), true);
1532 
1533  spec.getAcquisitionInfo().setMethodOfCombination(result.value(1).toString());
1534  parent_id = result.value(0).toInt();
1535 
1536  //----------------------------------------------------------------------------------------
1537  //------------------------------- load AQUISITION ----------------------------------------
1538  //----------------------------------------------------------------------------------------
1539  query.str("");
1540  query << "SELECT Number,fid_MetaInfo FROM META_Acquisition WHERE fid_AcquisitionInfo='" << parent_id << "' ORDER BY id ASC";
1541  result = db_con_.executeQuery(query.str());
1542 
1543  while (result.next())
1544  {
1545  Acquisition acquisition;
1546  acquisition.setIdentifier(result.value(0).toString());
1547  loadMetaInfo_(result.value(1).toInt(), acquisition);
1548  spec.getAcquisitionInfo().push_back(acquisition);
1549  }
1550 
1551  //----------------------------------------------------------------------------------------
1552  //------------------------------- load DATAPROCESSINGS -----------------------------------
1553  //----------------------------------------------------------------------------------------
1554  query.str("");
1555  query << "SELECT CompletionTime,fid_MetaInfo, id FROM META_DataProcessing WHERE fid_Spectrum='" << id << "'";
1556  result = db_con_.executeQuery(query.str());
1557 
1558  while (result.next())
1559  {
1560  DataProcessing processings;
1561 
1562  try
1563  {
1564  DateTime d;
1565  d.set(result.value(0).toDateTime().toString(Qt::ISODate));
1566  processings.setCompletionTime(d);
1567  }
1568  catch (Exception::ParseError &)
1569  {
1570  //no nothing, the date is simply unset
1571  }
1572 
1573  loadMetaInfo_(result.value(1).toInt(), processings);
1574 
1575  query.str("");
1576  query << "SELECT ProcessingActionType-1 FROM META_ProcessingActions WHERE fid_DataProcessing='" << result.value(2).toInt() << "'";
1577  sub_result = db_con_.executeQuery(query.str());
1578  while (sub_result.next())
1579  {
1580  processings.getProcessingActions().insert((DataProcessing::ProcessingAction)sub_result.value(0).toInt());
1581  }
1582 
1583  query.str("");
1584  query << "SELECT Name,Version,fid_MetaInfo, id FROM META_Software WHERE fid_SoftwareApplicator='" << result.value(2).toInt() << "' AND SoftwareApplicator = 'META_DataProcessing'";
1585  sub_result = db_con_.executeQuery(query.str(), true);
1586  if (sub_result.isValid())
1587  {
1588  Software sw;
1589  sw.setName(sub_result.value(0).toString());
1590  sw.setVersion(sub_result.value(1).toString());
1591  loadMetaInfo_(sub_result.value(2).toInt(), sw);
1592 
1593  processings.setSoftware(sw);
1594  }
1595 
1596  spec.getDataProcessing().push_back(processings);
1597  }
1598 
1599  //----------------------------------------------------------------------------------------
1600  //----------------- load METAINFODESCRIPTION/METADATAARRAYS ------------------------------
1601  //----------------------------------------------------------------------------------------
1602 
1603  query.str("");
1604  query << "SELECT Name, fid_MetaInfo FROM META_MetaInfoDescription WHERE fid_Spectrum=" << id;
1605  result = db_con_.executeQuery(query.str());
1606 
1607  while (result.next())
1608  {
1609  typename SpectrumType::FloatDataArray meta_array;
1610  meta_array.setName(result.value(0).toString());
1611  loadMetaInfo_(result.value(1).toInt(), meta_array);
1612 
1613  spec.getFloatDataArrays().push_back(meta_array);
1614  }
1615 
1616  //----------------------------------------------------------------------------------------
1617  //------------------------------- load PRECURSOR ----------------------------------------
1618  //----------------------------------------------------------------------------------------
1619  if (spec.getMSLevel() > 1)
1620  {
1621  query.str("");
1622  query << "SELECT WindowMz,Intensity,Charge,ActivationEnergy,WindowLow,WindowUp,fid_MetaInfo,id FROM DATA_Precursor WHERE fid_Spectrum='" << id << "' HAVING Intensity IS NOT NULL";
1623  result = db_con_.executeQuery(query.str());
1624  spec.getPrecursors().resize(result.size());
1625  UInt res = 0;
1626  while (result.next())
1627  {
1628  spec.getPrecursors()[res].setMZ(result.value(0).toDouble());
1629  spec.getPrecursors()[res].setIntensity(result.value(1).toDouble());
1630  spec.getPrecursors()[res].setCharge(result.value(2).toInt());
1631  spec.getPrecursors()[res].setActivationEnergy(result.value(3).toDouble());
1632  spec.getPrecursors()[res].setIsolationWindowLowerOffset(result.value(4).toDouble());
1633  spec.getPrecursors()[res].setIsolationWindowUpperOffset(result.value(5).toDouble());
1634  loadMetaInfo_(result.value(6).toInt(), spec.getPrecursors()[res]);
1635 
1636  UID prec_id = result.value(7).toInt();
1637  query.str("");
1638  query << "SELECT PossibleChargeStates FROM DATA_PrecursorPCS WHERE fid_Precursor='" << prec_id << "' HAVING PossibleChargeStates IS NOT NULL";
1639  QSqlQuery subresult = db_con_.executeQuery(query.str());
1640  while (subresult.next())
1641  {
1642  spec.getPrecursors()[res].getPossibleChargeStates().push_back(subresult.value(0).toInt());
1643  }
1644 
1645  query.str("");
1646  query << "SELECT ActivationMethods-1 FROM DATA_PrecursorAM WHERE fid_Precursor='" << prec_id << "'";
1647  subresult = db_con_.executeQuery(query.str());
1648  std::set<Precursor::ActivationMethod> tmp_set;
1649  while (subresult.next())
1650  {
1651  tmp_set.insert((Precursor::ActivationMethod)(subresult.value(0).toInt()));
1652  }
1653  spec.getPrecursors()[res].setActivationMethods(tmp_set);
1654  ++res;
1655  }
1656  }
1657 
1658  //----------------------------------------------------------------------------------------
1659  //------------------------------ load PRODUCTS ------------------------------------------
1660  //----------------------------------------------------------------------------------------
1661  query.str("");
1662  query << "SELECT WindowMz,WindowLow,WindowUp,fid_MetaInfo FROM DATA_Products WHERE fid_Spectrum='" << id << "'";
1663  result = db_con_.executeQuery(query.str());
1664  spec.getProducts().resize(result.size());
1665  UInt res = 0;
1666  while (result.next())
1667  {
1668  spec.getProducts()[res].setMZ(result.value(0).toDouble());
1669  spec.getProducts()[res].setIsolationWindowLowerOffset(result.value(1).toDouble());
1670  spec.getProducts()[res].setIsolationWindowUpperOffset(result.value(2).toDouble());
1671  loadMetaInfo_(result.value(3).toInt(), spec.getProducts()[res]);
1672  ++res;
1673  }
1674 
1675  //----------------------------------------------------------------------------------------
1676  //--------------------------- load PEAKS/METADATAARRAYS ----------------------------------
1677  //----------------------------------------------------------------------------------------
1678 
1679  query.str("");
1680  query << "SELECT mz,Intensity,fid_MetaInfo,id FROM DATA_Peak WHERE fid_Spectrum='" << id << "' ";
1681  if (options_.hasMZRange())
1682  {
1683  query << " AND mz > " << options_.getMZRange().minPosition() << " AND mz < " << options_.getMZRange().maxPosition();
1684  }
1686  {
1687  query << " AND Intensity > " << options_.getIntensityRange().minPosition() << " AND Intensity < " << options_.getIntensityRange().maxPosition();
1688  }
1689  query << " ORDER BY mz ASC";
1690  result = db_con_.executeQuery(query.str());
1691 
1692  while (result.next())
1693  {
1694  typename SpectrumType::PeakType p;
1695  p.setPosition(result.value(0).toDouble());
1696  p.setIntensity(result.value(1).toDouble());
1697  loadMetaInfo_(result.value(2).toInt(), p);
1698  spec.push_back(p);
1699  for (typename SpectrumType::FloatDataArrays::iterator mdarrays_it = spec.getFloatDataArrays().begin(); mdarrays_it != spec.getFloatDataArrays().end(); mdarrays_it++)
1700  {
1701  query.str("");
1702  query << "SELECT id FROM META_MetaInfoDescription WHERE Name='";
1703  query << mdarrays_it->getName() << "' AND fid_Spectrum=" << id;
1704  sub_result = db_con_.executeQuery(query.str(), true);
1705  query.str("");
1706  query << "SELECT Value FROM DATA_PeakMetaData WHERE fid_Peak=";
1707  query << result.value(3).toInt() << " AND fid_MetaInfoDescription=" << sub_result.value(0).toInt();
1708  sub_result = db_con_.executeQuery(query.str(), true);
1709  mdarrays_it->push_back(sub_result.value(0).toDouble());
1710  }
1711  }
1712 
1713  //id
1714  spec.setPersistenceId(id);
1715  }
1716 
1717 }
1718 
1719 #endif
Representation of a protein identification run.
Definition: ProteinIdentification.h:61
Descripton of the applied preprocessing steps.
Definition: DataProcessing.h:51
const String & getCustomizations() const
returns a description of customizations
void setSearchEngine(const String &search_engine)
Sets the search engine type.
PeakFileOptions options_
Definition: DBAdapter.h:167
Description of a MS instrument.
Definition: Instrument.h:64
String db
The used database.
Definition: ProteinIdentification.h:118
bool hasMZRange() const
returns true if an MZ range has been set
void loadSpectrum(UID id, SpectrumType &spec)
Reads a MSSpectrum.
Definition: DBAdapter.h:1420
const Software & getSoftware() const
returns a const reference to the instrument software
QSqlQuery executeQuery(const String &query, bool first=false)
Executes a query and returns the result.
void setAcquisitionMode(AcquisitionMode acquisition_mode)
sets the acquisition mode
Description of the settings a MS Instrument was run with.
Definition: InstrumentSettings.h:48
const String & getInstrument() const
returns a const reference to the instument name
A more convenient string class.
Definition: String.h:56
Precursor meta information.
Definition: Precursor.h:56
void setScore(DoubleReal score)
sets the score of the peptide hit
void setHits(const std::vector< ProteinHit > &hits)
Sets the protein hits.
void setScore(const DoubleReal score)
sets the score of the protein hit
void loadMetaInfo_(UID id, MetaInfoInterface &info)
Loads MetaInfo data from database.
void setContactInfo(const String &contact_info)
sets miscellaneous info about the contact person
void setZoomScan(bool zoom_scan)
sets if this scan is a zoom (enhanced resolution) scan
bool hasIntensityRange() const
returns true if an intensity range has been set
AcquisitionMode
Acquisition mode.
Definition: IonDetector.h:82
void set(UInt month, UInt day, UInt year, UInt hour, UInt minute, UInt second)
sets data from six integers
void setSequence(const String &sequence)
sets the protein sequence
void setLastName(const String &name)
sets the last name of the person
const std::set< ProcessingAction > & getProcessingActions() const
returns a const reference to the applied processing actions
UID storeSample_(const Sample &sample, UID exp_id, UID parent_id)
Stores, updates or deletes sample information.
SpectrumType
Spectrum peak type.
Definition: SpectrumSettings.h:71
Description of the combination of raw data to a single spectrum.
Definition: AcquisitionInfo.h:54
const DRange< 1 > & getIntensityRange() const
returns the intensity range
Contact person information.
Definition: ContactPerson.h:50
void setOrder(Int order)
sets the order
void setScanRate(DoubleReal scan_rate)
sets the scan rate (in s)
bool hasMSLevels() const
returns true, if MS levels have been set
Representation of a HPLC experiment.
Definition: HPLC.h:51
DoubleReal end
End of the window.
Definition: ScanWindow.h:64
Peak2D PeakType
Definition: MassTrace.h:49
bool hasRTRange() const
returns true if an RT range has been set
void setScanMode(ScanMode scan_mode)
sets the scan mode
const std::vector< Int > & getMSLevels() const
returns the set MS levels
IonOpticsType getIonOptics() const
returns the ion optics type
void setPolarity(IonSource::Polarity polarity)
sets the polarity
void setPolarity(Polarity polarity)
sets the ionization mode
Description of a file location, used to store the origin of (meta) data.
Definition: SourceFile.h:47
void setScanDirection(ScanDirection scan_direction)
sets the direction of scanning
DoubleReal begin
Begin of the window.
Definition: ScanWindow.h:62
Description of the software used for processing.
Definition: Software.h:49
ScanDirection
direction of scanning
Definition: MassAnalyzer.h:103
void loadFile_(UID id, SourceFile &file)
Loads file information.
void setType(Type type)
sets the detector type
A 1-dimensional raw data point or peak mith meta information.
Definition: RichPeak1D.h:52
void setAABefore(char acid)
sets the amino acid before the sequence
InletType
inlet type
Definition: IonSource.h:52
void setSearchParameters(const SearchParameters &search_parameters)
Sets the search parameters.
Type
Detector type.
Definition: IonDetector.h:52
bool checkDBVersion(bool warning)
Returns true if the DB is up-to-date (Checks the version in ADMIN_Version table). ...
void loadExperiment(UID id, ExperimentType &exp)
Reads a MSExperiment.
Definition: DBAdapter.h:1070
UInt getFlux() const
returns the flux (in microliter/sec)
void setOrder(Int order)
sets the order
const std::vector< std::vector< UInt > > & getPercentages() const
returns a const reference to the percentages
Search parameters of the DB search.
Definition: ProteinIdentification.h:115
void setHigherScoreBetter(bool higher_is_better)
Sets the orientation of the score (is higher better?)
ResolutionType
Resolution type.
Definition: MassAnalyzer.h:92
String getComment() const
returns the comments
void storeExperiment(ExperimentType &exp)
Stores a MSExperiment.
Definition: DBAdapter.h:174
UID storeMetaInfo_(const String &parent_table, UID parent_id, const MetaInfoInterface &info)
Stores, updates or deletes MetaInfo data.
bool getZoomScan() const
return if this scan is a zoom (enhanced resolution) scan
ScanMode getScanMode() const
returns the scan mode
void setResolutionMethod(ResolutionMethod resolution_method)
sets the method used for determination of the resolution
void setAccuracy(DoubleReal accuracy)
sets the accuracy i.e. how much the theoretical mass may differ from the measured mass (in ppm) ...
PeakMassType
Peak mass type.
Definition: ProteinIdentification.h:91
void setSignificanceThreshold(DoubleReal value)
setting of the peptide significance threshold value
void setADCSamplingFrequency(DoubleReal ADC_sampling_frequency)
sets the analog-to-digital converter sampling frequency (in Hz)
ResolutionMethod
resolution method
Definition: MassAnalyzer.h:80
void setCompletionTime(const DateTime &completion_time)
sets the time of completition taking a DateTime object
Int toInt() const
Conversion to int.
void setHigherScoreBetter(bool value)
sets the peptide score orientation
DigestionEnzyme
Definition: ProteinIdentification.h:101
const std::vector< ScanWindow > & getScanWindows() const
returns a const reference to the m/z scan windows
void setCharge(Int charge)
sets the charge of the peptide
void setMagneticFieldStrength(DoubleReal magnetic_field_strength)
sets the strength of the magnetic field (in T)
const DRange< 1 > & getRTRange() const
returns the RT range
void setEmail(const String &email)
sets the email address
void loadSample_(UID id, Sample &sample)
Loads sample information.
void setResolution(DoubleReal resolution)
sets the resolution
DBConnection & db_con_
Reference to the DB connection handed over in the constructor.
Definition: DBAdapter.h:108
ProcessingAction
Definition: DataProcessing.h:58
Representation of a HPLC gradient.
Definition: Gradient.h:53
Int getTemperature() const
returns the temperature (in degree C)
UInt getAutoId()
Returns the last auto_increment ID of the SQL database.
Representation of a peptide hit.
Definition: PeptideHit.h:54
const String & getModel() const
returns the instrument model
void setSoftware(const Software &software)
sets the software used for processing
void setType(AnalyzerType type)
sets the analyzer type
A 1-dimensional raw data point or peak.
Definition: Peak1D.h:55
Scan window description.
Definition: ScanWindow.h:47
IonOpticsType
ion optics type
Definition: Instrument.h:71
const String & getVendor() const
returns the instrument vendor
void setResolution(DoubleReal resolution)
sets the resolution (in ns)
Meta information about the sample.
Definition: Sample.h:60
ReflectronState
Reflectron state.
Definition: MassAnalyzer.h:126
void setScanLaw(ScanLaw scan_law)
sets the scan law
Description of a ion detector (part of a MS Instrument)
Definition: IonDetector.h:47
void setFirstName(const String &name)
sets the first name of the person
void setHits(const std::vector< PeptideHit > &hits)
Sets the peptide hits.
Information about one raw data spectrum that was combined with several other raw data spectra...
Definition: Acquisition.h:50
void setPosition(const PositionType &position)
Mutable access to the position.
Definition: Peak2D.h:185
A class for connecting to a SQL database.
Definition: DBConnection.h:60
void setReflectronState(ReflectronState reflecton_state)
sets the reflectron state (for TOF)
IonizationMethod
ionization method
Definition: IonSource.h:80
const String & getName() const
returns the name of the software
const std::vector< Int > & getTimepoints() const
returns a const reference to the list of timepoints
Interface for classes that can store arbitrary meta information (Type-Name-Value tuples).
Definition: MetaInfoInterface.h:61
QString toQString() const
Conversion to Qt QString.
void setScoreType(const String &type)
Sets the protein score type.
void setSequence(const AASequence &sequence)
sets the peptide sequence
void setVersion(const String &version)
sets the software version
void setInstitution(const String &institution)
sets the affiliation
void setTOFTotalPathLength(DoubleReal TOF_total_path_length)
sets the path length for a TOF mass analyzer (in meter)
void setScanTime(DoubleReal scan_time)
sets the scan time for a single scan (in s)
void setSearchEngineVersion(const String &search_engine_version)
Sets the search engine version.
void setAccession(const String &accession)
sets the accession of the protein
Representation of a protein hit.
Definition: ProteinHit.h:54
void setInletType(InletType inlet_type)
sets the inlet type
PositionType const & minPosition() const
Accessor to minimum position.
Definition: DIntervalBase.h:121
const String & getVersion() const
returns the software version
IonSource::Polarity getPolarity() const
returns the polarity
UID storeFile_(const String &parent_table, UID parent_id, const SourceFile &file)
Stores, updates or deletes file information.
void setAAAfter(char acid)
sets the amino acid after the sequence
size_t Size
Size type e.g. used as variable which can hold result of size()
Definition: Types.h:144
const String & getMethodOfCombination() const
returns the method of combination
ScanMode
scan mode
Definition: InstrumentSettings.h:53
void setRank(UInt newrank)
sets the rank
DateTime Class.
Definition: DateTime.h:55
void setSignificanceThreshold(DoubleReal value)
Sets the protein significance threshold value.
void setScoreType(const String &type)
sets the peptide score type
void setIdentifier(const String &identifier)
sets the index/number of the scan
void setResolutionType(ResolutionType resolution_type)
sets the resolution type
const SearchParameters & getSearchParameters() const
Returns the search parameters.
A class for accessing and storing data in a SQL database.
Definition: DBAdapter.h:69
PositionType const & maxPosition() const
Accessor to maximum position.
Definition: DIntervalBase.h:127
const DRange< 1 > & getMZRange() const
returns the MZ range
UInt getPressure() const
returns the pressure (in bar)
void setName(const String &name)
sets the name of the software
ActivationMethod
Method of activation.
Definition: Precursor.h:64
void setOrder(Int order)
sets the order
Options for loading files containing peak data.
Definition: PeakFileOptions.h:47
AnalyzerType
analyzer type
Definition: MassAnalyzer.h:53
void setIonizationMethod(IonizationMethod ionization_type)
sets the ionization method
void setIsolationWidth(DoubleReal isolation_width)
sets the isolation width i.e. in which m/z range the precursor ion is selected for MS to the n (in m/...
ScanLaw
Scan law.
Definition: MassAnalyzer.h:114
int Int
Signed integer type.
Definition: Types.h:100
void setFinalMSExponent(Int final_MS_exponent)
sets the final MS exponent
Descripton of a mass analyzer (part of a MS Instrument)
Definition: MassAnalyzer.h:48
Description of a ion source (part of a MS Instrument)
Definition: IonSource.h:47
void setDateTime(const DateTime &date)
Sets the date of the protein identification run.
OPENMS_UINT64_TYPE UID
A unique object ID (as unsigned 64bit type).
Definition: Types.h:137
Polarity
Polarity of the ion source.
Definition: IonSource.h:140
const String & getColumn() const
returns a const reference to the column description
void deleteMetaInfo_(const String &parent_table, const String &condition)
Conditionally deletes MetaInfo data from database.
Parse Error exception.
Definition: Exception.h:608
Represents the peptide hits for a spectrum.
Definition: PeptideIdentification.h:63
const std::vector< String > & getEluents() const
returns a const reference to the list of eluents
bool getMetadataOnly() const
returns whether or not to load only meta data

OpenMS / TOPP release 1.11.1 Documentation generated on Thu Nov 14 2013 11:19:12 using doxygen 1.8.5