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 2130959825 Issue #7151 : Load Execute SQL script from a file and
restyle the dialog (#8144)
2130959825 is described below
commit 2130959825c8a150d6d88f5a1948509b85efcef9
Author: Matt Casters <[email protected]>
AuthorDate: Fri Aug 28 16:15:58 2026 +0200
Issue #7151 : Load Execute SQL script from a file and restyle the dialog
(#8144)
Allow a VFS SQL file as the script source instead of only embedding SQL
in the pipeline XML. Put the Execute SQL script dialog on General, SQL,
and Parameters tabs.
---
.../execute-sql-script-dialog-general-tab.png | Bin 0 -> 74536 bytes
.../execute-sql-script-dialog-parameters-tab.png | Bin 0 -> 67609 bytes
.../execute-sql-script-dialog-sql-tab.png | Bin 0 -> 98954 bytes
.../ROOT/pages/pipeline/transforms/execsql.adoc | 55 +-
.../database/0019-execute-sqlscript-sql-file.hpl | 220 +++++++
.../database/main-0019-execute-sqlscript.hwf | 66 +++
.../database/scripts/0019-execute-sql.sql | 21 +
.../hop/pipeline/transforms/sql/ExecSql.java | 5 +-
.../transforms/sql/ExecSqlArgumentItem.java | 26 +-
.../hop/pipeline/transforms/sql/ExecSqlDialog.java | 644 ++++++++-------------
.../hop/pipeline/transforms/sql/ExecSqlMeta.java | 343 ++++++-----
...ecSqlArgumentItem.java => TypeSqlFilename.java} | 43 +-
.../sql/messages/messages_en_US.properties | 13 +
.../sql/ExecSqlDisabledWidgetsTest.java} | 46 +-
.../pipeline/transforms/sql/ExecSqlMetaTest.java | 59 ++
15 files changed, 884 insertions(+), 657 deletions(-)
diff --git
a/docs/hop-user-manual/modules/ROOT/assets/images/transforms/execute-sql-script-dialog-general-tab.png
b/docs/hop-user-manual/modules/ROOT/assets/images/transforms/execute-sql-script-dialog-general-tab.png
new file mode 100644
index 0000000000..0abe413856
Binary files /dev/null and
b/docs/hop-user-manual/modules/ROOT/assets/images/transforms/execute-sql-script-dialog-general-tab.png
differ
diff --git
a/docs/hop-user-manual/modules/ROOT/assets/images/transforms/execute-sql-script-dialog-parameters-tab.png
b/docs/hop-user-manual/modules/ROOT/assets/images/transforms/execute-sql-script-dialog-parameters-tab.png
new file mode 100644
index 0000000000..7014989976
Binary files /dev/null and
b/docs/hop-user-manual/modules/ROOT/assets/images/transforms/execute-sql-script-dialog-parameters-tab.png
differ
diff --git
a/docs/hop-user-manual/modules/ROOT/assets/images/transforms/execute-sql-script-dialog-sql-tab.png
b/docs/hop-user-manual/modules/ROOT/assets/images/transforms/execute-sql-script-dialog-sql-tab.png
new file mode 100644
index 0000000000..4e13041515
Binary files /dev/null and
b/docs/hop-user-manual/modules/ROOT/assets/images/transforms/execute-sql-script-dialog-sql-tab.png
differ
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/execsql.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/execsql.adoc
index f0664ffefa..0b08ac5c7f 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/execsql.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/execsql.adoc
@@ -33,6 +33,8 @@ The Execute SQL Script transform runs a SQL script either
once, during the initi
When used for every input row, parameters can be passed to the SQL scripts
specified in the transform.
Only in this mode, input fields are passed on to the next hop.
+The dialog is split into *General*, *SQL*, and *Parameters* tabs.
+
|
== Supported Engines
[%noheader,cols="2,1a",frame=none, role="table-supported-engines"]
@@ -46,27 +48,66 @@ Only in this mode, input fields are passed on to the next
hop.
!===
|===
-== Options
+== General
+
+The General tab holds the database connection and optional statistics output
fields.
+
+image:transforms/execute-sql-script-dialog-general-tab.png[Execute SQL script
General tab, width="90%"]
[options="header"]
|===
|Option|Description
|Transform name|Name of the transform; This name has to be unique in a single
pipeline
|Connection|Select a database connection to use
+|Field to contain insert stats|Optional: If you want to get an additional
field in our stream with the number or records that where inserted, please
define the field name here.
+|Field to contain update stats|Same as insert stats, but for updated rows.
+|Field to contain delete stats|Same as insert stats, but for deleted rows.
+|Field to contain read stats|Same as insert stats, but for read rows.
+|===
+
+== SQL
+
+The SQL tab is where you specify the script to run, either inline or from a
file.
+
+image:transforms/execute-sql-script-dialog-sql-tab.png[Execute SQL script SQL
tab, width="90%"]
+
+You can keep the script 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/script.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 script in the dialog (the previous default).
+
+Enable *Variable substitution* to substitute Hop variables in the SQL (inline
or loaded from the file) before the statement is executed.
+
+[options="header"]
+|===
+|Option|Description
+|Load SQL from file|Optional VFS path to a `.sql` file (variables in the path
are resolved). When set, the script 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.
|SQL script to execute|Specify the SQL to execute.
Separate statements by ; and use question marks as place holders for parameter.
+When *Load SQL from file* is set, this editor is a read-only preview of the
file.
|Execute for each row?|Select this option to execute the SQL for each incoming
row.
-In this case input fileds can be used as parameters and are passed on to the
output.
+In this case input fields can be used as parameters and are passed on to the
output.
When this option is unchecked, the SQL statement is executed at the transform
initialization phase and inputs fields are not processed.
|Execute as a single statement|This option does not split the statements by ;
and will send the whole SQL to the database.
|Variable substitution|In case you want to use variables in the SQL, e.g.
{openvar}table_name{closevar}, this option needs to be checked. If you are
using Parameter fields, they are only used in conjunction with "question marks"
in the SQL (see below).
-|Bind parameters?|Check this option to bind parameters using prepared
statements, otherwise this transform will perform a literal string replacement
of the parameters.
|Quote Strings?|This option adds quotes around the string according to the
database dialect and also escapes special characters like CR, LF and the quote
character itself.
+|===
+
+== Parameters
+
+The Parameters tab lists the input fields that replace question marks in the
SQL, in order.
+*Bind parameters* and the parameters table are enabled when *Execute for each
row* is selected on the SQL tab.
+Use *Get Fields* to fill the table from the previous transform.
+
+image:transforms/execute-sql-script-dialog-parameters-tab.png[Execute SQL
script Parameters tab, width="90%"]
+
+[options="header"]
+|===
+|Option|Description
+|Bind parameters?|Check this option to bind parameters using prepared
statements, otherwise this transform will perform a literal string replacement
of the parameters.
|Parameters|The list of parameters that will replace the question marks in the
query in the given order.
So the first question mark will be replaced by the first parameter, the second
question mark by the second parameter etc.
-|Field to contain insert stats|Optional: If you want to get an additional
field in our stream with the number or records that where inserted, please
define the field name here.
-|Field to contain update stats|Same as insert stats, but for updated rows.
-|Field to contain delete stats|Same as insert stats, but for deleted rows.
-|Field to contain read stats|Same as insert stats, but for read rows.
|===
diff --git a/integration-tests/database/0019-execute-sqlscript-sql-file.hpl
b/integration-tests/database/0019-execute-sqlscript-sql-file.hpl
new file mode 100644
index 0000000000..7d0472bef0
--- /dev/null
+++ b/integration-tests/database/0019-execute-sqlscript-sql-file.hpl
@@ -0,0 +1,220 @@
+<?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>0019-execute-sqlscript-sql-file</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <pipeline_status>0</pipeline_status>
+ <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>validate count</from>
+ <to>success</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>validate count</from>
+ <to>failed on count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Execute SQL script</from>
+ <to>validate count</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Execute SQL script</name>
+ <type>ExecSql</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>unit-test-db</connection>
+ <execute_each_row>N</execute_each_row>
+ <single_statement>N</single_statement>
+ <replace_variables>N</replace_variables>
+ <quoteString>N</quoteString>
+ <sql>SELECT 1</sql>
+ <sql_from_file>${PROJECT_HOME}/scripts/0019-execute-sql.sql</sql_from_file>
+ <set_params>N</set_params>
+ <insert_field>inserted</insert_field>
+ <update_field>updated</update_field>
+ <delete_field>deleted</delete_field>
+ <read_field>read</read_field>
+ <arguments>
+ </arguments>
+ <attributes/>
+ <GUI>
+ <xloc>320</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>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <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>
+ <conditions>
+ <condition>
+ <negated>N</negated>
+ <leftvalue>updated</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>0</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ <condition>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <leftvalue>deleted</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>2</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ <condition>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <leftvalue>inserted</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>2</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ <condition>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <leftvalue>read</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>0</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ </conditions>
+ </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-0019-execute-sqlscript.hwf
b/integration-tests/database/main-0019-execute-sqlscript.hwf
index 8f5f4aaa45..937162be2f 100644
--- a/integration-tests/database/main-0019-execute-sqlscript.hwf
+++ b/integration-tests/database/main-0019-execute-sqlscript.hwf
@@ -152,6 +152,58 @@ CREATE TABLE public.testtable
<yloc>48</yloc>
<attributes_hac/>
</action>
+ <action>
+ <name>Prepare Test 3</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <sql>
+DROP TABLE IF EXISTS public.testtable;
+
+CREATE TABLE public.testtable
+(
+ "key" varchar NULL,
+ value varchar NULL
+);
+</sql>
+ <useVariableSubstitution>F</useVariableSubstitution>
+ <sqlfromfile>F</sqlfromfile>
+ <sqlfilename/>
+ <sendOneStatement>T</sendOneStatement>
+ <connection>unit-test-db</connection>
+ <parallel>N</parallel>
+ <xloc>928</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0019-execute-sqlscript-sql-file</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <filename>${PROJECT_HOME}/0019-execute-sqlscript-sql-file.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <set_logfile>N</set_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <set_append_logfile>N</set_append_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <create_parent_folder>N</create_parent_folder>
+ <run_configuration>local</run_configuration>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <parallel>N</parallel>
+ <xloc>1136</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
</actions>
<hops>
<hop>
@@ -182,6 +234,20 @@ CREATE TABLE public.testtable
<evaluation>Y</evaluation>
<unconditional>Y</unconditional>
</hop>
+ <hop>
+ <from>0019-execute-sqlscript-byeachrow</from>
+ <to>Prepare Test 3</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>Prepare Test 3</from>
+ <to>0019-execute-sqlscript-sql-file</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
</hops>
<notepads>
</notepads>
diff --git a/integration-tests/database/scripts/0019-execute-sql.sql
b/integration-tests/database/scripts/0019-execute-sql.sql
new file mode 100644
index 0000000000..a18ff7f5ed
--- /dev/null
+++ b/integration-tests/database/scripts/0019-execute-sql.sql
@@ -0,0 +1,21 @@
+/*
+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.
+*/
+
+insert into public.testtable (key, value) values ('key1', 'value1');
+insert into public.testtable (key, value) values ('key2', 'value2');
+
+delete from public.testtable;
diff --git
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSql.java
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSql.java
index 7de6365ea6..7f58347f1a 100644
---
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSql.java
+++
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSql.java
@@ -294,10 +294,11 @@ public class ExecSql extends BaseTransform<ExecSqlMeta,
ExecSqlData> {
logDetailed(BaseMessages.getString(PKG,
"ExecSql.Log.ConnectedToDB"));
}
+ String sqlToUse = meta.getEffectiveSql(this);
if (meta.isReplaceVariables()) {
- data.sql = resolve(meta.getSql());
+ data.sql = resolve(sqlToUse);
} else {
- data.sql = meta.getSql();
+ data.sql = sqlToUse;
}
// If the SQL needs to be executed once, this is a starting transform
// somewhere.
diff --git
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
index eb7546a03d..59ab64fcd5 100644
---
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
+++
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
@@ -1,5 +1,3 @@
-package org.apache.hop.pipeline.transforms.sql;
-
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
@@ -17,9 +15,15 @@ package org.apache.hop.pipeline.transforms.sql;
* limitations under the License.
*/
+package org.apache.hop.pipeline.transforms.sql;
+
import java.util.Objects;
+import lombok.Getter;
+import lombok.Setter;
import org.apache.hop.metadata.api.HopMetadataProperty;
+@Getter
+@Setter
public class ExecSqlArgumentItem {
@HopMetadataProperty(
@@ -33,20 +37,16 @@ public class ExecSqlArgumentItem {
this.name = name;
}
- public String getName() {
- return name;
- }
-
- public void setName(String name) {
- this.name = name;
- }
-
@Override
public boolean equals(Object o) {
- if (this == o) return true;
- if (o == null || getClass() != o.getClass()) return false;
+ if (this == o) {
+ return true;
+ }
+ if (o == null || getClass() != o.getClass()) {
+ return false;
+ }
ExecSqlArgumentItem that = (ExecSqlArgumentItem) o;
- return name.equals(that.name);
+ return Objects.equals(name, that.name);
}
@Override
diff --git
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlDialog.java
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlDialog.java
index 7d2725ffb5..49e9b2fe68 100644
---
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlDialog.java
+++
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlDialog.java
@@ -17,15 +17,19 @@
package org.apache.hop.pipeline.transforms.sql;
+import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
+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.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;
@@ -34,12 +38,15 @@ import org.apache.hop.ui.core.PropsUi;
import org.apache.hop.ui.core.dialog.BaseDialog;
import org.apache.hop.ui.core.dialog.ErrorDialog;
import org.apache.hop.ui.core.dialog.MessageBox;
+import org.apache.hop.ui.core.gui.GuiCompositeWidgets;
+import org.apache.hop.ui.core.gui.GuiCompositeWidgetsAdapter;
import org.apache.hop.ui.core.widget.ColumnInfo;
import org.apache.hop.ui.core.widget.MetaSelectionLine;
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;
@@ -47,60 +54,34 @@ import org.eclipse.swt.events.FocusAdapter;
import org.eclipse.swt.events.FocusEvent;
import org.eclipse.swt.events.KeyAdapter;
import org.eclipse.swt.events.KeyEvent;
-import org.eclipse.swt.events.ModifyListener;
import org.eclipse.swt.events.MouseAdapter;
import org.eclipse.swt.events.MouseEvent;
-import org.eclipse.swt.events.SelectionAdapter;
-import org.eclipse.swt.events.SelectionEvent;
-import org.eclipse.swt.events.SelectionListener;
-import org.eclipse.swt.graphics.Rectangle;
import org.eclipse.swt.layout.FormAttachment;
import org.eclipse.swt.layout.FormData;
+import org.eclipse.swt.layout.FormLayout;
import org.eclipse.swt.widgets.Button;
+import org.eclipse.swt.widgets.Composite;
+import org.eclipse.swt.widgets.Control;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.TableItem;
-import org.eclipse.swt.widgets.Text;
public class ExecSqlDialog extends BaseTransformDialog {
private static final Class<?> PKG = ExecSqlMeta.class;
- private MetaSelectionLine<DatabaseMeta> wConnection;
+ private final ExecSqlMeta input;
+ private GuiCompositeWidgets widgets;
private TextComposite wSql;
-
+ private Label wlPosition;
private Button wEachRow;
-
- private Label wlSetParams;
- private Button wSetParams;
-
private Button wSingleStatement;
-
- private Text wInsertField;
-
- private Text wUpdateField;
-
- private Text wDeleteField;
-
- private Text wReadField;
-
- private Label wlFields;
-
- private TableView wFields;
-
private Button wVariables;
-
- private Label wlQuoteString;
private Button wQuoteString;
-
- private final ExecSqlMeta input;
- private boolean changedInDialog;
-
- private Label wlPosition;
-
- private final List<String> inputFields = new ArrayList<>();
-
+ private Label wlFields;
+ private TableView wFields;
private ColumnInfo[] colinf;
+ private final List<String> inputFields = new ArrayList<>();
public ExecSqlDialog(
Shell parent, IVariables variables, ExecSqlMeta transformMeta,
PipelineMeta pipelineMeta) {
@@ -112,63 +93,151 @@ public class ExecSqlDialog extends BaseTransformDialog {
public String open() {
createShell(BaseMessages.getString(PKG, "ExecSqlDialog.Shell.Label"));
- buildButtonBar().ok(e -> ok()).get(e -> get()).cancel(e ->
cancel()).build();
+ changed = input.hasChanged();
- ModifyListener lsMod =
- e -> {
- changedInDialog = true;
- input.setChanged();
- };
+ buildButtonBar().ok(e -> ok()).get(e -> get()).cancel(e ->
cancel()).build();
- SelectionAdapter lsSel =
- new SelectionAdapter() {
+ Composite area = new Composite(shell, SWT.NONE);
+ PropsUi.setLook(area);
+ area.setLayout(new FormLayout());
+ FormData fdArea = new FormData();
+ fdArea.left = new FormAttachment(0, 0);
+ fdArea.top = new FormAttachment(wSpacer, margin);
+ fdArea.right = new FormAttachment(100, 0);
+ fdArea.bottom = new FormAttachment(wOk, -2 * margin);
+ area.setLayoutData(fdArea);
+
+ widgets = new GuiCompositeWidgets(variables);
+ widgets.registerExtraGroup(
+ BaseMessages.getString(PKG, "ExecSqlMeta.Group.SQL"),
+ "20",
+ null,
+ this::addSqlEditorAndOptions);
+ widgets.registerExtraGroup(
+ BaseMessages.getString(PKG, "ExecSqlMeta.Group.Parameters"),
+ "30",
+ null,
+ this::addParametersTable);
+ widgets.setWidgetsListener(
+ new GuiCompositeWidgetsAdapter() {
@Override
- public void widgetSelected(SelectionEvent e) {
- input.setChanged();
+ public void widgetModified(
+ GuiCompositeWidgets compositeWidgets, Control changedWidget,
String widgetId) {
+ if (!loading) {
+ input.setChanged();
+ }
+ if (ExecSqlMeta.WIDGET_SQL_FROM_FILE.equals(widgetId)) {
+ loadSqlFromFileAndSetReadOnly(false);
+ }
+ if (ExecSqlMeta.WIDGET_BIND_PARAMETERS.equals(widgetId)) {
+ setExecutedSetParams();
+ }
}
- };
-
- changed = input.hasChanged();
+ });
+ widgets.createCompositeWidgets(
+ input, null, area, ExecSqlMeta.GUI_PLUGIN_ELEMENT_PARENT_ID, null);
- SelectionListener lsSelection =
- new SelectionAdapter() {
- @Override
- public void widgetSelected(SelectionEvent e) {
- input.setChanged();
+ final Runnable runnable =
+ () -> {
+ TransformMeta transformMeta =
pipelineMeta.findTransform(transformName);
+ if (transformMeta != null) {
+ try {
+ IRowMeta row = pipelineMeta.getPrevTransformFields(variables,
transformMeta);
+ for (int i = 0; i < row.size(); i++) {
+ inputFields.add(row.getValueMeta(i).getName());
+ }
+ setComboBoxes();
+ } catch (HopException e) {
+ logError(BaseMessages.getString(PKG,
"System.Dialog.GetFieldsFailed.Message"));
+ }
}
};
+ new Thread(runnable).start();
+
+ getData();
+ setExecutedEachInputRow();
+ setExecutedSetParams();
+ input.setChanged(changed);
+ focusTransformName();
+ BaseDialog.defaultShellHandling(shell, c -> ok(), c -> cancel());
+
+ return transformName;
+ }
- // Connection line
- wConnection = addConnectionLine(shell, wSpacer, input.getConnection(),
lsMod);
- wConnection.addSelectionListener(lsSelection);
- wConnection.addListener(SWT.Selection, e -> getSqlReservedWords());
+ private void addSqlEditorAndOptions(Composite parent) {
+ Control last =
widgets.getWidgetsMap().get(ExecSqlMeta.WIDGET_SQL_FROM_FILE);
- // Table line...
- Label wlSql = new Label(shell, SWT.LEFT);
+ Label wlSql = new Label(parent, SWT.LEFT);
wlSql.setText(BaseMessages.getString(PKG, "ExecSqlDialog.SQL.Label"));
PropsUi.setLook(wlSql);
FormData fdlSql = new FormData();
fdlSql.left = new FormAttachment(0, 0);
- fdlSql.top = new FormAttachment(wConnection, margin);
+ fdlSql.top = last == null ? new FormAttachment(0, margin) : new
FormAttachment(last, margin);
wlSql.setLayoutData(fdlSql);
+ wQuoteString = new Button(parent, SWT.CHECK);
+ wQuoteString.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.QuoteString.Label"));
+ wQuoteString.setToolTipText(BaseMessages.getString(PKG,
"ExecSqlDialog.QuoteString.Tooltip"));
+ PropsUi.setLook(wQuoteString);
+ wQuoteString.addListener(SWT.Selection, e -> input.setChanged());
+ FormData fdQuoteString = new FormData();
+ fdQuoteString.left = new FormAttachment(0, 0);
+ fdQuoteString.bottom = new FormAttachment(100, 0);
+ wQuoteString.setLayoutData(fdQuoteString);
+
+ wVariables = new Button(parent, SWT.CHECK);
+ wVariables.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.ReplaceVariables"));
+ PropsUi.setLook(wVariables);
+ wVariables.addListener(SWT.Selection, e -> input.setChanged());
+ FormData fdVariables = new FormData();
+ fdVariables.left = new FormAttachment(0, 0);
+ fdVariables.bottom = new FormAttachment(wQuoteString, -margin);
+ wVariables.setLayoutData(fdVariables);
+
+ wSingleStatement = new Button(parent, SWT.CHECK);
+ wSingleStatement.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.SingleStatement.Label"));
+ PropsUi.setLook(wSingleStatement);
+ wSingleStatement.addListener(SWT.Selection, e -> input.setChanged());
+ FormData fdSingleStatement = new FormData();
+ fdSingleStatement.left = new FormAttachment(0, 0);
+ fdSingleStatement.bottom = new FormAttachment(wVariables, -margin);
+ wSingleStatement.setLayoutData(fdSingleStatement);
+
+ wEachRow = new Button(parent, SWT.CHECK);
+ wEachRow.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.EachRow.Label"));
+ PropsUi.setLook(wEachRow);
+ wEachRow.addListener(
+ SWT.Selection,
+ e -> {
+ setExecutedEachInputRow();
+ input.setChanged();
+ });
+ FormData fdEachRow = new FormData();
+ fdEachRow.left = new FormAttachment(0, 0);
+ fdEachRow.bottom = new FormAttachment(wSingleStatement, -margin);
+ wEachRow.setLayoutData(fdEachRow);
+
+ wlPosition = new Label(parent, SWT.NONE);
+ PropsUi.setLook(wlPosition);
+ FormData fdlPosition = new FormData();
+ fdlPosition.left = new FormAttachment(0, 0);
+ fdlPosition.right = new FormAttachment(100, 0);
+ fdlPosition.bottom = new FormAttachment(wEachRow, -margin);
+ wlPosition.setLayoutData(fdlPosition);
+
wSql =
EnvironmentUtils.getInstance().isWeb()
? new StyledTextComp(
variables,
- shell,
+ parent,
SWT.MULTI | SWT.LEFT | SWT.BORDER | SWT.H_SCROLL |
SWT.V_SCROLL,
TextComposite.STYLE_TYPE_SQL)
: new SQLStyledTextComp(
- variables, shell, SWT.MULTI | SWT.LEFT | SWT.BORDER |
SWT.H_SCROLL | SWT.V_SCROLL);
- final List<String> sqlKeywords = getSqlReservedWords();
-
- wSql.addLineStyleListener(sqlKeywords);
-
+ variables, parent, SWT.MULTI | SWT.LEFT | SWT.BORDER |
SWT.H_SCROLL | SWT.V_SCROLL);
+ wSql.addLineStyleListener(getSqlReservedWords());
PropsUi.setLook(wSql, Props.WIDGET_STYLE_FIXED);
wSql.addModifyListener(lsMod);
wSql.addModifyListener(arg0 -> setPosition());
-
wSql.addKeyListener(
new KeyAdapter() {
@Override
@@ -210,99 +279,28 @@ public class ExecSqlDialog extends BaseTransformDialog {
setPosition();
}
});
+ FormData fdSql = new FormData();
+ fdSql.left = new FormAttachment(0, 0);
+ fdSql.top = new FormAttachment(wlSql, margin);
+ fdSql.right = new FormAttachment(100, 0);
+ fdSql.bottom = new FormAttachment(wlPosition, -margin);
+ fdSql.height = 200;
+ wSql.setLayoutData(fdSql);
+
+ setPosition();
+ }
- // Build it up from the bottom up...
- // Read field
- //
- Label wlReadField = new Label(shell, SWT.RIGHT);
- wlReadField.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.ReadField.Label"));
- PropsUi.setLook(wlReadField);
- FormData fdlReadField = new FormData();
- fdlReadField.left = new FormAttachment(middle, margin);
- fdlReadField.right = new FormAttachment(middle * 2, -margin);
- fdlReadField.bottom = new FormAttachment(wOk, -3 * margin);
- wlReadField.setLayoutData(fdlReadField);
- wReadField = new Text(shell, SWT.SINGLE | SWT.LEFT | SWT.BORDER);
- PropsUi.setLook(wReadField);
- wReadField.addModifyListener(lsMod);
- FormData fdReadField = new FormData();
- fdReadField.left = new FormAttachment(middle * 2, 0);
- fdReadField.bottom = new FormAttachment(wOk, -3 * margin);
- fdReadField.right = new FormAttachment(100, 0);
- wReadField.setLayoutData(fdReadField);
-
- // Delete field
- //
- Label wlDeleteField = new Label(shell, SWT.RIGHT);
- wlDeleteField.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.DeleteField.Label"));
- PropsUi.setLook(wlDeleteField);
- FormData fdlDeleteField = new FormData();
- fdlDeleteField.left = new FormAttachment(middle, margin);
- fdlDeleteField.right = new FormAttachment(middle * 2, -margin);
- fdlDeleteField.bottom = new FormAttachment(wReadField, -margin);
- wlDeleteField.setLayoutData(fdlDeleteField);
- wDeleteField = new Text(shell, SWT.SINGLE | SWT.LEFT | SWT.BORDER);
- PropsUi.setLook(wDeleteField);
- wDeleteField.addModifyListener(lsMod);
- FormData fdDeleteField = new FormData();
- fdDeleteField.left = new FormAttachment(middle * 2, 0);
- fdDeleteField.bottom = new FormAttachment(wReadField, -margin);
- fdDeleteField.right = new FormAttachment(100, 0);
- wDeleteField.setLayoutData(fdDeleteField);
-
- // Update field
- //
- Label wlUpdateField = new Label(shell, SWT.RIGHT);
- wlUpdateField.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.UpdateField.Label"));
- PropsUi.setLook(wlUpdateField);
- FormData fdlUpdateField = new FormData();
- fdlUpdateField.left = new FormAttachment(middle, margin);
- fdlUpdateField.right = new FormAttachment(middle * 2, -margin);
- fdlUpdateField.bottom = new FormAttachment(wDeleteField, -margin);
- wlUpdateField.setLayoutData(fdlUpdateField);
- wUpdateField = new Text(shell, SWT.SINGLE | SWT.LEFT | SWT.BORDER);
- PropsUi.setLook(wUpdateField);
- wUpdateField.addModifyListener(lsMod);
- FormData fdUpdateField = new FormData();
- fdUpdateField.left = new FormAttachment(middle * 2, 0);
- fdUpdateField.bottom = new FormAttachment(wDeleteField, -margin);
- fdUpdateField.right = new FormAttachment(100, 0);
- wUpdateField.setLayoutData(fdUpdateField);
-
- // insert field
- //
- Label wlInsertField = new Label(shell, SWT.RIGHT);
- wlInsertField.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.InsertField.Label"));
- PropsUi.setLook(wlInsertField);
- FormData fdlInsertField = new FormData();
- fdlInsertField.left = new FormAttachment(middle, margin);
- fdlInsertField.right = new FormAttachment(middle * 2, -margin);
- fdlInsertField.bottom = new FormAttachment(wUpdateField, -margin);
- wlInsertField.setLayoutData(fdlInsertField);
- wInsertField = new Text(shell, SWT.SINGLE | SWT.LEFT | SWT.BORDER);
- PropsUi.setLook(wInsertField);
- wInsertField.addModifyListener(lsMod);
- FormData fdInsertField = new FormData();
- fdInsertField.left = new FormAttachment(middle * 2, 0);
- fdInsertField.bottom = new FormAttachment(wUpdateField, -margin);
- fdInsertField.right = new FormAttachment(100, 0);
- wInsertField.setLayoutData(fdInsertField);
-
- // Setup the "Parameters" label
- //
- wlFields = new Label(shell, SWT.NONE);
+ private void addParametersTable(Composite parent) {
+ Control last =
widgets.getWidgetsMap().get(ExecSqlMeta.WIDGET_BIND_PARAMETERS);
+
+ wlFields = new Label(parent, SWT.NONE);
wlFields.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.Fields.Label"));
PropsUi.setLook(wlFields);
FormData fdlFields = new FormData();
fdlFields.left = new FormAttachment(0, 0);
- fdlFields.right = new FormAttachment(middle, 0);
- fdlFields.bottom = new FormAttachment(wInsertField, -25);
+ fdlFields.top = last == null ? new FormAttachment(0, margin) : new
FormAttachment(last, margin);
wlFields.setLayoutData(fdlFields);
- // Parameter fields...
- //
- final int FieldsRows = input.getArguments().size();
-
colinf =
new ColumnInfo[] {
new ColumnInfo(
@@ -315,244 +313,92 @@ public class ExecSqlDialog extends BaseTransformDialog {
wFields =
new TableView(
variables,
- shell,
+ parent,
SWT.BORDER | SWT.FULL_SELECTION | SWT.MULTI,
colinf,
- FieldsRows,
+ input.getArguments().size(),
lsMod,
props);
FormData fdFields = new FormData();
fdFields.left = new FormAttachment(0, 0);
fdFields.top = new FormAttachment(wlFields, margin);
- fdFields.right = new FormAttachment(middle, 0);
- fdFields.bottom = new FormAttachment(wOk, -3 * margin);
+ fdFields.right = new FormAttachment(100, 0);
+ fdFields.bottom = new FormAttachment(100, 0);
+ fdFields.height = 150;
wFields.setLayoutData(fdFields);
-
- // For the "execute for each row" and "variable substitution" labels,
- // find their maximum width
- // and use that in the alignment
- //
- Label wlEachRow = new Label(shell, SWT.RIGHT);
- wlEachRow.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.EachRow.Label"));
- wlEachRow.pack();
- Label wlSingleStatement = new Label(shell, SWT.RIGHT);
- wlSingleStatement.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.SingleStatement.Label"));
- wlSingleStatement.pack();
- Label wlVariables = new Label(shell, SWT.RIGHT);
- wlVariables.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.ReplaceVariables"));
- wlVariables.pack();
- wlQuoteString = new Label(shell, SWT.RIGHT);
- wlQuoteString.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.QuoteString.Label"));
- wlQuoteString.pack();
- Rectangle rEachRow = wlEachRow.getBounds();
- Rectangle rSingleStatement = wlSingleStatement.getBounds();
- Rectangle rVariables = wlVariables.getBounds();
- Rectangle rQuoteString = wlQuoteString.getBounds();
- int width =
- Math.max(
- Math.max(Math.max(rEachRow.width, rSingleStatement.width),
rVariables.width),
- rQuoteString.width)
- + 30;
-
- // Setup the "Quote String" label and checkbox
- //
- PropsUi.setLook(wlQuoteString);
- FormData fdlQuoteString = new FormData();
- fdlQuoteString.left = new FormAttachment(0, margin);
- fdlQuoteString.right = new FormAttachment(0, width);
- fdlQuoteString.bottom = new FormAttachment(wlFields, -margin);
- wlQuoteString.setLayoutData(fdlQuoteString);
- wQuoteString = new Button(shell, SWT.CHECK);
- PropsUi.setLook(wQuoteString);
- wQuoteString.setToolTipText(BaseMessages.getString(PKG,
"ExecSqlDialog.QuoteString.Tooltip"));
- FormData fdQuoteString = new FormData();
- fdQuoteString.left = new FormAttachment(wlQuoteString, margin);
- fdQuoteString.top = new FormAttachment(wlQuoteString, 0, SWT.CENTER);
- fdQuoteString.right = new FormAttachment(middle, 0);
- wQuoteString.setLayoutData(fdQuoteString);
- wQuoteString.addSelectionListener(lsSel);
-
- // Setup the "Bind parameters" label and checkbox
- //
- wlSetParams = new Label(this.shell, SWT.RIGHT);
- wlSetParams.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.SetParams.Label"));
- PropsUi.setLook(this.wlSetParams);
- FormData fdlSetParams = new FormData();
- fdlSetParams.left = new FormAttachment(0, margin);
- fdlSetParams.bottom = new FormAttachment(wQuoteString, -margin);
- fdlSetParams.right = new FormAttachment(0, width);
- wlSetParams.setLayoutData(fdlSetParams);
- wSetParams = new Button(shell, SWT.CHECK);
- PropsUi.setLook(this.wSetParams);
- wSetParams.setToolTipText(BaseMessages.getString(PKG,
"ExecSqlDialog.SetParams.Tooltip"));
- FormData fdSetParams = new FormData();
- fdSetParams.left = new FormAttachment(wlSetParams, margin);
- fdSetParams.top = new FormAttachment(wlSetParams, 0, SWT.CENTER);
- fdSetParams.right = new FormAttachment(middle, 0);
- wSetParams.setLayoutData(fdSetParams);
- wSetParams.addSelectionListener(
- new SelectionAdapter() {
- @Override
- public void widgetSelected(SelectionEvent e) {
- setExecutedSetParams();
- input.setChanged();
- }
- });
-
- // Setup the "variable substitution" label and checkbox
- //
- PropsUi.setLook(wlVariables);
- FormData fdlVariables = new FormData();
- fdlVariables.left = new FormAttachment(0, margin);
- fdlVariables.right = new FormAttachment(0, width);
- fdlVariables.bottom = new FormAttachment(wSetParams, -margin);
- wlVariables.setLayoutData(fdlVariables);
- wVariables = new Button(shell, SWT.CHECK);
- PropsUi.setLook(wVariables);
- FormData fdVariables = new FormData();
- fdVariables.left = new FormAttachment(wlVariables, margin);
- fdVariables.top = new FormAttachment(wlVariables, 0, SWT.CENTER);
- fdVariables.right = new FormAttachment(middle, 0);
- wVariables.setLayoutData(fdVariables);
- wVariables.addSelectionListener(lsSel);
-
- // Setup the "Single statement" label and checkbox
- //
- PropsUi.setLook(wlSingleStatement);
- FormData fdlSingleStatement = new FormData();
- fdlSingleStatement.left = new FormAttachment(0, margin);
- fdlSingleStatement.right = new FormAttachment(0, width);
- fdlSingleStatement.bottom = new FormAttachment(wVariables, -margin);
- wlSingleStatement.setLayoutData(fdlSingleStatement);
- wSingleStatement = new Button(shell, SWT.CHECK);
- PropsUi.setLook(wSingleStatement);
- FormData fdSingleStatement = new FormData();
- fdSingleStatement.left = new FormAttachment(wlEachRow, margin);
- fdSingleStatement.top = new FormAttachment(wlSingleStatement, 0,
SWT.CENTER);
- fdSingleStatement.right = new FormAttachment(middle, 0);
- wSingleStatement.setLayoutData(fdSingleStatement);
- wSingleStatement.addSelectionListener(lsSel);
-
- // Setup the "execute for each row" label and checkbox
- //
- PropsUi.setLook(wlEachRow);
- FormData fdlEachRow = new FormData();
- fdlEachRow.left = new FormAttachment(0, margin);
- fdlEachRow.right = new FormAttachment(0, width);
- fdlEachRow.bottom = new FormAttachment(wSingleStatement, -margin);
- wlEachRow.setLayoutData(fdlEachRow);
- wEachRow = new Button(shell, SWT.CHECK);
- PropsUi.setLook(wEachRow);
- FormData fdEachRow = new FormData();
- fdEachRow.left = new FormAttachment(wlEachRow, margin);
- fdEachRow.top = new FormAttachment(wlEachRow, 0, SWT.CENTER);
- fdEachRow.right = new FormAttachment(middle, 0);
- wEachRow.setLayoutData(fdEachRow);
- wEachRow.addSelectionListener(lsSel);
-
- // Position label under the SQL editor
- //
- wlPosition = new Label(shell, SWT.NONE);
- PropsUi.setLook(wlPosition);
- FormData fdlPosition = new FormData();
- fdlPosition.left = new FormAttachment(0, 0);
- fdlPosition.right = new FormAttachment(100, 0);
- fdlPosition.bottom =
- new FormAttachment(wEachRow, -margin); // 2 times since we deal with
bottom instead of
- // top
- wlPosition.setLayoutData(fdlPosition);
-
- // Finally, the SQL editor takes up all other variables between the
position and the SQL label
- //
- FormData fdSql = new FormData();
- fdSql.left = new FormAttachment(0, 0);
- fdSql.top = new FormAttachment(wlSql, margin);
- fdSql.right = new FormAttachment(100, -margin);
- fdSql.bottom = new FormAttachment(wlPosition, -margin);
- fdSql.height = 200;
- wSql.setLayoutData(fdSql);
-
- // Search the fields in the background
- //
- final Runnable runnable =
- () -> {
- TransformMeta transformMeta =
pipelineMeta.findTransform(transformName);
- if (transformMeta != null) {
- try {
- IRowMeta row = pipelineMeta.getPrevTransformFields(variables,
transformMeta);
-
- // Remember these fields...
- for (int i = 0; i < row.size(); i++) {
- inputFields.add(row.getValueMeta(i).getName());
- }
- setComboBoxes();
- } catch (HopException e) {
- logError(BaseMessages.getString(PKG,
"System.Dialog.GetFieldsFailed.Message"));
- }
- }
- };
- new Thread(runnable).start();
-
- // Add listeners
- //
- wEachRow.addSelectionListener(
- new SelectionAdapter() {
- @Override
- public void widgetSelected(SelectionEvent e) {
- ExecSqlDialog.this.setExecutedEachInputRow();
- ExecSqlDialog.this.input.setChanged();
- }
- });
-
- getData();
- setExecutedEachInputRow();
- setExecutedSetParams();
- changedInDialog = false; // for prompting if dialog is simply closed
- input.setChanged(changed);
- focusTransformName();
- BaseDialog.defaultShellHandling(shell, c -> ok(), c -> cancel());
-
- return transformName;
}
private List<String> getSqlReservedWords() {
- // Do not search keywords when connection is empty
- if (Utils.isEmpty(input.getConnection())) {
+ String connectionName = getConnectionName();
+ if (Utils.isEmpty(connectionName)) {
return List.of();
}
-
- // If connection is a variable that can't be resolved
- if (variables.resolve(input.getConnection()).startsWith("${")) {
+ if (variables.resolve(connectionName).startsWith("${")) {
return List.of();
}
-
- DatabaseMeta databaseMeta =
pipelineMeta.findDatabase(input.getConnection(), variables);
+ DatabaseMeta databaseMeta = pipelineMeta.findDatabase(connectionName,
variables);
if (databaseMeta == null) {
return List.of();
}
return Arrays.stream(databaseMeta.getReservedWords()).toList();
}
- private void setExecutedEachInputRow() {
+ private String getConnectionName() {
+ Control control =
widgets.getWidgetsMap().get(ExecSqlMeta.WIDGET_CONNECTION);
+ if (control instanceof MetaSelectionLine<?> line &&
!Utils.isEmpty(line.getText())) {
+ return line.getText();
+ }
+ return input.getConnection();
+ }
- wlFields.setEnabled(wEachRow.getSelection());
- wFields.setEnabled(wEachRow.getSelection());
- wlSetParams.setEnabled(wEachRow.getSelection());
- wSetParams.setEnabled(wEachRow.getSelection());
- if (!wEachRow.getSelection()) {
- wSetParams.setSelection(wEachRow.getSelection());
+ private String getSqlFromFilePath() {
+ Control control =
widgets.getWidgetsMap().get(ExecSqlMeta.WIDGET_SQL_FROM_FILE);
+ if (control instanceof TextVar textVar) {
+ return textVar.getText();
}
+ return Const.NVL(input.getSqlFromFile(), "");
+ }
- if (!wEachRow.getSelection()) {
- wQuoteString.setSelection(wEachRow.getSelection());
+ private Button getBindParametersButton() {
+ Control control =
widgets.getWidgetsMap().get(ExecSqlMeta.WIDGET_BIND_PARAMETERS);
+ if (control instanceof Button button) {
+ return button;
+ }
+ return null;
+ }
+
+ private void setExecutedEachInputRow() {
+ if (wEachRow == null || wFields == null) {
+ return;
+ }
+ boolean eachRow = wEachRow.getSelection();
+ wlFields.setEnabled(eachRow);
+ wFields.setEnabled(eachRow);
+ Button wSetParams = getBindParametersButton();
+ Control wlSetParams =
widgets.getLabelsMap().get(ExecSqlMeta.WIDGET_BIND_PARAMETERS);
+ if (wSetParams != null) {
+ wSetParams.setEnabled(eachRow);
+ }
+ if (wlSetParams != null) {
+ wlSetParams.setEnabled(eachRow);
+ }
+ if (!eachRow) {
+ if (wSetParams != null) {
+ wSetParams.setSelection(false);
+ }
+ wQuoteString.setSelection(false);
}
}
private void setExecutedSetParams() {
- wlQuoteString.setEnabled(!wSetParams.getSelection());
- wQuoteString.setEnabled(!wSetParams.getSelection());
- if (wSetParams.getSelection()) {
- wQuoteString.setSelection(!wSetParams.getSelection());
+ if (wQuoteString == null) {
+ return;
+ }
+ Button wSetParams = getBindParametersButton();
+ boolean bind = wSetParams != null && wSetParams.getSelection();
+ wQuoteString.setEnabled(!bind);
+ if (bind) {
+ wQuoteString.setSelection(false);
}
}
@@ -565,46 +411,58 @@ public class ExecSqlDialog extends BaseTransformDialog {
}
protected void setComboBoxes() {
- // Something was changed in the row.
- //
String[] fieldNames = ConstUi.sortFieldNames(inputFields);
colinf[0].setComboValues(fieldNames);
}
- /** Copy information from the meta-data input to the dialog fields. */
- public void getData() {
- if (input.getSql() != null) {
- wSql.setText(input.getSql());
+ private void loadSqlFromFileAndSetReadOnly(boolean warnOnError) {
+ String path = variables.resolve(getSqlFromFilePath());
+ if (Utils.isEmpty(path)) {
+ wSql.setEditable(true);
+ return;
}
- if (input.getConnection() != null) {
- wConnection.setText(input.getConnection());
+ try {
+ String content = HopVfs.getTextFileContent(path, StandardCharsets.UTF_8);
+ wSql.setText(content);
+ wSql.setEditable(false);
+ } catch (HopFileException e) {
+ wSql.setEditable(true);
+ if (warnOnError) {
+ MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_WARNING);
+ mb.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.DialogCaptionError"));
+ mb.setMessage(
+ BaseMessages.getString(PKG,
"ExecSqlDialog.CouldNotLoadSqlFromFile", path)
+ + Const.CR
+ + e.getMessage());
+ mb.open();
+ }
}
+ }
+
+ /** Copy information from the meta-data input to the dialog fields. */
+ public void getData() {
+ widgets.setWidgetsContents(input, shell,
ExecSqlMeta.GUI_PLUGIN_ELEMENT_PARENT_ID);
+ wSql.setText(Const.NVL(input.getSql(), ""));
wEachRow.setSelection(input.isExecutedEachInputRow());
wSingleStatement.setSelection(input.isSingleStatement());
wVariables.setSelection(input.isReplaceVariables());
wQuoteString.setSelection(input.isQuoteString());
- if (input.getUpdateField() != null) {
- wUpdateField.setText(input.getUpdateField());
- }
- if (input.getInsertField() != null) {
- wInsertField.setText(input.getInsertField());
- }
- if (input.getDeleteField() != null) {
- wDeleteField.setText(input.getDeleteField());
- }
- if (input.getReadField() != null) {
- wReadField.setText(input.getReadField());
- }
-
for (int i = 0; i < input.getArguments().size(); i++) {
TableItem item = wFields.table.getItem(i);
ExecSqlArgumentItem arg = input.getArguments().get(i);
if (arg != null) {
- item.setText(1, arg.getName());
+ item.setText(1, Const.NVL(arg.getName(), ""));
}
}
- wSetParams.setSelection(input.isParams());
+ wFields.setRowNums();
+ wFields.optWidth(true);
+
+ if (!Utils.isEmpty(getSqlFromFilePath())) {
+ loadSqlFromFileAndSetReadOnly(true);
+ } else {
+ wSql.setEditable(true);
+ }
}
private void cancel() {
@@ -618,23 +476,17 @@ public class ExecSqlDialog extends BaseTransformDialog {
return;
}
- transformName = wTransformName.getText(); // return value
- // copy info to TextFileInputMeta class (input)
+ transformName = wTransformName.getText();
+ widgets.getWidgetsContents(input,
ExecSqlMeta.GUI_PLUGIN_ELEMENT_PARENT_ID);
input.setSql(wSql.getText());
- input.setConnection(wConnection.getText());
input.setExecutedEachInputRow(wEachRow.getSelection());
input.setSingleStatement(wSingleStatement.getSelection());
input.setReplaceVariables(wVariables.getSelection());
input.setQuoteString(wQuoteString.getSelection());
- input.setParams(wSetParams.getSelection());
- input.setInsertField(wInsertField.getText());
- input.setUpdateField(wUpdateField.getText());
- input.setDeleteField(wDeleteField.getText());
- input.setReadField(wReadField.getText());
int nrargs = wFields.nrNonEmpty();
if (log.isDebug()) {
- logDebug(BaseMessages.getString(PKG, "ExecSqlDialog.Log.FoundArguments",
+nrargs + ""));
+ logDebug(BaseMessages.getString(PKG, "ExecSqlDialog.Log.FoundArguments",
nrargs + ""));
}
input.getArguments().clear();
@@ -643,7 +495,7 @@ public class ExecSqlDialog extends BaseTransformDialog {
input.getArguments().add(new ExecSqlArgumentItem(item.getText(1)));
}
- if (input.getConnection() == null) {
+ if (Utils.isEmpty(input.getConnection())) {
MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_ERROR);
mb.setMessage(BaseMessages.getString(PKG,
"ExecSqlDialog.InvalidConnection.DialogMessage"));
mb.setText(BaseMessages.getString(PKG,
"ExecSqlDialog.InvalidConnection.DialogTitle"));
diff --git
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlMeta.java
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlMeta.java
index 69f18a65c8..3c5c27f05c 100644
---
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlMeta.java
+++
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlMeta.java
@@ -17,8 +17,11 @@
package org.apache.hop.pipeline.transforms.sql;
+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;
@@ -29,10 +32,16 @@ import org.apache.hop.core.annotations.Transform;
import org.apache.hop.core.database.Database;
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.exception.HopTransformException;
+import org.apache.hop.core.gui.plugin.GuiElementType;
+import org.apache.hop.core.gui.plugin.GuiPlugin;
+import org.apache.hop.core.gui.plugin.GuiWidgetElement;
+import org.apache.hop.core.gui.plugin.GuiWidgetGroupType;
import org.apache.hop.core.row.IRowMeta;
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;
@@ -55,51 +64,135 @@ import org.apache.hop.pipeline.transform.TransformMeta;
keywords = "i18n::ExecSqlMeta.keyword",
documentationUrl = "/pipeline/transforms/execsql.html",
actionTransformTypes = {ActionTransformType.RDBMS})
+@GuiPlugin
+@Getter
+@Setter
public class ExecSqlMeta extends BaseTransformMeta<ExecSql, ExecSqlData> {
private static final Class<?> PKG = ExecSqlMeta.class;
+ public static final String GUI_PLUGIN_ELEMENT_PARENT_ID =
"EXEC_SQL_DIALOG_OPTIONS";
+ public static final String WIDGET_CONNECTION = "CONNECTION";
+ public static final String WIDGET_INSERT_FIELD = "INSERT_FIELD";
+ public static final String WIDGET_UPDATE_FIELD = "UPDATE_FIELD";
+ public static final String WIDGET_DELETE_FIELD = "DELETE_FIELD";
+ public static final String WIDGET_READ_FIELD = "READ_FIELD";
+ public static final String WIDGET_SQL_FROM_FILE = "SQL_FROM_FILE";
+ public static final String WIDGET_BIND_PARAMETERS = "BIND_PARAMETERS";
+
+ public static final String GROUP_GENERAL = "i18n::ExecSqlMeta.Group.General";
+ public static final String GROUP_SQL = "i18n::ExecSqlMeta.Group.SQL";
+ public static final String GROUP_PARAMETERS =
"i18n::ExecSqlMeta.Group.Parameters";
+
+ @GuiWidgetElement(
+ id = WIDGET_CONNECTION,
+ order = "0100",
+ type = GuiElementType.METADATA,
+ metadata = DatabaseMeta.class,
+ label = "i18n::ExecSqlMeta.Connection.Label",
+ toolTip = "i18n::ExecSqlMeta.Connection.Tooltip",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS)
@HopMetadataProperty(
key = "connection",
injectionKeyDescription = "ExecSqlMeta.Injection.CONNECTIONNAME",
hopMetadataPropertyType = HopMetadataPropertyType.RDBMS_CONNECTION)
private String connection;
+ @GuiWidgetElement(
+ id = WIDGET_INSERT_FIELD,
+ order = "0200",
+ type = GuiElementType.TEXT,
+ label = "i18n::ExecSqlDialog.InsertField.Label",
+ toolTip = "i18n::ExecSqlMeta.Injection.INSERT_STATS_FIELD",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS)
@HopMetadataProperty(
- injectionKeyDescription = "ExecSqlMeta.Injection.SQL",
- injectionKey = "SQL",
- hopMetadataPropertyType = HopMetadataPropertyType.RDBMS_SQL)
- private String sql;
-
- @HopMetadataProperty(
- key = "execute_each_row",
- injectionKeyDescription = "ExecSqlMeta.Injection.EXECUTE_FOR_EACH_ROW",
- injectionKey = "EXECUTE_FOR_EACH_ROW")
- private boolean executedEachInputRow;
+ key = "insert_field",
+ injectionKeyDescription = "ExecSqlMeta.Injection.INSERT_STATS_FIELD",
+ injectionKey = "INSERT_STATS_FIELD")
+ private String insertField;
+ @GuiWidgetElement(
+ id = WIDGET_UPDATE_FIELD,
+ order = "0300",
+ type = GuiElementType.TEXT,
+ label = "i18n::ExecSqlDialog.UpdateField.Label",
+ toolTip = "i18n::ExecSqlMeta.Injection.UPDATE_STATS_FIELD",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS)
@HopMetadataProperty(
key = "update_field",
injectionKeyDescription = "ExecSqlMeta.Injection.UPDATE_STATS_FIELD",
injectionKey = "UPDATE_STATS_FIELD")
private String updateField;
- @HopMetadataProperty(
- key = "insert_field",
- injectionKeyDescription = "ExecSqlMeta.Injection.INSERT_STATS_FIELD",
- injectionKey = "INSERT_STATS_FIELD")
- private String insertField;
-
+ @GuiWidgetElement(
+ id = WIDGET_DELETE_FIELD,
+ order = "0400",
+ type = GuiElementType.TEXT,
+ label = "i18n::ExecSqlDialog.DeleteField.Label",
+ toolTip = "i18n::ExecSqlMeta.Injection.DELETE_STATS_FIELD",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS)
@HopMetadataProperty(
key = "delete_field",
injectionKeyDescription = "ExecSqlMeta.Injection.DELETE_STATS_FIELD",
injectionKey = "DELETE_STATS_FIELD")
private String deleteField;
+ @GuiWidgetElement(
+ id = WIDGET_READ_FIELD,
+ order = "0500",
+ type = GuiElementType.TEXT,
+ label = "i18n::ExecSqlDialog.ReadField.Label",
+ toolTip = "i18n::ExecSqlMeta.Injection.READ_STATS_FIELD",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS)
@HopMetadataProperty(
key = "read_field",
injectionKeyDescription = "ExecSqlMeta.Injection.READ_STATS_FIELD",
injectionKey = "READ_STATS_FIELD")
private String readField;
+ @GuiWidgetElement(
+ id = WIDGET_SQL_FROM_FILE,
+ order = "0100",
+ type = GuiElementType.FILENAME,
+ typeFilename = TypeSqlFilename.class,
+ label = "i18n::ExecSqlMeta.SqlFromFile.Label",
+ toolTip = "i18n::ExecSqlMeta.SqlFromFile.Tooltip",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_SQL,
+ groupOrder = "20",
+ groupType = GuiWidgetGroupType.TABS)
+ @HopMetadataProperty(
+ key = "sql_from_file",
+ injectionKey = "SQL_FROM_FILE",
+ injectionKeyDescription = "ExecSqlMeta.Injection.SQL_FROM_FILE")
+ private String sqlFromFile;
+
+ @HopMetadataProperty(
+ injectionKeyDescription = "ExecSqlMeta.Injection.SQL",
+ injectionKey = "SQL",
+ hopMetadataPropertyType = HopMetadataPropertyType.RDBMS_SQL)
+ private String sql;
+
+ @HopMetadataProperty(
+ key = "execute_each_row",
+ injectionKeyDescription = "ExecSqlMeta.Injection.EXECUTE_FOR_EACH_ROW",
+ injectionKey = "EXECUTE_FOR_EACH_ROW")
+ private boolean executedEachInputRow;
+
@HopMetadataProperty(
key = "single_statement",
injectionKeyDescription =
"ExecSqlMeta.Injection.EXECUTE_AS_SINGLE_STATEMENT",
@@ -117,6 +210,18 @@ public class ExecSqlMeta extends
BaseTransformMeta<ExecSql, ExecSqlData> {
injectionKey = "QUOTE_STRINGS")
private boolean quoteString;
+ @GuiWidgetElement(
+ id = WIDGET_BIND_PARAMETERS,
+ order = "0100",
+ type = GuiElementType.CHECKBOX,
+ label = "i18n::ExecSqlDialog.SetParams.Label",
+ toolTip = "i18n::ExecSqlDialog.SetParams.Tooltip",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_PARAMETERS,
+ groupOrder = "30",
+ groupType = GuiWidgetGroupType.TABS,
+ getterMethod = "isParams",
+ setterMethod = "setParams")
@HopMetadataProperty(
key = "set_params",
injectionKeyDescription = "ExecSqlMeta.Injection.BIND_PARAMETERS",
@@ -136,121 +241,27 @@ public class ExecSqlMeta extends
BaseTransformMeta<ExecSql, ExecSqlData> {
}
/**
- * @return Returns the true if we have to set params.
- */
- public boolean isParams() {
- return this.params;
- }
-
- /**
- * @param value set true if we have to set params.
- */
- public void setParams(boolean value) {
- this.params = value;
- }
-
- /**
- * @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,
"ExecSqlMeta.Exception.CouldNotLoadSqlFromFile", path), e);
+ }
+ }
return sql;
}
- /**
- * @param sql The sql to set.
- */
- public void setSql(String sql) {
- this.sql = sql;
- }
-
- /**
- * @return Returns the arguments.
- */
- public List<ExecSqlArgumentItem> getArguments() {
- return arguments;
- }
-
- /**
- * @param arguments The arguments to set.
- */
- public void setArguments(List<ExecSqlArgumentItem> arguments) {
- this.arguments = arguments;
- }
-
- /**
- * @return Returns the executedEachInputRow.
- */
- public boolean isExecutedEachInputRow() {
- return executedEachInputRow;
- }
-
- /**
- * @param executedEachInputRow The executedEachInputRow to set.
- */
- public void setExecutedEachInputRow(boolean executedEachInputRow) {
- this.executedEachInputRow = executedEachInputRow;
- }
-
- /**
- * @return Returns the deleteField.
- */
- public String getDeleteField() {
- return deleteField;
- }
-
- /**
- * @param deleteField The deleteField to set.
- */
- public void setDeleteField(String deleteField) {
- this.deleteField = deleteField;
- }
-
- /**
- * @return Returns the insertField.
- */
- public String getInsertField() {
- return insertField;
- }
-
- /**
- * @param insertField The insertField to set.
- */
- public void setInsertField(String insertField) {
- this.insertField = insertField;
- }
-
- /**
- * @return Returns the readField.
- */
- public String getReadField() {
- return readField;
- }
-
- /**
- * @param readField The readField to set.
- */
- public void setReadField(String readField) {
- this.readField = readField;
- }
-
- /**
- * @return Returns the updateField.
- */
- public String getUpdateField() {
- return updateField;
- }
-
- /**
- * @param updateField The updateField to set.
- */
- public void setUpdateField(String updateField) {
- this.updateField = updateField;
- }
-
@Override
public void setDefault() {
sql = "";
- arguments = new ArrayList();
+ sqlFromFile = "";
+ arguments = new ArrayList<>();
}
@Override
@@ -318,21 +329,35 @@ public class ExecSqlMeta extends
BaseTransformMeta<ExecSql, ExecSqlData> {
transformMeta);
remarks.add(cr);
- if (!Utils.isEmpty(sql)) {
- cr =
- new CheckResult(
- ICheckResult.TYPE_RESULT_OK,
- BaseMessages.getString(PKG,
"ExecSqlMeta.CheckResult.SQLStatementEntered"),
- transformMeta);
- remarks.add(cr);
- } else {
+ String effectiveSql = null;
+ try {
+ effectiveSql = getEffectiveSql(variables);
+ } catch (HopException e) {
cr =
new CheckResult(
ICheckResult.TYPE_RESULT_ERROR,
- BaseMessages.getString(PKG,
"ExecSqlMeta.CheckResult.SQLStatementMissing"),
+ BaseMessages.getString(PKG,
"ExecSqlMeta.CheckResult.CouldNotGetSql")
+ + e.getMessage(),
transformMeta);
remarks.add(cr);
}
+ if (effectiveSql != null) {
+ if (!Utils.isEmpty(effectiveSql)) {
+ cr =
+ new CheckResult(
+ ICheckResult.TYPE_RESULT_OK,
+ BaseMessages.getString(PKG,
"ExecSqlMeta.CheckResult.SQLStatementEntered"),
+ transformMeta);
+ remarks.add(cr);
+ } else {
+ cr =
+ new CheckResult(
+ ICheckResult.TYPE_RESULT_ERROR,
+ BaseMessages.getString(PKG,
"ExecSqlMeta.CheckResult.SQLStatementMissing"),
+ transformMeta);
+ remarks.add(cr);
+ }
+ }
} catch (HopException e) {
cr =
new CheckResult(
@@ -405,6 +430,12 @@ public class ExecSqlMeta extends
BaseTransformMeta<ExecSql, ExecSqlData> {
try {
DatabaseMeta databaseMeta =
metadataProvider.getSerializer(DatabaseMeta.class).load(variables.resolve(connection));
+ String impactSql;
+ try {
+ impactSql = getEffectiveSql(variables);
+ } catch (HopException e) {
+ impactSql = sql;
+ }
DatabaseImpact ii =
new DatabaseImpact(
DatabaseImpact.TYPE_IMPACT_READ_WRITE,
@@ -415,7 +446,7 @@ public class ExecSqlMeta extends BaseTransformMeta<ExecSql,
ExecSqlData> {
BaseMessages.getString(PKG,
"ExecSqlMeta.DatabaseMeta.Unknown2.Label"),
BaseMessages.getString(PKG,
"ExecSqlMeta.DatabaseMeta.Unknown3.Label"),
transformMeta.getName(),
- sql,
+ impactSql,
BaseMessages.getString(PKG, "ExecSqlMeta.DatabaseMeta.Title"));
impact.add(ii);
@@ -426,52 +457,8 @@ public class ExecSqlMeta extends
BaseTransformMeta<ExecSql, ExecSqlData> {
}
}
- /**
- * @return Returns the variableReplacementActive.
- */
- public boolean isReplaceVariables() {
- return replaceVariables;
- }
-
- /**
- * @param replaceVariables The variableReplacement to set.
- */
- public void setReplaceVariables(boolean replaceVariables) {
- this.replaceVariables = replaceVariables;
- }
-
- public boolean isQuoteString() {
- return quoteString;
- }
-
- public void setQuoteString(boolean quoteString) {
- this.quoteString = quoteString;
- }
-
- public String getConnection() {
- return connection;
- }
-
- public void setConnection(String connection) {
- this.connection = connection;
- }
-
@Override
public boolean supportsErrorHandling() {
return true;
}
-
- /**
- * @return the singleStatement
- */
- public boolean isSingleStatement() {
- return singleStatement;
- }
-
- /**
- * @param singleStatement the singleStatement to set
- */
- public void setSingleStatement(boolean singleStatement) {
- this.singleStatement = singleStatement;
- }
}
diff --git
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/TypeSqlFilename.java
similarity index 53%
copy from
plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
copy to
plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/TypeSqlFilename.java
index eb7546a03d..a3a2d3e9e9 100644
---
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
+++
b/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/TypeSqlFilename.java
@@ -1,5 +1,3 @@
-package org.apache.hop.pipeline.transforms.sql;
-
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
@@ -17,40 +15,29 @@ package org.apache.hop.pipeline.transforms.sql;
* limitations under the License.
*/
-import java.util.Objects;
-import org.apache.hop.metadata.api.HopMetadataProperty;
-
-public class ExecSqlArgumentItem {
-
- @HopMetadataProperty(
- injectionKeyDescription = "ExecSqlMeta.Injection.PARAMETER_NAME",
- injectionKey = "PARAMETER_NAME")
- private String name;
-
- public ExecSqlArgumentItem() {}
+package org.apache.hop.pipeline.transforms.sql;
- public ExecSqlArgumentItem(String name) {
- this.name = name;
- }
+import org.apache.hop.core.gui.plugin.ITypeFilename;
+import org.apache.hop.i18n.BaseMessages;
- public String getName() {
- return name;
- }
+public class TypeSqlFilename implements ITypeFilename {
+ private static final Class<?> PKG = ExecSqlMeta.class;
- public void setName(String name) {
- this.name = name;
+ @Override
+ public String getDefaultFileExtension() {
+ return ".sql";
}
@Override
- public boolean equals(Object o) {
- if (this == o) return true;
- if (o == null || getClass() != o.getClass()) return false;
- ExecSqlArgumentItem that = (ExecSqlArgumentItem) o;
- return name.equals(that.name);
+ public String[] getFilterExtensions() {
+ return new String[] {"*.sql", "*"};
}
@Override
- public int hashCode() {
- return Objects.hash(name);
+ public String[] getFilterNames() {
+ return new String[] {
+ BaseMessages.getString(PKG, "ExecSqlMeta.SqlFiles"),
+ BaseMessages.getString("System.FileType.AllFiles")
+ };
}
}
diff --git
a/plugins/transforms/sql/src/main/resources/org/apache/hop/pipeline/transforms/sql/messages/messages_en_US.properties
b/plugins/transforms/sql/src/main/resources/org/apache/hop/pipeline/transforms/sql/messages/messages_en_US.properties
index ac94a2bf85..28de4d1760 100644
---
a/plugins/transforms/sql/src/main/resources/org/apache/hop/pipeline/transforms/sql/messages/messages_en_US.properties
+++
b/plugins/transforms/sql/src/main/resources/org/apache/hop/pipeline/transforms/sql/messages/messages_en_US.properties
@@ -28,6 +28,18 @@ ExecSql.Log.FinishingReadingQuery=Finished reading query,
closing connection.
ExecSql.Log.LineNumber=linenr
ExecSql.Name=Execute SQL script
ExecSqlDialog.ColumnInfo.ArgumentFieldname=Field name to be used as argument
+ExecSqlMeta.Group.General=General
+ExecSqlMeta.Group.SQL=SQL
+ExecSqlMeta.Group.Parameters=Parameters
+ExecSqlMeta.Connection.Label=Connection
+ExecSqlMeta.Connection.Tooltip=The relational database connection used to
execute the SQL
+ExecSqlMeta.SqlFromFile.Label=Load SQL from file
+ExecSqlMeta.SqlFromFile.Tooltip=Optional VFS path to a .sql file (variables in
the path are resolved, for example '${PROJECT_HOME}'/sql/script.sql). When set,
the script is loaded from this file at transform initialization and the SQL
editor is read-only. Question marks in the file still bind to the Parameters
table. Leave empty to use the SQL editor.
+ExecSqlMeta.SqlFiles=SQL files
+ExecSqlMeta.Exception.CouldNotLoadSqlFromFile=Could not load SQL from file: {0}
+ExecSqlMeta.CheckResult.CouldNotGetSql=Could not get SQL:
+ExecSqlDialog.CouldNotLoadSqlFromFile=Could not load SQL from file: {0}
+ExecSqlDialog.DialogCaptionError=Error
ExecSqlDialog.DeleteField.Label=Field to contain Delete stats
ExecSqlDialog.EachRow.Label=Execute for each row
ExecSqlDialog.FailedToGetFields.DialogMessage=Unable to get fields from
previous transforms because of an error
@@ -77,5 +89,6 @@ ExecSqlMeta.Injection.QUOTE_STRINGS=Add quotes around the
string to escape speci
ExecSqlMeta.Injection.READ_STATS_FIELD=Optional field in the stream that
contains the number of records that were read.
ExecSqlMeta.Injection.REPLACE_VARIABLES=Allow you to use variables in the SQL
(Y/N)?
ExecSqlMeta.Injection.SQL=SQL script to execute.
+ExecSqlMeta.Injection.SQL_FROM_FILE=Optional VFS path of a .sql file to
execute instead of the inline SQL script.
ExecSqlMeta.Injection.UPDATE_STATS_FIELD=Optional field in the stream that
contains the number of records that were updated.
ExecSqlMeta.keyword=sql,execute,ddl,query,database
diff --git
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
b/plugins/transforms/sql/src/test/java/org/apache/hop/pipeline/transforms/sql/ExecSqlDisabledWidgetsTest.java
similarity index 52%
copy from
plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
copy to
plugins/transforms/sql/src/test/java/org/apache/hop/pipeline/transforms/sql/ExecSqlDisabledWidgetsTest.java
index eb7546a03d..2e0a731ea6 100644
---
a/plugins/transforms/sql/src/main/java/org/apache/hop/pipeline/transforms/sql/ExecSqlArgumentItem.java
+++
b/plugins/transforms/sql/src/test/java/org/apache/hop/pipeline/transforms/sql/ExecSqlDisabledWidgetsTest.java
@@ -1,5 +1,3 @@
-package org.apache.hop.pipeline.transforms.sql;
-
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
@@ -17,40 +15,22 @@ package org.apache.hop.pipeline.transforms.sql;
* limitations under the License.
*/
-import java.util.Objects;
-import org.apache.hop.metadata.api.HopMetadataProperty;
-
-public class ExecSqlArgumentItem {
-
- @HopMetadataProperty(
- injectionKeyDescription = "ExecSqlMeta.Injection.PARAMETER_NAME",
- injectionKey = "PARAMETER_NAME")
- private String name;
-
- public ExecSqlArgumentItem() {}
-
- public ExecSqlArgumentItem(String name) {
- this.name = name;
- }
-
- public String getName() {
- return name;
- }
+package org.apache.hop.pipeline.transforms.sql;
- public void setName(String name) {
- this.name = name;
- }
+import java.util.List;
+import org.apache.hop.ui.testing.DisabledGuiWidgetsTestBase;
+import org.junit.jupiter.api.Tag;
- @Override
- public boolean equals(Object o) {
- if (this == o) return true;
- if (o == null || getClass() != o.getClass()) return false;
- ExecSqlArgumentItem that = (ExecSqlArgumentItem) o;
- return name.equals(that.name);
- }
+/**
+ * Disables every {@code @GuiWidgetElement} this plugin registers, one at a
time, and checks that
+ * its composite still builds and still survives the listener callbacks. See
{@link
+ * DisabledGuiWidgetsTestBase}.
+ */
+@Tag("uitest")
+class ExecSqlDisabledWidgetsTest extends DisabledGuiWidgetsTestBase {
@Override
- public int hashCode() {
- return Objects.hash(name);
+ protected List<String> packagesUnderTest() {
+ return List.of("org.apache.hop.pipeline.transforms.sql");
}
}
diff --git
a/plugins/transforms/sql/src/test/java/org/apache/hop/pipeline/transforms/sql/ExecSqlMetaTest.java
b/plugins/transforms/sql/src/test/java/org/apache/hop/pipeline/transforms/sql/ExecSqlMetaTest.java
index 0260b9537a..3083770055 100644
---
a/plugins/transforms/sql/src/test/java/org/apache/hop/pipeline/transforms/sql/ExecSqlMetaTest.java
+++
b/plugins/transforms/sql/src/test/java/org/apache/hop/pipeline/transforms/sql/ExecSqlMetaTest.java
@@ -16,6 +16,8 @@
*/
package org.apache.hop.pipeline.transforms.sql;
+import java.nio.file.Files;
+import java.nio.file.Path;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
@@ -26,6 +28,7 @@ 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.variables.Variables;
import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
import org.apache.hop.pipeline.transform.ITransformMeta;
import org.apache.hop.pipeline.transforms.loadsave.LoadSaveTester;
@@ -34,6 +37,7 @@ import
org.apache.hop.pipeline.transforms.loadsave.validator.IFieldLoadSaveValid
import
org.apache.hop.pipeline.transforms.loadsave.validator.IFieldLoadSaveValidatorFactory;
import
org.apache.hop.pipeline.transforms.loadsave.validator.ListLoadSaveValidator;
import org.apache.hop.pipeline.transforms.loadsave.validator.ObjectValidator;
+import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.RegisterExtension;
@@ -62,6 +66,7 @@ class ExecSqlMetaTest implements IInitializer<ITransformMeta>
{
"replace_variables",
"quoteString",
"set_params",
+ "sqlFromFile",
"arguments");
Map<String, String> getterMap =
@@ -78,6 +83,7 @@ class ExecSqlMetaTest implements IInitializer<ITransformMeta>
{
put("replace_variables", "isReplaceVariables");
put("quoteString", "isQuoteString");
put("set_params", "isParams");
+ put("sqlFromFile", "getSqlFromFile");
put("arguments", "getArguments");
}
};
@@ -95,6 +101,7 @@ class ExecSqlMetaTest implements
IInitializer<ITransformMeta> {
put("replace_variables", "setReplaceVariables");
put("quoteString", "setQuoteString");
put("set_params", "setParams");
+ put("sqlFromFile", "setSqlFromFile");
put("arguments", "setArguments");
}
};
@@ -178,4 +185,56 @@ class ExecSqlMetaTest implements
IInitializer<ITransformMeta> {
void testSerialization() throws HopException {
loadSaveTester.testSerialization();
}
+
+ @Test
+ void getEffectiveSqlUsesInlineWhenNoFile() throws Exception {
+ ExecSqlMeta meta = new ExecSqlMeta();
+ meta.setSql("SELECT 1");
+ Assertions.assertEquals("SELECT 1", meta.getEffectiveSql(new Variables()));
+ }
+
+ @Test
+ void getEffectiveSqlLoadsFromFile() throws Exception {
+ Path file = Files.createTempFile("execsql-", ".sql");
+ try {
+ String sql = "insert into public.testtable (key, value) values ('k',
'v');";
+ Files.writeString(file, sql);
+ ExecSqlMeta meta = new ExecSqlMeta();
+ 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("execsql-", ".sql");
+ try {
+ Files.writeString(file, "SELECT 2");
+ ExecSqlMeta meta = new ExecSqlMeta();
+ 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() {
+ ExecSqlMeta meta = new ExecSqlMeta();
+ meta.setSqlFromFile("/this/path/does/not-exist-execsql.sql");
+ Assertions.assertThrows(HopException.class, () -> meta.getEffectiveSql(new
Variables()));
+ }
+
+ @Test
+ void getEffectiveSqlEmptyFilePathUsesInlineSql() throws Exception {
+ ExecSqlMeta meta = new ExecSqlMeta();
+ meta.setSql("SELECT 1");
+ meta.setSqlFromFile("");
+ Assertions.assertEquals("SELECT 1", meta.getEffectiveSql(new Variables()));
+ }
}
