/ 
Database-BI-0.012.1
/ Database::BI::Model::DataSource

NAME

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

VERSION

0.012.0

SYNOPSIS

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};
}

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";
}

Find out which column is the primary key:

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

Open a SQLite file (.sql extension):

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

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
);

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
);

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,
);

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;

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
);

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;

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;
    }
}

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;

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: $@";
}

DESCRIPTION

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

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. DataSource generates an ephemeral subclass at construction time so callers never interact with Database::Abstraction directly. To swap the backend for Database::Join in Phase 2, only the open_table helper in Database::BI needs to change; the controller and DataSource are untouched.

_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 Database::Abstraction defaults corrupt every result: sep_char defaults to '!' (turning a comma-separated file into a single-field table) and id defaults to 'entry' (causing every row to be discarded when no entry column exists).

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

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

ENCODING

What is safe to put in cell values

Cell values (the actual data inside rows) are passed through as Perl character strings exactly as 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).

What must stay ASCII

The following structural items must contain only ASCII characters:

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 (_) by the sanitization step in new().

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

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

host argument

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

URL-backed tables

URL-backed tables (the url => constructor path) are fetched with LWP::UserAgent::Cached. If the remote server declares a charset in its HTTP headers or in an HTML meta tag, 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.

File-backed tables (CSV / TSV / PSV)

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

CONSTRUCTOR

new

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

API SPECIFICATION

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
}

internal_table selects a named table inside a multi-table SQLite file. When omitted, 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 error_table_not_found.

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

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

When file_ext is provided together with 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 csv, tsv, psv, xlsx, xls, sql, sqlite, sqlite3, db, 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.

DOMAIN CONSTRAINTS

directory

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

Valid partition:   any existing directory path
Invalid partition: non-existent path, regular file path, empty string ""
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 _. 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)

OUTPUT

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

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

ACCESSORS

table_name

Returns the (lowercased) table name this instance was opened against.

API SPECIFICATION

INPUT

None.

OUTPUT

Returns a SCALAR string.

MESSAGES

None.

sqlite_tables

Returns an arrayref of internal table names when the backing file is a multi-table SQLite database, or undef for single-table and non-SQLite sources. Set during _init_backend after probing sqlite_master.

When the return value is a reference to a list with more than one element, the caller should redirect the user to the schema browser (/tables) so they can pick which table to open, rather than silently opening the first one.

Example:

my $tbls = $source->sqlite_tables;
if ($tbls && @{$tbls} > 1) {
    # multiple tables -- let the user choose
    print "Tables: ", join(', ', @{$tbls}), "\n";
}

API SPECIFICATION

INPUT

None.

OUTPUT

ARRAYREF of SCALAR    # ['customer', 'product', 'sales'] for a 3-table SQLite file
undef                 # for CSV, TSV, PSV, XML, URL-backed, and single-table SQLite

The arrayref contains the names in the order returned by sqlite_master (alphabetical). It is set once during construction and never changes.

MESSAGES

None.

columns

Returns an arrayref of column names in file order, or undef when no order is available.

CSV / TSV / PSV / XLSX

Column names come from the file header (first row). They are returned in the order they appear in the file. For header-less files (detected automatically) the synthesized names (Date, Amount, Description, etc.) are returned in the same synthesized order.

SQLite / XML

Falls back to the underlying Database::Abstraction object's columns() method. This is useful when a DataSource is passed directly to Database::Join as a component database.

URL-backed tables

Always returns undef. Database::Abstraction fetches URL data lazily, so calling _db->columns() here would trigger a live network request even when the data came from the CHI cache. The controller derives column order from the data records instead (alphabetical fallback).

Redis-backed tables

Delegates to the underlying Database::Abstraction columns(), which triggers a lazy open of the Redis connection on first call and returns the column names derived from the first stored row (alphabetical order, includes entry). This is required so that Database::Join can validate the join column at construction time.

Always check the return value before dereferencing:

my $cols = $source->columns;
if ($cols) {
    print join(', ', @{$cols}), "\n";
} else {
    # URL-backed tables: derive columns from the first data row
}

API SPECIFICATION

INPUT

None.

OUTPUT

