Script for converting the output of https://github.com/tarmstrong/git2json to SQLite

git_json_to_sqlite.py

import json
import os
import sqlite3


def init_db(filename):
    if os.path.exists(filename):
        return

    conn = sqlite3.connect(filename)
    conn.executescript(
        """
    CREATE TABLE [commit] (
        [commit] VARCHAR(40),
        tree TEXT,
        author_name TEXT,
        author_email TEXT,
        author_date INTEGER,
        author_timezone TEXT,
        committer_name TEXT,
        committer_email TEXT,
        committer_date INTEGER,
        committer_timezone TEXT,
        message TEXT,
        PRIMARY KEY ([commit])
    );
    CREATE TABLE [change] (
        [commit] VARCHAR(40),
        added INTEGER,
        deleted INTEGER,
        filepath TEXT,
        FOREIGN KEY ([commit]) REFERENCES [commit]("commit")
    );
    CREATE TABLE parent (
        [commit] VARCHAR(40),
        parent VARCHAR(40),
        FOREIGN KEY ("parent") REFERENCES [commit]("commit")
    );
    CREATE INDEX parent_commit ON parent("commit");
    CREATE INDEX parent_parent ON parent("parent");
    """
    )
    conn.close()


def create_and_populate_fts(conn):
    create_sql = """
        CREATE VIRTUAL TABLE "commit_fts"
        USING FTS4 (author_name, committer_name, message, content="commit")
    """
    conn.executescript(create_sql)
    conn.executescript(
        """
        INSERT INTO "commit_fts" (rowid, author_name, committer_name, message)
        SELECT rowid, author_name, committer_name, message
        FROM [commit];
    """)


def insert_or_replace(conn, table, record):
    pairs = record.items()
    columns = [p[0] for p in pairs]
    params = [p[1] for p in pairs]
    sql = """
        INSERT OR REPLACE INTO [{table}] ({column_list})
        VALUES ({value_list});
    """.format(
        table=table,
        column_list=", ".join('[{}]'.format(c) for c in columns),
        value_list=", ".join(["?" for p in params]),
    )
    print(sql)
    conn.execute(sql, params)


def parse_and_load(data, db):
    for commit in data:
        insert_or_replace(
            db,
            "commit",
            {
                "commit": commit["commit"],
                "tree": commit["tree"],
                "author_name": commit["author"]["name"],
                "author_email": commit["author"]["email"],
                "author_date": commit["author"]["date"],
                "author_timezone": commit["author"]["timezone"],
                "committer_name": commit["committer"]["name"],
                "committer_email": commit["committer"]["email"],
                "committer_date": commit["committer"]["date"],
                "committer_timezone": commit["committer"]["timezone"],
                "message": commit["message"],
            },
        )
        for parent in commit["parents"]:
            insert_or_replace(
                db,
                "parent",
                {
                    "commit": commit["commit"],
                    "parent": parent,
                },
            )
        for change in commit["changes"]:
            insert_or_replace(
                db,
                "change",
                {
                    "commit": commit["commit"],
                    "added": change[0],
                    "deleted": change[1],
                    "filepath": change[2],
                },
            )


if __name__ == "__main__":
    import sys

    dbfile = sys.argv[-1]
    assert dbfile.endswith(".db")
    init_db(dbfile)
    db = sqlite3.connect(dbfile)
    # Read from stdin
    parse_and_load(json.load(sys.stdin), db)
    create_and_populate_fts(db)
    db.close()

Usage:

git2json | python git_json_to_sqlite.py commits.db