<jsp:directive.page pageEncoding="UTF-8" />
<!-- PostgreSQL 版 -->
<og:equals val1="{@DBF.NAME @FROM_DBID}" val2="POSTGRES">
	<og:value command="SET" key="IND_COLUMNS">
		select COLUMN_NAME AS CLM,'' as CONDITION,COLUMN_NAME AS VAL,ORDINAL_POSITION AS SEQ
		from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
		where CONSTRAINT_SCHEMA = 'public'
		and   TABLE_NAME = '{@TABLE_NAME}'
		union
		select '' as CLM,'' as CONDITION,'' as VAL,999 as SEQ
		order by 4
	</og:value>

	<og:value command="SET" key="TAB_COLUMNS">
		select COLUMN_NAME
		from INFORMATION_SCHEMA.COLUMNS
		where  TABLE_NAME   = '{@TABLE_NAME}'
		and    TABLE_SCHEMA = 'public'
		order by TABLE_NAME,ORDINAL_POSITION
	</og:value>

	<og:value command="SET" key="TABLES">
		select TABLENAME
		from PG_TABLES
		where  TABLENAME   = '{@TABLE_NAME}'
		and    SCHEMANAME  = 'public'
		order by TABLENAME
	</og:value>

	<og:value command="SQL" action="ROW_APPEND"  dbid="{@FROM_DBID}" >
		select COLUMN_NAME		AS UNIQS
		from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
		where TABLE_NAME = '{@TABLE_NAME}'
		and   CONSTRAINT_SCHEMA = 'public'
		and   CONSTRAINT_NAME LIKE '%_pkey'
		order by TABLE_NAME,ORDINAL_POSITION
	</og:value>
</og:equals>

<!-- ORACLE 版 -->
<og:equals val1="{@DBF.NAME @FROM_DBID}" val2="ORACLE">
	<og:value command="SET" key="IND_COLUMNS">
		select A.COLUMN_NAME as CLM,'' as CONDITION,A.COLUMN_NAME as VAL,A.COLUMN_ID as SEQ
		from USER_TAB_COLUMNS A,USER_IND_COLUMNS B
		where  A.TABLE_NAME = '{@TABLE_NAME}'
		and    A.TABLE_NAME  = B.TABLE_NAME
		and    A.COLUMN_NAME = B.COLUMN_NAME
		union
		select '' as CLM,'' as CONDITION,'' as VAL,999 as SEQ
		from DUAL
		order by 4
	</og:value>

	<og:value command="SET" key="TAB_COLUMNS">
		select A.COLUMN_NAME,COALESCE(C.COMMENTS,A.COLUMN_NAME) AS CLM_NAME
		from USER_TAB_COLUMNS A
					 left outer join USER_COL_COMMENTS C
					on  A.TABLE_NAME  = C.TABLE_NAME
					and A.COLUMN_NAME = C.COLUMN_NAME
		where  A.TABLE_NAME = '{@TABLE_NAME}'
		order by A.TABLE_NAME,A.COLUMN_ID
	</og:value>

	<og:value command="SET" key="TABLES">
		select TABLE_NAME
		from USER_TABLES
		where  INSTR(TABLE_NAME,'$') < 1
		order by TABLE_NAME
	</og:value>

<!-- NONUNIQ だが、INDEX_NAME が、xx_SA -->
	<og:value command="SQL" action="ROW_APPEND"  dbid="{@FROM_DBID}" >
		select a.COLUMN_NAME		AS INDX_SA
		from USER_IND_COLUMNS a inner join USER_INDEXES b
			on  a.TABLE_NAME = b.TABLE_NAME
			and a.INDEX_NAME = b.INDEX_NAME
		where a.TABLE_NAME = '{@TABLE_NAME}'
	--	and   b.UNIQUENESS = 'UNIQUE'
		and   b.UNIQUENESS = 'NONUNIQUE'
		and   a.INDEX_NAME like '%SA'
		order by a.TABLE_NAME,a.COLUMN_POSITION
	</og:value>

<!-- NUNIQ キー (プライマリでないので論理ユニークのイメージ) -->
	<og:value command="SQL" action="ROW_APPEND"  dbid="{@FROM_DBID}" >
		select a.COLUMN_NAME		AS LUQ
		from USER_IND_COLUMNS a inner join USER_CONSTRAINTS b
			on  a.TABLE_NAME = b.TABLE_NAME
			and a.INDEX_NAME = b.CONSTRAINT_NAME
		where a.TABLE_NAME      = '{@TABLE_NAME}'
		and   b.CONSTRAINT_TYPE = 'U'
		order by a.TABLE_NAME,a.COLUMN_POSITION
	</og:value>