ARRAYREF of SCALAR    # ['id', 'name', 'price', ...] -- original header order
undef                 # SQLite, XML, URL-backed tables

MESSAGES

None.

dbi_source

Returns a hashref { dbh => $dbh, table => $name } for SQLite-backed instances, and undef for everything else.

Database::Join 0.009.0 probes dbi_source at join-construction time to decide whether it can take the zero-copy ATTACH path (avoiding a row-by-row spill into a temporary SQLite file). Without this method, every /join on a SQLite-backed DataSource uses the spill path regardless of row count.

Returns undef for:

  • 0-byte and header-less pre-parsed files (_file_is_empty, _file_data set).

  • Redis-backed and URL-backed sources (no DBI handle).

  • Any backend that does not itself implement dbi_source.

API SPECIFICATION

INPUT

None.

OUTPUT

{ dbh => $dbh_object, table => 'table_name' }   # SQLite-backed
undef                                            # all other backends

MESSAGES

None.

aggregate

Delegates to the inner Database::Abstraction backend's aggregate() method (requires Database::Abstraction >= 0.49).

Returns [] for in-memory backends (headerless CSV, XLSX, empty files) and for URL-backed sources where aggregation is not available.

Accepts the same by, measures, having, sort_by, limit, offset, and WHERE-criteria parameters as Database::Abstraction::aggregate().

API SPECIFICATION

INPUT

by        arrayref    Column names to GROUP BY.
measures  arrayref    Measure descriptors (see Database::Abstraction::aggregate).
having    hashref     Optional HAVING criteria.
sort_by   string      Optional ORDER BY column.
limit     integer     Optional result limit.
offset    integer     Optional result offset.
(any other key)       WHERE column criteria passed through to D::A.

OUTPUT

arrayref of hashrefs   -- one row per group.
[]                     -- in-memory / empty / URL-backed sources.

MESSAGES

None.

id_column

Returns the name of the column used as the primary key and as Database::Abstraction's row-existence anchor.

For file-backed tables this is the first column in the CSV/TSV/PSV header whose name is a valid SQL identifier (letters, digits, underscores only, not starting with a digit) and whose value in the first data row is non-empty. DataSource detects this automatically to avoid the Database::Abstraction default of 'entry', which would silently discard every row in a file that has no entry column.

Returns undef for URL/HTML-table backends (no primary-key concept applies).

API SPECIFICATION

INPUT

None.

OUTPUT

SCALAR string    # e.g. 'id', 'date', 'sku' -- the first safe-identifier column
undef            # URL-backed (HTML table import) sources

MESSAGES

None.

source_url

Returns the source URL for URL/HTML-table-backed instances. Returns undef for all file-backed instances (local files and SFTP-fetched remote files).

Use this to distinguish a URL-backed DataSource from a file-backed one when you need to decide whether to show a "re-fetch" button to the user.

API SPECIFICATION

INPUT

None.

OUTPUT

SCALAR string    # 'https://example.com/data.html' -- the original URL
undef            # file-backed (local or SFTP) sources

MESSAGES

None.

updated

Returns the Unix epoch timestamp (mtime) of the backing data file, or undef for URL-backed tables and for sources whose file cannot be stat()-ed.

Database::Join calls updated() on each component database to detect whether its SQLite spill cache is stale. When a source file changes on disk, the mtime increases, the join detects the change, and re-runs the join rather than serving stale cached data.

API SPECIFICATION

INPUT

None.

OUTPUT

SCALAR integer   # Unix epoch seconds, e.g. 1728601234
undef            # URL-backed tables, or file cannot be stat()-ed

MESSAGES

None.

METHODS

fetch_all

Returns every record in the table as an arrayref of hashrefs (one hashref per row; keys are the column names).

This is the primary data-retrieval method. It is equivalent to calling selectall_arrayref with no arguments and normalising the result to an arrayref. Prefer selectall_arrayref when passing to Database::Join (it is the method that Database::Join calls internally).

Filtering (eq, contains, gt, lt, etc.) is applied at the controller layer by Dashboard::_apply_filter_spec after fetch_all returns. DataSource itself is filter-unaware.

