This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new 211c122239 Database perspective: Generate DDL without quoting, Stop
doesn't stop DDL/DML, rows of PRAGMA/RETURNING/CALL are discarded, fixes #9314
(#9369)
211c122239 is described below
commit 211c122239c31f4f9eaef8b7a7117761d920c90c
Author: Bart Maertens <[email protected]>
AuthorDate: Wed Nov 11 12:51:55 2026 +0100
Database perspective: Generate DDL without quoting, Stop doesn't stop
DDL/DML, rows of PRAGMA/RETURNING/CALL are discarded, fixes #9314 (#9369)
---
.../org/apache/hop/core/database/Database.java | 80 ++++++++++++++++++++--
.../hop/core/database/ExecutedStatement.java | 41 +++++++++++
.../hop/core/database/SqlQueryClassifier.java | 18 +++++
.../org/apache/hop/core/database/DatabaseTest.java | 56 +++++++++++++++
.../hop/core/database/SqlQueryClassifierTest.java | 11 +++
.../ROOT/pages/hop-gui/perspective-database.adoc | 8 +--
.../hop/ui/core/database/dialog/SqlEditor.java | 46 +++++++++++--
.../perspective/database/DatabaseOperation.java | 12 +++-
.../perspective/database/DatabaseSqlEditorTab.java | 73 +++++++++++++++++---
.../perspective/database/DatabaseWorkbench.java | 4 +-
.../dialog/messages/messages_en_US.properties | 1 +
.../database/messages/messages_en_US.properties | 1 +
.../database/DatabaseOperationsPanelTest.java | 18 +++++
.../database/DatabaseSqlEditorTabTest.java | 68 ++++++++++++++++--
14 files changed, 406 insertions(+), 31 deletions(-)
diff --git a/core/src/main/java/org/apache/hop/core/database/Database.java
b/core/src/main/java/org/apache/hop/core/database/Database.java
index cd31deb4c1..aba99e0647 100644
--- a/core/src/main/java/org/apache/hop/core/database/Database.java
+++ b/core/src/main/java/org/apache/hop/core/database/Database.java
@@ -152,6 +152,14 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
private Statement selStmt;
private PreparedStatement pstmt;
+
+ /**
+ * The statement {@link #execStatement(String, IRowMeta, Object[])} or {@link
+ * #executeStatement(String, int)} is running, so that {@link
#cancelQuery()} can stop DDL and DML
+ * from another thread as well as queries.
+ */
+ private volatile Statement runningStatement;
+
@Getter private PreparedStatement prepStatementLookup;
@Getter private PreparedStatement prepStatementUpdate;
@Getter private PreparedStatement prepStatementInsert;
@@ -805,6 +813,7 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
cancelStatement(pstmt);
cancelStatement(selStmt);
+ cancelStatement(runningStatement);
}
/**
@@ -1558,18 +1567,24 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
boolean resultSet;
int count;
if (params != null) {
- PreparedStatement prepStmt =
connection.prepareStatement(databaseMeta.stripCR(sql));
- setValues(params, data, prepStmt); // set the parameters!
- applyStatementQueryTimeout(prepStmt);
- resultSet = prepStmt.execute();
- count = prepStmt.getUpdateCount();
- prepStmt.close();
+ try (PreparedStatement prepStmt =
connection.prepareStatement(databaseMeta.stripCR(sql))) {
+ setValues(params, data, prepStmt); // set the parameters!
+ applyStatementQueryTimeout(prepStmt);
+ runningStatement = prepStmt;
+ resultSet = prepStmt.execute();
+ count = prepStmt.getUpdateCount();
+ } finally {
+ runningStatement = null;
+ }
} else {
String sqlStripped = databaseMeta.stripCR(sql);
try (Statement stmt = connection.createStatement()) {
applyStatementQueryTimeout(stmt);
+ runningStatement = stmt;
resultSet = stmt.execute(sqlStripped);
count = stmt.getUpdateCount();
+ } finally {
+ runningStatement = null;
}
}
if (!resultSet) {
@@ -1594,6 +1609,59 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
return result;
}
+ /**
+ * Execute one statement of any kind and read the rows it returns, if it
returns any. Unlike
+ * {@link #execStatement(String)} this keeps the result set of statements
that are not plain
+ * queries, such as {@code PRAGMA table_info(t)}, {@code INSERT ...
RETURNING id} or {@code CALL
+ * proc()}. Only the first result is read, up to {@code limit} rows. {@link
#cancelQuery()} stops
+ * the statement while it runs.
+ *
+ * @param rawsql the statement, comments allowed
+ * @param limit the maximum number of rows to read, {@literal <=0} for no
limit
+ * @return the update count, the counts per verb and the rows, if any
+ * @throws HopDatabaseException in case anything goes wrong.
+ */
+ public ExecutedStatement executeStatement(String rawsql, int limit) throws
HopDatabaseException {
+ String sql =
databaseMeta.getIDatabase().createSqlScriptParser().removeComments(rawsql).trim();
+ Result result = new Result();
+ int count = -1;
+ IRowMeta resultRowMeta = null;
+ List<Object[]> rows = null;
+ try (Statement stmt = connection.createStatement()) {
+ applyStatementQueryTimeout(stmt);
+ // No setMaxRows here: some drivers (jTDS, for one) implement it with
SET ROWCOUNT, which
+ // would also cap the rows an UPDATE or DELETE touches. The limit
applies while reading.
+ runningStatement = stmt;
+ boolean hasResultSet = stmt.execute(databaseMeta.stripCR(sql));
+ if (hasResultSet) {
+ try (ResultSet rs = stmt.getResultSet()) {
+ resultRowMeta = getRowInfo(rs.getMetaData(),
databaseMeta.isMySqlVariant(), false);
+ rows = new ArrayList<>();
+ while (limit <= 0 || rows.size() < limit) {
+ Object[] row = getRow(rs, null, resultRowMeta);
+ if (row == null) {
+ break;
+ }
+ rows.add(row);
+ }
+ }
+ } else {
+ count = stmt.getUpdateCount();
+ countAffectedRows(result, sql, count);
+ }
+ if (SqlQueryClassifier.isSchemaChange(sql)) {
+ DbCache.getInstance().clear(databaseMeta.getName());
+ }
+ } catch (SQLException ex) {
+ throw new HopDatabaseException("Couldn't execute SQL: " + sql +
Const.CR, ex);
+ } catch (Exception e) {
+ throw new HopDatabaseException("Unexpected error executing SQL: " +
Const.CR, e);
+ } finally {
+ runningStatement = null;
+ }
+ return new ExecutedStatement(result, count, resultRowMeta, rows);
+ }
+
/**
* Attribute the number of rows a statement affected to the matching counter
of the result. JDBC
* reports a single count per statement; for MERGE, UPSERT and REPLACE it
covers the inserted,
diff --git
a/core/src/main/java/org/apache/hop/core/database/ExecutedStatement.java
b/core/src/main/java/org/apache/hop/core/database/ExecutedStatement.java
new file mode 100644
index 0000000000..1f6b80f81a
--- /dev/null
+++ b/core/src/main/java/org/apache/hop/core/database/ExecutedStatement.java
@@ -0,0 +1,41 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.core.database;
+
+import java.util.List;
+import org.apache.hop.core.Result;
+import org.apache.hop.core.row.IRowMeta;
+
+/**
+ * What {@link Database#executeStatement(String, int)} found after running one
statement.
+ *
+ * @param result the counts per verb, as {@link
Database#execStatement(String)} reports them
+ * @param updateCount the JDBC update count, {@code -1} when the statement
returned rows
+ * @param rowMeta the layout of the returned rows, {@code null} when there
were none
+ * @param rows the returned rows, {@code null} when the statement did not
return a result set
+ */
+public record ExecutedStatement(
+ Result result, int updateCount, IRowMeta rowMeta, List<Object[]> rows) {
+
+ /**
+ * @return {@code true} when the statement returned a result set, even an
empty one
+ */
+ public boolean hasRows() {
+ return rows != null;
+ }
+}
diff --git
a/core/src/main/java/org/apache/hop/core/database/SqlQueryClassifier.java
b/core/src/main/java/org/apache/hop/core/database/SqlQueryClassifier.java
index 8dc9ffcb31..9bf63176b9 100644
--- a/core/src/main/java/org/apache/hop/core/database/SqlQueryClassifier.java
+++ b/core/src/main/java/org/apache/hop/core/database/SqlQueryClassifier.java
@@ -94,6 +94,10 @@ public final class SqlQueryClassifier {
"MULTISET",
"VOLATILE");
+ /** Verbs of statements that insert, update or delete rows. */
+ private static final Set<String> ROW_CHANGE_VERBS =
+ Set.of("INSERT", "UPDATE", "DELETE", "MERGE", "UPSERT", "REPLACE");
+
/** Object types whose layout is reflected in cached row metadata. */
private static final Set<String> SCHEMA_CHANGE_OBJECTS = Set.of("TABLE",
"VIEW", "SYNONYM");
@@ -215,6 +219,20 @@ public final class SqlQueryClassifier {
return first;
}
+ /**
+ * Whether the update count of a statement is a number of rows. Some drivers
report a stale count
+ * for DDL (sqlite-jdbc repeats the count of the previous INSERT after a
{@code CREATE TABLE}), so
+ * only these verbs are trusted.
+ *
+ * @param sql one statement, comments allowed
+ * @return {@code true} for {@code INSERT}, {@code UPDATE}, {@code DELETE},
{@code MERGE}, {@code
+ * UPSERT} and {@code REPLACE}, also after a {@code WITH} clause
+ */
+ public static boolean isRowChange(String sql) {
+ String verb = statementVerb(sql);
+ return verb != null && ROW_CHANGE_VERBS.contains(verb);
+ }
+
/**
* Whether a statement changes the layout of a table or a view, which makes
any row metadata
* cached for that connection unreliable.
diff --git a/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
b/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
index ad23986036..8cd63712c5 100644
--- a/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
+++ b/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
@@ -1015,6 +1015,62 @@ class DatabaseTest {
.getNrLinesDeleted());
}
+ @Test
+ void cancelQueryStopsTheStatementExecStatementIsRunning() throws Exception {
+ when(meta.getIDatabase()).thenReturn(new NoneDatabaseMeta());
+ when(meta.stripCR(anyString())).thenAnswer(invocation ->
invocation.getArgument(0));
+ Statement statement = mock(Statement.class);
+ when(conn.createStatement()).thenReturn(statement);
+ Database db = new Database(log, variables, meta);
+ db.setConnection(conn);
+ // The Stop button calls cancelQuery from another thread while execute()
blocks.
+ when(statement.execute(anyString()))
+ .thenAnswer(
+ invocation -> {
+ db.cancelQuery();
+ return false;
+ });
+
+ db.execStatement("CREATE TABLE big AS SELECT * FROM src");
+
+ verify(statement).cancel();
+ // Once the statement is done, cancelling does not touch it again.
+ db.cancelQuery();
+ verify(statement, times(1)).cancel();
+ }
+
+ @Test
+ void cancelQueryStopsTheStatementExecuteStatementIsRunning() throws
Exception {
+ when(meta.getIDatabase()).thenReturn(new NoneDatabaseMeta());
+ when(meta.stripCR(anyString())).thenAnswer(invocation ->
invocation.getArgument(0));
+ Statement statement = mock(Statement.class);
+ when(conn.createStatement()).thenReturn(statement);
+ Database db = new Database(log, variables, meta);
+ db.setConnection(conn);
+ when(statement.execute(anyString()))
+ .thenAnswer(
+ invocation -> {
+ db.cancelQuery();
+ return false;
+ });
+ when(statement.getUpdateCount()).thenReturn(0);
+
+ db.executeStatement("UPDATE t SET v = 1", 10);
+
+ verify(statement).cancel();
+ }
+
+ @Test
+ void executeStatementReportsTheUpdateCount() throws Exception {
+ ExecutedStatement executed =
+ databaseAffecting(5).executeStatement("UPDATE t SET v = 1 WHERE id <
6", 1000);
+
+ assertFalse(executed.hasRows());
+ assertNull(executed.rowMeta());
+ assertEquals(5, executed.updateCount());
+ assertEquals(5, executed.result().getNrLinesUpdated());
+ }
+
@Test
void execStatementIgnoresStatementsWithoutAffectedRows() throws Exception {
Result result = databaseAffecting(0).execStatement("MERGE INTO t USING s
ON t.id = s.id");
diff --git
a/core/src/test/java/org/apache/hop/core/database/SqlQueryClassifierTest.java
b/core/src/test/java/org/apache/hop/core/database/SqlQueryClassifierTest.java
index 900882ca75..873be4d3ec 100644
---
a/core/src/test/java/org/apache/hop/core/database/SqlQueryClassifierTest.java
+++
b/core/src/test/java/org/apache/hop/core/database/SqlQueryClassifierTest.java
@@ -261,4 +261,15 @@ class SqlQueryClassifierTest {
assertFalse(SqlQueryClassifier.isSchemaChange("ALTER SESSION SET x = 1"));
assertFalse(SqlQueryClassifier.isSchemaChange("DROP INDEX i"));
}
+
+ @Test
+ void rowChangeVerbs() {
+ assertTrue(SqlQueryClassifier.isRowChange("UPDATE t SET v = 1"));
+ assertTrue(SqlQueryClassifier.isRowChange("-- note\ninsert into t values
(1)"));
+ assertTrue(SqlQueryClassifier.isRowChange("WITH s AS (SELECT 1) DELETE
FROM t"));
+ assertTrue(SqlQueryClassifier.isRowChange("MERGE INTO t USING s ON t.id =
s.id"));
+ assertFalse(SqlQueryClassifier.isRowChange("CREATE TABLE t (id INT)"));
+ assertFalse(SqlQueryClassifier.isRowChange("SELECT 1"));
+ assertFalse(SqlQueryClassifier.isRowChange(""));
+ }
}
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/hop-gui/perspective-database.adoc
b/docs/hop-user-manual/modules/ROOT/pages/hop-gui/perspective-database.adoc
index d2f9e8f949..6c3964d450 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/hop-gui/perspective-database.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/hop-gui/perspective-database.adoc
@@ -42,7 +42,7 @@ Toolbar:
* *Refresh* — reload the selected connected connection, or the connection list
* *Edit connection* — open this connection in the Metadata perspective (or a
dialog if that perspective is disabled)
* *Open SQL editor* — new SQL tab for the selected connection (`SELECT * FROM
table` when a table is selected)
-* *Generate DDL* — `CREATE TABLE` for the selected table, opened in a SQL tab
+* *Generate DDL* — `CREATE TABLE` for the selected table, with the schema and
quoted names where the database needs them, opened in a SQL tab
* *Preview table* — run `SELECT` with a dialect-aware row limit (the **Query
row limit** in Configuration → Plugins → Database Perspective, default 1000)
* *Show object information* — columns, indexes, and a **DDL** tab with `CREATE
TABLE` or `CREATE VIEW`. Views use the catalog definition when the database
provides it (`INFORMATION_SCHEMA`, `pg_get_viewdef`, `SHOW CREATE TABLE`, and
similar).
@@ -59,12 +59,12 @@ Each SQL tab is bound to one connection.
* If you have a selection, that selection is executed (all statements in it).
* If you do not, **Run** takes the blank-line-delimited block around the
caret, splits on `;`, and runs the statement at the caret. A leftover clause
after a semicolon (for example `WHERE` on the next line) is not executed; use
**Run all** to send every statement.
* **Select the executed SQL in the editor** (Configuration perspective,
**Plugins → Database Perspective**, on by default) highlights only the
statement(s) that were sent.
-* Results appear in a sash under the editor (one tab per query, plus a
messages tab). Close hides the results panel; the next run shows it again.
**Open in separate window** (left of Close) opens the selected result table in
a separate window.
-* Queries are capped at the **Query row limit** (Configuration perspective,
**Plugins → Database Perspective**, default 1000). That cap is sent to the
driver (`setMaxRows`) as well as applied when reading rows, so a `SELECT *` or
a SQL `LIMIT` above the option still does not pull the whole table. When the
cap is hit, the Messages tab says so. Statement timeout
(`+'${HOP_QUERY_PREVIEW_TIMEOUT}'+`, seconds; `0` is the driver default)
applies to both queries and DDL/DML.
+* Results appear in a sash under the editor (one tab per query, plus a
messages tab). Statements that return rows without being a `SELECT` (`PRAGMA
table_info(t)`, `CALL proc()`, `INSERT ... RETURNING id`) get a result tab too.
For `INSERT`, `UPDATE`, `DELETE` and `MERGE` the messages tab shows the number
of rows affected. Close hides the results panel; the next run shows it again.
**Open in separate window** (left of Close) opens the selected result table in
a separate window.
+* Queries are capped at the **Query row limit** (Configuration perspective,
**Plugins → Database Perspective**, default 1000). That cap is sent to the
driver (`setMaxRows`) as well as applied when reading rows, so a `SELECT *` or
a SQL `LIMIT` above the option still does not pull the whole table. When a
result has more rows than the cap, the Messages tab says so. Statement timeout
(`+'${HOP_QUERY_PREVIEW_TIMEOUT}'+`, seconds; `0` is the driver default)
applies to both queries and DDL/DML.
image::hop-gui/database/database-perspective-panel.png[SQL editor with query
results, Open in separate window, and the operations status line,width="90%"]
-Long-running work (connect, listing, SQL, DDL, table info) is shown as a
one-line status at the bottom of the right-hand side (`description - connection
- Done/Failed - 23 ms`). *Stop* cancels the current operation
(`Statement.cancel`). *Expand* opens the operations list; *Minimize* on that
list returns to the status line.
+Long-running work (connect, listing, SQL, DDL, table info) is shown as a
one-line status at the bottom of the right-hand side (`description - connection
- Done/Failed - 23 ms`). *Stop* cancels the current operation
(`Statement.cancel`), queries as well as DDL and DML. A driver that cannot
interrupt a statement lets it finish; the operation then shows *Done*, because
the statement ran and was committed. *Expand* opens the operations list;
*Minimize* on that list returns to the status line.
Open and save `.sql` files with the normal File menu. Explorer `.sql` files
stay in the File explorer; use the database icon on the content-editor toolbar
to open the buffer in this perspective (you pick the connection).
diff --git
a/ui/src/main/java/org/apache/hop/ui/core/database/dialog/SqlEditor.java
b/ui/src/main/java/org/apache/hop/ui/core/database/dialog/SqlEditor.java
index 4d9cf3f9ed..7f215fa9ca 100644
--- a/ui/src/main/java/org/apache/hop/ui/core/database/dialog/SqlEditor.java
+++ b/ui/src/main/java/org/apache/hop/ui/core/database/dialog/SqlEditor.java
@@ -26,6 +26,8 @@ import org.apache.hop.core.IProgressMonitor;
import org.apache.hop.core.Props;
import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.ExecutedStatement;
+import org.apache.hop.core.database.SqlQueryClassifier;
import org.apache.hop.core.database.SqlScriptStatement;
import org.apache.hop.core.exception.HopDatabaseException;
import org.apache.hop.core.logging.HopLogStore;
@@ -368,7 +370,7 @@ public class SqlEditor {
executeSelectStatement(db, statement, nrStats[0], message, monitor);
} else {
nrStats[0]++;
- aborted = executeDdlStatement(db, statement, databaseMeta, message);
+ aborted = executeDdlStatement(db, statement, nrStats[0], databaseMeta,
message);
}
}
@@ -450,14 +452,50 @@ public class SqlEditor {
dbe));
}
+ private void showReturnedRows(int statNr, ExecutedStatement executed) {
+ if (executed.rows().isEmpty()) {
+ return;
+ }
+ shell
+ .getDisplay()
+ .syncExec(
+ () ->
+ new ShowRowsDialog(
+ shell,
+ variables,
+ BaseMessages.getString(
+ PKG, "SQLEditor.ResultRows.Title",
Integer.toString(statNr)),
+ BaseMessages.getString(PKG,
"SQLEditor.ResultRows.Message"),
+ executed.rowMeta(),
+ executed.rows())
+ .open());
+ }
+
private boolean executeDdlStatement(
- Database db, SqlScriptStatement sql, DatabaseMeta databaseMeta,
StringBuilder message) {
+ Database db,
+ SqlScriptStatement sql,
+ int statNr,
+ DatabaseMeta databaseMeta,
+ StringBuilder message) {
log.logDetailed("launch DDL statement: " + Const.CR + sql);
int startLogLine = HopLogStore.getLastBufferLineNr();
try {
log.logDetailed("Executing SQL: " + Const.CR + sql);
- db.execStatement(sql.getStatement());
- message.append(BaseMessages.getString(PKG, "SQLEditor.Log.SQLExecuted",
sql));
+ // PRAGMA, CALL, INSERT ... RETURNING and the like return rows without
being a SELECT.
+ ExecutedStatement executed = db.executeStatement(sql.getStatement(),
1000);
+ if (executed.hasRows()) {
+ showReturnedRows(statNr, executed);
+ }
+ if (executed.updateCount() >= 0 &&
SqlQueryClassifier.isRowChange(sql.getStatement())) {
+ message.append(
+ BaseMessages.getString(
+ PKG,
+ "SQLEditor.Log.SQLExecutedRows",
+ sql,
+ Integer.toString(executed.updateCount())));
+ } else {
+ message.append(BaseMessages.getString(PKG,
"SQLEditor.Log.SQLExecuted", sql));
+ }
message.append(Const.CR);
if (dbcache != null) {
dbcache.clear(databaseMeta.getName());
diff --git
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperation.java
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperation.java
index bfbc4b7639..2f94e427b0 100644
---
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperation.java
+++
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperation.java
@@ -52,6 +52,7 @@ public class DatabaseOperation {
private volatile long endTime;
private volatile String errorMessage;
private volatile boolean cancelled;
+ private volatile boolean ranToEnd;
private final AtomicReference<Database> database = new AtomicReference<>();
@@ -81,6 +82,15 @@ public class DatabaseOperation {
return cancelled;
}
+ /**
+ * The work finished everything it set out to do, even if a cancel came in
meanwhile. A driver
+ * that cannot interrupt a statement lets it run to the end and commit it,
so the operation is
+ * done rather than cancelled.
+ */
+ public void markRanToEnd() {
+ ranToEnd = true;
+ }
+
public IProgressMonitor newMonitor() {
return new CancelMonitor();
}
@@ -92,7 +102,7 @@ public class DatabaseOperation {
detachDatabase();
return;
}
- status = cancelled ? Status.CANCELLED : Status.DONE;
+ status = cancelled && !ranToEnd ? Status.CANCELLED : Status.DONE;
endTime = System.currentTimeMillis();
detachDatabase();
}
diff --git
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTab.java
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTab.java
index 7aa1efc809..edb1b63154 100644
---
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTab.java
+++
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTab.java
@@ -34,6 +34,8 @@ import org.apache.commons.vfs2.FileObject;
import org.apache.hop.core.Const;
import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.ExecutedStatement;
+import org.apache.hop.core.database.SqlQueryClassifier;
import org.apache.hop.core.database.SqlScriptStatement;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.gui.plugin.GuiPlugin;
@@ -459,31 +461,44 @@ public class DatabaseSqlEditorTab implements
IHopFileTypeHandler {
db.setQueryLimit(rowLimit);
db.connect();
int nr = 0;
+ // One row more than the limit tells a result that fills the limit
exactly apart from
+ // one that was cut off.
+ int readLimit = rowLimit > 0 && rowLimit < Integer.MAX_VALUE ?
rowLimit + 1 : rowLimit;
+ boolean stopped = false;
for (SqlScriptStatement sql : statements) {
if (operation.isCancelled()) {
messages
.append(BaseMessages.getString(PKG,
"DatabasePerspective.SqlTab.Cancelled"))
.append(Const.CR);
+ stopped = true;
break;
}
nr++;
if (sql.isQuery()) {
- List<Object[]> rows = db.getRows(sql.getStatement(), rowLimit);
+ List<Object[]> rows = db.getRows(sql.getStatement(),
readLimit);
if (rows == null) {
rows = List.of();
}
IRowMeta rowMeta = db.getReturnRowMeta();
- queryResults.add(new DatabaseResultsPanel.QueryResult(nr,
rowMeta, rows));
- messages.append(queryResultMessage(nr, rows.size(),
rowLimit)).append(Const.CR);
+ addQueryResult(nr, rowMeta, rows, rowLimit, queryResults,
messages);
} else {
- db.execStatement(sql.getStatement());
- messages
- .append(
- BaseMessages.getString(
- PKG, "DatabasePerspective.SqlTab.Executed",
sql.getStatement().trim()))
- .append(Const.CR);
+ // Not every statement that returns rows starts with SELECT:
PRAGMA, CALL,
+ // INSERT ... RETURNING, ... Show what comes back instead of
discarding it.
+ ExecutedStatement executed =
db.executeStatement(sql.getStatement(), readLimit);
+ if (executed.hasRows()) {
+ addQueryResult(
+ nr, executed.rowMeta(), executed.rows(), rowLimit,
queryResults, messages);
+ } else {
+ messages
+ .append(executedMessage(sql.getStatement(),
executed.updateCount()))
+ .append(Const.CR);
+ }
}
}
+ if (!stopped) {
+ // A driver that cannot interrupt a statement runs it to the end
and commits it.
+ operation.markRanToEnd();
+ }
} catch (Exception e) {
// A bad statement is a result, not a GUI failure. Rethrowing it
makes runOperation log
// the stack, and on Hop Web that takes the session down (issue
#9136).
@@ -580,8 +595,44 @@ public class DatabaseSqlEditorTab implements
IHopFileTypeHandler {
return false;
}
- static String queryResultMessage(int statementNr, int rowCount, int
rowLimit) {
- if (rowLimit > 0 && rowCount >= rowLimit) {
+ static void addQueryResult(
+ int statementNr,
+ IRowMeta rowMeta,
+ List<Object[]> rows,
+ int rowLimit,
+ List<DatabaseResultsPanel.QueryResult> queryResults,
+ StringBuilder messages) {
+ boolean capped = rowLimit > 0 && rows.size() > rowLimit;
+ List<Object[]> shown = capped ? rows.subList(0, rowLimit) : rows;
+ queryResults.add(new DatabaseResultsPanel.QueryResult(statementNr,
rowMeta, shown));
+ messages
+ .append(queryResultMessage(statementNr, shown.size(), rowLimit,
capped))
+ .append(Const.CR);
+ }
+
+ /**
+ * @param statement the statement as written
+ * @param updateCount the JDBC update count, negative when there is none
+ * @return {@code OK: <statement>}, with the number of rows affected for
DML. The count of other
+ * statements is not shown: some drivers repeat the count of the
previous statement.
+ */
+ static String executedMessage(String statement, int updateCount) {
+ String text = statement.trim();
+ if (updateCount >= 0 && SqlQueryClassifier.isRowChange(statement)) {
+ return BaseMessages.getString(
+ PKG, "DatabasePerspective.SqlTab.ExecutedRows", text,
Integer.toString(updateCount));
+ }
+ return BaseMessages.getString(PKG, "DatabasePerspective.SqlTab.Executed",
text);
+ }
+
+ /**
+ * @param statementNr the number of the statement in the script
+ * @param rowCount the number of rows shown
+ * @param rowLimit the perspective's row limit
+ * @param capped {@code true} when the result had more rows than the limit
+ */
+ static String queryResultMessage(int statementNr, int rowCount, int
rowLimit, boolean capped) {
+ if (capped) {
return BaseMessages.getString(
PKG,
"DatabasePerspective.SqlTab.QueryRowsLimited",
diff --git
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseWorkbench.java
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseWorkbench.java
index 09695ab4a4..9ea22e65f3 100644
---
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseWorkbench.java
+++
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseWorkbench.java
@@ -1106,7 +1106,9 @@ public class DatabaseWorkbench extends Composite
implements TabClosable {
operation.attachDatabase(db);
db.connect();
IRowMeta fields = db.getTableFields(qualified);
- ddl = db.getCreateTableStatement(node.getObjectName(), fields,
null, false, null, true);
+ // getTableFields hands out a copy of the cached layout, so
quoting it is safe.
+ meta.quoteReservedWords(fields);
+ ddl = db.getCreateTableStatement(qualified, fields, null, false,
null, true);
}
String sql = ddl;
host.asyncExec(() -> openGeneratedSql(meta, sql));
diff --git
a/ui/src/main/resources/org/apache/hop/ui/core/database/dialog/messages/messages_en_US.properties
b/ui/src/main/resources/org/apache/hop/ui/core/database/dialog/messages/messages_en_US.properties
index 1d495dec36..7ac27935c9 100644
---
a/ui/src/main/resources/org/apache/hop/ui/core/database/dialog/messages/messages_en_US.properties
+++
b/ui/src/main/resources/org/apache/hop/ui/core/database/dialog/messages/messages_en_US.properties
@@ -83,6 +83,7 @@ SQLEditor.LineNr.Label=Line number: {0}
SQLEditor.Log.SQLExecError=Error while executing: {0}\n{1}
SQLEditor.Monitor.Execute=Executing SQL statements...
SQLEditor.Log.SQLExecuted=SQL executed: {0}\n
+SQLEditor.Log.SQLExecutedRows=SQL executed, {1} row(s) affected: {0}\n
SQLEditor.Log.StatsExecuted={0} SQL statements executed
SQLEditor.NoRows.Message=No results found for SQL statement:\n\n{0}
SQLEditor.NoRows.Title=No result
diff --git
a/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
b/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
index bd3583dda7..2d3ddcc179 100644
---
a/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
+++
b/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
@@ -53,6 +53,7 @@ DatabasePerspective.SqlTab.Failed=Failed
DatabasePerspective.SqlTab.QueryRows=Query {0}: {1} row(s)
DatabasePerspective.SqlTab.QueryRowsLimited=Query {0}: {1} row(s). Results are
capped at {2} rows by the Database perspective (a SQL LIMIT above this value is
ignored). Change the query row limit under Configuration > Plugins > Database
Perspective.
DatabasePerspective.SqlTab.Executed=OK: {0}
+DatabasePerspective.SqlTab.ExecutedRows=OK, {1} row(s) affected: {0}
DatabasePerspective.Results.Title=Results
DatabasePerspective.Results.Close=Close
DatabasePerspective.Results.Close.Tooltip=Hide the results panel
diff --git
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperationsPanelTest.java
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperationsPanelTest.java
index f10f4e0bd8..0562204d71 100644
---
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperationsPanelTest.java
+++
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperationsPanelTest.java
@@ -87,6 +87,24 @@ class DatabaseOperationsPanelTest {
assertEquals(DatabaseOperation.Status.CANCELLED, operation.getStatus());
}
+ @Test
+ void completeOfARunThatFinishedDespiteTheCancelIsDone() {
+ // The driver could not stop the statement: it ran to the end and was
committed.
+ DatabaseOperation operation = new DatabaseOperation("Execute SQL on CRM",
"CRM");
+ operation.cancel();
+ operation.markRanToEnd();
+ operation.complete();
+ assertEquals(DatabaseOperation.Status.DONE, operation.getStatus());
+ }
+
+ @Test
+ void failOfACancelledStatementIsCancelled() {
+ DatabaseOperation operation = new DatabaseOperation("Execute SQL on CRM",
"CRM");
+ operation.cancel();
+ operation.fail("Statement was canceled");
+ assertEquals(DatabaseOperation.Status.CANCELLED, operation.getStatus());
+ }
+
@Test
void failDetachesDatabase() {
DatabaseOperation operation = new DatabaseOperation("Connect", "shop");
diff --git
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTabTest.java
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTabTest.java
index 584f38ba55..c2c1ee0551 100644
---
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTabTest.java
+++
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTabTest.java
@@ -21,19 +21,22 @@ import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertTrue;
+import java.util.ArrayList;
+import java.util.List;
import org.apache.hop.core.exception.HopDatabaseException;
+import org.apache.hop.core.row.RowMeta;
import org.junit.jupiter.api.Test;
class DatabaseSqlEditorTabTest {
@Test
void queryResultMessageUnderTheCap() {
- assertEquals("Query 1: 12 row(s)",
DatabaseSqlEditorTab.queryResultMessage(1, 12, 1000));
+ assertEquals("Query 1: 12 row(s)",
DatabaseSqlEditorTab.queryResultMessage(1, 12, 1000, false));
}
@Test
- void queryResultMessageAtTheCapExplainsTheLimit() {
- String message = DatabaseSqlEditorTab.queryResultMessage(1, 1000, 1000);
+ void queryResultMessageAboveTheCapExplainsTheLimit() {
+ String message = DatabaseSqlEditorTab.queryResultMessage(1, 1000, 1000,
true);
assertTrue(message.startsWith("Query 1: 1000 row(s)"));
assertTrue(message.contains("capped at 1000"));
assertTrue(message.contains("Database Perspective"));
@@ -42,11 +45,68 @@ class DatabaseSqlEditorTabTest {
@Test
void queryResultMessageBelowCapDoesNotMentionTheOption() {
- String message = DatabaseSqlEditorTab.queryResultMessage(2, 50, 2000);
+ String message = DatabaseSqlEditorTab.queryResultMessage(2, 50, 2000,
false);
assertEquals("Query 2: 50 row(s)", message);
assertFalse(message.contains("capped"));
}
+ @Test
+ void resultOfExactlyTheLimitIsNotCapped() {
+ List<DatabaseResultsPanel.QueryResult> results = new ArrayList<>();
+ StringBuilder messages = new StringBuilder();
+
+ DatabaseSqlEditorTab.addQueryResult(1, new RowMeta(), rows(3), 3, results,
messages);
+
+ assertEquals(3, results.get(0).getRows().size());
+ assertFalse(messages.toString().contains("capped"));
+ }
+
+ @Test
+ void resultAboveTheLimitIsCutAndSaysSo() {
+ List<DatabaseResultsPanel.QueryResult> results = new ArrayList<>();
+ StringBuilder messages = new StringBuilder();
+
+ // The editor reads one row more than the limit to tell the two cases
apart.
+ DatabaseSqlEditorTab.addQueryResult(1, new RowMeta(), rows(4), 3, results,
messages);
+
+ assertEquals(3, results.get(0).getRows().size());
+ assertTrue(messages.toString().startsWith("Query 1: 3 row(s)"));
+ assertTrue(messages.toString().contains("capped at 3"));
+ }
+
+ @Test
+ void executedMessageReportsAffectedRowsOfDml() {
+ assertEquals(
+ "OK, 5 row(s) affected: UPDATE t SET v = 1",
+ DatabaseSqlEditorTab.executedMessage("UPDATE t SET v = 1\n", 5));
+ assertEquals(
+ "OK, 0 row(s) affected: DELETE FROM t WHERE 1 = 0",
+ DatabaseSqlEditorTab.executedMessage("DELETE FROM t WHERE 1 = 0", 0));
+ assertEquals(
+ "OK, 2 row(s) affected: WITH s AS (SELECT 1) DELETE FROM t",
+ DatabaseSqlEditorTab.executedMessage("WITH s AS (SELECT 1) DELETE FROM
t", 2));
+ }
+
+ @Test
+ void executedMessageLeavesDdlAlone() {
+ assertEquals(
+ "OK: CREATE TABLE t (id INT)",
+ DatabaseSqlEditorTab.executedMessage("CREATE TABLE t (id INT)", 0));
+ assertEquals("OK: SET SCHEMA s", DatabaseSqlEditorTab.executedMessage("SET
SCHEMA s", -1));
+ // sqlite-jdbc repeats the count of the previous INSERT after a CREATE
TABLE.
+ assertEquals(
+ "OK: CREATE TABLE u (x INT)",
+ DatabaseSqlEditorTab.executedMessage("CREATE TABLE u (x INT)", 1));
+ }
+
+ private static List<Object[]> rows(int count) {
+ List<Object[]> rows = new ArrayList<>();
+ for (int i = 0; i < count; i++) {
+ rows.add(new Object[] {(long) i});
+ }
+ return rows;
+ }
+
@Test
void restoredOrLoadedTextIsNotAUserEdit() {
assertFalse(DatabaseSqlEditorTab.isModifiedFromClean("SELECT 1", "SELECT
1"));