PostgreSQL Flux Asset

Introduction

PostgreSQL is a powerful SQL database management system known for its robust architecture and comprehensive data management features. It is extensively utilized across various industries for its high perfomance, reliability, and scalability. PostgreSQL serve as a dependable backend for a diverse range of applications, from straightforward web applications to intricate enterprise-level solutions.

PostgreSQLMQTT facilitates seamless integration between PostgreSQL and various MQTT-enabled devices and services. It supports efficient data transfer from PostgreSQL to a MQTT broker and vice versa. This asset enables users to leverage MQTT for interacting with the database, allowing for both data extraction and insertion via MQTT messages. This interaction model supports real-time data processing and enhances the capabilities of IoT applications by linking them directly with PostgreSQL.

Features and Benefits

Seamless Communication: Enables seamless integration between PostgreSQL databases and MQTT clients, streamlining the exchange of data across platforms.
Real-time Monitoring: Facilitates real-time querying of the PostgreSQL through MQTT messages, enabling dynamic data retrieval and manipulation.
Easy Integration: Simplifies the integration of PostgreSQL with your IoT projects, making it straightforward to connect database services with various IoT devices.
Dynamic Query Capability: Allows users to execute SQL queries directly through MQTT, enhancing flexibility and responsiveness.

Prerequisites

Before you install and configure the asset, make sure you have the following prerequisites:

Basic understanding of the MQTT protocol.
Basic undertstanding of SQL and PostgreSQL operations.
Access to an MQTT broker compatible with your setup.
MQTT Client software (such as MQTT Explorer or similar).
Assess to a PostgreSQL database instance.

Asset installation

Using Coreflux HUB

Please refer the to general docs for asset installation through the Coreflux HUB.

Using Coreflux HUBLESS

Asset management and control are conducted using the MQTT protocol. Commands (payload) are sent to the $SYS/Coreflux/Command topic. The results of these commands are published to $SYS/Coreflux/Command/Output. The following steps will focus solely on the payload that needs to be sent.

