OpenTREP Logo  0.07.4
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  // //////////////////////////////////////////////////////////////////////
34  bool DBManager::
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  // //////////////////////////////////////////////////////////////////////
405  void DBManager::
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 for the SQLite3 database");
646 
647  } else if (lDBType == DBType::MYSQL) {
648 
649  // DEBUG
650  OPENTREP_LOG_DEBUG ("Create the indices for the MySQL database");
651 
652  try {
653 
667  ioSociSession
668  << "alter table optd_por add unique index optd_por_pk (pk asc);";
669  ioSociSession
670  << "alter table optd_por add index optd_por_iata_code (iata_code asc);";
671  ioSociSession
672  << "alter table optd_por add index optd_por_iata_date (iata_code asc, date_from asc, date_until asc);";
673  ioSociSession
674  << "alter table optd_por add index optd_por_icao_code (icao_code asc);";
675  ioSociSession
676  << "alter table optd_por add index optd_por_geonameid (geoname_id asc);";
677  ioSociSession
678  << "alter table optd_por add index optd_por_unlocode_code (unlocode_code asc);";
679  ioSociSession
680  << "alter table optd_por add index optd_por_uic_code (uic_code asc);";
681 
682  } catch (std::exception const& lException) {
683  std::ostringstream errorStr;
684  errorStr << "Error when trying to create MySQL/MariaDB indices: "
685  << lException.what();
686  OPENTREP_LOG_ERROR (errorStr.str());
687  throw SQLDatabaseIndexCreationException (errorStr.str());
688  }
689 
690  // DEBUG
691  OPENTREP_LOG_DEBUG ("The indices have been created for the MySQL/MariaDB database");
692 
693  } else if (lDBType == DBType::NODB) {
694  // Do nothing
695 
696  } else {
697  std::ostringstream errorStr;
698  errorStr << "Error: the '" << lDBName
699  << "' SQL database type is not supported";
700  OPENTREP_LOG_ERROR (errorStr.str());
701  throw SQLDatabaseIndexCreationException (errorStr.str());
702  }
703  }
704 
705  // //////////////////////////////////////////////////////////////////////
706  std::string DBManager::
707  prepareSelectAllBlobStatement (soci::session& ioSociSession,
708  soci::statement& ioSelectStatement) {
709  std::string oSerialisedPlaceStr;
710 
711  try {
712 
713  // Instanciate a SQL statement (no request is performed at that stage)
718  ioSelectStatement = (ioSociSession.prepare
719  << "select serialised_place from optd_por",
720  soci::into (oSerialisedPlaceStr));
721 
722  // Execute the SQL query
723  ioSelectStatement.execute();
724 
725  } catch (std::exception const& lException) {
726  std::ostringstream errorStr;
727  errorStr
728  << "Error in the 'select serialised_place from optd_por' SQL request: "
729  << lException.what();
730  OPENTREP_LOG_ERROR (errorStr.str());
731  throw SQLDatabaseException (errorStr.str());
732  }
733 
734  //
735  return oSerialisedPlaceStr;
736  }
737 
738  // //////////////////////////////////////////////////////////////////////
739  void DBManager::
740  prepareSelectBlobOnIataCodeStatement (soci::session& ioSociSession,
741  soci::statement& ioSelectStatement,
742  const IATACode_T& iIataCode,
743  std::string& ioSerialisedPlaceStr) {
744  std::string oSerialisedPlaceStr;
745 
746  try {
747 
748  // Instanciate a SQL statement (no request is performed at that stage)
752  const std::string lCode = static_cast<std::string> (iIataCode);
753  const std::string lCodeUpper = boost::algorithm::to_upper_copy (lCode);
754 
755  ioSelectStatement = (ioSociSession.prepare
756  << "select serialised_place from optd_por "
757  << "where iata_code = :place_iata_code",
758  soci::into (ioSerialisedPlaceStr),
759  soci::use (lCodeUpper));
760 
761  // Execute the SQL query
762  ioSelectStatement.execute();
763 
764  } catch (std::exception const& lException) {
765  std::ostringstream errorStr;
766  errorStr
767  << "Error in the 'select serialised_place from optd_por' SQL request: "
768  << lException.what();
769  OPENTREP_LOG_ERROR (errorStr.str());
770  throw SQLDatabaseException (errorStr.str());
771  }
772  }
773 
774  // //////////////////////////////////////////////////////////////////////
775  void DBManager::
776  prepareSelectBlobOnIcaoCodeStatement (soci::session& ioSociSession,
777  soci::statement& ioSelectStatement,
778  const ICAOCode_T& iIcaoCode,
779  std::string& ioSerialisedPlaceStr) {
780  std::string oSerialisedPlaceStr;
781 
782  try {
783 
784  // Instanciate a SQL statement (no request is performed at that stage)
788  const std::string lCode = static_cast<std::string> (iIcaoCode);
789  const std::string lCodeUpper = boost::algorithm::to_upper_copy (lCode);
790 
791  ioSelectStatement = (ioSociSession.prepare
792  << "select serialised_place from optd_por "
793  << "where icao_code = :place_icao_code",
794  soci::into (ioSerialisedPlaceStr),
795  soci::use (lCodeUpper));
796 
797  // Execute the SQL query
798  ioSelectStatement.execute();
799 
800  } catch (std::exception const& lException) {
801  std::ostringstream errorStr;
802  errorStr
803  << "Error in the 'select serialised_place from optd_por' SQL request: "
804  << lException.what();
805  OPENTREP_LOG_ERROR (errorStr.str());
806  throw SQLDatabaseException (errorStr.str());
807  }
808  }
809 
810  // //////////////////////////////////////////////////////////////////////
811  void DBManager::
812  prepareSelectBlobOnFaaCodeStatement (soci::session& ioSociSession,
813  soci::statement& ioSelectStatement,
814  const FAACode_T& iFaaCode,
815  std::string& ioSerialisedPlaceStr) {
816  std::string oSerialisedPlaceStr;
817 
818  try {
819 
820  // Instanciate a SQL statement (no request is performed at that stage)
824  const std::string lCode = static_cast<std::string> (iFaaCode);
825  const std::string lCodeUpper = boost::algorithm::to_upper_copy (lCode);
826 
827  ioSelectStatement = (ioSociSession.prepare
828  << "select serialised_place from optd_por "
829  << "where faa_code = :place_faa_code",
830  soci::into (ioSerialisedPlaceStr),
831  soci::use (lCodeUpper));
832 
833  // Execute the SQL query
834  ioSelectStatement.execute();
835 
836  } catch (std::exception const& lException) {
837  std::ostringstream errorStr;
838  errorStr
839  << "Error in the 'select serialised_place from optd_por' SQL request: "
840  << lException.what();
841  OPENTREP_LOG_ERROR (errorStr.str());
842  throw SQLDatabaseException (errorStr.str());
843  }
844  }
845 
846  // //////////////////////////////////////////////////////////////////////
847  void DBManager::
848  prepareSelectBlobOnUNLOCodeStatement (soci::session& ioSociSession,
849  soci::statement& ioSelectStatement,
850  const UNLOCode_T& iUNLOCode,
851  std::string& ioSerialisedPlaceStr) {
852  std::string oSerialisedPlaceStr;
853 
854  try {
855 
856  // Instanciate a SQL statement (no request is performed at that stage)
860  const std::string lCode = static_cast<std::string> (iUNLOCode);
861  const std::string lCodeUpper = boost::algorithm::to_upper_copy (lCode);
862 
863  ioSelectStatement = (ioSociSession.prepare
864  << "select serialised_place from optd_por "
865  << "where unlocode_code = :place_unlocode_code",
866  soci::into (ioSerialisedPlaceStr),
867  soci::use (lCodeUpper));
868 
869  // Execute the SQL query
870  ioSelectStatement.execute();
871 
872  } catch (std::exception const& lException) {
873  std::ostringstream errorStr;
874  errorStr
875  << "Error in the 'select serialised_place from optd_por' SQL request: "
876  << lException.what();
877  OPENTREP_LOG_ERROR (errorStr.str());
878  throw SQLDatabaseException (errorStr.str());
879  }
880  }
881 
882  // //////////////////////////////////////////////////////////////////////
883  void DBManager::
884  prepareSelectBlobOnUICCodeStatement (soci::session& ioSociSession,
885  soci::statement& ioSelectStatement,
886  const UICCode_T& iUICCode,
887  std::string& ioSerialisedPlaceStr) {
888  std::string oSerialisedPlaceStr;
889 
890  try {
891 
892  // Instanciate a SQL statement (no request is performed at that stage)
897  ioSelectStatement = (ioSociSession.prepare
898  << "select serialised_place from optd_por "
899  << "where uic_code = :place_uic_code",
900  soci::into (ioSerialisedPlaceStr),
901  soci::use (iUICCode));
902 
903  // Execute the SQL query
904  ioSelectStatement.execute();
905 
906  } catch (std::exception const& lException) {
907  std::ostringstream errorStr;
908  errorStr
909  << "Error in the 'select serialised_place from optd_por' SQL request: "
910  << lException.what();
911  OPENTREP_LOG_ERROR (errorStr.str());
912  throw SQLDatabaseException (errorStr.str());
913  }
914  }
915 
916  // //////////////////////////////////////////////////////////////////////
917  void DBManager::
918  prepareSelectBlobOnPlaceGeoIDStatement (soci::session& ioSociSession,
919  soci::statement& ioSelectStatement,
920  const GeonamesID_T& iGeonameID,
921  std::string& ioSerialisedPlaceStr) {
922  std::string oSerialisedPlaceStr;
923 
924  try {
925 
926  // Instanciate a SQL statement (no request is performed at that stage)
931  ioSelectStatement = (ioSociSession.prepare
932  << "select serialised_place from optd_por "
933  << "where geoname_id = :place_geoname_id",
934  soci::into (ioSerialisedPlaceStr),
935  soci::use (iGeonameID));
936 
937  // Execute the SQL query
938  ioSelectStatement.execute();
939 
940  } catch (std::exception const& lException) {
941  std::ostringstream errorStr;
942  errorStr
943  << "Error in the 'select serialised_place from optd_por' SQL request: "
944  << lException.what();
945  OPENTREP_LOG_ERROR (errorStr.str());
946  throw SQLDatabaseException (errorStr.str());
947  }
948  }
949 
950  // //////////////////////////////////////////////////////////////////////
951  bool DBManager::iterateOnStatement (soci::statement& ioStatement,
952  const std::string& iSerialisedPlaceStr) {
953  bool hasStillData = false;
954 
955  try {
956 
957  // Retrieve the next row of Place object
958  hasStillData = ioStatement.fetch();
959 
960  } catch (std::exception const& lException) {
961  std::ostringstream errorStr;
962  errorStr << "Error when iterating on the SQL fetch: " << lException.what();
963  errorStr << ". The current place is: " << iSerialisedPlaceStr;
964  OPENTREP_LOG_ERROR (errorStr.str());
965  throw SQLDatabaseException (errorStr.str());
966  }
967 
968  return hasStillData;
969  }
970 
971  // //////////////////////////////////////////////////////////////////////
972  void DBManager::insertPlaceInDB (soci::session& ioSociSession,
973  const Place& iPlace) {
974 
975  try {
976 
977  // Begin a transaction on the database
978  ioSociSession.begin();
979 
980  // Instanciate a SQL statement (no request is performed at that stage)
981  const LocationKey& lLocationKey = iPlace.getKey();
982  const std::string lPK (lLocationKey.toString());
983  const IATAType& lIataType = iPlace.getIataType();
984  const std::string lLocationType (lIataType.getTypeAsString());
985  const std::string lIataCode (iPlace.getIataCode());
986  const std::string lIcaoCode (iPlace.getIcaoCode());
987  const std::string lFaaCode (iPlace.getFaaCode());
988  const std::string lIsGeonames ((iPlace.isGeonames())?"Y":"N");
989  const std::string lGeonameID =
990  boost::lexical_cast<std::string> (iPlace.getGeonamesID());
991  const std::string lEnvID =
992  boost::lexical_cast<std::string> (iPlace.getEnvelopeID());
993  const std::string lDateFrom =
994  boost::gregorian::to_iso_extended_string (iPlace.getDateFrom());
995  const std::string lDateEnd =
996  boost::gregorian::to_iso_extended_string (iPlace.getDateEnd());
997  const std::string lRawDataString (iPlace.getRawDataString());
998 
1023  const UNLOCodeList_T& lUNLOCodeList = iPlace.getUNLOCodeList();
1024  std::string lUNLOCodeStr ("");
1025  if (lUNLOCodeList.empty() == false) {
1026  const UNLOCode_T& lUNLOCode = lUNLOCodeList.front();
1027  lUNLOCodeStr = static_cast<const std::string> (lUNLOCode);
1028  }
1029 
1034  const UICCodeList_T& lUICCodeList = iPlace.getUICCodeList();
1035  UICCode_T lUICCodeInt = 0;
1036  if (lUICCodeList.empty() == false) {
1037  const UICCode_T& lUICCode = lUICCodeList.front();
1038  lUICCodeInt = static_cast<const UICCode_T> (lUICCode);
1039  }
1040 
1041 
1042  // DEBUG
1043  /*
1044  std::ostringstream oStr;
1045  oStr << "insert into optd_por values (" << lPK << ", ";
1046  oStr << lLocationType << ", ";
1047  oStr << lIataCode << ", " << lIcaoCode << ", " << lFaaCode << ", ";
1048  oStr << lUNLOCode << ", ";
1049  oStr << lUICCode << ", ";
1050  oStr << lIsGeonames << ", " << lGeonameID << ", ";
1051  oStr << lEnvID << ", " << lDateFrom << ", " << lDateEnd << ", ";
1052  oStr << lRawDataString << ")";
1053  OPENTREP_LOG_DEBUG ("Full SQL statement: '" << oStr.str() << "'");
1054  */
1055 
1056  ioSociSession << "insert into optd_por values (:pk, "
1057  << ":location_type, :iata_code, :icao_code, :faa_code, "
1058  << ":unlocode_code, :uic_code, "
1059  << ":is_geonames, :geoname_id, "
1060  << ":envelope_id, :date_from, :date_until, "
1061  << ":serialised_place)",
1062  soci::use (lPK), soci::use (lLocationType), soci::use (lIataCode),
1063  soci::use (lIcaoCode), soci::use (lFaaCode),
1064  soci::use (lUNLOCodeStr), soci::use (lUICCodeInt),
1065  soci::use (lIsGeonames), soci::use (lGeonameID),
1066  soci::use (lEnvID), soci::use (lDateFrom), soci::use (lDateEnd),
1067  soci::use (lRawDataString);
1068 
1069  // Commit the transaction on the database
1070  ioSociSession.commit();
1071 
1072  // Debug
1073  // OPENTREP_LOG_DEBUG ("[" << lDocID << "] " << iPlace);
1074 
1075  } catch (std::exception const& lException) {
1076  std::ostringstream errorStr;
1077  errorStr << "Error when updating " << iPlace.toString() << ": "
1078  << lException.what();
1079  OPENTREP_LOG_ERROR (errorStr.str());
1080  throw SQLDatabaseException (errorStr.str());
1081  }
1082  }
1083 
1084  // //////////////////////////////////////////////////////////////////////
1085  void DBManager::updatePlaceInDB (soci::session& ioSociSession,
1086  const Place& iPlace) {
1087 
1088  try {
1089 
1090  // Begin a transaction on the database
1091  ioSociSession.begin();
1092 
1093  // Instanciate a SQL statement (no request is performed at that stage)
1094  XapianDocID_T lDocID;
1095  std::string lIataCode;
1096  soci::statement lUpdateStatement =
1097  (ioSociSession.prepare
1098  << "update place_details "
1099  << "set xapian_docid = :xapian_docid "
1100  << "where iata_code = :iata_code",
1101  soci::use (lDocID), soci::use (lIataCode));
1102 
1103  // Execute the SQL query
1104  lDocID = iPlace.getDocID();
1105  lIataCode = iPlace.getIataCode();
1106  lUpdateStatement.execute (true);
1107 
1108  // Commit the transaction on the database
1109  ioSociSession.commit();
1110 
1111  // Debug
1112  // OPENTREP_LOG_DEBUG ("[" << lDocID << "] " << iPlace);
1113 
1114  } catch (std::exception const& lException) {
1115  std::ostringstream errorStr;
1116  errorStr << "Error when updating " << iPlace.toString() << ": "
1117  << lException.what();
1118  OPENTREP_LOG_ERROR (errorStr.str());
1119  throw SQLDatabaseException (errorStr.str());
1120  }
1121  }
1122 
1123  // //////////////////////////////////////////////////////////////////////
1124  NbOfDBEntries_T DBManager::displayCount (soci::session& ioSociSession) {
1125  NbOfDBEntries_T oNbOfEntries = 0;
1126 
1127  try {
1128 
1135  ioSociSession << "select count(1) from optd_por;", soci::into(oNbOfEntries);
1136 
1137  } catch (std::exception const& lException) {
1138  std::ostringstream errorStr;
1139  errorStr
1140  << "Error when trying to count the number of rows in the optd_por table: "
1141  << lException.what();
1142  OPENTREP_LOG_ERROR (errorStr.str());
1143  throw SQLDatabaseIndexCreationException (errorStr.str());
1144  }
1145 
1146  return oNbOfEntries;
1147  }
1148 
1149  // //////////////////////////////////////////////////////////////////////
1150  NbOfDBEntries_T DBManager::displayAll (soci::session& ioSociSession) {
1151  NbOfDBEntries_T oNbOfEntries = 0;
1152 
1153  try {
1154 
1155  // Prepare the SQL request corresponding to the select statement
1156  soci::statement lSelectStatement (ioSociSession);
1157  std::string lPlace =
1159  lSelectStatement);
1160 
1165  bool hasStillData = true;
1166  while (hasStillData == true) {
1167  hasStillData = iterateOnStatement (lSelectStatement, lPlace);
1168 
1169  // It is enough to have (at least) one database retrieved row
1170  if (hasStillData == true) {
1171  ++oNbOfEntries;
1172 
1173  // Debug
1174  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lPlace);
1175  }
1176  }
1177 
1178  } catch (std::exception const& lException) {
1179  std::ostringstream errorStr;
1180  errorStr << "Error when trying to retrieve " << oNbOfEntries
1181  << "-th row from the SQL database: " << lException.what();
1182  OPENTREP_LOG_ERROR (errorStr.str());
1183  throw SQLDatabaseException (errorStr.str());
1184  }
1185 
1186  return oNbOfEntries;
1187  }
1188 
1189  // //////////////////////////////////////////////////////////////////////
1190  NbOfDBEntries_T DBManager::getPORByIATACode (soci::session& ioSociSession,
1191  const IATACode_T& iIataCode,
1192  LocationList_T& ioLocationList,
1193  const bool iUniqueEntry) {
1194  NbOfDBEntries_T oNbOfEntries = 0;
1195  LocationList_T lLocationList;
1196 
1197  try {
1198 
1199  // Prepare the SQL request corresponding to the select statement
1200  soci::statement lSelectStatement (ioSociSession);
1201  std::string lPlaceRawDataString;
1202  DBManager::prepareSelectBlobOnIataCodeStatement (ioSociSession,
1203  lSelectStatement,
1204  iIataCode,
1205  lPlaceRawDataString);
1206 
1211  bool hasStillData = true;
1212  while (hasStillData == true) {
1213  hasStillData = iterateOnStatement (lSelectStatement,
1214  lPlaceRawDataString);
1215 
1216  // DEBUG
1217  const std::string lFoundStr = hasStillData?"more; see below":"no more";
1218  OPENTREP_LOG_DEBUG ("Checked whether there are more locations "
1219  << "corresponding to '" << iIataCode
1220  << "' IATA code. Result: " << lFoundStr);
1221 
1222  if (hasStillData == true) {
1223  //
1224  ++oNbOfEntries;
1225 
1226  // Parse the POR details and create the corresponding
1227  // Location structure
1228  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1229  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1230  lLocation.setCorrectedKeywords (iIataCode);
1231 
1232  // Add the new found location to the list
1233  lLocationList.push_back (lLocation);
1234 
1235  // Debug
1236  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1237  }
1238  }
1239 
1240  } catch (std::exception const& lException) {
1241  std::ostringstream errorStr;
1242  errorStr << "Error when trying to retrieve a POR for " << iIataCode
1243  << " from the SQL database: " << lException.what();
1244  OPENTREP_LOG_ERROR (errorStr.str());
1245  throw SQLDatabaseException (errorStr.str());
1246  }
1247 
1248  // Add the just retrieved Location structure(s) to the list given
1249  // as parameter
1250  const Location* lHighestPRLocation_ptr = NULL;
1251  PageRank_T lHighestPRValue = 0.0;
1252  for (LocationList_T::const_iterator itLoc = lLocationList.begin();
1253  itLoc != lLocationList.end(); ++itLoc) {
1254  const Location& lLocation = *itLoc;
1255  const PageRank_T& lPRValue = lLocation.getPageRank();
1256 
1257  // Store (a pointer on) the Location structure with the highest Page Rank
1258  if (lPRValue > lHighestPRValue) {
1259  lHighestPRLocation_ptr = &lLocation;
1260  lHighestPRValue = lPRValue;
1261  }
1262 
1263  // Add the Location structure now, only when
1264  if (iUniqueEntry == false) {
1265  ioLocationList.push_back (lLocation);
1266  }
1267  }
1268 
1269  // Add the Location structure with the highest Page Rank value
1270  if (iUniqueEntry == true && lHighestPRLocation_ptr != NULL) {
1271  assert (lHighestPRLocation_ptr != NULL);
1272  ioLocationList.push_back (*lHighestPRLocation_ptr);
1273 
1274  // DEBUG
1275  OPENTREP_LOG_DEBUG("Kept the location with the highest PageRank value ("
1276  << lHighestPRValue << ") for '" << iIataCode
1277  << "' IATA code: " << lHighestPRLocation_ptr->getKey());
1278  }
1279 
1280  //
1281  return oNbOfEntries;
1282  }
1283 
1284  // //////////////////////////////////////////////////////////////////////
1285  NbOfDBEntries_T DBManager::getPORByICAOCode (soci::session& ioSociSession,
1286  const ICAOCode_T& iIcaoCode,
1287  LocationList_T& ioLocationList) {
1288  NbOfDBEntries_T oNbOfEntries = 0;
1289 
1290  try {
1291 
1292  // Prepare the SQL request corresponding to the select statement
1293  soci::statement lSelectStatement (ioSociSession);
1294  std::string lPlaceRawDataString;
1295  DBManager::prepareSelectBlobOnIcaoCodeStatement (ioSociSession,
1296  lSelectStatement,
1297  iIcaoCode,
1298  lPlaceRawDataString);
1299 
1304  bool hasStillData = true;
1305  while (hasStillData == true) {
1306  hasStillData = iterateOnStatement (lSelectStatement,
1307  lPlaceRawDataString);
1308 
1309  // DEBUG
1310  const std::string lFoundStr = hasStillData?"Yes":"No";
1311  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1312  << iIcaoCode << " ICAO code. Found: " << lFoundStr);
1313 
1314  if (hasStillData == true) {
1315  //
1316  ++oNbOfEntries;
1317 
1318  // Parse the POR details and create the corresponding
1319  // Location structure
1320  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1321  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1322  lLocation.setCorrectedKeywords (iIcaoCode);
1323 
1324  // Add the new found location to the list
1325  ioLocationList.push_back (lLocation);
1326 
1327  // Debug
1328  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1329  }
1330  }
1331 
1332  } catch (std::exception const& lException) {
1333  std::ostringstream errorStr;
1334  errorStr << "Error when trying to retrieve a POR for " << iIcaoCode
1335  << " from the SQL database: " << lException.what();
1336  OPENTREP_LOG_ERROR (errorStr.str());
1337  throw SQLDatabaseException (errorStr.str());
1338  }
1339 
1340  //
1341  return oNbOfEntries;
1342  }
1343 
1344  // //////////////////////////////////////////////////////////////////////
1345  NbOfDBEntries_T DBManager::getPORByFAACode (soci::session& ioSociSession,
1346  const FAACode_T& iFaaCode,
1347  LocationList_T& ioLocationList) {
1348  NbOfDBEntries_T oNbOfEntries = 0;
1349 
1350  try {
1351 
1352  // Prepare the SQL request corresponding to the select statement
1353  soci::statement lSelectStatement (ioSociSession);
1354  std::string lPlaceRawDataString;
1355  DBManager::prepareSelectBlobOnFaaCodeStatement (ioSociSession,
1356  lSelectStatement,
1357  iFaaCode,
1358  lPlaceRawDataString);
1359 
1364  bool hasStillData = true;
1365  while (hasStillData == true) {
1366  hasStillData = iterateOnStatement (lSelectStatement,
1367  lPlaceRawDataString);
1368 
1369  // DEBUG
1370  const std::string lFoundStr = hasStillData?"Yes":"No";
1371  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1372  << iFaaCode << " FAA code. Found: " << lFoundStr);
1373 
1374  if (hasStillData == true) {
1375  //
1376  ++oNbOfEntries;
1377 
1378  // Parse the POR details and create the corresponding
1379  // Location structure
1380  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1381  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1382  lLocation.setCorrectedKeywords (iFaaCode);
1383 
1384  // Add the new found location to the list
1385  ioLocationList.push_back (lLocation);
1386 
1387  // Debug
1388  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1389  }
1390  }
1391 
1392  } catch (std::exception const& lException) {
1393  std::ostringstream errorStr;
1394  errorStr << "Error when trying to retrieve a POR for " << iFaaCode
1395  << " from the SQL database: " << lException.what();
1396  OPENTREP_LOG_ERROR (errorStr.str());
1397  throw SQLDatabaseException (errorStr.str());
1398  }
1399 
1400  //
1401  return oNbOfEntries;
1402  }
1403 
1404  // //////////////////////////////////////////////////////////////////////
1405  NbOfDBEntries_T DBManager::getPORByUNLOCode (soci::session& ioSociSession,
1406  const UNLOCode_T& iUNLOCode,
1407  LocationList_T& ioLocationList) {
1408  NbOfDBEntries_T oNbOfEntries = 0;
1409 
1410  try {
1411 
1412  // Prepare the SQL request corresponding to the select statement
1413  soci::statement lSelectStatement (ioSociSession);
1414  std::string lPlaceRawDataString;
1415  DBManager::prepareSelectBlobOnUNLOCodeStatement (ioSociSession,
1416  lSelectStatement,
1417  iUNLOCode,
1418  lPlaceRawDataString);
1419 
1424  bool hasStillData = true;
1425  while (hasStillData == true) {
1426  hasStillData = iterateOnStatement (lSelectStatement,
1427  lPlaceRawDataString);
1428 
1429  // DEBUG
1430  const std::string lFoundStr = hasStillData?"Yes":"No";
1431  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1432  << iUNLOCode << " UN/LOCODE code. Found: "
1433  << lFoundStr);
1434 
1435  if (hasStillData == true) {
1436  //
1437  ++oNbOfEntries;
1438 
1439  // Parse the POR details and create the corresponding
1440  // Location structure
1441  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1442  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1443  lLocation.setCorrectedKeywords (iUNLOCode);
1444 
1445  // Add the new found location to the list
1446  ioLocationList.push_back (lLocation);
1447 
1448  // Debug
1449  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1450  }
1451  }
1452 
1453  } catch (std::exception const& lException) {
1454  std::ostringstream errorStr;
1455  errorStr << "Error when trying to retrieve a POR for " << iUNLOCode
1456  << " from the SQL database: " << lException.what();
1457  OPENTREP_LOG_ERROR (errorStr.str());
1458  throw SQLDatabaseException (errorStr.str());
1459  }
1460 
1461  //
1462  return oNbOfEntries;
1463  }
1464 
1465  // //////////////////////////////////////////////////////////////////////
1466  NbOfDBEntries_T DBManager::getPORByUICCode (soci::session& ioSociSession,
1467  const UICCode_T& iUICCode,
1468  LocationList_T& ioLocationList) {
1469  NbOfDBEntries_T oNbOfEntries = 0;
1470 
1471  try {
1472 
1473  // Prepare the SQL request corresponding to the select statement
1474  soci::statement lSelectStatement (ioSociSession);
1475  std::string lPlaceRawDataString;
1476  DBManager::prepareSelectBlobOnUICCodeStatement (ioSociSession,
1477  lSelectStatement,
1478  iUICCode,
1479  lPlaceRawDataString);
1480 
1485  bool hasStillData = true;
1486  while (hasStillData == true) {
1487  hasStillData = iterateOnStatement (lSelectStatement,
1488  lPlaceRawDataString);
1489 
1490  // DEBUG
1491  const std::string lFoundStr = hasStillData?"Yes":"No";
1492  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1493  << iUICCode << " UIC code. Found: "
1494  << lFoundStr);
1495 
1496  if (hasStillData == true) {
1497  //
1498  ++oNbOfEntries;
1499 
1500  // Parse the POR details and create the corresponding
1501  // Location structure
1502  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1503  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1504  const std::string lUICCodeStr =
1505  boost::lexical_cast<std::string> (iUICCode);
1506  lLocation.setCorrectedKeywords (lUICCodeStr);
1507 
1508  // Add the new found location to the list
1509  ioLocationList.push_back (lLocation);
1510 
1511  // Debug
1512  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1513  }
1514  }
1515 
1516  } catch (std::exception const& lException) {
1517  std::ostringstream errorStr;
1518  errorStr << "Error when trying to retrieve a POR for " << iUICCode
1519  << " from the SQL database: " << lException.what();
1520  OPENTREP_LOG_ERROR (errorStr.str());
1521  throw SQLDatabaseException (errorStr.str());
1522  }
1523 
1524  //
1525  return oNbOfEntries;
1526  }
1527 
1528  // //////////////////////////////////////////////////////////////////////
1529  NbOfDBEntries_T DBManager::getPORByGeonameID (soci::session& ioSociSession,
1530  const GeonamesID_T& iGeonameID,
1531  LocationList_T& ioLocationList) {
1532  NbOfDBEntries_T oNbOfEntries = 0;
1533 
1534  try {
1535 
1536  // Prepare the SQL request corresponding to the select statement
1537  soci::statement lSelectStatement (ioSociSession);
1538  std::string lPlaceRawDataString;
1539  DBManager::prepareSelectBlobOnPlaceGeoIDStatement (ioSociSession,
1540  lSelectStatement,
1541  iGeonameID,
1542  lPlaceRawDataString);
1543 
1548  bool hasStillData = true;
1549  while (hasStillData == true) {
1550  hasStillData = iterateOnStatement (lSelectStatement,
1551  lPlaceRawDataString);
1552 
1553  // DEBUG
1554  const std::string lFoundStr = hasStillData?"Yes":"No";
1555  OPENTREP_LOG_DEBUG ("Checked locations corresponding to "
1556  << iGeonameID<< " Geonames ID. Found: "<< lFoundStr);
1557 
1558  if (hasStillData == true) {
1559  //
1560  ++oNbOfEntries;
1561 
1562  // Parse the POR details and create the corresponding
1563  // Location structure
1564  const RawDataString_T lPlaceRawData (lPlaceRawDataString);
1565  Location lLocation = Result::retrieveLocation (lPlaceRawData);
1566  const std::string lGeonamesIDStr =
1567  boost::lexical_cast<std::string> (iGeonameID);
1568  lLocation.setCorrectedKeywords (lGeonamesIDStr);
1569 
1570  // Add the new found location to the list
1571  ioLocationList.push_back (lLocation);
1572 
1573  // Debug
1574  OPENTREP_LOG_DEBUG ("[" << oNbOfEntries << "] " << lLocation);
1575  }
1576  }
1577 
1578  } catch (std::exception const& lException) {
1579  std::ostringstream errorStr;
1580  errorStr << "Error when trying to retrieve a POR for " << iGeonameID
1581  << " from the SQL database: " << lException.what();
1582  OPENTREP_LOG_ERROR (errorStr.str());
1583  throw SQLDatabaseException (errorStr.str());
1584  }
1585 
1586  //
1587  return oNbOfEntries;
1588  }
1589 
1590 }
const EnvelopeID_T & getEnvelopeID() const
Definition: Place.hpp:144
Class modelling the primary key of a location/POR (point of reference).
Definition: LocationKey.hpp:29
const Date_T & getDateEnd() const
Definition: Place.hpp:158
#define OPENTREP_LOG_ERROR(iToBeLogged)
Definition: Logger.hpp:24
#define OPENTREP_LOG_DEBUG(iToBeLogged)
Definition: Logger.hpp:33
std::list< UNLOCode_T > UNLOCodeList_T
unsigned int GeonamesID_T
void setCorrectedKeywords(const std::string &iCorrectedKeywords)
Definition: Location.hpp:818
const GeonamesID_T & getGeonamesID() const
Definition: Place.hpp:80
static void updatePlaceInDB(soci::session &, const Place &)
Definition: DBManager.cpp:1085
const Date_T & getDateFrom() const
Definition: Place.hpp:151
const IATACode_T & getIataCode() const
Definition: Place.hpp:66
Structure modelling a (geographical) location.
Definition: Location.hpp:25
double PageRank_T
const std::string DEFAULT_OPENTREP_MYSQL_DB_HOST
std::string toString() const
Definition: Place.cpp:85
static NbOfDBEntries_T getPORByFAACode(soci::session &, const FAACode_T &, LocationList_T &)
Definition: DBManager.cpp:1345
std::list< UICCode_T > UICCodeList_T
static Location retrieveLocation(const Xapian::Document &)
Definition: Result.cpp:266
const std::string DEFAULT_OPENTREP_MYSQL_DB_USER
static bool checkSQLiteDirectory(const std::string &iSQLDBConnStr)
Definition: FileManager.cpp:16
unsigned int NbOfDBEntries_T
const std::string DEFAULT_OPENTREP_MYSQL_DB_DBNAME
const UNLOCodeList_T & getUNLOCodeList() const
Definition: Place.hpp:108
static bool createSQLDBUser(const DBType &, const SQLDBConnectionString_T &, const DeploymentNumber_T &)
Definition: DBManager.cpp:35
static std::string prepareSelectAllBlobStatement(soci::session &, soci::statement &)
Definition: DBManager.cpp:707
static void createSQLDBTables(soci::session &)
Definition: DBManager.cpp:460
const PageRank_T & getPageRank() const
Definition: Location.hpp:354
static void terminateSQLDBSession(const DBType &, const SQLDBConnectionString_T &, soci::session &)
Definition: DBManager.cpp:406
static NbOfDBEntries_T getPORByUICCode(soci::session &, const UICCode_T &, LocationList_T &)
Definition: DBManager.cpp:1466
Enumeration of database types.
Definition: DBType.hpp:17
unsigned int UICCode_T
const UICCodeList_T & getUICCodeList() const
Definition: Place.hpp:115
const ICAOCode_T & getIcaoCode() const
Definition: Place.hpp:94
static NbOfDBEntries_T getPORByIATACode(soci::session &, const IATACode_T &, LocationList_T &, const bool iUniqueEntry)
Definition: DBManager.cpp:1190
static NbOfDBEntries_T displayCount(soci::session &)
Definition: DBManager.cpp:1124
std::string toString() const
Definition: LocationKey.cpp:60
const std::string describe() const
Definition: DBType.cpp:131
Class modelling a place/POR (point of reference).
Definition: Place.hpp:29
unsigned int XapianDocID_T
std::list< Location > LocationList_T
const FAACode_T & getFaaCode() const
Definition: Place.hpp:101
const LocationKey & getKey() const
Definition: Place.hpp:59
static NbOfDBEntries_T getPORByUNLOCode(soci::session &, const UNLOCode_T &, LocationList_T &)
Definition: DBManager.cpp:1405
const RawDataString_T & getRawDataString() const
Definition: Place.hpp:453
static bool iterateOnStatement(soci::statement &, const std::string &)
Definition: DBManager.cpp:951
const IATAType & getIataType() const
Definition: Place.hpp:73
const std::string DEFAULT_OPENTREP_MYSQL_DB_PASSWD
const IsGeonames_T & isGeonames() const
Definition: Place.hpp:87
static NbOfDBEntries_T displayAll(soci::session &)
Definition: DBManager.cpp:1150
static NbOfDBEntries_T getPORByGeonameID(soci::session &, const GeonamesID_T &, LocationList_T &)
Definition: DBManager.cpp:1529
const XapianDocID_T & getDocID() const
Definition: Place.hpp:460
const LocationKey & getKey() const
Definition: Location.hpp:31
Enumeration of place/location types with respect to their use for transportation purposes.
Definition: IATAType.hpp:42
static NbOfDBEntries_T getPORByICAOCode(soci::session &, const ICAOCode_T &, LocationList_T &)
Definition: DBManager.cpp:1285
static void createSQLDBIndexes(soci::session &)
Definition: DBManager.cpp:595
static soci::session * initSQLDBSession(const DBType &, const SQLDBConnectionString_T &)
Definition: DBManager.cpp:318
unsigned short DeploymentNumber_T
static void insertPlaceInDB(soci::session &, const Place &)
Definition: DBManager.cpp:972