Default to only storing columns that have changed in item_version

[simonw]

(Original title: Option to store only columns that have changed in item_versions)

When browsing a list of item versions like this one it's difficult to tell at a glance which rows have changed since the previous version:

It would be neat if there was a mode that could only store values in the versions table for columns that have changed since the last version.

[simonw]

This could also help shrink the size of the overall database (see also #12).

One catch: does null mean "this value did not change" or does it mean "this value was set to null"?

I'm going to see if I can address that using a bitmap - an additional column, probably called _changed (though I'm worried that might be confused for a timestamp) which stores an integer which represents which columns changed since the previous item.

A custom Datasette plugin could read that bitmap and use it to show extra information when the table is displayed.

[simonw]

I started my prototype by writing a _changed column that contained a JSON list of columns, rather than a bitmap: ["AcresBurned", "PercentContained", "ConditionStatement", "Updated", "PersonnelInvolved", "CrewsInvolved", "Helicopters", "Engines", "Dozers"]

I really like this, but it's very verbose and consumes a lot of table space.

So... how about turning that into a lookup table? So the JSON value becomes a key in another table?

This would save on space, and display reasonably well. Running a query for "all versions that had an updated Dozers column" would be possible but would involve a join against that table plus some JSON shenanigans, which wouldn't be particularly simple for users.

Probably simpler than dealing with a bitmap column though!

[simonw]

Alternative formats for the _changed column:

• JSON array of column string names - verbose but easy to query
• Integer that foreign keys to a string that's a JSON array, using table.lookup() in sqlite-utils
• JSON array of column offset integers
• Single integer bitmap of column offset integers
• ,3,5,7, style string, which allows for where _changes like '%,3,% queries - could even add a trigram index?
• JSON array of integers that refer to column names stored in a lookup table
• a full many-to-many relationship in a separate versions_column_changed table

Maybe the many-to-many relationship would be the best way to handle this? I'm slightly put off because Datasette doesn't have facet by many-to-many yet - I mostly implemented that a few years ago but never finished it, see simonw/datasette#551

[simonw]

After sweating over this decision for a full week, I'm going to try the m2m table and see how well it works.

I may try turning it back into a JSON list for one of the views.

[simonw]

That commit is my initial prototype of a _changed column which stores a full JSON list of column names.

I'm going to try using m2m instead next.

[simonw]

Since item_versions currently uses a compound primary key I'm ending up with a m2m table that relates against a compound foreign key, which doesn't work great with Datasette yet. Code looks something like this:

                    db["item_versions"].insert(
                        item_version,
                        pk=("_item", "_version"),
                        alter=True,
                        replace=True,
                        column_order=("_item", "_version", "_commit"),
                        foreign_keys=(
                            ("_item", "items", "_id"),
                            ("_commit", "commits", "id"),
                        ),
                    )

                    if changed_columns:
                        # Record which columns changed in the changed m2m table
                        for column in changed_columns:
                            db["changed"].insert({
                                "_item": item_id,
                                "_version": version,
                                "column": db["columns"].lookup({"name": column})
                            }, pk=("_item", "_version", "column"), foreign_keys=(
                                ("column", "columns", "id"),
                                ("_item_id", "items", "_id"),
                            ))

I think I'll switch item_versions to having a regular integer primary key instead to make the m2m table more predictable.

[simonw]

The changed table looks like this:

CREATE TABLE [changed] (
   [item_version] INTEGER REFERENCES [item_versions]([id]),
   [column] INTEGER REFERENCES [columns]([id]),
   PRIMARY KEY ([item_version], [column])
);

This makes it difficult to facet by item in the Datasette interface because you have to first go through item_version - I can fix that by adding a changed_details view or similar that makes that join for you.

[simonw]

% sqlite-utils create-view ca-fires-m2m-2.db changed_details '
select
  item_versions.id as _item_version_pk,
  items._id as _item_pk,
  item_versions._version,
  columns.name
from
  changed
  join item_versions on changed.item_version = item_versions.id
  join columns on changed.column = columns.id
  join items on item_versions._item = items._id'

[simonw]

That prototype worked, but highlighted that without foreign key links on views this interface isn't as useful as I'd like it to be:

[simonw]

I'm tempted to address that problem using a Datasette render_cell() plugin, which looks out for a table (actually a view) with that name and columns of that name and turns them into hyperlinks to the right place.

Maybe a datasette-git-history-viewer plugin?

Or... datasette-git-history, which depends on git-history and also adds a datasette git-history CLI command using the new register_commands hook.

[simonw]

Building a Datasette plugin that imports an existing Click CLI tool and re-registers it is proving hard - Click doesn't really want you to do that. I tried this:

from datasette import hookimpl
from git_history.cli import file as git_history_file


@hookimpl
def register_commands(cli):
    cli.command(name="git-history")(git_history_file.callback)

But when I run this:

 % datasette git-history --help     
Usage: datasette git-history [OPTIONS]

  Analyze the history of a specific file and write it to SQLite

Options:
  --help  Show this message and exit.

The options are all missing - which means that the command doesn't actually work. Will need to research this pattern separately.