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