public static Alteration alter(Table masterTable, Table targetTable, Dialect dialect)
{
Alteration alteration = new Alteration(masterTable, targetTable, dialect);
//string sql = "CREATE TABLE " + table.schema + "." + table.name + "\r\n(\r\n";
string sqlCmn = "";
string sql = "";
if (dialect == Dialect.db2)
{
sql = "ALTER TABLE " + targetTable.name + "\r\n";
}
else
{
sqlCmn = "ALTER TABLE " + targetTable.name + "\r\n";
sql = "";
}
string noChangeSql = sql;
//int ordinal = 0;
// Drop any "extra" columns in the target table
foreach (Column column in targetTable.columns.Values)
{
if (masterTable.columns.ContainsKey(column.name))
{
alteration.commonColumns.Add(column.name, column);
}
else
{
if (dialect == Dialect.db2)
{
sql += " DROP COLUMN " + column.name + "\r\n";
}
else
{
sql += sqlCmn + " DROP COLUMN " + column.name + "\r\n";
}
alteration.dropColumns.Add(column.name,column);
}
}
// Add any "new" columns in from the master table.
// Also update any changes between to column data types
string colDef;
foreach (Column column in masterTable.columns.Values)
{
if (targetTable.columns.ContainsKey(column.name))
{
colDef = columnDefinition(targetTable.columns[column.name], dialect);
if (colDef != columnDefinition(column, dialect))
{
if (dialect == Dialect.db2)
{
sql += " ALTER COLUMN " + columnDefinition(column, dialect) + "\r\n";
}
else
{
sql += sqlCmn + " ALTER COLUMN " + columnDefinition(column, dialect) + "\r\n";
}
alteration.alterColumns.Add(new ColumnAlterCandidate(column, targetTable.columns[column.name]));
}
}
else
{
if (dialect == Dialect.db2)
sql += " ADD COLUMN " + columnDefinition(column, dialect) + "\r\n";
else
{
//sql += " ADD " + columnDefinition(column, dialect) + "\r\n";
sql += sqlCmn + " ADD " + columnDefinition(column, dialect) + "\r\n";
}
alteration.addColumns.Add(column.name,column);
}
}
// Check to see if primary keys are the same
bool primaryKeySame = false;
//if (masterTable.primaryKey.constraintName == targetTable.primaryKey.constraintName)
//{
if (masterTable.primaryKey.columnNames.Count == targetTable.primaryKey.columnNames.Count)
{
primaryKeySame = true;
for (int index = 0; index < masterTable.primaryKey.columnNames.Count; index++)
{
if (masterTable.primaryKey.columnNames[index] != targetTable.primaryKey.columnNames[index])
{
primaryKeySame = false;
break;
}
if (masterTable.primaryKey.constraintName != targetTable.primaryKey.constraintName)
{
primaryKeySame = false;
break;
}
}
}
//}
alteration.ddlPrimaryKeyDrop = "";
alteration.ddlPrimaryKeyAdd = "";
if (!primaryKeySame)
{
string sqlDrop = "";
string sqlAdd = "";
if (dialect == Dialect.db2)
{
sqlDrop = "ALTER TABLE " + targetTable.name + "\r\n" + " DROP PRIMARY KEY\r\n";
sqlAdd = "ALTER TABLE " + targetTable.name + "\r\n" + " ADD " + primaryKeyConstraint(masterTable, dialect);
}
else
{
if (masterTable.primaryKey.constraintName != "")
{
sqlDrop = "ALTER TABLE " + targetTable.name + "\r\n" + " DROP CONSTRAINT " + targetTable.primaryKey.constraintName + "\r\n";
sqlAdd = "ALTER TABLE " + targetTable.name + "\r\n" + " ADD " + primaryKeyConstraint(masterTable, dialect);
}
if (masterTable.primaryKey.constraintName == "")
{
sqlDrop = "ALTER TABLE " + targetTable.name + "\r\n" + " DROP CONSTRAINT " + targetTable.primaryKey.constraintName + "\r\n";
}
}
//if (masterTable.primaryKey.constraintName == targetTable.primaryKey.constraintName)
//{
if (masterTable.primaryKey.columnNames.Count > 0)
{
if (targetTable.primaryKey.columnNames.Count > 0)
{
alteration.ddlPrimaryKeyDrop = sqlDrop;
alteration.ddlPrimaryKeyAdd = sqlAdd;
}
else
{
alteration.ddlPrimaryKeyAdd = sqlAdd;
}
}
else
{
if (targetTable.primaryKey.columnNames.Count > 0)
{
alteration.ddlPrimaryKeyDrop = sqlDrop;
}
}
//}
//else
//{
// alteration.ddlPrimaryKeyDrop = sqlDrop;
// alteration.ddlPrimaryKeyAdd = sqlAdd;
//}
}
/*
// Check to see if foreign keys are the same
bool foreignKeySame = false;
if (masterTable.foreignKey.columnNames.Count == targetTable.foreignKey.columnNames.Count)
{
foreignKeySame = true;
for (int index = 0; index < masterTable.foreignKey.columnNames.Count; index++)
{
if (masterTable.foreignKey.columnNames[index] != targetTable.foreignKey.columnNames[index])
{
foreignKeySame = false;
break;
}
}
}
alteration.ddlForeignKeyDrop = "";
alteration.ddlForeignKeyAdd = "";
if (!foreignKeySame)
{
string sqlDrop = "";
string sqlAdd = "";
for (int count = 0; count < masterTable.foreignKey.columnNames.Count; count = count + 4)
{
sqlDrop += "ALTER TABLE " + targetTable.name + "\r\n" + " DROP FOREIGN KEY\r\n" + "\r\n" + masterTable.foreignKey.columnNames[count] + ";";
sqlAdd += "ALTER TABLE " + targetTable.name + "\r\n" + " ADD " + foreignKeyConstraint(masterTable, dialect);
}
if (masterTable.foreignKey.columnNames.Count > 0)
{
if (targetTable.foreignKey.columnNames.Count > 0)
{
alteration.ddlForeignKeyDrop = sqlDrop;
alteration.ddlForeignKeyAdd = sqlAdd;
}
else
{
alteration.ddlForeignKeyAdd = sqlAdd;
}
}
else
{
if (targetTable.foreignKey.columnNames.Count > 0)
{
for (int count = 0; count < targetTable.foreignKey.columnNames.Count; count = count + 4)
{
sqlDrop += "ALTER TABLE " + targetTable.name + "\r\n" + " DROP FOREIGN KEY\r\n" + "\r\n" + targetTable.foreignKey.columnNames[count] + ";";
}
alteration.ddlForeignKeyDrop = sqlDrop;
}
}
}
*/
if (sql == noChangeSql)
sql = "";
alteration.ddl = sql;
return alteration;
}
public static string ddl(Alteration alteration, string tempTableName)
{
string masterFieldsClause = "";
string targetFieldsClause = "";
int count = 0;
foreach(Column column in alteration.commonColumns.Values)
{
if( count++ > 0 )
{
masterFieldsClause += ",";
targetFieldsClause += ",";
}
masterFieldsClause += column.name;
targetFieldsClause += column.name;
}
foreach(Mapping mapping in alteration.mappings)
{
if( count++ > 0)
{
masterFieldsClause += ",";
targetFieldsClause += ",";
}
//masterFieldsClause += mapping.fromColumn.name;
//targetFieldsClause += mapping.toColumn.name;
masterFieldsClause += mapping.toColumn.name;
targetFieldsClause += mapping.fromColumn.name;
}
string sql = "INSERT INTO " + tempTableName + "\r\n";
sql += "(" + masterFieldsClause + ")\r\n" ;
sql += "SELECT " + targetFieldsClause + "\r\n FROM " + alteration.targetTable.name + "\r\n";
return sql;
}
private bool AlterSimple(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
{
bool success = false;
string alterDdl = alteration.ddl;
if (alterDdl == "")
{
AlterPrimaryKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
success = true;
}
else
{
try
{
if (config.type == 4)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alteration.ddl + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
else
{
if (config.ddlLogging >= Configuration.DdlLogging.changes)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alteration.ddl + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
OdbcCommand alter = new OdbcCommand(alteration.ddl, target.connection);
alter.CommandTimeout = 0;
alter.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Table " + targetTable.name + " altered successfully.");
AlterPrimaryKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
success = true;
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.warning, "Exception occurred while trying to perform simple alteration of table " + targetTable.name + ".");
log.log(Logger.LogLevel.warning, ex.Message);
}
}
return success;
}
private void AlterPrimaryKey(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
{
OdbcCommand alter = new OdbcCommand();
alter.CommandType = CommandType.Text;
alter.Connection = target.connection;
if (alteration.ddlPrimaryKeyDrop != "")
{
try
{
if (config.type == 4)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alteration.ddlPrimaryKeyDrop + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
else
{
if (config.ddlLogging >= Configuration.DdlLogging.changes)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alteration.ddlPrimaryKeyDrop + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
alter.CommandText = alteration.ddlPrimaryKeyDrop;
alter.CommandTimeout = 0;
alter.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Primary key constraint " + targetTable.primaryKey.constraintName + " dropped from table " + targetTable.name + " successfully.");
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to drop primary key constraint " + masterTable.primaryKey.constraintName + " from table " + targetTable.name + ".");
log.log(Logger.LogLevel.error, ex.Message);
}
}
if (alteration.ddlPrimaryKeyAdd != "")
{
try
{
if (config.type == 4)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alteration.ddlPrimaryKeyAdd + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
else
{
if (config.ddlLogging >= Configuration.DdlLogging.changes)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alteration.ddlPrimaryKeyAdd + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
alter.CommandText = alteration.ddlPrimaryKeyAdd;
alter.CommandTimeout = 0;
//Database["DB2"].ExecuteNonQuery("call SYSPROC.ADMIN_CMD ('REORG TABLE MyTable')");
if (targetDialect != Ddl.Dialect.sqlServer)
{
alter.CommandText = "CALL SYSPROC.ADMIN_CMD ('REORG TABLE " + targetTable.name + "'); " + alter.CommandText;
}
alter.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Primary key constraint " + masterTable.primaryKey.constraintName + " added to table " + targetTable.name + " successfully.");
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to add primary key constraint " + masterTable.primaryKey.constraintName + " from table " + targetTable.name + ".");
log.log(Logger.LogLevel.error, ex.Message);
}
}
}
private void AlterForeignKey(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
{
OdbcCommand alter = new OdbcCommand();
alter.CommandType = CommandType.Text;
alter.Connection = target.connection;
if (alteration.ddlForeignKeyDrop != "")
{
try
{
alter.CommandText = alteration.ddlForeignKeyDrop;
alter.CommandTimeout = 0;
alter.ExecuteNonQuery();
for (int count = 0; count < targetTable.foreignKey.columnNames.Count; count = count + 4)
{
log.log(Logger.LogLevel.change, "Foreign key constraint " + targetTable.foreignKey.columnNames[count] + " dropped from table " + targetTable.name + " successfully.");
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to drop foreign key constraint " + masterTable.foreignKey.columnNames[0] + " from table " + targetTable.name + ".");
log.log(Logger.LogLevel.error, ex.Message);
}
}
if (alteration.ddlForeignKeyAdd != "")
{
try
{
alter.CommandText = alteration.ddlForeignKeyAdd;
alter.CommandTimeout = 0;
alter.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Foreign key constraint " + masterTable.foreignKey.columnNames[0] + " added to table " + targetTable.name + " successfully.");
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to add foreign key constraint " + masterTable.primaryKey.columnNames[0] + " from table " + targetTable.name + ".");
log.log(Logger.LogLevel.error, ex.Message);
}
}
}
private bool AlterEasy(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
{
bool canDo = true;
bool success = false;
string alterDdl = "ALTER TABLE " + targetTable.name + "\r\n";
string nochangeDdl = alterDdl;
foreach (Column col in alteration.dropColumns.Values)
alterDdl += " DROP COLUMN " + col.name + "\r\n";
foreach (Column col in alteration.addColumns.Values)
{
if (targetDialect == Ddl.Dialect.db2)
{
alterDdl += " ADD COLUMN " + Ddl.columnDefinition(col, targetDialect) + "\r\n";
}
else
{
alterDdl += " ADD " + Ddl.columnDefinition(col, targetDialect) + "\r\n";
}
}
foreach (ColumnAlterCandidate candidate in alteration.alterColumns)
{
if (candidate.masterColumn.typeName.ToUpper() != candidate.targetColumn.typeName.ToUpper())
{
canDo = false;
log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + "'s type was changed.");
break;
}
if (candidate.masterColumn.columnSize != candidate.targetColumn.columnSize)
{
if (candidate.masterColumn.typeName.ToUpper() != "VARCHAR")
{
canDo = false;
log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + "'s length was changed whose type was not VARCHAR.");
break;
}
if (candidate.masterColumn.columnSize > candidate.targetColumn.columnSize)
{
canDo = false;
log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + "'s length was changed to less than its current length.");
break;
}
if (canDo)
{
alterDdl += " ALTER COLUMN " + candidate.targetColumn.name + " SET DATA TYPE VARCHAR(" + candidate.masterColumn.columnSize + ")\r\n";
}
}
if (candidate.masterColumn.decimalDigits != candidate.targetColumn.decimalDigits)
{
canDo = false;
log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + "'s decimal digits changed.");
break;
}
if (candidate.masterColumn.nullable != candidate.targetColumn.nullable)
{
if (candidate.targetColumn.nullable)
{
canDo = false;
log.log(Logger.LogLevel.warning, "Complex alteration required because " + candidate.targetColumn.name + " that was defined as NOT NULL must be changed to NULL.");
break;
}
if (canDo)
{
//alterDdl += " ALTER COLUMN " + candidate.targetColumn.name + " SET NOT NULL\r\n";
alterDdl += " ALTER COLUMN " + candidate.targetColumn.name + " DROP NOT NULL\r\n";
}
}
if (candidate.masterColumn.defaultValue != candidate.targetColumn.defaultValue)
{
// Alter the default value here!
if (canDo)
{
if (candidate.masterColumn.defaultValue == null)
alterDdl += " ALTER COLUMN " + candidate.targetColumn.name + " DROP DEFAULT " + "\r\n";
else
alterDdl += " ALTER COLUMN " + candidate.targetColumn.name + " SET DEFAULT " + candidate.masterColumn.defaultValue + "\r\n";
}
}
}
if (canDo)
{
if (alterDdl == nochangeDdl)
{
AlterPrimaryKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
success = true;
}
else
{
try
{
if (config.type == 4)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alterDdl + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
else
{
if (config.ddlLogging >= Configuration.DdlLogging.changes)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alterDdl + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
OdbcCommand alter = new OdbcCommand(alterDdl, target.connection);
alter.CommandTimeout = 0;
alter.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Table " + targetTable.name + " altered successfully.");
AlterPrimaryKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
success = true;
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.warning, "Exception occurred while trying to alter table " + targetTable.name + ".");
log.log(Logger.LogLevel.warning, ex.Message);
log.log(Logger.LogLevel.warning, "Complex alteration will be attempted for table " + targetTable.name + ".");
}
}
}
if (canDo)
{
if (alterDdl == nochangeDdl)
{
//AlterForeignKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
//success = true;
}
else
{
try
{
if (config.type == 4)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alterDdl + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
else
{
if (config.ddlLogging >= Configuration.DdlLogging.changes)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, alterDdl + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
OdbcCommand alter = new OdbcCommand(alterDdl, target.connection);
alter.CommandTimeout = 0;
alter.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Table " + targetTable.name + " altered successfully.");
//AlterForeignKey(alteration, masterTable, targetTable, targetDialect, targetConnection);
//success = true;
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.warning, "Exception occurred while trying to alter table " + targetTable.name + ".");
log.log(Logger.LogLevel.warning, ex.Message);
log.log(Logger.LogLevel.warning, "Complex alteration will be attempted for table " + targetTable.name + ".");
}
}
}
return success;
}
private void AlterComplex(Alteration alteration, Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
{
string PrimaryColumnName = "";
if (config.type != 4)
{
bool failureDetected = false;
// Make sure that the META_DB_TRANSFER_TEMP is deleted
OdbcCommand command = new OdbcCommand("DROP TABLE META_DB_DEPLOY_TRANSFER_TEMP", target.connection);
try
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
catch {/*Do Nothing*/}
// Drop the Primary key constraint in target table to create the Meta Temp table in SQL server database 20110811
if (targetDialect == Ddl.Dialect.sqlServer)
{
PrimaryColumnName = findPrimaryKeyColumnNameSqlServer(targetTable);
if (PrimaryColumnName != "")
{
command.CommandText = " ALTER TABLE " + targetTable.name + " DROP CONSTRAINT " + PrimaryColumnName;
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
// CREATE the META_DB_TRANSFER_TEMP table with the new target schema
try
{
command.CommandText = Ddl.ddl(masterTable, targetDialect).Replace("CREATE TABLE " + masterTable.name, "CREATE TABLE META_DB_DEPLOY_TRANSFER_TEMP");
if (targetDialect == Ddl.Dialect.sqlServer)
{
command.CommandText = command.CommandText.Replace("getdate", "getdate()");
}
//TableSapce Change
if (targetDialect == Ddl.Dialect.db2)
{
findTableSpaceSQL(masterTable.name);
}
//TableSapce Change
command.CommandTimeout = 0;
command.CommandText += TableSpaceSQL;
command.ExecuteNonQuery();
log.log(Logger.LogLevel.progress, "Transfer temporary table for complex alteration of " + masterTable.name + " created.");
}
catch (Exception ex)
{
failureDetected = true;
log.log(Logger.LogLevel.error, "Exception occurred while trying to perform complex alteration of table " + masterTable.name + ".");
log.log(Logger.LogLevel.error, "CREATE TABLE META_DB_DEPLOY_TRANSFER_TEMP failed.");
log.log(Logger.LogLevel.error, ex.Message);
}
if (!failureDetected)
{
try
{
command.CommandText = Ddl.ddl(alteration, "META_DB_DEPLOY_TRANSFER_TEMP");
if (targetDialect == Ddl.Dialect.sqlServer)
{
if (PrimaryColumnName != "")
{
command.CommandText = "SET IDENTITY_INSERT META_DB_DEPLOY_TRANSFER_TEMP ON;" + command.CommandText + "; SET IDENTITY_INSERT META_DB_DEPLOY_TRANSFER_TEMP OFF";
}
else
{
command.CommandText = command.CommandText + ";";
}
//command.CommandText = "SET IDENTITY_INSERT META_DB_DEPLOY_TRANSFER_TEMP ON;" + command.CommandText + "; SET IDENTITY_INSERT META_DB_DEPLOY_TRANSFER_TEMP OFF";
}
if (config.ddlLogging >= Configuration.DdlLogging.changes)
log.log(Logger.LogLevel.ddlChange, command.CommandText);
command.CommandTimeout = 0;
int insertCount = command.ExecuteNonQuery();
log.log(Logger.LogLevel.progress, insertCount + " rows were successfully transfered to temp table from table " + masterTable.name + ".");
}
catch (Exception ex)
{
failureDetected = true;
log.log(Logger.LogLevel.error, "Exception occurred while trying to perform complex alteration of table " + masterTable.name + ".");
log.log(Logger.LogLevel.error, "Transfer of data to tempory table failed.");
log.log(Logger.LogLevel.error, ex.Message);
}
}
if (!failureDetected)
{
try
{
command.CommandText = "DROP TABLE " + targetTable.name;
command.CommandTimeout = 0;
command.ExecuteNonQuery();
log.log(Logger.LogLevel.progress, "Successfully dropped " + targetTable.name + ".");
}
catch (Exception ex)
{
failureDetected = true;
log.log(Logger.LogLevel.error, "Exception occurred while trying to perform complex alteration of table " + masterTable.name + ".");
log.log(Logger.LogLevel.error, "Drop of original table " + targetTable.name + " failed.");
log.log(Logger.LogLevel.error, ex.Message);
}
}
if (!failureDetected)
{
try
{
if (targetDialect == Ddl.Dialect.db2)
command.CommandText = "RENAME TABLE META_DB_DEPLOY_TRANSFER_TEMP TO " + targetTable.name;
else if (targetDialect == Ddl.Dialect.sqlServer)
command.CommandText = "SP_RENAME 'META_DB_DEPLOY_TRANSFER_TEMP','" + targetTable.name + "'";
command.CommandTimeout = 0;
command.ExecuteNonQuery();
log.log(Logger.LogLevel.progress, "Successfully renamed temp table to " + targetTable.name + ".");
}
catch (Exception ex)
{
failureDetected = true;
log.log(Logger.LogLevel.error, "Exception occurred while trying to perform complex alteration of table " + masterTable.name + ".");
log.log(Logger.LogLevel.error, "RENAMING OF TEMP TABLE (META_DB_DEPLY_TRANSFER_TEMP) TO " + targetTable.name + " FAILED.");
log.log(Logger.LogLevel.error, "FAILURE TO FIX THE PROBLEM MANUALLY PRIOR TO THE NEXT RUN OF DB DEPLOY WILL RESULT IN ALL DATA THAT WAS IN " + targetTable.name + "!");
log.log(Logger.LogLevel.error, ex.Message);
}
}
if (!failureDetected)
{
log.log(Logger.LogLevel.progress, "Complex alteration of " + targetTable.name + " completed successfully.");
}
}
}
