Akonadi

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

KDE's Doxygen guidelines are available online.