This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/master by this push:
new 9a0817482d HOP-3904 Clickhouse: wrong SQL statement when deleting rows
new 61d31632e7 Merge pull request #1533 from sramazzina/HOP-3904
9a0817482d is described below
commit 9a0817482d48f1735fed90cedc1d19acf540ddf7
Author: Sergio Ramazzina <[email protected]>
AuthorDate: Wed Jun 15 13:58:02 2022 +0200
HOP-3904 Clickhouse: wrong SQL statement when deleting rows
---
.../apache/hop/core/database/BaseDatabaseMeta.java | 34 ++++++++++++++++++++++
.../org/apache/hop/core/database/Database.java | 19 +++++++-----
.../org/apache/hop/core/database/DatabaseMeta.java | 30 +++++++++++++++++++
.../org/apache/hop/core/database/IDatabase.java | 18 ++++++++++++
.../clickhouse/ClickhouseDatabaseMeta.java | 32 ++++++++++++++++++++
5 files changed, 125 insertions(+), 8 deletions(-)
diff --git
a/core/src/main/java/org/apache/hop/core/database/BaseDatabaseMeta.java
b/core/src/main/java/org/apache/hop/core/database/BaseDatabaseMeta.java
index 493bae55aa..e17b159ff2 100644
--- a/core/src/main/java/org/apache/hop/core/database/BaseDatabaseMeta.java
+++ b/core/src/main/java/org/apache/hop/core/database/BaseDatabaseMeta.java
@@ -405,6 +405,40 @@ public abstract class BaseDatabaseMeta implements
Cloneable, IDatabase {
return true;
}
+ /* Returns weather or not the database supports a custom SQL statement to
perform delete operations */
+ @Override
+ public boolean isSupportsCustomDeleteStmt() {
+ return false;
+ }
+
+ /* Returns weather or not the database supports a custom SQL statement to
perform update operations */
+ @Override
+ public boolean isSupportsCustomUpdateStmt() {
+ return false;
+ }
+
+ /**
+ * Get the DELETE statement for the current database given the table name
+ *
+ * @param tableName
+ * @return
+ */
+ @Override
+ public String getSqlDeleteStmt(String tableName) {
+ return "DELETE FROM " + tableName;
+ }
+
+ /**
+ * Get the UPDATE statement for the current database given the table name
+ *
+ * @param tableName
+ * @return
+ */
+ @Override
+ public String getSqlUpdateStmt(String tableName) {
+ return "UPDATE " + tableName + Const.CR + "SET ";
+ }
+
@Override
public String getLimitClause(int nrRows) {
return "";
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 368335988e..5931dfe829 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
@@ -1291,9 +1291,13 @@ public class Database implements IVariables,
ILoggingObject {
// You should have called something else!
if (upperSql.startsWith("INSERT")) {
result.setNrLinesOutput(count);
- } else if (upperSql.startsWith("UPDATE")) {
+ } else if (!databaseMeta.isSupportsCustomUpdateStmt() &&
upperSql.startsWith("UPDATE")) {
result.setNrLinesUpdated(count);
- } else if (upperSql.startsWith("DELETE")) {
+ } else if (databaseMeta.isSupportsCustomUpdateStmt() &&
upperSql.contains("UPDATE")) {
+ result.setNrLinesUpdated(count);
+ } else if (!databaseMeta.isSupportsCustomDeleteStmt() &&
upperSql.startsWith("DELETE")) {
+ result.setNrLinesDeleted(count);
+ } else if (databaseMeta.isSupportsCustomDeleteStmt() &&
upperSql.contains("DELETE")) {
result.setNrLinesDeleted(count);
}
}
@@ -2523,7 +2527,7 @@ public class Database implements IVariables,
ILoggingObject {
String schemaTable =
databaseMeta.getQuotedSchemaTableCombination(this, schemaName,
tableName);
- sql.append("UPDATE ").append(schemaTable).append(Const.CR).append("SET
");
+ sql.append(databaseMeta.getSqlUpdateStmt(schemaTable));
for (int i = 0; i < sets.length; i++) {
if (i != 0) {
@@ -2596,7 +2600,7 @@ public class Database implements IVariables,
ILoggingObject {
String sql;
String table = databaseMeta.getQuotedSchemaTableCombination(this,
schemaName, tableName);
- sql = "DELETE FROM " + table + Const.CR;
+ sql = databaseMeta.getSqlDeleteStmt(table) + Const.CR;
sql += "WHERE ";
for (int i = 0; i < codes.length; i++) {
@@ -2992,7 +2996,7 @@ public class Database implements IVariables,
ILoggingObject {
}
execStatement(truncateStatement);
} else {
- execStatement("DELETE FROM " + databaseMeta.quoteField(tableName));
+
execStatement(databaseMeta.getSqlDeleteStmt(databaseMeta.quoteField(tableName)));
}
}
@@ -3006,7 +3010,7 @@ public class Database implements IVariables,
ILoggingObject {
execStatement(truncateStatement);
} else {
execStatement(
- "DELETE FROM " + databaseMeta.getQuotedSchemaTableCombination(this,
schema, tableName));
+
databaseMeta.getSqlDeleteStmt(databaseMeta.getQuotedSchemaTableCombination(this,
schema, tableName)));
}
}
@@ -4235,8 +4239,7 @@ public class Database implements IVariables,
ILoggingObject {
}
return truncateStatement;
} else {
- return ("DELETE FROM "
- + databaseMeta.getQuotedSchemaTableCombination(this, schema,
tableName));
+ return
(databaseMeta.getSqlDeleteStmt(databaseMeta.getQuotedSchemaTableCombination(this,
schema, tableName)));
}
}
diff --git a/core/src/main/java/org/apache/hop/core/database/DatabaseMeta.java
b/core/src/main/java/org/apache/hop/core/database/DatabaseMeta.java
index 8b089e6255..1ffb2568d1 100644
--- a/core/src/main/java/org/apache/hop/core/database/DatabaseMeta.java
+++ b/core/src/main/java/org/apache/hop/core/database/DatabaseMeta.java
@@ -1848,6 +1848,36 @@ public class DatabaseMeta extends HopMetadataBase
implements Cloneable, IHopMeta
return iDatabase.getSqlColumnExists(columnname, tableName);
}
+ /**
+ * Get the DELETE statement for the current database given the table name
+ *
+ * @param tableName
+ * @return
+ */
+ public String getSqlDeleteStmt(String tableName) {
+ return iDatabase.getSqlDeleteStmt(tableName);
+ }
+
+ /**
+ * Get the UPDATE statement for the current database given the table name
+ *
+ * @param tableName
+ * @return
+ */
+ public String getSqlUpdateStmt(String tableName) {
+ return iDatabase.getSqlUpdateStmt(tableName);
+ }
+
+ /* Returns weather or not the database supports a custom SQL statement to
perform delete operations */
+ public boolean isSupportsCustomDeleteStmt() {
+ return iDatabase.isSupportsCustomDeleteStmt();
+ }
+
+ /* Returns weather or not the database supports a custom SQL statement to
perform update operations */
+ public boolean isSupportsCustomUpdateStmt() {
+ return iDatabase.isSupportsCustomUpdateStmt();
+ }
+
/**
* @return true if the database is streaming results (normally this is an
option just for MySQL).
*/
diff --git a/core/src/main/java/org/apache/hop/core/database/IDatabase.java
b/core/src/main/java/org/apache/hop/core/database/IDatabase.java
index 47b4129516..69278afa92 100644
--- a/core/src/main/java/org/apache/hop/core/database/IDatabase.java
+++ b/core/src/main/java/org/apache/hop/core/database/IDatabase.java
@@ -143,6 +143,11 @@ public interface IDatabase extends Cloneable {
/** @return Whether or not the database can use auto increment type of
fields (pk) */
boolean isSupportsAutoInc();
+ /* Returns weather or not the database supports a custom SQL statement to
perform delete operations */
+ boolean isSupportsCustomDeleteStmt();
+
+ /* Returns weather or not the database supports a custom SQL statement to
perform update operations */
+ boolean isSupportsCustomUpdateStmt();
/**
* Describe a Value as a field in the database.
*
@@ -861,6 +866,19 @@ public interface IDatabase extends Cloneable {
String getSqlValue(IValueMeta valueMeta, Object valueData, String dateFormat)
throws HopValueException;
+ /**
+ * Get the DELETE statement for the current database given the table name
+ * @param tableName
+ * @return
+ */
+ String getSqlDeleteStmt(String tableName);
+
+ /**
+ * Get the UPDATE statement for the current database given the table name
+ * @param tableName
+ * @return
+ */
+ String getSqlUpdateStmt(String tableName);
/**
* @return true if this database only supports metadata retrieval on a
result set, never on a
* statement (even if the statement has been executed)
diff --git
a/plugins/databases/clickhouse/src/main/java/org/apache/hop/databases/clickhouse/ClickhouseDatabaseMeta.java
b/plugins/databases/clickhouse/src/main/java/org/apache/hop/databases/clickhouse/ClickhouseDatabaseMeta.java
index b66198f93e..e4a664e9a5 100644
---
a/plugins/databases/clickhouse/src/main/java/org/apache/hop/databases/clickhouse/ClickhouseDatabaseMeta.java
+++
b/plugins/databases/clickhouse/src/main/java/org/apache/hop/databases/clickhouse/ClickhouseDatabaseMeta.java
@@ -56,6 +56,38 @@ public class ClickhouseDatabaseMeta extends BaseDatabaseMeta
implements IDatabas
return "com.clickhouse.jdbc.ClickHouseDriver";
}
+ @Override
+ public boolean isSupportsCustomDeleteStmt() {
+ return true;
+ }
+
+ @Override
+ public boolean isSupportsCustomUpdateStmt() {
+ return true;
+ }
+
+ /**
+ * Get the DELETE statement for the current database given the table name
+ *
+ * @param tableName
+ * @return
+ */
+ @Override
+ public String getSqlDeleteStmt(String tableName) {
+ return "ALTER TABLE " + tableName + " DELETE ";
+ }
+
+ /**
+ * Get the UPDATE statement for the current database given the table name
+ *
+ * @param tableName
+ * @return
+ */
+ @Override
+ public String getSqlUpdateStmt(String tableName) {
+ return "ALTER TABLE " + tableName + " UPDATE ";
+ }
+
@Override
public String getURL(String hostName, String port, String databaseName) {
