<?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.github.wxiaoqi.security.generator.mapper.GeneratorMapper">
	<select id="queryList" resultType="map">
		select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables 
			where table_schema = (select database()) 
		<if test="tableName != null and tableName.trim() != ''">
			and table_name like concat('%', #{tableName}, '%') 
		</if>
		order by create_time desc
		<if test="offset != null and limit != null">
			limit #{offset}, #{limit}
		</if>
	</select>
	
	<select id="queryTotal" resultType="int">
		select count(*) from information_schema.tables where table_schema = (select database())
		<if test="tableName != null and tableName.trim() != ''">
			and table_name like concat('%', #{tableName}, '%')
		</if>
	</select>

	<select id="queryTable" resultType="map">
		select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables
			where table_schema = (select database()) and table_name = #{tableName}
	</select> 
	
	<select id="queryColumns" resultType="map">
		select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns
 			where table_name = #{tableName} and table_schema = (select database()) order by ordinal_position
	</select>

	<select id="psqlQueryList" resultType="map">
		select c.relname "tableName", 'InnoDB' as engine, cast(obj_description(c.relfilenode,'pg_class') as varchar) as "tableComment", '2020-01-01 00:00:00' as "createTime"
		from pg_class c
		where c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0)
		<if test="tableName != null and tableName.trim() != ''">
			and c.relname like '%'||#{tableName}||'%'
		</if>
		order by c.relfilenode desc
		<if test="offset != null and limit != null">
			limit #{limit} offset #{offset}
		</if>
	</select>

	<select id="psqlQueryTotal" resultType="int">
		select count(*)
		from pg_class c
		where c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0)
		<if test="tableName != null and tableName.trim() != ''">
			and c.relname like '%'||#{tableName}||'%'
		</if>
	</select>

	<select id="psqlQueryTable" resultType="map">
		select c.relname "tableName", 'InnoDB' as engine, cast(obj_description(c.relfilenode,'pg_class') as varchar) as "tableComment", '2020-01-01 00:00:00' as "createTime"
		from pg_class c
		where c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0)
		 and c.relname = #{tableName}
	</select>

	<select id="psqlQueryColumns" resultType="map">
	select a.attname as "columnName",d.description "columnComment",concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as "dataType"
from pg_class c,pg_attribute a,pg_type t,pg_description d
where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
  and c.relname in (select c from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by relfilenode desc, c.relname,a.attnum

		select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns
 			where table_name = #{tableName} and table_schema = (select database()) order by ordinal_position
	</select>
</mapper>