aDbSetup Namespace Reference

The aDbSetup namespace is responsible for the inital setup of the database when the application is first launched. It creates the database in the specified default location and creates all required tables and views. More...

Functions
bool	createDatabase ()
	createDatabase runs a number of CREATE queries that create the database tables and columns. More...
bool	commitData (const QJsonArray &json_arr, const QString &table_name)
	commitData commits the data read from a JSON file into a table in the database.
bool	importTemplateData (bool use_local_ressources)
	importTemplateData fills an empty database with the template data (Aircraft, Airports, currencies, changelog) as read from the JSON templates. More...
bool	resetUserData ()
	Empties all user-generated content in the database. More...

Variables
const auto	TEMPLATE_URL = QStringLiteral("https://raw.githubusercontent.com/fiffty-50/openpilotlog/develop/assets/database/templates/")
const auto	CREATE_TABLE_PILOTS
const auto	CREATE_TABLE_TAILS
const auto	CREATE_TABLE_FLIGHTS
const auto	CREATE_TABLE_AIRPORTS
const auto	CREATE_TABLE_AIRCRAFT
const auto	CREATE_TABLE_CHANGELOG
const auto	CREATE_TABLE_CURRENCIES
const auto	CREATE_VIEW_DEFAULT
const auto	CREATE_VIEW_EASA
const auto	CREATE_VIEW_TAILS
const auto	CREATE_VIEW_PILOTS
const auto	CREATE_VIEW_QCOMPLETER
const auto	CREATE_VIEW_TOTALS
const QList< QLatin1String >	DATABASE_TABLES
const QList< QLatin1String >	DATABASE_VIEWS
const QList< QLatin1String >	USER_TABLES
const QList< QLatin1String >	TEMPLATE_TABLES

Detailed Description

The aDbSetup namespace is responsible for the inital setup of the database when the application is first launched. It creates the database in the specified default location and creates all required tables and views.

Function Documentation

createDatabase()

bool aDbSetup::createDatabase

(

)

createDatabase runs a number of CREATE queries that create the database tables and columns.

Returns

importTemplateData()

bool aDbSetup::importTemplateData

(

bool

use_local_ressources

)

importTemplateData fills an empty database with the template data (Aircraft, Airports, currencies, changelog) as read from the JSON templates.

Parameters

use_local_ressources

determines whether the included ressource files or a previously downloaded file should be used.

Returns

resetUserData()

bool aDbSetup::resetUserData

(

)

Empties all user-generated content in the database.

Returns

true on success

Variable Documentation

CREATE_TABLE_AIRCRAFT

const auto aDbSetup::CREATE_TABLE_AIRCRAFT

Initial value:

= QLatin1String("CREATE TABLE aircraft ("
            " aircraft_id   INTEGER NOT NULL,"
            " make          TEXT,"
            " model         TEXT,"
            " variant       TEXT,"
            " name          TEXT,"
            " iata          TEXT,"
            " icao          TEXT,"
            " multipilot    INTEGER,"
            " multiengine   INTEGER,"
            " engineType    INTEGER,"
            " weightClass   INTEGER,"
            " PRIMARY KEY(aircraft_id AUTOINCREMENT)"
            ")")

CREATE_TABLE_AIRPORTS

const auto aDbSetup::CREATE_TABLE_AIRPORTS

Initial value:

= QLatin1String("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) "
            ")")

CREATE_TABLE_CHANGELOG

const auto aDbSetup::CREATE_TABLE_CHANGELOG

Initial value:

= QLatin1String("CREATE TABLE changelog ( "
            " revision   INTEGER NOT NULL, "
            " comment    TEXT, "
            " date       NUMERIC, "
            " PRIMARY KEY(revision) "
            ")")

CREATE_TABLE_CURRENCIES

const auto aDbSetup::CREATE_TABLE_CURRENCIES

Initial value:

= QLatin1String("CREATE TABLE currencies ( "
            " currency_id   INTEGER PRIMARY KEY AUTOINCREMENT, "
            " description   TEXT, "
            " expiryDate     NUMERIC "
            ")"
            )

CREATE_TABLE_PILOTS

const auto aDbSetup::CREATE_TABLE_PILOTS

Initial value:

