SQLServerHelper.java
/*
** Module : SQLServerHelper.java
** Abstract : Helper class which initializes Microsoft SQL Server 2012 database.
**
** Copyright (c) 2014-2022, Golden Code Development Corporation.
**
** -#- -I- --Date-- -------------------------------------- Description --------------------------------------
** 001 OM 20140307 Created initial version. Helper class which provides utilities for
** Microsoft SQL Server 2012.
** 002 OM 20140728 Added support for HQLFunction annotated methods. Granted exec access to
** defined UDFs. Switched nvarchar(MAX) for String parameters and double
** precision for floating point numerical values.
** 003 EVL 20160217 Clean up comments from symbols invalid for Solaris to compile.
** 004 OM 20221103 New class names for FQLPreprocessor, FQLExpression, FQLBundle, and FQLCache.
*/
/*
** 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.*;
import com.goldencode.p2j.persist.pl.*;
import java.lang.reflect.*;
import java.util.*;
/**
* Helper class which provides Microsoft SQL Server 2012 - specific services:
* <ul>
* <li>gather HQL/SQL functions
* <li>decorate them to avoid the overloading restriction of the server
* <li>create the udf function/alias that maps to CLR assembly
* </ul>
*/
public final class SQLServerHelper
{
/** The name of the assembly that maps SQL functions and datatypes to CLR. */
private static final String P2J2CLR = "p2j2clr";
/**
* Private constructor. All access to this class is via static methods.
*/
private SQLServerHelper()
{
}
/**
* Register the function aliases with the HQL preprocessor, for the permanent database.
*
* @param database
* Database to prepare.
*/
public static void preparePermanentDatabase(Database database)
{
getFunctionAliases(database, BuiltIns.getPublicFunctions(), true, true);
getFunctionAliases(database, BuiltIns.getSupportFunctions(), true, true);
}
/**
* Get a list of database prepare DDL statements.
*
* @param database
* Database to prepare.
*
* @return the list of DDL statements.
*/
public static List<String> getPrepareStatements(Database database)
{
List<String> ddl = new ArrayList<>();
// Create built-in function aliases.
ddl.addAll(getFunctionAliases(database, BuiltIns.getPublicFunctions(), true, false));
ddl.addAll(getFunctionAliases(database, BuiltIns.getSupportFunctions(), true, false));
return ddl;
}
/**
* Get function aliases for the given list of static methods, optionally making the alias names
* unique by appending parameter type decorations.
*
* @param database
* Database in which to create aliases.
* @param funcs
* List of static methods for which function aliases should be created.
* @param overload
* <code>true</code> to create unique function names for each overloaded method.
* @param register
* <code>true</code> if the function should be registered with
* the {@link FQLPreprocessor}.
*
* @return the list of function aliases.
*/
private static List<String> getFunctionAliases(Database database,
List<Method> funcs,
boolean overload,
boolean register)
{
List<String> ddl = new ArrayList<>();
for (Method method : funcs)
{
HQLFunction hqlFn = method.getAnnotation(HQLFunction.class);
if (hqlFn == null)
{
// not a support function
continue;
}
String sqlUniqueName = method.getName();
String hqlName = hqlFn.name().isEmpty() ? method.getName() : hqlFn.name();
StringBuilder functionDecoration = new StringBuilder("_");
StringBuilder paramList = new StringBuilder();
Class<?>[] signature = method.getParameterTypes();
for (int i = 0; i < signature.length; i++)
{
if (i > 0)
{
paramList.append(", ");
}
String paramType = signature[i].getName();
paramList.append("@param").append(i + 1);
paramList.append(" ").append(getSqlType(paramType));
functionDecoration.append(getSqlDecoration(paramType));
}
if (overload)
{
sqlUniqueName += functionDecoration.toString();
}
ddl.add("\ncreate function [dbo].[" + sqlUniqueName + "]" +
"\n (" + paramList.toString() + ")" +
"\n returns " + getSqlType(method.getReturnType().getName()) +
"\n as external name [" + P2J2CLR + "].[" +
method.getDeclaringClass().getSimpleName() + "].[" + sqlUniqueName + "]" +
"\ngo" + // only one CREATE FUNCTION in a batch is allowed
"\ngrant exec on [dbo].[" + sqlUniqueName + "] to public" +
"\ngo"); // allow the function to be executed by all database users
if (overload && register)
{
// drop the [ / ] in order to pass through Hibernate
FQLPreprocessor.registerFunction(database, hqlName, "dbo." + sqlUniqueName, method);
}
}
return ddl;
}
/**
* Returns the MS SQL Server datatype associated with the java type.
*
* @param javaType
* The java type (full name).
*
* @return The MS SQL Server datatype mapped by <code>javaType</code>.
* If <code>javaType</code> is invalid, <code>null</code> is returned.
*/
private static String getSqlType(String javaType)
{
switch (javaType)
{
case "java.lang.Boolean":
case "boolean":
return "bit";
case "java.lang.Integer":
return "int";
case "java.lang.Long":
return "bigint";
case "java.lang.String":
return "nvarchar(MAX)"; // CLR string maps to nvarchar !
case "java.math.BigDecimal":
// return "numeric(38, 10)"; // tough decision regarding the precision
return "double precision";
case "java.sql.Date":
return "date";
case "java.sql.Timestamp":
return "datetime2";
}
return null;
}
/**
* Returns the parameter decoration for fixing the function overloading constraint in
* MS SQL Server.
*
* @param javaType
* The java type (full name).
*
* @return a character for decorating the SQL function.
*/
private static char getSqlDecoration(String javaType)
{
switch (javaType)
{
case "java.lang.Boolean":
case "boolean":
return 'b';
case "java.lang.Integer":
return 'i';
case "java.lang.Long":
return 'l';
case "java.lang.String":
return 's';
case "java.math.BigDecimal":
return 'r';
case "java.sql.Date":
return 'd';
case "java.sql.Timestamp":
return 't';
}
return ' ';
}
}