analytics/init.sql

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE ROLE senslog NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT;
CREATE ROLE analytics_app NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN PASSWORD 'analytics';
GRANT senslog TO analytics_app;

CREATE DATABASE analytics WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = 'libc' LOCALE = 'en_US.UTF-8';

ALTER DATABASE analytics OWNER TO senslog;

\connect analytics

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';

CREATE SCHEMA analytics;
ALTER SCHEMA analytics OWNER TO senslog;
ALTER SCHEMA public OWNER TO senslog;

-- CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;


CREATE TYPE analytics.attribute_type AS ENUM ('MIN', 'MAX', 'AVG', 'VAL');

CREATE TYPE analytics.threshold_mode AS ENUM ('LT', 'LE', 'GE', 'GT', 'NE', 'EQ');

CREATE TYPE analytics.collector_type AS ENUM ('DOUBLE', 'MOLD');

CREATE TYPE analytics.notify_trigger_mode AS ENUM ('INSTANT', 'ON_CHANGE', 'DISABLED');

-- CREATE TYPE message_broker_type AS ENUM ('SENSLOG_ALERT');


create table analytics.entity_source (
    id SERIAL PRIMARY KEY NOT NULL,
    unit_id BIGINT NOT NULL,
    sensor_id BIGINT NOT NULL,
    UNIQUE (unit_id, sensor_id)
);

alter table analytics.entity_source OWNER TO senslog;

create table analytics.analytic_group (
    id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(200) NOT NULL DEFAULT 'no_name',
    time_interval INTEGER NOT NULL,     -- interval in seconds
    persistence BOOLEAN NOT NULL,
    collector_type analytics.collector_type
);

alter table analytics.analytic_group OWNER TO senslog;

create table analytics.entity_source_to_analytic_group (
    id SERIAL PRIMARY KEY NOT NULL,
    entity_source_id BIGINT REFERENCES analytics.entity_source(id),
    analytic_group_id INTEGER REFERENCES analytics.analytic_group(id),
    time_created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL
);

alter table analytics.entity_source_to_analytic_group OWNER TO senslog;

create table analytics.record (
    id SERIAL PRIMARY KEY NOT NULL,
    analytic_group_id INTEGER NOT NULL,
    attribute_type analytics.attribute_type NOT NULL,
    calculated_value DOUBLE PRECISION NOT NULL,
    time_interval INTEGER NOT NULL,     -- interval in seconds
    time_stamp TIMESTAMP WITH TIME ZONE NOT NULL,
    time_created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    UNIQUE (analytic_group_id, attribute_type, time_stamp)
);

alter table analytics.record OWNER TO senslog;


create table analytics.threshold (
   id SERIAL PRIMARY KEY NOT NULL,
   analytic_group_id INTEGER NOT NULL,
   notify_trigger_mode analytics.notify_trigger_mode NOT NULL,
   attribute_type analytics.attribute_type NOT NULL,
   process_on_fail BOOLEAN NOT NULL
);

alter table analytics.threshold OWNER TO senslog;

create table analytics.threshold_rule (
   id SERIAL PRIMARY KEY NOT NULL,
   threshold_id INTEGER NOT NULL REFERENCES analytics.threshold(id),
   threshold_mode analytics.threshold_mode NOT NULL,
   threshold_value DOUBLE PRECISION NOT NULL
);

alter table analytics.threshold_rule OWNER TO senslog;

-- create table analytics.alert_broker (
--    id SERIAL PRIMARY KEY NOT NULL,
--    broker_type message_broker_type NOT NULL,
--    config jsonb NOT NULL
-- );
--
-- alter table analytics.alert_broker OWNER TO senslog;
--
-- create table analytics.notify_to_msg_broker (
--     id SERIAL PRIMARY KEY NOT NULL,
--     threshold_id integer REFERENCES analytics.thresholds(id),
--     message_broker_id integer REFERENCES analytics.alert_broker(id),
--     properties json NOT NULL
-- );
--
-- alter table analytics.notify_to_msg_broker OWNER TO senslog;