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 2ed2e47ddf Issues #6626 : table compare CTE improvements (#6629)
2ed2e47ddf is described below
commit 2ed2e47ddf0f90ca7764a93586f253013e5d509a
Author: Matt Casters <[email protected]>
AuthorDate: Mon Feb 23 09:05:54 2026 +0100
Issues #6626 : table compare CTE improvements (#6629)
Co-authored-by: Matt Casters <[email protected]>
---
.../pages/pipeline/transforms/tablecompare.adoc | 4 ++
.../transforms/tablecompare/TableCompare.java | 78 +++++++++++++++++-----
.../transforms/tablecompare/TableCompareData.java | 2 +
.../tablecompare/TableCompareDialog.java | 34 ++++++++++
.../transforms/tablecompare/TableCompareMeta.java | 12 ++++
.../messages/messages_en_US.properties | 6 ++
6 files changed, 118 insertions(+), 18 deletions(-)
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/tablecompare.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/tablecompare.adoc
index 6eeb781333..ca686f8689 100644
---
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/tablecompare.adoc
+++
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/tablecompare.adoc
@@ -52,6 +52,10 @@ TIP: You can click on the red error handling arrow to
configure additional infor
|Reference schema field / Compare schema field|contain the schema names for
the reference/compare table.
|Reference table field / Compare table field|contain the actual table names.
This means that you could compare two tables with a different name, as long as
they have the same column names.
+|Reference CTE field / Compare CTE field|Specify the Common Table Expression
to use with the table queries to compare values.
+This CTE clause will be pre-pended to the generated SQL.
+You can specify multiple CTEs but the one being read from must be called
`_<table name>`
+(with an underscore in front of the table name in other words).
|===
=== Other fields tab
diff --git
a/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompare.java
b/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompare.java
index 25e0969246..e19145988c 100644
---
a/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompare.java
+++
b/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompare.java
@@ -18,6 +18,7 @@
package org.apache.hop.pipeline.transforms.tablecompare;
import java.sql.ResultSet;
+import org.apache.commons.lang.StringUtils;
import org.apache.hop.core.Const;
import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
@@ -95,6 +96,8 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
CONST_TABLE_COMPARE_EXCEPTION_CAN_NOT_FIND_FIELD,
meta.getReferenceTableField()));
}
+ // Optional CTE
+ data.refCteIndex =
getInputRowMeta().indexOfValue(meta.getReferenceCteField());
// Compare schema
//
@@ -125,6 +128,8 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
CONST_TABLE_COMPARE_EXCEPTION_CAN_NOT_FIND_FIELD,
meta.getCompareTableField()));
}
+ // Optional CTE
+ data.cmpCteIndex =
getInputRowMeta().indexOfValue(meta.getCompareCteField());
// Key fields
//
@@ -210,8 +215,17 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
try {
String referenceSchema = getInputRowMeta().getString(r,
data.refSchemaIndex);
String referenceTable = getInputRowMeta().getString(r,
data.refTableIndex);
+
+ String referenceCte = "";
+ if (data.refCteIndex >= 0) {
+ referenceCte = getInputRowMeta().getString(r, data.refCteIndex);
+ }
String compareSchema = getInputRowMeta().getString(r,
data.cmpSchemaIndex);
String compareTable = getInputRowMeta().getString(r, data.cmpTableIndex);
+ String compareCte = "";
+ if (data.cmpCteIndex >= 0) {
+ compareCte = getInputRowMeta().getString(r, data.cmpCteIndex);
+ }
String keyFields = getInputRowMeta().getString(r, data.keyFieldsIndex);
String excludeFields = getInputRowMeta().getString(r,
data.excludeFieldsIndex);
@@ -220,8 +234,10 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
r,
referenceSchema,
referenceTable,
+ referenceCte,
compareSchema,
compareTable,
+ compareCte,
keyFields,
excludeFields);
} catch (Exception e) {
@@ -235,8 +251,10 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
Object[] r,
String referenceSchema,
String referenceTable,
+ String referenceCte,
String compareSchema,
String compareTable,
+ String compareCte,
String keyFields,
String excludeFields)
throws HopException {
@@ -315,7 +333,9 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
try {
IRowMeta refFields =
data.referenceDb.getTableFieldsMeta(referenceSchema, referenceTable);
+ IRowMeta allRefFields = refFields.clone();
IRowMeta cmpFields = data.compareDb.getTableFieldsMeta(compareSchema,
compareTable);
+ IRowMeta allCmpFields = cmpFields.clone();
// Remove the excluded fields from these fields...
//
@@ -350,7 +370,7 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
// See if all the key fields exist in the reference & compare tables...
//
for (String key : keys) {
- if (refFields.indexOfValue(key) < 0) {
+ if (allRefFields.indexOfValue(key) < 0) {
if (getTransformMeta().isDoingErrorHandling()) {
Object[] errorRowData = constructErrorRow(rowMeta, r, null,
null, null);
putError(
@@ -369,7 +389,7 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
}
}
for (String key : keys) {
- if (cmpFields.indexOfValue(key) < 0) {
+ if (allCmpFields.indexOfValue(key) < 0) {
if (getTransformMeta().isDoingErrorHandling()) {
Object[] errorRowData = constructErrorRow(rowMeta, r, null,
null, null);
putError(
@@ -403,10 +423,18 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
int[] keyNrs = new int[keys.length];
+ // The reference SQL
StringBuilder refSql = new StringBuilder();
+ if (StringUtils.isNotEmpty(referenceCte)) {
+ refSql.append(referenceCte);
+ }
refSql.append("SELECT ");
- StringBuilder cmpSql = new StringBuilder();
+ // The compare SQL
+ StringBuilder cmpSql = new StringBuilder();
+ if (StringUtils.isNotEmpty(compareCte)) {
+ cmpSql.append(compareCte);
+ }
cmpSql.append("SELECT ");
for (int i = 0; i < keys.length; i++) {
if (i > 0) {
@@ -417,25 +445,39 @@ public class TableCompare extends
BaseTransform<TableCompareMeta, TableCompareDa
refSql.append(refConnectionDatabaseMeta.quoteField(keys[i]));
cmpSql.append(refConnectionDatabaseMeta.quoteField(keys[i]));
}
- int[] valueNrs = new int[refFields.size() - keys.length];
- int valueNr = keys.length;
- int valueIndex = 0;
- for (int i = 0; i < refFields.getFieldNames().length; i++) {
- String field = refFields.getFieldNames()[i];
- if (Const.indexOfString(field, keys) < 0) {
- refSql.append(",
").append(refConnectionDatabaseMeta.quoteField(field));
- valueRowMeta.addValueMeta(refFields.searchValueMeta(field));
- valueNrs[valueIndex++] = valueNr++;
+
+ // We only compare values if there are any to compare
+ //
+ int[] valueNrs = new int[0];
+ if (refFields.size() - keys.length > 0) {
+ valueNrs = new int[refFields.size() - keys.length];
+ int valueNr = keys.length;
+ int valueIndex = 0;
+ for (int i = 0; i < refFields.getFieldNames().length; i++) {
+ String field = refFields.getFieldNames()[i];
+ if (Const.indexOfString(field, keys) < 0) {
+ refSql.append(",
").append(refConnectionDatabaseMeta.quoteField(field));
+ valueRowMeta.addValueMeta(refFields.searchValueMeta(field));
+ valueNrs[valueIndex++] = valueNr++;
+ }
}
- }
- for (String field : cmpFields.getFieldNames()) {
- if (Const.indexOfString(field, keys) < 0) {
- cmpSql.append(",
").append(compConnectionDatabaseMeta.quoteField(field));
+ for (String field : cmpFields.getFieldNames()) {
+ if (Const.indexOfString(field, keys) < 0) {
+ cmpSql.append(",
").append(compConnectionDatabaseMeta.quoteField(field));
+ }
}
}
- refSql.append(" FROM ").append(refSchemaTable).append(" ORDER BY ");
- cmpSql.append(" FROM ").append(cmpSchemaTable).append(" ORDER BY ");
+ refSql.append(" FROM ");
+ if (StringUtils.isNotEmpty(referenceCte)) {
+ refSql.append("_");
+ }
+ refSql.append(refSchemaTable).append(" ORDER BY ");
+ cmpSql.append(" FROM ");
+ if (StringUtils.isNotEmpty(compareCte)) {
+ cmpSql.append("_");
+ }
+ cmpSql.append(cmpSchemaTable).append(" ORDER BY ");
for (int i = 0; i < keys.length; i++) {
if (i > 0) {
refSql.append(", ");
diff --git
a/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareData.java
b/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareData.java
index 37d3ea91df..c17833128d 100644
---
a/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareData.java
+++
b/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareData.java
@@ -30,8 +30,10 @@ public class TableCompareData extends BaseTransformData
implements ITransformDat
public int refSchemaIndex;
public int refTableIndex;
+ public int refCteIndex;
public int cmpSchemaIndex;
public int cmpTableIndex;
+ public int cmpCteIndex;
public int keyFieldsIndex;
public int excludeFieldsIndex;
diff --git
a/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareDialog.java
b/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareDialog.java
index 3c6090e62d..36584e367b 100644
---
a/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareDialog.java
+++
b/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareDialog.java
@@ -62,10 +62,12 @@ public class TableCompareDialog extends BaseTransformDialog
{
private MetaSelectionLine<DatabaseMeta> wReferenceDB;
private LabelCombo wReferenceSchema;
private LabelCombo wReferenceTable;
+ private LabelCombo wReferenceCte;
private MetaSelectionLine<DatabaseMeta> wCompareDB;
private LabelCombo wCompareSchema;
private LabelCombo wCompareTable;
+ private LabelCombo wCompareCte;
private LabelCombo wKeyFields;
private LabelCombo wExcludeFields;
@@ -166,6 +168,19 @@ public class TableCompareDialog extends
BaseTransformDialog {
fdReferenceTable.top = new FormAttachment(lastControl, margin);
fdReferenceTable.right = new FormAttachment(100, 0);
wReferenceTable.setLayoutData(fdReferenceTable);
+ lastControl = wReferenceTable;
+
+ wReferenceCte =
+ new LabelCombo(
+ wReferenceComp,
+ BaseMessages.getString(PKG,
"TableCompareDialog.ReferenceCteField.Label"),
+ BaseMessages.getString(PKG,
"TableCompareDialog.ReferenceCteField.Tooltip"));
+ PropsUi.setLook(wReferenceCte);
+ FormData fdReferenceCte = new FormData();
+ fdReferenceCte.left = new FormAttachment(0, 0);
+ fdReferenceCte.top = new FormAttachment(lastControl, margin);
+ fdReferenceCte.right = new FormAttachment(100, 0);
+ wReferenceCte.setLayoutData(fdReferenceCte);
FormData fdReferenceComp = new FormData();
fdReferenceComp.left = new FormAttachment(0, 0);
@@ -236,6 +251,19 @@ public class TableCompareDialog extends
BaseTransformDialog {
fdCompareTable.top = new FormAttachment(lastControl, margin);
fdCompareTable.right = new FormAttachment(100, 0);
wCompareTable.setLayoutData(fdCompareTable);
+ lastControl = wCompareTable;
+
+ wCompareCte =
+ new LabelCombo(
+ wComparisonComp,
+ BaseMessages.getString(PKG,
"TableCompareDialog.CompareCteField.Label"),
+ BaseMessages.getString(PKG,
"TableCompareDialog.CompareCteField.Tooltip"));
+ PropsUi.setLook(wCompareCte);
+ FormData fdCompareCte = new FormData();
+ fdCompareCte.left = new FormAttachment(0, 0);
+ fdCompareCte.top = new FormAttachment(lastControl, margin);
+ fdCompareCte.right = new FormAttachment(100, 0);
+ wCompareCte.setLayoutData(fdCompareCte);
FormData fdComparisonComp = new FormData();
fdComparisonComp.left = new FormAttachment(0, 0);
@@ -521,8 +549,10 @@ public class TableCompareDialog extends
BaseTransformDialog {
wReferenceSchema.setItems(prevTransformFieldNames);
wReferenceTable.setItems(prevTransformFieldNames);
+ wReferenceCte.setItems(prevTransformFieldNames);
wCompareSchema.setItems(prevTransformFieldNames);
wCompareTable.setItems(prevTransformFieldNames);
+ wCompareCte.setItems(prevTransformFieldNames);
wKeyFields.setItems(prevTransformFieldNames);
wExcludeFields.setItems(prevTransformFieldNames);
wKeyDesc.setItems(prevTransformFieldNames);
@@ -541,9 +571,11 @@ public class TableCompareDialog extends
BaseTransformDialog {
input.getReferenceConnection() != null ?
input.getReferenceConnection() : "");
wReferenceSchema.setText(Const.NVL(input.getReferenceSchemaField(), ""));
wReferenceTable.setText(Const.NVL(input.getReferenceTableField(), ""));
+ wReferenceCte.setText(Const.NVL(input.getReferenceCteField(), ""));
wCompareDB.setText(input.getCompareConnection() != null ?
input.getCompareConnection() : "");
wCompareSchema.setText(Const.NVL(input.getCompareSchemaField(), ""));
wCompareTable.setText(Const.NVL(input.getCompareTableField(), ""));
+ wCompareCte.setText(Const.NVL(input.getCompareCteField(), ""));
wKeyFields.setText(Const.NVL(input.getKeyFieldsField(), ""));
wExcludeFields.setText(Const.NVL(input.getExcludeFieldsField(), ""));
@@ -576,9 +608,11 @@ public class TableCompareDialog extends
BaseTransformDialog {
input.setReferenceConnection(wReferenceDB.getText());
input.setReferenceSchemaField(wReferenceSchema.getText());
input.setReferenceTableField(wReferenceTable.getText());
+ input.setReferenceCteField(wReferenceCte.getText());
input.setCompareConnection(wCompareDB.getText());
input.setCompareSchemaField(wCompareSchema.getText());
input.setCompareTableField(wCompareTable.getText());
+ input.setCompareCteField(wCompareCte.getText());
input.setKeyFieldsField(wKeyFields.getText());
input.setExcludeFieldsField(wExcludeFields.getText());
diff --git
a/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareMeta.java
b/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareMeta.java
index 4e374f1983..fd44271bbc 100644
---
a/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareMeta.java
+++
b/plugins/transforms/tablecompare/src/main/java/org/apache/hop/pipeline/transforms/tablecompare/TableCompareMeta.java
@@ -70,6 +70,12 @@ public class TableCompareMeta extends
BaseTransformMeta<TableCompare, TableCompa
hopMetadataPropertyType = HopMetadataPropertyType.RDBMS_TABLE)
private String referenceTableField;
+ @HopMetadataProperty(
+ key = "reference_cte_field",
+ injectionKeyDescription = "
TableCompareMeta.Injection.ReferenceCteField",
+ hopMetadataPropertyType = HopMetadataPropertyType.RDBMS_TABLE)
+ private String referenceCteField;
+
@HopMetadataProperty(
key = "compare_connection",
injectionKeyDescription = "TableCompareMeta.Injection.CompareConnection",
@@ -88,6 +94,12 @@ public class TableCompareMeta extends
BaseTransformMeta<TableCompare, TableCompa
hopMetadataPropertyType = HopMetadataPropertyType.RDBMS_TABLE)
private String compareTableField;
+ @HopMetadataProperty(
+ key = "compare_cte_field",
+ injectionKeyDescription = "TableCompareMeta.Injection.CompareCteField",
+ hopMetadataPropertyType = HopMetadataPropertyType.RDBMS_TABLE)
+ private String compareCteField;
+
@HopMetadataProperty(
key = "key_fields_field",
injectionKeyDescription = "TableCompareMeta.Injection.KeyFieldsField")
diff --git
a/plugins/transforms/tablecompare/src/main/resources/org/apache/hop/pipeline/transforms/tablecompare/messages/messages_en_US.properties
b/plugins/transforms/tablecompare/src/main/resources/org/apache/hop/pipeline/transforms/tablecompare/messages/messages_en_US.properties
index 5a9864455a..29a6e5a861 100644
---
a/plugins/transforms/tablecompare/src/main/resources/org/apache/hop/pipeline/transforms/tablecompare/messages/messages_en_US.properties
+++
b/plugins/transforms/tablecompare/src/main/resources/org/apache/hop/pipeline/transforms/tablecompare/messages/messages_en_US.properties
@@ -51,6 +51,8 @@ TableCompareDialog.CompareSchemaField.Label=Compare schema
field
TableCompareDialog.CompareSchemaField.Tooltip=Select the field to get the
compare schema values from
TableCompareDialog.CompareTableField.Label=Compare table field
TableCompareDialog.CompareTableField.Tooltip=Select the field to get the
compare table values from
+TableCompareDialog.CompareCteField.Label=Compare CTE field
+TableCompareDialog.CompareCteField.Tooltip=Select the field to get the compare
CTE values from
TableCompareDialog.CompareValueField.Label=Error handling compare value input
field
TableCompareDialog.CompareValueField.Tooltip=The name of the field that will
be used by the error handling of this transform to describe the compare value
that caused an error.
TableCompareDialog.DoMapping.UnableToFindInput=Error getting fields from
incoming stream\!
@@ -82,6 +84,8 @@ TableCompareDialog.ReferenceSchemaField.Label=Reference
schema field
TableCompareDialog.ReferenceSchemaField.Tooltip=Select the field to get the
reference schema values from
TableCompareDialog.ReferenceTableField.Label=Reference table field
TableCompareDialog.ReferenceTableField.Tooltip=Select the field to get the
reference table values from
+TableCompareDialog.ReferenceCteField.Label=Reference CTE field
+TableCompareDialog.ReferenceCteField.Tooltip=Select the field to get the
reference CTE values from
TableCompareDialog.ReferenceValueField.Label=Error handling reference value
input field
TableCompareDialog.ReferenceValueField.Tooltip=The name of the field that will
be used by the error handling of this transform to describe the reference value
that caused an error.
TableCompareDialog.Shell.Title=Table compare
@@ -95,6 +99,7 @@
TableCompareMeta.Exception.NrRecordsReferenceFieldNotSpecified=The name of the r
TableCompareMeta.Injection.CompareConnection=Comparison connection
TableCompareMeta.Injection.CompareSchemaField=Comparison schema field
TableCompareMeta.Injection.CompareTableField=Compare table field
+TableCompareMeta.Injection.CompareCteField=Compare CTE field
TableCompareMeta.Injection.ExcludeFieldsField=Exclude fields field
TableCompareMeta.Injection.KeyDescriptionField=Error handling key description
input field
TableCompareMeta.Injection.KeyFieldsField=Key fields field
@@ -107,6 +112,7 @@ TableCompareMeta.Injection.NrRecordsReferenceField=Number
of reference table rec
TableCompareMeta.Injection.ReferenceConnection=Reference connection
TableCompareMeta.Injection.ReferenceSchemaField=Reference schema field
TableCompareMeta.Injection.ReferenceTableField=Reference table field
+TableCompareMeta.Injection.ReferenceCteField=Reference CTE field
TableCompareMeta.Injection.ValueCompareField=Error handling compare value
input field
TableCompareMeta.Injection.ValueReferenceField=Error handling reference value
input field
TableCompareMeta.keyword=table,compare
