File Coverage

=head1 NAME

Database::Join - Read-only combined view across two or more Database::Abstraction objects

=head1 VERSION

Version 0.008.2

=head1 SYNOPSIS

B<Basic two-database join>

    use Database::Join;

    # Step 1: create each component database the normal way
    my $customers = Database::Customers->new(directory => '/data');
    my $loyalty   = Database::Loyalty->new(directory  => '/data');

    # Step 2: combine them on the shared key column 'entry'
    my $join = Database::Join->new(
        databases   => [ $customers, $loyalty ],
        join_column => 'entry',
    );

    # Step 3: query exactly as you would a single Database::Abstraction object
    my $all_rows  = $join->selectall_arrayref();
    my $vip_rows  = $join->selectall_arrayref(tier => 'gold');
    my $one_row   = $join->fetchrow_hashref(entry => 'C001');
    my $total     = $join->count();
    my $col_names = $join->columns();

B<Hiding internal columns>

    my $join = Database::Join->new(
        databases      => [ $customers, $loyalty ],
        join_column    => 'entry',
        remove_columns => [ 'internal_id', 'audit_ts' ],
    );
    # 'internal_id' and 'audit_ts' never appear in results or columns()

B<join_map: when the key column has different names in each database>

    # $cities  (index 0) has a column called 'statecode' -- matches join_column
    # $stnames (index 1) has a column called 'entry'     -- different name

    my $join = Database::Join->new(
        databases   => [ $cities,  $stnames ],
        #                index 0   index 1
        join_column => 'statecode',
        join_map    => { 1 => 'entry' },  # index 1 calls its join key 'entry'
    );

    # All returned rows use 'statecode'; 'entry' is never exposed
    my $rows = $join->selectall_arrayref();

B<filters: permanently restrict a database's visible rows>

    # Only show orders placed more than 60 days ago, without repeating
    # the criterion on every query call.
    my $join = Database::Join->new(
        databases   => [ $customers, $orders ],
        join_column => 'entry',
        filters     => { 1 => { age_days => { '>' => 60 } } },
    );

    my $rows = $join->selectall_arrayref();                 # all old orders
    my $vip  = $join->selectall_arrayref(tier => 'gold');   # old + gold tier

B<Inner and outer join types>

    my $inner = Database::Join->new(
        databases   => [ $customers, $loyalty ],
        join_column => 'entry',
        join_type   => 'inner',   # only keys present in BOTH databases
    );

    my $outer = Database::Join->new(
        databases   => [ $customers, $loyalty ],
        join_column => 'entry',
        join_type   => 'outer',   # all keys from EITHER database
    );

B<Building the view incrementally with add_database>

    my $join = Database::Join->new(
        databases   => [ $customers ],
        join_column => 'entry',
    );

    $join->add_database($loyalty)
         ->add_database($scores, remove_columns => ['raw_score']);

B<AUTOLOAD column shortcut>

    # Returns the 'name' value for entry 'C001' (scalar context)
    my $name = $join->name(entry => 'C001');

    # Returns all 'tier' values (list context)
    my @tiers = $join->tier();

B<SQLite join backend for large datasets>

    # 'auto' (default): switches to SQLite automatically above the threshold
    my $join = Database::Join->new(
        databases      => [ $customers, $loyalty ],
        join_column    => 'entry',
        backend        => 'auto',          # default
        max_array_rows => 50_000,          # use SQLite when combined rows > 50,000
    );

    # Always use SQLite -- useful when you know the data is large
    my $join = Database::Join->new(
        databases   => [ $customers, $loyalty ],
        join_column => 'entry',
        backend     => 'sqlite',
        tmpdir      => '/fast/nvme/tmp',   # optional: faster temp disk
    );

    # Always use the original in-memory path
    my $join = Database::Join->new(
        databases   => [ $customers, $loyalty ],
        join_column => 'entry',
        backend     => 'array',
    );

=head1 DESCRIPTION

C<Database::Join> merges two or more L<Database::Abstraction> objects into a
single logical, read-only view.  Each component database is queried
independently through its own C<Database::Abstraction> interface.  The results
are combined using a shared key column (C<join_column>).
In effect, this means that you can view data from more than one database using
an intuitive, non-SQL interface.

Every storage format that C<Database::Abstraction> supports works as a
component database: CSV, PSV, TSV, SQLite, JSON, XML, XLSX, BerkeleyDB, HTML
URL, JSON URL, or any custom subclass.  Component databases may mix formats
within the same join.

The module exposes the same read-only API as C<Database::Abstraction>:
C<selectall_arrayref>, C<selectall_array>, C<fetchrow_hashref>, C<count>,
C<columns>, C<schema>, C<updated>, C<set_logger>, and the AUTOLOAD column
shortcut.  Callers do not need to know how many underlying databases are
involved.

Think of it as a virtual database table that is assembled on demand from
several real tables, one per component database.

B<Join backends>

By default (C<backend =E<gt> 'auto'>), C<Database::Join> first checks the
combined source row count.  For small datasets (up to C<max_array_rows>,
default 10,000 rows) it merges entirely in Perl memory.  For larger datasets it
automatically spills source rows into a temporary SQLite database and executes
a single SQL JOIN there, keeping peak RAM to roughly one times the source data
size instead of three.  You can also force either path unconditionally with
C<backend =E<gt> 'sqlite'> or C<backend =E<gt> 'array'>.

=head2 Join semantics

The C<join_type> parameter controls what happens when a particular key value
exists in some component databases but not all:

=over 4

=item C<left> (the default)

All rows from the I<primary> (first) database are returned.  Columns from
subsequent databases are included where a matching row is found, and simply
absent from the hashref where there is no match.  If you are familiar with
SQL, this is a LEFT OUTER JOIN on the first table.

=item C<inner>

Only rows whose join-column value is present in I<every> component database
are returned.  This is equivalent to a SQL INNER JOIN.

=item C<outer>

Every join-column value found in I<any> component database is returned.
Columns from databases that do not have that key value are absent from the
merged row.  This is a FULL OUTER JOIN.

=back

B<Important override rule:> whenever you pass a query criterion for a column
that belongs to a secondary database, that database automatically acts as an
inner-join partner for that query only -- regardless of C<join_type>.  This
gives WHERE-clause semantics.  For example, if you have a LEFT join but query
C<< tier => 'gold' >> on a secondary database, only rows whose secondary entry
has tier = 'gold' are returned (rows with no secondary entry are excluded, just
as a WHERE clause would exclude them).

=head2 Column ownership and routing

At construction time, C<Database::Join> calls C<columns()> on each component
database and builds an internal index that maps every column name to the
database that owns it.

When you pass criteria to a query method, each key-value pair is automatically
routed to the right database.  You never need to say which database a column
belongs to.

The C<join_column> is special: criteria on it are broadcast to I<all>
databases so that each database fetches only the relevant rows before the
in-memory merge.

When the same non-join column name exists in more than one database, the
I<last> database in the C<databases> array wins by default: its value
overwrites earlier ones in merged rows.  Use C<collision_prefix> to
preserve both values under distinct names instead.

=head1 LIMITATIONS

=over 4

=item Memory usage (array backend)

When C<backend> is C<'array'> (or C<'auto'> and the dataset is small), all
matching rows are fetched into Perl memory.  Peak RAM is roughly three times
the source data size.  For large datasets use C<backend =E<gt> 'sqlite'>, or
leave C<backend =E<gt> 'auto'> and set C<max_array_rows> appropriately.

=item SQLite backend uses a persistent cache file

When the SQLite path is active, a single C<.db> file with a randomly generated
name (chosen by C<File::Temp> to avoid collisions) is created in C<tmpdir> the
first time a query runs on a given C<Database::Join> object.  Source data is
spilled into that file once; subsequent queries against the same object reuse
the file without re-fetching the source data.

The cache is automatically invalidated and rebuilt whenever any source
database's C<updated()> timestamp changes (indicating new data), or when
C<add_database()> is called.

The file is deleted when the C<Database::Join> object is destroyed (typically
when it goes out of scope).  At any given moment no more than one such file
exists per object.  The directory must be writable and have enough free space
for the full source data (once, not per-query).

=item No chained builder or raw SQL

C<query()> and C<execute()> are not implemented.  Use C<selectall_arrayref>
or C<fetchrow_hashref> instead.

=item Single-column equi-join only

Joining on more than one column simultaneously, or on expressions, is not
supported.  When the join key has different names in different databases,
use C<join_map> to declare each database's local column name.

=item Sort order

Results are sorted ascending by C<join_column> by default.  Pass
C<< sort_by => 'colname' >> (or C<< sort_by => ['colname', 'DESC'] >>)
to any query method to override this.  The array path uses string comparison
(C<cmp>); for accurate numeric ordering on large datasets use the SQLite
backend, which sorts natively by type.

