Changeset: 5eb9d54057e6 for monetdb-java
URL: https://dev.monetdb.org/hg/monetdb-java/rev/5eb9d54057e6
Modified Files:
src/main/java/org/monetdb/jdbc/MonetDatabaseMetaData.java
tests/JDBC_API_Tester.java
Branch: default
Log Message:
Improved DatabaseMetaData.getBestRowIdentifier() further by introducing an
extra cte: tableids. It makes it easier to understand/maintain and possibly
faster.
Also implemented a TODO: when there is No PK and No unique constraints. It now
returns all columns of the table, but none for views.
Also updated and extended JDBC_API_Tester program.
diffs (truncated from 669 to 300 lines):
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
@@ -2420,11 +2420,11 @@ public class MonetDatabaseMetaData
* Instead of the first (in case of multiple) we should
potentially use the uc which has
* a) the least number of columns and
* b) the smallest total(size in bytes).
- * That's complex to built in SQL.
+ * That's much more complex to do in SQL than the current
implementation, which is fast and gives a correct result.
*/
- // TODO: when there is No PK and No unique constraints, we
potentially should return all columns of the table (else in SQuirreL no header
is shown in the "Row IDs" tab)
-
- final StringBuilder query = new StringBuilder(2600);
+
+ final StringBuilder query = new StringBuilder(3000);
+ // 1st cte: syskeys
query.append("with syskeys as (" +
// all pkeys
"SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\"
WHERE \"type\" = 0 " +
@@ -2435,6 +2435,7 @@ public class MonetDatabaseMetaData
"AND (\"table_id\", \"id\") IN (select \"table_id\",
min(\"id\") from \"sys\".\"keys\" where \"type\" = 1 group by \"table_id\"))");
if (incltmpkey) {
// we must also include the primary key or unique
constraint of local temporary tables which are stored in tmp.keys
+ // 2nd cte: tmpkeys
query.append(", tmpkeys as (" +
"SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\"
WHERE \"type\" = 0 " +
"UNION ALL " +
@@ -2442,72 +2443,76 @@ public class MonetDatabaseMetaData
"AND \"table_id\" NOT IN (select \"table_id\" from
\"tmp\".\"keys\" where \"type\" = 0) " +
"AND (\"table_id\", \"id\") IN (select \"table_id\",
min(\"id\") from \"tmp\".\"keys\" where \"type\" = 1 group by \"table_id\"))");
}
- query.append(", cols as (" +
- "SELECT c.\"name\", c.\"type\", c.\"type_digits\",
c.\"type_scale\", o.\"nr\" " +
- "FROM syskeys k " +
- "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
- "JOIN \"sys\".\"_columns\" c ON (k.\"table_id\" =
c.\"table_id\" AND o.\"name\" = c.\"name\") " +
- "JOIN \"sys\".\"_tables\" t ON k.\"table_id\" =
t.\"id\" " +
+ // 3rd cte: tableids
+ query.append(", tableids as (" +
+ "SELECT t.\"id\" " +
+ "FROM \"sys\".\"tables\" t " +
"JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" =
s.\"id\" " +
- "WHERE 1=1");
+ "WHERE t.\"type\" NOT IN (1, 11) "); // exclude all
VIEWs and SYSTEM VIEWs
if (catalog != null && !catalog.isEmpty()) {
// non-empty catalog selection.
// as we do not support catalogs this always results in
no rows returned
- query.append(" AND 1=0");
+ query.append("AND 1=0");
} else {
if (scope == DatabaseMetaData.bestRowSession
|| scope == DatabaseMetaData.bestRowTransaction
|| scope == DatabaseMetaData.bestRowTemporary) {
if (schema != null) {
// do not allow wildcard matching with
LIKE, as the resultset does not include the schema info
- query.append(" AND s.\"name\" =
").append(MonetWrapper.sq(schema));
+ query.append("AND s.\"name\" =
").append(MonetWrapper.sq(schema));
}
if (table != null) {
// do not allow wildcard matching with
LIKE, as the resultset does not include the table info
query.append(" AND t.\"name\" =
").append(MonetWrapper.sq(table));
}
- if (!nullable) {
- query.append(" AND c.\"null\" = false");
- }
} else {
- query.append(" AND 1=0");
+ query.append("AND 1=0");
}
}
+ // 4th cte: cols, this unions 2 (or 4 when incltmpkey == true)
select queries
+ query.append("), cols as (" +
+ "SELECT c.\"name\", c.\"type\", c.\"type_digits\",
c.\"type_scale\", o.\"nr\" " +
+ "FROM syskeys k " +
+ "JOIN tableids t ON k.\"table_id\" = t.\"id\" " +
+ "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" " +
+ "JOIN \"sys\".\"_columns\" c ON (k.\"table_id\" =
c.\"table_id\" AND o.\"name\" = c.\"name\")");
+ if (!nullable) {
+ query.append(" WHERE c.\"null\" = false");
+ }
if (incltmpkey) {
// we must also include the primary key or unique
constraint of local temporary tables
// which are stored in tmp.keys, tmp.objects,
tmp._tables and tmp._columns
query.append(" UNION ALL " +
"SELECT c.\"name\", c.\"type\", c.\"type_digits\",
c.\"type_scale\", o.\"nr\" " +
"FROM tmpkeys k " +
+ "JOIN tableids t ON k.\"table_id\" = t.\"id\" " +
"JOIN \"tmp\".\"objects\" o ON k.\"id\" = o.\"id\" " +
- "JOIN \"tmp\".\"_columns\" c ON (k.\"table_id\" =
c.\"table_id\" AND o.\"name\" = c.\"name\") " +
- "JOIN \"tmp\".\"_tables\" t ON k.\"table_id\" =
t.\"id\" " +
- "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" =
s.\"id\" " +
- "WHERE 1=1");
- if (catalog != null && !catalog.isEmpty()) {
- // non-empty catalog selection.
- // as we do not support catalogs this always
results in no rows returned
- query.append(" AND 1=0");
- } else {
- if (scope == DatabaseMetaData.bestRowSession
- || scope == DatabaseMetaData.bestRowTransaction
- || scope == DatabaseMetaData.bestRowTemporary)
{
- if (schema != null) {
- // do not allow wildcard
matching with LIKE, as the resultset does not include the schema info
- query.append(" AND s.\"name\" =
").append(MonetWrapper.sq(schema));
- }
- if (table != null) {
- // do not allow wildcard
matching with LIKE, as the resultset does not include the table info
- query.append(" AND t.\"name\" =
").append(MonetWrapper.sq(table));
- }
- if (!nullable) {
- query.append(" AND c.\"null\" =
false");
- }
- } else {
- query.append(" AND 1=0");
- }
+ "JOIN \"tmp\".\"_columns\" c ON (k.\"table_id\" =
c.\"table_id\" AND o.\"name\" = c.\"name\")");
+ if (!nullable) {
+ query.append(" WHERE c.\"null\" = false");
}
}
+ // when there is No PK and No unique constraints, we should
return all columns of the table
+ // (else in SQuirreL no header is shown in the "Row IDs" tab)
+ query.append(" UNION ALL " +
+ "SELECT c.\"name\", c.\"type\", c.\"type_digits\",
c.\"type_scale\", c.\"number\" " +
+ "FROM tableids t " +
+ "JOIN \"sys\".\"_columns\" c ON t.\"id\" =
c.\"table_id\" " +
+ "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM
\"sys\".\"keys\" WHERE \"type\" in (0, 1))");
+ if (!nullable) {
+ query.append(" AND c.\"null\" = false");
+ }
+ if (incltmpkey) {
+ query.append(" UNION ALL " +
+ "SELECT c.\"name\", c.\"type\", c.\"type_digits\",
c.\"type_scale\", c.\"number\" " +
+ "FROM tableids t " +
+ "JOIN \"tmp\".\"_columns\" c ON t.\"id\" =
c.\"table_id\" " +
+ "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM
\"tmp\".\"keys\" WHERE \"type\" in (0, 1))");
+ if (!nullable) {
+ query.append(" AND c.\"null\" = false");
+ }
+ }
+ // the final select query
query.append(") SELECT " +
"cast(").append(DatabaseMetaData.bestRowSession).append(" AS smallint) AS
\"SCOPE\", " +
"c.\"name\" AS \"COLUMN_NAME\", " +
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
@@ -60,6 +60,7 @@ final public class JDBC_API_Tester {
jt.Test_Csavepoints();
jt.Test_Ctransaction();
jt.Test_Dobjects();
+ jt.Test_DBCmetadata();
jt.Test_FetchSize();
jt.Test_Int128();
jt.Test_PlanExplainTraceDebugCmds();
@@ -676,49 +677,44 @@ final public class JDBC_API_Tester {
"13. commit...failed as expected: COMMIT: not allowed
in auto commit mode\n");
}
+ private void handleExecuteDDL(Statement stmt, String action, String
objtype, String objname, String sql) {
+ try {
+ int response = stmt.executeUpdate(sql);
+ if (response != Statement.SUCCESS_NO_INFO)
+ sb.append(action).append("
").append(objtype).append(" ").append(objname).append(" failed to return -2!!
It returned: ").append(response).append("\n");
+ } catch (SQLException e) {
+ sb.append("Failed to ").append(action).append("
").append(objtype).append(" ").append(objname).append(":
").append(e.getMessage()).append("\n");
+ }
+ }
+
private void Test_Dobjects() {
sb.setLength(0); // clear the output log buffer
- String tablename = "";
- int response;
Statement stmt = null;
try {
stmt = con.createStatement();
- tablename = "nopk_twoucs";
- response = stmt.executeUpdate("CREATE TABLE nopk_twoucs
(id INT NOT NULL UNIQUE, name VARCHAR(99) UNIQUE)");
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Creating table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
-
- tablename = "tmp_nopk_twoucs";
- response = stmt.executeUpdate("CREATE LOCAL TEMP TABLE
tmp_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE)");
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Creating table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
-
- tablename = "tmp_pk_uc";
- response = stmt.executeUpdate("CREATE LOCAL TEMP TABLE
tmp_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE)");
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Creating table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
-
- tablename = "glbl_nopk_twoucs";
- response = stmt.executeUpdate("CREATE GLOBAL TEMP TABLE
glbl_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE)");
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Creating table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
-
- tablename = "glbl_pk_uc";
- response = stmt.executeUpdate("CREATE GLOBAL TEMP TABLE
glbl_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE)");
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Creating table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
} catch (SQLException e) {
- sb.append("failed to create test table
").append(tablename).append(": ").append(e.getMessage()).append("\n");
+ sb.append("failed to createStatement:
").append(e.getMessage()).append("\n");
}
- try {
- response = stmt.executeUpdate("CREATE TYPE xml EXTERNAL
NAME xml");
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Creating type xml failed to return
-2!! It returned: ").append(response).append("\n");
- } catch (SQLException e) {
- sb.append("failed to create type xml:
").append(e.getMessage()).append("\n");
- }
+ String action = "Create";
+ final String objtype = "table";
+ handleExecuteDDL(stmt, action, objtype, "nopk_twoucs",
+ "CREATE TABLE nopk_twoucs (id INT NOT NULL UNIQUE, name
VARCHAR(99) UNIQUE)");
+
+ handleExecuteDDL(stmt, action, objtype, "tmp_nopk_twoucs",
+ "CREATE LOCAL TEMP TABLE tmp_nopk_twoucs (id2 INT NOT
NULL UNIQUE, name2 VARCHAR(99) UNIQUE)");
+
+ handleExecuteDDL(stmt, action, objtype, "tmp_pk_uc",
+ "CREATE LOCAL TEMP TABLE tmp_pk_uc (id1 INT NOT NULL
PRIMARY KEY, name1 VARCHAR(99) UNIQUE)");
+
+ handleExecuteDDL(stmt, action, objtype, "glbl_nopk_twoucs",
+ "CREATE GLOBAL TEMP TABLE glbl_nopk_twoucs (id2 INT NOT
NULL UNIQUE, name2 VARCHAR(99) UNIQUE)");
+
+ handleExecuteDDL(stmt, action, objtype, "glbl_pk_uc",
+ "CREATE GLOBAL TEMP TABLE glbl_pk_uc (id1 INT NOT NULL
PRIMARY KEY, name1 VARCHAR(99) UNIQUE)");
+
+ handleExecuteDDL(stmt, action, "type", "xml", "CREATE TYPE xml
EXTERNAL NAME xml");
try {
DatabaseMetaData dbmd = con.getMetaData();
@@ -914,42 +910,13 @@ final public class JDBC_API_Tester {
}
// cleanup created db objects
- try {
- tablename = "nopk_twoucs";
- response = stmt.executeUpdate("DROP TABLE " +
tablename);
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Dropping table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
-
- tablename = "tmp_nopk_twoucs";
- response = stmt.executeUpdate("DROP TABLE " +
tablename);
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Dropping table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
-
- tablename = "tmp_pk_uc";
- response = stmt.executeUpdate("DROP TABLE " +
tablename);
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Dropping table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
-
- tablename = "glbl_nopk_twoucs";
- response = stmt.executeUpdate("DROP TABLE " +
tablename);
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Dropping table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
-
- tablename = "glbl_pk_uc";
- response = stmt.executeUpdate("DROP TABLE " +
tablename);
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Dropping table
").append(tablename).append(" failed to return -2!! It returned:
").append(response).append("\n");
- } catch (SQLException e) {
- sb.append("failed to drop test table
").append(tablename).append(": ").append(e.getMessage()).append("\n");
- }
-
- try {
- response = stmt.executeUpdate("DROP TYPE xml");
- if (response != Statement.SUCCESS_NO_INFO)
- sb.append("Dropping type xml failed to return
-2!! It returned: ").append(response).append("\n");
- } catch (SQLException e) {
- sb.append("failed to drop type:
").append(e.getMessage()).append("\n");
- }
+ action = "Drop";
+ handleExecuteDDL(stmt, action, objtype, "nopk_twoucs", "DROP
TABLE nopk_twoucs");
+ handleExecuteDDL(stmt, action, objtype, "tmp_nopk_twoucs",
"DROP TABLE tmp_nopk_twoucs");
+ handleExecuteDDL(stmt, action, objtype, "tmp_pk_uc", "DROP
TABLE tmp_pk_uc");
+ handleExecuteDDL(stmt, action, objtype, "glbl_nopk_twoucs",
"DROP TABLE glbl_nopk_twoucs");
+ handleExecuteDDL(stmt, action, objtype, "glbl_pk_uc", "DROP
TABLE glbl_pk_uc");
+ handleExecuteDDL(stmt, action, "type", "xml", "DROP TYPE xml");
closeStmtResSet(stmt, null);
@@ -1010,6 +977,385 @@ final public class JDBC_API_Tester {
compareExpectedOutput(methodnm, expected);
}
+ // same tests as done in clients/odbc/tests/ODBCmetadata.c
+ private void Test_DBCmetadata() {
+ sb.setLength(0); // clear the output log buffer
+
+ Statement stmt = null;
+ DatabaseMetaData dbmd = null;
+ try {
+ stmt = con.createStatement();
+ dbmd = con.getMetaData();
+ } catch (SQLException e) {
+ sb.append("Failed to createStatement:
").append(e.getMessage()).append("\n");
+ }
+
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
