diff options
Diffstat (limited to 'heap/sql')
| -rwxr-xr-x | heap/sql/chart.r | 148 | ||||
| -rw-r--r-- | heap/sql/populate_cache.sql | 45 | ||||
| -rw-r--r-- | heap/sql/refresh_cache.sql | 1 | ||||
| -rw-r--r-- | heap/sql/schema_ddl.sql | 361 | ||||
| -rw-r--r-- | heap/sql/trigger.sql | 57 |
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(); |