使用 SQLite 分析 Git 历史的工具

关于此项目的背景信息，请参阅git-history：一个用于分析使用 Git 和 SQLite 收集的抓取数据的工具。

衡量半月湾南瓜节期间的流量详细描述了一个使用此工具的项目。

使用 pip 安装此工具

$ pip install git-history

git-history-demos.datasette.io 上托管了使用此工具创建的三个示例数据库

• pge-outages 展示了 PG&E（电力供应商）的 停电历史，使用了在 simonw/pge-outages 中收集的数据，并使用 pge-outages.sh 进行了转换
• ca-fires 展示了在 fire.ca.gov/incidents 上报告的加州火灾历史，使用了在 simonw/ca-fires-history 中的数据，并使用 ca-fires.sh 进行了转换

这些演示使用 Datasette 部署在 Google Cloud Run 上，由 此 GitHub Actions 工作流程 完成。

此工具可用于分析包含 JSON、CSV/TSV 或其他格式文件，并且在 Git 历史中跟踪了多个版本变更的 Git 仓库。阅读 Git scraping：通过抓取到 Git 仓库来跟踪随时间变化的变更，了解如何创建此类仓库。

file 命令分析仓库中单个文件的历史记录，并生成一个 SQLite 数据库表，该表表示该文件随时间变化的不同版本。

该文件假定包含多个对象 - 例如，抓取电力停电地图的结果或一个包含许多记录的 CSV 文件。

假设您有一个名为 incidents.json 的文件，它是一个 JSON 对象数组，并且该文件有多个版本记录在一个仓库中。该文件的每个版本可能看起来像这样

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

切换到相关的 GitHub 仓库目录中，然后运行以下命令

git-history file incidents.db incidents.json

这将在 incidents.db 文件中创建一个新的 SQLite 数据库，包含三个表

• commits 包含每个提交的一行，带有 hash 列、commit_at 日期以及指向 namespace 的外键。
• item 包含 filename.json 文件每个版本中每个条目的一行 - 带有额外的 _commit 列，该列是指向 commit 表的外键。
• namespaces 包含单行。这允许您为不同的文件构建多个表，使用下面描述的 --namespace 选项。

此示例的数据库模式将如下所示

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])
);

如果您的 incidents.json 文件有 10 个历史版本，每个版本包含 30 个事件，那么您的 item 表最终将有 10 * 30 = 300 行。

如果您的对象具有唯一标识符 - 或多个列一起构成唯一标识符 - 您可以使用 --id 选项来去重并跟踪这些条目随时间的变化。

这提供了一种更有趣的方式来应用此工具。

如果有一个名为 IncidentID 的唯一标识符列，您可以运行以下命令

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

此处使用的数据库模式与不带 --id 选项时使用的数据库模式有很大不同。

如果您已经导入了历史记录，该命令将跳过已处理过的提交，只处理新的提交。这意味着即使初始导入可能很慢，后续导入的速度也会快得多。

此命令将创建六个表 - commits、item、item_version、columns、item_changed 和 namespaces。

以下是完整的模式

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 表将包含按 ID 去重后的每行最新版本，以及以下附加列

• _id - 数字整型主键，用作 item_version 表的外键。
• _item_id - 使用命令的 --id 选项指定的列值的哈希。处理新版本时用于去重。
• _commit - 指向 commit 表的外键，表示修改此条目的最新提交。

item_version 表将包含该条目每个捕获到的不同版本的一行，以及以下列

• _id - 条目版本记录的数字 ID。
• _item - 指向 item 表的外键。
• _version - 数字版本号，从 1 开始，每个捕获到的版本递增。
• _commit - 指向 commit 表的外键。
• _item_full_hash - 此版本条目的哈希。工具内部使用此哈希来识别在提交之间发生变化的条目。

此表中的其他列表示原始数据中自上一版本以来已更改的列。如果值未更改，则将显示为 null。

如果某个值先前已设置但又改回为 null，在 item_version 行中仍会显示为 null。您可以使用下面描述的 item_changed 多对多表来识别这些情况。

您可以使用 --full-versions 选项存储条目每个版本的完整副本，而不是仅存储已更改的列。

此 SQL 视图将 item_version 与 commits 连接，添加三个额外的列：_commit_at 表示提交日期，_commit_hash 表示 Git 提交哈希。

此多对多表精确指示了 item_version 中哪些列已更改。

• item_version 是指向 item_version 表中行的外键。
• column 是指向 columns 表中行的外键。

