Skip to content

Tilda JSON Syntax: Object Columns

Jump to bottom
Laurent Hasson edited this page Sep 6, 2022 · 6 revisions

<-- Object Syntax

Columns

Tables contain columns. That’s pretty obvious. A column looks like the following:

// A regular column
{ "name": "type"
 ,"type": "STRING"
 ,"size":2
 ,"nullable": false
 ,"mode":"NORMAL"
 ,"protect":"ABSOLUTE"
 ,"invariant": true
 ,"description":"Type"
 , "values":[
    ]
 , "default":"abc"
}

// A column that inherits attributes from another column (sameAs).
{ "name":"orgId"
 ,"sameAs":"Organization.id"
 ,"invariant": true
 ,"description":"Medical system source id"
}

// A JSON column with a type definition.
{ "name":"data"
 ,"type":"JSON"
 ,"description":"JSON Data"
 ,"jsonSchema": {
   }

}

The fields are:

  • name: the mandatory name of the column. See the Tilda Naming Convention.

  • type: A type for the column. Tilda uses a generic type system. See the Tilda Type System.

  • nullable: optional, true by default, whether the column is nullable or not.

  • mode: optional, NORMAL by default, the mode for the column. The following values are allowed:

    • NORMAL: a normal column.
    • AUTO: a column that is wholly managed by the application code and cannot be set/get directly. This can be used for caching or hidden values.
    • CALCULATED: a column that only exists in the application space (doesn’t exist in the database), and can represent some client-side calculations.

  • protect: optional, ABSOLUTE by default, denotes the protection/security mode for the column. This is settable only for STRING columns. This is typically used for contents that is client-based and is re-rendered back on the client (i.e., the Browser). Allowed values are:

    • NONE: no protection. What goes in is what goes out.
    • SMART: will parse input data and eliminate injection opportunities in JavaScript.
    • ABSOLUTE: replaces all ‘<’ characters as ‘&lt;’

  • invariant: whether the column can be updated after being initially set. This means the column can be set when a record is created, but cannot be updated afterwards, i.e., no setter is available in the application space.

  • 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).

  • values: an optional set of name/value pairs for values allowed for the column. See the next section for details on this element.

  • default: a default value for a column. The value must match the type of the column and is generally used for non-nullable fields, although the framework won't scream at you if you use it for nullable fields too.

  • jsonSchema: an optional json definition of the schema for that field. See the next section for details on this element.

  • sameAs: A column can be defined in terms of another column. This is useful for a variety of things:

    • This can help make the database more consistent with related fields linked together across tables and schemas, for example, an addressLine1 field which would be a String of 255 characters across the board.
    • This is also the base construct to declare a foreign key by linking the definition of a column to that of the column for the primary key in a parent table.

    When declaring a sameAs, a number of fields are automatically inherited from the linked column:

    • type: cannot be changed
    • size: cannot be changed
    • nullable: can be overridden (i.e., it’s possible to have a column in the parent table be not-null, while a child table would have a nullable foreign key).
    • invariant: same as for nullable.
    • mode: cannot be changed
    • protect: cannot be changed
    • values: cannot be changed
    • description: can be changed to reflect the semantics of the column in the child table. For example, a primary key field may be described as “Surrogate key for the Patient table”, whereas in the child table, it would be “Foreign key to the Patient table”.
    • jsonSchema: cannot be changed

Values

A column can define a set of values that are allowed, i.e., an enumeration type. This is only setting constants and making it easier to generate client-side artifacts (e.g., drop downs), or help abstract code from constant values. No constraint is generated to limit values for this column in the database itself.

"values":[
   { "name": "Org" , "value": "O", "description": "Organization" }
  ,{ "name": "Fac" , "value": "F", "description": "Facility"
    ,"label":"Facility", "groupings":["Facilities"]
   },
  ,{ "name": "Clin", "value": "C", "description": "Clinics"
    ,"label":"Clinics", "groupings":["Facilities"]
   },
  ,{ "name": "Other", "value": "X", "description": "Other"
    ,"default":"CREATE"
   },
]

The fields are:

  • name: a mandatory partial name of the constant generated. If the column names is called “xxx” and the value is named “Abc”, the final variable generated will be called “xxxAbc”, which is the reason why contrary to column names, value names start with an upper case.
  • value: the mandatory value to be stored in the DB. The type of the values must be compatible with the type of the column, i.e., you cannot have a string value for an integer column.
    • For DATE and DATETIME columns, values of NOW (current date/timestamp), UNDEFINED (a system value of Jan 1st year 1 at 0h:0mn:0s:0ms).
    • For DATE, a an explicit timestamp of the format yyyy-MM-dd can be used.
  • description: a mandatory description for the value
  • label: an optional label for the value, to be used in front end ui for example in a drop down. If a label isn’t supplied, the value of “name” will be used.
  • groupings: a list of string that can be used in the UI to subset the list of values.
  • default: CREATE, UPDATE, NONE, ALWAYS, whether the value should be used as default at creation time, update time, or always. If unspecified, NONE is used. This allows to create columns with default values.

🎈 NOTE: Names, Values and Labels should be unique in the list.

🎈 NOTE: Only one value can be made as default CREATE, UPDATE or ALWAYS.

jsonSchema

A column that contains JSON data may also define a mini-schema. Code-side artifacts will be generated to access that data at runtime. The data can be extended easily, i.e., add new elements etc… However, on the code-side only fields defined here will be accessible unless the developer implements custom json logic. Typically, the json definition will be evolved naturally as part of the application design, without affecting the database table structure (i.e., schema-less), making incremental improvements a breeze without requiring complex redesigns or migrations (i.e., the promise of most NoSQL systems).

  • If the database targeted does not support JSON as a data type, a text field will be used, and only code-side will have json capabilities (i.e., database level artifacts such as external ETL processes, user defined functions or stored procedures will access the field as a plain text field and will need to implement their own json logic).
  • If the targeted database supports JSONB, that type will be preferred over text-based plain JSON.
  • PostgreSQL is a particularly great database to manage JSON payloads and we have implemented very large tables (billions of rows) with essentially a primary key, some fields related to the natural key of the object, and then a single column containing a flexible JSON payload.
// A JSON column with a JSON schema type definition.
{ "name":"data"
 ,"type":"JSON"
 ,"description":"Some JSON Data"
 ,"jsonSchema": {
     "typeName": "ClinicalRule"
    ,"descr": "blah..."
    ,"fields": [
	 {"name":"drgs" , "type":"STRING[]", "description":"DRG codes" }
	,{"name":"icd9s", "type":"STRING[]", "description":"ICD9 dx codes" }
      ]
    ,"validation": {
         "rule": [
            "   drgs   IS NOT NULL AND len(drgs  ) > 0"
           ,"OR icd9s  IS NOT NULL AND len(icd9s ) > 0"
          ]
        ,"descr": "At least one DRG or ICD9 value must be supplied"
      }
   }
}

The elements are as follows:

  • typeName: the name of top-level element for this field when a full JSON is exported.
  • descr: a description for the json data type
  • fields: the list of fields, similar to columns in an object definition, with the field's name, type, size, nullable, and description.
  • validation: the definition of rules (as an array of strings to make it easier to capture more complex multi-line rules) to validate whether the json data is ok or not.
    • rule: An expression that can be run at runtime in the generated code. This formula uses SQL with a few generalized functions for data processing. See the next chapter for details on the formula language.
    • descr: the description of what the formula does.
Clone this wiki locally