1. Oracle Table, Column comments update procedure
-- ORACLE
CREATE OR REPLACE PROCEDURE SP_DB_TAB_COMMENT ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2
, P_COMMENT IN VARCHAR2)
IS
V_SQL VARCHAR2(4000);
BEGIN
V_SQL := 'COMMENT ON TABLE ' || P_OWNER || '.' || P_TABLE_NAME || ' IS ' || CHR(39) || P_COMMENT || CHR(39);
EXECUTE IMMEDIATE V_SQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END SP_DB_TAB_COMMENT;
CREATE OR REPLACE PROCEDURE SP_DB_COL_COMMENT ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2
, P_COLUMN_NAME IN VARCHAR2
, P_COMMENT IN VARCHAR2)
IS
V_SQL VARCHAR2(4000);
BEGIN
V_SQL := 'COMMENT ON COLUMN ' || P_OWNER || '.' || P_TABLE_NAME || '.' || P_COLUMN_NAME || ' IS ' || CHR(39) || P_COMMENT || CHR(39);
EXECUTE IMMEDIATE V_SQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END SP_DB_COL_COMMENT;
2. Tibero Table, Column comments update procedure
-- TIBERO
CREATE OR REPLACE PROCEDURE SP_DB_TAB_COMMENT ( P_OWNER IN VARCHAR
, P_TABLE_NAME IN VARCHAR
, P_COMMENT IN VARCHAR)
IS
V_SQL VARCHAR(4000);
BEGIN
V_SQL := 'COMMENT ON TABLE ' || P_OWNER || '.' || P_TABLE_NAME || ' IS ' || CHR(39) || P_COMMENT || CHR(39);
EXECUTE IMMEDIATE V_SQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END SP_DB_TAB_COMMENT;
CREATE OR REPLACE PROCEDURE SP_DB_COL_COMMENT ( P_OWNER IN VARCHAR
, P_TABLE_NAME IN VARCHAR
, P_COLUMN_NAME IN VARCHAR
, P_COMMENT IN VARCHAR)
IS
V_SQL VARCHAR(4000);
BEGIN
V_SQL := 'COMMENT ON COLUMN ' || P_OWNER || '.' || P_TABLE_NAME || '.' || P_COLUMN_NAME || ' IS ' || CHR(39) || P_COMMENT || CHR(39);
EXECUTE IMMEDIATE V_SQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END SP_DB_COL_COMMENT;
3. db-as-is.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="db-as-is">
<select id="selTableListByColumn" resultType="map">
/* db-as-is.selTableListByColumn : Column을 AS-IS Table 목록 조회 */
SELECT T.OWNER
, T.TABLE_NAME
, T.TABLE_TYPE
, T.COMMENTS AS TABLE_COMMENTS
, C.COLUMN_NAME
, C.COMMENTS
, U.USE_TYPE_CD
, U.USE_DESC
, D.DATA_TYPE
, CASE
WHEN D.DATA_TYPE = 'NUMBER' AND D.DATA_SCALE IS NOT NULL THEN NVL(D.DATA_PRECISION, 38) || ', ' || D.DATA_SCALE
WHEN D.DATA_TYPE IN ('CHAR', 'VARCHAR2', 'NVARCHAR') THEN D.CHAR_LENGTH || DECODE(D.CHAR_USED, 'B', ' BYTE', 'C', ' CHAR')
ELSE ' '
END AS DATA_LENGTH
FROM ALL_TAB_COMMENTS T
JOIN ALL_COL_COMMENTS C ON C.OWNER = T.OWNER
AND C.TABLE_NAME = T.TABLE_NAME
<if test="g.isNotEmpty(p.column_name)">
AND C.COLUMN_NAME LIKE '%' || UPPER(TRIM(#{p.column_name})) || '%'
</if>
<if test="g.isNotEmpty(p.comments)">
AND C.COMMENTS LIKE '%' || TRIM(#{p.comments}) || '%'
</if>
JOIN ALL_TAB_COLUMNS D ON D.OWNER = C.OWNER
AND D.TABLE_NAME = C.TABLE_NAME
AND D.COLUMN_NAME = C.COLUMN_NAME
LEFT OUTER JOIN ALL_TAB_USE_DESC U ON U.OWNER = T.OWNER
AND U.TABLE_NAME = T.TABLE_NAME
AND U.TABLE_TYPE = T.TABLE_TYPE
WHERE T.TABLE_TYPE = 'TABLE'
<if test="@org.apache.commons.collections4.CollectionUtils@isNotEmpty(p.owners)">
AND T.OWNER IN
<foreach collection="p.owners" item="owner" separator="," open="(" close=")">
#{owner}
</foreach>
</if>
ORDER BY T.OWNER
, T.TABLE_NAME
</select>
<sql id="SQL_selTable">
<![CDATA[
SELECT T.OWNER
, T.TABLE_NAME
, T.TABLE_TYPE
, T.COMMENTS
, U.USE_TYPE_CD
, U.USE_DESC
FROM ALL_TAB_COMMENTS T
LEFT OUTER JOIN ALL_TAB_USE_DESC U ON U.OWNER = T.OWNER
AND U.TABLE_NAME = T.TABLE_NAME
AND U.TABLE_TYPE = T.TABLE_TYPE
]]>
</sql>
<select id="selTableList" resultType="map">
/* db-as-is.selTableList : AS-IS Table 목록 */
<include refid="SQL_selTable"/>
WHERE T.TABLE_TYPE = 'TABLE'
<if test="@org.apache.commons.collections4.CollectionUtils@isNotEmpty(p.owners)">
AND T.OWNER IN
<foreach collection="p.owners" item="owner" separator="," open="(" close=")">
#{owner}
</foreach>
</if>
<choose>
<when test="@org.apache.commons.collections4.CollectionUtils@isNotEmpty(p.table_names)">
AND T.TABLE_NAME IN
<foreach collection="p.table_names" item="table_name" separator="," open="(" close=")">
#{table_name}
</foreach>
</when>
<when test="g.isNotEmpty(p.table_name)">
AND T.TABLE_NAME LIKE '%' || UPPER(TRIM(#{p.table_name})) || '%'
</when>
</choose>
<if test="g.isNotEmpty(p.comments)">
AND T.COMMENTS LIKE '%' || TRIM(#{p.comments}) || '%'
</if>
ORDER BY T.OWNER
, T.TABLE_NAME
</select>
<select id="selColumnList" resultType="map">
<![CDATA[
/* db-as-is.selColumnList : AS-IS Table Column 목록 */
SELECT TAB.OWNER AS OWNER
, TAB.TABLE_NAME AS TABLE_NAME
, TO_CHAR(COL.COLUMN_ID) AS NO
, COL.COLUMN_NAME AS COLUMN_NAME
, REPLACE(CMT.COMMENTS, CHR(10), ' ') AS COMMENTS
, CST.ISPK AS PK_YN
, CST.PKNO AS PK_NO
, CST.ISAK AS AK_YN
, CST.AK AS AK_NO
, COL.DATA_TYPE AS DATA_TYPE
, CASE
WHEN COL.DATA_TYPE = 'NUMBER' AND COL.DATA_SCALE IS NOT NULL THEN NVL(COL.DATA_PRECISION, 38) || ', ' || COL.DATA_SCALE
WHEN COL.DATA_TYPE IN ('CHAR', 'VARCHAR2', 'NVARCHAR') THEN COL.CHAR_LENGTH || DECODE(COL.CHAR_USED, 'B', ' BYTE', 'C', ' CHAR')
ELSE ' '
END AS DATA_LENGTH
, TO_CHAR(DECODE(COL.CHAR_USED, 'C', COL.CHAR_LENGTH * LENGTHB('가'), COL.DATA_LENGTH)) AS BYTE_LENGTH
, DECODE(COL.NULLABLE, 'N', 'Y', 'Y', 'N') AS NOTNULL_YN
, TRIM(FC_DB_DEFAULT(COL.OWNER, COL.TABLE_NAME, COL.COLUMN_NAME)) AS COLUMN_DEFAULT
, TRIM(DECODE(CST.ISFK, 'Y', 'FK', ' ') || ' ' || DECODE(CST.ISAK, 'Y', 'AK', ' ')) AS CST_DESC
FROM ALL_TAB_COMMENTS TAB
JOIN ALL_TAB_COLUMNS COL ON COL.OWNER = TAB.OWNER
AND COL.TABLE_NAME = TAB.TABLE_NAME
JOIN ALL_COL_COMMENTS CMT ON CMT.OWNER = COL.OWNER
AND CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'Y')) AS ISPK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'PK(' || CTC.POSITION || ') ')) AS PK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', CTC.POSITION)) AS PKNO
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'U', 'Y')) AS ISAK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'U', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS AK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'R', 'Y')) AS ISFK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS FK
FROM ALL_CONS_COLUMNS CTC
JOIN ALL_CONSTRAINTS CST ON CST.OWNER = CTC.OWNER
AND CST.CONSTRAINT_NAME = CTC.CONSTRAINT_NAME
AND CST.CONSTRAINT_TYPE IN ('P', 'R', 'U')
AND CST.TABLE_NAME = CTC.TABLE_NAME
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.OWNER = COL.OWNER
AND CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
WHERE TAB.TABLE_TYPE = {#p.table_type}
AND TAB.OWNER = {#p.owner}
AND TAB.TABLE_NAME = {#p.table_name}
ORDER BY COL.COLUMN_ID
]]>
</select>
<select id="selTable" resultType="map">
/* db-as-is.selTable : AS-IS Table */
<include refid="SQL_selTable"/>
WHERE TAB.TABLE_TYPE = {#p.table_type}
AND TAB.OWNER = {#p.owner}
AND TAB.TABLE_NAME = {#p.table_name}
</select>
<update id="updTable">
<![CDATA[
/* db-as-is.updTable : Update AS-IS Table */
BEGIN
-- 1. Update Table's comments
SP_DB_TAB_COMMENT (#{p.owner , mode=IN, jdbcType=VARCHAR}
,#{p.table_name , mode=IN, jdbcType=VARCHAR}
,#{p.comments , mode=IN, jdbcType=VARCHAR});
-- 2. Update Table using description
MERGE INTO ALL_TAB_USE_DESC T
USING (SELECT #{p.owner} AS OWNER -- PK(1) Owner
, #{p.table_name} AS TABLE_NAME -- PK(2) Table Name
, #{p.table_type} AS TABLE_TYPE -- PK(3) Table Type
, #{p.use_type_cd} AS USE_TYPE_CD -- Using Type Code [TAB_USE_TYPE]
, #{p.use_desc} AS USE_DESC -- Using Description
, #{g.username} AS REG_ID -- 등록자 아이디
, #{g.now} AS REG_DT -- 등록 일시
, #{g.username} AS MOD_ID -- 수정자 아이디
, #{g.now} AS MOD_DT -- 수정 일시
FROM DUAL) D
ON ( D.OWNER = T.OWNER
AND D.TABLE_NAME = T.TABLE_NAME
AND D.TABLE_TYPE = T.TABLE_TYPE)
WHEN MATCHED THEN
UPDATE SET USE_TYPE_CD = D.USE_TYPE_CD -- Using Type Code [TAB_USE_TYPE]
, USE_DESC = D.USE_DESC -- Using Description
--, REG_ID = D.REG_ID -- 등록자 아이디
--, REG_DT = D.REG_DT -- 등록 일시
, MOD_ID = D.MOD_ID -- 수정자 아이디
, MOD_DT = D.MOD_DT -- 수정 일시
WHEN NOT MATCHED THEN
INSERT ( OWNER -- PK(1) Owner
, TABLE_NAME -- PK(2) Table Name
, TABLE_TYPE -- PK(3) Table Type
, USE_TYPE_CD -- Using Type Code [TAB_USE_TYPE]
, USE_DESC -- Using Description
, REG_ID -- 등록자 아이디
, REG_DT -- 등록 일시
, MOD_ID -- 수정자 아이디
, MOD_DT) -- 수정 일시
VALUES ( D.OWNER -- PK(1) Owner
, D.TABLE_NAME -- PK(2) Table Name
, D.TABLE_TYPE -- PK(3) Table Type
, D.USE_TYPE_CD -- Using Type Code [TAB_USE_TYPE]
, D.USE_DESC -- Using Description
, D.REG_ID -- 등록자 아이디
, D.REG_DT -- 등록 일시
, D.MOD_ID -- 수정자 아이디
, D.MOD_DT); -- 수정 일시
END;
]]>
</update>
<update id="updColumn" statementType="CALLABLE">
<![CDATA[
/* db-as-is.updColumn : Update AS-IS Column */
CALL SP_DB_COL_COMMENT (#{p.owner , mode=IN, jdbcType=VARCHAR}
,#{p.table_name , mode=IN, jdbcType=VARCHAR}
,#{p.column_name, mode=IN, jdbcType=VARCHAR}
,#{p.comments , mode=IN, jdbcType=VARCHAR});
]]>
</update>
</mapper>
4. db-to-be.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="db-to-be">
<select id="selTableListByColumn" resultType="map">
/* db-to-be.selTableListByColumn : Column을 TO-BE Table 목록 조회 */
SELECT T.OWNER
, T.TABLE_NAME
, T.TABLE_TYPE
, T.COMMENTS AS TABLE_COMMENTS
, C.COLUMN_NAME
, C.COMMENTS
, U.USE_TYPE_CD
, U.USE_DESC
, D.DATA_TYPE
, CASE
WHEN D.DATA_TYPE = 'NUMBER' AND D.DATA_SCALE IS NOT NULL THEN NVL(D.DATA_PRECISION, 38) || ', ' || D.DATA_SCALE
WHEN D.DATA_TYPE IN ('CHAR', 'VARCHAR', 'NVARCHAR') THEN D.CHAR_LENGTH || DECODE(D.CHAR_USED, 'B', ' BYTE', 'C', ' CHAR')
ELSE ' '
END AS DATA_LENGTH
FROM ALL_TAB_COMMENTS T
JOIN ALL_COL_COMMENTS C ON C.OWNER = T.OWNER
AND C.TABLE_NAME = T.TABLE_NAME
<if test="g.isNotEmpty(p.column_name)">
AND C.COLUMN_NAME LIKE '%' || UPPER(TRIM(#{p.column_name})) || '%'
</if>
<if test="g.isNotEmpty(p.comments)">
AND C.COMMENTS LIKE '%' || TRIM(#{p.comments}) || '%'
</if>
JOIN ALL_TAB_COLUMNS D ON D.OWNER = C.OWNER
AND D.TABLE_NAME = C.TABLE_NAME
AND D.COLUMN_NAME = C.COLUMN_NAME
LEFT OUTER JOIN ALL_TAB_USE_DESC U ON U.OWNER = T.OWNER
AND U.TABLE_NAME = T.TABLE_NAME
AND U.TABLE_TYPE = T.TABLE_TYPE
WHERE T.TABLE_TYPE = 'TABLE'
<if test="@org.apache.commons.collections4.CollectionUtils@isNotEmpty(p.owners)">
AND T.OWNER IN
<foreach collection="p.owners" item="owner" separator="," open="(" close=")">
#{owner}
</foreach>
</if>
ORDER BY T.OWNER
, T.TABLE_NAME
</select>
<sql id="SQL_selTable">
<![CDATA[
SELECT T.OWNER
, T.TABLE_NAME
, T.TABLE_TYPE
, T.COMMENTS
, U.USE_TYPE_CD
, U.USE_DESC
FROM ALL_TAB_COMMENTS T
LEFT OUTER JOIN ALL_TAB_USE_DESC U ON U.OWNER = T.OWNER
AND U.TABLE_NAME = T.TABLE_NAME
AND U.TABLE_TYPE = T.TABLE_TYPE
]]>
</sql>
<select id="selTableList" resultType="map">
/* db-to-be.selTableList : TO-BE Table 목록 */
<include refid="SQL_selTable"/>
WHERE T.TABLE_TYPE = 'TABLE'
<if test="@org.apache.commons.collections4.CollectionUtils@isNotEmpty(p.owners)">
AND T.OWNER IN
<foreach collection="p.owners" item="owner" separator="," open="(" close=")">
#{owner}
</foreach>
</if>
<choose>
<when test="@org.apache.commons.collections4.CollectionUtils@isNotEmpty(p.table_names)">
AND T.TABLE_NAME IN
<foreach collection="p.table_names" item="table_name" separator="," open="(" close=")">
#{table_name}
</foreach>
</when>
<when test="g.isNotEmpty(p.table_name)">
AND T.TABLE_NAME LIKE '%' || UPPER(TRIM(#{p.table_name})) || '%'
</when>
</choose>
<if test="g.isNotEmpty(p.comments)">
AND T.COMMENTS LIKE '%' || TRIM(#{p.comments}) || '%'
</if>
ORDER BY T.OWNER
, T.TABLE_NAME
</select>
<select id="selColumnList" resultType="map">
<![CDATA[
/* db-to-be.selColumnList : TO-BE Table Column 목록 */
SELECT TAB.OWNER AS OWNER
, TAB.TABLE_NAME AS TABLE_NAME
, TO_CHAR(COL.COLUMN_ID + 1) AS NO
, COL.COLUMN_NAME AS COLUMN_NAME
, REPLACE(CMT.COMMENTS, CHR(10), ' ') AS COMMENTS
, CST.ISPK AS PK_YN
, CST.PKNO AS PK_NO
, CST.ISAK AS AK_YN
, CST.AK AS AK_NO
, COL.DATA_TYPE AS DATA_TYPE
, CASE
WHEN COL.DATA_TYPE = 'NUMBER' AND COL.DATA_SCALE IS NOT NULL THEN NVL(COL.DATA_PRECISION, 38) || ', ' || COL.DATA_SCALE
WHEN COL.DATA_TYPE IN ('CHAR', 'VARCHAR', 'NVARCHAR') THEN COL.CHAR_LENGTH || DECODE(COL.CHAR_USED, 'B', ' BYTE', 'C', ' CHAR')
ELSE ' '
END AS DATA_LENGTH
, TO_CHAR(DECODE(COL.CHAR_USED, 'C', COL.CHAR_LENGTH * LENGTHB('가'), COL.DATA_LENGTH)) AS BYTE_LENGTH
, DECODE(COL.NULLABLE, 'N', 'Y', 'Y', 'N') AS NOTNULL_YN
, TRIM(FC_DB_DEFAULT(COL.OWNER, COL.TABLE_NAME, COL.COLUMN_NAME)) AS COLUMN_DEFAULT
, TRIM(DECODE(CST.ISFK, 'Y', 'FK', ' ') || ' ' || DECODE(CST.ISAK, 'Y', 'AK', ' ')) AS CST_DESC
FROM ALL_TAB_COMMENTS TAB
JOIN ALL_TAB_COLUMNS COL ON COL.OWNER = TAB.OWNER
AND COL.TABLE_NAME = TAB.TABLE_NAME
JOIN ALL_COL_COMMENTS CMT ON CMT.OWNER = COL.OWNER
AND CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'Y')) AS ISPK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'PK(' || TO_CHAR(CTC.POSITION + 1) || ') ')) AS PK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', CTC.POSITION + 1)) AS PKNO
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'U', 'Y')) AS ISAK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'U', CTC.CONSTRAINT_NAME || '(' || TO_CHAR(CTC.POSITION + 1) || ')'), ', ') WITHIN GROUP (ORDER BY CST.CONSTRAINT_NAME) AS AK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'R', 'Y')) AS ISFK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME || '(' || TO_CHAR(CTC.POSITION + 1) || ')'), ', ') WITHIN GROUP (ORDER BY CST.CONSTRAINT_NAME) AS FK
FROM ALL_CONS_COLUMNS CTC
JOIN ALL_CONSTRAINTS CST ON CST.OWNER = CTC.OWNER
AND CST.CONSTRAINT_NAME = CTC.CONSTRAINT_NAME
AND CST.CONSTRAINT_TYPE IN ('P', 'R', 'U')
AND CST.TABLE_NAME = CTC.TABLE_NAME
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.OWNER = COL.OWNER
AND CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
WHERE TAB.TABLE_TYPE = {#p.table_type}
AND TAB.OWNER = {#p.owner}
AND TAB.TABLE_NAME = {#p.table_name}
ORDER BY COL.COLUMN_ID
]]>
</select>
<select id="selTable" resultType="map">
/* db-to-be.selTable : TO-BE Table */
<include refid="SQL_selTable"/>
WHERE TAB.TABLE_TYPE = {#p.table_type}
AND TAB.OWNER = {#p.owner}
AND TAB.TABLE_NAME = {#p.table_name}
</select>
<update id="updTable">
<![CDATA[
/* db-to-be.updTable : Update TO-BE Table */
BEGIN
-- 1. Update Table's comments
SP_DB_TAB_COMMENT (#{p.owner , mode=IN, jdbcType=VARCHAR}
,#{p.table_name , mode=IN, jdbcType=VARCHAR}
,#{p.comments , mode=IN, jdbcType=VARCHAR});
-- 2. Update Table using description
MERGE INTO ALL_TAB_USE_DESC T
USING (SELECT #{p.owner} AS OWNER -- PK(1) Owner
, #{p.table_name} AS TABLE_NAME -- PK(2) Table Name
, #{p.table_type} AS TABLE_TYPE -- PK(3) Table Type
, #{p.use_type_cd} AS USE_TYPE_CD -- Using Type Code [TAB_USE_TYPE]
, #{p.use_desc} AS USE_DESC -- Using Description
, #{g.username} AS REG_ID -- 등록자 아이디
, #{g.now} AS REG_DT -- 등록 일시
, #{g.username} AS MOD_ID -- 수정자 아이디
, #{g.now} AS MOD_DT -- 수정 일시
FROM DUAL) D
ON ( D.OWNER = T.OWNER
AND D.TABLE_NAME = T.TABLE_NAME
AND D.TABLE_TYPE = T.TABLE_TYPE)
WHEN MATCHED THEN
UPDATE SET USE_TYPE_CD = D.USE_TYPE_CD -- Using Type Code [TAB_USE_TYPE]
, USE_DESC = D.USE_DESC -- Using Description
--, REG_ID = D.REG_ID -- 등록자 아이디
--, REG_DT = D.REG_DT -- 등록 일시
, MOD_ID = D.MOD_ID -- 수정자 아이디
, MOD_DT = D.MOD_DT -- 수정 일시
WHEN NOT MATCHED THEN
INSERT ( OWNER -- PK(1) Owner
, TABLE_NAME -- PK(2) Table Name
, TABLE_TYPE -- PK(3) Table Type
, USE_TYPE_CD -- Using Type Code [TAB_USE_TYPE]
, USE_DESC -- Using Description
, REG_ID -- 등록자 아이디
, REG_DT -- 등록 일시
, MOD_ID -- 수정자 아이디
, MOD_DT) -- 수정 일시
VALUES ( D.OWNER -- PK(1) Owner
, D.TABLE_NAME -- PK(2) Table Name
, D.TABLE_TYPE -- PK(3) Table Type
, D.USE_TYPE_CD -- Using Type Code [TAB_USE_TYPE]
, D.USE_DESC -- Using Description
, D.REG_ID -- 등록자 아이디
, D.REG_DT -- 등록 일시
, D.MOD_ID -- 수정자 아이디
, D.MOD_DT); -- 수정 일시
END;
]]>
</update>
<update id="updColumn" statementType="CALLABLE">
<![CDATA[
/* db-to-be.updColumn : Update TO-BE Column */
CALL SP_DB_COL_COMMENT (#{p.owner , mode=IN, jdbcType=VARCHAR}
,#{p.table_name , mode=IN, jdbcType=VARCHAR}
,#{p.column_name, mode=IN, jdbcType=VARCHAR}
,#{p.comments , mode=IN, jdbcType=VARCHAR});
]]>
</update>
</mapper>
5. DBCompareController.java
package smartsuite.app.bp.admin.db;
import java.util.List;
import java.util.Map;
import javax.inject.Inject;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import smartsuite.app.common.shared.Const;
import smartsuite.security.annotation.AuthCheck;
@SuppressWarnings({"rawtypes", "unchecked"})
@Controller
@RequestMapping(value="**/common/db/")
public class DBCompareController
{
@Inject
DBCompareService dbCompareService;
/**
* Table 목록 조회 By Column
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selTableListByColumn
*/
@AuthCheck(authCode=Const.READ)
@RequestMapping(value = "selTableListByColumn.do")
public @ResponseBody List selTableListByColumn(@RequestBody Map param)
{
return dbCompareService.selTableListByColumn(param);
}
/**
* AS-IS Table 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selTableListAsIs
*/
@AuthCheck(authCode=Const.READ)
@RequestMapping(value = "selTableListAsIs.do")
public @ResponseBody List selTableListAsIs(@RequestBody Map param)
{
return dbCompareService.selTableListAsIs(param);
}
/**
* TO-BE Table 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selTableListToBe
*/
@AuthCheck(authCode=Const.READ)
@RequestMapping(value = "selTableListToBe.do")
public @ResponseBody List selTableListToBe(@RequestBody Map param)
{
return dbCompareService.selTableListToBe(param);
}
/**
* AS-IS Table Column 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selColumnListAsIs
*/
@AuthCheck(authCode=Const.READ)
@RequestMapping(value = "selColumnListAsIs.do")
public @ResponseBody List selColumnListAsIs(@RequestBody Map param)
{
return dbCompareService.selColumnListAsIs(param);
}
/**
* TO-BE Table Column 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selColumnListToBe
*/
@AuthCheck(authCode=Const.READ)
@RequestMapping(value = "selColumnListToBe.do")
public @ResponseBody List selColumnListToBe(@RequestBody Map param)
{
return dbCompareService.selColumnListToBe(param);
}
/**
* Table 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : Map
* @Date : 2021-11-01
* @Method Name : selTable
*/
@AuthCheck(authCode=Const.READ)
@RequestMapping(value = "selTable.do")
public @ResponseBody Map selTable(@RequestBody Map param)
{
return dbCompareService.selTable(param);
}
/**
* Table 저장
*
* @Author : Aiden Park
* @Param : Map
* @Return : Map
* @Date : 2021-11-01
* @Method Name : updTable
*/
@AuthCheck(authCode=Const.READ)
@RequestMapping(value = "updTable.do")
public @ResponseBody Map updTable(@RequestBody Map param)
{
return dbCompareService.updTable(param);
}
}
6. DBCompareService.java
package smartsuite.app.bp.admin.db;
import java.util.List;
import java.util.Map;
import javax.inject.Inject;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.ktnet.ets.hub.common.util.StringUtil;
import smartsuite.app.common.shared.Const;
@SuppressWarnings ({"rawtypes", "unchecked"})
@Service
@Transactional
public class DBCompareService
{
private static final Log LOG = LogFactory.getLog(DBCompareService.class);
@Inject
private SqlSession sqlSessionASIS;
@Inject
private SqlSession sqlSession;
private static final String NAMESPACE_AS_IS = "db-as-is.";
private static final String NAMESPACE_TO_BE = "db-to-be.";
/**
* Table 목록 조회 By Column
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selTableListByColumn
*/
public List<Map<String, Object>> selTableListByColumn(Map<String, Object> param)
{
SqlSession _sqlSession = null;
String _namespace = null;
if ("ASIS".equals(param.get("dbType")))
{
_sqlSession = sqlSessionASIS;
_namespace = NAMESPACE_AS_IS;
}
else
{
_sqlSession = sqlSession;
_namespace = NAMESPACE_TO_BE;
}
return _sqlSession.selectList(_namespace + "selTableListByColumn", param);
}
/**
* AS-IS Table 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selTableListAsIs
*/
public List<Map<String, Object>> selTableListAsIs(Map<String, Object> param)
{
return this.selTableList(sqlSessionASIS, NAMESPACE_AS_IS, param);
}
/**
* TO-BE Table 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selTableListToBe
*/
public List<Map<String, Object>> selTableListToBe(Map<String, Object> param)
{
return this.selTableList(sqlSession, NAMESPACE_TO_BE, param);
}
/**
* Table 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selTableList
*/
private List<Map<String, Object>> selTableList(SqlSession sqlSession, String namespace, Map<String, Object> param)
{
return sqlSession.selectList(namespace + "selTableList", param);
}
/**
* AS-IS Table Column 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selColumnListAsIs
*/
public List<Map<String, Object>> selColumnListAsIs(Map<String, Object> param)
{
return this.selColumnList(sqlSessionASIS, NAMESPACE_AS_IS, param);
}
/**
* TO-BE Table Column 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selColumnListToBe
*/
public List<Map<String, Object>> selColumnListToBe(Map<String, Object> param)
{
return this.selColumnList(sqlSession, NAMESPACE_TO_BE, param);
}
/**
* Table Column 목록 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : List
* @Date : 2021-11-01
* @Method Name : selColumnList
*/
private List<Map<String, Object>> selColumnList(SqlSession sqlSession, String namespace, Map<String, Object> param)
{
return sqlSession.selectList(namespace + "selColumnList", param);
}
/**
* Table 조회
*
* @Author : Aiden Park
* @Param : Map
* @Return : Map
* @Date : 2021-11-01
* @Method Name : selTable
*/
public Map<String, Object> selTable(Map<String, Object> param)
{
Map<String, Object> resultMap = Maps.newHashMap();
resultMap.put(Const.RESULT_STATUS, Const.SUCCESS);
SqlSession _sqlSession = null;
String _namespace = null;
if ("ASIS".equals(param.get("dbType")))
{
_sqlSession = sqlSessionASIS;
_namespace = NAMESPACE_AS_IS;
}
else
{
_sqlSession = sqlSession;
_namespace = NAMESPACE_TO_BE;
}
Map<String, Object> _table = Maps.newHashMap();
List<Map<String, Object>> _columnList = Lists.newArrayList();
_table = _sqlSession.selectOne(_namespace + "selTable", param);
_columnList = _sqlSession.selectList(_namespace + "selColumnList", param);
resultMap.put("table" , _table);
resultMap.put("columnList" , _columnList);
return resultMap;
}
/**
* Table 저장
*
* @Author : Aiden Park
* @Param : Map
* @Return : Map
* @Date : 2021-11-01
* @Method Name : updTable
*/
public Map<String, Object> updTable(Map<String, Object> param)
{
Map<String, Object> resultMap = Maps.newHashMap();
resultMap.put(Const.RESULT_STATUS, Const.SUCCESS);
SqlSession _sqlSession = null;
String _namespace = null;
if ("ASIS".equals(param.get("dbType")))
{
_sqlSession = sqlSessionASIS;
_namespace = NAMESPACE_AS_IS;
}
else
{
_sqlSession = sqlSession;
_namespace = NAMESPACE_TO_BE;
}
Map<String, Object> _table = (Map<String, Object>)param.get("table");
List<Map<String, Object>> _columnList = (List<Map<String, Object>>)param.get("columnList");
String _comments = StringUtil.nvl(_table.get("comments"), "");
_comments = _comments.replace("'", "");
_table.put("comments", _comments);
_sqlSession.update(_namespace + "updTable", _table);
for (Map<String, Object> _column : _columnList)
{
_comments = StringUtil.nvl(_column.get("comments"), "");
_comments = _comments.replace("'", "");
_column.put("comments", _comments);
_sqlSession.update(namespace + "updColumn", _column);
}
return resultMap;
}
}
7. em-db-compare-table.html
<sc-link rel="import" href="es-db-compare-table.html"></sc-link>
<dom-module id="em-db-compare-table">
<style>
:host {
@apply(--fit-layout);
}
</style>
<template>
<sc-pages id="pages" selected="0" class="fit">
<es-db-compare-table id="list"></es-db-compare-table>
</sc-pages>
</template>
<script>
Polymer({
is : "em-db-compare-table",
initialized : function(event)
{
var me = this;
var param = me.params;
me.$.list.load();
},
});
</script>
</dom-module>