File Coverage

=head1 NAME

Database::Abstraction - Read-only Database Abstraction Layer (ORM)

=head1 VERSION

Version 0.52

=cut

=head1 DESCRIPTION

C<Database::Abstraction> is a read-only ORM for Perl that gives a uniform
interface over CSV, PSV, TSV, JSON, XML, SQLite, DBM::Deep, BerkeleyDB, and
Excel (XLS/XLSX) files - local, remote (via SSH), or fetched from a URL -
without writing any SQL.
Effectively it allows you to access a database table, of many different
database formats, as an object.

Key features:

=over 4

=item *

B<No SQL required.>  Use plain Perl method calls for simple lookups and
scans; switch storage formats without changing application code.

=item *

B<Rich query criteria.>  Pass plain values, SQL wildcards, C<undef> (IS NULL),
comparison operators (C<< > >> C<< < >> C<< >= >> C<< <= >> C<!=>), pattern
operators (C<-like>, C<-not_like>), set operators (C<-in>, C<-not_in>,
C<-between>), and logical groupings (C<-or>, C<-and>).

=item *

B<Automatic joins.>  Add a C<join> parameter to any select method to
combine tables with INNER, LEFT, RIGHT, FULL, or CROSS joins.

=item *

B<Chained query builder.>  The C<query()> method returns a
L<Database::Abstraction::Query> object for fluent, composable queries:
C<< $db->query->where(...)->order_by(...)->limit(...)->all() >>.

=item *

B<Aggregation.>  The C<aggregate()> method and the fluent
C<< ->group_by()->measure()->having()->all() >> chain push C<GROUP BY> to
SQL backends and fold rows in Perl on slurp-only backends.

=item *

B<Schema introspection.>  C<columns()> lists column names; C<schema()>
returns full type/nullability metadata, using native driver introspection
(C<PRAGMA table_info> for SQLite, C<column_info> for others).

=item *

B<DSN portability.>  Pass a C<dsn> (plus optional C<username>/C<password>)
to connect to any DBI-supported database (SQLite, PostgreSQL, MySQL, ...)
instead of pointing at a local file.

=item *

B<Performance.>  Small files are slurped into a RAM hash for sub-millisecond
lookups.  All DBI statement handles are cached with C<prepare_cached()>.
A CHI-compatible cache layer is also supported.

=back

=head1 SYNOPSIS

    # 1. Create a thin subclass for your table (e.g. Database/Foo.pm)
    package Database::Foo;
    use parent 'Database::Abstraction';

    # 2. Open the database - file is auto-detected from the class name
    #    (looks for foo.sql / foo.sqlite / foo.sqlite3 / foo.psv / foo.tsv / foo.csv / foo.xlsx / foo.xml / foo.json / foo.db)
    my $db = Database::Foo->new(directory => '/path/to/data');

    # 3. Simple lookups -----------------------------------------------

    # Fetch one row
    my $row = $db->fetchrow_hashref(entry => 'key1');

    # Fetch all rows matching a criterion
    my $rows = $db->selectall_arrayref(status => 'active');

    # Column shortcut via AUTOLOAD
    my $name = $db->name(entry => 'key1');

    # 4. Rich criteria ------------------------------------------------

    # Comparison operators
    my $high = $db->selectall_arrayref(score => { '>' => 90 });

    # Set membership
    my $selected = $db->selectall_arrayref(
        name => { -in => ['Alice', 'Bob'] }
    );

    # Range
    my $mid = $db->selectall_arrayref(
        score => { -between => [60, 80] }
    );

    # OR grouping
    my $either = $db->selectall_arrayref(
        -or => [
            { status => 'active'    },
            { score  => { '>' => 95 } },
        ]
    );

    # 5. Joins --------------------------------------------------------

    my $joined = $db->selectall_arrayref(
        join => { table => 'dept', on => 'foo.dept_id = dept.id', type => 'LEFT' }
    );

    # 6. Chained query builder ----------------------------------------

    my $results = $db->query
        ->where(status => 'active')
        ->where(score  => { '>=' => 80 })
        ->order_by('score DESC')
        ->limit(10)
        ->all();

    my $first = $db->query->where(name => 'Alice')->first();
    my $count = $db->query->where(status => 'active')->count();

    # 7. Connect via DSN (PostgreSQL, MySQL, SQLite, ...) ---------------

    my $db2 = Database::Foo->new(
        dsn      => 'dbi:Pg:dbname=mydb;host=db.example.com',
        username => 'myuser',
        password => 's3cret',
    );

    # 8. Schema introspection -----------------------------------------

    my $cols   = $db->columns();  # ['entry', 'name', 'score', ...]
    my $schema = $db->schema();   # { name => { type=>'TEXT', nullable=>1, ... }, ... }

    # 9. Aggregation (GROUP BY) ----------------------------------------

    # Total revenue and order count per region
    my $summary = $db->aggregate(
        by       => ['region'],
        measures => [
            { col => 'amount', fn => 'sum',   as => 'revenue' },
            {                  fn => 'count', as => 'orders'  },
        ],
    );

    # Grand total across all rows
    my $totals = $db->aggregate(
        measures => [{ col => 'amount', fn => 'sum', as => 'total' }],
    )->[0];

    # Fluent style with HAVING and sort
    my $top = $db->query
        ->group_by('region')
        ->measure('amount', 'sum',   'revenue')
        ->measure(undef,    'count', 'orders')
        ->having(revenue => { '>' => 1000 })
        ->order_by('revenue DESC')
        ->limit(5)
        ->all();

