/// <param name="dbUtil"> </param>
/// <param name="schema">
/// @return </param>
/// <exception cref="Exception"> </exception>
//JAVA TO C# CONVERTER WARNING: Method 'throws' clauses are not available in .NET:
//ORIGINAL LINE: public java.util.Dictionary<String, TableInfo> getAllTable(com.rbt.util.db.AbstractDBUtil dbUtil, String schema) throws Exception
public virtual Dictionary <string, TableInfo> GetAllTable(SqlLiteDBUtil dbUtil, string schema)
{
// =======================================================
// 查詢 所有的欄位資訊
// =======================================================
// 兜組SQL
var columnsQSql = new StringBuilder();
columnsQSql.Append("SELECT col.OWNER, ");
columnsQSql.Append(" col.TABLE_NAME, ");
columnsQSql.Append(" tabComt.COMMENTS AS TABLE_COMMENTS, ");
columnsQSql.Append(" col.COLUMN_ID, ");
columnsQSql.Append(" col.COLUMN_NAME, ");
columnsQSql.Append(" col.DATA_TYPE, ");
columnsQSql.Append(" col.DATA_LENGTH, ");
columnsQSql.Append(" col.DATA_PRECISION, ");
columnsQSql.Append(" col.DATA_DEFAULT, ");
columnsQSql.Append(" col.CHAR_LENGTH, ");
columnsQSql.Append(" col.NULLABLE, ");
columnsQSql.Append(" colComt.COMMENTS ");
columnsQSql.Append("FROM ALL_TAB_COLUMNS col ");
columnsQSql.Append(" JOIN ALL_TABLES tab ");
columnsQSql.Append(" ON col.OWNER = tab.OWNER ");
columnsQSql.Append(" AND col.TABLE_NAME = tab.TABLE_NAME ");
columnsQSql.Append(" LEFT JOIN ALL_COL_COMMENTS colComt ");
columnsQSql.Append(" ON col.OWNER = colComt.Owner ");
columnsQSql.Append(" AND col.TABLE_NAME = colComt.TABLE_NAME ");
columnsQSql.Append(" AND col.COLUMN_NAME = colComt.COLUMN_NAME ");
columnsQSql.Append(" LEFT JOIN sys.USER_TAB_COMMENTS tabComt ");
columnsQSql.Append(" ON tabComt.TABLE_TYPE = 'TABLE' ");
columnsQSql.Append(" AND tabComt.TABLE_NAME = col.TABLE_NAME ");
columnsQSql.Append("WHERE col.OWNER = '" + schema + "' ");
// 測試縮限範圍
if (StringUtil.NotEmpty(_testTable))
{
columnsQSql.Append("AND col.TABLE_NAME = '" + _testTable + "' ");
}
columnsQSql.Append("ORDER BY col.TABLE_NAME, ");
columnsQSql.Append(" col.COLUMN_ID ");
// 查詢所有的欄位List
DataTable allColumnList = dbUtil.QueryDataTable(columnsQSql.ToString());
// 依據 table 分類
var tableColumnProcResult = Process(allColumnList);
// =======================================================
// 查詢 所有的欄位資訊
// =======================================================
var pKeyQSql = new StringBuilder();
pKeyQSql.Append("SELECT C.OWNER, ");
pKeyQSql.Append(" C.TABLE_NAME, ");
pKeyQSql.Append(" D.POSITION, ");
pKeyQSql.Append(" D.COLUMN_NAME ");
pKeyQSql.Append("FROM ALL_CONSTRAINTS C ");
pKeyQSql.Append(" JOIN ALL_CONS_COLUMNS D ");
pKeyQSql.Append(" ON C.OWNER = D.OWNER ");
pKeyQSql.Append(" AND C.CONSTRAINT_NAME = D.CONSTRAINT_NAME ");
pKeyQSql.Append("WHERE C.CONSTRAINT_TYPE = 'P' ");
pKeyQSql.Append(" AND C.OWNER = '"+ schema + "' ");
// 測試縮限範圍
if (StringUtil.NotEmpty(_testTable))
{
columnsQSql.Append("AND C.TABLE_NAME = '" + _testTable + "' ");
}
pKeyQSql.Append("ORDER BY C.TABLE_NAME, ");
pKeyQSql.Append(" D.POSITION ");
DataTable allPKeyColumnList = dbUtil.QueryDataTable(pKeyQSql.ToString());
// 依據 table 分類
var pkColumnProcResult = Process(allPKeyColumnList);
// =======================================================
// 組 tableInfo
// =======================================================
var tableInfoMap = new Dictionary <string, TableInfo>();
foreach (var tableName in tableColumnProcResult.ColumnInfoListByTableName.Keys)
{
var tableInfo = new TableInfo(this);
// 一般欄位
tableInfo.columnDataMapList = tableColumnProcResult.ColumnInfoListByTableName[tableName];
tableInfo.columnDataMapByColName = tableColumnProcResult.ColumnInfoByColNameTableName[tableName];
tableInfo.columnNameSet = tableColumnProcResult.ColumnNameSetByTableName[tableName];
// PK欄位
tableInfo.PKeyDataMapList = pkColumnProcResult.ColumnInfoListByTableName[tableName];
tableInfo.PKeySet = pkColumnProcResult.ColumnNameSetByTableName[tableName];
// PUT
tableInfoMap.Add(tableName, tableInfo);
}
return(tableInfoMap);
}
/// <summary>
///
/// </summary>
/// <param name="sourceDb"></param>
/// <param name="sourceSchema"></param>
/// <param name="targetDb"></param>
/// <param name="targetSchema"></param>
/// <param name="resultFilePath"></param>
/// <param name="resultFileName"></param>
public SqliteDif(
SqlLiteDBUtil sourceDb, string sourceSchema, SqlLiteDBUtil targetDb, string targetSchema,
string resultFilePath, string resultFileName)
{
//FileUtil fu = new FileUtil();
var fu = new StringBuilder();
// ==============================================================
// 取得資料庫的 Schema 資訊
// ==============================================================
// 取得來源的table Schema 資料
Console.WriteLine("取得來源資料庫 Schema..");
var sourceTableInfoMap = GetAllTable(sourceDb, sourceSchema);
// System.out.println(new BeanUtil().showContent(sourceTableInfoMap));
// 取得來源的table Schema 資料
Console.WriteLine("取得目標資料庫 Schema..");
var targetTableInfoMap = GetAllTable(targetDb, targetSchema);
Console.WriteLine("比對中..");
// ==============================================================
// 比對缺少的 TABLE
// ==============================================================
var firstAddFlag = true;
foreach (var tableName in sourceTableInfoMap.Keys)
{
if (!targetTableInfoMap.ContainsKey(tableName))
{
Console.WriteLine("目標缺少 TABLE :[" + tableName + "]");
if (firstAddFlag)
{
fu.AppendLine("/*==============================================================*/");
fu.AppendLine("/* 新增 Table*/");
fu.AppendLine("/*==============================================================*/");
firstAddFlag = false;
}
// 產生 create sql
var sourceTableInfo = sourceTableInfoMap[tableName];
fu.AppendLine(GanCreateSql(targetSchema, tableName, sourceTableInfo.ColumnDataMapList,
sourceTableInfo.GetpKeySet()));
}
}
// ==============================================================
// 各 table 欄位比對
// ==============================================================
foreach (var tableName in sourceTableInfoMap.Keys)
{
// 目標無此 table 時跳過
if (!targetTableInfoMap.ContainsKey(tableName))
{
continue;
}
// 取得來源 TableInfo
var sourceTableInfo = sourceTableInfoMap[tableName];
// 取得目標 TableInfo
var targetTableInfo = targetTableInfoMap[tableName];
// 新增欄位
var addSql = DiffAddColumn(targetSchema, tableName, sourceTableInfo.ColumnDataMapList,
targetTableInfo.ColumnNameSet);
// 修改欄位
var modifySql = DiffModifyColumn(targetSchema, tableName, sourceTableInfo.ColumnDataMapByColName,
targetTableInfo.ColumnDataMapByColName);
if (StringUtil.NotEmpty(addSql) || StringUtil.NotEmpty(modifySql))
{
fu.AppendLine("/*==============================================================*/");
fu.AppendLine("/* 異動 Table : " + tableName + " */");
fu.AppendLine("/*==============================================================*/");
fu.AppendLine(addSql);
fu.AppendLine(modifySql);
}
}
// ==============================================================
// 反向比對
// ==============================================================
//FileUtil reFu = new FileUtil();
var reFu = new StringBuilder();
foreach (var tableName in targetTableInfoMap.Keys)
{
// 比對缺少TABLE
if (!sourceTableInfoMap.ContainsKey(tableName))
{
reFu.AppendLine("--來源缺少 TABLE :[" + tableName + "]");
continue;
}
// 比對缺少欄位
// 取得來源 TableInfo
var sourceTableInfo = sourceTableInfoMap[tableName];
// 取得目標 TableInfo
var targetTableInfo = targetTableInfoMap[tableName];
foreach (var columnName in targetTableInfo.ColumnNameSet)
{
if (!sourceTableInfo.ColumnNameSet.Contains(columnName))
{
reFu.AppendLine("--來源 TABLE " + tableName + " 缺少欄位 :[" + columnName + "]");
}
}
}
if (StringUtil.NotEmpty(fu.ToString()))
{
fu.AppendLine("/*==============================================================*/");
fu.AppendLine("/* 反向比對差異部分 */");
fu.AppendLine("/*==============================================================*/");
fu.AppendLine(reFu.ToString());
}
//TODO
//fu.writeToFile(resultFilePath, resultFileName);
}