Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @ 882:cd6e5449fb1a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved DatabaseMetaData.getTypeInfo(). It now also returns the serial and bigserial data types and all 13 possible interval data types.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 07 Mar 2024 19:55:44 +0100 (14 months ago)
parents 0e304689c415
children be8476c1acec
comparison
equal deleted inserted replaced
881:4973317db1cc 882:cd6e5449fb1a
3055 * @return ResultSet each row is a SQL type description 3055 * @return ResultSet each row is a SQL type description
3056 * @throws SQLException if a database error occurs 3056 * @throws SQLException if a database error occurs
3057 */ 3057 */
3058 @Override 3058 @Override
3059 public ResultSet getTypeInfo() throws SQLException { 3059 public ResultSet getTypeInfo() throws SQLException {
3060 final StringBuilder query = new StringBuilder(3200); 3060 final StringBuilder query = new StringBuilder(4816);
3061 query.append("SELECT CASE \"sqlname\" WHEN 'day_interval' THEN 'interval day'" + 3061 query.append("SELECT \"sqlname\" AS \"TYPE_NAME\", " +
3062 " WHEN 'month_interval' THEN 'interval month'" +
3063 " WHEN 'sec_interval' THEN 'interval second'" +
3064 " ELSE \"sqlname\" END AS \"TYPE_NAME\", " +
3065 "cast(").append(MonetDriver.getSQLTypeMap("\"sqlname\"")).append(" AS int) AS \"DATA_TYPE\", " + 3062 "cast(").append(MonetDriver.getSQLTypeMap("\"sqlname\"")).append(" AS int) AS \"DATA_TYPE\", " +
3066 "cast(CASE WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz') THEN \"digits\" -1" + 3063 "cast(CASE WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz') THEN \"digits\" -1" +
3067 " WHEN \"sqlname\" IN ('sec_interval','day_interval') THEN 3" + 3064 " ELSE \"digits\" END AS int) AS \"PRECISION\", " + // note that when radix is 2 the precision shows the number of bits
3068 " WHEN \"sqlname\" = 'month_interval' THEN 0 ELSE \"digits\" END AS int) AS \"PRECISION\", " + // note that when radix is 2 the precision shows the number of bits 3065 "cast(CASE WHEN \"sqlname\" IN ('char','varchar') THEN ''''" +
3069 "cast(CASE WHEN \"sqlname\" IN ('char','varchar','sec_interval','day_interval','month_interval') THEN ''''" +
3070 " WHEN \"sqlname\" IN ('clob','inet','json','url','uuid','date','time','timetz','timestamp','timestamptz','blob','sqlblob','xml') THEN \"sqlname\"||' '''" + 3066 " WHEN \"sqlname\" IN ('clob','inet','json','url','uuid','date','time','timetz','timestamp','timestamptz','blob','sqlblob','xml') THEN \"sqlname\"||' '''" +
3071 " ELSE NULL END AS varchar(16)) AS \"LITERAL_PREFIX\", " + 3067 " ELSE NULL END AS varchar(16)) AS \"LITERAL_PREFIX\", " +
3072 "cast(CASE WHEN \"sqlname\" IN ('char','varchar','sec_interval','day_interval','month_interval'" + 3068 "cast(CASE WHEN \"sqlname\" IN ('char','varchar','clob','inet','json','url','uuid','date','time','timetz','timestamp','timestamptz','blob','sqlblob','xml') THEN ''''" +
3073 ",'clob','inet','json','url','uuid','date','time','timetz','timestamp','timestamptz','blob','sqlblob','xml') THEN ''''" +
3074 " ELSE NULL END AS varchar(2)) AS \"LITERAL_SUFFIX\", " + 3069 " ELSE NULL END AS varchar(2)) AS \"LITERAL_SUFFIX\", " +
3075 "CASE WHEN \"sqlname\" IN ('char','varchar') THEN 'max length'" + 3070 "CASE WHEN \"sqlname\" IN ('char','varchar') THEN 'max length'" +
3076 " WHEN \"sqlname\" = 'decimal' THEN 'precision, scale'" + 3071 " WHEN \"sqlname\" = 'decimal' THEN 'precision, scale'" +
3077 " WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz') THEN 'precision'" + 3072 " WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz') THEN 'scale'" +
3078 " ELSE NULL END AS \"CREATE_PARAMS\", " + 3073 " ELSE NULL END AS \"CREATE_PARAMS\", " +
3079 "cast(CASE WHEN \"systemname\" = 'oid' THEN " + DatabaseMetaData.typeNoNulls + 3074 "cast(CASE WHEN \"systemname\" = 'oid' THEN " + DatabaseMetaData.typeNoNulls +
3080 " ELSE " + DatabaseMetaData.typeNullable + " END AS smallint) AS \"NULLABLE\", " + 3075 " ELSE " + DatabaseMetaData.typeNullable + " END AS smallint) AS \"NULLABLE\", " +
3081 "CASE WHEN \"systemname\" IN ('str','json','url','xml') THEN true ELSE false END AS \"CASE_SENSITIVE\", " + 3076 "CASE WHEN \"systemname\" IN ('str','json','url','xml') THEN true ELSE false END AS \"CASE_SENSITIVE\", " +
3082 "cast(CASE WHEN \"systemname\" IN ('str','inet','json','url','uuid','xml') THEN " + DatabaseMetaData.typeSearchable + 3077 "cast(CASE WHEN \"systemname\" IN ('str','inet','json','url','uuid','xml') THEN " + DatabaseMetaData.typeSearchable +
3083 " ELSE " + DatabaseMetaData.typePredBasic + " END AS smallint) AS \"SEARCHABLE\", " + 3078 " ELSE " + DatabaseMetaData.typePredBasic + " END AS smallint) AS \"SEARCHABLE\", " +
3084 "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint','hugeint','decimal','real','double'" + 3079 "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint','hugeint','decimal','real','double') THEN false ELSE true END AS \"UNSIGNED_ATTRIBUTE\", " +
3085 ",'day_interval','month_interval','sec_interval') THEN false ELSE true END AS \"UNSIGNED_ATTRIBUTE\", " +
3086 "CASE \"sqlname\" WHEN 'decimal' THEN true ELSE false END AS \"FIXED_PREC_SCALE\", " + 3080 "CASE \"sqlname\" WHEN 'decimal' THEN true ELSE false END AS \"FIXED_PREC_SCALE\", " +
3087 "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint') THEN true ELSE false END AS \"AUTO_INCREMENT\", " + 3081 "CASE WHEN \"sqlname\" IN ('tinyint','smallint','int','bigint') THEN true ELSE false END AS \"AUTO_INCREMENT\", " +
3088 "CASE WHEN \"sqlname\" IN ('sec_interval','day_interval','month_interval') THEN \"sqlname\" ELSE \"systemname\" END AS \"LOCAL_TYPE_NAME\", " + 3082 "\"systemname\" AS \"LOCAL_TYPE_NAME\", " +
3089 "cast(0 AS smallint) AS \"MINIMUM_SCALE\", " + 3083 "cast(0 AS smallint) AS \"MINIMUM_SCALE\", " +
3090 "cast(CASE WHEN \"sqlname\" = 'decimal' THEN (CASE \"systemname\" WHEN 'int' THEN 9 WHEN 'lng' THEN 18 WHEN 'sht' THEN 4 WHEN 'hge' THEN 38 WHEN 'bte' THEN 2 ELSE 0 END)" + 3084 "cast(CASE WHEN \"sqlname\" = 'decimal' THEN (CASE \"systemname\" WHEN 'int' THEN 9 WHEN 'lng' THEN 18 WHEN 'sht' THEN 4 WHEN 'hge' THEN 38 WHEN 'bte' THEN 2 ELSE 0 END)" +
3091 " WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz') THEN 6" + 3085 " WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz') THEN 6" +
3092 " WHEN \"sqlname\" IN ('day_interval','sec_interval') THEN 3 ELSE 0 END AS smallint) AS \"MAXIMUM_SCALE\", " + 3086 " ELSE 0 END AS smallint) AS \"MAXIMUM_SCALE\", " +
3093 "cast(0 AS int) AS \"SQL_DATA_TYPE\", " + 3087 "cast(0 AS int) AS \"SQL_DATA_TYPE\", " +
3094 "cast(0 AS int) AS \"SQL_DATETIME_SUB\", " + 3088 "cast(0 AS int) AS \"SQL_DATETIME_SUB\", " +
3095 "cast(CASE WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz','sec_interval') THEN 10 ELSE \"radix\" END AS int) AS \"NUM_PREC_RADIX\" " + 3089 "cast(CASE WHEN \"sqlname\" IN ('time','timetz','timestamp','timestamptz') THEN 10 ELSE \"radix\" END AS int) AS \"NUM_PREC_RADIX\" " +
3096 "FROM \"sys\".\"types\" " + 3090 "FROM \"sys\".\"types\" " +
3097 "ORDER BY \"DATA_TYPE\", \"sqlname\", \"id\""); 3091 // exclude the 3 interval types here as they are added next
3098 3092 "WHERE \"sqlname\" NOT IN ('sec_interval','day_interval','month_interval') " +
3099 /* if (query.length() >= 3200) System.err.println("getTypeInfo(), extend query default size to: " + query.length()); */ 3093 "UNION ALL " +
3094 // add the 13 interval types (like done in ODBC SQLGetTypeInfo())
3095 "SELECT \"TYPE_NAME\", 1111 AS \"DATA_TYPE\", PRECISION, 'interval ''' AS LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS, " +
3096 DatabaseMetaData.typeNullable + " AS NULLABLE, false AS CASE_SENSITIVE, " + DatabaseMetaData.typePredBasic + " AS SEARCHABLE, " +
3097 "false AS UNSIGNED_ATTRIBUTE, false AS FIXED_PREC_SCALE, false AS \"AUTO_INCREMENT\", LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, " +
3098 "MAXIMUM_SCALE, 0 AS SQL_DATA_TYPE, 0 AS SQL_DATETIME_SUB, 10 AS NUM_PREC_RADIX " +
3099 "FROM (VALUES" +
3100 " ('interval year', 6, ''' year', CAST(NULL AS VARCHAR(5)), 'month_interval', 0)" +
3101 ",('interval month', 8, ''' month', NULL, 'month_interval', 0)" +
3102 ",('interval day', 10, ''' day', NULL, 'day_interval', 0)" +
3103 ",('interval hour', 12, ''' hour', NULL, 'sec_interval', 0)" +
3104 ",('interval minute', 12, ''' minute', NULL, 'sec_interval', 0)" +
3105 ",('interval second', 15, ''' second', 'scale', 'sec_interval', 3)" +
3106 ",('interval year to month', 8, ''' year to month', NULL, 'month_interval', 0)" +
3107 ",('interval day to hour', 12, ''' day to hour', NULL, 'sec_interval', 0)" +
3108 ",('interval day to minute', 12, ''' day to minute', NULL, 'sec_interval', 0)" +
3109 ",('interval day to second', 15, ''' day to second', 'scale', 'sec_interval', 3)" +
3110 ",('interval hour to minute', 12, ''' hour to minute', NULL, 'sec_interval', 0)" +
3111 ",('interval hour to second', 15, ''' hour to second', 'scale', 'sec_interval', 3)" +
3112 ",('interval minute to second', 15, ''' minute to second', 'scale', 'sec_interval', 3)" +
3113 ") AS interval_types(\"TYPE_NAME\", PRECISION, LITERAL_SUFFIX, CREATE_PARAMS, LOCAL_TYPE_NAME, MAXIMUM_SCALE) " +
3114 "UNION ALL " +
3115 // also add the 2 serial types (like done in ODBC SQLGetTypeInfo())
3116 "SELECT \"TYPE_NAME\", \"DATA_TYPE\", PRECISION, NULL AS LITERAL_PREFIX, NULL AS LITERAL_SUFFIX, NULL AS CREATE_PARAMS, " +
3117 DatabaseMetaData.typeNoNulls + " AS NULLABLE, false AS CASE_SENSITIVE, " + DatabaseMetaData.typePredBasic + " AS SEARCHABLE, " +
3118 "false AS UNSIGNED_ATTRIBUTE, false AS FIXED_PREC_SCALE, true AS \"AUTO_INCREMENT\", LOCAL_TYPE_NAME, 0 AS MINIMUM_SCALE, " +
3119 "0 AS MAXIMUM_SCALE, 0 AS SQL_DATA_TYPE, 0 AS SQL_DATETIME_SUB, 2 AS NUM_PREC_RADIX " +
3120 "FROM (VALUES" +
3121 " ('bigserial', -5, 64, 'bigint')" +
3122 ",('serial', 4, 32, 'int')" +
3123 ") AS serial_types(\"TYPE_NAME\", \"DATA_TYPE\", PRECISION, LOCAL_TYPE_NAME) " +
3124 "ORDER BY \"DATA_TYPE\", \"TYPE_NAME\"");
3125
3126 // System.err.println("getTypeInfo() query: " + query.toString());
3127 // if (query.length() >= 4800) System.err.println("getTypeInfo(), extend query initial size to: " + query.length());
3100 return executeMetaDataQuery(query.toString()); 3128 return executeMetaDataQuery(query.toString());
3101 } 3129 }
3102 3130
3103 /** 3131 /**
3104 * Retrieves a description of the given table's indices and statistics. 3132 * Retrieves a description of the given table's indices and statistics.