Install and configure

Requirements

Two modules are recommended rather than required, so the application installs, starts and tests without them: HTML::OSM (0.12 or later) draws maps, and Geo::Coder::Free (0.48 or later) turns a place column into coordinates from a local database. Without them the map pages are hidden and the rest of the application is unaffected. Redis (or Redis::Fast) is optional too, and switches on the redis: data source when installed.

DBD::MariaDB (or DBD::mysql) is optional too, and is what a mariadb: source connects with. t/mariadb.t runs without a server; t/mariadb_live.t runs when MARIADB_SERVER is set, and creates its own tables and drops them.

MARIADB_SERVER=mariadb://user:pass@host/test prove -lv t/mariadb_live.t

Install

# from CPAN
cpanm Database::BI

# or from a checkout
cpanm --installdeps .
perl Makefile.PL
make
make test

Run it

# development: restarts when a source file is edited
script/database-bi

# the same thing, spelled out
morbo script/database-bi

# the same, showing one database at the home address
script/database-bi data/store.db

# production: a preforking server; start, or restart with no downtime
hypnotoad script/database-bi

# stop it
hypnotoad -s script/database-bi

With no arguments the launcher starts the development server itself; any other Mojolicious command word (routes, get, …) is passed through to the application. The preforking server is the one to put behind a reverse proxy for long-running use. It listens on port 8080 and ignores MOJO_LISTEN; set the port in database_bi.conf, as hypnotoad => { listen => ['http://*:3000'] }. It keeps its process id in script/hypnotoad.pid unless pid_file says otherwise.

Given the name of a database, the launcher starts the same development server, and the home address shows that database. The Home link in its breadcrumb leads to the usual list. It takes a data file, a remote path, a Redis spec or a MariaDB spec, as /open does.

Only one can be named, because a join cannot be given on the command line: open one and use Merge or Combine in the browser. Naming more than one, or a file that is missing, cannot be read or is of an unsupported type, prints the reason and exits with status 2 before any server starts.

MOJO_LISTEN chooses the port. script/database-bi daemon ignores a file named after it, and so does hypnotoad; to show a database from either, set BI_OPEN:

BI_OPEN=/full/path/store.db script/database-bi daemon
BI_OPEN=/full/path/store.db hypnotoad script/database-bi

Configuration

This is for whoever runs the application. A reader's own choices, such as light or dark, are not set here: see Preferences.

Create database_bi.conf in the application root next to script/. It is optional, but if it exists it must be valid Perl and must return a hashref:

{
    data_dir  => 'data',          # scanned by the home page
    model_dir => '.bi',           # analyses.json, models.json
    language  => 'en',            # template directory under templates/web/
    platform  => 'web',
}

Serving your own D3 and Leaflet

Chart pages load D3 from a CDN and map pages load Leaflet from unpkg by default. A deployment with no outbound network access, or one that prefers its own assets, points the keys at local copies under public/:

{
    d3_url                 => '/static/d3/d3.min.js',
    leaflet_js_url         => '/static/leaflet/leaflet.js',
    leaflet_css_url        => '/static/leaflet/leaflet.css',
    leaflet_cluster_js_url => '/static/leaflet/leaflet.markercluster.js',
    tile_url               => 'https://tiles.internal/{z}/{x}/{y}.png',
}

That is public/static/d3/d3.min.js and so on. The versions to copy are the ones the defaults name: D3 7.9.0, Leaflet 1.9.4 and leaflet.markercluster 1.5.3. With tile_url set, every request needed to draw a map can stay on the local network.

Command-line tools

script/bi-geocode

Warms the shared geocode cache for one column, so later map requests make no lookups:

./script/bi-geocode --spec table:customers --col city \
                    [--data-dir DIR] [--model-dir DIR] \
                    [--openaddr PATH] [--region CC] [--dry-run] [--verbose]

It is idempotent: a second run against a warm cache resolves nothing and says so. Exit status is 0 on success, 1 when the column is missing or no rows load, and 2 on bad arguments or an unopenable source.

script/bi-extract

Accelerates a large file: builds a SQLite copy of it, an extract, which the pages read for as long as the file is unchanged. Once the file changes, the file itself is read and the table page says the extract is stale.

./script/bi-extract --spec table:sales [--spec ...]   # accelerate, and build
./script/bi-extract --all                             # rebuild every one
./script/bi-extract --remove --spec table:sales       # stop, and remove its extract
./script/bi-extract --list
                    [--data-dir DIR] [--model-dir DIR]

Extracts live in <model_dir>/extracts/, never under data_dir, and deleting that directory loses nothing. Exit status is 0 on success, 1 when an extract could not be built, and 2 on bad arguments. Run --all from cron after the files are refreshed.

script/bi-alert

Reports saved analyses and turns a crossed threshold into a non-zero exit status, so cron can act on it:

./script/bi-alert --analysis ID [--analysis ID ...] \
                  [--threshold MEASURE:OP:VALUE] \
                  [--suppress 1h] [--recovery] \
                  [--notify-cmd 'mail -s "$BI_ALERT_SUBJECT" oncall@example.com']

Exit status is 0 when nothing is announced, 1 when a threshold is crossed and announced, 2 on error, and 3 when a crossing that was announced has ended and --recovery asked for that to be announced. --suppress takes a duration and stops a condition that is still true from being reported as news again; the throttle is one small JSON file in the model directory. The digest goes to standard output and nothing leaves the machine unless --notify-cmd says where.

Staying up to date

The distribution ships a test suite that covers the routes, the pipeline, the renderer invariants and the templates; run it before upgrading:

prove -l t/

The release notes in Changes list every behaviour change, and the test dashboard is published at nigelhorne.github.io/Database-BI/coverage.