Skip to content

Tilda JSON Syntax: Views

Jump to bottom
Laurent Hasson edited this page Oct 28, 2023 · 11 revisions

<-- Main Schema Syntax

Views

Whereas Objects define table equivalents, Views define view equivalents. Tilda breaks down the various components of a view in a way that makes semantics clear, e.g., joins, where clause filters, calculations and so on. Views can be used in a very simple straightforward way, or make use of advanced patterns to generate complex code logic. In this section, we'll focus on the various definitional elements, and provide some examples including generated SQL code for comparison. In particular, pivoting, formulas and view realization (an optimized form of materialization) will be discussed.

Tilda views can be very powerful because semantics of how the views are assembled are maintained, enabling dependency management which is key for validation and migration purpose for example.

Main Outline

Views are defined as follows:

{ "name":"OrganizationView"
 ,"description": "blah blah"
 ,"mode":"NORMAL"|"DB_ONLY"|"CODE_ONLY"|"NONE"
 ,"columns":[
   ],
 ,"distinctOn":{
   }
 ,"subWhereX":{
   }
 ,"joins":[
   ]
 ,"pivots":[
   ]
 ,"timeSeries":[
   ]
 ,"importFormulas":[
   ]
 ,"formulaTemplates":[
   ]
 ,"formulaColumns":[
   ]
 ,"queries":[
   ]
 ,"outputMaps":[
   ]
 ,"masks":[
   ]
 ,"realize":{
   }
}

The fields are:

  • name: the name of the view, mapping effectively to the name of the Viewin the database. The name must end in "View".
  • mode: optional, NORMAL by default, indicating how code is generated at the application and database level.
    • NORMAL: generates ALL artifacts, at the database level as well as the application level.
    • DB_ONLY: only generates database-level artifacts (i.e., no application code generated). In some cases, Tilda is used to manage complex schemas and not necessarily to support applications. For that use case, this field can be used to eliminate a lot of code generating, making the project size (i.e., files and final compiled JARs) much lighter.
    • CODE_ONLY: No code or database assets is generated at all. The object exists purely as meta-data, for example, to map to a custom table/view not defined in Tilda per se.
    • NONE: only generates application-level code (i.e., no database-level artifacts are generated). This is useful to customize a complex query where columns are known in advance and reuse JDBC Row handling, queries, JSON formatting etc… on the code-side.* description: HTML-based description field to document the view. This field is mandatory: models must be documented in Tilda.
  • columns: a list of view column definitions (see next section for details).
  • subWhereX: a definition of a where clause for the view.
  • joins: additional join specifications for non-obvious joins, i.e., joins that cannot be automatically deduced from the model.
  • distinctOn: a list of columns to define distinctness on the view.
  • pivots: specify pivoting columns and results
  • timeSeries: expand a view for a date/datetime column against a date dimension, for example, to get daily data in aggregates.
  • importFormulas: a list of formulas from other views to be imported in this view.
  • formulaTemplates: a list of formula templates, constructs used to define a group of individual formulas in a generative way.
  • formulaColumns: a list of formula definitions.
  • queries: definition of preferred queries for access.
  • outputMaps: definition of output capabilities such as NVPs, CSV or JSON data formats.
  • masks: definition of field masking logic.
  • realize: a mechanism to define how a view may be materialized in an optimized fashion against a table.

🎈 NOTE: All view must have a name ending in "view" (or "View", or "VIEW"), e.g., OrganizationView, Organization_VIEW etc...

The meat of an View's definition is to define columns, possibly where clauses and joins, and export output formats:

Advanced features:

Clone this wiki locally