<?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.datau.mdm.mapper.DatabaseManageMapper">

	<insert id="saveDatabase" parameterType="com.datau.mdm.dto.MdmInfoDatabase">
		insert into
		mdm_info_database(id,resource_name,ip,resource_type,resource_port,
		username,password,database_name,database_type,charset,update_user_id,update_time)
		values(#{id},#{resourceName},#{ip},#{resourceType},#{resourcePort},#{username}
		,#{password},#{databaseName},#{databaseType},#{charset},#{updateUserId},now())
	</insert>

	<select id="listDatabaseType" resultType="com.datau.mdm.dto.MdmDimDatabaseType">
		select
		id,type_name from
		mdm_dim_database_type
	</select>

	<select id="listResourceType" resultType="com.datau.mdm.dto.MdmDimResourceType">
		select
		id,type_name from
		mdm_dim_resource_type
	</select>

	<select id="listCharsetType" resultType="com.datau.mdm.dto.MdmDimResourceCharset">
		select
		id,charset_name
		from
		mdm_dim_database_charset
	</select>
	<select id="listDatabase" resultType="com.datau.mdm.dto.MdmInfoDatabase">
		SELECT
		mid.id,
		resource_name,
		ip,
		resource_type,
		resource_port,
		mid.username,
		mid.PASSWORD , mddt.type_name database_type_name,
		mdrt.type_name
		resource_type_name,
		mddc.charset_name charset_name,
		database_name,
		database_type,
		charset,
		update_user_id,
		update_time,diu.nickname update_user_name
		FROM
		mdm_info_database
		mid
		LEFT
		JOIN mdm_dim_database_type mddt ON mid.database_type = mddt.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
		LEFT JOIN
		dc_info_user diu ON mid.update_user_id = diu.id

		where
		1 = 1
		<if test="resourceName !=null &amp;&amp; resourceName !=''">
			and resource_name like concat('%',#{resourceName},'%')
		</if>
		<if test="resourceType !=null">
			and resource_type = #{resourceType}
		</if>
	</select>

	<select id="getResorceTypeById" resultType="com.datau.mdm.dto.MdmDimResourceType">
		select
		id,type_name,url,driver_name,type_desc from mdm_dim_resource_type
		where
		id = #{resourceType}
	</select>
	<select id="getDatabaseById" resultType="com.datau.mdm.dto.MdmInfoDatabase">
		SELECT
		mid.id,
		resource_name,
		ip,
		resource_type,
		resource_port,
		username,
		PASSWORD , mddt.type_name database_type_name,
		mdrt.type_name
		resource_type_name,
		mddc.charset_name charset_name, database_name,
		database_type,
		charset,
		update_user_id,
		update_time
		FROM
		mdm_info_database
		mid
		LEFT JOIN mdm_dim_database_type mddt ON mid.database_type = mddt.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="id !=null &amp;&amp; id !=''">
			and mid.id = #{id}
		</if>
	</select>
	<update id="updateDatabase" parameterType="com.datau.mdm.dto.MdmInfoDatabase">

		update mdm_info_database set
		update_time = now()
		<if test="resourceName !=null &amp;&amp; resourceName !=''">
			,resource_name = #{resourceName}
		</if>
		<if test="resourceType !=null">
			,resource_type = #{resourceType}
		</if>
		<if test="ip !=null &amp;&amp; ip !=''">
			,ip = #{ip}
		</if>
		<if test="resourcePort !=null &amp;&amp; resourcePort !=''">
			,resource_port = #{resourcePort}
		</if>
		<if test="databaseType !=null">
			,database_type = #{databaseType}
		</if>
		<if test="charset !=null">
			,charset = #{charset}
		</if>
		<if test="updateUserId !=null &amp;&amp; updateUserId !=''">
			,update_user_id = #{updateUserId}
		</if>
		where id = #{id}
	</update>
	<delete id="removeDatabase" parameterType="java.lang.String">
		delete from
		mdm_info_database where id = #{id}
	</delete>
	<select id="listColumnIdByDatabaseId" resultType="java.lang.String">
		SELECT
		mic.id
		FROM
		mdm_info_column mic
		LEFT JOIN mdm_info_table mit ON mic.table_id =
		mit.id
		LEFT JOIN
		mdm_info_database mid ON mit.database_id = mid.id
		where
		mid.id =
		#{databaseId}
	</select>
	<select id="checkDatabaseName" resultType="java.lang.Integer">
		SELECT
		if(count(0)= 0,0,1)
		FROM
		mdm_info_database
		WHERE
		1 = 1 and
		resource_name = #{resourceName}
		<if test="databaseId !=null &amp;&amp; databaseId !=''">
			and id != #{databaseId}
		</if>
	</select>
	<delete id="removeTablesByDatabaseId" parameterType="java.lang.String">
		delete from
		mdm_info_table where database_id = #{databaseId}
	</delete>
	<delete id="removeColumnsByDatabaseId" parameterType="java.lang.String">
		delete mic
		from mdm_info_column mic left join mdm_info_table mit on
		mic.table_id =
		mit.id where mit.database_id = #{databaseId}

	</delete>
	<select id="countDatabase" parameterType="java.lang.String"
		resultType="java.lang.Integer">
		select count(0) from mdm_info_database where ip = #{ip} and
		resource_port = #{resourcePort} and database_name = #{databaseName}
	</select>
</mapper>