public class SQLHelper
extends java.lang.Object
WHERE
clauses.
All convertToSQL___
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 MATCHES
and CONTAINS
that will be
converted to their respective SQL equivalents.
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
character.encodeToJavaSource(java.lang.String)
.
If the literals to be executed by inlined queries on server side, convertToSQL___
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.
Constructor and Description |
---|
SQLHelper() |
Modifier and Type | Method and Description |
---|---|
static java.lang.String |
convertToRegEx(java.lang.String pattern,
boolean windows)
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.
|
static character |
convertToSQLBegins(character str)
Converts a second operand of the "op1 BEGINS op2" function into corresponding SQL LIKE
expression.
|
static java.lang.String |
convertToSQLBegins(java.lang.String str,
boolean inline)
Converts a second operand of the "op1 BEGINS op2" function into corresponding SQL LIKE
expression.
|
static character |
convertToSQLContains(character str)
Converts a second operand of the "op1 CONTAINS op2" function into corresponding SQL LIKE
expression.
|
static java.lang.String |
convertToSQLContains(java.lang.String str,
boolean inline)
Converts a second operand of the "op1 CONTAINS op2" function into
corresponding SQL LIKE expression.
|
static character |
convertToSQLLike(character pattern,
boolean windows)
Converts a second operand of the "op1 MATCHES op2" function into corresponding SQL LIKE
expression.
|
static java.lang.String |
convertToSQLLike(java.lang.String pattern,
boolean windows,
boolean inline)
Converts a second operand of the "op1 MATCHES op2" function into corresponding SQL LIKE
expression.
|
public static character convertToSQLLike(character pattern, boolean windows)
The method assumes the input string (str
) is a in-memory string without any
escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
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 where
parameter of a
P2JQuery
in the java source file.
character
P4GL variable, field or any
other character expression (at runtime, as one of the arguments of the where clause
of a P2JQuery
in the java source file.
The following conversions will be performed:
P4GL | to | Notes |
---|---|---|
_ | \_ | SQL 'single character' wildcard must be escaped because it does not have any meaning in P4GL |
% | \% | SQL 'zero or more characters' wildcard must be escaped as it does not have any meaning in P4GL |
' | '' | single quote is used to quote string literals in SQL. We double
it in inline mode for correct encoding in SQL string literal. |
\ | \\ or escape | 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. 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. |
* | % | Conversion from P4GL to SQL of the 'zero or more characters' wildcard. Unless it was not escaped. |
. | _ | Conversion from P4GL to SQL of the 'single character' wildcard. Unless it was not escaped. |
Depending on inline
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.
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 character.encodeToJavaSource(String)
).
pattern
- Progress 4GL matching pattern.windows
- how backslash should be handled (on Windows it's a normal character, in Linux it's
an escape character similar to tilde (~))public static java.lang.String convertToSQLLike(java.lang.String pattern, boolean windows, boolean inline)
The method assumes the input string (str
) is a in-memory string without any
escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
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 where
parameter of a
P2JQuery
in the java source file.
character
P4GL variable, field or any
other character expression (at runtime, as one of the arguments of the where clause
of a P2JQuery
in the java source file.
The following conversions will be performed:
P4GL | to | Notes |
---|---|---|
_ | \_ | SQL 'single character' wildcard must be escaped because it does not have any meaning in P4GL |
% | \% | SQL 'zero or more characters' wildcard must be escaped as it does not have any meaning in P4GL |
' | '' | single quote is used to quote string literals in SQL. We double
it in inline mode for correct encoding in SQL string literal. |
\ | \\ or escape | 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. 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. |
* | % | Conversion from P4GL to SQL of the 'zero or more characters' wildcard. Unless it was not escaped. |
. | _ | Conversion from P4GL to SQL of the 'single character' wildcard. Unless it was not escaped. |
Depending on inline
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.
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 character.encodeToJavaSource(String)
).
pattern
- Progress 4GL matching pattern.windows
- how backslash should be handled (on Windows it's a normal character, in Linux it's
an escape character similar to tilde (~))public static character convertToSQLBegins(character str)
The method assumes the input string (str
) is a in-memory string without any
escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
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 where
parameter of a P2JQuery
in the java
source file.
character
P4GL variable, field or any
other character expression (at runtime, as one of the arguments of the where clause
of a P2JQuery
in the java source file.
The following conversions will be performed:
P4GL | to | Notes |
---|---|---|
_ | \_ | SQL 'single character' wildcard must be escaped because it does not have any meaning in P4GL |
% | \% | SQL 'zero or more characters' wildcard must be escaped as it does not have any meaning in P4GL |
' | '' | single quote is used to quote string literals in SQL. We double
it in inline mode for correct encoding in SQL string literal. |
\ | \\ | in SQL backslash is used to escape other character's meaning. We double it for correct encoding in SQL string literal. |
To implement the begin semantic, a 'zero or more characters' wildcard (%) will be appended to the end of the returned string.
Depending on inline
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.
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 character.encodeToJavaSource(String)
).
str
- Represents a second operand of the "op1 BEGINS op2" function.public static java.lang.String convertToSQLBegins(java.lang.String str, boolean inline)
The method assumes the input string (str
) is a in-memory string without any
escapes (neither of Progress, HQL/SQL or Java). The string can be obtained form either of:
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 where
parameter of a P2JQuery
in the java
source file.
character
P4GL variable, field or any
other character expression (at runtime, as one of the arguments of the where clause
of a P2JQuery
in the java source file.
The following conversions will be performed:
P4GL | to | Notes |
---|---|---|
_ | \_ | SQL 'single character' wildcard must be escaped because it does not have any meaning in P4GL |
% | \% | SQL 'zero or more characters' wildcard must be escaped as it does not have any meaning in P4GL |
' | '' | single quote is used to quote string literals in SQL. We double
it in inline mode for correct encoding in SQL string literal. |
\ | \\ | in SQL backslash is used to escape other character's meaning. We double it for correct encoding in SQL string literal. |
To implement the begin semantic, a 'zero or more characters' wildcard (%) will be appended to the end of the returned string.
Depending on inline
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.
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 character.encodeToJavaSource(String)
).
str
- Represents a second operand of the "op1 BEGINS op2" function.public static character convertToSQLContains(character str)
str
- Represents a second operand of the "op1 CONTAINS op2" clause.public static java.lang.String convertToSQLContains(java.lang.String str, boolean inline)
Progress syntax for op2 is some kind of recursive expression like:
<expr> := word | word"*" | "(" <or-expr> ")" <or-expr> := <and-expr> ( <or-op> <and-expr> )* <and-expr> := <expr> (<and-op> <expr>)* <or-op> is one of: "^" "|" "!" <and-op> = "&"Spaces are not allowed.
However, the DIRTY method only does the following:
convertToSQLBegins(String, boolean)
)
A better algorithm would convert a clause like:
SomeField CONTAINS "word1&(word2*|word3)into the more complex SQL form:
((SomeField LIKE "%word1%) AND ((SomeField LIKE "%word2%) OR (SomeField LIKE "%word3%)))The correct implementation has to use regular expressions together with SQL clause
SIMILAR TO
. However, I am not aware of how and if hibernate supports it.str
- Represents a second operand of the "op1 CONTAINS op2" clause.public static java.lang.String convertToRegEx(java.lang.String pattern, boolean windows)
String.matches
method.
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.
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
character.progressToJavaString(java.lang.String)
and
StringHelper.processEscapes(java.lang.String)
.
Once the standard Progress conversions have occurred, then this method can be used and the following conversions will be additionally handled:
Progress Regular Expression -------- ------------------ ~. \\. \. \\. ~* \\* \* \\* ~~ ~ \~ ~ ~\ \\\\ \\ \\\\ * .* ~ (removed or converted to \\ if the following char requires) \ (removed or converted to \\ if the following char requires)
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:
Progress Extended Regular Expression -------- ------------------ ? \\\\? + \\\\+ ^ \\\\^ $ \\\\$ | \\\\| ( \\\\( ) \\\\) [ \\\\[ ] \\\\] { \\\\{ } \\\\}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):
Progress Regular Expression -------- ------------------ . .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.
pattern
- Progress 4GL matching pattern.windows
- How the backslashes will be treated. On Unix, windows = false
,
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.