Mercurial > hg > monetdb-java

comparison tests/JDBC_API_Tester.java @ 881:4973317db1cc

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Extend Test_DBCmetadata() test with missing calls to getProcedures(), getProcedureColumns(), getFunctions() and getFunctionColumns() for user defined procedure and user defined function.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 29 Feb 2024 20:33:35 +0100 (13 months ago)
parents aa30a3be8787
children 674f9ed21308
comparison
equal deleted inserted replaced
880:74b907f25564 881:4973317db1cc
962 private void compareResultSet(ResultSet rs, String methodnm, String expected) throws SQLException { 962 private void compareResultSet(ResultSet rs, String methodnm, String expected) throws SQLException {
963 sb.setLength(0); // clear the output log buffer 963 sb.setLength(0); // clear the output log buffer
964 964
965 ResultSetMetaData rsmd = rs.getMetaData(); 965 ResultSetMetaData rsmd = rs.getMetaData();
966 int columnCount = rsmd.getColumnCount(); 966 int columnCount = rsmd.getColumnCount();
967 int suppressSPECIFIC_NAME = 0;
967 sb.append("Resultset with ").append(columnCount).append(" columns\n"); 968 sb.append("Resultset with ").append(columnCount).append(" columns\n");
968 // print result column header names 969 // print result column header names
969 for (int col = 1; col <= columnCount; col++) { 970 for (int col = 1; col <= columnCount; col++) {
970 if (col > 1) 971 if (col > 1)
971 sb.append("\t"); 972 sb.append("\t");
972 sb.append(rsmd.getColumnName(col)); 973 sb.append(rsmd.getColumnName(col));
974 if ("SPECIFIC_NAME".equals(rsmd.getColumnName(col)))
975 suppressSPECIFIC_NAME = col; // contains internal id values which change
973 } 976 }
974 sb.append("\n"); 977 sb.append("\n");
975 // print result column data type info 978 // print result column data type info
976 for (int col = 1; col <= columnCount; col++) { 979 for (int col = 1; col <= columnCount; col++) {
977 if (col > 1) 980 if (col > 1)
1002 // print result rows data 1005 // print result rows data
1003 while (rs.next()) { 1006 while (rs.next()) {
1004 for (int col = 1; col <= columnCount; col++) { 1007 for (int col = 1; col <= columnCount; col++) {
1005 if (col > 1) 1008 if (col > 1)
1006 sb.append("\t"); 1009 sb.append("\t");
1007 sb.append(rs.getString(col)); 1010 if (col == suppressSPECIFIC_NAME)
1011 sb.append("suppressed");
1012 else
1013 sb.append(rs.getString(col));
1008 } 1014 }
1009 sb.append("\n"); 1015 sb.append("\n");
1010 } 1016 }
1011 rs.close(); 1017 rs.close();
1012 1018
1138 handleExecuteDDL(stmt, action, objtype, "tmp.glbl_nopk_twoucs", 1144 handleExecuteDDL(stmt, action, objtype, "tmp.glbl_nopk_twoucs",
1139 "GRANT DELETE, INSERT ON TABLE tmp.glbl_nopk_twoucs TO monetdb;"); 1145 "GRANT DELETE, INSERT ON TABLE tmp.glbl_nopk_twoucs TO monetdb;");
1140 handleExecuteDDL(stmt, action, objtype, "tmp.glbl_nopk_twoucs", 1146 handleExecuteDDL(stmt, action, objtype, "tmp.glbl_nopk_twoucs",
1141 "GRANT SELECT (id2, name2), UPDATE (name2) ON TABLE tmp.glbl_nopk_twoucs TO monetdb;"); 1147 "GRANT SELECT (id2, name2), UPDATE (name2) ON TABLE tmp.glbl_nopk_twoucs TO monetdb;");
1142 1148
1143 // TODO add user procedures / functions to test getProcedures() and getProcedureColumns() more 1149 // create user procedure to test getProcedures() and getProcedureColumns()
1150 action = "create";
1151 objtype = "procedure";
1152 handleExecuteDDL(stmt, action, objtype, "jdbctst.proc",
1153 "CREATE PROCEDURE jdbctst.proc(id bigint, name varchar(999), dt date) BEGIN SET SCHEMA jdbctst; /* ... */ END;");
1154
1155 // create user function to test getFunctions() and getFunctionColumns()
1156 action = "create";
1157 objtype = "function";
1158 handleExecuteDDL(stmt, action, objtype, "jdbctst.func",
1159 "CREATE FUNCTION jdbctst.func(id bigint, name varchar(999), dt date) RETURNS TABLE (val varchar(1022), dt date, id int) BEGIN return SELECT id||' '||name ||' '|| dt, dt, id WHERE id > 0; END;");
1144 1160
1145 // set COMMENT ON schema, tables, columns, indexes, procedures and functions to fetch (and test) data in the REMARKS result column 1161 // set COMMENT ON schema, tables, columns, indexes, procedures and functions to fetch (and test) data in the REMARKS result column
1146 action = "comment on"; 1162 action = "comment on";
1147 handleExecuteDDL(stmt, action, "schema", "jdbctst", 1163 handleExecuteDDL(stmt, action, "schema", "jdbctst",
1148 "COMMENT ON SCHEMA jdbctst IS 'jdbctst schema comment';"); 1164 "COMMENT ON SCHEMA jdbctst IS 'jdbctst schema comment';");
1162 handleExecuteDDL(stmt, action, objtype, "jdbctst.nopk_twoucs_i", 1178 handleExecuteDDL(stmt, action, objtype, "jdbctst.nopk_twoucs_i",
1163 "COMMENT ON INDEX jdbctst.nopk_twoucs_i IS 'jdbctst.nopk_twoucs_i index comment';"); 1179 "COMMENT ON INDEX jdbctst.nopk_twoucs_i IS 'jdbctst.nopk_twoucs_i index comment';");
1164 objtype = "procedure"; 1180 objtype = "procedure";
1165 handleExecuteDDL(stmt, action, objtype, "sys.analyze()", 1181 handleExecuteDDL(stmt, action, objtype, "sys.analyze()",
1166 "COMMENT ON PROCEDURE sys.analyze() IS 'sys.analyze() procedure comment';"); 1182 "COMMENT ON PROCEDURE sys.analyze() IS 'sys.analyze() procedure comment';");
1183 handleExecuteDDL(stmt, action, objtype, "jdbctst.proc",
1184 "COMMENT ON PROCEDURE jdbctst.proc IS 'jdbctst.proc procedure comment';");
1167 objtype = "function"; 1185 objtype = "function";
1168 handleExecuteDDL(stmt, action, objtype, "sys.sin(double)", 1186 handleExecuteDDL(stmt, action, objtype, "sys.sin(double)",
1169 "COMMENT ON FUNCTION sys.sin(double) IS 'sys.sin(double) function comment';"); 1187 "COMMENT ON FUNCTION sys.sin(double) IS 'sys.sin(double) function comment';");
1170 handleExecuteDDL(stmt, action, objtype, "sys.env()", 1188 handleExecuteDDL(stmt, action, objtype, "sys.env()",
1171 "COMMENT ON FUNCTION sys.env() IS 'sys.env() function comment';"); 1189 "COMMENT ON FUNCTION sys.env() IS 'sys.env() function comment';");
1172 handleExecuteDDL(stmt, action, objtype, "sys.statistics()", 1190 handleExecuteDDL(stmt, action, objtype, "sys.statistics()",
1173 "COMMENT ON FUNCTION sys.statistics() IS 'sys.statistics() function comment';"); 1191 "COMMENT ON FUNCTION sys.statistics() IS 'sys.statistics() function comment';");
1192 handleExecuteDDL(stmt, action, objtype, "jdbctst.func",
1193 "COMMENT ON FUNCTION jdbctst.func IS 'jdbctst.func function comment';");
1174 1194
1175 try { 1195 try {
1176 // query the catalog by calling DatabaseMetaData methods 1196 // query the catalog by calling DatabaseMetaData methods
1177 compareResultSet(dbmd.getCatalogs(), "getCatalogs()", 1197 compareResultSet(dbmd.getCatalogs(), "getCatalogs()",
1178 "Resultset with 1 columns\n" + 1198 "Resultset with 1 columns\n" +
1405 "getBestRowIdentifier(null, sys, table\\_types, DatabaseMetaData.bestRowTransaction, false)", 1425 "getBestRowIdentifier(null, sys, table\\_types, DatabaseMetaData.bestRowTransaction, false)",
1406 "Resultset with 8 columns\n" + 1426 "Resultset with 8 columns\n" +
1407 "SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n" + 1427 "SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n" +
1408 "smallint varchar(1024) int varchar(1024) int int smallint smallint\n" + 1428 "smallint varchar(1024) int varchar(1024) int int smallint smallint\n" +
1409 "2 table_type_id 5 smallint " + (isPostDec2023 ? "15" : "16") + " 0 0 1\n"); 1429 "2 table_type_id 5 smallint " + (isPostDec2023 ? "15" : "16") + " 0 0 1\n");
1430
1431 compareResultSet(dbmd.getProcedures(null, "jdbctst", "proc"), "getProcedures(null, jdbctst, proc)",
1432 "Resultset with 9 columns\n" +
1433 "PROCEDURE_CAT PROCEDURE_SCHEM PROCEDURE_NAME Field4 Field5 Field6 REMARKS PROCEDURE_TYPE SPECIFIC_NAME\n" +
1434 "char(1) varchar(1024) varchar(256) char(1) char(1) char(1) varchar(65000) smallint varchar(10)\n" +
1435 "null jdbctst proc null null null jdbctst.proc procedure comment 1 suppressed\n");
1436 // note that the real SPECIFIC_NAME value (containing the internal functions.id) is replaced with "suppressed" in order to get stable output
1437
1438 compareResultSet(dbmd.getProcedureColumns(null, "jdbctst", "proc", "%%"), "getProcedureColumns(null, jdbctst, proc, %%)",
1439 "Resultset with 20 columns\n" +
1440 "PROCEDURE_CAT PROCEDURE_SCHEM PROCEDURE_NAME COLUMN_NAME COLUMN_TYPE DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SPECIFIC_NAME\n" +
1441 "char(1) varchar(1024) varchar(256) varchar(256) smallint int varchar(1024) int int smallint smallint smallint char(1) char(1) int int bigint int varchar(3) varchar(10)\n" +
1442 "null jdbctst proc id 1 -5 bigint 19 8 0 10 2 null null 0 0 null 1 suppressed\n" +
1443 "null jdbctst proc name 1 12 varchar 999 999 null null 2 null null 0 0 3996 2 suppressed\n" +
1444 "null jdbctst proc dt 1 91 date 0 0 null null 2 null null 0 0 null 3 suppressed\n");
1445 // note that the real SPECIFIC_NAME value (containing the internal functions.id) is replaced with "suppressed" in order to get stable output
1446
1447 compareResultSet(dbmd.getFunctions(null, "jdbctst", "func"), "getFunctions(null, jdbctst, func)",
1448 "Resultset with 6 columns\n" +
1449 "FUNCTION_CAT FUNCTION_SCHEM FUNCTION_NAME REMARKS FUNCTION_TYPE SPECIFIC_NAME\n" +
1450 "char(1) varchar(1024) varchar(256) varchar(65000) tinyint varchar(10)\n" +
1451 "null jdbctst func jdbctst.func function comment 2 suppressed\n");
1452 // note that the real SPECIFIC_NAME value (containing the internal functions.id) is replaced with "suppressed" in order to get stable output
1453
1454 compareResultSet(dbmd.getFunctionColumns(null, "jdbctst", "func", "%%"), "getFunctionColumns(null, jdbctst, func, %%)",
1455 "Resultset with 17 columns\n" +
1456 "FUNCTION_CAT FUNCTION_SCHEM FUNCTION_NAME COLUMN_NAME COLUMN_TYPE DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SPECIFIC_NAME\n" +
1457 "char(1) varchar(1024) varchar(256) varchar(256) smallint int varchar(1024) int int smallint smallint smallint char(1) bigint int varchar(3) varchar(10)\n" +
1458 "null jdbctst func val 4 12 varchar 1022 1022 null null 2 null 4088 0 suppressed\n" +
1459 "null jdbctst func dt 3 91 date 0 0 null null 2 null null 1 suppressed\n" +
1460 "null jdbctst func id 3 4 int 10 4 0 10 2 null null 2 suppressed\n" +
1461 "null jdbctst func id 1 -5 bigint 19 8 0 10 2 null null 3 suppressed\n" +
1462 "null jdbctst func name 1 12 varchar 999 999 null null 2 null 3996 4 suppressed\n" +
1463 "null jdbctst func dt 1 91 date 0 0 null null 2 null null 5 suppressed\n");
1464 // note that the real SPECIFIC_NAME value (containing the internal functions.id) is replaced with "suppressed" in order to get stable output
1410 1465
1411 compareResultSet(dbmd.getTablePrivileges(null, "sys", "table\\_types"), "getTablePrivileges(null, sys, table\\_types)", 1466 compareResultSet(dbmd.getTablePrivileges(null, "sys", "table\\_types"), "getTablePrivileges(null, sys, table\\_types)",
1412 "Resultset with 7 columns\n" + 1467 "Resultset with 7 columns\n" +
1413 "TABLE_CAT TABLE_SCHEM TABLE_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE\n" + 1468 "TABLE_CAT TABLE_SCHEM TABLE_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE\n" +
1414 "char(1) varchar(1024) varchar(1024) varchar(1024) varchar(1024) varchar(40) varchar(3)\n" + 1469 "char(1) varchar(1024) varchar(1024) varchar(1024) varchar(1024) varchar(40) varchar(3)\n" +
1478 handleExecuteDDL(stmt, action, objtype, "jdbctst.\"ORDERS\"", "DROP TABLE jdbctst.\"ORDERS\";"); 1533 handleExecuteDDL(stmt, action, objtype, "jdbctst.\"ORDERS\"", "DROP TABLE jdbctst.\"ORDERS\";");
1479 handleExecuteDDL(stmt, action, objtype, "jdbctst.\"CUSTOMERS\"", "DROP TABLE jdbctst.\"CUSTOMERS\";"); 1534 handleExecuteDDL(stmt, action, objtype, "jdbctst.\"CUSTOMERS\"", "DROP TABLE jdbctst.\"CUSTOMERS\";");
1480 handleExecuteDDL(stmt, action, objtype, "jdbctst.fk2c", "DROP TABLE jdbctst.fk2c;"); 1535 handleExecuteDDL(stmt, action, objtype, "jdbctst.fk2c", "DROP TABLE jdbctst.fk2c;");
1481 handleExecuteDDL(stmt, action, objtype, "jdbctst.pk2c", "DROP TABLE jdbctst.pk2c;"); 1536 handleExecuteDDL(stmt, action, objtype, "jdbctst.pk2c", "DROP TABLE jdbctst.pk2c;");
1482 objtype = "procedure"; 1537 objtype = "procedure";
1538 handleExecuteDDL(stmt, action, objtype, "jdbctst.proc()", "DROP PROCEDURE jdbctst.proc(bigint, varchar(999), date);");
1483 handleExecuteDDL(stmt, action, objtype, "sys.analyze()", "COMMENT ON PROCEDURE sys.analyze() IS NULL;"); 1539 handleExecuteDDL(stmt, action, objtype, "sys.analyze()", "COMMENT ON PROCEDURE sys.analyze() IS NULL;");
1484 objtype = "function"; 1540 objtype = "function";
1541 handleExecuteDDL(stmt, action, objtype, "jdbctst.func()", "DROP FUNCTION jdbctst.func(bigint, varchar(999), date);");
1485 handleExecuteDDL(stmt, action, objtype, "sys.sin(double)", "COMMENT ON FUNCTION sys.sin(double) IS NULL;"); 1542 handleExecuteDDL(stmt, action, objtype, "sys.sin(double)", "COMMENT ON FUNCTION sys.sin(double) IS NULL;");
1486 handleExecuteDDL(stmt, action, objtype, "sys.env()", "COMMENT ON FUNCTION sys.env() IS NULL;"); 1543 handleExecuteDDL(stmt, action, objtype, "sys.env()", "COMMENT ON FUNCTION sys.env() IS NULL;");
1487 handleExecuteDDL(stmt, action, objtype, "sys.statistics()", "COMMENT ON FUNCTION sys.statistics() IS NULL;"); 1544 handleExecuteDDL(stmt, action, objtype, "sys.statistics()", "COMMENT ON FUNCTION sys.statistics() IS NULL;");
1488 1545
1489 // All tables in schema jdbctst should now be gone, else we missed some DROP statements 1546 // All tables in schema jdbctst should now be gone, else we missed some DROP statements