Java Inside the Database(User-Defined Functions) - FWD - Golden Code Redmine

Java Inside the Database(User-Defined Functions)¶

Java Inside the Database(User-Defined Functions)

Prerequisites¶

This chapter assumes PL/Java is properly installed on the database server. If not, please refer to the PL/Java installation instructions provided in the Development Environment Setup chapter.

Introduction¶

User-defined functions (UDFs) are custom functions written in the Java language, which are embedded within an HQL/SQL query. In FWD, the Java equivalents of many of Progress' built-in functions are implemented as UDFs. They are used in the WHERE clauses of converted queries and are run within the context of the database server. This allows built-in functions embedded in queries to filter their result sets at the database server, rather than having to bring potentially larger data sets from the database to the application server to perform this filtering at the application level.

This optimization allows certain converted queries to run much faster than if the built-in functions had to run in the application server - often orders of magnitude faster.

Here is an example of usage of the indexOf UDF within the WHERE clause of a FindQuery:

new FindQuery(dmo, "indexOf('ABC', upper(dmo.field)) != 0", null, "dmo.id asc").first();

Without the use of this UDF, one would have to bring back each row in the dmo table and apply the indexOf test in application logic. There is an architected way to do this in FWD, which involves defining a subclass of the abstract class com.goldencode.p2j.persist.WhereExpression, and passing an instance of that subclass to an existing FWD query type, like FindQuery. An example of this follows:

