package hiro.yoshioka.sql;

public interface IPostgresDBConst {
	String JDBC_URL_EXAMPLE = "jdbc:postgresql://localhost/database";

	String _SELECT_TAB_COMMENTS = "select "
			+ "	  ns.nspname AS OWNER "
			+ "	  ,a.relname AS TABLE_NAME "
			+ "	  ,des.description AS COMMENTS "
			+ "	from "
			+ "	  pg_catalog.pg_class a  "
			+ "	  inner join pg_catalog.pg_namespace ns on (a.relnamespace = ns.oid) "
			+ "	  left  join pg_catalog.pg_description des on (a.oid=des.objoid and des.objsubid=0) "
			+ "	where " + "	    a.relkind IN ('r', 'v') "
			+ "	and ns.nspname =? " + "	order by ns.oid ,a.oid ";

	String _SELECT_COL_OF_VIEW = " SELECT                                                                          "
			+ "   table_schema,    table_name,                                                  "
			+ "   column_name,     is_nullable,                                                 "
			+ "   data_type,       ordinal_position                                             "
			+ " FROM                                                                            "
			+ "   information_schema.columns  sc                                                "
			+ "   INNER JOIN pg_views pv ON ( pv.schemaname = sc.table_schema and pv.viewname= sc.table_name )"
			+ " WHERE                                                                           "
			+ "   UPPER(table_schema) = ?                                                       "
			+ "   AND UPPER(table_name) = ?                                                     "
			+ " ORDER BY                                                                        "
			+ "   table_name,      ordinal_position                                             ";

	String _SELECT_TAB_COL_COMMENTS = "SELECT "
			+ "	sut.schemaname AS OWNER, sut.relname AS TABLE_NAME, "
			+ "    att.attname AS COLUMN_NAME, "
			+ "    com.description AS COMMENTS "
			+ " FROM pg_attribute att "
			+ " INNER JOIN pg_stat_user_tables sut ON (att.attrelid = sut.relid) "
			+ " INNER JOIN pg_description com ON ( att.attrelid = com.objoid AND att.attnum = com.objsubid) "
			+ " WHERE att.attnum > 0 AND sut.schemaname=?  "
			+ " ORDER BY att.attnum";

	String _SELECT_TRIGGER = "select  "
			+ "   ns.nspname AS OWNER, "
			+ "   a.relname AS TABLE_NAME, "
			+ "   tr.tgname AS TRIGGER_NAME, "
			+ "   tr.tgfoid AS tgfoid, "
			+ "   tr.tgtype AS tgtype, "
			+ "   tr.tgenabled AS tgenabled, "
			+ "   tr.tgfoid AS tgconstrrelid, "
			+ "   tr.tgfoid AS tgconstrindid, "
			+ "   tr.tgfoid AS tgconstraint "
			+ " from "
			+ "   pg_catalog.pg_class a "
			+ "   inner join pg_catalog.pg_namespace ns on (a.relnamespace = ns.oid) "
			+ "   inner join pg_trigger tr on (a.oid=tr.tgrelid ) " + " where "
			+ "   a.relkind IN ('r') and ns.nspname=?";

	String _SELECT_SCHEMA_CNT = "SELECT COUNT(*) AS \"CNT\" FROM information_schema.schemata WHERE schema_name ILIKE ?";
	String _SELECT_TABLE_CNT = "SELECT COUNT(*) AS \"CNT\" FROM information_schema.tables WHERE table_schema ILIKE ? AND table_name ILIKE ?";
	String _SELECT_ALL_SEQUENCE = "SELECT SEQUENCE_NAME AS \"NAME\" FROM information_schema.sequences WHERE sequence_schema ILIKE ?";
	String _SELECT_SEQUENCE_CNT = "SELECT COUNT(*) AS \"CNT\" FROM information_schema.sequences WHERE sequence_schema ILIKE ? AND sequence_name ILIKE ?";
	String _SELECT_SEQUENCE_BODY = "SELECT sequence_name AS \"SEQUENCE_NAME\", "
			+ "last_value AS \"LAST_NUMBER\", "
			+ "max_value AS \"MAX_VALUE\", "
			+ "min_value AS \"MIN_VALUE\", "
			+ "increment_by AS \"INCREMENT_BY\", "
			+ "cache_value AS \"CASHE_SIZE\", "
			+ "is_cycled AS \"CYCLE_FLAG\" " + " FROM ";
	String _SELECT_VIEW_CNT = "SELECT COUNT(*) AS \"CNT\" FROM information_schema.views  "
			+ "WHERE table_schema ILIKE ? AND table_name ILIKE ? ";
	String _SELECT_VIEW_TEXT = "SELECT view_definition AS \"TEXT\" FROM information_schema.views "
			+ "WHERE table_schema ILIKE ? AND table_name ILIKE ? ";
	String _SELECT_SESSION = "select * from pg_stat_activity ";
	String _SELECT_INDEX_CNT = "SELECT COUNT(*) AS \"CNT\" "
			+ "FROM information_schema.referential_constraints  "
			+ "WHERE constraint_schema ILIKE ? AND constraint_name ILIKE ? ";

	String _SELECT_FUNCTION_TEXT = "SELECT pg_get_functiondef(p.oid) as TEXT "
			+ " FROM pg_proc p join pg_namespace ns on (p.pronamespace = ns.oid) "
			+ " WHERE ns.nspname ilike ? and proname ilike ?";

