querybuilder.cpp

/*
    Copyright (c) 2007 - 2012 Volker Krause <vkrause@kde.org>

    This library is free software; you can redistribute it and/or modify it
    under the terms of the GNU Library General Public License as published by
    the Free Software Foundation; either version 2 of the License, or (at your
    option) any later version.

    This library is distributed in the hope that it will be useful, but WITHOUT
    ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
    FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Library General Public
    License for more details.

    You should have received a copy of the GNU Library General Public License
    along with this library; see the file COPYING.LIB.  If not, write to the
    Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
    02110-1301, USA.
*/

#include "querybuilder.h"
#include "akonadiserver_debug.h"
#include "dbexception.h"

#ifndef QUERYBUILDER_UNITTEST
#include "storage/datastore.h"
#include "storage/querycache.h"
#include "storage/storagedebugger.h"
#endif

#include <shared/akranges.h>

#include <QSqlRecord>
#include <QSqlError>
#include <QTime>

using namespace Akonadi::Server;

static QLatin1String compareOperatorToString(Query::CompareOperator op)
{
    switch (op) {
    case Query::Equals:
        return QLatin1String(" = ");
    case Query::NotEquals:
        return QLatin1String(" <> ");
    case Query::Is:
        return QLatin1String(" IS ");
    case Query::IsNot:
        return QLatin1String(" IS NOT ");
    case Query::Less:
        return QLatin1String(" < ");
    case Query::LessOrEqual:
        return QLatin1String(" <= ");
    case Query::Greater:
        return QLatin1String(" > ");
    case Query::GreaterOrEqual:
        return QLatin1String(" >= ");
    case Query::In:
        return QLatin1String(" IN ");
    case Query::NotIn:
        return QLatin1String(" NOT IN ");
    case Query::Like:
        return QLatin1String(" LIKE ");
    }
    Q_ASSERT_X(false, "QueryBuilder::compareOperatorToString()", "Unknown compare operator.");
    return QLatin1String("");
}

static QLatin1String logicOperatorToString(Query::LogicOperator op)
{
    switch (op) {
    case Query::And:
        return QLatin1String(" AND ");
    case Query::Or:
        return QLatin1String(" OR ");
    }
    Q_ASSERT_X(false, "QueryBuilder::logicOperatorToString()", "Unknown logic operator.");
    return QLatin1String("");
}

static QLatin1String sortOrderToString(Query::SortOrder order)
{
    switch (order) {
    case Query::Ascending:
        return QLatin1String(" ASC");
    case Query::Descending:
        return QLatin1String(" DESC");
    }
    Q_ASSERT_X(false, "QueryBuilder::sortOrderToString()", "Unknown sort order.");
    return QLatin1String("");
}

static void appendJoined(QString *statement, const QStringList &strings, const QLatin1String &glue = QLatin1String(", "))
{
    for (int i = 0, c = strings.size(); i < c; ++i) {
        *statement += strings.at(i);
        if (i + 1 < c) {
            *statement += glue;
        }
    }
}

QueryBuilder::QueryBuilder(const QString &table, QueryBuilder::QueryType type)
    : mTable(table)
#ifndef QUERYBUILDER_UNITTEST
    , mDatabaseType(DbType::type(DataStore::self()->database()))
    , mQuery(DataStore::self()->database())
#else
    , mDatabaseType(DbType::Unknown)
#endif
    , mType(type)
    , mIdentificationColumn()
    , mLimit(-1)
    , mDistinct(false)
{
    static const QString defaultIdColumn = QStringLiteral("id");
    mIdentificationColumn = defaultIdColumn;
}

void QueryBuilder::setDatabaseType(DbType::Type type)
{
    mDatabaseType = type;
}

void QueryBuilder::addJoin(JoinType joinType, const QString &table, const Query::Condition &condition)
{
    Q_ASSERT((joinType == InnerJoin && (mType == Select || mType == Update)) ||
             (joinType == LeftJoin && mType == Select));

    if (mJoinedTables.contains(table)) {
        // InnerJoin is more restrictive than a LeftJoin, hence use that in doubt
        mJoins[table].first = qMin(joinType, mJoins.value(table).first);
        mJoins[table].second.addCondition(condition);
    } else {
        mJoins[table] = qMakePair(joinType, condition);
        mJoinedTables << table;
    }
}

