A simple tag database with logging backed by MySQL

tag_db.sql

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.6-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             10.2.0.5611
-- Author: Nicholas Folse
-- Copyright (c) 2020-2023 Nicholas Folse
-- --------------------------------------------------------


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


-- Dumping database structure for tag_db
CREATE DATABASE IF NOT EXISTS `tag_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `tag_db`;

-- Dumping structure for table tag_db.doubles
CREATE TABLE IF NOT EXISTS `doubles` (
  `tag_id` char(253) NOT NULL,
  `timestamp` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
  `value` double NOT NULL,
  PRIMARY KEY (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.

-- Dumping structure for table tag_db.doubles_cfg
CREATE TABLE IF NOT EXISTS `doubles_cfg` (
  `tag_id` char(253) NOT NULL,
  `update_db` double DEFAULT 0,
  `log_db` double DEFAULT 0,
  `interval_db` double DEFAULT 0,
  PRIMARY KEY (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.

-- Dumping structure for table tag_db.doubles_log
CREATE TABLE IF NOT EXISTS `doubles_log` (
  `tag_id` char(253) NOT NULL,
  `timestamp` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
  `value` double DEFAULT NULL,
  PRIMARY KEY (`tag_id`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.

-- Dumping structure for trigger tag_db.doubles_after_insert
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `doubles_after_insert` AFTER INSERT ON `doubles` FOR EACH ROW BEGIN
   insert into doubles_log (tag_id, `timestamp`, `value`) 
	values (new.tag_id, new.`timestamp`, new.`value`);
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for trigger tag_db.doubles_after_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `doubles_after_update` AFTER UPDATE ON `doubles` FOR EACH ROW BEGIN

   declare ldb real;
   declare prevlog real;

   if (old.`value` != new.`value` and old.`timestamp` != new.`timestamp`) then

begin
	select doubles_cfg.log_db into ldb from doubles_cfg where doubles_cfg.tag_id = new.tag_id;
	select doubles_log.`value` into prevlog from doubles_log where doubles_log.tag_id = new.tag_id order by doubles_log.`timestamp` desc limit 1;
end;

if ldb is not null then
if (prevlog is null or abs(new.`value` - prevlog) >= ldb) then

	insert into doubles_log (tag_id, `timestamp`, `value`) 
	values (new.tag_id, new.`timestamp`, new.`value`);

end if;
end if;
end if;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for trigger tag_db.doubles_before_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `doubles_before_update` BEFORE UPDATE ON `doubles` FOR EACH ROW BEGIN

declare udb real;

begin
	select ifnull(doubles_cfg.update_db,0) into udb from doubles_cfg where doubles_cfg.tag_id = new.tag_id;
end;

if (abs(new.`value` - old.`value`) < udb) then
  set new.`value` = old.`value`;
  set new.`timestamp` = old.`timestamp`;
else
set new.`timestamp` = now();
end if;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

tag_db_sqlite.sql

-- Tag database with tag history. Translated from MariaDB version by ChatGPT.
-- Copyright (c) 2020-2023 Nicholas Folse

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS doubles (
  tag_id TEXT NOT NULL,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  value REAL NOT NULL,
  PRIMARY KEY (tag_id)
);
CREATE TABLE IF NOT EXISTS doubles_cfg (
  tag_id TEXT NOT NULL,
  update_db REAL DEFAULT 0,
  log_db REAL DEFAULT 0,
  interval_db REAL DEFAULT 0,
  PRIMARY KEY (tag_id)
);
CREATE TABLE IF NOT EXISTS doubles_log (
  tag_id TEXT NOT NULL,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  value REAL,
  PRIMARY KEY (tag_id, timestamp)
);
CREATE TRIGGER doubles_after_insert
AFTER INSERT ON doubles
FOR EACH ROW
BEGIN
    INSERT INTO doubles_log (tag_id, timestamp, value)
    VALUES (new.tag_id, new.timestamp, new.value);
END;
CREATE TRIGGER doubles_after_update
AFTER UPDATE ON doubles
FOR EACH ROW
BEGIN
    INSERT INTO doubles_log (tag_id, timestamp, value)
    VALUES (new.tag_id, new.timestamp, new.value);
END;
COMMIT;

tag_db_sqlite_example_queries.sql

-- Insert or update tag value:
INSERT OR REPLACE INTO doubles (tag_id, timestamp, value)
VALUES ("test01", CURRENT_TIMESTAMP, 12)

-- Query for tag history, grouped in intervals of <300> seconds
-- Return min, max, mean, first, last, first_timestamp, last_timestamp, for each group.
-- Created by ChatGPT (with substantial coaching)
WITH subquery AS (
    SELECT
        tag_id,
        datetime((strftime('%s', timestamp) / 300 ) * 300 , 'unixepoch') as bucket_start_time,
        timestamp,
        value
    FROM doubles_log
    WHERE tag_id = "test01"
)

SELECT 
    tag_id, 
    bucket_start_time,
    MIN(value) as min_value,
    MAX(value) as max_value,
    SUM(value) / COUNT(value) as mean_value,
    COUNT(value) as count_value,
    (SELECT timestamp FROM subquery WHERE tag_id = s.tag_id and bucket_start_time = s.bucket_start_time ORDER BY timestamp LIMIT 1) as first_time,
    (SELECT value FROM subquery WHERE tag_id = s.tag_id and bucket_start_time = s.bucket_start_time ORDER BY timestamp LIMIT 1) as first_value,
    (SELECT timestamp FROM subquery WHERE tag_id = s.tag_id and bucket_start_time = s.bucket_start_time ORDER BY timestamp DESC LIMIT 1) as last_time,
    (SELECT value FROM subquery WHERE tag_id = s.tag_id and bucket_start_time = s.bucket_start_time ORDER BY timestamp DESC LIMIT 1) as last_value
FROM subquery s
GROUP BY tag_id, bucket_start_time
ORDER BY bucket_start_time;