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 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 table analytics.sensor_to_unit (
    id SERIAL PRIMARY KEY NOT NULL,
    unit_id BIGINT NOT NULL,
    sensor_id BIGINT NOT NULL,
    last_observation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT to_timestamp(0),
    UNIQUE (unit_id, sensor_id)
);

alter table analytics.sensor_to_unit 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 NOT NULL,
    CHECK ( time_interval > 0 )
);

alter table analytics.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,
   notify_trigger_mode analytics.notify_trigger_mode NOT NULL,
   attribute_type analytics.attribute_type NOT NULL,
   process_on_fail BOOLEAN NOT NULL,
   alert_enable 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.sensor_to_unit_to_threshold (
    sensor_to_unit_id INTEGER REFERENCES analytics.sensor_to_unit(id),
    threshold_id INTEGER REFERENCES analytics.threshold(id),
    UNIQUE (sensor_to_unit_id, threshold_id)
);

alter table analytics.sensor_to_unit_to_threshold OWNER TO senslog;


create table analytics.sensor_to_unit_to_analytic_group (
    sensor_to_unit_id INTEGER REFERENCES analytics.sensor_to_unit(id),
    analytic_group_id INTEGER REFERENCES analytics.analytic_group(id),
    time_created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    UNIQUE (sensor_to_unit_id, analytic_group_id)
);

alter table analytics.sensor_to_unit_to_analytic_group OWNER TO senslog;


create table analytics.analytic_group_to_threshold (
    analytic_group_id INTEGER REFERENCES analytics.analytic_group(id),
    threshold_id INTEGER REFERENCES analytics.threshold(id),
    UNIQUE (analytic_group_id, threshold_id)
);

alter table analytics.analytic_group_to_threshold OWNER TO senslog;