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 7ab2b12669 issue #7251 : fine-tuning of DDL generation (#7287)
7ab2b12669 is described below
commit 7ab2b12669d90ce7fa2ec2325b421b65f661742a
Author: Matt Casters <[email protected]>
AuthorDate: Mon Jun 15 12:46:14 2026 +0200
issue #7251 : fine-tuning of DDL generation (#7287)
* issue #7251 : fine-tuning of DDL generation
* issue #7251 : javadoc of compatibility variable HOP_DB_DDL_COMPATIBLE
---
core/src/main/java/org/apache/hop/core/Const.java | 14 +
.../org/apache/hop/core/database/Database.java | 315 +++++++++++++++++++--
.../modules/ROOT/pages/database/databases.adoc | 46 ++-
3 files changed, 353 insertions(+), 22 deletions(-)
diff --git a/core/src/main/java/org/apache/hop/core/Const.java
b/core/src/main/java/org/apache/hop/core/Const.java
index 2769da1d21..27d72a05b5 100644
--- a/core/src/main/java/org/apache/hop/core/Const.java
+++ b/core/src/main/java/org/apache/hop/core/Const.java
@@ -1058,6 +1058,20 @@ public class Const {
"Default JDBC statement query timeout in seconds for database query
preview (0 = unset).")
public static final String HOP_QUERY_PREVIEW_TIMEOUT =
"HOP_QUERY_PREVIEW_TIMEOUT";
+ /**
+ * Apache Hop 2.18 and before (even before Hop) used an optimistic system
where each value type is
+ * given a chance to interpret the database field data type. This was done
to support special data
+ * types like GIS, and so on. If you set this variable to true the old
system is used. If not Hop
+ * tries first to do an explicit mapping of the most comm data types before
falling back to the
+ * old system.
+ */
+ @Variable(
+ scope = VariableScope.SYSTEM,
+ value = "false",
+ description =
+ "Make the data type detection in RDBMS connections compatible with
2.18 and before.")
+ public static final String HOP_DB_DDL_COMPATIBLE = "HOP_DB_DDL_COMPATIBLE";
+
/**
* rounds double f to any number of places after decimal point Does
arithmetic using BigDecimal
* class to avoid integer overflow while rounding
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 4a190dcac3..73001b73fc 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
@@ -35,6 +35,7 @@ import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
+import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
@@ -91,6 +92,7 @@ import org.apache.hop.core.row.value.ValueMetaNone;
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.apache.hop.core.util.EnvUtil;
import org.apache.hop.core.util.Utils;
import org.apache.hop.core.variables.IVariables;
import org.apache.hop.core.variables.Variables;
@@ -2398,38 +2400,43 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
}
}
+ private static boolean ddlCompatible =
+ Const.toBoolean(EnvUtil.getSystemProperty(Const.HOP_DB_DDL_COMPATIBLE,
"false"));
+
private IValueMeta getValueFromSqlType(
- ResultSetMetaData rm, int i, boolean ignoreLength, boolean
lazyConversion)
+ ResultSetMetaData rm, int columnIndex, boolean ignoreLength, boolean
lazyConversion)
throws HopDatabaseException, SQLException {
- // TODO If we do lazy conversion, we need to find out about the encoding
- //
-
- // Extract the name from the result set meta data...
+ // Extract the name from the result set metadata...
//
String name;
if (databaseMeta.isMySqlVariant()) {
- name =
databaseMeta.getIDatabase().getLegacyColumnName(getDatabaseMetaData(), rm, i);
+ name =
+
databaseMeta.getIDatabase().getLegacyColumnName(getDatabaseMetaData(), rm,
columnIndex);
} else {
- name = rm.getColumnName(i);
+ name = rm.getColumnName(columnIndex);
}
// Check the name, sometimes it's empty.
//
if (Utils.isEmpty(name) || Const.onlySpaces(name)) {
- name = "Field" + (i + 1);
+ name = "Field" + (columnIndex + 1);
}
- // Ask all the value meta types if they want to handle the SQL type.
- // The first to reply something gets the workflow...
- //
- IValueMeta valueMeta = null;
- for (IValueMeta valueMetaClass : valueMetaPluginClasses) {
- IValueMeta v =
- valueMetaClass.getValueFromSqlType(
- this, databaseMeta, name, rm, i, ignoreLength, lazyConversion);
- if (v != null) {
- valueMeta = v;
- break;
+ IValueMeta valueMeta;
+ if (ddlCompatible) {
+ // Classic approach is to ask all value metadata data types for advice
+ // on which data type the database column maps to best.
+ //
+ valueMeta =
+ getDataTypeFromAllValueMetaLegacy(rm, columnIndex, ignoreLength,
lazyConversion, name);
+ } else {
+ // Map obvious JDBC types to Hop data types first, then fall back to
value meta plugins
+ // for specialized types (UUID, INET, JSON, and so on).
+ //
+ valueMeta = getDataTypeFromKnownSqlType(rm, columnIndex, ignoreLength,
lazyConversion, name);
+ if (valueMeta == null) {
+ valueMeta =
+ getDataTypeFromAllValueMetaLegacy(rm, columnIndex, ignoreLength,
lazyConversion, name);
}
}
@@ -2441,10 +2448,278 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
"Unable to handle database column '"
+ name
+ "', on column index "
- + i
+ + columnIndex
+ " : not a handled data type");
}
+ /**
+ * Map well-known JDBC {@link Types} values to Hop value metadata using
hard-coded rules. Returns
+ * {@code null} when the SQL type is not one of the standard mappings so
that specialized value
+ * meta plugins can handle it.
+ */
+ private IValueMeta getDataTypeFromKnownSqlType(
+ ResultSetMetaData rm,
+ int columnIndex,
+ boolean ignoreLength,
+ boolean lazyConversion,
+ String name)
+ throws HopDatabaseException, SQLException {
+ int sqlType = rm.getColumnType(columnIndex);
+ int valtype = IValueMeta.TYPE_NONE;
+ int length = -1;
+ int precision = -1;
+ boolean isClob = false;
+ boolean signed = false;
+ try {
+ signed = rm.isSigned(columnIndex);
+ } catch (Exception ignored) {
+ // This JDBC driver doesn't support the isSigned method.
+ }
+
+ switch (sqlType) {
+ case Types.CHAR,
+ Types.VARCHAR,
+ Types.NVARCHAR,
+ Types.LONGVARCHAR,
+ Types.NCHAR,
+ Types.LONGNVARCHAR:
+ valtype = IValueMeta.TYPE_STRING;
+ if (!ignoreLength) {
+ length = rm.getColumnDisplaySize(columnIndex);
+ }
+ break;
+
+ case Types.CLOB, Types.NCLOB:
+ valtype = IValueMeta.TYPE_STRING;
+ length = DatabaseMeta.CLOB_LENGTH;
+ isClob = true;
+ break;
+
+ case Types.BIGINT:
+ if (signed) {
+ valtype = IValueMeta.TYPE_INTEGER;
+ precision = 0;
+ length = 15;
+ } else {
+ valtype = IValueMeta.TYPE_BIGNUMBER;
+ precision = 0;
+ length = 16;
+ }
+ break;
+
+ case Types.INTEGER:
+ valtype = IValueMeta.TYPE_INTEGER;
+ precision = 0;
+ length = 9;
+ break;
+
+ case Types.SMALLINT:
+ valtype = IValueMeta.TYPE_INTEGER;
+ precision = 0;
+ length = 4;
+ break;
+
+ case Types.TINYINT:
+ valtype = IValueMeta.TYPE_INTEGER;
+ precision = 0;
+ length = 2;
+ break;
+
+ case Types.DECIMAL, Types.DOUBLE, Types.FLOAT, Types.REAL, Types.NUMERIC:
+ valtype = IValueMeta.TYPE_NUMBER;
+ length = rm.getPrecision(columnIndex);
+ precision = rm.getScale(columnIndex);
+ // Precision in the database means the number of significant digits.
+ // In Hop, it means the digits before the decimal.
+ //
+ if (length > 0 && length > precision) {
+ length -= precision;
+ }
+
+ if (length >= 126) {
+ length = -1;
+ }
+ if (precision >= 126) {
+ precision = -1;
+ }
+
+ if (sqlType == Types.DOUBLE || sqlType == Types.FLOAT || sqlType ==
Types.REAL) {
+ if (precision == 0) {
+ precision = -1;
+ }
+
+ if (databaseMeta.getIDatabase().isPostgresVariant()
+ && sqlType == Types.DOUBLE
+ && precision >= 16
+ && length >= 16) {
+ precision = -1;
+ length = -1;
+ }
+
+ if (databaseMeta.getIDatabase().isMySqlVariant() && precision >=
length) {
+ precision = -1;
+ length = -1;
+ }
+
+ if (length > 15 || precision > 15) {
+ valtype = IValueMeta.TYPE_BIGNUMBER;
+ }
+ } else {
+ if (precision == 0) {
+ if (length <= 18 && length > 0) {
+ valtype = IValueMeta.TYPE_INTEGER;
+ } else if (length > 18) {
+ valtype = IValueMeta.TYPE_BIGNUMBER;
+ }
+ } else if (length > 15 || precision > 15) {
+ valtype = IValueMeta.TYPE_BIGNUMBER;
+ }
+ }
+
+ if (databaseMeta.getIDatabase().isPostgresVariant()
+ && sqlType == Types.NUMERIC
+ && length == 0
+ && precision == 0) {
+ valtype = IValueMeta.TYPE_BIGNUMBER;
+ length = -1;
+ precision = -1;
+ }
+
+ if (databaseMeta.getIDatabase().isOracleVariant()) {
+ if (precision == 0 && length == 38) {
+ valtype =
+ databaseMeta.getIDatabase().isStrictBigNumberInterpretation()
+ ? IValueMeta.TYPE_BIGNUMBER
+ : IValueMeta.TYPE_INTEGER;
+ }
+ if (precision <= 0 && length <= 0) {
+ valtype = IValueMeta.TYPE_BIGNUMBER;
+ length = -1;
+ precision = -1;
+ }
+ }
+ break;
+
+ case Types.TIMESTAMP:
+ if (databaseMeta.supportsTimestampDataType()) {
+ valtype = IValueMeta.TYPE_TIMESTAMP;
+ length = rm.getScale(columnIndex);
+ } else {
+ valtype = IValueMeta.TYPE_DATE;
+ }
+ break;
+
+ case Types.DATE, Types.TIME:
+ valtype = IValueMeta.TYPE_DATE;
+ if (databaseMeta.getIDatabase().isMySqlVariant()) {
+ String property =
+
databaseMeta.getConnectionProperties(this).getProperty("yearIsDateType");
+ if (property != null
+ && property.equalsIgnoreCase("false")
+ && rm.getColumnTypeName(columnIndex).equalsIgnoreCase("YEAR")) {
+ valtype = IValueMeta.TYPE_INTEGER;
+ precision = 0;
+ length = 4;
+ }
+ }
+ if (databaseMeta.getIDatabase().isTeradataVariant()) {
+ precision = 1;
+ }
+ break;
+
+ case Types.BOOLEAN, Types.BIT:
+ valtype = IValueMeta.TYPE_BOOLEAN;
+ break;
+
+ case Types.BINARY, Types.BLOB, Types.VARBINARY, Types.LONGVARBINARY:
+ valtype = IValueMeta.TYPE_BINARY;
+
+ if (databaseMeta.isDisplaySizeTwiceThePrecision()
+ && (2 * rm.getPrecision(columnIndex)) ==
rm.getColumnDisplaySize(columnIndex)) {
+ length = rm.getPrecision(columnIndex);
+ } else if (databaseMeta.getIDatabase().isOracleVariant()
+ && (sqlType == Types.VARBINARY || sqlType == Types.LONGVARBINARY))
{
+ valtype = IValueMeta.TYPE_STRING;
+ length = rm.getColumnDisplaySize(columnIndex);
+ } else if (databaseMeta.isMySqlVariant()
+ && (sqlType == Types.VARBINARY || sqlType == Types.LONGVARBINARY))
{
+ length = -1;
+ } else if (databaseMeta.getIDatabase().isSqliteVariant()) {
+ valtype = IValueMeta.TYPE_STRING;
+ } else {
+ length = -1;
+ }
+ precision = -1;
+ break;
+
+ default:
+ return null;
+ }
+
+ try {
+ IValueMeta valueMeta = ValueMetaFactory.createValueMeta(name, valtype);
+ valueMeta.setLength(length);
+ valueMeta.setPrecision(precision);
+ valueMeta.setLargeTextField(isClob);
+
+ setOriginalColumnMetadata(valueMeta, rm, columnIndex, ignoreLength);
+
+ if (lazyConversion && valtype == IValueMeta.TYPE_STRING) {
+ valueMeta.setStorageType(IValueMeta.STORAGE_TYPE_BINARY_STRING);
+ IValueMeta storageMetaData =
+ ValueMetaFactory.cloneValueMeta(valueMeta, IValueMeta.TYPE_STRING);
+ storageMetaData.setStorageType(IValueMeta.STORAGE_TYPE_NORMAL);
+ valueMeta.setStorageMetadata(storageMetaData);
+ }
+
+ IValueMeta customized =
+ databaseMeta.getIDatabase().customizeValueFromSqlType(valueMeta, rm,
columnIndex);
+ return customized == null ? valueMeta : customized;
+ } catch (Exception e) {
+ throw new HopDatabaseException(
+ "Error determining value metadata from SQL resultset metadata", e);
+ }
+ }
+
+ private void setOriginalColumnMetadata(
+ IValueMeta valueMeta, ResultSetMetaData rm, int columnIndex, boolean
ignoreLength)
+ throws SQLException {
+ valueMeta.setComments(rm.getColumnLabel(columnIndex));
+ valueMeta.setOriginalColumnType(rm.getColumnType(columnIndex));
+ valueMeta.setOriginalColumnTypeName(rm.getColumnTypeName(columnIndex));
+
+ int originalPrecision = -1;
+ if (!ignoreLength) {
+ originalPrecision = rm.getPrecision(columnIndex);
+ }
+ valueMeta.setOriginalPrecision(originalPrecision);
+ valueMeta.setOriginalScale(rm.getScale(columnIndex));
+
+ boolean originalSigned = false;
+ try {
+ originalSigned = rm.isSigned(columnIndex);
+ } catch (Exception ignored) {
+ // This JDBC driver doesn't support the isSigned method.
+ }
+ valueMeta.setOriginalSigned(originalSigned);
+ }
+
+ private IValueMeta getDataTypeFromAllValueMetaLegacy(
+ ResultSetMetaData rm, int i, boolean ignoreLength, boolean
lazyConversion, String name)
+ throws HopDatabaseException {
+ IValueMeta valueMeta = null;
+ for (IValueMeta valueMetaClass : valueMetaPluginClasses) {
+ IValueMeta v =
+ valueMetaClass.getValueFromSqlType(
+ this, databaseMeta, name, rm, i, ignoreLength, lazyConversion);
+ if (v != null) {
+ valueMeta = v;
+ break;
+ }
+ }
+ return valueMeta;
+ }
+
public boolean absolute(ResultSet rs, int position) throws
HopDatabaseException {
try {
return rs.absolute(position);
diff --git a/docs/hop-user-manual/modules/ROOT/pages/database/databases.adoc
b/docs/hop-user-manual/modules/ROOT/pages/database/databases.adoc
index e3d265e948..be9a58a4c4 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/database/databases.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/database/databases.adoc
@@ -74,11 +74,12 @@ The options table contains a list of key/value pairs that
can be added to your J
For example, to add additional options to a MS SQL database, you can add
options to achieve a JDBC URL like the one below. Apache Hop will take care of
these properties in the background, there usually is no need to manually modify
the JDBC URL (but you _can_, and in a `Generic` connection, you _have to_).
-```
+[source]
+----
jdbc:sqlserver://localhost:1433;" +
"databaseName=AdventureWorks;integratedSecurity=true;" +
"encrypt=true;trustServerCertificate=true
-```
+----
[%noheader, width="50%", cols="4,1"]
|===
@@ -87,5 +88,46 @@ jdbc:sqlserver://localhost:1433;" +
|trustServerCertificate|true
|===
+== Hard-coded JDBC to Hop data type mappings
+
+Below are the mappings applied by Hop when converting from database column
types to Hop data types when `HOP_DB_DDL_COMPATIBLE` is `false` (the default).
+
+[cols="2,2", options="header"]
+|===
+| JDBC type | Hop type
+
+| `CHAR`, `VARCHAR`, `NVARCHAR`, `LONGVARCHAR`, `NCHAR`, `LONGNVARCHAR`
+| String
+
+| `CLOB`, `NCLOB`
+| String (large text)
+
+| `INTEGER`, `SMALLINT`, `TINYINT`
+| Integer
+
+| `BIGINT` (signed)
+| Integer
+
+| `BIGINT` (unsigned)
+| BigNumber
+
+| `FLOAT`, `DOUBLE`, `REAL`, `DECIMAL`, `NUMERIC`
+| Number (with length/precision rules; BigNumber when needed)
+
+| `DATE`, `TIME`
+| Date
+
+| `TIMESTAMP`
+| Timestamp (or Date if the database does not support Timestamp)
+
+| `BOOLEAN`, `BIT`
+| Boolean
+
+| `BINARY`, `BLOB`, `VARBINARY`, `LONGVARBINARY`
+| Binary (with database-specific overrides)
+|===
+
+Unmapped types (for example `OTHER` for UUID or INET) return no match from the
hard-coded path and fall through to legacy method of figuring out the data
type, which was asking all the different value metadata plugin types for a
guess.
+
// tag::website-links[]
// end::website-links[]
