New upstream version 3.3.3+dfsg1.upstream/3.3.3+dfsg1

1 files changed, 183 insertions, 49 deletions

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::ObjectCacheKey,QVariant> SchemaResolver::cache;
+ExpiringCache<QString, QString> 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<QStringList> SchemaResolver::getGroupedIndexes(const QString &database)
 {
-    QStringList allIndexes = getIndexes(database);
-    return getGroupedObjects(database, allIndexes, SqliteQueryType::CreateIndex);
+    StrHash<QString> indexesWithTables = getIndexesWithTables(database);
+
+    StrHash<QStringList> groupedIndexes;
+    auto it = indexesWithTables.iterator();
+    while (it.hasNext())
+    {
+        auto entry = it.next();
+        groupedIndexes[entry.value()] << entry.key();
+    }
+
+    return groupedIndexes;
 }
 
 StrHash<QStringList> SchemaResolver::getGroupedTriggers(const QString &database)
@@ -68,10 +95,10 @@ StrHash<QStringList> SchemaResolver::getGroupedTriggers(const QString &database)
     return getGroupedObjects(database, allTriggers, SqliteQueryType::CreateTrigger);
 }
 
-StrHash< QStringList> SchemaResolver::getGroupedObjects(const QString &database, const QStringList &inputList, SqliteQueryType type)
+StrHash<QStringList> SchemaResolver::getGroupedObjects(const QString &database, const QStringList &inputList, SqliteQueryType type)
 {
     QString strType = sqliteQueryTypeToString(type);
-    StrHash< QStringList> groupedTriggers;
+    StrHash<QStringList> 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<SelectResolver::Column> 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<QString> 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<QString> 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<SqliteQuery>(database, QString::null);
+    return getAllParsedObjectsForType<SqliteQuery>(database, QString());
 }
 
 StrHash< SqliteCreateTablePtr> SchemaResolver::getAllParsedTables()
@@ -550,6 +606,81 @@ StrHash< SqliteCreateViewPtr> SchemaResolver::getAllParsedViews(const QString& d
     return getAllParsedObjectsForType<SqliteCreateView>(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<QString> 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<SqliteCreateTablePtr> 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::ObjectDetails> 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<SqliteCreateIndexPtr> 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<SqliteCreateViewPtr> SchemaResolver::getParsedViewsForTable(const QString&
 
 void SchemaResolver::filterSystemIndexes(QStringList& indexes)
 {
-    Dialect dialect = db->getDialect();
     QMutableListIterator<QString> 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())
     {