openPilotLog/docs/man/man3/aDbSetup.3

.TH "aDbSetup" 3 "Fri Mar 4 2022" "openPilotLog" \" -*- nroff -*-
.ad l
.nh
.SH NAME
aDbSetup \- The \fBaDbSetup\fP 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\&.  

.SH SYNOPSIS
.br
.PP
.SS "Functions"

.in +1c
.ti -1c
.RI "bool \fBcreateDatabase\fP ()"
.br
.RI "createDatabase runs a number of CREATE queries that create the database tables and columns\&. "
.ti -1c
.RI "bool \fBcommitData\fP (const QJsonArray &json_arr, const QString &table_name)"
.br
.RI "commitData commits the data read from a JSON file into a table in the database\&. "
.ti -1c
.RI "bool \fBimportTemplateData\fP (bool use_local_ressources)"
.br
.RI "importTemplateData fills an empty database with the template data (Aircraft, Airports, currencies, changelog) as read from the JSON templates\&. "
.ti -1c
.RI "bool \fBresetUserData\fP ()"
.br
.RI "Empties all user-generated content in the database\&. "
.in -1c
.SS "Variables"

.in +1c
.ti -1c
.RI "const auto \fBTEMPLATE_URL\fP = QStringLiteral('https://raw\&.githubusercontent\&.com/fiffty\-50/openpilotlog/develop/assets/database/templates/')"
.br
.ti -1c
.RI "const auto \fBCREATE_TABLE_PILOTS\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_TABLE_TAILS\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_TABLE_FLIGHTS\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_TABLE_AIRPORTS\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_TABLE_AIRCRAFT\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_TABLE_CHANGELOG\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_TABLE_CURRENCIES\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_VIEW_DEFAULT\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_VIEW_EASA\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_VIEW_TAILS\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_VIEW_PILOTS\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_VIEW_QCOMPLETER\fP"
.br
.ti -1c
.RI "const auto \fBCREATE_VIEW_TOTALS\fP"
.br
.ti -1c
.RI "const QList< QLatin1String > \fBDATABASE_TABLES\fP"
.br
.ti -1c
.RI "const QList< QLatin1String > \fBDATABASE_VIEWS\fP"
.br
.ti -1c
.RI "const QList< QLatin1String > \fBUSER_TABLES\fP"
.br
.ti -1c
.RI "const QList< QLatin1String > \fBTEMPLATE_TABLES\fP"
.br
.in -1c
.SH "Detailed Description"
.PP 
The \fBaDbSetup\fP 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\&. 
.SH "Function Documentation"
.PP 
.SS "bool aDbSetup::createDatabase ()"

.PP
createDatabase runs a number of CREATE queries that create the database tables and columns\&. 
.PP
\fBReturns\fP
.RS 4

.RE
.PP

.SS "bool aDbSetup::importTemplateData (bool use_local_ressources)"

.PP
importTemplateData fills an empty database with the template data (Aircraft, Airports, currencies, changelog) as read from the JSON templates\&. 
.PP
\fBParameters\fP
.RS 4
\fIuse_local_ressources\fP determines whether the included ressource files or a previously downloaded file should be used\&. 
.RE
.PP
\fBReturns\fP
.RS 4
.RE
.PP

.SS "bool aDbSetup::resetUserData ()"

.PP
Empties all user-generated content in the database\&. 
.PP
\fBReturns\fP
.RS 4
true on success 
.RE
.PP

.SH "Variable Documentation"
.PP 
.SS "const auto aDbSetup::CREATE_TABLE_AIRCRAFT"
\fBInitial value:\fP
.PP
.nf
= 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)"
            ")")
.fi
.SS "const auto aDbSetup::CREATE_TABLE_AIRPORTS"
\fBInitial value:\fP
.PP
.nf
= 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) "
            ")")
.fi
.SS "const auto aDbSetup::CREATE_TABLE_CHANGELOG"
\fBInitial value:\fP
.PP
.nf
= QLatin1String("CREATE TABLE changelog ( "
            " revision   INTEGER NOT NULL, "
            " comment    TEXT, "
            " date       NUMERIC, "
            " PRIMARY KEY(revision) "
            ")")
.fi
.SS "const auto aDbSetup::CREATE_TABLE_CURRENCIES"
\fBInitial value:\fP
.PP
.nf
= QLatin1String("CREATE TABLE currencies ( "
            " currency_id   INTEGER PRIMARY KEY AUTOINCREMENT, "
            " description   TEXT, "
            " expiryDate     NUMERIC "
            ")"
            )
.fi
.SS "const auto aDbSetup::CREATE_TABLE_PILOTS"
\fBInitial value:\fP
.PP
.nf
= 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)"
            ")")
.fi
.SS "const auto aDbSetup::CREATE_TABLE_TAILS"
\fBInitial value:\fP
.PP
.nf
= 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)"
            ")")
.fi
.SS "const auto aDbSetup::CREATE_VIEW_DEFAULT"
\fBInitial value:\fP
.PP
.nf
= 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 ")
.fi
.SS "const auto aDbSetup::CREATE_VIEW_PILOTS"
\fBInitial value:\fP
.PP
.nf
= QLatin1String("CREATE VIEW viewPilots AS "
        " SELECT "
        " pilot_id AS 'ID', "
        " lastname AS 'Last Name', "
        " firstname AS 'First Name', "
        " company AS 'Company' "
        " FROM pilots")
.fi
.SS "const auto aDbSetup::CREATE_VIEW_QCOMPLETER"
\fBInitial value:\fP
.PP
.nf
= 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")
.fi
.SS "const auto aDbSetup::CREATE_VIEW_TAILS"
\fBInitial value:\fP
.PP
.nf
= 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")
.fi
.SS "const auto aDbSetup::CREATE_VIEW_TOTALS"
\fBInitial value:\fP
.PP
.nf
= 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")
.fi
.SS "const QList<QLatin1String> aDbSetup::DATABASE_TABLES"
\fBInitial value:\fP
.PP
.nf
= {
    CREATE_TABLE_PILOTS,
    CREATE_TABLE_TAILS,
    CREATE_TABLE_FLIGHTS,
    CREATE_TABLE_AIRCRAFT,
    CREATE_TABLE_AIRPORTS,
    CREATE_TABLE_CURRENCIES,
    CREATE_TABLE_CHANGELOG
}
.fi
.SS "const QList<QLatin1String> aDbSetup::DATABASE_VIEWS"
\fBInitial value:\fP
.PP
.nf
= {
    CREATE_VIEW_DEFAULT,
    CREATE_VIEW_EASA,
    CREATE_VIEW_TAILS,
    CREATE_VIEW_PILOTS,
    CREATE_VIEW_TOTALS,
    CREATE_VIEW_QCOMPLETER,
}
.fi
.SS "const QList<QLatin1String> aDbSetup::TEMPLATE_TABLES"
\fBInitial value:\fP
.PP
.nf
= {
    QLatin1String("aircraft"),
    QLatin1String("airports"),
    QLatin1String("currencies"),
    QLatin1String("changelog")
}
.fi
.SS "const QList<QLatin1String> aDbSetup::USER_TABLES"
\fBInitial value:\fP
.PP
.nf
= {
    QLatin1String("flights"),
    QLatin1String("pilots"),
    QLatin1String("tails")
}
.fi
.SH "Author"
.PP 
Generated automatically by Doxygen for openPilotLog from the source code\&.