gitqlite
gitqlite
is a tool for running SQL queries on git repositories.
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.
It can execute queries that look like:
-- how many commits have been authored by user@email.com?
SELECT count(*) FROM commits WHERE author_email = 'user@email.com'
More in-depth examples and documentation can be found below.
Installation
More Installation Instructions Coming Soon
go get -v -tags=sqlite_vtable github.com/augmentable-dev/gitqlite
Will use the go tool chain to install a binary to $GOBIN
.
GOBIN=$(pwd) go get -v -tags=sqlite_vtable github.com/augmentable-dev/gitqlite
Will produce a binary in your current directory.
Using Docker
Build an image locally using docker
docker build -t gitqlite:latest .
Or use an official image from docker hub
docker pull augmentable/gitqlite:latest
Running commands
gitqlite
operates on a git repository. This repository needs to be attached as a volume. This example uses the (bash) built-in command pwd
for the current working directory
[pwd] Print the absolute pathname of the current working directory.
docker run -v `pwd`:/repo:ro gitqlite "SELECT * FROM commits"
Running commands from STDIN
For piping commands via STDIN, the docker command needs to be told to run non-interactively, as well as attaching the repository at /repo
.
cat query.sql | docker run -i -v `pwd`:/repo:ro gitqlite
Usage
gitqlite -h
Will output the most up to date usage instructions for your version of the CLI.
Typically the first argument is a SQL query string:
gitqlite "SELECT * FROM commits"
Your current working directory will be used as the path to the git repository to query by default.
Use the --repo
flag to specify an alternate path, or even a remote repository reference (http(s) or ssh).
gitqlite
will clone the remote repository to a temporary directory before executing a query.
You can also pass a query in via stdin
:
cat query.sql | gitqlite
By default, output will be an ASCII table.
Use --format json
or --format csv
for alternatives.
See -h
for all the options.
Tables
commits
Similar to git log
, the commits
table includes all commits in the history of the currently checked out commit.
Column |
Type |
id |
TEXT |
message |
TEXT |
summary |
TEXT |
author_name |
TEXT |
author_email |
TEXT |
author_when |
DATETIME |
committer_name |
TEXT |
committer_email |
TEXT |
committer_when |
DATETIME |
parent_id |
TEXT |
parent_count |
INT |
tree_id |
TEXT |
additions |
INT |
deletions |
INT |
files
The files
table iterates over ALL the files in a commit history, by default from what's checked out in the repository.
The full table is every file in every tree of a commit history.
Use the commit_id
column to filter for files that belong to the work tree of a specific commit.
Column |
Type |
commit_id |
TEXT |
tree_id |
TEXT |
file_id |
TEXT |
name |
TEXT |
mode |
TEXT |
type |
TEXT |
contents |
TEXT |
refs
Column |
Type |
name |
TEXT |
type |
TEXT |
hash |
TEXT |
Example Queries
This will return all commits in the history of the currently checked out branch/commit of the repo.
SELECT * FROM commits
Return the (de-duplicated) email addresses of commit authors:
SELECT DISTINCT author_email FROM commits
Return the commit counts of every author (by email):
SELECT author_email, count(*) FROM commits GROUP BY author_email ORDER BY count(*) DESC
Same as above, but excluding merge commits:
SELECT author_email, count(*) FROM commits WHERE parent_count < 2 GROUP BY author_email ORDER BY count(*) DESC
This is an expensive query.
It will iterate over every file in every tree of every commit in the current history:
SELECT * FROM files
Outputs the set of files in the tree of a certain commit:
SELECT * FROM files WHERE commit_id='some_commit_id'
Same as above if you just have the commit short id:
SELECT * FROM files WHERE commit_id LIKE 'shortened_commit_id%'
Returns author emails with lines added/removed, ordered by total number of commits in the history:
SELECT count(*) AS commits, SUM(additions) AS additions, SUM(deletions) AS deletions, author_email FROM commits GROUP BY author_email ORDER BY commits
Returns commit counts by author, broken out by 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