Contribution system: History heatmap for user #1

@bkcsoft commented on GitHub (Nov 2, 2016):

I would prefer a solution that is in the backend, unless it's required to have it update without reloading. Unfortunately I can't seem to find anything that does that 😒

Also this would require caching, since it will fetch huge amounts of data from disk...

@bkcsoft commented on GitHub (Nov 2, 2016): I would prefer a solution that is in the backend, unless it's required to have it update without reloading. Unfortunately I can't seem to find anything that does that :unamused: Also this would _require_ caching, since it will fetch huge amounts of data from disk...

GiteaMirror commented

@joubertredrat commented on GitHub (Nov 2, 2016):

Yes, this plugin build on front-end, with backend I don't know too, but we can consider make component for this.

In my case I use ajax to get data, but build on frontend anyway

@joubertredrat commented on GitHub (Nov 2, 2016): Yes, this plugin build on front-end, with backend I don't know too, but we can consider make component for this. In my case I use ajax to get data, but build on frontend anyway

GiteaMirror commented

@xinity commented on GitHub (Nov 3, 2016):

following @bkcsoft retention strategy in cache is a must if we don't want gitea to waste ressources.
something like in the latest 15days could be enough don't you think ?

@xinity commented on GitHub (Nov 3, 2016): following @bkcsoft retention strategy in cache is a must if we don't want gitea to waste ressources. something like in the latest 15days could be enough don't you think ?

GiteaMirror commented

@bkcsoft commented on GitHub (Nov 4, 2016):

@xinity a simple LRU-cache should suffice in my opinion. Might be useful to be able to set the size of that cache though.
For my own projects I use hashicorp/golang-lru which works just fine and has a configurable size 🙂

