bool DBBrowserDB::renameColumn(const QString& tablename, const QString& name, sqlb::FieldPtr to, int move)
{
// NOTE: This function is working around the incomplete ALTER TABLE command in SQLite.
// If SQLite should fully support this command one day, this entire
// function can be changed to executing something like this:
//QString sql;
//if(to.isNull())
// sql = QString("ALTER TABLE `%1` DROP COLUMN `%2`;").arg(table).arg(column);
//else
// sql = QString("ALTER TABLE `%1` MODIFY `%2` %3").arg(tablename).arg(to).arg(type); // This is wrong...
//return executeSQL(sql);
// Collect information on the current DB layout
QString tableSql = getObjectByName(tablename).getsql();
if(tableSql.isEmpty())
{
lastErrorMessage = QObject::tr("renameColumn: cannot find table %1.").arg(tablename);
qWarning() << lastErrorMessage;
return false;
}
// Create table schema
sqlb::Table oldSchema = sqlb::Table::parseSQL(tableSql).first;
// Check if field actually exists
if(oldSchema.findField(name) == -1)
{
lastErrorMessage = QObject::tr("renameColumn: cannot find column %1.").arg(name);
qWarning() << lastErrorMessage;
return false;
}
// Create savepoint to be able to go back to it in case of any error
if(!executeSQL("SAVEPOINT sqlitebrowser_rename_column"))
{
lastErrorMessage = QObject::tr("renameColumn: creating savepoint failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
return false;
}
// Create a new table with a name that hopefully doesn't exist yet.
// Its layout is exactly the same as the one of the table to change - except for the column to change
// of course
sqlb::Table newSchema = oldSchema;
newSchema.setName("sqlitebrowser_rename_column_new_table");
QString select_cols;
if(to.isNull())
{
// We want drop the column - so just remove the field
newSchema.removeField(name);
for(int i=0;i<newSchema.fields().count();++i)
select_cols.append(QString("`%1`,").arg(newSchema.fields().at(i)->name()));
select_cols.chop(1); // remove last comma
} else {
// We want to modify it
// Move field
int index = newSchema.findField(name);
sqlb::FieldPtr temp = newSchema.fields().at(index);
newSchema.setField(index, newSchema.fields().at(index + move));
newSchema.setField(index + move, temp);
// Get names of fields to select from old table now - after the field has been moved and before it might be renamed
for(int i=0;i<newSchema.fields().count();++i)
select_cols.append(QString("`%1`,").arg(newSchema.fields().at(i)->name()));
select_cols.chop(1); // remove last comma
// Modify field
newSchema.setField(index + move, to);
}
// Create the new table
if(!executeSQL(newSchema.sql()))
{
lastErrorMessage = QObject::tr("renameColumn: creating new table failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Copy the data from the old table to the new one
if(!executeSQL(QString("INSERT INTO sqlitebrowser_rename_column_new_table SELECT %1 FROM `%2`;").arg(select_cols).arg(tablename)))
{
lastErrorMessage = QObject::tr("renameColumn: copying data to new table failed. DB says:\n"
"%1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Save all indices, triggers and views associated with this table because SQLite deletes them when we drop the table in the next step
QString otherObjectsSql;
for(objectMap::ConstIterator it=objMap.begin();it!=objMap.end();++it)
{
// If this object references the table and it's not the table itself save it's SQL string
if((*it).getTableName() == tablename && (*it).gettype() != "table")
otherObjectsSql += (*it).getsql() + "\n";
}
// Delete the old table
if(!executeSQL(QString("DROP TABLE `%1`;").arg(tablename)))
{
lastErrorMessage = QObject::tr("renameColumn: deleting old table failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Rename the temporary table
if(!renameTable("sqlitebrowser_rename_column_new_table", tablename))
{
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Restore the saved triggers, views and indices
if(!executeMultiSQL(otherObjectsSql, true, true))
{
QMessageBox::information(0, qApp->applicationName(), QObject::tr("Restoring some of the objects associated with this table failed. "
"This is most likely because some column names changed. "
"Here's the SQL statement which you might want to fix and execute manually:\n\n")
+ otherObjectsSql);
}
// Release the savepoint - everything went fine
if(!executeSQL("RELEASE SAVEPOINT sqlitebrowser_rename_column;"))
{
lastErrorMessage = QObject::tr("renameColumn: releasing savepoint failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
return false;
}
// Success, update the DB schema before returning
updateSchema();
return true;
}
