Support for editing records within a view

[kgodey]

Mathesar should allow users to edit data in the underlying tables from within a view.

Scenarios

Identifying editable columns

• Users should be able to tell which columns are editable and which are not editable.

Editing directly represented data

• When the user tries to edit data in a cell that is directly editable, we should allow them to edit the data using the appropriate input method for the cell's data type.
• We should indicate to the user that editing the cell is actually editing the underlying table's data and will be updated everywhere.
• There should be a way for the user to edit the underlying record, not just the specific cell.
  • Please note that the record might have foreign key fields, so we will need a way to integrate foreign key autocompletion when they are editing that record.

Error scenarios:

• Editing the record fails.
• The values entered for the record are invalid in some way.
• Foreign key autocomplete lookup fails.

Other scenarios:

• The edits that the user makes might cause the record to disappear from the view. For example, imagine a view of movies that are filtered to movies released between 2000 and 2010. If the user edits a release date and sets it to 2021, then it will be removed from the view.

Editing list formula data

There is special editable behavior for cells generated using some list formulas. Please see the wiki for details.

• We need to convey to users that they are actually editing/adding underlying records here.
• We also need to convey to users whether a given list formula column is editable, and if not, why it is not editable. (i.e.. the formula generating it might not be compatible with editing it).
  • For example, a list generated by a filter that says release_year > 2010 cannot have a new item added to it because we don't have enough information to set the release_year when a new item is added.
  • On the other hand, a list generated by a filter that says release_year = 2010 can have a new item added to it because we can automatically set the release_year to 2010 based on the information in the filter.

Error scenarios:

• Saving data fails due to some error (either invalid input or some other backend error)
• Retrieving related records for autocomplete fails.

Additional Context

• Blocked by Support for opening and interacting with a View #466
• Previous design and discussion: Editing Records within a View #653

Implementation

• The backend will provide details of which columns are editable.
  • Columns that are direct references of data from an underlying table are editable.
  • Columns generated via list formulas are editable.

[kgodey]

@dmos62 @ghislaineguerin @pavish @mathemancer @seancolsen @silentninja This issue has been updated and is ready for review. Please look through it and unassign yourself after you've added any feedback that you might have.

[mathemancer]

I thought we were going to start with only supporting views which can be automatically inserted to / updated in the DB. In that case, we need to restrict the scope to only views which are very simple selects from a single table. If we want to do more than that, the implementation will get quite intense, and I suggest we break this into multiple different stories based on different scenarios. See "updatable views" in this page from the docs: https://www.postgresql.org/docs/13/sql-createview.html

I really like the idea (you alluded to above) of having a record-editing form that makes it clear to the user that they need to edit an underlying record. As I think of that, it makes sense that even implementation-wise, we could simply avoid directly updating rows in or inserting rows into a view, and just have a flow that lets the user edit all source tables which feed a row. This would simplify the implementation (at least at the lower levels) and also give us more functionality earlier on (i.e., we'd be able to handle much more complicated views with this method right out of the gate). The flow could be something like:

• user clicks cell they want to insert data into
• a number of forms pop up (probably one at a time) that let them add, update, or select rows from underlying tables as appropriate, and they use those to compose a new row in their view.

This would be one way to handle the "watched movies" view scenario above. If they always edit data in a view this way, it'll be slightly less convenient for very simple views, but it'll have the advantage that it'll work for many types of views in the same way. Very simple views are likely to quickly run out of usefulness even for pretty easy use cases.

[ghislaineguerin]

@kgodey Do we need to consider editing a record from a view that references another view rather than a table and how that would look like?

[kgodey]

I really like the idea (you alluded to above) of having a record-editing form that makes it clear to the user that they need to edit an underlying record. As I think of that, it makes sense that even implementation-wise, we could simply avoid directly updating rows in or inserting rows into a view, and just have a flow that lets the user edit all source tables which feed a row.

@mathemancer Yes, this was my idea. I think we should not worry about supporting inserting or updating into Views directly and instead use this approach.

@kgodey Do we need to consider editing a record from a view that references another view rather than a table and how that would look like?

@ghislaineguerin I think we need to treat columns whose data source is other views as read only. The user can update the data in the original view directly if they would like.

[kgodey]

Views will be read only.