From 862282ce99760832d3e9e5b4b1171b861105e004 Mon Sep 17 00:00:00 2001
From: Ebus-at-dockstar <ebus@dockstar>
Date: Mon, 25 Mar 2013 10:24:28 +0100
Subject: move old stuff away

---
 sql/schema_ddl.sql | 361 -----------------------------------------------------
 1 file changed, 361 deletions(-)
 delete mode 100644 sql/schema_ddl.sql

(limited to 'sql/schema_ddl.sql')

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
---
-
-- 
cgit v1.2.1

