sql: schema_ddl

1 files changed, 361 insertions, 0 deletions

diff --git a/sql/schema_ddl.sql b/sql/schema_ddl.sql
new file mode 100644
index 0000000..15f969b
--- /dev/null
+++ b/sql/schema_ddl.sql
@@ -0,0 +1,361 @@
+--
+-- 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
+--
+