OpenTREP Logo  0.07.7
C++ Open Travel Request Parsing Library
DBManager.cpp
Go to the documentation of this file.
1 // //////////////////////////////////////////////////////////////////////
2 // Import section
3 // //////////////////////////////////////////////////////////////////////
4 // STL
5 #include <cassert>
6 #include <sstream>
7 // Boost
8 #include <boost/lexical_cast.hpp>
9 #include <boost/date_time/gregorian/gregorian.hpp>
10 #include <boost/filesystem.hpp>
11 #include <boost/algorithm/string.hpp>
12 // SOCI
13 #include <soci/soci.h>
14 #include <soci/sqlite3/soci-sqlite3.h>
15 #include <soci/mysql/soci-mysql.h>
16 // OpenTrep
17 #include <opentrep/Location.hpp>
20 #include <opentrep/bom/World.hpp>
21 #include <opentrep/bom/Place.hpp>
22 #include <opentrep/bom/Result.hpp>
30 
31 namespace OPENTREP {
32 
33  // //////////////////////////////////////////////////////////////////////
35  createSQLDBUser (const DBType& iDBType,
36  const SQLDBConnectionString_T& iSQLDBConnStr,
37  const DeploymentNumber_T& iDeploymentNumber) {
38  bool oCreationSuccessful = true;
39 
40  // DEBUG
41  if (iDBType == DBType::MYSQL) {
42  OPENTREP_LOG_DEBUG ("The MySQL/MariaDB database user will be created/reset");
43  }
44 
45  if (iDBType == DBType::SQLITE3) {
46 
47  try {
48 
49  // Retrieve the full file-path of the SQLite3 directory
50  boost::filesystem::path lSQLiteDBFullPath (iSQLDBConnStr.begin(),
51  iSQLDBConnStr.end());
52  // Retrieve the directory hosting the SQLite3 database
53  boost::filesystem::path lSQLiteDBParentPath =
54  lSQLiteDBFullPath.parent_path();
55 
56  // DEBUG
57  OPENTREP_LOG_DEBUG ("The SQLite database file ('" << lSQLiteDBFullPath
58  << "') will be cleared and re-created");
59 
60  // Delete the SQL database/file and its directory
61  boost::filesystem::remove_all (lSQLiteDBFullPath);
62 
63  // Re-create the SQLite3 directory
64  boost::filesystem::create_directories (lSQLiteDBParentPath);
65 
66  // Check whether the just created directory exists and is a directory.
67  //boost::filesystem::path lSQLiteDBFilename=lSQLiteDBFullPath.filename();
68  if (!(boost::filesystem::exists (lSQLiteDBParentPath)
69  && boost::filesystem::is_directory (lSQLiteDBParentPath))) {
70  std::ostringstream oStr;
71  oStr << "Error. The path to the SQLite3 database directory ('"
72  << lSQLiteDBParentPath
73  << "') does not exist or is not a directory.";
74  OPENTREP_LOG_ERROR (oStr.str());
75  throw FileNotFoundException (oStr.str());
76  }
77 
78  } catch (std::exception const& lException) {
79  std::ostringstream errorStr;
80  errorStr << "Error when trying to create " << iSQLDBConnStr
81  << " SQLite3 database file: " << lException.what();
82  errorStr << ". Check that the program has got write permission on the "
83  << "corresponding parent directories.";
84  OPENTREP_LOG_ERROR (errorStr.str());
85  throw SQLDatabaseFileCannotBeCreatedException (errorStr.str());
86  }
87 
88  // DEBUG
89  OPENTREP_LOG_DEBUG ("The SQLite database ('" << iSQLDBConnStr
90  << "') has been cleared and re-created");
91 
92  } else if (iDBType == DBType::MYSQL) {
93  // DEBUG
94  OPENTREP_LOG_DEBUG ("Create the '"
96  << "' user and '" << DEFAULT_OPENTREP_MYSQL_DB_DBNAME
97  << iDeploymentNumber
98  << "' database in MySQL/MariaDB ('" << iSQLDBConnStr
99  << "')");
100 
101  // Connection to the MySQL/MariaDB database
102  soci::session* lSociSession_ptr = NULL;
103  try {
104 
105  // Connect to the SQL database/file
106  lSociSession_ptr = initSQLDBSession (iDBType, iSQLDBConnStr);
107  if (lSociSession_ptr == NULL) {
108  oCreationSuccessful = false;
109  return oCreationSuccessful;
110  }
111 
112  } catch (soci::mysql_soci_error const& lSociException) {
113  std::ostringstream errorStr;
114  errorStr << "SOCI-related error when trying to connect to the "
115  << "MySQL/MariaDB database ('" << iSQLDBConnStr
116  << "'). SOCI error message: " << lSociException.what();
117  OPENTREP_LOG_ERROR (errorStr.str());
118  std::cerr << errorStr.str() << std::endl;
119  oCreationSuccessful = false;
120  return oCreationSuccessful;
121  }
122  assert (lSociSession_ptr != NULL);
123  soci::session& lSociSession = *lSociSession_ptr;
124 
153  try {
154  // Drop user 'trep'@'localhost'
155  std::ostringstream lSQLDropTrepLocalStr;
156  lSQLDropTrepLocalStr << "drop user '"
159  lSociSession << lSQLDropTrepLocalStr.str();
160 
161  // Drop user 'trep'@'%'
162  std::ostringstream lSQLDropTrepAllStr;
163  lSQLDropTrepAllStr << "drop user '"
164  << DEFAULT_OPENTREP_MYSQL_DB_USER << "'@'%';";
165  lSociSession << lSQLDropTrepAllStr.str();
166 
167  } catch (soci::mysql_soci_error const& lSociException) {
168  std::ostringstream issueStr;
169  issueStr << "Issue when trying to drop MySQL/MariaDB '"
170  << DEFAULT_OPENTREP_MYSQL_DB_USER << "' user. "
171  << "Most probably the user did not exist before. " << std::endl
172  << "SOCI error message: " << lSociException.what() << std::endl
173  << "The database users should however be created without "
174  << "any issue ";
175  OPENTREP_LOG_DEBUG (issueStr.str());
176  std::cout << issueStr.str() << std::endl;
177  }
178 
179  try {
180  // Create user 'trep'@'localhost'
181  std::ostringstream lSQLCreateTrepLocalStr;
182  lSQLCreateTrepLocalStr << "create user '"
185  lSQLCreateTrepLocalStr << "identified by '"
187  lSociSession << lSQLCreateTrepLocalStr.str();
188 
189  // Grant privileges to 'trep'@'localhost'
190  std::ostringstream lSQLGrantTrepLocalStr;
191  lSQLGrantTrepLocalStr << "grant SELECT, INSERT, UPDATE, DELETE, ";
192  lSQLGrantTrepLocalStr << "CREATE, DROP, FILE, INDEX, ALTER, ";
193  lSQLGrantTrepLocalStr << "CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, ";
194  lSQLGrantTrepLocalStr << "TRIGGER, SHOW VIEW, CREATE ROUTINE, ";
195  lSQLGrantTrepLocalStr << "ALTER ROUTINE, EXECUTE ON *.*";
196  lSQLGrantTrepLocalStr << " to '" << DEFAULT_OPENTREP_MYSQL_DB_USER
197  << "'@'" << DEFAULT_OPENTREP_MYSQL_DB_HOST << "';";
198  lSociSession << lSQLGrantTrepLocalStr.str();
199 
200  // Create user 'trep'@'%'
201  std::ostringstream lSQLCreateTrepAllStr;
202  lSQLCreateTrepAllStr << "create user '"
204  << "'@'%' identified by '"
206  lSociSession << lSQLCreateTrepAllStr.str();
207 
208  // Grant privileges to 'trep'@'%'
209  std::ostringstream lSQLGrantTrepAllStr;
210  lSQLGrantTrepAllStr << "grant SELECT, INSERT, UPDATE, DELETE, ";
211  lSQLGrantTrepAllStr << "CREATE, DROP, FILE, INDEX, ALTER, ";
212  lSQLGrantTrepAllStr << "CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, ";
213  lSQLGrantTrepAllStr << "TRIGGER, SHOW VIEW, CREATE ROUTINE, ";
214  lSQLGrantTrepAllStr << "ALTER ROUTINE, EXECUTE ON *.*";
215  lSQLGrantTrepAllStr << " to '" << DEFAULT_OPENTREP_MYSQL_DB_USER
216  << "'@'%';";
217  lSociSession << lSQLGrantTrepAllStr.str();
218 
219  // Flush privileges
220  std::ostringstream lSQLFlushPrivilegesStr;
221  lSQLFlushPrivilegesStr << "flush privileges;";
222  lSociSession << lSQLFlushPrivilegesStr.str();
223 
224  } catch (soci::mysql_soci_error const& lSociException) {
225  oCreationSuccessful = false;
226  std::ostringstream errorStr;
227  errorStr << "SOCI-related error when trying to create MySQL/MariaDB "
229  << "' user. Error message: " << lSociException.what();
230  OPENTREP_LOG_ERROR (errorStr.str());
231  std::cerr << errorStr.str() << std::endl;
232  oCreationSuccessful = false;
233  return oCreationSuccessful;
234  }
235 
244  try {
245  // Drop the 'trep_trep' database, if existing
246  std::ostringstream lSQLDropDBStr;
247  lSQLDropDBStr << "drop database if exists "
248  << DEFAULT_OPENTREP_MYSQL_DB_DBNAME << iDeploymentNumber
249  << ";";
250  lSociSession << lSQLDropDBStr.str();
251 
252  // Create the 'trep_trep' database
253  std::ostringstream lSQLCreateDBStr;
254  lSQLCreateDBStr << "create database if not exists "
255  << DEFAULT_OPENTREP_MYSQL_DB_DBNAME << iDeploymentNumber;
256  lSQLCreateDBStr << " default character set utf8mb4";
257  lSQLCreateDBStr << " collate utf8mb4_unicode_ci;";
258  lSociSession << lSQLCreateDBStr.str();
259 
260  } catch (soci::mysql_soci_error const& lSociException) {
261  oCreationSuccessful = false;
262  std::ostringstream errorStr;
263  errorStr << "SOCI-related error when trying to create MySQL/MariaDB "
264  << "'" << DEFAULT_OPENTREP_MYSQL_DB_DBNAME << iDeploymentNumber
265  << "' database with 'utf8mb4' as character set. "
266  << "Error message: " << lSociException.what();
267  OPENTREP_LOG_ERROR (errorStr.str());
268  std::cerr << errorStr.str() << std::endl;
269  }
270  if (oCreationSuccessful == false) {
271  try {
272  // Drop the 'trep_trep' database, if existing
273  std::ostringstream lSQLDropDBStr;
274  lSQLDropDBStr << "drop database if exists "
275  << DEFAULT_OPENTREP_MYSQL_DB_DBNAME << iDeploymentNumber
276  << ";";
277  lSociSession << lSQLDropDBStr.str();
278 
279  // Create the 'trep_trep' database
280  std::ostringstream lSQLCreateDBStr;
281  lSQLCreateDBStr << "create database if not exists "
283  << iDeploymentNumber;
284  lSQLCreateDBStr << " default character set utf8";
285  lSQLCreateDBStr << " collate utf8_unicode_ci;";
286  lSociSession << lSQLCreateDBStr.str();
287 
288  } catch (soci::mysql_soci_error const& lSociException) {
289  oCreationSuccessful = false;
290  std::ostringstream errorStr;
291  errorStr << "SOCI-related error when trying to create MySQL/MariaDB "
293  << iDeploymentNumber
294  << "' database. Error message: " << lSociException.what();
295  OPENTREP_LOG_ERROR (errorStr.str());
296  std::cerr << errorStr.str() << std::endl;
297  oCreationSuccessful = false;
298  return oCreationSuccessful;
299  }
300  }
301 
302  // DEBUG
304  << "' user and '" << DEFAULT_OPENTREP_MYSQL_DB_DBNAME
305  << iDeploymentNumber
306  << "' database have been created in MySQL/MariaDB ('"
307  << iSQLDBConnStr << "')");
308 
309  } else if (iDBType == DBType::NODB || iDBType == DBType::SQLITE3) {
310  // Do nothing
311  }
312 
313  return oCreationSuccessful;
314  }
315 
316  // //////////////////////////////////////////////////////////////////////
317  soci::session* DBManager::
318  initSQLDBSession (const DBType& iDBType,
319  const SQLDBConnectionString_T& iSQLDBConnStr) {
320  soci::session* oSociSession_ptr = NULL;
321 
322  // DEBUG
323  if (!(iDBType == DBType::NODB)) {
324  OPENTREP_LOG_DEBUG ("Connecting to the " << iDBType.describe()
325  << " SQL database/file ('" << iSQLDBConnStr << "')");
326  }
327 
328  if (iDBType == DBType::SQLITE3) {
329 
330  // Check that the directory hosting the SQLite database exists
331  const bool existSQLDBDir =
332  FileManager::checkSQLiteDirectory (iSQLDBConnStr);
333  if (existSQLDBDir == false) {
334  std::ostringstream errorStr;
335  errorStr << "Error when trying to connect to the '" << iSQLDBConnStr
336  << "' SQLite3 database; the directory hosting that "
337  << "database does not exist or is not readable";
338  OPENTREP_LOG_ERROR (errorStr.str());
339  throw SQLDatabaseImpossibleConnectionException (errorStr.str());
340  }
341 
342  try {
343 
344  // Connect to the SQL database.
345  oSociSession_ptr = new soci::session();
346  assert (oSociSession_ptr != NULL);
347  soci::session& lSociSession = *oSociSession_ptr;
348  lSociSession.open (soci::sqlite3, iSQLDBConnStr);
349 
350  // DEBUG
351  OPENTREP_LOG_DEBUG ("The SQLite3 database/file ('" << iSQLDBConnStr
352  << "') has been checked and opened");
353 
354  } catch (std::exception const& lException) {
355  std::ostringstream errorStr;
356  errorStr << "Error when trying to connect to the '" << iSQLDBConnStr
357  << "' SQLite3 database: " << lException.what();
358  OPENTREP_LOG_ERROR (errorStr.str());
359  throw SQLDatabaseImpossibleConnectionException (errorStr.str());
360  }
361 
362  // The SQLite3 connection is assumed to have been successful
363  assert (oSociSession_ptr != NULL);
364 
365  } else if (iDBType == DBType::MYSQL) {
366 
367  try {
368 
369  // Connect to the SQL database.
370  oSociSession_ptr = new soci::session();
371  assert (oSociSession_ptr != NULL);
372  soci::session& lSociSession = *oSociSession_ptr;
373  lSociSession.open (soci::mysql, iSQLDBConnStr);
374 
375  // DEBUG
376  OPENTREP_LOG_DEBUG ("The " << iDBType.describe() << " database ("
377  << iSQLDBConnStr << ") is accessible");
378 
379  } catch (std::exception const& lException) {
380  std::ostringstream errorStr;
381  errorStr << "Error when trying to connect to the '" << iSQLDBConnStr
382  << "' MySQL/MariaDB database: " << lException.what();
383  OPENTREP_LOG_ERROR (errorStr.str());
384  throw SQLDatabaseImpossibleConnectionException (errorStr.str());
385  }
386 
387  // The MySQL/MariaDB connection is assumed to have been successful
388  assert (oSociSession_ptr != NULL);
389 
390  } else if (iDBType == DBType::NODB) {
391  // Do nothing
392 
393  } else {
394  std::ostringstream errorStr;
395  errorStr << "Error: the '" << iDBType.describe()
396  << "' SQL database type is not supported";
397  OPENTREP_LOG_ERROR (errorStr.str());
398  throw SQLDatabaseTableCreationException (errorStr.str());
399  }
400 
401  return oSociSession_ptr;
402  }
403 
404  // //////////////////////////////////////////////////////////////////////
406  terminateSQLDBSession (const DBType& iDBType,
407  const SQLDBConnectionString_T& iSQLDBConnStr,
408  soci::session& ioSociSession) {
409  // DEBUG
410  if (!(iDBType == DBType::NODB)) {
411  OPENTREP_LOG_DEBUG ("Connecting to the " << iDBType.describe()
412  << " SQL database/file ('" << iSQLDBConnStr << "')");
413  }
414 
415  if (iDBType == DBType::SQLITE3) {
416  //
417  try {
418 
419  // Release the SQL database connection
420  ioSociSession.close();
421 
422  } catch (std::exception const& lException) {
423  std::ostringstream errorStr;
424  errorStr << "Error when trying to release the connection ('"
425  << iSQLDBConnStr
426  << "') to the SQLite3 database: " << lException.what();
427  OPENTREP_LOG_ERROR (errorStr.str());
428  throw SQLDatabaseConnectionReleaseException (errorStr.str());
429  }
430 
431  } else if (iDBType == DBType::MYSQL) {
432  //
433  try {
434 
435  // Release the SQL database connection
436  ioSociSession.close();
437 
438  } catch (std::exception const& lException) {
439  std::ostringstream errorStr;
440  errorStr << "Error when trying to release the connection ('"
441  << iSQLDBConnStr
442  << "') to the MySQL/MariaDB database: " << lException.what();
443  OPENTREP_LOG_ERROR (errorStr.str());
444  throw SQLDatabaseConnectionReleaseException (errorStr.str());
445  }
446 
447  } else if (iDBType == DBType::NODB) {
448  // Do nothing
449 
450  } else {
451  std::ostringstream errorStr;
452  errorStr << "Error: the '" << iDBType.describe()
453  << "' SQL database type is not supported";
454  OPENTREP_LOG_ERROR (errorStr.str());
455  throw SQLDatabaseTableCreationException (errorStr.str());
456  }
457  }
458 
459  // //////////////////////////////////////////////////////////////////////
460  void DBManager::createSQLDBTables (soci::session& ioSociSession) {
461  const std::string& lDBName = ioSociSession.get_backend_name();
462  const DBType lDBType (lDBName);
463 
464  // DEBUG
465  if (!(lDBType == DBType::NODB)) {
466  OPENTREP_LOG_DEBUG ("The tables of the " << lDBType.describe()
467  << " SQL database/file will be created/reset");
468  }
469 
470  if (lDBType == DBType::SQLITE3) {
471 
472  // DEBUG
473  OPENTREP_LOG_DEBUG ("Create the optd_por table in the SQLite3 database");
474 
475  try {
476 
497  ioSociSession << "drop table if exists optd_por;";
498  std::ostringstream lSQLTableCreationStr;
499  lSQLTableCreationStr << "create table optd_por (";
500  lSQLTableCreationStr << "pk varchar(20) NOT NULL, ";
501  lSQLTableCreationStr << "location_type varchar(4) default NULL, ";
502  lSQLTableCreationStr << "iata_code varchar(3) default NULL, ";
503  lSQLTableCreationStr << "icao_code varchar(4) default NULL, ";
504  lSQLTableCreationStr << "faa_code varchar(4) default NULL, ";
505  lSQLTableCreationStr << "unlocode_code varchar(5) default NULL, ";
506  lSQLTableCreationStr << "uic_code int(11) default NULL, ";
507  lSQLTableCreationStr << "is_geonames varchar(1) default NULL, ";
508  lSQLTableCreationStr << "geoname_id int(11) default NULL, ";
509  lSQLTableCreationStr << "envelope_id int(11) default NULL, ";
510  lSQLTableCreationStr << "date_from date default NULL, ";
511  lSQLTableCreationStr << "date_until date default NULL, ";
512  lSQLTableCreationStr << "serialised_place varchar(12000) default NULL);";
513  ioSociSession << lSQLTableCreationStr.str();
514 
515  } catch (std::exception const& lException) {
516  std::ostringstream errorStr;
517  errorStr << "Error when trying to create SQLite3 tables: "
518  << lException.what();
519  OPENTREP_LOG_ERROR (errorStr.str());
520  throw SQLDatabaseTableCreationException (errorStr.str());
521  }
522 
523  // DEBUG
524  OPENTREP_LOG_DEBUG ("The optd_por table has been created in the SQLite3 database");
525 
526  } else if (lDBType == DBType::MYSQL) {
527 
528  // DEBUG
529  OPENTREP_LOG_DEBUG ("Create the optd_por table in the MySQL database");
530 
531  try {
532 
553  ioSociSession << "drop table if exists optd_por;";
554  std::ostringstream lSQLTableCreationStr;
555  lSQLTableCreationStr << "create table optd_por (";
556  lSQLTableCreationStr << "pk varchar(20) NOT NULL, ";
557  lSQLTableCreationStr << "location_type varchar(4) default NULL, ";
558  lSQLTableCreationStr << "iata_code varchar(3) default NULL, ";
559  lSQLTableCreationStr << "icao_code varchar(4) default NULL, ";
560  lSQLTableCreationStr << "faa_code varchar(4) default NULL, ";
561  lSQLTableCreationStr << "unlocode_code varchar(5) default NULL, ";
562  lSQLTableCreationStr << "uic_code int(11) default NULL, ";
563  lSQLTableCreationStr << "is_geonames varchar(1) default NULL, ";
564  lSQLTableCreationStr << "geoname_id int(11) default NULL, ";
565  lSQLTableCreationStr << "envelope_id int(11) default NULL, ";
566  lSQLTableCreationStr << "date_from date default NULL, ";
567  lSQLTableCreationStr << "date_until date default NULL, ";
568  lSQLTableCreationStr << "serialised_place varchar(12000) default NULL); ";
569  ioSociSession << lSQLTableCreationStr.str();
570 
571  } catch (std::exception const& lException) {
572  std::ostringstream errorStr;
573  errorStr << "Error when trying to create MySQL/MariaDB tables: "
574  << lException.what();
575  OPENTREP_LOG_ERROR (errorStr.str());
576  throw SQLDatabaseTableCreationException (errorStr.str());
577  }
578 
579  // DEBUG
580  OPENTREP_LOG_DEBUG ("The optd_por table has been created in the MySQL database");
581 
582  } else if (lDBType == DBType::NODB) {
583  // Do nothing
584 
585  } else {
586  std::ostringstream errorStr;
587  errorStr << "Error: the '" << lDBName
588  << "' SQL database type is not supported";
589  OPENTREP_LOG_ERROR (errorStr.str());
590  throw SQLDatabaseTableCreationException (errorStr.str());
591  }
592  }
593 
594  // //////////////////////////////////////////////////////////////////////
595  void DBManager::createSQLDBIndexes (soci::session& ioSociSession) {
596  const std::string& lDBName = ioSociSession.get_backend_name();
597  const DBType lDBType (lDBName);
598 
599  // DEBUG
600  if (!(lDBType == DBType::NODB)) {
601  OPENTREP_LOG_DEBUG ("The indexes of the " << lDBType.describe()
602  << " SQL database/file will be created/reset");
603  }
604 
605  if (lDBType == DBType::SQLITE3) {
606 
607  // DEBUG
608  OPENTREP_LOG_DEBUG ("Create the indices for the SQLite3 database");
609 
610  try {
611 
623  ioSociSession
624  << "create index optd_por_iata_code on optd_por (iata_code);";
625  ioSociSession
626  << "create index optd_por_iata_date on optd_por (iata_code, date_from, date_until);";
627  ioSociSession
628  << "create index optd_por_icao_code on optd_por (icao_code);";
629  ioSociSession
630  << "create index optd_por_geonameid on optd_por (geoname_id);";
631  ioSociSession
632  << "create index optd_por_unlocode_code on optd_por (unlocode_code);";
633  ioSociSession
634  << "create index optd_por_uic_code on optd_por (uic_code);";
635 
636  } catch (std::exception const& lException) {
637  std::ostringstream errorStr;
638  errorStr << "Error when trying to create SQLite3 indexes: "
639  << lException.what();
640  OPENTREP_LOG_ERROR (errorStr.str());
641  throw SQLDatabaseIndexCreationException (errorStr.str());
642  }
643 
644  // DEBUG
645  OPENTREP_LOG_DEBUG ("The indices have been created "
646  "for the SQLite3 database");
647 
648  } else if (lDBType == DBType::MYSQL) {
649 
650  // DEBUG
651  OPENTREP_LOG_DEBUG ("Create the indices for the MySQL database");
652 
653  try {
654 
668  ioSociSession
669  << "alter table optd_por add unique index optd_por_pk (pk asc);";
670  ioSociSession
671  << "alter table optd_por add index optd_por_iata_code (iata_code asc);";
672  ioSociSession
673  << "alter table optd_por add index optd_por_iata_date (iata_code asc, date_from asc, date_until asc);";
674  ioSociSession
675  << "alter table optd_por add index optd_por_icao_code (icao_code asc);";
676  ioSociSession
677  << "alter table optd_por add index optd_por_geonameid (geoname_id asc);";
678  ioSociSession
679  << "alter table optd_por add index optd_por_unlocode_code (unlocode_code asc);";
680  ioSociSession
681  << "alter table optd_por add index optd_por_uic_code (uic_code asc);";
682 
683  } catch (std::exception const& lException) {
684  std::ostringstream errorStr;
685  errorStr << "Error when trying to create MySQL/MariaDB indices: "
686  << lException.what();
687  OPENTREP_LOG_ERROR (errorStr.str());
688  throw SQLDatabaseIndexCreationException (errorStr.str());
689  }
690 
691  // DEBUG
692  OPENTREP_LOG_DEBUG ("The indices have been created "
693  "for the MySQL/MariaDB database");
694 
695  } else if (lDBType == DBType::NODB) {
696  // Do nothing
697 
698  } else {
699  std::ostringstream errorStr;
700  errorStr << "Error: the '" << lDBName
701  << "' SQL database type is not supported";
702  OPENTREP_LOG_ERROR (errorStr.str());
703  throw SQLDatabaseIndexCreationException (errorStr.str());
704  }
705  }
706 
707  // //////////////////////////////////////////////////////////////////////
708  std::string DBManager::
709  prepareSelectAllBlobStatement (soci::session& ioSociSession,
710  soci::statement& ioSelectStatement) {
711  std::string oSerialisedPlaceStr;
712 
713  try {
714 
715  // Instanciate a SQL statement (no request is performed at that stage)
720  ioSelectStatement = (ioSociSession.prepare
721  << "select serialised_place from optd_por",
722  soci::into (oSerialisedPlaceStr));
723 
724  // Execute the SQL query
725  ioSelectStatement.execute();
726 
727  } catch (std::exception const& lException) {
728  std::ostringstream errorStr;
729  errorStr
730  << "Error in the 'select serialised_place from optd_por' SQL request: "
731  << lException.what();
732  OPENTREP_LOG_ERROR (errorStr.str());
733  throw SQLDatabaseException (errorStr.str());
734  }
735 
736  //
737  return oSerialisedPlaceStr;
738  }
739 
740  // //////////////////////////////////////////////////////////////////////
741  void DBManager::
742  prepareSelectBlobOnIataCodeStatement (soci::session& ioSociSession,
743  soci::statement& ioSelectStatement,
744  const std::string& iIataCode,
745  std::string& ioSerialisedPlaceStr) {
746 
747  try {
748 
749  // Instanciate a SQL statement (no request is performed at that stage)
753  ioSelectStatement = (ioSociSession.prepare
754  << "select serialised_place from optd_por "
755  << "where iata_code = :place_iata_code",
756  soci::use (iIataCode),
757  soci::into (ioSerialisedPlaceStr));
758 
759  // Execute the SQL query
760  ioSelectStatement.execute();
761 
762  } catch (std::exception const& lException) {
763  std::ostringstream errorStr;
764  errorStr
765  << "Error in the 'select serialised_place from optd_por' SQL request: "
766  << lException.what();
767  OPENTREP_LOG_ERROR (errorStr.str());
768  throw SQLDatabaseException (errorStr.str());
769  }
770  }
771 
772  // //////////////////////////////////////////////////////////////////////
773  void DBManager::
774  prepareSelectBlobOnIcaoCodeStatement (soci::session& ioSociSession,
775  soci::statement& ioSelectStatement,
776  const std::string& iIcaoCode,
777  std::string& ioSerialisedPlaceStr) {
778 
779  try {
780 
781  // Instanciate a SQL statement (no request is performed at that stage)
785  ioSelectStatement = (ioSociSession.prepare
786  << "select serialised_place from optd_por "
787  << "where icao_code = :place_icao_code",
788  soci::use (iIcaoCode),
789  soci::into (ioSerialisedPlaceStr));
790 
791  // Execute the SQL query
792  ioSelectStatement.execute();
793 
794  } catch (std::exception const& lException) {
795  std::ostringstream errorStr;
796  errorStr
797  << "Error in the 'select serialised_place from optd_por' SQL request: "
798  << lException.what();
799  OPENTREP_LOG_ERROR (errorStr.str());
800  throw SQLDatabaseException (errorStr.str());
801  }
802  }
803 
804  // //////////////////////////////////////////////////////////////////////
805  void DBManager::
806  prepareSelectBlobOnFaaCodeStatement (soci::session& ioSociSession,
807  soci::statement& ioSelectStatement,
808  const std::string& iFaaCode,
809  std::string& ioSerialisedPlaceStr) {
810 
811  try {
812 
813  // Instanciate a SQL statement (no request is performed at that stage)
817  ioSelectStatement = (ioSociSession.prepare
818  << "select serialised_place from optd_por "
819  << "where faa_code = :place_faa_code",
820  soci::use (iFaaCode),
821  soci::into (ioSerialisedPlaceStr));
822 
823  // Execute the SQL query
824  ioSelectStatement.execute();
825 
826  } catch (std::exception const& lException) {
827  std::ostringstream errorStr;
828  errorStr
829  << "Error in the 'select serialised_place from optd_por' SQL request: "
830  << lException.what();
831  OPENTREP_LOG_ERROR (errorStr.str());
832  throw SQLDatabaseException (errorStr.str());
833  }
834  }
835 
836  // //////////////////////////////////////////////////////////////////////
837  void DBManager::
838  prepareSelectBlobOnUNLOCodeStatement (soci::session& ioSociSession,
839  soci::statement& ioSelectStatement,
840  const std::string& iUNLOCode,
841  std::string& ioSerialisedPlaceStr) {
842 
843  try {
844 
845  // Instanciate a SQL statement (no request is performed at that stage)
849  ioSelectStatement = (ioSociSession.prepare
850  << "select serialised_place from optd_por "
851  << "where unlocode_code = :place_unlocode_code",
852  soci::use (iUNLOCode),
853  soci::into (ioSerialisedPlaceStr));
854 
855  // Execute the SQL query
856  ioSelectStatement.execute();
857 
858  } catch (std::exception const& lException) {
859  std::ostringstream errorStr;
860  errorStr
861  << "Error in the 'select serialised_place from optd_por' SQL request: "
862  << lException.what();
863  OPENTREP_LOG_ERROR (errorStr.str());
864  throw SQLDatabaseException (errorStr.str());
865  }
866  }
867 
868  // //////////////////////////////////////////////////////////////////////
869  void DBManager::
870  prepareSelectBlobOnUICCodeStatement (soci::session& ioSociSession,
871  soci::statement& ioSelectStatement,
872  const UICCode_T& iUICCode,
873  std::string& ioSerialisedPlaceStr) {
874 
875  try {
876 
877  // Instanciate a SQL statement (no request is performed at that stage)
881  ioSelectStatement = (ioSociSession.prepare
882  << "select serialised_place from optd_por "
883  << "where uic_code = :place_uic_code",
884  soci::use (iUICCode),
885  soci::into (ioSerialisedPlaceStr));
886 
887  // Execute the SQL query
888  ioSelectStatement.execute();
889 
890  } catch (std::exception const& lException) {
891  std::ostringstream errorStr;
892  errorStr
893  << "Error in the 'select serialised_place from optd_por' SQL request: "
894  << lException.what();
895  OPENTREP_LOG_ERROR (errorStr.str());
896  throw SQLDatabaseException (errorStr.str());
897  }
898  }
899 
900  // //////////////////////////////////////////////////////////////////////
901  void DBManager::
902  prepareSelectBlobOnPlaceGeoIDStatement (soci::session& ioSociSession,
903  soci::statement& ioSelectStatement,
904  const GeonamesID_T& iGeonameID,
905  std::string& ioSerialisedPlaceStr) {
906 
907  try {
908 
909  // Instanciate a SQL statement (no request is performed at that stage)
913  ioSelectStatement = (ioSociSession.prepare
914  << "select serialised_place from optd_por "
915  << "where geoname_id = :place_geoname_id",
916  soci::use (iGeonameID),
917  soci::into (ioSerialisedPlaceStr));
918 
919  // Execute the SQL query
920  ioSelectStatement.execute();
921 
922  } catch (std::exception const& lException) {
923  std::ostringstream errorStr;
924  errorStr
925  << "Error in the 'select serialised_place from optd_por' SQL request: "
926  << lException.what();
927  OPENTREP_LOG_ERROR (errorStr.str());
928  throw SQLDatabaseException (errorStr.str());
929  }
930  }
931 
932  // //////////////////////////////////////////////////////////////////////
933  bool DBManager::iterateOnStatement (soci::statement& ioStatement,
934  const std::string& iSerialisedPlaceStr) {
935  bool hasStillData = false;
936 
937  try {
938 
939  // Retrieve the next row of Place object
940  hasStillData = ioStatement.fetch();
941 
942  } catch (std::exception const& lException) {
943  std::ostringstream errorStr;
944  errorStr << "Error when iterating on the SQL fetch: " << lException.what();
945  errorStr << ". The current place is: " << iSerialisedPlaceStr;
946  OPENTREP_LOG_ERROR (errorStr.str());
947  throw SQLDatabaseException (errorStr.str());
948  }
949 
950  return hasStillData;
951  }
952 
953  // //////////////////////////////////////////////////////////////////////
954  void DBManager::insertPlaceInDB (soci::session& ioSociSession,
955  const Place& iPlace) {
956 
957  try {
958 
959  // Begin a transaction on the database
960  ioSociSession.begin();
961 
962  // Instanciate a SQL statement (no request is performed at that stage)
963  const LocationKey& lLocationKey = iPlace.getKey();
964  const std::string lPK (lLocationKey.toString());
965  const IATAType& lIataType = iPlace.getIataType();
966  const std::string lLocationType (lIataType.getTypeAsString());
967  const std::string lIataCode (iPlace.getIataCode());
968  const std::string lIcaoCode (iPlace.getIcaoCode());
969  const std::string lFaaCode (iPlace.getFaaCode());
970  const std::string lIsGeonames ((iPlace.isGeonames())?"Y":"N");
971  const std::string lGeonameID =
972  boost::lexical_cast<std::string> (iPlace.getGeonamesID());
973  const std::string lEnvID =
974  boost::lexical_cast<std::string> (iPlace.getEnvelopeID());
975  const std::string lDateFrom =
976  boost::gregorian::to_iso_extended_string (iPlace.getDateFrom());
977  const std::string lDateEnd =
978  boost::gregorian::to_iso_extended_string (iPlace.getDateEnd());
979  const std::string lRawDataString (iPlace.getRawDataString());
980 
1005  const UNLOCodeList_T& lUNLOCodeList = iPlace.getUNLOCodeList();
1006  std::string lUNLOCodeStr ("");
1007  if (lUNLOCodeList.empty() == false) {
1008  const UNLOCode_T& lUNLOCode = lUNLOCodeList.front();
1009  lUNLOCodeStr = static_cast<const std::string> (lUNLOCode);
1010  }
1011 
1016  const UICCodeList_T& lUICCodeList = iPlace.getUICCodeList();
1017  UICCode_T lUICCodeInt = 0;
1018  if (lUICCodeList.empty() == false) {
1019  const UICCode_T& lUICCode = lUICCodeList.front();
1020  lUICCodeInt = static_cast<const UICCode_T> (lUICCode);
1021  }
1022 
1023 
1024  // DEBUG
1025  /*
1026  std::ostringstream oStr;
1027  oStr << "insert into optd_por values (" << lPK << ", ";
1028  oStr << lLocationType << ", ";
1029  oStr << lIataCode << ", " << lIcaoCode << ", " << lFaaCode << ", ";
1030  oStr << lUNLOCode << ", ";
1031  oStr << lUICCode << ", ";
1032  oStr << lIsGeonames << ", " << lGeonameID << ", ";
1033  oStr << lEnvID << ", " << lDateFrom << ", " << lDateEnd << ", ";
1034  oStr << lRawDataString << ")";
1035  OPENTREP_LOG_DEBUG ("Full SQL statement: '" << oStr.str() << "'");
1036  */
1037 
1038  ioSociSession << "insert into optd_por values (:pk, "
1039  << ":location_type, :iata_code, :icao_code, :faa_code, "
1040  << ":unlocode_code, :uic_code, "
1041  << ":is_geonames, :geoname_id, "
1042  << ":envelope_id, :date_from, :date_until, "
1043  << ":serialised_place)",
1044  soci::use (lPK), soci::use (lLocationType), soci::use (lIataCode),
1045  soci::use (lIcaoCode), soci::use (lFaaCode),
1046  soci::use (lUNLOCodeStr), soci::use (lUICCodeInt),
1047  soci::use (lIsGeonames), soci::use (lGeonameID),
1048  soci::use (lEnvID), soci::use (lDateFrom), soci::use (lDateEnd),
1049  soci::use (lRawDataString);
1050 
1051  // Commit the transaction on the database
1052  ioSociSession.commit();
1053 
1054  // Debug
1055  // OPENTREP_LOG_DEBUG ("[" << lDocID << "] " << iPlace);
1056 
1057  } catch (std::exception const& lException) {
1058  std::ostringstream errorStr;
1059  errorStr << "Error when updating " << iPlace.toString() << ": "
1060  << lException.what();
1061  OPENTREP_LOG_ERROR (errorStr.str());
1062  throw SQLDatabaseException (errorStr.str());
1063  }
1064  }
1065 
1066  // //////////////////////////////////////////////////////////////////////
1067  void DBManager::updatePlaceInDB (soci::session& ioSociSession,
1068  const Place& iPlace) {
1069 
1070  try {
1071 
1072  // Begin a transaction on the database
1073  ioSociSession.begin();
1074 
1075  // Instanciate a SQL statement (no request is performed at that stage)
1076  XapianDocID_T lDocID;
1077  std::string lIataCode;
1078  soci::statement lUpdateStatement =
1079  (ioSociSession.prepare
1080  << "update place_details "
1081  << "set xapian_docid = :xapian_docid "
1082  << "where iata_code = :iata_code",
1083  soci::use (lDocID), soci::use (lIataCode));
1084 
1085  // Execute the SQL query
1086  lDocID = iPlace.getDocID();
1087  lIataCode = iPlace.getIataCode();
1088  lUpdateStatement.execute (true);
1089 
1090  // Commit the transaction on the database
1091  ioSociSession.commit();
1092 
1093  // Debug
1094  // OPENTREP_LOG_DEBUG ("[" << lDocID << "] " << iPlace);
1095 
1096  } catch (std::exception const& lException) {
1097  std::ostringstream errorStr;
1098  errorStr << "Error when updating " << iPlace.toString() << ": "
1099  << lException.what();
1100  OPENTREP_LOG_ERROR (errorStr.str());
1101  throw SQLDatabaseException (errorStr.str());
1102  }
1103  }
1104 
1105  // //////////////////////////////////////////////////////////////////////
1106  NbOfDBEntries_T DBManager::displayCount (soci::session& ioSociSession) {
1107  NbOfDBEntries_T oNbOfEntries = 0;
1108 
1109  try {
1110 
1117  ioSociSession << "select count(1) from optd_por;", soci::into(oNbOfEntries);
1118 
1119  } catch (std::exception const& lException) {
1120  std::ostringstream errorStr;
1121  errorStr
1122  << "Error when trying to count the number of rows in the optd_por table: "
1123  << lException.what();
1124  OPENTREP_LOG_ERROR (errorStr.str());
1125  throw SQLDatabaseIndexCreationException (errorStr.str());
1126  }
1127 
1128  return oNbOfEntries;
1129  }
1130 
1131  // //////////////////////////////////////////////////////////////////////
1132  NbOfDBEntries_T DBManager::displayAll (soci::session& ioSociSession) {
1133  NbOfDBEntries_T oNbOfEntries = 0;
1134 
1135  try {
1136 
1137  // Prepare the SQL request corresponding to the select statement
1138  soci::statement lSelectStatement (ioSociSession);
1139  std::string lPlace =
1141  lSelectStatement);
1142 
1147  bool hasStillData = true;
1148  while (hasStillData == true) {
1149  hasStillData = iterateOnStatement (lSelectStatement, lPlace);
1150 
1151  // It is enough to have (at least) one database retrieved row
1152  if (hasStillData == true) {
1153  ++oNbOfEntries;
1154 
1155  // Debug
1156  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lPlace);
1157  }
1158  }
1159 
1160  } catch (std::exception const& lException) {
1161  std::ostringstream errorStr;
1162  errorStr << "Error when trying to retrieve " << oNbOfEntries
1163  << "-th row from the SQL database: " << lException.what();
1164  OPENTREP_LOG_ERROR (errorStr.str());
1165  throw SQLDatabaseException (errorStr.str());
1166  }
1167 
1168  return oNbOfEntries;
1169  }
1170 
1171  // //////////////////////////////////////////////////////////////////////
1172  NbOfDBEntries_T DBManager::getPORByIATACode (soci::session& ioSociSession,
1173  const IATACode_T& iIataCode,
1174  LocationList_T& ioLocationList,
1175  const bool iUniqueEntry) {
1176  NbOfDBEntries_T oNbOfEntries = 0;
1177  LocationList_T lLocationList;
1178 
1179  try {
1180 
1181  // Convert the code into uppercase. That way, one can search for codes
1182  // irrespective of the case (knwing that codes are stored uppercase
1183  // in the database)
1184  const std::string& lCode = static_cast<const std::string&> (iIataCode);
1185  const std::string lCodeUpper = boost::algorithm::to_upper_copy (lCode);
1186 
1187  // Prepare the SQL request corresponding to the select statement
1188  soci::statement lSelectStatement (ioSociSession);
1189  std::string lPlaceRawDataString;
1190  DBManager::prepareSelectBlobOnIataCodeStatement (ioSociSession,
1191  lSelectStatement,
1192  lCodeUpper,
1193  lPlaceRawDataString);
1194 
1199  bool hasStillData = true;
1200  while (hasStillData == true) {
1201  hasStillData = iterateOnStatement (lSelectStatement,
1202  lPlaceRawDataString);
1203 
1204  // DEBUG
1205  const std::string lFoundStr = hasStillData?"more; see below":"no more";
1206  OPENTREP_LOG_DEBUG ("Checked whether there are more locations "
1207  << "corresponding to '" << iIataCode
1208  << "' IATA code. Result: " << lFoundStr);
1209 
1210  if (hasStillData == true) {
1211  //
1212  ++oNbOfEntries;
1213 
1214  // Parse the POR details and create the corresponding
1215  // Location structure
1216  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1217  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1218  lLocation.setCorrectedKeywords (iIataCode);
1219 
1220  // Add the new found location to the list
1221  lLocationList.push_back (lLocation);
1222 
1223  // Debug
1224  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1225  }
1226  }
1227 
1228  } catch (std::exception const& lException) {
1229  std::ostringstream errorStr;
1230  errorStr << "Error when trying to retrieve a POR for " << iIataCode
1231  << " from the SQL database: " << lException.what();
1232  OPENTREP_LOG_ERROR (errorStr.str());
1233  throw SQLDatabaseException (errorStr.str());
1234  }
1235 
1236  // Add the just retrieved Location structure(s) to the list given
1237  // as parameter
1238  const Location* lHighestPRLocation_ptr = NULL;
1239  PageRank_T lHighestPRValue = 0.0;
1240  for (LocationList_T::const_iterator itLoc = lLocationList.begin();
1241  itLoc != lLocationList.end(); ++itLoc) {
1242  const Location& lLocation = *itLoc;
1243  const PageRank_T& lPRValue = lLocation.getPageRank();
1244 
1245  // Store (a pointer on) the Location structure with the highest Page Rank.
1246  // Normally, when there is no PageRank value, the field should be empty
1247  // (empty string). In some rare cases, actually when OPTD is buggy,
1248  // the PageRank value may be zero. While it is a bug from OPTD, there is
1249  // no reason it should trigger a bug from OpenTREP in turn. So, rather
1250  // then ignoring any POR with zero PageRank value, rather the first one
1251  // is (randomly) selected
1252  if (lPRValue >= lHighestPRValue) {
1253  lHighestPRLocation_ptr = &lLocation;
1254  lHighestPRValue = lPRValue;
1255  }
1256 
1257  // Add the Location structure now, only when a unique solution
1258  // is not expected
1259  if (iUniqueEntry == false) {
1260  ioLocationList.push_back (lLocation);
1261  }
1262  }
1263 
1264  // Add the Location structure with the highest Page Rank value
1265  if (iUniqueEntry == true && lHighestPRLocation_ptr != NULL) {
1266  assert (lHighestPRLocation_ptr != NULL);
1267  ioLocationList.push_back (*lHighestPRLocation_ptr);
1268 
1269  // DEBUG
1270  OPENTREP_LOG_DEBUG("Kept the location with the highest PageRank value ("
1271  << lHighestPRValue << ") for '" << iIataCode
1272  << "' IATA code: " << lHighestPRLocation_ptr->getKey());
1273  }
1274 
1275  // Make the number of retrieved locations consistent with the unicity
1276  // requirement (if set)
1277  if (oNbOfEntries > 0 && iUniqueEntry == true) {
1278  oNbOfEntries = 1;
1279  }
1280 
1281  //
1282  return oNbOfEntries;
1283  }
1284 
1285  // //////////////////////////////////////////////////////////////////////
1286  NbOfDBEntries_T DBManager::getPORByICAOCode (soci::session& ioSociSession,
1287  const ICAOCode_T& iIcaoCode,
1288  LocationList_T& ioLocationList) {
1289  NbOfDBEntries_T oNbOfEntries = 0;
1290 
1291  try {
1292 
1293  // Convert the code into uppercase. That way, one can search for codes
1294  // irrespective of the case (knwing that codes are stored uppercase
1295  // in the database)
1296  const std::string& lCode = static_cast<const std::string&> (iIcaoCode);
1297  const std::string lCodeUpper = boost::algorithm::to_upper_copy (lCode);
1298 
1299  // Prepare the SQL request corresponding to the select statement
1300  soci::statement lSelectStatement (ioSociSession);
1301  std::string lPlaceRawDataString;
1302  DBManager::prepareSelectBlobOnIcaoCodeStatement (ioSociSession,
1303  lSelectStatement,
1304  lCodeUpper,
1305  lPlaceRawDataString);
1306 
1311  bool hasStillData = true;
1312  while (hasStillData == true) {
1313  hasStillData = iterateOnStatement (lSelectStatement,
1314  lPlaceRawDataString);
1315 
1316  // DEBUG
1317  const std::string lFoundStr = hasStillData?"Yes":"No";
1318  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1319  << iIcaoCode << " ICAO code. Found: " << lFoundStr);
1320 
1321  if (hasStillData == true) {
1322  //
1323  ++oNbOfEntries;
1324 
1325  // Parse the POR details and create the corresponding
1326  // Location structure
1327  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1328  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1329  lLocation.setCorrectedKeywords (iIcaoCode);
1330 
1331  // Add the new found location to the list
1332  ioLocationList.push_back (lLocation);
1333 
1334  // Debug
1335  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1336  }
1337  }
1338 
1339  } catch (std::exception const& lException) {
1340  std::ostringstream errorStr;
1341  errorStr << "Error when trying to retrieve a POR for " << iIcaoCode
1342  << " from the SQL database: " << lException.what();
1343  OPENTREP_LOG_ERROR (errorStr.str());
1344  throw SQLDatabaseException (errorStr.str());
1345  }
1346 
1347  //
1348  return oNbOfEntries;
1349  }
1350 
1351  // //////////////////////////////////////////////////////////////////////
1352  NbOfDBEntries_T DBManager::getPORByFAACode (soci::session& ioSociSession,
1353  const FAACode_T& iFaaCode,
1354  LocationList_T& ioLocationList) {
1355  NbOfDBEntries_T oNbOfEntries = 0;
1356 
1357  try {
1358 
1359  // Convert the code into uppercase. That way, one can search for codes
1360  // irrespective of the case (knwing that codes are stored uppercase
1361  // in the database)
1362  const std::string& lCode = static_cast<const std::string&> (iFaaCode);
1363  const std::string lCodeUpper = boost::algorithm::to_upper_copy (lCode);
1364 
1365  // Prepare the SQL request corresponding to the select statement
1366  soci::statement lSelectStatement (ioSociSession);
1367  std::string lPlaceRawDataString;
1368  DBManager::prepareSelectBlobOnFaaCodeStatement (ioSociSession,
1369  lSelectStatement,
1370  lCodeUpper,
1371  lPlaceRawDataString);
1372 
1377  bool hasStillData = true;
1378  while (hasStillData == true) {
1379  hasStillData = iterateOnStatement (lSelectStatement,
1380  lPlaceRawDataString);
1381 
1382  // DEBUG
1383  const std::string lFoundStr = hasStillData?"Yes":"No";
1384  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1385  << iFaaCode << " FAA code. Found: " << lFoundStr);
1386 
1387  if (hasStillData == true) {
1388  //
1389  ++oNbOfEntries;
1390 
1391  // Parse the POR details and create the corresponding
1392  // Location structure
1393  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1394  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1395  lLocation.setCorrectedKeywords (iFaaCode);
1396 
1397  // Add the new found location to the list
1398  ioLocationList.push_back (lLocation);
1399 
1400  // Debug
1401  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1402  }
1403  }
1404 
1405  } catch (std::exception const& lException) {
1406  std::ostringstream errorStr;
1407  errorStr << "Error when trying to retrieve a POR for " << iFaaCode
1408  << " from the SQL database: " << lException.what();
1409  OPENTREP_LOG_ERROR (errorStr.str());
1410  throw SQLDatabaseException (errorStr.str());
1411  }
1412 
1413  //
1414  return oNbOfEntries;
1415  }
1416 
1417  // //////////////////////////////////////////////////////////////////////
1418  NbOfDBEntries_T DBManager::getPORByUNLOCode (soci::session& ioSociSession,
1419  const UNLOCode_T& iUNLOCode,
1420  LocationList_T& ioLocationList,
1421  const bool iUniqueEntry) {
1422  NbOfDBEntries_T oNbOfEntries = 0;
1423  LocationList_T lLocationList;
1424 
1425  try {
1426 
1427  // Convert the code into uppercase. That way, one can search for codes
1428  // irrespective of the case (knwing that codes are stored uppercase
1429  // in the database)
1430  const std::string& lCode = static_cast<const std::string&> (iUNLOCode);
1431  const std::string lCodeUpper = boost::algorithm::to_upper_copy (lCode);
1432 
1433  // Prepare the SQL request corresponding to the select statement
1434  soci::statement lSelectStatement (ioSociSession);
1435  std::string lPlaceRawDataString;
1436  DBManager::prepareSelectBlobOnUNLOCodeStatement (ioSociSession,
1437  lSelectStatement,
1438  lCodeUpper,
1439  lPlaceRawDataString);
1440 
1445  bool hasStillData = true;
1446  while (hasStillData == true) {
1447  hasStillData = iterateOnStatement (lSelectStatement,
1448  lPlaceRawDataString);
1449 
1450  // DEBUG
1451  const std::string lFoundStr = hasStillData?"Yes":"No";
1452  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1453  << iUNLOCode << " UN/LOCODE code. Found: "
1454  << lFoundStr);
1455 
1456  if (hasStillData == true) {
1457  //
1458  ++oNbOfEntries;
1459 
1460  // Parse the POR details and create the corresponding
1461  // Location structure
1462  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1463  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1464  lLocation.setCorrectedKeywords (iUNLOCode);
1465 
1466  // Add the new found location to the list
1467  lLocationList.push_back (lLocation);
1468 
1469  // Debug
1470  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1471  }
1472  }
1473 
1474  } catch (std::exception const& lException) {
1475  std::ostringstream errorStr;
1476  errorStr << "Error when trying to retrieve a POR for " << iUNLOCode
1477  << " from the SQL database: " << lException.what();
1478  OPENTREP_LOG_ERROR (errorStr.str());
1479  throw SQLDatabaseException (errorStr.str());
1480  }
1481 
1482  // Add the just retrieved Location structure(s) to the list given
1483  // as parameter
1484  const Location* lHighestPRLocation_ptr = NULL;
1485  PageRank_T lHighestPRValue = 0.0;
1486  for (LocationList_T::const_iterator itLoc = lLocationList.begin();
1487  itLoc != lLocationList.end(); ++itLoc) {
1488  const Location& lLocation = *itLoc;
1489  const PageRank_T& lPRValue = lLocation.getPageRank();
1490 
1491  // Store (a pointer on) the Location structure with the highest Page Rank
1492  if (lPRValue > lHighestPRValue) {
1493  lHighestPRLocation_ptr = &lLocation;
1494  lHighestPRValue = lPRValue;
1495  }
1496 
1497  // Add the Location structure now, only when
1498  if (iUniqueEntry == false) {
1499  ioLocationList.push_back (lLocation);
1500  }
1501  }
1502 
1503  // Add the Location structure with the highest Page Rank value
1504  if (iUniqueEntry == true && lHighestPRLocation_ptr != NULL) {
1505  assert (lHighestPRLocation_ptr != NULL);
1506  ioLocationList.push_back (*lHighestPRLocation_ptr);
1507 
1508  // DEBUG
1509  OPENTREP_LOG_DEBUG("Kept the location with the highest PageRank value ("
1510  << lHighestPRValue << ") for '" << iUNLOCode
1511  << "' IATA code: " << lHighestPRLocation_ptr->getKey());
1512  }
1513 
1514  // Make the number of retrieved locations consistent with the unicity
1515  // requirement (if set)
1516  if (oNbOfEntries > 0 && iUniqueEntry == true) {
1517  oNbOfEntries = 1;
1518  }
1519 
1520  //
1521  return oNbOfEntries;
1522  }
1523 
1524  // //////////////////////////////////////////////////////////////////////
1525  NbOfDBEntries_T DBManager::getPORByUICCode (soci::session& ioSociSession,
1526  const UICCode_T& iUICCode,
1527  LocationList_T& ioLocationList) {
1528  NbOfDBEntries_T oNbOfEntries = 0;
1529 
1530  try {
1531 
1532  // Prepare the SQL request corresponding to the select statement
1533  soci::statement lSelectStatement (ioSociSession);
1534  std::string lPlaceRawDataString;
1535  DBManager::prepareSelectBlobOnUICCodeStatement (ioSociSession,
1536  lSelectStatement,
1537  iUICCode,
1538  lPlaceRawDataString);
1539 
1544  bool hasStillData = true;
1545  while (hasStillData == true) {
1546  hasStillData = iterateOnStatement (lSelectStatement,
1547  lPlaceRawDataString);
1548 
1549  // DEBUG
1550  const std::string lFoundStr = hasStillData?"Yes":"No";
1551  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1552  << iUICCode << " UIC code. Found: "
1553  << lFoundStr);
1554 
1555  if (hasStillData == true) {
1556  //
1557  ++oNbOfEntries;
1558 
1559  // Parse the POR details and create the corresponding
1560  // Location structure
1561  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1562  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1563  const std::string lUICCodeStr =
1564  boost::lexical_cast<std::string> (iUICCode);
1565  lLocation.setCorrectedKeywords (lUICCodeStr);
1566 
1567  // Add the new found location to the list
1568  ioLocationList.push_back (lLocation);
1569 
1570  // Debug
1571  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1572  }
1573  }
1574 
1575  } catch (std::exception const& lException) {
1576  std::ostringstream errorStr;
1577  errorStr << "Error when trying to retrieve a POR for " << iUICCode
1578  << " from the SQL database: " << lException.what();
1579  OPENTREP_LOG_ERROR (errorStr.str());
1580  throw SQLDatabaseException (errorStr.str());
1581  }
1582 
1583  //
1584  return oNbOfEntries;
1585  }
1586 
1587  // //////////////////////////////////////////////////////////////////////
1588  NbOfDBEntries_T DBManager::getPORByGeonameID (soci::session& ioSociSession,
1589  const GeonamesID_T& iGeonameID,
1590  LocationList_T& ioLocationList) {
1591  NbOfDBEntries_T oNbOfEntries = 0;
1592 
1593  try {
1594 
1595  // Prepare the SQL request corresponding to the select statement
1596  soci::statement lSelectStatement (ioSociSession);
1597  std::string lPlaceRawDataString;
1598  DBManager::prepareSelectBlobOnPlaceGeoIDStatement (ioSociSession,
1599  lSelectStatement,
1600  iGeonameID,
1601  lPlaceRawDataString);
1602 
1607  bool hasStillData = true;
1608  while (hasStillData == true) {
1609  hasStillData = iterateOnStatement (lSelectStatement,
1610  lPlaceRawDataString);
1611 
1612  // DEBUG
1613  const std::string lFoundStr = hasStillData?"Yes":"No";
1614  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1615  << iGeonameID<< " Geonames ID. Found: "<< lFoundStr);
1616 
1617  if (hasStillData == true) {
1618  //
1619  ++oNbOfEntries;
1620 
1621  // Parse the POR details and create the corresponding
1622  // Location structure
1623  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1624  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1625  const std::string lGeonamesIDStr =
1626  boost::lexical_cast<std::string> (iGeonameID);
1627  lLocation.setCorrectedKeywords (lGeonamesIDStr);
1628 
1629  // Add the new found location to the list
1630  ioLocationList.push_back (lLocation);
1631 
1632  // Debug
1633  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1634  }
1635  }
1636 
1637  } catch (std::exception const& lException) {
1638  std::ostringstream errorStr;
1639  errorStr << "Error when trying to retrieve a POR for " << iGeonameID
1640  << " from the SQL database: " << lException.what();
1641  OPENTREP_LOG_ERROR (errorStr.str());
1642  throw SQLDatabaseException (errorStr.str());
1643  }
1644 
1645  //
1646  return oNbOfEntries;
1647  }
1648 
1649 }
OPENTREP::DBManager::terminateSQLDBSession
static void terminateSQLDBSession(const DBType &, const SQLDBConnectionString_T &, soci::session &)
Definition: DBManager.cpp:406
OPENTREP::FileManager::checkSQLiteDirectory
static bool checkSQLiteDirectory(const std::string &iSQLDBConnStr)
Definition: FileManager.cpp:16
OPENTREP::Place::getGeonamesID
const GeonamesID_T & getGeonamesID() const
Definition: Place.hpp:80
OPENTREP::IATAType::getTypeAsString
std::string getTypeAsString() const
Definition: IATAType.cpp:174
OPENTREP::DBType::MYSQL
@ MYSQL
Definition: DBType.hpp:22
OPENTREP::Place::getDateFrom
const Date_T & getDateFrom() const
Definition: Place.hpp:151
OPENTREP::DEFAULT_OPENTREP_MYSQL_DB_USER
const std::string DEFAULT_OPENTREP_MYSQL_DB_USER
OPENTREP::SQLDatabaseException
Definition: OPENTREP_exceptions.hpp:321
OPENTREP::DBType::SQLITE3
@ SQLITE3
Definition: DBType.hpp:21
OPENTREP::FileNotFoundException
Definition: OPENTREP_exceptions.hpp:145
PORFileHelper.hpp
OPENTREP::DBManager::getPORByUICCode
static NbOfDBEntries_T getPORByUICCode(soci::session &, const UICCode_T &, LocationList_T &)
Definition: DBManager.cpp:1525
DBManager.hpp
OPENTREP::NbOfDBEntries_T
unsigned int NbOfDBEntries_T
Definition: OPENTREP_Types.hpp:680
DbaPlace.hpp
OPENTREP::SQLDatabaseImpossibleConnectionException
Definition: OPENTREP_exceptions.hpp:345
OPENTREP::DBManager::createSQLDBUser
static bool createSQLDBUser(const DBType &, const SQLDBConnectionString_T &, const DeploymentNumber_T &)
Definition: DBManager.cpp:35
Result.hpp
OPENTREP::Place::getEnvelopeID
const EnvelopeID_T & getEnvelopeID() const
Definition: Place.hpp:144
Place.hpp
OPENTREP::DBType::describe
const std::string describe() const
Definition: DBType.cpp:131
OPENTREP::DBManager::getPORByIATACode
static NbOfDBEntries_T getPORByIATACode(soci::session &, const IATACode_T &, LocationList_T &, const bool iUniqueEntry)
Definition: DBManager.cpp:1172
OPENTREP::DBManager::initSQLDBSession
static soci::session * initSQLDBSession(const DBType &, const SQLDBConnectionString_T &)
Definition: DBManager.cpp:318
OPENTREP::UNLOCode_T
Definition: OPENTREP_Types.hpp:225
OPENTREP::Location::setCorrectedKeywords
void setCorrectedKeywords(const std::string &iCorrectedKeywords)
Definition: Location.hpp:846
OPENTREP::ICAOCode_T
Definition: OPENTREP_Types.hpp:170
OPENTREP::UICCode_T
unsigned int UICCode_T
Definition: OPENTREP_Types.hpp:238
OPENTREP::IATACode_T
Definition: OPENTREP_Types.hpp:154
OPENTREP::Place::getUICCodeList
const UICCodeList_T & getUICCodeList() const
Definition: Place.hpp:115
OPENTREP::SQLDBConnectionString_T
Definition: OPENTREP_Types.hpp:56
OPENTREP::SQLDatabaseIndexCreationException
Definition: OPENTREP_exceptions.hpp:417
OPENTREP::UNLOCodeList_T
std::list< UNLOCode_T > UNLOCodeList_T
Definition: OPENTREP_Types.hpp:229
OPENTREP::XapianDocID_T
unsigned int XapianDocID_T
Definition: OPENTREP_Types.hpp:650
OPENTREP::DBType::NODB
@ NODB
Definition: DBType.hpp:20
OPENTREP::DBManager::getPORByICAOCode
static NbOfDBEntries_T getPORByICAOCode(soci::session &, const ICAOCode_T &, LocationList_T &)
Definition: DBManager.cpp:1286
OPENTREP::RawDataString_T
Definition: OPENTREP_Types.hpp:76
OPENTREP_LOG_DEBUG
#define OPENTREP_LOG_DEBUG(iToBeLogged)
Definition: Logger.hpp:33
OPENTREP::DBManager::displayCount
static NbOfDBEntries_T displayCount(soci::session &)
Definition: DBManager.cpp:1106
OPENTREP::PageRank_T
double PageRank_T
Definition: OPENTREP_Types.hpp:630
OPENTREP::Location
Structure modelling a (geographical) location.
Definition: Location.hpp:25
OPENTREP::LocationList_T
std::list< Location > LocationList_T
Definition: LocationList.hpp:13
OPENTREP::SQLDatabaseConnectionReleaseException
Definition: OPENTREP_exceptions.hpp:357
OPENTREP::Place::getKey
const LocationKey & getKey() const
Definition: Place.hpp:59
OPENTREP::Place::isGeonames
const IsGeonames_T & isGeonames() const
Definition: Place.hpp:87
OPENTREP::UICCodeList_T
std::list< UICCode_T > UICCodeList_T
Definition: OPENTREP_Types.hpp:242
BasConst_OPENTREP_Service.hpp
OPENTREP::DEFAULT_OPENTREP_MYSQL_DB_HOST
const std::string DEFAULT_OPENTREP_MYSQL_DB_HOST
OPENTREP::DBManager::getPORByGeonameID
static NbOfDBEntries_T getPORByGeonameID(soci::session &, const GeonamesID_T &, LocationList_T &)
Definition: DBManager.cpp:1588
OPENTREP::Place::getIataType
const IATAType & getIataType() const
Definition: Place.hpp:73
OPENTREP::Place::getRawDataString
const RawDataString_T & getRawDataString() const
Definition: Place.hpp:467
OPENTREP
Definition: BasChronometer.cpp:10
OPENTREP::Place::getIataCode
const IATACode_T & getIataCode() const
Definition: Place.hpp:66
OPENTREP::DEFAULT_OPENTREP_MYSQL_DB_PASSWD
const std::string DEFAULT_OPENTREP_MYSQL_DB_PASSWD
OPENTREP::DBManager::displayAll
static NbOfDBEntries_T displayAll(soci::session &)
Definition: DBManager.cpp:1132
OPENTREP::Result::retrieveLocation
static Location retrieveLocation(const Xapian::Document &)
Definition: Result.cpp:272
OPENTREP::Location::getKey
const LocationKey & getKey() const
Definition: Location.hpp:31
OPENTREP::DEFAULT_OPENTREP_MYSQL_DB_DBNAME
const std::string DEFAULT_OPENTREP_MYSQL_DB_DBNAME
OPENTREP::Place::toString
std::string toString() const
Definition: Place.cpp:85
OPENTREP::Place::getDocID
const XapianDocID_T & getDocID() const
Definition: Place.hpp:474
Location.hpp
OPENTREP::Place::getUNLOCodeList
const UNLOCodeList_T & getUNLOCodeList() const
Definition: Place.hpp:108
OPENTREP::DBManager::getPORByUNLOCode
static NbOfDBEntries_T getPORByUNLOCode(soci::session &, const UNLOCode_T &, LocationList_T &, const bool iUniqueEntry)
Definition: DBManager.cpp:1418
OPENTREP::DBManager::updatePlaceInDB
static void updatePlaceInDB(soci::session &, const Place &)
Definition: DBManager.cpp:1067
OPENTREP::DBManager::insertPlaceInDB
static void insertPlaceInDB(soci::session &, const Place &)
Definition: DBManager.cpp:954
Logger.hpp
OPENTREP::Location::getPageRank
const PageRank_T & getPageRank() const
Definition: Location.hpp:354
OPENTREP::Place::getIcaoCode
const ICAOCode_T & getIcaoCode() const
Definition: Place.hpp:94
OPENTREP::Place::getFaaCode
const FAACode_T & getFaaCode() const
Definition: Place.hpp:101
OPENTREP::LocationKey
Class modelling the primary key of a location/POR (point of reference).
Definition: LocationKey.hpp:29
World.hpp
OPENTREP::SQLDatabaseFileCannotBeCreatedException
Definition: OPENTREP_exceptions.hpp:369
FacPlace.hpp
OPENTREP::DeploymentNumber_T
unsigned short DeploymentNumber_T
Definition: OPENTREP_Types.hpp:108
OPENTREP::Place
Class modelling a place/POR (point of reference).
Definition: Place.hpp:29
OPENTREP::DBManager::iterateOnStatement
static bool iterateOnStatement(soci::statement &, const std::string &)
Definition: DBManager.cpp:933
OPENTREP::IATAType
Enumeration of place/location types with respect to their use for transportation purposes.
Definition: IATAType.hpp:42
OPENTREP::Place::getDateEnd
const Date_T & getDateEnd() const
Definition: Place.hpp:158
OPENTREP::LocationKey::toString
std::string toString() const
Definition: LocationKey.cpp:60
OPENTREP::SQLDatabaseTableCreationException
Definition: OPENTREP_exceptions.hpp:405
OPENTREP::DBType
Enumeration of database types.
Definition: DBType.hpp:17
OPENTREP_LOG_ERROR
#define OPENTREP_LOG_ERROR(iToBeLogged)
Definition: Logger.hpp:24
PORParserHelper.hpp
FileManager.hpp
OPENTREP::DBManager::prepareSelectAllBlobStatement
static std::string prepareSelectAllBlobStatement(soci::session &, soci::statement &)
Definition: DBManager.cpp:709
Utilities.hpp
OPENTREP::GeonamesID_T
unsigned int GeonamesID_T
Definition: OPENTREP_Types.hpp:182
OPENTREP::DBManager::createSQLDBTables
static void createSQLDBTables(soci::session &)
Definition: DBManager.cpp:460
OPENTREP::DBManager::getPORByFAACode
static NbOfDBEntries_T getPORByFAACode(soci::session &, const FAACode_T &, LocationList_T &)
Definition: DBManager.cpp:1352
OPENTREP::DBManager::createSQLDBIndexes
static void createSQLDBIndexes(soci::session &)
Definition: DBManager.cpp:595
OPENTREP::FAACode_T
Definition: OPENTREP_Types.hpp:199