RegisterCustomTable

(VTSSQLInterface library)

Description:	A launched module that registers a name for a virtual database table and defines what information will be available from that table.
Returns:	Varies. 0 if array size is invalid,
Usage:	Script Only.
Function Groups:	Database and Data Source, ODBC
Related to:	SQLQuery
Format:	\VTSSQLInterface\RegisterCustomTable(Name, Description, Columns, IsReadOnly, CallbackObj, SupportsTPP)
Parameters:

Name

Required. Any text value for the name of the table.

Description

Required. Any text value for the description of the table

Columns

Required. May be either a dictionary or a callback module.

If the first, then Columns will be a dictionary of structures that describing the column contained in the table. The dictionary keys should be the names of the columns. Use this option when the table will refer to a single tag or type of information where the column format is known.

If the second, then a callback context may be provided where the module SQLQueryGetColumnInfo is called to retrieve the column info. If the current module contains this submodule (as is usually the case) then you will use Self() for this parameter.
Use this option if the custom table may refer to multiple tag types, each having its own structure.

Further information is provided in the comments section.

IsReadOnly

Required numeric. Set to 1 for read only. Writes (value 0) are not currently supported through the SQL interface.

CallbackObj

Required. The object value of a module which must contain a submodule named SQLQueryRetrieveData. In most cases, this will be stored in the current file, in which case self() should be used for this parameter.

SQLQueryRetrieveData will be called by VTSSQLInterface\SQLQuery when it needs to retrieve data from the custom table. Further information is provided in the comments section.

SupportsTPP

Required Boolean. Set TRUE if the table has support for additional TPP tables. In this case SQLQueryRetrieveData will be called with a TPP value specified at query-time.

Comments:

Columns Parameter

If the Columns parameter is defined as a dictionary, each element must be a VTSSQLColumnInfo structure. This structure is defined as follows:

VTSSQLColumnInfo STRUCT [
                   SQLType                   { One of the SQL type constants     };
                   IsPrimaryKey              { 1 for primary key column, 0 if not                                 };
];

For example, VTScada uses the following code to define the columns when registering the table name, AlarmHistory:

    AlarmHistoryColumns = Dictionary(); 
  { Standard fields that will always exist for the alarm history table }

