Changeset: 71b039bc2d99 for monetdb-java
URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=71b039bc2d99
Modified Files:
ChangeLog
src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java
src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
Branch: default
Log Message:
Added support for querying the sys.comments table for some meta data methods
when connected to a MonetDB server which has table sys.comments.
diffs (290 lines):
diff --git a/ChangeLog b/ChangeLog
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,6 +1,12 @@
# ChangeLog file for monetdb-java
# This file is updated with Maddlog
+* Thu Apr 19 2018 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
+- DatabaseMetaData methods getTables(), getColumns(), getProcedures() and
+ getFunctions() now return the comment in the REMARKS result column when a
+ comment has been set for the table / view / column / procedure / function
+ via the SQL command COMMENT ON <db-object> IS 'comment-text'.
+
* Mon Oct 23 2017 Sjoerd Mullender <sjo...@acm.org>
- Compiled and released new jars: monetdb-jdbc-2.27.jar, monetdb-mcl-1.16.jar
and updated jdbcclient.jar
diff --git a/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java
b/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java
+++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java
@@ -147,6 +147,11 @@ public class MonetConnection
/** Whether or not CLOB is mapped to Types.VARCHAR instead of
Types.CLOB within this connection */
private boolean treatClobAsVarChar = false;
+ // Internal cache for determining if system table sys.comments (new as
of Mar2018 release) exists on server
+ private boolean queriedCommentsTable = false;
+ private boolean hasCommentsTable = false;
+
+
/**
* Constructor of a Connection for MonetDB. At this moment the
* current implementation limits itself to storing the given host,
@@ -1671,6 +1676,46 @@ public class MonetConnection
}
/**
+ * Internal utility method to query the server to find out if it has
+ * the system table sys.comments (which is new as of Mar2018 release).
+ * The result is cached and reused, so that we only test the query once
per connection.
+ * This method is used by methods from MonetDatabaseMetaData.
+ */
+ boolean commentsTableExists() {
+ if (queriedCommentsTable)
+ return hasCommentsTable;
+
+ queriedCommentsTable = true; // set flag, so the querying
part below is done only once, at first invocation.
+ Statement stmt = null;
+ ResultSet rs = null;
+ try {
+ stmt = createStatement();
+ if (stmt != null) {
+ rs = stmt.executeQuery( "SELECT \"id\",
\"remark\" FROM \"sys\".\"comments\" LIMIT 1");
+ if (rs != null) {
+ rs.next();
+ hasCommentsTable = true;
+ }
+ }
+ } catch (SQLException se) {
+ /* ignore */
+ } finally {
+ if (rs != null) {
+ try {
+ rs.close();
+ } catch (SQLException e) { /* ignore */ }
+ }
+ if (stmt != null) {
+ try {
+ stmt.close();
+ } catch (SQLException e) { /* ignore */ }
+ }
+ }
+// for debug: System.out.println("commentsTableExists returns: " +
hasCommentsTable);
+ return hasCommentsTable;
+ }
+
+ /**
* Sends the given string to MonetDB as special transaction command.
* All possible returned information is discarded.
* Encountered errors are reported.
diff --git a/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
b/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
--- a/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
+++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
@@ -23,7 +23,7 @@ import java.util.ArrayList;
* A DatabaseMetaData object suitable for the MonetDB database.
*
* @author Fabian Groffen, Martin van Dinther
- * @version 0.6
+ * @version 0.7
*/
public class MonetDatabaseMetaData extends MonetWrapper implements
DatabaseMetaData {
private Connection con;
@@ -1705,21 +1705,25 @@ public class MonetDatabaseMetaData exten
String procedureNamePattern
) throws SQLException
{
+ boolean useCommentsTable =
((MonetConnection)con).commentsTableExists();
StringBuilder query = new StringBuilder(980);
query.append("SELECT cast(null as varchar(1)) AS
\"PROCEDURE_CAT\", " +
"\"schemas\".\"name\" AS \"PROCEDURE_SCHEM\", " +
"\"functions\".\"name\" AS \"PROCEDURE_NAME\", " +
"cast(null as char(1)) AS \"Field4\", " +
"cast(null as char(1)) AS \"Field5\", " +
- "cast(null as char(1)) AS \"Field6\", " +
- "cast(\"functions\".\"func\" as varchar(9999)) AS
\"REMARKS\", " +
+ "cast(null as char(1)) AS \"Field6\", ")
+ .append(useCommentsTable ?
"COALESCE(\"comments\".\"remark\", cast(\"functions\".\"func\" as
varchar(9999)))" : "cast(\"functions\".\"func\" as varchar(9999))").append(" AS
\"REMARKS\", " +
// in MonetDB procedures have no return value by design.
"CAST(").append(DatabaseMetaData.procedureNoResult).append(" AS smallint) AS
\"PROCEDURE_TYPE\", " +
// only the id value uniquely identifies a procedure.
Include it to be able to differentiate between multiple overloaded procedures
with the same name
"cast(\"functions\".\"id\" as varchar(10)) AS
\"SPECIFIC_NAME\" " +
- "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON
(\"functions\".\"schema_id\" = \"schemas\".\"id\") " +
+ "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON
(\"functions\".\"schema_id\" = \"schemas\".\"id\") ");
+ if (useCommentsTable) {
+ query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON
(\"functions\".\"id\" = \"comments\".\"id\") ");
+ }
// include procedures only (type = 2). Others will be returned
via getFunctions()
- "WHERE \"functions\".\"type\" = 2");
+ query.append("WHERE \"functions\".\"type\" = 2");
if (catalog != null && catalog.length() > 0) {
// none empty catalog selection.
@@ -1732,7 +1736,6 @@ public class MonetDatabaseMetaData exten
if (procedureNamePattern != null) {
query.append(" AND \"functions\".\"name\"
").append(composeMatchPart(procedureNamePattern));
}
-
query.append(" ORDER BY \"PROCEDURE_SCHEM\",
\"PROCEDURE_NAME\", \"SPECIFIC_NAME\"");
return executeMetaDataQuery(query.toString());
@@ -1837,11 +1840,11 @@ public class MonetDatabaseMetaData exten
"CAST('' as varchar(3)) AS \"IS_NULLABLE\", " +
// the specific name contains the function id, in order
to be able to match the args to the correct overloaded procedure name
"cast(\"functions\".\"id\" as varchar(10)) AS
\"SPECIFIC_NAME\" " +
- "FROM \"sys\".\"args\", \"sys\".\"functions\",
\"sys\".\"schemas\" " +
- "WHERE \"args\".\"func_id\" = \"functions\".\"id\" " +
- "AND \"functions\".\"schema_id\" = \"schemas\".\"id\" " +
+ "FROM \"sys\".\"args\" " +
+ "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" =
\"functions\".\"id\") " +
+ "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" =
\"schemas\".\"id\") " +
// include procedures only (type = 2). Others will be returned
via getFunctionColumns()
- "AND \"functions\".\"type\" = 2");
+ "WHERE \"functions\".\"type\" = 2");
if (catalog != null && catalog.length() > 0) {
// none empty catalog selection.
@@ -1950,6 +1953,7 @@ public class MonetDatabaseMetaData exten
boolean preJul2015 =
("11.19.15".compareTo(getDatabaseProductVersion()) >= 0);
/* for debug: System.out.println("getDatabaseProductVersion()
is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015); */
+ boolean useCommentsTable =
((MonetConnection)con).commentsTableExists();
StringBuilder query = new StringBuilder(1600);
if (preJul2015 && types != null && types.length > 0) {
// we need to filter on the constructed "TABLE_TYPE"
expression, this is only possible when we use a subquery in the FROM
@@ -1972,13 +1976,17 @@ public class MonetDatabaseMetaData exten
} else {
query.append("\"table_types\".\"table_type_name\" AS
\"TABLE_TYPE\", ");
}
- query.append("\"tables\".\"query\" AS \"REMARKS\", " +
+ query.append(useCommentsTable ?
"COALESCE(\"comments\".\"remark\", \"tables\".\"query\")" :
"\"tables\".\"query\"").append(" AS \"REMARKS\", " +
"cast(null as char(1)) AS \"TYPE_CAT\", " +
"cast(null as char(1)) AS \"TYPE_SCHEM\", " +
"cast(null as char(1)) AS \"TYPE_NAME\", " +
"cast(null as char(1)) AS
\"SELF_REFERENCING_COL_NAME\", " +
"cast(null as char(1)) AS \"REF_GENERATION\" " +
- "FROM \"sys\".\"tables\", \"sys\".\"schemas\"");
+ "FROM \"sys\".\"tables\"");
+ if (useCommentsTable) {
+ query.append(" LEFT OUTER JOIN \"sys\".\"comments\" ON
(\"tables\".\"id\" = \"comments\".\"id\")");
+ }
+ query.append(", \"sys\".\"schemas\"");
if (!preJul2015) {
query.append(", \"sys\".\"table_types\"");
}
@@ -2195,6 +2203,7 @@ public class MonetDatabaseMetaData exten
String columnNamePattern
) throws SQLException
{
+ boolean useCommentsTable =
((MonetConnection)con).commentsTableExists();
StringBuilder query = new StringBuilder(2450);
query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
"\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
@@ -2209,8 +2218,8 @@ public class MonetDatabaseMetaData exten
"WHEN \"columns\".\"type\" IN ('int',
'smallint', 'tinyint', 'bigint', 'hugeint', 'float', 'real', 'double', 'oid',
'wrd') THEN 2 " +
"ELSE 0 END AS int) AS \"NUM_PREC_RADIX\", " +
"cast(CASE \"null\" WHEN true THEN
").append(ResultSetMetaData.columnNullable)
- .append(" WHEN false THEN
").append(ResultSetMetaData.columnNoNulls).append(" END AS int) AS
\"NULLABLE\", " +
- "cast(null AS varchar(1)) AS \"REMARKS\", " +
+ .append(" WHEN false THEN
").append(ResultSetMetaData.columnNoNulls).append(" END AS int) AS
\"NULLABLE\", ")
+ .append(useCommentsTable ? "\"comments\".\"remark\"" :
"cast(null AS varchar(1))").append(" AS \"REMARKS\", " +
"\"columns\".\"default\" AS \"COLUMN_DEF\", " +
"cast(0 as int) AS \"SQL_DATA_TYPE\", " +
"cast(0 as int) AS \"SQL_DATETIME_SUB\", " +
@@ -2223,11 +2232,14 @@ public class MonetDatabaseMetaData exten
"cast(null AS smallint) AS \"SOURCE_DATA_TYPE\", " +
"cast(CASE WHEN \"columns\".\"default\" IS NOT NULL AND
\"columns\".\"default\" LIKE 'next value for %' THEN 'YES' ELSE 'NO' END AS
varchar(3)) AS \"IS_AUTOINCREMENT\", " +
"cast('NO' AS varchar(3)) AS \"IS_GENERATEDCOLUMN\" " +
- "FROM \"sys\".\"columns\", " +
- "\"sys\".\"tables\", " +
- "\"sys\".\"schemas\" " +
- "WHERE \"columns\".\"table_id\" = \"tables\".\"id\" " +
- "AND \"tables\".\"schema_id\" = \"schemas\".\"id\"");
+ "FROM \"sys\".\"columns\"");
+ if (useCommentsTable) {
+ query.append(" LEFT OUTER JOIN \"sys\".\"comments\" ON
(\"columns\".\"id\" = \"comments\".\"id\")");
+ }
+ query.append(", \"sys\".\"tables\"" +
+ ", \"sys\".\"schemas\" " +
+ "WHERE \"columns\".\"table_id\" = \"tables\".\"id\"" +
+ " AND \"tables\".\"schema_id\" = \"schemas\".\"id\"");
if (catalog != null && catalog.length() > 0) {
// none empty catalog selection.
@@ -3277,7 +3289,7 @@ public class MonetDatabaseMetaData exten
"\"schemas\".\"name\" AS \"TYPE_SCHEM\", " +
"\"types\".\"sqlname\" AS \"TYPE_NAME\", " +
"CASE \"types\".\"sqlname\"" +
- // next 4 UDTs are known
+ // next 4 UDTs are standard
" WHEN 'inet' THEN
'nl.cwi.monetdb.jdbc.types.INET'" +
" WHEN 'json' THEN 'java.lang.String'" +
" WHEN 'url' THEN
'nl.cwi.monetdb.jdbc.types.URL'" +
@@ -3287,9 +3299,9 @@ public class MonetDatabaseMetaData exten
.append(" ELSE ").append(Types.STRUCT).append("
END AS int) AS \"DATA_TYPE\", " +
"\"types\".\"systemname\" AS \"REMARKS\", " +
"cast(null as smallint) AS \"BASE_TYPE\" " +
- "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON
\"types\".\"schema_id\" = \"schemas\".\"id\" " +
- // exclude the built-in types (I assume they always
have id <= 99 and eclass < 15)
- "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\"
>= 15");
+ "FROM \"sys\".\"types\" JOIN \"sys\".\"schemas\" ON
\"types\".\"schema_id\" = \"schemas\".\"id\" " +
+ // exclude the built-in types (I assume they always have id <=
99 and eclass < 15)
+ "WHERE \"types\".\"id\" > 99 AND \"types\".\"eclass\" >= 15");
if (catalog != null && catalog.length() > 0) {
// none empty catalog selection.
@@ -3302,6 +3314,7 @@ public class MonetDatabaseMetaData exten
if (typeNamePattern != null) {
query.append(" AND \"types\".\"sqlname\"
").append(composeMatchPart(typeNamePattern));
}
+
if (types != null && types.length > 0) {
query.append(") AS getUDTs WHERE \"DATA_TYPE\" IN (");
for (int i = 0; i < types.length; i++) {
@@ -3866,11 +3879,12 @@ public class MonetDatabaseMetaData exten
String functionNamePattern)
throws SQLException
{
+ boolean useCommentsTable =
((MonetConnection)con).commentsTableExists();
StringBuilder query = new StringBuilder(800);
query.append("SELECT cast(null as varchar(1)) AS
\"FUNCTION_CAT\", " +
"\"schemas\".\"name\" AS \"FUNCTION_SCHEM\", " +
- "\"functions\".\"name\" AS \"FUNCTION_NAME\", " +
- "cast(\"functions\".\"func\" as varchar(9999)) AS
\"REMARKS\", " +
+ "\"functions\".\"name\" AS \"FUNCTION_NAME\", ")
+ .append(useCommentsTable ?
"COALESCE(\"comments\".\"remark\", cast(\"functions\".\"func\" as
varchar(9999)))" : "cast(\"functions\".\"func\" as varchar(9999))").append(" AS
\"REMARKS\", " +
"CASE \"functions\".\"type\"" +
" WHEN 1 THEN
").append(DatabaseMetaData.functionNoTable)
.append(" WHEN 2 THEN
").append(DatabaseMetaData.functionNoTable)
@@ -3880,10 +3894,12 @@ public class MonetDatabaseMetaData exten
.append(" ELSE
").append(DatabaseMetaData.functionResultUnknown).append(" END AS
\"FUNCTION_TYPE\", " +
// only the id value uniquely identifies a function.
Include it to be able to differentiate between multiple overloaded functions
with the same name
"cast(\"functions\".\"id\" as varchar(10)) AS
\"SPECIFIC_NAME\" " +
- "FROM \"sys\".\"functions\", \"sys\".\"schemas\" " +
- "WHERE \"functions\".\"schema_id\" = \"schemas\".\"id\" " +
- // exclude procedures (type = 2). Those need to be returned via
getProcedures()
- "AND \"functions\".\"type\" <> 2");
+ "FROM \"sys\".\"functions\" JOIN \"sys\".\"schemas\" ON
(\"functions\".\"schema_id\" = \"schemas\".\"id\") ");
+ if (useCommentsTable) {
+ query.append("LEFT OUTER JOIN \"sys\".\"comments\" ON
(\"functions\".\"id\" = \"comments\".\"id\") ");
+ }
+ // only functions, so exclude procedures (type = 2). Those will
to be returned via getProcedures()
+ query.append("WHERE \"functions\".\"type\" <> 2");
if (catalog != null && catalog.length() > 0) {
// none empty catalog selection.
@@ -3994,11 +4010,11 @@ public class MonetDatabaseMetaData exten
"CAST('' as varchar(3)) AS \"IS_NULLABLE\", " +
// the specific name contains the function id, in order
to be able to match the args to the correct overloaded function name
"cast(\"functions\".\"id\" as varchar(10)) AS
\"SPECIFIC_NAME\" " +
- "FROM \"sys\".\"args\", \"sys\".\"functions\",
\"sys\".\"schemas\" " +
- "WHERE \"args\".\"func_id\" = \"functions\".\"id\" " +
- "AND \"functions\".\"schema_id\" = \"schemas\".\"id\" " +
- // exclude procedures (type = 2). Those need to be returned via
getProcedureColumns()
- "AND \"functions\".\"type\" <> 2");
+ "FROM \"sys\".\"args\" " +
+ "JOIN \"sys\".\"functions\" ON (\"args\".\"func_id\" =
\"functions\".\"id\") " +
+ "JOIN \"sys\".\"schemas\" ON (\"functions\".\"schema_id\" =
\"schemas\".\"id\") " +
+ // only functions, so exclude procedures (type = 2). Those will
to be returned via getProcedureColumns()
+ "WHERE \"functions\".\"type\" <> 2");
if (catalog != null && catalog.length() > 0) {
// none empty catalog selection.
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
