package hiro.yoshioka.sql;

public interface IOracleConst {
	String STATEMENT_ID = "OracleSQL";

	String[] SKIP_SCHEMA_NAMES_AT_GET_ALL_META_DATA = { "ANONYMOUS", "CTXSYS",
			"DBSNMP", "DIP", "FLOWS_020100", "FLOWS_FILES", "HR", "MDSYS",
			"OUTLN", "TSMSYS", "XDB", "SYS", "SYSTEM" };

	String _SELECT_USER_OBJECTS = " SELECT                                                                          "
			+ "    DECODE(O.OBJECT_TYPE,'TABLE','1','VIEW','2','FUNCTION','3','PROCEDURE','4',  'TRIGGER','5','SEQUENCE','6','SYNONYM','0',O.OBJECT_TYPE)  AS SO,"
			+ "    O.OBJECT_NAME  ,                                                              "
			+ "    O.OBJECT_TYPE  ,                                                              "
			+ "    O.STATUS  ,                                                                   "
			+ "    DECODE(O.OBJECT_TYPE,'SYNONYM',S.TABLE_OWNER||'.' || S.TABLE_NAME, C.COMMENTS) COMMENTS                                                                    "
			+ "  FROM                                                                            "
			+ "    USER_OBJECTS  O                                                               "
			+ "    LEFT JOIN SYS.USER_TAB_COMMENTS C ON (O.OBJECT_NAME = C.TABLE_NAME AND O.OBJECT_TYPE = C.TABLE_TYPE)"
			+ "    LEFT JOIN USER_SYNONYMS S ON (S.SYNONYM_NAME = O.OBJECT_NAME AND O.OBJECT_TYPE='SYNONYM') "
			+ "  WHERE                                                                           "
			+ "    O.OBJECT_TYPE <> 'INDEX'                                                      "
			+ "  ORDER BY                                                                        "
			+ "    1, 2                                                                         ";

	String SELECT_PK = "SELECT  "
			+ " S.TABLE_NAME,S.COLUMN_NAME "
			+ "FROM ALL_CONSTRAINTS M  "
			+ "INNER JOIN ALL_CONS_COLUMNS S ON (M.CONSTRAINT_NAME = S.CONSTRAINT_NAME) "
			+ "WHERE M.OWNER = ? AND M.CONSTRAINT_TYPE='P' ";

	/** */
	String _SELECT_SYNOYMS = "SELECT SYNONYM_NAME, TABLE_OWNER,TABLE_NAME FROM ALL_SYNONYMS WHERE OWNER = ? AND SYNONYM_NAME = ? ";
	/** テーブルコメント取得用SQL */
	String _SELECT_TAB_COMMENTS = "SELECT OWNER,TABLE_NAME,TABLE_TYPE,COMMENTS FROM SYS.ALL_TAB_COMMENTS WHERE COMMENTS IS NOT NULL AND OWNER = ?";

	String _SELECT_PRIMARY_KEY = "SELECT  " + "  C.OWNER,    C.TABLE_NAME,  "
			+ "  C.COLUMN_NAME, T.VALIDATED  " + "FROM "
			+ "  ALL_CONS_COLUMNS  C,  ALL_CONSTRAINTS  T " + "WHERE "
			+ "  T.CONSTRAINT_TYPE='P' "
			+ "  AND C.CONSTRAINT_NAME = T.CONSTRAINT_NAME "
			+ "  AND C.OWNER = ? AND C.TABLE_NAME = ? ";

