File Coverage

=head1 NAME

Database::BI::Model::DataSource - Table-agnostic adapter around Database::Abstraction

=head1 VERSION

0.011.0

=head1 SYNOPSIS

B<Read all rows from a CSV file:>

    use Database::BI::Model::DataSource;

    my $source = Database::BI::Model::DataSource->new(
        directory => '/path/to/data',
        table     => 'sales',           # looks for data/sales.csv, tsv, .psv, .sql, .xml, etc.
    );

    my $records = $source->fetch_all;   # arrayref of hashrefs -- one hashref per row

    for my $row (@{$records}) {
        printf "Product: %s, Amount: %s\n", $row->{product}, $row->{amount};
    }

B<Get column names in the original file order (CSV/PSV/TSV only):>

    my $cols = $source->columns;        # returns undef for SQLite and XML
    if ($cols) {
        print join(', ', @{$cols}), "\n";
    }

B<Find out which column is the primary key:>

    print "Primary key column: ", $source->id_column, "\n";

B<Open a SQLite file (.sql extension):>

    my $source = Database::BI::Model::DataSource->new(
        directory => '/var/data',
        table     => 'inventory',       # looks for /var/data/inventory.sql
    );

B<Open a pipe-separated file (.psv extension):>

    my $source = Database::BI::Model::DataSource->new(
        directory => '/var/data',
        table     => 'products',        # looks for /var/data/products.psv
    );

B<Open a tab-separated file (.tsv extension):>

    my $source = Database::BI::Model::DataSource->new(
        directory => '/var/data',
        table     => 'products',        # looks for /var/data/products.tsv
    );

B<Use a custom i18n object to translate error messages:>

    # The i18n object must have a maketext($key, @args) method.
    my $source = Database::BI::Model::DataSource->new(
        directory => '/path/to/data',
        table     => 'sales',
        i18n      => My::I18N::Handle->new,
    );

B<Open a remote HTML table from a URL:>

    # Requires LWP::UserAgent::Cached and HTML::TableExtract.
    # The table is fetched and cached in memory; no file is saved to disk.
    my $source = Database::BI::Model::DataSource->new(
        url => 'https://example.com/data-page.html',
    );
    my $records = $source->fetch_all;

B<Select a specific table when a page has more than one HTML table:>

    my $source = Database::BI::Model::DataSource->new(
        url              => 'https://example.com/page.html',
        html_table_index => 2,   # zero-based: 0 = first table, 2 = third table
    );

B<Open one specific internal table from a multi-table SQLite database:>

    # A SQLite file may contain more than one table.
    # Use internal_table to say which one you want.
    my $source = Database::BI::Model::DataSource->new(
        directory      => '/var/data',
        table          => 'store',          # looks for store.db, store.sql, etc.
        internal_table => 'customer',       # open just the "customer" table inside
    );
    my $records = $source->fetch_all;

B<Check whether a SQLite file has more than one table:>

    my $source = Database::BI::Model::DataSource->new(
        directory => '/var/data',
        table     => 'store',
    );
    my $tbl_list = $source->sqlite_tables;   # undef unless it is a multi-table SQLite
    if ($tbl_list && @{$tbl_list} > 1) {
        # let the user choose which table to open
        for my $name (@{$tbl_list}) {
            printf "  table: %s\n", $name;
        }
    }

B<Open a remote file over SSH (SFTP):>

    # Requires Net::SFTP::Foreign.
    my $source = Database::BI::Model::DataSource->new(
        directory => '/remote/data',
        table     => 'report',
        host      => 'deploy@dbserver.example.com',
        file_ext  => 'log',         # hint: try report.log first, then other exts
    );
    my $records = $source->fetch_all;

B<Handle errors gracefully:>

    my $source = eval {
        Database::BI::Model::DataSource->new(
            directory => $dir,
            table     => $table,
        );
    };
    if ($@) {
        carp "Could not open table: $@";
        # $@ contains a translated message from %MESSAGES
    }

    my $records = eval { $source->fetch_all };
    if ($@) {
        carp "Could not read records: $@";
    }

