From 02f48f7a905973428fc7766cf92bb04614e1fdca Mon Sep 17 00:00:00 2001
From: yvesf <yvesf-git@xapek.org>
Date: Sun, 24 Jul 2011 13:12:14 +0200
Subject: cleanup, tag version 0.3

---
 populate_cache.sql | 45 ---------------------------------------------
 1 file changed, 45 deletions(-)
 delete mode 100644 populate_cache.sql

(limited to 'populate_cache.sql')

diff --git a/populate_cache.sql b/populate_cache.sql
deleted file mode 100644
index f302607..0000000
--- a/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), 
-                                                  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 '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;
-- 
cgit v1.2.1