= QLatin1String("CREATE TABLE pilots ( "
            " pilot_id       INTEGER NOT NULL, "
            " lastname       TEXT    NOT NULL, "
            " firstname      TEXT, "
            " alias          TEXT, "
            " company        TEXT, "
            " employeeid     TEXT, "
            " phone          TEXT, "
            " email          TEXT, "
            " PRIMARY KEY(pilot_id AUTOINCREMENT)"
            ")")

CREATE_TABLE_TAILS

const auto aDbSetup::CREATE_TABLE_TAILS

Initial value:

= QLatin1String("CREATE TABLE tails ("
            " tail_id        INTEGER NOT NULL,"
            " registration   TEXT NOT NULL,"
            " company        TEXT,"
            " make           TEXT,"
            " model          TEXT,"
            " variant        TEXT,"
            " multipilot     INTEGER,"
            " multiengine    INTEGER,"
            " engineType     INTEGER,"
            " weightClass    INTEGER,"
            " PRIMARY KEY(tail_id AUTOINCREMENT)"
            ")")

CREATE_VIEW_DEFAULT

const auto aDbSetup::CREATE_VIEW_DEFAULT

Initial value:

= QLatin1String("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', "
        " CASE "
        " WHEN pilot_id = 1 THEN alias "
        " ELSE lastname||', '||substr(firstname, 1, 1)||'.' "
        " END "
        " AS 'Name PIC', "
        " CASE "
        " WHEN variant IS NOT NULL THEN make||' '||model||'-'||variant "
        " ELSE make||' '||model "
        " END "
        " AS 'Type', "
        " registration AS 'Registration', "
        " FlightNumber AS 'Flight #', "
        " remarks AS '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 ")

CREATE_VIEW_PILOTS

const auto aDbSetup::CREATE_VIEW_PILOTS

Initial value:

= QLatin1String("CREATE VIEW viewPilots AS "
        " SELECT "
        " pilot_id AS 'ID', "
        " lastname AS 'Last Name', "
        " firstname AS 'First Name', "
        " company AS 'Company' "
        " FROM pilots")

CREATE_VIEW_QCOMPLETER

const auto aDbSetup::CREATE_VIEW_QCOMPLETER

Initial value:

= QLatin1String("CREATE VIEW viewQCompleter AS "
        " SELECT airport_id, icao, iata, tail_id, registration, pilot_id, "
        " lastname||', '||firstname 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")

CREATE_VIEW_TAILS

const auto aDbSetup::CREATE_VIEW_TAILS

Initial value:

= QLatin1String("CREATE VIEW viewTails AS "
        " SELECT "
        " tail_id AS 'ID', "
        " registration AS 'Registration', "
        " make||' '||model AS 'Type', "
        " company AS 'Company' "
        " FROM tails WHERE model IS NOT NULL AND variant IS NULL "
        " UNION "
        " SELECT "
        " tail_id AS 'ID', "
        " registration AS 'Registration', "
        " make||' '||model||'-'||variant AS 'Type', "
        " company AS 'Company' "
        " FROM tails WHERE variant IS NOT NULL")

CREATE_VIEW_TOTALS

const auto aDbSetup::CREATE_VIEW_TOTALS

Initial value:

= QLatin1String("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")

DATABASE_TABLES

const QList<QLatin1String> aDbSetup::DATABASE_TABLES

Initial value:

= {
    CREATE_TABLE_PILOTS,
    CREATE_TABLE_TAILS,
    CREATE_TABLE_FLIGHTS,
    CREATE_TABLE_AIRCRAFT,
    CREATE_TABLE_AIRPORTS,
    CREATE_TABLE_CURRENCIES,
    CREATE_TABLE_CHANGELOG
}

DATABASE_VIEWS

const QList<QLatin1String> aDbSetup::DATABASE_VIEWS

Initial value:

= {
    CREATE_VIEW_DEFAULT,
    CREATE_VIEW_EASA,
    CREATE_VIEW_TAILS,
    CREATE_VIEW_PILOTS,
    CREATE_VIEW_TOTALS,
    CREATE_VIEW_QCOMPLETER,
}

TEMPLATE_TABLES

const QList<QLatin1String> aDbSetup::TEMPLATE_TABLES

Initial value:

= {
    QLatin1String("aircraft"),
    QLatin1String("airports"),
    QLatin1String("currencies"),
    QLatin1String("changelog")
}

USER_TABLES

const QList<QLatin1String> aDbSetup::USER_TABLES

Initial value:

= {
    QLatin1String("flights"),
    QLatin1String("pilots"),
    QLatin1String("tails")
}