Appboard/2.4/builder/shim expressions

In many cases, you will want to pass a parameter value into a query rather than using a static query definition. AppBoard provides a system of variable expressions, called SHIM, that can be used for this purpose. This page reviews the concepts of using SHIM expressions and provides some examples.

Inserting SHIM Expressions

There are a number of input fields in AppBoard that allow the insertion of SHIM expressions. To see if a SHIM expression can be inserted, click in an edit box and check to see if the "Insert Expression" button is displayed and clickable in the footer bar.

Perform the following steps to insert a SHIM expression:

1. Click in an edit box at the location where you want to insert an expression
2. Select Insert Expression
3. Click the tab label to select from one of the available options:
   1. Session Variable - Allows you to insert a custom variable that you have configured for the system. For more information on this option, see configuring Session Variables
   2. Basic User Info - Will insert a pre-defined SHIM expression related to the attributes of the current User. Select the variable to be inserted from the list:
      1. username - Apply the User's id in the selected location when data is accessed
      2. password - Apply the User's password in the selected location when data is accessed
      3. domain - Apply the User's domain in the selected location when data is accessed
      4. rolename - Apply the User's current role in the selected location when data is accessed
      5. rolepath - Apply the User's current hierarchical role path in the selected location when data is accessed
   3. Date - Build a date function to apply in the selected location when data is accessed
   4. Input Parameter - Apply a value for a named variable in the selected location when data is accessed
      1. Input Name - The name of the variable to be replaced in the expression
      2. Default Value - The initial value to use for the variable in the expression, unless/until the value is overridden by an Action or another process
   5. Click Close and observe the SHIM expression that was generated and inserted in the location where you added the expression.

SHIM Reference

A reference guide listing many available SHIM functions is available on the SHIM reference page.

SHIM Example

The following example walks through an example of using a SHIM expression to pass a variable to an AppBoard query that will filter a Data Collection in real time based on a user-selected item in a Widget.

The example provides a sample database to be loaded into MySQL.

Load Sample Database Into MySQL

Perform the following steps to configure MySQL:

1. Download and install a local MySQL database, such as from http://www.wampserver.com/
2. Create a user in MySQL named "appboarduser" with the password "appboardpw"
3. Create a database in MySQL by importing the following sql file from the AppBoard file system:
   • [INSTALL_HOME]/server/webapps/enportal/WEB-INF/data/pkg/sample/CreateDatabase.sql
4. Confirm that a database was created called "appboard_tutorial" with tables "asset" and "assettype"
5. Make sure the user "appboarduser" has full permissions on the new database

Browse Data

Perform the following steps to develop a familiarity with the data that will be used in this exercise:

1. Observe that there is a table called "asset" and a table called "assettype"
2. Observe that the "assetType" field in the asset table corresponds to the "id" field in the assettype table

Create the AppBoard Data Source

Perform the following steps to configure a Database Table Data Source in AppBoard:

1. If AppBoard is running, stop AppBoard
2. Install the MySQL driver by downloading the MySQL driver jar file and saving it into [INSTALL_HOME]/ server/webapps/enportal/WEB-INF/lib/
3. Start AppBoard
4. Log in to AppBoard as administrator
5. Create a new Data Source called "Tutorial"
   1. Select the Database adapter type
   2. Select the Database Table adapter
   3. Click Add Data Source
   4. Select the MySQL adapter Template
6. Configure the database connection properties
   1. url: jdbc:mysql://localhost:3306/APPBOARD_TUTORIAL
   2. username: appboarduser
   3. password: appboardpw
7. Click Next to proceed to the Queries step.
8. Confirm on the Queries screen that there are two tables listed in the Entities panel: "asset" and "assettype".
9. Click Next to proceed to the Explore step.
10. Confirm on the Explore screen that each table's fields are shown and the types and Primary Keys have been captured from the database.
11. Click "Next" and "Finish"
12. In the Data Collections panel, click the Preview button to view the "tutorial.asset" and "tutorial.assettype" data and observe that it matches the original MySQL source data previously observed.

Create Asset Type Widget

Perform the following steps to create an asset type selector Widget. This is a Widget that the User will click in to filter the data such that only the assets of the selected type will be shown in an adjacent Widget.

1. Under the Quick Actions menu, click Add Stack.
2. Enter the stack title "DB Tutorial" and click the Add Stack button.
3. Under the Quick Actions menu, click Add Widget.
4. Configure the Data properties in the Widget Wizard:
   1. Widget Type: Table
   2. Data Collection: tutorial.assettype
   3. Widget Name: Asset Type
