Storing History to External Database

Sometimes it is useful to store a variables history in an external database. There are few steps to do this: setup a desired database with a table to store values, connect this database to the Iotellect Server and create a Model that will listen to variable changes and insert them into the table.

This tutorial assumes you have some familiarity with databases management.

For our example we've set up a mysql database named history_db with a table variable_history where we will be storing our variable history from Iotellect. Our table has the following fields:

Field types shown above are for our demonstration, you will define appropriate database-specific type for each field according to your purpose.

Now let's connect the database to the Iotellect Server. We need to create a Device Account for it. Open a popup menu for the Device node in the System Tree and click on the Add Device item.

After the Add Device dialog, choose SQL Database as the Device Driver. We've named our device historyDatabase and specified the Database URL, Database Username, and Database Password. We're using the root user because it's convenient in the test environment, but please be sure to use proper security protocols in your live environments.  

A note about filling out the Database URL correctly: this JDBC connection string should point to your database. The correct connection string depends on your DBMS. You can find template strings in the field's combo box, which must be edited to match the target database. The Driver Class field can be skipped for most modern databases.

If the parameters are correct and the connection is successful, then the History Database device appears in the system tree and the Device Properties dialog will be shown. Navigate to the Queries tab (1) and add a new query (2). In step (3) we've added an example query SELECT * FROM variable_history to illustrate retrieving all changes to a given variable.

After the device is created and configured, we can interact with the database by executing queries with the History Database device.

We will use a Model to listen to the variable changes and write event data to the database. We create a Model in a way similar to creating a new device, by right-clicking on the Models node () and selecting Create from the context menu. We name our new Model externalEventStorage and click OK.

The Model Configuration dialog will open. Go to the Bindings tab (1) and add a row (2). A new binding will be added to the Bindings table. We'll discuss in detail how to fill in each field. For now simply check the On Startup field (3).

The values for the remaining fields, Target, Expression, Activator and Condition are described below.

Leave this field empty. We do not want to put event data in any Target. We simply execute an update database query at the Expression field instead.

Let's make our binding listen to the variable change event. Click on the [...] button of the Activator field to open the Select Activator dialog.

Open the Event tab (1), then locate a desired device to listen and choose the Context variable changed event item (2). The reference to this event is displayed at the bottom, in our case users.admin.devices.defaultAdminDevice:updated@.

After that our binding will be executed every time any variable is changed at the Default Admin Device. To evaluate our binding on specific changes, we now set a condition.

The Condition will check every variable change event. When the condition is false the event will be ignored. So we set the condition expression to cell({env/value}, 'variable') == 'random'. That means that the binding expression will be evaluated only when a variable change event belongs to the variable named random  in the selected activator context. Note that generic type device, which we have selected, have a number of common variable types, one of which is a random number generator useful for testing.

The Expression is evaluated every time the binding is called and the condition is fulfilled. Open the Expression Editor for this field by clicking on the [...] button. At the Expression Editor dialog we will set the expression to execute an update query for the database. It is performed by calling an executeQuery function of our History Database device. The query will add a new record to the variable_history table and is described in detail below.

• We will use the callFunction(String context, String function, Object parameter1, Object parameter2, ...) expression language function to call the executeQuery function of the History Database device

• You can find the template for callFunction from the dialog opened by the Functions button.

• The context is a string path to the context from which the function will be called. In our case it is for the History Database device, which can be found in the Server Data tree and copied as a string into our expression.

• The path to the History Database device in our example is users.admin.devices.historyDatabase

• The function parameter is the name of the function to be called form the specified context, in our case executeQuery. This function belongs to the SQL Database History Database context.

• parameter1 (query) is the second parameter of the calling function. For the executeQuery function, it is the query string:

• Our example query follows the pattern insert into variable_history(id, name, time, value) values(..., ..., ..., ...) Real values should be placed instead of '...'. We get the real values from our expression environment. It will hold a variable changed event parameters in our case because we set this binding to be evaluated on the event.

• {env/id} refers to the event Id. cell({env/value}, "variable") gets the variable name from event data.

• formatDate({env/time}, "YYYY-MM-dd HH:MM:s.SS") takes the event time and formats it to the database acceptable time string.

• cell(cell({env/value}, "value"), "value") obtains the changed value from event data, takes a table that is a changed variable value and gets the value field of the variable value.

7. parameter2 (update) - the second parameter of the executeQuery indicates if this query is an update query. In our case it is true, meaning that query should not return any value.

Apply the expression by pressing OK button and then press OK button at the previous Model Configuration dialog to save our binding changes to the model.

Now if everything was done correctly, as the random variable of the Default Admin Device changes, they will be stored to the value_history table of the history_db database.

You can check if there are some binding execution errors by looking at the External Event Storage model's related events. Simply choose Monitor Related Events from the model's context menu in the System tree. The opened Event Log can be used for realtime debugging.

Finally we can examine our results by opening History Database configurations. Open the context menu of the History Database node from the System Tree and select Configure Device to open a window with available queries Variable History. From here, we choose our "Variable History" query defined in step 1.

We see the variable history which has been saved in our external database:

Was this page helpful?

Tell us more

Send

Thank you!