File Coverage

=head1 NAME

Database::BI - Web-based Business Intelligence viewer for data files

=head1 VERSION

0.012.0

=head1 DESCRIPTION

C<Database::BI> is a self-contained L<Mojolicious> web application that reads
and views arbitrary flat data (CSV, PSV, TSV, SQLite, XML, XLSX, etc.)
and presents them as styled, sortable, reorderable
HTML tables.
It has no persistent database of its own,
it reads your files on every request.

Users navigate to pick a file, open the filesystem browser to navigate anywhere
on disc, drag-and-drop files to upload, join multiple tables in memory, apply
server-side filters, and export results as CSV, SQLite, or JSON.  D3.js
visualisations (line chart, pie chart) are available from the toolbar.

Key features:

=over 4

=item *

B<File picker> -- the home page scans C<data_dir> and shows a card for
every supported file.  Recently opened filesystem files appear in a
"Recently opened" section powered by C<localStorage>.

=item *

B<Filesystem browser> -- C</browse> lets the user navigate the entire
filesystem and open any supported data file, not just files in C<data_dir>.

=item *

B<Column sort and reorder> -- clicking a header sorts the table; headers
are draggable to reorder.  Both settings are persisted in C<localStorage>
by column name and survive page reloads.

=item *

B<Left join> -- the "Merge data" panel on any table view lets the user
join one or more additional tables on a shared key via L<Database::Join>.
Every left row is kept; right-table columns are appended for matching rows.
Multiple joins can be chained.

=item *

B<Quick-filter bar> -- a column/operator/value row below the toolbar lets
the user add filter conditions without opening any panel.  Pressing Enter
applies the filter.  Each active filter chip has an individual remove (x)
button so conditions can be dropped one at a time.  Operators: C<eq>,
C<ne>, C<contains>, C<starts>, C<lt>, C<le>, C<gt>, C<ge>, C<empty>,
C<notempty>.  Filters are applied server-side after all joins.

=item *

B<Combine (UNION ALL)> -- the "Combine data" panel stacks rows from a
second (or further) file beneath the current rows, using a unified column
set and leaving blanks where a source file lacks a column.

=item *

B<Charts> -- the toolbar offers a line chart (L<HTML::D3>
C<render_zoomable_line_chart_snippet>; brush-to-zoom, reference lines for
min/avg/max), a pie chart (C<render_pie_chart_snippet>; animated,
sorted by value, capped at 12 slices, click-a-slice to filter the table),
and a heatmap (C<render_heatmap_snippet>; two categorical axes, optional
value column or row count, configurable sequential colour scheme).

=item *

B<Drag-and-drop upload> -- any supported data file can be dropped directly
onto the application.  The file is opened immediately; when the "Combine
data" panel is open the dropped file populates the right-table path field.

=item *

B<Export> -- the toolbar export panel writes the current logical view
(after joins, filters, and dedup) to a chosen filesystem path as CSV
(C<.csv>), SQLite (C<.sql>), or JSON (C<.json>).

=item *

B<URL import> -- C</import> fetches an HTML table from any public URL and
renders it in the browser without saving to disc.

=item *

B<Redis data source> (optional) -- when the C<Redis> or C<Redis::Fast> module
is installed, any Redis hash store can be opened as a read-only data table by
entering a C<redis:tablename|redis://host:port> spec in the path field or in a
join/combine panel.  Redis keys must follow the C<tablename:entry> convention
used by L<Database::Abstraction>.  Because C<Redis> is an optional dependency,
the application works fully without it; Redis support activates automatically
when the module is available.

=back

=head2 UTF-8 and Encoding

All file data is returned as Perl character strings.  CSV/PSV/TSV files are
read by L<Text::xSV::Slurp> or L<DBD::CSV>, both of which pass bytes
through without re-encoding; the application serves the resulting page as
C<text/html; charset=UTF-8>, so full Unicode is displayed correctly as
long as the source file itself is UTF-8.

