lib/Database/Join.pm

        start → end → jump
        

    1: package Database::Join;
    2: 
    3: # ABSTRACT: Combined view across two or more Database::Abstraction objects
    4: 
    5: use 5.010001;
    6: use strict;
    7: use warnings;
    8: use autodie qw(:all);
    9: 
   10: use Carp qw(croak carp);
   11: use File::Spec;
   12: use List::Util qw(max);
   13: use Log::Abstraction;
   14: use Readonly;
   15: use Scalar::Util qw(blessed);
   16: use Object::Configure;
   17: use Params::Get qw(get_params);
   18: use Params::Validate::Strict qw(validate_strict);
   19: use Sub::Protected;
   20: 
   21: # Named-pair keys accepted by add_database; kept here so the guard and the
   22: # validate_strict schema cannot silently diverge.
   23: Readonly::Array my @_ADD_DB_KEYS => qw(database join_column filter remove_columns);
   24: 
   25: # SQL comparison operators that are safe to interpolate into WHERE clauses.
   26: # Scalar-binding operators: the value is always passed as a bind param (?), so
   27: # no quoting or escaping is required on the value side.  LIKE and NOT LIKE are
   28: # safe for the same reason — the pattern is bound, not interpolated.
   29: Readonly::Hash my %SAFE_SQL_OPS => map { $_ => 1 } ('>', '<', '>=', '<=', '!=', '=', 'LIKE', 'NOT LIKE');
   30: 
   31: # List-membership operators whose criterion value is an arrayref.
   32: # Each element is bound as a separate ?, so injection is impossible.
   33: # IN  with an empty list → WHERE 1=0 (no rows match, correct SQL semantics).
   34: # NOT IN with an empty list → no WHERE term (all rows match, correct semantics).
   35: Readonly::Hash my %SAFE_LIST_OPS => map { $_ => 1 } ('IN', 'NOT IN');
   36: 
   37: # Nullability operators: no bind parameter.  The hashref value is ignored —
   38: # any value (undef, 1, ...) signals intent; only the key selects the operator.
   39: # A bare undef criterion value (col => undef) also generates IS NULL.
   40: Readonly::Hash my %SAFE_NOARG_OPS => map { $_ => 1 } ('IS NULL', 'IS NOT NULL');
   41: 
   42: our $VERSION = '0.008.2';
   43: 
   44: # Package-level cache for threads availability.  undef = not yet checked;
   45: # 1 = available; 0 = not available.  Checked lazily on the first parallel
   46: # query and never re-evaluated (require is cached in %INC after success).
   47: my $HAS_THREADS;
   48: 
   49: # ---------------------------------------------------------------------------
   50: # All user-facing strings route through this dictionary.  Supply an i18n
   51: # object with a translate($key, @sprintf_args) method to localise them.
   52: # ---------------------------------------------------------------------------
   53: Readonly::Hash my %MESSAGES => (
   54: 	error_no_databases	=> 'At least one Database::Abstraction object is required',
   55: 	error_invalid_db	=> 'databases[%d] does not support the selectall_arrayref/columns interface',
   56: 	error_join_col_missing	=> 'join_column "%s" is absent from databases[%d] (%s)',
   57: 	error_col_conflict	=> 'Column "%s" exists in multiple databases; use the owning database directly or rename the column',
   58: 	error_remove_join_col	=> 'Cannot remove join_column "%s"; it is required for the join',
   59: 	warn_unknown_column	=> 'Column "%s" is not present in any configured database; criterion ignored',
   60: 	error_join_criterion	=> 'join => criteria are not supported on Database::Join (SQL JOINs target a single DA; use multiple DAs in the databases => [] constructor instead)',
   61: 	error_query_unsupported	=> 'query() chained builder is not supported on Database::Join (Database::Abstraction::Query targets a single DA, not the merged view); use selectall_arrayref, selectall_array, fetchrow_hashref, count, or each_row instead',
   62: 	error_execute_unsupported => 'execute() raw SQL is not supported on Database::Join',
   63: 	error_unknown_message	=> 'Unknown message key "%s"',
   64: 	error_invalid_prefix	=> 'collision_prefix[%d] must be a plain string, not a reference; passing a reference would leak a heap address into column names',
   65: 	error_invalid_backend	=> 'backend must be "array", "sqlite", or "auto"; got "%s"',
   66: 	error_sqlite_connect	=> 'Failed to open temporary SQLite database for join backend: %s',
   67: 	warn_schema_type_mismatch => 'column "%s" has type "%s" in database[%d] but type "%s" in database[%d]; use collision_prefix to preserve both values without silent type coercion',
   68: 	error_invalid_callback	=> 'each_row: first argument must be a code reference',
   69: );
   70: 
   71: =head1 NAME
   72: 
   73: Database::Join - Read-only combined view across two or more Database::Abstraction objects
   74: 
   75: =head1 VERSION
   76: 
   77: Version 0.008.2
   78: 
   79: =head1 SYNOPSIS
   80: 
   81: B<Basic two-database join>
   82: 
   83:     use Database::Join;
   84: 
   85:     # Step 1: create each component database the normal way
   86:     my $customers = Database::Customers->new(directory => '/data');
   87:     my $loyalty   = Database::Loyalty->new(directory  => '/data');
   88: 
   89:     # Step 2: combine them on the shared key column 'entry'
   90:     my $join = Database::Join->new(
   91:         databases   => [ $customers, $loyalty ],
   92:         join_column => 'entry',
   93:     );
   94: 
   95:     # Step 3: query exactly as you would a single Database::Abstraction object
   96:     my $all_rows  = $join->selectall_arrayref();
   97:     my $vip_rows  = $join->selectall_arrayref(tier => 'gold');
   98:     my $one_row   = $join->fetchrow_hashref(entry => 'C001');
   99:     my $total     = $join->count();
  100:     my $col_names = $join->columns();
  101: 
  102: B<Hiding internal columns>
  103: 
  104:     my $join = Database::Join->new(
  105:         databases      => [ $customers, $loyalty ],
  106:         join_column    => 'entry',
  107:         remove_columns => [ 'internal_id', 'audit_ts' ],
  108:     );
  109:     # 'internal_id' and 'audit_ts' never appear in results or columns()
  110: 
  111: B<join_map: when the key column has different names in each database>
  112: 
  113:     # $cities  (index 0) has a column called 'statecode' -- matches join_column
  114:     # $stnames (index 1) has a column called 'entry'     -- different name
  115: 
  116:     my $join = Database::Join->new(
  117:         databases   => [ $cities,  $stnames ],
  118:         #                index 0   index 1
  119:         join_column => 'statecode',
  120:         join_map    => { 1 => 'entry' },  # index 1 calls its join key 'entry'
  121:     );
  122: 
  123:     # All returned rows use 'statecode'; 'entry' is never exposed
  124:     my $rows = $join->selectall_arrayref();
  125: 
  126: B<filters: permanently restrict a database's visible rows>
  127: 
  128:     # Only show orders placed more than 60 days ago, without repeating
  129:     # the criterion on every query call.
  130:     my $join = Database::Join->new(
  131:         databases   => [ $customers, $orders ],
  132:         join_column => 'entry',
  133:         filters     => { 1 => { age_days => { '>' => 60 } } },
  134:     );
  135: 
  136:     my $rows = $join->selectall_arrayref();                 # all old orders
  137:     my $vip  = $join->selectall_arrayref(tier => 'gold');   # old + gold tier
  138: 
  139: B<Inner and outer join types>
  140: 
  141:     my $inner = Database::Join->new(
  142:         databases   => [ $customers, $loyalty ],
  143:         join_column => 'entry',
  144:         join_type   => 'inner',   # only keys present in BOTH databases
  145:     );
  146: 
  147:     my $outer = Database::Join->new(
  148:         databases   => [ $customers, $loyalty ],
  149:         join_column => 'entry',
  150:         join_type   => 'outer',   # all keys from EITHER database
  151:     );
  152: 
  153: B<Building the view incrementally with add_database>
  154: 
  155:     my $join = Database::Join->new(
  156:         databases   => [ $customers ],
  157:         join_column => 'entry',
  158:     );
  159: 
  160:     $join->add_database($loyalty)
  161:          ->add_database($scores, remove_columns => ['raw_score']);
  162: 
  163: B<AUTOLOAD column shortcut>
  164: 
  165:     # Returns the 'name' value for entry 'C001' (scalar context)
  166:     my $name = $join->name(entry => 'C001');
  167: 
  168:     # Returns all 'tier' values (list context)
  169:     my @tiers = $join->tier();
  170: 
  171: B<SQLite join backend for large datasets>
  172: 
  173:     # 'auto' (default): switches to SQLite automatically above the threshold
  174:     my $join = Database::Join->new(
  175:         databases      => [ $customers, $loyalty ],
  176:         join_column    => 'entry',
  177:         backend        => 'auto',          # default
  178:         max_array_rows => 50_000,          # use SQLite when combined rows > 50,000
  179:     );
  180: 
  181:     # Always use SQLite -- useful when you know the data is large
  182:     my $join = Database::Join->new(
  183:         databases   => [ $customers, $loyalty ],
  184:         join_column => 'entry',
  185:         backend     => 'sqlite',
  186:         tmpdir      => '/fast/nvme/tmp',   # optional: faster temp disk
  187:     );
  188: 
  189:     # Always use the original in-memory path
  190:     my $join = Database::Join->new(
  191:         databases   => [ $customers, $loyalty ],
  192:         join_column => 'entry',
  193:         backend     => 'array',
  194:     );
  195: 
  196: =head1 DESCRIPTION
  197: 
  198: C<Database::Join> merges two or more L<Database::Abstraction> objects into a
  199: single logical, read-only view.  Each component database is queried
  200: independently through its own C<Database::Abstraction> interface.  The results
  201: are combined using a shared key column (C<join_column>).
  202: In effect, this means that you can view data from more than one database using
  203: an intuitive, non-SQL interface.
  204: 
  205: Every storage format that C<Database::Abstraction> supports works as a
  206: component database: CSV, PSV, TSV, SQLite, JSON, XML, XLSX, BerkeleyDB, HTML
  207: URL, JSON URL, or any custom subclass.  Component databases may mix formats
  208: within the same join.
  209: 
  210: The module exposes the same read-only API as C<Database::Abstraction>:
  211: C<selectall_arrayref>, C<selectall_array>, C<fetchrow_hashref>, C<count>,
  212: C<columns>, C<schema>, C<updated>, C<set_logger>, and the AUTOLOAD column
  213: shortcut.  Callers do not need to know how many underlying databases are
  214: involved.
  215: 
  216: Think of it as a virtual database table that is assembled on demand from
  217: several real tables, one per component database.
  218: 
  219: B<Join backends>
  220: 
  221: By default (C<backend =E<gt> 'auto'>), C<Database::Join> first checks the
  222: combined source row count.  For small datasets (up to C<max_array_rows>,
  223: default 10,000 rows) it merges entirely in Perl memory.  For larger datasets it
  224: automatically spills source rows into a temporary SQLite database and executes
  225: a single SQL JOIN there, keeping peak RAM to roughly one times the source data
  226: size instead of three.  You can also force either path unconditionally with
  227: C<backend =E<gt> 'sqlite'> or C<backend =E<gt> 'array'>.
  228: 
  229: =head2 Join semantics
  230: 
  231: The C<join_type> parameter controls what happens when a particular key value
  232: exists in some component databases but not all:
  233: 
  234: =over 4
  235: 
  236: =item C<left> (the default)
  237: 
  238: All rows from the I<primary> (first) database are returned.  Columns from
  239: subsequent databases are included where a matching row is found, and simply
  240: absent from the hashref where there is no match.  If you are familiar with
  241: SQL, this is a LEFT OUTER JOIN on the first table.
  242: 
  243: =item C<inner>
  244: 
  245: Only rows whose join-column value is present in I<every> component database
  246: are returned.  This is equivalent to a SQL INNER JOIN.
  247: 
  248: =item C<outer>
  249: 
  250: Every join-column value found in I<any> component database is returned.
  251: Columns from databases that do not have that key value are absent from the
  252: merged row.  This is a FULL OUTER JOIN.
  253: 
  254: =back
  255: 
  256: B<Important override rule:> whenever you pass a query criterion for a column
  257: that belongs to a secondary database, that database automatically acts as an
  258: inner-join partner for that query only -- regardless of C<join_type>.  This
  259: gives WHERE-clause semantics.  For example, if you have a LEFT join but query
  260: C<< tier => 'gold' >> on a secondary database, only rows whose secondary entry
  261: has tier = 'gold' are returned (rows with no secondary entry are excluded, just
  262: as a WHERE clause would exclude them).
  263: 
  264: =head2 Column ownership and routing
  265: 
  266: At construction time, C<Database::Join> calls C<columns()> on each component
  267: database and builds an internal index that maps every column name to the
  268: database that owns it.
  269: 
  270: When you pass criteria to a query method, each key-value pair is automatically
  271: routed to the right database.  You never need to say which database a column
  272: belongs to.
  273: 
  274: The C<join_column> is special: criteria on it are broadcast to I<all>
  275: databases so that each database fetches only the relevant rows before the
  276: in-memory merge.
  277: 
  278: When the same non-join column name exists in more than one database, the
  279: I<last> database in the C<databases> array wins by default: its value
  280: overwrites earlier ones in merged rows.  Use C<collision_prefix> to
  281: preserve both values under distinct names instead.
  282: 
  283: =head1 LIMITATIONS
  284: 
  285: =over 4
  286: 
  287: =item Memory usage (array backend)
  288: 
  289: When C<backend> is C<'array'> (or C<'auto'> and the dataset is small), all
  290: matching rows are fetched into Perl memory.  Peak RAM is roughly three times
  291: the source data size.  For large datasets use C<backend =E<gt> 'sqlite'>, or
  292: leave C<backend =E<gt> 'auto'> and set C<max_array_rows> appropriately.
  293: 
  294: =item SQLite backend uses a persistent cache file
  295: 
  296: When the SQLite path is active, a single C<.db> file with a randomly generated
  297: name (chosen by C<File::Temp> to avoid collisions) is created in C<tmpdir> the
  298: first time a query runs on a given C<Database::Join> object.  Source data is
  299: spilled into that file once; subsequent queries against the same object reuse
  300: the file without re-fetching the source data.
  301: 
  302: The cache is automatically invalidated and rebuilt whenever any source
  303: database's C<updated()> timestamp changes (indicating new data), or when
  304: C<add_database()> is called.
  305: 
  306: The file is deleted when the C<Database::Join> object is destroyed (typically
  307: when it goes out of scope).  At any given moment no more than one such file
  308: exists per object.  The directory must be writable and have enough free space
  309: for the full source data (once, not per-query).
  310: 
  311: =item No chained builder or raw SQL
  312: 
  313: C<query()> and C<execute()> are not implemented.  Use C<selectall_arrayref>
  314: or C<fetchrow_hashref> instead.
  315: 
  316: =item Single-column equi-join only
  317: 
  318: Joining on more than one column simultaneously, or on expressions, is not
  319: supported.  When the join key has different names in different databases,
  320: use C<join_map> to declare each database's local column name.
  321: 
  322: =item Sort order
  323: 
  324: Results are sorted ascending by C<join_column> by default.  Pass
  325: C<< sort_by => 'colname' >> (or C<< sort_by => ['colname', 'DESC'] >>)
  326: to any query method to override this.  The array path uses string comparison
  327: (C<cmp>); for accurate numeric ordering on large datasets use the SQLite
  328: backend, which sorts natively by type.
  329: 
  330: =item count() on the array backend fetches all rows
  331: 
  332: On the array backend, C<count()> executes the full in-memory join and counts
  333: the resulting rows in Perl; no C<COUNT(*)> is pushed to the component
  334: databases.  On the SQLite backend, C<count()> executes a C<SELECT COUNT(*)>
  335: SQL query against the cached join tables, avoiding a full row transfer.
  336: 
  337: =back
  338: 
  339: =head1 COMMON PITFALLS
  340: 
  341: =over 4
  342: 
  343: =item The join_column must exist in every component database
  344: 
  345: If even one database is missing the join key column, C<new()> (or
  346: C<add_database()>) will C<croak> immediately.  Use C<join_map> when the
  347: column has a different local name in some databases.
  348: 
  349: =item Criteria on a removed column are silently dropped
  350: 
  351: If you call C<remove_column('tier')> and later query
  352: C<< selectall_arrayref(tier => 'gold') >>, the criterion is ignored (with a
  353: C<carp> warning) and all rows are returned.  Always pass criteria before
  354: removing columns, or restructure your code to avoid this.
  355: 
  356: =item You cannot remove the join column
  357: 
  358: C<< $join->remove_column($join->join_column) >> will C<croak>.  The join key
  359: is required for the merge to work.
  360: 
  361: =item Left join does not guarantee all columns are populated
  362: 
  363: Under a LEFT join, rows from the primary database that have no matching row
  364: in a secondary database will be returned with I<no keys> from that secondary
  365: database.  Accessing C<< $row->{score} >> on such a row returns C<undef> --
  366: not zero, not an empty string.  Always test C<defined $row->{score}> rather
  367: than just C<$row->{score}> when the secondary match is optional.
  368: 
  369: =item Filters act as inner-join partners
  370: 
  371: Any database that has a C<filters> entry is promoted to an inner-join partner,
  372: regardless of C<join_type>.  A row whose join-key value does not appear in
  373: the filtered database's result is removed from the merged output entirely, not
  374: merely missing its secondary columns.  This is intentional but can be
  375: surprising if you expected LEFT join semantics.
  376: 
  377: =item Criteria-merging replaces scalar filters
  378: 
  379: When both a base filter and a query criterion target the same column, and both
  380: are operator hashrefs (e.g. C<< { '>' => 60 } >>), the operators are combined
  381: (AND semantics).  But if the query criterion is a plain scalar (e.g.
  382: C<< score => 75 >>), it I<replaces> the base filter for that column entirely --
  383: the base filter is ignored for that query.
  384: 
  385: =item AUTOLOAD sees the full merged join when filters or join_map are active
  386: 
  387: When either C<filters> or C<join_map> is in effect, the AUTOLOAD shortcut
  388: (C<< $join->columnname(...) >>) runs the full join query rather than
  389: delegating directly to the owning database.  This is necessary for correctness
  390: but means the result respects all active filters and join-key translations,
  391: which may differ from what the owning database would return on its own.
  392: 
  393: =item Duplicate column names: last database wins (unless collision_prefix is set)
  394: 
  395: When two component databases each have a column called C<notes>, the second
  396: database's value silently overwrites the first in every merged row.  Use
  397: C<collision_prefix => { 1 => 'right' }> to publish the second database's
  398: C<notes> as C<right.notes> so both values survive, or use C<remove_columns>
  399: (or C<remove_column>) to drop the unwanted duplicate entirely.
  400: 
  401: =item Mutating the filters hashref after construction has no effect
  402: 
  403: C<Database::Join> deep-copies the C<filters> hashref (and any C<filter>
  404: passed to C<add_database>) at the moment of construction.  The original hashref
  405: you passed in is never stored.  If you later modify it -- for example, to
  406: tighten or loosen a filter criterion -- the joined view is I<not> affected.
  407: Construct a new C<Database::Join> object, or use a component
  408: C<Database::Abstraction> that supports dynamic filter modification.
  409: 
  410: =item auto mode may always use the array path for some DAs
  411: 
  412: C<backend =E<gt> 'auto'> counts rows cheaply only when each component database
  413: either implements C<dbi_source()> (SQLite-backed) or directly defines a
  414: C<count()> method in its own package.  A DA that merely I<inherits> C<count()>
  415: from C<Database::Abstraction> is treated as uncountable, because the parent
  416: class C<count()> expects a key argument and behaves differently from a
  417: "return total row count" function.  In that case C<Database::Join>
  418: conservatively uses the array path for the whole query, even if the dataset is
  419: large.  To opt in to the SQLite path for such a DA, either add your own
  420: C<count()> override that returns the total row count, implement C<dbi_source()>,
  421: or use C<backend =E<gt> 'sqlite'> unconditionally.
  422: 
  423: =item dbi_source() ATTACH is unconditional - query-time criteria go into WHERE
  424: 
  425: When a component database implements C<dbi_source()>, C<Database::Join> always
  426: uses the zero-copy ATTACH path, I<even when the current query includes criteria
  427: for columns in that database>.  The criteria are translated into parameterised
  428: SQL C<WHERE> clauses applied against the ATTACHed table; no row-level copy is
  429: performed.  (Prior to 0.005.0 the presence of any query-time criteria would
  430: force a spill; that restriction has been removed.)
  431: 
  432: =item Broadcast join-column criterion does not force secondaries into inner-join
  433: 
  434: When a caller passes a join-column criterion (e.g. C<entry =E<gt> 'k1'>),
  435: C<Database::Join> broadcasts it to all component databases so each DA can
  436: filter its fetch to the requested key.  Prior to 0.006.0 this broadcast was
  437: incorrectly counted as "having criteria" for secondary databases, causing
  438: C<left> and C<outer> joins to silently behave as C<inner> joins when a
  439: join-column criterion was present.  The fix: only non-join-column criteria
  440: (e.g. column filters from the caller or base C<filters =E<gt> {...}>) promote
  441: a secondary to inner-join status.  The broadcast itself is now a transparent
  442: key-range selector that does not affect join semantics.
  443: 
  444: =item LIKE and NOT LIKE work on the SQLite path; other pattern operators do not yet
  445: 
  446: C<LIKE>, C<NOT LIKE>, C<IN>, and C<NOT IN> are fully supported on the SQLite
  447: backend.  C<LIKE>/C<NOT LIKE> take a scalar pattern; C<IN>/C<NOT IN> take an
  448: arrayref of values.  All are injection-safe because values are passed as bind
  449: parameters, never interpolated.
  450: 
  451:     # LIKE
  452:     my $rows = $join->selectall_arrayref(name => { LIKE => 'A%' });
  453: 
  454:     # IN
  455:     my $rows = $join->selectall_arrayref(tier => { IN => ['gold', 'silver'] });
  456: 
  457:     # NOT IN
  458:     my $rows = $join->selectall_arrayref(tier => { 'NOT IN' => ['bronze'] });
  459: 
  460: C<IN> with an empty arrayref matches no rows (SQL semantics: C<IN ()> is
  461: always false).  C<NOT IN> with an empty arrayref matches all rows (no
  462: constraint added).
  463: 
  464: C<IS NULL> and C<IS NOT NULL> are supported on the SQLite backend using an
  465: explicit operator hashref:
  466: 
  467:     my $rows = $join->selectall_arrayref(score => { 'IS NULL'     => undef });
  468:     my $rows = $join->selectall_arrayref(score => { 'IS NOT NULL' => 1    });
  469: 
  470: The hashref value is ignored; only the key selects the operator.  A bare
  471: C<undef> criterion value (C<< score => undef >>) also generates C<IS NULL>
  472: on the SQLite path.  Note: on the in-memory array path, the component DA
  473: may treat an C<undef> criterion value as C<< no filter >> rather than
  474: C<IS NULL>, so use the explicit hashref form for consistent behaviour
  475: across backends.
  476: 
  477: Any operator not in the supported set is skipped on the SQLite path with a
  478: C<carp> warning.  The array path forwards all operators to the component DA
  479: unchanged, which may or may not honour them.
  480: 
  481: =item Temp file directory must be writable and have free space
  482: 
  483: The SQLite path creates one temporary C<.db> file per C<Database::Join> object
  484: in C<tmpdir> (default: C<File::Spec-E<gt>tmpdir()>, usually C</tmp> on Unix).
  485: The filename is randomly generated by C<File::Temp> -- you cannot predict it,
  486: only the directory is under your control.  The file is created on the first
  487: query and kept alive until the object is destroyed; it is not re-created on
  488: every query call.  If the directory is not writable, or the filesystem is full,
  489: the call will C<croak> with C<error_sqlite_connect>.  Check permissions and
  490: free space if you see that error.
  491: 
  492: =item C<join =E<gt>> criteria are not supported
  493: 
  494: C<Database::Abstraction> accepts a C<join =E<gt> { table =E<gt> ..., on =E<gt> ... }>
  495: key in its criteria hashrefs to express an SQL JOIN within a single table.
  496: C<Database::Join> cannot route this to a component DA meaningfully: the merged
  497: view has no concept of a single underlying table.  Passing C<join =E<gt>> to any
  498: query method will C<croak> with C<error_join_criterion>.
  499: 
  500: B<Fix:> Model the joined table as a separate C<Database::Abstraction> object and
  501: add it to the C<databases =E<gt> []> list.  Use C<join_column> or C<join_map> to
  502: specify the shared key.
  503: 
  504: =back
  505: 
  506: =head1 METHODS
  507: 
  508: =head2 new
  509: 
  510: =head3 SYNOPSIS
  511: 
  512:     my $join = Database::Join->new(
  513:         databases        => [ $db1, $db2 ],
  514:         join_column      => 'entry',
  515:         join_type        => 'left',
  516:         join_map         => { 1 => 'local_col' },
  517:         filters          => { 1 => { score => { '>' => 60 } } },
  518:         collision_prefix => { 1 => 'right' },
  519:         remove_columns   => [ 'email', 'internal_id' ],
  520:         backend          => 'auto',        # 'auto' | 'sqlite' | 'array'
  521:         max_array_rows   => 10_000,        # threshold for 'auto' mode
  522:         tmpdir           => '/tmp',        # directory for temp SQLite file
  523:         logger           => $log,
  524:         i18n             => $locale,
  525:     );
  526: 
  527: =head3 DESCRIPTION
  528: 
  529: Constructs and returns a new C<Database::Join> object.
  530: 
  531: Each element of C<databases> must be an already-instantiated subclass of
  532: C<Database::Abstraction>.  The constructor calls C<columns()> on every
  533: database to build an internal column-routing table and verifies that
  534: C<join_column> (or its local alias from C<join_map>) is present in each one.
  535: 
  536: Columns listed in C<remove_columns> are hidden immediately: they do not appear
  537: in C<columns()>, C<schema()>, or any returned row hashref.  This is equivalent
  538: to calling C<remove_column> once per name after construction.
  539: 
  540: =head3 API SPECIFICATION
  541: 
  542: =head4 Input
  543: 
  544:     databases      => { type => 'arrayref', required => 1 }
  545:                       # One or more Database::Abstraction subclass objects.
  546:                       #
  547:                       # DOMAIN -- EP valid:   non-empty arrayref of blessed DA subclasses.
  548:                       # DOMAIN -- EP invalid: scalar, hashref, or absent => croak.
  549:                       # DOMAIN -- BVA size:   minimum 1 element; no documented upper bound.
  550:                       # DOMAIN -- BVA elem:   each element must pass isa('Database::Abstraction').
  551: 
  552:     join_column    => { type => 'string',   optional => 1, default => 'entry' }
  553:                       # The column name shared by all databases (the join key).
  554:                       #
  555:                       # DOMAIN -- EP valid:   any non-empty string present in every component DA.
  556:                       # DOMAIN -- EP invalid: column absent from any DA => croak join_col_missing.
  557:                       # DOMAIN -- BVA:        empty string '' is treated as a column name and
  558:                       #                       will croak if (as expected) it is absent from every DA.
  559:                       # DOMAIN -- NOTE:       matching is case-sensitive and exact.
  560: 
  561:     join_type      => { type => 'string',   optional => 1, default => 'left',
  562:                         enum => ['inner', 'left', 'outer'] }
  563:                       # Controls which keys appear in the result when not all
  564:                       # databases share the same key values.
  565:                       #
  566:                       # DOMAIN -- EP valid:   exactly 'inner', 'left', or 'outer'.
  567:                       # DOMAIN -- EP invalid: any other string including 'INNER', 'LEFT',
  568:                       #                       'OUTER' (enum check is case-sensitive), 'cross',
  569:                       #                       or '' => croak from validate_strict.
  570: 
  571:     join_map       => { type => 'hashref',  optional => 1 }
  572:                       # Zero-based database index => local column name.
  573:                       # See the join_map section for full details.
  574:                       #
  575:                       # DOMAIN -- EP valid:   hashref values must be plain strings.
  576:                       # DOMAIN -- EP invalid: reference value (hashref, arrayref, coderef, etc.)
  577:                       #                       => croak; the guard prevents heap-address leakage.
  578:                       # DOMAIN -- BVA:        out-of-range keys (beyond the databases array) are
  579:                       #                       silently ignored.
  580: 
  581:     filters        => { type => 'hashref',  optional => 1 }
  582:                       # Zero-based database index => criteria hashref.
  583:                       # Permanent row restrictions on individual databases.
  584:                       # See the filters section for full details.
  585: 
  586:     base_criteria  => { type => 'hashref',  optional => 1 }
  587:                       # Column name => value hashref.
  588:                       # Permanent criteria applied to every query, specified by
  589:                       # column name rather than database index.  Each key must
  590:                       # be a column that appears in the merged view (or the
  591:                       # join_column); each value is a plain scalar or an
  592:                       # operator hashref in the same format as
  593:                       # selectall_arrayref accepts.  Criteria are automatically
  594:                       # routed to the database that owns each column (same
  595:                       # routing used by selectall_arrayref).
  596:                       # Equivalent to filters but more convenient when you know
  597:                       # the column name but not which database index owns it.
  598:                       # When a column appears in both base_criteria and filters,
  599:                       # the filters entry takes precedence.
  600:                       # See the base_criteria section for full details.
  601:                       #
  602:                       # DOMAIN -- EP valid:   hashref of column-name => scalar or
  603:                       #                       operator-hashref pairs.  Unknown column
  604:                       #                       names emit warn_unknown_column (carp)
  605:                       #                       and are silently dropped.
  606:                       # DOMAIN -- EP invalid: non-hashref value => croak from
  607:                       #                       validate_strict.
  608:                       # DOMAIN -- BVA:        {} empty hashref is a safe no-op.
  609: 
  610:     collision_prefix => { type => 'hashref', optional => 1 }
  611:                       # Zero-based database index (>0) => prefix string.
  612:                       # When a secondary database has a column that collides
  613:                       # with a column already present in the merged view, the
  614:                       # secondary column is published as "$prefix.$col" instead
  615:                       # of silently overwriting the earlier value.
  616:                       # Index 0 entries are silently ignored.
  617:                       # Omitting this parameter preserves the original
  618:                       # last-database-wins behaviour.
  619:                       # See the collision_prefix section for full details.
  620:                       #
  621:                       # DOMAIN -- EP valid:   absent or {} => last-database-wins (no change).
  622:                       # DOMAIN -- EP valid:   { N => 'prefix' } where N > 0 => colliding
  623:                       #                       columns from DB[N] published as "$prefix.$col";
  624:                       #                       non-colliding columns from the same DB added plain.
  625:                       # DOMAIN -- EP note:    index-0 entries are silently ignored.
  626:                       # DOMAIN -- Invariant:  join_column is never prefixed regardless of
  627:                       #                       collision_prefix configuration.
  628: 
  629:     remove_columns => { type => 'arrayref', optional => 1 }
  630:                       # Column names to hide from the merged view.
  631:                       #
  632:                       # DOMAIN -- EP valid:   arrayref of any strings; non-existent columns
  633:                       #                       are silently ignored (idempotent).
  634:                       # DOMAIN -- EP invalid: join_column itself => croak remove_join_col.
  635:                       # DOMAIN -- BVA:        [] empty arrayref is a safe no-op.
  636: 
  637:     backend        => { type => 'string',   optional => 1, default => 'auto',
  638:                         enum => ['array', 'sqlite', 'auto'] }
  639:                       # Controls which join strategy is used.
  640:                       #   'auto'   -- (default) use 'array' when combined source row count
  641:                       #              <= max_array_rows, 'sqlite' otherwise.
  642:                       #   'sqlite' -- always spill to a temporary SQLite database.
  643:                       #   'array'  -- always use the in-memory merge path.
  644:                       #
  645:                       # DOMAIN -- EP valid:   'array', 'sqlite', or 'auto' (case-sensitive).
  646:                       # DOMAIN -- EP invalid: any other string => croak error_invalid_backend.
  647:                       # DOMAIN -- Default:    'auto'.
  648: 
  649:     max_array_rows => { type => 'integer',  optional => 1, default => 10_000 }
  650:                       # Row-count threshold for 'auto' mode.  When the combined
  651:                       # source row count exceeds this value, the SQLite path is used.
  652:                       # Ignored when backend is 'array' or 'sqlite'.
  653:                       #
  654:                       # DOMAIN -- EP valid:   any non-negative integer.
  655:                       # DOMAIN -- BVA:        0 means always use SQLite (all counts exceed 0).
  656:                       # DOMAIN -- Default:    10,000.
  657: 
  658:     tmpdir         => { type => 'string',   optional => 1 }
  659:                       # Directory for the per-call temporary SQLite database file.
  660:                       # The file is created securely by File::Temp and removed when
  661:                       # the query completes.  Ignored when backend is 'array'.
  662:                       #
  663:                       # DOMAIN -- EP valid:   any writable directory path string.
  664:                       # DOMAIN -- EP absent:  uses File::Spec->tmpdir() (system temp dir).
  665: 
  666:     parallel       => { type => 'integer',  optional => 1, default => 0 }
  667:                       # When set to 1 and the join has more than 2 databases (primary +
  668:                       # 2 or more secondaries), secondary DA fetches are issued in
  669:                       # parallel Perl threads.  Requires the 'threads' module; falls
  670:                       # back to sequential with a carp warning when unavailable.
  671:                       # Has no effect on the SQLite backend (which uses a single SQL
  672:                       # JOIN).  DBI-backed DAs are not thread-safe by default; only
  673:                       # enable this for in-memory or otherwise thread-safe DA backends.
  674:                       #
  675:                       # DOMAIN -- EP valid:   0 (sequential, default) or 1 (parallel).
  676:                       # DOMAIN -- EP invalid: any other integer is treated as truthy/falsy.
  677:                       # DOMAIN -- Default:    0.
  678: 
  679:     logger         => { type => 'object',   optional => 1 }
  680:                       # Logger object propagated to all component databases.
  681: 
  682:     i18n           => { type => 'object',   optional => 1 }
  683:                       # Localisation object with a translate($key, @args) method.
  684: 
  685: =head4 Output
  686: 
  687:     A blessed Database::Join object.
  688: 
  689: =head3 EXAMPLE
  690: 
  691:     # Customers database: entry | name | email
  692:     # Loyalty   database: entry | tier | points
  693: 
  694:     my $join = Database::Join->new(
  695:         databases      => [ $customers, $loyalty ],
  696:         join_column    => 'entry',
  697:         join_type      => 'inner',            # only customers who also have loyalty records
  698:         remove_columns => [ 'email' ],        # hide PII from query results
  699:         filters        => { 1 => { points => { '>' => 0 } } }, # ignore zero-point records
  700:     );
  701: 
  702:     my $rows = $join->selectall_arrayref();
  703:     # Each row: { entry => ..., name => ..., tier => ..., points => ... }
  704:     # 'email' is absent. Zero-point loyalty records are excluded.
  705: 
  706: =head3 PSEUDOCODE
  707: 
  708:     validate all parameters with validate_strict
  709:     croak if databases is empty
  710:     croak if any element of databases is not a Database::Abstraction subclass
  711:     bless the object with all fields initialised
  712:     call _build_col_index to map every column to its owning database
  713:         and verify join_column presence in each database
  714:     if base_criteria given:
  715:         partition base_criteria by column ownership into per-db slices
  716:         for each database with a non-empty slice:
  717:             merge the slice into _filters[i]
  718:             (explicit filters win on plain-scalar conflicts)
  719:     for each column in remove_columns: call remove_column
  720:     return the new object
  721: 
  722: =head3 MESSAGES
  723: 
  724:     error_no_databases        -- databases arrayref was empty
  725:     error_invalid_db          -- an element of databases is not a D::A subclass
  726:     error_join_col_missing    -- join_column (or its join_map alias) not found in a database
  727:     error_invalid_backend     -- backend value is not 'array', 'sqlite', or 'auto'
  728:     error_sqlite_connect      -- temporary SQLite database could not be created (backend='sqlite'/'auto')
  729:     warn_schema_type_mismatch -- (carp) a shared column has different types across databases;
  730:                                  use collision_prefix to preserve both values
  731: 
  732: =cut
  733: 
  734: sub new {
●735 → 780 → 794  735: 	my ($class, @args) = @_;
  736: 
  737: 	my $p = validate_strict(
  738: 		schema => {
  739: 			# databases	=> { type => 'arrayref', element_type => 'object' },
  740: 			databases	=> { type => 'arrayref' },
  741: 			join_column	=> { type => 'string',   optional => 1, default => 'entry' },
  742: 			join_type	=> {
  743: 				type => 'string',
  744: 				optional => 1,
  745: 				default => 'left',
  746: 				enum => ['inner', 'left', 'outer']
  747: 			},
  748: 			join_map	      => { type => 'hashref',  optional => 1 },
  749: 			filters		      => { type => 'hashref',  optional => 1 },
  750: 			base_criteria     => { type => 'hashref',  optional => 1 },
  751: 			collision_prefix  => { type => 'hashref',  optional => 1 },
  752: 			remove_columns	  => { type => 'arrayref', optional => 1 },
  753: 			backend	=> {
  754: 				type => 'string',
  755: 				optional => 1,
  756: 				default => 'auto',
  757: 				enum => ['array', 'sqlite', 'auto']
  758: 			},
  759: 			max_array_rows    => { type => 'integer',  optional => 1, default => 10_000 },
  760: 			tmpdir            => { type => 'string',   optional => 1 },
  761: 			parallel          => { type => 'integer',  optional => 1, default => 0 },
  762: 			logger		=> { type => 'object',   optional => 1 },
  763: 			i18n		=> { type => 'object',   optional => 1 },
  764: 		},
  765: 		input => get_params(undef, \@args) // {},
  766: 	);
  767: 
  768: 	croak _msg($p->{i18n}, 'error_no_databases')
  769: 		unless @{ $p->{databases} };
  770: 
  771: 	# Capture caller-supplied logger and i18n object BEFORE Object::Configure::configure
  772: 	# overwrites them with class-level defaults.  configure() reads default values
  773: 	# from Database::Abstraction's class config and silently replaces any caller-
  774: 	# supplied value if a class default exists for that key.
  775: 	my $caller_logger = $p->{logger};
  776: 	my $caller_i18n   = $p->{i18n};
  777: 
  778: 	$p = Object::Configure::configure($class, $p);
  779: 
  780: 	for my $i (0 .. $#{ $p->{databases} }) {
  781: 		croak _msg($p->{i18n}, 'error_invalid_db', $i)
  782: 			unless blessed($p->{databases}[$i])
  783: 			    && $p->{databases}[$i]->can('selectall_arrayref')
  784: 			    && $p->{databases}[$i]->can('columns');
  785: 	}
  786: 
  787: 	# Cache the primary database's internal primary-key column name once at
  788: 	# construction.  AUTOLOAD uses this to map a bare positional argument to the
  789: 	# correct column when join_map is active and the primary DB's primary key
  790: 	# differs from join_column (e.g. join_col='statecode' but primary key='entry').
  791: 	# Accessing {id} here — at construction time, before the object is shared —
  792: 	# is the single permitted point of coupling to DA's internal field; caching
  793: 	# avoids repeating the hash intrusion on every AUTOLOAD call.
●794 → 832 → 846  794: 	my $primary_pk = $p->{databases}[0]{id} // $p->{join_column};
  795: 
  796: 	my $self = bless {
  797: 		_dbs          => $p->{databases},
  798: 		_join_col     => $p->{join_column},
  799: 		_join_type    => $p->{join_type},
  800: 		_join_map     => $p->{join_map} // {},	# db_index => local join col name
  801: 		# Security: deep-copy filters so post-construction mutation of the caller's
  802: 		# hashref cannot silently bypass the inner-join row-security guarantee.
  803: 		# Two-level copy mirrors the broadcast-copy idiom in _partition_criteria:
  804: 		# outer keys are db indices (integers); inner values are criteria hashrefs
  805: 		# whose operator sub-hashrefs are also shallow-copied one level deeper.
  806: 		_filters          => _copy_filters($p->{filters}),	# db_index => criteria hashref
  807: 		_collision_prefix => $p->{collision_prefix} // {},	# db_index => prefix string
  808: 		_logger           => $caller_logger,
  809: 		_i18n             => $caller_i18n,
  810: 		_col_db           => {},	# published_col_name => db_index
  811: 		_db_cols          => [],	# per-db column-presence hashref
  812: 		_removed_cols     => {},	# published_col_name => 1 (hidden from view)
  813: 		_col_cache        => undef,	# memoised columns() result
  814: 		_schema_cache     => undef,	# memoised schema() result
  815: 		_col_rename       => [],	# per-db: { orig_col => published_col } for collisions
  816: 		_col_unrename     => [],	# per-db: { published_col => orig_col } reverse map
  817: 		_autoload_pk      => $primary_pk,	# primary DB's key col; positional arg for AUTOLOAD
  818: 		_backend          => $p->{backend},
  819: 		_max_array_rows   => $p->{max_array_rows},
  820: 		_tmpdir           => $p->{tmpdir} // File::Spec->tmpdir,
  821: 		_parallel         => $p->{parallel} // 0,
  822: 	}, $class;
  823: 
  824: 	$self->_build_col_index();
  825: 	$self->_validate_schema_types();
  826: 
  827: 	# base_criteria: partition by column ownership into _filters.
  828: 	# Security: _copy_criteria() deep-copies before partitioning so
  829: 	# post-construction mutation of the caller's hashref cannot bypass filters.
  830: 	# Explicit filters (db-indexed) take precedence: they are treated as
  831: 	# "extra" in _merge_criteria so their values win on plain-scalar conflicts.
  832: 	if (my $bc = $p->{base_criteria}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
  833: 		my $partitioned = $self->_partition_criteria(_copy_criteria($bc));
  834: 		for my $i (0 .. $#{ $self->{_dbs} }) {
  835: 			next unless %{ $partitioned->[$i] };
  836: 			$self->{_filters}{$i} = _merge_criteria(
  837: 				$partitioned->[$i],
  838: 				$self->{_filters}{$i} // {},
  839: 			);
  840: 		}
  841: 	}
  842: 
  843: 	# Propagate the logger to every component database if one was supplied.
  844: 	# set_logger() is used here (rather than a direct hash write) to honour each
  845: 	# DA's own logging setup hook and remain decoupled from DA internals.
●846 → 846 → 851  846: 	if (my $log = $self->{_logger}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
  847: 		$_->set_logger($log) for @{ $self->{_dbs} };
  848: 	}
  849: 
  850: 	# Apply column removals requested in the constructor
●851 → 851 → 855  851: 	if (my $rc = $p->{remove_columns}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
  852: 		$self->remove_column($_) for @{$rc};
  853: 	}
  854: 
  855: 	return $self;
  Mutants (Total: 2, Killed: 2, Survived: 0)
  856: }
  857: 
  858: # ---------------------------------------------------------------------------
  859: # Public API (mirrors Database::Abstraction)
  860: # ---------------------------------------------------------------------------
  861: 
  862: =head2 join_map - joining on differently-named columns
  863: 
  864: By default every component database must have a column whose name matches
  865: C<join_column>.  If a database uses a different local name for the join key,
  866: declare the mapping with C<join_map>.
  867: 
  868: C<join_map> is a hashref.  Each B<key> is the B<zero-based position> of a
  869: database in the C<databases> array (0 = first, 1 = second, and so on).  Each
  870: B<value> is the name that B<that particular database> uses for the join key.
  871: 
  872: Databases not listed in C<join_map> are assumed to already have a column
  873: named C<join_column> and need no entry.
  874: 
  875: Throughout the merged view the join key is I<always> referred to by the name
  876: given in C<join_column>.  The local alias is never exposed in returned rows,
  877: in C<columns()>, or in C<schema()>.
  878: 
  879: B<When do you need join_map?>
  880: 
  881: You need C<join_map> when you have two tables like:
  882: 
  883:     cities table  : entry (the city name) | statecode
  884:     stnames table : entry (the state code) | state
  885: 
  886: Here you want to join cities.statecode to stnames.entry.  You choose
  887: C<< join_column => 'statecode' >> as the canonical name, but stnames calls
  888: that same concept C<entry>, so you declare:
  889: 
  890:     join_map => { 1 => 'entry' }  # stnames (index 1) calls it 'entry'
  891: 
  892: B<Example>
  893: 
  894:     #                        index 0     index 1
  895:     my @databases = (       $cities,    $stnames  );
  896:     #  join key column:    'statecode'  'entry'
  897:     #  join_column:        'statecode' (chosen canonical name)
  898:     #  stnames differs, so declare the alias:
  899: 
  900:     my $join = Database::Join->new(
  901:         databases   => \@databases,
  902:         join_column => 'statecode',
  903:         join_map    => { 1 => 'entry' },
  904:     );
  905: 
  906:     my $rows = $join->selectall_arrayref();
  907:     # Each $row has keys: entry (city), statecode, state
  908:     # 'entry' from stnames is never exposed directly.
  909: 
  910:     my $row = $join->fetchrow_hashref(statecode => 'CA');
  911: 
  912: B<Using add_database instead>
  913: 
  914: If you build the join incrementally with C<add_database>, pass
  915: C<join_column> directly to that call instead of using C<join_map>:
  916: 
  917:     my $join = Database::Join->new(
  918:         databases   => [ $cities ],
  919:         join_column => 'statecode',
  920:     );
  921:     $join->add_database($stnames, join_column => 'entry');
  922: 
  923: This is exactly equivalent to the C<join_map> form above.
  924: 
  925: =head2 filters - permanent per-database row filters
  926: 
  927: C<filters> lets you restrict a component database to a subset of its rows
  928: permanently, without repeating the criterion on every query call.
  929: 
  930: Think of it as telling the join: "whenever you query this database, always
  931: add these extra conditions".  Callers never need to specify the restriction
  932: themselves and can never accidentally omit it.
  933: 
  934: C<filters> is a hashref.  Each B<key> is the B<zero-based position> of a
  935: database in the C<databases> array (same numbering as C<join_map>).  Each
  936: B<value> is a criteria hashref in the same format as C<selectall_arrayref>
  937: accepts.
  938: 
  939: B<Key-set semantics>
  940: 
  941: A filtered database always acts as an inner-join partner, regardless of the
  942: C<join_type> setting.  Any join-key value that does not pass the filter is
  943: excluded from the merged output entirely -- not just missing its secondary
  944: columns.  This ensures the filter genuinely restricts the view rather than
  945: simply hiding a few fields.
  946: 
  947: B<Criteria merging>
  948: 
  949: When a query call also passes a criterion for a column that already has a base
  950: filter, the two constraints are combined:
  951: 
  952: =over 4
  953: 
  954: =item *
  955: 
  956: When both the base filter value and the query criterion are operator hashrefs
  957: (e.g. C<< { '>' => 60 } >> and C<< { '<' => 365 } >>), their operators are
  958: merged: I<both> constraints apply simultaneously (AND semantics).
  959: 
  960: =item *
  961: 
  962: When either value is a plain scalar, or the operators conflict, the
  963: query-time criterion wins and the base filter for that column is ignored for
  964: that one call.
  965: 
  966: =back
  967: 
  968: B<Example -- only show orders placed more than 60 days ago>
  969: 
  970:     my $join = Database::Join->new(
  971:         databases   => [ $customers, $orders ],
  972:         join_column => 'entry',
  973:         filters     => { 1 => { age_days => { '>' => 60 } } },
  974:     );
  975: 
  976:     # Every query automatically sees only old orders
  977:     my $rows = $join->selectall_arrayref();
  978: 
  979:     # Additional criteria layer on top -- gold tier AND old order
  980:     my $vip  = $join->selectall_arrayref(tier => 'gold');
  981: 
  982:     # Range intersection: age_days > 60 AND age_days < 365
  983:     my $mid  = $join->selectall_arrayref(age_days => { '<' => 365 });
  984: 
  985: When using C<add_database>, pass C<filter> (singular) to set the base
  986: criteria for the new database:
  987: 
  988:     $join->add_database($orders, filter => { age_days => { '>' => 60 } });
  989: 
  990: =head2 base_criteria - permanent view-level row filters by column name
  991: 
  992: C<base_criteria> is a convenience alternative to C<filters> for callers who
  993: know the column names they want to restrict but prefer not to track database
  994: indices.
  995: 
  996:     my $join = Database::Join->new(
  997:         databases     => [ $customers, $orders ],
  998:         join_column   => 'entry',
  999:         base_criteria => { active => 1, deleted_at => undef },
 1000:     );
 1001: 
 1002:     # Every query automatically sees only active, non-deleted rows.
 1003:     my $rows = $join->selectall_arrayref();
 1004: 
 1005: At construction time C<base_criteria> is partitioned by column ownership
 1006: using the same routing logic as C<selectall_arrayref>.  Each criterion is
 1007: sent to the database that owns that column and stored as a permanent base
 1008: filter (equivalent to the corresponding C<filters> entry).
 1009: 
 1010: B<Unknown columns> emit a C<warn_unknown_column> carp and are silently
 1011: dropped, just as they would be in a query call.
 1012: 
 1013: B<Key-set semantics> are identical to C<filters>: any database that receives
 1014: a C<base_criteria> slice acts as an inner-join partner regardless of
 1015: C<join_type>.
 1016: 
 1017: B<Precedence>: when a column appears in both C<base_criteria> and C<filters>,
 1018: the C<filters> entry wins on plain-scalar conflicts; operator-hashref values
 1019: are combined with AND semantics.
 1020: 
 1021: B<Use cases>
 1022: 
 1023: =over 4
 1024: 
 1025: =item *
 1026: 
 1027: Row-level security: C<< base_criteria => { tenant_id => $tid } >>
 1028: 
 1029: =item *
 1030: 
 1031: Soft-delete filtering: C<< base_criteria => { deleted_at => undef } >>
 1032: 
 1033: =item *
 1034: 
 1035: Status gates: C<< base_criteria => { active => 1 } >>
 1036: 
 1037: =back
 1038: 
 1039: =head2 collision_prefix - preserve colliding columns from secondary databases
 1040: 
 1041: By default, when a column name appears in more than one database the I<last>
 1042: database wins: its value silently overwrites earlier ones in merged rows.
 1043: This loses data and makes the origin invisible.
 1044: 
 1045: C<collision_prefix> changes this for secondary databases you designate.
 1046: When a secondary database at index N has a column that already exists in the
 1047: merged view, and C<collision_prefix-E<gt>{N}> is set, the colliding column is
 1048: published as C<"$prefix.$col"> instead of overwriting.  Both values are then
 1049: visible: the original column keeps its name (from the earlier database), and
 1050: the collision gets the prefixed name.
 1051: 
 1052: C<collision_prefix> is a hashref.  Each B<key> is the B<zero-based index> of
 1053: a secondary database in the C<databases> array (same numbering as C<join_map>).
 1054: Each B<value> is the prefix string to prepend.  An index-0 entry is
 1055: meaningless and silently ignored.  Omitting C<collision_prefix> entirely
 1056: preserves the previous last-wins behaviour and changes nothing.
 1057: 
 1058: Non-colliding columns from a secondary database are always added as-is with
 1059: no prefix, whether or not C<collision_prefix> is configured.
 1060: 
 1061: B<Example -- sales table and products table, both with a "product" column>
 1062: 
 1063:     # $sales    columns: id, product, amount, date
 1064:     # $products columns: sku, product, price, category
 1065:     # join on 'product' (left key) matched against 'sku' (right key via join_map)
 1066: 
 1067:     my $join = Database::Join->new(
 1068:         databases        => [$sales, $products],
 1069:         join_column      => 'product',
 1070:         join_map         => { 1 => 'sku' },
 1071:         collision_prefix => { 1 => 'products' },
 1072:     );
 1073: 
 1074:     $join->columns;
 1075:     # => ['amount', 'category', 'date', 'id', 'price', 'product', 'products.product']
 1076:     #                                                               ^-- prefixed collision
 1077: 
 1078:     my $row = $join->fetchrow_hashref(product => 'widget');
 1079:     # $row->{product}            -- value from $sales
 1080:     # $row->{'products.product'} -- value from $products (different row, same column name)
 1081:     # $row->{price}              -- from $products, no collision, kept as-is
 1082: 
 1083: B<Querying on a prefixed column>
 1084: 
 1085: Use the full published name as the criterion key:
 1086: 
 1087:     my $rows = $join->selectall_arrayref('products.product' => 'widget');
 1088:     # Internally routes as: product => 'widget' to $products
 1089: 
 1090: B<Interaction with remove_column>
 1091: 
 1092: C<remove_column> operates on published names.  To suppress a prefixed
 1093: collision column entirely, pass the prefixed name:
 1094: 
 1095:     $join->remove_column('products.product');
 1096: 
 1097: =head2 backend - SQLite join backend for large datasets
 1098: 
 1099: C<Database::Join> can merge component databases in two different ways,
 1100: controlled by the C<backend> constructor parameter.
 1101: 
 1102: =over 4
 1103: 
 1104: =item C<backend =E<gt> 'array'> -- in-memory merge (original behaviour)
 1105: 
 1106: All matching rows are fetched from every component database into Perl hashes
 1107: and merged there.  Simple and fast for small and medium datasets.  Peak RAM
 1108: is roughly three times the combined source data size (one copy per database
 1109: plus one merged copy).
 1110: 
 1111: =item C<backend =E<gt> 'sqlite'> -- SQL JOIN via a cached temporary file
 1112: 
 1113: C<Database::Join> creates a temporary SQLite database file, spills source
 1114: rows into it (one table per component database), then executes a single SQL
 1115: C<JOIN> statement per query call.  Peak RAM drops to roughly one times the
 1116: source data size.
 1117: 
 1118: The temporary file is created once and reused across multiple query calls on
 1119: the same object (the cache).  Only query-time criteria vary per call; they
 1120: are applied as SQL C<WHERE> clauses against the cached data.  The cache is
 1121: automatically rebuilt when any source database's C<updated()> timestamp
 1122: changes.  The file is deleted when the object is destroyed (goes out of
 1123: scope).  See I<Temporary file: name, location, and lifetime> below for
 1124: details.
 1125: 
 1126: Requires C<DBD::SQLite E<gt>= 1.70> (C<FULL OUTER JOIN> support was added in
 1127: SQLite 3.39.0; DBD::SQLite 1.70 ships SQLite 3.39.2).
 1128: 
 1129: =item C<backend =E<gt> 'auto'> (default)
 1130: 
 1131: C<Database::Join> counts the total rows from all component databases cheaply
 1132: -- without fetching them -- and then decides:
 1133: 
 1134: =over 4
 1135: 
 1136: =item *
 1137: 
 1138: If the combined count is less than or equal to C<max_array_rows> (default
 1139: 10,000), use the array path.
 1140: 
 1141: =item *
 1142: 
 1143: If the combined count exceeds C<max_array_rows>, use the SQLite path.
 1144: 
 1145: =back
 1146: 
 1147: For counting to work without fetching, each component database must either
 1148: implement the C<dbi_source()> interface (for SQLite-backed sources, where a
 1149: C<COUNT(*)> SQL query is issued directly), or directly define a C<count()>
 1150: method in its own package -- not just inherit one from a parent class.  If
 1151: neither is available for a particular database, C<Database::Join> plays it
 1152: safe and uses the array path for the whole query without fetching any rows.
 1153: 
 1154: =back
 1155: 
 1156: B<Choosing max_array_rows>
 1157: 
 1158: The default of 10,000 is a reasonable starting point.  Adjust it to match
 1159: your hardware and typical row width.  For wide rows (many columns or long
 1160: strings) you may want a lower threshold; for narrow rows you can raise it.
 1161: 
 1162: B<Temporary file: name, location, and lifetime>
 1163: 
 1164: When the SQLite path is active, a single temporary SQLite database file acts
 1165: as the join cache for the life of the C<Database::Join> object.
 1166: 
 1167: B<Name>: the filename is randomly generated by C<File::Temp>, for example:
 1168: 
 1169:     /tmp/Cj8xK7mP2Q.db
 1170: 
 1171: The random portion (ten characters) is chosen automatically to avoid
 1172: collisions.  Only the directory is under your control; you cannot specify
 1173: the filename itself.
 1174: 
 1175: B<Location>: controlled by the C<tmpdir> constructor parameter.
 1176: If C<tmpdir> is not specified, C<File::Spec-E<gt>tmpdir()> is used (usually
 1177: C</tmp> on Unix, or the value of the C<TEMP> or C<TMP> environment variable
 1178: on Windows).
 1179: 
 1180: B<Lifetime: one file per object, deleted when the object is destroyed>: the
 1181: file is created on the first query call that uses the SQLite path and kept
 1182: alive until the C<Database::Join> object is destroyed (i.e. when it goes out
 1183: of scope or is explicitly C<undef>-d).  At most one file exists per object at
 1184: any given moment.  Calling C<selectall_arrayref()> ten times on the same
 1185: object creates and uses I<one> file, not ten.
 1186: 
 1187: B<Cache invalidation>: the cache is automatically rebuilt (the old file is
 1188: replaced with a new one) when any source database's C<updated()> return value
 1189: changes, or when C<add_database()> is called.  Base-filter criteria
 1190: (C<filters> constructor parameter) are applied once at build time for spilled
 1191: sources; query-time criteria are applied per-call as SQL C<WHERE> clauses.
 1192: 
 1193: To use a different directory -- for example a RAM-backed filesystem or a
 1194: faster local disk:
 1195: 
 1196:     my $join = Database::Join->new(
 1197:         databases      => [ $db1, $db2 ],
 1198:         join_column    => 'entry',
 1199:         backend        => 'sqlite',
 1200:         tmpdir         => '/dev/shm',     # Linux RAM disk
 1201:     );
 1202: 
 1203: B<Parallel secondary fetches (C<parallel> constructor parameter)>
 1204: 
 1205: By default, component databases are queried sequentially - the primary first,
 1206: then each secondary in order.  When the component databases are network- or
 1207: disk-backed and have non-trivial per-query latency, the sequential fetch means
 1208: total latency is the I<sum> of all per-DA latencies.
 1209: 
 1210: Setting C<< parallel => 1 >> in the constructor enables concurrent fetching
 1211: of secondary databases using Perl C<threads>.  With C<parallel => 1> and
 1212: two or more secondary databases (C<n E<gt> 2> total), secondary fetches run in
 1213: parallel after the primary fetch completes; total latency drops to
 1214: I<max(secondary latencies)> instead of I<sum(secondary latencies)>.
 1215: 
 1216:     my $join = Database::Join->new(
 1217:         databases   => [ $customers, $loyalty, $scores ],   # 3 DAs - 2 secondaries
 1218:         join_column => 'entry',
 1219:         parallel    => 1,    # loyalty and scores fetched concurrently
 1220:     );
 1221: 
 1222: Requirements and caveats:
 1223: 
 1224: =over 4
 1225: 
 1226: =item *
 1227: 
 1228: The C<threads> module must be available.  Most distributions ship it, but it
 1229: requires a Perl binary compiled with C<-Dusethreads>.  When threads are
 1230: unavailable, a C<carp> warning is emitted and fetching falls back to
 1231: sequential; the result is identical, only slower.
 1232: 
 1233: =item *
 1234: 
 1235: Parallel fetching is only active when the join has three or more total
 1236: databases (C<n E<gt> 2>).  With two databases (one secondary), the thread
 1237: creation overhead exceeds the benefit of concurrency; sequential is used
 1238: regardless of C<parallel>.
 1239: 
 1240: =item *
 1241: 
 1242: Component databases must be safe to call from Perl threads.  In-memory
 1243: databases (CSV, JSON, TSV after slurp) are safe.  DBI-backed databases
 1244: whose handles were created in the same thread may not be safe - consult your
 1245: DBD driver's thread documentation.  The array backend is recommended for
 1246: DBI-backed sources; the SQLite backend performs its join in a single SQL
 1247: statement and does not use parallel fetching.
 1248: 
 1249: =item *
 1250: 
 1251: C<parallel => 1> applies to the array backend only.  The SQLite backend
 1252: performs a single SQL JOIN after spilling source data, so per-DA parallelism
 1253: is irrelevant.
 1254: 
 1255: =back
 1256: 
 1257: B<Zero-copy ATTACH (C<dbi_source()> interface)>
 1258: 
 1259: Normally, when the SQLite path is active, rows from each component database
 1260: are fetched one by one and inserted into the temporary SQLite file.  This is
 1261: efficient but does involve INSERT overhead.
 1262: 
 1263: If a component database is itself SQLite-backed and implements a
 1264: C<dbi_source()> method, C<Database::Join> can skip the row-by-row copy
 1265: entirely and instead use C<ATTACH DATABASE> to link the source file directly
 1266: to the temporary join connection.  This is the zero-copy path and is
 1267: significantly faster for large SQLite sources.
 1268: 
 1269: The C<dbi_source()> method must return a hashref with two keys:
 1270: 
 1271: =over 4
 1272: 
 1273: =item C<dbh>
 1274: 
 1275: A connected C<DBD::SQLite> database handle (C<DBI> connection object).
 1276: 
 1277: =item C<table>
 1278: 
 1279: The name of the table in that database that holds the source rows.
 1280: 
 1281: =back
 1282: 
 1283: Example implementation:
 1284: 
 1285:     package My::SQLiteDatabase;
 1286:     use parent 'Database::Abstraction';
 1287: 
 1288:     sub dbi_source {
 1289:         my ($self) = @_;
 1290:         return {
 1291:             dbh   => $self->{_dbh},      # connected DBD::SQLite handle
 1292:             table => $self->{_table},    # table name in that database
 1293:         };
 1294:     }
 1295: 
 1296:     1;
 1297: 
 1298: The zero-copy ATTACH path is always used when a component database implements
 1299: C<dbi_source()>.  Query-time criteria are applied as parameterised SQL
 1300: C<WHERE> clauses against the ATTACHed source table, so no row-level copy is
 1301: needed even when the current call includes column filters.  (Prior to 0.005.0
 1302: any query-time criteria forced a spill; that restriction was removed in
 1303: 0.005.0.)
 1304: 
 1305: B<Result identity>
 1306: 
 1307: Both the array path and the SQLite path produce identical results for any
 1308: given query.  You can switch between them freely without changing callers.
 1309: The C<collision_prefix> column renaming, C<join_map> key translation, and all
 1310: three join types (left, inner, outer) work identically on both paths.
 1311: 
 1312: =head2 selectall_arrayref
 1313: 
 1314: =head3 SYNOPSIS
 1315: 
 1316:     my $rows = $join->selectall_arrayref();
 1317:     my $rows = $join->selectall_arrayref(tier  => 'gold');
 1318:     my $rows = $join->selectall_arrayref(score => { '>' => 80 });
 1319:     my $rows = $join->selectall_arrayref('C001');  # positional: entry => 'C001'
 1320:     my $rows = $join->selectall_arrayref(sort_by => 'name');
 1321:     my $rows = $join->selectall_arrayref(tier => 'gold', sort_by => ['score', 'DESC']);
 1322:     my $rows = $join->selectall_arrayref(limit => 10);
 1323:     my $rows = $join->selectall_arrayref(limit => 10, offset => 20);
 1324:     my $rows = $join->selectall_arrayref(tier => 'gold', sort_by => 'name', limit => 5);
 1325: 
 1326: =head3 DESCRIPTION
 1327: 
 1328: Returns an arrayref of hashrefs representing the merged view of all component
 1329: databases, optionally filtered by the given criteria.
 1330: 
 1331: Criteria for columns that live in different databases are routed
 1332: automatically: each database is queried with only the criteria that apply to
 1333: its own columns.  The results are combined in memory using C<join_column>.
 1334: 
 1335: Accepts the same criteria syntax as C<Database::Abstraction::selectall_arrayref>.
 1336: A single plain scalar argument is interpreted as the C<join_column> value
 1337: (equivalent to C<< entry => 'C001' >> when C<join_column> is C<'entry'>).
 1338: 
 1339: =head3 API SPECIFICATION
 1340: 
 1341: =head4 Input
 1342: 
 1343:     Calling conventions (in order of precedence):
 1344:       1. No arguments             -- returns all rows
 1345:       2. One plain scalar         -- shorthand for join_column => $scalar
 1346:       3. Key-value pairs or
 1347:          a criteria hashref       -- routed per-database
 1348: 
 1349:     Values may be:
 1350:       Plain scalar                -- exact match
 1351:       Hashref of operators        -- e.g. { '>' => 80 }
 1352: 
 1353:     Optional parameters (mixed in with any of the above):
 1354:       sort_by => 'colname'            -- sort ascending by that column
 1355:       sort_by => ['colname', 'DESC']  -- sort descending
 1356:       sort_by => ['colname', 'ASC']   -- sort ascending (explicit)
 1357:       limit    => N                    -- return at most N rows (positive integer)
 1358:       offset   => M                   -- skip the first M rows (non-negative integer)
 1359: 
 1360:     The column named in sort_by must be present in the merged view (i.e. it
 1361:     must appear in columns()).  An unknown column or an invalid direction emits
 1362:     a carp warning and falls back to the default join_column ascending sort.
 1363: 
 1364:     limit and offset are applied after ordering.  offset without limit skips
 1365:     rows but returns all remaining rows.  limit without offset starts from
 1366:     the first qualifying row.  An invalid limit or offset emits a carp warning
 1367:     and the parameter is ignored (treated as absent).
 1368: 
 1369:     DOMAIN -- sort_by:
 1370:       EP absent:          result sorted by join_column ASC (default).
 1371:       EP string:          any column name in columns(); sorts ASC by that column.
 1372:       EP ['col','ASC']:   explicit ascending; equivalent to the string form.
 1373:       EP ['col','DESC']:  descending sort by the named column.
 1374:       EP ['col']:         single-element arrayref; direction defaults to ASC.
 1375:       EP []:              empty arrayref; column is undef -> carp + join_col ASC fallback.
 1376:       EP invalid column:  column not in columns() -> carp + join_col ASC fallback.
 1377:       EP invalid dir:     direction not 'ASC' or 'DESC' -> carp + ASC used.
 1378:       Sort is lexicographic (cmp); use backend=>'sqlite' for numeric ORDER BY.
 1379: 
 1380:     DOMAIN -- limit:
 1381:       EP absent:          no truncation; all qualifying rows are returned.
 1382:       EP 0:               not a positive integer -> carp + ignored (all rows returned).
 1383:       BVA min valid = 1:  exactly 1 row returned.
 1384:       BVA at count:       limit == total rows -> all rows returned (no truncation).
 1385:       BVA above count:    limit > total rows -> all rows returned.
 1386:       EP invalid:         negative integer, float string, or non-numeric string
 1387:                           => carp + ignored (all rows returned).
 1388:       Valid domain:       integers in [1, INF); matched by /^\d+\z/a with value >= 1.
 1389: 
 1390:     DOMAIN -- offset:
 1391:       EP absent:          no rows skipped; result starts from row 0.
 1392:       BVA min valid = 0:  no rows skipped (zero is a valid non-negative integer).
 1393:       BVA offset=1:       first row skipped; result starts from row 1.
 1394:       BVA offset=N-1:     N-1 rows skipped; only the last row returned.
 1395:       BVA offset=N:       all N rows skipped; empty result returned.
 1396:       BVA offset>N:       all rows skipped; empty result returned.
 1397:       EP invalid:         negative integer, float string, or non-numeric string
 1398:                           => carp + ignored (no rows skipped).
 1399:       Valid domain:       integers in [0, INF); matched by /^\d+\z/a.
 1400: 
 1401: =head4 Output
 1402: 
 1403:     Arrayref of hashrefs; one hashref per qualifying merged row.
 1404:     Sorted ascending by join_column by default; caller-controlled via sort_by.
 1405:     At most C<limit> rows when limit is given; the first C<offset> rows are
 1406:     skipped when offset is given.
 1407:     Returns a reference to an empty array when no rows match.
 1408: 
 1409: =head3 EXAMPLE
 1410: 
 1411:     # All rows from both databases
 1412:     my $all = $join->selectall_arrayref();
 1413: 
 1414:     # Only rows where the 'tier' column (from the loyalty database)
 1415:     # equals 'gold' -- the criterion is routed to the right database
 1416:     my $vip = $join->selectall_arrayref(tier => 'gold');
 1417: 
 1418:     # Operator hashref: score > 80
 1419:     my $high = $join->selectall_arrayref(score => { '>' => 80 });
 1420: 
 1421:     # Access each merged row
 1422:     for my $row (@{$vip}) {
 1423:         printf "%-10s tier=%-8s score=%d\n",
 1424:             $row->{entry}, $row->{tier}, $row->{score} // 0;
 1425:     }
 1426: 
 1427: =head3 MESSAGES
 1428: 
 1429:     warn_unknown_column (carp)
 1430:         -- A criterion key names a column not present in any component database;
 1431:            the criterion is silently dropped and all rows are returned.
 1432:     error_join_criterion (croak)
 1433:         -- A criterion hashref contains a join => key (DA SQL-JOIN syntax).
 1434:            Use a separate DA object for the joined table instead.
 1435:     sort_by column unknown (carp)
 1436:         -- The column given in sort_by is not in the merged view; the result
 1437:            is returned in the default join_column ascending order instead.
 1438:     sort_by direction invalid (carp)
 1439:         -- The direction given in sort_by is not 'ASC' or 'DESC'; ASC is used.
 1440:     limit invalid (carp)
 1441:         -- The value given for limit is not a positive integer; it is ignored.
 1442:     offset invalid (carp)
 1443:         -- The value given for offset is not a non-negative integer; it is ignored.
 1444:     operator-unsupported (carp, SQLite/auto path only)
 1445:         -- An operator hashref key is not in the supported set (>, <, >=, <=,
 1446:            !=, =, LIKE, NOT LIKE, IS NULL, IS NOT NULL, IN, NOT IN); the
 1447:            individual operator term is dropped from the WHERE clause (other
 1448:            operators in the same hashref still apply).
 1449:     IN/NOT IN wrong value type (carp, SQLite/auto path only)
 1450:         -- An IN or NOT IN criterion was given a non-arrayref value; the operator
 1451:            term is skipped.
 1452:     error_sqlite_connect (croak, SQLite/auto path only)
 1453:         -- The temporary SQLite join file could not be created; check tmpdir
 1454:            permissions and available disk space.
 1455: 
 1456: =cut
 1457: 
 1458: sub selectall_arrayref {
 1459: 	my ($self, @args) = @_;
 1460: 	my $params   = $self->_parse_query_args(undef, @args);
 1461: 	my $sort_by = delete $params->{sort_by};
 1462: 	my $limit    = delete $params->{limit};
 1463: 	my $offset   = delete $params->{offset};
 1464: 	return $self->_joined_query($params, sort_by => $sort_by, limit => $limit, offset => $offset);
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1465: }
 1466: 
 1467: =head2 selectall_array
 1468: 
 1469: =head3 SYNOPSIS
 1470: 
 1471:     my @rows = $join->selectall_array(tier => 'gold');
 1472: 
 1473:     # Scalar context: only the first matching row
 1474:     my $first = $join->selectall_array(entry => 'C001');
 1475: 
 1476: =head3 DESCRIPTION
 1477: 
 1478: In list context returns a list of merged hashrefs -- the same rows that
 1479: C<selectall_arrayref> would return, just as a flat list rather than an
 1480: arrayref.
 1481: 
 1482: In scalar context returns only the first matching hashref (or C<undef> if
 1483: nothing matches).
 1484: 
 1485: =head3 API SPECIFICATION
 1486: 
 1487: =head4 Input
 1488: 
 1489:     Same as selectall_arrayref.
 1490: 
 1491: =head4 Output
 1492: 
 1493:     List context:   list of hashrefs (may be empty).
 1494:     Scalar context: single hashref or undef.
 1495: 
 1496: =head3 EXAMPLE
 1497: 
 1498:     my @all = $join->selectall_array();
 1499:     print scalar @all, " rows\n";
 1500: 
 1501:     # First gold-tier customer only
 1502:     my $first_vip = $join->selectall_array(tier => 'gold');
 1503:     print $first_vip->{name}, "\n" if defined $first_vip;
 1504: 
 1505: =head3 MESSAGES
 1506: 
 1507: Same messages as C<selectall_arrayref>.
 1508: 
 1509: =cut
 1510: 
 1511: sub selectall_array {
 1512: 	my ($self, @args) = @_;
 1513: 	my $params   = $self->_parse_query_args(undef, @args);
 1514: 	my $sort_by = delete $params->{sort_by};
 1515: 	my $limit    = delete $params->{limit};
 1516: 	my $offset   = delete $params->{offset};
 1517: 	my $rows     = $self->_joined_query($params, sort_by => $sort_by, limit => $limit, offset => $offset);
 1518: 	return wantarray ? @{$rows} : $rows->[0];
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1519: }
 1520: 
 1521: =head2 selectall_hashref
 1522: 
 1523: Deprecated alias for L</selectall_arrayref>, present for compatibility with
 1524: callers written against C<Database::Abstraction>'s deprecated API.  Use
 1525: C<selectall_arrayref> in new code.
 1526: 
 1527: =cut
 1528: 
 1529: sub selectall_hashref {
 1530: 	my $self = shift;
 1531: 	carp 'Database::Join::selectall_hashref is deprecated; use selectall_arrayref';
 1532: 	return $self->selectall_arrayref(@_);
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1533: }
 1534: 
 1535: =head2 selectall_hash
 1536: 
 1537: Deprecated alias for L</selectall_array>, present for compatibility with
 1538: callers written against C<Database::Abstraction>'s deprecated API.  Use
 1539: C<selectall_array> in new code.
 1540: 
 1541: =cut
 1542: 
 1543: sub selectall_hash {
 1544: 	my $self = shift;
 1545: 	carp 'Database::Join::selectall_hash is deprecated; use selectall_array';
 1546: 	return $self->selectall_array(@_);
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1547: }
 1548: 
 1549: =head2 fetchrow_hashref
 1550: 
 1551: =head3 SYNOPSIS
 1552: 
 1553:     my $row = $join->fetchrow_hashref(entry => 'C001');
 1554:     my $row = $join->fetchrow_hashref('C001');   # positional shorthand
 1555: 
 1556: =head3 DESCRIPTION
 1557: 
 1558: Returns a single merged hashref for the first row matching the given
 1559: criteria, or C<undef> when nothing matches.
 1560: 
 1561: Equivalent to calling C<selectall_arrayref> and taking only the first element.
 1562: All the same criteria conventions apply.
 1563: 
 1564: =head3 API SPECIFICATION
 1565: 
 1566: =head4 Input
 1567: 
 1568:     Same as selectall_arrayref.
 1569: 
 1570: =head4 Output
 1571: 
 1572:     Hashref, or undef when no row matches.
 1573: 
 1574: =head3 EXAMPLE
 1575: 
 1576:     my $row = $join->fetchrow_hashref(entry => 'C001');
 1577:     if (defined $row) {
 1578:         print "Name: $row->{name}, Tier: $row->{tier}\n";
 1579:     } else {
 1580:         print "No record for C001\n";
 1581:     }
 1582: 
 1583:     # Positional: works when join_column is 'entry'
 1584:     my $row2 = $join->fetchrow_hashref('C001');
 1585: 
 1586: =head3 MESSAGES
 1587: 
 1588: Same messages as C<selectall_arrayref>.
 1589: 
 1590: =cut
 1591: 
 1592: sub fetchrow_hashref {
 1593: 	my ($self, @args) = @_;
 1594: 	my $params   = $self->_parse_query_args(undef, @args);
 1595: 	my $sort_by = delete $params->{sort_by};
 1596: 	delete $params->{limit};   # fetchrow_hashref always returns one row; limit is meaningless
 1597: 	delete $params->{offset};  # offset would change which row is "first"; not supported here
 1598: 	my $rows     = $self->_joined_query($params, sort_by => $sort_by);
 1599: 	return $rows->[0];
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1600: }
 1601: 
 1602: =head2 count
 1603: 
 1604: =head3 SYNOPSIS
 1605: 
 1606:     my $total  = $join->count();
 1607:     my $active = $join->count(tier => 'gold');
 1608: 
 1609: =head3 DESCRIPTION
 1610: 
 1611: Returns the number of merged rows that satisfy the given criteria.
 1612: 
 1613: On the array backend, the full in-memory join is performed and the resulting
 1614: rows are counted in Perl.  On the SQLite backend, a C<SELECT COUNT(*)> SQL
 1615: query is executed against the cached join tables, avoiding a full row fetch.
 1616: 
 1617: =head3 API SPECIFICATION
 1618: 
 1619: =head4 Input
 1620: 
 1621:     Same criteria syntax as selectall_arrayref.
 1622: 
 1623: =head4 Output
 1624: 
 1625:     Non-negative integer.
 1626: 
 1627: =head3 EXAMPLE
 1628: 
 1629:     my $total   = $join->count();
 1630:     my $gold    = $join->count(tier => 'gold');
 1631:     my $high    = $join->count(score => { '>' => 90 });
 1632: 
 1633:     printf "%d total, %d gold-tier, %d high-scorers\n",
 1634:         $total, $gold, $high;
 1635: 
 1636: =head3 MESSAGES
 1637: 
 1638: Same messages as C<selectall_arrayref>.
 1639: 
 1640: =cut
 1641: 
 1642: sub count {
 1643: 	my ($self, @args) = @_;
 1644: 	my $params = $self->_parse_query_args(undef, @args);
 1645: 	delete $params->{sort_by};  # row ordering is irrelevant for a count
 1646: 	delete $params->{limit};     # count returns total matching rows, not a page
 1647: 	delete $params->{offset};
 1648: 	# On the SQLite path, push COUNT(*) into SQL to avoid fetching all rows.
 1649: 	return $self->_sqlite_join($params, count_only => 1)
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1650: 		unless $self->{_backend} eq 'array';
 1651: 	return scalar @{ $self->_joined_query_array($params) };
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1652: }
 1653: 
 1654: =head2 each_row
 1655: 
 1656: =head3 SYNOPSIS
 1657: 
 1658:     my $count = $join->each_row(sub { my ($row) = @_; ... });
 1659:     my $count = $join->each_row(sub { ... }, tier => 'gold');
 1660:     my $count = $join->each_row(sub { ... }, sort_by => 'name', limit => 100);
 1661: 
 1662: =head3 DESCRIPTION
 1663: 
 1664: Calls C<\&callback> once for every row in the merged view that matches the
 1665: given criteria, then returns the total count of rows visited.
 1666: 
 1667: Accepts the same criteria, C<sort_by>, C<limit>, and C<offset> parameters as
 1668: C<selectall_arrayref>.
 1669: 
 1670: B<Note on memory usage>: C<Database::Join> always materialises the complete
 1671: merged result set before invoking the callback, because merging rows from
 1672: multiple independent sources requires that every source be queried and
 1673: cross-referenced first.  True constant-memory streaming (as C<Database::Abstraction>
 1674: provides on single-source SQL queries) is not possible at the join layer.
 1675: For genuinely large result sets use the SQLite backend (C<backend =E<gt>
 1676: 'sqlite'>), which keeps peak RAM to approximately one times the source data
 1677: size rather than three.
 1678: 
 1679: Any exception thrown inside the callback propagates to the caller after the
 1680: current row; subsequent rows are not visited.
 1681: 
 1682: =head3 API SPECIFICATION
 1683: 
 1684: =head4 Input
 1685: 
 1686:     \&callback   Positional coderef (required).
 1687:                  Called as $callback->($row_hashref) for each merged row.
 1688: 
 1689:     Additional arguments follow the same calling conventions as
 1690:     selectall_arrayref: no args, a single scalar join-column value,
 1691:     or key-value criteria pairs (including sort_by, limit, offset).
 1692: 
 1693: =head4 Output
 1694: 
 1695:     Non-negative integer: the count of rows for which the callback was invoked.
 1696: 
 1697: =head3 EXAMPLE
 1698: 
 1699:     # Print every gold-tier customer's name, sorted by name
 1700:     my $count = $join->each_row(
 1701:         sub { my ($row) = @_; print "$row->{name}\n" },
 1702:         tier    => 'gold',
 1703:         sort_by => 'name',
 1704:     );
 1705:     print "$count gold-tier customers\n";
 1706: 
 1707:     # Accumulate without holding the full result
 1708:     my $total_score = 0;
 1709:     $join->each_row(sub { $total_score += $_[0]->{score} // 0 });
 1710: 
 1711: =head3 MESSAGES
 1712: 
 1713:     error_invalid_callback (croak)
 1714:         -- First argument is not a code reference.
 1715: 
 1716: =cut
 1717: 
 1718: sub each_row {
●1719 → 1727 → 1731 1719: 	my ($self, $cb, @args) = @_;
 1720: 	croak $self->_err('error_invalid_callback')
 1721: 		unless ref($cb) eq 'CODE';
 1722: 	# Materialise the full merged result first; multi-source joins cannot
 1723: 	# stream rows one at a time because the merge key set is not known until
 1724: 	# all sources have been queried and cross-referenced.
 1725: 	my $rows  = $self->selectall_arrayref(@args);
 1726: 	my $count = 0;
 1727: 	for my $row (@{$rows}) {
 1728: 		$cb->($row);
 1729: 		$count++;
 1730: 	}
 1731: 	return $count;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1732: }
 1733: 
 1734: =head2 dbi_source
 1735: 
 1736: =head3 SYNOPSIS
 1737: 
 1738:     # Use a Database::Join object as a zero-copy SQLite source inside a
 1739:     # parent Database::Join, giving the parent ATTACHed-speed access to
 1740:     # the child's joined data without iterating through Perl.
 1741:     my $child  = Database::Join->new(databases => [$da, $db], join_column => 'id', backend => 'sqlite');
 1742:     my $parent = Database::Join->new(databases => [$child, $dc], join_column => 'id', backend => 'sqlite');
 1743: 
 1744: =head3 DESCRIPTION
 1745: 
 1746: Returns a hashref C<< { dbh => $sqlite_dbh, table => '_dj_result' } >> that
 1747: allows a parent C<Database::Join> (or any other caller that understands the
 1748: C<dbi_source()> interface) to ATTACH the child's temporary SQLite database
 1749: file and query the materialised join result directly via SQL, without routing
 1750: rows through Perl.
 1751: 
 1752: The first call builds the SQLite cache (if not already current) and
 1753: materialises the full join result - with C<filters> applied but no query-time
 1754: criteria - into a real table named C<_dj_result> inside the cache file.
 1755: Subsequent calls within the same cache cycle reuse the existing table.
 1756: 
 1757: Returns C<undef> when the backend is C<'array'> (no SQLite file exists).
 1758: 
 1759: =head3 API SPECIFICATION
 1760: 
 1761: =head4 Input
 1762: 
 1763:     None.
 1764: 
 1765: =head4 Output
 1766: 
 1767:     On the SQLite/auto backend:
 1768:       Hashref with keys:
 1769:         dbh   => DBI handle to the child's temporary SQLite database file.
 1770:         table => '_dj_result'  (the materialized join table inside that file).
 1771:     On the array backend:
 1772:       undef
 1773: 
 1774: =head3 EXAMPLE
 1775: 
 1776:     # The parent automatically ATTACHes the child's SQLite file and queries
 1777:     # _dj_result for zero-copy composable nested joins.
 1778:     my $inner = Database::Join->new(
 1779:         databases   => [$customers, $loyalty],
 1780:         join_column => 'entry',
 1781:         backend     => 'sqlite',
 1782:     );
 1783:     my $outer = Database::Join->new(
 1784:         databases   => [$inner, $scores],
 1785:         join_column => 'entry',
 1786:         backend     => 'sqlite',
 1787:     );
 1788:     my $rows = $outer->selectall_arrayref(tier => 'gold');
 1789: 
 1790: =head3 MESSAGES
 1791: 
 1792:     error_sqlite_connect (croak)
 1793:         -- The temporary SQLite join file could not be created.
 1794: 
 1795: =cut
 1796: 
 1797: sub dbi_source {
●1798 → 1813 → 1821 1798: 	my ($self) = @_;
 1799: 
 1800: 	# The array backend has no SQLite handle to expose.
 1801: 	return undef if $self->{_backend} eq 'array';
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1802: 
 1803: 	# Build or refresh the per-source SQLite cache.  On 'auto' backend this
 1804: 	# forces the SQLite path regardless of the auto-threshold decision — a
 1805: 	# parent that wants to ATTACH us requires a real on-disk file.
 1806: 	$self->_build_sqlite_cache() unless $self->_cache_fresh();
 1807: 
 1808: 	my $cache = $self->{_sqlite_cache};
 1809: 
 1810: 	# If the materialised result table is already current, reuse it.
 1811: 	# _dj_built is reset implicitly when _build_sqlite_cache creates a fresh
 1812: 	# cache hashref (the old hashref is replaced, so its _dj_built is gone).
 1813: 	unless ($cache->{_dj_built}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 1814: 		# Materialise all joined rows (filter criteria only, no query-time
 1815: 		# criteria) into _dj_result so a parent connection can ATTACH and
 1816: 		# query it as a plain table.
 1817: 		$self->_sqlite_join({}, create_table => '_dj_result');
 1818: 		$cache->{_dj_built} = 1;
 1819: 	}
 1820: 
 1821: 	return { dbh => $cache->{dbh}, table => '_dj_result' };
 1822: }
 1823: 
 1824: =head2 columns
 1825: 
 1826: =head3 SYNOPSIS
 1827: 
 1828:     my $cols = $join->columns();
 1829: 
 1830: =head3 DESCRIPTION
 1831: 
 1832: Returns an arrayref of all column names visible in the merged view,
 1833: deduplicated and sorted alphabetically.
 1834: 
 1835: The C<join_column> appears exactly once, even if it exists under different
 1836: local names in some databases (see C<join_map>).  Columns that have been
 1837: hidden with C<remove_column> or C<remove_columns> do not appear.
 1838: 
 1839: The result is memoised: repeated calls are cheap.
 1840: 
 1841: =head3 API SPECIFICATION
 1842: 
 1843: =head4 Input
 1844: 
 1845:     None.
 1846: 
 1847: =head4 Output
 1848: 
 1849:     Arrayref of column name strings, sorted alphabetically.
 1850: 
 1851: =head3 EXAMPLE
 1852: 
 1853:     my $cols = $join->columns();
 1854:     print join(', ', @{$cols}), "\n";
 1855:     # e.g. "entry, name, score, tier"
 1856: 
 1857: =head3 MESSAGES
 1858: 
 1859: C<columns()> does not itself emit any warnings or errors.  Any exception thrown
 1860: by a component database's C<columns()> method propagates uncaught.
 1861: 
 1862: =cut
 1863: 
 1864: sub columns {
●1865 → 1873 → 1888 1865: 	my ($self) = @_;
 1866: 
 1867: 	return $self->{_col_cache} if $self->{_col_cache};
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1868: 
 1869: 	my %seen;
 1870: 	my @cols;
 1871: 	my $join_col = $self->{_join_col};
 1872: 
 1873: 	for my $i (0 .. $#{ $self->{_dbs} }) {
 1874: 		my $local_jc = $self->{_join_map}{$i};
 1875: 		my $renames  = $self->{_col_rename}[$i] // {};
 1876: 		for my $col (@{ $self->{_dbs}[$i]->columns() }) {
 1877: 			# The local join-key alias is not a data column; the canonical name
 1878: 			# is already contributed by the database that owns it under that name.
 1879: 			next if $local_jc && $col eq $local_jc && $col ne $join_col;
 1880: 			# Use the published name (prefixed if this column was a collision rename)
 1881: 			my $pub = $renames->{$col} // $col;
 1882: 			next if $seen{$pub}++;
 1883: 			next if $self->{_removed_cols}{$pub};
 1884: 			push @cols, $pub;
 1885: 		}
 1886: 	}
 1887: 
 1888: 	$self->{_col_cache} = [ sort @cols ];
 1889: 	return $self->{_col_cache};
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1890: }
 1891: 
 1892: =head2 schema
 1893: 
 1894: =head3 SYNOPSIS
 1895: 
 1896:     my $schema = $join->schema();
 1897: 
 1898: =head3 DESCRIPTION
 1899: 
 1900: Returns a merged schema hashref for all visible columns across all component
 1901: databases.  Each key is a column name; each value is the schema metadata
 1902: hashref returned by C<Database::Abstraction::schema()> for that column
 1903: (typically C<{ type, nullable, default, pk }>).
 1904: 
 1905: When the same column name appears in more than one database the I<last>
 1906: database's metadata is used.  Columns hidden with C<remove_column> are not
 1907: included.
 1908: 
 1909: The result is memoised.
 1910: 
 1911: =head3 API SPECIFICATION
 1912: 
 1913: =head4 Input
 1914: 
 1915:     None.
 1916: 
 1917: =head4 Output
 1918: 
 1919:     Hashref: column_name => { type => ..., nullable => ..., default => ..., pk => ... }.
 1920: 
 1921: =head3 EXAMPLE
 1922: 
 1923:     my $schema = $join->schema();
 1924:     for my $col (sort keys %{$schema}) {
 1925:         my $info = $schema->{$col};
 1926:         printf "%-15s type=%-10s nullable=%s\n",
 1927:             $col, $info->{type}, $info->{nullable} ? 'yes' : 'no';
 1928:     }
 1929: 
 1930: =head3 MESSAGES
 1931: 
 1932: C<schema()> does not itself emit any warnings or errors.  Any exception thrown
 1933: by a component database's C<schema()> method propagates uncaught.
 1934: 
 1935: =cut
 1936: 
 1937: sub schema {
●1938 → 1946 → 1962 1938: 	my ($self) = @_;
 1939: 
 1940: 	return $self->{_schema_cache} if $self->{_schema_cache};
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1941: 
 1942: 	# Hash slice assignment (@merged{keys} = values) is O(M) per database;
 1943: 	# the previous (%merged = (%merged, %s)) pattern was O(N×M) per iteration,
 1944: 	# totalling O(N²×M) over all databases for the same result.
 1945: 	my %merged;
 1946: 	for my $i (0 .. $#{ $self->{_dbs} }) {
 1947: 		my $s        = $self->{_dbs}[$i]->schema() // {};
 1948: 		my $local_jc = $self->{_join_map}{$i};
 1949: 		my $renames  = $self->{_col_rename}[$i] // {};
 1950: 		my $skip_jc  = $local_jc && $local_jc ne $self->{_join_col};
 1951: 		if (!$skip_jc && !%{$renames}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 1952: 			# Fast path: no join-key alias to exclude and no collision renames
 1953: 			@merged{keys %{$s}} = values %{$s};
 1954: 		} else {
 1955: 			for my $col (keys %{$s}) {
 1956: 				next if $skip_jc && $col eq $local_jc;
 1957: 				$merged{ $renames->{$col} // $col } = $s->{$col};
 1958: 			}
 1959: 		}
 1960: 	}
 1961: 
 1962: 	delete @merged{keys %{ $self->{_removed_cols} }}
 1963: 		if %{ $self->{_removed_cols} };
 1964: 
 1965: 	$self->{_schema_cache} = \%merged;
 1966: 	return $self->{_schema_cache};
  Mutants (Total: 2, Killed: 2, Survived: 0)
 1967: }
 1968: 
 1969: =head2 updated
 1970: 
 1971: =head3 SYNOPSIS
 1972: 
 1973:     my $ts = $join->updated();
 1974: 
 1975: =head3 DESCRIPTION
 1976: 
 1977: Returns the Unix timestamp of the most recent modification across all
 1978: component databases.  This is the maximum of all individual C<updated()>
 1979: return values.
 1980: 
 1981: Use this to implement simple cache-invalidation logic: if C<updated()>
 1982: has advanced since your last snapshot, re-query.
 1983: 
 1984: =head3 API SPECIFICATION
 1985: 
 1986: =head4 Input
 1987: 
 1988:     None.
 1989: 
 1990: =head4 Output
 1991: 
 1992:     Unix timestamp (positive integer).
 1993: 
 1994: =head3 EXAMPLE
 1995: 
 1996:     my $last_modified = $join->updated();
 1997:     if ($last_modified > $my_cache_timestamp) {
 1998:         $my_cache = $join->selectall_arrayref();
 1999:         $my_cache_timestamp = $last_modified;
 2000:     }
 2001: 
 2002: =head3 MESSAGES
 2003: 
 2004: C<updated()> does not emit any warnings or errors.  Component databases that do
 2005: not implement C<updated()>, or whose C<updated()> throws, are silently skipped;
 2006: only defined return values contribute to the maximum.  If no component database
 2007: implements C<updated()>, C<undef> is returned (same as C<List::Util::max> on an
 2008: empty list).
 2009: 
 2010: =cut
 2011: 
 2012: sub updated {
●2013 → 2015 → 2020 2013: 	my ($self) = @_;
 2014: 	my @timestamps;
 2015: 	for my $db (@{ $self->{_dbs} }) {
 2016: 		my $ts;
 2017: 		do { local $@; $ts = eval { $db->updated() } };
 2018: 		push @timestamps, $ts if defined $ts;
 2019: 	}
 2020: 	return max(@timestamps);
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2021: }
 2022: 
 2023: =head2 set_logger
 2024: 
 2025: =head3 SYNOPSIS
 2026: 
 2027:     $join->set_logger($log);
 2028:     $join->set_logger(logger => $log);   # named-pair form also accepted
 2029: 
 2030: =head3 DESCRIPTION
 2031: 
 2032: Attaches a new logger object to the join and propagates it to every component
 2033: database.  The logger is used for diagnostic output by all component databases.
 2034: 
 2035: Non-blessed values (a log-level string such as C<"debug">, a filename, or a
 2036: code reference) are wrapped in C<Log::Abstraction->new(...)> automatically,
 2037: matching the behaviour of C<Database::Abstraction::set_logger>.
 2038: 
 2039: =head3 API SPECIFICATION
 2040: 
 2041: =head4 Input
 2042: 
 2043:     logger  Positional or named: a logger object, log-level string, filename,
 2044:             or code reference (required).  Non-blessed values are wrapped in
 2045:             Log::Abstraction automatically.
 2046: 
 2047: =head4 Output
 2048: 
 2049:     Returns C<$self> for method chaining.
 2050: 
 2051: =head3 EXAMPLE
 2052: 
 2053:     # Log::Any is used here as an example; any object that implements
 2054:     # debug() and info() (or whichever methods your component databases
 2055:     # call internally) works equally well.
 2056:     use Log::Any qw($log);
 2057: 
 2058:     my $join = Database::Join->new(databases => [$db1, $db2], join_column => 'entry');
 2059:     $join->set_logger($log);
 2060:     # $log is now used by $join and by $db1 and $db2
 2061: 
 2062:     # Named-pair form (mirrors Database::Abstraction API):
 2063:     $join->set_logger(logger => $log);
 2064: 
 2065: =head3 MESSAGES
 2066: 
 2067:     (croak) Usage: set_logger(logger => $logger)
 2068:         -- Called with an undefined argument.  Pass a valid logger object.
 2069: 
 2070: =cut
 2071: 
 2072: sub set_logger {
 2073: 	my $self = shift;
 2074: 	my $p    = Params::Get::get_params('logger', @_);
 2075: 	my $logger = $p->{'logger'};
 2076: 
 2077: 	croak 'Usage: set_logger(logger => $logger)' unless defined $logger;
 2078: 
 2079: 	# Wrap non-blessed values (log-level strings, filenames, coderefs) exactly
 2080: 	# as Database::Abstraction does, so callers get identical behaviour from DJ.
 2081: 	$logger = Log::Abstraction->new($logger)
 2082: 		unless Scalar::Util::blessed($logger);
 2083: 
 2084: 	$self->{_logger} = $logger;
 2085: 	$_->set_logger($logger) for @{ $self->{_dbs} };
 2086: 
 2087: 	return $self;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2088: }
 2089: 
 2090: =head2 add_database
 2091: 
 2092: =head3 SYNOPSIS
 2093: 
 2094:     # Positional: database object as first argument
 2095:     $join->add_database($db);
 2096: 
 2097:     # Named: equivalent to the above
 2098:     $join->add_database(database => $db);
 2099: 
 2100:     # With options (mixed positional + named)
 2101:     $join->add_database($db, remove_columns => ['internal_id']);
 2102:     $join->add_database($db, join_column    => 'local_key_name');
 2103:     $join->add_database($db, filter         => { score => { '>' => 60 } });
 2104: 
 2105:     # Chainable
 2106:     $join->add_database($db1)->add_database($db2, remove_columns => ['notes']);
 2107: 
 2108: =head3 DESCRIPTION
 2109: 
 2110: Adds one more C<Database::Abstraction> subclass object to the logical view
 2111: and immediately updates the column-ownership index.
 2112: 
 2113: After the call, all query methods return rows that include columns from the
 2114: newly added database, and criteria on those new columns are routed to it
 2115: automatically.
 2116: 
 2117: When a column name in the new database already exists in an earlier database,
 2118: the new database becomes the authoritative source for that column
 2119: (last-database-wins, the same rule that applies at construction time).
 2120: 
 2121: The join-column must be present in the new database (or declared via
 2122: C<join_column>).  The logger is propagated to the new database if one is set.
 2123: 
 2124: C<add_database> is the runtime equivalent of listing the database in the
 2125: C<databases> array to C<new>.  The optional C<join_column> parameter is
 2126: equivalent to a C<join_map> entry; the optional C<filter> parameter is
 2127: equivalent to a C<filters> entry.
 2128: 
 2129: =head3 API SPECIFICATION
 2130: 
 2131: =head4 Input
 2132: 
 2133:     database       => { type => 'object',   required => 1 }
 2134:                       # A Database::Abstraction subclass instance.
 2135:                       #
 2136:                       # DOMAIN -- EP valid:   blessed object that passes
 2137:                       #                       isa('Database::Abstraction').
 2138:                       # DOMAIN -- EP invalid: non-reference, unblessed ref, wrong class,
 2139:                       #                       or non-reference non-key scalar (the guard at
 2140:                       #                       the top of add_database rejects it with
 2141:                       #                       error_invalid_db before validate_strict runs).
 2142: 
 2143:     join_column    => { type => 'string',   optional => 1 }
 2144:                       # The name of the join key in THIS new database,
 2145:                       # when it differs from the canonical join_column.
 2146:                       #
 2147:                       # DOMAIN -- EP valid:   any string that exists as a column in the
 2148:                       #                       new database.
 2149:                       # DOMAIN -- EP invalid: string absent from the new database's columns()
 2150:                       #                       => croak error_join_col_missing.
 2151: 
 2152:     filter         => { type => 'hashref',  optional => 1 }
 2153:                       # Permanent criteria for this database only.
 2154:                       # Same format as selectall_arrayref.
 2155:                       #
 2156:                       # DOMAIN -- EP valid:   hashref of criteria (may be {} for no-op).
 2157:                       # DOMAIN -- EP absent:  no permanent filter applied; all rows visible.
 2158:                       # DOMAIN -- Key-set:    a non-empty filter makes this DB an inner-join
 2159:                       #                       partner regardless of the outer join_type.
 2160: 
 2161:     remove_columns => { type => 'arrayref', optional => 1 }
 2162:                       # Column names from this database to hide.
 2163:                       #
 2164:                       # DOMAIN -- EP valid:   arrayref of strings; non-existent columns silently
 2165:                       #                       ignored; empty [] is a safe no-op.
 2166:                       # DOMAIN -- EP invalid: join_column itself => croak error_remove_join_col.
 2167: 
 2168: =head4 Output
 2169: 
 2170:     Returns C<$self> to support method chaining.
 2171: 
 2172: =head3 EXAMPLE
 2173: 
 2174:     my $join = Database::Join->new(
 2175:         databases   => [ $customers ],
 2176:         join_column => 'entry',
 2177:     );
 2178: 
 2179:     # Add loyalty data; hide internal columns from it
 2180:     $join->add_database($loyalty, remove_columns => ['audit_ts']);
 2181: 
 2182:     # Add score data; only include rows with score > 60
 2183:     $join->add_database($scores, filter => { score => { '>' => 60 } });
 2184: 
 2185:     # Add a database whose join key has a different local name
 2186:     $join->add_database($stnames, join_column => 'state_code');
 2187: 
 2188:     # All three options combined, and chained
 2189:     $join->add_database($db4,
 2190:         join_column    => 'ref_id',
 2191:         filter         => { active => 1 },
 2192:         remove_columns => ['legacy_col'],
 2193:     );
 2194: 
 2195: =head3 PSEUDOCODE
 2196: 
 2197:     determine the new database's index (length of current _dbs array)
 2198:     extract the database object from positional or named argument
 2199:     croak if it is not a Database::Abstraction subclass
 2200:     register join_column alias in _join_map if different from canonical
 2201:     register filter in _filters if provided
 2202:     fetch column list from the new database
 2203:     croak if the join key is missing from the new database
 2204:     append the new database to _dbs and _db_cols
 2205:     update _col_db: for each new column, point it at the new index
 2206:         (last-database-wins; skip removed columns and the local join alias)
 2207:     invalidate _col_cache and _schema_cache
 2208:     propagate logger if set
 2209:     apply remove_columns if provided
 2210:     return $self
 2211: 
 2212: =head3 MESSAGES
 2213: 
 2214:     error_invalid_db          -- argument is not a Database::Abstraction subclass
 2215:     error_join_col_missing    -- join_column not found in the new database
 2216:     warn_schema_type_mismatch -- (carp) the new database has a shared column whose type
 2217:                                  differs from the type already in the view; use
 2218:                                  collision_prefix to preserve both values
 2219: 
 2220: =cut
 2221: 
 2222: sub add_database {
●2223 → 2232 → 2241 2223: 	my ($self, @args) = @_;
 2224: 
 2225: 	my $idx = scalar @{ $self->{_dbs} };
 2226: 	my $db;
 2227: 
 2228: 	# Fail-fast guard: a non-reference first arg must be a recognised named-pair key.
 2229: 	# Modus Ponens: !ref(x) ∧ x ∉ @_ADD_DB_KEYS → cannot be a database object → croak.
 2230: 	# De Morgan reduction: the elsif below is logically equivalent to (@args && ref(args[0]))
 2231: 	# because the !ref branch was already handled; exhaustion makes the ref() check redundant.
 2232: 	if (@args && !ref($args[0])) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2233: 		croak $self->_err('error_invalid_db', $idx)
 2234: 			unless defined($args[0]) && grep { $args[0] eq $_ } @_ADD_DB_KEYS;
 2235: 	} elsif (@args) {
 2236: 		# Positional form: first arg is a reference — extract it before get_params
 2237: 		# to avoid the mixed positional+named-pairs confusion.
 2238: 		$db = shift @args;
 2239: 	}
 2240: 
●2241 → 2285 → 2303 2241: 	my $p = validate_strict(
 2242: 		schema => {
 2243: 			database  => {
 2244: 				type => 'object',
 2245: 				optional => 1,
 2246: 				can => ['selectall_arrayref', 'columns']
 2247: 			},
 2248: 			join_column    => { type => 'string',   optional => 1 },
 2249: 			filter         => { type => 'hashref',  optional => 1 },
 2250: 			remove_columns => { type => 'arrayref', optional => 1 },
 2251: 		},
 2252: 		input => (@args ? get_params(undef, @args) : {}) // {},
 2253: 	);
 2254: 
 2255: 	$db //= $p->{database};
 2256: 
 2257: 	croak $self->_err('error_invalid_db', $idx)
 2258: 		unless blessed($db)
 2259: 		    && $db->can('selectall_arrayref')
 2260: 		    && $db->can('columns');
 2261: 
 2262: 	# Determine and register the local join column name for this database
 2263: 	my $local_jc = $p->{join_column} // $self->{_join_col};
 2264: 	$self->{_join_map}{$idx} = $local_jc if $p->{join_column};
 2265: 	# Security: deep-copy the filter; same rationale as the constructor's _copy_filters call.
 2266: 	$self->{_filters}{$idx}  = _copy_criteria($p->{filter}) if $p->{filter};
 2267: 
 2268: 	my $cols         = $db->columns();
 2269: 	my %col_presence = map { $_ => 1 } @{$cols};
 2270: 
 2271: 	croak $self->_err('error_join_col_missing', $local_jc, $idx, ref($db))
 2272: 		unless $col_presence{$local_jc};
 2273: 
 2274: 	# Register the new database
 2275: 	push @{ $self->{_dbs} },     $db;
 2276: 	push @{ $self->{_db_cols} }, \%col_presence;
 2277: 
 2278: 	# Update column routing: last-database-wins for duplicates, unless a
 2279: 	# collision_prefix is configured for this index (in which case the
 2280: 	# duplicate is published under "$prefix.$col" instead of overwriting).
 2281: 	my $prefix = $self->{_collision_prefix}{$idx};
 2282: 	$self->{_col_rename}[$idx]   //= {};
 2283: 	$self->{_col_unrename}[$idx] //= {};
 2284: 
 2285: 	for my $col (@{$cols}) {
 2286: 		next if $local_jc ne $self->{_join_col} && $col eq $local_jc;
 2287: 
 2288: 		my $pub;
 2289: 		if (defined $prefix && exists $self->{_col_db}{$col} && $col ne $self->{_join_col}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2290: 			# Same guard as _build_col_index: never prefix the join_column itself.
 2291: 			$pub = "$prefix.$col";
 2292: 			$self->{_col_rename}[$idx]{$col}   = $pub;
 2293: 			$self->{_col_unrename}[$idx]{$pub} = $col;
 2294: 		} else {
 2295: 			$pub = $col;
 2296: 		}
 2297: 
 2298: 		next if $self->{_removed_cols}{$pub};
 2299: 		$self->{_col_db}{$pub} = $idx;
 2300: 	}
 2301: 
 2302: 	# Invalidate memoisation caches
●2303 → 2310 → 2316 2303: 	$self->{_col_cache}    = undef;
 2304: 	$self->{_schema_cache} = undef;
 2305: 
 2306: 	# Warn about schema type mismatches introduced by the new database.
 2307: 	$self->_validate_schema_types();
 2308: 
 2309: 	# Invalidate the SQLite join cache: a new source requires a full rebuild.
 2310: 	if (my $old = delete $self->{_sqlite_cache}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2311: 		local $@;
 2312: 		eval { $old->{dbh}->disconnect } if $old->{dbh};
 2313: 	}
 2314: 
 2315: 	# Propagate logger if one is configured
●2316 → 2316 → 2321 2316: 	if (my $log = $self->{_logger}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2317: 		$db->set_logger($log);
 2318: 	}
 2319: 
 2320: 	# Apply any column removals requested for this database
●2321 → 2321 → 2325 2321: 	if (my $rc = $p->{remove_columns}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2322: 		$self->remove_column($_) for @{$rc};
 2323: 	}
 2324: 
 2325: 	return $self;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2326: }
 2327: 
 2328: =head2 remove_column
 2329: 
 2330: =head3 SYNOPSIS
 2331: 
 2332:     $join->remove_column('email');
 2333: 
 2334:     # Chainable
 2335:     $join->remove_column('internal_id')->remove_column('audit_ts');
 2336: 
 2337: =head3 DESCRIPTION
 2338: 
 2339: Permanently hides a column from the merged view.  After this call:
 2340: 
 2341: =over 4
 2342: 
 2343: =item *
 2344: 
 2345: The column does not appear in C<columns()> or C<schema()>.
 2346: 
 2347: =item *
 2348: 
 2349: Returned row hashrefs do not contain the column key.
 2350: 
 2351: =item *
 2352: 
 2353: Any query criterion that references the removed column is silently dropped
 2354: (with a C<carp> warning).
 2355: 
 2356: =back
 2357: 
 2358: The C<join_column> cannot be removed; attempting to do so will C<croak>.
 2359: Removing a column that does not exist in any database is silently ignored
 2360: (the call is idempotent and safe).  The C<columns()> and C<schema()>
 2361: memoisation caches are cleared automatically.
 2362: 
 2363: =head3 API SPECIFICATION
 2364: 
 2365: =head4 Input
 2366: 
 2367:     $col    Positional string: the column name to remove.
 2368: 
 2369:             DOMAIN -- EP valid:   any string; non-existent columns are silently
 2370:                                   ignored (idempotent call, returns $self).
 2371:             DOMAIN -- EP invalid: join_column value => croak error_remove_join_col.
 2372:             DOMAIN -- BVA:        undef and '' are explicit no-ops (returns $self).
 2373:                                   These are below the minimum meaningful string
 2374:                                   length and are handled without any warning.
 2375: 
 2376: =head4 Output
 2377: 
 2378:     Returns C<$self> to support method chaining.
 2379: 
 2380: =head3 EXAMPLE
 2381: 
 2382:     # Hide private fields immediately after construction
 2383:     my $join = Database::Join->new(
 2384:         databases   => [ $customers, $loyalty ],
 2385:         join_column => 'entry',
 2386:     )->remove_column('email')
 2387:      ->remove_column('internal_notes');
 2388: 
 2389:     # Verify they are gone
 2390:     my $cols = $join->columns();
 2391:     # 'email' and 'internal_notes' are absent
 2392: 
 2393: =head3 MESSAGES
 2394: 
 2395:     error_remove_join_col -- attempt to remove the join_column itself
 2396: 
 2397: =cut
 2398: 
 2399: sub remove_column {
 2400: 	my ($self, $col) = @_;
 2401: 
 2402: 	# Premise: undef and '' are provably no-ops (nothing to remove).
 2403: 	# Conclusion: guard at the top eliminates two separate defined() checks below.
 2404: 	return $self unless defined $col && length $col;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2405: 
 2406: 	# Premise: $col is defined (proven above) and join_col is always a non-empty string.
 2407: 	# Conclusion: direct string comparison is safe without a redundant defined() check.
 2408: 	croak $self->_err('error_remove_join_col', $col)
 2409: 		if $col eq $self->{_join_col};
 2410: 
 2411: 	$self->{_removed_cols}{$col} = 1;
 2412: 	delete $self->{_col_db}{$col};
 2413: 	$self->{_col_cache}    = undef;
 2414: 	$self->{_schema_cache} = undef;
 2415: 	$self->{_removed_list} = undef;	# invalidate the cached removed-column list
 2416: 
 2417: 	return $self;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2418: }
 2419: 
 2420: =head2 query
 2421: 
 2422: Not supported.  C<Database::Join> does not implement the
 2423: C<Database::Abstraction::Query> chained builder because the builder's
 2424: C<.all()> / C<.first()> methods would be targeting a single component DA
 2425: rather than the merged view.  Calling this method will always C<croak>.
 2426: 
 2427: Use C<selectall_arrayref>, C<selectall_array>, C<fetchrow_hashref>,
 2428: C<count>, or C<each_row> against the C<Database::Join> object instead.
 2429: 
 2430: =cut
 2431: 
 2432: sub query {
 2433: 	my $self = $_[0];
 2434: 	croak $self->_err('error_query_unsupported');
 2435: }
 2436: 
 2437: =head2 execute
 2438: 
 2439: Not supported.  Raw SQL cannot span heterogeneous backends that may use
 2440: different database engines.  Calling this method will always C<croak>.
 2441: 
 2442: Use C<selectall_arrayref> or C<fetchrow_hashref> to query the joined view.
 2443: 
 2444: =cut
 2445: 
 2446: sub execute {
 2447: 	my $self = $_[0];
 2448: 	croak $self->_err('error_execute_unsupported');
 2449: }
 2450: 
 2451: =head2 AUTOLOAD - column shortcut
 2452: 
 2453: Calling an unknown method whose name matches a visible column name performs
 2454: a column lookup across the merged view.
 2455: 
 2456: =head3 SYNOPSIS
 2457: 
 2458:     # Scalar context: value from the first matching row
 2459:     my $name  = $join->name(entry => 'C001');
 2460: 
 2461:     # List context: values from every matching row
 2462:     my @tiers = $join->tier();
 2463: 
 2464:     # With a positional join-key argument (when join_column is 'entry')
 2465:     my $score = $join->score('C001');
 2466: 
 2467: =head3 DESCRIPTION
 2468: 
 2469: AUTOLOAD routes the call to the appropriate component database by looking up
 2470: the column name in the internal column-ownership index.
 2471: 
 2472: When either C<join_map> or C<filters> is active, AUTOLOAD performs a full
 2473: join query instead of delegating directly to the owning database.  This is
 2474: necessary because:
 2475: 
 2476: =over 4
 2477: 
 2478: =item *
 2479: 
 2480: With C<join_map>, the owning database's primary key may differ from the
 2481: canonical join key used in the call arguments.
 2482: 
 2483: =item *
 2484: 
 2485: With C<filters>, bypassing the join would return rows that the filter is
 2486: meant to exclude.
 2487: 
 2488: =back
 2489: 
 2490: In list context, every matching merged row contributes one value to the
 2491: returned list.  In scalar context, only the first row's value is returned.
 2492: 
 2493: Calling a method whose name begins with C<_> (a private method) via AUTOLOAD
 2494: will C<croak> with a clear error message rather than being silently ignored.
 2495: 
 2496: =head3 EXAMPLE
 2497: 
 2498:     # Lookup a single customer's name (scalar context)
 2499:     my $name = $join->name('C001');   # 'C001' maps to entry => 'C001'
 2500:     print "Name: $name\n";
 2501: 
 2502:     # Get every tier value in the view (list context)
 2503:     my @all_tiers = $join->tier();
 2504:     my %freq;
 2505:     $freq{$_}++ for @all_tiers;
 2506: 
 2507:     # join_map active: AUTOLOAD runs a full join so the criteria are
 2508:     # translated correctly between the canonical and local key names.
 2509:     my @leesburg_states = sort $join->state('Leesburg');
 2510:     # ['Florida', 'Virginia'] if Leesburg appears in two states
 2511: 
 2512: =head3 PSEUDOCODE
 2513: 
 2514:     extract column name from $AUTOLOAD
 2515:     return if DESTROY
 2516:     croak if column name starts with '_' (private method guard)
 2517:     croak if column name is not in _col_db (unknown column)
 2518:     if join_map or filters are active:
 2519:         parse calling arguments using _parse_query_args
 2520:         call _joined_query to get all merged rows
 2521:         return map { $_->{col} } @rows  in list context
 2522:         return $rows[0]{col}            in scalar context
 2523:     else:
 2524:         delegate directly to the owning database
 2525: 
 2526: =head3 MESSAGES
 2527: 
 2528:     (croak) Database::Join: cannot call private method '_NAME' via AUTOLOAD
 2529:         -- Method name begins with '_'.  Private methods must be called directly,
 2530:            not via AUTOLOAD.  This is a programming error.
 2531: 
 2532:     (croak) Database::Join: unknown column 'NAME'
 2533:         -- Method name does not match any visible column in the merged view.
 2534:            Check spelling, or whether the column was removed with remove_column().
 2535: 
 2536: =cut
 2537: 
 2538: our $AUTOLOAD;
 2539: 
 2540: sub AUTOLOAD {
●2541 → 2563 → 2577 2541: 	my $self = shift;
 2542: 
 2543: 	my ($col) = $AUTOLOAD =~ /
 2544: 		::      # package separator — skip the fully-qualified prefix
 2545: 		(\w++)  # method name: possessive quantifier commits immediately;
 2546: 		        # no backtrack possible because \w chars cannot match \z
 2547: 		\z      # strict end-of-string (\z never matches a trailing newline,
 2548: 		        # unlike $ which can — important if $AUTOLOAD ever embeds \n)
 2549: 	/x;
 2550: 
 2551: 	# Private methods must not be reached via AUTOLOAD — croak immediately so
 2552: 	# typos like $join->_join_col are not silently swallowed.
 2553: 	# substr() avoids regex-engine overhead for this single-character prefix check.
 2554: 	croak ref($self), ": cannot call private method '$col' via AUTOLOAD"
 2555: 		if substr($col, 0, 1) eq '_';
 2556: 
 2557: 	my $db_idx = $self->{_col_db}{$col};
 2558: 	croak ref($self), ": unknown column '$col'" unless defined $db_idx;
 2559: 
 2560: 	# Use a full join query when join_map OR filters are active.  Direct
 2561: 	# delegation to the owning database would bypass the join key translation
 2562: 	# (join_map) and skip any permanent per-database row filters (filters).
 2563: 	if (%{ $self->{_join_map} } || %{ $self->{_filters} }) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2564: 		# _autoload_pk was captured once at construction from the primary DA's
 2565: 		# {id} field; using the cached value avoids re-introspecting the blessed
 2566: 		# hash on every call and isolates the coupling to a single known site.
 2567: 		my $params   = $self->_parse_query_args($self->{_autoload_pk}, @_);
 2568: 		my $sort_by = delete $params->{sort_by};
 2569: 		my $limit    = delete $params->{limit};
 2570: 		my $offset   = delete $params->{offset};
 2571: 		my $rows     = $self->_joined_query($params, sort_by => $sort_by, limit => $limit, offset => $offset);
 2572: 		return map { $_->{$col} } @{$rows} if wantarray;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2573: 		return @{$rows} ? $rows->[0]{$col} : undef;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2574: 	}
 2575: 
 2576: 	# $db is resolved here (not earlier) to avoid a dead store on the join path above.
 2577: 	my $db = $self->{_dbs}[$db_idx];
 2578: 	return $db->$col(@_);
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2579: }
 2580: 
 2581: sub DESTROY {
●2582 → 2585 → 0 2582: 	my ($self) = @_;
 2583: 	# Disconnect and release the cached SQLite handle (if any) so File::Temp
 2584: 	# can unlink the temp file before the object is freed.
 2585: 	if (my $cache = delete $self->{_sqlite_cache}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2586: 		local $@;
 2587: 		eval { $cache->{dbh}->disconnect } if $cache->{dbh};
 2588: 		# $cache->{tmpfile} (File::Temp, UNLINK => 1) is released here.
 2589: 	}
 2590: }
 2591: 
 2592: # ---------------------------------------------------------------------------
 2593: # Private helpers
 2594: # ---------------------------------------------------------------------------
 2595: 
 2596: # _parse_query_args( $self, $positional_key, @caller_args ) -> \%params
 2597: # Purpose: Normalise the three calling conventions used by every public query
 2598: #          method and AUTOLOAD into a single criteria hashref.
 2599: # Entry:   $positional_key -- the column name mapped to a bare scalar argument;
 2600: #          pass undef to use the join_column (the default for public methods).
 2601: # Exit:    Always returns a hashref; never undef.
 2602: sub _parse_query_args :Protected {
 2603: 	my ($self, $key, @args) = @_;
 2604: 	# D~ elimination (Modus Tollens): when @args is empty the early return fires
 2605: 	# before $key is ever read, making the $key //= assignment a dead store.
 2606: 	# Moving the guard above the assignment removes the wasted hash dereference.
 2607: 	return {} unless @args;
 2608: 	$key //= $self->{_join_col};
 2609: 	return { $key => $args[0] }        if @args == 1 && !ref($args[0]);
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2610: 	return get_params(undef, @args) // {};
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2611: }
 2612: 
 2613: # _err( $self, $msg_key, @sprintf_args ) -> $string
 2614: # Convenience wrapper around _msg for use after construction, so callers do
 2615: # not have to extract $self->{_i18n} at every error site.
 2616: sub _err :Protected {
 2617: 	my ($self, $key, @args) = @_;
 2618: 	return _msg($self->{_i18n}, $key, @args);
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2619: }
 2620: 
 2621: # _build_col_index()
 2622: # Purpose: Populate _col_db (column_name => db_index) and _db_cols
 2623: #          (per-db column-presence hashrefs) by calling columns() on each
 2624: #          component database at construction time.
 2625: # Entry:   _dbs, _join_col, _join_map must already be set.
 2626: # Exit:    _col_db and _db_cols are set; join_column verified in every db.
 2627: # Effects: Croaks if any database is missing its join key column.
 2628: sub _build_col_index :Protected {
●2629 → 2638 → 2689 2629: 	my ($self) = @_;
 2630: 
 2631: 	my $join_col = $self->{_join_col};
 2632: 	my $cp       = $self->{_collision_prefix} // {};
 2633: 	my %col_db;
 2634: 	my @db_cols;
 2635: 	my @col_rename;    # per-db: { orig_col => published_col } for renamed collisions
 2636: 	my @col_unrename;  # per-db: { published_col => orig_col } reverse map
 2637: 
 2638: 	for my $i (0 .. $#{ $self->{_dbs} }) {
 2639: 		my $db        = $self->{_dbs}[$i];
 2640: 		my $local_jc  = $self->{_join_map}{$i} // $join_col;
 2641: 		my $cols      = $db->columns();
 2642: 		$db_cols[$i]      = { map { $_ => 1 } @{$cols} };
 2643: 		$col_rename[$i]   = {};
 2644: 		$col_unrename[$i] = {};
 2645: 
 2646: 		# Guard: a join_map value that is a reference (e.g. a hashref) would
 2647: 		# stringify to "HASH(0x...)" when interpolated into an error message,
 2648: 		# leaking a heap address to callers.  Reject early with a clear message.
 2649: 		croak $self->_err('error_join_col_missing', "(join_map[$i] must be a string)", $i, ref($db))
 2650: 			if ref $local_jc;
 2651: 
 2652: 		croak $self->_err('error_join_col_missing', $local_jc, $i, ref($db))
 2653: 			unless $db_cols[$i]{$local_jc};
 2654: 
 2655: 		# collision_prefix only applies to secondary databases (index > 0);
 2656: 		# an index-0 entry is meaningless and silently ignored.
 2657: 		my $prefix = ($i > 0) ? $cp->{$i} : undef;
  Mutants (Total: 3, Killed: 3, Survived: 0)
 2658: 
 2659: 		# Guard: a collision_prefix value that is a reference would stringify
 2660: 		# to "HASH(0x...)" or "ARRAY(0x...)" when interpolated into "$prefix.$col",
 2661: 		# leaking a heap address into every column name, columns(), schema(), and
 2662: 		# merged row hashref.  This is the same class of leak as the join_map guard
 2663: 		# above; reject early with a clear message before any column name is built.
 2664: 		croak $self->_err('error_invalid_prefix', $i)
 2665: 			if defined $prefix && ref $prefix;
 2666: 
 2667: 		for my $col (@{$cols}) {
 2668: 			# Skip the local alias for the join key — it is not a data column
 2669: 			next if $local_jc ne $join_col && $col eq $local_jc;
 2670: 
 2671: 			if (defined $prefix && exists $col_db{$col} && $col ne $join_col) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2672: 				# Column already claimed by an earlier database AND a prefix is
 2673: 				# configured: publish the collision as "$prefix.$col" so both
 2674: 				# values survive in the merged row rather than one silently winning.
 2675: 				# The join_column itself is never prefixed — it is the shared merge
 2676: 				# key and is always broadcast by name; renaming it would break routing.
 2677: 				my $pub = "$prefix.$col";
 2678: 				$col_rename[$i]{$col}   = $pub;
 2679: 				$col_unrename[$i]{$pub} = $col;
 2680: 				$col_db{$pub} = $i;
 2681: 			} else {
 2682: 				# No collision, or no prefix configured: last database wins
 2683: 				# (preserved backward-compatible behaviour).
 2684: 				$col_db{$col} = $i;
 2685: 			}
 2686: 		}
 2687: 	}
 2688: 
 2689: 	$self->{_col_db}       = \%col_db;
 2690: 	$self->{_db_cols}      = \@db_cols;
 2691: 	$self->{_col_rename}   = \@col_rename;
 2692: 	$self->{_col_unrename} = \@col_unrename;
 2693: 
 2694: 	return;
 2695: }
 2696: 
 2697: # _validate_schema_types()
 2698: # Purpose: Carp when two databases share a column name (without collision_prefix)
 2699: #          but disagree on its type, which would cause silent type coercion.
 2700: # Entry:   _dbs, _col_rename, _join_map, _join_col are all populated.
 2701: # Exit:    Emits one carp per mismatched column; no other side effects.
 2702: sub _validate_schema_types :Protected {
●2703 → 2708 → 2750 2703: 	my ($self) = @_;
 2704: 
 2705: 	my $join_col = $self->{_join_col};
 2706: 	my %seen;    # col_name => { idx => $i, type => $type_str }
 2707: 
 2708: 	for my $i (0 .. $#{ $self->{_dbs} }) {
 2709: 		my $db       = $self->{_dbs}[$i];
 2710: 		my $s        = do { local $@; eval { $db->schema() } } // {};
 2711: 		my $local_jc = $self->{_join_map}{$i} // $join_col;
 2712: 		my $renames  = $self->{_col_rename}[$i] // {};
 2713: 
 2714: 		for my $orig_col (keys %{$s}) {
 2715: 			# Skip the join-key alias (a different name for the same join key)
 2716: 			next if $local_jc ne $join_col && $orig_col eq $local_jc;
 2717: 
 2718: 			# Skip the canonical join column itself — types may legitimately
 2719: 			# differ between DAs (e.g. INTEGER PK vs TEXT) without causing issues
 2720: 			# because the join column is not a data column.
 2721: 			next if $orig_col eq $join_col;
 2722: 
 2723: 			# Skip columns that have a collision prefix configured for this DB:
 2724: 			# they are published under distinct names, so no silent merge occurs.
 2725: 			next if exists $renames->{$orig_col};
 2726: 
 2727: 			# Normalise to a plain type string; skip if the DA returns no type.
 2728: 			my $entry    = $s->{$orig_col};
 2729: 			my $type_str = ref($entry) eq 'HASH'
 2730: 				? uc($entry->{type} // '')
 2731: 				: uc($entry // '');
 2732: 			next unless length $type_str;
 2733: 
 2734: 			if (exists $seen{$orig_col}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2735: 				my $prev = $seen{$orig_col};
 2736: 				if ($prev->{type} ne $type_str) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2737: 					carp $self->_err(
 2738: 						'warn_schema_type_mismatch',
 2739: 						$orig_col,
 2740: 						$prev->{type}, $prev->{idx},
 2741: 						$type_str,     $i,
 2742: 					);
 2743: 				}
 2744: 			} else {
 2745: 				$seen{$orig_col} = { idx => $i, type => $type_str };
 2746: 			}
 2747: 		}
 2748: 	}
 2749: 
 2750: 	return;
 2751: }
 2752: 
 2753: # _partition_criteria( \%params ) -> \@per_db
 2754: # Purpose: Split a flat criteria hashref into one slice per component database.
 2755: # Entry:   $params is a criteria hashref; all keys must be column names or
 2756: #          join_column.
 2757: # Exit:    Returns an arrayref of per-database criteria hashrefs.  The
 2758: #          join_column criterion is broadcast to every database using each
 2759: #          database's local join-key name.  Unknown columns trigger a carp.
 2760: # Effects: Carps for each unrecognised column name.
 2761: sub _partition_criteria :Protected {
●2762 → 2768 → 2795 2762: 	my ($self, $params) = @_;
 2763: 
 2764: 	my $join_col = $self->{_join_col};
 2765: 	my $n        = scalar @{ $self->{_dbs} };
 2766: 	my @per_db   = map { {} } 1 .. $n;
 2767: 
 2768: 	for my $col (keys %{$params}) {
 2769: 		if ($col eq $join_col) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2770: 			# Broadcast to every database using each one's local key column name.
 2771: 			# Shallow-copy operator hashrefs so a malicious component DA that
 2772: 			# mutates its criteria hashref contents cannot corrupt siblings.
 2773: 			my $val = $params->{$col};
 2774: 			for my $i (0 .. $n - 1) {
 2775: 				my $local = $self->{_join_map}{$i} // $join_col;
 2776: 				$per_db[$i]{$local} = ref($val) eq 'HASH' ? { %{$val} } : $val;
 2777: 			}
 2778: 		} elsif ($col eq 'join') {
 2779: 			# DA accepts a 'join =>' criteria key for SQL JOINs within one table.
 2780: 			# DJ cannot route such a criterion to a component DA meaningfully, so
 2781: 			# croak early with a diagnostic rather than silently dropping it.
 2782: 			croak $self->_err('error_join_criterion');
 2783: 		} elsif (defined(my $idx = $self->{_col_db}{$col})) {
 2784: 			# Translate the published column name back to the database's own name
 2785: 			# when the column was renamed for a collision (e.g. "pfx.col" -> "col").
 2786: 			# Invariant: _col_unrename[$idx] is always initialised to {} by _build_col_index
 2787: 		# and add_database, so the // {} fallback can never trigger (transitive reduction).
 2788: 		my $db_col = $self->{_col_unrename}[$idx]{$col} // $col;
 2789: 			$per_db[$idx]{$db_col} = $params->{$col};
 2790: 		} else {
 2791: 			carp $self->_err('warn_unknown_column', $col);
 2792: 		}
 2793: 	}
 2794: 
 2795: 	return \@per_db;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2796: }
 2797: 
 2798: # _fetch_indexed( $db_idx, \%criteria ) -> \%join_val_to_\@rows
 2799: # Purpose: Query one component database and index its rows by join-key value.
 2800: # Entry:   $db_idx is the zero-based database index; $criteria is the
 2801: #          pre-partitioned criteria hashref for this database.
 2802: # Exit:    Returns a hashref: join-key value => arrayref of row hashrefs.
 2803: #          Multiple rows sharing the same join-key value are all preserved
 2804: #          (important for the primary database when one key maps to many rows).
 2805: # Effects: Calls selectall_arrayref on the component database.
 2806: sub _fetch_indexed :Protected {
●2807 → 2816 → 2822 2807: 	my ($self, $db_idx, $criteria) = @_;
 2808: 
 2809: 	my $db        = $self->{_dbs}[$db_idx];
 2810: 	my $local_jc  = $self->{_join_map}{$db_idx} // $self->{_join_col};
 2811: 
 2812: 	my $rows = $db->selectall_arrayref($criteria);
 2813: 	$rows //= [];
 2814: 
 2815: 	my %indexed;
 2816: 	for my $row (@{$rows}) {
 2817: 		my $key = $row->{$local_jc};
 2818: 		next unless defined $key;
 2819: 		push @{ $indexed{$key} }, $row;
 2820: 	}
 2821: 
 2822: 	return \%indexed;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2823: }
 2824: 
 2825: # _joined_query( \%params, %opts ) -> \@merged_rows
 2826: #
 2827: # Purpose: Dispatcher — routes to the array (in-memory) or SQLite join backend
 2828: #          based on $self->{_backend}.  %opts are passed through to the backend
 2829: #          (currently: sort_by, limit, offset).
 2830: sub _joined_query :Protected {
 2831: 	my ($self, $params, %opts) = @_;
 2832: 	my $backend = $self->{_backend};
 2833: 	return $self->_joined_query_array($params, %opts) if $backend eq 'array';
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2834: 	return $self->_sqlite_join($params, %opts);
  Mutants (Total: 2, Killed: 2, Survived: 0)
 2835: }
 2836: 
 2837: # _joined_query_array( \%params ) -> \@merged_rows
 2838: #
 2839: # Purpose: Core in-memory join algorithm.  Partitions criteria, fetches per-database
 2840: #          results, resolves the key set, and merges rows.
 2841: #
 2842: # Key-set resolution (applied for each secondary database after the primary):
 2843: #
 2844: #   If the database had criteria in this query call (after base filter overlay),
 2845: #   it acts as an INNER-JOIN partner: only keys present in its filtered result
 2846: #   survive.  This gives WHERE-clause semantics even under a LEFT join.
 2847: #
 2848: #   If the database had NO effective criteria:
 2849: #     inner  -> intersect  (standard inner join)
 2850: #     left   -> no change  (primary defines the key set)
 2851: #     outer  -> union      (all keys from any database)
 2852: #
 2853: # Row merge: for each qualifying primary row, secondary rows are overlaid in
 2854: # index order.  For duplicate columns, later databases win.  Local join-key
 2855: # aliases are renamed to the canonical join_column before merging.
 2856: # Removed columns are deleted from every merged row.
 2857: # _validate_pagination( $limit, $offset ) -> ($validated_limit, $validated_offset)
 2858: #
 2859: # Purpose: Single source of truth for pagination parameter validation.
 2860: #          Shared by _joined_query_array (array path) and _sqlite_join (SQLite path).
 2861: # Entry:   $limit and $offset are raw caller values -- may be undef, negative,
 2862: #          non-integer, etc.
 2863: # Exit:    Returns the original values when valid; returns undef (with carp) when
 2864: #          invalid.  Premise: after this call, limit ∈ ℤ+ ∪ {undef} and
 2865: #          offset ∈ ℤ≥0 ∪ {undef}.  All downstream guards may safely rely on this.
 2866: sub _validate_pagination :Protected {
●2867 → 2874 → 2883 2867: 	my ($self, $limit, $offset) = @_;
 2868: 	# Syllogism: limit must be a positive integer ∧ matches /^\d+\z/a ∧ >= 1.
 2869: 	# Conclusion: any value that fails either check is treated as absent.
 2870: 	# \z (not $): rejects strings ending with \n that $ would silently accept.
 2871: 	# /a flag:    restricts \d to ASCII [0-9]; rejects Unicode decimal digits
 2872: 	#             (e.g. Arabic-Indic Ù£) that \d matches but Perl's numeric
 2873: 	#             coercion would silently treat as 0, bypassing the >= 1 guard.
 2874: 	if (defined $limit) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2875: 		if ($limit !~ /^\d+\z/a || $limit < 1) {
  Mutants (Total: 4, Killed: 4, Survived: 0)
 2876: 			carp "Database::Join: limit must be a positive integer; ignored";
 2877: 			undef $limit;
 2878: 		}
 2879: 	}
 2880: 	# Syllogism: offset must be a non-negative integer ∧ matches /^\d+\z/a.
 2881: 	# Conclusion: any value that fails the check is treated as absent.
 2882: 	# Same \z / /a rationale as limit above.
