CData Hierarchy Sample

Introduction

This sample project illustrates how to build a dynamic User Interface in HTML for setting up a connection using a CData JDBC driver based on hierarchy.

The project consists of a simple Java backend that can read the information about the connection properties, and a React-based frontend that will render the user interface based on this.

Property definitions

For the most part, properties are defined like this in Hierarchy (NOTE: this only includes the most relevant properties):

    {
      "name": "Share Point Edition",
      "propertyName": "SharePointEdition",
      "display": "RequiredBasic",
      "type": "String",
      "description": "Specifies the Microsoft SharePoint edition to connect to.",
      "default": "SharePoint OnPremise",
      "enum": [
        "SharePoint Online",
        "SharePoint OnPremise"
      ],
      "sensitivity": "",
      "etc": ""
}

Most of these are self-explanatory, with a few notes:

• name will usually be a human readable version of the property name, with spaces in it, while propertyName will be the single word name used in the connection string.
• type describes the data type of the property. This can be one of String, Number, Boolean.
• default defines the default value for a property. This is valid for all property types
• enum will only be present (optionally) for String properties, and defines the set of valid values for the property.
• sensitivity defines if a property is either:
  • SENSITIVE: Value should be masked in logs, etc, but not in UI
  • PASSWORD: Value should be masked in UI and logs.

What is hierarchy?

Hierarchy is a mechanism that provides a more useful alternative to reading a flat list of connection properties from the driver using the JDBC interface. This is achieved by representing connection property information in a hierarchical JSON document.

The Hierarchy JSON document presents a semantic model for a connection user interface. This starts by creating a model that split across two different views: Basic properties, and Advanced properties.

Let's review each one in detail.

Basic Properties

The basic section of the Hierarchy JSON document presents a view that contains the most relevant connection properties for setting up a working connection.

The idea behind Basic is to render a user experience that allows the user to quickly setup a connection by using the most relevant properties required to successfully connect to the datasource.

The Basic view also defines a set of dynamic hierarchical rules that drive a dynamic user interface based on the selected values for other properties. For example, this can be used to indicate that a certain property is only valid if the parent property is set to a specific value.

This provides the necessary means for defining a dynamic UI that is more intuitive for the user, while guiding them into setting the right properties based on the configuration selected.

Advanced View

The advanced view defines the traditional user experience, which includes all possible connection properties. In the Advanced View, properties are grouped by category. CData defines an ordering of categories and of properties within each category, which is implicit in the Hierarchy JSON based on the order of definition within the JSON document.

Hierarchy Rules

What really sets apart the Basic from the Advanced view is hierarchy rules. This defines the relationship between a parent and a child property. This relationship is defined by the child property only being relevant if the parent property is set to a specific value.

Hierarchy rules are defined like this on the parent property:

{
    ...
    "hierarchyRules: {
        "value1": [
        ...
        ],
        "value2": [
        ...
        ]
    }
}

What this represents is that when the parent property is set to value1, then the corresponding array will contain the valid list of child properties that can be set. Same if the parent property is set to value2.

There are a few important things to know about hierarchy rules:

• A single child property can appear in multiple places of the same hierarchy. For example, OAuthClientId might be valid if the AuthScheme is set to OAuth or if it is set to OAuthClient.
• When this happens, the two definitions for the property can differ from one another. Specifically:
  • The default value for the property can be different depending on how the hierarchy is constructed. For example, in SharePoint, the AuthScheme property will have a default of AzureAD when SharePointEdition = SharePoint Online, but a default of NTLM when SharePointEdition = SharePoint OnPremise.
  • The set of valid enumeration values can also differ depending on where in the hierarchy it sits. For example, in SharePoint, the AuthScheme property has 13 valid enumeration values when SharePointEdition = SharePoint Online, but only 5 when SharePointEdition = SharePoint OnPremise.

How to retrieve the hierarchy definition

The hierarchy definition can be queried at runtime from the driver by running a query. THere are two steps required:

• First, open a configuration-only connection. You can do this without specifying any connection property simply by using a JDBC URI prefix like the following jdbc:cdata:<source>:config: where <source> is the data source object name.
• Second, run the following query: SELECT Definition FROM sys_connection_hierarchy WHERE Context = '_jdbc'.

The resulting result set will contain a single String column that contains the corresponding hierarchy JSON document.

Computing the properties

One important aspect to making use of Hierarchy is computing the resulting set of properties that need to be set on the connection URL for the driver to work correctly. This should be done as follows:

• All basic properties currently displayed should be included, even if the value is set to the hierarchy default or a blank string.
• Then add any property from the Advanced tab that you don't already have a value for and has a value different from the default. NOTE: This last part only works if you are using a hierarchy that matches the JDBC URI prefix. If it doesn't, then you might need to set properties with a default value as well.

Running the sample

See the instructions under hierarchy-app.