Filter values (C<f=col:op:val>) are decoded from the URL by Mojolicious
before reaching the controller and are compared against cell values as
Perl character strings.  Unicode characters in filter values are therefore
supported transparently.

URL-imported HTML tables are fetched with L<LWP::UserAgent::Cached>;
L<Database::Abstraction> uses the page's own charset declaration to decode
the body.  If the remote page declares an incorrect charset, cell values
may contain mojibake -- this is a limitation of the source data, not the
application.

=head1 SYNOPSIS

B<Start the development server (restarts automatically when you edit a file):>

    morbo script/database-bi

B<Start the production server:>

    hypnotoad script/database-bi

B<Use a different data directory:>

    # In database_bi.conf (create this file in the same folder as script/):
    { data_dir => '/home/user/data' }

B<Change the language used for templates:>

    # In database_bi.conf:
    { data_dir => 'data', language => 'fr', platform => 'web' }
    # Then create templates/web/fr/ and put your French .html.tt files there.

B<Run the test suite to verify everything is working:>

    make test

B<Generate the Makefile for the first time or after editing Makefile.PL:>

    perl Makefile.PL

=head1 ROUTES

=over 4

=item C<GET />

Scans C<data_dir>, renders a card grid of available tables.

=item C<GET /view/:table>

Opens the named table from C<data_dir>.  Accepts C<?f=col:op:val>
(repeatable) to pre-filter results.

=item C<GET /browse>

Filesystem navigator.  Accepts C<?path=> to set the starting directory
(defaults to C<$HOME>).

=item C<GET /open>

Opens any supported file by absolute path (C<?path=>).  Accepts C<?f=>
filters.  When the file is a multi-table SQLite database, passing
C<?it=E<lt>tableE<gt>> selects a specific internal table instead of the
auto-detected first one.

=item C<GET /join>

Performs one or more left joins and renders the merged table.  Parameters:

  l=<spec>               left table spec (see below)
  j=<spec>|<lk>|<rk>    join step (repeatable): right-spec, left key, right key
  f=<col>:<op>:<val>     result filter (repeatable)

Spec formats:

  table:name                         named table from data_dir
  path:/abs/path/to/file.csv         absolute local path
  /../hostname/dir/file.csv          remote file via SFTP
  redis:tablename|redis://host:port  Redis hash store (requires Redis module)

=item C<GET /api/columns>

Returns C<{ "columns": [...] }> for a table (C<?table=name>) or file
(C<?path=/abs/path>).  Used by the join UI to populate the right-key
dropdown without a page reload.

=item C<GET /export>

Exports the current logical view (same C<l=>, C<j=>, C<f=> parameters as
C</join>) as a file download.  Additional parameter:

  format=csv      (default) - RFC 4180 CSV; UTF-8; CRLF line endings
  format=sqlite   - SQLite 3 database with a single table named "data"

The download filename is derived from the left table label with
non-alphanumeric characters replaced by underscores.

=item C<POST /export>

Writes the current logical view to a chosen filesystem path.
Body params: C<l=>, C<j=>, C<f=> (same as GET), plus
C<dir=> (target directory) and C<filename=> (name including extension;
extension determines format: C<.csv> or C<.sql>).
Returns JSON C<{ saved: "/abs/path" }> or C<{ error: "..." }>.

=item C<GET /api/dirs>

Returns a JSON directory listing (subdirectories only) for the export
panel's inline directory browser.  Accepts C<?path=> (defaults to
C<$HOME>).  Returns C<{ path, parent, dirs: [{name, path}] }>.

=item C<GET /api/stat>

Returns filesystem metadata for a file path (C<?path=>).
Returns C<{ exists, path, mtime, size }>.  For SQLite files
(C<.sql>, C<.sqlite>, C<.sqlite3>, C<.db>) an additional C<table_count>
integer is included when the file is a valid SQLite database; the field
is omitted for Berkeley DB or other non-SQLite payloads.
If the file does not exist, C<exists> is C<false> and the remaining
fields are absent (HTTP 200).  Returns HTTP 400 when C<path> is missing.

