Create, Edit and Delete Views

[ghislaineguerin]

The design for issue #454 is ready for review. Please follow the Review Guidelines and make sure to review before the set deadline expires.

Document

https://wiki.mathesar.org/en/design/specs/create-edit-delete-views

Reviewers

@kgodey @pavish Your review is required.
Feedback from the community is also welcome. More context about Mathesar's design strategy can be found here.

Deadline

The deadline for submitting feedback is set 2 days from now, on Tuesday 31st.

[kgodey]

@ghislaineguerin This design looks good overall. Some feedback:

• It would be good to show users when they have made changes to their view and it needs to be saved. e.g. if I create a view with filters A and B, and then further filter the view by C, it would be nice to know that filter C won't be saved unless I explicitly save the view.
  • It might also be useful to offer users the possibility of saving the view filtered by C as a new view (and keeping the original view only filtered by A and B)
• We need to figure out how we can allow users to get data from other views since they may not have primary or foreign keys. We'll need to flesh this out later once we have more backend implementation details.
• For the view query, I think we probably just want to show the SELECT statement minus the CREATE VIEW part. That way, they can run it and get the same results as the view without necessarily creating a new view. The text should say something like "This is the SQL statement used to get the results shown in this view."
  • We should also probably use some syntax highlighting for the SQL there.
  • I like the copy to clipboard option.
  • I also think the option to view the query should go at the bottom of the menu since it might not be as commonly accessed. It should be called something like "Database Query". I don't think we need to include the word "View" in the menu name since the menu is already called "View Options". Also the word "Open" makes me think it's going to open a file or something, so I'd leave that out too.
• Can you explain what the different icon colors in each column mean?
• Can you explain the fields underneath the table name when selecting a column mean? Is it the foreign and primary keys? Why are they relevant here?

[ghislaineguerin]

• It would be good to show users when they have made changes to their view, and it needs to be saved. e.g. if I create a view with filters A and B, and then further filter the view by C, it would be nice to know that filter C won't be saved unless I explicitly save the view.

I've added an additional flow for this scenario. It's included in the spec.

• It might also be useful to offer users the possibility of saving the view filtered by C as a new view (and keeping the original view only filtered by A and B)

Added option to save as for views. I also remove the 'view' part of the button label, as it was redundant.

• We need to figure out how we can allow users to get data from other views since they may not have primary or foreign keys. We'll need to flesh this out later once we have more backend implementation details.

Makes sense. I assume they can use other field's values to map the relationship.

• For the view query, I think we probably just want to show the SELECT statement minus the CREATE VIEW part. That way, they can run it and get the same results as the view without necessarily creating a new view. The text should say something like "This is the SQL statement used to get the results shown in this view."

Makes sense. I've removed that part.

• We should also probably use some syntax highlighting for the SQL there.

Will add.

• I like the copy to clipboard option.

:)

• I also think the option to view the query should go at the bottom of the menu since it might not be as commonly accessed. It should be called something like "Database Query". I don't think we need to include the word "View" in the menu name since the menu is already called "View Options". Also the word "Open" makes me think it's going to open a file or something, so I'd leave that out too.

Done.

• Can you explain what the different icon colors in each column mean?

I'm trying to visually represent the relations between tables so that if I select a 'purple+orange' table, I understand that now I can only get columns from those 'colors'. So basically, when referenced as a foreign key in another table, a primary key from a table would have the same color. Maybe color isn't the only way to convey this, but I think it might help users understand those table choices will limit column options.

• Can you explain the fields underneath the table name when selecting a column mean? Is it the foreign and primary keys? Why are they relevant here?

Yes, I'm trying to show how tables are related. I might consider a different way of showing the relationship. Having just the table names seems insufficient.

[kgodey]

@ghislaineguerin The changes look good. More comments below

I've added an additional flow for this scenario. It's included in the spec.

I assume this is the second prototype in the spec. When the second filter is applied, I don't see any indication to the user that the view is not yet saved and they need to save it manually.

Added option to save as for views. I also remove the 'view' part of the button label, as it was redundant.

Thanks, I see the "Save as New" button.

I'm trying to visually represent the relations between tables so that if I select a 'purple+orange' table, I understand that now I can only get columns from those 'colors'. So basically, when referenced as a foreign key in another table, a primary key from a table would have the same color. Maybe color isn't the only way to convey this, but I think it might help users understand those table choices will limit column options.

This would be good to describe in the text of the spec so that the implementer can implement this.

Yes, I'm trying to show how tables are related. I might consider a different way of showing the relationship. Having just the table names seems insufficient.

I think that's not clear, are there other ideas for showing the relationship that you had in mind?

[ghislaineguerin]

I assume this is the second prototype in the spec. When the second filter is applied, I don't see any indication to the user that the view is not yet saved and they need to save it manually.

I've added an unsaved indicator. I thought about being more specific and saying something like 'You have unsaved filters' but I prefer to have unsaved indicators that work globally so that we can apply them to other scenarios.

This would be good to describe in the text of the spec so that the implementer can implement this.

Yes, once the review is done I will describe this in detail.

I think that's not clear, are there other ideas for showing the relationship that you had in mind?

I need to reconsider this design as views or tables created outside of Mathesar might not have primary or foreign keys. We do plan on having "synthetic keys" at some point and this design might not adapt so well.

[kgodey]

I've added an unsaved indicator. I thought about being more specific and saying something like 'You have unsaved filters' but I prefer to have unsaved indicators that work globally so that we can apply them to other scenarios.

The indicator is subtle, it may be easy to miss. I like the idea of context-sensitive indicators better (e.g. "You have unsaved filters"). Couldn't we define a global place for indicators like this to go so that they do work globally but they're also helpful to the user?

I need to reconsider this design as views or tables created outside of Mathesar might not have primary or foreign keys. We do plan on having "synthetic keys" at some point and this design might not adapt so well.

I'm not sure what "synthetic keys" means.

I assume you'll post here when you have a new design, let me know if I can help in any way.

[ghislaineguerin]

@kgodey I'm adding a note to include context-sensitive indicators globally. I will address this in the next review.
Synthetic keys will be those, where fields with equal values can be used to relate records.

[mathemancer]

Overall, I think this looks quite good and makes some difficult concepts quite approachable.

I have a question about the concept of "editable": Is this to restrict further modification of view structure under certain conditions? If so, what would be the reason for that?

[kgodey]

@mathemancer "editable" in what context?

[pavish]

@ghislaineguerin This looks good to me. :)

Some comments:

• The 'select from' menu option when adding column/new view, should be a typeahead instead of a select menu.
  • I think it might be better to have both tables and views listed together instead of the tab within the dropdown. Tables and views share the same namespace, so there cannot be duplicate names.
  • Or we could have the tab above the select menu. Having a tab within a select dropdown will need a whole new component, which I would like to avoid.
• The top pane with the table actions need to defined for different screen sizes. There are a lot of options there, for which we need to determine how it would change when screen size changes.

[ghislaineguerin]

@pavish Thanks, I have applied the typeahead suggestion in the most recent version for this component. The tabs are also now outside of the selector. I will add responsive fixes as part of the upcoming UI revamp project.

[kgodey]

Based on the new action plan from the 2021-10-15 meeting, we're going to update the design requirements for issues in this milestone, including this one.