Awm0d6WC

1-- --------------------------------------------------------
2-- Host:                         127.0.0.1
3-- Server version:               10.4.6-MariaDB - mariadb.org binary distribution
4-- Server OS:                    Win64
5-- HeidiSQL Version:             10.2.0.5611
6-- --------------------------------------------------------
7
8/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
9/*!40101 SET NAMES utf8 */;
10/*!50503 SET NAMES utf8mb4 */;
11/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
12/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
13
14
15-- Dumping database structure for tag_db
16CREATE DATABASE IF NOT EXISTS `tag_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
17USE `tag_db`;
18
19-- Dumping structure for table tag_db.doubles
20CREATE TABLE IF NOT EXISTS `doubles` (
21  `tag_id` char(253) NOT NULL,
22  `timestamp` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
23  `value` double NOT NULL,
24  PRIMARY KEY (`tag_id`)
25) ENGINE=InnoDB DEFAULT CHARSET=utf8;
26
27-- Data exporting was unselected.
28
29-- Dumping structure for table tag_db.doubles_cfg
30CREATE TABLE IF NOT EXISTS `doubles_cfg` (
31  `tag_id` char(253) NOT NULL,
32  `update_db` double DEFAULT 0,
33  `log_db` double DEFAULT 0,
34  `interval_db` double DEFAULT 0,
35  PRIMARY KEY (`tag_id`)
36) ENGINE=InnoDB DEFAULT CHARSET=utf8;
37
38-- Data exporting was unselected.
39
40-- Dumping structure for table tag_db.doubles_log
41CREATE TABLE IF NOT EXISTS `doubles_log` (
42  `tag_id` char(253) NOT NULL,
43  `timestamp` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
44  `value` double DEFAULT NULL,
45  PRIMARY KEY (`tag_id`,`timestamp`)
46) ENGINE=InnoDB DEFAULT CHARSET=utf8;
47
48-- Data exporting was unselected.
49
50-- Dumping structure for trigger tag_db.doubles_after_insert
51SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
52DELIMITER //
53CREATE TRIGGER `doubles_after_insert` AFTER INSERT ON `doubles` FOR EACH ROW BEGIN
54   insert into doubles_log (tag_id, `timestamp`, `value`) 
55	values (new.tag_id, new.`timestamp`, new.`value`);
56END//
57DELIMITER ;
58SET SQL_MODE=@OLDTMP_SQL_MODE;
59
60-- Dumping structure for trigger tag_db.doubles_after_update
61SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
62DELIMITER //
63CREATE TRIGGER `doubles_after_update` AFTER UPDATE ON `doubles` FOR EACH ROW BEGIN
64
65   declare ldb real;
66   declare prevlog real;
67
68   if (old.`value` != new.`value` and old.`timestamp` != new.`timestamp`) then
69
70begin
71	select doubles_cfg.log_db into ldb from doubles_cfg where doubles_cfg.tag_id = new.tag_id;
72	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;
73end;
74
75if ldb is not null then
76if (prevlog is null or abs(new.`value` - prevlog) >= ldb) then
77
78	insert into doubles_log (tag_id, `timestamp`, `value`) 
79	values (new.tag_id, new.`timestamp`, new.`value`);
80
81end if;
82end if;
83end if;
84END//
85DELIMITER ;
86SET SQL_MODE=@OLDTMP_SQL_MODE;
87
88-- Dumping structure for trigger tag_db.doubles_before_update
89SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
90DELIMITER //
91CREATE TRIGGER `doubles_before_update` BEFORE UPDATE ON `doubles` FOR EACH ROW BEGIN
92
93declare udb real;
94
95begin
96	select ifnull(doubles_cfg.update_db,0) into udb from doubles_cfg where doubles_cfg.tag_id = new.tag_id;
97end;
98
99if (abs(new.`value` - old.`value`) < udb) then
100  set new.`value` = old.`value`;
101  set new.`timestamp` = old.`timestamp`;
102else
103set new.`timestamp` = now();
104end if;
105END//
106DELIMITER ;
107SET SQL_MODE=@OLDTMP_SQL_MODE;
108
109/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
110/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
111/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;