Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Hack: history heatmap for user #4918

Closed
markuman opened this issue Sep 12, 2018 · 7 comments
Closed

Hack: history heatmap for user #4918

markuman opened this issue Sep 12, 2018 · 7 comments
Labels
issue/duplicate The issue has already been reported.

Comments

@markuman
Copy link

markuman commented Sep 12, 2018

heatmap2 0

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
Copy link
Member

lunny commented Sep 12, 2018

Could you send a PR ?

@markuman
Copy link
Author

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

@markuman
Copy link
Author

markuman commented Sep 12, 2018

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
Copy link
Author

markuman commented Sep 12, 2018

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?
  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
Copy link
Member

lunny commented Sep 12, 2018

It seems the SQL is not complex.

@techknowlogick techknowlogick added type/proposal The new feature has not been accepted yet but needs to be discussed first. type/feature Completely new functionality. Can only be merged if feature freeze is not active. labels Oct 20, 2018
@yasuokav
Copy link
Contributor

this one should be closed?

@lunny lunny added this to the 1.7.0 milestone Oct 26, 2018
@lunny lunny closed this as completed Oct 26, 2018
@lunny lunny removed this from the 1.7.0 milestone Oct 26, 2018
@lunny lunny added issue/duplicate The issue has already been reported. and removed type/feature Completely new functionality. Can only be merged if feature freeze is not active. type/proposal The new feature has not been accepted yet but needs to be discussed first. labels Oct 26, 2018
@lunny
Copy link
Member

lunny commented Oct 26, 2018

duplicated with #6

@go-gitea go-gitea locked and limited conversation to collaborators Nov 24, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
issue/duplicate The issue has already been reported.
Projects
None yet
Development

No branches or pull requests

4 participants