diff options
Diffstat (limited to 'sql/schema_ddl.sql')
| -rw-r--r-- | sql/schema_ddl.sql | 361 |
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 +-- + |