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());
}
}