# SQLHelper.java

    /*
    ** Module   : SQLHelper.java
    ** Abstract : Static class that gather utility methods for string processing related to SQL.
    **
    ** Copyright (c) 2014-2024, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------Description---------------------------------
    ** 001 OM  20050517 Created initial version by refactoring methods from character class.
    ** 002 EVL 20160224 Javadoc fixes to make compatible with Oracle Java 8 for Solaris 10.
    ** 003 CA  20230322 Cache the pattern used by 'convertToSQLContains'.
    ** 004 CA  20240512 Added POLY versions for convertToSQLLike and convertToSQLBegins.
    */
    /*
    ** 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.util;

    import java.util.regex.*;

    /**
     * Static class that exposes utility methods for processing strings that are fed to SQL queries as
     * part of <code>WHERE</code> clauses.
     * <p>
     * All <code>convertToSQL___</code> methods assume that the input value is not encoded in any way
     * (does not contain escape characters of Progress, Java or SQL). The only allowed escapes are for
     * DB side Postgres patterns for <code>MATCHES</code> and <code>CONTAINS</code> that will be
     * converted to their respective SQL equivalents. <br>
     * The result of these methods will be SQL encoded but not Java encoded. This way, they can be
     * further processed (rtrim or uppercase) at either conversion or runtime. To be written to java
     * source file at conversion time, the literal must be java-escaped using
     * {@link character#encodeToJavaSource(java.lang.String)}.
     * <p>
     * If the literals to be executed by inlined queries on server side, <code>convertToSQL___</code>
     * methods automatically escapes them. Otherwise, they need to be SQL encoded at runtime, when
     * they are inlined automatically or by the SQL sriver when they are sent as SQL parameters.
     *
     */
    public class SQLHelper
    {
       /** The pattern used by {@link #convertToSQLContains}. */
       private static final Pattern SQL_CONTAINS = Pattern.compile("[&\\(\\)\\^\\|\\!]");

       /**
        * Converts a second operand of the "op1 MATCHES op2" function into corresponding SQL LIKE
        * expression. This processing is meant to be used ONLY for argument in the left-side of
        * a LIKE SQL operator in a WHERE clause.
        * <p>
        * The method assumes the input string (<code>str</code>) is a in-memory string without any
        * escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
        * <ul>
        *    <li>read from a P4GL source file with
        *       {@link character#parseProgressCharLiteral(String, boolean)}
        *       that will process all Progress escape encoding (used at conversion time, when the
        *       input is known and result is written as the <code>where</code> parameter of a
        *       <code>P2JQuery</code> in the java source file.
        *    <li>the value of a {@link character} P4GL variable, field or any
        *       other character expression (at runtime, as one of the arguments of the where clause
        *       of a <code>P2JQuery</code> in the java source file.
        * </ul>
        * <p>
        * The following conversions will be performed:
        * <table border='1' summary="">
        * <tr><th>P4GL</th><th>to</th><th>Notes</th></tr>
        * <tr><td>_</td><td>\_</td><td>SQL 'single character' wildcard must be escaped because it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>%</td><td>\%</td><td>SQL 'zero or more characters' wildcard must be escaped as it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>'</td><td>''</td><td>single quote is used to quote string literals in SQL. We double
        *    it in <code>inline</code> mode for correct encoding in SQL string literal.</td></tr>
        * <tr><td>\</td><td>\\ or escape</td><td>P4GL in Windows: normal character. In SQL backslash
        *    is used to remove other character's meaning. We double it for correct encoding in SQL
        *    string literal.<br>
        *    P4GL in Linux: escape character (like ~). If this is the first occurrence, then handle it
        *    as escape and the following character has no meaning in P4GL. If this character was
        *    already escaped, we double it for correct encoding in SQL string literal.</td></tr>
        * <tr><td>*</td><td>%</td><td>Conversion from P4GL to SQL of the 'zero or more characters'
        *    wildcard. Unless it was not escaped.</td></tr>
        * <tr><td>.</td><td>_</td><td>Conversion from P4GL to SQL of the 'single character'
        *    wildcard. Unless it was not escaped.</td></tr>
        * </table>
        * <p>
        * Depending on <code>inline</code> parameter, the resulting string is HQL/SQL escaped or not.
        * If the value is meant to be inlined at conversion time, the single quotes are automatically
        * doubled (sql escaped). Otherwise, the result can be further processed and will be SQL
        * escaped when inlined at runtime or automatically as query parameter.
        * <p>
        * If the method is called at conversion time and the result will be written to disk as a java
        * literal, it must be escaped accordingly (see {@link character#encodeToJavaSource(String)}).
        *
        * @param   pattern
        *          Progress 4GL matching pattern.
        * @param   windows
        *          how backslash should be handled (on Windows it's a normal character, in Linux it's
        *          an escape character similar to tilde (~))
        *
        * @return  the pattern converted SQL LIKE expression. Optionally SQL escaped.
        */
       public static character convertToSQLLike(character pattern, boolean windows)
       {
          if (pattern.isUnknown())
          {
             return new character();
          }

          return new character(convertToSQLLike(pattern.getValue(), windows, false));
       }

       /**
        * Converts a second operand of the "op1 MATCHES op2" function into corresponding SQL LIKE
        * expression. This processing is meant to be used ONLY for argument in the left-side of
        * a LIKE SQL operator in a WHERE clause.
        * <p>
        * The method assumes the input string (<code>str</code>) is a in-memory string without any
        * escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
        * <ul>
        *    <li>read from a P4GL source file with
        *       {@link character#parseProgressCharLiteral(String, boolean)}
        *       that will process all Progress escape encoding (used at conversion time, when the
        *       input is known and result is written as the <code>where</code> parameter of a
        *       <code>P2JQuery</code> in the java source file.
        *    <li>the value of a {@link character} P4GL variable, field or any
        *       other character expression (at runtime, as one of the arguments of the where clause
        *       of a <code>P2JQuery</code> in the java source file.
        * </ul>
        * <p>
        * The following conversions will be performed:
        * <table border='1' summary="">
        * <tr><th>P4GL</th><th>to</th><th>Notes</th></tr>
        * <tr><td>_</td><td>\_</td><td>SQL 'single character' wildcard must be escaped because it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>%</td><td>\%</td><td>SQL 'zero or more characters' wildcard must be escaped as it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>'</td><td>''</td><td>single quote is used to quote string literals in SQL. We double
        *    it in <code>inline</code> mode for correct encoding in SQL string literal.</td></tr>
        * <tr><td>\</td><td>\\ or escape</td><td>P4GL in Windows: normal character. In SQL backslash
        *    is used to remove other character's meaning. We double it for correct encoding in SQL
        *    string literal.<br>
        *    P4GL in Linux: escape character (like ~). If this is the first occurrence, then handle it
        *    as escape and the following character has no meaning in P4GL. If this character was
        *    already escaped, we double it for correct encoding in SQL string literal.</td></tr>
        * <tr><td>*</td><td>%</td><td>Conversion from P4GL to SQL of the 'zero or more characters'
        *    wildcard. Unless it was not escaped.</td></tr>
        * <tr><td>.</td><td>_</td><td>Conversion from P4GL to SQL of the 'single character'
        *    wildcard. Unless it was not escaped.</td></tr>
        * </table>
        * <p>
        * Depending on <code>inline</code> parameter, the resulting string is HQL/SQL escaped or not.
        * If the value is meant to be inlined at conversion time, the single quotes are automatically
        * doubled (sql escaped). Otherwise, the result can be further processed and will be SQL
        * escaped when inlined at runtime or automatically as query parameter.
        * <p>
        * If the method is called at conversion time and the result will be written to disk as a java
        * literal, it must be escaped accordingly (see {@link character#encodeToJavaSource(String)}).
        *
        * @param   pattern
        *          Progress 4GL matching pattern.
        * @param   windows
        *          how backslash should be handled (on Windows it's a normal character, in Linux it's
        *          an escape character similar to tilde (~))
        *
        * @return  the pattern converted SQL LIKE expression. Optionally SQL escaped.
        */
       public static character convertToSQLLike(BaseDataType pattern, boolean windows)
       {
          if (pattern.isUnknown())
          {
             return new character();
          }

          return new character(convertToSQLLike(pattern.toStringMessage(), windows, false));
       }

       /**
        * Converts a second operand of the "op1 MATCHES op2" function into corresponding SQL LIKE
        * expression. This processing is meant to be used ONLY for argument in the left-side of
        * a LIKE SQL operator in a WHERE clause.
        * <p>
        * The method assumes the input string (<code>str</code>) is a in-memory string without any
        * escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
        * <ul>
        *    <li>read from a P4GL source file with
        *       {@link character#parseProgressCharLiteral(String, boolean)}
        *       that will process all Progress escape encoding (used at conversion time, when the
        *       input is known and result is written as the <code>where</code> parameter of a
        *       <code>P2JQuery</code> in the java source file.
        *    <li>the value of a {@link character} P4GL variable, field or any
        *       other character expression (at runtime, as one of the arguments of the where clause
        *       of a <code>P2JQuery</code> in the java source file.
        * </ul>
        * <p>
        * The following conversions will be performed:
        * <table border='1' summary="">
        * <tr><th>P4GL</th><th>to</th><th>Notes</th></tr>
        * <tr><td>_</td><td>\_</td><td>SQL 'single character' wildcard must be escaped because it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>%</td><td>\%</td><td>SQL 'zero or more characters' wildcard must be escaped as it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>'</td><td>''</td><td>single quote is used to quote string literals in SQL. We double
        *    it in <code>inline</code> mode for correct encoding in SQL string literal.</td></tr>
        * <tr><td>\</td><td>\\ or escape</td><td>P4GL in Windows: normal character. In SQL backslash
        *    is used to remove other character's meaning. We double it for correct encoding in SQL
        *    string literal.<br>
        *    P4GL in Linux: escape character (like ~). If this is the first occurrence, then handle it
        *    as escape and the following character has no meaning in P4GL. If this character was
        *    already escaped, we double it for correct encoding in SQL string literal.</td></tr>
        * <tr><td>*</td><td>%</td><td>Conversion from P4GL to SQL of the 'zero or more characters'
        *    wildcard. Unless it was not escaped.</td></tr>
        * <tr><td>.</td><td>_</td><td>Conversion from P4GL to SQL of the 'single character'
        *    wildcard. Unless it was not escaped.</td></tr>
        * </table>
        * <p>
        * Depending on <code>inline</code> parameter, the resulting string is HQL/SQL escaped or not.
        * If the value is meant to be inlined at conversion time, the single quotes are automatically
        * doubled (sql escaped). Otherwise, the result can be further processed and will be SQL
        * escaped when inlined at runtime or automatically as query parameter.
        * <p>
        * If the method is called at conversion time and the result will be written to disk as a java
        * literal, it must be escaped accordingly (see {@link character#encodeToJavaSource(String)}).
        *
        * @param   pattern
        *          Progress 4GL matching pattern.
        * @param   windows
        *          how backslash should be handled (on Windows it's a normal character, in Linux it's
        *          an escape character similar to tilde (~))
        *
        * @return  the pattern converted SQL LIKE expression. Optionally SQL escaped.
        */
       public static String convertToSQLLike(String pattern, boolean windows, boolean inline)
       {
          StringBuilder sb = new StringBuilder("");
          int end = pattern.length();

          // convert the contents into the equivalent Java form
          for (int i = 0; i < end; i++)
          {
             char current = pattern.charAt(i);

             switch (current)
             {
                case '*':
                   sb.append("%");
                   break;
                case '.':
                   sb.append("_");
                   break;
                case '\'':
                   sb.append(inline ? "\'\'" : "\'");
                   break;
                case '%':
                   sb.append("\\%");
                   break;
                case '_':
                   sb.append("\\_");
                   break;
                case '\\':
                   if (windows)
                   {
                      //sb.append(current);
                      sb.append("\\\\");
                      break;
                   }
                   // else fall trough next case '~', on non-windows \ is also escape character
                case '~':
                   // There must always be a following character that will be escaped.
                   // Note that the Progress Language escaping chars were already replaced.
                   // This is only an escape for MATCHES operator wildcard so that the next
                   // character has no special meaning. In Progress, if the next was already
                   // a normal character, the escape char is simply discarded.
                   char next = pattern.charAt(i + 1);
                   // look ahead one character and if must be processed now, consume it (i++)
                   switch (next)
                   {
                      case '*':
                      case '.':
                         // when escaped, they have no special meaning in Progress
                         i++;
                         sb.append(next);
                         break;

                         // we dropped its Progress meaning, but it must be hql escaped
                      case '\\':
                         i++;
                         sb.append("\\\\");
                         break;

                      default:
                         // any other character will be processed in next iteration, SQL escaping them
                         // if they have a special role in SQL.
                         break;
                   }
                   break;
                default:
                   // not an escaped char or special case
                   sb.append(current);
             }
          }

          return sb.toString();
       }

       /**
        * Converts a second operand of the "op1 BEGINS op2" function into corresponding SQL LIKE
        * expression. This processing is meant to be used ONLY for argument in the left-side of
        * a LIKE SQL operator in a WHERE clause.
        * <p>
        * The method assumes the input string (<code>str</code>) is a in-memory string without any
        * escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
        * <ul>
        *    <li>read from a P4GL source file with
        *       {@link character#parseProgressCharLiteral(String, boolean)} that will process all
        *       Progress escape encoding (used at conversion time, when the input is known and result
        *       is written as the <code>where</code> parameter of a <code>P2JQuery</code> in the java
        *       source file.
        *    <li>the value of a {@link character} P4GL variable, field or any
        *       other character expression (at runtime, as one of the arguments of the where clause
        *       of a <code>P2JQuery</code> in the java source file.
        * </ul>
        * <p>
        * The following conversions will be performed:
        * <table border='1' summary="">
        * <tr><th>P4GL</th><th>to</th><th>Notes</th></tr>
        * <tr><td>_</td><td>\_</td><td>SQL 'single character' wildcard must be escaped because it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>%</td><td>\%</td><td>SQL 'zero or more characters' wildcard must be escaped as it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>'</td><td>''</td><td>single quote is used to quote string literals in SQL. We double
        *    it in <code>inline</code> mode for correct encoding in SQL string literal.</td></tr>
        * <tr><td>\</td><td>\\</td><td>in SQL backslash is used to escape other character's meaning.
        *    We double it for correct encoding in SQL string literal.</td></tr>
        * </table>
        * <p>
        * To implement the begin semantic, a 'zero or more characters' wildcard (%) will be appended
        * to the end of the returned string.
        * <p>
        * Depending on <code>inline</code> parameter, the resulting string is HQL/SQL escaped or not.
        * If the value is meant to be inlined at conversion time, the single quotes are automatically
        * doubled (sql escaped). Otherwise, the result can be further processed and will be SQL
        * escaped when inlined at runtime or automatically as query parameter.
        * <p>
        * If the method is called at conversion time and the result will be written to disk as a java
        * literal, it must be escaped accordingly (see {@link character#encodeToJavaSource(String)}).
        *
        * @param   str
        *          Represents a second operand of the "op1 BEGINS op2" function.
        *
        * @return  the pattern converted SQL LIKE expression. Optionally SQL escaped.
        */
       public static character convertToSQLBegins(character str)
       {
          if (str.isUnknown())
          {
             return new character();
          }

          return new character(convertToSQLBegins(str.getValue(), false));
       }

       /**
        * Converts a second operand of the "op1 BEGINS op2" function into corresponding SQL LIKE
        * expression. This processing is meant to be used ONLY for argument in the left-side of
        * a LIKE SQL operator in a WHERE clause.
        * <p>
        * The method assumes the input string (<code>str</code>) is a in-memory string without any
        * escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
        * <ul>
        *    <li>read from a P4GL source file with
        *       {@link character#parseProgressCharLiteral(String, boolean)} that will process all
        *       Progress escape encoding (used at conversion time, when the input is known and result
        *       is written as the <code>where</code> parameter of a <code>P2JQuery</code> in the java
        *       source file.
        *    <li>the value of a {@link character} P4GL variable, field or any
        *       other character expression (at runtime, as one of the arguments of the where clause
        *       of a <code>P2JQuery</code> in the java source file.
        * </ul>
        * <p>
        * The following conversions will be performed:
        * <table border='1' summary="">
        * <tr><th>P4GL</th><th>to</th><th>Notes</th></tr>
        * <tr><td>_</td><td>\_</td><td>SQL 'single character' wildcard must be escaped because it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>%</td><td>\%</td><td>SQL 'zero or more characters' wildcard must be escaped as it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>'</td><td>''</td><td>single quote is used to quote string literals in SQL. We double
        *    it in <code>inline</code> mode for correct encoding in SQL string literal.</td></tr>
        * <tr><td>\</td><td>\\</td><td>in SQL backslash is used to escape other character's meaning.
        *    We double it for correct encoding in SQL string literal.</td></tr>
        * </table>
        * <p>
        * To implement the begin semantic, a 'zero or more characters' wildcard (%) will be appended
        * to the end of the returned string.
        * <p>
        * Depending on <code>inline</code> parameter, the resulting string is HQL/SQL escaped or not.
        * If the value is meant to be inlined at conversion time, the single quotes are automatically
        * doubled (sql escaped). Otherwise, the result can be further processed and will be SQL
        * escaped when inlined at runtime or automatically as query parameter.
        * <p>
        * If the method is called at conversion time and the result will be written to disk as a java
        * literal, it must be escaped accordingly (see {@link character#encodeToJavaSource(String)}).
        *
        * @param   str
        *          Represents a second operand of the "op1 BEGINS op2" function.
        *
        * @return  the pattern converted SQL LIKE expression. Optionally SQL escaped.
        */
       public static character convertToSQLBegins(BaseDataType str)
       {
          if (str.isUnknown())
          {
             return new character();
          }

          return new character(convertToSQLBegins(str.toStringMessage(), false));
       }

       /**
        * Converts a second operand of the "op1 BEGINS op2" function into corresponding SQL LIKE
        * expression. This processing is meant to be used ONLY for argument in the left-side of
        * a LIKE SQL operator in a WHERE clause.
        * <p>
        * The method assumes the input string (<code>str</code>) is a in-memory string without any
        * escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
        * <ul>
        *    <li>read from a P4GL source file with
        *       {@link character#parseProgressCharLiteral(String, boolean)} that will process all
        *       Progress escape encoding (used at conversion time, when the input is known and result
        *       is written as the <code>where</code> parameter of a <code>P2JQuery</code> in the java
        *       source file.
        *    <li>the value of a {@link character} P4GL variable, field or any
        *       other character expression (at runtime, as one of the arguments of the where clause
        *       of a <code>P2JQuery</code> in the java source file.
        * </ul>
        * <p>
        * The following conversions will be performed:
        * <table border='1' summary="">
        * <tr><th>P4GL</th><th>to</th><th>Notes</th></tr>
        * <tr><td>_</td><td>\_</td><td>SQL 'single character' wildcard must be escaped because it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>%</td><td>\%</td><td>SQL 'zero or more characters' wildcard must be escaped as it
        *    does not have any meaning in P4GL</td></tr>
        * <tr><td>'</td><td>''</td><td>single quote is used to quote string literals in SQL. We double
        *    it in <code>inline</code> mode for correct encoding in SQL string literal.</td></tr>
        * <tr><td>\</td><td>\\</td><td>in SQL backslash is used to escape other character's meaning.
        *    We double it for correct encoding in SQL string literal.</td></tr>
        * </table>
        * <p>
        * To implement the begin semantic, a 'zero or more characters' wildcard (%) will be appended
        * to the end of the returned string.
        * <p>
        * Depending on <code>inline</code> parameter, the resulting string is HQL/SQL escaped or not.
        * If the value is meant to be inlined at conversion time, the single quotes are automatically
        * doubled (sql escaped). Otherwise, the result can be further processed and will be SQL
        * escaped when inlined at runtime or automatically as query parameter.
        * <p>
        * If the method is called at conversion time and the result will be written to disk as a java
        * literal, it must be escaped accordingly (see {@link character#encodeToJavaSource(String)}).
        *
        * @param   str
        *          Represents a second operand of the "op1 BEGINS op2" function.
        *
        * @return  the pattern converted SQL LIKE expression. Optionally SQL escaped.
        */
       public static String convertToSQLBegins(String str, boolean inline)
       {
          StringBuilder sb = new StringBuilder("");
          int end = str.length();

          for (int i = 0; i < end; i++)
          {
             char current = str.charAt(i);

             switch (current)
             {
                case '%':
                   sb.append("\\%");
                   break;
                case '_':
                   sb.append("\\_");
                   break;
                case '\'':
                   sb.append(inline ? "\'\'" : "\'");
                   break;
                case '\\':
                   sb.append("\\\\");
                   break;
                default:
                   sb.append(current);
             }
          }
          sb.append("%"); // beginning pattern can be followed by anything
          return sb.toString();
       }

       /**
        * Converts a second operand of the "op1 CONTAINS op2" function into corresponding SQL LIKE
        * expression.
        *
        * Progress syntax for op2 is some kind of recursive expression like:
        * &lt;expr&gt;     := word | word* | (&lt;or-expr&gt;)
        * &lt;or-expr&gt;  := &lt;and-expr&gt; ( &lt;or-op&gt; &lt;and-expr&gt; )*
        * &lt;and-expr&gt; := &lt;expr&gt; (&lt;and-op&gt; &lt;expr&gt;)*
        * &lt;or-op&gt;    is one of: "^" "|" "!"
        * &lt;and-op&gt;   = "&amp;"
        *
        * @param  str
        *         Represents a second operand of the "op1 CONTAINS op2" clause.
        *
        * @return The corresponding SQL LIKE expression.
        */
       public static character convertToSQLContains(character str)
       {
          if (str.isUnknown())
          {
             return new character();
          }

          return new character(convertToSQLContains(str.getValue(), false));
       }

       /**
        * Converts a second operand of the "op1 CONTAINS op2" function into
        * corresponding SQL LIKE expression.
        * <p>
        * Progress syntax for op2 is some kind of recursive expression like:
        * <pre>
        * &lt;expr&gt;     := word | word"*" | "(" &lt;or-expr&gt; ")"
        * &lt;or-expr&gt;  := &lt;and-expr&gt; ( &lt;or-op&gt; &lt;and-expr&gt; )*
        * &lt;and-expr&gt; := &lt;expr&gt; (&lt;and-op&gt; &lt;expr&gt;)*
        * &lt;or-op&gt;    is one of: "^" "|" "!"
        * &lt;and-op&gt;   = "&amp;"
        * </pre>
        * Spaces are not allowed.<p>
        * However, the DIRTY method only does the following: <ul>
        * <li>looks for words
        * <li>removes * from the end, if any
        * <li>replaces special characters (see {@link #convertToSQLBegins(String, boolean)})
        * <li>replaces the word with %word% in the original text
        * </ul>
        * This way, if no fancy expression is used the correct query is generated. Any more complex
        * expression cannot be converted without recursive processing of the expression using the
        * above definition.
        * <p>
        * A better algorithm would convert a clause like:
        * <pre>SomeField CONTAINS "word1&amp;(word2*|word3)</pre>
        * into the more complex SQL form:
        * <pre>
        * ((SomeField LIKE "%word1%) AND ((SomeField LIKE "%word2%) OR (SomeField LIKE "%word3%)))
        * </pre>
        * The correct implementation has to use regular expressions together with SQL clause
        * <code>SIMILAR TO</code>. However, I am not aware of how and if hibernate supports it.
        *
        * @param  str
        *         Represents a second operand of the "op1 CONTAINS op2" clause.
        *
        * @return A substitution SQL LIKE expression.
        */
       public static String convertToSQLContains(String str, boolean inline)
       {
          StringBuilder sb = new StringBuilder();
          Matcher m = SQL_CONTAINS.matcher(str);
          String word;
          int wordStart = 0;
          while (m.find())
          {
             char tok = m.group().charAt(0); // all tokens are exactly 1 char long
             int beg = m.start();
             int end = m.end();

             word = str.substring(wordStart, beg).trim();
             if (!word.isEmpty())
             {
                if (word.endsWith("*"))
                {
                   word = word.substring(0,  word.length() - 1);
                }
                sb.append('%');
                sb.append(convertToSQLBegins(word, inline)); // TODO how to handle this ?
             }
             if (tok == '(' || tok == ')')
             {
                sb.append(tok);
             }
             else
             {
                sb.append(' ').append(tok).append(' ');
             }
             wordStart = end;
          }

          word = str.substring(wordStart).trim();
          if (!word.isEmpty())
          {
             if (word.endsWith("*"))
             {
                word = word.substring(0,  word.length() - 1);
             }
             sb.append('%');
             sb.append(convertToSQLBegins(word, inline)); // TODO how to handle this ?
          }

          return sb.toString();
       }

       /**
        * Converts a string (from a Progress source file) that represents a Progress 4GL matching
        * "pattern" to a traditional regular expression which is suitable for a Java source file.
        * This allows a trivial implementation of the matches operator using the
        * <code>String.matches</code> method.
        * <p>
        * In Progress 4GL, there are 2 escape characters '~' (tilde) and '\' backslash. The latter is
        * only used in Unix OS, while in windows it does not have any special meaning.
        * <p>
        * In both Java and in regular expressions, the only escape character is '\'. In addition,
        * since these strings come directly from source files, the Progress runtime has not handled
        * the escape sequences on input as it normally would. So although the sequences '~*' or '\*'
        * are normally the literal asterisk, the string must be coded to appear as '~~*' or '\\*' in
        * the source because the escape chars are normally dropped on input. This method is written
        * assuming that all of the normal Progress conversions have already occurred by utilizing
        * {@link com.goldencode.p2j.util.character#progressToJavaString} and
        * {@link com.goldencode.util.StringHelper#processEscapes}.
        * <p>
        * Once the standard Progress conversions have occurred, then this method can be used and the
        * following conversions will be additionally handled:
        * <pre>
        *    Progress                Regular Expression
        *    --------                ------------------
        *    ~.                      \\.
        *    \.                      \\.
        *    ~*                      \\*
        *    \*                      \\*
        *    ~~                      ~
        *    \~                      ~
        *    ~\                      \\\\
        *    \\                      \\\\
        *    *                       .*
        *    ~                       (removed or converted to \\ if the following char requires)
        *    \                       (removed or converted to \\ if the following char requires)
        * </pre>
        * <p>
        * The following characters in Progress have special meaning in the extended regular expression
        * syntax used in Java. Each of these must be properly escaped to ensure that their meaning is
        * not accidentally invoked by an input pattern that expects it to be matched literally:
        * <p>
        * <pre>
        *    Progress                Extended Regular Expression
        *    --------                ------------------
        *    ?                       \\\\?
        *    +                       \\\\+
        *    ^                       \\\\^
        *    $                       \\\\$
        *    |                       \\\\|
        *    (                       \\\\(
        *    )                       \\\\)
        *    [                       \\\\[
        *    ]                       \\\\]
        *    {                       \\\\{
        *    }                       \\\\}
        * </pre>
        * The following special Progress pattern constructs actually match those of regular
        * expressions and for this reason, these constructs are left alone (not disturbed or
        * modified):
        * <pre>
        *    Progress                Regular Expression
        *    --------                ------------------
        *    .                       .
        * </pre>
        * This conversion seems trivial, except that the backslash '\' character must be handled
        * properly since it needs to be doubled up for Java to recognise one in a Java string and if
        * two are needed in the regular expression then one needs to double them up, twice. This means
        * that in order to match a single \ in a regular expression, the Java string will need \\\\
        * (4 backslashes). In addition, since the asterisk '*' character is a wildcard (it matches
        * any number of any character) in Progress, it must be properly replaced in the regular
        * expression (where .* is the unlimited wildcard). Both Progress and regular expressions use
        * '.' to mean match any single character.
        *
        * @param   pattern
        *          Progress 4GL matching pattern.
        * @param   windows
        *          How the backslashes will be treated. On Unix, <code>windows = false</code>,
        *          they are escape characters that remove special meaning of other characters
        *          (same like tilde ~). On Windows, backslashes are normal characters, the tilde is
        *          the only escape character.
        *
        * @return  converted regular expression.
        */
       public static String convertToRegEx(String pattern, boolean windows)
       {
          StringBuilder sb = new StringBuilder("");
          int end = pattern.length();

          // convert the contents into the equivalent Java form
          for (int i = 0; i < end; i++)
          {
             char current = pattern.charAt(i);
             char next;

             switch (current)
             {
                case '*':
                   sb.append(".*");
                   break;

                case '\\':
                   if (windows)
                   {
                      // escape it
                      sb.append("\\\\");
                      break;
                   }
                   // else, the backslash is treated the same like tilde, fall trough

                case '~':
                   // there must always be a following character
                   next = pattern.charAt(i + 1);

                   if (next == '.' || next == '*' || next == '\\')
                   {
                      // these have special meanings in P4GL that the escape character will remove
                      // so they must be handled as normal chars in regexp. Since they have also
                      // special meaning in regexp, they must be escaped
                      sb.append('\\').append(next);
                      ++ i;
                   }

                   // all the other characters that were not escaped have no special meaning in p4gl
                   // so they are just processed in next iteration
                   break;

                default:
                   // certain chars need to have an escape inserted because they have no special
                   // meaning in Progress but do have a special meaning in Java's extended regular
                   // expression syntax
                   if (current == '?' || current == '+' || current == '^' ||
                       current == '$' || current == '|' || current == '(' ||
                       current == ')' || current == '{' || current == '}' ||
                       current == '[' || current == ']')
                   {
                      // insert a leading escape char
                      sb.append("\\");
                   }

                   // not an escaped char or special case
                   sb.append(current);
             }
          }

          return sb.toString();
       }
    }