Changeset: 63cccacad468 for monetdb-java
URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=63cccacad468
Modified Files:
src/main/java/nl/cwi/monetdb/client/JdbcClient.java
src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java
Branch: default
Log Message:
Add sys.ms_stuff() to returned list of StringFunctions.
diffs (truncated from 1098 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
@@ -26,9 +26,11 @@ import java.sql.Connection;
import java.sql.DriverManager; // required as it will load the
nl.cwi.monetdb.jdbc.MonetDriver class
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.SQLWarning;
+import java.sql.Types;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
@@ -41,7 +43,7 @@ import java.util.List;
* of JDBC only.
*
* @author Fabian Groffen, Martin van Dinther
- * @version 1.4
+ * @version 1.5
*/
public final class JdbcClient {
@@ -779,6 +781,8 @@ 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(";"))
@@ -1301,3 +1305,1041 @@ 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);
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
