File indexing completed on 2025-01-05 04:47:00
0001 /* 0002 SPDX-FileCopyrightText: 2007-2012 Volker Krause <vkrause@kde.org> 0003 0004 SPDX-License-Identifier: LGPL-2.0-or-later 0005 */ 0006 0007 #include "querybuilder.h" 0008 #include "akonadiserver_debug.h" 0009 #include "dbexception.h" 0010 #include <qglobal.h> 0011 0012 #ifndef QUERYBUILDER_UNITTEST 0013 #include "storage/datastore.h" 0014 #include "storage/querycache.h" 0015 #include "storage/storagedebugger.h" 0016 #endif 0017 0018 #include "shared/akranges.h" 0019 0020 #include <QElapsedTimer> 0021 #include <QSqlError> 0022 #include <QSqlRecord> 0023 #include <QSqlField> 0024 #include <QSqlDriver> 0025 0026 using namespace Akonadi::Server; 0027 0028 namespace 0029 { 0030 0031 DataStore *defaultDataStore() 0032 { 0033 #ifdef QUERYBUILDER_UNITTEST 0034 return nullptr; 0035 #else 0036 return DataStore::self(); 0037 #endif 0038 } 0039 0040 } // namespace 0041 0042 static QLatin1StringView compareOperatorToString(Query::CompareOperator op) 0043 { 0044 switch (op) { 0045 case Query::Equals: 0046 return QLatin1StringView(" = "); 0047 case Query::NotEquals: 0048 return QLatin1StringView(" <> "); 0049 case Query::Is: 0050 return QLatin1StringView(" IS "); 0051 case Query::IsNot: 0052 return QLatin1StringView(" IS NOT "); 0053 case Query::Less: 0054 return QLatin1StringView(" < "); 0055 case Query::LessOrEqual: 0056 return QLatin1StringView(" <= "); 0057 case Query::Greater: 0058 return QLatin1StringView(" > "); 0059 case Query::GreaterOrEqual: 0060 return QLatin1StringView(" >= "); 0061 case Query::In: 0062 return QLatin1StringView(" IN "); 0063 case Query::NotIn: 0064 return QLatin1StringView(" NOT IN "); 0065 case Query::Like: 0066 return QLatin1StringView(" LIKE "); 0067 } 0068 Q_ASSERT_X(false, "QueryBuilder::compareOperatorToString()", "Unknown compare operator."); 0069 return QLatin1StringView(""); 0070 } 0071 0072 static QLatin1StringView logicOperatorToString(Query::LogicOperator op) 0073 { 0074 switch (op) { 0075 case Query::And: 0076 return QLatin1StringView(" AND "); 0077 case Query::Or: 0078 return QLatin1StringView(" OR "); 0079 } 0080 Q_ASSERT_X(false, "QueryBuilder::logicOperatorToString()", "Unknown logic operator."); 0081 return QLatin1StringView(""); 0082 } 0083 0084 static QLatin1StringView sortOrderToString(Query::SortOrder order) 0085 { 0086 switch (order) { 0087 case Query::Ascending: 0088 return QLatin1StringView(" ASC"); 0089 case Query::Descending: 0090 return QLatin1StringView(" DESC"); 0091 } 0092 Q_ASSERT_X(false, "QueryBuilder::sortOrderToString()", "Unknown sort order."); 0093 return QLatin1StringView(""); 0094 } 0095 0096 static void appendJoined(QString *statement, const QStringList &strings, QLatin1StringView glue = QLatin1StringView(", ")) 0097 { 0098 for (int i = 0, c = strings.size(); i < c; ++i) { 0099 *statement += strings.at(i); 0100 if (i + 1 < c) { 0101 *statement += glue; 0102 } 0103 } 0104 } 0105 0106 QueryBuilder::QueryBuilder(const QString &table, QueryBuilder::QueryType type) 0107 : QueryBuilder(defaultDataStore(), table, type) 0108 { 0109 } 0110 0111 QueryBuilder::QueryBuilder(DataStore *store, const QString &table, QueryBuilder::QueryType type) 0112 : mTable(table) 0113 #ifndef QUERYBUILDER_UNITTEST 0114 , mDataStore(store) 0115 , mDatabaseType(DbType::type(store->database())) 0116 , mQuery(store->database()) 0117 #else 0118 , mDatabaseType(DbType::Unknown) 0119 #endif 0120 , mType(type) 0121 , mLimit(-1) 0122 , mOffset(-1) 0123 , mDistinct(false) 0124 { 0125 static const QString defaultIdColumn = QStringLiteral("id"); 0126 mIdentificationColumn = defaultIdColumn; 0127 } 0128 0129 QueryBuilder::QueryBuilder(const QSqlQuery &tableQuery, const QString &tableQueryAlias) 0130 : QueryBuilder(defaultDataStore(), tableQuery, tableQueryAlias) 0131 { 0132 } 0133 0134 QueryBuilder::QueryBuilder(DataStore *store, const QSqlQuery &tableQuery, const QString &tableQueryAlias) 0135 : mTable(tableQueryAlias) 0136 , mTableSubQuery(tableQuery) 0137 #ifndef QUERYBUILDER_UNITTEST 0138 , mDataStore(store) 0139 , mDatabaseType(DbType::type(store->database())) 0140 , mQuery(store->database()) 0141 #else 0142 , mDatabaseType(DbType::Unknown) 0143 #endif 0144 , mType(QueryType::Select) 0145 , mLimit(-1) 0146 , mOffset(-1) 0147 , mDistinct(false) 0148 { 0149 static const QString defaultIdColumn = QStringLiteral("id"); 0150 mIdentificationColumn = defaultIdColumn; 0151 } 0152 0153 void QueryBuilder::setDatabaseType(DbType::Type type) 0154 { 0155 mDatabaseType = type; 0156 } 0157 0158 void QueryBuilder::addJoin(JoinType joinType, const QString &table, const Query::Condition &condition) 0159 { 0160 Q_ASSERT((joinType == InnerJoin && (mType == Select || mType == Update)) || (joinType == LeftJoin && mType == Select)); 0161 0162 if (mJoinedTables.contains(table)) { 0163 // InnerJoin is more restrictive than a LeftJoin, hence use that in doubt 0164 mJoins[table].first = qMin(joinType, mJoins.value(table).first); 0165 mJoins[table].second.addCondition(condition); 0166 } else { 0167 mJoins[table] = qMakePair(joinType, condition); 0168 mJoinedTables << table; 0169 } 0170 } 0171 0172 void QueryBuilder::addJoin(JoinType joinType, const QString &table, const QString &col1, const QString &col2) 0173 { 0174 Query::Condition condition; 0175 condition.addColumnCondition(col1, Query::Equals, col2); 0176 addJoin(joinType, table, condition); 0177 } 0178 0179 void QueryBuilder::addValueCondition(const QString &column, Query::CompareOperator op, const QVariant &value, ConditionType type) 0180 { 0181 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select)); 0182 mRootCondition[type].addValueCondition(column, op, value); 0183 } 0184 0185 void QueryBuilder::addColumnCondition(const QString &column, Query::CompareOperator op, const QString &column2, ConditionType type) 0186 { 0187 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select)); 0188 mRootCondition[type].addColumnCondition(column, op, column2); 0189 } 0190 0191 QSqlQuery &QueryBuilder::query() 0192 { 0193 return mQuery; 0194 } 0195 0196 void QueryBuilder::sqliteAdaptUpdateJoin(Query::Condition &condition) 0197 { 0198 // FIXME: This does not cover all cases by far. It however can handle most 0199 // (probably all) of the update-join queries we do in Akonadi and convert them 0200 // properly into a SQLite-compatible query. Better than nothing ;-) 0201 0202 if (!condition.mSubConditions.isEmpty()) { 0203 for (int i = condition.mSubConditions.count() - 1; i >= 0; --i) { 0204 sqliteAdaptUpdateJoin(condition.mSubConditions[i]); 0205 } 0206 return; 0207 } 0208 0209 QString table; 0210 if (condition.mColumn.contains(QLatin1Char('.'))) { 0211 table = condition.mColumn.left(condition.mColumn.indexOf(QLatin1Char('.'))); 0212 } else { 0213 return; 0214 } 0215 0216 if (!mJoinedTables.contains(table)) { 0217 return; 0218 } 0219 0220 const auto &[type, joinCondition] = mJoins.value(table); 0221 0222 QueryBuilder qb(table, Select); 0223 qb.addColumn(condition.mColumn); 0224 qb.addCondition(joinCondition); 0225 0226 // Convert the subquery to string 0227 condition.mColumn.reserve(1024); 0228 condition.mColumn.resize(0); 0229 condition.mColumn += QLatin1StringView("( "); 0230 qb.buildQuery(&condition.mColumn); 0231 condition.mColumn += QLatin1StringView(" )"); 0232 } 0233 0234 void QueryBuilder::buildQuery(QString *statement) 0235 { 0236 // we add the ON conditions of Inner Joins in a Update query here 0237 // but don't want to change the mRootCondition on each exec(). 0238 Query::Condition whereCondition = mRootCondition[WhereCondition]; 0239 0240 switch (mType) { 0241 case Select: 0242 // Enable forward-only on all SELECT queries, since we never need to 0243 // iterate backwards. This is a memory optimization. 0244 mQuery.setForwardOnly(true); 0245 *statement += QLatin1StringView("SELECT "); 0246 if (mDistinct) { 0247 *statement += QLatin1StringView("DISTINCT "); 0248 } 0249 Q_ASSERT_X(mColumns.count() > 0, "QueryBuilder::exec()", "No columns specified"); 0250 appendJoined(statement, mColumns); 0251 *statement += QLatin1StringView(" FROM "); 0252 *statement += mTableSubQuery.isValid() 0253 ? getTableQuery(mTableSubQuery, mTable) : mTable; 0254 for (const QString &joinedTable : std::as_const(mJoinedTables)) { 0255 const auto &[joinType, joinCond] = mJoins.value(joinedTable); 0256 switch (joinType) { 0257 case LeftJoin: 0258 *statement += QLatin1StringView(" LEFT JOIN "); 0259 break; 0260 case InnerJoin: 0261 *statement += QLatin1StringView(" INNER JOIN "); 0262 break; 0263 } 0264 *statement += joinedTable; 0265 *statement += QLatin1StringView(" ON "); 0266 buildWhereCondition(statement, joinCond); 0267 } 0268 break; 0269 case Insert: { 0270 *statement += QLatin1StringView("INSERT INTO "); 0271 *statement += mTable; 0272 *statement += QLatin1StringView(" ("); 0273 for (int i = 0, c = mColumnValues.size(); i < c; ++i) { 0274 *statement += mColumnValues.at(i).first; 0275 if (i + 1 < c) { 0276 *statement += QLatin1StringView(", "); 0277 } 0278 } 0279 *statement += QLatin1StringView(") VALUES ("); 0280 for (int i = 0, c = mColumnValues.size(); i < c; ++i) { 0281 bindValue(statement, mColumnValues.at(i).second); 0282 if (i + 1 < c) { 0283 *statement += QLatin1StringView(", "); 0284 } 0285 } 0286 *statement += QLatin1Char(')'); 0287 if (mDatabaseType == DbType::PostgreSQL && !mIdentificationColumn.isEmpty()) { 0288 *statement += QLatin1StringView(" RETURNING ") + mIdentificationColumn; 0289 } 0290 break; 0291 } 0292 case Update: { 0293 // put the ON condition into the WHERE part of the UPDATE query 0294 if (mDatabaseType != DbType::Sqlite) { 0295 for (const QString &table : std::as_const(mJoinedTables)) { 0296 const auto &[joinType, joinCond] = mJoins.value(table); 0297 Q_ASSERT(joinType == InnerJoin); 0298 whereCondition.addCondition(joinCond); 0299 } 0300 } else { 0301 // Note: this will modify the whereCondition 0302 sqliteAdaptUpdateJoin(whereCondition); 0303 } 0304 0305 *statement += QLatin1StringView("UPDATE "); 0306 *statement += mTable; 0307 0308 if (mDatabaseType == DbType::MySQL && !mJoinedTables.isEmpty()) { 0309 // for mysql we list all tables directly 0310 *statement += QLatin1StringView(", "); 0311 appendJoined(statement, mJoinedTables); 0312 } 0313 0314 *statement += QLatin1StringView(" SET "); 0315 Q_ASSERT_X(mColumnValues.count() >= 1, "QueryBuilder::exec()", "At least one column needs to be changed"); 0316 for (int i = 0, c = mColumnValues.size(); i < c; ++i) { 0317 const auto &[column, value] = mColumnValues.at(i); 0318 *statement += column; 0319 *statement += QLatin1StringView(" = "); 0320 bindValue(statement, value); 0321 if (i + 1 < c) { 0322 *statement += QLatin1StringView(", "); 0323 } 0324 } 0325 0326 if (mDatabaseType == DbType::PostgreSQL && !mJoinedTables.isEmpty()) { 0327 // PSQL have this syntax 0328 // FROM t1 JOIN t2 JOIN ... 0329 *statement += QLatin1StringView(" FROM "); 0330 appendJoined(statement, mJoinedTables, QLatin1StringView(" JOIN ")); 0331 } 0332 break; 0333 } 0334 case Delete: 0335 *statement += QLatin1StringView("DELETE FROM "); 0336 *statement += mTable; 0337 break; 0338 default: 0339 Q_ASSERT_X(false, "QueryBuilder::exec()", "Unknown enum value"); 0340 } 0341 0342 if (!whereCondition.isEmpty()) { 0343 *statement += QLatin1StringView(" WHERE "); 0344 buildWhereCondition(statement, whereCondition); 0345 } 0346 0347 if (!mGroupColumns.isEmpty()) { 0348 *statement += QLatin1StringView(" GROUP BY "); 0349 appendJoined(statement, mGroupColumns); 0350 } 0351 0352 if (!mRootCondition[HavingCondition].isEmpty()) { 0353 *statement += QLatin1StringView(" HAVING "); 0354 buildWhereCondition(statement, mRootCondition[HavingCondition]); 0355 } 0356 0357 if (!mSortColumns.isEmpty()) { 0358 Q_ASSERT_X(mType == Select, "QueryBuilder::exec()", "Order statements are only valid for SELECT queries"); 0359 *statement += QLatin1StringView(" ORDER BY "); 0360 for (int i = 0, c = mSortColumns.size(); i < c; ++i) { 0361 const auto &[column, order] = mSortColumns.at(i); 0362 *statement += column; 0363 *statement += sortOrderToString(order); 0364 if (i + 1 < c) { 0365 *statement += QLatin1StringView(", "); 0366 } 0367 } 0368 } 0369 0370 if (mLimit > 0) { 0371 *statement += QLatin1StringView(" LIMIT ") + QString::number(mLimit); 0372 if (mOffset > 0) { 0373 *statement += QLatin1StringView(" OFFSET ") + QString::number(mOffset); 0374 } 0375 } 0376 0377 if (mType == Select && mForUpdate) { 0378 if (mDatabaseType == DbType::Sqlite) { 0379 // SQLite does not support SELECT ... FOR UPDATE syntax, because it does 0380 // table-level locking 0381 } else { 0382 *statement += QLatin1StringView(" FOR UPDATE"); 0383 } 0384 } 0385 } 0386 0387 bool QueryBuilder::exec() 0388 { 0389 QString statement; 0390 statement.reserve(1024); 0391 buildQuery(&statement); 0392 0393 #ifndef QUERYBUILDER_UNITTEST 0394 auto query = QueryCache::query(statement); 0395 if (query.has_value()) { 0396 mQuery = *query; 0397 } else { 0398 mQuery.clear(); 0399 if (!mQuery.prepare(statement)) { 0400 qCCritical(AKONADISERVER_LOG) << "DATABASE ERROR while PREPARING QUERY:"; 0401 qCCritical(AKONADISERVER_LOG) << " Error code:" << mQuery.lastError().nativeErrorCode(); 0402 qCCritical(AKONADISERVER_LOG) << " DB error: " << mQuery.lastError().databaseText(); 0403 qCCritical(AKONADISERVER_LOG) << " Error text:" << mQuery.lastError().text(); 0404 qCCritical(AKONADISERVER_LOG) << " Query:" << statement; 0405 return false; 0406 } 0407 QueryCache::insert(mDataStore->database(), statement, mQuery); 0408 } 0409 0410 // too heavy debug info but worths to have from time to time 0411 // qCDebug(AKONADISERVER_LOG) << "Executing query" << statement; 0412 bool isBatch = false; 0413 for (int i = 0; i < mBindValues.count(); ++i) { 0414 mQuery.bindValue(QLatin1Char(':') + QString::number(i), mBindValues[i]); 0415 if (!isBatch && static_cast<QMetaType::Type>(mBindValues[i].type()) == QMetaType::QVariantList) { 0416 isBatch = true; 0417 } 0418 // qCDebug(AKONADISERVER_LOG) << QString::fromLatin1( ":%1" ).arg( i ) << mBindValues[i]; 0419 } 0420 0421 bool ret; 0422 0423 if (StorageDebugger::instance()->isSQLDebuggingEnabled()) { 0424 QElapsedTimer t; 0425 t.start(); 0426 if (isBatch) { 0427 ret = mQuery.execBatch(); 0428 } else { 0429 ret = mQuery.exec(); 0430 } 0431 StorageDebugger::instance()->queryExecuted(reinterpret_cast<qint64>(mDataStore), mQuery, t.elapsed()); 0432 } else { 0433 StorageDebugger::instance()->incSequence(); 0434 if (isBatch) { 0435 ret = mQuery.execBatch(); 0436 } else { 0437 ret = mQuery.exec(); 0438 } 0439 } 0440 0441 if (!ret) { 0442 bool needsRetry = false; 0443 // Handle transaction deadlocks and timeouts by attempting to replay the transaction. 0444 if (mDatabaseType == DbType::PostgreSQL) { 0445 const QString dbError = mQuery.lastError().databaseText(); 0446 if (dbError.contains(QLatin1StringView("40P01" /* deadlock_detected */))) { 0447 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction"; 0448 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text(); 0449 needsRetry = true; 0450 } 0451 } else if (mDatabaseType == DbType::MySQL) { 0452 const QString lastErrorStr = mQuery.lastError().nativeErrorCode(); 0453 const int error = lastErrorStr.isEmpty() ? -1 : lastErrorStr.toInt(); 0454 if (error == 1213 /* ER_LOCK_DEADLOCK */) { 0455 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction"; 0456 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text(); 0457 needsRetry = true; 0458 } else if (error == 1205 /* ER_LOCK_WAIT_TIMEOUT */) { 0459 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction timeout, retrying transaction"; 0460 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text(); 0461 // Not sure retrying helps, maybe error is good enough.... but doesn't hurt to retry a few times before giving up. 0462 needsRetry = true; 0463 } 0464 } else if (mDatabaseType == DbType::Sqlite) { 0465 const QString lastErrorStr = mQuery.lastError().nativeErrorCode(); 0466 const int error = lastErrorStr.isEmpty() ? -1 : lastErrorStr.toInt(); 0467 if (error == 6 /* SQLITE_LOCKED */) { 0468 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction"; 0469 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text(); 0470 mDataStore->doRollback(); 0471 needsRetry = true; 0472 } else if (error == 5 /* SQLITE_BUSY */) { 0473 qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction timeout, retrying transaction"; 0474 qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text(); 0475 mDataStore->doRollback(); 0476 needsRetry = true; 0477 } 0478 } 0479 0480 if (needsRetry) { 0481 mDataStore->transactionKilledByDB(); 0482 throw DbDeadlockException(mQuery); 0483 } 0484 0485 qCCritical(AKONADISERVER_LOG) << "DATABASE ERROR:"; 0486 qCCritical(AKONADISERVER_LOG) << " Error code:" << mQuery.lastError().nativeErrorCode(); 0487 qCCritical(AKONADISERVER_LOG) << " DB error: " << mQuery.lastError().databaseText(); 0488 qCCritical(AKONADISERVER_LOG) << " Error text:" << mQuery.lastError().text(); 0489 qCCritical(AKONADISERVER_LOG) << " Values:" << mQuery.boundValues(); 0490 qCCritical(AKONADISERVER_LOG) << " Query:" << statement; 0491 return false; 0492 } 0493 #else 0494 mStatement = statement; 0495 #endif 0496 return true; 0497 } 0498 0499 void QueryBuilder::addColumns(const QStringList &cols) 0500 { 0501 mColumns << cols; 0502 } 0503 0504 void QueryBuilder::addColumn(const QString &col) 0505 { 0506 mColumns << col; 0507 } 0508 0509 void QueryBuilder::addColumn(const Query::Case &caseStmt) 0510 { 0511 QString query; 0512 buildCaseStatement(&query, caseStmt); 0513 mColumns.append(query); 0514 } 0515 0516 void QueryBuilder::addAggregation(const QString &col, const QString &aggregate) 0517 { 0518 mColumns.append(aggregate + QLatin1Char('(') + col + QLatin1Char(')')); 0519 } 0520 0521 void QueryBuilder::addAggregation(const Query::Case &caseStmt, const QString &aggregate) 0522 { 0523 QString query(aggregate + QLatin1Char('(')); 0524 buildCaseStatement(&query, caseStmt); 0525 query += QLatin1Char(')'); 0526 0527 mColumns.append(query); 0528 } 0529 0530 void QueryBuilder::bindValue(QString *query, const QVariant &value) 0531 { 0532 mBindValues << value; 0533 *query += QLatin1Char(':') + QString::number(mBindValues.count() - 1); 0534 } 0535 0536 void QueryBuilder::buildWhereCondition(QString *query, const Query::Condition &cond) 0537 { 0538 if (!cond.isEmpty()) { 0539 *query += QLatin1StringView("( "); 0540 const QLatin1StringView glue = logicOperatorToString(cond.mCombineOp); 0541 const Query::Condition::List &subConditions = cond.subConditions(); 0542 for (qsizetype i = 0, c = subConditions.size(); i < c; ++i) { 0543 buildWhereCondition(query, subConditions.at(i)); 0544 if (i + 1 < c) { 0545 *query += glue; 0546 } 0547 } 0548 *query += QLatin1StringView(" )"); 0549 } else { 0550 *query += cond.mColumn; 0551 *query += compareOperatorToString(cond.mCompareOp); 0552 if (cond.mComparedColumn.isEmpty()) { 0553 if (cond.mComparedValue.isValid()) { 0554 if (cond.mComparedValue.canConvert<QVariantList>() && cond.mComparedValue.typeId() != QMetaType::QString 0555 && cond.mComparedValue.typeId() != QMetaType::QByteArray) { 0556 *query += QLatin1StringView("( "); 0557 const QVariantList &entries = cond.mComparedValue.toList(); 0558 Q_ASSERT_X(!entries.isEmpty(), "QueryBuilder::buildWhereCondition()", "No values given for IN condition."); 0559 for (qsizetype i = 0, c = entries.size(); i < c; ++i) { 0560 bindValue(query, entries.at(i)); 0561 if (i + 1 < c) { 0562 *query += QLatin1StringView(", "); 0563 } 0564 } 0565 *query += QLatin1StringView(" )"); 0566 } else { 0567 bindValue(query, cond.mComparedValue); 0568 } 0569 } else { 0570 *query += QLatin1StringView("NULL"); 0571 } 0572 } else { 0573 *query += cond.mComparedColumn; 0574 } 0575 } 0576 } 0577 0578 void QueryBuilder::buildCaseStatement(QString *query, const Query::Case &caseStmt) 0579 { 0580 *query += QLatin1StringView("CASE "); 0581 for (const auto &whenThen : caseStmt.mWhenThen) { 0582 *query += QLatin1StringView("WHEN "); 0583 buildWhereCondition(query, whenThen.first); // When 0584 *query += QLatin1StringView(" THEN ") + whenThen.second; // then 0585 } 0586 if (!caseStmt.mElse.isEmpty()) { 0587 *query += QLatin1StringView(" ELSE ") + caseStmt.mElse; 0588 } 0589 *query += QLatin1StringView(" END"); 0590 } 0591 0592 void QueryBuilder::setSubQueryMode(Query::LogicOperator op, ConditionType type) 0593 { 0594 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select)); 0595 mRootCondition[type].setSubQueryMode(op); 0596 } 0597 0598 void QueryBuilder::addCondition(const Query::Condition &condition, ConditionType type) 0599 { 0600 Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select)); 0601 mRootCondition[type].addCondition(condition); 0602 } 0603 0604 void QueryBuilder::addSortColumn(const QString &column, Query::SortOrder order) 0605 { 0606 mSortColumns << qMakePair(column, order); 0607 } 0608 0609 void QueryBuilder::addGroupColumn(const QString &column) 0610 { 0611 Q_ASSERT(mType == Select); 0612 mGroupColumns << column; 0613 } 0614 0615 void QueryBuilder::addGroupColumns(const QStringList &columns) 0616 { 0617 Q_ASSERT(mType == Select); 0618 mGroupColumns += columns; 0619 } 0620 0621 void QueryBuilder::setColumnValue(const QString &column, const QVariant &value) 0622 { 0623 mColumnValues << qMakePair(column, value); 0624 } 0625 0626 void QueryBuilder::setDistinct(bool distinct) 0627 { 0628 mDistinct = distinct; 0629 } 0630 0631 void QueryBuilder::setLimit(int limit, int offset) 0632 { 0633 mLimit = limit; 0634 mOffset = offset; 0635 } 0636 0637 void QueryBuilder::setIdentificationColumn(const QString &column) 0638 { 0639 mIdentificationColumn = column; 0640 } 0641 0642 qint64 QueryBuilder::insertId() 0643 { 0644 if (mDatabaseType == DbType::PostgreSQL) { 0645 query().next(); 0646 if (mIdentificationColumn.isEmpty()) { 0647 return 0; // FIXME: Does this make sense? 0648 } 0649 return query().record().value(mIdentificationColumn).toLongLong(); 0650 } else { 0651 const QVariant v = query().lastInsertId(); 0652 if (!v.isValid()) { 0653 return -1; 0654 } 0655 bool ok; 0656 const qint64 insertId = v.toLongLong(&ok); 0657 if (!ok) { 0658 return -1; 0659 } 0660 return insertId; 0661 } 0662 return -1; 0663 } 0664 0665 void QueryBuilder::setForUpdate(bool forUpdate) 0666 { 0667 mForUpdate = forUpdate; 0668 } 0669 0670 QString QueryBuilder::getTable() const 0671 { 0672 return mTable; 0673 } 0674 0675 QString QueryBuilder::getTableWithColumn(const QString &column) const 0676 { 0677 return mTable + QLatin1Char('.') + column; 0678 } 0679 0680 QString QueryBuilder::getTableQuery(const QSqlQuery& query, const QString &alias) 0681 { 0682 Q_ASSERT_X(query.isValid() && query.isSelect(), "QueryBuilder::getTableQuery", "Table subquery use only for valid SELECT queries"); 0683 0684 QString tableQuery = query.lastQuery(); 0685 if (tableQuery.isEmpty()) { 0686 qCWarning(AKONADISERVER_LOG) << "Table subquery is empty"; 0687 return tableQuery; 0688 } 0689 0690 tableQuery.prepend(QLatin1StringView("( ")); 0691 0692 const QList<QVariant> boundValues = query.boundValues(); 0693 for (qsizetype pos = boundValues.size() - 1; pos >= 0; --pos) { 0694 const QVariant &value = boundValues.at(pos); 0695 const QString key(QLatin1Char(':') + QString::number(pos)); 0696 QSqlField field(QLatin1StringView(""), value.metaType()); 0697 if (value.isNull()) { 0698 field.clear(); 0699 } 0700 else { 0701 field.setValue(value); 0702 } 0703 0704 const QString formattedValue = query.driver()->formatValue(field); 0705 tableQuery.replace(key, formattedValue); 0706 } 0707 0708 tableQuery.append(QLatin1StringView(" ) AS %1").arg(alias)); 0709 0710 return tableQuery; 0711 }