OpenTREP Logo  0.6.0
C++ Open Travel Request Parsing Library
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Pages
DBManager.cpp
Go to the documentation of this file.
1 // //////////////////////////////////////////////////////////////////////
2 // Import section
3 // //////////////////////////////////////////////////////////////////////
4 // STL
5 #include <cassert>
6 #include <sstream>
7 // SOCI
8 #include <soci/soci.h>
9 #include <soci/sqlite3/soci-sqlite3.h>
10 // OpenTrep
11 #include <opentrep/bom/World.hpp>
12 #include <opentrep/bom/Place.hpp>
13 #include <opentrep/factory/FacPlace.hpp>
14 #include <opentrep/dbadaptor/DbaPlace.hpp>
15 #include <opentrep/command/DBManager.hpp>
16 #include <opentrep/service/Logger.hpp>
17 
18 namespace OPENTREP {
19 
20  // //////////////////////////////////////////////////////////////////////
21  void DBManager::
22  prepareSelectAllStatement (soci::session& ioSociSession,
23  soci::statement& ioSelectStatement,
24  Place& ioPlace) {
25 
26  try {
27 
28  // Instanciate a SQL statement (no request is performed at that stage)
61  ioSelectStatement =
62  (ioSociSession.prepare
63  << "select dts.iata_code as iata_code, "
64  << " dts.location_type as location_type, "
65  << " dts.geoname_id as geoname_id, "
66  << " icao_code, faa_code, "
67  << " envelope_id, dts.name as utf_name, asciiname, "
68  << " latitude, longitude, fclass, fcode, "
69  << " page_rank, "
70  << " date(date_from) as date_from, date(date_until) as date_until, "
71  << " comment, "
72  << " country_code, cc2, country_name, continent_name, "
73  << " admin1_code, admin1_UTF8_name, admin1_ASCII_name, "
74  << " admin2_code, admin2_UTF8_name, admin2_ASCII_name, "
75  << " admin3_code, admin4_code, "
76  << " population, elevation, gtopo30, "
77  << " time_zone, gmt_offset, dst_offset, raw_offset, "
78  << " date(moddate) as moddate, "
79  << " state_code, wiki_link, "
80  << " alt.lang_code as alt_lang_code, alt.name as alt_name, "
81  << " alt.specifiers as alt_spec, "
82  << " city_iata_code, city_location_type, city_geoname_id, "
83  << " city_UTF8_name, city_ASCII_name "
84  << "from ori_por_public_details dts, ori_por_public_alt_names alt, "
85  << " ori_por_public_served_cities cty "
86  << "where dts.iata_code = alt.iata_code "
87  << " and dts.location_type = alt.location_type "
88  << " and dts.geoname_id = alt.geoname_id "
89  << " and dts.iata_code = cty.iata_code "
90  << " and dts.location_type = cty.location_type "
91  << " and dts.geoname_id = cty.geoname_id "
92  << "order by dts.iata_code, dts.location_type, dts.geoname_id",
93  soci::into (ioPlace));
94 
95  // Execute the SQL query
96  ioSelectStatement.execute();
97 
98  } catch (std::exception const& lException) {
99  std::ostringstream errorStr;
100  errorStr << "Error in the 'select * from ori_por_public_xxx' SQL request: "
101  << lException.what();
102  OPENTREP_LOG_ERROR (errorStr.str());
103  throw SQLDatabaseException (errorStr.str());
104  }
105  }
106 
107  // //////////////////////////////////////////////////////////////////////
108  void DBManager::
109  prepareSelectFromCodeStatement (soci::session& ioSociSession,
110  soci::statement& ioSelectStatement,
111  Place& ioPlace) {
112 
113  try {
114 
115  // Instanciate a SQL statement (no request is performed at that stage)
137  ioSelectStatement =
138  (ioSociSession.prepare
139  << "select rpd.iata_code, xapian_docid, icao_code, "
140  << "is_geonames, geonameid, "
141  << "latitude, longitude, fclass, fcode, "
142  << "country_code, cc2, admin1, admin2, admin3, admin4, "
143  << "population, elevation, gtopo30, "
144  << "timezone, gmt_offset, dst_offset, raw_offset, moddate, "
145  << "is_airport, is_commercial, "
146  << "city_code, state_code, region_code, location_type, wiki_link, "
147  << "language_code, ascii_name, utf_name, "
148  << "alternate_name1, alternate_name2, alternate_name3, "
149  << "alternate_name4, alternate_name5, alternate_name6, "
150  << "alternate_name7, alternate_name8, alternate_name9, "
151  << "alternate_name10, "
152  << "page_rank, por_type "
153  << "from place_names as pn, place_details as rpd "
154  << "left join airport_pageranked pr on pr.iata_code = rpd.iata_code "
155  << "where rpd.iata_code = pn.iata_code "
156  << "order by rpd.iata_code, icao_code, geonameid", soci::into(ioPlace));
157 
158  // Execute the SQL query
159  ioSelectStatement.execute();
160 
161  } catch (std::exception const& lException) {
162  std::ostringstream errorStr;
163  errorStr << "Error in the 'select * from place_details' SQL request: "
164  << lException.what();
165  OPENTREP_LOG_ERROR (errorStr.str());
166  throw SQLDatabaseException (errorStr.str());
167  }
168  }
169 
170  // //////////////////////////////////////////////////////////////////////
171  void DBManager::
172  prepareSelectFromCoordStatement (soci::session& ioSociSession,
173  soci::statement& ioSelectStatement,
174  const double& iLatitude,
175  const double& iLongitude) {
176 
177  try {
178 
179  // Instanciate a SQL statement (no request is performed at that stage)
203  Place& lPlace = FacPlace::instance().create();
204  const double K_ERROR = 2.0;
205  const double lLowerBoundLatitude = iLatitude - K_ERROR;
206  const double lUpperBoundLatitude = iLatitude + K_ERROR;
207  const double lLowerBoundLongitude = iLongitude - K_ERROR;
208  const double lUpperBoundLongitude = iLongitude + K_ERROR;
209 
210  ioSelectStatement =
211  (ioSociSession.prepare
212  << "select rpd.iata_code, xapian_docid, icao_code, "
213  << "is_geonames, geonameid, "
214  << "latitude, longitude, fclass, fcode, "
215  << "country_code, cc2, admin1, admin2, admin3, admin4, "
216  << "population, elevation, gtopo30, "
217  << "timezone, gmt_offset, dst_offset, raw_offset, moddate, "
218  << "is_airport, is_commercial, "
219  << "city_code, state_code, region_code, location_type, wiki_link, "
220  << "language_code, ascii_name, utf_name, "
221  << "alternate_name1, alternate_name2, alternate_name3, "
222  << "alternate_name4, alternate_name5, alternate_name6, "
223  << "alternate_name7, alternate_name8, alternate_name9, "
224  << "alternate_name10, "
225  << "page_rank, por_type "
226  << "from place_names as pn, place_details as rpd "
227  << "left join airport_pageranked pr on pr.iata_code = rpd.iata_code "
228  << "where latitude >= :lower_latitude "
229  << " and latitude <= :upper_latitude "
230  << " and longitude >= :lower_longitude "
231  << " and longitude <= :upper_longitude "
232  << " and rpd.iata_code = pn.iata_code",
233  soci::into (lPlace), soci::use (lLowerBoundLatitude),
234  soci::use (lUpperBoundLatitude), soci::use (lLowerBoundLongitude),
235  soci::use (lUpperBoundLongitude));
236 
237  // Execute the SQL query
238  ioSelectStatement.execute();
239 
240  } catch (std::exception const& lException) {
241  std::ostringstream errorStr;
242  errorStr << "Error in the 'select * from place_details' SQL request: "
243  << lException.what();
244  OPENTREP_LOG_ERROR (errorStr.str());
245  throw SQLDatabaseException (errorStr.str());
246  }
247  }
248 
249  // //////////////////////////////////////////////////////////////////////
250  void DBManager::
251  prepareSelectOnPlaceCodeStatement (soci::session& ioSociSession,
252  soci::statement& ioSelectStatement,
253  const std::string& iIataCode,
254  const std::string& iIcaoCode,
255  const GeonamesID_T& iGeonamesID,
256  Place& ioPlace) {
257 
258  try {
259 
260  // Instanciate a SQL statement (no request is performed at that stage)
284  ioSelectStatement =
285  (ioSociSession.prepare
286  << "select rpd.iata_code, xapian_docid, icao_code, "
287  << "is_geonames, geonameid, "
288  << "latitude, longitude, fclass, fcode, "
289  << "country_code, cc2, admin1, admin2, admin3, admin4, "
290  << "population, elevation, gtopo30, "
291  << "timezone, gmt_offset, dst_offset, raw_offset, moddate, "
292  << "is_airport, is_commercial, "
293  << "city_code, state_code, region_code, location_type, wiki_link, "
294  << "language_code, ascii_name, utf_name, "
295  << "alternate_name1, alternate_name2, alternate_name3, "
296  << "alternate_name4, alternate_name5, alternate_name6, "
297  << "alternate_name7, alternate_name8, alternate_name9, "
298  << "alternate_name10, "
299  << "page_rank, por_type "
300  << "from place_names as pn, place_details as rpd "
301  << "left join airport_pageranked pr on pr.iata_code = rpd.iata_code "
302  << "where rpd.iata_code = :place_iata_code "
303  << " and rpd.icao_code = :place_icao_code "
304  << " and rpd.geonameid = :place_geonameid "
305  << " and rpd.iata_code = pn.iata_code",
306  soci::into (ioPlace), soci::use (iIataCode), soci::use (iIcaoCode),
307  soci::use (iGeonamesID));
308 
309  // Execute the SQL query
310  ioSelectStatement.execute();
311 
312  } catch (std::exception const& lException) {
313  std::ostringstream errorStr;
314  errorStr << "Error in the 'select * from place_details' SQL request: "
315  << lException.what();
316  OPENTREP_LOG_ERROR (errorStr.str());
317  throw SQLDatabaseException (errorStr.str());
318  }
319  }
320 
321  // //////////////////////////////////////////////////////////////////////
322  void DBManager::
323  prepareSelectOnDocIDStatement (soci::session& ioSociSession,
324  soci::statement& ioSelectStatement,
325  const XapianDocID_T& iDocID,
326  Place& ioPlace) {
327 
328  try {
329 
330  // Instanciate a SQL statement (no request is performed at that stage)
353  ioSelectStatement =
354  (ioSociSession.prepare
355  << "select rpd.iata_code, xapian_docid, icao_code, "
356  << "is_geonames, geonameid, "
357  << "latitude, longitude, fclass, fcode, "
358  << "country_code, cc2, admin1, admin2, admin3, admin4, "
359  << "population, elevation, gtopo30, "
360  << "timezone, gmt_offset, dst_offset, raw_offset, moddate, "
361  << "is_airport, is_commercial, "
362  << "city_code, state_code, region_code, location_type, wiki_link, "
363  << "language_code, ascii_name, utf_name, "
364  << "alternate_name1, alternate_name2, alternate_name3, "
365  << "alternate_name4, alternate_name5, alternate_name6, "
366  << "alternate_name7, alternate_name8, alternate_name9, "
367  << "alternate_name10, "
368  << "page_rank, por_type "
369  << "from place_names as pn, place_details as rpd "
370  << "left join airport_pageranked pr on pr.iata_code = rpd.iata_code "
371  << "where rpd.xapian_docid = :xapian_docid "
372  << " and rpd.iata_code = pn.iata_code "
373  << " and rpd.iata_code = pr.iata_code",
374  soci::into (ioPlace), soci::use (iDocID));
375 
376  // Execute the SQL query
377  ioSelectStatement.execute();
378 
379  } catch (std::exception const& lException) {
380  std::ostringstream errorStr;
381  errorStr << "Error: " << lException.what();
382  OPENTREP_LOG_ERROR (errorStr.str());
383  throw SQLDatabaseException (errorStr.str());
384  }
385  }
386 
387  // //////////////////////////////////////////////////////////////////////
388  bool DBManager::iterateOnStatement (soci::statement& ioStatement,
389  Place& ioPlace) {
390  bool hasStillData = false;
391 
392  try {
393 
394  // Retrieve the next row of Place object
395  hasStillData = ioStatement.fetch();
396 
397  } catch (std::exception const& lException) {
398  std::ostringstream errorStr;
399  errorStr << "Error when iterating on the SQL fetch: " << lException.what();
400  errorStr << ". The current place key is: " << ioPlace.describeKey()
401  << " (it may be mis-leading, though, if the key could not be retrieved).";
402  OPENTREP_LOG_ERROR (errorStr.str());
403  throw SQLDatabaseException (errorStr.str());
404  }
405 
406  return hasStillData;
407  }
408 
409  // //////////////////////////////////////////////////////////////////////
410  void DBManager::updatePlaceInDB (soci::session& ioSociSession,
411  const Place& iPlace) {
412 
413  try {
414 
415  // Begin a transaction on the database
416  ioSociSession.begin();
417 
418  // Instanciate a SQL statement (no request is performed at that stage)
419  XapianDocID_T lDocID;
420  std::string lIataCode;
421  soci::statement lUpdateStatement =
422  (ioSociSession.prepare
423  << "update place_details "
424  << "set xapian_docid = :xapian_docid "
425  << "where iata_code = :iata_code",
426  soci::use (lDocID), soci::use (lIataCode));
427 
428  // Execute the SQL query
429  lDocID = iPlace.getDocID();
430  lIataCode = iPlace.getIataCode();
431  lUpdateStatement.execute (true);
432 
433  // Commit the transaction on the database
434  ioSociSession.commit();
435 
436  // Debug
437  // OPENTREP_LOG_DEBUG ("[" << lDocID << "] " << iPlace);
438 
439  } catch (std::exception const& lException) {
440  std::ostringstream errorStr;
441  errorStr << "Error when updating " << iPlace.toString() << ": "
442  << lException.what();
443  OPENTREP_LOG_ERROR (errorStr.str());
444  throw SQLDatabaseException (errorStr.str());
445  }
446  }
447 
448  // //////////////////////////////////////////////////////////////////////
449  bool DBManager::retrievePlace (soci::session& ioSociSession,
450  const LocationKey& iPlaceKey, Place& ioPlace) {
451  bool oHasRetrievedPlace = false;
452 
453  try {
454 
455  // Prepare the SQL request corresponding to the select statement
456  soci::statement lSelectStatement (ioSociSession);
457  const std::string& lIataCode = iPlaceKey.getIataCode();
458  const IATAType& lIataType = iPlaceKey.getIataType();
459  const std::string& lIataTypeStr = lIataType.getTypeAsString();
460  const GeonamesID_T& lGeonamesID = iPlaceKey.getGeonamesID();
461  DBManager::prepareSelectOnPlaceCodeStatement (ioSociSession,
462  lSelectStatement,
463  lIataCode, lIataTypeStr,
464  lGeonamesID, ioPlace);
469  bool hasStillData = true;
470  while (hasStillData == true) {
471  hasStillData = iterateOnStatement (lSelectStatement, ioPlace);
472 
473  // It is enough to have (at least) one database retrieved row
474  if (hasStillData == true) {
475  oHasRetrievedPlace = true;
476  }
477 
478  // Debug
479  OPENTREP_LOG_DEBUG ("[" << iPlaceKey << "] " << ioPlace);
480  }
481 
482  } catch (std::exception const& lException) {
483  std::ostringstream errorStr;
484  errorStr << "Error when trying to retrieve " << iPlaceKey
485  << " from the SQLite3 database: " << lException.what();
486  OPENTREP_LOG_ERROR (errorStr.str());
487  throw SQLDatabaseException (errorStr.str());
488  }
489 
490  return oHasRetrievedPlace;
491  }
492 
493  // //////////////////////////////////////////////////////////////////////
494  bool DBManager::retrieveClosestPlaces (soci::session& ioSociSession,
495  const double& iLatitude,
496  const double& iLongitude,
497  PlaceOrderedList_T& ioPlaceList) {
498  bool oHasRetrievedPlace = false;
499 
500  try {
501 
502 
503  } catch (std::exception const& lException) {
504  std::ostringstream errorStr;
505  errorStr << "Error when trying to retrieve the closest places for "
506  << iLatitude << " / " << iLongitude << ": " << lException.what();
507  OPENTREP_LOG_ERROR (errorStr.str());
508  throw SQLDatabaseException (errorStr.str());
509  }
510 
511  return oHasRetrievedPlace;
512  }
513 
514  // //////////////////////////////////////////////////////////////////////
515  NbOfDBEntries_T DBManager::
516  buildSQLDB (const PORFilePath_T& iPORFilepath,
517  const SQLiteDBFilePath_T& iSQLiteDBFilePath) {
518  NbOfDBEntries_T oNbOfEntries = 0;
519 
520  try {
521 
522  // Open the connection to the database
523  soci::session* lSociSession_ptr = new soci::session();
524  assert (lSociSession_ptr != NULL);
525  soci::session& lSociSession = *lSociSession_ptr;
526  lSociSession.open (soci::sqlite3, iSQLiteDBFilePath);
527 
528  // Prepare the SQL request corresponding to the select statement
529  soci::statement lSelectStatement (lSociSession);
530  Place& lPlace = FacPlace::instance().create();
531  DBManager::prepareSelectAllStatement (lSociSession, lSelectStatement,
532  lPlace);
537  bool hasStillData = true;
538  while (hasStillData == true) {
539  hasStillData = iterateOnStatement (lSelectStatement, lPlace);
540 
541  // It is enough to have (at least) one database retrieved row
542  if (hasStillData == true) {
543  ++oNbOfEntries;
544 
545  // Debug
546  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "][" << lPlace.getKey()
547  << "] " << lPlace);
548  }
549  }
550 
551  } catch (std::exception const& lException) {
552  std::ostringstream errorStr;
553  errorStr << "Error when trying to retrieve " << oNbOfEntries
554  << "-th row from the SQLite3 database: " << lException.what();
555  OPENTREP_LOG_ERROR (errorStr.str());
556  throw SQLDatabaseException (errorStr.str());
557  }
558 
559  return oNbOfEntries;
560  }
561 
562 }
OPENTREP::LocationKey
Class modelling the primary key of a location/POR (point of reference).
Definition: LocationKey.hpp:21
OPENTREP_LOG_ERROR
#define OPENTREP_LOG_ERROR(iToBeLogged)
Definition: Logger.hpp:23
OPENTREP_LOG_DEBUG
#define OPENTREP_LOG_DEBUG(iToBeLogged)
Definition: Logger.hpp:32
OPENTREP::LocationKey::getIataType
const IATAType & getIataType() const
Definition: LocationKey.hpp:34
OPENTREP::FacPlace::instance
static FacPlace & instance()
Definition: FacPlace.cpp:29
OPENTREP::DBManager::updatePlaceInDB
static void updatePlaceInDB(soci::session &, const Place &)
Definition: DBManager.cpp:410
OPENTREP::SQLiteDBFilePath_T
Definition: OPENTREP_Types.hpp:56
OPENTREP::DBManager::buildSQLDB
static NbOfDBEntries_T buildSQLDB(const PORFilePath_T &, const SQLiteDBFilePath_T &)
Definition: DBManager.cpp:516
OPENTREP::DBManager::prepareSelectFromCodeStatement
static void prepareSelectFromCodeStatement(soci::session &, soci::statement &, Place &)
Definition: DBManager.cpp:109
OPENTREP::NbOfDBEntries_T
unsigned int NbOfDBEntries_T
Definition: OPENTREP_Types.hpp:463
OPENTREP::DBManager::prepareSelectFromCoordStatement
static void prepareSelectFromCoordStatement(soci::session &, soci::statement &, const double &iLatitude, const double &iLongitude)
Definition: DBManager.cpp:172
OPENTREP::Place
Class modelling a place/POR (point of reference).
Definition: Place.hpp:28
OPENTREP::Place::describeKey
std::string describeKey() const
Definition: Place.hpp:1002
OPENTREP::LocationKey::getGeonamesID
const GeonamesID_T & getGeonamesID() const
Definition: LocationKey.hpp:41
OPENTREP::Place::getDocID
const XapianDocID_T & getDocID() const
Definition: Place.hpp:445
OPENTREP::DBManager::iterateOnStatement
static bool iterateOnStatement(soci::statement &, Place &)
Definition: DBManager.cpp:388
OPENTREP::IATAType::getTypeAsString
std::string getTypeAsString() const
Definition: IATAType.cpp:174
OPENTREP::DBManager::prepareSelectAllStatement
static void prepareSelectAllStatement(soci::session &, soci::statement &, Place &)
Definition: DBManager.cpp:22
OPENTREP::DBManager::retrievePlace
static bool retrievePlace(soci::session &, const LocationKey &, Place &)
Definition: DBManager.cpp:449
OPENTREP::PlaceOrderedList_T
std::list< Place * > PlaceOrderedList_T
Definition: PlaceList.hpp:21
OPENTREP::Place::toString
std::string toString() const
Definition: Place.cpp:83
OPENTREP::IATAType
Enumeration of output types.
Definition: IATAType.hpp:22
OPENTREP::Place::getKey
const LocationKey & getKey() const
Definition: Place.hpp:58
OPENTREP::FacPlace::create
Place & create()
Definition: FacPlace.cpp:41
OPENTREP::Place::getIataCode
const IATACode_T & getIataCode() const
Definition: Place.hpp:65
OPENTREP::LocationKey::getIataCode
const IATACode_T & getIataCode() const
Definition: LocationKey.hpp:27
OPENTREP::DBManager::retrieveClosestPlaces
static bool retrieveClosestPlaces(soci::session &, const double &iLatitude, const double &iLongitude, PlaceOrderedList_T &)
Definition: DBManager.cpp:494
OPENTREP::PORFilePath_T
Definition: OPENTREP_Types.hpp:37
OPENTREP::RootException::what
const char * what() const
Definition: OPENTREP_exceptions.hpp:39
  • Generated on Mon Feb 3 2014 14:17:07 for OpenTREP by doxygen 1.8.6
  • SourceForge Logo