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 d854c335f2 Database Join add load SQL from a file (#8064)
d854c335f2 is described below
commit d854c335f2d8d4a7ca94e967ba2248af9691fd53
Author: Lance <[email protected]>
AuthorDate: Mon Aug 24 15:26:45 2026 +0800
Database Join add load SQL from a file (#8064)
Signed-off-by: lance <[email protected]>
---
.../pages/pipeline/transforms/databasejoin.adoc | 19 +-
.../database/0009-database-join-sql-file.hpl | 247 +++++++++++++++++++++
.../database/main-0009-database-join.hwf | 33 +++
.../database/scripts/0009-database-join-lookup.sql | 19 ++
.../transforms/databasejoin/DatabaseJoin.java | 2 +-
.../transforms/databasejoin/DatabaseJoinData.java | 8 -
.../databasejoin/DatabaseJoinDialog.java | 87 +++++++-
.../transforms/databasejoin/DatabaseJoinMeta.java | 159 ++++---------
.../transforms/databasejoin/ParameterField.java | 19 +-
.../databasejoin/cache/DatabaseCache.java | 2 +-
.../messages/messages_en_US.properties | 7 +
.../messages/messages_zh_CN.properties | 7 +
.../databasejoin/DatabaseJoinDataTest.java} | 37 ++-
.../databasejoin/DatabaseJoinMetaTest.java | 141 +++++++++++-
.../transforms/databasejoin/DatabaseJoinTest.java | 5 +
.../databasejoin/ParameterFieldTest.java | 99 +++++++++
.../databasejoin/cache/DatabaseCacheTest.java | 108 +++++++++
17 files changed, 835 insertions(+), 164 deletions(-)
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/databasejoin.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/databasejoin.adoc
index ec4358b538..67fe9dcd96 100644
---
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/databasejoin.adoc
+++
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/databasejoin.adoc
@@ -16,7 +16,9 @@ under the License.
////
:documentationPath: /pipeline/transforms/
:language: en_US
-:description: The Database Join transform allows you to run a query against a
database using data from your pipelines fields as parameters..
+:description: The Database Join transform allows you to run a query against a
database using data from your pipelines fields as parameters.
+:openvar: ${
+:closevar: }
= image:transforms/icons/dbjoin.svg[Database Join transform Icon,
role="image-doc-icon"] Database Join
@@ -69,6 +71,17 @@ image::transforms/databasejoin/databasejoin-grid.png[]
When the transform runs, the (?) placeholder defined in the SQL query will be
replaced with the incoming productnr field value from the source hop.
To define and use multiple parameters, list the fields in order you want them
to be replaced in the SQL statement.
+=== Load SQL from a file
+
+You can keep the query in the SQL editor, or load it from an external `.sql`
file with *Load SQL from file*.
+Use a VFS path; variables in the path (for example
`{openvar}PROJECT_HOME{closevar}/sql/lookup_records.sql`) are resolved.
+The file is read once when the transform initializes, not once per incoming
row.
+When a path is set, the SQL editor shows a read-only preview of the file
contents.
+Question marks (`?`) in the file still bind to the *Parameters* table in the
same order as for inline SQL.
+Leave the path empty to edit the query in the dialog (the previous default).
+
+Enable *Replace variables* to substitute Hop variables in the SQL (inline or
loaded from the file) before the statement is prepared.
+
== Options
[options="header"]
@@ -85,7 +98,9 @@ If you can't use the cache, consider launching several copies
of the simultaneou
A simultaneous launch keeps the database busy through different connections.
See Launching several copies of a transform.
|Cache size in rows|The size of the cache (number of rows), 0 means cache
everything.
-|SQL|SQL query to form the join; use question marks as parameter placeholders
+|SQL|SQL query to form the join; use question marks (`?`) as parameter
placeholders. When *Load SQL from file* is set, this editor is a read-only
preview of the file.
+|Load SQL from file|Optional VFS path to a `.sql` file (variables in the path
are resolved). When set, the query is loaded from this file at transform
initialization and the SQL editor is read-only. Parameter placeholders in the
file still bind to the *Parameters* table. Leave empty to use the SQL editor.
+|Replace variables|Substitute Hop variables (for example
`{openvar}schema{closevar}`) in the SQL before the statement is prepared.
Applies to inline SQL and to SQL loaded from a file.
|Number of rows to return|Zero (0) returns all rows; any other number limits
the number of rows returned.
|Outer join?|Enable to always return a result, even if the query did not
return a result
|Parameters table|Specify the fields containing parameters.
diff --git a/integration-tests/database/0009-database-join-sql-file.hpl
b/integration-tests/database/0009-database-join-sql-file.hpl
new file mode 100644
index 0000000000..1d8d912fdc
--- /dev/null
+++ b/integration-tests/database/0009-database-join-sql-file.hpl
@@ -0,0 +1,247 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+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.
+
+-->
+<pipeline>
+ <info>
+ <name>0009-database-join-sql-file</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2021/04/30 11:01:28.333</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2021/04/30 11:01:28.333</modified_date>
+ <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key>
+ <is_key_private>N</is_key_private>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>Generate rows</from>
+ <to>Database join</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Database join</from>
+ <to>count rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>count rows</from>
+ <to>validate count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>validate count</from>
+ <to>success</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>validate count</from>
+ <to>failed on count</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Database join</name>
+ <type>DBJoin</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>unit-test-db</connection>
+ <rowlimit>0</rowlimit>
+ <sql>select value from public.testtable
+where 1 = 0</sql>
+
<sql_from_file>${PROJECT_HOME}/scripts/0009-database-join-lookup.sql</sql_from_file>
+ <outer_join>N</outer_join>
+ <replace_vars>N</replace_vars>
+ <parameter>
+ <field>
+ <name>key</name>
+ <type>String</type>
+ </field>
+ </parameter>
+ <attributes/>
+ <GUI>
+ <xloc>336</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Generate rows</name>
+ <type>RowGenerator</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <fields>
+ <field>
+ <name>key</name>
+ <type>String</type>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ <nullif>10</nullif>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ </fields>
+ <limit>1</limit>
+ <never_ending>N</never_ending>
+ <interval_in_ms>5000</interval_in_ms>
+ <row_time_field>now</row_time_field>
+ <last_time_field>FiveSecondsAgo</last_time_field>
+ <attributes/>
+ <GUI>
+ <xloc>144</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>count rows</name>
+ <type>GroupBy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <all_rows>N</all_rows>
+ <ignore_aggregate>N</ignore_aggregate>
+ <field_ignore/>
+ <directory>${java.io.tmpdir}</directory>
+ <prefix>grp</prefix>
+ <add_linenr>N</add_linenr>
+ <linenr_fieldname/>
+ <give_back_row>Y</give_back_row>
+ <group>
+ </group>
+ <fields>
+ <field>
+ <aggregate>count</aggregate>
+ <subject/>
+ <type>COUNT_ANY</type>
+ <valuefield/>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>512</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>failed on count</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <row_threshold>0</row_threshold>
+ <message/>
+ <always_log_rows>Y</always_log_rows>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <attributes/>
+ <GUI>
+ <xloc>640</xloc>
+ <yloc>176</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>success</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>784</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>validate count</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>success</send_true_to>
+ <send_false_to>failed on count</send_false_to>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <leftvalue>count</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>1</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>640</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/database/main-0009-database-join.hwf
b/integration-tests/database/main-0009-database-join.hwf
index 9c5bc88d19..24bf32c060 100644
--- a/integration-tests/database/main-0009-database-join.hwf
+++ b/integration-tests/database/main-0009-database-join.hwf
@@ -293,6 +293,32 @@ ok;</script>
<yloc>48</yloc>
<attributes_hac/>
</action>
+ <action>
+ <name>0009-database-join-sql-file</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+ <filename>${PROJECT_HOME}/0009-database-join-sql-file.hpl</filename>
+ <loglevel>Basic</loglevel>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <parallel>N</parallel>
+ <xloc>2912</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
</actions>
<hops>
<hop>
@@ -365,6 +391,13 @@ ok;</script>
<evaluation>Y</evaluation>
<unconditional>N</unconditional>
</hop>
+ <hop>
+ <from>Check input rows limited cache size very big</from>
+ <to>0009-database-join-sql-file</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
</hops>
<notepads>
</notepads>
diff --git a/integration-tests/database/scripts/0009-database-join-lookup.sql
b/integration-tests/database/scripts/0009-database-join-lookup.sql
new file mode 100644
index 0000000000..b9bf9fc63e
--- /dev/null
+++ b/integration-tests/database/scripts/0009-database-join-lookup.sql
@@ -0,0 +1,19 @@
+/*
+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.
+*/
+
+select value from public.testtable
+where key = ?
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoin.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoin.java
index f4cca36ee9..aa0c89f2c7 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoin.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoin.java
@@ -273,7 +273,7 @@ public class DatabaseJoin extends
BaseTransform<DatabaseJoinMeta, DatabaseJoinDa
logDetailed(BaseMessages.getString(PKG,
"DatabaseJoin.Log.ConnectedToDB"));
}
- String sql = meta.getSql();
+ String sql = meta.getEffectiveSql(variables);
if (meta.isReplaceVariables()) {
sql = resolve(sql);
}
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
index fff2eaf409..52fcfec614 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
@@ -36,12 +36,4 @@ public class DatabaseJoinData extends BaseTransformData
implements ITransformDat
public int[] keynrs; // parameter value index in an input row...
public Object[] notfound; // Values in case nothing is found...
public boolean isCanceled;
-
- public DatabaseJoinData() {
- super();
-
- db = null;
- notfound = null;
- cache = null;
- }
}
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDialog.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDialog.java
index 2454a5f1ff..bfb37f8a02 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDialog.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDialog.java
@@ -17,6 +17,7 @@
package org.apache.hop.pipeline.transforms.databasejoin;
+import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
@@ -24,10 +25,12 @@ import org.apache.hop.core.Const;
import org.apache.hop.core.Props;
import org.apache.hop.core.database.DatabaseMeta;
import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.exception.HopFileException;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.value.ValueMetaFactory;
import org.apache.hop.core.util.Utils;
import org.apache.hop.core.variables.IVariables;
+import org.apache.hop.core.vfs.HopVfs;
import org.apache.hop.i18n.BaseMessages;
import org.apache.hop.pipeline.PipelineMeta;
import org.apache.hop.pipeline.transform.TransformMeta;
@@ -42,6 +45,7 @@ import org.apache.hop.ui.core.widget.SQLStyledTextComp;
import org.apache.hop.ui.core.widget.StyledTextComp;
import org.apache.hop.ui.core.widget.TableView;
import org.apache.hop.ui.core.widget.TextComposite;
+import org.apache.hop.ui.core.widget.TextVar;
import org.apache.hop.ui.pipeline.transform.BaseTransformDialog;
import org.apache.hop.ui.util.EnvironmentUtils;
import org.eclipse.swt.SWT;
@@ -69,6 +73,8 @@ public class DatabaseJoinDialog extends BaseTransformDialog {
private TextComposite wSql;
+ private TextVar wSqlFromFile;
+
private Text wLimit;
private Button wOuter;
@@ -156,13 +162,57 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
fdCacheSize.top = new FormAttachment(wCache, margin);
wCacheSize.setLayoutData(fdCacheSize);
+ // Load SQL from file
+ Label wlSqlFromFile = new Label(shell, SWT.RIGHT);
+ wlSqlFromFile.setText(BaseMessages.getString(PKG,
"DatabaseJoinDialog.LoadSqlFromFile"));
+ PropsUi.setLook(wlSqlFromFile);
+ FormData fdlSqlFromFile = new FormData();
+ fdlSqlFromFile.left = new FormAttachment(0, 0);
+ fdlSqlFromFile.right = new FormAttachment(middle, -margin);
+ fdlSqlFromFile.top = new FormAttachment(wCacheSize, margin);
+ wlSqlFromFile.setLayoutData(fdlSqlFromFile);
+ Button wbSqlFromFile = new Button(shell, SWT.PUSH);
+ PropsUi.setLook(wbSqlFromFile);
+ wbSqlFromFile.setText(BaseMessages.getString(PKG,
"DatabaseJoinDialog.Browse"));
+ FormData fdbSqlFromFile = new FormData();
+ fdbSqlFromFile.right = new FormAttachment(100, 0);
+ fdbSqlFromFile.top = new FormAttachment(wlSqlFromFile, 0, SWT.CENTER);
+ wbSqlFromFile.setLayoutData(fdbSqlFromFile);
+
+ wSqlFromFile = new TextVar(variables, shell, SWT.SINGLE | SWT.LEFT |
SWT.BORDER);
+ PropsUi.setLook(wSqlFromFile);
+ wSqlFromFile.addModifyListener(lsMod);
+ FormData fdSqlFromFile = new FormData();
+ fdSqlFromFile.left = new FormAttachment(middle, 0);
+ fdSqlFromFile.right = new FormAttachment(wbSqlFromFile, -margin);
+ fdSqlFromFile.top = new FormAttachment(wlSqlFromFile, 0, SWT.CENTER);
+ wSqlFromFile.setLayoutData(fdSqlFromFile);
+ wbSqlFromFile.addListener(
+ SWT.Selection,
+ e -> {
+ String path =
+ BaseDialog.presentFileDialog(
+ shell,
+ wSqlFromFile,
+ variables,
+ new String[] {"*.sql", "*"},
+ new String[] {
+ BaseMessages.getString(PKG, "DatabaseJoinDialog.SqlFiles"),
+ BaseMessages.getString(PKG, "System.FileType.AllFiles")
+ },
+ false);
+ if (path != null) {
+ loadSqlFromFileAndSetReadOnly();
+ }
+ });
+
// SQL editor...
Label wlSql = new Label(shell, SWT.NONE);
wlSql.setText(BaseMessages.getString(PKG, "DatabaseJoinDialog.SQL.Label"));
PropsUi.setLook(wlSql);
FormData fdlSql = new FormData();
fdlSql.left = new FormAttachment(0, 0);
- fdlSql.top = new FormAttachment(wCacheSize, margin);
+ fdlSql.top = new FormAttachment(wbSqlFromFile, margin);
wlSql.setLayoutData(fdlSql);
wSql =
@@ -177,6 +227,12 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
wSql.addLineStyleListener(getSqlReservedWords());
PropsUi.setLook(wSql, Props.WIDGET_STYLE_FIXED);
wSql.addModifyListener(lsMod);
+ wSqlFromFile.addModifyListener(
+ e -> {
+ if (Utils.isEmpty(wSqlFromFile.getText())) {
+ wSql.setEditable(true);
+ }
+ });
FormData fdSql = new FormData();
fdSql.left = new FormAttachment(0, 0);
fdSql.top = new FormAttachment(wlSql, margin);
@@ -411,6 +467,28 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
PKG, "DatabaseJoinDialog.Position.Label", "" + lineNumber, "" +
columnNumber));
}
+ private void loadSqlFromFileAndSetReadOnly() {
+ String path = variables.resolve(wSqlFromFile.getText());
+ if (Utils.isEmpty(path)) {
+ wSql.setEditable(true);
+ return;
+ }
+ try {
+ String content = HopVfs.getTextFileContent(path, StandardCharsets.UTF_8);
+ wSql.setText(content);
+ wSql.setEditable(false);
+ } catch (HopFileException e) {
+ MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_WARNING);
+ mb.setText(BaseMessages.getString(PKG,
"DatabaseJoinDialog.InvalidConnection.DialogTitle"));
+ mb.setMessage(
+ BaseMessages.getString(PKG,
"DatabaseJoinDialog.CouldNotLoadSqlFromFile", path)
+ + Const.CR
+ + e.getMessage());
+ mb.open();
+ wSql.setEditable(true);
+ }
+ }
+
/** Copy information from the meta-data input to the dialog fields. */
public void getData() {
logDebug(BaseMessages.getString(PKG,
"DatabaseJoinDialog.Log.GettingKeyInfo"));
@@ -421,6 +499,12 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
wCacheSize.setText("" + input.getCacheSize());
wSql.setText(Const.NVL(input.getSql(), ""));
+ wSqlFromFile.setText(Const.NVL(input.getSqlFromFile(), ""));
+ if (!Utils.isEmpty(wSqlFromFile.getText())) {
+ loadSqlFromFileAndSetReadOnly();
+ } else {
+ wSql.setEditable(true);
+ }
wLimit.setText("" + input.getRowLimit());
wOuter.setSelection(input.isOuterJoin());
wUseVars.setSelection(input.isReplaceVariables());
@@ -459,6 +543,7 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
input.setCacheSize(Const.toInt(wCacheSize.getText(), 0));
input.setRowLimit(Const.toIntExpanded(wLimit.getText(), 0));
input.setSql(wSql.getText());
+ input.setSqlFromFile(wSqlFromFile.getText());
input.setOuterJoin(wOuter.getSelection());
input.setReplaceVariables(wUseVars.getSelection());
logDebug(
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMeta.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMeta.java
index e301335596..32cebec8a4 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMeta.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMeta.java
@@ -17,8 +17,11 @@
package org.apache.hop.pipeline.transforms.databasejoin;
+import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
+import lombok.Getter;
+import lombok.Setter;
import org.apache.hop.core.CheckResult;
import org.apache.hop.core.Const;
import org.apache.hop.core.ICheckResult;
@@ -28,6 +31,7 @@ import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
import org.apache.hop.core.exception.HopDatabaseException;
import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.exception.HopFileException;
import org.apache.hop.core.exception.HopPluginException;
import org.apache.hop.core.exception.HopTransformException;
import org.apache.hop.core.row.IRowMeta;
@@ -37,6 +41,7 @@ import org.apache.hop.core.row.value.ValueMetaFactory;
import org.apache.hop.core.row.value.ValueMetaNone;
import org.apache.hop.core.util.Utils;
import org.apache.hop.core.variables.IVariables;
+import org.apache.hop.core.vfs.HopVfs;
import org.apache.hop.i18n.BaseMessages;
import org.apache.hop.metadata.api.HopMetadataProperty;
import org.apache.hop.metadata.api.HopMetadataPropertyType;
@@ -46,6 +51,8 @@ import org.apache.hop.pipeline.PipelineMeta;
import org.apache.hop.pipeline.transform.BaseTransformMeta;
import org.apache.hop.pipeline.transform.TransformMeta;
+@Getter
+@Setter
@Transform(
id = "DBJoin",
image = "dbjoin.svg",
@@ -85,6 +92,15 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
hopMetadataPropertyType = HopMetadataPropertyType.RDBMS_SQL)
private String sql;
+ /**
+ * When set, SQL is loaded from this file (VFS path, supports variables).
SQL editor is read-only.
+ */
+ @HopMetadataProperty(
+ key = "sql_from_file",
+ injectionKey = "SQL_FROM_FILE",
+ injectionKeyDescription = "DatabaseJoinMeta.Injection.SqlFromFile")
+ private String sqlFromFile;
+
/** Number of rows to return (0=ALL) */
@HopMetadataProperty(
key = "rowlimit",
@@ -114,104 +130,26 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
injectionKeyDescription = "DatabaseJoinMeta.Injection.ReplaceVariables")
private boolean replaceVariables;
- public DatabaseJoinMeta() {
- super(); // allocate BaseTransformMeta
- }
-
- public String getConnection() {
- return connection;
- }
-
- public void setConnection(String connection) {
- this.connection = connection;
- }
-
- /**
- * Gets cached
- *
- * @return value of cached
- */
- public boolean isCached() {
- return cached;
- }
-
- /**
- * @param cached The cached to set
- */
- public void setCached(boolean cached) {
- this.cached = cached;
- }
-
- /**
- * Gets cacheSize
- *
- * @return value of cacheSize
- */
- public int getCacheSize() {
- return cacheSize;
- }
-
- /**
- * @param cacheSize The cacheSize to set
- */
- public void setCacheSize(int cacheSize) {
- this.cacheSize = cacheSize;
- }
-
- /**
- * @return Returns the outerJoin.
- */
- public boolean isOuterJoin() {
- return outerJoin;
- }
-
- /**
- * @param outerJoin The outerJoin to set.
- */
- public void setOuterJoin(boolean outerJoin) {
- this.outerJoin = outerJoin;
- }
-
- /**
- * @return Returns the replacevars.
- */
- public boolean isReplaceVariables() {
- return replaceVariables;
- }
-
- /**
- * @param enabled The replacevars to set.
- */
- public void setReplaceVariables(boolean enabled) {
- this.replaceVariables = enabled;
- }
-
- /**
- * @return Returns the rowLimit.
- */
- public int getRowLimit() {
- return rowLimit;
- }
-
- /**
- * @param rowLimit The rowLimit to set.
- */
- public void setRowLimit(int rowLimit) {
- this.rowLimit = rowLimit;
- }
-
/**
- * @return Returns the sql.
+ * Returns the SQL to execute: either from the inline editor or loaded from
the file specified by
+ * sqlFromFile (using VFS). Variables are resolved in the file path.
*/
- public String getSql() {
+ public String getEffectiveSql(IVariables variables) throws HopException {
+ if (!Utils.isEmpty(sqlFromFile)) {
+ String path = variables.resolve(sqlFromFile);
+ try {
+ return HopVfs.getTextFileContent(path, StandardCharsets.UTF_8);
+ } catch (HopFileException e) {
+ throw new HopException(
+ BaseMessages.getString(PKG,
"DatabaseJoinMeta.Exception.CouldNotLoadSqlFromFile", path),
+ e);
+ }
+ }
return sql;
}
- /**
- * @param sql The sql to set.
- */
- public void setSql(String sql) {
- this.sql = sql;
+ private String resolveSql(IVariables variables) throws HopException {
+ return variables.resolve(Const.NVL(getEffectiveSql(variables), ""));
}
@Override
@@ -271,17 +209,24 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
//
IRowMeta param = getParameterRow(row);
+ String sqlToUse;
+ try {
+ sqlToUse = resolveSql(variables);
+ } catch (HopException e) {
+ throw new HopTransformException(e.getMessage(), e);
+ }
+
// First try without connecting to the database... (can be S L O W)
// See if it's in the cache...
//
IRowMeta add = null;
try {
- add = db.getQueryFields(variables.resolve(sql), true, param, new
Object[param.size()]);
+ add = db.getQueryFields(sqlToUse, true, param, new
Object[param.size()]);
} catch (HopDatabaseException dbe) {
throw new HopTransformException(
BaseMessages.getString(PKG,
"DatabaseJoinMeta.Exception.UnableToDetermineQueryFields")
+ Const.CR
- + sql,
+ + sqlToUse,
dbe);
}
@@ -296,7 +241,7 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
//
try {
db.connect();
- add = db.getQueryFields(variables.resolve(sql), true, param, new
Object[param.size()]);
+ add = db.getQueryFields(sqlToUse, true, param, new
Object[param.size()]);
for (int i = 0; i < add.size(); i++) {
IValueMeta v = add.getValueMeta(i);
v.setOrigin(name);
@@ -347,13 +292,13 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
try {
db.connect();
- if (!Utils.isEmpty(sql)) {
+ String sqlToUse = resolveSql(variables);
+ if (!Utils.isEmpty(sqlToUse)) {
IRowMeta param = getParameterRow(prev);
errorMessage = "";
- IRowMeta r =
- db.getQueryFields(variables.resolve(sql), true, param, new
Object[param.size()]);
+ IRowMeta r = db.getQueryFields(sqlToUse, true, param, new
Object[param.size()]);
if (r != null) {
cr =
new CheckResult(
@@ -368,7 +313,7 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
remarks.add(cr);
}
- int q = db.countParameters(variables.resolve(sql));
+ int q = db.countParameters(sqlToUse);
if (q != parameters.size()) {
errorMessage =
BaseMessages.getString(
@@ -494,8 +439,8 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
try {
db.connect();
- fields = db.getQueryFields(variables.resolve(sql), true, param, new
Object[param.size()]);
- } catch (HopDatabaseException dbe) {
+ fields = db.getQueryFields(resolveSql(variables), true, param, new
Object[param.size()]);
+ } catch (HopException dbe) {
logError(
BaseMessages.getString(PKG,
"DatabaseJoinMeta.Log.DatabaseErrorOccurred")
+ dbe.getMessage());
@@ -549,7 +494,7 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
outvalue.getName(),
outvalue.getName(),
transformMeta.getName(),
- variables.resolve(sql),
+ resolveSql(variables),
BaseMessages.getString(PKG,
"DatabaseJoinMeta.DatabaseImpact.Title"));
impact.add(di);
}
@@ -565,12 +510,4 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
public boolean supportsErrorHandling() {
return true;
}
-
- public List<ParameterField> getParameters() {
- return parameters;
- }
-
- public void setParameters(List<ParameterField> parameters) {
- this.parameters = parameters;
- }
}
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/ParameterField.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/ParameterField.java
index 953bbcfa32..c09125b807 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/ParameterField.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/ParameterField.java
@@ -6,7 +6,7 @@
* (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
+ * 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,
@@ -17,12 +17,16 @@
package org.apache.hop.pipeline.transforms.databasejoin;
+import lombok.Getter;
+import lombok.NoArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.row.value.ValueMetaFactory;
import org.apache.hop.metadata.api.HopMetadataProperty;
/** The parameters field. */
+@Getter
+@NoArgsConstructor
public class ParameterField implements Cloneable {
/** The target field name */
@@ -38,12 +42,7 @@ public class ParameterField implements Cloneable {
injectionKeyDescription = "DatabaseJoinMeta.Injection.Parameter.Type")
private String type =
ValueMetaFactory.getValueMetaName(IValueMeta.TYPE_NONE);
- public ParameterField() {
- super();
- }
-
public ParameterField(ParameterField cloned) {
- super();
this.name = cloned.name;
this.type = cloned.type;
}
@@ -53,18 +52,10 @@ public class ParameterField implements Cloneable {
return new ParameterField(this);
}
- public String getName() {
- return name;
- }
-
public void setName(final String name) {
this.name = StringUtils.stripToNull(name);
}
- public String getType() {
- return this.type;
- }
-
public void setType(final String type) {
this.type = type;
}
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/cache/DatabaseCache.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/cache/DatabaseCache.java
index ef7a375231..6124d0ee7a 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/cache/DatabaseCache.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/cache/DatabaseCache.java
@@ -36,7 +36,7 @@ public class DatabaseCache {
}
public List<Object[]> getRowsFromCache(RowMetaAndData key) {
- return cache.getOrDefault(key, null);
+ return cache.get(key);
}
public void putRowsIntoCache(RowMetaAndData key, List<Object[]> values) {
diff --git
a/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_en_US.properties
b/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_en_US.properties
index 10319c4747..90ff031e2c 100644
---
a/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_en_US.properties
+++
b/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_en_US.properties
@@ -44,6 +44,10 @@ DatabaseJoinDialog.Param.Label=The parameters to use\:
DatabaseJoinDialog.Position.Label=Line {0} Column {1}
DatabaseJoinDialog.Shell.Title=Database join
DatabaseJoinDialog.SQL.Label=SQL
+DatabaseJoinDialog.LoadSqlFromFile=Load SQL from file
+DatabaseJoinDialog.Browse=Browse...
+DatabaseJoinDialog.SqlFiles=SQL files
+DatabaseJoinDialog.CouldNotLoadSqlFromFile=Could not load SQL from file: {0}
DatabaseJoinDialog.TransformName.Label=Transform name
DatabaseJoinDialog.useVarsjoin.Label=Replace variables
DatabaseJoinDialog.useVarsjoin.Tooltip=Replace variables in SQL script
@@ -64,6 +68,7 @@ DatabaseJoinMeta.CheckResult.ReceivingInfo=Transform is
receiving info from othe
DatabaseJoinMeta.DatabaseImpact.Title=read from one or more database tables
via SQL statement
DatabaseJoinMeta.Exception.ErrorObtainingFields=Error obtaining fields for
this transform
DatabaseJoinMeta.Exception.UnableToDetermineQueryFields=Unable to determine
the fields of query\:
+DatabaseJoinMeta.Exception.CouldNotLoadSqlFromFile=Could not load SQL from
file: {0}
DatabaseJoinMeta.Injection.Cache=Enable cache ? (Y/N)
DatabaseJoinMeta.Injection.CacheSize=Cache size in rows
DatabaseJoinMeta.Injection.Connection=Connection
@@ -75,5 +80,7 @@ DatabaseJoinMeta.Injection.Parameters=The parameter fields to
use
DatabaseJoinMeta.Injection.ReplaceVariables=Replace variables in SQL script ?
(Y/N)
DatabaseJoinMeta.Injection.RowLimit=Number of rows to return
DatabaseJoinMeta.Injection.SQL=SQL
+DatabaseJoinMeta.Injection.SqlFromFile=The SQL file to load the query from
(VFS path, supports variables)
DatabaseJoinMeta.keyword=database,join,sql,lookup,query
DatabaseJoinMeta.Log.DatabaseErrorOccurred=A database error occurred\:
+System.FileType.AllFiles=All files
diff --git
a/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_zh_CN.properties
b/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_zh_CN.properties
index 86211f1d5c..1453ee9d70 100644
---
a/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_zh_CN.properties
+++
b/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_zh_CN.properties
@@ -46,6 +46,10 @@ DatabaseJoinDialog.Param.Label=The parameters to use\:
DatabaseJoinDialog.Position.Label=\u884C {0} \u5217 {1}
DatabaseJoinDialog.Shell.Title=Database join
DatabaseJoinDialog.SQL.Label=SQL\:
+DatabaseJoinDialog.LoadSqlFromFile=\u4ECE\u6587\u4EF6\u52A0\u8F7D SQL
+DatabaseJoinDialog.Browse=\u6D4F\u89C8...
+DatabaseJoinDialog.SqlFiles=SQL \u6587\u4EF6
+DatabaseJoinDialog.CouldNotLoadSqlFromFile=\u65E0\u6CD5\u4ECE\u6587\u4EF6\u52A0\u8F7D
SQL: {0}
DatabaseJoinDialog.TransformName.Label=Transform \u540D\u79F0\:
DatabaseJoinDialog.useVarsjoin.Label=\u66FF\u6362\u53D8\u91CF\:
DatabaseJoinDialog.useVarsjoin.Tooltip=\u66FF\u6362 SQL
\u811A\u672C\u4E2D\u7684\u53D8\u91CF
@@ -66,6 +70,7 @@ DatabaseJoinMeta.CheckResult.ReceivingInfo=Transform is
receiving info from othe
DatabaseJoinMeta.DatabaseImpact.Title=read from one or more database tables
via SQL statement
DatabaseJoinMeta.Exception.ErrorObtainingFields=Error obtaining fields for
this transform
DatabaseJoinMeta.Exception.UnableToDetermineQueryFields=Unable to determine
the fields of query\:
+DatabaseJoinMeta.Exception.CouldNotLoadSqlFromFile=\u65E0\u6CD5\u4ECE\u6587\u4EF6\u52A0\u8F7D
SQL: {0}
DatabaseJoinMeta.Injection.Cache=\u542F\u7528\u7F13\u5B58?(Y/N)
DatabaseJoinMeta.Injection.CacheSize=\u7F13\u5B58\u5927\u5C0F
DatabaseJoinMeta.Injection.Connection=\u8FDE\u63A5
@@ -77,5 +82,7 @@
DatabaseJoinMeta.Injection.Parameters=\u8981\u4F7F\u7528\u7684\u53C2\u6570\u5B57
DatabaseJoinMeta.Injection.ReplaceVariables=\u66FF\u6362 SQL
\u811A\u672C\u4E2D\u7684\u53D8\u91CF?(Y/N)
DatabaseJoinMeta.Injection.RowLimit=\u8981\u8FD4\u56DE\u7684\u884C\u6570
DatabaseJoinMeta.Injection.SQL=SQL
+DatabaseJoinMeta.Injection.SqlFromFile=\u8981\u52A0\u8F7D\u67E5\u8BE2\u7684
SQL \u6587\u4EF6\u8DEF\u5F84\uFF08\u652F\u6301\u53D8\u91CF\uFF09
DatabaseJoinMeta.keyword=database,db,join
DatabaseJoinMeta.Log.DatabaseErrorOccurred=A database error occurred\:
+System.FileType.AllFiles=\u6240\u6709\u6587\u4EF6
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDataTest.java
similarity index 51%
copy from
plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
copy to
plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDataTest.java
index fff2eaf409..2c07dafb4a 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
+++
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDataTest.java
@@ -17,31 +17,22 @@
package org.apache.hop.pipeline.transforms.databasejoin;
-import java.sql.PreparedStatement;
-import org.apache.hop.core.database.Database;
-import org.apache.hop.core.row.IRowMeta;
-import org.apache.hop.pipeline.transform.BaseTransformData;
-import org.apache.hop.pipeline.transform.ITransformData;
-import org.apache.hop.pipeline.transforms.databasejoin.cache.DatabaseCache;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertNull;
-@SuppressWarnings("java:S1104")
-public class DatabaseJoinData extends BaseTransformData implements
ITransformData {
- public Database db;
- public PreparedStatement pstmt;
- public DatabaseCache cache;
+import org.junit.jupiter.api.Test;
- IRowMeta outputRowMeta;
- IRowMeta lookupRowMeta;
+/** Unit test for {@link DatabaseJoinData} */
+class DatabaseJoinDataTest {
- public int[] keynrs; // parameter value index in an input row...
- public Object[] notfound; // Values in case nothing is found...
- public boolean isCanceled;
-
- public DatabaseJoinData() {
- super();
-
- db = null;
- notfound = null;
- cache = null;
+ @Test
+ void defaultsAreUnset() {
+ DatabaseJoinData data = new DatabaseJoinData();
+ assertNull(data.db);
+ assertNull(data.pstmt);
+ assertNull(data.cache);
+ assertNull(data.notfound);
+ assertNull(data.keynrs);
+ assertFalse(data.isCanceled);
}
}
diff --git
a/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMetaTest.java
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMetaTest.java
index 42a78d08d0..ca599feefe 100644
---
a/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMetaTest.java
+++
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMetaTest.java
@@ -16,27 +16,35 @@
*/
package org.apache.hop.pipeline.transforms.databasejoin;
+import java.nio.file.Files;
+import java.nio.file.Path;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
-import java.util.Random;
import java.util.UUID;
import org.apache.commons.lang3.builder.EqualsBuilder;
import org.apache.hop.core.HopEnvironment;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.plugins.PluginRegistry;
+import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.core.variables.Variables;
import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
import org.apache.hop.pipeline.transforms.loadsave.LoadSaveTester;
import org.apache.hop.pipeline.transforms.loadsave.initializer.IInitializer;
import
org.apache.hop.pipeline.transforms.loadsave.validator.IFieldLoadSaveValidator;
import
org.apache.hop.pipeline.transforms.loadsave.validator.ListLoadSaveValidator;
+import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.RegisterExtension;
+/** Unit test for {@link DatabaseJoinMeta} */
class DatabaseJoinMetaTest implements IInitializer<DatabaseJoinMeta> {
LoadSaveTester<DatabaseJoinMeta> loadSaveTester;
Class<DatabaseJoinMeta> testMetaClass = DatabaseJoinMeta.class;
@@ -54,7 +62,15 @@ class DatabaseJoinMetaTest implements
IInitializer<DatabaseJoinMeta> {
void setUpLoadSave() throws Exception {
List<String> attributes =
Arrays.asList(
- "sql", "rowLimit", "outerJoin", "replaceVariables", "connection",
"parameters");
+ "sql",
+ "sqlFromFile",
+ "rowLimit",
+ "outerJoin",
+ "replaceVariables",
+ "connection",
+ "cached",
+ "cacheSize",
+ "parameters");
Map<String, String> getterMap = new HashMap<>();
@@ -88,8 +104,127 @@ class DatabaseJoinMetaTest implements
IInitializer<DatabaseJoinMeta> {
loadSaveTester.testSerialization();
}
+ @Test
+ void getEffectiveSqlUsesInlineWhenNoFile() throws Exception {
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ meta.setSql("SELECT 1");
+ Assertions.assertEquals("SELECT 1", meta.getEffectiveSql(new Variables()));
+ }
+
+ @Test
+ void getEffectiveSqlLoadsFromFile() throws Exception {
+ Path file = Files.createTempFile("databasejoin-", ".sql");
+ try {
+ String sql = "SELECT * FROM lookup WHERE id = ?";
+ Files.writeString(file, sql);
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ meta.setSql("SELECT 1");
+ meta.setSqlFromFile(file.toAbsolutePath().toString());
+ Assertions.assertEquals(sql, meta.getEffectiveSql(new Variables()));
+ } finally {
+ Files.deleteIfExists(file);
+ }
+ }
+
+ @Test
+ void getEffectiveSqlResolvesVariablesInPath() throws Exception {
+ Path file = Files.createTempFile("databasejoin-", ".sql");
+ try {
+ Files.writeString(file, "SELECT 2");
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ meta.setSqlFromFile("${SQL_FILE}");
+ Variables variables = new Variables();
+ variables.setVariable("SQL_FILE", file.toAbsolutePath().toString());
+ Assertions.assertEquals("SELECT 2", meta.getEffectiveSql(variables));
+ } finally {
+ Files.deleteIfExists(file);
+ }
+ }
+
+ @Test
+ void getEffectiveSqlMissingFileThrows() {
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ meta.setSqlFromFile("/this/path/does/not/exist-databasejoin.sql");
+ Assertions.assertThrows(HopException.class, () -> meta.getEffectiveSql(new
Variables()));
+ }
+
+ @Test
+ void getEffectiveSqlEmptyFilePathUsesInlineSql() throws Exception {
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ meta.setSql("SELECT 1");
+ meta.setSqlFromFile("");
+ Assertions.assertEquals("SELECT 1", meta.getEffectiveSql(new Variables()));
+ }
+
+ @Test
+ void setDefaultResetsLookupOptions() {
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ meta.setSql("SELECT 1");
+ meta.setRowLimit(10);
+ meta.setOuterJoin(true);
+ meta.setReplaceVariables(true);
+ ParameterField field = new ParameterField();
+ field.setName("id");
+ meta.getParameters().add(field);
+
+ meta.setDefault();
+
+ Assertions.assertEquals("", meta.getSql());
+ Assertions.assertEquals(0, meta.getRowLimit());
+ Assertions.assertFalse(meta.isOuterJoin());
+ Assertions.assertFalse(meta.isReplaceVariables());
+ Assertions.assertTrue(meta.getParameters().isEmpty());
+ }
+
+ @Test
+ void supportsErrorHandling() {
+ Assertions.assertTrue(new DatabaseJoinMeta().supportsErrorHandling());
+ }
+
+ @Test
+ void getFieldsDoesNothingWhenConnectionIsMissing() throws Exception {
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ IRowMeta row = new RowMeta();
+ row.addValueMeta(new ValueMetaString("id"));
+ meta.getFields(row, "join", null, null, new Variables(), null);
+ Assertions.assertEquals(1, row.size());
+ Assertions.assertEquals("id", row.getValueMeta(0).getName());
+ }
+
+ @Test
+ void getParameterRowSelectsMatchingIncomingFieldsInOrder() {
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ ParameterField id = new ParameterField();
+ id.setName("id");
+ ParameterField missing = new ParameterField();
+ missing.setName("missing");
+ ParameterField name = new ParameterField();
+ name.setName("name");
+ meta.setParameters(List.of(id, missing, name));
+
+ IRowMeta incoming = new RowMeta();
+ incoming.addValueMeta(new ValueMetaString("name"));
+ incoming.addValueMeta(new ValueMetaInteger("id"));
+ incoming.addValueMeta(new ValueMetaString("extra"));
+
+ IRowMeta param = meta.getParameterRow(incoming);
+ Assertions.assertEquals(2, param.size());
+ Assertions.assertEquals("id", param.getValueMeta(0).getName());
+ Assertions.assertEquals("name", param.getValueMeta(1).getName());
+ }
+
+ @Test
+ void getParameterRowHandlesNullIncoming() {
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ ParameterField field = new ParameterField();
+ field.setName("id");
+ meta.getParameters().add(field);
+ IRowMeta param = meta.getParameterRow(null);
+ Assertions.assertNotNull(param);
+ Assertions.assertTrue(param.isEmpty());
+ }
+
public class ParameterFieldLoadSaveValidator implements
IFieldLoadSaveValidator<ParameterField> {
- final Random rand = new Random();
@Override
public ParameterField getTestObject() {
diff --git
a/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinTest.java
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinTest.java
index 3712a775c0..3566da85ac 100644
---
a/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinTest.java
+++
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinTest.java
@@ -42,6 +42,11 @@ import
org.apache.hop.pipeline.transform.TransformPartitioningMeta;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
+/**
+ * Unit test for {@link DatabaseJoin}
+ *
+ * @author lance 2026/8/22 7:43
+ */
class DatabaseJoinTest {
DatabaseJoinMeta mockTransformMetaInterface;
diff --git
a/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/ParameterFieldTest.java
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/ParameterFieldTest.java
new file mode 100644
index 0000000000..df524061c9
--- /dev/null
+++
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/ParameterFieldTest.java
@@ -0,0 +1,99 @@
+/*
+ * 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.pipeline.transforms.databasejoin;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNotSame;
+import static org.junit.jupiter.api.Assertions.assertNull;
+
+import org.apache.hop.core.HopEnvironment;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.value.ValueMetaFactory;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+
+/** Unit test for {@link ParameterField} */
+class ParameterFieldTest {
+
+ @BeforeAll
+ static void setUpClass() throws Exception {
+ HopEnvironment.init();
+ }
+
+ @Test
+ void defaultTypeIsNone() {
+ ParameterField field = new ParameterField();
+ assertNull(field.getName());
+ assertEquals(ValueMetaFactory.getValueMetaName(IValueMeta.TYPE_NONE),
field.getType());
+ }
+
+ @Test
+ void setNameStripsBlankToNull() {
+ ParameterField field = new ParameterField();
+ field.setName(" id ");
+ assertEquals("id", field.getName());
+ field.setName(" ");
+ assertNull(field.getName());
+ field.setName(null);
+ assertNull(field.getName());
+ }
+
+ @Test
+ void setTypeAcceptsNameAndId() {
+ ParameterField field = new ParameterField();
+ field.setType("Integer");
+ assertEquals("Integer", field.getType());
+ field.setType(IValueMeta.TYPE_STRING);
+ assertEquals(ValueMetaFactory.getValueMetaName(IValueMeta.TYPE_STRING),
field.getType());
+ }
+
+ @Test
+ void cloneCopiesValuesIndependently() {
+ ParameterField original = new ParameterField();
+ original.setName("id");
+ original.setType(IValueMeta.TYPE_INTEGER);
+
+ ParameterField copy = (ParameterField) original.clone();
+ assertNotSame(original, copy);
+ assertEquals(original.getName(), copy.getName());
+ assertEquals(original.getType(), copy.getType());
+
+ copy.setName("other");
+ copy.setType(IValueMeta.TYPE_STRING);
+ assertEquals("id", original.getName());
+ assertEquals(ValueMetaFactory.getValueMetaName(IValueMeta.TYPE_INTEGER),
original.getType());
+ }
+
+ @Test
+ void copyConstructorCopiesValues() {
+ ParameterField original = new ParameterField();
+ original.setName("code");
+ original.setType("String");
+ ParameterField copy = new ParameterField(original);
+ assertEquals("code", copy.getName());
+ assertEquals("String", copy.getType());
+ }
+
+ @Test
+ void toStringIncludesNameAndType() {
+ ParameterField field = new ParameterField();
+ field.setName("id");
+ field.setType("Integer");
+ assertEquals("id:Integer", field.toString());
+ }
+}
diff --git
a/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/cache/DatabaseCacheTest.java
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/cache/DatabaseCacheTest.java
new file mode 100644
index 0000000000..2c789d512b
--- /dev/null
+++
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/cache/DatabaseCacheTest.java
@@ -0,0 +1,108 @@
+/*
+ * 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.pipeline.transforms.databasejoin.cache;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertSame;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import java.util.ArrayList;
+import java.util.List;
+import org.apache.hop.core.HopEnvironment;
+import org.apache.hop.core.RowMetaAndData;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+/** Unit test for {@link DatabaseCache} */
+class DatabaseCacheTest {
+
+ private IRowMeta lookupMeta;
+
+ @BeforeAll
+ static void setUpClass() throws Exception {
+ HopEnvironment.init();
+ }
+
+ @BeforeEach
+ void setUp() {
+ lookupMeta = new RowMeta();
+ lookupMeta.addValueMeta(new ValueMetaInteger("id"));
+ }
+
+ @Test
+ void newCacheIsEmpty() {
+ DatabaseCache cache = new DatabaseCache(10);
+ assertTrue(cache.isEmpty());
+ assertNull(cache.getRowsFromCache(lookupMeta, new Object[] {1L}));
+ }
+
+ @Test
+ void putAndGetByLookupRow() {
+ DatabaseCache cache = new DatabaseCache(10);
+ List<Object[]> rows = rows("one");
+ cache.putRowsIntoCache(lookupMeta, new Object[] {1L}, rows);
+
+ assertFalse(cache.isEmpty());
+ assertSame(rows, cache.getRowsFromCache(lookupMeta, new Object[] {1L}));
+ assertNull(cache.getRowsFromCache(lookupMeta, new Object[] {2L}));
+ }
+
+ @Test
+ void putAndGetByRowMetaAndData() {
+ DatabaseCache cache = new DatabaseCache(10);
+ List<Object[]> rows = rows("one");
+ cache.putRowsIntoCache(new RowMetaAndData(lookupMeta, 1L), rows);
+
+ assertSame(rows, cache.getRowsFromCache(new RowMetaAndData(lookupMeta,
1L)));
+ }
+
+ @Test
+ void maxSizeZeroDoesNotEvict() {
+ DatabaseCache cache = new DatabaseCache(0);
+ cache.putRowsIntoCache(lookupMeta, new Object[] {1L}, rows("a"));
+ cache.putRowsIntoCache(lookupMeta, new Object[] {2L}, rows("b"));
+ cache.putRowsIntoCache(lookupMeta, new Object[] {3L}, rows("c"));
+
+ assertEquals("a", cache.getRowsFromCache(lookupMeta, new Object[]
{1L}).getFirst()[0]);
+ assertEquals("b", cache.getRowsFromCache(lookupMeta, new Object[]
{2L}).getFirst()[0]);
+ assertEquals("c", cache.getRowsFromCache(lookupMeta, new Object[]
{3L}).getFirst()[0]);
+ }
+
+ @Test
+ void maxSizeEvictsEldestEntry() {
+ DatabaseCache cache = new DatabaseCache(1);
+ cache.putRowsIntoCache(lookupMeta, new Object[] {1L}, rows("a"));
+ cache.putRowsIntoCache(lookupMeta, new Object[] {2L}, rows("b"));
+
+ assertNull(cache.getRowsFromCache(lookupMeta, new Object[] {1L}));
+ assertEquals("b", cache.getRowsFromCache(lookupMeta, new Object[]
{2L}).getFirst()[0]);
+ assertFalse(cache.isEmpty());
+ }
+
+ private static List<Object[]> rows(Object value) {
+ List<Object[]> result = new ArrayList<>();
+ result.add(new Object[] {value});
+ return result;
+ }
+}