Three fast-path shortcuts are checked before touching the DBI backend, in this order:

  1. _file_is_empty is set (0-byte or blank-first-line file) -- returns [] immediately, no DBI connection.

  2. _file_data is set (pre-parsed header-less CSV, XLSX, or BerkeleyDB data) -- returns the in-memory arrayref directly.

  3. A matching CHI cache entry exists -- returns the cached arrayref without calling Database::Abstraction.

API SPECIFICATION

INPUT

None. Filtering is applied at the controller layer, not inside DataSource.

OUTPUT

ARRAYREF of HASHREF   # one hashref per row; keys are column names
[]                    # empty arrayref when the file is empty or has no data rows

Croaks (via croak) when the backend raises an exception. Emits a warning via carp (non-fatal) when the result set is empty, so the caller can distinguish "opened successfully, no data" from a silent failure.

MESSAGES

error_fetch_failed     -- backend threw an exception during retrieval
warn_empty_result      -- query succeeded but returned zero records
warn_data_normalised   -- backend returned a hashref; converted to arrayref

selectall_arrayref

Database::Abstraction-compatible alias that allows a DataSource object to be passed directly to Database::Join as a component database.

When called with no arguments (the normal path for a full-table scan), results are served from the CHI cache when available. When called with arguments (e.g. limit => N, offset => M for server-side pagination), the cache is bypassed -- narrowed results must not pollute the full-table cache entry.

Three fast-path shortcuts are checked before touching the DBI backend:

  1. _file_is_empty is set -- returns [] immediately.

  2. _file_data is set (pre-parsed headerless CSV / XLSX / BerkeleyDB) -- returns the in-memory rows directly.

  3. A matching CHI cache entry exists for the full-table scan -- returns the cached arrayref.

API SPECIFICATION

INPUT

Optional flat key/value list passed through to Database::Abstraction:

limit  => N    # return at most N rows (used by pagination)
offset => M    # skip the first M rows (used by pagination)

When no arguments are given, the full table is returned (and cached).

OUTPUT

ARRAYREF of HASHREF   # one hashref per row; keys are column names
[]                    # empty arrayref when the file is empty or no rows match

MESSAGES

None. Errors from the DBI backend propagate as uncaught exceptions; wrap calls in eval if you need to recover.

count

Returns the total number of rows in the table.

For pre-parsed sources (header-less CSV, XLSX, BerkeleyDB) the count comes from the already-loaded in-memory slice and is instant (no I/O). For empty-file sentinels it returns 0 immediately. For all DBI-backed sources (SQLite, large CSV via DBD::CSV) it delegates to Database::Abstraction::count(), which issues a SELECT COUNT(*) FROM <table> query against the file.

Warning: do not call count() on a very large SQLite file if you only need to know whether a next page exists. The pagination controller uses the N+1 trick (fetch per_page + 1 rows via limit/offset) specifically to avoid a COUNT(*) on multi-gigabyte databases, which can block the server for minutes.

API SPECIFICATION

INPUT

None.

OUTPUT

SCALAR non-negative integer   # total row count, 0 for empty files

MESSAGES

None.

COMMON PITFALLS

These are the most common mistakes when using DataSource.

SQLite databases may use .sql, .sqlite, or .sqlite3 as their file extension

DataSource recognises .sql, .sqlite, and .sqlite3 as SQLite database files. It does not recognise .db3. If you have a file called inventory.db3, rename it to inventory.sql before passing it to DataSource.

The table name is always lowercased

The constructor lowercases the table argument before using it. Passing table => 'Sales' and table => 'sales' both look for sales.csv (or sales.sql, etc.). The matching is case-insensitive on the table name but case-sensitive on the directory path.

CSV files with the wrong separator appear as one giant field per row

Database::Abstraction defaults to ! (exclamation mark) as its field separator. A standard comma-separated CSV file will look like one big field per row (for example, 1,Widget A,North,100) because the library never sees the commas as separators. DataSource fixes this automatically by reading the first line of the file and detecting the actual separator. If you bypass DataSource and call Database::Abstraction directly, you must pass sep_char => ',' yourself.

A table with no "entry" column returns zero rows (without DataSource)

