「履歴情報つきinteger試作」をMySQLで

nuko_yokohamaさんの記事「履歴情報つきinteger試作」が面白い．PostgreSQLのJSON機能を用いてSQLだけで履歴情報を管理している．こうした機能を考えていたので試してみたくなった．JSON機能についてはMySQLでもバージョン5.7からサポートしているらしい．せっかくなのでMySQLのJSON機能の勉強もかねてMySQLでの実装を試みてみた．

関数定義

「履歴情報つき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;

中身は以下のとおり．履歴情報がJSON形式で記録されている．

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

ここから値を取り出すにはgetValueByUnixTimeを使う．

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)

ちゃんと履歴から値をとってこれている．