<?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="com.zkqy.datamodeling.mapper.TableInfoMapper">
    
    <resultMap id="mysqlTableInfoResult" type="com.zkqy.datamodeling.domain.TableInfo">
        <result property="fieldName" column="fieldName"></result>
        <result property="fieldType" column="fieldType"></result>
        <result property="isNull" column="isNull" javaType="boolean"></result>
        <result property="isPrimary" column="isPrimary" javaType="boolean"></result>
        <result property="fieldDescription" column="fieldDescription"></result>
        <result property="fieldLength" column="fieldLength"></result>
        <result property="isAuto" column="isAuto" javaType="boolean"></result>
    </resultMap>

    <insert id="executeCreateTableSql">
        ${createTableSql}
    </insert>

    <update id="useDataBase">
        use ${dataBaseName}
    </update>

    <update id="createMysqlTable">
        CREATE TABLE IF NOT EXISTS `${databases}`.`${tableName}`
        (
        <foreach collection="filedList" item="filed" separator=",">
            ${filed}
        </foreach>
        )COMMENT #{tableComment};
    </update>

    <insert id="createMysqlDataBase">
        CREATE
        DATABASE
        #{dataBaseName};
    </insert>

    <insert id="createSqlServerDataBase">
        CREATE
        DATABASE
        #{dataBaseName};
    </insert>



    <select id="mysqlDataBaseExist" resultType="int">
        SELECT count(1) FROM information_schema.SCHEMATA where SCHEMA_NAME= #{dataBaseName};
    </select>

    <select id="mysqlTableExist" resultType="int">
        SELECT count(1) FROM information_schema.TABLES t, information_schema.SCHEMATA n WHERE t.table_name = #{tableName} AND n.SCHEMA_NAME = #{dataBaseName};
    </select>

    <select id="tableInfoList" resultType="com.zkqy.datamodeling.domain.vo.TableInfoVO">
        select table_name tableName,create_time createTime,table_comment tableComment from information_schema.tables where table_schema=#{databaseName}
        <if test="map.tableName != null and map.tableName != ''">and table_name like concat('%', #{map.tableName}, '%')</if>
        <if test="map.tableComment != null and map.tableComment !=''">and table_comment like concat('%', #{map.tableComment}, '%')</if>
        order by createTime DESC
    </select>

<!--    <select id="selectDataCount" resultType="int">-->
<!--        SELECT table_rows FROM information_schema.tables-->
<!--        WHERE TABLE_SCHEMA = #{dataBaseName}-->
<!--          and table_name = #{tableName}-->
<!--    </select>-->

    <select id="selectDataCount" resultType="int">
        select count(1) from ${tableName}
    </select>

    <delete id="dropMysqlTable">
        DROP TABLE IF EXISTS `${dataBaseName}`.`${tableName}`;
    </delete>

<!--    <update id="updateMysqlTable">-->
<!--        START TRANSACTION;-->
<!--        DROP TABLE `${dataBaseName}`.`${tableName}`;-->
<!--        IF NOT EXISTS (  CREATE TABLE `${dataBaseName}`.`${tableName}`-->
<!--        (-->
<!--        <foreach collection="filedList" item="filed" separator=",">-->
<!--            ${filed}-->
<!--        </foreach>-->
<!--        )COMMENT #{tableComment};-->
<!--         ) THEN ROLLBACK;-->
<!--        END IF;-->

<!--        COMMIT;-->
<!--    </update>-->

    <update id="updateMysqlTable">
        DROP TABLE `${dataBaseName}`.`${tableName}`;
        CREATE TABLE `${dataBaseName}`.`${tableName}`
        (
        <foreach collection="filedList" item="filed" separator=",">
            ${filed}
        </foreach>
        ) COMMENT #{tableComment};

    </update>

    <select id="mysqlTableFieldInfo" resultMap="mysqlTableInfoResult">
        SELECT
            column_name fieldName,
            IF
                ( is_nullable = 'NO', TRUE, FALSE ) isNull,
            data_type fieldType,
            character_maximum_length fieldLength,
            IF
                ( column_key = 'PRI', TRUE, FALSE ) isPrimary,
            column_comment fieldDescription ,
            if(extra='auto_increment',true,false) isAuto
        FROM
            information_schema.COLUMNS
        WHERE
            table_name = #{tableName}
          AND table_schema = #{dataBaseName}
          ORDER BY ordinal_position asc;

    </select>

    <select id="backupTableSql" resultType="map">
        SHOW CREATE TABLE `${dataBaseName}`.`${tableName}`
    </select>


    <select id="sqlServerTableExist" resultType="int">
        select count(1) from sysobjects where id = object_id(#{tableName})
    </select>


    <update id="createSqlServerTable">
        create table ${tableName}
        (
        <foreach collection="filedList" item="filed" separator=",">
            ${filed}
        </foreach>
        );
        <foreach collection="descriptionList" item="description" separator=";">
            ${description}
        </foreach>
        ;EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'${tableComment}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'${tableName}'
    </update>

    <select id="existOrNot" resultType="int">
        select count(1) From master.dbo.sysdatabases where name = #{dataBaseName}
    </select>

    <select id="sqlServerTableInfoList" resultType="com.zkqy.datamodeling.domain.vo.TableInfoVO">
        select
            a.name AS tableName,
            a.create_date AS createTime,
            CONVERT ( NVARCHAR ( 100 ), isnull( g.[value], '' ) ) AS tableComment
        from ${databaseName}.sys.tables a
                 LEFT JOIN sys.extended_properties g ON ( a.object_id = g.major_id AND g.minor_id = 0 )
        where a.type = 'U'
        <if test="map.tableName != null and map.tableName != ''">and a.name like concat('%', #{map.tableName}, '%')</if>
        <if test="map.tableComment != null and map.tableComment !=''">and CONVERT ( NVARCHAR ( 100 ), isnull( g.[value], '' ) ) like concat('%', #{map.tableComment}, '%')</if>
        order by createTime DESC
    </select>

    <select id="selectSqlServerDataCount" resultType="int">
        select count(1) from ${tableName}
    </select>

    <delete id="dropSqlserverTable">
        drop table ${tableName}
    </delete>

    <update id="updateSqlserverTable">
        CREATE TABLE ${tableName}
        (
        <foreach collection="filedList" item="filed" separator=",">
            ${filed}
        </foreach>
        );
        <foreach collection="descriptionList" item="description" separator=";">
            ${description}
        </foreach>
        ;EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'${tableComment}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'${tableName}'
    </update>

    <select id="sqlserverTableFieldInfo" resultMap="mysqlTableInfoResult">
        SELECT

        fieldName     = a.name,
        isAuto       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end,
        isPrimary       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
        SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
        fieldType       = b.name,
        fieldLength       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
        isNull     = case when a.isnullable=1 then 0 else 1 end,

        fieldDescription   = isnull(g.[value],'')
        FROM
        syscolumns a
        left join
        systypes b
        on
        a.xusertype=b.xusertype
        inner join
        sysobjects d
        on
        a.id=d.id  and d.xtype='U' and  d.name<![CDATA[<>]]>'dtproperties'
        left join
        syscomments e
        on
        a.cdefault=e.id
        left join
        sys.extended_properties   g
        on
        a.id=G.major_id and a.colid=g.minor_id
        left join
        sys.extended_properties f
        on
        d.id=f.major_id and f.minor_id=0
        where
        d.name=#{tableName}
        order by
        a.id,a.colorder
    </select>


    <select id="dmTableExist" resultType="int">
        select count(1) from dba_segments where dba_segments.OWNER=#{dataBaseName} and SEGMENT_NAME=#{tableName}
    </select>

    <select id="dmDataBaseExist" resultType="int">
        select COUNT(1) from sysobjects where NAME=#{dataBaseName} and SUBTYPE$ is null
    </select>

    <update id="createDmDataBase">
        CREATE SCHEMA ${databaseName}
    </update>

<!--    <update id="useDmDataBase">-->
<!--        set schema ${dataBaseName};-->
<!--    </update>-->

    <update id="createDmTable">

        create table ${databaseName}.${tableName}
        (
            <foreach collection="filedList" item="filed" separator=",">
                ${filed}
            </foreach>
        );
    </update>

    <update id="addTableDescription">
           ${description}
    </update>

    <update id="addDmTableComment">
        comment on table ${databaseName}.${tableName} is '${tableComment}';
    </update>

    <select id="dmTableInfoList" resultType="com.zkqy.datamodeling.domain.vo.TableInfoVO">
        select a.object_name as tableName,b.comment$ as tableComment,a.created as createTime
        from dba_objects a
                 left join SYSTABLECOMMENTS b on b.tvname = a.object_name and b.schname = a.owner
        where a.object_type = 'TABLE' and a.owner = #{databaseName}
        <if test="map.tableName != null and map.tableName != ''">and a.object_name like concat('%', #{map.tableName}, '%')</if>
        <if test="map.tableComment != null and map.tableComment !=''">and b.comment$ like concat('%', #{map.tableComment}, '%')</if>
        order by createTime DESC
    </select>

    <select id="selectDmDataCount" resultType="int">
        select count(1) from ${tableName}
    </select>

    <delete id="dropDmTable">
        drop table ${dataBaseName}.${tableName}
    </delete>

    <select id="queryDmTableCount" resultType="int">
        select count(1)
        from dba_objects a
        left join SYSTABLECOMMENTS b on b.tvname = a.object_name
        where a.object_type = 'TABLE' and a.owner = #{databaseName}
        <if test="map.tableName != null and map.tableName != ''">and a.object_name like concat('%', #{map.tableName}, '%')</if>
        <if test="map.tableComment != null and map.tableComment !=''">and b.comment$ like concat('%', #{map.tableComment}, '%')</if>
    </select>

    <select id="dmTableFieldInfo" resultMap="mysqlTableInfoResult">
        SELECT a.table_name As tableName
             , c.comments As tableComment
             , a.column_name As fieldName
             , b.comments As fieldDescription
             , a.data_type As fieldType
             , a.data_length As fieldLength
             ,(case when d.column_position = 1 then 1 else 0 end) As isPrimary
             ,(case when (e.nullable = 'N') then 1 else 0 end) as isNull
             ,(case when f.info2 = 1 then 1 else 0 end) as  isAuto
        FROM all_tab_cols a
                 LEFT JOIN all_col_comments b ON b.table_name = a.table_name AND b.column_name = a.column_name AND a.owner = b.schema_name
                 LEFT JOIN all_tab_comments c ON c.table_name = b.table_name AND c.owner      = b.owner
                 LEFT JOIN ( SELECT dcc.table_name,dcc.column_name,dcc.column_position,dcc.index_owner,dc.constraint_name
                             FROM all_ind_columns dcc
                                      JOIN all_constraints dc ON dcc.index_name = dc.index_name AND dc.constraint_type = UPPER('p') AND dcc.index_owner = dc.owner) d
                           ON d.table_name  = a.table_name AND d.column_name = a.column_name AND d.index_owner = a.owner
                 LEFT JOIN all_tab_columns e ON a.table_name = e.table_name AND a.owner = e.owner and a.column_name = e.column_name
                 LEFT JOIN (SELECT f1.owner,f1.object_name As table_name,f0.name,f0.info2 FROM syscolumns f0
                                                                                                   INNER JOIN dba_objects f1 ON f1.object_type = 'TABLE' AND info2 =1 AND f1.object_id = f0.id
        ) f ON f.name = a.column_name AND f.table_name = a.table_name AND f.owner = a.owner
        WHERE a.owner = UPPER(#{dataBaseName}) and a.table_name = #{tableName}
        ORDER BY a.table_name DESC, a.column_id DESC;
    </select>

    <update id="createOracleUser">
        CREATE USER ${username} IDENTIFIED BY ${password} DEFAULT TABLESPACE users QUOTA unlimited ON users
    </update>

    <update id="assignAuthority">
        GRANT ALL PRIVILEGES TO ${username}
    </update>

    <select id="oracleTableExist" resultType="int">
        select count(1) from user_tables where table_name=upper(#{tableName})
    </select>

    <update id="createOracleTable">
        create table ${tableName}
        (
            <foreach collection="filedList" item="filed" separator=",">
                ${filed}
            </foreach>
        )
    </update>

    <update id="createSequence">
        create sequence ${sequenceName}
            increment by 1
            start with 1
            maxvalue 100000000
            minvalue 1
            nocache
            nocycle
    </update>

    <update id="createTrigger">
        create or replace trigger ${triggerName}
        before insert on ${triggerName}
        for each row
        begin
        select ${triggerName}.nextval into :new.${autoField} from dual;
        end
    </update>

    <update id="addOracleTableDescription">
        ${description}
    </update>

    <select id="oracleTableInfoList" resultType="com.zkqy.datamodeling.domain.vo.TableInfoVO">
        SELECT
            a.table_name tableName,
            a.comments tableComment,
            o.created createTime
        FROM
            all_tab_comments a
                LEFT JOIN all_objects o ON a.owner = o.owner
                AND a.table_name = o.object_name
        WHERE
            a.table_type = 'TABLE'
          AND a.owner = #{databaseName}
        <if test="map.tableName != null and map.tableName != ''">and a.table_name like concat('%', #{map.tableName}, '%')</if>
        <if test="map.tableComment != null and map.tableComment !=''">and a.comments like concat('%', #{map.tableComment}, '%')</if>
    </select>


</mapper>