=item C<POST /upload>

Accepts a multipart file upload (field name: C<file>), validates the
extension, saves to a managed C<.uploads/> subdirectory under the app
home, and returns JSON C<{ url, path }>.

=item C<GET /import>

Fetches an HTML table from a public URL (C<?url=>) and renders it as a
data grid.  An optional C<?t=> parameter (zero-based integer) selects
which HTML table on the page to display when the page contains multiple
tables.

=item C<GET /tables>

Schema browser for multi-table SQLite databases.  Accepts C<?l=E<lt>specE<gt>>
(same C<table:name> or C<path:/abs/path> spec as C</join>).  Returns a card
grid listing every internal table with its columns; each card links to
C</open?path=...&it=E<lt>tableE<gt>>.  Redirects to 404 when the spec is
absent, invalid, or the resolved file is not a multi-table SQLite database.

=item C<GET /combine>

Stacks rows from two or more tables vertically (UNION ALL) into a unified
view.  All columns from all sources appear as headers; cells are blank
where a source file lacks a column.  Parameters:

  l=<spec>               left table: "table:name" or "path:/abs/path"
  c=<spec>               additional table to stack (repeatable)
  f=<col>:<op>:<val>     result filter applied after combining (repeatable)

=item C<GET /graph>

Renders a D3.js v7 zoomable line chart of any two columns.  Parameters:

  l=<spec>           left table (required)
  x=<col>            X-axis column name (required; any type, shown as labels)
  y=<col>            Y-axis column name (required; must be numeric after stripping
                     currency symbols and commas; accounting-notation negatives
                     like (1,234.56) are handled automatically)
  back=<url>         URL for the second breadcrumb link (optional; default "/")
  back2=<url>        URL for the outermost breadcrumb link when three levels are
                     needed (optional; e.g. /tables?l=... for the schema browser)
  back2_label=<text> Label for the back2 link (optional; default "Back")
  j=, f=, d=         pipeline params (same as /join)

=item C<GET /pie>

Renders a D3.js v7 animated pie chart grouped by a category column.
Clicking a slice or legend entry navigates to the table view filtered to
that category.  Parameters:

  l=<spec>           left table (required)
  cat=<col>          category column to group by (required)
  val=<col>          numeric column to sum per category (required)
  donut=1            show a hole in the centre (optional)
  back=<url>         URL for the second breadcrumb link (optional; default "/")
  back2=<url>        URL for the outermost breadcrumb link (optional)
  back2_label=<text> Label for the back2 link (optional; default "Back")
  f=                 result filters applied before aggregating (repeatable)

=item C<GET /heatmap>

Renders a D3.js v7 grid heatmap with two categorical axes.  Each cell
colour encodes a summed or counted numeric value.  Parameters:

  l=<spec>           left table (required)
  x=<col>            X-axis column name (required; categorical)
  y=<col>            Y-axis column name (required; categorical)
  val=<col>          numeric column to sum per cell (optional; omit to count rows)
  scheme=<name>      colour scheme: YlOrRd Blues Greens Purples RdPu YlGnBu
                     (optional; default YlOrRd)
  show_val=1         print the value inside each cell (optional)
  back=<url>         URL for the second breadcrumb link (optional; default "/")
  back2=<url>        URL for the outermost breadcrumb link (optional)
  back2_label=<text> Label for the back2 link (optional; default "Back")
  j=, f=, d=         pipeline params (same as /join)

=item C<GET /bar>

Renders a D3.js v7 bar chart grouped by a categorical column.  Parameters:

  l=<spec>           left table (required)
  cat=<col>          category (X-axis) column (required)
  val=<col>          numeric column to sum per category; omit or use __count__
                     to count rows instead of summing (optional)
  orient=h           horizontal bars (default: vertical)
  sort=value|label   sort bars by descending value or ascending label
  max=N              collapse bars beyond the top-N into an "Other" bar
  back=<url>         URL for the second breadcrumb link (optional; default "/")
  back2=<url>        URL for the outermost breadcrumb link (optional)
  back2_label=<text> Label for the back2 link (optional; default "Back")
  j=, f=, d=         pipeline params (same as /join)

