New upstream version 3.4.13+dfsg.upstream/3.4.13+dfsgupstream

1 files changed, 309 insertions, 89 deletions

diff --git a/SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp b/SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp
index 129bb43..324cbf4 100644
--- a/SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp
+++ b/SQLiteStudio3/coreSQLiteStudio/schemaresolver.cpp
@@ -190,6 +190,33 @@ QStringList SchemaResolver::getTableColumns(const QString &database, const QStri
     return columns;
 }
 
+StrHash<DataType> SchemaResolver::getTableColumnDataTypesByName(const QString &table)
+{
+    return getTableColumnDataTypesByName("main", table);
+}
+
+StrHash<DataType> SchemaResolver::getTableColumnDataTypesByName(const QString &database, const QString &table)
+{
+    StrHash<DataType> dataTypes;
+    SqliteCreateTablePtr createTable = getParsedObject(database, table, TABLE).dynamicCast<SqliteCreateTable>();
+    if (!createTable)
+    {
+        return dataTypes;
+    }
+
+    for (SqliteCreateTable::Column* col : createTable->columns)
+    {
+        if (!col->type)
+        {
+            dataTypes[col->name] = DataType();
+            continue;
+        }
+
+        dataTypes[col->name] = col->type->toDataType();
+    }
+    return dataTypes;
+}
+
 QList<DataType> SchemaResolver::getTableColumnDataTypes(const QString& table, int expectedNumberOfTypes)
 {
     return getTableColumnDataTypes("main", table, expectedNumberOfTypes);
@@ -806,56 +833,52 @@ QStringList SchemaResolver::getFkReferencingTables(const QString& table)
 
 QStringList SchemaResolver::getFkReferencingTables(const QString& database, const QString& table)
 {
-    // Get all tables
-    StrHash<SqliteCreateTablePtr> parsedTables = getAllParsedTables(database);
+    static_qstring(fkQueryTpl, R"(
+                WITH foreign_keys AS (
+                   SELECT m.name AS table_name, lower(fk.[table]) AS foreign_table
+                     FROM %1.sqlite_master AS m
+                     JOIN %1.pragma_foreign_key_list(m.name) AS fk
+                    WHERE m.type = 'table'
+                )
+                SELECT table_name
+                  FROM foreign_keys
+                 WHERE foreign_table = '%2';)");
+
+    SqlQueryPtr results = db->exec(fkQueryTpl.arg(getPrefixDb(database), escapeString(table.toLower())), dbFlags);
+    if (results->isError())
+    {
+        qCritical() << "Error while getting FK-referencing table list in SchemaResolver:" << results->getErrorCode();
+        return QStringList();
+    }
 
-    // Exclude queried table from the list
-    parsedTables.remove(table);
+    QStringList resList;
+    for (SqlResultsRowPtr row : results->getAll())
+        resList << row->value(0).toString();
 
-    // Resolve referencing tables
-    return getFkReferencingTables(table, parsedTables.values());
+    return resList;
 }
 
-QStringList SchemaResolver::getFkReferencingTables(const QString& table, const QList<SqliteCreateTablePtr>& allParsedTables)
+QStringList SchemaResolver::getFkReferencedTables(const QString& table)
 {
-    QStringList tables;
-
-    QList<SqliteCreateTable::Constraint*> tableFks;
-    QList<SqliteCreateTable::Column::Constraint*> fks;
-    bool result = false;
-    for (SqliteCreateTablePtr createTable : allParsedTables)
-    {
-        // Check table constraints
-        tableFks = createTable->getForeignKeysByTable(table);
-        result = contains<SqliteCreateTable::Constraint*>(tableFks, [&table](SqliteCreateTable::Constraint* fk)
-        {
-           return fk->foreignKey->foreignTable == table;
-        });
-
-        if (result)
-        {
-            tables << createTable->table;
-            continue;
-        }
+    return getFkReferencedTables("main", table);
+}
 
-        // Check column constraints
-        for (SqliteCreateTable::Column* column : createTable->columns)
-        {
-            fks = column->getForeignKeysByTable(table);
-            result = contains<SqliteCreateTable::Column::Constraint*>(fks, [&table](SqliteCreateTable::Column::Constraint* fk)
-            {
-                return fk->foreignKey->foreignTable == table;
-            });
+QStringList SchemaResolver::getFkReferencedTables(const QString& database, const QString& table)
+{
+    static_qstring(fkQueryTpl, "SELECT [table] FROM %1.pragma_foreign_key_list('%2');");
 
-            if (result)
-            {
-                tables << createTable->table;
-                break;
-            }
-        }
+    SqlQueryPtr results = db->exec(fkQueryTpl.arg(getPrefixDb(database), escapeString(table)), dbFlags);
+    if (results->isError())
+    {
+        qCritical() << "Error while getting FK-referenced table list in SchemaResolver:" << results->getErrorCode() << results->getErrorText();
+        return QStringList();
     }
 
-    return tables;
+    QStringList resList;
+    for (SqlResultsRowPtr row : results->getAll())
+        resList << row->value(0).toString();
+
+    return resList;
 }
 
 SchemaResolver::ObjectType SchemaResolver::objectTypeFromQueryType(const SqliteQueryType& queryType)
@@ -901,9 +924,9 @@ SchemaResolver::ObjectType SchemaResolver::objectTypeFromQueryType(const SqliteQ
 
 QStringList SchemaResolver::getIndexesForTable(const QString& database, const QString& table)
 {
-    static_qstring(idxForTableTpl, "SELECT name FROM %1.pragma_index_list(%2)");
+    static_qstring(idxForTableTpl, "SELECT name FROM %1.sqlite_master WHERE type = 'index' AND (tbl_name = '%2' OR lower(tbl_name) = lower('%2'));");
 
-    QString query = idxForTableTpl.arg(wrapObjName(database), wrapString(table));
+    QString query = idxForTableTpl.arg(wrapObjName(database), wrapObjIfNeeded(table));
     SqlQueryPtr results = db->exec(query, dbFlags);
 
     QStringList indexes;
@@ -927,9 +950,14 @@ QStringList SchemaResolver::getIndexesForTable(const QString& table)
 
 QStringList SchemaResolver::getTriggersForTable(const QString& database, const QString& table)
 {
+    static_qstring(trigForTableTpl, "SELECT name FROM %1.sqlite_master WHERE type = 'trigger' AND (tbl_name = '%2' OR lower(tbl_name) = lower('%2'));");
+
+    QString query = trigForTableTpl.arg(wrapObjName(database), escapeString(table));
+    SqlQueryPtr results = db->exec(query, dbFlags);
+
     QStringList names;
-    for (SqliteCreateTriggerPtr trig : getParsedTriggersForTable(database, table))
-        names << trig->trigger;
+    for (SqlResultsRowPtr row : results->getAll())
+        names << row->value(0).toString();
 
     return names;
 }
@@ -967,6 +995,96 @@ QStringList SchemaResolver::getViewsForTable(const QString& table)
     return getViewsForTable("main", table);
 }
 
+QStringList SchemaResolver::getIndexDdlsForTable(const QString& database, const QString& table)
+{
+    return getObjectDdlsReferencingTableOrView(database, table, INDEX);
+}
+
+QStringList SchemaResolver::getIndexDdlsForTable(const QString& table)
+{
+    return getIndexDdlsForTable("main", table);
+}
+
+QStringList SchemaResolver::getTriggerDdlsForTableOrView(const QString& database, const QString& table)
+{
+    return getObjectDdlsReferencingTableOrView(database, table, TRIGGER);
+}
+
+QStringList SchemaResolver::getTriggerDdlsForTableOrView(const QString& table)
+{
+    return getTriggerDdlsForTableOrView("main", table);
+}
+
+QList<SchemaResolver::TableListItem> SchemaResolver::getAllTableListItems()
+{
+    return getAllTableListItems("main");
+}
+
+QList<SchemaResolver::TableListItem> SchemaResolver::getAllTableListItems(const QString& database)
+{
+    QList<TableListItem> items;
+
+    QList<QVariant> rows;
+    bool useCache = usesCache();
+    ObjectCacheKey key(ObjectCacheKey::TABLE_LIST_ITEM, db, database);
+    if (useCache && cache.contains(key))
+    {
+        rows = cache.object(key, true)->toList();
+    }
+    else
+    {
+        //SqlQueryPtr results = db->exec(QString("PRAGMA %1.table_list").arg(getPrefixDb(database)), dbFlags); // not using for now to support SQLite versions < 3.37.0
+        static_qstring(queryTpl, "SELECT name, (CASE WHEN type = 'view' THEN 'view' WHEN sql LIKE 'CREATE VIRTUAL%' THEN 'virtual' ELSE 'table' END) AS type FROM %1.sqlite_master WHERE type IN ('table', 'view')");
+        SqlQueryPtr results = db->exec(queryTpl.arg(getPrefixDb(database)), dbFlags);
+        if (results->isError())
+        {
+            qCritical() << "Error while getting all table list items in SchemaResolver:" << results->getErrorCode();
+            return items;
+        }
+
+        for (const SqlResultsRowPtr& row : results->getAll())
+            rows << row->valueMap();
+
+        if (!ignoreSystemObjects)
+        {
+            static QHash<QString, QVariant> sqliteMasterRow {
+                {"name", "sqlite_master"},
+                {"type", "table"}
+            };
+
+            static QHash<QString, QVariant> sqliteTempMasterRow {
+                {"name", "sqlite_temp_master"},
+                {"type", "table"}
+            };
+
+            rows << QVariant(sqliteMasterRow) << QVariant(sqliteTempMasterRow);
+        }
+
+        if (useCache)
+            cache.insert(key, new QVariant(rows));
+    }
+
+    QHash<QString, QVariant> row;
+    for (const QVariant& rowVariant : rows)
+    {
+        row = rowVariant.toHash();
+        QString value = row["name"].toString();
+        QString type = row["type"].toString();
+        if (isFilteredOut(value, type))
+            continue;
+
+        TableListItem item;
+        item.type = stringToTableListItemType(type);
+        if (item.type == TableListItem::UNKNOWN)
+            qCritical() << "Unhlandled table item type:" << type;
+
+        item.name = value;
+        items << item;
+    }
+
+    return items;
+}
+
 StrHash<SchemaResolver::ObjectDetails> SchemaResolver::getAllObjectDetails()
 {
     return getAllObjectDetails("main");
@@ -974,13 +1092,13 @@ StrHash<SchemaResolver::ObjectDetails> SchemaResolver::getAllObjectDetails()
 
 StrHash<SchemaResolver::ObjectDetails> SchemaResolver::getAllObjectDetails(const QString& database)
 {
-    StrHash< ObjectDetails> details;
+    StrHash<ObjectDetails> details;
     ObjectDetails detail;
     QString type;
 
     QList<QVariant> rows;
     bool useCache = usesCache();
-    ObjectCacheKey key(ObjectCacheKey::OBJECT_DETAILS, db, database);
+    ObjectCacheKey key(ObjectCacheKey::OBJECT_DETAILS, db, ignoreSystemObjects, database);
     if (useCache && cache.contains(key))
     {
         rows = cache.object(key, true)->toList();
@@ -995,7 +1113,12 @@ StrHash<SchemaResolver::ObjectDetails> SchemaResolver::getAllObjectDetails(const
         }
 
         for (const SqlResultsRowPtr& row : results->getAll())
+        {
+            if (isFilteredOut(row->value("name").toString(), row->value("type").toString()))
+                continue;
+
             rows << row->valueMap();
+        }
 
         if (useCache)
             cache.insert(key, new QVariant(rows));
@@ -1019,27 +1142,33 @@ StrHash<SchemaResolver::ObjectDetails> SchemaResolver::getAllObjectDetails(const
 
 QList<SqliteCreateIndexPtr> SchemaResolver::getParsedIndexesForTable(const QString& database, const QString& table)
 {
-    QList<SqliteCreateIndexPtr> createIndexList;
+    static_qstring(idxForTableTpl, "SELECT sql, name FROM %1.sqlite_master WHERE type = 'index' AND lower(tbl_name) = lower(?);");
 
-    QStringList indexes = getIndexes(database);
-    SqliteQueryPtr query;
-    SqliteCreateIndexPtr createIndex;
-    for (const QString& index : indexes)
+    QString query = idxForTableTpl.arg(getPrefixDb(database));
+    SqlQueryPtr results = db->exec(query, {table}, dbFlags);
+
+    QList<SqliteCreateIndexPtr> createIndexList;
+    for (SqlResultsRowPtr row : results->getAll())
     {
-        query = getParsedObject(database, index, INDEX);
+        QString ddl = row->value(0).toString();
+        QString name = row->value(1).toString();
+        if (ddl.isEmpty() || isFilteredOut(name, "index"))
+            continue;
+
+        SqliteQueryPtr query = getParsedDdl(ddl);
         if (!query)
             continue;
 
-        createIndex = query.dynamicCast<SqliteCreateIndex>();
+        SqliteCreateIndexPtr createIndex = query.dynamicCast<SqliteCreateIndex>();
         if (!createIndex)
         {
-            qWarning() << "Parsed DDL was not a CREATE INDEX statement, while queried for indexes.";
+            qWarning() << "Parsed DDL was not a CREATE INDEX statement, while queried for indexes. Queried db & table:"
+                       << database << table << "Index name:" << name << "DDL:" << ddl;
             continue;
         }
-
-        if (createIndex->table.compare(table, Qt::CaseInsensitive) == 0)
-            createIndexList << createIndex;
+        createIndexList << createIndex;
     }
+
     return createIndexList;
 }
 
@@ -1050,7 +1179,7 @@ QList<SqliteCreateIndexPtr> SchemaResolver::getParsedIndexesForTable(const QStri
 
 QList<SqliteCreateTriggerPtr> SchemaResolver::getParsedTriggersForTable(const QString& database, const QString& table, bool includeContentReferences)
 {
-    return getParsedTriggersForTableOrView(database, table, includeContentReferences, true);
+    return getParsedTriggersForTableOrView(database, table, includeContentReferences);
 }
 
 QList<SqliteCreateTriggerPtr> SchemaResolver::getParsedTriggersForTable(const QString& table, bool includeContentReferences)
@@ -1060,7 +1189,7 @@ QList<SqliteCreateTriggerPtr> SchemaResolver::getParsedTriggersForTable(const QS
 
 QList<SqliteCreateTriggerPtr> SchemaResolver::getParsedTriggersForView(const QString& database, const QString& view, bool includeContentReferences)
 {
-    return getParsedTriggersForTableOrView(database, view, includeContentReferences, false);
+    return getParsedTriggersForTableOrView(database, view, includeContentReferences);
 }
 
 QList<SqliteCreateTriggerPtr> SchemaResolver::getParsedTriggersForView(const QString& view, bool includeContentReferences)
@@ -1069,40 +1198,88 @@ QList<SqliteCreateTriggerPtr> SchemaResolver::getParsedTriggersForView(const QSt
 }
 
 QList<SqliteCreateTriggerPtr> SchemaResolver::getParsedTriggersForTableOrView(const QString& database, const QString& tableOrView,
-                                                                        bool includeContentReferences, bool table)
+                                                                        bool includeContentReferences)
 {
-    QList<SqliteCreateTriggerPtr> createTriggerList;
+    static_qstring(trigForTableTpl, "SELECT sql, name FROM %1.sqlite_master WHERE type = 'trigger' AND lower(tbl_name) = lower('%2');");
+    static_qstring(allTrigTpl, "SELECT sql FROM %1.sqlite_master WHERE type = 'trigger' AND lower(name) NOT IN (%2);");
 
-    QStringList triggers = getTriggers(database);
-    SqliteQueryPtr query;
-    SqliteCreateTriggerPtr createTrigger;
-    for (const QString& trig : triggers)
+    QString query = trigForTableTpl.arg(getPrefixDb(database), escapeString(tableOrView));
+    SqlQueryPtr results = db->exec(query, dbFlags);
+
+    QStringList alreadyProcessed;
+    QList<SqliteCreateTriggerPtr> createTriggerList;
+    for (SqlResultsRowPtr row : results->getAll())
     {
-        query = getParsedObject(database, trig, TRIGGER);
-        if (!query)
+        alreadyProcessed << wrapString(escapeString(row->value(1).toString().toLower()));
+        SqliteQueryPtr parsedDdl = getParsedDdl(row->value(0).toString());
+        if (!parsedDdl)
             continue;
 
-        createTrigger = query.dynamicCast<SqliteCreateTrigger>();
+        SqliteCreateTriggerPtr createTrigger = parsedDdl.dynamicCast<SqliteCreateTrigger>();
         if (!createTrigger)
         {
-            qWarning() << "Parsed DDL was not a CREATE TRIGGER statement, while queried for triggers." << createTrigger.data();
+            qWarning() << "Parsed DDL was not a CREATE TRIGGER statement, while queried for triggers.";
             continue;
         }
+        createTriggerList << createTrigger;
+    }
 
-        // The condition below checks:
-        // 1. if this is a call for table triggers and event time is INSTEAD_OF - skip this iteration
-        // 2. if this is a call for view triggers and event time is _not_ INSTEAD_OF - skip this iteration
-        // In other words, it's a logical XOR for "table" flag and "eventTime == INSTEAD_OF" condition.
-        if (table == (createTrigger->eventTime == SqliteCreateTrigger::Time::INSTEAD_OF))
-            continue;
+    if (includeContentReferences)
+    {
+        query = allTrigTpl.arg(wrapObjName(database), alreadyProcessed.join(", "));
+        results = db->exec(query, dbFlags);
 
-        if (createTrigger->table.compare(tableOrView, Qt::CaseInsensitive) == 0)
-            createTriggerList << createTrigger;
-        else if (includeContentReferences && indexOf(createTrigger->getContextTables(), tableOrView, Qt::CaseInsensitive) > -1)
-            createTriggerList << createTrigger;
+        for (SqlResultsRowPtr row : results->getAll())
+        {
+            SqliteQueryPtr parsedDdl = getParsedDdl(row->value(0).toString());
+            if (!parsedDdl)
+                continue;
 
+            SqliteCreateTriggerPtr createTrigger = parsedDdl.dynamicCast<SqliteCreateTrigger>();
+            if (!createTrigger)
+            {
+                qWarning() << "Parsed DDL was not a CREATE TRIGGER statement, while queried for triggers.";
+                continue;
+            }
+            if (indexOf(createTrigger->getContextTables(), tableOrView, Qt::CaseInsensitive) > -1)
+                createTriggerList << createTrigger;
+        }
     }
+
     return createTriggerList;
+
+//    QList<SqliteCreateTriggerPtr> createTriggerList;
+
+//    QStringList triggers = getTriggersForTable(database);
+//    SqliteQueryPtr query;
+//    SqliteCreateTriggerPtr createTrigger;
+//    for (const QString& trig : triggers)
+//    {
+//        query = getParsedObject(database, trig, TRIGGER);
+//        if (!query)
+//            continue;
+
+//        createTrigger = query.dynamicCast<SqliteCreateTrigger>();
+//        if (!createTrigger)
+//        {
+//            qWarning() << "Parsed DDL was not a CREATE TRIGGER statement, while queried for triggers." << createTrigger.data();
+//            continue;
+//        }
+
+//        // The condition below checks:
+//        // 1. if this is a call for table triggers and event time is INSTEAD_OF - skip this iteration
+//        // 2. if this is a call for view triggers and event time is _not_ INSTEAD_OF - skip this iteration
+//        // In other words, it's a logical XOR for "table" flag and "eventTime == INSTEAD_OF" condition.
+//        if (table == (createTrigger->eventTime == SqliteCreateTrigger::Time::INSTEAD_OF))
+//            continue;
+
+//        if (createTrigger->table.compare(tableOrView, Qt::CaseInsensitive) == 0)
+//            createTriggerList << createTrigger;
+//        else if (includeContentReferences && indexOf(createTrigger->getContextTables(), tableOrView, Qt::CaseInsensitive) > -1)
+//            createTriggerList << createTrigger;
+
+//    }
+//    return createTriggerList;
 }
 
 QString SchemaResolver::objectTypeToString(SchemaResolver::ObjectType type)
@@ -1137,6 +1314,20 @@ SchemaResolver::ObjectType SchemaResolver::stringToObjectType(const QString& typ
         return SchemaResolver::ANY;
 }
 
+SchemaResolver::TableListItem::Type SchemaResolver::stringToTableListItemType(const QString& type)
+{
+    if (type == "table")
+        return SchemaResolver::TableListItem::TABLE;
+    else if (type == "virtual")
+        return SchemaResolver::TableListItem::VIRTUAL_TABLE;
+    else if (type == "shadow")
+        return SchemaResolver::TableListItem::SHADOW_TABLE;
+    else if (type == "view")
+        return SchemaResolver::TableListItem::VIEW;
+    else
+        return SchemaResolver::TableListItem::UNKNOWN;
+}
+
 void SchemaResolver::staticInit()
 {
     cache.setExpireTime(3000);
@@ -1208,12 +1399,8 @@ bool SchemaResolver::isWithoutRowIdTable(const QString& database, const QString&
 
 bool SchemaResolver::isVirtualTable(const QString& database, const QString& table)
 {
-    SqliteQueryPtr query = getParsedObject(database, table, TABLE);
-    if (!query)
-        return false;
-
-    SqliteCreateVirtualTablePtr createVirtualTable = query.dynamicCast<SqliteCreateVirtualTable>();
-    return !createVirtualTable.isNull();
+    QString ddl = getObjectDdl(database, table, TABLE);
+    return ddl.simplified().toUpper().startsWith("CREATE VIRTUAL TABLE");
 }
 
 bool SchemaResolver::isVirtualTable(const QString& table)
@@ -1331,17 +1518,50 @@ QString SchemaResolver::normalizeCaseObjectNameByQuery(const QString& query, con
     return results->getSingleCell().toString();
 }
 
+QStringList SchemaResolver::getObjectDdlsReferencingTableOrView(const QString& database, const QString& table, ObjectType type)
+{
+    static_qstring(trigForTableTpl, "SELECT sql FROM %1.sqlite_master WHERE type = '%3' AND (tbl_name = '%2' OR lower(tbl_name) = lower('%2'));"); // non-lower variant for cyrlic alphabet
+
+    QString query = trigForTableTpl.arg(wrapObjName(database), escapeString(table), objectTypeToString(type));
+    SqlQueryPtr results = db->exec(query, dbFlags);
+
+    QStringList ddls;
+    for (SqlResultsRowPtr row : results->getAll())
+    {
+        QString ddl = row->value(0).toString();
+        if (!ddl.trimmed().endsWith(";"))
+            ddl += ";";
+
+        ddls << ddl;
+    }
+
+    return ddls;
+
+}
+
 SchemaResolver::ObjectCacheKey::ObjectCacheKey(Type type, Db* db, const QString& value1, const QString& value2, const QString& value3) :
-    type(type), db(db), value1(value1), value2(value2), value3(value3)
+  ObjectCacheKey(type, db, false, value1, value2, value3)
+{
+}
+
+SchemaResolver::ObjectCacheKey::ObjectCacheKey(Type type, Db* db, bool skipSystemObj, const QString& value1, const QString& value2, const QString& value3) :
+    type(type), db(db), skipSystemObj(skipSystemObj), value1(value1), value2(value2), value3(value3)
 {
 }
 
 int qHash(const SchemaResolver::ObjectCacheKey& key)
 {
-    return qHash(key.type) ^ qHash(key.db) ^ qHash(key.value1) ^ qHash(key.value2) ^ qHash(key.value3);
+    return qHash(key.type) ^ qHash(key.db) ^ qHash(key.value1) ^ qHash(key.value2) ^ qHash(key.value3) ^ qHash(key.skipSystemObj);
 }
 
 int operator==(const SchemaResolver::ObjectCacheKey& k1, const SchemaResolver::ObjectCacheKey& k2)
 {
-    return (k1.type == k2.type && k1.db == k2.db && k1.value1 == k2.value1 && k1.value2 == k2.value2 && k1.value3 == k2.value3);
+    return (
+        k1.type == k2.type &&
+        k1.db == k2.db &&
+        k1.value1 == k2.value1 &&
+        k1.value2 == k2.value2 &&
+        k1.value3 == k2.value3 &&
+        k1.skipSystemObj == k2.skipSystemObj
+    );
 }