void QueryBuilder::addJoin(JoinType joinType, const QString &table, const QString &col1, const QString &col2)
{
    Query::Condition condition;
    condition.addColumnCondition(col1, Query::Equals, col2);
    addJoin(joinType, table, condition);
}

void QueryBuilder::addValueCondition(const QString &column, Query::CompareOperator op, const QVariant &value, ConditionType type)
{
    Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
    mRootCondition[type].addValueCondition(column, op, value);
}

void QueryBuilder::addColumnCondition(const QString &column, Query::CompareOperator op, const QString &column2, ConditionType type)
{
    Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
    mRootCondition[type].addColumnCondition(column, op, column2);
}

QSqlQuery &QueryBuilder::query()
{
    return mQuery;
}

void QueryBuilder::sqliteAdaptUpdateJoin(Query::Condition &condition)
{
    // FIXME: This does not cover all cases by far. It however can handle most
    // (probably all) of the update-join queries we do in Akonadi and convert them
    // properly into a SQLite-compatible query. Better than nothing ;-)

    if (!condition.mSubConditions.isEmpty()) {
        for (int i = condition.mSubConditions.count() - 1; i >= 0; --i) {
            sqliteAdaptUpdateJoin(condition.mSubConditions[i]);
        }
        return;
    }

    QString table;
    if (condition.mColumn.contains(QLatin1Char('.'))) {
        table = condition.mColumn.left(condition.mColumn.indexOf(QLatin1Char('.')));
    } else {
        return;
    }

    if (!mJoinedTables.contains(table)) {
        return;
    }

    const QPair<JoinType, Query::Condition> joinCondition = mJoins.value(table);

    QueryBuilder qb(table, Select);
    qb.addColumn(condition.mColumn);
    qb.addCondition(joinCondition.second);

    // Convert the subquery to string
    condition.mColumn.reserve(1024);
    condition.mColumn.resize(0);
    condition.mColumn += QLatin1String("( ");
    qb.buildQuery(&condition.mColumn);
    condition.mColumn += QLatin1String(" )");
}