=item C<GET /folder>

Hierarchical folder view.  Groups a data table by the letter-prefix of an
order-number column (e.g. A10, CT4, B3) and shows one card per prefix group.
Clicking a card with sub-prefixes drills down; clicking a leaf shows the
matching data rows.  Parameters:

  l=<spec>     left table (required)
  col=<col>    order-code column (auto-detected when absent)
  prefix=<str> current drill-down prefix (top level when absent)
  back=<url>   URL for the back link (optional)
  f=           result filters (repeatable)

=item C<POST /uploads/clear>

Deletes every file from the C<.uploads/> staging directory.  Returns JSON
C<{ "freed": <bytes>, "count": <n> }>.  No request body is needed.

=back

=head1 CONFIGURATION

Place a C<database_bi.conf> file in the application root to override
defaults:

    {
        data_dir => 'data',   # directory scanned for data files on the home page
        platform => 'web',    # VWF template dimension
        language => 'en',     # VWF template dimension
    }

=head1 COMMON PITFALLS

=over 4

=item B<The configuration file is optional but must be valid Perl if present>

C<database_bi.conf> is loaded by C<Mojolicious::Plugin::Config>, which
evaluates it as a Perl data structure.  If the file exists but contains a
syntax error, the application will refuse to start.  If the file does not
exist, built-in defaults are used and no error occurs.  The file must return
a hashref:

    # database_bi.conf -- correct
    { data_dir => 'data', platform => 'web', language => 'en' }

    # WRONG -- missing braces
    data_dir => 'data'

=item B<data_dir is relative to the application home directory, not the process cwd>

Setting C<data_dir =E<gt> 'data'> looks for a folder called C<data/> in the
same directory as the C<script/database-bi> launcher, regardless of where you
run the server from.  An absolute path works on any system:

    { data_dir => '/var/db/mydata' }

=item B<The download_dir default is computed once at startup>

When the application starts, it picks the export directory in this order:
C<~/Downloads> (if it exists), then C<$HOME>, then the system temp directory.
This value is fixed for the life of the process.  Renaming or creating
C<~/Downloads> after the server starts has no effect.  To force a different
default, set it before starting:

    { data_dir => 'data' }   # and create ~/Downloads before starting the server

=item B<Adding a new language requires a template directory, not just a config change>

Setting C<language =E<gt> 'de'> in C<database_bi.conf> tells the controller
to look for templates in C<templates/web/de/>.  If that directory does not
exist, the controller automatically falls back to the default language.  To
add German support: (1) create C<templates/web/de/>, (2) copy and translate
the C<.html.tt> files from C<templates/web/en/>, then (3) set the config.

=item B<Supported data file extensions are: csv, db, sql, sqlite, sqlite3, xml, psv, tsv, xlsx>

The application recognises C<.csv>, C<.db>, C<.sql>, C<.sqlite>, C<.sqlite3>,
C<.xml>, C<.psv>, C<.tsv>, and C<.xlsx> files.  All three SQLite extensions
(C<.sql>, C<.sqlite>, C<.sqlite3>) are treated identically -- C<inventory.sqlite>
and C<inventory.sqlite3> are both opened as SQLite databases without renaming.
Excel C<.xlsx> files are read directly via C<Spreadsheet::ParseXLSX>.

URLs will work.
For example enter
L<https://worldpopulationreview.com/country-rankings/immigration-by-country>
into the C<Import from a web page> field on the dashboard.

=item B<The open_table helper lowercases the table name>

When the router matches C<GET /view/Sales> or C<GET /view/SALES>, the table
name is lowercased to C<sales> before being passed to the helper.  The data
file on disc must therefore also be lowercase (C<sales.csv>, not
C<Sales.csv>).

=item B<Redis support requires the Redis or Redis::Fast module>

