--id option for de-duplicating objects over time

[simonw]

Spun off from #1.

[simonw]

More interesting is if you specify columns to be treated as IDs within that data, using the --id option one or more times.

If you do this, two columns will be created - item and item_history.

The item table will contain just the most recent version of each row.

The item_history table will contain a row for each captured differing version of that item, plus git_commit_at, git_hashandgit_version` columns (where version starts at 1 and increments for each captured version).

[simonw]

This means I need to be able to tell if the item has changed since being spotted in a previous version.

I'm going to reuse the _hash() function from sqlite-utils (used for hash_id there) for this:

def _hash(record):
    return hashlib.sha1(
        json.dumps(record, separators=(",", ":"), sort_keys=True, default=repr).encode(
            "utf8"
        )
    ).hexdigest()

[simonw]

Problem: sqlite-utils still doesn't support compound foreign keys:

• Support for compound (composite) foreign keys sqlite-utils#117

I want items in the versions table to relate back to the row in the items table - but that table was going to have a compound primary key.

There's a PR but it's a bit out-of-date.

One workaround could be to synthesize an item ID from the sha256 hash of the values in those ID columns.

[simonw]

I'm going to do that - but I'll actually abuse the _hash() function by passing in a dictionary of just the ID => value columns.

[simonw]

New schema design:

• commits: hash, commit_at
• item: id, columns
• item_versions: item, version, commit, columns

[simonw]

I was testing this against https://github.com/simonw/sfpublicworks-tree-removal-notifications/blob/main/tree-list.json like so:

git-history file db.db ../sfpublicworks-tree-removal-notifications/tree-list.json \
  --repo ../sfpublicworks-tree-removal-notifications \
  --id title --id district \
  --ignore img_src

And getting really confusing results. Turns out title, district is not unique across commits!

simonw/sfpublicworks-tree-removal-notifications@0413e61

Maybe try to detect if a single page has multiple items with the same ID in it?

[simonw]

Added this error:

(git-history) git-history % git-history file db.db ../sfpublicworks-tree-removal-notifications/tree-list.json --repo ../sfpublicworks-tree-removal-notifications --id title --id district --ignore img_src                   
Error: Commit: c98322bcf14655c606e6ab8c383ea5079fde5781 - found multiple items with the same ID:
[
    {
        "title": "183 Westwood Drive",
        "district": "District 7",
        "Site number": "1",
        "Reason(s) for removal": "Has arbutus fungal canker with 100% mortality rate, there is no cure or treatment and canker will spread and girdle cambium then kill tree as it forms cracks, prompt removal critical for preventing spread to other arbutus; replaceable with species appropriate for narrow sidewalk",
        "Posting period": "6/2/2020 - 7/17/2020"
    },
    {
        "title": "183 Westwood Drive",
        "district": "District 7",
        "Site number": "2",
        "Reason(s) for removal": "Has arbutus fungal canker with 100% mortality rate, there is no cure or treatment and canker will spread and girdle cambium then kill tree as it forms cracks, prompt removal critical for preventing spread to other arbutus; replaceable with species appropriate for narrow sidewalk",
        "Posting period": "6/2/2020 - 7/17/2020"
    }
]

[simonw]

OK this is working:

git-history file db.db /tmp/getIncidentsGit/incidents.json --repo /tmp/getIncidentsGit \
    --id CallTimeOpened --id Address --id CrossStreet \
    --convert 'json.loads(content)["ActiveIncidentDataTable"]' \
    --ignore-duplicate-ids

The duplicate IDs were for an incident that appeared to be filed twice, with slightly different categories.