telemetry/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 maplog_app NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN PASSWORD 'MAPlog';
GRANT senslog TO maplog_app;

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

ALTER DATABASE maplog OWNER TO senslog;

\connect maplog

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 SCHEMA maplog;

ALTER SCHEMA maplog OWNER TO senslog;

ALTER SCHEMA public OWNER TO senslog;


CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
COMMENT ON EXTENSION postgis IS 'PostGIS geometry and geography spatial types and functions';


SET default_tablespace = '';


CREATE TABLE maplog.campaign (
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    from_time TIMESTAMP WITH TIME ZONE NOT NULL,
    to_time TIMESTAMP WITH TIME ZONE
--     CONSTRAINT campaign_check_time CHECK (from_time < to_time)
);
ALTER TABLE maplog.campaign OWNER TO senslog;

CREATE SEQUENCE maplog.campaign_id_seq AS INTEGER START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE maplog.campaign_id_seq OWNER TO senslog;

ALTER SEQUENCE maplog.campaign_id_seq OWNED BY maplog.campaign.id;

ALTER TABLE ONLY maplog.campaign ALTER COLUMN id SET DEFAULT nextval('maplog.campaign_id_seq'::regclass);


CREATE TABLE maplog.obs_telemetry (
    id BIGINT NOT NULL PRIMARY KEY,
    time_stamp TIMESTAMP WITH TIME ZONE NOT NULL,
    unit_id BIGINT NOT NULL,
    observed_values jsonb NOT NULL,
    the_geom public.geometry NOT NULL,
    speed INTEGER NOT NULL,
    time_received TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    UNIQUE (unit_id, time_stamp) -- can not have multiple measurements on the same unit at the same time
);

ALTER TABLE maplog.obs_telemetry OWNER TO senslog;

CREATE SEQUENCE maplog.obs_telemetry_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE maplog.obs_telemetry_id_seq OWNER TO senslog;

ALTER SEQUENCE maplog.obs_telemetry_id_seq OWNED BY maplog.obs_telemetry.id;

ALTER TABLE ONLY maplog.obs_telemetry ALTER COLUMN id SET DEFAULT nextval('maplog.obs_telemetry_id_seq'::regclass);


CREATE TABLE maplog.entity (
    id          INTEGER NOT NULL PRIMARY KEY,
    identity    VARCHAR(30) NOT NULL,
    name        VARCHAR(100) NOT NULL
);

ALTER TABLE maplog.entity OWNER TO senslog;

CREATE SEQUENCE maplog.entity_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE maplog.entity_id_seq OWNER TO senslog;

ALTER SEQUENCE maplog.entity_id_seq OWNED BY maplog.entity.id;

ALTER TABLE ONLY maplog.entity ALTER COLUMN id SET DEFAULT nextval('maplog.entity_id_seq'::regclass);

