Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/util/MDBvalidator.java @ 890:7621c80b08da

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Optimise code, use append(char) instead of append(String)
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 04 Apr 2024 19:13:43 +0200 (13 months ago)
parents e890195256ac
children d311affc65f0
comparison
equal deleted inserted replaced
889:485c75b35cc9 890:7621c80b08da
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.");
287 287
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) {
300 String sch = null, tbl, key, col; 300 String sch = null, tbl, key, col;
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.");
415 415
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);
438 if (rs != null) { 438 if (rs != null) {
439 String fsch = null, ftbl = null, fcol = null; 439 String fsch = null, ftbl = null, fcol = null;
586 // fetch the NOT NULL info from the MonetDB system tables as those are leading for user tables (but not system tables) 586 // fetch the NOT NULL info from the MonetDB system tables as those are leading for user tables (but not system tables)
587 final StringBuilder sb = new StringBuilder(400); 587 final StringBuilder sb = new StringBuilder(400);
588 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" 588 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"
589 + " where t.type in (0, 10, 1, 11) and c.\"null\" = false" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW 589 + " where t.type in (0, 10, 1, 11) and c.\"null\" = false" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW
590 + " and t.system = ").append(system) 590 + " and t.system = ").append(system)
591 .append(" and s.name = '").append(schema).append("'"); 591 .append(" and s.name = '").append(schema).append('\'');
592 String qry = sb.toString(); 592 String qry = sb.toString();
593 final int count = runCountQuery(qry); 593 final int count = runCountQuery(qry);
594 if (showValidationInfo) 594 if (showValidationInfo)
595 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations."); 595 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations.");
596 596
638 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" 638 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"
639 + " where t.type in (0, 10, 1, 11)" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW 639 + " where t.type in (0, 10, 1, 11)" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 = SYSTEM VIEW
640 + " and c.type_digits >= 1" // only when a positive max length is specified 640 + " and c.type_digits >= 1" // only when a positive max length is specified
641 + " and t.system = ").append(system) 641 + " and t.system = ").append(system)
642 .append(" and c.type in ('varchar','char','clob','json','url','blob')") // only for variable character/bytes data type columns 642 .append(" and c.type in ('varchar','char','clob','json','url','blob')") // only for variable character/bytes data type columns
643 .append(" and s.name = '").append(schema).append("'"); 643 .append(" and s.name = '").append(schema).append('\'');
644 String qry = sb.toString(); 644 String qry = sb.toString();
645 final int count = runCountQuery(qry); 645 final int count = runCountQuery(qry);
646 if (showValidationInfo) 646 if (showValidationInfo)
647 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations."); 647 System.out.println("Checking " + minimumWidth(count,6) + " columns in schema " + schema + " for " + checkType + " violations.");
648 648