Revision history for Database::Join

0.007.1	Fri Sep 25 08:17:24 AM EDT 2026
	[ Bug Fixes ]
	'order_by' is rather SQL like, 'sort_by' is clearer

0.007.0	Thu Sep 24 09:56:58 PM EDT 2026
	[ Enhancements ]
	- Schema type consistency validation: new() and add_database() now call the
	  new _validate_schema_types() protected helper immediately after the column
	  routing table is built.  For every column that is shared across two or more
	  databases WITHOUT a collision_prefix, the helper compares the schema() type
	  strings (e.g. "TEXT" vs "INTEGER") and emits a warn_schema_type_mismatch
	  carp when they disagree.  This surfaces silent type-coercion bugs at
	  construction time rather than at query time.  The join column itself and
	  any column that has a collision prefix configured are exempt (the join
	  column is structural, not a data column; prefixed columns already have
	  distinct published names so no merge occurs).  Callers that intentionally
	  accept the merge may suppress the warning by adding collision_prefix for
	  the secondary database.  DAs that return an empty or undef schema() are
	  skipped silently.
	- parallel => 1 constructor flag: when set and the join has three or more
	  databases (primary + two or more secondaries), secondary DA fetches are
	  issued concurrently using Perl threads.  Each secondary is queried in its
	  own thread; results are collected with join() and indexed exactly as in the
	  sequential path.  Falls back to sequential with a carp warning when the
	  'threads' module is unavailable.  No effect on the SQLite backend (which
	  executes a single SQL JOIN).  DBI-backed DAs are not thread-safe by default;
	  documented in POD.  The threshold n > 2 avoids thread-spawn overhead for the
	  common single-secondary case.
	- dbi_source() on Database::Join itself: a Database::Join object with a
	  SQLite or auto backend now implements dbi_source(), returning {dbh, table}
	  pointing at a materialised '_dj_result' table inside its temporary SQLite
	  file.  A parent Database::Join (or any caller that understands the
	  dbi_source() interface) can ATTACH the child's file and query _dj_result
	  directly via SQL, giving composable nested joins at full ATTACHed speed
	  without routing rows through Perl.  Filter criteria (filters =>) are baked
	  into _dj_result at materialisation time; query-time criteria are applied
	  by the parent's WHERE clause per-call.  The result table is rebuilt lazily
	  when the underlying source data changes (cache invalidation propagates via
	  updated() timestamps exactly as for leaf sources).  Returns undef on the
	  array backend.  The 'auto' backend forces the SQLite path when dbi_source()
	  is called so a parent always gets a usable handle.
	- Limit / offset pagination: selectall_arrayref and selectall_array now
	  accept limit => N (positive integer) and offset => M (non-negative integer).
	  The SQLite path appends LIMIT ? OFFSET ? as bind parameters to the generated
	  SQL (injection-safe).  The array path uses splice() on the ordered result.
	  offset without limit uses LIMIT -1 OFFSET ? on the SQLite path (all rows
	  from offset).  An invalid limit or offset emits a carp warning and the
	  parameter is treated as absent.  count() and fetchrow_hashref ignore both
	  parameters silently.
	- Caller-specified ORDER BY on all query methods (selectall_arrayref,
	  selectall_array, fetchrow_hashref, AUTOLOAD).  Pass order_by => 'col' for
	  ascending or order_by => ['col', 'DESC'] for descending.  The SQLite
	  backend translates this to a SQL ORDER BY clause (type-aware sort); the
	  array backend re-sorts the in-memory result using Perl string comparison
	  (cmp).  An unknown column or an invalid direction emits a carp and falls
	  back to the default join_column ascending sort.  count() ignores order_by
	  as row ordering does not affect a count.
	- IS NULL and IS NOT NULL operators are now fully supported on the SQLite
	  backend via explicit operator hashrefs ({ 'IS NULL' => undef }).  These
	  operators generate no bind parameter — the fixed SQL keywords are
	  interpolated, not caller values — so they are injection-safe.  A bare
	  undef criterion value (col => undef) also generates IS NULL on the SQLite
	  path (previously it silently generated col = NULL which never matches
	  anything in SQL).
	- IN and NOT IN list operators are now fully supported on the SQLite backend.
	  The criterion value is an arrayref; each element becomes a separate bind
	  parameter (col IN (?, ?, ...)) so injection via list elements is impossible.
	  IN with an empty arrayref returns no rows (SQL 1=0 semantics); NOT IN with
	  an empty arrayref adds no constraint (all rows match).  A non-arrayref value
	  emits a carp warning and the criterion is skipped.
	- count() on the SQLite backend now executes SELECT COUNT(*) against the
	  cached join tables instead of fetching all rows.  The JOIN and WHERE
	  clauses are the same as for selectall_arrayref, so criteria and join-type
	  semantics are identical; only the SQL projection changes.  On large cached
	  datasets this avoids transferring and allocating every row just to count
	  them.  The array path and the 'auto' array-path fallback are unchanged.
	- Add =head3 MESSAGES POD sections to all public methods (selectall_arrayref,
	  selectall_array, fetchrow_hashref, count, columns, schema, updated,
	  set_logger, AUTOLOAD) documenting every carp/croak each method can emit.
	[ Bug fixes ]
	- Fix: updated() now silently skips component databases that do not implement
	  updated() or whose updated() throws.  Previously the exception propagated
	  uncaught.  Consistent with the existing _cache_fresh() defensive pattern.
	  Returns undef when no component database implements updated().
	- Fix: LIKE and NOT LIKE operators now work correctly on the SQLite backend.
	  Both operators use a single bind parameter (col LIKE ?) and are
	  injection-safe.  Previously, LIKE/NOT LIKE were silently skipped on the
	  SQLite path with no warning, causing silently wider results for callers
	  who developed against a small dataset (array path) and deployed at scale
	  (SQLite path).  Unknown operators now emit a carp warning instead of
	  being silently ignored, so callers are not misled.

