Patrick DeVivo Avatar

Patrick DeVivo

Patrick DeVivoaugmentable.medium.com

Identifying code churn with AskGit SQL

In which I detail A SQL query that helps you identify files in a codebase that have “churned” in the past year. In other words, list the files that have been changed by the most number of commits in the last year.

SELECT file,
       COUNT(*)
FROM   stats
       JOIN commits
         ON stats.commit_id = commits.id
WHERE  commits.author_when > DATE('now', '-12 month')
       AND commits.parent_count < 2 -- ignore merge commits
GROUP  BY file
ORDER  BY COUNT(*) DESC
LIMIT  50

Patrick DeVivoaskgit.com

AskGit - query your git repo with SQL

Built in Go, askgit is an open source CLI and coming soon web interface (linked above). With this tool in your toolbox, you can mine your repo for info like commit count by author on each day of the week:

SELECT
    count(*) AS commits,
    count(CASE WHEN strftime('%w',author_when)='0' THEN 1 END) AS sunday,
    count(CASE WHEN strftime('%w',author_when)='1' THEN 1 END) AS monday,
    count(CASE WHEN strftime('%w',author_when)='2' THEN 1 END) AS tuesday,
    count(CASE WHEN strftime('%w',author_when)='3' THEN 1 END) AS wednesday,
    count(CASE WHEN strftime('%w',author_when)='4' THEN 1 END) AS thursday,
    count(CASE WHEN strftime('%w',author_when)='5' THEN 1 END) AS friday,
    count(CASE WHEN strftime('%w',author_when)='6' THEN 1 END) AS saturday,
    author_email
FROM commits GROUP BY author_email ORDER BY commits

Patrick DeVivogithub.com

Using SQL to query git repos

gitqlite is a tool for running SQL queries on git repositories. It implements SQLite virtual tables and uses go-git. It’s meant for ad-hoc querying of git repositories on disk through a common interface (SQL), as an alternative to patching together various shell commands.

Mine your repo’s history for goodies. Here’s how to get commit count by author email:

SELECT author_email, count(*) FROM commits GROUP BY author_email ORDER BY count(*) DESC

Patrick DeVivogithub.com

Identify the most relevant git contributors based on commit recency, frequency, and impact

gitpert measures the “pertinence” of git authors as a time-decayed measure of LOC added and removed to a repository (or a set of files in a repository). It’s meant to help identify who the most relevant contributors are based on commit recency, frequency and impact.

Cool tool, as long as we don’t forget about non-code contributors.

Patrick DeVivotickgit.com

Never forget a #TODO comment

Patrick DeVivo:

tickgit is a tool for software developers to do project management within their codebase. It searches code comments for markers indicating areas and files worth returning to (TODO, FIXME, etc). It can be used to proactively identify areas of technical debt, or handle day-to-day to-do items and checklists.

Free for public repos and open source.

0:00 / 0:00