Index | Table schemata

Table schemata

Table of contents

Introduction

The data records managed by Tables are comprised by dictionary that consist of key-value pairs, where key denotes the identifier of a datum expressed by the value. A new table is created using one of the predefined schemata as a starting point. Once created, the schema of a table can be changed.

Notice that any changes you make after you have inserted rows into a table will not affect the data records existing prior to the change.

Changing the schema of a table

To change the schema of a table, switch to the management view. Locate the table whose schema you want to change in the list. In the drop-down menu opened by the cog wheel icon of the list item click Schema. You can now edit the schema of the table in JSON format. Once you are finished making your changes, click Save.

Structure

At the top level, a JSON table schema consists of an object containing information about the run-time environment itself. It supports the following attributes, all of which are optional and of type string:

Example:

{
  "project": "p11",
  "title": "A title",
  "description": "A brief description",
  "version": "0.1",
  "canonicalColumn": "personName",
  ...
}

Column specification

The top-level attribute columns specifies an array of the columns displayed in the table. Columns are specified in from left to right, i. e. the left-most column is specified first. Each column is a nested object.

Example:

{
  "columns": [
    {
      "id": "_id",
      "description": "Row ID",
      ...
    },
    ...
  ]
}

Each column is an object consisting of the following attributes, each of which is either mandatory, recommended or optional:

Identifiers

Column identifiers are character strings and are unique across columns. For example, foo refers to the key in a data record (and thus the homonymous column) called foo.

If used as references to other columns, identifiers can be used in logical expressions (see Logical evaluation below) or computations (see Computations below).

Default values

When a new row is created all its columns default to null. If a default value is specified, this value is used instead.

If autoIncrement equals true, the column value is generated based on the highest existing value of this column plus 1. If no other rows exists, or if the values of this column are null, the default value or 1 ("1" for string types) is used instead.

autoIncrement works for column types number and string only.

If the type of an autoIncrement column is string, the numeric value can be combined with a prefix and/or postfix, both of which must also be string values. The resulting format is <prefix><number><postfix>, where prefix and postfix default to the empty string.

autoIncrement can be specified for a single column only.

Example for a simple numeric auto-increment column, starting at 1:

{
  "id": "autoNumber",
  "description": "Auto-incremented number",
  "title": "AutoNum",
  "type": "number",
  "autoIncrement": true
}

Example for a column that consists of the prefix "FA" followed by a 4-digit integer value, starting at FA1001:

{
  "id": "familyId",
  "description": "Family ID",
  "title": "Family",
  "type": "string",
  "autoIncrement": true,
  "default": "FA1001",
  "prefix": "FA"
}

Note that ‘textual numeric’ values are sorted as follows:

This means that string types (with or without prefix/postfix) may produce unpredictable results and/or duplicates. If in doubt about the number of rows, use number instead of string.

Options (categorical data)

Sets of categorical data (data type option) are specified via an ordered array of options. Options either consist of a single character string, which denotes both the textual and actual representation of the data, or of an object of attributes. In the latter case, each option consists of the following attributes:

Example for a simple set of options, each of which is used as both the actual value stored in the record, and the text displayed on screen:

"options": [
  "foo",
  "bar",
  "baz"
]

Example for a complex set of options, which represents the underlying numeric data via text: Display the option called ‘foo’ to get the numeric value 1.

"options": [
  {
    "value": 1,
    "text": "foo",
    "description": "Bogus value"
  },
  {
    "value": 2,
    "text": "bar"
  },
  {
    "value": 3,
    "text": "baz"
  }
]

Logical evaluation

Tables supports conditional processing based on logical clauses. A clause is a binary expression consisting of two operands and an operator.

Operands can be one of the following types:

The following keywords are reserved and correspond to the respective literal values: true, false and null.

Operators are:

Except for ‘equal’ and ‘not equal’, all operators depend on ordinal data, i. e. both operands must have properties that support ordered comparisons. The parser expects one or more blank characters ("\u0020") between operands and operators.

Examples:

Expressions can be combined via logical conjunctions (and) and disjunctions (or) to form a more complex clause.

Examples:

Computations

Tables is able to perform simple arithmetic computations, all of which depend on two operands and a basic operator. Operands can be literals or references to keys in the data row. Operators can be one of the following:

Example:

{
  "compute": "foo * bar + 4.7"
}

Styling via CSS

Table cell can be decorated using CSS classes. Style rules can be unconditional or conditional.

Unconditional rules are specified via a character string that denotes the CSS class that applies, or as an array of such strings.

Example:

{
  "css": "my-css-class"
}
{
  "css": [
    "foo",
    "bar"
  ]
}