=item count() on the array backend fetches all rows

On the array backend, C<count()> executes the full in-memory join and counts
the resulting rows in Perl; no C<COUNT(*)> is pushed to the component
databases.  On the SQLite backend, C<count()> executes a C<SELECT COUNT(*)>
SQL query against the cached join tables, avoiding a full row transfer.

=back

=head1 COMMON PITFALLS

=over 4

=item The join_column must exist in every component database

If even one database is missing the join key column, C<new()> (or
C<add_database()>) will C<croak> immediately.  Use C<join_map> when the
column has a different local name in some databases.

=item Criteria on a removed column are silently dropped

If you call C<remove_column('tier')> and later query
C<< selectall_arrayref(tier => 'gold') >>, the criterion is ignored (with a
C<carp> warning) and all rows are returned.  Always pass criteria before
removing columns, or restructure your code to avoid this.

=item You cannot remove the join column

C<< $join->remove_column($join->join_column) >> will C<croak>.  The join key
is required for the merge to work.

=item Left join does not guarantee all columns are populated

Under a LEFT join, rows from the primary database that have no matching row
in a secondary database will be returned with I<no keys> from that secondary
database.  Accessing C<< $row->{score} >> on such a row returns C<undef> --
not zero, not an empty string.  Always test C<defined $row->{score}> rather
than just C<$row->{score}> when the secondary match is optional.

=item Filters act as inner-join partners

Any database that has a C<filters> entry is promoted to an inner-join partner,
regardless of C<join_type>.  A row whose join-key value does not appear in
the filtered database's result is removed from the merged output entirely, not
merely missing its secondary columns.  This is intentional but can be
surprising if you expected LEFT join semantics.

=item Criteria-merging replaces scalar filters

When both a base filter and a query criterion target the same column, and both
are operator hashrefs (e.g. C<< { '>' => 60 } >>), the operators are combined
(AND semantics).  But if the query criterion is a plain scalar (e.g.
C<< score => 75 >>), it I<replaces> the base filter for that column entirely --
the base filter is ignored for that query.

=item AUTOLOAD sees the full merged join when filters or join_map are active

When either C<filters> or C<join_map> is in effect, the AUTOLOAD shortcut
(C<< $join->columnname(...) >>) runs the full join query rather than
delegating directly to the owning database.  This is necessary for correctness
but means the result respects all active filters and join-key translations,
which may differ from what the owning database would return on its own.

=item Duplicate column names: last database wins (unless collision_prefix is set)

When two component databases each have a column called C<notes>, the second
database's value silently overwrites the first in every merged row.  Use
C<collision_prefix => { 1 => 'right' }> to publish the second database's
C<notes> as C<right.notes> so both values survive, or use C<remove_columns>
(or C<remove_column>) to drop the unwanted duplicate entirely.

=item Mutating the filters hashref after construction has no effect

C<Database::Join> deep-copies the C<filters> hashref (and any C<filter>
passed to C<add_database>) at the moment of construction.  The original hashref
you passed in is never stored.  If you later modify it -- for example, to
tighten or loosen a filter criterion -- the joined view is I<not> affected.
Construct a new C<Database::Join> object, or use a component
C<Database::Abstraction> that supports dynamic filter modification.

=item auto mode may always use the array path for some DAs

C<backend =E<gt> 'auto'> counts rows cheaply only when each component database
either implements C<dbi_source()> (SQLite-backed) or directly defines a
C<count()> method in its own package.  A DA that merely I<inherits> C<count()>
from C<Database::Abstraction> is treated as uncountable, because the parent
class C<count()> expects a key argument and behaves differently from a
"return total row count" function.  In that case C<Database::Join>
conservatively uses the array path for the whole query, even if the dataset is
large.  To opt in to the SQLite path for such a DA, either add your own
C<count()> override that returns the total row count, implement C<dbi_source()>,
or use C<backend =E<gt> 'sqlite'> unconditionally.

=item dbi_source() ATTACH is unconditional - query-time criteria go into WHERE

When a component database implements C<dbi_source()>, C<Database::Join> always
uses the zero-copy ATTACH path, I<even when the current query includes criteria
for columns in that database>.  The criteria are translated into parameterised
SQL C<WHERE> clauses applied against the ATTACHed table; no row-level copy is
performed.  (Prior to 0.005.0 the presence of any query-time criteria would
force a spill; that restriction has been removed.)

=item Broadcast join-column criterion does not force secondaries into inner-join

When a caller passes a join-column criterion (e.g. C<entry =E<gt> 'k1'>),
C<Database::Join> broadcasts it to all component databases so each DA can
filter its fetch to the requested key.  Prior to 0.006.0 this broadcast was
incorrectly counted as "having criteria" for secondary databases, causing
C<left> and C<outer> joins to silently behave as C<inner> joins when a
join-column criterion was present.  The fix: only non-join-column criteria
(e.g. column filters from the caller or base C<filters =E<gt> {...}>) promote
a secondary to inner-join status.  The broadcast itself is now a transparent
key-range selector that does not affect join semantics.

=item LIKE and NOT LIKE work on the SQLite path; other pattern operators do not yet

C<LIKE>, C<NOT LIKE>, C<IN>, and C<NOT IN> are fully supported on the SQLite
backend.  C<LIKE>/C<NOT LIKE> take a scalar pattern; C<IN>/C<NOT IN> take an
arrayref of values.  All are injection-safe because values are passed as bind
parameters, never interpolated.

    # LIKE
    my $rows = $join->selectall_arrayref(name => { LIKE => 'A%' });

    # IN
    my $rows = $join->selectall_arrayref(tier => { IN => ['gold', 'silver'] });

    # NOT IN
    my $rows = $join->selectall_arrayref(tier => { 'NOT IN' => ['bronze'] });

C<IN> with an empty arrayref matches no rows (SQL semantics: C<IN ()> is
always false).  C<NOT IN> with an empty arrayref matches all rows (no
constraint added).

C<IS NULL> and C<IS NOT NULL> are supported on the SQLite backend using an
explicit operator hashref:

    my $rows = $join->selectall_arrayref(score => { 'IS NULL'     => undef });
    my $rows = $join->selectall_arrayref(score => { 'IS NOT NULL' => 1    });

The hashref value is ignored; only the key selects the operator.  A bare
C<undef> criterion value (C<< score => undef >>) also generates C<IS NULL>
on the SQLite path.  Note: on the in-memory array path, the component DA
may treat an C<undef> criterion value as C<< no filter >> rather than
C<IS NULL>, so use the explicit hashref form for consistent behaviour
across backends.

Any operator not in the supported set is skipped on the SQLite path with a
C<carp> warning.  The array path forwards all operators to the component DA
unchanged, which may or may not honour them.

=item Temp file directory must be writable and have free space

The SQLite path creates one temporary C<.db> file per C<Database::Join> object
in C<tmpdir> (default: C<File::Spec-E<gt>tmpdir()>, usually C</tmp> on Unix).
The filename is randomly generated by C<File::Temp> -- you cannot predict it,
only the directory is under your control.  The file is created on the first
query and kept alive until the object is destroyed; it is not re-created on
every query call.  If the directory is not writable, or the filesystem is full,
the call will C<croak> with C<error_sqlite_connect>.  Check permissions and
free space if you see that error.

=item C<join =E<gt>> criteria are not supported

C<Database::Abstraction> accepts a C<join =E<gt> { table =E<gt> ..., on =E<gt> ... }>
key in its criteria hashrefs to express an SQL JOIN within a single table.
C<Database::Join> cannot route this to a component DA meaningfully: the merged
view has no concept of a single underlying table.  Passing C<join =E<gt>> to any
query method will C<croak> with C<error_join_criterion>.

B<Fix:> Model the joined table as a separate C<Database::Abstraction> object and
add it to the C<databases =E<gt> []> list.  Use C<join_column> or C<join_map> to
specify the shared key.

=back

=head1 METHODS

=head2 new

=head3 SYNOPSIS

    my $join = Database::Join->new(
        databases        => [ $db1, $db2 ],
        join_column      => 'entry',
        join_type        => 'left',
        join_map         => { 1 => 'local_col' },
        filters          => { 1 => { score => { '>' => 60 } } },
        collision_prefix => { 1 => 'right' },
        remove_columns   => [ 'email', 'internal_id' ],
        backend          => 'auto',        # 'auto' | 'sqlite' | 'array'
        max_array_rows   => 10_000,        # threshold for 'auto' mode
        tmpdir           => '/tmp',        # directory for temp SQLite file
        logger           => $log,
        i18n             => $locale,
    );

