Database Interface

Supported Databases

• SQlite
• MySQL

Tables, Views and Indexes

The database definition is stored in the YAML-file data/schema.yml.

Tables

The rsensor-database consists of four tables:

• nodes
• locations
• sensors
• timeseries

The following diagram shows the relations between the table fields.

The table nodes stores node specific information (see MQTT-info topic):

mysql> select * from nodes;
+----------+----------+--------------+---------------+------------------+---------+
| node_idx | id       | location_idx | board         | firmware         | version |
+----------+----------+--------------+---------------+------------------+---------+
|        1 | 36004b00 |            1 | esp8266+bm280 | TempHumLogger.py | 1.00    |
+----------+----------+--------------+---------------+------------------+---------+
1 row in set (0.00 sec)

The field location_idx references to the table locations.

The table locations stores a list of all seen locations, based on the field loctag:

mysql> select * from locations;
+--------------+----------+-------------+-------------+
| location_idx | loctag   | geolocation | description |
+--------------+----------+-------------+-------------+
|            1 | WH.KG.K2 | NULL        | NULL        |
+--------------+----------+-------------+-------------+
1 row in set (0.00 sec)

Each node serves one or sensors, all of these are stored in the table sensors:

mysql> select * from sensors;
+------------+---------------+-----------+-------------+-------------+-------+-------+
| sensor_idx | id            | node_name | sensor_name | description | type  | unit  |
+------------+---------------+-----------+-------------+-------------+-------+-------+
|          1 | 36004b00_temp | 36004b00  | temp        | NULL        | float | �C    |
|          2 | 36004b00_rh   | 36004b00  | rh          | NULL        | float | %     |
|          3 | 36004b00_p    | 36004b00  | p           | NULL        | float | hPa   |
|          4 | 36004b00_ah   | 36004b00  | ah          | NULL        | float | g/m^3 |
|          5 | 36004b00_svp  | 36004b00  | svp         | NULL        | float | hPa   |
+------------+---------------+-----------+-------------+-------------+-------+-------+
5 rows in set (0.00 sec)

The individual measurement values are stored in table timeseries:

mysql> select * from timeseries;
+-----+------------+------------+--------------+---------+---------+
| idx | ts         | sensor_idx | location_idx | value   | invalid |
+-----+------------+------------+--------------+---------+---------+
|   1 | 1586237223 |          4 |            1 | 6.21505 |       0 |
|   2 | 1586237223 |          2 |            1 |      34 |       0 |
|   3 | 1586237223 |          5 |            1 | 24.8118 |       0 |
|   4 | 1586237223 |          1 |            1 |   20.97 |       0 |
|   5 | 1586237223 |          3 |            1 |    1019 |       0 |
|   6 | 1586237258 |          4 |            1 | 6.21867 |       0 |
|   7 | 1586237258 |          2 |            1 |      34 |       0 |
|   8 | 1586237258 |          5 |            1 | 24.8271 |       0 |
|   9 | 1586237258 |          1 |            1 |   20.98 |       0 |
|  10 | 1586237258 |          3 |            1 |    1019 |       0 |
+-----+------------+------------+--------------+---------+---------+
10 rows in set (0.00 sec)

Views

Todo

add support views in schema.yml

Indexes

Todo

describe indexes

SQlite Commands

The initial content of the SQLite database can be explored with the commands .tables and .schema <tablename>:

sqlite> .tables
locations   nodes       sensors     timeseries
sqlite> .schema nodes
CREATE TABLE `nodes`
(
   node_idx INTEGER PRIMARY KEY NOT NULL  /*!40101 AUTO_INCREMENT */,
   id VARCHAR(64) DEFAULT NULL,
   location_idx INTEGER DEFAULT -1,
   board VARCHAR(64) DEFAULT NULL,
   firmware VARCHAR(64) DEFAULT NULL,
   version VARCHAR(64) DEFAULT NULL
);
sqlite>

If data was received, the table timeseries contains data:

sqlite> select * from timeseries

idx         ts          sensor_idx  location_idx  value       invalid
----------  ----------  ----------  ------------  ----------  ----------
1           1586844220  4           1             5.68866     FALSE
2           1586844220  2           1             29.0        FALSE
3           1586844220  5           1             26.7363     FALSE
4           1586844220  1           1             22.19       FALSE
5           1586844220  3           1             1010.0      FALSE
6           1586844352  4           1             5.46655     FALSE
7           1586844352  2           1             29.0        FALSE
8           1586844352  5           1             25.6324     FALSE
9           1586844352  1           1             21.5        FALSE
10          1586844352  3           1             1011.0      FALSE

More about the representation of the data in the timeseries table can be found in section Database Interface.

MySQL Commands

Query a specific sensor value:

select from_unixtime(t.ts), s.id, t.value, s.unit from timeseries as t join sensors as s on t.sensor_idx = s.sensor_idx where s.id like "%ah";

SELECT
    FROM_UNIXTIME(t.ts), s.id, t.value, s.unit
FROM
    timeseries AS t
        JOIN
    sensors AS s ON t.sensor_idx = s.sensor_idx
WHERE
    s.id LIKE '%ah';