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]);
+  }
+}

Reply via email to