Along with fixes for reported bugs, there is going to be a significant new feature included, dynamic functions.
Contents
Dynamic functions
So what is a dynamic function?
It is a script like based mathematic function as opposed to the native built in functions like SIN, COS, COALESCE etc. or any of your own custom native functions.
A number of dynamic functions can be collected in a function library, and any number of function libraries can be registered.
In case a function is declared in two different function libraries, the one that is registered last, is taking precedence, in reality overriding the original function.
Since dynamic functions can call both other dynamic and native functions, you can this way “override” the meaning of centrally used function with some other meaning, which could be a formula for calculating price for something based on various factors, that may be different from customer to customer or contract to contract etc. in case you were creating a customer or contract based application, that needed to do price calculations.
So how does dynamic functions look like?
mysum(a,b) = a + b
mysine(a) = sin(a)
myselection(x,y) =
if(x<y,
10,
20)
mycomplex(x,y) = myselection(x,y)*mysum(x,y)
These are examples of dynamic functions, defined with a name, a number of named arguments and the expression, which uses those named arguments.
A line can start with a # to define a comment.
The first one will work for both number and string arguments, the next one only number argument. The next one, which contains a condition (via the IF statement), is shown split up in multiple lines. That is perfectly legal, as long as the lines of the expression is indented by at least one space.
Function names can not be indented but must start at start of line.
CASE variable/constant WHEN expression THEN expression … ELSE expression END
Example: CASE a WHEN 10 THEN 100 WHEN 20 THEN 130 ELSE 140 END
CASE (expression) WHEN expression THEN expression … ELSE expression END
Example: CASE (a+b) WHEN 10 THEN 100 WHEN 20 THEN 130 ELSE 140 END
CASE WHEN expression THEN expression … ELSE expression END
Example: CASE WHEN a<b THEN -1 WHEN a>b THEN 1 ELSE 0 END
BETWEEN expression AND expression
Example (returns a boolean true/false or NULL if any arguments are NULL): a BETWEEN 10 AND 20
LIKE stringexpression
Example: a LIKE “%some?alue%”
IN
Example (returns boolean true, false or NULL): a IN (10,20,40)
IS NULL, IS NOT NULL, IS TRUE, IS NOT TRUE, IS FALSE, IS NOT FALSE
Example (returns boolean true, false or NULL): a IS NULL
Classes
There exists a number of classes which one can use to evaluate expressions, and all of them will now accept sets libraries containing functions.
TkbmMemSQL
TkbmMemSQL is one class that contain features to evaluate expressions, either as part of its SQL operation, or as part of its additional Calculate and Evaluate methods. Both Calculate and Evaluate compiles a given expression, takes some arguments and executes the compiled expressionto finally return a result.
The difference between the two is that Calculate do NOT allow for conditional features, and thus only simple math calculations, except if those conditional features are used within a library, but the expression given to the Calculate statement must not contain any conditional features.
Evaluate on the other hand, allow for being fed a conditional expression.
Both methods default accepts only numeric processing, however that can be overruled by setting the AOptions argument to not include seoOnlyNumericExpressions which is otherwise included by default.
TkbmCalculator
TkbmCalculator is a simple and easy way to take advantage of the expression evaluator for non conditional calculations.
var
result:variant;
begin
// Simple calculation
result:=TkbmCalculator.Calc('sin(100)');
// Calculation with arguments
// Providing a=45, x=10 and y=20 to the expression.
result:=TkbmCalculator.Calc('sin(a)*x+y',['a','x','y'],[45,10,20])
end;
If the specific expression is complex and needs executed many times, it may be performance wise a good idea to precompile it and then use the precompiled variant like this:
var
c:TkbmCalculator;
result:variant;
begin
c:=TkbmCalculator.Create(nil);
try
c.Compile('sin(a)*x+y');
result:=c.Calculate(['a','x','y'],[45,10,20]);
... Many calculate statements with differing argument values.
finally
c.Free;
end;
end;
TkbmEvaluator
The TkbmEvaluator class is used in the same way as the TkbmCalculator class, except the methods are called Eval and Evaluate instead of Calc and Calculate. It allows for conditional expressions.
var
result:variant;
begin
result:=TkbmEvaluator.Eval('if(sin(100)<0.5,10,20)');
// Evaluation with arguments
// Providing a=45, x=10 and y=20 to the expression.
result:=TkbmEvaluator.Eval('if((sin(a)*x+y)<50,10,20)',['a','x','y'],[45,10,20])
end;
Making dynamic function libraries
When you have chosen which class you will want to use for your purpose, you can start to define your dynamic function libraries. It is really simple.
dvar
c:TkbmCalculator;
result:variant;
begin
c:=TkbmCalculator.Create(nil);
try
c.AddLibrary(
'mysum(a,b) = a + b'#10#13+
'mysine(a) = sin(a)'#10#13+
'myselection(x,y) = if(x<y,10,20)'#10#13+
'mycomplex(x,y) = myselection(x,y)*mysum(x,y)',
'Base');
c.Compile('mycomplex(x,y)');
result:=c.Calculate(['x','y'],[10,20]);
... Many calculate statements with differing argument values.
finally
c.Free;
end;
end;
Loops and iterations
In addition to having semi simple functions, and calculations, you can even add loops. The upcoming release contains new for and while loops in addition to a couple of neat helper functions for summing data in the loop, and to define variable values.
Example:
SumNum(a,b) = for(a,a+4,inc(b,c,OUT b), OUT c)
We have defined a dynamic function (SumNum) taking two arguments, a start index (a) and a start sum (b).
If we run it like this:
var eval:TkbmEvaluator; begin eval:=TkbmEvaluator.Create(nil); try eval.AddLibrary(‘SumNum(a,b) = for(a,a+4,inc(b,c,OUT b), OUT c)’,’mylib’); ShowMessage(‘SumNum(10,100)=’+VarToStr(eval.Evaluate(‘SumNum(10,100)’)); finally eval.Free; end; end;
It will result in the sum of the values 100 + 10,11,12,13,14 = 160
The For statement takes 3 or 4 arguments:
first index value
last index value
the statement to be executed on each loop
(optional) the name of an OUT variable. If provided the For statement will put the current index value into this variable (in this case the variable c), which we then use later in the embedded expression.
Why 100 you ask? Well… we need to define a value for all variables before they are used. In this case the value of a is coming from the call, c comes from the for statement, outputting current index value (10,11,12,13,14) before the expression in the for statement is executed, but the initial value for b is not defined anywhere, unless we provide it as an argument to the call.
The expression: inc(b, c, OUT b) is executed 5 times, incrementing the variable b with the contents of the variable c, outputting the result in the variable b again, in effect summing the values of the iteration counter.
Let us look at another example:
Product(a) = def(1,b,for(1,a,mul(b,c,OUT b), OUT c))
The function Product is in Danish called Fakultet, and is in mathematical way written as an exclamation mark. Eg. 10! means calculate 1*2*3*4*5*6*7*8*9*10 and that is exactly what this dynamic function does.
In this example I have introduced a new Def function. The Def function provides a way to set a variable value before use, and then execute an expression.
In this case we start out with setting b=1, so it has an initial value for the mul function which operates similar to the inc function shown above.
Hence by using the Def function, we can drop the additional b argument of the function call, since we now define a static value for the variable, and will not get an execution error about variable b not being known. The Def statement returns the value of the expression executed.
Finally we have the new While function.
It will evaluate an expression, and while that expression is true, it will continue to loop. The number of loops can be limited by an optional count. The number of times the loop has run can be output to an OUT variable. The last value of the expression is returned as result from the While statement.
Simple example:
Loop(a,b) = while(a<b,inc(a,1))
This statement simply loops a number of times while the expression a<b is true.
If you want to limit the number of loops (perhaps as a failsafe), you can add a number:
Loop(a,b) = while(10,a<b,inc(a,1))
Then the loop will run while a<b, but maximum 10 iterations.
If you want access to the number of loops, use the OUT variable:
The order of the registration of library functions determines which function is called, if the same function name occurs multiple times in different libraries.
The duplication function in the latest loaded library will always override a matching function name in a previously loaded library.
This way you can create a base library, and augment its functions with variations which may be dependent on for example which customer or order or item you are working with in your end user application.
Even built in native functions can be overridden by a dynamic function.
