private void _dropColumn(APDatabase db, APGenTable table, columninfo info) { if (info.dfname != "") { _dropConstraint(db, table, info.dfname); } DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop column {1}", table.TableName, info.colname); dbCmd.ExecuteNonQuery(); }
private Dictionary <string, columninfo> _getColumns(APDatabase db, APGenTable table) { DbCommand dbCmd = db.CreateSqlCommand( @"select col.colid, col.name as colname, tp.name as typename, tp.variable, col.length, col.xprec, col.xscale, col.isnullable, df.name as dfname, df.definition as dfvalue, id.is_identity from syscolumns as col inner join systypes as tp on col.xtype = tp.xtype and tp.name <> 'sysname' left join sys.default_constraints as df on col.cdefault = df.object_id left join sys.identity_columns as id on col.colid = id.column_id and id.object_id = col.id where col.id = object_id('{0}') order by col.colid", table.TableName); Dictionary <string, columninfo> dbColumns = new Dictionary <string, columninfo>(); using (IDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { int idx = 0; columninfo data = new columninfo() { colid = Convert.ToInt32(reader.GetValue(idx++)), colname = Convert.ToString(reader.GetValue(idx++)), typename = Convert.ToString(reader.GetValue(idx++)), variable = Convert.ToBoolean(reader.GetValue(idx++)), length = Convert.ToInt32(reader.GetValue(idx++)), xprec = Convert.ToInt32(reader.GetValue(idx++)), xscale = Convert.ToInt32(reader.GetValue(idx++)), isnullable = Convert.ToBoolean(reader.GetValue(idx++)), dfname = Convert.ToString(reader.GetValue(idx++)), dfvalue = Convert.ToString(reader.GetValue(idx++)), is_identity = reader.IsDBNull(idx++) ? false : true, }; data.Resolve(); dbColumns.Add(data.colname, data); } } return(dbColumns); }
private Dictionary <string, columninfo> _getColumns(APDatabase db, APGenTable table) { OracleCommand dbCmd = db.CreateSqlCommand( @"select column_id, column_name, data_type, data_length, nvl(data_precision, 0), nvl(data_scale, 0), nullable, nvl(default_length, 0), data_default from user_tab_columns where table_name = '{0}' order by column_id", table.TableName.ToUpper()) as OracleCommand; dbCmd.InitialLONGFetchSize = -1; Dictionary <string, columninfo> dbColumns = new Dictionary <string, columninfo>(StringComparer.InvariantCultureIgnoreCase); using (OracleDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { int idx = 0; columninfo data = new columninfo() { colid = Convert.ToInt32(reader.GetValue(idx++)), colname = Convert.ToString(reader.GetValue(idx++)), typename = Convert.ToString(reader.GetValue(idx++)).ToLower(), length = Convert.ToInt32(reader.GetValue(idx++)), xprec = Convert.ToInt32(reader.GetValue(idx++)), xscale = Convert.ToInt32(reader.GetValue(idx++)), isnullable = Convert.ToString(reader.GetValue(idx++)) == "Y", dflength = Convert.ToInt32(reader.GetValue(idx++)), dfvalue = "", }; if (data.dflength > 0) { data.dfvalue = reader.GetString(idx++).Trim(); } data.Resolve(); dbColumns.Add(data.colname, data); } } return(dbColumns); }
private void ModifyTable(APDatabase db, APGenTable table) { Dictionary <string, columninfo> dbColumns = _getColumns(db, table); Dictionary <string, indexinfo> dbIndexes = _getIndexes(db, table); bool isTableEmpty = _isTableEmpty(db, table); // analysis columns List <APGenColumn> listCreateColumn = new List <APGenColumn>(); List <APGenColumn> listModifyColumn = new List <APGenColumn>(); Dictionary <string, columninfo> deleteColumns = new Dictionary <string, columninfo>(dbColumns, StringComparer.InvariantCultureIgnoreCase); foreach (APGenColumn column in table.Columns) { _setScanColumn(column.ColumnName); string colname = column.ColumnName; if (!dbColumns.ContainsKey(colname)) { // db has not this column, wait for create. listCreateColumn.Add(column); } else { deleteColumns.Remove(colname); columninfo colinfo = dbColumns[colname]; if (column.IsNullable != colinfo.isnullable || column.DBDefaultValue != colinfo.dfvalue || DBTypeName(column) != colinfo.GetTypeFullName() // no safe mode to change identity, so ingone change this // || (column.IdentityType == APColumnIdentityType.Database ^ colinfo.is_identity) ) { listModifyColumn.Add(column); } } } // analysis indexes List <APGenIndex> listCreateIndex = new List <APGenIndex>(); foreach (APGenIndex index in table.Indexes) { string ix_name = index.Name; if (!dbIndexes.ContainsKey(ix_name)) { // db has not this index, wait for create. listCreateIndex.Add(index); } else { // db has this index and columns according fitted, then to nothing. // elsewise, wait fo modify this index. // drop in db indexes residual in dbIndexes. indexinfo info = dbIndexes[ix_name]; string ix_keys = IndKeys(table, index, false); if (info.indkeys.Equals(ix_keys, StringComparison.InvariantCultureIgnoreCase)) { bool maybe = false; foreach (APGenColumn column in listModifyColumn) { if (info.columns.ContainsKey(column.ColumnName)) { listCreateIndex.Add(index); maybe = true; break; } } if (!maybe) { dbIndexes.Remove(ix_name); } } else { listCreateIndex.Add(index); } } } // analysis uniques List <APGenIndex> listCreateUnique = new List <APGenIndex>(); foreach (APGenIndex index in table.Uniques) { string ix_name = index.Name; if (!dbIndexes.ContainsKey(ix_name)) { // db has not this unique, wait for create. listCreateUnique.Add(index); } else { // db has this unique, then to nothing. // elsewise, wait fo modify this unique. // drop in db uniques residual in dbIndexes. indexinfo info = dbIndexes[ix_name]; string uq_keys = IndKeys(table, index, true); if (info.indkeys.Equals(uq_keys, StringComparison.InvariantCultureIgnoreCase)) { bool maybe = false; foreach (APGenColumn column in listModifyColumn) { if (info.columns.ContainsKey(column.ColumnName)) { listCreateUnique.Add(index); maybe = true; break; } } if (!maybe) { dbIndexes.Remove(ix_name); } } else { listCreateUnique.Add(index); } } } // 1. dbIndexes for 'drop', but PK_ index analysis columns. // 2. listCreateIndex for 'create'. // 2. listCreateUnique for 'create'. // process string pkKeys = ""; foreach (APGenColumn column in table.PrimaryKeyColumns) { if (pkKeys != "") { pkKeys += ","; } pkKeys += column.ColumnName; } // 1. drop indexes bool needAddPrimary = true; foreach (indexinfo info in dbIndexes.Values) { if (info.indtype == indexType.Primary && info.indkeys.Equals(pkKeys, StringComparison.InvariantCultureIgnoreCase)) { needAddPrimary = false; } else { _dropIndex(db, table, info); } } // 2. drop columns foreach (columninfo info in deleteColumns.Values) { _dropColumn(db, table, info); } // 3. modify columns foreach (APGenColumn column in listModifyColumn) { _alterColumn(db, table, column, dbColumns[column.ColumnName]); } // 4. create columns foreach (APGenColumn column in listCreateColumn) { _createColumn(db, table, column, isTableEmpty); } // 5. mayby primary key if (needAddPrimary) { _createPrimaryKey(db, table); } // 6. create indexes foreach (APGenIndex index in listCreateIndex) { _createIndex(db, table, index); } // 7. create unique foreach (APGenIndex unique in listCreateUnique) { _createUnique(db, table, unique); } }
private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info) { DbCommand dbCmd; int totalCount, nullCount = 0; dbCmd = db.CreateSqlCommand("select count(*) from {0}", table.TableName, column.ColumnName); totalCount = Convert.ToInt32(dbCmd.ExecuteScalar()); if (totalCount > 0) { dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName); nullCount = Convert.ToInt32(dbCmd.ExecuteScalar()); } if (totalCount > 0) { if (!_sameType(DBTypeName(column), info.GetTypeFullName()) && nullCount != totalCount) { // change db type, the column value must be null if (!info.isnullable) { dbCmd = db.CreateSqlCommand("alter table {0} modify {1} null", table.TableName, column.ColumnName); dbCmd.ExecuteNonQuery(); info.isnullable = true; } _tempSql(String.Format("update {0} set {1} = null where {1} is not null", table.TableName, column.ColumnName)); nullCount = totalCount; } } string sqlPadding = ""; if (DBTypeName(column) != info.GetTypeFullName()) { sqlPadding += " " + DBTypeName(column); } if (column.DBDefaultValue != info.dfvalue) { sqlPadding += " " + DF_constraint(table, column); } bool lazyNotNullable = false; if (column.IsNullable != info.isnullable) { if (column.IsNullable || nullCount == 0) { sqlPadding += " " + IsNullableString(column); } else { lazyNotNullable = true; } } if (sqlPadding != "") { dbCmd = db.CreateSqlCommand("alter table {0} modify {1}", table.TableName, column.ColumnName); dbCmd.CommandText += sqlPadding; dbCmd.ExecuteNonQuery(); } if (lazyNotNullable) { string dfv = column.DBDefaultValue; if (dfv == "") { dfv = "0"; } _tempSql(String.Format("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, dfv)); dbCmd = db.CreateSqlCommand("alter table {0} modify {1} not null", table.TableName, column.ColumnName); dbCmd.ExecuteNonQuery(); } // alter table table_nae modify column_name type_name default dfvalue [not] null // go }
private Dictionary<string, columninfo> _getColumns(APDatabase db, APGenTable table) { DbCommand dbCmd = db.CreateSqlCommand( @"select col.colid, col.name as colname, tp.name as typename, tp.variable, col.length, col.xprec, col.xscale, col.isnullable, df.name as dfname, df.definition as dfvalue, id.is_identity from syscolumns as col inner join systypes as tp on col.xtype = tp.xtype and tp.name <> 'sysname' left join sys.default_constraints as df on col.cdefault = df.object_id left join sys.identity_columns as id on col.colid = id.column_id and id.object_id = col.id where col.id = object_id('{0}') order by col.colid", table.TableName); Dictionary<string, columninfo> dbColumns = new Dictionary<string, columninfo>(); using (IDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { int idx = 0; columninfo data = new columninfo() { colid = Convert.ToInt32(reader.GetValue(idx++)), colname = Convert.ToString(reader.GetValue(idx++)), typename = Convert.ToString(reader.GetValue(idx++)), variable = Convert.ToBoolean(reader.GetValue(idx++)), length = Convert.ToInt32(reader.GetValue(idx++)), xprec = Convert.ToInt32(reader.GetValue(idx++)), xscale = Convert.ToInt32(reader.GetValue(idx++)), isnullable = Convert.ToBoolean(reader.GetValue(idx++)), dfname = Convert.ToString(reader.GetValue(idx++)), dfvalue = Convert.ToString(reader.GetValue(idx++)), is_identity = reader.IsDBNull(idx++) ? false : true, }; data.Resolve(); dbColumns.Add(data.colname, data); } } return dbColumns; }
private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info) { if (info.dfname != "") _dropConstraint(db, table, info.dfname); DbCommand dbCmd; // no safe mode to change identity, so ingone this, throw sql exception. if (info.isnullable && !column.IsNullable) { dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName); if ((int)dbCmd.ExecuteScalar() > 0) { // when column nullable change to not nullable and has data, // set default value. dbCmd = db.CreateSqlCommand("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, info.GetDefaultValue()); dbCmd.ExecuteNonQuery(); } } dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}", table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column)); dbCmd.ExecuteNonQuery(); if (column.DBDefaultValue != "") { dbCmd = db.CreateSqlCommand("alter table {0} add {1} for {2}", table.TableName, DF_constraint(table, column), column.ColumnName); dbCmd.ExecuteNonQuery(); } // alter table table_nae alter column column_name type_name [not] null // go // alter table add constraint DF_tablename_columnname default dfvalue for column_name // go }
private void _dropColumn(APDatabase db, APGenTable table, columninfo info) { if (info.dfname != "") _dropConstraint(db, table, info.dfname); DbCommand dbCmd = db.CreateSqlCommand("alter table {0} drop column {1}", table.TableName, info.colname); dbCmd.ExecuteNonQuery(); }
private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info) { if (info.dfname != "") { _dropConstraint(db, table, info.dfname); } DbCommand dbCmd; // no safe mode to change identity, so ingone this, throw sql exception. if (info.isnullable && !column.IsNullable) { dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName); if ((int)dbCmd.ExecuteScalar() > 0) { // when column nullable change to not nullable and has data, // set default value. dbCmd = db.CreateSqlCommand("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, info.GetDefaultValue()); dbCmd.ExecuteNonQuery(); } } dbCmd = db.CreateSqlCommand("alter table {0} alter column {1} {2} {3}", table.TableName, column.ColumnName, DBTypeName(column), IsNullableString(column)); dbCmd.ExecuteNonQuery(); if (column.DBDefaultValue != "") { dbCmd = db.CreateSqlCommand("alter table {0} add {1} for {2}", table.TableName, DF_constraint(table, column), column.ColumnName); dbCmd.ExecuteNonQuery(); } // alter table table_nae alter column column_name type_name [not] null // go // alter table add constraint DF_tablename_columnname default dfvalue for column_name // go }
private Dictionary<string, columninfo> _getColumns(APDatabase db, APGenTable table) { OracleCommand dbCmd = db.CreateSqlCommand( @"select column_id, column_name, data_type, data_length, nvl(data_precision, 0), nvl(data_scale, 0), nullable, nvl(default_length, 0), data_default from user_tab_columns where table_name = '{0}' order by column_id", table.TableName.ToUpper()) as OracleCommand; dbCmd.InitialLONGFetchSize = -1; Dictionary<string, columninfo> dbColumns = new Dictionary<string, columninfo>(StringComparer.InvariantCultureIgnoreCase); using (OracleDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { int idx = 0; columninfo data = new columninfo(){ colid = Convert.ToInt32(reader.GetValue(idx++)), colname = Convert.ToString(reader.GetValue(idx++)), typename = Convert.ToString(reader.GetValue(idx++)).ToLower(), length = Convert.ToInt32(reader.GetValue(idx++)), xprec = Convert.ToInt32(reader.GetValue(idx++)), xscale = Convert.ToInt32(reader.GetValue(idx++)), isnullable = Convert.ToString(reader.GetValue(idx++)) == "Y", dflength = Convert.ToInt32(reader.GetValue(idx++)), dfvalue = "", }; if (data.dflength > 0) data.dfvalue = reader.GetString(idx++).Trim(); data.Resolve(); dbColumns.Add(data.colname, data); } } return dbColumns; }
private void _alterColumn(APDatabase db, APGenTable table, APGenColumn column, columninfo info) { DbCommand dbCmd; int totalCount, nullCount = 0; dbCmd = db.CreateSqlCommand("select count(*) from {0}", table.TableName, column.ColumnName); totalCount = Convert.ToInt32(dbCmd.ExecuteScalar()); if (totalCount > 0) { dbCmd = db.CreateSqlCommand("select count(*) from {0} where {1} is null", table.TableName, column.ColumnName); nullCount = Convert.ToInt32(dbCmd.ExecuteScalar()); } if (totalCount > 0) { if (!_sameType(DBTypeName(column), info.GetTypeFullName()) && nullCount != totalCount) { // change db type, the column value must be null if (!info.isnullable) { dbCmd = db.CreateSqlCommand("alter table {0} modify {1} null", table.TableName, column.ColumnName); dbCmd.ExecuteNonQuery(); info.isnullable = true; } _tempSql(String.Format("update {0} set {1} = null where {1} is not null", table.TableName, column.ColumnName)); nullCount = totalCount; } } string sqlPadding = ""; if (DBTypeName(column) != info.GetTypeFullName()) sqlPadding += " " + DBTypeName(column); if (column.DBDefaultValue != info.dfvalue) sqlPadding += " " + DF_constraint(table, column); bool lazyNotNullable = false; if (column.IsNullable != info.isnullable) { if (column.IsNullable || nullCount == 0) sqlPadding += " " + IsNullableString(column); else lazyNotNullable = true; } if (sqlPadding != "") { dbCmd = db.CreateSqlCommand("alter table {0} modify {1}", table.TableName, column.ColumnName); dbCmd.CommandText += sqlPadding; dbCmd.ExecuteNonQuery(); } if (lazyNotNullable) { string dfv = column.DBDefaultValue; if (dfv == "") dfv = "0"; _tempSql(String.Format("update {0} set {1} = {2} where {1} is null", table.TableName, column.ColumnName, dfv)); dbCmd = db.CreateSqlCommand("alter table {0} modify {1} not null", table.TableName, column.ColumnName); dbCmd.ExecuteNonQuery(); } // alter table table_nae modify column_name type_name default dfvalue [not] null // go }