Patrick DeVivo
Posted on July 6, 2020
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
💖 💪 🙅 🚩
Patrick DeVivo
Posted on July 6, 2020
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related