foreach(sqlb::FieldPtr f, fields)
{
QTreeWidgetItem *tbitem = new QTreeWidgetItem(ui->treeWidget);
tbitem->setFlags(tbitem->flags() | Qt::ItemIsEditable);
tbitem->setText(kName, f->name());
QComboBox* typeBox = new QComboBox(ui->treeWidget);
typeBox->setProperty("column", f->name());
typeBox->setEditable(false);
typeBox->addItems(sqlb::Field::Datatypes);
int index = typeBox->findText(f->type(), Qt::MatchExactly);
if(index == -1)
{
// non standard named type
typeBox->addItem(f->type());
index = typeBox->count() - 1;
}
typeBox->setCurrentIndex(index);
connect(typeBox, SIGNAL(currentIndexChanged(int)), this, SLOT(updateTypes()));
ui->treeWidget->setItemWidget(tbitem, kType, typeBox);
tbitem->setCheckState(kNotNull, f->notnull() ? Qt::Checked : Qt::Unchecked);
tbitem->setCheckState(kPrimaryKey, f->primaryKey() ? Qt::Checked : Qt::Unchecked);
tbitem->setCheckState(kAutoIncrement, f->autoIncrement() ? Qt::Checked : Qt::Unchecked);
tbitem->setCheckState(kUnique, f->unique() ? Qt::Checked : Qt::Unchecked);
tbitem->setText(kDefault, f->defaultValue());
tbitem->setText(kCheck, f->check());
ui->treeWidget->addTopLevelItem(tbitem);
}
bool DBBrowserDB::addColumn(const QString& tablename, const sqlb::FieldPtr& field)
{
QString sql = QString("ALTER TABLE `%1` ADD COLUMN %2").arg(tablename).arg(field->toString());
// Execute it and update the schema
bool result = executeSQL(sql);
updateSchema();
return result;
}
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;
}
foreach(sqlb::FieldPtr f, fields)
{
QTreeWidgetItem *tbitem = new QTreeWidgetItem(ui->treeWidget);
tbitem->setFlags(tbitem->flags() | Qt::ItemIsEditable);
tbitem->setText(kName, f->name());
QComboBox* typeBox = new QComboBox(ui->treeWidget);
typeBox->setProperty("column", f->name());
typeBox->setEditable(true);
typeBox->addItems(sqlb::Field::Datatypes);
int index = typeBox->findText(f->type(), Qt::MatchExactly);
if(index == -1)
{
// non standard named type
typeBox->addItem(f->type());
index = typeBox->count() - 1;
}
typeBox->setCurrentIndex(index);
connect(typeBox, SIGNAL(currentIndexChanged(int)), this, SLOT(updateTypes()));
//connect(typeBox, SIGNAL(editTextChanged(QString)), this, SLOT(updateTypes()));
ui->treeWidget->setItemWidget(tbitem, kType, typeBox);
tbitem->setCheckState(kNotNull, f->notnull() ? Qt::Checked : Qt::Unchecked);
tbitem->setCheckState(kPrimaryKey, f->primaryKey() ? Qt::Checked : Qt::Unchecked);
tbitem->setCheckState(kAutoIncrement, f->autoIncrement() ? Qt::Checked : Qt::Unchecked);
tbitem->setCheckState(kUnique, f->unique() ? Qt::Checked : Qt::Unchecked);
// For the default value check if it is surrounded by parantheses and if that's the case
// add a '=' character before the entire string to match the input format we're expecting
// from the user when using functions in the default value field.
if(f->defaultValue().startsWith('(') && f->defaultValue().endsWith(')'))
tbitem->setText(kDefault, "=" + f->defaultValue());
else
tbitem->setText(kDefault, f->defaultValue());
tbitem->setText(kCheck, f->check());
tbitem->setText(kForeignKey, f->foreignKey());
ui->treeWidget->addTopLevelItem(tbitem);
}
