H2Helper.java
/*
** Module : H2Helper.java
** Abstract : Helper class which initializes embedded H2 database
**
** Copyright (c) 2004-2024, Golden Code Development Corporation.
**
** -#- -I- --Date-- --JPRM-- -----------------------------------Description-----------------------------------
** 001 ECF 20080311 @37516 Created initial version. Helper class which initializes embedded H2 database.
** 002 ECF 20080320 @37649 Added new steps to database preparation. Set sorting of nulls to high
** (NULL values last). Set collation.
** 003 SVL 20080429 @38135 Collation is loaded from the directory.
** 004 ECF 20080508 @38487 Changed createFunctionAliases(). Use Database object when registering
** functions with the HQLPreprocessor rather than database name String.
** This allows differentiation of the same function across multiple
** databases with the same name.
** 005 ECF 20080724 @39169 Fixed bugs in executeDDL(). Connection and SessionFactory must not be
** closed after use.
** 006 ECF 20090729 @43451 Fixed logging in executeDDL(). It was possible to trigger NPE in
** BatchUpdateException catch block.
** 007 CA 20101029 Added method to retrieve a list of database prepare statements.
** 009 CA 20101202 Added method to prepare a permanent database, as the custom functions
** need to be registered with the HQL preprocessor. Removed redundant
** code (collation was set more than 1 time).
** The "h2.sortNullsHigh" setting was removed, as it wasn't taken into
** consideration anyway; also, more testing needs to be done to exactly
** determine how 4GL sorts the unknown value in the temporary and
** permanent database.
** 010 SVL 20130331 Upgraded to Hibernate 4.
** 011 ECF 20131001 Added metadata support and getH2Version method.
** 012 OM 20140728 Added support for HQLFunction annotation. Moved to dialect package.
** 013 EVL 20160225 Javadoc fixes to make compatible with Oracle Java 8 for Solaris 10.
** 014 OM 20160608 Fixed support for HQLFunction annotation.
** 015 OM 20160905 Small optimizations that got lost in previous commit.
** 016 ECF 20160912 Added setCommonInMemoryProperties. Established en_US_P2J as the
** default H2 collation.
** 017 ECF 20180816 Upgrade to H2 1.4.197; needed to add mvcc=false to JDBC URL.
** 018 ECF 20190306 Increase H2 query cache (really a prepared statement cache) size to
** reduce SQL statement parsing.
** 019 CA 20190722 Fixed datetimetz query arguments - they are treated like a ISO date,
** in string representation. UDF are used to compare them.
** 020 CA 20200827 Restore the autocommit state in 'executeDDL'.
** 021 IAS 20210315 Added optional override for in-memory databases (for development)
** 022 AIL 20210319 Allow disabling the undo on H2.
** 023 IAS 20210409 Added uid/pwd override for the remote-meta mode.
** IAS 20219526 Added ';DB_CLOSE_ON_EXIT=FALSE' option.
** OM 20210629 Updated DEFAULT_COLLATION.
** OM 20210716 Final naming schema for custom collation. To be consistent with PG/ Linux locale
** name, the DOT is replaced by underscore in the background.
** OM 20210825 Updated SPI jar name in error message.
** OM 20210909 Used SchemaDictionary.META_DB instead of hardcoded constant.
** OM 20221103 New class names for FQLPreprocessor, FQLExpression, FQLBundle, and FQLCache.
** AL2 20230112 Allow LOCK_MODE 0 to H2 connection.
** 024 DDF 20230322 Added RTRIM property to in-memory temp-tables.
** 025 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
** 026 DDF 20230818 Added DEFAULT_IGNORE_CASE property to in-memory temp-tables.
** 027 DDF 20240122 Added NEVER_RECOMPILE property to in-memory temp-tables.
** 028 RAA 20240319 Added USE_NULL_EQUALITY option to H2 url.
** 029 AL2 20240322 Disable USE_NULL_EQUALITY as it affects internal usages of H2 (validation).
** 030 DDF 20240322 Added closeDatabase() method.
** 031 DDF 20240404 Removed closeDatabase() method.
** 032 DDF 20241002 Added closeDatabase() method (removed previously).
** 033 AL2 20241108 Reduced logging level to FINE if NO_LOCK is used.
*/
/*
** 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.dialect;
import java.io.*;
import java.lang.reflect.*;
import java.sql.*;
import java.util.*;
import java.util.logging.*;
import com.goldencode.p2j.schema.*;
import org.h2.engine.*;
import org.h2.tools.*;
import org.h2.tools.Server;
import com.goldencode.p2j.directory.*;
import com.goldencode.p2j.persist.*;
import com.goldencode.p2j.persist.Database;
import com.goldencode.p2j.persist.orm.*;
import com.goldencode.p2j.persist.orm.Session;
import com.goldencode.p2j.persist.pl.*;
import com.goldencode.p2j.util.*;
import com.goldencode.p2j.util.logging.CentralLogger;
import com.goldencode.util.*;
/**
* Helper class which provides H2-specific services, such as initializing an embedded H2 database,
* running scripts against it, and running a server to provide mixed mode access to it.
*/
public final class H2Helper
{
/** Logger */
private static final CentralLogger log = CentralLogger.get(H2Helper.class.getName());
/** Optional override for in-memory databases */
private static final String JDBC_PREFIX = System.getProperty("fwd.h2.mem", "jdbc:h2:mem:");
/** Default embedded collation. Intentionally use of AT. It will be replaced with underscore. */
private static final String DEFAULT_COLLATION = "en_US@iso88591_fwd_basic";
/**
* Private constructor. All access to this class is via static methods.
*/
private H2Helper()
{
}
/**
* Report the full H2 database version.
* @return full H2 database version.
*/
public static String getH2Version()
{
return Constants.FULL_VERSION;
}
/**
* Set the properties commonly needed by an in-memory, embedded H2 database into the given
* properties object. This includes the JDBC connection URL, JDBC driver, randomly generated
* user name and password, and several Hibernate options.
*
* @param database
* Database name to be embedded in the connection URL.
* @param settings
* Object into which to store properties.
* @param avoidLogging
* Flag indicating tables must be NO-UNDO
* @param noLock
* Set if the database shouldn't use locking. Use only on single-user databases.
* @return The populated properties object (same object instance as {@code props}).
*/
public static Settings setCommonInMemoryProperties(String database,
Settings settings,
boolean avoidLogging,
boolean noLock)
{
boolean inMem = "jdbc:h2:mem:".equals(JDBC_PREFIX);
StringBuilder url = new StringBuilder();
url.append(JDBC_PREFIX).append(database);
url.append(";db_close_delay=-1;mv_store=false;rtrim=true;default_ignore_case=true;");
// url.append("use_null_equality=true;");
url.append("never_recompile=true;");
url.append("query_cache_size=1024;db_close_on_exit=false;");
if (avoidLogging)
{
url.append(";undo_log=0");
}
if (noLock)
{
if (log.isLoggable(Level.FINE))
{
log.log(Level.FINE, "Connecting to H2 database \"" + database + "\" with LOCK MODE set on 0.");
}
url.append(";LOCK_MODE=0");
}
if (!inMem)
{
url.append(";TRACE_LEVEL_FILE=2;TRACE_LEVEL_SYSTEM_OUT=2;TRACE_MAX_FILE_SIZE=4096");
}
String uid = RandomWordGenerator.create(128, 16, 16);
String pwd = RandomWordGenerator.create(128, 16, 16);
DirectoryService ds = DirectoryService.getInstance();
if (database.startsWith(SchemaDictionary.META_DB) && ds.bind())
{
uid = Utils.getDirectoryNodeString(ds, "persistence/remote-meta/user", uid, false);
pwd = Utils.getDirectoryNodeString(ds, "persistence/remote-meta/password", pwd, false);
int trace = Utils.getDirectoryNodeInt(ds, "persistence/remote-meta/trace", 0, false);
url.append(
String.format(
";TRACE_LEVEL_FILE=%d;TRACE_LEVEL_SYSTEM_OUT=%d;TRACE_MAX_FILE_SIZE=4096",
trace,
trace
)
);
}
settings.put(Settings.URL, url.toString());
settings.put(Settings.USER, inMem ? uid : "user");
settings.put(Settings.PASSWORD, inMem ? pwd : "pwd");
// props.put(AvailableSettings.USE_SECOND_LEVEL_CACHE, "false");
// props.put(AvailableSettings.USE_QUERY_CACHE, "false");
// props.put(PersistenceSettings.RELEASE_CONNECTIONS, "on_close");
// props.put(PersistenceSettings.DRIVER, "org.h2.Driver");
settings.put(Settings.DIALECT, P2JH2Dialect.class.getName());
return settings;
}
/**
* Prepare the specified database by creating all necessary function
* aliases and setting global parameters.
* <p>
* The "h2.sortNullsHigh" setting was removed, as the H2 driver was already
* initiated at the time when this method is called, so the setting has no
* effect.
*
* @param database
* Database to prepare.
*
* @throws PersistenceException
* if there is any error executing DDL.
*/
public static void prepareDatabase(Database database)
throws PersistenceException
{
// create built-in function aliases
createFunctionAliases(database, BuiltIns.getPublicFunctions(), true);
createFunctionAliases(database, BuiltIns.getSupportFunctions(), false);
// collation setting: it must be the very first statement executed. There must be absolutely no tables
// defined at the moment the collation is set
String path = String.format("database/%s/p2j/embedded-collation", database.getName());
String collation = Utils.getDirectoryNodeString(null, path, DEFAULT_COLLATION, false);
if (collation != null)
{
try
{
// replacing DOT (.) and AT (@) with underscore (_) so that Linux - compatible schema name
// to be accepted by Java/H2. Example:
// "en_US@iso88591_fwd_basic" -> "en_US_iso88591_fwd_basic"
collation = collation.replace('.', '_').replace('@', '_');
List<String> ddl = new ArrayList<>(1);
ddl.add(String.format("set collation \"%s\"", collation));
executeDDL(ddl, database);
}
catch (PersistenceException exc)
{
log.log(
Level.SEVERE,
"Error setting FWD-specific collation; please ensure " +
"'fwdspi.jar' is installed in Java extension directory");
throw exc;
}
}
else if (log.isLoggable(Level.WARNING))
{
log.log(Level.WARNING,
"Embedded " + (database.isDirty() ? "dirty" : "primary") +
" database '" + database.getName() +
"' collation not set; using default Java collation");
}
}
/**
* Register the overloaded function aliases with the HQL preprocessor, for the permanent
* database and register the overloaded functions with static functions/operators for
* execution on database-side during statement evaluations.
*
* @param database
* Database to prepare.
*
* @throws PersistenceException
* if there is any error executing DDL.
*/
public static void preparePermanentDatabase(Database database)
throws PersistenceException
{
List<String> ddl = new ArrayList<>();
ddl.addAll(getFunctionAliases(database, BuiltIns.getPublicFunctions(), true, true));
ddl.addAll(getFunctionAliases(database, BuiltIns.getSupportFunctions(), false, true));
executeDDL(ddl, database);
}
/**
* Get a list of database prepare DDL statements.
*
* @param database
* Database to prepare.
*
* @return The list of DDL statements.
*/
public static List<String> getPrepareStatements(Database database)
{
List<String> ddl = new ArrayList<>();
// Create built-in function aliases.
ddl.addAll(getFunctionAliases(database, BuiltIns.getPublicFunctions(), true, false));
ddl.addAll(getFunctionAliases(database, BuiltIns.getSupportFunctions(), false, false));
return ddl;
}
/**
* Close the specified database.
* <p>
* Because the DB_CLOSE_ON_EXIT option is used and is set to <code>false</code>, it is required
* to close the database as it will be kept in memory and never be reused (no new connections will
* be established to the database).
*
* @param database
* Database to close.
*
* @throws PersistenceException
* If there is any error during closing.
*/
public static boolean closeDatabase(Database database)
throws PersistenceException
{
try (Session session = new Session(database))
{
Connection conn = session.getConnection();
try
{
return conn.createStatement().execute("SHUTDOWN");
}
catch (SQLException exc)
{
conn.rollback();
throw new PersistenceException("Error closing embedded database", exc);
}
}
catch (SQLException exc)
{
throw new PersistenceException(exc);
}
}
/**
* Run a script of SQL statements using the specified database connection.
*
* @param connection
* Connection to target database; caller should clean up the connection.
* @param reader
* Reader from which the script will be read.
*
* @throws PersistenceException
* if there is any error running the script.
*/
public static void runScript(Connection connection, Reader reader)
throws PersistenceException
{
try
{
RunScript.execute(connection, reader);
connection.commit();
}
catch (SQLException exc)
{
try
{
connection.rollback();
}
catch (SQLException sqle)
{
}
throw new PersistenceException("Error running script against embedded database", exc);
}
}
/**
* Start a mixed mode TCP server using the specified arguments, which are passed directly to
* the H2 server.
*
* @param args
* Arguments to be passed to the H2 server.
*
* @throws PersistenceException
* if there is an error launching the server.
*/
public static void startMixedModeServer(String... args)
throws PersistenceException
{
try
{
Server tcpSrv = Server.createTcpServer(args);
tcpSrv.start();
}
catch (SQLException exc)
{
throw new PersistenceException("Error starting up mixed mode database server", exc);
}
}
/**
* Get function aliases for the given list of static methods, optionally
* making the alias names unique by appending a unique number for each
* overloaded method.
*
* @param database
* Database in which to create aliases.
* @param funcs
* List of static methods for which function aliases should be
* created.
* @param overload
* <code>true</code> to create unique function names for each
* overloaded method
* @param register
* <code>true</code> if the function should be registered with
* the {@link FQLPreprocessor}.
*/
private static List<String> getFunctionAliases(Database database,
List<Method> funcs,
boolean overload,
boolean register)
{
List<String> ddl = new ArrayList<>();
Map<String, Integer> overloads = new HashMap<>();
for (Method method : funcs)
{
HQLFunction hqlFn = method.getAnnotation(HQLFunction.class);
if (hqlFn == null)
{
// not a support function
continue;
}
String name = method.getName();
String hqlFnName = hqlFn.name();
String hqlName = hqlFnName.isEmpty() ? name : hqlFnName;
Integer count = null;
if (overload)
{
count = overloads.get(name);
if (count == null)
{
count = 1;
}
else
{
count++;
}
overloads.put(name, count);
}
StringBuilder buf = new StringBuilder();
buf.append(hqlName);
if (overload)
{
buf.append('_');
buf.append(count);
}
String uniqueName = buf.toString();
buf.insert(0, "create alias if not exists ");
buf.append(" for ");
buf.append(formatMethod(method));
buf.append(";");
ddl.add(buf.toString());
if (overload && register)
{
FQLPreprocessor.registerFunction(database, hqlName, uniqueName, method);
}
}
return ddl;
}
/**
* Create function aliases for the given list of static methods, optionally
* making the alias names unique by appending a unique number for each
* overloaded method.
*
* @param database
* Database in which to create aliases.
* @param funcs
* List of static methods for which function aliases should be
* created.
* @param overload
* <code>true</code> to create unique function names for each
* overloaded method
*
* @throws PersistenceException
* if there is any error executing DDL.
*/
private static void createFunctionAliases(Database database,
List<Method> funcs,
boolean overload)
throws PersistenceException
{
List<String> ddl = getFunctionAliases(database, funcs, overload, true);
executeDDL(ddl, database);
}
/**
* Generate a quoted method name and signature, suitable for use within a
* CREATE ALIAS DDL statement.
*
* @param method
* Method which is to be mapped to a function alias.
*
* @return Quoted method name and signature.
*/
private static String formatMethod(Method method)
{
StringBuilder buf = new StringBuilder();
buf.append("\"");
buf.append(method.getDeclaringClass().getName());
buf.append('.');
buf.append(method.getName());
buf.append('(');
Class<?>[] sig = method.getParameterTypes();
for (int i = 0; i < sig.length; i++)
{
if (i > 0)
{
buf.append(", ");
}
if (sig[i].isArray())
{
buf.append(sig[i].getComponentType().getName());
buf.append("[]");
}
else
{
buf.append(sig[i].getName());
}
}
buf.append(')');
buf.append("\"");
return buf.toString();
}
/**
* Execute a batch of DDL statements for the given database. The batch is executed within a
* single transaction.
*
* @param ddl
* The list of DDL statements to be executed against the database.
* @param database
* The target database.
*
* @throws PersistenceException
* in case of any issue encountered while executing the DDL statements.
*/
private static void executeDDL(List<String> ddl, Database database)
throws PersistenceException
{
try (Session session = new Session(database))
{
Connection conn = session.getConnection();
Statement statement = null;
boolean isAutoCommit = conn.getAutoCommit();
try
{
boolean debug = log.isLoggable(Level.FINEST);
conn.setAutoCommit(false);
statement = conn.createStatement();
for (String next : ddl)
{
statement.addBatch(next);
if (debug)
{
log.log(Level.FINEST, "DDL: " + next);
}
}
statement.executeBatch();
conn.commit();
}
catch (BatchUpdateException exc)
{
conn.rollback();
SQLException next = exc.getNextException();
throw new PersistenceException(
"Error initializing embedded database",
(next != null ? next : exc));
}
catch (SQLException exc)
{
conn.rollback();
throw new PersistenceException("Error initializing embedded database", exc);
}
finally
{
if (statement != null)
{
statement.close();
}
conn.setAutoCommit(isAutoCommit);
}
}
catch (SQLException exc)
{
throw new PersistenceException(exc);
}
}
}