OpenTREP Logo  0.07.18
C++ Open Travel Request Parsing Library
Loading...
Searching...
No Matches
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>
30
31namespace 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
152
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
496
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
552
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
622
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
667
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)
719
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
1116
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}
#define OPENTREP_LOG_ERROR(iToBeLogged)
Definition Logger.hpp:24
#define OPENTREP_LOG_DEBUG(iToBeLogged)
Definition Logger.hpp:33
static void terminateSQLDBSession(const DBType &, const SQLDBConnectionString_T &, soci::session &)
static void createSQLDBTables(soci::session &)
static std::string prepareSelectAllBlobStatement(soci::session &, soci::statement &)
static NbOfDBEntries_T getPORByUICCode(soci::session &, const UICCode_T &, LocationList_T &)
static NbOfDBEntries_T getPORByICAOCode(soci::session &, const ICAOCode_T &, LocationList_T &)
static soci::session * initSQLDBSession(const DBType &, const SQLDBConnectionString_T &)
static NbOfDBEntries_T getPORByFAACode(soci::session &, const FAACode_T &, LocationList_T &)
static void createSQLDBIndexes(soci::session &)
static NbOfDBEntries_T displayCount(soci::session &)
static NbOfDBEntries_T displayAll(soci::session &)
static NbOfDBEntries_T getPORByUNLOCode(soci::session &, const UNLOCode_T &, LocationList_T &, const bool iUniqueEntry)
static NbOfDBEntries_T getPORByGeonameID(soci::session &, const GeonamesID_T &, LocationList_T &)
static NbOfDBEntries_T getPORByIATACode(soci::session &, const IATACode_T &, LocationList_T &, const bool iUniqueEntry)
static void updatePlaceInDB(soci::session &, const Place &)
static bool iterateOnStatement(soci::statement &, const std::string &)
static bool createSQLDBUser(const DBType &, const SQLDBConnectionString_T &, const DeploymentNumber_T &)
Definition DBManager.cpp:35
static void insertPlaceInDB(soci::session &, const Place &)
static bool checkSQLiteDirectory(const std::string &iSQLDBConnStr)
Class modelling a place/POR (point of reference).
Definition Place.hpp:29
const IsGeonames_T & isGeonames() const
Definition Place.hpp:87
const ICAOCode_T & getIcaoCode() const
Definition Place.hpp:94
const RawDataString_T & getRawDataString() const
Definition Place.hpp:467
const FAACode_T & getFaaCode() const
Definition Place.hpp:101
const Date_T & getDateEnd() const
Definition Place.hpp:158
const GeonamesID_T & getGeonamesID() const
Definition Place.hpp:80
const XapianDocID_T & getDocID() const
Definition Place.hpp:474
const IATACode_T & getIataCode() const
Definition Place.hpp:66
const Date_T & getDateFrom() const
Definition Place.hpp:151
const EnvelopeID_T & getEnvelopeID() const
Definition Place.hpp:144
const UNLOCodeList_T & getUNLOCodeList() const
Definition Place.hpp:108
std::string toString() const
Definition Place.cpp:85
const IATAType & getIataType() const
Definition Place.hpp:73
const LocationKey & getKey() const
Definition Place.hpp:59
const UICCodeList_T & getUICCodeList() const
Definition Place.hpp:115
static Location retrieveLocation(const Xapian::Document &)
Definition Result.cpp:272
unsigned int UICCode_T
unsigned int NbOfDBEntries_T
const std::string DEFAULT_OPENTREP_MYSQL_DB_DBNAME
const std::string DEFAULT_OPENTREP_MYSQL_DB_USER
double PageRank_T
const std::string DEFAULT_OPENTREP_MYSQL_DB_PASSWD
std::list< Location > LocationList_T
std::list< UICCode_T > UICCodeList_T
const std::string DEFAULT_OPENTREP_MYSQL_DB_HOST
std::list< UNLOCode_T > UNLOCodeList_T
unsigned short DeploymentNumber_T
unsigned int GeonamesID_T
unsigned int XapianDocID_T
Enumeration of database types.
Definition DBType.hpp:17
const std::string describe() const
Definition DBType.cpp:131
Enumeration of place/location types with respect to their use for transportation purposes.
Definition IATAType.hpp:42
std::string getTypeAsString() const
Definition IATAType.cpp:174
Class modelling the primary key of a location/POR (point of reference).
std::string toString() const
Structure modelling a (geographical) location.
Definition Location.hpp:25
const LocationKey & getKey() const
Definition Location.hpp:31
const PageRank_T & getPageRank() const
Definition Location.hpp:354
void setCorrectedKeywords(const std::string &iCorrectedKeywords)
Definition Location.hpp:846