Designing a Dynamic UI for Establishing Connections using CData Embedded Connectors


Overview

CData provides connectors for a variety of different data sources, each with their own authentication mechanisms and configuration options. Each connector has a distinct set of properties that users configure to establish a connection to these sources. CData's Embedded partners often require an easily configurable user interface that exposes these properties to the user. To support this requirement, the drivers return information on the available properties via a system table called sys_connection_hierarchy.

sys_connection_hierarchy

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

A sample property definition can be seen below:

{
    "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": ""
}

Exposed attributes

Below are a few definitions on the exposed attributes:

  • name will usually be a human readable version of the property name with spaces in it.
  • propertyName will be the single word name used in the connection string.
  • type describes the data type of the property. This value can be one of String, Number, or Boolean.
  • default defines the default value for a property. This value is used 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 the UI.
    • PASSWORD: Value should be masked in both the UI and the logs.

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

Basic View

The Hierarchy JSON document's basic section presents a view containing the most relevant connection properties for setting up a working connection. The Basic section allows the user to quickly setup a connection with only the most relevant properties required to successfully connect to the data source.

The Basic view also defines a set of dynamic hierarchy rules that drive a dynamic user interface based on the selected values for other properties. It can be used to indicate that a certain property is only valid if the parent property is set to a specific value. For example, the child property APIToken may only be visible when the parent property AuthScheme is set to the value of APIToken. 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 section presents a traditional configuration experience by including 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. This ordering is implicit in the Hierarchy JSON based on the order of definition within the JSON document.

Hierarchy Rules

Hierarchy rules define the relationship between a parent and a child property, which is an important aspect of the Basic view. 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 the below example on the parent property:

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

In hierarchyRules, when the parent property is set to value1, then the corresponding array will contain the valid list of child properties that can be set. An alternative set of child properties will be included if the parent property is set to value2 instead.

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

It is important to note that a single child property can appear in multiple places of the same hierarchy. For example, the OAuthClientId property 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 the child property sits. For example, in SharePoint, the AuthScheme property has 13 valid enumeration values when SharePointEdition = SharePoint Online, but only five when SharePointEdition = SharePoint OnPremise.

A sample project utilizing hierarchy in CData's JDBC drivers can be found on GitHub.

Retrieving the Hierarchy Definition

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

  1. Open a configuration-only connection. Using CData's JDBC Drivers, this connection can be opened without specifying any connection properties by using a JDBC URI prefix like the following jdbc:cdata:<source>:config: where <source> is the data source object name. For other editions, the property DriverConfigOnly can be set to True to open a configuration-only connection.
  2. Run the following query: 
    SELECT Definition FROM sys_connection_hierarchy WHERE Context = '_jdbc'
    . The value for Context will vary for non-JDBC editions.

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

Testing Connections

In addition to collecting properties from the user, there are several other items that should be considered when designing a connection UI. The first of which is testing for a valid connection. By default, CData connectors do not validate a connection when the connection object is created. To test for a valid connection, the application can create a connection object with an additional property ConnectOnOpen set to True. When this property is set, the connector will invoke an internal TestConnection() method. If this method fails, an exception will be thrown with details on the cause of the failure. We recommend providing a Test Connection element in your connection UI to allow users to confirm that their credentials are correct before continuing.

Configuring Logging

Logging in the driver is primarily managed by two properties: Logfile (which controls where the log is written) and Verbosity (which controls the amount of detail that is logged). These properties are set at the connection level in the CData connectors. To assist with troubleshooting, CData recommends providing a method for end users to configure verbosity differently for individual jobs. Logging is critical to the support process and some issues may not be resolvable without the ability to generate logs.

More information on logging can be found in the driver's documentation here: https://cdn.cdata.com/help/RFM/jdbc/pg_advancedlogging.htm

OAuth 2.0 Authorization Code Flow

The Authorization Code flow is a common authentication mechanism used by the sources that CData supports. This flow is unique in that it requires the end user to be redirected to the source system to login in order to establish the initial connection. CData connectors provide a standardized mechanism to implement this flow, which is covered further in the Embedded OAuth documentation.

In this article

Related articles