	String _SELECT_COL_INFO_ALL = " SELECT UC.TABLE_NAME,UC.COLUMN_NAME,UC.DATA_TYPE,UC.DATA_LENGTH,                "
			+ " UC.DATA_PRECISION,UC.DATA_SCALE,UC.NULLABLE,COM.COMMENTS,DECODE(PK.COLUMN_NAME,NULL,NULL,'PK') PK"
			+ " FROM USER_TAB_COLUMNS UC                                                           "
			+ " LEFT JOIN user_COL_COMMENTS COM ON (UC.TABLE_NAME=COM.TABLE_NAME AND UC.COLUMN_NAME = COM.COLUMN_NAME)"
			+ " LEFT JOIN (                                                                     "
			+ " 	SELECT                                                                         "
			+ " 	  C.TABLE_NAME, C.COLUMN_NAME, T.VALIDATED                                     "
			+ " 	FROM                                                                           "
			+ " 	  USER_CONS_COLUMNS  C                                                         "
			+ " 	  INNER JOIN USER_CONSTRAINTS T ON (C.CONSTRAINT_NAME = T.CONSTRAINT_NAME AND T.CONSTRAINT_TYPE='P')"
			+ " ) PK ON (UC.TABLE_NAME=PK.TABLE_NAME AND UC.COLUMN_NAME = PK.COLUMN_NAME)       "
			+ " ORDER BY UC.TABLE_NAME,UC.COLUMN_ID                                             ";

	String _SELECT_COL_INFO_A_TABLE_ONLY_ME = " SELECT UC.TABLE_NAME,UC.COLUMN_NAME,UC.DATA_TYPE,UC.DATA_LENGTH,                "
			+ " UC.DATA_PRECISION,UC.DATA_SCALE,UC.NULLABLE,COM.COMMENTS,DECODE(PK.COLUMN_NAME,NULL,NULL,'PK') PK"
			+ " FROM USER_TAB_COLUMNS UC                                                           "
			+ " LEFT JOIN user_COL_COMMENTS COM ON (UC.TABLE_NAME=COM.TABLE_NAME AND UC.COLUMN_NAME = COM.COLUMN_NAME)"
			+ " LEFT JOIN (                                                                     "
			+ " 	SELECT                                                                         "
			+ " 	  C.TABLE_NAME, C.COLUMN_NAME, T.VALIDATED                                     "
			+ " 	FROM                                                                           "
			+ " 	  USER_CONS_COLUMNS  C                                                         "
			+ " 	  INNER JOIN USER_CONSTRAINTS T ON (C.CONSTRAINT_NAME = T.CONSTRAINT_NAME AND T.CONSTRAINT_TYPE='P')"
			+ " ) PK ON (UC.TABLE_NAME=PK.TABLE_NAME AND UC.COLUMN_NAME = PK.COLUMN_NAME)       "
			+ " WHERE UPPER(UC.TABLE_NAME) = UPPER(?)                                             "
			+ " ORDER BY UC.COLUMN_ID                                             ";

	String _SELECT_COL_INFO_A_TABLE_FROMALL = " SELECT UC.OWNER,UC.TABLE_NAME,UC.COLUMN_NAME,UC.DATA_TYPE,UC.DATA_LENGTH,                "
			+ " UC.DATA_PRECISION,UC.DATA_SCALE,UC.NULLABLE,COM.COMMENTS,DECODE(PK.COLUMN_NAME,NULL,NULL,'PK') PK"
			+ "  FROM ALL_TAB_COLUMNS UC                                                           "
			+ "  LEFT JOIN all_COL_COMMENTS COM ON (UC.OWNER = COM.OWNER AND UC.TABLE_NAME=COM.TABLE_NAME AND UC.COLUMN_NAME = COM.COLUMN_NAME)"
			+ "  LEFT JOIN (                                                                     "
			+ "  	SELECT                                                                         "
			+ "  	  C.TABLE_NAME, C.COLUMN_NAME, T.VALIDATED, C.OWNER                               "
			+ "  	FROM                                                                           "
			+ "  	  ALL_CONS_COLUMNS  C                                                         "
			+ "  	  INNER JOIN ALL_CONSTRAINTS T ON (C.OWNER = T.OWNER AND C.CONSTRAINT_NAME = T.CONSTRAINT_NAME AND T.CONSTRAINT_TYPE ='P')"
			+ "  ) PK ON (UC.OWNER=PK.OWNER AND UC.TABLE_NAME=PK.TABLE_NAME AND UC.COLUMN_NAME = PK.COLUMN_NAME)       "
			+ " WHERE UPPER(UC.OWNER) = UPPER(?) AND UPPER(UC.TABLE_NAME) = UPPER(?)                                             "
			+ " ORDER BY UC.COLUMN_ID                                             ";

