commit 0e22ff2d1919f92710845813cdaa3e50213c5fd6
parent 89cf478e17e9c7fef4214bf8c45e222b710ebb8e
Author: Chris Bracken <chris@bracken.jp>
Date: Fri, 12 Nov 2021 22:59:32 -0800
Add sensors table and save_reading stored proc
This provides a simple transactional mechanism for inserting new
readings into the database and associating them with a record in the
sensors table.
Diffstat:
2 files changed, 29 insertions(+), 5 deletions(-)
diff --git a/setup_db.sql b/setup_db.sql
@@ -11,3 +11,28 @@ CREATE TABLE sensor_data (
);
GRANT SELECT, INSERT ON sensor_data TO tempestas;
+
+CREATE TABLE sensors (
+ sensor_id VARCHAR(63) PRIMARY KEY,
+ name VARCHAR(20) NOT NULL
+);
+
+GRANT SELECT, INSERT ON sensors TO tempestas;
+
+CREATE OR REPLACE PROCEDURE save_reading(
+ new_sensor_id VARCHAR(63),
+ new_sw_version VARCHAR(63),
+ new_reading_time TIMESTAMP,
+ new_reading_type VARCHAR(63),
+ new_reading_value NUMERIC(20,6)
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ INSERT INTO sensors(sensor_id, name) VALUES(new_sensor_id, 'Unknown') ON CONFLICT DO NOTHING;
+
+ INSERT INTO sensor_data(sensor_id, sw_version, reading_time, reading_type, reading_value)
+ VALUES(new_sensor_id, new_sw_version, new_reading_time, new_reading_type, new_reading_value);
+
+ COMMIT;
+END$$;
diff --git a/storage/storage.go b/storage/storage.go
@@ -8,10 +8,8 @@ import (
)
const (
- INSERT_READING = "INSERT INTO " +
- "sensor_data(sensor_id, sw_version, reading_time, reading_type, reading_value) " +
- "VALUES($1, $2, $3, $4, $5)"
- QUERY_SENSORS = "SELECT DISTINCT sensor_id FROM sensor_data ORDER BY sensor_id"
+ INSERT_READING = "CALL save_reading($1, $2, $3, $4, $5)"
+ QUERY_SENSORS = "SELECT sensor_id, name FROM sensors ORDER BY sensor_id"
QUERY_READINGS = "SELECT " +
" sensor_id, sw_version, reading_time, reading_type, reading_value " +
"FROM " +
@@ -32,6 +30,7 @@ type Reading struct {
type Sensor struct {
SensorId string `json:"sensor_id"`
+ Name string `json:"name"`
}
type DataStore interface {
@@ -109,7 +108,7 @@ func (s *PostgresDataStore) QuerySensors() ([]Sensor, error) {
var sensors []Sensor
for rows.Next() {
var s Sensor
- if err := rows.Scan(&s.SensorId); err != nil {
+ if err := rows.Scan(&s.SensorId, &s.Name); err != nil {
return sensors, err
}
sensors = append(sensors, s)
