From c5fb76f07ec013e90c2ff41b7439b21402002788 Mon Sep 17 00:00:00 2001
From: Random Hacker <random_hacker@xapek.org>
Date: Thu, 14 Jul 2011 22:54:38 +0200
Subject: cache

---
 populate_cache.sql | 44 ++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 44 insertions(+)
 create mode 100644 populate_cache.sql

(limited to 'populate_cache.sql')

diff --git a/populate_cache.sql b/populate_cache.sql
new file mode 100644
index 0000000..0af4b36
--- /dev/null
+++ b/populate_cache.sql
@@ -0,0 +1,44 @@
+drop table value_cache cascade;
+
+CREATE TABLE value_cache (
+value_int integer,
+timestamp timestamp,
+sensor_id integer
+);
+
+create or replace view vi_value_cache as 
+  SELECT timestamp, sensor_id, value_int, 'CACHE'
+    FROM value_cache
+UNION
+  SELECT date_trunc('hour', timestamp), sensor_id, avg(value_int), 'LIVE'
+    FROM value
+   WHERE timestamp > coalesce( (select max(timestamp) from value_cache), now() - 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 = now();
+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 = now() - interval '3 days';
+      RAISE NOTICE 'last update set to %', last_update;
+    end if;
+
+    delete from value_cache where timestamp >= last_update;
+
+    insert into value_cache 
+        select 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;
-- 
cgit v1.2.1