●2883 → 2883 → 2889 2883: 	if (defined $offset) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2884: 		if ($offset !~ /^\d+\z/a) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2885: 			carp "Database::Join: offset must be a non-negative integer; ignored";
 2886: 			undef $offset;
 2887: 		}
 2888: 	}
 2889: 	return ($limit, $offset);
 2890: }
 2891: 
 2892: sub _joined_query_array :Protected {
●2893 → 2912 → 2919 2893: 	my ($self, $params, %opts) = @_;
 2894: 	my $sort_by = $opts{sort_by};
 2895: 	my $limit    = $opts{limit};
 2896: 	my $offset   = $opts{offset};
 2897: 
 2898: 	# Fast path: skip Sub::Protected dispatch entirely when no pagination params are
 2899: 	# supplied (the common case).  Saves one method-lookup overhead per query.
 2900: 	($limit, $offset) = $self->_validate_pagination($limit, $offset)
 2901: 		if defined $limit || defined $offset;
 2902: 
 2903: 	my $join_col  = $self->{_join_col};
 2904: 	my $join_type = $self->{_join_type};
 2905: 	my $n         = scalar @{ $self->{_dbs} };
 2906: 
 2907: 	my $per_db = $self->_partition_criteria($params);
 2908: 
 2909: 	# Overlay any per-database base filters onto the partitioned criteria.
 2910: 	# A filtered database always has effective criteria, so $had_criteria will
 2911: 	# be true for it — giving inner-join key-set semantics regardless of join_type.
 2912: 	for my $i (0 .. $n - 1) {
 2913: 		my $base = $self->{_filters}{$i} // {};
 2914: 		next unless %{$base};
 2915: 		$per_db->[$i] = _merge_criteria($base, $per_db->[$i]);
 2916: 	}
 2917: 
 2918: 	# Fetch and index each database with its own criteria slice.
●2919 → 2960 → 3025 2919: 	my @indexed;
 2920: 	$indexed[0] = $self->_fetch_indexed(0, $per_db->[0]);
 2921: 
 2922: 	# Early exit: for inner and left joins, an empty primary result means the
 2923: 	# key set is provably empty (left: primary defines it; inner: ∩ ∅ = ∅).
 2924: 	# Skipping secondary fetches avoids up to N-1 unnecessary DA round-trips.
 2925: 	#
 2926: 	# Two guards prevent premature exit:
 2927: 	#   (a) join-column broadcast: when a join-col criterion is present it must
 2928: 	#       be physically delivered to each secondary DA (the call itself is what
 2929: 	#       forwards it; the partition only prepared the per-db slice).
 2930: 	#   (b) secondary-owned criteria: a secondary with its own criteria (e.g.
 2931: 	#       score => $val) must still be queried so those criteria are delivered.
 2932: 	#       Without the call the DA never receives them — breaking the partition-
 2933: 	#       isolation invariant the security tests verify.
 2934: 	my $local_jc_0_early    = $self->{_join_map}{0} // $join_col;
 2935: 	my $sec_has_criteria    = grep { %{ $per_db->[$_] } } 1 .. $n - 1;
 2936: 	return [] if !%{ $indexed[0] }
 2937: 	          && $join_type ne 'outer'
 2938: 	          && !exists $per_db->[0]{$local_jc_0_early}
 2939: 	          && !$sec_has_criteria;
 2940: 
 2941: 	# Fetch and index secondary databases.
 2942: 	# With parallel => 1 and 2+ secondaries: spawn one Perl thread per secondary
 2943: 	# so all secondaries are queried concurrently.  Total latency becomes
 2944: 	# max(DA latencies) instead of sum(DA latencies).  The primary was already
 2945: 	# fetched sequentially above (needed for the early-exit short-circuit).
 2946: 	# The thread closure captures only $db, $crit, and $local_jc (plain scalars
 2947: 	# or in-memory data) — $self is intentionally not captured to avoid copying
 2948: 	# the full blessed hashref (including all DA refs) into each thread.
 2949: 	# Falls back to sequential when: threads module is unavailable, n <= 2, or
 2950: 	# parallel => 0 (the default).
 2951: 	#
 2952: 	# Windows / ithreads safety: cloned DBI handles inside DA objects are not
 2953: 	# thread-safe.  Pre-initialise every secondary slot to {} so that if a thread
 2954: 	# fails the slot holds a valid (empty) hashref rather than undef, which would
 2955: 	# corrupt the inner-join key-set resolution.  Any secondary whose thread does
 2956: 	# not deliver a valid hashref is re-fetched sequentially after all joins
 2957: 	# complete, preserving correctness on every platform.
 2958: 	$indexed[$_] = {} for 1 .. $n - 1;
 2959: 
 2960: 	if ($self->{_parallel} && $n > 2) {
  Mutants (Total: 4, Killed: 4, Survived: 0)
 2961: 		$HAS_THREADS //= do { local $@; eval { require threads; 1 } ? 1 : 0 };
 2962: 		if ($HAS_THREADS) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 2963: 			my @thr;
 2964: 			for my $i (1 .. $n - 1) {
 2965: 				my ($db, $crit, $local_jc) = (
 2966: 					$self->{_dbs}[$i], $per_db->[$i],
 2967: 					$self->{_join_map}{$i} // $join_col,
 2968: 				);
 2969: 				# Wrap thread creation: it can fail if the DA cannot be cloned
 2970: 				# (e.g. DBI handles on Windows).  On failure we skip the push so
 2971: 				# the sequential fallback below handles that secondary.
 2972: 				local $@;
 2973: 				my $t = eval {
 2974: 					threads->create(sub {
 2975: 						# eval inside the thread: prevents a DA exception from
 2976: 						# killing the thread and returning an empty list to join().
 2977: 						local $@;
 2978: 						my $rows = eval { $db->selectall_arrayref($crit) } // [];
 2979: 						my %idx;
 2980: 						for my $row (@{$rows}) {
 2981: 							my $key = $row->{$local_jc};
 2982: 							push @{$idx{$key}}, $row if defined $key;
 2983: 						}
 2984: 						return ($i, \%idx);
 2985: 					});
 2986: 				};
 2987: 				push @thr, $t if $t;
 2988: 			}
 2989: 
 2990: 			my %done;
 2991: 			for my $t (@thr) {
 2992: 				# eval on join: a thread that died (e.g. uncaught exception)
 2993: 				# causes join() to rethrow on some platforms.
 2994: 				local $@;
 2995: 				my ($i, $idx);
 2996: 				eval { ($i, $idx) = $t->join() };
 2997: 				if (defined $i && ref($idx) eq 'HASH') {

					

					
Mutants (Total: 1, Killed: 0, Survived: 1)
					

				
COND_INV_2997_5: Invert condition if to unless
MEDIUM: Add tests asserting both true and false outcomes

 2998: 					$indexed[$i] = $idx;
 2999: 					$done{$i}    = 1;
 3000: 				}
 3001: 			}
 3002: 
 3003: 			# Re-fetch sequentially any secondary not delivered by a thread.
 3004: 			# This covers: thread creation failure, thread death, or an empty
 3005: 			# result that may indicate a non-thread-safe DA (e.g. DBI on Windows).
 3006: 			for my $i (1 .. $n - 1) {
 3007: 				next if $done{$i};
 3008: 				$indexed[$i] = $self->_fetch_indexed($i, $per_db->[$i]);
 3009: 			}
 3010: 		} else {
 3011: 			carp 'Database::Join: parallel => 1 requires the threads module; falling back to sequential';
 3012: 			$indexed[$_] = $self->_fetch_indexed($_, $per_db->[$_]) for 1 .. $n - 1;
 3013: 		}
 3014: 	} else {
 3015: 		$indexed[$_] = $self->_fetch_indexed($_, $per_db->[$_]) for 1 .. $n - 1;
 3016: 	}
 3017: 
 3018: 	# Premise: the key-set resolution loop starts at i=1 (primary seeds %key_set).
 3019: 	# Conclusion: $had_criteria[0] is a dead store (D~); compute only for i >= 1.
 3020: 	#
 3021: 	# The broadcast join-column criterion (entry=>'A3' delivered to ALL databases)
 3022: 	# must NOT count as "had criteria" for secondaries.  It is a key-range selector
 3023: 	# on the merged view, not a predicate that bounds what the secondary contributes.
 3024: 	# Only base filters and non-join-column query-time criteria trigger inner-join.
