/*
*openPilot Log - A FOSS Pilot Logbook Application
*Copyright (C) 2020 Felix Turowsky
*
*This program is free software: you can redistribute it and/or modify
*it under the terms of the GNU General Public License as published by
*the Free Software Foundation, either version 3 of the License, or
*(at your option) any later version.
*
*This program is distributed in the hope that it will be useful,
*but WITHOUT ANY WARRANTY; without even the implied warranty of
*MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
*GNU General Public License for more details.
*
*You should have received a copy of the GNU General Public License
*along with this program. If not, see .
*/
#include "dbsetup.h"
#include "debug.h"
// Statements for creation of database tables, Revision 10
const QString createTablePilots = "CREATE TABLE \"pilots\" ( "
"\"pilot_id\" INTEGER NOT NULL, "
"\"piclastname\" TEXT NOT NULL, "
"\"picfirstname\" TEXT, "
"\"alias\" TEXT, "
"\"company\" TEXT, "
"\"employeeid\" TEXT, "
"\"phone\" TEXT, "
"\"email\" TEXT, "
"\"displayname\" TEXT, "
"PRIMARY KEY(\"pilot_id\" AUTOINCREMENT)"
")";
const QString createTableTails = "CREATE TABLE \"tails\" ( "
"\"tail_id\" INTEGER NOT NULL, "
"\"registration\" TEXT NOT NULL, "
"\"company\" TEXT, "
"\"make\" TEXT, "
"\"model\" TEXT, "
"\"variant\" TEXT, "
"\"singlepilot\" INTEGER, "
"\"multipilot\" INTEGER, "
"\"singleengine\" INTEGER, "
"\"multiengine\" INTEGER, "
"\"unpowered\" INTEGER, "
"\"piston\" INTEGER, "
"\"turboprop\" INTEGER, "
"\"jet\" INTEGER, "
"\"light\" INTEGER, "
"\"medium\" INTEGER, "
"\"heavy\" INTEGER, "
"\"super\" INTEGER, "
"PRIMARY KEY(\"tail_id\" AUTOINCREMENT)"
")";
const QString createTableFlights = "CREATE TABLE \"flights\" ("
"\"flight_id\" INTEGER NOT NULL, "
"\"doft\" NUMERIC NOT NULL, "
"\"dept\" TEXT NOT NULL, "
"\"dest\" TEXT NOT NULL, "
"\"tofb\" INTEGER NOT NULL, "
"\"tonb\" INTEGER NOT NULL, "
"\"pic\" INTEGER NOT NULL, "
"\"acft\" INTEGER NOT NULL, "
"\"tblk\" INTEGER NOT NULL, "
"\"tSPSE\" INTEGER, "
"\"tSPME\" INTEGER, "
"\"tMP\" INTEGER, "
"\"tNIGHT\" INTEGER, "
"\"tIFR\" INTEGER, "
"\"tPIC\" INTEGER, "
"\"tPICUS\" INTEGER, "
"\"tSIC\" INTEGER, "
"\"tDUAL\" INTEGER, "
"\"tFI\" INTEGER, "
"\"tSIM\" INTEGER, "
"\"pilotFlying\" INTEGER, "
"\"toDay\" INTEGER, "
"\"toNight\" INTEGER, "
"\"ldgDay\" INTEGER, "
"\"ldgNight\" INTEGER, "
"\"autoland\" INTEGER, "
"\"secondPilot\" INTEGER, "
"\"thirdPilot\" INTEGER, "
"\"ApproachType\" TEXT, "
"\"FlightNumber\" TEXT, "
"\"Remarks\" TEXT, "
"FOREIGN KEY(\"pic\") REFERENCES \"pilots\"(\"pilot_id\") ON DELETE RESTRICT, "
"FOREIGN KEY(\"acft\") REFERENCES \"tails\"(\"tail_id\") ON DELETE RESTRICT, "
"PRIMARY KEY(\"flight_id\" AUTOINCREMENT) "
")";
const QString createTableAirports = "CREATE TABLE \"airports\" ( "
"\"airport_id\" INTEGER NOT NULL, "
"\"icao\" TEXT NOT NULL, "
"\"iata\" TEXT, "
"\"name\" TEXT, "
"\"lat\" REAL, "
"\"long\" REAL, "
"\"country\" TEXT, "
"\"alt\" INTEGER, "
"\"utcoffset\" INTEGER, "
"\"tzolson\" TEXT, "
"PRIMARY KEY(\"airport_id\" AUTOINCREMENT) "
")";
const QString createTableAircraft = "CREATE TABLE \"aircraft\" ( "
"\"aircraft_id\" INTEGER NOT NULL, "
"\"make\" TEXT, "
"\"model\" TEXT, "
"\"variant\" TEXT, "
"\"name\" TEXT, "
"\"iata\" TEXT, "
"\"icao\" TEXT, "
"\"singlepilot\" INTEGER, "
"\"multipilot\" INTEGER, "
"\"singleengine\" INTEGER, "
"\"multiengine\" INTEGER, "
"\"unpowered\" INTEGER, "
"\"piston\" INTEGER, "
"\"turboprop\" INTEGER, "
"\"jet\" INTEGER, "
"\"light\" INTEGER, "
"\"medium\" INTEGER, "
"\"heavy\" INTEGER, "
"\"super\" INTEGER, "
"PRIMARY KEY(\"aircraft_id\" AUTOINCREMENT)"
")";
const QString createTableChangelog = "CREATE TABLE \"changelog\" ( "
"\"revision\" INTEGER NOT NULL, "
"\"comment\" TEXT, "
"\"date\" NUMERIC, "
"PRIMARY KEY(\"revision\") "
")";
// Statements for creation of views in the database
const QString createViewDefault = "CREATE VIEW viewDefault AS "
"SELECT flight_id, doft as 'Date', "
"dept AS 'Dept', "
"printf('%02d',(tofb/60))||':'||printf('%02d',(tofb%60)) AS 'Time', "
"dest AS 'Dest', printf('%02d',(tonb/60))||':'||printf('%02d',(tonb%60)) AS 'Time ', "
"printf('%02d',(tblk/60))||':'||printf('%02d',(tblk%60)) AS 'Total', "
"displayname AS 'Name PIC', "
"make||' '||model||'-'||variant AS 'Type', "
"registration AS 'Registration', "
"FlightNumber AS 'Flight #', "
"Remarks "
"FROM flights "
"INNER JOIN pilots on flights.pic = pilots.pilot_id "
"INNER JOIN tails on flights.acft = tails.tail_id "
"ORDER BY date DESC ";
const QString createViewEASA = "CREATE VIEW viewEASA AS "
"SELECT "
"flight_id, doft as 'Date', "
"dept AS 'Dept', "
"printf('%02d',(tofb/60))||':'||printf('%02d',(tofb%60)) AS 'Time', "
"dest AS 'Dest', printf('%02d',(tonb/60))||':'||printf('%02d',(tonb%60)) AS 'Time ', "
"make||' '||model||'-'||variant AS 'Type', "
"registration AS 'Registration', "
"(SELECT printf('%02d',(tSPSE/60))||':'||printf('%02d',(tSPSE%60)) WHERE tSPSE IS NOT \"\") AS 'SP SE', "
"(SELECT printf('%02d',(tSPME/60))||':'||printf('%02d',(tSPME%60)) WHERE tSPME IS NOT \"\") AS 'SP ME', "
"(SELECT printf('%02d',(tMP/60))||':'||printf('%02d',(tMP%60)) WHERE tMP IS NOT \"\") AS 'MP', "
"printf('%02d',(tblk/60))||':'||printf('%02d',(tblk%60)) AS 'Total', "
"displayname AS 'Name PIC', "
"ldgDay AS 'L/D', "
"ldgNight AS 'L/N', "
"(SELECT printf('%02d',(tNight/60))||':'||printf('%02d',(tNight%60)) WHERE tNight IS NOT \"\") AS 'Night', "
"(SELECT printf('%02d',(tIFR/60))||':'||printf('%02d',(tIFR%60)) WHERE tIFR IS NOT \"\") AS 'IFR', "
"(SELECT printf('%02d',(tPIC/60))||':'||printf('%02d',(tPIC%60)) WHERE tPIC IS NOT \"\") AS 'PIC', "
"(SELECT printf('%02d',(tSIC/60))||':'||printf('%02d',(tSIC%60)) WHERE tSIC IS NOT \"\") AS 'SIC', "
"(SELECT printf('%02d',(tDual/60))||':'||printf('%02d',(tDual%60)) WHERE tDual IS NOT \"\") AS 'Dual', "
"(SELECT printf('%02d',(tFI/60))||':'||printf('%02d',(tFI%60)) WHERE tFI IS NOT \"\") AS 'FI', "
"Remarks "
"FROM flights "
"INNER JOIN pilots on flights.pic = pilots.pilot_id "
"INNER JOIN tails on flights.acft = tails.tail_id "
"ORDER BY date DESC";
const QString createViewTails = "CREATE VIEW viewTails AS "
"SELECT "
"tail_id AS 'ID', registration AS 'Registration', "
"make||' '||model||'-'||variant AS 'Type', "
"company AS 'Company' "
"FROM tails";
const QString createViewPilots = "CREATE VIEW viewPilots AS "
"SELECT "
"pilot_id AS 'ID', "
"piclastname AS 'Last Name', "
"picfirstname AS 'First Name', company AS 'Company' "
"FROM pilots";
const QString createViewQCompleter = "CREATE VIEW viewQCompleter AS "
"SELECT airport_id, icao, iata, tail_id, registration, pilot_id, "
"piclastname||', '||picfirstname AS 'pilot_name', alias "
"FROM airports "
"LEFT JOIN tails ON airports.airport_id = tails.tail_id "
"LEFT JOIN pilots ON airports.airport_id = pilots.pilot_id";
const QString createViewTotals = "CREATE VIEW viewTotals AS "
"SELECT "
"printf('%02d',CAST(SUM(tblk) AS INT)/60)||':'||printf('%02d',CAST(SUM(tblk) AS INT)%60) AS 'TOTAL', "
"printf('%02d',CAST(SUM(tSPSE) AS INT)/60)||':'||printf('%02d',CAST(SUM(tSPSE) AS INT)%60) AS 'SP SE', "
"printf('%02d',CAST(SUM(tSPME) AS INT)/60)||':'||printf('%02d',CAST(SUM(tSPME) AS INT)%60) AS 'SP ME', "
"printf('%02d',CAST(SUM(tNIGHT) AS INT)/60)||':'||printf('%02d',CAST(SUM(tNIGHT) AS INT)%60) AS 'NIGHT', "
"printf('%02d',CAST(SUM(tIFR) AS INT)/60)||':'||printf('%02d',CAST(SUM(tIFR) AS INT)%60) AS 'IFR', "
"printf('%02d',CAST(SUM(tPIC) AS INT)/60)||':'||printf('%02d',CAST(SUM(tPIC) AS INT)%60) AS 'PIC', "
"printf('%02d',CAST(SUM(tPICUS) AS INT)/60)||':'||printf('%02d',CAST(SUM(tPICUS) AS INT)%60) AS 'PICUS', "
"printf('%02d',CAST(SUM(tSIC) AS INT)/60)||':'||printf('%02d',CAST(SUM(tSIC) AS INT)%60) AS 'SIC', "
"printf('%02d',CAST(SUM(tDual) AS INT)/60)||':'||printf('%02d',CAST(SUM(tDual) AS INT)%60) AS 'DUAL', "
"printf('%02d',CAST(SUM(tFI) AS INT)/60)||':'||printf('%02d',CAST(SUM(tFI) AS INT)%60) AS 'INSTRUCTOR', "
"printf('%02d',CAST(SUM(tSIM) AS INT)/60)||':'||printf('%02d',CAST(SUM(tSIM) AS INT)%60) AS 'SIMULATOR', "
"printf('%02d',CAST(SUM(tMP) AS INT)/60)||':'||printf('%02d',CAST(SUM(tMP) AS INT)%60) AS 'MultPilot', "
"CAST(SUM(toDay) AS INT) AS 'TO Day', CAST(SUM(toNight) AS INT) AS 'TO Night', "
"CAST(SUM(ldgDay) AS INT) AS 'LDG Day', CAST(SUM(ldgNight) AS INT) AS 'LDG Night' "
"FROM flights";
const QStringList tables = {
createTablePilots,
createTableTails,
createTableFlights,
createTableAircraft,
createTableAirports,
createTableChangelog
};
const QStringList views = {
createViewDefault,
createViewEASA,
createViewTails,
createViewPilots,
createViewTotals,
createViewQCompleter
};
const QStringList userTables = {
"flights",
"pilots",
"tails"
};
const QStringList templateTables= {
"aircraft",
"airports",
"changelog"
};
bool DbSetup::createDatabase()
{
/// [George]: Not necessary to heap allocate for such a trivial task
/// TODO: Since you want to be fancy well do it with some cheeky bit operations
/// for the lolz.
QVector returnValues;
DEB("Creating tables...");
returnValues << createSchemata(tables);
DEB("Creating views...");
returnValues << createSchemata(views);
DEB("Populating tables...");
returnValues << importDefaultData();
for (const auto& allGood : returnValues) {
if (!allGood){
return false;
}
}
DEB("Database successfully created!");
return true;
}
bool DbSetup::importDefaultData()
{
QSqlQuery query;
// reset template tables
for (const auto& table : templateTables) {
//clear tables
query.prepare("DELETE FROM " + table);
if (!query.exec()) {
DEB("Error: " << query.lastError().text());
}
//fill with data from csv
if (!commitData(Csv::read("data/templates/" + table + ".csv"), table)) {
DEB("Error importing data.");
return false;
}
}
return true;
};
/*!
* \brief DbSetup::resetToDefault Empties all user-generated content in the database.
* \return true on success
*/
bool DbSetup::resetToDefault()
{
QSqlQuery query;
// clear user tables
for (const auto& table : userTables) {
query.prepare("DELETE FROM " + table);
if (!query.exec()) {
DEB("Error: " << query.lastError().text());
}
}
return true;
}
/*!
* \brief dbSetup::debug prints Database Layout
*/
void DbSetup::debug()
{
DEB("Database tables and views: ");
QSqlQuery query;
const QVector types = { "\"table\"", "\"view\"" };
for (const auto& var : types){
query.prepare("SELECT name FROM sqlite_master WHERE type=" + var);
query.exec();
while (query.next()) {
QString table = query.value(0).toString();
QSqlQuery entries("SELECT COUNT(*) FROM " + table);
entries.next();
DEB("Element " << query.value(0).toString()) << "with"
<< entries.value(0).toString() << "rows";
}
}
}
/*!
* \brief dbSetup::createTables Create the required tables for the database
* \return true on success
*/
bool DbSetup::createSchemata(const QStringList &statements)
{
QSqlQuery query;
QStringList errors;
for (const auto& statement : statements) {
query.prepare(statement);
query.exec();
if(!query.isActive()) {
errors << statement.section(QLatin1Char(' '),2,2) + " ERROR - " + query.lastError().text();
} else {
DEB("Schema added: " << statement.section(QLatin1Char(' '),2,2));
}
}
if (!errors.isEmpty()) {
DEB("The following errors have ocurred: ");
for (const auto& error : errors) {
DEB(error);
}
return false;
} else {
DEB("All schemas added successfully");
return true;
}
}
/*!
* \brief DbSetup::commitData inserts the data parsed from a csv file into the
* database. The first line of the csv file has to contain the column names
* of the corresponding table in the database.
* \param fromCSV input as parsed from CSV::read()
* \param tableName as in the database
* \return
*/
bool DbSetup::commitData(QVector fromCSV, const QString &tableName)
{
DEB("Importing Data to" << tableName);
auto dbLayout = DbInfo();
if (!dbLayout.tables.contains(tableName)){
DEB(tableName << "is not a table in the database. Aborting.");
DEB("Please check input data.");
return false;
}
// create insert statement
QString statement = "INSERT INTO " + tableName + " (";
QString placeholder = ") VALUES (";
for (auto& csvColumn : fromCSV) {
if(dbLayout.format.value(tableName).contains(csvColumn.first())){
statement += csvColumn.first() + ',';
csvColumn.removeFirst();
placeholder.append("?,");
} else {
DEB(csvColumn.first() << "is not a column of " << tableName << "Aborting.");
DEB("Please check input data.");
return false;
}
}
statement.chop(1);
placeholder.chop(1);
placeholder.append(')');
statement.append(placeholder);
/*
* Using exclusive transaction and the loop below is MUCH faster than
* passing the QStringLists to QSqlQuery::addBindValue and using QSqlQuery::execBatch()
*/
QSqlQuery query;
query.exec("BEGIN EXCLUSIVE TRANSACTION;");
for (int i = 0; i < fromCSV.first().length(); i++){
query.prepare(statement);
for(int j = 0; j < fromCSV.length(); j++) {
query.addBindValue(fromCSV[j][i]);
}
query.exec();
}
query.exec("COMMIT;"); //commit transaction
if (query.lastError().text().length() > 3) {
DEB("Error:" << query.lastError().text());
return false;
} else {
qDebug() << tableName << "Database successfully updated!";
return true;
}
}