Database::Abstraction uses entry as its default primary-key column name. When slurping a CSV, it discards any row where $row->{entry} is undefined. Because most CSV files do not have an entry column, all rows are silently discarded. DataSource prevents this by reading the actual first column name from the CSV header and passing it as id, and also by setting no_entry => 1 so all rows are kept as an ordered array.

columns() returns undef for SQLite and XML files

columns() only returns an arrayref for file formats where the header order is visible before data is read (CSV, TSV and PSV). For SQLite and XML files it returns undef. Always check: if ($source->columns) { ... }. The controller falls back to putting id_column first and then sorting the rest alphabetically when columns() is undef.

XML elements named "name", "id", or "key" cause parse failures

XML::Simple (used by Database::Abstraction for XML files) automatically turns a child element called name, id, or key into a hash key instead of keeping it as an array element. This breaks the expected data structure. Use different element names in your XML: for example, <sku>, <label>, or <code> instead of <id> and <name>.

<!-- WRONG: these element names trigger XMLin key-folding -->
<items>
  <item><id>1</id><name>Widget</name></item>
</items>

<!-- CORRECT: use neutral element names -->
<items>
  <item><sku>1</sku><label>Widget</label></item>
</items>
fetch_all returns an empty arrayref, not undef, for an empty table

When a table exists but contains no data rows, fetch_all returns [] (an empty arrayref), not undef. Check with scalar @{$records}, not with defined $records or $records.

0-byte CSV/TSV/PSV files bypass Database::Abstraction entirely

When _detect_file_info opens a CSV, TSV or PSV file and the first readline returns undef (the file is 0 bytes), it returns a { _file_is_empty => 1 } sentinel instead of the normal { id, sep_char, columns, file_size } hashref. _init_backend detects this sentinel and skips Database::Abstraction construction; fetch_all returns [] immediately. No DBI connection is created for 0-byte files. If you mock or spy on DBI handles and open a 0-byte CSV, the mock will never fire - this is expected behaviour, not a mock misconfiguration.

Remote files require Net::SFTP::Foreign; one connection per open

When host is set, DataSource makes one SFTP connection per new() call. It tries the extension list in order (the file_ext hint first, if provided, then the full standard-extension list) and downloads the first file it finds. If no file is found under any tried extension, new() croaks error_backend_init. Connections are not pooled or reused between DataSource instances.

If the remote file has a non-standard extension (e.g. .log, .dat), the content is sniffed: SQLite magic bytes map to .db, an XML preamble to .xml, a tab-delimited first line to .tsv, a pipe-delimited line to .psv, and everything else to .csv. The temp file is then renamed to the detected extension before further processing.

Multi-table SQLite: opening without internal_table silently picks the first table

When a SQLite file contains more than one user-defined table and you call new() without internal_table, DataSource auto-selects the first table whose name matches the filename stem; if there is no match, it picks whatever sqlite_master lists first. This is silent -- you get data from a table you may not have intended to open.

Always check sqlite_tables() after construction if you are dealing with a SQLite file that might have multiple tables:

my $tbl_list = $source->sqlite_tables;
if ($tbl_list && @{$tbl_list} > 1) {
    # warn the user or redirect to the schema browser
}

Database::Abstraction does not use its DBI/SQLite backend for files with a .db extension -- it falls through to the CSV reader, which tries to read a SQLite binary file as text and fails with a parse error.

When DataSource detects a multi-table SQLite .db file, it creates a temporary symlink named <table>.sql (not .db) pointing to the original .db file. SQLite follows symlinks transparently, so the data is read correctly. If you ever audit temporary directories and see *.sql symlinks pointing at *.db files, this is expected and intentional.

DBD::CSV silently lowercases column names for files larger than 16 KB

Database::Abstraction uses Text::xSV::Slurp for files up to 16 KB and DBD::CSV for larger ones. The DBD::CSV path lowercases column names and replaces spaces with underscores (Account Number becomes account_number), which causes a "disallowed key" error at render time when the template iterates the original column names. DataSource avoids this by always passing max_slurp_size => -s $path to Database::Abstraction, forcing the slurp path regardless of file size. If you call Database::Abstraction directly, you must pass this option yourself for any file whose column names contain spaces or mixed case.