此表将拥有最多的行数，这就是为什么它只存储两个整数以节省空间的原因。

columns 表存储列名。它被 item_changed 引用。

• id - 整型 ID。
• name - 列名。
• namespace - 指向 namespaces 的外键，用于当多个文件历史记录共享同一个数据库时。

请注意，_id、_item_full_hash、_item、_item_id、_version、_commit、_item_id、_commit_at、_commit_hash、_changed_columns、rowid 被视为此工具的保留列名。

如果您的数据包含这些名称中的任何一个，它们将被重命名以添加一个后缀下划线，例如 _id_、_item_、_version_，以避免与保留列冲突。

如果您的列名如 _commit_，它也会被重命名，添加额外的后缀下划线，因此 _commit_ 变成 _commit__，_commit__ 变成 _commit___。

• --repo DIRECTORY - Git 仓库的路径，如果它不是当前工作目录的话。
• --branch TEXT - 要分析的 Git 分支 - 默认为 main。
• --id TEXT - 如上所述：传入一个或多个唯一标识记录的列，以便可以计算该记录随时间的变化。
• --full-versions - 在 item_version 表中记录每个版本的完整副本，而不是只记录已更改的列。
• --ignore TEXT - 一个或多个要忽略的列 - 它们将不会包含在生成的数据库中。
• --csv - 将数据视为 CSV 或 TSV 而不是 JSON，并尝试猜测正确的方言
• --dialect - 使用特定的 CSV 方言。选项包括 excel、excel-tab 和 unix - 详见 Python CSV 文档。
• --skip TEXT - 一个或多个应跳过的完整 Git 提交哈希。如果您的修订历史中某些数据已损坏，导致此工具无法工作，您可以使用此选项。
• --start-at TEXT - 跳过指定提交哈希之前的提交。
• --start-after TEXT - 跳过包括指定提交哈希在内的所有提交，然后从后续提交开始处理。
• --convert TEXT - 用于转换的自定义 Python 代码，如下所述。
• --import TEXT - 用于 --convert 的附加 Python 模块导入。
• --ignore-duplicate-ids - 如果文件的一个版本中包含相同的 ID 多次，工具将报错退出。使用此选项可以忽略此错误，并仅选择重复项中的第一个。
• --namespace TEXT - 如果您希望在同一个数据库中包含多个不同文件的历史记录，请使用此选项。默认值为 item，但您可以将其设置为其他名称，这将生成类似 yournamespace 和 yournamespace_version 的表名。
• --wal - 在创建的数据库文件上启用 WAL 模式。如果您计划在 git-history 创建数据库的同时对其运行查询，请使用此选项。
• --silent - 不显示进度条。

如果您的仓库中的数据是 CSV 或 TSV 文件，您可以通过添加 --csv 选项来处理它。这将尝试检测文件中使用的分隔符，因此此选项对逗号分隔值和制表符分隔值都有效。

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

您还可以使用 --dialect 选项指定 CSV 方言。

如果您的数据不是 CSV/TSV 或简单的 JSON 数组，您可以使用 --convert 选项对其进行重塑。

此工具需要的数据格式是一个字典数组，如上面的 incidents.json 示例所示。

如果您的数据不符合此形状，您可以提供一段 Python 代码，将每个存储文件的磁盘内容转换为 Python 字典列表。

例如，如果您的存储文件看起来像这样

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

您可以使用以下 Python 片段将它们转换为所需格式

json.loads(content)["incidents"]

（默认情况下，json 模块已暴露给您的自定义函数。）

然后您就可以这样运行该工具

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

content 变量始终是一个 bytes 对象，表示文件在仓库历史中特定时刻的内容。

您可以使用 --import 导入附加模块。此示例展示了如何读取使用 ; 作为分隔符的 CSV 文件

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

您可以使用 --import xml.etree.ElementTree 导入嵌套模块，例如 ElementTree，然后在函数体中将其称为 xml.etree.ElementTree。例如，如果您的跟踪数据位于一个名为 items.xml 的文件中，内容如下所示

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

您可以使用以下 --convert 脚本加载它

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

如果您的 Python 代码超过一行，则需要包含一个 return 语句。

您也可以在 --convert 代码中使用 Python 生成器，例如

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

此转换函数期望的数据看起来像这样

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

要对此工具做出贡献，首先签出代码。然后创建一个新的虚拟环境

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

或者如果您使用 pipenv

pipenv shell

现在安装依赖项和测试依赖项

pip install -e '.[test]'

运行测试

pytest

更新此 README 文件中的模式示例

cog -r README.md