P2JH2Dialect.java
/*
** Module : P2JH2Dialect.java
** Abstract : Concrete implementation of Dialect for an H2 backend
**
** Copyright (c) 2008-2025, Golden Code Development Corporation.
**
** -#- -I- --Date-- --JPRM-- -----------------------------------Description-----------------------------------
** 001 ECF 20080223 @37519 Created initial version. Adds P2JDialect
** method implementations to extend Hibernate's
** H2Dialect.
** 002 ECF 20080611 @38701 Added isCaseInsensitiveColumn(). Provides a
** rough test for case-insensitivity, based only
** on an expression which contains a column
** name, such as might be extracted from index
** metadata. Commented out experimental code.
** 003 SVL 20080623 @38890 Column type for the VARBINARY type was
** changed to "binary" (instead of
** "binary($l)").
** 004 ECF 20080805 @39315 Added getCreateSequenceString(). Generates
** DDL to create a sequence starting at a
** particular value.
** 005 CA 20080815 @39443 Added isConnectionError method - it checks if
** a given exception represents a connection
** error.
** 006 ECF 20080930 @40002 Added isQueryRangeParameterInlined().
** Indicates whether query substitution
** parameters within range checks should be
** inlined.
** 007 ECF 20081015 @40097 Added formatDate(). Formats a date into a
** string suitable for use in an SQL statement.
** 008 ECF 20090310 @41500 Added inlineLimit(). Enables inlining the
** limit/max for records returned by a query
** into the SELECT statement. Currently it is
** unimplemented.
** 009 SVL 20090810 @43574 Added requiresExplicitCastInsideTernary().
** Determines whether substitution parameters should
** be explicitly casted to their datatypes inside
** "then ? else ?" statement.
** 010 GES 20100701 Added a type name override to allow honoring of
** precision and scale in numeric types.
** 011 CA 20101026 Added methods to generate the DROP/CREATE INDEX
** statements in a dialect specific form. Also,
** added method to generate the ALTER TABLE statement
** which sets the formula for the computed columns.
** Added methods to prepare/process string metadata
** query parameters and results, to get the database
** prepare DDL statements and collation statement.
** 012 CA 20101202 Added method to build the URL for a remote
** connection. Removed the "h2.sortNullsHigh"
** setting, as it caused regressions; more testing
** in a 4GL environment is needed, to determine
** exactly how 4GL sorts the unknown value with the
** temporary and permanent databases.
** 013 SVL 20110622 Added usesSingleBackslash.
** 014 OM 20121120 Added DDL/SQL generation support for sequences.
** 015 SVL 20130331 Upgraded to Hibernate 4.
** 016 ECF 20130510 Fixed sequence-related methods to eliminate use of case-sensitive
** sequence names.
** 017 OM 20130712 Added if exists clause to drop sequence statement.
** Implemented createSequenceHandler().
** 018 VMN 20131005 Added injectComputedColumns. Replaced $p to 50 in registerColumnType
** for Types.NUMERIC.
** 019 ECF 20131007 Updated buildRemoteURL to enable secure, mixed-mode URLs for embedded
** databases on remote P2J servers. Added "if exists" to drop index
** string.
** 020 OM 20130820 Added support for PRIMARY KEY in mixed asc/desc indexes.
** 021 OM 20140410 Fixed support case-sensitive fields in indexes.
** Added Override method annotations. Simplified string concatenations.
** 022 VMN 20140427 Added enhance schema name conversion support for hint "escape"
** attribute.
** 023 OM 20140508 Added support for computing the index-key size.
** 024 OM 20140623 Added rtrim() dialect specific implementation. Added query max
** parameters count support.
** 025 OM 20140924 Removed usesSingleBackslash.
** 026 OM 20150522 Added getReservedKeywords() static method.
** 027 OM 20151130 rtrim() dialect specific implementation returns the number of
** occurrences of the expression processed. Implemented
** supportsBooleanDatatype().
** 028 EVL 20160223 Javadoc fixes to make compatible with Oracle Java 8 for Solaris 10.
** 029 OM 20160603 Fixed rtrimming to space character only.
** 030 ECF 20160912 Added getSequencePrefetchString.
** 031 OM 20200610 Replaced Hibernate with customized ORM.
** CA 20200624 Augment the sort with the NULLS FIRST/LAST depending on the direction and dialect.
** CA 20200714 Updated the reserved keywords with the 1.4.200 version.
** AIL 20201120 Changed syntax for sequence nextval.
** IAS 20201204 Added id() method and support stub for word tables
** IAS 20210104 Added trigger's for the population/update word tables
** IAS 20210219 Word tables support for custom extents
** IAS 20210303 Added containsUdfName() function
** CA 20210305 The object and handle fields have a Long representation at the SQL table field.
** IAS 20210310 Added implicit ordering
** IAS 20210315 Refactored for working with word tables for _temp database
** OM 20210412 Replace Hibernate-specific casts with dialect-specific casts.
** OM 20210908 Used SchemaDictionary.TEMP_TABLE_DB instead of hardcoded constant.
** IAS 20211223 Special processing of errors raised by UDFs.
** IAS 20220112 Resolve dialect by JDBC database type.
** IAS 20220602 Added isNativeUDFsSupported() method.
** IAS 20220712 Added getJdbcType() method.
** OM 20220721 Added partial conversion-time MariaDb dialect support.
** IAS 20220816 Added getScriptRunner() method.
** DDF 20220823 Replaced .nextvalue with next value for.
** IAS 20230109 Add 'currval' to reserved keywords' list
** IAS 20220913 Re-work processing of UDFs errors/warnings.
** OM 20220914 Use MAX-WIDTH to generate custom sized varchar columns.
** OM 20221012 Fixed regression (invalid sort criteria) in previous commit.
** OM 20221026 Excluded table name from index name by default. It will be added as needed by
** dialects.
** RAA 20230109 Overrided methods getSequenceSetValString() and getSequenceCurrValString().
** 032 RAA 20230221 Added supportsLazyQueryResultsMode function.
** AL2 20230210 Added getInvisibleSpecificator.
** RAA 20230208 Overrided method for returning the postfix of a no-undo temp-table.
** AL2 20230303 Added a space to the invisible specificator to avoid appending it later.
** 033 AL2 20230316 Allow allowSelectWildcard.
** 034 IAS 20230405 Added fix for H2 bug with bind parameters in recursive CTEs.
** DDF 20230306 Removed usage of computed columns. Set isAutoRtrimAndCi to true. Included
** token for getting types varchar_casesensitive/varchar_ignorecase/varchar
** (refs: #7108).
** DDF 20230309 Removed unnecessary methods related to computed columns and checked for
** CASE_TYPE_TOK before replacing it. Consider all columns to be case
** insensitive in isCaseInsensitiveColumn because it is rarely used.
** 034 DDF 20230406 Removed isAutoRtrimAndCi. Added isAutoRtrim and isAutoCi methods.
** 035 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
** 036 AL2 20231002 Added VALIDATE to the reserved keywords list. This is added in the FWD-H2 fork.
** 037 IAS 20230910 Fixed flags for word tables' generation. Added support for the ScriptRunner.
** 038 DDF 20230818 Created an additional getCreateSequenceString() method that generates
** 039 DDF 20231127 Added isUniqueConstraintViolation() method.
** 040 OM 20231218 Added implementation for DBCODEPAGE and DBCOLLATION 4GL functions.
** 041 OM 20240131 Fixed flaw in DBCODEPAGE / DBCOLLATION implementation.
** 042 RAA 20230601 Added supportsValidateMode function.
** RAA 20230619 Overrided getCreateIndexString function that deals with SOFT_UNIQUE indexes.
** RAA 20230704 Changed name of validateMode to allowDBUniqueCheck.
** 043 RAA 20240319 Added useNullEquality function.
** 044 AL2 20240322 Disable useNullEquality function due to regressions on validation.
** 045 DDF 20240612 Added getAddForeignKeyConstraintString(). ALTER TABLE should be transactional
** as to not commit the session and release all H2 savepoints internally.
** DDF 20240620 Add the referred column besides the table name for REFERENCES.
** 046 OM 20240718 Fixed the list of columns in foreign key when creating FK constraints.
** 047 AS 20240903 Increased the precision for decimal fields to 60.
** 048 OM 20240909 Improved Database API. Concurrent multitenancy functionality implementation.
** 049 AL2 20240812 Fixed infinite loop and ClassCastException in isUniqueConstraintViolation.
** 050 OM 20250117 Added support for multiple dialects for multi-tenant landlord database.
** 051 OM 20250331 Added c3p0 connection parameters.
*/
/*
** 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.net.InetAddress;
import java.net.URI;
import java.sql.*;
import java.text.NumberFormat;
import java.util.*;
import java.util.function.*;
import java.util.logging.*;
import java.util.regex.*;
import java.util.stream.*;
import org.apache.commons.lang.*;
import com.goldencode.p2j.cfg.*;
import com.goldencode.p2j.persist.*;
import com.goldencode.p2j.persist.deploy.*;
import com.goldencode.p2j.persist.h2.*;
import com.goldencode.p2j.persist.orm.*;
import com.goldencode.p2j.persist.sequence.*;
import com.goldencode.p2j.schema.*;
import com.goldencode.p2j.util.*;
import com.goldencode.p2j.util.ErrorManager.*;
import com.goldencode.p2j.util.logging.*;
import static com.goldencode.p2j.persist.dialect.DialectHelper.*;
/**
* Extends abstract {@link Dialect} for an H2 backend.
*/
// TODO: implement base dialect features we need
public class P2JH2Dialect
extends Dialect
{
/** Logger */
private static final CentralLogger LOG = CentralLogger.get(P2JH2Dialect.class.getName());
/** Name of the primary key column. */
private static final String PK = Configuration.getSchemaConfig().getPrimaryKeyName();
/** Mapping between the FWD possible field/column types and their SQL counterparts. */
private static final Map<String, String> fwd2sql = new HashMap<>();
/** Weights' aggregator */
private static final WeightAggregator WEIGHT_AGGREGATOR = new SumAggregator();
/** Regex Pattern for parsing message cased by error raised by UDF. */
public static final Pattern UDF_ERROR_MESSAGE = Pattern.compile(
"^Exception calling user-defined function: .*: (\\*\\* )?(.*)(\\. *)?\\(([0-9]*)\\).*$",
Pattern.DOTALL
);
/* Initialization of static data. */
static
{
fwd2sql.put("integer", "integer");
fwd2sql.put("Integer", "integer");
fwd2sql.put("recid", "integer");
fwd2sql.put("int64", "bigint");
fwd2sql.put("Long", "bigint");
fwd2sql.put("rowid", "bigint");
fwd2sql.put("decimal", "numeric(60," + SCALE_TOK + ")"); // TODO: set precision?
fwd2sql.put("logical", "boolean");
fwd2sql.put("character", "varchar" + CASE_TYPE_TOK);
fwd2sql.put("String", "varchar");
fwd2sql.put("handle", "bigint");
fwd2sql.put("comhandle", "varchar");
fwd2sql.put("date", "date");
fwd2sql.put("datetime", "timestamp");
fwd2sql.put("datetimetz", "timestamp with time zone");
fwd2sql.put("blob", "blob");
fwd2sql.put("clob", "clob");
fwd2sql.put("object", "bigint");
fwd2sql.put("raw", "binary");
}
/** AFTER INSERT trigger for words tables' support template */
private static final List<String> AFTER_INSERT_TRIGGER = Collections.unmodifiableList(
Arrays.asList(
"CREATE TRIGGER %s AFTER INSERT ON %s FOR EACH ROW AS",
"$$org.h2.api.Trigger create() { return new %s(\"%s;%s;%s;%s;%d\"); } $$;"
)
);
/** AFTER UPDATE trigger for words tables' support template */
private static final List<String> AFTER_UPDATE_TRIGGER = Collections.unmodifiableList(
Arrays.asList(
"CREATE TRIGGER %s AFTER UPDATE ON %s FOR EACH ROW AS",
"$$org.h2.api.Trigger create() { return new %s(\"%s;%s;%s;%s;%d\"); } $$;"
)
);
/** DROP TRIGGER statement */
private static final String DROP_TRIGGER = "drop trigger if exists %s;";
// /** Actual minimum interval in milliseconds between analyze commands */
// private static final long analyzeInterval = 60000;
// /** Debug level logging enabled? */
// private static final boolean debug = LOG.isDebugEnabled();
/**
* Default constructor. This constructor is for Hibernate's use. Use code
* should not construct instances of this class.
* <p>
* We provide this constructor to override the superclass' mapping of
* <code>Types.VARCHAR</code>, in order to force this type to map to
* PostgreSQL's <code>text</code> data type.
*/
public P2JH2Dialect()
{
super();
// Override superclass' VARCHAR and VARBINARY mappings, since we always
// use the unbounded variants of these data types.
// registerColumnType(Types.VARCHAR, "varchar");
// registerColumnType(Types.VARBINARY, "binary");
// Override superclass' NUMERIC mapping, since the Hibernate H2 dialect
// doesn't honor precision and scale.
// registerColumnType(Types.NUMERIC, "numeric(50,$s)");
}
/**
* Get a collection of reserved keywords specific to this dialect.
*
* The {@link com.goldencode.p2j.convert.NameConverter} uses thes list to create an exclusion
* list of names so there will not be schema collisions with identifiers (tables, fields) from
* the generated schema.
*
* @return A list with H2 1.3.176 reserved keywords.
*
* @see <a href="http://www.h2database.com/html/advanced.html#compatibility">
* http://www.h2database.com/html/advanced.html#compatibility</a>
*/
public static List<String> getReservedKeywords()
{
String[] keywords =
{
"all",
"array",
"case",
"check",
"constraint",
"cross",
"current_catalog",
"current_date",
"current_schema",
"current_time",
"current_timestamp",
"current_user",
"currval",
"distinct",
"except",
"exists",
"false",
"fetch",
"for",
"foreign",
"from",
"full",
"group",
"having",
"if",
"inner",
"intersect",
"intersects",
"interval",
"is",
"join",
"left",
"like",
"limit",
"localtime",
"localtimestamp",
"minus",
"natural",
"not",
"null",
"offset",
"on",
"order",
"primary",
"qualify",
"right",
"row",
"rownum",
"select",
"sysdate",
"systime",
"systimestamp",
"table",
"today",
"true",
"union",
"unique",
"unknown",
"using",
"values",
"validate",
"where",
"window",
"with",
"_rowid_"
};
return Arrays.asList(keywords);
}
/**
* Get the name of the JDBC driver class associated with this dialect.
*
* @return Fully qualified driver class name.
*/
@Override
public String getDriverClassName()
{
return "org.h2.Driver";
}
/**
* Get the name of the JDBC database type (as in JBC URL).
*
* @return The name of the JDBC database type.
*/
public String getJdbcDatabaseType()
{
return "h2";
}
/**
* Indicate whether this dialect requires computed columns to participate
* within index definitions, instead of embedded expressions.
*
* @return <code>true</code> to indicate computed columns are required.
*/
@Override
public boolean needsComputedColumns()
{
return false;
}
/**
* Indicate whether this dialect natively supports the overloading of user
* defined functions by parameter number and data type.
*
* @return <code>false</code>.
*/
@Override
public boolean supportsFunctionOverloading()
{
return false;
}
/**
* Indicates whether this dialect supports boolean datatype. Support includes:
* <ul>
* <li>usage of {@code true} and {@code false} literals;
* <li>operations with boolean values;
* <li>allowing functions to have booleans as parameters and return datatype.
* </ul>
*
* @return Always {@code true}, all the above mentioned items are supported.
*/
@Override
public boolean supportsBooleanDatatype()
{
return true;
}
/**
* Create a data object, the purpose of which is specific to a particular
* dialect implementation, to be stored as a context-local object. This
* allows a dialect to establish context-local data which is stored by the
* persistence runtime framework, without the overhead of a separate,
* context-local variable. This object will be handed back to the dialect
* in callback methods when the services of certain hooks are required.
* <p>
* Since the context-local data is specific to a particular dialect
* implementation, it is optional. Dialect implementations which do not
* have a need for this data should simply return <code>null</code>.
*
* @param database
* The database of the persistence services.
*
* @return Dialect-specific, context-local data, or <code>null</code>.
*/
@Override
public Object createContext(Database database)
{
//return (analyzeInterval > 0 && database.isTemporary()) ? new Context() : null;
return null;
}
/**
* A dialect-specific callback hook which is invoked by the persistence
* runtime framework after a transaction is complete. The Hibernate
* session will have been closed by the time this method is invoked.
*
* @param persistence
* Provider of persistence services.
* @param context
* Optional, context-local data created previously by {@link
* #createContext}.
* @param rollback
* <code>true</code> if transaction ended with a rollback;
* <code>false</code> if transaction ended with a commit.
*
* @throws PersistenceException
* if any persistence-related error occurs.
*/
@Override
public void postTransaction(Persistence persistence, Object context, boolean rollback)
throws PersistenceException
{
/*
if (context != null)
{
Context local = (Context) context;
long now = System.currentTimeMillis();
long diff = now - local.lastAnalyze;
if (diff >= analyzeInterval)
{
int count = 0;
try
{
Iterator<String> iter = TemporaryBuffer.activeTables();
while (iter.hasNext())
{
iter.next();
count++;
}
if (count > 0)
{
persistence.executeSQL("analyze");
}
}
finally
{
long end = System.currentTimeMillis();
long elapsed = end - now;
local.analyzeTime += elapsed;
if (debug && count > 0)
{
StringBuilder buf = new StringBuilder("Analyze ");
buf.append(count);
buf.append(" temp table(s) [");
buf.append(diff);
buf.append(":");
buf.append(elapsed);
buf.append(":");
buf.append(local.analyzeTime);
buf.append("]");
LOG.debug(persistence.message(buf.toString()));
}
local.lastAnalyze = end;
}
}
}
*/
}
/**
* Format a date object as a string which is appropriate for use in an SQL
* statement.
*
* @param d
* Date to be formatted as a string.
*
* @return Formatted representation of the given date.
*/
@Override
public String formatDate(date d)
{
NumberFormat nf = NumberFormat.getInstance();
nf.setMinimumIntegerDigits(4);
nf.setGroupingUsed(false);
StringBuilder buf = new StringBuilder();
int year = d.getYear();
buf.append(nf.format(Math.abs(year)));
buf.append('-');
nf.setMinimumIntegerDigits(2);
buf.append(nf.format(d.getMonth()));
buf.append('-');
buf.append(nf.format(d.getDay()));
if (year < 0)
{
buf.append(" BC");
}
return buf.toString();
}
/**
* Given a native column name as queried from database metadata, extract the root name of a
* column. For example, a column name as found within an index in metadata might be represented
* as:
* <pre>
* __my_column
* </pre>
* We are interested only in the {@code my_column} root name, so we strip away the leading
* {@code __} prefix.
*
* @param expr
* Column name as natively represented by the database dialect.
*
* @return Root column name.
*/
@Override
public String extractColumnName(String expr)
{
if (expr.startsWith(INSENSITIVE_CHAR_FIELD))
{
return expr.substring(INSENSITIVE_CHAR_FIELD.length());
}
else if (expr.startsWith(SENSITIVE_CHAR_FIELD))
{
return expr.substring(SENSITIVE_CHAR_FIELD.length());
}
else if (expr.startsWith(CC_PREFIX))
{
// has the computed column marker but not case-sensitivity
if (LOG.isLoggable(Level.WARNING))
{
LOG.log(Level.WARNING, "Invalid column name: [" + expr + "]." +
"Failed to extract the root name of the column.");
}
}
return expr;
}
/**
* Adjust a character column name appropriate to this dialect, such that it
* can be inserted into an index. This involves prepending a special
* prefix to designate a computed column.
*
* @param name
* Base column name.
* @param ignoreCase
* <code>true</code> to cause case to be ignored, else <code>false</code>.
*
* @return Processed column name.
*/
@Override
public String getProcessedCharacterColumnName(String name, boolean ignoreCase)
{
if (!needsComputedColumns())
{
return name;
}
return DBUtils.getPrefixedParameter(
name,
ignoreCase ? INSENSITIVE_CHAR_FIELD : SENSITIVE_CHAR_FIELD);
}
/**
* Report whether the given column name represents an alias or an expression which indicates
* the column is case insensitive. Assume the column represented by <code>name</code> is
* already determined to be the appropriate data type for textual data.
*
* @param name
* Column name, alias, or expression.
*
* @return <code>true</code> if <code>name</code> represents case-insensitive data.
*/
@Override
public boolean isCaseInsensitiveColumn(String name)
{
return true;
}
/**
* Generate a DDL string which will create a global sequence.
*
* @param name
* Sequence name.
*
* @return Dialect-specific string to create the sequence, or {@code null} if not supported.
*/
@Override
public String getCreateSequenceString(String name)
{
return "create sequence " + name;
}
/**
* Generate a DDL string which will create a global sequence.
*
* @param name
* Sequence name.
* @param isTemporary
* If the sequence should be temporary or not.
*
* @return Dialect-specific string to create the sequence, or {@code null} if not supported.
*/
@Override
public String getCreateSequenceString(String name, boolean isTemporary)
{
if (isTemporary)
{
return getCreateSequenceString(name) + " temporary";
}
return getCreateSequenceString(name);
}
/**
* Generate a DDL string which will create a global sequence with the given starting value.
*
* @param name
* Sequence name.
* @param start
* Sequence starting value.
*
* @return Dialect-specific string to create the sequence, or {@code null} if not supported.
*/
@Override
public String getCreateSequenceString(String name, long start)
{
return getCreateSequenceString(name) + " start with " + start;
}
/**
* Generate a DDL string which will create a sequence with given properties.
*
* @param name
* Sequence name. Mandatory not null.
* @param init
* Sequence starting value. Mandatory(not null).
* @param increment
* Sequence incrementing value. Mandatory(not null).
* @param minVal
* Sequence minimum value. Optional (may be null).
* @param maxVal
* Sequence maximum value. Optional (may be null).
* @param cycle
* Sequence cycling property. Optional, if missing assumed not cycling.
*
* @return Dialect-specific string to create the sequence, or <code>null</code> if
* not supported.
*/
@Override
public String getCreateSequenceString(String name,
Long init,
Long increment,
Long minVal,
Long maxVal,
Boolean cycle)
{
// H2 create sequence syntax:
// CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName
// [ START WITH long ]
// [ INCREMENT BY long ]
// [ CACHE long ]
// H2 (at least in version 1.3.169) does not support min/max values
// nor cycle sequences, those features will be emulated by p2j
return getCreateSequenceString(name, init) + " increment by " + increment + " cache 1";
}
/**
* Generate a DDL string which will drop a sequence.
*
* @param name
* The name of the sequence to be dropped.
*
* @return Dialect-specific string to drop the sequence, or {@code null} if not supported.
*/
public String getDropSequenceString(String name)
{
return "drop sequence if exists " + name;
}
/**
* Generate the appropriate select statement to reset the value of a
* sequence.
*
* @param sequenceName
* The name of the sequence.
* @param newVal
* The new value to be set.
*
* @return Dialect-specific string to query the sequence, or
* <code>null</code> if not supported.
*/
@Override
public String getSequenceSetValString(String sequenceName, long newVal)
{
return "alter sequence " + sequenceName + " restart with " + newVal;
}
/**
* Generate the appropriate select statement to reset the value of a
* sequence.
*
* @param sequenceName
* The name of the sequence.
*
* @return Dialect-specific string to query the sequence, or
* <code>null</code> if not supported.
*/
@Override
public String getSequenceSetValString(String sequenceName)
{
return "alter sequence " + sequenceName + " restart with ?";
}
/**
* Generate the appropriate select statement to reset the value of a
* sequence.
*
* @return Dialect-specific string to query the sequence, or
* <code>null</code> if not supported.
*/
@Override
public String getSequenceSetValString()
{
throw new IllegalStateException("Can't reset value of a sequence with prepared statement in H2");
}
/**
* Generate the appropriate select statement to retrieve the current
* value of a sequence.
*
* @param sequenceName
* The name of the sequence to be queried.
*
* @return Dialect-specific string to query the sequence, or
* <code>null</code> if not supported.
*/
@Override
public String getSequenceCurrValString(String sequenceName)
{
// this feature is not supported by H2 directly, use a workaround
return "select current_value from information_schema.sequences " +
"where upper(sequence_name) = '" + sequenceName.toUpperCase() + "'";
}
/**
* Generate the appropriate select statement to retrieve the current
* value of a sequence.
*
* @return Dialect-specific string to query the sequence, or
* <code>null</code> if not supported.
*/
@Override
public String getSequenceCurrValString()
{
// this feature is not supported by H2 directly, use a workaround
return "select current_value from information_schema.sequences " +
"where upper(sequence_name) = ?";
}
/**
* Returns <code>true</code> if this dialect fully support the sequences as
* 4GL language (most important: bounded values, cycle). If not fully
* supported, additional code is needed in P2J sequence class
* implementation to fix this.
*
* @return false as H2 dialect does not fully support 4GL sequences
*/
@Override
public boolean hasFullSequenceSupport()
{
return false;
}
/**
* Generate the appropriate select statement to pre-fetch multiple, ascending values from a
* sequence. The values need not be contiguous.
*
* @param sequenceName
* The name of the sequence from which the values are to be retrieved.
*
* @return Dialect-specific SQL expression, or {@code null} if this feature is not supported
* by the dialect.
*/
@Override
public String getSequencePrefetchString(String sequenceName)
{
return "select next value for " + sequenceName + " from system_range(?, ?)";
}
/**
* Generate the appropriate select statement to retrieve the next value of a sequence.
*
* @param sequenceName
* The name of the sequence to be queried.
*
* @return Dialect-specific string to query the sequence, or {@code null} if not supported.
*/
@Override
public String getSequenceNextValString(String sequenceName)
{
return "call next value for " + sequenceName;
}
/**
* Given an exception, this method will check if it indicates a database
* connectivity error.
* <p>
* Currently, as the H2 database is used only for in-memory temp-tables,
* this method returns false.
*
* @param exc
* The exception to be checked.
*
* @return <code>true</code> if the exception indicates a database
* connectivity error.
*/
@Override
public boolean isConnectionError(Exception exc)
{
// TODO: implement.
return false;
}
/**
* Indicate whether a query parameter which is part of a range check should
* be inlined into the where clause, or whether it should be substituted
* into a prepared statement. This will result in an "inlined"
* substitution parameter within a where clause. For example, given a
* where clause of:
* <pre>
* where value ≥ ? and id = ?
* </pre>
* along with respective substitution parameters of 45 and 100, the
* inlined version would read:
* <pre>
* where value ≥ 45 and id = ?
* </pre>
* Only the <code>id</code> substitution parameter of 100 would be
* substituted into the prepared statement.
* <p>
* Inlining in such a way can give some database dialects the opportunity
* to choose a better query plan, than if the parameter being tested with
* a range check were to be substituted at statement execution time.
* <p>
* Note: this serves as a hint which may be overridden by runtime logic or
* other configuration settings.
*
* @return <code>true</code> to inline range check parameters;
* <code>false</code> to use normal parameter substitution.
*/
@Override
public boolean isQueryRangeParameterInlined()
{
return false;
}
/**
* Enable/disable limit clause inlining. Inlining the limit clause will
* cause the limit/max value to be hard-coded into the SQL statement,
* rather than being bound as a substitution parameter.
* <p>
* Dialects are free to treat this method as a hint. This implementation
* does nothing.
*
* @param on
* <code>true</code> to enable inlining; <code>false</code> to
* disable it.
*/
@Override
public void inlineLimit(boolean on)
{
}
/**
* Determines whether substitution parameters should be explicitly casted
* to their datatypes inside "then ? else ?" statement.
*
* @return <code>true</code>.
*/
@Override
public boolean requiresExplicitCastInsideTernary()
{
return true;
}
/**
* Check if bind parameters in recursive CTEs are supported.
*
* @return <code>true</code> if bind parameters in recursive CTEs are supported.
*/
public boolean allowBindParamsInRecursiveCTE()
{
return false;
}
// /**
// * Context local data needed by this object to perform dialect-specific
// * processing. In this case, we track the last time a temp table analyze
// * was performed and how much accumulated time we have spent performing
// * such analyze commands in this context.
// */
// private static class Context
// {
// /** Time of last temp table analyze */
// private long lastAnalyze = System.currentTimeMillis();
//
// /** Accumulated time spent analyzing temp tables */
// private long analyzeTime = 0L;
// }
/**
* Build the DDL to drop an index based upon the specified definition.
* The SQL is generated by using the database-specific dialect.
*
* @param index
* Definition of the index to be dropped.
*
* @return The DDL to drop the given index.
*/
@Override
public String getDropIndexString(P2JIndex index)
{
return "drop index if exists " + quote(getSqlIndexName(index));
}
/**
* Return a string which marks that a column is computed, so it should be marked as invisible
* to allow implicit skipping of it when using wildcard selection (*) or direct access.
*
* @return An SQL string specificator for invisible columns. {@code null} means that no such
* specificator is supported.
*/
public String getInvisibleSpecificator()
{
return " invisible";
}
/**
* Prepare the given metadata query parameter by making it uppercase.
*
* @param param
* The metadata query parameter.
*
* @return the uppercased parameter.
*/
@Override
public String prepareMetadataParameter(String param)
{
return param == null ? null : param.toUpperCase();
}
/**
* Process the given metadata query result, by making it lowercase.
*
* @param value
* The metadata query result.
*
* @return the lowercased result.
*/
@Override
public String processMetadataResult(String value)
{
return value == null ? null : value.toLowerCase();
}
/**
* Check if the given value is a desceding clause for a column index.
*
* @param value
* The value to be checked.
*
* @return <code>true</code> if the value is the "D" or "d" string.
*/
@Override
public boolean isMetadataSortDesc(String value)
{
return "D".equalsIgnoreCase(value);
}
/**
* Check if the given value is an asceding clause for a column index.
*
* @param value
* The value to be checked.
*
* @return <code>true</code> if the value is the "A" or "a" string.
*/
@Override
public boolean isMetadataSortAsc(String value)
{
return "A".equalsIgnoreCase(value);
}
/**
* Get a list of database prepare DDL statements.
*
* @param database
* The database which needs to be prepared.
*
* @return The statement list.
*/
@Override
public List<String> getDatabasePrepareStatements(Database database)
{
return H2Helper.getPrepareStatements(database);
}
/**
* Check if the dialect needs to set an explicit collation during schema
* DDL generation.
*
* @return always <code>true</code>.
*/
@Override
public boolean explicitSetCollation()
{
return true;
}
/**
* Get the statement delimiter for this dialect.
*
* @return the statement delimiter.
*/
@Override
public String getDelimiter()
{
return ";";
}
/**
* Set any database parameters before, to be available when the database
* driver is loaded.
* <p>
* The "h2.sortNullsHigh" setting was removed, as it was determined that
* by forcing the H2 to sort nulls high, it causes a regression in a test
* where 4GL seems to sort the unknown value at the end of the result set,
* when an descending sort clause was used (instead of sorting it at the
* begining of the result set).
* <p>
* TODO: more testing needs to be done to determine exactly how 4GL sorts
* the unknown value in the temporary database (and permanent DB too). At
* the time of this comment, H2 has a bug which doesn't enforce the "sort
* nulls high" setting in cases when the query's result order is determined
* by the index.
*/
@Override
public void beforeDriverLoad()
{
// System.setProperty("h2.sortNullsHigh", "true");
}
/**
* Build the URL to be used for a remote connection, based on the given URL and database.
*
* @param url
* The URL as received from the remote server. If this indicates an embedded
* database, a mixed mode URL which will allow secure (i.e., SSL), remote connections
* to that database is generated. The mixed mode port is fixed.
* @param database
* The database to which this URL belongs.
*
* @return The built URL as to be used for a remote connection.
*
* @throws PersistenceException
* if the specified connection URL is invalid.
*/
@Override
public String buildRemoteURL(String url, Database database)
throws PersistenceException
{
// save the original URL
String origUrl = url;
url = url.substring("jdbc:".length());
try
{
// save the connection mode
String mode = url.substring(0, "h2:tcp:".length());
// if mode is neither TCP nor SSL, we have an embedded database running in mixed mode
boolean mixedMode = !("h2:tcp:".equals(mode) || "h2:ssl:".equals(mode));
// handle network and embedded URLs differently, but in both cases, temporarily drop
// mode so that we can operate on a valid URI below
if (mixedMode)
{
// embedded URL; temporarily insert localhost address after mode, which will force
// replacement with remote host name below
url = url.substring("h2:".length());
url = "h2://localhost/" + url;
mode = "h2:ssl:";
}
else
{
// network URL; get rid of the "h2:tcp:" or "h2:ssl:" text
url = url.substring("h2:tcp:".length());
url = "h2:" + url;
}
URI uri = new URI(url).normalize();
// Check for localhost references.
String host = uri.getHost().toLowerCase();
if (InetAddress.getByName(host).isLoopbackAddress())
{
// Use hostname stored in database object.
host = database.getSocketAddress().getHostName();
// Generate new URI.
uri = new URI(uri.getScheme(),
uri.getUserInfo(),
host,
mixedMode ? DatabaseManager.MIXED_MODE_SSL_PORT : uri.getPort(),
uri.getPath(),
uri.getQuery(),
uri.getFragment());
// Prepend 'jdbc:' prefix.
url = "jdbc:" + mode + uri.toString().substring("h2:".length());
}
else
{
// no preparation is needed
url = origUrl;
}
}
catch (Exception exc)
{
throw new PersistenceException("Invalid database connection URL", exc);
}
return url;
}
/**
* Creates and returns a <code>SequenceHandler</code> for a H2 database.
*
* @param ldbName
* The logical name of the database to create the <code>SequenceHandler</code> for.
*
* @return A <code>H2SequenceHandler</code> for respective database.
*/
@Override
public SequenceHandler createSequenceHandler(String ldbName)
{
return new H2SequenceHandler(ldbName);
}
/**
* Report whether the computed columns should be injected on table creating.
*
* @return <code>true</code> for H2 database.
*/
@Override
public boolean injectComputedColumns()
{
return false;
}
/**
* Checks if the SQLState of the SQLException matches the dialect specific UNIQUE_VIOLATION
* code for H2 (23505). It unwraps the SQLException until an SQLState matching the code is
* found, or there is no exception to unwrap anymore.
*
* @param sqle
* The SQLException that is causing the unique constraint violation.
*
* @return <code>true</code> if the SQLState matches the dialect specific code for
* UNIQUE_VIOLATION, <code>false</code> otherwise.
*/
@Override
public boolean isUniqueConstraintViolation(SQLException sqle)
{
Throwable ex = sqle;
String sqleState;
while (ex != null)
{
if (ex instanceof SQLException)
{
sqleState = ((SQLException) ex).getSQLState();
if ("23505".equals(sqleState))
{
return true;
}
}
ex = ex.getCause();
// avoid infinite loop by continious unwrapping of the cause
if (ex == ex.getCause())
{
return false;
}
}
return false;
}
/**
* Some dialects do have constraints about the maximum size of fields that compose an index.
* This method reports the dialect-specific index length limit, if any.
*
* @return always 0 as this dialect do not have any known constraints about the maximum size
* of the fields of an index.
*/
@Override
public int getIndexLengthLimit()
{
return 0;
}
/**
* Returns the size of the synthetic part of the index. At this moment only the <code>id</code>
* of the record may be additionally added to an index to add custom properties (eg uniqueness)
* but other fields could be added. This part of the index is not visible using index
* reflection routines from <code>IndexHelper</code>. The synthetic components may differ
* between unique and non-unique indexes.
* <p>
* See {@link #getCreateIndexString(P2JIndex, boolean, boolean)}
*
* @param unique
* <code>true</code> if the index is unique
*
* @return 0 if index is unique, 8 = size of an pkid (bigint)
*/
public int getSyntheticIndexSize(boolean unique)
{
return unique ? 0 : 8;
}
/**
* Composes a rtrim equivalent call for a specified expression. This is dialect specific.
* The returned value looks like:
* <pre>
* rtrim(expr)
* </pre>
* The rtrimmed expression is appended to the string builder.
*
* @param expr
* The expression to be rtrim -med. It's up to the caller if it is a qualified field
* or not or any other kind of character-type expression.
* @param sb
* The {@link StringBuilder} to append the trimmed filed to.
*/
@Override
public void addRtrimmedExpression(String expr, StringBuilder sb)
{
sb.append("rtrim(").append(expr).append(")");
}
/**
* Obtain the number of parameters a query of this dialect is allowed by the SQL server.
*
* @return always 0 because H2 does no have such limitation.
*/
@Override
public int getMaxParameterCount()
{
return 0;
}
/**
* Return the beginning of the DDL statement that creates a temporary table.
*
* @param ifNotExist
* If {@code true} an dialect-specific, optional {@code if not exist} clause is added.
*
* @return A string representing the beginning of the DDL statement that creates a temporary
* table.
*/
@Override
public String getCreateTemporaryTableString(boolean ifNotExist) {
return ifNotExist ? "create local temporary table if not exists "
: "create local temporary table ";
}
/**
* Return the ending of the DDL statement that creates a temporary table.
*
* @param noUndo
* Parameter that indicates what keyword should be returned. The choices are "transactional"
* and "noundo".
*
* @return A string representing the ending of the DDL statement that creates a temporary
* table.
*/
@Override
public String getCreateTemporaryTablePostfix(boolean noUndo)
{
if (!noUndo)
{
return " transactional";
}
return " noundo";
}
/**
* Obtain a DDL statement which drops a SQL index.
*
* @param ifExists
* Flag that requires that the DDL include an optional {@code IF EXIST} clause.
* @param idxName
* The name of the index to be dropped by this DDL.
* @param tableName
* The name of the table to which the index belongs to. (Optional, is needed by the dialect).
*
* @return a DDL statement which drops a SQL index.
*/
@Override
public String getDropIndexString(boolean ifExists, String idxName, String tableName)
{
return (ifExists ? "drop index if exists " : "drop index ") + idxName;
}
/**
* Obtain a DDL statement which drops a SQL table.
*
* @param ifExists
* Flag that requires that the DDL include an optional {@code IF EXIST} clause.
* @param tableName
* The name of the table to be dropped by this DDL.
*
* @return a DDL statement which drops a SQL index.
*/
@Override
public String getDropTableString(boolean ifExists, String tableName)
{
return (ifExists ? "drop table if exists " : "drop table ") + tableName + " cascade";
}
/**
* Converts and return the specified {@code FqlType} to a string representation which can be used in a
* SQL query ({@code cast} or other construct).
*
* @param type
* The type to be converted to string.
*
* @return A dialect specific string representation of the respective fql data type.
*/
@Override
public String getSpecificTypeAsString(FqlType type)
{
switch (type.getSqlType())
{
case Types.BIT: return "boolean";
case Types.INTEGER: return "integer";
case Types.BIGINT: return "long";
case Types.NUMERIC: return "numeric";
case Types.DOUBLE: return "double";
case Types.DATE: return "date";
case Types.VARBINARY: return "binary";
case Types.TIMESTAMP: return "timestamp";
case Types.TIMESTAMP_WITH_TIMEZONE: return "timestamp with time zone";
case Types.ARRAY: return "array";
case Types.BLOB: return "blob";
case Types.VARCHAR: return "varchar";
case Types.CLOB: return "clob";
default: return type.toString(); // maybe log this?
}
}
/**
* Get the SQL mapping for a specific FWD data type.
*
* @param fwdType
* The FWD type.
* @param scale
* Optional scale parameter (for {@code decimal} data type).
*
* @return The string representation of the SQL datatype mapped by {@code fwdType}.
*/
@Override
public String getSqlMappedType(String fwdType, int scale, Boolean caseSensitive)
{
String s = fwd2sql.get(fwdType);
if (s == null)
{
if (LOG.isLoggable(Level.WARNING))
{
LOG.log(Level.WARNING, "Unknown '" + fwdType + "' type for H2 dialect.");
}
}
else
{
if (caseSensitive != null)
{
String tmp = caseSensitive ? "_casesensitive" : "_ignorecase";
s = s.replace(CASE_TYPE_TOK, tmp);
}
else
{
int i = s.indexOf(CASE_TYPE_TOK);
if (i > 0)
{
s = s.substring(0, i);
}
}
int k = s.indexOf(SCALE_TOK);
if (k > 0)
{
s = s.substring(0, k) + scale + s.substring(k + SCALE_TOK.length());
}
}
return s;
}
/**
* Construct and return a string which represents the {@code analyze} or similar statements
* for this dialect. If the {@code table} is not {@code null} the statement will analyze only
* the respective table. Otherwise, all the tables are analyzed.
*
* @param table
* The name of the table to be analyzed. If {@code null}, all tables from the database
* will be analyzed, if possible.
*
* @return The analyze statement as requested. If the dialect does not support the
* {@code analyze} statement in the form requested {@code null} is returned.
*/
@Override
public String getAnalyzeString(String table)
{
return table == null ? "analyze" : "analyze table " + table;
}
/**
* Determine if the sort component or index needs to be augmented with a NULLS FIRST/LAST option and
* add the required expression.
*
* @param sb
* The {@code StringBuilder} which contains the SQL statement to be built.
* @param expression
* The field name (or expression) which is the current criterion to be augmented.
* @param field
* The field name which is the base of the previous parameter (unused here).
* @param asc
* The sort direction.
* @param forceAsc
* If {@code true}, force the insertion of the default {@code asc} direction.
*
* @return 1, representing the number of expressions which were added to the string builder.
*/
@Override
public int orderByNulls(StringBuilder sb, String expression, String field, boolean asc, boolean forceAsc)
{
super.orderByNulls(sb, expression, null, asc, forceAsc);
sb.append(asc ? " nulls last" : " nulls first");
return 1;
}
/**
* Get the splitter for DDL scripts.
* @return the splitter for DDL scripts
*/
@Override
public ScriptSplitter scriptSplitter()
{
throw new UnsupportedOperationException();
}
/**
* Get a ScriptRunner instance for the dialect.
*
* @return a ScriptRunner instance for the dialect.
*
*/
@Override
public ScriptRunner getScriptRunner()
{
return new H2ScriptRunner(this);
}
/**
* Create a {@link Blob} instance from the specified bytes.
*
* @param conn
* The JDBC connection.
* @param bytes
* The blob data.
*
* @return The blob instance.
*/
@Override
public Blob blobCreator(Connection conn, byte[] bytes)
{
try
{
Blob blob = conn.createBlob();
blob.setBytes(1, bytes);
return blob;
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
}
/**
* Create a {@link Clob} instance from the specified value.
*
* @param conn
* The JDBC connection.
* @param value
* The clob data.
*
* @return The clob instance.
*/
@Override
public Clob clobCreator(Connection conn, String value)
{
try
{
Clob clob = conn.createClob();
clob.setString(1, value);
return clob;
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
}
/**
* Check if UDF should be used for converted CONTAINS operator
*
* @return <code>true</code> if UDF should be used for converted CONTAINS operator.
*/
@Override
public boolean useUdf4Contains()
{
return true;
}
/**
* Check if word tables should be used for converted CONTAINS operator
*
* @return <code>true</code> if tables should be used for converted CONTAINS operator
*/
public boolean useWordTables()
{
return false;
}
/**
* Check if native UDFs are supported for the dialect
*
* @return <code>true</code> if native UDFs are supported for the dialect
*/
public boolean isNativeUDFsSupported()
{
return false;
}
/**
* Check if UDF should be used for converted CONTAINS operator
*
* @return <code>true</code> if UDF should be used for converted CONTAINS operator
*/
@Override
public boolean isUdfOriginatedError(SQLException e)
{
return "90105".equals(e.getSQLState());
}
/**
* Check if the exception was caused by a warning in UDF.
* @param e
* the exception in question
* @return <code>true</code> if the exception was caused by error in UDF.
*/
@Override
public boolean isUdfOriginatedWarning(SQLException e)
{
return "90105".equals(e.getSQLState());
}
/**
* Create the error descriptor from the SQLException message
*
* @param message
* SQLException message
* @return error descriptor.
*/
@Override
public ErrorEntry errorEntry(String message)
{
Matcher matcher = UDF_ERROR_MESSAGE.matcher(message);
if (matcher.matches() && matcher.groupCount() == 5)
{
try
{
return new ErrorEntry(Integer.parseInt(matcher.group(5)), // was 4
matcher.group(2), !StringUtils.isBlank(matcher.group(1)));
}
catch (NumberFormatException e)
{
// no-op
}
}
return new ErrorEntry(-1, message, false);
}
/**
* Check if Common Table Expressions should be used for converted CONTAINS operator
*
* @return <code>true</code> if Common Table Expressions should be used for converted CONTAINS operator
*/
public boolean useCTE4Contains()
{
return true;
}
/**
* Check if mixed mode re-write should be used for converted CONTAINS operator
*
* @return <code>true</code> if mixed mode re-write should be used
*/
public boolean useMixedNode4Contains()
{
return true;
}
/**
* Check if the provided name is the name of the CONTAINS UDF
* @param name
* name to be checked
* @return <code>true</code> if the provided name is the name of the CONTAINS UDF
*/
public boolean isUdfContains(String name)
{
return "contains_1".equals(name) || "contains_2".equals(name);
}
/**
* Return the name of the CONTAINS UDF
* @return the name of the CONTAINS UDF
*/
public String containsUdfName()
{
return "contains_1";
}
/**
* Get the WeightAggregator instance for the dialect if defined
* @return the WeightAggregator instance for the dialect if defined
*/
@Override
public Optional<WeightAggregator> weightAggregator()
{
return Optional.ofNullable(WEIGHT_AGGREGATOR);
}
/**
* Worker method for {@code generateTriggerDDLs()}. It generates the DDLs needed to create all
* triggers in a database with a specific schema and using a certain dialect.
*
* @param dbName
* The target schema.
* @param out
* The {@code PrintStream} used for output.
* @param eoln
* OS-specific end of line terminator.
* @param wordTables
* word tables by name
*/
@Override
public void generateWordTablesDDLImpl(String dbName,
PrintStream out,
String eoln,
Collection<WordTable> wordTables)
{
boolean isTemp = SchemaDictionary.TEMP_TABLE_DB.equals(dbName);
String afterInsertTrigger = String.join(eoln, AFTER_INSERT_TRIGGER);
String afterUpdateTrigger = String.join(eoln, AFTER_UPDATE_TRIGGER);
wordTables.forEach(wt -> {
if (!isTemp)
{
out.printf(DROP_TRIGGER, wt.afterInsertTriggerName);
out.print(eoln);
out.print(eoln);
}
if (wt.customExtentFields == null)
{
out.printf(afterInsertTrigger,
wt.afterInsertTriggerName, wt.parentTableName,
(wt.extent == 0 ? OnInsertWords.class : OnInsertWordsExt.class).getName(),
PK, wt.fieldName, wt.tableName, wt.caseSensitive, 0);
}
else
{
String flist = wt.customExtentFields.stream().
map(f -> f.fieldName).collect(Collectors.joining(","));
out.printf(afterInsertTrigger,
wt.afterInsertTriggerName, wt.parentTableName,
OnInsertWordsCExt.class.getName(),
PK, flist, wt.tableName, wt.caseSensitive, 0);
}
out.print(eoln);
out.print(eoln);
if (wt.customExtentFields == null)
{
if (!isTemp)
{
out.printf(DROP_TRIGGER, wt.afterUpdateTriggerName);
out.print(eoln);
out.print(eoln);
}
out.printf(afterUpdateTrigger,
wt.afterUpdateTriggerName, wt.parentTableName,
(wt.extent == 0 ? OnUpdateWords.class : OnUpdateWordsExt.class).getName(),
PK, wt.fieldName, wt.tableName, wt.caseSensitive, 0);
out.print(eoln);
out.print(eoln);
}
else
{
for (CustomExtentField f : wt.customExtentFields)
{
if (!isTemp)
{
out.printf(DROP_TRIGGER, wt.afterUpdateTriggerName);
out.print(eoln);
out.print(eoln);
}
out.printf(afterUpdateTrigger,
f.afterUpdateTriggerName, wt.parentTableName,
OnUpdateWordsCExt.class.getName(),
PK, f.fieldName, wt.tableName, wt.caseSensitive, f.pos);
out.print(eoln);
out.print(eoln);
}
}
});
}
/**
* Determine if this {@code Dialect} supports queries executed in a lazy manner.
*
* @return {@code true}
* H2 does support lazy queries.
*/
@Override
public boolean supportsLazyQueryResultsMode()
{
return true;
}
/**
* Determine if the {@code Dialect} supports index validation (with the VALIDATE keyword).
*
* @return {@code true}
* H2 supports this type of validation.
*/
@Override
public boolean allowDBUniqueCheck()
{
return true;
}
/**
* Returns the beginning of the DDL statement for creating an index.
*
* @param unique
* {@code true} for unique indexes.
* @param softUnique
* {@code true} for soft-unique indexes.
*
* @return the beginning of the DDL statement for creating an index.
*/
@Override
public String getCreateIndexString(boolean unique, boolean softUnique)
{
if (unique)
{
return "create unique index ";
}
if (softUnique)
{
return "create soft_unique index ";
}
return "create index ";
}
/**
* Get dialect id.
*
* @return Dialect id.
*/
@Override
public String id()
{
return "h2";
}
/**
* Obtains an object responsible with providing dialect specific SQL statements for tenant management.
*/
@Override
public Dialect.TenantSQL getTenantSql()
{
return new Dialect.TenantSQL()
{
/** SQL for creating the master table, if it doesn't already exist. */
@Override
public String getCreateMasterTable()
{
return "CREATE TABLE IF NOT EXISTS master (\n"+
" tenant_id UUID NOT NULL,\n" +
" tenant_name VARCHAR(32767) NOT NULL,\n" +
" tenant_description VARCHAR(32767),\n" +
" tenant_info VARCHAR(32767),\n" +
" status BOOLEAN,\n" +
" PRIMARY KEY(tenant_id))";
}
/**
* Obtain the SQL statement for creating the {@code tenant_databases} table, if it doesn't already
* exist. The statement must declare at least the following fields: {@code tenant_name (string)},
* {@code pdb_name (string)}, {@code ldb_name (string)}, {@code url (string)},
* {@code username (string)}, {@code password (string)}, {@code C3P0_MAX_STMTS (integer)},
* {@code C3P0_MIN_POOL (integer)}, {@code C3P0_MAX_POOL (integer)}, {@code C3P0_ACQ_INC (integer)},
* {@code C3P0_MAX_IDLE (integer)}.
*
* @return the SQL statement for creating the {@code tenant_databases} table, if it doesn't already
* exist.
*/
@Override
public String getCreateDatabasesTable()
{
return "CREATE TABLE IF NOT EXISTS tenant_databases (\n" +
" tenant_name VARCHAR(255) NOT NULL,\n" +
" pdb_name VARCHAR(255) NOT NULL,\n" +
" ldb_name VARCHAR(255) NOT NULL,\n" +
" url VARCHAR(1023),\n" +
" username VARCHAR(255),\n" +
" password VARCHAR(255),\n" +
" " + C3P0_MAX_STMTS + " INTEGER,\n" +
" " + C3P0_MIN_POOL + " INTEGER,\n" +
" " + C3P0_MAX_POOL + " INTEGER,\n" +
" " + C3P0_ACQ_INC + " INTEGER,\n" +
" " + C3P0_MAX_IDLE + " INTEGER,\n" +
" PRIMARY KEY(tenant_name, pdb_name))";
}
/** SQL for creating an index for the master table on the {@code tenant_name} column. */
@Override
public String getCreateMasterIndex()
{
return "CREATE INDEX IF NOT EXISTS master_idx ON master (tenant_name)";
}
/**
* Obtain the SQL statement for creating an index for the {@code tenant_databases} table.
*
* @return the SQL statement for creating an index for the {@code tenant_databases} table.
*/
@Override
public String getCreateTenantDbIndex()
{
return "CREATE INDEX IF NOT EXISTS tenant_db_idx ON tenant_databases (tenant_name, pdb_name)";
}
};
}
/**
* Helper interface for weights' aggregation
*/
public static class SumAggregator
implements WeightAggregator
{
/**
* Get an aggregate wrapper for converted converted CONTAINS operator.
*
* @return an aggregate wrapper for converted converted CONTAINS operator
*/
public BiConsumer<StringBuilder, Runnable> aggregate()
{
return (sb, r) -> {sb.append("sum(cast("); r.run(); sb.append(" as int))");};
}
/**
* Get the minimal value of the aggregator.
*
* @return the minimal value of the aggregator.
*/
public String minAggregatedValue()
{
return "-1";
}
}
/**
* Returns {@code true} if the dialect handles automatically rtrimming for
* character columns.
*
* @return as described above.
*/
public boolean isAutoRtrim()
{
return true;
}
/**
* Returns {@code true} if the dialect handles null equality.
* This is visible when doing {@code null == null}, which logically should return {@code true},
* but not all dialects support this condition.
*
* @return {@code true} if the dialect permits null equality.
*/
public boolean useNullEquality()
{
return false;
}
/**
* Returns {@code true} if the dialect handles case-insensitive compare
* operations for case-insensitive character columns
*
* @return as described above.
*/
public boolean isAutoCi()
{
return true;
}
/**
* Prepare the specified database by creating all necessary function aliases and setting global parameters.
*
* @param db
* Database to prepare.
*
* @throws PersistenceException
* if there is any error executing DDL.
*/
@Override
public void preparePermanentDatabase(Database db)
throws PersistenceException
{
H2Helper.preparePermanentDatabase(db);
}
/**
* Obtain the name of an index. This dialect overrides this method in order to make sure the name is unique
* among tables because in H2 the index names must be unique.
*
* @param index
* The index structure.
*
* @return the unique SQL name of the index.
*/
@Override
public String getSqlIndexName(P2JIndex index)
{
return "idx__" + index.getTable() + "_" + index.getName();
}
/**
* Creates and returns a string which represent the {@code alter table} statement for adding a new
* foreign key constraint.
*
* @param table
* The table to be altered.
* @param fkName
* The name of the constraint.
* @param fkFields
* Comma separated names of the fields which make the reference to parent table.
* @param refTable
* The parent / referred table.
* @param refFields
* Comma separated names of the fields which uniquely identify the record in parent table.
* @param indent
* The indentation for pretty print the result.
* @param eoln
* OS-specific end of line terminator (if the result is a multiline).
*
* @return A string used to create the foreign key constraint as described above.
*/
@Override
public String getAddForeignKeyConstraintString(String table,
String fkName,
String fkFields,
String refTable,
String refFields,
String indent,
String eoln)
{
if (indent == null)
{
indent = "";
}
if (eoln == null || eoln.isEmpty())
{
eoln = " ";
}
return "alter table " + table + eoln +
indent + "add constraint " + fkName + eoln +
indent + "foreign key (" + fkFields + ")" + eoln +
indent + "references " + refTable + "(" + refFields + ")" + eoln +
indent + "on delete cascade transactional";
}
/**
* Check if this dialect allows the selection of fields using * wildcard. Also, quantification should
* also be supported if this is allowed (alias.*).
*
* @return {@code true} if we can use * as selection wildcard.
*/
public boolean allowSelectWildcard()
{
return true;
}
/**
* Use the provided database connection to extract the codepage/CharSet used by the database and report it
* back to the caller.
*
* @param conn
* The JDBC database connection to be used.
*
* @return The name of the codepage/CharSet used by the database to store character data.
*/
public String getCodepage(Connection conn)
{
try
{
ResultSet rs = conn.prepareStatement(
"SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME " +
"FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='PUBLIC'"
).executeQuery();
if (rs.next())
{
String collName = rs.getString(2); // DEFAULT_COLLATION_NAME
if ("en_US_P2J".equals(collName))
{
return "ISO8859-1"; // FWD 3.0 legacy default
}
int i = collName.indexOf("_fwd_");
if (i > 0)
{
collName = collName.substring(0, i);
i = collName.lastIndexOf('_');
if (i > 0)
{
return mapCP(collName.substring(i + 1));
}
}
return mapCP(rs.getString(1)); // DEFAULT_CHARACTER_SET_NAME
}
}
catch (SQLException e)
{
if (LOG.isLoggable(Level.WARNING))
{
LOG.warning("Failed to retrieve CODEPAGE from database.", e);
}
}
// fallback for all errors
return null;
}
/**
* Use the provided database connection to extract the collation used by the database and report it
* back to the caller.
*
* @param conn
* The JDBC database connection to be used.
*
* @return The name of the collation used by the database to store character data.
*/
@Override
public String getCollation(Connection conn)
{
try
{
ResultSet rs = conn.prepareStatement(
"SELECT DEFAULT_COLLATION_NAME " +
"FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='PUBLIC'"
).executeQuery();
if (rs.next())
{
String collName = rs.getString(1); // DEFAULT_COLLATION_NAME
if ("en_US_P2J".equals(collName))
{
return "basic"; // FWD 3.0 legacy default
}
int i = collName.indexOf("_fwd_");
if (i > 0)
{
return collName.substring(i + 5 /* "_fwd_".length */);
}
return collName;
}
}
catch (SQLException e)
{
if (LOG.isLoggable(Level.WARNING))
{
LOG.warning("Failed to retrieve COLLATION from database.", e);
}
}
// fallback for all errors
return null;
}
/**
* Maps the H2 specific charset names to 4GL codepage names.
*
* @param h2Charset
* A H2 specific charset name.
*
* @return The 4GL codepage name of {@code h2Charset}.
*/
private static String mapCP(String h2Charset)
{
if (h2Charset == null)
{
return null;
}
switch (h2Charset.toUpperCase())
{
case "CP1251": return "1251";
case "CP1252": return "1252";
case "CP1257": return "1257";
case "ISO88591": return "ISO8859-1";
case "ISO885915": return "ISO8859-15";
// case "IBM850": return "IBM850";
default: return h2Charset;
}
}
}