diff options
Diffstat (limited to 'SQLiteStudio3/coreSQLiteStudio/db')
61 files changed, 10756 insertions, 0 deletions
diff --git a/SQLiteStudio3/coreSQLiteStudio/db/abstractdb.cpp b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb.cpp new file mode 100644 index 0000000..56275aa --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb.cpp @@ -0,0 +1,879 @@ +#include "abstractdb.h" +#include "services/dbmanager.h" +#include "common/utils.h" +#include "asyncqueryrunner.h" +#include "sqlresultsrow.h" +#include "common/utils_sql.h" +#include "services/config.h" +#include "sqlerrorresults.h" +#include "sqlerrorcodes.h" +#include "services/notifymanager.h" +#include "log.h" +#include "parser/lexer.h" +#include <QDebug> +#include <QTime> +#include <QWriteLocker> +#include <QReadLocker> +#include <QThreadPool> +#include <QMetaEnum> +#include <QtConcurrent/QtConcurrentRun> + +quint32 AbstractDb::asyncId = 1; + +AbstractDb::AbstractDb(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions) : + name(name), path(path), connOptions(connOptions) +{ +} + +AbstractDb::~AbstractDb() +{ +} + +bool AbstractDb::open() +{ + bool res = isOpen() || openQuiet(); + if (res) + emit connected(); + + return res; +} + +bool AbstractDb::close() +{ + bool deny = false; + emit aboutToDisconnect(deny); + if (deny) + return false; + + bool res = !isOpen() || closeQuiet(); + if (res) + emit disconnected(); + + return res; +} + +bool AbstractDb::openQuiet() +{ + QWriteLocker locker(&dbOperLock); + QWriteLocker connectionLocker(&connectionStateLock); + return openAndSetup(); +} + +bool AbstractDb::closeQuiet() +{ + QWriteLocker locker(&dbOperLock); + QWriteLocker connectionLocker(&connectionStateLock); + interruptExecution(); + bool res = closeInternal(); + clearAttaches(); + registeredFunctions.clear(); + registeredCollations.clear(); + if (FUNCTIONS) // FUNCTIONS is already null when closing db while closing entire app + disconnect(FUNCTIONS, SIGNAL(functionListChanged()), this, SLOT(registerAllFunctions())); + + return res; +} + +bool AbstractDb::openForProbing() +{ + QWriteLocker locker(&dbOperLock); + QWriteLocker connectionLocker(&connectionStateLock); + bool res = openInternal(); + if (!res) + return res; + + // Implementation specific initialization + initAfterOpen(); + return res; +} + +void AbstractDb::registerAllFunctions() +{ + for (const RegisteredFunction& regFn : registeredFunctions) + { + if (!deregisterFunction(regFn.name, regFn.argCount)) + qWarning() << "Failed to deregister custom SQL function:" << regFn.name; + } + + registeredFunctions.clear(); + + RegisteredFunction regFn; + for (FunctionManager::ScriptFunction* fnPtr : FUNCTIONS->getScriptFunctionsForDatabase(getName())) + { + regFn.argCount = fnPtr->undefinedArgs ? -1 : fnPtr->arguments.count(); + regFn.name = fnPtr->name; + regFn.type = fnPtr->type; + registerFunction(regFn); + } + + for (FunctionManager::NativeFunction* fnPtr : FUNCTIONS->getAllNativeFunctions()) + { + regFn.argCount = fnPtr->undefinedArgs ? -1 : fnPtr->arguments.count(); + regFn.name = fnPtr->name; + regFn.type = fnPtr->type; + registerFunction(regFn); + } + + disconnect(FUNCTIONS, SIGNAL(functionListChanged()), this, SLOT(registerAllFunctions())); + connect(FUNCTIONS, SIGNAL(functionListChanged()), this, SLOT(registerAllFunctions())); +} + +void AbstractDb::registerAllCollations() +{ + foreach (const QString& name, registeredCollations) + { + if (!deregisterCollation(name)) + qWarning() << "Failed to deregister custom collation:" << name; + } + + registeredCollations.clear(); + + foreach (const CollationManager::CollationPtr& collPtr, COLLATIONS->getCollationsForDatabase(getName())) + registerCollation(collPtr->name); + + disconnect(COLLATIONS, SIGNAL(collationListChanged()), this, SLOT(registerAllCollations())); + connect(COLLATIONS, SIGNAL(collationListChanged()), this, SLOT(registerAllCollations())); +} + +bool AbstractDb::isOpen() +{ + // We use separate mutex for connection state to avoid situations, when some query is being executed, + // and we cannot check if database is open, which is not invasive method call. + QReadLocker connectionLocker(&connectionStateLock); + return isOpenInternal(); +} + +QString AbstractDb::generateUniqueDbName(bool lock) +{ + if (lock) + { + QReadLocker locker(&dbOperLock); + return generateUniqueDbNameNoLock(); + } + else + { + return generateUniqueDbNameNoLock(); + } +} + +QString AbstractDb::generateUniqueDbNameNoLock() +{ + SqlQueryPtr results = exec("PRAGMA database_list;", Db::Flag::NO_LOCK); + if (results->isError()) + { + qWarning() << "Could not get PRAGMA database_list. Falling back to internal db list. Error was:" << results->getErrorText(); + return generateUniqueName("attached", attachedDbMap.leftValues()); + } + + QStringList existingDatabases; + foreach (SqlResultsRowPtr row, results->getAll()) + existingDatabases << row->value("name").toString(); + + return generateUniqueName("attached", existingDatabases); +} + +ReadWriteLocker::Mode AbstractDb::getLockingMode(const QString &query, Flags flags) +{ + return ReadWriteLocker::getMode(query, getDialect(), flags.testFlag(Flag::NO_LOCK)); +} + +QString AbstractDb::getName() +{ + return name; +} + +QString AbstractDb::getPath() +{ + return path; +} + +quint8 AbstractDb::getVersion() +{ + return version; +} + +Dialect AbstractDb::getDialect() +{ + if (version == 2) + return Dialect::Sqlite2; + else + return Dialect::Sqlite3; +} + +QString AbstractDb::getEncoding() +{ + bool doClose = false; + if (!isOpen()) + { + if (!openQuiet()) + return QString::null; + + doClose = true; + } + QString encoding = exec("PRAGMA encoding;")->getSingleCell().toString(); + if (doClose) + closeQuiet(); + + return encoding; +} + +QHash<QString, QVariant>& AbstractDb::getConnectionOptions() +{ + return connOptions; +} + +void AbstractDb::setName(const QString& value) +{ + if (isOpen()) + { + qWarning() << "Tried to change database's name while the database was open."; + return; + } + name = value; +} + +void AbstractDb::setPath(const QString& value) +{ + if (isOpen()) + { + qWarning() << "Tried to change database's file path while the database was open."; + return; + } + path = value; +} + +void AbstractDb::setConnectionOptions(const QHash<QString, QVariant>& value) +{ + if (isOpen()) + { + qWarning() << "Tried to change database's connection options while the database was open."; + return; + } + connOptions = value; +} + +SqlQueryPtr AbstractDb::exec(const QString& query, AbstractDb::Flags flags) +{ + return exec(query, QList<QVariant>(), flags); +} + +SqlQueryPtr AbstractDb::exec(const QString& query, const QVariant& arg) +{ + return exec(query, {arg}); +} + +SqlQueryPtr AbstractDb::exec(const QString& query, std::initializer_list<QVariant> argList) +{ + return exec(query, QList<QVariant>(argList)); +} + +SqlQueryPtr AbstractDb::exec(const QString &query, std::initializer_list<std::pair<QString, QVariant> > argMap) +{ + return exec(query, QHash<QString,QVariant>(argMap)); +} + +void AbstractDb::asyncExec(const QString &query, const QList<QVariant> &args, AbstractDb::QueryResultsHandler resultsHandler, AbstractDb::Flags flags) +{ + quint32 asyncId = asyncExec(query, args, flags); + resultHandlers[asyncId] = resultsHandler; +} + +void AbstractDb::asyncExec(const QString &query, const QHash<QString, QVariant> &args, AbstractDb::QueryResultsHandler resultsHandler, AbstractDb::Flags flags) +{ + quint32 asyncId = asyncExec(query, args, flags); + resultHandlers[asyncId] = resultsHandler; +} + +void AbstractDb::asyncExec(const QString &query, AbstractDb::QueryResultsHandler resultsHandler, AbstractDb::Flags flags) +{ + quint32 asyncId = asyncExec(query, flags); + resultHandlers[asyncId] = resultsHandler; +} + +SqlQueryPtr AbstractDb::exec(const QString &query, const QList<QVariant>& args, Flags flags) +{ + return execListArg(query, args, flags); +} + +SqlQueryPtr AbstractDb::exec(const QString& query, const QHash<QString, QVariant>& args, AbstractDb::Flags flags) +{ + return execHashArg(query, args, flags); +} + +SqlQueryPtr AbstractDb::execHashArg(const QString& query, const QHash<QString,QVariant>& args, Flags flags) +{ + if (!isOpenInternal()) + return SqlQueryPtr(new SqlErrorResults(SqlErrorCode::DB_NOT_OPEN, tr("Cannot execute query on closed database."))); + + logSql(this, query, args, flags); + QString newQuery = query; + SqlQueryPtr queryStmt = prepare(newQuery); + queryStmt->setArgs(args); + queryStmt->setFlags(flags); + queryStmt->execute(); + + if (flags.testFlag(Flag::PRELOAD)) + queryStmt->preload(); + + return queryStmt; +} + +SqlQueryPtr AbstractDb::execListArg(const QString& query, const QList<QVariant>& args, Flags flags) +{ + if (!isOpenInternal()) + return SqlQueryPtr(new SqlErrorResults(SqlErrorCode::DB_NOT_OPEN, tr("Cannot execute query on closed database."))); + + logSql(this, query, args, flags); + QString newQuery = query; + SqlQueryPtr queryStmt = prepare(newQuery); + queryStmt->setArgs(args); + queryStmt->setFlags(flags); + queryStmt->execute(); + + if (flags.testFlag(Flag::PRELOAD)) + queryStmt->preload(); + + return queryStmt; +} + +bool AbstractDb::openAndSetup() +{ + bool result = openInternal(); + if (!result) + return result; + + // When this is an internal configuration database + if (connOptions.contains(DB_PURE_INIT)) + return true; + + // Implementation specific initialization + initAfterOpen(); + + // Custom SQL functions + registerAllFunctions(); + + // Custom collations + registerAllCollations(); + + return result; +} + +void AbstractDb::initAfterOpen() +{ +} + +void AbstractDb::checkForDroppedObject(const QString& query) +{ + TokenList tokens = Lexer::tokenize(query, getDialect()); + tokens.trim(Token::OPERATOR, ";"); + if (tokens.size() == 0) + return; + + if (tokens[0]->type != Token::KEYWORD || tokens.first()->value.toUpper() != "DROP") + return; + + tokens.removeFirst(); // remove "DROP" from front + tokens.trimLeft(); // remove whitespaces and comments from front + if (tokens.size() == 0) + { + qWarning() << "Successful execution of DROP, but after removing DROP from front of the query, nothing has left. Original query:" << query; + return; + } + + QString type = tokens.first()->value.toUpper(); + + // Now go to the first ID in the tokens + while (tokens.size() > 0 && tokens.first()->type != Token::OTHER) + tokens.removeFirst(); + + if (tokens.size() == 0) + { + qWarning() << "Successful execution of DROP, but after removing DROP and non-ID tokens from front of the query, nothing has left. Original query:" << query; + return; + } + + QString database = "main"; + QString object; + + if (tokens.size() > 1) + { + database = tokens.first()->value; + object = tokens.last()->value; + } + else + object = tokens.first()->value; + + object = stripObjName(object, getDialect()); + + if (type == "TABLE") + emit dbObjectDeleted(database, object, DbObjectType::TABLE); + else if (type == "INDEX") + emit dbObjectDeleted(database, object, DbObjectType::INDEX); + else if (type == "TRIGGER") + emit dbObjectDeleted(database, object, DbObjectType::TRIGGER); + else if (type == "VIEW") + emit dbObjectDeleted(database, object, DbObjectType::VIEW); + else + qWarning() << "Unknown object type dropped:" << type; +} + +bool AbstractDb::registerCollation(const QString& name) +{ + if (registeredCollations.contains(name)) + { + qCritical() << "Collation" << name << "is already registered!" + << "It should already be deregistered while call to register is being made."; + return false; + } + + if (registerCollationInternal(name)) + { + registeredCollations << name; + return true; + } + + qCritical() << "Could not register collation:" << name; + return false; +} + +bool AbstractDb::deregisterCollation(const QString& name) +{ + if (!registeredCollations.contains(name)) + { + qCritical() << "Collation" << name << "not registered!" + << "It should already registered while call to deregister is being made."; + return false; + } + + if (deregisterCollationInternal(name)) + { + registeredCollations.removeOne(name); + return true; + } + qWarning() << "Could not deregister collation:" << name; + return false; +} + +bool AbstractDb::isCollationRegistered(const QString& name) +{ + return registeredCollations.contains(name); +} + +QHash<QString, QVariant> AbstractDb::getAggregateContext(void* memPtr) +{ + if (!memPtr) + { + qCritical() << "Could not allocate aggregate context."; + return QHash<QString, QVariant>(); + } + + QHash<QString,QVariant>** aggCtxPtr = reinterpret_cast<QHash<QString,QVariant>**>(memPtr); + if (!*aggCtxPtr) + *aggCtxPtr = new QHash<QString,QVariant>(); + + return **aggCtxPtr; +} + +void AbstractDb::setAggregateContext(void* memPtr, const QHash<QString, QVariant>& aggregateContext) +{ + if (!memPtr) + { + qCritical() << "Could not extract aggregate context."; + return; + } + + QHash<QString,QVariant>** aggCtxPtr = reinterpret_cast<QHash<QString,QVariant>**>(memPtr); + **aggCtxPtr = aggregateContext; +} + +void AbstractDb::releaseAggregateContext(void* memPtr) +{ + if (!memPtr) + { + qCritical() << "Could not release aggregate context."; + return; + } + + QHash<QString,QVariant>** aggCtxPtr = reinterpret_cast<QHash<QString,QVariant>**>(memPtr); + delete *aggCtxPtr; +} + +QVariant AbstractDb::evaluateScalar(void* dataPtr, const QList<QVariant>& argList, bool& ok) +{ + if (!dataPtr) + return QVariant(); + + FunctionUserData* userData = reinterpret_cast<FunctionUserData*>(dataPtr); + + return FUNCTIONS->evaluateScalar(userData->name, userData->argCount, argList, userData->db, ok); +} + +void AbstractDb::evaluateAggregateStep(void* dataPtr, QHash<QString, QVariant>& aggregateContext, QList<QVariant> argList) +{ + if (!dataPtr) + return; + + FunctionUserData* userData = reinterpret_cast<FunctionUserData*>(dataPtr); + + QHash<QString,QVariant> storage = aggregateContext["storage"].toHash(); + if (!aggregateContext.contains("initExecuted")) + { + FUNCTIONS->evaluateAggregateInitial(userData->name, userData->argCount, userData->db, storage); + aggregateContext["initExecuted"] = true; + } + + FUNCTIONS->evaluateAggregateStep(userData->name, userData->argCount, argList, userData->db, storage); + aggregateContext["storage"] = storage; +} + +QVariant AbstractDb::evaluateAggregateFinal(void* dataPtr, QHash<QString, QVariant>& aggregateContext, bool& ok) +{ + if (!dataPtr) + return QVariant(); + + FunctionUserData* userData = reinterpret_cast<FunctionUserData*>(dataPtr); + QHash<QString,QVariant> storage = aggregateContext["storage"].toHash(); + + return FUNCTIONS->evaluateAggregateFinal(userData->name, userData->argCount, userData->db, ok, storage); +} + +quint32 AbstractDb::asyncExec(const QString &query, Flags flags) +{ + AsyncQueryRunner* runner = new AsyncQueryRunner(query, QList<QVariant>(), flags); + return asyncExec(runner); +} + +quint32 AbstractDb::asyncExec(const QString& query, const QHash<QString, QVariant>& args, AbstractDb::Flags flags) +{ + AsyncQueryRunner* runner = new AsyncQueryRunner(query, args, flags); + return asyncExec(runner); +} + +quint32 AbstractDb::asyncExec(const QString& query, const QList<QVariant>& args, AbstractDb::Flags flags) +{ + AsyncQueryRunner* runner = new AsyncQueryRunner(query, args, flags); + return asyncExec(runner); +} + +quint32 AbstractDb::asyncExec(AsyncQueryRunner *runner) +{ + quint32 asyncId = generateAsyncId(); + runner->setDb(this); + runner->setAsyncId(asyncId); + + connect(runner, SIGNAL(finished(AsyncQueryRunner*)), + this, SLOT(asyncQueryFinished(AsyncQueryRunner*))); + + QThreadPool::globalInstance()->start(runner); + + return asyncId; +} + +void AbstractDb::asyncQueryFinished(AsyncQueryRunner *runner) +{ + // Extract everything from the runner + SqlQueryPtr results = runner->getResults(); + quint32 asyncId = runner->getAsyncId(); + delete runner; + + if (handleResultInternally(asyncId, results)) + return; + + emit asyncExecFinished(asyncId, results); + + if (isReadable() && isWritable()) + emit idle(); +} + +QString AbstractDb::attach(Db* otherDb, bool silent) +{ + QWriteLocker locker(&dbOperLock); + if (!isOpenInternal()) + return QString::null; + + if (attachedDbMap.containsRight(otherDb)) + { + attachCounter[otherDb]++; + return attachedDbMap.valueByRight(otherDb); + } + + QString attName = generateUniqueDbName(false); + SqlQueryPtr results = exec(getAttachSql(otherDb, attName), Flag::NO_LOCK); + if (results->isError()) + { + if (!silent) + notifyError(tr("Error attaching database %1: %2").arg(otherDb->getName()).arg(results->getErrorText())); + else + qDebug() << QString("Error attaching database %1: %2").arg(otherDb->getName()).arg(results->getErrorText()); + + return QString::null; + } + + attachedDbMap.insert(attName, otherDb); + + emit attached(otherDb); + return attName; +} + +void AbstractDb::detach(Db* otherDb) +{ + QWriteLocker locker(&dbOperLock); + + if (!isOpenInternal()) + return; + + detachInternal(otherDb); +} + +void AbstractDb::detachInternal(Db* otherDb) +{ + if (!attachedDbMap.containsRight(otherDb)) + return; + + if (attachCounter.contains(otherDb)) + { + attachCounter[otherDb]--; + return; + } + + exec(QString("DETACH %1;").arg(attachedDbMap.valueByRight(otherDb)), Flag::NO_LOCK); + attachedDbMap.removeRight(otherDb); + emit detached(otherDb); +} + +void AbstractDb::clearAttaches() +{ + attachedDbMap.clear(); + attachCounter.clear(); +} + +void AbstractDb::detachAll() +{ + QWriteLocker locker(&dbOperLock); + + if (!isOpenInternal()) + return; + + foreach (Db* db, attachedDbMap.rightValues()) + detachInternal(db); +} + +const QHash<Db *, QString> &AbstractDb::getAttachedDatabases() +{ + QReadLocker locker(&dbOperLock); + return attachedDbMap.toInvertedQHash(); +} + +QSet<QString> AbstractDb::getAllAttaches() +{ + QReadLocker locker(&dbOperLock); + QSet<QString> attaches = attachedDbMap.leftValues().toSet(); + // TODO query database for attached databases and unite them here + return attaches; +} + +QString AbstractDb::getUniqueNewObjectName(const QString &attachedDbName) +{ + QString dbName = getPrefixDb(attachedDbName, getDialect()); + + QSet<QString> existingNames; + SqlQueryPtr results = exec(QString("SELECT name FROM %1.sqlite_master").arg(dbName)); + + foreach (SqlResultsRowPtr row, results->getAll()) + existingNames << row->value(0).toString(); + + return randStrNotIn(16, existingNames, false); +} + +QString AbstractDb::getErrorText() +{ + QReadLocker locker(&dbOperLock); + return getErrorTextInternal(); +} + +int AbstractDb::getErrorCode() +{ + QReadLocker locker(&dbOperLock); + return getErrorCodeInternal(); +} + +bool AbstractDb::initAfterCreated() +{ + bool isOpenBefore = isOpen(); + if (!isOpenBefore) + { + if (!openForProbing()) + { + qWarning() << "Could not open database for initAfterCreated(). Database:" << name; + return false; + } + } + + // SQLite version + QVariant value = exec("SELECT sqlite_version()")->getSingleCell(); + version = value.toString().mid(0, 1).toUInt(); + + if (!isOpenBefore) + closeQuiet(); + + return true; +} + +void AbstractDb::setTimeout(int secs) +{ + timeout = secs; +} + +int AbstractDb::getTimeout() const +{ + return timeout; +} + +bool AbstractDb::isValid() const +{ + return true; +} + +QString AbstractDb::getAttachSql(Db* otherDb, const QString& generatedAttachName) +{ + return QString("ATTACH '%1' AS %2;").arg(otherDb->getPath(), generatedAttachName); +} + +quint32 AbstractDb::generateAsyncId() +{ + if (asyncId > 4000000000) + asyncId = 1; + + return asyncId++; +} + +bool AbstractDb::begin() +{ + QWriteLocker locker(&dbOperLock); + + if (!isOpenInternal()) + return false; + + SqlQueryPtr results = exec("BEGIN;", Flag::NO_LOCK); + if (results->isError()) + { + qCritical() << "Error while starting a transaction: " << results->getErrorCode() << results->getErrorText(); + return false; + } + + return true; +} + +bool AbstractDb::commit() +{ + QWriteLocker locker(&dbOperLock); + + if (!isOpenInternal()) + return false; + + SqlQueryPtr results = exec("COMMIT;", Flag::NO_LOCK); + if (results->isError()) + { + qCritical() << "Error while commiting a transaction: " << results->getErrorCode() << results->getErrorText(); + return false; + } + + return true; +} + +bool AbstractDb::rollback() +{ + QWriteLocker locker(&dbOperLock); + + if (!isOpenInternal()) + return false; + + SqlQueryPtr results = exec("ROLLBACK;", Flag::NO_LOCK); + if (results->isError()) + { + qCritical() << "Error while rolling back a transaction: " << results->getErrorCode() << results->getErrorText(); + return false; + } + + return true; +} + +void AbstractDb::interrupt() +{ + // Lock connection state to forbid closing db before interrupt() returns. + // This is required by SQLite. + QWriteLocker locker(&connectionStateLock); + interruptExecution(); +} + +void AbstractDb::asyncInterrupt() +{ + QtConcurrent::run(this, &AbstractDb::interrupt); +} + +bool AbstractDb::isReadable() +{ + bool res = dbOperLock.tryLockForRead(); + if (res) + dbOperLock.unlock(); + + return res; +} + +bool AbstractDb::isWritable() +{ + bool res = dbOperLock.tryLockForWrite(); + if (res) + dbOperLock.unlock(); + + return res; +} + +AttachGuard AbstractDb::guardedAttach(Db* otherDb, bool silent) +{ + QString attachName = attach(otherDb, silent); + return AttachGuard::create(this, otherDb, attachName); +} + +bool AbstractDb::handleResultInternally(quint32 asyncId, SqlQueryPtr results) +{ + if (!resultHandlers.contains(asyncId)) + return false; + + resultHandlers[asyncId](results); + resultHandlers.remove(asyncId); + + return true; +} + +void AbstractDb::registerFunction(const AbstractDb::RegisteredFunction& function) +{ + if (registeredFunctions.contains(function)) + return; // native function was overwritten by script function + + bool successful = false; + switch (function.type) + { + case FunctionManager::ScriptFunction::SCALAR: + successful = registerScalarFunction(function.name, function.argCount); + break; + case FunctionManager::ScriptFunction::AGGREGATE: + successful = registerAggregateFunction(function.name, function.argCount); + break; + } + + if (successful) + registeredFunctions << function; + else + qCritical() << "Could not register SQL function:" << function.name << function.argCount << function.type; +} + +int qHash(const AbstractDb::RegisteredFunction& fn) +{ + return qHash(fn.name) ^ fn.argCount ^ fn.type; +} + +bool operator==(const AbstractDb::RegisteredFunction& fn1, const AbstractDb::RegisteredFunction& fn2) +{ + return fn1.name == fn2.name && fn1.argCount == fn2.argCount && fn1.type == fn2.type; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/abstractdb.h b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb.h new file mode 100644 index 0000000..89edf03 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb.h @@ -0,0 +1,485 @@ +#ifndef ABSTRACTDB_H +#define ABSTRACTDB_H + +#include "returncode.h" +#include "sqlquery.h" +#include "dialect.h" +#include "db/db.h" +#include "common/bihash.h" +#include "services/functionmanager.h" +#include "common/readwritelocker.h" +#include "coreSQLiteStudio_global.h" +#include <QObject> +#include <QVariant> +#include <QList> +#include <QHash> +#include <QSet> +#include <QReadWriteLock> +#include <QRunnable> +#include <QStringList> + +class AsyncQueryRunner; + +/** + * @brief Base database logic implementation. + * + * This class implements common base logic for all database implementations. It's still abstract class + * and needs further implementation to be usable. + */ +class API_EXPORT AbstractDb : public Db +{ + Q_OBJECT + + public: + /** + * @brief Initializes database object. + * @param name Name for the database. + * @param path File path of the database. + * @param connOptions Connection options. See below for details. + * + * Connection options are handled individually by the derived database implementation class. + * It can be password for encrypted databases, read-only access flag, etc. + */ + AbstractDb(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions); + + virtual ~AbstractDb(); + + bool isOpen(); + QString getName(); + QString getPath(); + quint8 getVersion(); + Dialect getDialect(); + QString getEncoding(); + QHash<QString,QVariant>& getConnectionOptions(); + void setName(const QString& value); + void setPath(const QString& value); + void setConnectionOptions(const QHash<QString,QVariant>& value); + SqlQueryPtr exec(const QString& query, const QList<QVariant> &args, Flags flags = Flag::NONE); + SqlQueryPtr exec(const QString& query, const QHash<QString, QVariant>& args, Flags flags = Flag::NONE); + SqlQueryPtr exec(const QString &query, Db::Flags flags = Flag::NONE); + SqlQueryPtr exec(const QString &query, const QVariant &arg); + SqlQueryPtr exec(const QString &query, std::initializer_list<QVariant> argList); + SqlQueryPtr exec(const QString &query, std::initializer_list<std::pair<QString,QVariant>> argMap); + void asyncExec(const QString& query, const QList<QVariant>& args, QueryResultsHandler resultsHandler, Flags flags = Flag::NONE); + void asyncExec(const QString& query, const QHash<QString, QVariant>& args, QueryResultsHandler resultsHandler, Flags flags = Flag::NONE); + void asyncExec(const QString& query, QueryResultsHandler resultsHandler, Flags flags = Flag::NONE); + quint32 asyncExec(const QString& query, const QList<QVariant>& args, Flags flags = Flag::NONE); + quint32 asyncExec(const QString& query, const QHash<QString, QVariant>& args, Flags flags = Flag::NONE); + quint32 asyncExec(const QString& query, Flags flags = Flag::NONE); + bool begin(); + bool commit(); + bool rollback(); + void interrupt(); + void asyncInterrupt(); + bool isReadable(); + bool isWritable(); + AttachGuard guardedAttach(Db* otherDb, bool silent = false); + QString attach(Db* otherDb, bool silent = false); + void detach(Db* otherDb); + void detachAll(); + const QHash<Db*,QString>& getAttachedDatabases(); + QSet<QString> getAllAttaches(); + QString getUniqueNewObjectName(const QString& attachedDbName = QString()); + QString getErrorText(); + int getErrorCode(); + bool initAfterCreated(); + void setTimeout(int secs); + int getTimeout() const; + bool isValid() const; + + protected: + struct FunctionUserData + { + QString name; + int argCount = 0; + Db* db = nullptr; + }; + + virtual QString getAttachSql(Db* otherDb, const QString& generatedAttachName); + + /** + * @brief Generates unique database name for ATTACH. + * @param lock Defines if the lock on dbOperLock mutex. + * @return Unique database name. + * + * Database name here is the name to be used for ATTACH statement. + * For example it will never be "main" or "temp", as those names are already used. + * It also will never be any name that is currently used for any ATTACH'ed database. + * It respects both manual ATTACH'es (called by user), as well as by attach() calls. + * + * Operations on database are normally locked during name generation, because it involves + * queries to the database about what are currently existing objects. + * The lock can be ommited if the calling method already locked dbOperLock. + */ + QString generateUniqueDbName(bool lock = true); + + /** + * @brief Detaches given database object from this database. + * @param otherDb Other registered database. + * + * This is called from detach() and detachAll(). + */ + void detachInternal(Db* otherDb); + + /** + * @brief Clears attached databases list. + * + * Called by closeQuiet(). Only clears maps and lists regarding attached databases. + * It doesn't call detach(), because closing the database will already detach all databases. + */ + void clearAttaches(); + + /** + * @brief Generated unique ID for asynchronous query execution. + * @return Unique ID. + */ + static quint32 generateAsyncId(); + + /** + * @brief Executes query asynchronously. + * @param runner Prepared object for asynchronous execution. + * @return Asynchronous execution unique ID. + * + * This is called by asyncExec(). Runs prepared runner object (which has all information about the query) + * on separate thread. + */ + quint32 asyncExec(AsyncQueryRunner* runner); + + /** + * @brief Opens the database and calls initial setup. + * @return true on success, false on failure. + * + * Calls openInternal() and if it succeeded, calls initialDbSetup(). + * It's called from openQuiet(). + */ + bool openAndSetup(); + + /** + * @brief Checks if the database connection is open. + * @return true if the connection is open, or false otherwise. + * + * This is called from isOpen(). Implementation should test and return information if the database + * connection is open. A lock on connectionStateLock is already set by the isOpen() method. + */ + virtual bool isOpenInternal() = 0; + + /** + * @brief Interrupts execution of any queries. + * + * Implementation of this method should interrupt any query executions that are currently in progress. + * Typical implementation for SQLite databases will call sqlite_interupt() / sqlite3_interupt(). + */ + virtual void interruptExecution() = 0; + + /** + * @brief Returns error message. + * @return Error string. + * + * This can be either error from last query execution, but also from connection opening problems, etc. + */ + virtual QString getErrorTextInternal() = 0; + + /** + * @brief Returns error code. + * @return Error code. + * + * This can be either error from last query execution, but also from connection opening problems, etc. + */ + virtual int getErrorCodeInternal() = 0; + + /** + * @brief Opens database connection. + * @return true on success, false on failure. + * + * Opens database. Called by open() and openAndSetup(). + */ + virtual bool openInternal() = 0; + + /** + * @brief Closes database connection. + * + * Closes database. Called by open() and openQuiet(). + */ + virtual bool closeInternal() = 0; + + virtual void initAfterOpen(); + + void checkForDroppedObject(const QString& query); + bool registerCollation(const QString& name); + bool deregisterCollation(const QString& name); + bool isCollationRegistered(const QString& name); + + /** + * @brief Registers a collation sequence implementation in the database. + * @param name Name of the collation. + * @return true on success, false on failure. + * + * This should be low-level implementation depended on SQLite driver. + * The general implementation of registerCollation() in this class just keeps track on collations + * registered. + */ + virtual bool registerCollationInternal(const QString& name) = 0; + + /** + * @brief Deregisters previously registered collation from this database. + * @param name Collation name. + * @return true on success, false on failure. + * + * This should be low-level implementation depended on SQLite driver. + * The general implementation of registerCollation() in this class just keeps track on collations + * registered. + */ + virtual bool deregisterCollationInternal(const QString& name) = 0; + + static QHash<QString,QVariant> getAggregateContext(void* memPtr); + static void setAggregateContext(void* memPtr, const QHash<QString,QVariant>& aggregateContext); + static void releaseAggregateContext(void* memPtr); + + /** + * @brief Evaluates requested function using defined implementation code and provides result. + * @param dataPtr SQL function user data (defined when registering function). Must be of FunctionUserData* type, or descendant. + * @param argList List of arguments passed to the function. + * @param[out] ok true (default) to indicate successful execution, or false to report an error. + * @return Result returned from the plugin handling function implementation. + * + * This method is aware of the implementation language and the code defined for it, + * so it delegates the execution to the proper plugin handling that language. + * + * This method is called for scalar functions. + */ + static QVariant evaluateScalar(void* dataPtr, const QList<QVariant>& argList, bool& ok); + static void evaluateAggregateStep(void* dataPtr, QHash<QString, QVariant>& aggregateContext, QList<QVariant> argList); + static QVariant evaluateAggregateFinal(void* dataPtr, QHash<QString, QVariant>& aggregateContext, bool& ok); + + /** + * @brief Database name. + * + * It must be unique across all Db instances. Use generateUniqueDbName() to get the unique name + * for new database. It's used as a key for DbManager. + * + * Databases are also presented to the user with this name on UI. + */ + QString name; + + /** + * @brief Path to the database file. + */ + QString path; + + /** + * @brief Connection options. + * + * There are no standard options. Custom DbPlugin implementations may support some options. + */ + QHash<QString,QVariant> connOptions; + + /** + * @brief SQLite version of this database. + * + * This is only a major version number (2 or 3). + */ + quint8 version = 0; + + /** + * @brief Map of databases attached to this database. + * + * It's mapping from ATTACH name to the database object. It contains only attaches + * that were made with attach() calls. + */ + BiHash<QString,Db*> attachedDbMap; + + /** + * @brief Counter of attaching requrests for each database. + * + * When calling attach() on other Db, it gets its own entry in this mapping. + * If the mapping already exists, its value is incremented. + * Then, when calling detach(), counter is decremented and when it reaches 0, + * the database is actualy detached. + */ + QHash<Db*,int> attachCounter; + + /** + * @brief Result handler functions for asynchronous executions. + * + * For each asyncExec() with function pointer in argument there's an entry in this map + * pointing to the function. Keys are asynchronous IDs. + */ + QHash<int,QueryResultsHandler> resultHandlers; + + /** + * @brief Database operation lock. + * + * This lock is set whenever any operation on the actual database is performed (i.e. call to + * exec(), interrupt(), open(), close(), generateUniqueDbName(true), attach(), detach(), and others... + * generally anything that does operations on database that must be synchronous). + * + * In case of exec() it can be locked for READ or WRITE (depending on query type), + * because there can be multiple SELECTs and there's nothing wrong with it, + * while for other methods is always lock for WRITE. + */ + QReadWriteLock dbOperLock; + + private: + /** + * @brief Represents single function that is registered in the database. + * + * Registered custom SQL functions are diversed by SQLite by their name, arguments count and their type, + * so this structure has exactly those parameters. + */ + struct RegisteredFunction + { + /** + * @brief Function name. + */ + QString name; + + /** + * @brief Arguments count (-1 for undefined count). + */ + int argCount; + + /** + * @brief Function type. + */ + FunctionManager::ScriptFunction::Type type; + }; + + friend int qHash(const AbstractDb::RegisteredFunction& fn); + friend bool operator==(const AbstractDb::RegisteredFunction& fn1, const AbstractDb::RegisteredFunction& fn2); + + /** + * @brief Applies execution flags and executes query. + * @param query Query to be executed. + * @param args Query parameters. + * @param flags Query execution flags. + * @return Execution results - either successful or failed. + * + * This is called from both exec() and execNoLock() and is a final step before calling execInternal() + * (the plugin-provided execution). This is where \p flags are interpreted and applied. + */ + SqlQueryPtr execHashArg(const QString& query, const QHash<QString, QVariant>& args, Flags flags); + + /** + * @overload + */ + SqlQueryPtr execListArg(const QString& query, const QList<QVariant>& args, Flags flags); + + /** + * @brief Generates unique database name. + * @return Unique database name. + * + * This is a lock-less variant of generateUniqueDbName(). It is called from that method. + * See generateUniqueDbName() for details. + */ + QString generateUniqueDbNameNoLock(); + + /** + * @brief Provides required locking mode for given query. + * @param query Query to be executed. + * @return Locking mode: READ or WRITE. + * + * Given the query this method analyzes what is the query and provides information if the query + * will do some changes on the database, or not. Then it returns proper locking mode that should + * be used for this query execution. + * + * Query execution methods from this class check if lock mode of the query to be executed isn't + * in conflict with the lock being currently applied on the dbOperLock (if any is applied at the moment). + * + * This method works on a very simple rule. It assumes that queries: SELECT, ANALYZE, EXPLAIN, + * and PRAGMA - are read-only, while all other queries are read-write. + * In case of PRAGMA this is not entirely true, but it's not like using PRAGMA for changing + * some setting would cause database state inconsistency. At least not from perspective of SQLiteStudio. + * + * In case of WITH statement it filters out the "WITH clause" and then checks for SELECT keyword. + */ + ReadWriteLocker::Mode getLockingMode(const QString& query, Db::Flags flags); + + /** + * @brief Handles asynchronous query results with results handler function. + * @param asyncId Asynchronous ID. + * @param results Results from execution. + * @return true if the results were handled, or false if they were not. + * + * This method checks if there is a handler function for given asynchronous ID (in resultHandlers) + * and if there is, then evaluates it and returns true. Otherwise does nothing and returns false. + */ + bool handleResultInternally(quint32 asyncId, SqlQueryPtr results); + + /** + * @brief Registers single custom SQL function. + * @param function Function to register. + * + * If function got registered successfully, it's added to registeredFunctions. + * If there was a function with the same name, argument count and type already registered, + * it will be overwritten (both in SQLite and in registeredFunctions). + */ + void registerFunction(const RegisteredFunction& function); + + /** + * @brief Connection state lock. + * + * It's locked whenever the connection state is changed or tested. + * For open() and close() it's a WRITE lock, for isOpen() it's READ lock. + */ + QReadWriteLock connectionStateLock; + + /** + * @brief Sequence container for generating unique asynchronous IDs. + */ + static quint32 asyncId; + + /** + * @brief Current timeout (in seconds) for waiting for the database to be released from the lock. + * + * See Db::setTimeout() for details. + */ + int timeout = 60; + + /** + * @brief List of all functions currently registered in this database. + */ + QSet<RegisteredFunction> registeredFunctions; + + /** + * @brief List of all collations currently registered in this database. + */ + QStringList registeredCollations; + + private slots: + /** + * @brief Handles asynchronous execution results. + * @param runner Container with input and output data of the query. + * + * This is called from the other thread when it finished asynchronous execution. + * It checks if there is any handler function to evaluate it with results + * and if there's not, emits asyncExecFinished() signal. + */ + void asyncQueryFinished(AsyncQueryRunner* runner); + + public slots: + bool open(); + bool close(); + bool openQuiet(); + bool closeQuiet(); + bool openForProbing(); + void registerAllFunctions(); + void registerAllCollations(); +}; + +/** + * @brief Standard function required by QHash. + * @param fn Function to calculate hash for. + * @return Hash value calculated from all members of DbBase::RegisteredFunction. + */ +int qHash(const AbstractDb::RegisteredFunction& fn); + +/** + * @brief Simple comparator operator, compares all members. + * @param other Other function to compare. + * @return true if \p other is equal, false otherwise. + * + * This function had to be declared/defined outside of the DbBase::RegisteredFunction, because QSet/QHash requires this. + */ +bool operator==(const AbstractDb::RegisteredFunction& fn1, const AbstractDb::RegisteredFunction& fn2); + +#endif // ABSTRACTDB_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/abstractdb2.h b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb2.h new file mode 100644 index 0000000..e35e038 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb2.h @@ -0,0 +1,882 @@ +#ifndef ABSTRACTDB2_H +#define ABSTRACTDB2_H + +#include "db/abstractdb.h" +#include "parser/lexer.h" +#include "common/utils_sql.h" +#include "common/unused.h" +#include "db/sqlerrorcodes.h" +#include "db/sqlerrorresults.h" +#include <sqlite.h> +#include <QThread> +#include <QPointer> +#include <QDebug> + +/** + * @brief Complete implementation of SQLite 2 driver for SQLiteStudio. + * + * Inherit this when implementing Db for SQLite 2. In most cases you will only need + * to create one public constructor, which forwards parameters to the AbstractDb constructor. + * This be sufficient to implement SQLite 2 database plugin. + * Just link it with proper SQLite library. + * + * The template parameter is currently not used for anything specific, so pass any unique type name. + * The best would be to define empty class/structure just for this purpose. + * The parameter is there, so this class becomes a template class. + * We need a template class so we can provide common code base for all SQLite 2 plugins, while the + * code doesn't introduce dependency to SQLite 2 library, until it's used, which is in SQLite 2 plugins. + * + * @see DbQt + */ +template <class T> +class AbstractDb2 : public AbstractDb +{ + public: + /** + * @brief Creates SQLite database object. + * @param name Name for the database. + * @param path File path of the database. + * @param connOptions Connection options. See AbstractDb for details. + * + * All values from this constructor are just passed to AbstractDb constructor. + */ + AbstractDb2(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions); + + ~AbstractDb2(); + + protected: + bool isOpenInternal(); + void interruptExecution(); + QString getErrorTextInternal(); + int getErrorCodeInternal(); + bool openInternal(); + bool closeInternal(); + void initAfterOpen(); + SqlQueryPtr prepare(const QString& query); + QString getTypeLabel(); + bool deregisterFunction(const QString& name, int argCount); + bool registerScalarFunction(const QString& name, int argCount); + bool registerAggregateFunction(const QString& name, int argCount); + bool registerCollationInternal(const QString& name); + bool deregisterCollationInternal(const QString& name); + + private: + class Query : public SqlQuery, public Sqlite2ColumnDataTypeHelper + { + public: + class Row : public SqlResultsRow + { + public: + void init(const QStringList& columns, const QList<QVariant>& resultValues); + }; + + Query(AbstractDb2<T>* db, const QString& query); + ~Query(); + + QString getErrorText(); + int getErrorCode(); + QStringList getColumnNames(); + int columnCount(); + qint64 rowsAffected(); + QString finalize(); + + protected: + SqlResultsRowPtr nextInternal(); + bool hasNextInternal(); + bool execInternal(const QList<QVariant>& args); + bool execInternal(const QHash<QString, QVariant>& args); + + private: + int prepareStmt(const QString& processedQuery); + int resetStmt(); + int bindParam(int paramIdx, const QVariant& value); + int fetchNext(); + int fetchFirst(); + void init(int columnsCount, const char** columns); + bool checkDbState(); + void copyErrorFromDb(); + void copyErrorToDb(); + void setError(int code, const QString& msg); + + static QString replaceNamedParams(const QString& query); + + QPointer<AbstractDb2<T>> db; + sqlite_vm* stmt = nullptr; + int errorCode = SQLITE_OK; + QString errorMessage; + int colCount = -1; + QStringList colNames; + QList<QVariant> nextRowValues; + int affected = 0; + bool rowAvailable = false; + }; + + void cleanUp(); + void resetError(); + QString freeStatement(sqlite_vm* stmt); + + static void storeResult(sqlite_func* func, const QVariant& result, bool ok); + static QList<QVariant> getArgs(int argCount, const char** args); + static void evaluateScalar(sqlite_func* func, int argCount, const char** args); + static void evaluateAggregateStep(sqlite_func* func, int argCount, const char** args); + static void evaluateAggregateFinal(sqlite_func* func); + static void* getContextMemPtr(sqlite_func* func); + static QHash<QString,QVariant> getAggregateContext(sqlite_func* func); + static void setAggregateContext(sqlite_func* func, const QHash<QString,QVariant>& aggregateContext); + static void releaseAggregateContext(sqlite_func* func); + + sqlite* dbHandle = nullptr; + QString dbErrorMessage; + int dbErrorCode = SQLITE_OK; + QList<FunctionUserData*> userDataList; + QList<Query*> queries; +}; + +//------------------------------------------------------------------------------------ +// AbstractDb2 +//------------------------------------------------------------------------------------ + +template <class T> +AbstractDb2<T>::AbstractDb2(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions) : + AbstractDb(name, path, connOptions) +{ +} + +template <class T> +AbstractDb2<T>::~AbstractDb2() +{ + if (isOpenInternal()) + closeInternal(); +} + +template <class T> +bool AbstractDb2<T>::isOpenInternal() +{ + return dbHandle != nullptr; +} + +template <class T> +SqlQueryPtr AbstractDb2<T>::prepare(const QString& query) +{ + return SqlQueryPtr(new Query(this, query)); +} + +template <class T> +void AbstractDb2<T>::interruptExecution() +{ + if (!isOpenInternal()) + return; + + sqlite_interrupt(dbHandle); +} + +template <class T> +QString AbstractDb2<T>::getErrorTextInternal() +{ + return dbErrorMessage; +} + +template <class T> +int AbstractDb2<T>::getErrorCodeInternal() +{ + return dbErrorCode; +} + +template <class T> +bool AbstractDb2<T>::openInternal() +{ + resetError(); + sqlite* handle = nullptr; + char* errMsg = nullptr; + handle = sqlite_open(path.toUtf8().constData(), 0, &errMsg); + if (!handle) + { + dbErrorCode = SQLITE_ERROR; + + if (errMsg) + { + dbErrorMessage = tr("Could not open database: %1").arg(QString::fromUtf8(errMsg)); + sqlite_freemem(errMsg); + } + return false; + } + dbHandle = handle; + return true; +} + +template <class T> +bool AbstractDb2<T>::closeInternal() +{ + resetError(); + if (!dbHandle) + return false; + + cleanUp(); + + sqlite_close(dbHandle); + dbHandle = nullptr; + return true; +} + +template <class T> +void AbstractDb2<T>::initAfterOpen() +{ +} + +template <class T> +QString AbstractDb2<T>::getTypeLabel() +{ + return T::label; +} + +template <class T> +bool AbstractDb2<T>::deregisterFunction(const QString& name, int argCount) +{ + if (!dbHandle) + return false; + + sqlite_create_function(dbHandle, name.toLatin1().data(), argCount, nullptr, nullptr); + sqlite_create_aggregate(dbHandle, name.toLatin1().data(), argCount, nullptr, nullptr, nullptr); + + FunctionUserData* userData = nullptr; + QMutableListIterator<FunctionUserData*> it(userDataList); + while (it.hasNext()) + { + userData = it.next(); + if (userData->name == name && userData->argCount == argCount) + { + it.remove(); + delete userData; + } + } + + return true; +} + +template <class T> +bool AbstractDb2<T>::registerScalarFunction(const QString& name, int argCount) +{ + if (!dbHandle) + return false; + + FunctionUserData* userData = new FunctionUserData; + userData->db = this; + userData->name = name; + userData->argCount = argCount; + userDataList << userData; + + int res = sqlite_create_function(dbHandle, name.toUtf8().constData(), argCount, + &AbstractDb2<T>::evaluateScalar, userData); + + return res == SQLITE_OK; +} + +template <class T> +bool AbstractDb2<T>::registerAggregateFunction(const QString& name, int argCount) +{ + if (!dbHandle) + return false; + + FunctionUserData* userData = new FunctionUserData; + userData->db = this; + userData->name = name; + userData->argCount = argCount; + userDataList << userData; + + int res = sqlite_create_aggregate(dbHandle, name.toUtf8().constData(), argCount, + &AbstractDb2<T>::evaluateAggregateStep, + &AbstractDb2<T>::evaluateAggregateFinal, + userData); + + return res == SQLITE_OK; +} + +template <class T> +bool AbstractDb2<T>::registerCollationInternal(const QString& name) +{ + // Not supported in SQLite 2 + UNUSED(name); + return false; +} + +template <class T> +bool AbstractDb2<T>::deregisterCollationInternal(const QString& name) +{ + // Not supported in SQLite 2 + UNUSED(name); + return false; +} + +template <class T> +void AbstractDb2<T>::cleanUp() +{ + for (Query* q : queries) + q->finalize(); +} + +template <class T> +void AbstractDb2<T>::resetError() +{ + dbErrorCode = 0; + dbErrorMessage = QString::null; +} + +template <class T> +void AbstractDb2<T>::storeResult(sqlite_func* func, const QVariant& result, bool ok) +{ + if (!ok) + { + QByteArray ba = result.toString().toUtf8(); + sqlite_set_result_error(func, ba.constData(), ba.size()); + return; + } + + // Code below is a modified code from Qt (its SQLite plugin). + if (result.isNull()) + { + sqlite_set_result_string(func, nullptr, -1); + return; + } + + switch (result.type()) + { + case QVariant::ByteArray: + { + QByteArray ba = result.toByteArray(); + sqlite_set_result_string(func, ba.constData(), ba.size()); + break; + } + case QVariant::Int: + case QVariant::Bool: + case QVariant::UInt: + case QVariant::LongLong: + { + sqlite_set_result_int(func, result.toInt()); + break; + } + case QVariant::Double: + { + sqlite_set_result_double(func, result.toDouble()); + break; + } + case QVariant::List: + { + QList<QVariant> list = result.toList(); + QStringList strList; + for (const QVariant& v : list) + strList << v.toString(); + + QByteArray ba = strList.join(" ").toUtf8(); + sqlite_set_result_string(func, ba.constData(), ba.size()); + break; + } + case QVariant::StringList: + { + QByteArray ba = result.toStringList().join(" ").toUtf8(); + sqlite_set_result_string(func, ba.constData(), ba.size()); + break; + } + default: + { + // SQLITE_TRANSIENT makes sure that sqlite buffers the data + QByteArray ba = result.toString().toUtf8(); + sqlite_set_result_string(func, ba.constData(), ba.size()); + break; + } + } +} + +template <class T> +QList<QVariant> AbstractDb2<T>::getArgs(int argCount, const char** args) +{ + QList<QVariant> results; + + for (int i = 0; i < argCount; i++) + { + if (!args[i]) + { + results << QVariant(); + continue; + } + + results << QString::fromUtf8(args[i]); + } + return results; +} + +template <class T> +void AbstractDb2<T>::evaluateScalar(sqlite_func* func, int argCount, const char** args) +{ + QList<QVariant> argList = getArgs(argCount, args); + bool ok = true; + QVariant result = AbstractDb::evaluateScalar(sqlite_user_data(func), argList, ok); + storeResult(func, result, ok); +} + +template <class T> +void AbstractDb2<T>::evaluateAggregateStep(sqlite_func* func, int argCount, const char** args) +{ + void* dataPtr = sqlite_user_data(func); + QList<QVariant> argList = getArgs(argCount, args); + QHash<QString,QVariant> aggregateContext = getAggregateContext(func); + + AbstractDb::evaluateAggregateStep(dataPtr, aggregateContext, argList); + + setAggregateContext(func, aggregateContext); +} + +template <class T> +void AbstractDb2<T>::evaluateAggregateFinal(sqlite_func* func) +{ + void* dataPtr = sqlite_user_data(func); + QHash<QString,QVariant> aggregateContext = getAggregateContext(func); + + bool ok = true; + QVariant result = AbstractDb::evaluateAggregateFinal(dataPtr, aggregateContext, ok); + + storeResult(func, result, ok); + releaseAggregateContext(func); +} + +template <class T> +void*AbstractDb2<T>::getContextMemPtr(sqlite_func* func) +{ + return sqlite_aggregate_context(func, sizeof(QHash<QString,QVariant>**)); +} + +template <class T> +QHash<QString, QVariant> AbstractDb2<T>::getAggregateContext(sqlite_func* func) +{ + return AbstractDb::getAggregateContext(getContextMemPtr(func)); +} + +template <class T> +void AbstractDb2<T>::setAggregateContext(sqlite_func* func, const QHash<QString, QVariant>& aggregateContext) +{ + AbstractDb::setAggregateContext(getContextMemPtr(func), aggregateContext); +} + +template <class T> +void AbstractDb2<T>::releaseAggregateContext(sqlite_func* func) +{ + AbstractDb::releaseAggregateContext(getContextMemPtr(func)); +} + +//------------------------------------------------------------------------------------ +// Query +//------------------------------------------------------------------------------------ + +template <class T> +AbstractDb2<T>::Query::Query(AbstractDb2<T>* db, const QString& query) : + db(db) +{ + this->query = query; + db->queries << this; +} + +template <class T> +AbstractDb2<T>::Query::~Query() +{ + if (db.isNull()) + return; + + finalize(); + db->queries.removeOne(this); +} + +template <class T> +void AbstractDb2<T>::Query::copyErrorFromDb() +{ + if (db->dbErrorCode != 0) + { + errorCode = db->dbErrorCode; + errorMessage = db->dbErrorMessage; + return; + } +} + +template <class T> +void AbstractDb2<T>::Query::copyErrorToDb() +{ + db->dbErrorCode = errorCode; + db->dbErrorMessage = errorMessage; +} + +template <class T> +void AbstractDb2<T>::Query::setError(int code, const QString& msg) +{ + if (errorCode != SQLITE_OK) + return; // don't overwrite first error + + errorCode = code; + errorMessage = msg; + copyErrorToDb(); +} + +template <class T> +int AbstractDb2<T>::Query::prepareStmt(const QString& processedQuery) +{ + char* errMsg = nullptr; + const char* tail; + QByteArray queryBytes = processedQuery.toUtf8(); + int res = sqlite_compile(db->dbHandle, queryBytes.constData(), &tail, &stmt, &errMsg); + if (res != SQLITE_OK) + { + finalize(); + if (errMsg) + { + setError(res, QString::fromUtf8((errMsg))); + sqlite_freemem(errMsg); + } + return res; + } + + if (tail && !QString::fromUtf8(tail).trimmed().isEmpty()) + qWarning() << "Executed query left with tailing contents:" << tail << ", while executing query:" << query; + + return SQLITE_OK; +} + +template <class T> +int AbstractDb2<T>::Query::resetStmt() +{ + errorCode = 0; + errorMessage = QString::null; + affected = 0; + colCount = -1; + rowAvailable = false; + nextRowValues.clear(); + + char* errMsg = nullptr; + int res = sqlite_reset(stmt, &errMsg); + if (res != SQLITE_OK) + { + stmt = nullptr; + if (errMsg) + { + setError(res, QString::fromUtf8((errMsg))); + sqlite_freemem(errMsg); + } + return res; + } + return SQLITE_OK; +} + +template <class T> +bool AbstractDb2<T>::Query::execInternal(const QList<QVariant>& args) +{ + if (!checkDbState()) + return false; + + ReadWriteLocker locker(&(db->dbOperLock), query, Dialect::Sqlite2, flags.testFlag(Db::Flag::NO_LOCK)); + + QueryWithParamCount queryWithParams = getQueryWithParamCount(query, Dialect::Sqlite2); + QString singleStr = replaceNamedParams(queryWithParams.first); + + int res; + if (stmt) + res = resetStmt(); + else + res = prepareStmt(singleStr); + + if (res != SQLITE_OK) + return false; + + for (int paramIdx = 1; paramIdx <= queryWithParams.second; paramIdx++) + { + res = bindParam(paramIdx, args[paramIdx-1]); + if (res != SQLITE_OK) + return false; + } + + bool ok = (fetchFirst() == SQLITE_OK); + if (ok) + db->checkForDroppedObject(query); + + return ok; +} + +template <class T> +bool AbstractDb2<T>::Query::execInternal(const QHash<QString, QVariant>& args) +{ + if (!checkDbState()) + return false; + + ReadWriteLocker locker(&(db->dbOperLock), query, Dialect::Sqlite2, flags.testFlag(Db::Flag::NO_LOCK)); + + QueryWithParamNames queryWithParams = getQueryWithParamNames(query, Dialect::Sqlite2); + QString singleStr = replaceNamedParams(queryWithParams.first); + + int res; + if (stmt) + res = resetStmt(); + else + res = prepareStmt(singleStr); + + if (res != SQLITE_OK) + return false; + + int paramIdx = 1; + foreach (const QString& paramName, queryWithParams.second) + { + if (!args.contains(paramName)) + { + setError(SqlErrorCode::OTHER_EXECUTION_ERROR, "Error while preparing statement: could not bind parameter " + paramName); + return false; + } + + res = bindParam(paramIdx++, args[paramName]); + if (res != SQLITE_OK) + return false; + } + + bool ok = (fetchFirst() == SQLITE_OK); + if (ok) + db->checkForDroppedObject(query); + + return ok; +} + +template <class T> +QString AbstractDb2<T>::Query::replaceNamedParams(const QString& query) +{ + TokenList tokens = Lexer::tokenize(query, Dialect::Sqlite2); + for (TokenPtr token : tokens) + { + if (token->type == Token::BIND_PARAM) + token->value = "?"; + } + return tokens.detokenize(); +} + +template <class T> +int AbstractDb2<T>::Query::bindParam(int paramIdx, const QVariant& value) +{ + if (value.isNull()) + { + return sqlite_bind(stmt, paramIdx, nullptr, 0, 0); + } + + switch (value.type()) + { + case QVariant::ByteArray: + { + // NOTE: SQLite 2 has a bug that makes it impossible to write BLOB with nulls inside. First occurrance of the null + // makes the whole value to be saved as truncated to that position. Nothing I can do about it. + QByteArray ba = value.toByteArray(); + return sqlite_bind(stmt, paramIdx, ba.constData(), ba.size(), true); + } + default: + { + QByteArray ba = value.toString().toUtf8(); + ba.append('\0'); + return sqlite_bind(stmt, paramIdx, ba.constData(), ba.size(), true); + } + } + + return SQLITE_MISUSE; // not going to happen +} +template <class T> +QString AbstractDb2<T>::Query::getErrorText() +{ + return errorMessage; +} + +template <class T> +int AbstractDb2<T>::Query::getErrorCode() +{ + return errorCode; +} + +template <class T> +QStringList AbstractDb2<T>::Query::getColumnNames() +{ + return colNames; +} + +template <class T> +int AbstractDb2<T>::Query::columnCount() +{ + return colCount; +} + +template <class T> +qint64 AbstractDb2<T>::Query::rowsAffected() +{ + return affected; +} + +template <class T> +SqlResultsRowPtr AbstractDb2<T>::Query::nextInternal() +{ + if (!rowAvailable || db.isNull()) + return SqlResultsRowPtr(); + + Row* row = new Row; + row->init(colNames, nextRowValues); + + int res = fetchNext(); + if (res != SQLITE_OK) + { + delete row; + return SqlResultsRowPtr(); + } + return SqlResultsRowPtr(row); +} + +template <class T> +bool AbstractDb2<T>::Query::hasNextInternal() +{ + return rowAvailable && stmt; +} + +template <class T> +int AbstractDb2<T>::Query::fetchFirst() +{ + rowAvailable = true; + int res = fetchNext(); + if (res == SQLITE_OK) + { + if (colCount == 0) + { + affected = 0; + } + else + { + affected = sqlite_changes(db->dbHandle); + insertRowId["ROWID"] = sqlite_last_insert_rowid(db->dbHandle); + } + } + return res; +} + +template <class T> +bool AbstractDb2<T>::Query::checkDbState() +{ + if (db.isNull() || !db->dbHandle) + { + setError(SqlErrorCode::DB_NOT_DEFINED, "SqlQuery is no longer valid."); + return false; + } + + return true; +} + +template <class T> +QString AbstractDb2<T>::Query::finalize() +{ + QString msg; + if (stmt) + { + char* errMsg = nullptr; + sqlite_finalize(stmt, &errMsg); + stmt = nullptr; + if (errMsg) + { + msg = QString::fromUtf8(errMsg); + sqlite_freemem(errMsg); + } + } + return msg; +} + +template <class T> +int AbstractDb2<T>::Query::fetchNext() +{ + if (!checkDbState()) + rowAvailable = false; + + if (!rowAvailable || !stmt) + { + setError(SQLITE_MISUSE, tr("Result set expired or no row available.")); + return SQLITE_MISUSE; + } + + rowAvailable = false; + + const char** values; + const char** columns; + int columnsCount; + + int res; + int secondsSpent = 0; + while ((res = sqlite_step(stmt, &columnsCount, &values, &columns)) == SQLITE_BUSY && secondsSpent < db->getTimeout()) + { + QThread::sleep(1); + if (db->getTimeout() >= 0) + secondsSpent++; + } + + switch (res) + { + case SQLITE_ROW: + rowAvailable = true; + break; + case SQLITE_DONE: + // Empty pointer as no more results are available. + break; + default: + setError(res, finalize()); + return SQLITE_ERROR; + } + + // First row, initialize members + if (colCount == -1) + init(columnsCount, columns); + + // Then read the next row data + nextRowValues.clear(); + if (rowAvailable) + { + for (int i = 0; i < colCount; i++) + { + if (isBinaryColumn(i)) + nextRowValues << QByteArray(values[i]); + else + nextRowValues << QString::fromUtf8(values[i]); + } + } + + return SQLITE_OK; +} + +template <class T> +void AbstractDb2<T>::Query::init(int columnsCount, const char** columns) +{ + colCount = columnsCount; + + TokenList columnDescription; + for (int i = 0; i < colCount; i++) + { + columnDescription = Lexer::tokenize(QString::fromUtf8(columns[i]), Dialect::Sqlite2).filterWhiteSpaces(); + if (columnDescription.size() > 0) + { + // If the column is prefixed with dbname and table name, then we remove them. + for (int j = 0; j < 2 &&columnDescription.size() > 1 && columnDescription[1]->type == Token::OPERATOR && columnDescription[1]->value == "."; j++) + { + columnDescription.removeFirst(); + columnDescription.removeFirst(); + } + + colNames << stripObjName(columnDescription.first()->value, Dialect::Sqlite2); + } + else + colNames << ""; + } +} + +//------------------------------------------------------------------------------------ +// Row +//------------------------------------------------------------------------------------ + +template <class T> +void AbstractDb2<T>::Query::Row::init(const QStringList& columns, const QList<QVariant>& resultValues) +{ + for (int i = 0; i < columns.size(); i++) + { + values << resultValues[i]; + valuesMap[columns[i]] = resultValues[i]; + } +} + +#endif // ABSTRACTDB2_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/abstractdb3.h b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb3.h new file mode 100644 index 0000000..d8e54b4 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/abstractdb3.h @@ -0,0 +1,1157 @@ +#ifndef ABSTRACTDB3_H +#define ABSTRACTDB3_H + +#include "db/abstractdb.h" +#include "parser/lexer.h" +#include "common/utils_sql.h" +#include "common/unused.h" +#include "services/collationmanager.h" +#include "sqlitestudio.h" +#include "db/sqlerrorcodes.h" +#include <QThread> +#include <QPointer> +#include <QDebug> + +/** + * @brief Complete implementation of SQLite 3 driver for SQLiteStudio. + * + * Inherit this when implementing Db for SQLite 3. In most cases you will only need + * to create one public constructor, which forwards parameters to the AbstractDb constructor. + * This be sufficient to implement SQLite 3 database plugin. + * Just link it with proper SQLite library. + * + * The template parameter should provide all necessary SQLite symbols used by this implementation. + * This way every Db plugin can provide it's own symbols to work on SQLite and so it allows + * for loading multiple SQLite libraries into the same application, while symbols in each library + * can be different (and should be different, to avoid name conflicts and symbol overlapping). + * See how it's done in dbsqlite3.h. + * + * @see DbQt + */ +template <class T> +class AbstractDb3 : public AbstractDb +{ + public: + /** + * @brief Creates SQLite database object. + * @param name Name for the database. + * @param path File path of the database. + * @param connOptions Connection options. See AbstractDb for details. + * + * All values from this constructor are just passed to AbstractDb constructor. + */ + AbstractDb3(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions); + ~AbstractDb3(); + + protected: + bool isOpenInternal(); + void interruptExecution(); + QString getErrorTextInternal(); + int getErrorCodeInternal(); + bool openInternal(); + bool closeInternal(); + void initAfterOpen(); + SqlQueryPtr prepare(const QString& query); + QString getTypeLabel(); + bool deregisterFunction(const QString& name, int argCount); + bool registerScalarFunction(const QString& name, int argCount); + bool registerAggregateFunction(const QString& name, int argCount); + bool registerCollationInternal(const QString& name); + bool deregisterCollationInternal(const QString& name); + + private: + class Query : public SqlQuery + { + public: + class Row : public SqlResultsRow + { + public: + int init(const QStringList& columns, typename T::stmt* stmt); + + private: + int getValue(typename T::stmt* stmt, int col, QVariant& value); + }; + + Query(AbstractDb3<T>* db, const QString& query); + ~Query(); + + QString getErrorText(); + int getErrorCode(); + QStringList getColumnNames(); + int columnCount(); + qint64 rowsAffected(); + void finalize(); + + protected: + SqlResultsRowPtr nextInternal(); + bool hasNextInternal(); + bool execInternal(const QList<QVariant>& args); + bool execInternal(const QHash<QString, QVariant>& args); + + private: + int prepareStmt(); + int resetStmt(); + int bindParam(int paramIdx, const QVariant& value); + int fetchFirst(); + int fetchNext(); + bool checkDbState(); + void copyErrorFromDb(); + void copyErrorToDb(); + void setError(int code, const QString& msg); + + QPointer<AbstractDb3<T>> db; + typename T::stmt* stmt = nullptr; + int errorCode = T::OK; + QString errorMessage; + int colCount = 0; + QStringList colNames; + int affected = 0; + bool rowAvailable = false; + }; + + struct CollationUserData + { + QString name; + AbstractDb3<T>* db = nullptr; + }; + + QString extractLastError(); + void cleanUp(); + void resetError(); + + /** + * @brief Registers function to call when unknown collation was encountered by the SQLite. + * + * For unknown collations SQLite calls function registered by this method and expects it to register + * proper function handling that collation, otherwise the query will result with error. + * + * The default collation handler does a simple QString::compare(), case insensitive. + */ + void registerDefaultCollationRequestHandler(); + + /** + * @brief Stores given result in function's context. + * @param context Custom SQL function call context. + * @param result Value returned from function execution. + * @param ok true if the result is from a successful execution, or false if the result contains error message (QString). + * + * This method is called after custom implementation of the function was evaluated and it returned the result. + * It stores the result in function's context, so it becomes the result of the function call. + */ + static void storeResult(typename T::context* context, const QVariant& result, bool ok); + + /** + * @brief Converts SQLite arguments into the list of argument values. + * @param argCount Number of arguments. + * @param args SQLite argument values. + * @return Convenient Qt list with argument values as QVariant. + * + * This function does necessary conversions reflecting internal SQLite datatype, so if the type + * was for example BLOB, then the QVariant will be a QByteArray, etc. + */ + static QList<QVariant> getArgs(int argCount, typename T::value** args); + + /** + * @brief Evaluates requested function using defined implementation code and provides result. + * @param context SQL function call context. + * @param argCount Number of arguments passed to the function. + * @param args Arguments passed to the function. + * + * This method is aware of the implementation language and the code defined for it, + * so it delegates the execution to the proper plugin handling that language. Then it stores + * result returned from the plugin in function's context, so it becomes function's result. + * + * This method is called for scalar functions. + * + * @see DbQt::evaluateScalar() + */ + static void evaluateScalar(typename T::context* context, int argCount, typename T::value** args); + + /** + * @brief Evaluates requested function using defined implementation code and provides result. + * @param context SQL function call context. + * @param argCount Number of arguments passed to the function. + * @param args Arguments passed to the function. + * + * This method is called for aggregate functions. + * + * If this is the first call to this function using this context, then it will execute + * both "initial" and then "per step" code for this function implementation. + * + * @see DbQt3::evaluateScalar() + * @see DbQt::evaluateAggregateStep() + */ + static void evaluateAggregateStep(typename T::context* context, int argCount, typename T::value** args); + + /** + * @brief Evaluates "final" code for aggregate function. + * @param context SQL function call context. + * + * This method is called for aggregate functions. + * + * It's called once, at the end of aggregate function evaluation. + * It executes "final" code of the function implementation. + */ + static void evaluateAggregateFinal(typename T::context* context); + + /** + * @brief Evaluates code of the collation. + * @param userData Collation user data (name of the collation inside). + * @param length1 Number of characters in value1 (excluding \0). + * @param value1 First value to compare. + * @param length2 Number of characters in value2 (excluding \0). + * @param value2 Second value to compare. + * @return -1, 0, or 1, as SQLite's collation specification demands it. + */ + static int evaluateCollation(void* userData, int length1, const void* value1, int length2, const void* value2); + + /** + * @brief Cleans up collation user data when collation is deregistered. + * @param userData User data to delete. + */ + static void deleteCollationUserData(void* userData); + + /** + * @brief Destructor for function user data object. + * @param dataPtr Pointer to the user data object. + * + * This is called by SQLite when the function is deregistered. + */ + static void deleteUserData(void* dataPtr); + + /** + * @brief Allocates and/or returns shared memory for the aggregate SQL function call. + * @param context SQL function call context. + * @return Pointer to the memory. + * + * It allocates exactly the number of bytes required to store pointer to a QHash. + * The memory is released after the aggregate function is finished. + */ + static void* getContextMemPtr(typename T::context* context); + + /** + * @brief Allocates and/or returns QHash shared across all aggregate function steps. + * @param context SQL function call context. + * @return Shared hash table. + * + * The hash table is created before initial aggregate function step is made. + * Then it's shared across all further steps (using this method to get it) + * and then releases the memory after the last (final) step of the function call. + */ + static QHash<QString,QVariant> getAggregateContext(typename T::context* context); + + /** + * @brief Sets new value of the aggregate function shared hash table. + * @param context SQL function call context. + * @param aggregateContext New shared hash table value to store. + * + * This should be called after each time the context was requested with getAggregateContext() and then modified. + */ + static void setAggregateContext(typename T::context* context, const QHash<QString,QVariant>& aggregateContext); + + /** + * @brief Releases aggregate function shared hash table. + * @param context SQL function call context. + * + * This should be called from final aggregate function step to release the shared context (delete QHash). + * The memory used to store pointer to the shared context will be released by the SQLite itself. + */ + static void releaseAggregateContext(typename T::context* context); + + /** + * @brief Registers default collation for requested collation. + * @param fnUserData User data passed when registering collation request handling function. + * @param fnDbHandle Database handle for which this call is being made. + * @param eTextRep Text encoding (for now always T::UTF8). + * @param collationName Name of requested collation. + * + * This function is called by SQLite to order registering collation with given name. We register default collation, + * cause all known collations should already be registered. + * + * Default collation is implemented by evaluateDefaultCollation(). + */ + static void registerDefaultCollation(void* fnUserData, typename T::handle* fnDbHandle, int eTextRep, const char* collationName); + + /** + * @brief Called as a default collation implementation. + * @param userData Collation user data, not used. + * @param length1 Number of characters in value1 (excluding \0). + * @param value1 First value to compare. + * @param length2 Number of characters in value2 (excluding \0). + * @param value2 Second value to compare. + * @return -1, 0, or 1, as SQLite's collation specification demands it. + */ + static int evaluateDefaultCollation(void* userData, int length1, const void* value1, int length2, const void* value2); + + typename T::handle* dbHandle = nullptr; + QString dbErrorMessage; + int dbErrorCode = T::OK; + QList<Query*> queries; + + /** + * @brief User data for default collation request handling function. + * + * That function doesn't have destructor function pointer, so we need to keep track of that user data + * and delete it when database is closed. + */ + CollationUserData* defaultCollationUserData = nullptr; +}; + +//------------------------------------------------------------------------------------ +// AbstractDb3 +//------------------------------------------------------------------------------------ + +template <class T> +AbstractDb3<T>::AbstractDb3(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions) : + AbstractDb(name, path, connOptions) +{ +} + +template <class T> +AbstractDb3<T>::~AbstractDb3() +{ + if (isOpenInternal()) + closeInternal(); +} + +template <class T> +bool AbstractDb3<T>::isOpenInternal() +{ + return dbHandle != nullptr; +} + +template <class T> +void AbstractDb3<T>::interruptExecution() +{ + if (!isOpenInternal()) + return; + + T::interrupt(dbHandle); +} + +template <class T> +QString AbstractDb3<T>::getErrorTextInternal() +{ + return dbErrorMessage; +} + +template <class T> +int AbstractDb3<T>::getErrorCodeInternal() +{ + return dbErrorCode; +} + +template <class T> +bool AbstractDb3<T>::openInternal() +{ + resetError(); + typename T::handle* handle = nullptr; + int res = T::open_v2(path.toUtf8().constData(), &handle, T::OPEN_READWRITE|T::OPEN_CREATE, nullptr); + if (res != T::OK) + { + if (handle) + T::close(handle); + + dbErrorMessage = tr("Could not open database: %1").arg(extractLastError()); + dbErrorCode = res; + return false; + } + dbHandle = handle; + return true; +} + +template <class T> +bool AbstractDb3<T>::closeInternal() +{ + resetError(); + if (!dbHandle) + return false; + + cleanUp(); + + int res = T::close(dbHandle); + if (res != T::OK) + { + dbErrorMessage = tr("Could not close database: %1").arg(extractLastError()); + dbErrorCode = res; + qWarning() << "Error closing database. That's weird:" << dbErrorMessage; + return false; + } + dbHandle = nullptr; + return true; +} + +template <class T> +void AbstractDb3<T>::initAfterOpen() +{ + T::enable_load_extension(dbHandle, true); + registerDefaultCollationRequestHandler();; + exec("PRAGMA foreign_keys = 1;", Flag::NO_LOCK); + exec("PRAGMA recursive_triggers = 1;", Flag::NO_LOCK); +} + +template <class T> +SqlQueryPtr AbstractDb3<T>::prepare(const QString& query) +{ + return SqlQueryPtr(new Query(this, query)); +} + +template <class T> +QString AbstractDb3<T>::getTypeLabel() +{ + return T::label; +} + +template <class T> +bool AbstractDb3<T>::deregisterFunction(const QString& name, int argCount) +{ + if (!dbHandle) + return false; + + T::create_function(dbHandle, name.toUtf8().constData(), argCount, T::UTF8, 0, nullptr, nullptr, nullptr); + return true; +} + +template <class T> +bool AbstractDb3<T>::registerScalarFunction(const QString& name, int argCount) +{ + if (!dbHandle) + return false; + + FunctionUserData* userData = new FunctionUserData; + userData->db = this; + userData->name = name; + userData->argCount = argCount; + + int res = T::create_function_v2(dbHandle, name.toUtf8().constData(), argCount, T::UTF8, userData, + &AbstractDb3<T>::evaluateScalar, + nullptr, + nullptr, + &AbstractDb3<T>::deleteUserData); + + return res == T::OK; +} + +template <class T> +bool AbstractDb3<T>::registerAggregateFunction(const QString& name, int argCount) +{ + if (!dbHandle) + return false; + + FunctionUserData* userData = new FunctionUserData; + userData->db = this; + userData->name = name; + userData->argCount = argCount; + + int res = T::create_function_v2(dbHandle, name.toUtf8().constData(), argCount, T::UTF8, userData, + nullptr, + &AbstractDb3<T>::evaluateAggregateStep, + &AbstractDb3<T>::evaluateAggregateFinal, + &AbstractDb3<T>::deleteUserData); + + return res == T::OK; +} + +template <class T> +bool AbstractDb3<T>::registerCollationInternal(const QString& name) +{ + if (!dbHandle) + return false; + + CollationUserData* userData = new CollationUserData; + userData->name = name; + + int res = T::create_collation_v2(dbHandle, name.toUtf8().constData(), T::UTF8, userData, + &AbstractDb3<T>::evaluateCollation, + &AbstractDb3<T>::deleteCollationUserData); + return res == T::OK; +} + +template <class T> +bool AbstractDb3<T>::deregisterCollationInternal(const QString& name) +{ + if (!dbHandle) + return false; + + T::create_collation_v2(dbHandle, name.toUtf8().constData(), T::UTF8, nullptr, nullptr, nullptr); + return true; +} + +template <class T> +QString AbstractDb3<T>::extractLastError() +{ + dbErrorCode = T::extended_errcode(dbHandle); + dbErrorMessage = QString::fromUtf8(T::errmsg(dbHandle)); + return dbErrorMessage; +} + +template <class T> +void AbstractDb3<T>::cleanUp() +{ + for (Query* q : queries) + q->finalize(); + + safe_delete(defaultCollationUserData); +} + +template <class T> +void AbstractDb3<T>::resetError() +{ + dbErrorCode = 0; + dbErrorMessage = QString::null; +} + +template <class T> +void AbstractDb3<T>::storeResult(typename T::context* context, const QVariant& result, bool ok) +{ + if (!ok) + { + QString str = result.toString(); + T::result_error16(context, str.utf16(), str.size() * sizeof(QChar)); + return; + } + + // Code below is a modified code from Qt (its SQLite plugin). + if (result.isNull()) + { + T::result_null(context); + return; + } + + switch (result.type()) + { + case QVariant::ByteArray: + { + QByteArray ba = result.toByteArray(); + T::result_blob(context, ba.constData(), ba.size(), T::TRANSIENT()); + break; + } + case QVariant::Int: + case QVariant::Bool: + { + T::result_int(context, result.toInt()); + break; + } + case QVariant::Double: + { + T::result_double(context, result.toDouble()); + break; + } + case QVariant::UInt: + case QVariant::LongLong: + { + T::result_int64(context, result.toLongLong()); + break; + } + case QVariant::List: + { + QList<QVariant> list = result.toList(); + QStringList strList; + for (const QVariant& v : list) + strList << v.toString(); + + QString str = strList.join(" "); + T::result_text16(context, str.utf16(), str.size() * sizeof(QChar), T::TRANSIENT()); + break; + } + case QVariant::StringList: + { + QString str = result.toStringList().join(" "); + T::result_text16(context, str.utf16(), str.size() * sizeof(QChar), T::TRANSIENT()); + break; + } + default: + { + // T::TRANSIENT makes sure that sqlite buffers the data + QString str = result.toString(); + T::result_text16(context, str.utf16(), str.size() * sizeof(QChar), T::TRANSIENT()); + break; + } + } +} + +template <class T> +QList<QVariant> AbstractDb3<T>::getArgs(int argCount, typename T::value** args) +{ + int dataType; + QList<QVariant> results; + QVariant value; + + // The loop below uses slightly modified code from Qt (its SQLite plugin) to extract values. + for (int i = 0; i < argCount; i++) + { + dataType = T::value_type(args[i]); + switch (dataType) + { + case T::INTEGER: + value = T::value_int64(args[i]); + break; + case T::BLOB: + value = QByteArray( + static_cast<const char*>(T::value_blob(args[i])), + T::value_bytes(args[i]) + ); + break; + case T::FLOAT: + value = T::value_double(args[i]); + break; + case T::NULL_TYPE: + value = QVariant(QVariant::String); + break; + default: + value = QString( + reinterpret_cast<const QChar*>(T::value_text16(args[i])), + T::value_bytes16(args[i]) / sizeof(QChar) + ); + break; + } + results << value; + } + return results; +} + +template <class T> +void AbstractDb3<T>::evaluateScalar(typename T::context* context, int argCount, typename T::value** args) +{ + QList<QVariant> argList = getArgs(argCount, args); + bool ok = true; + QVariant result = AbstractDb::evaluateScalar(T::user_data(context), argList, ok); + storeResult(context, result, ok); +} + +template <class T> +void AbstractDb3<T>::evaluateAggregateStep(typename T::context* context, int argCount, typename T::value** args) +{ + void* dataPtr = T::user_data(context); + QList<QVariant> argList = getArgs(argCount, args); + QHash<QString,QVariant> aggregateContext = getAggregateContext(context); + + AbstractDb::evaluateAggregateStep(dataPtr, aggregateContext, argList); + + setAggregateContext(context, aggregateContext); +} + +template <class T> +void AbstractDb3<T>::evaluateAggregateFinal(typename T::context* context) +{ + void* dataPtr = T::user_data(context); + QHash<QString,QVariant> aggregateContext = getAggregateContext(context); + + bool ok = true; + QVariant result = AbstractDb::evaluateAggregateFinal(dataPtr, aggregateContext, ok); + + storeResult(context, result, ok); + releaseAggregateContext(context); +} + +template <class T> +int AbstractDb3<T>::evaluateCollation(void* userData, int length1, const void* value1, int length2, const void* value2) +{ + UNUSED(length1); + UNUSED(length2); + CollationUserData* collUserData = reinterpret_cast<CollationUserData*>(userData); + return COLLATIONS->evaluate(collUserData->name, QString::fromUtf8((const char*)value1), QString::fromUtf8((const char*)value2)); +} + +template <class T> +void AbstractDb3<T>::deleteCollationUserData(void* userData) +{ + if (!userData) + return; + + CollationUserData* collUserData = reinterpret_cast<CollationUserData*>(userData); + delete collUserData; +} + +template <class T> +void AbstractDb3<T>::deleteUserData(void* dataPtr) +{ + if (!dataPtr) + return; + + FunctionUserData* userData = reinterpret_cast<FunctionUserData*>(dataPtr); + delete userData; +} + +template <class T> +void* AbstractDb3<T>::getContextMemPtr(typename T::context* context) +{ + return T::aggregate_context(context, sizeof(QHash<QString,QVariant>**)); +} + +template <class T> +QHash<QString, QVariant> AbstractDb3<T>::getAggregateContext(typename T::context* context) +{ + return AbstractDb::getAggregateContext(getContextMemPtr(context)); +} + +template <class T> +void AbstractDb3<T>::setAggregateContext(typename T::context* context, const QHash<QString, QVariant>& aggregateContext) +{ + AbstractDb::setAggregateContext(getContextMemPtr(context), aggregateContext); +} + +template <class T> +void AbstractDb3<T>::releaseAggregateContext(typename T::context* context) +{ + AbstractDb::releaseAggregateContext(getContextMemPtr(context)); +} + +template <class T> +void AbstractDb3<T>::registerDefaultCollation(void* fnUserData, typename T::handle* fnDbHandle, int eTextRep, const char* collationName) +{ + CollationUserData* defUserData = reinterpret_cast<CollationUserData*>(fnUserData); + if (!defUserData) + { + qWarning() << "Null userData in AbstractDb3<T>::registerDefaultCollation()."; + return; + } + + AbstractDb3<T>* db = defUserData->db; + if (!db) + { + qWarning() << "No database defined in userData of AbstractDb3<T>::registerDefaultCollation()."; + return; + } + + // If SQLite seeks for collation implementation with different encoding, we force it to use existing one. + if (db->isCollationRegistered(QString::fromUtf8(collationName))) + return; + + // Check if dbHandle matches - just in case + if (db->dbHandle != fnDbHandle) + { + qWarning() << "Mismatch of dbHandle in AbstractDb3<T>::registerDefaultCollation()."; + return; + } + + int res = T::create_collation_v2(fnDbHandle, collationName, eTextRep, nullptr, + &AbstractDb3<T>::evaluateDefaultCollation, nullptr); + + if (res != T::OK) + qWarning() << "Could not register default collation in AbstractDb3<T>::registerDefaultCollation()."; +} + +template <class T> +int AbstractDb3<T>::evaluateDefaultCollation(void* userData, int length1, const void* value1, int length2, const void* value2) +{ + UNUSED(userData); + UNUSED(length1); + UNUSED(length2); + return COLLATIONS->evaluateDefault(QString::fromUtf8((const char*)value1), QString::fromUtf8((const char*)value2)); +} + +template <class T> +void AbstractDb3<T>::registerDefaultCollationRequestHandler() +{ + if (!dbHandle) + return; + + defaultCollationUserData = new CollationUserData; + defaultCollationUserData->db = this; + + int res = T::collation_needed(dbHandle, defaultCollationUserData, &AbstractDb3<T>::registerDefaultCollation); + if (res != T::OK) + qWarning() << "Could not register default collation request handler. Unknown collations will cause errors."; +} + +//------------------------------------------------------------------------------------ +// Results +//------------------------------------------------------------------------------------ + +template <class T> +AbstractDb3<T>::Query::Query(AbstractDb3<T>* db, const QString& query) : + db(db) +{ + this->query = query; + db->queries << this; +} + +template <class T> +AbstractDb3<T>::Query::~Query() +{ + if (db.isNull()) + return; + + finalize(); + db->queries.removeOne(this); +} + +template <class T> +void AbstractDb3<T>::Query::copyErrorFromDb() +{ + if (db->dbErrorCode != 0) + { + errorCode = db->dbErrorCode; + errorMessage = db->dbErrorMessage; + return; + } +} + +template <class T> +void AbstractDb3<T>::Query::copyErrorToDb() +{ + db->dbErrorCode = errorCode; + db->dbErrorMessage = errorMessage; +} + +template <class T> +void AbstractDb3<T>::Query::setError(int code, const QString& msg) +{ + if (errorCode != T::OK) + return; // don't overwrite first error + + errorCode = code; + errorMessage = msg; + copyErrorToDb(); +} + +template <class T> +int AbstractDb3<T>::Query::prepareStmt() +{ + const char* tail; + QByteArray queryBytes = query.toUtf8(); + int res = T::prepare_v2(db->dbHandle, queryBytes.constData(), queryBytes.size(), &stmt, &tail); + if (res != T::OK) + { + stmt = nullptr; + db->extractLastError(); + copyErrorFromDb(); + return res; + } + + if (tail && !QString::fromUtf8(tail).trimmed().isEmpty()) + qWarning() << "Executed query left with tailing contents:" << tail << ", while executing query:" << query; + + return T::OK; +} + +template <class T> +int AbstractDb3<T>::Query::resetStmt() +{ + errorCode = 0; + errorMessage = QString::null; + affected = 0; + colCount = -1; + rowAvailable = false; + + int res = T::reset(stmt); + if (res != T::OK) + { + stmt = nullptr; + setError(res, QString::fromUtf8(T::errmsg(db->dbHandle))); + return res; + } + return T::OK; +} + +template <class T> +bool AbstractDb3<T>::Query::execInternal(const QList<QVariant>& args) +{ + if (!checkDbState()) + return false; + + ReadWriteLocker locker(&(db->dbOperLock), query, Dialect::Sqlite3, flags.testFlag(Db::Flag::NO_LOCK)); + QueryWithParamCount queryWithParams = getQueryWithParamCount(query, Dialect::Sqlite3); + + int res; + if (stmt) + res = resetStmt(); + else + res = prepareStmt(); + + if (res != T::OK) + return false; + + for (int paramIdx = 1; paramIdx <= queryWithParams.second; paramIdx++) + { + res = bindParam(paramIdx, args[paramIdx-1]); + if (res != T::OK) + { + db->extractLastError(); + copyErrorFromDb(); + return false; + } + } + + bool ok = (fetchFirst() == T::OK); + if (ok) + db->checkForDroppedObject(query); + + return ok; +} + +template <class T> +bool AbstractDb3<T>::Query::execInternal(const QHash<QString, QVariant>& args) +{ + if (!checkDbState()) + return false; + + ReadWriteLocker locker(&(db->dbOperLock), query, Dialect::Sqlite3, flags.testFlag(Db::Flag::NO_LOCK)); + + QueryWithParamNames queryWithParams = getQueryWithParamNames(query, Dialect::Sqlite3); + + int res; + if (stmt) + res = resetStmt(); + else + res = prepareStmt(); + + if (res != T::OK) + return false; + + int paramIdx = 1; + foreach (const QString& paramName, queryWithParams.second) + { + if (!args.contains(paramName)) + { + setError(SqlErrorCode::OTHER_EXECUTION_ERROR, "Error while preparing statement: could not bind parameter " + paramName); + return false; + } + + res = bindParam(paramIdx++, args[paramName]); + if (res != T::OK) + { + db->extractLastError(); + copyErrorFromDb(); + return false; + } + } + + bool ok = (fetchFirst() == T::OK); + if (ok) + db->checkForDroppedObject(query); + + return ok; +} + +template <class T> +int AbstractDb3<T>::Query::bindParam(int paramIdx, const QVariant& value) +{ + if (value.isNull()) + { + return T::bind_null(stmt, paramIdx); + } + + switch (value.type()) + { + case QVariant::ByteArray: + { + QByteArray ba = value.toByteArray(); + return T::bind_blob(stmt, paramIdx, ba.constData(), ba.size(), T::TRANSIENT()); + } + case QVariant::Int: + case QVariant::Bool: + { + return T::bind_int(stmt, paramIdx, value.toInt()); + } + case QVariant::Double: + { + return T::bind_double(stmt, paramIdx, value.toDouble()); + } + case QVariant::UInt: + case QVariant::LongLong: + { + return T::bind_int64(stmt, paramIdx, value.toLongLong()); + } + default: + { + // T::TRANSIENT makes sure that sqlite buffers the data + QString str = value.toString(); + return T::bind_text16(stmt, paramIdx, str.utf16(), str.size() * sizeof(QChar), T::TRANSIENT()); + } + } + + return T::MISUSE; // not going to happen +} + +template <class T> +bool AbstractDb3<T>::Query::checkDbState() +{ + if (db.isNull() || !db->dbHandle) + { + setError(SqlErrorCode::DB_NOT_DEFINED, "SqlQuery is no longer valid."); + return false; + } + + return true; +} + +template <class T> +void AbstractDb3<T>::Query::finalize() +{ + if (stmt) + { + T::finalize(stmt); + stmt = nullptr; + } +} + +template <class T> +QString AbstractDb3<T>::Query::getErrorText() +{ + return errorMessage; +} + +template <class T> +int AbstractDb3<T>::Query::getErrorCode() +{ + return errorCode; +} + +template <class T> +QStringList AbstractDb3<T>::Query::getColumnNames() +{ + return colNames; +} + +template <class T> +int AbstractDb3<T>::Query::columnCount() +{ + return colCount; +} + +template <class T> +qint64 AbstractDb3<T>::Query::rowsAffected() +{ + return affected; +} + +template <class T> +SqlResultsRowPtr AbstractDb3<T>::Query::nextInternal() +{ + Row* row = new Row; + int res = row->init(colNames, stmt); + if (res != T::OK) + { + delete row; + setError(res, QString::fromUtf8(T::errmsg(db->dbHandle))); + return SqlResultsRowPtr(); + } + + res = fetchNext(); + if (res != T::OK) + { + delete row; + return SqlResultsRowPtr(); + } + + return SqlResultsRowPtr(row); +} + +template <class T> +bool AbstractDb3<T>::Query::hasNextInternal() +{ + return rowAvailable && stmt && checkDbState(); +} + +template <class T> +int AbstractDb3<T>::Query::fetchFirst() +{ + colCount = T::column_count(stmt); + for (int i = 0; i < colCount; i++) + colNames << QString::fromUtf8(T::column_name(stmt, i)); + + rowAvailable = true; + int res = fetchNext(); + + affected = 0; + if (res == T::OK) + { + affected = T::changes(db->dbHandle); + insertRowId["ROWID"] = T::last_insert_rowid(db->dbHandle); + } + + return res; +} + +template <class T> +int AbstractDb3<T>::Query::fetchNext() +{ + if (!checkDbState()) + rowAvailable = false; + + if (!rowAvailable || !stmt) + { + setError(T::MISUSE, tr("Result set expired or no row available.")); + return T::MISUSE; + } + + rowAvailable = false; + int res; + int secondsSpent = 0; + while ((res = T::step(stmt)) == T::BUSY && secondsSpent < db->getTimeout()) + { + QThread::sleep(1); + if (db->getTimeout() >= 0) + secondsSpent++; + } + + switch (res) + { + case T::ROW: + rowAvailable = true; + break; + case T::DONE: + // Empty pointer as no more results are available. + break; + default: + setError(res, QString::fromUtf8(T::errmsg(db->dbHandle))); + return T::ERROR; + } + return T::OK; +} + +//------------------------------------------------------------------------------------ +// Row +//------------------------------------------------------------------------------------ + +template <class T> +int AbstractDb3<T>::Query::Row::init(const QStringList& columns, typename T::stmt* stmt) +{ + int res = T::OK; + QVariant value; + for (int i = 0; i < columns.size(); i++) + { + res = getValue(stmt, i, value); + if (res != T::OK) + return res; + + values << value; + valuesMap[columns[i]] = value; + } + return res; +} + +template <class T> +int AbstractDb3<T>::Query::Row::getValue(typename T::stmt* stmt, int col, QVariant& value) +{ + int dataType = T::column_type(stmt, col); + switch (dataType) + { + case T::INTEGER: + value = T::column_int64(stmt, col); + break; + case T::BLOB: + value = QByteArray( + static_cast<const char*>(T::column_blob(stmt, col)), + 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; + default: + value = QString( + reinterpret_cast<const QChar*>(T::column_text16(stmt, col)), + T::column_bytes16(stmt, col) / sizeof(QChar) + ); + break; + } + return T::OK; +} + +#endif // ABSTRACTDB3_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/asyncqueryrunner.cpp b/SQLiteStudio3/coreSQLiteStudio/db/asyncqueryrunner.cpp new file mode 100644 index 0000000..ff7e51a --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/asyncqueryrunner.cpp @@ -0,0 +1,62 @@ +#include "db/asyncqueryrunner.h" +#include "db/sqlquery.h" +#include "db/db.h" +#include <QDebug> + +AsyncQueryRunner::AsyncQueryRunner(const QString &query, const QVariant& args, Db::Flags flags) + : query(query), args(args), flags(flags) +{ + init(); +} + +void AsyncQueryRunner::init() +{ + setAutoDelete(false); +} + +void AsyncQueryRunner::run() +{ + if (!db || !db->isValid()) + { + qCritical() << "No Db or invalid Db defined in AsyncQueryRunner!"; + emit finished(this); + } + + SqlQueryPtr res; + if (args.userType() == QVariant::List) + { + res = db->exec(query, args.toList(), flags); + } + else if (args.userType() == QVariant::Hash) + { + res = db->exec(query, args.toHash(), flags); + } + else + { + qCritical() << "Invalid argument type in AsyncQueryRunner::run():" << args.userType(); + } + + results = SqlQueryPtr(res); + emit finished(this); +} + +SqlQueryPtr AsyncQueryRunner::getResults() +{ + return results; +} + + +void AsyncQueryRunner::setDb(Db *db) +{ + this->db = db; +} + +void AsyncQueryRunner::setAsyncId(quint32 id) +{ + asyncId = id; +} + +quint32 AsyncQueryRunner::getAsyncId() +{ + return asyncId; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/asyncqueryrunner.h b/SQLiteStudio3/coreSQLiteStudio/db/asyncqueryrunner.h new file mode 100644 index 0000000..5f0e41c --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/asyncqueryrunner.h @@ -0,0 +1,126 @@ +#ifndef ASYNCQUERYRUNNER_H +#define ASYNCQUERYRUNNER_H + +#include "db.h" + +#include <QVariant> +#include <QHash> +#include <QRunnable> +#include <QString> +#include <QPointer> +#include <QByteArray> + +/** + * @brief Direct query executor to be run in a thread. + * + * It's an implementation of QRunnable (so it can be run simply within QThread), + * that takes query string and arguments for the query and executes the query + * in separate thread (the one that is owning the runner). + * + * The runner is not deleted automatically. Instead the slot for finished() signal + * has to delete it. It's done like that because the slot will also be interested + * in execution results and asyncId, before the runner gets deleted. + * + * What it does is simply execute Db::exec() from another thread. + * + * It's a kind of helper class that is used to implement Db::asyncExec(). + */ +class AsyncQueryRunner : public QObject, public QRunnable +{ + Q_OBJECT + + public: + /** + * @brief Creates runner and defines basic parameters. + * @param query Query string to be executed. + * @param args Parameters to the query (can be either QHash or QList). + * @param flags Execution flags, that will be later passed to Db::exec(). + * + * It's not enough to just create runner. You also need to define db with setDb() + * and asyncId with setAsyncId(). + */ + AsyncQueryRunner(const QString& query, const QVariant& args, Db::Flags flags); + + /** + * @brief Executes query. + * + * This is the major method inherited from QRunnable. It's called from another thread + * and it executes the query. + */ + void run(); + + /** + * @brief Provides result from execution. + * @return Execution results. + */ + SqlQueryPtr getResults(); + + /** + * @brief Defines database for execution. + * @param db Database object. + */ + void setDb(Db* db); + + /** + * @brief Defines asynchronous ID for this execution. + * @param id Unique ID. + */ + void setAsyncId(quint32 id); + + /** + * @brief Provides previously defined asynchronous ID. + * @return Unique asynchronous ID. + */ + quint32 getAsyncId(); + + private: + /** + * @brief Initializes default values. + */ + void init(); + + /** + * @brief Database to execute the query on. + */ + Db* db = nullptr; + + /** + * @brief Query to execute. + */ + QString query; + + /** + * @brief Results from execution. + */ + SqlQueryPtr results; + + /** + * @brief Parameters for execution. + * + * It's either QList<QVariant> or QHash<QString,QVariant>. If it's anything else, + * then no execution will be performed and critical error will be logged. + */ + QVariant args; + + /** + * @brief The unique asynchronous ID for this query execution. + */ + quint32 asyncId; + + /** + * @brief Execution flags passed to Db::exec(). + */ + Db::Flags flags; + + signals: + /** + * @brief Emitted after the runner has finished its job. + * + * Slot connected to this signal should at least delete the runner, + * but it can also extract execution results. + */ + void finished(AsyncQueryRunner*); +}; + + +#endif // ASYNCQUERYRUNNER_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/attachguard.cpp b/SQLiteStudio3/coreSQLiteStudio/db/attachguard.cpp new file mode 100644 index 0000000..bcd4890 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/attachguard.cpp @@ -0,0 +1,20 @@ +#include "attachguard.h" +#include "db/db.h" + +GuardedAttach::GuardedAttach(Db* db, Db* attachedDb, const QString& attachName) : + db(db), attachedDb(attachedDb), name(attachName) +{ +} + +GuardedAttach::~GuardedAttach() +{ + if (name.isNull()) + return; + + db->detach(attachedDb); +} + +QString GuardedAttach::getName() const +{ + return name; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/attachguard.h b/SQLiteStudio3/coreSQLiteStudio/db/attachguard.h new file mode 100644 index 0000000..be708b8 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/attachguard.h @@ -0,0 +1,24 @@ +#ifndef ATTACHGUARD_H +#define ATTACHGUARD_H + +#include <QSharedPointer> + +class Db; + +class GuardedAttach +{ + public: + GuardedAttach(Db* db, Db* attachedDb, const QString& attachName); + virtual ~GuardedAttach(); + + QString getName() const; + + private: + Db* db = nullptr; + Db* attachedDb = nullptr; + QString name; +}; + +typedef QSharedPointer<GuardedAttach> AttachGuard; + +#endif // ATTACHGUARD_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.cpp b/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.cpp new file mode 100644 index 0000000..f36a3bd --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.cpp @@ -0,0 +1,200 @@ +#include "chainexecutor.h" +#include "sqlerrorcodes.h" +#include "db/sqlquery.h" +#include <QDebug> + +ChainExecutor::ChainExecutor(QObject *parent) : + QObject(parent) +{ +} + +bool ChainExecutor::getTransaction() const +{ + return transaction; +} + +void ChainExecutor::setTransaction(bool value) +{ + transaction = value; +} +QStringList ChainExecutor::getQueries() const +{ + return sqls; +} + +void ChainExecutor::setQueries(const QStringList& value) +{ + sqls = value; + queryParams.clear(); +} + +void ChainExecutor::exec() +{ + if (!db) + { + emit failure(SqlErrorCode::DB_NOT_DEFINED, tr("The database for executing queries was not defined.", "chain executor")); + return; + } + + if (!db->isOpen()) + { + emit failure(SqlErrorCode::DB_NOT_OPEN, tr("The database for executing queries was not open.", "chain executor")); + return; + } + + if (transaction && !db->begin()) + { + emit failure(db->getErrorCode(), tr("Could not start a database transaction. Details: %1", "chain executor").arg(db->getErrorText())); + return; + } + + currentSqlIndex = 0; + if (async) + executeCurrentSql(); + else + executeSync(); +} + +void ChainExecutor::interrupt() +{ + interrupted = true; + db->interrupt(); +} + +void ChainExecutor::executeCurrentSql() +{ + if (currentSqlIndex >= sqls.size()) + { + executionSuccessful(); + return; + } + + if (interrupted) + { + executionFailure(SqlErrorCode::INTERRUPTED, tr("Interrupted", "chain executor")); + return; + } + + asyncId = db->asyncExec(sqls[currentSqlIndex], queryParams); +} + +QList<bool> ChainExecutor::getMandatoryQueries() const +{ + return mandatoryQueries; +} + +void ChainExecutor::setMandatoryQueries(const QList<bool>& value) +{ + mandatoryQueries = value; +} + +Db* ChainExecutor::getDb() const +{ + return db; +} + +void ChainExecutor::setDb(Db* value) +{ + if (db) + disconnect(db, SIGNAL(asyncExecFinished(quint32,SqlQueryPtr)), this, SLOT(handleAsyncResults(quint32,SqlQueryPtr))); + + db = value; + + if (db) + connect(db, SIGNAL(asyncExecFinished(quint32,SqlQueryPtr)), this, SLOT(handleAsyncResults(quint32,SqlQueryPtr))); +} + + +void ChainExecutor::handleAsyncResults(quint32 asyncId, SqlQueryPtr results) +{ + if (asyncId != this->asyncId) + return; + + if (!handleResults(results)) + return; + + currentSqlIndex++; + executeCurrentSql(); +} + +void ChainExecutor::executionFailure(int errorCode, const QString& errorText) +{ + if (transaction) + db->rollback(); + + successfulExecution = false; + executionErrors << ExecutionError(errorCode, errorText); + emit failure(errorCode, errorText); +} + +void ChainExecutor::executionSuccessful() +{ + if (transaction && !db->commit()) + { + executionFailure(db->getErrorCode(), tr("Could not commit a database transaction. Details: %1", "chain executor").arg(db->getErrorText())); + return; + } + + successfulExecution = true; + emit success(); +} + +void ChainExecutor::executeSync() +{ + SqlQueryPtr results; + foreach (const QString& sql, sqls) + { + results = db->exec(sql, queryParams); + if (!handleResults(results)) + return; + + currentSqlIndex++; + } + executionSuccessful(); +} + +bool ChainExecutor::handleResults(SqlQueryPtr results) +{ + if (results->isError()) + { + if (interrupted || currentSqlIndex >= mandatoryQueries.size() || mandatoryQueries[currentSqlIndex]) + { + executionFailure(results->getErrorCode(), results->getErrorText()); + return false; + } + } + return true; +} +bool ChainExecutor::getSuccessfulExecution() const +{ + return successfulExecution; +} + +void ChainExecutor::setParam(const QString ¶mName, const QVariant &value) +{ + queryParams[paramName] = value; +} + +bool ChainExecutor::getAsync() const +{ + return async; +} + +void ChainExecutor::setAsync(bool value) +{ + async = value; +} + +QStringList ChainExecutor::getErrorsMessages() const +{ + QStringList msgs; + for (const ExecutionError& e : executionErrors) + msgs << e.second; + + return msgs; +} + +const QList<ChainExecutor::ExecutionError>& ChainExecutor::getErrors() const +{ + return executionErrors; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.h b/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.h new file mode 100644 index 0000000..2d3e3d3 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/chainexecutor.h @@ -0,0 +1,359 @@ +#ifndef CHAINEXECUTOR_H +#define CHAINEXECUTOR_H + +#include "db/db.h" +#include <QObject> + +// TODO add parameters support for ChainExecutor. +// it requires clever api, cause there can be multiple queries and each can use differend parameters, +// while we cannot apply same parameter set for each query, cause they are bind by an available list/hash. + +/** + * @brief Simple query executor, which executes queries one by one. + * + * A query executor which lets you execute query (or many queries) + * using asynchronous execution and it gets back to the called only when + * all queries succeeded, or it failed at some query. + * + * This is very useful if there is a sequence of queries to be executed + * and you're interested only in the result of the last query. + * + * It also lets to configure if queries should be executed within transaction, + * or not. + */ +class API_EXPORT ChainExecutor : public QObject +{ + Q_OBJECT + + public: + typedef QPair<int,QString> ExecutionError; + + /** + * @brief Creates executor. + * @param parent Parent object for QObject. + */ + explicit ChainExecutor(QObject *parent = 0); + + /** + * @brief Tells if transactional execution is enabled. + * @return Transactional execution status. + */ + bool getTransaction() const; + + /** + * @brief Enabled or disables transactional execution. + * @param value True to enable, false to disable. + * + * Transactional execution is enabled by default. It means that all defined SQL queries + * will be executed in single SQL transaction. + */ + void setTransaction(bool value); + + /** + * @brief Provides list of SQL queries configured for this executor. + * @return List of queries. + */ + QStringList getQueries() const; + + /** + * @brief Defines list of queries to be executed. + * @param value List of query strings. + * + * This is the main mathod you're interested in when using ChainExecutor. + * This is how you define what SQL queries will be executed. + * + * Calling this method will clear any parameters defined previously with setParam(). + */ + void setQueries(const QStringList& value); + + /** + * @brief Provides currently configured database. + * @return Database that the queries are executed on in this executor. + */ + Db* getDb() const; + + /** + * @brief Defines database for executing queries. + * @param value The database object. + * + * It is necessary to define the database before executing queries, + * otherwise the start() will emit failure() signal and do nothing else. + */ + void setDb(Db* value); + + /** + * @brief Provides list of configured query mandatory flags. + * @return List of flags. + * + * See setMandatoryQueries() for details on mandatory flags. + */ + QList<bool> getMandatoryQueries() const; + + /** + * @brief Defines list of mandatory flags for queries. + * @param value List of flags - a boolean per each defined query. + * + * Setting mandatory flags lets you define which queries (defined with setSqls()) + * are mandatory for the successful execution and which are not. + * Queries are mandatory by default (when flags are not defined), + * which means that every defined query execution must be successfull, + * otherwise executor breaks the execution chain and reports error. + * + * By defining mandatory flags to false for some queries, you're telling + * to ChainExecutor, that it's okay if those queries fail and it should + * move along. + * + * For example: + * @code + * ChainExecutor executor; + * executor.setSqls({ + * "DELETE FROM table1 WHERE value = 5", + * "DELETE FROM possibly_not_existing_table WHERE column > 3", + * "INSERT INTO table1 VALUES (4, 6)" + * }); + * executor.setMandatoryQueries({true, false, true}); + * @endcode + * We defined second query to be optional, therefore if the table + * "possibly_not_existing_table" doesn't exist, that's fine. + * It will be ignored and the third query will be executed. + * If flags were not defined, then execution of second query would fail, + * executor would stop there, report error (with failure() signal) + * and the third query would not be executed. + * + * It also affects transactions. If executor was defined to execute + * in a transaction (with setTransaction()), then failed query + * that was not mandatory will also not rollback the transaction. + * + * In other words, queries marked as not mandatory are silently ignored + * when failed. + */ + void setMandatoryQueries(const QList<bool>& value); + + /** + * @brief Provides list of execution error messages. + * @return List of messages. + * + * Execution error messages usually have zero or one message, + * but if you defined some queries to be not mandatory, + * then each failed optional query will be silently ignored, + * but its error message will be stored and returned by this method. + * In that case, the result of this method can provide more than + * one message. + */ + QStringList getErrorsMessages() const; + + /** + * @brief Provides list of execution errors. + * @return List of errors. + * + * These are the same errors as returned by getErrorsMessages(), except this list contains + * both error code (as returned from SQLite) and error message. + */ + const QList<ExecutionError>& getErrors() const; + + /** + * @brief Tells if the executor is configured for asynchronous execution. + * @return Asynchronous flag value. + */ + bool getAsync() const; + + /** + * @brief Defines asynchronous execution mode. + * @param value true to enable asynchronous execution, false to disable it. + * + * Asynchronous execution causes start() to return immediately. + * + * When asynchronous mode is enabled, results of execution + * have to be handled by connecting to failed() and success() signals. + * + * If the asynchronous mode is disabled, result can be queried + * by getSuccessfulExecution() call. + */ + void setAsync(bool value); + + /** + * @brief Tells if the most recent execution was successful. + * @return true if execution was successful, or false if it failed. + * + * Successful execution means that all mandatory queries + * (see setMandatoryQueries()) executed successfully. + * Optional (not mandatory) queries do not affect result of this method. + * + * If this method returns true, it also means that success() signal + * was emitted. + * If this method returns false, it also means that failure() signal + * was emitted. + */ + bool getSuccessfulExecution() const; + + /** + * @brief Defines named parameter to bind in queries. + * @param paramName Parameter name (must include the preceding ':'). + * @param value Value for the parameter. + * + * Any parameter defined with this method will be applied to each query + * executed by the executor. If some query doesn't include parameter + * placeholder with defined name, then the parameter will simply + * not be applied to that query. + */ + void setParam(const QString& paramName, const QVariant& value); + + private: + /** + * @brief Executes query defines as the current one. + * + * Checks is there is a current query defined (pointed by currentSqlIndex). + * If there is, then executes it. If not, goes to executionSuccessful(). + * + * This is called for each next query in asynchronous mode. + */ + void executeCurrentSql(); + + /** + * @brief Handles failed execution. + * @param errorCode Error code. + * @param errorText Error message. + * + * Rolls back transaction (in case of transactional execution) and emits failure(). + */ + void executionFailure(int errorCode, const QString& errorText); + + /** + * @brief Handles successful execution. + * + * Commits transaction (in case of transactional execution) and emits success(). + */ + void executionSuccessful(); + + /** + * @brief Executes all queries synchronously. + * + * If the asynchronous mode is disabled, then this method executes all queries. + */ + void executeSync(); + + /** + * @brief Handles single query execution results. + * @param results Results from the query. + * @return true if the execution was successful, or false otherwise. + * + * If there was an error while execution, then executionFailure() is also called. + */ + bool handleResults(SqlQueryPtr results); + + /** + * @brief Database for execution. + */ + Db* db = nullptr; + + /** + * @brief Transactional execution mode. + */ + bool transaction = true; + + /** + * @brief Asynchronous execution mode. + */ + bool async = true; + + /** + * @brief Queries to be executed. + */ + QStringList sqls; + + /** + * @brief List of flags for mandatory queries. + * + * See setMandatoryQueries() for details. + */ + QList<bool> mandatoryQueries; + + /** + * @brief Index pointing to the current query in sqls list. + * + * When executing query in asynchronous mode, this index points to the next + * query that should be executed. + */ + int currentSqlIndex = -1; + + /** + * @brief Asynchronous ID of current query execution. + * + * The ID is provided by Db::asyncExec(). + */ + quint32 asyncId = -1; + + /** + * @brief Execution interrupted flag. + * + * Once the interrup() was called, this flag is set to true, + * so the executor knows that it should not execute any further queries. + */ + bool interrupted = false; + + /** + * @brief Errors raised during queries execution. + * + * In case of major failure, the error message is appended to this list, + * but when mandatory flags allow some failures, than this list may + * contain more error messages. + */ + QList<ExecutionError> executionErrors; + + /** + * @brief Successful execution indicator. + * + * This is set after execution is finished. + */ + bool successfulExecution = false; + + /** + * @brief Parameters to bind to queries. + * + * This is filled with setParam() calls and used later to bind + * parameters to executed queries. + */ + QHash<QString,QVariant> queryParams; + + public slots: + /** + * @brief Interrupts query execution. + */ + void interrupt(); + + /** + * @brief Starts execution of all defined queries, one by one. + */ + void exec(); + + private slots: + /** + * @brief Handles asynchronous execution results from Db::asyncExec(). + * @param asyncId Asynchronous ID of the execution for the results. + * @param results Results returned from execution. + * + * Checks if given asynchronous ID matches the internally stored asyncId + * and if yes, then handles results and executes next query in the queue. + */ + void handleAsyncResults(quint32 asyncId, SqlQueryPtr results); + + signals: + /** + * @brief Emitted when all mandatory queries were successfully executed. + * + * See setMandatoryQueries() for details on mandatory queries. + */ + void success(); + + /** + * @brief Emitted when major error occurred while executing a query. + * @param errorCode Error code. + * @param errorText Error message. + * + * It's emitted only when mandatory query has failed execution. + * See setMandatoryQueries() for details on mandatory queries. + */ + void failure(int errorCode, const QString& errorText); +}; + +#endif // CHAINEXECUTOR_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/db.cpp b/SQLiteStudio3/coreSQLiteStudio/db/db.cpp new file mode 100644 index 0000000..c89349c --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/db.cpp @@ -0,0 +1,57 @@ +#include "db.h" +#include <QMetaEnum> + +Db::Db() +{ +} + +Db::~Db() +{ +} + +void Db::metaInit() +{ + qRegisterMetaType<Db*>("Db*"); + qRegisterMetaTypeStreamOperators<Db*>("Db*"); +} + +QString Db::flagsToString(Db::Flags flags) +{ + int idx = staticMetaObject.indexOfEnumerator("Flag"); + if (idx == -1) + return QString::null; + + QMetaEnum en = staticMetaObject.enumerator(idx); + return en.valueToKeys(static_cast<int>(flags)); +} + +QDataStream &operator <<(QDataStream &out, const Db* myObj) +{ + out << reinterpret_cast<quint64>(myObj); + return out; +} + + +QDataStream &operator >>(QDataStream &in, Db*& myObj) +{ + quint64 ptr; + in >> ptr; + myObj = reinterpret_cast<Db*>(ptr); + return in; +} + + +void Sqlite2ColumnDataTypeHelper::setBinaryType(int columnIndex) +{ + binaryColumns << columnIndex; +} + +bool Sqlite2ColumnDataTypeHelper::isBinaryColumn(int columnIndex) const +{ + return binaryColumns.contains(columnIndex); +} + +void Sqlite2ColumnDataTypeHelper::clearBinaryTypes() +{ + binaryColumns.clear(); +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/db.h b/SQLiteStudio3/coreSQLiteStudio/db/db.h new file mode 100644 index 0000000..7d10a05 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/db.h @@ -0,0 +1,831 @@ +#ifndef DB_H +#define DB_H + +#include "returncode.h" +#include "dialect.h" +#include "services/functionmanager.h" +#include "common/readwritelocker.h" +#include "coreSQLiteStudio_global.h" +#include "db/attachguard.h" +#include "interruptable.h" +#include "dbobjecttype.h" +#include <QObject> +#include <QVariant> +#include <QList> +#include <QHash> +#include <QReadWriteLock> +#include <QRunnable> +#include <QStringList> +#include <QSet> + +/** @file */ + +class AsyncQueryRunner; +class Db; +class DbManager; +class SqlQuery; + +typedef QSharedPointer<SqlQuery> SqlQueryPtr; + +/** + * @brief Option to make new Db instance not install any functions or collations in the database. + * + * This connection option should be used (with boolean value = true) when creating Db instance + * to be used internally (not exposed to the user) and you don't want any special features + * (like custom SQL functions, custom collations) to be registered in that database. + */ +static_char* DB_PURE_INIT = "sqlitestudio_pure_db_initalization"; + +/** + * @brief Option name for plugin handling the database. + * + * This is a constant naming the connection option, which tells SQLiteStudio which plugin was dedicated to handle + * particular database. + */ +static_char* DB_PLUGIN = "plugin"; + +/** + * @brief Database managed by application. + * + * Everything you might want to do with SQLite databases goes through this interface in the application. + * It's has a common interface for common database operations, such as connecting and disconnecting, + * checking current status, executing queries and reading results. + * It keeps information about the database version, dialect (SQLite 2 vs SQLite 3), encoding (UTF-8, UTF-16, etc.), + * symbolic name of the database and path to the file. + * + * Regular routine with the database object would be to open it (if not open yet), execute some query + * and collect results. It can be done in several ways, but here's simple one: + * @code + * QList<int> queryDb(const QString& dbName, const QString& colValue1, int colValue2) + * { + * // Getting database object by its name and opening it if necessary + * Db* db = DBLIST->getDb(dbName); + * if (!db) + * return; // no such database + * + * if (!db->isOpen()) + * db->open(); + * + * // Executing query and getting results + * SqlQueryPtr results = db->exec("SELECT intCol FROM table WHERE col1 = ?, col2 = ?", colValue1, colValue2) + * + * QList<int> resultList; + * SqlResultsRowPtr row; + * while (row = results->next()) + * { + * resultList << row->value("intCol").toInt(); + * } + * return resultList; + * } + * @endcode + * + * The example above is very generic way to do things. You can use many methods which simplifies tasks in case + * you work with smaller data sets. For example: + * @code + * int getRowId(Db* db, int colVal) + * { + * // We assume that db is already open + * return db->exec("SELECT rowid FROM table WHERE column = ?", colVal).getSingleCell().toInt(); + * } + * @endcode + * + * To write some data into database you can write as this: + * @code + * void insert(Db* db, const QString& val1, int val2) + * { + * // We assume that db is already open + * db->exec("INSERT INTO table (col1, col2) VALUES (?, ?)", val1, val2); + * } + * @endcode + * + * You can use named parameters: + * @code + * void insert(Db* db, const QString& val1, int val2) + * { + * QHash<QString,QVariant> params; + * params["c1"] = val1; + * params["c2"] = val2; + * db->exec("INSERT INTO table (col1, col2) VALUES (:c1, :c2)", params); + * } + * @endcode + * + * To check if the execution was successful, test results: + * @code + * void insert(Db* db, const QString& val1, int val2) + * { + * SqlQueryPtr results = db->exec("INSERT INTO table (col1, col2) VALUES (?, ?)", val1, val2); + * if (results->isError()) + * { + * qWarning() << "Error while inserting:" << results->getErrorCode() << results->getErrorText(); + * } + * } + * @endcode + * + * @see DbBase + * @see DbQt + * @see DbQt2 + * @see DbQt3 + */ +class API_EXPORT Db : public QObject, public Interruptable +{ + Q_OBJECT + + public: + /** + * @brief Flags for query execution. + * + * Those flags are used by exec() and asyncExec(). They can be used with bit-wise operators. + */ + enum class Flag + { + NONE = 0x0, /**< No flags. This is default. */ + PRELOAD = 0x1, /**< Preloads all execution results into the results object. Useful for asynchronous execution. */ + 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 + * of code, where the lock on Db was already set. Never (!) use this to ommit lock from different + * 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. + */ + }; + Q_DECLARE_FLAGS(Flags, Flag) + + /** + * @brief Function to handle SQL query results. + * + * The function has to accept single results object and return nothing. + * After results are processed, they will be deleted automatically, no need to handle that. + */ + typedef std::function<void(SqlQueryPtr)> QueryResultsHandler; + + /** + * @brief Default, empty constructor. + */ + Db(); + + /** + * @brief Releases resources. + * + * Detaches any attached databases and closes the database if open. + */ + virtual ~Db(); + + /** + * @brief Registers Db in Qt meta subsystem. + * + * It's called at the application startup. Makes Db* supported by Qt meta subsystem. + */ + static void metaInit(); + + /** + * @brief Converts flags into string representation. + * @param flags Flags to convert. Can be multiple flags OR'ed. + * @return Flags as string representation, for example: STRING_REPLACE_ARGS. + */ + static QString flagsToString(Flags flags); + + /** + * @brief Checks if database is open (connected). + * @return true if the database is connected, or false otherwise. + */ + virtual bool isOpen() = 0; + + /** + * @brief Gets database symbolic name. + * @return Database symbolic name (as it was defined in call to DbManager#addDb() or DbManager#updateDb()). + */ + virtual QString getName() = 0; + + /** + * @brief Gets database file path. + * @return Database file path (as it was defined in call to DbManager#addDb() or DbManager#updateDb()). + */ + virtual QString getPath() = 0; + + /** + * @brief Gets SQLite version major number for this database. + * @return Major version number, that is 3 for SQLite 3.x.x and 2 for SQLite 2.x.x. + * + * You don't have to open the database. This information is always available. + */ + virtual quint8 getVersion() = 0; + + /** + * @brief Gets database dialect. + * @return Database dialect, which is either Sqlite2 or Sqlite3. + * + * You don't have to open the database. This information is always available. + */ + virtual Dialect getDialect() = 0; + + /** + * @brief Gets database encoding. + * @return Database encoding as returned from SQLite query: <tt>PRAGMA encoding;</tt> + * + * If the database is not open, then this methods quickly opens it, queries the encoding and closes the database. + * The opening and closing of the database is not visible outside, it's just an internal operation. + */ + virtual QString getEncoding() = 0; + + /** + * @brief Gets connection options. + * @return Connection options, the same as were passed to DbManager#addDb() or DbManager#updateDb(). + */ + virtual QHash<QString,QVariant>& getConnectionOptions() = 0; + + /** + * @brief Sets new name for the database. + * @param value New name. + * + * This method works only on closed databases. If the database is open, then warning is logged + * and function does nothing more. + */ + virtual void setName(const QString& value) = 0; + + /** + * @brief Sets new file path for the database. + * @param value New file path. + * + * This method works only on closed databases. If the database is open, then warning is logged + * and function does nothing more. + */ + virtual void setPath(const QString& value) = 0; + + /** + * @brief Sets connection options for the database. + * @param value Connection options. See DbManager::addDb() for details. + * + * This method works only on closed databases. If the database is open, then warning is logged + * and function does nothing more. + */ + virtual void setConnectionOptions(const QHash<QString,QVariant>& value) = 0; + + /** + * @brief Sets the timeout for waiting for the database to be unlocked. + * @param secs Number of seconds. + * + * When the database is locked by another application, then the SQLiteStudio will wait given number + * of seconds for the database to be released, before the execution error is reported. + * + * Set it to negative value to set infinite timeout. + * + * This doesn't involve locking done by SQLiteStudio internally (see Db::Flag::NO_LOCK), which doesn't time out. + */ + virtual void setTimeout(int secs) = 0; + + /** + * @brief Gets the current database lock waiting timeout value. + * @return Number of seconds to wait for the database to be released. + * + * See setTimeout() for details. + */ + virtual int getTimeout() const = 0; + + /** + * @brief Executes SQL query. + * @param query Query to be executed. Parameter placeholders can be either of: ?, :param, \@param, just don't mix different types in single query. + * @param args List of values to bind to parameter placeholders. As those are unnamed parameters, the order is important. + * @param flags Execution flags. + * @return Execution results. + * + * Executes SQL query and returns results. If there was an error, the results will tell you when you call SqlResults::isError(). + * + * Queries like SELECT, INSERT, UPDATE, and DELETE accept positional parameters, but only for column values. If you would like to pass table name + * for SELECT, you would have to use Flags::STRING_REPLACE_ARGS and parameter placeholders in format %1, %2, %3, and so on. You cannot mix + * string parameters (as for Flags::STRING_REPLACE_ARGS) and regular SQLite parameters in single query. If you really need to, then you should + * build query string first (using QString::arg() for string parameters) and then pass it to exec(), which will accept SQLite parameters binding. + * + * If the query doesn't return any interesting results (for example it's INSERT) and you don't care about errors, you can safely ignore results object. + * The result object is shared pointer, therefore it will delete itself if not used. + * + * Given C++11 you can initialize list with braces, like this: + * @code + * SqlQueryPtr results = db->exec("SELECT * FROM table WHERE c1 = ? AND c2 = ? AND c3 = ? AND c4 = ?", + * {45, 76, "test", 3.56}); + * @endcode + */ + virtual SqlQueryPtr exec(const QString& query, const QList<QVariant> &args, Flags flags = Flag::NONE) = 0; + + /** + * @brief Executes SQL query using named parameters. + * @param query Query to be executed. Parameter placeholders can be either of: :param, \@param, just don't mix different types in single query. + * @param args Map of parameter name and the value assigned to it. + * @param flags Execution flags. See exec() for setails. + * @return Execution results. + * + * Given C++11 you can initialize hash map with braces, like this: + * @code + * SqlQueryPtr results = db->exec("SELECT * FROM table WHERE id = :userId AND name = :firstName", + * { + * {":userId", 45}, + * {":firstName", "John"} + * }); + * @endcode + * + * @overload + */ + virtual SqlQueryPtr exec(const QString& query, const QHash<QString, QVariant>& args, Flags flags = Flag::NONE) = 0; + + /** + * @brief Executes SQL query. + * @overload + */ + virtual SqlQueryPtr exec(const QString &query, Db::Flags flags = Flag::NONE) = 0; + + /** + * @brief Executes SQL query. + * @overload + */ + virtual SqlQueryPtr exec(const QString &query, const QVariant &arg) = 0; + + /** + * @brief Executes SQL query. + * @overload + */ + virtual SqlQueryPtr exec(const QString &query, std::initializer_list<QVariant> argList) = 0; + + /** + * @brief Executes SQL query. + * @overload + */ + virtual SqlQueryPtr exec(const QString &query, std::initializer_list<std::pair<QString,QVariant>> argMap) = 0; + + /** + * @brief Executes SQL query asynchronously using list of parameters. + * @param query Query to be executed. Parameter placeholders can be either of: ?, :param, \@param, just don't mix different types in single query. + * @param args List of parameter values to bind. + * @param resultsHandler Function (can be lambda) to handle results. The function has to accept single SqlQueryPtr object and return nothing. + * @param flags Execution flags. See exec() for setails. + * + * Asynchronous execution takes place in another thread. Once the execution is finished, the results handler function is called. + * + * Example: + * @code + * db->asyncExec("SELECT * FROM table WHERE col = ?", {5}, [=](SqlQueryPtr results) + * { + * qDebug() << "Received" << results->rowCount() << "rows in results."; + * }); + * @endcode + */ + virtual void asyncExec(const QString& query, const QList<QVariant>& args, QueryResultsHandler resultsHandler, Flags flags = Flag::NONE) = 0; + + /** + * @brief Executes SQL query asynchronously using named parameters. + * @param query Query to be executed. Parameter placeholders can be either of: :param, \@param, just don't mix different types in single query. + * @param args Map of parameter name and the value assigned to it. + * @param resultsHandler Function (can be lambda) to handle results. The function has to accept single SqlQueryPtr object and return nothing. + * @param flags Execution flags. See exec() for details. + * @return Asynchronous execution ID. + * @overload + */ + virtual void asyncExec(const QString& query, const QHash<QString, QVariant>& args, QueryResultsHandler resultsHandler, Flags flags = Flag::NONE) = 0; + + /** + * @brief Executes SQL query asynchronously. + * @param query Query to be executed. See exec() for details. + * @param resultsHandler Function (can be lambda) to handle results. The function has to accept single SqlQueryPtr object and return nothing. + * @param flags Execution flags. See exec() for details. + * @return Asynchronous execution ID. + * @overload + */ + virtual void asyncExec(const QString& query, QueryResultsHandler resultsHandler, Flags flags = Flag::NONE) = 0; + + /** + * @brief Executes SQL query asynchronously using list of parameters. + * @param query Query to be executed. Parameter placeholders can be either of: ?, :param, \@param, just don't mix different types in single query. + * @param args List of parameter values to bind. + * @param flags Execution flags. See exec() for setails. + * @return Asynchronous execution ID. + * + * Asynchronous execution takes place in another thread. Once the execution is finished, the results is provided + * with asyncExecFinished() signal. You should get the ID from results of this method and compare it with ID + * from the signal, so when it matches, it means that the results object from signal is the answer to this execution. + * + * It's recommended to use method version which takes function pointer for results handing, as it's more resiliant to errors in the code. + * + * Given C++11 you can initialize list with braces, like this: + * @code + * int asyncId = db->asyncExec("SELECT * FROM table WHERE c1 = ? AND c2 = ? AND c3 = ? AND c4 = ?", + * {45, 76, "test", 3.56}); + * @endcode + */ + virtual quint32 asyncExec(const QString& query, const QList<QVariant>& args, Flags flags = Flag::NONE) = 0; + + /** + * @brief Executes SQL query asynchronously using named parameters. + * @param query Query to be executed. Parameter placeholders can be either of: :param, \@param, just don't mix different types in single query. + * @param args Map of parameter name and the value assigned to it. + * @param flags Execution flags. See exec() for details. + * @return Asynchronous execution ID. + * @overload + * + * It's recommended to use method version which takes function pointer for results handing, as it's more resiliant to errors in the code. + */ + virtual quint32 asyncExec(const QString& query, const QHash<QString, QVariant>& args, Flags flags = Flag::NONE) = 0; + + /** + * @brief Executes SQL query asynchronously. + * @param query Query to be executed. See exec() for details. + * @param flags Execution flags. See exec() for details. + * @return Asynchronous execution ID. + * @overload + * + * It's recommended to use method version which takes function pointer for results handing, as it's more resiliant to errors in the code. + */ + virtual quint32 asyncExec(const QString& query, Flags flags = Flag::NONE) = 0; + + virtual SqlQueryPtr prepare(const QString& query) = 0; + + /** + * @brief Begins SQL transaction. + * @return true on success, or false on failure. + * + * This method uses basic "BEGIN" statement to begin transaction, therefore recurrent transactions are not supported. + * This is because SQLite2 doesn't support "SAVEPOINT" and this is the common interface for all SQLite versions. + */ + virtual bool begin() = 0; + + /** + * @brief Commits SQL transaction. + * @return true on success, or false otherwise. + */ + virtual bool commit() = 0; + + /** + * @brief Rolls back the transaction. + * @return true on success, or false otherwise (i.e. there was no transaction open, there was a connection problem, etc). + */ + virtual bool rollback() = 0; + + /** + * @brief Interrupts current execution asynchronously. + * + * It's almost the same as interrupt(), except it returns immediately, instead of waiting for the interruption to finish. + * In case of some heavy queries the interruption process might take a little while. + */ + virtual void asyncInterrupt() = 0; + + /** + * @brief Checks if the database is readable at the moment. + * @return true if the database is readable, or false otherwise. + * + * The database can be in 3 states: not locked, locked for reading or locked for writing. + * If it's locked for writing, than it's not readable and this method will return false. + * If it's locked for reading or not locked at all, then this method will return true. + * Database can be locked by other threads executing their queries on the database. + */ + virtual bool isReadable() = 0; + + /** + * @brief Checks if the database is writable at the moment. + * @return true if the database is writable, or false otherwise. + * + * The database can be in 3 states: not locked, locked for reading or locked for writing. + * If it's locked for writing (by other thread) or reading, than it's not writable and this method will return false. + * If it's not locked at all, then this method will return true. + * Database can be locked by other threads executing their queries on the database. + */ + virtual bool isWritable() = 0; + + /** + * @brief Tells if the database is valid for operating on it. + * @return true if the databse is valid, false otherwise. + * + * A valid database is the one that has valid path and driver plugin support loaded. + * Invalid database is the one that application failed to load. Those are marked with the exclamation icon on the UI. + */ + virtual bool isValid() const = 0; + + /** + * @brief Attaches given database to this database. + * @param otherDb Other registered database object. + * @param silent If true, no errors or warnings will be reported to the NotifyManager (they will still appear in logs). + * @return Name of the attached database (it's not the symbolic name of the other database, it's a name you would use in <tt>ATTACH 'name'</tt> statement). + * + * This is convinent method to attach other registered databases to this database. It generates attached database name, so it doesn't conflict + * with other - already attached - database names, attaches the database with that name and returns that name to you, so you can refer to it in queries. + */ + virtual QString attach(Db* otherDb, bool silent = false) = 0; + + /** + * @brief Attaches given database to this database using guarded attach. + * @param otherDb Other registered database object. + * @param silent If true, no errors or warnings will be reported to the NotifyManager (they will still appear in logs). + * @return Guarded attach instance with the name of the attached database inside. + * + * The guarded attach automatically detaches attached database when the attach guard is destroyed (goes out of scope). + * The AttachGuard is in fact a QSharedPointer, so you can pass it by value to other functions prolong attchment. + */ + virtual AttachGuard guardedAttach(Db* otherDb, bool silent = false) = 0; + + /** + * @brief Detaches given database from this database. + * @param otherDb Other registered database object. + * + * If the otherDb is not attached, this method does nothing. Otherwise it calls <tt>DETACH</tt> statement using the attach name generated before by attach(). + * You don't have to provide the attach name, as Db class remembers those names internally. + */ + virtual void detach(Db* otherDb) = 0; + + /** + * @brief Detaches all attached databases. + * + * Detaches all attached databases. This includes only databases attached with attach(). Databases attached with manual <tt>ATTACH</tt> query execution + * will not be detached. + */ + virtual void detachAll() = 0; + + /** + * @brief Gets attached databases. + * @return Table of attached databases and the attach names used to attach them. + * + * This method returns only databases attached with attach() method. + */ + virtual const QHash<Db*,QString>& getAttachedDatabases() = 0; + + /** + * @brief Gets all attached databases. + * @return Set of attach names. + * + * This method returns all attached database names (the attach names), including both those from attach() and manual <tt>ATTACH</tt> query execution. + */ + virtual QSet<QString> getAllAttaches() = 0; + + /** + * @brief Generates unique name for object to be created in the database. + * @param attachedDbName Optional attach name, so the name will be in context of that database. + * @return Unique object name. + * + * Queries database for all existing objects and then generates name that is not on that list. + * The generated name is a random string of length 16. + */ + virtual QString getUniqueNewObjectName(const QString& attachedDbName = QString()) = 0; + + /** + * @brief Gets last error string from database driver. + * @return Last encountered error. + * + * Result of this method is determinated by DbPlugin. + */ + virtual QString getErrorText() = 0; + + /** + * @brief Gets last error code from database driver. + * @return Code of last encountered error. + * + * Result of this method is determinated by DbPlugin. + */ + virtual int getErrorCode() = 0; + + /** + * @brief Gets database type label. + * @return Database type label. + * + * The database type label is used on UI to tell user what database it is (SQLite 3, SQLite 2, Encrypted SQLite 3, etc). + * This is defined by DbPlugin. + */ + virtual QString getTypeLabel() = 0; + + /** + * @brief Initializes resources once the all derived Db classes are constructed. + * @return true on success, false on failure. + * + * It's called just after this object was created. Implementation of this method can call virtual methods, which was a bad idea + * to do in constructor (because of how it's works in C++, if you didn't know). + * + * It usually queries database for it's version, etc. + */ + virtual bool initAfterCreated() = 0; + + /** + * @brief Deregisters custom SQL function from this database. + * @param name Name of the function. + * @param argCount Number of arguments accepted by the function (-1 for undefined). + * @return true if deregistering was successful, or false otherwise. + * + * @see FunctionManager + */ + virtual bool deregisterFunction(const QString& name, int argCount) = 0; + + /** + * @brief Registers scalar custom SQL function. + * @param name Name of the function. + * @param argCount Number of arguments accepted by the function (-1 for undefined). + * @return true on success, false on failure. + * + * Scalar functions are evaluated for each row and their result is used in place of function invokation. + * Example of SQLite built-in scalar function is abs(), or length(). + * + * This method is used only to let the database know, that the given function exists in FunctionManager and we want it to be visible + * in this database's context. When the function is called from SQL query, then the function execution is delegated to the FunctionManager. + * + * For details about usage of custom SQL functions see http://wiki.sqlitestudio.pl/index.php/User_Manual#Custom_SQL_functions + * + * @see FunctionManager + */ + virtual bool registerScalarFunction(const QString& name, int argCount) = 0; + + /** + * @brief Registers aggregate custom SQL function. + * @param name Name of the function. + * @param argCount Number of arguments accepted by the function (-1 for undefined). + * @return true on success, false on failure. + * + * Aggregate functions are used to aggregate many rows into single row. They are common in queries with GROUP BY statements. + * The aggregate function in SQLite is actually implemented by 2 functions - one for executing per each row (and which doesn't return any result yet, + * just collects the data) and then the second function, executed at the end. The latter one must return the result, which becomes the result + * of aggregate function. + * + * Aggregate functions in SQLiteStudio are almost the same as in SQLite itself, except SQLiteStudio has also a third function, which is called + * at the very begining, before the first "per step" function is called. It's used to initialize anything that the step function might need. + * + * This method is used only to let the database know, that the given function exists in FunctionManager and we want it to be visible + * in this database's context. When the function is called from SQL query, then the function execution is delegated to the FunctionManager. + * + * For details about usage of custom SQL functions see http://wiki.sqlitestudio.pl/index.php/User_Manual#Custom_SQL_functions + * + * @see FunctionManager + */ + virtual bool registerAggregateFunction(const QString& name, int argCount) = 0; + + /** + * @brief Registers a collation sequence implementation in the database. + * @param name Name of the collation. + * @return true on success, false on failure. + * + * Collations are not supported by SQLite 2, so this method will always fail for those databases. + * + * Collations are handled by CollationManager. Each collation managed by the manager has a code implemented to return -1, 0 or 1 + * when comparing 2 values in the database in order to sort query results. The name passed to this method is a name of the collation + * as it is used in SQL queries and also the same name must be used when defining collation in Collations editor window. + * + * For details about usage of custom collations see http://wiki.sqlitestudio.pl/index.php/User_Manual#Custom_collations + * + * @see CollationManager + */ + virtual bool registerCollation(const QString& name) = 0; + + /** + * @brief Deregisters previously registered collation from this database. + * @param name Collation name. + * @return true on success, false on failure. + * + * See registerCollation() for details on custom collations. + */ + virtual bool deregisterCollation(const QString& name) = 0; + + signals: + /** + * @brief Emitted when the connection to the database was established. + */ + void connected(); + + /** + * @brief Emitted after connection to the database was closed. + */ + void disconnected(); + + /** + * @brief Emitted when other database was attached to this datbase. + * @param db Other database that was attached. + * + * This is emitted only when the database was attached with attach() call. + * Manual "ATTACH" query execution doesn't cause this signal to be emitted. + */ + void attached(Db* db); + + /** + * @brief Emitted when other database was detached from this datbase. + * @param db Other database that was detached. + * + * This is emitted only when the database was detached with detach() call. + * Manual "DETACH" query execution doesn't cause this signal to be emitted. + */ + void detached(Db* db); + + //void attached(QString db); // TODO emit when called by user's sql + //void detached(QString db); // TODO emit when called by user's sql + + /** + * @brief Emitted when the asynchronous execution was finished. + * @param asyncId Asynchronous ID. + * @param results Results from query execution. + * + * This signal is emitted only when no handler function was passed to asyncExec(). + * It's emitted, so the results can be handled. + * Always test \p asyncId if it's equal to ID returned from asyncExec(). + */ + void asyncExecFinished(quint32 asyncId, SqlQueryPtr results); + + /** + * @brief idle Database became idle and awaits for instructions. + * + * This signal is emited after async execution has finished. + * It is important to re-check isWritable() or isReadable() + * in any slot connected to this signal, because some other slot + * called before currently processed slot could already order + * another async execution. + */ + void idle(); + + /** + * @brief Emitted when any database object (table, index, trigger, or view) was just deleted from this database. + * @param database Database (attach) name from which the object was deleted. Usually the "main". + * @param name Name of the object deleted. + * @param type Type of the object deleted. + * + * This signal covers only deletions made by this database of course. Deletions made by any other application + * are not announced by this signal (as this is impossible to detect it just like that). + */ + void dbObjectDeleted(const QString& database, const QString& name, DbObjectType type); + + /** + * @brief Emitted just before disconnecting and user can deny it. + * @param disconnectingDenied If set to true by anybody, then disconnecting is aborted. + */ + void aboutToDisconnect(bool& disconnectingDenied); + + public slots: + /** + * @brief Opens connection to the database. + * @return true on success, false on error. + * + * Emits connected() only on success. + */ + virtual bool open() = 0; + + /** + * @brief Closes connection to the database. + * @return true on success, false on error. + * + * Emits disconnected() only on success (i.e. db was open before). + */ + virtual bool close() = 0; + + /** + * @brief Opens connection to the database quietly. + * @return true on success, false on error. + * + * Opens database, doesn't emit any signal. + */ + virtual bool openQuiet() = 0; + + /** + * @brief Opens connection to the database quietly, without applying any specific settings. + * @return true on success, false on error. + * + * Opens database, doesn't emit any signal. It also doesn't apply any pragmas, neither registers + * functions or collations. It should be used when you want to do some basic query on the database, + * like when you probe the database for being the correct database for this implementation (driver, etc). + * Actually, that's what DbPluginSqlite3 plugin (among others) use. + * + * To close database open with this method use closeQuiet(). + */ + virtual bool openForProbing() = 0; + + /** + * @brief Closes connection to the database quietly. + * @return true on success, false on error. + * + * Closes database, doesn't emit any signal. + */ + virtual bool closeQuiet() = 0; + + /** + * @brief Deregisters all funtions registered in the database and registers new (possibly the same) functions. + * + * This slot is called from openAndSetup() and then every time user modifies custom SQL functions and commits changes to them. + * It deregisters all functions registered before in this database and registers new functions, currently defined for + * this database. + * + * @see FunctionManager + */ + virtual void registerAllFunctions() = 0; + + /** + * @brief Deregisters all collations registered in the database and registers new (possibly the same) collations. + * + * This slot is called from openAndsetup() and then every time user modifies custom collations and commits changes to them. + */ + virtual void registerAllCollations() = 0; +}; + +QDataStream &operator<<(QDataStream &out, const Db* myObj); +QDataStream &operator>>(QDataStream &in, Db*& myObj); + +Q_DECLARE_METATYPE(Db*) +Q_DECLARE_OPERATORS_FOR_FLAGS(Db::Flags) + +class API_EXPORT Sqlite2ColumnDataTypeHelper +{ + public: + void setBinaryType(int columnIndex); + bool isBinaryColumn(int columnIndex) const; + void clearBinaryTypes(); + + private: + QSet<int> binaryColumns; +}; + +#endif // DB_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/dbpluginoption.h b/SQLiteStudio3/coreSQLiteStudio/db/dbpluginoption.h new file mode 100644 index 0000000..7b594ef --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/dbpluginoption.h @@ -0,0 +1,97 @@ +#ifndef DBPLUGINOPTION_H +#define DBPLUGINOPTION_H + +#include <QString> +#include <QVariant> + +/** + * @brief Database plugin connection options. + * + * It is used to identify connection options that the DbPlugin implementation needs + * for the plugin to be configured by the user in the DbDialog. + * + * Single DbPluginOption represents in DbDialog: + * <ul> + * <li>single QLabel with text set to DbPluginOption::label,</li> + * <li>an input widget, that depends on DbPluginOption::type.</li> + * </ul> + * + * The input widget is different for different data type expected for the option. + * See DbPluginOption::Type for details. + * + * After user entered his values for options in DbDialog, they are passed + * to the DbPlugin::getInstance() and later to the Db::init(). Options are passed + * as key-value pairs, given the DbPluginOption::key and value specified by the user + * in DbDialog. + */ +struct DbPluginOption +{ + /** + * @brief Option data type + * + * Determinates what kind of input widget will be added to DbDialog. + */ + enum Type + { + STRING = 0, /**< QLineEdit will be added. */ + INT = 1, /**< QSpinBox will be added */ + BOOL = 2, /**< QCheckBox will be added */ + DOUBLE = 3, /**< QDoubleSpinBox will be added */ + FILE = 4, /**< QLineEdit will be added */ + PASSWORD = 5, /**< QLineEdit with value masking will be added */ + CHOICE = 6 /**< QComboBox will be added */ + }; + + /** + * @brief Name for the key in QHash collected from options in DbDialog and + * later passed to DbPlugin::getInstance(). + */ + QString key; + + /** + * @brief Label text to be used in DbDialog to inform user what is this option. + */ + QString label; + + /** + * @brief Optional tooltip to show for added widget. + */ + QString toolTip; + + /** + * @brief Optional placeholder text for QLineEdit widget. + */ + QString placeholderText; + + /** + * @brief List of values for QComboBox. + */ + QStringList choiceValues; + + /** + * @brief Default value to be set in the editor widget. + */ + QVariant defaultValue; + + /** + * @brief Indicates if the combobox should be read only or writable. + */ + bool choiceReadOnly = true; + + /** + * @brief Minimum value for numeric editors (double or int). + */ + QVariant minValue; + + /** + * @brief Maximum value for numeric editors (double or int). + */ + QVariant maxValue; + + /** + * @brief Expected data type for the option. + */ + Type type; +}; + +#endif // DBPLUGINOPTION_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/dbsqlite.h.autosave b/SQLiteStudio3/coreSQLiteStudio/db/dbsqlite.h.autosave new file mode 100644 index 0000000..9d11dac --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/dbsqlite.h.autosave @@ -0,0 +1,72 @@ +#ifndef DBSQLITE_H +#define DBSQLITE_H + +#include "db.h" +#include "../returncode.h" +#include "sqlerror.h" +#include "sqlresults.h" +#include "../dialect.h" + +#include <QObject> +#include <QSqlDatabase> +#include <QVariant> +#include <QList> +#include <QMap> +#include <QHash> +#include <QMutex> +#include <QRunnable> + +class AsyncQueryRunner; + +class DbSqlite : public Db +{ + Q_OBJECT + + public: + virtual ~DbSqlite(); + + static DbPtr getInstance(const QString &name, const QString& path, + const QString &options = QString::null); + + QString getName(); + QString getPath(); + quint8 getVersion(); + virtual QString driver() = 0; + Dialect getDialect(); + + quint32 asyncExec(const QString& query, const QVariant& arg1 = QVariant(), + const QVariant& arg2 = QVariant(), const QVariant& arg3 = QVariant()); + quint32 asyncExecStr(const QString& query, const QVariant& arg1 = QVariant(), + const QVariant& arg2 = QVariant(), const QVariant& arg3 = QVariant()); + quint32 asyncExecArgs(const QString& query, const QList<QVariant>& args); + quint32 asyncExecArgs(const QString& query, const QMap<QString,QVariant>& args); + + void begin(); + bool commit(); + void rollback(); + QString getType(); + SqlError lastError(); + + protected: + Db(); + + void cleanUp(); + QString generateUniqueDbName(); + bool isOpenNoLock(); + quint32 asyncExec(AsyncQueryRunner* runner); + SqlResultsPtr execInternal(const QString& query, const QList<QVariant>& args, + bool singleCell); + SqlResultsPtr execInternal(const QString& query, const QMap<QString,QVariant>& args, + bool singleCell); + bool init(); + + QSqlDatabase db; + quint8 version = 0; + + public slots: + bool openQuiet(); + bool closeQuiet(); + +}; + +#endif // DBSQLITE_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/dbsqlite3.cpp b/SQLiteStudio3/coreSQLiteStudio/db/dbsqlite3.cpp new file mode 100644 index 0000000..a4a8b73 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/dbsqlite3.cpp @@ -0,0 +1,11 @@ +#include "dbsqlite3.h" + +DbSqlite3::DbSqlite3(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions) : + AbstractDb3(name, path, connOptions) +{ +} + +DbSqlite3::DbSqlite3(const QString& name, const QString& path) : + DbSqlite3(name, path, QHash<QString,QVariant>()) +{ +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/dbsqlite3.h b/SQLiteStudio3/coreSQLiteStudio/db/dbsqlite3.h new file mode 100644 index 0000000..29db5a8 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/dbsqlite3.h @@ -0,0 +1,33 @@ +#ifndef DBSQLITE3_H +#define DBSQLITE3_H + +#include "abstractdb3.h" +#include "common/global.h" +#include "stdsqlite3driver.h" +#include <sqlite3.h> + +STD_SQLITE3_DRIVER(Sqlite3, "SQLite 3",,) + +class API_EXPORT DbSqlite3 : public AbstractDb3<Sqlite3> +{ + public: + /** + * @brief Creates SQLite database object. + * @param name Name for the database. + * @param path File path of the database. + * @param connOptions Connection options. See AbstractDb for details. + * + * All values from this constructor are just passed to AbstractDb3 constructor. + */ + DbSqlite3(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions); + + /** + * @brief Creates SQLite database object. + * @param name Name for the database. + * @param path File path of the database. + * @overload + */ + DbSqlite3(const QString& name, const QString& path); +}; + +#endif // DBSQLITE3_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/invaliddb.cpp b/SQLiteStudio3/coreSQLiteStudio/db/invaliddb.cpp new file mode 100644 index 0000000..e4810a1 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/invaliddb.cpp @@ -0,0 +1,336 @@ +#include "invaliddb.h" +#include "common/unused.h" +#include <QSet> + +InvalidDb::InvalidDb(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions) : + name(name), path(path), connOptions(connOptions) +{ +} + +bool InvalidDb::isOpen() +{ + return false; +} + +QString InvalidDb::getName() +{ + return name; +} + +QString InvalidDb::getPath() +{ + return path; +} + +quint8 InvalidDb::getVersion() +{ + return 0; +} + +Dialect InvalidDb::getDialect() +{ + return Dialect::Sqlite3; +} + +QString InvalidDb::getEncoding() +{ + return QString::null; +} + +QHash<QString, QVariant>& InvalidDb::getConnectionOptions() +{ + return connOptions; +} + +void InvalidDb::setName(const QString& value) +{ + name = value; +} + +void InvalidDb::setPath(const QString& value) +{ + path = value; +} + +void InvalidDb::setConnectionOptions(const QHash<QString, QVariant>& value) +{ + connOptions = value; +} + +void InvalidDb::setTimeout(int secs) +{ + timeout = secs; +} + +int InvalidDb::getTimeout() const +{ + return timeout; +} + +SqlQueryPtr InvalidDb::exec(const QString& query, const QList<QVariant>& args, Db::Flags flags) +{ + UNUSED(query); + UNUSED(args); + UNUSED(flags); + return SqlQueryPtr(); +} + +SqlQueryPtr InvalidDb::exec(const QString& query, const QHash<QString, QVariant>& args, Db::Flags flags) +{ + UNUSED(query); + UNUSED(args); + UNUSED(flags); + return SqlQueryPtr(); +} + +SqlQueryPtr InvalidDb::exec(const QString& query, Db::Flags flags) +{ + UNUSED(query); + UNUSED(flags); + return SqlQueryPtr(); +} + +SqlQueryPtr InvalidDb::exec(const QString& query, const QVariant& arg) +{ + UNUSED(query); + UNUSED(arg); + return SqlQueryPtr(); +} + +SqlQueryPtr InvalidDb::exec(const QString& query, std::initializer_list<QVariant> argList) +{ + UNUSED(query); + UNUSED(argList); + return SqlQueryPtr(); +} + +SqlQueryPtr InvalidDb::exec(const QString& query, std::initializer_list<std::pair<QString, QVariant> > argMap) +{ + UNUSED(query); + UNUSED(argMap); + return SqlQueryPtr(); +} + +void InvalidDb::asyncExec(const QString& query, const QList<QVariant>& args, Db::QueryResultsHandler resultsHandler, Db::Flags flags) +{ + UNUSED(query); + UNUSED(args); + UNUSED(resultsHandler); + UNUSED(flags); +} + +void InvalidDb::asyncExec(const QString& query, const QHash<QString, QVariant>& args, Db::QueryResultsHandler resultsHandler, Db::Flags flags) +{ + UNUSED(query); + UNUSED(args); + UNUSED(resultsHandler); + UNUSED(flags); +} + +void InvalidDb::asyncExec(const QString& query, Db::QueryResultsHandler resultsHandler, Db::Flags flags) +{ + UNUSED(query); + UNUSED(resultsHandler); + UNUSED(flags); +} + +quint32 InvalidDb::asyncExec(const QString& query, const QList<QVariant>& args, Db::Flags flags) +{ + UNUSED(query); + UNUSED(args); + UNUSED(flags); + return 0; +} + +quint32 InvalidDb::asyncExec(const QString& query, const QHash<QString, QVariant>& args, Db::Flags flags) +{ + UNUSED(query); + UNUSED(args); + UNUSED(flags); + return 0; +} + +quint32 InvalidDb::asyncExec(const QString& query, Db::Flags flags) +{ + UNUSED(query); + UNUSED(flags); + return 0; +} + +SqlQueryPtr InvalidDb::prepare(const QString& query) +{ + UNUSED(query); + return SqlQueryPtr(); +} + +bool InvalidDb::begin() +{ + return false; +} + +bool InvalidDb::commit() +{ + return false; +} + +bool InvalidDb::rollback() +{ + return false; +} + +void InvalidDb::asyncInterrupt() +{ +} + +bool InvalidDb::isReadable() +{ + return false; +} + +bool InvalidDb::isWritable() +{ + return false; +} + +QString InvalidDb::attach(Db* otherDb, bool silent) +{ + UNUSED(otherDb); + UNUSED(silent); + return QString::null; +} + +AttachGuard InvalidDb::guardedAttach(Db* otherDb, bool silent) +{ + UNUSED(silent); + return AttachGuard::create(this, otherDb, QString::null); +} + +void InvalidDb::detach(Db* otherDb) +{ + UNUSED(otherDb); +} + +void InvalidDb::detachAll() +{ +} + +const QHash<Db*, QString>& InvalidDb::getAttachedDatabases() +{ + return attachedDbs; +} + +QSet<QString> InvalidDb::getAllAttaches() +{ + return QSet<QString>(); +} + +QString InvalidDb::getUniqueNewObjectName(const QString& attachedDbName) +{ + UNUSED(attachedDbName); + return QString::null; +} + +QString InvalidDb::getErrorText() +{ + return QString::null; +} + +int InvalidDb::getErrorCode() +{ + return 0; +} + +QString InvalidDb::getTypeLabel() +{ + return QStringLiteral("INVALID"); +} + +bool InvalidDb::initAfterCreated() +{ + return false; +} + +bool InvalidDb::deregisterFunction(const QString& name, int argCount) +{ + UNUSED(name); + UNUSED(argCount); + return false; +} + +bool InvalidDb::registerScalarFunction(const QString& name, int argCount) +{ + UNUSED(name); + UNUSED(argCount); + return false; +} + +bool InvalidDb::registerAggregateFunction(const QString& name, int argCount) +{ + UNUSED(name); + UNUSED(argCount); + return false; +} + +bool InvalidDb::registerCollation(const QString& name) +{ + UNUSED(name); + return false; +} + +bool InvalidDb::deregisterCollation(const QString& name) +{ + UNUSED(name); + return false; +} + +bool InvalidDb::open() +{ + return false; +} + +bool InvalidDb::close() +{ + return false; +} + +bool InvalidDb::openQuiet() +{ + return false; +} + +bool InvalidDb::openForProbing() +{ + return false; +} + +bool InvalidDb::closeQuiet() +{ + return false; +} + +void InvalidDb::registerAllFunctions() +{ +} + +void InvalidDb::registerAllCollations() +{ +} +QString InvalidDb::getError() const +{ + return error; +} + +void InvalidDb::setError(const QString& value) +{ + error = value; +} + + +void InvalidDb::interrupt() +{ +} + +bool InvalidDb::isValid() const +{ + return false; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/invaliddb.h b/SQLiteStudio3/coreSQLiteStudio/db/invaliddb.h new file mode 100644 index 0000000..759aa4c --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/invaliddb.h @@ -0,0 +1,81 @@ +#ifndef INVALIDDB_H +#define INVALIDDB_H + +#include "db/db.h" + +class API_EXPORT InvalidDb : public Db +{ + public: + InvalidDb(const QString& name, const QString& path, const QHash<QString, QVariant>& connOptions); + + bool isOpen(); + QString getName(); + QString getPath(); + quint8 getVersion(); + Dialect getDialect(); + QString getEncoding(); + QHash<QString, QVariant>& getConnectionOptions(); + void setName(const QString& value); + void setPath(const QString& value); + void setConnectionOptions(const QHash<QString, QVariant>& value); + void setTimeout(int secs); + int getTimeout() const; + SqlQueryPtr exec(const QString& query, const QList<QVariant>& args, Flags flags); + SqlQueryPtr exec(const QString& query, const QHash<QString, QVariant>& args, Flags flags); + SqlQueryPtr exec(const QString& query, Db::Flags flags); + SqlQueryPtr exec(const QString& query, const QVariant& arg); + SqlQueryPtr exec(const QString& query, std::initializer_list<QVariant> argList); + SqlQueryPtr exec(const QString& query, std::initializer_list<std::pair<QString, QVariant> > argMap); + void asyncExec(const QString& query, const QList<QVariant>& args, QueryResultsHandler resultsHandler, Flags flags); + void asyncExec(const QString& query, const QHash<QString, QVariant>& args, QueryResultsHandler resultsHandler, Flags flags); + void asyncExec(const QString& query, QueryResultsHandler resultsHandler, Flags flags); + quint32 asyncExec(const QString& query, const QList<QVariant>& args, Flags flags); + quint32 asyncExec(const QString& query, const QHash<QString, QVariant>& args, Flags flags); + quint32 asyncExec(const QString& query, Flags flags); + SqlQueryPtr prepare(const QString& query); + bool begin(); + bool commit(); + bool rollback(); + void asyncInterrupt(); + bool isReadable(); + bool isWritable(); + QString attach(Db* otherDb, bool silent); + AttachGuard guardedAttach(Db* otherDb, bool silent); + void detach(Db* otherDb); + void detachAll(); + const QHash<Db*, QString>& getAttachedDatabases(); + QSet<QString> getAllAttaches(); + QString getUniqueNewObjectName(const QString& attachedDbName); + QString getErrorText(); + int getErrorCode(); + QString getTypeLabel(); + bool initAfterCreated(); + bool deregisterFunction(const QString& name, int argCount); + bool registerScalarFunction(const QString& name, int argCount); + bool registerAggregateFunction(const QString& name, int argCount); + bool registerCollation(const QString& name); + bool deregisterCollation(const QString& name); + void interrupt(); + bool isValid() const; + QString getError() const; + void setError(const QString& value); + + public slots: + bool open(); + bool close(); + bool openQuiet(); + bool openForProbing(); + bool closeQuiet(); + void registerAllFunctions(); + void registerAllCollations(); + + private: + QString name; + QString path; + QHash<QString, QVariant> connOptions; + int timeout = 0; + QHash<Db*, QString> attachedDbs; + QString error; +}; + +#endif // INVALIDDB_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.cpp new file mode 100644 index 0000000..c840947 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.cpp @@ -0,0 +1,784 @@ +#include "queryexecutor.h" +#include "sqlerrorresults.h" +#include "sqlerrorcodes.h" +#include "services/dbmanager.h" +#include "db/sqlerrorcodes.h" +#include "services/notifymanager.h" +#include "queryexecutorsteps/queryexecutoraddrowids.h" +#include "queryexecutorsteps/queryexecutorcolumns.h" +#include "queryexecutorsteps/queryexecutorparsequery.h" +#include "queryexecutorsteps/queryexecutorattaches.h" +#include "queryexecutorsteps/queryexecutorcountresults.h" +#include "queryexecutorsteps/queryexecutorexecute.h" +#include "queryexecutorsteps/queryexecutorcellsize.h" +#include "queryexecutorsteps/queryexecutorlimit.h" +#include "queryexecutorsteps/queryexecutororder.h" +#include "queryexecutorsteps/queryexecutorwrapdistinctresults.h" +#include "queryexecutorsteps/queryexecutordatasources.h" +#include "queryexecutorsteps/queryexecutorexplainmode.h" +#include "queryexecutorsteps/queryexecutorreplaceviews.h" +#include "queryexecutorsteps/queryexecutordetectschemaalter.h" +#include "queryexecutorsteps/queryexecutorvaluesmode.h" +#include "common/unused.h" +#include <QMutexLocker> +#include <QDateTime> +#include <QThreadPool> +#include <QDebug> +#include <schemaresolver.h> +#include <parser/lexer.h> +#include <common/table.h> +#include <QtMath> + +// TODO modify all executor steps to use rebuildTokensFromContents() method, instead of replacing tokens manually. + +QueryExecutor::QueryExecutor(Db* db, const QString& query, QObject *parent) : + QObject(parent) +{ + context = new Context(); + originalQuery = query; + setDb(db); + setAutoDelete(false); + + connect(this, SIGNAL(executionFinished(SqlQueryPtr)), this, SLOT(cleanupAfterExecFinished(SqlQueryPtr))); + connect(this, SIGNAL(executionFailed(int,QString)), this, SLOT(cleanupAfterExecFailed(int,QString))); + connect(DBLIST, SIGNAL(dbAboutToBeUnloaded(Db*, DbPlugin*)), this, SLOT(cleanupBeforeDbDestroy(Db*, DbPlugin*))); +} + +QueryExecutor::~QueryExecutor() +{ + delete context; + context = nullptr; +} + +void QueryExecutor::setupExecutionChain() +{ + executionChain << new QueryExecutorParseQuery("initial") + << new QueryExecutorDetectSchemaAlter() + << new QueryExecutorExplainMode() + << new QueryExecutorValuesMode() + << new QueryExecutorAttaches() // needs to be at the begining, because columns needs to know real databases + << new QueryExecutorParseQuery("after Attaches") + << new QueryExecutorDataSources() + << new QueryExecutorReplaceViews() + << new QueryExecutorParseQuery("after ReplaceViews") + << new QueryExecutorAddRowIds() + << new QueryExecutorParseQuery("after AddRowIds") + << new QueryExecutorColumns() + << new QueryExecutorParseQuery("after Columns") + //<< new QueryExecutorColumnAliases() + << new QueryExecutorOrder() + << new QueryExecutorWrapDistinctResults() + << new QueryExecutorParseQuery("after WrapDistinctResults") + << new QueryExecutorCellSize() + << new QueryExecutorCountResults() + << new QueryExecutorParseQuery("after Order") + << new QueryExecutorLimit() + << new QueryExecutorParseQuery("after Limit") + << new QueryExecutorExecute(); + + foreach (QueryExecutorStep* step, executionChain) + step->init(this, context); +} + +void QueryExecutor::clearChain() +{ + foreach (QueryExecutorStep* step, executionChain) + delete step; + + executionChain.clear(); +} + +void QueryExecutor::executeChain() +{ + // Go through all remaining steps + bool result; + foreach (QueryExecutorStep* currentStep, executionChain) + { + if (interrupted) + { + stepFailed(currentStep); + return; + } + + result = currentStep->exec(); + if (!result) + { + stepFailed(currentStep); + return; + } + } + + // We're done. + clearChain(); + + executionMutex.lock(); + executionInProgress = false; + executionMutex.unlock(); + + emit executionFinished(context->executionResults); +} + +void QueryExecutor::stepFailed(QueryExecutorStep* currentStep) +{ + qDebug() << "Smart execution failed at step" << currentStep->metaObject()->className() << currentStep->objectName() + << "\nUsing simple execution method."; + + clearChain(); + + if (interrupted) + { + executionInProgress = false; + emit executionFailed(SqlErrorCode::INTERRUPTED, tr("Execution interrupted.")); + return; + } + + // Clear anything meaningful set up for smart execution - it's not valid anymore and misleads results for simple method + context->rowIdColumns.clear(); + + executeSimpleMethod(); +} + +void QueryExecutor::cleanupAfterExecFinished(SqlQueryPtr results) +{ + UNUSED(results); + cleanup(); +} + +void QueryExecutor::cleanupAfterExecFailed(int code, QString errorMessage) +{ + UNUSED(code); + UNUSED(errorMessage); + cleanup(); +} + +void QueryExecutor::cleanupBeforeDbDestroy(Db* dbToBeUnloaded, DbPlugin* plugin) +{ + UNUSED(plugin); + if (!dbToBeUnloaded || dbToBeUnloaded != db) + return; + + setDb(nullptr); + context->executionResults.clear(); +} + +void QueryExecutor::setQuery(const QString& query) +{ + originalQuery = query; +} + +void QueryExecutor::exec(Db::QueryResultsHandler resultsHandler) +{ + if (!db) + { + qWarning() << "Database is not set in QueryExecutor::exec()."; + return; + } + + if (!db->isOpen()) + { + error(SqlErrorCode::DB_NOT_OPEN, tr("Database is not open.")); + return; + } + + // Get exclusive flow for execution on this query executor + executionMutex.lock(); + if (executionInProgress) + { + error(SqlErrorCode::QUERY_EXECUTOR_ERROR, tr("Only one query can be executed simultaneously.")); + executionMutex.unlock(); + return; + } + executionInProgress = true; + executionMutex.unlock(); + + this->resultsHandler = resultsHandler; + + if (asyncMode) + QThreadPool::globalInstance()->start(this); + else + run(); +} + +void QueryExecutor::run() +{ + execInternal(); +} + +void QueryExecutor::execInternal() +{ + simpleExecution = false; + interrupted = false; + + if (resultsCountingAsyncId != 0) + { + resultsCountingAsyncId = 0; + db->interrupt(); + } + + // Reset context + delete context; + context = new Context(); + context->processedQuery = originalQuery; + context->explainMode = explainMode; + context->skipRowCounting = skipRowCounting; + context->noMetaColumns = noMetaColumns; + context->resultsHandler = resultsHandler; + context->preloadResults = preloadResults; + + // Start the execution + setupExecutionChain(); + executeChain(); +} + +void QueryExecutor::interrupt() +{ + if (!db) + { + qWarning() << "Called interrupt() on empty db in QueryExecutor."; + return; + } + + interrupted = true; + db->asyncInterrupt(); +} + +void QueryExecutor::countResults() +{ + if (context->skipRowCounting) + return; + + if (context->countingQuery.isEmpty()) // simple method doesn't provide that + return; + + if (asyncMode) + { + // Start asynchronous results counting query + resultsCountingAsyncId = db->asyncExec(context->countingQuery, context->queryParameters); + } + else + { + SqlQueryPtr results = db->exec(context->countingQuery, context->queryParameters); + context->totalRowsReturned = results->getSingleCell().toLongLong(); + context->totalPages = (int)qCeil(((double)(context->totalRowsReturned)) / ((double)getResultsPerPage())); + + emit resultsCountingFinished(context->rowsAffected, context->totalRowsReturned, context->totalPages); + + if (results->isError()) + { + notifyError(tr("An error occured while executing the count(*) query, thus data paging will be disabled. Error details from the database: %1") + .arg(results->getErrorText())); + } + } +} + +qint64 QueryExecutor::getLastExecutionTime() const +{ + return context->executionTime; +} + +qint64 QueryExecutor::getRowsAffected() const +{ + return context->rowsAffected; +} + +qint64 QueryExecutor::getTotalRowsReturned() const +{ + return context->totalRowsReturned; +} + +SqliteQueryType QueryExecutor::getExecutedQueryType(int index) +{ + if (context->parsedQueries.size() == 0) + return SqliteQueryType::UNDEFINED; + + if (index < 0) + return context->parsedQueries.last()->queryType; + + if (index < context->parsedQueries.size()) + return context->parsedQueries[index]->queryType; + + return SqliteQueryType::UNDEFINED; +} + +QSet<QueryExecutor::SourceTablePtr> QueryExecutor::getSourceTables() const +{ + return context->sourceTables; +} + +int QueryExecutor::getTotalPages() const +{ + return context->totalPages; +} + +QList<QueryExecutor::ResultColumnPtr> QueryExecutor::getResultColumns() const +{ + return context->resultColumns; +} + +QList<QueryExecutor::ResultRowIdColumnPtr> QueryExecutor::getRowIdResultColumns() const +{ + return context->rowIdColumns; +} + +int QueryExecutor::getMetaColumnCount() const +{ + int count = 0; + for (ResultRowIdColumnPtr rowIdCol : context->rowIdColumns) + count += rowIdCol->queryExecutorAliasToColumn.size(); + + return count; +} + +QSet<QueryExecutor::EditionForbiddenReason> QueryExecutor::getEditionForbiddenGlobalReasons() const +{ + return context->editionForbiddenReasons; +} + +void QueryExecutor::setParam(const QString& name, const QVariant& value) +{ + context->queryParameters[name] = value; +} + +void QueryExecutor::arg(const QVariant& value) +{ + QVariant::Type type = value.type(); + switch (type) + { + case QVariant::Bool: + case QVariant::Int: + originalQuery = originalQuery.arg(value.toInt()); + break; + case QVariant::LongLong: + originalQuery = originalQuery.arg(value.toLongLong()); + break; + case QVariant::UInt: + originalQuery = originalQuery.arg(value.toUInt()); + break; + case QVariant::ULongLong: + originalQuery = originalQuery.arg(value.toULongLong()); + break; + case QVariant::Double: + originalQuery = originalQuery.arg(value.toDouble()); + break; + case QVariant::String: + { + if (value.canConvert(QVariant::LongLong)) + originalQuery = originalQuery.arg(value.toLongLong()); + else if (value.canConvert(QVariant::Double)) + originalQuery = originalQuery.arg(value.toDouble()); + else + originalQuery = originalQuery.arg("'"+value.toString().replace("'", "''")+"'"); + + break; + } + default: + return; + } +} + +void QueryExecutor::exec(const QString& query) +{ + setQuery(query); + exec(); +} + +void QueryExecutor::dbAsyncExecFinished(quint32 asyncId, SqlQueryPtr results) +{ + if (handleRowCountingResults(asyncId, results)) + return; + + if (!simpleExecution) + return; + + if (this->asyncId == 0) + return; + + if (this->asyncId != asyncId) + return; + + this->asyncId = 0; + + simpleExecutionFinished(results); +} + +void QueryExecutor::executeSimpleMethod() +{ + simpleExecution = true; + context->editionForbiddenReasons << EditionForbiddenReason::SMART_EXECUTION_FAILED; + simpleExecutionStartTime = QDateTime::currentMSecsSinceEpoch(); + asyncId = db->asyncExec(originalQuery, context->queryParameters, Db::Flag::PRELOAD); +} + +void QueryExecutor::simpleExecutionFinished(SqlQueryPtr results) +{ + if (results->isError()) + { + executionMutex.lock(); + executionInProgress = false; + executionMutex.unlock(); + error(results->getErrorCode(), results->getErrorText()); + return; + } + + if (simpleExecIsSelect()) + context->countingQuery = "SELECT count(*) AS cnt FROM ("+originalQuery+");"; + else + context->rowsCountingRequired = true; + + ResultColumnPtr resCol; + context->resultColumns.clear(); + foreach (const QString& colName, results->getColumnNames()) + { + resCol = ResultColumnPtr::create(); + resCol->displayName = colName; + context->resultColumns << resCol; + } + + context->executionTime = QDateTime::currentMSecsSinceEpoch() - simpleExecutionStartTime; + context->rowsAffected = results->rowsAffected(); + context->totalRowsReturned = 0; + + executionMutex.lock(); + executionInProgress = false; + executionMutex.unlock(); + if (context->resultsHandler) + { + context->resultsHandler(results); + context->resultsHandler = nullptr; + } + + notifyWarn(tr("SQLiteStudio was unable to extract metadata from the query. Results won't be editable.")); + + emit executionFinished(results); +} + +bool QueryExecutor::simpleExecIsSelect() +{ + TokenList tokens = Lexer::tokenize(originalQuery, db->getDialect()); + tokens.trim(); + + // First check if it's explicit "SELECT" or "VALUES" (the latter one added in SQLite 3.8.4). + TokenPtr token = tokens.first(); + QString upper = token->value.toUpper(); + if (token->type == Token::KEYWORD && (upper == "SELECT" || upper == "VALUES")) + return true; + + // Now it's only possible to be a SELECT if it starts with "WITH" statement. + if (token->type != Token::KEYWORD || upper != "WITH") + return false; + + // Go through all tokens and find which one appears first (exclude contents indise parenthesis, + // cause there will always be a SELECT for Common Table Expression). + int depth = 0; + foreach (token, tokens) + { + switch (token->type) + { + case Token::PAR_LEFT: + depth--; + break; + case Token::PAR_RIGHT: + depth++; + break; + case Token::KEYWORD: + { + if (depth > 0) + break; + + upper = token->value.toUpper(); + if (upper == "SELECT") + return true; + + if (upper == "UPDATE" || upper == "DELETE" || upper == "INSERT") + return false; + + break; + } + default: + break; + } + } + return false; +} + +void QueryExecutor::cleanup() +{ + Db* attDb = nullptr; + foreach (const QString& attDbName, context->dbNameToAttach.leftValues()) + { + attDb = DBLIST->getByName(attDbName, Qt::CaseInsensitive); + if (attDbName.isNull()) + { + qWarning() << "Could not find db by name for cleanup after execution in QueryExecutor. Searched for db named:" << attDbName; + continue; + } + db->detach(attDb); + } +} + +bool QueryExecutor::handleRowCountingResults(quint32 asyncId, SqlQueryPtr results) +{ + if (resultsCountingAsyncId == 0) + return false; + + if (resultsCountingAsyncId != asyncId) + return false; + + if (isExecutionInProgress()) // shouldn't be true, but just in case + return false; + + resultsCountingAsyncId = 0; + + context->totalRowsReturned = results->getSingleCell().toLongLong(); + context->totalPages = (int)qCeil(((double)(context->totalRowsReturned)) / ((double)getResultsPerPage())); + + emit resultsCountingFinished(context->rowsAffected, context->totalRowsReturned, context->totalPages); + + if (results->isError()) + { + notifyError(tr("An error occured while executing the count(*) query, thus data paging will be disabled. Error details from the database: %1") + .arg(results->getErrorText())); + } + + return true; +} +bool QueryExecutor::getNoMetaColumns() const +{ + return noMetaColumns; +} + +void QueryExecutor::setNoMetaColumns(bool value) +{ + noMetaColumns = value; +} + +SqlQueryPtr QueryExecutor::getResults() const +{ + return context->executionResults; +} + +bool QueryExecutor::wasSchemaModified() const +{ + return context->schemaModified; +} + +QList<DataType> QueryExecutor::resolveColumnTypes(Db* db, QList<QueryExecutor::ResultColumnPtr>& columns, bool noDbLocking) +{ + QSet<Table> tables; + for (ResultColumnPtr col : columns) + tables << Table(col->database, col->table); + + SchemaResolver resolver(db); + resolver.setNoDbLocking(noDbLocking); + + QHash<Table,SqliteCreateTablePtr> parsedTables; + SqliteCreateTablePtr createTable; + for (const Table& t : tables) + { + createTable = resolver.getParsedObject(t.getDatabase(), t.getTable(), SchemaResolver::TABLE).dynamicCast<SqliteCreateTable>(); + if (!createTable) + { + qWarning() << "Could not resolve columns of table" << t.getTable() << "while quering datatypes for queryexecutor columns."; + continue; + } + parsedTables[t] = createTable; + } + + QList<DataType> datatypeList; + Table t; + SqliteCreateTable::Column* parsedCol = nullptr; + for (ResultColumnPtr col : columns) + { + t = Table(col->database, col->table); + if (!parsedTables.contains(t)) + { + datatypeList << DataType(); + continue; + } + + parsedCol = parsedTables[t]->getColumn(col->column); + if (!parsedCol || !parsedCol->type) + { + datatypeList << DataType(); + continue; + } + + datatypeList << parsedCol->type->toDataType(); + } + return datatypeList; +} + +bool QueryExecutor::getAsyncMode() const +{ + return asyncMode; +} + +void QueryExecutor::setAsyncMode(bool value) +{ + asyncMode = value; +} + +void QueryExecutor::setPreloadResults(bool value) +{ + preloadResults = value; +} + +bool QueryExecutor::getExplainMode() const +{ + return explainMode; +} + +void QueryExecutor::setExplainMode(bool value) +{ + explainMode = value; +} + + +void QueryExecutor::error(int code, const QString& text) +{ + emit executionFailed(code, text); +} + +Db* QueryExecutor::getDb() const +{ + return db; +} + +void QueryExecutor::setDb(Db* value) +{ + if (db) + disconnect(db, SIGNAL(asyncExecFinished(quint32,SqlQueryPtr)), this, SLOT(dbAsyncExecFinished(quint32,SqlQueryPtr))); + + db = value; + + if (db) + connect(db, SIGNAL(asyncExecFinished(quint32,SqlQueryPtr)), this, SLOT(dbAsyncExecFinished(quint32,SqlQueryPtr))); +} + +bool QueryExecutor::getSkipRowCounting() const +{ + return skipRowCounting; +} + +void QueryExecutor::setSkipRowCounting(bool value) +{ + skipRowCounting = value; +} + +QString QueryExecutor::getOriginalQuery() const +{ + return originalQuery; +} + +int qHash(QueryExecutor::EditionForbiddenReason reason) +{ + return static_cast<int>(reason); +} + +int qHash(QueryExecutor::ColumnEditionForbiddenReason reason) +{ + return static_cast<int>(reason); +} + +int QueryExecutor::getDataLengthLimit() const +{ + return dataLengthLimit; +} + +void QueryExecutor::setDataLengthLimit(int value) +{ + dataLengthLimit = value; +} + +bool QueryExecutor::isRowCountingRequired() const +{ + return context->rowsCountingRequired; +} + +QString QueryExecutor::getCountingQuery() const +{ + return context->countingQuery; +} + +int QueryExecutor::getResultsPerPage() const +{ + return resultsPerPage; +} + +void QueryExecutor::setResultsPerPage(int value) +{ + resultsPerPage = value; +} + +int QueryExecutor::getPage() const +{ + return page; +} + +void QueryExecutor::setPage(int value) +{ + page = value; +} + +bool QueryExecutor::isExecutionInProgress() +{ + QMutexLocker executionLock(&executionMutex); + return executionInProgress; +} + +QueryExecutor::Sort::Sort() +{ +} + +QueryExecutor::Sort::Sort(QueryExecutor::Sort::Order order, int column) + : order(order), column(column) +{ +} + +QueryExecutor::Sort::Sort(Qt::SortOrder order, int column) + : column(column) +{ + switch (order) + { + case Qt::AscendingOrder: + this->order = ASC; + break; + case Qt::DescendingOrder: + this->order = DESC; + break; + default: + this->order = NONE; + qWarning() << "Invalid sort order passed to QueryExecutor::setSortOrder():" << order; + break; + } +} + +Qt::SortOrder QueryExecutor::Sort::getQtOrder() const +{ + // The column should be checked first for being > -1. + if (order == QueryExecutor::Sort::DESC) + return Qt::DescendingOrder; + + return Qt::AscendingOrder; +} + +QueryExecutor::SortList QueryExecutor::getSortOrder() const +{ + return sortOrder; +} + +void QueryExecutor::setSortOrder(const SortList& value) +{ + sortOrder = value; +} + +int operator==(const QueryExecutor::SourceTable& t1, const QueryExecutor::SourceTable& t2) +{ + return t1.database == t2.database && t1.table == t2.table && t1.alias == t2.alias; +} + +int qHash(QueryExecutor::SourceTable sourceTable) +{ + return qHash(sourceTable.database + "." + sourceTable.table + "/" + sourceTable.alias); +} + diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.h new file mode 100644 index 0000000..c4a3e4d --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutor.h @@ -0,0 +1,1369 @@ +#ifndef QUERYEXECUTOR_H +#define QUERYEXECUTOR_H + +#include "db/db.h" +#include "parser/token.h" +#include "selectresolver.h" +#include "coreSQLiteStudio_global.h" +#include "common/bistrhash.h" +#include "datatype.h" +#include <QObject> +#include <QHash> +#include <QMutex> +#include <QRunnable> + +/** @file */ + +class Parser; +class SqliteQuery; +class QueryExecutorStep; +class DbPlugin; + +/** + * @brief Advanced SQL query execution handler. + * + * QueryExecutor is an advanced SQL query execution handler, which lets you execute any query (with subqueries, joins, etc) + * and is capable of providing meta information about returned data, such as ROWID for all rows and columns, + * data sources (database, table and column) for every column, rows affected, total rows number for query, etc. + * All of this available for both SQLite versions: 2 and 3. + * + * Queries are executed asynchronously. To handle result a lambda function can be used (or any function pointer), + * or manual tracking of asynchronous execution ID and signals from this class. Function pointers and lambdas + * are recommended way to handle results. + * + * It also allows you to: + * <ul> + * <li>programatically define sorting on desired column.</li> + * <li>define result rows paging (page size and queried page)</li> + * <li>refer other databases by their symbolic name and they will be attached and detached on the fly</li> + * <li>define maximum cell data size (in bytes), so you won't read too much data at once</li> + * </ul> + * + * Total number of result rows is counted by a separate call to the database (using <tt>SELECT count(*) ...</tt>) + * and its result is provided later, which is signalized by signal resultsCountingFinished(). Row counting can + * be disabled with setSkipRowCounting(). See "Counting query" section below for details. + * + * The simplest use case would be: + * @code + * Db* db = getDb(); + * QueryExecutor *executor = new QueryExecutor(db, "SELECT * FROM table"); + * executor->exec([=](SqlQueryPtr results) + * { + * if (results->isError()) + * { + * qCritical() << "Error " << results->getErrorCode() << ": " << results->getErrorText() << "\n"; + * return; + * } + * qDebug() << results->valueList(); + * } + * @endcode + * + * Unless you want some of QueryExecutor's special features, it's recommended to use + * Db::exec() and Db::asyncExec(), because while QueryExecutor is powerful, it also does lots of thing underneeth + * you may not need at all. + * + * \note This class is used in SQL editor window (SqlQueryModel) to execute queries entered by the user. + * + * \section smart_simple_sec "smart mode" vs "simple mode" + * + * Documentation of this class references many times to "smart mode" and "simple mode" expressions. + * The "smart mode" means that the QueryExecutor was able to parse the input query, modify it for its needs + * (add some meta-information columns, etc) and executed modified query successfully. + * When the "smart mode" fails (which should be rare), the "simple mode" steps in as a fallback strategy. + * The "simple mode" doesn't modify input query, just directly executes in on the database + * and then QueryExecutor tries to extract as much meta-information from "simple mode" as it can (which is not much). + * + * The "simple mode" also doesn't apply any paging (see QueryExecutor::setPage()), nor data size limits + * (see QueryExecutor::setDataLengthLimit()). + * + * The meta-information is all the data from the query that is not the essential data requested in the input query. + * That is full description on all requested columns (their source tables, databases, data types), + * ROWID value for all returned data rows, and more... + * + * \section counting_query Counting query + * + * QueryExecutor can split results into pages. In such cases, results are not all read, instead they are limited + * at SQL level with LIMIT and OFFSET keywords. Because of that it is impossible to tell how many rows + * would actualy be returned if there were no limit keywords. + * + * To deal with it the QueryExecutor makes extra query execution, which happens asynchronously to the main query + * execution. This extra execution starts just after the main query execution has finished (with success). + * This extra query (aka "Counting query") is made of original query wrapped with: + * @code + * SELECT count(*) FROM (original_query) + * @endcode + * This way QueryExecutor know the true number of rows to be retuend by the query. + * + * Since this extra query execution takes some extra time, this is performed asynchronously and only after + * successful execution of the main query. If you need to work with QueryExecutor::getTotalRowsReturned(), + * wait for the QueryExecutor::resultsCountingFinished() signal first. + * + * Row counting query execution can be disabled with QueryExecutor::setSkipRowCounting(), + */ +class API_EXPORT QueryExecutor : public QObject, public QRunnable +{ + Q_OBJECT + + public: + /** + * @brief General reasons for which results data cannot be edited. + */ + enum class EditionForbiddenReason + { + NOT_A_SELECT, /**< Executed query was not a SELECT. Only SELECT results can be edited. */ + SMART_EXECUTION_FAILED /**< + * QueryExecutor could not perform "smart" execution, + * which means that it was unable to gather meta information + * about returned data and therefore it cannot tell what are ROWIDs + * or data sources for each column. Still it was able to perform + * simple (direct, without query modifications) execution + * and it returned results, so they can be presented to the user, + * but not edited. + * + * This happens usually when there's a but in SQLiteStudio, + * which caused - for example - error during query parsing by Parser, + * or other query syntax issues, that wasn't handled correctly + * by SQLiteStudio. + */ + }; + + /** + * @brief Per-column reasons for which the data in the column cannot be edited. + */ + enum class ColumnEditionForbiddenReason + { + COMPOUND_SELECT, /**< + * The data cell comes from compound SELECT (UNION, EXCEPT, INTERSECT), + * which makes it problematic to SQLiteStudio to find out to which table + * does the particular row belong to. + * + * It might be resolved in future SQLiteStudio versions and this enum value + * would disappear then. + */ + GROUPED_RESULTS, /**< + * The data cell comes from SELECT with aggregated results, therefore it's + * hard to hard what were ROWIDs of each row in the results. + * + * It might be resolved in future SQLiteStudio versions and this enum value + * would disappear then. + */ + DISTINCT_RESULTS, /**< + * The data cell comes from SELECT DISTINCT clause, therefore extracting + * ROWIDs from the results is impossible, becasuse querying ROWID would + * make every row unique, therefore DISTINCT would not remove any rows, + * even the rest of the data (which matters to the user) would not be + * unique and should have been removed by the DISTINCT keyword. + * + * Because of that, SQLiteStudio doesn't extract ROWIDs for DISTINCT + * queries, so the results are accurate, but in consequence, + * the data cannot be edited. + */ + EXPRESSION, /**< + * The data cell is a result of a formula, function or other expression, + * which is not a direct data source, therefore it's impossible to change + * it's value. + */ + SYSTEM_TABLE, /**< + * The data cell comes from system table (sqlite_*) and those tables cannot + * be edited. + */ + COMM_TAB_EXPR, /**< + * The data cell comes from system "WITH common-table-expression" SELECT + * statement and those tables cannot be edited for the same reasons as + * in COMPOUND_SELECT case. To learn about common table expression statement, + * see http://sqlite.org/lang_with.html + */ + }; + + /** + * @brief Sort order definition. + * + * QueryExecutor supports programmatic sort order definition. + * It supports smooth transition from/to Qt sorting direction enum + * and defines sorting column by its index (0-based). + */ + struct API_EXPORT Sort + { + /** + * @brief Sorting order. + */ + enum Order + { + ASC, /**< Ascending order */ + DESC, /**< Descending order */ + NONE /**< No sorting at all */ + }; + + /** + * @brief Default constructor with no sorting defined. + * + * Constructed object uses NONE as sorting order. + */ + Sort(); + + /** + * @brief Creates sort order with given order on given column. + * @param order Order to sort with. + * @param column 0-based column number. + */ + Sort(Order order, int column); + + /** + * @brief Creates sort order with given order on given column. + * @param order Qt typed sort order (Qt::AscendingOrder, Qt::DescendingOrder). + * @param column 0-based column number. + */ + Sort(Qt::SortOrder order, int column); + + /** + * @brief Gets Qt typed sort order. + * @return Sort order. + */ + Qt::SortOrder getQtOrder() const; + + /** + * @brief Sorting order. + */ + Order order = NONE; + + /** + * @brief 0-based column number to sort by. + */ + int column = -1; + }; + + typedef QList<Sort> SortList; + + /** + * @brief ResultColumn as represented by QueryExecutor. + * + * QueryExecutor has its own result column representation, because it provides more + * meta information on the column. + */ + struct API_EXPORT ResultColumn + { + /** + * @brief Database name that the result column comes from. + * + * It's an SQLite internal name of the database, which means it's either "main", or "temp", + * or symbolic name of registered database (as represented in the databases tree), + * or the name of any attached databases. + * + * Symbolic database name is provided when user used it in his query and SQLiteStudio attached + * it transparently. In that case the temporary name used for "ATTACH" statement would make no sense, + * because that database was detached automatically after the query execution finished. + * + * In case of databases attached manually by user, it's exactly the same string as used when executing + * "ATTACH" statement. + */ + QString database; + + /** + * @brief Table name that the result column comes from. + */ + QString table; + + /** + * @brief Table column name that the result column comes from. + */ + QString column; + + /** + * @brief Alias defined for the result column in the query. + */ + QString alias; + + /** + * @brief Table alias defined in the query. + * + * This is an alias defined in the query for the table that the result column comes from. + */ + QString tableAlias; + + /** + * @brief Name of the column as presented to user. + * + * This is the name of a column as SQLite would present it to the user. + * If the query requested just a column from table, it will be that column name. + * If the query resuested two columns with the same name, then the second column will get + * suffix ":1", next one would get suffix ":2", and so on. + * For expressions the display name is direct copy of the SQL code used to define the expression. + * + * If the alias was defined in query, than it's used for the display name instead of anything else. + */ + QString displayName; + + /** + * @brief QueryExecutor's internal alias for the column. + * + * This value has no sense outside of QueryExecutor. It's used by QueryExecutor to + * keep track of columns from subselects, etc. + */ + QString queryExecutorAlias; + + /** + * @brief Set of reasons for which column editing is denied. + * + * If the set is empty, it means that the column can be edited. + */ + QSet<ColumnEditionForbiddenReason> editionForbiddenReasons; + + /** + * @brief Flag indicating that the column is actually an expression. + * + * Column representing an expression is not just a column and it should not be ever wrapped with + * quoting wrapper ([], "", ``). Such a column is for example call to the SQL function. + * + * For regular columns this will be false. + */ + bool expression = false; + }; + + /** + * @brief Shared pointer to ResultColumn. + */ + typedef QSharedPointer<ResultColumn> ResultColumnPtr; + + /** + * @brief Combined row ID columns for tables in the query. + * + * Since version 3.8.2 SQLite introduced the "WITHOUT ROWID" clause. It allows tables to have no + * ROWID built-in. Such tables must have PRIMARY KEY defined, which does the job of the unique key + * for the table. + * + * This structure describes the unique key for the table, regardless if it's a regular ROWID, + * or if it's a PRIMARY KEY on a column, or if it's a multi-column PRIMARY KEY. + * + * You should always understand it as a set of PRIMARY KEY columns for given table. + * Referencing to that table using given columns will guarantee uniqueness of the row. + * + * In case of regular table (with no "WITHOUT ROWID" clause), there will be only one column + * defined in ResultRowIdColumn::columns and it will be named "ROWID". + */ + struct API_EXPORT ResultRowIdColumn + { + /** + * @brief Database name that the table with this row ID is in. + */ + QString database; + + /** + * @brief Table name that the row ID is for. + */ + QString table; + + /** + * @brief Table alias defined in the query. + * @see ResultColumn::tableAlias + */ + QString tableAlias; + + /** + * @brief Mapping from alias to real column. + * + * This is mapping from QueryExecutor's internal aliases for columns + * into primary key column names of the table that the result column comes from. + * + * If you want to get list of column names used for RowId, use values() on this member. + * If you want to get list of query executor aliases, use keys() on this member. + */ + QHash<QString,QString> queryExecutorAliasToColumn; + }; + + /** + * @brief Shared pointer to ResultRowIdColumn. + */ + typedef QSharedPointer<ResultRowIdColumn> ResultRowIdColumnPtr; + + /** + * @brief Table that was a data source for at least one column in the query. + */ + struct API_EXPORT SourceTable + { + /** + * @brief Table's database. + * + * Same rules apply as for ResultColumn::database. + */ + QString database; + + /** + * @brief Table name. + */ + QString table; + + /** + * @brief Table alias defined in query. + */ + QString alias; + }; + + /** + * @brief Shared pointer to SourceTable. + */ + typedef QSharedPointer<SourceTable> SourceTablePtr; + + /** + * @brief Query execution context. + * + * This class is used to share data across all executor steps. + * It also provides initial configuration for executor steps. + */ + struct Context + { + /** + * @brief Query string after last query step processing. + * + * Before any step was executed, this is the same as originalQuery. + * + * The processed query is the one that will be executed in the end, + * so any steps should apply their changes to this query. + * + * This string should be modified and updated from QueryExecutorStep implementations. + * + * You won't usually modify this string directly. Instead you will + * want to use one of 2 methods: + * <ul> + * <li>Modify tokens</li> - modify tokens of top level objects in parsedQueries + * and call QueryExecutorStep::updateQueries(). + * <li>Modify parsed objects</li> - modify logical structure and values of + * objects in parsedQueries, then call on those objects SqliteStatement::rebuildTokens() + * and finally call QueryExecutorStep::updateQueries. + * </ul> + * + * The parsedQueries are refreshed every time when QueryExecutor executes + * QueryExecutorParse step. + */ + QString processedQuery; + + /** + * @brief Number of milliseconds that query execution took. + * + * This is measured and set by QueryExecutorStepExecute step. + */ + qint64 executionTime = 0; + + /** + * @brief Number of rows affected by the query. + */ + qint64 rowsAffected = 0; + + /** + * @brief Total number of rows returned from query. + * + * It provides correct number for all queries, no matter if it's SELECT, PRAGMA, or other. + */ + qint64 totalRowsReturned = 0; + + /** + * @brief Total number of pages. + * + * If there's a lot of result rows, they are split to pages. + * There's always at least one page of results. + */ + int totalPages = 1; + + /** + * @brief Defines if row counting will be performed. + * + * In case of EXPLAIN or PRAGMA queries the number of result rows is not provided from + * SQLite (at least not from Qt's drivers for them). Instead we need to manually count + * number of rows. This is when this flag is set (it's done by QueryExecutor, + * no need to care about it). + */ + bool rowsCountingRequired = false; + + /** + * @brief Executing query in EXPLAIN mode. + * + * This is configuration parameter passed from QueryExecutor just before executing + * the query. It can be defined by QueryExecutor::setExplainMode(). + */ + bool explainMode = false; + + /** + * @brief Defines if row counting should be skipped. + * + * This is a configuration flag predefined by QueryExecutor just before executing starts. + * You can set it with QueryExecutor::setSkipRowCounting(). + * + * Row counting is done asynchronously, just after normal query execution is finished. + * It's done by executing yet another query, which is more or less an orginal query + * wrapped with "SELECT count(*) FROM (...)". + * + * Separate counting has to be done, because QueryExecutor adds LIMIT and OFFSET + * to SELECT queries for results paging. + * + * When counting is done, the resultsCountingFinished() signal is emitted. + */ + bool skipRowCounting = false; + + /** + * @brief Parameters for query execution. + * + * It's defined by setParam(). + */ + QHash<QString,QVariant> queryParameters; + + /** + * @brief Results handler function pointer. + * + * This serves the same purpose as in Db class. It's used for execution + * with results handled by provided function. See Db::QueryResultsHandler for details. + * + * It's defined by exec(). + */ + Db::QueryResultsHandler resultsHandler = nullptr; + + /** + * @brief List of queries parsed from input query string. + * + * List of parsed queries is updated each time the QueryExecutorParseQuery step + * is executed. When it's called is defined by QueryExecutor::executionChain. + */ + QList<SqliteQueryPtr> parsedQueries; + + /** + * @brief Results of executed query. + * + * This is results object defined by the final query execution. It means that the + * query executed passed all preprocessing steps and was executed in its final form. + * + * This member is defined by QueryExecutorExecute step. + */ + SqlQueryPtr executionResults; + + /** + * @brief Currently attached databases. + * + * This is a cross-context information about currently attached databases. + * As QueryExecutorAttaches step does attaching, other steps may need information + * about attached databases. It's a map of orginal_db_name_used to attached_name. + */ + BiStrHash dbNameToAttach; + + /** + * @brief Sequence used by executor steps to generate column names. + */ + int colNameSeq = 0; + + /** + * @brief List of reasons that editing results is forbidden for. + * + * Executor steps may decide that the results of query cannot be edited. + * In that case they add proper enum to this set. + */ + QSet<EditionForbiddenReason> editionForbiddenReasons; + + /** + * @brief Result columns that provide ROWID. + * + * QueryExecutorAddRowIds step adds those columns. There is one or more columns + * per data source table mentioned in the query. It depends on "WITHOUT ROWID" clause + * in CREATE TABLE of the source table. + */ + QList<ResultRowIdColumnPtr> rowIdColumns; + + /** + * @brief Result columns from the query. + * + * List of result columns, just like they would be returned from regular execution + * of the query. Column in this list are not just a names of those columns, + * they provide full meta information about every single column. + */ + QList<ResultColumnPtr> resultColumns; + + /** + * @brief Data source tables mentioned in the query. + * + * List of tables used as data source in the query. + */ + QSet<SourceTablePtr> sourceTables; + + /** + * @brief Query used for counting results. + * + * Filled with SQL to be used for results counting (even if counting is disabled). + * @see QueryExecutor::getCountingQuery() + */ + QString countingQuery; + + /** + * @brief Flag indicating results preloading. + * + * Causes flag Db::Flag::PRELOAD to be added to the query execution. + */ + bool preloadResults = false; + + /** + * @brief Tells if executed queries did modify database schema. + * + * This is defined by QueryExecutorDetectSchemaAlter step + * and can be accessed by QueryExecutor::wasSchemaModified(). + */ + bool schemaModified = false; + + /** + * @brief Forbids QueryExecutor to return meta columns. + * + * See QueryExecutor::noMetaColumns for details. + */ + bool noMetaColumns = false; + }; + + /** + * @brief Creates query executor, initializes internal context object. + * @param db Optional database. If not provided, it has to be defined later with setDb(). + * @param query Optional query to execute. If not provided, it has to be defined later with setQuery(). + * @param parent Parent QObject. + */ + QueryExecutor(Db* db = nullptr, const QString& query = QString::null, QObject *parent = 0); + + /** + * @brief Releases internal resources. + */ + ~QueryExecutor(); + + /** + * @brief Defined query to be executed. + * @param query SQL query string. + * + * The query string can actually be multiple queries separated with a semicolon, just like you would + * write multiple queries in the SQL Editor window. Query executor will handle that. + * + * The query can contain parameter placeholders (such as :param, \@param). To bind values to params + * use setParam(). + */ + void setQuery(const QString& query); + + /** + * @brief Executes the query. + * @param resultsHandler Optional handler function pointer, can be lambda function. See Db::QueryResultsHandler for details. + * + * While execution is asynchronous, the executor notifies about results by signals. + * In case of success emits executionFinished(), in case of error emits executionFailed(). + */ + void exec(Db::QueryResultsHandler resultsHandler = nullptr); + + /** + * @brief Interrupts current execution. + * + * Calls Db::asyncInterrupt() internally. + */ + void interrupt(); + + /** + * @brief Executes counting query. + * + * Executes (asynchronously) counting query for currently defined query. After execution is done, the resultsCountingFinished() + * signal is emitted. + * + * Counting query is made of original query wrapped with "SELECT count(*) FROM (original_query)". + * + * It is executed after the main query execution has finished. + */ + void countResults(); + + /** + * @brief Gets time of how long it took to execute query. + * @return Execution time in milliseconds. + * + * The execution time is number of milliseconds from begining of the query execution, till receiving of the results. + */ + qint64 getLastExecutionTime() const; + + /** + * @brief Gets number of rows affected by the query. + * @return Affected rows number. + * + * Rows affected are defined by DbPlugin implementation and are usually a number of rows modified by UPDATE statement, + * or deleted by DELETE statement, or inserted by INSERT statement. + */ + qint64 getRowsAffected() const; + + /** + * @brief Gets number of rows returned by the query. + * @return Number of rows. + * + * If QueryExecutor limits result rows number (if defined by setResultsPerPage()), the actual number of rows + * to be returned from query can be larger. This methods returns this true number of rows, + * that would be returned from the query. + * + * Calling this method makes sense only after resultsCountingFinished() was emitted, otherwise the value + * returned will not be accurate. + */ + qint64 getTotalRowsReturned() const; + + /** + * @brief Gets type of the SQL statement in the defined query. + * @param index Index of the SQL statement in the query (statements are separated by semicolon character), or -1 to get the last one. + * @return Type of the query. If there were no parsed queries in the context, or if passed index is out of range, + * then SqliteQueryType::UNDEFINED is returned. + */ + SqliteQueryType getExecutedQueryType(int index = -1); + + /** + * @brief Provides set of data source tables used in query. + * @return Set of tables. + */ + QSet<QueryExecutor::SourceTablePtr> getSourceTables() const; + + /** + * @brief Gets number of pages available. + * @return Number of pages. + * + * Since QueryExecutor organizes results of the query into pages, this method gives number of pages that is necessary + * to display all the data. In other words: "results of this method" - 1 = "last page index". + * + * Single page size is defined by setResultsPerPage(). + */ + int getTotalPages() const; + + /** + * @brief Gets ordered list of result columns. + * @return Result columns. + * + * See Context::resultColumns for details. + */ + QList<QueryExecutor::ResultColumnPtr> getResultColumns() const; + + /** + * @brief Gets list of ROWID columns. + * @return ROWID columns. + * + * Note, that this returns list of ROWID columns as entities. This means that for ROWID a single ROWID column + * can be actually couple of columns in the results. To count the ROWID columns offset for extracting + * data columns use getMetaColumnCount(). + * + * See Context::rowIdColumns for details. + */ + QList<QueryExecutor::ResultRowIdColumnPtr> getRowIdResultColumns() const; + + /** + * @brief Gives number of meta columns in the executed query. + * @return Number of the actual meta columns (such as ROWID columns) added to the executed query. + * + * This method should be used to find out the number of meta columns that were added to the begining + * of the result columns in the executed query. This way you can learn which column index use as a start + * for reading the actual data from the query. + * + * Meta columns are used by QueryExecutor to find more information about the query being executed + * (like ROWID of each row for example). + */ + int getMetaColumnCount() const; + + /** + * @brief Gets reasons for which editing results is forbidden. + * @return Set of reasons. + * + * See Context::editionForbiddenReasons for details. + */ + QSet<EditionForbiddenReason> getEditionForbiddenGlobalReasons() const; + + /** + * @brief Defines named bind parameter for the query. + * @param name Name of the parameter (without the : or @ prefix). + * @param value Value of the parameter. + * + * Positional (index oriented) parameters are not supported by the QueryExecutor. + * Always use named parameters with QueryExecutor. + */ + void setParam(const QString& name, const QVariant& value); + + /** + * @brief Replaces placeholder in the query. + * @param value Value to replace placeholder with. + * + * This works almost the same as QString::arg(), but it's specialized + * for SQL domain. It means that it will work only with numeric + * or string values passed in the parameter. If the value is numeric, + * then it just replaces a placeholder. If the value is a string, + * then it's wrapped with a quote character ('), if necessary, then + * it replaces a placeholder. + * + * Placeholders are the same as for QString::arg(): %1, %2, %3... + */ + void arg(const QVariant& value); + + /** + * @brief Gets currently defined database. + * @return Database object, or null pointer if not yet defined. + */ + Db* getDb() const; + + /** + * @brief Defines new database for query execution. + * @param value Database object. It should be open before calling exec(). + */ + void setDb(Db* value); + + /** + * @brief Gets original, not modified query. + * @return SQL query string. + */ + QString getOriginalQuery() const; + + /** + * @brief Gets data size limit. + * @return Number of bytes, or UTF-8/UTF-16 characters. + * + * See setDataLengthLimit() for details. + */ + int getDataLengthLimit() const; + + /** + * @brief Defines data size limit for results. + * @param value Number of bytes, or UTF-8/UTF-16 characters. + * + * Limit is not defined by default and in that case it's not applied + * to the query. To enable limit, set it to any positive number. + * To disable limit, set it to any negative number. + * + * When QueryExecutor prepares query for execution, it applies SUBSTR() + * to all result columns, so if the database has a huge value in some column, + * SQLiteStudio won't load 1000 rows with huge values - that would kill performance + * of the application. Instead it loads small chunk of every value. + * + * SqlQueryModel loads limited chunks of data and loads on-the-fly full cell values + * when user requests it (edits the cell, or views it in form editor). + * + * Parameter defined by this method is passed to SQLite's SUBSTR() function. + * As SQLite's documentation stand, numbers passed to that function are treated + * as number of bytes for non-textual data and for textual data they are number + * of characters (for UTF-8 and UTF-16 they can be made of more than 1 byte). + */ + void setDataLengthLimit(int value); + + // TODO manual row counting -> should be done by query executor already and returned in total rows + /** + * @brief Tests if manual row counting is required. + * @return True if manual counting is required. + * + * In case of some queries the getTotalRowsReturned() won't provide proper value. + * Then you will need to count result rows from the results object. + * + * It's okay, because this applies only for EXPLAIN and PRAGMA queries, + * which will never return any huge row counts. + */ + bool isRowCountingRequired() const; + + /** + * @brief Gets SQL query used for counting results. + * @return SQL query. + * + * This is the query used by countResults(). + */ + QString getCountingQuery() const; + + /** + * @brief Gets number of rows per page. + * @return Number of rows. + * + * By default results are not split to pages and this method will return -1. + */ + int getResultsPerPage() const; + + /** + * @brief Defines number of rows per page. + * @param value Number of rows. + * + * By default results are not split to pages. + * See setPage() for details on enabling and disabling paging. + */ + void setResultsPerPage(int value); + + /** + * @brief Gets current results page. + * @return Page index. + * + * Results page is 0-based index. It's always value between 0 and (getTotalPages() - 1). + * If results paging is disabled (see setResultsPerPage()), then this method + * will always return 0, as this is the first page (and in that case - the only one). + */ + int getPage() const; + + /** + * @brief Defines results page for next execution. + * @param value 0-based page index. + * + * If page value is negative, then paging is disabled. + * Any positive value or 0 enables paging and sets requested page of results to given page. + * + * If requested page value is greater than "getTotalPages() - 1", then no results will be returned. + * It's an invalid page value. + * If requested page value is lower then 0, then paging is disabled. + * + * Once the page is defined, the exec() must be called to get results + * from new defined page. + */ + void setPage(int value); + + /** + * @brief Tests if there's any execution in progress at the moment. + * @return true if the execution is in progress, or false otherwise. + */ + bool isExecutionInProgress(); + + /** + * @brief Gets sorting defined for executor. + * @return Sorting definition. + * + * See Sort for details. + */ + QueryExecutor::SortList getSortOrder() const; + + /** + * @brief Defines sorting for next query execution. + * @param value Sorting definition. + * + * Once the sorting definition is changed, the exec() must be called + * to receive results in new order. + */ + void setSortOrder(const QueryExecutor::SortList& value); + + /** + * @brief Tests if row counting is disabled. + * @return true if row counting will be skipped, or false otherwise. + * + * See Context::skipRowCounting for details. + */ + bool getSkipRowCounting() const; + + /** + * @brief Defines if executor should skip row counting. + * @param value New value for this parameter. + * + * See Context::skipRowCounting for details. + */ + void setSkipRowCounting(bool value); + + /** + * @brief Asynchronous executor processing in thread. + * + * This is an implementation of QRunnable::run(), so the QueryExecutor + * does it's own asynchronous work on object members. + */ + void run(); + + /** + * @brief Tests if query execution should be performed in EXPLAIN mode. + * @return true if the mode is enabled, or false otherwise. + */ + bool getExplainMode() const; + + /** + * @brief Defines EXPLAIN mode for next query execution. + * @param value true to enable EXPLAIN mode, or false to disable it. + * + * EXPLAIN mode means simply that the EXPLAIN keyword will be prepended + * to the query, except when the query already started with the EXPLAIN keyword. + * + * Once the mode is changed, the exec() must be called + * to receive "explain" results. + */ + void setExplainMode(bool value); + + /** + * @brief Defines results preloading. + * @param value true to preload results. + * + * Results preloading is disabled by default. See Db::Flag::PRELOAD for details. + */ + void setPreloadResults(bool value); + + bool getAsyncMode() const; + void setAsyncMode(bool value); + + SqlQueryPtr getResults() const; + bool wasSchemaModified() const; + + static QList<DataType> resolveColumnTypes(Db* db, QList<ResultColumnPtr>& columns, bool noDbLocking = false); + + bool getNoMetaColumns() const; + void setNoMetaColumns(bool value); + + private: + /** + * @brief Executes query. + * + * It's called from run(). This is the execution of query but called from different + * thread than exec() was called from. + */ + void execInternal(); + + /** + * @brief Raises execution error. + * @param code Error code. Can be either from SQLite error codes, or from SqlErrorCode. + * @param text Error message. + * + * This method is called when some of executor's preconditions has failed, or when SQLite + * execution raised an error. + */ + void error(int code, const QString& text); + + /** + * @brief Build chain of executor steps. + * + * Defines executionChain by adding new QueryExecutorStep descendants. + * Each step has its own purpose described in its class documentation. + * See inheritance hierarchy of QueryExecutorStep. + */ + void setupExecutionChain(); + + /** + * @brief Deletes executor step objects. + * + * Deletes all QueryExecutorStep objects from executionChain clears the list. + */ + void clearChain(); + + /** + * @brief Executes all steps from executor chain. + * + * The steps chain is defined by setupExecutionChain(). + * On execution error, the stepFailed() is called and the method returns. + */ + void executeChain(); + + /** + * @brief Executes the original, unmodified query. + * + * When smart execution (using steps chain) failed, then this method + * is a fallback. It executes original query passed to the executor. + * Given, that query was not modified, it cannot provide meta information, + * therefore results of that execution won't editable. + */ + void executeSimpleMethod(); + + /** + * @brief Handles results of simple execution. + * @param results Results object returned from Db. + * + * Checks results for errors and extracts basic meta information, + * such as rows affected, total result rows and time of execution. + * + * In case of success emits executionFinished(), in case of error emits executionFailed(). + */ + void simpleExecutionFinished(SqlQueryPtr results); + + /** + * @brief Tests whether the original query is a SELECT statement. + * @return true if the query is SELECT, or false otherwise. + * + * This method assumes that there was a problem with parsing the query with the Parser + * (and that's why we're using simple execution method) and so it tries to figure out + * a query type using other algorithms. + */ + bool simpleExecIsSelect(); + + /** + * @brief Releases resources acquired during query execution. + * + * Currently it just detaches databases attached for query execution needs (transparent + * database attaching feature). + */ + void cleanup(); + + /** + * @brief Extracts counting query results. + * @param asyncId Asynchronous ID of the counting query execution. + * @param results Results from the counting query execution. + * @return true if passed asyncId is the one for currently running counting query, or false otherwise. + * + * It's called from database asynchronous execution thread. The database might have executed + * some other acynchronous queries too, so this method checks if the asyncId is the expected one. + * + * Basicly this method is called a result of countResults() call. Extracts counted number of rows + * and stores it in query executor's context. + */ + bool handleRowCountingResults(quint32 asyncId, SqlQueryPtr results); + + /** + * @brief Query executor context object. + * + * Context object is shared across all execution steps. It's (re)initialized for every + * call to exec(). Initialization involves copying configuration parameters (such as sortOrder, + * explainMode, etc) from local members to the context. + * + * During steps execution the context is used to share information between steps. + * For example if one step modifies query in anyway, it should store updated query + * in Context::processedQuery. See QueryExecutorStep for details on possible methods + * for updating Context::processedQuery (you don't always have to build the whole processed + * query string by yourself). + * + * Finally, the context serves as a results container from all steps. QueryExecutor reads + * result columns metadata, total rows number, affected rows and other information from the context. + */ + Context* context = nullptr; + + /** + * @brief Database that all queries will be executed on. + * + * It can be passed in constructor or defined later with setDb(), but it cannot be null + * when calling exec(). The exec() will simply return with no execution performed + * and will log a warning. + */ + Db* db = nullptr; + + /** + * @brief Synchronization mutex for "execution in progress" state of executor. + * + * 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; + + /** + * @brief Query to execute as defined by the user. + * + * This is a copy of original query provided by user to the executor. + */ + QString originalQuery; + + /** + * @brief Predefined number of results per page. + * + * See setResultsPerPage() for details. + */ + int resultsPerPage = -1; + + /** + * @brief Predefined results page index. + * + * See setPage() for details. + */ + int page = -1; + + /** + * @brief Predefined sorting order. + * + * There's no sorting predefined by default. If you want it, you have to apply it with setSortOrder(). + */ + SortList sortOrder; + + /** + * @brief Flag indicating that the execution is currently in progress. + * + * This variable is synchronized across threads and therefore you can always ask QueryExecutor + * if it's currently busy (with isExecutionInProgress()). + */ + bool executionInProgress = false; + + /** + * @brief Flag indicating that the most recent execution was made in "simple mode". + * + * This flag is set by executeSimpleMethod() method. See its documentation for details. + * The exec() resets this flag to false each time, but each time the smart execution fails, + * the executeSimpleMethod() is called and the flag is set to true. + */ + bool simpleExecution = false; + + /** + * @brief Flag indicating that the most recent execution was interrupted. + * + * This flag is set only if execution was interrupted by call to interrupt() on this class. + * If the execution was interrupted by another thread (which called sqlite_interrupt() + * or Db::interrupt()), then this flag is not set. + * + * This variable is tested at several stages of query execution in order to abort + * execution if the interruption was already requested. + */ + bool interrupted = false; + + /** + * @brief Flag indicating that the execution is performed in EXPLAIN mode. + * + * See setExplainMode() for details. + */ + bool explainMode = false; + + /** + * @brief Flag indicating that the row counting was disabled. + * + * See Context::skipRowCounting for details. + */ + bool skipRowCounting = false; + + /** + * @brief Defines results data size limit. + * + * See setDataLengthLimit() for details. + */ + int dataLengthLimit = -1; + + /** + * @brief Exact moment when query execution started. + * + * Expressed in number of milliseconds since 1970-01-01 00:00:00. + */ + qint64 simpleExecutionStartTime; + + /** + * @brief Asynchronous ID of query execution. + * + * Asynchronous ID returned from Db::asyncExec() for the query execution. + */ + quint32 asyncId = 0; + + /** + * @brief Asynchronous ID of counting query execution. + * + * Asynchronous ID returned from Db::asyncExec() for the counting query execution. + * See countResults() for details on counting query. + */ + quint32 resultsCountingAsyncId = 0; + + /** + * @brief Flag indicating results preloading. + * + * See Context::preloadResults. + */ + bool preloadResults = false; + + /** + * @brief Determinates if asynchronous mode is used. + * + * By default QueryExecutor runs in asynchronous mode (in another thread). + * You can set this to false to make exec() work synchronously, on calling thread. + */ + bool asyncMode = true; + + /** + * @brief Defines if the QueryExecutor will provide meta columns in the results. + * + * Set to true to forbid providing meta columns, or leave as false to let QueryExecutor + * provide meta columns. + * + * Meta columns are additional columns that are not part of the query that was passed to the executor. + * Those are for example ROWID columns (currently those are the only meta columns). + * + * You can always find out number of ROWID columns from getRowIdResultColumns(). + * + * Meta columns are placed always at the begining. + */ + bool noMetaColumns = false; + + /** + * @brief Chain of executor steps. + * + * Executor step list is set up by setupExecutionChain() and cleaned up after + * execution is finished. Steps are executed in order they appear in this list. + * + * Steps are executed one by one and if any of them raises the error, + * execution stops and error from QueryExecutor is raised (with executionFailed() signal). + */ + QList<QueryExecutorStep*> executionChain; + + /** + * @brief Execution results handler. + * + * This member keeps address of function that was defined for handling results. + * It is defined only if exec() method was called with the handler function argument. + * + * Results handler function is evaluated once the query execution has finished + * with success. It's not called on failure. + */ + Db::QueryResultsHandler resultsHandler = nullptr; + + signals: + /** + * @brief Emitted on successful query execution. + * @param results Results from query execution. + * + * It's emitted at the very end of the whole query execution process + * and only on successful execution. It doesn't matter if the execution was + * performed in "smart mode" or "simple mode". + */ + void executionFinished(SqlQueryPtr results); + + /** + * @brief Emitted on failed query execution. + * @param code Error code. + * @param errorMessage Error message. + * + * It doesn't matter if the execution was performed in "smart mode" or "simple mode". + */ + void executionFailed(int code, QString errorMessage); + + /** + * @brief Emitted on successful counting query execution. + * @param rowsAffected Rows affected by the original query. + * @param rowsReturned Rows returned by the original query. + * @param totalPages Number of pages needed to represent all rows given the value defined with setResultsPerPage(). + * + * This signal is emitted only when setSkipRowCounting() was set to false (it is by default) + * and the counting query execution was successful. + * + * The counting query actually counts only \p rowsReturned, while \p rowsAffected and \p totalPages + * are extracted from original query execution. + */ + void resultsCountingFinished(quint64 rowsAffected, quint64 rowsReturned, int totalPages); + + public slots: + /** + * @brief Executes given query. + * @param originalQuery to be executed. + * + * This is a shorthand for: + * @code + * queryExecutor->setQuery(query); + * queryExecutor->exec(); + * @endcode + * + * This exec() version is a SLOT, while the other exec() method is not. + */ + void exec(const QString& originalQuery); + + private slots: + /** + * @brief Handles asynchronous database execution results. + * @param asyncId Asynchronous ID of the execution. + * @param results Results from the execution. + * + * QueryExecutor checks whether the \p asyncId belongs to the counting query execution, + * or the simple execution. + * Dispatches query results to a proper handler method. + */ + void dbAsyncExecFinished(quint32 asyncId, SqlQueryPtr results); + + /** + * @brief Calledn when an executor step has failed with its job. + * + * An executor step reported an error. "Smart execution" failed and now the executor will try + * to execute query with a "simple method". + */ + void stepFailed(QueryExecutorStep *currentStep); + + /** + * @brief Cleanup routines after successful query execution. + * @param results Query results. + * + * Releases resources that are no longer used. Currently simply calls cleanup(). + */ + void cleanupAfterExecFinished(SqlQueryPtr results); + + /** + * @brief Cleanup routines after failed query execution. + * @param code Error code. + * @param errorMessage Error message. + * + * Releases resources that are no longer used. Currently simply calls cleanup(). + */ + void cleanupAfterExecFailed(int code, QString errorMessage); + + /** + * @brief Called when the currently set db is about to be destroyed. + * + * Deletes results from the Context if there were any, so they are not referencing anything + * from deleted Db. Keeping results is dangerous, becuase the Db driver (plugin) is most likely to + * be unloaded soon and we won't be able to call results destructor. + */ + void cleanupBeforeDbDestroy(Db* dbToBeUnloaded, DbPlugin* plugin); +}; + +int qHash(QueryExecutor::EditionForbiddenReason reason); +int qHash(QueryExecutor::ColumnEditionForbiddenReason reason); +int qHash(QueryExecutor::SourceTable sourceTable); +int operator==(const QueryExecutor::SourceTable& t1, const QueryExecutor::SourceTable& t2); + +#endif // QUERYEXECUTOR_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutoraddrowids.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutoraddrowids.cpp new file mode 100644 index 0000000..55203e4 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutoraddrowids.cpp @@ -0,0 +1,216 @@ +#include "queryexecutoraddrowids.h" +#include "parser/ast/sqliteselect.h" +#include "selectresolver.h" +#include "common/utils_sql.h" +#include "parser/ast/sqlitecreatetable.h" +#include "schemaresolver.h" +#include <QDebug> + +bool QueryExecutorAddRowIds::exec() +{ + if (context->noMetaColumns) + return true; + + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + return true; + + if (select->coreSelects.size() > 1) + return true; + + if (select->coreSelects.first()->distinctKw || select->coreSelects.first()->valuesMode) + return true; + + bool ok = true; + addRowIdForTables(select.data(), ok); + + if (!ok) + { + qCritical() << "Error in QueryExecutorAddRowIds step."; + return false; + } + + // ...and putting it into parsed query, then update processed query + select->rebuildTokens(); + updateQueries(); + + return true; +} + +QHash<SelectResolver::Table,QHash<QString,QString>> QueryExecutorAddRowIds::addRowIdForTables(SqliteSelect* select, bool& ok, bool isTopSelect) +{ + QHash<SelectResolver::Table,QHash<QString,QString>> rowIdColsMap; + if (select->coreSelects.size() > 1) + return rowIdColsMap; + + SqliteSelect::Core* core = select->coreSelects.first(); + + if (core->groupBy.size() > 0) + return rowIdColsMap; + + if (core->distinctKw) + return rowIdColsMap; + + // Go trough subselects to add ROWID result columns there and collect rowId mapping to use here. + foreach (SqliteSelect* subSelect, getSubSelects(core)) + { + rowIdColsMap.unite(addRowIdForTables(subSelect, ok, false)); + if (!ok) + return rowIdColsMap; + } + + // Getting all tables we need to get ROWID for + SelectResolver resolver(db, select->tokens.detokenize()); + resolver.resolveMultiCore = false; // multicore subselects result in not editable columns, skip them + + QSet<SelectResolver::Table> tables = resolver.resolveTables(core); + foreach (const SelectResolver::Table& table, tables) + { + if (table.flags & (SelectResolver::FROM_COMPOUND_SELECT | SelectResolver::FROM_DISTINCT_SELECT | SelectResolver::FROM_GROUPED_SELECT)) + continue; // we don't get ROWID from compound, distinct or aggregated subselects + + if (!addResultColumns(core, table, rowIdColsMap, isTopSelect)) + { + ok = false; + return rowIdColsMap; + } + } + return rowIdColsMap; +} + +QList<SqliteSelect*> QueryExecutorAddRowIds::getSubSelects(SqliteSelect::Core* core) +{ + QList<SqliteSelect*> selects; + if (!core->from) + return selects; + + if (core->from->singleSource && core->from->singleSource->select) + selects << core->from->singleSource->select; + + foreach (SqliteSelect::Core::JoinSourceOther* otherSource, core->from->otherSources) + { + if (!otherSource->singleSource->select) + continue; + + selects << otherSource->singleSource->select; + } + + return selects; +} + +QHash<QString,QString> QueryExecutorAddRowIds::getNextColNames(const SelectResolver::Table& table) +{ + QHash<QString,QString> colNames; + + SchemaResolver resolver(db); + SqliteQueryPtr query = resolver.getParsedObject(table.database, table.table, SchemaResolver::TABLE); + SqliteCreateTablePtr createTable = query.dynamicCast<SqliteCreateTable>(); + if (!createTable) + { + qCritical() << "No CREATE TABLE object after parsing and casting in QueryExecutorAddRowIds::getNextColNames(). Cannot provide ROWID columns."; + return colNames; + } + + if (createTable->withOutRowId.isNull()) + { + // It's a regular ROWID table + colNames[getNextColName()] = "ROWID"; + return colNames; + } + + SqliteStatement* primaryKey = createTable->getPrimaryKey(); + if (!primaryKey) + { + qCritical() << "WITHOUT ROWID table, but could not find // Co PRIMARY KEY in QueryExecutorAddRowIds::getNextColNames()."; + return colNames; + } + + SqliteCreateTable::Column::Constraint* columnConstr = dynamic_cast<SqliteCreateTable::Column::Constraint*>(primaryKey); + if (columnConstr) + { + colNames[getNextColName()] = dynamic_cast<SqliteCreateTable::Column*>(columnConstr->parentStatement())->name; + return colNames; + } + + SqliteCreateTable::Constraint* tableConstr = dynamic_cast<SqliteCreateTable::Constraint*>(primaryKey); + if (tableConstr) + { + foreach (SqliteIndexedColumn* idxCol, tableConstr->indexedColumns) + colNames[getNextColName()] = idxCol->name; + + return colNames; + } + + qCritical() << "PRIMARY KEY that is neither table or column constraint. Should never happen (QueryExecutorAddRowIds::getNextColNames())."; + return colNames; +} + +bool QueryExecutorAddRowIds::addResultColumns(SqliteSelect::Core* core, const SelectResolver::Table& table, + QHash<SelectResolver::Table,QHash<QString,QString>>& rowIdColsMap, bool isTopSelect) +{ + QHash<QString, QString> executorToRealColumns; + if (rowIdColsMap.contains(table)) + { + executorToRealColumns = rowIdColsMap[table]; // we already have resCol names from subselect + } + else + { + executorToRealColumns = getNextColNames(table); + rowIdColsMap[table] = executorToRealColumns; + } + + if (executorToRealColumns.size() == 0) + { + qCritical() << "No result column defined for a table in QueryExecutorAddRowIds::addResCols()."; + return false; + } + + QHashIterator<QString,QString> it(executorToRealColumns); + while (it.hasNext()) + { + it.next(); + if (!addResultColumns(core, table, it.key(), it.value())) + return false; + } + + if (isTopSelect) + { + // Query executor result column description + QueryExecutor::ResultRowIdColumnPtr queryExecutorResCol = QueryExecutor::ResultRowIdColumnPtr::create(); + queryExecutorResCol->database = table.database; + queryExecutorResCol->table = table.table; + queryExecutorResCol->tableAlias = table.alias; + queryExecutorResCol->queryExecutorAliasToColumn = executorToRealColumns; + context->rowIdColumns << queryExecutorResCol; + } + + return true; +} + +bool QueryExecutorAddRowIds::addResultColumns(SqliteSelect::Core* core, const SelectResolver::Table& table, const QString& queryExecutorColumn, + const QString& realColumn) +{ + SqliteSelect::Core::ResultColumn* resCol = new SqliteSelect::Core::ResultColumn(); + resCol->setParent(core); + + resCol->expr = new SqliteExpr(); + resCol->expr->setParent(resCol); + + resCol->expr->initId(realColumn); + if (!table.alias.isNull()) + { + resCol->expr->table = table.alias; + } + else + { + if (!table.database.isNull()) + resCol->expr->database = table.database; + + resCol->expr->table = table.table; + } + resCol->asKw = true; + resCol->alias = queryExecutorColumn; + + core->resultColumns.insert(0, resCol); + return true; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutoraddrowids.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutoraddrowids.h new file mode 100644 index 0000000..a5431fa --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutoraddrowids.h @@ -0,0 +1,81 @@ +#ifndef QUERYEXECUTORADDROWIDS_H +#define QUERYEXECUTORADDROWIDS_H + +#include "queryexecutorstep.h" +#include "parser/token.h" + +/** + * @brief Adds ROWID to result columns. + * + * This step adds ROWID to result column list for each table mentioned in result columns. + * For WITHOUT ROWID tables there might be several columns per table. + * + * It also provides list of added columns in QueryExecutor::Context::rowIdColumns. + */ +class QueryExecutorAddRowIds : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); + + private: + /** + * @brief Adds ROWID columns to the result columns and the context. + * @param core SELECT's core that keeps result columns. + * @param table Table we want to add ROWID columns for. + * @param rowIdColsMap Map of ROWID columns from inner selects (built with addRowIdForTables()). + * @param isTopSelect True only for top-most select to store rowid columns in context only for the final list of columns. + * @return true on success, false on any failure. + * + * Finds columns representing ROWID for the \p table and adds them to result columns and to the context. + */ + bool addResultColumns(SqliteSelect::Core* core, const SelectResolver::Table& table, + QHash<SelectResolver::Table, QHash<QString, QString> >& rowIdColsMap, bool isTopSelect); + + /** + * @brief Adds the column to result columns list. + * @param core SELECT's core that keeps the result columns. + * @param table Table that the column is for. + * @param queryExecutorColumn Alias name for the column that will be used by the query executor. + * @param realColumn Actual column name in the database. + * @return true on success, false on any failure. + * + * Adds given column to the result column list in the SELECT statement. + */ + bool addResultColumns(SqliteSelect::Core* core, const SelectResolver::Table& table, const QString& queryExecutorColumn, + const QString& realColumn); + + /** + * @brief Adds all necessary ROWID columns to result columns. + * @param select SELECT that keeps result columns. + * @param ok[out] Reference to a flag for telling if the method was executed successly (true), or not (false). + * @param isTopSelect True only for top-most select call of this method, so the list of rowid columns is stored + * only basing on this select (and rowid mappind for it), not all subqueries. This is to avoid redundant rowid columns in context + * in case of subselects. + * @return Mapping for every table mentioned in the SELECT with map of ROWID columns for the table. + * The column map is a query_executor_alias to real_database_column_name. + * + * Adds ROWID columns for all tables mentioned in result columns of the \p select. + */ + QHash<SelectResolver::Table,QHash<QString,QString>> addRowIdForTables(SqliteSelect* select, bool& ok, bool isTopSelect = true); + + /** + * @brief Extracts all subselects used in the SELECT. + * @param core SELECT's core to extract subselects from. + * @return List of subselects. + * + * Extracts only subselects of given select core, but not recurrently. + * As it works on the SELECT's core, it means that it's not applicable for compound selects. + */ + QList<SqliteSelect*> getSubSelects(SqliteSelect::Core* core); + + /** + * @brief Provides list of columns representing ROWID for the table. + * @param table Table to get ROWID columns for. + * @return Map of query executor alias to real database column name. + */ + QHash<QString, QString> getNextColNames(const SelectResolver::Table& table); +}; + +#endif // QUERYEXECUTORADDROWIDS_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorattaches.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorattaches.cpp new file mode 100644 index 0000000..0df0445 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorattaches.cpp @@ -0,0 +1,16 @@ +#include "queryexecutorattaches.h" +#include "dbattacher.h" +#include "sqlitestudio.h" +#include <QScopedPointer> + +bool QueryExecutorAttaches::exec() +{ + QScopedPointer<DbAttacher> attacher(SQLITESTUDIO->createDbAttacher(db)); + if (!attacher->attachDatabases(context->parsedQueries)) + return false; + + context->dbNameToAttach = attacher->getDbNameToAttach(); + updateQueries(); + + return true; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorattaches.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorattaches.h new file mode 100644 index 0000000..b6346fb --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorattaches.h @@ -0,0 +1,28 @@ +#ifndef QUERYEXECUTORATTACHES_H +#define QUERYEXECUTORATTACHES_H + +#include "queryexecutorstep.h" +#include "parser/token.h" +#include <QObject> + +/** + * @brief Checks for any databases required to attach and attaches them. + * + * If the query contains any name that is identified to be name of database registered in DbManager, + * then that database gets attached to the current database (the one that we execute query on) + * and its attach name is stored in the query executor context, so all attached databases + * can be later detached. + * + * This step accomplishes a transparent database attaching feature of SQLiteStudio. + * + * @see DbAttacher + */ +class QueryExecutorAttaches : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); +}; + +#endif // QUERYEXECUTORATTACHES_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.cpp new file mode 100644 index 0000000..fceea3f --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.cpp @@ -0,0 +1,99 @@ +#include "queryexecutorcellsize.h" +#include <QDebug> + +bool QueryExecutorCellSize::exec() +{ + if (queryExecutor->getDataLengthLimit() < 0) + return true; + + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + return true; + + foreach (SqliteSelect::Core* core, select->coreSelects) + { + if (!applyDataLimit(select.data(), core)) + return false; + } + + updateQueries(); + return true; +} + +bool QueryExecutorCellSize::applyDataLimit(SqliteSelect* select, SqliteSelect::Core* core) +{ + if (core->tokensMap["selcollist"].size() == 0) + { + qCritical() << "No 'selcollist' in Select::Core. Cannot apply cell size limits."; + return false; + } + + bool first = true; + TokenList tokens; + + foreach (const QueryExecutor::ResultRowIdColumnPtr& col, context->rowIdColumns) + { + if (!first) + tokens += getSeparatorTokens(); + + tokens += getNoLimitTokens(col); + first = false; + } + + foreach (const QueryExecutor::ResultColumnPtr& col, context->resultColumns) + { + if (!first) + tokens += getSeparatorTokens(); + + tokens += getLimitTokens(col); + first = false; + } + + // Wrapping original select with new select with limited columns + select->tokens = wrapSelect(select->tokens, tokens); + + return true; +} + +TokenList QueryExecutorCellSize::getLimitTokens(const QueryExecutor::ResultColumnPtr& resCol) +{ + TokenList newTokens; + newTokens << TokenPtr::create(Token::OTHER, "substr") + << TokenPtr::create(Token::PAR_LEFT, "(") + << TokenPtr::create(Token::OTHER, resCol->queryExecutorAlias) + << TokenPtr::create(Token::OPERATOR, ",") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::INTEGER, "1") + << TokenPtr::create(Token::OPERATOR, ",") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::INTEGER, QString::number(queryExecutor->getDataLengthLimit())) + << TokenPtr::create(Token::PAR_RIGHT, ")") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::KEYWORD, "AS") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::OTHER, resCol->queryExecutorAlias); + return newTokens; +} + +TokenList QueryExecutorCellSize::getNoLimitTokens(const QueryExecutor::ResultRowIdColumnPtr& resCol) +{ + TokenList newTokens; + bool first = true; + foreach (const QString& col, resCol->queryExecutorAliasToColumn.keys()) + { + if (!first) + newTokens += getSeparatorTokens(); + + newTokens << TokenPtr::create(Token::OTHER, col); + first = false; + } + return newTokens; +} + +TokenList QueryExecutorCellSize::getSeparatorTokens() +{ + TokenList newTokens; + newTokens << TokenPtr::create(Token::OPERATOR, ","); + newTokens << TokenPtr::create(Token::SPACE, " "); + return newTokens; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.h new file mode 100644 index 0000000..c174c69 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcellsize.h @@ -0,0 +1,62 @@ +#ifndef QUERYEXECUTORCELLSIZE_H +#define QUERYEXECUTORCELLSIZE_H + +#include "queryexecutorstep.h" + +/** + * @brief Applies per-cell byte size limit to the query. + * + * Size of data extracted for each cell is limited in order to avoid huge memory use + * when the database contains column with like 500MB values per row and the query + * returns for example 100 rows. + * + * This is accomplished by wrapping all result columns (except ROWID columns) with substr() SQL function. + * + * SQLiteStudio limits each column to SqlQueryModel::cellDataLengthLimit when displaying + * data in SqlQueryView. + * + * This feature is disabled by default in QueryExecutor and has to be enabled by defining + * QueryExecutor::setDataLengthLimit(). + */ +class QueryExecutorCellSize : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); + + private: + /** + * @brief Applies limit function to all result columns in given SELECT. + * @param select Select that we want to limit. + * @param core Select's core that we want to limit. + * @return true on success, false on failure. + * + * This method is called for each core in the \p select. + */ + bool applyDataLimit(SqliteSelect* select, SqliteSelect::Core* core); + + /** + * @brief Generates tokens that will return limited value of the result column. + * @param resCol Result column to wrap. + * @return List of tokens. + */ + TokenList getLimitTokens(const QueryExecutor::ResultColumnPtr& resCol); + + /** + * @brief Generates tokens that will return unlimited value of the ROWID result column. + * @param resCol ROWID result column. + * @return List of tokens. + */ + TokenList getNoLimitTokens(const QueryExecutor::ResultRowIdColumnPtr& resCol); + + /** + * @brief Generates tokens representing result columns separator. + * @return List of tokens. + * + * Result columns separator tokens are just a period and a space. + */ + TokenList getSeparatorTokens(); +}; + +#endif // QUERYEXECUTORCELLSIZE_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.cpp new file mode 100644 index 0000000..02f90b2 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.cpp @@ -0,0 +1,256 @@ +#include "queryexecutorcolumns.h" +#include "common/utils_sql.h" +#include "parser/parser.h" +#include "parser/parsererror.h" +#include <QDebug> + +// TODO need to test if attach name resolving works here + +bool QueryExecutorColumns::exec() +{ + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + { + context->editionForbiddenReasons << QueryExecutor::EditionForbiddenReason::NOT_A_SELECT; + return true; + } + + // Resolving result columns of the select + SelectResolver resolver(db, queryExecutor->getOriginalQuery(), context->dbNameToAttach); + resolver.resolveMultiCore = true; + QList<SelectResolver::Column> columns = resolver.resolve(select.data()).first(); + + if (resolver.hasErrors()) + { + qWarning() << "SelectResolver could not resolve the SELECT properly:" << resolver.getErrors().join("\n"); + return false; + } + + if (columns.size() == 0) + { + qWarning() << "SelectResolver could not resolve any column. Probably wrong table name entered by user, or something like that."; + return false; + } + + // Deleting old result columns and defining new ones + SqliteSelect::Core* core = select->coreSelects.first(); + for (SqliteSelect::Core::ResultColumn* resCol : core->resultColumns) + delete resCol; + + core->resultColumns.clear(); + + // Count total rowId columns + int rowIdColCount = 0; + for (const QueryExecutor::ResultRowIdColumnPtr& rowIdCol : context->rowIdColumns) + rowIdColCount += rowIdCol->queryExecutorAliasToColumn.size(); + + // Defining result columns + QueryExecutor::ResultColumnPtr resultColumn; + SqliteSelect::Core::ResultColumn* resultColumnForSelect = nullptr; + bool isRowIdColumn = false; + int i = 0; + for (const SelectResolver::Column& col : columns) + { + // Convert column to QueryExecutor result column + resultColumn = getResultColumn(col); + + // Adding new result column to the query + isRowIdColumn = (i < rowIdColCount); + resultColumnForSelect = getResultColumnForSelect(resultColumn, col, isRowIdColumn); + if (!resultColumnForSelect) + return false; + + resultColumnForSelect->setParent(core); + core->resultColumns << resultColumnForSelect; + + if (!isRowIdColumn) + context->resultColumns << resultColumn; // store it in context for later usage by any step + + i++; + } + + // Update query + select->rebuildTokens(); + wrapWithAliasedColumns(select.data()); + updateQueries(); + +// qDebug() << context->processedQuery; + + return true; +} + +QueryExecutor::ResultColumnPtr QueryExecutorColumns::getResultColumn(const SelectResolver::Column &resolvedColumn) +{ + QueryExecutor::ResultColumnPtr resultColumn = QueryExecutor::ResultColumnPtr::create(); + if (resolvedColumn.type == SelectResolver::Column::OTHER) + { + resultColumn->editionForbiddenReasons << QueryExecutor::ColumnEditionForbiddenReason::EXPRESSION; + resultColumn->displayName = resolvedColumn.displayName; + resultColumn->column = resolvedColumn.column; + resultColumn->alias = resolvedColumn.alias; + resultColumn->expression = true; + resultColumn->queryExecutorAlias = getNextColName(); + } + else + { + if (isSystemTable(resolvedColumn.table)) + resultColumn->editionForbiddenReasons << QueryExecutor::ColumnEditionForbiddenReason::SYSTEM_TABLE; + + if (resolvedColumn.flags & SelectResolver::FROM_COMPOUND_SELECT) + resultColumn->editionForbiddenReasons << QueryExecutor::ColumnEditionForbiddenReason::COMPOUND_SELECT; + + if (resolvedColumn.flags & SelectResolver::FROM_GROUPED_SELECT) + resultColumn->editionForbiddenReasons << QueryExecutor::ColumnEditionForbiddenReason::GROUPED_RESULTS; + + if (resolvedColumn.flags & SelectResolver::FROM_DISTINCT_SELECT) + resultColumn->editionForbiddenReasons << QueryExecutor::ColumnEditionForbiddenReason::DISTINCT_RESULTS; + + resultColumn->database = resolvedColumn.originalDatabase; + resultColumn->table = resolvedColumn.table; + resultColumn->column = resolvedColumn.column; + resultColumn->tableAlias = resolvedColumn.tableAlias; + resultColumn->alias = resolvedColumn.alias; + resultColumn->displayName = resolvedColumn.displayName; + + if (isRowIdColumnAlias(resultColumn->alias)) + { + resultColumn->queryExecutorAlias = resultColumn->alias; + } + else + { + resultColumn->queryExecutorAlias = getNextColName(); + } + } + return resultColumn; +} + +SqliteSelect::Core::ResultColumn* QueryExecutorColumns::getResultColumnForSelect(const QueryExecutor::ResultColumnPtr& resultColumn, const SelectResolver::Column& col, bool rowIdColumn) +{ + SqliteSelect::Core::ResultColumn* selectResultColumn = new SqliteSelect::Core::ResultColumn(); + + QString colString = resultColumn->column; + if (!resultColumn->expression) + colString = wrapObjIfNeeded(colString, dialect); + + Parser parser(dialect); + SqliteExpr* expr = parser.parseExpr(colString); + if (!expr) + { + qWarning() << "Could not parse result column expr:" << colString; + if (parser.getErrors().size() > 0) + qWarning() << "The error was:" << parser.getErrors().first()->getFrom() << ":" << parser.getErrors().first()->getMessage(); + + return nullptr; + } + + expr->setParent(selectResultColumn); + selectResultColumn->expr = expr; + + if (!(col.flags & SelectResolver::FROM_ANONYMOUS_SELECT)) // anonymous subselect will result in no prefixes for result column + { + if (!resultColumn->tableAlias.isNull()) + { + selectResultColumn->expr->table = resultColumn->tableAlias; + } + else if (!resultColumn->table.isNull()) + { + if (!resultColumn->database.isNull()) + { + if (context->dbNameToAttach.containsLeft(resultColumn->database, Qt::CaseInsensitive)) + selectResultColumn->expr->database = context->dbNameToAttach.valueByLeft(resultColumn->database, Qt::CaseInsensitive); + else + selectResultColumn->expr->database = resultColumn->database; + } + + selectResultColumn->expr->table = resultColumn->table; + } + } + + if (!col.alias.isNull()) + { + selectResultColumn->asKw = true; + selectResultColumn->alias = col.alias; + } + else if (rowIdColumn || resultColumn->expression) + { + selectResultColumn->asKw = true; + selectResultColumn->alias = resultColumn->queryExecutorAlias; + } + + return selectResultColumn; +} + +QString QueryExecutorColumns::resolveAttachedDatabases(const QString &dbName) +{ + if (context->dbNameToAttach.containsRight(dbName, Qt::CaseInsensitive)) + return context->dbNameToAttach.valueByRight(dbName, Qt::CaseInsensitive); + + return dbName; +} + +bool QueryExecutorColumns::isRowIdColumnAlias(const QString& alias) +{ + foreach (QueryExecutor::ResultRowIdColumnPtr rowIdColumn, context->rowIdColumns) + { + if (rowIdColumn->queryExecutorAliasToColumn.keys().contains(alias)) + return true; + } + return false; +} + +void QueryExecutorColumns::wrapWithAliasedColumns(SqliteSelect* select) +{ + // Wrap everything in a surrounding SELECT and given query executor alias to all columns this time + TokenList sepTokens; + sepTokens << TokenPtr::create(Token::OPERATOR, ",") << TokenPtr::create(Token::SPACE, " "); + + bool first = true; + TokenList outerColumns; + for (const QueryExecutor::ResultRowIdColumnPtr& rowIdColumn : context->rowIdColumns) + { + for (const QString& alias : rowIdColumn->queryExecutorAliasToColumn.keys()) + { + if (!first) + outerColumns += sepTokens; + + outerColumns << TokenPtr::create(Token::OTHER, alias); + first = false; + } + } + + QStringList columnNamesUsed; + QString baseColName; + QString colName; + static_qstring(colNameTpl, "%1:%2"); + for (const QueryExecutor::ResultColumnPtr& resCol : context->resultColumns) + { + if (!first) + outerColumns += sepTokens; + + // If alias was given, we use it. If it was anything but expression, we also use its display name, + // because it's explicit column (no matter if from table, or table alias). + baseColName = QString(); + if (!resCol->alias.isNull()) + baseColName = resCol->alias; + else if (!resCol->expression) + baseColName = resCol->column; + + if (!baseColName.isNull()) + { + colName = baseColName; + for (int i = 1; columnNamesUsed.contains(colName, Qt::CaseInsensitive); i++) + colName = colNameTpl.arg(resCol->column, QString::number(i)); + + columnNamesUsed << colName; + outerColumns << TokenPtr::create(Token::OTHER, wrapObjIfNeeded(colName, dialect)); + outerColumns << TokenPtr::create(Token::SPACE, " "); + outerColumns << TokenPtr::create(Token::KEYWORD, "AS"); + outerColumns << TokenPtr::create(Token::SPACE, " "); + } + outerColumns << TokenPtr::create(Token::OTHER, resCol->queryExecutorAlias); + first = false; + } + + //QString t = outerColumns.detokenize(); // keeping it for debug purposes + select->tokens = wrapSelect(select->tokens, outerColumns); +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.h new file mode 100644 index 0000000..3f90311 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcolumns.h @@ -0,0 +1,72 @@ +#ifndef QUERYEXECUTORCOLUMNS_H +#define QUERYEXECUTORCOLUMNS_H + +#include "queryexecutorstep.h" +#include "selectresolver.h" + +/** + * @brief Assigns unique alias names for all result columns. + * + * This step replaces result columns of the SELECT query. + * It's performed only if last query is the SELECT, otherwise it does nothing. + * + * It works on subselects first, then goes towards outer SELECTs. + * + * Star operator ("all columns") is replaced by list of columns and each column gets alias. + * + * If result column comes from subselect and the subselect was already covered by this step, + * then the column does not get new alias, instead the existing one is used. + * + * While generating alias names, this step also finds out details about columns: source database, source table + * column contraints, etc. Those informations are stored using generated alias name as a key. + * + * Some columns can be defined as not editable, because of various reasons: QueryExecutor::ColumnEditionForbiddenReason. + * Those reasons are defined in this step. + */ +class QueryExecutorColumns : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); + + private: + + /** + * @brief Transforms SelectResolver's columns into QueryExecutor's columns. + * @param resolvedColumn Result columns resolved by SelectResolver. + * @return Converted column. + * + * QueryExecutor understands different model of result columns than SelectResolver. + * Converted columns are later used by other steps and it's also returned from QueryExecutor as an information + * about result columns of the query. + */ + QueryExecutor::ResultColumnPtr getResultColumn(const SelectResolver::Column& resolvedColumn); + + /** + * @brief Generates result column object with proper alias name. + * @param resultColumn Original result column from the query. + * @param col Original result column as resolved by SelectResolver. + * @param rowIdColumn Indicates if this is a call for ROWID column added by QueryExecutorRowId step. + * @return Result column object ready for rebuilding tokens and detokenizing. + */ + SqliteSelect::Core::ResultColumn* getResultColumnForSelect(const QueryExecutor::ResultColumnPtr& resultColumn, const SelectResolver::Column& col, bool rowIdColumn); + + /** + * @brief Translates attach name into database name. + * @param dbName Attach name. + * @return Database name as registered in DbManager, or \p dbName if given name was not resolved to any registered database. + */ + QString resolveAttachedDatabases(const QString& dbName); + + /** + * @brief Checks if given alias name belongs to ROWID result column. + * @param alias Alias name to check. + * @return true if the alias belongs to ROWID column, or false otherwise. + */ + bool isRowIdColumnAlias(const QString& alias); + + void wrapWithAliasedColumns(SqliteSelect* select); +}; + +#endif // QUERYEXECUTORCOLUMNS_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcountresults.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcountresults.cpp new file mode 100644 index 0000000..f51bf34 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcountresults.cpp @@ -0,0 +1,22 @@ +#include "queryexecutorcountresults.h" +#include "parser/ast/sqlitequery.h" +#include "db/queryexecutor.h" +#include <math.h> +#include <QDebug> + +bool QueryExecutorCountResults::exec() +{ + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + { + // No return rows, but we're good to go. + // Pragma and Explain statements use "rows affected" for number of rows. + return true; + } + + QString countSql = "SELECT count(*) AS cnt FROM ("+select->detokenize()+");"; + context->countingQuery = countSql; + + // qDebug() << "count sql:" << countSql; + return true; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcountresults.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcountresults.h new file mode 100644 index 0000000..654433a --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorcountresults.h @@ -0,0 +1,19 @@ +#ifndef QUERYEXECUTORCOUNTRESULTS_H +#define QUERYEXECUTORCOUNTRESULTS_H + +#include "queryexecutorstep.h" + +/** + * @brief Defines counting query string. + * + * @see QueryExecutor::countResults() + */ +class QueryExecutorCountResults : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); +}; + +#endif // QUERYEXECUTORCOUNTRESULTS_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordatasources.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordatasources.cpp new file mode 100644 index 0000000..9a5c1c2 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordatasources.cpp @@ -0,0 +1,32 @@ +#include "queryexecutordatasources.h" +#include "parser/ast/sqliteselect.h" +#include "selectresolver.h" + +bool QueryExecutorDataSources::exec() +{ + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + return true; + + if (select->coreSelects.size() > 1) // compound selects might have different collection of tables + return true; + + if (select->coreSelects.first()->valuesMode) + return true; + + SelectResolver resolver(db, select->tokens.detokenize()); + resolver.resolveMultiCore = false; // multicore subselects result in not editable columns, skip them + + SqliteSelect::Core* core = select->coreSelects.first(); + QSet<SelectResolver::Table> tables = resolver.resolveTables(core); + foreach (SelectResolver::Table resolvedTable, tables) + { + QueryExecutor::SourceTablePtr table = QueryExecutor::SourceTablePtr::create(); + table->database = resolvedTable.database; + table->table = resolvedTable.table; + table->alias = resolvedTable.alias; + context->sourceTables << table; + } + + return true; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordatasources.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordatasources.h new file mode 100644 index 0000000..26650aa --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordatasources.h @@ -0,0 +1,24 @@ +#ifndef QUERYEXECUTORDATASOURCES_H +#define QUERYEXECUTORDATASOURCES_H + +#include "queryexecutorstep.h" + +/** + * @brief Finds all source tables. + * + * Finds all source tables for the SELECT query (if it's the last query in the query string) + * and stores them in QueryExecutor::Context::sourceTables. They are later provided by QueryExecutor::getSourceTables() + * as a meta information about data sources. + * + * Source tables are tables that result columns come from. If there's multiple columns selected + * from single table, only single table is resolved. + */ +class QueryExecutorDataSources : public QueryExecutorStep +{ + Q_OBJECT + public: + bool exec(); + +}; + +#endif // QUERYEXECUTORDATASOURCES_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordetectschemaalter.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordetectschemaalter.cpp new file mode 100644 index 0000000..c3c8a5c --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordetectschemaalter.cpp @@ -0,0 +1,26 @@ +#include "queryexecutordetectschemaalter.h" + +bool QueryExecutorDetectSchemaAlter::exec() +{ + for (SqliteQueryPtr query : context->parsedQueries) + { + switch (query->queryType) + { + case SqliteQueryType::AlterTable: + case SqliteQueryType::CreateIndex: + case SqliteQueryType::CreateTable: + case SqliteQueryType::CreateTrigger: + case SqliteQueryType::CreateView: + case SqliteQueryType::DropIndex: + case SqliteQueryType::DropTable: + case SqliteQueryType::DropTrigger: + case SqliteQueryType::DropView: + case SqliteQueryType::CreateVirtualTable: + context->schemaModified = true; + break; + default: + break; + } + } + return true; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordetectschemaalter.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordetectschemaalter.h new file mode 100644 index 0000000..760513e --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutordetectschemaalter.h @@ -0,0 +1,14 @@ +#include "queryexecutorstep.h" + +#ifndef QUERYEXECUTORDETECTSCHEMAALTER_H +#define QUERYEXECUTORDETECTSCHEMAALTER_H + +class QueryExecutorDetectSchemaAlter : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); +}; + +#endif // QUERYEXECUTORDETECTSCHEMAALTER_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.cpp new file mode 100644 index 0000000..7e0abe5 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.cpp @@ -0,0 +1,149 @@ +#include "queryexecutorexecute.h" +#include "db/sqlerrorcodes.h" +#include "db/queryexecutor.h" +#include "parser/ast/sqlitequery.h" +#include "parser/lexer.h" +#include "parser/ast/sqlitecreatetable.h" +#include "datatype.h" +#include <QDateTime> +#include <QDebug> +#include <schemaresolver.h> + +bool QueryExecutorExecute::exec() +{ + qDebug() << "q:" << context->processedQuery; + + startTime = QDateTime::currentMSecsSinceEpoch(); + return executeQueries(); +} + +void QueryExecutorExecute::provideResultColumns(SqlQueryPtr results) +{ + QueryExecutor::ResultColumnPtr resCol; + foreach (const QString& colName, results->getColumnNames()) + { + resCol = QueryExecutor::ResultColumnPtr::create(); + resCol->displayName = colName; + context->resultColumns << resCol; + } +} + +bool QueryExecutorExecute::executeQueries() +{ + QHash<QString, QVariant> bindParamsForQuery; + SqlQueryPtr results; + + Db::Flags flags; + if (context->preloadResults) + flags |= Db::Flag::PRELOAD; + + int queryCount = context->parsedQueries.size(); + for (const SqliteQueryPtr& query : context->parsedQueries) + { + bindParamsForQuery = getBindParamsForQuery(query); + results = db->prepare(query->detokenize()); + results->setArgs(bindParamsForQuery); + results->setFlags(flags); + + queryCount--; + if (queryCount == 0) // last query? + setupSqlite2ColumnDataTypes(results); + + results->execute(); + + if (results->isError()) + { + handleFailResult(results); + return false; + } + } + handleSuccessfulResult(results); + return true; +} + +void QueryExecutorExecute::handleSuccessfulResult(SqlQueryPtr results) +{ + SqliteSelectPtr select = getSelect(); + if (!select || select->coreSelects.size() > 1 || select->explain) + { + // In this case, the "Columns" step didn't provide result columns. + // We need to do it here, basing on actual results. + provideResultColumns(results); + } + + context->executionTime = QDateTime::currentMSecsSinceEpoch() - startTime; + context->rowsAffected = results->rowsAffected(); + + // For PRAGMA and EXPLAIN we simply count results for rows returned + SqliteQueryPtr lastQuery = context->parsedQueries.last(); + if (lastQuery->queryType != SqliteQueryType::Select || lastQuery->explain) + context->rowsCountingRequired = true; + + if (context->resultsHandler) + { + context->resultsHandler(results); + context->resultsHandler = nullptr; + } + + context->executionResults = results; +} + +void QueryExecutorExecute::handleFailResult(SqlQueryPtr results) +{ + if (!results->isInterrupted()) + { + qWarning() << "Could not execute query with smart method:" << queryExecutor->getOriginalQuery() + << "\nError message:" << results->getErrorText() + << "\nSkipping smart execution."; + } +} + +QHash<QString, QVariant> QueryExecutorExecute::getBindParamsForQuery(SqliteQueryPtr query) +{ + QHash<QString, QVariant> queryParams; + QStringList bindParams = query->tokens.filter(Token::BIND_PARAM).toStringList(); + foreach (const QString& bindParam, bindParams) + { + if (context->queryParameters.contains(bindParam)) + queryParams.insert(bindParam, context->queryParameters[bindParam]); + } + return queryParams; +} + +void QueryExecutorExecute::setupSqlite2ColumnDataTypes(SqlQueryPtr results) +{ + Sqlite2ColumnDataTypeHelper* sqlite2Helper = dynamic_cast<Sqlite2ColumnDataTypeHelper*>(results.data()); + if (!sqlite2Helper) + return; + + QPair<QString,QString> key; + SqliteCreateTablePtr createTable; + + SchemaResolver resolver(db); + QHash<QPair<QString,QString>,SqliteCreateTablePtr> tables; + for (QueryExecutor::SourceTablePtr tab : context->sourceTables) + { + if (tab->table.isNull()) + continue; + + key = QPair<QString,QString>(tab->database, tab->table); + createTable = resolver.getParsedObject(tab->database, tab->table, SchemaResolver::TABLE).dynamicCast<SqliteCreateTable>(); + tables[key] = createTable; + } + + sqlite2Helper->clearBinaryTypes(); + + SqliteCreateTable::Column* column = nullptr; + int idx = -1 + context->rowIdColumns.size(); + for (QueryExecutor::ResultColumnPtr resCol : context->resultColumns) + { + idx++; + key = QPair<QString,QString>(resCol->database, resCol->table); + if (!tables.contains(key)) + continue; + + column = tables[key]->getColumn(resCol->column); + if (column->type && DataType::isBinary(column->type->name)) + sqlite2Helper->setBinaryType(idx); + } +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.h new file mode 100644 index 0000000..a88bf56 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexecute.h @@ -0,0 +1,85 @@ +#ifndef QUERYEXECUTOREXECUTE_H +#define QUERYEXECUTOREXECUTE_H + +#include "queryexecutorstep.h" +#include <QHash> + +/** + * @brief Executes query in current form. + * + * Executes query synchronously (since entire query executor works in another thread anyway). + * After execution is finished it provides information about how long it took, whether there was + * an error, and how many rows were affected/returned. + * + * The query string may contain many queries separated by semicolon and this step will split + * them correctly, then execute one-by-one. Results are loaded only from last query execution. + * + * If the last query was not processed by QueryExecutorColumns step, then this step + * will provide list result column names basing on what names returned SQLite. + * + * For PRAGMA and EXPLAIN statements rows returned are not accurate + * and QueryExecutor::Context::rowsCountingRequired is set to true. + */ +class QueryExecutorExecute : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); + + private: + /** + * @brief Gives list of column names as SQLite returned them. + * @param results Execution results. + */ + void provideResultColumns(SqlQueryPtr results); + + /** + * @brief Executes the query. + * @return true on success, false on failure. + * + * Stops on first error and in that case rolls back transaction. + * + * If QueryExecutor::Context::preloadResults is true, then also Db::Flag::PRELOAD + * is appended to execution flags. + */ + bool executeQueries(); + + /** + * @brief Extracts meta information from results. + * @param results Execution results. + * + * Meta information includes rows affected, execution time, etc. + */ + void handleSuccessfulResult(SqlQueryPtr results); + + /** + * @brief Handles failed execution. + * @param results Execution results. + * + * Currently this method doesn't do much. It just checks whether execution + * error was caused by call to Db::interrupt(), or not and if not, + * then the warning is logged about it and executor falls back to simple + * execution method. + */ + void handleFailResult(SqlQueryPtr results); + + /** + * @brief Prepares parameters for query execution. + * @param query Query to be executed. + * @return Map of parameters for the query. + * + * It generates parameters basing on what are parameter placeholders in the query + * and what are parameter values available in QueryExecutor::Context::queryParameters. + */ + QHash<QString, QVariant> getBindParamsForQuery(SqliteQueryPtr query); + + /** + * @brief Number of milliseconds since 1970 at execution start moment. + */ + qint64 startTime; + + void setupSqlite2ColumnDataTypes(SqlQueryPtr results); +}; + +#endif // QUERYEXECUTOREXECUTE_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexplainmode.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexplainmode.cpp new file mode 100644 index 0000000..117022e --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexplainmode.cpp @@ -0,0 +1,28 @@ +#include "queryexecutorexplainmode.h" + +bool QueryExecutorExplainMode::exec() +{ + if (!context->explainMode) + return true; // explain mode disabled + + SqliteQueryPtr lastQuery = context->parsedQueries.last(); + + if (!lastQuery) + return true; + + // If last query wasn't in explain mode, switch it on + if (!lastQuery->explain) + { + lastQuery->explain = true; + lastQuery->tokens.prepend(TokenPtr::create(Token::SPACE, " ")); + lastQuery->tokens.prepend(TokenPtr::create(Token::KEYWORD, "EXPLAIN")); + } + + // Limit queries to only last one + context->parsedQueries.clear(); + context->parsedQueries << lastQuery; + + updateQueries(); + + return true; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexplainmode.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexplainmode.h new file mode 100644 index 0000000..bd806c7 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorexplainmode.h @@ -0,0 +1,18 @@ +#ifndef QUERYEXECUTOREXPLAINMODE_H +#define QUERYEXECUTOREXPLAINMODE_H + +#include "queryexecutorstep.h" + +/** + * @brief Applies QueryExecutor::Context::explainMode to the query. + * + * If explain mode is enabled, it just prepends "EXPLAIN" before the query. + */ +class QueryExecutorExplainMode : public QueryExecutorStep +{ + Q_OBJECT + public: + bool exec(); +}; + +#endif // QUERYEXECUTOREXPLAINMODE_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorlimit.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorlimit.cpp new file mode 100644 index 0000000..af1d7a6 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorlimit.cpp @@ -0,0 +1,30 @@ +#include "queryexecutorlimit.h" +#include "parser/ast/sqlitelimit.h" +#include <QDebug> + +bool QueryExecutorLimit::exec() +{ + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + return true; + + int page = queryExecutor->getPage(); + if (page < 0) + return true; // no paging requested + + if (select->tokens.size() < 1) + return true; // shouldn't happen, but if happens, quit gracefully + + quint64 limit = queryExecutor->getResultsPerPage(); + quint64 offset = limit * page; + + // The original query is last, so if it contained any %N strings, + // they won't be replaced. + static_qstring(selectTpl, "SELECT * FROM (%1) LIMIT %2 OFFSET %3"); + QString newSelect = selectTpl.arg(select->detokenize(), QString::number(limit), QString::number(offset)); + + int begin = select->tokens.first()->start; + int length = select->tokens.last()->end - select->tokens.first()->start + 1; + context->processedQuery = context->processedQuery.replace(begin, length, newSelect); + return true; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorlimit.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorlimit.h new file mode 100644 index 0000000..56714b4 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorlimit.h @@ -0,0 +1,22 @@ +#ifndef QUERYEXECUTORLIMIT_H +#define QUERYEXECUTORLIMIT_H + +#include "queryexecutorstep.h" + +/** + * @brief Applies row count limit to the query. + * + * If row count limit is enabled (QueryExecutor::Context::setPage() + * and QueryExecutor::Context::setResultsPerPage), then the SELECT query + * is wrapped with another SELECT which defines it's own LIMIT and OFFSET + * basing on the page and the results per page parameters. + */ +class QueryExecutorLimit : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); +}; + +#endif // QUERYEXECUTORLIMIT_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutororder.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutororder.cpp new file mode 100644 index 0000000..09e35ed --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutororder.cpp @@ -0,0 +1,79 @@ +#include "queryexecutororder.h" +#include "common/utils_sql.h" +#include "parser/parser.h" +#include <QDebug> + +bool QueryExecutorOrder::exec() +{ + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + return true; + + QueryExecutor::SortList sortOrder = queryExecutor->getSortOrder(); + if (sortOrder.size() == 0) + return true; // no sorting requested + + if (select->tokens.size() < 1) + return true; // shouldn't happen, but if happens, leave gracefully + + TokenList tokens = getOrderTokens(sortOrder); + if (tokens.size() == 0) + return false; + + static_qstring(selectTpl, "SELECT * FROM (%1) ORDER BY %2"); + QString newSelect = selectTpl.arg(select->detokenize(), tokens.detokenize()); + + Parser parser(dialect); + if (!parser.parse(newSelect) || parser.getQueries().size() == 0) + { + qWarning() << "Could not parse SELECt after applying order. Tried to parse query:\n" << newSelect; + return false; + } + + context->parsedQueries.removeLast(); + context->parsedQueries << parser.getQueries().first(); + + updateQueries(); + return true; +} + +TokenList QueryExecutorOrder::getOrderTokens(const QueryExecutor::SortList& sortOrder) +{ + TokenList tokens; + QueryExecutor::ResultColumnPtr resCol; + bool next = false; + for (const QueryExecutor::Sort& sort : sortOrder) + { + if (sort.column >= context->resultColumns.size()) + { + qCritical() << "There is less result columns in query executor context than index of requested sort column"; + return TokenList(); + } + + if (next) + { + tokens << TokenPtr::create(Token::OPERATOR, ","); + tokens << TokenPtr::create(Token::SPACE, " "); + } + else + next = true; + + resCol = context->resultColumns[sort.column]; + + tokens << TokenPtr::create(Token::OTHER, resCol->queryExecutorAlias); + tokens << TokenPtr::create(Token::SPACE, " "); + + if (sort.order == QueryExecutor::Sort::DESC) + { + tokens << TokenPtr::create(Token::KEYWORD, "DESC"); + tokens << TokenPtr::create(Token::SPACE, " "); + } + else + { + tokens << TokenPtr::create(Token::KEYWORD, "ASC"); + tokens << TokenPtr::create(Token::SPACE, " "); + } + } + + return tokens; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutororder.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutororder.h new file mode 100644 index 0000000..d321f9b --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutororder.h @@ -0,0 +1,30 @@ +#ifndef QUERYEXECUTORORDER_H +#define QUERYEXECUTORORDER_H + +#include "queryexecutorstep.h" + +/** + * @brief Applies sorting to the query. + * + * Sorting is done by enclosing SELECT query with another SELECT + * query, but the outer one uses order defined in this step. + * + * The order is defined by QueryExecutor::setSortOrder(). + */ +class QueryExecutorOrder : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); + + private: + /** + * @brief Generates tokens to sort by given column and order. + * @param sortOrder Definition of order to use. + * @return Tokens to append to the SELECT. + */ + TokenList getOrderTokens(const QueryExecutor::SortList& sortOrder); +}; + +#endif // QUERYEXECUTORORDER_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorparsequery.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorparsequery.cpp new file mode 100644 index 0000000..cd91734 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorparsequery.cpp @@ -0,0 +1,48 @@ +#include "queryexecutorparsequery.h" +#include "db/queryexecutor.h" +#include "parser/parser.h" +#include <QDebug> + +QueryExecutorParseQuery::QueryExecutorParseQuery(const QString& name) + : QueryExecutorStep() +{ + setObjectName(name); +} + +QueryExecutorParseQuery::~QueryExecutorParseQuery() +{ + if (parser) + delete parser; +} + +bool QueryExecutorParseQuery::exec() +{ + // Prepare parser + if (parser) + delete parser; + + parser = new Parser(dialect); + + // Do parsing + context->parsedQueries.clear(); + parser->parse(context->processedQuery); + if (parser->getErrors().size() > 0) + { + qWarning() << "QueryExecutorParseQuery:" << parser->getErrorString() << "\n" + << "Query parsed:" << context->processedQuery; + return false; + } + + if (parser->getQueries().size() == 0) + { + qWarning() << "No queries parsed in QueryExecutorParseQuery step."; + return false; + } + + context->parsedQueries = parser->getQueries(); + + // We never want the semicolon in last query, because the query could be wrapped with a SELECT + context->parsedQueries.last()->tokens.trimRight(Token::OPERATOR, ";"); + + return true; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorparsequery.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorparsequery.h new file mode 100644 index 0000000..6dc8ab5 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorparsequery.h @@ -0,0 +1,29 @@ +#ifndef QUERYEXECUTORPARSEQUERY_H +#define QUERYEXECUTORPARSEQUERY_H + +#include "queryexecutorstep.h" + +/** + * @brief Parses current query and stores results in the context. + * + * Parses QueryExecutor::Context::processedQuery and stores results + * in QueryExecutor::Context::parsedQueries. + * + * This is used after some changes were made to the query and next steps will + * require parsed representation of queries to be updated. + */ +class QueryExecutorParseQuery : public QueryExecutorStep +{ + Q_OBJECT + + public: + explicit QueryExecutorParseQuery(const QString& name); + ~QueryExecutorParseQuery(); + + bool exec(); + + private: + Parser* parser = nullptr; +}; + +#endif // QUERYEXECUTORPARSEQUERY_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorreplaceviews.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorreplaceviews.cpp new file mode 100644 index 0000000..94300a0 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorreplaceviews.cpp @@ -0,0 +1,113 @@ +#include "queryexecutorreplaceviews.h" +#include "parser/ast/sqlitecreateview.h" +#include "schemaresolver.h" +#include <QDebug> + +QueryExecutorReplaceViews::~QueryExecutorReplaceViews() +{ + if (schemaResolver) + { + delete schemaResolver; + schemaResolver = nullptr; + } +} + +bool QueryExecutorReplaceViews::exec() +{ + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + return true; + + if (select->coreSelects.size() > 1) + return true; + + if (select->coreSelects.first()->distinctKw) + return true; + + replaceViews(select.data()); + updateQueries(); + + return true; +} + +void QueryExecutorReplaceViews::init() +{ + if (!schemaResolver) + schemaResolver = new SchemaResolver(db); +} + +QStringList QueryExecutorReplaceViews::getViews(const QString& database) +{ + QString dbName = database.isNull() ? "main" : database.toLower(); + if (views.contains(dbName)) + return views[dbName]; + + views[dbName] = schemaResolver->getViews(database); + return views[dbName]; +} + +SqliteCreateViewPtr QueryExecutorReplaceViews::getView(const QString& database, const QString& viewName) +{ + View view(database, viewName); + if (viewStatements.contains(view)) + return viewStatements[view]; + + SqliteQueryPtr query = schemaResolver->getParsedObject(database, viewName, SchemaResolver::VIEW); + if (!query) + return SqliteCreateViewPtr(); + + SqliteCreateViewPtr viewPtr = query.dynamicCast<SqliteCreateView>(); + if (!viewPtr) + return SqliteCreateViewPtr(); + + viewStatements[view] = viewPtr; + return viewPtr; +} + +void QueryExecutorReplaceViews::replaceViews(SqliteSelect* select) +{ + SqliteSelect::Core* core = select->coreSelects.first(); + + QStringList viewsInDatabase; + SqliteCreateViewPtr view; + + QList<SqliteSelect::Core::SingleSource*> sources = core->getAllTypedStatements<SqliteSelect::Core::SingleSource>(); + foreach (SqliteSelect::Core::SingleSource* src, sources) + { + if (src->table.isNull()) + continue; + + viewsInDatabase = getViews(src->database); + if (!viewsInDatabase.contains(src->table, Qt::CaseInsensitive)) + continue; + + view = getView(src->database, src->table); + if (!view) + { + qWarning() << "Object" << src->database << "." << src->table + << "was identified to be a view, but could not get it's parsed representation."; + continue; + } + + src->select = view->select; + src->database = QString::null; + src->table = QString::null; + } + + select->rebuildTokens(); +} + +uint qHash(const QueryExecutorReplaceViews::View& view) +{ + return qHash(view.database + "." + view.view); +} + +QueryExecutorReplaceViews::View::View(const QString& database, const QString& view) : + database(database), view(view) +{ +} + +int QueryExecutorReplaceViews::View::operator==(const QueryExecutorReplaceViews::View& other) const +{ + return database == other.database && view == other.view; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorreplaceviews.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorreplaceviews.h new file mode 100644 index 0000000..633b0bc --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorreplaceviews.h @@ -0,0 +1,110 @@ +#ifndef QUERYEXECUTORREPLACEVIEWS_H +#define QUERYEXECUTORREPLACEVIEWS_H + +#include "queryexecutorstep.h" +#include "parser/ast/sqlitecreateview.h" + +/** + * @brief Replaces all references to views in query with SELECTs from those views. + * + * Replacing views with their SELECTs (as subselects) simplifies later tasks + * with the query. + */ +class QueryExecutorReplaceViews : public QueryExecutorStep +{ + Q_OBJECT + + public: + ~QueryExecutorReplaceViews(); + + bool exec(); + + protected: + void init(); + + private: + /** + * @brief View representation in context of QueryExecutorReplaceViews step. + */ + struct View + { + /** + * @brief Creates view. + * @param database Database of the view. + * @param view View name. + */ + View(const QString& database, const QString& view); + + /** + * @brief Database of the view. + */ + QString database; + + /** + * @brief View name. + */ + QString view; + + /** + * @brief Checks if it's the same view as the \p other. + * @param other Other view to compare. + * @return 1 if other view is the same one, or 0 otherwise. + * + * Views are equal if they have equal name and database. + */ + int operator==(const View& other) const; + }; + + friend uint qHash(const View& view); + + /** + * @brief Provides all views existing in the database. + * @param database Database name as typed in the query. + * @return List of view names. + * + * Uses internal cache (using views). + */ + QStringList getViews(const QString& database); + + /** + * @brief Reads view's DDL, parses it and returns results. + * @param database Database of the view. + * @param viewName View name. + * @return Parsed view or null pointer if view doesn't exist or could not be parsed. + * + * It uses internal cache (using viewStatements). + */ + SqliteCreateViewPtr getView(const QString& database, const QString& viewName); + + /** + * @brief Replaces views in the query with SELECT statements. + * @param select SELECT statement to replace views in. + * + * It explores the \p select looking for view names and replaces them with + * apropriate subselect queries, using getView() calls. + */ + void replaceViews(SqliteSelect* select); + + /** + * @brief Used for caching view list per database. + */ + QHash<QString,QStringList> views; + + /** + * @brief Resolver used several time in this step. + * + * It's stored as member of this class, cause otherwise it would be created + * and deleted many times. Instead it's shared across all calls to resolve something + * from schema. + */ + SchemaResolver* schemaResolver = nullptr; + + /** + * @brief Used for caching parsed view statement. + */ + QHash<View,SqliteCreateViewPtr> viewStatements; +}; + +uint qHash(const QueryExecutorReplaceViews::View& view); + +#endif // QUERYEXECUTORREPLACEVIEWS_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorstep.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorstep.cpp new file mode 100644 index 0000000..d64ea2e --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorstep.cpp @@ -0,0 +1,63 @@ +#include "queryexecutorstep.h" +#include "db/queryexecutor.h" +#include "common/unused.h" + +QueryExecutorStep::~QueryExecutorStep() +{ +} + +void QueryExecutorStep::init(QueryExecutor *queryExecutor, QueryExecutor::Context *context) +{ + this->queryExecutor = queryExecutor; + this->context = context; + db = queryExecutor->getDb(); + dialect = db->getDialect(); + init(); +} + +void QueryExecutorStep::updateQueries() +{ + QString newQuery; + foreach (SqliteQueryPtr query, context->parsedQueries) + { + newQuery += query->detokenize(); + newQuery += "\n"; + } + context->processedQuery = newQuery; +} + +QString QueryExecutorStep::getNextColName() +{ + return "ResCol_" + QString::number(context->colNameSeq++); +} + +SqliteSelectPtr QueryExecutorStep::getSelect() +{ + SqliteQueryPtr lastQuery = context->parsedQueries.last(); + if (lastQuery->queryType != SqliteQueryType::Select) + return SqliteSelectPtr(); + + return lastQuery.dynamicCast<SqliteSelect>(); +} + +void QueryExecutorStep::init() +{ +} + +TokenList QueryExecutorStep::wrapSelect(const TokenList& selectTokens, const TokenList& resultColumnsTokens) +{ + TokenList oldSelectTokens = selectTokens; + oldSelectTokens.trimRight(Token::OPERATOR, ";"); + + TokenList newTokens; + newTokens << TokenPtr::create(Token::KEYWORD, "SELECT") + << TokenPtr::create(Token::SPACE, " "); + newTokens += resultColumnsTokens; + newTokens << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::KEYWORD, "FROM") + << TokenPtr::create(Token::SPACE, " ") + << TokenPtr::create(Token::PAR_LEFT, "("); + newTokens += oldSelectTokens; + newTokens << TokenPtr::create(Token::PAR_RIGHT, ")"); + return newTokens; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorstep.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorstep.h new file mode 100644 index 0000000..a15ad9c --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorstep.h @@ -0,0 +1,158 @@ +#ifndef QUERYEXECUTORSTEP_H +#define QUERYEXECUTORSTEP_H + +#include "db/sqlquery.h" +#include "parser/ast/sqliteselect.h" +#include "db/queryexecutor.h" +#include <QObject> + +/** + * @brief Base class for all query executor steps. + * + * Query Executor step is a single step that query executor performs at a time + * during the "smart execution" (see QueryExecutor for details). + * + * Steps are created and queued in QueryExecutor::setupExecutionChain(). + * Order of steps execution is very important and should not be distrurbed, + * unless you know what you're doing. + * + * A step must implement one method: exec(). + * The implementation can access QueryExecutor instance that is running the step + * and also it can access common context for executor and all the steps. + * + * Steps usually introduce some modifications to the query, so the final execution + * can provide more meta-information, or works on limited set of data, etc. + * + * Steps can access common context to get parsed object of the current query. + * The current query is the query processed by previous steps and re-parsed after + * those modifications. Current query is also available in a string representation + * in the context. The original query string (before any modifications) is also + * available in the context. See QueryExecutor::Context for more. + * + * QueryExecutorStep provides several methods to help dealing with common routines, + * such as updating current query with modified query definition (updateQueries()), + * or extracting parsed SELECT (if the last query defined was the SELECT) object + * (getSelect()). When the step needs to add new result column, it can use + * getNextColName() to generate unique name. + * + * To access database object, that the query is executed on, use QueryExecutor::getDb(). + */ +class QueryExecutorStep : public QObject +{ + Q_OBJECT + + public: + /** + * @brief Cleans up resources. + */ + virtual ~QueryExecutorStep(); + + /** + * @brief Initializes step with a pointer to calling executor and a common context. + * @param queryExecutor Calling query executor. + * @param context Common context, shared among query executor and all steps. + * + * This method also calls virtual method init(), which can be used to one-time setup + * in derived step. + */ + void init(QueryExecutor* queryExecutor, QueryExecutor::Context* context); + + /** + * @brief Executes step routines. + * @return result of execution - successful or failed. + * + * The exec() method should execute all necessary routines required by this step. + * If it needs to execute anything on the database + * + * Once the false is returned by exec(), there should be no signal emitted by the step. + */ + virtual bool exec() = 0; + + protected: + /** + * @brief Updates QueryExecutor::Context::processedQuery string. + * + * Goes through all queries in QueryExecutor::Context::parsedQueries and detokenizes + * their tokens, concatenatins all results into QueryExecutor::Context::processedQuery. + * + * This should be called every time tokens of any parsed query were modified + * and you want those changes to be reflected in the processed query. + * + * See QueryExecutor::Context::processedQuery for more details; + */ + void updateQueries(); + + /** + * @brief Generates unique name for result column alias. + * @return Unique name. + * + * When a step needs to add another column to results, it can use this method + * to make sure that the name (alias) of this column will be unique across + * other result columns. + * + * QueryExecutorColumn step makes sure that every result column processed + * by query executor gets its own unique alias name. + * + * The getNextColName() whould be used only once per result column. When forwarding + * the result column from inner select to outer select, use the same alias name + * ad in the inner select. + */ + QString getNextColName(); + + /** + * @brief Extracts SELECT object from parsed queries. + * @return Parsed SELECT, or null pointer. + * + * This is a helper method. Since most of the logic in steps is required in regards + * of the last SELECT statement in all queries, this method comes handy. + * + * If the last statement in executed queries is the SELECT, then this method + * returns parsed object of that statement, otherwise it returns null pointer. + */ + SqliteSelectPtr getSelect(); + + /** + * @brief Custom initialization of the step. + * + * If a step needs some initial code to be executed, or some members to be initialized, + * this is the place to put it into. + */ + virtual void init(); + + /** + * @brief Puts the SELECT as a subselect. + * @param selectTokens All tokens of the original SELECT. + * @param resultColumnsTokens Result columns tokens for the new SELECT. + * @return New SELECT tokens. + * + * Original SELECT tokens are put into subselect of the new SELECT statement. New SELECT statement + * is built using given \p resultColumnTokens. + */ + TokenList wrapSelect(const TokenList& selectTokens, const TokenList& resultColumnsTokens); + + /** + * @brief Pointer to the calling executor. + */ + QueryExecutor* queryExecutor = nullptr; + + /** + * @brief Pointer to a shared context for all steps. + */ + QueryExecutor::Context* context = nullptr; + + /** + * @brief Database that all queries will be executed on. + * + * Defined by init(). + */ + Db* db = nullptr; + + /** + * @brief SQLite dialect of the database. + * + * Defined by init(). + */ + Dialect dialect; +}; + +#endif // QUERYEXECUTORSTEP_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorvaluesmode.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorvaluesmode.cpp new file mode 100644 index 0000000..8807178 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorvaluesmode.cpp @@ -0,0 +1,26 @@ +#include "queryexecutorvaluesmode.h" + +bool QueryExecutorValuesMode::exec() +{ + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + return true; + + bool modified = false; + for (SqliteSelect::Core* core : select->coreSelects) + { + if (core->valuesMode) + { + core->valuesMode = false; + modified = true; + } + } + + if (modified) + { + select->rebuildTokens(); + updateQueries(); + } + + return true; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorvaluesmode.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorvaluesmode.h new file mode 100644 index 0000000..4037a5b --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorvaluesmode.h @@ -0,0 +1,14 @@ +#ifndef QUERYEXECUTORVALUESMODE_H +#define QUERYEXECUTORVALUESMODE_H + +#include "queryexecutorstep.h" + +class QueryExecutorValuesMode : public QueryExecutorStep +{ + Q_OBJECT + + public: + bool exec(); +}; + +#endif // QUERYEXECUTORVALUESMODE_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorwrapdistinctresults.cpp b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorwrapdistinctresults.cpp new file mode 100644 index 0000000..2565c2c --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorwrapdistinctresults.cpp @@ -0,0 +1,42 @@ +#include "queryexecutorwrapdistinctresults.h" + +bool QueryExecutorWrapDistinctResults::exec() +{ + SqliteSelectPtr select = getSelect(); + if (!select || select->explain) + return true; + + SqliteSelect::Core* core = select->coreSelects.first(); + + if (core->distinctKw || core->groupBy.size() > 0) + wrapSelect(select.data()); + + return true; +} + +void QueryExecutorWrapDistinctResults::wrapSelect(SqliteSelect* select) +{ + // Trim tokens from right side + TokenList origTokens = select->tokens; + origTokens.trimRight(); + + // Remove ; operator if present at the end + while (origTokens.last()->type == Token::OPERATOR && origTokens.last()->value == ";") + origTokens.removeLast(); + + // Create new list + TokenList tokens; + tokens << TokenPtr::create(Token::KEYWORD, "SELECT"); + tokens << TokenPtr::create(Token::SPACE, " "); + tokens << TokenPtr::create(Token::OPERATOR, "*"); + tokens << TokenPtr::create(Token::SPACE, " "); + tokens << TokenPtr::create(Token::KEYWORD, "FROM"); + tokens << TokenPtr::create(Token::SPACE, " "); + tokens << TokenPtr::create(Token::OPERATOR, "("); + tokens += origTokens; + tokens << TokenPtr::create(Token::OPERATOR, ")"); + tokens << TokenPtr::create(Token::OPERATOR, ";"); + + select->tokens = tokens; + updateQueries(); +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorwrapdistinctresults.h b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorwrapdistinctresults.h new file mode 100644 index 0000000..eeb94ef --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/queryexecutorsteps/queryexecutorwrapdistinctresults.h @@ -0,0 +1,33 @@ +#ifndef QUERYEXECUTORWRAPDISTINCTRESULTS_H +#define QUERYEXECUTORWRAPDISTINCTRESULTS_H + +#include "queryexecutorstep.h" + +/** + * @brief The QueryExecutorWrapDistinctResults class + * + * This step is necessary for DISTINCT and GROUP BY selects, + * because result columns are always limited by substr() function, + * which makes INT and TEXT types of data to be the same, which is false. + * For those cases, we need to put DISTINCT/GROUP BY into subselect, + * so it works on original result columns, then get the substr() from them. + * + * Processed query is stored in QueryExecutor::Context::processedQuery. + */ +class QueryExecutorWrapDistinctResults : public QueryExecutorStep +{ + Q_OBJECT + public: + bool exec(); + + private: + /** + * @brief Wraps single SELECT statement. + * @param select SELECT to wrap. + * + * Puts given \p select as subselect of a new SELECT. + */ + void wrapSelect(SqliteSelect* select); +}; + +#endif // QUERYEXECUTORWRAPDISTINCTRESULTS_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorcodes.cpp b/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorcodes.cpp new file mode 100644 index 0000000..f18a785 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorcodes.cpp @@ -0,0 +1,13 @@ +#include "db/sqlerrorcodes.h" + +// This is defined in sqlite3.h, but we don't want to make a dependency +// from coreSQLiteStudio to sqlite3.h just for this one constraint, +// while the coreSQLiteStudio doesn't depend on sqlite lib at all. +// This should not change anyway. It's a documented value. +#define SQLITE_INTERRUPT 9 + +bool SqlErrorCode::isInterrupted(int errorCode) +{ + // Luckily SQLITE_INTERRUPT has the same value defined in both SQLite versions. + return (errorCode == SqlErrorCode::INTERRUPTED || errorCode == SQLITE_INTERRUPT); +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorcodes.h b/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorcodes.h new file mode 100644 index 0000000..12849cf --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorcodes.h @@ -0,0 +1,39 @@ +#ifndef SQLERRORCODES_H +#define SQLERRORCODES_H + +#include "coreSQLiteStudio_global.h" + +/** + * @brief Custom SQL error codes. + * + * Those are custom error codes that can be returned by SqlResults::getErrorCode(). + * Usually error codes come from SQLite itself, but some errors can be generated + * by SQLiteStudio and for those cases this enum lists possible codes. + * + * Codes in this enum are not conflicting with error codes returned from SQLite. + */ +struct API_EXPORT SqlErrorCode +{ + enum + { + DB_NOT_OPEN = -1000, /**< Database was not open */ + QUERY_EXECUTOR_ERROR = -1001, /**< QueryExecutor error (its sophisticated logic encountered some problem) */ + PARSER_ERROR = -1002, /**< Parser class could not parse the query, because it was either invalid SQL, or bug in the Parser */ + INTERRUPTED = -1003, /**< Query execution was interrupted */ + INVALID_ARGUMENT = -1004, /**< Passed query argument was invalid (out of range, invalid format, etc.) */ + DB_NOT_DEFINED = -1005, /**< Database was not defined */ + OTHER_EXECUTION_ERROR = -1006 /**< Identifies other execution errors, see error message for details */ + }; + + /** + * @brief Tests if given error code means that execution was interrupted. + * @param errorCode Error code to test. + * @return true if the code represents interruption, or false otherwise. + * + * This method checks both SqlErrorCode::INTERRUPTED and SQLITE_INTERRUPT values, + * so if the code is either of them, it returns true. + */ + static bool isInterrupted(int errorCode); +}; + +#endif // SQLERRORCODES_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorresults.cpp b/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorresults.cpp new file mode 100644 index 0000000..e7f6acd --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorresults.cpp @@ -0,0 +1,55 @@ +#include "sqlerrorresults.h" +#include "common/unused.h" + +SqlErrorResults::SqlErrorResults(int code, const QString& text) +{ + errText = text; + errCode = code; +} + +QString SqlErrorResults::getErrorText() +{ + return errText; +} + +int SqlErrorResults::getErrorCode() +{ + return errCode; +} + +QStringList SqlErrorResults::getColumnNames() +{ + return QStringList(); +} + +int SqlErrorResults::columnCount() +{ + return 0; +} + +qint64 SqlErrorResults::rowsAffected() +{ + return 0; +} + +SqlResultsRowPtr SqlErrorResults::nextInternal() +{ + return SqlResultsRowPtr(); +} + +bool SqlErrorResults::hasNextInternal() +{ + return false; +} + +bool SqlErrorResults::execInternal(const QList<QVariant>& args) +{ + UNUSED(args); + return false; +} + +bool SqlErrorResults::execInternal(const QHash<QString, QVariant>& args) +{ + UNUSED(args); + return false; +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorresults.h b/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorresults.h new file mode 100644 index 0000000..f837245 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/sqlerrorresults.h @@ -0,0 +1,48 @@ +#ifndef SQLERRORRESULTS_H +#define SQLERRORRESULTS_H + +#include "sqlquery.h" +#include <QStringList> + +/** + * @brief SqlResults implementation for returning errors. + * + * It's very simple implementation of SqlResults, which has hardcoded number of columns and rows (0). + * It has single constructor which accepts error code and error message, which are later + * returned from getErrorCode() and getErrorText(). + */ +class SqlErrorResults : public SqlQuery +{ + public: + /** + * @brief Creates error results with given code and message. + * @param code Error code. + * @param text Error message. + */ + SqlErrorResults(int code, const QString &text); + + QString getErrorText(); + int getErrorCode(); + QStringList getColumnNames(); + int columnCount(); + qint64 rowsAffected(); + + protected: + SqlResultsRowPtr nextInternal(); + bool hasNextInternal(); + bool execInternal(const QList<QVariant>& args); + bool execInternal(const QHash<QString, QVariant>& args); + + private: + /** + * @brief Error message passed in constructor. + */ + QString errText; + + /** + * @brief errCode Error code passed in constructor. + */ + int errCode; +}; + +#endif // SQLERRORRESULTS_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/sqlquery.cpp b/SQLiteStudio3/coreSQLiteStudio/db/sqlquery.cpp new file mode 100644 index 0000000..4217711 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/sqlquery.cpp @@ -0,0 +1,142 @@ +#include "sqlquery.h" +#include "db/sqlerrorcodes.h" + +SqlQuery::~SqlQuery() +{ +} + +bool SqlQuery::execute() +{ + if (queryArgs.type() == QVariant::Hash) + return execInternal(queryArgs.toHash()); + else + return execInternal(queryArgs.toList()); +} + +SqlResultsRowPtr SqlQuery::next() +{ + if (preloaded) + { + if (preloadedRowIdx >= preloadedData.size()) + return SqlResultsRowPtr(); + + return preloadedData[preloadedRowIdx++]; + } + return nextInternal(); +} + +bool SqlQuery::hasNext() +{ + if (preloaded) + return (preloadedRowIdx < preloadedData.size()); + + return hasNextInternal(); +} + +qint64 SqlQuery::rowsAffected() +{ + return affected; +} + +QList<SqlResultsRowPtr> SqlQuery::getAll() +{ + if (!preloaded) + preload(); + + return preloadedData; +} + +void SqlQuery::preload() +{ + if (preloaded) + return; + + QList<SqlResultsRowPtr> allRows; + while (hasNextInternal()) + allRows << nextInternal(); + + preloadedData = allRows; + preloaded = true; + preloadedRowIdx = 0; +} + +QVariant SqlQuery::getSingleCell() +{ + SqlResultsRowPtr row = next(); + if (row.isNull()) + return QVariant(); + + return row->value(0); +} + +bool SqlQuery::isError() +{ + return getErrorCode() != 0; +} + +bool SqlQuery::isInterrupted() +{ + return SqlErrorCode::isInterrupted(getErrorCode()); +} + +RowId SqlQuery::getInsertRowId() +{ + return insertRowId; +} + +qint64 SqlQuery::getRegularInsertRowId() +{ + return insertRowId["ROWID"].toLongLong(); +} + +QString SqlQuery::getQuery() const +{ + return query; +} + +void SqlQuery::setFlags(Db::Flags flags) +{ + this->flags = flags; +} + +void SqlQuery::clearArgs() +{ + queryArgs = QVariant(); +} + +void SqlQuery::setArgs(const QList<QVariant>& args) +{ + queryArgs = args; +} + +void SqlQuery::setArgs(const QHash<QString, QVariant>& args) +{ + queryArgs = args; +} + + +void RowIdConditionBuilder::setRowId(const RowId& rowId) +{ + static const QString argTempalate = QStringLiteral(":rowIdArg%1"); + + QString arg; + QHashIterator<QString,QVariant> it(rowId); + int i = 0; + while (it.hasNext()) + { + it.next(); + arg = argTempalate.arg(i++); + queryArgs[arg] = it.value(); + conditions << it.key() + " = " + arg; + } +} + +const QHash<QString, QVariant>& RowIdConditionBuilder::getQueryArgs() +{ + return queryArgs; +} + +QString RowIdConditionBuilder::build() +{ + return conditions.join(" AND "); +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/sqlquery.h b/SQLiteStudio3/coreSQLiteStudio/db/sqlquery.h new file mode 100644 index 0000000..a7610fa --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/sqlquery.h @@ -0,0 +1,323 @@ +#ifndef SQLQUERY_H +#define SQLQUERY_H + +#include "coreSQLiteStudio_global.h" +#include "db/db.h" +#include "db/sqlresultsrow.h" +#include <QList> +#include <QSharedPointer> + +/** @file */ + +/** + * @brief Row ID of the row in any table. + * + * It's a advanced ROWID container that can hold either simple integer ROWID, + * or a set of column values reflecting one or more PRIMARY KEY columns. + * + * This way it RowId can be applied to both regular tables, as well as "WITHOUT ROWID" tables. + * + * Each entry in the RowId has a key and a value (after all it's a QHash). Keys are names + * of the column and values are values in that columns. For regular tables the RowId + * will contain exactly one entry: <tt>ROWID -> some_integer</tt> + */ +typedef QHash<QString,QVariant> RowId; + +/** + * @brief SQL query to execute and get results from + * + * This object is created by and returned from Db::exec() (and familiar methods) + * or Db::prepare() calls. + * It uses incremental reading for accessing data, so it only reads as much data + * as you ask it to. It can tell you how many rows and how many columns are available + * in the results. It also provides information about errors that occured during query execution. + * + * Typical workflow looks like this: + * @code + * SqlQueryPtr results = db->exec("SELECT * FROM table"); + * SqlResultsRowPtr row; + * while (row = results->next()) + * { + * qDebug() << row->valueList(); + * } + * @endcode + * + * To re-use compiled query, use it like this: + * @code + * SqlQueryPtr query = db->prepare("SELECT * FROM table WHERE id BETWEEN ? AND ?"); + * SqlResultsRowPtr row; + * + * query->setArgs({5, 10}); + * query->execute(); + * while (row = query->next()) + * { + * qDebug() << row->valueList(); + * } + * + * query->setArgs({1, 3}); + * query->execute(); + * while (row = query->next()) + * { + * qDebug() << row->valueList(); + * } + * @endcode + */ +class API_EXPORT SqlQuery +{ + public: + /** + * @brief Releases result resources. + */ + virtual ~SqlQuery(); + + /** + * @brief Executes or re-executes prepared query. + * @return true on success, false on failure. + * + * You can ignore result of this method and check for error later with isError(). + */ + virtual bool execute(); + + /** + * @brief Reads next row of results + * @return Next results row, or null pointer if no more rows are available. + */ + SqlResultsRowPtr next(); + + /** + * @brief Tells if there is next row available. + * @return true if there's next row, of false if there's not. + * + * If you just want to iterate through rows, you don't need to call this method. + * The next() method will return null pointer if there is no next row available, + * so you can tell when to stop iterating. Furthermore, you should avoid + * calling this method just for iterating through rows, because next() method + * already does that internally (in most implementations). + * + * In other cases this method might be useful. For example when you read single cell: + * @code + * SqlQueryPtr results = db->("SELECT value FROM table WHERE rowid = ?", {rowId}); + * if (results->isError() || !results->hasNext()) + * return "some default value"; + * + * return results->getSingleCell().toString(); + * @endcode + */ + bool hasNext(); + + /** + * @brief Gets error test of the most recent error. + * @return Error text. + */ + virtual QString getErrorText() = 0; + + /** + * @brief Gets error code of the most recent error. + * @return Error code as returned from DbPlugin. + */ + virtual int getErrorCode() = 0; + + /** + * @brief Gets list of column names in the results. + * @return List of column names. + */ + virtual QStringList getColumnNames() = 0; + + /** + * @brief Gets number of columns in the results. + * @return Columns count. + */ + virtual int columnCount() = 0; + + /** + * @brief Gets number of rows that were affected by the query. + * @return Number of rows affected. + * + * For SELECT statements this is number of returned rows. + * For UPDATE this is number of rows updated. + * For DELETE this is number of rows deleted. + * FOR INSERT this is number of rows inserted (starting with SQLite 3.7.11 you can insert multiple rows with single INSERT statement). + */ + virtual qint64 rowsAffected(); + + /** + * @brief Reads all rows immediately and returns them. + * @return All data rows as a list. + * + * Don't use this method against huge data results. + */ + virtual QList<SqlResultsRowPtr> getAll(); + + /** + * @brief Loads all data immediately into memory. + * + * This method makes sense only if you plan to use getAll() later on. + * If you won't use getAll(), then calling this method is just a waste of memory. + * + * It is useful if you execute query asynchronously and you will be using all results. + * In that case the asynchronous execution takes care of loading data from the database and the final code + * just operates on in-memory data. + */ + virtual void preload(); + + /** + * @brief Reads first column of first row and returns its value. + * @return Value read. + * + * This method is useful when dealing with for example PRAGMA statement results, + * or for SELECT queries with expected single row and single column. + */ + virtual QVariant getSingleCell(); + + /** + * @brief Tells if there was an error while query execution. + * @return true if there was an error, false otherwise. + */ + virtual bool isError(); + + /** + * @brief Tells if the query execution was interrupted. + * @return true if query was interrupted, or false otherwise. + * + * Interruption of execution is interpreted as an execution error, + * so if this method returns true, then isError() will return true as well. + */ + virtual bool isInterrupted(); + + /** + * @brief Retrieves ROWID of the INSERT'ed row. + * @return ROWID as 64-bit signed integer or set of multiple columns. If empty, then there was no row inserted. + * @see RowId + * @see getRegularInsertRowId() + */ + virtual RowId getInsertRowId(); + + /** + * @brief Retrieves ROWID of the INSERT'ed row. + * @return ROWID as 64-bit signed integer. + * + * This is different from getInsertRowId(), because it assumes that the insert was made to a regular table, + * while getInsertRowId() supports also inserts to WITHOUT ROWID tables. + * + * If you know that the insert was made to a regular table, you can use this method to simply get the ROWID. + */ + virtual qint64 getRegularInsertRowId(); + + /** + * @brief columnAsList + * @tparam T Data type to use for the result list. + * @param name name of the column to get values from. + * @return List of all values from given column. + */ + template <class T> + QList<T> columnAsList(const QString& name) + { + QList<T> list; + SqlResultsRowPtr row; + while (hasNext()) + { + row = next(); + list << row->value(name).value<T>(); + } + return list; + } + + /** + * @brief columnAsList + * @tparam T Data type to use for the result list. + * @param index Index of the column to get values from (must be between 0 and columnCount()-1). + * @return List of all values from given column. + */ + template <class T> + QList<T> columnAsList(int index) + { + QList<T> list; + if (index < 0 || index >= columnCount()) + return list; + + SqlResultsRowPtr row; + while (hasNext()) + { + row = next(); + list << row->value(index).value<T>(); + } + return list; + } + + QString getQuery() const; + void setFlags(Db::Flags flags); + void clearArgs(); + void setArgs(const QList<QVariant>& args); + void setArgs(const QHash<QString,QVariant>& args); + + protected: + /** + * @brief Reads next row of results + * @return Next results row, or null pointer if no more rows are available. + * + * This is pretty much the same as next(), except next() handles preloaded data, + * while this method should work natively on the derived implementation of results object. + */ + virtual SqlResultsRowPtr nextInternal() = 0; + + /** + * @brief Tells if there is next row available. + * @return true if there's next row, of false if there's not. + * + * This is pretty much the same as hasNext(), except hasNext() handles preloaded data, + * while this method should work natively on the derived implementation of results object. + */ + virtual bool hasNextInternal() = 0; + + virtual bool execInternal(const QList<QVariant>& args) = 0; + virtual bool execInternal(const QHash<QString, QVariant>& args) = 0; + + /** + * @brief Row ID of the most recently inserted row. + */ + RowId insertRowId; + + /** + * @brief Flag indicating if the data was preloaded with preload(). + */ + bool preloaded = false; + + /** + * @brief Index of the next row to be returned. + * + * If the data was preloaded (see preload()), then iterating with next() whould use this index to find out + * which preloaded row should be returned next. + */ + int preloadedRowIdx = -1; + + /** + * @brief Data preloaded with preload(). + */ + QList<SqlResultsRowPtr> preloadedData; + + int affected = 0; + + QString query; + QVariant queryArgs; + Db::Flags flags; +}; + +class API_EXPORT RowIdConditionBuilder +{ + public: + void setRowId(const RowId& rowId); + const QHash<QString,QVariant>& getQueryArgs(); + QString build(); + + protected: + QStringList conditions; + QHash<QString,QVariant> queryArgs; +}; + +/** + * @brief Shared pointer to query object. + * Results are usually passed as shared pointer, so it's used as needed and deleted when no longer required. + */ +typedef QSharedPointer<SqlQuery> SqlQueryPtr; + +#endif // SQLQUERY_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/sqlresultsrow.cpp b/SQLiteStudio3/coreSQLiteStudio/db/sqlresultsrow.cpp new file mode 100644 index 0000000..bb5c6e2 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/sqlresultsrow.cpp @@ -0,0 +1,42 @@ +#include "sqlresultsrow.h" + +SqlResultsRow::SqlResultsRow() +{ +} + +SqlResultsRow::~SqlResultsRow() +{ +} + +const QVariant SqlResultsRow::value(const QString &key) const +{ + return valuesMap[key]; +} + +const QHash<QString, QVariant> &SqlResultsRow::valueMap() const +{ + return valuesMap; +} + +const QList<QVariant>& SqlResultsRow::valueList() const +{ + return values; +} + +const QVariant SqlResultsRow::value(int idx) const +{ + if (idx < 0 || idx >= values.size()) + return QVariant(); + + return values[idx]; +} + +bool SqlResultsRow::contains(const QString &key) const +{ + return valuesMap.contains(key); +} + +bool SqlResultsRow::contains(int idx) const +{ + return idx >= 0 && idx < values.size(); +} diff --git a/SQLiteStudio3/coreSQLiteStudio/db/sqlresultsrow.h b/SQLiteStudio3/coreSQLiteStudio/db/sqlresultsrow.h new file mode 100644 index 0000000..2a5e17c --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/sqlresultsrow.h @@ -0,0 +1,102 @@ +#ifndef SQLRESULTSROW_H +#define SQLRESULTSROW_H + +#include "coreSQLiteStudio_global.h" +#include <QVariant> +#include <QList> +#include <QHash> +#include <QSharedPointer> + +/** @file */ + +/** + * @brief SQL query results row. + * + * Single row of data from SQL query results. It already has all columns stored in memory, + * so it doesn't matter if you read only one column, or all columns available in the row. + * + * You will never encounter object of exactly this class, as it has protected constructor + * and has no methods to populate internal data members. Instead of creating objects of this class, + * other class inherits it and handles populating internal data members, then this class + * is just an interface to read data from it. + * + * In other words, it's kind of an abstract class. + */ +class API_EXPORT SqlResultsRow +{ + public: + /** + * @brief Releases resources. + */ + virtual ~SqlResultsRow(); + + /** + * @brief Gets value for given column. + * @param key Column name. + * @return Value from requested column. If column name is invalid, the invalid QVariant is returned. + */ + const QVariant value(const QString& key) const; + + /** + * @brief Gets value for given column. + * @param idx 0-based index of column. + * @return Value from requested column. If index was invalid, the invalid QVariant is returned. + */ + const QVariant value(int idx) const; + + /** + * @brief Gets table of column->value entries. + * @return Hash table with column names as keys and QVariants as their values. + * + * Note, that QHash doesn't guarantee order of entries. If you want to iterate through columns + * in order they were returned from the database, use valueList(), or iterate through SqlResults::getColumnNames() + * and use it to call value(). + */ + const QHash<QString, QVariant>& valueMap() const; + + /** + * @brief Gets list of values in this row. + * @return Ordered list of values in the row. + * + * Note, that this method returns values in order they were returned from database. + */ + const QList<QVariant>& valueList() const; + + /** + * @brief Tests if the row contains given column name. + * @param key Column name. Case sensitive. + * @return true if column exists in the row, or false otherwise. + */ + bool contains(const QString& key) const; + + /** + * @brief Tests if the row has column indexed with given number. + * @param idx 0-based index to test. + * @return true if index is in range of existing columns, or false if it's greater than "number of columns - 1", or if it's less than 0. + */ + bool contains(int idx) const; + + protected: + SqlResultsRow(); + + /** + * @brief Columns and their values in the row. + */ + QHash<QString,QVariant> valuesMap; + /** + * @brief Ordered list of values in the row. + * + * Technical note: + * We keep list of values next to valuesMap, so we have it in the same order as column names when asked by valueList(). + * This looks like having redundant data storage, but Qt container classes (such as QVariant) + * use smart pointers to keep their data internally, so here we actually keep only reference objects. + */ + QList<QVariant> values; +}; + +/** + * @brief Shared pointer to SQL query results row. + */ +typedef QSharedPointer<SqlResultsRow> SqlResultsRowPtr; + +#endif // SQLRESULTSROW_H diff --git a/SQLiteStudio3/coreSQLiteStudio/db/stdsqlite3driver.h b/SQLiteStudio3/coreSQLiteStudio/db/stdsqlite3driver.h new file mode 100644 index 0000000..eff3621 --- /dev/null +++ b/SQLiteStudio3/coreSQLiteStudio/db/stdsqlite3driver.h @@ -0,0 +1,83 @@ +#ifndef STDSQLITE3DRIVER_H +#define STDSQLITE3DRIVER_H + +#define STD_SQLITE3_DRIVER(Name, Label, Prefix, UppercasePrefix) \ + struct Name \ + { \ + static_char* label = Label; \ + \ + static const int OK = UppercasePrefix##SQLITE_OK; \ + static const int ERROR = UppercasePrefix##SQLITE_ERROR; \ + static const int OPEN_READWRITE = UppercasePrefix##SQLITE_OPEN_READWRITE; \ + static const int OPEN_CREATE = UppercasePrefix##SQLITE_OPEN_CREATE; \ + static const int UTF8 = UppercasePrefix##SQLITE_UTF8; \ + static const int INTEGER = UppercasePrefix##SQLITE_INTEGER; \ + static const int FLOAT = UppercasePrefix##SQLITE_FLOAT; \ + static const int NULL_TYPE = UppercasePrefix##SQLITE_NULL; \ + static const int BLOB = UppercasePrefix##SQLITE_BLOB; \ + static const int MISUSE = UppercasePrefix##SQLITE_MISUSE; \ + static const int BUSY = UppercasePrefix##SQLITE_BUSY; \ + static const int ROW = UppercasePrefix##SQLITE_ROW; \ + static const int DONE = UppercasePrefix##SQLITE_DONE; \ + \ + typedef Prefix##sqlite3 handle; \ + typedef Prefix##sqlite3_stmt stmt; \ + typedef Prefix##sqlite3_context context; \ + typedef Prefix##sqlite3_value value; \ + typedef Prefix##sqlite3_int64 int64; \ + typedef Prefix##sqlite3_destructor_type destructor_type; \ + \ + static destructor_type TRANSIENT() {return UppercasePrefix##SQLITE_TRANSIENT;} \ + static void interrupt(handle* arg) {Prefix##sqlite3_interrupt(arg);} \ + static const void *value_blob(value* arg) {return Prefix##sqlite3_value_blob(arg);} \ + static double value_double(value* arg) {return Prefix##sqlite3_value_double(arg);} \ + static int64 value_int64(value* arg) {return Prefix##sqlite3_value_int64(arg);} \ + static const void *value_text16(value* arg) {return Prefix##sqlite3_value_text16(arg);} \ + static int value_bytes(value* arg) {return Prefix##sqlite3_value_bytes(arg);} \ + static int value_bytes16(value* arg) {return Prefix##sqlite3_value_bytes16(arg);} \ + static int value_type(value* arg) {return Prefix##sqlite3_value_type(arg);} \ + static int bind_blob(stmt* a1, int a2, const void* a3, int a4, void(*a5)(void*)) {return Prefix##sqlite3_bind_blob(a1, a2, a3, a4, a5);} \ + static int bind_double(stmt* a1, int a2, double a3) {return Prefix##sqlite3_bind_double(a1, a2, a3);} \ + static int bind_int(stmt* a1, int a2, int a3) {return Prefix##sqlite3_bind_int(a1, a2, a3);} \ + static int bind_int64(stmt* a1, int a2, int64 a3) {return Prefix##sqlite3_bind_int64(a1, a2, a3);} \ + static int bind_null(stmt* a1, int a2) {return Prefix##sqlite3_bind_null(a1, a2);} \ + static int bind_text16(stmt* a1, int a2, const void* a3, int a4, void(*a5)(void*)) {return Prefix##sqlite3_bind_text16(a1, a2, a3, a4, a5);} \ + static void result_blob(context* a1, const void* a2, int a3, void(*a4)(void*)) {Prefix##sqlite3_result_blob(a1, a2, a3, a4);} \ + static void result_double(context* a1, double a2) {Prefix##sqlite3_result_double(a1, a2);} \ + static void result_error16(context* a1, const void* a2, int a3) {Prefix##sqlite3_result_error16(a1, a2, a3);} \ + static void result_int(context* a1, int a2) {Prefix##sqlite3_result_int(a1, a2);} \ + static void result_int64(context* a1, int64 a2) {Prefix##sqlite3_result_int64(a1, a2);} \ + static void result_null(context* a1) {Prefix##sqlite3_result_null(a1);} \ + static void result_text16(context* a1, const void* a2, int a3, void(*a4)(void*)) {Prefix##sqlite3_result_text16(a1, a2, a3, a4);} \ + static int open_v2(const char *a1, handle **a2, int a3, const char *a4) {return Prefix##sqlite3_open_v2(a1, a2, a3, a4);} \ + static int finalize(stmt *arg) {return Prefix##sqlite3_finalize(arg);} \ + static const char *errmsg(handle* arg) {return Prefix##sqlite3_errmsg(arg);} \ + static int extended_errcode(handle* arg) {return Prefix##sqlite3_extended_errcode(arg);} \ + static const void *column_blob(stmt* arg1, int arg2) {return Prefix##sqlite3_column_blob(arg1, arg2);} \ + static int column_bytes(stmt* arg1, int arg2) {return Prefix##sqlite3_column_bytes(arg1, arg2);} \ + static int column_bytes16(stmt* arg1, int arg2) {return Prefix##sqlite3_column_bytes16(arg1, arg2);} \ + static double column_double(stmt* arg1, int arg2) {return Prefix##sqlite3_column_double(arg1, arg2);} \ + static int64 column_int64(stmt* arg1, int arg2) {return Prefix##sqlite3_column_int64(arg1, arg2);} \ + static const void *column_text16(stmt* arg1, int arg2) {return Prefix##sqlite3_column_text16(arg1, arg2);} \ + static const char *column_name(stmt* arg1, int arg2) {return Prefix##sqlite3_column_name(arg1, arg2);} \ + static int column_type(stmt* arg1, int arg2) {return Prefix##sqlite3_column_type(arg1, arg2);} \ + static int column_count(stmt* arg1) {return Prefix##sqlite3_column_count(arg1);} \ + static int changes(handle* arg) {return Prefix##sqlite3_changes(arg);} \ + static int last_insert_rowid(handle* arg) {return Prefix##sqlite3_last_insert_rowid(arg);} \ + static int step(stmt* arg) {return Prefix##sqlite3_step(arg);} \ + static int reset(stmt* arg) {return Prefix##sqlite3_reset(arg);} \ + static int close(handle* arg) {return Prefix##sqlite3_close(arg);} \ + static int enable_load_extension(handle* arg1, int arg2) {return Prefix##sqlite3_enable_load_extension(arg1, arg2);} \ + static void* user_data(context* arg) {return Prefix##sqlite3_user_data(arg);} \ + static void* aggregate_context(context* arg1, int arg2) {return Prefix##sqlite3_aggregate_context(arg1, arg2);} \ + static int collation_needed(handle* a1, void* a2, void(*a3)(void*,handle*,int eTextRep,const char*)) {return Prefix##sqlite3_collation_needed(a1, a2, a3);} \ + static int prepare_v2(handle *a1, const char *a2, int a3, stmt **a4, const char **a5) {return Prefix##sqlite3_prepare_v2(a1, a2, a3, a4, a5);} \ + static int create_function(handle *a1, const char *a2, int a3, int a4, void *a5, void (*a6)(context*,int,value**), void (*a7)(context*,int,value**), void (*a8)(context*)) \ + {return Prefix##sqlite3_create_function(a1, a2, a3, a4, a5, a6, a7, a8);} \ + static int create_function_v2(handle *a1, const char *a2, int a3, int a4, void *a5, void (*a6)(context*,int,value**), void (*a7)(context*,int,value**), void (*a8)(context*), void(*a9)(void*)) \ + {return Prefix##sqlite3_create_function_v2(a1, a2, a3, a4, a5, a6, a7, a8, a9);} \ + static int create_collation_v2(handle* a1, const char *a2, int a3, void *a4, int(*a5)(void*,int,const void*,int,const void*), void(*a6)(void*)) \ + {return Prefix##sqlite3_create_collation_v2(a1, a2, a3, a4, a5, a6);} \ + }; + +#endif // STDSQLITE3DRIVER_H |