database.h 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379
  1. /*
  2. *openPilotLog - A FOSS Pilot Logbook Application
  3. *Copyright (C) 2020-2023 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. #ifndef DATABASE_H
  19. #define DATABASE_H
  20. #include <QPair>
  21. #include <QHash>
  22. #include <QString>
  23. #include <QDir>
  24. #include <QSqlDatabase>
  25. #include <QSqlDriver>
  26. #include <QSqlQuery>
  27. #include <QSqlError>
  28. #include <QSqlTableModel>
  29. #include <QSqlQuery>
  30. #include <QSqlRecord>
  31. #include <QSqlField>
  32. #include "src/classes/paths.h"
  33. #include "src/database/aircraftentry.h"
  34. #include "src/database/airportentry.h"
  35. #include "src/database/currencyentry.h"
  36. #include "src/database/flightentry.h"
  37. #include "src/database/pilotentry.h"
  38. #include "src/database/simulatorentry.h"
  39. #include "src/database/tailentry.h"
  40. #include "src/opl.h"
  41. #include "src/database/row.h"
  42. namespace OPL {
  43. /*!
  44. * \brief Convenience macro that returns instance of DataBase.
  45. * Instead of this:
  46. * OPL::DataBase::getInstance().commit(...)
  47. * Use this:
  48. * DB->commit(...)
  49. */
  50. #define DB OPL::Database::instance()
  51. /*!
  52. * \brief The DB class encapsulates the SQL database by providing fast access
  53. * to hot database data.
  54. */
  55. class Database : public QObject {
  56. private:
  57. Q_OBJECT
  58. Database()
  59. : databaseFile(OPL::Paths::databaseFileInfo())
  60. {}
  61. static Database* self;
  62. const QFileInfo databaseFile;
  63. QStringList tableNames;
  64. QHash<QString, QStringList> tableColumns;
  65. inline const static QString SQLITE_DRIVER = QStringLiteral("QSQLITE");
  66. inline const static QList<OPL::DbTable> USER_TABLES = {
  67. OPL::DbTable::Flights,
  68. OPL::DbTable::Pilots,
  69. OPL::DbTable::Tails,
  70. OPL::DbTable::Currencies,
  71. };
  72. inline const static QList<OPL::DbTable> TEMPLATE_TABLES = {
  73. OPL::DbTable::Aircraft,
  74. OPL::DbTable::Airports,
  75. };
  76. public:
  77. Database(const Database&) = delete;
  78. void operator=(const Database&) = delete;
  79. static Database* instance();
  80. /*!
  81. * \brief Holds information about the last error that ocurred during
  82. * a SQL operation. If the error type is QSqlError::UnknownError, the error is related to data
  83. * from the database (entry not found,...), otherwise the error is related to SQL execution. In this
  84. * case error.type() provides further information.
  85. *
  86. * If the error type is QSqlError::NoError, the last executed database query was successful.
  87. */
  88. QSqlError lastError;
  89. /*!
  90. * \brief Connect to the database and populate database information.
  91. */
  92. bool connect();
  93. /*!
  94. * \brief closes the database connection.
  95. */
  96. void disconnect();
  97. /*!
  98. * \brief Updates the member variables tableNames and tableColumns with up-to-date layout information
  99. * if the database has been altered. This function is normally only required during database setup or maintenance.
  100. */
  101. void updateLayout();
  102. /*!
  103. * \brief Database::sqliteVersion returns the database sqlite version. See also dbRevision()
  104. * \return sqlite version string
  105. */
  106. const QString sqliteVersion() const;
  107. /*!
  108. * \brief Return the names of all tables in the database
  109. */
  110. const QStringList getTableNames() const;
  111. /*!
  112. * \brief Return the names of a given table in the database.
  113. */
  114. const QStringList getTableColumns(OPL::DbTable table_name) const;
  115. /*!
  116. * \brief Can be used to access the database connection.
  117. * \return The QSqlDatabase object pertaining to the connection.
  118. */
  119. static QSqlDatabase database();
  120. /*!
  121. * \brief Can be used to send a complex query to the database.
  122. * \param query - the full sql query statement
  123. * \param returnValues - the number of return values
  124. */
  125. QVector<QVariant> customQuery(QString statement, int return_values);
  126. /*!
  127. * \brief Checks if an entry exists in the database, based on position data
  128. */
  129. bool exists(const OPL::Row &row);
  130. /*!
  131. * \brief clear resets the database, i.e. deletes all content in the tables containing
  132. * userdata (pilots, flights, tails)
  133. */
  134. bool clear();
  135. /*!
  136. * \brief commits an entry to the database, calls either insert or update,
  137. * based on position data
  138. */
  139. bool commit(const OPL::Row &row);
  140. /*!
  141. * \brief commits data imported from JSON
  142. * \details This function is used to import values to the databases which are held in JSON documents.
  143. * These entries are pre-filled data used for providing completion data, such as Airport or Aircraft Type Data.
  144. */
  145. bool commit(const QJsonArray &json_arr, const OPL::DbTable table);
  146. /*!
  147. * \brief Create new entry in the databse based on UserInput
  148. */
  149. bool insert(const OPL::Row &new_row);
  150. /*!
  151. * \brief Updates entry in database from existing entry tweaked by the user.
  152. */
  153. bool update(const OPL::Row &updated_row);
  154. /*!
  155. * \brief deletes an entry from the database.
  156. */
  157. bool remove(const OPL::Row &row);
  158. /*!
  159. * \brief deletes a batch of entries from the database. Optimised for speed when
  160. * deleting many entries. The entries are identified using their row id
  161. */
  162. bool removeMany(OPL::DbTable table, const QList<int> &row_id_list);
  163. /*!
  164. * \brief retreive a Row from the database
  165. */
  166. OPL::Row getRow(const OPL::DbTable table, const int row_id);
  167. /*!
  168. * \brief retreive a Map of <column name, column content> for a specific row in the database.
  169. */
  170. RowData_T getRowData(const OPL::DbTable table, const int row_id);
  171. /*!
  172. * \brief retreives a PilotEntry from the database. See row class for details.
  173. */
  174. inline OPL::PilotEntry getPilotEntry(int row_id)
  175. {
  176. const auto data = getRowData(OPL::DbTable::Pilots, row_id);
  177. return OPL::PilotEntry(row_id, data);
  178. }
  179. /*!
  180. * \brief get the database entry for the logbook owner (self)
  181. */
  182. inline OPL::PilotEntry getLogbookOwner()
  183. {
  184. auto data = getRowData(OPL::DbTable::Pilots, 1);
  185. data.insert(OPL::PilotEntry::ROWID, 1);
  186. return OPL::PilotEntry(1, data);
  187. }
  188. /*!
  189. * \brief Set the database entry for the logbook owner (self)
  190. */
  191. inline bool setLogbookOwner(RowData_T &ownerData)
  192. {
  193. if(ownerData.value(OPL::PilotEntry::LASTNAME).isNull()) {
  194. lastError = QSqlError("Logbook owners last name is mandatory.");
  195. return false;
  196. }
  197. ownerData.insert(OPL::PilotEntry::ROWID, 1);
  198. return commit(OPL::PilotEntry(1, ownerData));
  199. }
  200. /*!
  201. * \brief retreives a TailEntry from the database. See row class for details.
  202. */
  203. inline OPL::TailEntry getTailEntry(int row_id)
  204. {
  205. const auto data = getRowData(OPL::DbTable::Tails, row_id);
  206. return OPL::TailEntry(row_id, data);
  207. }
  208. /*!
  209. * \brief retreives a TailEntry from the database. See row class for details.
  210. */
  211. inline OPL::AircraftEntry getAircraftEntry(int row_id)
  212. {
  213. const auto data = getRowData(OPL::DbTable::Aircraft, row_id);
  214. return OPL::AircraftEntry(row_id, data);
  215. }
  216. /*!
  217. * \brief retreives a flight entry from the database. See row class for details.
  218. */
  219. inline OPL::FlightEntry getFlightEntry(int row_id)
  220. {
  221. const auto data = getRowData(OPL::DbTable::Flights, row_id);
  222. return OPL::FlightEntry(row_id, data);
  223. }
  224. /*!
  225. * \brief retreives a Simulator entry from the database. See row class for details.
  226. */
  227. inline OPL::SimulatorEntry getSimEntry(int row_id)
  228. {
  229. const auto data = getRowData(OPL::DbTable::Simulators, row_id);
  230. return OPL::SimulatorEntry(row_id, data);
  231. }
  232. /*!
  233. * \brief Retreives a currency entry from the database. See row class for details.
  234. */
  235. inline OPL::CurrencyEntry getCurrencyEntry(OPL::CurrencyEntry::Currency currency)
  236. {
  237. const auto data = getRowData(OPL::DbTable::Currencies, currency);
  238. return OPL::CurrencyEntry(currency, data);
  239. }
  240. /*!
  241. * \brief Retreives an airport entry from the database. See row class for details.
  242. */
  243. inline OPL::AirportEntry getAirportEntry(int row_id)
  244. {
  245. const auto data = getRowData(OPL::DbTable::Airports, row_id);
  246. return OPL::AirportEntry(row_id, data);
  247. }
  248. /*!
  249. * \brief returns the ROWID for the newest entry in the respective table.
  250. */
  251. int getLastEntry(OPL::DbTable table);
  252. /*!
  253. * \brief returns a list of ROWID's in the flights table for which foreign key constraints
  254. * exist.
  255. */
  256. QList<int> getForeignKeyConstraints(int foreign_row_id, OPL::DbTable table);
  257. /*!
  258. * \brief getTable returns all contents of a given table from the database
  259. * \return
  260. */
  261. QVector<RowData_T> getTable(OPL::DbTable table);
  262. /*!
  263. * \brief getUserTables returns a list of the of the tables that contain user-created data
  264. * (flights, pilots,..)
  265. */
  266. const QList<OPL::DbTable> &getUserTables() const;
  267. /*!
  268. * \brief getTemplateTables returns a list of the tables that contain template data
  269. * (aiports, aircraft,..)
  270. */
  271. const QList<OPL::DbTable> &getTemplateTables() const;
  272. // Maintenance and setup
  273. /*!
  274. * \brief Create or restore the database to its ready-to-use but empty state
  275. * \details The SQL code for the database creation is stored in a .sql file which is available as a ressource.
  276. * This file gets read, and the querys executed. If errors occur, returns false.
  277. */
  278. bool createSchema();
  279. /*!
  280. * \brief importTemplateData fills an empty database with the template
  281. * data (Aircraft, Airports) as read from the JSON
  282. * templates.
  283. * \param use_local_ressources determines whether the included ressource files
  284. * or a previously downloaded file should be used.
  285. * \return
  286. */
  287. bool importTemplateData(bool use_local_ressources);
  288. /*!
  289. * \brief Delete all rows from the user data tables (flights, pliots, tails)
  290. */
  291. bool resetUserData();
  292. /*!
  293. * \brief Database::createBackup copies the currently used database to an external backup location provided by the user
  294. * \param dest_file This is the full path and filename of where the backup will be created, e.g. 'home/Sully/myBackups/backupFromOpl.db'
  295. */
  296. bool createBackup(const QString& dest_file);
  297. /*!
  298. * \brief Database::restoreBackup restores the database from a given backup file and replaces the currently active database.
  299. * \param backup_file This is the full path and filename of the backup, e.g. 'home/Sully/myBackups/backupFromOpl.db'
  300. */
  301. bool restoreBackup(const QString& backup_file);
  302. /*!
  303. * @brief Retreive the total time of all flight entries in the databas
  304. * @param includePreviousExperience determines whether experience from previous logbooks
  305. * is included.
  306. * @return The sum of all entries in the flights table
  307. */
  308. const RowData_T getTotals(bool includePreviousExperience);
  309. signals:
  310. /*!
  311. * \brief updated is emitted whenever the database contents have been updated.
  312. * This can be either a commit, update or remove. This signal should be used to
  313. * trigger an update to the models of the views displaying database contents in
  314. * the user interface so that a user is always presented with up-to-date information.
  315. */
  316. void dataBaseUpdated(const OPL::DbTable table);
  317. /*!
  318. * \brief connectionReset is emitted whenever the database connection is reset, for
  319. * example when creating or restoring a backup.
  320. */
  321. void connectionReset();
  322. };
  323. } // namespace OPL
  324. #endif // DATABASE_H