diff options
| -rwxr-xr-x | bin/ebus_dump.py | 4 | ||||
| -rw-r--r-- | sql/trigger.sql | 44 |
2 files changed, 26 insertions, 22 deletions
diff --git a/bin/ebus_dump.py b/bin/ebus_dump.py index 2d6933c..c170849 100755 --- a/bin/ebus_dump.py +++ b/bin/ebus_dump.py @@ -36,9 +36,7 @@ class MyEbusReader(EbusReader): for field in self.sql.get_values(ebus_packet): session.add(field) - if int(time.time()) % 4 == 0: - logger.info("COMMIT") - session.commit() + session.commit() logger.info("packet %s" % ebus_packet.name()) diff --git a/sql/trigger.sql b/sql/trigger.sql index ce91f61..2191ca0 100644 --- a/sql/trigger.sql +++ b/sql/trigger.sql @@ -18,27 +18,33 @@ CREATE AGGREGATE public.first ( 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 - 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); - NOTIFY evt_ebus_value_insert; RETURN NULL; |