move old stuff away

5 files changed, 612 insertions, 0 deletions

diff --git a/heap/sql/chart.r b/heap/sql/chart.r
new file mode 100755
index 0000000..5575293
--- /dev/null
+++ b/heap/sql/chart.r
@@ -0,0 +1,148 @@
+#!/usr/bin/Rscript --vanilla
+require(RPostgreSQL)
+require(RSvgDevice)
+nulltime <- strptime("01.01.1970", "%d.%m.%Y")
+
+drv <- dbDriver("PostgreSQL")
+con <- dbConnect(drv, host="10.2.2.22", user="jack", password="jack", dbname="ebus")
+
+
+## plot 1
+#png(file = "Rplots.png", width=1500, height=800)
+devSVG(file="Rplots.svg",width=15,height=10)
+par(cex=1.25)
+cat("Starte Abfragen\n")
+brenner <- dbGetQuery(con,
+    paste("SELECT timestamp as \"time\"
+                , value_string
+             FROM value
+            WHERE sensor_id IN (select id from sensor where name = 'heizkreisregler1.betriebsdatenFeuerungsautomat.betriebszustand')
+              AND timestamp > now() - interval '1 day'",
+            " AND value_string IS NOT NULL",
+            " ORDER BY  timestamp asc", sep=""))
+
+## Brennerstatus auf Brennerstaotusänderungen reduzieren
+
+brennerBla <- table(NULL,NULL)
+liste <- c()
+old_value_string <- "bla"
+for (i in seq(1,nrow(brenner))) {
+    if (brenner$value_string[i] != old_value_string) {
+        old_value_string <- brenner$value_string[i]
+        brennerBla <- rbind(brennerBla, data.frame(time=brenner$time[i], value_string=brenner$value_string[i]))
+    }
+}
+brenner <- brennerBla
+
+#  Solar Pumpe
+solarEngine <- dbGetQuery(con,
+    paste("SELECT timestamp as \"time\",
+                  value_int
+           FROM value
+           WHERE sensor_id IN (select id from sensor where name = 'heizkreisregler9.solarDaten.solarPumpe')
+                AND timestamp > now() - interval '1 day'
+                AND value_int IS NOT NULL
+           ORDER BY timestamp asc", sep=""))
+# reduzieren
+solarEngineNew <- table(NULL,NULL)
+emptyList <- c()
+old_value_int <- "blub"
+for (i in seq(1,nrow(solarEngine))) {
+    if (solarEngine$value_int[i] != old_value_int) {
+        old_value_int <- solarEngine$value_int[i]
+        solarEngineNew <- rbind(solarEngineNew, data.frame(time=solarEngine$time[i], value_int=solarEngine$value_int[i]))
+    }
+}
+solarEngine <- solarEngineNew
+
+# Numerische Sensoren Lesen
+readNumericSensorDay <- function (sensorName) {
+    d <- dbGetQuery(con,
+        paste("SELECT timestamp",
+              "     , COALESCE(value_int,value_float) \"value\"",
+              "  FROM value",
+              " WHERE sensor_id IN (select id from sensor where name = '",sensorName,"')",
+              "   AND timestamp > now() - interval '1 day'",
+              # ausreißer ausblenden
+              "   AND COALESCE(value_int,value_float) < 200",
+              "   AND COALESCE(value_int,value_float) > -50",
+              " ORDER BY timestamp desc", sep=""))
+    d$timestamp <- as.POSIXct(round(d$timestamp, "mins"))
+    #d <- supsmu(as.vector(d$timestamp), as.vector(d$value))
+    names(d) <- c("time", "value")
+    
+    myMean <- function(values) {
+      if (mad(values) > 2) {
+        print(paste("Streiche ",length(values),
+                    " Werte aus",sensorName,
+                    "mad =", mad(values)))
+        values <- values * NA
+      }
+      mean(values)
+    }
+
+#    dm <- aggregate(d$value, list(time=d$time), myMean)
+    dm <- d
+    dm$time <- as.POSIXct(dm$time,origin=nulltime)
+    names(dm) <- c("time", sensorName)
+    dm
+}
+
+warmwasser <- readNumericSensorDay("heizkreisregler9.solarDaten.tempWarmwasserSolar")
+kessel <- readNumericSensorDay("heizkreisregler10.betriebsdatenRegler1.kesselTemperatur")
+kollektor <- readNumericSensorDay("heizkreisregler9.solarDaten.tempKollektor")
+aussenTemperatur <- readNumericSensorDay("feuerungsautomat1.betriebsdatenRegler1.aussenTemperatur")
+
+# -- TODO ausreißer als punkte darstellen
+data <- Reduce(function(x,y) { merge(x,y,all.x=TRUE) }, list(kollektor, kessel, warmwasser, aussenTemperatur))
+
+
+
+cat("Plotte\n")
+plot.new()
+plot.window(xlim=c(min(data$time), max(data$time)),
+            ylim=c(min(data[2:length(data)],na.rm=TRUE)*0.9, max(data[2:length(data)],na.rm=TRUE)*1.1))
+par(lwd=2)
+
+
+for (i in c(2:length(data))) {
+  lines(subset(data,select=c(1,i)),
+        col=rainbow(length(data)-1)[i-1])
+}
+box()
+
+for (i in c(1:nrow(brenner))) {
+  text(as.numeric(brenner$time[i]), max(data[2:length(data)],na.rm=TRUE)/2, paste("ab hier", brenner$value_string[i]) ,srt=90)
+}
+
+# quick and dirty add solarEngine to plot
+
+rect_start <- 0
+rect_color <- rgb(255, 255, 0, alpha=40, maxColorValue=255)
+
+for (i in c(1:nrow(solarEngine))) {
+    if (solarEngine$value_int[i] == 1) {
+        rect_start <- solarEngine$time[i];
+    } else if (rect_start != 0) {
+        end <- solarEngine$time[i];
+        # plot
+        rect(rect_start, 0, solarEngine$time[i], 100, col=rect_color,border=F)
+        beginn <- 0
+    }
+    #text(as.numeric(solarEngine$time[i]), max(data[2:length(data)],na.rm=TRUE)/2, paste("Solar Pumpe", solarEngine$value_int[i]), srt=90)
+}
+
+rangeX <- as.POSIXct(range(data$time))
+s <- round.POSIXt(seq(rangeX[1], rangeX[2], by=60*60),"hour")
+axis.POSIXct(1, at=s, format="%H:%M")
+
+r <- range(data[2:length(data)], na.rm=TRUE)
+s <- seq(r[1], r[2], by=(r[2]-r[1])/8)
+axis(2, at=s, labels=paste(round(s,1),"°C"))
+legend('topleft',
+       legend=names(data)[2:length(data)],
+       fill=rainbow(length(data)-1))
+title(paste("Temperaturen ", rangeX[1], " bis ", rangeX[2]))
+dev.off()
+
+dbDisconnect(con)
diff --git a/heap/sql/populate_cache.sql b/heap/sql/populate_cache.sql
new file mode 100644
index 0000000..2733cbb
--- /dev/null
+++ b/heap/sql/populate_cache.sql
@@ -0,0 +1,45 @@
+drop table value_cache cascade;
+
+CREATE TABLE value_cache (
+	value_real integer,
+	timestamp timestamp,
+	sensor_id integer
+);
+
+create or replace view vi_value_cache as 
+  SELECT timestamp, sensor_id, value_real, 'CACHE'
+    FROM value_cache
+UNION
+  SELECT date_trunc('hour', timestamp), sensor_id, COALESCE(avg(value_int),avg(value_float)), 'LIVE'
+    FROM value
+   WHERE date_trunc('hour', timestamp) > coalesce((select max(timestamp) from value_cache), 
+                                                  CURRENT_TIMESTAMP - interval '2 days')
+GROUP BY date_trunc('hour', timestamp), sensor_id;
+;
+
+
+CREATE OR REPLACE FUNCTION value_cache_aktualisieren() RETURNS timestamp AS $value_cache_aktualisieren$
+DECLARE
+   last_update timestamp = CURRENT_TIMESTAMP;
+BEGIN
+    select max(date_trunc('hour'::text, "timestamp"))
+      into last_update
+      from value_cache;
+
+    RAISE NOTICE 'last update=%', last_update;
+    if last_update is NULL then
+      last_update = CURRENT_TIMESTAMP - interval '20 days';
+      RAISE NOTICE 'last update set to %', last_update;
+    end if;
+
+    delete from value_cache where timestamp >= last_update;
+
+    insert into value_cache 
+        select COALESCE(avg(value_float),avg(value_int)), date_trunc('hour', timestamp), sensor_id 
+          from value
+         where date_trunc('hour', timestamp) > last_update
+      group by date_trunc('hour', timestamp), sensor_id;
+
+    RETURN last_update;
+END;
+$value_cache_aktualisieren$ LANGUAGE plpgsql;
diff --git a/heap/sql/refresh_cache.sql b/heap/sql/refresh_cache.sql
new file mode 100644
index 0000000..8734348
--- /dev/null
+++ b/heap/sql/refresh_cache.sql
@@ -0,0 +1 @@
+select value_cache_aktualisieren();
diff --git a/heap/sql/schema_ddl.sql b/heap/sql/schema_ddl.sql
new file mode 100644
index 0000000..15f969b
--- /dev/null
+++ b/heap/sql/schema_ddl.sql
@@ -0,0 +1,361 @@
+--
+-- PostgreSQL database dump
+--
+
+SET statement_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = off;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+SET escape_string_warning = off;
+
+--
+-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: pgsql
+--
+
+CREATE PROCEDURAL LANGUAGE plpgsql;
+
+
+ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO pgsql;
+
+SET search_path = public, pg_catalog;
+
+--
+-- Name: first_agg(anyelement, anyelement); Type: FUNCTION; Schema: public; Owner: ebus
+--
+
+CREATE FUNCTION first_agg(anyelement, anyelement) RETURNS anyelement
+    LANGUAGE sql STABLE
+    AS $_$
+       SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
+$_$;
+
+
+ALTER FUNCTION public.first_agg(anyelement, anyelement) OWNER TO ebus;
+
+--
+-- Name: value_cache_aktualisieren(); Type: FUNCTION; Schema: public; Owner: ebus
+--
+
+CREATE FUNCTION value_cache_aktualisieren() RETURNS timestamp without time zone
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+   last_update timestamp = CURRENT_TIMESTAMP;
+BEGIN
+    select max(date_trunc('hour'::text, "timestamp"))
+      into last_update
+      from value_cache;
+
+    RAISE NOTICE 'last update=%', last_update;
+    if last_update is NULL then
+      last_update = CURRENT_TIMESTAMP - interval '20 days';
+      RAISE NOTICE 'last update set to %', last_update;
+    end if;
+
+    delete from value_cache where timestamp >= last_update;
+
+    insert into value_cache 
+        select COALESCE(avg(value_float),avg(value_int)), date_trunc('hour', timestamp), sensor_id 
+          from value
+         where date_trunc('hour', timestamp) > last_update
+      group by date_trunc('hour', timestamp), sensor_id;
+
+    RETURN last_update;
+END;
+$$;
+
+
+ALTER FUNCTION public.value_cache_aktualisieren() OWNER TO ebus;
+
+--
+-- Name: value_insert_trigger_func(); Type: FUNCTION; Schema: public; Owner: ebus
+--
+
+CREATE FUNCTION value_insert_trigger_func() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+       BEGIN
+    NOTIFY evt_ebus_value_insert;
+ 
+    RETURN NULL;
+        END;
+$$;
+
+
+ALTER FUNCTION public.value_insert_trigger_func() OWNER TO ebus;
+
+--
+-- Name: first(anyelement); Type: AGGREGATE; Schema: public; Owner: ebus
+--
+
+CREATE AGGREGATE first(anyelement) (
+    SFUNC = first_agg,
+    STYPE = anyelement
+);
+
+
+ALTER AGGREGATE public.first(anyelement) OWNER TO ebus;
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- Name: sensor; Type: TABLE; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE TABLE sensor (
+    id integer NOT NULL,
+    name character varying,
+    description character varying
+);
+
+
+ALTER TABLE public.sensor OWNER TO ebus;
+
+--
+-- Name: sensor_id_seq; Type: SEQUENCE; Schema: public; Owner: ebus
+--
+
+CREATE SEQUENCE sensor_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+ALTER TABLE public.sensor_id_seq OWNER TO ebus;
+
+--
+-- Name: sensor_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ebus
+--
+
+ALTER SEQUENCE sensor_id_seq OWNED BY sensor.id;
+
+
+--
+-- Name: temp_value; Type: TABLE; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE TABLE temp_value (
+    id integer,
+    "timestamp" timestamp without time zone,
+    sensor_id integer,
+    type character varying(50),
+    value_float real,
+    value_int integer,
+    value_string character varying
+);
+
+
+ALTER TABLE public.temp_value OWNER TO ebus;
+
+--
+-- Name: value; Type: TABLE; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE TABLE value (
+    id integer NOT NULL,
+    "timestamp" timestamp without time zone,
+    sensor_id integer,
+    type character varying(50),
+    value_float real,
+    value_int integer,
+    value_string character varying
+);
+
+
+ALTER TABLE public.value OWNER TO ebus;
+
+--
+-- Name: value_cache; Type: TABLE; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE TABLE value_cache (
+    value_real integer,
+    "timestamp" timestamp without time zone,
+    sensor_id integer
+);
+
+
+ALTER TABLE public.value_cache OWNER TO ebus;
+
+--
+-- Name: value_id_seq; Type: SEQUENCE; Schema: public; Owner: ebus
+--
+
+CREATE SEQUENCE value_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+ALTER TABLE public.value_id_seq OWNER TO ebus;
+
+--
+-- Name: value_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ebus
+--
+
+ALTER SEQUENCE value_id_seq OWNED BY value.id;
+
+
+--
+-- Name: vi_value_cache; Type: VIEW; Schema: public; Owner: ebus
+--
+
+CREATE VIEW vi_value_cache AS
+    SELECT value_cache."timestamp", value_cache.sensor_id, value_cache.value_real, 'CACHE' FROM value_cache UNION SELECT date_trunc('hour'::text, value."timestamp") AS "timestamp", value.sensor_id, COALESCE((avg(value.value_int))::double precision, avg(value.value_float)) AS value_real, 'LIVE' FROM value WHERE (date_trunc('hour'::text, value."timestamp") > COALESCE(((SELECT max(value_cache."timestamp") AS max FROM value_cache))::timestamp with time zone, (now() - '2 days'::interval))) GROUP BY date_trunc('hour'::text, value."timestamp"), value.sensor_id;
+
+
+ALTER TABLE public.vi_value_cache OWNER TO ebus;
+
+--
+-- Name: id; Type: DEFAULT; Schema: public; Owner: ebus
+--
+
+ALTER TABLE sensor ALTER COLUMN id SET DEFAULT nextval('sensor_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: public; Owner: ebus
+--
+
+ALTER TABLE value ALTER COLUMN id SET DEFAULT nextval('value_id_seq'::regclass);
+
+
+--
+-- Name: sensor_pkey; Type: CONSTRAINT; Schema: public; Owner: ebus; Tablespace: 
+--
+
+ALTER TABLE ONLY sensor
+    ADD CONSTRAINT sensor_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: value_pkey; Type: CONSTRAINT; Schema: public; Owner: ebus; Tablespace: 
+--
+
+ALTER TABLE ONLY value
+    ADD CONSTRAINT value_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: ix_value__sensor_timestamp; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value__sensor_timestamp ON value USING btree (date_trunc('hour'::text, "timestamp"), sensor_id);
+
+
+--
+-- Name: ix_value__sensor_timestamp_day; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value__sensor_timestamp_day ON value USING btree (date_trunc('day'::text, "timestamp"), sensor_id);
+
+
+--
+-- Name: ix_value_cache_sensor_id; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_cache_sensor_id ON value_cache USING btree (sensor_id);
+
+
+--
+-- Name: ix_value_cache_timestamp_sensor_id; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_cache_timestamp_sensor_id ON value_cache USING btree ("timestamp", sensor_id);
+
+
+--
+-- Name: ix_value_sensor_id; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_sensor_id ON value USING btree (sensor_id);
+
+
+--
+-- Name: ix_value_timestamp_full; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_timestamp_full ON value USING btree ("timestamp");
+
+
+--
+-- Name: ix_value_timestamp_sensor_144; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_timestamp_sensor_144 ON value USING btree ("timestamp", sensor_id) WHERE (sensor_id = 144);
+
+
+--
+-- Name: ix_value_timestamp_sensor_146; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_timestamp_sensor_146 ON value USING btree ("timestamp", sensor_id) WHERE (sensor_id = 146);
+
+
+--
+-- Name: ix_value_timestamp_sensor_159; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_timestamp_sensor_159 ON value USING btree ("timestamp", sensor_id) WHERE (sensor_id = 159);
+
+
+--
+-- Name: ix_value_timestamp_sensor_160; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_timestamp_sensor_160 ON value USING btree ("timestamp", sensor_id) WHERE (sensor_id = 160);
+
+
+--
+-- Name: ix_value_timestamp_sensor_170; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_timestamp_sensor_170 ON value USING btree ("timestamp", sensor_id) WHERE ((sensor_id = 170) AND ("timestamp" > '2011-07-01 00:00:00'::timestamp without time zone));
+
+
+--
+-- Name: ix_value_timestamp_sensoren; Type: INDEX; Schema: public; Owner: ebus; Tablespace: 
+--
+
+CREATE INDEX ix_value_timestamp_sensoren ON value USING btree ("timestamp", sensor_id) WHERE ((((sensor_id = 159) OR (sensor_id = 144)) OR (sensor_id = 160)) OR (sensor_id = 170));
+
+
+--
+-- Name: value_insert_trigger; Type: TRIGGER; Schema: public; Owner: ebus
+--
+
+CREATE TRIGGER value_insert_trigger
+    AFTER INSERT ON value
+    FOR EACH STATEMENT
+    EXECUTE PROCEDURE value_insert_trigger_func();
+
+
+--
+-- Name: value_sensor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ebus
+--
+
+ALTER TABLE ONLY value
+    ADD CONSTRAINT value_sensor_id_fkey FOREIGN KEY (sensor_id) REFERENCES sensor(id);
+
+
+--
+-- Name: public; Type: ACL; Schema: -; Owner: pgsql
+--
+
+REVOKE ALL ON SCHEMA public FROM PUBLIC;
+REVOKE ALL ON SCHEMA public FROM pgsql;
+GRANT ALL ON SCHEMA public TO pgsql;
+GRANT ALL ON SCHEMA public TO PUBLIC;
+
+
+--
+-- PostgreSQL database dump complete
+--
+
diff --git a/heap/sql/trigger.sql b/heap/sql/trigger.sql
new file mode 100644
index 0000000..2191ca0
--- /dev/null
+++ b/heap/sql/trigger.sql
@@ -0,0 +1,57 @@
+-- createlang pgpgsql ebus
+
+CREATE TABLE value_cache ( LIKE value );
+
+DELETE FROM value_cache;
+
+-- Create a function that always returns the first non-NULL item
+CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
+       SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
+$$ LANGUAGE SQL STABLE;
+ 
+-- And then wrap an aggreagate around it
+CREATE AGGREGATE public.first (
+       sfunc    = public.first_agg,
+       basetype = anyelement,
+       stype    = anyelement
+);
+
+DROP FUNCTION value_insert_trigger_func() CASCADE;
+
+--CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$
+--       BEGIN
+--    DELETE FROM value_cache
+--          WHERE date_trunc('hour', timestamp) = 
+--                               (SELECT date_trunc('hour', max(timestamp)) FROM value)
+--                AND sensor_id = (SELECT sensor_id from value where id = (select max(id) from value));
+--
+--    INSERT INTO value_cache
+--               SELECT
+--              COALESCE(0, (select max(id)+1 from value_cache)) as "id" --XXX use seq
+--                 ,date_trunc('hour', timestamp) AS "timestamp"
+--                 ,sensor_id AS "sensor_id"
+--                 ,type AS "type"
+--                 ,AVG(value_float) AS "value_float"
+--                 ,AVG(value_int) AS "value_int"
+--                 ,first(value_string) AS "value_string"
+--                 FROM value
+--             WHERE date_trunc('hour', timestamp) = (SELECT date_trunc('hour', max(timestamp)) FROM value)
+--           AND sensor_id  = (SELECT sensor_id FROM value WHERE id = (SELECT MAX(id) FROM value))
+--          GROUP BY type, sensor_id, date_trunc('hour', timestamp);               
+--    RETURN NULL;
+--        END;
+--$$ LANGUAGE plpgsql;
+
+
+CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$
+       BEGIN
+    NOTIFY evt_ebus_value_insert;
+ 
+    RETURN NULL;
+        END;
+$$ LANGUAGE plpgsql;
+
+-- DROP TRIGGER value_insert_trigger ON value;
+
+CREATE TRIGGER value_insert_trigger
+    AFTER INSERT ON value FOR EACH STATEMENT EXECUTE PROCEDURE value_insert_trigger_func();