Add option to only update when value changed (-> save to history) about mqtt2sql HOT 5 CLOSED

possible:

as historical data creation is not functional part of the python daemon, I suggest to handle this on SQL side too.

Remove existing trigger mqtt_after_update with the following mqtt_before_update trigger:

DROP TRIGGER IF EXISTS `mqtt_after_update`;

DROP TRIGGER IF EXISTS `mqtt_before_update`;
DELIMITER //
CREATE TRIGGER `mqtt_before_update` BEFORE UPDATE ON `mqtt` FOR EACH ROW BEGIN
    IF ((SELECT value FROM mqtt WHERE id=NEW.id)!=NEW.value) THEN
        INSERT INTO mqtt_history SET ts=NEW.ts, topicid=NEW.id, value=NEW.value;
    END IF;
END//
DELIMITER ;

This will insert data into history table only if the current value is different to the last published

from mqtt2sql.

An alternate way with detailed control for each topic is

1. add an additonal field history

ALTER TABLE `mqtt`
	ADD COLUMN `history` TINYINT(4) NOT NULL DEFAULT '1' AFTER `active`;

2. insert values (check in triggers) only if history is set, so change the existing original triggers like:

DROP TRIGGER IF EXISTS `mqtt_after_insert`;
DROP TRIGGER IF EXISTS `mqtt_after_update`;
DELIMITER //
CREATE TRIGGER `mqtt_after_insert` AFTER INSERT ON `mqtt` FOR EACH ROW BEGIN
	IF NEW.history!=0 THEN
		INSERT INTO mqtt_history SET ts=NEW.ts, topicid=NEW.id, value=NEW.value;
	END IF;
END//
CREATE TRIGGER `mqtt_after_update` AFTER UPDATE ON `mqtt` FOR EACH ROW BEGIN
	IF NEW.history!=0 THEN
		INSERT INTO mqtt_history SET ts=NEW.ts, topicid=NEW.id, value=NEW.value;
	END IF;
END//
DELIMITER ;

This solution allows you to fine control using the history column of mqtt table which topic should be in history and which not.

from mqtt2sql.

Cool thanks for the statements! I think they would also fit good for README?

I prefer the history for all, and no exclusions, so nr 1 😃 .

EDIT: Actually, I think it would make sense to add the "history" flag in general (i.e. to the default README statement), and have the trigger "changed only" optionally as "alternative trigger setup"?

from mqtt2sql.

I've created a PR to add the history flag to mqtt mysql table on README.

If you are interested, after that PR we could upgrade it to have both suggestions coexisting in DB and in trigger: enable_history and enable_history_changes_only

from mqtt2sql.

btw, what I want todo with the data that this script produces: create simple dashboard for e.g. temperature history and turn heating on/off.
I'm running openhab since quite some time but it's quite unstable and resource consuming while I only need a very small faction of features. ... as data anyway coming from mqtt this should be enough to realize it in the simplest fashion

from mqtt2sql.