Imported Upstream version 3.1.0upstream/3.1.0

1 files changed, 120 insertions, 65 deletions

diff --git a/SQLiteStudio3/coreSQLiteStudio/tablemodifier.cpp b/SQLiteStudio3/coreSQLiteStudio/tablemodifier.cpp
index 3e23239..a0f6262 100644
--- a/SQLiteStudio3/coreSQLiteStudio/tablemodifier.cpp
+++ b/SQLiteStudio3/coreSQLiteStudio/tablemodifier.cpp
@@ -36,6 +36,11 @@ void TableModifier::alterTable(SqliteCreateTablePtr newCreateTable)
     existingColumns = newCreateTable->getColumnNames();
     newName = newCreateTable->table;
 
+    if (db->getDialect() == Dialect::Sqlite3)
+        sqls << "PRAGMA foreign_keys = 0;";
+
+    handleFkConstrains(newCreateTable.data(), createTable->table, newName);
+
     QString tempTableName;
     if (table.compare(newName, Qt::CaseInsensitive) == 0)
         tempTableName = renameToTemp();
@@ -44,7 +49,7 @@ void TableModifier::alterTable(SqliteCreateTablePtr newCreateTable)
     sqls << newCreateTable->detokenize();
     copyDataTo(newCreateTable);
 
-    handleFks(tempTableName);
+    handleFks();
 
     // If temp table was created, it means that table name hasn't changed. In that case we need to cleanup temp table (drop it).
     // Otherwise, the table name has changed, therefor there still remains the old table which we copied data from - we need to drop it here.
@@ -53,6 +58,9 @@ void TableModifier::alterTable(SqliteCreateTablePtr newCreateTable)
     handleIndexes();
     handleTriggers();
     handleViews();
+
+    if (db->getDialect() == Dialect::Sqlite3)
+        sqls << "PRAGMA foreign_keys = 1;";
 }
 
 void TableModifier::renameTo(const QString& newName)
@@ -60,15 +68,10 @@ void TableModifier::renameTo(const QString& newName)
     if (!createTable)
         return;
 
-    if (dialect == Dialect::Sqlite3)
-    {
-        sqls << QString("ALTER TABLE %1 RENAME TO %2;").arg(wrapObjIfNeeded(table, dialect), wrapObjIfNeeded(newName, dialect));
-    }
-    else
-    {
-        sqls << QString("CREATE TABLE %1 AS SELECT * FROM %2;").arg(wrapObjIfNeeded(newName, dialect), wrapObjIfNeeded(table, dialect))
-             << QString("DROP TABLE %1;").arg(wrapObjIfNeeded(table, dialect));
-    }
+    // Using ALTER TABLE RENAME TO is not a good solution here, because it automatically renames all occurrences in REFERENCES,
+    // which we don't want, because we rename a lot to temporary tables and drop them.
+    sqls << QString("CREATE TABLE %1 AS SELECT * FROM %2;").arg(wrapObjIfNeeded(newName, dialect), wrapObjIfNeeded(table, dialect))
+         << QString("DROP TABLE %1;").arg(wrapObjIfNeeded(table, dialect));
 
     table = newName;
     createTable->table = newName;
@@ -93,14 +96,19 @@ void TableModifier::copyDataTo(const QString& targetTable)
     copyDataTo(targetTable, colsToCopy, colsToCopy);
 }
 
