Mercurial > hg > monetdb-java
diff src/main/java/org/monetdb/util/MDBvalidator.java @ 778:12e076445cd6
Extended Not Null violation check also with empty string checks for some of the syte tables varchar columns.
author | Martin van Dinther <martin.van.dinther@monetdbsolutions.com> |
---|---|
date | Wed, 06 Sep 2023 21:31:42 +0200 (19 months ago) |
parents | aeb268156580 |
children | e890195256ac |
line wrap: on
line diff
--- a/src/main/java/org/monetdb/util/MDBvalidator.java +++ b/src/main/java/org/monetdb/util/MDBvalidator.java @@ -25,14 +25,14 @@ import java.util.Set; * a) validate system tables data integrity in system schemas: sys and tmp * this includes violations of: * primary key uniqueness - * primary key column(s) not null + * primary key column(s) not null and when varchar not empty string * unique constraint uniqueness * foreign key referential integrity - * column not null + * column not null and some varchar columns not empty string * column maximum length for char/varchar/clob/blob/json/url columns which have max length > 0 * b) validate user schema tables & columns data integrity based on available meta data from system tables & system views * primary key uniqueness - * TODO primary key column(s) not null + * TODO: primary key column(s) not null * unique constraint uniqueness * foreign key referential integrity * column not null @@ -42,7 +42,7 @@ import java.util.Set; * col char/varchar/clob/blob/json/url minimum length (some columns may not be empty, so length >= 1) * col with sequence (serial/bigserial/autoincrement) in range (0/1/min_value .. max_value) * col value is valid in domain (date/time/timestamp/json/inet/url/uuid/...) - * col in list checks (some columns may have only certain values which are not stored in a table or view (eg as fk)) + * col in list-of-values checks (some columns may have only certain values which are not stored in a table or view (eg as fk)) SELECT * FROM sys.table_partitions WHERE "type" NOT IN (5,6,9,10); -- 5=By Column Range (1+4), 6=By Expression Range (2+4), 9=By Column Value (1+8), 10=By Expression Value (2+8), see sql_catalog.h #define PARTITION_*. Note table_partitions is introduced in Apr2019 "33" * col conditional checks (column is not null when other column is (not) null) -- i.e.: either column_id or expression in sys.table_partitions must be populated @@ -536,13 +536,28 @@ public final class MDBvalidator { if (multicolumn) { String[] cols = col.split(", "); for (int c = 0; c < cols.length; c++) { + final String colx = cols[c]; if (c > 0) { isNullCond.append(" OR "); } - isNullCond.append(cols[c]).append(" IS NULL"); + isNullCond.append(colx).append(" IS NULL"); + + /* for some sys/tmp columns also check for empty strings */ + if (colx.endsWith("name") || colx.endsWith("keyword") + || "schema".equals(colx) || "table".equals(colx) || "column".equals(colx) || "func".equals(colx) || "mod".equals(colx) || "statement".equals(colx) + || ("type".equals(colx) && ("_columns".equals(tbl) || "args".equals(tbl) || "storage()".equals(tbl) || "storagemodelinput".equals(tbl)))) { + isNullCond.append(" OR ").append(colx).append(" = ''"); + } } } else { isNullCond.append(col).append(" IS NULL"); + + /* for some sys/tmp columns also check for empty strings */ + if (col.endsWith("name") || col.endsWith("keyword") + || "schema".equals(col) || "table".equals(col) || "column".equals(col) || "func".equals(col) || "mod".equals(col) || "statement".equals(col) + || ("type".equals(col) && ("_columns".equals(tbl) || "args".equals(tbl) || "storage()".equals(tbl) || "storagemodelinput".equals(tbl)))) { + isNullCond.append(" OR ").append(col).append(" = ''"); + } } // reuse the StringBuilder by cleaning it partial sb.setLength(qry_len);