.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 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 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 aDbSetup::TEMPLATE_TABLES" \fBInitial value:\fP .PP .nf = { QLatin1String("aircraft"), QLatin1String("airports"), QLatin1String("currencies"), QLatin1String("changelog") } .fi .SS "const QList 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\&.