public Index(Table table, string name, bool nonUnique, short type)
{
this.table = table;
this.name = name;
this.nonUnique = nonUnique;
this.type = type;
}
public PrimaryKey(Table table)
{
this.table = table;
}
public static string primaryKeyConstraint(Table table, Dialect dialect)
{
string sql = "";
if (table.primaryKey.columnNames.Count > 0)
{
if (dialect == Dialect.db2 || dialect == Dialect.generic)
{
sql += " CONSTRAINT " + table.primaryKey.constraintName + " PRIMARY KEY (";
}
else
{
sql += " CONSTRAINT [" + table.primaryKey.constraintName + "] PRIMARY KEY (";
}
//sql += " PRIMARY KEY (";
int count = 0;
foreach (string columnName in table.primaryKey.columnNames)
{
if (count++ > 0)
sql += ",";
sql += "\"" + columnName + "\"";
}
sql += ")";
}
return sql;
}
public static string foreignKeyConstraint(Table table, Dialect dialect)
{
string sql = "";
if (table.foreignKey.columnNames.Count > 0)
{
if (dialect == Dialect.db2 || dialect == Dialect.generic)
{
sql += " CONSTRAINT " + table.foreignKey.columnNames[0];
}
else
{
sql += " CONSTRAINT [" + table.foreignKey.columnNames[0] + "]";
}
sql += " FOREIGN KEY (";
string columnName = "";
int countForeignKeyTotal = 1;
int countForeignKey = 1;
for (int count = 1; count < table.foreignKey.columnNames.Count; count++)
{
columnName = table.foreignKey.columnNames[count];
if (countForeignKey == 2)
{
sql += ") REFERENCES ";
sql += "\"" + columnName.Trim() + "\"";
countForeignKey++;
countForeignKeyTotal++;
}
else if (countForeignKey == 3)
{
sql += " (";
sql += "\"" + columnName.Trim() + "\"";
countForeignKey++;
countForeignKeyTotal++;
}
else if (countForeignKey == 4)
{
if (countForeignKeyTotal < table.foreignKey.columnNames.Count)
{
if (dialect == Dialect.db2 || dialect == Dialect.generic)
{
sql += "), CONSTRAINT " + table.foreignKey.columnNames[countForeignKeyTotal];
}
else
{
sql += "), CONSTRAINT [" + table.foreignKey.columnNames[countForeignKeyTotal] + "]";
}
sql += " FOREIGN KEY (";
}
countForeignKey = 1;
countForeignKeyTotal++;
}
else
{
sql += "\"" + columnName.Trim() + "\"";
countForeignKey++;
countForeignKeyTotal++;
}
}
}
sql += ") ";
return sql;
}
public static string ddlTemp(Table table, Dialect dialect)
{
//string sql = "CREATE TABLE " + table.schema + "." + table.name + "\r\n(\r\n";
string sql = "CREATE TABLE " + table.name + "\r\n(\r\n";
int ordinal = 0;
foreach (Column column in table.columns.Values)
{
if (ordinal++ != 0)
sql += ",\r\n";
sql += columnDefinition(column, dialect);
}
return sql;
}
public static string ddl(Table table, Dialect dialect)
{
//string sql = "CREATE TABLE " + table.schema + "." + table.name + "\r\n(\r\n";
string sql = "CREATE TABLE " + table.name + "\r\n(\r\n";
int ordinal = 0;
foreach (Column column in table.columns.Values)
{
if (ordinal++ != 0)
sql += ",\r\n";
sql += columnDefinition(column, dialect);
}
if (table.primaryKey.columnNames.Count > 0)
sql += ",\r\n" + primaryKeyConstraint(table,dialect);
//if (table.foreignKey.columnNames.Count > 0)
// sql += ",\r\n" + foreignKeyConstraint(table, dialect); sql += "\r\n)\r\n";
sql += "\r\n)\r\n";
if (dialect != Dialect.db2)
{
sql = sql.Replace("VARCHAR(0)", "VARCHAR(max)");
sql = sql.Replace("VARBINARY(0)", "VARBINARY(max)");
}
return sql;
}
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.");
}
}
}
public Alteration(Table masterTable, Table targetTable, Ddl.Dialect dialect)
{
this.masterTable = masterTable;
this.targetTable = targetTable;
this.dialect = dialect;
}
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 string findPrimaryKeyColumnNameSqlServer(Table table)
{
OdbcCommand command = new OdbcCommand();
command.Connection = target.connection;
command.CommandType = CommandType.Text;
// GET Primary Key Name
command.CommandText = " SELECT i.name AS IndexName FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic " +
" ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id WHERE i.is_primary_key = 1 " +
" and OBJECT_NAME(ic.OBJECT_ID) = '" + table.name + "' ";
object value = command.ExecuteScalar();
string pkName = "";
//if (value == DBNull.Value)
if (value == null)
pkName = "";
else
pkName = (string)(value);
return pkName;
}
private string findPrimaryKeyColumnName(Table table)
{
OdbcCommand command = new OdbcCommand();
command.Connection = target.connection;
command.CommandType = CommandType.Text;
// GET Primary Key Name
command.CommandText = " SELECT K.COLNAME " +
" FROM SYSCAT.KEYCOLUSE K, SYSCAT.TABCONST T " +
" WHERE K.CONSTNAME = T.CONSTNAME AND K.TABNAME = T.TABNAME " +
" AND K.TABSCHEMA = '" + table.schema + "'" +
" AND K.TABNAME = '" + table.name + "' " +
" AND T.TYPE = 'P'";
object value = command.ExecuteScalar();
string pkName = "";
//if (value == DBNull.Value)
if (value == null)
pkName = "";
else
pkName = (string)(value);
return pkName;
}
private void DropAndCreate(Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
{
log.log(Logger.LogLevel.change, "Dropping and recreating table " + targetTable.name + ".");
try
{
if (config.type == 4)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, "drop table " + targetTable.name + ";");
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, "drop table " + targetTable.name + ";");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
OdbcCommand drop = new OdbcCommand();
drop.Connection = targetConnection;
drop.CommandType = CommandType.Text;
drop.CommandText = "drop table " + targetTable.name;
drop.CommandTimeout = 0;
drop.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Table " + targetTable.name + " dropped successfully.");
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to drop table " + targetTable.name + ".");
log.log(Logger.LogLevel.error, ex.Message);
}
try
{
//TableSapce Change
if (targetDialect == Ddl.Dialect.db2)
{
findTableSpaceSQL(masterTable.name);
}
//TableSapce Change
if (config.type == 4)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, (Ddl.ddl(masterTable, Ddl.Dialect.generic) + TableSpaceSQL + ";").Replace("DATETIME", "TIMESTAMP"));
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, (Ddl.ddl(masterTable, Ddl.Dialect.generic) + TableSpaceSQL + ";").Replace("DATETIME", "TIMESTAMP"));
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
//OdbcCommand create = new OdbcCommand(Ddl.ddl(masterTable, targetDialect), targetConnection);
OdbcCommand create = new OdbcCommand();
if (targetDialect == Ddl.Dialect.sqlServer)
create = new OdbcCommand(Ddl.ddl(masterTable, targetDialect).Replace("getdate", "getdate()"), targetConnection);
else
create = new OdbcCommand(Ddl.ddl(masterTable, targetDialect), targetConnection);
create.CommandTimeout = 0;
create.CommandText += TableSpaceSQL;
create.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Table " + targetTable.name + " created successfully.");
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to create table " + masterTable.name + ".");
log.log(Logger.LogLevel.error, ex.Message);
}
}
public ForeignKey(Table table)
{
this.table = table;
}
public Column(
Table table,
string name,
//OdbcType type,
string typeName,
int columnSize,
short decimalDigits,
bool nullable,
string defaultValue
)
{
this.table = table;
this.name = name;
this.typeName = typeName;
if (this.typeName == "CHAR () FOR BIT DATA") // DB2
{
this.typeName = "CHAR";
this.specialTypeExtender = "FOR BIT DATA";
}
if (this.typeName == "VARCHAR () FOR BIT DATA") // DB2
{
this.typeName = "VARCHAR";
this.specialTypeExtender = "FOR BIT DATA";
}
if (this.typeName == "INTEGER") // DB2
this.typeName = "INT";
else if (this.typeName == "CLOB") // DB2
this.typeName = "TEXT";
else if (this.typeName == "BLOB") // DB2
this.typeName = "BINARY";
else if (this.typeName == "TIMESTAMP" && table.database.dialect == Ddl.Dialect.db2)
this.typeName = "DATETIME";
else if (this.typeName == "int identity") // MS SQL 2005
this.typeName = "int";
else if (this.typeName == "bigint identity") // MS SQL 2005
this.typeName = "bigint";
else if (this.typeName == "float") // MS SQL 2005
this.typeName = "double";
//else if (this.typeName == "money") // MS SQL 2005
// this.typeName = "decimal";
else if (this.typeName == "smallint identity") // MS SQL 2008 R2
this.typeName = "smallint";
else if (this.typeName == "tinyint identity") // MS SQL 2008 R2
this.typeName = "tinyint";
else if (this.typeName == "datetime2") // MS SQL 2008 R2
{
this.typedateTime2 = true;
this.typeName = "datetime";
}
else if (this.typeName == "datetimeoffset") // MS SQL 2008 R2
{
this.typedateTimeoffset = true;
this.typeName = "datetime";
}
else if (this.typeName == "geography") // MS SQL 2008 R2
{
this.typegeography = true;
this.typeName = "Text";
}
else if (this.typeName == "geometry") // MS SQL 2008 R2
{
this.typegeometry = true;
this.typeName = "Text";
}
else if (this.typeName == "hierarchyid") // MS SQL 2008 R2
{
this.typehierarchyid = true;
this.typeName = "Text";
}
else if (this.typeName == "smallmoney") // MS SQL 2008 R2
{
this.typesmallmoney = true;
this.typeName = "Double";
}
else if (this.typeName == "money") // MS SQL 2008 R2
{
this.typemoney = true;
this.typeName = "Double";
}
else if (this.typeName == "sql_variant") // MS SQL 2008 R2
{
this.typesql_variant = true;
this.typeName = "Text";
}
else if (this.typeName == "xml") // MS SQL 2008 R2
{
this.typexml = true;
this.typeName = "Text";
}
this.type = (OdbcType)Enum.Parse(typeof(OdbcType), this.typeName, true);
this.columnSize = columnSize;
this.decimalDigits = decimalDigits;
this.nullable = nullable;
this.defaultValue = defaultValue;
parameter = new OdbcParameter("@" + this.name, this.type);
if(this.type == OdbcType.Timestamp)
parameter.DbType = DbType.DateTime;
identity = DbIdentity.check(this);
}
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 Trigger(Table table, string name, string TriggerTable)
{
this.table = table;
this.name = name;
this.TriggerTable = TriggerTable;
}
private void Alter(Table masterTable, Table targetTable, Ddl.Dialect targetDialect, OdbcConnection targetConnection)
{
//TODO: 1. Check for appropriate return value here.
Alteration alteration = Ddl.alter(masterTable, targetTable, targetDialect);
// Check for simple alteration
if (alteration.alterColumns.Count == 0 &&
((alteration.addColumns.Count > 0 && alteration.dropColumns.Count == 0) ||
(alteration.addColumns.Count == 0 && alteration.dropColumns.Count > 0))
)
{
if (!AlterSimple(alteration, masterTable, targetTable, targetDialect, targetConnection))
{
AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
}
}
else
{
// Complex alteration
// Are we moving data from the source?
//if (ProcessData(masterTable.name))
if (ProcessData(masterTable.name) && (!config.filterTables.ContainsKey(masterTable.name)))
{
// Yes - drop the current table and recreate it, data will be copied over later via standard process
DropAndCreate(masterTable, targetTable, targetDialect, targetConnection);
}
else
{
// No - need to copy the data in the current table to the newly created one...
// See if we need to get a mapping from the user...
int mappedCount = 0;
if (alteration.addColumns.Count > 0 && alteration.dropColumns.Count > 0)
{
MappingForm form = new MappingForm();
form.tableNameText.Text = masterTable.name;
foreach (Column column in alteration.dropColumns.Values)
form.oldColumnList.Items.Add(column);
foreach (Column column in alteration.addColumns.Values)
form.newColumnList.Items.Add(column);
if (form.ShowDialog() != DialogResult.OK)
{
log.log(Logger.LogLevel.error, "User cancelled out of a required mapping. Alteration of table " + targetTable.name + " skipped.");
return;
}
mappedCount = form.mappedColumnList.Items.Count;
////Update the alteration structure from the mapping form editor
//alteration.dropColumns.Clear();
//foreach (Column column in form.oldColumnList.Items)
// alteration.dropColumns.Add(column.name, column);
//alteration.addColumns.Clear();
//foreach (Column column in form.newColumnList.Items)
// alteration.addColumns.Add(column.name, column);
alteration.mappings.Clear();
foreach (Mapping mapping in form.mappedColumnList.Items)
alteration.mappings.Add(mapping);
}
if (mappedCount == 0 && alteration.alterColumns.Count == 0)
{
if (Ddl.ddlTemp(masterTable, Ddl.Dialect.generic) == Ddl.ddlTemp(targetTable, Ddl.Dialect.generic))
{
if (!AlterSimple(alteration, masterTable, targetTable, targetDialect, targetConnection))
{
AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
}
}
else
{
AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
}
}
else
{
if (targetDialect == Ddl.Dialect.sqlServer)
{
if (Ddl.ddlTemp(masterTable, Ddl.Dialect.generic) == Ddl.ddlTemp(targetTable, Ddl.Dialect.generic))
{
if (!AlterSimple(alteration, masterTable, targetTable, targetDialect, targetConnection))
{
AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
}
}
else
{
AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
}
}
//else if (mappedCount != 0 && alteration.mappings.Count != 0)
//{
// if (!AlterEasy(alteration, masterTable, targetTable, targetDialect, targetConnection))
// {
// AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
// }
//}
else if (mappedCount >= 0)
{
if (Ddl.ddlTemp(masterTable, Ddl.Dialect.generic) == Ddl.ddlTemp(targetTable, Ddl.Dialect.generic))
{
if (!AlterEasy(alteration, masterTable, targetTable, targetDialect, targetConnection))
{
AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
}
}
else
{
AlterComplex(alteration, masterTable, targetTable, targetDialect, targetConnection);
}
}
}
}
}
}
