DEV Community

Cover image for Daily Commit Counts as a Terminal Graph w/ Emojis 🚀
Patrick DeVivo
Patrick DeVivo

Posted on

Daily Commit Counts as a Terminal Graph w/ Emojis 🚀

Here's a use case of a project I recently made public. It's called gitqlite and lets users run SQL queries on git repositories.

Display a terminal chart of commit counts for a repo (facebook/react, here) by day, for the past N days (30 in this case):

cat query.sql | gitqlite --repo https://github.com/facebook/react
+------------+-----+-------+----------------------------
|    DAY     | DOW | COUNT |          COMMITS           
+------------+-----+-------+----------------------------
| 2020-07-06 | Mon |     6 | ✅✅✅✅✅✅               
| 2020-07-05 | Sun |     0 |                            
| 2020-07-04 | Sat |     0 |                            
| 2020-07-03 | Fri |     0 |                            
| 2020-07-02 | Thu |     2 | ✅✅                       
| 2020-07-01 | Wed |    13 | ✅✅✅✅✅✅✅✅✅✅✅✅✅ 
| 2020-06-30 | Tue |     8 | ✅✅✅✅✅✅✅✅           
| 2020-06-29 | Mon |     1 | ✅                         
| 2020-06-28 | Sun |     0 |                            
| 2020-06-27 | Sat |     0 |                            
| 2020-06-26 | Fri |     2 | ✅✅                       
| 2020-06-25 | Thu |     2 | ✅✅                       
| 2020-06-24 | Wed |     0 |                            
| 2020-06-23 | Tue |     5 | ✅✅✅✅✅                 
| 2020-06-22 | Mon |     0 |                            
| 2020-06-21 | Sun |     0 |                            
| 2020-06-20 | Sat |     0 |                            
| 2020-06-19 | Fri |     2 | ✅✅                       
| 2020-06-18 | Thu |     0 |                            
| 2020-06-17 | Wed |     1 | ✅                         
| 2020-06-16 | Tue |     2 | ✅✅                       
| 2020-06-15 | Mon |     1 | ✅                         
| 2020-06-14 | Sun |     0 |                            
| 2020-06-13 | Sat |     1 | ✅                         
| 2020-06-12 | Fri |     3 | ✅✅✅                     
| 2020-06-11 | Thu |     1 | ✅                         
| 2020-06-10 | Wed |     0 |                            
| 2020-06-09 | Tue |     0 |                            
| 2020-06-08 | Mon |     0 |                            
| 2020-06-07 | Sun |     0 |                            
+------------+-----+-------+----------------------------

The SQL in query.sql looks like:

WITH RECURSIVE
days(day) AS (
    SELECT strftime('%Y-%m-%d', DATETIME('now'))
    UNION ALL
    SELECT strftime('%Y-%m-%d', (SELECT DATETIME(day, '-1 day'))) FROM days
    LIMIT 30
)
SELECT
    days.day,
    substr('SunMonTueWedThuFriSat', 1 + 3*strftime('%w', days.day), 3) as dow,
    IFNULL(count, 0) AS count,
    CASE count IS NULL WHEN true THEN '' ELSE printf('%.' || (IFNULL(count, 0)) || 'c', '✅') END AS commits
FROM days LEFT JOIN (
    SELECT strftime('%Y-%m-%d', committer_when) AS day, count(*) AS count FROM commits GROUP BY day
) c ON c.day = days.day

More examples are available in the README

Top comments (0)