sql

DROP VIEW IF EXISTS "mqtt_history_view";
DROP TABLE IF EXISTS "mqtt_history";
DROP TABLE IF EXISTS "mqtt";
DROP INDEX IF EXISTS idx_mqtt_history_topicid;
DROP INDEX IF EXISTS idx_mqtt_history_ts;


CREATE TABLE IF NOT EXISTS "mqtt" (
    id serial UNIQUE,
    ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    topic TEXT NOT NULL,
    value TEXT NOT NULL,
    qos INT NOT NULL,
    retain INT NOT NULL,
    history_enable INT NOT NULL DEFAULT 1,
    history_diffonly INT NOT NULL DEFAULT 0,
    PRIMARY KEY (topic)
);

--
CREATE TABLE IF NOT EXISTS mqtt_history (
    id serial,
    ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    topicid INT NOT NULL,
    value TEXT NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FK_mqtt_history_mqtt FOREIGN KEY (topicid) REFERENCES mqtt (id)
);
CREATE INDEX idx_mqtt_history_topicid ON mqtt_history(topicid);
CREATE INDEX idx_mqtt_history_ts ON mqtt_history(ts);


CREATE OR REPLACE FUNCTION mqtt_before_insert() RETURNS trigger AS


CREATE TRIGGER mqtt_before_insert BEFORE INSERT ON mqtt FOR EACH ROW EXECUTE PROCEDURE mqtt_before();