.NET query extensions

To create a .NET query extension,

Create a Class Library project in Visual Studio.
Add a reference to SkySparc.Platform.Plugin.dll in your OmniFi installation directory.
Add a class to the project and let it extend the abstract class SkySparc.Platform.Plugin.NET.ACustomQueryExtension.
Implement the abstract methods om ACustomQueryExtension.

Constructor

public class MyDotNetQueryExtensions : ACustomQueryExtension
{
    public MyDotNetQueryExtensions(string System, string UserId, string Password)
            : base(System, UserId, Password)
    {
    }
}

The constructor is required, and accepts the system, user, and password. The user and password are credentials mapped in the OmniFi Administration application.

GetParametersImpl

Extensions that require some form of user input can define parameters. The return value is a List<Parameter>.

protected override List<Plugin_Parameter> GetParametersImpl()
{
    return (new List<Plugin_Parameter>
    {
        new Plugin_Parameter
        {
            Type = Plugin_DataType.INTEGER,
            Name = "count",
            Description = "Count",
            Mandatory = true,
            DefaultValue = new Plugin_Value(11),
            Comment = "Number of records to generate",
            MultiSelect = false,
            Operators = null
        }
    });            
}

Default value

You can provide a default value in the form of a typed Plugin_Value . The Plugin_Value class ensures type safety in communication between OmniFi and the extension by including data type information.

Parameter domains

Domains allow parameter input to be restricted to a certain subset of discrete values. For example, a yield curve gap set could be described as a set of discrete values [O/N | SPOT | 1W | 1M | 3M | 1Y …]. To provide the user with a convenient drop-down you can use the parameter domain feature. To provide a domain for a parameter, set the Domain property on the Plugin_Parameter to a List<Plugin_DomainItems>.

protected override List<Plugin_Parameter> GetParametersImpl()
{
    return (new List<Plugin_Parameter>
    {
        new Plugin_Parameter
        {
            Type = Plugin_DataType.INTEGER,
            Name = "count",
            Description = "Count",
            Mandatory = true,
            DefaultValue = new Plugin_Value(11),
            Comment = "Number of records to generate",
            Domain = new List<Plugin_DomainItem>
            {
                new Plugin_DomainItem
                {
                    DisplayMember = "First",
                    ValueMember = new Plugin_Value(1)
                },
                new Plugin_DomainItem
                {
                    DisplayMember = "Second",
                    ValueMember = new Plugin_Value(2)
                },
            }
        }
    });            
}

Multi-Select parameters

Multi-select can be enabled for string domain parameters, and allows the user to select multiple items from the domain.

Multi-select values are sent to the extension as a CSV encoded string, where each element is one selected item, e.g. OPTION_A,OPTION_B.

Items that themselves contain quotation characters or list separators are CSV encoded, e.g. "value,1","value""2".

Parameter operators

A parameter can be associated with a set of operators. OmniFi doesn't have support for any particular operators, instead you define a set of string options that the user can choose from, e.g. `["=", ">", "<"]. The extension is responsible for implementing the operator logic, allowing you to define any operators that make sense in your context.

new Plugin_Parameter
{
    Name = "period_length",
    Description = "Period Length",
    Type = Plugin_DataType.INTEGER,
    Mandatory = true,
    Operators = new List<string> {  "+", "-" }
}

The selected operator is provided to GetMetadata() and ExecuteQuery() with the parameter argument.

