Changeset: 1d7f8e969c47 for monetdb-java
URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=1d7f8e969c47
Modified Files:
src/main/java/nl/cwi/monetdb/client/JdbcClient.java
Branch: default
Log Message:
Undo accidental checkin of Work In Progress.
diffs (truncated from 1062 to 300 lines):
diff --git a/src/main/java/nl/cwi/monetdb/client/JdbcClient.java
b/src/main/java/nl/cwi/monetdb/client/JdbcClient.java
--- a/src/main/java/nl/cwi/monetdb/client/JdbcClient.java
+++ b/src/main/java/nl/cwi/monetdb/client/JdbcClient.java
@@ -43,7 +43,7 @@ import java.util.List;
* of JDBC only.
*
* @author Fabian Groffen, Martin van Dinther
- * @version 1.5
+ * @version 1.4
*/
public final class JdbcClient {
@@ -781,8 +781,6 @@ public final class JdbcClient {
if (tbl != null)
tbl.close();
}
- } else if
(command.equals("\\check_system_catalog_integrity")) {
-
MDBvalidator.check_system_catalog_integrity(con);
} else if (command.startsWith("\\l") ||
command.startsWith("\\i")) {
String object =
command.substring(2).trim();
if (scolonterm &&
object.endsWith(";"))
@@ -1306,1040 +1304,3 @@ final class Table {
}
}
-/**
- * MonetDB Data Integrity Validator program (MDBvalidator) can
- * a) check system tables data integrity (in system schemas: sys, tmp, json,
profiler, and possibly more depending on MonetDB version)
- * this includes violations of:
- * primary key uniqueness
- * primary key column(s) not null
- * unique constraint uniqueness (alternate keys)
- * foreign key referential integrity (match / partial)
- * column not null
- * column maximum length for char/varchar/clob/blob/json/url
- * b) check user schema tables & columns data integrity based on available
meta data from system tables/views
- * column not null
- * column maximum length for char/varchar/clob/blob/json/url
- * TODO primary key uniqueness
- * TODO primary key column(s) not null
- * TODO unique constraint uniqueness (alternate keys)
- * TODO foreign key referential integrity (match / partial)
- *
- * designed and created by Martin van Dinther
- */
-
-final class MDBvalidator {
- private static final String prg = "MDBvalidator";
- private static Connection con;
- private static int majorversion;
- private static int minorversion;
-
- private static boolean verbose = false; // set it to true for
tracing all generated SQL queries
-// private static boolean use_log_table = false; // Not Yet Supported
(and maybe not needed)
-
-// MDBvalidator() {}
-
- public static void main(String[] args) throws Exception {
- System.out.println(prg + " started with " + args.length + "
arguments." + (args.length == 0 ? " Using default JDBC URL !" : ""));
- // parse input args: connection (JDBC_URL), check systbls
(default) or user schema or user db
-
- String JDBC_URL = (args.length > 0) ? args[0]
- :
"jdbc:monetdb://localhost:50000/demo?user=monetdb&password=monetdb&so_timeout=14000";
// &treat_clob_as_varchar=true";
- if (!JDBC_URL.startsWith("jdbc:monetdb://")) {
- System.out.println("ERROR: Invalid JDBC URL. It does
not start with jdbc:monetdb:");
- return;
- }
- try {
- // make connection to target server
- con = java.sql.DriverManager.getConnection(JDBC_URL);
- System.out.println(prg + " connected to MonetDB
server");
- printExceptions(con.getWarnings());
-
- check_system_catalog_integrity(con);
- } catch (SQLException e) {
- printExceptions(e);
- }
-
- // free resources
- if (con != null) {
- try { con.close(); } catch (SQLException e) { /* ignore
*/ }
- }
- }
-
- static void check_system_catalog_integrity(Connection conn) {
- long start_time = System.currentTimeMillis();
- try {
- con = conn;
- // retrieve server version numbers (major and minor).
These are needed to filter out version specific validations
- DatabaseMetaData dbmd = con.getMetaData();
- if (dbmd != null) {
- majorversion = dbmd.getDatabaseMajorVersion();
- minorversion = dbmd.getDatabaseMinorVersion();
- System.out.println("MonetDB server version " +
dbmd.getDatabaseProductVersion());
- // validate majorversion (should be 11) and
minorversion (should be >= 19) (from Jul2015 (11.19.15))
- if (majorversion < 11 || (majorversion == 11 &&
minorversion < 19)) {
- System.out.println("ERROR: this MonetDB
server is too old for " + prg + ". Please upgrade server.");
- con.close();
- return;
- }
- }
- String cur_schema = con.getSchema();
- if (!"sys".equals(cur_schema))
- con.setSchema("sys");
-
- verify("sys", null, sys_pkeys, sys_akeys, sys_fkeys,
sys_notnull, true);
- verify("tmp", null, tmp_pkeys, tmp_akeys, tmp_fkeys,
tmp_notnull, true);
-
- // first determine if netcdf tables (sys.netcdf_files,
sys.netcdf_dims, sys.netcdf_vars, sys.netcdf_vardim) exist in the db
- if (false) { // ToDo built check for existance of
the 5 netcdf tables in sys
- verify("sys", "netcdf", netcdf_pkeys,
netcdf_akeys, netcdf_fkeys, netcdf_notnull, false);
- }
-
- // first determine if geom tables (sys.spatial_ref_sys)
exist in the db
- if (true) { // ToDo built check for existance of
the 2 geom tables in sys
- verify("sys", "geom", geom_pkeys, geom_akeys,
geom_fkeys, geom_notnull, false);
- }
- } catch (SQLException e) {
- printExceptions(e);
- }
-
- long elapsed = System.currentTimeMillis() - start_time;
- long secs = elapsed /1000;
- System.out.println("Validation completed in " + secs + "s and "
+ (elapsed - (secs *1000)) + "ms");
- }
-
- private static void verify(String schema, String group, String[][]
pkeys, String[][] akeys, String[][] fkeys, String[][] colnotnull, boolean
checkmaxstr) {
- boolean is_system_schema = ("sys".equals(schema) ||
"tmp".equals(schema));
- if (pkeys != null)
- verifyUniqueness(schema, group, pkeys, "Primary Key
uniqueness");
- if (pkeys != null)
- verifyNotNull(schema, group, pkeys, "Primary Key Not
Null");
- if (akeys != null)
- verifyUniqueness(schema, group, akeys, "Alternate Key
uniqueness");
- if (fkeys != null)
- verifyFKs(schema, group, fkeys, "Foreign Key
referential integrity");
- if (colnotnull != null)
- verifyNotNull(schema, group, colnotnull, "Not Null");
- else
- verifyNotNull(schema, is_system_schema, "Not Null");
- if (checkmaxstr)
- verifyMaxCharStrLength(schema, is_system_schema, "Max
Character Length");
-/* TODO
- * 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 domain is valid (date/time/timestamp/json/inet/url/uuid/...)
- * col in list checks (some columns may have only certain values
which are not recorded somewhere (eg as fk))
- * col conditional checks (column is not null when other column is
(not) null)
- -- either column_id or expression in sys.table_partitions must
be populated
- SELECT "column_id", "expression", 'Missing either column_id or
expression' AS violation, * FROM "sys"."table_partitions" WHERE "column_id" IS
NULL AND "expression" IS NULL;
- SELECT "column_id", "expression", 'column_id and expression may
not both be populated. One of them must be NULL' AS violation, * FROM
"sys"."table_partitions" WHERE "column_id" IS NOT NULL AND "expression" IS NOT
NULL;
- */
- }
-
- private static void verifyUniqueness(String schema, String group,
String[][] data, String check_type) {
- final int len = data.length;
- System.out.println("Checking " + len + (group != null ? " " +
group : "") + " tables in schema " + schema + " for " + check_type + "
violations.");
-
- StringBuilder sb = new StringBuilder(256); // reusable
buffer to compose SQL validation queries
- sb.append("SELECT COUNT(*) AS duplicates, ");
- final int qry_len = sb.length();
- String tbl;
- String keycols;
- for (int i = 0; i < len; i++) {
- if (isValidVersion(data[i][2])) {
- tbl = data[i][0];
- keycols = data[i][1];
- // reuse the StringBuilder by cleaning it
partial
- sb.setLength(qry_len);
- sb.append(keycols).append(" FROM ");
- if (!tbl.startsWith("(")) { // when tbl
starts with ( it is a unioned table set which we cannot prefix with a schema
name qualifier
- sb.append(schema).append('.');
- }
- sb.append(tbl)
- .append(" GROUP BY ").append(keycols)
- .append(" HAVING COUNT(*) > 1;");
- validate(sb.toString(), schema, tbl, keycols,
check_type);
- }
- }
- }
-
- private static void verifyNotNull(String schema, String group,
String[][] data, String check_type) {
- final int len = data.length;
- System.out.println("Checking " + len + (group != null ? " " +
group : "") + " columns in schema " + schema + " for " + check_type + "
violations.");
-
- StringBuilder sb = new StringBuilder(256); // reusable
buffer to compose SQL validation queries
- sb.append("SELECT ");
- final int qry_len = sb.length();
- String tbl;
- String col;
- boolean multicolumn = false;
- StringBuilder isNullCond = new StringBuilder(80);
- for (int i = 0; i < len; i++) {
- if (isValidVersion(data[i][2])) {
- tbl = data[i][0];
- col = data[i][1];
- multicolumn = col.contains(", "); // some
pkeys consist of multiple columns
- isNullCond.setLength(0); // empty
previous content
- if (multicolumn) {
- String[] cols = col.split(", ");
- for (int c = 0; c < cols.length; c++) {
- if (c > 0) {
- isNullCond.append(" OR
");
- }
-
isNullCond.append(cols[c]).append(" IS NULL");
- }
- } else {
- isNullCond.append(col).append(" IS
NULL");
- }
- // reuse the StringBuilder by cleaning it
partial
- sb.setLength(qry_len);
- sb.append(col)
- .append(", * FROM
").append(schema).append('.').append(tbl)
- .append(" WHERE
").append(isNullCond).append(';');
- validate(sb.toString(), schema, tbl, col,
check_type);
- }
- }
- }
-
- private static void verifyFKs(String schema, String group, String[][]
data, String check_type) {
- final int len = data.length;
- System.out.println("Checking " + len + (group != null ? " " +
group : "") + " foreign keys in schema " + schema + " for " + check_type + "
violations.");
-
- StringBuilder sb = new StringBuilder(400); // reusable
buffer to compose SQL validation queries
- sb.append("SELECT ");
- final int qry_len = sb.length();
- String tbl;
- String cols;
- String ref_tbl;
- String ref_cols;
- for (int i = 0; i < len; i++) {
- if (isValidVersion(data[i][4])) {
- tbl = data[i][0];
- cols = data[i][1];
- ref_cols = data[i][2];
- ref_tbl = data[i][3];
- // reuse the StringBuilder by cleaning it
partial
- sb.setLength(qry_len);
- sb.append(cols).append(", * FROM
").append(schema).append('.').append(tbl);
- if (!tbl.contains(" WHERE "))
- sb.append(" WHERE ");
- sb.append('(').append(cols).append(") NOT IN
(SELECT ").append(ref_cols).append(" FROM ");
- if (!ref_tbl.contains("."))
- sb.append(schema).append('.');
- sb.append(ref_tbl).append(");");
- validate(sb.toString(), schema, tbl, cols,
check_type);
- }
- }
- }
-
- private static void verifyNotNull(String schema, boolean system, String
check_type) {
- // fetch the NOT NULL info from the MonetDB system tables as
those are leading for user tables (but not system tables)
- StringBuilder sb = new StringBuilder(400);
- 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"
- + " where t.type in (0, 10, 1, 11) and
c.\"null\" = false" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 =
SYSTEM VIEW
- + " and t.system = ").append(system)
- .append(" and s.name = '").append(schema).append("'");
- String qry = sb.toString();
- long count = runCountQuery(qry);
- System.out.println("Checking " + count + " columns in schema "
+ schema + " for " + check_type + " violations.");
-
- Statement stmt = createStatement("verifyMaxCharStrLength()");
- ResultSet rs = null;
- try {
- sb.setLength(0); // empty previous usage of sb
- 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
- .append(qry).append(" ORDER BY s.name, t.name,
c.name;");
- qry = sb.toString();
- if (stmt != null)
- rs = stmt.executeQuery(qry);
- if (rs != null) {
- String sch, tbl, col;
- while (rs.next()) {
- // retrieve meta data
- sch = rs.getString(1);
- tbl = rs.getString(2);
- col = rs.getString(3);
- // compose validation query for this
specific column
- sb.setLength(0); // empty
previous usage of sb
- sb.append("SELECT
'").append(sch).append('.').append(tbl).append('.').append(col).append("' as
full_col_nm, *")
- .append(" FROM
\"").append(sch).append("\".\"").append(tbl).append("\"")
- .append(" WHERE
\"").append(col).append("\" IS NULL;");
- validate(sb.toString(), sch, tbl, col,
check_type);
- }
- }
- } catch (SQLException e) {
- System.out.println("Failed to execute query: " + qry);
- printExceptions(e);
- }
- freeStmtRs(stmt, rs);
- }
-
- private static void verifyMaxCharStrLength(String schema, boolean
system, String check_type) {
- // fetch the max char str len info from the MonetDB system
tables as those are leading
- StringBuilder sb = new StringBuilder(400);
- 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"
- + " where t.type in (0, 10, 1, 11) and
c.type_digits >= 1" // t.type 0 = TABLE, 10 = SYSTEM TABLE, 1 = VIEW, 11 =
SYSTEM VIEW
- + " and t.system = ").append(system)
- .append(" and s.name = '").append(schema).append("'")
- .append(" and c.type in ('varchar', 'char', 'clob',
'json', 'url', 'blob')"); // only for variable character/bytes data type
columns
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
