Changeset: e79bfbd0553e for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=e79bfbd0553e Modified Files: ChangeLog src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java tests/JDBC_API_Tester.java Branch: default Log Message:
Added support for escaped wildcards (\% en \_) in String arguments of DatabaseMetaData methods which return a ResultSet, such as getTables(), getColumns(), etc. When you do not want the characters % or _ to be interpreted as wildcards but as normal characters you can prefix them with a backslash (so \% and \_). Note: be sure all wildcards characters in the String argument are escaped else the search must still use a LIKE operator instead of an = comparison operator. This fixes: https://github.com/MonetDB/monetdb-java/issues/3 diffs (162 lines): diff --git a/ChangeLog b/ChangeLog --- a/ChangeLog +++ b/ChangeLog @@ -1,6 +1,16 @@ # ChangeLog file for monetdb-java # This file is updated with Maddlog +* Wed Feb 3 2021 Martin van Dinther <martin.van.dint...@monetdbsolutions.com> +- Added support for escaped wildcards (\% en \_) in String arguments of + DatabaseMetaData methods which return a ResultSet, such as getTables(), + getColumns(), etc. When you do not want the characters % or _ to be + interpreted as wildcards but as normal characters you can prefix them + with a backslash (so \% and \_). Note: be sure all wildcards characters + in the String argument are escaped else the search must still use a + LIKE operator instead of an = comparison operator. + This fixes: https://github.com/MonetDB/monetdb-java/issues/3 + * Thu Jan 28 2021 Martin van Dinther <martin.van.dint...@monetdbsolutions.com> - Corrected the ordering of the output of DatabaseMetaData methods getImportedKeys(), getExportedKeys() and getCrossReference(). In cases diff --git a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java --- a/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java +++ b/src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java @@ -4089,8 +4089,13 @@ public class MonetDatabaseMetaData } /** - * Returns a SQL match part string where depending on the input value we - * compose an exact match (use =) or match with wildcards (use LIKE) or IS NULL + * Returns a SQL match part string where depending on the input value we compose + * - an exact string match (using = comparator) or + * - a match with wildcards (using LIKE comparator) or + * - when input is missing (using IS NULL comparator) + * + * Note that wildcard characters can be escaped by prefix \ (a backslash) + * When ALL wildcard characters are escaped then we remove the \'s and compose an = comparator string match. * * @param in the string to match * @return the SQL match part string @@ -4099,12 +4104,67 @@ public class MonetDatabaseMetaData if (in == null) return "IS NULL"; - String cmp = "= "; - // check if SQL wildcards are used in the input, if so use LIKE - if (in.contains("%") || in.contains("_")) - cmp = "LIKE "; - - return cmp + MonetWrapper.sq(in); + // Scan input string for SQL wildcard characters: % and _ + // When they are all prefixed by a backslash then the backslash is removed (to allow usage of = comparator) + // else it needs to be interpreted as a wildcard and we need to use LIKE instead of = comparator. + // A backslash can be escaped by using two backslashes. + final int len = in.length(); + final StringBuilder sb = new StringBuilder(len); + boolean removed_bs = false; + boolean use_like = false; + boolean escaped = false; + try { + // parse all characters in input to find if the wildcards are escaped by a \. + // note: the escape character \ can also be escaped, so \\. + for (int i = 0; i < len; i++) { + char c = in.charAt(i); + switch(c) { + case '\\': + if (escaped) { + // copy the 2 backslash characters as in the original string + sb.append(c).append(c); + escaped = false; + } else { + escaped = true; + } + break; + case '%': + case '_': + if (escaped) { + removed_bs = true; + escaped = false; + } else { + use_like = true; + i = len; /* no need to scan further */ + } + sb.append(c); + break; + default: + if (escaped) { + sb.append('\\'); // also copy the preceding escape found before this character + escaped = false; + } + sb.append(c); + break; + } + } + } catch (IndexOutOfBoundsException iob) { + /* ignore */ + } + + if (use_like) { + // for debug: System.out.println("input: " + in + " changed into: " + "LIKE " + MonetWrapper.sq(in)); + // we found a non-escaped wildcard character, use like and the original input + return "LIKE " + MonetWrapper.sq(in); + } + if (removed_bs) { + // for debug: System.out.println("input: " + in + " changed into: " + "= " + MonetWrapper.sq(sb.toString())); + // we found only escaped wildcard character(s), + // use the edited string without the ecapes before the wildcard character(s) so an equals match can be done (its is faster than LIKE) + return "= " + MonetWrapper.sq(sb.toString()); + } + // for debug: System.out.println("input: " + in + " changed into: " + "= " + MonetWrapper.sq(in)); + return "= " + MonetWrapper.sq(in); } //== end helper methods diff --git a/tests/JDBC_API_Tester.java b/tests/JDBC_API_Tester.java --- a/tests/JDBC_API_Tester.java +++ b/tests/JDBC_API_Tester.java @@ -677,22 +677,26 @@ final public class JDBC_API_Tester { "TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS TYPE_CAT TYPE_SCHEM TYPE_NAME SELF_REFERENCING_COL_NAME REF_GENERATION\n" + "null sys schemas SYSTEM TABLE null null null null null null\n"); - compareResultSet(dbmd.getColumns(null, "sys", "table_types", null), "getColumns(null, sys, table_types, null)", + compareResultSet(dbmd.getColumns(null, "sys", "table\\_types", null), "getColumns(null, sys, table\\_types, null)", "Resultset with 24 columns\n" + "TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SCOPE_CATALOG SCOPE_SCHEMA SCOPE_TABLE SOURCE_DATA_TYPE IS_AUTOINCREMENT IS_GENERATEDCOLUMN\n" + "null sys table_types table_type_id 5 smallint 16 0 0 2 0 null null 0 0 null 1 NO null null null null NO NO\n" + "null sys table_types table_type_name 12 varchar 25 0 0 0 0 null null 0 0 25 2 NO null null null null NO NO\n"); - compareResultSet(dbmd.getPrimaryKeys(null, "sys", "table_types"), "getPrimaryKeys(null, sys, table_types)", + compareResultSet(dbmd.getPrimaryKeys(null, "sys", "table\\_types"), "getPrimaryKeys(null, sys, table\\_types)", "Resultset with 6 columns\n" + "TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME\n" + "null sys table_types table_type_id 1 table_types_table_type_id_pkey\n"); - compareResultSet(dbmd.getCrossReference(null, "sys", "tables", null, "sys", "table_types"), "getCrossReference(null, sys, tables, null, sys, table_types)", + compareResultSet(dbmd.getExportedKeys(null, "sys", "table\\_types"), "getExportedKeys(null, sys, table\\_types)", "Resultset with 14 columns\n" + "PKTABLE_CAT PKTABLE_SCHEM PKTABLE_NAME PKCOLUMN_NAME FKTABLE_CAT FKTABLE_SCHEM FKTABLE_NAME FKCOLUMN_NAME KEY_SEQ UPDATE_RULE DELETE_RULE FK_NAME PK_NAME DEFERRABILITY\n"); - compareResultSet(dbmd.getImportedKeys(null, "sys", "table_types"), "getImportedKeys(null, sys, table_types)", + compareResultSet(dbmd.getCrossReference(null, "sys", "tables", null, "sys", "table\\_types"), "getCrossReference(null, sys, tables, null, sys, table\\_types)", + "Resultset with 14 columns\n" + + "PKTABLE_CAT PKTABLE_SCHEM PKTABLE_NAME PKCOLUMN_NAME FKTABLE_CAT FKTABLE_SCHEM FKTABLE_NAME FKCOLUMN_NAME KEY_SEQ UPDATE_RULE DELETE_RULE FK_NAME PK_NAME DEFERRABILITY\n"); + + compareResultSet(dbmd.getImportedKeys(null, "sys", "table\\_types"), "getImportedKeys(null, sys, table\\_types)", "Resultset with 14 columns\n" + "PKTABLE_CAT PKTABLE_SCHEM PKTABLE_NAME PKCOLUMN_NAME FKTABLE_CAT FKTABLE_SCHEM FKTABLE_NAME FKCOLUMN_NAME KEY_SEQ UPDATE_RULE DELETE_RULE FK_NAME PK_NAME DEFERRABILITY\n"); @@ -702,12 +706,12 @@ final public class JDBC_API_Tester { "null sys key_types false null key_types_key_type_id_pkey 2 1 key_type_id null 3 0 null\n" + "null sys key_types false null key_types_key_type_name_unique 2 1 key_type_name null 3 0 null\n"); - compareResultSet(dbmd.getTablePrivileges(null, "sys", "table_types"), "getTablePrivileges(null, sys, table_types)", + compareResultSet(dbmd.getTablePrivileges(null, "sys", "table\\_types"), "getTablePrivileges(null, sys, table\\_types)", "Resultset with 7 columns\n" + "TABLE_CAT TABLE_SCHEM TABLE_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE\n" + "null sys table_types monetdb public SELECT NO\n"); - compareResultSet(dbmd.getColumnPrivileges(null, "sys", "table_types", null), "getColumnPrivileges(null, sys, table_types, null)", + compareResultSet(dbmd.getColumnPrivileges(null, "sys", "table\\_types", null), "getColumnPrivileges(null, sys, table\\_types, null)", "Resultset with 8 columns\n" + "TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE\n"); _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
