4 posts tagged with "git"

For organizations using GitHub Enterprise, the GitHub audit log is now supported as a table in the MergeStat CLI, enabling SQL queries against audit log events 🎉. Check it out!

The new table-value-function is github_org_audit_log, and may be queried like so:

SELECT * FROM github_org_audit_log('mergestat') -- replace with your org name

and has the following columns (type is TEXT unless otherwise specified):

• id
• entry_type
• action
• actor_type
• actor_login
• actor_ip
• actor_location (JSON)
• created_at (DATETIME)
• operation_type
• user_login

This enables some interesting use-cases for GitHub enterprise users looking to monitor, alert and report on events from the GitHub audit log. We're working with some early users of this table, and will publish some guides and examples in the near future 🔜.

The JSON column (actor_location) can be accessed with the SQLite JSON operators.

SELECT actor_location->>'country' FROM github_org_audit_log('mergestat')

+----------------------------+
| ACTOR_LOCATION->>'COUNTRY' |
+----------------------------+
| United States              |
+----------------------------+
| United States              |
+----------------------------+
| Belgium                    |
+----------------------------+
| ...                        |
+----------------------------+

It's important to note the following from the GitHub API docs as well:

SQLite 3.38.0 (released 2022-02-22) added support for built-in JSON functions (no longer a compile-time option) and two new JSON operators, -> and ->>, similar to ones available in MySQL and PostgreSQL.

MergeStat is a SQL interface to data in git repositories, based on SQLite. As such, we can now take advantage of these new operators in our queries, wherever JSON data is returned.

brew tap mergestat/mergestat
brew install mergestat

package.json​

An easy source of JSON to start with is the package.json file present in most JavaScript/TypeScript codebases. Let's take a look at the one in the popular tailwindlabs/tailwindcss repo.

SELECT contents->>'name', contents->>'license' FROM files WHERE path = 'package.json'

+-------------------+----------------------+
| CONTENTS->>'NAME' | CONTENTS->>'LICENSE' |
+-------------------+----------------------+
| tailwindcss       | MIT                  |
+-------------------+----------------------+

Here we use contents->>'name' and contents->>'license' to pull out the name and license fields from the package.json JSON file.

Let's take this up a level, and look at at the name and license fields for all repos in an org where a package.json file is present. Sticking with tailwindlabs (and now adding a GITHUB_TOKEN when we execute the following):

