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