Tilda JSON Syntax: View Columns

<-- View Syntax

Columns

Views contain columns as a baseline for their definition. That’s pretty obvious. Although many elements are shared with Object columns, view columns are very different: for example, they allow aggregates, expressions, are picked from a source column in either another view or a root table etc... A view column looks like the following:

// A regular view column
{ "sameAs":"someSchema.SomeTable.someColumn" // let's assume it's a date time column
 ,"name": "someColumnX"
 ,"coalesce":'1111-11-11'
 ,"as":"_2"
 ,"description":"Type"
 ,"formulaOnly": true|false
 ,"joinOnly": true|false
}

// An aggregate view column
{ "sameAs":"someSchema.SomeTable.someColumnInt" 
 ,"name": "someColumnIntX"
 ,"description":"ColumnInt X blah blah blah"
 ,"aggregate": "SUM"|"AVG"|"MIN"|"FIRST"|"MAX"|"LAST"
              |"DEV"|"VAR"|"COUNT"|"ARRAY"|"ARRAYCAT"
 ,"orderBy":["col1","col2"]
 ,"distinct": true|false
 ,"filter": "\"colA\" > 10"
}

// An expression view column
{ "sameAs":"someSchema.SomeTable.someColumnDateTime" // let's assume it's a date time column
 ,"name": "someColumnDateTimeX"
 ,"description":"ColumnDate X blah blah blah"
 ,"expression":"?::DATE" // convert to a DATE
 ,"type": "DATE" // new type
 ,"size": 2 // only for STRING type expressions
}

// A .* view column
{ "sameAs":"someSchema.SomeTable2.*"
 ,"prefix":"abc_"
 ,"exclude":["col1","col2"]
 ,"block":["colA","colB"]
}

The main fields are:

• sameAs: the mandatory source of the column, which could come from a Table or another View. the format is <package_name>.<schema_name>.<table_name|view_name>.<column_name>. If referencing a column in the same schema as the defined view, <package_name>.<schema_name> doesn't need to be specified.
• name: an optional renaming of the column. This is useful if creating a view over multiple tables where there are duplicate column names. See the Tilda Naming Convention.
• coalesce: a value to coalesce to in case the original column has a null value. Tilda will check that the value is compatible to the type of the underlying column.
• as: A string, typically of the form "_2" or "_3" that is used to identify which joined table/view to use in case of a naming conflict.
• formulaOnly: true|false, false by default, indicating whether this column should be visible to formulas only, i.e., the column woldn't be exposed in the final view.,
• joinOnly: true|false, false by default, indicating whether the column is brought in purely to facility automated join resolution, i.e., the column is not exposed in the final view.
• description: a meaningful description of the column, which could contain plain HTML, and which will be output in the automatically generated documentation (JavaDocs as well as HTML SchemaDocs).

🎈 NOTE: Column types are inherited from the baseline columns referenced.

You can also define Short Expressions for your view columns.

Additionally, you can define aggregate attributes:

• aggregate: SUM|AVG|MIN|FIRST|MAX|LAST|DEV|VAR|COUNT|ARRAY|ARRAYCAT. Some aggregates may not be supported by some databases by default and may need to be created. For example, FIRST and LAST are not supported out of the box in Postgres and are defined in the supporting Tilda SQL functions. ARRAY for example is not supported in SQLServer and may instead return a comma-separated concatenation of the data retrieved.
• orderBy: an optional array of 1 or more columns to order the results in the aggregate if applicable, e.g., first, last, array.
• distinct: true|false, false by default, indicating whether the results in the aggregate should be unique if applicable, e.g., first, last, array.
• filter: a boolean expression using SQL syntax that can be used to filter the results for an aggregate, for example, "\"colA\" > 10". The expression provided is used as is and can therefore make use of database-specific functionality. The filter feature is in and of itself not supported in all databases: it is in Postgres but not in SQLServer for example. Note that column names must be escaped properly.

🎈 NOTE: Since you can use any valid code in the filter, Tilda can't validate it. If incorrect, you will get an error when migrating your schema and the view is created.

🎈 NOTE: If you define an expression, it takes precedence, and any aggregate will operate over the final value.

🎈 NOTE: Coalesce happens at the final level and will consume any value returned by an expression or an aggregate (or combination).

🎈 NOTE: If you use aggregates, these must be defined last in the list of columns. Tilda will enforce this and will automatically handle any group-by clause in the final SQL based on the non-aggregate columns provided.

Finally, you can use column expansion, with some limitations. Attributes are:

• sameAs: "someSchema.SomeTable2.*" where the "*" denotes a wild-card. You can use wildcards at the beginning or end of a column pattern only. For example, if a source table/view has columns "firstName", "lastName" and "middleName", you could sweep all three by doing "*Name". Similarly, if a source table has fields "addressLine1", "addressLine2" and "addressLine3", you can sweep all of them with "addressLine*". A simple "*" will get ALL columns from the referenced table/view.
• prefix: a simple pattern to prepend to resulting column names. This is useful for example when creating a view over multiple tables with generic names that may collide in the final view.
• exclude: an optional array of 1 or more column names to exclude from the expansion. For example, you may want most columns from a source table/view except for a few of them.
• block: an optional array of 1 or more column names to block from the final view. This is equivalent to the "formulaOnly" attribute discussed earlier: the columns blocked can be used by the view for internal calculations, but will not be exposed in the final view.

🎈 NOTE: Column expansion is very powerful but can backfire. Because the collection of columns is automated, a change in a referenced table, maybe in a completely separate module, will cause the view to be updated automatically. This is nice because you can create layered views to capture specific concepts and the whole is maintained automatically, but it could also create surprises if your view suddenly gets a new column you weren't expecting. In the best case, this is just a new column and won't break anything. In the worst case, a new column could clash with a pre-existing column and cause an error due to the name duplication. The generated documentation should make clear what the final view looks like.

🎈 NOTE: Similarly, if someone were to remove a column from an underlying table or view, it would affect your view in more drastic ways, i.e., break code that depended on that column. In general, it's not good to remove columns from a table or view and such operations is typically part of a concerted refactoring exercise and should be managed accordingly.