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 8527c9cadc Add support for sequences in DuckDb #8522 (#8523)
8527c9cadc is described below
commit 8527c9cadc6291843065bec44367829edc7ea071
Author: Nicolas Adment <[email protected]>
AuthorDate: Tue Sep 22 10:04:16 2026 +0200
Add support for sequences in DuckDb #8522 (#8523)
---
.../hop/databases/duckdb/DuckDBDatabaseMeta.java | 88 ++++++++++++++++++++++
.../databases/duckdb/DuckDBDatabaseMetaTest.java | 61 +++++++++++++--
2 files changed, 143 insertions(+), 6 deletions(-)
diff --git
a/plugins/databases/duckdb/src/main/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMeta.java
b/plugins/databases/duckdb/src/main/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMeta.java
index da64073901..7836315057 100644
---
a/plugins/databases/duckdb/src/main/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMeta.java
+++
b/plugins/databases/duckdb/src/main/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMeta.java
@@ -17,7 +17,9 @@
package org.apache.hop.databases.duckdb;
+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;
@@ -257,4 +259,90 @@ public class DuckDBDatabaseMeta extends BaseDatabaseMeta
implements IDatabase {
setSupportsBooleanDataType(true);
setSupportsTimestampDataType(true);
}
+
+ @Override
+ public boolean isSupportsSequences() {
+ return true;
+ }
+
+ @Override
+ public boolean isSupportsSequenceNoMaxValueOption() {
+ return true;
+ }
+
+ /** DuckDB's parser only knows the two word form; the default NOMAXVALUE is
a syntax error. */
+ @Override
+ public String getSequenceNoMaxValueOption() {
+ return "NO MAXVALUE";
+ }
+
+ /** Sequences live in the duckdb_sequences() catalog function;
information_schema has no view. */
+ @Override
+ public String getSqlListOfSequences() {
+ return "SELECT sequence_name FROM duckdb_sequences() ORDER BY schema_name,
sequence_name";
+ }
+
+ @Override
+ public String getSqlNextSequenceValue(String sequenceName) {
+ return "SELECT nextval('" + sequenceName + "')";
+ }
+
+ @Override
+ public String getSqlCurrentSequenceValue(String sequenceName) {
+ return "SELECT currval('" + sequenceName + "')";
+ }
+
+ @Override
+ public String getSqlSequenceExists(String sequenceName) {
+ // The name arrives the way getQuotedSchemaTableCombination built it, so
it can carry a schema
+ // and, since a DuckDB schema is listed as catalog.schema, a catalog
before that.
+ List<String> parts = splitQualifiedName(sequenceName);
+ StringBuilder sql =
+ new StringBuilder("SELECT sequence_name FROM duckdb_sequences() WHERE
")
+ // Identifiers are case-insensitive in DuckDB, quoted ones
included.
+ .append("lower(sequence_name) = ")
+ .append(quoteSqlString(lower(parts.getLast())));
+ if (parts.size() > 1) {
+ sql.append(" AND lower(schema_name) = ")
+ .append(quoteSqlString(lower(parts.get(parts.size() - 2))));
+ }
+ if (parts.size() > 2) {
+ sql.append(" AND lower(database_name) = ")
+ .append(quoteSqlString(lower(parts.get(parts.size() - 3))));
+ }
+ 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;
+ }
+
+ private static String lower(String identifier) {
+ return identifier.toLowerCase(Locale.ROOT);
+ }
}
diff --git
a/plugins/databases/duckdb/src/test/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMetaTest.java
b/plugins/databases/duckdb/src/test/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMetaTest.java
index 847d04d9fa..a01c32078e 100644
---
a/plugins/databases/duckdb/src/test/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMetaTest.java
+++
b/plugins/databases/duckdb/src/test/java/org/apache/hop/databases/duckdb/DuckDBDatabaseMetaTest.java
@@ -18,6 +18,7 @@
package org.apache.hop.databases.duckdb;
import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertTrue;
@@ -70,9 +71,8 @@ class DuckDBDatabaseMetaTest {
*/
@Test
void tablesAreListedWhicheverNameTheDriverGivesTheirType() throws Exception {
- Database db = database("tables");
- db.connect();
- try {
+ try (Database db = database("tables")) {
+ db.connect();
db.execStatement("CREATE TABLE ORDINARY(a INT)");
db.execStatement("CREATE TEMP TABLE TEMPORARY_ONE(a INT)");
db.execStatement("CREATE VIEW A_VIEW AS SELECT * FROM ORDINARY");
@@ -80,7 +80,7 @@ class DuckDBDatabaseMetaTest {
List<String> tables = Arrays.asList(db.getTablenames());
assertTrue(tables.contains("ORDINARY"), "an ordinary table is a table: "
+ tables);
assertTrue(tables.contains("TEMPORARY_ONE"), "a temporary table is a
table: " + tables);
- assertTrue(!tables.contains("A_VIEW"), "a view is not a table: " +
tables);
+ assertFalse(tables.contains("A_VIEW"), "a view is not a table: " +
tables);
assertTrue(
db.getTableMap().values().stream().anyMatch(names ->
names.contains("ORDINARY")),
@@ -88,8 +88,6 @@ class DuckDBDatabaseMetaTest {
List<String> views = Arrays.asList(db.getViews(false));
assertTrue(views.contains("A_VIEW"), "a view is still a view: " + views);
- } finally {
- db.disconnect();
}
}
@@ -118,4 +116,55 @@ class DuckDBDatabaseMetaTest {
schemas.stream().distinct().count(),
"no two schemas share a name once qualified: " + schemas);
}
+
+ /** DuckDB has CREATE SEQUENCE, nextval() and currval() */
+ @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 that was 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, spelled NO MAXVALUE on
DuckDB. */
+ @Test
+ void aSequenceWithoutAMaximumIsCreated() throws Exception {
+ try (Database db = database("unbounded")) {
+ db.connect();
+ String sql = db.getCreateSequenceStatement(null, "SEQ_UNBOUNDED", "1",
"1", "-1", false);
+ assertTrue(sql.contains("NO MAXVALUE"), "NOMAXVALUE is a syntax error on
DuckDB: " + sql);
+
+ db.execStatement(sql);
+ assertEquals(Long.valueOf(1L), db.getNextSequenceValue("SEQ_UNBOUNDED",
"id"));
+ }
+ }
+
+ /** A schema tells two sequences of the same name apart, catalog qualified
schemas included. */
+ @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("main", "SEQ_TWO"), "and not in the one that
does not hold it");
+ assertTrue(
+ db.checkSequenceExists("memory.SIDE", "SEQ_TWO"),
+ "the schema list qualifies schemas by their catalog, so a lookup has
to take that form");
+
+ assertEquals(Long.valueOf(5L), db.getNextSequenceValue("SIDE",
"SEQ_TWO", "id"));
+ }
+ }
}
