1. Documentation
  2. Custom Extensions
  3. Custom SQL Functions

Custom SQL Functions

When learning to create custom configuration in LogViewPlus it is helpful to begin with the sample code projects.  This tutorial will assume that you have downloaded and run the sample projects successfully. Please see running the samples for more information.

Starting with LogViewPlus 3.0.16, our SQL engine gives you the ability to write your own SQL functions.  When used in conjunction with Dashboard Reports this can be an extremely powerful for custom log file analysis.

To write a custom SQL function, you must extend the ISqlFunction interface which provides a function definition as well as the underlying execution method. 

public interface ISqlFunction
{
     string Name { get; }
     Type[] ParameterTypes { get; }
     Type ReturnType { get; }
     object Execute(object[] parameters);
     bool CacheResult { get; }
}

If we look at the code included in the CustomSqlFunction there is a file called IndexOf.cs which provides a simple INDEXOF function implementation which enhances the standard CHARINDEX function by adding case sensitivity.  The implementation provided here is very similar to the IndexOf implementation found in the .Net framework.  If we remove all of the comments, we can see the code is relatively straight forward. 

public class IndexOf : ISqlFunction
{
     public string Name => "INDEXOF";
     public Type[] ParameterTypes => new[] {typeof(string), typeof(string), typeof(int), typeof(bool)};
     public Type ReturnType => typeof(int);
     public bool CacheResult => false;
     public object Execute(object[] parameters)
     {
          // The number and type of parameters will have already been verified.  So we can just do...
          //
          var source = (string)parameters[0];
          var find = (string)parameters[1];
          var startPos = (int)parameters[2];
          var caseSensitivity = ((bool) parameters[3]) ?
                                    StringComparison.Ordinal : StringComparison.OrdinalIgnoreCase;

          return source.IndexOf(find, startPos, caseSensitivity); // The SQL engine will assume an integer.
     }
}

Our implementation consists of four properties and one 'Execute' method.  Let's start by looking at the properties.

Property
Description
Name
The name of the function.  This will be the named used in the SQL statement.
ParameterTypes
The data types used by the function input.  This property will be responsible for data validation to ensure that the data received by the Execute method always valid.  Data type validation within the Execute method is therefore unnecessary.
ReturnType
The type of data returned by the function.  This is the same as the underlying type of data returned by the Execute method.
CacheResult
If your custom function is deterministic and slow, you may want to cache the data.  Rather than writing your own caching implementation, you can use the one provided by the SQL engine by setting this flag to true.  Caches will be keyed on all parameters provided into the function.

The Execute method is then responsible for method execution.  It receives an array of parameters where the data types have already been validated using the definition provided by the ParameterTypes property.  The underlying data returned by the Execute method should match the type defined in the ReturnType property.

This light weight function implementation makes it easy to extend the SQL engine to meet your needs.  If you have any questions or comments please feel free to contact us.

< >

Chapters

Topics