0.006.0	Tue Sep 22 07:30:33 PM EDT 2026
	[ Enhancements ]
	- JSON backend support: Database::Join now works with JSON-backed
	  Database::Abstraction objects (Database::Abstraction 0.45+,
	  JSON::MaybeXS required).  Both array-form ([{entry:k,...},...]) and
	  object-form ({"k":{col:val},...}) JSON files are supported.  JSON URL
	  sources are also supported.  Component databases may now freely mix
	  formats (CSV, TSV, SQLite, JSON, XML, XLSX, etc.) in a single join.
	- Add use 5.010001 pragma (enforces minimum Perl version at compile time).
	- Replace $DBI::errstr with DBI->errstr method call to eliminate the
	  "used only once" warning under perl -c.
	- Add _sql_quote_identifier() helper; apply to all column and table name
	  sites in the SQLite DDL/DML, preventing SQL identifier injection from
	  DA-supplied column names that contain embedded double-quote characters.
	- Add t/json.t: 20 subtests covering JSON-backed joins (left/inner/outer,
	  criteria routing, fetchrow_hashref, columns, schema, collision_prefix,
	  add_database, updated() timestamp, SQLite backend spill path, and both
	  array-form and object-form JSON).
	[ Bug fixes ]
	- Fix: left and outer joins with a join-column criterion (e.g.
	  selectall_arrayref(entry => 'k1')) now correctly return primary-only
	  rows.  Previously the broadcast of the join-column criterion to all
	  secondary databases was incorrectly counted as "having criteria",
	  promoting every secondary to inner-join status and silently dropping
	  primary-only keys.  Only non-join-column criteria (column filters from
	  the caller or base filters => {...}) now trigger inner-join semantics.
	  The fix applies to both the in-memory array backend and the SQLite
	  backend (the SQLite path also no longer adds the broadcast join-column
	  criterion to secondary table WHERE clauses, which would have nullified
	  LEFT JOIN at the SQL level).


