Changeset: 13a9d5316e37 for monetdb-java
URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=13a9d5316e37
Modified Files:
ChangeLog
src/main/java/nl/cwi/monetdb/util/Exporter.java
src/main/java/nl/cwi/monetdb/util/SQLExporter.java
src/main/java/nl/cwi/monetdb/util/XMLExporter.java
Branch: default
Log Message:
Corrected JdbcClient program: the dumping of a view DDL was incorrect when the
view creation statement was not starting with "create view ", such as when it
was created using "CREATE VIEW" or "create or replace view".
It now uses the same DDL text as when it was created, pulled from
sys.tables.query, similar to mclient.
Also corrected the missing ON clause part when dumping a REMOTE TABLE
definition.
diffs (218 lines):
diff --git a/ChangeLog b/ChangeLog
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,11 +1,17 @@
# ChangeLog file for monetdb-java
# This file is updated with Maddlog
+* Wed Sep 4 2019 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
+- Corrected JdbcClient program: the dumping of a view DDL was incorrect when
+ the view creation statement was not starting with "create view ", such as
+ when it was created using "CREATE VIEW" or "create or replace view". It now
+ uses the same DDL text as when it was created, pulled from sys.tables.query.
+
* Thu Aug 22 2019 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
- Improved JdbcClient program by including the schema prefix when dumping
- schema objects. It now behaves more similar to mclient -D -N.
+ schema objects. It now behaves more similar to: mclient -D -N.
- Improved JdbcClient program. It now also dumps definitions of MERGE TABLE,
- REMOTE TABLE, REPLICA TABLE and STREAM TABLE when dumping all tables.
+ REMOTE TABLE, REPLICA TABLE and STREAM TABLE when dumping (all) tables.
* Wed Aug 14 2019 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
- Improved MonetDatabaseMetaData methods:
diff --git a/src/main/java/nl/cwi/monetdb/util/Exporter.java
b/src/main/java/nl/cwi/monetdb/util/Exporter.java
--- a/src/main/java/nl/cwi/monetdb/util/Exporter.java
+++ b/src/main/java/nl/cwi/monetdb/util/Exporter.java
@@ -9,7 +9,6 @@
package nl.cwi.monetdb.util;
import java.io.PrintWriter;
-import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
@@ -22,7 +21,7 @@ public abstract class Exporter {
}
public abstract void dumpSchema(
- final DatabaseMetaData dbmd,
+ final java.sql.DatabaseMetaData dbmd,
final String type,
final String schema,
final String name) throws SQLException;
@@ -32,9 +31,10 @@ public abstract class Exporter {
public abstract void setProperty(final int type, final int value)
throws Exception;
public abstract int getProperty(final int type) throws Exception;
+
//=== shared utilities
- public void useSchemas(final boolean use) {
+ public final void useSchemas(final boolean use) {
useSchema = use;
}
@@ -45,7 +45,7 @@ public abstract class Exporter {
* @param in the string to quote
* @return the quoted string
*/
- protected static String dq(final String in) {
+ protected static final String dq(final String in) {
return "\"" + in.replaceAll("\\\\",
"\\\\\\\\").replaceAll("\"", "\\\\\"") + "\"";
}
@@ -56,21 +56,61 @@ public abstract class Exporter {
* @param in the string to quote
* @return the quoted string
*/
- protected static String q(final String in) {
+ protected static final String q(final String in) {
return "'" + in.replaceAll("\\\\", "\\\\\\\\").replaceAll("'",
"\\\\'") + "'";
}
/**
- * Simple helper function to repeat a given character a number of
- * times.
+ * Simple helper function to repeat a given character a number of times.
*
* @param chr the character to repeat
* @param cnt the number of times to repeat chr
* @return a String holding cnt times chr
*/
- protected static String repeat(final char chr, final int cnt) {
+ protected static final String repeat(final char chr, final int cnt) {
final char[] buf = new char[cnt];
java.util.Arrays.fill(buf, chr);
return new String(buf);
}
+
+ /**
+ * Utility method to fetch the "query" value from sys.tables for a
specific view or table in a specific schema
+ * The "query" value contains the original SQL view creation text or
the ON clause text when it is a REMOTE TABLE
+ *
+ * @param con the JDBC connection, may not be null
+ * @param schema the schem name, may not be null or empty
+ * @param name the view or table name, may not be null or empty
+ * @return the value of the "query" field for the specified view/table
name and schema. It can return null.
+ */
+ protected static final String fetchSysTablesQueryValue(
+ final java.sql.Connection con,
+ final String schema,
+ final String name)
+ {
+ java.sql.Statement stmt = null;
+ ResultSet rs = null;
+ String val = null;
+ try {
+ stmt = con.createStatement();
+ final String cmd = "SELECT query FROM sys.tables WHERE
name = '" + name
+ + "' and schema_id IN (SELECT id FROM
sys.schemas WHERE name = '" + schema + "')";
+ rs = stmt.executeQuery(cmd);
+ if (rs != null) {
+ if (rs.next()) {
+ val = rs.getString(1);
+ }
+ }
+ } catch (SQLException se) {
+ /* ignore */
+ } finally {
+ // free resources
+ if (rs != null) {
+ try { rs.close(); } catch (SQLException se) {
/* ignore */ }
+ }
+ if (stmt != null) {
+ try { stmt.close(); } catch (SQLException se) {
/* ignore */ }
+ }
+ }
+ return val;
+ }
}
diff --git a/src/main/java/nl/cwi/monetdb/util/SQLExporter.java
b/src/main/java/nl/cwi/monetdb/util/SQLExporter.java
--- a/src/main/java/nl/cwi/monetdb/util/SQLExporter.java
+++ b/src/main/java/nl/cwi/monetdb/util/SQLExporter.java
@@ -61,27 +61,12 @@ public final class SQLExporter extends E
changeSchema(schema);
// handle views directly
- if (type.indexOf("VIEW") != -1) { // for types: VIEW and
SYSTEM VIEW
- final String[] types = new String[1];
- types[0] = type;
- final ResultSet tbl = dbmd.getTables(null, schema,
name, types);
- if (tbl != null) {
- if (!tbl.next()) {
- tbl.close();
- throw new SQLException("Whoops no meta
data for view " + fqname);
- }
-
- // This will only work for MonetDB JDBC driver
- final String remarks =
tbl.getString("REMARKS"); // for MonetDB driver this contains the view
definition (if no comment is set) or else the comment
- if (remarks == null) {
- out.println("-- invalid " + type + " "
+ fqname + ": no definition found");
- } else {
- // TODO when remarks does not contain
the create view ... command, but a user added comment, we need to use query:
- // "select query from sys.tables where
name = '" + name + "' and schema_id in (select id from sys.schemas where name =
'" + schema + "')"
- out.println("CREATE " + type + " " +
fqname + " AS " + remarks.replaceFirst("create view [^ ]+ as", ""));
- }
- tbl.close();
- }
+ if (type.endsWith("VIEW")) { // for types: VIEW and SYSTEM
VIEW
+ final String viewDDL =
fetchSysTablesQueryValue(dbmd.getConnection(), schema, name);
+ if (viewDDL != null)
+ out.println(viewDDL);
+ else
+ out.println("-- unknown " + type + " " + fqname
+ ": no SQL view definition found!");
return;
}
@@ -283,7 +268,11 @@ public final class SQLExporter extends E
out.println();
// end the create table statement
- out.println(");");
+ if (type.equals("REMOTE TABLE")) {
+ final String on_clause =
fetchSysTablesQueryValue(dbmd.getConnection(), schema, name);
+ out.println(") ON '" + ((on_clause != null) ? on_clause
: "!!missing mapi:monetdb:// spec") + "';");
+ } else
+ out.println(");");
// create the non unique indexes defined for this table
// we use getIndexInfo to get non-unique indexes, but need to
exclude
diff --git a/src/main/java/nl/cwi/monetdb/util/XMLExporter.java
b/src/main/java/nl/cwi/monetdb/util/XMLExporter.java
--- a/src/main/java/nl/cwi/monetdb/util/XMLExporter.java
+++ b/src/main/java/nl/cwi/monetdb/util/XMLExporter.java
@@ -44,28 +44,12 @@ public final class XMLExporter extends E
throws SQLException
{
// handle views directly
- if (type.indexOf("VIEW") != -1) { // for types: VIEW and
SYSTEM VIEW
- final String[] types = new String[1];
- types[0] = type;
- final ResultSet tbl = dbmd.getTables(null, schema,
name, types);
- if (tbl != null) {
- final String fqname = dq(schema) + "." +
dq(name);
- if (!tbl.next()) {
- tbl.close();
- throw new SQLException("Whoops no meta
data for view " + fqname);
- }
-
- // This will only work for MonetDB JDBC driver
- final String remarks =
tbl.getString("REMARKS"); // for MonetDB driver this contains the view
definition (if no comment is set) or else the comment
- if (remarks == null) {
- out.print("<!-- unable to represent:
CREATE " + type + " " + fqname + " AS ? -->");
- } else {
- // TODO when remarks does not contain
the create view ... command, but a comment, we need to use query:
- // "select query from sys.tables where
name = '" + name + "' and schema_id in (select id from sys.schemas where name =
'" + schema + "')"
- out.println("<!-- CREATE " + type + " "
+ fqname + " AS " + remarks.replaceFirst("create view [^ ]+ as", "") + " -->");
- }
- tbl.close();
- }
+ if (type.endsWith("VIEW")) { // for types: VIEW and SYSTEM
VIEW
+ final String viewDDL =
fetchSysTablesQueryValue(dbmd.getConnection(), schema, name);
+ if (viewDDL != null)
+ out.println("<!-- " + viewDDL + " -->");
+ else
+ out.print("<!-- unknown " + type + " " +
dq(schema) + "." + dq(name) + ": no SQL view definition found! -->");
return;
}
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
