Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/util/MDBvalidator.java @ 935:540d8b5944b1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add double quotes also round column names: type and language. They are keywords and may become reserved keywords in the future.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 12 Dec 2024 17:20:18 +0100 (4 months ago)
parents 80ade6a717c2
children e9d65d746c80
comparison
equal deleted inserted replaced
934:80ade6a717c2 935:540d8b5944b1
276 return; 276 return;
277 277
278 // fetch the primary or unique key info from the MonetDB system tables 278 // fetch the primary or unique key info from the MonetDB system tables
279 final StringBuilder sb = new StringBuilder(400); 279 final StringBuilder sb = new StringBuilder(400);
280 sb.append(" FROM sys.keys k JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id" 280 sb.append(" FROM sys.keys k JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id"
281 + " WHERE k.type = ").append(pkey ? 0 : 1) // 0 = primary keys, 1 = unique keys 281 + " WHERE k.\"type\" = ").append(pkey ? 0 : 1) // 0 = primary keys, 1 = unique keys
282 .append(" and s.name = '").append(schema).append('\''); 282 .append(" and s.name = '").append(schema).append('\'');
283 String qry = sb.toString(); 283 String qry = sb.toString();
284 final int count = runCountQuery(qry); 284 final int count = runCountQuery(qry);
285 if (showValidationInfo) 285 if (showValidationInfo)
286 System.out.println("Checking " + minimumWidth(count,6) + " keys in schema " + schema + " for " + checkType + " violations."); 286 System.out.println("Checking " + minimumWidth(count,6) + " keys in schema " + schema + " for " + checkType + " violations.");
289 try { 289 try {
290 sb.setLength(0); // empty previous usage of sb 290 sb.setLength(0); // empty previous usage of sb
291 // fetch the primary or unique key info including columns from the MonetDB system tables 291 // fetch the primary or unique key info including columns from the MonetDB system tables
292 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, k.name as key_nm, o.name as col_nm, o.nr") 292 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, k.name as key_nm, o.name as col_nm, o.nr")
293 .append(" FROM sys.keys k JOIN sys.objects o ON k.id = o.id JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id" 293 .append(" FROM sys.keys k JOIN sys.objects o ON k.id = o.id JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id"
294 + " WHERE k.type = ").append(pkey ? 0 : 1) // 0 = primary keys, 1 = unique keys 294 + " WHERE k.\"type\" = ").append(pkey ? 0 : 1) // 0 = primary keys, 1 = unique keys
295 .append(" and s.name = '").append(schema).append('\'') 295 .append(" and s.name = '").append(schema).append('\'')
296 .append(" ORDER BY t.name, k.name, o.nr;"); 296 .append(" ORDER BY t.name, k.name, o.nr;");
297 qry = sb.toString(); 297 qry = sb.toString();
298 rs = stmt.executeQuery(qry); 298 rs = stmt.executeQuery(qry);
299 if (rs != null) { 299 if (rs != null) {
404 return; 404 return;
405 405
406 // fetch the foreign key info from the MonetDB system tables 406 // fetch the foreign key info from the MonetDB system tables
407 final StringBuilder sb = new StringBuilder(400); 407 final StringBuilder sb = new StringBuilder(400);
408 sb.append(" FROM sys.keys k JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id" 408 sb.append(" FROM sys.keys k JOIN sys.tables t ON k.table_id = t.id JOIN sys.schemas s ON t.schema_id = s.id"
409 + " WHERE k.type = 2") // 2 = foreign keys 409 + " WHERE k.\"type\" = 2") // 2 = foreign keys
410 .append(" and s.name = '").append(schema).append('\''); 410 .append(" and s.name = '").append(schema).append('\'');
411 String qry = sb.toString(); 411 String qry = sb.toString();
412 final int count = runCountQuery(qry); 412 final int count = runCountQuery(qry);
413 if (showValidationInfo) 413 if (showValidationInfo)
414 System.out.println("Checking " + minimumWidth(count,6) + " foreign keys in schema " + schema + " for " + checkType + " violations."); 414 System.out.println("Checking " + minimumWidth(count,6) + " foreign keys in schema " + schema + " for " + checkType + " violations.");
427 " JOIN sys.schemas fs ON ft.schema_id = fs.id" + 427 " JOIN sys.schemas fs ON ft.schema_id = fs.id" +
428 " JOIN sys.keys pk ON fk.rkey = pk.id" + 428 " JOIN sys.keys pk ON fk.rkey = pk.id" +
429 " JOIN sys.objects po ON pk.id = po.id" + 429 " JOIN sys.objects po ON pk.id = po.id" +
430 " JOIN sys.tables pt ON pk.table_id = pt.id" + 430 " JOIN sys.tables pt ON pk.table_id = pt.id" +
431 " JOIN sys.schemas ps ON pt.schema_id = ps.id" + 431 " JOIN sys.schemas ps ON pt.schema_id = ps.id" +
432 " WHERE fk.type = 2" + // 2 = foreign keys 432 " WHERE fk.\"type\" = 2" + // 2 = foreign keys
433 " AND fo.nr = po.nr") // important: matching fk-pk column ordering 433 " AND fo.nr = po.nr") // important: matching fk-pk column ordering
434 .append(" AND fs.name = '").append(schema).append('\'') 434 .append(" AND fs.name = '").append(schema).append('\'')
435 .append(" ORDER BY ft.name, fk.name, fo.nr;"); 435 .append(" ORDER BY ft.name, fk.name, fo.nr;");
436 qry = sb.toString(); 436 qry = sb.toString();
437 rs = stmt.executeQuery(qry); 437 rs = stmt.executeQuery(qry);
548 548
549 /* for some sys/tmp columns also check for empty strings */ 549 /* for some sys/tmp columns also check for empty strings */
550 if (colx.endsWith("name") || colx.endsWith("keyword") 550 if (colx.endsWith("name") || colx.endsWith("keyword")
551 || "\"schema\"".equals(colx) || "\"table\"".equals(colx) || "\"column\"".equals(colx) 551 || "\"schema\"".equals(colx) || "\"table\"".equals(colx) || "\"column\"".equals(colx)
552 || "func".equals(colx) || "mod".equals(colx) || "statement".equals(colx) 552 || "func".equals(colx) || "mod".equals(colx) || "statement".equals(colx)
553 || ("type".equals(colx) && ("_columns".equals(tbl) || "args".equals(tbl) || "storage()".equals(tbl) || "storagemodelinput".equals(tbl)))) { 553 || ("\"type\"".equals(colx) && ("_columns".equals(tbl) || "args".equals(tbl) || "storage()".equals(tbl) || "storagemodelinput".equals(tbl)))) {
554 isNullCond.append(" OR ").append(colx).append(" = ''"); 554 isNullCond.append(" OR ").append(colx).append(" = ''");
555 } 555 }
556 } 556 }
557 } else { 557 } else {
558 isNullCond.append(col).append(" IS NULL"); 558 isNullCond.append(col).append(" IS NULL");
559 559
560 /* for some sys/tmp columns also check for empty strings */ 560 /* for some sys/tmp columns also check for empty strings */
561 if (col.endsWith("name") || col.endsWith("keyword") 561 if (col.endsWith("name") || col.endsWith("keyword")
562 || "\"schema\"".equals(col) || "\"table\"".equals(col) || "\"column\"".equals(col) 562 || "\"schema\"".equals(col) || "\"table\"".equals(col) || "\"column\"".equals(col)
563 || "func".equals(col) || "mod".equals(col) || "statement".equals(col) 563 || "func".equals(col) || "mod".equals(col) || "statement".equals(col)
564 || ("type".equals(col) && ("_columns".equals(tbl) || "args".equals(tbl) || "storage()".equals(tbl) || "storagemodelinput".equals(tbl)))) { 564 || ("\"type\"".equals(col) && ("_columns".equals(tbl) || "args".equals(tbl) || "storage()".equals(tbl) || "storagemodelinput".equals(tbl)))) {
565 isNullCond.append(" OR ").append(col).append(" = ''"); 565 isNullCond.append(" OR ").append(col).append(" = ''");
566 } 566 }
567 } 567 }
568 // reuse the StringBuilder by cleaning it partial 568 // reuse the StringBuilder by cleaning it partial
569 sb.setLength(qry_len); 569 sb.setLength(qry_len);
586 return; 586 return;
587 587
588 // fetch the NOT NULL info from the MonetDB system tables as those are leading for user tables (but not system tables) 588 // fetch the NOT NULL info from the MonetDB system tables as those are leading for user tables (but not system tables)
589 final StringBuilder sb = new StringBuilder(400); 589 final StringBuilder sb = new StringBuilder(400);
590 sb.append(" from sys.columns c join sys.tables t on c.table_id = t.id join sys.schemas s on t.schema_id = s.id" 590 sb.append(" from sys.columns c join sys.tables t on c.table_id = t.id join sys.schemas s on t.schema_id = s.id"
591 + " where t.type in (0, 10, 1, 11) and c.\"null\" = false" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW 591 + " where t.\"type\" in (0, 10, 1, 11) and c.\"null\" = false" // t."type" 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW
592 + " and t.system = ").append(system) 592 + " and t.system = ").append(system)
593 .append(" and s.name = '").append(schema).append('\''); 593 .append(" and s.name = '").append(schema).append('\'');
594 String qry = sb.toString(); 594 String qry = sb.toString();
595 final int count = runCountQuery(qry); 595 final int count = runCountQuery(qry);
596 if (showValidationInfo) 596 if (showValidationInfo)
597 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations."); 597 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations.");
598 598
599 ResultSet rs = null; 599 ResultSet rs = null;
600 try { 600 try {
601 sb.setLength(0); // empty previous usage of sb 601 sb.setLength(0); // empty previous usage of sb
602 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, c.name as col_nm") // , t.type, t.system, c.type, c.type_digits 602 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, c.name as col_nm") // , t."type", t."system", c."type", c.type_digits
603 .append(qry).append(" ORDER BY s.name, t.name, c.name;"); 603 .append(qry).append(" ORDER BY s.name, t.name, c.name;");
604 qry = sb.toString(); 604 qry = sb.toString();
605 rs = stmt.executeQuery(qry); 605 rs = stmt.executeQuery(qry);
606 if (rs != null) { 606 if (rs != null) {
607 String sch, tbl, col; 607 String sch, tbl, col;
636 return; 636 return;
637 637
638 // fetch the max char str len info from the MonetDB system tables as those are leading 638 // fetch the max char str len info from the MonetDB system tables as those are leading
639 final StringBuilder sb = new StringBuilder(400); 639 final StringBuilder sb = new StringBuilder(400);
640 sb.append(" from sys.columns c join sys.tables t on c.table_id = t.id join sys.schemas s on t.schema_id = s.id" 640 sb.append(" from sys.columns c join sys.tables t on c.table_id = t.id join sys.schemas s on t.schema_id = s.id"
641 + " where t.type in (0, 10, 1, 11)" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW 641 + " where t.\"type\" in (0, 10, 1, 11)" // t."type" 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW
642 + " and c.type_digits >= 1" // only when a positive max length is specified 642 + " and c.type_digits >= 1" // only when a positive max length is specified
643 + " and t.system = ").append(system) 643 + " and t.system = ").append(system)
644 .append(" and c.type in ('varchar','char','clob','json','url','blob')") // only for variable character/bytes data type columns 644 .append(" and c.\"type\" in ('varchar','char','clob','json','url','blob')") // only for variable character/bytes data type columns
645 .append(" and s.name = '").append(schema).append('\''); 645 .append(" and s.name = '").append(schema).append('\'');
646 String qry = sb.toString(); 646 String qry = sb.toString();
647 final int count = runCountQuery(qry); 647 final int count = runCountQuery(qry);
648 if (showValidationInfo) 648 if (showValidationInfo)
649 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations."); 649 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations.");
650 650
651 ResultSet rs = null; 651 ResultSet rs = null;
652 try { 652 try {
653 sb.setLength(0); // empty previous usage of sb 653 sb.setLength(0); // empty previous usage of sb
654 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, c.name as col_nm, c.type_digits") // , t.type, t.system, c.type 654 sb.append("SELECT s.name as sch_nm, t.name as tbl_nm, c.name as col_nm, c.type_digits") // , t."type", t."system", c."type"
655 .append(qry).append(" ORDER BY s.name, t.name, c.name, c.type_digits;"); 655 .append(qry).append(" ORDER BY s.name, t.name, c.name, c.type_digits;");
656 qry = sb.toString(); 656 qry = sb.toString();
657 rs = stmt.executeQuery(qry); 657 rs = stmt.executeQuery(qry);
658 if (rs != null) { 658 if (rs != null) {
659 long max_len = 0; 659 long max_len = 0;
1067 {"columns", "table_id, number", null}, // is a view 1067 {"columns", "table_id, number", null}, // is a view
1068 // The id values from sys.schemas, sys._tables, sys._columns and sys.functions combined must be exclusive (see FK from sys.privileges.obj_id) 1068 // The id values from sys.schemas, sys._tables, sys._columns and sys.functions combined must be exclusive (see FK from sys.privileges.obj_id)
1069 {"(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) as T", "T.id", null}, 1069 {"(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) as T", "T.id", null},
1070 {"(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys.tables UNION ALL SELECT id FROM sys.columns UNION ALL SELECT id FROM sys.functions) as T", "T.id", null}, 1070 {"(SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys.tables UNION ALL SELECT id FROM sys.columns UNION ALL SELECT id FROM sys.functions) as T", "T.id", null},
1071 // the next query used to return duplicates for overloaded functions (same function but with different arg names/types), hence it has been extended 1071 // the next query used to return duplicates for overloaded functions (same function but with different arg names/types), hence it has been extended
1072 {"functions f join sys.args a on f.id=a.func_id", "schema_id, f.name, func, mod, language, f.type, side_effect, varres, vararg, a.id", null}, 1072 {"functions f join sys.args a on f.id=a.func_id", "schema_id, f.name, func, mod, \"language\", f.\"type\", side_effect, varres, vararg, a.id", null},
1073 {"args", "func_id, name, inout", null}, 1073 {"args", "func_id, name, inout", null},
1074 {"types", "schema_id, systemname, sqlname", null}, 1074 {"types", "schema_id, systemname, sqlname", null},
1075 {"objects", "id, name", null}, 1075 {"objects", "id, name", null},
1076 {"keys", "table_id, name", null}, 1076 {"keys", "table_id, name", null},
1077 {"idxs", "table_id, name", null}, 1077 {"idxs", "table_id, name", null},
1123 private static final String[][] sys_fkeys = { 1123 private static final String[][] sys_fkeys = {
1124 {"schemas", "authorization", "id", "auths", null}, 1124 {"schemas", "authorization", "id", "auths", null},
1125 {"schemas", "owner", "id", "auths", null}, 1125 {"schemas", "owner", "id", "auths", null},
1126 {"_tables", "schema_id", "id", "schemas", null}, 1126 {"_tables", "schema_id", "id", "schemas", null},
1127 {"tables", "schema_id", "id", "schemas", null}, 1127 {"tables", "schema_id", "id", "schemas", null},
1128 {"_tables", "type", "table_type_id", "table_types", "21"}, 1128 {"_tables", "\"type\"", "table_type_id", "table_types", "21"},
1129 {"tables", "type", "table_type_id", "table_types", "21"}, 1129 {"tables", "\"type\"", "table_type_id", "table_types", "21"},
1130 {"_columns", "table_id", "id", "_tables", null}, 1130 {"_columns", "table_id", "id", "_tables", null},
1131 {"columns", "table_id", "id", "tables", null}, 1131 {"columns", "table_id", "id", "tables", null},
1132 {"_columns", "type", "sqlname", "types", null}, 1132 {"_columns", "\"type\"", "sqlname", "types", null},
1133 {"columns", "type", "sqlname", "types", null}, 1133 {"columns", "\"type\"", "sqlname", "types", null},
1134 {"functions", "schema_id", "id", "schemas", null}, 1134 {"functions", "schema_id", "id", "schemas", null},
1135 {"functions", "type", "function_type_id", "function_types", "27"}, 1135 {"functions", "\"type\"", "function_type_id", "function_types", "27"},
1136 {"functions", "language", "language_id", "function_languages", "27"}, 1136 {"functions", "\"language\"", "language_id", "function_languages", "27"},
1137 // system functions should refer only to functions in MonetDB system schemas 1137 // system functions should refer only to functions in MonetDB system schemas
1138 {"functions WHERE system AND ", "schema_id", "id", "schemas WHERE system", "33"}, // column "system" was added in release 11.33.3 1138 {"functions WHERE system AND ", "schema_id", "id", "schemas WHERE system", "33"}, // column "system" was added in release 11.33.3
1139 {"args", "func_id", "id", "functions", null}, 1139 {"args", "func_id", "id", "functions", null},
1140 {"args", "type", "sqlname", "types", null}, 1140 {"args", "\"type\"", "sqlname", "types", null},
1141 {"types", "schema_id", "id", "schemas", null}, 1141 {"types", "schema_id", "id", "schemas", null},
1142 // {"types WHERE schema_id <> 0 AND ", "schema_id", "id", "schemas", null}, // types with schema_id = 0 should no longer exist 1142 // {"types WHERE schema_id <> 0 AND ", "schema_id", "id", "schemas", null}, // types with schema_id = 0 should no longer exist
1143 {"objects", "id", "id", "ids", "29"}, 1143 {"objects", "id", "id", "ids", "29"},
1144 {"ids WHERE obj_type IN ('key', 'index') AND ", "id", "id", "objects", "29"}, 1144 {"ids WHERE obj_type IN ('key', 'index') AND ", "id", "id", "objects", "29"},
1145 {"keys", "id", "id", "objects", null}, 1145 {"keys", "id", "id", "objects", null},
1146 {"keys", "table_id", "id", "_tables", null}, 1146 {"keys", "table_id", "id", "_tables", null},
1147 {"keys", "table_id", "id", "tables", null}, 1147 {"keys", "table_id", "id", "tables", null},
1148 {"keys", "type", "key_type_id", "key_types", "27"}, 1148 {"keys", "\"type\"", "key_type_id", "key_types", "27"},
1149 {"keys WHERE rkey <> -1 AND ", "rkey", "id", "keys", null}, 1149 {"keys WHERE rkey <> -1 AND ", "rkey", "id", "keys", null},
1150 {"idxs", "id", "id", "objects", null}, 1150 {"idxs", "id", "id", "objects", null},
1151 {"idxs", "table_id", "id", "_tables", null}, 1151 {"idxs", "table_id", "id", "_tables", null},
1152 {"idxs", "table_id", "id", "tables", null}, 1152 {"idxs", "table_id", "id", "tables", null},
1153 {"idxs", "type", "index_type_id", "index_types", "27"}, 1153 {"idxs", "\"type\"", "index_type_id", "index_types", "27"},
1154 {"sequences", "schema_id", "id", "schemas", null}, 1154 {"sequences", "schema_id", "id", "schemas", null},
1155 {"triggers", "table_id", "id", "_tables", null}, 1155 {"triggers", "table_id", "id", "_tables", null},
1156 {"triggers", "table_id", "id", "tables", null}, 1156 {"triggers", "table_id", "id", "tables", null},
1157 {"comments", "id", "id", "ids", "29"}, 1157 {"comments", "id", "id", "ids", "29"},
1158 {"dependencies", "id", "id", "ids", "29"}, 1158 {"dependencies", "id", "id", "ids", "29"},
1182 // not a fk: {"queue", "sessionid", "sessionid", "sessions", "37"}, // as queue contains a historical list, the session may have been closed in the meantime, so not a real persistent fk 1182 // not a fk: {"queue", "sessionid", "sessionid", "sessions", "37"}, // as queue contains a historical list, the session may have been closed in the meantime, so not a real persistent fk
1183 // not a fk: {"queue", "\"username\"", "name", "users", null}, // as queue contains a historical list, the user may have been removed in the meantime, so not a real persistent fk 1183 // not a fk: {"queue", "\"username\"", "name", "users", null}, // as queue contains a historical list, the user may have been removed in the meantime, so not a real persistent fk
1184 {"sessions", "\"username\"", "name", "users", "37"}, 1184 {"sessions", "\"username\"", "name", "users", "37"},
1185 {"sessions", "sessions.optimizer", "name", "optimizers", "37"}, // without the sessions. prefix it will give an error on Jun2020 release 1185 {"sessions", "sessions.optimizer", "name", "optimizers", "37"}, // without the sessions. prefix it will give an error on Jun2020 release
1186 {"statistics", "column_id", "id", "(SELECT id FROM sys._columns UNION ALL SELECT id FROM tmp._columns) as c", null}, 1186 {"statistics", "column_id", "id", "(SELECT id FROM sys._columns UNION ALL SELECT id FROM tmp._columns) as c", null},
1187 {"statistics", "type", "sqlname", "types", null}, 1187 {"statistics", "\"type\"", "sqlname", "types", null},
1188 {"storage()", "\"schema\"", "name", "schemas", null}, 1188 {"storage()", "\"schema\"", "name", "schemas", null},
1189 {"storage()", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null}, 1189 {"storage()", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1190 {"storage()", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null}, 1190 {"storage()", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1191 {"storage()", "\"column\"", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null}, 1191 {"storage()", "\"column\"", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null},
1192 {"storage()", "type", "sqlname", "types", null}, 1192 {"storage()", "\"type\"", "sqlname", "types", null},
1193 {"storage", "\"schema\"", "name", "schemas", null}, 1193 {"storage", "\"schema\"", "name", "schemas", null},
1194 {"storage", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null}, 1194 {"storage", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1195 {"storage", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null}, 1195 {"storage", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1196 {"storage", "\"column\"", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null}, 1196 {"storage", "\"column\"", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null},
1197 {"storage", "type", "sqlname", "types", null}, 1197 {"storage", "\"type\"", "sqlname", "types", null},
1198 {"storagemodel", "\"schema\"", "name", "schemas", null}, 1198 {"storagemodel", "\"schema\"", "name", "schemas", null},
1199 {"storagemodel", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null}, 1199 {"storagemodel", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1200 {"storagemodel", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null}, 1200 {"storagemodel", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1201 {"storagemodel", "\"column\"", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null}, 1201 {"storagemodel", "\"column\"", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null},
1202 {"storagemodel", "type", "sqlname", "types", null}, 1202 {"storagemodel", "\"type\"", "sqlname", "types", null},
1203 {"storagemodelinput", "\"schema\"", "name", "schemas", null}, 1203 {"storagemodelinput", "\"schema\"", "name", "schemas", null},
1204 {"storagemodelinput", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null}, 1204 {"storagemodelinput", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1205 {"storagemodelinput", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null}, 1205 {"storagemodelinput", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1206 {"storagemodelinput", "\"column\"", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null}, 1206 {"storagemodelinput", "\"column\"", "name", "(SELECT name FROM sys._columns UNION ALL SELECT name FROM tmp._columns UNION ALL SELECT name FROM sys.keys UNION ALL SELECT name FROM tmp.keys UNION ALL SELECT name FROM sys.idxs UNION ALL SELECT name FROM tmp.idxs) as c", null},
1207 {"storagemodelinput", "type", "sqlname", "types", null}, 1207 {"storagemodelinput", "\"type\"", "sqlname", "types", null},
1208 {"tablestoragemodel", "\"schema\"", "name", "schemas", null}, 1208 {"tablestoragemodel", "\"schema\"", "name", "schemas", null},
1209 {"tablestoragemodel", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null}, 1209 {"tablestoragemodel", "\"table\"", "name", "(SELECT name FROM sys._tables UNION ALL SELECT name FROM tmp._tables) as t", null},
1210 {"tablestoragemodel", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null}, 1210 {"tablestoragemodel", "\"schema\", \"table\"", "sname, tname", "(SELECT sch.name as sname, tbl.name as tname FROM sys.schemas AS sch JOIN sys.tables AS tbl ON sch.id = tbl.schema_id) as t", null},
1211 // new tables / views introduced in Apr2019 "33" 1211 // new tables / views introduced in Apr2019 "33"
1212 {"schemastorage", "\"schema\"", "name", "schemas", "33"}, 1212 {"schemastorage", "\"schema\"", "name", "schemas", "33"},
1220 {"value_partitions", "table_id", "id", "_tables", "33"}, 1220 {"value_partitions", "table_id", "id", "_tables", "33"},
1221 {"value_partitions", "partition_id", "id", "table_partitions", "33"}, 1221 {"value_partitions", "partition_id", "id", "table_partitions", "33"},
1222 // new tables / views introduced in Jan2022 feature release (11.43.1) 1222 // new tables / views introduced in Jan2022 feature release (11.43.1)
1223 {"keys WHERE action >= 0 AND ", "cast(((action >> 8) & 255) as smallint)", "action_id", "fkey_actions", "43"}, // update action id 1223 {"keys WHERE action >= 0 AND ", "cast(((action >> 8) & 255) as smallint)", "action_id", "fkey_actions", "43"}, // update action id
1224 {"keys WHERE action >= 0 AND ", "cast((action & 255) as smallint)", "action_id", "fkey_actions", "43"}, // delete action id 1224 {"keys WHERE action >= 0 AND ", "cast((action & 255) as smallint)", "action_id", "fkey_actions", "43"}, // delete action id
1225 {"fkeys", "id, table_id, type, name, rkey", "id, table_id, type, name, rkey", "keys", "43"}, 1225 {"fkeys", "id, table_id, \"type\", name, rkey", "id, table_id, \"type\", name, rkey", "keys", "43"},
1226 {"fkeys", "update_action_id", "action_id", "fkey_actions", "43"}, 1226 {"fkeys", "update_action_id", "action_id", "fkey_actions", "43"},
1227 {"fkeys", "delete_action_id", "action_id", "fkey_actions", "43"} 1227 {"fkeys", "delete_action_id", "action_id", "fkey_actions", "43"}
1228 }; 1228 };
1229 1229
1230 private static final String[][] tmp_fkeys = { 1230 private static final String[][] tmp_fkeys = {
1231 {"_tables", "schema_id", "id", "sys.schemas", null}, 1231 {"_tables", "schema_id", "id", "sys.schemas", null},
1232 {"_tables", "type", "table_type_id", "sys.table_types", "21"}, 1232 {"_tables", "\"type\"", "table_type_id", "sys.table_types", "21"},
1233 {"_columns", "table_id", "id", "_tables", null}, 1233 {"_columns", "table_id", "id", "_tables", null},
1234 {"_columns", "type", "sqlname", "sys.types", null}, 1234 {"_columns", "\"type\"", "sqlname", "sys.types", null},
1235 {"keys", "id", "id", "objects", null}, 1235 {"keys", "id", "id", "objects", null},
1236 {"keys", "table_id", "id", "_tables", null}, 1236 {"keys", "table_id", "id", "_tables", null},
1237 {"keys", "type", "key_type_id", "sys.key_types", "27"}, 1237 {"keys", "\"type\"", "key_type_id", "sys.key_types", "27"},
1238 {"keys WHERE rkey <> -1 AND ", "rkey", "id", "keys", null}, 1238 {"keys WHERE rkey <> -1 AND ", "rkey", "id", "keys", null},
1239 {"keys WHERE action >= 0 AND ", "cast(((action >> 8) & 255) as smallint)", "action_id", "sys.fkey_actions", "43"}, // update action id 1239 {"keys WHERE action >= 0 AND ", "cast(((action >> 8) & 255) as smallint)", "action_id", "sys.fkey_actions", "43"}, // update action id
1240 {"keys WHERE action >= 0 AND ", "cast((action & 255) as smallint)", "action_id", "sys.fkey_actions", "43"}, // delete action id 1240 {"keys WHERE action >= 0 AND ", "cast((action & 255) as smallint)", "action_id", "sys.fkey_actions", "43"}, // delete action id
1241 {"idxs", "id", "id", "objects", null}, 1241 {"idxs", "id", "id", "objects", null},
1242 {"idxs", "table_id", "id", "_tables", null}, 1242 {"idxs", "table_id", "id", "_tables", null},
1243 {"idxs", "type", "index_type_id", "sys.index_types", "27"}, 1243 {"idxs", "\"type\"", "index_type_id", "sys.index_types", "27"},
1244 {"objects", "id", "id", "sys.ids", "29"}, 1244 {"objects", "id", "id", "sys.ids", "29"},
1245 {"triggers", "table_id", "id", "_tables", null} 1245 {"triggers", "table_id", "id", "_tables", null}
1246 }; 1246 };
1247 1247
1248 private static final String[][] netcdf_fkeys = { 1248 private static final String[][] netcdf_fkeys = {
1265 // each entry contains: table_nm, col_nm, from_minor_version 1265 // each entry contains: table_nm, col_nm, from_minor_version
1266 // data originally pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_Not_Nullable_columns.sql 1266 // data originally pulled from https://dev.monetdb.org/hg/MonetDB/file/Jun2020/sql/test/sys-schema/Tests/check_Not_Nullable_columns.sql
1267 private static final String[][] sys_notnull = { 1267 private static final String[][] sys_notnull = {
1268 {"_columns", "id", null}, 1268 {"_columns", "id", null},
1269 {"_columns", "name", null}, 1269 {"_columns", "name", null},
1270 {"_columns", "type", null}, 1270 {"_columns", "\"type\"", null},
1271 {"_columns", "type_digits", null}, 1271 {"_columns", "type_digits", null},
1272 {"_columns", "type_scale", null}, 1272 {"_columns", "type_scale", null},
1273 {"_columns", "table_id", null}, 1273 {"_columns", "table_id", null},
1274 {"_columns", "\"null\"", null}, 1274 {"_columns", "\"null\"", null},
1275 {"_columns", "number", null}, 1275 {"_columns", "number", null},
1276 {"_tables", "id", null}, 1276 {"_tables", "id", null},
1277 {"_tables", "name", null}, 1277 {"_tables", "name", null},
1278 {"_tables", "schema_id", null}, 1278 {"_tables", "schema_id", null},
1279 {"_tables", "type", null}, 1279 {"_tables", "\"type\"", null},
1280 {"_tables", "system", null}, 1280 {"_tables", "system", null},
1281 {"_tables", "commit_action", null}, 1281 {"_tables", "commit_action", null},
1282 {"_tables", "access", null}, 1282 {"_tables", "access", null},
1283 {"args", "id", null}, 1283 {"args", "id", null},
1284 {"args", "func_id", null}, 1284 {"args", "func_id", null},
1285 {"args", "name", null}, 1285 {"args", "name", null},
1286 {"args", "type", null}, 1286 {"args", "\"type\"", null},
1287 {"args", "type_digits", null}, 1287 {"args", "type_digits", null},
1288 {"args", "type_scale", null}, 1288 {"args", "type_scale", null},
1289 {"args", "inout", null}, 1289 {"args", "inout", null},
1290 {"args", "number", null}, 1290 {"args", "number", null},
1291 {"auths", "id", null}, 1291 {"auths", "id", null},
1304 {"function_types", "function_type_keyword", "29"}, // column is added in release 29 1304 {"function_types", "function_type_keyword", "29"}, // column is added in release 29
1305 {"functions", "id", null}, 1305 {"functions", "id", null},
1306 {"functions", "name", null}, 1306 {"functions", "name", null},
1307 {"functions", "func", null}, 1307 {"functions", "func", null},
1308 {"functions", "mod", null}, 1308 {"functions", "mod", null},
1309 {"functions", "language", null}, 1309 {"functions", "\"language\"", null},
1310 {"functions", "type", null}, 1310 {"functions", "\"type\"", null},
1311 {"functions", "side_effect", null}, 1311 {"functions", "side_effect", null},
1312 {"functions", "varres", null}, 1312 {"functions", "varres", null},
1313 {"functions", "vararg", null}, 1313 {"functions", "vararg", null},
1314 {"functions", "schema_id", null}, 1314 {"functions", "schema_id", null},
1315 {"functions", "system", "33"}, 1315 {"functions", "system", "33"},
1316 {"idxs", "id", null}, 1316 {"idxs", "id", null},
1317 {"idxs", "table_id", null}, 1317 {"idxs", "table_id", null},
1318 {"idxs", "type", null}, 1318 {"idxs", "\"type\"", null},
1319 {"idxs", "name", null}, 1319 {"idxs", "name", null},
1320 {"index_types", "index_type_id", "27"}, 1320 {"index_types", "index_type_id", "27"},
1321 {"index_types", "index_type_name", "27"}, 1321 {"index_types", "index_type_name", "27"},
1322 {"key_types", "key_type_id", "27"}, 1322 {"key_types", "key_type_id", "27"},
1323 {"key_types", "key_type_name", "27"}, 1323 {"key_types", "key_type_name", "27"},
1324 {"keys", "id", null}, 1324 {"keys", "id", null},
1325 {"keys", "table_id", null}, 1325 {"keys", "table_id", null},
1326 {"keys", "type", null}, 1326 {"keys", "\"type\"", null},
1327 {"keys", "name", null}, 1327 {"keys", "name", null},
1328 {"keys", "rkey", null}, 1328 {"keys", "rkey", null},
1329 {"keys", "action", null}, 1329 {"keys", "action", null},
1330 {"keywords", "keyword", "21"}, 1330 {"keywords", "keyword", "21"},
1331 {"objects", "id", null}, 1331 {"objects", "id", null},
1357 {"sequences", "cycle", null}, 1357 {"sequences", "cycle", null},
1358 {"statistics", "column_id", null}, 1358 {"statistics", "column_id", null},
1359 {"statistics", "\"schema\"", "43"}, // new column as of Jan2022 release (11.43.1) 1359 {"statistics", "\"schema\"", "43"}, // new column as of Jan2022 release (11.43.1)
1360 {"statistics", "\"table\"", "43"}, // new column as of Jan2022 release (11.43.1) 1360 {"statistics", "\"table\"", "43"}, // new column as of Jan2022 release (11.43.1)
1361 {"statistics", "\"column\"", "43"}, // new column as of Jan2022 release (11.43.1) 1361 {"statistics", "\"column\"", "43"}, // new column as of Jan2022 release (11.43.1)
1362 {"statistics", "type", null}, 1362 {"statistics", "\"type\"", null},
1363 {"statistics", "\"width\"", null}, 1363 {"statistics", "\"width\"", null},
1364 {"statistics", "\"count\"", null}, 1364 {"statistics", "\"count\"", null},
1365 {"statistics", "\"unique\"", null}, 1365 {"statistics", "\"unique\"", null},
1366 {"statistics", "nils", null}, 1366 {"statistics", "nils", null},
1367 {"statistics", "sorted", null}, 1367 {"statistics", "sorted", null},
1368 {"statistics", "revsorted", null}, 1368 {"statistics", "revsorted", null},
1369 // the table producing function "storage"() also lists the storage for system tables, whereas the view "storage" does not, so use "storage"() 1369 // the table producing function "storage"() also lists the storage for system tables, whereas the view "storage" does not, so use "storage"()
1370 {"\"storage\"()", "\"schema\"", null}, 1370 {"\"storage\"()", "\"schema\"", null},
1371 {"\"storage\"()", "\"table\"", null}, 1371 {"\"storage\"()", "\"table\"", null},
1372 {"\"storage\"()", "\"column\"", null}, 1372 {"\"storage\"()", "\"column\"", null},
1373 {"\"storage\"()", "type", null}, 1373 {"\"storage\"()", "\"type\"", null},
1374 {"\"storage\"()", "mode", null}, 1374 {"\"storage\"()", "mode", null},
1375 {"\"storage\"()", "location", null}, 1375 {"\"storage\"()", "location", null},
1376 {"\"storage\"()", "count", null}, 1376 {"\"storage\"()", "count", null},
1377 {"\"storage\"()", "typewidth", null}, 1377 {"\"storage\"()", "typewidth", null},
1378 {"\"storage\"()", "columnsize", null}, 1378 {"\"storage\"()", "columnsize", null},
1382 {"\"storage\"()", "imprints", null}, 1382 {"\"storage\"()", "imprints", null},
1383 {"\"storage\"()", "orderidx", null}, 1383 {"\"storage\"()", "orderidx", null},
1384 {"storagemodelinput", "\"schema\"", null}, 1384 {"storagemodelinput", "\"schema\"", null},
1385 {"storagemodelinput", "\"table\"", null}, 1385 {"storagemodelinput", "\"table\"", null},
1386 {"storagemodelinput", "\"column\"", null}, 1386 {"storagemodelinput", "\"column\"", null},
1387 {"storagemodelinput", "type", null}, 1387 {"storagemodelinput", "\"type\"", null},
1388 {"storagemodelinput", "typewidth", null}, 1388 {"storagemodelinput", "typewidth", null},
1389 {"storagemodelinput", "count", null}, 1389 {"storagemodelinput", "count", null},
1390 {"storagemodelinput", "\"distinct\"", null}, 1390 {"storagemodelinput", "\"distinct\"", null},
1391 {"storagemodelinput", "atomwidth", null}, 1391 {"storagemodelinput", "atomwidth", null},
1392 {"storagemodelinput", "reference", null}, 1392 {"storagemodelinput", "reference", null},
1396 {"table_types", "table_type_id", "21"}, 1396 {"table_types", "table_type_id", "21"},
1397 {"table_types", "table_type_name", "21"}, 1397 {"table_types", "table_type_name", "21"},
1398 {"tables", "id", null}, 1398 {"tables", "id", null},
1399 {"tables", "name", null}, 1399 {"tables", "name", null},
1400 {"tables", "schema_id", null}, 1400 {"tables", "schema_id", null},
1401 {"tables", "type", null}, 1401 {"tables", "\"type\"", null},
1402 {"tables", "system", null}, 1402 {"tables", "system", null},
1403 {"tables", "commit_action", null}, 1403 {"tables", "commit_action", null},
1404 {"tables", "access", null}, 1404 {"tables", "access", null},
1405 {"tables", "temporary", null}, 1405 {"tables", "temporary", null},
1406 {"tracelog", "ticks", null}, 1406 {"tracelog", "ticks", null},
1431 {"range_partitions", "table_id", "33"}, 1431 {"range_partitions", "table_id", "33"},
1432 {"range_partitions", "partition_id", "33"}, 1432 {"range_partitions", "partition_id", "33"},
1433 {"range_partitions", "with_nulls", "33"}, 1433 {"range_partitions", "with_nulls", "33"},
1434 {"table_partitions", "id", "33"}, 1434 {"table_partitions", "id", "33"},
1435 {"table_partitions", "table_id", "33"}, 1435 {"table_partitions", "table_id", "33"},
1436 {"table_partitions", "type", "33"}, 1436 {"table_partitions", "\"type\"", "33"},
1437 {"value_partitions", "table_id", "33"}, 1437 {"value_partitions", "table_id", "33"},
1438 {"value_partitions", "partition_id", "33"}, 1438 {"value_partitions", "partition_id", "33"},
1439 // new tables / views introduced in Jan2022 feature release (11.43.1) 1439 // new tables / views introduced in Jan2022 feature release (11.43.1)
1440 {"fkey_actions", "action_id", "43"}, 1440 {"fkey_actions", "action_id", "43"},
1441 {"fkey_actions", "action_name", "43"}, 1441 {"fkey_actions", "action_name", "43"},
1442 {"fkeys", "id", "43"}, 1442 {"fkeys", "id", "43"},
1443 {"fkeys", "table_id", "43"}, 1443 {"fkeys", "table_id", "43"},
1444 {"fkeys", "type", "43"}, 1444 {"fkeys", "\"type\"", "43"},
1445 {"fkeys", "name", "43"}, 1445 {"fkeys", "name", "43"},
1446 {"fkeys", "rkey", "43"}, 1446 {"fkeys", "rkey", "43"},
1447 {"fkeys", "update_action_id", "43"}, 1447 {"fkeys", "update_action_id", "43"},
1448 {"fkeys", "update_action", "43"}, 1448 {"fkeys", "update_action", "43"},
1449 {"fkeys", "delete_action_id", "43"}, 1449 {"fkeys", "delete_action_id", "43"},
1451 }; 1451 };
1452 1452
1453 private static final String[][] tmp_notnull = { 1453 private static final String[][] tmp_notnull = {
1454 {"_columns", "id", null}, 1454 {"_columns", "id", null},
1455 {"_columns", "name", null}, 1455 {"_columns", "name", null},
1456 {"_columns", "type", null}, 1456 {"_columns", "\"type\"", null},
1457 {"_columns", "type_digits", null}, 1457 {"_columns", "type_digits", null},
1458 {"_columns", "type_scale", null}, 1458 {"_columns", "type_scale", null},
1459 {"_columns", "table_id", null}, 1459 {"_columns", "table_id", null},
1460 {"_columns", "\"null\"", null}, 1460 {"_columns", "\"null\"", null},
1461 {"_columns", "number", null}, 1461 {"_columns", "number", null},
1462 {"_tables", "id", null}, 1462 {"_tables", "id", null},
1463 {"_tables", "name", null}, 1463 {"_tables", "name", null},
1464 {"_tables", "schema_id", null}, 1464 {"_tables", "schema_id", null},
1465 {"_tables", "type", null}, 1465 {"_tables", "\"type\"", null},
1466 {"_tables", "system", null}, 1466 {"_tables", "system", null},
1467 {"_tables", "commit_action", null}, 1467 {"_tables", "commit_action", null},
1468 {"_tables", "access", null}, 1468 {"_tables", "access", null},
1469 {"idxs", "id", null}, 1469 {"idxs", "id", null},
1470 {"idxs", "table_id", null}, 1470 {"idxs", "table_id", null},
1471 {"idxs", "type", null}, 1471 {"idxs", "\"type\"", null},
1472 {"idxs", "name", null}, 1472 {"idxs", "name", null},
1473 {"keys", "id", null}, 1473 {"keys", "id", null},
1474 {"keys", "table_id", null}, 1474 {"keys", "table_id", null},
1475 {"keys", "type", null}, 1475 {"keys", "\"type\"", null},
1476 {"keys", "name", null}, 1476 {"keys", "name", null},
1477 {"keys", "rkey", null}, 1477 {"keys", "rkey", null},
1478 {"keys", "action", null}, 1478 {"keys", "action", null},
1479 {"objects", "id", null}, 1479 {"objects", "id", null},
1480 {"objects", "name", null}, 1480 {"objects", "name", null},