0.005.0	Mon Sep 21 02:13:04 PM EDT 2026
	[ Enhancements ]
	- SQLite backend now caches the spilled temporary database across query
	  calls on the same Database::Join object.  Source data is spilled once
	  at first use; subsequent selectall_arrayref / fetchrow_hashref / count
	  calls reuse the same File::Temp SQLite file and DBI handle.  The cache
	  is invalidated automatically when any source database's updated()
	  timestamp changes, or when add_database() is called.
	- Query-time criteria are no longer applied at spill time.  They are
	  translated into parameterised SQL WHERE clauses (bind values, not
	  string interpolation) and applied per-call against the cached data.
	  This means the cached tables are query-agnostic and serve any
	  combination of criteria without rebuilding.
	- Zero-copy ATTACH (dbi_source() path) is now unconditional: the
	  previous restriction that prevented ATTACH when query-time criteria
	  existed has been removed.  Criteria for ATTACHed sources go into the
	  SQL WHERE clause alongside criteria for spilled sources.
	- Add %SAFE_SQL_OPS constant to gate operator-hashref interpolation
	  into WHERE clauses (>, <, >=, <=, !=, =), preventing SQL injection
	  from caller-supplied operator hashrefs.
	- DESTROY now disconnects the cached DBI handle and releases the
	  File::Temp object, ensuring the temp file is removed when the
	  Database::Join object goes out of scope.
	- add_database() now invalidates the SQLite cache so the next query
	  picks up the new source.
	- _cache_fresh :Protected helper added; _build_sqlite_cache :Protected
	  helper added (formerly inline in _sqlite_join).
	- Update t/sqlite-backend.t: S12 (temp file cleanup) updated to match
	  new lifetime semantics; add S16 (cache reuse verified by DA call
	  counts), S17 (cache invalidation via UpdatableDA), S18 (ATTACH with
	  query-time criteria now produces correct filtered results).
	- Update POD: LIMITATIONS, COMMON PITFALLS, and backend section
	  updated to document per-object cache lifetime, cache invalidation
	  rules, and %SAFE_SQL_OPS operator gating.

0.004.0	Fri Sep 18 08:27:22 PM EDT 2026
	[ Enhancements ]
	- Add SQLite-backed join backend (backend => 'sqlite' | 'auto' | 'array').
	  'auto' (default) transparently spills source data to a temporary SQLite
	  database and executes a single SQL JOIN when combined source row count
	  exceeds max_array_rows (default 10,000), reducing peak RAM from 3x to
	  roughly 1x source size for large joins.  Small joins continue to use the
	  existing in-memory array path with no overhead.  'sqlite' forces the SQL
	  path unconditionally; 'array' forces the existing path unconditionally.
	- Add max_array_rows constructor parameter (integer, default 10,000):
	  threshold above which 'auto' mode activates the SQLite path.
	- Add tmpdir constructor parameter (string, default File::Spec->tmpdir):
	  directory used for the per-call temporary SQLite database file.
	- Zero-copy ATTACH optimisation: source databases that implement
	  dbi_source() (returning a hashref with dbh => $sqlite_dbh and
	  table => $name) bypass row-level copying; their SQLite file is ATTACHed
	  directly to the temporary join connection.
	- collision_prefix renaming is applied as SQL AS aliases in the SELECT
	  clause, preserving identical column-name semantics on the SQLite path.
	- join_map asymmetric key names are applied in the JOIN ON clause.
	- Auto-threshold row count uses dbi_source() COUNT(*) or a DA-defined
	  count() override (checked via defined &{"${pkg}::count"}) to avoid
	  fetching rows purely for sizing; falls back conservatively to the array
	  path when neither is available.
	- Move DBD::SQLite (>= 1.70), DBI, and File::Temp from TEST_REQUIRES to
	  PREREQ_PM; add File::Spec to PREREQ_PM.
	- Add t/sqlite-backend.t: 14 subtests covering backend dispatch, auto
	  threshold, result identity, inner/outer join, 3-way join, collision_prefix
	  SQL aliases, join_map ON clause, dbi_source() zero-copy ATTACH, temp file
	  cleanup, and max_array_rows boundary conditions.

0.003.1	Fri Sep 18 04:30:36 PM EDT 2026
	[ Enhancements ]
	- Added tsv file tests

0.003.0	Fri Sep 11 05:12:03 PM EDT 2026
	[ Bug Fixes ]
	- Replace isa('Database::Abstraction') guard in new() and add_database()
	  with duck-typing (can('selectall_arrayref') && can('columns')).
	  Database::Join now accepts any object that exposes those two methods,
	  including wrapper classes (e.g. Database::BI::Model::DataSource) and
	  chained Database::Join instances, without requiring them to inherit
	  from Database::Abstraction.  Existing callers passing genuine
	  Database::Abstraction subclasses are unaffected.

