ACTIONS

index

GET / -- Scan data_dir and present a card grid of available tables.

API SPECIFICATION

INPUT

None (reads data_dir from application config).

OUTPUT

Renders home.html.tt with:

tables     ARRAYREF of { name => $stem, file => $basename }
title      'Choose a Database'

MESSAGES

None produced by this action; any file-scan errors are silently ignored (empty directory yields an empty card grid).

FORMAL SPECIFICATION

index == lambda self .
  let dir  = resolve self.app.config.data_dir in
  let tbls = { b | b in dir /\ basename(b) =~ EXT_RE } in
  post render(home, tables: map(stem, tbls))

EXAMPLE

GET / -> 200 text/html containing a card for each file in data/

view

GET /view/:table -- Open and display the chosen table from data_dir.

API SPECIFICATION

INPUT

:table   string   Table name (alphanumeric + underscore).  Returns 404 on
                  other characters.
?f=      string   (repeatable) Filter spec: "col:op:val".

DOMAIN CONSTRAINTS: :table

:table is validated against \A[A-Za-z_][A-Za-z0-9_]*\z before open_table is ever called. The first character must be a letter (A-Z, a-z) or an underscore; subsequent characters may also include digits (0-9).

Valid partition: sales (letters only), _temp (underscore-start), report_2024 (mixed). A name that is valid but has no backing file returns 200 with an error message -- NOT 404.
Invalid partition: 1sales (digit-start, 404), my.data (dot, 404), my-data (hyphen, 404), non-ASCII characters (404).
Boundary values: a (single letter, valid), _ (single underscore, valid), 1 (single digit, 404), a1 (letter then digit, valid), 1a (digit then letter, 404).

OUTPUT

On success renders dashboard.html.tt with table data. On error re-renders home.html.tt with an error stash variable.

MESSAGES

error_table_open   -- DataSource initialisation or fetch_all threw.

FORMAL SPECIFICATION

view == lambda self .
  pre  self.stash('table') =~ TABLE_NAME_RE
  let src     = open_table(table) in
  let records = src.fetch_all()   in
  let cols    = get_columns(src, records) in
  post render(dashboard, records: filter(records, f_params))

EXAMPLE

GET /view/sales      -> 200 HTML table of all sales rows
GET /view/sales?f=region:eq:North -> shows only North region rows
GET /view/../etc     -> 404

browse

GET /browse -- Navigate the filesystem and pick a data file.

API SPECIFICATION

INPUT

?path=   string   Absolute filesystem path to browse (default: $HOME).
                  Returns 404 when the path does not exist or is not a
                  directory.

OUTPUT

Renders browse.html.tt with:

current_path   string
parent_href    string or undef (undef at filesystem root)
crumbs         ARRAYREF of { name, href }
dirs           ARRAYREF of { name, href }   -- subdirectories, sorted
files          ARRAYREF of { name, href }   -- supported data files, sorted

MESSAGES

None; errors render as 404.

FORMAL SPECIFICATION

