Edit UI (DML)

[cldellow]

This issue lays out the vision for the editing UI that datasette-ui-extras will provide. See #54 for the DDL version.

Editing is only for SQLite databases, not DuckDB.

Editing will use the write API introduced in Datasette 1.0, so you'll need to be on Datasette 1.0.

My goal is to bring an automatic, pluggable, user-friendly UI that enables these use cases: traditional data entry, turker mode, and forms.

Non-goals: supporting JavaScript-disabled browsers.

UI Attributes

Automatic

Don't make users define redundant mappings. Lean into the structure that SQL provides us.

Use SQL foreign keys and CHECK constraints to define what is permissible and drive UI control selection.

Because SQLite is untyped, we'll have to sniff rows and/or use heuristics in some cases.

Things we'll aim to support:

• you can't edit primary keys
• foreign keys
• CHECK constraints of the form x IN (...)
• ISO dates
• ISO timestamps
• DEFAULT values (eg, when inserting a row, we'll try to prepopulate with what the DEFAULT would be, to hint that you don't need to fill it in)
• JSON string arrays

You can declare a SQL VIEW to further control the user experience. Imagine that you have:

CREATE TABLE reviews(
  id integer primary key,
  url text not null,
  review text not null,
  rating text (check rating in ('negative', 'positive')),
  rated_at text,
  rated_by text
)

You'd like to have some contractors fill out the rating field. They shouldn't have access to the url field. They should only have access to rows that still need a rating. When they rate something, you'd like to automatically track who rated it and when. Oh, and you'd like to give some instructions.

You can do this by creating a view, and giving them access to that:

CREATE VIEW needs_rating AS
/* set rated_by=current_actor() */
/* set rated_at=datetime() */
SELECT
  id,
  'What is the sentiment of the review? If unsure, choose positive.' AS instructions,
  review AS review_readonly,
  rating
FROM reviews 
WHERE rating IS NULL

Only two unaliased columns from the base table are present, and thus candidates to be editable. id, however, is part of the primary key, and so only rating is editable.

When the user submits their entry, the set statements are automatically executed, tracking who edited the row, and when.

Pluggable

We'll try to render sensible controls. Sometimes we might get it wrong -- perhaps we'll render an input field that expects a number, when it really ought to have been a checkbox that stored 1 for checked and 0 for unchecked.

You can override us by implementing a plugin hook:

@hookimpl
def edit_control(datasette, database, table, column):
  if column == 'name':
    return 'ShoutyControl'

ShoutyControl must be a JavaScript class that is available to the page. This can be a pre-defined one provided by datasette-ui-extras or one you author via a file loaded by extra_js_urls or inlined by extra_body_script

The class should conform to this interface:

class ShoutyControl {
  constructor(db, table, column, initialValue) {
    this.initialValue = initialValue;
    this.el = null;
  }

  // Return a DOM element that will be shown to the user to edit this column's value
  createControl() {
    this.el = document.createElement('input');
    this.el.value = this.initialValue;
    return this.el;
  }

  get value() {
    // Be shouty.
    return this.el.value.toUpperCase();
  }
}

TODO: consider if the interface should have an isValid function, and a way to signal that its value has changed (for example, to permit "autocommit on blur" modes)

TODO: document how you might reference other columns. eg, say you have text The quick brown fox jumped over the lazy red dog. in column A, and you want the user to annotate it and have those annotations show up in column B as [{"substring": "jumped", "label": "verb"}]

User friendly

We'll try to show a good control. For small, closed sets, we'll use a drop-down. For larger sets or open sets, an autocomplete combobox.

For the turker use case, we might render a space-inefficient control that has key-bindings that permit you to quickly advance through a dataset, eg from https://prodi.gy:

Use cases

The use cases don't assume any particular access scheme. Some scenarios may be only authenticated users, some may permit anonymous users (eg forms). See the Authentication, authorization and auditing section for more.

These are imagined as alternative layouts for the row view, eg the pages located at /db/table/1, /db/table/2 and so on.

Traditional (add new + edit existing)

It's the typical vertical layout of column name, UI-control-to-specify value.

You might be in auto commit mode, or you might have to click an explicit Save button.

Clicking Save keeps you on the current row page.

Example: curation of detailed data, ability to deep-link in workflows.

Forms (add new)

You can submit new rows, but not read, edit or delete existing rows.

After submitting you are redirected to a customizable URL. By default, you are sent to the new form submission page.

Example: collecting feedback from the general public.

Turker (edit existing)

You can edit a subset of fields on a subset of existing rows, but can not read or delete other rows.

After submitting an edit, you are advanced to the next row that needs editing.

You might want affordances to enable very fast editing -- for example, focus the first control, permit keyboard shortcuts to auto-select an answer and move on.

Example: contractors who are doing piecemeal data entry, trusted internal staff doing manual annotations.

Authentication, authorization, auditing

This is all delegated to other systems.

Authentication is handled by Datasette's actor system.

Authorization is handled by Datasette's permission system.

There's no built-in support for auditing. If you'd like to track which users created/updated rows, use the datasette-current-actor plugin and create suitable DEFAULT values or trigger functions.

Open questions

Implementation notes

• You can configure which tables are editable by default in metadata.json, or activate edit mode with ?_dux_edit=1

• We'll need a hook to create/update INSTEAD OF triggers for editable views.

Rejected ideas

Spreadsheet mode

The table view would let you update values in-place, without navigating to the row page for each entry.

Rejected because I think I can't do a sufficiently good job on the UI: it likely won't be an actual spreadsheet view with a seamless grid and resizeable columns. More likely, it would have inline controls that automatically commit changes via ajax.

[cldellow]

This is largely complete. What remains:

• support for views (tracked in Decide strategies for views #82)
• documenting the turker / data entry / forms use cases (Document data entry / turker / forms use cases #83)