0.002.0	Fri Sep 11 02:07:01 PM EDT 2026
	[ Enhancements ]
	- Add collision_prefix constructor parameter (HashRef[Str], keyed by
	  zero-based database index).  When a secondary database has a column
	  that collides with an existing column in the merged view, the secondary
	  column is published as "$prefix.$col" so both values survive in every
	  merged row.  Omitting collision_prefix preserves the previous
	  last-database-wins behaviour unchanged.  Criteria on prefixed column
	  names are correctly translated back to the database's own column name
	  before the query is issued.  The join_column is never prefixed.
	- Add t/collision-prefix.t: 12 subtests covering columns(), schema(),
	  row merge, criteria routing by prefixed name, fetchrow_hashref,
	  backward compat, index-0 ignore, non-colliding pass-through,
	  remove_column, three-database chaining, and add_database.
	- Add t/xlsx.t: end-to-end tests with XLSX-backed component databases
	  (requires DBD::Excel + Spreadsheet::WriteExcel; skipped otherwise).
	  Exercises left/inner/outer join, criteria routing, fetchrow_hashref,
	  columns(), collision_prefix, and add_database against real .xlsx files.
	  Documents that DBD::Excel returns empty schema() for XLSX databases.
	- Hoist per-secondary-database constants (local join-column name, rename
	  flag, rename map) outside the O(K*N*R) key/row/column merge loop,
	  eliminating repeated hash lookups on every merged row.
	- Seed the key_set hash via a hash slice instead of building an
	  intermediate list, halving allocations for large primary result sets.
	- Cache the removed-column list lazily in _removed_list (invalidated by
	  remove_column) to avoid rebuilding keys(%_removed_cols) on every query.
	- Iterate base rows directly from the indexed arrayref instead of copying
	  into @base_rows, eliminating one array allocation per qualifying key.
	- Restructure remove_column guard to a single early-return that covers
	  both undef and empty-string inputs (boolean reduction).
	- Remove dead store: _joined_query no longer evaluates had_criteria[0]
	  for the primary database (it is never read).
	- Remove spurious // {} fallback in _partition_criteria: _col_unrename
	  entries are always hashrefs by construction.
	- Harden AUTOLOAD regex: possessive quantifier \w++ prevents backtracking;
	  $ anchor replaced by \z (never matches a trailing newline); /x modifier
	  with inline comments added.  Private-method guard replaced with a
	  substr() check, eliminating a second regex entirely.
	[ Bug Fixes ]
	- Fix heap-address leakage via collision_prefix: _build_col_index now
	  rejects non-string values (refs) immediately, croaking with
	  error_invalid_prefix before any column name is constructed.  Mirrors
	  the existing join_map value type guard.
	- Fix filter reference aliasing: the filters constructor parameter and
	  the add_database filter option are now deep-copied (_copy_filters /
	  _copy_criteria helpers) so post-construction mutation of the caller's
	  hashref cannot widen the logical view or bypass row-security guarantees.

0.001.1	Sun Aug 23 03:29:39 PM EDT 2026
	[ Bug fixes ]
	- Fix t/edge_cases.t false failures on CPAN Testers: heap-address guard tests
	  now inspect only the first line of $@ (the error text) rather than the full
	  exception string including croak's stack trace, which naturally contains
	  object addresses.
	  Fixes https://matrix.perl-magpie.org/results/def96e6c-9e2e-11f1-af92-fa432959d47e

0.001.0	Fri Aug 21 08:54:30 PM EDT 2026
    - Initial CPAN release.
    - Read-only combined view across two or more Database::Abstraction objects
      joined on a shared key column.
    - Supports left, inner, and outer join types.
    - Per-database base filters with AND-merge semantics for compound criteria.
    - join_map for cross-name join-key column support.
    - add_database for runtime extension of the logical view.
    - remove_column / remove_columns for hiding columns from callers.
    - set_logger for logger propagation to all component databases.
    - AUTOLOAD column-shortcut delegation.