public MyProgram
{
   private SomeDMO dmo = RecordBuffer.define(SomeDMO.class, "database", "dmo");

   private final WhereExpression indexOfFilter = new WhereExpression()
   {
      public logical evaluate(final BaseDataType[] args)
      {
         integer result = character.indexOf("ABC", character.toUpperCase(dmo.getField()));

         return (new logical(result.intValue() > 0));
      }
   };

   public void execute()
   {
      ...
      new FindQuery(dmo, (String) null, indexOfFilter, "dmo.id asc").first();
      ...
   }

Note that the FindQuery in this case does not provide a WHERE clause: the second parameter to the FindQuery constructor is instead (String) null. This means that the SQL statement sent to the database for this FindQuery will perform no filtering of the result set at all. The third parameter to the FindQuery constructor (which was null in the earlier example) is now indexOfFilter, an instance of an anonymous subclass of com.goldencode.p2j.persist.WhereExpression.

This instance of FindQuery will query a record in the table backing the dmo record buffer, load that record into the buffer, and will then invoke the evaluate(BaseDataType[]) method of the indexOfFilter object. That method will perform the indexOf filtering operation on the current record, returning true or false, depending on the outcome of the test. This succession will continue for each record in the table until either: (a) the evaluate method returns a result of true; or (b) there are no more results in the table to be processed.

This approach potentially is devastating to performance of the query. In the worst case scenario, where no row in the table matches the indexOf criterion, all rows in the table would have to be brought back to the application server and processed before we could determine that there was not a match! Even if there is a match, it may require many records to be retrieved from the database before we find it.

An alternative to this approach might be to retrieve records from the target table using a lower-level API, like com.goldencode.p2j.persist.Persistence.scroll(), then loop through the results “manually”, testing whether each matches the desired criteria. In general, this approach is slightly preferable to the “architected” approach described above, since it can be performed more efficiently than the implementation used by FindQuery, which carries with it some additional overhead required for Progress compatibility.

Both of these approaches are referred to in FWD documentation as client-side query filtering, or client-side where clause processing. The term client-side may be a bit misleading, because the filtering is taking place on the application server, not the user's client. However, from the standpoint of the database server, the application server is the database client. This nomenclature is historical; in the pre-conversion, Progress architecture, all application logic (including higher level WHERE clause processing) actually did run in a client process (not in the database).

Client-side query filtering generally is undesirable from a performance standpoint and should be used only as a last resort, when there is no clean way to perform the work of result set filtering at the database server. Client-side query filtering requires too many records to be retrieved from the database and to be processed by application logic. The situation depicted in the sample code above represents a case where the use of a UDF is preferable, as it potentially can make a huge performance difference.

The FWD conversion leverages UDFs wherever possible. When a query is encountered which cannot be converted to use a UDF naturally, it is refactored to use client-side query filtering - specifically, the WhereExpression mechanism described above.

Hand-written Java code also can take advantage of UDFs to improve the execution time of certain queries. However, like stored procedures, Java UDFs should be used selectively; see the Considerations and Limitations section below. Note that within hand-written Java code, you can use UDFs in any parts of SQL statements where an expression is possible (e.g., the ORDER BY clause), whereas the automated conversion will only use UDFs in WHERE clauses today.

UDF Implementation in FWD¶

All databases supported by FWD support Java UDFs. At the time of this writing, this includes H2 for temporary tables and PostgreSQL for permanent tables.

Since H2 is itself implemented in Java, it provides native support for Java UDFs. To the extent H2 is used as an embedded database (as it is for FWD temporary tables), UDFs are executed within the application server's JVM directly. FWD also provides limited support for H2 as an external database for permanent tables, in which case UDFs are executed within the separate JVM launched for the database server.

For Java UDF support in PostgreSQL, it is necessary to install and configure the PL/Java add-on. PL/Java is a framework implemented in Java and C (supported through JNI on Linux and Windows), which integrates with the UDF architecture in PostgreSQL to permit the definition and installation of Java UDFs in PostgreSQL. PL/Java is not part of the core PostgreSQL code base. Rather, it is a separate, open source project, released under the Berkley/BSD license. PL/Java executes Java UDFs in one or more separate JVM (a new JVM is launched for each connection), each of which is started within the context of the backend database server process.

Considerations and Limitations¶

UDFs allow you to perform data filtering by applying custom selection criteria at the database level instead of in the application. Since this enables all filtering to be performed at the database server, it generally means fewer results are returned to the application server and usually fewer round trips to the database are required. This saves bandwidth and reduces response time. In some cases using UDFs can greatly speed up application logic.

UDFs can be used to improve hand-written application code in the following cases:

Performing application-specific validation or calculations, where it is not possible or practical to use an HQL/SQL expression to perform the necessary work.
Not all Progress-specific, built-in functions are implemented by FWD yet; you may need to extend the existing set of functions.

When implementing UDFs, it is important that they be:

Stateless; you should not store information or share data across UDFs, nor make any assumption about the order in which UDFs are evaluated in a given HQL/SQL statement. A UDF should never assume it is running within or sharing data with the FWD application server's JVM.
Without side effect; you should neither retrieve nor alter data in the database from within a UDF Although it is possible to obtain a JDBC connection and execute statements from a UDF, doing so will conflict with record locking and caching done by the FWD framework in the application server.

A UDF is appropriate if it will reduce the number of records retrieved from the database, and if it does not require state information from the FWD server (other than what can be passed as UDF parameter data) to do its work. If a UDF requires independent access to state data in the FWD server, it is best to perform the same work directly in application logic.

Finally, consider that:

a UDF is for the most part opaque to the database's query planner; and
where a separate, or potentially multiple, JVM instances are necessary to implement Java UDFs, there is memory and performance overhead in launching and communicating with each such instance.

So, if the same work can be expressed reasonably well in pure HQL/SQL rather than implementing a UDF, this usually is preferable from a resource and performance standpoint.

Special Error Handling Considerations for Sub-Queries¶

HQL and SQL SELECT statements which themselves contain nested SELECT statements (i.e., sub-queries) pose a special challenge for using Java UDFs.

Consider the following example:

select toInt('foo')

The toInt UDF accepts a text value and returns an integer. The text value is meant to be parsed into an integral, numeric value, but in this case, the string 'foo' will cause the Java code to throw a runtime exception. This simply will cause the SELECT statement to fail, which is appropriate.

Now consider a more complex example, where the toInt function is used within a sub-query (in blue below):

select *
from table_a
   where ...
   and (
      select id
      from table_b
      where toInt(table_b.text_column) = 5
      limit 1
   )
   is not null

Assume table_b.text_column contains text data that should be parseable to integral values. If one of the rows in table_b contained the text 'foo' in text_column, the toInt function would fail. In this case, however, having the entire SQL statement fail is not appropriate.

What we want instead is for the sub-expression

toInt(table_b.text_column) = 5

to evaluate to false as a result of the error, so that the sub-query simply returns no result.

We have identified this portion of the overall expression because it is the smallest sub-expression which evaluates to a boolean result AND which encloses the UDF which is at risk for generating an error. We term the scope defined by this sub-expression the smallest, enclosing, logical expression scope related to the UDF.

To accomplish this limited-scope failure, we use some helper UDFs provided by FWD (in blue below):

select *
from table_a
   where ...
   and (
      select id
      from table_b
      where checkError(
               initError(false),
               toInt(table_b.text_column) = 5
            )
      limit 1
   )
   is not null

Here we have taken the sub-expression which comprises the smallest, enclosing, logical expression scope:

toInt(table_b.text_column) = 5

and made it the second argument to the checkError UDF, which has replaced it as the condition of the sub-query's WHERE clause. The first parameter to checkError is another UDF, initError (with an argument of false).

When the checkError UDF is evaluated by PostgreSQL or H2, its arguments are first evaluated. Since they are evaluated left to right, the initError(false) UDF is evaluated first. FWD uses this opportunity to initialize the smallest, enclosing, logical expression scope to false, identifying this scope as a potential failure point and indicating that it has not encountered an error condition. This value is then pushed onto a thread-local stack.

The Java code which implements the toInt UDF follows:

public static Integer toInt(String value)
{
   try
   {
      return (new integer(value)).toJavaType();
   }
   catch (RuntimeException exc)
   {
      ErrorHandler.handleError(exc);

      return null;
   }
}

If the integer constructor called in the try block returns normally, integer's toJavaType method is invoked to convert the object's value to an instance of java.lang.Integer, which is then returned from the toInt UDF.

The integer constructor will instead throw a RuntimeException if the value parameter passed to it cannot be parsed to an integral value; e.g., 'foo'. The exception is caught and passed to the ErrorHandler.handleError method. The handleError method checks the current expression scope.

If the current scope is null (i.e., an associated initError UDF was not invoked prior to this error), the exception is simply re-thrown. This represents the common case (i.e., no sub-query) where the exception is allowed to propagate normally and the overall SQL statement fails with an error.

If the current scope is non-null (i.e., an associated initError UDF was invoked prior to this error), the scope is popped from the stack, and a value of true is pushed onto the top of the stack. This indicates an error has now occurred in the current scope of the expression. ErrorHandler.handleError returns normally, and the toInt UDF returns null.

Now that both parameters of the enclosing checkError UDF have been evaluated, checkError itself is invoked. It pops the top-most expression scope. If the value is true (signaling an error occurred in the current scope), checkError returns false, indicating failure of its second parameter, the original sub-expression. Otherwise, checkError simply returns the result of evaluating the original sub-expression.

IMPORTANT: there are several critical points to be aware of when hand-writing HQL/SQL which uses UDFs within a sub-query:

The checkError and initError UDFs must only be used within a sub-query (i.e., a SELECT statement embedded within an existing WHERE clause) as described above. Statements without sub-queries must be allowed to fail normally.
The checkError and initError UDFs need only be used with hand-written HQL/SQL which is not executed using legacy query types (e.g., FindQuery, PreselectQuery, etc.). A good rule of thumb is: if you are using the RecordBuffer class in your query, you do not need to add these UDFs explicitly. When legacy support classes are used, all HQL statements are preprocessed at runtime by the FWD persistence framework, and the checkError and initError UDFs are injected transparently where needed.
The checkError and initError UDFs must always be used together, exactly as described above. The checkError UDF will not work properly without initError(false) as its first parameter, and initError will cause problems for your query if not paired with checkError appropriately.
This error handling mechanism relies on several key assumptions to work properly. These assumptions hold in the currently supported versions of PostgreSQL and H2, but have not been tested with other database implementations. Therefore, it is not guaranteed that this mechanism will work properly with future databases targeted for FWD support (a new error handling scheme may need to be developed), or even with future versions of PostgreSQL and H2. The developers of FWD anticipate being able to handle any such differences transparently for converted code, but any HQL/SQL which is hand-written post-conversion to use this error-handling mechanism may be less portable to other databases. The assumptions are:
- The database's internal expression evaluation engine always will evaluate a UDF's parameters in a consistent order, from left to right, such that the initError UDF always is evaluated before the expression which comprises the second checkError parameter. This is critical to manage error states properly.
- The database's internal expression evaluation engine always will invoke the two parameters to the checkError UDF within the same JVM and on the same JVM thread. Doing otherwise would break the use of thread-local data to manage error states.
- All Java UDFs are implemented to use the try-catch idiom as in the toInt implementation depicted above, whereby any RuntimeException caught is passed to ErrorHandler.handleError. All FWD-provided UDFs do this by convention, but custom-written UDFs must obey this contract as well.

Creating new UDFs¶

Here we describe the steps to define and implement a new Java UDF.

Declaration¶

The method of declaring UDFs differs between PostgreSQL and H2.

New UDFs should be always installed in tandem for both PostgreSQL and H2. This is important because the FWD persistence framework maintains an embedded H2 database to manage transient information related to updates made to permanent database tables. This implementation was an unfortunate necessity to mimic side effects caused by the Progress environment's transaction isolation implementation, whereby certain, uncommitted database updates in one transaction are visible from other active transactions.

Thus, if the application code executing a UDF relies upon any legacy persistence support classes (particularly com.goldencode.p2j.persist.RecordBuffer), the FWD server will likely need to execute any UDFs contained in queries against both database types.

For PostgreSQL, a UDF is registered with the database server backend using the SQL CREATE FUNCTION statement:

create function myFunc(int, text, date)
returns boolean as
'java.lang.Boolean
 org.myorg.myapp.pl.Functions.myFunc(
    java.lang.Integer,
    java.lang.String,
    java.sql.Date)'
language java immutable

Implementation¶

UDFs are represented by static methods contained within one or more Java classes:

package org.myorg.myapp.pl;

import java.sql.*;
import com.goldencode.p2j.persist.pl.*;
import com.goldencode.p2j.util.*;

public final class Functions
{
   public static final Boolean myFunc(Integer I, String s, Date d)
   {
      try
      {
         logical result = SomWorkerClass.myFuncWorker(new integer(i),
                                                      new character(s),
                                                      new date(d));

         return result.toJavaType();
      }
      catch (RuntimeException exc)
      {
         ErrorHandler.handleError(exc);

         return null;
      }
   }
   ...
}

In this case, the containing class is org.myorg.myapp.pl.Functions. This class contains the static method myFunc. The method and the containing class are public.

When calling a UDF, it is possible for the implementing Java code to throw an exception. In most cases, it would be appropriate to allow an unchecked exception to simply propagate up the call stack (or to wrap a checked exception in a runtime exception and throw it). Depending on the nature of a UDF, it might instead be appropriate to handle the exception by consuming it and returning a value appropriate to this circumstance from the method; how an exception is handled ultimately is up to the UDF implementor.

To deal with potential unchecked exceptions, the body of the method is enclosed within a try block. The associated catch block catches all RuntimeException s, passes them to the com.goldencode.p2j.persist.pl.ErrorHandler.handleError method, and returns null. This is a general purpose error handling scheme to deal with the case where the UDF is called within a sub-query. UDF use within a sub-query has special implications on the way your HQL/SQL is written. Please see the Special Error Handling Considerations for Sub-Queries section above for further details.

Note that the above example defines a thin, forwarding API layer which delegates the actual work of the UDF to a separate worker class. This is a recommended approach and is typical of the UDFs provided by the FWD project, which re-use FWD utility code for built-in functions and operators. By taking a similar approach of sharing application code between your application logic and your UDFs, you ensure consistent behavior across your application and in your queries.

However, by sharing code in this fashion, it is likely that you will have to deal with converting data between Java wrapper types (e.g., java.lang.Integer) and FWD wrapper types (e.g., com.goldencode.p2j.util.integer). This is because the database (or more specifically - PL/Java in the case of PostgreSQL) will pass Java wrapper types to UDFs and expect them to return Java wrapper types, while most application code will be written to deal with FWD wrapper types, for compatibility with converted 4GL code.

Thus, the Java method backing a UDF should accept Java wrapper types as input parameters and should return them. Note how in the above example, the input parameters i, s, and d are used to construct instances of the FWD wrapper types integer, character, and date, respectively, as they are passed to the SomeWorkerClass.myWorkerFunc method. Likewise a logical result is returned by this worker method, which is converted to the java.lang.Boolean type using the logical class' toJavaType method, before it is returned from the UDF method. The following table lists the relevant data type mappings:

SQL Type (used for data storage)	SQL Type used in UDF definition (PL/Java only)	Java Wrapper Type (most appropriate for UDF implementation)	FWD Wrapper Type (4GL-compatible)
`integer`	`integer`	`java.lang.Integer`	`com.goldencode.p2j.util.integer`
`numeric`	`numeric`	`java.math.BigDecimal`	`com.goldencode.p2j.util.decimal`
`text varchar`	`text`	`java.lang.String`	`com.goldencode.p2j.util.character`
`date`	`date`	`java.sql.Date`	`com.goldencode.p2j.util.date`
`boolean`	`boolean`	`java.lang.Boolean`	`com.goldencode.p2j.util.logical`
`bytea` `binary`	`bytea`	`byte[]`	`com.goldencode.p2j.util.raw`

Special care must be taken when defining UDFs to ensure that the function with the proper input parameters can be found by PL/Java or H2 at runtime. In particular, certain UDFs must be polymorphic to support a mixture of integral and arbitrary precision numeric data, particularly since it will not necessarily be known until runtime which data type will be used for a particular query. If a function whose signature matches the parameter data types provided at runtime cannot be found, an error will occur and the query will fail. For example, the polymorphic divide functions may look like this:

public static BigDecimal divide(BigDecimal a, Integer b);
public static BigDecimal divide(Integer a,    Integer b);
public static BigDecimal divide(BigDecimal a, BigDecimal b);
public static BigDecimal divide(Integer a,    BigDecimal b);

It is important to remember that there is no communication or state-sharing between the PL/Java-enabled functions and the active FWD server, except insofar as the former are used to filter the query result sets requested by the latter. Java code within a UDF backing method should assume that it has no access to any application state other than the data passed into it as input parameters. Avoid writing code which makes direct calls to data structures in other parts of the application or which tries to communicate with the application via a back channel.

Packaging and Installation¶

The process of packaging/installing UDFs differs depending upon whether the target database is embedded within the application server's JVM process (H2 only), or whether it is an external database (PostgreSQL or H2).

Since UDFs running within an embedded database are executed in the same JVM as the application, the classes that implement these functions just have to be into the application CLASSPATH in order to be available for H2. At this time, the only UDFs available for use with embedded H2 databases are in the com.goldencode.p2j.persist.pl package (in the Functions and Operators classes). To define new UDFs for use with H2 embedded databases, they must be added to this package, and the p2j.jar file must be rebuilt. This process is described in the next section, Extending the Set of UDFs for FWD. Future, planned improvements to FWD will enable the creation of UDFs for embedded H2 databases in an external jar file, without having to rebuild FWD artifacts.

As for PostgreSQL, PL/Java executes UDFs in separate JVMs. So, the classes containing these UDF implementations need to be installed in each database instance in which they are needed. They are accessed by the PostgreSQL backend via PL/Java, which initiates JVMs as needed. For this purpose a jar file should be created. It should contain:

the classes which directly implement UDFs;
all classes required as dependencies by the UDF implementations (insofar as they are not already included in the JRE);
a deployment descriptor (.ddr) to register functions on installation and drops them on uninstallation.

PL/Java does not access installed jars file from the file system. Instead, PL/Java serializes jar files to a byte array and stores these within a database table in the sqlj schema (this schema should appear after PL/Java was installed for a specific database). At runtime, it uses a custom ClassLoader implementation to deserialize these byte arrays back into classes and load them into the JVM.

The following SQL statements must be executed against each target database which should support given UDFs:

select sqlj.install_jar('file:///file_path/your_file.jar', 'your_file_handler', true);
select sqlj.set_classpath('public', 'your_file_handler');

where:

file_path is the absolute path to your jar file and your_file.jar is the name of your jar file;

your_file_handler, the second parameter passed to the install_jar function, is used in future statements as a handle for the serialized your_file.jar@;

the third parameter of the install_jar function, true, indicates that the your_file.jar contains a deployment descriptor which should be executed upon installation

In order to uninstall your PL/Java UDF jar, run the following command:

select sqlj.remove_jar('your_file_handler', true)

where:

your_file_handler is the handle specified during jar installation.

FWD ships with a jar file which encapsulates all of the code necessary to support the UDFs defined for FWD: p2jpl.jar. This jar is built automatically with ant jar (or any ant targets dependent upon FWD's build.xml jar target).

Extending the Set of UDFs for FWD¶

In the current version of FWD, not all 4GL functions have been implemented yet, so over time it will be necessary to extend the existing set of functions. If there is no appropriate UDF, then client-side filtering will be used in the converted code, which may cause poor performance.

Let us implement the 4GL function R-INDEX(source, target), which returns an integer that indicates the position of the target string within the source string. The search is performed from right to left.

The following steps will be performed:

Implement the UDF in Java¶

The implementation of the function itself is pretty straightforward and is added to com.goldencode.p2j.persist.pl.Functions, where all built-in functions currently are implemented:

public static Integer lastIndexOf(String source, String target)
{
   try
   {
      return character.lastIndexOf(new character(source),
                               new character(target)).toJavaType();
   }
   catch (RuntimeException exc)
   {
      ErrorHandler.handleError(exc);

      return null;
   }
}

Note the use of the try-catch block for error handling (see the Special Error Handling Considerations for Sub-Queries section above).

Update the PL/Java Deployment Descriptor¶

The PL/Java deployment descriptor for FWD, pl/p2jpl.ddr, is modified: statements for registering and unregistering this function are added as follows:

SQLActions[] =
{
"BEGIN INSTALL
    ...
     create function lastIndexOf(text, text)
      returns integer as
     'java.lang.Integer=
         com.goldencode.p2j.persist.pl.Functions.lastIndexOf(
             java.lang.String,
             java.lang.String)'
      language java immutable;
     ...
   END INSTALL",

   "BEGIN REMOVE
      ...
      drop function lastIndexOf(text, text);
      ...
   END REMOVE" 
}

Note the use of the equals operator (=) between the method's return type (java.lang.Integer) and the method name (com.goldencode.p2j.persist.pl.Functions.lastIndexOf). This operator is not required according to the PL/Java documentation. However, due to an apparent bug in the PL/Java deployment descriptor parsing logic, it is necessary to include this operator here. This has been reported to the PL/Java project team and may be fixed in a future version of PL/Java.

The UDF is created as immutable. This provides an instruction to the database that the function will not make any changes to the database, and will always return the same result, given the same inputs. This allows the database some freedom to employ optimizations when executing the UDF. All current FWD UDFs abide by these restrictions and are declared immutable. New UDFs should follow the same contract.

Rebuild `p2jpl.jar`¶

This is accomplished simply by executing ant jar.

Re-install `p2jpl.jar` in the Target Database¶

Connect to the target database with psql or your favorite SQL client. Execute the following statements, modifying the path to the p2jpl.jar as necessary to match your build environment:

select sqlj.remove_jar('p2j', true);
select sqlj.install_jar('file:///p2j/build/lib/p2jpl.jar', 'p2j', true);
select sqlj.set_classpath('public', 'p2j');

If the jar file was not previously installed in this database, it cannot be removed, so the first statement will generate an error. This is normal.

The second statement installs the jar file into the sqlj schema and defines p2j as its handle.

The third statement sets the CLASSPATH to be used by any JVMs launched by PL/Java for this database. It provides the p2j handle defined in the previous statement, so that the PL/Java class loader will know to search the p2jpl.jar file for classes it requires.

At this point, the new function has been added to the target database. You can check its operability by executing a test query with it (the following example assumes a table book containing a text column book_title) and checking whether any errors have occurred:

select * from book where lastIndexOf(book_title, 'J') > 0

Note that you do not need any special installation actions for the embedded H2 databases, other than to ensure your application is using the most recent version of p2j.jar, which includes your new function.

Modify the FWD Conversion Rules¶

Although you've created a new function, the conversion code isn't aware of this function and it will not use it, falling back to client-side filtering. So, we need to modify the appropriate conversion rules.

First edit rules/include/common-progress.rules, adding R-INDEX token to the set of tokens which represent a UDF:

<function name="server_builtin_funcs">
    <variable name="oldtype"   type="java.lang.Integer" />
    <return   name="result"    type="java.lang.Boolean" />
    <expression>result = false</expression>
    <rule>evalLib("builtin_funcs") and ancestor(prog.kw_where, -1)
            <action>oldtype = #(int) getNoteLong("oldtype")</action>
            <rule>

               oldtype == prog.kw_r_index   or
               oldtype == prog.kw_index   or
               oldtype == prog.kw_int     or
               oldtype == prog.kw_dec     or
               ...

This enables all places within the conversion which use the common conversion function server_builtin_funcs to classify this token properly.

Next, we want to update the part of the conversion which generates HQL WHERE clauses to use the new UDF. Edit rules/annotations/where_clause.rules, as follows:

...
<!-- functions (including operators implemented as functions)
              executed on the server -->
         <rule>getNoteBoolean("server_op")
            <rule>type == prog.func_int

               <rule>getNoteLong("oldtype") == prog.kw_r_index
                  <action>opTxt = "lastIndexOf("</action>
               </rule>
...

This tells the rules engine to inject the lastIndexOf UDF into the HQL WHERE clause being generated, when it encounters the R-INDEX token within a Progress 4GL WHERE clause, where the function represented by that token previously has been annotated as a database server-side operation.

Once these changes are made, you should be able to convert programs which contain the R-INDEX function in the WHERE clause, and it will be translated to the proper UDF. For example, the following 4GL code:

FIND FIRST book WHERE R-INDEX(book-title, "J") > 0.

should now convert to:

new FindQuery(book,
              "lastIndexOf(upper(book.bookTitle), 'J') > 0",
              null, "book.bookId asc").first();

Extending the Set of UDFs for Custom Applications¶

You can implement some application-specific validation or calculations as UDFs. This may greatly improve code performance, if it avoids bringing data from the database server to the application server. However, as noted above, since UDFs generally are opaque to a database's query planner, you should prefer using native SQL operators and functions to UDFs where possible, if you can achieve the same functionality using the former. This will ensure the database chooses the best query plan on your behalf.

That being said, let us implement the function inverseMatches, which returns true if one string matches the reverse representation of another string.

The following steps will be performed:

Implement the UDF in Java¶

The implementation is represented by a single class which contain the target function. In this case, we chose the name CustomDatabaseFunctions for clarity, but any name will do:

package com.project.pl;

import com.goldencode.p2j.persist.pl.*;

public class CustomDatabaseFunctions
{
   public static Boolean inverseMatches(String a, String b)
   {
      try
      {
         if ((a == null && b != null) || (a != null && b == null))
         {
            return false;
         }

         if (a == null && b == null)
         {
            return true;
         }

         return (new StringBuilder(a)).reverse().toString().equals(b);
      }
      catch (RuntimeException exc)
      {
         ErrorHandler.handleError(exc);

         return null;
      }
   }

}

First, note the package name com.project.pl, which indicates that this class is external to the FWD project. Therefore, we need to import com.goldencode.p2j.persist.pl.* in order to use the ErrorHandler class for error handling (see the Special Error Handling Considerations for Sub-Queries section above).

Create a PL/Java Deployment Descriptor¶

A PL/Java deployment descriptor must be created for PostgreSQL. In our case, it will contain queries for registering and unregistering a single function. We name the descriptor file custom-pl.ddr:

SQLActions[] =
{
   "BEGIN INSTALL

      BEGIN PostgreSQL set search_path to public END PostgreSQL;

      create function inverseMatches(text, text)
      returns boolean as
      'java.lang.Boolean=
          com.project.pl.Functions.inverseMatches(
             java.lang.String,
             java.lang.String)'
      language java immutable;

   END INSTALL",

   "BEGIN REMOVE

      drop function inverseMatches(text, text);

   END REMOVE" 
}

As usual, the new UDF should be declared immutable.

Create a Manifest File¶

A manifest file is created for the jar which will be installed into the target database. We name it project-pl.mf in this case and place it in the manifest subdirectory of the project root directory. The base name can be anything, but the extension should be .mf. The file contains a reference to the deployment descriptor, which will be used by PL/Java when the jar file is installed in the database:

Manifest-Version: 1.0

Name: custom-pl.ddr
SQLJDeploymentDescriptor: TRUE

Create an `ant` Build File¶

An ant build file (build.xml) is created to build the target jar file. Alternatively, if an ant build file already exists for the application, the relevant content from the following listing should be integrated with it:

<project name="Project" default="jar" basedir=".">
  <!-- set global properties for this build -->
  <property name="src" location="src"/>
  <property name="build" location="build"/>

  <target name="init">
    <!-- Create the build directory structure used by compile -->
    <mkdir dir="${build}/classes"/>
  </target>

  <target name="compile" depends="init">
    <!-- Compile the java code from ${src} into ${build} -->
    <javac srcdir="${src}" destdir="${build}/classes"/>
  </target>

  <target name="jar" depends="compile">
    <!-- Create the lib directory -->
    <mkdir dir="${build}/lib"/>

    <!-- Put everything in ${build} into the project-pl.jar file -->
    <jar destfile="${build}/lib/project-pl.jar" 
     manifest="manifest/project-pl.mf">
      <fileset dir="${build}/classes"/>
      <fileset dir="pl"/>
    </jar>
  </target>

  <target name="clean">
    <!-- Delete the ${build} directory tree -->
    <delete dir="${build}"/>
  </target>
</project>

At this point, the project directory related to the target jar includes, at minimum, the following:

build.xml
build
+--- classes
src
+--- com
     +--- project
          +--- pl
               +--- CustomDatabaseFunctions.java
pl
+--- custom-pl.ddr
manifest
+--- project-pl.mf

Build the UDF Jar File¶

Execute ant jar, which builds the target jar, project-pl.jar.

Install project-pl.jar into the Target Database¶

Connect to the PostgreSQL backend with your favorite SQL client and execute the following statements:

select sqlj.remove_jar('projectpl', true);
select sqlj.install_jar('file:///project/build/lib/project-pl.jar', 'projectpl', true);
select sqlj.set_classpath('public', 'p2j:projectpl');

Note that the set_classpath accepts a colon-separated list of jar handlers. Since the core FWD UDFs must also used with this database, the p2j handle defined previously must be included in this list.

After these steps are complete, the new UDF has been installed for the target PostgreSQL database. You can check its operability by executing a query which uses the new function (the following example assumes a table book containing a text column book_title) and checking whether any errors have occurred:

select * from book where inverseMatches(book_title, 'gnimmargorP avaJ')

Since we implemented the UDF in a package and jar file outside of the FWD project, a bit of additional work is necessary to enable this function for and embedded H2 database. At the time of this writing, there is no clean way to extend FWD for this purpose, so it gets a bit messy, in that it involves modifying and rebuilding FWD itself. Future development plans call for an architected approach to enable the addition of new UDFs without having to change the FWD project, but for now...

Make the UDF Implementation Class(es) Available to the FWD Project¶

We will be modifying FWD source code below to reference our new com.project.pl.CustomDatabaseFunctions class, so this class needs to be visible to the FWD project. The simplest way to do this is to add the com.project.pl package to the FWD project under the src directory.

Enable FWD to Create the H2 Aliases¶

FWD currently uses a hard-coded list of classes whose methods are made available to embedded H2 databases as built-in UDFs. We modify this list (com.goldencode.p2j.persist.pl.BuiltIns.publicFunctionClasses) to include our new UDF implementation class:

Edit the com.goldencode.p2j.persist.pl.BuiltIns class and add the line in blue below:

private static final Class<?>[] publicFunctionClasses = new Class<?>[]
{
   Functions.class,
   Operators.class,
   com.project.pl.CustomDatabaseFunctions.class
};

Rebuild p2j.jar¶

Execute ant jar.

Test the new UDF¶

At this point, you should be able to write statements that use the new UDF:

new FindQuery(record,
              "inverseMatches(record.field1, 'gnimmargorP avaJ')",
              null,
              "record.id asc").first();

Project

General

Profile

FWD

Wiki