void Migration_20100216122008_rename_tables::renameTables (Direction direction)
{
renameTable ("person" , "people" , direction);
renameTable ("flugzeug", "planes" , direction);
renameTable ("flug" , "flights", direction);
renameTable ("user" , "users" , direction);
}
void Migration_20100216122008_rename_tables::renameTable (const QString &oldName, const QString &newName, Direction direction)
{
switch (direction)
{
case dirUp: renameTable (oldName, newName); break;
case dirDown: renameTable (newName, oldName); break;
}
}
int internalMaterializeVectorView(MYSQL * sqlConn, rdbVector * viewVector,
rdbVector * newVector)
{
/* Build the sql string and add the data from the view */
int length = strlen(sqlTemplateMaterializeVectorView) +
strlen(newVector->tableName) +
strlen(viewVector->tableName) + 1;
char strMaterializeViewSQL[length];
sprintf( strMaterializeViewSQL, sqlTemplateMaterializeVectorView,
newVector->tableName, viewVector->tableName );
int success = mysql_query(sqlConn, strMaterializeViewSQL);
if( success != 0 )
return 0;
/* Remove the current view */
length = strlen(sqlTemplateDropVectorView) + strlen(viewVector->tableName) + 1;
char strDropViewSQL[length];
sprintf( strDropViewSQL, sqlTemplateDropVectorView, viewVector->tableName );
success = mysql_query(sqlConn, strDropViewSQL);
if( success != 0 )
return 0;
/* Delete corresponding references */
success = removeVectorViewReferences(sqlConn, viewVector);
/* Rename new vector table to the view's name*/
success *= renameTable(sqlConn, newVector->tableName, viewVector->tableName);
/* Update the metadata info of the view table */
viewVector->isView = 0;
success *= updateVectorMetadataInfo(sqlConn, viewVector);
/* Delete Metadata info of new table */
success *= deleteMetadataInfo(sqlConn, newVector->metadataID);
return success;
}
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;
}
/*
* Constructs a editTableForm as a child of 'parent', with the
* name 'name' and widget flags set to 'f'.
*
* The dialog will by default be modeless, unless you set 'modal' to
* TRUE to construct a modal dialog.
*/
editTableForm::editTableForm( QWidget* parent, const char* name, bool modal, WFlags fl )
: QDialog( parent, name, modal, fl )
{
if ( !name )
setName( "editTableForm" );
editTableFormLayout = new QGridLayout( this, 1, 1, 11, 6, "editTableFormLayout");
layout50 = new QVBoxLayout( 0, 0, 6, "layout50");
tableLine = new QLineEdit( this, "tableLine" );
tableLine->setFocusPolicy( QLineEdit::NoFocus );
tableLine->setReadOnly( TRUE );
layout50->addWidget( tableLine );
fieldListView = new QListView( this, "fieldListView" );
fieldListView->addColumn( tr( "Field name" ) );
fieldListView->addColumn( tr( "Field type" ) );
fieldListView->setResizePolicy( QScrollView::Manual );
fieldListView->setResizeMode( QListView::AllColumns );
layout50->addWidget( fieldListView );
editTableFormLayout->addLayout( layout50, 0, 0 );
layout42 = new QVBoxLayout( 0, 0, 6, "layout42");
renameTableButton = new QPushButton( this, "renameTableButton" );
layout42->addWidget( renameTableButton );
QSpacerItem* spacer = new QSpacerItem( 20, 23, QSizePolicy::Minimum, QSizePolicy::Expanding );
layout42->addItem( spacer );
renameFieldButton = new QPushButton( this, "renameFieldButton" );
renameFieldButton->setEnabled( FALSE );
layout42->addWidget( renameFieldButton );
removeFieldButton = new QPushButton( this, "removeFieldButton" );
removeFieldButton->setEnabled( FALSE );
layout42->addWidget( removeFieldButton );
addFieldButton = new QPushButton( this, "addFieldButton" );
layout42->addWidget( addFieldButton );
editTableFormLayout->addLayout( layout42, 0, 1 );
layout41 = new QHBoxLayout( 0, 0, 6, "layout41");
QSpacerItem* spacer_2 = new QSpacerItem( 161, 20, QSizePolicy::Expanding, QSizePolicy::Minimum );
layout41->addItem( spacer_2 );
closeButton = new QPushButton( this, "closeButton" );
layout41->addWidget( closeButton );
editTableFormLayout->addMultiCellLayout( layout41, 1, 1, 0, 1 );
languageChange();
resize( QSize(428, 266).expandedTo(minimumSizeHint()) );
clearWState( WState_Polished );
// signals and slots connections
connect( closeButton, SIGNAL( clicked() ), this, SLOT( accept() ) );
connect( renameTableButton, SIGNAL( clicked() ), this, SLOT( renameTable() ) );
connect( removeFieldButton, SIGNAL( clicked() ), this, SLOT( removeField() ) );
connect( addFieldButton, SIGNAL( clicked() ), this, SLOT( addField() ) );
connect( renameFieldButton, SIGNAL( clicked() ), this, SLOT( editField() ) );
connect( fieldListView, SIGNAL( selectionChanged() ), this, SLOT( fieldSelectionChanged() ) );
init();
}