Extention Scenario (Context)¶

The extension shows how to retrieve specific data from a database, which can then be utilized to provide context for the XML data obtained from the equipment.

The goal of this exercise is to retrieve a database entry identified by the EquipmentId and to add two more fields from the database (OrderNo and Product) to the structure, which will then be output on a REST server.

{
    "OrderNo": "12345",
    "Product": "Gingerbread",
    "EquipmentId": 1,
    "Temperature": 180.1,
    "FlowRate": 0.0833,
    "PowerConsumption": 300
}

Prerequisites and Preperation¶

In this walkthrough, we will use HSQLDB, but generally, any SQL database should work just fine.

You can download the prepared HSQLDB file from here and extract the files to your preferred location on your system.

SMARTUNIFIER Configuration¶

The following steps are necessary to integrate the database into our scenario:

1. Create a Database Information Model and a SQL Database Channel

2. Create an updated version of the existing REST Information Model and clone the existing REST Channel

3. Create a new Mapping, containing the XML, Database, and REST Information Model

4. Create a new Device Type and a new Instance

Information Model - Database¶

Create an Information Model that represents the structure of the HSQLDB:

We need to use the Node Type Command because we will utilize the following SQL statement in the Mapping later: SELECT * FROM Context WHERE EquipmentId = ${EquipmentId}, which requires a result to be returned.

Communication Channel - Database¶

To create the SQL Database Communication Channel we need to proceed with the steps outlined below:

1. Create the REST Server Channel:

• Enter the group name: demo.xml-rest.extention

• Enter a name: e.g. Database

• Select the Database Information Model created previously

• Select Sql Database as Channel Type

2. Configuration of the SQL Database Channel:

a. Select the root node to configure:

• Select HSQLDB as Type

• Enter the JDBC Url jdbc:hsqldb:file:<Path_to_HSQLDB_files>/demoDatabase

• Enter the Username SA and Password Test-1234

Hint

In this instance, the names of the database columns match exactly with those specified in the information model. However, should there be any discrepancies, you have the option to align the names correctly by activating the Extended Column Configuration feature.

3. Apply your changes and save the Channel.

Information Model - Rest Server¶

Clone the existing RestServer Information Model by entering the edit mode and clicking on the clone button.

Change the Group to demo.xml-rest.extention and the Name to RestServer. Then add the two additional fields OrderNo and Product.

Save the updated RestServer Information Model.

Communication Channel - Rest Server¶

Clone the existing RestServer Communication Channel by entering the edit mode and clicking on the clone button.

Change the Group to demo.xml-rest.extention and the Name to RestServer. Then save the new Channel.

Mapping¶

To create the Mapping we need to proceed with the steps outlined below:

1. Create the Mapping:

• Enter the group name: demo.xml-rest

• Enter a name: e.g. XmlDataToRest

• Add the Information Models form earlier:

• demo.xml-rest.FileReader

• demo.xml-rest.extention.RestServer

• demo.xml-rest.extention.Database

Hint

You can enter for each Information Model a short name which is used within the Rule to access all elements of the specific Information Model e.g. csv, db and rest.

2. Create a Single Rule that defines the Mapping between the three Information Models.

a. Enter a Rule Name e.g. RuleWithContext

b. Drag and drop the XmlData Event from the File Reader Information Model into the trigger field.

c. Drag and drop the DatabaseSelect command from the Database Information Model into the actions panel. Make sure to choose the Complex assignment type.

d. Drag and drop the Data variable from the Rest Server Information Model into the Command Reply panel.

e. Assign the appropriate source element to each target element either via drag-and-drop or, in the case of type conversion, by entering code.

• db/DatabaseSelect/Parameters/EquipmentId := toInt(event.equipmentId)

• rest/Data/OrderNo := Drag and drop the OrderNo from the Database Reply

• rest/Data/Product := Drag and drop the Product from the Database Reply

• rest/Data/EquipmentId := toInt(event.equipmentId)

• rest/Data/Temperature := toDouble(event.temperature)

• rest/Data/FlowRate := toDouble(event.flowRate)

• rest/Data/PowerConsumption := toInt(event.powerConsumption)

Hint

To enter code in the Source field click the Source Code <> icon.

3. Compile the Rule and save the Mapping.

Device Type¶

To create the Device Type we need to proceed with the steps outlined below:

1. Enter the group name used for this demo: demo.xml-rest.extention and a name: e.g. SU DeviceType.

2. Select the Mapping created previously (demo.xml-rest.extention:XmlDataWithContextToRest:latest)

3. Assign the Channels (File Reader, Rest Server and Database) to their respective Information Models

4. Save the Device Type.

Instance¶

1. Here we just need to select the Device Type created previously.

Hint

We could modify the configuration of the different Channels at this level, but we have already done so at the Channel level previously.

2. Save the Instance.

Deployment¶

To deploy our Communication Instance we need to proceed with the steps outlined below:

1. Select the Local Deployment

2. Select the demo.xml-rest.extention.SUInstance, the Endpoint default:Default and Info for the level of detail of the log (We can leave the other configuration at their default value for this demo).

3. Save the Deployment.

4. Now Deploy and Start the Instance.