LIMITATIONS

  • Only read operations are supported. Write-back is not in scope.

  • One DataSource instance corresponds to exactly one table. Multi-table joins are now delegated to Database::Join (see Database::Join), which accepts DataSource objects directly as component databases via the selectall_arrayref and columns methods this class exposes.

  • The ephemeral backend class is generated into a package namespace (Database::BI::_DB::*) that persists for the lifetime of the process. Instantiating two DataSource objects for the same table name reuses the same ephemeral class.

  • The i18n object, if supplied, must implement maketext($key, @args) compatible with Locale::Maketext.

CONFIGURATION AND ENVIRONMENT

No environment variables are read. All configuration is passed through the constructor.

DEPENDENCIES

Carp, Readonly, Scalar::Util, Params::Validate::Strict, Params::Get, Database::Abstraction.

Optional (loaded lazily):

Net::SFTP::Foreign -- required for remote file access (host => ...). Spreadsheet::ParseXLSX -- required for opening .xlsx files.

INCOMPATIBILITIES

None known.

BUGS AND LIMITATIONS

Please report bugs via https://github.com/nigelhorne/Database-BI/issues.

AUTHOR

Nigel Horne <njh@nigelhorne.com>

FORMAL SPECIFICATION

The following specifications use a Z-calculus notation. undef represents the undefined Perl value. seq T means an ordered sequence of values of type T. \A...\z denotes a Perl regular expression match. |S| means the cardinality (size) of set or sequence S.

new

new == [directory : PATH; table : NAME;
        host? : HOST_STRING; file_ext? : EXT_STRING;
        internal_table? : STRING;
        cache? : CHI_OBJECT; cache_ttl_url? : TTL_STRING;
        i18n? : I18N_OBJECT]
       pre  (host = undef => is_dir directory)
            /\ (host /= undef => host =~ /\A(?:[a-zA-Z0-9][a-zA-Z0-9._-]*\@)?[a-zA-Z0-9][a-zA-Z0-9._-]*\z/)
            /\ table /= ""
            /\ table =~ /[^\/\\\x00]/
       post result.class = DataSource
            /\ (host = undef => result._db.class = Database::Abstraction | result._file_data /= undef | result._file_is_empty)
            /\ (host /= undef => result._remote_tmpdir /= undef)
            /\ (internal_table /= undef => internal_table in result._sqlite_tables)

table_name

table_name == lambda self . self._table

pre  self._table /= undef
post result : STRING /\ result =~ /\A[A-Za-z_][A-Za-z0-9_]*\z/

sqlite_tables

sqlite_tables == lambda self . self._sqlite_tables

pre  true
post (result = undef /\ self._url /= undef)
     \/ (result = undef /\ source is not SQLite)
     \/ (result : seq STRING /\ forall t in result . t =~ /\A[A-Za-z_][A-Za-z0-9_]*\z/)

columns

columns == lambda self .
  if defined self._columns then self._columns
  else if defined self._url then undef
  else if defined self._db  then self._db.columns()
  else undef

pre  true
post (result : seq STRING /\ |result| >= 1)
     \/ (result = undef)

id_column

id_column == lambda self . self._id_col

pre  true
post (result : STRING /\ result =~ /\A[a-zA-Z_][a-zA-Z0-9_]*\z/)
     \/ (result = undef)

source_url

source_url == lambda self . self._url