=head3 DESCRIPTION

Constructs and returns a new C<Database::Join> object.

Each element of C<databases> must be an already-instantiated subclass of
C<Database::Abstraction>.  The constructor calls C<columns()> on every
database to build an internal column-routing table and verifies that
C<join_column> (or its local alias from C<join_map>) is present in each one.

Columns listed in C<remove_columns> are hidden immediately: they do not appear
in C<columns()>, C<schema()>, or any returned row hashref.  This is equivalent
to calling C<remove_column> once per name after construction.

=head3 API SPECIFICATION

=head4 Input

    databases      => { type => 'arrayref', required => 1 }
                      # One or more Database::Abstraction subclass objects.
                      #
                      # DOMAIN -- EP valid:   non-empty arrayref of blessed DA subclasses.
                      # DOMAIN -- EP invalid: scalar, hashref, or absent => croak.
                      # DOMAIN -- BVA size:   minimum 1 element; no documented upper bound.
                      # DOMAIN -- BVA elem:   each element must pass isa('Database::Abstraction').

    join_column    => { type => 'string',   optional => 1, default => 'entry' }
                      # The column name shared by all databases (the join key).
                      #
                      # DOMAIN -- EP valid:   any non-empty string present in every component DA.
                      # DOMAIN -- EP invalid: column absent from any DA => croak join_col_missing.
                      # DOMAIN -- BVA:        empty string '' is treated as a column name and
                      #                       will croak if (as expected) it is absent from every DA.
                      # DOMAIN -- NOTE:       matching is case-sensitive and exact.

    join_type      => { type => 'string',   optional => 1, default => 'left',
                        enum => ['inner', 'left', 'outer'] }
                      # Controls which keys appear in the result when not all
                      # databases share the same key values.
                      #
                      # DOMAIN -- EP valid:   exactly 'inner', 'left', or 'outer'.
                      # DOMAIN -- EP invalid: any other string including 'INNER', 'LEFT',
                      #                       'OUTER' (enum check is case-sensitive), 'cross',
                      #                       or '' => croak from validate_strict.

    join_map       => { type => 'hashref',  optional => 1 }
                      # Zero-based database index => local column name.
                      # See the join_map section for full details.
                      #
                      # DOMAIN -- EP valid:   hashref values must be plain strings.
                      # DOMAIN -- EP invalid: reference value (hashref, arrayref, coderef, etc.)
                      #                       => croak; the guard prevents heap-address leakage.
                      # DOMAIN -- BVA:        out-of-range keys (beyond the databases array) are
                      #                       silently ignored.

    filters        => { type => 'hashref',  optional => 1 }
                      # Zero-based database index => criteria hashref.
                      # Permanent row restrictions on individual databases.
                      # See the filters section for full details.

    base_criteria  => { type => 'hashref',  optional => 1 }
                      # Column name => value hashref.
                      # Permanent criteria applied to every query, specified by
                      # column name rather than database index.  Each key must
                      # be a column that appears in the merged view (or the
                      # join_column); each value is a plain scalar or an
                      # operator hashref in the same format as
                      # selectall_arrayref accepts.  Criteria are automatically
                      # routed to the database that owns each column (same
                      # routing used by selectall_arrayref).
                      # Equivalent to filters but more convenient when you know
                      # the column name but not which database index owns it.
                      # When a column appears in both base_criteria and filters,
                      # the filters entry takes precedence.
                      # See the base_criteria section for full details.
                      #
                      # DOMAIN -- EP valid:   hashref of column-name => scalar or
                      #                       operator-hashref pairs.  Unknown column
                      #                       names emit warn_unknown_column (carp)
                      #                       and are silently dropped.
                      # DOMAIN -- EP invalid: non-hashref value => croak from
                      #                       validate_strict.
                      # DOMAIN -- BVA:        {} empty hashref is a safe no-op.

    collision_prefix => { type => 'hashref', optional => 1 }
                      # Zero-based database index (>0) => prefix string.
                      # When a secondary database has a column that collides
                      # with a column already present in the merged view, the
                      # secondary column is published as "$prefix.$col" instead
                      # of silently overwriting the earlier value.
                      # Index 0 entries are silently ignored.
                      # Omitting this parameter preserves the original
                      # last-database-wins behaviour.
                      # See the collision_prefix section for full details.
                      #
                      # DOMAIN -- EP valid:   absent or {} => last-database-wins (no change).
                      # DOMAIN -- EP valid:   { N => 'prefix' } where N > 0 => colliding
                      #                       columns from DB[N] published as "$prefix.$col";
                      #                       non-colliding columns from the same DB added plain.
                      # DOMAIN -- EP note:    index-0 entries are silently ignored.
                      # DOMAIN -- Invariant:  join_column is never prefixed regardless of
                      #                       collision_prefix configuration.

    remove_columns => { type => 'arrayref', optional => 1 }
                      # Column names to hide from the merged view.
                      #
                      # DOMAIN -- EP valid:   arrayref of any strings; non-existent columns
                      #                       are silently ignored (idempotent).
                      # DOMAIN -- EP invalid: join_column itself => croak remove_join_col.
                      # DOMAIN -- BVA:        [] empty arrayref is a safe no-op.

    backend        => { type => 'string',   optional => 1, default => 'auto',
                        enum => ['array', 'sqlite', 'auto'] }
                      # Controls which join strategy is used.
                      #   'auto'   -- (default) use 'array' when combined source row count
                      #              <= max_array_rows, 'sqlite' otherwise.
                      #   'sqlite' -- always spill to a temporary SQLite database.
                      #   'array'  -- always use the in-memory merge path.
                      #
                      # DOMAIN -- EP valid:   'array', 'sqlite', or 'auto' (case-sensitive).
                      # DOMAIN -- EP invalid: any other string => croak error_invalid_backend.
                      # DOMAIN -- Default:    'auto'.

    max_array_rows => { type => 'integer',  optional => 1, default => 10_000 }
                      # Row-count threshold for 'auto' mode.  When the combined
                      # source row count exceeds this value, the SQLite path is used.
                      # Ignored when backend is 'array' or 'sqlite'.
                      #
                      # DOMAIN -- EP valid:   any non-negative integer.
                      # DOMAIN -- BVA:        0 means always use SQLite (all counts exceed 0).
                      # DOMAIN -- Default:    10,000.

    tmpdir         => { type => 'string',   optional => 1 }
                      # Directory for the per-call temporary SQLite database file.
                      # The file is created securely by File::Temp and removed when
                      # the query completes.  Ignored when backend is 'array'.
                      #
                      # DOMAIN -- EP valid:   any writable directory path string.
                      # DOMAIN -- EP absent:  uses File::Spec->tmpdir() (system temp dir).

    parallel       => { type => 'integer',  optional => 1, default => 0 }
                      # When set to 1 and the join has more than 2 databases (primary +
                      # 2 or more secondaries), secondary DA fetches are issued in
                      # parallel Perl threads.  Requires the 'threads' module; falls
                      # back to sequential with a carp warning when unavailable.
                      # Has no effect on the SQLite backend (which uses a single SQL
                      # JOIN).  DBI-backed DAs are not thread-safe by default; only
                      # enable this for in-memory or otherwise thread-safe DA backends.
                      #
                      # DOMAIN -- EP valid:   0 (sequential, default) or 1 (parallel).
                      # DOMAIN -- EP invalid: any other integer is treated as truthy/falsy.
                      # DOMAIN -- Default:    0.

    logger         => { type => 'object',   optional => 1 }
                      # Logger object propagated to all component databases.

    i18n           => { type => 'object',   optional => 1 }
                      # Localisation object with a translate($key, @args) method.

=head4 Output

    A blessed Database::Join object.

=head3 EXAMPLE

    # Customers database: entry | name | email
    # Loyalty   database: entry | tier | points

    my $join = Database::Join->new(
        databases      => [ $customers, $loyalty ],
        join_column    => 'entry',
        join_type      => 'inner',            # only customers who also have loyalty records
        remove_columns => [ 'email' ],        # hide PII from query results
        filters        => { 1 => { points => { '>' => 0 } } }, # ignore zero-point records
    );

    my $rows = $join->selectall_arrayref();
    # Each row: { entry => ..., name => ..., tier => ..., points => ... }
    # 'email' is absent. Zero-point loyalty records are excluded.

=head3 PSEUDOCODE

    validate all parameters with validate_strict
    croak if databases is empty
    croak if any element of databases is not a Database::Abstraction subclass
    bless the object with all fields initialised
    call _build_col_index to map every column to its owning database
        and verify join_column presence in each database
    if base_criteria given:
        partition base_criteria by column ownership into per-db slices
        for each database with a non-empty slice:
            merge the slice into _filters[i]
            (explicit filters win on plain-scalar conflicts)
    for each column in remove_columns: call remove_column
    return the new object

