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); }