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