Introduction

The JSON Filter Pattern, JFP for short, enables server-side paging, filtering and sorting in Business Entities. Support for the JFP can be added to Business Entities by extending the READ operation.

Business Entities can be created using the New Business Entity wizard in PDSOE.

When using the option to create a Business Entity from a database table, the wizard creates a .i file with the schema for the given table and a .cls file with code for the operations (CRUD+Submit).

The JFP is generally used as the filter option for the Kendo UI DataSource when using a data source of type = “jsdo” (also called the JSDO dialect for the Kendo UI DataSource).

Support for the JFP, JSON Filter Pattern, is done by adding code to the READ method to accept a serialized JSON object as the value for its filter parameter. The object includes properties specifying paging, filtering and sorting.

Additionally, the schema is updated to support the functionality.

The OpenEdge documentation provides a sample implementation of the JSON Filter Pattern:
https://documentation.progress.com/output/OpenEdge117/openedge117/#page/dvwsv%2Fupdates-to-allow-access-by-kendo-ui-datasources.html

A different implementation can be used as long as the protocol is met. For example, the code could cache query results and/or cache count calculations to speed up the READ operation. The code could also use the new Change Data Capture functionality in OpenEdge 11.7 to detect when the data has changed and refresh the cache.

It is also possible to use a custom Mapping Plugin (mappingType) to implement an entirely different approach to perform server-side paging, filtering and sorting.

Changes to the temp-table definition

To enable server-side sorting using the JFP, the temp-table definition needs to have a primary index using a field called “seq”. This field is used to implement dynamic sorting in the Business Entity.

Serialization of temp-tables into JSON sorts the data based on the primary key.

Without this field the order of the records would be based on that particular key and not dynamic. The sample implementation sets “seq” appropriately to sort the records based on the specified parameters.

The “id” field is used to provide and ID field for the Business Entity which can be used to optimize paging. The "id" field is not directly used for paging, filtering and sorting. However, having it present enables potential optimizations.

DEFINE TEMP-TABLE ttCustomer BEFORE-TABLE bttCustomer
FIELD id            AS CHARACTER
FIELD seq           AS INTEGER      INITIAL ?
FIELD CustNum       AS INTEGER      INITIAL "0" LABEL "Cust Num"
FIELD Name          AS CHARACTER    LABEL "Name"
FIELD Address       AS CHARACTER    LABEL "Address"
FIELD Address2      AS CHARACTER    LABEL "Address2"
FIELD Balance       AS DECIMAL      INITIAL "0" LABEL "Balance"
FIELD City          AS CHARACTER    LABEL "City"
FIELD Comments      AS CHARACTER    LABEL "Comments"
FIELD Contact       AS CHARACTER    LABEL "Contact"
FIELD Country       AS CHARACTER    INITIAL "USA" LABEL "Country"
FIELD CreditLimit   AS DECIMAL      INITIAL "1500" LABEL "Credit Limit"
FIELD Discount      AS INTEGER      INITIAL "0" LABEL "Discount"
FIELD EmailAddress  AS CHARACTER    LABEL "Email"
FIELD Fax           AS CHARACTER    LABEL "Fax"
FIELD Phone         AS CHARACTER    LABEL "Phone"
FIELD PostalCode    AS CHARACTER    LABEL "Postal Code"
FIELD SalesRep      AS CHARACTER    LABEL "Sales Rep"
FIELD State         AS CHARACTER    LABEL "State"
FIELD Terms         AS CHARACTER    INITIAL "Net30" LABEL "Terms"
INDEX seq IS PRIMARY UNIQUE seq
INDEX CustNum IS UNIQUE CustNum

DEFINE DATASET dsCustomer for ttCustomer.

Changes to the READ method

The changes to the READ method includes the following:

  • Annotations:
    • mappingType
    • capabilities
  • Support method JFPFillMethod()
  • Support method AddIdField().
  • Changes to the actual READ method.
    @openapi.openedge.export(type="REST", useReturnValue="false", writeDataSetBeforeImage="true").
    @progress.service.resourceMapping(type="REST", operation="read", URI="?filter=~{filter~}", alias="", mediaType="application/json"). 
    @openapi.openedge.method.property (name="mappingType", value="JFP").
    @openapi.openedge.method.property (name="capabilities", value="ablFilter,top,skip,id,orderBy").
    METHOD PUBLIC VOID ReadCustomer(
            INPUT filter AS CHARACTER, 
            OUTPUT DATASET dsCustomer):

      IF filter BEGINS "~{" THEN
        THIS-OBJECT:JFPFillMethod (INPUT filter).
      ELSE DO:
        BUFFER ttCustomer:HANDLE:BATCH-SIZE = 0.
        BUFFER ttCustomer:SET-CALLBACK ("AFTER-ROW-FILL", "AddIdField").

        SUPER:ReadData(filter).
      END.
    END METHOD.
 

The method.property annotation is used to specify additional properties in the data catalog file for the method.

The setting mappingType=”JFP” tells the JSDO on the client side that the JFP will be used to specify server-side paging, filtering and sorting. On the client-side, the JSDO uses a built-in JFP plugin to process the filter expressions.

The capabilities properties is a convention used by the server code to tell the client what parameters are support by the implementation.

The JFPFillMethod() provides the implementation to process the parameters.

The AddInField() method sets the id and seq fields.

The READ method calls the JFPFillMethod() to process the JSON object.

Count method

The COUNT method is used to calculate the total number of records in the result set.
It uses the same filter specified to the READ method.

The method definition looks like the following:

@openapi.openedge.export(type="REST", useReturnValue="false", writeDataSetBeforeImage="false").
@progress.service.resourceMapping(type="REST", operation="invoke", 
                                    URI="/MyCount?filter=~{filter~}", 
                                      alias="", mediaType="application/json").
    METHOD PUBLIC VOID MyCount( INPUT filter AS CHARACTER, OUTPUT numRecs AS INTEGER):

…

    END METHOD.
 

The documentation provides a sample implementation of this method. 

A different implementation can be used as long as it uses the same protocol.

Alternatively, a custom mapping plugins cab be used. In the custom plugin, you can set the “server.count” property for the JSDO instance, in a responseMapping, to specify the value for the count.

The COUNT method can be specified in two ways. One way is using operation=”invoke”, in which case the countFnName property for the JSDO dialect needs to be specified in the transport propety for the Kendo UI DataSource. The 2nd way, available in recent versions of Progress Developer Studio, the operation for the Count method would use operation=”count” (instead of operation="invoke"). In this case, the JSDO dialect can use the information in the catalog to determine the name of the count method.