private void _dropIndex(APDatabase db, APGenTable table, indexinfo info) { if (info.indtype != indexType.Index) { _dropConstraint(db, table, info.indname); } else { DbCommand dbCmd = db.CreateSqlCommand("drop index {0}", info.indname); dbCmd.ExecuteNonQuery(); } }
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 Dictionary <string, indexinfo> _getIndexes(APDatabase db, APGenTable table) { OracleCommand dbCmd = db.CreateSqlCommand( @"select col.index_name, col.column_name, expr.column_expression, col.descend, nvl(cst.constraint_type,'C') from user_ind_columns col left join user_ind_expressions expr on col.index_name=expr.index_name and col.table_name=expr.table_name and col.column_position=expr.column_position left join user_constraints cst on col.table_name=cst.table_name and col.index_name=cst.constraint_name where col.table_name = '{0}' order by col.index_name, col.column_position", table.TableName.ToUpper()) as OracleCommand; dbCmd.InitialLONGFetchSize = -1; Dictionary <string, indexinfo> dbIndexes = new Dictionary <string, indexinfo>(StringComparer.InvariantCultureIgnoreCase); using (OracleDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { string name = Convert.ToString(reader.GetValue(0)); string col = Convert.ToString(reader.GetValue(1)); if (!reader.IsDBNull(2)) { col = reader.GetString(2); col = col.Substring(1, col.Length - 2); } bool is_descending_key = Convert.ToString(reader.GetValue(3)) == "DESC"; string type = Convert.ToString(reader.GetValue(4)); indexinfo data; if (dbIndexes.ContainsKey(name)) { data = dbIndexes[name]; } else { data = new indexinfo() { indname = name }; if (type == "P") { data.indtype = indexType.Primary; } else if (type == "U") { data.indtype = indexType.Unique; } else { data.indtype = indexType.Index; } dbIndexes.Add(name, data); } data.columns.Add(col, is_descending_key); } } foreach (var item in dbIndexes.Values) { item.pickKeys(); } return(dbIndexes); }
private Dictionary<string, indexinfo> _getIndexes(APDatabase db, APGenTable table) { DbCommand dbCmd = db.CreateSqlCommand( @"select idx.name, col.name as col, idxCol.is_descending_key, idx.is_unique, idx.is_primary_key, idx.is_unique_constraint from sys.indexes as idx inner join sys.index_columns as idxCol on (idx.object_id = idxCol.object_id AND idx.index_id = idxCol.index_id) inner join sys.columns as col on (idx.object_id = col.object_id AND idxCol.column_id = col.column_id) where idx.object_id = object_id('{0}') order by idx.name", table.TableName); Dictionary<string, indexinfo> dbIndexes = new Dictionary<string, indexinfo>(); using (IDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { string name = Convert.ToString(reader.GetValue(0)); string col = Convert.ToString(reader.GetValue(1)); bool is_descending_key = Convert.ToBoolean(reader.GetValue(2)); bool is_unique = Convert.ToBoolean(reader.GetValue(3)); bool is_primary_key = Convert.ToBoolean(reader.GetValue(4)); bool is_unique_constraint = Convert.ToBoolean(reader.GetValue(5)); indexinfo data; if (dbIndexes.ContainsKey(name)) { data = dbIndexes[name]; } else { data = new indexinfo() { indname = name }; if (is_primary_key) data.indtype = indexType.Primary; else if (is_unique_constraint) data.indtype = indexType.Unique; else data.indtype = indexType.Index; dbIndexes.Add(name, data); } data.columns.Add(col, is_descending_key); } } foreach (var item in dbIndexes.Values) { item.pickKeys(); } return dbIndexes; }
private void _dropIndex(APDatabase db, APGenTable table, indexinfo info) { if (info.indtype != indexType.Index) { _dropConstraint(db, table, info.indname); } else { DbCommand dbCmd = db.CreateSqlCommand("drop index {1} on {0}", table.TableName, info.indname); dbCmd.ExecuteNonQuery(); } }
private Dictionary <string, indexinfo> _getIndexes(APDatabase db, APGenTable table) { DbCommand dbCmd = db.CreateSqlCommand( @"select idx.name, col.name as col, idxCol.is_descending_key, idx.is_unique, idx.is_primary_key, idx.is_unique_constraint from sys.indexes as idx inner join sys.index_columns as idxCol on (idx.object_id = idxCol.object_id AND idx.index_id = idxCol.index_id) inner join sys.columns as col on (idx.object_id = col.object_id AND idxCol.column_id = col.column_id) where idx.object_id = object_id('{0}') order by idx.name", table.TableName); Dictionary <string, indexinfo> dbIndexes = new Dictionary <string, indexinfo>(); using (IDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { string name = Convert.ToString(reader.GetValue(0)); string col = Convert.ToString(reader.GetValue(1)); bool is_descending_key = Convert.ToBoolean(reader.GetValue(2)); bool is_unique = Convert.ToBoolean(reader.GetValue(3)); bool is_primary_key = Convert.ToBoolean(reader.GetValue(4)); bool is_unique_constraint = Convert.ToBoolean(reader.GetValue(5)); indexinfo data; if (dbIndexes.ContainsKey(name)) { data = dbIndexes[name]; } else { data = new indexinfo() { indname = name }; if (is_primary_key) { data.indtype = indexType.Primary; } else if (is_unique_constraint) { data.indtype = indexType.Unique; } else { data.indtype = indexType.Index; } dbIndexes.Add(name, data); } data.columns.Add(col, is_descending_key); } } foreach (var item in dbIndexes.Values) { item.pickKeys(); } return(dbIndexes); }
private Dictionary<string, indexinfo> _getIndexes(APDatabase db, APGenTable table) { OracleCommand dbCmd = db.CreateSqlCommand( @"select col.index_name, col.column_name, expr.column_expression, col.descend, nvl(cst.constraint_type,'C') from user_ind_columns col left join user_ind_expressions expr on col.index_name=expr.index_name and col.table_name=expr.table_name and col.column_position=expr.column_position left join user_constraints cst on col.table_name=cst.table_name and col.index_name=cst.constraint_name where col.table_name = '{0}' order by col.index_name, col.column_position", table.TableName.ToUpper()) as OracleCommand; dbCmd.InitialLONGFetchSize = -1; Dictionary<string, indexinfo> dbIndexes = new Dictionary<string, indexinfo>(StringComparer.InvariantCultureIgnoreCase); using (OracleDataReader reader = dbCmd.ExecuteReader()) { while (reader.Read()) { string name = Convert.ToString(reader.GetValue(0)); string col = Convert.ToString(reader.GetValue(1)); if (!reader.IsDBNull(2)) { col = reader.GetString(2); col = col.Substring(1, col.Length - 2); } bool is_descending_key = Convert.ToString(reader.GetValue(3)) == "DESC"; string type = Convert.ToString(reader.GetValue(4)); indexinfo data; if (dbIndexes.ContainsKey(name)) { data = dbIndexes[name]; } else { data = new indexinfo() { indname = name }; if (type == "P") data.indtype = indexType.Primary; else if (type == "U") data.indtype = indexType.Unique; else data.indtype = indexType.Index; dbIndexes.Add(name, data); } data.columns.Add(col, is_descending_key); } } foreach (var item in dbIndexes.Values) { item.pickKeys(); } return dbIndexes; }