-void TableModifier::handleFks(const QString& tempTableName)
+void TableModifier::handleFks()
 {
+    tablesHandledForFk << originalTable;
+
     SchemaResolver resolver(db);
 
     QStringList fkTables = resolver.getFkReferencingTables(originalTable);
 
-    foreach (const QString& fkTable, fkTables)
+    for (const QString& fkTable : fkTables)
     {
+        if (tablesHandledForFk.contains(fkTable, Qt::CaseInsensitive))
+            continue; // Avoid recurrent FK handling
+
         TableModifier subModifier(db, fkTable);
         if (!subModifier.isValid())
         {
@@ -109,15 +117,22 @@ void TableModifier::handleFks(const QString& tempTableName)
             continue;
         }
 
+        // Those were removed when fixing #2954. Seem to be useless for subHandleFks(). Unless there's some bug report for it,
+        // they should be removed in near future:
+
         subModifier.usedTempTableNames = usedTempTableNames;
-        subModifier.tableColMap = tableColMap;
         subModifier.triggerNameToDdlMap = triggerNameToDdlMap;
-        subModifier.existingColumns = existingColumns;
-        subModifier.newName = newName;
-        subModifier.subHandleFks(originalTable, tempTableName);
+        subModifier.existingColumns = existingColumns; // for identifying removed columns
+        subModifier.tableColMap = tableColMap; // for identifying renamed columns
+        subModifier.newName = fkTable;
+        subModifier.tablesHandledForFk = tablesHandledForFk;
+        subModifier.handleFks(originalTable, newName);
         sqls += subModifier.generateSqls();
         modifiedTables << fkTable;
+
         triggerNameToDdlMap = subModifier.triggerNameToDdlMap;
+        tablesHandledForFk = subModifier.tablesHandledForFk;
+        usedTempTableNames = subModifier.usedTempTableNames;
 
         modifiedTables += subModifier.getModifiedTables();
         modifiedIndexes += subModifier.getModifiedIndexes();
@@ -129,22 +144,9 @@ void TableModifier::handleFks(const QString& tempTableName)
     }
 }
 
