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 1d1105ef80 Fix #6472: map SQLite column types by affinity, not driver
JDBC type (#7564)
1d1105ef80 is described below
commit 1d1105ef80745f0bfcfa13399c451390fc8edbee
Author: Sergio Ramazzina <[email protected]>
AuthorDate: Sat Jul 18 21:08:36 2026 +0200
Fix #6472: map SQLite column types by affinity, not driver JDBC type (#7564)
---
.../hop/databases/sqlite/SqliteDatabaseMeta.java | 58 ++++++++++
.../databases/sqlite/SqliteDatabaseMetaTest.java | 127 +++++++++++++++++++++
2 files changed, 185 insertions(+)
diff --git
a/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
b/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
index 9f9cfc21c6..e935b8874b 100644
---
a/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
+++
b/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
@@ -17,14 +17,19 @@
package org.apache.hop.databases.sqlite;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.Locale;
import org.apache.hop.core.Const;
import org.apache.hop.core.database.BaseDatabaseMeta;
import org.apache.hop.core.database.DatabaseMeta;
import org.apache.hop.core.database.DatabaseMetaPlugin;
import org.apache.hop.core.database.DriverDownload;
import org.apache.hop.core.database.IDatabase;
+import org.apache.hop.core.exception.HopPluginException;
import org.apache.hop.core.gui.plugin.GuiPlugin;
import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.value.ValueMetaFactory;
/** Contains SQLite specific information through static final members */
@DatabaseMetaPlugin(
@@ -212,4 +217,57 @@ public class SqliteDatabaseMeta extends BaseDatabaseMeta
implements IDatabase {
public boolean isSqliteVariant() {
return true;
}
+
+ /**
+ * SQLite uses dynamic typing with name-based type affinity (see
+ * https://www.sqlite.org/datatype3.html section 3.1). The JDBC driver
reports columns whose
+ * declared type carries a size preceded by a space (e.g. {@code "TEXT
(50)"}) as {@link
+ * java.sql.Types#NUMERIC}, which makes the generic mapper turn them into
BigNumber/Integer. Since
+ * the declared size is meaningless in SQLite, re-derive the Hop type from
the affinity of the
+ * declared type name (which the driver still reports correctly), correcting
only the columns the
+ * generic mapper got wrong. See issue #6472.
+ */
+ @Override
+ public IValueMeta customizeValueFromSqlType(IValueMeta v, ResultSetMetaData
rm, int index)
+ throws SQLException {
+ if (v == null || rm == null) {
+ return super.customizeValueFromSqlType(v, rm, index);
+ }
+ String typeName = rm.getColumnTypeName(index);
+ if (typeName == null) {
+ return super.customizeValueFromSqlType(v, rm, index);
+ }
+ typeName = typeName.toUpperCase(Locale.ROOT);
+
+ int targetType;
+ if (typeName.contains("INT")) {
+ // INTEGER affinity: SQLite integers are 64-bit, which fit a Hop Integer
(Long).
+ targetType = IValueMeta.TYPE_INTEGER;
+ } else if (typeName.contains("CHAR")
+ || typeName.contains("CLOB")
+ || typeName.contains("TEXT")) {
+ // TEXT affinity.
+ targetType = IValueMeta.TYPE_STRING;
+ } else if (typeName.contains("REAL")
+ || typeName.contains("FLOA")
+ || typeName.contains("DOUB")) {
+ // REAL affinity.
+ targetType = IValueMeta.TYPE_NUMBER;
+ } else {
+ // NUMERIC and BLOB affinity: keep whatever the generic mapper produced.
+ return super.customizeValueFromSqlType(v, rm, index);
+ }
+
+ if (v.getType() == targetType) {
+ return super.customizeValueFromSqlType(v, rm, index);
+ }
+
+ try {
+ IValueMeta corrected = ValueMetaFactory.cloneValueMeta(v, targetType);
+ corrected.setPrecision(-1);
+ return corrected;
+ } catch (HopPluginException e) {
+ throw new SQLException(e);
+ }
+ }
}
diff --git
a/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteDatabaseMetaTest.java
b/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteDatabaseMetaTest.java
index 9413442462..243384757c 100644
---
a/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteDatabaseMetaTest.java
+++
b/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteDatabaseMetaTest.java
@@ -20,23 +20,54 @@ import static
org.junit.jupiter.api.Assertions.assertArrayEquals;
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 static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.HashMap;
+import java.util.Map;
import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.DatabasePluginType;
+import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.plugins.PluginRegistry;
+import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.row.value.ValueMetaBigNumber;
import org.apache.hop.core.row.value.ValueMetaBinary;
import org.apache.hop.core.row.value.ValueMetaBoolean;
import org.apache.hop.core.row.value.ValueMetaDate;
+import org.apache.hop.core.row.value.ValueMetaFactory;
+import org.apache.hop.core.row.value.ValueMetaInteger;
import org.apache.hop.core.row.value.ValueMetaInternetAddress;
import org.apache.hop.core.row.value.ValueMetaNumber;
+import org.apache.hop.core.row.value.ValueMetaPluginType;
import org.apache.hop.core.row.value.ValueMetaString;
import org.apache.hop.core.row.value.ValueMetaTimestamp;
+import org.apache.hop.core.variables.Variables;
+import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
+import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.RegisterExtension;
class SqliteDatabaseMetaTest {
+ @RegisterExtension
+ static RestoreHopEngineEnvironmentExtension env = new
RestoreHopEngineEnvironmentExtension();
+
private SqliteDatabaseMeta nativeMeta;
+ @BeforeAll
+ static void setUpBeforeClass() throws HopException {
+ PluginRegistry.addPluginType(ValueMetaPluginType.getInstance());
+ PluginRegistry.addPluginType(DatabasePluginType.getInstance());
+ PluginRegistry.init();
+ }
+
@BeforeEach
void setupBefore() {
nativeMeta = new SqliteDatabaseMeta();
@@ -146,4 +177,100 @@ class SqliteDatabaseMetaTest {
nativeMeta.getFieldDefinition(
new ValueMetaInternetAddress("FOO"), "", "", false, false, true));
}
+
+ /**
+ * The SQLite JDBC driver reports columns whose declared type carries a size
with a space before
+ * the parenthesis (e.g. "TEXT (50)") as {@link java.sql.Types#NUMERIC}, so
the generic mapper
+ * wrongly turns them into BigNumber/Integer. The Hop type must instead
follow SQLite's name-based
+ * type affinity. See issue #6472.
+ */
+ private ResultSetMetaData resultSetMetaWithTypeName(String typeName) throws
SQLException {
+ ResultSetMetaData rm = mock(ResultSetMetaData.class);
+ when(rm.getColumnTypeName(1)).thenReturn(typeName);
+ return rm;
+ }
+
+ private IValueMeta effective(IValueMeta original, ResultSetMetaData rm)
throws SQLException {
+ IValueMeta customized = nativeMeta.customizeValueFromSqlType(original, rm,
1);
+ return customized != null ? customized : original;
+ }
+
+ @Test
+ void testTextAffinityStaysStringWhenDriverReportsNumeric() throws
SQLException {
+ // "TEXT (50)" -> driver says NUMERIC -> generic mapper produced
BigNumber(50)
+ IValueMeta generic = new ValueMetaBigNumber("TEXT_50", 50, 0);
+ assertTrue(effective(generic,
resultSetMetaWithTypeName("TEXT")).isString());
+ }
+
+ @Test
+ void testIntegerAffinityStaysIntegerWhenDriverReportsNumeric() throws
SQLException {
+ // "INTEGER (20)" -> driver says NUMERIC -> generic mapper produced
BigNumber(20)
+ IValueMeta generic = new ValueMetaBigNumber("INTEGER20", 20, 0);
+ assertTrue(effective(generic,
resultSetMetaWithTypeName("INTEGER")).isInteger());
+ }
+
+ @Test
+ void testRealAffinityStaysNumberWhenDriverReportsNumeric() throws
SQLException {
+ // "REAL (10)" -> driver says NUMERIC -> generic mapper produced
Integer(10)
+ IValueMeta generic = new ValueMetaInteger("REAL10", 10, 0);
+ assertTrue(effective(generic,
resultSetMetaWithTypeName("REAL")).isNumber());
+ }
+
+ @Test
+ void testNumericAffinityKeepsGenericNumericMapping() throws SQLException {
+ // "NUMERIC (20)" is genuine NUMERIC affinity: keep the generic mapping
(BigNumber)
+ IValueMeta generic = new ValueMetaBigNumber("NUMERIC20", 20, 0);
+ assertTrue(effective(generic,
resultSetMetaWithTypeName("NUMERIC")).isBigNumber());
+ }
+
+ @Test
+ void testCorrectlyTypedStringIsNotAltered() throws SQLException {
+ // "TEXT" (no size) already maps to String: no correction needed
+ IValueMeta generic = new ValueMetaString("TEXT");
+ assertTrue(effective(generic,
resultSetMetaWithTypeName("TEXT")).isString());
+ }
+
+ /**
+ * End-to-end reproduction of issue #6472 against the real SQLite JDBC
driver, using the exact DDL
+ * from the report (sizes written with a space before the parenthesis).
Drives the full
+ * ValueMetaBase.getValueFromSqlType -> customizeValueFromSqlType chain.
+ */
+ @Test
+ void testGetValueFromSqlTypeFollowsSqliteAffinity() throws Exception {
+ Class.forName("org.sqlite.JDBC");
+ Map<String, Integer> types = new HashMap<>();
+ try (Connection c = DriverManager.getConnection("jdbc:sqlite::memory:");
+ Statement st = c.createStatement()) {
+ st.execute(
+ "CREATE TABLE str_test ("
+ + "\"TEXT\" TEXT, TEXT_50 TEXT (50), "
+ + "\"INTEGER\" INTEGER, INTEGER20 INTEGER (20), "
+ + "\"NUMERIC\" NUMERIC, NUMERIC20 NUMERIC (20), "
+ + "\"REAL\" REAL, REAL10 REAL (10), \"BLOB\" BLOB)");
+ try (ResultSet rs = st.executeQuery("SELECT * FROM str_test")) {
+ ResultSetMetaData rm = rs.getMetaData();
+ DatabaseMeta dbMeta = new DatabaseMeta();
+ dbMeta.setIDatabase(new SqliteDatabaseMeta());
+ IValueMeta valueMetaBase =
ValueMetaFactory.createValueMeta(IValueMeta.TYPE_NONE);
+ for (int i = 1; i <= rm.getColumnCount(); i++) {
+ IValueMeta v =
+ valueMetaBase.getValueFromSqlType(
+ new Variables(), dbMeta, rm.getColumnName(i), rm, i, false,
false);
+ types.put(rm.getColumnName(i), v.getType());
+ }
+ }
+ }
+
+ // Sized columns (the buggy cases) must follow SQLite name-based affinity.
+ assertEquals(IValueMeta.TYPE_STRING, types.get("TEXT_50"));
+ assertEquals(IValueMeta.TYPE_INTEGER, types.get("INTEGER20"));
+ assertEquals(IValueMeta.TYPE_NUMBER, types.get("REAL10"));
+ assertEquals(IValueMeta.TYPE_BIGNUMBER, types.get("NUMERIC20"));
+
+ // Unsized columns were already correct and must stay so.
+ assertEquals(IValueMeta.TYPE_STRING, types.get("TEXT"));
+ assertEquals(IValueMeta.TYPE_INTEGER, types.get("INTEGER"));
+ assertEquals(IValueMeta.TYPE_NUMBER, types.get("REAL"));
+ assertEquals(IValueMeta.TYPE_STRING, types.get("BLOB"));
+ }
}
