Top Internet of Things Daily & Weekly

developerWorks Open

Node-RED nodes bring #GaianDB to the Internet of Things #IoT

  • Table: LT0 – returns data from all databases in the Gaian network with the LT0 table.
  • Table: LT0_0 – returns data from the single database to which Node Red is connected (this is a local query) Table: LT0_P – returns data from all databases in with the LT0 table and includes “provenance columns” identifying from which data source the data originated.
  • “GaianQuery” functionality, where a query is propagated and executed separately on each node in the Gaian network, can be specified using this module.
  • The node queries a Gaian database based on a configured SQL query string.
  • You can either set the table (the modification target of the query) in the node configuration or on the incoming message.

GaianDB nodes for Node-RED, which use GaianDB as a data source, can now be installed from NPM, the JavaScript package manager.

@About_IoT: Node-RED nodes bring #GaianDB to the Internet of Things #IoT

Node-RED is a popular tool for wiring together hardware devices, APIs, and online services in new and interesting ways. It provides a browser-based flow editor that makes it easy to wire together flows using the wide range of nodes from a built-in library, specifying what processing is performed when data is available.

Node-RED can be extended with user-created nodes, and I have just added nodes to use GaianDB as a data source. These GaianDB nodes for Node-RED are publicly available and can be installed from NPM, the JavaScript package manager.

Here is an example flow using the Node-RED nodes (click on image for a larger view):

There are three Node-RED nodes:

Gaiandb in – queries a gaiandb table, returning the data, or a count of the matching rows.

Gaiandb out – can insert, update and remove objects from a chosen local gaian table.

Gaiandb sql – executes any sql query within the gaian environment, allows complex queries.

You can choose “Select” or “Count” as the operator. “Select” queries a logical table and allows you to set the SQL “where” clause and the fields to include in the output. “Count” returns a count of the number of rows in a logical table or matching a filter condition.

The result is returned in msg.payload. The Output parameter determines whether the results are returned as individual messages or a single array in one message.

In line with standard Gaian Query functionality, specifying table aliases allow a query to be local or global, or to return provenance columns.

Table: LT0 – returns data from all databases in the Gaian network with the LT0 table.

Table: LT0_0 – returns data from the single database to which Node Red is connected (this is a local query)

Table: LT0_P – returns data from all databases in with the LT0 table and includes “provenance columns” identifying from which data source the data originated.

You can choose “Insert”, “Update”, or “Delete:”as the operator. “Insert” will insert a new object based on the incoming msg.payload. “Update” will modify an existing object or multiple objects to the values in the incoming msg.payload, based on a filter condition which can be specified as the msg.filter property. “Delete” will remove objects that match the conditions passed in the incoming message.

You can either set the table (the modification target of the query) in the node configuration or on the incoming message. A table specified in the configuration will be used in preference to a table in the message.

This node queries a Gaian database based on a configured SQL query string. The query can be specified as a template, including “mustache” format tags, which are then substituted for values from the input message.

Example 1: SELECT * FROM {{{payload.logicaltable}}} WHERE ID = ‘{{{payload.id}}}’ substitutes msg.payload.logicaltable and msg.payload.id into the query.

Example 2: {{{msg.sql}}} will execute the SQL specified in the sql property of the incoming message, allowing the node red flow to formulate the query.

The result is returned in the outgoing message payload. The results can be returned as individual messages or a single array in one message.

“GaianQuery” functionality, where a query is propagated and executed separately on each node in the Gaian network, can be specified using this module. Example queries are:

Gaian-specific “Stored Procedures” can also be called using this module. Following are some query examples:

Show logical table definitions in the whole network

Show all configuration properties and their values.

Show all stored procedures and functions.

Server configurations

Each Gaian node has a link to a shared Server configuration. A number of Server configurations can be set to allow access to multiple Gaian databases. Each Server configuration specifies the server, database and credentials to access.

Gaian Database is a lightweight data-federation technology that fits into a variety of computing domains. It accesses and transforms data “at the edge” — on clients or edge devices — in distributed and disparate data sources, and then merges results into a “logical table” with a consolidated structure.

As our Node-RED work shows, we’re making updates and improvements all the time. We invite you to discover and use this valuable data-federation technology. Feedback and contributions are welcome!

developerWorks Open