browse == lambda self .
  let dir = realpath(param('path') // HOME) in
  pre  is_dir(dir)
  post render(browse, dirs: subdirs(dir), files: supported_files(dir))

EXAMPLE

GET /browse                            -> 200, lists $HOME
GET /browse?path=/tmp                  -> 200, lists /tmp
GET /browse?path=/nonexistent/xyz      -> 404

open_file

GET /open -- Open a supported data file from any absolute filesystem path.

API SPECIFICATION

INPUT

?path=   string   Absolute path to the data file.  Returns 404 when missing,
                  not a regular file, or the extension is not in SUPPORTED_EXT.
?f=      string   (repeatable) Filter spec: "col:op:val".

DOMAIN CONSTRAINTS: ?path=

Extension filter (EXT_RE): The basename must match \.(?:csv|db|sql|xml|psv)\z (case-insensitive). The \z anchor (absolute end-of-string) means a URL-encoded trailing newline (e.g. file.csv%0A decoded to file.csv\n) does NOT pass -- the \n falls after the \z boundary and the extension check fails.
Valid partition: /data/sales.csv (lowercase extension), /tmp/REPORT.CSV (uppercase extension, /i matches), any .db, .sql, .xml, .psv regular file.
Invalid partition: Absent ?path= (404), non-existent file (404), directory instead of file (404), unsupported extension such as .txt (404), empty string (404), path containing a %0A (newline) suffix (404).
Double-extension filenames: A file like file.php.csv passes EXT_RE (last segment is .csv) but produces table stem file.php which fails DataSource's TABLE_NAME_RE. The open_table call is inside eval, so the croak is caught and the action returns 200 with a friendly error, not a 500.

OUTPUT

On success renders dashboard.html.tt with back_url pointing to the directory's /browse page, and file_path set to the resolved absolute path (used by the template to record the file in localStorage). On error re-renders home.html.tt with error, back_url, back_label.

MESSAGES

error_file_open   -- DataSource threw during initialisation or fetch_all.

FORMAL SPECIFICATION

open_file == lambda self .
  let file = realpath(param('path')) in
  pre  is_file(file) /\ basename(file) =~ EXT_RE
  let src = open_table(stem(file), dir=dirname(file)) in
  post render(dashboard, file_path: file.to_string)

EXAMPLE

GET /open?path=/data/archive/sales.csv   -> 200, table view
GET /open                                -> 404
GET /open?path=/etc/passwd               -> 404

import_url

GET /import -- Fetch a remote HTML page, extract the first (or selected) table, and render it as a sortable data grid.

API SPECIFICATION

INPUT

?url=          string   Required.  Full http:// or https:// URL of the page
                        that contains the HTML table.  Returns home with an
                        error message if the scheme is wrong, the fetch fails,
                        or no table is found at the specified index.
?table_index=  integer  Zero-based index of the C<< <table> >> to use when
                        the page contains more than one table (default: 0).
?f=            string   (repeatable) Filter spec: "col:op:val".

OUTPUT

On success renders dashboard.html.tt with the same stash shape as open_file, plus source_url (the original URL) for localStorage tracking.

On error re-renders home.html.tt with an error stash variable.

MESSAGES

error_url_required  -- empty or missing C<url> param
error_url_invalid   -- URL does not begin with http:// or https://
error_url_fetch     -- LWP fetch failure or no table found at the index

EXAMPLE

GET /import?url=https://matrix.perl-magpie.org/dist/Database-Abstraction/
GET /import?url=https://example.com/data.html&table_index=2

columns_api

GET /api/columns -- Return column names for a table or file as JSON.

Used by the join UI to populate the right-key dropdown without a page reload.

API SPECIFICATION

INPUT

?table=  string   Table name from C<data_dir>.
?path=   string   Absolute path to a data file.

One of table or path must be present; if both are, table takes precedence.

OUTPUT

200 application/json   { "columns": ["col1", "col2", ...] }
404 application/json   { "error": "not found" }

MESSAGES

None produced by this action directly; internal errors yield a 404.

FORMAL SPECIFICATION

columns_api == lambda self .
  let src = open_spec(table_param or path_param) in
  pre  src /= undef
  post render_json({ columns: get_columns(src) })

EXAMPLE

GET /api/columns?table=sales  -> {"columns":["product","region","amount"]}
GET /api/columns?table=noexist -> 404

join_tables

GET /join -- Perform one or more left joins and render the merged table.

API SPECIFICATION

INPUT

?l=    string   Required. Left table spec: "table:name" or "path:/abs/path".
                Returns 404 if unresolvable.
?j=    string   (repeatable) Join step: "<right-spec>|<left-key>|<right-key>".
                Invalid or non-existent steps are silently skipped.
?f=    string   (repeatable) Result filter: "col:op:val".

OUTPUT

Renders dashboard.html.tt with the merged/filtered result set.

MESSAGES

error_table_open   -- Left table fetch threw (re-renders home with error).

FORMAL SPECIFICATION

join_tables == lambda self .
  let left = open_spec(param('l')) in
  pre  left /= undef
  let joined  = fold(_left_join, left, param_list('j')) in
  let filtered = fold(_apply_filter_spec, joined, param_list('f')) in
  post render(dashboard, records: filtered)

PSEUDOCODE

1. Parse and open left table spec; 404 on failure.
2. For each j= param (in order):
     a. Parse right-spec, left-key, right-key from "|"-split.
     b. Verify left-key exists in current column list.
     c. Open right table; skip step on any error.
     d. Verify right-key exists in right column list.
     e. Call _left_join; update records and columns.
3. Apply all f= filters via _apply_filter_spec.
4. Build stable table key for localStorage ("join:left:right1:...").
5. Render dashboard.

EXAMPLE

GET /join?l=table:sales&j=table:products|product|name
  -> merged left join of sales and products on product/name columns

export_data

GET /export -- Stream the current logical view as a browser file download.

API SPECIFICATION

INPUT

?l=        string   (required) Left table spec.
?j=        string   (repeatable) Join steps.
?f=        string   (repeatable) Filter specs.
?format=   string   "csv" (default) or "sqlite".

DOMAIN CONSTRAINTS: ?format=

The comparison is $format eq 'sqlite' -- case-sensitive, exact match.

Valid partitions: csv (explicit CSV), sqlite (exact lowercase, SQLite binary), absent/undef (defaults to CSV).
Invalid partitions (all fall back to CSV): SQLITE (uppercase, not equal to 'sqlite'), Sqlite (mixed case), sqlit (truncated), sqlite1 (extra character), json (unknown format).

OUTPUT

200 text/csv                  or application/vnd.sqlite3
404 application/json          { "error": "Table not found" }

MESSAGES

None beyond the 404 response.

FORMAL SPECIFICATION

export_data == lambda self .
  let (recs, cols, label) = _run_export_pipeline() in
  pre  recs /= undef
  post if format = 'sqlite' then render_sqlite(recs, cols)
       else render_csv(recs, cols)

EXAMPLE

GET /export?l=table:sales&format=csv     -> downloads sales.csv
GET /export?l=table:sales&format=sqlite  -> downloads sales.db

export_write

POST /export -- Write the current logical view to a chosen filesystem path.

API SPECIFICATION

INPUT

l=         string   (required) Left table spec.
j=         string   (repeatable) Join steps.
f=         string   (repeatable) Filter specs.
dir=       string   Target directory (must exist; resolved via realpath).
filename=  string   Output filename including extension.  Extension determines
                    format: C<.csv> -> RFC 4180 CSV; C<.sql> -> SQLite.

DOMAIN CONSTRAINTS: filename=

The extension check uses /\.csv\z/i (CSV) or /\.sql\z/i (SQLite): the /i flag makes matching case-insensitive, so .CSV and .SQL are accepted alongside lowercase forms. Everything else returns 415.

Path separator characters (/ and \) in filename are stripped first via m{([^/\\]+)\z} -- only the basename is kept, preventing directory traversal.

Valid partitions: report.csv, report.sql, REPORT.CSV, report.SQL. A single-character stem (a.csv) is also valid.
Invalid partitions (415): report.txt, report.json, report (no extension), empty string.

OUTPUT

200 application/json   { "saved": "/abs/path/to/file" }
404 application/json   { "error": "..." }  -- bad dir or table not found
415 application/json   { "error": "..." }  -- unsupported extension
500 application/json   { "error": "..." }  -- write failure

MESSAGES

error_dir_not_found   -- realpath of dir failed or result is not a directory.
error_ext_required    -- filename extension is not .csv or .sql.
error_write_failed    -- DBI or filesystem write threw.

FORMAL SPECIFICATION

export_write == lambda self .
  let dir  = realpath(param('dir'))      in
  let file = dir / strip_path(param('filename')) in
  pre  is_dir(dir) /\ ext(file) in {csv, sql}
  let (recs, cols, _) = _run_export_pipeline() in
  pre  recs /= undef
  post spurt(file, serialise(recs, cols))
       /\ render_json({ saved: file.to_string })

EXAMPLE

POST /export  l=table:sales dir=/home/user/exports filename=report.csv
  -> { "saved": "/home/user/exports/report.csv" }

dirs_api

GET /api/dirs -- Return a JSON directory listing for the export panel.

API SPECIFICATION

INPUT

?path=   string   Directory to list (default: $HOME).  Returns 404 when not
                  a directory.  Hidden entries (names starting with ".") are
                  excluded.

OUTPUT

200 application/json
  {
    "path":   "/abs/path",
    "parent": "/abs/parent" or null (at filesystem root),
    "dirs":   [ { "name": "alpha", "path": "/abs/path/alpha" }, ... ]
  }

404 application/json   { "error": "Not a directory" }

MESSAGES

None beyond the 404 response.

FORMAL SPECIFICATION

dirs_api == lambda self .
  let dir = realpath(param('path') // HOME) in
  pre  is_dir(dir)
  post render_json({ path: dir, parent: parent(dir), dirs: subdirs(dir) })

EXAMPLE

GET /api/dirs?path=/home/user  -> {"path":"/home/user","parent":"/home","dirs":[...]}

stat_api

GET /api/stat -- Return filesystem metadata for a file path.

Used by the home page tooltip to show modification time and size for recently opened files without a page reload.

API SPECIFICATION

INPUT

?path=   string   Absolute path to query.  Returns HTTP 400 when absent.

OUTPUT

200 application/json   (file exists)
  { "exists": true, "path": "/resolved/path", "mtime": 1700000000, "size": 4096 }

200 application/json   (file does not exist)
  { "exists": false, "path": "/original/path" }

400 application/json   { "error": "\"path\" parameter is required" }

mtime is Unix epoch seconds. size is bytes. A missing/unresolvable path returns HTTP 200 with exists: false (not 404) so the UI can distinguish "file was deleted" from a request error.

MESSAGES

error_path_required   -- the "path" query parameter was not supplied.

FORMAL SPECIFICATION

stat_api == lambda self .
  let path = param('path') in
  pre  path /= undef
  let file = realpath(path) in
  post if is_file(file) then
         render_json({ exists: true, mtime: mtime(file), size: size(file) })
       else render_json({ exists: false })

EXAMPLE

GET /api/stat?path=/data/sales.csv
  -> { "exists": true, "path": "/data/sales.csv", "mtime": 1700000000, "size": 1234 }

GET /api/stat?path=/deleted.csv
  -> { "exists": false, "path": "/deleted.csv" }

upload_file

POST /upload -- Accept a drag-and-dropped data file and return a redirect URL.

The file is saved under its original filename in a managed subdirectory of <app_home>/.uploads/. The subdirectory name is randomised so that concurrent uploads of files with the same name do not collide.

API SPECIFICATION

INPUT

Multipart form upload, field name: file.

file   upload   Supported extensions: csv, db, sql, xml, psv.

OUTPUT

200 application/json   { "url": "/open?path=/abs/path/file.csv", "path": "/abs/path/file.csv" }
400 application/json   { "error": "No file received" }
415 application/json   { "error": "Unsupported file type. Accepted: ..." }

MESSAGES

error_upload_none   -- no file was received in the multipart upload.
error_upload_ext    -- the file's extension is not in the supported list.

FORMAL SPECIFICATION

upload_file == lambda self .
  let upload = req.upload('file') in
  pre  upload /= undef /\ basename(upload.filename) =~ EXT_RE
  let dest = home/.uploads/<random>/<filename> in
  post upload.move_to(dest)
       /\ render_json({ url: '/open?path=' ++ url_escape(dest), path: dest })

EXAMPLE

POST /upload  (multipart: file=@sales.csv)
  -> { "url": "/open?path=%2F...%2Fsales.csv", "path": "/.../.uploads/abc123/sales.csv" }

NAME

Database::BI::Controller::Dashboard - Home picker, filesystem browser, table viewer, left-join engine, result filter, export, and file upload

SYNOPSIS

All routes in Database::BI are handled by this controller. You do not call its methods directly -- Mojolicious dispatches HTTP requests to them automatically. The examples below show browser URLs and their curl equivalents.

Open the home page and see all tables in the data directory:

# Browser
http://localhost:3000/

# curl
curl http://localhost:3000/

View a single table (file: data/sales.csv):

# Browser
http://localhost:3000/view/sales

# curl
curl http://localhost:3000/view/sales

Filter rows -- show only rows where region equals "North":

# Browser (add ?f=col:op:val to any view URL)
http://localhost:3000/view/sales?f=region:eq:North

# Multiple filters -- "North" AND amount greater than 100:
http://localhost:3000/view/sales?f=region:eq:North&f=amount:gt:100

# curl
curl 'http://localhost:3000/view/sales?f=region:eq:North'

Join two tables -- left-join sales with products on the product column:

# Browser
http://localhost:3000/join?l=table:sales&j=table:products|product|name

# The j= parameter is: right-table-spec | left-key | right-key
# You can chain multiple joins:
http://localhost:3000/join?l=table:sales&j=table:products|product|name&j=table:regions|region|id

Open a file anywhere on the filesystem (not just in data/):

http://localhost:3000/open?path=/home/user/reports/q3.csv

Download the current view as a CSV file:

# Uses the same l=, j=, f= parameters as /join
http://localhost:3000/export?l=table:sales&format=csv

# Download as a SQLite database file instead:
http://localhost:3000/export?l=table:sales&format=sqlite

# Download a filtered + joined result:
http://localhost:3000/export?l=table:sales&j=table:products|product|name&f=region:eq:North&format=csv

Save the current view to a file on the server (instead of downloading):

# POST with form fields; format is inferred from the filename extension
curl -X POST http://localhost:3000/export \
     -F l=table:sales \
     -F dir=/home/user/exports \
     -F filename=report.csv

# Save as SQLite:
curl -X POST http://localhost:3000/export \
     -F l=table:sales \
     -F dir=/home/user/exports \
     -F filename=report.sql

Get the column list for a table (used by the join UI):

curl http://localhost:3000/api/columns?table=sales
# Returns: {"columns":["product","region","amount","date"]}

Check when a file was last modified (used by the tooltip on the home page):

curl 'http://localhost:3000/api/stat?path=/data/sales.csv'
# Returns: {"exists":true,"path":"/data/sales.csv","mtime":1700000000,"size":1234}

Browse the filesystem to find a data file:

http://localhost:3000/browse
http://localhost:3000/browse?path=/home/user/data

Upload a data file by dropping it onto the page (multipart form POST):

curl -X POST http://localhost:3000/upload \
     -F file=@/home/user/data/sales.csv
# Returns: {"url":"/open?path=/.../.uploads/.../sales.csv","path":"/.../.uploads/.../sales.csv"}

DESCRIPTION

All user-facing routes in Database::BI are handled by this controller. See the individual action POD above for per-endpoint documentation.

Filter operators

The f=col:op:val filter spec supports:

eq        case-insensitive string equality
ne        case-insensitive string inequality
contains  case-insensitive substring match
starts    case-insensitive prefix match
lt        numeric less-than
le        numeric less-than-or-equal
gt        numeric greater-than
ge        numeric greater-than-or-equal
empty     cell is undef or empty string (val ignored)
notempty  cell is defined and non-empty (val ignored)

The colon separator is split with a limit of 3, so values may themselves contain colons (e.g. f=sale_date:eq:2025-01-15).

COMMON PITFALLS

These are the most common mistakes when working with this controller.

SQLite files must use the .sql extension, not .sqlite

Database::Abstraction (the data-reading library) probes for a file called tablename.sql when it wants to open a SQLite database. It does not look for .sqlite or .db3. If your file is called inventory.sqlite, rename it to inventory.sql or it will not appear in the file browser and will return 404 when opened.

Filter values that contain a colon still work

A date like 2025-01-15 contains hyphens, not colons, so it is fine. But if your value itself contains a colon (for example, a time like 14:30:00), the filter still works because the col:op:val spec is split on the first two colons only -- the rest of the string becomes the value.

# This correctly matches "14:30:00" in the start_time column:
?f=start_time:eq:14:30:00

Left join keeps only the FIRST matching right-table row

When the right table has two rows with the same join key, only the first one (in file order) is used. The second is silently ignored. If you need all matches, consider pre-processing your data so join keys are unique.

Export format comes from the filename extension, not a Content-Type header

When using POST /export to save a file to disk, the format (CSV or SQLite) is determined by the extension of the filename parameter. .csv produces a CSV file; .sql produces a SQLite database. Any other extension returns HTTP 415 (Unsupported Media Type). The Content-Type request header is ignored entirely.

Template Toolkit variables starting with underscore are silently dropped

If you add a stash variable with a name starting with _ (for example, _tmp or _result), Template Toolkit will silently produce an empty string when the template tries to read it. This is a TT quirk when TRIM => 1 is active. Always use names that start with a letter.

_apply_filter_spec always passes all records through for unknown operators

If you pass an operator that is not in the supported list (for example regex or like), the filter is treated as a no-op and all rows are returned. No error is produced. This is intentional so that future operators can be added without breaking existing clients that read a wider response.

Uploading a file does not clean up automatically

Files uploaded via POST /upload are stored in .uploads/ under the application's home directory and are never deleted automatically. They accumulate until you manually remove the .uploads/ directory. This is intentional for a single-user local tool, but you should be aware of it on long-running servers.

Open data/ tables by name; open other files by absolute path

The view action (GET /view/:table) only looks inside data_dir. To open a file from anywhere else on the filesystem, use open_file (GET /open?path=/abs/path). The two routes use different URL schemes and are not interchangeable.

LIMITATIONS

The in-memory left join in _left_join holds both the left and right result sets in RAM simultaneously. For files with millions of rows, replace the open_table helper in Database::BI with a Database::Join backend without changing this controller.
_resolve_language extracts only the primary language subtag from the first Accept-Language tag (e.g. de from de-DE,de;q=0.9,en;q=0.8). Quality weights and multiple alternatives are not ranked.
No GeoIP-based language resolution is implemented. The language is resolved from the HTTP Accept-Language header only.
Sub::Private enforcement requires the CHECK compilation phase; when modules are loaded dynamically at test time the "Too late to run CHECK block" warning is emitted and the restriction is not enforced in that context.

AUTHOR

Nigel Horne <njh@nigelhorne.com>

LICENCE AND COPYRIGHT

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)