Changeset: 0b3ac566e5d9 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0b3ac566e5d9
Modified Files:
java/ChangeLog.Jun2016
java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
Branch: Jun2016
Log Message:
Corrected DatabaseMetaData methods which accept a catalog filter argument.
Those methods will now filter the results on the specified catalog name,
whereas previously the catalog filter argument was ignored.
Corrected output of column KEY_SEQ of DatabaseMetaData methods:
getPrimaryKeys(), getImportedKeys(), getExportedKeys() and
getCrossReference(). It now starts at 1 instead of 0 previously.
Use StringBuilder as much as possible when composing sql query strings.
diffs (truncated from 777 to 300 lines):
diff --git a/java/ChangeLog.Jun2016 b/java/ChangeLog.Jun2016
--- a/java/ChangeLog.Jun2016
+++ b/java/ChangeLog.Jun2016
@@ -1,6 +1,14 @@
# ChangeLog file for java
# This file is updated with Maddlog
+* Thu Mar 31 2016 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
+- Corrected DatabaseMetaData methods which accept a catalog filter argument.
+ Those methods will now filter the results on the specified catalog name,
+ whereas previously the catalog filter argument was ignored.
+- Corrected output of column KEY_SEQ of DatabaseMetaData methods:
+ getPrimaryKeys(), getImportedKeys(), getExportedKeys() and
+ getCrossReference(). It now starts at 1 instead of 0 previously.
+
* Thu Mar 24 2016 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
- Corrected DatabaseMetaData.getSchemas() by returning 2 instead of 3 columns.
- Improved DatabaseMetaData.getColumns() by returning two additional
diff --git a/java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
b/java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
--- a/java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
+++ b/java/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
@@ -1741,20 +1741,10 @@ public class MonetDatabaseMetaData exten
return executeMetaDataQuery(query.toString());
}
+
//== this is a helper method which does not belong to the interface
/**
- * Returns the given string where all slashes and single quotes are
- * escaped with a slash.
- *
- * @param in the string to escape
- * @return the escaped string
- */
- private static final String escapeQuotes(String in) {
- return in.replaceAll("\\\\", "\\\\\\\\").replaceAll("'",
"\\\\'");
- }
-
- /**
* Returns a SQL match part string where depending on the input value we
* compose an exact match (use =) or match with wildcards (use LIKE)
*
@@ -1765,13 +1755,15 @@ public class MonetDatabaseMetaData exten
if (in == null)
return "IS NULL";
- String sql;
+ String sql = "= '";
// check if SQL wildcards are used in the input, if so use LIKE
if (in.contains("%") || in.contains("_"))
- sql = "LIKE '" + escapeQuotes(in) + "'";
- else
- sql = "= '" + escapeQuotes(in) + "'";
- return sql;
+ sql = "LIKE '";
+
+ // all slashes and single quotes in input are escaped with a
slash.
+ String escaped = in.replaceAll("\\\\",
"\\\\\\\\").replaceAll("'", "\\\\'");
+
+ return sql + escaped + "'";
}
/**
@@ -1781,13 +1773,14 @@ public class MonetDatabaseMetaData exten
* @param in the string to quote
* @return the quoted string
*/
- @SuppressWarnings("unused")
- private static final String dq(String in) {
- return "\"" + in.replaceAll("\\\\",
"\\\\\\\\").replaceAll("\"", "\\\\\"") + "\"";
- }
+// @SuppressWarnings("unused")
+// private static final String dq(String in) {
+// return "\"" + in.replaceAll("\\\\",
"\\\\\\\\").replaceAll("\"", "\\\\\"") + "\"";
+// }
//== end helper methods
+
/**
* Get a description of tables available in a catalog.
*
@@ -2074,7 +2067,7 @@ public class MonetDatabaseMetaData exten
String columnNamePattern
) throws SQLException
{
- StringBuilder query = new StringBuilder(2350);
+ StringBuilder query = new StringBuilder(2450);
query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
"\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
"\"tables\".\"name\" AS \"TABLE_NAME\", " +
@@ -2165,50 +2158,57 @@ public class MonetDatabaseMetaData exten
String columnNamePattern
) throws SQLException
{
- String query =
- "SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
+ StringBuilder query = new StringBuilder(1100);
+ query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
"\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
"\"tables\".\"name\" AS \"TABLE_NAME\", " +
"\"columns\".\"name\" AS \"COLUMN_NAME\", " +
"\"grantors\".\"name\" AS \"GRANTOR\", " +
"\"grantees\".\"name\" AS \"GRANTEE\", " +
- "CASE \"privileges\".\"privileges\" " +
- "WHEN 1 THEN cast('SELECT' AS varchar(7)) " +
- "WHEN 2 THEN cast('UPDATE' AS varchar(7)) " +
- "WHEN 4 THEN cast('INSERT' AS varchar(7)) " +
- "WHEN 8 THEN cast('DELETE' AS varchar(7)) " +
- "WHEN 16 THEN cast('EXECUTE' AS varchar(7)) " +
- "WHEN 32 THEN cast('GRANT' AS varchar(7)) " +
- "END AS \"PRIVILEGE\", " +
- "CASE \"privileges\".\"grantable\" " +
- "WHEN 0 THEN cast('NO' AS varchar(3)) " +
- "WHEN 1 THEN cast('YES' AS varchar(3)) " +
- "END AS \"IS_GRANTABLE\" " +
- "FROM \"sys\".\"privileges\" AS \"privileges\", " +
- "\"sys\".\"tables\" AS \"tables\", " +
- "\"sys\".\"schemas\" AS \"schemas\", " +
- "\"sys\".\"columns\" AS \"columns\", " +
+ "CAST(CASE \"privileges\".\"privileges\" " +
+ "WHEN 1 THEN 'SELECT' " +
+ "WHEN 2 THEN 'UPDATE' " +
+ "WHEN 4 THEN 'INSERT' " +
+ "WHEN 8 THEN 'DELETE' " +
+ "WHEN 16 THEN 'EXECUTE' " +
+ "WHEN 32 THEN 'GRANT' " +
+ "ELSE NULL " +
+ "END AS varchar(7)) AS \"PRIVILEGE\", " +
+ "CAST(CASE \"privileges\".\"grantable\" " +
+ "WHEN 0 THEN 'NO' " +
+ "WHEN 1 THEN 'YES' " +
+ "ELSE NULL " +
+ "END AS varchar(3)) AS \"IS_GRANTABLE\" " +
+ "FROM \"sys\".\"privileges\", " +
+ "\"sys\".\"tables\", " +
+ "\"sys\".\"schemas\", " +
+ "\"sys\".\"columns\", " +
"\"sys\".\"auths\" AS \"grantors\", " +
"\"sys\".\"auths\" AS \"grantees\" " +
"WHERE \"privileges\".\"obj_id\" = \"columns\".\"id\" " +
"AND \"columns\".\"table_id\" = \"tables\".\"id\" " +
"AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
"AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" "
+
- "AND \"privileges\".\"grantor\" = \"grantors\".\"id\" ";
-
+ "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
+
+ if (catalog != null && catalog.length() > 0) {
+ // none empty catalog selection.
+ // as we do not support catalogs this always results in
no rows returned
+ query.append(" AND 1 = 0");
+ }
if (schemaPattern != null) {
- query += "AND \"schemas\".\"name\" ILIKE '" +
escapeQuotes(schemaPattern) + "' ";
+ query.append(" AND \"schemas\".\"name\"
").append(composeMatchPart(schemaPattern));
}
if (tableNamePattern != null) {
- query += "AND \"tables\".\"name\" ILIKE '" +
escapeQuotes(tableNamePattern) + "' ";
+ query.append(" AND \"tables\".\"name\"
").append(composeMatchPart(tableNamePattern));
}
if (columnNamePattern != null) {
- query += "AND \"columns\".\"name\" ILIKE '" +
escapeQuotes(columnNamePattern) + "' ";
+ query.append(" AND \"columns\".\"name\"
").append(composeMatchPart(columnNamePattern));
}
- query += "ORDER BY \"COLUMN_NAME\", \"PRIVILEGE\"";
-
- return executeMetaDataQuery(query);
+ query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\",
\"COLUMN_NAME\", \"PRIVILEGE\"");
+
+ return executeMetaDataQuery(query.toString());
}
/**
@@ -2247,44 +2247,51 @@ public class MonetDatabaseMetaData exten
String tableNamePattern
) throws SQLException
{
- String query =
- "SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
+ StringBuilder query = new StringBuilder(1000);
+ query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
"\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
"\"tables\".\"name\" AS \"TABLE_NAME\", " +
"\"grantors\".\"name\" AS \"GRANTOR\", " +
"\"grantees\".\"name\" AS \"GRANTEE\", " +
- "CASE \"privileges\".\"privileges\" " +
- "WHEN 1 THEN cast('SELECT' AS varchar(7)) " +
- "WHEN 2 THEN cast('UPDATE' AS varchar(7)) " +
- "WHEN 4 THEN cast('INSERT' AS varchar(7)) " +
- "WHEN 8 THEN cast('DELETE' AS varchar(7)) " +
- "WHEN 16 THEN cast('EXECUTE' AS varchar(7)) " +
- "WHEN 32 THEN cast('GRANT' AS varchar(7)) " +
- "END AS \"PRIVILEGE\", " +
- "CASE \"privileges\".\"grantable\" " +
- "WHEN 0 THEN cast('NO' AS varchar(3)) " +
- "WHEN 1 THEN cast('YES' AS varchar(3)) " +
- "END AS \"IS_GRANTABLE\" " +
- "FROM \"sys\".\"privileges\" AS \"privileges\", " +
- "\"sys\".\"tables\" AS \"tables\", " +
- "\"sys\".\"schemas\" AS \"schemas\", " +
+ "CAST(CASE \"privileges\".\"privileges\" " +
+ "WHEN 1 THEN 'SELECT' " +
+ "WHEN 2 THEN 'UPDATE' " +
+ "WHEN 4 THEN 'INSERT' " +
+ "WHEN 8 THEN 'DELETE' " +
+ "WHEN 16 THEN 'EXECUTE' " +
+ "WHEN 32 THEN 'GRANT' " +
+ "ELSE NULL " +
+ "END AS varchar(7)) AS \"PRIVILEGE\", " +
+ "CAST(CASE \"privileges\".\"grantable\" " +
+ "WHEN 0 THEN 'NO' " +
+ "WHEN 1 THEN 'YES' " +
+ "ELSE NULL " +
+ "END AS varchar(3)) AS \"IS_GRANTABLE\" " +
+ "FROM \"sys\".\"privileges\", " +
+ "\"sys\".\"tables\", " +
+ "\"sys\".\"schemas\", " +
"\"sys\".\"auths\" AS \"grantors\", " +
"\"sys\".\"auths\" AS \"grantees\" " +
"WHERE \"privileges\".\"obj_id\" = \"tables\".\"id\" " +
"AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
"AND \"privileges\".\"auth_id\" = \"grantees\".\"id\" "
+
- "AND \"privileges\".\"grantor\" = \"grantors\".\"id\" ";
-
+ "AND \"privileges\".\"grantor\" = \"grantors\".\"id\"");
+
+ if (catalog != null && catalog.length() > 0) {
+ // none empty catalog selection.
+ // as we do not support catalogs this always results in
no rows returned
+ query.append(" AND 1 = 0");
+ }
if (schemaPattern != null) {
- query += "AND \"schemas\".\"name\" ILIKE '" +
escapeQuotes(schemaPattern) + "' ";
+ query.append(" AND \"schemas\".\"name\"
").append(composeMatchPart(schemaPattern));
}
if (tableNamePattern != null) {
- query += "AND \"tables\".\"name\" ILIKE '" +
escapeQuotes(tableNamePattern) + "' ";
+ query.append(" AND \"tables\".\"name\"
").append(composeMatchPart(tableNamePattern));
}
- query += "ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\",
\"PRIVILEGE\"";
-
- return executeMetaDataQuery(query);
+ query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\",
\"PRIVILEGE\"");
+
+ return executeMetaDataQuery(query.toString());
}
/**
@@ -2331,37 +2338,48 @@ public class MonetDatabaseMetaData exten
boolean nullable
) throws SQLException
{
- String query = "SELECT " + DatabaseMetaData.bestRowSession + "
AS \"SCOPE\", " +
- "\"columns\".\"name\" AS \"COLUMN_NAME\", " +
- MonetDriver.getSQLTypeMap("\"columns\".\"type\"") + "
AS \"DATA_TYPE\", " +
+ StringBuilder query = new StringBuilder(1500);
+ query.append("SELECT
CAST(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS
\"SCOPE\", " +
+ "\"columns\".\"name\" AS \"COLUMN_NAME\", ")
+
.append(MonetDriver.getSQLTypeMap("\"columns\".\"type\"")).append(" AS
\"DATA_TYPE\", " +
"\"columns\".\"type\" AS \"TYPE_NAME\", " +
- "\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", 0 AS
\"BUFFER_LENGTH\", " +
- "\"columns\".\"type_scale\" AS \"DECIMAL_DIGITS\", " +
- DatabaseMetaData.bestRowNotPseudo + " AS
\"PSEUDO_COLUMN\" " +
- "FROM \"sys\".\"keys\" AS \"keys\", " +
- "\"sys\".\"objects\" AS \"objects\", " +
- "\"sys\".\"columns\" AS \"columns\", " +
- "\"sys\".\"tables\" AS \"tables\", " +
- "\"sys\".\"schemas\" AS \"schemas\" " +
- "WHERE \"keys\".\"id\" = \"objects\".\"id\" " +
- "AND \"keys\".\"table_id\" =
\"tables\".\"id\" " +
- "AND \"keys\".\"table_id\" =
\"columns\".\"table_id\" " +
- "AND \"objects\".\"name\" =
\"columns\".\"name\" " +
- "AND \"tables\".\"schema_id\" =
\"schemas\".\"id\" " +
- "AND \"keys\".\"type\" IN (0, 1) ";
// only primary keys (type = 0) and unique keys (type = 1), not fkeys (type = 2)
-
+ "\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", " +
+ "CASE WHEN \"columns\".\"type\" IN ('varchar', 'char')
THEN \"columns\".\"type_digits\" ELSE 0 END AS \"BUFFER_LENGTH\", " +
+ "CAST(\"columns\".\"type_scale\" AS smallint) AS
\"DECIMAL_DIGITS\", " +
+
"CAST(").append(DatabaseMetaData.bestRowNotPseudo).append(" AS smallint) AS
\"PSEUDO_COLUMN\" " +
+ "FROM \"sys\".\"keys\", " +
+ "\"sys\".\"objects\", " +
+ "\"sys\".\"columns\", " +
+ "\"sys\".\"tables\", " +
+ "\"sys\".\"schemas\" " +
+ "WHERE \"keys\".\"id\" = \"objects\".\"id\" " +
+ "AND \"keys\".\"table_id\" = \"tables\".\"id\" " +
+ "AND \"keys\".\"table_id\" = \"columns\".\"table_id\" "
+
+ "AND \"objects\".\"name\" = \"columns\".\"name\" " +
+ "AND \"tables\".\"schema_id\" = \"schemas\".\"id\" " +
+ "AND \"keys\".\"type\" IN (0, 1)"); // only primary
keys (type = 0) and unique keys (type = 1), not fkeys (type = 2)
+
+ if (catalog != null && catalog.length() > 0) {
+ // none empty catalog selection.
+ // as we do not support catalogs this always results in
no rows returned
+ query.append(" AND 1 = 0");
+ }
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
