IoTool Readings

Introduction

The /api/iotool_readings flow is designed to process incoming data, typically from IoT devices, and interact with a PostgreSQL database for data storage and retrieval. This flow handles data through a series of steps including data transformation, database interaction, and response management based on the presence or absence of data.

Flow Overview

This documentation outlines the key components and functionalities within the IoTool Readings, focusing on:

  1. Structuring sensor data and sending it to Node-RED using HTTP GET request,

  2. PostgreSQL user login

  3. Retrieving sensor data from database

  4. Merging data to string and returning HTTP response to sender

Handling GET Requests

Purpose

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.

Configuration

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

Purpose

This segment of the workflow is dedicated to executing database queries for user authentication. It employs a reusable node, defined in an external file iotool_v1_funct, to facilitate this process. Essentially, this function initializes a pgConfig object within the msg payload, encapsulating crucial details such as login credentials and the database’s location.

Retrieving Sensor ID

A straightforward SQL search algorithm is implemented in this subflow to fetch the sensor ID, which is then conveyed via the msg object. This ID serves as a key to access further data within the database.

Data Aggregation and HTTP Response

This phase involves the consolidation of incoming data through a function node, which organizes the sensor data into a cohesive string and tallies the number of entries. Should pre-existing data be detected, the function integrates this new data, thereby refreshing the data count and signifying the presence of data where applicable. The compiled data is subsequently dispatched back to the POST request initiator via an HTTP response subflow.

Overview

The primary function of this flow is to extract data from a PostgreSQL database, contingent upon specified input parameters. It dynamically crafts SQL queries to align with the criteria delineated in the POST request, ensuring a customized data retrieval process. An HTTP response is issued to the requestor as a testament to the operation’s success.