o Adding utility to insert samples from json files.

1 files changed, 24 insertions, 0 deletions

diff --git a/apps/soil-moisture.sql b/apps/soil-moisture.sql
new file mode 100644
index 0000000..7f16200
--- /dev/null
+++ b/apps/soil-moisture.sql
@@ -0,0 +1,24 @@
+DROP TABLE IF EXISTS soil_moisture_sample;
+DROP TABLE IF EXISTS soil_moisture_sensor;
+DROP TABLE IF EXISTS soil_moisture_device;
+
+DROP SEQUENCE IF EXISTS sm_id;
+CREATE SEQUENCE sm_id;
+
+CREATE TABLE soil_moisture_device (
+  id  BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('sm_id'),
+  mac VARCHAR(100)
+);
+
+CREATE TABLE soil_moisture_sensor (
+  id     BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('sm_id'),
+  device BIGINT             NOT NULL REFERENCES soil_moisture_device,
+  sensor BIGINT             NOT NULL
+);
+
+CREATE TABLE soil_moisture_sample (
+  sensor    BIGINT NOT NULL REFERENCES soil_moisture_sensor,
+  timestamp BIGINT NOT NULL,
+  value     BIGINT NOT NULL,
+  PRIMARY KEY (sensor, timestamp)
+) WITHOUT OIDS;