AlarmHistoryColumns["Timestamp"] = ColumnInfo(\#SQL_DATETIME, 1, "Timestamp"
AlarmHistoryColumns["Id"]        = ColumnInfo(\#SQL_VARCHAR,  0, "Name");
AlarmHistoryColumns["Name"]      = ColumnInfo(\#SQL_VARCHAR,  0, "Name");
AlarmHistoryColumns["SubName"]   = ColumnInfo(\#SQL_VARCHAR,  0, "Name");
AlarmHistoryColumns["Event"]     = ColumnInfo(\#SQL_VARCHAR,  0);

If using a callback object for the Columns parameter, your code must include a module named SQLQueryGetColumnInfo. This subroutine will be called by the SQLQuery module, and must return a dictionary of VTSSQLColumnInfo structures, keyed by column name, for each column that to be queried in the given custom table.

RawTableName

Required. Any text value for the custom table name without :TPP modifiers.

SplitColumnName

Optional text. If specified, the returned dictionary is only guaranteed to return that column (if it exists), but may contain other columns as well. This allows for performance optimization for custom tables that contain large numbers of columns.

SQLQueryGetColumnInfo will normally start with code to strip off any prefix or delimiter from the table name, then obtain the object value of the tag type:

{ code to clean up the name followed by...}
TagModuleVal = Scope(Code, CleanTagTypeName );

This will be followed by a call to ListVars to obtain the variables in the specified tag object, then a loop to add each required variable to the dictionary of VTSSQLColumnInfo structures.

CallbackObj Parameter: SQLQueryRetrieveData

SQLQueryRetrieveData is a launched module that must be implemented on the CallbackObj. Note that zero is the proper return if the array size is zero.

Its parameters are as follows:

PtrResult

Required. A pointer to the variable, within which the results will be returned.

The returned results must be a 2-dimensional array (even if only one column is returned), indexed in the form: Result[Col][Row]. This variable should not be set until the results are complete.

TableName

Required. The name of the table being queried. This is required in case the caller decides to register more than one table with the same CallbackObj.

RequestedColumns

Required. An array of the names of the columns needed to satisfy the SQL query.

MaxRecords

Required numeric. The maximum number of result rows that this module is allowed to return.

StartTime

Optional. If the WHERE clause of the query resolves to a starting timestamp, it will be passed here, otherwise Invalid.

EndTime

Optional If the WHERE clause of the query resolves to an ending timestamp, it will be passed here, otherwise Invalid.

TPP

Optional. The time period per row to apply to the query.

FilterHints

Optional. A dictionary containing structured entries for each field in the WHERE clause that can be filtered to one or more ranges or exact values.

The structure definition follows:

HintStruct STRUCT [
  Ranges;           { Array of RangeDescriptors, field value must fall into one or more of the ranges }
  ExactMatches;     { Array of values that field value must match one of. 
                      Only valid if all Ranges have min=max }
  OverallMin;       { Overall minimum for the field value, may be Invalid }
  OverallMax;       { Overall maximum for the field value, may be Invalid }
];

In turn, a RangeDescriptor is a simple structure to contain a min/max range. No distinction is made between inclusive and exclusive ranges. One of min or max may be Invalid, indicating an open-ended range, but not both. For queries that are exact matches, Min and Max will be identical.

RangeDescriptor STRUCT [
  Minimum;
  Maximum;
];

SQLQueryRetrieveData may ignore any or all of the information received in FilterHints. A simple implementation might use only OverallMin and OverallMax, or just the ExactMatches array. More advanced implementations could use the complete information from the Ranges. Note that ExactMatches, OverallMin, and OverallMax are all derivable from Ranges. They are derived by ParseWhereClause itself for convenience of the consumer.

SQLQuery itself will always apply a post-filter derived from the WHERE expression to filter out any rows that do not match the expression. So SQLQueryRetrieveData need only filter out some of the rows suggested by FilterHints. Filtering out not enough rows will not result in incorrect results returned from the query. (This is also why no distinction is made between inclusive and exclusive ranges in FilterHints. If the range was meant to be exclusive in the original WHERE clause it will be post-filtered correctly.)

Example: for the WHERE clause:

... WHERE WellKey="MyWell1" AND Timestamp >= '2015-06-01' AND Timestamp < '2015-07-01'

the dictionary passed to SQLQueryRetrieveData will contain two keys: WellKey and Timestamp.

WellKey's value is a HintStruct containing the following elements:

Ranges - In this example the Ranges array would contain one element of the Range structure with Minimum="MyWell1" and Maximum="MyWell1".
ExactMatches - In this example ExactMatches array would contain one element set to "MyWell1".
OverallMin - In this example would be set to "MyWell1".
OverallMax - In this example would be set to "MyWell1".

Timestamp's value is a HintStruct containing these elements, where the timestamps use the VTScada timestamp type.

Ranges - Array(Range('2015-06-01 00:00:00', '2015-07-01 00:00:00' ))
ExactMatches - Invalid
OverallMin - '2015-06-01 00:00:00' -- Note that if the Timestamp is set to be the primary key on this custom table, this same timestamp value will be passed as StartTime to SQLQueryRetrieveData.
OverallMax - '2015-07-01 00:00:00' -- Note that if the Timestamp is set to be the primary key on this custom table, this same timestamp value will be passed as EndTime to SQLQueryRetrieveData.

OrderByHints

Object that indicates whether to sort the result in ascending or descending order. Defaults to chronological order.

Related information:

VTScada SQLInterface Module