diff options
Diffstat (limited to 'sql/schema_ddl.sql')
| -rw-r--r-- | sql/schema_ddl.sql | 361 |
1 files changed, 0 insertions, 361 deletions
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 --- - |