<?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.ozs.mdm.mapper.StorageManageMapper">

	<select id="listDatabaseOption" resultType="com.ozs.mdm.dto.MdmInfoDatabase">
		SELECT
		mid.id,
		resource_name,
		database_name,
		resource_type,
		mdrt.type_name
		resource_type_name
		FROM
		mdm_info_database mid
		LEFT JOIN
		mdm_dim_resource_type mdrt ON mid.resource_type = mdrt.id where
		1=1 and
		database_type != 1
		<if test="resourceName !=null &amp;&amp; resourceName !=''">
			and resource_name like concat('%',#{resourceName},'%')
		</if>
	</select>
	<select id="listDatabaseOptionAll" resultType="com.ozs.mdm.dto.MdmInfoDatabase">
		SELECT
		mid.id,
		resource_name,
		database_name,
		resource_type,
		mdrt.type_name
		resource_type_name
		FROM
		mdm_info_database mid
		LEFT JOIN
		mdm_dim_resource_type mdrt ON mid.resource_type = mdrt.id where
		1=1
		<if test="resourceName !=null &amp;&amp; resourceName !=''">
			and resource_name like concat('%',#{resourceName},'%')
		</if>
	</select>
	<select id="listCreateTableOption" resultType="com.ozs.mdm.dto.MdmDimCreateOption">
		select id,name
		from mdm_dim_create_option
	</select>

	<select id="listColumnTypeByIds" resultType="com.ozs.mdm.dto.MdmDimColumnType">
		select id,type_name,is_float_point,is_time from mdm_dim_column_type
		where id in(
		<foreach collection="list" separator="," item="column">
			#{column.columnType}
		</foreach>
		)
	</select>

	<select id="getDatabaseById" resultType="com.ozs.mdm.dto.MdmInfoDatabase">
		select mid.id,
		mid.resource_name,
		mid.ip,
		mid.username,
		mid.password,
		mid.resource_port,
		mid.database_name,
		mdrt.type_name resource_type_name,
		mdrt.driver_name,
		mdrt.url,
		mid.charset ,
		mddc.charset_name
		from
		mdm_info_database mid left join
		mdm_dim_resource_type mdrt on
		mid.resource_type = mdrt.id left join mdm_dim_database_charset mddc on
		mid.charset = mddc.id
		where 1=1
		<if test="databaseId !=null &amp;&amp; databaseId !=''">
			and mid.id = #{databaseId}
		</if>
	</select>
	<select id="getCharsetNameById" resultType="java.lang.String">
		select charset_name from mdm_dim_database_charset
		where 1=1
		<if test="charsetId !=null &amp;&amp; charsetId !=''">
			and id = #{charsetId}
		</if>
	</select>
	<insert id="saveTable" parameterType="com.ozs.mdm.dto.MdmCreateTableDTO">
		insert into
		mdm_info_table(id,table_name,table_comment,table_desc,database_id,charset
		,hive_separator_row,hive_separator_column,hive_partition,update_user_id,update_time)
		values(#{tableId},#{tableName},#{tableComment},#{tableDesc},#{databaseId},#{charsetId}
		,#{hiveSeparatorRow},#{hiveSeparatorColumn},#{hivePartition},#{updateUserId},now())
	</insert>
	<insert id="saveColumns" parameterType="java.util.Map">
		insert into
		mdm_info_column(id,column_code,column_name,column_comment,column_type,length
		,numeric_scale,if_primary,if_null,table_id,update_user_id,update_time)
		values
		<foreach collection="list" separator="," item="column">
			(uuid(),#{column.columnCode},#{column.columnName},#{column.columnComment},#{column.columnType},#{column.length}
			,#{column.numericScale},#{column.ifPrimary},#{column.ifNull},#{column.tableId},#{column.updateUserId},now())
		</foreach>
	</insert>
	<select id="getMaxColumnCode" resultType="java.lang.Integer">
		select if (
		max(column_code) is null,
		0,
		max(column_code)
		)
		from mdm_info_column for
		update
	</select>
	<select id="listColumnsTypeName" resultType="java.lang.String">
		select distinct
		type_name from mdm_dim_column_type where resource_type =
		#{resourceType}
	</select>

	<select id="listColumnTypeByNames" resultType="com.ozs.mdm.dto.MdmDimColumnType">
		select id,type_name from mdm_dim_column_type where type_name
		in(
		<foreach collection="list" separator="," item="column">
			#{column.columnTypeName}
		</foreach>
		)
	</select>
	<select id="listTable" resultType="com.ozs.mdm.dto.MdmInfoTable">
		select
		mit.id,table_comment,table_name,mid.database_name,mid.resource_name,mit.update_time,
		diu.nickname updateUserName
		from mdm_info_table mit left join
		mdm_info_database mid on
		mit.database_id = mid.id LEFT JOIN
		dc_info_user diu ON
		mit.update_user_id = diu.id
		where 1=1 and
		mid.database_type != 1
		<if test="tableName !=null &amp;&amp; tableName !=''">
			and mit.table_name like concat('%',#{tableName},'%')
		</if>
		<if test="tableComment !=null &amp;&amp; tableComment !=''">
			and mit.table_comment like concat('%',#{tableComment},'%')
		</if>
		<if test="databaseId !=null &amp;&amp; databaseId !=''">
			and mit.database_id = #{databaseId}
		</if>
	</select>
	<select id="listTableScan" resultType="com.ozs.mdm.dto.MdmInfoTable">
		select
		mit.id,table_comment,table_name,mid.database_name,mid.resource_name,mit.update_time,
		diu.nickname updateUserName
		from mdm_info_table mit left join
		mdm_info_database mid on
		mit.database_id = mid.id LEFT JOIN
		dc_info_user diu ON
		mit.update_user_id = diu.id
		where 1=1
		<if test="tableName !=null &amp;&amp; tableName !=''">
			and mit.table_name like concat('%',#{tableName},'%')
		</if>
		<if test="tableComment !=null &amp;&amp; tableComment !=''">
			and mit.table_comment like concat('%',#{tableComment},'%')
		</if>
		<if test="databaseId !=null &amp;&amp; databaseId !=''">
			and mit.database_id = #{databaseId}
		</if>
	</select>
	<select id="getTableInfoById" resultType="com.ozs.mdm.dto.MdmCreateTableDTO">
		SELECT
		mit.id,
		table_name,
		table_comment,
		mid.id database_id,
		mid.database_name,
		mit.table_desc,
		mit.update_time,
		mit.hive_separator_row,
		mit.hive_separator_column,
		mit.hive_partition,
		mit.update_user_id
		FROM
		mdm_info_table mit
		LEFT JOIN mdm_info_database mid ON mit.database_id =
		mid.id
		where mit.id = #{tableId}
	</select>
	<update id="removeTableById" parameterType="java.lang.String">
		delete from
		mdm_info_table where id = #{tableId}
	</update>
	<update id="removeColumnByTableId" parameterType="java.lang.String">
		delete from
		mdm_info_column
		where table_id = #{tableId}
	</update>
	<select id="listColumnIdByTableId" resultType="java.lang.String">
		select id from
		mdm_info_column where table_id = #{tableId}
	</select>
	<insert id="saveUpdateColumn" parameterType="com.ozs.mdm.dto.UpdateColumnDTO">
		insert into
		mdm_info_column(id,column_code,column_name,column_comment,column_type,length
		,numeric_scale,if_primary,if_null,table_id,update_user_id,update_time)
		values
		<foreach collection="list" separator="," item="column">
			(uuid(),#{column.columnCode},#{column.columnName},#{column.columnComment},#{column.columnType},#{column.length}
			,#{column.numericScale},#{column.ifPrimary},#{column.ifNull},#{column.tableId},#{column.updateUserId},now())
		</foreach>
	</insert>
	<update id="updateUpdateColumn" parameterType="com.ozs.mdm.dto.UpdateColumnDTO">
		update
		mdm_info_column set
		column_name = case
		<foreach collection="list" separator=" " item="column">
			when id =
			#{column.id} then #{column.columnName}
		</foreach>
		end ,
		column_comment = case
		<foreach collection="list" separator=" " item="column">
			when id =
			#{column.id} then #{column.columnComment}
		</foreach>
		end ,
		column_type =case
		<foreach collection="list" separator=" " item="column">
			when id =
			#{column.id} then #{column.columnType}
		</foreach>
		end ,
		length =case
		<foreach collection="list" separator=" " item="column">
			when id =
			#{column.id} then #{column.length}
		</foreach>
		end ,
		numeric_scale =case
		<foreach collection="list" separator=" " item="column">
			when id =
			#{column.id} then #{column.numericScale}
		</foreach>
		end ,
		if_primary =case
		<foreach collection="list" separator=" " item="column">
			when id =
			#{column.id} then #{column.ifPrimary}
		</foreach>
		end ,
		if_null =case
		<foreach collection="list" separator=" " item="column">
			when id =
			#{column.id} then #{column.ifNull}
		</foreach>
		end ,update_time = now()
		where id in (
		<foreach collection="list" separator="," item="column">
			#{column.id}
		</foreach>
		)
	</update>
	<delete id="removeUpdateColumn" parameterType="com.ozs.mdm.dto.UpdateColumnDTO">
		delete from mdm_info_column where id in(
		<foreach collection="list" separator="," item="column">
			#{column.id}
		</foreach>
		)
	</delete>
	<update id="updateTableInfo" parameterType="com.ozs.mdm.dto.UpdateTableDTO">
		update mdm_info_table
		set
		<if test="newTableName !=null &amp;&amp; newTableName !=''">
			table_name = #{newTableName},
		</if>
		<if test="tableComment !=null &amp;&amp; tableComment !=''">
			table_comment = #{tableComment},
		</if>
		<if test="tableDesc !=null &amp;&amp; tableDesc !=''">
			table_desc =
			#{tableDesc},
		</if>
		<if test="updateUserId !=null &amp;&amp; updateUserId !=''">
			update_user_id =
			#{updateUserId},
		</if>
		update_time =
		now()
		where id = #{id}
	</update>
	<select id="listColumsByTableId" resultType="com.ozs.mdm.dto.MdmInfoColumn">
		SELECT
		mic.id,
		mic.column_code,
		mic.column_name,
		mic.column_comment,
		mic.column_type,
		mic.length,
		mic.numeric_scale,
		mic.if_primary,
		mic.if_null,
		mic.update_time,
		mdct.type_name columnTypeName,
		mdct.is_float_point,mdct.is_time,
		mit.database_id
		FROM
		mdm_info_column
		mic
		LEFT JOIN
		mdm_dim_column_type mdct ON
		mic.column_type = mdct.id
		LEFT
		JOIN
		mdm_info_table mit on mic.table_id = mit.id
		WHERE
		1 = 1
		<if test="tableId !=null &amp;&amp; tableId !=''">
			and mic.table_id = #{tableId}
		</if>
		order by column_code
	</select>

	<select id="listColumTypeByDatabaseType" resultType="com.ozs.mdm.dto.MdmDimColumnType">
		select id,type_desc,type_name,resource_type,is_float_point,is_time
		from
		mdm_dim_column_type
		where 1=1
		<if test="resourceType !=null &amp;&amp; resourceType !=''">
			and resource_type = #{resourceType}
		</if>
	</select>

	<select id="getDatabaseByTableId" resultType="com.ozs.mdm.dto.MdmInfoDatabase">
		select mid.id,
		mid.resource_name,
		mid.ip,
		mid.username,
		mid.password,
		mid.resource_port,
		mid.database_name,
		mdrt.type_name resource_type_name,
		mdrt.driver_name,
		mdrt.url,
		mid.charset ,
		mddc.charset_name
		from
		mdm_info_table mit
		left join mdm_info_database mid on mit.database_id =
		mid.id
		left join
		mdm_dim_resource_type mdrt on
		mid.resource_type =
		mdrt.id left join mdm_dim_database_charset mddc on
		mid.charset =
		mddc.id
		where 1=1
		<if test="tableId !=null &amp;&amp; tableId !=''">
			and mit.id = #{tableId}
		</if>
	</select>
	<select id="listCreateTableByIds" resultType="com.ozs.mdm.dto.MdmCreateTableDTO">
		select
		mit.id tableId,table_comment,mit.charset
		charsetId,table_desc,table_name,mid.database_name,mid.resource_name,mddc.charset_name
		from mdm_info_table mit left join mdm_info_database mid on
		mit.database_id = mid.id
		left join mdm_dim_database_charset mddc on
		mid.charset = mddc.id where 1=1
		and mit.id in (
		<foreach collection="list" item="id" separator=",">
			#{id}
		</foreach>
		)
	</select>
	<select id="listColumnByTableIds" resultType="com.ozs.mdm.dto.MdmInfoColumn">
		SELECT
		mic.id,
		mic.table_id,
		mic.column_code,
		mic.column_name,
		mic.column_comment,
		mic.column_type,
		mic.length,
		mic.numeric_scale,
		mic.if_primary,
		mic.if_null,
		mic.update_time,
		mdct.type_name
		columnTypeName
		FROM
		mdm_info_column mic
		LEFT JOIN mdm_dim_column_type
		mdct ON
		mic.column_type = mdct.id
		left join mdm_info_table mit on
		mic.table_id
		= mit.id
		WHERE
		1 = 1 and
		mic.table_id in (
		<foreach collection="list" item="id" separator=",">
			#{id}
		</foreach>
		)
	</select>
	<select id="listTableNameByDatabaseId" resultType="java.lang.String">
		select
		table_name from mdm_info_table where database_id = #{databaseId}
	</select>
	<insert id="saveCopyTables" parameterType="com.ozs.mdm.dto.MdmCreateTableDTO">
		insert into
		mdm_info_table(id,table_name,table_comment,table_desc,database_id,charset
		,hive_separator_row,hive_separator_column,hive_partition,update_user_id,update_time)
		values
		<foreach collection="list" item="table" separator=",">
			(#{table.tableId},#{table.tableName},#{table.tableComment},#{table.tableDesc},#{table.databaseId},#{table.charsetId}
			,#{table.hiveSeparatorRow},#{table.hiveSeparatorColumn},#{table.hivePartition},#{table.updateUserId},now())
		</foreach>
	</insert>
	<select id="listColumnTypeAll" resultType="com.ozs.mdm.dto.MdmDimColumnType">
		select
		id,type_name,is_float_point,is_time from mdm_dim_column_type
	</select>
	<select id="listTableByDatabaseId" resultType="com.ozs.mdm.dto.MdmCreateTableDTO">
		select
		mit.id
		tableId,table_comment,mit.charset
		charsetId,table_desc,table_name,mid.database_name,mid.resource_name,mddc.charset_name
		from mdm_info_table mit left join mdm_info_database mid on
		mit.database_id = mid.id
		left join mdm_dim_database_charset mddc on
		mid.charset = mddc.id where 1=1
		and mit.database_id = #{databaseId}
	</select>
	<select id="listColumnByDatabaseId" resultType="com.ozs.mdm.dto.MdmInfoColumn">
		SELECT
		mic.id,
		mic.column_code,
		mic.column_name,
		mic.column_comment,
		mic.column_type,
		mic.length,
		mic.numeric_scale,
		mic.if_primary,
		mic.if_null,
		mic.update_time,
		mdct.type_name columnTypeName,
		mdct.is_float_point,mdct.is_time,
		mit.database_id,
		mit.table_name
		
		FROM
		mdm_info_column
		mic
		LEFT JOIN
		mdm_dim_column_type mdct ON
		mic.column_type = mdct.id
		LEFT
		JOIN
		mdm_info_table mit on mic.table_id = mit.id
		WHERE
		1 = 1
		and
		mit.database_id = #{databaseId}
		order by column_code
	</select>
	<delete id="removeColumnById" parameterType="java.lang.String">
		delete from
		mdm_info_column where id = #{id}
	</delete>
</mapper>