void QueryBuilder::buildQuery(QString *statement)
{
    // we add the ON conditions of Inner Joins in a Update query here
    // but don't want to change the mRootCondition on each exec().
    Query::Condition whereCondition = mRootCondition[WhereCondition];

    switch (mType) {
    case Select:
        // Enable forward-only on all SELECT queries, since we never need to
        // iterate backwards. This is a memory optimization.
        mQuery.setForwardOnly(true);
        *statement += QLatin1String("SELECT ");
        if (mDistinct) {
            *statement += QLatin1String("DISTINCT ");
        }
        Q_ASSERT_X(mColumns.count() > 0, "QueryBuilder::exec()", "No columns specified");
        appendJoined(statement, mColumns);
        *statement += QLatin1String(" FROM ");
        *statement += mTable;
        for (const QString &joinedTable : qAsConst(mJoinedTables)) {
            const QPair<JoinType, Query::Condition> &join = mJoins.value(joinedTable);
            switch (join.first) {
            case LeftJoin:
                *statement += QLatin1String(" LEFT JOIN ");
                break;
            case InnerJoin:
                *statement += QLatin1String(" INNER JOIN ");
                break;
            }
            *statement += joinedTable;
            *statement += QLatin1String(" ON ");
            buildWhereCondition(statement, join.second);
        }
        break;
    case Insert: {
        *statement += QLatin1String("INSERT INTO ");
        *statement += mTable;
        *statement += QLatin1String(" (");
        for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
            *statement += mColumnValues.at(i).first;
            if (i + 1 < c) {
                *statement += QLatin1String(", ");
            }
        }
        *statement += QLatin1String(") VALUES (");
        for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
            bindValue(statement, mColumnValues.at(i).second);
            if (i + 1 < c) {
                *statement += QLatin1String(", ");
            }
        }
        *statement += QLatin1Char(')');
        if (mDatabaseType == DbType::PostgreSQL && !mIdentificationColumn.isEmpty()) {
            *statement += QLatin1String(" RETURNING ") + mIdentificationColumn;
        }
        break;
    }
    case Update: {
        // put the ON condition into the WHERE part of the UPDATE query
        if (mDatabaseType != DbType::Sqlite) {
            for (const QString &table : qAsConst(mJoinedTables)) {
                const QPair< JoinType, Query::Condition > &join = mJoins.value(table);
                Q_ASSERT(join.first == InnerJoin);
                whereCondition.addCondition(join.second);
            }
        } else {
            // Note: this will modify the whereCondition
            sqliteAdaptUpdateJoin(whereCondition);
        }

        *statement += QLatin1String("UPDATE ");
        *statement += mTable;

        if (mDatabaseType == DbType::MySQL && !mJoinedTables.isEmpty()) {
            // for mysql we list all tables directly
            *statement += QLatin1String(", ");
            appendJoined(statement, mJoinedTables);
        }

        *statement += QLatin1String(" SET ");
        Q_ASSERT_X(mColumnValues.count() >= 1, "QueryBuilder::exec()", "At least one column needs to be changed");
        for (int i = 0, c = mColumnValues.size(); i < c; ++i) {
            const QPair<QString, QVariant> &p = mColumnValues.at(i);
            *statement += p.first;
            *statement += QLatin1String(" = ");
            bindValue(statement, p.second);
            if (i + 1 < c) {
                *statement += QLatin1String(", ");
            }
        }

        if (mDatabaseType == DbType::PostgreSQL && !mJoinedTables.isEmpty()) {
            // PSQL have this syntax
            // FROM t1 JOIN t2 JOIN ...
            *statement += QLatin1String(" FROM ");
            appendJoined(statement, mJoinedTables, QLatin1String(" JOIN "));
        }
        break;
    }
    case Delete:
        *statement += QLatin1String("DELETE FROM ");
        *statement += mTable;
        break;
    default:
        Q_ASSERT_X(false, "QueryBuilder::exec()", "Unknown enum value");
    }

    if (!whereCondition.isEmpty()) {
        *statement += QLatin1String(" WHERE ");
        buildWhereCondition(statement, whereCondition);
    }

    if (!mGroupColumns.isEmpty()) {
        *statement += QLatin1String(" GROUP BY ");
        appendJoined(statement, mGroupColumns);
    }

    if (!mRootCondition[HavingCondition].isEmpty()) {
        *statement += QLatin1String(" HAVING ");
        buildWhereCondition(statement, mRootCondition[HavingCondition]);
    }

    if (!mSortColumns.isEmpty()) {
        Q_ASSERT_X(mType == Select, "QueryBuilder::exec()", "Order statements are only valid for SELECT queries");
        *statement += QLatin1String(" ORDER BY ");
        for (int i = 0, c = mSortColumns.size(); i < c; ++i) {
            const QPair<QString, Query::SortOrder> &order = mSortColumns.at(i);
            *statement += order.first;
            *statement += sortOrderToString(order.second);
            if (i + 1 < c) {
                *statement += QLatin1String(", ");
            }
        }
    }

    if (mLimit > 0) {
        *statement += QLatin1Literal(" LIMIT ") + QString::number(mLimit);
    }

    if (mType == Select && mForUpdate) {
        if (mDatabaseType == DbType::Sqlite) {
            // SQLite does not support SELECT ... FOR UPDATE syntax, because it does
            // table-level locking
        } else {
            *statement += QLatin1Literal(" FOR UPDATE");
        }
    }
}

