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().

API SPECIFICATION

INPUT

{
    expr    => 'string',    # the expression; at most 512 characters
    columns => 'arrayref',  # optional; column names the expression may read (default [])
    params  => 'hashref',   # optional; named constants, read like columns
}

OUTPUT

Always a Database::BI::Calc object; new does not croak. A bad expression, an unknown identifier, an expression over 512 characters, a parameter named like a column, or a bad span() interval leaves the reason in error() and the object evaluates nothing.

METHODS

error

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

has_error

Returns true when error() is defined.

API SPECIFICATION

Neither takes an argument. error returns 'string' | undef; has_error returns a boolean.

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.

API SPECIFICATION

INPUT

\%row   # hashref of column name => value

OUTPUT

'scalar' | undef

undef when the expression did not compile, or when evaluating it fails for this row. Bound params are read in place of same-named row keys.

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
rx(s, 'pattern')         the first capture of a regular expression
rx(s, 'pattern', 'g')    capture g: a number (1 is the first) or a group's name

rx(ref,'^([A-Z]{2})-','1') is GB for GB-2026-001. The pattern and the group are quoted constants, and the pattern is checked once, when the expression is compiled (see "Regular expressions"). A value it does not match, an undefined value and a group the pattern does not have are all undef, not an error; a group that took no part in the match is ''. One rx() gives one value: a stored extraction is what makes several columns from one pattern.

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.

Time of day

hour(v)          hour of day, 0-23, or undef when v carries no time
minute(v)        minute, 0-59
second(v)        second, 0-59
epoch(v)         epoch seconds
epoch(v, fmt)    epoch seconds, read with a strptime-style pattern

These read the same values as the date functions and one more shape besides:

C<2026-10-27>, C<2026-10-27T14:05>, C<2026-10-27 14:05:32>
                 ISO date or timestamp, local time
C<1761660332>    ten digits: an epoch in seconds
C<1761660332000> thirteen digits: an epoch in milliseconds
anything else with C<fmt>, e.g. C<epoch(t,'%d/%m/%Y %H:%M')>
                 read with the pattern

Only the digit count separates the two epoch shapes, which is why it is the rule and why it is written down here. A pattern (fmt, and the stored time pattern below) may use %Y (year), %m (month number), %d (day), %H (hour), %M (minute), %S (second) and %b (month abbreviation), with any other character taken literally; a pattern must name a year, a month and a day, and any other token is a compile error rather than a silent skip.

Nothing here parses or stores a time zone: every value is local time, by POSIX::mktime, exactly as the date functions and the relative time ranges already are. A cell that carries no time -- 2026-10-27 -- gives undef for hour(), minute() and second() rather than a made-up zero.

Time buckets

span(v, '<n><unit>')     the ISO label of the bucket v falls in

The unit is s, m, h, d, w, mon (months, because m is minutes), q (quarters) or y, and the count is one or more. The label is the start of the half-open interval containing the value:

span(v, '1s')    2026-10-27T14:05:03
span(v, '15m')   2026-10-27T14:00:00
span(v, '1h')    2026-10-27T14:00:00
span(v, '1d')    2026-10-27        local midnight, so a 23- or 25-hour day
                                   still has one bucket
span(v, '1w')    2026-10-26        the Monday of that ISO week
span(v, '1mon')  2026-10-01        the first of that month
span(v, '1q')    2026-10-01        the first day of that quarter
span(v, '1y')    2026-01-01

A count above one widens the bucket without moving the calendar: 3mon aligns to January, April, July and October because a month count runs from January 1970, not from the first value seen.

Labels are ISO at the bucket's own resolution, never 'Oct 2026'. Group keys are sorted as strings, so an ISO label makes lexicographic order chronological order -- a bucketed chart is in the right order with no second sort path. A pretty name is a presentation step; month_name() already reads a month number, and it is never the group key.

A value that cannot be read, or an interval that is not one -- '1x', '0h', '-1d' -- gives undef, which Dashboard::_apply_calc_fields stores as '(unparseable)' so the row stays visible in one explicit bucket rather than disappearing. The interval is a constant in the expression, so a bad one is rejected when the expression is compiled, not when a row is reached.

The interval rule is also a function:

Database::BI::Calc::span_interval('1mon')   # { n => 1, unit => 'mon' }
Database::BI::Calc::span_interval('15m')    # { n => 15, unit => 'm', secs => 60 }
Database::BI::Calc::span_interval('0h')     # undef

API SPECIFICATION

INPUT

