NAME

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

VERSION

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

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.

UTF-8 and Encoding

DataSource passes cell values through as Perl character strings exactly as Database::Abstraction and the underlying DBI driver return them. For CSV, TSV and PSV files smaller than 16 KB, Text::xSV::Slurp is used and bytes are returned without re-encoding; for larger files the DBD::CSV path is used. In both cases the caller (the controller) is responsible for setting the correct Content-Type header.

The table argument and all column names must be ASCII-only identifiers. Full Unicode is supported inside cell values -- the restriction applies only to structural metadata (column headers, table name), not to the data itself.

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 HTML meta tag, Database::Abstraction uses it to decode the response body. If the declaration is absent or wrong, cell values may contain raw bytes rather than character strings.

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

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_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.

columns

Returns an arrayref of column names in file order, or undef when no order is available. For CSV, TSV and PSV files the order comes from the file header. For SQLite and XML, falls back to the underlying Database::Abstraction object's columns() - useful when a DataSource is passed directly to Database::Join as a component database.

id_column

Returns the name of the column used as the primary key / slurp-filter anchor. Returns undef for URL/HTML-table backends (no primary-key concept applies).

source_url

Returns the source URL for URL/HTML-table-backed instances, or undef for file-backed instances.

METHODS

fetch_all

Returns every record in the table as an arrayref of hashrefs.

API SPECIFICATION

INPUT

None. Filtering is performed at the controller layer (Dashboard::_apply_filter_spec) after fetch_all returns, not inside DataSource.

OUTPUT

ARRAYREF of HASHREF   # one hashref per row, keys are column names
                      # returns [] when the table exists but is empty

Croaks if the backend raises an exception. Carps (non-fatal) when the result set is empty so the caller can distinguish "open succeeded, no rows" 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. Passes any criteria through to the underlying backend; the BI viewer always calls it with no arguments.

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.

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

new

new == [directory : PATH; table : NAME;
        host? : HOST_STRING; file_ext? : EXT_STRING;
        cache? : CHI_OBJECT; cache_ttl_url? : TTL_STRING;
        i18n? : I18N_OBJECT]
       pre  (host = undef => is_dir directory)
            /\ (host /= undef => host =~ REMOTE_HOST_RE)
            /\ table /= ""
       post result.class = DataSource
            /\ (host = undef => result._db.class = Database::Abstraction)
            /\ (host /= undef => result._remote_tmpdir /= undef)

table_name

table_name == lambda self . self._table

fetch_all

fetch_all == lambda self .
  let rows = self._db.selectall_hashref() in
  pre  self._db /= undef
  post result : seq HASHREF
       /\ #result >= 0

LICENCE AND COPYRIGHT

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

To install Database::BI, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Database::BI

CPAN shell

perl -MCPAN -e shell
install Database::BI

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	Go to GitHub issues (only if GitHub is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

VERSION

SYNOPSIS

DESCRIPTION

UTF-8 and Encoding

CONSTRUCTOR

new

API SPECIFICATION

INPUT

DOMAIN CONSTRAINTS

OUTPUT

MESSAGES

ACCESSORS

table_name

API SPECIFICATION

INPUT

OUTPUT

MESSAGES

columns

id_column

source_url

METHODS

fetch_all

API SPECIFICATION

INPUT

OUTPUT

MESSAGES

selectall_arrayref

COMMON PITFALLS

LIMITATIONS

CONFIGURATION AND ENVIRONMENT

DEPENDENCIES

INCOMPATIBILITIES

BUGS AND LIMITATIONS

AUTHOR

FORMAL SPECIFICATION

new

table_name

fetch_all

LICENCE AND COPYRIGHT

Module Install Instructions

Keyboard Shortcuts