From 1fdc150116cad39aae5c5da407c3312b47a59e3a Mon Sep 17 00:00:00 2001 From: Unit 193 Date: Fri, 17 Dec 2021 07:06:30 -0500 Subject: New upstream version 3.3.3+dfsg1. --- SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp | 232 +++++++++++++++++----- 1 file changed, 183 insertions(+), 49 deletions(-) (limited to 'SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp') diff --git a/SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp b/SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp index 62a685c..4d65461 100644 --- a/SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp +++ b/SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp @@ -16,11 +16,12 @@ const char* sqliteTempMasterDdl = "CREATE TABLE sqlite_temp_master (type text, name text, tbl_name text, rootpage integer, sql text)"; ExpiringCache SchemaResolver::cache; +ExpiringCache SchemaResolver::autoIndexDdlCache; SchemaResolver::SchemaResolver(Db *db) : db(db) { - parser = new Parser(db->getDialect()); + parser = new Parser(); } SchemaResolver::~SchemaResolver() @@ -39,9 +40,26 @@ QStringList SchemaResolver::getTables(const QString &database) QStringList SchemaResolver::getIndexes(const QString &database) { - QStringList indexes = getObjects(database, "index"); - if (ignoreSystemObjects) - filterSystemIndexes(indexes); + static_qstring(idxForTableTpl, "SELECT name FROM %1.pragma_index_list(%2)"); + + QStringList tables = getTables(database); + QStringList queryParts; + for (const QString& table : tables) + queryParts << idxForTableTpl.arg(wrapObjName(database), wrapString(table)); + + QString query = queryParts.join(" UNION "); + SqlQueryPtr results = db->exec(query, dbFlags); + + QStringList indexes; + QString value; + for (SqlResultsRowPtr row : results->getAll()) + { + value = row->value(0).toString(); + if (isFilteredOut(value, "index")) + continue; + + indexes << value; + } return indexes; } @@ -58,8 +76,17 @@ QStringList SchemaResolver::getViews(const QString &database) StrHash SchemaResolver::getGroupedIndexes(const QString &database) { - QStringList allIndexes = getIndexes(database); - return getGroupedObjects(database, allIndexes, SqliteQueryType::CreateIndex); + StrHash indexesWithTables = getIndexesWithTables(database); + + StrHash groupedIndexes; + auto it = indexesWithTables.iterator(); + while (it.hasNext()) + { + auto entry = it.next(); + groupedIndexes[entry.value()] << entry.key(); + } + + return groupedIndexes; } StrHash SchemaResolver::getGroupedTriggers(const QString &database) @@ -68,10 +95,10 @@ StrHash SchemaResolver::getGroupedTriggers(const QString &database) return getGroupedObjects(database, allTriggers, SqliteQueryType::CreateTrigger); } -StrHash< QStringList> SchemaResolver::getGroupedObjects(const QString &database, const QStringList &inputList, SqliteQueryType type) +StrHash SchemaResolver::getGroupedObjects(const QString &database, const QStringList &inputList, SqliteQueryType type) { QString strType = sqliteQueryTypeToString(type); - StrHash< QStringList> groupedTriggers; + StrHash groupedObjects; SqliteQueryPtr parsedQuery; SqliteTableRelatedDdlPtr tableRelatedDdl; @@ -93,10 +120,10 @@ StrHash< QStringList> SchemaResolver::getGroupedObjects(const QString &database, continue; } - groupedTriggers[tableRelatedDdl->getTargetTable()] << object; + groupedObjects[tableRelatedDdl->getTargetTable()] << object; } - return groupedTriggers; + return groupedObjects; } bool SchemaResolver::isFilteredOut(const QString& value, const QString& type) @@ -106,7 +133,7 @@ bool SchemaResolver::isFilteredOut(const QString& value, const QString& type) if (type == "table" && isSystemTable(value)) return true; - if (type == "index" && isSystemIndex(value, db->getDialect())) + if (type == "index" && isSystemIndex(value)) return true; } @@ -245,13 +272,12 @@ QList SchemaResolver::getViewColumnObjects(const QString SqliteCreateTablePtr SchemaResolver::virtualTableAsRegularTable(const QString &database, const QString &table) { - Dialect dialect = db->getDialect(); - QString strippedName = stripObjName(table, dialect); - QString dbName = getPrefixDb(database, dialect); + QString strippedName = stripObjName(table); + QString dbName = getPrefixDb(database); // Create temp table to see columns. QString newTable = db->getUniqueNewObjectName(strippedName); - QString origTable = wrapObjIfNeeded(strippedName, dialect); + QString origTable = wrapObjIfNeeded(strippedName); SqlQueryPtr tempTableRes = db->exec(QString("CREATE TEMP TABLE %1 AS SELECT * FROM %2.%3 LIMIT 0;").arg(newTable, dbName, origTable), dbFlags); if (tempTableRes->isError()) qWarning() << "Could not create temp table to identify virtual table columns of virtual table " << origTable << ". Error details:" << tempTableRes->getErrorText(); @@ -278,18 +304,19 @@ QString SchemaResolver::getObjectDdl(const QString& name, ObjectType type) QString SchemaResolver::getObjectDdl(const QString &database, const QString &name, ObjectType type) { if (name.isNull()) - return QString::null; + return QString(); + + // Prepare db prefix. + QString dbName = getPrefixDb(database); - Dialect dialect = db->getDialect(); // In case of sqlite_master or sqlite_temp_master we have static definitions - QString lowerName = stripObjName(name, dialect).toLower(); + QString lowerName = stripObjName(name).toLower(); if (lowerName == "sqlite_master") return getSqliteMasterDdl(false); else if (lowerName == "sqlite_temp_master") return getSqliteMasterDdl(true); - - // Prepare db prefix. - QString dbName = getPrefixDb(database, dialect); + else if (lowerName.startsWith("sqlite_autoindex_")) + return getSqliteAutoIndexDdl(dbName, stripObjName(name)); // Standalone or temp table? QString targetTable = "sqlite_master"; @@ -346,7 +373,7 @@ QString SchemaResolver::getObjectDdlWithDifficultName(const QString &dbName, con if (queryResults->isError()) { qDebug() << "Could not get object's DDL:" << dbName << "." << lowerName << ", details:" << queryResults->getErrorText(); - return QString::null; + return QString(); } // The DDL string @@ -387,7 +414,7 @@ QString SchemaResolver::getObjectDdlWithSimpleName(const QString &dbName, const if (queryResults->isError()) { qDebug() << "Could not get object's DDL:" << dbName << "." << lowerName << ", details:" << queryResults->getErrorText(); - return QString::null; + return QString(); } // The DDL string @@ -395,9 +422,38 @@ QString SchemaResolver::getObjectDdlWithSimpleName(const QString &dbName, const return results.toString(); } +StrHash SchemaResolver::getIndexesWithTables(const QString& database) +{ + static_qstring(idxForTableTpl, "SELECT %2 as tbl_name, name FROM %1.pragma_index_list(%2)"); + + QStringList tables = getTables(database); + QString dbName = getPrefixDb(database); + QStringList queryParts; + for (const QString& table : tables) + queryParts << idxForTableTpl.arg(wrapObjName(dbName), wrapString(table)); + + QString query = queryParts.join(" UNION "); + SqlQueryPtr results = db->exec(query, dbFlags); + + StrHash indexes; + QString tabName; + QString idxName; + for (SqlResultsRowPtr row : results->getAll()) + { + tabName = row->value("tbl_name").toString(); + idxName = row->value("name").toString(); + if (isFilteredOut(idxName, "index")) + continue; + + indexes[idxName] = tabName; + } + + return indexes; +} + QStringList SchemaResolver::getColumnsFromDdlUsingPragma(const QString& ddl) { - Parser parser(db->getDialect()); + Parser parser; if (!parser.parse(ddl) || parser.getQueries().isEmpty()) { qWarning() << "Could not parse DDL for determinating columns using PRAGMA. The DDL was:\n" << ddl; @@ -418,7 +474,7 @@ QStringList SchemaResolver::getColumnsFromDdlUsingPragma(const QString& ddl) QStringList SchemaResolver::getColumnsUsingPragma(const QString& tableOrView) { static_qstring(query, "PRAGMA table_info(%1)"); - SqlQueryPtr results = db->exec(query.arg(wrapObjIfNeeded(tableOrView, db->getDialect()))); + SqlQueryPtr results = db->exec(query.arg(wrapObjIfNeeded(tableOrView))); if (results->isError()) { qWarning() << "Could not get column list using PRAGMA for table or view:" << tableOrView << ", error was:" << results->getErrorText(); @@ -453,7 +509,7 @@ QStringList SchemaResolver::getColumnsUsingPragma(SqliteCreateTable* createTable QStringList columns = getColumnsUsingPragma(name); static_qstring(dropSql, "DROP TABLE %1"); - db->exec(dropSql.arg(wrapObjIfNeeded(name, db->getDialect()))); + db->exec(dropSql.arg(wrapObjIfNeeded(name))); return columns; } @@ -479,7 +535,7 @@ QStringList SchemaResolver::getColumnsUsingPragma(SqliteCreateView* createView) QStringList columns = getColumnsUsingPragma(name); static_qstring(dropSql, "DROP VIEW %1"); - db->exec(dropSql.arg(wrapObjIfNeeded(name, db->getDialect()))); + db->exec(dropSql.arg(wrapObjIfNeeded(name))); return columns; } @@ -507,7 +563,7 @@ StrHash< SqliteQueryPtr> SchemaResolver::getAllParsedObjects() StrHash< SqliteQueryPtr> SchemaResolver::getAllParsedObjects(const QString& database) { - return getAllParsedObjectsForType(database, QString::null); + return getAllParsedObjectsForType(database, QString()); } StrHash< SqliteCreateTablePtr> SchemaResolver::getAllParsedTables() @@ -550,6 +606,81 @@ StrHash< SqliteCreateViewPtr> SchemaResolver::getAllParsedViews(const QString& d return getAllParsedObjectsForType(database, "view"); } +QString SchemaResolver::getSqliteAutoIndexDdl(const QString& database, const QString& index) +{ + // First, let's try to use cached value + static_qstring(cacheKeyTpl, "%1.%2"); + QString cacheKey = cacheKeyTpl.arg(database, index).toLower(); + QString* cachedDdlPtr = autoIndexDdlCache[cacheKey]; + if (cachedDdlPtr) + return *(cachedDdlPtr); + + // Not in cache. We need to find out indexed table. + // Let's try to find it in sqlite_master. + // If it's there, we will at least know it's referenced table. + static_qstring(masterQuery, "SELECT tbl_name FROM %1.sqlite_master WHERE type = 'index' AND name = ?"); + + QString table; + QString dbName = getPrefixDb(database); + QVariant masterRes = db->exec(masterQuery.arg(dbName), {index}, dbFlags)->getSingleCell(); + if (masterRes.isNull()) + { + // Not lucky. We need to find out the table. + StrHash indexesWithTables = getIndexesWithTables(database); + auto it = indexesWithTables.iterator(); + while (it.hasNext()) + { + auto entry = it.next(); + if (entry.key().toLower() == index.toLower()) + { + table = entry.value(); + break; + } + } + } + else + table = masterRes.toString(); + + if (table.isNull()) + { + qCritical() << "Could not determin table associated with index" << database << "." << index; + return QString(); + } + + // Check the unique flag of the index + static_qstring(idxUniqueQueryTpl, "SELECT unique FROM %1.pragma_index_list(%2) WHERE name = ?"); + SqlQueryPtr uniqRes = db->exec(idxUniqueQueryTpl.arg(dbName, wrapString(table)), {index}, dbFlags); + bool unique = uniqRes->getSingleCell().toInt() > 0; + + // Now let's find out columns + static_qstring(idxColQueryTpl, "SELECT name, coll, desc FROM %1.pragma_index_xinfo(%2) WHERE key = 1"); + static_qstring(idxColTpl, "%1 COLLATE %2"); + + QStringList columns; + SqlQueryPtr colRes = db->exec(idxColQueryTpl.arg(dbName, wrapString(index)), dbFlags); + while (colRes->hasNext()) + { + SqlResultsRowPtr row = colRes->next(); + QString column = idxColTpl.arg(wrapObjIfNeeded(row->value("name").toString()), row->value("coll").toString()); + if (row->value("desc").toInt() > 0) + column += " DESC"; + + columns << column; + } + + // Finally, let's build it up & cache + static_qstring(ddlTpl, "CREATE %1INDEX %2 ON %3 (%4)"); + QString ddl = ddlTpl.arg( + unique ? "UNIQUE " : "", + wrapObjIfNeeded(index), + wrapObjIfNeeded(table), + columns.join(", ") + ); + + autoIndexDdlCache.insert(cacheKey, new QString(ddl)); + return ddl; +} + SqliteQueryPtr SchemaResolver::getParsedDdl(const QString& ddl) { if (!parser->parse(ddl)) @@ -558,6 +689,8 @@ SqliteQueryPtr SchemaResolver::getParsedDdl(const QString& ddl) for (ParserError* err : parser->getErrors()) qDebug() << err->getMessage(); + qDebug() << "The DDL is:" << ddl; + return SqliteQueryPtr(); } @@ -575,7 +708,7 @@ SqliteQueryPtr SchemaResolver::getParsedDdl(const QString& ddl) QStringList SchemaResolver::getObjects(const QString &type) { - return getObjects(QString::null, type); + return getObjects(QString(), type); } QStringList SchemaResolver::getObjects(const QString &database, const QString &type) @@ -586,7 +719,7 @@ QStringList SchemaResolver::getObjects(const QString &database, const QString &t return cache.object(key, true)->toStringList(); QStringList resList; - QString dbName = getPrefixDb(database, db->getDialect()); + QString dbName = getPrefixDb(database); SqlQueryPtr results = db->exec(QString("SELECT name FROM %1.sqlite_master WHERE type = ?;").arg(dbName), {type}, dbFlags); @@ -606,7 +739,7 @@ QStringList SchemaResolver::getObjects(const QString &database, const QString &t QStringList SchemaResolver::getAllObjects() { - return getAllObjects(QString::null); + return getAllObjects(QString()); } QStringList SchemaResolver::getAllObjects(const QString& database) @@ -617,7 +750,7 @@ QStringList SchemaResolver::getAllObjects(const QString& database) return cache.object(key, true)->toStringList(); QStringList resList; - QString dbName = getPrefixDb(database, db->getDialect()); + QString dbName = getPrefixDb(database); SqlQueryPtr results = db->exec(QString("SELECT name, type FROM %1.sqlite_master;").arg(dbName), dbFlags); @@ -661,10 +794,6 @@ QStringList SchemaResolver::getFkReferencingTables(const QString& table) QStringList SchemaResolver::getFkReferencingTables(const QString& database, const QString& table) { - Dialect dialect = db->getDialect(); - if (dialect == Dialect::Sqlite2) - return QStringList(); - // Get all tables StrHash parsedTables = getAllParsedTables(database); @@ -719,11 +848,23 @@ QStringList SchemaResolver::getFkReferencingTables(const QString& table, const Q QStringList SchemaResolver::getIndexesForTable(const QString& database, const QString& table) { - QStringList names; - for (SqliteCreateIndexPtr idx : getParsedIndexesForTable(database, table)) - names << idx->index; + static_qstring(idxForTableTpl, "SELECT name FROM %1.pragma_index_list(%2)"); - return names; + QString query = idxForTableTpl.arg(wrapObjName(database), wrapString(table)); + SqlQueryPtr results = db->exec(query, dbFlags); + + QStringList indexes; + QString value; + for (SqlResultsRowPtr row : results->getAll()) + { + value = row->value(0).toString(); + if (isFilteredOut(value, "index")) + continue; + + indexes << value; + } + + return indexes; } QStringList SchemaResolver::getIndexesForTable(const QString& table) @@ -793,7 +934,7 @@ StrHash SchemaResolver::getAllObjectDetails(const } else { - SqlQueryPtr results = db->exec(QString("SELECT name, type, sql FROM %1.sqlite_master").arg(getPrefixDb(database, db->getDialect())), dbFlags); + SqlQueryPtr results = db->exec(QString("SELECT name, type, sql FROM %1.sqlite_master").arg(getPrefixDb(database)), dbFlags); if (results->isError()) { qCritical() << "Error while getting all object details in SchemaResolver:" << results->getErrorCode(); @@ -832,9 +973,6 @@ QList SchemaResolver::getParsedIndexesForTable(const QStri SqliteCreateIndexPtr createIndex; for (const QString& index : indexes) { - if (index.startsWith("sqlite_", Qt::CaseInsensitive)) - continue; - query = getParsedObject(database, index, INDEX); if (!query) continue; @@ -989,11 +1127,10 @@ QList SchemaResolver::getParsedViewsForTable(const QString& void SchemaResolver::filterSystemIndexes(QStringList& indexes) { - Dialect dialect = db->getDialect(); QMutableListIterator it(indexes); while (it.hasNext()) { - if (isSystemIndex(it.next(), dialect)) + if (isSystemIndex(it.next())) it.remove(); } } @@ -1075,9 +1212,6 @@ QString SchemaResolver::getSqliteMasterDdl(bool temp) QStringList SchemaResolver::getCollations() { QStringList list; - if (db->getDialect() != Dialect::Sqlite3) - return list; - SqlQueryPtr results = db->exec("PRAGMA collation_list", dbFlags); if (results->isError()) { -- cgit v1.2.3