Conditional rules apply only if a particular condition applies, i. e. if a logical expression (see Logical evaluation above) evaluates as true. Such rules consist of an object, or array of objects, that specify a series of rules that apply depending on the actual value of the datum in question. Such rules must contain the two properties clause and class. clause is a conditional term that determines whether a particular CSS class applies.

Example:

{
  "css": [
    {
      "clause": "risk < 0.25",
      "class": "low-risk",
      "description": "low range"
    },
    {
      "clause": "risk >= 0.25 and risk < 0.75",
      "class": "mid-risk",
      "description": "mid range"
    },
    {
      "clause": "risk >= 0.75",
      "class": "high-risk",
      "description": "high range"
    }
  ]
}

applies one of three, mutually exclusive classes depending on the value of risk (assuming a positive normalised value for risk).

Badges

Badges work like CSS styling in that they decorate the content of a table cell based on the value of the underlying datum. However, instead of decorating the table cell, badges render the value as a decorative ‘badge’, which in turn is displayed in the table cell.

Badges work exactly like CSS definitions, but use the attribute called badge instead of class to specify a Bootstrap badge instead of a CSS class. Bootstrap supports the following badges:

Example:

{
  "badge": [
    {
      "clause": "risk < 0.5",
      "badge": "success",
      "description": "OK"
    },
    {
      "clause": "risk >= 0.5",
      "badge": "warning",
      "description": "Potential danger"
    }
  ]
}

Memos

Data type memo treats the column as a gateway to the memos of a table row. Note that a column of type memo does not correspond to any data in the data record itself. Such a column just grants access to its memos. See section Widgets below for an example of how to make memos accessible via an action menu.

Widgets

Widgets are UI components that are placed into table cells in lieu of actual data. The specification of a widget column includes properties such as the form and appearance of the widget in question, as well as the action triggered by it.

The id of a widget column is used for referential purposes only, and the type is always widget. The form of the widget determines the shape the widget takes, which can be either of

text denotes textual information used for decorating the widget. This is only useful for form badge and button, as form glyph generates a graphical construct (Unicode code points, really) devoid of textual information. menu renders a drop-down menu that provides access to various actions pertaining to the underlying table row. These action are specified by an additional key, actions, whose value is a list of action applicable for the row. Each action consists of an object that specifies the actual action (key action) and the menu label displayed in the menu (key text). For a list of possible actions, see the explanation of action below.

description serves as an informational hint about the widget’s purpose, and is used to render its tooltip.

class denotes an array of CSS classes used to decorate the widget.

Finally, action denotes the action that is triggered when a user clicks the widget or a menu item (form equals menu). The following actions exist:

Example for a glyph widget:

{
  "id": "column_list",
  "type": "widget",
  "form": "glyph",
  "action": "list",
  "description": "List contents of row",
  "class": [
    "fa",
    "fa-list-alt"
  ]
}

Example for a menu widget:

{
  "id": "actions",
  "type": "widget",
  "form": "menu",
  "description": "Row actions",
  "actions": [
    {
      "text": "List",
      "action": "list"
    },
    {
      "text": "Edit",
      "action": "edit"
    },
    {
      "text": "Audit",
      "action": "audit"
    },
    {
      "text": "Memos",
      "action": "memo"
    },
    {
      "text": "Link",
      "action": "link"
    },
    {
      "text": "Delete",
      "action": "delete",
      "class": [
        "text-bg-danger"
      ]
    }
  ]
}

Triggers

Triggers are operations that occur in response to actions. Triggers are specified as keywords that denote their type, and values that are an object (or array thereof) of the action that is taken in response to the trigger.

onChange

Currently, the only trigger supported is onChange, which triggers once the value of a specific column undergoes change.

Currently, the only operation that can be performed upon trigger is a series of computations (see Computations above), which are specified via compute. It performs a computation of any combination of columns and stores the result in a column.

Secondary attributes of compute are depends and target. depends specifies what columns the computation depends on. Computations strictly depend on numeric data, which means that the underlying operands must not be unspecified, null, boolean or textual. If any of the operands does not meet these requirements, the trigger is skipped.

target specifies the column into which the result of the computation is stored. If a trigger is specified for the target, that secondary trigger does not trigger itself.

Example:

{
  "columns": [
    ...
    {
      "id": "probability",
      "type": "number",
      "min": 1,
      "max": 4,
      "onChange": [
        {
          "depends": [
            "probability",
            "severity"
          ],
          "compute": "_ * severity",
          "target": "risk"
        }
      ],
      ...
    },
    ...
  ]
}

This example specifies that if the column probability is changed, a multiplication be performed and the result be stored in the column called risk. This means that updates of probability indirectly affect the value of risk:

risk ← probability﹒severity

_ refers to the value of probability itself. probability and severity serve as factors of the multiplication, which is why both are specified as dependencies. If any of the dependencies are undefined or null, risk is not updated.