Issue review for "Working with Views"

[kgodey]

I took an initial pass at creating design and backend issues for the "Working with Views" milestone. Please see:

• Meta-issue: Query Builder and Views design #442
• Design
  • Support for opening and interacting with a View #466
  • Support for deleting a database view #454
  • Support for listing database views #455
  • Support for editing records within a view #456
• Backend
  • API for reading views #467
  • API for suggesting columns relevant to building a view #464
  • API for creating new views #465
  • API for editing the structure of existing views #468
  • API for deleting views #469
  • API for editing records within a view #470

Views are an entirely new class of database object with a whole bunch of complexities, so I'd like to get feedback from @mathemancer and @pavish on implementation details and whether I've covered the necessary details in the design issues. I'm especially worried abut the view creation and editing flow – we need to convert lists of columns to queries with JOINs and I'm not sure what we can and cannot automate easily.

Please review by end of day Monday, July 26.

@ghislaineguerin I'd also like your feedback on the design issues, but it's not as time-sensitive since I'm mainly focused on covering implementation details right now.

[mathemancer]

For an initial pass, I think the specification that we only make views from columns in tables joined by foreign key relationships is a good idea. This makes automating joining fairly straightforward.

Editing records within a view is extremely complicated, and will require both time for definition and specification, as well as a quite complicated implementation involving both SQL and python. We'll probably need bunches of sub-issues to track the work underlying that API change.

I commented on a couple of the specific issues with some more details.

[kgodey]

@mathemancer I'd like to understand editing records within a view better. I think we should have the conversation directly on that issue. I posted a follow up question.

I'm assuming everything else looks good/doable?

[pavish]

@kgodey The issues look good to me. I have the same concern as @mathemancer, regarding allowing editing records in views.

I think it will be quite time consuming to implement on the backend. Since this is a rare usecase, maybe we should reconsider having this for the MVP.

[kgodey]

@pavish I'm not so sure that it will be a rare use case. If we're trying to get people to use databases optimally, they'll be working with views very often, since their tables will be normalized and may not offer a great view of all the data they want to see. It would be ideal if they could update data wherever they were working.

As I mentioned in my response to @mathemancer, we should continue the conversation in #442. I'd like to understand the complexity involved a bit better before I make a decision on whether to remove it from the MVP scope. Postgres supports updates, inserts, and deletes directly into views in some cases, so I'm hoping that we can make use of that functionality without having to do anything too complicated on the backend.

[kgodey]

I'm calling this review complete, it looks like the issues as-is as reasonable. The only thing I made was a future issue:

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

[ghislaineguerin]

@kgodey I've reviewed the design issues and have no comments for now, they all look good to me.

[kgodey]

Thanks @ghislaineguerin!