Connect to the Coreflux MQTT Broker using your preferred client.
Login using your coreflux account:
```
-L myname@mydomain.com password`
```
Check your available assets:
```
-l
```
Install asset:
```
-I coreflux_postgresqlmqtt
```

If there were no issues during the installation you should receive a message on the $SYS/Coreflux/Command/Output:

Your coreflux_postgresqlmqtt was installed with version <version> with the <asset_guid>. Let the magic begin!

Asset configuration

Before using the asset, it must be properly configured. This configuration process entails adjusting various parameters that affect its functionality, the setup of the device, and how they interact with each other. This involves specifying the device's IP address, selecting which symbols to read from the device, among other settings. The configuration is detailed in a JSON file, divided into three main sections: MQTT Parameters, SQL Parameters and Tags. The instructions below will guide you through configuring each section. By the end, you will have a comprehensive example configuration that can be tailored to your specific needs.

⚠ : If a parameter is not included in your asset's configuration and it is not required, a default value will be applied. Incorrect configuration may cause the asset to not work as intended.

ℹ : This configuration is presented using a hubless setup as an example. The key takeaway is the understanding of the parameters and their significance. If you are configuring the asset via Coreflux Hub, the same parameters and configurations apply.

MQTT Parameters

The MQTTParameters in the JSON configuration define how to connect to an MQTT broker, specifying the communication details. The Address and Port indicate the broker's network location (in this case, 127.0.0.1 on port 1883), which is where the data will be sent to or received from. The parameters also detail authentication methods and the use of TLS for secure communication. This setup determines the pathway for data exchange between the device and the MQTT broker, facilitating the monitoring or control of device operations.

Parameter	Description	Required	Example	Default Value
Port	Port number on which the MQTT broker is running.	Yes	`1883`	1883
Address	IP address or hostname of the MQTT broker.	Yes	`"iot.coreflux.cloud"`	`"127.0.0.1"`
IsAnonymous	Indicates if the connection is anonymous (no username/password required).	No	`true`	`true`
Username	Username for authentication, if not anonymous.	No	`""` (empty string)	`""` (empty string)
Password	Password for authentication, if not anonymous.	No	`""` (empty string)	`""` (empty string)
WithTLS	Specifies whether TLS encryption is enabled for secure communication.	No	`true`	`false`
ClientId	Used to uniquely to identify the client to the MQTT broker.	Yes	`Client1`	`Random.Name.Generator`
Example:

{
  "MqttParameters": {
    "Port": 1883,
    "Address": "iot.coreflux.cloud",
    "IsAnonymous": true,
    "Username": "",
    "Password": "",
    "WithTLS": false,
    "ClientId": "ClientId1"
  }
}

SQL Parameters

The SQL in the JSON configuration outline the setup for SQL communication between the asset and a PostgreSQL database. These parameters are crucial for establishing a connection to the database, enabling the asset to execute queries and manage data effectively. They include specifying the server address, port, database name, and credentials for authentication. This configuration ensures the asset can interact with the database securely and efficiently. Additionally, the query timeout setting helps manage the perfomance of data interactions, preventing long waits in case of database response delays.

Parameter	Description	Required	Example	Default Value
ServerAddress	SQL Server IP Address.	Yes	`"127.0.0.1"`	`"127.0.0.1"`
ServerPort	SQL Server Port.	Yes	`5432`	`1893`
DataBaseName	Name of the database to connect to.	Yes	`"example-database"`	`"DB"`
Username	Username for authentication with the SQL server.	Yes	`"user"`	`"user"`
Password	Password for authentication with the SQL server.	Yes	`"pass"`	`"pass"`
QueryTimeoutInSeconds	Maximum duration in seconds before a query is terminated if not done.	Yes	`10`	`10`

Example:

{
    "MqttParameters": {
    "Port": 1883,
    "Address": "iot.coreflux.cloud",
    "IsAnonymous": true,
    "Username": "",
    "Password": "",
    "WithTLS": false,
    "ClientId": "ClientId1"
  },
  "SQL": {
    "ServerAddress": "test.address",
    "ServerPort": 5432,
    "Databasename": "example-database",
    "Username": "user",
    "Password": "pass",
    "QueryTimeoutInSeconds": "10"
    }

}

Data Insertion Guide

This Asset supports multiple data formats for inserting records into the data table: Raw, CSV, and JSON. Below is a quick reference guide on how to send data in each format.

Format	SQL Query	Payload
Raw	`INSERT INTO table(age) VALUES ({value});`	`15`
CSV	`INSERT INTO table(name,position) VALUES ('{value.csv.name}', '{value.csv.position}');`	`name, position` `Rita, Developer`
JSON	`INSERT INTO table(age, name) VALUES ({value.json.age}, '{value.json.name}');`	`{ "age": 25, "name": "John"}`

Explanation

Raw: The value is passed directly into the SQL query.
CSV: Data is formatted as CSV, with field names and values separated by new lines.
JSON: Data is sent in JSON format, and the fields are referenced in the SQL query using dot notation.

Saving the configuration

Asset management and control are conducted using the MQTT protocol. Commands (MQTT payload) are sent to the $SYS/Coreflux/Command topic. The results of these commands are published to $SYS/Coreflux/Command/Output. The following steps will focus solely on the payload that needs to be sent.

ℹ : Configuration requires the user to be logged in and have an asset installed. If these requirements are not met, please refer to the installation section.

Save configuration:
```
-assetConfigSave <asset_guid> <configuration>`
```
ℹ : The asset asset_guid can be obtained by consulting the $SYS/Coreflux/Assets topic.

Example:

