Record Selection Criteria¶
- Record Selection Criteria
- WHERE Clause Life Cycle
- Alias Naming
- Implicit WHERE Clause Components
- Extraction Of Substitution Parameters
- Comparison Operators and Literals
- Operators and Functions
- Character Operands in a WHERE Clause
- ROWID and RECID Functions
- Ternary IF Operator
- Conversion-Time Optimizations/Adjustments
- Extents
- CAN-FIND as a Part of Where Clause
- Client-Side Filtering
Progress 4GL database access language statements (e.g., CAN-FIND, FIND, FOR, [DO/REPEAT] PRESELECT, or OPEN QUERY) offer a number of ways to describe the selection criteria to be applied when retrieving records from the database. The most straightforward of these is the WHERE clause, which specifies comparison or matching criteria for particular fields of a database record. In addition, statement options such as OF or USING are used to restrict record selection, as is the use of constants which uniquely identify records within the context of a table's primary index.
The conversion of these restriction criteria to a form usable in a FWD-enabled Java application presents special challenges. This chapter discusses the details of that conversion, a process which generally is independent of the 4GL language statement within which the criteria are specified. For further information about the database access language statements themselves and the types of database queries used to represent them in converted code, please refer to the Queries chapter of this reference.
WHERE Clause Life Cycle¶
The 4GL WHERE clause is a commonly used form of expressing database record selection criteria. It sometimes is used in combination with other options, such as OF and USING. The life cycle of the WHERE clause can be represented as follows:
- For the
CAN-FIND,FIND,FOR,OPEN QUERYand[DO/REPEAT] PRESELECTstatements an explicitWHEREclause or an implicit record selection criterion (like theOFandUSINGoption) is converted to an HQL (Hibernate Query Language) where clause:- Implicit record selection criteria are translated to an explicit form that can be included in the converted where clause.
WHEREclause is optimized.- Query substitution parameters are extracted.
- The converted HQL where clause is passed to the constructor of a FWD query (see the Queries chapter) which represents the converted statement (if it is one of the
CAN-FIND,FINDorOPEN QUERYstatements) or to the query which backs the converted block (if it isFORorDO/REPEAT PRESELECT). Some example 4GL code for the direct query case follows (note: comments in the converted examples below are added for instructional purposes):
Example 1:
def var bid as integer. find book where book.book-id = bid.
Converted code:
new FindQuery(book,
"book.bookId = ?", /* WHERE clause. */
null,
"book.bookId asc",
new Object[]
{
bid /* Substitution parameter. */
}).unique();
Details:
Note how the selection criteria is split in two parts in converted code: a string containing the converted WHERE clause in HQL form (i.e., the second parameter passed to the FindQuery constructor, where the bid variable is replaced by a substitution parameter (?); and the value for this substitution parameter (i.e., the fifth parameter to the FindQuery constructor: an Object array containing the bid variable).
Example 2:
def var price-limit as decimal. for each book where book.price < price-limit: ... end.
Converted code:
forEach("loopLabel0", new Block()
{
AdaptiveQuery query0 = null; /* Query which backs the FOR cycle. */
public void init()
{
query0 = new AdaptiveQuery(book,
"book.price < ?", /* WHERE clause. */
null,
"book.bookId asc",
new Object[]
{
priceLimit /* Substitution parameter. */
});
}
public void body()
{
query0.next();
...
}
});
Details:
Similar to the previous example, the 4GL WHERE clause was converted to its HQL representation and the price-limit variable was replaced by a substitution parameter.
- Before the query is executed the following actions are performed:
- Values of the substitution parameters are evaluated.
- Based on the evaluated values of the substitution parameters, the HQL where clause is optimized.
- Parts of where clause required for query iteration are appended. For example, if we are performing dynamic search of the next book record along the specified index (which is represented by a sort clause in FWD) “
book.bookId”, then in order to find the book we should append “book.bookId > ?” to the where clause, where “?” is a substitution parameter placeholder, which represents thebookIdof the current book. - Joins are performed by appending foreign key filters to the where clause.
- Where clause may be cached for future use.
- The query is executed using Hibernate API.
Alias Naming¶
In 4GL, fields can be used in a WHERE clause without specifying the buffer name to which this field belongs, but this aspect does not survive the conversion process: during conversion, all implicit fields will be resolved depending on which buffer is in scope (see the Record Buffers chapter of this book for details about how the implicit fields are resolved).
After conversion, the converted buffer name (which can be based on the the physical table name or the name of the explicitly defined buffer) will be used as an alias in the converted WHERE clause. This alias is specified in the converted code in the section where the buffer instance is created.
Example 1:
find first book where book.cost > 0.
Converted code:
Book book = RecordBuffer.define(Book.class,
"p2j_test",
"book"); /* The alias for this record. */
...
new FindQuery(book,
"book.cost > 0", /* The form alias.property is used. */
null,
"book.bookId asc").first();
Details:
The buffer definition in the converted code will take as parameter - in addition to the DMO implementation class and the database name - the buffer's alias, which will be used in the converted WHERE clause (and also in the sort criteria). This means that any property for this DMO must be prefixed with the alias name (book in this case), whenever it is used in the converted HQL where and order by clauses.
While in 4GL, explicit buffer specifications can be omitted, after conversion the explicit record aliases will be specified.
Example 2:
find first book where price < 100. /* It is implicitly assumed that the price is a field
of the book record. */
Converted code:
Book book = RecordBuffer.define(Book.class,
"p2j_test",
"book"); /* The alias for this record. */
...
new FindQuery(book,
"book.price < 100", /* Alias “book” is explicitly specified. */
null,
"book.bookId asc").first();
Details:
In this case, even if the price field is not prefixed by the buffer name, the 4GL runtime will determine the buffer based on which buffers are active in this scope (book in this case). The converted code always prefixes the field name with the buffer name, regardless of whether the prefix was used in the original, 4GL code. The book alias is set at buffer instantiation (i.e., within the RecordBuffer.define method call) and it is used in both the where and order by HQL clauses.
Implicit WHERE Clause Components¶
Implicit WHERE clause components are special 4GL constructs which enable certain 4GL data access statements to use a shorthand syntax to retrieve records. Although implicit WHERE clause components have explicit, “longhand” WHERE clause equivalents which can be used instead, when implicit components are used, the 4GL automatically will expand the WHERE clause internally to include them as selection criteria.
An equivalent expansion is performed during the conversion process: FWD rules gather all the implicit record selection criteria and translate them to an explicit form which are either integrated into the HQL where clause, or which are passed in as additional parameters when query objects are constructed. The next section will present the different kinds of implicit record selection criteria.
Unqualified Constant¶
A FIND statement with a single literal value after the record phrase is the same as making an addition to the WHERE clause referencing a primary unique index in an equality match with the specified literal. The constraint is that this constant can be used only with buffers for which the primary key contains only a single field.
Example 1:
find book 1 where price < 100.
Converted code:
new FindQuery(book,
"book.price < 100 and book.bookId = 1",
null,
"book.bookId asc").unique();
Details:
Assuming the book table has a primary key with only a single-component book-id, the literal 1 will be converted to the book.bookId = 1 selection criteria and this will be appended to the existing WHERE clause.
OF Clause¶
The OF clause is a 4GL mechanism to retrieve records from a table which has an implicit association with another table. This association is determined at compile time and requires that:
- there must be at least one field which has the same name and data type in both tables;
- the common field(s) must participate in a unique index in at least one of the tables; and
- only one such relationship (as defined by the previous two points) may exist between the two tables.
Once it is determined that such a relationship exists, the 4GL will append to the WHERE clause equality matches between these matching fields, which are connected to the explicit WHERE clause via AND operators.
Example 2:
find first pers-addr of person where pers-addr.active = true.
Converted code:
new FindQuery(persAddr,
"persAddr.active = true", /* WHERE clause left as is. */
null,
"persAddr.active asc",
person /* The joined buffer referenced within the OF clause. */
).first();
Details:
Here we need to consider that both pers-addr and person tables have a common index which contains the two fields site-id and emp-num, and that the index is unique on the person table. The FWD conversion process detects this association and leaves information behind in the dmo_index.xml file (see the DMO Index chapter for additional detail). This information becomes part of and is loaded with the converted application. Thus, in FWD the transformations which take these implicit selection criteria into account are performed implicitly by the FWD runtime, i.e., the where clause isn't changed during conversion to reflect the implicit association between the tables. Rather, the joined buffer (person in this case) is specified along with the other query parameters.
Example 3:
find first pers-addr where pers-addr.active = true and
pers-addr.side-id = person.side-id and
pers-addr.emp-num = person.emp-num.
Converted code:
new FindQuery(persAddr,
"persAddr.active = true and persAddr.siteId = ? and persAddr.empNum = ?",
null,
"persAddr.active asc",
new Object[]
{
person.getSiteId(),
person.getEmpNum()
}
).first();
Details:
Even if this 4GL code is functionally equivalent to the one in the previous example, the converted code is not the same. As the join is done explicitly in the WHERE clause, the HQL where clause will contain these filters too.
USING clause¶
The USING clause causes an addition of an equality match between each referenced buffer field and the value currently held by its associated form field, in the default screen buffer. These equality matches are connected via an AND operator.
Example 4:
prompt-for book.book-id book.author-id with frame f. find first book where book.price < 100 using book.book-id and book.author-id.
is equivalent to:
prompt-for book.book-id book.author-id with frame f.
find first book where book.price < 100 and
book.book-id = input book.book-id and /* Compare fields with their screen
book.author-id = input book.author-id. buffer values. */
Converted code:
FrameElement[] elementList0 = new FrameElement[]
{
new Element(new FieldReference(book, "bookId"), fFrame.widgetBookId()),
new Element(new FieldReference(book, "authorId"), fFrame.widgetAuthorId())
};
fFrame.promptFor(elementList0);
RecordBuffer.prepare(book);
new FindQuery(book,
"book.price < 100 and book.bookId = ? and book.authorId = ?",
null,
"book.bookId asc", new Object[]
{
fFrame.getBookId(), /* Screen buffer field values. */
fFrame.getAuthorId()
}).first();
Details:
In this case, the two 4GL code versions will convert the same - this is because the idioms require the retrieval of the field values as they appear in the screen buffer for the specified frame, which can't be done implicitly by FWD. Rather, the screen buffer values are extracted during construction of the FindQuery object and are passed in as query substitution values.
Direct Logical Field Reference¶
A logical field that is directly referenced as the entire record selection criterion or as a direct operand of a logical operator is actually considered to be an equality comparison with the literal true. If the logical field is inverted by the NOT operator it is considered to be an equality comparison with the literal false. Note that this does not necessarily reproduce 4GL behavior.
Example 5:
find first pers-addr where pers-addr.active.
Converted code:
new FindQuery(persAddr,
"persAddr.active = true",
null,
"persAddr.active asc").first();
Details:
The pers-addr.active is a logical field used without a match in the 4GL code. During conversion, the field will automatically be matched against the true literal.
Example 6:
find first pers-addr where not pers-addr.active.
Converted code:
new FindQuery(persAddr,
"persAddr.active = false",
null,
"persAddr.active asc").first();
Details:
The pers-addr.active is a logical field negated the 4GL code. During conversion, the field will automatically be matched against the false literal.
Extraction Of Substitution Parameters¶
FWD tries to minimize the number of substitution parameters as much as possible. The conversion rules determine the cases when the expression evaluates to a constant value and will replace the entire expression with a substitution parameter. This is done because there is no reason to replace only the variable or other field references with substitution parameters, as the underlying database server may need to re-evaluate the expression each time a record match is tested, possibly affecting the query execution time. Note that the expression evaluates to a constant value only when the expression doesn't reference any fields in any buffer which is part of the enclosing query.
Example:
find first book where book.price < i + j + 1.
Converted code:
new FindQuery(book,
"book.price < ?", /* Where clause with the single substitution parameter. */
null,
"book.bookId asc",
new Object[]
{
plus(plus(i, j), 1) /* Calculating the value of the substitution parameter. */
}).first();
Details:
As the right-side operand doesn't reference any fields from the book buffer, FWD will extract (i + j + 1) as a single substitution parameter rather than using i and j as substitution parameters.
Comparison Operators and Literals¶
For the most part, the 4GL logical operators are equivalent to the logical operators used by the HQL where clause. The following table illustrates the correspondence between 4GL and FWD operators used in WHERE clauses (note that only the not equal operator has a different equivalent in FWD):
| Comparison Operator | 4GL | FWD |
|---|---|---|
| Less than | < |
< |
| Greater than | > |
> |
| Less than or equal | <= |
<= |
| Greater than or equal | >= |
>= |
| Equal | = |
= |
| Not equal | <> |
!= |
Literals in a 4GL WHERE clause need to be converted so they are compatible with what Hibernate expects of HQL. In some cases, this means transforming the literal to a different, compatible format. The table of correspondence between 4GL and FWD literals format used in where clauses is:
| Literal type | 4GL | FWD | Notes |
|---|---|---|---|
| Integer | 123 |
123 |
No difference. |
| Decimal | 1.23 |
1.23 |
No difference. |
| String | “The string” |
'The string' |
If a literal is used in case-insensitive comparison (string fields are case-insensitive by default), the conversion rules automatically uppercase the literal and compare it with the field's uppercased value, e.g.:upper(book.bookTitle) = 'THE STRING'In HQL, the escape character is ( ') (a single quote), so all single quotes are automatically escaped, as in:4GL literal: “The field's value”Converted literal: 'The field''s value' |
| Date | 02/01/2011 or 02/01/11 |
'2011-02-01' |
Date is converted to YYYY-MM-DD format contained in single quotes. |
| Logical | yes or trueno or false |
truefalse |
Only true and false are allowed in HQL. |
Operators and Functions¶
Progress-specific operators and built-in functions are implemented by the FWD framework. In the event these operators and functions appear in 4GL WHERE clauses, after conversion they must properly be represented in HQL where clauses or within expressions that produce query substitution values. To the extent they appear in HQL where clauses, these operators and functions are implemented as custom, Java-written, user-defined functions (UDFs), which are executed within the database server in an embedded, in-process JVM.
UDFs are used rather than the native operators of the database in order to ensure the operation executes and produces a result exactly in the same way as the Progress 4GL would have have done it. Although a database implementation may perform the same basic operation for a given operator, it may produce different results on some occasions due to different rounding behavior or perhaps a different implementation of dealing with unknown (NULL) values.
There are two types of operands which can participate in an HQL where clause:
- Fields which belong to the table whose records are retrieved. These cannot be extracted from the where clause. If at least one of the operands of an operator or a function is of this type, built-in functions should be used inside the where clause.
- Other operands: literals, variables, fields of the external records which are not going to be retrieved by this query. If all operands of an operator or a function are of this type, then one can use both built-in functions in a where clause or regular functions outside a where clause (in a substitution parameter). It is recommended to use the latter method, because it can help to minimize the number of substitution parameters, and the conversion process does so.
For example, the addition (+) operator can be represented within a where clause by the plus built-in function: “plus(book.price, 1) < 10”. It can be represented outside of the where clause, inside substitution parameters, by the MathOps.plus function.
Example 1:
find first book where book.price + 1 < 10.
Converted code:
new FindQuery(book,
"plus(book.price, 1) < 10", /* Built-in function is used. */
null,
"book.bookId asc").first();
Details:
Here, the first query tests the book's price after it adds $1 to the amount. As a query field is part of the expression, the field can not be extracted from the expression and replaced by a substitution parameter - instead, the plus built-in function will be called at query execution time, for each record.
Example 2:
find first book where book.price < i + 10.
Converted code:
new FindQuery(book,
"book.price < ?",
null,
"book.bookId asc",
new Object[]
{
plus(i, 10) /* General replacement function is used. */
}).first();
Details:
In this second example, the right-side of the expression doesn't contain any query fields so it can be replaced by a substitution parameter and evaluated before the query is executed. When the query is executed, each candidate record will have its book.price field matched against a constant (the result of the evaluated expression or better said the value of the substitution parameter).
The following table contains all 4GL functions and operators which have a corresponding built-in function and therefore can be represented inside a where clause. The third column contains a general replacement function to be used inside substitution parameters.
If you are planning to use operands of character data type please read the “Character Operands in a Where Clause” section below first.
| 4GL Function/Operator | Built-in Function to Be Used in Where Clause | General Replacement Function to Be Used Inside Substitution Parameters |
|---|---|---|
+Operands are numeric. |
plus |
MathOps.plus |
-Operands are numeric. |
minus |
MathOps.minus |
* |
multiply |
MathOps.multiply |
/ |
divide |
MathOps.divide |
modulo |
modulo |
MathOps.modulo |
+One of the operands is date, the other is integer. |
plus |
date.plusDays |
-The first operand is date, the second is integer. |
minus |
date.minusDays |
-Operands are dates. |
dateSpan |
date.differenceNum |
+Operands are strings. |
concat |
character.concat |
+Operands are string literals. |
Direct concatenation, e.g. “abc” + “def” → 'abcdef' |
Direct concatenation, e.g. “abc” + “def” → “abcdef” |
op1 begins op2 |
begins (op1, op2) orop1 like op2% (preferred way)Note that if op2 is reference to a field part of the query, the conversion rules will emit the begins built-in function instead of the like operator. Also, whenever op1 and op2 are complex expression, the begins buit-in function will be emitted. |
character.begins |
op1 matches op2 |
matches (op1, op2)@_or_ where op2 was converted from 4GL-style pattern to SQL-style pattern during conversion (if it is a literal) or using character.convertToSQLLike during runtime. See javadoc of character.convertToSQLLike in order to get familiar with pattern conversion process. |
character.matches |
length |
lengthOf |
character.length |
substring |
substringOf |
character.substring |
index |
indexOf |
character.indexOf |
string |
toString |
character.valueOf |
integer (operand)The operand is character, numeric or logical. |
toInt (operand) |
new integer (operand) |
decimal (operand)The operand is character, numeric or logical. |
toDec (operand) |
new decimal (operand) |
integer (operand)The operand is date. |
julianDayInt (operand) |
new integer (operand) |
decimal (operand)The operand is date. |
julianDayDec (operand) |
new decimal (operand) |
round |
roundDec |
MathOps.round |
year |
getYear |
date.year |
month |
getMonth |
date.month |
day |
getDay |
date.day |
entry |
entry |
character.entry |
lookup |
lookup |
character.lookup |
can-do |
matchesList |
character.matchesList |
Character Operands in a WHERE Clause¶
Character literals, variables and fields should be handled in the special way because by default character variables and fields are case-insensitive. This means that if both character operands of an operator or function are case-insensitive, they should be uppercased by the upper HQL function if the operand is a part of where clause, or by character.toUpperCase if the operand is replaced by a substitution parameter. When string literals are involved in case-insensitive comparisons, they will automatically be uppercased during the conversion process.
Example 1:
def var str as char. find first book where book.book-title = str.
Converted code:
new FindQuery(book,
"upper(book.bookTitle) = ?", /* book-title was uppercased. */
null,
"book.bookId asc",
new Object[]
{
toUpperCase(str) /* str was uppercased. */
}).first();
Details:
Assuming that book.book-title is a case-insensitive parameter, both the book.book-title field and the converted parameter str are upercased.
Example 2:
find first book where book.book-title = "FWD Conversion Reference".
Converted code:
new FindQuery(book,
"upper(book.bookTitle) = 'FWD CONVERSION REFERENCE'",
null,
"book.bookId asc").first();
Details:
Note how the string literal appears uppercased in the converted code, as the comparison needs to be performed case-insensitively.
Example 3:
find first book where book.publisher = "Golden Code Development".
Converted code:
new FindQuery(book,
"book.publisher = 'Golden Code Development'",
null,
"book.publisher asc").first();
Details:
Assuming the book.publisher field is marked as case-sensitive, the conversion process will not enclose the field in an upper function call and also will leave the string literals unchanged.
ROWID and RECID Functions¶
4GL ROWID and RECID functions return the unique identifier associated with the given record. In FWD, this identifier is represented by the id field of the database table row associated with the given record. Therefore in a where clause ROWID(alias) and RECID(alias) functions (where alias is the alias of the record which is retrieved) are translated to an alias.id reference. If these functions are used in substitution parameters, they are translated to RecordBuffer.recordID and RecordBuffer.rowID accordingly.
Example:
def buffer x-book for book. ... find first book where recid(book) = recid(x-book).
Converted code:
new FindQuery(book,
"book.id = ?", /* Converted to .id field reference. */
null,
"book.bookId asc",
new Object[]
{
RecordBuffer.rowID(xBook) /* Converted to rowID function. */
}).first();
Details:
As the recid function is used with a query field and matched against another recid called for a buffer which is not part of the query, the first recid call will be converted to a reference to the table's id field (book.id in this case, in the converted where clause). The second call is converted to a RecordBuffer.rowId function call, as the x-book buffer is not part of the query.
Ternary IF Operator¶
The ternary IF operator acts as a conditional function which, depending on the result of the condition, will evaluate and return either the expression on the then or on the else side of this function. The 4GL syntax of this function is:
IF condition THEN expression1 ELSE expression2
and will return the expression1's result when the condition evaluates to true or the expression2's result when the condition evaluates to false. Also, the 4GL requires that the data type of expression1 and expression2 be compatible with the type of the expression on the other side of the operand.
When this operator is used in a query's WHERE clause in 4GL, the converted code will express the conditional either as HQL or as an expression in Java. The HQL statement will be generated when this operator contains (either in its condition or then/else expressions) a buffer field which is part of the query; otherwise, the Java ternary operator will be generated and the result of the entire expression will be used to replace a substitution parameter in the where clause.
The syntax of the HQL operator to which the 4GL ternary IF operator converts is:
CASE WHEN condition THEN expression1 ELSE expression2 END
and the syntax of the Java ternary operator:
condition ? expression1 : expression2
In 4GL, the ternary IF operators can be nested. When this is encountered, the conversion process will start with the first ternary IF and will subsequently evaluate and convert all the nested operators, following these rules:
- when the ternary
IFoperator does not contain any query field references on any level, the entire operator is replaced by a substitution parameter in the converted where clause and the operator is added to the query's parameter list, using the Java's ternary operator:condition ? expression1 : expression2. Here, if thecondition,expression1orexpression2contain one or more nested ternaryIFoperators, the converted expression passed to the query's parameter list will look like:
condition1 ? expression1 : condition2 ? expression2 : ... expressionN
or like:
(condition ? condition1 : condition2) ? expression1 : condition2 ? expression2 : ... expressionN
when the condition contains another nested IF operator.
- when the ternary
IFoperator contains a query field reference (on any level and in any part - be it condition or then/else expressions), the entireIFoperator is kept in the converted where clause and is converted using the HQL'sCASE WHEN condition THEN expression1 ELSE expression2operator. After this, each part of this operator - thecondition,expression1andexpression2- are evaluated again, recursively, following these rules:- if it contains a query field reference, the condition or the expression will be kept in the converted where clause, and the converted where clause will look like:
CASE WHEN condition1 THEN expression1 ELSE (CASE WHEN condition2 THEN expression2 ... ELSE expressionN END) ... END
or like
CASE WHEN (CASE WHEN condition THEN condition1 ELSE condition2) THEN expression1 ELSE expression2 ... END
when the actual condition contains a nested IF operator.
- if it does not contain a query field reference, the condition or the expression will be replaced by a substitution parameter and will be converted to a query parameter.
Example 1:
def var cost-avail as logical. def var book-cost as decimal. def var default-cost as decimal. ... find first book where book.price < if cost-avail then book-cost else default-cost.
Converted example:
new FindQuery(book,
"book.price < ?",
null,
"book.bookId asc",
new Object[]
{
(costAvail).booleanValue() ? bookCost : defaultCost
}).first();
Details:
As the ternary IF operator has a query field reference in neither the condition nor the expression, the entire operator is replaced by a substitution parameter in the converted where clause and the converted code uses Java's ternary operator condition ? ... : ...
Example 2:
def var default-cost as decimal. ... find first book where book.price = if book.cost > 0 then book.cost else default-cost.
Converted example:
new FindQuery(book,
"book.price = case when book.cost > 0 then book.cost else ? end",
null,
"book.bookId asc",
new Object[]
{
defaultCost
}).first();
Details:
In this case, the ternary IF operator uses a query field (book.cost) and can't be replaced by a substitution parameter. Instead, the HQL CASE WHEN condition THEN ... ELSE ... is used in the converted where clause. Note that, depending on where the query fields are not used, either the condition, the then expression or the else expression can be replaced by substitution parameters. In this case, the default-cost was replaced by a substitution and the defaultCost variable is then passed as a parameter to the query.
Example 3:
def var book-cost as decimal.
def var default-cost as decimal.
...
find first book where book.price < if book-cost > 10 then book-cost
else if book-cost > 0 then book-cost + 10
else default-cost.
Converted code:
new FindQuery(book,
"book.price < ?",
null,
"book.bookId asc",
new Object[]
{
_isGreaterThan(bookCost, 10) ? bookCost : _isGreaterThan(bookCost, 0) ?
plus(bookCost, 10) : defaultCost
}).first();
Details:
As no query field reference exists in the right-side operand of the less-than operator, the right-side operand will be replaced by a substitution parameter and the expression will be converted using the Java ternary operator.
Example 4:
def var book-cost as decimal.
def var default-cost as decimal.
...
find first book where book.price = if book.cost > 0 then book.cost
else if book.cost = 0 then book.cost + 10
else default-cost.
Converted code:
new FindQuery(book,
"book.price = case when book.cost > 0 then book.cost
else (case when book.cost = 0 then plus(book.cost, 10)
else ? end) end",
null,
"book.bookId asc",
new Object[]
{
defaultCost
}).first();
Details:
In this case, the right-side operator contains query field references. The conversion rules recursively evaluate each condition and then/else expression and, when one with no query field reference is found, convert it to a substitution parameter. As both the nested IF operators contain query field references, they are converted to a nested CASE WHEN HQL clause.
Example 5:
def var book-cost as decimal.
def var default-cost as decimal.
...
find first book where book.price = if (if i = 1 then default-cost > 0 else default-cost = 0)
then book-cost
else default-cost.
Converted code:
new FindQuery(book,
"book.price = ?",
null,
"book.bookId asc",
new Object[]
{
(_isEqual(i, 1)
? isGreaterThan(defaultCost, 0)
: isEqual(defaultCost, 0)).booleanValue()
? bookCost : defaultCost
}).first();
Details:
As the equality test's right-side operand doesn't reference any field from the queried buffers, the entire ternary IF is replaced by a substitution parameter - see how the condition contains a nested ternary IF which in turn gets converted using the Java ternary operator.
Conversion-Time Optimizations/Adjustments¶
The FWD conversion rules perform a number of adjustments and optimizations during WHERE clause conversion. Some of these are necessary for the converted where clause to produce the correct result; others will result in better performance at runtime, due to the way the backing database might evaluate the SQL which would otherwise result from an uncorrected conversion. Even if correctness or performance will not necessarily be improved in all cases, these optimizations are intended also to produce simpler, less ugly HQL in the converted application.
Note that some of the 4GL examples below may appear strange, silly, or otherwise unlikely. However, many of these cases may and have appeared in real applications, due in large part to use and abuse of include files and preprocessor substitutions.
Here is the list of optimizations that are performed with a WHERE clause during conversion, presented as 4GL code/converted code examples:
Example 1:
def var str as char. find first book where book.price = book.price. find first book where str >= str. find first book where "test" <= "test". find first book where 1 = 1. find first rec where rec.extent-field[1] = rec.extent-field[1].
Converted code:
new FindQuery(book, (String) null, null, "book.bookId asc").first(); new FindQuery(book, (String) null, null, "book.bookId asc").first(); new FindQuery(book, (String) null, null, "book.bookId asc").first(); new FindQuery(book, (String) null, null, "book.bookId asc").first(); new FindQuery(rec, (String) null, null, "rec.id asc").first();
Details:
Equality tests between identical operands (literals, variables, fields or array elements), including =, <= and >= are replaced with TRUE.
WHERE clause in all cases evaluated to TRUE and therefore was omitted.
Example 2:
def var str as char. find first book where book.price <> book.price. find first book where str > str. find first book where "test" < "test". find first book where 1 <> 1. find first rec where rec.extent-field[1] <> rec.extent-field[1].
Converted code:
new FindQuery(book, "false", null, "book.bookId asc").first(); new FindQuery(book, "false", null, "book.bookId asc").first(); new FindQuery(book, "false", null, "book.bookId asc").first(); new FindQuery(book, "false", null, "book.bookId asc").first(); new FindQuery(rec, "false", null, "rec.id asc").first();
Details:
Inequality tests between identical operands (literals, variables, fields or array elements), including <>, < and > are replaced with FALSE.
WHERE clause in all cases evaluated to FALSE.
Example 3:
find first book where book.book-id > ?.
Converted code:
new FindQuery(book, "false", null, "book.bookId asc").first();
Details:
The expression operand > UNKNOWN is replaced with FALSE.
Example 4:
find first book where book.book-id <= ?.
Converted code:
/* WHERE clause was evaluated to TRUE and therefore omitted. */ new FindQuery(book, (String) null, null, "book.bookId asc").first();
Details:
The expression operand <= UNKNOWN is replaced with TRUE.
Example 5:
find first book where book.book-id < ?.
Converted code:
new FindQuery(book, "book.bookId is not null", null, "book.bookId asc").first();
Details:
The expressions record_field < UNKNOWN and record_field <> UNKNOWN are replaced with record_field IS NOT NULL.
Example 6:
find first book where book.book-id >= ?.
Converted code:
new FindQuery(book, "book.bookId is null", null, "book.bookId asc").first();
Details:
The expressions record_field >= UNKNOWN and record_field = UNKNOWN are replaced with record_field IS NULL.
Example 7:
find first book where true.
Converted code:
new FindQuery(book, (String) null, null, "book.bookId asc").first();
Details:
Redundant WHERE clause (i.e. WHERE TRUE) is removed.
Example 8:
find first book where false.
Converted code:
RecordBuffer.release(book);
Details:
WHERE FALSE clause specified for the FIND statement results in the statement being replaced during conversion with the RELEASE statement for the specified record buffer. This happens because while performing a query, the current record is released from the buffer and then the buffer is filled with the newly retrieved record; in this case no records will be retrieved, thus the only operation that will be performed is release of the current record.
Example 9:
def var bid as integer. def var default-bid as integer. ... find first book where book.book-id = if true then bid else default-bid. find first book where book.book-id = if false then bid else default-bid.
Converted code:
new FindQuery(book, "book.bookId = ?", null, "book.bookId asc", new Object[]
{
bid /* THEN expression. */
}).first();
new FindQuery(book, "book.bookId = ?", null, "book.bookId asc", new Object[]
{
defaultBid /* ELSE expression. */
}).first();
Details:
Ternary IF is replaced with THEN or ELSE expression if condition is a boolean literal.
Example 10:
find first book where book.book-id = 1 or true.
Converted code:
/* WHERE clause was evaluated to TRUE and therefore omitted. */ new FindQuery(book, (String) null, null, "book.bookId asc").first();
Details:
The expressions operand OR TRUE and TRUE OR operand are simplified to TRUE.
Example 11:
find first book where book.book-id = 1 or false.
Converted code:
new FindQuery(book, "book.bookId = 1", null, "book.bookId asc").first();
Details:
The expressions operand OR FALSE and FALSE OR operand are simplified to operand.
Example 12:
find first book where book.book-id = 1 and true.
Converted code:
new FindQuery(book, "book.bookId = 1", null, "book.bookId asc").first();
Details:
The expressions operand AND TRUE and TRUE AND operand are simplified to operand.
Example 13:
open query q for each book where book.book-id = 1 and false.
Converted code:
query0 = new AdaptiveQuery(book, "false", null, "book.bookId asc"); query0.open();
Details:
The expressions operand AND FALSE and FALSE AND operand are simplified to FALSE.
Extents¶
A specific element of an extent record field of the record can be referenced within a WHERE clause using the element addressing construct, record_field[element_index], where element_index is a 0-based index of the target element. As in 4GL code 1-based indexes are used, they are decremented by one during conversion. If an extent record field element is a part of a substitution parameter, it is referenced using ArrayAssigner.subscript function (which takes 1-based index of an element).
Example 1:
def var extent-var as integer extent 3. ... find first rec where rec.extent-field[1] = extent-var[1].
Converted code:
new FindQuery(rec,
"rec.extentField[0] = ?",
null,
"rec.id asc",
new Object[]
{
subscript(extentVar, 1)
}).first();
Details:
The query field has its index downgraded from 1-based to 0-based, as the conversion rules had automatically subtracted 1 from the integer index constant. The operand, which is an array element, doesn't need to have its index converted from 1-based to 0-based, as the FWD subscript functions take as parameter the 1-based index and internally converts it to 0-based, so it can retrieve the correct array element.
Example 2:
def var extent-var as integer extent 3. def var i as integer. ... find first rec where rec.extent-field[i] = extent-var[i].
Converted code:
new FindQuery(rec,
"rec.extentField[?] = ?",
null,
"rec.id asc",
new Object[]
{
minus((i), 1), /* Index was decremented by 1. */
subscript(extentVar, i) /* Index was left as is. */
}).first();
Details:
When variables are used to specify an array element, the extent query field will have its index converted to a substitution parameter. In this case too, the index needs to be decremented by one, and the conversion rules will do this automatically by augmenting the corresponding query parameter. For the array variable case, as the FWD subscript functions take as parameter the 1-based index and internally converts it to 0-based, no special conversion changes are necessary.
CAN-FIND as a Part of Where Clause¶
All CAN-FIND functions embedded within an existing 4GL WHERE clause require special conversion handling. The conversion rules will follow different paths, depending on whether the nesting CAN-FIND and the enclosing query access records in the same database or not. When the CAN-FIND access records from the same database as the enclosing query, it will be converted as an in-line sub-query of the primary statement's converted HQL where clause. The following sections will discuss the two versions of the CAN-FIND function: test for an unique record or for the existence of a record which satisfies the search criteria, when they are converted to in-line sub-queries.
When different databases are referenced by an embedded CAN-FIND function and its enclosing database statement, the CAN-FIND must be converted as a “client-side” filter. Details about client-side filters can be found in the Client-Side Filtering section of this chapter.
CAN-FIND A Unique Record (Inline Conversion)¶
This form of the embedded CAN-FIND function seeks to confirm the existence of exactly one (and only one) record matching the specified criteria. Its syntax is:
... CAN-FIND(record WHERE ... )
and is converted to the following form of HQL:
... (select count(record.id) from Record as record where ...) = 1
The CAN-FIND is converted to a test for exactly one record which matches the given criteria, hence the condition which compares the sub-query result of count(record.id) to the constant value 1. If one and only one record is found the condition evaluates to TRUE; if either no record is found or more than one record is found, the condition evaluates to FALSE.
Example 1:
find first person where person.site-id = 1 and
can-find(pers-addr where pers-addr.emp-num = person.emp-num).
Converted code:
new FindQuery(person,
"person.siteId = 1 and
(select count(persAddr.id) from PersAddr as persAddr
where persAddr.empNum = person.empNum) = 1",
null,
"person.siteId asc, person.empNum asc").first();
Details:
The inner CAN-FIND is converted to the (select count(persAddr.id) ... ) = 1 sub-query, as it references records from the same database as the enclosing query.
CAN-FIND Any Record (Inline Conversion)¶
This form of the embedded CAN-FIND function seeks to confirm the existence of a record which satisfies the given criteria. Its syntax is:
CAN-FIND({FIRST | LAST} record WHERE ... )
This form is converted to to following form of HQL:
exists(from Dmo as record where ...)
Example 2:
find first person where person.site-id = 1 and
can-find(first pers-addr where pers-addr.emp-num = person.emp-num).
Converted code:
new FindQuery(person,
"person.siteId = 1 and
exists(from PersAddr as persAddr where persAddr.empNum = person.empNum)",
null,
"person.siteId asc, person.empNum asc").first();
Details:
In this case, the CAN-FIND is converted as an exists(from PersAddr ...) sub-query, as both tables belong to the same database. The fact that the CAN-FIND specified the FIRST qualifier effectively is irrelevant. The pers-addr buffer will not actually be loaded with a record as a result of the CAN-FIND, so it is unimportant whether we can find the first record specifically. What is important is whether any pers-addr record can be found which matches the specified criteria. Thus, the use of the exists sub-query is appropriate for this purpose.
Client-Side Filtering¶
Sometimes a WHERE clause cannot entirely be converted to HQL selection criteria that can be evaluated by the database server at query execution time. For these cases, client-side filtering is performed. The term “client-side” is something of a misnomer with respect to the converted application; it refers to the fact that in the Progress environment, selection criteria which cannot be enforced by a database index alone are applied within the Progress client process. In the FWD environment, where the logical architecture consists of a database server tier and an application server tier, this filtering actually occurs in the application server process; the term “client-side” therefore indicates relativity to the database server.
Client-side filtering in the FWD environment consists of applying as much selection criteria as possible on the database server, bringing the associated results back to the FWD application server, and then applying additional selection criteria to that reduced set of candidate records at the application server. In practice, this means that a query is executed against the database server - potentially using a converted where clause - and a result set is retrieved. The first record is brought over to the application server and is matched against a “client-side” filter. If it fails, the record is discarded and the next record is retrieved. This process continues until a matching record is found, or the initial result set is exhausted. Because of the potential inefficiency of marshaling, transferring, and unmarshaling possibly a large number of records which will not match the client-side filter, this approach can cause extremely poor performance. Queries that require conversion which relies upon client-side filtering should be avoided whenever possible.
A client-side filter is implemented by creating a derived class of the abstract class WhereExpression. The main logic is contained in the evaluate method:
logical evaluate(final BaseDataType[] args)
which makes the decision as to whether a particular candidate record matches the client-side filter.
Consider the following example, where person is a record of the permanent database, and rec is a record of the _temp database. Since these records belong to different databases, they cannot be represented by an ordinary server-side where clause.
Example:
find first person where person.site-id = 1 and
can-find(rec where rec.emp-num = person.emp-num and
rec.active).
Converted code:
WhereExpression whereExpr0 = new WhereExpression()
{
public logical evaluate(final BaseDataType[] args)
{
/* Client-side part of the where clause (CAN-FIND subexpression) is represented by
the FindQuery. */
return new FindQuery(rec,
"rec.empNum = ? and rec.active = true", /* Where clause of the CAN-FIND
null, subexpression. */
(String) null,
new Object[]
{
new FieldReference(person, "empNum")
}, LockType.NONE).hasOne();
}
};
...
new FindQuery(person,
"person.siteId = 1", /* Server-side part of the where clause. */
whereExpr0, /* Reference to the client-side part of the where clause. */
"person.siteId asc, person.empNum asc").first();
Details:
In the converted code above, comments are added for instructional purposes. The conversion refactors the original, 4GL FIND FIRST statement into a FindQuery (at the bottom of the converted code) which implements part of the original WHERE clause, and an instance (whereExpr0) of an anonymous WhereExpression subclass, which implements the remaining portion of the original WHERE clause. The whereExpr0 object is passed as a parameter to the FindQuery's constructor, which allows the FWD runtime environment's persistence framework to apply client-side filtering as described above.
The conversion refactors the original WHERE clause so as to push as much of the selection criteria to the database server as possible. This allows the smallest possible pool of candidate records to be returned from the initial database request as possible. In this case, the portion of the WHERE clause which could be applied to a query against the permanent database's person table is:
person.site-id = 1
which is converted to the HQL where clause:
person.siteId = 1
which is passed to the (bottom) FindQuery's constructor. This means that when that FindQuery's first method is executed, it will retrieve only those records from the person table whose siteId property is set to 1.
The remaining criteria from the original 4GL WHERE clause
can-find(rec where rec.emp-num = person.emp-num and rec.active)
cannot be expressed as part of the same, server-side HQL where clause, because it references the rec table, which resides in a different database (the _temp database). The conversion therefore refactors this portion of the selection criteria into an anonymous WhereExpression subclass.
The implementation of that class' evaluate method uses a secondary FindQuery with the rec DMO to represent the original CAN-FIND function. FindQuery's hasOne method is emitted because the original CAN-FIND function omits the FIRST | NEXT | LAST | PREV option, indicating it is intended to check for a unique record (i.e., the function is checking for the existence of one and only one matching record).
The HQL where clause for this secondary FindQuery is as follows:
"rec.empNum = ? and rec.active = true"
The substitution parameter placeholder (?) is necessary because the number to match against rec.empNum will vary each time this FindQuery's hasOne method is invoked.
Notice that the substitution parameter itself for this FindQuery is emitted as
new FieldReference(person, "empNum")
rather than simply
person.getEmpNum()
The use of FieldReference here is necessary because execution of WhereExpression.evaluate is deferred. Invocation of this method is driven from within the persistence runtime, after each person record matching the initial criterion of person.siteId = 1 is retrieved. The FieldReference object extracts the current value of the empNum property for each such record at the moment it is needed. Please refer to the Database Field References chapter for more information on the use of the FieldReference class.
© 2004-2017 Golden Code Development Corporation. ALL RIGHTS RESERVED.