Column types for Views

[kgodey]

As I've been thinking about Views, I'm conceptualizing them as a superset of tables. Columns in Views can have the same data types as tables but they can also be data that doesn't directly map to table data types and is computed or summarized in some way. I think that we'll need to create additional "Mathesar types" that only apply to views.

As an example, let's take the view referenced in the Views wiki page. We would have the following data types:

Column	DB type	Mathesar type	SQL
ID	INTEGER	Number	-
Title	VARCHAR	Text	-
Actors	-	List	something like SELECT array( SELECT Name FROM Actors );
Last Watched	-	Maximum	something like SELECT MAX(Date) FROM Movie Watch WHERE Movie ID == <id>
Times Watched	-	Count	something like SELECT COUNT(Date) FROM Movie Watch WHERE Movie ID == <id>
Language	VARCHAR	Text	-
Release Year	INTEGER	Number	-

Here, List, Maximum and Count would be View-specific Mathesar data types mapping to a certain type of SQL query. We may not allow users to update data in these types, but we'd be able to recognize them, display them differently, and allow users to create new columns of these types. Please note that these are examples of the concept, I haven't finalized these names or specific behavior.

Questions

1. Does the concept of View specific Mathesar types sound like it will work? Does anyone have alternate ideas on how to represent these?
2. What types should we have?
   1. I think we definitely need something to represent lists.
   2. I'm unsure about having separate types for "Count" and "Maximum" as I've portrayed above. Postgres has a lot of functions and we don't want to have a separate type for each function. I'm thinking about a "Function" type instead.
   3. We might also want to have some types for common functions (Count, etc.) and a generic "Formula" type for everything else (which could include functions).
3. Any other feedback or thoughts raised when reading this?

[ghislaineguerin]

Does the concept of View specific Mathesar types sound like it will work? Does anyone have alternate ideas on how to represent these?

I think these work and will solve most of the immediate problems users might have. I also think that we should have the ability to list or count items based on specific user criteria for list and count.

What types should we have?

I had the same impression regarding count vs. maximum as separate types, and I'd go with function as a type in this case.

Any other feedback or thoughts raised when reading this?

For convenience, I'm wondering if users would appreciate being able to extend types (save functions as custom types).

[seancolsen]

I have some thoughts. But: I'm a little worried that I don't understand or our type system well enough, or perhaps I'm misunderstanding the question. So this could be totally off-base....

Could we instead make read_only a property of all Mathesar types? Aggregate views columns would have read_only == True whereas simpler views columns which map to one table column would have read_only == False. Someday we might even have read-only table columns too, once we have computed data. Perhaps is_writable would be a better name, but that's besides the point.

Beyond "read-only", if there are other characteristics that distinguish views types from table types, I'd lean towards using a similar approach of making type properties based on the smallest possible kernel of that characteristic, conceptually.

As a counter-example, if we think of "Maximum" as a type, I predict we're actually going to end up wanting separate types for "Maximum Number" and "Maximum Date" (and others too) because I should be able to do different things with a date than I can with a number.

[kgodey]

@seancolsen I do think we could make read_only a property of Mathesar types but I am not sure why it would be instead of what I'm proposing.

The problem I'm trying to solve here is to figure out how to model View columns that are based on the result of computed SQL queries to the user in the UI. For example, if a user wanted to add a column to a view to show a comma separated list of actors related to a movie, we'd probably want to allow them to add a "List" column to the view and then configure what the column should be listing. If they wanted to add a count of related actors, we'd allow them to add a "Count" column to the view and then configure what the column should be counting. These types would be read only, but that doesn't help the user identify the intent of the column and configure it accordingly.

I'm not sure if I'm making sense - please let me know if any of this is unclear.

---

Here's some screenshots from creating a column in Airtable that might illustrate the concept:

Airtable's "Rollup" field, which is similar to the "List" field I was proposing.

Airtable's "Count" field.

Airtable's "Formula" field with some example formulas on the left.

[seancolsen]

@kgodey Ok yes, I see. Your question is targeting the UI layer of Mathesar. I misunderstood. I thought you were discussing the types system in a lower-level sense.

[kgodey]

Yes, I'm trying to figure this out so that I can finish creating the design issues for Views. I do think it will tie into the underlying architecture, but we can figure out details after the design is done. I'm mainly trying to figure out if extending the type system to cover Views makes sense as a high-level concept.

