private EntityCollection <OracleTableColumnList> GetTableColumnListByName(string name, string strConn)
{
DBHelperBase <OracleTableColumnList> helper = new DBHelperBase <OracleTableColumnList>(DataBaseType.Oracle9i, strConn);
OracleTableColumnList searchCondition = helper.CreateSimpleSeachCondition();
searchCondition.Owner = name;
EntityCollection <OracleTableColumnList> tableColumnList = helper.SelectDataByCondition(searchCondition, false);
return(tableColumnList);
}
private void AnalyseTableColumnDiff(List <OracleTableList> tables, List <OracleTableColumnList> relatedColumns1, List <OracleTableColumnList> relatedColumns2)
{
tableColumnDiff = new List <TableColumnDiffInfo>();
foreach (OracleTableList table in tables)
{
List <OracleTableColumnList> columns1 = relatedColumns1.FindAll(new Predicate <OracleTableColumnList>((obj) => { return(obj.Owner == table.Owner && obj.TableName == table.TableName); }));
List <OracleTableColumnList> columns2 = relatedColumns2.FindAll(new Predicate <OracleTableColumnList>((obj) => { return(obj.Owner == table.Owner && obj.TableName == table.TableName); }));
List <OracleTableColumnList> columns = columns1.Union(columns2, new OracleTableColumnListComparer()).ToList();
bool hasDiff = false;
bool isCross = false; //含新库库中没有的列
bool hasNewField = false; //不含新库库中的列
bool hasAttrDiff = false; //列属性存在不同
List <string> sqls = new List <string>();
string[] dataBaseTypeWithoutLength = new string[] { "BLOB", "CLOB", "DATE", "LONG" };
foreach (OracleTableColumnList col in columns)
{
//修改由于A做基础库
//bool
OracleTableColumnList colInTable1 = columns1.Find(new Predicate <OracleTableColumnList>((obj) => { return(obj.Owner == col.Owner && obj.TableName == col.TableName && obj.ColumnName == col.ColumnName); }));
OracleTableColumnList colInTable2 = columns2.Find(new Predicate <OracleTableColumnList>((obj) => { return(obj.Owner == col.Owner && obj.TableName == col.TableName && obj.ColumnName == col.ColumnName); }));
if (colInTable1 == null && colInTable2 == null)
{
continue;
}
else if (colInTable1 != null && colInTable2 == null)
{
TableColumnDiffInfo diff = new TableColumnDiffInfo();
diff.TableName = col.Owner + "." + col.TableName;
diff.ColumName = col.ColumnName;
diff.DiffReason = "列在A库的表中但不再B库的表中";
tableColumnDiff.Add(diff);
hasNewField = true;
hasDiff = true;
string s = "";
if (colInTable1.DataType == "NUMBER")
{
if (colInTable1.DataScale != null && colInTable1.DataPrecision != null)
{
s = string.Format("alter table {4}.{0} add {1} {2}({5},{6}) {3};", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner, colInTable1.DataPrecision, colInTable1.DataScale);
}
else if (colInTable1.DataScale == null && colInTable1.DataPrecision != null)
{
s = string.Format("alter table {4}.{0} add {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataPrecision + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner);
}
else
{
s = string.Format("alter table {4}.{0} add {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner);
}
}
else
{
s = string.Format("alter table {4}.{0} add {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataLength + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner);
}
sqls.Add(s);
}
else if (colInTable1 == null && colInTable2 != null)
{
TableColumnDiffInfo diff = new TableColumnDiffInfo();
diff.TableName = col.Owner + "." + col.TableName;
diff.ColumName = col.ColumnName;
diff.DiffReason = "列在B库的表中但不再A库的表中";
tableColumnDiff.Add(diff);
isCross = true;
hasDiff = true;
}
else if (colInTable1.Nullable != colInTable2.Nullable)
{
TableColumnDiffInfo diff = new TableColumnDiffInfo();
diff.TableName = col.Owner + "." + col.TableName;
diff.ColumName = col.ColumnName;
diff.DiffReason = "列的是否可空不同";
tableColumnDiff.Add(diff);
hasAttrDiff = true;
hasDiff = true;
string s = "";
if (colInTable1.DataType == "NUMBER")
{
if (colInTable1.DataScale != null && colInTable1.DataPrecision != null)
{
s = string.Format("alter table {4}.{0} modify {1} {2}({5},{6}) {3};", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner, colInTable1.DataPrecision, colInTable1.DataScale);
}
else if (colInTable1.DataScale == null && colInTable1.DataPrecision != null)
{
s = string.Format("alter table {4}.{0} modify {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataPrecision + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner);
}
else
{
s = string.Format("alter table {4}.{0} modify {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner);
}
}
else
{
s = string.Format("alter table {4}.{0} modify {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataLength + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner);
}
sqls.Add(s);
}
else if (colInTable1.DataType != colInTable2.DataType)
{
TableColumnDiffInfo diff = new TableColumnDiffInfo();
diff.TableName = col.Owner + "." + col.TableName;
diff.ColumName = col.ColumnName;
diff.DiffReason = "列的类型不同";
tableColumnDiff.Add(diff);
hasAttrDiff = true;
hasDiff = true;
string s = "";
if (colInTable1.DataType == "NUMBER")
{
if (colInTable1.DataScale != null && colInTable1.DataPrecision != null)
{
s = string.Format("alter table {4}.{0} modify {1} {2}({5},{6}) ;", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner, colInTable1.DataPrecision, colInTable1.DataScale);
}
else if (colInTable1.DataScale == null && colInTable1.DataPrecision != null)
{
s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataPrecision + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner);
}
else
{
s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner);
}
}
else
{
s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataLength + ")", "", colInTable1.Owner);
}
sqls.Add(s);
}
else if (colInTable1.DataLength != colInTable2.DataLength)
{
TableColumnDiffInfo diff = new TableColumnDiffInfo();
diff.TableName = col.Owner + "." + col.TableName;
diff.ColumName = col.ColumnName;
diff.DiffReason = "列的数据长度不同";
tableColumnDiff.Add(diff);
hasAttrDiff = true;
hasDiff = true;
string s = "";
if (colInTable1.DataType == "NUMBER")
{
if (colInTable1.DataScale != null && colInTable1.DataPrecision != null)
{
s = string.Format("alter table {4}.{0} modify {1} {2}({5},{6}) ;", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : "", colInTable1.Owner, colInTable1.DataPrecision, colInTable1.DataScale);
}
else if (colInTable1.DataScale == null && colInTable1.DataPrecision != null)
{
s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataPrecision + ")", colInTable1.Nullable == "N" ? " not null " : "", colInTable1.Owner);
}
else
{
s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : "", colInTable1.Owner);
}
}
else
{
s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataLength + ")", "", colInTable1.Owner);
}
sqls.Add(s);
}
}
if (hasDiff)
{
TableDiffInfo diff = new TableDiffInfo();
diff.TableName = table.Owner + "." + table.TableName;
diff.DiffReason = "";
diff.UpdateSql = sqls;
if (hasAttrDiff)
{
diff.DiffReason = diff.DiffReason + "|存在列属性不同";
}
if (hasNewField)
{
diff.DiffReason = diff.DiffReason + "|缺少列";
}
if (isCross)
{
diff.UpdateSql = null;
diff.DiffReason = "|老库存在新库中没有的字段";
}
tableDiff.Add(diff);
}
}
}