<!-- プライマリキー (シーケンス等、実際の並び順には適さない) -->
	<og:value command="SQL" action="ROW_APPEND"  dbid="{@FROM_DBID}" >
		select a.COLUMN_NAME		AS UNIQS
		from USER_IND_COLUMNS a inner join USER_CONSTRAINTS b
			on  a.TABLE_NAME = b.TABLE_NAME
			and a.INDEX_NAME = b.CONSTRAINT_NAME
		where a.TABLE_NAME      = '{@TABLE_NAME}'
		and   b.CONSTRAINT_TYPE = 'P'
		order by a.TABLE_NAME,a.COLUMN_POSITION
	</og:value>
</og:equals>

<!-- FIREBIRD 版 -->
<og:equals val1="{@DBF.NAME @FROM_DBID}" val2="FIREBIRD">
	<og:value command="SET" key="IND_COLUMNS">
		select	trim(a.RDB$FIELD_NAME) as CLM , '' as CONDITION,
				trim(a.RDB$FIELD_NAME) as VAL , trim(a.RDB$FIELD_POSITION) as SEQ
		from RDB$RELATION_FIELDS a
		where  a.RDB$SYSTEM_FLAG = 0
		and    trim(a.RDB$RELATION_NAME) = '{@TABLE_NAME}'
		and  exists (select 'X' from RDB$INDICES b inner join RDB$INDEX_SEGMENTS c
						on     b.RDB$INDEX_NAME  = c.RDB$INDEX_NAME
						where  b.RDB$SYSTEM_FLAG = 0
						and    b.RDB$RELATION_NAME = a.RDB$RELATION_NAME
						and    c.RDB$FIELD_NAME    = a.RDB$FIELD_NAME
		)
		union
		select '' as CLM,'' as CONDITION,'' as VAL,999 as SEQ
		from RDB$DATABASE
		order by 4
	</og:value>

	<og:value command="SET" key="TAB_COLUMNS">
		select trim(RDB$FIELD_NAME)
		from RDB$RELATION_FIELDS
		where  RDB$SYSTEM_FLAG = 0
		and    RDB$RELATION_NAME = '{@TABLE_NAME}'
		order by RDB$RELATION_NAME,RDB$FIELD_POSITION
	</og:value>

	<og:value command="SET" key="TABLES">
		select trim(RDB$RELATION_NAME)
		from RDB$RELATIONS
		where RDB$SYSTEM_FLAG   = 0
		and   RDB$RELATION_TYPE = 0
		order by RDB$RELATION_NAME
	</og:value>

	<og:value command="SQL" action="ROW_APPEND"  dbid="{@FROM_DBID}" >
		select trim(b.RDB$FIELD_NAME) AS UNIQS
		from RDB$INDICES a inner join RDB$INDEX_SEGMENTS b
		on   a.RDB$INDEX_NAME = b.RDB$INDEX_NAME
		where trim(a.RDB$RELATION_NAME) = '{@TABLE_NAME}'
		and   a.RDB$UNIQUE_FLAG   = '1'
		order by a.RDB$RELATION_NAME,b.RDB$FIELD_POSITION
	</og:value>
</og:equals>

<!-- MySQL 版 -->
<!-- ※ CONDITION が、予約語みたいで、そのままだとエラーになる。 -->
<!-- MySQL の UNIONでは、select文を 括弧() で、囲う。 -->
<og:equals val1="{@DBF.NAME @FROM_DBID}" val2="MYSQL">
	<og:value command="SET" key="IND_COLUMNS">
		(
			select	A.COLUMN_NAME    AS CLM , '' as "CONDITION",
					A.COLUMN_COMMENT AS VAL , A.ORDINAL_POSITION AS SEQ
			from INFORMATION_SCHEMA.COLUMNS A
			where A.table_schema=database()
			and   A.TABLE_NAME = '{@TABLE_NAME}'
			and   exists (select 'X' from information_schema.statistics b
							where  A.table_schema = B.table_schema
							and    A.TABLE_NAME   = B.TABLE_NAME
							and    A.COLUMN_NAME  = B.COLUMN_NAME
			)
		)
		union
		(
			select '' as CLM,'' as "CONDITION",'' as VAL,999 as SEQ
			from DUAL
		)
		order by SEQ
	</og:value>

	<og:value command="SET" key="TAB_COLUMNS">
		select COLUMN_NAME,
				case when length(COLUMN_COMMENT) = 0 then COLUMN_NAME
					 else COLUMN_COMMENT end AS CLM_NAME
		from INFORMATION_SCHEMA.COLUMNS
		where table_schema=database()
		and   TABLE_NAME = '{@TABLE_NAME}'
		order by TABLE_NAME,ORDINAL_POSITION
	</og:value>

	<og:value command="SET" key="TABLES">
		select TABLE_NAME , TABLE_NAME {@DBF.CON} ' : ' {@DBF.CON} TABLE_COMMENT
		from INFORMATION_SCHEMA.TABLES
		where table_schema=database()
		and   TABLE_TYPE = 'BASE TABLE'
		order by TABLE_NAME
	</og:value>