=head1 QUICK START EXAMPLE

If F</var/dat/foo.csv> contains:

    "customer_id","name"
    "plugh","John"
    "xyzzy","Jane"

Create a driver in F<.../Database/foo.pm>:

    package Database::foo;
    use parent 'Database::Abstraction';

    # Regular CSV: no entry column, comma-separated
    sub new {
        my ($class, %args) = @_;
        return $class->SUPER::new(no_entry => 1, sep_char => ',', %args);
    }

Then query it:

    my $foo = Database::foo->new(directory => '/var/dat');

    # Prints "John"
    print 'Customer: ', $foo->name(customer_id => 'plugh'), "\n";

    # Returns { customer_id => 'xyzzy', name => 'Jane' }
    my $row = $foo->fetchrow_hashref(customer_id => 'xyzzy');

=head1 FILE FORMATS

The module probes the C<directory> for files in this priority order:

=over 4

=item 1. C<SQLite>

File ending C<.sql>, C<.sqlite>, or C<.sqlite3>.
Requires L<DBD::SQLite>.

=item 2. C<Deep>

DBM::Deep file ending C<.dbm> or C<.deep>.  The entire file is slurped
into a plain Perl hash on open; all in-memory fast-paths apply.
Requires L<DBM::Deep> (loaded lazily).

=item 3. C<PSV>

Pipe-separated file, ending C<.psv>.

=item 4. C<TSV>

Tab-separated file, ending C<.tsv>.

=item 5. C<CSV>

Comma (or custom) separated file, ending C<.csv> or C<.db>; can be
gzipped (C<.csv.gz> or C<.db.gz>).
B<Note:> the default separator is C<!> not C<,> for historical
reasons - pass C<< sep_char => ',' >> for standard CSVs.
Requires L<Text::xSV::Slurp> for the slurp fast-path (loaded lazily).

=item 6. C<Excel> (C<.xls>) and C<XLSX> (C<.xlsx>)

Two separate Excel backends - one per file format:

=over 4

=item B<.xls> - old binary format, opened via L<DBD::Excel> (which uses
L<Spreadsheet::ParseExcel> internally).  All queries go through DBI/SQL;
no in-memory slurp path.  C<max_slurp_size> has no effect.

=item B<.xlsx> - modern OOXML format, parsed directly via
L<Spreadsheet::ParseXLSX> and slurped into an in-memory hash (keyed mode)
or array (C<no_entry> mode).  No DBI handle is created; all queries use
the in-memory fast-path.  Complex criteria (operator hashes, C<-or>/C<-and>)
will fall through to the SQL path and croak - use simple scalar criteria.

=back

