This is an automated email from the ASF dual-hosted git repository.
mattcasters 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 2a248a3a4e Show column definition in Database perspective, fixes #8464
(#8468)
2a248a3a4e is described below
commit 2a248a3a4eb92412f6e2e1b0d79582179b5cc7c5
Author: Matt Casters <[email protected]>
AuthorDate: Sun Sep 20 16:34:08 2026 +0200
Show column definition in Database perspective, fixes #8464 (#8468)
* Show column definition in Database perspective, fixes #8464
* Address review feedback on column definitions in Database perspective
(#8464)
- Drop unused sqlType and autoIncrement parameters from
DatabaseColumn.calculateDefinition
- Allow numeric/decimal precision up to 1000 when scale == 0 (e.g.
numeric(50))
- Remove redundant size < 10000000 check and duplicate DATE check
- Simplify empty typeName fallback in calculateDefinition(IValueMeta)
- Support sized bit(n) when n > 1 while preserving bare bit when n <= 1
- Validate TABLE_NAME, TABLE_CAT, and TABLE_SCHEM in readColumnDefinitions
to avoid cross-schema leakage
- Simplify fillColumns definition lookup to avoid redundant recalculation
- Add tests for bit(n), large numeric(50), wildcard character escaping in
table/schema, and schema filtering
---
.../hop/core/database/types/DatabaseColumn.java | 283 ++++++++++++++++++++-
.../database/types/StandardJdbcTypeMapper.java | 1 +
.../core/database/types/DatabaseColumnTest.java | 158 ++++++++++++
.../perspective/database/DatabaseTableInfoTab.java | 215 +++++++++++++++-
.../database/messages/messages_en_US.properties | 3 +-
.../database/DatabaseTableInfoTabTest.java | 186 ++++++++++++++
6 files changed, 834 insertions(+), 12 deletions(-)
diff --git
a/core/src/main/java/org/apache/hop/core/database/types/DatabaseColumn.java
b/core/src/main/java/org/apache/hop/core/database/types/DatabaseColumn.java
index 6a2d6fd4ba..46f0ed5a7a 100644
--- a/core/src/main/java/org/apache/hop/core/database/types/DatabaseColumn.java
+++ b/core/src/main/java/org/apache/hop/core/database/types/DatabaseColumn.java
@@ -19,6 +19,11 @@ package org.apache.hop.core.database.types;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
+import java.util.Locale;
+import org.apache.hop.core.Const;
+import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.util.Utils;
/**
* A database column described independently of which JDBC metadata API it
came from.
@@ -41,6 +46,7 @@ public final class DatabaseColumn {
private final int displaySize;
private final boolean signed;
private final String comment;
+ private final boolean autoIncrement;
/**
* The result set metadata this column came from, or null when it came from
a getColumns() row.
@@ -61,6 +67,7 @@ public final class DatabaseColumn {
int displaySize,
boolean signed,
String comment,
+ boolean autoIncrement,
ResultSetMetaData resultSetMetaData,
int columnIndex) {
this.name = name;
@@ -72,6 +79,7 @@ public final class DatabaseColumn {
this.displaySize = displaySize;
this.signed = signed;
this.comment = comment;
+ this.autoIncrement = autoIncrement;
this.resultSetMetaData = resultSetMetaData;
this.columnIndex = columnIndex;
}
@@ -97,6 +105,7 @@ public final class DatabaseColumn {
rm.getColumnDisplaySize(index),
readSigned(rm, index),
rm.getColumnLabel(index),
+ readAutoIncrement(rm, index),
rm,
index);
}
@@ -113,6 +122,12 @@ public final class DatabaseColumn {
public static DatabaseColumn ofColumnsRow(ResultSet columnsRow) throws
SQLException {
int columnSize = columnsRow.getInt("COLUMN_SIZE");
Object decimalDigits = columnsRow.getObject("DECIMAL_DIGITS");
+ boolean autoIncrement = false;
+ try {
+ autoIncrement =
"YES".equalsIgnoreCase(columnsRow.getString("IS_AUTOINCREMENT"));
+ } catch (Exception ignored) {
+ // Driver-dependent.
+ }
return new DatabaseColumn(
columnsRow.getString("COLUMN_NAME"),
columnsRow.getString("TABLE_NAME"),
@@ -123,6 +138,7 @@ public final class DatabaseColumn {
columnSize,
true,
columnsRow.getString("REMARKS"),
+ autoIncrement,
null,
-1);
}
@@ -147,6 +163,19 @@ public final class DatabaseColumn {
}
}
+ /**
+ * Not every JDBC driver implements isAutoIncrement(); those that don't are
treated as not auto
+ * increment.
+ */
+ private static boolean readAutoIncrement(ResultSetMetaData rm, int index) {
+ try {
+ return rm.isAutoIncrement(index);
+ } catch (Exception ignored) {
+ // This JDBC driver doesn't support the isAutoIncrement method.
+ return false;
+ }
+ }
+
public String getName() {
return name;
}
@@ -194,6 +223,10 @@ public final class DatabaseColumn {
return comment;
}
+ public boolean isAutoIncrement() {
+ return autoIncrement;
+ }
+
public ResultSetMetaData getResultSetMetaData() {
return resultSetMetaData;
}
@@ -208,7 +241,255 @@ public final class DatabaseColumn {
*/
public static DatabaseColumn of(
String name, int sqlType, String nativeTypeName, int precision, int
scale, int displaySize) {
+ return of(name, sqlType, nativeTypeName, precision, scale, displaySize,
false);
+ }
+
+ /**
+ * Describes a column with an explicit auto increment indicator when the
caller already has JDBC
+ * type information.
+ */
+ public static DatabaseColumn of(
+ String name,
+ int sqlType,
+ String nativeTypeName,
+ int precision,
+ int scale,
+ int displaySize,
+ boolean autoIncrement) {
return new DatabaseColumn(
- name, null, sqlType, nativeTypeName, precision, scale, displaySize,
true, null, null, -1);
+ name,
+ null,
+ sqlType,
+ nativeTypeName,
+ precision,
+ scale,
+ displaySize,
+ true,
+ null,
+ autoIncrement,
+ null,
+ -1);
+ }
+
+ /**
+ * The definition of this column as defined in the database (for example:
varchar(100), bigint,
+ * int identity, bool, float, timestamp(6), numeric(10, 2)).
+ */
+ public String getDefinition() {
+ return calculateDefinition(
+ nativeTypeName, precision, scale, displaySize > 0 ? displaySize :
precision);
+ }
+
+ /**
+ * Calculates the database column definition for an {@link IValueMeta} using
whatever original
+ * database metadata was attached to it.
+ */
+ public static String calculateDefinition(IValueMeta valueMeta) {
+ if (valueMeta == null) {
+ return "";
+ }
+ String typeName = valueMeta.getOriginalColumnTypeName();
+ if (Utils.isEmpty(typeName)) {
+ return Const.NVL(valueMeta.getTypeDesc(), "");
+ }
+ int precision =
+ valueMeta.getLength() > 0 ? valueMeta.getLength() :
valueMeta.getOriginalPrecision();
+ int scale;
+ if (valueMeta.getType() == IValueMeta.TYPE_TIMESTAMP
+ || valueMeta.getType() == IValueMeta.TYPE_DATE) {
+ scale =
+ valueMeta.getOriginalScale() > 0 ? valueMeta.getOriginalScale() :
valueMeta.getLength();
+ } else {
+ scale =
+ valueMeta.getPrecision() >= 0 ? valueMeta.getPrecision() :
valueMeta.getOriginalScale();
+ }
+ int length = valueMeta.getLength();
+ return calculateDefinition(typeName, precision, scale, length);
+ }
+
+ /**
+ * Formats the database column data type definition given the native type
name and column
+ * dimensions.
+ */
+ public static String calculateDefinition(
+ String nativeTypeName, int precision, int scale, int length) {
+ if (Utils.isEmpty(nativeTypeName)) {
+ return "";
+ }
+ String trimmed = nativeTypeName.trim();
+ if (trimmed.contains("(")) {
+ return trimmed;
+ }
+
+ String upper = trimmed.toUpperCase(Locale.ROOT);
+
+ // 1. Types that do not take length/precision
+ if (isIntegerType(upper)
+ || isFloatingPointType(upper)
+ || isBooleanType(upper)
+ || isUnsizedType(upper)) {
+ return trimmed;
+ }
+
+ // 2. String/Character, Binary, and Bit types: VARCHAR, CHAR, NVARCHAR,
NCHAR, VARCHAR2,
+ // NVARCHAR2,
+ // BPCHAR, BINARY, VARBINARY, RAW, BIT
+ if (isSizedStringType(upper) || isSizedBinaryType(upper) ||
isSizedBitType(upper)) {
+ int size = precision > 0 ? precision : length;
+ if (upper.equals("BIT") && size <= 1) {
+ return trimmed;
+ }
+ if (size > 0 && size < DatabaseMeta.CLOB_LENGTH) {
+ return trimmed + "(" + size + ")";
+ }
+ return trimmed;
+ }
+
+ // 3. Decimal/Numeric types: DECIMAL, NUMERIC, NUMBER, DEC
+ if (isDecimalType(upper)) {
+ int p = precision > 0 ? precision : length;
+ if (p > 0 && p <= 1000) {
+ if (scale > 0) {
+ return trimmed + "(" + p + ", " + scale + ")";
+ } else if (scale == 0) {
+ return trimmed + "(" + p + ")";
+ }
+ }
+ return trimmed;
+ }
+
+ // 4. Timestamp / Time / DateTime types: TIMESTAMP, TIMESTAMPTZ, TIME,
TIMETZ, DATETIME2
+ if (isDateTimeWithPrecision(upper)) {
+ int fracSec =
+ (scale >= 0 && scale <= 9) ? scale : ((length >= 0 && length <= 9) ?
length : -1);
+ if (fracSec > 0) {
+ int tzIndex = -1;
+ if (upper.contains(" WITHOUT TIME ZONE")) {
+ tzIndex = upper.indexOf(" WITHOUT TIME ZONE");
+ } else if (upper.contains(" WITH LOCAL TIME ZONE")) {
+ tzIndex = upper.indexOf(" WITH LOCAL TIME ZONE");
+ } else if (upper.contains(" WITH TIME ZONE")) {
+ tzIndex = upper.indexOf(" WITH TIME ZONE");
+ }
+ if (tzIndex >= 0) {
+ return trimmed.substring(0, tzIndex) + "(" + fracSec + ")" +
trimmed.substring(tzIndex);
+ } else {
+ return trimmed + "(" + fracSec + ")";
+ }
+ }
+ return trimmed;
+ }
+
+ return trimmed;
+ }
+
+ private static boolean isIntegerType(String upper) {
+ return upper.equals("BIGINT")
+ || upper.startsWith("BIGINT ")
+ || upper.equals("INT")
+ || upper.startsWith("INT ")
+ || upper.equals("INTEGER")
+ || upper.startsWith("INTEGER ")
+ || upper.equals("SMALLINT")
+ || upper.startsWith("SMALLINT ")
+ || upper.equals("TINYINT")
+ || upper.startsWith("TINYINT ")
+ || upper.equals("MEDIUMINT")
+ || upper.startsWith("MEDIUMINT ")
+ || upper.equals("INT2")
+ || upper.equals("INT4")
+ || upper.equals("INT8")
+ || upper.equals("INT16")
+ || upper.equals("INT32")
+ || upper.equals("INT64")
+ || upper.equals("UINT")
+ || upper.equals("SERIAL")
+ || upper.equals("BIGSERIAL")
+ || upper.equals("SMALLSERIAL");
+ }
+
+ private static boolean isFloatingPointType(String upper) {
+ return upper.equals("FLOAT")
+ || upper.startsWith("FLOAT ")
+ || upper.equals("DOUBLE")
+ || upper.startsWith("DOUBLE ")
+ || upper.equals("REAL")
+ || upper.equals("DOUBLE PRECISION")
+ || upper.equals("FLOAT4")
+ || upper.equals("FLOAT8")
+ || upper.equals("BINARY_FLOAT")
+ || upper.equals("BINARY_DOUBLE");
+ }
+
+ private static boolean isBooleanType(String upper) {
+ return upper.equals("BOOL") || upper.equals("BOOLEAN");
+ }
+
+ private static boolean isSizedBitType(String upper) {
+ return upper.equals("BIT") || upper.equals("BIT VARYING") ||
upper.equals("VARBIT");
+ }
+
+ private static boolean isUnsizedType(String upper) {
+ return upper.equals("TEXT")
+ || upper.equals("TINYTEXT")
+ || upper.equals("MEDIUMTEXT")
+ || upper.equals("LONGTEXT")
+ || upper.equals("CLOB")
+ || upper.equals("NCLOB")
+ || upper.equals("BLOB")
+ || upper.equals("BYTEA")
+ || upper.equals("IMAGE")
+ || upper.equals("JSON")
+ || upper.equals("JSONB")
+ || upper.equals("XML")
+ || upper.equals("UUID")
+ || upper.equals("UNIQUEIDENTIFIER")
+ || upper.equals("MONEY")
+ || upper.equals("SMALLMONEY")
+ || upper.equals("DATETIME")
+ || upper.equals("SMALLDATETIME")
+ || upper.equals("ROWVERSION")
+ || upper.equals("DATE")
+ || upper.equals("GEOMETRY")
+ || upper.equals("GEOGRAPHY")
+ || upper.equals("HIERARCHYID")
+ || upper.equals("SQL_VARIANT")
+ || upper.equals("SYSNAME");
+ }
+
+ private static boolean isSizedStringType(String upper) {
+ return upper.equals("VARCHAR")
+ || upper.equals("CHAR")
+ || upper.equals("CHARACTER")
+ || upper.equals("CHARACTER VARYING")
+ || upper.equals("VARCHAR2")
+ || upper.equals("NVARCHAR")
+ || upper.equals("NVARCHAR2")
+ || upper.equals("NCHAR")
+ || upper.equals("BPCHAR");
+ }
+
+ private static boolean isSizedBinaryType(String upper) {
+ return upper.equals("BINARY") || upper.equals("VARBINARY") ||
upper.equals("RAW");
+ }
+
+ private static boolean isDecimalType(String upper) {
+ return upper.equals("DECIMAL")
+ || upper.equals("NUMERIC")
+ || upper.equals("NUMBER")
+ || upper.equals("DEC");
+ }
+
+ private static boolean isDateTimeWithPrecision(String upper) {
+ return upper.equals("TIMESTAMP")
+ || upper.startsWith("TIMESTAMP ")
+ || upper.equals("TIMESTAMPTZ")
+ || upper.startsWith("TIMESTAMPTZ ")
+ || upper.equals("DATETIME2")
+ || upper.startsWith("DATETIME2 ")
+ || upper.equals("TIME")
+ || upper.startsWith("TIME ")
+ || upper.equals("TIMETZ")
+ || upper.startsWith("TIMETZ ");
}
}
diff --git
a/core/src/main/java/org/apache/hop/core/database/types/StandardJdbcTypeMapper.java
b/core/src/main/java/org/apache/hop/core/database/types/StandardJdbcTypeMapper.java
index 751c809cbc..c2d8f2906c 100644
---
a/core/src/main/java/org/apache/hop/core/database/types/StandardJdbcTypeMapper.java
+++
b/core/src/main/java/org/apache/hop/core/database/types/StandardJdbcTypeMapper.java
@@ -333,6 +333,7 @@ public final class StandardJdbcTypeMapper {
valueMeta.setOriginalColumnTypeName(column.getNativeTypeName());
valueMeta.setOriginalPrecision(ignoreLength ? -1 : column.getPrecision());
valueMeta.setOriginalScale(column.getScale());
+ valueMeta.setOriginalAutoIncrement(column.isAutoIncrement());
valueMeta.setOriginalSigned(column.isSigned());
}
}
diff --git
a/core/src/test/java/org/apache/hop/core/database/types/DatabaseColumnTest.java
b/core/src/test/java/org/apache/hop/core/database/types/DatabaseColumnTest.java
new file mode 100644
index 0000000000..2ea961178f
--- /dev/null
+++
b/core/src/test/java/org/apache/hop/core/database/types/DatabaseColumnTest.java
@@ -0,0 +1,158 @@
+/*
+ * 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.types;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import java.sql.Types;
+import org.apache.hop.core.HopClientEnvironment;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaNumber;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.core.row.value.ValueMetaTimestamp;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+
+class DatabaseColumnTest {
+
+ @BeforeAll
+ static void setUp() throws Exception {
+ HopClientEnvironment.init();
+ }
+
+ @Test
+ void calculatesDefinitionsForStandardTypes() {
+ // Exact examples from issue #8464:
+ // varchar(100), bigint, int identity, bool, float, timestamp(6)
+ assertEquals("varchar(100)", DatabaseColumn.calculateDefinition("varchar",
100, 0, 100));
+ assertEquals("VARCHAR(50)", DatabaseColumn.calculateDefinition("VARCHAR",
50, 0, 50));
+ assertEquals("bigint", DatabaseColumn.calculateDefinition("bigint", 19, 0,
15));
+ assertEquals("int identity", DatabaseColumn.calculateDefinition("int
identity", 10, 0, 9));
+ assertEquals("bool", DatabaseColumn.calculateDefinition("bool", 1, 0, -1));
+ assertEquals("float", DatabaseColumn.calculateDefinition("float", 53, 0,
-1));
+ assertEquals("timestamp(6)",
DatabaseColumn.calculateDefinition("timestamp", 26, 6, 6));
+ }
+
+ @Test
+ void calculatesNumericAndDecimalDefinitions() {
+ assertEquals("numeric(10, 2)",
DatabaseColumn.calculateDefinition("numeric", 10, 2, 10));
+ assertEquals("DECIMAL(12, 4)",
DatabaseColumn.calculateDefinition("DECIMAL", 12, 4, 12));
+ assertEquals("NUMBER(10)", DatabaseColumn.calculateDefinition("NUMBER",
10, 0, 10));
+ assertEquals("numeric(50)", DatabaseColumn.calculateDefinition("numeric",
50, 0, 50));
+ assertEquals("numeric(50, 2)",
DatabaseColumn.calculateDefinition("numeric", 50, 2, 50));
+ assertEquals("numeric", DatabaseColumn.calculateDefinition("numeric", 0,
0, -1));
+ }
+
+ @Test
+ void calculatesDateTimeDefinitions() {
+ assertEquals("datetime2(7)",
DatabaseColumn.calculateDefinition("datetime2", 27, 7, 7));
+ assertEquals("time(3)", DatabaseColumn.calculateDefinition("time", 12, 3,
3));
+ assertEquals("timestamptz(6)",
DatabaseColumn.calculateDefinition("timestamptz", 29, 6, 6));
+ assertEquals(
+ "timestamp(6) without time zone",
+ DatabaseColumn.calculateDefinition("timestamp without time zone", 29,
6, 6));
+ assertEquals(
+ "timestamp(6) with local time zone",
+ DatabaseColumn.calculateDefinition("timestamp with local time zone",
29, 6, 6));
+ assertEquals(
+ "TIMESTAMP(6) WITH LOCAL TIME ZONE",
+ DatabaseColumn.calculateDefinition("TIMESTAMP WITH LOCAL TIME ZONE",
29, 6, 6));
+ assertEquals("timestamp", DatabaseColumn.calculateDefinition("timestamp",
19, 0, 0));
+ assertEquals("date", DatabaseColumn.calculateDefinition("date", 10, 0,
-1));
+ assertEquals("datetime", DatabaseColumn.calculateDefinition("datetime",
23, 3, 3));
+ assertEquals("smalldatetime",
DatabaseColumn.calculateDefinition("smalldatetime", 16, 0, 0));
+ }
+
+ @Test
+ void calculatesSizedCharacterAndBinaryDefinitions() {
+ assertEquals("char(10)", DatabaseColumn.calculateDefinition("char", 10, 0,
10));
+ assertEquals("nvarchar(50)",
DatabaseColumn.calculateDefinition("nvarchar", 50, 0, 50));
+ assertEquals("varbinary(64)",
DatabaseColumn.calculateDefinition("varbinary", 64, 0, 64));
+ assertEquals("binary(16)", DatabaseColumn.calculateDefinition("binary",
16, 0, 16));
+ assertEquals("bit", DatabaseColumn.calculateDefinition("bit", 1, 0, 1));
+ assertEquals("bit", DatabaseColumn.calculateDefinition("bit", 0, 0, -1));
+ assertEquals("bit(8)", DatabaseColumn.calculateDefinition("bit", 8, 0, 8));
+ assertEquals("varbit(16)", DatabaseColumn.calculateDefinition("varbit",
16, 0, 16));
+ }
+
+ @Test
+ void preservesUnsizedAndComplexDefinitions() {
+ assertEquals("text", DatabaseColumn.calculateDefinition("text",
2147483647, 0, -1));
+ assertEquals("clob", DatabaseColumn.calculateDefinition("clob",
2147483647, 0, -1));
+ assertEquals("blob", DatabaseColumn.calculateDefinition("blob",
2147483647, 0, -1));
+ assertEquals("json", DatabaseColumn.calculateDefinition("json", 0, 0, -1));
+ assertEquals("jsonb", DatabaseColumn.calculateDefinition("jsonb", 0, 0,
-1));
+ assertEquals("uuid", DatabaseColumn.calculateDefinition("uuid", 0, 0, -1));
+ assertEquals(
+ "uniqueidentifier",
DatabaseColumn.calculateDefinition("uniqueidentifier", 36, 0, 36));
+ assertEquals("money", DatabaseColumn.calculateDefinition("money", 19, 4,
19));
+ assertEquals("smallmoney",
DatabaseColumn.calculateDefinition("smallmoney", 10, 4, 10));
+ assertEquals("varchar(100)",
DatabaseColumn.calculateDefinition("varchar(100)", 100, 0, 100));
+ assertEquals("enum('a', 'b')",
DatabaseColumn.calculateDefinition("enum('a', 'b')", 0, 0, -1));
+ }
+
+ @Test
+ void calculatesDefinitionFromDatabaseColumnInstance() {
+ DatabaseColumn col = DatabaseColumn.of("c_name", Types.VARCHAR, "varchar",
100, 0, 100);
+ assertEquals("varchar(100)", col.getDefinition());
+ assertFalse(col.isAutoIncrement());
+
+ DatabaseColumn colAi = DatabaseColumn.of("id", Types.INTEGER, "int", 10,
0, 9, true);
+ assertEquals("int", colAi.getDefinition());
+ assertTrue(colAi.isAutoIncrement());
+
+ DatabaseColumn colIdentity =
+ DatabaseColumn.of("id", Types.INTEGER, "int identity", 10, 0, 9, true);
+ assertEquals("int identity", colIdentity.getDefinition());
+ }
+
+ @Test
+ void calculatesDefinitionFromValueMeta() {
+ IValueMeta vmString = new ValueMetaString("name");
+ vmString.setOriginalColumnTypeName("varchar");
+ vmString.setOriginalColumnType(Types.VARCHAR);
+ vmString.setLength(100);
+ assertEquals("varchar(100)", DatabaseColumn.calculateDefinition(vmString));
+
+ IValueMeta vmInt = new ValueMetaInteger("id");
+ vmInt.setOriginalColumnTypeName("bigint");
+ vmInt.setOriginalColumnType(Types.BIGINT);
+ vmInt.setLength(15);
+ assertEquals("bigint", DatabaseColumn.calculateDefinition(vmInt));
+
+ IValueMeta vmNumeric = new ValueMetaNumber("amount");
+ vmNumeric.setOriginalColumnTypeName("numeric");
+ vmNumeric.setOriginalColumnType(Types.NUMERIC);
+ vmNumeric.setLength(10);
+ vmNumeric.setPrecision(2);
+ assertEquals("numeric(10, 2)",
DatabaseColumn.calculateDefinition(vmNumeric));
+
+ IValueMeta vmTs = new ValueMetaTimestamp("ts");
+ vmTs.setOriginalColumnTypeName("timestamp");
+ vmTs.setOriginalColumnType(Types.TIMESTAMP);
+ vmTs.setLength(6);
+ assertEquals("timestamp(6)", DatabaseColumn.calculateDefinition(vmTs));
+
+ // Fallback to Hop type description when originalColumnTypeName is null
+ IValueMeta vmFallback = new ValueMetaString("raw_field");
+ assertEquals("String", DatabaseColumn.calculateDefinition(vmFallback));
+ }
+}
diff --git
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseTableInfoTab.java
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseTableInfoTab.java
index 3d4365a0ad..2397c0217e 100644
---
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseTableInfoTab.java
+++
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseTableInfoTab.java
@@ -32,6 +32,7 @@ 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.DatabaseObjectDdl;
+import org.apache.hop.core.database.types.DatabaseColumn;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.IValueMeta;
@@ -205,6 +206,7 @@ public class DatabaseTableInfoTab implements
IHopFileTypeHandler {
IRowMeta fields;
List<DatabaseIndexInfo> indexes;
String ddl;
+ Map<String, String> definitions;
boolean view = kind == DatabaseTreeNode.Kind.VIEW;
try (Database db =
new Database(host.getLoggingObject(), host.getVariables(),
databaseMeta)) {
@@ -215,6 +217,7 @@ public class DatabaseTableInfoTab implements
IHopFileTypeHandler {
}
fields = loadFields(db, qualified);
indexes = loadIndexes(db, schemaName, tableName);
+ definitions = loadColumnDefinitions(db, schemaName, tableName,
fields);
try {
ddl = db.getObjectDdl(schemaName, tableName, view, fields);
} catch (Exception e) {
@@ -223,12 +226,13 @@ public class DatabaseTableInfoTab implements
IHopFileTypeHandler {
}
IRowMeta loadedFields = fields;
List<DatabaseIndexInfo> loadedIndexes = indexes;
+ Map<String, String> loadedDefinitions = definitions;
String loadedDdl =
view
? ddl
: withIndexStatements(
databaseMeta, host.getVariables(), schemaName,
tableName, ddl, indexes);
- host.asyncExec(() -> populate(loadedFields, loadedIndexes,
loadedDdl));
+ host.asyncExec(() -> populate(loadedFields, loadedIndexes,
loadedDdl, loadedDefinitions));
});
}
@@ -277,11 +281,193 @@ public class DatabaseTableInfoTab implements
IHopFileTypeHandler {
return new ArrayList<>(byName.values());
}
- private void populate(IRowMeta fields, List<DatabaseIndexInfo> indexes,
String ddl) {
+ static Map<String, String> loadColumnDefinitions(
+ Database db, String schemaName, String tableName, IRowMeta fields) {
+ Map<String, String> definitions = new HashMap<>();
+ if (db != null) {
+ try {
+ DatabaseMetaData metaData = db.getDatabaseMetaData();
+ if (metaData != null) {
+ String escape = null;
+ try {
+ escape = metaData.getSearchStringEscape();
+ } catch (Exception ignored) {
+ // Driver-dependent.
+ }
+ boolean supportsCatalogs = false;
+ boolean supportsSchemas = false;
+ try {
+ supportsCatalogs = metaData.supportsCatalogsInTableDefinitions();
+ supportsSchemas = metaData.supportsSchemasInTableDefinitions();
+ } catch (Exception ignored) {
+ // Driver-dependent.
+ }
+
+ String catalog = null;
+ String schemaPattern = Utils.isEmpty(schemaName) ? null : schemaName;
+ if (supportsCatalogs && !supportsSchemas) {
+ // Catalogs are used instead of schemas (e.g. MySQL / MariaDB)
+ catalog = schemaName;
+ schemaPattern = null;
+ } else {
+ try {
+ if (supportsCatalogs && db.getConnection() != null) {
+ catalog = db.getConnection().getCatalog();
+ }
+ } catch (Exception ignored) {
+ // Driver-dependent.
+ }
+ }
+
+ String escapedSchema = escapePattern(schemaPattern, escape);
+ String escapedTable = escapePattern(tableName, escape);
+
+ readColumnDefinitions(
+ metaData,
+ catalog,
+ escapedSchema,
+ escapedTable,
+ catalog,
+ schemaPattern,
+ tableName,
+ definitions);
+
+ // If empty and schemaName was provided, try using schemaName as
catalog
+ if (definitions.isEmpty() && !Utils.isEmpty(schemaName)) {
+ if (catalog == null || !schemaName.equals(catalog)) {
+ readColumnDefinitions(
+ metaData,
+ schemaName,
+ null,
+ escapedTable,
+ schemaName,
+ null,
+ tableName,
+ definitions);
+ }
+ }
+
+ if (definitions.isEmpty() && !Utils.isEmpty(tableName)) {
+ String upperTable =
escapePattern(tableName.toUpperCase(Locale.ROOT), escape);
+ readColumnDefinitions(
+ metaData,
+ catalog,
+ escapedSchema,
+ upperTable,
+ catalog,
+ schemaPattern,
+ tableName,
+ definitions);
+ if (definitions.isEmpty()) {
+ String lowerTable =
escapePattern(tableName.toLowerCase(Locale.ROOT), escape);
+ readColumnDefinitions(
+ metaData,
+ catalog,
+ escapedSchema,
+ lowerTable,
+ catalog,
+ schemaPattern,
+ tableName,
+ definitions);
+ }
+ }
+ }
+ } catch (Exception ignored) {
+ // Fall back to fields.
+ }
+ }
+ if (fields != null) {
+ for (int i = 0; i < fields.size(); i++) {
+ IValueMeta value = fields.getValueMeta(i);
+ if (value != null && !Utils.isEmpty(value.getName())) {
+ String key = value.getName().toLowerCase(Locale.ROOT);
+ if (!definitions.containsKey(key)) {
+ definitions.put(key, DatabaseColumn.calculateDefinition(value));
+ }
+ }
+ }
+ }
+ return definitions;
+ }
+
+ static String escapePattern(String value, String escape) {
+ if (Utils.isEmpty(value) || Utils.isEmpty(escape)) {
+ return value;
+ }
+ char escapeChar = escape.charAt(0);
+ StringBuilder escaped = new StringBuilder(value.length() + 4);
+ for (char c : value.toCharArray()) {
+ if (c == '_' || c == '%' || c == escapeChar) {
+ escaped.append(escapeChar);
+ }
+ escaped.append(c);
+ }
+ return escaped.toString();
+ }
+
+ private static void readColumnDefinitions(
+ DatabaseMetaData metaData,
+ String catalog,
+ String schemaPattern,
+ String tableNamePattern,
+ String expectedCatalog,
+ String expectedSchema,
+ String expectedTableName,
+ Map<String, String> definitions) {
+ try (ResultSet columns = metaData.getColumns(catalog, schemaPattern,
tableNamePattern, null)) {
+ if (columns != null) {
+ String chosenSchema = null;
+ while (columns.next()) {
+ try {
+ if (!Utils.isEmpty(expectedTableName)) {
+ String actualTable = columns.getString("TABLE_NAME");
+ if (!Utils.isEmpty(actualTable) &&
!expectedTableName.equalsIgnoreCase(actualTable)) {
+ continue;
+ }
+ }
+ String actualSchema = columns.getString("TABLE_SCHEM");
+ if (!Utils.isEmpty(expectedSchema)) {
+ if (!Utils.isEmpty(actualSchema) &&
!expectedSchema.equalsIgnoreCase(actualSchema)) {
+ continue;
+ }
+ } else if (!Utils.isEmpty(actualSchema)) {
+ if (chosenSchema == null) {
+ chosenSchema = actualSchema;
+ } else if (!chosenSchema.equalsIgnoreCase(actualSchema)) {
+ continue;
+ }
+ }
+ if (!Utils.isEmpty(expectedCatalog)) {
+ String actualCatalog = columns.getString("TABLE_CAT");
+ if (!Utils.isEmpty(actualCatalog)
+ && !expectedCatalog.equalsIgnoreCase(actualCatalog)) {
+ continue;
+ }
+ }
+ DatabaseColumn column = DatabaseColumn.ofColumnsRow(columns);
+ if (column != null && !Utils.isEmpty(column.getName())) {
+ definitions.putIfAbsent(
+ column.getName().toLowerCase(Locale.ROOT),
column.getDefinition());
+ }
+ } catch (Exception ignored) {
+ // Ignore single column read errors
+ }
+ }
+ }
+ } catch (Exception ignored) {
+ // Driver error on getColumns
+ }
+ }
+
+ private void populate(
+ IRowMeta fields,
+ List<DatabaseIndexInfo> indexes,
+ String ddl,
+ Map<String, String> definitions) {
if (control.isDisposed()) {
return;
}
- fillColumns(fields);
+ fillColumns(fields, definitions);
fillIndexes(indexes);
fillDdl(ddl);
}
@@ -346,7 +532,7 @@ public class DatabaseTableInfoTab implements
IHopFileTypeHandler {
|| DatabaseObjectDdl.startsWithCreate(ddl) && upper.contains(" KEY ");
}
- private void fillColumns(IRowMeta fields) {
+ private void fillColumns(IRowMeta fields, Map<String, String> definitions) {
columnsView.clearAll(false);
if (fields == null) {
columnsView.removeEmptyRows();
@@ -357,11 +543,16 @@ public class DatabaseTableInfoTab implements
IHopFileTypeHandler {
IValueMeta value = fields.getValueMeta(i);
TableItem item =
i == 0 ? columnsView.table.getItem(0) : new
TableItem(columnsView.table, SWT.NONE);
+ String definition =
+ definitions != null && !Utils.isEmpty(value.getName())
+ ?
Const.NVL(definitions.get(value.getName().toLowerCase(Locale.ROOT)), "")
+ : DatabaseColumn.calculateDefinition(value);
item.setText(1, Const.NVL(value.getName(), ""));
- item.setText(2, Const.NVL(value.getTypeDesc(), ""));
- item.setText(3, value.getLength() >= 0 ?
Integer.toString(value.getLength()) : "");
- item.setText(4, value.getPrecision() >= 0 ?
Integer.toString(value.getPrecision()) : "");
- item.setText(5, Const.NVL(value.getComments(), ""));
+ item.setText(2, Const.NVL(definition, ""));
+ item.setText(3, Const.NVL(value.getTypeDesc(), ""));
+ item.setText(4, value.getLength() >= 0 ?
Integer.toString(value.getLength()) : "");
+ item.setText(5, value.getPrecision() >= 0 ?
Integer.toString(value.getPrecision()) : "");
+ item.setText(6, Const.NVL(value.getComments(), ""));
}
columnsView.removeEmptyRows();
columnsView.setRowNums();
@@ -392,14 +583,18 @@ public class DatabaseTableInfoTab implements
IHopFileTypeHandler {
indexesView.optWidth(true);
}
- private ColumnInfo[] columnInfos() {
+ static ColumnInfo[] columnInfos() {
return new ColumnInfo[] {
new ColumnInfo(
BaseMessages.getString(PKG,
"DatabasePerspective.TableInfo.Column.Name"),
ColumnInfo.COLUMN_TYPE_TEXT,
false),
new ColumnInfo(
- BaseMessages.getString(PKG,
"DatabasePerspective.TableInfo.Column.Type"),
+ BaseMessages.getString(PKG,
"DatabasePerspective.TableInfo.Column.Definition"),
+ ColumnInfo.COLUMN_TYPE_TEXT,
+ false),
+ new ColumnInfo(
+ BaseMessages.getString(PKG,
"DatabasePerspective.TableInfo.Column.HopType"),
ColumnInfo.COLUMN_TYPE_TEXT,
false),
new ColumnInfo(
diff --git
a/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
b/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
index 21ccda47e7..80688d969c 100644
---
a/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
+++
b/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
@@ -87,7 +87,8 @@ DatabasePerspective.TableInfo.Columns=Columns
DatabasePerspective.TableInfo.Indexes=Indexes
DatabasePerspective.TableInfo.Ddl=DDL
DatabasePerspective.TableInfo.Column.Name=Name
-DatabasePerspective.TableInfo.Column.Type=Type
+DatabasePerspective.TableInfo.Column.Definition=Definition
+DatabasePerspective.TableInfo.Column.HopType=Hop Type
DatabasePerspective.TableInfo.Column.Length=Length
DatabasePerspective.TableInfo.Column.Precision=Precision
DatabasePerspective.TableInfo.Column.Comments=Comments
diff --git
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseTableInfoTabTest.java
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseTableInfoTabTest.java
index ee7614813f..59f3f607c3 100644
---
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseTableInfoTabTest.java
+++
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseTableInfoTabTest.java
@@ -25,9 +25,20 @@ import static org.mockito.ArgumentMatchers.eq;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.when;
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
import java.util.List;
+import java.util.Map;
+import java.util.concurrent.atomic.AtomicInteger;
+import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaString;
import org.apache.hop.core.variables.IVariables;
+import org.apache.hop.ui.core.widget.ColumnInfo;
import org.junit.jupiter.api.Test;
class DatabaseTableInfoTabTest {
@@ -78,4 +89,179 @@ class DatabaseTableInfoTabTest {
"CREATE TABLE `t` (\n `id` int,\n KEY `idx` (`id`)\n)"));
assertFalse(DatabaseTableInfoTab.catalogDdlIncludesIndexes("CREATE TABLE t
(id INTEGER);"));
}
+
+ @Test
+ void columnInfosIncludesDefinition() {
+ ColumnInfo[] infos = DatabaseTableInfoTab.columnInfos();
+ assertEquals(6, infos.length);
+ assertEquals("Name", infos[0].getName());
+ assertEquals("Definition", infos[1].getName());
+ assertEquals("Hop Type", infos[2].getName());
+ assertEquals("Length", infos[3].getName());
+ assertEquals("Precision", infos[4].getName());
+ assertEquals("Comments", infos[5].getName());
+ }
+
+ @Test
+ void loadColumnDefinitionsFromMetaData() throws Exception {
+ Database db = mock(Database.class);
+ DatabaseMetaData metaData = mock(DatabaseMetaData.class);
+ ResultSet rs = mock(ResultSet.class);
+ ResultSetMetaData rsmd = mock(ResultSetMetaData.class);
+
+ when(db.getDatabaseMetaData()).thenReturn(metaData);
+ when(metaData.getColumns(any(), any(), eq("orders"),
any())).thenReturn(rs);
+ when(rs.getMetaData()).thenReturn(rsmd);
+ when(rsmd.getColumnCount()).thenReturn(10);
+ when(rs.next()).thenReturn(true, false);
+ when(rs.getString("COLUMN_NAME")).thenReturn("customer_name");
+ when(rs.getInt("DATA_TYPE")).thenReturn(java.sql.Types.VARCHAR);
+ when(rs.getString("TYPE_NAME")).thenReturn("varchar");
+ when(rs.getInt("COLUMN_SIZE")).thenReturn(100);
+
+ Map<String, String> defs =
+ DatabaseTableInfoTab.loadColumnDefinitions(db, "public", "orders",
null);
+ assertEquals("varchar(100)", defs.get("customer_name"));
+ }
+
+ @Test
+ void loadColumnDefinitionsFallsBackToFields() {
+ IRowMeta rowMeta = new RowMeta();
+ IValueMeta vm = new ValueMetaString("notes");
+ vm.setLength(255);
+ vm.setOriginalColumnTypeName("varchar");
+ rowMeta.addValueMeta(vm);
+
+ Map<String, String> defs =
+ DatabaseTableInfoTab.loadColumnDefinitions(null, "public", "orders",
rowMeta);
+ assertEquals("varchar(255)", defs.get("notes"));
+ }
+
+ @Test
+ void escapePatternEscapesSpecialCharacters() {
+ assertEquals("order\\_items",
DatabaseTableInfoTab.escapePattern("order_items", "\\"));
+ assertEquals("sales\\%total",
DatabaseTableInfoTab.escapePattern("sales%total", "\\"));
+ assertEquals("path\\\\test",
DatabaseTableInfoTab.escapePattern("path\\test", "\\"));
+ assertEquals("plain_text",
DatabaseTableInfoTab.escapePattern("plain_text", null));
+ assertEquals("plain_text",
DatabaseTableInfoTab.escapePattern("plain_text", ""));
+ }
+
+ @Test
+ void loadColumnDefinitionsUsesCatalogWhenSchemasNotSupported() throws
Exception {
+ Database db = mock(Database.class);
+ DatabaseMetaData metaData = mock(DatabaseMetaData.class);
+ ResultSet rs = mock(ResultSet.class);
+ ResultSetMetaData rsmd = mock(ResultSetMetaData.class);
+
+ when(db.getDatabaseMetaData()).thenReturn(metaData);
+ when(metaData.supportsCatalogsInTableDefinitions()).thenReturn(true);
+ when(metaData.supportsSchemasInTableDefinitions()).thenReturn(false);
+ when(metaData.getSearchStringEscape()).thenReturn("\\");
+ when(metaData.getColumns(eq("otherdb"), eq(null), eq("orders"),
any())).thenReturn(rs);
+ when(rs.getMetaData()).thenReturn(rsmd);
+ when(rsmd.getColumnCount()).thenReturn(10);
+ when(rs.next()).thenReturn(true, false);
+ when(rs.getString("COLUMN_NAME")).thenReturn("id");
+ when(rs.getInt("DATA_TYPE")).thenReturn(java.sql.Types.INTEGER);
+ when(rs.getString("TYPE_NAME")).thenReturn("int");
+
+ Map<String, String> defs =
+ DatabaseTableInfoTab.loadColumnDefinitions(db, "otherdb", "orders",
null);
+ assertEquals("int", defs.get("id"));
+ }
+
+ @Test
+ void loadColumnDefinitionsEscapesWildcardCharactersInTableNameAndSchema()
throws Exception {
+ Database db = mock(Database.class);
+ DatabaseMetaData metaData = mock(DatabaseMetaData.class);
+ ResultSet rs = mock(ResultSet.class);
+ ResultSetMetaData rsmd = mock(ResultSetMetaData.class);
+
+ when(db.getDatabaseMetaData()).thenReturn(metaData);
+ when(metaData.supportsCatalogsInTableDefinitions()).thenReturn(false);
+ when(metaData.supportsSchemasInTableDefinitions()).thenReturn(true);
+ when(metaData.getSearchStringEscape()).thenReturn("\\");
+ when(metaData.getColumns(any(), eq("sales\\_schema"),
eq("order\\_items\\%2024"), any()))
+ .thenReturn(rs);
+ when(rs.getMetaData()).thenReturn(rsmd);
+ when(rsmd.getColumnCount()).thenReturn(10);
+ when(rs.next()).thenReturn(true, false);
+ when(rs.getString("TABLE_NAME")).thenReturn("order_items%2024");
+ when(rs.getString("TABLE_SCHEM")).thenReturn("sales_schema");
+ when(rs.getString("COLUMN_NAME")).thenReturn("item_id");
+ when(rs.getInt("DATA_TYPE")).thenReturn(java.sql.Types.INTEGER);
+ when(rs.getString("TYPE_NAME")).thenReturn("int");
+
+ Map<String, String> defs =
+ DatabaseTableInfoTab.loadColumnDefinitions(db, "sales_schema",
"order_items%2024", null);
+ assertEquals("int", defs.get("item_id"));
+ }
+
+ @Test
+ void loadColumnDefinitionsFiltersMismatchedTablesAndSchemas() throws
Exception {
+ Database db = mock(Database.class);
+ DatabaseMetaData metaData = mock(DatabaseMetaData.class);
+ ResultSet rs = mock(ResultSet.class);
+ ResultSetMetaData rsmd = mock(ResultSetMetaData.class);
+
+ when(db.getDatabaseMetaData()).thenReturn(metaData);
+ when(metaData.supportsCatalogsInTableDefinitions()).thenReturn(false);
+ when(metaData.supportsSchemasInTableDefinitions()).thenReturn(true);
+ when(metaData.getSearchStringEscape()).thenReturn("\\");
+ when(metaData.getColumns(any(), eq("sales"), eq("orders"),
any())).thenReturn(rs);
+ when(rs.getMetaData()).thenReturn(rsmd);
+ when(rsmd.getColumnCount()).thenReturn(10);
+ AtomicInteger row = new AtomicInteger(0);
+ when(rs.next()).thenAnswer(inv -> row.incrementAndGet() <= 3);
+ when(rs.getString("TABLE_NAME"))
+ .thenAnswer(
+ inv -> {
+ switch (row.get()) {
+ case 1:
+ return "orders_extra";
+ case 2:
+ case 3:
+ return "orders";
+ default:
+ return null;
+ }
+ });
+ when(rs.getString("TABLE_SCHEM"))
+ .thenAnswer(
+ inv -> {
+ switch (row.get()) {
+ case 1:
+ case 3:
+ return "sales";
+ case 2:
+ return "other_schema";
+ default:
+ return null;
+ }
+ });
+ when(rs.getString("COLUMN_NAME"))
+ .thenAnswer(
+ inv -> {
+ switch (row.get()) {
+ case 1:
+ return "extra_col";
+ case 2:
+ return "wrong_col";
+ case 3:
+ return "valid_col";
+ default:
+ return null;
+ }
+ });
+ when(rs.getInt("DATA_TYPE")).thenReturn(java.sql.Types.VARCHAR);
+ when(rs.getString("TYPE_NAME")).thenReturn("varchar");
+ when(rs.getInt("COLUMN_SIZE")).thenReturn(50);
+
+ Map<String, String> defs =
+ DatabaseTableInfoTab.loadColumnDefinitions(db, "sales", "orders",
null);
+ assertEquals(1, defs.size());
+ assertEquals("varchar(50)", defs.get("valid_col"));
+ assertFalse(defs.containsKey("extra_col"));
+ assertFalse(defs.containsKey("wrong_col"));
+ }
}
