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
14#ifndef QUERYBUILDER_UNITTEST
15#include "storage/datastore.h"
16#include "storage/querycache.h"
17#include "storage/storagedebugger.h"
18#endif
19
20#include "shared/akranges.h"
21
22#include <QElapsedTimer>
23#include <QSqlError>
24#include <QSqlRecord>
25#include <QSqlField>
26#include <QSqlDriver>
27
28using namespace Akonadi::Server;
29using namespace AkRanges;
30
31namespace
32{
33
34DataStore *defaultDataStore()
35{
36#ifdef QUERYBUILDER_UNITTEST
37 return nullptr;
38#else
39 return DataStore::self();
40#endif
41}
42
43} // namespace
44
45static QLatin1StringView compareOperatorToString(Query::CompareOperator op)
46{
47 switch (op) {
48 case Query::Equals:
49 return QLatin1StringView(" = ");
50 case Query::NotEquals:
51 return QLatin1StringView(" <> ");
52 case Query::Is:
53 return QLatin1StringView(" IS ");
54 case Query::IsNot:
55 return QLatin1StringView(" IS NOT ");
56 case Query::Less:
57 return QLatin1StringView(" < ");
58 case Query::LessOrEqual:
59 return QLatin1StringView(" <= ");
60 case Query::Greater:
61 return QLatin1StringView(" > ");
62 case Query::GreaterOrEqual:
63 return QLatin1StringView(" >= ");
64 case Query::In:
65 return QLatin1StringView(" IN ");
66 case Query::NotIn:
67 return QLatin1StringView(" NOT IN ");
68 case Query::Like:
69 return QLatin1StringView(" LIKE ");
70 }
71 Q_ASSERT_X(false, "QueryBuilder::compareOperatorToString()", "Unknown compare operator.");
72 return QLatin1StringView("");
73}
74
75static QLatin1StringView logicOperatorToString(Query::LogicOperator op)
76{
77 switch (op) {
78 case Query::And:
79 return QLatin1StringView(" AND ");
80 case Query::Or:
81 return QLatin1StringView(" OR ");
82 }
83 Q_ASSERT_X(false, "QueryBuilder::logicOperatorToString()", "Unknown logic operator.");
84 return QLatin1StringView("");
85}
86
87static QLatin1StringView sortOrderToString(Query::SortOrder order)
88{
89 switch (order) {
90 case Query::Ascending:
91 return QLatin1StringView(" ASC");
92 case Query::Descending:
93 return QLatin1StringView(" DESC");
94 }
95 Q_ASSERT_X(false, "QueryBuilder::sortOrderToString()", "Unknown sort order.");
96 return QLatin1StringView("");
97}
98
99static void appendJoined(QString *statement, const QStringList &strings, QLatin1StringView glue = QLatin1StringView(", "))
100{
101 for (int i = 0, c = strings.size(); i < c; ++i) {
102 *statement += strings.at(i);
103 if (i + 1 < c) {
104 *statement += glue;
105 }
106 }
107}
108
109QueryBuilder::QueryBuilder(const QString &table, QueryBuilder::QueryType type)
110 : QueryBuilder(defaultDataStore(), table, type)
111{
112}
113
114QueryBuilder::QueryBuilder(DataStore *store, const QString &table, QueryBuilder::QueryType type)
115 : mTable(table)
116#ifndef QUERYBUILDER_UNITTEST
117 , mDataStore(store)
118 , mDatabaseType(DbType::type(store->database()))
119 , mQuery(store->database())
120#else
121 , mDatabaseType(DbType::Unknown)
122#endif
123 , mType(type)
124 , mLimit(-1)
125 , mOffset(-1)
126 , mDistinct(false)
127{
128 static const QString defaultIdColumn = QStringLiteral("id");
129 mIdentificationColumn = defaultIdColumn;
130}
131
132QueryBuilder::QueryBuilder(const QSqlQuery &tableQuery, const QString &tableQueryAlias)
133 : QueryBuilder(defaultDataStore(), tableQuery, tableQueryAlias)
134{
135}
136
137QueryBuilder::QueryBuilder(DataStore *store, const QSqlQuery &tableQuery, const QString &tableQueryAlias)
138 : mTable(tableQueryAlias)
139 , mTableSubQuery(tableQuery)
140#ifndef QUERYBUILDER_UNITTEST
141 , mDataStore(store)
142 , mDatabaseType(DbType::type(store->database()))
143 , mQuery(store->database())
144#else
145 , mDatabaseType(DbType::Unknown)
146#endif
147 , mType(QueryType::Select)
148 , mLimit(-1)
149 , mOffset(-1)
150 , mDistinct(false)
151{
152 static const QString defaultIdColumn = QStringLiteral("id");
153 mIdentificationColumn = defaultIdColumn;
154}
155
157{
158 mDatabaseType = type;
159}
160
161void QueryBuilder::addJoin(JoinType joinType, const QString &table, const Query::Condition &condition)
162{
163 Q_ASSERT((joinType == InnerJoin && (mType == Select || mType == Update)) || (joinType == LeftJoin && mType == Select));
164
165 if (mJoinedTables.contains(table)) {
166 // InnerJoin is more restrictive than a LeftJoin, hence use that in doubt
167 mJoins[table].first = qMin(joinType, mJoins.value(table).first);
168 mJoins[table].second.addCondition(condition);
169 } else {
170 mJoins[table] = qMakePair(joinType, condition);
171 mJoinedTables << table;
172 }
173}
174
175void QueryBuilder::addJoin(JoinType joinType, const QString &table, const QString &col1, const QString &col2)
176{
177 Query::Condition condition;
178 condition.addColumnCondition(col1, Query::Equals, col2);
179 addJoin(joinType, table, condition);
180}
181
183{
184 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
185 mRootCondition[type].addValueCondition(column, op, value);
186}
187
189{
190 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
191 mRootCondition[type].addValueCondition(column, op, value);
192}
193
195{
196 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
197 mRootCondition[type].addColumnCondition(column, op, column2);
198}
199
201{
202 return mQuery;
203}
204
205void QueryBuilder::sqliteAdaptUpdateJoin(Query::Condition &condition)
206{
207 // FIXME: This does not cover all cases by far. It however can handle most
208 // (probably all) of the update-join queries we do in Akonadi and convert them
209 // properly into a SQLite-compatible query. Better than nothing ;-)
210
211 if (!condition.mSubConditions.isEmpty()) {
212 for (int i = condition.mSubConditions.count() - 1; i >= 0; --i) {
213 sqliteAdaptUpdateJoin(condition.mSubConditions[i]);
214 }
215 return;
216 }
217
218 QString table;
219 if (condition.mColumn.contains(QLatin1Char('.'))) {
220 table = condition.mColumn.left(condition.mColumn.indexOf(QLatin1Char('.')));
221 } else {
222 return;
223 }
224
225 if (!mJoinedTables.contains(table)) {
226 return;
227 }
228
229 const auto &[type, joinCondition] = mJoins.value(table);
230
231 QueryBuilder qb(table, Select);
232 qb.addColumn(condition.mColumn);
233 qb.addCondition(joinCondition);
234
235 // Convert the subquery to string
236 condition.mColumn.reserve(1024);
237 condition.mColumn.resize(0);
238 condition.mColumn += QLatin1StringView("( ");
239 qb.buildQuery(&condition.mColumn);
240 condition.mColumn += QLatin1StringView(" )");
241}
242
243void QueryBuilder::buildQuery(QString *statement)
244{
245 // we add the ON conditions of Inner Joins in a Update query here
246 // but don't want to change the mRootCondition on each exec().
247 Query::Condition whereCondition = mRootCondition[WhereCondition];
248
249 switch (mType) {
250 case Select:
251 // Enable forward-only on all SELECT queries, since we never need to
252 // iterate backwards. This is a memory optimization.
253 mQuery.setForwardOnly(true);
254 *statement += QLatin1StringView("SELECT ");
255 if (mDistinct) {
256 *statement += QLatin1StringView("DISTINCT ");
257 }
258 Q_ASSERT_X(mColumns.count() > 0, "QueryBuilder::exec()", "No columns specified");
259 appendJoined(statement, mColumns);
260 *statement += QLatin1StringView(" FROM ");
261 *statement += mTableSubQuery.isValid()
262 ? getTableQuery(mTableSubQuery, mTable) : mTable;
263 for (const QString &joinedTable : std::as_const(mJoinedTables)) {
264 const auto &[joinType, joinCond] = mJoins.value(joinedTable);
265 switch (joinType) {
266 case LeftJoin:
267 *statement += QLatin1StringView(" LEFT JOIN ");
268 break;
269 case InnerJoin:
270 *statement += QLatin1StringView(" INNER JOIN ");
271 break;
272 }
273 *statement += joinedTable;
274 *statement += QLatin1StringView(" ON ");
275 buildWhereCondition(statement, joinCond);
276 }
277 break;
278 case Insert: {
279 *statement += QLatin1StringView("INSERT INTO ");
280 *statement += mTable;
281 *statement += QLatin1StringView(" (");
282 for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
283 *statement += mColumnValues.at(i).first;
284 if (i + 1 < c) {
285 *statement += QLatin1StringView(", ");
286 }
287 }
288 *statement += QLatin1StringView(") VALUES (");
289 for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
290 bindValue(statement, mColumnValues.at(i).second);
291 if (i + 1 < c) {
292 *statement += QLatin1StringView(", ");
293 }
294 }
295 *statement += QLatin1Char(')');
296 if (mDatabaseType == DbType::PostgreSQL && !mIdentificationColumn.isEmpty()) {
297 *statement += QLatin1StringView(" RETURNING ") + mIdentificationColumn;
298 }
299 break;
300 }
301 case Update: {
302 // put the ON condition into the WHERE part of the UPDATE query
303 if (mDatabaseType != DbType::Sqlite) {
304 for (const QString &table : std::as_const(mJoinedTables)) {
305 const auto &[joinType, joinCond] = mJoins.value(table);
306 Q_ASSERT(joinType == InnerJoin);
307 whereCondition.addCondition(joinCond);
308 }
309 } else {
310 // Note: this will modify the whereCondition
311 sqliteAdaptUpdateJoin(whereCondition);
312 }
313
314 *statement += QLatin1StringView("UPDATE ");
315 *statement += mTable;
316
317 if (mDatabaseType == DbType::MySQL && !mJoinedTables.isEmpty()) {
318 // for mysql we list all tables directly
319 *statement += QLatin1StringView(", ");
320 appendJoined(statement, mJoinedTables);
321 }
322
323 *statement += QLatin1StringView(" SET ");
324 Q_ASSERT_X(mColumnValues.count() >= 1, "QueryBuilder::exec()", "At least one column needs to be changed");
325 for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
326 const auto &[column, value] = mColumnValues.at(i);
327 *statement += column;
328 *statement += QLatin1StringView(" = ");
329 bindValue(statement, value);
330 if (i + 1 < c) {
331 *statement += QLatin1StringView(", ");
332 }
333 }
334
335 if (mDatabaseType == DbType::PostgreSQL && !mJoinedTables.isEmpty()) {
336 // PSQL have this syntax
337 // FROM t1 JOIN t2 JOIN ...
338 *statement += QLatin1StringView(" FROM ");
339 appendJoined(statement, mJoinedTables, QLatin1StringView(" JOIN "));
340 }
341 break;
342 }
343 case Delete:
344 *statement += QLatin1StringView("DELETE FROM ");
345 *statement += mTable;
346 break;
347 default:
348 Q_ASSERT_X(false, "QueryBuilder::exec()", "Unknown enum value");
349 }
350
351 if (!whereCondition.isEmpty()) {
352 *statement += QLatin1StringView(" WHERE ");
353 buildWhereCondition(statement, whereCondition);
354 }
355
356 if (!mGroupColumns.isEmpty()) {
357 *statement += QLatin1StringView(" GROUP BY ");
358 appendJoined(statement, mGroupColumns);
359 }
360
361 if (!mRootCondition[HavingCondition].isEmpty()) {
362 *statement += QLatin1StringView(" HAVING ");
363 buildWhereCondition(statement, mRootCondition[HavingCondition]);
364 }
365
366 if (!mSortColumns.isEmpty()) {
367 Q_ASSERT_X(mType == Select, "QueryBuilder::exec()", "Order statements are only valid for SELECT queries");
368 *statement += QLatin1StringView(" ORDER BY ");
369 for (int i = 0, c = mSortColumns.size(); i < c; ++i) {
370 const auto &[column, order] = mSortColumns.at(i);
371 *statement += column;
372 *statement += sortOrderToString(order);
373 if (i + 1 < c) {
374 *statement += QLatin1StringView(", ");
375 }
376 }
377 }
378
379 if (mLimit > 0) {
380 *statement += QLatin1StringView(" LIMIT ") + QString::number(mLimit);
381 if (mOffset > 0) {
382 *statement += QLatin1StringView(" OFFSET ") + QString::number(mOffset);
383 }
384 }
385
386 if (mType == Select && mForUpdate) {
387 if (mDatabaseType == DbType::Sqlite) {
388 // SQLite does not support SELECT ... FOR UPDATE syntax, because it does
389 // table-level locking
390 } else {
391 *statement += QLatin1StringView(" FOR UPDATE");
392 }
393 }
394}
395
397{
398 QString statement;
399 statement.reserve(1024);
400 buildQuery(&statement);
401
402#ifndef QUERYBUILDER_UNITTEST
403 auto query = QueryCache::query(statement);
404 if (query.has_value()) {
405 mQuery = *query;
406 } else {
407 mQuery.clear();
408 if (!mQuery.prepare(statement)) {
409 qCCritical(AKONADISERVER_LOG) << "DATABASE ERROR while PREPARING QUERY:";
410 qCCritical(AKONADISERVER_LOG) << " Error code:" << mQuery.lastError().nativeErrorCode();
411 qCCritical(AKONADISERVER_LOG) << " DB error: " << mQuery.lastError().databaseText();
412 qCCritical(AKONADISERVER_LOG) << " Error text:" << mQuery.lastError().text();
413 qCCritical(AKONADISERVER_LOG) << " Query:" << statement;
414 return false;
415 }
416 QueryCache::insert(mDataStore->database(), statement, mQuery);
417 }
418
419 // too heavy debug info but worths to have from time to time
420 // qCDebug(AKONADISERVER_LOG) << "Executing query" << statement;
421 bool isBatch = false;
422 for (int i = 0; i < mBindValues.count(); ++i) {
423 mQuery.bindValue(QLatin1Char(':') + QString::number(i), mBindValues[i]);
424 if (!isBatch && static_cast<QMetaType::Type>(mBindValues[i].typeId()) == qMetaTypeId<QVariantList>()) {
425 isBatch = true;
426 }
427 // qCDebug(AKONADISERVER_LOG) << QString::fromLatin1( ":%1" ).arg( i ) << mBindValues[i];
428 }
429
430 bool ret;
431
432 if (StorageDebugger::instance()->isSQLDebuggingEnabled()) {
434 t.start();
435 if (isBatch) {
436 ret = mQuery.execBatch();
437 } else {
438 ret = mQuery.exec();
439 }
440 StorageDebugger::instance()->queryExecuted(reinterpret_cast<qint64>(mDataStore), mQuery, t.elapsed());
441 } else {
442 StorageDebugger::instance()->incSequence();
443 if (isBatch) {
444 ret = mQuery.execBatch();
445 } else {
446 ret = mQuery.exec();
447 }
448 }
449
450 if (!ret) {
451 bool needsRetry = false;
452 // Handle transaction deadlocks and timeouts by attempting to replay the transaction.
453 if (mDatabaseType == DbType::PostgreSQL) {
454 const QString dbError = mQuery.lastError().databaseText();
455 if (dbError.contains(QLatin1StringView("40P01" /* deadlock_detected */))) {
456 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction";
457 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
458 needsRetry = true;
459 }
460 } else if (mDatabaseType == DbType::MySQL) {
461 const QString lastErrorStr = mQuery.lastError().nativeErrorCode();
462 const int error = lastErrorStr.isEmpty() ? -1 : lastErrorStr.toInt();
463 if (error == 1213 /* ER_LOCK_DEADLOCK */) {
464 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction";
465 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
466 needsRetry = true;
467 } else if (error == 1205 /* ER_LOCK_WAIT_TIMEOUT */) {
468 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction timeout, retrying transaction";
469 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
470 // Not sure retrying helps, maybe error is good enough.... but doesn't hurt to retry a few times before giving up.
471 needsRetry = true;
472 }
473 } else if (mDatabaseType == DbType::Sqlite) {
474 const QString lastErrorStr = mQuery.lastError().nativeErrorCode();
475 const int error = lastErrorStr.isEmpty() ? -1 : lastErrorStr.toInt();
476 if (error == 6 /* SQLITE_LOCKED */) {
477 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction";
478 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
479 mDataStore->doRollback();
480 needsRetry = true;
481 } else if (error == 5 /* SQLITE_BUSY */) {
482 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction timeout, retrying transaction";
483 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
484 mDataStore->doRollback();
485 needsRetry = true;
486 }
487 }
488
489 if (needsRetry) {
490 mDataStore->transactionKilledByDB();
491 throw DbDeadlockException(mQuery);
492 }
493
494 qCCritical(AKONADISERVER_LOG) << "DATABASE ERROR:";
495 qCCritical(AKONADISERVER_LOG) << " Error code:" << mQuery.lastError().nativeErrorCode();
496 qCCritical(AKONADISERVER_LOG) << " DB error: " << mQuery.lastError().databaseText();
497 qCCritical(AKONADISERVER_LOG) << " Error text:" << mQuery.lastError().text();
498 qCCritical(AKONADISERVER_LOG) << " Values:" << mQuery.boundValues();
499 qCCritical(AKONADISERVER_LOG) << " Query:" << statement;
500 return false;
501 }
502#else
503 mStatement = statement;
504#endif
505 return true;
506}
507
509{
510 mColumns << cols;
511}
512
514{
515 mColumns << col;
516}
517
518void QueryBuilder::addColumn(const Query::Case &caseStmt)
519{
521 buildCaseStatement(&query, caseStmt);
522 mColumns.append(query);
523}
524
525void QueryBuilder::addAggregation(const QString &col, const QString &aggregate)
526{
527 mColumns.append(aggregate + QLatin1Char('(') + col + QLatin1Char(')'));
528}
529
530void QueryBuilder::addAggregation(const Query::Case &caseStmt, const QString &aggregate)
531{
532 QString query(aggregate + QLatin1Char('('));
533 buildCaseStatement(&query, caseStmt);
534 query += QLatin1Char(')');
535
536 mColumns.append(query);
537}
538
539void QueryBuilder::bindValue(QString *query, const QVariant &value)
540{
541 if (value.metaType().id() == qMetaTypeId<QDateTime>()) {
542 mBindValues.emplace_back(Utils::dateTimeToVariant(value.toDateTime(), mDataStore));
543 } else {
544 mBindValues.emplace_back(value);
545 }
546 *query += QLatin1Char(':') + QString::number(mBindValues.count() - 1);
547}
548
549namespace
550{
551
552bool isList(const QVariant &value)
553{
554 if (value.typeId() == qMetaTypeId<QVariantList>() || value.typeId() == qMetaTypeId<QStringList>() || value.typeId() == qMetaTypeId<QByteArrayList>()) {
555 return true;
556 }
557
558 if (value.typeId() >= QMetaType::User) {
559 // QVariant will do anything to convert its content into a QVariantList,
560 // so we can't rely on QVariant::canConvert<QVariantList>() here.
561 // The only reliable solution is this hack
562 const char *name = value.typeName();
563 if (qstrncmp(name, "QList<", 6) == 0) {
564 return true;
565 }
566 }
567
568 return false;
569}
570
571} // namespace
572
573void QueryBuilder::buildWhereCondition(QString *query, const Query::Condition &cond)
574{
575 if (!cond.isEmpty()) {
576 *query += QLatin1StringView("( ");
577 const QLatin1StringView glue = logicOperatorToString(cond.mCombineOp);
578 const Query::Condition::List &subConditions = cond.subConditions();
579 for (qsizetype i = 0, c = subConditions.size(); i < c; ++i) {
580 buildWhereCondition(query, subConditions.at(i));
581 if (i + 1 < c) {
582 *query += glue;
583 }
584 }
585 *query += QLatin1StringView(" )");
586 } else {
587 *query += cond.mColumn;
588 *query += compareOperatorToString(cond.mCompareOp);
589 if (!cond.mComparedColumn.isEmpty()) {
590 *query += cond.mComparedColumn;
591 } else if (!cond.mComparedValue.isValid()) {
592 *query += u"NULL";
593 } else if (isList(cond.mComparedValue)) {
594 *query += u"( ";
595 const auto list = cond.mComparedValue.toList();
596 if (list.isEmpty()) {
597 qCWarning(AKONADISERVER_LOG) << "Empty list given for IN condition.";
598 }
599 for (const auto &[i, entry] : list | Views::enumerate()) {
600 if (i > 0) {
601 *query += QLatin1StringView(", ");
602 }
603 bindValue(query, entry);
604 }
605 *query += u" )";
606 } else {
607 bindValue(query, cond.mComparedValue);
608 }
609 }
610}
611
612void QueryBuilder::buildCaseStatement(QString *query, const Query::Case &caseStmt)
613{
614 *query += QLatin1StringView("CASE ");
615 for (const auto &whenThen : caseStmt.mWhenThen) {
616 *query += QLatin1StringView("WHEN ");
617 buildWhereCondition(query, whenThen.first); // When
618 *query += QLatin1StringView(" THEN ") + whenThen.second; // then
619 }
620 if (!caseStmt.mElse.isEmpty()) {
621 *query += QLatin1StringView(" ELSE ") + caseStmt.mElse;
622 }
623 *query += QLatin1StringView(" END");
624}
625
627{
628 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
629 mRootCondition[type].setSubQueryMode(op);
630}
631
633{
634 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
635 mRootCondition[type].addCondition(condition);
636}
637
639{
640 mSortColumns << qMakePair(column, order);
641}
642
644{
645 Q_ASSERT(mType == Select);
646 mGroupColumns << column;
647}
648
650{
651 Q_ASSERT(mType == Select);
652 mGroupColumns += columns;
653}
654
655void QueryBuilder::setColumnValue(const QString &column, const QVariant &value)
656{
657 mColumnValues << qMakePair(column, value);
658}
659
660void QueryBuilder::setDistinct(bool distinct)
661{
662 mDistinct = distinct;
663}
664
665void QueryBuilder::setLimit(int limit, int offset)
666{
667 mLimit = limit;
668 mOffset = offset;
669}
670
672{
673 mIdentificationColumn = column;
674}
675
677{
678 if (mDatabaseType == DbType::PostgreSQL) {
679 query().next();
680 if (mIdentificationColumn.isEmpty()) {
681 return 0; // FIXME: Does this make sense?
682 }
683 return query().record().value(mIdentificationColumn).toLongLong();
684 } else {
685 const QVariant v = query().lastInsertId();
686 if (!v.isValid()) {
687 return -1;
688 }
689 bool ok;
690 const qint64 insertId = v.toLongLong(&ok);
691 if (!ok) {
692 return -1;
693 }
694 return insertId;
695 }
696 return -1;
697}
698
699void QueryBuilder::setForUpdate(bool forUpdate)
700{
701 mForUpdate = forUpdate;
702}
703
705{
706 return mTable;
707}
708
710{
711 return mTable + QLatin1Char('.') + column;
712}
713
714QString QueryBuilder::getTableQuery(const QSqlQuery& query, const QString &alias)
715{
716 Q_ASSERT_X(query.isValid() && query.isSelect(), "QueryBuilder::getTableQuery", "Table subquery use only for valid SELECT queries");
717
718 QString tableQuery = query.lastQuery();
719 if (tableQuery.isEmpty()) {
720 qCWarning(AKONADISERVER_LOG) << "Table subquery is empty";
721 return tableQuery;
722 }
723
724 tableQuery.prepend(QLatin1StringView("( "));
725
726 const QList<QVariant> boundValues = query.boundValues();
727 for (qsizetype pos = boundValues.size() - 1; pos >= 0; --pos) {
728 const QVariant &value = boundValues.at(pos);
729 const QString key(QLatin1Char(':') + QString::number(pos));
730 QSqlField field(QLatin1StringView(""), value.metaType());
731 if (value.isNull()) {
732 field.clear();
733 }
734 else {
735 field.setValue(value);
736 }
737
738 const QString formattedValue = query.driver()->formatValue(field);
739 tableQuery.replace(key, formattedValue);
740 }
741
742 tableQuery.append(QLatin1StringView(" ) AS %1").arg(alias));
743
744 return tableQuery;
745}
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 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.
QSqlQuery & query()
Returns the query, only valid after exec().
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
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...
@ 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, const QSqlQuery &query)
Insert query into the cache for queryStatement.
std::optional< QSqlQuery > query(const QString &queryStatement)
Returns the cached (and prepared) query for 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
VehicleSection::Type type(QStringView coachNumber, QStringView coachClassification)
KIOCORE_EXPORT QStringList list(const QString &fileClass)
QString name(StandardAction id)
A glue between Qt and the standard library.
qint64 elapsed() const const
void append(QList< T > &&value)
const_reference at(qsizetype i) const const
qsizetype count() const const
reference emplace_back(Args &&... args)
bool isEmpty() const const
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
void clear()
const QSqlDriver * driver() const const
bool exec()
bool execBatch(BatchExecutionMode mode)
bool first()
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)
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
This file is part of the KDE documentation.
Documentation copyright © 1996-2024 The KDE developers.
Generated on Fri May 17 2024 11:54:40 by doxygen 1.10.0 written by Dimitri van Heesch, © 1997-2006

KDE's Doxygen guidelines are available online.