-assetConfigSave assetName {
  "MqttParameters": {
    "Port": 1883,
    "Address": "iot.coreflux.cloud",
    "IsAnonymous": true,
    "Username": "",
    "Password": "",
    "WithTLS": false,
    "ClientId": "ClientId1"
  },
  "SQL": {
    "ServerAddress": "test.address",
    "ServerPort": 3306,
    "Databasename": "example-database",
    "Username": "user",
    "Password": "pass",
    "QueryTimeoutInSeconds": "10"
    },
  "Tags": [
    {
      "Name": "TemperatureData",
      "Route": 0,
      "Publish": 0,
      "PublishCycle": 350,
      "MqttTopic": "room/temp",
      "MqttRetain": true,
      "MqttFeedbackTopic": "feedback/topic",
      "Query": "SELECT temperature FROM data",
      "OutputType": 1,
      "QualityOfService": 0
    }
  ]
}

Check if the configuration was saved (optional) :
```
-assetConfigLoad <asset_guid>
```

Using the asset

⚠ : It is important to understand that the flow of data between the broker and the device highly depends on the configuration of both the asset and the device. In case of unexpected results, please verify the configuration and/or check the logs for any possible errors.

With Coreflux HUB

Please refer the to general docs for asset installation through the Coreflux HUB.

With Coreflux HUBLESS

Asset management and control are conducted using the MQTT protocol. Commands (MQTT payload) are sent to the $SYS/Coreflux/Command topic. The results of these commands are published to $SYS/Coreflux/Command/Output. The following steps will focus solely on the payload that needs to be sent.

ℹ : The asset guid can be obtained by consulting the $SYS/Coreflux/Assets topic.

Run asset

Run asset:

-R <asset_guid>

Stop asset

Stop asset:

-S <asset_guid>

Logs

Logs are essential for monitoring and debugging your system. They provide insight into the operation of your assets and can help identify and resolve issues promptly.

With Coreflux HUB

On Coreflux HUB, the logs are displayed in the "Log" section of the asset configuration.

With MQTT Explorer

To display logs in MQTT Explorer, follow these steps:

Publish the following command to the $SYS/Coreflux/Command topic:

-addTraceLog topic=topic/to/show/log level=Error/Information/Warning messageContains=(assetID)

2. The logs will be shown on the $SYS/Coreflux/Log/Traces/topic/to/show/log topic.

Example1:

To display informational logs, use:

-addTraceLog topic=log/inf level=Information

Note : This will display the Logs "Information" of all the assets

Example2:

To display error logs, from a specific asset, use:

-addTraceLog topic=log/mysql/err level=Error messageContains=assetID

Note : This will display the Logs "Error" of a specific asset

Note: To display all three types of logs (Error, Information, and Warning), you will need to repeat this process three times, one for each log level. three times, one for each log level.

Parameter	Description	Required	Example	Default Value
Name	Unique Tag identifier.	Yes	`"TemperatureData"`	`"TagName"`
Route	Specifies the direction of data flow within the system. Acceptable values are: 'ToSubscribers' (0), 'ToOther' (1).	Yes	`0`	`1`
Publish	Chose how data is sent. Acceptable values are: 'Update' (0), 'Cyclic' (1), 'Once' (2)	Yes	`0`	`2`
PublishCycle	Specifies the interval for publishing messages when 'publish' is set to 'Cyclic'. Must be a value between 1 and 86,400 seconds (24 hours).	No	`500`	`1`
MqttTopic	Unique topic connecting with the specified Register.	Yes	`"room/temp"`	`"mqtt/topic"`
MqttRetain	Defines if the message should be retained. Options are: true or false.	Yes	`true`	`false`
MqttFeedbackTopic	Unique topic that will receive the feedback from SQL query execution.	Yes	`"feedback/topic"`	`"feedback/topic"`
Query	The SQL query string that is to be executed.	Yes	`INSERT INTO data(temp) VALUES ({value.csv.temp})`	`"SELECT * FROM table"`
OutputType	The format of the SQL query result. Options are: Raw (0), Csv(1), and Json(2).	Yes	`2`	`0`
QualityOfService	Defines the level of delivery assurance for MQTT messages. The options are: AtMostOnce (0), AtLeastOnce(1), ExactlyOnce(1).	Yes	`2`	`0`