Support for creating a database view from scratch

[kgodey]

The Mathesar UI should allow users to create a new database view from scratch. The flow should be as follows:

1. The user clicks on a "new view" button (or something similar).
2. We open the Data Explorer (see Design for visual query builder ("Data Explorer") #1065)
   1. There should be some indication that the data explorer was opened in response to a View creation request.
3. The user can save the output as a View.

Scenarios

1. The flow listed above should be covered.
2. Error scenarios:
   1. The user clicks on the "new view" button and there is an error loading columns from the backend.
3. Error scenarios for query builder (in case they have not been covered by previous work):
   1. There are errors loading various portions of the query builder
   2. The user has gone through the query builder flow but there is a problem creating the view.
   3. The user has gone through the query builder flow and we have created the view but there is a problem with loading the data for the view.

Additional context

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

[kgodey]

@ghislaineguerin @pavish @mathemancer @seancolsen 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.

[seancolsen]

Before I get deeper into formulating feedback, I have a question...

Say I have the following schema:

- address
  - id
  - addressee
  - street
  - city
  - state
  - zip_code

- shipment
  - id
  - origin (FK to address.id)
  - destination (FK to address.id)
  - priority_level
  - departure_datetime
  - arrival_datetime

Will it be possible for me to create a view that displays this?

SELECT
  shipment.id,
  origin.addressee,
  destination.addressee
FROM shipment
JOIN address AS origin ON origin.id = shipment.origin
JOIN address AS destination ON destination.id = shipment.destination;

I've crafted this example to have a view that meaningfully references the same column twice in two different ways.

The proposed design reads:

...the user to select columns...

That makes it seem to me as though the a set of columns (perhaps ordered) will comprise the entire input to the view creation. If that's true, I have many more follow-up thoughts -- but I want to make sure I understand the proposal correctly first.

[mathemancer]

@seancolsen has a great example there. Many JOINs require some understanding of what the data is and how it relates. Perhaps we could start by only handling very simple cases, then extend the UI to be more featureful. I fear we'll very quickly run up against the boundaries of the simple cases, though. @seancolsen's example isn't that exotic, and I'd expect joins of that type to be semi-common.

[mathemancer]

One idea would be to only be able to do very basic views through the described flow, and have more advanced views via:

• show preview of other table when interacting with foreign key column (somehow).
• Let the user add columns from that preview. This would essentially specify the join used.
• If they want to do the same from another preview for another foreign key column, they can.

So, for @seancolsen's example, you'd start from the shipments table, hover (or whatever) the destination foreign key column, and pick the addressee column from the addresses table. Then you'd hover (or whatever) the origin foreign key column, and pick the addressee column from the addresses table.

I do think that when constructing a view, much more "column-oriented" thinking should be useful than when initially linking tables. (Though in the shipments example, you'd need it at both stages somewhat).

Please don't take the example UI too literally. The point is just that it might make sense to interact with the foreign key column and choose available linked columns at that point.

[pavish]

We will only show columns in the same table or view or columns from related tables or views
(based on existing foreign key constraints).

Instead of thinking in terms of adding columns from a linked table, I think we should think in terms of adding columns from a link i.e. treating the link as the primary input.

i.e In the example @seancolsen provided, if the user tries creating the view from the address table instead of the shipments table, we could show 2 options like:

• Add column from shipments using link address.id -> shipment.origin
• Add column from shipments using link address.id -> shipment.destination

[kgodey]

I like the idea of thinking in terms of adding columns from a link. I've updated the description.

I do think it should be possible to reference the same column twice in different ways.

[kgodey]

Marking this as ready and moving to backlog.

[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.