This example will show how to create a data function that creates a table suitable to be used to model a many-to-many relationship. First as an example imagine that we have a data set where each row have an identifier and a free-form text column that contains references to another table.
id;text
1;Relates to A123 and A432
2;Some text.
3;Relates to A534.
4;Relates to A123,A431.
5;A342,A001,A912
The other table that the A followed by number parts refer to looks like this:
id;value
A123;0.123
A001;1.439
A432;7.213
A433;8.123
A534;8.121
A431;7.231
A912;10.123
Now we want to setup a relation between these two table which is hard since they are only referenced by using free-form text. So we create a data function which takes one id-column, one-value column and a regular expression that specifies how to match parts in the value column. The result will be a table that contains one row for each match with the regular expression combined with the id for that match. If we do this on the first example table and use the “id” column as the identifier and the “text” column as the value column combined with the regular expression “(A[0-9]{3})” we will get the following result.
Identifier;Value
1;A123
1;A432
3;A534
4;A123
4;A431
5;A342
5;A001
5;A912
Since this was made using data functions we used the standard data functions UI perform the configuration.
This code is very similar to the example shown in a previous article that showed how to retrieve images from an URL, see that article for more motivations on why all these components are needed. First we create that function executor which performs the algorithm.
namespace TableFromRegex
{
using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using Spotfire.Dxp.Application.Extension;
using Spotfire.Dxp.Data;
using Spotfire.Dxp.Data.Collections;
/// <summary>
/// Function executor for creating a relation table from a regular expression.
/// </summary>
public class RelationTableFromRegexExecutor : CustomDataFunctionExecutor
{
/// <summary>
/// Execute a function invocation. The executor is expected to add the output results to the invocation
/// object.
/// </summary>
/// <param name="invocation">The function invocation.</param>
/// <returns>
/// The prompt models to return, the implementor is expected to use the yield return pattern.
/// </returns>
protected override IEnumerable<object> ExecuteFunctionCore(Spotfire.Dxp.Data.DataFunctions.DataFunctionInvocation invocation)
{
DataRowReader regexReader = invocation.GetInput("RegEx");
DataValueCursor<string> regexCursor = regexReader.Columns[0].GetFormattedCursor();
if (!regexReader.MoveNext() || !regexCursor.IsCurrentValueValid)
{
throw new InvalidOperationException("No values specified for regex.");
}
Regex regex = new Regex(regexCursor.CurrentValue, RegexOptions.Compiled);
DataRowReader idReader = invocation.GetInput("IdColumn");
DataRowReader valueReader = invocation.GetInput("ValueColumn");
PageableListSettings settings = new PageableListSettings();
settings.CanReplaceValue = false;
PageableList<string> idResult = new PageableList<string>(settings);
PageableList<string> valueResult = new PageableList<string>(settings);
DataValueCursor<string> idCursor = idReader.Columns[0].GetFormattedCursor();
DataValueCursor<string> valueCursor = valueReader.Columns[0].GetFormattedCursor();
while (idReader.MoveNext() && valueReader.MoveNext())
{
if (!valueCursor.IsCurrentValueValid)
{
continue;
}
string value = valueCursor.CurrentValue;
foreach (Match match in regex.Matches(value))
{
idResult.Add(idCursor.CurrentValue);
valueResult.Add(match.Groups[match.Groups.Count - 1].Value);
}
}
invocation.SetOutput("RelationTable", new ResultReader(idResult, valueResult));
yield break;
}
/// <summary>
/// Simple reader to return the result.
/// </summary>
private class ResultReader : CustomDataRowReader
{
/// <summary>
/// The list of the identifiers.
/// </summary>
private readonly PageableList<string> idList;
/// <summary>
/// The list of the values.
/// </summary>
private readonly PageableList<string> valueList;
/// <summary>
/// The cursor to assign the result for the current row.
/// </summary>
private readonly MutableValueCursor<string> idCursor;
/// <summary>
/// The cursor to assign the result for the current row.
/// </summary>
private readonly MutableValueCursor<string> valueCursor;
/// <summary>
/// The columns returned by the reader.
/// </summary>
private readonly List<DataRowReaderColumn> columns;
/// <summary>
/// The current index to return.
/// </summary>
private int index = -1;
/// <summary>
/// Initializes a new instance of the <see cref="ResultReader"/> class.
/// </summary>
/// <param name="idList">The identifiers list.</param>
/// <param name="valueList">The values list.</param>
public ResultReader(PageableList<string> idList, PageableList<string> valueList)
{
this.idList = idList;
this.valueList = valueList;
this.columns = new List<DataRowReaderColumn>();
this.idCursor = (MutableValueCursor<string>)DataValueCursor.CreateMutableCursor(DataType.String);
this.valueCursor = (MutableValueCursor<string>)DataValueCursor.CreateMutableCursor(DataType.String);
// Create the metadata for the put column.
DataRowReaderColumn idColumn =
new DataRowReaderColumn(
"Identifier",
DataType.String,
this.idCursor);
this.columns.Add(idColumn);
DataRowReaderColumn valueColumn =
new DataRowReaderColumn(
"Value",
DataType.String,
this.valueCursor);
this.columns.Add(valueColumn);
}
/// <summary>
/// The implementor should provide the result properties.
/// </summary>
/// <returns>The result properties.</returns>
/// <remarks>
/// This method is only called once.
/// </remarks>
protected override ResultProperties GetResultPropertiesCore()
{
// Table metadata can be returned here, but we have nothing to return for now.
return new ResultProperties();
}
/// <summary>
/// The implementor should implement this method to reset the
/// enumerator. If this method is called then the <see cref="M:Spotfire.Dxp.Data.DataRowReader.MoveNextCore"/>
/// method should return the first row again when called the next time.
/// </summary>
protected override void ResetCore()
{
this.index = -1;
}
/// <summary>
/// The implementor should provide a list of <see cref="T:Spotfire.Dxp.Data.DataRowReaderColumn"/>s that it
/// returns.
/// </summary>
/// <returns>The metadata of the columns that are returned by the reader.</returns>
/// <remarks>
/// This method is only called once.
/// </remarks>
protected override IEnumerable<DataRowReaderColumn> GetColumnsCore()
{
return this.columns;
}
/// <summary>
/// Advance to the next row.
/// The implementor should update all <see cref="T:Spotfire.Dxp.Data.DataValueCursor"/>s in the <see cref="T:Spotfire.Dxp.Data.DataRowReaderColumn"/>s
/// with values for the next row.
/// </summary>
/// <returns>
/// <c>true</c> if there are more rows; otherwise <c>false</c>.
/// </returns>
protected override bool MoveNextCore()
{
this.index++;
if (this.index >= this.valueList.Count)
{
// We are done, past the final row.
return false;
}
// Get the text for the current row and assign to the cursor.
string text = this.idList[this.index];
if (string.IsNullOrEmpty(text))
{
this.idCursor.MutableDataValue.SetNullValue();
}
else
{
this.idCursor.MutableDataValue.ValidValue = text;
}
text = this.valueList[this.index];
if (string.IsNullOrEmpty(text))
{
this.valueCursor.MutableDataValue.SetNullValue();
}
else
{
this.valueCursor.MutableDataValue.ValidValue = text;
}
return true;
}
}
}
}
We also need a type identifier:
namespace TableFromRegex
{
using Spotfire.Dxp.Application.Extension;
/// <summary>
/// Type identifiers for the image function exectuor.
/// </summary>
public class TypeIdentifiers : CustomTypeIdentifiers
{
/// <summary>
/// The table from regex identifier.
/// </summary>
public static CustomTypeIdentifier TableFromRegex = CreateTypeIdentifier("TableFromRegex", "Table from regex.", "Creates a relation table from a regular expression.");
}
}
Then we create a simple tool to register the function definition in the library in the same way as for the image from URL example:
namespace TableFromRegex
{
using Spotfire.Dxp.Application;
using Spotfire.Dxp.Application.Extension;
using Spotfire.Dxp.Data.DataFunctions;
using Spotfire.Dxp.Framework.Library;
/// <summary>
/// One-off tool to create and save a function definition in the library.
/// Assumes that there is an registertable folder in the library.
/// </summary>
public class RegisterTableTool : CustomTool<AnalysisApplication>
{
/// <summary>
/// Initializes a new instance of the <see cref="RegisterTableTool"/> class.
/// </summary>
public RegisterTableTool()
: base("Register table from regex tool.")
{
}
/// <summary>
/// Implement this method to perform the tool-specific logic.
/// </summary>
/// <param name="context">The context of the tool. Is never <c>null</c>.</param>
/// <remarks>Note that this method is only called if
/// <paramref name="context"/> is not <c>null</c> and
/// <see cref="M:Spotfire.Dxp.Application.Tool`1.IsEnabled(`0)"/>
/// returns <c>true</c>.</remarks>
protected override void ExecuteCore(AnalysisApplication context)
{
// Create the function definition builder.
DataFunctionDefinitionBuilder builder =
new DataFunctionDefinitionBuilder("Table from RegEx", TypeIdentifiers.TableFromRegex);
InputParameterBuilder inputBuilder = new InputParameterBuilder("IdColumn", ParameterType.Column);
builder.InputParameters.Add(inputBuilder.Build());
inputBuilder = new InputParameterBuilder("ValueColumn", ParameterType.Column);
builder.InputParameters.Add(inputBuilder.Build());
inputBuilder = new InputParameterBuilder("RegEx", ParameterType.Value);
builder.InputParameters.Add(inputBuilder.Build());
// One output parameter called image which is a column.
OutputParameterBuilder outputBuilder = new OutputParameterBuilder("RelationTable", ParameterType.Table);
builder.OutputParameters.Add(outputBuilder.Build());
DataFunctionDefinition functionDefinition = builder.Build();
LibraryManager lm = context.GetService<LibraryManager>();
// Find the folder where it should be stored.
LibraryItem li = lm.Search("registertable")[0];
// Save the function definition in the library.
DataFunctionDefinition saved;
functionDefinition.SaveAs(li, functionDefinition.FunctionName, new string[] { }, out saved);
}
}
}
Finally we just need to register the executor and tool in the add-in:
namespace TableFromRegex
{
using Spotfire.Dxp.Application.Extension;
/// <summary>Addin registration.
/// </summary>
public sealed class CustomAddIn : AddIn
{
/// <summary>
/// Override this method to register data function executors with the application.
/// </summary>
/// <param name="registrar">Object responsible for registering new data function executors.</param>
protected override void RegisterDataFunctionExecutors(AddIn.DataFunctionExecutorRegistrar registrar)
{
base.RegisterDataFunctionExecutors(registrar);
registrar.Register(TypeIdentifiers.TableFromRegex, new RelationTableFromRegexExecutor());
}
/// <summary>
/// Override this method to extend the application with new tools.
/// Each new tool should inherit from <see cref="T:Spotfire.Dxp.Application.Extension.CustomTool`1"/>,
/// and is then registered with the <see cref="T:Spotfire.Dxp.Application.Extension.AddIn.ToolRegistrar"/>.
/// </summary>
/// <param name="registrar">Object responsible for registering new tools.</param>
protected override void RegisterTools(AddIn.ToolRegistrar registrar)
{
base.RegisterTools(registrar);
registrar.Register(new RegisterTableTool());
}
}
}