	// -----------------------------------------------------------------------
	// DIFF
	// -----------------------------------------------------------------------
	String _SELECT_DIFF_TABLE = " select                                                                          "
			+ "    a.relname        as table_name,                                              "
			+ "    des.description  as table_comment,                                           "
			+ "    attr.attname     as column_name,                                             "
			+ "    CASE substr(type.typname,1,1)                                                 "
			+ "      WHEN '_' THEN substr(type.typname,2)                                        "
			+ "      ELSE type.typname                                                           "
			+ "    END AS column_type,                                                           "
			+ "    CASE type.typname                                                             "
			+ "      WHEN '_bpchar' THEN attr.atttypmod - 4                                       "
			+ "      WHEN '_varchar' THEN attr.atttypmod - 4                                      "
			+ "      WHEN '_numeric' THEN (attr.atttypmod - 4) / 65536                            "
			+ "      WHEN '_date'      THEN attr.attlen                                           "
			+ "      WHEN '_timestamp' THEN attr.attlen                                           "
			+ "      ELSE attr.attlen                                                             "
			+ "    END AS column_length,                                                         "
			+ "    CASE type.typname                                                             "
			+ "      WHEN '_numeric' THEN (attr.atttypmod - 4) % 65536 ELSE 0                     "
			+ "    END AS floatPart,                                                             "
			+ "    CASE attr.attnotnull                                                           "
			+ "          WHEN 't' THEN 'NN'                                                      "
			+ "          WHEN 'f' THEN NULL                                                      "
			+ "    END AS not_null,                                                              "
			+ "    CASE con_p.contype                                                            "
			+ "      WHEN 'p' THEN 'pri' ELSE NULL                                               "
			+ "    END AS pri_key,                                                               "
			+ "    CASE con_u.contype                                                            "
			+ "      WHEN 'u' THEN 'uni' ELSE NULL                                               "
			+ "    END AS uniq_key,                                                                "
			+ "    attr.attnum      as column_Number,                                            "
			+ "    des2.description as column_Comment                                            "
			+ " from                                                                            "
			+ "   pg_class a                                                                    "
			+ " inner join pg_namespace   ns   on a.relnamespace = ns.oid                       "
			+ " left  join pg_description des                                                   "
			+ "   on (a.oid = des.objoid and des.objsubid   = 0)                                "
			+ " inner join pg_attribute attr                                                    "
			+ "   on (a.oid = attr.attrelid and attr.attnum > 0 AND attr.atttypid!=0)      "
			+ " INNER JOIN pg_type type ON attr.atttypid = type.typelem                          "
			+ " LEFT JOIN pg_constraint con_u ON con_u.conkey[1] = attr.attnum                   "
			+ "    AND con_u.contype = 'u' AND con_u.conrelid = a.relfilenode                   "
			+ " LEFT JOIN pg_constraint con_p ON attr.attnum = ANY (con_p.conkey)                "
			+ "    AND con_p.contype = 'p' AND con_p.conrelid = a.relfilenode                   "
			+ " left  join pg_description des2                                                  "
			+ "   on (a.oid = des2.objoid and attr.attnum    = des2.objsubid)                   "
			+ " where                                                                           "
			+ "     a.relkind IN ('r' )                                                         "
			+ " and ns.nspname ILIKE ?                                                          "
			+ " order by                                                                        "
			+ "     1,3                                                                 ";

	String _SELECT_DIFF_VIEW = " SELECT  "
			// +
			// "   viewowner,       table_schema,                                                "
			+ "   table_name,      column_name,                                                 "
			+ "   data_type,       ordinal_position,                                            "
			+ "   CASE WHEN ordinal_position = 1 THEN definition ELSE NULL END AS definition    "
			+ " FROM                                                                            "
			+ "   information_schema.columns  sc                                                "
			+ "   INNER JOIN pg_views pv ON ( pv.schemaname = sc.table_schema AND               "
			+ "   pv.viewname= sc.table_name )                                                  "
			+ " WHERE                                                                           "
			+ "   UPPER(table_schema) = UPPER(?)                                         "
			+ " ORDER BY                                                                        "
			+ "   table_name,      ordinal_position                                             ";

	String[] _SELECT_DIFF_A_SEQUENCE_INFO_COLUMNS = new String[] {
			"sequence_name", "start_value", "increment_by", "max_value",
			"min_value", "cache_value", "is_cycled" };

	String _SELECT_DIFF_A_SEQUENCE_INFO = " SELECT sequence_name, start_value, increment_by,max_value,    "
			+ "   min_value,   cache_value, is_cycled  FROM %s ";

	String _SELECT_DIFF_FNCTIONS = " SELECT                                                                          "
			+ "   p.proname, oidvectortypes(p.proargtypes) as types, proargnames, prosrc        "
			+ " FROM pg_proc p                                                                  "
			+ "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace               "
			+ " WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype                  "
			+ "      AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype           "
			+ "      AND NOT p.proisagg                                                         "
			+ "      and nspname ilike ?                                                        "
			+ " ORDER BY 1,2                                                                    ";

	String _MAX_CONNECTION_INFO = " SELECT setting as max_connections "
			+ " FROM pg_settings  " + " WHERE name='max_connections'  ";

}
