# H2SequenceHandler.java

    /*
    ** Module   : H2SequenceHandler.java
    ** Abstract : Static class that handles sequence functions and statements from Progress / OpenEdge 10.x.
    **
    ** Copyright (c) 2012-2025, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------Description---------------------------------
    ** 001 OM  20121116 Created initial version with CURRVAL, NEXTVAL, and SETVAL support only.
    ** 002 SVL 20120331 Upgraded to Hibernate 4.
    ** 003 OM  20120712 Fixed extraction data from ResultSets. Added error handling.
    **                  Fixed sequence list query from database, enforced cycle and bounds checking.
    ** 004 OM  20151130 Replaced print stack traces with proper logging.
    ** 005 ECF 20160501 Replaced direct SQL use with a more efficient implementation.
    ** 006 ECF 20160609 SQL performance improvement.
    ** 007 ECF 20180219 Reduce Hibernate session flushing, if possible.
    ** 008 OM  20200906 New ORM implementation.
    **     OM  20220531 Fixed an error message.
    **     RAA 20230109 Changed inline statement(s) to prepared statement(s).
    ** 009 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
    ** 010 OM  20241128 Multi-tenant runtime support: selected the proper persistence context, eventually
    **                  based on [sharedDb] parameter.
    ** 011 OM  20250110 Added sequence support for multi-tenant databases.
    */

    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero General Public License as
    ** published by the Free Software Foundation, either version 3 of the
    ** License, or (at your option) any later version.
    **
    ** This program is distributed in the hope that it will be useful,
    ** but WITHOUT ANY WARRANTY; without even the implied warranty of
    ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    ** GNU Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.persist.sequence;

    import java.util.*;
    import java.util.logging.Level;

    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.dialect.*;
    import com.goldencode.p2j.persist.orm.*;
    import com.goldencode.p2j.util.*;
    import com.goldencode.p2j.util.logging.*;

    /**
     * H2 implementation of sequences using a hybrid native SQL sequence primitives and 4GL fixes
     * backed by directory. The primary goal is to detect unknown (?) values that are not supported by
     * H2 and also full management of cycling sequences.
     * H2 only support incremental sequences, no cycling, no limits.
     *
     * TODO:
     * H2 1.3.176 (2014-04-05) fixes support for MINVALUE, MAXVALUE and CYCLE for sequences.
     * It was added in Version 1.3.175 (2014-01-18). The current implementation is based on an older
     * version (1.3.169) that had only basic sequence support.
     */
    public class H2SequenceHandler
    extends SequenceHandler
    {
       /** J2SE Logger. */
       private static final CentralLogger LOG = CentralLogger.get(H2SequenceHandler.class);

       /** The list of sequences grouped by databases. It uses lazy initialization. */
       private HashMap<String, LinkedList<String>> sequences = null;

       /**
        * The only constructor takes the name of the database as argument.
        * It is passed on to super class.
        *
        * @param   ldbName
        *          The database logical name on which this handler will work with.
        */
       public H2SequenceHandler(String ldbName)
       {
          super(ldbName);
       }

       /**
        * Queries the current value of a sequence.
        * <p>
        * This method should be called only in a safe environment, where the database-level lock for
        * access to sequences has already been acquired.
        *
        * @param   seqName
        *          The name of the sequence.
        * @param   ldbName
        *          The database to which the sequence belongs to. It is assumed it is not null and valid.
        * @param   tenantId
        *          The tenant id. If present (not null) it is checked. If not it is computed as
        *          {@code tenant-id(ldbName}).
        *
        * @return the current value of the sequence or unknown value (?) if sequence/database not
        *         found or the sequence is not cycling and has already passed the maximum value.
        */
       @Override
       protected int64 safeGetCurrentValue(String seqName, String ldbName, Long tenantId)
       {
          Sequence seq = SequenceManager.getSequence(ldbName, seqName);
          if (seq == null)
          {
             ErrorManager.recordOrThrowError(2913, seqName.toUpperCase(), "");
             // Unknown sequence expression <sequence name>. (2913)
             return new int64();
          }

          Persistence persistence = ConnectionManager.getPersistence(ldbName);
          if (tenantId != null)
          {
             int crtTenant = persistence.getContext(Persistence.PRIVATE_CTX).getTenantId();
             if (crtTenant != tenantId)
             {
                // NOTE: super-tenants are not implemented yet so we raise this unconditionally, for the moment:
                ErrorManager.recordOrThrowError(15956);
                // Only super-tenants may use a tenant-id other than their own in a sequence function. (15956)
                return new int64();
             }
          }

          Dialect dialect = persistence.getDialect();
          Object[] args = new Object[] {seqName};
          String strQuery = dialect.getSequenceCurrValString();
          int64 ret;

          try
          {
             ret = getResultValue(persistence.getSingleSQLResult(strQuery, !seq.isMultiTenant(), args));
          }
          catch (PersistenceException pe)
          {
             if (LOG.isLoggable(Level.WARNING))
             {
                LOG.log(Level.WARNING,
                        "Failed to get CURRENT-VALUE from " + ldbName + "." + seqName + ": " +
                        pe.getMessage());
             }
             return new int64(); // return unknown value
          }

          // check the result against the dmo-index properties of the sequence
          long inc = seq.getIncrement();
          if (inc > 0)
          {
             // ascendant sequence
             if (ret.longValue() > seq.getMax())
             {
                // current-value return the last valid value
                return new int64(seq.getMax());
             }
          }
          else if (inc < 0)
          {
             // descendant sequence
             if (ret.longValue() < seq.getMin())
             {
                // current-value return the last valid value
                return new int64(seq.getMin());
             }
          }

          return ret;
       }

       /**
        * Computes and returns the next value of a sequence.
        * <p>
        * This method should be called only in a safe environment, where the database-level lock for
        * access to sequences has already been acquired.
        *
        * @param   seqName
        *          The name of the sequence.
        * @param   ldbName
        *          The database to which the sequence belongs to. It is assumed it is not null and valid.
        * @param   tenantId
        *          The tenant id. If present (not null) it is checked. If not it is computed as
        *          {@code tenant-id(ldbName}).
        *
        * @return the current value of the sequence or unknown value (?) if sequence/database not
        *         found or the sequence is not cycling and has passed the maximum/minimum value.
        */
       @Override
       protected int64 safeGetNextValue(String seqName, String ldbName, Long tenantId)
       {
          Sequence seq = SequenceManager.getSequence(ldbName, seqName);
          if (seq == null)
          {
             ErrorManager.recordOrThrowError(2913, seqName.toUpperCase(), "");
             // Unknown sequence expression <sequence name>. (2913)
             return new int64();
          }

          // H2 server is different from Progress, it has not MIN/MAX attributes:
          //    * normally there is no CYCLE attribute
          //    * always loops back to Long.MAX_VALUE and Long.MIN_VALUE
          //       We need to detect these occurrences and return ? instead.

          long crtVal = safeGetCurrentValue(seqName, ldbName, tenantId).longValue();
          long inc = seq.getIncrement();
          if (!seq.isCycle())
          {
             if (inc > 0)
             {
                if (crtVal + inc > seq.getMax() ||  // prevent H2 from passing MAX-value
                    crtVal + inc < crtVal)          // prevent H2 from overflowing downwards on long
                {
                   return new int64();
                }
             }
             else
             {
                if (crtVal + inc < seq.getMin() ||  // prevent H2 from passing MIN-value
                    crtVal + inc > crtVal)          // prevent H2 from overflowing upwards on long
                  {
                     return new int64();
                  }
             }
          }
          else
          {
             if (inc > 0)
             {
                if (crtVal + inc > seq.getMax() ||  // prevent H2 from passing MAX-value
                    crtVal + inc < crtVal)          // prevent H2 from overflowing downwards on long
                {
                   long ret = seq.getInitial();
                   safeSetValue(seqName, ldbName, ret, tenantId);
                   return new int64(ret);
                }
             }
             else
             {
                if (crtVal + inc < seq.getMin() ||  // prevent H2 from passing MIN-value
                    crtVal + inc > crtVal)          // prevent H2 from overflowing upwards on long
                {
                   long ret = seq.getInitial();
                   safeSetValue(seqName, ldbName, ret, tenantId);
                   return new int64(ret);
                }
             }
          }

          Persistence persistence = ConnectionManager.getPersistence(ldbName);
          if (tenantId != null)
          {
             int crtTenant = persistence.getContext(Persistence.PRIVATE_CTX).getTenantId();
             if (crtTenant != tenantId)
             {
                // NOTE: super-tenants are not implemented yet so we raise this unconditionally, for the moment:
                ErrorManager.recordOrThrowError(15956);
                // Only super-tenants may use a tenant-id other than their own in a sequence function. (15956)
                return new int64();
             }
          }

          Dialect dialect = persistence.getDialect();
          String strQuery = dialect.getSequenceNextValString(seqName);
          int64 ret;

          try
          {
             ret = getResultValue(persistence.getSingleSQLResult(strQuery, !seq.isMultiTenant(), null));
          }
          catch (PersistenceException pe)
          {
             if (LOG.isLoggable(Level.WARNING))
             {
                LOG.log(Level.WARNING,
                        "Failed to get NEXT-VALUE from " + ldbName + "." + seqName + ": " +
                        pe.getMessage());
             }
             return new int64(); // return unknown value
          }

          // check the result against the dmo-index properties of the sequence
          if (inc > 0)
          {
             // ascendant sequence
             if (ret.longValue() > seq.getMax())
             {
                if (seq.isCycle())
                {
                   // compute the correct value by restart the counter and set to database:
                   safeSetValue(seqName, ldbName, seq.getInitial(), tenantId);

                   return new int64(seq.getInitial());
                }
                else
                {
                   // terminating sequence, returning unknown
                   return new int64();
                }
             }
          }
          else if (inc < 0)
          {
             // descendant sequence
             if (ret.longValue() < seq.getMin())
             {
                if (seq.isCycle())
                {
                   // compute the correct value by restart the counter and set to database:
                   safeSetValue(seqName, ldbName, seq.getInitial(), tenantId);

                   return new int64(seq.getInitial());
                }
                else
                {
                   // terminating sequence, returning unknown
                   return new int64();
                }
             }
          }

          return ret;
       }

       /**
        * Initialize (reset) the current value of a sequence.
        * <p>
        * This method should be called only in a safe environment, where the database-level lock for
        * access to sequences has already been acquired.
        *
        * @param   seqName
        *          The name of the sequence.
        * @param   ldbName
        *          The database to which the sequence belongs to. It is assumed it is not null and
        *          valid.
        * @param   newVal
        *          The value the sequence will be reset to.
        * @param   tenantId
        *          The tenant id. If present (not null) it is checked. If not it is computed as
        *          {@code tenant-id(ldbName}).
        */
       @Override
       protected void safeSetValue(String seqName, String ldbName, long newVal, Long tenantId)
       {
          Sequence seq = SequenceManager.getSequence(ldbName, seqName);
          if (seq == null)
          {
             ErrorManager.recordOrThrowError(3133,
                     "SYSTEM ERROR: Sequence " + seqName.toUpperCase() + " not found");
             return;
          }
          if (seq.getMin() > newVal || newVal > seq.getMax())
          {
             ErrorManager.recordOrThrowError(3132,
                     "Cannot set sequence " + seq.getLegacyName().toUpperCase() +
                     " beyond its max/min values");
             return;
          }

          /* H2 database server sets the current value of a sequence so as
           * the following <code>next value</code> is called this newVal argument
           * is returned so the actual current value is newVal - increment.
           * Progress 4GL instead sets the value and it is accessible via
           * <code>(dynamic-)current-value</code> while
           * <code>(dynamic-)next-value</code> will return newVal + increment.
           * The solution is to set the value already incremented into database.
           * Note: newValFixed might be overflowing over long limits! */
          long newValFixed = newVal + seq.getIncrement();

          Persistence persistence = ConnectionManager.getPersistence(ldbName);
          if (tenantId != null)
          {
             int crtTenant = persistence.getContext(Persistence.PRIVATE_CTX).getTenantId();
             if (crtTenant != tenantId)
             {
                // NOTE: super-tenants are not implemented yet so we raise this unconditionally, for the moment:
                ErrorManager.recordOrThrowError(15956);
                // Only super-tenants may use a tenant-id other than their own in a sequence function. (15956)
                return;
             }
          }

          Dialect dialect = persistence.getDialect();
          Object[] args = new Object[] {newValFixed};
          String strQuery = dialect.getSequenceSetValString(seqName);

          try
          {
             persistence.executeSQL(strQuery, !seq.isMultiTenant(), args);
          }
          catch (PersistenceException pe)
          {
             if (LOG.isLoggable(Level.WARNING))
             {
                LOG.log(Level.WARNING,
                        "Failed to set CURRENT-VALUE to " + ldbName + "." + seqName + ": " + pe.getMessage());
             }
          }
       }

       /**
        * Test whether the database contains a sequence. This is useful to identify the correct
        * database/sequences when a 4GL static function / statement is called without the database
        * parameter.
        * <p>
        * This method should be called only in a safe environment, where the database-level lock for
        * access to sequences has already been acquired.
        *
        * @param   seqName
        *          The name of the sequence.
        * @param   ldbName
        *          The database to which the sequence belongs to. If is null then the first connected
        *          database is assumed.
        *
        * @return true if the sequence is defined in the database
        */
       @Override
       protected boolean safeContains(String seqName, String ldbName)
       {
          // lazy initialization
          if (sequences == null)
          {
             sequences = new HashMap<>();
          }

          // make sure the structure is initialized for this database:
          LinkedList<String> crtSeqList;
          if (!sequences.containsKey(ldbName))
          {
             // create the list
             crtSeqList = new LinkedList<>();
             sequences.put(ldbName, crtSeqList);

             Persistence persistence = ConnectionManager.getPersistence(ldbName);
             try
             {
                ScrollableResults<String> rs = persistence.executeSQLQuery(
                            "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES;",
                            Persistence.META_CTX,
                            null);
                while (rs.next())
                {
                   crtSeqList.add(rs.get(0, String.class).toUpperCase());
                }
             }
             catch (PersistenceException pe)
             {
                if (LOG.isLoggable(Level.WARNING))
                {
                   LOG.log(Level.WARNING,
                           "Failed to get sequence list from " + ldbName + ": " + pe.getMessage());
                }
                return false;
             }
          }
          else
          {
             crtSeqList = sequences.get(ldbName);
          }

          // now check whether the sequence is in database
          return crtSeqList.contains(seqName.toUpperCase());
       }
    }