[mathemancer]

Does the concept of View specific Mathesar types sound like it will work? Does anyone have alternate ideas on how to represent these?

I'm uncomfortable with overloading the concept of "type" in this way. The List type is a (slightly imprecise) actual data type. However, "count" would just be an integer, and "maximum" would just be whatever type the source column was. I do think we need some way to express generated columns and aggregations to the user. An example of where this overloading creates serious problems: We want to be able to filter based on the type of a column. If we have the "most common" (mode) aggregation, we'll end up with values in the column that have an actual DB type from the original column (e.g., emails). But to filter or otherwise work with those we'll need to have all the apparatus that goes with the MATHESAR_TYPES.EMAIL type, not with some "mode" type. If there are other considerations having to do with the fact that the cell is the mode of some group of entries, that should be handled separately.

As for alternate representations: I think we should separate the data type of a column from the data source or generation method in the user's mind. I.e., you could have an integer column with data from input, or you could have an integer column with data from an aggregation. You could have a composite column with data from input, or you could have a composite column with data from a foreign table. Separating the source or generation of data from its type would be clearer, and would also remove some of the difficulties around the "read only" discussion. Types wouldn't control "read only", and the data source or generation method would make "read only" or not obvious.

What types should we have?

I think we should have a few concepts (avoiding the terminology of types, but trying to capture the concepts you seem to be going for):

1. composite columns (especially for representing columns of foreign tables; also useful for composite types)
2. aggregation columns: These are vertical aggregations of a column:

• sum
• count
• mean
• median
• mode
• max
• min
• lists
• etc.

3. "wide" aggregations: These are vertical aggregations that can be over more than one column:

• count distinct
• first
• last

4. Generated columns: These are horizontal aggregations that use other data in the same row

• same as aggregation columns, but horizontal.

I think we definitely need something to represent lists.

Totally agree. I think we should separate the list type from the method used to generate the list, however. Lists can come from aggregations, but they could also come from interpolating existing data, or even extrapolating from it. They could also be user input.

I'm unsure about having separate types for "Count" and "Maximum" as I've portrayed above. Postgres has a lot of functions and we don't want to have a separate type for each function. I'm thinking about a "Function" type instead.

See my above comment; I think this is one of many tricky problems that would be avoided if we don't overload the concept of "type" to handle "where did the data come from".

3. We might also want to have some types for common functions (Count, etc.) and a generic "Formula" type for everything else (which could include functions).

I think we should provide common data generation methods, and ways to combine them.

• Any other feedback or thoughts raised when reading this?

We're going to have to be reaally careful how we present data generation options to users. There are some methods (e.g., window functions, CTEs, etc.) that are way more powerful than anything available in spreadsheets. However, there are some concepts that are just kind of not available in DB-land. For example, think of the "total" cell in a spreadsheet. You have a column representing per-sale profit that's generated from each row, and then a cell called "total profit" at the bottom of the profit column that's the sum of all cells in the profit column. This is obviously not something that is appropriate in a database table, or even a view.

sale price | our cost | profit
-----------+----------+--------
      5.00 |     3.00 |   2.00
     10.00 |     5.00 |   5.00
-------------------------------
                   TOTAL  7.00

My inclination would be to guide the user toward having a separate view that's just the TOTAL cell above, and perhaps providing a way in the future for them to compose a dashboard of multiple views nicely arranged together (perhaps with charts and whatnot as well).

I think we also need to make sure that it's clear that horizontal aggregation and vertical aggregation aren't the same, and they have different properties in a DB. Any vertical aggregations (e.g., MEDIAN) can be used for horizontal formulas for generated columns as long as the input columns are of appropriate type. It makes sense to take the median of 10 integer columns and put it in a generated column. However, there are a number of formula options for generated columns that don't make sense for vertical aggregations. For example, you could have a formula like "column 7 equals column 1 concatenated with column 3". It doesn't make sense to try to do that in a vertical sense. For vertical aggregations, you really need to be able to just apply some operation to a list of input with arbitrary length.

We should also consider window functions for aggregations and formulas. You can do some really cool stuff (rolling averages, accumulated sums, etc.) that would be pretty useful in a number of situations.

