IoTool Users, Sensors, Readings

Introduction

This document consolidates the workflows related to PostgreSQL database interactions within the IoTool framework, specifically designed for sandbox environments. It encompasses three main operations, each focusing on different aspects of data handling and retrieval:

  • IoTool Users: Processes incoming data to construct an SQL query for retrieving information about the current PostgreSQL user.

  • IoTool Sensors: Focuses on the _sensors table, handling incoming sensor data and interacting with the PostgreSQL database for data retrieval.

  • IoTool Readings: Manages incoming data from IoT devices to fetch sensor readings from the database.

These operations demonstrate the integration of custom functions within Node-RED, querying a PostgreSQL database, basic error handling, and HTTP response mechanisms.

Common Components and Processes

Handling HTTP GET Requests

Unlike the typical POST request handling, these flows utilize GET requests to transfer data. GET requests embed data within the URL and headers, necessitating the extraction of user credentials and other data elements from the headers for dynamic SQL query formulation.

While many of our examples utilize POST requests to transfer data, this section focuses on handling a GET request. Unlike POST requests, which carry data within the message body, GET requests encode data within the URL and headers. This example demonstrates how to extract user credentials from the request headers using basic authentication. Additional data elements are also retrieved from the headers. It is crucial to replace the placeholder username and password with the actual credentials corresponding to your PostgreSQL database. The extracted information is then employed to formulate a dynamic SQL query, enabling the retrieval of data entries from the specified table.

Example of GET request to send:

{
    "info": {
        "_postman_id": "3c9c6706-93a9-4c4a-bce9-c48f65bc706a",
        "name": "New Collection",
        "schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json",
        "_exporter_id": "33058006"
    },
    "item": [
        {
            "name": "iotool_readings",
            "protocolProfileBehavior": {
                "disableBodyPruning": true
            },
            "request": {
                "auth": {
                    "type": "basic",
                    "basic": [
                        {
                            "key": "password",
                            "value": "password",
                            "type": "string"
                        },
                        {
                            "key": "username",
                            "value": "username",
                            "type": "string"
                        }
                    ]
                },
                "method": "GET",
                "header": [],
                "body": {
                    "mode": "urlencoded",
                    "urlencoded": [
                        {
                            "key": "un",
                            "value": "username",
                            "type": "text"
                        },
                        {
                            "key": "up",
                            "value": "password",
                            "type": "text"
                        },
                        {
                            "key": "db",
                            "value": "localhost",
                            "type": "text"
                        },
                        {
                            "key": "sensorcount",
                            "value": "1",
                            "type": "text"
                        },
                        {
                            "key": "db0",
                            "value": "senzor1",
                            "type": "text"
                        },
                        {
                            "key": "data0",
                            "value": ",1#,2#,3",
                            "type": "text"
                        }
                    ]
                },
                "url": {
                    "raw": "http://test.sandbox.engineering:1880/api/iotool_readings?sensor_id=123&min_datetimems=1609459200000&max_datetimems=1609545600000",
                    "protocol": "http",
                    "host": [
                        "test",
                        "sandbox",
                        "engineering"
                    ],
                    "port": "1880",
                    "path": [
                        "api",
                        "iotool_readings"
                    ],
                    "query": [
                        {
                            "key": "sensor_id",
                            "value": "123"
                        },
                        {
                            "key": "min_datetimems",
                            "value": "1609459200000"
                        },
                        {
                            "key": "max_datetimems",
                            "value": "1609545600000"
                        }
                    ]
                }
            },
            "response": []
        }
    ]
}

PostgreSQL User Authentication

A reusable node (iotool_v1_funct) facilitates the execution of database queries for user authentication across all flows. This process involves initializing a pgConfig object within the msg payload, containing essential login details and database location. This node or function is called from another Node-RED flow named Iotool_funct.

Retrieving Data from Database

Each flow implements a straightforward SQL search algorithm to retrieve relevant data from the database, whether it be user information, sensor data, or specific readings.

Data Aggregation and HTTP Response

Incoming data is consolidated through a function node, organizing the data into a coherent string and counting the entries. The compiled data is then returned to the initiator of the GET request through an HTTP response subflow.

Unique Aspects of Each Flow

  • IoTool Users: Specifically constructs SQL queries to retrieve information about the current PostgreSQL user, highlighting user login and data retrieval processes.

  • IoTool Sensors: Targets the _sensors table, focusing on sensor data structure and retrieval, and emphasizes the interaction with the PostgreSQL database for fetching sensor-specific data.

  • IoTool Readings: Aims at fetching sensor readings from the database, detailing the process of structuring sensor data for GET requests and merging data for HTTP responses.

Main difference between all 3 flows is in Log result function node, which uses different techniques for appending strings, which are used for data presentation.

Conclusion

This unified documentation provides a comprehensive overview of the IoTool flows related to PostgreSQL database interactions, emphasizing their shared foundations in handling HTTP GET requests, user authentication, data retrieval, and response management. Understanding the specific functionalities and configurations of these flows allows for effective utilization of the IoTool suite for diverse database interaction tasks within sandbox environments.