=head3 MESSAGES

    error_no_databases        -- databases arrayref was empty
    error_invalid_db          -- an element of databases is not a D::A subclass
    error_join_col_missing    -- join_column (or its join_map alias) not found in a database
    error_invalid_backend     -- backend value is not 'array', 'sqlite', or 'auto'
    error_sqlite_connect      -- temporary SQLite database could not be created (backend='sqlite'/'auto')
    warn_schema_type_mismatch -- (carp) a shared column has different types across databases;
                                 use collision_prefix to preserve both values

=cut

=head2 join_map - joining on differently-named columns

By default every component database must have a column whose name matches
C<join_column>.  If a database uses a different local name for the join key,
declare the mapping with C<join_map>.

C<join_map> is a hashref.  Each B<key> is the B<zero-based position> of a
database in the C<databases> array (0 = first, 1 = second, and so on).  Each
B<value> is the name that B<that particular database> uses for the join key.

Databases not listed in C<join_map> are assumed to already have a column
named C<join_column> and need no entry.

Throughout the merged view the join key is I<always> referred to by the name
given in C<join_column>.  The local alias is never exposed in returned rows,
in C<columns()>, or in C<schema()>.

B<When do you need join_map?>

You need C<join_map> when you have two tables like:

    cities table  : entry (the city name) | statecode
    stnames table : entry (the state code) | state

Here you want to join cities.statecode to stnames.entry.  You choose
C<< join_column => 'statecode' >> as the canonical name, but stnames calls
that same concept C<entry>, so you declare:

    join_map => { 1 => 'entry' }  # stnames (index 1) calls it 'entry'

B<Example>

    #                        index 0     index 1
    my @databases = (       $cities,    $stnames  );
    #  join key column:    'statecode'  'entry'
    #  join_column:        'statecode' (chosen canonical name)
    #  stnames differs, so declare the alias:

    my $join = Database::Join->new(
        databases   => \@databases,
        join_column => 'statecode',
        join_map    => { 1 => 'entry' },
    );

    my $rows = $join->selectall_arrayref();
    # Each $row has keys: entry (city), statecode, state
    # 'entry' from stnames is never exposed directly.

    my $row = $join->fetchrow_hashref(statecode => 'CA');

B<Using add_database instead>

If you build the join incrementally with C<add_database>, pass
C<join_column> directly to that call instead of using C<join_map>:

    my $join = Database::Join->new(
        databases   => [ $cities ],
        join_column => 'statecode',
    );
    $join->add_database($stnames, join_column => 'entry');

This is exactly equivalent to the C<join_map> form above.

=head2 filters - permanent per-database row filters

C<filters> lets you restrict a component database to a subset of its rows
permanently, without repeating the criterion on every query call.

Think of it as telling the join: "whenever you query this database, always
add these extra conditions".  Callers never need to specify the restriction
themselves and can never accidentally omit it.

C<filters> is a hashref.  Each B<key> is the B<zero-based position> of a
database in the C<databases> array (same numbering as C<join_map>).  Each
B<value> is a criteria hashref in the same format as C<selectall_arrayref>
accepts.

B<Key-set semantics>

A filtered database always acts as an inner-join partner, regardless of the
C<join_type> setting.  Any join-key value that does not pass the filter is
excluded from the merged output entirely -- not just missing its secondary
columns.  This ensures the filter genuinely restricts the view rather than
simply hiding a few fields.

B<Criteria merging>

When a query call also passes a criterion for a column that already has a base
filter, the two constraints are combined:

=over 4

=item *

When both the base filter value and the query criterion are operator hashrefs
(e.g. C<< { '>' => 60 } >> and C<< { '<' => 365 } >>), their operators are
merged: I<both> constraints apply simultaneously (AND semantics).

=item *

When either value is a plain scalar, or the operators conflict, the
query-time criterion wins and the base filter for that column is ignored for
that one call.

=back

B<Example -- only show orders placed more than 60 days ago>

    my $join = Database::Join->new(
        databases   => [ $customers, $orders ],
        join_column => 'entry',
        filters     => { 1 => { age_days => { '>' => 60 } } },
    );

    # Every query automatically sees only old orders
    my $rows = $join->selectall_arrayref();

    # Additional criteria layer on top -- gold tier AND old order
    my $vip  = $join->selectall_arrayref(tier => 'gold');

    # Range intersection: age_days > 60 AND age_days < 365
    my $mid  = $join->selectall_arrayref(age_days => { '<' => 365 });

When using C<add_database>, pass C<filter> (singular) to set the base
criteria for the new database:

    $join->add_database($orders, filter => { age_days => { '>' => 60 } });

=head2 base_criteria - permanent view-level row filters by column name

C<base_criteria> is a convenience alternative to C<filters> for callers who
know the column names they want to restrict but prefer not to track database
indices.

    my $join = Database::Join->new(
        databases     => [ $customers, $orders ],
        join_column   => 'entry',
        base_criteria => { active => 1, deleted_at => undef },
    );

    # Every query automatically sees only active, non-deleted rows.
    my $rows = $join->selectall_arrayref();

At construction time C<base_criteria> is partitioned by column ownership
using the same routing logic as C<selectall_arrayref>.  Each criterion is
sent to the database that owns that column and stored as a permanent base
filter (equivalent to the corresponding C<filters> entry).

B<Unknown columns> emit a C<warn_unknown_column> carp and are silently
dropped, just as they would be in a query call.

B<Key-set semantics> are identical to C<filters>: any database that receives
a C<base_criteria> slice acts as an inner-join partner regardless of
C<join_type>.

B<Precedence>: when a column appears in both C<base_criteria> and C<filters>,
the C<filters> entry wins on plain-scalar conflicts; operator-hashref values
are combined with AND semantics.

B<Use cases>

=over 4

=item *

Row-level security: C<< base_criteria => { tenant_id => $tid } >>

=item *

Soft-delete filtering: C<< base_criteria => { deleted_at => undef } >>

=item *

Status gates: C<< base_criteria => { active => 1 } >>

=back

=head2 collision_prefix - preserve colliding columns from secondary databases

By default, when a column name appears in more than one database the I<last>
database wins: its value silently overwrites earlier ones in merged rows.
This loses data and makes the origin invisible.

C<collision_prefix> changes this for secondary databases you designate.
When a secondary database at index N has a column that already exists in the
merged view, and C<collision_prefix-E<gt>{N}> is set, the colliding column is
published as C<"$prefix.$col"> instead of overwriting.  Both values are then
visible: the original column keeps its name (from the earlier database), and
the collision gets the prefixed name.

C<collision_prefix> is a hashref.  Each B<key> is the B<zero-based index> of
a secondary database in the C<databases> array (same numbering as C<join_map>).
Each B<value> is the prefix string to prepend.  An index-0 entry is
meaningless and silently ignored.  Omitting C<collision_prefix> entirely
preserves the previous last-wins behaviour and changes nothing.

Non-colliding columns from a secondary database are always added as-is with
no prefix, whether or not C<collision_prefix> is configured.

B<Example -- sales table and products table, both with a "product" column>

    # $sales    columns: id, product, amount, date
    # $products columns: sku, product, price, category
    # join on 'product' (left key) matched against 'sku' (right key via join_map)

    my $join = Database::Join->new(
        databases        => [$sales, $products],
        join_column      => 'product',
        join_map         => { 1 => 'sku' },
        collision_prefix => { 1 => 'products' },
    );

    $join->columns;
    # => ['amount', 'category', 'date', 'id', 'price', 'product', 'products.product']
    #                                                               ^-- prefixed collision

    my $row = $join->fetchrow_hashref(product => 'widget');
    # $row->{product}            -- value from $sales
    # $row->{'products.product'} -- value from $products (different row, same column name)
    # $row->{price}              -- from $products, no collision, kept as-is

B<Querying on a prefixed column>

Use the full published name as the criterion key:

    my $rows = $join->selectall_arrayref('products.product' => 'widget');
    # Internally routes as: product => 'widget' to $products

B<Interaction with remove_column>

C<remove_column> operates on published names.  To suppress a prefixed
collision column entirely, pass the prefixed name:

    $join->remove_column('products.product');

=head2 backend - SQLite join backend for large datasets

C<Database::Join> can merge component databases in two different ways,
controlled by the C<backend> constructor parameter.

=over 4

=item C<backend =E<gt> 'array'> -- in-memory merge (original behaviour)

