github-starred/gitea
2
0
Fork 0
mirror of https://github.com/go-gitea/gitea.git synced 2026-07-15 20:09:18 -05:00
Code Issues 2.6k Packages Projects Releases 100 Wiki Activity

Hack: history heatmap for user #2325

New Issue
Closed
opened 2025-11-02 04:32:53 -06:00 by GiteaMirror · 7 comments
GiteaMirror commented 2025-11-02 04:32:53 -06:00
Owner
Copy Link

Originally created by @markuman on GitHub (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

Originally created by @markuman on GitHub (Sep 12, 2018). ![heatmap2 0](https://user-images.githubusercontent.com/3920157/45409348-33b62400-b66f-11e8-9f75-b9ae2ca19502.png) 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
GiteaMirror added the issue/duplicate label 2025-11-02 04:32:53 -06:00
GiteaMirror commented 2025-11-02 04:32:54 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Sep 12, 2018):

Could you send a PR ?

@lunny commented on GitHub (Sep 12, 2018): Could you send a PR ?
GiteaMirror commented 2025-11-02 04:32:54 -06:00
Author
Owner
Copy Link

@markuman commented on GitHub (Sep 12, 2018):

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

@markuman commented on GitHub (Sep 12, 2018): Unfortunately not yet, because I hack the necessary API for that with python. It need to be integrated into gitea API.
GiteaMirror commented 2025-11-02 04:32:55 -06:00
Author
Owner
Copy Link

@markuman commented on GitHub (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 commented on GitHub (Sep 12, 2018): The main problem is my SQL statement (which only works for MariaDB) ```sql 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.
GiteaMirror commented 2025-11-02 04:32:55 -06:00
Author
Owner
Copy Link

@markuman commented on GitHub (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

@markuman commented on GitHub (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. ```sql 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 https://github.com/WildCodeSchool/vue-calendar-heatmap/issues/5#issuecomment-420304770 2. Should we use a procedure and call it with the username or should we prepare a select statement with the username? cc @lunny
GiteaMirror commented 2025-11-02 04:32:55 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Sep 12, 2018):

It seems the SQL is not complex.

@lunny commented on GitHub (Sep 12, 2018): It seems the SQL is not complex.
GiteaMirror commented 2025-11-02 04:32:55 -06:00
Author
Owner
Copy Link

@ghost commented on GitHub (Oct 26, 2018):

this one should be closed?

@ghost commented on GitHub (Oct 26, 2018): this one should be closed?
GiteaMirror commented 2025-11-02 04:32:56 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Oct 26, 2018):

duplicated with #6

@lunny commented on GitHub (Oct 26, 2018): duplicated with #6
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
$20
$250
$50
$500
backport/done
💎 Bounty
docs-update-needed
good first issue
hacktoberfest
issue/bounty
issue/confirmed
issue/critical
issue/duplicate
issue/needs-feedback
issue/not-a-bug
issue/regression
issue/stale
issue/workaround
lgtm/need 2
modifies/api
modifies/translation
outdated/backport/v1.18
outdated/theme/markdown
outdated/theme/timetracker
performance/bigrepo
performance/cpu
performance/memory
performance/speed
pr/breaking
proposal/accepted
proposal/rejected
pr/wip
pull-request
Mirrored from GitHub Pull Request
 reviewed/wontfix
💰 Rewarded
skip-changelog
status/blocked
topic/accessibility
topic/api
topic/authentication
topic/build
topic/code-linting
topic/commit-signing
topic/content-rendering
topic/deployment
topic/distribution
topic/federation
topic/gitea-actions
topic/issues
topic/lfs
topic/mobile
topic/moderation
topic/packages
topic/pr
topic/projects
topic/repo
topic/repo-migration
topic/security
topic/theme
topic/ui
topic/ui-interaction
topic/ux
topic/webhooks
topic/wiki
type/bug
type/deprecation
type/docs
type/enhancement
type/feature
type/miscellaneous
type/proposal
type/question
type/refactoring
type/summary
type/testing
type/upstream
No Label issue/duplicate
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/gitea#2325
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?