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

Reply via email to