	String _SELECT_TAB_COL_COMMENTS_SCHEMA = "SELECT OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS "
			+ "FROM SYS.ALL_COL_COMMENTS "
			+ "WHERE COMMENTS IS NOT NULL AND OWNER = ? AND TABLE_NAME NOT LIKE '%BIN$%' ORDER BY 1,2,3";

	String _SELECT_TAB_COL_COMMENTS_TABLE = "SELECT OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS "
			+ "FROM SYS.ALL_COL_COMMENTS "
			+ "WHERE COMMENTS IS NOT NULL AND OWNER = ? AND TABLE_NAME = ? AND TABLE_NAME NOT LIKE '%BIN$%' ORDER BY 1,2,3";

	String _SELECT_TRIGGER = "select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME,STATUS from ALL_TRIGGERS  where owner = ?";
	String _SELECT_SEQUENCES = "SELECT SEQUENCE_NAME \"NAME\", "
			+ "MIN_VALUE \"MIN\", " + "MAX_VALUE \"MAX\", "
			+ "INCREMENT_BY \"INCREMENT\", " + "CYCLE_FLAG \"CYCLE\", "
			+ "ORDER_FLAG, " + "CACHE_SIZE \"CACHE\", LAST_NUMBER \"START\", "
			+ "LAST_NUMBER " + " FROM ALL_SEQUENCES where SEQUENCE_OWNER = ?";

	String _SELECT_VALIDATION = "SELECT OBJECT_NAME, OBJECT_TYPE,STATUS FROM USER_OBJECTS WHERE OBJECT_NAME NOT LIKE 'BIN$%' ";

	String _SELECT_WORST = " SELECT                "
			+ "   SQL_FULLTEXT TEXT   "
			+ "   ,TO_CHAR(elapsed_time/executions/1000,'9990.000') \"a_time(msec)\"  "
			+ "   ,cpu_time          " + "   ,elapsed_time      "
			+ "   ,ROWS_PROCESSED    " + "   ,OPTIMIZER_MODE    "
			+ "   ,OPTIMIZER_COST    " + "   ,address           "
			+ "   ,hash_value        " + "   ,parse_calls       "
			+ "   ,executions        " + "   ,MODULE            "
			+ " FROM                 " + "   V$SQL              "
			+ " WHERE                " + "   executions>0       "
			+ "   AND sql_text not like '%v$sql%'                   "
			+ "   AND (MODULE = 'JDBC Thin Client')  "
			+ "   AND PARSING_SCHEMA_NAME NOT LIKE 'SYS%'   "
			+ " ORDER BY                                "
			+ "   \"a_time(msec)\" desc                   ";

	String SELECT_GET_DDL = "SELECT dbms_metadata.get_ddl(?,?,?) TEXT from dual";

	String SET_EXPLAIN_PLAN_HEAD = "EXPLAIN PLAN SET STATEMENT_ID=\'";

	String SET_EXPLAIN_PLAN_TAIL = "\' INTO PLAN_TABLE FOR ";

	String SELECT_EXPLAIN_PLAN = "SELECT LPAD(' ',2*(LEVEL-1))||OPERATION OPERATION,OPTIONS,OBJECT_NAME,POSITION,COST,BYTES,OPTIMIZER,CARDINALITY FROM PLAN_TABLE START WITH ID=0 AND STATEMENT_ID=? CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID=?";

	String DELETE_EXPLAIN_PLAN = "DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=?";