All matching rows are fetched from every component database into Perl hashes
and merged there.  Simple and fast for small and medium datasets.  Peak RAM
is roughly three times the combined source data size (one copy per database
plus one merged copy).

=item C<backend =E<gt> 'sqlite'> -- SQL JOIN via a cached temporary file

C<Database::Join> creates a temporary SQLite database file, spills source
rows into it (one table per component database), then executes a single SQL
C<JOIN> statement per query call.  Peak RAM drops to roughly one times the
source data size.

The temporary file is created once and reused across multiple query calls on
the same object (the cache).  Only query-time criteria vary per call; they
are applied as SQL C<WHERE> clauses against the cached data.  The cache is
automatically rebuilt when any source database's C<updated()> timestamp
changes.  The file is deleted when the object is destroyed (goes out of
scope).  See I<Temporary file: name, location, and lifetime> below for
details.

Requires C<DBD::SQLite E<gt>= 1.70> (C<FULL OUTER JOIN> support was added in
SQLite 3.39.0; DBD::SQLite 1.70 ships SQLite 3.39.2).

=item C<backend =E<gt> 'auto'> (default)

C<Database::Join> counts the total rows from all component databases cheaply
-- without fetching them -- and then decides:

=over 4

=item *

If the combined count is less than or equal to C<max_array_rows> (default
10,000), use the array path.

=item *

If the combined count exceeds C<max_array_rows>, use the SQLite path.

=back

For counting to work without fetching, each component database must either
implement the C<dbi_source()> interface (for SQLite-backed sources, where a
C<COUNT(*)> SQL query is issued directly), or directly define a C<count()>
method in its own package -- not just inherit one from a parent class.  If
neither is available for a particular database, C<Database::Join> plays it
safe and uses the array path for the whole query without fetching any rows.

=back

B<Choosing max_array_rows>

The default of 10,000 is a reasonable starting point.  Adjust it to match
your hardware and typical row width.  For wide rows (many columns or long
strings) you may want a lower threshold; for narrow rows you can raise it.

B<Temporary file: name, location, and lifetime>

When the SQLite path is active, a single temporary SQLite database file acts
as the join cache for the life of the C<Database::Join> object.

B<Name>: the filename is randomly generated by C<File::Temp>, for example:

    /tmp/Cj8xK7mP2Q.db

The random portion (ten characters) is chosen automatically to avoid
collisions.  Only the directory is under your control; you cannot specify
the filename itself.

B<Location>: controlled by the C<tmpdir> constructor parameter.
If C<tmpdir> is not specified, C<File::Spec-E<gt>tmpdir()> is used (usually
C</tmp> on Unix, or the value of the C<TEMP> or C<TMP> environment variable
on Windows).

B<Lifetime: one file per object, deleted when the object is destroyed>: the
file is created on the first query call that uses the SQLite path and kept
alive until the C<Database::Join> object is destroyed (i.e. when it goes out
of scope or is explicitly C<undef>-d).  At most one file exists per object at
any given moment.  Calling C<selectall_arrayref()> ten times on the same
object creates and uses I<one> file, not ten.

B<Cache invalidation>: the cache is automatically rebuilt (the old file is
replaced with a new one) when any source database's C<updated()> return value
changes, or when C<add_database()> is called.  Base-filter criteria
(C<filters> constructor parameter) are applied once at build time for spilled
sources; query-time criteria are applied per-call as SQL C<WHERE> clauses.

To use a different directory -- for example a RAM-backed filesystem or a
faster local disk:

    my $join = Database::Join->new(
        databases      => [ $db1, $db2 ],
        join_column    => 'entry',
        backend        => 'sqlite',
        tmpdir         => '/dev/shm',     # Linux RAM disk
    );

B<Parallel secondary fetches (C<parallel> constructor parameter)>

By default, component databases are queried sequentially - the primary first,
then each secondary in order.  When the component databases are network- or
disk-backed and have non-trivial per-query latency, the sequential fetch means
total latency is the I<sum> of all per-DA latencies.

Setting C<< parallel => 1 >> in the constructor enables concurrent fetching
of secondary databases using Perl C<threads>.  With C<parallel => 1> and
two or more secondary databases (C<n E<gt> 2> total), secondary fetches run in
parallel after the primary fetch completes; total latency drops to
I<max(secondary latencies)> instead of I<sum(secondary latencies)>.

    my $join = Database::Join->new(
        databases   => [ $customers, $loyalty, $scores ],   # 3 DAs - 2 secondaries
        join_column => 'entry',
        parallel    => 1,    # loyalty and scores fetched concurrently
    );

Requirements and caveats:

=over 4

=item *

The C<threads> module must be available.  Most distributions ship it, but it
requires a Perl binary compiled with C<-Dusethreads>.  When threads are
unavailable, a C<carp> warning is emitted and fetching falls back to
sequential; the result is identical, only slower.

=item *

Parallel fetching is only active when the join has three or more total
databases (C<n E<gt> 2>).  With two databases (one secondary), the thread
creation overhead exceeds the benefit of concurrency; sequential is used
regardless of C<parallel>.

=item *

Component databases must be safe to call from Perl threads.  In-memory
databases (CSV, JSON, TSV after slurp) are safe.  DBI-backed databases
whose handles were created in the same thread may not be safe - consult your
DBD driver's thread documentation.  The array backend is recommended for
DBI-backed sources; the SQLite backend performs its join in a single SQL
statement and does not use parallel fetching.

=item *

C<parallel => 1> applies to the array backend only.  The SQLite backend
performs a single SQL JOIN after spilling source data, so per-DA parallelism
is irrelevant.

=back

B<Zero-copy ATTACH (C<dbi_source()> interface)>

Normally, when the SQLite path is active, rows from each component database
are fetched one by one and inserted into the temporary SQLite file.  This is
efficient but does involve INSERT overhead.

If a component database is itself SQLite-backed and implements a
C<dbi_source()> method, C<Database::Join> can skip the row-by-row copy
entirely and instead use C<ATTACH DATABASE> to link the source file directly
to the temporary join connection.  This is the zero-copy path and is
significantly faster for large SQLite sources.

The C<dbi_source()> method must return a hashref with two keys:

=over 4

=item C<dbh>

A connected C<DBD::SQLite> database handle (C<DBI> connection object).

=item C<table>

The name of the table in that database that holds the source rows.

=back

Example implementation:

    package My::SQLiteDatabase;
    use parent 'Database::Abstraction';

    sub dbi_source {
        my ($self) = @_;
        return {
            dbh   => $self->{_dbh},      # connected DBD::SQLite handle
            table => $self->{_table},    # table name in that database
        };
    }

    1;

The zero-copy ATTACH path is always used when a component database implements
C<dbi_source()>.  Query-time criteria are applied as parameterised SQL
C<WHERE> clauses against the ATTACHed source table, so no row-level copy is
needed even when the current call includes column filters.  (Prior to 0.005.0
any query-time criteria forced a spill; that restriction was removed in
0.005.0.)

B<Result identity>

Both the array path and the SQLite path produce identical results for any
given query.  You can switch between them freely without changing callers.
The C<collision_prefix> column renaming, C<join_map> key translation, and all
three join types (left, inner, outer) work identically on both paths.

=head2 selectall_arrayref

=head3 SYNOPSIS

    my $rows = $join->selectall_arrayref();
    my $rows = $join->selectall_arrayref(tier  => 'gold');
    my $rows = $join->selectall_arrayref(score => { '>' => 80 });
    my $rows = $join->selectall_arrayref('C001');  # positional: entry => 'C001'
    my $rows = $join->selectall_arrayref(sort_by => 'name');
    my $rows = $join->selectall_arrayref(tier => 'gold', sort_by => ['score', 'DESC']);
    my $rows = $join->selectall_arrayref(limit => 10);
    my $rows = $join->selectall_arrayref(limit => 10, offset => 20);
    my $rows = $join->selectall_arrayref(tier => 'gold', sort_by => 'name', limit => 5);

=head3 DESCRIPTION

Returns an arrayref of hashrefs representing the merged view of all component
databases, optionally filtered by the given criteria.

Criteria for columns that live in different databases are routed
automatically: each database is queried with only the criteria that apply to
its own columns.  The results are combined in memory using C<join_column>.

Accepts the same criteria syntax as C<Database::Abstraction::selectall_arrayref>.
A single plain scalar argument is interpreted as the C<join_column> value
(equivalent to C<< entry => 'C001' >> when C<join_column> is C<'entry'>).

=head3 API SPECIFICATION

