関数定義
「履歴情報つきinteger試作」では色々と関数を定義しているが，ここでは最低限の「create_tt_int」「add_tt_int」「value(実際にはgetValueByUnixTimeとgetValueByNum)」の実装を試みた．履歴つきinteger作成
履歴つきinteger作成関数．時間はUNIX_TIMESTAMPで表現する．
DROP FUNCTION IF EXISTS create_tt_int;
delimiter //
CREATE FUNCTION create_tt_int(value integer)
RETURNS JSON DETERMINISTIC
BEGIN
DECLARE t INT UNSIGNED;
set t = UNIX_TIMESTAMP();
RETURN (SELECT JSON_OBJECT('header',JSON_OBJECT('nums',1,'newest',t,'oldest',t),'values',JSON_ARRAY(JSON_OBJECT('value',value,'timestamp',t,'num',1))));
END//
delimiter ;
履歴つきinteger更新
履歴つきinteger更新関数．JSON_ARRAYのマージに手間取った．JSON_MERGEでよかった．
DROP FUNCTION IF EXISTS add_tt_int;
delimiter //
CREATE FUNCTION add_tt_int(src json, value integer)
RETURNS JSON DETERMINISTIC
BEGIN
DECLARE t INT UNSIGNED;
SET t = UNIX_TIMESTAMP();
RETURN (SELECT JSON_OBJECT("header",JSON_OBJECT("nums",JSON_EXTRACT(src,"$.header.nums")+1,"oldest",JSON_EXTRACT(src,"$.header.oldest"),"newest",t),"values",JSON_MERGE(JSON_ARRAY(JSON_OBJECT("value",value,"num",JSON_EXTRACT(src,"$.header.nums")+1,"timestamp",t)),JSON_EXTRACT(src,"$.values"))));
END//
delimiter ;
UnixTimeを指定して値を取得
UnixTimeを指定して値を取得．
DROP FUNCTION IF EXISTS getValueByUnixTime;
delimiter //
CREATE FUNCTION getValueByUnixTime(src json,t INT UNSIGNED)
RETURNS JSON DETERMINISTIC
BEGIN
DECLARE i int;
DECLARE x int;
DECLARE t2 JSON;
DECLARE value JSON;
IF src->'$.header.oldest' > t THEN RETURN JSON_OBJECT();
ELSEIF src->'$.header.newest' < t THEN RETURN JSON_EXTRACT(src,CONCAT("$.values[0].value"));
END IF;
SET x = src->'$.header.nums';
SET i = 0;
set value = JSON_OBJECT();
WHILE i < x DO
SET t2 = JSON_EXTRACT(src,CONCAT("$.values[",i,"].timestamp"));
IF t2<=t THEN RETURN JSON_EXTRACT(src,CONCAT("$.values[",i,"].value"));
END IF;
SET i = i + 1;
END WHILE;
RETURN value;
END//
delimiter ;
Numを指定して値を取得
履歴番号Numを指定して値を取得．
DROP FUNCTION IF EXISTS getValueByNum;
delimiter //
CREATE FUNCTION getValueByNum(src json,num INT UNSIGNED)
RETURNS JSON DETERMINISTIC
BEGIN
DECLARE i int;
DECLARE x int;
DECLARE num2 INT UNSIGNED;
DECLARE value JSON;
SET x = src->'$.header.nums';
IF (num < 0 ) THEN RETURN JSON_OBJECT();
ELSEIF (num > x) THEN RETURN JSON_OBJECT();
END IF;
SET i = 0;
set value = JSON_OBJECT();
WHILE i < x DO
SET num2 = JSON_EXTRACT(src,CONCAT("$.values[",i,"].num"));
IF (num=num2) THEN RETURN JSON_EXTRACT(src,CONCAT("$.values[",i,"].value"));
END IF;
SET i = i + 1;
END WHILE;
RETURN value;
END//
delimiter ;
使い方
テーブルを作成，履歴つきintegerを作成．2回ほど更新．
DROP TABLE test2;
CREATE TABLE test2 (id INT UNSIGNED, data JSON);
INSERT INTO test2 VALUES (1, create_tt_int(100));
INSERT INTO test2 VALUES (2, create_tt_int(300));
SELECT SLEEP(60);
UPDATE test2 SET data = add_tt_int(data, 200) WHERE id = 1;
UPDATE test2 SET data = add_tt_int(data, 500) WHERE id = 2;
SELECT SLEEP(60);
UPDATE test2 SET data = add_tt_int(data, 300) WHERE id = 1;
UPDATE test2 SET data = add_tt_int(data, 600) WHERE id = 2;
SELECT * FROM test2;
mysql> select * from test2\G;
*************************** 1. row ***************************
id: 1
data: {"header": {"nums": 3, "newest": 1470061025, "oldest": 1470060905}, "values": [{"num": 3, "value": 300, "timestamp": 1470061025}, {"num": 2, "value": 200, "timestamp": 1470060965}, {"num": 1, "value": 100, "timestamp": 1470060905}]}
*************************** 2. row ***************************
id: 2
data: {"header": {"nums": 3, "newest": 1470061025, "oldest": 1470060905}, "values": [{"num": 3, "value": 600, "timestamp": 1470061025}, {"num": 2, "value": 500, "timestamp": 1470060965}, {"num": 1, "value": 300, "timestamp": 1470060905}]}
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT getValueByUnixTime(data,UNIX_TIMESTAMP('2016-08-01 23:15:00')) AS value FROM test2;
+-------+
| value |
+-------+
| {} |
| {} |
+-------+
2 rows in set (0.00 sec)
mysql> SELECT getValueByUnixTime(data,UNIX_TIMESTAMP('2016-08-01 23:15:05')) AS value FROM test2;
+-------+
| value |
+-------+
| 100 |
| 300 |
+-------+
2 rows in set (0.00 sec)
mysql> SELECT getValueByUnixTime(data,UNIX_TIMESTAMP('2016-08-01 23:50:00')) AS value FROM test2;
+-------+
| value |
+-------+
| 300 |
| 600 |
+-------+
2 rows in set (0.00 sec)
