# 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 &ge; ? and id = ?
        * </pre>
        * along with respective substitution parameters of 45 and 100, the
        * inlined version would read:
        * <pre>
        *    where value &ge; 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;
       }
    }