protected override Plugin_QueryResult ExecuteQueryImpl(List<Plugin_Argument> Parameters, Plugin_ColumnSchema ColumnSchema)
{
    var paramLookup = Parameters.ToDictionary((x) => x.ParameterName);
    
    var periodLength = paramLookup["period_length"].IntegerValue.Value;
    string periodOp = paramLookup["period_length"].Operator;

The query output is defined by the Plugin_MetadataCollection returned by GetMetadataImpl(). The metadata can define any number of entities.
To define an output column, simply add it to a Plugin_MetadataCollection. By default, the metadata is added to the main data set, however you can specify which data set to add the column to directly in the Add method:

protected override Plugin_MetadataCollection GetMetadataImpl(List<Plugin_Argument> Parameters)
{
    Plugin_MetadataCollection metadata = new Plugin_MetadataCollection();
    metadata.Add(
        new Plugin_Metadata
        {
            Type = Plugin_DataType.INTEGER,
            Name = "int",
            Description = "Integer"
        });
    metadata.Add(
        new Plugin_Metadata
        {
            Type = Plugin_DataType.STRING,
            Name = "str",
            Description = "String"
        });
    metadata.Add(
        new Plugin_Metadata
        {
            Type = Plugin_DataType.INTEGER,
            Name = "int",
            Description = "Integer"
        }, "SubEntity");
    metadata.Add(
        new Plugin_Metadata
        {
            Type = Plugin_DataType.STRING,
            Name = "str",
            Description = "String"
        }, "SubEntity");
    return (metadata);
}

📘
Note that the configured parameters are provided as arguments to the GetMetadataImpl method. It is recommended to avoid depending metadata on parameter input values where possible, as it makes parameterizing any queries in OmniFi Reporting much more difficult, but depending on the data source, it may be necessary.

ExecuteQueryImpl

Query execution is implemented by the ExecuteQueryImpl()? method. It accepts parameter and requested schema input, and returns a Plugin_QueryResult`.

protected override Plugin_QueryResult ExecuteQueryImpl(List<Plugin_Argument> Parameters, Plugin_ColumnSchema ColumnSchema)
{
  var paramLookup = Parameters.ToDictionary((x) => x.ParameterName);
  Plugin_Argument countValue;
  if (!paramLookup.TryGetValue("count", out countValue))
    throw new ApplicationException("Count parameter not provided");

  int count = countValue.IntegerValue ?? 10;
  Plugin_QueryResult result = new Plugin_QueryResult(
    new Plugin_Dataset(
      new Plugin_DataColumn("int", "Integer", Enumerable.Range(0, count)
                            .Select((x) => (int?)x).ToArray()),
      new Plugin_DataColumn("str", "String", Enumerable.Range(0, count)
                            .Select((x) => $"number {x}").ToArray())
    ),
    new Plugin_Dataset("SubEntity",
                       new Plugin_DataColumn("int", "Integer", Enumerable.Range(0, count)
                                             .Select((x) => (int?)x).ToArray()),
                       new Plugin_DataColumn("str", "String", Enumerable.Range(0, count)
                                             .Select((x) => $"number {x}").ToArray())
                      )
  );

  return (result);
}

Arguments

Input parameters are provided as a list of Plugin_Argument. The ParameterName property of Plugin_Argument is unique, and can be used to create a dictionary:

var paramLookup = Parameters.ToDictionary((x) => x.ParameterName);

Schema

The ColumnSchema argument defines the output the user has chosen to include in the query.

Utilizing the provided schema is optional. As long as your data matches your metadata (as returned by GetMetadata) precisely, OmniFi will arrange the query output to match the user configuration. However, for large volume scenarios, it is recommended you make use of the provided schema to minimize the data and improve performance.

Return value

The return value of ExecuteQueryImpl is a Plugin_QueryResult object, containing a dictionary of column-based data sets.

var result = new Plugin_QueryResult(
    new Plugin_Dataset(
        new Plugin_DataColumn("type_id", "Rate Type", data.Select(x => x.typeId).ToArray()),
        new Plugin_DataColumn("name_id", "Rate", data.Select(x => x.nameId).ToArray()),
        new Plugin_DataColumn("period_id", "Period", data.Select(x => x.periodId).ToArray()),
        new Plugin_DataColumn("bid", "Bid", data.Select(x => (double?)x.bid).ToArray()),
        new Plugin_DataColumn("ask", "Ask", data.Select(x => (double?)x.ask).ToArray()),
        new Plugin_DataColumn("rate_date", "Rate Date", data.Select(x => (DateTime?)x.date).ToArray())
    ));
return result;

Note that to create sub-entity tables, just create a named Dataset:

var result = new Plugin_QueryResult(
    new Plugin_Dataset("subentity",
        // ...
    ));
return result;