For both formats, each worksheet is a separate logical table; the active
worksheet is determined by the class-derived table name (or the C<table>
constructor parameter).  Both modules are loaded lazily.

=item 7. C<XML>

File ending C<.xml>.
Requires L<XML::Simple> for the slurp fast-path (loaded lazily).

=item 8. C<JSON>

File ending C<.json>, slurped into memory via L<JSON::MaybeXS> (loaded
lazily).

The file may contain either a JSON array of row objects:

    [
      { "entry": "key1", "col": "val1" },
      { "entry": "key2", "col": "val2" }
    ]

or a JSON object whose keys are the primary-key values:

    {
      "key1": { "col": "val1" },
      "key2": { "col": "val2" }
    }

In the object form, each key is injected into its row hash under the C<id>
column name (default C<entry>), so all normal lookups work identically to
the array form.

A zero-byte or whitespace-only file is treated as empty - all query methods
return 0 / C<undef> / C<[]> without throwing.
Requires L<JSON::MaybeXS> (loaded lazily).

=item 9. C<BerkeleyDB>

Binary key-value file ending C<.db>.

=item 10. C<HTML>

HTML page fetched via a C<url>.  Pass C<url => 'https://...'> instead of
C<directory>; the module fetches the page with L<LWP::UserAgent::Cached>, parses all
C<< <table> >> elements with L<HTML::TableExtract>, and slurps the first (or
C<html_table_index>-selected) table into memory.  The first row of the table
is treated as column headers.  Both modules are loaded lazily and are not
required for other backends.

=back

Pass C<dsn> to bypass file detection entirely and connect via any DBI driver.
Pass C<url> to fetch and slurp data from a remote source without a local
directory.  When the URL returns C<Content-Type: application/json> or the URL
path ends in C<.json>, the response is parsed as JSON (see item 8 above).
Otherwise the response is parsed as an HTML page (item 10).

Example - fetching CPAN Testers results:

    package Database::cpantesters;
    use parent 'Database::Abstraction';

    my $db = Database::cpantesters->new(
        url      => 'https://www.cpantesters.org/show/Database-Abstraction.json',
        no_entry => 1,
    );
    my $passes = $db->selectall_arrayref(grade => 'PASS');

=head1 QUERY CRITERIA

All select methods (C<selectall_arrayref>, C<selectall_array>,
C<fetchrow_hashref>, C<count>) accept the same criteria syntax.

=head2 Plain value

    status => 'active'          # status = 'active'
    name   => undef             # name IS NULL

Values containing C<%> or C<_> are matched with C<LIKE>:

    name => 'A%'                # name LIKE 'A%'

=head2 Comparison operator hashref

    score => { '>'  => 90  }   # score > 90
    score => { '<'  => 50  }   # score < 50
    score => { '>=' => 80  }   # score >= 80
    score => { '<=' => 100 }   # score <= 100
    score => { '!=' => 0   }   # score != 0

Multiple operators on one column are ANDed:

    score => { '>' => 60, '<' => 90 }   # 60 < score < 90

=head2 Pattern matching

    name => { -like     => 'A%'  }   # name LIKE 'A%'
    name => { -not_like => 'Z%'  }   # name NOT LIKE 'Z%'

=head2 Set membership

    name => { -in     => ['Alice', 'Bob'] }   # name IN (...)
    name => { -not_in => ['Alice', 'Bob'] }   # name NOT IN (...)

=head2 Range

    score => { -between => [60, 90] }   # score BETWEEN 60 AND 90

=head2 Logical groupings

C<-or> and C<-and> take an arrayref of condition hashrefs:

    -or => [
        { status => 'active'        },
        { score  => { '>' => 95 }   },
    ]

    -and => [
        { status => 'active'        },
        { score  => { '>=' => 80 }  },
    ]

=head2 Joins

Any select method accepts a C<join> key with a hashref (or arrayref of
hashrefs) describing the join:

    join => {
        table => 'dept',
        on    => 'employees.dept_id = dept.id',
        type  => 'LEFT',    # INNER (default) | LEFT | RIGHT | FULL | CROSS
    }

    # Multiple joins
    join => [
        { table => 'dept',    on => 'e.dept_id   = dept.id'   },
        { table => 'country', on => 'e.country_id = country.id' },
    ]

