adatabasesetup.cpp 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554
  1. /*
  2. *openPilotLog - A FOSS Pilot Logbook Application
  3. *Copyright (C) 2020-2021 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 "adatabasesetup.h"
  19. #include "src/database/adatabase.h"
  20. #include "src/functions/alog.h"
  21. #include "src/functions/areadcsv.h"
  22. #include "src/classes/astandardpaths.h"
  23. #include "src/classes/adownload.h"
  24. #include "src/opl.h"
  25. #include "src/functions/adatetime.h"
  26. #include "src/functions/alog.h"
  27. const auto createTablePilots = QLatin1String("CREATE TABLE pilots ( "
  28. " pilot_id INTEGER NOT NULL, "
  29. " lastname TEXT NOT NULL, "
  30. " firstname TEXT, "
  31. " alias TEXT, "
  32. " company TEXT, "
  33. " employeeid TEXT, "
  34. " phone TEXT, "
  35. " email TEXT, "
  36. " PRIMARY KEY(pilot_id AUTOINCREMENT)"
  37. ")");
  38. const auto createTableTails = QLatin1String("CREATE TABLE tails ("
  39. " tail_id INTEGER NOT NULL,"
  40. " registration TEXT NOT NULL,"
  41. " company TEXT,"
  42. " make TEXT,"
  43. " model TEXT,"
  44. " variant TEXT,"
  45. " multipilot INTEGER,"
  46. " multiengine INTEGER,"
  47. " engineType INTEGER,"
  48. " weightClass INTEGER,"
  49. " PRIMARY KEY(tail_id AUTOINCREMENT)"
  50. ")");
  51. const auto createTableFlights = QLatin1String("CREATE TABLE flights ("
  52. " flight_id INTEGER NOT NULL, "
  53. " doft NUMERIC NOT NULL, "
  54. " dept TEXT NOT NULL, "
  55. " dest TEXT NOT NULL, "
  56. " tofb INTEGER NOT NULL, "
  57. " tonb INTEGER NOT NULL, "
  58. " pic INTEGER NOT NULL, "
  59. " acft INTEGER NOT NULL, "
  60. " tblk INTEGER NOT NULL, "
  61. " tSPSE INTEGER, "
  62. " tSPME INTEGER, "
  63. " tMP INTEGER, "
  64. " tNIGHT INTEGER, "
  65. " tIFR INTEGER, "
  66. " tPIC INTEGER, "
  67. " tPICUS INTEGER, "
  68. " tSIC INTEGER, "
  69. " tDUAL INTEGER, "
  70. " tFI INTEGER, "
  71. " tSIM INTEGER, "
  72. " pilotFlying INTEGER, "
  73. " toDay INTEGER, "
  74. " toNight INTEGER, "
  75. " ldgDay INTEGER, "
  76. " ldgNight INTEGER, "
  77. " autoland INTEGER, "
  78. " secondPilot INTEGER, "
  79. " thirdPilot INTEGER, "
  80. " approachType TEXT, "
  81. " flightNumber TEXT, "
  82. " remarks TEXT, "
  83. " FOREIGN KEY(pic) REFERENCES pilots(pilot_id) ON DELETE RESTRICT, "
  84. " FOREIGN KEY(acft) REFERENCES tails(tail_id) ON DELETE RESTRICT, "
  85. " PRIMARY KEY(flight_id AUTOINCREMENT) "
  86. ")");
  87. const auto createTableAirports = QLatin1String("CREATE TABLE airports ( "
  88. " airport_id INTEGER NOT NULL, "
  89. " icao TEXT NOT NULL, "
  90. " iata TEXT, "
  91. " name TEXT, "
  92. " lat REAL, "
  93. " long REAL, "
  94. " country TEXT, "
  95. " alt INTEGER, "
  96. " utcoffset INTEGER, "
  97. " tzolson TEXT, "
  98. " PRIMARY KEY(airport_id AUTOINCREMENT) "
  99. ")");
  100. const auto createTableAircraft = QLatin1String("CREATE TABLE aircraft ("
  101. " aircraft_id INTEGER NOT NULL,"
  102. " make TEXT,"
  103. " model TEXT,"
  104. " variant TEXT,"
  105. " name TEXT,"
  106. " iata TEXT,"
  107. " icao TEXT,"
  108. " multipilot INTEGER,"
  109. " multiengine INTEGER,"
  110. " engineType INTEGER,"
  111. " weightClass INTEGER,"
  112. " PRIMARY KEY(aircraft_id AUTOINCREMENT)"
  113. ")");
  114. const auto createTableChangelog = QLatin1String("CREATE TABLE changelog ( "
  115. " revision INTEGER NOT NULL, "
  116. " comment TEXT, "
  117. " date NUMERIC, "
  118. " PRIMARY KEY(revision) "
  119. ")");
  120. const auto createTableCurrencies = QLatin1String("CREATE TABLE currencies ( "
  121. " currency_id INTEGER PRIMARY KEY AUTOINCREMENT, "
  122. " description TEXT, "
  123. " expiryDate NUMERIC "
  124. ")"
  125. );
  126. // Statements for creation of views in the database
  127. const auto createViewDefault = QLatin1String("CREATE VIEW viewDefault AS "
  128. " SELECT flight_id, doft as 'Date', "
  129. " dept AS 'Dept', "
  130. " printf('%02d',(tofb/60))||':'||printf('%02d',(tofb%60)) AS 'Time', "
  131. " dest AS 'Dest', printf('%02d',(tonb/60))||':'||printf('%02d',(tonb%60)) AS 'Time ', "
  132. " printf('%02d',(tblk/60))||':'||printf('%02d',(tblk%60)) AS 'Total', "
  133. " CASE "
  134. " WHEN pilot_id = 1 THEN alias "
  135. " ELSE lastname||', '||substr(firstname, 1, 1)||'.' "
  136. " END "
  137. " AS 'Name PIC', "
  138. " CASE "
  139. " WHEN variant IS NOT NULL THEN make||' '||model||'-'||variant "
  140. " ELSE make||' '||model "
  141. " END "
  142. " AS 'Type', "
  143. " registration AS 'Registration', "
  144. " FlightNumber AS 'Flight #', "
  145. " remarks AS 'Remarks'"
  146. " FROM flights "
  147. " INNER JOIN pilots on flights.pic = pilots.pilot_id "
  148. " INNER JOIN tails on flights.acft = tails.tail_id "
  149. " ORDER BY date DESC ");
  150. const auto createViewEASA = QLatin1String("CREATE VIEW viewEASA AS "
  151. " SELECT "
  152. " flight_id, doft as 'Date', "
  153. " dept AS 'Dept', "
  154. " printf('%02d',(tofb/60))||':'||printf('%02d',(tofb%60)) AS 'Time', "
  155. " dest AS 'Dest', printf('%02d',(tonb/60))||':'||printf('%02d',(tonb%60)) AS 'Time ', "
  156. " CASE "
  157. " WHEN variant IS NOT NULL THEN make||' '||model||'-'||variant "
  158. " ELSE make||' '||model "
  159. " END "
  160. " AS 'Type', "
  161. " registration AS 'Registration', "
  162. " (SELECT printf('%02d',(tSPSE/60))||':'||printf('%02d',(tSPSE%60)) WHERE tSPSE IS NOT NULL) AS 'SP SE', "
  163. " (SELECT printf('%02d',(tSPME/60))||':'||printf('%02d',(tSPME%60)) WHERE tSPME IS NOT NULL) AS 'SP ME', "
  164. " (SELECT printf('%02d',(tMP/60))||':'||printf('%02d',(tMP%60)) WHERE tMP IS NOT NULL) AS 'MP', "
  165. " printf('%02d',(tblk/60))||':'||printf('%02d',(tblk%60)) AS 'Total', "
  166. " CASE "
  167. " WHEN pilot_id = 1 THEN alias "
  168. " ELSE lastname||', '||substr(firstname, 1, 1)||'.' "
  169. " END "
  170. " AS 'Name PIC', "
  171. " ldgDay AS 'L/D', "
  172. " ldgNight AS 'L/N', "
  173. " (SELECT printf('%02d',(tNight/60))||':'||printf('%02d',(tNight%60)) WHERE tNight IS NOT NULL) AS 'Night', "
  174. " (SELECT printf('%02d',(tIFR/60))||':'||printf('%02d',(tIFR%60)) WHERE tIFR IS NOT NULL) AS 'IFR', "
  175. " (SELECT printf('%02d',(tPIC/60))||':'||printf('%02d',(tPIC%60)) WHERE tPIC IS NOT NULL) AS 'PIC', "
  176. " (SELECT printf('%02d',(tSIC/60))||':'||printf('%02d',(tSIC%60)) WHERE tSIC IS NOT NULL) AS 'SIC', "
  177. " (SELECT printf('%02d',(tDual/60))||':'||printf('%02d',(tDual%60)) WHERE tDual IS NOT NULL) AS 'Dual', "
  178. " (SELECT printf('%02d',(tFI/60))||':'||printf('%02d',(tFI%60)) WHERE tFI IS NOT NULL) AS 'FI', "
  179. " remarks AS 'Remarks' "
  180. " FROM flights "
  181. " INNER JOIN pilots on flights.pic = pilots.pilot_id "
  182. " INNER JOIN tails on flights.acft = tails.tail_id "
  183. " ORDER BY date DESC");
  184. const auto createViewTails = QLatin1String("CREATE VIEW viewTails AS "
  185. " SELECT "
  186. " tail_id AS 'ID', "
  187. " registration AS 'Registration', "
  188. " make||' '||model AS 'Type', "
  189. " company AS 'Company' "
  190. " FROM tails WHERE model IS NOT NULL AND variant IS NULL "
  191. " UNION "
  192. " SELECT "
  193. " tail_id AS 'ID', "
  194. " registration AS 'Registration', "
  195. " make||' '||model||'-'||variant AS 'Type', "
  196. " company AS 'Company' "
  197. " FROM tails WHERE variant IS NOT NULL");
  198. const auto createViewPilots = QLatin1String("CREATE VIEW viewPilots AS "
  199. " SELECT "
  200. " pilot_id AS 'ID', "
  201. " lastname AS 'Last Name', "
  202. " firstname AS 'First Name', "
  203. " company AS 'Company' "
  204. " FROM pilots");
  205. const auto createViewQCompleter = QLatin1String("CREATE VIEW viewQCompleter AS "
  206. " SELECT airport_id, icao, iata, tail_id, registration, pilot_id, "
  207. " lastname||', '||firstname AS 'pilot_name', alias "
  208. " FROM airports "
  209. " LEFT JOIN tails ON airports.airport_id = tails.tail_id "
  210. " LEFT JOIN pilots ON airports.airport_id = pilots.pilot_id");
  211. const auto createViewTotals = QLatin1String("CREATE VIEW viewTotals AS "
  212. " SELECT "
  213. " printf(\"%02d\",CAST(SUM(tblk) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tblk) AS INT)%60) AS \"TOTAL\", "
  214. " printf(\"%02d\",CAST(SUM(tSPSE) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tSPSE) AS INT)%60) AS \"SP SE\", "
  215. " printf(\"%02d\",CAST(SUM(tSPME) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tSPME) AS INT)%60) AS \"SP ME\", "
  216. " printf(\"%02d\",CAST(SUM(tNIGHT) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tNIGHT) AS INT)%60) AS \"NIGHT\", "
  217. " printf(\"%02d\",CAST(SUM(tIFR) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tIFR) AS INT)%60) AS \"IFR\", "
  218. " printf(\"%02d\",CAST(SUM(tPIC) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tPIC) AS INT)%60) AS \"PIC\", "
  219. " printf(\"%02d\",CAST(SUM(tPICUS) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tPICUS) AS INT)%60) AS \"PICUS\", "
  220. " printf(\"%02d\",CAST(SUM(tSIC) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tSIC) AS INT)%60) AS \"SIC\", "
  221. " printf(\"%02d\",CAST(SUM(tDual) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tDual) AS INT)%60) AS \"DUAL\", "
  222. " printf(\"%02d\",CAST(SUM(tFI) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tFI) AS INT)%60) AS \"INSTRUCTOR\", "
  223. " printf(\"%02d\",CAST(SUM(tSIM) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tSIM) AS INT)%60) AS \"SIMULATOR\", "
  224. " printf(\"%02d\",CAST(SUM(tMP) AS INT)/60)||\":\"||printf(\"%02d\",CAST(SUM(tMP) AS INT)%60) AS \"MultPilot\", "
  225. " CAST(SUM(toDay) AS INT) AS \"TO Day\", CAST(SUM(toNight) AS INT) AS \"TO Night\", "
  226. " CAST(SUM(ldgDay) AS INT) AS \"LDG Day\", CAST(SUM(ldgNight) AS INT) AS \"LDG Night\" "
  227. " FROM flights");
  228. const QStringList tables = {
  229. createTablePilots,
  230. createTableTails,
  231. createTableFlights,
  232. createTableAircraft,
  233. createTableAirports,
  234. createTableCurrencies,
  235. createTableChangelog
  236. };
  237. const QStringList views = {
  238. createViewDefault,
  239. createViewEASA,
  240. createViewTails,
  241. createViewPilots,
  242. createViewTotals,
  243. createViewQCompleter,
  244. };
  245. const QStringList userTables = {
  246. QStringLiteral("flights"),
  247. QStringLiteral("pilots"),
  248. QStringLiteral("tails")
  249. };
  250. const QStringList templateTables= {
  251. QStringLiteral("aircraft"),
  252. QStringLiteral("airports"),
  253. QStringLiteral("currencies"),
  254. QStringLiteral("changelog")
  255. };
  256. QT_DEPRECATED
  257. bool ADataBaseSetup::createDatabase()
  258. {
  259. DEB << "Creating tables...";
  260. if (!createSchemata(tables)) {
  261. DEB << "Creating tables has failed.";
  262. return false;
  263. }
  264. DEB << "Creating views...";
  265. if (!createSchemata(views)) {
  266. DEB << "Creating views failed.";
  267. return false;
  268. }
  269. aDB->updateLayout();
  270. LOG << "Database successfully created!\n";
  271. return true;
  272. }
  273. QT_DEPRECATED
  274. bool ADataBaseSetup::downloadTemplates()
  275. {
  276. QDir template_dir(AStandardPaths::directory(AStandardPaths::Templates));
  277. DEB << template_dir;
  278. for (const auto& table : templateTables) {
  279. QEventLoop loop;
  280. ADownload* dl = new ADownload;
  281. QObject::connect(dl, &ADownload::done, &loop, &QEventLoop::quit );
  282. dl->setTarget(QUrl(TEMPLATE_URL + table + QLatin1String(".csv")));
  283. dl->setFileName(template_dir.absoluteFilePath(table + QLatin1String(".csv")));
  284. dl->download();
  285. dl->deleteLater();
  286. loop.exec(); // event loop waits for download done signal before allowing loop to continue
  287. QFileInfo downloaded_file(template_dir.filePath(table + QLatin1String(".csv")));
  288. if (downloaded_file.size() == 0)
  289. return false; // ssl/network error
  290. }
  291. return true;
  292. }
  293. QT_DEPRECATED
  294. bool ADataBaseSetup::backupOldData()
  295. {
  296. LOG << "Backing up old database...";
  297. QFileInfo database_file(AStandardPaths::directory(AStandardPaths::Database).
  298. absoluteFilePath(QStringLiteral("logbook.db")));
  299. DEB << "File Info:" << database_file;
  300. if(!database_file.exists()) {
  301. DEB << "No Database to backup, returning.";
  302. return true;
  303. }
  304. auto date_string = ADateTime::toString(QDateTime::currentDateTime(),
  305. Opl::Datetime::Backup);
  306. auto backup_dir = AStandardPaths::directory(AStandardPaths::Backup);
  307. QString backup_name = database_file.baseName() + QLatin1String("_bak_")
  308. + date_string + QLatin1String(".db");
  309. QFile file(database_file.absoluteFilePath());
  310. DEB << "File:" << file.fileName();
  311. if (!file.rename(backup_dir.absoluteFilePath(backup_name))) {
  312. LOG << "Unable to backup old database.\n";
  313. return false;
  314. }
  315. LOG << "Backed up old database as: " << backup_name << "\n";
  316. return true;
  317. }
  318. QT_DEPRECATED
  319. bool ADataBaseSetup::importDefaultData(bool use_ressource_data)
  320. {
  321. QSqlQuery query;
  322. // reset template tables
  323. for (const auto& table_name : templateTables) {
  324. //clear tables
  325. query.prepare("DELETE FROM " + table_name);
  326. if (!query.exec()) {
  327. DEB << "Error: " << query.lastError().text();
  328. return false;
  329. }
  330. // Prepare data
  331. QVector<QStringList> data_to_commit;
  332. QString error_message("Error importing data ");
  333. if (use_ressource_data) {
  334. data_to_commit = aReadCsv(QStringLiteral(":templates/database/templates/")
  335. + table_name + QLatin1String(".csv"));
  336. error_message.append(" (ressource) ");
  337. } else {
  338. data_to_commit = aReadCsv(AStandardPaths::directory(
  339. AStandardPaths::Templates).absoluteFilePath(
  340. table_name + QLatin1String(".csv")));
  341. error_message.append(" (downloaded) ");
  342. }
  343. //fill with data from csv
  344. if (!commitData(data_to_commit, table_name)) {
  345. LOG << error_message;
  346. return false;
  347. }
  348. }
  349. return true;
  350. };
  351. QT_DEPRECATED
  352. /*!
  353. * \brief DbSetup::resetToDefault Empties all user-generated content in the database.
  354. * \return true on success
  355. */
  356. bool ADataBaseSetup::resetToDefault()
  357. {
  358. QSqlQuery query;
  359. // clear user tables
  360. for (const auto& table : userTables) {
  361. query.prepare("DELETE FROM " + table);
  362. if (!query.exec()) {
  363. DEB << "Error: " << query.lastError().text();
  364. }
  365. }
  366. return true;
  367. }
  368. QT_DEPRECATED
  369. /*!
  370. * \brief dbSetup::debug prints Database Layout
  371. */
  372. void ADataBaseSetup::debug()
  373. {
  374. DEB << "Database tables and views: ";
  375. QSqlQuery query;
  376. const QVector<QString> types = { "table", "view" };
  377. for (const auto& var : types){
  378. query.prepare("SELECT name FROM sqlite_master WHERE type=" + var);
  379. query.exec();
  380. while (query.next()) {
  381. QString table = query.value(0).toString();
  382. QSqlQuery entries("SELECT COUNT(*) FROM " + table);
  383. entries.next();
  384. DEB << "Element " << query.value(0).toString() << "with"
  385. << entries.value(0).toString() << "rows";
  386. }
  387. }
  388. }
  389. QT_DEPRECATED
  390. /*!
  391. * \brief dbSetup::createTables Create the required tables for the database
  392. * \return true on success
  393. */
  394. bool ADataBaseSetup::createSchemata(const QStringList &statements)
  395. {
  396. QSqlQuery query;
  397. QStringList errors;
  398. for (const auto& statement : statements) {
  399. query.prepare(statement);
  400. query.exec();
  401. if(!query.isActive()) {
  402. errors << statement.section(QLatin1Char(' '),2,2) + " ERROR - " + query.lastError().text();
  403. DEB << "Query: " << query.lastQuery();
  404. continue;
  405. }
  406. DEB << "Schema added: " << statement.section(QLatin1Char(' '), 2, 2);
  407. }
  408. if (!errors.isEmpty()) {
  409. DEB << "The following errors have ocurred: ";
  410. for (const auto& error : qAsConst(errors)) {
  411. DEB << error;
  412. }
  413. return false;
  414. }
  415. LOG << "All database tables created successfully\n";
  416. return true;
  417. }
  418. QT_DEPRECATED
  419. /*!
  420. * \brief DbSetup::commitData inserts the data parsed from a csv file into the
  421. * database. The first line of the csv file has to contain the column names
  422. * of the corresponding table in the database.
  423. * \param fromCSV input as parsed from CSV::read()
  424. * \param tableName as in the database
  425. * \return
  426. */
  427. bool ADataBaseSetup::commitData(QVector<QStringList> from_csv, const QString &table_name)
  428. {
  429. aDB->updateLayout();
  430. if (!aDB->getTableNames().contains(table_name)){
  431. DEB << table_name << "is not a table in the database. Aborting.";
  432. DEB << "Please check input data.";
  433. return false;
  434. }
  435. // create insert statement
  436. QString statement = "INSERT INTO " + table_name + " (";
  437. QString placeholder = ") VALUES (";
  438. for (auto& csvColumn : from_csv) {
  439. if(aDB->getTableColumns(table_name).contains(csvColumn.first())) {
  440. statement += csvColumn.first() + ',';
  441. csvColumn.removeFirst();
  442. placeholder.append("?,");
  443. } else {
  444. DEB << csvColumn.first() << "is not a column of " << table_name << "Aborting.";
  445. DEB << "Please check input data.";
  446. return false;
  447. }
  448. }
  449. statement.chop(1);
  450. placeholder.chop(1);
  451. placeholder.append(')');
  452. statement.append(placeholder);
  453. /*
  454. * Using exclusive transaction and the loop below is MUCH faster than
  455. * passing the QStringLists to QSqlQuery::addBindValue and using QSqlQuery::execBatch()
  456. */
  457. QSqlQuery query;
  458. query.exec("BEGIN EXCLUSIVE TRANSACTION;");
  459. for (int i = 0; i < from_csv.first().length(); i++){
  460. query.prepare(statement);
  461. for(int j = 0; j < from_csv.length(); j++) {
  462. from_csv[j][i] == QString("") ? // make sure NULL is committed for empty values
  463. query.addBindValue(QVariant(QString()))
  464. : query.addBindValue(from_csv[j][i]);
  465. //query.addBindValue(fromCSV[j][i]);
  466. }
  467. query.exec();
  468. }
  469. query.exec("COMMIT;"); //commit transaction
  470. if (query.lastError().text().length() > 3) {
  471. DEB << "Error:" << query.lastError().text();
  472. return false;
  473. } else {
  474. qDebug() << table_name << "Database successfully updated!";
  475. return true;
  476. }
  477. }
  478. QT_DEPRECATED
  479. bool ADataBaseSetup::commitDataJson(const QJsonArray &json_arr, const QString &table_name)
  480. {
  481. aDB->updateLayout();
  482. QSqlQuery q;
  483. // create insert statement
  484. QString statement = QLatin1String("INSERT INTO ") + table_name + QLatin1String(" (");
  485. QString placeholder = QStringLiteral(") VALUES (");
  486. for (const auto &column_name : aDB->getTableColumns(table_name)) {
  487. statement += column_name + ',';
  488. placeholder.append(QLatin1Char(':') + column_name + QLatin1Char(','));
  489. }
  490. statement.chop(1);
  491. placeholder.chop(1);
  492. placeholder.append(')');
  493. statement.append(placeholder);
  494. q.prepare(QStringLiteral("BEGIN EXCLUSIVE TRANSACTION"));
  495. q.exec();
  496. //DEB << statement;
  497. for (const auto &entry : json_arr) {
  498. q.prepare(statement);
  499. auto object = entry.toObject();
  500. const auto keys = object.keys();
  501. for (const auto &key : keys){
  502. object.value(key).isNull() ? q.bindValue(key, QVariant(QVariant::String)) :
  503. q.bindValue(QLatin1Char(':') + key, object.value(key).toVariant());
  504. }
  505. q.exec();
  506. }
  507. q.prepare(QStringLiteral("COMMIT"));
  508. if (q.exec())
  509. return true;
  510. else
  511. return false;
  512. }