	String SELECT_INDEX_INFO = "SELECT DISTINCT I.TABLE_OWNER,C.INDEX_NAME, C.TABLE_NAME, C.COLUMN_NAME "
			+ " FROM USER_IND_COLUMNS C, USER_INDEXES I"
			+ " WHERE C.INDEX_NAME=I.INDEX_NAME AND C.TABLE_NAME=I.TABLE_NAME AND I.UNIQUENESS = 'NONUNIQUE'"
			+ " ORDER BY 1,2,3,4";

	String SELECT_SESSION_INFO = "SELECT A.SID,A.SERIAL#,A.USER#,A.USERNAME,'('||A.COMMAND||') '||B.NAME AS COMMAND_NAME,A.LOCKWAIT,A.STATUS,A.SCHEMANAME,A.OSUSER,A.PROCESS,A.MACHINE,A.TERMINAL,A.PROGRAM,A.TYPE,A.ACTION,TO_CHAR(A.LOGON_TIME,'MM/DD HH24:MI') LOGON_TIME,A.CLIENT_INFO "
			+ " FROM V$SESSION A,AUDIT_ACTIONS B WHERE A.COMMAND = B.ACTION";

	String SELECT_ALL_TAB_PRIVS = "SELECT COUNT(*) AS CNT FROM ALL_TAB_PRIVS WHERE TABLE_NAME =?";

	String SELECT_DICTIONARY = "SELECT"
			+ "  MAIN.TABLE_NAME TN,MAIN.COMMENTS TC,"
			+ "  SLAVE .COLUMN_NAME CN,SLAVE .COMMENTS CC"
			+ " FROM"
			+ "  DICTIONARY MAIN,DICT_COLUMNS SLAVE "
			+ " WHERE"
			+ "  MAIN.TABLE_NAME = SLAVE.TABLE_NAME"
			+ " AND"
			+ "  (MAIN.TABLE_NAME LIKE 'USER_%' OR MAIN.TABLE_NAME LIKE 'ALL_%'"
			+ "  OR  MAIN.TABLE_NAME LIKE 'DBA_%' OR MAIN.TABLE_NAME LIKE 'V$_%')"
			+ " ORDER BY TN,CN";

	String SELECT_V_SQLAREA = "SELECT SQL_TEXT, SORTS, EXECUTIONS, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, PARSE_CALLS, ROWS_PROCESSED, OPTIMIZER_MODE, PARSING_USER_ID, PARSING_SCHEMA_ID, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT NOT LIKE 'SELECT SQL_TEXT, SORTS, EXECUTIONS, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, PARSE_CALLS, ROWS_PROCESSED, OPTIMIZER_MODE, PARSING_USER_ID, PARSING_SCHEMA_ID, HASH_VALUE FROM V$SQLAREA%' AND FIRST_LOAD_TIME > TO_CHAR(sysdate -  ";

	String SELECT_LOCK_INFO = "SELECT "
			+ "     A.SID, C.USERNAME, "
			+ "     C.OSUSER, "
			+ "     DECODE(A.TYPE, 'TM', B1.NAME, '') as Object, "
			+ "     DECODE(A.TYPE, 'TM', B2.NAME, '') as Owner, "
			+ "     DECODE(A.TYPE, 'TX', 'ROW', 'TM', 'Table', A.TYPE) as LockType, "
			+ "     A.LMODE,  A.REQUEST , "
			+ "     A.CTIME as \"ElapsedTime(sec)\" "
			+ " FROM "
			+ "     V$LOCK A "
			+ "     LEFT JOIN SYS.OBJ$ B1 ON (A.ID1 = B1.OBJ#) "
			+ "     LEFT JOIN SYS.USER$ B2 ON (B1.OWNER# = B2.USER#) "
			+ "     INNER JOIN V$SESSION C ON (A.SID = C.SID AND C.TYPE = 'USER') ";

