How do I create a Definition for a database-type Data Source? – Flow Support

This article will be updated in the near future! In the meanwhile, if the following information is insufficient, contact support@flow-software.com.

A custom namespace definition file defines the queries required to convert SQL data tables and rows into a folder-and-tag-like namespace. This feature makes it easier and quicker for users to create Flow tags, measures and events using simple drag 'n drop operations. This process will generate the necessary retrieval queries automatically based on the concept of placeholders in the definition file.

The custom namespace definition is a Json file that defines the SQL queries required to build the folder structure and the item (i.e. tag) properties, including their retrieval queries.

If no definition file is provided, or the Json cannot be parsed, the Data Source connector will provide a view of the database tables, views and stored procedures.

Definition file

The definition file consists of the following:

"paths"	Root object that contains an array of "path" and "items" objects.
"path"	SQL query that returns one or more folder path strings. The query must return a Path field. Folders in the Path field can be delimited using the \\ separator (e.g. 'ERP\\Cost') Additional fields can be returned as required for filtering subsequent "item" queries (e.g. the ExampleColumn shown is used later as a placeholder)
"items"	Array of "item" and "properties" objects.
"item"	SQL query that returns the items (i.e. tags) for a specific path (i.e. folder). The query must return Name, Description, UOM, and Type fields. The query can refer to the results of the path query using placeholder syntax (e.g. [ExampleColumn]) Type must be one of "Analog", "Boolean" or "String"
"properties"	Array of item specific properties.
"measureQuery"	This is the SQL query that will be used to retrieve raw or aggregated values by the Flow engine. The query must return a Value field The query can return a Quality field If the query is used for Aggregation = None, or for a Flow tag, then the query must return a Timestamp field Use standard placeholders where required (e.g. [PeriodStart], [PeriodEnd]) Use placeholder syntax to refer to the results of the item query (e.g. [Name])
"measureAggregation"	One of "None", "Sum", "Average", "Minimum", "Maximum", "Counter", "Range", "Delta", "First", "Last", "Count", "Time in State", "Variance", or "Standard Deviation"

Path Query

The path query specifies the file structure of your namespace. These queries can also be defined as an array of queries in the JSON file if you need to generate more than one folder structure for different items. There are some rules that must be adhered to for the query to build a namespace successfully:

The query must return a column specifying the path of an item. The column must be called “path”
Levels of derivation in your folder structure is separated in the result of the path query by the” \\” characters.
The query can return other field to be used in subsequent queries
It must return a link to resolve your items to the folder structure you created.

It is good practice to order your query results by the resolving path.

Item Query

The item query specifies the detail of your item and can include items such as unit of measure, description and the data type of the item. The query must resolve the item to only one path structure. These queries can also be defined as an array of queries in the JSON file if you need to generate more than one item type per path.

There are some rules that must be adhered to for the query in order to build add an item successfully:

The special characters “[*,”,”*]” are deemed as placeholders. The resulting row value from previous queries will be replaced within the query.
The query must return the values in the following columns:

Name	Required. The name of the item.
Description	Optional. The description of the item.
UOM	Optional. The unit of measure of the item.
Type	Optional. The data type of the item. By default, the “Analog” type will be selected if nothing is specified.

Valid types include:

Analog
String
Boolean

Item Value Queries

The item value query specifies the query that will be executed by the Flow engine to return the actual values of the item for a specific time period. The values can either be aggregated by the query, or it could be the raw values and the Flow engine will do the aggregation method selected in the properties section of the file.

There are some rules that must be adhered to for the query to get item values successfully:

The special characters “[*,”,”*]” are deemed as placeholders. The resulting row value from previous queries will be replaced within the query
The query must return the values in the flowing columns:

Value	The actual item value for a specific timestamp.
Quality	Quality data representing the quality data of the item. If no quality data is available, hardcode this field to 192.
Timestamp	Timestamp of the data. This is required if the Flow engine needs to perform an aggregation method on the data.
Time Delimiters	[PeriodStart], PeriodEnd]. These placeholders will be replaced by the Flow engine with the correct time period based on the retrieval that must be performed.

Item Properties

Other properties that can be defined, but are optional include:

Measure Aggregation – Aggregation method to be applied if the raw data is returned by the item value query (result must include a timestamp field).

Valid values include:

Measure Rollover	Applicable if the Counter retrieval style is selected.
Measure Deadband	Applicable if the Counter retrieval style is selected.
Measure State Comparator	Applicable if the Time in State retrieval style is selected.
Measure State	Applicable if the Time in State retrieval style is selected.