Query Builder and Views design

[kgodey]

Mathesar should support Postgres views. This is a meta-issue to structure all of the work needed to support that functionality. We will complete this work in milestone Working with Views.

Please see the following more information about views and queries:

• Queries and Views Product Spec
• Views "Concepts" wiki page

Issues

Issues directly related to queries and views:

• Design for visual query builder ("Data Explorer") #1065
• Support for listing database views #455
• Support for opening and interacting with a View #466
• Support for editing records within a view #456
• Support for creating a database view from scratch #781
• Support for editing the structure of a database view #783
• Support for creating a view based on an existing table or view #782
• Support for materialized views #834
• Support for Adding Formula Columns in Data Explorer #1252

Issues supporting or depending on queries and views:

• Left pane UX improvements #723
• Design for List data type #978
• Support the PostgreSQL Range data types #1274
• Improve support for finding duplicate rows #1028

Previously created issues that were folded into other issues above:

• Support for deleting a database view #454
• Support for renaming a view #805

[mathemancer]

@kgodey
Continuing the conversation from #471 :

One problem is going to be that if the purpose for having views is to be able to put a table back together that's been normalized into its constituent atoms, this will fall outside of what PostgreSQL will be able to deal with automatically, w.r.t. editing records. Note from the docs:

• The view must have exactly one entry in its FROM list, which must be a table or another updatable view.

This means we won't be able to compose an automatically-updatable view from multiple tables. We would be able to compose one from a select on a single table under some circumstances. Off the top of my head, some uses for which this makes sense are:

• hiding columns,
• reordering columns (i.e., changing the column order, not the record order),
• renaming columns without renaming the underlying data columns,
• changing the type of the columns without changing the underlying type
  • suppose you want to see a given REAL column without the digits after the decimal point, but don't want to actually remove those digits in the underlying data. You could create a view where that column is an INTEGER.

[kgodey]

@mathemancer That makes sense. I'd like us to build out the product a little more before we decide whether we need to provide functionality to update views that don't meet the PostgreSQL definition of updatable. I'll create an issue for it but put it in "future" for now. We can reevaluate when to schedule it in a few weeks.

Edited to add:

• Created Backend spec for updating data in views that don't meet PostgreSQL "updatable" status #504

[mathemancer]

Another quick note about views: When implementing, note that they do have OIDs (and are available the same way as tables in the pg_catalog.pg_class system catalog table). So, the implementer setting up the views could probably create a model similar to the Table model, using the same id -- oid mapping.

[kgodey]

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