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_"
 ,"postfix":"_123"
 ,"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 and Aggregate And Window Functions for your view columns.

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. This works on concert with the "postfix" property: they are optional, either can be specified, or both can be specified.
• postfix: a simple pattern to append 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. This works on concert with the "prefix" property: they are optional, either can be specified, or both can be specified.
• 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.