●3025 → 3026 → 3041 3025: 	my @had_criteria;
 3026: 	for my $i (1 .. $n - 1) {
 3027: 		my $local_jc   = $self->{_join_map}{$i} // $join_col;
 3028: 		my $has_filter = !!%{ $self->{_filters}{$i} // {} };
 3029: 		my $crit       = $per_db->[$i];
 3030: 		# Avoid copying the criteria hash just to delete the join-col key.
 3031: 		# Arithmetic is O(1) allocations: count total keys, subtract 1 when the
 3032: 		# local join-col key is present.  Any result > 0 is truthy (has own criteria).
 3033: 		my $own = (keys %{$crit}) - (exists $crit->{$local_jc} ? 1 : 0);
 3034: 		$had_criteria[$i] = $has_filter || $own;
 3035: 	}
 3036: 
 3037: 	# Seed the key set from the primary database.
 3038: 	# Hash-slice assignment avoids the intermediate 2K-element flat list that
 3039: 	# map { $_ => 1 } would allocate before assigning to %key_set.  Values are
 3040: 	# undef; only exists() is used for lookups, so the sentinel value is irrelevant.
●3041 → 3048 → 3070 3041: 	my %key_set;
 3042: 	@key_set{ keys %{ $indexed[0] } } = ();
 3043: 
 3044: 	# Merge in each secondary database.
 3045: 	# Premise 1: indexed[$i] is a valid hashref (returned by _fetch_indexed).
 3046: 	# Premise 2: join_type ∈ {left, inner, outer} (enforced by validate_strict).
 3047: 	# Conclusion: the three branches below are exhaustive and mutually exclusive.
 3048: 	for my $i (1 .. $n - 1) {
 3049: 		if ($had_criteria[$i] || $join_type eq 'inner') {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3050: 			# Intersect: single-pass delete for keys absent from this secondary.
 3051: 			# A single loop avoids the intermediate list that grep would allocate
 3052: 			# before the delete loop could iterate it (saves O(K) allocations).
 3053: 			for my $k (keys %key_set) {
 3054: 				delete $key_set{$k} unless exists $indexed[$i]{$k};
 3055: 			}
 3056: 		} elsif ($join_type eq 'outer') {
 3057: 			# Union: hash slice assignment is a single Perl op, not a per-key loop.
 3058: 			@key_set{ keys %{ $indexed[$i] } } = ();
 3059: 		}
 3060: 		# left + no criteria: key_set unchanged (primary defines the set).
 3061: 	}
 3062: 
 3063: 	# Pre-hoist per-secondary constants outside the key loop.
 3064: 	# $sec_local_jc[$i], the rename flag, and $sec_renames[$i] are all invariant
 3065: 	# across every key and every primary row.  Computing them inside the key loop
 3066: 	# wastes K dereferences per secondary database (K = number of qualifying keys).
 3067: 	# Splitting the inner column loop on the rename flag eliminates the flag check
 3068: 	# from inside the per-column loop, saving R-1 branch evaluations per secondary
 3069: 	# per row (R = columns in the secondary row).
●3070 → 3071 → 3081 3070: 	my (@sec_local_jc, @sec_rename, @sec_renames);
 3071: 	for my $i (1 .. $n - 1) {
 3072: 		$sec_local_jc[$i] = $self->{_join_map}{$i};
 3073: 		$sec_rename[$i]   = ($sec_local_jc[$i] && $sec_local_jc[$i] ne $join_col) ? 1 : 0;
 3074: 		# _col_rename[$i] is always initialised to {} by _build_col_index / add_database
 3075: 		# (transitive reduction: the // {} fallback can never trigger).
 3076: 		$sec_renames[$i]  = $self->{_col_rename}[$i];
 3077: 	}
 3078: 
 3079: 	# Cache the removed-column list across calls; avoids extracting keys %hash every
 3080: 	# query.  Lazily built here and invalidated to undef by remove_column().
●3081 → 3090 → 3104 3081: 	my $removed = ($self->{_removed_list} //= [keys %{ $self->{_removed_cols} }]);
 3082: 
 3083: 	# Parse sort_by once before the merge loop so we can decide whether the
 3084: 	# initial O(K log K) sort of %key_set is necessary.
 3085: 	# When sort_by targets a column other than the join_col, that initial sort
 3086: 	# is overridden by the final Schwarzian pass -- skip it to save a full sort.
 3087: 	# When sort_by is absent or targets the join_col, the initial sort IS the
 3088: 	# final order and must be kept.
 3089: 	my ($ob_col, $ob_dir) = ($join_col, 'ASC');
 3090: 	if (defined $sort_by) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3091: 		my ($req_col, $req_dir) = ref($sort_by) eq 'ARRAY' ? @{$sort_by} : ($sort_by, 'ASC');
 3092: 		$req_dir = uc($req_dir // 'ASC');
 3093: 		unless ($req_dir eq 'ASC' || $req_dir eq 'DESC') {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3094: 			carp "Database::Join: sort_by direction '$req_dir' is not supported; using ASC";
 3095: 			$req_dir = 'ASC';
 3096: 		}
 3097: 		if ($req_col ne $join_col && !exists $self->{_col_db}{$req_col}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3098: 			carp "Database::Join: sort_by column '$req_col' is not in the merged view; result sorted by join_column";
 3099: 		} else {
 3100: 			($ob_col, $ob_dir) = ($req_col, $req_dir);
 3101: 		}
 3102: 	}
 3103: 	# True when sort_by targets a non-join column: the initial sort is redundant.
●3104 → 3113 → 3171 3104: 	my $ob_override = ($ob_col ne $join_col);
 3105: 
 3106: 	# Build one merged result row for every primary-database row that qualifies.
 3107: 	# Secondary databases act as lookup tables: when a key maps to multiple
 3108: 	# secondary rows, the last one wins (consistent with construction-time
 3109: 	# last-database-wins column routing).
 3110: 	my @result;
 3111: 	# When sort_by will override the join_col order, iterate keys unsorted (O(K))
 3112: 	# instead of sorted (O(K log K)); the Schwarzian pass at the end reorders.
 3113: 	for my $key ($ob_override ? keys %key_set : sort keys %key_set) {
 3114: 		# Iterate directly over the arrayref: avoids copying primary rows into a
 3115: 		# new @base_rows array (saves P element copies per key, P = rows per key).
 3116: 		# [{}] ensures outer-join keys absent from the primary produce one merged row.
 3117: 		for my $prow (@{ $indexed[0]{$key} // [{}] }) {
 3118: 			my %merged = %{$prow};
 3119: 
 3120: 			for my $i (1 .. $n - 1) {
 3121: 				my $sec_arr = $indexed[$i]{$key};
 3122: 				next unless $sec_arr && @{$sec_arr};
 3123: 
 3124: 				# Write secondary columns directly into %merged without copying
 3125: 				# the source row into a temporary hash first.
 3126: 				# Before: %row_copy = %{$src}   then %merged = (%merged,%row_copy)
 3127: 				#   → 2 full hash copies per secondary per row: O(C) + O(|merged|+C)
 3128: 				# After: per-key loop writes straight into %merged
 3129: 				#   → O(C) key assignments only; no intermediate allocation
 3130: 				my $src = $sec_arr->[-1];
 3131: 
 3132: 				if ($sec_rename[$i]) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3133: 					my $local_jc = $sec_local_jc[$i];
 3134: 					my $renames  = $sec_renames[$i];
 3135: 					for my $k (keys %{$src}) {
 3136: 						if ($k eq $local_jc) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3137: 							# Translate local join-key alias to the canonical join_column name
 3138: 							$merged{$join_col} = $src->{$k};
 3139: 						} elsif (my $pub = $renames->{$k}) {
 3140: 							$merged{$pub} = $src->{$k};
 3141: 						} else {
 3142: 							$merged{$k} = $src->{$k};
 3143: 						}
 3144: 					}
 3145: 				} else {
 3146: 					my $renames = $sec_renames[$i];
 3147: 					for my $k (keys %{$src}) {
 3148: 						if (my $pub = $renames->{$k}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3149: 							# Collision-renamed column: write under the published prefixed name
 3150: 							$merged{$pub} = $src->{$k};
 3151: 						} else {
 3152: 							$merged{$k} = $src->{$k};
 3153: 						}
 3154: 					}
 3155: 				}
 3156: 			}
 3157: 
 3158: 			delete @merged{@{$removed}} if @{$removed};
 3159: 			push @result, \%merged;
 3160: 		}
 3161: 	}
 3162: 
 3163: 	# Caller-specified ORDER BY.
 3164: 	# $ob_col / $ob_dir / $ob_override were parsed BEFORE the merge loop.
 3165: 	# Three cases:
 3166: 	#   1. Non-join-col override ($ob_override true): Schwarzian transform
 3167: 	#      O(R) key extractions + O(R log R) scalar comparisons -- cheaper than
 3168: 	#      O(2R log R) hash dereferences that a naive sort block would make.
 3169: 	#   2. join_col DESC: O(R) reverse -- already sorted ASC by the loop.
 3170: 	#   3. join_col ASC (default): already in order, nothing to do.
●3171 → 3171 → 3190 3171: 	if ($ob_override) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3172: 		# Schwarzian: decorate, sort, undecorate.
 3173: 		# String comparison (cmp).  For accurate numeric ordering on large
 3174: 		# numeric columns use backend => 'sqlite', which sorts by SQL type.
 3175: 		my @tagged = map { [$_, $_->{$ob_col} // ''] } @result;
 3176: 		if ($ob_dir eq 'DESC') {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3177: 			@result = map { $_->[0] } sort { $b->[1] cmp $a->[1] } @tagged;
 3178: 		} else {
 3179: 			@result = map { $_->[0] } sort { $a->[1] cmp $b->[1] } @tagged;
 3180: 		}
 3181: 	} elsif ($ob_dir eq 'DESC') {
 3182: 		# join_col DESC: O(R) reverse is cheaper than O(R log R) re-sort
 3183: 		# because the merge loop already produced join_col ASC order.
 3184: 		@result = reverse @result;
 3185: 	}
 3186: 	# join_col ASC: @result is already in join_column ascending order.
 3187: 
 3188: 	# LIMIT / OFFSET pagination — applied after ordering.
 3189: 	# splice removes elements from the front (offset) then truncates to limit.
●3190 → 3190 → 3193 3190: 	if (defined $offset && $offset > 0) {
  Mutants (Total: 4, Killed: 4, Survived: 0)
 3191: 		splice(@result, 0, $offset);
 3192: 	}
●3193 → 3193 → 3197 3193: 	if (defined $limit && $limit < scalar @result) {
  Mutants (Total: 4, Killed: 4, Survived: 0)
 3194: 		splice(@result, $limit);
 3195: 	}
 3196: 
 3197: 	return \@result;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3198: }
 3199: 
 3200: # _cache_fresh() -> bool
 3201: # Purpose: Check whether the SQLite join cache is still valid.
 3202: # Entry:   $self->{_sqlite_cache} may or may not be set.
 3203: # Exit:    Returns 1 if the cache exists, the DBI handle is active, the source
 3204: #          count matches, and all source updated() timestamps match.  Returns 0
 3205: #          if any of these conditions fail (caller must rebuild the cache).
 3206: sub _cache_fresh :Protected {
●3207 → 3221 → 3229 3207: 	my ($self) = @_;
 3208: 
 3209: 	my $cache = $self->{_sqlite_cache} // return 0;
 3210: 	my $n     = scalar @{ $self->{_dbs} };
 3211: 
 3212: 	return 0 if ($cache->{n} // 0) != $n;
  Mutants (Total: 3, Killed: 3, Survived: 0)
 3213: 
 3214: 	# Verify the DBI handle is still usable.
 3215: 	return 0 unless do { local $@; eval { $cache->{dbh}{Active} } };
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3216: 
 3217: 	# Verify that no source has been updated since the cache was built.
 3218: 	# If a source does not implement updated(), skip the timestamp check for
 3219: 	# it (the data is assumed stable; the cache stays valid indefinitely for
 3220: 	# that source unless add_database() is called or the object is destroyed).
 3221: 	for my $i (0 .. $n - 1) {
 3222: 		my $cached_ts = $cache->{updated}{$i} // next;   # not captured → skip
 3223: 		my $current_ts;
 3224: 		do { local $@; $current_ts = eval { $self->{_dbs}[$i]->updated() } };
 3225: 		next unless defined $current_ts;                  # no updated() → skip
 3226: 		return 0 if $current_ts != $cached_ts;
  Mutants (Total: 3, Killed: 3, Survived: 0)
 3227: 	}
 3228: 
 3229: 	return 1;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3230: }
 3231: 
 3232: # _build_sqlite_cache()
 3233: # Purpose: Create (or rebuild) the persistent SQLite join cache.  Spills each
 3234: #          source database into a temp SQLite file using filter-only criteria;
 3235: #          SQLite-backed sources are zero-copy ATTACHed instead of spilled.
 3236: #          Query-time criteria are NOT applied here — they become WHERE clauses
 3237: #          in the per-call SQL generated by _sqlite_join.
 3238: # _sql_quote_identifier( $name ) -> $quoted
 3239: # Purpose: Produce a properly double-quoted SQL identifier, escaping any
 3240: #          embedded double-quote characters by doubling them (SQL standard).
 3241: #          Defence-in-depth: prevents SQL identifier injection when DA-supplied
 3242: #          column names or table names contain literal double-quote characters.
 3243: #          SQLite, like all ANSI SQL databases, represents a literal " inside a
 3244: #          double-quoted identifier as ""; this routine applies that transform.
 3245: # Entry:   $name — raw identifier string (column name, table name, or alias).
 3246: # Exit:    Returns the double-quoted, injection-safe SQL identifier string.
 3247: sub _sql_quote_identifier {
 3248: 	my ($name) = @_;
 3249: 	(my $safe = $name) =~ s/"/""/g;
 3250: 	return "\"$safe\"";
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3251: }
 3252: 
 3253: # Entry:   _dbs, _join_map, _filters, _tmpdir must be set.
 3254: # Exit:    $self->{_sqlite_cache} holds {dbh, tmpfile, table_refs, source_cols,
 3255: #          is_attached, updated, n}.  Any previous cache is disconnected first.
 3256: #          Each spilled table has a B-tree index on its join column.
 3257: # Effects: Creates a File::Temp file (SUFFIX='.db', DIR=_tmpdir, UNLINK=1).
 3258: #          Croaks with error_sqlite_connect if DBI::connect fails.
 3259: sub _build_sqlite_cache :Protected {
●3260 → 3263 → 3268 3260: 	my ($self) = @_;
 3261: 
 3262: 	# Disconnect any previous cache to release the old temp file.
 3263: 	if (my $old = delete $self->{_sqlite_cache}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3264: 		local $@;
 3265: 		eval { $old->{dbh}->disconnect } if $old->{dbh};
 3266: 	}
 3267: 
●3268 → 3287 → 3369 3268: 	require DBI;
 3269: 	require File::Temp;
 3270: 
 3271: 	my $join_col = $self->{_join_col};
 3272: 	my $n        = scalar @{ $self->{_dbs} };
 3273: 
 3274: 	my $tmpfile = File::Temp->new(
 3275: 		SUFFIX => '.db',
 3276: 		DIR    => $self->{_tmpdir},
 3277: 		UNLINK => 1,
 3278: 	);
 3279: 
 3280: 	my $tmpdbh = DBI->connect(
 3281: 		'dbi:SQLite:dbname=' . $tmpfile->filename, '', '',
 3282: 		{ RaiseError => 1, PrintError => 0, AutoCommit => 1 },
 3283: 	) or croak $self->_err('error_sqlite_connect', DBI->errstr // 'unknown error');
 3284: 
 3285: 	my (@table_refs, @source_cols, @is_attached);
 3286: 
 3287: 	for my $i (0 .. $n - 1) {
 3288: 		my $db       = $self->{_dbs}[$i];
 3289: 		my $local_jc = $self->{_join_map}{$i} // $join_col;
 3290: 
 3291: 		# Zero-copy ATTACH path: unconditionally available when the source
 3292: 		# implements dbi_source() returning a live SQLite handle.  Query-time
 3293: 		# criteria for this source will go into the SQL WHERE clause.
 3294: 		# eval wraps can() to suppress ISA warnings from stub packages in tests.
 3295: 		if (do { local $@; eval { $db->can('dbi_source') } }) {

					

					
Mutants (Total: 1, Killed: 0, Survived: 1)
					

				
COND_INV_3295_3: Invert condition if to unless
MEDIUM: Add tests asserting both true and false outcomes

 3296: 			# Probe dbi_source() in an isolated scope: local $@ prevents leaking
 3297: 			# eval failure into the caller's $@; local $SIG{__WARN__} suppresses
 3298: 			# spurious warnings from inherited dbi_source() probing non-SQLite DAs
 3299: 			# (e.g. Database::Abstraction 0.46 base-class dbi_source() calling
 3300: 			# _open() on a DA with no backing file).
 3301: 			my $src = do {
 3302: 				local $SIG{__WARN__} = sub {};
 3303: 				local $@;
 3304: 				eval { $db->dbi_source() }
 3305: 			};
 3306: 			if ($src && ref($src) eq 'HASH' && $src->{dbh} && $src->{table}
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3307: 				&& eval { $src->{dbh}{Driver}{Name} } eq 'SQLite') {
 3308: 				my ($db_file) = $src->{dbh}->selectrow_array(
 3309: 					"SELECT file FROM pragma_database_list WHERE name='main'"
 3310: 				);
 3311: 				my $alias = "ext$i";
 3312: 				$tmpdbh->do(sprintf("ATTACH DATABASE %s AS %s",
 3313: 					$tmpdbh->quote($db_file), $alias));
 3314: 				$table_refs[$i]  = $alias . '.' . _sql_quote_identifier($src->{table});
 3315: 				$is_attached[$i] = 1;
 3316: 				# Transitive reduction: new() and add_database() both validate
 3317: 				# can('columns') before registering any DA (P1 invariant).
 3318: 				# The else branch is dead code; the guard is vacuous.
 3319: 				$source_cols[$i] = $db->columns();
 3320: 				next;
 3321: 			}
 3322: 		}
 3323: 
 3324: 		# Spill path: fetch rows using filter-only criteria.  Query-time
 3325: 		# criteria are NOT applied here — they become WHERE clauses per call.
 3326: 		my $filter_crit = $self->{_filters}{$i} // {};
 3327: 		my $rows        = $db->selectall_arrayref($filter_crit) // [];
 3328: 
 3329: 		# No need to check if $rows exists or not
 3330: 		# Transitive reduction (P1 invariant): can('columns') is guaranteed for
 3331: 		# all _dbs elements
 3332: 		$source_cols[$i] = $db->columns();
 3333: 
 3334: 		my $tbl  = "t$i";
 3335: 		my $cols = $source_cols[$i] // [];
 3336: 
 3337: 		my $col_defs = join(', ', map { _sql_quote_identifier($_) . ' TEXT' } @{$cols});
 3338: 		$tmpdbh->do('CREATE TABLE ' . _sql_quote_identifier($tbl) . " ($col_defs)");
 3339: 		# Index on the join column: upgrades ON-clause equality lookups from
 3340: 		# an O(N²) full-table nested-loop scan to O(N log N) b-tree seek.
 3341: 		# SQLite query planner uses it for INNER JOIN / LEFT JOIN ON expressions.
 3342: 		$tmpdbh->do('CREATE INDEX ' . _sql_quote_identifier("${tbl}_jc")
 3343: 			. ' ON ' . _sql_quote_identifier($tbl)
 3344: 			. ' (' . _sql_quote_identifier($local_jc) . ')');
 3345: 
 3346: 		if (@{$rows}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3347: 			my $col_list     = join(', ', map { _sql_quote_identifier($_) } @{$cols});
 3348: 			my $placeholders = join(', ', ('?') x scalar @{$cols});
 3349: 			my $sth = $tmpdbh->prepare(
 3350: 				'INSERT INTO ' . _sql_quote_identifier($tbl) . " ($col_list) VALUES ($placeholders)"
 3351: 			);
 3352: 			my $batch = 0;
 3353: 			$tmpdbh->begin_work;
 3354: 			for my $row (@{$rows}) {
 3355: 				$sth->execute(map { $row->{$_} } @{$cols});
 3356: 				if (++$batch >= 1_000) {

					

					
Mutants (Total: 4, Killed: 0, Survived: 4)
					

				
NUM_BOUNDARY_3356_18_>: Numeric boundary flip >= to >
HIGH: Likely missing edge-case test (boundary value)

								

								
🧪 Suggested Test
								
# Boundary test suggestion
is( func(VALUE_AT_BOUNDARY), EXPECTED, 'Test boundary behaviour' );

								
							
NUM_BOUNDARY_3356_18_<: Numeric boundary flip >= to <
HIGH: Likely missing edge-case test (boundary value)

								

								
🧪 Suggested Test
								
# Boundary test suggestion
is( func(VALUE_AT_BOUNDARY), EXPECTED, 'Test boundary behaviour' );

								
							
NUM_BOUNDARY_3356_18_<=: Numeric boundary flip >= to <=
HIGH: Likely missing edge-case test (boundary value)

								

								
🧪 Suggested Test
								
# Boundary test suggestion
is( func(VALUE_AT_BOUNDARY), EXPECTED, 'Test boundary behaviour' );

								
							
COND_INV_3356_5: Invert condition if to unless
MEDIUM: Add tests asserting both true and false outcomes

 3357: 					$tmpdbh->commit;
 3358: 					$tmpdbh->begin_work;
 3359: 					$batch = 0;
 3360: 				}
 3361: 			}
 3362: 			$tmpdbh->commit;
 3363: 		}
 3364: 		$table_refs[$i]  = _sql_quote_identifier($tbl);
 3365: 		$is_attached[$i] = 0;
 3366: 	}
 3367: 
 3368: 	# Snapshot updated() timestamps for cache-validity checks.
●3369 → 3370 → 3376 3369: 	my %updated;
 3370: 	for my $i (0 .. $n - 1) {
 3371: 		local $@;
 3372: 		my $ts = eval { $self->{_dbs}[$i]->updated() };
 3373: 		$updated{$i} = $ts unless $@;
 3374: 	}
 3375: 
 3376: 	$self->{_sqlite_cache} = {
 3377: 		dbh         => $tmpdbh,
 3378: 		tmpfile     => $tmpfile,
 3379: 		table_refs  => \@table_refs,
 3380: 		source_cols => \@source_cols,
 3381: 		is_attached => \@is_attached,
 3382: 		updated     => \%updated,
 3383: 		n           => $n,
 3384: 	};
 3385: 
 3386: 	return;
 3387: }
 3388: 
 3389: # _sqlite_join( \%params ) -> \@merged_rows
 3390: #
 3391: # Purpose: Join via a persistent SQLite database cache.  The first call (or
 3392: #          any call after a source updated() changes) spills source data into
 3393: #          a File::Temp SQLite file via _build_sqlite_cache; subsequent calls
 3394: #          reuse the same file and handle.  A single SQL JOIN with a per-call
 3395: #          WHERE clause (built from query-time criteria) produces the result.
 3396: #          For 'auto' mode, uses count() or dbi_source() COUNT(*) to check
 3397: #          the threshold without fetching rows; falls back to
 3398: #          _joined_query_array when count <= $self->{_max_array_rows} or when
 3399: #          no count method is available.
 3400: # Entry:   $params is the query criteria hashref.
 3401: # Exit:    Returns arrayref of merged hashrefs sorted by join_column.
 3402: # Effects: On the first call (or after cache invalidation), creates a
 3403: #          File::Temp SQLite file in _tmpdir; the file persists until the
 3404: #          Database::Join object is destroyed or the source data changes.
 3405: sub _sqlite_join :Protected {
●3406 → 3433 → 3444 3406: 	my ($self, $params, %opts) = @_;
 3407: 	my $count_only   = $opts{count_only} // 0;
 3408: 	my $sort_by     = $opts{sort_by};
 3409: 	my $limit        = $opts{limit};
 3410: 	my $offset       = $opts{offset};
 3411: 	my $create_table = $opts{create_table};  # when set, materialize into a real table
 3412: 
 3413: 	# Transitive reduction: _validate_pagination is the single validation site.
 3414: 	# count() and dbi_source() never supply limit/offset (both methods delete them
 3415: 	# before calling _sqlite_join), so for those callers this is a cheap undef-check.
 3416: 	($limit, $offset) = $self->_validate_pagination($limit, $offset);
 3417: 
 3418: 	my $backend   = $self->{_backend};
 3419: 	my $join_col  = $self->{_join_col};
 3420: 	my $join_type = $self->{_join_type};
 3421: 	my $n         = scalar @{ $self->{_dbs} };
 3422: 
 3423: 	# Partition query-time criteria only (no filter overlay).
 3424: 	# Filters are applied at cache-build time for spilled sources, and via the
 3425: 	# SQL WHERE clause for ATTACHed sources.  Keeping them separate means the
 3426: 	# cached tables can serve any query without rebuilding.
 3427: 	my $per_db_query = $self->_partition_criteria($params);
 3428: 
 3429: 	# Compute the full merged criteria (filter + query) for each source.
 3430: 	# Used for had_criteria (join-type semantics) and the WHERE clause for
 3431: 	# ATTACHed sources (which were not filtered at spill time).
 3432: 	my @per_db_full;
 3433: 	for my $i (0 .. $n - 1) {
 3434: 		my $base = $self->{_filters}{$i} // {};
 3435: 		$per_db_full[$i] = %{$base}
 3436: 			? _merge_criteria($base, $per_db_query->[$i])
 3437: 			: $per_db_query->[$i];
 3438: 	}
 3439: 
 3440: 	# Determine which secondary sources had effective criteria (inner-join semantics).
 3441: 	# The broadcast join-column criterion must NOT count — it is a key-range selector
 3442: 	# on the merged view, not a predicate that restricts the secondary's contribution.
 3443: 	# Base filters always count (documented: a filtered db is always inner-join).
●3444 → 3445 → 3458 3444: 	my @had_criteria;
 3445: 	for my $i (1 .. $n - 1) {
 3446: 		my $local_jc   = $self->{_join_map}{$i} // $join_col;
 3447: 		my $has_filter = !!%{ $self->{_filters}{$i} // {} };
 3448: 		my %q          = %{ $per_db_query->[$i] };
 3449: 		delete $q{$local_jc};
 3450: 		$had_criteria[$i] = $has_filter || !!%q;
 3451: 	}
 3452: 
 3453: 	# For 'auto' mode: check total row count without fetching rows.
 3454: 	# Count(*) is used for dbi_source() sources; count() for others.
 3455: 	# If any source supports neither, fall back to the array path.
 3456: 	# Skipped when create_table is set — dbi_source() has already committed to
 3457: 	# the SQLite path and we must materialise regardless of row count.
●3458 → 3458 → 3507 3458: 	if ($backend eq 'auto' && !defined $create_table) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3459: 		my $total     = 0;
 3460: 		my $can_count = 1;
 3461: 		for my $i (0 .. $n - 1) {
 3462: 			my $db  = $self->{_dbs}[$i];
 3463: 			# dbi_source() path: COUNT(*) against the entire source table
 3464: 			# (no WHERE) gives a conservative upper bound on the spilled size.
 3465: 			if (do { local $@; eval { $db->can('dbi_source') } }) {

					

					
Mutants (Total: 1, Killed: 0, Survived: 1)
					

				
COND_INV_3465_4: Invert condition if to unless
MEDIUM: Add tests asserting both true and false outcomes

 3466: 				my $src = do {
 3467: 					local $SIG{__WARN__} = sub {};
 3468: 					local $@;
 3469: 					eval { $db->dbi_source() }
 3470: 				};
 3471: 				if ($src && ref($src) eq 'HASH' && $src->{dbh} && $src->{table}
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3472: 					&& eval { $src->{dbh}{Driver}{Name} } eq 'SQLite') {
 3473: 					my ($cnt) = $src->{dbh}->selectrow_array(
 3474: 						'SELECT COUNT(*) FROM '
 3475: 						. _sql_quote_identifier($src->{table})
 3476: 					);
 3477: 					$total += $cnt // 0;
 3478: 					next;
 3479: 				}
 3480: 			}
 3481: 			# count() path: only use it when the DA's own class directly defines
 3482: 			# count() (not inherited).  Database::Abstraction's inherited count($entry)
 3483: 			# takes a key argument and emits uninitialized-value warnings when called
 3484: 			# with no args, so we must not invoke it for the threshold probe.
 3485: 			my $pkg = ref($db) // '';
 3486: 			if ($pkg && do { no strict 'refs'; defined &{"${pkg}::count"} }) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3487: 				my ($cnt, $failed);
 3488: 				do { local $@; $cnt = eval { $db->count() }; $failed = $@ };
 3489: 				if ($failed) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3490: 					$can_count = 0;
 3491: 					last;
 3492: 				}
 3493: 				$total += $cnt // 0;
 3494: 				next;
 3495: 			}
 3496: 			$can_count = 0;
 3497: 			last;
 3498: 		}
 3499: 		if (!$can_count || $total <= $self->{_max_array_rows}) {
  Mutants (Total: 4, Killed: 4, Survived: 0)
 3500: 			my $rows = $self->_joined_query_array($params,
 3501: 				sort_by => $sort_by, limit => $limit, offset => $offset);
 3502: 			return $count_only ? scalar @{$rows} : $rows;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3503: 		}
 3504: 	}
 3505: 
 3506: 	# Ensure the SQLite cache is valid; rebuild if stale or absent.
●3507 → 3521 → 3573 3507: 	$self->_build_sqlite_cache() unless $self->_cache_fresh();
 3508: 
 3509: 	my $cache       = $self->{_sqlite_cache};
 3510: 	my $tmpdbh      = $cache->{dbh};
 3511: 	my @table_refs  = @{ $cache->{table_refs}  };
 3512: 	my @source_cols = @{ $cache->{source_cols} };
 3513: 	my @is_attached = @{ $cache->{is_attached} };
 3514: 
 3515: 	# Build the WHERE clause from per-call criteria.
 3516: 	#   Spilled sources: query-only criteria (filter already applied to spilled data).
 3517: 	#   ATTACHed sources: full criteria (filter + query), since source was not filtered.
 3518: 	#   Secondary tables (i>0): the broadcast join-column criterion is omitted because
 3519: 	#   it is already enforced by the ON clause; adding it to WHERE nullifies LEFT JOIN.
 3520: 	my (@where_parts, @bind_vals);
 3521: 	for my $i (0 .. $n - 1) {
 3522: 		my $crit = $is_attached[$i] ? $per_db_full[$i] : $per_db_query->[$i];
 3523: 		next unless %{$crit};
 3524: 		my $tref       = $table_refs[$i];
 3525: 		my $local_jc_i = $i > 0 ? ($self->{_join_map}{$i} // $join_col) : undef;

					

					
Mutants (Total: 3, Killed: 0, Survived: 3)
					

				
NUM_BOUNDARY_3525_23_<: Numeric boundary flip > to <
HIGH: Likely missing edge-case test (boundary value)

								

								
🧪 Suggested Test
								
# Boundary test suggestion
is( func(VALUE_AT_BOUNDARY), EXPECTED, 'Test boundary behaviour' );

								
							
NUM_BOUNDARY_3525_23_>=: Numeric boundary flip > to >=
HIGH: Likely missing edge-case test (boundary value)

								

								
🧪 Suggested Test
								
# Boundary test suggestion
is( func(VALUE_AT_BOUNDARY), EXPECTED, 'Test boundary behaviour' );

								
							
NUM_BOUNDARY_3525_23_<=: Numeric boundary flip > to <=
HIGH: Likely missing edge-case test (boundary value)

								

								
🧪 Suggested Test
								
# Boundary test suggestion
is( func(VALUE_AT_BOUNDARY), EXPECTED, 'Test boundary behaviour' );

								
							
 3526: 		for my $col (sort keys %{$crit}) {
 3527: 			next if defined $local_jc_i && $col eq $local_jc_i;
 3528: 			my $val = $crit->{$col};
 3529: 			if (ref($val) eq 'HASH') {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3530: 				for my $op (sort keys %{$val}) {
 3531: 					if ($SAFE_LIST_OPS{$op}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3532: 						# IN / NOT IN: value must be an arrayref; each element is bound.
 3533: 						my $arr = $val->{$op};
 3534: 						unless (ref($arr) eq 'ARRAY') {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3535: 							carp "Database::Join: operator '$op' requires an arrayref value; criterion skipped";
 3536: 							next;
 3537: 						}
 3538: 						my @items = @{$arr};
 3539: 						if (!@items) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3540: 							# IN ()  → always false: add a tautologically-false term.
 3541: 							# NOT IN () → always true: omit the term (all rows match).
 3542: 							push @where_parts, '1 = 0' if $op eq 'IN';
 3543: 							next;
 3544: 						}
 3545: 						push @where_parts,
 3546: 							$tref . '.' . _sql_quote_identifier($col)
 3547: 							. " $op (" . join(', ', ('?') x scalar @items) . ')';
 3548: 						push @bind_vals, @items;
 3549: 						next;
 3550: 					}
 3551: 					if ($SAFE_NOARG_OPS{$op}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3552: 						# IS NULL / IS NOT NULL: no bind parameter at all.
 3553: 						push @where_parts, $tref . '.' . _sql_quote_identifier($col) . " $op";
 3554: 						next;
 3555: 					}
 3556: 					unless ($SAFE_SQL_OPS{$op}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3557: 						carp "Database::Join: operator '$op' is not supported on the SQLite backend; criterion skipped (use the array backend or a supported operator)";
 3558: 						next;
 3559: 					}
 3560: 					push @where_parts, $tref . '.' . _sql_quote_identifier($col) . " $op ?";
 3561: 					push @bind_vals, $val->{$op};
 3562: 				}
 3563: 			} elsif (!defined $val) {
 3564: 				# A bare undef value means "WHERE col IS NULL".
 3565: 				# (col = NULL is always UNKNOWN in SQL and would match nothing.)
 3566: 				push @where_parts, $tref . '.' . _sql_quote_identifier($col) . ' IS NULL';
 3567: 			} else {
 3568: 				push @where_parts, $tref . '.' . _sql_quote_identifier($col) . ' = ?';
 3569: 				push @bind_vals, $val;
 3570: 			}
 3571: 		}
 3572: 	}
●3573 → 3585 → 3597 3573: 	my $where_sql = @where_parts ? ' WHERE ' . join(' AND ', @where_parts) : '';
 3574: 
 3575: 	my $local_jc_0 = $self->{_join_map}{0} // $join_col;
 3576: 
 3577: 	# Build JOIN clauses.  Hoisted before the SELECT list so the count_only
 3578: 	# path can return early without building the (unused) column expressions.
 3579: 	# Join type mirrors the key-set semantics of _joined_query_array:
 3580: 	#   had_criteria[i] OR inner  => INNER JOIN
 3581: 	#   outer (no criteria)       => FULL OUTER JOIN
 3582: 	#   left  (no criteria)       => LEFT JOIN
 3583: 	my $from     = $table_refs[0];
 3584: 	my $join_sql = '';
 3585: 	for my $i (1 .. $n - 1) {
 3586: 		my $local_jc = $self->{_join_map}{$i} // $join_col;
 3587: 		my $join_kw  = ($had_criteria[$i] || $join_type eq 'inner') ? 'JOIN'
 3588: 		             : ($join_type eq 'outer')                       ? 'FULL OUTER JOIN'
 3589: 		             :                                                  'LEFT JOIN';
 3590: 		$join_sql .= " $join_kw $table_refs[$i]"
 3591: 		          . ' ON ' . $table_refs[0] . '.' . _sql_quote_identifier($local_jc_0)
 3592: 		          . ' = '  . $table_refs[$i] . '.' . _sql_quote_identifier($local_jc);
 3593: 	}
 3594: 
 3595: 	# COUNT(*) short-circuit: WHERE and JOIN are built; SELECT list and ORDER BY
 3596: 	# are not needed.  selectrow_array returns a single integer without fetching rows.
●3597 → 3597 → 3611 3597: 	if ($count_only) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3598: 		my ($cnt) = $tmpdbh->selectrow_array(
 3599: 			'SELECT COUNT(*) FROM ' . $from . $join_sql . $where_sql,
 3600: 			undef,
 3601: 			@bind_vals,
 3602: 		);
 3603: 		return $cnt // 0;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3604: 	}
 3605: 
 3606: 	# Build SELECT clause.
 3607: 	# Walk sources in order, applying collision_prefix renaming exactly as
 3608: 	# _build_col_index does: first occurrence of a column name wins; subsequent
 3609: 	# occurrences in a source that has a collision_prefix are published as
 3610: 	# "$prefix.$col"; removed columns are omitted.
●3611 → 3616 → 3634 3611: 	my %pub_seen;
 3612: 	my @selects;
 3613: 
 3614: 	# Join-column expression: for outer joins, use COALESCE across all sources
 3615: 	# so that B-only (primary-absent) rows carry their join key rather than NULL.
 3616: 	unless ($self->{_removed_cols}{$join_col}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3617: 		my $jc_expr;
 3618: 		if ($join_type eq 'outer' && $n > 1) {
  Mutants (Total: 4, Killed: 4, Survived: 0)
 3619: 			$jc_expr = 'COALESCE('
 3620: 			         . join(', ', map {
 3621: 			               my $lc = $self->{_join_map}{$_} // $join_col;
 3622: 			               $table_refs[$_] . '.' . _sql_quote_identifier($lc)
 3623: 			           } 0 .. $n - 1)
 3624: 			         . ') AS ' . _sql_quote_identifier($join_col);
 3625: 		} else {
 3626: 			$jc_expr = $table_refs[0] . '.' . _sql_quote_identifier($local_jc_0);
 3627: 			$jc_expr .= ' AS ' . _sql_quote_identifier($join_col) if $local_jc_0 ne $join_col;
 3628: 		}
 3629: 		push @selects, $jc_expr;
 3630: 		$pub_seen{$join_col} = 1;
 3631: 	}
 3632: 
 3633: 	# Non-join columns from the primary table.
●3634 → 3634 → 3642 3634: 	for my $col (@{$source_cols[0] // []}) {
 3635: 		next if $col eq $local_jc_0;	# join column already handled above
 3636: 		next if $self->{_removed_cols}{$col};
 3637: 		$pub_seen{$col} = 1;
 3638: 		push @selects, $table_refs[0] . '.' . _sql_quote_identifier($col);
 3639: 	}
 3640: 
 3641: 	# Non-join columns from secondary tables, with collision_prefix renaming.
●3642 → 3642 → 3666 3642: 	for my $i (1 .. $n - 1) {
 3643: 		my $local_jc = $self->{_join_map}{$i} // $join_col;
 3644: 		my $prefix   = $self->{_collision_prefix}{$i};
 3645: 		for my $col (@{$source_cols[$i] // []}) {
 3646: 			next if $col eq $local_jc;	# join key already contributed above
 3647: 			my $pub = $col;
 3648: 			$pub = "$prefix.$col"
 3649: 				if defined $prefix && exists $pub_seen{$col} && $col ne $join_col;
 3650: 			next if $self->{_removed_cols}{$pub};
 3651: 			$pub_seen{$pub} = 1;
 3652: 			my $expr = $table_refs[$i] . '.' . _sql_quote_identifier($col);
 3653: 			$expr   .= ' AS ' . _sql_quote_identifier($pub) if $pub ne $col;
 3654: 			push @selects, $expr;
 3655: 		}
 3656: 	}
 3657: 
 3658: 	# dbi_source() materialisation path: CREATE TABLE name AS SELECT ...
 3659: 	# Executed BEFORE ORDER BY / LIMIT / OFFSET because they are irrelevant here —
 3660: 	# the parent join will impose its own ordering and pagination per-call.
 3661: 	# _sql_quote_identifier guards against any injection via the table name.
 3662: 	# D~: $sort_by, $limit, $offset are dead stores on this path.  They were
 3663: 	# parsed and validated above (shared with the normal query path) but the
 3664: 	# caller of create_table always passes undef for these, so _validate_pagination
 3665: 	# is a no-op and the variables are harmlessly abandoned at this return.
●3666 → 3666 → 3681 3666: 	if (defined $create_table) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3667: 		my $mat_sql = 'SELECT ' . join(', ', @selects)
 3668: 		            . ' FROM ' . $from . $join_sql . $where_sql;
 3669: 		$tmpdbh->do('DROP TABLE IF EXISTS ' . _sql_quote_identifier($create_table));
 3670: 		$tmpdbh->do('CREATE TABLE ' . _sql_quote_identifier($create_table)
 3671: 		          . ' AS ' . $mat_sql,
 3672: 		          undef, @bind_vals);
 3673: 		return;
 3674: 	}
 3675: 
 3676: 	# ORDER BY: default is join_column ascending.  Caller may override via
 3677: 	# sort_by => 'col' or sort_by => ['col', 'DESC'].
 3678: 	# For the join column on an outer join, reference the COALESCE alias.
 3679: 	# For any other column, reference the published SELECT-list alias —
 3680: 	# SQLite resolves ORDER BY aliases from the SELECT clause.
●3681 → 3682 → 3695 3681: 	my ($ob_col, $ob_dir) = ($join_col, 'ASC');
 3682: 	if (defined $sort_by) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3683: 		my ($req_col, $req_dir) = ref($sort_by) eq 'ARRAY' ? @{$sort_by} : ($sort_by, 'ASC');
 3684: 		$req_dir = uc($req_dir // 'ASC');
 3685: 		unless ($req_dir eq 'ASC' || $req_dir eq 'DESC') {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3686: 			carp "Database::Join: sort_by direction '$req_dir' is not supported; using ASC";
 3687: 			$req_dir = 'ASC';
 3688: 		}
 3689: 		if ($req_col ne $join_col && !exists $self->{_col_db}{$req_col}) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3690: 			carp "Database::Join: sort_by column '$req_col' is not in the merged view; result sorted by join_column";
 3691: 		} else {
 3692: 			($ob_col, $ob_dir) = ($req_col, $req_dir);
 3693: 		}
 3694: 	}
●3695 → 3710 → 3726 3695: 	my $order_expr = ($ob_col eq $join_col)
 3696: 	    ? (($join_type eq 'outer' && $n > 1)

					

					
Mutants (Total: 3, Killed: 0, Survived: 3)
					

				
NUM_BOUNDARY_3696_38_<: Numeric boundary flip > to <
HIGH: Likely missing edge-case test (boundary value)

								

								
🧪 Suggested Test
								
# Boundary test suggestion
is( func(VALUE_AT_BOUNDARY), EXPECTED, 'Test boundary behaviour' );

								
							
NUM_BOUNDARY_3696_38_>=: Numeric boundary flip > to >=
HIGH: Likely missing edge-case test (boundary value)

								

								
🧪 Suggested Test
								
# Boundary test suggestion
is( func(VALUE_AT_BOUNDARY), EXPECTED, 'Test boundary behaviour' );

								
							
NUM_BOUNDARY_3696_38_<=: Numeric boundary flip > to <=
HIGH: Likely missing edge-case test (boundary value)

								

								
🧪 Suggested Test
								
# Boundary test suggestion
is( func(VALUE_AT_BOUNDARY), EXPECTED, 'Test boundary behaviour' );

								
							
 3697: 	          ? _sql_quote_identifier($join_col)
 3698: 	          : $table_refs[0] . '.' . _sql_quote_identifier($local_jc_0))
 3699: 	    : _sql_quote_identifier($ob_col);
 3700: 	my $sql = 'SELECT '
 3701: 	        . join(', ', @selects)
 3702: 	        . ' FROM ' . $from . $join_sql
 3703: 	        . $where_sql
 3704: 	        . ' ORDER BY ' . $order_expr . ($ob_dir eq 'DESC' ? ' DESC' : '');
 3705: 
 3706: 	# LIMIT / OFFSET pagination — appended after ORDER BY as bind parameters
 3707: 	# (never interpolated) to prevent any SQL injection from caller values.
 3708: 	# OFFSET without LIMIT uses LIMIT -1 (SQLite extension: "all rows from offset").
 3709: 	my @page_bind;
 3710: 	if (defined $limit) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3711: 		$sql .= ' LIMIT ?';
 3712: 		push @page_bind, $limit;
 3713: 		if (defined $offset) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3714: 			$sql .= ' OFFSET ?';
 3715: 			push @page_bind, $offset;
 3716: 		}
 3717: 	} elsif (defined $offset) {
 3718: 		$sql .= ' LIMIT -1 OFFSET ?';
 3719: 		push @page_bind, $offset;
 3720: 	}
 3721: 
 3722: 	# prepare_cached reuses the parsed statement when the same SQL is executed
 3723: 	# again (e.g. identical criteria pattern in a pagination or batch loop),
 3724: 	# avoiding repeated statement compilation overhead.  fetchall_arrayref
 3725: 	# always exhausts the result set, so the handle is never left active.
 3726: 	my $sth = $tmpdbh->prepare_cached($sql);
 3727: 	$sth->execute(@bind_vals, @page_bind);
 3728: 	return $sth->fetchall_arrayref({});
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3729: }
 3730: 
 3731: # _merge_criteria( \%base, \%extra ) -> \%merged
 3732: # Purpose: Merge two criteria hashrefs for the same database column set.
 3733: # Entry:   %base is the permanent filter; %extra is the query-time criteria.
 3734: # Exit:    Returns a new hashref with both applied.
 3735: # Merging rule: when both values for the same column are operator hashrefs
 3736: #   (e.g. { '>' => 60 } and { '<' => 365 }), the operators are combined
 3737: #   so both constraints apply simultaneously (AND semantics).
 3738: #   Otherwise the extra (query-time) value overwrites the base value.
 3739: sub _merge_criteria :Protected {
●3740 → 3742 → 3751 3740: 	my ($base, $extra) = @_;
 3741: 	my %merged = %{$base};
 3742: 	for my $col (keys %{$extra}) {
 3743: 		if (exists $merged{$col}
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3744: 		        && ref($merged{$col}) eq 'HASH'
 3745: 		        && ref($extra->{$col}) eq 'HASH') {
 3746: 			$merged{$col} = { %{ $merged{$col} }, %{ $extra->{$col} } };
 3747: 		} else {
 3748: 			$merged{$col} = $extra->{$col};
 3749: 		}
 3750: 	}
 3751: 	return \%merged;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3752: }
 3753: 
 3754: # _copy_criteria( \%criteria ) -> \%copy
 3755: # Purpose: Return a two-level deep copy of a single criteria hashref so that
 3756: #          post-construction mutation of the caller's hash cannot change the
 3757: #          stored filter.  Operator sub-hashrefs (e.g. { '>' => 80 }) are
 3758: #          shallow-copied one additional level, matching the broadcast-copy
 3759: #          idiom used in _partition_criteria for join-column criteria.
 3760: # Entry:   $criteria is a hashref (may be undef).
 3761: # Exit:    Returns a new hashref; never returns the input reference itself.
 3762: sub _copy_criteria {
 3763: 	my ($criteria) = @_;
 3764: 	return {} unless $criteria && %{$criteria};
 3765: 	return {
 3766: 		map {
 3767: 			$_ => ref($criteria->{$_}) eq 'HASH'
 3768: 				? { %{ $criteria->{$_} } }  # shallow-copy operator sub-hashref
 3769: 				: $criteria->{$_}
 3770: 		} keys %{$criteria}
 3771: 	};
 3772: }
 3773: 
 3774: # _copy_filters( \%filters ) -> \%copy
 3775: # Purpose: Deep-copy the filters hashref (db_index => criteria_hashref) so
 3776: #          that post-construction mutation of the caller's hash cannot silently
 3777: #          bypass the inner-join row-security guarantee.
 3778: # Entry:   $filters may be undef.
 3779: # Exit:    Returns a new hashref; never returns the input reference itself.
 3780: sub _copy_filters {
 3781: 	my ($filters) = @_;
 3782: 	return {} unless $filters && %{$filters};
 3783: 	return { map { $_ => _copy_criteria($filters->{$_}) } keys %{$filters} };
 3784: }
 3785: 
 3786: # _msg( $i18n, $key, @sprintf_args ) -> $string
 3787: # Purpose: Format a user-facing message, routing through the i18n object when
 3788: #          one is provided.  Falls back to the built-in %MESSAGES dictionary.
 3789: # Entry:   $i18n may be undef.  $key must be a key in %MESSAGES.
 3790: # Exit:    Returns the formatted string.
 3791: sub _msg :Protected {
●3792 → 3794 → 3798 3792: 	my ($i18n, $key, @args) = @_;
 3793: 
 3794: 	if ($i18n && $i18n->can('translate')) {
  Mutants (Total: 1, Killed: 1, Survived: 0)
 3795: 		return $i18n->translate($key, @args);
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3796: 	}
 3797: 
 3798: 	my $fmt = $MESSAGES{$key}
 3799: 		// sprintf($MESSAGES{error_unknown_message}, $key);
 3800: 
 3801: 	return @args ? sprintf($fmt, @args) : $fmt;
  Mutants (Total: 2, Killed: 2, Survived: 0)
 3802: }
 3803: 
 3804: 1;
 3805: 
 3806: __END__
 3807: 
 3808: =head1 ENCODING
 3809: 
 3810: All text that passes through C<Database::Join> at the Perl layer (column names,
 3811: criteria values, merged row values) is treated as opaque strings.
 3812: C<Database::Join> does not inspect, encode, or transform string content.
 3813: 
 3814: =over 4
 3815: 
 3816: =item Column names
 3817: 
 3818: Column names are plain ASCII strings as returned by C<Database::Abstraction::columns()>.
 3819: Non-ASCII column names are accepted but not tested; behaviour depends on the
 3820: underlying DA and database driver.
 3821: 
 3822: =item Criteria values and row data
 3823: 
 3824: Values are passed verbatim between callers and component DAs.  Full UTF-8 is
 3825: safe as long as the underlying C<Database::Abstraction> objects and their
 3826: database drivers handle UTF-8 correctly.  C<Database::Join> neither encodes
 3827: nor decodes any value.
 3828: 
 3829: =item SQLite backend
 3830: 
 3831: When the SQLite path is active, values are inserted into the temporary SQLite
 3832: database via DBI placeholders (never string interpolation), so binary-safe
 3833: round-tripping depends on C<DBD::SQLite>'s character encoding settings.
 3834: By default C<DBD::SQLite> operates in UTF-8 mode, which is correct for text
 3835: data.  Binary blobs are not explicitly tested.
 3836: 
 3837: =item i18n messages
 3838: 
 3839: All internal error and warning messages route through the C<i18n> object
 3840: (if one is supplied) via a C<translate($key, @args)> call.  The translation
 3841: dictionary controls the final encoding of those strings.
 3842: 
 3843: =back
 3844: 
 3845: =head1 MESSAGES
 3846: 
 3847: The following messages can be produced by C<Database::Join>.  All messages
 3848: can be localised by supplying an C<i18n> object to C<new>.
 3849: 
 3850: =over 4
 3851: 
 3852: =item C<error_no_databases>
 3853: 
 3854: B<When:> The C<databases> arrayref passed to C<new> is empty.
 3855: 
 3856: B<Fix:> Pass at least one C<Database::Abstraction> subclass object.
 3857: 
 3858: =item C<error_invalid_db>
 3859: 
 3860: B<When:> An element of the C<databases> array (or the argument to
 3861: C<add_database>) is not an object, or is not a C<Database::Abstraction>
 3862: subclass.
 3863: 
 3864: B<Fix:> Instantiate the component database with its own C<new> method before
 3865: passing it to C<Database::Join>.
 3866: 
 3867: =item C<error_join_col_missing>
 3868: 
 3869: B<When:> The join key column (or its C<join_map> alias) does not exist in one
 3870: of the component databases.
 3871: 
 3872: B<Fix:> Either add the column to the database, change C<join_column> to a
 3873: column that is present everywhere, or use C<join_map> to declare the local
 3874: alias for databases that call it something different.
 3875: 
 3876: =item C<error_remove_join_col>
 3877: 
 3878: B<When:> C<remove_column> is called with the name of the join key column.
 3879: 
 3880: B<Fix:> The join key is required for the merge to work and cannot be hidden.
 3881: Remove a different column.
 3882: 
 3883: =item C<error_invalid_prefix>
 3884: 
 3885: B<When:> A value in the C<collision_prefix> hashref is a reference (e.g. a
 3886: hashref or arrayref) rather than a plain string.
 3887: 
 3888: B<Fix:> All C<collision_prefix> values must be plain strings.  A reference
 3889: would be stringified to C<HASH(0x...)> or C<ARRAY(0x...)>, leaking a heap
 3890: address into every column name returned by C<columns()>, C<schema()>, and all
 3891: query results.  Pass a plain string such as C<'db2'> or C<'secondary'>.
 3892: 
 3893: =item C<warn_unknown_column> (carp)
 3894: 
 3895: B<When:> A criterion is passed for a column that does not exist in any
 3896: component database (or has been removed with C<remove_column>).
 3897: 
 3898: B<Fix:> Check the column name spelling.  The criterion is ignored.
 3899: 
 3900: =item C<error_join_criterion>
 3901: 
 3902: B<When:> A criterion hashref contains a C<join =E<gt>> key (the
 3903: C<Database::Abstraction> SQL-JOIN syntax).
 3904: 
 3905: B<Fix:> C<join =E<gt>> targets a single table inside one DA; it cannot be
 3906: routed through a merged view.  Express multi-table relationships by adding
 3907: the joined table as a separate C<Database::Abstraction> object in the
 3908: C<databases =E<gt> []> constructor list instead.
 3909: 
 3910: =item C<error_query_unsupported>
 3911: 
 3912: B<When:> C<query()> is called on a C<Database::Join> object.
 3913: 
 3914: B<Fix:> Use C<selectall_arrayref>, C<selectall_array>, C<fetchrow_hashref>,
 3915: or C<count> instead.
 3916: 
 3917: =item C<error_execute_unsupported>
 3918: 
 3919: B<When:> C<execute()> is called on a C<Database::Join> object.
 3920: 
 3921: B<Fix:> Use the Perl-level query methods instead.  Raw SQL cannot span
 3922: heterogeneous database backends.
 3923: 
 3924: =item C<error_invalid_backend>
 3925: 
 3926: B<When:> The C<backend> parameter passed to C<new> is not one of C<'array'>,
 3927: C<'sqlite'>, or C<'auto'>.
 3928: 
 3929: B<Fix:> Use exactly one of those three strings.  The check is case-sensitive;
 3930: C<'SQLite'> or C<'Auto'> will not be accepted.
 3931: 
 3932: =item C<error_sqlite_connect>
 3933: 
 3934: B<When:> The SQLite join backend fails to open the temporary SQLite database
 3935: file.  Common causes: the C<tmpdir> directory is not writable, the filesystem
 3936: has no free space, or C<DBD::SQLite> is not installed.
 3937: 
 3938: B<Fix:> Check that the directory given by C<tmpdir> (or the system temp
 3939: directory if C<tmpdir> was not set) is writable and has sufficient free space.
 3940: Verify that C<DBD::SQLite> 1.70 or later is installed.
 3941: 
 3942: =back
 3943: 
 3944: =head1 REPOSITORY
 3945: 
 3946: L<https://github.com/nigelhorne/Database-Join>
 3947: 
 3948: =head1 SUPPORT
 3949: 
 3950: This module is provided as-is without any warranty.
 3951: 
 3952: =head1 SEE ALSO
 3953: 
 3954: =over 4
 3955: 
 3956: =item * L<Configure an Object at Runtime|Object::Configure>
 3957: 
 3958: =item * L<Test Dashboard|https://nigelhorne.github.io/Database-Join/coverage/>
 3959: 
 3960: =item * L<Database::Abstraction>
 3961: 
 3962: =back
 3963: 
 3964: =head1 SECURITY CONSIDERATIONS
 3965: 
 3966: C<Database::Join> is a pure in-memory routing and merge layer.  It never
 3967: generates SQL strings, never opens files, and never calls C<system()>,
 3968: C<exec()>, or C<eval()>.  The security properties described below are
 3969: architectural guarantees, not run-time checks.
 3970: 
 3971: =head2 What Database::Join guarantees
 3972: 
 3973: =over 4
 3974: 
 3975: =item Criteria partition isolation
 3976: 
 3977: Every criterion you pass to a query method is routed to I<exactly one>
 3978: component database (the one that owns that column), or to I<all> databases
 3979: when the criterion is on the join key column.  A hostile value in a criterion
 3980: for column C<name> (owned by database A) will never reach database B.
 3981: 
 3982: =item Unknown columns are rejected before reaching any database
 3983: 
 3984: If a criterion column name is not present in any component database (or has
 3985: been hidden with C<remove_column>), C<Database::Join> logs a C<carp> warning
 3986: and silently drops the criterion.  No database receives the hostile key.
 3987: 
 3988: =item AUTOLOAD only accepts word-character column names
 3989: 
 3990: Perl's method dispatch extracts the column name via C<\w+>, which matches only
 3991: C<[A-Za-z0-9_]>.  Hostile method names with shell metacharacters, quotes, or
 3992: spaces cannot reach the AUTOLOAD dispatch path.  Private names (starting with
 3993: C<_>) are additionally blocked with an explicit C<croak>.
 3994: 
 3995: =item No value sanitisation (by design)
 3996: 
 3997: C<Database::Join> does I<not> sanitise, HTML-encode, or validate the
 3998: I<values> in criteria hashrefs.  Preventing SQL injection is the
 3999: responsibility of the underlying C<Database::Abstraction> objects (which use
 4000: parameterised queries).  Preventing XSS or header injection is the
 4001: responsibility of the CGI or web layer that renders the output.
 4002: 
 4003: =item Taint-mode compatible (array path)
 4004: 
 4005: The array merge path contains no C<system()>, C<exec()>, backtick,
 4006: C<open(PIPE)>, or C<eval STRING> calls.  It neither opens files nor constructs
 4007: shell commands.  The AUTOLOAD regex C</ :: (\w++) \z /x> uses a possessive
 4008: quantifier (C<\w++>) and a strict end-of-string anchor (C<\z>) and produces
 4009: an I<untainted> capture, so the column name used for dispatch is clean under
 4010: C<-T>.  Criteria values are passed verbatim to component C<Database::Abstraction>
 4011: objects; those objects are responsible for handling tainted values at the SQL
 4012: parameterisation layer.
 4013: 
 4014: =item SQLite backend: column names are quoted, values are parametrised
 4015: 
 4016: When the SQLite path is active, C<Database::Join> generates SQL internally.
 4017: All column and table names are double-quoted (SQL identifier quoting) before
 4018: being embedded in statement strings.  All row values are passed to SQLite
 4019: exclusively through DBI prepared statement placeholders -- never by string
 4020: interpolation.  A hostile value in a source row therefore cannot inject SQL
 4021: into the temporary database.
 4022: 
 4023: The temporary SQLite file is created by C<File::Temp> using a securely random,
 4024: unpredictable filename.  No C<system()> or shell command is used to create or
 4025: remove it.  The connection is made with C<DBI-E<gt>connect(..., { RaiseError =E<gt> 1,
 4026: PrintError =E<gt> 0 })> and is closed before the method returns.  Column names
 4027: in the generated SQL come from C<columns()>, which is produced by
 4028: C<Database::Abstraction> at construction time -- they are not derived from
 4029: caller-supplied criteria values.
 4030: 
 4031: =item Operator hashref broadcast copy
 4032: 
 4033: When the same join-key criterion (an operator hashref such as
 4034: C<< { '>' => 'A' } >>) is broadcast to multiple component databases, each
 4035: database receives its own I<shallow copy> of the hashref.  A component database
 4036: that mutates the hashref's contents at the top level cannot affect what
 4037: subsequent databases receive.
 4038: 
 4039: =item collision_prefix value type guard
 4040: 
 4041: C<_build_col_index> rejects any C<collision_prefix> value that is a reference
 4042: (hashref, arrayref, coderef, etc.) with an immediate C<croak>.  A reference
 4043: value would stringify to C<"HASH(0x...)">, leaking a heap address into every
 4044: column name, C<columns()> listing, and merged row returned to the caller.  The
 4045: guard fires before any column name is constructed.
 4046: 
 4047: =item Filter deep-copy isolation
 4048: 
 4049: The C<filters> constructor parameter and the C<filter> option of
 4050: C<add_database()> are I<deep-copied> at the point of use.  The caller's
 4051: original hashrefs are never stored; post-construction mutation of those
 4052: hashrefs cannot widen or bypass the configured row-security constraints.
 4053: 
 4054: =back
 4055: 
 4056: =head2 What the caller is responsible for
 4057: 
 4058: =over 4
 4059: 
 4060: =item Sanitise values before building criteria
 4061: 
 4062: DJ passes criterion values verbatim to component databases.  If your
 4063: application accepts user-supplied filter values (e.g. from a CGI query
 4064: string), those values I<must> be validated or sanitised by your application
 4065: before being passed to DJ.
 4066: 
 4067: =item Restrict which columns the caller can filter on
 4068: 
 4069: Any column in C<columns()> can be used as a filter criterion.  If a column
 4070: should not be filterable by end users (e.g. an internal status flag), hide it
 4071: with C<remove_column> so that queries on it are silently dropped.
 4072: 
 4073: =item Do not expose the joined view directly to user-supplied criteria
 4074: 
 4075: DJ is not a firewall.  It faithfully routes user input to component databases.
 4076: Wrap DJ calls in a thin service layer that whitelists the permitted criterion
 4077: columns and validates their values.
 4078: 
 4079: =back
 4080: 
 4081: =head3 API SPECIFICATION (security surface)
 4082: 
 4083:     Input accepted by all query methods and passed through DJ to component databases:
 4084: 
 4085:     Criterion values:
 4086:         type: scalar string | operator hashref { OP => scalar }
 4087:         validation: NONE (DJ trusts the caller; component DA is responsible)
 4088:         max size: unconstrained (OOM risk on very large values)
 4089: 
 4090:     Column name keys in criteria:
 4091:         type: string
 4092:         validation: must be present in _col_db (else carp + drop)
 4093:         character set: any Perl string (including control chars); DJ does
 4094:                        not impose a character-set restriction on criteria KEYS
 4095: 
 4096:     AUTOLOAD method-name-as-column:
 4097:         type: \w+ (enforced by Perl regex /::(\w+)$/)
 4098:         validation: must not start with '_'; must be in _col_db
 4099: 
 4100: =encoding UTF-8
 4101: 
 4102: =head1 FORMAL SPECIFICATION
 4103: 
 4104: Z calculus schemas for the key invariants and operations.
 4105: Unicode is used throughout this section as required by Z notation.
 4106: 
 4107:     ─── Database_Join ─────────────────────────────────────────────────
 4108:     dbs            : seq DATABASE_ABSTRACTION
 4109:     join_col       : NAME
 4110:     join_type      : {left, inner, outer}
 4111:     join_map       : ℕ ⇸ NAME
 4112:     filters        : ℕ ⇸ CRITERIA
 4113:     col_db         : NAME ⇸ ℕ
 4114:     removed        : â„™ NAME
 4115:     backend        : {array, sqlite, auto}
 4116:     max_array_rows : â„•
 4117:     tmpdir         : PATH
 4118:     ───────────────────────────────────────────────────────────────────
 4119:     #dbs ≥ 1
 4120:     dom join_map ⊆ 0 ‥ (#dbs - 1)
 4121:     dom filters  ⊆ 0 ‥ (#dbs - 1)
 4122:     dom col_db   = (⋃ { i : 0 ‥ #dbs-1 • ran((dbs i).columns) }) \ removed
 4123:     join_col ∉ removed
 4124:     ∀ i : 0 ‥ #dbs-1 •
 4125:         local_jc(i) = if i ∈ dom join_map then join_map(i) else join_col
 4126:     ∀ i : 0 ‥ #dbs-1 •
 4127:         local_jc(i) ∈ ran((dbs i).columns)
 4128: 
 4129:     ─── Init ──────────────────────────────────────────────────────────
 4130:     ΔDatabase_Join
 4131:     dbs?            : seq DATABASE_ABSTRACTION
 4132:     join_col?       : NAME
 4133:     join_type?      : {left, inner, outer}
 4134:     join_map?       : ℕ ⇸ NAME
 4135:     filters?        : ℕ ⇸ CRITERIA
 4136:     base_criteria?  : CRITERIA              -- optional; column-name keyed
 4137:     removed?        : â„™ NAME
 4138:     backend?        : {array, sqlite, auto}   -- default auto
 4139:     max_array_rows? : â„•                      -- default 10000
 4140:     tmpdir?         : PATH                    -- default File::Spec->tmpdir
 4141:     ───────────────────────────────────────────────────────────────────
 4142:     #dbs? ≥ 1
 4143:     dbs'            = dbs?
 4144:     join_col'       = join_col?
 4145:     join_type'      = join_type?
 4146:     join_map'       = join_map?
 4147:     -- base_criteria is partitioned by column ownership and merged into filters:
 4148:     -- bc_slice(i) = partition(base_criteria?, i)
 4149:     -- filters'(i) = merge_criteria(bc_slice(i), filters?(i))
 4150:     --             when bc_slice(i) ≠ ∅; otherwise filters?(i)
 4151:     -- (filters? wins on plain-scalar conflicts; operator hashrefs are ANDed)
 4152:     filters'        = ∀ i : 0 ‥ #dbs?-1 •
 4153:                           merge_criteria(partition(base_criteria?, i), filters?(i) ∪ ∅)
 4154:     col_db'         = buildColIndex(dbs?, join_col?, join_map?)
 4155:     removed'        = removed?
 4156:     backend'        = backend?
 4157:     max_array_rows' = max_array_rows?
 4158:     tmpdir'         = tmpdir?
 4159: 
 4160:     ─── SelectAllArrayref ─────────────────────────────────────────────
 4161:     ΞDatabase_Join        -- state unchanged
 4162:     criteria? : CRITERIA
 4163:     result!   : seq MERGED_ROW
 4164:     ───────────────────────────────────────────────────────────────────
 4165:     ∀ c : dom criteria? • c ∈ dom col_db ∪ {join_col}
 4166:     result! = joinedQuery(criteria?)
 4167:     result! is sorted ascending by join_col value
 4168: 
 4169:     ─── AddDatabase ───────────────────────────────────────────────────
 4170:     ΔDatabase_Join
 4171:     db?         : DATABASE_ABSTRACTION
 4172:     local_jc?   : NAME   -- optional; defaults to join_col
 4173:     filter?     : CRITERIA   -- optional
 4174:     remove?     : â„™ NAME     -- optional
 4175:     ───────────────────────────────────────────────────────────────────
 4176:     db?.isa('Database::Abstraction')
 4177:     local_jc? ∈ ran(db?.columns)
 4178:     dbs'      = dbs ^ ⟨db?⟩
 4179:     col_db'   = col_db ⊕ { c ↦ #dbs | c ∈ ran(db?.columns) \ {local_jc?} \ removed }
 4180:     filters'  = if filter? ≠ ∅ then filters ⊕ {#dbs ↦ filter?} else filters
 4181:     join_map' = if local_jc? ≠ join_col
 4182:                 then join_map ⊕ {#dbs ↦ local_jc?}
 4183:                 else join_map
 4184:     removed'  = removed ∪ remove?
 4185: 
 4186:     ─── RemoveColumn ──────────────────────────────────────────────────
 4187:     ΔDatabase_Join
 4188:     col? : NAME
 4189:     ───────────────────────────────────────────────────────────────────
 4190:     col? ≠ join_col
 4191:     removed'  = removed ∪ {col?}
 4192:     col_db'   = col_db \ {col?}
 4193:     join_map' = join_map
 4194:     filters'  = filters
 4195:     dbs'      = dbs
 4196: 
 4197: =head2 collision_prefix
 4198: 
 4199:     ─── CollisionPrefix ───────────────────────────────────────────────
 4200:     collision_prefix : ℕ ⇸ STRING
 4201:     dbs              : seq DATABASE_ABSTRACTION
 4202:     col_db           : NAME ⇸ ℕ
 4203:     ───────────────────────────────────────────────────────────────────
 4204:     -- Only secondary databases (index > 0) carry a meaningful prefix:
 4205:     dom collision_prefix ⊆ 1 ‥ (#dbs - 1)
 4206: 
 4207:     -- Published name for column col from database i:
 4208:     published(i, col) ==
 4209:         if i ∈ dom collision_prefix ∧ col ∈ dom col_db ∧ col_db(col) < i
 4210:         then (collision_prefix i) ^ "." ^ col
 4211:         else col
 4212: 
 4213:     -- col_db routes the published name to the owning database:
 4214:     col_db(published(i, col)) = i
 4215: 
 4216:     -- The original column name is never removed from an earlier database:
 4217:     ∀ i : 1 ‥ #dbs-1; col : columns(dbs i) •
 4218:         published(i, col) ≠ col  ⟹
 4219:             ∃ j : 0 ‥ i-1 • col ∈ dom col_db ∧ col_db(col) = j
 4220: 
 4221: =head2 join_map
 4222: 
 4223:     ─── JoinMap ───────────────────────────────────────────────────────
 4224:     join_map : ℕ ⇸ NAME
 4225:     dbs      : seq DATABASE_ABSTRACTION
 4226:     join_col : NAME
 4227:     ───────────────────────────────────────────────────────────────────
 4228:     dom join_map ⊆ 0 ‥ (#dbs - 1)
 4229:     ∀ i : dom join_map • (join_map i) ∈ ran(dbs i).columns
 4230:     ∀ i : 0 ‥ (#dbs - 1) \ dom join_map •
 4231:         join_col ∈ ran(dbs i).columns
 4232: 
 4233:     -- Resolution of the local join-key name for database i:
 4234:     local_jc(i) == if i ∈ dom join_map then join_map(i) else join_col
 4235: 
 4236:     -- The canonical name is always join_col; local_jc is never exposed.
 4237: 
 4238: =head2 SECURITY INVARIANTS
 4239: 
 4240:     ─── PartitionIsolation ─────────────────────────────────────────────
 4241:     -- For every query call with criteria C and column col ≠ join_col:
 4242:     ∀ i : 0 ‥ #dbs-1 •
 4243:         i ≠ _col_db(col)  ⟹  col ∉ dom(per_db(i))
 4244: 
 4245:     -- Unknown column is dropped before any database sees it:
 4246:     col ∉ dom(_col_db) ∧ col ≠ join_col  ⟹
 4247:         (∀ i : 0 ‥ #dbs-1 • col ∉ dom(per_db(i)))
 4248: 
 4249:     ─── NoCodeExecution ────────────────────────────────────────────────
 4250:     -- DJ contains no call to system(), exec(), open(PIPE), or eval().
 4251:     -- Hostile criterion values therefore cannot achieve code execution
 4252:     -- within the Database::Join layer.
 4253:     ∀ v : VALUE • _joined_query({col ↦ v}) ≠ ⊥ due to code injection
 4254: 
 4255: =head2 filters
 4256: 
 4257:     ─── Filters ─────────────────────────────────────────────────────
 4258:     filters  : ℕ ⇸ CRITERIA
 4259:     dbs      : seq DATABASE_ABSTRACTION
 4260:     ─────────────────────────────────────────────────────────────────
 4261:     dom filters ⊆ 0 ‥ (#dbs - 1)
 4262: 
 4263:     -- A filtered database i always contributes to key-set intersection.
 4264:     -- For each query with criteria C:
 4265:     effective_criteria(i, C) ==
 4266:         if i ∈ dom filters
 4267:         then merge_criteria(filters(i), partition(C, i))
 4268:         else partition(C, i)
 4269: 
 4270:     -- Criteria merging (AND semantics for operator hashrefs):
 4271:     merge_criteria(base, extra) ==
 4272:         { col : dom base ∪ dom extra •
 4273:             if col ∈ dom base ∩ dom extra
 4274:                ∧ base(col) ∈ HASHREF ∧ extra(col) ∈ HASHREF
 4275:             then col ↦ base(col) ∪ extra(col)   -- operator union
 4276:             else col ↦ (if col ∈ dom extra then extra(col) else base(col)) }
 4277: 
 4278: =head2 base_criteria
 4279: 
 4280:     ─── BaseCriteria ────────────────────────────────────────────────
 4281:     base_criteria : CRITERIA              -- column-name keyed
 4282:     col_db        : NAME ⇸ ℕ
 4283:     filters       : ℕ ⇸ CRITERIA         -- after Init
 4284:     ─────────────────────────────────────────────────────────────────
 4285:     -- base_criteria is applied once at construction by partitioning
 4286:     -- its columns into per-db slices and merging into filters:
 4287:     ∀ i : 0 ‥ #dbs-1 •
 4288:         bc_slice(i) = partition(base_criteria, i)
 4289:         filters(i)  = merge_criteria(bc_slice(i), filters_explicit(i))
 4290: 
 4291:     -- where filters_explicit is the filters parameter as supplied.
 4292:     -- merge_criteria semantics: explicit filters win on plain-scalar
 4293:     -- conflicts; operator hashrefs are combined (AND).
 4294: 
 4295:     -- Unknown columns are dropped (warn_unknown_column carp);
 4296:     -- the join_column is broadcast to all databases.
 4297: 
 4298:     -- Key-set semantics: any database that receives a non-empty
 4299:     -- bc_slice acts as an inner-join partner (same as filters).
 4300: 
 4301: =head2 selectall_arrayref
 4302: 
 4303:     selectall_arrayref : CRITERIA → seq MERGED_ROW
 4304:     pre:  ∀ col : dom criteria • col ∈ dom self._col_db ∪ {self._join_col}
 4305:     post: result = _joined_query(criteria)
 4306:           result is sorted ascending by join_col value
 4307: 
 4308: =head2 selectall_array
 4309: 
 4310:     selectall_array : CRITERIA → seq MERGED_ROW | MERGED_ROW?
 4311:     pre:  same as selectall_arrayref
 4312:     post: wantarray  => result = @{ selectall_arrayref(criteria) }
 4313:           !wantarray => result = selectall_arrayref(criteria)[0]  (or undef)
 4314: 
 4315: =head2 fetchrow_hashref
 4316: 
 4317:     fetchrow_hashref : CRITERIA → MERGED_ROW?
 4318:     post: result = selectall_arrayref(criteria)[0]  (or undef if empty)
 4319: 
 4320: =head2 count
 4321: 
 4322:     count : CRITERIA → ℕ
 4323:     post: result = #selectall_arrayref(criteria)
 4324: 
 4325: =head2 columns
 4326: 
 4327:     columns : → seq NAME
 4328:     post: result = sort(
 4329:               (⋃ { i : 0 ‥ #dbs-1 • ran(dbs(i).columns) }
 4330:                \ dom removed_cols
 4331:                \ { local_jc(i) | i ∈ dom join_map ∧ local_jc(i) ≠ join_col })
 4332:           )
 4333: 
 4334: =head2 schema
 4335: 
 4336:     schema : → NAME ⇸ SCHEMA_INFO
 4337:     post: dom(result) = ran(columns())
 4338:           ∀ col : dom(result) •
 4339:               result(col) = (last database containing col).schema()(col)
 4340: 
 4341: =head2 updated
 4342: 
 4343:     updated : → ℕ
 4344:     post: result = max { i : 0 ‥ #dbs-1 • dbs(i).updated() }
 4345: 
 4346: =head2 remove_column
 4347: 
 4348:     remove_column : NAME → Database_Join
 4349:     pre:  col ≠ self._join_col
 4350:     post: self'._removed_cols = self._removed_cols ∪ {col}
 4351:           self'._col_db       = self._col_db \ {col}
 4352:           self'._col_cache    = undef
 4353:           self'._schema_cache = undef
 4354: 
 4355: =head2 AUTOLOAD
 4356: 
 4357:     AUTOLOAD : NAME × CRITERIA → VALUE | seq VALUE
 4358:     pre:  col ∈ dom self._col_db
 4359:           col does not begin with '_'
 4360:     post: let rows = _joined_query(criteria)
 4361:           wantarray  => result = { r : rows • r(col) }
 4362:           !wantarray => result = rows(0)(col)  (or undef if rows is empty)
 4363: 
 4364: =head2 backend
 4365: 
 4366:     ─── BackendDispatch ───────────────────────────────────────────────
 4367:     backend        : {array, sqlite, auto}
 4368:     max_array_rows : â„•
 4369:     ───────────────────────────────────────────────────────────────────
 4370: 
 4371:     -- row_count(db): cheaply count rows in a component database.
 4372:     -- Uses dbi_source() COUNT(*) SQL for SQLite-backed sources,
 4373:     -- or the DA's own count() method when defined in its own package.
 4374:     -- Returns ⊥ (bottom / unknown) when neither is available.
 4375:     row_count(db) ==
 4376:         if (db has dbi_source() returning a SQLite dbh)
 4377:         then SELECT COUNT(*) FROM source_table
 4378:         else if (defined &{ref(db) ^ "::count"})
 4379:         then db.count()
 4380:         else ⊥
 4381: 
 4382:     -- Combined row count across all component databases.
 4383:     -- If any database returns ⊥, total is ⊥ (cannot determine).
 4384:     total_count ==
 4385:         if ∀ i : 0 ‥ #dbs-1 • row_count(dbs i) ≠ ⊥
 4386:         then Σ { i : 0 ‥ #dbs-1 • row_count(dbs i) }
 4387:         else ⊥
 4388: 
 4389:     -- Dispatch rule for _joined_query:
 4390:     use_sqlite(C) ==
 4391:         backend = 'sqlite'
 4392:         ∨ (backend = 'auto' ∧ total_count ≠ ⊥ ∧ total_count > max_array_rows)
 4393: 
 4394:     _joined_query(C) ==
 4395:         if use_sqlite(C)
 4396:         then _sqlite_join(C)
 4397:         else _joined_query_array(C)
 4398: 
 4399:     -- Result identity invariant: both paths return identical rows.
 4400:     ∀ C : CRITERIA •
 4401:         _sqlite_join(C) = _joined_query_array(C)
 4402: 
 4403: =head1 STATE DIAGRAM
 4404: 
 4405: C<Database::Join> objects follow three independent finite state machines (FSMs).
 4406: Each FSM is described with an ASCII diagram showing valid states (boxes), the
 4407: triggers that cause transitions (arrows), and important side-effects.
 4408: 
 4409: =head2 FSM 1: Object Lifecycle
 4410: 
 4411: Governs the structural state of a C<Database::Join> instance.
 4412: Query methods (C<selectall_arrayref>, C<fetchrow_hashref>, C<count>,
 4413: C<columns>, C<schema>, C<updated>) are schema-preserving (Xi-transitions) and
 4414: are not shown because they do not change state.
 4415: 
 4416:     [pre-creation]
 4417:          |
 4418:          | new( databases => [...], join_column => '...' )
 4419:          |   Side-effect: _col_db routing table built;
 4420:          |                _autoload_pk cached from dbs[0]{id}
 4421:          v
 4422:     [CONSTRUCTED] <-----------------------------------------+
 4423:          |    |                                              |
 4424:          |    +--------------------------------------------+ |
 4425:          |    (query methods: no structural change)         | |
 4426:          |                                                  | |
 4427:          |-- remove_column( col ) -------> [COL_REMOVED] <--+ |
 4428:          |                                      |    |        |
 4429:          |   Side-effect: col removed from       |    |        |
 4430:          |   _col_db; _col_cache and             +----+        |
 4431:          |   _schema_cache cleared.              (idempotent;  |
 4432:          |   Join column cannot be removed.)      chainable)   |
 4433:          |                                                     |
 4434:          +-- add_database( db ) ----------> [DB_ADDED] <------+
 4435:                                                 |    |
 4436:              Side-effect: new columns added;    |    | add_database( db )
 4437:              _col_db extended; SQLite cache     |    | (chainable; each
 4438:              invalidated (if any).              +----+  extends the view)
 4439: 
 4440:     Note: COL_REMOVED and DB_ADDED are not mutually exclusive.
 4441:     Both transitions are legal on any valid object, in any order.
 4442: 
 4443:     Illegal triggers (always croak; object state is not changed):
 4444: 
 4445:       Trigger                              Error
 4446:       -----------------------------------  ---------------------------------
 4447:       new( databases => [] )               error_no_databases
 4448:       remove_column( join_column )         error_remove_join_column
 4449:       add_database( non-reference )        error_invalid_database
 4450:       new() with join_col absent from DB   error_join_col_absent
 4451: 
 4452: =head2 FSM 2: SQLite Cache Lifecycle
 4453: 
 4454: Governs the temporary SQLite cache used by the C<backend='sqlite'> and
 4455: C<backend='auto'> join paths.  The cache does not exist until the first
 4456: query on the SQLite path.
 4457: 
 4458:     [ABSENT] <----- add_database( db )
 4459:        |                  |
 4460:        |  (no temp file)  | Side-effect: old DBI handle disconnected;
 4461:        |                  |   _sqlite_cache deleted.
 4462:        |                  |
 4463:        |                  +<-------------------------------------------+
 4464:        |                                                               |
 4465:        | first query on SQLite path                                    |
 4466:        | Side-effect: File::Temp db created in tmpdir;                 |
 4467:        |   DBI connected; sources ATTACHed or spilled;                 |
 4468:        |   _sqlite_cache = { dbh, tmpfile, n, updated, ... }           |
 4469:        v                                                               |
 4470:     [FRESH] <--+                                                       |
 4471:        |        |                                                      |
 4472:        |        | subsequent queries                                   |
 4473:        |        | (cache reused; refaddr of _sqlite_cache unchanged)   |
 4474:        +--------+                                                      |
 4475:        |                                                               |
 4476:        | updated() timestamp of any source DA changes                  |
 4477:        | -- OR -- source row count changes                             |
 4478:        | Side-effect: none yet (_cache_fresh returns false)            |
 4479:        v                                                               |
 4480:     [STALE]                                                            |
 4481:        |                                                               |
 4482:        | next query                                                    |
 4483:        | Side-effect: old DBI handle disconnected; old temp file       |
 4484:        |   unlinked; new temp file built from current source data.     |
 4485:        +---------------------------------------------------------------+
 4486:        (transitions to FRESH)
 4487: 
 4488:     On object DESTROY:
 4489:       FRESH/STALE:  DBI handle disconnected; File::Temp object released
 4490:                     (temp file unlinked by File::Temp DESTROY).
 4491:       ABSENT:       No temp file exists; no-op.
 4492: 
 4493: =head2 FSM 3: Column Visibility (per column)
 4494: 
 4495: Each column in the logical view independently follows a two-state machine.
 4496: Transition from VISIBLE to REMOVED is one-way: C<add_database> never
 4497: restores a column that is in C<_removed_cols>.
 4498: 
 4499:     [VISIBLE] <-- initial state for every column at construction
 4500:          |    |
 4501:          |    | query / columns() / schema()
 4502:          |    | (column present in results; no state change)
 4503:          +----+
 4504:          |
 4505:          | remove_column( col )
 4506:          | Side-effect: col deleted from _col_db;
 4507:          |   _col_cache and _schema_cache cleared.
 4508:          v
 4509:     [REMOVED] <---+
 4510:          |         |
 4511:          |         | remove_column( col ) again
 4512:          |         | (idempotent; no error; no second side-effect)
 4513:          +---------+
 4514: 
 4515:     One-way invariant:
 4516:       If col is in _removed_cols, then add_database( db_that_has_col )
 4517:       does NOT re-add it.  Formal: col_db' = col_db ⊕ { c | c in
 4518:       ran(db.columns) \ {local_jc} \ removed }.
 4519: 
 4520:     Illegal trigger:
 4521:       remove_column( join_column )  -- error_remove_join_column (croaks;
 4522:                                        state unchanged)
 4523: 
 4524: =head1 AUTHOR
 4525: 
 4526: Nigel Horne, C<< <njh@nigelhorne.com> >>
 4527: 
 4528: =head1 LICENSE AND COPYRIGHT
 4529: 
 4530: Copyright (C) 2026 Nigel Horne.
 4531: 
 4532: Usage is subject to the GPL2 licence terms.
 4533: If you use it, please let me know.
 4534: 
 4535: =cut