CREATE TABLE maplog.action (
    action_id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

ALTER TABLE maplog.action OWNER TO senslog;


CREATE TABLE maplog.event (
    id          BIGINT NOT NULL PRIMARY KEY,
    entity_id   INTEGER NOT NULL,
    action_id   INTEGER NOT NULL,
    unit_id     BIGINT NOT NULL,
    from_time   TIMESTAMP WITH TIME ZONE NOT NULL,
    to_time     TIMESTAMP WITH TIME ZONE,
    CONSTRAINT dta_check_time CHECK (from_time < to_time)
);
ALTER TABLE maplog.event OWNER TO senslog;

CREATE SEQUENCE maplog.event_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE maplog.event_id_seq OWNER TO senslog;

ALTER SEQUENCE maplog.event_id_seq OWNED BY maplog.event.id;

ALTER TABLE ONLY maplog.event ALTER COLUMN id SET DEFAULT nextval('maplog.event_id_seq'::regclass);

CREATE TABLE maplog.phenomenon (
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    uom CHARACTER VARYING(30) NOT NULL,
    uom_link TEXT
);
ALTER TABLE maplog.phenomenon OWNER TO senslog;

CREATE SEQUENCE maplog.phenomenon_id_seq AS INTEGER START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE maplog.phenomenon_id_seq OWNER TO senslog;

ALTER SEQUENCE maplog.phenomenon_id_seq OWNED BY maplog.phenomenon.id;

ALTER TABLE ONLY maplog.phenomenon ALTER COLUMN id SET DEFAULT nextval('maplog.phenomenon_id_seq'::regclass);

INSERT INTO maplog.phenomenon(id, name, uom, uom_link) VALUES (0, 'GENERAL', 'UOM', null);

CREATE TABLE maplog.sensor (
    sensor_id BIGINT NOT NULL PRIMARY KEY,
    name CHARACTER VARYING(100) UNIQUE NOT NULL,
    type TEXT,
    description TEXT,
    io_id INTEGER NOT NULL,
    io_multiplier DOUBLE PRECISION NOT NULL DEFAULT 1.0,
    phenomenon_id INTEGER NOT NULL
);
ALTER TABLE maplog.sensor OWNER TO senslog;

CREATE SEQUENCE maplog.sensor_sensor_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE maplog.sensor_sensor_id_seq OWNER TO senslog;

ALTER SEQUENCE maplog.sensor_sensor_id_seq OWNED BY maplog.sensor.sensor_id;

ALTER TABLE ONLY maplog.sensor ALTER COLUMN sensor_id SET DEFAULT nextval('maplog.sensor_sensor_id_seq'::regclass);


CREATE TABLE maplog.system_user (
    id          INTEGER NOT NULL PRIMARY KEY,
    identity    VARCHAR(50) NOT NULL,
    name        TEXT NOT NULL
);
ALTER TABLE maplog.system_user OWNER TO senslog;

CREATE SEQUENCE maplog.system_user_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE maplog.system_user_id_seq OWNER TO senslog;

ALTER SEQUENCE maplog.system_user_id_seq OWNED BY maplog.sensor.sensor_id;

ALTER TABLE ONLY maplog.system_user ALTER COLUMN id SET DEFAULT nextval('maplog.system_user_id_seq'::regclass);

CREATE TABLE maplog.unit (
    unit_id BIGINT NOT NULL PRIMARY KEY,
    imei CHARACTER VARYING(20) NOT NULL UNIQUE,
    name CHARACTER VARYING(100) NOT NULL,
    description TEXT,
    is_mobile boolean DEFAULT true NOT NULL,
    unit_type_id CHARACTER VARYING(2) DEFAULT 'X'::CHARACTER VARYING NOT NULL
);
ALTER TABLE maplog.unit OWNER TO senslog;


CREATE TABLE maplog.unit_to_campaign (
    id INTEGER NOT NULL PRIMARY KEY,
    campaign_id INTEGER NOT NULL,
    unit_id BIGINT NOT NULL,
    from_time TIMESTAMP WITH TIME ZONE NOT NULL,
    to_time TIMESTAMP WITH TIME ZONE
);
ALTER TABLE maplog.unit_to_campaign OWNER TO senslog;

CREATE SEQUENCE maplog.unit_to_campaign_id_seq AS INTEGER START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE maplog.unit_to_campaign_id_seq OWNER TO senslog;

ALTER SEQUENCE maplog.unit_to_campaign_id_seq OWNED BY maplog.unit_to_campaign.id;

ALTER TABLE ONLY maplog.unit_to_campaign ALTER COLUMN id SET DEFAULT nextval('maplog.unit_to_campaign_id_seq'::regclass);


CREATE TABLE maplog.unit_to_sensor (
    sensor_id BIGINT NOT NULL,
    unit_id BIGINT NOT NULL,
    PRIMARY KEY (unit_id, sensor_id)
);
ALTER TABLE maplog.unit_to_sensor OWNER TO senslog;


CREATE TABLE maplog.unit_type (
    unit_type_id CHARACTER VARYING(2) NOT NULL PRIMARY KEY,
    name CHARACTER VARYING(20) NOT NULL,
    description TEXT
);
ALTER TABLE maplog.unit_type OWNER TO senslog;


CREATE TABLE maplog.user_to_campaign_config (
    user_id INTEGER NOT NULL,
    campaign_id INTEGER NOT NULL,
    entity_id INTEGER NOT NULL,
    config JSONB NOT NULL DEFAULT '{}'::JSONB,
    PRIMARY KEY (user_id, campaign_id, entity_id)
);
ALTER TABLE maplog.user_to_campaign_config OWNER TO senslog;


CREATE TYPE alert_status AS ENUM ('CREATED', 'INFORMED', 'IN_PROCESS', 'SOLVED', 'DELETED');

CREATE TABLE maplog.alert (
    id BIGINT NOT NULL PRIMARY KEY,
    time_stamp TIMESTAMP WITH TIME ZONE NOT NULL,
    unit_id BIGINT NOT NULL,
    message TEXT NOT NULL,
    status alert_status NOT NULL,
    time_received TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL
);
ALTER TABLE maplog.alert OWNER TO senslog;

CREATE TABLE maplog.unit_static_to_mobile (
    static_unit_id  BIGINT NOT NULL,
    mobile_unit_id  BIGINT NOT NULL,
    the_geom public.geometry NOT NULL,
    PRIMARY KEY (static_unit_id, mobile_unit_id)
);
ALTER TABLE maplog.unit_static_to_mobile OWNER TO senslog;

CREATE SEQUENCE maplog.alert_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE maplog.alert_id_seq OWNER TO senslog;

ALTER SEQUENCE maplog.alert_id_seq OWNED BY maplog.alert.id;

ALTER TABLE ONLY maplog.alert ALTER COLUMN id SET DEFAULT nextval('maplog.alert_id_seq'::regclass);



CREATE INDEX fki_obss_unitid_fk ON maplog.obs_telemetry USING btree (unit_id);

CREATE INDEX fki_sens_phenom_fk ON maplog.sensor USING btree (phenomenon_id);

CREATE INDEX fki_u2c_campid_fk ON maplog.user_to_campaign_config USING btree (campaign_id);

CREATE INDEX fki_u2c_userid_fk ON maplog.user_to_campaign_config USING btree (user_id);

CREATE INDEX fki_u2c_entityid_fk ON maplog.user_to_campaign_config USING btree (entity_id);

CREATE INDEX fki_un2c_campid ON maplog.unit_to_campaign USING btree (campaign_id);

CREATE INDEX fki_un2c_unitid ON maplog.unit_to_campaign USING btree (unit_id);

CREATE INDEX fki_unit_unittype_fk ON maplog.unit USING btree (unit_type_id);

CREATE INDEX fki_uts_sensorid_fk ON maplog.unit_to_sensor USING btree (sensor_id);

CREATE INDEX fki_uts_unitid_fk ON maplog.unit_to_sensor USING btree (unit_id);

CREATE INDEX fki_event_unitid_fk ON maplog.event USING btree (unit_id);

CREATE INDEX fki_dr2ac_entityid_fk ON maplog.event USING btree (entity_id);

CREATE INDEX fki_dr2ac_actionid_fk ON maplog.event USING btree (action_id);

CREATE INDEX fki_dr2ac_unitid_fk ON maplog.event USING btree (unit_id);

CREATE INDEX fki_alert_unitid_fk ON maplog.alert USING btree (unit_id);



ALTER TABLE ONLY maplog.obs_telemetry ADD CONSTRAINT obss_unitid_fk FOREIGN KEY (unit_id) REFERENCES maplog.unit(unit_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.sensor ADD CONSTRAINT sens_phenom_fk FOREIGN KEY (phenomenon_id) REFERENCES maplog.phenomenon(id);

ALTER TABLE ONLY maplog.user_to_campaign_config ADD CONSTRAINT u2c_campid FOREIGN KEY (campaign_id) REFERENCES maplog.campaign(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.user_to_campaign_config ADD CONSTRAINT u2c_userid_fk FOREIGN KEY (user_id) REFERENCES maplog.system_user(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.user_to_campaign_config ADD CONSTRAINT u2c_entityid_fk FOREIGN KEY (entity_id) REFERENCES maplog.entity(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.unit_to_campaign ADD CONSTRAINT un2c_campid FOREIGN KEY (campaign_id) REFERENCES maplog.campaign(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.unit_to_campaign ADD CONSTRAINT un2c_unitid FOREIGN KEY (unit_id) REFERENCES maplog.unit(unit_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.unit ADD CONSTRAINT unit_unittype_fk FOREIGN KEY (unit_type_id) REFERENCES maplog.unit_type(unit_type_id);

ALTER TABLE ONLY maplog.unit_to_sensor ADD CONSTRAINT uts_sensorid_fk FOREIGN KEY (sensor_id) REFERENCES maplog.sensor(sensor_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.unit_to_sensor ADD CONSTRAINT uts_unitid_fk FOREIGN KEY (unit_id) REFERENCES maplog.unit(unit_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.event ADD CONSTRAINT event_unitid_fk FOREIGN KEY (unit_id) REFERENCES maplog.unit(unit_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.event ADD CONSTRAINT event_entityid_fk FOREIGN KEY (entity_id) REFERENCES maplog.entity(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.event ADD CONSTRAINT event_actionid_fk FOREIGN KEY (action_id) REFERENCES maplog.action(action_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.alert ADD CONSTRAINT alert_unitid_fk FOREIGN KEY (unit_id) REFERENCES maplog.unit(unit_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.unit_static_to_mobile ADD CONSTRAINT static_mobile_st_fk FOREIGN KEY (static_unit_id) REFERENCES maplog.unit(unit_id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY maplog.unit_static_to_mobile ADD CONSTRAINT static_mobile_mb_fk FOREIGN KEY (mobile_unit_id) REFERENCES maplog.unit(unit_id) ON UPDATE CASCADE ON DELETE CASCADE;

REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


CREATE SCHEMA tracking;
ALTER SCHEMA tracking OWNER TO senslog;

CREATE TYPE order_status AS ENUM ('READY', 'MONITORING', 'DELIVERED');

CREATE TYPE delivery_type AS ENUM ('SERVICE_FOFR', 'VEHICLE_CITROEN', 'VEHICLE_PEUGEOT');

CREATE TABLE tracking.record (
    id BIGINT NOT NULL PRIMARY KEY,
    unit_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    tracking_id BIGINT NOT NULL,
    status order_status NOT NULL DEFAULT 'READY',
    delivery_type delivery_type NOT NULL,
    time_tracking_start TIMESTAMP WITH TIME ZONE DEFAULT null,
    time_tracking_stop TIMESTAMP WITH TIME ZONE DEFAULT null,
    time_received TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);
ALTER TABLE tracking.record OWNER TO senslog;

CREATE UNIQUE INDEX tracking_record_uniq_open ON tracking.record(unit_id) WHERE (status = 'READY' OR status = 'MONITORING');

CREATE SEQUENCE tracking.record_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE tracking.record_id_seq OWNER TO senslog;

ALTER SEQUENCE tracking.record_id_seq OWNED BY tracking.record.id;

ALTER TABLE ONLY tracking.record ALTER COLUMN id SET DEFAULT nextval('tracking.record_id_seq'::regclass);

CREATE TABLE tracking.order_to_event (
    record_id   BIGINT NOT NULL,
    event_id    BIGINT NOT NULL,
    PRIMARY KEY (record_id, event_id)
);
ALTER TABLE tracking.order_to_event OWNER TO senslog;

ALTER TABLE ONLY tracking.order_to_event ADD CONSTRAINT order_event_rec_fk FOREIGN KEY (record_id) REFERENCES tracking.record(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY tracking.order_to_event ADD CONSTRAINT order_event_ev_fk FOREIGN KEY (event_id) REFERENCES maplog.event(id) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TYPE tracking.comparison_operator AS ENUM ('LT', 'LE', 'GE', 'GT', 'NE', 'EQ');
ALTER TYPE tracking.comparison_operator OWNER TO senslog;

CREATE TABLE tracking.violating_conditions (
                                               unit_id BIGINT NOT NULL,
                                               phenomenon_id INTEGER NOT NULL,
                                               comparison_operator tracking.comparison_operator NOT NULL,
                                               value DOUBLE PRECISION NOT NULL,
                                               UNIQUE (unit_id, phenomenon_id)
);

ALTER TABLE tracking.violating_conditions OWNER TO senslog;

ALTER TABLE ONLY tracking.violating_conditions ADD CONSTRAINT v_cnd_unit FOREIGN KEY (unit_id) REFERENCES maplog.unit(unit_id);

ALTER TABLE ONLY tracking.violating_conditions ADD CONSTRAINT v_cnd_ph FOREIGN KEY (phenomenon_id) REFERENCES maplog.phenomenon(id);