search

simonw / git-history

Tools for analyzing Git history using SQLite
Apache License 2.0
191 stars 18 forks source link
git sqlite

readme

git-history

PyPI Changelog Tests License

Tools for analyzing Git history using SQLite

For background on this project see git-history: a tool for analyzing scraped data collected using Git and SQLite.

Measuring traffic during the Half Moon Bay Pumpkin Festival describes a project using this tool in detail.

Installation

Install this tool using pip:

$ pip install git-history

Demos

git-history-demos.datasette.io hosts three example databases created using this tool:

The demos are deployed using Datasette on Google Cloud Run by this GitHub Actions workflow.

Usage

This tool can be run against a Git repository that holds a file that contains JSON, CSV/TSV or some other format and which has multiple versions tracked in the Git history. Read Git scraping: track changes over time by scraping to a Git repository to understand how you might create such a repository.

The file command analyzes the history of an individual file within the repository, and generates a SQLite database table that represents the different versions of that file over time.

The file is assumed to contain multiple objects - for example, the results of scraping an electricity outage map or a CSV file full of records.

Assuming you have a file called incidents.json that is a JSON array of objects, with multiple versions of that file recorded in a repository. Each version of that file might look something like this:

[
    {
        "IncidentID": "abc123",
        "Location": "Corner of 4th and Vermont",
        "Type": "fire"
    },
    {
        "IncidentID": "cde448",
        "Location": "555 West Example Drive",
        "Type": "medical"
    }
]

Change directory into the GitHub repository in question and run the following:

git-history file incidents.db incidents.json

This will create a new SQLite database in the incidents.db file with three tables:

The database schema for this example will look like this:

CREATE TABLE [namespaces] (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT
);
CREATE UNIQUE INDEX [idx_namespaces_name]
    ON [namespaces] ([name]);
CREATE TABLE [commits] (
   [id] INTEGER PRIMARY KEY,
   [namespace] INTEGER REFERENCES [namespaces]([id]),
   [hash] TEXT,
   [commit_at] TEXT
);
CREATE UNIQUE INDEX [idx_commits_namespace_hash]
    ON [commits] ([namespace], [hash]);
CREATE TABLE [item] (
   [IncidentID] TEXT,
   [Location] TEXT,
   [Type] TEXT,
   [_commit] INTEGER REFERENCES [commits]([id])
);

If you have 10 historic versions of the incidents.json file and each one contains 30 incidents, you will end up with 10 * 30 = 300 rows in your item table.

Track the history of individual items using IDs

If your objects have a unique identifier - or multiple columns that together form a unique identifier - you can use the --id option to de-duplicate and track changes to each of those items over time.

This provides a much more interesting way to apply this tool.

If there is a unique identifier column called IncidentID you could run the following:

git-history file incidents.db incidents.json --id IncidentID

The database schema used here is very different from the one used without the --id option.

If you have already imported history, the command will skip any commits that it has seen already and just process new ones. This means that even though an initial import could be slow subsequent imports should run a lot faster.

This command will create six tables - commits, item, item_version, columns, item_changed and namespaces.

Here's the full schema:

CREATE TABLE [namespaces] (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT
);
CREATE UNIQUE INDEX [idx_namespaces_name]
    ON [namespaces] ([name]);
CREATE TABLE [commits] (
   [id] INTEGER PRIMARY KEY,
   [namespace] INTEGER REFERENCES [namespaces]([id]),
   [hash] TEXT,
   [commit_at] TEXT
);
CREATE UNIQUE INDEX [idx_commits_namespace_hash]
    ON [commits] ([namespace], [hash]);
CREATE TABLE [item] (
   [_id] INTEGER PRIMARY KEY,
   [_item_id] TEXT
, [IncidentID] TEXT, [Location] TEXT, [Type] TEXT, [_commit] INTEGER);
CREATE UNIQUE INDEX [idx_item__item_id]
    ON [item] ([_item_id]);
CREATE TABLE [item_version] (
   [_id] INTEGER PRIMARY KEY,
   [_item] INTEGER REFERENCES [item]([_id]),
   [_version] INTEGER,
   [_commit] INTEGER REFERENCES [commits]([id]),
   [IncidentID] TEXT,
   [Location] TEXT,
   [Type] TEXT,
   [_item_full_hash] TEXT
);
CREATE TABLE [columns] (
   [id] INTEGER PRIMARY KEY,
   [namespace] INTEGER REFERENCES [namespaces]([id]),
   [name] TEXT
);
CREATE UNIQUE INDEX [idx_columns_namespace_name]
    ON [columns] ([namespace], [name]);
