Decide strategies for views

[cldellow]

Views are editable in SQLite if you define triggers for them.

Should we automatically create such triggers for all views for which we can (ie any view which is based on a single table)? I think no.

Instead, admins should be able to pick which operations to support: insert, update, delete or some combination.

Should this be done entirely via comments in schema? This is appealing, as it means no special UI is needed to start (we could layer a UI on later)

CREATE VIEW editable AS
/* insert */
/* update */
/* delete */
SELECT id, name FROM base_table

That seems reasonable! Might we want other abilities? And can they be layered in later?

Other useful things:

• be able to add default values for inserts that refer to the new row (maybe not super useful? We can just use DEFAULT on the base table)
  • /* insert column = new.column + 1 */
• be able to set columns of the base table in updates
  • /* update column = new.column + 1 */
• be able to run arbitrary statements, eg to log to a table
  • /* after-insert: insert into audit_table(x, y, z) values (new.x, new.y, new.z); */
• have a macro that produces a json representation of the row
  • /* after-insert: insert into audit_table(before, after) values (old_as_json, new_as_json); */
• have a macro that produces a diff of the old and new row
  • /* after-insert: insert into audit_table(changes) values (diff_as_json); */
• be able to specify a where clause that limits the rows the user can insert/update/delete
  • eg can insert max 5 rows /* insert-where (select count(*) from table where actor = current_actor()) < 5 */
  • can update/delete only your own rows /* delete-where actor = current_actor() */

I suspect these can all be layered in after the fact.

[cldellow]

As I was exploring this: views seem really powerful, and we should do them.

But also: having to reify something as a view seems a bit onerous. Could we make something work with just SQL queries?

e.g. sometimes you want to write an ad hoc query that pulls in data from other tables, and then quickly take action based on that.

We need a view so that we have a hook to hang on -- editing is currently driven through the row page, and queries don't have row pages, only views and tables do.

But maaaaybe there is some wiggle room?

eg imagine we could make:

SELECT id, name, reviewed AS reviewed_editable FROM table

and get reviewed_editable as an inline edit control on the table screen?

That is, do some convention where we look for a rename of the base column to base column + _editable to light up the experience.

We can get the query from grovelling in the request, which would let us figure out the pkeys.

Ideally we'd be able to re-use the existing edit controls -- maybe they just need an onChange handler?

Anyway, this is a bit of a digression. Let's stick with views for now, since they also offer interesting permissions controls for collaboration capabilities. (Presumably a raw SQL query would just be subject to the permissions of the base table?)

[cldellow]

Views also have easy sorting and filtering, whereas queries don't (granted, you can "just" edit the query)

[cldellow]

...hm, if you do make a view, there's not currently a way to get to the row of the view, we'd have to add that in.

[cldellow]

I'm going to solve my immediate problem using a query that links to the underlying base table for now, and think on which direction to pursue for dux itself. I hadn't really considered inline editing in ad hoc queries, but it's starting to seem pretty attractive.