TL;DR: I think we should avoid lumping data generation, functions, and other calculation in with "types". We should instead make it a separate concept and handle it appropriately. We may even want to have different concepts for fundamentally different ways of generating data in the DB.

[kgodey]

@mathemancer Thanks for this thoughtful answer and accompanying explanation. I think I agree with you about having separate concepts for the type of a column and how it's generated. I'll need to do some more thinking about what the new concept should be.

[kgodey]

As for as "totals", I don't think we'd want to represent that in a cell. I think we should offer separate functionality for summaries of groups, we can tackle that in the Summarized Data milestone.

[dmos62]

totals in a cell

I like the idea of using a separate view that would just aggregate the former view. If we can do that that would be pretty cool, because it would mean that views are so frictionless that there's no resistance to firing a new one up for a possibly minor task. I also like the simplicity of that architecture.

[dmos62]

I agree with @seancolsen and @mathemancer. My first instinct was to just have the "derived" columns like sum, list or max (columns whose values are derived from other data in the database) be same old Mathesar types, but read-only, like Sean suggested. Like Brent pointed out, it's not that these columns contain new data types (they don't), but that their value is derived not from external input (like user input or some something else outside the database), but from data that's already on the database. A formalization of the concept of "value origin" and of a database function (e.g. sum, list, max), which would effectively be a type of value origin, is a good solution, I think.

[pavish]

Does the concept of View specific Mathesar types sound like it will work? Does anyone have alternate ideas on how to represent these?

It sounds good to me to have view specific data types.

• I think we definitely need something to represent lists.

  List should probably refer to the array data type that postgres supports. The List mentioned here refers to a result of a subquery. I think this should be a different type.

  • We would only query from a linked table and we probably need a separate discussion on how to show rows for one-one, one-many and many-many relationships in a view.
  • Showing the results like a list would work for one-one and one-many, but would not work for many-many.
  • I think the user should choose whther the linked table values need to be displayed like a list within a column or the joins need to be flattened out to be displayed as how we would expect from a traditional db client.

• "Count" and "Maximum" ... I'm thinking about a "Function" type instead.

  I think having a 'Function' type makes more sense. The cells would be non-editable for function types.

• We might also want to have some types for common functions (Count, etc.) and a generic "Formula" type for everything else (which could include functions).

  I don't think this is a good idea in the long run. We can use a function type and a set of subtypes which includes the common functions (Similar to how Number type has subtypes like integer, float etc.,)

I see the point made by @seancolsen and @mathemancer regarding having the same original mathesar type for the aggregation columns. It does make sense to me to have the user choose a data source for the column separate to the type as this would also solve other cases like representing auto-generated default-value for columns (such as for id). But the design for this is going to be tricky. Hiding it behind a data-source option on the UX might lead to the user not knowing if we support aggregations.

[mathemancer]

List should probably refer to the array data type that postgres supports. The List mentioned here refers to a result of a subquery. I think this should be a different type.

I'm not so sure. We may want to instead use JSON or JSONB arrays for the default list type implementation, since SQL ARRAYs have to be single-type, which might present constraints we don't want. Also, if we want to use the basic SQL ARRAYs, we'll need to figure out how to portray "list of integers" to the user, which adds a bit of mental overhead for them, and a UI/UX challenge for us. The typed arrays are more performant in some ways and include some DB-layer convenience operations, but I think we should be encouraging users to use arrays as the result of some aggregation, and not as a part of their persisted data model structure. From that perspective, the performance is probably less of an issue than convenience. We can pretty easily work around the missing operations.

[dmos62]

The point about the multi-type array not being well suited to production, due to performance issues, gave me pause. A tuple containing multiple types sounds like a table row, which would amount to a row within a row. Maybe a single-type array makes more sense?

[kgodey]

Thanks for the feedback, everyone. I think the simplest solution might be for all columns in Views to have both "type" and "source" attributes. The "source" can be another table or a function. I'll need to do some more thinking about how we can make the source attribute user friendly, I'll start another discussion when I have some thoughts about it.

@ghislaineguerin This is going to affect the design for all the other Views issues as well, since now every column in Views will have a new attribute.

[ghislaineguerin]

@kgodey source will be different from the concept of "reference" that we use in tables right?

[kgodey]

Yes, it will be different.