$interval   # '<n><unit>', n >= 1, unit one of s m h d w mon q y

OUTPUT

{ n => 'integer', unit => 'string', secs => 'integer' } | undef

secs is present only for s, m and h, whose buckets are floored from the epoch. Anything else, including a reference, is undef.

Regular expressions

compile_pattern($pattern)              -> { re, groups, names }
capture_of($compiled, $value, $group)  -> capture | '' | undef

rx() and the stored extractions both compile through compile_pattern, so they refuse the same patterns: one over 200 characters; one that uses (?{, (??{, (?p or (*, which run code or steer the engine (an escaped paren is a literal and is allowed); and one that does not compile, with perl's reason. names lists the named groups, (?<name>...), in the order they are written. capture_of cuts a capture to 200 characters, because a capture becomes a group key.

Perl's regular-expression engine has no timeout, so these checks are not a sandbox: a pathological pattern, such as (a+)+$ against a long string of as, stalls the request that uses it. Patterns are typed by the reader who owns the server, never accepted from a remote party, which is the only reason this is acceptable.

API SPECIFICATION

INPUT

compile_pattern: $pattern   # string, at most 200 characters
capture_of:      $compiled  # from compile_pattern
                 $value     # string | undef
                 $group     # integer >= 1 | named group

OUTPUT

compile_pattern: { re => 'Regexp', groups => 'integer', names => [ 'string', ... ] }
                 croaks error_pattern_too_long | error_pattern_construct | error_pattern_invalid
capture_of:      'string' (at most 200 characters) | '' | undef

compile_pattern keeps the caller's $@.

Date normalisation (standalone helper)

date_normalise($value, $pattern) -> ISO string or undef

Converts $value from a non-ISO format to ISO-8601. Returns undef when the value does not match the pattern. $pattern is one of:

%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)

or a hashref for a column that holds a time as well as a date:

{ date => '%d/%m/%Y', time => '%H:%M' }                  -> '2026-10-27T14:05:00'
{ date => '%d/%m/%Y', time => '%H:%M:%S' }               -> '2026-10-27T14:05:32'
{ date => '%d/%m/%Y', as => 'timestamp' }                -> '2026-10-27T00:00:00'

With a time pattern the value is normalised to YYYY-MM-DDTHH:MM:SS, which is the shape the date comparisons in Database::BI::Controller::Dashboard already read to the second and which a last_24_hours window can filter on; with as => 'timestamp' and no time pattern it is that shape at midnight, so a date-only column can be compared the same way. A date pattern on its own still means YYYY-MM-DD and still normalises to YYYY-MM-DD, so a stored pattern that has not changed its meaning has not changed its meaning.

This is called by Dashboard::_apply_date_normalization before calc fields are evaluated, so YEAR(date), HOUR(date) and span(date, '1h') see ISO values regardless of the source format.

API SPECIFICATION

INPUT

$value     # string
$pattern   # one of the three date patterns, or { date, time, as }

OUTPUT

'string' | undef   # YYYY-MM-DD, or YYYY-MM-DDTHH:MM:SS with a time or as => 'timestamp'

An undefined or empty value, an unknown pattern, or a value that does not match returns undef. It does not croak.

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.
A regular expression from rx() is compiled with qr//, never eval, and the constructs that run code are refused before that. It has no timeout; see "Regular expressions".
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.

AUTHOR

Nigel Horne, <njh@nigelhorne.com>

LICENCE AND COPYRIGHT

Usage is subject to the GPL2 licence terms.

To install Database::BI, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Database::BI

CPAN shell

perl -MCPAN -e shell
install Database::BI

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	Go to GitHub issues (only if GitHub is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

VERSION

SYNOPSIS

DESCRIPTION

CONSTRUCTOR

new(%args)

API SPECIFICATION

INPUT

OUTPUT

METHODS

error

has_error

API SPECIFICATION

evaluate(\%row)

API SPECIFICATION

INPUT

OUTPUT

OPERATORS

BUILT-IN FUNCTIONS

String

Numeric

Conditional

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

Time of day

Time buckets

API SPECIFICATION

INPUT

OUTPUT

Regular expressions

API SPECIFICATION

INPUT

OUTPUT

Date normalisation (standalone helper)

API SPECIFICATION

INPUT

OUTPUT

Binning

COLUMN NAMES WITH SPACES

SECURITY

DEPENDENCIES

SEE ALSO

AUTHOR

LICENCE AND COPYRIGHT

Module Install Instructions

Keyboard Shortcuts

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