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 d9cf84b8ef Add support for sequences in H2 #8526 (#8542)
d9cf84b8ef is described below
commit d9cf84b8eff13264222697fb6514f6e4bce95cc2
Author: Nicolas Adment <[email protected]>
AuthorDate: Thu Sep 24 13:51:22 2026 +0200
Add support for sequences in H2 #8526 (#8542)
---
.../apache/hop/databases/h2/H2DatabaseMeta.java | 86 ++++++++++++++++++-
.../hop/databases/h2/H2BaseDatabaseMetaTest.java | 47 -----------
.../hop/databases/h2/H2DatabaseMetaTest.java | 98 +++++++++++++++++++++-
.../h2/H2SequenceAndReleaseSavePointTest.java | 75 -----------------
4 files changed, 178 insertions(+), 128 deletions(-)
diff --git
a/plugins/databases/h2/src/main/java/org/apache/hop/databases/h2/H2DatabaseMeta.java
b/plugins/databases/h2/src/main/java/org/apache/hop/databases/h2/H2DatabaseMeta.java
index ac77387b87..e1c44a58a9 100644
---
a/plugins/databases/h2/src/main/java/org/apache/hop/databases/h2/H2DatabaseMeta.java
+++
b/plugins/databases/h2/src/main/java/org/apache/hop/databases/h2/H2DatabaseMeta.java
@@ -17,7 +17,9 @@
package org.apache.hop.databases.h2;
+import java.util.ArrayList;
import java.util.List;
+import java.util.Locale;
import org.apache.hop.core.Const;
import org.apache.hop.core.database.BaseDatabaseMeta;
import org.apache.hop.core.database.DatabaseMeta;
@@ -193,12 +195,12 @@ public class H2DatabaseMeta extends BaseDatabaseMeta
implements IDatabase {
IValueMeta v, String tk, String pk, boolean useAutoinc, boolean
addFieldName, boolean addCr) {
String retval = "";
- String fieldname = v.getName();
+ String fieldName = v.getName();
int length = v.getLength();
int precision = v.getPrecision();
if (addFieldName) {
- retval += fieldname + " ";
+ retval += fieldName + " ";
}
int type = v.getType();
@@ -214,9 +216,9 @@ public class H2DatabaseMeta extends BaseDatabaseMeta
implements IDatabase {
}
break;
case IValueMeta.TYPE_NUMBER, IValueMeta.TYPE_INTEGER,
IValueMeta.TYPE_BIGNUMBER:
- if (fieldname.equalsIgnoreCase(tk)
+ if (fieldName.equalsIgnoreCase(tk)
|| // Technical key
- fieldname.equalsIgnoreCase(pk) // Primary key
+ fieldName.equalsIgnoreCase(pk) // Primary key
) {
retval += "IDENTITY";
} else {
@@ -322,4 +324,80 @@ public class H2DatabaseMeta extends BaseDatabaseMeta
implements IDatabase {
public boolean isSupportsPreparedStatementMetadataRetrieval() {
return false;
}
+
+ @Override
+ public boolean isSupportsSequences() {
+ return true;
+ }
+
+ /**
+ * H2 takes the single word NOMAXVALUE that {@link BaseDatabaseMeta}
supplies, and rejects the
+ * MAXVALUE -1 that Hop would write for an unbounded sequence otherwise.
+ */
+ @Override
+ public boolean isSupportsSequenceNoMaxValueOption() {
+ return true;
+ }
+
+ @Override
+ public String getSqlListOfSequences() {
+ return "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES"
+ + " ORDER BY SEQUENCE_SCHEMA, SEQUENCE_NAME";
+ }
+
+ /** The standard form takes an identifier, so a quoted or schema qualified
name works as it is. */
+ @Override
+ public String getSqlNextSequenceValue(String sequenceName) {
+ return "SELECT NEXT VALUE FOR " + sequenceName;
+ }
+
+ @Override
+ public String getSqlCurrentSequenceValue(String sequenceName) {
+ return "SELECT CURRENT VALUE FOR " + sequenceName;
+ }
+
+ @Override
+ public String getSqlSequenceExists(String sequenceName) {
+ // The name arrives the way getQuotedSchemaTableCombination built it, so
it can carry a schema.
+ List<String> parts =
splitQualifiedName(sequenceName.toUpperCase(Locale.ROOT));
+ StringBuilder sql =
+ new StringBuilder("SELECT SEQUENCE_NAME FROM
INFORMATION_SCHEMA.SEQUENCES WHERE ")
+ // H2 folds an unquoted identifier to upper case, so a lookup
cannot be case-sensitive.
+ .append("UPPER(SEQUENCE_NAME) = ")
+ .append(quoteSqlString(parts.getLast()));
+ if (parts.size() > 1) {
+ sql.append(" AND UPPER(SEQUENCE_SCHEMA) = ")
+ .append(quoteSqlString(parts.get(parts.size() - 2)));
+ }
+ return sql.toString();
+ }
+
+ /**
+ * Splits a qualified name into its parts, on the dots outside a quoted
identifier, and gives
+ * every part back the way the catalog holds it: unquoted.
+ */
+ private static List<String> splitQualifiedName(String name) {
+ List<String> parts = new ArrayList<>();
+ StringBuilder part = new StringBuilder();
+ boolean quoted = false;
+ for (int i = 0; i < name.length(); i++) {
+ char c = name.charAt(i);
+ if (c == '"') {
+ // Two quotes within a quoted identifier stand for one quote in the
name itself.
+ if (quoted && i + 1 < name.length() && name.charAt(i + 1) == '"') {
+ part.append('"');
+ i++;
+ } else {
+ quoted = !quoted;
+ }
+ } else if (c == '.' && !quoted) {
+ parts.add(part.toString().trim());
+ part.setLength(0);
+ } else {
+ part.append(c);
+ }
+ }
+ parts.add(part.toString().trim());
+ return parts;
+ }
}
diff --git
a/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2BaseDatabaseMetaTest.java
b/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2BaseDatabaseMetaTest.java
deleted file mode 100644
index 0f2c860f1a..0000000000
---
a/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2BaseDatabaseMetaTest.java
+++ /dev/null
@@ -1,47 +0,0 @@
-/*
- * 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.databases.h2;
-
-import static org.junit.jupiter.api.Assertions.assertTrue;
-
-import java.util.List;
-import org.apache.hop.core.HopClientEnvironment;
-import org.apache.hop.core.database.BaseDatabaseMeta;
-import org.apache.hop.core.database.SqlScriptStatement;
-import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
-import org.junit.jupiter.api.BeforeEach;
-import org.junit.jupiter.api.Test;
-import org.junit.jupiter.api.extension.RegisterExtension;
-
-class H2BaseDatabaseMetaTest {
- @RegisterExtension
- static RestoreHopEngineEnvironmentExtension env = new
RestoreHopEngineEnvironmentExtension();
-
- BaseDatabaseMeta nativeMeta;
-
- @BeforeEach
- void setupOnce() throws Exception {
- HopClientEnvironment.init();
- }
-
- @Test
- void testShowIsTreatedAsAResultsQuery() {
- List<SqlScriptStatement> sqlScriptStatements =
- new H2DatabaseMeta().getSqlScriptStatements("show annotations from
service");
- assertTrue(sqlScriptStatements.get(0).isQuery());
- }
-}
diff --git
a/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2DatabaseMetaTest.java
b/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2DatabaseMetaTest.java
index 9be0b18b86..255bb37049 100644
---
a/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2DatabaseMetaTest.java
+++
b/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2DatabaseMetaTest.java
@@ -21,7 +21,13 @@ 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.util.Arrays;
+import java.util.List;
+import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.SqlScriptStatement;
+import org.apache.hop.core.logging.LoggingObjectType;
+import org.apache.hop.core.logging.SimpleLoggingObject;
import org.apache.hop.core.row.value.ValueMetaBigNumber;
import org.apache.hop.core.row.value.ValueMetaBinary;
import org.apache.hop.core.row.value.ValueMetaBoolean;
@@ -31,10 +37,18 @@ import
org.apache.hop.core.row.value.ValueMetaInternetAddress;
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.variables.Variables;
+import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.RegisterExtension;
class H2DatabaseMetaTest {
+ @RegisterExtension
+ static RestoreHopEngineEnvironmentExtension env = new
RestoreHopEngineEnvironmentExtension();
+
+ final String sequenceName = "sequence_name";
+
H2DatabaseMeta nativeMeta;
@BeforeEach
@@ -99,6 +113,8 @@ class H2DatabaseMetaTest {
assertTrue(nativeMeta.isFetchSizeSupported());
assertEquals("FOO.BAR", nativeMeta.getSchemaTableCombination("FOO",
"BAR"));
+ assertTrue(nativeMeta.isReleaseSavepoint());
+ assertTrue(nativeMeta.isSupportsSequences());
assertFalse(nativeMeta.isSupportsBitmapIndex());
assertTrue(nativeMeta.isSupportsAutoInc());
assertTrue(nativeMeta.isSupportsGetBlob());
@@ -216,9 +232,8 @@ class H2DatabaseMetaTest {
nativeMeta.getAddColumnStatement(
"FOO", new ValueMetaNumber("BAR", 26, 8), "", true, "BAR", false));
- String lineSep = System.getProperty("line.separator");
assertEquals(
- "ALTER TABLE FOO DROP BAR" + lineSep,
+ "ALTER TABLE FOO DROP BAR" + System.lineSeparator(),
nativeMeta.getDropColumnStatement(
"FOO", new ValueMetaString("BAR", 15, 0), "", false, "", true));
@@ -275,4 +290,83 @@ class H2DatabaseMetaTest {
"insert into FOO(FOOKEY, FOOVERSION) values (0, 1)",
nativeMeta.getSqlInsertAutoIncUnknownDimensionRow("FOO", "FOOKEY",
"FOOVERSION"));
}
+
+ @Test
+ void testShowIsTreatedAsAResultsQuery() {
+ List<SqlScriptStatement> sqlScriptStatements =
+ new H2DatabaseMeta().getSqlScriptStatements("show annotations from
service");
+ assertTrue(sqlScriptStatements.getFirst().isQuery());
+ }
+
+ @Test
+ void testSupportsSequence() {
+ assertEquals(
+ "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE
UPPER(SEQUENCE_NAME) = 'SEQUENCE_NAME'",
+ nativeMeta.getSqlSequenceExists(sequenceName));
+ assertEquals(
+ "SELECT NEXT VALUE FOR " + sequenceName,
nativeMeta.getSqlNextSequenceValue(sequenceName));
+ assertEquals(
+ "SELECT CURRENT VALUE FOR " + sequenceName,
+ nativeMeta.getSqlCurrentSequenceValue(sequenceName));
+ }
+
+ /** An in-memory database of its own per test, which lives as long as the
connection. */
+ private Database database(String name) {
+ DatabaseMeta databaseMeta = new DatabaseMeta();
+ databaseMeta.setIDatabase(new H2DatabaseMeta());
+ databaseMeta.setName(name);
+ databaseMeta.setDBName("mem:" + name);
+ databaseMeta.setAccessType(DatabaseMeta.TYPE_ACCESS_NATIVE);
+ databaseMeta.setUsername("sa");
+ return new Database(
+ new SimpleLoggingObject(name, LoggingObjectType.GENERAL, null),
+ new Variables(),
+ databaseMeta);
+ }
+
+ @Test
+ void sequencesAreCreatedListedAndRead() throws Exception {
+ try (Database db = database("sequences")) {
+ db.connect();
+ db.execStatement(db.getCreateSequenceStatement(null, "SEQ_ONE", 1L, 1L,
999L, false));
+
+ assertTrue(db.checkSequenceExists("SEQ_ONE"), "the sequence just created
is found");
+ assertFalse(db.checkSequenceExists("SEQ_MISSING"), "one never created is
not invented");
+ assertTrue(
+ Arrays.asList(db.getSequences()).contains("SEQ_ONE"),
+ "the picker lists it: " + Arrays.toString(db.getSequences()));
+
+ assertEquals(Long.valueOf(1L), db.getNextSequenceValue("SEQ_ONE", "id"));
+ assertEquals(Long.valueOf(2L), db.getNextSequenceValue("SEQ_ONE", "id"));
+ }
+ }
+
+ /** A maximum of -1 stands for an unbounded sequence; H2 rejects the
MAXVALUE -1 it replaces. */
+ @Test
+ void sequenceWithoutAMaximumIsCreated() throws Exception {
+ try (Database db = database("unbounded")) {
+ db.connect();
+ String sql = db.getCreateSequenceStatement(null, "SEQ_UNBOUNDED", "1",
"1", "-1", false);
+ assertTrue(sql.contains("NOMAXVALUE"), "an unbounded sequence has no
maximum: " + sql);
+
+ db.execStatement(sql);
+ assertEquals(Long.valueOf(1L), db.getNextSequenceValue("SEQ_UNBOUNDED",
"id"));
+ }
+ }
+
+ /** A schema tells two sequences of the same name apart. */
+ @Test
+ void sequencesAreLookedUpWithinTheirSchema() throws Exception {
+ try (Database db = database("schemas")) {
+ db.connect();
+ db.execStatement("CREATE SCHEMA SIDE");
+ db.execStatement(db.getCreateSequenceStatement("SIDE", "SEQ_TWO", 5L,
1L, 999L, false));
+
+ assertTrue(db.checkSequenceExists("SIDE", "SEQ_TWO"), "found in the
schema holding it");
+ assertFalse(
+ db.checkSequenceExists("PUBLIC", "SEQ_TWO"), "and not in the one
that does not hold it");
+
+ assertEquals(Long.valueOf(5L), db.getNextSequenceValue("SIDE",
"SEQ_TWO", "id"));
+ }
+ }
}
diff --git
a/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2SequenceAndReleaseSavePointTest.java
b/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2SequenceAndReleaseSavePointTest.java
deleted file mode 100644
index 52955d6f2f..0000000000
---
a/plugins/databases/h2/src/test/java/org/apache/hop/databases/h2/H2SequenceAndReleaseSavePointTest.java
+++ /dev/null
@@ -1,75 +0,0 @@
-/*
- * 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.databases.h2;
-
-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 org.apache.hop.core.database.IDatabase;
-import org.apache.hop.core.util.Utils;
-import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
-import org.junit.jupiter.api.Test;
-import org.junit.jupiter.api.extension.RegisterExtension;
-
-class H2SequenceAndReleaseSavePointTest {
- @RegisterExtension
- static RestoreHopEngineEnvironmentExtension env = new
RestoreHopEngineEnvironmentExtension();
-
- final String sequenceName = "sequence_name";
-
- // Set these parameters for the test
- IDatabase db = new H2DatabaseMeta();
- Boolean sequenceSupport = false;
- Boolean savepointSupport = true;
-
- @Test
- void testSequenceSupport() {
- assertSupports(db, sequenceSupport);
- assertEquals("", db.getSqlNextSequenceValue(sequenceName));
- assertEquals("", db.getSqlCurrentSequenceValue(sequenceName));
- }
-
- @Test
- void testSavepointSuport() {
- if (savepointSupport) {
- assertTrue(db.isReleaseSavepoint());
- } else {
- assertFalse(db.isReleaseSavepoint());
- }
- }
-
- public static void assertSupports(IDatabase db, boolean expected) {
- String dbType = db.getClass().getSimpleName();
- if (expected) {
- assertTrue(db.isSupportsSequences(), dbType);
- assertFalse(Utils.isEmpty(db.getSqlListOfSequences()), dbType + ": List
of Sequences");
- assertFalse(Utils.isEmpty(db.getSqlSequenceExists("testSeq")), dbType +
": Sequence Exists");
- assertFalse(
- Utils.isEmpty(db.getSqlCurrentSequenceValue("testSeq")), dbType + ":
Current Value");
- assertFalse(Utils.isEmpty(db.getSqlNextSequenceValue("testSeq")), dbType
+ ": Next Value");
- } else {
- assertFalse(db.isSupportsSequences(), db.getClass().getSimpleName());
- assertTrue(Utils.isEmpty(db.getSqlListOfSequences()), dbType + ": List
of Sequences");
- assertTrue(Utils.isEmpty(db.getSqlSequenceExists("testSeq")), dbType +
": Sequence Exists");
- assertTrue(
- Utils.isEmpty(db.getSqlCurrentSequenceValue("testSeq")), dbType + ":
Current Value");
- assertTrue(Utils.isEmpty(db.getSqlNextSequenceValue("testSeq")), dbType
+ ": Next Value");
- }
- }
-}
