Web DB Server

A Linux LAMP server on a ARM processor (64bit Quad Core @ 1.2GHz) in a Raspberry Pi 3. The stack in this case consists of;

  • Linux-raspi2 (5.3.0-1027-raspi2)(Ubuntu 18.04)
  • Apache/2.4.29 
  • 10.1.44-MariaDB
  • PHP 7.2

PHP is used to store then display the data. Apache for the heavy lifting.

API

The REST API is in a simple single PHP file. This is the interface to interact with the database. It accepts POSTs from the gateway and returns data in response to GETs. The API provides the ability to;

  • Add sensor Data
  • Track Gateway Heartbeat
  • Create alerts based on thresholds
  • Device info
  • gain current data for APRS WX update
  • testing

Using Apache with the rewrite module, all api requests are redirect to api.php that handles them. You end up with nice clean interface. This is not an example of best practices or even reasonable coding. Its a routine that evolved as I was experimenting to see what these devices are capable of and as a distraction during COVID restrictions.

RewriteOptions inherit

DirectoryIndex index.php
php_flag log_errors On
php_value error_log /tmp/API.log

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule api/(.*)$ api/api.php?request=$1 [QSA,NC,L]
RewriteRule (recipe/.*) api/app.php?request=$1 [QSA,NC,L]
</IfModule>

The call formats are along the lines of

  • POST
    • http://Dolos/api/ADDNEW
      • {“id”:6,”8″:50,”9″:48.99023,”18″:971.7467}
  • GET
    • http://Dolos/api/APRSDATA
    • http://Dolos/api/DEVICE/8
  • PUT
    • http://Dolos/api/UPDATERSSI/DEBUG/-40/6

where Dolos is the host name. Dolos in this case, is the spirit of trickery. He is also a master at cunning deception, craftiness, and treachery. His female counterpart is Apate, who is the goddess of fraud and deception. She is a Rasberry Pi 4 with 2G memory currently connected to a RTLSDR.

Data is passed using JSON. POST in the header and returned for GET. RSSI is also passed as a value for some POST calls. The intent was to use the RSSI as data during heartbeat updates. Whilst the values returned by LoRa and WiFi are not comparable, it is an indication of the strength of the connectivity, the greater the RSSI value, the stronger the signal. Typical LoRa RSSI values are typically -50 down to -120 dBm. Anyway it may be worth looking into later so left in.

Database Tables

  • Sensors. Contains the sensor information including device and type and is updated with Max and Min valyes as posted
  • sensorType. Inspired by the IPSO Alliance’s “smart objects”. The sensor In that framework, smart objects are virtual functions or controls in a physical device.
  • Devices. Contains device information including type, desc, RSSI and status if participating in a HA arrangement.
  • deviceLocation. To track moving devices. Not used at the present.
  • sensorData. This contains the raw data from each update for each sensor
  • reportData. Updated on receiving sensor data for names types (magic numbers so yes bad) to feed the Gauges and Line Chart.
  • alertData. Updated on receiving data when new value breaches previous max, min or defined event (lightning)

Data flows

The nodes read their attached sensors, encapsulated in JSON then broadcast via LoRa. Two gateway devices both receive the packet. The Primary will send to the database for processing whilst the Secondary sends to the Web device and it is written to a file. This supports testing.

Once sensor data is received the database is updated (sensorData, reportData and alertData)

Once the data is sent the Gateway device will then update the database with the RSSI of the last packet (LoRa RSSI) which keeps track of active devices. Then the Gateway will query the database to see what its role is. The secondary will be promoted to Primary if no up[dates for defined period.

Current conditions are displayed on the main web page. This data is drawn from reportData with the raw sensorData available.

Simple PHP driven web app.

Files

Below are the sources for each. This is not a example of best practice. I intend to take much further so there will be updates to improve the HA and reporting when the revised external station is deployed. Provided for commentary.

Conclusion

Simple procedural process that received, stores and displays sensor data. APRS WX station is updated with summarised data hourly. This is a convenient way to see conditions when not at home and I don’t have to send into a IoT cloud.

Tags :