dbsetup.cpp 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316
  1. /*
  2. *openPilot Log - A FOSS Pilot Logbook Application
  3. *Copyright (C) 2020 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. #include "dbsetup.h"
  19. // Pragmas for creation of database table
  20. const QString createTablePilots = "CREATE TABLE pilots ( "
  21. "pilot_id INTEGER, "
  22. "picfirstname TEXT, "
  23. "piclastname TEXT NOT NULL, "
  24. "alias TEXT, "
  25. "phone TEXT, "
  26. "email TEXT, "
  27. "rostername TEXT, "
  28. "PRIMARY KEY(pilot_id) "
  29. ")";
  30. const QString createTableAircraft = "CREATE TABLE aircraft ( "
  31. "aircraft_id integer, "
  32. "make TEXT, "
  33. "model TEXT, "
  34. "variant text, "
  35. "name TEXT, "
  36. "iata TEXT, "
  37. "icao TEXT, "
  38. "singlepilot INTEGER, "
  39. "multipilot INTEGER, "
  40. "singleengine INTEGER, "
  41. "multiengine INTEGER, "
  42. "turboprop INTEGER, "
  43. "jet INTEGER, "
  44. "heavy INTEGER, "
  45. "PRIMARY KEY(aircraft_id) "
  46. ") ";
  47. const QString createTableTails = "CREATE TABLE tails ( "
  48. "tail_id INTEGER, "
  49. "registration TEXT NOT NULL, "
  50. "aircraft_id INTEGER NOT NULL, "
  51. "company TEXT, "
  52. "PRIMARY KEY(tail_id), "
  53. "FOREIGN KEY(aircraft_id) REFERENCES aircraft(aircraft_id) "
  54. ")";
  55. const QString createTableFlights = "CREATE TABLE flights ( "
  56. "id INTEGER, "
  57. "doft NUMERIC NOT NULL, "
  58. "dept TEXT NOT NULL, "
  59. "tofb INTEGER NOT NULL, "
  60. "dest TEXT NOT NULL, "
  61. "tonb INTEGER NOT NULL, "
  62. "tblk INTEGER NOT NULL, "
  63. "pic INTEGER, "
  64. "acft INTEGER, "
  65. "PRIMARY KEY(id), "
  66. "FOREIGN KEY(pic) REFERENCES pilots(pilot_id), "
  67. "FOREIGN KEY(acft) REFERENCES tails(tail_id) "
  68. ")";
  69. //extras table might eventually be merged into flights table.
  70. const QString createTableExtras = "CREATE TABLE extras ( "
  71. "extras_id INTEGER NOT NULL, "
  72. "PilotFlying INTEGER, "
  73. "TOday INTEGER, "
  74. "TOnight INTEGER, "
  75. "LDGday INTEGER, "
  76. "LDGnight INTEGER, "
  77. "autoland INTEGER, "
  78. "tSPSE INTEGER, "
  79. "tSPME INTEGER, "
  80. "tMPME INTEGER, "
  81. "tNight INTEGER, "
  82. "tIFR INTEGER, "
  83. "tPIC INTEGER, "
  84. "tSIC INTEGER, "
  85. "tDual INTEGER, "
  86. "tInstructor INTEGER, "
  87. "tSIM INTEGER, "
  88. "ApproachType TEXT, "
  89. "FlightNumber TEXT, "
  90. "Remarks TEXT, "
  91. "PRIMARY KEY(extras_id) "
  92. ")";
  93. const QString createTableAirports = "CREATE TABLE airports ( "
  94. "airport_id INTEGER primary key, "
  95. "icao TEXT NOT NULL, "
  96. "iata TEXT, "
  97. "name TEXT, "
  98. "lat REAL, "
  99. "long REAL, "
  100. "country TEXT, "
  101. "alt INTEGER, "
  102. "utcoffset INTEGER, "
  103. "tzolson TEXT "
  104. ")";
  105. const QString createTableScratchpad = "CREATE TABLE scratchpad ( "
  106. "id INTEGER, "
  107. "doft NUMERIC, "
  108. "dept TEXT, "
  109. "tofb INTEGER, "
  110. "dest TEXT, "
  111. "tonb INTEGER, "
  112. "tblk INTEGER, "
  113. "pic INTEGER, "
  114. "acft INTEGER, "
  115. "PRIMARY KEY(id) "
  116. ") ";
  117. const QString createTableSettings = "CREATE TABLE settings ( "
  118. "setting_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
  119. "setting TEXT, "
  120. "description TEXT "
  121. ")";
  122. // Pragmas for creation of views in the database
  123. const QString createViewQCompleterView = "CREATE VIEW QCompleterView AS "
  124. "SELECT airport_id, icao, iata, "
  125. "tail_id, registration, "
  126. "pilot_id, "
  127. "piclastname||', '||picfirstname AS 'pilot_name', "
  128. "alias "
  129. "FROM airports "
  130. "LEFT JOIN tails ON airports.airport_id = tails.tail_id "
  131. "LEFT JOIN pilots ON airports.airport_id = pilots.pilot_id";
  132. const QString createViewAircraftList = "CREATE VIEW AircraftListView AS "
  133. "SELECT make AS 'Make', "
  134. "model AS 'Model', "
  135. "variant AS 'Variant' "
  136. "FROM aircraft "
  137. "WHERE variant IS NOT NULL";
  138. const QString createViewLogbook = "CREATE VIEW Logbook AS "
  139. "SELECT id, doft as 'Date', dept AS 'Dept', "
  140. "printf('%02d',(tofb/60))||':'||printf('%02d',(tofb%60)) AS 'Time', "
  141. "dest AS 'Dest', "
  142. "printf('%02d',(tonb/60))||':'||printf('%02d',(tonb%60)) AS 'Time ', "
  143. "printf('%02d',(tblk/60))||':'||printf('%02d',(tblk%60)) AS 'Total', "
  144. "piclastname||', '||substr(picfirstname,1,1)||'.' AS 'Name PIC', "
  145. "make||' '||model||'-'||variant AS 'Type', Registration, "
  146. "FlightNumber AS 'Flight #', Remarks "
  147. "FROM flights "
  148. "INNER JOIN pilots on flights.pic = pilots.pilot_id "
  149. "INNER JOIN tails on flights.acft = tails.tail_id "
  150. "INNER JOIN aircraft on tails.aircraft_id = aircraft.aircraft_id "
  151. "INNER JOIN extras on extras.extras_id = flights.id "
  152. "ORDER BY date DESC ";
  153. //Displays Single Engine, Multi Engine and Multi Pilot Time
  154. const QString createViewTimes = "CREATE VIEW ViewPilotTimes AS "
  155. "SELECT id, "
  156. "tblk*singlepilot*singleengine AS 'SPSE', "
  157. "tblk*singlepilot*multiengine AS 'SPME', "
  158. "tblk*multipilot AS 'MP' "
  159. "FROM flights "
  160. "INNER JOIN tails on flights.acft = tails.tail_id "
  161. "INNER JOIN aircraft on tails.aircraft_id = aircraft.aircraft_id ";
  162. QStringList tables = {
  163. createTablePilots,
  164. createTableAircraft,
  165. createTableTails,
  166. createTableFlights,
  167. createTableExtras,
  168. createTableScratchpad,
  169. createTableAirports,
  170. createTableSettings
  171. };
  172. QStringList views = {
  173. createViewQCompleterView,
  174. createViewAircraftList,
  175. createViewLogbook,
  176. createViewTimes
  177. };
  178. /*!
  179. * \brief dbSetup::showDatabase Outputs database information to Console
  180. */
  181. void dbSetup::showDatabase()
  182. {
  183. QSqlQuery query;
  184. query.prepare("SELECT name FROM sqlite_master WHERE type='table'");
  185. query.exec();
  186. while (query.next()) {
  187. qDebug() << "Tables: " << query.value(0).toString();
  188. }
  189. query.prepare("SELECT name FROM sqlite_master WHERE type='view'");
  190. query.exec();
  191. while (query.next()) {
  192. qDebug() << "Views: " << query.value(0).toString();
  193. }
  194. }
  195. /*!
  196. * \brief dbSetup::createTables Create the required tables for the database
  197. */
  198. void dbSetup::createTables()
  199. {
  200. QSqlQuery query;
  201. for(int i = 0; i<tables.length(); i++) {
  202. query.prepare(tables[i]);
  203. query.exec();
  204. if(!query.isActive()) {
  205. qWarning() << "DatabaseInit - ERROR: " << query.lastError().text();
  206. }else
  207. qDebug() << "Adding table " << tables[i].section(QLatin1Char(' '),2,2);
  208. }
  209. }
  210. /*!
  211. * \brief dbSetup::createViews Create the required views for the database
  212. */
  213. void dbSetup::createViews()
  214. {
  215. QSqlQuery query;
  216. for(int i = 0; i<views.length() ; i++) {
  217. query.prepare(views[i]);
  218. query.exec();
  219. if(!query.isActive()){
  220. qWarning() << "DatabaseInit - ERROR: " << query.lastError().text();
  221. }else{
  222. qDebug() << "Adding View " << views[i].section(QLatin1Char(' '),2,2);
  223. }
  224. }
  225. }
  226. /*!
  227. * \brief dbSetup::importCSV reads from a CSV file
  228. * \param filename - QString to csv file.
  229. * \return QVector<QStringList> of the CSV data, where each QStringList is one column of the input file
  230. */
  231. QVector<QStringList> dbSetup::importCSV(QString filename)
  232. {
  233. QFile csvfile(filename);
  234. csvfile.open(QIODevice::ReadOnly);
  235. QTextStream stream(&csvfile);
  236. QVector<QStringList> values;
  237. //Read CSV headers and create QStringLists accordingly
  238. QString line = stream.readLine();
  239. auto items = line.split(",");
  240. for (int i = 0; i < items.length(); i++) {
  241. QStringList list;
  242. list.append(items[i]);
  243. values.append(list);
  244. }
  245. //Fill QStringLists with data
  246. while (!stream.atEnd()) {
  247. QString line = stream.readLine();
  248. auto items = line.split(",");
  249. for (int i = 0; i < values.length(); i++) {
  250. values[i].append(items[i]);
  251. }
  252. }
  253. return values;
  254. }
  255. void dbSetup::commitAirportData(QVector<QStringList> airportData)
  256. {
  257. //remove header names
  258. for(int i=0; i < airportData.length(); i++)
  259. {
  260. airportData[i].removeFirst();
  261. }
  262. QSqlQuery query;
  263. qDebug() << "Updating Airport Database...";
  264. query.exec("BEGIN EXCLUSIVE TRANSACTION;"); // otherwise execution takes forever
  265. for (int i = 0; i < airportData[0].length(); i++){
  266. query.prepare("INSERT INTO airports ("
  267. "icao, "
  268. "iata, "
  269. "name, "
  270. "lat, "
  271. "long, "
  272. "country, "
  273. "alt, "
  274. "utcoffset, "
  275. "tzolson"
  276. ") "
  277. "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
  278. query.addBindValue(airportData[0][i]);
  279. query.addBindValue(airportData[1][i]);
  280. query.addBindValue(airportData[2][i]);
  281. query.addBindValue(airportData[3][i]);
  282. query.addBindValue(airportData[4][i]);
  283. query.addBindValue(airportData[5][i]);
  284. query.addBindValue(airportData[6][i]);
  285. query.addBindValue(airportData[7][i]);
  286. query.addBindValue(airportData[8][i]);
  287. query.exec();
  288. }
  289. query.exec("COMMIT;"); //commit transaction
  290. qDebug() << "Airport Database updated!";
  291. }