Example

An example of a translation rule for function calls

Description

Let's create a translation rule class that will transform DB2 function calls of foo to snowflakeFoo and make some changes in its parameters. The rule will remove the first argument and will make an explicit cast to string in the remaining parameter. Here is an example of the input and output code.

-- DB2 input code
SELECT foo('valueNotNeeded', COL1) FROM T1;

-- Snowflake output code 
SELECT snowflakeFoo( COL1 :: STRING) FROM T1;

Now, let's take a look at the transformation rule code. As mentioned above, the class definition must have the ExportContract attribute and inherit from the BaseExtensibleTranslationRule class. Notice both parameters of the class definition are SqlFuntionExpr, that means that the rule will take a function call as the input and return a function call as the output.

[ExportContract(TransformationRulesContracts.Db2ToSnow)]
public class MyFirstExtensibilityRule : 
   BaseExtensibleTranslationRule<SqlFunctionExpr, SqlFunctionExpr>
{
   ...
}

Next, the IsApplicable method must make sure that only the desired functions are targeted in this translation rule. This is accomplished by asking for the name of the current function expression and making sure it is equal to foo.

protected override bool IsApplicable(SqlFunctionExpr node)
{
    var functionName = node.GetFunctionName();
    return functionName.Equals("foo");
}

More complex situations can be verified in the IsApplicable method, like checking for the amount or type of arguments, or values of internal fields of the AST. All of that can be verified by exploring the exposed types in the SnowConvertExtensibility API.

Screenshot of internal fields of the SqlFunctionExpr AST type

Now, the replace method will be called for each one of the instances of the foo function in the input code. The first argument of the function is removed with line 3. The remaining parameter is casted to string with line 4 and the name of the function is changed to snowflakeFoo with line 5.

protected override void Replace(SqlFunctionExpr node, out SqlFunctionExpr result)
{
    result = node.RemoveArgument(0);
    result = result.CastArgument(0, SfEmitter.StringType());
    result = result.RenameFunction("mySpecialFoo");
}

Highlights

There are a couple of important things to highlight in the Replace method code.

Immutable AST

As mentioned in the Working with ASTs section, the classes used to represent the ASTs (in this case SqlFunctionExpr) are immutable. Therefore, each one of the functions in lines 3-5 creates a new instance of the class that is assigned to the result parameter every time.

Emitting code

The concept of Emitting code is also mentioned in Working with ASTs section. When using the SnowConvertExtensibility API, creating new AST nodes or parts of them, cannot be accomplished via the traditional approach of calling the type constructor with the new keyword. Instead, an instance of an Emitter class must be used to emit new code. For example, each one of the functions called in lines 3-5 end up emitting a new SqlFunctionExpr.

In line 4, the SfEmitter object (part of the BaseExtensibleTranslationRule class) is used to produce new Snowflake ASTs. The CastArgument function takes the index of the parameter and the type to use in the casting, this type must be an instance of the SqlDataType class. The SfEmitter is used in this case to produce an instance of the SfStringType which represents an string in Snowflake.

Get the code

The full code of the example and some other code mentioned in previous sections can be found here.

75KB

TranslationRulesProject.zip

archive