This is an automated email from the ASF dual-hosted git repository.
bamaer 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 89e022f33c Add sequence support to the MariaDB database type, fixes
#8525 (#9484)
89e022f33c is described below
commit 89e022f33cf3213ebc7c154bf57b6eba89b4fff5
Author: vasipallyn <[email protected]>
AuthorDate: Tue Nov 17 05:01:56 2026 -0500
Add sequence support to the MariaDB database type, fixes #8525 (#9484)
* Add sequence support to the MariaDB database type, fixes #8525
MariaDB has supported sequences natively since 10.3, but the MariaDB
database type inherited MySQL's lack of sequence support, so sequences
could not be listed, checked or used from Hop.
- Report sequence support.
- List sequences of the current database from
INFORMATION_SCHEMA.TABLES, where MariaDB stores them with
TABLE_TYPE = 'SEQUENCE'.
- Check existence in the current database, or in the given database
for a schema-qualified (backtick-quoted) name.
- Use NEXTVAL() for the next value and LASTVAL() for the current one.
* Use NOMAXVALUE for MariaDB sequences without a maximum, fixes #8525
The SQL button of Dimension lookup and Combination lookup generates
CREATE SEQUENCE with max value -1 ("no maximum"). MariaDB rejects
MAXVALUE -1 with "out of range value for options", but accepts
NOMAXVALUE, so report support for the no-max-value option.
---------
Co-authored-by: vasipallyn <[email protected]>
---
.../hop/databases/mariadb/MariaDBDatabaseMeta.java | 74 ++++++++++++++++++++++
.../MariaDBSequenceAndReleaseSavePointTest.java | 48 +++++++++++++-
2 files changed, 119 insertions(+), 3 deletions(-)
diff --git
a/plugins/databases/mariadb/src/main/java/org/apache/hop/databases/mariadb/MariaDBDatabaseMeta.java
b/plugins/databases/mariadb/src/main/java/org/apache/hop/databases/mariadb/MariaDBDatabaseMeta.java
index e7f4c87256..89936a0a9e 100644
---
a/plugins/databases/mariadb/src/main/java/org/apache/hop/databases/mariadb/MariaDBDatabaseMeta.java
+++
b/plugins/databases/mariadb/src/main/java/org/apache/hop/databases/mariadb/MariaDBDatabaseMeta.java
@@ -52,6 +52,10 @@ public class MariaDBDatabaseMeta extends MySqlDatabaseMeta {
private static final Class<?> PKG = MariaDBDatabaseMeta.class;
+ private static final String SQL_SELECT_SEQUENCES =
+ "SELECT TABLE_NAME AS sequence_name FROM INFORMATION_SCHEMA.TABLES"
+ + " WHERE TABLE_TYPE = 'SEQUENCE' AND ";
+
private static final Set<String> SHORT_MESSAGE_EXCEPTIONS =
Sets.newHashSet("org.mariadb.jdbc.internal.stream.MaxAllowedPacketException");
@@ -89,6 +93,76 @@ public class MariaDBDatabaseMeta extends MySqlDatabaseMeta {
return !(cause != null &&
SHORT_MESSAGE_EXCEPTIONS.contains(cause.getClass().getName()));
}
+ /** MariaDB supports sequences natively since 10.3, unlike MySQL. */
+ @Override
+ public boolean isSupportsSequences() {
+ return true;
+ }
+
+ /**
+ * MariaDB accepts NOMAXVALUE in CREATE SEQUENCE but rejects MAXVALUE -1,
which Hop would
+ * otherwise generate for a sequence without a maximum value.
+ */
+ @Override
+ public boolean isSupportsSequenceNoMaxValueOption() {
+ return true;
+ }
+
+ /** Sequences are listed from the current database, where MariaDB stores
them as tables. */
+ @Override
+ public String getSqlListOfSequences() {
+ return SQL_SELECT_SEQUENCES + "TABLE_SCHEMA = DATABASE()";
+ }
+
+ /**
+ * Check if a sequence exists. The name may be qualified with a database
(schema) name and quoted
+ * with backticks, as Hop passes it; without a database the current one is
searched.
+ *
+ * @param sequenceName The sequence to check
+ * @return The SQL to get the name of the sequence back from the information
schema
+ */
+ @Override
+ public String getSqlSequenceExists(String sequenceName) {
+ String name = sequenceName.replace(getStartQuote(),
"").replace(getEndQuote(), "");
+ int dotPos = name.indexOf('.');
+ String schemaCondition = "TABLE_SCHEMA = DATABASE()";
+ if (dotPos != -1) {
+ schemaCondition = "TABLE_SCHEMA = '" + escapeLiteral(name.substring(0,
dotPos)) + "'";
+ name = name.substring(dotPos + 1);
+ }
+ return SQL_SELECT_SEQUENCES
+ + schemaCondition
+ + " AND TABLE_NAME = '"
+ + escapeLiteral(name)
+ + "'";
+ }
+
+ /**
+ * Get the SQL to get the next value of a sequence.
+ *
+ * @param sequenceName The sequence name
+ * @return the SQL to get the next value of a sequence.
+ */
+ @Override
+ public String getSqlNextSequenceValue(String sequenceName) {
+ return "SELECT NEXTVAL(" + sequenceName + ")";
+ }
+
+ /**
+ * Get the SQL to get the current value of a sequence: the last value
generated in this session.
+ *
+ * @param sequenceName The sequence name
+ * @return the SQL to get the current value of a sequence.
+ */
+ @Override
+ public String getSqlCurrentSequenceValue(String sequenceName) {
+ return "SELECT LASTVAL(" + sequenceName + ")";
+ }
+
+ private static String escapeLiteral(String value) {
+ return value.replace("'", "''");
+ }
+
/**
* Returns the column name for a MariaDB field.
*
diff --git
a/plugins/databases/mariadb/src/test/java/org/apache/hop/databases/mariadb/MariaDBSequenceAndReleaseSavePointTest.java
b/plugins/databases/mariadb/src/test/java/org/apache/hop/databases/mariadb/MariaDBSequenceAndReleaseSavePointTest.java
index 81939ef23c..6ded0f3b48 100644
---
a/plugins/databases/mariadb/src/test/java/org/apache/hop/databases/mariadb/MariaDBSequenceAndReleaseSavePointTest.java
+++
b/plugins/databases/mariadb/src/test/java/org/apache/hop/databases/mariadb/MariaDBSequenceAndReleaseSavePointTest.java
@@ -35,14 +35,56 @@ class MariaDBSequenceAndReleaseSavePointTest {
// Set these parameters for the test
IDatabase db = new MariaDBDatabaseMeta();
- Boolean sequenceSupport = false;
+ Boolean sequenceSupport = true;
Boolean savepointSupport = false;
@Test
void testSequenceSupport() {
assertSupports(db, sequenceSupport);
- assertEquals("", db.getSqlNextSequenceValue(sequenceName));
- assertEquals("", db.getSqlCurrentSequenceValue(sequenceName));
+ assertEquals("SELECT NEXTVAL(sequence_name)",
db.getSqlNextSequenceValue(sequenceName));
+ assertEquals("SELECT LASTVAL(sequence_name)",
db.getSqlCurrentSequenceValue(sequenceName));
+ assertEquals(
+ "SELECT TABLE_NAME AS sequence_name FROM INFORMATION_SCHEMA.TABLES"
+ + " WHERE TABLE_TYPE = 'SEQUENCE' AND TABLE_SCHEMA = DATABASE()",
+ db.getSqlListOfSequences());
+ }
+
+ @Test
+ void testSequenceNoMaxValueOption() {
+ // MariaDB rejects MAXVALUE -1 in CREATE SEQUENCE, so a sequence without a
maximum value must
+ // use NOMAXVALUE
+ assertTrue(db.isSupportsSequenceNoMaxValueOption());
+ assertEquals("NOMAXVALUE", db.getSequenceNoMaxValueOption());
+ }
+
+ @Test
+ void testSequenceExistsInCurrentDatabase() {
+ assertEquals(
+ "SELECT TABLE_NAME AS sequence_name FROM INFORMATION_SCHEMA.TABLES"
+ + " WHERE TABLE_TYPE = 'SEQUENCE' AND TABLE_SCHEMA = DATABASE()"
+ + " AND TABLE_NAME = 'sequence_name'",
+ db.getSqlSequenceExists(sequenceName));
+ }
+
+ @Test
+ void testSequenceExistsInQuotedSchema() {
+ // Hop passes schema-qualified names quoted the MySQL way, e.g.
`sales`.`order_seq`
+ assertEquals(
+ "SELECT TABLE_NAME AS sequence_name FROM INFORMATION_SCHEMA.TABLES"
+ + " WHERE TABLE_TYPE = 'SEQUENCE' AND TABLE_SCHEMA = 'sales'"
+ + " AND TABLE_NAME = 'order_seq'",
+ db.getSqlSequenceExists("`sales`.`order_seq`"));
+ assertEquals(
+ "SELECT NEXTVAL(`sales`.`order_seq`)",
db.getSqlNextSequenceValue("`sales`.`order_seq`"));
+ }
+
+ @Test
+ void testSequenceExistsEscapesQuotes() {
+ assertEquals(
+ "SELECT TABLE_NAME AS sequence_name FROM INFORMATION_SCHEMA.TABLES"
+ + " WHERE TABLE_TYPE = 'SEQUENCE' AND TABLE_SCHEMA = DATABASE()"
+ + " AND TABLE_NAME = 'o''seq'",
+ db.getSqlSequenceExists("o'seq"));
}
@Test