search

go-gitea / gitea

Git with a cup of tea! Painless self-hosted all-in-one software development service, including Git hosting, code review, team collaboration, package registry and CI/CD
https://gitea.com
MIT License
44.39k stars 5.43k forks source link

Hack: history heatmap for user #4918

Closed markuman closed 5 years ago

markuman commented 6 years ago

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 commented 6 years ago

Could you send a PR ?

markuman commented 6 years ago

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

markuman commented 6 years ago

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 6 years ago

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 commented 6 years ago

It seems the SQL is not complex.

yasuokav commented 5 years ago

this one should be closed?

lunny commented 5 years ago

duplicated with #6