<!-- NONUNIQ だが、INDEX_NAME が、xx_SA -->
	<og:value command="SQL" action="ROW_APPEND"  dbid="{@FROM_DBID}" >
		select a.COLUMN_NAME		AS INDX_SA
		from  information_schema.statistics A left outer join information_schema.table_constraints B
				on  A.table_schema = B.constraint_schema
				and A.TABLE_NAME   = B.TABLE_NAME
				and A.INDEX_NAME   = B.CONSTRAINT_NAME
		where A.table_schema=database()
		and   a.TABLE_NAME = '{@TABLE_NAME}'
		and   a.NON_UNIQUE = '1'
		order by a.TABLE_NAME,a.SEQ_IN_INDEX
	</og:value>

<!-- NONUNIQ キー (プライマリでないので論理ユニークのイメージ) -->
	<og:value command="SQL" action="ROW_APPEND"  dbid="{@FROM_DBID}" >
		select a.COLUMN_NAME		AS LUQ
		from  information_schema.statistics A left outer join information_schema.table_constraints B
				on  A.table_schema = B.constraint_schema
				and A.TABLE_NAME   = B.TABLE_NAME
				and A.INDEX_NAME   = B.CONSTRAINT_NAME
		where A.table_schema=database()
		and   a.TABLE_NAME = '{@TABLE_NAME}'
		and   a.NON_UNIQUE = '0'
		order by a.TABLE_NAME,a.SEQ_IN_INDEX
	</og:value>

<!-- プライマリキー (シーケンス等、実際の並び順には適さない) -->
	<og:value command="SQL" action="ROW_APPEND"  dbid="{@FROM_DBID}" >
		select a.COLUMN_NAME		AS UNIQS
		from  information_schema.statistics A left outer join information_schema.table_constraints B
				on  A.table_schema = B.constraint_schema
				and A.TABLE_NAME   = B.TABLE_NAME
				and A.INDEX_NAME   = B.CONSTRAINT_NAME
		where A.table_schema=database()
		and   a.TABLE_NAME = '{@TABLE_NAME}'
		and   a.NON_UNIQUE = '0'
		and   b.CONSTRAINT_TYPE = 'PRIMARY KEY'
		order by a.TABLE_NAME,a.SEQ_IN_INDEX
	</og:value>
</og:equals>

<!-- SQLServer版 2017/04/14 ADD -->
<og:equals val1="{@DBF.NAME @FROM_DBID}" val2="SQLSERVER">
	<og:value command="SET" key="IND_COLUMNS">
		select	b.NAME as CLM,'' as CONDITION,b.NAME as VAL,b.COLUMN_ID as SEQ
		from	SYS.TABLES a
		inner join SYS.COLUMNS b
		on	A.OBJECT_ID = B.OBJECT_ID
		where A.NAME = '{@TABLE_NAME}'
		union SELECT '' as CLM,'' as CONDITION,'' as VAL,999 as SEQ
		order by 4
	</og:value>

	<og:value command="SET" key="TAB_COLUMNS">
		select B.NAME COLUMN_NAME
		from SYS.TABLES A
		inner join SYS.COLUMNS B
		on A.OBJECT_ID = B.OBJECT_ID
		where A.NAME = '{@TABLE_NAME}'
	</og:value>

	<og:value command="SET" key="TABLES">
		select		NAME
		from		SYS.TABLES
		order by	NAME
	</og:value>

	<og:value command="SET" key="ROW_APPEND">
		select		D.NAME as COL_NAME
		from		SYS.TABLES as A
		inner join	SYS.KEY_CONSTRAINTS as B
		on 			A.OBJECT_ID	= B.PARENT_OBJECT_ID
		and			B.TYPE		= 'PK'
		inner join	SYS.INDEX_COLUMNS as C
		on			B.PARENT_OBJECT_ID = C.OBJECT_ID
		and			B.UNIQUE_INDEX_ID  = C.INDEX_ID
		inner join	SYS.COLUMNS as D
		on			C.OBJECT_ID	= D.OBJECT_ID
		and			C.COLUMN_ID	= D.COLUMN_ID
		where		A.NAME	= '{@TABLE_NAME}'
		order by	D.COLUMN_ID
	</og:value>
</og:equals>
