Expressions and predicates
A predicate is a condition that will be tested for each row of the result-set. If this condition is positively evaluated then the row will validate the predicate else not. Constraints such as all-rows or no-rows will check that respectively all and no rows are validating the predicate.
content of cells for each row
For this kind of test, you’ll assert the value of one cell (or a combination of cells) of each row with a predicate (see bellow). This cell is named the operand and must be specified to the predicate by the means of the operand attribute. Before version 1.17, this attribute was named name, this notation is now deprecated.
To identify a column, you can refer to it by its name but you’ve more options:
- By its index: To apply this strategy, use the syntax #3 where the number 3 identifies the fourth column (index equal to 3). The # specifies to NBi that you’ll be using a column index.
- By its name: To apply this strategy, just use the name of column. If this name is containing a space or is starting by a figure just surround it with square brackets. For example the following syntax identifies [1 col] a column name ‘1 col’.
- By an alias: To apply this strategy you must use the element alias (before the predicate) and specify a column index with the attribute column. The name of the alias is specified as the inner text of this element.
If the name of an alias is conflecting with the name of a column, the alias has the precedence.
Before version 1.16, only the third option was available and the element was named variable and not alias. The notation variable is now deprecated.
Using native transformations to adjust the value of a cell
You can define an expression to create a new virtual column to your result-set containing a slightly adjust cell’s value. The adjustement is defined with the help of native transformations.
In the example bellow, we’ve a dateTime value contained in the column named
myDateTime but we want to convert the value from UTC to local time (at Brussels), take the previous day, and finally set the time part of this date to 7.00AM.
Using two or more cells of the current row to create a new value
You can use an expression to combine two cells of the same row. For example, if you’ve two columns UnitPrice and Quantity, you can calculate the TotalPrice with an expression. To achieve this, you must define en element expression and set its formula. To reference a cell in your formula, use one of the three strategies above. You can also combine the content of the cells with fixed value. If you want to calculate a TotalPriceWithVAT based on the columns Quantity and UnitPrice, you can define an expression equal to UnitPrice * Quantity * 1.21
The functions supported in an expression are these supported by NCalc
In this kind of test, a cell can be used later in the predicate or in an expression. For each row, the value contained in the different columns will be assigned to the variable. If you want to assert that the column with index 1 is greater than 1000 then the first step is to create a variable for the column with a column-index equal to 1 and give it a name.
It’s possible to use an expression in an expression (nested expressions). The previous example could be written:
List of predicates
The predicate can be used with the previously defined assertions: no-rows, all-rows, some-rows and single-row. They supports many different operators, see the table here under for the full list.
In addition to this operator, you must also define the column or expression that you want to validate with this predicate. This indication is provided by identifying the column or the expression in the attribute name. Once again, you can use the three strategies described above to identify a column and for an expression, you can use its name.
As most predicates are valid for different types, you must specify the type of the column or expression that will be tested. By default the type is set to numeric but you configure it to any other type.
Each predicate is not valid for each data type. The list of possible combinaison is described here under.
|more-than||Yes||Yes||Yes||No||case-sensitive, reference, inline alternative|
|less-than||Yes||Yes||Yes||No||case-sensitive, reference, inline alternative|
|any-of (aka within-list)||Yes||No||No||No||reference|
Some of the predicates, require to specify a reference. For example if you want to check that the content of a column is equal to 1000 then your reference is
1000. This value must be specified in the inner text of the predicate element.
The predicate any-of is not expecting a unique reference but a list of items as the reference. Use the xml element item to delimitate each item that you want to put in your reference.
The predicates equal, more/less-than, starts/ends-with, contains, matches-regex and within-list are supporting two kinds of comparison for textual: case-sensitive (default) and case-insensitive. To change this behaviour adapat the value of the xml attribute ignore-case (default is false).
The following predicates are expecting a culture: matches-numeric, matches-date, matches-time. The culture is a group of 4 letters separated in two groups of two by the means of a dash. The list of valid cultures is defined at MSDN. If this culture is not provided then an invariant culture is applied with the pattern yyyy-MM-dd for the date format, HH:mm for the time format and a dot as decimal separator (no thousand seperator) for the numeric format.
The predicate modulo is expecting a second operand (the divisor) that you can specify in the attribute second-operand.
The two predicates more-than and less-than also supports the variant or-equal moreover the option empty supports the variant or-null.
Negation of a predicate
It could be useful to use the negation of a predicate. By specifying the attribute not available for each predicate, the result of the predicate’s evalution will be inverted (false will become true and true will become false).
In the example above, the test will succeed if at least one of the charachter of each Name is not a lower-case char.
Variables for predicate’s reference
Sometimes, the reference must be dynamic. One of the most famous examples is the need to check that all rows returned by the query are for the days before today. When you’re facing this kind of issues, you can use a variable. These items are described at the top of the test-suite and are dynamically evaluated. To reference them in the predicate you must use the name of the variable prefixed by an arrobas (@)
Combination of predicates
It’s possible to combine predicates with one of the three operators and, or and xor. To achieve this you must specify the element combination and specify the operator in the attribute operator. This operator will be used between each operator. To specify that the TotalPriceWithVAT must be greater or equal to @maxAmount or that the column with index 0 must be in upper-case, apply the following guidelines.
To be able to identify quickly the root cause of your bugs, we do not recommend the usage of the and operator. In place, create two tests.