pre  true
post (result : STRING /\ result =~ /\Ahttps?:\/\//)
     \/ (result = undef)

updated

updated == lambda self .
  if defined self._db /\ can(self._db, 'updated')  then self._db.updated()
  else if defined self._file_path /\ -f self._file_path then (stat self._file_path)[9]
  else undef

pre  true
post (result : N /\ result > 0) \/ (result = undef)

fetch_all

fetch_all == lambda self .
  if self._file_is_empty                     then []
  else if defined self._file_data            then self._file_data
  else if cache_hit(self)                    then cache_get(self)
  else
    let rows = self._db.selectall_hashref() in
    normalise(rows)

pre  self._file_is_empty \/ defined self._file_data \/ defined self._db
post result : seq HASHREF
     /\ |result| >= 0

selectall_arrayref

selectall_arrayref == lambda self args .
  if self._file_is_empty                         then []
  else if defined self._file_data /\ args = ()   then self._file_data
  else if cache_hit(self) /\ args = ()           then cache_get(self)
  else self._db.selectall_arrayref(args)

pre  self._file_is_empty \/ defined self._file_data \/ defined self._db
post result : seq HASHREF /\ |result| >= 0

count

count == lambda self .
  if self._file_is_empty     then 0
  else if defined self._file_data then |self._file_data|
  else self._db.count()

pre  self._file_is_empty \/ defined self._file_data \/ defined self._db
post result : N /\ result >= 0

STATE DIAGRAM

The lifecycle of a single DataSource object from creation to destruction. Each state is stable until the trigger event listed to its right fires.

  +------------------+
  |     [START]      |  -- new() called
  +------------------+
           |
           v
  +------------------+  _detect_file_info + _init_backend run
  |   INITIALISING   |  (SFTP download, BerkeleyDB probe, SQLite
  |                  |   sqlite_master probe, CSV header parse,
  |                  |   XLSX parse, or URL fetch)
  +------------------+
        /     \
       /       \
      v         v
+----------+  +-----------+
|  ERROR   |  |   READY   |
| (croaks) |  |           |
+----------+  +-----------+
               |         |
               |         |
  fetch_all()  |         |  selectall_arrayref()
  called       |         |  called
               v         v
       +---------------+
       |   FETCHING    |  DBI query or pre-loaded data returned;
       |               |  result stored in CHI cache (if configured)
       +---------------+
               |
               v
       +---------------+
       | DATA RETURNED |  arrayref of hashrefs delivered to caller
       +---------------+
               |
               | (next call to fetch_all or selectall_arrayref)
               v
       +---------------+
       | CACHE CHECK   |  CHI cache hit? -> DATA RETURNED immediately
       |               |  CHI cache miss? -> FETCHING
       +---------------+

  Special cases branching from INITIALISING:

  1. File is 0 bytes or blank first line
     -> _file_is_empty flag set -> READY (fetch_all returns [] instantly)

  2. Header-less CSV / XLSX / BerkeleyDB
     -> _file_data pre-loaded -> READY (fetch_all returns pre-loaded rows)

  3. Multi-table SQLite without internal_table
     -> _sqlite_tables set -> READY
     -> caller inspects sqlite_tables() and may redirect to /tables
        before calling fetch_all (which would open the auto-selected table)

  4. Multi-table SQLite WITH internal_table
     -> symlink created for named table -> READY -> fetch_all opens that table

  5. SFTP remote file
     -> Net::SFTP::Foreign::get() -> local tmpdir -> normal init -> READY
     -> tmpdir kept alive via $self->{_remote_tmpdir} until DESTROY

  Object lifetime ends when $self goes out of scope:

  +---------------+
  |   DESTROY     |  File::Temp tmpdirs auto-cleaned (symlinks removed,
  |               |  SFTP tmpdir removed)
  +---------------+

  Valid state transitions summary:

  [START]          -> INITIALISING     (new() invoked)
  INITIALISING     -> ERROR            (croak on bad args, SFTP failure,
                                        no safe id, no SQLite tables, etc.)
  INITIALISING     -> READY            (all checks passed)
  READY            -> FETCHING         (fetch_all / selectall_arrayref, cache miss)
  READY            -> DATA RETURNED    (fetch_all, _file_is_empty or _file_data fast-path)
  READY            -> CACHE CHECK      (fetch_all / selectall_arrayref, cache configured)
  CACHE CHECK      -> DATA RETURNED    (cache hit)
  CACHE CHECK      -> FETCHING         (cache miss)
  FETCHING         -> DATA RETURNED    (DBI query completed)
  DATA RETURNED    -> CACHE CHECK      (next call, cache configured)
  DATA RETURNED    -> FETCHING         (next call, no cache)
  READY / DATA RETURNED -> DESTROY     ($self goes out of scope)

LICENCE AND COPYRIGHT

Copyright 2026 Nigel Horne.

Usage is subject to the GPL2 licence terms. If you use it, please let me know.