Viewing Views – Database Queries

[ppii775]

This is the third of a series of new discussions for #466 Support for viewing a database view. This discussion is related to the now-closed discussion #624, and the first two discussions of this series #911 and #916. Feel free to let me know if I missed anything or there's anything incorrect, since I'm new to the group and was not part of the last discussions. The Figma prototype that I'm using might contain outdated styles and components beyond the scope of this discussion.

The Problem

4. Advanced users should be able to view the query that the entire view is derived from. The query should be syntax highlighted and should be able to be copy/pasted easily.
   i. Error scenario 1: Backend call to get the view query fails for some reason.
   ii. Error scenario 2: User has permissions to view the view, but not the query.

The error scenarios could be similar to the examples mentioned in #911, but the question here for me is more about how to incorporate the query viewing feature in views and users' workflow.

It seems that we'd like non-technical users to primarily work with views visually (please correct me if I'm wrong). They could add/delete/edit individual columns directly from tables or perform some data aggregation based on tables, and achieve their desired view step by step. I saw some discussions on #624 regarding column reordering – would working on views step by step require saving a new view at DB level for every step? If so, is that a concern for us?

The reason that I'm asking is because working with queries might need to mirror the visual workflow somehow. It might not be intuitive for non-technical users (not sure how we define "advanced users" here) to paste/edit a long query and press "execute" or "play" at the end – they might incorporate queries in certain steps only to avoid reconfigure a column from scratch. Would we like to incorporate a query generation feature for individual columns or would users have to generate a new view from scratch when using queries? (Pardon me if this is a dumb question, since I have never used database nor database query.)

Possible Solutions

1. Database query for the entire view

I really like what Ghislaine has currently in the prototype for viewing queries (although this prototype might be outdated). I think it works well in displaying the query for the entire view.

2. Database query for each column (possibly other elements as well?)

Alternatively, we could allow users to build views step by step using query (again, not sure if this is how it works). This would allow them to have a flexible workflow, where working visually and working with query could come together. The query shown here is just a placeholder.

Final Thoughts

I'm not an expert in this area and haven't personally worked on a database. Any guidance on common database workflows welcomed!

[kgodey]

It seems that we'd like non-technical users to primarily work with views visually (please correct me if I'm wrong). They could add/delete/edit individual columns directly from tables or perform some data aggregation based on tables, and achieve their desired view step by step.

This is correct.

Would working on views step by step require saving a new view at DB level for every step? If so, is that a concern for us?

At the DB level, we will be deleting and creating new views but the user will not need to know this at the UI level. The only thing to keep in mind that I can think of is how to represent errors from this process to the user. We might not be able to automatically update the view in some cases.

Would we like to incorporate a query generation feature for individual columns or would users have to generate a new view from scratch when using queries?

I think per-column query editing (option 2 in your discussion) would be a really cool feature to support in the future, but we don't need to worry about it yet. It would be complicated to implement so I don't think it's worth prioritizing for the alpha release. It might be good to keep in mind that we might want to add this feature in the future while designing, though.