=head1 SUBROUTINES/METHODS

=head2 init

Set class-level defaults shared by all instances.

    Database::Abstraction::init(directory => '../data');

Accepts the same parameters as L</new>.  Returns a reference to the
current defaults hash, so you can read them back:

    my $defaults = Database::Abstraction::init();
    print $defaults->{'directory'}, "\n";

=cut

=head2 import

The module can be initialised by the C<use> directive.

    use Database::Abstraction 'directory' => '/etc/data';

or

    use Database::Abstraction { 'directory' => '/etc/data' };

=cut

=head2 new

Create an object pointing to a read-only database.

Accepts arguments as a hash, a hashref, or - as a shortcut - a single bare
string which is taken to be C<directory>.

=head3 Connection parameters

=over 4

=item * C<directory>

Directory containing the data files.  The module probes this directory for
files named after the subclass (see L</FILE FORMATS>).  Required unless
C<dsn> is given.

=item * C<dsn>

A DBI data-source string (e.g. C<dbi:SQLite:dbname=/path/to/db> or
C<dbi:Pg:dbname=mydb;host=db.example.com>).  When present, file detection
is skipped entirely and the DSN is used directly.  The SQL dialect is
inferred from the DSN prefix (C<sqlite>, C<postgres>, C<mysql>).

=item * C<username>

Database username.  Used only with C<dsn>; ignored for file-based backends.

=item * C<password>

Database password.  Used only with C<dsn>; ignored for file-based backends.

=item * C<dbname>

Override the filename stem searched in C<directory> (default: the table
name derived from the class name).

=item * C<table>

Override the table (or worksheet) name used in SQL queries for this object.
Default is the class-name-derived table name (e.g. C<Database::Foo> =>
C<foo>).  Particularly useful for Excel workbooks where a single F<.xlsx>
file contains multiple worksheets: pass C<< table => 'Summary' >> to query
the C<Summary> worksheet without creating a dedicated subclass.  Also works
with SQLite/DSN connections to select a table other than the class-derived
default.  The filename stem (C<dbname>) continues to fall back to the class
name, so the correct file is opened regardless of this override.  The value
is validated against C<$SAFE_QUALIFIED> at construction time.

=item * C<filename>

Override the full filename (relative to C<directory>).  Takes precedence
over C<dbname>.

=item * C<host>

Remote hostname (or C<user@host>) from which to fetch the data file(s) via
SSH/SCP.  When present, each candidate filename is fetched with
L<File::Slurp::Remote> into a local temporary directory; the existing
extension-based file-type detection then runs against that directory.
C<directory> is treated as the remote path (no local canonicalization is
applied).  Using C<filename> together with C<host> avoids probing multiple
extensions and is therefore more efficient.  L<File::Slurp::Remote> must be
installed; it is loaded lazily (only when C<host> is given).

=item * C<url>

