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 4ed0ed6aba Get table names fails with an empty schema when the
database has tabl… (#9375)
4ed0ed6aba is described below
commit 4ed0ed6abaf807df4e8d85808b60ca7e3fa724e2
Author: Bart Maertens <[email protected]>
AuthorDate: Thu Nov 12 10:59:26 2026 +0100
Get table names fails with an empty schema when the database has tabl…
(#9375)
* Get table names fails with an empty schema when the database has tables
outside the default schema, fixes #9373
* Pass the catalog to the primary key lookup and fix the PRIMARY KEY clause
in Get table names, #9373
---
.../org/apache/hop/core/database/Database.java | 210 ++++++++-------
.../hop/core/database/DatabaseObjectName.java | 38 +++
.../database/datasets/golden-get-table-names.csv | 25 +-
.../database/main-0024-get-table-names.hwf | 8 +
.../samples/read-samples-build-hop-run.hpl | 4 -
.../transforms/gettablenames/GetTableNames.java | 137 ++++++----
.../gettablenames/GetTableNamesTest.java | 288 +++++++++++++++++++++
7 files changed, 560 insertions(+), 150 deletions(-)
diff --git a/core/src/main/java/org/apache/hop/core/database/Database.java
b/core/src/main/java/org/apache/hop/core/database/Database.java
index aba99e0647..2059938c61 100644
--- a/core/src/main/java/org/apache/hop/core/database/Database.java
+++ b/core/src/main/java/org/apache/hop/core/database/Database.java
@@ -4247,75 +4247,96 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
public Map<String, Collection<String>> getTableMap(String schemanamein,
Map<String, String> props)
throws HopDatabaseException {
+ Map<String, Collection<String>> tableMap = new HashMap<>();
+ for (DatabaseObjectName table : getTableObjectNames(schemanamein, props)) {
+ multimapPut(table.schemaOrCatalog(), table.name(), tableMap);
+ }
+
+ if (log.isDetailed()) {
+ log.logDetailed(CONST_READ + multimapSize(tableMap) + " table names from
db meta-data.");
+ }
+
+ return tableMap;
+ }
+
+ /**
+ * List the tables with their catalog and schema kept apart, for lookups
that need both, like
+ * {@link #getPrimaryKeyColumnNames(String, String, String)}.
+ *
+ * @param schemanamein The schema to list, null for all schemas (or the
user's schema on databases
+ * that list tables per user).
+ * @param props Extra table properties that have to match, may be null.
+ */
+ public List<DatabaseObjectName> getTableObjectNames(
+ String schemanamein, Map<String, String> props) throws
HopDatabaseException {
String schemaname = schemanamein;
if (schemaname == null && databaseMeta.useSchemaNameForTableList()) {
schemaname = resolve(databaseMeta.getUsername()).toUpperCase();
}
- Map<String, Collection<String>> tableMap = new HashMap<>();
+ List<DatabaseObjectName> tables = new ArrayList<>();
try (ResultSet alltables =
getDatabaseMetaData().getTables(null, schemaname, null,
databaseMeta.getTableTypes())) {
while (alltables.next()) {
- String cat = "";
- try {
- cat = alltables.getString(CONST_TABLE_CAT);
- } catch (Exception e) {
- // ignore
- if (log.isDebug()) {
- log.logDebug("Error getting tables for field TABLE_CAT (ignored):
" + e);
- }
- }
-
- String schema = "";
- try {
- schema = alltables.getString(CONST_TABLE_SCHEM);
- } catch (Exception e) {
- // ignore
- if (log.isDebug()) {
- log.logDebug("Error getting tables for field TABLE_SCHEM
(ignored): " + e);
- }
- }
-
- if (Utils.isEmpty(schema)) {
- schema = cat;
- }
-
- String table = alltables.getString(TABLES_META_DATA_TABLE_NAME);
-
- if (log.isRowLevel()) {
- log.logRowlevel(
- toString(),
- "got table from meta-data: "
- + databaseMeta.getQuotedSchemaTableCombination(this, schema,
table));
- }
+ DatabaseObjectName table = readObjectName(alltables, "table");
// Check for any extra properties that might require validation
- if (!Utils.isEmpty(props)) {
- for (Map.Entry<String, String> prop : props.entrySet()) {
- String propName = prop.getKey();
-
- String tableProperty = alltables.getString(propName);
- if (tableProperty != null) {
- String propValue = prop.getValue();
-
- if (tableProperty.equals(propValue)) {
- multimapPut(schema, table, tableMap);
- }
- }
- }
- } else {
- multimapPut(schema, table, tableMap);
+ if (Utils.isEmpty(props) || matchesTableProperties(alltables, props)) {
+ tables.add(table);
}
}
} catch (SQLException e) {
log.logError("Error getting tablenames from schema [" + schemaname +
"]");
}
+ return tables;
+ }
- if (log.isDetailed()) {
- log.logDetailed(CONST_READ + multimapSize(tableMap) + " table names from
db meta-data.");
+ private boolean matchesTableProperties(ResultSet alltables, Map<String,
String> props)
+ throws SQLException {
+ for (Map.Entry<String, String> prop : props.entrySet()) {
+ String tableProperty = alltables.getString(prop.getKey());
+ if (tableProperty != null && tableProperty.equals(prop.getValue())) {
+ return true;
+ }
}
+ return false;
+ }
- return tableMap;
+ /** Read the catalog, schema and name of the current row of a {@code
getTables()} result. */
+ private DatabaseObjectName readObjectName(ResultSet resultSet, String kind)
throws SQLException {
+ String cat = "";
+ try {
+ cat = resultSet.getString(CONST_TABLE_CAT);
+ } catch (Exception e) {
+ // ignore
+ if (log.isDebug()) {
+ log.logDebug("Error getting " + kind + "s for field TABLE_CAT
(ignored): " + e);
+ }
+ }
+
+ String schema = "";
+ try {
+ schema = resultSet.getString(CONST_TABLE_SCHEM);
+ } catch (Exception e) {
+ // ignore
+ if (log.isDebug()) {
+ log.logDebug("Error getting " + kind + "s for field TABLE_SCHEM
(ignored): " + e);
+ }
+ }
+
+ DatabaseObjectName objectName =
+ new DatabaseObjectName(cat, schema,
resultSet.getString(TABLES_META_DATA_TABLE_NAME));
+
+ if (log.isRowLevel()) {
+ log.logRowlevel(
+ toString(),
+ "got "
+ + kind
+ + " from meta-data: "
+ + databaseMeta.getQuotedSchemaTableCombination(
+ this, objectName.schemaOrCatalog(), objectName.name()));
+ }
+ return objectName;
}
public String[] getViews() throws HopDatabaseException {
@@ -4349,8 +4370,28 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
public Map<String, Collection<String>> getViewMap(String schemanamein)
throws HopDatabaseException {
+ Map<String, Collection<String>> viewMap = new HashMap<>();
+ for (DatabaseObjectName view : getViewObjectNames(schemanamein)) {
+ multimapPut(view.schemaOrCatalog(), view.name(), viewMap);
+ }
+
+ if (log.isDetailed()) {
+ log.logDetailed(CONST_READ + multimapSize(viewMap) + " views from db
meta-data.");
+ }
+
+ return viewMap;
+ }
+
+ /**
+ * List the views with their catalog and schema kept apart.
+ *
+ * @param schemanamein The schema to list, null for all schemas (or the
user's schema on databases
+ * that list tables per user).
+ */
+ public List<DatabaseObjectName> getViewObjectNames(String schemanamein)
+ throws HopDatabaseException {
if (!databaseMeta.supportsViews()) {
- return Collections.emptyMap();
+ return Collections.emptyList();
}
String schemaname = schemanamein;
@@ -4358,53 +4399,16 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
schemaname = resolve(databaseMeta.getUsername()).toUpperCase();
}
- Map<String, Collection<String>> viewMap = new HashMap<>();
+ List<DatabaseObjectName> views = new ArrayList<>();
try (ResultSet allviews =
getDatabaseMetaData().getTables(null, schemaname, null,
databaseMeta.getViewTypes())) {
while (allviews.next()) {
- String cat = "";
- try {
- cat = allviews.getString(CONST_TABLE_CAT);
- } catch (Exception e) {
- // ignore
- if (log.isDebug()) {
- log.logDebug("Error getting views for field TABLE_CAT (ignored): "
+ e);
- }
- }
-
- String schema = "";
- try {
- schema = allviews.getString(CONST_TABLE_SCHEM);
- } catch (Exception e) {
- // ignore
- if (log.isDebug()) {
- log.logDebug("Error getting views for field TABLE_SCHEM (ignored):
" + e);
- }
- }
-
- if (Utils.isEmpty(schema)) {
- schema = cat;
- }
-
- String table = allviews.getString(TABLES_META_DATA_TABLE_NAME);
-
- if (log.isRowLevel()) {
- log.logRowlevel(
- toString(),
- "got view from meta-data: "
- + databaseMeta.getQuotedSchemaTableCombination(this, schema,
table));
- }
- multimapPut(schema, table, viewMap);
+ views.add(readObjectName(allviews, "view"));
}
} catch (SQLException e) {
throw new HopDatabaseException("Error getting views from schema [" +
schemaname + "]", e);
}
-
- if (log.isDetailed()) {
- log.logDetailed(CONST_READ + multimapSize(viewMap) + " views from db
meta-data.");
- }
-
- return viewMap;
+ return views;
}
public String[] getSynonyms() throws HopDatabaseException {
@@ -5324,10 +5328,30 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
* @throws HopDatabaseException
*/
public String[] getPrimaryKeyColumnNames(String tableName) throws
HopDatabaseException {
+ return getPrimaryKeyColumnNames(null, null, tableName);
+ }
+
+ /**
+ * Return the primary key column names of a table in a specific catalog and
schema.
+ *
+ * @param catalogName The catalog as listed by the database (TABLE_CAT, see
{@link
+ * #getTableObjectNames(String, Map)}). Null or empty does not filter on
catalog.
+ * @param schemaName The unquoted schema name (TABLE_SCHEM). Null or empty
does not filter on
+ * schema.
+ * @param tableName The unquoted table name.
+ * @throws HopDatabaseException
+ */
+ public String[] getPrimaryKeyColumnNames(String catalogName, String
schemaName, String tableName)
+ throws HopDatabaseException {
List<String> names = new ArrayList<>();
ResultSet allkeys = null;
try {
- allkeys = getDatabaseMetaData().getPrimaryKeys(null, null, tableName);
+ allkeys =
+ getDatabaseMetaData()
+ .getPrimaryKeys(
+ Utils.isEmpty(catalogName) ? null : catalogName,
+ Utils.isEmpty(schemaName) ? null : schemaName,
+ tableName);
while (allkeys.next()) {
String keyname = allkeys.getString("PK_NAME");
String colName = allkeys.getString("COLUMN_NAME");
diff --git
a/core/src/main/java/org/apache/hop/core/database/DatabaseObjectName.java
b/core/src/main/java/org/apache/hop/core/database/DatabaseObjectName.java
new file mode 100644
index 0000000000..1c0c2844a5
--- /dev/null
+++ b/core/src/main/java/org/apache/hop/core/database/DatabaseObjectName.java
@@ -0,0 +1,38 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.core.database;
+
+import org.apache.hop.core.util.Utils;
+
+/**
+ * A table or view as listed by the JDBC metadata, with its catalog and schema
kept apart.
+ *
+ * @param catalog the TABLE_CAT value, may be null or empty
+ * @param schema the TABLE_SCHEM value, may be null or empty
+ * @param name the unquoted table or view name
+ */
+public record DatabaseObjectName(String catalog, String schema, String name) {
+
+ /**
+ * The schema, or the catalog when the object has no schema (MySQL, MariaDB,
...). This is the key
+ * that {@link Database#getTableMap(String)} and {@link
Database#getViewMap(String)} use.
+ */
+ public String schemaOrCatalog() {
+ return Utils.isEmpty(schema) ? catalog : schema;
+ }
+}
diff --git a/integration-tests/database/datasets/golden-get-table-names.csv
b/integration-tests/database/datasets/golden-get-table-names.csv
index 5050e6c1de..685f3db06c 100644
--- a/integration-tests/database/datasets/golden-get-table-names.csv
+++ b/integration-tests/database/datasets/golden-get-table-names.csv
@@ -2,30 +2,45 @@ tablename,type,is system,sql
t_0024_1,TABLE,N,"CREATE TABLE t_0024_1
(
n VARCHAR(100)
-, PRIMARY KEY ()
)
;"
t_0024_2,TABLE,N,"CREATE TABLE t_0024_2
(
n VARCHAR(100)
-, PRIMARY KEY ()
)
;"
t_0024_3,TABLE,N,"CREATE TABLE t_0024_3
(
n VARCHAR(100)
-, PRIMARY KEY ()
)
;"
t_0024_4,TABLE,N,"CREATE TABLE t_0024_4
(
n VARCHAR(100)
-, PRIMARY KEY ()
)
;"
t_0024_5,TABLE,N,"CREATE TABLE t_0024_5
(
n VARCHAR(100)
-, PRIMARY KEY ()
+)
+;"
+t_0024_6,TABLE,N,"CREATE TABLE t_0024_6
+(
+ n VARCHAR(100)
+)
+;"
+t_0024_7,TABLE,N,"CREATE TABLE t_0024_7
+(
+ id BIGSERIAL
+, n VARCHAR(100)
+, PRIMARY KEY (id)
+)
+;"
+t_0024_8,TABLE,N,"CREATE TABLE t_0024_8
+(
+ a INTEGER
+, b INTEGER
+, n VARCHAR(100)
+, PRIMARY KEY (a, b)
)
;"
diff --git a/integration-tests/database/main-0024-get-table-names.hwf
b/integration-tests/database/main-0024-get-table-names.hwf
index dddd41757f..35c52c9b5e 100644
--- a/integration-tests/database/main-0024-get-table-names.hwf
+++ b/integration-tests/database/main-0024-get-table-names.hwf
@@ -63,6 +63,14 @@ CREATE TABLE IF NOT EXISTS T_0024_4(n varchar(100))
;
CREATE TABLE IF NOT EXISTS T_0024_5(n varchar(100))
;
+CREATE SCHEMA IF NOT EXISTS S_0024
+;
+CREATE TABLE IF NOT EXISTS S_0024.T_0024_6(n varchar(100))
+;
+CREATE TABLE IF NOT EXISTS T_0024_7(id int PRIMARY KEY, n varchar(100))
+;
+CREATE TABLE IF NOT EXISTS T_0024_8(a int, b int, n varchar(100), PRIMARY KEY
(a, b))
+;
</sql>
<useVariableSubstitution>F</useVariableSubstitution>
<sqlfromfile>F</sqlfromfile>
diff --git a/integration-tests/samples/read-samples-build-hop-run.hpl
b/integration-tests/samples/read-samples-build-hop-run.hpl
index a5c81d9c65..1d18d4217b 100644
--- a/integration-tests/samples/read-samples-build-hop-run.hpl
+++ b/integration-tests/samples/read-samples-build-hop-run.hpl
@@ -603,10 +603,6 @@ var hop_run_cmd = './hop-run.sh -j samples -r ' +
run_config + ' -f ' + filename
<item>call-db-procedure.hpl</item>
<item>requires a stored procedure in the database</item>
</line>
- <line>
- <item>get-table-names.hpl</item>
- <item>fails on the H2 2.x metadata of the hop-samples connection
(INFORMATION_SCHEMA.CONSTANTS)</item>
- </line>
<line>
<item>http-client.hpl</item>
<item>requires network access to the URL</item>
diff --git
a/plugins/transforms/gettablenames/src/main/java/org/apache/hop/pipeline/transforms/gettablenames/GetTableNames.java
b/plugins/transforms/gettablenames/src/main/java/org/apache/hop/pipeline/transforms/gettablenames/GetTableNames.java
index 2c45593d21..6cfe511e42 100644
---
a/plugins/transforms/gettablenames/src/main/java/org/apache/hop/pipeline/transforms/gettablenames/GetTableNames.java
+++
b/plugins/transforms/gettablenames/src/main/java/org/apache/hop/pipeline/transforms/gettablenames/GetTableNames.java
@@ -18,9 +18,14 @@
package org.apache.hop.pipeline.transforms.gettablenames;
import com.google.common.annotations.VisibleForTesting;
+import java.util.ArrayList;
+import java.util.Comparator;
+import java.util.List;
+import java.util.Locale;
import org.apache.hop.core.Const;
import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.DatabaseObjectName;
import org.apache.hop.core.exception.HopDatabaseException;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.exception.HopTransformException;
@@ -102,7 +107,10 @@ public class GetTableNames extends
BaseTransform<GetTableNamesMeta, GetTableName
if (meta.isDynamicSchema()) {
// Get value of dynamic schema ...
- data.realSchemaName = data.inputRowMeta.getString(data.inputRow,
data.indexOfSchemaField);
+ // An empty schema lists all schemas, as it does without a dynamic
schema. JDBC reads "" as
+ // "objects without a schema".
+ String schemaName = data.inputRowMeta.getString(data.inputRow,
data.indexOfSchemaField);
+ data.realSchemaName = Utils.isEmpty(schemaName) ? null : schemaName;
}
Object[] outputRow =
RowDataUtil.allocateRowData(data.outputRowMeta.size());
@@ -189,22 +197,21 @@ public class GetTableNames extends
BaseTransform<GetTableNamesMeta, GetTableName
// Views
if (meta.isIncludeView()) {
try {
- String[] viewNames = data.db.getViews(data.realSchemaName,
meta.isAddSchemaInOutput());
- String[] viewNamesWithoutSchema =
data.db.getViews(data.realSchemaName, false);
+ List<SchemaObject> views =
+
sortedSchemaObjects(data.db.getViewObjectNames(data.realSchemaName), false);
String objectType = BaseMessages.getString(PKG,
"GetTableNamesDialog.ObjectType.View");
- for (int i = 0; i < viewNames.length && !isStopped(); i++) {
+ for (int i = 0; i < views.size() && !isStopped(); i++) {
Object[] outputRowView = outputRow.clone();
int outputIndex = data.totalpreviousfields;
- String viewName = viewNames[i];
- String viewNameWithoutSchema = viewNamesWithoutSchema[i];
- outputRowView[outputIndex++] = viewName;
+ SchemaObject view = views.get(i);
+ outputRowView[outputIndex++] = view.outputName();
if (!Utils.isEmpty(data.realObjectTypeFieldName)) {
outputRowView[outputIndex++] = objectType;
}
if (!Utils.isEmpty(data.realIsSystemObjectFieldName)) {
- outputRowView[outputIndex++] =
data.db.isSystemTable(viewNameWithoutSchema);
+ outputRowView[outputIndex++] =
data.db.isSystemTable(view.object().name());
}
if (!Utils.isEmpty(data.realSqlCreationFieldName)) {
@@ -227,60 +234,29 @@ public class GetTableNames extends
BaseTransform<GetTableNamesMeta, GetTableName
if (meta.isIncludeTable()) {
// Tables
- String[] tableNames = data.db.getTablenames(data.realSchemaName,
meta.isAddSchemaInOutput());
- String[] tableNamesWithoutSchema =
data.db.getTablenames(data.realSchemaName, false);
+ // The catalog and schema of each table are needed for the lookups
below: with an empty
+ // schema name the list holds the tables of every schema, not only those
of the default one.
+ List<SchemaObject> tables =
+ sortedSchemaObjects(data.db.getTableObjectNames(data.realSchemaName,
null), true);
String objectType = BaseMessages.getString(PKG,
"GetTableNamesDialog.ObjectType.Table");
- for (int i = 0; i < tableNames.length && !isStopped(); i++) {
+ for (int i = 0; i < tables.size() && !isStopped(); i++) {
Object[] outputRowTable = outputRow.clone();
int outputIndex = data.totalpreviousfields;
- String tableName = tableNames[i];
- String tableNameWithoutSchema = tableNamesWithoutSchema[i];
- outputRowTable[outputIndex++] = tableName;
+ SchemaObject table = tables.get(i);
+ outputRowTable[outputIndex++] = table.outputName();
if (!Utils.isEmpty(data.realObjectTypeFieldName)) {
outputRowTable[outputIndex++] = objectType;
}
if (!Utils.isEmpty(data.realIsSystemObjectFieldName)) {
- outputRowTable[outputIndex++] =
data.db.isSystemTable(tableNameWithoutSchema);
- }
- // Get primary key
- String pk = null;
- String[] pkc =
data.db.getPrimaryKeyColumnNames(tableNameWithoutSchema);
- if (pkc != null && pkc.length == 1) {
- pk = pkc[0];
- }
- // return sql creation
- // handle simple primary key (one field)
- String sql =
- data.db.getCreateTableStatement(
- tableName,
- data.db.getTableFieldsMeta(data.realSchemaName,
tableNameWithoutSchema),
- null,
- false,
- pk,
- true);
-
- if (pkc != null) {
- // add composite primary key (several fields in primary key)
- int indexOfLastClosedBracket = sql.lastIndexOf(")");
- if (indexOfLastClosedBracket > -1) {
- sql = sql.substring(0, indexOfLastClosedBracket);
- sql += ", PRIMARY KEY (";
- for (int k = 0; k < pkc.length; k++) {
- if (k > 0) {
- sql += ", ";
- }
- sql += pkc[k];
- }
- sql += ")" + Const.CR + ")" + Const.CR + ";";
- }
+ outputRowTable[outputIndex++] =
data.db.isSystemTable(table.object().name());
}
if (!Utils.isEmpty(data.realSqlCreationFieldName)) {
- outputRowTable[outputIndex] = sql;
+ outputRowTable[outputIndex] = getCreateTableSql(table);
}
data.rownr++;
@@ -291,6 +267,71 @@ public class GetTableNames extends
BaseTransform<GetTableNamesMeta, GetTableName
}
}
+ private String getCreateTableSql(SchemaObject table) throws
HopDatabaseException {
+ DatabaseObjectName object = table.object();
+ String[] pkc =
+ data.db.getPrimaryKeyColumnNames(object.catalog(), object.schema(),
object.name());
+
+ // A primary key on one field goes to the create table statement
+ String pk = null;
+ if (pkc != null && pkc.length == 1) {
+ pk = pkc[0];
+ }
+ String sql =
+ data.db.getCreateTableStatement(
+ table.outputName(),
+ data.db.getTableFieldsMeta(object.schemaOrCatalog(),
object.name()),
+ null,
+ false,
+ pk,
+ true);
+
+ // Add the primary key clause for a composite key, and for a one-field key
when the dialect did
+ // not write one (PostgreSQL writes BIGSERIAL, MySQL only marks numeric
keys inline, ...)
+ if (pkc != null
+ && (pkc.length > 1
+ || (pkc.length == 1 &&
!sql.toUpperCase(Locale.ROOT).contains("PRIMARY KEY")))) {
+ int indexOfLastClosedBracket = sql.lastIndexOf(")");
+ if (indexOfLastClosedBracket > -1) {
+ sql = sql.substring(0, indexOfLastClosedBracket);
+ sql += ", PRIMARY KEY (" + String.join(", ", pkc) + ")" + Const.CR +
")" + Const.CR + ";";
+ }
+ }
+ return sql;
+ }
+
+ /**
+ * Keeps each table or view together with the name that goes to the output,
sorted on that output
+ * name like {@link Database#getTablenames(String, boolean)}.
+ *
+ * @param quoteWithoutSchema quote the output name also when the schema is
left out, as {@link
+ * Database#getTablenames(String, boolean)} does and {@link
Database#getViews(String,
+ * boolean)} does not.
+ */
+ private List<SchemaObject> sortedSchemaObjects(
+ List<DatabaseObjectName> objectNames, boolean quoteWithoutSchema) {
+ DatabaseMeta databaseMeta = data.db.getDatabaseMeta();
+ List<SchemaObject> objects = new ArrayList<>();
+ for (DatabaseObjectName object : objectNames) {
+ String outputName;
+ if (meta.isAddSchemaInOutput()) {
+ outputName =
+ databaseMeta.getQuotedSchemaTableCombination(
+ data.db, object.schemaOrCatalog(), object.name());
+ } else if (quoteWithoutSchema) {
+ outputName = databaseMeta.getQuotedSchemaTableCombination(data.db,
null, object.name());
+ } else {
+ outputName = object.name();
+ }
+ objects.add(new SchemaObject(object, outputName));
+ }
+ objects.sort(Comparator.comparing(SchemaObject::outputName,
String.CASE_INSENSITIVE_ORDER));
+ return objects;
+ }
+
+ /** A table or view and the name written to the output. */
+ private record SchemaObject(DatabaseObjectName object, String outputName) {}
+
private void processIncludeSchema(Object[] outputRow)
throws HopDatabaseException, HopTransformException, HopValueException {
// Schemas
diff --git
a/plugins/transforms/gettablenames/src/test/java/org/apache/hop/pipeline/transforms/gettablenames/GetTableNamesTest.java
b/plugins/transforms/gettablenames/src/test/java/org/apache/hop/pipeline/transforms/gettablenames/GetTableNamesTest.java
new file mode 100644
index 0000000000..6448b370cc
--- /dev/null
+++
b/plugins/transforms/gettablenames/src/test/java/org/apache/hop/pipeline/transforms/gettablenames/GetTableNamesTest.java
@@ -0,0 +1,288 @@
+/*
+ * 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.gettablenames;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.anyBoolean;
+import static org.mockito.ArgumentMatchers.anyString;
+import static org.mockito.ArgumentMatchers.eq;
+import static org.mockito.ArgumentMatchers.isNull;
+import static org.mockito.Mockito.doAnswer;
+import static org.mockito.Mockito.doReturn;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.never;
+import static org.mockito.Mockito.spy;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
+
+import java.util.ArrayList;
+import java.util.List;
+import org.apache.hop.core.Const;
+import org.apache.hop.core.database.Database;
+import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.DatabaseObjectName;
+import org.apache.hop.core.logging.ILoggingObject;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.core.variables.Variables;
+import org.apache.hop.pipeline.transforms.mock.TransformMockHelper;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+class GetTableNamesTest {
+ private TransformMockHelper<GetTableNamesMeta, GetTableNamesData> helper;
+ private GetTableNamesData data;
+ private Database db;
+ private GetTableNames transform;
+ private final List<Object[]> rows = new ArrayList<>();
+
+ @BeforeEach
+ void setUp() throws Exception {
+ helper =
+ new TransformMockHelper<>(
+ "Get table names", GetTableNamesMeta.class,
GetTableNamesData.class);
+ when(helper.logChannelFactory.create(any(), any(ILoggingObject.class)))
+ .thenReturn(helper.iLogChannel);
+ when(helper.iTransformMeta.isIncludeTable()).thenReturn(true);
+ when(helper.iTransformMeta.isAddSchemaInOutput()).thenReturn(false);
+
+ DatabaseMeta databaseMeta = mock(DatabaseMeta.class);
+ when(databaseMeta.getQuotedSchemaTableCombination(any(), any(),
anyString()))
+ .thenAnswer(
+ invocation -> {
+ String schema = invocation.getArgument(1);
+ String table = invocation.getArgument(2);
+ return schema == null ? table : schema + "." + table;
+ });
+
+ db = spy(new Database(null, new Variables(), databaseMeta));
+ doAnswer(invocation -> "a_system".equals(invocation.getArgument(0)))
+ .when(db)
+ .isSystemTable(anyString());
+ doReturn(new String[0]).when(db).getPrimaryKeyColumnNames(any(), any(),
anyString());
+ doReturn(new RowMeta()).when(db).getTableFieldsMeta(any(), anyString());
+ doAnswer(invocation -> "CREATE TABLE " + invocation.getArgument(0))
+ .when(db)
+ .getCreateTableStatement(
+ anyString(), any(IRowMeta.class), isNull(), anyBoolean(), any(),
anyBoolean());
+
+ // Tables in two schemas, an empty schema name lists both
+ doReturn(
+ List.of(
+ new DatabaseObjectName("hop", "public", "t_pub"),
+ new DatabaseObjectName("hop", "sales", "a_system"),
+ new DatabaseObjectName("hop", "sales", "t_sales")))
+ .when(db)
+ .getTableObjectNames(isNull(), isNull());
+
+ data = new GetTableNamesData();
+ data.db = db;
+ data.realSchemaName = null;
+ data.realIsSystemObjectFieldName = "isSystem";
+ data.outputRowMeta = new RowMeta();
+ data.outputRowMeta.addValueMeta(new ValueMetaString("tablename"));
+
+ transform =
+ spy(
+ new GetTableNames(
+ helper.transformMeta,
+ helper.iTransformMeta,
+ data,
+ 0,
+ helper.pipelineMeta,
+ helper.pipeline));
+ doAnswer(
+ invocation -> {
+ rows.add(invocation.getArgument(1));
+ return null;
+ })
+ .when(transform)
+ .putRow(any(), any());
+ }
+
+ @AfterEach
+ void tearDown() {
+ helper.cleanUp();
+ }
+
+ @Test
+ void looksUpEachTableInItsOwnSchema() throws Exception {
+ data.realSqlCreationFieldName = "sql";
+
+ transform.processIncludeTable(new Object[4]);
+
+ verify(db).getTableFieldsMeta("public", "t_pub");
+ verify(db).getTableFieldsMeta("sales", "t_sales");
+ verify(db).getPrimaryKeyColumnNames("hop", "public", "t_pub");
+ verify(db).getPrimaryKeyColumnNames("hop", "sales", "t_sales");
+
+ assertEquals(3, rows.size());
+ assertRow(rows.get(0), "a_system", true, "CREATE TABLE a_system");
+ assertRow(rows.get(1), "t_pub", false, "CREATE TABLE t_pub");
+ assertRow(rows.get(2), "t_sales", false, "CREATE TABLE t_sales");
+ }
+
+ @Test
+ void keepsSchemaInOutputAndPairsItWithTheRightTable() throws Exception {
+ when(helper.iTransformMeta.isAddSchemaInOutput()).thenReturn(true);
+ data.realSqlCreationFieldName = "sql";
+
+ transform.processIncludeTable(new Object[4]);
+
+ assertEquals(3, rows.size());
+ assertRow(rows.get(0), "public.t_pub", false, "CREATE TABLE public.t_pub");
+ assertRow(rows.get(1), "sales.a_system", true, "CREATE TABLE
sales.a_system");
+ assertRow(rows.get(2), "sales.t_sales", false, "CREATE TABLE
sales.t_sales");
+ }
+
+ @Test
+ void skipsTheFieldLookupWithoutSqlCreationField() throws Exception {
+ data.realSqlCreationFieldName = null;
+
+ transform.processIncludeTable(new Object[4]);
+
+ verify(db, never()).getTableFieldsMeta(any(), anyString());
+ verify(db, never()).getPrimaryKeyColumnNames(any(), any(), anyString());
+ assertEquals(3, rows.size());
+ }
+
+ @Test
+ void pairsViewsWithTheirOwnName() throws Exception {
+ when(helper.iTransformMeta.isIncludeTable()).thenReturn(false);
+ when(helper.iTransformMeta.isIncludeView()).thenReturn(true);
+ when(helper.iTransformMeta.isAddSchemaInOutput()).thenReturn(true);
+ doReturn(
+ List.of(
+ new DatabaseObjectName("hop", "public", "v_pub"),
+ new DatabaseObjectName("hop", "sales", "a_system")))
+ .when(db)
+ .getViewObjectNames(isNull());
+
+ transform.processIncludeView(new Object[4]);
+
+ verify(db).isSystemTable("v_pub");
+ verify(db).isSystemTable("a_system");
+ assertEquals(2, rows.size());
+ assertEquals("public.v_pub", rows.get(0)[0]);
+ assertEquals(false, rows.get(0)[1]);
+ assertEquals("sales.a_system", rows.get(1)[0]);
+ assertEquals(true, rows.get(1)[1]);
+ }
+
+ @Test
+ void passesTheCatalogToThePrimaryKeyLookupWhenTablesHaveNoSchema() throws
Exception {
+ // MySQL and MariaDB list a database as a catalog, the same table can be
in two of them
+ doReturn(
+ List.of(
+ new DatabaseObjectName("db1", null, "users"),
+ new DatabaseObjectName("db2", null, "users")))
+ .when(db)
+ .getTableObjectNames(isNull(), isNull());
+ when(helper.iTransformMeta.isAddSchemaInOutput()).thenReturn(true);
+ data.realSqlCreationFieldName = "sql";
+
+ transform.processIncludeTable(new Object[4]);
+
+ verify(db).getPrimaryKeyColumnNames("db1", null, "users");
+ verify(db).getPrimaryKeyColumnNames("db2", null, "users");
+ verify(db).getTableFieldsMeta("db1", "users");
+ verify(db).getTableFieldsMeta("db2", "users");
+ assertEquals(2, rows.size());
+ assertEquals("db1.users", rows.get(0)[0]);
+ assertEquals("db2.users", rows.get(1)[0]);
+ }
+
+ @Test
+ void addsAPrimaryKeyClauseOnceAndOnlyWhenThereIsAKey() throws Exception {
+ doReturn(
+ List.of(
+ new DatabaseObjectName("hop", "public", "t_none"),
+ new DatabaseObjectName("hop", "public", "t_one"),
+ new DatabaseObjectName("hop", "public", "t_one_text"),
+ new DatabaseObjectName("hop", "public", "t_two")))
+ .when(db)
+ .getTableObjectNames(isNull(), isNull());
+ doReturn(new String[0]).when(db).getPrimaryKeyColumnNames("hop", "public",
"t_none");
+ doReturn(new String[] {"id"}).when(db).getPrimaryKeyColumnNames("hop",
"public", "t_one");
+ doReturn(new String[] {"code"})
+ .when(db)
+ .getPrimaryKeyColumnNames("hop", "public", "t_one_text");
+ doReturn(new String[] {"a", "b"}).when(db).getPrimaryKeyColumnNames("hop",
"public", "t_two");
+ // Like most dialects, the statement marks a numeric key ("id") itself but
not a text key
+ doAnswer(
+ invocation -> {
+ String pk = invocation.getArgument(4);
+ return "CREATE TABLE "
+ + invocation.getArgument(0)
+ + "\n(\n n VARCHAR(100)\n"
+ + ("id".equals(pk) ? ", PRIMARY KEY (id)\n" : "")
+ + ")\n;";
+ })
+ .when(db)
+ .getCreateTableStatement(
+ anyString(), any(IRowMeta.class), isNull(), anyBoolean(), any(),
anyBoolean());
+ data.realSqlCreationFieldName = "sql";
+
+ transform.processIncludeTable(new Object[4]);
+
+ verify(db)
+ .getCreateTableStatement(
+ eq("t_none"), any(IRowMeta.class), isNull(), anyBoolean(),
isNull(), anyBoolean());
+ verify(db)
+ .getCreateTableStatement(
+ eq("t_one"), any(IRowMeta.class), isNull(), anyBoolean(),
eq("id"), anyBoolean());
+ verify(db)
+ .getCreateTableStatement(
+ eq("t_one_text"),
+ any(IRowMeta.class),
+ isNull(),
+ anyBoolean(),
+ eq("code"),
+ anyBoolean());
+ verify(db)
+ .getCreateTableStatement(
+ eq("t_two"), any(IRowMeta.class), isNull(), anyBoolean(),
isNull(), anyBoolean());
+ assertEquals(4, rows.size());
+ assertEquals("CREATE TABLE t_none\n(\n n VARCHAR(100)\n)\n;",
rows.get(0)[2]);
+ assertEquals(
+ "CREATE TABLE t_one\n(\n n VARCHAR(100)\n, PRIMARY KEY (id)\n)\n;",
rows.get(1)[2]);
+ assertEquals(
+ "CREATE TABLE t_one_text\n(\n n VARCHAR(100)\n, PRIMARY KEY (code)"
+ + Const.CR
+ + ")"
+ + Const.CR
+ + ";",
+ rows.get(2)[2]);
+ assertEquals(
+ "CREATE TABLE t_two\n(\n n VARCHAR(100)\n, PRIMARY KEY (a, b)"
+ + Const.CR
+ + ")"
+ + Const.CR
+ + ";",
+ rows.get(3)[2]);
+ }
+
+ private static void assertRow(Object[] row, String name, boolean isSystem,
String sql) {
+ assertEquals(name, row[0]);
+ assertEquals(isSystem, row[1]);
+ assertEquals(sql, row[2]);
+ }
+}