=head4 Input

    Calling conventions (in order of precedence):
      1. No arguments             -- returns all rows
      2. One plain scalar         -- shorthand for join_column => $scalar
      3. Key-value pairs or
         a criteria hashref       -- routed per-database

    Values may be:
      Plain scalar                -- exact match
      Hashref of operators        -- e.g. { '>' => 80 }

    Optional parameters (mixed in with any of the above):
      sort_by => 'colname'            -- sort ascending by that column
      sort_by => ['colname', 'DESC']  -- sort descending
      sort_by => ['colname', 'ASC']   -- sort ascending (explicit)
      limit    => N                    -- return at most N rows (positive integer)
      offset   => M                   -- skip the first M rows (non-negative integer)

    The column named in sort_by must be present in the merged view (i.e. it
    must appear in columns()).  An unknown column or an invalid direction emits
    a carp warning and falls back to the default join_column ascending sort.

    limit and offset are applied after ordering.  offset without limit skips
    rows but returns all remaining rows.  limit without offset starts from
    the first qualifying row.  An invalid limit or offset emits a carp warning
    and the parameter is ignored (treated as absent).

    DOMAIN -- sort_by:
      EP absent:          result sorted by join_column ASC (default).
      EP string:          any column name in columns(); sorts ASC by that column.
      EP ['col','ASC']:   explicit ascending; equivalent to the string form.
      EP ['col','DESC']:  descending sort by the named column.
      EP ['col']:         single-element arrayref; direction defaults to ASC.
      EP []:              empty arrayref; column is undef -> carp + join_col ASC fallback.
      EP invalid column:  column not in columns() -> carp + join_col ASC fallback.
      EP invalid dir:     direction not 'ASC' or 'DESC' -> carp + ASC used.
      Sort is lexicographic (cmp); use backend=>'sqlite' for numeric ORDER BY.

    DOMAIN -- limit:
      EP absent:          no truncation; all qualifying rows are returned.
      EP 0:               not a positive integer -> carp + ignored (all rows returned).
      BVA min valid = 1:  exactly 1 row returned.
      BVA at count:       limit == total rows -> all rows returned (no truncation).
      BVA above count:    limit > total rows -> all rows returned.
      EP invalid:         negative integer, float string, or non-numeric string
                          => carp + ignored (all rows returned).
      Valid domain:       integers in [1, INF); matched by /^\d+\z/a with value >= 1.

    DOMAIN -- offset:
      EP absent:          no rows skipped; result starts from row 0.
      BVA min valid = 0:  no rows skipped (zero is a valid non-negative integer).
      BVA offset=1:       first row skipped; result starts from row 1.
      BVA offset=N-1:     N-1 rows skipped; only the last row returned.
      BVA offset=N:       all N rows skipped; empty result returned.
      BVA offset>N:       all rows skipped; empty result returned.
      EP invalid:         negative integer, float string, or non-numeric string
                          => carp + ignored (no rows skipped).
      Valid domain:       integers in [0, INF); matched by /^\d+\z/a.

=head4 Output

    Arrayref of hashrefs; one hashref per qualifying merged row.
    Sorted ascending by join_column by default; caller-controlled via sort_by.
    At most C<limit> rows when limit is given; the first C<offset> rows are
    skipped when offset is given.
    Returns a reference to an empty array when no rows match.

=head3 EXAMPLE

    # All rows from both databases
    my $all = $join->selectall_arrayref();

    # Only rows where the 'tier' column (from the loyalty database)
    # equals 'gold' -- the criterion is routed to the right database
    my $vip = $join->selectall_arrayref(tier => 'gold');

    # Operator hashref: score > 80
    my $high = $join->selectall_arrayref(score => { '>' => 80 });

    # Access each merged row
    for my $row (@{$vip}) {
        printf "%-10s tier=%-8s score=%d\n",
            $row->{entry}, $row->{tier}, $row->{score} // 0;
    }

=head3 MESSAGES

    warn_unknown_column (carp)
        -- A criterion key names a column not present in any component database;
           the criterion is silently dropped and all rows are returned.
    error_join_criterion (croak)
        -- A criterion hashref contains a join => key (DA SQL-JOIN syntax).
           Use a separate DA object for the joined table instead.
    sort_by column unknown (carp)
        -- The column given in sort_by is not in the merged view; the result
           is returned in the default join_column ascending order instead.
    sort_by direction invalid (carp)
        -- The direction given in sort_by is not 'ASC' or 'DESC'; ASC is used.
    limit invalid (carp)
        -- The value given for limit is not a positive integer; it is ignored.
    offset invalid (carp)
        -- The value given for offset is not a non-negative integer; it is ignored.
    operator-unsupported (carp, SQLite/auto path only)
        -- An operator hashref key is not in the supported set (>, <, >=, <=,
           !=, =, LIKE, NOT LIKE, IS NULL, IS NOT NULL, IN, NOT IN); the
           individual operator term is dropped from the WHERE clause (other
           operators in the same hashref still apply).
    IN/NOT IN wrong value type (carp, SQLite/auto path only)
        -- An IN or NOT IN criterion was given a non-arrayref value; the operator
           term is skipped.
    error_sqlite_connect (croak, SQLite/auto path only)
        -- The temporary SQLite join file could not be created; check tmpdir
           permissions and available disk space.

=cut

=head2 selectall_array

=head3 SYNOPSIS

    my @rows = $join->selectall_array(tier => 'gold');

    # Scalar context: only the first matching row
    my $first = $join->selectall_array(entry => 'C001');

=head3 DESCRIPTION

In list context returns a list of merged hashrefs -- the same rows that
C<selectall_arrayref> would return, just as a flat list rather than an
arrayref.

In scalar context returns only the first matching hashref (or C<undef> if
nothing matches).

=head3 API SPECIFICATION

=head4 Input

    Same as selectall_arrayref.

=head4 Output

    List context:   list of hashrefs (may be empty).
    Scalar context: single hashref or undef.

=head3 EXAMPLE

    my @all = $join->selectall_array();
    print scalar @all, " rows\n";

    # First gold-tier customer only
    my $first_vip = $join->selectall_array(tier => 'gold');
    print $first_vip->{name}, "\n" if defined $first_vip;

=head3 MESSAGES

Same messages as C<selectall_arrayref>.

=cut

=head2 selectall_hashref

Deprecated alias for L</selectall_arrayref>, present for compatibility with
callers written against C<Database::Abstraction>'s deprecated API.  Use
C<selectall_arrayref> in new code.

=cut

=head2 selectall_hash

Deprecated alias for L</selectall_array>, present for compatibility with
callers written against C<Database::Abstraction>'s deprecated API.  Use
C<selectall_array> in new code.

=cut

=head2 fetchrow_hashref

=head3 SYNOPSIS

    my $row = $join->fetchrow_hashref(entry => 'C001');
    my $row = $join->fetchrow_hashref('C001');   # positional shorthand

=head3 DESCRIPTION

Returns a single merged hashref for the first row matching the given
criteria, or C<undef> when nothing matches.

Equivalent to calling C<selectall_arrayref> and taking only the first element.
All the same criteria conventions apply.

=head3 API SPECIFICATION

=head4 Input

    Same as selectall_arrayref.

=head4 Output

    Hashref, or undef when no row matches.

=head3 EXAMPLE

    my $row = $join->fetchrow_hashref(entry => 'C001');
    if (defined $row) {
        print "Name: $row->{name}, Tier: $row->{tier}\n";
    } else {
        print "No record for C001\n";
    }

    # Positional: works when join_column is 'entry'
    my $row2 = $join->fetchrow_hashref('C001');

=head3 MESSAGES

Same messages as C<selectall_arrayref>.

=cut

=head2 count

=head3 SYNOPSIS

    my $total  = $join->count();
    my $active = $join->count(tier => 'gold');

=head3 DESCRIPTION

Returns the number of merged rows that satisfy the given criteria.

On the array backend, the full in-memory join is performed and the resulting
rows are counted in Perl.  On the SQLite backend, a C<SELECT COUNT(*)> SQL
query is executed against the cached join tables, avoiding a full row fetch.

=head3 API SPECIFICATION

=head4 Input

    Same criteria syntax as selectall_arrayref.

=head4 Output

    Non-negative integer.

=head3 EXAMPLE

    my $total   = $join->count();
    my $gold    = $join->count(tier => 'gold');
    my $high    = $join->count(score => { '>' => 90 });

    printf "%d total, %d gold-tier, %d high-scorers\n",
        $total, $gold, $high;

=head3 MESSAGES

Same messages as C<selectall_arrayref>.

=cut

=head2 each_row

=head3 SYNOPSIS

    my $count = $join->each_row(sub { my ($row) = @_; ... });
    my $count = $join->each_row(sub { ... }, tier => 'gold');
    my $count = $join->each_row(sub { ... }, sort_by => 'name', limit => 100);

