IoTool Check, Get Last Time, Sensor List

Introduction

This document consolidates the workflows related to PostgreSQL database interactions within the IoTool framework, specifically designed for sandbox environments. It includes three main operations:

  • Testing user credentials (/android/iotool_v1_check)

  • Fetching the timestamp of the most recent sensor reading (/android/iotool_v1_get_last_time)

  • Displaying all sensors used by a specific user (/android/iotool_v1_sensorlist)

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

Receiving POST Requests

Each flow initiates by receiving data through a POST request to a specified endpoint. These requests contain crucial information for database operations, such as PostgreSQL username and password. It’s important to replace placeholder credentials with actual database user credentials.

Example of Post request to send:

{
  "info": {
    "_postman_id": "56ff6dea-0cd1-40b3-80ef-8a7426935ede",
    "name": "IoTool",
    "schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json",
    "_exporter_id": "4611963"
  },
  "item": [
    {
      "name": "postgres_drop_table",
      "request": {
        "method": "POST",
        "header": [],
        "body": {
          "mode": "urlencoded",
          "urlencoded": [
            {
              "key": "un",
              "value": "username",
              "type": "text"
            },
            {
              "key": "up",
              "value": "password",
              "type": "text"
            }
          ]
        },
        "url": {
          "raw": "http://dev.sandbox.engineering:1880/postgres_drop_table",
          "protocol": "http",
          "host": [
            "test.sandbox.engineering"
          ],
          "port": "1880",
          "path": [
            "android",
            "postgres_drop_table"
          ]
        }
      },
      "response": []
    }
  ]
}

Dynamic SQL Query Generation

A key component across all flows is the use of the Function node in Node-RED, which allows for the incorporation of custom JavaScript code. This facilitates the dynamic construction of SQL queries based on incoming POST request data, enabling flexible database interactions.

DB access subflow

This subflow is easy to use. It incorporates PostgreSQL user login and query execution. Query needs to be passed to this subflow in msg.query property.

Error Handling

Strategically placed Catch nodes within each flow monitor for any errors during database interactions. Detected errors are captured and relayed back to the sender of the POST request, providing essential feedback for troubleshooting.

Unique Aspects of Each Flow

  • IoTool v1 Check: Focuses on testing PostgreSQL database credentials by constructing dynamic queries for user login and responding with appropriate HTTP codes based on the outcome of the credential check.

  • IoTool v1 Get Last Time: Designed to retrieve the timestamp of the latest sensor reading from the database, showcasing the ability to fetch specific data points and return them in the HTTP response.

  • IoTool v1 Sensor List: Demonstrates simple SQL commands for listing all sensors associated with a specific user, highlighting how user data received in POST requests can be used to tailor database queries.

Conclusion

This unified documentation provides an overview of the IoTool flows related to PostgreSQL database interactions, emphasizing the shared foundations in HTTP POST request handling, dynamic SQL query generation, and error management. By understanding the specific functionalities and configurations of these flows, users can effectively utilize the IoTool suite for diverse database interaction tasks within sandbox environments.