-- CTE (https://www.sqlite.org/lang_with.html)
-- to select all the package.json files from repos in the tailwindlabs GitHub org
WITH package_json_files AS (
    SELECT
        name,
        github_repo_file_content('tailwindlabs', repo.name, 'package.json') AS package_json
    FROM github_org_repos('tailwindlabs') repo
)
SELECT
    name as repo_name,
    package_json->>'name',
    package_json->>'license'
FROM package_json_files
WHERE package_json IS NOT NULL

+-----------------------------+-----------------------------+--------------------------+
| REPO_NAME                   | PACKAGE_JSON->>'NAME'       | PACKAGE_JSON->>'LICENSE' |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss                 | tailwindcss                 | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| webpack-starter             | NULL                        | NULL                     |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss.com             | NULL                        | NULL                     |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss-plugin-examples | NULL                        | NULL                     |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss-intellisense    | root                        | NULL                     |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss-playground      | NULL                        | NULL                     |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss-custom-forms    | @tailwindcss/custom-forms   | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss-typography      | @tailwindcss/typography     | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| heroicons                   | heroicons                   | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| tailwindui-vue              | @tailwindui/vue             | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| blog.tailwindcss.com        | tailwind-blog               | NULL                     |
+-----------------------------+-----------------------------+--------------------------+
| tailwindui-react            | @tailwindui/react           | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| heroicons.com               | NULL                        | NULL                     |
+-----------------------------+-----------------------------+--------------------------+
| play.tailwindcss.com        | play.tailwindcss.com        | NULL                     |
+-----------------------------+-----------------------------+--------------------------+
| headlessui                  | headlessui                  | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| tailwind-play-api           | NULL                        | NULL                     |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss-aspect-ratio    | @tailwindcss/aspect-ratio   | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss-forms           | @tailwindcss/forms          | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss-line-clamp      | @tailwindcss/line-clamp     | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| tailwindcss-jit             | @tailwindcss/jit            | MIT                      |
+-----------------------------+-----------------------------+--------------------------+
| prettier-plugin-tailwindcss | prettier-plugin-tailwindcss | NULL                     |
+-----------------------------+-----------------------------+--------------------------+

We see that most repos with a package.json file also have a name, while some do not (NULL). All the declared licences appear to be MIT.

Dependencies​

Finally, let's take a look at all the dependencies declared in package.json files across (JavaScript/TypeScript) codebases in an org. This time, let's try on the freeCodeCamp GitHub org.

WITH package_json_files AS (
    SELECT
        name,
        github_repo_file_content('freeCodeCamp', repo.name, 'package.json') AS package_json
    FROM github_org_repos('freeCodeCamp') repo
    WHERE (primary_language = 'TypeScript' OR primary_language = 'JavaScript')
)
SELECT
    count(*),
    json_group_array(name) AS repo_names,
    dep.key
FROM package_json_files, json_each(package_json->'dependencies') dep
WHERE package_json IS NOT NULL
GROUP BY dep.key
ORDER BY count(*) DESC

This query will show the most frequently used dependencies within a GitHub org (as extracted from the dependencies JSON value of package.json files). Here are the top 10 results from the above query, on freeCodeCamp:

1. dotenv (35)
2. express (31)
3. body-parser (26)
4. react-dom (16)
5. react (16)
6. lodash (16)
7. cors (14)
8. mongoose (12)
9. chai (12)
10. passport (11)

We had a user looking for a way to track merged GitHub pull requests that were unapproved (when merged into a main branch). This is typically done with an "Admin Override" when merging a PR, or from loose branch protection rules allowing for unapproved merges into a branch.

Allowing repo administrators to merge without a review can be a useful shortcut for "emergency" hotfixes that avoid a potentially time-consuming code review cycle. This of course depends on the habits and culture of your engineering organization.

Too many unapproved merges, however, is probably a sign of something wrong (again, depending on the organization and codebase). For instance, a high volume of "emergency fixes" shipping without any review, or developers simply short-circuiting the review cycle to get code merged/deployed quickly.

Surfacing Unapproved, Merged PRs​

The following MergeStat query will list all unapproved pull requests merged into the main branch.

SELECT
    number,
    title,
    date(created_at) AS created_at,
    date(merged_at) AS merged_at, 
    author_login,
    review_decision,
    merged
FROM github_prs('mergestat', 'mergestat') -- set to your own repo
WHERE
    merged = true
    AND review_decision <> 'APPROVED'
    AND base_ref_name = 'main' -- set to your own branch
ORDER BY created_at DESC

This will yield a table that looks something like (in our web app):

If you'd like to know which GitHub users most frequently merge without an approval, you can run the following:

SELECT
    count(*), merged_by
FROM github_prs('mergestat', 'mergestat')
WHERE
    merged = true
    AND review_decision <> 'APPROVED'
    AND base_ref_name = 'main'
GROUP BY merged_by
ORDER BY count(*) DESC

which will display an ordered list of the most common "unapproved mergers" in a codebase.

Additional Ideas​

1. Filter by created_at or merged_at if you only care about recently merged PRs
2. Query for unapproved (and merged) PRs across an entire GitHub org (rather than a single repo)
3. Create a Slack/Discord bot that alerts when an unnapproved PR is merged, or shares a monthly report
4. Track in a dashboard (a line chart) to record instances over time

It's a new year, and a post listing the top 30 committers to the Blender project in 2021 was on the front-page of HN this morning.

Below is the list of top committers to the Blender project in 2021. The amount of commits obviously doesn’t mean much, but it’s a neutral metric to put limelight on people who made Blender possible last year.

As the post says, counting commits doesn't mean much alone, but it's a decent proxy for relative activity within a codebase. As a maintainer of the MergeStat project, a SQL interface to data in git, I wanted to show how to make a similar list for any repo, and take it a step further with some additional metrics.

Top 30 Contributors by Commit Count​

Replicating the list in the original blog post can be done with the following query:

SELECT
    count(*), author_name, author_email
FROM commits
WHERE parents < 2 -- ignore merge commits
AND strftime('%Y', author_when) = '2021' -- commits only authored in 2021
GROUP BY author_name, author_email
ORDER BY count(*) DESC
LIMIT 30

This yields a list of names (ordered and with counts) very similar to what's in the original post 🙂.

Adding Commit Stats​

The stats table allows us to also look at the number of files modified in each commit as well as LOC added or removed. We can join the stats table with commits to measure:

1. Total lines added by a contributor
2. Total lines removed by a contributor
3. Distinct files modified by a contributor

In addition to just counting the number of commits.

SELECT
    count(DISTINCT hash) AS commits,
    sum(additions),
    sum(deletions),
    count(DISTINCT file_path),
    author_name, author_email
FROM commits, stats('', commits.hash)
WHERE parents < 2 -- ignore merge commits
AND strftime('%Y', author_when) = '2021' -- commits only authored in 2021
GROUP BY author_name, author_email
ORDER BY count(*) DESC
LIMIT 30

Similar to counting commits, LOC added or removed doesn't mean much indepedently. Files modified (distinct files changed by an author over the year) is a bit more interesting - depending on the size/nature of a codebase, it could be a measure of how "deeply" involved a particular contributor is - i.e. someone who contributes to all aspects of the project, not just a subset of it.

Making Some Graphs​

Finally, by adding the --format csv flag to the mergestat command when executing the above queries, we can copy the output into a spreadsheet to make some charts.

These queries should be portable to your own repositories (or any other). Try them out in our web app or with our CLI.