o Better sql generation.

1 files changed, 190 insertions, 0 deletions

diff --git a/sql/raw-schema.sql b/sql/raw-schema.sql
new file mode 100644
index 0000000..651003d
--- /dev/null
+++ b/sql/raw-schema.sql
@@ -0,0 +1,190 @@
+DROP TABLE IF EXISTS raw_table CASCADE;
+DROP TABLE IF EXISTS raw_incoming CASCADE;
+DROP TABLE IF EXISTS raw_processed CASCADE;
+DROP TABLE IF EXISTS sample_second CASCADE;
+
+DROP TABLE IF EXISTS sample_hour;
+DROP TABLE IF EXISTS sample_hour_invalid;
+
+DROP TABLE IF EXISTS log;
+
+CREATE TABLE raw_table (
+  timestamp             VARCHAR(1000),
+  now                   VARCHAR(1000),
+  last_watering_stopped VARCHAR(1000),
+  analog                VARCHAR(1000),
+  dry                   VARCHAR(1000),
+  pumping_water         VARCHAR(1000),
+  last_watering_started VARCHAR(1000),
+
+  millis VARCHAR(1000),
+  "#0" VARCHAR(1000),
+  "#1" VARCHAR(1000)
+);
+
+CREATE OR REPLACE VIEW raw AS
+  SELECT *
+  FROM raw_table;
+
+CREATE TABLE sample_second (
+  ts                    TIMESTAMP,
+  last_watering_started TIMESTAMP,
+  last_watering_stopped TIMESTAMP,
+  analog                INT2,
+  dry                   BOOLEAN,
+  pumping_water         BOOLEAN
+);
+
+CREATE TABLE sample_hour (
+  ts         TIMESTAMP PRIMARY KEY,
+  analog_avg INT2 NOT NULL,
+  analog_max INT2 NOT NULL,
+  analog_min INT2 NOT NULL
+);
+
+CREATE TABLE sample_hour_invalid (
+  hour         TIMESTAMP NOT NULL PRIMARY KEY,
+  created_date TIMESTAMP NOT NULL DEFAULT now(),
+  updated_date TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE OR REPLACE FUNCTION raw_instead_of_function()
+  RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  hr  TIMESTAMP;
+  cnt INT;
+BEGIN
+  IF TG_OP = 'INSERT'
+  THEN
+    INSERT INTO sample_second (
+      ts,
+      last_watering_started,
+      last_watering_stopped,
+      analog,
+      dry,
+      pumping_water)
+    VALUES (
+      to_timestamp(new.timestamp :: DOUBLE PRECISION),
+      to_timestamp(new.timestamp :: DOUBLE PRECISION + new.last_watering_started :: BIGINT - new.now :: BIGINT),
+      to_timestamp(new.timestamp :: DOUBLE PRECISION + new.last_watering_stopped :: BIGINT - new.now :: BIGINT),
+      new.analog :: INT2,
+      new.dry = '1',
+      new.pumping_water = '1')
+    RETURNING date_trunc('hour', ts)
+      INTO hr;
+
+    UPDATE sample_hour_invalid
+    SET hour = hour, updated_date = now()
+    WHERE hour = hr;
+
+    GET DIAGNOSTICS cnt = ROW_COUNT;
+
+    IF cnt = 0
+    THEN
+      INSERT INTO sample_hour_invalid (hour) VALUES (hr);
+    END IF;
+
+    RETURN NEW;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+CREATE TRIGGER raw_instead_of_trigger
+INSTEAD OF INSERT OR UPDATE OR DELETE ON
+  raw FOR EACH ROW EXECUTE PROCEDURE raw_instead_of_function();
+
+CREATE TABLE log (
+  ts      TIMESTAMP DEFAULT now(),
+  message VARCHAR(1000)
+);
+
+CREATE INDEX ix_log__timestamp ON log (ts);
+
+CREATE OR REPLACE FUNCTION info(message VARCHAR)
+  RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  INSERT INTO log (message) VALUES (message);
+END
+$$;
+
+CREATE OR REPLACE FUNCTION invalidate_all_hours()
+  RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  TRUNCATE sample_hour_invalid;
+  INSERT INTO sample_hour_invalid (hour)
+    SELECT DISTINCT date_trunc('hour', ts)
+    FROM sample_second;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION update_hours()
+  RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    hour_c CURSOR FOR SELECT hour
+                      FROM sample_hour_invalid
+                      ORDER BY hour;
+
+    second_c CURSOR (hr TIMESTAMP) FOR SELECT
+                                         ts,
+                                         analog
+                                       FROM sample_second
+                                       WHERE date_trunc('hour', ts) = hr;
+BEGIN
+  FOR hour_rec IN hour_c LOOP
+
+    EXECUTE info('Processing hour ' || hour_rec.hour);
+
+    DELETE FROM sample_hour
+    WHERE date_trunc('hour', ts) = date_trunc('hour', hour_rec.hour);
+
+    INSERT INTO sample_hour (ts, analog_avg, analog_max, analog_min)
+      SELECT
+        hour_rec.hour,
+        avg(analog) AS analog_avg,
+        max(analog) AS analog_max,
+        min(analog) AS analog_min
+      FROM sample_second
+      WHERE date_trunc('hour', ts) = date_trunc('hour', hour_rec.hour)
+      GROUP BY date_trunc('hour', ts)
+      HAVING avg(analog) IS NOT NULL;
+
+    DELETE FROM sample_hour_invalid
+    WHERE hour = date_trunc('hour', hour_rec.hour);
+  END LOOP;
+
+END;
+$$;
+
+TRUNCATE log;
+SELECT invalidate_all_hours();
+SELECT update_hours();
+
+SELECT *
+FROM log
+ORDER BY ts DESC;
+
+SELECT *
+FROM sample_hour
+ORDER BY ts;
+
+SELECT *
+FROM sample_second;
+
+
+INSERT INTO raw(timestamp, "millis", "#1", "#0") VALUES(1115015587, 101629234, 531, 1021);
+
+select count(*) from log;
+
+select * from;
+
+SELECT DISTINCT date_trunc('hour', ts)
+FROM sample_second
\ No newline at end of file