-void TableModifier::subHandleFks(const QString& oldName, const QString& oldTempName)
+void TableModifier::handleFks(const QString& oldName, const QString& theNewName)
 {
-    bool modified = false;
-    foreach (SqliteCreateTable::Constraint* fk, createTable->getForeignKeysByTable(oldName))
-    {
-        if (subHandleFks(fk->foreignKey, oldName, oldTempName))
-            modified = true;
-    }
-
-    foreach (SqliteCreateTable::Column::Constraint* fk, createTable->getColumnForeignKeysByTable(oldName))
-    {
-        if (subHandleFks(fk->foreignKey, oldName, oldTempName))
-            modified = true;
-    }
-
-    if (!modified)
+    if (!handleFkConstrains(createTable.data(), oldName, theNewName))
         return;
 
     QString tempName = renameToTemp();
@@ -155,7 +157,7 @@ void TableModifier::subHandleFks(const QString& oldName, const QString& oldTempN
 
     copyDataTo(originalTable);
 
-    handleFks(tempName);
+    handleFks();
 
     sqls << QString("DROP TABLE %1;").arg(wrapObjIfNeeded(tempName, dialect));
 
@@ -163,21 +165,15 @@ void TableModifier::subHandleFks(const QString& oldName, const QString& oldTempN
     simpleHandleTriggers();
 }
 
-bool TableModifier::subHandleFks(SqliteForeignKey* fk, const QString& oldName, const QString& oldTempName)
+bool TableModifier::handleFks(SqliteForeignKey* fk, const QString& oldName, const QString& theNewName)
 {
     // If table was not renamed (but uses temp table name), we will rename temp name into target name.
     // If table was renamed, we will rename old name to new name.
     bool modified = false;
 
     // Table
-    if (handleName(oldName, fk->foreignTable))
-        modified = true;
-    else if (!oldTempName.isNull() && fk->foreignTable.compare(oldName, Qt::CaseInsensitive) == 0)
-    {
-        // This is the case when main table was not renamed - we will stay with the original name, but we need to mark it as modified,
-        // so the table gets recreated (the temp table is changed to the original table name in this FK).
+    if (handleName(oldName, theNewName, fk->foreignTable))
         modified = true;
-    }
 
     // Columns
     if (handleIndexedColumns(fk->indexedColumns))
@@ -186,45 +182,59 @@ bool TableModifier::subHandleFks(SqliteForeignKey* fk, const QString& oldName, c
     return modified;
 }
 
+bool TableModifier::handleFkConstrains(SqliteCreateTable* stmt, const QString& oldName, const QString& theNewName)
+{
+    bool modified = false;
+    for (SqliteCreateTable::Constraint* fk : stmt->getForeignKeysByTable(oldName))
+    {
+        if (handleFks(fk->foreignKey, oldName, theNewName))
+            modified = true;
+    }
+
+    for (SqliteCreateTable::Column::Constraint* fk : stmt->getColumnForeignKeysByTable(oldName))
+    {
+        if (handleFks(fk->foreignKey, oldName, theNewName))
+            modified = true;
+    }
+    return modified;
+}
+
 bool TableModifier::handleName(const QString& oldName, QString& valueToUpdate)
 {
-    if (newName.compare(oldName, Qt::CaseInsensitive) == 0)
+    return handleName(oldName, newName, valueToUpdate);
+}
+
+bool TableModifier::handleName(const QString& oldName, const QString& theNewName, QString& valueToUpdate)
+{
+    if (theNewName.compare(oldName, Qt::CaseInsensitive) == 0)
         return false;
 
     if (valueToUpdate.compare(oldName, Qt::CaseInsensitive) == 0)
     {
-        valueToUpdate = newName;
+        valueToUpdate = theNewName;
         return true;
     }
     return false;
 }
 
-bool TableModifier::handleIndexedColumns(QList<SqliteIndexedColumn*>& columnsToUpdate)
+bool TableModifier::handleIndexedColumnsInitial(SqliteOrderBy* col, bool& modified)
 {
-    bool modified = false;
-    QString lowerName;
-    QMutableListIterator<SqliteIndexedColumn*> it(columnsToUpdate);
-    while (it.hasNext())
-    {
-        SqliteIndexedColumn* idxCol = it.next();
+    if (col->isSimpleColumn())
+        return false;
 
-        // If column was modified, assign new name
-        lowerName = idxCol->name.toLower();
-        if (tableColMap.contains(lowerName))
-        {
-            idxCol->name = tableColMap[lowerName];
-            modified = true;
-            continue;
-        }
+    QString oldExpr = col->expr->tokens.detokenize();
+    if (!handleExpr(col->expr))
+        qWarning() << "Handling column change in multi-level expression of CREATE INDEX column failed. The change will most probably be skipped in the final update DDL.";
 
-        // It wasn't modified, but it's not on existing columns list? Remove it.
-        if (indexOf(existingColumns, idxCol->name, Qt::CaseInsensitive) == -1)
-        {
-            it.remove();
-            modified = true;
-        }
-    }
-    return modified;
+    modified = (col->expr->tokens.detokenize() != oldExpr);
+    return true;
+}
+
+bool TableModifier::handleIndexedColumnsInitial(SqliteIndexedColumn* col, bool& modified)
+{
+    UNUSED(col);
+    UNUSED(modified);
+    return false;
 }
 
 bool TableModifier::handleColumnNames(QStringList& columnsToUpdate)
@@ -783,6 +793,51 @@ bool TableModifier::handleExprWithTrigTable(SqliteExpr* expr)
     return true;
 }
 
+bool TableModifier::handleExpr(SqliteExpr* expr)
+{
+    // Handle subqueries
+    QList<SqliteExpr*> exprList;
+    exprList << expr->expr1;
+    exprList << expr->expr2;
+    exprList << expr->expr3;
+    exprList.append(expr->exprList);
+    exprList.removeAll(nullptr);
+    if (!exprList.isEmpty())
+    {
+        bool res = true;
+        for (SqliteExpr* e : exprList)
+        {
+            res &= handleExpr(e);
+            if (!res)
+                break;
+        }
+        return res;
+    }
+
+    // No need to handle subselect. Currently handleExpr() is used only in context of expr index column (in CREATE INDEX),
+    // which does not allow subselects. If the method comes to use with subselects supported, then this has to be implemented.
+
+    // Handle specific column
+    if (expr->mode != SqliteExpr::Mode::ID)
+        return true;
+
+    if (!expr->database.isNull())
+        return true;
+
+    QStringList columns = QStringList({expr->column});
+    if (!handleColumnNames(columns))
+        return true;
+
+    if (columns.isEmpty())
+    {
+        qDebug() << "Column in the expression is no longer present in the table. Cannot update the expression automatically.";
+        return false;
+    }
+
+    expr->column = columns.first();
+    return true;
+}
+
 void TableModifier::simpleHandleIndexes()
 {
     SchemaResolver resolver(db);