Support for creating a view based on an existing table or view

[kgodey]

We would like users to use the Views feature if they would like to persist a specific view of their data, such as filters, sorts, groups, column order, etc. The Mathesar UI should prompt users to create a new database view from an existing view or table as needed in these cases.

Creating a view could take the form of:

• Directly creating a view (skipping the Data Explorer)
• Opening the Data Explorer with pre-populated data.

We should choose one of these options based on the use-case.

Scenarios

1. The user should be able save existing filters or sorts applied to a table as a view.
   1. Error scenario: view creation fails.
2. The user can hide columns in a table. At this point, they should be prompted to create a view of the table without the hidden columns if they want to save the way it looks.
   1. Error scenario: view creation fails.
3. The user can reorder columns in a table. At this point, they should be prompted to create a view of the table where the columns are reordered if they want to save the way it looks.
   1. Error scenario: view creation fails.
4. The user should be able to reorder columns in a view.
   1. This will only reflect in the frontend and not be saved automatically. We need to show the user that their view has not been saved and prompt them to either manually save the view or create a new view.
   2. Error scenario: saving the view fails
   3. Error scenario: creating a new view fails
5. The user should be able to hide columns in a view.
   1. This will only reflect in the frontend and not be saved automatically. We need to show the user that their view has not been saved and prompt them to either manually save the view or create a new view.
   2. Error scenario: saving the view fails
   3. Error scenario: creating a new view fails
   4. Error scenario: the column that is hidden is a dependency for another column in the view.
6. The user should be able to change filters or sorts in a view.
   1. This will only reflect in the frontend and not be saved automatically. We need to show the user that their view has not been saved and prompt them to either manually save the view or create a new view.
   2. Error scenario: saving the view fails
   3. Error scenario: creating a new view fails
7. The user should be able to save groups in a table or a view as a new "summarized" view.
   1. Error scenario: creating a new view fails

Design problems to solve

We have been treating Filters, Sorts, and Groups as the same type of operation, but they are not.

• Filters and sorts can be saved to the database (as a query),
• Groups are a UI-only thing and don't persist at the database level. However, groups can be saved as their own "summary" view.

We need to convey this to the user somehow.

Additional context

• Blocked by design work:
  • Support for opening and interacting with a View #466
  • Design for visual query builder ("Data Explorer") #1065
  • Support for creating a database view from scratch #781

[kgodey]

@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]

• Error scenario: saving the view fails
• Error scenario: creating a new view fails

There is no way to save most relevant changes to a pre-existing view at the DB layer. From the docs:

