Skip to content

Table Query Integration

“Table Query Integration” is project to build a new user-facing abstraction that would combine the best of the table page and data explorer into a single, powerful tool.

This wiki page is the top-level place to organize information about the project.

Specs

Adopted

(none yet)

Proposed

  • Product spec
    • Sean’s “Worksheets” proposal: PR / rendered
    • Brent’s “Data Palettes” proposal: rendered

Meetings

2024-02-03 Sean/Brent

  • Recording (1h 44m)
  • We discussed some of Brent’s concerns with Sean’s Worksheets product spec proposal:
    • Composition Instead of the query types being polymorphic, Brent wants query transformations to be polymorphic. To enter raw SQL, you’d enter it into a transformation. This way transformations could be saved independently and composed without any display. Brent doesn’t want worksheet composition. He wants transformation composition.
    • DML Brent would like to offer DML in worksheets, but with some additional guardrails put in place to help users avoid confusing situations. We looked at an example of editing an author name shown in a table of books. He also wants to allow DML that updates more than one cell in PostgreSQL. His vision for this is not entirely fleshed out yet. We need to spend more time exploring and discussing this.
    • Row Grouping Brent has some concerns about the proposed row grouping functionality. At one point he said it “reduces the utility of row grouping”. We didn’t fully chase this topic down though.

2024-02-05 Sean/Pavish

  • Recording (1h 14m)
  • We mostly discussed questions that Pavish had about Sean’s proposed Worksheets product spec in order to clarify Pavish’s understanding of how Worksheets would work.
  • Clarification seemed to resolve many of Pavish’s concerns.
  • Where to worksheets get “saved” was a question we spent some time on, with Sean being open to different approaches. Pavish didn’t seem 100% convinced that saving a worksheet within a schema would be the best approach because (for example), the worksheet could reference DB objects outside the schema which could be confusing for the user. Perhaps some or all worksheets could or should be stored at the database level? We didn’t fully chase down this thread to reach a point of clarity and agreement.
  • DML: Sean gave Pavish a review of some of the user-facing problems that Brent had previously pointed out. We also discussed implementation details for DML. Pavish didn’t raise any significant concerns or strong opinions here.
  • “Nested” data (for lack of a better term) we discussed a new UI idea from Pavish to place aggregated data inside or underneath certain cells. Pavish’s vision for this feature is still not 100% clear to Sean, but Sean expressed interest an enthusiasm for it. It’s not something in the current draft of the spec, and Pavish seemed okay with that for the time being.

2024-02-06 Sean/Brent

  • Recording (1h 30m)
  • We discussed the differences between composition, and query-building in Sean’s Worksheets vision and Brent’s Data Palettes vision, with an emphasis on clarifying Brent’s vision for Sean.

2024-03-07 Sean/Brent

  • recording (1h 36m)

  • Short term vs long term goals:

    • Brent expressed concerns that Sean has been focusing too much on long term goals at the expense of short term goals.
    • Sean expressed a strong intent to prioritize short term goals too. He conceded that his proposed Worksheets product spec did not articulate his desire to build incrementally. Sean wants the whole team to be aligned on the long term goals before building.
    • Brent wanted to know exactly how much clarity do we need on the long term goals before we can start building.
      • Sean didn’t have a simple answer.
      • But in talking it through, Brent and Sean seemed reasonably well aligned on this topic of “how much clarity do we need”. We should figure out roughly where we’re going, and then identify strategies for building incrementally as soon as possible.

  • Long term — Worksheets vs Data Palettes:

    • At the start of the call, Sean and Brent did not feel aligned on a long-term product vision for the user to define queries.

    • Joins:

      • Interestingly, Sean and Brent were able to identify (and hone in on) the concept of joins as a sticking point, mutually, in their concerns with one another’s designs.

      • Sean expressed concern that joins in Data Palettes would be too hard to use.

        Specifically he worried that they would require a conceptual understanding of joins which business users would be unlikely to have. He briefly mentioned difficulties that he had when learning joins, and said he’s seen others struggle with these concepts too. Brent found this interesting and suggested circling back to it in another call. But Brent and Sean didn’t explore any means to solve this problem within Data Palettes.

      • Brent expressed concern that joins in Worksheets would not offer enough power.

        Specifically he worried that the Basic Query would be incapable of joining a worksheet with a table or other worksheet. This join-related concern seemed to be the crux of his larger concern about worksheets being ill-suited for composition. Sean found Brent’s join-related concern thrilling since he had also been pondering it, and he readily offered two potential solutions:

        1. The most elegant (but hardest) approach would be to use static analysis of SQL queries to identify join paths along existing foreign keys. He offered an example. This made sense to Brent.
        2. As a quicker solution, we could allow users to manually codify join paths between worksheets and tables, persisting those join paths in Mathesar metadata. It would be the responsibility of more technical users to specify the join paths. Then less-technical users would automatically gain access to them. This also made sense to Brent.

        Brent seemed rather appeased by Sean’s potential solutions.

    • Brent expressed a desire to focus on Worksheets moving forward.

      His attempts at polishing out the problems with Data Palettes has required a lot of UI/UX thought, and he would like to think less about user interfaces moving forward.

      Instead, he would like to focus on identifying specific problems with Worksheets and working with Sean to find solutions to those problems.

    • Overall, this conversation brought Sean and Brent significantly closer to alignment on the long term goals!

  • Short term goals — editing exploration cells:

    • Conveniently, Sean and Brent agreed that the nearest-term goal should be: allowing users to edit cells within explorations.
    • How to trace data provenance
      • Sean suggested building a SQL static analysis tool into which we could feed the SQL generated by the data explorer.
        • Brent didn’t want to spend time on static analysis, at least not yet. He worried this would introduce too much delay in our deliverables. Sean understood this concern.
      • Brent suggested that we instead rely on the query structure within the data explorer.
        • Sean expressed concern that we’d be throwing away that work later.
        • Brent agreed we’d throw it away, but said it would only constitute about a week’s worth of work.
        • Sean was satisfied with the throw-away work being small enough.
        • Both agreed.
    • How to hand off the right info to the front end
      • Sean suggested the following:
        • The exploration.run API would return some “analysis” data along with the result set.
        • For every column in the result set, the analysis would specify its origin table/column as well as which other column in the result set would provide the row-reference values. Sean walked through some examples.
      • Brent liked Sean’s approach
    • Both agreed we should aim to complete this editing flow for the 0.2.3 release.

Threads

  • 2024-01-28 Email: A “Worksheets for Everything” vision - Sean introduces the concept of worksheets as a hypothetical future project

  • 2024-01-31 Email: Worksheets Product Spec - Sean introduces a PR proposing a formal product spec. He invites people to read it but requests withholding detailed critique until the team has decided to pursue the project.