Plugin CreateRowOnEmptyTablePlugin in Jitterbit App Builder

Summary

Creates a new row and saves it to the data object if the Filter event does not return any rows.

Registration

The CreateRowOnEmptyTablePlugin is an action plugin for a table-level event. It must be registered to run after the Filter event. Because the plugin alters the behavior of the intrinsic Filter event, it should be registered on a data object, not a physical table.

Parameters

None

Remarks

The CreateRowOnEmptyTablePlugin determines if the Filter event returned any rows. If the Filter event returned one or more rows, the plugin exits without further processing. Otherwise, the plugin creates a new row and persists it to the data object.

The plugin creates a row by invoking the New event. The New event is responsible for applying binding criteria, as well as invoking the data object's default rules. In the context of a web page request, binding criteria is derived from URL query string parameters. In other words, the new row will be populated with any query string parameter values with names matching data object column names.

After invoking the New event, the plugin invokes the data object's Insert event, persisting the row to the target table. Developers may register actions on the Insert event to perform additional processing. This is important because the Filter event is a table-level event and therefore does not support row-level actions such as CRUD rules. In contrast, the Insert event is a row-level event. Row-level events support most actions, including CRUD rules.

If the Insert event is unsuccessful because one or more validation rules failed, the plugin exits without further processing. Otherwise, the plugin checks the Insert event's change scope to determine if the row should be refreshed. If the change scope is set to a value other than "None", the plugin refreshes the row. The refresh operation invokes the Filter event on the new row.

Finally, the plugin exits, returning the newly created row. Since this all happens within the context of the Filter event, the web client is unaware. As far as the web client is concerned, the row always existed.

Usage

Imagine a developer wants to create a link which automatically adds a product to the user's shopping cart. The link might look something like this:

https://example.com/App Builder/app/Shop/AddToCart?ProductId=1234

In this example, the link opens the AddToCart page, passing in ProductId as binding criteria.

Assume the data source contains a Cart table which looks something like this:

• CartId - Unique identifier, primary key, auto-generated.
• ProductId - Integer, foreign key to the Product table.
• Quantity - Integer.
• SessionId - Unique identifier, identifies the user session.

The developer starts by creating an AddToCart data object which targets the Cart table. The AddToCart data object selects all columns from the Cart table. Although not technically required, the developer flags the ProductId as a binding column. Doing so ensures that the ProductId is present before executing the event. The AddToCart data object is restricted such that users can only view cart items belonging to their current session. The resultant mvSQL statement might look something like this:

SELECT
    C.CartId AS CartId,
    C.ProductId AS ProductId,
    C.Quantity AS Quantity,
    C.SessionId AS SessionId
FROM Cart AS C
WHERE C.SessionId = session()

Note that the CartId does not need to be explicitly defaulted since it is auto-generated. ProductId is not defaulted because it will be sourced from binding criteria.

In addition to the AddToCart data object, the developer creates a data object named MyCart. Like the AddToCart data object, the MyCart data object targets and selects all columns from the Cart table. The MyCart data object is also restricted to the current session. However, the MyCart data object does not have an explicit Filter event. It does not utilize the CreateRowOnEmptyTablePlugin plugin.

The developer then creates a page named AddToCart, registering the AddToCart data object as the page data object. The developer then adds a multi-row panel to the AddToCart page. The multi-row panel selects from the MyCart data object. In addition, the multi-row panel is bound to the page data object.

The multi-row panel contains two controls:

• Product - List, bound to the ProductId column. Selects from the Product table using the ProductId as the key, ProductName as the title.
• Quantity - Numeric, bound to the Quantity column.

Viewing the page without providing any binding criteria will result in an error.

If the query string contains a parameter named ProductId with a valid value, App Builder will execute the AddToCart data object's Filter event, which in turn executes the CreateRowOnEmptyTablePlugin plugin. The plugin will create a new row, inserting it into the Cart table. After the AddToCart data object's Filter event executes, App Builder will execute the MyCart data object's Filter event. This will return the newly created row, displaying it in the multi-row panel.

The developer could perform additional actions when the new row is inserted into the Cart table. For instance, the developer could register a CRAM rule which inserts a row into the Session table if it does not exist. The mvSQL statement might look like this:

INSERT INTO Session
(
    SessionId,
    DateCreated
)
SELECT
    SessionId,
    DateCreated
FROM
(
    SELECT
    session() AS SessionId,
    now() AS DateCreated
) AS SOURCE
WHERE NOT EXISTS
(
    SELECT 1
    FROM Session
    WHERE
        Session.SessionId = SOURCE.SessionId
        AND Session.DateCreated = SOURCE.DateCreated
)

This would allow the developer to schedule an event which deletes carts belonging to expired sessions.

Errors

Infinite loops

Because the CreateRowOnEmptyTablePlugin invokes the Filter event recursively, there is a chance of an infinite loop. This would occur if the developer designed the data object such that it did not return the newly created row. For example:

SELECT *
FROM Table
WHERE 1 = 0

App Builder guards against this situation. If the Filter event does not find the new row when performing a refresh, the following exception is thrown:

Infinite loop detected when creating a row on an empty table.

Security

When a browser follows a link, it makes an HTTP GET request. As a rule, an HTTP GET request should be both safe and idempotent. An HTTP request is safe if it does not have side-effects. An HTTP request is idempotent if making the same request a second time returns the same result.

The CreateRowOnEmptyTablePlugin plugin is idempotent: refreshing the browser window will not create a second row. However, the CreateRowOnEmptyTablePlugin plugin is not safe: the plugin creates a persistent row in the database. This is, by definition, a side effect.

Developers should bear this in mind as an unsafe GET request can be exploited in a cross-site request forgery attack. For this reason, developers should avoid any potentially destructive operation such as registering a delete rule which is executed when the page loads. Developers should also avoid registering any rule or plugins which interact with external systems such as sending an email or making a payment.