Redis data source access is an optional feature.  If neither C<Redis> nor
C<Redis::Fast> is installed, entering a C<redis:...> spec returns a 404.
To enable Redis support, install either module:

    cpanm Redis::Fast   # recommended -- C bindings, faster
    cpanm Redis         # pure-Perl fallback

Redis keys must follow the C<tablename:entry> convention: each key is the
table name, a colon, and an entry identifier; the value is a Redis hash
whose fields become the row columns.  For example:

    HSET players:row1 name Alice score 10
    HSET players:row2 name Bob   score 20

Opening C<redis:players|redis://localhost:6379> then displays a two-row table
with columns C<entry>, C<name>, and C<score>.

=back

=head1 LIMITATIONS

=over 4

=item *

Only read operations on data files are supported.  Write-back (editing
cell values in the browser and saving them to the data file) is not
implemented.

=item *

Multi-table left joins are performed in memory by L<Database::Join>.  All
component tables are fetched into RAM before the merge; this is not
suitable for files that do not fit in the process's available memory.

=item *

Upload staging is managed automatically.  On every server startup,
C<Database::BI> evicts upload subdirectories whose modification time is
older than 24 hours.  The "Clear upload cache" button (C<POST
/uploads/clear>) triggers an immediate full purge regardless of age.

=item *

C<Sub::Protected>/:Protected enforcement relies on the CHECK compilation
phase.  When a module is loaded dynamically at test time (e.g. via
C<Test::Mojo->new(...)>), the CHECK phase has already passed and the
"Too late to run CHECK block" warning is emitted -- the access
restriction is not enforced in that test context.  This does not affect
production (morbo/hypnotoad) deployments where modules are compiled on
startup.  Unlike the former C<Sub::Private> approach, C<Sub::Protected>
does not delete stash entries, so OO dispatch C<< $self->_method() >>
works correctly in production without any special workarounds.

=back

=head1 ROADMAP

Features planned for future releases (post-0.012.0).  Items are ordered by
priority.

=over 4

=item * B<XLSX export> (High) -- C</export> supports CSV, SQLite, and JSON
but not XLSX output.  C<Excel::Writer::XLSX> would close the round-trip for
users whose source data is XLSX.

=item * B<Folder view filter/join propagation> (Medium) -- The folder view
panel in the dashboard does not carry forward active C<j=> and C<f=> params
when building the C</folder> URL.  Users who have filtered or joined a table
and then click "Folder view" silently lose their filter context.

=item * B<Copy-link button on chart pages> (Medium) -- The dashboard data
view has a copy-link button when filters are active, but C</graph>, C</pie>,
C</heatmap>, and C</bar> do not, even though their URLs are fully
parameterised and users share them.

=item * B<Column statistics panel> (Medium) -- A per-column popover showing
min, max, mean, median, and null-count.  C<List::Util> is already in
C<PREREQ_PM>; only C<Statistics::Descriptive> (or manual computation) is
needed.

=item * B<Multi-sheet XLSX> (Medium) -- C<_detect_file_info> reads only the
first worksheet.  A C<?sheet=> URL param with a sheet-name picker would
expose the full workbook.

=item * B<C<between> and C<in (a,b,c)> filter operators> (Low) -- Would
reduce multi-filter chains for common range and set queries.

=item * B<SSE streaming for large join results> (Low) -- The join pipeline
blocks the HTTP response until all rows are assembled.  Mojolicious supports
server-sent events, which could progressively stream rows to a JS table
renderer, giving visible progress on slow joins.

=back

=cut

=head1 SEE ALSO

=over 4

=item * L<Test Dashboard|https://nigelhorne.github.io/Database-BI/coverage/>

=back

=head1 REPOSITORY

L<https://github.com/nigelhorne/Database-BI>

=head1 SUPPORT

This module is provided as-is without any warranty.

=head1 AUTHOR

Nigel Horne C<< <njh@nigelhorne.com> >>

=head1 LICENCE AND COPYRIGHT

Copyright 2026 Nigel Horne.

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

=cut