# P2JSQLServer2012Dialect.java

    /*
    ** Module   : P2JSQLServer2012Dialect.java
    ** Abstract : Concrete implementation of Dialect for a SQL Server 2012 backend
    **
    ** Copyright (c) 2014-2017, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- -------------------------------- Description --------------------------------
    ** 001 VMN 20131012 Created initial version with conversion time sequences support.
    ** 002 OM  20140312 Fixed getSequenceCurrValString and added getSequenceNextValString
    **                  Added @Override annotations for all implemented methods. Fixed javadoc.
    **                  Simplified string concatenation operations.
    ** 003 OM  20140508 Fixed sequence generation.
    ** 004 OM  20150522 Added getReservedKeywords() static method.
    ** 005 ECF 20150801 Added applySqlLimit() method implementation. Removed unused logger.
    ** 006 OM  20151130 Fixed sequence generation. Avoid 'variant' datatype by using SQL cast.
    ** 007 ECF 20160912 Added no-op getSequencePrefetchString as a placeholder. Needs an
    **                  implementation which can generate a series like PostgreSQL dialect.
    ** 008 ECF 20200906 New ORM implementation.
    ** 008 IAS 20201204 Added id() method and support stub for word tables
    **     IAS 20210315 Refactored for working with word tables for _temp database
    **     RAA 20230109 Overrided method getSequenceCurrValString().
    **/

    /*
    ** 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 com.goldencode.p2j.persist.orm.*;
    import com.goldencode.p2j.persist.orm.DDLGeneratorWorker.*;
    import com.goldencode.p2j.persist.sequence.*;
    import java.io.*;
    import java.util.*;

    /**
     * Extends P2JSQLServer2008Dialect to add method implementations supported with SQL Server 2012.
     * <p>
     * Note:  this implementation expects a directory to be available at the time
     * the class is loaded.
     */
    public class P2JSQLServer2012Dialect
    extends P2JSQLServer2008Dialect
    {
       /**
        * Get a collection of reserved keywords specific to this dialect.
        *
        * The {@link com.goldencode.p2j.convert.NameConverter} uses thes list to create an exclusion
        * list of names so there will not be schema collisions with identifiers (tables, fields) from
        * the generated schema.
        *
        * @return  A list with SQL Server 2012 reserved keywords.
        *
        * @see     <a href="https://msdn.microsoft.com/en-us/library/ms189822%28v=sql.110%29.aspx">
        *          https://msdn.microsoft.com/en-us/library/ms189822%28v=sql.110%29.aspx</a>
        */
       public static List<String> getReservedKeywords()
       {
          // SQL Server keywords, some may overlap ANSI
          String[] keywords = {
             "add",
             "all",
             "alter",
             "and",
             "any",
             "as",
             "asc",
             "authorization",
             "backup",
             "begin",
             "between",
             "break",
             "browse",
             "bulk",
             "by",
             "cascade",
             "case",
             "check",
             "checkpoint",
             "close",
             "clustered",
             "coalesce",
             "collate",
             "column",
             "commit",
             "compute",
             "constraint",
             "contains",
             "containstable",
             "continue",
             "convert",
             "create",
             "cross",
             "current",
             "current_date",
             "current_time",
             "current_timestamp",
             "current_user",
             "cursor",
             "database",
             "dbcc",
             "deallocate",
             "declare",
             "default",
             "delete",
             "deny",
             "desc",
             "disk",
             "distinct",
             "distributed",
             "double",
             "drop",
             "dump",
             "else",
             "end",
             "errlvl",
             "escape",
             "except",
             "exec",
             "execute",
             "exists",
             "exit",
             "external",
             "fetch",
             "file",
             "fillfactor",
             "for",
             "foreign",
             "freetext",
             "freetexttable",
             "from",
             "full",
             "function",
             "goto",
             "grant",
             "group",
             "having",
             "holdlock",
             "identity",
             "identitycol",
             "identity_insert",
             "if",
             "in",
             "index",
             "inner",
             "insert",
             "intersect",
             "into",
             "is",
             "join",
             "key",
             "kill",
             "left",
             "like",
             "lineno",
             "load",
             "merge",
             "national",
             "nocheck",
             "nonclustered",
             "not",
             "null",
             "nullif",
             "of",
             "off",
             "offsets",
             "on",
             "open",
             "opendatasource",
             "openquery",
             "openrowset",
             "openxml",
             "option",
             "or",
             "order",
             "outer",
             "over",
             "percent",
             "pivot",
             "plan",
             "precision",
             "primary",
             "print",
             "proc",
             "procedure",
             "public",
             "raiserror",
             "read",
             "readtext",
             "reconfigure",
             "references",
             "replication",
             "restore",
             "restrict",
             "return",
             "revert",
             "revoke",
             "right",
             "rollback",
             "rowcount",
             "rowguidcol",
             "rule",
             "save",
             "schema",
             "securityaudit",
             "select",
             "semantickeyphrasetable",
             "semanticsimilaritydetailstable",
             "semanticsimilaritytable",
             "session_user",
             "set",
             "setuser",
             "shutdown",
             "some",
             "statistics",
             "system_user",
             "table",
             "tablesample",
             "textsize",
             "then",
             "to",
             "top",
             "tran",
             "transaction",
             "trigger",
             "truncate",
             "try_convert",
             "tsequal",
             "union",
             "unique",
             "unpivot",
             "update",
             "updatetext",
             "use",
             "user",
             "values",
             "varying",
             "view",
             "waitfor",
             "when",
             "where",
             "while",
             "with",
             "within",
             "writetext",
          };

          return Arrays.asList(keywords);
       }

       /**
        * Generate a DDL string which will create a global sequence.
        *
        * @param   name
        *          Sequence name.
        *
        * @return  Dialect-specific string to create the sequence, or {@code null} if not supported.
        */
       @Override
       public String getCreateSequenceString(String name)
       {
          return "create sequence " + name;
       }

       /**
        * Generate a DDL string which will create a global sequence with the given
        * starting value.
        *
        * @param   name
        *          Sequence name.
        * @param   start
        *          Sequence starting value.
        *
        * @return  Dialect-specific string to create the sequence.
        */
       @Override
       public String getCreateSequenceString(String name, long start)
       {
          return "create sequence " + name + " start with " + start;
       }

       /**
        * Generate a DDL string which will create a sequence with given properties.
        *
        * @param   name
        *          Sequence name. Mandatory not null.
        * @param   init
        *          Sequence starting value. Mandatory(not null).
        * @param   increment
        *          Sequence incrementing value. Mandatory(not null).
        * @param   minVal
        *          Sequence minimum value. Optional (may be null).
        * @param   maxVal
        *          Sequence maximum value. Optional (may be null).
        * @param   cycle
        *          Sequence cycling property. Optional, if missing assumed not cycling.
        *
        * @return  Dialect-specific string to create the sequence.
        */
       @Override
       public String getCreateSequenceString(String name,
                                             Long init,
                                             Long increment,
                                             Long minVal,
                                             Long maxVal,
                                             Boolean cycle)
       {
          StringBuilder buf = new StringBuilder("create sequence ");
          buf.append(name);
          // SQL server allows a sequence can be defined as any integer type, we prefer int64/bigint
          buf.append(" as [bigint]");
          buf.append(" increment by ").append(increment);

          if (minVal != null)
          {
             // MINVALUE.
             // The default minimum value for a new sequence object is the minimum value of the data
             // type of the sequence object. This is zero for the tinyint data type and a negative
             // number for all other data types.
             buf.append(" minvalue ").append(minVal);
             if (increment > 0 && init < minVal)
             {
                init = minVal;
             }
          }

          if (maxVal != null)
          {
             // MAXVALUE.
             // The default maximum value for a new sequence object is the maximum value of the data
             // type of the sequence object.
             buf.append(" maxvalue ").append(maxVal);
             if (increment < 0 && init > maxVal)
             {
                init = maxVal;
             }
          }

          buf.append(" start with ").append(init);
          // START WITH <constant>
          // The START value must be a value less than or equal to the maximum and greater than or
          // equal to the minimum value of the sequence object. The default start value for a new
          // sequence object is the minimum value for an ascending sequence object and the maximum
          // value for a descending sequence object.

          if (cycle != null && cycle)
          {
             buf.append(" cycle");
          }
          // TODO cache parameter is also supported but:
          // If the cache option is enabled without specifying a cache size, the Database Engine
          // will select a size. However, users should not rely upon the selection being consistent.
          // Microsoft might change the method of calculating the cache size without notice.

          return buf.toString();
       }

       /**
        * Generate a DDL string which will drop a sequence with given properties.
        *
        * @param   name
        *          The name of the sequence to be dropped.
        *
        * @return  Dialect-specific string to drop the sequence.
        */
       @Override
       public String getDropSequenceString(String name)
       {
          // http://technet.microsoft.com/en-us/library/ff877934.aspx and
          // http://technet.microsoft.com/en-us/library/ms190324.aspx
          // type SO = Sequence object

          return "if exists (select * from sys.sequences where name='" + name +"' and type='SO') " +
                      "drop sequence " + name;
       }

       /**
        * Generate the appropriate select statement to reset the value of a sequence.
        *
        * @param   sequenceName
        *          The name of the sequence.
        * @param   newVal
        *          The new value to be set.
        *
        * @return  Dialect-specific string to query the sequence.
        */
       @Override
       public String getSequenceSetValString(String sequenceName, long newVal)
       {
          return "alter sequence " + sequenceName + " restart with " + newVal;
       }

       /**
        * Generate the appropriate select statement to retrieve the current value of a sequence.
        *
        * @param   sequenceName
        *          The name of the sequence to be queried.
        *
        * @return  Dialect-specific string to query the sequence.
        */
       @Override
       public String getSequenceCurrValString(String sequenceName)
       {
          // the cast is needed because the column is of 'variant' datatype.
          // the sql driver does not handle it and reports the following error:
          // 08S01 The "variant" data type is not supported.
          return "select cast(current_value as bigint) " +
                 "from sys.sequences where name = '" + sequenceName + "'";
       }

       /**
        * Generate the appropriate select statement to retrieve the current value of a sequence.
        *
        * @return  Dialect-specific string to query the sequence.
        */
       @Override
       public String getSequenceCurrValString()
       {
          // the cast is needed because the column is of 'variant' datatype.
          // the sql driver does not handle it and reports the following error:
          // 08S01 The "variant" data type is not supported.
          return "select cast(current_value as bigint) " +
                 "from sys.sequences where name = ?";
       }

       /**
        * Generate the appropriate select statement to retrieve the next value of a sequence.
        *
        * @param   sequenceName
        *          The name of the sequence.
        *
        * @return  MS SQL Server dialect-specific string to query the sequence.
        */
       @Override
       public String getSequenceNextValString(String sequenceName)
       {
          return "select next value for " + sequenceName;
       }

       /**
        * 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>true</code> because Server 2012 supports the concept of a SEQUENCE.
        */
       @Override
       public boolean hasFullSequenceSupport()
       {
          return true;
       }

       /**
        * 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)
       {
          // TODO: implement; needs feature similar to PostgreSQL generate_series
          return null;
       }

       /**
        * Creates and returns a <code>SequenceHandler</code> for a SQL Server 2012 database.
        *
        * @param   ldbName
        *          The logical name of the database to create the <code>SequenceHandler</code> for.
        *
        * @return  A <code>SQLServer2012SequenceHandler</code> instance.
        */
       @Override
       public SequenceHandler createSequenceHandler(String ldbName)
       {
          return new SQLServer2012SequenceHandler(ldbName);
       }

       /**
        * Given a string builder containing an SQL SELECT statement, a limit to apply, and the
        * position in the builder at which the SELECT statement begins, augment the SQL statement to
        * apply the given limit.
        * <p>
        * This method is intended to be used only in the specialized case of a well-known subquery
        * which always begins with the SELECT keyword and always ends with an ORDER BY clause.
        * <p>
        * This implementation inserts <code>top</code> keyword, followed by the given numeric limit,
        * immediately after the SELECT keyword.
        *
        * @param   limit
        *          Maximum number of records to return.
        * @param   buf
        *          String builder containing the select statement. At the time this method is
        *          invoked, the builder will contain a statement which always will begin with the
        *          SELECT keyword and will end with an ORDER BY clause.
        * @param   selectStartPos
        *          Zero-based index position in the string builder of the first character of the
        *          SELECT keyword.
        */
       public void applySqlLimit(int limit, StringBuilder buf, int selectStartPos)
       {
          buf.insert(selectStartPos + "select".length(), " top " + limit);
       }

       /**
        * Get dialect id.
        *
        * @return  Dialect id
        */
       @Override
       public String id()
       {
          return "sqlserver2012";
       }

       /**
        * 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
       }
    }