summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rwxr-xr-xsql/chart.r148
-rw-r--r--sql/populate_cache.sql45
-rw-r--r--sql/refresh_cache.sql1
-rw-r--r--sql/schema_ddl.sql361
-rw-r--r--sql/trigger.sql57
5 files changed, 0 insertions, 612 deletions
diff --git a/sql/chart.r b/sql/chart.r
deleted file mode 100755
index 5575293..0000000
--- a/sql/chart.r
+++ /dev/null
@@ -1,148 +0,0 @@
-#!/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/sql/populate_cache.sql b/sql/populate_cache.sql
deleted file mode 100644
index 2733cbb..0000000
--- a/sql/populate_cache.sql
+++ /dev/null
@@ -1,45 +0,0 @@
-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/sql/refresh_cache.sql b/sql/refresh_cache.sql
deleted file mode 100644
index 8734348..0000000
--- a/sql/refresh_cache.sql
+++ /dev/null
@@ -1 +0,0 @@
-select value_cache_aktualisieren();
diff --git a/sql/schema_ddl.sql b/sql/schema_ddl.sql
deleted file mode 100644
index 15f969b..0000000
--- a/sql/schema_ddl.sql
+++ /dev/null
@@ -1,361 +0,0 @@
---
--- 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/sql/trigger.sql b/sql/trigger.sql
deleted file mode 100644
index 2191ca0..0000000
--- a/sql/trigger.sql
+++ /dev/null
@@ -1,57 +0,0 @@
--- 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();