P2JSQLServer2008Dialect.java
/*
** Module : P2JSQLServer2008Dialect.java
** Abstract : Concrete implementation of Dialect for a MS SQL Server 2008 backend
**
** Copyright (c) 2013-2025, Golden Code Development Corporation.
**
** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
** 001 VMN 20131012 Created initial version. Adds P2JDialect method implementations to extend
** Hibernate's SQLServer2008Dialect.
** 002 OM 20140106 Added support for mixed asc/desc indexes.
** 003 OM 20140410 Fixed support case-sensitive fields in indexes. Replaced Apache logging.
** Added Override method annotations.
** 004 VMN 20140427 Added enhance schema name conversion support for hint "escape" attribute.
** 005 OM 20140508 Added support for computing the index-key size.
** Fixed character computed columns expression.
** Added implementation for some method stubs required for p2j server startup.
** Forced adding ID to each index (including already unique) because SQL issue.
** 006 OM 20140529 Lowest date set to 01/01/001. Added rtrim() dialect specific implementation.
** Added query max parameters count support. Changed generation of computed
** character columns.
** 007 OM 20140407 Added support for optional SQL length specified in hints.
** 008 OM 20140924 Removed usesSingleBackslash.
** 009 OM 20151130 rtrim() dialect specific implementation returns the number of occurrences
** of the expression processed. Returned the list of ddl for creating support
** UDFs from p2jpl assembly. Implemented requiresExplicitCastInsideTernary() and
** supportsBooleanDatatype().
** 010 EVL 20160217 Clean up comments from symbols invalid for Solaris to compile.
** 011 OM 20160608 Greatly simplified rtrimming.
** 012 ECF 20160912 Added getSequencePrefetchString.
** 013 OM 20200610 Removed Hibernate dependencies.
** 014 OM 20200924 Index components carry multiple information to avoid map lookups for them.
** CA 20200927 Use IdentityHashMap instead of plain map when the key is a Class.
** 015 IAS 20201204 Added id() method and support stub for word tables
** CA 20210305 The object and handle fields have a Long representation at the SQL table field.
** IAS 20211223 Special processing of errors raised by UDFs (placeholder).
** 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.
** IAS 20220913 Re-work processing of UDFs errors/warnings.
** OM 20221026 Excluded table name from index name by default. It will be added as needed by dialects.
** RAA 20230109 Overrided method getSequenceSetValString().
** SVL 20230110 P2JIndex.components() provides direct access to the components array in order to improve
** performance.
** 016 RAA 20230208 Added parameter for getCreateTemporaryTablePostfix.
** DDF 20230306 Added a parameter to getSqlMappedType() (refs: #7108).
** 017 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
** 018 DDF 20231127 Added isUniqueConstraintViolation() method.
** 019 IAS 20230208 Dialect-specific error handler support.
** 020 RAA 20230601 Added support for the SOFT_UNIQUE keyword.
** RAA 20230619 Reverted the call of getCreateIndexString so that SOFT_UNIQUE does not get involved.
** 021 CA 20240331 Use logical constants for TRUE, FALSE, UNKNOWN, within internal FWD runtime, so logical
** type checks must include sub-classes, too.
** 022 OM 20240909 Concurrent multitenancy functionality implementation.
** 023 CA 20241107 Added character constant support.
** 024 OM 20250117 Added support for multiple dialects for multi-tenant landlord database.
** The implementation for this dialect is highly experimental and not properly tested.
** 025 ICP 20250123 Added support for integer and int64 constants.
*/
/*
** 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.sql.*;
import java.sql.Date;
import java.text.NumberFormat;
import java.util.*;
import java.util.logging.*;
import com.goldencode.p2j.persist.orm.*;
import com.goldencode.p2j.util.*;
import com.goldencode.p2j.persist.*;
import com.goldencode.p2j.persist.deploy.*;
import com.goldencode.p2j.persist.sequence.SequenceHandler;
import com.goldencode.p2j.security.ContextLocal;
import com.goldencode.p2j.util.date;
import com.goldencode.p2j.util.logging.*;
import com.goldencode.util.StringHelper;
import static com.goldencode.p2j.persist.dialect.DialectHelper.*;
/**
* Extends Hibernate's <code>SQLServer2008Dialect</code> class to add method implementations
* required by the abstract {@link Dialect} base class.
* <p>
* Note: this implementation expects a directory to be available at the time
* the class is loaded.
*/
//TODO: implement base dialect features we need
public class P2JSQLServer2008Dialect
extends Dialect
{
/** Logger */
private static final CentralLogger LOG = CentralLogger.get(P2JSQLServer2008Dialect.class);
/** Flag indicating whether to inline a record limit in an SQL SELECT */
private static final ContextLocal<Boolean> inlineLimit = new ContextLocal<Boolean>()
{
protected Boolean initialValue() { return Boolean.FALSE; }
};
/**
* <code>datetime</code> is deprecated and it does not align with the SQL Standard.
* We convert p4gl <code>date</code>-related datatypes using the MSSQL <code>date</code> and
* <code>datetime2</code>. For both the lower limit is January 1, 001.
* <p>
* date: <a href='http://msdn.microsoft.com/en-us/library/bb630352(v=sql.100).aspx'>
* msdn.microsoft.com/en-us/library/bb630352(v=sql.100).aspx</a><br>
* datetime2: <a href='http://msdn.microsoft.com/en-us/library/bb677335(v=sql.100).aspx'>
* msdn.microsoft.com/en-us/library/bb677335(v=sql.100).aspx</a>
*/
private static final Date lowDate;
/**
* Highest date supported by SQL Server 2008 (December 31, 9999)
* http://msdn.microsoft.com/en-US/library/ms187819%28v=sql.100%29.aspx
*/
private static final Date highDate;
/**
* Private constant from SQLServer2005Dialect used in sql types mapping with
* registerColumnType(). We need to overwrite mapping to varchar/varbinary exactly with this
* constant.
*/
private static final int MAX_LENGTH = 8000;
/** The maximum size of all fields of an index, in bytes. */
private static final int MAX_INDEX_SIZE = 900;
/** The maximum number of parameters allowed by the SQL Server is a single query. */
private static final int MAX_PARAMETER_COUNT = 2100;
/** Mapping between the FWD possible field/column types and their SQL counterparts. */
private static final Map<String, String> fwd2sql = new HashMap<>();
static final Map<Class<? extends BaseDataType>, Integer> indexFieldSizes = new IdentityHashMap<>();
/* Initialization of static data. */
static
{
indexFieldSizes.put(integer.class, 4); // int
indexFieldSizes.put(logical.class, 1); // bit
indexFieldSizes.put(int64.class, 8); // bigint
indexFieldSizes.put(datetimetz.class, 10); // datetimeoffset
indexFieldSizes.put(datetime.class, 8); // datetime2
indexFieldSizes.put(date.class, 3); // date
indexFieldSizes.put(decimal.class, 17); // numeral(38, n)
indexFieldSizes.put(character.class, 0); // null values do not occupy space in SQL index key.
// BDT constants
indexFieldSizes.put(logical.logicalConstant.class, 1); // bit
indexFieldSizes.put(character.characterConstant.class, 0);
indexFieldSizes.put(integer.integerConstant.class, 4);
indexFieldSizes.put(int64.int64Constant.class, 8);
fwd2sql.put("integer", "int");
fwd2sql.put("Integer", "int");
fwd2sql.put("recid", "int");
fwd2sql.put("int64", "bigint");
fwd2sql.put("Long", "bigint");
fwd2sql.put("rowid", "bigint");
fwd2sql.put("decimal", "numeric(38," + SCALE_TOK + ")");
fwd2sql.put("logical", "bit");
fwd2sql.put("character", "varchar(" + MAX_LENGTH + ")");
fwd2sql.put("String", "varchar(" + MAX_LENGTH + ")");
fwd2sql.put("handle", "bigint");
fwd2sql.put("comhandle", "varchar(" + MAX_LENGTH + ")");
fwd2sql.put("date", "date");
fwd2sql.put("datetime", "datetime2");
fwd2sql.put("datetimetz", "datetimeoffset");
fwd2sql.put("blob", "varbinary(MAX)");
fwd2sql.put("clob", "varchar(MAX)");
fwd2sql.put("object", "bigint");
fwd2sql.put("raw", "binary");
// Prepare supported date values. We use the GMT timezone to define
// our date range, since this is how com.goldencode.p2j.util.date handles its dates.
TimeZone tz = TimeZone.getTimeZone("GMT");
GregorianCalendar cal = new GregorianCalendar(tz);
cal.set(Calendar.ERA, GregorianCalendar.AD);
cal.set(Calendar.YEAR, 1);
cal.set(Calendar.MONTH, Calendar.JANUARY);
cal.set(Calendar.DATE, 1);
lowDate = new Date(cal.getTime().getTime());
cal.clear();
cal.set(Calendar.ERA, GregorianCalendar.AD);
cal.set(Calendar.YEAR, 9999);
cal.set(Calendar.MONTH, Calendar.DECEMBER);
cal.set(Calendar.DATE, 31);
highDate = new Date(cal.getTime().getTime());
}
/**
* 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> and <code>Types.VARBINARY</code>.
*/
public P2JSQLServer2008Dialect()
{
super();
// Override superclass' VARCHAR and VARBINARY mappings, since we always
// use the unbounded variants of these data types.
// registerColumnType(Types.VARBINARY, MAX_LENGTH, "varbinary");
// the varchar (set in CharacterUserType.sqlTypes()) will be specially handled in
// getTypeName(int code, long length, int precision, int scale), see javadoc for details
// registerColumnType(Types.VARCHAR, MAX_LENGTH, "varchar($l)");
// even if SQL Servers support much larger datatypes here (2GB), we use the P4GL limitation
// of 32K instead, however, these columns CANNOT be part of an index
// registerColumnType(Types.LONGNVARCHAR, 32*1024, "varchar(MAX)");
// Maximum SQL Server 2008-2012 NUMERIC column precision is 38.
// registerColumnType(Types.NUMERIC, "numeric(38,$s)");
}
/**
* Get the name of the JDBC driver class associated with this dialect.
*
* @return Fully qualified driver class name.
*/
@Override
public String getDriverClassName()
{
// TODO: test me
return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
}
/**
* Get the name of the JDBC database type (as in JBC URL).
*
* @return The name of the JDBC database type.
*/
public String getJdbcDatabaseType()
{
return "sqlserver";
}
/**
* 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 null;
}
/**
* Retrieve the earliest possible date value which is valid for the
* backing database.
*
* @return Earliest date supported by the backing database.
*/
@Override
public Date getLowDate()
{
return lowDate;
}
/**
* Retrieve the latest possible date value which is valid for the backing
* database.
*
* @return Latest date supported by the backing database.
*/
@Override
public Date getHighDate()
{
return highDate;
}
/**
* 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()));
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.");
}
}
// Strip enclosing double quotes or backticks.
expr = StringHelper.stripEnclosing(expr, '"');
expr = StringHelper.stripEnclosing(expr, '`');
return expr;
}
/**
* Adjust a character column name appropriate to this dialect, such that it
* can be inserted into an index. This may involve wrapping it within one
* or more functions, prepending a special prefix to designate a computed
* column, or any other transformation appropriate to the database syntax.
*
* @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)
{
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)
{
name = StringHelper.stripEnclosing(name, DOUBLE_QUOTE);
return name.startsWith(INSENSITIVE_CHAR_FIELD);
}
/**
* Report whether the given column name represents an alias or an expression which indicates
* the column is computed. 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 a computed column.
*/
public boolean isComputedColumn(String name)
{
// we rely on the fact that both INSENSITIVE_CHAR_FIELD and SENSITIVE_CHAR_FIELD
// have the same prefix: CC_PREFIX
return name.startsWith(CC_PREFIX);
}
/**
* 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 true;
}
/**
* Get the prefix, if any, which should be prepended to computed column and/or property names.
*
* @param caseSensitive
* <code>true</code> if this is a character field was declared with
* <code>CASE-SENSITIVE</code> attribute.
*
* @return <code>null</code> to indicate the backing database does not use computed columns.
*
* @see #needsComputedColumns()
*/
@Override
public String getComputedColumnPrefix(boolean caseSensitive)
{
return caseSensitive ? SENSITIVE_CHAR_FIELD : INSENSITIVE_CHAR_FIELD;
}
/**
* Indicate whether this dialect natively supports the overloading of user
* defined functions by parameter number and data type.
*
* @return false because Server 2008 does not support functions overloading
* ("Function names ... must be unique within the database and to its schema.")
*/
@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>
* Since SQL Server uses BIT (integer datatype with {@code 0} and {@code 1} values) as boolean
* replacement, this method always returns {@code false}.
*
* @return Always {@code false}.
*/
public boolean supportsBooleanDatatype()
{
return false;
}
/**
* 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 <code>false</code> because Server 2008 does not have the concept of a SEQUENCE.
* It appeared since SQL Server 2012.
*/
@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 null;
}
/**
* Given an exception, this method will check if it indicates a database
* connectivity error.
* <p>
* For the SQLServer dialect, 08S01 - common state for disconnect and cancel.
* <p>
*
* @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)
{
boolean error = false;
Throwable next = exc;
while (next != null && !error)
{
if (next instanceof SQLException)
{
String state = ((SQLException) next).getSQLState();
error = "08S01".equals(state);
}
next = next.getCause();
}
return error;
}
/**
* 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.
*
* @param on
* <code>true</code> to enable inlining; <code>false</code> to disable it.
*/
@Override
public void inlineLimit(boolean on)
{
// TODO
}
/**
* Determines whether substitution parameters should be explicitly casted to their datatypes
* inside "then ? else ?" statement.
*
* @return always {@code true} for SQL Server dialects.
*/
@Override
public boolean requiresExplicitCastInsideTernary()
{
return true;
}
/**
* 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)
{
// SQL Server 2008-2012 requires fully qualified index name for drop index.
String idxName = getSqlIndexName(index);
return "if exists (select * from sys.indexes where name = '" + idxName + "') " +
"drop index " + index.getTable() + '.' + idxName;
}
/**
* Build the DDL to create an index based upon the specified definition.
* The SQL is generated by using a database-specific dialect.
*
* @param index
* Definition of the index to be created.
* @param unique
* If <code>true</code>, create the index as a unique index,
* <code>false</code> to omit the unique constraint.
* @param toFile
* <code>true</code> if the string will be output to a file or
* <code>false</code> if it will be directly submitted via JDBC.
*
* @return The DDL which was created.
*/
@Override
public String getCreateIndexString(P2JIndex index, boolean unique, boolean toFile)
{
StringBuilder sb = new StringBuilder();
sb.append(getCreateIndexString(unique)).append(getSqlIndexName(index));
sb.append(" on ").append(index.getTable()).append(" (");
boolean lastDesc = false;
ArrayList<P2JIndexComponent> comps = index.components();
for (int i = 0; i < comps.size(); i++)
{
P2JIndexComponent comp = comps.get(i);
if (comp.isCharType() && comp.isIgnoreCase())
{
sb.append(getProcessedCharacterColumnName(comp.getNormalizedName(), true));
}
else
{
sb.append(comp.getNormalizedName());
}
if (comp.isDescending())
{
sb.append(" desc");
}
if (i != comps.size() - 1)
{
sb.append(", ");
}
lastDesc = comp.isDescending();
}
// Note 1: the direction of the pk column name is the same with the last component before it.
// This make this index much easier to fit using the sort clause of the FindQuery,
// especially in reverse sorting mode (at runtime I cannot tell is the sorting
// direction was already reversed during conversion for FIND LAST/FIND PREV
// structures so I cannot assume the direction of pk id is always ascending;
// however, if the direction is linked to one of the components of the index,
// reversing it's sorting direction will also reverse the sort order of the pk id
// so the query planner will be able to identify the correct index at runtime).
// Note 2: because MSSQL does not allow multiple nulls in unique indexes, we are forced to
// add the [id] field as index component. This way, all records become unique, but
// the application is validating the records (using 4GL constraints) before saving
// to persistence. This workaround is used until we find some solution to the
// problem. Some solutions exist but we need to investigate which one is suitable to
// our environment.
sb.append(", ").append(Session.PK);
if (lastDesc)
{
sb.append(" desc");
}
sb.append(")");
return sb.toString();
}
/**
* Returns the formula for character computed columns. This will be called in two cases: when
* generating the ddl for permanent databases schema (this will be written to a .sql file) and
* when creating temp-tables (the code will be sent directly to SQL).
*
* @param colName
* The base column name.
* @param ignoreCase
* {@code true} if this is a char field which has the "ignore case" flag set.
*
* @return The formula for computed columns or {@code null} if the dialect does not
* support computed columns.
*/
@Override
public String getComputedColumnFormula(String colName, boolean ignoreCase)
{
StringBuilder buf = new StringBuilder();
if (ignoreCase)
{
buf.append("upper(");
}
buf.append("rtrim(").append(colName).append(")");
if (ignoreCase)
{
buf.append(")");
}
return buf.toString();
}
/**
* Prepare the given metadata query parameter.
*
* @param param
* The metadata query parameter.
*
* @return the prepared parameter.
*/
@Override
public String prepareMetadataParameter(String param)
{
switch (param)
{
case "public" :
// at this moment we are using the [dbo] public/default schema in SQL Server.
// Later we might define out own schema to allow you to better manage your namespace.
return "dbo";
// other 'parameters' go here
default:
return param;
}
}
/**
* Process the given metadata query result, (by making it lowercase ?).
*
* @param value
* The metadata query result.
*
* @return the result.
*/
@Override
public String processMetadataResult(String value)
{
return value;
}
/**
* Check if the given value is a descending 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 ascending 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 an empty list.
*/
@Override
public List<String> getDatabasePrepareStatements(Database database)
{
return SQLServerHelper.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()
{
// collation is a property of a database or a column, not of a connection. But we can
// override the collation on the statement level using the COLLATE statement:
// SELECT ... FROM ... COLLATE ...
return false;
}
/**
* Get the statement delimiter for this dialect.
*
* @return the statement delimiter.
*/
@Override
public String getDelimiter()
{
return ";";
}
/**
* 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.
* @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
{
return DialectHelper.buildRemoteURL(url, database);
}
/**
* Generate a DDL string which will create a global sequence.
*
* @param name
* Sequence name.
*
* @return Returning {@code null} since this dialect does not support sequences.
*/
@Override
public String getCreateSequenceString(String name)
{
return null;
}
/**
* 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 Returning {@code null} since this dialect does not support sequences.
*/
@Override
public String getCreateSequenceString(String name, long start)
{
return null;
}
/**
* 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.
* @param maxVal
* Sequence maximum value. Optional.
* @param cycle
* Sequence cycling property. Optional, if missing assumed not cycling.
*
* @return Returning {@code null} since this dialect does not support sequences.
*/
@Override
public String getCreateSequenceString(String name,
Long init,
Long increment,
Long minVal,
Long maxVal,
Boolean cycle)
{
return null;
}
/**
* Generate a DDL string which will drop a sequence.
*
* @param name
* The name of the sequence to be dropped.
*
* @return Returning {@code null} since this dialect does not support sequences.
*/
@Override
public String getDropSequenceString(String name)
{
return null;
}
/**
* 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 Returning {@code null} since this dialect does not support sequences.
*/
@Override
public String getSequenceSetValString(String sequenceName, long newVal)
{
return null;
}
/**
* Generate the appropriate select statement to reset the value of a sequence.
*
* @param sequenceName
* The name of the sequence.
*
* @return Returning {@code null} since this dialect does not support sequences.
*/
@Override
public String getSequenceSetValString(String sequenceName)
{
return null;
}
/**
* Generate the appropriate select statement to reset the value of a sequence.
*
* @return Returning {@code null} since this dialect does not support sequences.
*/
@Override
public String getSequenceSetValString()
{
return null;
}
/**
* Generate the appropriate select statement to retrieve the next value of a sequence.
*
* @param sequenceName
* The name of the sequence to be queried.
*
* @return Returning {@code null} since this dialect does not support sequences.
*/
@Override
public String getSequenceNextValString(String sequenceName)
{
return null;
}
/**
* Generate the appropriate select statement to retrieve the current value of a sequence.
*
* @param sequenceName
* The name of the sequence to be queried.
*
* @return Returning {@code null} since this dialect does not support sequences.
*/
@Override
public String getSequenceCurrValString(String sequenceName)
{
return null;
}
/**
* Creates and returns a {@code SequenceHandler} for a SQL Server 2008 database.
*
* @param ldbName
* The logical name of the database to create the {@code SequenceHandler} for.
*
* @return Returning {@code null} since this dialect does not support sequences.
* It is appeared since SQL Server 2012.
*/
@Override
public SequenceHandler createSequenceHandler(String ldbName)
{
return null;
}
/**
* Report whether the computed columns should be injected on table creating.
*
* @return always <code>true</code> for SQL Server.
*/
@Override
public boolean injectComputedColumns()
{
return true;
}
/**
* Checks if the SQLState of the SQLException matches the dialect specific UNIQUE_VIOLATION
* code (23000).
*
* @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)
{
String sqleState = sqle.getSQLState();
if ("23000".equals(sqleState))
{
return true;
}
return false;
}
/**
* MS SQL Server has constraints about the maximum size of fields that compose an index. All
* combined fields of any index must not exceed 900 bytes.
*
* @return always 900.
*/
@Override
public int getIndexLengthLimit()
{
return MAX_INDEX_SIZE;
}
/**
* Computes the size (in bytes) occupied by this value when stored as index-key in an index.
* It only make sense to call this method if the {@code getIndexLengthLimit()} returns
* positive values.
* If this dialect does not have index-size constraints then this method should return 0.
*
* @param type
* The type to be analyzed.
* @param val
* The actual value. Except for the char datatype, only the type of value
* is used, the actual value is not needed to determine the size occupied by the
* value as index-key.
*
* @return the size (in bytes) occupied by this value when stored as index-key in an index.
*/
@Override
public int computeIndexKeySize(Class<? extends BaseDataType> type, Object val)
{
Integer size = indexFieldSizes.get(type);
if (size == null)
{
throw new RuntimeException("SQLServer Dialect: computeIndexKeySize: Unknown type "+ type);
}
if (size != 0)
{
return size;
}
// only characters are taking variable space (stored as 0 in indexFieldSizes):
if (val == null)
{
return 0; // null values do not occupy space in SQL index key.
}
return ((String) val).length(); // varchar
}
/**
* 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, unused.
*
* @return Always 8 because the workaround of the multiple NULLS in UNIQUE indexes described
* in <code>getCreateIndexString()</code>.
*/
public int getSyntheticIndexSize(boolean unique)
{
return unique ? 8 : 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 name or not or any other kind of character-type expression.
* @param sb
* The {@code 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.
* For 2008 and 2012 the maximum number of parameters is 2100.
* If the query need more parameter it have to be rewritten or split and the result
* reassembled in memory.
*
* @return always 2100.
*/
@Override
public int getMaxParameterCount()
{
return MAX_PARAMETER_COUNT;
}
/**
* 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 "create temporary table ";
}
/**
* Return the ending of the DDL statement that creates a temporary table.
*
* @param noUndo
* This parameter is not used at the moment. The intention was to generate a keyword for
* no-undo tables. Only {@link P2JH2Dialect} benefits from this parameter.
*
* @return An empty string. This dialect does not support this feature.
*/
@Override
public String getCreateTemporaryTablePostfix(boolean noUndo)
{
return "";
}
/**
* 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)
{
if (!ifExists)
{
return "drop index " + idxName; // TODO: test me
}
return "if exists (select * from sys.indexes where name = '" + idxName + "') 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;
}
/**
* 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 SQL Server dialect.");
}
}
else
{
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)
{
// NOTE: I could not find a MSSQL equivalent for 'analyze' stmt
return null;
}
/**
* Get the splitter for DDL scripts.
* @return the splitter for DDL scripts
*/
public ScriptSplitter scriptSplitter()
{
throw new UnsupportedOperationException();
}
/**
* Get a ScriptRunner instance for the dialect.
*
* @return a ScriptRunner instance for the dialect.
*
*/
public ScriptRunner getScriptRunner()
{
throw new UnsupportedOperationException();
}
/**
* 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;
}
/**
* Get name of the checkError UDF.
* @return the name of the checkError UDF.
*/
@Override
public String checkErrorFn()
{
return "dbo.checkError_bb";
}
/**
* Get name of the initError UDF.
* @return the name of the initError UDF.
*/
@Override
public String initErrorFn()
{
return "dbo.initError_b";
}
/**
* Check if the exception was caused by error in UDF.
* @param e
* the exception in question
* @return <code>true</code> if the exception was caused by error in UDF.
*/
@Override
public boolean isUdfOriginatedError(SQLException e)
{
// TODO: implement
throw new UnsupportedOperationException();
}
/**
* 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.
*/
public boolean isUdfOriginatedWarning(SQLException e)
{
// TODO: implement
throw new UnsupportedOperationException();
}
/**
* 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)
{
// TODO implement
}
/**
* Get dialect id.
*
* @return Dialect id
*/
@Override
public String id()
{
return "sqlserver2008";
}
/**
* Prepare the specified database by creating all necessary function aliases and setting global parameters.
*
* @param db
* Database to prepare.
*/
@Override
public void preparePermanentDatabase(Database db)
{
SQLServerHelper.preparePermanentDatabase(db);
}
/** Obtains an object responsible with providing dialect specific SQL statements for tenant management. */
@Override
public TenantSQL getTenantSql()
{
return null;
}
}