Hack: history heatmap for user

[markuman]

I deploy a 2nd service (using python hug) to serve a user heatmap (using https://github.com/WildCodeSchool/vue-calendar-heatmap). Finally I just included it in the templates/user/profile.tmpl

origin: https://git.osuv.de/m/gitea-user-heatmap
mirror: https://gitlab.com/markuman/gitea-user-heatmap

demo: https://git.osuv.de/m/

#6

[lunny]

Could you send a PR ?

[markuman]

Unfortunately not yet, because I hack the necessary API for that with python. It need to be integrated into gitea API.

[markuman]

The main problem is my SQL statement (which only works for MariaDB)

CREATE
	PROCEDURE commithistory( USERNAME varchar(64) )
	BEGIN 
		with list_of_dates as(
		SELECT
			CURRENT_DATE - INTERVAL (seq) DAY date
		FROM
			seq_0_to_365 ),
		actions as(
		select
			`action`.created_unix as time_sec,
			count( user_id ) as value,
			DATE_FORMAT( FROM_UNIXTIME( `action`.created_unix ),
			'%Y-%m-%d' ) as dates
		from
			`action`
		inner join `user` on
			( `user`.id = `action`.user_id )
		where
			`user`.lower_name = USERNAME
		group by
			DATE_FORMAT( FROM_UNIXTIME( `action`.created_unix ),
			'%Y-%m-%d' )
		order by
			`action`.created_unix ) select
			list_of_dates.`date` as `date`,
			ifnull( actions.value,
			0 ) as count
		from
			list_of_dates
		left join actions on
			( list_of_dates.`date` = actions.dates )
		order by
			list_of_dates.`date`;
END;

a call commithistory('m') results in

date	count
2017-09-12	0
2017-09-13	0
2017-09-14	0
2017-09-15	0
2017-09-16	0
2017-09-17	0
2017-09-18	0
...	0
2018-06-01	0
2018-06-02	0
2018-06-03	0
2018-06-04	69
2018-06-05	4
2018-06-06	12
2018-06-07	2
2018-06-08	2
2018-06-09	0
2018-06-10	0
2018-06-11	0
2018-06-12	0
2018-06-13	1
2018-06-14	0
2018-06-15	1
2018-06-16	0
2018-06-17	0
2018-06-18	0
2018-06-19	0
2018-06-20	3
2018-06-21	0
2018-06-22	0
2018-06-23	0
2018-06-24	0
2018-06-25	0
2018-06-26	0
2018-06-27	0
2018-06-28	0
2018-06-29	0
2018-06-30	0
2018-07-01	0
2018-07-02	0
2018-07-03	2
2018-07-04	0
2018-07-05	2
2018-07-06	7
2018-07-07	0
2018-07-08	0
2018-07-09	4
2018-07-10	3
2018-07-11	0
2018-07-12	0
2018-07-13	0
2018-07-14	0
2018-07-15	0
2018-07-16	0
2018-07-17	0
2018-07-18	0
2018-07-19	0
2018-07-20	0
2018-07-21	0
2018-07-22	0
2018-07-23	0
2018-07-24	0
2018-07-25	0
2018-07-26	0
2018-07-27	0
2018-07-28	0
2018-07-29	0
2018-07-30	0
2018-07-31	0
2018-08-01	0
2018-08-02	2
2018-08-03	7
2018-08-04	3
2018-08-05	0
2018-08-06	4
2018-08-07	3
2018-08-08	3
2018-08-09	0
2018-08-10	2
2018-08-11	0
2018-08-12	0
2018-08-13	0
2018-08-14	3
2018-08-15	4
2018-08-16	4
2018-08-17	3
2018-08-18	0
2018-08-19	0
2018-08-20	1
2018-08-21	14
2018-08-22	10
2018-08-23	14
2018-08-24	1
2018-08-25	0
2018-08-26	0
2018-08-27	3
2018-08-28	0
2018-08-29	0
2018-08-30	3
2018-08-31	0
2018-09-01	0
2018-09-02	0
2018-09-03	11
2018-09-04	0
2018-09-05	0
2018-09-06	0
2018-09-07	3
2018-09-08	0
2018-09-09	0
2018-09-10	0
2018-09-11	14
2018-09-12	9

The query time on my MariaDB instance is alway < 100ms :)

Depending on the js heatmap implementation, 0 values maybe necessary, maybe not.
If not, we can get rid of the sequence engine (MariaDB related) which I use in the first with expression and then it's just a select on the action table.

[markuman]

I've tested it, and for https://github.com/WildCodeSchool/vue-calendar-heatmap the 0 values are not necessary. So this will simplify the sql statement! The select statement should work on any DB backend technology. It also reduce the query time to < 50ms.

CREATE
	PROCEDURE commithistory2( USERNAME varchar(64) )
	BEGIN 		
		select
			count(user_id) as count,
			DATE_FORMAT( FROM_UNIXTIME( `action`.created_unix ),
			'%Y-%m-%d' ) as `date`
		from
			`action`
		inner join `user` on
			( `user`.id = `action`.user_id )
		where
			`user`.lower_name = USERNAME
		group by
			DATE_FORMAT( FROM_UNIXTIME( `action`.created_unix ),
			'%Y-%m-%d' )
		order by
			`action`.created_unix;
END;

So some questions are open before I start working on a pull request.

1. Is another vuejs component dependency allowed?
   • https://github.com/WildCodeSchool/vue-calendar-heatmap
   • however, I cannot get it worked and patched it. See Missing required prop: "endDate" julienr114/vue-calendar-heatmap#5 (comment)
2. Should we use a procedure and call it with the username or should we prepare a select statement with the username?

cc @lunny

[lunny]

It seems the SQL is not complex.

this one should be closed?