bool QueryBuilder::exec()
{
    QString statement;
    statement.reserve(1024);
    buildQuery(&statement);

#ifndef QUERYBUILDER_UNITTEST
    auto query = QueryCache::query(statement);
    if (query.has_value()) {
        mQuery = std::move(*query);
    } else {
        mQuery.clear();
        if (!mQuery.prepare(statement)) {
            qCCritical(AKONADISERVER_LOG) << "DATABASE ERROR while PREPARING QUERY:";
            qCCritical(AKONADISERVER_LOG) << "  Error code:" << mQuery.lastError().nativeErrorCode();
            qCCritical(AKONADISERVER_LOG) << "  DB error: " << mQuery.lastError().databaseText();
            qCCritical(AKONADISERVER_LOG) << "  Error text:" << mQuery.lastError().text();
            qCCritical(AKONADISERVER_LOG) << "  Query:" << statement;
            return false;
        }
        QueryCache::insert(statement, mQuery);
    }

    //too heavy debug info but worths to have from time to time
    //qCDebug(AKONADISERVER_LOG) << "Executing query" << statement;
    bool isBatch = false;
    for (int i = 0; i < mBindValues.count(); ++i) {
        mQuery.bindValue(QLatin1Char(':') + QString::number(i), mBindValues[i]);
        if (!isBatch && static_cast<QMetaType::Type>(mBindValues[i].type()) == QMetaType::QVariantList) {
            isBatch = true;
        }
        //qCDebug(AKONADISERVER_LOG) << QString::fromLatin1( ":%1" ).arg( i ) <<  mBindValues[i];
    }

    bool ret;

    if (StorageDebugger::instance()->isSQLDebuggingEnabled()) {
        QTime t;
        t.start();
        if (isBatch) {
            ret = mQuery.execBatch();
        } else {
            ret = mQuery.exec();
        }
        StorageDebugger::instance()->queryExecuted(reinterpret_cast<qint64>(DataStore::self()),
                                                   mQuery, t.elapsed());
    } else {
        StorageDebugger::instance()->incSequence();
        if (isBatch) {
            ret = mQuery.execBatch();
        } else {
            ret = mQuery.exec();
        }
    }

    if (!ret) {
        bool needsRetry = false;
        // Handle transaction deadlocks and timeouts by attempting to replay the transaction.
        if (mDatabaseType == DbType::PostgreSQL) {
            const QString dbError = mQuery.lastError().databaseText();
            if (dbError.contains(QLatin1String("40P01" /* deadlock_detected */))) {
                qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction";
                qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
                needsRetry = true;
            }
        } else if (mDatabaseType == DbType::MySQL) {
            const QString lastErrorStr = mQuery.lastError().nativeErrorCode();
            const int error = lastErrorStr.isEmpty() ? -1 : lastErrorStr.toInt();
            if (error == 1213 /* ER_LOCK_DEADLOCK */) {
                qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction";
                qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
                needsRetry = true;
            } else if (error == 1205 /* ER_LOCK_WAIT_TIMEOUT */) {
                qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction timeout, retrying transaction";
                qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
                // Not sure retrying helps, maybe error is good enough.... but doesn't hurt to retry a few times before giving up.
                needsRetry = true;
            }
        } else if (mDatabaseType == DbType::Sqlite && !DbType::isSystemSQLite(DataStore::self()->database())) {
            const QString lastErrorStr = mQuery.lastError().nativeErrorCode();
            const int error = lastErrorStr.isEmpty() ? -1 : lastErrorStr.toInt();
            if (error == 6 /* SQLITE_LOCKED */) {
                qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction deadlock, retrying transaction";
                qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
                DataStore::self()->doRollback();
                needsRetry = true;
            } else if (error == 5 /* SQLITE_BUSY */) {
                qCWarning(AKONADISERVER_LOG) << "QueryBuilder::exec(): database reported transaction timeout, retrying transaction";
                qCWarning(AKONADISERVER_LOG) << mQuery.lastError().text();
                DataStore::self()->doRollback();
                needsRetry = true;
            }
        } else if (mDatabaseType == DbType::Sqlite) {
            // We can't have a transaction deadlock in SQLite when using driver shipped
            // with Qt, because it does not support concurrent transactions and DataStore
            // serializes them through a global lock.
        }

        if (needsRetry) {
            DataStore::self()->transactionKilledByDB();
            throw DbDeadlockException(mQuery);
        }

        qCCritical(AKONADISERVER_LOG) << "DATABASE ERROR:";
        qCCritical(AKONADISERVER_LOG) << "  Error code:" << mQuery.lastError().nativeErrorCode();
        qCCritical(AKONADISERVER_LOG) << "  DB error: " << mQuery.lastError().databaseText();
        qCCritical(AKONADISERVER_LOG) << "  Error text:" << mQuery.lastError().text();
        qCCritical(AKONADISERVER_LOG) << "  Values:" << mQuery.boundValues();
        qCCritical(AKONADISERVER_LOG) << "  Query:" << statement;
        return false;
    }
#else
    mStatement = statement;
#endif
    return true;
}

void QueryBuilder::addColumns(const QStringList &cols)
{
    mColumns << cols;
}

void QueryBuilder::addColumn(const QString &col)
{
    mColumns << col;
}

void QueryBuilder::addColumn(const Query::Case &caseStmt)
{
    QString query;
    buildCaseStatement(&query, caseStmt);
    mColumns.append(query);
}

void QueryBuilder::addAggregation(const QString &col, const QString &aggregate)
{
    mColumns.append(aggregate + QLatin1Char('(') + col + QLatin1Char(')'));
}

void QueryBuilder::addAggregation(const Query::Case &caseStmt, const QString &aggregate)
{
    QString query(aggregate + QLatin1Char('('));
    buildCaseStatement(&query, caseStmt);
    query += QLatin1Char(')');

    mColumns.append(query);
}