5. Configure the Visualization properties in the Widget Wizard:
   1. Click Add Column and select the "Type" field to be displayed.
   2. Click "Next" and "Finish".
6. View the "Asset Type" table in the Builder

Create AssetQuery Data Source

Perform the following steps to configure a Database Query Data Source. This data source will create a query (e.g. subset) of the asset table that only shows assets of a specific assetType. The default assetType will be set to 1 ("Server"). Later this value will be set dynamically by an Action so that the User can control in real time which assets are displayed.

1. Create a new Data Source called "Tutorial.AssetQuery".
   1. Select the Database Query adapter.
   2. Click Add Data Source.
   3. Select the MySQL adapter Template.
2. Configure the database connection properties
   1. url: jdbc:mysql://localhost:3306/APPBOARD_TUTORIAL
   2. username: appboarduser
   3. password: appboardpw
   4. Click Next to proceed to Queries.
3. Click Add in the Entities panel to create a query definition.
   1. Name: AssetTypeQuery
   2. Description: Select assets of a specific assetType
   3. SQL Query: select * from asset where assetType =
   4. Insert the SHIM expression that will pass in the assetType to be used as a filter:
      1. Click to the right of the "=" in the SQL Query expression and select Insert Expression
      2. In the Create an Expression editor, click the Input Parameter (SHIM) tab
      3. Input Name: assetType
      4. Default Value: 1
      5. Click Apply
      6. Observe the new query and expression now shown for SQL Query: select * from asset where assetType =${shim:query.assetType('1')}
   5. Click Save
4. Confirm on the Queries screen that the "AssetTypeQuery" entity is shown.
5. Click Next to proceed to Explore.
6. Confirm on the Explore screen that the fields are shown and the types have been captured from the database. Click "Next" and "Finish".
7. In the Data Collections panel, click the Preview button to view the "Tutorial.AssetQuery" data and observe that only those records with assetType=1 are shown.

Create Asset Query Widget

Perform the following steps to create an asset query Widget that displays a list of assets from the asset table. By using the asset query Data Source, the list of assets will be initially filtered by the default asset type ("1") defined in the default query configuration.

1. Under the Quick Actions menu, click Add Widget
2. Configure the Data properties in the Widget Wizard:
   1. Widget Type: Table
   2. Data Collection: tutorial.assetquery.AssetTypeQuery
   3. Widget Name: Asset Query
3. Configure the Visualization properties in the Widget Wizard:
   1. Columns: assetid, assetName, assetOwner, assetRole, assetType
   2. Click "Next" and "Finish"
4. Observe two Widgets now shown in the "DB Tutorial" Stack
5. Drag the "Asset Query" Widget to the right of the "Asset Type" Widget
6. Shrink the width of the "Asset Type" Widget so it is about 25% of the width of the "Asset Query" Widget

Create Action on Asset Type Widget

Perform the following steps to create the Action that will enable the asset type selected in the selector to filter the asset records shown in the list. The Action will modify the asset type value being passed in to the asset query Data Collection:

1. Click the Actions (lightning bolt) icon in the Widget Title Bar to define an Action for the "Asset Type" Widget
2. Click Add Action and select Apply a Server Side Filter to a Data Collection
3. Under Filter Data Collection, select "tutorial.assetquery.AssetTypeQuery"
4. Click Add Rule and select Widget Interaction Comparison
5. Select "id", "=", "assetType" in the appropriate drop down lists
6. Click "Finish"

The Board is now configured to achieve the stated objective. When an Asset Type is selected in the "Asset Type" Widget, the Action applies the query to filter the data that is displayed in the "Asset Query" Widget. It accomplishes this through the use of the "${shim:query.assetType('1')}" SHIM expression in the database query.

Best Practices

In advanced system designs where there are multiple Widgets invoking queries with SHIM expression, care must be taken to avoid race conditions between a server filter being applied by an Action, and another server filter being applied by same Action on a related list.

If you have any Data Source that depends on a query parameter being set by a server-side filter and accessed with an expression like ${shim:query.testName('testValue')}, and that expression is used in your query to modify the data being returned (such as performing aggregate calculations on a selected month), and not just filtering out records, then that query parameter ("testName" in the above case) must be configured as follows:

1. Returned as an attribute of the data source
2. Part of the primary key

If you do not follow this convention, and if there are multiple ways to query this data (multiple Widgets using the same Data Source, a Widget using the Data Source and another having an association to the same Data Source), then results may be unpredictable, as the server will correctly think the last data in is the most recent. If you cannot follow this convention, then you would need to create duplicate Data Sources that are each only used by a single Widget (even through Associations) to make sure that there is no race condition.