| openPilotLog

adatabase.h
1 /*
2 *openPilotLog - A FOSS Pilot Logbook Application
3 *Copyright (C) 2020-2021 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 ADATABASE_H
19 #define ADATABASE_H
20
21 #include <QPair>
22 #include <QHash>
23 #include <QString>
24 #include <QDir>
25 #include <QSqlDatabase>
26 #include <QSqlDriver>
27 #include <QSqlQuery>
28 #include <QSqlError>
29 #include <QSqlTableModel>
30 #include <QSqlQuery>
31 #include <QSqlRecord>
32 #include <QSqlField>
33
34 #include "src/database/adatabasetypes.h"
35 #include "src/classes/aentry.h"
36 #include "src/classes/apilotentry.h"
37 #include "src/classes/atailentry.h"
38 #include "src/classes/aaircraftentry.h"
39 #include "src/classes/aflightentry.h"
40 #include "src/classes/astandardpaths.h"
41 #include "src/classes/acurrencyentry.h"
42
43 #define SQLITE_DRIVER QStringLiteral("QSQLITE")
44
52 #define aDB ADatabase::instance()
53
58 enum class ADatabaseTarget
59 {
60 airport_identifier_icao,
61 airport_identifier_iata,
62 airport_identifier_all,
63 airport_names,
64 registrations,
65 companies,
66 tails,
67 pilots,
68 aircraft
69 };
70
71 enum class ADatabaseTable
72 {
73 tails,
74 flights,
75 currencies,
76 aircraft,
77 pilots,
78 };
79
83 enum class ADatabaseSummaryKey {
84 total_flights,
85 total_tails,
86 total_pilots,
87 last_flight,
88 total_time,
89 };
90
97 struct UserDataState {
98
99 UserDataState(){numTails = 0; numPilots = 0;}
100 UserDataState(int numTails_, int numPilots_)
101 : numTails(numTails_), numPilots(numPilots_){}
102
103 bool operator==(const UserDataState& other)
104 {
105 return numTails == other.numTails && numPilots == other.numPilots;
106 }
107 bool operator!=(const UserDataState& other)
108 {
109 return numTails != other.numTails || numPilots != other.numPilots;
110 }
111
112 int numTails;
113 int numPilots;
114 };
115
123 class ADatabaseError : public QSqlError {
124 public:
125 ADatabaseError() = default;
126 ADatabaseError(QString msg);
127 QString text() const;
128 ADatabaseError(QSqlError);
129 };
130
135 class ADatabase : public QObject {
136 Q_OBJECT
137
138 private:
139 static ADatabase* self;
140 TableNames_T tableNames;
141 TableColumns_T tableColumns;
142 int databaseRevision;
143
144 ADatabase();
145 int checkDbVersion() const;
146
147 const static QStringList userTableNames;
148 const static QStringList templateTableNames;
149 const static int minimumDatabaseRevision;
150 public:
159 QSqlError lastError;
160
161 const QFileInfo databaseFile;
162
163 // Ensure DB is not copiable or assignable
164 ADatabase(const ADatabase&) = delete;
165 void operator=(const ADatabase&) = delete;
166 static ADatabase* instance();
167
173 int dbRevision() const;
174
178 const TableNames_T getTableNames() const;
179
183 const ColumnNames_T getTableColumns(TableName_T table_name) const;
184
189 void updateLayout();
190
195 const QString sqliteVersion() const;
196
200 bool connect();
201
205 void disconnect();
206
211 static QSqlDatabase database();
212
218 QVector<QVariant> customQuery(QString statement, int return_values);
219
223 bool exists(const AEntry &entry);
224 bool exists(DataPosition data_position);
225
230 bool clear();
231
236 bool commit(const AEntry &entry);
237
241 bool insert(const AEntry &new_entry);
242
246 bool update(const AEntry &updated_entry);
247
251 bool remove(const AEntry &entry);
252
257 bool removeMany(QList<DataPosition>);
258
262 RowData_T getEntryData(DataPosition data_position);
263
267 AEntry getEntry(DataPosition data_position);
268
277 APilotEntry getPilotEntry(RowId_T row_id);
278
287 ATailEntry getTailEntry(RowId_T row_id);
288
297 AAircraftEntry getAircraftEntry(RowId_T row_id);
298
307 AFlightEntry getFlightEntry(RowId_T row_id);
308
312 ACurrencyEntry getCurrencyEntry(ACurrencyEntry::CurrencyName currency_name);
313
318 const QStringList getCompletionList(ADatabaseTarget target);
319
324 const QHash<RowId_T, QString> getIdMap(ADatabaseTarget target);
325
329 RowId_T getLastEntry(ADatabaseTable table);
330
335 QList<RowId_T> getForeignKeyConstraints(RowId_T foreign_row_id, ADatabaseTable target);
336
341 APilotEntry resolveForeignPilot(RowId_T foreign_key);
342
347 ATailEntry resolveForeignTail(RowId_T foreign_key);
348
357 QMap<ADatabaseSummaryKey, QString> databaseSummary(const QString& db_path);
358
362 const QString databaseSummaryString(const QString& db_path);
363
364 bool restoreBackup(const QString& backup_file);
365 bool createBackup(const QString& dest_file);
366
371 QVector<RowData_T> getTable(ADatabaseTable table_name);
372
377 QStringList getUserTableNames();
378
383 QStringList getTemplateTableNames();
384
390 UserDataState getUserDataState();
391
395 static int getMinimumDatabaseRevision();
396
397 signals:
404 void dataBaseUpdated();
409 void connectionReset();
410 };
411
412 #endif // ADATABASE_H
ADatabaseError
Custom Database Error derived from QSqlError. Extends text() adding "Database Error: " before the tex...
Definition: adatabase.h:123
ADatabase
The DB class encapsulates the SQL database by providing fast access to hot database data.
Definition: adatabase.h:135
ADatabase::getLastEntry
RowId_T getLastEntry(ADatabaseTable table)
returns the ROWID for the newest entry in the respective database.
Definition: adatabase.cpp:600
ADatabase::restoreBackup
bool restoreBackup(const QString &backup_file)
ADatabase::restoreBackup restores the database from a given backup file and replaces the currently ac...
Definition: adatabase.cpp:848
ADatabase::exists
bool exists(const AEntry &entry)
Checks if an entry exists in the database, based on position data.
Definition: adatabase.cpp:253
ADatabase::connectionReset
void connectionReset()
connectionReset is emitted whenever the database connection is reset, for example when creating or re...
ADatabase::getForeignKeyConstraints
QList< RowId_T > getForeignKeyConstraints(RowId_T foreign_row_id, ADatabaseTable target)
returns a list of ROWID's in the flights table for which foreign key constraints exist.
Definition: adatabase.cpp:627
ADatabase::dbRevision
int dbRevision() const
dbRevision returns the database Revision Number. The Revision refers to what iteration of the databas...
Definition: adatabase.cpp:45
ADatabase::database
static QSqlDatabase database()
Can be used to access the database connection.
Definition: adatabase.cpp:168
ADatabase::customQuery
QVector< QVariant > customQuery(QString statement, int return_values)
Can be used to send a complex query to the database.
Definition: adatabase.cpp:674
ADatabase::databaseSummaryString
const QString databaseSummaryString(const QString &db_path)
returns a short summary string of the database, containing total time and date of last flight.
Definition: adatabase.cpp:763
ADatabase::getTailEntry
ATailEntry getTailEntry(RowId_T row_id)
retreives a TailEntry from the database.
Definition: adatabase.cpp:475
ADatabase::updateLayout
void updateLayout()
Updates the member variables tableNames and tableColumns with up-to-date layout information if the da...
Definition: adatabase.cpp:98
ADatabase::getTableNames
const TableNames_T getTableNames() const
Return the names of all tables in the database.
Definition: adatabase.cpp:93
ADatabase::getPilotEntry
APilotEntry getPilotEntry(RowId_T row_id)
retreives a PilotEntry from the database.
Definition: adatabase.cpp:468
ADatabase::resolveForeignPilot
APilotEntry resolveForeignPilot(RowId_T foreign_key)
Resolves the foreign key in a flight entry.
Definition: adatabase.cpp:664
ADatabase::sqliteVersion
const QString sqliteVersion() const
ADatabase::sqliteVersion returns the database sqlite version. See also dbRevision()
Definition: adatabase.cpp:124
ADatabase::remove
bool remove(const AEntry &entry)
deletes an entry from the database.
Definition: adatabase.cpp:182
ADatabase::databaseSummary
QMap< ADatabaseSummaryKey, QString > databaseSummary(const QString &db_path)
Return a summary of a database.
Definition: adatabase.cpp:702
ADatabase::resolveForeignTail
ATailEntry resolveForeignTail(RowId_T foreign_key)
Resolves the foreign key in a flight entry.
Definition: adatabase.cpp:669
ADatabase::createBackup
bool createBackup(const QString &dest_file)
ADatabase::createBackup copies the currently used database to an external backup location provided by...
Definition: adatabase.cpp:781
ADatabase::getCurrencyEntry
ACurrencyEntry getCurrencyEntry(ACurrencyEntry::CurrencyName currency_name)
Retreives a currency entry from the database.
Definition: adatabase.cpp:496
ADatabase::dataBaseUpdated
void dataBaseUpdated()
updated is emitted whenever the database contents have been updated. This can be either a commit,...
ADatabase::getUserTableNames
QStringList getUserTableNames()
getUserTableNames returns a list of the table names of tables that contain user-created data (flights...
Definition: adatabase.cpp:83
ADatabase::clear
bool clear()
clear resets the database, i.e. deletes all content in the tables containing userdata (pilots,...
Definition: adatabase.cpp:310
ADatabase::getFlightEntry
AFlightEntry getFlightEntry(RowId_T row_id)
retreives a flight entry from the database.
Definition: adatabase.cpp:489
ADatabase::commit
bool commit(const AEntry &entry)
commits an entry to the database, calls either insert or update, based on position data
Definition: adatabase.cpp:173
ADatabase::removeMany
bool removeMany(QList< DataPosition >)
deletes a list of entries from the database. Optimised for speed when deleting many entries.
Definition: adatabase.cpp:210
ADatabase::lastError
QSqlError lastError
Holds information about the last error that ocurred during a SQL operation. If the error type is QSql...
Definition: adatabase.h:159
ADatabase::getEntry
AEntry getEntry(DataPosition data_position)
retreive an Entry from the database.
Definition: adatabase.cpp:461
ADatabase::getCompletionList
const QStringList getCompletionList(ADatabaseTarget target)
getCompletionList returns a QStringList of values for a QCompleter based on database values
Definition: adatabase.cpp:503
ADatabase::getTable
QVector< RowData_T > getTable(ADatabaseTable table_name)
getTable returns all contents of a given table from the database
Definition: adatabase.cpp:799
ADatabase::getTableColumns
const ColumnNames_T getTableColumns(TableName_T table_name) const
Return the names of a given table in the database.
Definition: adatabase.cpp:88
ADatabase::getUserDataState
UserDataState getUserDataState()
getUserDataState returns a struct containing the current amount of entries in the tails and pilots ta...
Definition: adatabase.cpp:67
ADatabase::connect
bool connect()
Connect to the database and populate database information.
Definition: adatabase.cpp:133
ADatabase::insert
bool insert(const AEntry &new_entry)
Create new entry in the databse based on UserInput.
Definition: adatabase.cpp:360
ADatabase::getMinimumDatabaseRevision
static int getMinimumDatabaseRevision()
getMinimumDatabaseRevision returns the minimum required database revision number required by the appl...
Definition: adatabase.cpp:57
ADatabase::disconnect
void disconnect()
closes the database connection.
Definition: adatabase.cpp:161
ADatabase::update
bool update(const AEntry &updated_entry)
Updates entry in database from existing entry tweaked by the user.
Definition: adatabase.cpp:326
ADatabase::getTemplateTableNames
QStringList getTemplateTableNames()
getTemplateTableNames returns a list of the table names of tables that contain template data (aiports...
Definition: adatabase.cpp:62
ADatabase::getAircraftEntry
AAircraftEntry getAircraftEntry(RowId_T row_id)
retreives a TailEntry from the database.
Definition: adatabase.cpp:482
ADatabase::getIdMap
const QHash< RowId_T, QString > getIdMap(ADatabaseTarget target)
returns a QHash of a human-readable database value and its row id. Used in the Dialogs to map user in...
Definition: adatabase.cpp:552
ADatabase::getEntryData
RowData_T getEntryData(DataPosition data_position)
retreive entry data from the database to create an entry object
Definition: adatabase.cpp:404
AEntry
The Entry class encapsulates table metadata(table name, row id) and data for new and existing entries...
Definition: aentry.h:33
AFlightEntry
Definition: aflightentry.h:23
AAircraftEntry
Definition: aaircraftentry.h:24
ACurrencyEntry
Definition: acurrencyentry.h:8
APilotEntry
Definition: apilotentry.h:24
ATailEntry
Definition: atailentry.h:24
DataPosition
Definition: adatabasetypes.h:44
UserDataState
The UserDateState struct caches the current number of entries in relevant database tables for fast ac...
Definition: adatabase.h:97