void QueryBuilder::bindValue(QString *query, const QVariant &value)
{
    mBindValues << value;
    *query += QLatin1Char(':') + QString::number(mBindValues.count() - 1);
}

void QueryBuilder::buildWhereCondition(QString *query, const Query::Condition &cond)
{
    if (!cond.isEmpty()) {
        *query += QLatin1String("( ");
        const QLatin1String glue = logicOperatorToString(cond.mCombineOp);
        const Query::Condition::List &subConditions = cond.subConditions();
        for (int i = 0, c = subConditions.size(); i < c; ++i) {
            buildWhereCondition(query, subConditions.at(i));
            if (i + 1 < c) {
                *query += glue;
            }
        }
        *query += QLatin1String(" )");
    } else {
        *query += cond.mColumn;
        *query += compareOperatorToString(cond.mCompareOp);
        if (cond.mComparedColumn.isEmpty()) {
            if (cond.mComparedValue.isValid()) {
                if (cond.mComparedValue.canConvert(QVariant::List)) {
                    *query += QLatin1String("( ");
                    const QVariantList &entries = cond.mComparedValue.toList();
                    Q_ASSERT_X(!entries.isEmpty(),
                               "QueryBuilder::buildWhereCondition()", "No values given for IN condition.");
                    for (int i = 0, c = entries.size(); i < c; ++i) {
                        bindValue(query, entries.at(i));
                        if (i + 1 < c) {
                            *query += QLatin1String(", ");
                        }
                    }
                    *query += QLatin1String(" )");
                } else {
                    bindValue(query, cond.mComparedValue);
                }
            } else {
                *query += QLatin1String("NULL");
            }
        } else {
            *query += cond.mComparedColumn;
        }
    }
}

void QueryBuilder::buildCaseStatement(QString *query, const Query::Case &caseStmt)
{
    *query += QLatin1String("CASE ");
    Q_FOREACH (const auto &whenThen, caseStmt.mWhenThen) {
        *query += QLatin1String("WHEN ");
        buildWhereCondition(query, whenThen.first);    // When
        *query += QLatin1String(" THEN ") + whenThen.second; // then
    }
    if (!caseStmt.mElse.isEmpty()) {
        *query += QLatin1String(" ELSE ") + caseStmt.mElse;
    }
    *query += QLatin1String(" END");
}

void QueryBuilder::setSubQueryMode(Query::LogicOperator op, ConditionType type)
{
    Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
    mRootCondition[type].setSubQueryMode(op);
}

void QueryBuilder::addCondition(const Query::Condition &condition, ConditionType type)
{
    Q_ASSERT(type == WhereCondition || (type == HavingCondition && mType == Select));
    mRootCondition[type].addCondition(condition);
}

void QueryBuilder::addSortColumn(const QString &column, Query::SortOrder order)
{
    mSortColumns << qMakePair(column, order);
}

void QueryBuilder::addGroupColumn(const QString &column)
{
    Q_ASSERT(mType == Select);
    mGroupColumns << column;
}

void QueryBuilder::addGroupColumns(const QStringList &columns)
{
    Q_ASSERT(mType == Select);
    mGroupColumns += columns;
}

void QueryBuilder::setColumnValue(const QString &column, const QVariant &value)
{
    mColumnValues << qMakePair(column, value);
}

void QueryBuilder::setDistinct(bool distinct)
{
    mDistinct = distinct;
}

void QueryBuilder::setLimit(int limit)
{
    mLimit = limit;
}

void QueryBuilder::setIdentificationColumn(const QString &column)
{
    mIdentificationColumn = column;
}

qint64 QueryBuilder::insertId()
{
    if (mDatabaseType == DbType::PostgreSQL) {
        query().next();
        if (mIdentificationColumn.isEmpty()) {
            return 0; // FIXME: Does this make sense?
        }
        return query().record().value(mIdentificationColumn).toLongLong();
    } else {
        const QVariant v = query().lastInsertId();
        if (!v.isValid()) {
            return -1;
        }
        bool ok;
        const qint64 insertId = v.toLongLong(&ok);
        if (!ok) {
            return -1;
        }
        return insertId;
    }
    return -1;
}

void QueryBuilder::setForUpdate(bool forUpdate)
{
    mForUpdate = forUpdate;
}