	String[] COMMAND_TYPE = { "CREATE TABLE", "INSERT", "SELECT",
			"CREATE CLUSTER", "ALTER CLUSTER", "UPDATE", "DELETE",
			"DROP CLUSTER", "CREATE INDEX", "DROP INDEX", "ALTER INDEX",
			"DROP TABLE", "CREATE SEQUENCE", "ALTER SEQUENCE", "ALTER TABLE",
			"DROP SEQUENCE", "GRANT", "REVOKE", "CREATE SYNONYM",
			"DROP SYNONYM", "CREATE VIEW", "DROP VIEW", "VALIDATE INDEX",
			"CREATE PROCEDURE", "ALTER PROCEDURE", "LOCK TABLE",
			"NO OPERATION", "RENAME", "COMMENT", "AUDIT", "NOAUDIT",
			"CREATE DATABASE LINK", "DROP DATABASE LINK", "CREATE DATABASE",
			"ALTER DATABASE", "CREATE ROLLBACK SEGMENT",
			"ALTER ROLLBACK SEGMENT", "DROP ROLLBACK SEGMENT",
			"CREATE TABLESPACE", "ALTER TABLESPACE", "DROP TABLESPACE",
			"ALTER SESSION", "ALTER USE", "COMMIT", "ROLLBACK", "SAVEPOINT",
			"PL/SQL EXECUTE", "SET TRANSACTION", "ALTER SYSTEM SWITCH LOG",
			"EXPLAIN", "CREATE USER", "CREATE ROLE", "DROP USER", "DROP ROLE",
			"SET ROLE", "CREATE SCHEMA", "CREATE CONTROL FILE",
			"ALTER TRACING", "CREATE TRIGGER", "ALTER TRIGGER", "DROP TRIGGER",
			"ANALYZE TABLE", "ANALYZE INDEX", "ANALYZE CLUSTER",
			"CREATE PROFILE", "DROP PROFILE", "ALTER PROFILE",
			"DROP PROCEDURE", "DROP PROCEDURE", "ALTER RESOURCE COST",
			"CREATE SNAPSHOT LOG", "ALTER SNAPSHOT LOG", "DROP SNAPSHOT LOG",
			"CREATE SNAPSHOT", "ALTER SNAPSHOT", "DROP SNAPSHOT",
			"ALTER SESSION", "TRUNCATE TABLE", "TRUNCATE COUSTER",
			"ALTER VIEW", "CREATE FUNCTION", "ALTER FUNCTION", "DROP FUNCTION",
			"CREATE PACKAGE", "ALTER PACKAGE", "DROP PACKAGE",
			"CREATE PACKAGE BODY", "ALTER PACKAGE BODY", "DROP PACKAGE BODY", };
	String _SELECT_SCHEMA_CNT = "SELECT COUNT(*) AS \"CNT\" FROM ALL_USERS WHERE USERNAME = ?";
	String _SELECT_TABLE_CNT = "SELECT COUNT(*) AS \"CNT\" FROM ALL_TABLES WHERE OWNER=? AND TABLE_NAME=?";
	String _SELECT_USER_TABLE_CNT = "SELECT COUNT(*) AS \"CNT\" FROM USER_TABLES WHERE TABLE_NAME=?";
	String _SELECT_ALL_SEQUENCE = "SELECT SEQUENCE_NAME AS \"NAME\" FROM ALL_SEQUENCES WHERE sequence_owner = ?";
	String _SELECT_SEQUENCE_CNT = "SELECT COUNT(*) AS \"CNT\" FROM ALL_SEQUENCES WHERE sequence_owner = ? AND sequence_name = ?";
	String _SELECT_USER_SEQUENCE_CNT = "SELECT COUNT(*) AS \"CNT\" FROM USER_SEQUENCES WHERE sequence_name = ?";
	String _SELECT_VIEW_CNT = "SELECT COUNT(*) AS \"CNT\" FROM ALL_VIEWS WHERE OWNER = ? AND view_name = ? ";
	String _SELECT_USER_VIEW_CNT = "SELECT COUNT(*) AS \"CNT\" FROM USER_VIEWS WHERE view_name = ? ";

}