コード例 #1
0
        /// <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);
        }
コード例 #2
0
        /// <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);
        }