# 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 ' ';
       }
    }