telemetry/testing/update.sql

ALTER TABLE maplog.driver RENAME TO entity;

ALTER TABLE maplog.entity RENAME COLUMN driver_id TO id;

ALTER TABLE maplog.entity ADD COLUMN identity VARCHAR(30) NOT NULL DEFAULT '';

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);

ALTER TABLE maplog.driver_to_action RENAME TO event;
ALTER TABLE maplog.event RENAME COLUMN driver_id TO entity_id;

ALTER TABLE maplog.user_to_campaign RENAME TO user_to_campaign_config;
ALTER TABLE maplog.user_to_campaign_config RENAME COLUMN user_id TO entity_id;
ALTER TABLE maplog.user_to_campaign_config ADD COLUMN config JSONB NOT NULL DEFAULT '{}'::JSONB;
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;

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 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_alert_unitid_fk ON maplog.alert USING btree (unit_id);
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;