/ 
Database-BI-0.012.1
/ Database::BI::Calc

NAME

Database::BI::Calc - Safe recursive-descent expression evaluator for Database::BI

VERSION

0.012.0

SYNOPSIS

use Database::BI::Calc;

my $calc = Database::BI::Calc->new(
    expr    => 'if(qty > 0, round(amount / qty, 2), 0)',
    columns => [qw(amount qty)],
);

die $calc->error if $calc->error;

for my $row (@rows) {
    my $val = $calc->evaluate($row);   # undef on runtime error
    $row->{unit_price} = $val;
}

DESCRIPTION

A compile-once, evaluate-many expression engine. Expressions are parsed into a pure-Perl closure tree at construction time. No eval of user-supplied strings is ever performed anywhere in this module.

CONSTRUCTOR

new(%args)

my $calc = Database::BI::Calc->new(
    expr    => 'if(year(date) = 2026, amount * 1.1, amount)',
    columns => [qw(date amount)],
);

Recognised keys:

expr

The expression string. Maximum 512 characters.

columns

Arrayref of allowed column names. Any identifier in expr that is not a known function name must appear in this list (or must match when underscores are replaced with spaces). Unknown identifiers cause a compile-time error stored in error().

Always check error() after construction before calling evaluate().

METHODS

error

Returns the compile-time error string, or undef if the expression compiled successfully.

has_error

Returns true when error() is defined.

evaluate(\%row)

Evaluates the compiled expression against a data row. Returns the computed scalar value, or undef on any runtime error (division by zero, non-numeric operand, undef column value, etc.) rather than croaking.

OPERATORS

+  addition         -  subtraction
*  multiplication   /  division (division by zero -> undef)
-x unary negation   +x unary identity
(expr)  grouping

=   equal          !=  not equal
<   less than      <=  less-or-equal
>   greater than   >=  greater-or-equal

Operator precedence (highest to lowest): unary, then * /, then + -, then comparisons. Comparisons yield 1 (true) or 0 (false). When both operands look numeric, numeric comparison is used; otherwise case-insensitive string comparison, matching _apply_filter_spec semantics.

Use = for equality, not ==. Writing == is a compile-time error.

BUILT-IN FUNCTIONS

String

upper(s)                 uc($s)
lower(s)                 lc($s)
trim(s)                  strip leading/trailing whitespace
len(s)                   length($s)
concat(a, b, ...)        join('', ...), undef treated as ''
substr(s, start)         0-based start, to end of string
substr(s, start, len)    0-based start, len characters

Numeric

round(n, decimals)       sprintf-based rounding
floor(n)                 POSIX::floor
ceil(n)                  POSIX::ceil
abs(n)                   absolute value

Conditional

if(cond, true, false)    short-circuit ternary: only the taken branch is
                         evaluated, so if(qty > 0, amount/qty, 0) is safe
isnull(val, default)     val if defined, else default
coalesce(v1, v2, ...)    first defined value

Date (ISO-8601 YYYY-MM-DD only)

year(s)        four-digit year (2026)
month(s)       month number 1-12
day(s)         day of month 1-31
quarter(s)     quarter 1-4
week(s)        ISO 8601 week number 1-53
month_name(s)  full English month name ('January' .. 'December')
weekday(s)     ISO 8601 weekday: 1 = Monday, 7 = Sunday
now()          today's date as 'YYYY-MM-DD' (no arguments)

All date functions return undef when the value does not start with a valid YYYY-MM-DD prefix, rather than croaking.

Date normalisation (standalone helper)

date_normalise($value, $pattern) -> 'YYYY-MM-DD' or undef

Converts $value from a non-ISO format to ISO-8601 YYYY-MM-DD. Returns undef when the value does not match the pattern. Supported patterns:

%m/%d/%Y   US slash-date  (01/15/2026)
%d/%m/%Y   EU slash-date  (15/01/2026)
%d-%b-%Y   DD-Mon-YYYY   (15-Jan-2026; month abbreviation is case-insensitive)

This is called by Dashboard::_apply_date_normalization before calc fields are evaluated, so YEAR(date) sees ISO values regardless of the source format.

Binning

bin(value, width)   '[lo,hi)' label for the half-open interval containing
                    value.  Width must be a positive number.  Integer widths
                    produce integer labels ('[0,100)'); fractional widths
                    use %g ('[0.5,1)')

COLUMN NAMES WITH SPACES

Column names containing spaces can be referenced by replacing spaces with underscores. per_unit_price in an expression addresses a column named "Per Unit Price" automatically.

SECURITY

  • No eval of user-supplied strings is performed anywhere in this module.

  • Column references are validated at compile time against the columns whitelist. Unknown identifiers are rejected at new() time.

  • Function names are validated against a hardcoded dispatch table. Unknown function names are rejected at new() time.

  • Expressions longer than 512 characters are rejected.

  • Nesting deeper than 20 levels is rejected.

  • Division by zero and undef/non-numeric operands return undef rather than croaking, so a bad row does not abort an export pipeline.

DEPENDENCIES

POSIX, Scalar::Util, Readonly, Carp -- all present in the Database::BI dependency set.

SEE ALSO

Database::BI, Database::BI::Controller::Dashboard

AUTHOR

Nigel Horne, <njh@nigelhorne.com>

LICENSE

Same terms as Database::BI.