diff options
Diffstat (limited to 'SQLiteStudio3/coreSQLiteStudio/db')
11 files changed, 162 insertions, 40 deletions
diff --git a/SQLiteStudio3/coreSQLiteStudio/db/abstractdb2.h b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb2.h index 5b95f61..7419f8c 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/abstractdb2.h +++ b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb2.h @@ -600,7 +600,7 @@ bool AbstractDb2<T>::Query::execInternal(const QList<QVariant>& args) } bool ok = (fetchFirst() == SQLITE_OK); - if (ok) + if (ok && !flags.testFlag(Db::Flag::SKIP_DROP_DETECTION)) db->checkForDroppedObject(query); return ok; @@ -643,7 +643,7 @@ bool AbstractDb2<T>::Query::execInternal(const QHash<QString, QVariant>& args) } bool ok = (fetchFirst() == SQLITE_OK); - if (ok) + if (ok && !flags.testFlag(Db::Flag::SKIP_DROP_DETECTION)) db->checkForDroppedObject(query); return ok; diff --git a/SQLiteStudio3/coreSQLiteStudio/db/abstractdb3.h b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb3.h index e7b0a4b..db9bc02 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/abstractdb3.h +++ b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb3.h @@ -68,10 +68,10 @@ class AbstractDb3 : public AbstractDb class Row : public SqlResultsRow { public: - int init(const QStringList& columns, typename T::stmt* stmt); + int init(const QStringList& columns, typename T::stmt* stmt, Db::Flags flags); private: - int getValue(typename T::stmt* stmt, int col, QVariant& value); + int getValue(typename T::stmt* stmt, int col, QVariant& value, Db::Flags flags); }; Query(AbstractDb3<T>* db, const QString& query); @@ -888,7 +888,7 @@ bool AbstractDb3<T>::Query::execInternal(const QList<QVariant>& args) } bool ok = (fetchFirst() == T::OK); - if (ok) + if (ok && !flags.testFlag(Db::Flag::SKIP_DROP_DETECTION)) db->checkForDroppedObject(query); return ok; @@ -942,7 +942,7 @@ bool AbstractDb3<T>::Query::execInternal(const QHash<QString, QVariant>& args) } bool ok = (fetchFirst() == T::OK); - if (ok) + if (ok && !flags.testFlag(Db::Flag::SKIP_DROP_DETECTION)) db->checkForDroppedObject(query); return ok; @@ -1044,7 +1044,7 @@ template <class T> SqlResultsRowPtr AbstractDb3<T>::Query::nextInternal() { Row* row = new Row; - int res = row->init(colNames, stmt); + int res = row->init(colNames, stmt, flags); if (res != T::OK) { delete row; @@ -1131,13 +1131,13 @@ int AbstractDb3<T>::Query::fetchNext() //------------------------------------------------------------------------------------ template <class T> -int AbstractDb3<T>::Query::Row::init(const QStringList& columns, typename T::stmt* stmt) +int AbstractDb3<T>::Query::Row::init(const QStringList& columns, typename T::stmt* stmt, Db::Flags flags) { int res = T::OK; QVariant value; for (int i = 0; i < columns.size(); i++) { - res = getValue(stmt, i, value); + res = getValue(stmt, i, value, flags); if (res != T::OK) return res; @@ -1148,8 +1148,9 @@ int AbstractDb3<T>::Query::Row::init(const QStringList& columns, typename T::stm } template <class T> -int AbstractDb3<T>::Query::Row::getValue(typename T::stmt* stmt, int col, QVariant& value) +int AbstractDb3<T>::Query::Row::getValue(typename T::stmt* stmt, int col, QVariant& value, Db::Flags flags) { + UNUSED(flags); int dataType = T::column_type(stmt, col); switch (dataType) { @@ -1162,12 +1163,12 @@ int AbstractDb3<T>::Query::Row::getValue(typename T::stmt* stmt, int col, QVaria T::column_bytes(stmt, col) ); break; - case T::FLOAT: - value = T::column_double(stmt, col); - break; case T::NULL_TYPE: value = QVariant(QVariant::String); break; + case T::FLOAT: + value = T::column_double(stmt, col); + break; default: value = QString( reinterpret_cast<const QChar*>(T::column_text16(stmt, col)), diff --git a/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.cpp b/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.cpp index f36a3bd..a35856b 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.cpp +++ b/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.cpp @@ -42,6 +42,16 @@ void ChainExecutor::exec() return; } + if (disableForeignKeys && db->getDialect() == Dialect::Sqlite3) + { + SqlQueryPtr result = db->exec("PRAGMA foreign_keys = 0;"); + if (result->isError()) + { + emit failure(db->getErrorCode(), tr("Could not disable foreign keys in the database. Details: %1", "chain executor").arg(db->getErrorText())); + return; + } + } + if (transaction && !db->begin()) { emit failure(db->getErrorCode(), tr("Could not start a database transaction. Details: %1", "chain executor").arg(db->getErrorText())); @@ -75,7 +85,7 @@ void ChainExecutor::executeCurrentSql() return; } - asyncId = db->asyncExec(sqls[currentSqlIndex], queryParams); + asyncId = db->asyncExec(sqls[currentSqlIndex], queryParams, getExecFlags()); } QList<bool> ChainExecutor::getMandatoryQueries() const @@ -122,6 +132,7 @@ void ChainExecutor::executionFailure(int errorCode, const QString& errorText) if (transaction) db->rollback(); + restoreFk(); successfulExecution = false; executionErrors << ExecutionError(errorCode, errorText); emit failure(errorCode, errorText); @@ -135,16 +146,18 @@ void ChainExecutor::executionSuccessful() return; } + restoreFk(); successfulExecution = true; emit success(); } void ChainExecutor::executeSync() { + Db::Flags flags = getExecFlags(); SqlQueryPtr results; - foreach (const QString& sql, sqls) + for (const QString& sql : sqls) { - results = db->exec(sql, queryParams); + results = db->exec(sql, queryParams, flags); if (!handleResults(results)) return; @@ -165,6 +178,46 @@ bool ChainExecutor::handleResults(SqlQueryPtr results) } return true; } + +Db::Flags ChainExecutor::getExecFlags() const +{ + Db::Flags flags; + if (disableObjectDropsDetection) + flags |= Db::Flag::SKIP_DROP_DETECTION; + + return flags; +} + +void ChainExecutor::restoreFk() +{ + if (disableForeignKeys && db->getDialect() == Dialect::Sqlite3) + { + SqlQueryPtr result = db->exec("PRAGMA foreign_keys = 1;"); + if (result->isError()) + qCritical() << "Could not restore foreign keys in the database after chain execution. Details:" << db->getErrorText(); + } +} + +bool ChainExecutor::getDisableObjectDropsDetection() const +{ + return disableObjectDropsDetection; +} + +void ChainExecutor::setDisableObjectDropsDetection(bool value) +{ + disableObjectDropsDetection = value; +} + +bool ChainExecutor::getDisableForeignKeys() const +{ + return disableForeignKeys; +} + +void ChainExecutor::setDisableForeignKeys(bool value) +{ + disableForeignKeys = value; +} + bool ChainExecutor::getSuccessfulExecution() const { return successfulExecution; diff --git a/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.h b/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.h index 2d3e3d3..0afbdb8 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.h +++ b/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.h @@ -198,6 +198,12 @@ class API_EXPORT ChainExecutor : public QObject */ void setParam(const QString& paramName, const QVariant& value); + bool getDisableForeignKeys() const; + void setDisableForeignKeys(bool value); + + bool getDisableObjectDropsDetection() const; + void setDisableObjectDropsDetection(bool value); + private: /** * @brief Executes query defines as the current one. @@ -241,6 +247,10 @@ class API_EXPORT ChainExecutor : public QObject */ bool handleResults(SqlQueryPtr results); + Db::Flags getExecFlags() const; + + void restoreFk(); + /** * @brief Database for execution. */ @@ -315,6 +325,9 @@ class API_EXPORT ChainExecutor : public QObject */ QHash<QString,QVariant> queryParams; + bool disableForeignKeys = false; + bool disableObjectDropsDetection = false; + public slots: /** * @brief Interrupts query execution. diff --git a/SQLiteStudio3/coreSQLiteStudio/db/db.cpp b/SQLiteStudio3/coreSQLiteStudio/db/db.cpp index 7676f30..977812a 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/db.cpp +++ b/SQLiteStudio3/coreSQLiteStudio/db/db.cpp @@ -1,6 +1,7 @@ #include "db.h" #include <QMetaEnum> #include <QDebug> +#include <QDataStream> Db::Db() { diff --git a/SQLiteStudio3/coreSQLiteStudio/db/db.h b/SQLiteStudio3/coreSQLiteStudio/db/db.h index efadd41..4a9e80a 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/db.h +++ b/SQLiteStudio3/coreSQLiteStudio/db/db.h @@ -140,7 +140,7 @@ class API_EXPORT Db : public QObject, public Interruptable { NONE = 0x0, /**< No flags. This is default. */ PRELOAD = 0x1, /**< Preloads all execution results into the results object. Useful for asynchronous execution. */ - NO_LOCK = 0x2 /**< + NO_LOCK = 0x2, /**< * Prevents SQLiteStudio from setting the lock for execution on this base (not the SQLite lock, * just a Db internal lock for multi-threading access to the Db::exec()). This should be used * only in justified circumstances. That is when the Db call has to be done from within the part @@ -148,6 +148,7 @@ class API_EXPORT Db : public QObject, public Interruptable * threads. Justified situation is when you implement Db::initialDbSetup() in the derived class, * or when you implement SqlFunctionPlugin. Don't use it for the usual cases. */ + SKIP_DROP_DETECTION = 0x4, /**< Query execution will not notify about any detected objects dropped by the query. */ }; Q_DECLARE_FLAGS(Flags, Flag) diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.cpp index f8d7fd2..d2e7072 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.cpp +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.cpp @@ -20,6 +20,7 @@ #include "queryexecutorsteps/queryexecutordetectschemaalter.h" #include "queryexecutorsteps/queryexecutorvaluesmode.h" #include "common/unused.h" +#include "log.h" #include <QMutexLocker> #include <QDateTime> #include <QThreadPool> @@ -94,13 +95,16 @@ void QueryExecutor::executeChain() bool result; foreach (QueryExecutorStep* currentStep, executionChain) { - if (interrupted) + if (isInterrupted()) { stepFailed(currentStep); return; } + logExecutorStep(currentStep); result = currentStep->exec(); + logExecutorAfterStep(context->processedQuery); + if (!result) { stepFailed(currentStep); @@ -127,7 +131,7 @@ void QueryExecutor::stepFailed(QueryExecutorStep* currentStep) clearChain(); - if (interrupted) + if (isInterrupted()) { executionInProgress = false; emit executionFailed(SqlErrorCode::INTERRUPTED, tr("Execution interrupted.")); @@ -240,6 +244,7 @@ void QueryExecutor::interrupt() return; } + QMutexLocker lock(&interruptionMutex); interrupted = true; db->asyncInterrupt(); } @@ -255,11 +260,11 @@ bool QueryExecutor::countResults() if (asyncMode) { // Start asynchronous results counting query - resultsCountingAsyncId = db->asyncExec(context->countingQuery, context->queryParameters); + resultsCountingAsyncId = db->asyncExec(context->countingQuery, context->queryParameters, Db::Flag::NO_LOCK); } else { - SqlQueryPtr results = db->exec(context->countingQuery, context->queryParameters); + SqlQueryPtr results = db->exec(context->countingQuery, context->queryParameters, Db::Flag::NO_LOCK); context->totalRowsReturned = results->getSingleCell().toLongLong(); context->totalPages = (int)qCeil(((double)(context->totalRowsReturned)) / ((double)getResultsPerPage())); @@ -568,6 +573,11 @@ void QueryExecutor::setForceSimpleMode(bool value) forceSimpleMode = value; } +bool QueryExecutor::isInterrupted() const +{ + QMutexLocker lock(&interruptionMutex); + return interrupted; +} const QStringList& QueryExecutor::getRequiredDbAttaches() const { @@ -586,24 +596,29 @@ void QueryExecutor::setNoMetaColumns(bool value) void QueryExecutor::handleErrorsFromSmartAndSimpleMethods(SqlQueryPtr results) { - UNUSED(results); // It turns out that currently smart execution error has more sense to be displayed to user than the simple execution error, // so we're ignoring error from simple method, because it's usually misleading. - // The case when simple method error is more true than smart method error is very rare nowdays. + // The case when simple method error is more true than smart method error is very rare nowdays (but happens sometimes, + // therefore we need to check code from smart execution, before deciding which one to use). // Just rename attach names in the message. - QString msg = context->errorMessageFromSmartExecution; + bool useSmartError = context->errorCodeFromSmartExecution != 0; + QString msg = useSmartError ? context->errorMessageFromSmartExecution : results->getErrorText(); + int code = useSmartError ? context->errorCodeFromSmartExecution : results->getErrorCode(); QString match; QString replaceName; Dialect dialect = db->getDialect(); - for (const QString& attachName : context->dbNameToAttach.rightValues()) + if (useSmartError) { - match = attachName + "."; - replaceName = wrapObjIfNeeded(context->dbNameToAttach.valueByRight(attachName), dialect) + "."; - while (msg.contains(match)) - msg.replace(match, replaceName); + for (const QString& attachName : context->dbNameToAttach.rightValues()) + { + match = attachName + "."; + replaceName = wrapObjIfNeeded(context->dbNameToAttach.valueByRight(attachName), dialect) + "."; + while (msg.contains(match)) + msg.replace(match, replaceName); + } } - error(context->errorCodeFromSmartExecution, msg); + error(code, msg); } void QueryExecutor::releaseResultsAndCleanup() diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.h index 95c6a0c..eddbe8c 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.h +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.h @@ -1032,6 +1032,8 @@ class API_EXPORT QueryExecutor : public QObject, public QRunnable bool getForceSimpleMode() const; void setForceSimpleMode(bool value); + bool isInterrupted() const; + private: /** * @brief Executes query. @@ -1161,7 +1163,8 @@ class API_EXPORT QueryExecutor : public QObject, public QRunnable * The state of "execution in progress" is the only value synchronized between threads. * It makes sure that single QueryExecutor will execute only one query at the time. */ - QMutex executionMutex; + mutable QMutex executionMutex; + mutable QMutex interruptionMutex; /** * @brief Query to execute as defined by the user. diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.cpp index fceea3f..934a20d 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.cpp +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.cpp @@ -57,8 +57,39 @@ bool QueryExecutorCellSize::applyDataLimit(SqliteSelect* select, SqliteSelect::C TokenList QueryExecutorCellSize::getLimitTokens(const QueryExecutor::ResultColumnPtr& resCol) { + // CASE WHEN typeof(alias) IN ('real', 'integer', 'numeric', 'null') THEN alias ELSE substr(alias, 1, limit) END TokenList newTokens; - newTokens << TokenPtr::create(Token::OTHER, "substr") + newTokens << TokenPtr::create(Token::KEYWORD, "CASE") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::KEYWORD, "WHEN") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::OTHER, "typeof") + << TokenPtr::create(Token::PAR_LEFT, "(") + << TokenPtr::create(Token::OTHER, resCol->queryExecutorAlias) + << TokenPtr::create(Token::PAR_RIGHT, ")") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::KEYWORD, "IN") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::PAR_LEFT, "(") + << TokenPtr::create(Token::STRING, "'real'") + << TokenPtr::create(Token::OPERATOR, ",") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::STRING, "'integer'") + << TokenPtr::create(Token::OPERATOR, ",") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::STRING, "'numeric'") + << TokenPtr::create(Token::OPERATOR, ",") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::STRING, "'null'") + << TokenPtr::create(Token::PAR_RIGHT, ")") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::KEYWORD, "THEN") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::OTHER, resCol->queryExecutorAlias) + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::KEYWORD, "ELSE") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::OTHER, "substr") << TokenPtr::create(Token::PAR_LEFT, "(") << TokenPtr::create(Token::OTHER, resCol->queryExecutorAlias) << TokenPtr::create(Token::OPERATOR, ",") @@ -69,6 +100,8 @@ TokenList QueryExecutorCellSize::getLimitTokens(const QueryExecutor::ResultColum << TokenPtr::create(Token::INTEGER, QString::number(queryExecutor->getDataLengthLimit())) << TokenPtr::create(Token::PAR_RIGHT, ")") << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::KEYWORD, "END") + << TokenPtr::create(Token::SPACE, " ") << TokenPtr::create(Token::KEYWORD, "AS") << TokenPtr::create(Token::SPACE, " ") << TokenPtr::create(Token::OTHER, resCol->queryExecutorAlias); diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.cpp index 287a8ce..344f2e5 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.cpp +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.cpp @@ -71,12 +71,13 @@ bool QueryExecutorColumns::exec() i++; } +// qDebug() << "before: " << context->processedQuery; // Update query select->rebuildTokens(); wrapWithAliasedColumns(select.data()); updateQueries(); -// qDebug() << context->processedQuery; +// qDebug() << "after: " << context->processedQuery; return true; } @@ -130,6 +131,9 @@ SqliteSelect::Core::ResultColumn* QueryExecutorColumns::getResultColumnForSelect SqliteSelect::Core::ResultColumn* selectResultColumn = new SqliteSelect::Core::ResultColumn(); QString colString = resultColumn->column; + if (col.aliasDefinedInSubQuery) // #2931 + colString = col.alias; + if (!resultColumn->expression) colString = wrapObjIfNeeded(colString, dialect); @@ -167,16 +171,11 @@ SqliteSelect::Core::ResultColumn* QueryExecutorColumns::getResultColumnForSelect } } + selectResultColumn->asKw = true; if (!col.alias.isNull()) - { - selectResultColumn->asKw = true; selectResultColumn->alias = col.alias; - } else - { - selectResultColumn->asKw = true; selectResultColumn->alias = resultColumn->queryExecutorAlias; - } return selectResultColumn; } diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.cpp index a954da7..3036796 100644 --- a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.cpp +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.cpp @@ -41,11 +41,13 @@ bool QueryExecutorExecute::executeQueries() if (context->preloadResults) flags |= Db::Flag::PRELOAD; + QString queryStr; int queryCount = context->parsedQueries.size(); for (const SqliteQueryPtr& query : context->parsedQueries) { + queryStr = query->detokenize(); bindParamsForQuery = getBindParamsForQuery(query); - results = db->prepare(query->detokenize()); + results = db->prepare(queryStr); results->setArgs(bindParamsForQuery); results->setFlags(flags); @@ -56,6 +58,7 @@ bool QueryExecutorExecute::executeQueries() if (isBeginTransaction(query->queryType)) rowsAffectedBeforeTransaction.push(context->rowsAffected); +// qDebug() << "Executing query:" << queryStr; results->execute(); if (results->isError()) |