Menu
Simba Technologies
Simba Technologies

SimbaEngine X SDK 10.1.3
Developing Drivers for Data Stores Without SQL

SimbaEngine X SDK Documentation > SQL Engine Features > Custom Scalar and Aggregate Functions

Custom Scalar and Aggregate Functions

SimbaEngine X SDK version 9.3 introduced support for the creation of custom scalar and aggregate functions. To enable this functionality, the following changes were made to the SDK:

  • In the PSDataType enum, PS_DT_SCALARFN has been renamed to PS_DT_SCALARORAGGRFN.
  • In the PSNonTerminalType enum, PS_NT_SCALAR_FN has been renamed to PS_NT_SCALAR_OR_AGGR_FN, and PS_NT_CUSTOM_AGGR has been added. PS_NT_CUSTOM_AGGR is used when the parser knows unambiguously that a function is an aggregate function (i.e. a set quantifier ALL or DISTINCT has been specified).
  • Custom scalar functions are represented in the AETree as AECustomScalarFn nodes.
  • Custom aggregate functions are represented in the AETree with AECustomAggregateFn nodes.

Note:

Custom aggregate functions are not supported in Java.

To implement a custom scalar function in the C++ or Java SDK:

  1. In the C++ SDK, create a CustomerDSIExtScalarFunction class which subclasses DSIExtScalarFunction and represents your custom scalar function.
  2. Or, for the Java SDK, subclass CustomScalarFunction.

  3. Implement the methods defined by DSIExtScalarFunction or CustomScalarFunction, in particular the following:
    • Execute()

      Takes in a collection of input values and performs the execution of the scalar function.

    • RetrieveData()

      Used to retrieve the output from Execute().

    • Note:

      If you use Simba’s execution engine, Execute() will be called with the input arguments, and then RetrieveData()will be called to get the result. This happens at least once per row. If you do not plan to use Simba’s execution engine, then the implementation of both methods can remain empty or simply throw an exception since they will not be called by the SDK.

    • UpdateMetadata()
    • Called once during prepare and should compute preliminary metadata for the input arguments and return value of the scalar function. Note that this metadata could be inaccurate (e.g. due to parameters in the query), so the method should not throw any exceptions related to invalid argument types. The method will be called again at execution time, so you can update the metadata to the final input and output metadata, and throw an exception if the given input metadata is not valid for your scalar function.

      Note:

      The SQLEngine will attempt to convert any inputs into the specified argument types, so an exact match of types is not required.

  4. Override OpenScalarFunction() in your DSIExtSqlDataEngine derived class for C++, or SqlDataEnginederived class for Java. This method takes in the name of the scalar function to execute along with the arguments, and returns your CustomerDSIExtScalarFunction class. An exception can be thrown if the number of parameters doesn’t match that required by the scalar function.

To implement a custom aggregate function:

  1. Create a CustomerDSIExtAggregateFunction class which subclasses DSIExtAggregateFunction and represents your custom aggregate function.
  2. There are no Execute()/RetrieveData() functions since we currently do not support using custom aggregate functions in our execution engine. This means that if you use our execution engine, your DSII MUST handle the custom aggregate function during CQE. This limitation may be lifted in a future version of the SDK.

  3. Override OpenScalarFunction() in your DSIExtSqlDataEngine class (e.g. CustomerDSIIDataEngine) to return an instance of CustomerDSIExtAggregateFunction when provided with the aggregate function's name and correct number of arguments (currently only one parameter is supported).
  4. Note:

    Custom aggregate functions currently only support one parameter and are also not supported in the execution engine; they must be handled via CQE.

    The Codebase sample driver contains examples of custom scalar and aggregate functions. See CBDataEngine, CBAggrFnName, CBAggrFnum, CBScalarFnAdd, and CBScalarFnConcat. Note that the custom aggregate functions in this driver are only for illustration and cannot be executed as CQE handling has not been implemented for aggregate functions.

 

Related Links

How to Implement Custom SQL Scalar Functions in an ODBC Driver