A URL (C<http://> or C<https://>) pointing to an HTML page that contains one
or more C<< <table> >> elements.  When present, C<directory> is not required.
The first row of the selected table is used as column headers.
Requires L<LWP::UserAgent::Cached> and L<HTML::TableExtract> (both loaded lazily).

=item * C<database>

A Redis connection URL in the form C<redis://[password@]host[:port][/db_index]>.
When present, C<directory>, C<dsn>, and C<url> are not required.  Rows are
expected to be stored as Redis Hashes at keys of the form
C<tablename:entry_value>; fields of each Hash become column values.  The
module slurps all matching keys via C<KEYS tablename:*> + C<HGETALL> into the
same in-memory structure used by the CSV and DBM::Deep backends, so all
existing fast-paths (C<selectall_arrayref>, C<selectall_array>,
C<fetchrow_hashref>, C<count>, C<columns>, C<schema>, AUTOLOAD) work without
modification.  C<no_entry> mode is supported.

L<Redis::Fast> is tried first (XS, faster); if unavailable, L<Redis>
(pure-Perl) is used as a fallback.  Both are loaded lazily - neither is
required when using file-based or DSN-based backends.  The URL scheme is
validated at construction time (C<redis://> only; all other schemes croak).

Use L</select> to switch the active Redis database on an already-open
connection.

=back

=head3 Behaviour parameters

=over 4

=item * C<no_entry>

Set to C<1> when the table has no key column (standard CSVs, for example).
Default is C<0> (keyed on C<entry>).

=item * C<id>

Name of the key column.  Default is C<entry>.

=item * C<sep_char>

Field separator for CSV/PSV files.
Default is C<!> - pass C<< sep_char => ',' >>
for standard comma-separated files.

=item * C<max_slurp_size>

Files smaller than this (in bytes) are loaded entirely into memory for fast
lookups.  Default is 16 KB.  Set to C<0> to force SQL mode for all sizes.

=item * C<no_fixate>

Set to C<1> to return mutable arrays.  Default is C<0> (arrays are made
read-only via L<Data::Reuse>).

=item * C<auto_load>

Set to C<0> to disable the AUTOLOAD column shortcut.  Default is C<1>
(enabled).

=item * C<html_table_index>

Zero-based index of the HTML C<< <table> >> to extract when the C<url>
backend is used.  Default is C<0> (the first table on the page).

=item * C<infer_types>

Set to C<1> to enable heuristic type inference in C<schema()> for
slurp-backed sources (CSV, JSON, XLSX, XML, HTML URL, DBM::Deep).
Default is C<0> (off; all columns reported as C<TEXT> for backward
compatibility).

When enabled, C<schema()> scans the first 100 rows and promotes column
types: all-integer values => C<INTEGER>; all-floating-point values =>
C<REAL>; all ISO-8601 timestamps => C<TIMESTAMP>; all ISO-8601 dates =>
C<DATE>; otherwise C<TEXT>.  C<NULL> (C<undef> or empty string) values
are skipped during the scan.

=item * C<base_criteria>

A hashref of column-value pairs that is ANDed into B<every> SELECT
automatically.  Callers do not need to repeat these criteria on each
call.  Common 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

The hashref is shallow-copied at construction time so later mutations to
the original do not affect the object.  Column names are validated
against the same identifier rules as the C<id> parameter.  C<-or> and
C<-and> groupings are allowed as keys.

B<SQL path>: the criteria are merged into the C<WHERE> clause before
the caller-supplied criteria (caller values win on key collision).

B<Slurp/in-memory path>: the criteria are applied as an additional
filter.  The C<fetchrow_hashref> entry-key fast path checks
C<base_criteria> against the returned row before returning it.

B<Query builder>: C<base_criteria> are merged into the C<WHERE> clause
generated by C<_build_sql>, so C<< $db->query->where(...)->all() >>
also respects them.

=back

=head3 Caching and logging

=over 4

=item * C<cache>

A L<CHI>-compatible cache object.  When set, query results are stored and
retrieved from the cache.

=item * C<cache_duration> / C<expires_in>

TTL for cached results.  Default is C<'1 hour'>.  C<expires_in> is a
synonym for compatibility with L<CHI>.

=item * C<logger>

An object that understands C<warn()> and C<trace()> (e.g.
L<Log::Log4perl>, L<Log::Any>), a code reference, or a filename.

=item * C<config_file>

Path to a YAML, XML, or INI configuration file whose keys are merged into
the constructor arguments.  Loaded via L<Object::Configure>.

=back

=head3 Notes

=over 4

=item *

If no arguments are set, class-level defaults set via C<init()> or C<use>
are used.

=item *

Slurp mode assumes the key column (C<entry>) is unique.  If it is not,
searches will be incomplete - disable slurp mode by setting
C<< max_slurp_size => 0 >>.

=item *

Passing an existing object as C<$class> clones it, merging any new
arguments.

=back

=cut

=head2  set_logger

Sets the class, code reference, or file that will be used for logging.

=cut

=head2  select

Select a Redis database by number (0-15).  Clears the in-memory data cache so
the next query re-slurps from the newly selected database.  Returns C<$self>
for chaining.  Croaks if the object is not backed by a Redis connection.

    $db->select(3);          # switch to Redis DB 3
    my $count = $db->count;  # queries the new DB

=cut