path: root/sql/schema_ddl.sql

--
-- 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
--