ALTER VIEW changes various auxiliary properties of a view. (If you want to modify the view's defining query, use CREATE OR REPLACE VIEW.)

What this means is that you can't drop or add columns in a view without creating a new view, for example, or change the row order, or filter. You can rename the view, and alter some of its other metadata.

For most changes to views that I've seen discussed, the only possibility (I can think of; open to other ideas) is to create a new view, silently delete the old one, and attach the service layer model to the new view (thus preserving any service-layer options). I think we'll need to really figure out how to portray this to the user, since some of the failure states will be very confusing if their mental model is "save changes". For example, we may fail to delete the old version of the view because it has dependent objects (or some other reason, but I suspect that would be a common one). Maybe a better way to portray this would be "save view version". So, we'd just keep each view version (we could even rename them automatically in some reasonable fashion). Maybe there could be a service layer marker for which version is "current", and we could have a way to restrict to only showing those. We could also provide help for deleting old versions of views (though for non-materialized views, the space savings are minimal, and we'd want to make sure that users understand they're not really freeing up space by deleting views).

Finally, we'd probably want to make sure that we've clearly categorized which changes can be saved to the same view, and which changes would create a new view (or view version). This might be a source of confusion for users.

[silentninja]

There are two types of views, Temporary views and Materialized views, and we should split the uses cases(when we suggest views) based on those.

Temporary views should be suggested for most (disposable) use cases and materialized views very rarely as this helps in keeping views lightweight making them really flexible and helpful in a lot of use cases.

When it comes to updating, views should be treated as immutable(replaced rather than repaired) due to the reason @mathemancer mentioned. In terms of restful api, updating view should be PUT rather PATCH. This would also mean granting permission again.
A migration window could probably be helpful, we could automatically migrate compatible dependencies that Mathesar already deals with(like Table, column), ask the user to fix the dependencies we cannot resolve(will move this to relevant dependencies issue).

[pavish]

On points 2 through 5 (both inclusive):

The user may try to hide columns in a table. At this point, they should be prompted to create a view of the table without the columns they want to hide.

The user may try to reorder columns in a table. At this point, they should be prompted to create a view of the table where the columns are reordered.

This will only reflect in the frontend and not be saved. We need to show the user that their view has not been saved and prompt them to save the view or create a new view.

I don't think we should prompt the user to create a new view, for these usecases. I do think we should allow creating a view after applying these, however the user should be able to do them on the table/view itself.

These operations do not strike sense to me as global settings/at the db level. They would be better suited as user specific settings, which only apply to mathesar clients/api users. We do not have to do it on the backend for the initial release, but we can have it persisted on the browser local storage.

[silentninja]

Let's split the operations on a table based on what the user is planning to do:

1. Looking to add data-
   1. Adding a column
   2. Modifying a row
2. Might be looking to add data or reading it(grey area) -
   1. Filtering a column -
      1. Modifying scenario - Maybe the user is trying to add data to a filtered row(convenience)
      2. Viewing scenario - Maybe the user is trying to read the data or use it for some other purpose other than writing on it
   2. Reordering a column
3. Reading data
   1. Grouping data
   2. Hiding a column

Usually, the formats good for writes won't be good for reads and vice versa

When using any database based app we don't get the whole table like a spreadsheet and apply modification on top of it, we use Select statements and get the representation the user wants.

So I feel it would be better if we don't touch the table when it comes to reading data(groups and hiding column). Let the table be the actual representation of the truth, let the view do the talking when it comes to data representation the user wants. We are hiding the actual truth when we end up doing a reading operation on a table, we should rather be using the tool that fits the job.

To make it convenient for the user, we could let the users do a reading data modification on the table itself, but we should prompt them to create a view to bring in certain advantages like persistence, versioning, sharing, permission

[seancolsen]

Regarding the limitations which prevent the user from saving the changes they make to the view:

• Are all these limitations just for short-term task prioritization? If so, that's fine with me. If some of the limitations are part of the intentional product design, then I'd like to discuss further.
• Sort/filter/group seems useful without saving, if only marginally.
• Reordering columns and hiding columns does not seem to offer much value without the ability to save, so I'd suggest cutting those features until we're able to save them.

[kgodey]

Responding to various comments:

---

@mathemancer:

For most changes to views that I've seen discussed, the only possibility (I can think of; open to other ideas) is to create a new view, silently delete the old one, and attach the service layer model to the new view (thus preserving any service-layer options)

I think this is exactly what we should do. In addition, we should transfer dependent objects from the old view to the new one, and if we cannot do that automatically, then we don't allow the view to be updated.

I think we'll need to really figure out how to portray this to the user, since some of the failure states will be very confusing if their mental model is "save changes". For example, we may fail to delete the old version of the view because it has dependent objects (or some other reason, but I suspect that would be a common one).

If we do everything in a single transaction, then there won't be a failure state where the new view has been created but the old view is still floating around. If any operation fails, the whole things fails. Am I missing something here?

---

@silentninja:

There are two types of views, Temporary views and Materialized views, and we should split the uses cases(when we suggest views) based on those.

This implementation will be for temporary views. We are not dealing with materialized views yet.

To make it convenient for the user, we could let the users do a reading data modification on the table itself, but we should prompt them to create a view to bring in certain advantages like persistence, versioning, sharing, permission

This is exactly what we'll be doing.

---

@pavish:

I don't think we should prompt the user to create a new view, for these usecases. I do think we should allow creating a view after applying these, however the user should be able to do them on the table/view itself.

Agreed, I've updated the issue above to say the user can do that and they need to create a view to save them.

---

@seancolsen:

Regarding the limitations which prevent the user from saving the changes they make to the view:

I'm not sure what limitations you're referring to. Users should be able to save changes to views.

[kgodey]

Marking as ready and moving to the backlog. Please feel free to add comments if any of you have additional concerns.

[mathemancer]

If we do everything in a single transaction, then there won't be a failure state where the new view has been created but the old view is still floating around. If any operation fails, the whole things fails. Am I missing something here?

"Failure state" was the wrong word on my part, sorry. I'm talking about the feedback to the user; If they have no clue what's going on under the hood, it's likely we'll fail sometimes for reasons that make no sense to them: If they get an error message that says "could not update my_view. Details: my_view could not be dropped because it has dependent objects", they may be confused, and worry that Mathesar is trying to delete their view. Further, if we help them understand what's actually happening under the hood, they may be able to solve some problems manually that we're not able to solve automatically.

My point is not that we should completely discard the concept of a mutable view, but that if we want that we need to be very conscientious about how we portray the process to the user, and thoughtful about error messages and instructions for how to fix the issue. This is especially tricky since a few metadata associated with views can be updated: name, schema, comment, etc. If we make it seem to the user that adding a column is essentially the same as changing the name, even though the underlying processes are completely different, it seems bound to lead to confusion whenever something unexpected happens.

[seancolsen]

@kgodey

I'm not sure what limitations you're referring to. Users should be able to save changes to views.

There are three occurrences of the text "not be saved" in the ticket description. That's what I'm referring to. I'd expect that if I adjust the filter in a view, I'd have a way to save my new filter settings within the view I already created. It sounds like I won't be able to save those filter adjustments (and other adjustments), and I'd call that a limitation. Are those limitations short-term or long-term?

[kgodey]

There are three occurrences of the text "not be saved" in the ticket description. That's what I'm referring to. I'd expect that if I adjust the filter in a view, I'd have a way to save my new filter settings within the view I already created. It sounds like I won't be able to save those filter adjustments (and other adjustments), and I'd call that a limitation. Are those limitations short-term or long-term?

I think I wasn't clear enough in my description, I meant that the changes would not be saved automatically. The user will need to manually save the changes, but they should be able to save them. e.g. if I drag and drop columns, the frontend should not send a request to the backend to update the query that powers the View. Instead, the columns should be reordered in the frontend and there should be some indication in the frontend that the current view contains unsaved changes.

e.g. if I modify the search in a GitHub Project, it shows me this:

---

I updated the description above to hopefully be more clear.

[seancolsen]

Thanks @kgodey that explains it

[kgodey]

Although we are doing work related to this in Cycle 3, this ticket no longer covers accurate design requirements, so I'm going to close it.