SQL Examples

If you want to know what the database schema looks like, you can refer to the diagram about gitbase entities and relations, or just use regular SHOW or DESCRIBE queries.

In gitbase repository, you will find more SQL examples of queries.

Index

• Queries For Repositories

• Queries With Files

• Queries With UASTs

• Queries About Comitters

Queries For Repositories

Show me the repositories I am analyzing:

SELECT * FROM repositories;

Last commit messages for HEAD for every repository

SELECT commit_message
FROM refs
NATURAL JOIN commits
WHERE ref_name = 'HEAD';

Top 10 repositories by commit count from HEAD:

SELECT repository_id,commit_count
FROM (
    SELECT
        repository_id,
        COUNT(*) AS commit_count
    FROM ref_commits
    WHERE ref_name = 'HEAD'
    GROUP BY repository_id
) AS q
ORDER BY commit_count DESC
LIMIT 10;

10 top repos by file count at HEAD

SELECT repository_id, num_files FROM (
    SELECT COUNT(*) num_files, repository_id
    FROM refs
    NATURAL JOIN commit_files
    WHERE ref_name = 'HEAD'
    GROUP BY repository_id
) AS t
ORDER BY num_files DESC
LIMIT 10;

Queries With Files

Query for all LICENSE & README files across history:

SELECT file_path, repository_id, blob_size
FROM files
WHERE
    file_path = 'LICENSE'
    OR file_path = 'README.md';

Query all files at HEAD:

SELECT cf.file_path, f.blob_size
FROM ref_commits rc
NATURAL JOIN commit_files cf
NATURAL JOIN files f
WHERE
    rc.ref_name = 'HEAD'
    AND rc.history_index = 0;

Queries With UASTs

Note: UAST values are returned as binary blobs; they're best visualized in the web UI interface rather than the CLI where are seen as binary data.

Retrieve the UAST for all files at HEAD:

SELECT * FROM (
    SELECT cf.file_path,
        UAST(f.blob_content, LANGUAGE(f.file_path,  f.blob_content)) as uast
    FROM ref_commits r
    NATURAL JOIN commit_files cf
    NATURAL JOIN files f
    WHERE
        r.ref_name = 'HEAD'
        AND r.history_index = 0
) t WHERE uast != '';

Queries About Comitters

Top committers per repository

SELECT * FROM (
    SELECT
        commit_author_email as author,
        repository_id as id,
        count(*) as num_commits
    FROM commits
    GROUP BY commit_author_email, repository_id
) AS t
ORDER BY num_commits DESC;