Creating a Circular Log/History Table

[Former Member]

In MySQL, I was able to leverage a combination Unique Key with the REPLACE statement to create a table that had circular rows of data per user. With this, no user would have more than say 100 rows of logs in the table. Basically, I leveraged the ideas from http://dt.deviantart.com/journal/Build-Your-Own-Circular-Log-with-MySQL-222550965

The REPLACE/UPSERT in HANA doesn't use Unique Keys. Does anyone have an example of how a circular log table for multiple users could be created in HANA?

[Former Member · Accepted solution]

This will create a simple circular table:

Drop table T3;

CREATE TABLE T3 (KEY INT, ROW INT PRIMARY KEY, USER INT, VAL INT);

UPSERT T3 SELECT COALESCE(MAX(KEY), 0) + 1, (select MOD(COALESCE(MAX(KEY), 0), 5) + 1 from T3 where USER = 1), 1, 1 FROM T3 ;

UPSERT T3 SELECT COALESCE(MAX(KEY), 0) + 1, (select MOD(COALESCE(MAX(KEY), 0), 5) + 1 from T3 where USER = 1), 1, 1 FROM T3 ;

UPSERT T3 SELECT COALESCE(MAX(KEY), 0) + 1, (select MOD(COALESCE(MAX(KEY), 0), 5) + 1 from T3 where USER = 1), 1, 1 FROM T3 ;

UPSERT T3 SELECT COALESCE(MAX(KEY), 0) + 1, (select MOD(COALESCE(MAX(KEY), 0), 5) + 1 from T3 where USER = 1), 1, 1 FROM T3 ;

UPSERT T3 SELECT COALESCE(MAX(KEY), 0) + 1, (select MOD(COALESCE(MAX(KEY), 0), 5) + 1 from T3 where USER = 1), 1, 1 FROM T3 ;

UPSERT T3 SELECT COALESCE(MAX(KEY), 0) + 1, (select MOD(COALESCE(MAX(KEY), 0), 5) + 1 from T3 where USER = 1), 1, 1 FROM T3 ;

UPSERT T3 SELECT COALESCE(MAX(KEY), 0) + 1, (select MOD(COALESCE(MAX(KEY), 0), 5) + 1 from T3 where USER = 1), 1, 1 FROM T3 ;

select * from T3;

This will produce:

Key	ROW	USER	VAL
6	1	1	1
7	2	1	1
3	3	1	1
4	4	1	1
5	5	1	1

I would prefer to have a Unique Key on Row, USER so that I can rotate over the unique key for multiple users. Any insights?

[Former Member · Accepted solution]

I believe this is the correct solution. Note the use of a combination primary key and having to use a WHERE clause.

Drop table T3;

Drop sequence "ID_SEQ";

create sequence "ID_SEQ" increment by 1 start with 1 no cycle ;

CREATE TABLE T3 (KEY INT, HISTORY_ROW INT, ROW INT, USER INT, VAL INT, PRIMARY KEY(ROW, USER));

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 1), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1), 1, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1) AND USER = 1;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 1), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1), 1, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1) AND USER = 1;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 1), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1), 1, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1) AND USER = 1;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 1), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1), 1, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1) AND USER = 1;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 1), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1), 1, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1) AND USER = 1;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 1), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1), 1, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 1) AND USER = 1;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 2), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2), 2, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2) AND USER = 2;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 2), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2), 2, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2) AND USER = 2;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 2), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2), 2, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2) AND USER = 2;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 2), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2), 2, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2) AND USER = 2;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 2), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2), 2, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2) AND USER = 2;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 2), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2), 2, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2) AND USER = 2;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 2), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2), 2, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2) AND USER = 2;

UPSERT T3 VALUES((ID_SEQ.NEXTVAL), (SELECT COALESCE(MAX(HISTORY_ROW), 0) + 1 from T3 where USER = 2), (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2), 2, 1) where ROW = (select MOD(COALESCE(MAX(HISTORY_ROW), 0), 5) + 1 from T3 where USER = 2) AND USER = 2;

This yields a column which has the total count + 1 of all rows at the time of UPSERT (ie, KEY), a column that has the total count + 1 of rows per user (ie, HISTORY_ROW), and a column that has the current rolling row number + 1 upon UPSERT (ie, ROW). Due to the usage of MOD 5, no user will have more that 5 rows of history/logs.

KEY	HISTORY_ROW	ROW	USER	VAL
6	6	1	1	1
2	2	2	1	1
3	3	3	1	1
4	4	4	1	1
5	5	5	1	1
12	6	1	2	1
13	7	2	2	1
14	8	3	2	1
10	4	4	2	1
11	5	5	2	1