public static PdmModel PdmReader(string pdmFile)
{
PdmModel result;
if (string.IsNullOrEmpty(pdmFile))
{
result = null;
}
else
{
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(pdmFile);
XmlNamespaceManager xmlNamespaceManager = new XmlNamespaceManager(xmlDocument.NameTable);
xmlNamespaceManager.AddNamespace("a", "attribute");
xmlNamespaceManager.AddNamespace("c", "collection");
xmlNamespaceManager.AddNamespace("o", "object");
PdmModel pdmModels = new PdmModel();
XmlNodeList xmlNodeList = xmlDocument.SelectNodes("//c:Tables", xmlNamespaceManager);
if (xmlNodeList != null)
{
foreach (XmlNode current in from XmlNode xmlTables in xmlNodeList
from xnTable in xmlTables.ChildNodes.Cast <XmlNode>()
where xnTable.Name != "o:Shortcut"
select xnTable)
{
pdmModels.Tables.Add(GetTable(current));
}
}
result = pdmModels;
}
return(result);
}
private static PdmModel FetchPdmSchema(string pdmPath)
{
PdmFileReader reader = new PdmFileReader();
PdmModel model = reader.ReadFromFile(pdmPath);
return(model);
}
private static IList <PdmTableInfoModel> GetTables(List <string> pdmPaths)
{
List <PdmTableInfoModel> list = new List <PdmTableInfoModel>();
for (int i = 0; i < pdmPaths.Count; i++)
{
string text = pdmPaths[i];
if (File.Exists(text))
{
PdmModel pdmModels = PdmReaderBLL.PdmReader(text);
list.AddRange(pdmModels.Tables);
}
}
return((from t in list
orderby t.Code
select t).ToList <PdmTableInfoModel>());
}
// xcopy "$(SolutionDir)lib\ODP64\NeedCopyToAppBin\*.*" "$(TargetDir)" /y /c
static void Main(string[] args)
{
if (args.Length < 2)
{
Console.WriteLine("PDCompare 用于比对 Power Designer 物理模型(.pdm 文件) 与 Oracle 数据库表结构是否一致。");
Console.WriteLine("");
Console.WriteLine("使用方法为：");
Console.WriteLine(" PDCompare <pdm文件路径> <Oracle连接字符串> [/summary] [/compareScript]");
Console.WriteLine("");
Console.WriteLine(" 指定 /summary 可选参数将只显示汇总信息。");
Console.WriteLine(" 指定 /compareScript 可选参数将比对存储过程和视图。");
Console.WriteLine("");
Console.WriteLine(@" 示例：PDCompare ""d:\temp\ZenFramework.pdm"" ""User ID=ZEN;Password=forget;Pooling=true;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.4.45)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = onlyorcl)))""");
return;
}
string connStr = args[1]; // "User ID=ZEN;Password=000000;Pooling=true;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = jcltdb)))";
string pdmPath = args[0]; // @"Z:\工作资料\代码碎片\Oracle\数据库最佳实践\ZenFramework.pdm";
bool onlyShowSummary = (from arg in args where arg == "/summary" select arg).Any(); // 是否只显示汇总结果
bool compareScript = (from arg in args where arg == "/compareScript" select arg).Any(); // 是否比对存储过程和视图
Log log = new Log();
// 读取 PD 表结构
PdmModel pdm = FetchPdmSchema(pdmPath);
// 获取所有表名
IList <string> dbTableNames = FetchAllTableNameList(connStr);
// 获取表列定义
IDictionary <string, IList <DbColumn> > colDict = FetchAllColumnDict(connStr);
//
// 对比表名是否一致
//
if (onlyShowSummary == false)
{
log.Title("1) 检查表名是否一致...");
}
// 是否有 PD 有而 DB 没有的表名
IList <string> dbLackTables = new List <string>();
IList <string> identicalTables = new List <string>(); // 两边都有的表名列表
foreach (var pdTable in pdm.Tables)
{
bool isFound = (from t in dbTableNames where t.Nvl().ToUpper() == pdTable.Code.Nvl().ToUpper() select t).Any();
if (isFound == false)
{
dbLackTables.Add(pdTable.Code.Nvl().ToUpper());
}
else
{
identicalTables.Add(pdTable.Code.Nvl().ToUpper());
}
}
if (onlyShowSummary == false)
{
if (dbLackTables.Any())
{
log.Error1("> PD 里有而数据库里没有的表：");
foreach (var dbLackTable in dbLackTables)
{
log.Error2(dbLackTable);
}
}
}
// 是否有 DB 里有而 PD 里没有的表
IList <string> pdLackTables = new List <string>();
foreach (var dbTableName in dbTableNames)
{
bool isFound = (from t in pdm.Tables where t.Code.Nvl().ToUpper() == dbTableName.Nvl().ToUpper()
select t.Code.Nvl().ToUpper()).Any();
if (isFound == false)
{
pdLackTables.Add(dbTableName);
}
}
if (onlyShowSummary == false)
{
if (pdLackTables.Any())
{
log.Error1("> 数据库里有而 PD 里没有的表：");
foreach (var pdLackTable in pdLackTables)
{
log.Error2(pdLackTable);
}
}
if (dbLackTables.Any() == false && pdLackTables.Any() == false)
{
log.Sucess1("√ 完全一致");
}
}
//
// 对比列定义是否一致
//
if (onlyShowSummary == false)
{
log.Title("");
log.Title("2) 检查列定义是否一致...");
}
bool colsAllIdentical = true; // 是否所有列定义都一致
int unIdenticalColCount = 0; // 不一致的列数量
foreach (var tableName in identicalTables)
{
IList <ColumnInfo> pdCols = (from t in pdm.Tables where t.Code.Nvl().ToUpper() == tableName select t).First().Columns;
IList <DbColumn> dbCols = colDict[tableName];
//
// 对比列名是否一致
//
// 是否有 pd 里有而数据库里没有的列
IList <string> dbLackCols = new List <string>();
IList <ColumnInfo> identicalCols = new List <ColumnInfo>(); // PD 和数据库中都有的列
foreach (var pdCol in pdCols)
{
bool isFound = (from t in dbCols where pdCol.Code.Nvl().ToUpper() == t.ColName.Nvl().ToUpper()
select t).Any();
if (isFound == false)
{
dbLackCols.Add(pdCol.Code.Nvl().ToUpper());
}
else
{
identicalCols.Add(pdCol);
}
}
if (dbLackCols.Any())
{
colsAllIdentical = false;
unIdenticalColCount += dbLackCols.Count;
if (onlyShowSummary == false)
{
log.Error1(string.Format("> 表 [{0}] PD 里有而数据库里没有的列：", tableName));
foreach (var dbLackCol in dbLackCols)
{
log.Error2(dbLackCol);
}
}
}
// 是否有数据库里有而 PD 里没有的列
IList <string> pdLackCols = new List <string>();
foreach (var dbCol in dbCols)
{
bool isFound = (from t in pdCols where t.Code.Nvl().ToUpper() == dbCol.ColName.Nvl().ToUpper()
select t).Any();
if (isFound == false)
{
pdLackCols.Add(dbCol.ColName.Nvl().ToUpper());
}
}
if (pdLackCols.Any())
{
colsAllIdentical = false;
unIdenticalColCount += pdLackCols.Count();
if (onlyShowSummary == false)
{
log.Error1(string.Format("> 表 [{0}] 数据库里有而 PD 里没有的列：", tableName));
foreach (var pdLackCol in pdLackCols)
{
log.Error2(pdLackCol);
}
}
}
// 对比列数据类型是否一致
IList <string> notIdenticalCols = new List <string>();
foreach (var pdCol in identicalCols)
{
var dbCol = (from t in dbCols
where pdCol.Code.Nvl().ToUpper() == t.ColName.Nvl().ToUpper()
select t).First();
if (dbCol.DataType.Nvl().ToUpper() != pdCol.DataType.Nvl().ToUpper())
{
notIdenticalCols.Add(string.Format("{0}{1} <=> {2}",
pdCol.Code.Nvl().ToUpper().PadRight(30, ' '),
pdCol.DataType.Nvl().ToUpper().PadRight(14, ' '), dbCol.DataType.Nvl().ToUpper()));
}
}
if (notIdenticalCols.Any())
{
colsAllIdentical = false;
unIdenticalColCount += notIdenticalCols.Count;
if (onlyShowSummary == false)
{
log.Error1(string.Format("> 表 [{0}] 数据类型不一致的列：", tableName));
foreach (var notIdenticalCol in notIdenticalCols)
{
log.Error2(notIdenticalCol);
}
}
}
}
if (colsAllIdentical)
{
if (onlyShowSummary == false)
{
log.Sucess1("√ 完全一致");
}
}
IList <string> dbLackSpList = new List <string>(); // PD里有而数据库里没有的存储过程列表
IList <string> pdLackSpList = new List <string>(); // 数据库里有而PD里没有的存储过程列表
IList <string> unIdenticalSpList = new List <string>(); // 代码不一致的存储过程列表
IList <string> dbLackViewList = new List <string>(); // PD里有而数据库里没有的视图列表
IList <string> pdLackViewList = new List <string>(); // 数据库里有而PD里没有的视图列表
IList <string> unIdenticalViewList = new List <string>(); // 代码不一致的视图列表
if (compareScript == true)
{
//
// 比对存储过程和视图
//
if (onlyShowSummary == false)
{
log.Title("");
log.Title("3) 检查存储过程是否一致...");
}
IDictionary <string, string> dbSpDict = FetchProcedureDict(connStr);
IList <ProcedureInfo> existsSpList = new List <ProcedureInfo>(); // 两边都有的存储过程
// 是否有PD里有而数据库里没有的存储过程
foreach (ProcedureInfo procedure in pdm.Procedures)
{
bool isFound = (from t in dbSpDict.Keys
where t.Nvl().ToUpper() == procedure.Code.Nvl().ToUpper()
select t).Any();
if (isFound == false)
{
dbLackSpList.Add(procedure.Code.Nvl().ToUpper());
}
else
{
existsSpList.Add(procedure);
}
}
// 是否有数据库里有而PD里没有的存储过程
foreach (var spName in dbSpDict.Keys)
{
bool isFound = (from t in pdm.Procedures
where t.Code.Nvl().ToUpper() == spName.Nvl().ToUpper()
select t).Any();
if (isFound == false)
{
pdLackSpList.Add(spName.Nvl().ToUpper());
}
}
// 是否有代码不一致的存储过程
foreach (var sp in existsSpList)
{
string dbText = Util.CompressSqlText(dbSpDict[sp.Code.Nvl().ToUpper()]);
// 容错
if (dbText.Nvl().StartsWith("procedure"))
{
dbText = "create or replace " + dbText;
}
string spText = Util.CompressSqlText(sp.Text);
if (dbText != spText)
{
unIdenticalSpList.Add(sp.Code.Nvl().ToUpper());
}
}
if (onlyShowSummary == false)
{
if (dbLackSpList.Any())
{
log.Error1("> PD 里有而数据库里没有的存储过程：");
foreach (var dbLackSp in dbLackSpList)
{
log.Error2(dbLackSp);
}
}
if (pdLackSpList.Any())
{
log.Error1("> 数据库里有而 PD 里没有的存储过程：");
foreach (var pdLackSp in pdLackSpList)
{
log.Error2(pdLackSp);
}
}
if (unIdenticalSpList.Any())
{
log.Error1("> 代码不一致的存储过程：");
foreach (var unIdenticalSp in unIdenticalSpList)
{
log.Error2(unIdenticalSp);
}
}
if (dbLackSpList.Count == 0 && pdLackSpList.Count == 0 && unIdenticalSpList.Count == 0)
{
log.Sucess1("√ 完全一致");
}
}
//
// 比对视图是否一致
//
if (onlyShowSummary == false)
{
log.Title("");
log.Title("4) 检查视图是否一致...");
}
IDictionary <string, string> dbViewDict = FetchViewDict(connStr);
IList <ViewInfo> existsViewList = new List <ViewInfo>(); // 两边都有的视图
// 是否有PD里有而数据库里没有的视图
foreach (ViewInfo view in pdm.Views)
{
bool isFound = (from t in dbViewDict.Keys
where t.Nvl().ToUpper() == view.Code.Nvl().ToUpper()
select t).Any();
if (isFound == false)
{
dbLackViewList.Add(view.Code.Nvl().ToUpper());
}
else
{
existsViewList.Add(view);
}
}
// 是否有数据库里有而PD里没有的视图
foreach (var viewName in dbViewDict.Keys)
{
bool isFound = (from t in pdm.Views
where t.Code.Nvl().ToUpper() == viewName.Nvl().ToUpper()
select t).Any();
if (isFound == false)
{
pdLackViewList.Add(viewName.Nvl().ToUpper());
}
}
// 是否有代码不一致的视图
foreach (var view in existsViewList)
{
string dbText = Util.CompressSqlText(dbViewDict[view.Code.Nvl().ToUpper()]);
string pdText = Util.CompressSqlText(view.ViewSqlQuery);
// 容错
pdText = new Regex("^.+? as ").Replace(pdText, "");
if (dbText.Nvl().EndsWith(";"))
{
dbText = dbText.Remove(dbText.Length - 1);
}
if (pdText.Nvl().EndsWith(";"))
{
pdText = pdText.Remove(pdText.Length - 1);
}
if (dbText != pdText)
{
unIdenticalViewList.Add(view.Code.Nvl().ToUpper());
}
}
if (onlyShowSummary == false)
{
if (dbLackViewList.Any())
{
log.Error1("> PD 里有而数据库里没有的视图：");
foreach (var dbLackView in dbLackViewList)
{
log.Error2(dbLackView);
}
}
if (pdLackViewList.Any())
{
log.Error1("> 数据库里有而 PD 里没有的视图：");
foreach (var pdLackView in pdLackViewList)
{
log.Error2(pdLackView);
}
}
if (unIdenticalViewList.Any())
{
log.Error1("> 代码不一致的视图：");
foreach (var unIdenticalView in unIdenticalViewList)
{
log.Error2(unIdenticalView);
}
}
if (dbLackViewList.Count == 0 && pdLackViewList.Count == 0 && unIdenticalViewList.Count == 0)
{
log.Sucess1("√ 完全一致");
}
}
}
//FileInfo pdmFileInfo = new FileInfo(pdmPath);
//string logFileDir = Environment.CurrentDirectory + "\\比对记录\\";
//Directory.CreateDirectory(logFileDir);
//string logFilePath = logFileDir
// + pdmFileInfo.Name.Replace(pdmFileInfo.Extension, string.Empty)
// + "_" + DateTime.Now.ToString("yyyyMMdd") + ".txt";
if (onlyShowSummary == false)
{
//log.Title(" * 以上内容已输出到 " + logFilePath);
Console.WriteLine("");
Console.WriteLine("=== 按任意键退出 ===");
Console.ReadKey();
}
//log.SaveLogFile(logFilePath);
if (onlyShowSummary == true)
{
if (dbLackTables.Count == 0 && pdLackTables.Count == 0 && colsAllIdentical == true &&
dbLackSpList.Count == 0 && pdLackSpList.Count == 0 && unIdenticalSpList.Count == 0 &&
dbLackViewList.Count == 0 && pdLackViewList.Count == 0 && unIdenticalViewList.Count == 0)
{
log.Sucess1("√ 完全一致");
}
else
{
if (compareScript)
{
log.Error1(string.Format("{0} 个表名不一致。{1} 个列定义不一致。{2} 个存储过程不一致。{3} 个视图不一致。",
dbLackTables.Count + pdLackTables.Count,
unIdenticalColCount,
dbLackSpList.Count + pdLackSpList.Count + unIdenticalSpList.Count,
dbLackViewList.Count + pdLackViewList.Count + unIdenticalViewList.Count));
}
else
{
log.Error1(string.Format("{0} 个表名不一致。{1}个列定义不一致。",
dbLackTables.Count + pdLackTables.Count, unIdenticalColCount));
}
}
}
}
