explore: can the row page support views?

[cldellow]

As part of #48, it might be valuable to let users define a subset of a table as a view. For example, if you have:

CREATE TABLE data(
  id integer primary key,
  value text,
  approved integer,
  approved_at text,
  approved_by text
)

You might want to create a queue of records-to-be-reviewed, like:

CREATE VIEW needs_review AS
SELECT
  id,
  'Look closely at the value. Do you approve it?' AS instructions,
  value AS value_aliased_so_as_not_to_be_editable,
  approved
FROM data
WHERE approved IS NULL

That view declares that you should be able to edit the approved field. id isn't editable, as it's part of the pkey. The other two columns are aliased, and so aren't editable.

Then, you'd use SQLite's INSTEAD OF trigger to permit updates against the view. You'd probably want some jazz to create these triggers automatically, so users can get by with basic SQL understanding.

Questions:

• [x] Could we inject outside context into the update trigger? eg say we wanted to set approved_at and approved_by in a somewhat natural way
• [x] Can we coerce Datasette into supporting the row page for views, eg https://dux.fly.dev/cooking/posts/1 works but https://dux.fly.dev/cooking/questions/1 gives Row not found: ['1']. It looks like these two things need to be fixed:
  • [x] It tries to search for rowid = 1, because it falls back to rowid, see row_sql_params_pks, we'd need to teach db.primary_keys(table) to be smarter, which is really detect_primary_keys. I think that's doable.
  • [x] It fails to find foreign keys in views/row#foreign_key_tables. We'd want to teach this to be a bit smarter - every view should have an entry, views that are simple selects from a single table should have the columns in common.
• [x] Can the JSON API support views? The code seems to check that it's a table
  • [x] RowUpdateView#update_row delegates to sqlite-utils, which fails with 'View' object has no attribute 'update'. Compare View vs Table#update
  • [x] ...I tried to patch this to re-use the update from Table... I get an AssertionError with an empty message when the exit functon of the sqlite3.Connection context manager runs. What's going on. Ah, it's the assert rowcount == 1, which I guess doesn't apply for INSTEAD OF triggers

[cldellow]

A horrendous idea: we could add a current_actor() function (see #50 ). When invoked, it runs Python code that uses an asgi local to output the current actor. This gets us the value needed by the approved_by field.

approved_at is just datetime().

How ought the schema signal that it wants those fields to be populated? A proposal:

CREATE VIEW needs_review AS
/* set approved_by=current_actor() */
/* set approved_at=datetime() */
SELECT
  id,
  'Look closely at the value. Do you approve it?' AS instructions,
  value AS value_aliased_so_as_not_to_be_editable,
  approved
FROM data
WHERE approved IS NULL

I think that's actually... not terrible? You could let them express other transformations in there, for example they could have access to old and do arbitrary SQL queries. We'd introspect the view and create the necessary trigger. May god have mercy on our souls.

[cldellow]

Validating that triggers work the way I expect:

CREATE TABLE data(id integer primary key, name text, age integer);
INSERT INTO data(name,age) VALUES ('colin', 37);
INSERT INTO data(name,age) VALUES ('jenn', 38);
CREATE VIEW view AS SELECT * FROM data;
CREATE TRIGGER update_underlying_fields INSTEAD OF UPDATE OF age, name ON view
BEGIN
   UPDATE data SET age = new.age, name = new.name WHERE id = old.id;
END;
UPDATE view SET age = 1, name = 'charlie' WHERE id = 1;
UPDATE view SET age = 10 WHERE id = 2;
SELECT * FROM view;

Yeah, that gives the expected result:

1|charlie|1
2|jenn|10

[cldellow]

I'm actually getting pretty jazzed about views being the initial foothold for editing data. They solve two common issues:

• prioritizing -- eg, I want to first handle the rows that are missing data for column X, and I want them processed in order by how important column Y is -- this is a WHERE clause and an ORDER BY clause
• "blinders" -- I want to collect one big table of data, but when I'm grinding out data entry, I only want to see the relevant fields -- this is a list of column selection clauses