1 /***************************************************************************
2 * SPDX-FileCopyrightText: 2021 S. MANKOWSKI stephane@mankowski.fr
3 * SPDX-FileCopyrightText: 2021 G. DE BURE support@mankowski.fr
4 * SPDX-License-Identifier: GPL-3.0-or-later
5 ***************************************************************************/
6 /** @file
7 * This file implements classes SKGDocument.
8 *
9 * @author Stephane MANKOWSKI / Guillaume DE BURE
10 */
11 #include "skgdocument.h"
12
13 #include <kcolorscheme.h>
14
15 #include <qapplication.h>
16 #ifdef SKG_DBUS
17 #include <qdbusconnection.h>
18 #endif
19 #include <qdir.h>
20 #include <qfile.h>
21 #include <qhash.h>
22 #include <qicon.h>
23 #include <qjsondocument.h>
24 #include <qprocess.h>
25 #include <qregularexpression.h>
26 #include <qsqldatabase.h>
27 #include <qsqldriver.h>
28 #include <qsqlerror.h>
29 #include <qsqlquery.h>
30 #include <qtconcurrentrun.h>
31 #include <qthread.h>
32 #include <qurl.h>
33 #include <quuid.h>
34 #include <qvariant.h>
35
36 #include <sqlite3.h>
37 #include <cmath>
38
39 #include "skgdocumentprivate.h"
40 #include "skgerror.h"
41 #include "skgpropertyobject.h"
42 #include "skgreport.h"
43 #include "skgservices.h"
44 #include "skgtraces.h"
45 #include "skgtransactionmng.h"
46
47 #define SQLDRIVERNAME QStringLiteral("SKGSQLCIPHER")
48
49 /**
50 * Custom sqlite function.
51 */
sleepFunction(sqlite3_context * context,int,sqlite3_value ** argv)52 static void sleepFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
53 {
54 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
55 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
56
57 if (Q_LIKELY(data1)) {
58 auto s = SKGServices::stringToInt(QString(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar)));
59 QThread::sleep(s);
60
61 sqlite3_result_text(context, "OK", 2, SQLITE_TRANSIENT);
62 }
63 }
64
65 /**
66 * Custom sqlite function.
67 */
periodFunction(sqlite3_context * context,int,sqlite3_value ** argv)68 static void periodFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
69 {
70 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
71 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
72 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
73 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
74
75 if (Q_LIKELY(data1 && data2)) {
76 QDate date = SKGServices::stringToTime(QString(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar))).date();
77 QString format = QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar)).toUpper();
78 QString period = SKGServices::dateToPeriod(date, format);
79 QByteArray output = period.toUtf8();
80 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
81 }
82 }
83
84 /**
85 * Custom sqlite function.
86 */
formattedDateFunction(sqlite3_context * context,int,sqlite3_value ** argv)87 static void formattedDateFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
88 {
89 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
90 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
91 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
92 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
93
94 if (Q_LIKELY(data1 && data2)) {
95 QString string(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar));
96 QString format = QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar));
97
98 QString date = QDate::fromString(string, QStringLiteral("yyyy-MM-dd")).toString(format);
99
100 QByteArray output = date.toUtf8();
101 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
102 }
103 }
104
105 /**
106 * Custom sqlite function.
107 */
dateFunction(sqlite3_context * context,int,sqlite3_value ** argv)108 static void dateFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
109 {
110 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
111 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
112 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
113 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
114
115 if (Q_LIKELY(data1 && data2)) {
116 QString string(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar));
117 QString format = QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar));
118
119 QString date = SKGServices::dateToSqlString(string, format).trimmed();
120 if (date.isEmpty()) {
121 date = QDate::currentDate().toString(QStringLiteral("yyyy-MM-dd"));
122 }
123
124 QByteArray output = date.toUtf8();
125 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
126 }
127 }
128
129 /**
130 * Custom sqlite function.
131 */
currencyFunction(sqlite3_context * context,int,sqlite3_value ** argv)132 static void currencyFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
133 {
134 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
135 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
136 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
137 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
138
139 if (Q_LIKELY(data1 && data2)) {
140 double string = SKGServices::stringToDouble(QString::fromRawData(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar)));
141 QString symbol = QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar));
142
143 QString currency = SKGServices::toCurrencyString(string, symbol);
144
145 QByteArray output = currency.toUtf8();
146 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
147 }
148 }
149
150 /**
151 * Custom sqlite function.
152 */
xorFunction(sqlite3_context * context,int,sqlite3_value ** argv)153 static void xorFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
154 {
155 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
156 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
157 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
158 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
159
160 if (Q_LIKELY(data1 && data2)) {
161 auto string = QString::fromRawData(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar)).toUtf8();
162 auto key = QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar)).toUtf8();
163
164 if (string.startsWith(QByteArray("# "))) {
165 // Decrypt
166 string = QByteArray::fromHex(string.right(string.length() - 2));
167 QByteArray estring;
168 for (int i = 0; i < string.size(); ++i) {
169 estring += static_cast<char>(string[i] ^ key[i % key.size()]);
170 }
171 QByteArray output = estring;
172 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
173 } else {
174 // Encrypt
175 QByteArray estring;
176 for (int i = 0; i < string.size(); ++i) {
177 estring += static_cast<char>(string[i] ^ key[i % key.size()]);
178 }
179 QByteArray output = "# " + estring.toHex();
180 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
181 }
182 }
183 }
184
xordoubleFunction(sqlite3_context * context,int,sqlite3_value ** argv)185 static void xordoubleFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
186 {
187 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
188 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
189 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
190 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
191
192 if (Q_LIKELY(data1 && data2)) {
193 auto d = SKGServices::stringToDouble(QString::fromRawData(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar)).toUtf8());
194 auto key = QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar)).toUtf8();
195 int kk = 0;
196 for (int i = 0; i < key.size(); ++i) {
197 kk += key[i];
198 }
199 QByteArray output = SKGServices::doubleToString(static_cast<double>(kk) - d).toUtf8();
200 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
201 }
202 }
203
204 /**
205 * Custom sqlite function.
206 */
wordFunction(sqlite3_context * context,int,sqlite3_value ** argv)207 static void wordFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
208 {
209 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
210 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
211 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
212 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
213
214 if (Q_LIKELY(data1 && data2)) {
215 QString string1(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar));
216 string1 = string1.simplified();
217 QRegularExpression re(QStringLiteral("(\\w+)"));
218 QRegularExpressionMatchIterator i = re.globalMatch(string1);
219 QStringList list;
220 while (i.hasNext()) {
221 QRegularExpressionMatch match = i.next();
222 QString word = match.captured(1);
223 list << word;
224 }
225
226 int pos = SKGServices::stringToInt(QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar)));
227 if (pos == 0) {
228 pos = 1;
229 } else if (pos > list.count()) {
230 pos = list.count();
231 } else if (pos < -list.count()) {
232 pos = 1;
233 } else if (pos < 0) {
234 pos = list.count() + pos + 1;
235 }
236
237 QByteArray output = list[pos - 1].toUtf8();
238
239 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
240 }
241 }
242
243 /**
244 * Custom sqlite function.
245 */
wildcardFunction(sqlite3_context * context,int,sqlite3_value ** argv)246 static void wildcardFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
247 {
248 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
249 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
250 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
251 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
252
253 if (Q_LIKELY(data1 && data2)) {
254 QString string1(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar));
255 QString string2 = QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar));
256
257 QRegularExpression pattern(QRegularExpression::anchoredPattern(QRegularExpression::wildcardToRegularExpression(string1)), QRegularExpression::CaseInsensitiveOption);
258 if (pattern.isValid()) {
259 sqlite3_result_int(context, static_cast<int>(pattern.match(string2).hasMatch()));
260 } else {
261 sqlite3_result_error(context, pattern.errorString().toUtf8().constData(), -1);
262 }
263 }
264 }
265
266 /**
267 * Custom sqlite function.
268 */
regexpFunction(sqlite3_context * context,int,sqlite3_value ** argv)269 static void regexpFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
270 {
271 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
272 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
273 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
274 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
275
276 if (Q_LIKELY(data1 && data2)) {
277 QString string1(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar));
278 QString string2 = QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar));
279
280 QRegularExpression pattern(QRegularExpression::anchoredPattern(string1), QRegularExpression::CaseInsensitiveOption);
281 if (pattern.isValid()) {
282 sqlite3_result_int(context, static_cast<int>(pattern.match(string2).hasMatch()));
283 } else {
284 sqlite3_result_error(context, pattern.errorString().toUtf8().constData(), -1);
285 }
286 }
287 }
288
289 /**
290 * Custom sqlite function.
291 */
regexpCaptureFunction(sqlite3_context * context,int,sqlite3_value ** argv)292 static void regexpCaptureFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
293 {
294 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
295 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
296 int len2 = sqlite3_value_bytes16(argv[ 1 ]);
297 const void* data2 = sqlite3_value_text16(argv[ 1 ]);
298 int len3 = sqlite3_value_bytes16(argv[ 2 ]);
299 const void* data3 = sqlite3_value_text16(argv[ 2 ]);
300 if (Q_LIKELY(data1 && data2 && data3)) {
301 int pos = SKGServices::stringToInt(QString::fromRawData(reinterpret_cast<const QChar*>(data3), len3 / sizeof(QChar)));
302
303 QString string1(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar));
304 QString string2 = QString::fromRawData(reinterpret_cast<const QChar*>(data2), len2 / sizeof(QChar));
305
306 QRegularExpression pattern(string1, QRegularExpression::CaseInsensitiveOption);
307 if (pattern.isValid()) {
308 auto match = pattern.match(string2);
309 QByteArray output = match.capturedTexts().value(pos).toUtf8();
310 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
311 } else {
312 sqlite3_result_error(context, pattern.errorString().toUtf8().constData(), -1);
313 }
314 }
315 }
316
317 /**
318 * Custom sqlite function.
319 */
upperFunction(sqlite3_context * context,int,sqlite3_value ** argv)320 static void upperFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
321 {
322 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
323 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
324
325 if (Q_LIKELY(data1)) {
326 QByteArray output = QString::fromRawData(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar)).toUpper().toUtf8();
327 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
328 }
329 }
330
331 /**
332 * Custom sqlite function.
333 */
nextFunction(sqlite3_context * context,int,sqlite3_value ** argv)334 static void nextFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
335 {
336 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
337 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
338
339 if (Q_LIKELY(data1)) {
340 QByteArray output = SKGServices::getNextString(QString::fromRawData(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar))).toUtf8();
341 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
342 }
343 }
344
345 /**
346 * Custom sqlite function.
347 */
lowerFunction(sqlite3_context * context,int,sqlite3_value ** argv)348 static void lowerFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
349 {
350 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
351 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
352
353 if (Q_LIKELY(data1)) {
354 QByteArray output = QString::fromRawData(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar)).toLower().toUtf8();
355 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
356 }
357 }
358
359 /**
360 * Custom sqlite function.
361 */
capitalizeFunction(sqlite3_context * context,int,sqlite3_value ** argv)362 static void capitalizeFunction(sqlite3_context* context, int /*argc*/, sqlite3_value** argv)
363 {
364 int len1 = sqlite3_value_bytes16(argv[ 0 ]);
365 const void* data1 = sqlite3_value_text16(argv[ 0 ]);
366
367 if (Q_LIKELY(data1)) {
368 QString str = QString::fromRawData(reinterpret_cast<const QChar*>(data1), len1 / sizeof(QChar));
369 QByteArray output = (str.at(0).toUpper() + str.mid(1).toLower()).toUtf8();
370 sqlite3_result_text(context, output.constData(), output.size(), SQLITE_TRANSIENT);
371 }
372 }
373
addSqliteAddon(QSqlDatabase * iDb)374 static SKGError addSqliteAddon(QSqlDatabase* iDb)
375 {
376 SKGError err;
377 auto* sqlite_handle = iDb->driver()->handle().value<sqlite3*>();
378 if (sqlite_handle != nullptr) {
379 sqlite3_create_function(sqlite_handle, "REGEXP", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, ®expFunction, nullptr, nullptr);
380 sqlite3_create_function(sqlite_handle, "REGEXPCAPTURE", 3, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, ®expCaptureFunction, nullptr, nullptr);
381 sqlite3_create_function(sqlite_handle, "WILDCARD", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &wildcardFunction, nullptr, nullptr);
382 sqlite3_create_function(sqlite_handle, "WORD", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &wordFunction, nullptr, nullptr);
383 sqlite3_create_function(sqlite_handle, "TODATE", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &dateFunction, nullptr, nullptr);
384 sqlite3_create_function(sqlite_handle, "TOFORMATTEDDATE", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &formattedDateFunction, nullptr, nullptr);
385 sqlite3_create_function(sqlite_handle, "PERIOD", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &periodFunction, nullptr, nullptr);
386 sqlite3_create_function(sqlite_handle, "SLEEP", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &sleepFunction, nullptr, nullptr);
387 sqlite3_create_function(sqlite_handle, "TOCURRENCY", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, ¤cyFunction, nullptr, nullptr);
388 sqlite3_create_function(sqlite_handle, "XOR", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &xorFunction, nullptr, nullptr);
389 sqlite3_create_function(sqlite_handle, "XORD", 2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &xordoubleFunction, nullptr, nullptr);
390 sqlite3_create_function(sqlite_handle, "UPPER", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &upperFunction, nullptr, nullptr);
391 sqlite3_create_function(sqlite_handle, "LOWER", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &lowerFunction, nullptr, nullptr);
392 sqlite3_create_function(sqlite_handle, "NEXT", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &nextFunction, nullptr, nullptr);
393 int rc = sqlite3_create_function(sqlite_handle, "CAPITALIZE", 1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, nullptr, &capitalizeFunction, nullptr, nullptr);
394 if (rc != SQLITE_OK) {
395 err = SKGError(SQLLITEERROR + rc, QStringLiteral("sqlite3_create_function failed"));
396 }
397 } else {
398 SKGTRACE << "WARNING: Custom sqlite functions not added" << SKGENDL;
399 }
400 return err;
401 }
402
SKGDocument()403 SKGDocument::SKGDocument()
404 : d(new SKGDocumentPrivate())
405 {
406 SKGTRACEINFUNC(10)
407 // Set the QThreadPool
408 // QThreadPool::globalInstance()->setMaxThreadCount(3*QThread::idealThreadCount());
409
410 // DBUS registration
411 #ifdef SKG_DBUS
412 QDBusConnection dbus = QDBusConnection::sessionBus();
413 dbus.registerObject(QStringLiteral("/skg/skgdocument"), this, QDBusConnection::ExportAllContents);
414 dbus.registerService(QStringLiteral("org.skg"));
415 #endif
416
417 // Initialisation of undoable tables
418 SKGListNotUndoable.push_back(QStringLiteral("T.doctransaction"));
419 SKGListNotUndoable.push_back(QStringLiteral("T.doctransactionitem"));
420 SKGListNotUndoable.push_back(QStringLiteral("T.doctransactionmsg"));
421
422 // Database unique identifier
423 ++SKGDocumentPrivate::m_databaseUniqueIdentifier;
424 d->m_databaseIdentifier = "SKGDATABASE_" % SKGServices::intToString(SKGDocumentPrivate::m_databaseUniqueIdentifier);
425
426 // Initialisation of backup file parameters
427 setBackupParameters(QLatin1String(""), QStringLiteral(".old"));
428
429 // 320157 vvvv
430 // Disable OS lock
431 sqlite3_vfs* vfs = sqlite3_vfs_find("unix-none");
432 if (Q_LIKELY(vfs)) {
433 sqlite3_vfs_register(vfs, 1);
434 } else {
435 SKGTRACE << "WARNING: Impossible to use the 'unix-none' vfs mode of sqlite3. Use:'" << sqlite3_vfs_find(nullptr)->zName << "'" << SKGENDL;
436 }
437 // 320157 ^^^^
438 }
439
~SKGDocument()440 SKGDocument::~SKGDocument()
441 {
442 SKGTRACEINFUNC(10)
443 close();
444 d->m_progressFunction = nullptr;
445 d->m_progressData = nullptr;
446 d->m_checkFunctions.clear();
447
448 for (auto w : qAsConst(d->m_watchers)) {
449 delete w;
450 }
451 d->m_watchers.clear();
452
453 delete d->m_cacheSql;
454 d->m_cacheSql = nullptr;
455
456 delete d;
457 d = nullptr;
458 }
459
getUniqueIdentifier() const460 QString SKGDocument::getUniqueIdentifier() const
461 {
462 return d->m_uniqueIdentifier;
463 }
464
getDatabaseIdentifier() const465 QString SKGDocument::getDatabaseIdentifier() const
466 {
467 return d->m_databaseIdentifier;
468 }
469
setProgressCallback(FuncProgress iProgressFunction,void * iData)470 SKGError SKGDocument::setProgressCallback(FuncProgress iProgressFunction, void* iData)
471 {
472 d->m_progressFunction = iProgressFunction;
473 d->m_progressData = iData;
474 return SKGError();
475 }
476
addEndOfTransactionCheck(SKGError (* iCheckFunction)(SKGDocument *))477 SKGError SKGDocument::addEndOfTransactionCheck(SKGError(*iCheckFunction)(SKGDocument*))
478 {
479 d->m_checkFunctions.append(iCheckFunction);
480 return SKGError();
481 }
482
stepForward(int iPosition,const QString & iText)483 SKGError SKGDocument::stepForward(int iPosition, const QString& iText)
484 {
485 SKGError err;
486
487 // Increase the step for the last transaction
488 if (Q_LIKELY(getDepthTransaction())) {
489 d->m_posStepForTransaction.pop_back();
490 d->m_posStepForTransaction.push_back(iPosition);
491 }
492
493 // Check if a callback function exists
494 if (Q_LIKELY(d->m_progressFunction)) {
495 // YES ==> compute
496 double min = 0;
497 double max = 100;
498
499 bool emitevent = true;
500 auto nbIt = d->m_nbStepForTransaction.constBegin();
501 auto posIt = d->m_posStepForTransaction.constBegin();
502 for (; emitevent && nbIt != d->m_nbStepForTransaction.constEnd(); ++nbIt) {
503 int p = *posIt;
504 int n = *nbIt;
505 if (Q_UNLIKELY(p < 0 || p > n)) {
506 p = n;
507 }
508
509 if (Q_LIKELY(n != 0)) {
510 double pmin = min;
511 double pmax = max;
512 min = pmin + (pmax - pmin) * (static_cast<double>(p) / static_cast<double>(n));
513 max = pmin + (pmax - pmin) * (static_cast<double>(p + 1) / static_cast<double>(n));
514 if (Q_UNLIKELY(max > 100)) {
515 max = 100;
516 }
517 } else {
518 emitevent = false;
519 }
520
521 ++posIt;
522 }
523
524 int posPercent = rint(min);
525
526 // Call the call back
527 if (emitevent) {
528 d->m_inProgress = true;
529 QString text;
530 qint64 time = QDateTime::currentMSecsSinceEpoch() - d->m_timeBeginTransaction;
531 if (Q_UNLIKELY(time > 3000)) {
532 text = iText;
533 if (text.isEmpty()) {
534 text = d->m_nameForTransaction.at(d->m_nameForTransaction.count() - 1);
535 }
536 }
537 if (Q_LIKELY(d->m_progressFunction(posPercent, time, text, d->m_progressData) != 0)) {
538 err.setReturnCode(ERR_ABORT).setMessage(i18nc("User interrupted something that Skrooge was performing", "The current operation has been interrupted"));
539
540 // Remove all untransactionnal messaged
541 m_unTransactionnalMessages.clear();
542 }
543 d->m_inProgress = false;
544 }
545 }
546 return err;
547 }
548
beginTransaction(const QString & iName,int iNbStep,const QDateTime & iDate,bool iRefreshViews)549 SKGError SKGDocument::beginTransaction(const QString& iName, int iNbStep, const QDateTime& iDate, bool iRefreshViews)
550 {
551 SKGError err;
552 SKGTRACEINFUNCRC(5, err)
553 SKGTRACEL(10) << "Input parameter [name]=[" << iName << "] [nb step]=[" << iNbStep << "] [refresh]=[" << (iRefreshViews ? QStringLiteral("Y") : QStringLiteral("N")) << ']' << SKGENDL;
554 bool overrideCursor = false;
555 if (d->m_nbStepForTransaction.isEmpty()) {
556 // Open SQLtransaction
557 err = executeSqliteOrder(QStringLiteral("BEGIN;"));
558 IFOK(err) {
559 overrideCursor = true;
560
561 // Create undo redo transaction
562 err = executeSqliteOrder(QStringLiteral("insert into doctransaction (d_date, t_name, i_parent") %
563 (!iRefreshViews ? ", t_refreshviews" : "") %
564 ") values "
565 "('" % SKGServices::timeToString(iDate) %
566 "','" % SKGServices::stringToSqlString(iName) %
567 "', " % SKGServices::intToString(getTransactionToProcess(SKGDocument::UNDO)) %
568 (!iRefreshViews ? ",'N'" : "") %
569 ");");
570 addValueInCache(QStringLiteral("SKG_REFRESH_VIEW"), (iRefreshViews ? QStringLiteral("Y") : QStringLiteral("N")));
571 d->m_currentTransaction = getTransactionToProcess(SKGDocument::UNDO);
572 d->m_timeBeginTransaction = QDateTime::currentMSecsSinceEpoch();
573 }
574 } else {
575 // A transaction already exists
576 // Check if the child transaction is a opened in the progress callback
577 if (d->m_inProgress) {
578 err.setReturnCode(ERR_FAIL).setMessage(i18nc("Something went wrong with SQL transactions", "A transaction cannot be started during execution of another one"));
579 }
580 }
581 IFOK(err) {
582 d->m_nbStepForTransaction.push_back(iNbStep);
583 d->m_posStepForTransaction.push_back(iNbStep);
584 QString n = iName;
585 n = n.remove(QStringLiteral("#INTERNAL#"));
586 if (n.isEmpty() && !d->m_nameForTransaction.isEmpty()) {
587 n = d->m_nameForTransaction.at(d->m_nameForTransaction.count() - 1);
588 }
589 d->m_nameForTransaction.push_back(n);
590
591 if (iNbStep > 0) {
592 err = stepForward(0);
593 }
594 } else {
595 executeSqliteOrder(QStringLiteral("ROLLBACK;"));
596 }
597
598 if (Q_LIKELY(overrideCursor && !err && qobject_cast<QGuiApplication*>(qApp) != nullptr)) { // clazy:excludeall=unneeded-cast
599 QApplication::setOverrideCursor(QCursor(Qt::WaitCursor));
600 }
601
602 return err;
603 }
604
checkExistingTransaction() const605 SKGError SKGDocument::checkExistingTransaction() const
606 {
607 SKGError err;
608 if (d->m_nbStepForTransaction.isEmpty()) {
609 err.setReturnCode(ERR_ABORT).setMessage(i18nc("Something went wrong with SQL transactions", "A transaction must be opened to do this action"));
610 }
611 return err;
612 }
613
endTransaction(bool succeedded)614 SKGError SKGDocument::endTransaction(bool succeedded)
615 {
616 SKGError err;
617 SKGError errOverwritten;
618 SKGTRACEINFUNCRC(5, err)
619 if (Q_UNLIKELY(d->m_nbStepForTransaction.empty())) {
620 err.setReturnCode(ERR_ABORT).setMessage(i18nc("Something went wrong with SQL transactions", "Closing transaction failed because too many transactions ended"));
621 } else {
622 stepForward(d->m_nbStepForTransaction.at(d->m_nbStepForTransaction.count() - 1)); // =100%
623 if (Q_LIKELY(d->m_nbStepForTransaction.size())) { // This test is needed. It is a security in some cases.
624 d->m_nbStepForTransaction.pop_back();
625 d->m_posStepForTransaction.pop_back();
626 d->m_nameForTransaction.pop_back();
627 }
628 QString currentTransactionString = SKGServices::intToString(getCurrentTransaction());
629
630 if (d->m_nbStepForTransaction.empty()) {
631 QStringList listModifiedTables;
632
633 // Check
634 if (succeedded) {
635 auto cachepointer = d->m_cache;
636 d->m_cache = QHash<QString, QString>();
637
638 for (auto check : qAsConst(d->m_checkFunctions)) {
639 errOverwritten = check(this);
640 IFKO(errOverwritten) {
641 succeedded = false;
642 SKGTRACEL(5) << "Transaction cancelled by a check" << SKGENDL;
643 break;
644 }
645 }
646
647 d->m_cache = cachepointer;
648 }
649
650 if (succeedded) {
651 // Link items on current transaction
652 IFOK(err) {
653 err = executeSqliteOrder("UPDATE doctransactionitem set rd_doctransaction_id=" % currentTransactionString % " WHERE rd_doctransaction_id=0;");
654 }
655
656 // Optimization of the current transaction
657 IFOK(err) {
658 SKGStringListList listTmp;
659 err = executeSelectSqliteOrder("SELECT count(1) FROM doctransactionitem where rd_doctransaction_id=" % currentTransactionString, listTmp);
660 IFOK(err) {
661 int nbItem = SKGServices::stringToInt(listTmp.at(1).at(0));
662 if (nbItem == 0) {
663 // Optimization is needed
664 // Get non hidden messages
665 SKGMessageList messages;
666 getMessages(getCurrentTransaction(), messages, false);
667
668 // Delete current transaction
669 err = executeSqliteOrder("DELETE FROM doctransaction WHERE id=" % currentTransactionString);
670
671 int nb = messages.count();
672 for (int i = 0; i < nb; ++i) {
673 m_unTransactionnalMessages.push_back(messages.at(i));
674 }
675 }
676 }
677 }
678
679 // Optimization 2: remove duplicate orders
680 IFOK(err) {
681 QString wc = "DELETE FROM doctransactionitem WHERE id IN "
682 "(SELECT a.id FROM doctransactionitem a INDEXED BY idx_doctransactionitem_optimization, doctransactionitem b INDEXED BY idx_doctransactionitem_optimization "
683 "WHERE a.rd_doctransaction_id=" % currentTransactionString % " AND b.rd_doctransaction_id=" % currentTransactionString %
684 " AND a.i_object_id=b.i_object_id AND a.t_object_table=b.t_object_table AND b.t_action=a.t_action AND b.t_sqlorder=a.t_sqlorder AND a.id>b.id );";
685 err = executeSqliteOrder(wc);
686 }
687
688 // Get current transaction information to be able to emit envent in case of SKG_UNDO_MAX_DEPTH=0
689 IFOK(err) {
690 err = this->getDistinctValues(QStringLiteral("doctransactionitem"),
691 QStringLiteral("t_object_table"),
692 "rd_doctransaction_id=" % currentTransactionString,
693 listModifiedTables);
694 }
695
696 // Remove oldest transaction
697 IFOK(err) {
698 QString maxdepthstring = getParameter(QStringLiteral("SKG_UNDO_MAX_DEPTH"));
699 if (maxdepthstring.isEmpty()) {
700 maxdepthstring = QStringLiteral("-1");
701 }
702 int maxdepth = SKGServices::stringToInt(maxdepthstring);
703 if (maxdepth >= 0) {
704 err = executeSqliteOrder("delete from doctransaction where id in (select id from doctransaction limit max(0,((select count(1) from doctransaction)-(" % maxdepthstring % "))))");
705 }
706 }
707
708 // Remove SKGDocument::REDO transactions if we are not in a undo / redo transaction
709 if (d->m_inundoRedoTransaction == 0) {
710 int i = 0;
711 while (((i = getTransactionToProcess(SKGDocument::REDO)) != 0) && !err) {
712 err = executeSqliteOrder("delete from doctransaction where id=" % SKGServices::intToString(i));
713 }
714 }
715
716 // Commit the transaction
717 IFOK(err) {
718 err = executeSqliteOrder(QStringLiteral("COMMIT;"));
719 }
720 }
721
722 // clean cache sql (must be done before event emit)
723 d->m_cacheSql->clear();
724
725 if (!succeedded || err) {
726 // Rollback the transaction
727 SKGError err2 = executeSqliteOrder(QStringLiteral("ROLLBACK;"));
728 // delete the transaction
729 IFOKDO(err2, executeSqliteOrder("delete from doctransaction where id=" % currentTransactionString))
730
731 if (err2) {
732 err.addError(err2.getReturnCode(), err2.getMessage());
733 }
734 } else {
735 // For better performance, events are submitted only for the first recursive undo
736 if (Q_UNLIKELY(d->m_inundoRedoTransaction <= 1)) {
737 // Is it a light transaction?
738 bool lightTransaction = (getCachedValue(QStringLiteral("SKG_REFRESH_VIEW")) != QStringLiteral("Y"));
739
740 // Emit modification events
741 QStringList tablesRefreshed;
742 tablesRefreshed.reserve(listModifiedTables.count());
743 for (const auto& table : qAsConst(listModifiedTables)) {
744 Q_EMIT tableModified(table, getCurrentTransaction(), lightTransaction);
745 tablesRefreshed.push_back(table);
746 }
747
748 // Remove temporary transaction if needed
749 IFOKDO(err, executeSqliteOrder(QStringLiteral("delete from doctransaction where t_name LIKE '#INTERNAL#%';")))
750
751 Q_EMIT tableModified(QStringLiteral("doctransaction"), getCurrentTransaction(), lightTransaction);
752 Q_EMIT tableModified(QStringLiteral("doctransactionitem"), getCurrentTransaction(), lightTransaction);
753
754 // WARNING: list is modified during treatement
755 for (int i = 0; !err && i < listModifiedTables.count(); ++i) {
756 QString table = listModifiedTables.at(i);
757 QStringList toAdd = getImpactedViews(table);
758 int nbToAdd = toAdd.count();
759 for (int j = 0; !err && j < nbToAdd; ++j) {
760 const QString& toAddTable = toAdd.at(j);
761 if (!listModifiedTables.contains(toAddTable)) {
762 // Compute materialized view of modified table
763 if (!lightTransaction) {
764 err = computeMaterializedViews(toAddTable);
765 }
766 listModifiedTables.push_back(toAddTable);
767 }
768 }
769 }
770
771 // Emit events
772 for (int i = tablesRefreshed.count(); i < listModifiedTables.count(); ++i) {
773 Q_EMIT tableModified(listModifiedTables.at(i), 0, lightTransaction);
774 }
775
776 Q_EMIT transactionSuccessfullyEnded(getCurrentTransaction());
777 }
778 }
779
780 // clean cache
781 d->m_cache.clear();
782
783 d->m_currentTransaction = 0;
784
785 if (Q_LIKELY(qobject_cast<QGuiApplication*>(qApp) != nullptr)) { // clazy:excludeall=unneeded-cast
786 QApplication::restoreOverrideCursor();
787 }
788 }
789 }
790
791 IFOK(err) {
792 err = errOverwritten;
793 }
794 return err;
795 }
796
removeAllTransactions()797 SKGError SKGDocument::removeAllTransactions()
798 {
799 SKGError err;
800 SKGTRACEINFUNCRC(10, err)
801 // Check if a transaction is still opened
802 err = checkExistingTransaction();
803 IFOK(err) err.setReturnCode(ERR_ABORT).setMessage(i18nc("Something went wrong with SQL transactions", "Remove of transactions is forbidden inside a transaction"));
804 else {
805 err = SKGDocument::beginTransaction(QStringLiteral("#INTERNAL#"));
806 IFOKDO(err, executeSqliteOrder(QStringLiteral("delete from doctransaction")))
807 SKGENDTRANSACTION(this, err)
808
809 // Force the save
810 d->m_lastSavedTransaction = -1;
811 }
812 return err;
813 }
814
computeMaterializedViews(const QString & iTable) const815 SKGError SKGDocument::computeMaterializedViews(const QString& iTable) const
816 {
817 SKGError err;
818 SKGTRACEINFUNCRC(5, err)
819
820 // Compute additional where clause
821 QStringList tables;
822 if (d->m_MaterializedViews.contains(iTable)) {
823 tables = d->m_MaterializedViews[iTable];
824 } else {
825 QString wc;
826 if (!iTable.isEmpty()) {
827 QString t = iTable;
828 if (t.startsWith(QLatin1String("v_"))) {
829 t.replace(0, 2, QStringLiteral("vm_"));
830 }
831 wc = " AND name='" % t % '\'';
832 }
833
834 // Get list of materialized table
835 err = getDistinctValues(QStringLiteral("sqlite_master"), QStringLiteral("name"), "type='table' AND name LIKE 'vm_%' " % wc, tables);
836 d->m_MaterializedViews[iTable] = tables;
837 }
838
839 // Refresh tables
840 int nb = tables.count();
841 for (int i = 0; !err && i < nb; ++i) {
842 const QString& table = tables.at(i);
843 QString view = table;
844 view.replace(0, 3, QStringLiteral("v_"));
845
846 // Remove previous table
847 {
848 SKGTRACEINRC(5, "SKGDocument::computeMaterializedViews-drop-" % table, err)
849 err = executeSqliteOrder("DROP TABLE IF EXISTS " % table);
850 }
851 {
852 // Recreate table
853 SKGTRACEINRC(5, "SKGDocument::computeMaterializedViews-create-" % table, err)
854 IFOKDO(err, executeSqliteOrder("CREATE TABLE " % table % " AS SELECT * FROM " % view))
855 }
856 }
857
858 return err;
859 }
860
sendMessage(const QString & iMessage,MessageType iMessageType,const QString & iAction)861 SKGError SKGDocument::sendMessage(const QString& iMessage, MessageType iMessageType, const QString& iAction)
862 {
863 SKGError err;
864 SKGTRACEINFUNCRC(10, err)
865 // Associate message with transaction
866 if (!checkExistingTransaction()) {
867 SKGObjectBase msg(this, QStringLiteral("doctransactionmsg"));
868 err = msg.setAttribute(QStringLiteral("rd_doctransaction_id"), SKGServices::intToString(getCurrentTransaction()));
869 IFOKDO(err, msg.setAttribute(QStringLiteral("t_message"), iMessage))
870 IFOKDO(err, msg.setAttribute(QStringLiteral("t_type"), iMessageType == SKGDocument::Positive ? QStringLiteral("P") :
871 iMessageType == SKGDocument::Information ? QStringLiteral("I") :
872 iMessageType == SKGDocument::Warning ? QStringLiteral("W") :
873 iMessageType == SKGDocument::Error ? QStringLiteral("E") : QStringLiteral("H")))
874 IFOKDO(err, msg.save())
875 }
876
877 if (checkExistingTransaction() || !iAction.isEmpty()) {
878 // Addition message in global variable in case of no transaction opened
879 bool found = false;
880 for (const auto& m : qAsConst(m_unTransactionnalMessages)) {
881 if (m.Text == iMessage) {
882 found = true;
883 }
884 }
885 if (iMessageType != SKGDocument::Hidden && !found) {
886 SKGMessage m;
887 m.Text = iMessage;
888 m.Type = iMessageType;
889 m.Action = iAction;
890 m_unTransactionnalMessages.push_back(m);
891 }
892 }
893 return err;
894 }
895
removeMessages(int iIdTransaction)896 SKGError SKGDocument::removeMessages(int iIdTransaction)
897 {
898 SKGError err;
899 SKGTRACEINFUNCRC(10, err)
900
901 if (!checkExistingTransaction()) {
902 err = executeSqliteOrder("DELETE FROM doctransactionmsg WHERE rd_doctransaction_id=" % SKGServices::intToString(iIdTransaction));
903 }
904
905 m_unTransactionnalMessages.clear();
906 return err;
907 }
908
getMessages(int iIdTransaction,SKGMessageList & oMessages,bool iAll)909 SKGError SKGDocument::getMessages(int iIdTransaction, SKGMessageList& oMessages, bool iAll)
910 {
911 SKGError err;
912 SKGTRACEINFUNCRC(10, err)
913 oMessages = m_unTransactionnalMessages;
914
915 SKGStringListList listTmp;
916 err = executeSelectSqliteOrder(
917 QStringLiteral("SELECT t_message, t_type FROM doctransactionmsg WHERE ") %
918 (iAll ? "" : "t_type<>'H' AND ") %
919 "rd_doctransaction_id=" %
920 SKGServices::intToString(iIdTransaction) %
921 " ORDER BY id ASC",
922 listTmp);
923
924 int nb = listTmp.count();
925 for (int i = 1; !err && i < nb ; ++i) {
926 QString msg = listTmp.at(i).at(0);
927 QString type = listTmp.at(i).at(1);
928 bool found = false;
929 for (const auto& m : qAsConst(m_unTransactionnalMessages)) {
930 if (m.Text == msg) {
931 found = true;
932 }
933 }
934 if (!found) {
935 SKGMessage m;
936 m.Text = msg;
937 m.Type = type == QStringLiteral("P") ? SKGDocument::Positive : type == QStringLiteral("I") ? SKGDocument::Information : type == QStringLiteral("W") ? SKGDocument::Warning : type == QStringLiteral("E") ? SKGDocument::Error : SKGDocument::Hidden;
938 oMessages.push_back(m);
939 }
940 }
941
942 m_unTransactionnalMessages.clear();
943 return err;
944 }
945
getModifications(int iIdTransaction,SKGObjectModificationList & oModifications) const946 SKGError SKGDocument::getModifications(int iIdTransaction, SKGObjectModificationList& oModifications) const
947 {
948 SKGError err;
949 SKGTRACEINFUNCRC(10, err)
950 oModifications.clear();
951
952 SKGStringListList listTmp;
953 err = executeSelectSqliteOrder(
954 "SELECT i_object_id,t_object_table,t_action FROM doctransactionitem WHERE rd_doctransaction_id=" %
955 SKGServices::intToString(iIdTransaction) %
956 " ORDER BY id ASC",
957 listTmp);
958 int nb = listTmp.count();
959 for (int i = 1; !err && i < nb ; ++i) {
960 SKGObjectModification mod;
961 mod.id = SKGServices::stringToInt(listTmp.at(i).at(0));
962 mod.table = listTmp.at(i).at(1);
963 QString type = listTmp.at(i).at(2);
964 mod.type = (type == QStringLiteral("D") ? I : (type == QStringLiteral("I") ? D : U)); // Normal because in database we have to sql order to go back.
965 mod.uuid = listTmp.at(i).at(0) % '-' % mod.table;
966
967 oModifications.push_back(mod);
968 }
969 return err;
970 }
971
getImpactedViews(const QString & iTable) const972 QStringList SKGDocument::getImpactedViews(const QString& iTable) const
973 {
974 SKGTRACEINFUNC(10)
975 if (Q_UNLIKELY(d->m_ImpactedViews.isEmpty())) {
976 // Get list of tables and views
977 QStringList tables;
978 SKGStringListList result;
979 executeSelectSqliteOrder(QStringLiteral("SELECT tbl_name FROM sqlite_master WHERE tbl_name NOT LIKE '%_delete' AND type IN ('table', 'view')"), result);
980 int nb = result.count();
981 tables.reserve(nb);
982 for (int i = 1; i < nb; ++i) {
983 tables.push_back(result.at(i).at(0));
984 }
985
986 // First computation
987 executeSelectSqliteOrder(QStringLiteral("SELECT tbl_name, sql FROM sqlite_master WHERE tbl_name NOT LIKE '%_delete' AND type='view'"), result);
988 nb = result.count();
989 for (int i = 1; i < nb; ++i) {
990 const QStringList& line = result.at(i);
991 const QString& name = line.at(0);
992 const QString& sql = line.at(1);
993
994 QStringList words = SKGServices::splitCSVLine(sql, ' ', false);
995 words.push_back(QStringLiteral("parameters"));
996 int nbWords = words.count();
997 for (int j = 0; j < nbWords; ++j) {
998 QString word = words.at(j);
999 word = word.remove(',');
1000 if (word.startsWith(QLatin1String("vm_"))) {
1001 word.replace(0, 3, QStringLiteral("v_"));
1002 }
1003 if (word != name && tables.contains(word, Qt::CaseInsensitive)) {
1004 QStringList l = d->m_ImpactedViews.value(word);
1005 if (!l.contains(name)) {
1006 l.push_back(name);
1007 }
1008 d->m_ImpactedViews[word] = l;
1009 }
1010 }
1011 }
1012
1013 // Now, we have some thing like this
1014 // d->m_ImpactedViews[A]={ B, C, D}
1015 // d->m_ImpactedViews[B]={ E, F}
1016 // We must build d->m_ImpactedViews[A]={ B, C, D, E, F}
1017 QStringList keys = d->m_ImpactedViews.keys();
1018 for (const auto& k : qAsConst(keys)) {
1019 QStringList l = d->m_ImpactedViews.value(k);
1020 for (int i = 0; i < l.count(); ++i) { // Warning: the size of l will change in the loop
1021 QString item = l.at(i);
1022 if (d->m_ImpactedViews.contains(item)) {
1023 // No qAsConst here, item is already const
1024 for (const auto& name : d->m_ImpactedViews.value(item)) {
1025 if (!l.contains(name)) {
1026 l.push_back(name);
1027 }
1028 }
1029 }
1030 }
1031 d->m_ImpactedViews[k] = l;
1032 }
1033 }
1034 return d->m_ImpactedViews.value(iTable);
1035 }
1036
groupTransactions(int iFrom,int iTo)1037 SKGError SKGDocument::groupTransactions(int iFrom, int iTo)
1038 {
1039 SKGError err;
1040 SKGTRACEINFUNCRC(5, err)
1041
1042 ++d->m_inundoRedoTransaction; // It is a kind of undo redo
1043
1044 // Check if a transaction is still opened
1045 err = checkExistingTransaction();
1046 IFOK(err) err.setReturnCode(ERR_ABORT).setMessage(i18nc("Something went wrong with SQL transactions", "Creation of a group of transactions is forbidden inside a transaction"));
1047 else {
1048 int iidMaster = qMax(iFrom, iTo);
1049 QString smin = SKGServices::intToString(qMin(iFrom, iTo));
1050 QString smax = SKGServices::intToString(iidMaster);
1051
1052 // Get transaction
1053 SKGStringListList transactions;
1054 err = executeSelectSqliteOrder(
1055 QStringLiteral("SELECT id, t_name, t_mode, i_parent FROM doctransaction WHERE id BETWEEN ") %
1056 smin % " AND " %
1057 smax % " ORDER BY id ASC",
1058 transactions);
1059
1060 // Check and get main parameter for the group
1061 int nb = transactions.count();
1062 QString transactionMode;
1063 QString communParent;
1064 QString name;
1065 for (int i = 1; !err && i < nb; ++i) { // We forget header
1066 const QStringList& transaction = transactions.at(i);
1067 const QString& mode = transaction.at(2);
1068 if (!name.isEmpty()) {
1069 name += ',';
1070 }
1071 name += transaction.at(1);
1072
1073 if (!transactionMode.isEmpty() && mode != transactionMode) {
1074 err = SKGError(ERR_INVALIDARG, QStringLiteral("Undo and Redo transactions cannot be grouped"));
1075 } else {
1076 transactionMode = mode;
1077 }
1078
1079 if (i == 1) {
1080 communParent = transaction.at(3);
1081 }
1082 }
1083
1084 // Group
1085 IFOK(err) {
1086 err = SKGDocument::beginTransaction(QStringLiteral("#INTERNAL#"));
1087 // Group items
1088 IFOKDO(err, executeSqliteOrder(
1089 QStringLiteral("UPDATE doctransactionitem set rd_doctransaction_id=") %
1090 smax %
1091 " where rd_doctransaction_id BETWEEN " %
1092 smin % " AND " % smax))
1093 IFOKDO(err, executeSqliteOrder(
1094 QStringLiteral("UPDATE doctransaction set i_parent=") %
1095 communParent %
1096 ", t_name='" % SKGServices::stringToSqlString(name) %
1097 "' where id=" % smax))
1098
1099 IFOKDO(err, executeSqliteOrder(
1100 QStringLiteral("DELETE FROM doctransaction WHERE id BETWEEN ") %
1101 smin % " AND " % SKGServices::intToString(qMax(iFrom, iTo) - 1)))
1102
1103 SKGENDTRANSACTION(this, err)
1104 }
1105 }
1106
1107 --d->m_inundoRedoTransaction;
1108 return err;
1109 }
1110
undoRedoTransaction(UndoRedoMode iMode)1111 SKGError SKGDocument::undoRedoTransaction(UndoRedoMode iMode)
1112 {
1113 SKGError err;
1114 SKGTRACEINFUNCRC(5, err)
1115 // Check if a transaction is still opened
1116 err = checkExistingTransaction();
1117 IFOK(err) err.setReturnCode(ERR_ABORT).setMessage(i18nc("Something went wrong with SQL transactions", "Undo / Redo is forbidden inside a transaction"));
1118 else {
1119 if (iMode == SKGDocument::UNDOLASTSAVE) {
1120 // Create group
1121 SKGStringListList transactions;
1122 err = executeSelectSqliteOrder(
1123 QStringLiteral("SELECT id, t_savestep FROM doctransaction WHERE t_mode='U' ORDER BY id DESC"),
1124 transactions);
1125 int nb = transactions.count();
1126 int min = 0;
1127 int max = 0;
1128 for (int i = 1; !err && i < nb; ++i) {
1129 const QStringList& transaction = transactions.at(i);
1130 if (i == 1) {
1131 max = SKGServices::stringToInt(transaction.at(0));
1132 }
1133 if (i != 1 && transaction.at(1) == QStringLiteral("Y")) {
1134 break;
1135 }
1136 min = SKGServices::stringToInt(transaction.at(0));
1137 }
1138 if (min == 0) {
1139 min = max;
1140 }
1141 if (!err && min != max && min != 0) {
1142 err = groupTransactions(min, max);
1143 }
1144 } else {
1145 err = SKGError(); // To ignore error generated by checkExistingTransaction.
1146 }
1147
1148 // Get ID of the transaction to undo
1149 IFOK(err) {
1150 QString name;
1151 bool saveStep = false;
1152 QDateTime date;
1153 bool refreshViews;
1154 int id = getTransactionToProcess(iMode, &name, &saveStep, &date, &refreshViews);
1155 if (id == 0) {
1156 // No transaction found ==> generate an error
1157 err = SKGError(ERR_INVALIDARG, QStringLiteral("No transaction found. Undo / Redo impossible."));
1158 } else {
1159 // Undo transaction
1160 SKGTRACEL(5) << "Undoing transaction [" << id << "]- [" << name << "]..." << SKGENDL;
1161 SKGStringListList listSqlOrder;
1162 err = executeSelectSqliteOrder(
1163 "SELECT t_sqlorder FROM doctransactionitem WHERE rd_doctransaction_id=" %
1164 SKGServices::intToString(id) %
1165 " ORDER BY id DESC",
1166 listSqlOrder);
1167 IFOK(err) {
1168 int nb = listSqlOrder.count();
1169 err = SKGDocument::beginTransaction(name, nb + 3, date, refreshViews);
1170 IFOK(err) {
1171 ++d->m_inundoRedoTransaction; // Because we will be in a undo/redo transaction
1172 // Normal the first element is ignored because it is the header
1173 for (int i = 1; !err && i < nb ; ++i) {
1174 err = executeSqliteOrder(listSqlOrder.at(i).at(0));
1175
1176 IFOKDO(err, stepForward(i))
1177 }
1178
1179 IFOK(err) {
1180 // Set the NEW transaction in redo mode
1181 int lastredo = getTransactionToProcess((iMode == SKGDocument::UNDO || iMode == SKGDocument::UNDOLASTSAVE ? SKGDocument::REDO : SKGDocument::UNDO));
1182 int newredo = getTransactionToProcess(iMode);
1183 IFOKDO(err, executeSqliteOrder(
1184 QStringLiteral("UPDATE doctransaction set t_mode=") %
1185 (iMode == SKGDocument::UNDO || iMode == SKGDocument::UNDOLASTSAVE ? QStringLiteral("'R'") : QStringLiteral("'U'")) %
1186 ", i_parent=" %
1187 SKGServices::intToString(lastredo) %
1188 " where id=" % SKGServices::intToString(newredo)))
1189 IFOKDO(err, stepForward(nb))
1190
1191 // Move messages from previous transaction to new one
1192 IFOKDO(err, executeSqliteOrder(
1193 "UPDATE doctransactionmsg set rd_doctransaction_id=" %
1194 SKGServices::intToString(getCurrentTransaction()) %
1195 " where rd_doctransaction_id=" %
1196 SKGServices::intToString(id)))
1197 IFOKDO(err, stepForward(nb + 1))
1198
1199 // delete treated transaction
1200 IFOKDO(err, executeSqliteOrder(
1201 "DELETE from doctransaction where id="
1202 % SKGServices::intToString(id)))
1203 IFOKDO(err, stepForward(nb + 2))
1204
1205 // Check that new transaction has exactly the same number of item
1206 /* IFOK (err) {
1207 SKGStringListList listSqlOrder;
1208 err=executeSelectSqliteOrder(
1209 "SELECT count(1) FROM doctransactionitem WHERE rd_doctransaction_id=" %
1210 SKGServices::intToString(getCurrentTransaction()),
1211 listSqlOrder);
1212 if (!err && SKGServices::stringToInt(listSqlOrder.at(1).at(0))!=nb-1) {
1213 err=SKGError(ERR_ABORT, i18nc("Error message", "Invalid number of item after undo/redo. Expected (%1) != Result (%2)",nb-1,listSqlOrder.at(1).at(0)));
1214 }
1215 }*/
1216
1217 IFOKDO(err, stepForward(nb + 3))
1218 }
1219
1220 SKGENDTRANSACTION(this, err)
1221 --d->m_inundoRedoTransaction; // We left the undo / redo transaction
1222 }
1223 }
1224 }
1225 }
1226 }
1227
1228 return err;
1229 }
1230
getDepthTransaction() const1231 int SKGDocument::getDepthTransaction() const
1232 {
1233 return d->m_nbStepForTransaction.size();
1234 }
1235
getNbTransaction(UndoRedoMode iMode) const1236 int SKGDocument::getNbTransaction(UndoRedoMode iMode) const
1237 {
1238 SKGTRACEINFUNC(10)
1239 int output = 0;
1240 if (Q_LIKELY(getMainDatabase())) {
1241 QString sqlorder = QStringLiteral("select count(1) from doctransaction where t_mode='");
1242 sqlorder += (iMode == SKGDocument::UNDO || iMode == SKGDocument::UNDOLASTSAVE ? QStringLiteral("U") : QStringLiteral("R"));
1243 sqlorder += '\'';
1244 QSqlQuery query = getMainDatabase()->exec(sqlorder);
1245 if (query.next()) {
1246 output = query.value(0).toInt();
1247 }
1248 }
1249 return output;
1250 }
1251
getTransactionToProcess(UndoRedoMode iMode,QString * oName,bool * oSaveStep,QDateTime * oDate,bool * oRefreshViews) const1252 int SKGDocument::getTransactionToProcess(UndoRedoMode iMode, QString* oName, bool* oSaveStep, QDateTime* oDate, bool* oRefreshViews) const
1253 {
1254 SKGTRACEINFUNC(10)
1255 // initialisation
1256 int output = 0;
1257 if (oName != nullptr) {
1258 *oName = QLatin1String("");
1259 }
1260 if (Q_LIKELY(getMainDatabase())) {
1261 QString sqlorder = QStringLiteral("select A.id , A.t_name, A.t_savestep, A.d_date, A.t_refreshviews from doctransaction A where "
1262 "NOT EXISTS(select 1 from doctransaction B where B.i_parent=A.id) "
1263 "and A.t_mode='");
1264 sqlorder += (iMode == SKGDocument::UNDO || iMode == SKGDocument::UNDOLASTSAVE ? QStringLiteral("U") : QStringLiteral("R"));
1265 sqlorder += '\'';
1266 QSqlQuery query = getMainDatabase()->exec(sqlorder);
1267 if (query.next()) {
1268 output = query.value(0).toInt();
1269 if (oName != nullptr) {
1270 *oName = query.value(1).toString();
1271 }
1272 if (oSaveStep != nullptr) {
1273 *oSaveStep = (query.value(2).toString() == QStringLiteral("Y"));
1274 }
1275 if (oDate != nullptr) {
1276 *oDate = SKGServices::stringToTime(query.value(3).toString());
1277 }
1278 if (oRefreshViews != nullptr) {
1279 *oRefreshViews = (query.value(4).toString() == QStringLiteral("Y"));
1280 }
1281 }
1282 }
1283 return output;
1284 }
1285
getCurrentTransaction() const1286 int SKGDocument::getCurrentTransaction() const
1287 {
1288 SKGTRACEINFUNC(10)
1289 return d->m_currentTransaction;
1290 }
1291
getPassword() const1292 QString SKGDocument::getPassword() const
1293 {
1294 if (!d->m_password_got) {
1295 d->m_password = getParameter(QStringLiteral("SKG_PASSWORD"));
1296 d->m_password_got = true;
1297 }
1298 return d->m_password;
1299 }
1300
changePassword(const QString & iNewPassword)1301 SKGError SKGDocument::changePassword(const QString& iNewPassword)
1302 {
1303 SKGError err;
1304 SKGTRACEINFUNCRC(10, err)
1305 IFOK(checkExistingTransaction()) err.setReturnCode(ERR_ABORT).setMessage(i18nc("Something went wrong with SQL transactions", "Change password is forbidden inside a transaction"));
1306 else {
1307 IFOKDO(err, executeSqliteOrder("PRAGMA REKEY = '" % SKGServices::stringToSqlString(iNewPassword.isEmpty() ? QStringLiteral("DEFAULTPASSWORD") : iNewPassword) % "'"))
1308 IFOKDO(err, beginTransaction(QStringLiteral("#INTERNAL#"), 0, QDateTime::currentDateTime(), false))
1309 IFOKDO(err, setParameter(QStringLiteral("SKG_PASSWORD"), iNewPassword))
1310 IFOKDO(err, setParameter(QStringLiteral("SKG_PASSWORD_LASTUPDATE"), SKGServices::dateToSqlString(QDate::currentDate())))
1311 IFOKDO(err, sendMessage(iNewPassword.isEmpty() ? i18nc("Inform the user that the password protection was removed", "The document password has been removed.") :
1312 i18nc("Inform the user that the password was successfully changed", "The document password has been modified."), SKGDocument::Positive))
1313 SKGENDTRANSACTION(this, err)
1314
1315 // Force the save
1316 IFOK(err) {
1317 d->m_lastSavedTransaction = -1;
1318
1319 d->m_password = iNewPassword;
1320 d->m_password_got = true;
1321
1322 // Close all thread connection
1323 auto conNameMainConnection = getMainDatabase()->connectionName();
1324 const auto conNames = QSqlDatabase::connectionNames();
1325 for (const auto& conName : conNames) {
1326 if (conName.startsWith(conNameMainConnection % "_")) {
1327 /* NO NEED
1328 {
1329 auto con = QSqlDatabase::database(conName, false);
1330 con.close();
1331 }*/
1332 QSqlDatabase::removeDatabase(conName);
1333 }
1334 }
1335 }
1336 }
1337 return err;
1338 }
1339
setLanguage(const QString & iLanguage)1340 SKGError SKGDocument::setLanguage(const QString& iLanguage)
1341 {
1342 SKGError err;
1343 SKGTRACEINFUNCRC(5, err)
1344 QString previousLanguage = getParameter(QStringLiteral("SKG_LANGUAGE"));
1345 if (previousLanguage != iLanguage) {
1346 // Save language into the document
1347 IFOKDO(err, beginTransaction(QStringLiteral("#INTERNAL#"), 0, QDateTime::currentDateTime(), false))
1348 IFOKDO(err, setParameter(QStringLiteral("SKG_LANGUAGE"), iLanguage))
1349
1350 // Migrate view for new language
1351 IFOKDO(err, refreshViewsIndexesAndTriggers())
1352
1353 // close temporary transaction
1354 SKGENDTRANSACTION(this, err)
1355 }
1356 return err;
1357 }
1358
initialize()1359 SKGError SKGDocument::initialize()
1360 {
1361 SKGError err;
1362 SKGTRACEINFUNCRC(5, err)
1363 err = load(QLatin1String(""), QLatin1String(""));
1364 return err;
1365 }
1366
recover(const QString & iName,const QString & iPassword,QString & oRecoveredFile)1367 SKGError SKGDocument::recover(const QString& iName, const QString& iPassword, QString& oRecoveredFile)
1368 {
1369 SKGError err;
1370 SKGTRACEINFUNCRC(5, err)
1371 SKGTRACEL(10) << "Input parameter [name]=[" << iName << ']' << SKGENDL;
1372
1373 QString sqliteFile = QString(iName % "_recovered.sqlite").replace(QStringLiteral(".skg_"), QStringLiteral("_"));
1374 oRecoveredFile = QString(iName % "_recovered.skg").replace(QStringLiteral(".skg_"), QStringLiteral("_"));
1375 bool mode;
1376 err = SKGServices::cryptFile(iName, sqliteFile, iPassword, false, getDocumentHeader(), mode);
1377 IFOK(err) {
1378 QFile(oRecoveredFile).remove();
1379 QString cmd = "echo .dump | sqlcipher \"" % sqliteFile % "\" | sed -e 's/ROLLBACK; -- due to errors/COMMIT;/g' | sqlcipher \"" % oRecoveredFile % '"';
1380 QProcess p;
1381 p.start(QStringLiteral("sh"), QStringList() << QStringLiteral("-c") << cmd);
1382 if (!p.waitForFinished(1000 * 60 * 2) || p.exitCode() != 0) {
1383 err.setReturnCode(ERR_FAIL).setMessage(i18nc("Error message", "The following command line failed with code %2:\n'%1'", cmd, p.exitCode()));
1384 }
1385
1386 // Try to load the recovered file
1387 IFOKDO(err, load(oRecoveredFile, QLatin1String("")))
1388 IFOK(err) {
1389 SKGBEGINTRANSACTION(*this, i18nc("Noun", "Recovery"), err)
1390 IFOKDO(err, refreshViewsIndexesAndTriggers(true))
1391 }
1392 IFOKDO(err, save())
1393
1394 // Reset the current document
1395 initialize();
1396
1397 // Clean useless file
1398 IFOK(err) {
1399 // We keep only the recovered
1400 QFile(sqliteFile).remove();
1401 } else {
1402 // We keep the sqlite file in case of
1403 QFile(oRecoveredFile).remove();
1404 err.addError(ERR_FAIL, i18nc("Error message", "Impossible to recover this file"));
1405 }
1406 }
1407
1408 return err;
1409 }
1410
load(const QString & iName,const QString & iPassword,bool iRestoreTmpFile,bool iForceReadOnly)1411 SKGError SKGDocument::load(const QString& iName, const QString& iPassword, bool iRestoreTmpFile, bool iForceReadOnly)
1412 {
1413 // Close previous document
1414 SKGError err;
1415 SKGTRACEINFUNCRC(5, err)
1416 SKGTRACEL(10) << "Input parameter [name]=[" << iName << ']' << SKGENDL;
1417 SKGTRACEL(10) << "Input parameter [iRestoreTmpFile]=[" << (iRestoreTmpFile ? "TRUE" : "FALSE") << ']' << SKGENDL;
1418 SKGTRACEL(10) << "Input parameter [iForceReadOnly]=[" << (iForceReadOnly ? "TRUE" : "FALSE") << ']' << SKGENDL;
1419
1420 d->m_lastSavedTransaction = -1; // To avoid double event emission
1421 d->m_modeSQLCipher = true;
1422 d->m_blockEmits = true;
1423 err = close();
1424 d->m_blockEmits = false;
1425 IFOK(err) {
1426 if (!iName.isEmpty()) {
1427 // File exist
1428 QFileInfo fi(iName);
1429 d->m_modeReadOnly = iForceReadOnly || !fi.permission(QFile::WriteUser);
1430
1431 // Temporary file
1432 d->m_temporaryFile = SKGDocument::getTemporaryFile(iName, d->m_modeReadOnly);
1433 bool temporaryFileExisting = QFile(d->m_temporaryFile).exists();
1434 SKGTRACEL(10) << "Temporary file: [" << d->m_temporaryFile << ']' << SKGENDL;
1435 SKGTRACEL(10) << "Temporary file existing: [" << (temporaryFileExisting ? "TRUE" : "FALSE") << ']' << SKGENDL;
1436 if (!iRestoreTmpFile || !temporaryFileExisting) {
1437 SKGTRACEL(10) << "Create the temporary file" << SKGENDL;
1438 QFile::remove(d->m_temporaryFile); // Must remove it to be able to copy
1439 err = SKGServices::cryptFile(iName, d->m_temporaryFile, iPassword, false, getDocumentHeader(), d->m_modeSQLCipher);
1440 } else {
1441 SKGTRACEL(10) << "The temporary file is existing, try a restore but we must check if the file is password protected first" << SKGENDL;
1442 // 249955: Check if password protected vvv
1443 // Temporary file will be loaded but first we must check if original document is password protected
1444 QString temporaryFile2 = d->m_temporaryFile % '2';
1445 err = SKGServices::cryptFile(iName, temporaryFile2, iPassword, false, getDocumentHeader(), d->m_modeSQLCipher);
1446
1447 // Try an open to check if well descrypted
1448 IFOK(err) {
1449 QSqlDatabase tryOpen(QSqlDatabase::addDatabase(SQLDRIVERNAME, QStringLiteral("tryOpen")));
1450 tryOpen.setDatabaseName(temporaryFile2);
1451 if (!tryOpen.open()) {
1452 // Set error message
1453 QSqlError sqlErr = tryOpen.lastError();
1454 err = SKGError(SQLLITEERROR + sqlErr.nativeErrorCode().toInt(), sqlErr.text());
1455 }
1456 if (d->m_modeSQLCipher) {
1457 IFOKDO(err, SKGServices::executeSqliteOrder(tryOpen, "PRAGMA KEY = '" % SKGServices::stringToSqlString(iPassword.isEmpty() ? QStringLiteral("DEFAULTPASSWORD") : iPassword) % "'"))
1458 IFKO(err) {
1459 SKGTRACEL(10) << "Wrong installation of sqlcipher (doesn't support encryption)" << SKGENDL;
1460 err = SKGError(ERR_ENCRYPTION, i18nc("Error message", "Wrong installation"));
1461 }
1462
1463 // Migrate to the last version of SQLCipher
1464 IFOKDO(err, SKGServices::executeSqliteOrder(tryOpen, QStringLiteral("PRAGMA cipher_migrate")))
1465
1466 // Test the password
1467 IFOKDO(err, SKGServices::executeSqliteOrder(tryOpen, QStringLiteral("SELECT count(*) FROM sqlite_master")))
1468 IFKO(err) {
1469 SKGTRACEL(10) << "Wrong password in restore mode" << SKGENDL;
1470 err = SKGError(ERR_ENCRYPTION, i18nc("Error message", "Wrong password"));
1471 }
1472 }
1473 IFOKDO(err, SKGServices::executeSqliteOrder(tryOpen, QStringLiteral("PRAGMA synchronous = OFF")))
1474 }
1475 QSqlDatabase::removeDatabase(QStringLiteral("tryOpen"));
1476 QFile::remove(temporaryFile2);
1477
1478 // To avoid deletion of temporary file during next try
1479 IFKO(err) d->m_temporaryFile = QLatin1String("");
1480 // 249955: Check if password protected ^^^
1481 }
1482
1483 // Create file database
1484 IFOK(err) {
1485 d->m_currentDatabase = QSqlDatabase::addDatabase(SQLDRIVERNAME, d->m_databaseIdentifier);
1486 d->m_currentDatabase.setDatabaseName(d->m_temporaryFile);
1487 if (!d->m_currentDatabase.open()) {
1488 // Set error message
1489 QSqlError sqlErr = d->m_currentDatabase.lastError();
1490 err = SKGError(SQLLITEERROR + sqlErr.nativeErrorCode().toInt(), sqlErr.text());
1491 }
1492
1493 d->m_directAccessDb = true;
1494 if (QUrl::fromUserInput(iName).isLocalFile()) {
1495 d->m_currentFileName = iName;
1496 }
1497 }
1498 } else {
1499 // Temporary file
1500 d->m_temporaryFile = QDir::tempPath() % "/skg_" % QUuid::createUuid().toString() % ".skg";
1501
1502 // Create memory database
1503 d->m_currentDatabase = QSqlDatabase::addDatabase(SQLDRIVERNAME, d->m_databaseIdentifier);
1504 d->m_currentDatabase.setConnectOptions(QStringLiteral("QSQLITE_OPEN_URI"));
1505 d->m_currentDatabase.setDatabaseName(QStringLiteral("file:") + d->m_databaseIdentifier + QStringLiteral("?mode=memory&cache=shared"));
1506 if (!d->m_currentDatabase.open()) {
1507 // Set error message
1508 QSqlError sqlErr = d->m_currentDatabase.lastError();
1509 err = SKGError(SQLLITEERROR + sqlErr.nativeErrorCode().toInt(), sqlErr.text());
1510 }
1511
1512 d->m_directAccessDb = false;
1513 }
1514 if (d->m_modeSQLCipher) {
1515 // This is an encrypted data base
1516 IFOKDO(err, executeSqliteOrder("PRAGMA KEY = '" % SKGServices::stringToSqlString(iPassword.isEmpty() ? QStringLiteral("DEFAULTPASSWORD") : iPassword) % "'"))
1517 IFKO(err) {
1518 SKGTRACEL(10) << "Wrong installation of sqlcipher (doesn't support encryption)" << SKGENDL;
1519 err = SKGError(ERR_ENCRYPTION, i18nc("Error message", "Wrong installation"));
1520 }
1521
1522 // Migrate to the last version of SQLCipher
1523 IFOKDO(err, executeSqliteOrder(QStringLiteral("PRAGMA cipher_migrate")))
1524
1525 // Test the password
1526 IFOKDO(err, executeSqliteOrder(QStringLiteral("SELECT count(*) FROM sqlite_master")))
1527 IFKO(err) {
1528 SKGTRACEL(10) << "Wrong password on temporary file" << SKGENDL;
1529 err = SKGError(ERR_ENCRYPTION, i18nc("Error message", "Wrong password"));
1530 }
1531 }
1532
1533 // Check if the database is correct
1534 IFOK(err) {
1535 IFOKDO(err, executeSqliteOrder(QStringLiteral("PRAGMA journal_mode=MEMORY")))
1536 IFKO(err) {
1537 err.addError(ERR_CORRUPTION, i18nc("Error message", "Oups, this file seems to be corrupted"));
1538 }
1539 }
1540
1541 // Optimization
1542 QStringList optimization;
1543 optimization << QStringLiteral("PRAGMA case_sensitive_like=true")
1544 << QStringLiteral("PRAGMA journal_mode=MEMORY")
1545 << QStringLiteral("PRAGMA temp_store=MEMORY")
1546 // << QStringLiteral("PRAGMA locking_mode=EXCLUSIVE")
1547 << QStringLiteral("PRAGMA synchronous = OFF")
1548 << QStringLiteral("PRAGMA legacy_alter_table=ON") // For migration on sqlite >=3.25 (see https://sqlite.org/lang_altertable.html)
1549 << QStringLiteral("PRAGMA recursive_triggers=true");
1550 IFOKDO(err, executeSqliteOrders(optimization))
1551
1552 // Add custom sqlite functions
1553 IFOKDO(err, addSqliteAddon(getMainDatabase()))
1554
1555 if (!d->m_directAccessDb) {
1556 // Create parameter and undo redo table
1557 /**
1558 * This constant is used to initialized the data model (table creation)
1559 */
1560 QStringList InitialDataModel;
1561
1562 // ==================================================================
1563 // Table parameters
1564 InitialDataModel << QStringLiteral("CREATE TABLE parameters "
1565 "(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
1566 "t_uuid_parent TEXT NOT NULL DEFAULT '',"
1567 "t_name TEXT NOT NULL,"
1568 "t_value TEXT NOT NULL DEFAULT '',"
1569 "b_blob BLOB,"
1570 "d_lastmodifdate DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,"
1571 "i_tmp INTEGER NOT NULL DEFAULT 0"
1572 ")")
1573
1574 // ==================================================================
1575 // Table node
1576 << "CREATE TABLE node ("
1577 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
1578 "t_name TEXT NOT NULL DEFAULT '' CHECK (t_name NOT LIKE '%" % OBJECTSEPARATOR % "%'),"
1579 "t_fullname TEXT,"
1580 "t_icon TEXT DEFAULT '',"
1581 "f_sortorder FLOAT,"
1582 "t_autostart VARCHAR(1) DEFAULT 'N' CHECK (t_autostart IN ('Y', 'N')),"
1583 "t_data TEXT,"
1584 "rd_node_id INT CONSTRAINT fk_id REFERENCES node(id) ON DELETE CASCADE)"
1585
1586 // ==================================================================
1587 // Table doctransaction
1588 << QStringLiteral("CREATE TABLE doctransaction ("
1589 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
1590 "t_name TEXT NOT NULL,"
1591 "t_mode VARCHAR(1) DEFAULT 'U' CHECK (t_mode IN ('U', 'R')),"
1592 "d_date DATE NOT NULL,"
1593 "t_savestep VARCHAR(1) DEFAULT 'N' CHECK (t_savestep IN ('Y', 'N')),"
1594 "t_refreshviews VARCHAR(1) DEFAULT 'Y' CHECK (t_refreshviews IN ('Y', 'N')),"
1595 "i_parent INTEGER)")
1596
1597 // ==================================================================
1598 // Table doctransactionitem
1599 << QStringLiteral("CREATE TABLE doctransactionitem ("
1600 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
1601 "rd_doctransaction_id INTEGER NOT NULL,"
1602 "i_object_id INTEGER NOT NULL,"
1603 "t_object_table TEXT NOT NULL,"
1604 "t_action VARCHAR(1) DEFAULT 'I' CHECK (t_action IN ('I', 'U', 'D')),"
1605 "t_sqlorder TEXT NOT NULL DEFAULT '')")
1606
1607 << QStringLiteral("CREATE TABLE doctransactionmsg ("
1608 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
1609 "rd_doctransaction_id INTEGER NOT NULL,"
1610 "t_message TEXT NOT NULL DEFAULT '',"
1611 "t_type VARCHAR(1) DEFAULT 'I' CHECK (t_type IN ('P', 'I', 'W', 'E', 'H')))"); // Positive, Information, Warning, Error, Hidden
1612
1613 IFOKDO(err, executeSqliteOrders(InitialDataModel))
1614 IFOKDO(err, SKGDocument::refreshViewsIndexesAndTriggers())
1615 }
1616 }
1617
1618 // migrate
1619 IFOK(err) {
1620 bool mig = false;
1621 err = migrate(mig);
1622
1623 if (!err && getParameter(QStringLiteral("SKG_DATABASE_TYPE")) != SQLDRIVERNAME && !getPassword().isEmpty()) {
1624 err = sendMessage(i18nc("Information message", "This document is protected by a password but the database is still in SQLite mode.\nDo you know that the SQLCipher mode is more secured because even the temporary file is encrypted?"), SKGDocument::Warning, QStringLiteral("skg://migrate_sqlcipher"));
1625 }
1626 if (!err && getParameter(QStringLiteral("SKG_PASSWORD_LASTUPDATE")) == QLatin1String("") && !getPassword().isEmpty()) {
1627 err = sendMessage(i18nc("Information message", "A security hole has been detected and corrected on this version of the application. We strongly encourage you to change your password."), SKGDocument::Warning, QStringLiteral("skg://file_change_password"));
1628 }
1629
1630 // To authorize manual repair of document in case of error during migration
1631 // the error is not caught if traces are activated
1632 if (err && (SKGTraces::SKGLevelTrace != 0)) {
1633 err = sendMessage(i18nc("Popup message", "The migration failed but the document has been loaded without error because debug mode is activated"), SKGDocument::Warning);
1634 }
1635
1636 if (!err && mig && !iName.isEmpty()) {
1637 err = sendMessage(i18nc("The document has been upgraded to the latest Skrooge version format", "The document has been migrated"), SKGDocument::Positive);
1638 }
1639 }
1640
1641 // Optimization
1642 IFOK(err) {
1643 d->m_lastSavedTransaction = getTransactionToProcess(SKGDocument::UNDO);
1644 executeSqliteOrder(QStringLiteral("ANALYZE"));
1645 }
1646
1647 // Creation undo/redo triggers
1648 IFOKDO(err, createUndoRedoTemporaryTriggers())
1649
1650 IFOK(err) {
1651 QString sqliteQtVersion = getParameter(QStringLiteral("SKG_SQLITE_LAST_VERSION"));
1652 QString sqliteSystemVersion(sqlite3_libversion());
1653 QProcess sqlite3Process;
1654 QString mode;
1655 sqlite3Process.start(QStringLiteral("sqlcipher"), QStringList() << QStringLiteral("-version"));
1656 mode = QStringLiteral("SQLCipher");
1657 if (sqlite3Process.waitForFinished()) {
1658 sqliteSystemVersion = SKGServices::splitCSVLine(sqlite3Process.readAll(), ' ').value(0);
1659 }
1660 SKGTRACEL(5) << "SQLite version of Qt :" << sqliteQtVersion << SKGENDL;
1661 SKGTRACEL(5) << "SQLite version of the system:" << sqliteSystemVersion << SKGENDL;
1662 if (!sqliteQtVersion.isEmpty() && !sqliteSystemVersion.isEmpty() && sqliteQtVersion != sqliteSystemVersion) {
1663 QString message = i18nc("Error message", "This application can not run correctly because the %3 version of the system (%1) is not aligned with the %4 version embedded in Qt (%2). You should rebuild Qt with the option -system-sqlite.", sqliteSystemVersion, sqliteQtVersion, mode, mode);
1664 err = sendMessage(message, Warning);
1665 SKGTRACE << "WARNING:" << message << SKGENDL;
1666 }
1667 }
1668
1669 if (err && !iName.isEmpty()) {
1670 close();
1671 } else {
1672 // Send event
1673 d->m_uniqueIdentifier = QUuid::createUuid().toString();
1674 d->m_password = iPassword;
1675 d->m_password_got = true;
1676 Q_EMIT tableModified(QLatin1String(""), 0, false);
1677 Q_EMIT modified();
1678 }
1679
1680 return err;
1681 }
1682
isReadOnly() const1683 bool SKGDocument::isReadOnly() const
1684 {
1685 return d->m_modeReadOnly;
1686 }
1687
isFileModified() const1688 bool SKGDocument::isFileModified() const
1689 {
1690 // Get last executed transaction
1691 int last = getTransactionToProcess(SKGDocument::UNDO);
1692 // if (nbStepForTransaction.size()) --last;
1693 return (d->m_lastSavedTransaction != last);
1694 }
1695
setFileNotModified() const1696 void SKGDocument::setFileNotModified() const
1697 {
1698 d->m_lastSavedTransaction = getTransactionToProcess(SKGDocument::UNDO);
1699 }
1700
getCurrentFileName() const1701 QString SKGDocument::getCurrentFileName() const
1702 {
1703 return d->m_currentFileName;
1704 }
1705
save()1706 SKGError SKGDocument::save()
1707 {
1708 SKGError err;
1709 SKGTRACEINFUNCRC(5, err)
1710 if (d->m_currentFileName.isEmpty()) {
1711 err = SKGError(ERR_INVALIDARG, i18nc("Error message: Can not save a file if it has no name yet", "Save not authorized because the file name is not yet defined"));
1712 } else {
1713 // save
1714 err = saveAs(d->m_currentFileName, true);
1715 }
1716 return err;
1717 }
1718
saveAs(const QString & iName,bool iOverwrite)1719 SKGError SKGDocument::saveAs(const QString& iName, bool iOverwrite)
1720 {
1721 SKGError err;
1722 SKGTRACEINFUNCRC(5, err)
1723 SKGTRACEL(10) << "Input parameter [name]=[" << iName << ']' << SKGENDL;
1724
1725 bool simulateFileSystemFull = !SKGServices::getEnvVariable(QStringLiteral("SKGFILESYSTEMFULL")).isEmpty();
1726
1727 // Check if a transaction is still opened
1728 err = checkExistingTransaction();
1729 IFOK(err) err.setReturnCode(ERR_ABORT).setMessage(i18nc("Cannot save the file while the application is still performing an SQL transaction", "Save is forbidden if a transaction is still opened"));
1730 else {
1731 err = SKGError();
1732 if (getParameter(QStringLiteral("SKG_UNDO_CLEAN_AFTER_SAVE")) == QStringLiteral("Y")) {
1733 err = executeSqliteOrder(QStringLiteral("delete from doctransaction"));
1734 }
1735
1736 // No transaction opened ==> it is ok
1737 // We mark the last transaction as a save point
1738 IFOKDO(err, executeSqliteOrder(QStringLiteral("update doctransaction set t_savestep='Y' where id in (select A.id from doctransaction A where "
1739 "NOT EXISTS(select 1 from doctransaction B where B.i_parent=A.id) "
1740 "and A.t_mode='U')")))
1741 Q_EMIT tableModified(QStringLiteral("doctransaction"), 0, false);
1742
1743 // Optimization
1744 IFOK(err) {
1745 err = executeSqliteOrder(QStringLiteral("VACUUM;"));
1746 IFOK(err) {
1747 // Check if file already exist
1748 if (!iOverwrite && QFile(iName).exists()) {
1749 err.setReturnCode(ERR_INVALIDARG).setMessage(i18nc("There is already a file with the same name", "File '%1' already exist", iName));
1750 } else {
1751 // Get backup file name
1752 bool backupFileMustBeRemoved = false;
1753 QString backupFileName = getBackupFile(iName);
1754 if (backupFileName.isEmpty()) {
1755 backupFileName = iName % ".tmp";
1756 backupFileMustBeRemoved = true;
1757 }
1758
1759 // Create backup file
1760 QFile::remove(backupFileName % '~');
1761 QFile::rename(backupFileName, backupFileName % '~');
1762 if (QFile(iName).exists() && (simulateFileSystemFull || !QFile(iName).copy(backupFileName))) {
1763 this->sendMessage(i18nc("Error message: Could not create a backup file", "Creation of backup file %1 failed", backupFileName), Warning);
1764 }
1765
1766 // Save database
1767 IFOK(err) {
1768 QFile::remove(iName % '~');
1769 QFile::rename(iName, iName % '~');
1770
1771 // To be sure that db is flushed
1772 IFOKDO(err, executeSqliteOrder(QStringLiteral("PRAGMA synchronous = FULL")))
1773 QString pwd = getPassword();
1774
1775 // Copy memory to tmp db
1776 if (!d->m_directAccessDb && !err) {
1777 QFile::remove(d->m_temporaryFile);
1778 auto fileDb = QSqlDatabase::addDatabase(SQLDRIVERNAME, d->m_databaseIdentifier % "_tmp");
1779 fileDb.setDatabaseName(d->m_temporaryFile);
1780 if (!fileDb.open()) {
1781 // Set error message
1782 QSqlError sqlErr = fileDb.lastError();
1783 err = SKGError(SQLLITEERROR + sqlErr.nativeErrorCode().toInt(), sqlErr.text());
1784 } else {
1785 IFOKDO(err, SKGServices::executeSqliteOrder(fileDb, "PRAGMA KEY = '" % SKGServices::stringToSqlString(pwd.isEmpty() ? QStringLiteral("DEFAULTPASSWORD") : pwd) % "'"))
1786 addSqliteAddon(&fileDb);
1787 IFOKDO(err, SKGServices::copySqliteDatabase(fileDb, d->m_currentDatabase, false, pwd.isEmpty() ? QStringLiteral("DEFAULTPASSWORD") : pwd))
1788 }
1789
1790 fileDb.close();
1791 QSqlDatabase::removeDatabase(d->m_databaseIdentifier % "_tmp");
1792 }
1793
1794 // To simulate a file system full
1795 if (!err && simulateFileSystemFull) {
1796 err = SKGError(ERR_WRITEACCESS, i18nc("Error message: writing a file failed", "Write file '%1' failed", iName));
1797 }
1798
1799 // Crypt the file
1800 if (!err) {
1801 bool mode;
1802 err = SKGServices::cryptFile(d->m_temporaryFile, iName, pwd, true, getDocumentHeader(), mode);
1803 }
1804 if (!d->m_directAccessDb && !err) {
1805 QFile(d->m_temporaryFile).remove();
1806 }
1807
1808 // For performances
1809 IFOKDO(err, executeSqliteOrder(QStringLiteral("PRAGMA synchronous = OFF")))
1810 }
1811
1812 if (backupFileMustBeRemoved) {
1813 QFile::remove(backupFileName);
1814 }
1815
1816 IFOK(err) {
1817 // The document is not modified
1818 QString oldtemporaryFile = d->m_temporaryFile;
1819 d->m_currentFileName = iName;
1820 d->m_modeReadOnly = false;
1821 d->m_temporaryFile = getTemporaryFile(d->m_currentFileName);
1822 if (oldtemporaryFile != d->m_temporaryFile) {
1823 QFile(oldtemporaryFile).rename(d->m_temporaryFile);
1824 }
1825 d->m_lastSavedTransaction = getTransactionToProcess(SKGDocument::UNDO);
1826
1827 // Commit save
1828 QFile::remove(backupFileName % '~');
1829 QFile::remove(iName % '~');
1830 } else {
1831 // Rollback file
1832 QFile::remove(backupFileName);
1833 QFile::rename(backupFileName % '~', backupFileName);
1834
1835 QFile::remove(iName);
1836 QFile::rename(iName % '~', iName);
1837 }
1838 }
1839 }
1840 }
1841
1842 Q_EMIT transactionSuccessfullyEnded(0);
1843 }
1844 return err;
1845 }
1846
close()1847 SKGError SKGDocument::close()
1848 {
1849 SKGTRACEINFUNC(5)
1850 if (getMainDatabase() != nullptr) {
1851 QString conNameMainConnection = getMainDatabase()->connectionName();
1852 const auto& conNames = QSqlDatabase::connectionNames();
1853 for (const auto& conName : conNames) {
1854 if (conName.startsWith(conNameMainConnection % "_")) {
1855 /* NO NEED
1856 {
1857 auto con = QSqlDatabase::database(conName, false);
1858 con.close();
1859 }*/
1860 QSqlDatabase::removeDatabase(conName);
1861 }
1862 }
1863 getMainDatabase()->close();
1864 d->m_currentDatabase = QSqlDatabase(); // To avoid warning on remove
1865 QSqlDatabase::removeDatabase(d->m_databaseIdentifier);
1866 }
1867
1868 if (!d->m_temporaryFile.isEmpty()) {
1869 QFile(d->m_temporaryFile).remove();
1870 d->m_temporaryFile = QLatin1String("");
1871 }
1872
1873 // Emit events ?
1874 bool emitEvent = (d->m_lastSavedTransaction != -1);
1875
1876 // Init fields
1877 d->m_currentFileName = QLatin1String("");
1878 d->m_lastSavedTransaction = 0;
1879 d->m_nbStepForTransaction.clear();
1880 d->m_posStepForTransaction.clear();
1881 d->m_nameForTransaction.clear();
1882 d->m_password.clear();
1883 d->m_password_got = false;
1884
1885 // Send event
1886 if (!d->m_blockEmits && emitEvent && qApp && !qApp->closingDown()) {
1887 Q_EMIT tableModified(QLatin1String(""), 0, false);
1888 Q_EMIT transactionSuccessfullyEnded(0);
1889 Q_EMIT modified();
1890 }
1891
1892 return SKGError();
1893 }
1894
dropViewsAndIndexes(const QStringList & iTables) const1895 SKGError SKGDocument::dropViewsAndIndexes(const QStringList& iTables) const
1896 {
1897 SKGError err;
1898 // Drop all views
1899 SKGStringListList list;
1900 err = executeSelectSqliteOrder(QStringLiteral("SELECT tbl_name, name, type FROM sqlite_master WHERE type IN ('view','index')"), list);
1901 int nb = list.count();
1902 for (int i = 1; !err && i < nb; ++i) {
1903 QString name = list.at(i).at(1);
1904 QString table = SKGServices::getRealTable(list.at(i).at(0));
1905 QString type = list.at(i).at(2);
1906 if (iTables.contains(table)) {
1907 QString sql = "DROP " % type % " IF EXISTS " % name;
1908 err = this->executeSqliteOrder(sql);
1909 }
1910 }
1911 return err;
1912 }
1913
1914 #include "skgdocument2.cpp"
1915
migrate(bool & oMigrationDone)1916 SKGError SKGDocument::migrate(bool& oMigrationDone)
1917 {
1918 SKGError err;
1919 SKGTRACEINFUNCRC(5, err)
1920 oMigrationDone = false;
1921
1922 {
1923 SKGBEGINPROGRESSTRANSACTION(*this, "#INTERNAL#" % i18nc("Progression step", "Migrate document"), err, 3)
1924 if (getParameter(QStringLiteral("SKG_UNDO_MAX_DEPTH")).isEmpty()) {
1925 IFOKDO(err, setParameter(QStringLiteral("SKG_UNDO_MAX_DEPTH"), SKGServices::intToString(SKG_UNDO_MAX_DEPTH)))
1926 }
1927
1928 if (getParameter(QStringLiteral("SKG_UNDO_CLEAN_AFTER_SAVE")).isEmpty()) {
1929 IFOKDO(err, setParameter(QStringLiteral("SKG_UNDO_CLEAN_AFTER_SAVE"), QStringLiteral("N")))
1930 }
1931
1932 if (!err && getParameter(QStringLiteral("SKG_DATABASE_TYPE")) != (d->m_modeSQLCipher ? SQLDRIVERNAME : QStringLiteral("QSQLITE"))) {
1933 IFOKDO(err, setParameter(QStringLiteral("SKG_DATABASE_TYPE"), d->m_modeSQLCipher ? SQLDRIVERNAME : QStringLiteral("QSQLITE")))
1934 }
1935
1936 QString version = getParameter(QStringLiteral("SKG_DB_VERSION"));
1937 QString initialversion = version;
1938 QString lastversion = QStringLiteral("1.6");
1939
1940 if (!err && version.isEmpty()) {
1941 // First creation
1942 SKGTRACEL(10) << "Migration from 0 to " << lastversion << SKGENDL;
1943
1944 // Set new version
1945 version = lastversion;
1946 IFOKDO(err, setParameter(QStringLiteral("SKG_DB_VERSION"), version))
1947
1948 // Set sqlite creation version
1949 SKGStringListList listTmp;
1950 IFOKDO(err, executeSelectSqliteOrder(QStringLiteral("select sqlite_version()"), listTmp))
1951 if (!err && listTmp.count() == 2) {
1952 err = setParameter(QStringLiteral("SKG_SQLITE_CREATION_VERSION"), listTmp.at(1).at(0));
1953 }
1954 oMigrationDone = true;
1955 }
1956
1957 if (!err && SKGServices::stringToDouble(version) > SKGServices::stringToDouble(lastversion)) {
1958 err = SKGError(ERR_ABORT, i18nc("Error message", "Impossible to load a document generated by a more recent version"));
1959 }
1960
1961 {
1962 // Migration steps
1963 if (!err && version == QStringLiteral("0.1")) {
1964 // Migration from version 0.1 to 0.2
1965 SKGTRACEL(10) << "Migration from 0.1 to 0.2" << SKGENDL;
1966
1967 // ==================================================================
1968 // Table doctransactionmsg
1969 QStringList sqlOrders;
1970 sqlOrders << QStringLiteral("DROP TABLE IF EXISTS doctransactionmsg")
1971 << QStringLiteral("CREATE TABLE doctransactionmsg ("
1972 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
1973 "rd_doctransaction_id INTEGER NOT NULL,"
1974 "t_message TEXT NOT NULL DEFAULT '')");
1975 err = executeSqliteOrders(sqlOrders);
1976
1977 // Set new version
1978 version = QStringLiteral("0.2");
1979 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
1980 oMigrationDone = true;
1981 }
1982 if (!err && version == QStringLiteral("0.2")) {
1983 // Migration from version 0.2 to 0.3
1984 SKGTRACEL(10) << "Migration from 0.2 to 0.3" << SKGENDL;
1985
1986 err = executeSqliteOrder(QStringLiteral("UPDATE node set f_sortorder=id"));
1987
1988 // Set new version
1989 version = QStringLiteral("0.3");
1990 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
1991 oMigrationDone = true;
1992 }
1993 if (!err && version == QStringLiteral("0.3")) {
1994 // Migration from version 0.3 to 0.4
1995 SKGTRACEL(10) << "Migration from 0.3 to 0.4" << SKGENDL;
1996
1997 err = executeSqliteOrder(QStringLiteral("ALTER TABLE node ADD COLUMN t_autostart VARCHAR(1) DEFAULT 'N' CHECK (t_autostart IN ('Y', 'N'))"));
1998 IFOKDO(err, executeSqliteOrder("UPDATE node set t_autostart='Y' where t_name='" % i18nc("Verb, automatically load when the application is started", "autostart") % '\''))
1999
2000 // Set new version
2001 version = QStringLiteral("0.4");
2002 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2003 oMigrationDone = true;
2004 }
2005 if (!err && version == QStringLiteral("0.4")) {
2006 // Migration from version 0.4 to 0.5
2007 SKGTRACEL(10) << "Migration from 0.4 to 0.5" << SKGENDL;
2008
2009 err = executeSqliteOrder(QStringLiteral("ALTER TABLE doctransactionmsg ADD COLUMN t_popup VARCHAR(1) DEFAULT 'Y' CHECK (t_popup IN ('Y', 'N'))"));
2010
2011 // Set new version
2012 version = QStringLiteral("0.5");
2013 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2014 oMigrationDone = true;
2015 }
2016 if (!err && version == QStringLiteral("0.5")) {
2017 // Migration from version 0.5 to 0.6
2018 SKGTRACEL(10) << "Migration from 0.5 to 0.6" << SKGENDL;
2019
2020 err = executeSqliteOrder(QStringLiteral("UPDATE node set t_autostart='N' where t_autostart NOT IN ('Y', 'N')"));
2021
2022 // Set new version
2023 version = QStringLiteral("0.6");
2024 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2025 oMigrationDone = true;
2026 }
2027 if (!err && version == QStringLiteral("0.6")) {
2028 // Migration from version 0.6 to 0.7
2029 SKGTRACEL(10) << "Migration from 0.6 to 0.7" << SKGENDL;
2030
2031 err = executeSqliteOrder(QStringLiteral("ALTER TABLE parameters ADD COLUMN b_blob BLOB"));
2032
2033 // Set new version
2034 version = QStringLiteral("0.7");
2035 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2036 oMigrationDone = true;
2037 }
2038 if (!err && version == QStringLiteral("0.7")) {
2039 // Migration from version 0.7 to 0.8
2040 SKGTRACEL(10) << "Migration from 0.7 to 0.8" << SKGENDL;
2041
2042 err = executeSqliteOrder(QStringLiteral("UPDATE parameters set t_name='SKG_LANGUAGE' where t_name='SKGLANGUAGE'"));
2043
2044 // Set new version
2045 version = QStringLiteral("0.8");
2046 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2047 oMigrationDone = true;
2048 }
2049 if (!err && version == QStringLiteral("0.8")) {
2050 SKGTRACEL(10) << "Migration from 0.8 to 0.9" << SKGENDL;
2051
2052 QStringList sql;
2053 sql << QStringLiteral("ALTER TABLE parameters ADD COLUMN i_tmp INTEGER NOT NULL DEFAULT 0")
2054 << QStringLiteral("UPDATE parameters set i_tmp=0");
2055
2056 err = executeSqliteOrders(sql);
2057
2058 // Set new version
2059 version = QStringLiteral("0.9");
2060 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2061 oMigrationDone = true;
2062 }
2063 if (!err && version == QStringLiteral("0.9")) {
2064 SKGTRACEL(10) << "Migration from 0.9 to 1.0" << SKGENDL;
2065
2066 err = SKGDocument::setParameter(QStringLiteral("SKG_UNIQUE_ID"), QLatin1String(""));
2067
2068 // Set new version
2069 version = QStringLiteral("1.0");
2070 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2071 oMigrationDone = true;
2072 }
2073 if (!err && version == QStringLiteral("1.0")) {
2074 // Migration from version 1.0 to 1.1
2075 SKGTRACEL(10) << "Migration from 1.0 to 1.1" << SKGENDL;
2076
2077 err = executeSqliteOrder(QStringLiteral("ALTER TABLE node ADD COLUMN t_icon TEXT DEFAULT ''"));
2078 IFOK(err) {
2079 SKGStringListList result;
2080 err = executeSelectSqliteOrder(QStringLiteral("SELECT id,t_data from node"), result);
2081 int nb = result.count();
2082 for (int i = 1; !err && i < nb; ++i) {
2083 const QStringList& line = result.at(i);
2084 QString icon = QStringLiteral("folder-bookmark");
2085 QStringList data = SKGServices::splitCSVLine(line.at(1));
2086 if (data.count() > 2) {
2087 icon = data.at(2);
2088 }
2089 data.removeAt(2);
2090 err = executeSqliteOrder("UPDATE node set t_icon='" % SKGServices::stringToSqlString(icon) %
2091 "', t_data='" % SKGServices::stringToSqlString(SKGServices::stringsToCsv(data)) % "' where id=" % line.at(0));
2092 }
2093 }
2094
2095 // Set new version
2096 version = QStringLiteral("1.1");
2097 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2098 oMigrationDone = true;
2099 }
2100 if (!err && version == QStringLiteral("1.1")) {
2101 // Migration from version 1.1 to 1.2
2102 SKGTRACEL(10) << "Migration from 1.1 to 1.2" << SKGENDL;
2103
2104 QStringList sql;
2105 sql << QStringLiteral("ALTER TABLE doctransaction ADD COLUMN t_refreshviews VARCHAR(1) DEFAULT 'Y' CHECK (t_refreshviews IN ('Y', 'N'))")
2106 << QStringLiteral("UPDATE doctransaction set t_refreshviews='Y'");
2107
2108 err = executeSqliteOrders(sql);
2109
2110 // Set new version
2111 version = QStringLiteral("1.2");
2112 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2113 oMigrationDone = true;
2114 }
2115 if (!err && version == QStringLiteral("1.2")) {
2116 // Migration from version 1.2 to 1.3
2117 SKGTRACEL(10) << "Migration from 1.2 to 1.3" << SKGENDL;
2118
2119 err = SKGDocument::refreshViewsIndexesAndTriggers();
2120
2121 QStringList sql;
2122 sql << QStringLiteral("DELETE FROM node WHERE (r_node_id IS NULL OR r_node_id='') AND EXISTS (SELECT 1 FROM node n WHERE n.t_name=node.t_name AND r_node_id=0)")
2123 << QStringLiteral("UPDATE node SET t_name=t_name");
2124 IFOKDO(err, executeSqliteOrders(sql))
2125
2126 // Set new version
2127 version = QStringLiteral("1.3");
2128 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2129 oMigrationDone = true;
2130 }
2131 if (!err && version == QStringLiteral("1.3")) {
2132 // Migration from version 1.3 to 1.4
2133 SKGTRACEL(10) << "Migration from 1.3 to 1.4" << SKGENDL;
2134
2135 QStringList sql;
2136 sql << "CREATE TABLE node2 ("
2137 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
2138 "t_name TEXT NOT NULL DEFAULT '' CHECK (t_name NOT LIKE '%" % OBJECTSEPARATOR % "%'),"
2139 "t_fullname TEXT,"
2140 "t_icon TEXT DEFAULT '',"
2141 "f_sortorder FLOAT,"
2142 "t_autostart VARCHAR(1) DEFAULT 'N' CHECK (t_autostart IN ('Y', 'N')),"
2143 "t_data TEXT,"
2144 "rd_node_id INT CONSTRAINT fk_id REFERENCES node(id) ON DELETE CASCADE)"
2145
2146 << QStringLiteral("INSERT INTO node2 (id, t_name, t_fullname, t_icon, f_sortorder, t_autostart, t_data, rd_node_id) "
2147 "SELECT id, t_name, t_fullname, t_icon, f_sortorder, t_autostart, t_data, r_node_id FROM node")
2148
2149 << QStringLiteral("DROP TABLE IF EXISTS node")
2150 << QStringLiteral("ALTER TABLE node2 RENAME TO node");
2151
2152 err = executeSqliteOrders(sql);
2153
2154 // Set new version
2155 version = QStringLiteral("1.4");
2156 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2157 oMigrationDone = true;
2158 }
2159 if (!err && version == QStringLiteral("1.4")) {
2160 // Migration from version 1.4 to 1.5
2161 SKGTRACEL(10) << "Migration from 1.4 to 1.5" << SKGENDL;
2162
2163 err = SKGDocument::refreshViewsIndexesAndTriggers();
2164
2165 QStringList sql;
2166 sql << QStringLiteral("UPDATE parameters SET t_uuid_parent='advice' WHERE t_uuid_parent='advices'");
2167 IFOKDO(err, executeSqliteOrders(sql))
2168
2169 // Set new version
2170 version = QStringLiteral("1.5");
2171 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2172 oMigrationDone = true;
2173 }
2174 if (!err && version == QStringLiteral("1.5")) {
2175 // Migration from version 1.5 to 1.6
2176 SKGTRACEL(10) << "Migration from 1.5 to 1.6" << SKGENDL;
2177
2178 err = SKGDocument::refreshViewsIndexesAndTriggers();
2179
2180 QStringList sql;
2181 sql << QStringLiteral("DROP TABLE IF EXISTS doctransactionmsg2")
2182 << QStringLiteral("CREATE TABLE doctransactionmsg2 ("
2183 "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
2184 "rd_doctransaction_id INTEGER NOT NULL,"
2185 "t_message TEXT NOT NULL DEFAULT '',"
2186 "t_type VARCHAR(1) DEFAULT 'I' CHECK (t_type IN ('P', 'I', 'W', 'E', 'H')))") // Positive, Information, Warning, Error, Hidden
2187 << QStringLiteral("INSERT INTO doctransactionmsg2 (id, rd_doctransaction_id, t_message, t_type) SELECT id, rd_doctransaction_id, t_message, (CASE WHEN t_popup='Y' THEN 'I' ELSE 'H' END) FROM doctransactionmsg")
2188
2189 << QStringLiteral("DROP TABLE IF EXISTS doctransactionmsg")
2190 << QStringLiteral("ALTER TABLE doctransactionmsg2 RENAME TO doctransactionmsg");
2191 IFOKDO(err, executeSqliteOrders(sql))
2192
2193 // Set new version
2194 version = QStringLiteral("1.6");
2195 IFOKDO(err, SKGDocument::setParameter(QStringLiteral("SKG_DB_VERSION"), version))
2196 oMigrationDone = true;
2197 }
2198 }
2199 IFOKDO(err, stepForward(1, i18nc("Progression step", "Refresh views")))
2200
2201 // Set sqlite last version
2202 SKGStringListList listTmp;
2203 IFOKDO(err, executeSelectSqliteOrder(QStringLiteral("select sqlite_version()"), listTmp))
2204 if (!err && listTmp.count() == 2) {
2205 err = setParameter(QStringLiteral("SKG_SQLITE_LAST_VERSION"), listTmp.at(1).at(0));
2206 }
2207
2208 // Refresh views
2209 IFOKDO(err, refreshViewsIndexesAndTriggers())
2210 IFOKDO(err, stepForward(2, i18nc("Progression step", "Update materialized views")))
2211
2212 // Refresh materialized views
2213 if (!err && oMigrationDone) {
2214 err = computeMaterializedViews();
2215 }
2216 IFOKDO(err, stepForward(3))
2217
2218 IFKO(err) err.addError(ERR_FAIL, i18nc("Error message: Could not perform database migration", "Database migration from version %1 to version %2 failed", initialversion, version));
2219 }
2220
2221 return err;
2222 }
2223
createUndoRedoTemporaryTriggers() const2224 SKGError SKGDocument::createUndoRedoTemporaryTriggers() const
2225 {
2226 SKGError err;
2227 SKGTRACEINFUNCRC(10, err)
2228
2229 // Create triggers
2230 QStringList tables;
2231 err = this->getTablesList(tables);
2232 int nbTables = tables.count();
2233 for (int i = 0; !err && i < nbTables; ++i) {
2234 // Get table name
2235 const QString& table = tables.at(i);
2236
2237 // Do we have to treat this table
2238 if (!SKGListNotUndoable.contains("T." % table) && !table.startsWith(QLatin1String("vm_"))) {
2239 // YES
2240 // Get attributes name
2241 QStringList attributes;
2242 err = getAttributesList(table, attributes);
2243
2244 // Build sqlorder for update and insert
2245 QString sqlorderForUpdate2;
2246 QString sqlorderForInsert1;
2247 QString sqlorderForInsert2;
2248 int nbAttributes = attributes.count();
2249 for (int j = 0; !err && j < nbAttributes; ++j) {
2250 // Get attribute
2251 const QString& att = attributes.at(j);
2252
2253 // Do we have to treat this attribute
2254 if (!SKGListNotUndoable.contains("A." % table % '.' % att)) {
2255 // Build for update
2256 if (!sqlorderForUpdate2.isEmpty()) {
2257 sqlorderForUpdate2 += ',';
2258 }
2259 sqlorderForUpdate2 += att % "='||quote(old." % att % ")||'";
2260
2261 // Build for insert part 1
2262 if (!sqlorderForInsert1.isEmpty()) {
2263 sqlorderForInsert1 += ',';
2264 }
2265 sqlorderForInsert1 += att;
2266
2267 // Build for insert part 2
2268 if (!sqlorderForInsert2.isEmpty()) {
2269 sqlorderForInsert2 += ',';
2270 }
2271 sqlorderForInsert2 += "'||quote(old." % att % ")||'";
2272 }
2273 }
2274
2275 // Create specific triggers for the current transaction
2276 QStringList sqlOrders;
2277 // DROP DELETE trigger
2278 sqlOrders << "DROP TRIGGER IF EXISTS UR_" % table % "_IN"
2279
2280 // Create DELETE trigger
2281 << "CREATE TEMP TRIGGER UR_" % table % "_IN "
2282 "AFTER INSERT ON " % table % " BEGIN "
2283 "INSERT INTO doctransactionitem (rd_doctransaction_id, t_sqlorder,i_object_id,t_object_table,t_action) VALUES(0,'DELETE FROM " % table %
2284 " WHERE id='||new.id,new.id,'" % table % "','D');END"
2285
2286 // DROP UPDATE trigger
2287 << "DROP TRIGGER IF EXISTS UR_" % table % "_UP"
2288
2289 // Create UPDATE trigger
2290 << "CREATE TEMP TRIGGER UR_" % table % "_UP "
2291 "AFTER UPDATE ON " % table % " BEGIN "
2292 "INSERT INTO doctransactionitem (rd_doctransaction_id, t_sqlorder,i_object_id,t_object_table,t_action) VALUES(0,'UPDATE " % table %
2293 " SET " % sqlorderForUpdate2 %
2294 " WHERE id='||new.id,new.id,'" % table % "','U');END"
2295
2296 // DROP INSERT trigger
2297 << "DROP TRIGGER IF EXISTS UR_" % table % "_DE"
2298
2299 // Create INSERT trigger
2300 << "CREATE TEMP TRIGGER UR_" % table % "_DE "
2301 "AFTER DELETE ON " % table %
2302 " BEGIN "
2303 "INSERT INTO doctransactionitem (rd_doctransaction_id, t_sqlorder,i_object_id,t_object_table,t_action) VALUES(0,'INSERT INTO " % table %
2304 '(' % sqlorderForInsert1 % ") VALUES(" % sqlorderForInsert2 % ")',old.id,'" % table % "','I'); END";
2305 err = executeSqliteOrders(sqlOrders);
2306 }
2307 }
2308 return err;
2309 }
2310
getParameters(const QString & iParentUUID,const QString & iWhereClause)2311 QStringList SKGDocument::getParameters(const QString& iParentUUID, const QString& iWhereClause)
2312 {
2313 SKGTRACEINFUNC(10)
2314 QStringList output;
2315 QString wc = "t_uuid_parent='" % SKGServices::stringToSqlString(iParentUUID) % '\'';
2316 if (!iWhereClause.isEmpty()) {
2317 wc += " AND (" % iWhereClause % ')';
2318 }
2319 this->getDistinctValues(QStringLiteral("parameters"), QStringLiteral("t_name"), wc, output);
2320 return output;
2321 }
2322
getParameter(const QString & iName,const QString & iParentUUID) const2323 QString SKGDocument::getParameter(const QString& iName, const QString& iParentUUID) const
2324 {
2325 SKGTRACEINFUNC(10)
2326 SKGTRACEL(10) << "Input parameter [iName]=[" << iName << ']' << SKGENDL;
2327 QString output;
2328
2329 // Get parameter
2330 SKGObjectBase param;
2331 SKGError err = getObject(QStringLiteral("parameters"), "t_name='" % SKGServices::stringToSqlString(iName) %
2332 "' AND t_uuid_parent='" % SKGServices::stringToSqlString(iParentUUID) % '\'', param);
2333 IFOK(err) {
2334 output = param.getAttribute(QStringLiteral("t_value"));
2335 }
2336 return output;
2337 }
2338
getParameterBlob(const QString & iName,const QString & iParentUUID) const2339 QVariant SKGDocument::getParameterBlob(const QString& iName, const QString& iParentUUID) const
2340 {
2341 SKGTRACEINFUNC(10)
2342 SKGTRACEL(10) << "Input parameter [iName]=[" << iName << ']' << SKGENDL;
2343
2344 QVariant output;
2345 if (getMainDatabase() != nullptr) {
2346 QString sqlQuery = QStringLiteral("SELECT b_blob FROM parameters WHERE t_name=? AND t_uuid_parent=?");
2347 QSqlQuery query(*getMainDatabase());
2348 query.prepare(sqlQuery);
2349 query.addBindValue(iName);
2350 query.addBindValue(iParentUUID);
2351 if (Q_LIKELY(!query.exec())) {
2352 QSqlError sqlError = query.lastError();
2353 SKGTRACE << "WARNING: " << sqlQuery << SKGENDL;
2354 SKGTRACE << " returns :" << sqlError.text() << SKGENDL;
2355 } else {
2356 if (query.next()) {
2357 output = query.value(0);
2358 }
2359 }
2360 }
2361
2362 return output;
2363 }
2364
setParameter(const QString & iName,const QString & iValue,const QString & iFileName,const QString & iParentUUID,SKGPropertyObject * oObjectCreated) const2365 SKGError SKGDocument::setParameter(const QString& iName, const QString& iValue, const QString& iFileName, const QString& iParentUUID, SKGPropertyObject* oObjectCreated) const
2366 {
2367 SKGError err;
2368 SKGTRACEINFUNCRC(10, err)
2369 SKGTRACEL(10) << "Input parameter [iName] =[" << iName << ']' << SKGENDL;
2370 SKGTRACEL(10) << "Input parameter [iValue] =[" << iValue << ']' << SKGENDL;
2371 SKGTRACEL(10) << "Input parameter [iFileName]=[" << iFileName << ']' << SKGENDL;
2372 QVariant blob;
2373 QString value = iValue;
2374 QFile file(iFileName);
2375 if (file.exists()) {
2376 QFileInfo fileInfo(iFileName);
2377 if (fileInfo.isDir()) {
2378 value = "file://" % iFileName;
2379 } else {
2380 // Open file
2381 if (Q_UNLIKELY(!file.open(QIODevice::ReadOnly))) {
2382 err = SKGError(ERR_INVALIDARG, i18nc("Error message: Could not open a file", "Open file '%1' failed", iFileName));
2383 } else {
2384 QByteArray blob_bytes = file.readAll();
2385 if (blob_bytes.isEmpty()) {
2386 err = SKGError(ERR_INVALIDARG, i18nc("Error message: Could not open a file", "Open file '%1' failed", iFileName));
2387 } else {
2388 blob = blob_bytes;
2389 value = fileInfo.fileName();
2390 }
2391
2392 // close file
2393 file.close();
2394 }
2395 }
2396 }
2397
2398 IFOKDO(err, setParameter(iName, value, blob, iParentUUID, oObjectCreated))
2399 return err;
2400 }
2401
setParameter(const QString & iName,const QString & iValue,const QVariant & iBlob,const QString & iParentUUID,SKGPropertyObject * oObjectCreated) const2402 SKGError SKGDocument::setParameter(const QString& iName, const QString& iValue, const QVariant& iBlob, const QString& iParentUUID, SKGPropertyObject* oObjectCreated) const
2403 {
2404 SKGError err;
2405 SKGTRACEINFUNCRC(10, err)
2406 SKGTRACEL(10) << "Input parameter [iName] =[" << iName << ']' << SKGENDL;
2407 SKGTRACEL(10) << "Input parameter [iValue] =[" << iValue << ']' << SKGENDL;
2408 if (getMainDatabase() == nullptr) {
2409 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
2410 } else {
2411 SKGPropertyObject param(const_cast<SKGDocument*>(this));
2412 IFOKDO(err, param.setName(iName))
2413 IFOKDO(err, param.setValue(iValue))
2414 IFOKDO(err, param.setParentId(iParentUUID))
2415 IFOKDO(err, param.save(true, oObjectCreated != nullptr))
2416
2417 if (!err && !iBlob.isNull()) {
2418 err = param.load();
2419 IFOK(err) {
2420 // Set blob
2421 QString sqlQuery = QStringLiteral("UPDATE parameters SET b_blob=? WHERE id=?");
2422 QSqlQuery query(*getMainDatabase());
2423 query.prepare(sqlQuery);
2424 query.addBindValue(iBlob);
2425 query.addBindValue(param.getID());
2426 if (Q_LIKELY(!query.exec())) {
2427 QSqlError sqlError = query.lastError();
2428 QString msg = sqlQuery % ':' % sqlError.text();
2429 err = SKGError(SQLLITEERROR + sqlError.nativeErrorCode().toInt(), msg);
2430 }
2431 }
2432 }
2433 if (!err && oObjectCreated != nullptr) {
2434 *oObjectCreated = param;
2435 }
2436 }
2437 return err;
2438 }
2439
dump(int iMode) const2440 SKGError SKGDocument::dump(int iMode) const
2441 {
2442 SKGError err;
2443 // dump parameters
2444 SKGTRACE << "=== START DUMP ===" << SKGENDL;
2445 if ((iMode & DUMPSQLITE) != 0) {
2446 SKGTRACE << "=== DUMPSQLITE ===" << SKGENDL;
2447 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM sqlite_master order by type")));
2448
2449 SKGTRACE << "=== DUMPSQLITE (TEMPORARY) ===" << SKGENDL;
2450 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM sqlite_temp_master order by type")));
2451 }
2452
2453 if ((iMode & DUMPPARAMETERS) != 0) {
2454 SKGTRACE << "=== DUMPPARAMETERS ===" << SKGENDL;
2455 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM parameters order by id")));
2456 }
2457
2458 if ((iMode & DUMPNODES) != 0) {
2459 SKGTRACE << "=== DUMPNODES ===" << SKGENDL;
2460 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM node order by id")));
2461 }
2462
2463 if ((iMode & DUMPTRANSACTIONS) != 0) {
2464 // dump transaction
2465 SKGTRACE << "=== DUMPTRANSACTIONS ===" << SKGENDL;
2466 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM doctransaction order by id")));
2467
2468 // dump transaction
2469 SKGTRACE << "=== DUMPTRANSACTIONS (ITEMS) ===" << SKGENDL;
2470 err.addError(dumpSelectSqliteOrder(QStringLiteral("SELECT * FROM doctransactionitem order by rd_doctransaction_id, id")));
2471 }
2472 SKGTRACE << "=== END DUMP ===" << SKGENDL;
2473 return err;
2474 }
2475
getMainDatabase() const2476 QSqlDatabase* SKGDocument::getMainDatabase() const
2477 {
2478 if (!d->m_currentDatabase.isOpen()) {
2479 return nullptr;
2480 }
2481 return const_cast<QSqlDatabase*>(&d->m_currentDatabase);
2482 }
2483
getThreadDatabase() const2484 QSqlDatabase SKGDocument::getThreadDatabase() const
2485 {
2486 if (qApp->thread() != QThread::currentThread()) {
2487 d->m_mutex.lock();
2488 QString pwd = getPassword();
2489 QString dbName = getMainDatabase()->databaseName();
2490 QString conName = getMainDatabase()->connectionName();
2491
2492 QString id = conName % "_" % QString::number((quint64)QThread::currentThread(), 16);
2493 d->m_mutex.unlock();
2494
2495 auto tmpDatabase = QSqlDatabase::database(id);
2496 if (!tmpDatabase.isValid()) {
2497 tmpDatabase = QSqlDatabase::addDatabase(SQLDRIVERNAME, id);
2498 }
2499 if (tmpDatabase.databaseName() != dbName) {
2500 tmpDatabase.setConnectOptions(QStringLiteral("QSQLITE_OPEN_URI"));
2501 tmpDatabase.setDatabaseName(dbName);
2502 if (tmpDatabase.open()) {
2503 addSqliteAddon(&tmpDatabase);
2504 if (d->m_modeSQLCipher) {
2505 SKGServices::executeSqliteOrder(tmpDatabase, "PRAGMA KEY = '" % SKGServices::stringToSqlString(pwd.isEmpty() ? QStringLiteral("DEFAULTPASSWORD") : pwd) % "'");
2506 }
2507 }
2508 }
2509 return tmpDatabase;
2510 }
2511 return d->m_currentDatabase;
2512 }
2513
getConsolidatedView(const QString & iTable,const QString & iAsColumn,const QString & iAsRow,const QString & iAttribute,const QString & iOpAtt,const QString & iWhereClause,SKGStringListList & oTable,const QString & iMissingValue) const2514 SKGError SKGDocument::getConsolidatedView(const QString& iTable,
2515 const QString& iAsColumn,
2516 const QString& iAsRow,
2517 const QString& iAttribute,
2518 const QString& iOpAtt,
2519 const QString& iWhereClause,
2520 SKGStringListList& oTable,
2521 const QString& iMissingValue) const
2522 {
2523 SKGError err;
2524 SKGTRACEINFUNCRC(10, err)
2525 SKGTRACEL(10) << "Input parameter [iTable]=[" << iTable << ']' << SKGENDL;
2526 SKGTRACEL(10) << "Input parameter [iAsColumn]=[" << iAsColumn << ']' << SKGENDL;
2527 SKGTRACEL(10) << "Input parameter [iAsRow]=[" << iAsRow << ']' << SKGENDL;
2528 SKGTRACEL(10) << "Input parameter [iAttribute]=[" << iAttribute << ']' << SKGENDL;
2529 SKGTRACEL(10) << "Input parameter [iOpAtt]=[" << iOpAtt << ']' << SKGENDL;
2530 SKGTRACEL(10) << "Input parameter [iWhereClause]=[" << iWhereClause << ']' << SKGENDL;
2531 SKGTRACEL(10) << "Input parameter [iMissingValue]=[" << iMissingValue << ']' << SKGENDL;
2532
2533 // Mode
2534 int mode = 0;
2535 if (!iAsColumn.isEmpty()) {
2536 mode += 1;
2537 }
2538 if (!iAsRow.isEmpty()) {
2539 mode += 2;
2540 }
2541
2542 oTable.clear();
2543 oTable.push_back(QStringList());
2544
2545
2546 QStringList titles = oTable.at(0);
2547
2548 if (mode == 3) {
2549 titles.push_back(iAsRow % '/' % iAsColumn);
2550 } else {
2551 if (mode == 1) {
2552 titles.push_back(iAsColumn);
2553
2554 QStringList sums;
2555 sums.push_back(i18nc("Noun, the numerical sum of a list of values", "Sum"));
2556 oTable.push_back(sums);
2557 } else {
2558 if (mode == 2) {
2559 titles.push_back(iAsRow);
2560 titles.push_back(i18nc("Noun, the numerical sum of a list of values", "Sum"));
2561 }
2562 }
2563 }
2564 oTable.removeAt(0);
2565 oTable.insert(0, titles);
2566
2567 // Create sqlorder
2568 QString asColumn = iAsColumn;
2569 if (asColumn.startsWith(QLatin1String("p_"))) {
2570 QString propertyName = asColumn.right(asColumn.length() - 2);
2571 asColumn = "(SELECT t_value FROM parameters WHERE t_uuid_parent=" % iTable % ".id||'-" % SKGServices::getRealTable(iTable) % "' AND t_name='" % propertyName % "')";
2572 }
2573 QString asRow = iAsRow;
2574 if (asRow.startsWith(QLatin1String("p_"))) {
2575 QString propertyName = asRow.right(asRow.length() - 2);
2576 asRow = "(SELECT t_value FROM parameters WHERE t_uuid_parent=" % iTable % ".id||'-" % SKGServices::getRealTable(iTable) % "' AND t_name='" % propertyName % "')";
2577 }
2578
2579 QString att = asColumn;
2580 if (!att.isEmpty() && !asRow.isEmpty()) {
2581 att += ',';
2582 }
2583 att += asRow;
2584
2585 QString sort = asRow;
2586 if (!sort.isEmpty() && !asColumn.isEmpty()) {
2587 sort += ',';
2588 }
2589 sort += asColumn;
2590
2591 if (!att.isEmpty()) {
2592 QString sql = "SELECT " % att % ',' % iOpAtt % '(' % iAttribute % ") FROM " % iTable;
2593 if (!iWhereClause.isEmpty()) {
2594 sql += " WHERE " % iWhereClause;
2595 }
2596 if (!iOpAtt.isEmpty()) {
2597 sql += " GROUP BY " % att;
2598 }
2599 sql += " ORDER BY " % sort;
2600
2601 QHash<QString, int> cols;
2602 QHash<QString, int> rows;
2603
2604 SKGTRACEL(10) << "sqlorder=[" << sql << ']' << SKGENDL;
2605 SKGStringListList listTmp;
2606 err = executeSelectSqliteOrder(sql, listTmp);
2607 int nb = listTmp.count();
2608 for (int i = 1; !err && i < nb; ++i) { // Title is ignored
2609 const QStringList& line = listTmp.at(i);
2610 int rowindex = -1;
2611 int colindex = -1;
2612 if (mode >= 2) {
2613 const QString& rowname = line.at(mode == 3 ? 1 : 0);
2614
2615 if (!rows.contains(rowname)) {
2616 QStringList r;
2617 int nbx = oTable.at(0).count();
2618 r.reserve(nbx);
2619 r.push_back(rowname);
2620 for (int j = 1; j < nbx; ++j) {
2621 r.push_back(iMissingValue);
2622 }
2623
2624 oTable.push_back(r);
2625
2626 rowindex = oTable.count() - 1;
2627 rows.insert(rowname, rowindex);
2628 } else {
2629 rowindex = rows[rowname];
2630 }
2631 } else {
2632 rowindex = 1;
2633 }
2634
2635 if (mode == 1 || mode == 3) {
2636 const QString& colname = line.at(0);
2637
2638 if (!cols.contains(colname)) {
2639 // Search better position of this column
2640 colindex = -1;
2641 {
2642 QHashIterator<QString, int> cols_i(cols);
2643 while (cols_i.hasNext()) {
2644 cols_i.next();
2645 if (colname > cols_i.key() && cols_i.value() > colindex) {
2646 colindex = cols_i.value();
2647 }
2648 }
2649 }
2650 if (colindex == -1) {
2651 colindex = 1;
2652 } else {
2653 ++colindex;
2654 }
2655
2656 int nbx = oTable.count();
2657 for (int j = 0; j < nbx; ++j) {
2658 if (j == 0) {
2659 oTable[j].insert(colindex, colname);
2660 } else {
2661 oTable[j].insert(colindex, iMissingValue);
2662 }
2663 }
2664
2665 {
2666 QHash<QString, int> tmp;
2667 QHashIterator<QString, int> cols_i(cols);
2668 while (cols_i.hasNext()) {
2669 cols_i.next();
2670 tmp.insert(cols_i.key(), cols_i.value() + (cols_i.value() >= colindex ? 1 : 0));
2671 }
2672
2673 cols = tmp;
2674 }
2675 cols.insert(colname, colindex);
2676
2677 } else {
2678 colindex = cols[colname];
2679 }
2680 } else {
2681 colindex = 1;
2682 }
2683
2684 const QString& sum = line.at(mode == 3 ? 2 : 1);
2685
2686 oTable[rowindex][colindex] = sum;
2687 }
2688
2689 IFSKGTRACEL(10) {
2690 QStringList dump2 = SKGServices::tableToDump(oTable, SKGServices::DUMP_TEXT);
2691 int nbl = dump2.count();
2692 for (int i = 0; i < nbl; ++i) {
2693 SKGTRACE << dump2.at(i) << SKGENDL;
2694 }
2695 }
2696
2697 // Correction bug 205466 vvv
2698 // If some months or years are missing, we must add them.
2699 if (asColumn.startsWith(QLatin1String("d_"))) {
2700 for (int c = 1; c < oTable[0].count() - 1; ++c) { // Dynamic size
2701 bool forecast = false;
2702 QString title = oTable.at(0).at(c);
2703 if (title.isEmpty()) {
2704 title = QStringLiteral("0000");
2705 }
2706
2707 if (title.endsWith(QLatin1String("999"))) {
2708 title = title.left(title.count() - 3);
2709 forecast = true;
2710 }
2711 QString nextTitle = oTable.at(0).at(c + 1);
2712 if (nextTitle.endsWith(QLatin1String("999"))) {
2713 nextTitle = nextTitle.left(nextTitle.count() - 3);
2714 forecast = true;
2715 }
2716
2717 QString dateFormat = (asColumn == QStringLiteral("d_date") ? QStringLiteral("yyyy-MM-dd") :
2718 (asColumn == QStringLiteral("d_DATEMONTH") ? QStringLiteral("yyyy-MM") :
2719 (asColumn == QStringLiteral("d_DATEQUARTER") ? QStringLiteral("yyyy-QM") :
2720 (asColumn == QStringLiteral("d_DATESEMESTER") ? QStringLiteral("yyyy-SM") :
2721 (asColumn == QStringLiteral("d_DATEWEEK") ? QStringLiteral("yyyy-WM") : QStringLiteral("yyyy"))))));
2722 QDate nextExpected = QDate::fromString(title, dateFormat);
2723 QString nextExpectedString;
2724 if (asColumn == QStringLiteral("d_DATEWEEK")) {
2725 /* TODO(Stephane MANKOWSKI)
2726 QStringList items=SKGServices::splitCSVLine(oTable.at(0).at(c),'-');
2727 nextExpected=QDate(SKGServices::stringToInt(items.at(0)), 1, 1);
2728 QString w=items.at(1);
2729 w.remove('W');
2730 nextExpected=nextExpected.addDays(7*SKGServices::stringToInt(w));
2731 QString newW=SKGServices::intToString(nextExpected.weekNumber());
2732 if(newW.count()==1) newW='0'+newW;
2733 */
2734 nextExpectedString = nextTitle;
2735 } else if (asColumn == QStringLiteral("d_DATEMONTH")) {
2736 nextExpected = nextExpected.addMonths(1);
2737 nextExpectedString = nextExpected.toString(dateFormat);
2738 } else if (asColumn == QStringLiteral("d_DATEQUARTER")) {
2739 nextExpected = nextExpected.addMonths(nextExpected.month() * 3 - nextExpected.month()); // convert quarter in month
2740 nextExpected = nextExpected.addMonths(3);
2741 nextExpectedString = nextExpected.toString(QStringLiteral("yyyy-Q")) % (nextExpected.month() <= 3 ? '1' : (nextExpected.month() <= 6 ? '2' : (nextExpected.month() <= 9 ? '3' : '4')));
2742 } else if (asColumn == QStringLiteral("d_DATESEMESTER")) {
2743 nextExpected = nextExpected.addMonths(nextExpected.month() * 6 - nextExpected.month()); // convert semester in month
2744 nextExpected = nextExpected.addMonths(6);
2745 nextExpectedString = nextExpected.toString(QStringLiteral("yyyy-S")) % (nextExpected.month() <= 6 ? '1' : '2');
2746 } else if (asColumn == QStringLiteral("d_DATEYEAR")) {
2747 nextExpected = nextExpected.addYears(1);
2748 nextExpectedString = nextExpected.toString(dateFormat);
2749 } else {
2750 nextExpected = nextExpected.addDays(1);
2751 nextExpectedString = nextExpected.toString(dateFormat);
2752 }
2753 if (title != QStringLiteral("0000") && nextTitle != nextExpectedString && nextTitle != title) {
2754 int colindex = c + 1;
2755 if (forecast) {
2756 nextExpectedString += QStringLiteral("999");
2757 }
2758
2759 int nbx = oTable.count();
2760 oTable[0].insert(colindex, nextExpectedString);
2761 for (int j = 1; j < nbx; ++j) {
2762 oTable[j].insert(colindex, iMissingValue);
2763 }
2764 }
2765 }
2766 }
2767 // Correction bug 205466 ^^^
2768 }
2769
2770 return err;
2771 }
2772
getDisplaySchemas(const QString & iRealTable) const2773 SKGDocument::SKGModelTemplateList SKGDocument::getDisplaySchemas(const QString& iRealTable) const
2774 {
2775 SKGDocument::SKGModelTemplateList listSchema;
2776
2777 // Build schemas
2778 if (iRealTable == QStringLiteral("doctransaction")) {
2779 SKGModelTemplate def;
2780 def.id = QStringLiteral("default");
2781 def.name = i18nc("Noun, the default value of an item", "Default");
2782 def.icon = QStringLiteral("edit-undo");
2783 def.schema = QStringLiteral("t_name;t_value;d_lastmodifdate;t_savestep");
2784 listSchema.push_back(def);
2785
2786 SKGModelTemplate minimum;
2787 minimum.id = QStringLiteral("minimum");
2788 minimum.name = i18nc("Noun, the minimum value of an item", "Minimum");
2789 minimum.icon = QLatin1String("");
2790 minimum.schema = QStringLiteral("t_name;t_value;d_lastmodifdate|N;t_savestep|N");
2791 listSchema.push_back(minimum);
2792 } else if (iRealTable == QStringLiteral("parameters")) {
2793 SKGModelTemplate def;
2794 def.id = QStringLiteral("default");
2795 def.name = i18nc("Noun, the default value of an item", "Default");
2796 def.icon = QStringLiteral("edit-undo");
2797 def.schema = QStringLiteral("t_name;t_value");
2798 listSchema.push_back(def);
2799 } else if (iRealTable == QStringLiteral("node")) {
2800 SKGModelTemplate def;
2801 def.id = QStringLiteral("default");
2802 def.name = i18nc("Noun, the default value of an item", "Default");
2803 def.icon = QStringLiteral("edit-undo");
2804 def.schema = QStringLiteral("t_name");
2805 listSchema.push_back(def);
2806 } else {
2807 SKGModelTemplate def;
2808 def.id = QStringLiteral("default");
2809 def.name = i18nc("Noun, the default value of an item", "Default");
2810 def.icon = QStringLiteral("edit-undo");
2811 def.schema = QLatin1String("");
2812 SKGStringListList lines;
2813 executeSelectSqliteOrder("PRAGMA table_info(" % iRealTable % ");", lines);
2814 for (const auto& line : qAsConst(lines)) {
2815 if (!def.schema.isEmpty()) {
2816 def.schema += ';';
2817 }
2818 def.schema += line[1];
2819 }
2820 listSchema.push_back(def);
2821 }
2822
2823 return listSchema;
2824 }
2825
getDisplay(const QString & iString) const2826 QString SKGDocument::getDisplay(const QString& iString) const
2827 {
2828 QString output = iString.toLower();
2829
2830 if (output.endsWith(QLatin1String("t_name"))) {
2831 output = i18nc("Noun, the name of an item", "Name");
2832 } else if (output.endsWith(QLatin1String("d_date"))) {
2833 output = i18nc("Noun, the date of an item", "Date");
2834 } else if (output.endsWith(QLatin1String("t_savestep"))) {
2835 output = i18nc("Verb, save a document", "Save");
2836 } else if (output.endsWith(QLatin1String("t_value"))) {
2837 output = i18nc("Noun, the value of an item", "Value");
2838 } else if (output.endsWith(QLatin1String("d_lastmodifdate"))) {
2839 output = i18nc("Noun, date of last modification", "Last modification");
2840 } else if (output.startsWith(QLatin1String("p_"))) {
2841 output = iString;
2842 output = output.right(output.count() - 2);
2843 } else if (output.contains(QStringLiteral(".p_"))) {
2844 output = iString;
2845 output = output.replace(QStringLiteral(".p_"), QStringLiteral("."));
2846 } else {
2847 output = iString;
2848 }
2849 return output;
2850 }
2851
getIconName(const QString & iString) const2852 QString SKGDocument::getIconName(const QString& iString) const
2853 {
2854 QString output = iString.toLower();
2855 if (output.startsWith(QLatin1String("p_")) || output.contains(QStringLiteral("p_"))) {
2856 return QStringLiteral("tag");
2857 }
2858 return QLatin1String("");
2859 }
2860
getIcon(const QString & iString) const2861 QIcon SKGDocument::getIcon(const QString& iString) const
2862 {
2863 return SKGServices::fromTheme(getIconName(iString));
2864 }
2865
getRealAttribute(const QString & iString) const2866 QString SKGDocument::getRealAttribute(const QString& iString) const
2867 {
2868 if (iString == iString.toLower()) {
2869 return iString;
2870 }
2871 return QLatin1String("");
2872 }
2873
getAttributeType(const QString & iAttributeName) const2874 SKGServices::AttributeType SKGDocument::getAttributeType(const QString& iAttributeName) const
2875 {
2876 SKGServices::AttributeType output = SKGServices::TEXT;
2877 if (iAttributeName.startsWith(QLatin1String("d_"))) {
2878 output = SKGServices::DATE;
2879 } else if (iAttributeName.startsWith(QLatin1String("i_"))) {
2880 output = SKGServices::INTEGER;
2881 } else if (iAttributeName.startsWith(QLatin1String("rd_")) || iAttributeName.startsWith(QLatin1String("rc_")) || iAttributeName.startsWith(QLatin1String("r_")) || iAttributeName.startsWith(QLatin1String("id_"))) {
2882 output = SKGServices::LINK;
2883 } else if (iAttributeName.startsWith(QLatin1String("f_"))) {
2884 output = SKGServices::FLOAT;
2885 } else if (iAttributeName.startsWith(QLatin1String("b_"))) {
2886 output = SKGServices::BLOB;
2887 } else if (iAttributeName == QStringLiteral("id")) {
2888 output = SKGServices::ID;
2889 } else if (iAttributeName == QStringLiteral("t_savestep") || iAttributeName == QStringLiteral("t_refreshviews")) {
2890 output = SKGServices::BOOL;
2891 }
2892
2893 return output;
2894 }
2895
getUnit(const QString & iPrefixInCache) const2896 SKGServices::SKGUnitInfo SKGDocument::getUnit(const QString& iPrefixInCache) const
2897 {
2898 SKGServices::SKGUnitInfo output;
2899 output.Name = getCachedValue(iPrefixInCache % "UnitCache");
2900 if (output.Name.isEmpty()) {
2901 refreshCache(QStringLiteral("unit"));
2902 output.Name = getCachedValue(iPrefixInCache % "UnitCache");
2903 }
2904 output.Symbol = getCachedValue(iPrefixInCache % "UnitSymbolCache");
2905 QString val = getCachedValue(iPrefixInCache % "UnitValueCache");
2906 if (!val.isEmpty()) {
2907 output.Value = SKGServices::stringToDouble(val);
2908 } else {
2909 output.Value = 1;
2910 }
2911 val = getCachedValue(iPrefixInCache % "UnitDecimalCache");
2912 if (!val.isEmpty()) {
2913 output.NbDecimal = SKGServices::stringToInt(val);
2914 } else {
2915 output.NbDecimal = 2;
2916 }
2917
2918 return output;
2919 }
2920
formatMoney(double iValue,const SKGServices::SKGUnitInfo & iUnit,bool iHtml) const2921 QString SKGDocument::formatMoney(double iValue, const SKGServices::SKGUnitInfo& iUnit, bool iHtml) const
2922 {
2923 QString val = SKGServices::toCurrencyString(iValue / iUnit.Value, iUnit.Symbol, iUnit.NbDecimal);
2924 if (iHtml) {
2925 // Return value
2926 if (iValue < 0) {
2927 // Get std colors
2928 KColorScheme scheme(QPalette::Normal);
2929 return QStringLiteral("<font color=\"") % scheme.foreground(KColorScheme::NegativeText).color().name() % "\">" % SKGServices::stringToHtml(val) % "</font>";
2930 }
2931 return SKGServices::stringToHtml(val);
2932 }
2933 return val;
2934 }
2935
formatPrimaryMoney(double iValue) const2936 QString SKGDocument::formatPrimaryMoney(double iValue) const
2937 {
2938 return SKGServices::doubleToString(iValue);
2939 }
2940
formatSecondaryMoney(double iValue) const2941 QString SKGDocument::formatSecondaryMoney(double iValue)const
2942 {
2943 return SKGServices::doubleToString(iValue);
2944 }
2945
formatPercentage(double iValue,bool iInvertColors) const2946 QString SKGDocument::formatPercentage(double iValue, bool iInvertColors) const
2947 {
2948 // Get std colors
2949 KColorScheme scheme(QPalette::Normal);
2950 QString negative = scheme.foreground(KColorScheme::NegativeText).color().name();
2951 QString positive = scheme.foreground(KColorScheme::PositiveText).color().name();
2952
2953 // Return value
2954 QString p = SKGServices::toPercentageString(iValue);
2955 if (iValue > 0) {
2956 p = '+' % p;
2957 }
2958 if (p.count() > 20 || std::isnan(iValue) || std::isinf(iValue)) {
2959 p = QChar(8734);
2960 }
2961 return "<font color=\"" %
2962 QString((iValue < 0 && !iInvertColors) || (iValue >= 0 && iInvertColors) ? negative : positive) %
2963 "\">" % SKGServices::stringToHtml(p) %
2964 "</font>";
2965 }
2966
getFileExtension() const2967 QString SKGDocument::getFileExtension() const
2968 {
2969 return QStringLiteral("skgc");
2970 }
2971
getDocumentHeader() const2972 QString SKGDocument::getDocumentHeader() const
2973 {
2974 return QStringLiteral("SKG");
2975 }
2976
addValueInCache(const QString & iKey,const QString & iValue) const2977 void SKGDocument::addValueInCache(const QString& iKey, const QString& iValue) const
2978 {
2979 d->m_cache[iKey] = iValue;
2980 }
2981
addSqlResultInCache(const QString & iKey,const SKGStringListList & iValue) const2982 void SKGDocument::addSqlResultInCache(const QString& iKey, const SKGStringListList& iValue) const
2983 {
2984 d->m_mutex.lock();
2985 (*d->m_cacheSql)[iKey] = iValue;
2986 d->m_mutex.unlock();
2987 }
2988
getCachedValue(const QString & iKey) const2989 QString SKGDocument::getCachedValue(const QString& iKey) const
2990 {
2991 return d->m_cache.value(iKey);
2992 }
2993
getCachedSqlResult(const QString & iKey) const2994 SKGStringListList SKGDocument::getCachedSqlResult(const QString& iKey) const
2995 {
2996 return d->m_cacheSql->value(iKey);
2997 }
2998
refreshCache(const QString & iTable) const2999 void SKGDocument::refreshCache(const QString& iTable) const
3000 {
3001 Q_UNUSED(iTable)
3002 }
3003
setBackupParameters(const QString & iPrefix,const QString & iSuffix) const3004 void SKGDocument::setBackupParameters(const QString& iPrefix, const QString& iSuffix) const
3005 {
3006 d->m_backupPrefix = iPrefix;
3007 d->m_backupSuffix = iSuffix;
3008 }
3009
getCurrentTemporaryFile() const3010 QString SKGDocument::getCurrentTemporaryFile() const
3011 {
3012 return d->m_temporaryFile;
3013 }
3014
getTemporaryFile(const QString & iFileName,bool iForceReadOnly)3015 QString SKGDocument::getTemporaryFile(const QString& iFileName, bool iForceReadOnly)
3016 {
3017 QString output;
3018 QFileInfo fi(iFileName);
3019 QFileInfo di(fi.dir().path());
3020 if (iForceReadOnly || !QUrl::fromUserInput(iFileName).isLocalFile() || !di.permission(QFile::WriteUser)) {
3021 output = QDir::tempPath();
3022 } else {
3023 output = fi.absolutePath();
3024 }
3025 return output += "/." % fi.fileName() % ".wrk";
3026 }
3027
getBackupFile(const QString & iFileName) const3028 QString SKGDocument::getBackupFile(const QString& iFileName) const
3029 {
3030 QString output;
3031 if (!d->m_backupPrefix.isEmpty() || !d->m_backupSuffix.isEmpty()) {
3032 QFileInfo fi(iFileName);
3033 if (d->m_backupSuffix.endsWith(QLatin1String(".skg"))) {
3034 output = d->m_backupPrefix % fi.baseName() % d->m_backupSuffix;
3035 } else {
3036 output = d->m_backupPrefix % fi.fileName() % d->m_backupSuffix;
3037 }
3038 output = output.replace(QStringLiteral("<DATE>"), SKGServices::dateToSqlString(QDateTime::currentDateTime().date()));
3039 output = output.replace(QStringLiteral("<TIME>"), SKGServices::timeToString(QDateTime::currentDateTime()));
3040 if (!QFileInfo(output).isAbsolute()) {
3041 output = fi.absolutePath() % '/' % output;
3042 }
3043 }
3044
3045 return output;
3046 }
3047
getObjects(const QString & iTable,const QString & iWhereClause,SKGObjectBase::SKGListSKGObjectBase & oListObject) const3048 SKGError SKGDocument::getObjects(const QString& iTable, const QString& iWhereClause, SKGObjectBase::SKGListSKGObjectBase& oListObject) const
3049 {
3050 SKGError err;
3051
3052 // Initialisation
3053 oListObject.clear();
3054
3055 // Execute sqlorder
3056 SKGStringListList result;
3057 err = executeSelectSqliteOrder(
3058 QString("SELECT * FROM " % iTable %
3059 (!iWhereClause.isEmpty() ? QString(" WHERE " % iWhereClause) : QString())),
3060 result);
3061
3062 // Create output
3063 IFOK(err) {
3064 SKGStringListList::const_iterator itrow = result.constBegin();
3065 QStringList columns = *(itrow);
3066 ++itrow;
3067 for (; !err && itrow != result.constEnd(); ++itrow) {
3068 QStringList values = *(itrow);
3069 SKGObjectBase tmp(const_cast<SKGDocument*>(this), iTable);
3070 err = tmp.setAttributes(columns, values);
3071 oListObject.push_back(tmp);
3072 }
3073 }
3074 return err;
3075 }
3076
existObjects(const QString & iTable,const QString & iWhereClause,bool & oExist) const3077 SKGError SKGDocument::existObjects(const QString& iTable, const QString& iWhereClause, bool& oExist) const
3078 {
3079 SKGError err;
3080
3081 // Initialisation
3082 oExist = false;
3083
3084 // Execute sqlorder
3085 SKGStringListList result;
3086 err = executeSelectSqliteOrder(
3087 "SELECT EXISTS(SELECT 1 FROM " % iTable % " WHERE " %
3088 (!iWhereClause.isEmpty() ? iWhereClause : QStringLiteral("1=1")) % ')',
3089 result);
3090
3091 // Create output
3092 IFOK(err) oExist = (result.at(1).at(0) == QStringLiteral("1"));
3093 return err;
3094 }
3095
getNbObjects(const QString & iTable,const QString & iWhereClause,int & oNbObjects) const3096 SKGError SKGDocument::getNbObjects(const QString& iTable, const QString& iWhereClause, int& oNbObjects) const
3097 {
3098 SKGError err;
3099
3100 // Initialisation
3101 oNbObjects = 0;
3102
3103 // Execute sqlorder
3104 SKGStringListList result;
3105 err = executeSelectSqliteOrder(
3106 QString("SELECT count(1) FROM " % iTable %
3107 (!iWhereClause.isEmpty() ? QString(" WHERE " % iWhereClause) : QString())),
3108 result);
3109
3110 // Create output
3111 IFOK(err) oNbObjects = SKGServices::stringToInt(result.at(1).at(0));
3112 return err;
3113 }
3114
getObject(const QString & iTable,const QString & iWhereClause,SKGObjectBase & oObject) const3115 SKGError SKGDocument::getObject(const QString& iTable, const QString& iWhereClause, SKGObjectBase& oObject) const
3116 {
3117 SKGObjectBase::SKGListSKGObjectBase temporaryResult;
3118 oObject.resetID();
3119 SKGError err = SKGDocument::getObjects(iTable, iWhereClause, temporaryResult);
3120 IFOK(err) {
3121 int size = temporaryResult.size();
3122 if (Q_UNLIKELY(size > 1)) {
3123 err = SKGError(ERR_INVALIDARG, i18nc("Error message: We expected only one object in the result, but got more", "More than one object returned in '%1' for '%2'", iTable, iWhereClause));
3124 } else {
3125 if (Q_UNLIKELY(size == 0)) {
3126 err = SKGError(ERR_INVALIDARG, i18nc("Error message: We expected at least one object in the result, but got none", "No object returned in '%1' for '%2'", iTable, iWhereClause));
3127 } else {
3128 oObject = *(temporaryResult.begin());
3129 }
3130 }
3131 }
3132 return err;
3133 }
3134
getObject(const QString & iTable,int iId,SKGObjectBase & oObject) const3135 SKGError SKGDocument::getObject(const QString& iTable, int iId, SKGObjectBase& oObject) const
3136 {
3137 return getObject(iTable, "id=" % SKGServices::intToString(iId), oObject);
3138 }
3139
getTablesList(QStringList & oResult) const3140 SKGError SKGDocument::getTablesList(QStringList& oResult) const
3141 {
3142 return getDistinctValues(QStringLiteral("sqlite_master"), QStringLiteral("name"),
3143 QStringLiteral("type='table' AND name NOT LIKE 'sqlite_%'"),
3144 oResult);
3145 }
3146
getDistinctValues(const QString & iTable,const QString & iAttribute,const QString & iWhereClause,QStringList & oResult) const3147 SKGError SKGDocument::getDistinctValues(const QString& iTable, const QString& iAttribute, const QString& iWhereClause, QStringList& oResult) const
3148 {
3149 SKGError err;
3150 _SKGTRACEINFUNCRC(10, err)
3151 // initialisation
3152 oResult.clear();
3153
3154 // Search
3155 SKGStringListList temporaryResult;
3156 err = executeSelectSqliteOrder(
3157 "SELECT DISTINCT " % iAttribute %
3158 " FROM " % iTable % " WHERE (" %
3159 (!iWhereClause.isEmpty() ? iWhereClause : QStringLiteral("1=1")) %
3160 ") ORDER BY " % iAttribute
3161 // Correction bug 202167 vvv
3162 % " COLLATE NOCASE"
3163 // Correction bug 202167 ^^^
3164 , temporaryResult);
3165 IFOK(err) {
3166 SKGStringListList::const_iterator it = temporaryResult.constBegin();
3167 ++it; // to forget column name
3168 for (; it != temporaryResult.constEnd(); ++it) {
3169 oResult.push_back(*(it->constBegin()));
3170 }
3171 }
3172
3173 return err;
3174 }
3175
getDistinctValues(const QString & iTable,const QString & iAttribute,QStringList & oResult) const3176 SKGError SKGDocument::getDistinctValues(const QString& iTable, const QString& iAttribute, QStringList& oResult) const
3177 {
3178 return getDistinctValues(iTable, iAttribute,
3179 iAttribute % " IS NOT NULL AND " % iAttribute % "!=''",
3180 oResult);
3181 }
3182
executeSqliteOrder(const QString & iSqlOrder,int * iLastId) const3183 SKGError SKGDocument::executeSqliteOrder(const QString& iSqlOrder, int* iLastId) const
3184 {
3185 SKGError err;
3186 _SKGTRACEINFUNCRC(10, err)
3187 if (getMainDatabase() == nullptr) {
3188 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
3189 } else {
3190 err = SKGServices::executeSqliteOrder(*getMainDatabase(), iSqlOrder, iLastId);
3191 }
3192 return err;
3193 }
3194
executeSqliteOrder(const QString & iSqlOrder) const3195 SKGError SKGDocument::executeSqliteOrder(const QString& iSqlOrder) const
3196 {
3197 return SKGDocument::executeSqliteOrder(iSqlOrder, nullptr);
3198 }
3199
executeSqliteOrders(const QStringList & iSqlOrders) const3200 SKGError SKGDocument::executeSqliteOrders(const QStringList& iSqlOrders) const
3201 {
3202 SKGError err;
3203 _SKGTRACEINFUNCRC(10, err)
3204 if (getMainDatabase() == nullptr) {
3205 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
3206 } else {
3207 err = SKGServices::executeSqliteOrders(*getMainDatabase(), iSqlOrders);
3208 }
3209 return err;
3210 }
3211
executeSqliteOrder(const QString & iSqlOrder,const QMap<QString,QVariant> & iBind,int * iLastId) const3212 SKGError SKGDocument::executeSqliteOrder(const QString& iSqlOrder, const QMap< QString, QVariant >& iBind, int* iLastId) const
3213 {
3214 SKGError err;
3215 _SKGTRACEINFUNCRC(10, err)
3216 if (getMainDatabase() == nullptr) {
3217 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
3218 } else {
3219 err = SKGServices::executeSqliteOrder(*getMainDatabase(), iSqlOrder, iBind, iLastId);
3220 }
3221 return err;
3222 }
3223
dumpSelectSqliteOrder(const QString & iSqlOrder,QTextStream * oStream,SKGServices::DumpMode iMode) const3224 SKGError SKGDocument::dumpSelectSqliteOrder(const QString& iSqlOrder, QTextStream* oStream, SKGServices::DumpMode iMode) const
3225 {
3226 SKGError err;
3227 _SKGTRACEINFUNCRC(10, err)
3228 if (getMainDatabase() == nullptr) {
3229 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
3230 } else {
3231 err = SKGServices::dumpSelectSqliteOrder(*getMainDatabase(), iSqlOrder, oStream, iMode);
3232 }
3233 return err;
3234 }
3235
dumpSelectSqliteOrder(const QString & iSqlOrder,QString & oResult,SKGServices::DumpMode iMode) const3236 SKGError SKGDocument::dumpSelectSqliteOrder(const QString& iSqlOrder, QString& oResult, SKGServices::DumpMode iMode) const
3237 {
3238 SKGError err;
3239 _SKGTRACEINFUNCRC(10, err)
3240 if (getMainDatabase() == nullptr) {
3241 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
3242 } else {
3243 err = SKGServices::dumpSelectSqliteOrder(*getMainDatabase(), iSqlOrder, oResult, iMode);
3244 }
3245 return err;
3246 }
3247
dumpSelectSqliteOrder(const QString & iSqlOrder,QStringList & oResult,SKGServices::DumpMode iMode) const3248 SKGError SKGDocument::dumpSelectSqliteOrder(const QString& iSqlOrder, QStringList& oResult, SKGServices::DumpMode iMode) const
3249 {
3250 SKGError err;
3251 _SKGTRACEINFUNCRC(10, err)
3252 if (getMainDatabase() == nullptr) {
3253 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
3254 } else {
3255 err = SKGServices::dumpSelectSqliteOrder(*getMainDatabase(), iSqlOrder, oResult, iMode);
3256 }
3257 return err;
3258 }
3259
executeSingleSelectSqliteOrder(const QString & iSqlOrder,QString & oResult,bool iUseCache) const3260 SKGError SKGDocument::executeSingleSelectSqliteOrder(const QString& iSqlOrder, QString& oResult, bool iUseCache) const
3261 {
3262 SKGError err;
3263 _SKGTRACEINFUNCRC(10, err)
3264 // Is the select in cache?
3265 if (iUseCache && d->m_cache.contains(iSqlOrder)) {
3266 // Yes => get the value
3267 oResult = getCachedValue(iSqlOrder);
3268 SKGTRACEL(10) << "Result retrieved from cache for:" << iSqlOrder << SKGENDL;
3269
3270 } else {
3271 // No => Run the select
3272 oResult.clear();
3273 double elapse = SKGServices::getMicroTime();
3274 if (getMainDatabase() == nullptr) {
3275 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
3276 } else {
3277 err = SKGServices::executeSingleSelectSqliteOrder(*getMainDatabase(), iSqlOrder, oResult);
3278 }
3279
3280 // Is the execution time too long
3281 if (SKGServices::getMicroTime() - elapse > 50) {
3282 // Yes => put the result in cache
3283 addValueInCache(iSqlOrder, oResult);
3284 }
3285 }
3286 return err;
3287 }
3288
executeSelectSqliteOrder(const QString & iSqlOrder,SKGStringListList & oResult,bool iUseCache) const3289 SKGError SKGDocument::executeSelectSqliteOrder(const QString& iSqlOrder, SKGStringListList& oResult, bool iUseCache) const
3290 {
3291 SKGError err;
3292 _SKGTRACEINFUNCRC(10, err)
3293 // Is the select in cache?
3294 if (iUseCache && d->m_cacheSql->contains(iSqlOrder)) {
3295 // Yes => get the value
3296 oResult = getCachedSqlResult(iSqlOrder);
3297 SKGTRACEL(10) << "Result retrieved from cache for:" << iSqlOrder << SKGENDL;
3298
3299 } else {
3300 // No => Run the select
3301 oResult.clear();
3302 double elapse = SKGServices::getMicroTime();
3303 if (getMainDatabase() == nullptr) {
3304 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
3305 } else {
3306 err = SKGServices::executeSelectSqliteOrder(*getMainDatabase(), iSqlOrder, oResult);
3307 }
3308
3309 // Is the execution time too long
3310 if (SKGServices::getMicroTime() - elapse > 50) {
3311 // Yes => put the result in cache
3312 addSqlResultInCache(iSqlOrder, oResult);
3313 }
3314 }
3315 return err;
3316 }
3317
concurrentExecuteSelectSqliteOrder(const QString & iSqlOrder,const FuncSelect & iFunction,bool iExecuteInMainThread) const3318 void SKGDocument::concurrentExecuteSelectSqliteOrder(const QString& iSqlOrder, const FuncSelect& iFunction, bool iExecuteInMainThread) const
3319 {
3320 // Is the select in cache?
3321 if (d->m_cacheSql->contains(iSqlOrder)) {
3322 // Yes => get the value and call the function
3323 iFunction(getCachedSqlResult(iSqlOrder));
3324 SKGTRACEL(10) << "Result retrieved from cache for:" << iSqlOrder << SKGENDL;
3325
3326 } else {
3327 // No => Run the select asynchronously
3328 // Search a watcher free
3329 QFutureWatcher<SKGStringListList>* watcher = nullptr;
3330 if (iExecuteInMainThread) {
3331 for (auto w : qAsConst(d->m_watchers)) {
3332 if (w->isFinished()) {
3333 watcher = w;
3334 break;
3335 }
3336 }
3337 if (watcher == nullptr) {
3338 d->m_watchers.push_back(new QFutureWatcher<SKGStringListList>());
3339 watcher = d->m_watchers.at(d->m_watchers.count() - 1);
3340 }
3341
3342 watcher->disconnect();
3343 connect(watcher, &QFutureWatcherBase::finished, this, [ = ] {
3344 auto w = dynamic_cast< QFutureWatcher<SKGStringListList>* >(sender());
3345 iFunction(w->result());
3346 });
3347 }
3348
3349 // Launch in another thread the select
3350 auto future = QtConcurrent::run([ = ] {
3351 auto tmpDatabase = this->getThreadDatabase();
3352 double elapse = SKGServices::getMicroTime();
3353 SKGStringListList listTmp;
3354 SKGServices::executeSelectSqliteOrder(tmpDatabase, iSqlOrder, listTmp);
3355
3356 // Is the execution time too long
3357 if (SKGServices::getMicroTime() - elapse > 50)
3358 {
3359 // Yes => put the result in cache
3360 addSqlResultInCache(iSqlOrder, listTmp);
3361 }
3362 if (!iExecuteInMainThread)
3363 {
3364 iFunction(listTmp);
3365 }
3366 return listTmp;
3367 });
3368 if (watcher != nullptr) {
3369 watcher->setFuture(future);
3370 }
3371 }
3372 }
3373
concurrentExistObjects(const QString & iTable,const QString & iWhereClause,const FuncExist & iFunction,bool iExecuteInMainThread)3374 void SKGDocument::concurrentExistObjects(const QString& iTable, const QString& iWhereClause, const FuncExist& iFunction, bool iExecuteInMainThread)
3375 {
3376 // Execute sqlorder
3377 concurrentExecuteSelectSqliteOrder("SELECT EXISTS(SELECT 1 FROM " % iTable % " WHERE " % (!iWhereClause.isEmpty() ? iWhereClause : QStringLiteral("1=1")) % ')',
3378 [ = ](const SKGStringListList & iResult) {
3379 iFunction(iResult.count() > 0 && (iResult.at(1).at(0) == QStringLiteral("1")));
3380 }, iExecuteInMainThread);
3381 }
3382
getAttributesDescription(const QString & iTable,SKGServices::SKGAttributesList & oResult) const3383 SKGError SKGDocument::getAttributesDescription(const QString& iTable, SKGServices::SKGAttributesList& oResult) const
3384 {
3385 SKGError err;
3386 _SKGTRACEINFUNCRC(10, err)
3387 // initialisation
3388 oResult.clear();
3389
3390 // Search
3391 SKGStringListList temporaryResult;
3392 err = this->executeSelectSqliteOrder("PRAGMA table_info( " % iTable % " );", temporaryResult);
3393 IFOK(err) {
3394 int nblines = temporaryResult.count();
3395 QString realTable = SKGServices::getRealTable(iTable);
3396
3397 for (int i = 1; i < nblines; ++i) { // the first one is ignored because it is the headers
3398 QStringList line = temporaryResult.at(i);
3399
3400 SKGServices::SKGAttributeInfo attribute;
3401 attribute.name = line[1];
3402
3403 QString attname = realTable % '.' % attribute.name;
3404 attribute.display = getDisplay(attname);
3405 if (attribute.display == attname) {
3406 attribute.display = QLatin1String("");
3407 }
3408 attribute.icon = getIcon(attname);
3409 attribute.type = getAttributeType(attribute.name);
3410 attribute.notnull = (line[3] == QStringLiteral("0"));
3411 attribute.defaultvalue = line[4];
3412 oResult.push_back(attribute);
3413 }
3414 }
3415
3416 return err;
3417 }
3418
getAttributesList(const QString & iTable,QStringList & oResult) const3419 SKGError SKGDocument::getAttributesList(const QString& iTable, QStringList& oResult) const
3420 {
3421 SKGError err;
3422 _SKGTRACEINFUNCRC(10, err)
3423 oResult.clear();
3424 SKGServices::SKGAttributesList attDesc;
3425 err = getAttributesDescription(iTable, attDesc);
3426 int nblines = attDesc.count();
3427 for (int i = 0; !err && i < nblines; ++i) {
3428 oResult.push_back(attDesc.at(i).name);
3429 }
3430 return err;
3431 }
3432
getReport() const3433 SKGReport* SKGDocument::getReport() const
3434 {
3435 return new SKGReport(const_cast<SKGDocument*>(this));
3436 }
3437
copyToJson(QString & oDocument) const3438 SKGError SKGDocument::copyToJson(QString& oDocument) const
3439 {
3440 SKGError err;
3441 SKGTRACEINFUNCRC(10, err)
3442 QVariantMap doc;
3443
3444 // Copy the tables
3445 QVariantList list;
3446 QStringList listTables = getMainDatabase()->tables();
3447 int nb = listTables.count();
3448 for (int i = 0; !err && i < nb; ++i) {
3449 const QString& tableName = listTables.at(i);
3450 if (Q_UNLIKELY(!tableName.startsWith(QLatin1String("sqlite_")) && !tableName.startsWith(QLatin1String("vm_")))) {
3451 list.clear();
3452
3453 SKGStringListList listRows;
3454 if (getMainDatabase() == nullptr) {
3455 err = SKGError(ERR_POINTER, i18nc("Error message", "No database defined"));
3456 } else {
3457 err = SKGServices::executeSelectSqliteOrder(*getMainDatabase(), "SELECT * FROM " % tableName, listRows);
3458 }
3459 int nbRows = listRows.count();
3460 if (Q_LIKELY(nbRows)) {
3461 QVariantMap item;
3462 const QStringList& titles = listRows.at(0);
3463 for (int j = 1; !err && j < nbRows; ++j) { // Forget title
3464 const QStringList& values = listRows.at(j);
3465
3466 int nbVals = values.count();
3467 for (int k = 0; k < nbVals; ++k) {
3468 const QString& t = titles.at(k);
3469 SKGServices::AttributeType type = getAttributeType(t);
3470 if (type == SKGServices::ID || type == SKGServices::INTEGER || type == SKGServices::LINK) {
3471 item.insert(t, SKGServices::stringToInt(values.at(k)));
3472 } else if (type == SKGServices::FLOAT) {
3473 item.insert(t, SKGServices::stringToDouble(values.at(k)));
3474 } else if (type == SKGServices::BOOL) {
3475 item.insert(t, values.at(k) == QStringLiteral("Y"));
3476 } else {
3477 item.insert(t, values.at(k));
3478 }
3479 }
3480
3481 list << item;
3482 }
3483 }
3484 doc.insert(tableName, list);
3485 }
3486 }
3487
3488 QJsonDocument serializer = QJsonDocument::fromVariant(doc);
3489 oDocument = serializer.toJson(QJsonDocument::Compact);
3490 return err;
3491 }
3492
3493 Q_DECLARE_METATYPE(sqlite3*) // NOLINT(readability/casting)
3494 Q_DECLARE_OPAQUE_POINTER(sqlite3*) // NOLINT(readability/casting)
3495