Akonadi

querybuilder.cpp
1/*
2 SPDX-FileCopyrightText: 2007-2012 Volker Krause <vkrause@kde.org>
3
4 SPDX-License-Identifier: LGPL-2.0-or-later
5*/
6
7#include "querybuilder.h"
8#include "akonadiserver_debug.h"
9#include "dbexception.h"
10#include "entities.h"
11#include "storage/query.h"
12#include "utils.h"
13#include <memory>
14#include <qsqldatabase.h>
15
16#ifndef QUERYBUILDER_UNITTEST
17#include "storage/datastore.h"
18#include "storage/querycache.h"
19#include "storage/storagedebugger.h"
20#endif
21
22#include "shared/akranges.h"
23
24#include <QElapsedTimer>
25#include <QSqlDriver>
26#include <QSqlError>
27#include <QSqlField>
28#include <QSqlRecord>
29
30using namespace Akonadi::Server;
31using namespace AkRanges;
32
33namespace
34{
35
36DataStore *defaultDataStore()
37{
38#ifdef QUERYBUILDER_UNITTEST
39 return nullptr;
40#else
41 return DataStore::self();
42#endif
43}
44
45} // namespace
46
47static QLatin1StringView compareOperatorToString(Query::CompareOperator op)
48{
49 switch (op) {
50 case Query::Equals:
51 return QLatin1StringView(" = ");
52 case Query::NotEquals:
53 return QLatin1StringView(" <> ");
54 case Query::Is:
55 return QLatin1StringView(" IS ");
56 case Query::IsNot:
57 return QLatin1StringView(" IS NOT ");
58 case Query::Less:
59 return QLatin1StringView(" < ");
60 case Query::LessOrEqual:
61 return QLatin1StringView(" <= ");
62 case Query::Greater:
63 return QLatin1StringView(" > ");
64 case Query::GreaterOrEqual:
65 return QLatin1StringView(" >= ");
66 case Query::In:
67 return QLatin1StringView(" IN ");
68 case Query::NotIn:
69 return QLatin1StringView(" NOT IN ");
70 case Query::Like:
71 return QLatin1StringView(" LIKE ");
72 }
73 Q_ASSERT_X(false, "QueryBuilder::compareOperatorToString()", "Unknown compare operator.");
74 return QLatin1StringView("");
75}
76
77static QLatin1StringView logicOperatorToString(Query::LogicOperator op)
78{
79 switch (op) {
80 case Query::And:
81 return QLatin1StringView(" AND ");
82 case Query::Or:
83 return QLatin1StringView(" OR ");
84 }
85 Q_ASSERT_X(false, "QueryBuilder::logicOperatorToString()", "Unknown logic operator.");
86 return QLatin1StringView("");
87}
88
89static QLatin1StringView sortOrderToString(Query::SortOrder order)
90{
91 switch (order) {
92 case Query::Ascending:
93 return QLatin1StringView(" ASC");
94 case Query::Descending:
95 return QLatin1StringView(" DESC");
96 }
97 Q_ASSERT_X(false, "QueryBuilder::sortOrderToString()", "Unknown sort order.");
98 return QLatin1StringView("");
99}
100
101static void appendJoined(QString *statement, const QStringList &strings, QLatin1StringView glue = QLatin1StringView(", "))
102{
103 for (int i = 0, c = strings.size(); i < c; ++i) {
104 *statement += strings.at(i);
105 if (i + 1 < c) {
106 *statement += glue;
107 }
108 }
109}
110
111QueryBuilder::QueryBuilder(const QString &table, QueryBuilder::QueryType type)
112 : QueryBuilder(defaultDataStore(), table, type)
113{
114}
115
116QueryBuilder::QueryBuilder(DataStore *store, const QString &table, QueryBuilder::QueryType type)
117 : mTable(table)
118#ifndef QUERYBUILDER_UNITTEST
119 , mDataStore(store)
120 , mDatabaseType(DbType::type(store->database()))
121#else
122 , mDatabaseType(DbType::Unknown)
123#endif
124 , mType(type)
125 , mLimit(-1)
126 , mOffset(-1)
127 , mDistinct(false)
128{
129#ifdef QUERYBUILDER_UNITTEST
130 Q_UNUSED(store);
131#endif
132 static const QString defaultIdColumn = QStringLiteral("id");
133 mIdentificationColumn = defaultIdColumn;
134}
135
136QueryBuilder::QueryBuilder(const QSqlQuery &tableQuery, const QString &tableQueryAlias)
137 : QueryBuilder(defaultDataStore(), tableQuery, tableQueryAlias)
138{
139}
140
141QueryBuilder::QueryBuilder(DataStore *store, const QSqlQuery &tableQuery, const QString &tableQueryAlias)
142 : mTable(tableQueryAlias)
143 , mTableSubQuery(std::ref(tableQuery))
144#ifndef QUERYBUILDER_UNITTEST
145 , mDataStore(store)
146 , mDatabaseType(DbType::type(store->database()))
147#else
148 , mDatabaseType(DbType::Unknown)
149#endif
150 , mType(QueryType::Select)
151 , mLimit(-1)
152 , mOffset(-1)
153 , mDistinct(false)
154{
155#ifdef QUERYBUILDER_UNITTEST
156 Q_UNUSED(store);
157#endif
158 static const QString defaultIdColumn = QStringLiteral("id");
159 mIdentificationColumn = defaultIdColumn;
160}
161
163 : mTable(other.mTable)
164 , mTableSubQuery(std::move(other.mTableSubQuery))
165 , mDataStore(other.mDataStore)
166 , mDatabaseType(other.mDatabaseType)
167 , mRootCondition{std::move(other.mRootCondition[0]), std::move(other.mRootCondition[1])}
168 , mQuery(std::move(other.mQuery))
169 , mType(other.mType)
170 , mColumns(std::move(other.mColumns))
171 , mBindValues(std::move(other.mBindValues))
172 , mSortColumns(std::move(other.mSortColumns))
173 , mGroupColumns(std::move(other.mGroupColumns))
174 , mColumnValues(std::move(other.mColumnValues))
175 , mColumnMultiValues(std::move(other.mColumnMultiValues))
176 , mIdentificationColumn(std::move(other.mIdentificationColumn))
177 , mJoinedTables(std::move(other.mJoinedTables))
178 , mJoins(std::move(other.mJoins))
179 , mLimit(other.mLimit)
180 , mOffset(other.mOffset)
181 , mDistinct(other.mDistinct)
182 , mForUpdate(other.mForUpdate)
183{
184 // a moved-from QSqlQuery has null d-pointer, so calling any method on it results
185 // in a crash. As a workaround, we re-initialize the moved-from QSqlQuery to an invalid one.
186 other.mQuery = QSqlQuery();
187}
188
189QueryBuilder &QueryBuilder::operator=(QueryBuilder &&other) noexcept
190{
191 if (this != &other) {
192 mTable = other.mTable;
193 mTableSubQuery = std::move(other.mTableSubQuery);
194 mDataStore = other.mDataStore;
195 mDatabaseType = other.mDatabaseType;
196 mRootCondition[0] = std::move(other.mRootCondition[0]);
197 mRootCondition[1] = std::move(other.mRootCondition[1]);
198 mQuery = std::move(other.mQuery);
199 mType = other.mType;
200 mColumns = std::move(other.mColumns);
201 mBindValues = std::move(other.mBindValues);
202 mSortColumns = std::move(other.mSortColumns);
203 mGroupColumns = std::move(other.mGroupColumns);
204 mColumnValues = std::move(other.mColumnValues);
205 mColumnMultiValues = std::move(other.mColumnMultiValues);
206 mIdentificationColumn = std::move(other.mIdentificationColumn);
207 mJoinedTables = std::move(other.mJoinedTables);
208 mJoins = std::move(other.mJoins);
209 mLimit = other.mLimit;
210 mOffset = other.mOffset;
211 mDistinct = other.mDistinct;
212 mForUpdate = other.mForUpdate;
213
214 other.mQuery = QSqlQuery(); // see the comment in move constructor
215 }
216 return *this;
217}
218
219QueryBuilder::~QueryBuilder()
220{
221 if (mQuery.isActive()) {
222 mQuery.finish();
223#ifndef QUERYBUILDER_UNITTEST
224 // Cache the query now that we won't need it.
225 const auto stmt = mQuery.executedQuery();
226 QueryCache::insert(mDataStore->database(), stmt, std::move(mQuery));
227#endif
228 }
229}
230
232{
233 mDatabaseType = type;
234}
235
236void QueryBuilder::addJoin(JoinType joinType, const QString &table, const Query::Condition &condition)
237{
238 Q_ASSERT((joinType == InnerJoin && (mType == Select || mType == Update)) || (joinType == LeftJoin && mType == Select)
239 || (joinType == LeftOuterJoin && mType == Select));
240
241 if (mJoinedTables.contains(table)) {
242 // InnerJoin is more restrictive than a LeftJoin, hence use that in doubt
243 mJoins[table].first = qMin(joinType, mJoins.value(table).first);
244 mJoins[table].second.addCondition(condition);
245 } else {
246 mJoins[table] = qMakePair(joinType, condition);
247 mJoinedTables << table;
248 }
249}
250
251void QueryBuilder::addJoin(JoinType joinType, const QString &table, const QString &col1, const QString &col2)
252{
253 Query::Condition condition;
254 condition.addColumnCondition(col1, Query::Equals, col2);
255 addJoin(joinType, table, condition);
256}
257
259{
260 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
261 mRootCondition[type].addValueCondition(column, op, value);
262}
263
265{
266 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
267 mRootCondition[type].addValueCondition(column, op, value);
268}
269
271{
272 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
273 mRootCondition[type].addValueCondition(column, op, value);
274}
275
277{
278 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
279 mRootCondition[type].addColumnCondition(column, op, column2);
280}
281
282void QueryBuilder::sqliteAdaptUpdateJoin(Query::Condition &condition)
283{
284 // FIXME: This does not cover all cases by far. It however can handle most
285 // (probably all) of the update-join queries we do in Akonadi and convert them
286 // properly into a SQLite-compatible query. Better than nothing ;-)
287
288 if (!condition.mSubConditions.isEmpty()) {
289 for (int i = condition.mSubConditions.count() - 1; i >= 0; --i) {
290 sqliteAdaptUpdateJoin(condition.mSubConditions[i]);
291 }
292 return;
293 }
294
295 QString table;
296 if (condition.mColumn.contains(QLatin1Char('.'))) {
297 table = condition.mColumn.left(condition.mColumn.indexOf(QLatin1Char('.')));
298 } else {
299 return;
300 }
301
302 if (!mJoinedTables.contains(table)) {
303 return;
304 }
305
306 const auto &[type, joinCondition] = mJoins.value(table);
307
308 QueryBuilder qb(table, Select);
309 qb.addColumn(condition.mColumn);
310 qb.addCondition(joinCondition);
311
312 // Convert the subquery to string
313 condition.mColumn.reserve(1024);
314 condition.mColumn.resize(0);
315 condition.mColumn += QLatin1StringView("( ");
316 qb.buildQuery(&condition.mColumn);
317 condition.mColumn += QLatin1StringView(" )");
318}
319
320void QueryBuilder::buildInsertColumns(QString *statement)
321{
322 const auto &vals = mColumnMultiValues.empty() ? mColumnValues : mColumnMultiValues;
323 *statement += u'(';
324 for (qsizetype i = 0; i < vals.size(); ++i) {
325 *statement += vals.at(i).first;
326 if (i + 1 < vals.size()) {
327 *statement += u", ";
328 }
329 }
330 *statement += u')';
331}
332
333void QueryBuilder::buildInsertValues(QString *statement)
334{
335 if (mColumnMultiValues.empty()) {
336 *statement += u'(';
337 for (int col = 0, columnCount = mColumnValues.size(); col < columnCount; ++col) {
338 bindValue(statement, mColumnValues.at(col).second);
339 if (col + 1 < columnCount) {
340 *statement += u", ";
341 }
342 }
343 *statement += u')';
344 } else {
345 const auto rows = mColumnMultiValues.front().second.toList().size();
346 for (qsizetype row = 0; row < rows; ++row) {
347 *statement += u'(';
348 for (int col = 0, columnCount = mColumnMultiValues.size(); col < columnCount; ++col) {
349 const auto &[_, values] = mColumnMultiValues.at(col);
350 // FIXME: We perform tons of throw-away .toList() conversions here: figure out a better system
351 // to store the multi-values or transpose the data before building the query.
352 bindValue(statement, values.toList().at(row));
353 if (col + 1 < columnCount) {
354 *statement += u", ";
355 }
356 }
357 *statement += u')';
358 if (row + 1 < rows) {
359 *statement += u", ";
360 }
361 }
362 }
363}
364
365void QueryBuilder::buildQuery(QString *statement)
366{
367 // we add the ON conditions of Inner Joins in a Update query here
368 // but don't want to change the mRootCondition on each exec().
369 Query::Condition whereCondition = mRootCondition[WhereCondition];
370
371 switch (mType) {
372 case Select:
373 // Enable forward-only on all SELECT queries, since we never need to
374 // iterate backwards. This is a memory optimization.
375 mQuery.setForwardOnly(true);
376 *statement += QLatin1StringView("SELECT ");
377 if (mDistinct) {
378 *statement += QLatin1StringView("DISTINCT ");
379 }
380 Q_ASSERT_X(mColumns.count() > 0, "QueryBuilder::exec()", "No columns specified");
381 appendJoined(statement, mColumns);
382 *statement += QLatin1StringView(" FROM ");
383 *statement += mTableSubQuery.has_value() ? getTableQuery(*mTableSubQuery, mTable) : mTable;
384 for (const QString &joinedTable : std::as_const(mJoinedTables)) {
385 const auto &[joinType, joinCond] = mJoins.value(joinedTable);
386 switch (joinType) {
387 case LeftJoin:
388 *statement += QLatin1StringView(" LEFT JOIN ");
389 break;
390 case LeftOuterJoin:
391 *statement += QLatin1StringView(" LEFT OUTER JOIN ");
392 break;
393 case InnerJoin:
394 *statement += QLatin1StringView(" INNER JOIN ");
395 break;
396 }
397 *statement += joinedTable;
398 *statement += QLatin1StringView(" ON ");
399 buildWhereCondition(statement, joinCond);
400 }
401 break;
402 case Insert: {
403 *statement += QLatin1StringView("INSERT INTO ");
404 *statement += mTable + u" ";
405 buildInsertColumns(statement);
406 *statement += u" VALUES ";
407 buildInsertValues(statement);
408 if (mDatabaseType == DbType::PostgreSQL && !mIdentificationColumn.isEmpty()) {
409 *statement += QLatin1StringView(" RETURNING ") + mIdentificationColumn;
410 }
411 break;
412 }
413 case Update: {
414 // put the ON condition into the WHERE part of the UPDATE query
415 if (mDatabaseType != DbType::Sqlite) {
416 for (const QString &table : std::as_const(mJoinedTables)) {
417 const auto &[joinType, joinCond] = mJoins.value(table);
418 Q_ASSERT(joinType == InnerJoin);
419 whereCondition.addCondition(joinCond);
420 }
421 } else {
422 // Note: this will modify the whereCondition
423 sqliteAdaptUpdateJoin(whereCondition);
424 }
425
426 *statement += QLatin1StringView("UPDATE ");
427 *statement += mTable;
428
429 if (mDatabaseType == DbType::MySQL && !mJoinedTables.isEmpty()) {
430 // for mysql we list all tables directly
431 *statement += QLatin1StringView(", ");
432 appendJoined(statement, mJoinedTables);
433 }
434
435 *statement += QLatin1StringView(" SET ");
436 Q_ASSERT_X(mColumnValues.count() >= 1, "QueryBuilder::exec()", "At least one column needs to be changed");
437 for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
438 const auto &[column, value] = mColumnValues.at(i);
439 *statement += column;
440 *statement += QLatin1StringView(" = ");
441 bindValue(statement, value);
442 if (i + 1 < c) {
443 *statement += QLatin1StringView(", ");
444 }
445 }
446
447 if (mDatabaseType == DbType::PostgreSQL && !mJoinedTables.isEmpty()) {
448 // PSQL have this syntax
449 // FROM t1 JOIN t2 JOIN ...
450 *statement += QLatin1StringView(" FROM ");
451 appendJoined(statement, mJoinedTables, QLatin1StringView(" JOIN "));
452 }
453 break;
454 }
455 case Delete:
456 *statement += QLatin1StringView("DELETE FROM ");
457 *statement += mTable;
458 break;
459 default:
460 Q_ASSERT_X(false, "QueryBuilder::exec()", "Unknown enum value");
461 }
462
463 if (!whereCondition.isEmpty()) {
464 *statement += QLatin1StringView(" WHERE ");
465 buildWhereCondition(statement, whereCondition);
466 }
467
468 if (!mGroupColumns.isEmpty()) {
469 *statement += QLatin1StringView(" GROUP BY ");
470 appendJoined(statement, mGroupColumns);
471 }
472
473 if (!mRootCondition[HavingCondition].isEmpty()) {
474 *statement += QLatin1StringView(" HAVING ");
475 buildWhereCondition(statement, mRootCondition[HavingCondition]);
476 }
477
478 if (!mSortColumns.isEmpty()) {
479 Q_ASSERT_X(mType == Select, "QueryBuilder::exec()", "Order statements are only valid for SELECT queries");
480 *statement += QLatin1StringView(" ORDER BY ");
481 for (int i = 0, c = mSortColumns.size(); i < c; ++i) {
482 const auto &[column, order] = mSortColumns.at(i);
483 *statement += column;
484 *statement += sortOrderToString(order);
485 if (i + 1 < c) {
486 *statement += QLatin1StringView(", ");
487 }
488 }
489 }
490
491 if (mLimit > 0) {
492 *statement += QLatin1StringView(" LIMIT ") + QString::number(mLimit);
493 if (mOffset > 0) {
494 *statement += QLatin1StringView(" OFFSET ") + QString::number(mOffset);
495 }
496 }
497
498 if (mType == Select && mForUpdate) {
499 if (mDatabaseType == DbType::Sqlite) {
500 // SQLite does not support SELECT ... FOR UPDATE syntax, because it does
501 // table-level locking
502 } else {
503 *statement += QLatin1StringView(" FOR UPDATE");
504 }
505 }
506}
507
509{
510 QString statement;
511 statement.reserve(1024);
512 buildQuery(&statement);
513
514#ifndef QUERYBUILDER_UNITTEST
515 auto query = QueryCache::query(statement);
516 if (query) {
517 mQuery = std::move(*query);
518 } else {
519 mQuery = QSqlQuery(mDataStore->database());
520 if (!mQuery.prepare(statement)) {
521 qCCritical(AKONADISERVER_LOG) << "DATABASE ERROR while PREPARING QUERY:";
522 qCCritical(AKONADISERVER_LOG) << " Error code:" << mQuery.lastError().nativeErrorCode();
523 qCCritical(AKONADISERVER_LOG) << " DB error: " << mQuery.lastError().databaseText();
524 qCCritical(AKONADISERVER_LOG) << " Error text:" << mQuery.lastError().text();
525 qCCritical(AKONADISERVER_LOG) << " Query:" << statement;
526 return false;
527 }
528 }
529
530 // too heavy debug info but worths to have from time to time
531 // qCDebug(AKONADISERVER_LOG) << "Executing query" << statement;
532 bool isBatch = false;
533 for (int i = 0; i < mBindValues.count(); ++i) {
534 mQuery.bindValue(QLatin1Char(':') + QString::number(i), mBindValues[i]);
535 if (!isBatch && static_cast<QMetaType::Type>(mBindValues[i].typeId()) == qMetaTypeId<QVariantList>()) {
536 isBatch = true;
537 }
538 // qCDebug(AKONADISERVER_LOG) << QString::fromLatin1( ":%1" ).arg( i ) << mBindValues[i];
539 }
540
541 bool ret;
542
543 if (StorageDebugger::instance()->isSQLDebuggingEnabled()) {
545 t.start();
546 if (isBatch) {
547 ret = mQuery.execBatch();
548 } else {
549 ret = mQuery.exec();
550 }
551 StorageDebugger::instance()->queryExecuted(reinterpret_cast<qint64>(mDataStore), mQuery, t.elapsed());
552 } else {
553 StorageDebugger::instance()->incSequence();
554 if (isBatch) {
555 ret = mQuery.execBatch();
556 } else {
557 ret = mQuery.exec();
558 }
559 }
560
561 if (!ret) {
562 bool needsRetry = false;
563 // Handle transaction deadlocks and timeouts by attempting to replay the transaction.
564 if (mDatabaseType == DbType::PostgreSQL) {
565 const QString dbError = mQuery.lastError().databaseText();
566 if (dbError.contains(QLatin1StringView("40P01" /* deadlock_detected */))) {
567 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction";
568 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
569 needsRetry = true;
570 }
571 } else if (mDatabaseType == DbType::MySQL) {
572 const QString lastErrorStr = mQuery.lastError().nativeErrorCode();
573 const int error = lastErrorStr.isEmpty() ? -1 : lastErrorStr.toInt();
574 if (error == 1213 /* ER_LOCK_DEADLOCK */) {
575 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction";
576 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
577 needsRetry = true;
578 } else if (error == 1205 /* ER_LOCK_WAIT_TIMEOUT */) {
579 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction timeout, retrying transaction";
580 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
581 // Not sure retrying helps, maybe error is good enough.... but doesn't hurt to retry a few times before giving up.
582 needsRetry = true;
583 }
584 } else if (mDatabaseType == DbType::Sqlite) {
585 const QString lastErrorStr = mQuery.lastError().nativeErrorCode();
586 const int error = lastErrorStr.isEmpty() ? -1 : lastErrorStr.toInt();
587 if (error == 6 /* SQLITE_LOCKED */) {
588 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction";
589 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
590 mDataStore->doRollback();
591 needsRetry = true;
592 } else if (error == 5 /* SQLITE_BUSY */) {
593 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction timeout, retrying transaction";
594 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
595 mDataStore->doRollback();
596 needsRetry = true;
597 }
598 }
599
600 if (needsRetry) {
601 mDataStore->transactionKilledByDB();
602 throw DbDeadlockException(mQuery);
603 }
604
605 qCCritical(AKONADISERVER_LOG) << "DATABASE ERROR:";
606 qCCritical(AKONADISERVER_LOG) << " Error code:" << mQuery.lastError().nativeErrorCode();
607 qCCritical(AKONADISERVER_LOG) << " DB error: " << mQuery.lastError().databaseText();
608 qCCritical(AKONADISERVER_LOG) << " Error text:" << mQuery.lastError().text();
609 qCCritical(AKONADISERVER_LOG) << " Values:" << mQuery.boundValues();
610 qCCritical(AKONADISERVER_LOG) << " Query:" << statement;
611 return false;
612 }
613#else
614 mStatement = statement;
615#endif
616 return true;
617}
618
620{
621 mColumns << cols;
622}
623
625{
626 mColumns << col;
627}
628
629void QueryBuilder::addColumn(const Query::Case &caseStmt)
630{
632 buildCaseStatement(&query, caseStmt);
633 mColumns.append(query);
634}
635
636void QueryBuilder::addAggregation(const QString &col, const QString &aggregate)
637{
638 mColumns.append(aggregate + QLatin1Char('(') + col + QLatin1Char(')'));
639}
640
641void QueryBuilder::addAggregation(const Query::Case &caseStmt, const QString &aggregate)
642{
643 QString query(aggregate + QLatin1Char('('));
644 buildCaseStatement(&query, caseStmt);
645 query += QLatin1Char(')');
646
647 mColumns.append(query);
648}
649
650void QueryBuilder::bindValue(QString *query, const QVariant &value)
651{
652 if (value.metaType().id() == qMetaTypeId<QDateTime>()) {
653 mBindValues.emplace_back(Utils::dateTimeToVariant(value.toDateTime(), mDataStore));
654 } else {
655 mBindValues.emplace_back(value);
656 }
657 *query += QLatin1Char(':') + QString::number(mBindValues.count() - 1);
658}
659
660namespace
661{
662
663bool isType(const QVariant &value, QByteArrayView typeName)
664{
665 if (value.typeId() >= QMetaType::User) {
666 // QVariant will do anything to convert its content into a QVariantList,
667 // so we can't rely on QVariant::canConvert<QVariantList>() here.
668 // The only reliable solution is this hack
669 const char *name = value.typeName();
670 if (qstrncmp(name, typeName.data(), typeName.size()) == 0) {
671 return true;
672 }
673 }
674
675 return false;
676}
677
678bool isList(const QVariant &value)
679{
680 if (value.typeId() == qMetaTypeId<QVariantList>() || value.typeId() == qMetaTypeId<QStringList>() || value.typeId() == qMetaTypeId<QByteArrayList>()) {
681 return true;
682 }
683
684 return isType(value, "QList<");
685}
686
687bool isSet(const QVariant &value)
688{
689 return isType(value, "QSet<qlonglong>");
690}
691
692} // namespace
693
694void QueryBuilder::buildWhereCondition(QString *query, const Query::Condition &cond)
695{
696 constexpr auto buildWhereInContainerCondition = [](QueryBuilder *self, QString *query, const auto &values) {
697 *query += u"( ";
698 if (values.empty()) {
699 qCWarning(AKONADISERVER_LOG) << "Empty list given for IN condition.";
700 }
701 for (const auto &[i, entry] : values | Views::enumerate()) {
702 if (i > 0) {
703 *query += QLatin1StringView(", ");
704 }
705 self->bindValue(query, entry);
706 }
707 *query += u" )";
708 };
709
710 if (!cond.isEmpty()) {
711 *query += QLatin1StringView("( ");
712 const QLatin1StringView glue = logicOperatorToString(cond.mCombineOp);
713 const Query::Condition::List &subConditions = cond.subConditions();
714 for (qsizetype i = 0, c = subConditions.size(); i < c; ++i) {
715 buildWhereCondition(query, subConditions.at(i));
716 if (i + 1 < c) {
717 *query += glue;
718 }
719 }
720 *query += QLatin1StringView(" )");
721 } else {
722 *query += cond.mColumn;
723 *query += compareOperatorToString(cond.mCompareOp);
724 if (!cond.mComparedColumn.isEmpty()) {
725 *query += cond.mComparedColumn;
726 } else if (!cond.mComparedValue.isValid()) {
727 *query += u"NULL";
728 } else if (isList(cond.mComparedValue)) {
729 // FIXME: Ideally we would explicitly convert to QList<T> instead of doing the potentially costly
730 // conversion to QVariantList, but that would require much larger refactor...
731 buildWhereInContainerCondition(this, query, cond.mComparedValue.toList());
732 } else if (isSet(cond.mComparedValue)) {
733 buildWhereInContainerCondition(this, query, cond.mComparedValue.value<QSet<qint64>>());
734 } else {
735 bindValue(query, cond.mComparedValue);
736 }
737 }
738}
739
740void QueryBuilder::buildCaseStatement(QString *query, const Query::Case &caseStmt)
741{
742 *query += QLatin1StringView("CASE ");
743 for (const auto &whenThen : caseStmt.mWhenThen) {
744 *query += QLatin1StringView("WHEN ");
745 buildWhereCondition(query, whenThen.first); // When
746 *query += QLatin1StringView(" THEN ") + whenThen.second; // then
747 }
748 if (!caseStmt.mElse.isEmpty()) {
749 *query += QLatin1StringView(" ELSE ") + caseStmt.mElse;
750 }
751 *query += QLatin1StringView(" END");
752}
753
755{
756 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
757 mRootCondition[type].setSubQueryMode(op);
758}
759
761{
762 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
763 mRootCondition[type].addCondition(condition);
764}
765
767{
768 mSortColumns << qMakePair(column, order);
769}
770
772{
773 Q_ASSERT(mType == Select);
774 mGroupColumns << column;
775}
776
778{
779 Q_ASSERT(mType == Select);
780 mGroupColumns += columns;
781}
782
783void QueryBuilder::setColumnValue(const QString &column, const QVariant &value)
784{
785 mColumnMultiValues.clear();
786 mColumnValues.push_back(qMakePair(column, value));
787}
788
789void QueryBuilder::setColumnValues(const QString &column, const QVariant &values)
790{
791 Q_ASSERT(mType == Insert);
792
793 mColumnValues.clear();
794 mColumnMultiValues.push_back(qMakePair(column, values));
795}
796
797void QueryBuilder::setDistinct(bool distinct)
798{
799 mDistinct = distinct;
800}
801
802void QueryBuilder::setLimit(int limit, int offset)
803{
804 mLimit = limit;
805 mOffset = offset;
806}
807
809{
810 mIdentificationColumn = column;
811}
812
814{
815 if (mDatabaseType == DbType::PostgreSQL) {
816 query().next();
817 if (mIdentificationColumn.isEmpty()) {
818 return 0; // FIXME: Does this make sense?
819 }
820 return query().record().value(mIdentificationColumn).toLongLong();
821 } else {
822 const QVariant v = query().lastInsertId();
823 if (!v.isValid()) {
824 return -1;
825 }
826 bool ok;
827 const qint64 insertId = v.toLongLong(&ok);
828 if (!ok) {
829 return -1;
830 }
831 return insertId;
832 }
833 return -1;
834}
835
836void QueryBuilder::setForUpdate(bool forUpdate)
837{
838 mForUpdate = forUpdate;
839}
840
842{
843 return mTable;
844}
845
847{
848 return mTable + QLatin1Char('.') + column;
849}
850
851QString QueryBuilder::getTableQuery(const QSqlQuery &query, const QString &alias)
852{
853 Q_ASSERT_X(query.isValid() && query.isSelect(), "QueryBuilder::getTableQuery", "Table subquery use only for valid SELECT queries");
854
855 QString tableQuery = query.lastQuery();
856 if (tableQuery.isEmpty()) {
857 qCWarning(AKONADISERVER_LOG) << "Table subquery is empty";
858 return tableQuery;
859 }
860
861 tableQuery.prepend(QLatin1StringView("( "));
862
863 const QList<QVariant> boundValues = query.boundValues();
864 for (qsizetype pos = boundValues.size() - 1; pos >= 0; --pos) {
865 const QVariant &value = boundValues.at(pos);
866 const QString key(QLatin1Char(':') + QString::number(pos));
867 QSqlField field(QLatin1StringView(""), value.metaType());
868 if (value.isNull()) {
869 field.clear();
870 } else {
871 field.setValue(value);
872 }
873
874 const QString formattedValue = query.driver()->formatValue(field);
875 tableQuery.replace(key, formattedValue);
876 }
877
878 tableQuery.append(QLatin1StringView(" ) AS %1").arg(alias));
879
880 return tableQuery;
881}
This class handles all the database access.
Definition datastore.h:95
static DataStore * self()
Per thread singleton.
QSqlDatabase database()
Returns the QSqlDatabase object.
Helper class to construct arbitrary SQL queries.
void addValueCondition(const QString &column, Query::CompareOperator op, const QVariant &value, ConditionType type=WhereCondition)
Add a WHERE or HAVING condition which compares a column with a given value.
void setDatabaseType(DbType::Type type)
Sets the database which should execute the query.
void addGroupColumn(const QString &column)
Add a GROUP BY column.
void addSortColumn(const QString &column, Query::SortOrder order=Query::Ascending)
Add sort column.
QString getTableWithColumn(const QString &column) const
Returns concatenated table name with column name.
void addJoin(JoinType joinType, const QString &table, const Query::Condition &condition)
Join a table to the query.
bool exec()
Executes the query, returns true on success.
void addColumns(const QStringList &cols)
Adds the given columns to a select query.
void addCondition(const Query::Condition &condition, ConditionType type=WhereCondition)
Add a WHERE condition.
void setColumnValue(const QString &column, const QVariant &value)
Sets a column to the given value (only valid for INSERT and UPDATE queries).
void setColumnValues(const QString &column, const QList< T > &values)
Set column to given values (only valid for INSERT query).
void setDistinct(bool distinct)
Specify whether duplicates should be included in the result.
void addColumnCondition(const QString &column, Query::CompareOperator op, const QString &column2, ConditionType type=WhereCondition)
Add a WHERE or HAVING condition which compares a column with another column.
void addGroupColumns(const QStringList &columns)
Add list of columns to GROUP BY.
void setForUpdate(bool forUpdate=true)
Indicate to the database to acquire an exclusive lock on the rows already during SELECT statement.
ConditionType
Defines the place at which a condition should be evaluated.
@ HavingCondition
add condition to HAVING part of the query NOTE: only supported for SELECT queries
@ WhereCondition
add condition to WHERE part of the query
QSqlQuery & query()
Returns the query, only valid after exec().
void setIdentificationColumn(const QString &column)
Sets the column used for identification in an INSERT statement.
qint64 insertId()
Returns the ID of the newly created record (only valid for INSERT queries)
void setSubQueryMode(Query::LogicOperator op, ConditionType type=WhereCondition)
Define how WHERE or HAVING conditions are combined.
void addAggregation(const QString &col, const QString &aggregate)
Adds an aggregation statement.
void addColumn(const QString &col)
Adds the given column to a select query.
QueryBuilder(const QString &table, QueryType type=Select)
Creates a new query builder.
JoinType
When the same table gets joined as both, Inner- and LeftJoin, it will be merged into a single InnerJo...
@ LeftOuterJoin
NOTE: only supported for SELECT queries.
@ InnerJoin
NOTE: only supported for UPDATE and SELECT queries.
@ LeftJoin
NOTE: only supported for SELECT queries.
QString getTable() const
Returns the name of the main table or subquery.
void setLimit(int limit, int offset=-1)
Limits the amount of retrieved rows.
Represents a WHERE condition tree.
Definition query.h:62
void addColumnCondition(const QString &column, CompareOperator op, const QString &column2)
Add a WHERE condition which compares a column with another column.
Definition query.cpp:22
Condition::List subConditions() const
Returns the list of sub-conditions.
Definition query.cpp:44
void setSubQueryMode(LogicOperator op)
Set how sub-conditions should be combined, default is And.
Definition query.cpp:49
void addValueCondition(const QString &column, CompareOperator op, const QVariant &value)
Add a WHERE condition which compares a column with a given value.
Definition query.cpp:12
bool isEmpty() const
Returns if there are sub conditions.
Definition query.cpp:39
void addCondition(const Condition &condition)
Add a WHERE condition.
Definition query.cpp:54
Type
Supported database types.
Definition dbtype.h:19
void insert(const QSqlDatabase &db, const QString &queryStatement, QSqlQuery query)
Insert query into the cache for queryStatement.
std::optional< QSqlQuery > query(const QString &queryStatement)
Return a cached QSqlQuery for given queryStatement.
CompareOperator
Compare operators to be used in query conditions.
Definition query.h:28
SortOrder
Sort orders.
Definition query.h:53
LogicOperator
Logic operations used to combine multiple query conditions.
Definition query.h:45
QString name(GameStandardAction id)
VehicleSection::Type type(QStringView coachNumber, QStringView coachClassification)
KOSM_EXPORT const char * typeName(Type type)
qint64 elapsed() const const
void append(QList< T > &&value)
const_reference at(qsizetype i) const const
void clear()
qsizetype count() const const
reference emplace_back(Args &&... args)
bool empty() const const
reference front()
bool isEmpty() const const
void push_back(parameter_type value)
qsizetype size() const const
int id() const const
virtual QString formatValue(const QSqlField &field, bool trimStrings) const const
QString databaseText() const const
QString nativeErrorCode() const const
QString text() const const
void bindValue(const QString &placeholder, const QVariant &val, QSql::ParamType paramType)
QVariantList boundValues() const const
const QSqlDriver * driver() const const
bool exec()
bool execBatch(BatchExecutionMode mode)
QString executedQuery() const const
void finish()
bool first()
bool isActive() const const
bool isSelect() const const
bool isValid() const const
QSqlError lastError() const const
QVariant lastInsertId() const const
QString lastQuery() const const
bool next()
bool prepare(const QString &query)
QSqlRecord record() const const
void setForwardOnly(bool forward)
QVariant value(const QString &name) const const
QString & append(QChar ch)
const QChar at(qsizetype position) const const
bool contains(QChar ch, Qt::CaseSensitivity cs) const const
qsizetype indexOf(QChar ch, qsizetype from, Qt::CaseSensitivity cs) const const
bool isEmpty() const const
QString left(qsizetype n) const const
QString number(double n, char format, int precision)
QString & prepend(QChar ch)
QString & replace(QChar before, QChar after, Qt::CaseSensitivity cs)
void reserve(qsizetype size)
void resize(qsizetype newSize, QChar fillChar)
int toInt(bool *ok, int base) const const
bool contains(QLatin1StringView str, Qt::CaseSensitivity cs) const const
bool isNull() const const
bool isValid() const const
QMetaType metaType() const const
QDateTime toDateTime() const const
QList< QVariant > toList() const const
qlonglong toLongLong(bool *ok) const const
int typeId() const const
const char * typeName() const const
T value() const const
This file is part of the KDE documentation.
Documentation copyright © 1996-2024 The KDE developers.
Generated on Mon Nov 4 2024 16:31:59 by doxygen 1.12.0 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.