=head1 DESCRIPTION

C<Database::BI::Model::DataSource> is a thin, table-agnostic adapter that
wraps L<Database::Abstraction> and exposes three accessors (C<fetch_all>,
C<columns>, C<id_column>) used by the controller.

L<Database::Abstraction> is a read-only ORM that discovers data files
(CSV, PSV, TSV, SQLite, XML, etc.) automatically from a directory based on the
calling class name.  C<DataSource> generates an ephemeral subclass at
construction time so callers never interact with L<Database::Abstraction>
directly.  To swap the backend for L<Database::Join> in Phase 2, only the
C<open_table> helper in C<Database::BI> needs to change; the controller and
C<DataSource> are untouched.

C<_detect_file_info> peeks at the first header line of CSV/PSV/TSV files to
extract the correct separator character, the primary-key column name, and
the full ordered column list.  Without this, two silent L<Database::Abstraction>
defaults corrupt every result: C<sep_char> defaults to C<'!'> (turning a
comma-separated file into a single-field table) and C<id> defaults to
C<'entry'> (causing every row to be discarded when no C<entry> column
exists).

Result filtering (C<eq>, C<contains>, C<gt>, etc.) is performed at the
controller layer by C<Dashboard::_apply_filter_spec> after C<fetch_all>
returns.  C<DataSource> itself is filter-unaware.

All user-visible strings and exception messages are keyed through the
C<%MESSAGES> dictionary and routed via C<_msg()>, making every diagnostic
replaceable by an i18n object at instantiation time.

=cut

=head1 ENCODING

=head2 What is safe to put in cell values

Cell values (the actual data inside rows) are passed through as Perl
character strings exactly as C<Database::Abstraction> and the underlying
DBI driver return them.  Full Unicode -- including non-ASCII letters,
accented characters, CJK ideographs, and emoji -- is supported in cell
values, provided the source file is encoded in UTF-8 (or the encoding
declared in its HTTP headers for URL-backed tables).

=head2 What must stay ASCII

The following structural items must contain B<only ASCII characters>:

=over 4

=item C<table> constructor argument

The bare file stem used to look up the file on disk.  Non-ASCII characters
are not valid SQL identifiers and will be silently replaced with underscores
(C<_>) by the sanitization step in C<new()>.

=item Column header names (CSV / TSV / PSV first line)

Column names are used as hash keys and as SQL identifiers inside
C<Database::Abstraction>.  Non-ASCII headers cause C<DBD::CSV> to
fail or produce garbage keys.  If your source file has non-ASCII
column headers, rename them before opening via C<DataSource>.

=item C<host> argument

The hostname or C<user@hostname> string for SFTP access.  Must match
C<[A-Za-z0-9._@-]>; anything outside that character set is rejected by
the host-validation regex and produces C<error_backend_init>.

=back

=head2 URL-backed tables

URL-backed tables (the C<url =E<gt>> constructor path) are fetched with
L<LWP::UserAgent::Cached>.  If the remote server declares a charset in its
HTTP headers or in an HTML C<meta> tag, L<Database::Abstraction> uses it
to decode the response body.  If the charset declaration is absent or wrong,
cell values may arrive as raw bytes rather than character strings, and
multi-byte sequences will look garbled.

=head2 File-backed tables (CSV / TSV / PSV)

For files smaller than 16 KB, L<Text::xSV::Slurp> reads bytes and returns
them without re-encoding; for larger files the L<DBD::CSV> path is used
instead.  In both cases C<DataSource> forces the slurp path (by passing
C<max_slurp_size =E<gt> -s $file>) so that column names with spaces are
preserved as-is.  The caller (the Mojolicious controller) is responsible
for setting the correct C<Content-Type: text/html; charset=UTF-8> response
header.

=cut

=head1 CONSTRUCTOR

=head2 new

Creates and returns a new C<Database::BI::Model::DataSource> instance.

=head3 API SPECIFICATION