=head3 DESCRIPTION

Calls C<\&callback> once for every row in the merged view that matches the
given criteria, then returns the total count of rows visited.

Accepts the same criteria, C<sort_by>, C<limit>, and C<offset> parameters as
C<selectall_arrayref>.

B<Note on memory usage>: C<Database::Join> always materialises the complete
merged result set before invoking the callback, because merging rows from
multiple independent sources requires that every source be queried and
cross-referenced first.  True constant-memory streaming (as C<Database::Abstraction>
provides on single-source SQL queries) is not possible at the join layer.
For genuinely large result sets use the SQLite backend (C<backend =E<gt>
'sqlite'>), which keeps peak RAM to approximately one times the source data
size rather than three.

Any exception thrown inside the callback propagates to the caller after the
current row; subsequent rows are not visited.

=head3 API SPECIFICATION

=head4 Input

    \&callback   Positional coderef (required).
                 Called as $callback->($row_hashref) for each merged row.

    Additional arguments follow the same calling conventions as
    selectall_arrayref: no args, a single scalar join-column value,
    or key-value criteria pairs (including sort_by, limit, offset).

=head4 Output

    Non-negative integer: the count of rows for which the callback was invoked.

=head3 EXAMPLE

    # Print every gold-tier customer's name, sorted by name
    my $count = $join->each_row(
        sub { my ($row) = @_; print "$row->{name}\n" },
        tier    => 'gold',
        sort_by => 'name',
    );
    print "$count gold-tier customers\n";

    # Accumulate without holding the full result
    my $total_score = 0;
    $join->each_row(sub { $total_score += $_[0]->{score} // 0 });

=head3 MESSAGES

    error_invalid_callback (croak)
        -- First argument is not a code reference.

=cut

=head2 dbi_source

=head3 SYNOPSIS

    # Use a Database::Join object as a zero-copy SQLite source inside a
    # parent Database::Join, giving the parent ATTACHed-speed access to
    # the child's joined data without iterating through Perl.
    my $child  = Database::Join->new(databases => [$da, $db], join_column => 'id', backend => 'sqlite');
    my $parent = Database::Join->new(databases => [$child, $dc], join_column => 'id', backend => 'sqlite');

=head3 DESCRIPTION

Returns a hashref C<< { dbh => $sqlite_dbh, table => '_dj_result' } >> that
allows a parent C<Database::Join> (or any other caller that understands the
C<dbi_source()> interface) to ATTACH the child's temporary SQLite database
file and query the materialised join result directly via SQL, without routing
rows through Perl.

The first call builds the SQLite cache (if not already current) and
materialises the full join result - with C<filters> applied but no query-time
criteria - into a real table named C<_dj_result> inside the cache file.
Subsequent calls within the same cache cycle reuse the existing table.

Returns C<undef> when the backend is C<'array'> (no SQLite file exists).

=head3 API SPECIFICATION

=head4 Input

    None.

=head4 Output

    On the SQLite/auto backend:
      Hashref with keys:
        dbh   => DBI handle to the child's temporary SQLite database file.
        table => '_dj_result'  (the materialized join table inside that file).
    On the array backend:
      undef

=head3 EXAMPLE

    # The parent automatically ATTACHes the child's SQLite file and queries
    # _dj_result for zero-copy composable nested joins.
    my $inner = Database::Join->new(
        databases   => [$customers, $loyalty],
        join_column => 'entry',
        backend     => 'sqlite',
    );
    my $outer = Database::Join->new(
        databases   => [$inner, $scores],
        join_column => 'entry',
        backend     => 'sqlite',
    );
    my $rows = $outer->selectall_arrayref(tier => 'gold');

=head3 MESSAGES

    error_sqlite_connect (croak)
        -- The temporary SQLite join file could not be created.

=cut

=head2 columns

=head3 SYNOPSIS

    my $cols = $join->columns();

=head3 DESCRIPTION

Returns an arrayref of all column names visible in the merged view,
deduplicated and sorted alphabetically.

The C<join_column> appears exactly once, even if it exists under different
local names in some databases (see C<join_map>).  Columns that have been
hidden with C<remove_column> or C<remove_columns> do not appear.

The result is memoised: repeated calls are cheap.

=head3 API SPECIFICATION

=head4 Input

    None.

=head4 Output

    Arrayref of column name strings, sorted alphabetically.

=head3 EXAMPLE

    my $cols = $join->columns();
    print join(', ', @{$cols}), "\n";
    # e.g. "entry, name, score, tier"

=head3 MESSAGES

C<columns()> does not itself emit any warnings or errors.  Any exception thrown
by a component database's C<columns()> method propagates uncaught.

=cut

=head2 schema

=head3 SYNOPSIS

    my $schema = $join->schema();

=head3 DESCRIPTION

Returns a merged schema hashref for all visible columns across all component
databases.  Each key is a column name; each value is the schema metadata
hashref returned by C<Database::Abstraction::schema()> for that column
(typically C<{ type, nullable, default, pk }>).

When the same column name appears in more than one database the I<last>
database's metadata is used.  Columns hidden with C<remove_column> are not
included.

The result is memoised.

=head3 API SPECIFICATION

=head4 Input

    None.

=head4 Output

    Hashref: column_name => { type => ..., nullable => ..., default => ..., pk => ... }.

=head3 EXAMPLE

    my $schema = $join->schema();
    for my $col (sort keys %{$schema}) {
        my $info = $schema->{$col};
        printf "%-15s type=%-10s nullable=%s\n",
            $col, $info->{type}, $info->{nullable} ? 'yes' : 'no';
    }

=head3 MESSAGES

C<schema()> does not itself emit any warnings or errors.  Any exception thrown
by a component database's C<schema()> method propagates uncaught.

=cut

=head2 updated

=head3 SYNOPSIS

    my $ts = $join->updated();

=head3 DESCRIPTION

Returns the Unix timestamp of the most recent modification across all
component databases.  This is the maximum of all individual C<updated()>
return values.

Use this to implement simple cache-invalidation logic: if C<updated()>
has advanced since your last snapshot, re-query.

=head3 API SPECIFICATION

=head4 Input

    None.

=head4 Output

    Unix timestamp (positive integer).

=head3 EXAMPLE

    my $last_modified = $join->updated();
    if ($last_modified > $my_cache_timestamp) {
        $my_cache = $join->selectall_arrayref();
        $my_cache_timestamp = $last_modified;
    }

=head3 MESSAGES

C<updated()> does not emit any warnings or errors.  Component databases that do
not implement C<updated()>, or whose C<updated()> throws, are silently skipped;
only defined return values contribute to the maximum.  If no component database
implements C<updated()>, C<undef> is returned (same as C<List::Util::max> on an
empty list).

=cut

=head2 set_logger

=head3 SYNOPSIS

    $join->set_logger($log);
    $join->set_logger(logger => $log);   # named-pair form also accepted

=head3 DESCRIPTION

Attaches a new logger object to the join and propagates it to every component
database.  The logger is used for diagnostic output by all component databases.

Non-blessed values (a log-level string such as C<"debug">, a filename, or a
code reference) are wrapped in C<Log::Abstraction->new(...)> automatically,
matching the behaviour of C<Database::Abstraction::set_logger>.

=head3 API SPECIFICATION

=head4 Input

    logger  Positional or named: a logger object, log-level string, filename,
            or code reference (required).  Non-blessed values are wrapped in
            Log::Abstraction automatically.

=head4 Output

    Returns C<$self> for method chaining.

=head3 EXAMPLE

    # Log::Any is used here as an example; any object that implements
    # debug() and info() (or whichever methods your component databases
    # call internally) works equally well.
    use Log::Any qw($log);

    my $join = Database::Join->new(databases => [$db1, $db2], join_column => 'entry');
    $join->set_logger($log);
    # $log is now used by $join and by $db1 and $db2

    # Named-pair form (mirrors Database::Abstraction API):
    $join->set_logger(logger => $log);

=head3 MESSAGES

    (croak) Usage: set_logger(logger => $logger)
        -- Called with an undefined argument.  Pass a valid logger object.

=cut

=head2 add_database

=head3 SYNOPSIS

    # Positional: database object as first argument
    $join->add_database($db);

    # Named: equivalent to the above
    $join->add_database(database => $db);

    # With options (mixed positional + named)
    $join->add_database($db, remove_columns => ['internal_id']);
    $join->add_database($db, join_column    => 'local_key_name');
    $join->add_database($db, filter         => { score => { '>' => 60 } });

    # Chainable
    $join->add_database($db1)->add_database($db2, remove_columns => ['notes']);

=head3 DESCRIPTION

Adds one more C<Database::Abstraction> subclass object to the logical view
and immediately updates the column-ownership index.

After the call, all query methods return rows that include columns from the
newly added database, and criteria on those new columns are routed to it
automatically.

When a column name in the new database already exists in an earlier database,
the new database becomes the authoritative source for that column
(last-database-wins, the same rule that applies at construction time).

The join-column must be present in the new database (or declared via
C<join_column>).  The logger is propagated to the new database if one is set.

C<add_database> is the runtime equivalent of listing the database in the
C<databases> array to C<new>.  The optional C<join_column> parameter is
equivalent to a C<join_map> entry; the optional C<filter> parameter is
equivalent to a C<filters> entry.

=head3 API SPECIFICATION

=head4 Input

    database       => { type => 'object',   required => 1 }
                      # A Database::Abstraction subclass instance.
                      #
                      # DOMAIN -- EP valid:   blessed object that passes
                      #                       isa('Database::Abstraction').
                      # DOMAIN -- EP invalid: non-reference, unblessed ref, wrong class,
                      #                       or non-reference non-key scalar (the guard at
                      #                       the top of add_database rejects it with
                      #                       error_invalid_db before validate_strict runs).

    join_column    => { type => 'string',   optional => 1 }
                      # The name of the join key in THIS new database,
                      # when it differs from the canonical join_column.
                      #
                      # DOMAIN -- EP valid:   any string that exists as a column in the
                      #                       new database.
                      # DOMAIN -- EP invalid: string absent from the new database's columns()
                      #                       => croak error_join_col_missing.

    filter         => { type => 'hashref',  optional => 1 }
                      # Permanent criteria for this database only.
                      # Same format as selectall_arrayref.
                      #
                      # DOMAIN -- EP valid:   hashref of criteria (may be {} for no-op).
                      # DOMAIN -- EP absent:  no permanent filter applied; all rows visible.
                      # DOMAIN -- Key-set:    a non-empty filter makes this DB an inner-join
                      #                       partner regardless of the outer join_type.

    remove_columns => { type => 'arrayref', optional => 1 }
                      # Column names from this database to hide.
                      #
                      # DOMAIN -- EP valid:   arrayref of strings; non-existent columns silently
                      #                       ignored; empty [] is a safe no-op.
                      # DOMAIN -- EP invalid: join_column itself => croak error_remove_join_col.

=head4 Output

    Returns C<$self> to support method chaining.

=head3 EXAMPLE

    my $join = Database::Join->new(
        databases   => [ $customers ],
        join_column => 'entry',
    );

    # Add loyalty data; hide internal columns from it
    $join->add_database($loyalty, remove_columns => ['audit_ts']);

    # Add score data; only include rows with score > 60
    $join->add_database($scores, filter => { score => { '>' => 60 } });

    # Add a database whose join key has a different local name
    $join->add_database($stnames, join_column => 'state_code');

    # All three options combined, and chained
    $join->add_database($db4,
        join_column    => 'ref_id',
        filter         => { active => 1 },
        remove_columns => ['legacy_col'],
    );

=head3 PSEUDOCODE

    determine the new database's index (length of current _dbs array)
    extract the database object from positional or named argument
    croak if it is not a Database::Abstraction subclass
    register join_column alias in _join_map if different from canonical
    register filter in _filters if provided
    fetch column list from the new database
    croak if the join key is missing from the new database
    append the new database to _dbs and _db_cols
    update _col_db: for each new column, point it at the new index
        (last-database-wins; skip removed columns and the local join alias)
    invalidate _col_cache and _schema_cache
    propagate logger if set
    apply remove_columns if provided
    return $self

=head3 MESSAGES

    error_invalid_db          -- argument is not a Database::Abstraction subclass
    error_join_col_missing    -- join_column not found in the new database
    warn_schema_type_mismatch -- (carp) the new database has a shared column whose type
                                 differs from the type already in the view; use
                                 collision_prefix to preserve both values

=cut

=head2 remove_column

=head3 SYNOPSIS

    $join->remove_column('email');

    # Chainable
    $join->remove_column('internal_id')->remove_column('audit_ts');

=head3 DESCRIPTION

Permanently hides a column from the merged view.  After this call:

=over 4

=item *

The column does not appear in C<columns()> or C<schema()>.

=item *

Returned row hashrefs do not contain the column key.

=item *

Any query criterion that references the removed column is silently dropped
(with a C<carp> warning).

=back

The C<join_column> cannot be removed; attempting to do so will C<croak>.
Removing a column that does not exist in any database is silently ignored
(the call is idempotent and safe).  The C<columns()> and C<schema()>
memoisation caches are cleared automatically.

=head3 API SPECIFICATION

=head4 Input

    $col    Positional string: the column name to remove.

            DOMAIN -- EP valid:   any string; non-existent columns are silently
                                  ignored (idempotent call, returns $self).
            DOMAIN -- EP invalid: join_column value => croak error_remove_join_col.
            DOMAIN -- BVA:        undef and '' are explicit no-ops (returns $self).
                                  These are below the minimum meaningful string
                                  length and are handled without any warning.

=head4 Output

    Returns C<$self> to support method chaining.

=head3 EXAMPLE

    # Hide private fields immediately after construction
    my $join = Database::Join->new(
        databases   => [ $customers, $loyalty ],
        join_column => 'entry',
    )->remove_column('email')
     ->remove_column('internal_notes');

    # Verify they are gone
    my $cols = $join->columns();
    # 'email' and 'internal_notes' are absent

=head3 MESSAGES

    error_remove_join_col -- attempt to remove the join_column itself

=cut

=head2 query

Not supported.  C<Database::Join> does not implement the
C<Database::Abstraction::Query> chained builder because the builder's
C<.all()> / C<.first()> methods would be targeting a single component DA
rather than the merged view.  Calling this method will always C<croak>.

Use C<selectall_arrayref>, C<selectall_array>, C<fetchrow_hashref>,
C<count>, or C<each_row> against the C<Database::Join> object instead.

=cut

=head2 execute

Not supported.  Raw SQL cannot span heterogeneous backends that may use
different database engines.  Calling this method will always C<croak>.

Use C<selectall_arrayref> or C<fetchrow_hashref> to query the joined view.

=cut

=head2 AUTOLOAD - column shortcut

Calling an unknown method whose name matches a visible column name performs
a column lookup across the merged view.

=head3 SYNOPSIS

    # Scalar context: value from the first matching row
    my $name  = $join->name(entry => 'C001');

    # List context: values from every matching row
    my @tiers = $join->tier();

    # With a positional join-key argument (when join_column is 'entry')
    my $score = $join->score('C001');

=head3 DESCRIPTION

AUTOLOAD routes the call to the appropriate component database by looking up
the column name in the internal column-ownership index.

When either C<join_map> or C<filters> is active, AUTOLOAD performs a full
join query instead of delegating directly to the owning database.  This is
necessary because:

=over 4

=item *

With C<join_map>, the owning database's primary key may differ from the
canonical join key used in the call arguments.

=item *

With C<filters>, bypassing the join would return rows that the filter is
meant to exclude.

=back

In list context, every matching merged row contributes one value to the
returned list.  In scalar context, only the first row's value is returned.

Calling a method whose name begins with C<_> (a private method) via AUTOLOAD
will C<croak> with a clear error message rather than being silently ignored.

=head3 EXAMPLE

    # Lookup a single customer's name (scalar context)
    my $name = $join->name('C001');   # 'C001' maps to entry => 'C001'
    print "Name: $name\n";

    # Get every tier value in the view (list context)
    my @all_tiers = $join->tier();
    my %freq;
    $freq{$_}++ for @all_tiers;

    # join_map active: AUTOLOAD runs a full join so the criteria are
    # translated correctly between the canonical and local key names.
    my @leesburg_states = sort $join->state('Leesburg');
    # ['Florida', 'Virginia'] if Leesburg appears in two states

=head3 PSEUDOCODE

    extract column name from $AUTOLOAD
    return if DESTROY
    croak if column name starts with '_' (private method guard)
    croak if column name is not in _col_db (unknown column)
    if join_map or filters are active:
        parse calling arguments using _parse_query_args
        call _joined_query to get all merged rows
        return map { $_->{col} } @rows  in list context
        return $rows[0]{col}            in scalar context
    else:
        delegate directly to the owning database

=head3 MESSAGES

    (croak) Database::Join: cannot call private method '_NAME' via AUTOLOAD
        -- Method name begins with '_'.  Private methods must be called directly,
           not via AUTOLOAD.  This is a programming error.

    (croak) Database::Join: unknown column 'NAME'
        -- Method name does not match any visible column in the merged view.
           Check spelling, or whether the column was removed with remove_column().

=cut