@bkcsoft commented on GitHub (Nov 4, 2016): @xinity a simple LRU-cache should suffice in my opinion. Might be useful to be able to set the size of that cache though. For my own projects I use [hashicorp/golang-lru](https://github.com/hashicorp/golang-lru) which works just fine and has a configurable size :slightly_smiling_face:

GiteaMirror commented

@charlesreid1 commented on GitHub (Feb 22, 2018):

d3-calendar illustrates this really nicely and simply with javascript: https://bl.ocks.org/mbostock/4063318

git log is really flexible, so you can use it to output directly to JSON format, filter on users or dates, customize output format, etc (even has its own set of shortcodes). After an aggregation step, that data can be loaded into D3 calendar (basically just json objects with a key of the date and a value of the number of commits).

Here is a single-user activity graph that illustrates embedding the Javascript visualization in the home template file: git.charlesreid1.com and modified home template.

@charlesreid1 commented on GitHub (Feb 22, 2018): d3-calendar illustrates this really nicely and simply with javascript: [https://bl.ocks.org/mbostock/4063318](https://bl.ocks.org/mbostock/4063318) `git log` is [really flexible](https://www.git-scm.com/docs/git-log), so you can use it to output directly to JSON format, filter on users or dates, customize output format, etc (even has its own set of shortcodes). After an aggregation step, that data can be loaded into D3 calendar (basically just json objects with a key of the date and a value of the number of commits). Here is a single-user activity graph that illustrates embedding the Javascript visualization in the home template file: [git.charlesreid1.com](http://git.charlesreid1.com) and [modified home template](https://github.com/charlesreid1/gitea/blob/release/v1.2/templates/home.tmpl).

GiteaMirror commented

@lunny commented on GitHub (Feb 22, 2018):

@charlesreid1 could you send a PR to gitea with your heatmap?

@lunny commented on GitHub (Feb 22, 2018): @charlesreid1 could you send a PR to gitea with your heatmap?

GiteaMirror commented

@charlesreid1 commented on GitHub (Feb 22, 2018):

I'm happy to put together a visualization in an PR, but a couple of things need to be worked out first:

Placement. My example heatmap is commit data scraped for a single user, displayed on the home page for the (single-user) gitea server - which doesn't make sense for multi-user gitea servers. If we wanted to have a per-user heatmap, it could go a couple of places:

Behind the "Public Activity" tab on the user's profile (most appropriate place, but not very visible)
Just on top of the three tabs "Repositories"/"Public Activity"/"Starred repositories" on the user's profile (most visible, on user profile so useful for others to see your activity graph)
On the top of the user's dashboard (less useful since others cannot see your activity graph)

Data. The model I'm using for data would need to be changed a bit as well (my visualization points to a JSON file not dynamically generated by gitea). We could modify gitea to cache per-user commit data (updated with a post-commit hook or a daily maintenance task) and provide an endpoint route, where you provide it a username and it returns commit data in a JSON form convenient for visualization.

Thoughts? Potential roadblocks?

@charlesreid1 commented on GitHub (Feb 22, 2018): I'm happy to put together a visualization in an PR, but a couple of things need to be worked out first: 1. **Placement.** My example heatmap is commit data scraped for a single user, displayed on the home page for the (single-user) gitea server - which doesn't make sense for multi-user gitea servers. If we wanted to have a per-user heatmap, it could go a couple of places: * Behind the "Public Activity" tab on the user's profile (most appropriate place, but not very visible) * Just on top of the three tabs "Repositories"/"Public Activity"/"Starred repositories" on the user's profile (most visible, on user profile so useful for others to see your activity graph) * On the top of the user's dashboard (less useful since others cannot see your activity graph) 2. **Data.** The model I'm using for data would need to be changed a bit as well (my visualization points to a JSON file not dynamically generated by gitea). We could modify gitea to cache per-user commit data (updated with a post-commit hook or a daily maintenance task) and provide an endpoint route, where you provide it a username and it returns commit data in a JSON form convenient for visualization. Thoughts? Potential roadblocks?

GiteaMirror commented

@lunny commented on GitHub (Feb 22, 2018):

I would like put it under the Public Activity tab and top of all the activities on user's profile. Like the below example image:

@lunny commented on GitHub (Feb 22, 2018): I would like put it under the `Public Activity` tab and top of all the activities on user's profile. Like the below example image: ![heatmap](https://user-images.githubusercontent.com/81045/36541240-e800db6a-17a2-11e8-94ab-73a4d70528ad.jpg)

GiteaMirror commented

@joubertredrat commented on GitHub (Feb 22, 2018):

@lunny I liked this approach, if possible to change color to green can be best.... or not, github is green too.

@joubertredrat commented on GitHub (Feb 22, 2018): @lunny I liked this approach, if possible to change color to green can be best.... or not, github is green too.

GiteaMirror commented

@charlesreid1 commented on GitHub (Feb 22, 2018):

Thanks @lunny for the suggestion, looks like a great spot for it.

@joubertredrat I'll do something plain and let the hipsters decide what color scheme to use. 🤓

@charlesreid1 commented on GitHub (Feb 22, 2018): Thanks @lunny for the suggestion, looks like a great spot for it. @joubertredrat I'll do something plain and let the hipsters decide what color scheme to use. 🤓

GiteaMirror commented

@lafriks commented on GitHub (Feb 22, 2018):

Green would be more consistent with gitea theme

@lafriks commented on GitHub (Feb 22, 2018): Green would be more consistent with gitea theme

GiteaMirror commented

@charlesreid1 commented on GitHub (Feb 22, 2018):

I will use ColorBrewer's YlGn theme, which looks like the same one Github uses and is nicely consistent with Gitea green.

@charlesreid1 commented on GitHub (Feb 22, 2018): I will use [ColorBrewer's YlGn theme](http://colorbrewer2.org/#type=sequential&scheme=YlGn&n=7), which looks like the same one Github uses and is nicely consistent with Gitea green. ![YlGn scheme](https://i.imgur.com/vj36GzK.png)

GiteaMirror commented

@charlesreid1 commented on GitHub (Feb 26, 2018):

It looks like gitea/models/graph.go may already implement a model for commit counts. Is that correct?

@charlesreid1 commented on GitHub (Feb 26, 2018): It looks like [gitea/models/graph.go](https://github.com/go-gitea/gitea/blob/master/models/graph.go) may already implement a model for commit counts. Is that correct?

GiteaMirror commented

@charlesreid1 commented on GitHub (Feb 26, 2018):

Maybe not... But it looks like graph.go might be useful for implementing commit counts. There are a couple of files referring to a commit graph. Is this related to the heatmap/calendar idea, or is this referring to the timeline that shows up on a user's/organization's page?

@charlesreid1 commented on GitHub (Feb 26, 2018): Maybe not... But it looks like graph.go might be useful for implementing commit counts. There are a couple of files referring to a commit graph. Is this related to the heatmap/calendar idea, or is this referring to the timeline that shows up on a user's/organization's page?

GiteaMirror commented

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

I think we need a new table to store the statistics information.

type UserHeatMap struct {
      ID int64
      UserID int64 `xorm:"index"`
      Day string `xorm:"char(10)"` // 2018-02-26
      Count int
}

And all needed data could read from table action.

@lunny commented on GitHub (Feb 26, 2018): I think we need a new table to store the statistics information. ```Go type UserHeatMap struct { ID int64 UserID int64 `xorm:"index"` Day string `xorm:"char(10)"` // 2018-02-26 Count int } ``` And all needed data could read from table `action`.

GiteaMirror commented

@thehowl commented on GitHub (Feb 26, 2018):

Could we not use a SQL DATE instead of a char(10)?

@thehowl commented on GitHub (Feb 26, 2018): Could we not use a SQL `DATE` instead of a `char(10)`?

GiteaMirror commented

@lafriks commented on GitHub (Feb 26, 2018):

Also if this is just activity from action table than probably no new table is needed just select user_id, date, count(1) with group by user_id, date?

@lafriks commented on GitHub (Feb 26, 2018): Also if this is just activity from `action` table than probably no new table is needed just `select user_id, date, count(1)` with `group by user_id, date`?

GiteaMirror commented

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

@lafriks that maybe
select date, count(1) from action where user_id = ? and date >= ? and date < ? order by date group by date

@lunny commented on GitHub (Feb 26, 2018): @lafriks that maybe `select date, count(1) from action where user_id = ? and date >= ? and date < ? order by date group by date`

GiteaMirror commented

@lafriks commented on GitHub (Feb 26, 2018):

@lunny yes that what I meant but I don't know if xorm supports such queries

@lafriks commented on GitHub (Feb 26, 2018): @lunny yes that what I meant but I don't know if xorm supports such queries

GiteaMirror commented

@lunny commented on GitHub (Feb 27, 2018):

type Stat struct {
      Date string
      Count int
}
var stats = make([]Stat,0, 366)
x.SQL(`select date, count(1) as count from action where user_id = ? and date >= ? and date < ? order by date group by date`, 
userID, startDate, endDate).Find(&stats)

@lunny commented on GitHub (Feb 27, 2018): ```Go type Stat struct { Date string Count int } var stats = make([]Stat,0, 366) x.SQL(`select date, count(1) as count from action where user_id = ? and date >= ? and date < ? order by date group by date`, userID, startDate, endDate).Find(&stats) ```

GiteaMirror commented

@thehowl commented on GitHub (Feb 27, 2018):

would that work in all dialects?

@thehowl commented on GitHub (Feb 27, 2018): would that work in all dialects?

GiteaMirror commented

@lafriks commented on GitHub (Feb 27, 2018):

If we have date column (do we?) than it should work

@lafriks commented on GitHub (Feb 27, 2018): If we have date column (do we?) than it should work

GiteaMirror commented

@lunny commented on GitHub (Feb 28, 2018):

@lafriks we can use created and remove the time parts.

@lunny commented on GitHub (Feb 28, 2018): @lafriks we can use created and remove the time parts.

GiteaMirror commented

@lafriks commented on GitHub (Feb 28, 2018):

@lunny don't think removing time part will be that easy so that it would perform good enaugh for such select

@lafriks commented on GitHub (Feb 28, 2018): @lunny don't think removing time part will be that easy so that it would perform good enaugh for such select

GiteaMirror commented

@akaia-shadowfox commented on GitHub (Jun 4, 2018):

It's possible to see this feature in near future?

@akaia-shadowfox commented on GitHub (Jun 4, 2018): It's possible to see this feature in near future?

GiteaMirror commented

@charlesreid1 commented on GitHub (Jun 4, 2018):

This effort was over my head, as there many decisions where I didn't have a clear sense of how to proceed, so I didn't get very far.

@charlesreid1 commented on GitHub (Jun 4, 2018): This effort was over my head, as there many decisions where I didn't have a clear sense of how to proceed, so I didn't get very far.

GiteaMirror commented

@kolaente commented on GitHub (Oct 18, 2018):

Any updates on this?

@kolaente commented on GitHub (Oct 18, 2018): Any updates on this?

GiteaMirror commented

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

@kolaente no people are working on this.

@lunny commented on GitHub (Oct 18, 2018): @kolaente no people are working on this.

GiteaMirror commented

@kolaente commented on GitHub (Oct 20, 2018):

Someone created a nice article on how to implement this externally: https://medium.com/@markuman/gitea-contribution-heatmap-af4be1d7a0d5

@kolaente commented on GitHub (Oct 20, 2018): Someone created a nice article on how to implement this externally: https://medium.com/@markuman/gitea-contribution-heatmap-af4be1d7a0d5

GiteaMirror commented

@kolaente commented on GitHub (Oct 20, 2018):

I'll try to implement this.

@kolaente commented on GitHub (Oct 20, 2018): I'll try to implement this.

GiteaMirror commented

@lafriks commented on GitHub (Oct 20, 2018):

Problem is with this part:

group by DATE_FORMAT(FROM_UNIXTIME(created_unix), '%Y%m%d')

At least I did not find a way to implement it for sqlite

@lafriks commented on GitHub (Oct 20, 2018): Problem is with this part: ```sql group by DATE_FORMAT(FROM_UNIXTIME(created_unix), '%Y%m%d') ``` At least I did not find a way to implement it for sqlite

GiteaMirror commented

@kolaente commented on GitHub (Oct 20, 2018):

@lafriks I did find one:

SELECT created_unix as timestamp, count(user_id) as contributions
FROM `action`
WHERE (user_id = 1)
  AND (created_unix > 1508503562)
GROUP BY strftime('%Y-%m-%d', created_unix, 'unixepoch')
ORDER BY created_unix

I think I'll do it with a hard switch to provide another query if we are dealing with an sqlite db.

@kolaente commented on GitHub (Oct 20, 2018): @lafriks I did find one: ```sql SELECT created_unix as timestamp, count(user_id) as contributions FROM `action` WHERE (user_id = 1) AND (created_unix > 1508503562) GROUP BY strftime('%Y-%m-%d', created_unix, 'unixepoch') ORDER BY created_unix ``` I think I'll do it with a hard switch to provide another query if we are dealing with an sqlite db.

GiteaMirror commented

@lafriks commented on GitHub (Oct 20, 2018):

I think you will need different for mssql and Postgres also

@lafriks commented on GitHub (Oct 20, 2018): I think you will need different for mssql and Postgres also

GiteaMirror commented

@kolaente commented on GitHub (Oct 20, 2018):

PR is up: #5131

@kolaente commented on GitHub (Oct 20, 2018): PR is up: #5131

GiteaMirror commented