adatabase.cpp 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640
  1. /*
  2. *openPilot Log - A FOSS Pilot Logbook Application
  3. *Copyright (C) 2020 Felix Turowsky
  4. *
  5. *This program is free software: you can redistribute it and/or modify
  6. *it under the terms of the GNU General Public License as published by
  7. *the Free Software Foundation, either version 3 of the License, or
  8. *(at your option) any later version.
  9. *
  10. *This program is distributed in the hope that it will be useful,
  11. *but WITHOUT ANY WARRANTY; without even the implied warranty of
  12. *MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  13. *GNU General Public License for more details.
  14. *
  15. *You should have received a copy of the GNU General Public License
  16. *along with this program. If not, see <https://www.gnu.org/licenses/>.
  17. */
  18. #include "adatabase.h"
  19. #include "src/testing/adebug.h"
  20. #include "src/classes/astandardpaths.h"
  21. ADatabaseError::ADatabaseError(QString msg_)
  22. : QSqlError::QSqlError(msg_)
  23. {}
  24. QString ADatabaseError::text() const
  25. {
  26. return "Database Error: " + QSqlError::text();
  27. }
  28. ADatabase* ADatabase::self = nullptr;
  29. /*!
  30. * \brief Return the names of a given table in the database.
  31. */
  32. ColumnNames ADatabase::getTableColumns(TableName table_name) const
  33. {
  34. return tableColumns.value(table_name);
  35. }
  36. /*!
  37. * \brief Return the names of all tables in the database
  38. */
  39. TableNames ADatabase::getTableNames() const
  40. {
  41. return tableNames;
  42. }
  43. /*!
  44. * \brief Updates the member variables tableNames and tableColumns with up-to-date layout information
  45. * if the database has been altered. This function is normally only required during database setup or maintenance.
  46. */
  47. void ADatabase::updateLayout()
  48. {
  49. auto db = ADatabase::database();
  50. tableNames = db.tables();
  51. tableColumns.clear();
  52. for (const auto &table_name : tableNames) {
  53. ColumnNames table_columns;
  54. QSqlRecord fields = db.record(table_name);
  55. for (int i = 0; i < fields.count(); i++) {
  56. table_columns.append(fields.field(i).name());
  57. }
  58. tableColumns.insert(table_name, table_columns);
  59. }
  60. emit dataBaseUpdated();
  61. }
  62. ADatabase* ADatabase::instance()
  63. {
  64. #ifdef __GNUC__
  65. return self ?: self = new ADatabase();
  66. #else
  67. if(!self)
  68. self = new ADatabase();
  69. return self;
  70. #endif
  71. }
  72. ADatabase::ADatabase()
  73. : databaseDir(QDir(AStandardPaths::absPathOf(AStandardPaths::Database))),
  74. databaseFile(QFileInfo(databaseDir.filePath(QStringLiteral("logbook.db"))))
  75. {}
  76. /*!
  77. * \brief ADatabase::sqliteVersion returns database sqlite version.
  78. * \return sqlite version string
  79. */
  80. const QString ADatabase::sqliteVersion()
  81. {
  82. QSqlQuery query;
  83. query.prepare(QStringLiteral("SELECT sqlite_version()"));
  84. query.exec();
  85. query.next();
  86. return query.value(0).toString();
  87. }
  88. bool ADatabase::connect()
  89. {
  90. if (!QSqlDatabase::isDriverAvailable(SQLITE_DRIVER))
  91. return false;
  92. QSqlDatabase db = QSqlDatabase::addDatabase(SQLITE_DRIVER);
  93. db.setDatabaseName(databaseFile.absoluteFilePath());
  94. if (!db.open())
  95. return false;
  96. DEB << "Database connection established." << db.lastError().text();
  97. // Enable foreign key restrictions
  98. QSqlQuery query(QStringLiteral("PRAGMA foreign_keys = ON;"));
  99. updateLayout();
  100. return true;
  101. }
  102. void ADatabase::disconnect()
  103. {
  104. auto db = ADatabase::database();
  105. db.close();
  106. DEB << "Database connection closed.";
  107. }
  108. QSqlDatabase ADatabase::database()
  109. {
  110. return QSqlDatabase::database(QStringLiteral("qt_sql_default_connection"));
  111. }
  112. bool ADatabase::commit(AEntry entry)
  113. {
  114. if (exists(entry)) {
  115. return update(entry);
  116. } else {
  117. return insert(entry);
  118. }
  119. }
  120. bool ADatabase::remove(AEntry entry)
  121. {
  122. if (!exists(entry)) {
  123. DEB << "Error: Database entry not found.";
  124. lastError = ADatabaseError(QStringLiteral("Database entry not found."));
  125. return false;
  126. }
  127. QString statement = "DELETE FROM " + entry.getPosition().tableName +
  128. " WHERE ROWID=?";
  129. QSqlQuery query;
  130. query.prepare(statement);
  131. query.addBindValue(entry.getPosition().rowId);
  132. query.exec();
  133. if (query.lastError().type() == QSqlError::NoError)
  134. {
  135. DEB << "Entry " << entry.getPosition() << " removed.";
  136. emit dataBaseUpdated();
  137. lastError = QString();
  138. return true;
  139. } else {
  140. DEB << "Unable to delete.";
  141. DEB << "Query: " << statement;
  142. DEB << "Query Error: " << query.lastError().text();
  143. lastError = query.lastError().text();
  144. return false;
  145. }
  146. }
  147. bool ADatabase::removeMany(QList<DataPosition> data_position_list)
  148. {
  149. int errorCount = 0;
  150. QSqlQuery query;
  151. query.prepare(QStringLiteral("BEGIN EXCLUSIVE TRANSACTION"));
  152. query.exec();
  153. for (const auto& data_position : data_position_list) {
  154. if (!exists(data_position)) {
  155. lastError = ADatabaseError("Database entry not found.");
  156. errorCount++;
  157. }
  158. QString statement = "DELETE FROM " + data_position.tableName +
  159. " WHERE ROWID=?";
  160. query.prepare(statement);
  161. query.addBindValue(data_position.rowId);
  162. query.exec();
  163. if (!(query.lastError().type() == QSqlError::NoError))
  164. errorCount++;
  165. }
  166. if (errorCount == 0) {
  167. query.prepare(QStringLiteral("COMMIT"));
  168. query.exec();
  169. if(query.lastError().type() == QSqlError::NoError) {
  170. emit dataBaseUpdated();
  171. lastError = QString();
  172. return true;
  173. } else {
  174. lastError = "Transaction unsuccessful (Interrupted). Error count: " + QString::number(errorCount);
  175. return false;
  176. }
  177. } else {
  178. query.prepare(QStringLiteral("ROLLBACK"));
  179. query.exec();
  180. lastError = "Transaction unsuccessful (no changes have been made). Error count: " + QString::number(errorCount);
  181. return false;
  182. }
  183. }
  184. bool ADatabase::exists(AEntry entry)
  185. {
  186. if(entry.getPosition().rowId == 0)
  187. return false;
  188. //Check database for row id
  189. QString statement = "SELECT COUNT(*) FROM " + entry.getPosition().tableName +
  190. " WHERE ROWID=?";
  191. QSqlQuery query;
  192. query.prepare(statement);
  193. query.addBindValue(entry.getPosition().rowId);
  194. query.setForwardOnly(true);
  195. query.exec();
  196. //this returns either 1 or 0 since row ids are unique
  197. if (!query.isActive()) {
  198. lastError = query.lastError().text();
  199. DEB << "Query Error: " << query.lastError().text() << statement;
  200. return false;
  201. }
  202. query.next();
  203. int rowId = query.value(0).toInt();
  204. if (rowId) {
  205. return true;
  206. } else {
  207. DEB << "Database entry not found.";
  208. lastError = ADatabaseError(QStringLiteral("Database entry not found."));
  209. return false;
  210. }
  211. }
  212. bool ADatabase::exists(DataPosition data_position)
  213. {
  214. if(data_position.rowId == 0)
  215. return false;
  216. //Check database for row id
  217. QString statement = "SELECT COUNT(*) FROM " + data_position.tableName +
  218. " WHERE ROWID=?";
  219. QSqlQuery query;
  220. query.prepare(statement);
  221. query.addBindValue(data_position.rowId);
  222. query.setForwardOnly(true);
  223. query.exec();
  224. //this returns either 1 or 0 since row ids are unique
  225. if (!query.isActive()) {
  226. lastError = query.lastError().text();
  227. DEB << "Query Error: " << query.lastError().text() << statement;
  228. }
  229. query.next();
  230. int rowId = query.value(0).toInt();
  231. if (rowId) {
  232. return true;
  233. } else {
  234. DEB << "No entry exists at DataPosition: " << data_position.tableName << data_position.rowId;
  235. lastError = ADatabaseError(QStringLiteral("Database entry not found."));
  236. return false;
  237. }
  238. }
  239. bool ADatabase::update(AEntry updated_entry)
  240. {
  241. auto data = updated_entry.getData();
  242. QString statement = "UPDATE " + updated_entry.getPosition().tableName + " SET ";
  243. for (auto i = data.constBegin(); i != data.constEnd(); ++i) {
  244. statement.append(i.key() + "=?,");
  245. }
  246. statement.chop(1);
  247. statement.append(" WHERE ROWID=?");
  248. QSqlQuery query;
  249. query.prepare(statement);
  250. for (auto i = data.constBegin(); i != data.constEnd(); ++i) {
  251. if (i.value() == QVariant(QVariant::String)) {
  252. query.addBindValue(QVariant(QVariant::String));
  253. } else {
  254. query.addBindValue(i.value());
  255. }
  256. }
  257. query.addBindValue(updated_entry.getPosition().rowId);
  258. query.exec();
  259. if (query.lastError().type() == QSqlError::NoError)
  260. {
  261. DEB << "Entry successfully committed.";
  262. emit dataBaseUpdated();
  263. lastError = QString();
  264. return true;
  265. } else {
  266. DEB << "Unable to commit.";
  267. DEB << "Query: " << statement;
  268. DEB << "Query Error: " << query.lastError().text();
  269. lastError = query.lastError().text();
  270. return false;
  271. }
  272. }
  273. bool ADatabase::insert(AEntry new_entry)
  274. {
  275. auto data = new_entry.getData();
  276. QString statement = "INSERT INTO " + new_entry.getPosition().tableName + QLatin1String(" (");
  277. QMap<QString, QVariant>::iterator i;
  278. for (i = data.begin(); i != data.end(); ++i) {
  279. statement.append(i.key() + ',');
  280. }
  281. statement.chop(1);
  282. statement += QLatin1String(") VALUES (");
  283. for (int i=0; i < data.size(); ++i) {
  284. statement += QLatin1String("?,");
  285. }
  286. statement.chop(1);
  287. statement += ')';
  288. QSqlQuery query;
  289. query.prepare(statement);
  290. for (i = data.begin(); i != data.end(); ++i) {
  291. if (i.value() == "") {
  292. query.addBindValue(QVariant(QVariant::String));
  293. } else {
  294. query.addBindValue(i.value());
  295. }
  296. }
  297. query.exec();
  298. //check result.
  299. if (query.lastError().type() == QSqlError::NoError)
  300. {
  301. DEB << "Entry successfully committed.";
  302. emit dataBaseUpdated();
  303. lastError = QString();
  304. return true;
  305. } else {
  306. DEB << "Unable to commit.";
  307. DEB << "Query: " << statement;
  308. DEB << "Query Error: " << query.lastError().text();
  309. lastError = query.lastError().text();
  310. return false;
  311. }
  312. }
  313. RowData ADatabase::getEntryData(DataPosition data_position)
  314. {
  315. // check table exists
  316. if (!getTableNames().contains(data_position.tableName)) {
  317. DEB << data_position.tableName << " not a table in the database. Unable to retreive Entry data.";
  318. return RowData();
  319. }
  320. //Check Database for rowId
  321. QString statement = "SELECT COUNT(*) FROM " + data_position.tableName
  322. + " WHERE ROWID=?";
  323. QSqlQuery check_query;
  324. check_query.prepare(statement);
  325. check_query.addBindValue(data_position.rowId);
  326. check_query.setForwardOnly(true);
  327. check_query.exec();
  328. if (check_query.lastError().type() != QSqlError::NoError) {
  329. DEB << "SQL error: " << check_query.lastError().text();
  330. DEB << "Statement: " << statement;
  331. lastError = check_query.lastError().text();
  332. return RowData();
  333. }
  334. check_query.next();
  335. if (check_query.value(0).toInt() == 0) {
  336. DEB << "No Entry found for row id: " << data_position.rowId;
  337. lastError = ADatabaseError("Database entry not found.");
  338. return RowData();
  339. }
  340. // Retreive TableData
  341. statement = "SELECT * FROM " + data_position.tableName
  342. + " WHERE ROWID=?";
  343. QSqlQuery select_query;
  344. select_query.prepare(statement);
  345. select_query.addBindValue(data_position.rowId);
  346. select_query.setForwardOnly(true);
  347. select_query.exec();
  348. if (select_query.lastError().type() != QSqlError::NoError) {
  349. DEB << "SQL error: " << select_query.lastError().text();
  350. DEB << "Statement: " << statement;
  351. lastError = select_query.lastError().text();
  352. return RowData();
  353. }
  354. select_query.next();
  355. RowData entry_data;
  356. for (const auto &column : getTableColumns(data_position.tableName)) {
  357. entry_data.insert(column, select_query.value(column));
  358. }
  359. return entry_data;
  360. }
  361. AEntry ADatabase::getEntry(DataPosition data_position)
  362. {
  363. AEntry entry(data_position);
  364. entry.setData(getEntryData(data_position));
  365. return entry;
  366. }
  367. APilotEntry ADatabase::getPilotEntry(RowId row_id)
  368. {
  369. APilotEntry pilot_entry(row_id);
  370. pilot_entry.setData(getEntryData(pilot_entry.getPosition()));
  371. return pilot_entry;
  372. }
  373. ATailEntry ADatabase::getTailEntry(RowId row_id)
  374. {
  375. ATailEntry tail_entry(row_id);
  376. tail_entry.setData(getEntryData(tail_entry.getPosition()));
  377. return tail_entry;
  378. }
  379. AAircraftEntry ADatabase::getAircraftEntry(RowId row_id)
  380. {
  381. AAircraftEntry aircraft_entry(row_id);
  382. aircraft_entry.setData(getEntryData(aircraft_entry.getPosition()));
  383. return aircraft_entry;
  384. }
  385. AFlightEntry ADatabase::getFlightEntry(RowId row_id)
  386. {
  387. AFlightEntry flight_entry(row_id);
  388. flight_entry.setData(getEntryData(flight_entry.getPosition()));
  389. return flight_entry;
  390. }
  391. const QStringList ADatabase::getCompletionList(ADatabaseTarget target)
  392. {
  393. QString statement;
  394. switch (target) {
  395. case ADatabaseTarget::pilots:
  396. statement.append(QStringLiteral("SELECT lastname||', '||firstname FROM pilots"));
  397. break;
  398. case ADatabaseTarget::aircraft:
  399. statement.append(QStringLiteral("SELECT make||' '||model FROM aircraft WHERE model IS NOT NULL "
  400. "UNION "
  401. "SELECT make||' '||model||'-'||variant FROM aircraft WHERE variant IS NOT NULL"));
  402. break;
  403. case ADatabaseTarget::airport_identifier_all:
  404. statement.append(QStringLiteral("SELECT icao FROM airports UNION SELECT iata FROM airports"));
  405. break;
  406. case ADatabaseTarget::registrations:
  407. statement.append(QStringLiteral("SELECT registration FROM tails"));
  408. break;
  409. case ADatabaseTarget::companies:
  410. statement.append(QStringLiteral("SELECT company FROM pilots"));
  411. break;
  412. default:
  413. DEB << "Not a valid completer target for this function.";
  414. return QStringList();
  415. }
  416. QSqlQuery query;
  417. query.prepare(statement);
  418. query.setForwardOnly(true);
  419. query.exec();
  420. if(!query.isActive()) {
  421. lastError = query.lastError().text();
  422. return QStringList();
  423. }
  424. QStringList completer_list;
  425. while (query.next())
  426. completer_list.append(query.value(0).toString());
  427. completer_list.sort();
  428. completer_list.removeAll(QString(""));
  429. completer_list.removeDuplicates();
  430. return completer_list;
  431. }
  432. const QMap<QString, int> ADatabase::getIdMap(ADatabaseTarget target)
  433. {
  434. QString statement;
  435. switch (target) {
  436. case ADatabaseTarget::pilots:
  437. statement.append(QStringLiteral("SELECT ROWID, lastname||', '||firstname FROM pilots"));
  438. break;
  439. case ADatabaseTarget::aircraft:
  440. statement.append(QStringLiteral("SELECT ROWID, make||' '||model FROM aircraft WHERE model IS NOT NULL "
  441. "UNION "
  442. "SELECT ROWID, make||' '||model||'-'||variant FROM aircraft WHERE variant IS NOT NULL"));
  443. break;
  444. case ADatabaseTarget::airport_identifier_icao:
  445. statement.append(QStringLiteral("SELECT ROWID, icao FROM airports"));
  446. break;
  447. case ADatabaseTarget::airport_identifier_iata:
  448. statement.append(QStringLiteral("SELECT ROWID, iata FROM airports WHERE iata NOT NULL"));
  449. break;
  450. case ADatabaseTarget::airport_names:
  451. statement.append(QStringLiteral("SELECT ROWID, name FROM airports"));
  452. break;
  453. case ADatabaseTarget::tails:
  454. statement.append(QStringLiteral("SELECT ROWID, registration FROM tails"));
  455. break;
  456. default:
  457. DEB << "Not a valid completer target for this function.";
  458. return QMap<QString, int>();
  459. }
  460. auto id_map = QMap<QString, int>();
  461. auto query = QSqlQuery(statement);
  462. if (!query.isActive()) {
  463. DEB << "No result found. Check Query and Error.";
  464. DEB << "Query: " << statement;
  465. DEB << "Error: " << query.lastError().text();
  466. lastError = query.lastError().text();
  467. return QMap<QString, int>();
  468. } else {
  469. QVector<QString> query_result;
  470. while (query.next()) {
  471. id_map.insert(query.value(1).toString(), query.value(0).toInt());
  472. }
  473. return id_map;
  474. }
  475. }
  476. int ADatabase::getLastEntry(ADatabaseTarget target)
  477. {
  478. QString statement = "SELECT MAX(ROWID) FROM ";
  479. switch (target) {
  480. case ADatabaseTarget::pilots:
  481. statement.append(DB_TABLE_PILOTS);
  482. break;
  483. case ADatabaseTarget::aircraft:
  484. statement.append(DB_TABLE_AIRCRAFT);
  485. break;
  486. case ADatabaseTarget::tails:
  487. statement.append(DB_TABLE_TAILS);
  488. break;
  489. default:
  490. DEB << "Not a valid completer target for this function.";
  491. return 0;
  492. }
  493. auto query = QSqlQuery(statement);
  494. if (query.first()) {
  495. return query.value(0).toInt();
  496. } else {
  497. lastError = ADatabaseError(QStringLiteral("Database entry not found."));
  498. DEB << "No entry found.";
  499. return 0;
  500. }
  501. }
  502. QList<int> ADatabase::getForeignKeyConstraints(int foreign_row_id, ADatabaseTarget target)
  503. {
  504. QString statement = "SELECT ROWID FROM flights WHERE ";
  505. switch (target) {
  506. case ADatabaseTarget::pilots:
  507. statement.append("pic=?");
  508. break;
  509. case ADatabaseTarget::tails:
  510. statement.append("acft=?");
  511. break;
  512. default:
  513. DEB << "Not a valid target for this function.";
  514. return QList<int>();
  515. break;
  516. }
  517. QSqlQuery query;
  518. query.prepare(statement);
  519. query.addBindValue(foreign_row_id);
  520. query.exec();
  521. if (!query.isActive()) {
  522. lastError = query.lastError().text();
  523. DEB << "Error";
  524. DEB << statement;
  525. DEB << query.lastError().text();
  526. return QList<int>();
  527. }
  528. QList<int> row_ids;
  529. while (query.next()) {
  530. row_ids.append(query.value(0).toInt());
  531. }
  532. return row_ids;
  533. }
  534. APilotEntry ADatabase::resolveForeignPilot(int foreign_key)
  535. {
  536. return aDB->getPilotEntry(foreign_key);
  537. }
  538. ATailEntry ADatabase::resolveForeignTail(int foreign_key)
  539. {
  540. return aDB->getTailEntry(foreign_key);
  541. }
  542. QVector<QString> ADatabase::customQuery(QString statement, int return_values)
  543. {
  544. QSqlQuery query(statement);
  545. query.exec();
  546. if (!query.first()) {
  547. DEB << "No result found. Check Query and Error.";
  548. DEB << "Error: " << query.lastError().text();
  549. DEB << "Statement: " << statement;
  550. lastError = query.lastError().text();
  551. return QVector<QString>();
  552. } else {
  553. query.first();
  554. query.previous();
  555. QVector<QString> result;
  556. while (query.next()) {
  557. for (int i = 0; i < return_values ; i++) {
  558. result.append(query.value(i).toString());
  559. }
  560. }
  561. emit dataBaseUpdated();
  562. lastError = QString();
  563. return result;
  564. }
  565. }