CREATE TABLE [item_changed] (
   [item_version] INTEGER REFERENCES [item_version]([_id]),
   [column] INTEGER REFERENCES [columns]([id]),
   PRIMARY KEY ([item_version], [column])
);
CREATE VIEW item_version_detail AS select
  commits.commit_at as _commit_at,
  commits.hash as _commit_hash,
  item_version.*,
  (
    select json_group_array(name) from columns
    where id in (
      select column from item_changed
      where item_version = item_version._id
    )
) as _changed_columns
from item_version
  join commits on commits.id = item_version._commit;
CREATE INDEX [idx_item_version__item]
    ON [item_version] ([_item]);

item table

The item table will contain the most recent version of each row, de-duplicated by ID, plus the following additional columns:

item_version table

The item_version table will contain a row for each captured differing version of that item, plus the following columns:

The other columns in this table represent columns in the original data that have changed since the previous version. If the value has not changed, it will be represented by a null.

If a value was previously set but has been changed back to null it will still be represented as null in the item_version row. You can identify these using the item_changed many-to-many table described below.

You can use the --full-versions option to store full copies of the item at each version, rather than just storing the columns that have changed.

item_version_detail view

This SQL view joins item_version against commits to add three further columns: _commit_at with the date of the commit, and _commit_hash with the Git commit hash.

item_changed

This many-to-many table indicates exactly which columns were changed in an item_version.

This table with have the largest number of rows, which is why it stores just two integers in order to save space.

columns

The columns table stores column names. It is referenced by item_changed.

Reserved column names

Note that _id, _item_full_hash, _item, _item_id, _version, _commit, _item_id, _commit_at, _commit_hash, _changed_columns, rowid are considered reserved column names for the purposes of this tool.

If your data contains any of these they will be renamed to add a trailing underscore, for example _id_, _item_, _version_, to avoid clashing with the reserved columns.

If you have a column with a name such as _commit_ it will be renamed too, adding an additional trailing underscore, so _commit_ becomes _commit__ and _commit__ becomes _commit___.

Additional options

CSV and TSV data

If the data in your repository is a CSV or TSV file you can process it by adding the --csv option. This will attempt to detect which delimiter is used by the file, so the same option works for both comma- and tab-separated values.

git-history file trees.db trees.csv --id TreeID

You can also specify the CSV dialect using the --dialect option.

Custom conversions using --convert

If your data is not already either CSV/TSV or a flat JSON array, you can reshape it using the --convert option.

The format needed by this tool is an array of dictionaries, as demonstrated by the incidents.json example above.

If your data does not fit this shape, you can provide a snippet of Python code to converts the on-disk content of each stored file into a Python list of dictionaries.

For example, if your stored files each look like this:

{
    "incidents": [
        {
            "id": "552",
            "name": "Hawthorne Fire",
            "engines": 3
        },
        {
            "id": "556",
            "name": "Merlin Fire",
            "engines": 1
        }
    ]
}

You could use the following Python snippet to convert them to the required format:

json.loads(content)["incidents"]

(The json module is exposed to your custom function by default.)

You would then run the tool like this:

git-history file database.db incidents.json \
  --id id \
  --convert 'json.loads(content)["incidents"]'

The content variable is always a bytes object representing the content of the file at a specific moment in the repository's history.

You can import additional modules using --import. This example shows how you could read a CSV file that uses ; as the delimiter:

git-history file trees.db ../sf-tree-history/Street_Tree_List.csv \
  --repo ../sf-tree-history \
  --import csv \
  --import io \
  --convert '
    fp = io.StringIO(content.decode("utf-8"))
    return list(csv.DictReader(fp, delimiter=";"))
    ' \
  --id TreeID

You can import nested modules such as ElementTree using --import xml.etree.ElementTree, then refer to them in your function body as xml.etree.ElementTree. For example, if your tracked data was in an items.xml file that looked like this:

<items>
  <item id="1" name="One" />
  <item id="2" name="Two" />
  <item id="3" name="Three" />
</item>

You could load it using the following --convert script:

git-history file items.xml --convert '
tree = xml.etree.ElementTree.fromstring(content)
return [el.attrib for el in tree.iter("item")]
' --import xml.etree.ElementTree --id id

If your Python code spans more than one line it needs to include a return statement.

You can also use Python generators in your --convert code, for example:

git-history file stats.db package-stats/stats.json \
    --repo package-stats \
    --convert '
    data = json.loads(content)
    for key, counts in data.items():
        for date, count in counts.items():
            yield {
                "package": key,
                "date": date,
                "count": count
            }
    ' --id package --id date

This conversion function expects data that looks like this:

{
    "airtable-export": {
        "2021-05-18": 66,
        "2021-05-19": 60,
        "2021-05-20": 87
    }
}

Development

To contribute to this tool, first checkout the code. Then create a new virtual environment:

cd git-history
python -m venv venv
source venv/bin/activate

Or if you are using pipenv:

pipenv shell

Now install the dependencies and test dependencies:

pip install -e '.[test]'

To run the tests:

pytest

To update the schema examples in this README file:

cog -r README.md