=head4 INPUT

        {
            directory      => 'string',   # required; local dir, or remote dir when host is set
            table          => 'string',   # required; bare file stem (no extension)
            host           => 'string',   # optional; "hostname" or "user@hostname" for SFTP access
            file_ext       => 'string',   # optional; extension hint when remote file has non-standard suffix (e.g. "log")
            internal_table => 'string',   # optional; name of a specific internal SQLite table to open
            cache          => 'object',   # optional; CHI cache object for result caching
            cache_ttl_url  => 'string',   # optional; CHI TTL string for URL-backed tables (default "15 min")
            i18n           => 'object',   # optional; must implement maketext($key, @args) for i18n
        }

C<internal_table> selects a named table inside a multi-table SQLite file.
When omitted, C<DataSource> auto-selects the first user table whose name
matches the filename stem; if no match, it picks the first table found.
Supplying a name that does not exist in the file croaks C<error_table_not_found>.

Accepts a flat key/value list, a hashref, or positional arguments via
C<Params::Get>.

When C<host> is provided, C<directory> is treated as a path on the remote
host and is not checked with C<-d> locally.  C<DataSource> downloads the
file via SFTP (L<Net::SFTP::Foreign>) to a process-local temporary directory
and then processes the local copy.  The temporary directory persists for the
lifetime of the C<DataSource> object and is cleaned up automatically on
destruction.

When C<file_ext> is provided together with C<host>, it is used as the first
candidate extension when probing the remote host for the file.  If the
downloaded file does not have a standard extension (one of C<csv>, C<tsv>,
C<psv>, C<xlsx>, C<xls>, C<sql>, C<sqlite>, C<sqlite3>, C<db>, C<xml>),
the file content is sniffed (SQLite magic bytes, XML preamble, or first-line
field separator) and the file is renamed to the correct standard extension
before further processing.

=head4 DOMAIN CONSTRAINTS

=over 4

=item C<directory>

Must satisfy C<-d $directory> (must exist and be a directory).  An empty
string, a non-existent path, or the path to a regular file all produce
C<error_directory_missing>.

  Valid partition:   any existing directory path
  Invalid partition: non-existent path, regular file path, empty string ""

=item C<table>

The bare file stem (no extension).  Characters that are illegal in SQL
identifiers - hyphens, dots, spaces, etc. - are silently replaced with
underscores before the name is used internally.  A stem that starts with a
digit is prefixed with C<_>.  Only a completely empty string croaks.

  Valid partition:   "sales", "_tmp", "report_2024",
                     "Transactions-2026-09-08" (hyphens sanitized to underscores),
                     "my.data" (dot sanitized), "1sales" (prefixed to "_1sales")
  Invalid partition: "" (empty string)
  Boundary values:   "a" (length-1 letter, valid), "_" (length-1 underscore,
                     valid), "" (empty string, croaks error_table_name_invalid)

=back

=head4 OUTPUT

Returns C<$self> (a blessed hashref). Croaks on invalid arguments.

=head3 MESSAGES

  error_directory_required    -- "directory" argument was not supplied
  error_table_required        -- "table" argument was not supplied
  error_directory_missing     -- supplied directory does not exist / is unreadable
  error_table_name_invalid    -- table name contains illegal characters or is empty
  error_no_safe_id            -- no column in the file has a safe SQL identifier name;
                                 rename at least one column header to an alphanumeric name
  error_backend_init          -- backend initialisation failed; sub-cases:
                                   * Net::SFTP::Foreign is not installed
                                   * could not connect to <host> via SFTP
                                   * could not fetch any supported file from <host>:<dir>/<stem>.*
                                   * Database::Abstraction subclass could not be instantiated
  error_no_tables             -- SQLite file opened successfully but contains no user-defined tables
  error_unsupported_db        -- .db file has BerkeleyDB magic bytes but DB_File could not open it
                                 (file likely uses a newer BDB format than the installed libdb supports)
  error_fetch_failed          -- fetch_all raised an exception (wraps the underlying DBI/D::A error)
  error_url_invalid           -- URL passed to the url=> constructor path does not begin
                                 with http:// or https://
  error_url_fetch             -- fetching or parsing the HTML table at a URL failed

=cut