blib/lib/Database/BI.pm

filter: Statementstmt 77.8% 419 / 538 Branchbran 41.4% 126 / 304 Conditioncond 30.2% 116 / 384 MC/DCmcdc 10.2% 20 / 196 Subroutinesub 98.2% 56 / 57 Podpod 100% 1 / 1 totaltotal 49.8% 738 / 1480 CC 304 SCAR 39.3
CC
304
Cov
54%
_check_export_dirs
259.6
_strict_transport_security
170.0
_check_file_roots
99.2
CRAP 51.0
« blib/lib/Database/BI/SafeUA.pm ↑ Summary blib/lib/Database/BI/Password.pm »
1 package Database::BI;
2  
3 7 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 7 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5  
6 7 ▶use Mojo::Base 'Mojolicious', -strict, -signatures;
Subroutine BEGIN: called CC=1 SCAR=0.0
7  
8 7 ▶use Carp qw(croak);
Subroutine BEGIN: called CC=1 SCAR=0.0
9 7 ▶use Mojo::File ();
Subroutine BEGIN: called CC=1 SCAR=0.0
10 7 ▶use Mojo::URL;
Subroutine BEGIN: called CC=1 SCAR=0.0
11 7 ▶use Database::BI::I18N ();
Subroutine BEGIN: called CC=1 SCAR=0.0
12 7 ▶use Database::BI::Sessions;
Subroutine BEGIN: called CC=1 SCAR=0.0
13 7 ▶use CHI ();
Subroutine BEGIN: called CC=1 SCAR=0.0
14 7 ▶use File::Spec ();
Subroutine BEGIN: called CC=1 SCAR=0.0
15 7 ▶use Readonly;
Subroutine BEGIN: called CC=1 SCAR=0.0
16  
17 7 ▶use Database::BI::Model::DataSource;
Subroutine BEGIN: called CC=1 SCAR=0.0
18 7 ▶use Database::BI::Tutorial;
Subroutine BEGIN: called CC=1 SCAR=0.0
19  
20 # version->declare here alone: this is VERSION_FROM, and with it MakeMaker
21 # writes 0.023.1 into META; a plain dotted string becomes v0.023.1 there.
22 7 ▶use version; our $VERSION = version->declare('0.023.1');
Subroutine BEGIN: called CC=1 SCAR=0.0
23  
24 # The roadmap after 0.021.0, from its gap analysis (2026-11-06).
25 #
26 # Features:
27 # TODO: Time zones. A timestamp's Z or offset is accepted and ignored (times
28 # are compared as written). Normalise to UTC or a configured zone, with
29 # Time range and span= buckets aware of it.
30 # TODO: More JSON: JSON Lines (.jsonl, .ndjson); optional dot-path flattening
31 # of nested objects (address.city) instead of JSON text; reading a large
32 # file as a stream rather than all at once.
33 # TODO: Ranges for historical data: windows anchored to the data rather than
34 # to today ("the first decade in the data"), and decade and century span=
35 # buckets for year columns.
36 # TODO: Multi-sheet XLSX: ?sheet= and a sheet picker, through the /tables
37 # cards that JSON and SQLite use.
38 # TODO: A better Print: a heading naming the view's filters and range, a
39 # "print the rows shown" mode, and a server-side PDF for views too large
40 # for the browser to lay out (Print asks first past 10,000 rows).
41 # TODO: Encoding on upload: detect Windows-1252 (a BOM is read past since
42 # 0.021.0) and say which encoding was used.
43 # TODO: Alerts in the app: a canvas panel to set bi-alert's thresholds and
44 # see recent alerts, so they do not need cron.
45 # TODO: More databases: PostgreSQL through Database::Join::Catalog, as MariaDB
46 # is, and a columnar backend (DuckDB or Parquet) for files too large for
47 # SQLite extracts.
48 #
49 # Technical debt:
50 # TODO: Dashboard.pm is over 15,000 lines. Split it by area (charts, canvas,
51 # model stores, export and access, geo) into controllers of their own;
52 # the routes stay here.
53 # TODO: One validated filter representation. f= is split in _filter_request,
54 # _apply_filter_spec, _filter_specs_to_criteria, _fetch_page and the
55 # canvas, each with its own rules; parse it once into a typed form at the
56 # boundary.
57 # TODO: One date reader. _is_iso_date, _to_iso_date, _date_key,
58 # _time_columns, Calc::date_normalise and the browser's biDetectColType
59 # each recognise dates differently (the browser takes DD-Mon-YYYY, the
60 # server's filter does not). Share one definition.
61 # TODO: Move dashboard.html.tt's inline script into static files (allowed by
62 # script-src 'self') so it can be unit-tested, not only run in Playwright.
63 # TODO: Loading Dashboard.pm during the compile phase runs Sub::Protected's
64 # CHECK, which alone turned the SQL path's sums into JSON strings
65 # (t/time-bucket.t). The server never loads it that way; find which
66 # protected sub is responsible.
67 # TODO: Async work: long joins and geocoding block a worker. Use
68 # Mojo::IOLoop->subprocess, or the server-sent-events streaming, for
69 # progress and free workers.
70 # TODO: Generated CI: the Windows cpanm retry, and the cache that never fills
71 # on Windows (installs go into Perl's own site folder), belong in App::GHGen's
72 # template, not in perl-ci.yml, which it overwrites.
73  
74 # Default config values used by the Config plugin and referenced explicitly
75 # in startup() so callers always get a resolved value.
76 7 Readonly my $DEFAULT_DATA_DIR => 'data';
77 7 Readonly my $DEFAULT_PLATFORM => 'web';
78 7 Readonly my $DEFAULT_LANGUAGE => 'en';
79 7 Readonly my $DEFAULT_CACHE_TTL_URL => '15 min';
80  
81 # The browser-side chart library is fetched from a CDN unless the deployment
82 # self-hosts it. This is the pinned version the pages were tested with; the
83 # URL is a config seam (d3_url), and the Leaflet assets have their own keys
84 # (leaflet_*_url), so an offline installation can point them at its own copies.
85 7 Readonly my $DEFAULT_D3_URL => 'https://cdnjs.cloudflare.com/ajax/libs/d3/7.9.0/d3.min.js';
86  
87 # Where HTML::OSM fetches Leaflet and its tiles when the config names no
88 # other. Only their origins matter: they go into the Content-Security-Policy.
89 7 Readonly my $DEFAULT_LEAFLET_URL => 'https://unpkg.com/';
90 7 Readonly my $DEFAULT_TILE_URL => 'https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png';
91  
92 # How long a login lasts after the last request: a working day.
93 7 Readonly my $DEFAULT_LOGIN_EXPIRATION => 8 * 60 * 60;
94  
95 # Where a server started without -l or MOJO_LISTEN listens: this machine only.
96 # Config listen (morbo, daemon, prefork) and hypnotoad => { listen } (hypnotoad)
97 # change the port, or open it to other machines with 'http://*:3000'.
98 7 Readonly my $DEFAULT_LISTEN => 'http://127.0.0.1:3000';
99 7 Readonly my $DEFAULT_HYPNOTOAD_LISTEN => 'http://127.0.0.1:8080';
100  
101 # What a Content-Security-Policy nonce may be made of (base64 or base64url);
102 # anything else reaching the policy is dropped.
103 7 Readonly my $NONCE_RE => qr{\A[A-Za-z0-9+/_=-]+\z};
104  
105 # The Referer a page sends: the whole URL to this site, only the origin to
106 # another, and nothing from https to http. Specs, filter values and share
107 # tokens travel in the query string, and an older browser's default sends all
108 # of it to the CDNs, the tile servers and any site a reader follows a link to.
109 # OpenStreetMap's tile policy asks pages for a Referer, so it is not none.
110 7 Readonly my $REFERRER_POLICY => 'strict-origin-when-cross-origin';
111  
112 # Permissions-Policy: every powerful browser feature off, for this site and
113 # any frame it holds, but writing to the clipboard, which the Copy link
114 # buttons do. Only names Chromium knows: it logs an error for any other.
115 7 Readonly my @PERMISSIONS_DENIED => qw(
116 accelerometer autoplay browsing-topics camera clipboard-read display-capture
117 encrypted-media fullscreen gamepad geolocation gyroscope hid
118 identity-credentials-get idle-detection local-fonts magnetometer microphone
119 midi otp-credentials payment picture-in-picture publickey-credentials-create
120 publickey-credentials-get screen-wake-lock serial storage-access usb
121 window-management xr-spatial-tracking
122 );
123 Readonly my $DEFAULT_PERMISSIONS_POLICY =>
124 7 join(', ', (map { "$_=()" } @PERMISSIONS_DENIED), 'clipboard-write=(self)');
125  
126 # Cross-Origin-Opener-Policy for a page: a window another site opened, or a
127 # window a page opens on another site, gets no handle on the page. No page
128 # opens a window or reads window.opener; links out are rel="noopener" anyway.
129 7 Readonly my $DEFAULT_COOP => 'same-origin';
130 7 Readonly my %COOP_VALUES => map { $_ => 1 } qw(same-origin same-origin-allow-popups noopener-allow-popups unsafe-none);
131  
132 # Strict-Transport-Security, sent on replies to https requests: a year, and
133 # this host only. includeSubDomains and preload reach other sites of the same
134 # domain, and are hard to take back, so they are for the configuration to add.
135 7 Readonly my $DEFAULT_HSTS => 'max-age=31536000';
136 7 Readonly my $HSTS_RE => qr{\Amax-age=\d{1,10}(?:\s*;\s*includeSubDomains)?(?:\s*;\s*preload)?\z}i;
137  
138 # Transport-layer upload size cap (bytes). Mojolicious enforces this before
139 # the request body is read into memory, so an oversized upload never reaches
140 # the controller. Must match $MAX_UPLOAD_BYTES in Dashboard.pm.
141 7 Readonly my $MAX_REQUEST_SIZE => 50 * 1_048_576; # 50 MiB
142 7 Readonly my $UPLOAD_MAX_AGE_S => 24 * 3600; # evict uploads older than 24 h
143  
144 # CHI drivers that keep the table cache on disc, and so need a directory.
145 7 Readonly my %DISC_CACHE_DRIVER => map { ($_ => 1) } qw(File FastMmap);
146  
147 =head1 NAME
148  
149 Database::BI - Web-based Business Intelligence viewer for data files
150  
151 =head1 VERSION
152  
153 0.023.1
154  
155 =head1 DESCRIPTION
156  
157 C<Database::BI> is a self-contained L<Mojolicious> web application that reads
158 and views arbitrary data (CSV, PSV, TSV, SQLite, JSON, XML, XLSX, etc.)
159 and presents them as styled, sortable, reorderable
160 HTML tables.
161 It has no persistent database of its own,
162 it reads your files on every request.
163  
164 Users navigate to pick a file, open the filesystem browser to navigate anywhere
165 on disc, drag-and-drop files to upload, join multiple tables in memory, apply
166 server-side filters, and export results as CSV, SQLite, JSON, or XLSX. Nine
167 D3.js v7 chart types, a pivot/crosstab table, per-column statistics, and
168 multi-visual saved canvases are available from the toolbar.
169  
170 A tutorial in plain English is built into the application at C</tutorial>.
171 It is written for people who do not know Perl, and it is the place to start.
172  
173 Key features:
174  
175 =over 4
176  
177 =item *
178  
179 B<File picker> -- the home page scans C<data_dir> and shows a card for
180 every supported file. Recently opened filesystem files appear in a
181 "Recently opened" section powered by C<localStorage>.
182  
183 =item *
184  
185 B<Filesystem browser> -- C</browse> lets the user navigate the entire
186 filesystem and open any supported data file, not just files in C<data_dir>.
187  
188 =item *
189  
190 B<Column sort and reorder> -- clicking a header sorts the table; headers
191 are draggable to reorder. Both settings are persisted in C<localStorage>
192 by column name and survive page reloads. A table too long for one page is
193 sorted by the server (C<sort=> and C<dir=> in the address), every row of it,
194 so page 1 is the start of the sorted table and Next goes on from there.
195 A column sorts as dates when every cell is one, as numbers when every cell
196 is one (C<$1,234>, C<(5)> and C<45%> are numbers; C<T27> is not), and
197 otherwise as text, with case and accents ignored and the digits in it
198 compared as numbers, so C<T3> comes before C<T27>.
199  
200 =item *
201  
202 B<Left join> -- the "Merge data" panel on any table view lets the user
203 join one or more additional tables on a shared key via L<Database::Join>.
204 Every left row is kept; right-table columns are appended for matching rows.
205 Multiple joins can be chained.
206  
207 =item *
208  
209 B<Quick-filter bar> -- a column/operator/value row below the toolbar lets
210 the user add filter conditions without opening any panel. Pressing Enter
211 applies the filter. Each active filter chip has an individual remove (x)
212 button so conditions can be dropped one at a time. Operators: C<eq>,
213 C<ne>, C<contains>, C<starts>, C<lt>, C<le>, C<gt>, C<ge>, C<between>, C<empty>,
214 C<notempty>; and C<in>, which a link can carry but the bar does not offer: any
215 value of a JSON list, as in C<f=place:in:["Paris","Rome"]>. Filters are
216 applied server-side after all joins. C<lt>, C<le>,
217 C<gt> and C<ge> compare numbers, reading C<$1,234.56> and C<(1,234.56)> as
218 numbers too; a blank or a word in the cell matches none of them.
219 An operator that is none of these (operators are lower case) is a C<400> that
220 lists them, and so is a filter, or a time range's C<rcol=>, on a column the
221 view does not have (the message lists the columns): neither is taken as a
222 filter that keeps every row, or none. A canvas card's own saved filters are
223 the exception: one on a column its dataset no longer has is ignored.
224  
225 =item *
226  
227 B<Combine (UNION ALL)> -- the "Combine data" panel stacks rows from a
228 second (or further) file beneath the current rows, using a unified column
229 set and leaving blanks where a source file lacks a column.
230  
231 =item *
232  
233 B<Charts> -- nine L<HTML::D3> D3.js v7 chart types are accessible from
234 the toolbar: line chart (brush-to-zoom, reference lines for min/avg/max and a trend line,
235 multi-series interactive legend), pie chart (click-a-slice to filter,
236 top-N, donut mode, configurable colour scheme), heatmap (two categorical
237 axes, count or sum mode), bar chart (horizontal or vertical, sort,
238 top-N), scatter/bubble plot (optional categorical colour and numeric
239 size dimensions), stacked bar chart, combo bar+line chart, treemap, and
240 histogram (server-side binning, C<?bins=N>). All charts can be resized
241 by dragging the bottom-right corner grip, or from the keyboard: the grip
242 takes focus, the arrow keys resize the chart (Shift takes bigger steps,
243 Home and End go to the narrowest and widest it can be) and the size is announced
244 as it changes. All chart pages include Copy-link and Export SVG/PNG/PDF buttons.
245  
246 =item *
247  
248 B<Maps> -- C<GET /map> plots the filtered view on an OpenStreetMap map, either
249 from latitude and longitude columns or from a place column geocoded by a
250 L<Geo::Coder::List> chain: L<Geo::Coder::Free>'s local data first, then
251 OpenStreetMap's Nominatim service (L<Geo::Coder::OSM>, at most one request a
252 second) for the places the local data does not know. Nominatim is asked over
253 https, and what it finds is also kept for a month in
254 C<E<lt>model_dirE<gt>/geocode-cache/>, or the C<cache_dir> set (see
255 L</Where caches are kept>). Each distinct place is
256 looked up once, remembered in the model store and shared by every dataset that
257 names it. Rows sharing a position become one marker.
258  
259 =item *
260  
261 B<Pivot table> -- the "Pivot table" panel builds a crosstab from any
262 combination of row dimensions, column dimensions, and measures
263 (C<sum|avg|min|max|count|count_distinct|median|stdev|first|last|range>, plus
264 C<pE<lt>NE<gt>> for the Nth percentile). The backend aggregates what it can;
265 the rest are computed in Perl from each group's values, subtotals and grand
266 total included, because a median is not the sum of the medians of its parts.
267 Supports subtotals, HAVING filters, top-N row groups, and direct export to
268 CSV, SQLite, JSON, or XLSX.
269  
270 =item *
271  
272 B<Column statistics> -- the column-stats button runs C<GET /stats>, which
273 returns one row per column: row count, null count, distinct count, first and
274 last, and (for numeric columns) min, max, range, mean, median, a chosen
275 percentile and the sample standard deviation. The arithmetic is the same one
276 the pivot's measures use.
277  
278 =item *
279  
280 B<Calculated fields> -- virtual columns defined by inline C<?c=name:expr>
281 URL params or stored persistently via C<POST /calc/save>. Expressions
282 support arithmetic, comparison, C<IF>, string, numeric, and date
283 functions, plus C<bin(value,width)> for histogram grouping. Calc fields
284 are applied before aggregation on all chart and pivot routes.
285  
286 =item *
287  
288 B<Relative time ranges> -- any data view, chart, pivot or saved canvas can be
289 limited to a named window worked out from today -- the last 24 hours, the last
290 7, 30 or 90 days, this or last month, quarter or year -- instead of typing
291 dates. The window is resolved on every request (C<?range=> with C<?rcol=>),
292 so a bookmark or a canvas still means the same thing tomorrow, and the filter
293 bar disables a window the data cannot answer.
294  
295 =item *
296  
297 B<Saved canvases> -- the "Save as Canvas" panel saves the current data
298 configuration as a named multi-visual canvas (C</a/:id>). Visuals are
299 arranged on a 12-column grid: drag a card by its header to any free cell
300 (the header also takes focus, and the arrow keys move the card a cell at a
301 time, Shift going to the edge), and resize it from the corner handle. A
302 card's width and height can also be set without a drag from the Size menu in
303 its header: full, half, a third or a quarter of the width, and one, two,
304 three or six rows high. The size the card is sits on the card itself, beside
305 that menu, so a width the corner grip left it at reads without the menu being
306 opened. Each move is announced -- the column and row the
307 card landed in, and any card the move pushed down -- and saved as you go. The whole position and size is
308 saved with the canvas, so a card put in row 4 stays in row 4 rather than
309 being packed up the page. B<Add text> in the toolbar adds a card of plain
310 text, for a title or an explanation, with a font, a size in points, colours and
311 an alignment of its own; it can hide its frame and header until it is pointed
312 at, and a row holding only one-row text cards is as tall as its text. Double-click the text, or use B<Edit text>, to change it or
313 to remove the card. B<Add current view> in the toolbar appends another
314 table, chart or map without leaving the canvas, and a status region announces
315 the card and the new visual count; when the card's script has drawn it, the
316 region says the chart or map has finished drawing, and if D3 or Leaflet could
317 not be loaded it names the missing library instead. A scatter card can be
318 coloured by a column and its bubbles sized by a number there, with the same
319 choices as the scatter page. A card that shows an
320 aggregated value picks its own function in that panel, from the vocabulary the
321 pivot uses and with the same names: Sum, Average, Min, Max, Count distinct,
322 Median, Std dev, First, Last, Range or a Percentile with its level, so a card
323 can show a median, a spread or a percentile where the chart pages behind it
324 could only sum. Canvases support
325 cross-filtering, drill-through hierarchies, parameterisation, bookmarks
326 with undo/redo, per-visual SVG, PNG and PDF download, and print to PDF/paper.
327  
328 =item *
329  
330 B<Drag-and-drop upload> -- any supported data file can be dropped directly
331 onto the application. The file is opened immediately; when the "Combine
332 data" panel is open the dropped file populates the right-table path field.
333  
334 =item *
335  
336 B<Export> -- the toolbar export panel writes the current logical view
337 (after joins, filters, and dedup) to a chosen filesystem path as CSV
338 (C<.csv>), SQLite (C<.sql>), or JSON (C<.json>).
339  
340 =item *
341  
342 B<Print> -- the Print button, to the right of Refresh, prints the header row
343 and every row of the current view, not just the page on screen: the rows the
344 Export panel would save (after joins, filters, calculated and cross-row
345 columns, and dedup), read from C<GET /export?format=json>, with the columns in
346 the order the table shows them and sorted as it is sorted. Nothing else on
347 the page is printed, and the header row repeats on each printed page. A row
348 deleted from the page with Delete selected is still printed; a deleted column
349 is not.
350  
351 =item *
352  
353 B<URL import> -- C</import> fetches an HTML table from any public URL and
354 renders it in the browser without saving to disc.
355  
356 =item *
357  
358 B<Redis data source> (optional) -- when the C<Redis> or C<Redis::Fast> module
359 is installed, any Redis hash store can be opened as a read-only data table by
360 entering a C<redis:tablename|redis://host:port> spec in the path field or in a
361 join/combine panel. Redis keys must follow the C<tablename:entry> convention
362 used by L<Database::Abstraction>. Because C<Redis> is an optional dependency,
363 the application works fully without it; Redis support activates automatically
364 when the module is available.
365  
366 B<MariaDB and MySQL data source> (optional) -- a table on a server named in the
367 C<mariadb_servers> configuration opens as C<mariadb:server/table>, read-only, with
368 the columns in the table's own order and a page read with C<LIMIT>. See
369 L</MariaDB and MySQL servers>.
370  
371 =back
372  
373 =head2 UTF-8 and Encoding
374  
375 All file data is returned as Perl character strings. CSV/PSV/TSV files are
376 read by L<Text::xSV::Slurp> or L<DBD::CSV>, both of which pass bytes
377 through without re-encoding; the application serves the resulting page as
378 C<text/html; charset=UTF-8>, so full Unicode is displayed correctly as
379 long as the source file itself is UTF-8. A UTF-8 byte-order mark at the
380 start of a CSV, TSV, PSV or JSON file (Excel's "CSV UTF-8" writes one) is
381 read past, so it is not part of the first column's name.
382  
383 Filter values (C<f=col:op:val>) are decoded from the URL by Mojolicious
384 before reaching the controller and are compared against cell values as
385 Perl character strings. Unicode characters in filter values are therefore
386 supported transparently.
387  
388 URL-imported HTML tables are fetched with L<Database::BI::SafeUA> (see
389 L</Which addresses it may fetch>);
390 L<Database::Abstraction> uses the page's own charset declaration to decode
391 the body. If the remote page declares an incorrect charset, cell values
392 may contain mojibake -- this is a limitation of the source data, not the
393 application.
394  
395 =head1 SYNOPSIS
396  
397 B<Start the development server (restarts automatically when you edit a file):>
398  
399 script/database-bi
400  
401 C<morbo -l http://127.0.0.1:3000 script/database-bi> does the same thing (a bare
402 C<morbo script/database-bi> listens on every interface, so it starts only with a
403 password set), and
404 C<script/database-bi daemon> starts a plain server with no file watcher; any
405 other Mojolicious command word (C<routes>, C<get>, ...) is passed to the
406 application.
407  
408 B<Start it showing one database:>
409  
410 script/database-bi data/store.db
411  
412 The home address then shows that database, and its Home link leads to the usual
413 list. Only one can be named: more than one is an error (exit status 2), because
414 a join cannot be given on the command line, so open one and use Merge or Combine
415 in the browser. A file that is missing, cannot be read or is of an unsupported
416 type is the same error, before the server starts. A remote path, Redis spec or
417 MariaDB spec is accepted as it would be by C</open>, and is checked when it is first shown.
418 It listens on port 3000 of this machine only (C<http://127.0.0.1:3000>); see
419 L</Who can reach the server> to change the port or open it to other machines.
420 A plain server does the same with
421 C<BI_OPEN=/full/path/store.db script/database-bi daemon>; a file named after
422 C<daemon> is ignored.
423  
424 B<Start the production server:>
425  
426 hypnotoad script/database-bi # start, or restart with no downtime
427 hypnotoad -s script/database-bi # stop
428  
429 It listens on port 8080 of this machine only and ignores C<MOJO_LISTEN>; set the
430 port and anything else in C<database_bi.conf>, under
431 C<< hypnotoad => { listen => ['http://*:8080'] } >> (C<*> opens it to every machine).
432 It keeps its process id in F<script/hypnotoad.pid> unless C<pid_file> says
433 otherwise. It takes no database on its command line: use
434 C<BI_OPEN=/full/path/store.db hypnotoad script/database-bi>.
435  
436 B<Use a different data directory:>
437  
438 # In database_bi.conf (create this file in the same folder as script/):
439 { data_dir => '/home/user/data' }
440  
441 B<Change the language used for templates:>
442  
443 # In database_bi.conf:
444 { data_dir => 'data', language => 'fr', platform => 'web' }
445 # Then create templates/web/fr/ and put your French .html.tt files there.
446  
447 B<American spelling:> a browser that asks for English (United States) gets the
448 pages from C<templates/web/en-us/>; set C<< language => 'en-us' >> to make that
449 the default for every browser that names no other English.
450  
451 B<Run the test suite to verify everything is working:>
452  
453 make test
454  
455 B<Generate the Makefile for the first time or after editing Makefile.PL:>
456  
457 perl Makefile.PL
458  
459 =head1 ROUTES
460  
461 =over 4
462  
463 =item C<GET />
464  
465 Scans C<data_dir>, renders a card grid of available tables.
466  
467 When the server was started with the name of a database (see L</SYNOPSIS>), a
468 request from outside the application is redirected to C</open> for that
469 database. A request whose C<Referer> is this server, such as the Home link, gets
470 the card grid, so a browser that withholds C<Referer> always sees the database.
471  
472 =item C<GET /tutorial>
473  
474 Renders the tutorial that is part of the application. The chapters are data
475 in L<Database::BI::Tutorial>, not in this POD.
476  
477 =item C<GET /login>, C<POST /login>, C<POST /logout>
478  
479 The login, when C<password_hash> is set (see L</Logging in>). C<GET /login>
480 shows the form (with C<return=>, the page to go back to: a path on this site),
481 C<POST /login> takes C<password=> and C<return=>, and C<POST /logout> ends the
482 session. With no password set, C</login> says there is nothing to log in to.
483  
484 =item C<GET /preferences>
485  
486 Sets the reader's preferences: the theme, one of light, dark or the same as the
487 device (the default), and the rows a table or pivot shows on a page (25, 50,
488 100, 200 -- the default -- or All). The theme is kept in the browser's
489 C<localStorage> and the page size in its cookie C<bi_per_page>, because the
490 server reads it (a value other than 25, 50, 100, 200 or 0 is ignored, and the
491 page is 200 rows); neither is stored on the server, and both apply to every page.
492 Saved charts, printed pages and embedded canvases stay light. The Preferences
493 link at the top of each page passes C<back=> (that page), so the breadcrumb
494 here leads back to it.
495  
496 =item C<GET /view/:table>
497  
498 Opens the named table from C<data_dir>. Accepts C<?f=col:op:val>
499 (repeatable) to pre-filter results, and C<?range=name&rcol=column> to keep
500 only a relative window of rows (see L</Relative time ranges>).
501  
502 A long table is shown a page at a time: C<?page=> is the page, and
503 C<?per_page=> its size (else the reader's preference, else 200; up to 2000;
504 C<0> is every row on one page, and then C<page=> is ignored). The page says
505 "Page 2 of 7", and its Rows per page list (25, 50, 100, 200, All) changes the
506 size, keeping the first row on screen. A pivot pages its row groups the same
507 way.
508  
509 C<?f=col:between:lo:hi> keeps the rows whose C<col> lies from C<lo> to C<hi>,
510 both included, whichever bound is written first. Two numbers (a range of
511 years, C<Year:between:1880:1900>) compare as numbers; two dates or timestamps
512 compare as times (C<when:between:2026-11-01T09:00:00:2026-11-01T17:00:00>
513 splits at the colon between them); any other pair compares as text, ignoring
514 case. The filter row offers it for date columns (two
515 date boxes) and number columns (two number boxes).
516  
517 A name that is not a table in C<data_dir> is a C<404> page that says so ("There
518 is no table called ..."), with the breadcrumb and site navigation; it does not
519 show the data directory.
520  
521 =item C<GET /browse>
522  
523 Filesystem navigator. Accepts C<?path=> to set the starting directory
524 (defaults to C<$HOME>).
525  
526 =item C<GET /open>
527  
528 Opens any supported file by absolute path (C<?path=>). Accepts C<?f=>
529 filters. When the file is a multi-table SQLite database, passing
530 C<?it=E<lt>tableE<gt>> selects a specific internal table instead of the
531 auto-detected first one.
532  
533 =item C<GET /join>
534  
535 Performs one or more left joins and renders the merged table. Parameters:
536  
537 l=<spec> left table spec (see below)
538 j=<spec>|<lk>|<rk> join step (repeatable): right-spec, left key, right key
539 c=<spec> table to stack after the joins (repeatable; see /combine)
540 f=<col>:<op>:<val> result filter (repeatable)
541 range=<name> relative time range, with rcol=<column> (see below)
542  
543 Spec formats:
544  
545 table:name named table from data_dir
546 table:file/table a table inside a multi-table SQLite file
547 (or a list in a JSON file) in data_dir
548 path:/abs/path/to/file.csv absolute local path
549 path:/abs/path/to/file.db#table one table of such a file anywhere else;
550 a file whose own name has a # is that file
551 /../hostname/dir/file.csv remote file via SFTP
552 redis:tablename|redis://host:port Redis hash store (requires Redis module)
553 mariadb:server/table a table on a server in mariadb_servers
554 mariadb:server lists that server's tables
555  
556 =item C<GET /api/columns>
557  
558 Returns C<{ "columns": [...] }> for a table (C<?table=name> or
559 C<?table=file/table> for a table inside a multi-table SQLite file) or file
560 (C<?path=/abs/path>, or C<?spec=path:/abs/path#table> for one of its tables). Used by the join UI to populate the right-key
561 dropdown without a page reload.
562  
563 =item C<GET /export>
564  
565 Exports the current logical view (same C<l=>, C<j=>, C<f=> parameters as
566 C</join>) as a file download. Additional parameter:
567  
568 format=csv (default) - RFC 4180 CSV; UTF-8; CRLF line endings
569 format=sqlite - SQLite 3 database with a single table named "data"
570  
571 The download filename is derived from the left table label with
572 non-alphanumeric characters replaced by underscores.
573  
574 =item C<POST /export>
575  
576 Writes the current logical view to a chosen filesystem path.
577 Body params: C<l=>, C<j=>, C<f=> (same as GET), plus
578 C<dir=> (target directory) and C<filename=> (name including extension;
579 extension determines format: C<.csv> or C<.sql>).
580 Returns JSON C<{ saved: "/abs/path" }> or C<{ error: "..." }>.
581  
582 =item C<GET /api/dirs>
583  
584 Returns a JSON directory listing (subdirectories only) for the export
585 panel's inline directory browser. Accepts C<?path=> (defaults to
586 C<$HOME>). Returns C<{ path, parent, dirs: [{name, path}] }>.
587  
588 =item C<GET /api/stat>
589  
590 Returns filesystem metadata for a file path (C<?path=>).
591 Returns C<{ exists, path, mtime, size }>. For SQLite files
592 (C<.sql>, C<.sqlite>, C<.sqlite3>, C<.db>) an additional C<table_count>
593 integer is included when the file is a valid SQLite database; the field
594 is omitted for Berkeley DB or other non-SQLite payloads.
595 If the file does not exist, C<exists> is C<false> and the remaining
596 fields are absent (HTTP 200). Returns HTTP 400 when C<path> is missing.
597  
598 =item C<POST /upload>
599  
600 Accepts a multipart file upload (field name: C<file>), validates the
601 extension, saves to a managed C<.uploads/> subdirectory under the app
602 home, and returns JSON C<{ url, path }>.
603  
604 =item C<GET /import>
605  
606 Fetches an HTML table from a public URL (C<?url=>) and renders it as a
607 data grid. A URL that leads to this machine or a private network is refused
608 (see L</Which addresses it may fetch>). An optional C<?t=> parameter (zero-based integer) selects
609 which HTML table on the page to display when the page contains multiple
610 tables.
611  
612 =item C<GET /tables>
613  
614 Schema browser for multi-table SQLite databases. Accepts C<?l=E<lt>specE<gt>>
615 (same C<table:name> or C<path:/abs/path> spec as C</join>). Returns a card
616 grid listing every internal table with its columns; each card links to
617 C</open?path=...&it=E<lt>tableE<gt>>. Redirects to 404 when the spec is
618 absent, invalid, or the resolved file is not a multi-table SQLite database.
619  
620 =item C<GET /combine>
621  
622 Stacks rows from two or more tables vertically (UNION ALL) into a unified
623 view. All columns from all sources appear as headers; cells are blank
624 where a source file lacks a column. Parameters:
625  
626 l=<spec> left table: "table:name" or "path:/abs/path"
627 j=<spec>|<lk>|<rk> join step applied to the left table first (repeatable)
628 c=<spec> additional table to stack (repeatable)
629 f=<col>:<op>:<val> result filter applied after combining (repeatable)
630  
631 =item C<GET /graph>
632  
633 Renders a D3.js v7 zoomable line chart of any two columns. Parameters:
634  
635 l=<spec> left table (required)
636 x=<col> X-axis column name (required; any type, shown as labels)
637 y=<col> Y-axis column name (required; must be numeric after stripping
638 currency symbols and commas; accounting-notation negatives
639 like (1,234.56) are handled automatically)
640 span=<n><unit> bucket x into time buckets (optional; see "Time buckets")
641 agg=<fn> aggregate y per bucket, which is what makes this a
642 timechart (optional; default: one point per row)
643 series=<col> one line per value of this column (optional)
644 back=<url> URL for the second breadcrumb link (optional; default "/")
645 back2=<url> URL for the outermost breadcrumb link when three levels are
646 needed (optional; e.g. /tables?l=... for the schema browser)
647 back2_label=<text> Label for the back2 link (optional; default "Back")
648 j=, f=, d= pipeline params (same as /join)
649  
650 A one-line chart offers check boxes above it for reference lines: Min, Avg,
651 Max, and Trend, the straight line closest to the points (least squares, by
652 L<Statistics::LineFit>), labelled with its slope per point and its R squared
653 (a flat series is slope 0 and R squared 1: the line passes through every
654 point).
655 The points are evenly spaced along the axis, so the fit is of Y against each
656 point's place, not its label. After a zoom, every line is worked out again
657 from the points in view. None is offered on a chart with C<series=>, and Trend
658 needs two points.
659  
660 =item C<GET /pie>
661  
662 Renders a D3.js v7 animated pie chart grouped by a category column.
663 Clicking a slice or legend entry navigates to the table view filtered to
664 that category. Parameters:
665  
666 l=<spec> left table (required)
667 cat=<col> category column to group by (required)
668 val=<col> numeric column to sum per category (required)
669 donut=1 show a hole in the centre (optional)
670 back=<url> URL for the second breadcrumb link (optional; default "/")
671 back2=<url> URL for the outermost breadcrumb link (optional)
672 back2_label=<text> Label for the back2 link (optional; default "Back")
673 f= result filters applied before aggregating (repeatable)
674  
675 =item C<GET /heatmap>
676  
677 Renders a D3.js v7 grid heatmap with two categorical axes. Each cell
678 colour encodes a summed or counted numeric value. Parameters:
679  
680 l=<spec> left table (required)
681 x=<col> X-axis column name (required; categorical)
682 y=<col> Y-axis column name (required; categorical)
683 val=<col> numeric column to sum per cell (optional; omit to count rows)
684 scheme=<name> colour scheme: YlOrRd Blues Greens Purples RdPu YlGnBu
685 (optional; default YlOrRd)
686 show_val=1 print the value inside each cell (optional)
687 back=<url> URL for the second breadcrumb link (optional; default "/")
688 back2=<url> URL for the outermost breadcrumb link (optional)
689 back2_label=<text> Label for the back2 link (optional; default "Back")
690 j=, f=, d= pipeline params (same as /join)
691  
692 =item C<GET /bar>
693  
694 Renders a D3.js v7 bar chart grouped by a categorical column. Parameters:
695  
696 l=<spec> left table (required)
697 cat=<col> category (X-axis) column (required)
698 val=<col> numeric column to sum per category; omit or use __count__
699 to count rows instead of summing (optional)
700 orient=h horizontal bars (default: vertical)
701 sort=value|label sort bars by descending value or ascending label
702 max=N collapse bars beyond the top-N into an "Other" bar
703 back=<url> URL for the second breadcrumb link (optional; default "/")
704 back2=<url> URL for the outermost breadcrumb link (optional)
705 back2_label=<text> Label for the back2 link (optional; default "Back")
706 j=, f=, d= pipeline params (same as /join)
707  
708 =item C<GET /folder>
709  
710 Hierarchical folder view. Groups a data table by the letter-prefix of an
711 order-number column (e.g. A10, CT4, B3) and shows one card per prefix group.
712 Clicking a card with sub-prefixes drills down; clicking a leaf shows the
713 matching data rows. Parameters:
714  
715 l=<spec> left table (required)
716 col=<col> order-code column (auto-detected when absent)
717 prefix=<str> current drill-down prefix (top level when absent)
718 back=<url> URL for the back link (optional)
719 f= result filters (repeatable)
720  
721 =item C<POST /uploads/clear>
722  
723 Deletes every file from the C<.uploads/> staging directory. Returns JSON
724 C<{ "freed": <bytes>, "count": <n> }>. No request body is needed.
725  
726 =item C<GET /pivot>
727  
728 Renders a pivot/crosstab table. Parameters:
729  
730 l=<spec> left table (required)
731 rows=col1,col2 row-dimension column names (required)
732 cols=col3 column-dimension names (optional)
733 m=col:fn:alias measure spec, repeatable (fn: sum|avg|min|max|count|count_distinct
734 for the aggregates the backend computes, median|stdev|first|last|range
735 for those computed in Perl from the group's values, or p<N> for
736 the Nth percentile -- p50 is the median, and p1 to p100 are accepted)
737 totals=1 include subtotals column and grand-total row
738 topn=N keep only the top-N row groups
739 topn_col=<alias> measure alias to rank by (required when topn= is set)
740 max_cols=N collapse tail column combos beyond top-N into "Other"
741 format=csv|sqlite|json|xlsx stream result as a download
742 back=<url> URL for the back breadcrumb link
743 f= result filters applied before aggregating (repeatable)
744  
745 =item C<GET /stats>
746  
747 Per-column statistics view. Returns one row per column: row count, null
748 count, distinct count, first and last, and (for numeric columns) min, max,
749 range, mean, median, the percentile named by C<p=N> and sample standard
750 deviation. Parameters: C<l=E<lt>specE<gt>> (required), C<f=> filters and
751 C<p=N> for the percentile column (1 to 100, default 95).
752  
753 =item C<GET /scatter>
754  
755 D3.js v7 scatter/bubble plot. Parameters:
756  
757 l=<spec> left table (required)
758 x=<col> X-axis column (required; numeric)
759 y=<col> Y-axis column (required; numeric)
760 series=<col> categorical colour dimension (optional)
761 size=<col> numeric bubble-radius dimension (optional)
762 scheme=<name> colour scheme when series= is set: tableau10 (default),
763 category10, set2, set3 or paired; any other is a 400
764 min_r=N smallest bubble radius in px with size= (1-40, default 3)
765 max_r=N largest bubble radius in px with size= (1-40, default 15)
766 back=<url>, back2=<url>, back2_label=<text> breadcrumb links
767 f= result filters (repeatable)
768  
769 =item C<GET /stacked>
770  
771 D3.js v7 stacked bar chart. Parameters:
772  
773 l=<spec> left table (required)
774 cat=<col> X-axis categories (required)
775 series=<col> stack segments (required; seg= is a backwards-compatible alias)
776 val=<col> measure column (optional; omit to count rows)
777 scheme=<name> colour scheme (optional)
778 back=<url>, back2=<url>, back2_label=<text> breadcrumb links
779 f= result filters (repeatable)
780  
781 =item C<GET /combo>
782  
783 D3.js v7 combo bar+line chart. Parameters:
784  
785 l=<spec> left table (required)
786 cat=<col> X-axis categories (required; x= is a backwards-compatible alias)
787 bar_val=<col> bar measure column (required)
788 line_val=<col> line measure column (required)
789 scheme=<name> bar and line colours: the first two of tableau10,
790 category10, set2, set3 or paired (optional; steel blue and
791 red without it, and for any other name)
792 back=<url>, back2=<url>, back2_label=<text> breadcrumb links
793 f= result filters (repeatable)
794  
795 =item C<GET /treemap>
796  
797 D3.js v7 treemap. Parameters:
798  
799 l=<spec> left table (required)
800 group=<col> tile label column (required; cat= is a backwards-compatible alias)
801 size=<col> tile area column, summed (optional, counting rows without it;
802 val= is a backwards-compatible alias)
803 topn=N keep only the top-N tiles; remainder merged into "Other"
804 scheme=<name> colour scheme (optional)
805 color=<col> shade each tile by this column instead (optional), folded
806 over the tile's rows; "Other" is folded over the rows it holds
807 color_agg=F how color= is folded: avg (default), sum, min, max, median or
808 any other aggregate function; an unknown one is a 400
809 color_scheme=S scale for color=: YlOrRd (default), Blues, Greens, Purples,
810 RdPu or YlGnBu; any other name is YlOrRd
811 back=<url>, back2=<url>, back2_label=<text> breadcrumb links
812 f= result filters (repeatable)
813  
814 =item C<GET /histogram>
815  
816 D3.js v7 histogram with server-side binning. Parameters:
817  
818 l=<spec> left table (required)
819 col=<col> numeric column to bin (required)
820 bins=N bin count (default 10; clamped to range 1--200)
821 scheme=S bar colour: the first colour of tableau10, category10, set2,
822 set3 or paired (optional; steelblue without it, and for any
823 other name)
824 back=<url>, back2=<url>, back2_label=<text> breadcrumb links
825 f= result filters (repeatable)
826  
827 =item C<GET /wordcloud>
828  
829 Word cloud of a text column, drawn with HTML::TagCloud. Each row contributes
830 its words once, so a word's size is the number of rows that use it rather than
831 the number of times one long note repeats it. Common English words are
832 removed. Parameters:
833  
834 l=<spec> left table (required)
835 col=<col> text column to read (required)
836 topn=N number of different words to show (default 50; range 1--500)
837 minlen=N shortest word kept (default 3; range 1--64)
838 stop=<words> extra words to ignore, comma or space separated (optional)
839 back=<url>, back2=<url>, back2_label=<text> breadcrumb links
840 f= result filters (repeatable)
841  
842 With C<back=>, each word links to the rows that contain it.
843  
844 The same cloud is available as a card on a saved canvas: C<POST
845 /api/analyses/:id/visuals> takes C<< kind => wordcloud >> with C<encode.col>,
846 and the card counts the words of its own rows -- its filters, the page's
847 cross-filter and its own options -- so a canvas that is narrowed to one
848 region shows the words of that region.
849  
850 =item C<GET /map>
851  
852 OpenStreetMap map of the filtered view, drawn with HTML::OSM. A row's position
853 comes from a location column that is geocoded (C<loc=>, one lookup per distinct
854 value, remembered once in the model store and shared with every dataset that
855 names it) or from coordinate columns (C<lat=> and C<lon=>). Rows sharing a
856 position become one marker. When some rows cannot be plotted, the note that
857 says so counts them by reason (empty, not a place name, not found, or not looked
858 up yet), links to all of them in the table, and opens onto a list of their
859 places, each with its number of rows, why it was not plotted and a link to
860 those rows. Markers are clustered with
861 Leaflet.markercluster, so a dense map draws a count at low zoom and splits it
862 as it is zoomed in. Clustering is always on. The map has a scale bar, opens
863 on every marker unless C<zoom=> is given, and draws its key (colours, and
864 bubble sizes) on the map; the colours listed are those of the markers in view,
865 so the key changes as the map is zoomed and moved.
866  
867 Places that are not in the cache yet are looked up after the page is shown.
868 The map draws what the cache already knows, and a progress bar names each
869 place as it is looked up ("Looking up 5 of 10: 10 Downing St, London"). Each
870 answer is saved at once, through C<POST /geo/lookup>, and the map is drawn
871 again when the last one is in. Cancel stops the lookups and draws the map with
872 the places found so far. The places left are listed as not looked up yet, with
873 a link to look them up. Without JavaScript, or for a client that is not a
874 browser, the page waits for the lookups instead (C<geocode=wait>). Parameters:
875  
876 l=<spec> left table (required)
877 loc=<col> column of places to geocode (required unless lat= and lon= are);
878 repeat it for a place written across columns, which are joined
879 with ", " in that order (loc=Address&loc=Country)
880 loc_text=<text> text added to the end of each place before it is looked
881 up, after ", " (loc=County&loc_text=Virginia, USA looks up
882 "Accomack, Virginia, USA"); one line, at most 200 characters;
883 a row with no place is still not looked up
884 lat=<col> latitude column, -90..90
885 lon=<col> longitude column, -180..180
886 popup=<col> column shown in the marker popup (repeatable; default every
887 column but the place or coordinates)
888 max=N markers to draw (default map_max_markers, 2000)
889 zoom=N initial zoom 0..19 (default: fit the markers)
890 max_zoom=N tile layer maxZoom 0..28 (default: map_max_zoom, if set)
891 tiles=<name> named tile source from the tile_sources config key
892 heat=1 add a heatmap layer over the markers
893 bounds=<level> draw the outline of the area around each marker: country,
894 state, county, city, town or neighbourhood
895 weight=<col> numeric heatmap intensity column (normalised to 0..1)
896 size=<col> numeric bubble-radius column; markers become circles
897 color=<col> colour markers from a column
898 color_rule=<op>:<value>:<colour> repeatable; op is gt, ge, lt, le, eq or ne
899 color_default=<colour> fallback colour (default #3388ff)
900 series=<col> colour markers by category (not with color=)
901 scheme=<name> palette for series=: tableau10, category10, set2, set3, paired
902 geocode=wait look up the places not cached yet before the page is sent
903 (the default for a client that is not a browser)
904 geocode=cached look nothing up: plot what the cache knows (where Cancel leads)
905 back=<url>, back2=<url>, back2_label=<text> breadcrumb links
906 f= result filters (repeatable)
907  
908 With C<bounds=> (the map panel's "Show boundaries"), the map draws the outline
909 of the area each marker is in, with a checkbox on the map to hide them. The
910 level says how large an area:
911  
912 country the country
913 state a US or Indian state, a Canadian province, a New Zealand
914 region, England or Scotland
915 county the division below a state; see below
916 city a city, or the district named after it: Toronto, Manchester,
917 the district of Canterbury, Hamilton City in New Zealand.
918 Where OpenStreetMap has no city round a point, or has it only
919 as a point (York, Pune), the authority at the county's rank
920 town a town or a city ward: Margate, Toronto Centre
921 neighbourhood a neighbourhood, suburb or civil parish
922  
923 OpenStreetMap has a shape for every country, state and county, but not for
924 every smaller place: many towns and most suburbs are mapped as a single point
925 (Cliftonville and Garlinge in Margate are), and a marker there has no outline at
926 that level. The list under the map says which markers those are. Larger areas
927 are simplified more, so an outline is a few kilobytes at any level.
928  
929 The outlines come from Nominatim's reverse lookup, which is sent each
930 marker's position rounded to three decimal places (about 110 metres), and only
931 when C<map_geocode_online> allows it. Each outline is remembered under
932 C<model_dir> (C<boundary-cache>), with the points found in it, so a point inside
933 an outline already known costs no lookup; each level is remembered apart. A
934 county is the division of a
935 country below its states, whose place rank in Nominatim depends on the country:
936 12 (a US county or independent city, an English county, a German Landkreis), or
937 10 for India, Bangladesh, China and Spain (district, prefecture, province), or 8
938 for Kenya. Australia, Brazil and Japan have nothing at that level. Config
939 C<map_bounds_ranks> (C<< { lk => 10 } >>: two-letter codes, ranks 8, 10 or 12)
940 adds or changes countries for the county level; startup refuses anything else. Each reply names its
941 country, so a point is asked at the rank of the last country seen, and again
942 at its own when that differs.
943 Outlines not known yet are looked up as places are: by the progress bar, through
944 C<POST /geo/boundary>, or in the request for C<geocode=wait>; at most
945 C<map_geocode_online_max> in one request. The checkbox needs L<HTML::OSM> 0.15;
946 with 0.14 the outlines are always shown.
947  
948 With C<heat=1> as well, each outline is coloured by what is in it: the
949 C<weight=> column summed over every row inside it (or its rows counted, with no
950 C<weight=>), from green for the smallest total to red for the largest. The
951 colour follows the size of the total on a logarithmic scale: each step up the
952 colours is the same multiple, so totals close in size share a colour and skewed
953 totals (one county with 500 times the people of another) still spread across
954 the scale. When a total is zero or less the colours go by rank instead. With
955 one outline, or totals all alike, there is nothing to compare, so the outlines
956 stay grey. Each outline's popup gives its total, and the line under the title
957 says what the colours mean. The checkbox, the key and that line call an
958 outline what its country does (a district in India, a province in Spain, an
959 area when the map mixes them). With L<HTML::OSM>
960 0.16 a key in the map's bottom right corner lists each colour, highest first,
961 with the totals it covers, rounded to two figures ("30,000 to 37,000"); it hides with the outlines'
962 checkbox.
963  
964 When the markers are coloured (C<color=>), the outlines take their colours
965 instead, with or without C<heat=1>, so a border never disagrees with the
966 markers inside it. With L<HTML::OSM> 0.17 each coloured outline, here and on the heat scale, is
967 drawn just inside its own edge, so where two counties of different colours
968 meet the border is a line twice as wide, one colour on each side. An outline
969 whose markers differ gets a line of each of their colours, side by side, the
970 colour most of its markers have outermost. An older HTML::OSM draws each
971 outline along its edge in that first colour alone, one over another where they
972 meet. Outlines are simplified to about 100 metres one at a time, so zoomed
973 well in, neighbours' lines can part or overlap a little. The markers' key explains the colours, so the outlines have none of
974 their own.
975  
976 A map can have fewer outlines than markers, and a list under it says why: each
977 outline that holds more than one marker, with the places in it, and the markers
978 in no outline at all, each with a link to its rows in the table. Often that is
979 as it should be (several shops in one county). Where the places were looked up
980 by name it can also be a wrong answer, since a name may be ambiguous, and the
981 list says so; C<loc_text=>, or latitude and longitude columns, make a lookup
982 surer. The list names at most 50 outlines, the fullest first. While some
983 places have no outline yet (not looked up, or OpenStreetMap did not answer),
984 the note above the map links to their rows and, for those not looked up, to
985 the map again to look them up.
986  
987 Outlines are written into the page, about 7 KB each before compression, so a
988 map of a state's counties is well under a megabyte, and one of every county in
989 a large country would be very large: filter the rows first.
990  
991 C<shade=E<lt>columnE<gt>> (Colour shade, in the map panel) fills each outline in a
992 shade of one colour, the darker the larger that column's total over the rows
993 inside it, as a census map is drawn. There are five shades, placed on the same
994 logarithmic scale, from a light tint up to the colour itself: C<shade_color=>
995 (a colour name or C<#rrggbb>) chooses it, orange by default. A key lists each
996 shade in use, darkest first, with the totals it covers, as filled boxes with
997 L<HTML::OSM> 0.18 (lines before), and each outline's popup, which now opens from
998 anywhere inside it, gives its total. The lines stay grey, or take the markers'
999 colours: with a shade the heat layer no longer colours them, since the fill
1000 says the same. With one outline, or totals all alike, nothing is filled.
1001  
1002 When the markers are coloured (C<color=>) and no C<shade_color=> is given, each
1003 outline is shaded in its own markers' colour instead, each colour a scale of its
1004 own: the further a total is into what the colour stands for, the stronger the
1005 shade. For a rule on the shaded column that reads "less than" (C<lt>, C<le>)
1006 that means the smaller total: with C<color_rule=lt:20000:green> a county of
1007 2,000 is a stronger green than one of 19,000, while under
1008 C<color_rule=gt:100000:red> the largest county is the strongest red. The key
1009 lists every shade by size, the largest totals at the top, so it runs from the
1010 strongest red down to the strongest green. It then stands for the markers'
1011 colours as well, so the markers' own key of those colours is left out while the outlines are
1012 shown. With L<HTML::OSM> 0.19 it comes back when the reader hides the outlines
1013 with their checkbox, and goes again when they are shown; an older HTML::OSM
1014 leaves it out throughout. The markers' key always stays when the shade is of
1015 another column or of one colour, where nothing else says what the marker
1016 colours mean. Give C<shade_color=> to shade
1017 every outline in one colour as before.
1018  
1019 A marker stands for every row at its place, and its popup lists them all: a
1020 value every row shares is shown once ("Person: Valentin Puntar"), and the
1021 columns that differ are a small table, one line a row. The table shows at most
1022 C<map_popup_rows> rows (default 10) in a box that scrolls, so a busy place does
1023 not cover the map; the rest are counted, with a link to them in the table the
1024 map came from.
1025  
1026 An unknown C<tiles=> or C<scheme=> is a 400, as is an unknown column, C<color=>
1027 together with C<series=>, or a transposed lat/lon pair. The page never shows
1028 the L<HTML::OSM> Nominatim search box (C<search =E<gt> 0> is always passed), so
1029 nothing typed on the map is sent anywhere. With C<loc=>, a place the local
1030 data does not know is sent to Nominatim, as the place name alone; set
1031 C<map_geocode_online> to 0 to keep every lookup local. L<HTML::OSM> 0.14 or
1032 later is the renderer; an older optional install still returns the page with a
1033 warning.
1034  
1035 =item C<GET /line>
1036  
1037 Alias for C<GET /graph>. All C</graph> parameters accepted.
1038  
1039 =item C<GET /canvases>
1040  
1041 Lists the saved canvases, as cards that open them, with Import canvas. It is
1042 the main navigation's Canvases link; the home page shows the same list,
1043 without Import canvas. With
1044 no canvases, the page says how to make one. A canvas's breadcrumb leads back
1045 here unless its C<back=> names another page.
1046  
1047 =item C<GET /a/:id>
1048  
1049 Renders a saved canvas (analysis) as a full multi-visual page. Accepts
1050 cross-filter params C<?cf=E<lt>colE<gt>&cv=E<lt>valE<gt>>, drill-through
1051 params C<?dd=E<lt>colE<gt>&dl=E<lt>levelE<gt>>, parameter overrides
1052 C<?p.E<lt>nameE<gt>=E<lt>valueE<gt>>, and a relative time range
1053 C<?range=E<lt>nameE<gt>&rcol=E<lt>columnE<gt>> that narrows every card on the
1054 canvas (see L</Relative time ranges>).
1055  
1056 =item C<GET /api/analyses>
1057  
1058 Returns C<{ analyses: [{id, name, datasets, updated}] }>.
1059  
1060 =item C<POST /api/analyses>
1061  
1062 Creates a new analysis from a JSON request body. Required fields: C<name>
1063 and C<datasets> (array; each entry must have a C<spec> starting with
1064 C<table:>, C<path:>, C<url:> or C<redis:>, or being C<mariadb:server/table>).
1065 Returns C<201 { id, created: 1 }>.
1066  
1067 =item C<GET /api/analyses/:id>
1068  
1069 Returns the full analysis document.
1070  
1071 =item C<PUT /api/analyses/:id>
1072  
1073 Full replacement of an analysis document.
1074  
1075 =item C<PATCH /api/analyses/:id>
1076  
1077 Partial update; merges the supplied top-level keys. C<id> and C<created>
1078 are immutable. The C<layout> key places the visuals on the canvas grid: an
1079 array of C<{ visual, x, y, w, h }>, where C<visual> indexes the C<visuals>
1080 array and C<x>, C<y>, C<w> and C<h> are 1-based grid columns and rows (the
1081 grid is 12 columns wide, C<x + w - 1> cannot exceed 12, and one visual may
1082 span at most 6 rows). Positions are
1083 stored as given - a gap is a gap - but two visuals may not share a cell, so
1084 an overlapping set is rejected with C<400> and the stored layout is left
1085 alone.
1086  
1087 =item C<POST /api/analyses/:id/visuals>
1088  
1089 Appends one visual to a saved canvas. Body: C<{ kind, encode, options,
1090 title, query }>, with C<kind> one of C<table>, C<bar>, C<pie>, C<line>,
1091 C<graph>, C<scatter>, C<heatmap>, C<stacked>, C<combo>, C<treemap>,
1092 C<histogram>, C<wordcloud>, C<map> or C<text>. A C<text> card reads no data, and
1093 its C<options> are C<text> (required, up to 20,000 characters), C<font>
1094 (C<arial>, C<georgia>, C<times>, C<verdana>, C<trebuchet> or C<courier>), C<size>
1095 (8 to 96 points), C<colour> and C<background> (C<#rrggbb>; leaving out C<colour>
1096 follows the theme), C<align> (C<left>, C<center> or C<right>), C<bold>, C<italic>
1097 and C<plain>. The text is shown as plain text, never as markup. A map's C<encode> names either C<loc> (a place column
1098 to geocode) or C<lat> and C<lon>. A C<wordcloud> card's C<encode> names the
1099 text column to read in C<col>, and its C<options> take the same C<topn>,
1100 C<minlen> and C<stop> the C<GET /wordcloud> page takes, so the two count and
1101 rank words with one function. A C<scatter> card's C<encode> names C<x>
1102 and C<y>, and optionally C<series> (colour by value) and C<size> (bubble size); its
1103 C<options> take C<scheme>, C<min_r> and C<max_r> as C<GET /scatter> does,
1104 except that an unknown C<scheme> draws the default colours. A C<line>
1105 or C<graph> card's C<encode> takes an optional C<series>, one line per value.
1106 A C<histogram> card's C<options> take C<bins> and C<scheme> as C<GET /histogram>
1107 does. A C<treemap> card's C<encode> takes an optional C<color>, with
1108 C<options> C<color_agg> and C<color_scheme> as C<GET /treemap>'s C<color=>,
1109 C<color_agg=> and C<color_scheme=>; an unknown function is the card's error.
1110 A C<combo> card's C<options> take C<scheme> as C<GET /combo> does.
1111 The optional C<query> is the canvas page's
1112 query string: C<cf> (cross-filter), C<c> (calculated columns) and
1113 C<p.E<lt>nameE<gt>> (bound parameter values) are read from it, so a card added
1114 while the canvas is filtered or parameterised is drawn through the same state
1115 as the cards already on screen. The visual is rendered against the canvas's
1116 datasets, joins and calculated fields before it is stored, so an unknown
1117 column or an undrawable visual is rejected with C<400> and nothing is appended.
1118 Returns C<201 { id, visual, kind, title, html, head, nonce, cf_col }>, where
1119 C<visual> is the new visual's index, C<head> is any stylesheet and script the
1120 page must add once to draw the card (Leaflet, for a map), C<nonce> is the
1121 Content-Security-Policy nonce the scripts in C<html> carry (the canvas page
1122 runs only those, giving each its own nonce), and C<cf_col> is the
1123 column the card's marks cross-filter by. The card is left unplaced in
1124 C<layout>; it is auto-placed after the saved cards until the reader moves or
1125 resizes it.
1126  
1127 A C<kind> that aggregates a value column -- C<bar>, C<pie>, C<stacked>,
1128 C<treemap>, C<heatmap> and C<combo> -- reads an optional C<encode.agg>: the
1129 function its value column is aggregated with. It takes C<sum> (the default),
1130 C<avg>, C<min>, C<max>, C<count_distinct>, C<median>, C<stdev>, C<first>,
1131 C<last>, C<range>, or a percentile named C<pE<lt>NE<gt>> from C<p1> to C<p100>
1132 -- the same vocabulary and the same spelling a pivot's C<m=> spec uses, so a
1133 card can show a median, a spread or a percentile where the chart pages it came
1134 from could only sum. C<count> is accepted as well: it counts rows and ignores
1135 the value column. The function is checked whatever the kind is, so a misspelt
1136 one is a C<400> rather than a chart drawn some other way, as is a function that
1137 needs a column (C<median>, unlike C<count>) when the card names no value column
1138 or names the value menu's "Count (no column needed)". A combo aggregates both
1139 its series with the one function. A card's derived title says which function it
1140 used (C<median(amount) by region>); a plain sum keeps the title it has always
1141 had.
1142  
1143 A card may bring a cross-row plan of its own in C<cross_row>, in the spelling
1144 the canvas page's C<cs>, C<by> and C<order> use:
1145 C<< { cs => [ I<col:fn:name[:arg]> ... ], by => I<col>, order => [ I<col:dir> ... ] } >>.
1146 The card then runs that plan instead of the page's, so one card of a canvas can
1147 show a running total while the cards beside it show raw rows, and
1148 C<< { cs => [] } >> is a card that runs no cross-row pass at all -- which is how a
1149 card opts out of the page's. A card that sends no C<cross_row> inherits the
1150 page's plan. The plan is checked against the canvas's columns on every render,
1151 not only here, so a saved spec that no longer names a column is that card's
1152 error while a bad page-level C<cs> is the page's C<400>.
1153  
1154 =item C<DELETE /api/analyses/:id/visuals/:visual>
1155  
1156 Removes a text card; any other kind answers C<400>. Later cards move down one
1157 index, and the layout and any C<cv=> saved in a bookmark move with them.
1158  
1159 =item C<PATCH /api/analyses/:id/visuals/:visual>
1160  
1161 Changes the columns of one stored visual without removing it. Body:
1162 C<{ encode, query }>; C<encode> replaces the visual's encoding in place (for
1163 a map, C<{ loc }> or C<{ lat, lon }>), and the optional C<query> is the
1164 canvas page's query string, read for C<cf>, C<c> and C<p.E<lt>nameE<gt>> so
1165 the redrawn card matches the page it is on. The visual keeps its index, so
1166 its layout position, size and title are unchanged. The replacement is
1167 rendered before it is stored, so an unknown column or an undrawable map is a
1168 C<400> and the saved visual is left alone. Returns
1169 C<{ id, visual, kind, title, html, head, nonce, cf_col }>; C<nonce> is the one
1170 the scripts in C<html> carry, which the canvas page gives its own when it runs
1171 them.
1172  
1173 =item C<DELETE /api/analyses/:id>
1174  
1175 Removes the analysis. Returns C<200 { deleted: 1 }>.
1176  
1177 =item C<GET /api/analyses/:id/export>
1178  
1179 Downloads the analysis document as a C<.json> file.
1180  
1181 =item C<POST /api/analyses/import>
1182  
1183 Creates an analysis from an uploaded C<.json> file (multipart C<file>
1184 field) or a raw JSON request body. Caller-supplied C<id>, C<created>,
1185 and C<updated> are stripped. "Import canvas", on C</canvases>, sends the file
1186 that a canvas's Share panel downloads; that panel also makes the read-only
1187 C</share/:id> link (C<POST /api/analyses/:id/share>) and its C</embed/:id>
1188 code.
1189  
1190 =item C<GET /calc/list>
1191  
1192 Returns C<{ fields: { name: expr } }> for stored virtual calc fields
1193 for a given spec. Accepts C<?l=E<lt>specE<gt>>.
1194  
1195 =item C<POST /calc/save>
1196  
1197 Saves a named calc field to C<.bi/models.json>. Body:
1198 C<l=E<lt>specE<gt>&name=E<lt>identE<gt>&expr=E<lt>expressionE<gt>>.
1199 Returns C<{ saved: 1 }> or C<{ error: "..." }> (400 for hostile input).
1200  
1201 =item C<POST /calc/delete>
1202  
1203 Removes a named calc field. Body:
1204 C<l=E<lt>specE<gt>&name=E<lt>identE<gt>>.
1205  
1206 =item C<GET /groups/list>
1207  
1208 Returns C<{ groups: { label: [vals] } }> for named value groups for
1209 a column. Accepts C<?l=E<lt>specE<gt>&col=E<lt>colE<gt>>.
1210  
1211 =item C<POST /groups/save>
1212  
1213 Saves a named value group. Body: C<l=&col=&group=&values=> (values
1214 is a newline-separated list of raw cell values). The group column,
1215 C<E<lt>colE<gt>_group>, appears in the table page as well as in its charts and
1216 exports.
1217  
1218 =item C<POST /groups/delete>
1219  
1220 Removes a named value group. Body: C<l=&col=&group=>.
1221  
1222 =item C<GET /dates/list>
1223  
1224 Returns C<{ patterns: { col: pattern } }> for stored date-normalisation
1225 patterns. Accepts C<?l=E<lt>specE<gt>>. A column with only a date pattern is
1226 reported as the pattern string; one with a time part as well is reported as
1227 C<{ date, time }>, plus C<as> when the normalised value is filed under a name
1228 of its own.
1229  
1230 =item C<POST /dates/save>
1231  
1232 Saves a date-normalisation pattern for a column. Body: C<l=&col=&pattern=>,
1233 plus optional C<time=> and C<as=>. Supported patterns: C<%m/%d/%Y> (US),
1234 C<%d/%m/%Y> (EU), C<%d-%b-%Y> (DD-Mon-YYYY). With C<time=> (C<%H:%M> or
1235 C<%H:%M:%S>) the column is normalised to a timestamp, C<2026-10-27T14:05:00>,
1236 which is what C<epoch()>, C<span()> and a C<last_24_hours> range can read;
1237 C<as=> names the column the normalised value is written to (default: the
1238 column itself). Without a time part the stored value is the date pattern
1239 string, unchanged, so no existing store needs migrating.
1240  
1241 =item C<POST /dates/delete>
1242  
1243 Removes a date-normalisation pattern. Body: C<l=&col=>.
1244 Omit C<col=> to clear all patterns for the spec.
1245  
1246 =item C<GET /lookups/list>
1247  
1248 Returns C<{ lookups: { name: { file, key, col, fields, default } } }> for the
1249 stored lookups of C<?l=E<lt>specE<gt>>; C<col>, C<fields> and C<default> appear
1250 only when they were given. See L</Stored lookups>.
1251  
1252 =item C<POST /lookups/save>
1253  
1254 Saves a lookup. Body: C<l=&name=&file=&key=>, plus optional C<col=>,
1255 C<fields=> and C<default=>. The file is read when it is saved, so a bad one is
1256 a 400 at once.
1257  
1258 =item C<POST /lookups/delete>
1259  
1260 Removes a lookup. Body: C<l=&name=>. Omit C<name=> to remove every lookup of
1261 the spec.
1262  
1263 =item C<GET /extract/list>
1264  
1265 Returns C<{ extractions: { name: { col, pattern, fields } } }> for the stored
1266 field extractions of C<?l=E<lt>specE<gt>>. See L</Field extraction>.
1267  
1268 =item C<POST /extract/save>
1269  
1270 Saves an extraction. Body: C<l=&name=&col=&pattern=>, plus optional
1271 C<fields=>. The pattern is compiled when it is saved, so a bad one is a 400 at
1272 once. The reply names the columns it will add: C<{ ok: 1, fields: [...] }>.
1273  
1274 =item C<POST /extract/delete>
1275  
1276 Removes an extraction. Body: C<l=&name=>. Omit C<name=> to remove every
1277 extraction of the spec.
1278  
1279 =item C<GET /geo/list>
1280  
1281 Returns C<{ columns: { col: { kind: "lat"|"lon"|"location"|"location_suffix" } } }>
1282 for the saved coordinate-column preferences of the given C<l=> spec, with
1283 C<loc_text> when the text added to each place is remembered too.
1284  
1285 =item C<POST /geo/save>
1286  
1287 Saves a coordinate-column preference. Body: C<l=&col=&kind=>,
1288 where C<kind> is C<lat>, C<lon>, C<location> or C<location_suffix>. C<col=> and
1289 C<kind=> may repeat in pairs, so a latitude and a longitude are saved in one
1290 request, and C<replace=1> forgets the spec's saved columns first. C<loc_text=>
1291 remembers the text added to each place (as C<loc_text=> on C</map>, and refused
1292 as it would be there). The map panel's "Remember columns" sends all of them,
1293 so a new choice replaces the old one; its "Forget remembered columns" calls
1294 C</geo/delete>.
1295  
1296 =item C<POST /geo/delete>
1297  
1298 Removes a coordinate-column preference. Body: C<l=&col=>.
1299 Omit C<col=> to clear all preferences for the spec, and its C<loc_text>.
1300  
1301 =item C<POST /geo/lookup>
1302  
1303 Looks up one place for the map page's progress bar, and saves the answer in
1304 the shared place cache at once. Body: C<place=&sig=>, where C<sig> is the
1305 signature the map page gave the place (any other place is refused with 403),
1306 plus C<online=0> to keep the lookup on this machine. Returns
1307 C<{ place, status, stored, online_failed, messages }>, where C<status> is
1308 C<found>, C<not_found>, C<held> (not looked up now, and not remembered) or
1309 C<refused>. See L</Maps>.
1310  
1311 =item C<POST /geo/boundary>
1312  
1313 Finds the outline of the area around one point, for the map page's progress
1314 bar, and remembers it at once. Body: C<point=&level=&sig=>, where C<level> is
1315 one that C<bounds=> takes,
1316 C<point> is C<lat,lon> rounded to three places and C<sig> the signature the map
1317 page gave it (any other point is refused with 403), plus C<online=0> to ask
1318 nothing. Returns C<{ point, status, stored, online_failed, name }>, where
1319 C<status> is C<found>, C<nowhere> (no such area there, such as the sea) or C<held>
1320 (not settled now, and not remembered).
1321  
1322 =item C<GET /insights>
1323  
1324 Automatic statistical insights for a dataset. Runs the same pipeline as
1325 C</join> then detects outliers (Tukey IQR fence), period-over-period deltas,
1326 top movers, and seasonality (lag-1 autocorrelation). No LLM or network call
1327 is made. Every insight links to a chart or stats view that shows it.
1328 Parameters: C<l=E<lt>specE<gt>> (required), repeatable C<f=> filters.
1329  
1330 =item C<POST /api/ask>
1331  
1332 Natural language to analysis spec (Phase 5.1). Body params: C<l=E<lt>specE<gt>>
1333 (required), C<q=E<lt>questionE<gt>> (required), repeatable C<f=> filters.
1334 Returns C<{ spec, url, valid =E<gt> 1 }> on success. The model receives only
1335 column names and types -- never raw row data or file paths. Returns 503 when
1336 C<ai_api_key> is not configured; 400 for missing params or a question over 500
1337 characters; 422 for an unparseable or invalid-column spec; 502 for a provider
1338 error; 429 once the hour's provider calls are used up (C<ai_calls_per_hour>,
1339 below).
1340  
1341 Every call spends C<ai_api_key>, so each server process makes at most
1342 C<ai_calls_per_hour> provider calls (default 60; C<0> for no limit) in any
1343 hour, counting C</api/ask> and C</api/summary> together. Like the other
1344 routes, both need the login when C<password_hash> is set.
1345  
1346 =item C<POST /api/summary>
1347  
1348 Narrative summary of an analysis (Phase 5.3). Body params: C<l=E<lt>specE<gt>>
1349 (required), repeatable C<f=> filters, C<ai=1> (optional, honours
1350 C<ai_api_key> config). Returns C<{ text, source, cached }>. Two paths:
1351 deterministic template sentences (always available, works offline) and
1352 optional LLM-polished prose when C<ai=1> is passed and C<ai_api_key> is
1353 set. Only aggregate insight facts are sent to the LLM. Results are cached
1354 in-process for one hour keyed on the analysis fingerprint. Once the hour's
1355 provider calls are used up (C<ai_calls_per_hour>), C<ai=1> gives the template
1356 text with C<source =E<gt> 'template_limited'>, not cached.
1357  
1358 =back
1359  
1360 =head2 Breadcrumbs and missing pages
1361  
1362 Every page shows where it is: Home, then each page between Home and this one,
1363 then this page, which is not a link. The trail is read from the page's own
1364 URL -- a chart's C<back=>, drilled rows' C<back2=>, a file's folder -- and each
1365 step is named from its own URL ("Bar chart: amount by region", "sales (region
1366 = North)"). Text from the URL is shown only if it is short and has no markup,
1367 a C<back=> to another site or scheme is not followed, and a chain of C<back=>
1368 links stops after eight steps.
1369  
1370 An address that matches no route, a missing table (C</view/:table>) and a
1371 missing canvas (C</a/:id>) answer C<404> with an HTML page that keeps the
1372 breadcrumb and the site navigation. The JSON API (C</api/...>) still answers
1373 a C<404> with a JSON body.
1374  
1375 A request that is refused -- a chart of a column the view does not have, a
1376 calculated field that does not compile -- answers C<400> with the reason as
1377 plain text. When a browser asked for the page (a C<GET> whose C<Accept> names
1378 C<text/html>), the reason is shown on the same page as a missing one instead,
1379 with the breadcrumb back to the table and a Back button. Scripts, C<fetch()>
1380 and C</api/...> routes still get plain text or JSON.
1381  
1382 =head2 Saved columns that cannot be applied
1383  
1384 Calculated fields, lookups and extractions are saved for a dataset and applied
1385 to every page of it. When the file changes under one -- a lookup file is
1386 deleted, a column an extraction reads is dropped -- its charts and exports
1387 refuse with the reason. The table page (C</view>, C</open>, C</join>,
1388 C</combine>) is still drawn: without that dataset's saved calculated fields,
1389 lookups and extractions, with a note that names the one at fault and buttons
1390 that open the panels to correct or delete it. A mistake in the request's own
1391 C<c=> is still a C<400>.
1392  
1393 =head2 Relative time ranges
1394  
1395 Every data route accepts a named window in place of a pair of dates:
1396  
1397 GET /view/sales?range=last_30_days&rcol=sale_date
1398 GET /a/mycanvas?range=this_quarter&rcol=order_date
1399  
1400 C<range=> names the window and C<rcol=> names the column it applies to. The
1401 window is worked out when the request arrives, so a link, a bookmark or a
1402 saved canvas keeps meaning the same thing whenever it is opened rather than the
1403 dates it happened to resolve to when it was written. The vocabulary is
1404  
1405 last_24_hours the 24 hours ending now, to the second
1406 today, yesterday
1407 last_7_days, last_30_days, last_90_days including today
1408 this_month, last_month
1409 this_quarter, last_quarter
1410 this_year, last_year
1411  
1412 A C<this> period ends today; the completed one is C<last_month>,
1413 C<last_quarter> and so on.
1414  
1415 The range is applied as two ordinary filters on that column --
1416 C<< col:ge:lo >> and C<< col:le:hi >> -- so the table view, every chart, the
1417 pivot, the column statistics, the insights page, the exports and a saved canvas
1418 all show the same rows, and one chip in the toolbar names the window with an
1419 C<x> to remove it. Filters the reader wrote are untouched: the range travels in
1420 the URL as itself, so submitting a panel form or reopening a link does not turn
1421 it into frozen dates.
1422  
1423 The table page offers Time range in its filter bar only when one of the columns
1424 on the page holds dates or date-times (C<YYYY-MM-DD>, the same with a time after
1425 C<T> or a space, or C<M/D/YYYY>) in every non-empty cell, and its column menu
1426 lists only those columns. A range already in force stays in the bar, so it can
1427 be changed or removed.
1428  
1429 A window narrower than a day (C<last_24_hours>) is compared to the second, so
1430 it is exact only on a column that holds a time as well as a date. A
1431 day-granular window compared against a column of timestamps covers each day it
1432 reaches in full, which is what a date filter does on its own as well.
1433  
1434 The filter bar offers the same windows: choose one and the column it applies
1435 to. Entries whose window falls entirely outside the data are disabled, with the
1436 column's own earliest and latest value -- from C<GET /api/range> -- in the
1437 tooltip, so a window that can only show nothing says so before it is chosen.
1438 An unknown C<range=> is a 400 rather than a silently unfiltered view, and a
1439 range with no C<rcol=> is a 400 as well.
1440  
1441 On a saved canvas the range narrows every card, and a card added while one is
1442 active is drawn through it: C<query> in C<POST /api/analyses/:id/visuals>
1443 carries C<range=>/C<rcol=> as it carries C<cf=>.
1444  
1445 =head2 Time buckets
1446  
1447 The aggregating routes bucket the dimension they already take:
1448  
1449 GET /bar?l=table:sales&cat=at&val=amount&span=1h
1450 GET /pivot?l=table:sales&rows=at&m=amount:sum:total&span=1mon
1451 GET /view/sales?span=1d&tcol=at
1452 GET /graph?l=table:sales&x=at&y=amount&span=1h&agg=sum&series=region
1453  
1454 The chart and pivot panels carry a C<Time bucket> menu for it, and the line
1455 graph panel an C<Aggregate> menu beside it and a C<Lines by> menu, which
1456 sends C<series=>.
1457  
1458 C<span=> takes a count and one of C<s>, C<m>, C<h>, C<d>, C<w>, C<mon>, C<q>
1459 or C<y> -- C<15m>, C<6h>, C<2d>, C<3mon>, C<1q> -- and is refused, naming the
1460 interval and the parameter, when the count is zero or the unit is not one of
1461 those. It expands into the calculated field a reader could have typed,
1462 C<< <col>_span:span(<col>,'1h') >>, so there is one pipeline and one set of
1463 semantics; C<tcol=> names the column where the route has no single dimension or
1464 names more than one, and C<as=> names the bucket column. C<tcol=> without a
1465 C<span=>, an unknown column, a name that is already a column and a C<span=> that
1466 disagrees with the reader's own calculated field are each a 400.
1467  
1468 Labels are ISO at the bucket's own resolution -- C<2026-10-27T14:00:00>,
1469 C<2026-10-27>, C<2026-10-01> -- so they sort chronologically as strings and a
1470 bucketed chart comes out in the right order with no second sort. C<d>, C<w>,
1471 C<mon>, C<q> and C<y> are computed from the local calendar, so a daylight-saving
1472 day is one bucket and a leap day is its own date; C<s>, C<m> and C<h> floor the
1473 interval out of the epoch. A value that cannot be read as a time lands in one
1474 visible C<(unparseable)> bucket rather than disappearing.
1475  
1476 C<agg=> on C</graph> is the timechart: one point per bucket, aggregated with
1477 the measure vocabulary a pivot's C<m=> uses (C<sum>, C<avg>, C<min>, C<max>,
1478 C<count>, C<count_distinct>, C<median>, C<stdev>, C<first>, C<last>,
1479 C<range>, C<pE<lt>NE<gt>>). Without C<agg=> every row is still plotted.
1480  
1481 The same bucket is available by hand, because it is a calculated field:
1482 C<?c=hour:span(at,'1h')>. The timestamp functions are C<epoch(v[, fmt])>,
1483 C<hour(v)>, C<minute(v)>, C<second(v)> and C<span(v, interval)>; see
1484 L<Database::BI::Calc> for the shapes each reads and the local-time rule.
1485  
1486 =head2 Cross-row columns
1487  
1488 Every data route, C</export> and a saved canvas accept a cross-row plan: a
1489 column worked out from many rows and written beside each one.
1490  
1491 cs=<column>:<function>:<alias>[:<count>] repeatable
1492 by=<column>[,<column>...] partition (optional)
1493 order=<column>[:asc|desc] the order the rows are read in
1494  
1495 C<pct_of_total>, C<rank> and C<zscore> need no order. C<running_total>,
1496 C<running_count>, C<running_pct>, C<window_sum>, C<window_avg>, C<lag>,
1497 C<delta> and C<pct_change> need C<order=>; only C<window_sum> and
1498 C<window_avg> take the C<count>, the width of the window. The pass runs after
1499 the filters and before pagination, so page 2 continues page 1's running total.
1500 An unknown function or column, or a parameter where none is taken, is a 400.
1501 A calculated field can read the new column, as in
1502 C<c=half:ytd/2>, and C<cs=> can read a calculated field or a time bucket, as in
1503 C<span=1mon&tcol=at&order=at_span&cs=amount:running_total:ytd>. A filter runs
1504 before the pass, so a window is over the rows it leaves, unless it names a
1505 column the pass writes: C<f=ytd:gt:50> runs last, and selects rows without
1506 changing their totals. On a canvas, a card's own filters and a cross-filter
1507 follow the same rule.
1508 The table page's Cross-row panel builds the same parameters. See
1509 L<Database::BI::Controller::Dashboard/Cross-row columns> for the full rules.
1510  
1511 =head2 Stored lookups
1512  
1513 A lookup adds columns to every row of a dataset from a second file, matched on
1514 one key: a shop code in the orders gains the shop's city and region.
1515  
1516 POST /lookups/save l=table:orders&name=shops&file=lookups/shops.csv
1517 &key=code&col=shop&fields=city,region&default=unknown
1518  
1519 C<file=> is a data file under C<data_dir> (a relative path is read from there),
1520 of at most 8 MiB; a file outside it, a symlink that leads outside it, a URL and
1521 a remote path are refused. C<key=> is the lookup file's key column and C<col=>
1522 the dataset's (default: the same name). C<fields=> lists the columns to add
1523 (default: all but the key). Keys are compared as text after trimming spaces,
1524 and case matters. When the file has a key twice, the first row is used and the
1525 log says so. A row whose key is not in the file gets C<default=>, or nothing.
1526  
1527 A field keeps its own name unless the dataset already has a column of that
1528 name; it is then called C<E<lt>nameE<gt>_E<lt>fieldE<gt>> (C<shops_region>). A
1529 source column is never overwritten.
1530  
1531 The table page's Lookups panel saves, lists and deletes them, and can skip one
1532 in the current view. Stored lookups apply on every route, a canvas and
1533 C</export>, before calculated fields and filters, so either can read the new
1534 columns. A total
1535 grouped or filtered by one is worked out in Perl, never sent to SQL. For one request,
1536 C<nolk=E<lt>nameE<gt>> skips a stored lookup, and C<lk=E<lt>nameE<gt>> applies
1537 a lookup stored for another dataset. Both repeat, and the panels carry them on.
1538 A lookup is not a join: for more than one key, or rows that match many rows,
1539 use Merge.
1540  
1541 =head2 Accelerated datasets
1542  
1543 A large CSV, TSV, PSV, XLSX or XML file can be read from a SQLite copy of
1544 itself, an I<extract>, which is faster to page through and lets totals be
1545 worked out in SQL. C<script/bi-extract --spec table:sales> marks the dataset
1546 accelerated and builds the extract under C<E<lt>model_dirE<gt>/extracts/>, or in
1547 the cache directory set (see L</Where caches are kept>).
1548  
1549 An extract is used only while it matches the file: its name is a fingerprint
1550 of the spec and the file's modification time and size. Once the file changes,
1551 the file itself is read, and the table page says the extract is stale and how
1552 to rebuild it. Stale numbers are never shown. The table page also says when
1553 a dataset is read from its extract, and when that was built.
1554  
1555 An extract is a cache. Deleting its folder loses nothing: each accelerated
1556 dataset reads its file until the next build. Nothing is written under
1557 C<data_dir>, and a C<model_dir> inside it is refused. Only a C<table:> or
1558 C<path:> spec naming a local file can be accelerated; a SQLite file is one
1559 already. C<table:sales> and C<path:/data/sales.csv> are separate datasets, each
1560 accelerated on its own.
1561  
1562 =head2 Field extraction
1563  
1564 A regular expression can pull values out of a column. For one value, use
1565 C<rx()> in a calculated field:
1566  
1567 c=country:rx(ref,'^([A-Z]{2})-','1')
1568  
1569 To make several columns from one pattern, store an extraction. Each named
1570 group, C<(?E<lt>nameE<gt>...)>, becomes a column:
1571  
1572 POST /extract/save l=table:invoices&name=parts&col=ref
1573 &pattern=^(?<country>[A-Z]{2})-(?<year>\d{4})-
1574  
1575 C<fields=country> keeps only the groups it names. The table page's Extract
1576 fields panel saves, lists and deletes them. Stored extractions apply on
1577 every route, a canvas and C</export>, after the lookups and before calculated
1578 fields and filters, so either can read the new columns. A row the pattern does
1579 not match gets C<(unparseable)> in each column, as a calculated field's row
1580 does, so those rows group visibly instead of vanishing; they are not blank. A
1581 group that took no part in a match is empty. Columns are named as a lookup's
1582 are: the group's own name, or C<E<lt>nameE<gt>_E<lt>groupE<gt>> when the
1583 dataset has a column of that name. A capture is cut to 200 characters.
1584  
1585 A pattern is refused when it is over 200 characters, when it does not compile,
1586 or when it uses C<(?{>, C<(??{>, C<(?p> or C<(*>, which run code or steer the
1587 engine. These checks are not a sandbox. Perl's regular expressions have no
1588 timeout, so a pathological pattern such as C<(a+)+$> stalls the request that
1589 uses it. Patterns are entered by whoever runs the server, never by a remote
1590 party. See L<Database::BI::Calc/Regular expressions>.
1591  
1592 =head1 CONFIGURATION
1593  
1594 Place a C<database_bi.conf> file in the application root to override
1595 defaults:
1596  
1597 {
1598 data_dir => 'data', # directory scanned for data files on the home page
1599 uploads_dir => '.uploads', # staging directory for uploaded files
1600 platform => 'web', # VWF template dimension
1601 language => 'en', # VWF template dimension
1602  
1603 # Browser-side libraries. d3_url overrides the chart and canvas
1604 # script; the leaflet_* keys override the map assets, and tile_url
1605 # overrides the tile server. See "Self-hosting D3 and Leaflet"
1606 # below.
1607 d3_url => 'https://cdnjs.cloudflare.com/ajax/libs/d3/7.9.0/d3.min.js',
1608 }
1609  
1610 C<uploads_dir> is where C<POST /upload> stages the files a user drags onto the
1611 page, and the directory C<POST /uploads/clear> empties. It defaults to
1612 C<.uploads> under the application home. A relative value resolves against the
1613 application home, an absolute one is used as given, so a deployment whose
1614 application directory is read-only can stage uploads somewhere writable:
1615  
1616 { uploads_dir => '/var/tmp/database-bi-uploads' }
1617  
1618 =head2 Where caches are kept
1619  
1620 The places OpenStreetMap found for maps, the extracts of accelerated datasets
1621 (see L</Accelerated datasets>), the page cache when C<page_cache_dir> turns it
1622 on, and the table cache when C<< cache => { driver => 'File' } >> (or
1623 C<FastMmap>) asks for one, are kept on disc. Each object that keeps one
1624 chooses its directory when it is made (L<Database::BI::CacheDir>), from the
1625 first of these that is set:
1626  
1627 =over 4
1628  
1629 =item 1. C<cache_dir> through L<Object::Configure>, for the class making the
1630 cache: its config files, then the environment variable
1631 C<E<lt>ClassE<gt>__cache_dir> (C<Database__BI__Model__Geocode__cache_dir> for
1632 the places, C<Database__BI__Extract__cache_dir> for the extracts,
1633 C<Database__BI__cache_dir> for the page and table caches);
1634  
1635 =item 2. C<$ENV{CACHEDIR}>;
1636  
1637 =item 3. C<$ENV{CACHE_DIR}>;
1638  
1639 =item 4. C<cache_dir> here (relative: under the application home):
1640  
1641 { cache_dir => '/var/cache/database-bi' }
1642  
1643 =item 5. F<E<lt>model_dirE<gt>/geocode-cache> for the places,
1644 F<E<lt>model_dirE<gt>/extracts> for the extracts, C<page_cache_dir> for the
1645 page cache, F<E<lt>model_dirE<gt>/cache> for the table cache.
1646  
1647 =back
1648  
1649 Caches sharing a directory each keep to their own namespace in it; the
1650 extracts go in its F<database-bi-extracts/> folder. A cache directory inside
1651 C<data_dir> is not used for the extracts, which then stay in C<model_dir>: each
1652 is a C<.db> file the home page would list. C<page_cache_dir> still decides
1653 whether pages are cached at all (see L</Compression and the page cache>): a
1654 cache directory set only says where. A C<< cache => { root_dir } >> names the
1655 table cache's directory outright. C<script/bi-geocode> and
1656 C<script/bi-extract> read the same settings, and F<database_bi.conf> from
1657 C<MOJO_CONFIG> or the current folder (C<bi-extract> through the application).
1658  
1659 =head2 Who can reach the server
1660  
1661 A server started by C<script/database-bi> (with no arguments, a database, or
1662 C<daemon> or C<prefork>) listens on C<http://127.0.0.1:3000>, so only this
1663 machine can reach it: unless a password is set (see L</Logging in>), there
1664 is no login, and Mojolicious on its own would listen on every interface.
1665 C<listen> changes that:
1666  
1667 { listen => 'http://127.0.0.1:8000' } # another port, still this machine only
1668 { listen => 'http://*:3000' } # every machine that can reach this one
1669 { listen => ['http://127.0.0.1:3000', 'http://[::1]:3000'] } # IPv6 loopback too
1670  
1671 It takes a location, several separated by commas, or a list. A location
1672 that is not C<http://> or C<https://> stops the start. C<MOJO_LISTEN> and the
1673 C<-l> option of C<daemon> and C<prefork> still win over it.
1674 C<morbo script/database-bi> opens its port before it loads the application, so
1675 it does not read C<listen>; give it C<-l>, or run C<script/database-bi>.
1676  
1677 Anywhere but this machine needs a password: a C<listen> (or C<MOJO_LISTEN>, or
1678 C<-l>, or under hypnotoad its own C<listen>) that is not loopback stops the start
1679 unless C<password_hash> is set (see L</Logging in>). The message says what to
1680 change. The defaults are loopback, so they start as before.
1681  
1682 C<hypnotoad> reads its own C<< hypnotoad => { listen } >>, which defaults to
1683 C<http://127.0.0.1:8080>, this machine only:
1684  
1685 { hypnotoad => { listen => ['http://*:8080'] } } # every machine
1686  
1687 Behind a reverse proxy on the same machine, leave both at their defaults. The
1688 proxy can still bring other machines' requests in, so without a password a
1689 request from an address that is not loopback is refused (C<403>), including one
1690 a trusted proxy names in C<X-Forwarded-For>. For the public, set a password.
1691  
1692 B<This machine is not one person.> Loopback keeps other machines out, not
1693 other people on this one: any account on the same machine, or a container that
1694 shares its network, can reach C<127.0.0.1:3000>, and read, list and save files
1695 as the account running the server. On a machine with other users, set a
1696 password even when it listens on this machine only.
1697  
1698 =head2 Which folders it may use
1699  
1700 C</browse>, C</open>, the Export panel's folder list (C</api/dirs>), a C<path:>
1701 data source anywhere (a table, a chart, a merge, a canvas card) and
1702 C<POST /export> reach the whole filesystem, as far as the account running the
1703 server can. C<file_roots> fences them in:
1704  
1705 { file_roots => ['/srv/reports', 'shared'] } # 'shared' is under the application home
1706  
1707 C<data_dir> and C<uploads_dir> are always among them. Browsing starts at the
1708 home folder if it is inside, else at the first root, and stops at the roots. A
1709 folder or file outside is refused (C<403>, or a C<path:> source is not found),
1710 whether or not it is there, so the refusal says nothing about what is outside;
1711 a link inside that leads outside is outside. A path that climbs out with C<..>
1712 past what exists is refused too. A root that is not a folder that exists stops
1713 the start. Remote files (C</../host/...>), Redis and MariaDB are not files on
1714 this machine, and are not affected; neither are the command-line tools.
1715  
1716 Saving is the riskier half: an export replaces a C<.csv>, C<.json> or C<.sql>
1717 file of the same name, and some such files are settings another program obeys
1718 (a project's F<package.json> names commands that C<npm> runs). A server other
1719 people use keeps exports to folders of their own, so with C<password_hash> set,
1720 C<export_dirs> is required, and the start stops without it:
1721  
1722 { export_dirs => ['/srv/reports/exports'] } # a relative one is under the application home
1723  
1724 C<POST /export> then saves only in them (or below them), and the Export panel
1725 opens on the first and lists no other. Reading is unaffected: that is
1726 C<file_roots>. An export folder that does not exist, is outside C<file_roots>
1727 or is a dot-folder stops the start. Whatever is set, an export never replaces
1728 a link, a folder or anything else that is not a plain file, so a link inside
1729 the allowed folders cannot carry it outside them; it is written beside its name
1730 and renamed into place, so nobody reads half a file, and a file it replaces
1731 keeps its permissions.
1732  
1733 =head2 Requests from other sites
1734  
1735 Unless a password is set (see L</Logging in>), there is no login: whatever can
1736 reach the server can read any file it can (C</open>) and write exports into
1737 any folder it can (C<POST /export>). Even with one, a logged-in reader's
1738 browser carries the login to every request it sends here. Listening
1739 on this machine only keeps other machines out, but not the pages of other
1740 sites open in the reader's browser. So every request is checked first, and
1741 refused with C<403> when another site's page made it:
1742  
1743 =over 4
1744  
1745 =item * A write (any method but C<GET>, C<HEAD> and C<OPTIONS>) is refused when
1746 the browser's C<Sec-Fetch-Site> says it came from another site, or, without
1747 that header, when its C<Origin> is not this server. A form posted from
1748 another site needs no permission from this one, so this is what stops it. A
1749 script such as C<curl> sends neither header and is let through.
1750  
1751 =item * Any request is refused when its C<Host> is a name other than
1752 C<localhost> or C<*.localhost>, and is not an IP address. A page whose own
1753 name has been made to resolve to C<127.0.0.1> (DNS rebinding) counts as this
1754 server to the browser, but it still sends its own name. To reach the server
1755 by a name, list it:
1756  
1757 { allowed_hosts => ['bi.example.com', '*.corp.example'] } # '*' for any name
1758  
1759 Behind a trusted reverse proxy, the C<Host> is whatever the proxy sends, so it
1760 is checked only when C<allowed_hosts> is set; set it there too.
1761  
1762 =back
1763  
1764 C<POST /export> also refuses a folder or file whose name starts with a dot
1765 (F<~/.config>, F<.vscode/settings.json>), since those hold other programs'
1766 settings. The folder list in the Export panel leaves them out already.
1767  
1768 =head2 Which addresses it may fetch
1769  
1770 C</import> and a C<url:> data source make the server fetch a URL, so a page
1771 could point it at what only the server can reach: a service on this machine, a
1772 database on the office network, or a cloud's metadata service on
1773 C<169.254.169.254>, which hands out its credentials. So the server refuses a
1774 URL that leads to this machine, a private or shared network
1775 (C<10.0.0.0/8>, C<172.16.0.0/12>, C<192.168.0.0/16>, C<100.64.0.0/10>,
1776 C<fc00::/7>), link-local addresses, multicast or a reserved range.
1777  
1778 It is what the name I<resolves to> that counts, not how the URL reads:
1779 C<http://localtest.me/> and C<http://127.0.0.1.nip.io/> are names whose DNS
1780 answers C<127.0.0.1>, and C<http://2130706433/> or C<http://0x7f000001/> are
1781 C<127.0.0.1> written another way; all are refused. An address is looked up
1782 once, checked, and that address is the one connected to, so a name that answers
1783 differently the second time (DNS rebinding) gains nothing. Each redirect is
1784 checked the same way, and at most 5 are followed. Over C<https> the
1785 certificate must still be the site's. The refusal says why, for example
1786 C<localtest.me is 127.0.0.1, which is this machine>.
1787  
1788 To read from an intranet site, name it:
1789  
1790 { url_private_hosts => ['reports.corp.example', '*.corp.example'] }
1791  
1792 Those names may resolve to a private address; every other name is still
1793 checked, and an address written as such (C<http://10.1.2.3/>) is never let
1794 through. A name that is not a host name, or C<*> alone, stops the start.
1795 This check is not a firewall: where the server's machine can reach something it
1796 should not, rules on outgoing connections are the stronger defence.
1797  
1798 =head2 Logging in
1799  
1800 With C<password_hash> set, every page asks for a password first. A server
1801 other machines can reach must have one (see L</Who can reach the server>); on
1802 this machine alone it is optional. Make the line with
1803  
1804 script/database-bi password
1805  
1806 which asks for the password twice (without showing it) and prints
1807  
1808 password_hash => 'pbkdf2-sha256$200000$...',
1809  
1810 to put in C<database_bi.conf>. Only the hash is kept: PBKDF2-HMAC-SHA256 with
1811 a random salt (L<Database::BI::Password>). A plain password there stops the
1812 start, so it is never left in the file by mistake. To change the password,
1813 make a new line; every session is then logged out.
1814  
1815 There is one password, not one per person. A browser asking for a page is
1816 sent to C</login> and, once the password is right, back to that page; any other
1817 request (a script, a panel's request, a download) gets C<401> until it carries a
1818 logged-in session. C<POST /logout>, the Log out button at the top of each page,
1819 ends it. C</share/:id> and C</embed/:id> need no login: their signed, expiring
1820 token is their permission, so anyone holding the link sees that canvas, and
1821 only that one, for 30 days. The canvas itself (C</a/:id>) and the rest still
1822 ask for the password.
1823  
1824 The token is signed with C<share_secret> when it is set (at least 16
1825 characters), else with a key derived from C<secrets>. Without either, the key
1826 is new at each start, so a restart ends every share link, and the Share panel
1827 says so; set C<secrets> (which keeps logins too) or C<share_secret> to keep
1828 them. Changing either ends the links made with the old one. Before 0.021.0 a
1829 missing C<share_secret> meant a fixed key, the same on every install, with which
1830 anyone could sign a link to any canvas; that key in C<share_secret> stops the
1831 start.
1832  
1833 {
1834 password_hash => 'pbkdf2-sha256$200000$...',
1835 export_dirs => ['/srv/reports/exports'], # required with a password
1836 login_expiration => 8 * 60 * 60, # seconds idle before logging out; the default
1837 secrets => ['a long random string'], # or each restart logs everyone out
1838 }
1839  
1840 The session is a cookie signed with C<secrets>: C<HttpOnly>, C<SameSite=Lax>,
1841 and C<Secure> whenever the request came over https (directly, or through a
1842 trusted proxy that says so), so it is never sent back over plain http. Over
1843 plain http anyone on the network path can read the password and the cookie: a
1844 server other machines reach should be behind https. Ten wrong passwords from
1845 one address within 15 minutes lock that address out for the rest of the 15
1846 minutes (C<429>); each server process counts its own.
1847  
1848 =head2 MariaDB and MySQL servers
1849  
1850 A server is opened by name. Each entry of C<mariadb_servers> is one database:
1851  
1852 {
1853 mariadb_servers => {
1854 shop => {
1855 host => 'db.example.com', # default: localhost
1856 port => 3306, # optional
1857 database => 'shop', # required
1858 username => 'bi_reader',
1859 password => 'secret',
1860 },
1861 },
1862 }
1863  
1864 The home page then has a card for each name, leading to the list of its tables.
1865 C<mariadb:shop/orders> opens one anywhere a source is taken: the path field, C<l=>,
1866 C<j=>, C<c=> and a canvas's C<datasets>. C<mariadb:shop> lists them.
1867  
1868 The spec holds a name and no more, because a spec travels in URLs, bookmarks,
1869 shared links, saved canvases and server logs. So the password stays in the
1870 configuration file, which should be readable only by the account that runs the
1871 application, and no request can point the application at another server.
1872  
1873 L<DBD::MariaDB> is recommended; L<DBD::mysql> serves if it is the one installed.
1874 Without either, opening a server says so and the rest of the application is
1875 unaffected. Rows are read from the server each time, and never cached. The tests
1876 are C<t/mariadb.t>, which mocks the server, and C<t/mariadb_live.t>, which runs when
1877 C<MARIADB_SERVER> is set, for example to C<mariadb://user:pass@host/test>, and which
1878 creates its own tables and drops them.
1879  
1880 =head2 Compression and the page cache
1881  
1882 Every response goes through L<Mojolicious::Plugin::Buffer>: it is compressed
1883 (gzip or Brotli, as the browser accepts), HTML whitespace is minified (inline
1884 scripts and styles are left alone), and a C<200> to C<GET> or C<HEAD> carries
1885 an C<ETag>, so a repeat request can be answered C<304 Not Modified>. There
1886 is no C<Last-Modified>: the plugin would date a page by the code, not the data,
1887 which lets a browser reuse a stale reply without asking. File
1888 downloads (C</export> as CSV, SQLite or JSON, the pivot as XLSX, and a canvas
1889 card's data from C</a/:id/visual/:n/:fmt>) opt out and are sent as they are.
1890  
1891 Two optional keys keep finished pages on disc:
1892  
1893 {
1894 page_cache_dir => '/var/cache/database-bi', # relative: under the application home
1895 page_cache_duration => '5 minutes', # the default
1896 }
1897  
1898 When C<page_cache_dir> is set, C</catalog> and C</tutorial> are stored in a
1899 L<CHI> File cache there (or in the cache directory set: see
1900 L</Where caches are kept>), one copy per C<Accept-Language>, and served from it
1901 until they expire. No other page is cached, because the rest read data files
1902 that can change between requests. Without the key nothing is cached.
1903  
1904 =head2 Self-hosting D3 and Leaflet
1905  
1906 The chart pages load D3 from a public CDN by default, and the map pages load
1907 Leaflet and leaflet.markercluster from unpkg. A deployment with no outbound
1908 network access, or one that prefers to serve its own assets, sets the URLs in
1909 C<database_bi.conf>:
1910  
1911 {
1912 d3_url => '/static/d3/d3.min.js',
1913 leaflet_js_url => '/static/leaflet/leaflet.js',
1914 leaflet_css_url => '/static/leaflet/leaflet.css',
1915 leaflet_cluster_js_url => '/static/leaflet/leaflet.markercluster.js',
1916 leaflet_cluster_css_url => '/static/leaflet/MarkerCluster.css',
1917 leaflet_cluster_default_css_url => '/static/leaflet/MarkerCluster.Default.css',
1918 tile_url => 'https://tiles.internal/{z}/{x}/{y}.png',
1919 }
1920  
1921 An absolute path is served like any other static file: put the library under
1922 the application's C<public/> directory, so C</static/d3/d3.min.js> is
1923 C<public/static/d3/d3.min.js>. The five Leaflet keys may be set on their own;
1924 any left out keeps L<HTML::OSM>'s pinned CDN URL. Use a path beginning with
1925 C</>, or a full URL; a path without the leading slash is relative to the page. The versions to copy are the
1926 ones the defaults name: D3 7.9.0, Leaflet 1.9.4, and leaflet.markercluster 1.5.3.
1927 C<tile_url> points the map layer at a self-hosted tile server instead of
1928 OpenStreetMap. It keeps Leaflet's C<{z}>, C<{x}> and C<{y}> placeholders, and
1929 C<{s}> too if the server uses subdomains. Set it, and C<map_geocode_online>
1930 to 0, and every request needed to draw a map can stay on the local network. The OpenStreetMap attribution stays
1931 on the map, since it is the usual credit for OSM-derived tiles. There is no
1932 in-page place search: L<HTML::OSM> is always constructed with C<search =E<gt> 0>,
1933 so nothing typed on a map page is sent anywhere.
1934  
1935 Map-specific config keys: C<map_geocode_max> (new geocoder lookups per request,
1936 default 500), C<map_geocode_online> (0 keeps geocoding on this machine: a place
1937 the local data does not know is not sent to OpenStreetMap's Nominatim, nor a
1938 marker's position for its outline; default 1), C<map_geocode_online_max>
1939 (places, and outlines, asked of Nominatim per request, default 20;
1940 at one a second, the rest are left for the next request or for
1941 C<script/bi-geocode>; the map page's progress bar asks one place a request, so
1942 it reaches every place), C<secrets> (a list; the first signs the places a map
1943 page lists for C<POST /geo/lookup>. Without it each start makes a random one), C<map_max_markers> (markers per request, default 2000), C<map_popup_rows>
1944 (rows listed in one marker's popup, default 10),
1945 C<map_region> (a two-letter country hint for ambiguous places),
1946 C<map_bounds_ranks> (the place rank that is a county, by country; see above), C<map_max_zoom>
1947 (the default tile-layer maxZoom) and C<tile_sources> (a hashref of named tile
1948 sources, each C<< { url =E<gt> ... } >>, selected with C<tiles=>).
1949  
1950 B<Map colour rules.> C<color=> names the column and C<color_rule=> supplies
1951 C<< E<lt>opE<gt>:E<lt>valueE<gt>:E<lt>colourE<gt> >> rules; they are tried in
1952 the order given and the first match wins. The operators are C<gt>, C<ge>,
1953 C<lt>, C<le>, C<eq> and C<ne>; a comparison is numeric when both the cell and
1954 the rule's value look like numbers, and otherwise only C<eq>/C<ne> are
1955 meaningful (case-insensitive). A colour is C<#rgb>, C<#rrggbb> or a CSS name
1956 such as C<red> or C<teal>. A value that matches no rule gets C<color_default=>,
1957 which the legend shows as "Other". C<series=> is the categorical
1958 alternative: each distinct value takes the next colour from C<scheme=>,
1959 cycling through the palette, and the legend lists the categories. C<color=>
1960 and C<series=> are mutually exclusive.
1961  
1962 =head2 Content-Security-Policy
1963  
1964 Every HTML page is sent with a C<Content-Security-Policy> header. The pages
1965 show cells from files this application did not write, so the policy backs up
1966 the escaping. Scripts, stylesheets and images may come only from this site
1967 and from the hosts that C<d3_url>, the C<leaflet_*_url> keys, C<tile_url> and
1968 C<tile_sources> name. The defaults allow cdnjs.cloudflare.com, unpkg.com and
1969 OpenStreetMap's tile servers. A page's scripts may talk only to this site
1970 (C<connect-src 'self'>), and no other site may show a page in a frame
1971 (C<frame-ancestors 'self'>). JSON replies and downloads carry no policy.
1972  
1973 Every HTML page also carries C<X-Frame-Options: SAMEORIGIN>, whatever the
1974 policy keys below say. A report-only policy only reports C<frame-ancestors>,
1975 a policy written out in full may leave it out, and older browsers read only
1976 this header, so framing by another site is refused in each of those cases.
1977  
1978 Every reply carries C<Referrer-Policy: strict-origin-when-cross-origin>. A
1979 request a page starts sends its whole URL to this site, only the origin to
1980 another, and nothing from https to http. Specs, filter values and share
1981 tokens travel in the query string, and an older browser's default would send
1982 all of it to the CDNs, the tile servers and any site a reader follows a link
1983 to. It is not C<no-referrer>, because OpenStreetMap's tile policy asks pages
1984 for a C<Referer>.
1985  
1986 Every reply also carries C<X-Content-Type-Options: nosniff>, so a browser
1987 takes it for what its C<Content-Type> says and never guesses. A guess could
1988 read a CSV export, or text from a data file, as HTML or script. A
1989 self-hosted library under C<public/> keeps working, because Mojolicious names
1990 C<.js> and C<.css> files for what they are; one served some other way must be
1991 sent as C<text/javascript> or C<text/css>, or the browser will not load it.
1992  
1993 Every page carries C<Cross-Origin-Opener-Policy: same-origin>: a window that
1994 another site opened, or that a page opened on another site, gets no handle on
1995 the page through C<window.opener>. No page opens a window or reads an
1996 opener, and every link to a new window is C<rel="noopener">.
1997 C<cross_origin_opener_policy> sets another of C<same-origin>,
1998 C<same-origin-allow-popups>, C<noopener-allow-popups> and C<unsafe-none>, or
1999 C<''> or C<0> for none; any other value stops the application at startup. No
2000 C<Cross-Origin-Embedder-Policy> is sent: it would refuse the libraries and
2001 tiles the CDNs and OpenStreetMap serve, and nothing here needs the isolation
2002 it gives.
2003  
2004 Every reply carries a C<Permissions-Policy> that turns off the browser
2005 features no page uses: the camera, the microphone, location, payment, USB,
2006 serial and HID devices, screen capture, full screen, reading the clipboard and
2007 the rest, for this site and any frame it holds. Writing to the clipboard
2008 stays (C<clipboard-write=(self)>), for the Copy link buttons. A script that
2009 got in cannot ask the reader for any of them. C<permissions_policy> replaces
2010 the value (C<''> or C<0> for none); one that cannot be a header value stops
2011 the application at startup:
2012  
2013 { permissions_policy => 'camera=(), geolocation=(self)' }
2014  
2015 A reply to a request that came over https carries
2016 C<Strict-Transport-Security: max-age=31536000>, so the browser comes back over
2017 https for a year. It binds this host only; set C<strict_transport_security>
2018 to add C<includeSubDomains> or C<preload>, which reach every site of the
2019 domain and are hard to take back, or to C<''> or C<0> to send none. A value
2020 a browser would not read stops the application at startup:
2021  
2022 { strict_transport_security => 'max-age=63072000; includeSubDomains' }
2023  
2024 Over http no such header is sent, since a browser ignores it there, and none
2025 is sent to C<localhost> or to an IP address: it binds the host, not the port,
2026 so from C<https://localhost:8443> it would send every other server on that
2027 machine to https, and browsers ignore it for an address. Behind a proxy that
2028 ends TLS, a request is https only when Mojolicious is told to trust the proxy
2029 (C<MOJO_REVERSE_PROXY=1>, or C<< hypnotoad => { proxy => 1 } >>) and the proxy
2030 sends C<X-Forwarded-Proto: https>; otherwise the proxy must send the header
2031 itself.
2032  
2033 An inline script runs only if it carries the request's nonce, a random value
2034 made for each request and named in the policy (C<'nonce-...'>). Every
2035 C<E<lt>scriptE<gt>> the templates write carries it, and so do those that
2036 L<HTML::D3> (0.30 or later) and L<HTML::OSM> (0.14 or later) write, because
2037 the application passes it to them. A script that came in with a cell's text
2038 cannot know it, so it does not run; nor does an C<onclick> or C<onerror>
2039 attribute, which no nonce can cover, so the pages use none. Style attributes
2040 are still allowed (C<style-src 'unsafe-inline'>).
2041  
2042 A canvas card added without a reload arrives as JSON with the nonce its
2043 scripts were written with. The page runs only the scripts that carry it, and
2044 gives each the page's own nonce. A page kept in the page cache is stored with
2045 its header, so the nonce in each still matches when it is served again.
2046  
2047 Two keys change the policy:
2048  
2049 {
2050 content_security_policy_report_only => 1, # report breaches, block nothing
2051 content_security_policy => "default-src 'self'; script-src 'self' 'nonce-{nonce}'",
2052 }
2053  
2054 C<content_security_policy> is the whole policy, sent as written except that
2055 C<{nonce}> becomes the request's nonce; without it no inline script runs. Set
2056 to C<''> or C<0> it sends no header. A policy written out in full is not
2057 updated when the library keys change.
2058  
2059 =head3 Embedding a canvas on another site
2060  
2061 By default only pages of this site may show C</embed/:id> in a frame.
2062 C<embed_frame_ancestors> names other sites that may:
2063  
2064 { embed_frame_ancestors => [ 'https://intranet.example.com', 'https://*.corp.example' ] }
2065  
2066 Each entry is an origin: C<http> or C<https>, a host (a C<*> may stand for its
2067 first label only) and an optional port, with no path. Anything else stops the
2068 application at startup, because a security setting that does not do what it
2069 says should not be dropped quietly. The sites are added to that page's
2070 C<frame-ancestors>, and to no other page's. With a report-only policy, with
2071 none, or with one written out without C<frame-ancestors>, the page carries a
2072 second, enforced policy of C<frame-ancestors> alone, so naming one site never
2073 lets every site in. A policy written out with its own C<frame-ancestors> is
2074 left to say who may. C<X-Frame-Options: SAMEORIGIN> stays: browsers that know
2075 C<frame-ancestors> follow it instead, and those that know only the older
2076 header refuse the frame. The canvas's Share panel names the sites its embed
2077 code works on.
2078  
2079 =head1 METHODS
2080  
2081 =head2 startup
2082  
2083 Called once by Mojolicious when the application is loaded, by
2084 C<script/database-bi>, C<morbo>, C<hypnotoad> or C<< Test::Mojo->new('Database::BI') >>.
2085 It is not called directly.
2086  
2087 =head3 API SPECIFICATION
2088  
2089 =head4 INPUT
2090  
2091 $self # the Database::BI application object
2092  
2093 Configuration comes from C<database_bi.conf> in the application home, if it is
2094 there (see L</CONFIGURATION>), or from the hashref given to
2095 C<< Test::Mojo->new('Database::BI', { ... }) >>. Unset keys default to
2096 C<< data_dir => 'data' >>, C<< platform => 'web' >>, C<< language => 'en' >> and the
2097 public D3 URL.
2098  
2099 =head4 OUTPUT
2100  
2101 Nothing useful is returned. By the time it returns, the application has:
2102  
2103 =over 4
2104  
2105 =item * the login session (L<Database::BI::Sessions>), and C<require_login> in
2106 front of every route but the login, share and embed links (see
2107 L</Logging in>); a C<password_hash> that is not a hash, or a C<login_expiration>
2108 under 60 seconds, croaks; so does a C<listen> other machines could reach when
2109 there is no password, and a password without C<export_dirs>;
2110  
2111 =item * a C<file_roots> that is not a list of folders that exist croaks (see
2112 L</Which folders it may use>); so does a C<url_private_hosts> that is not a
2113 list of host names (see L</Which addresses it may fetch>);
2114  
2115 =item * the C<before_dispatch> hook that refuses requests from other sites (see
2116 L</Requests from other sites>); an C<allowed_hosts> that is not a list of host
2117 names croaks;
2118  
2119 =item * C<MOJO_LISTEN> set from C<listen> (default C<http://127.0.0.1:3000>) when it
2120 was unset, and C<< hypnotoad => { listen } >> defaulted to C<http://127.0.0.1:8080>
2121 (see L</Who can reach the server>); a C<listen> that is not a location croaks;
2122  
2123 =item * a 50 MiB limit on request bodies (an upload over it is answered C<413>);
2124  
2125 =item * Template Toolkit registered as handler C<tt>, and the Buffer plugin
2126 (see L</Compression and the page cache>);
2127  
2128 =item * the CHI cache for tables (the C<Memory> driver unless C<cache =E<gt> { driver }>
2129 names another; a C<File> or C<FastMmap> one is kept where L</Where caches are kept>
2130 says), and the page cache when C<page_cache_dir> is set;
2131  
2132 =item * the C<before_dispatch> hooks for relative time ranges, cross-row plans and
2133 the view context, and the C<before_render> hook that sets the breadcrumb, the help link
2134 and the HTML 404 page;
2135  
2136 =item * every route in L</ROUTES>.
2137  
2138 =back
2139  
2140 A configuration file that does not parse makes the C<Config> plugin croak, and
2141 the application does not start.
2142  
2143 =head1 COMMAND-LINE TOOLS
2144  
2145 The scripts in C<script/> are installed with the distribution, each with a
2146 manual page (C<man bi-extract>, C<man bi-geocode>, C<man bi-alert>,
2147 C<man database-bi>). The maintenance scripts use the same data and model
2148 directories as the web application, found from the folder they are run in or
2149 given with C<--data-dir> and C<--model-dir>, and are safe to run from cron.
2150  
2151 =head2 script/database-bi password
2152  
2153 Asks for a password (twice, without echo, at a terminal; once from a pipe) and
2154 prints the C<password_hash> line for C<database_bi.conf>. See L</Logging in>.
2155  
2156 =head2 script/bi-geocode
2157  
2158 Warms the shared geocode cache for one column, so later map requests make no
2159 lookups:
2160  
2161 ./script/bi-geocode --spec table:customers --col city \
2162 [--text TEXT] [--data-dir DIR] [--model-dir DIR] \
2163 [--openaddr PATH] [--region CC] [--dry-run] [--verbose]
2164  
2165 C<--text> is the map panel's I<Add to each place> (C<loc_text=>): with
2166 C<--col county --text 'Virginia, USA'> it looks up C<Accomack, Virginia, USA>,
2167 the place the map then asks for.
2168  
2169 It is idempotent: a second run against a warm cache resolves nothing and says
2170 so. Exit status is 0 on success, 1 when the column is missing or no rows load,
2171 and 2 on bad arguments or an unopenable source.
2172  
2173 =head2 script/bi-extract
2174  
2175 Builds the extracts of accelerated datasets (see L</Accelerated datasets>):
2176  
2177 ./script/bi-extract --spec table:sales [--spec ...] # accelerate, and build
2178 ./script/bi-extract --all # rebuild every one
2179 ./script/bi-extract --remove --spec table:sales # stop, and remove its extract
2180 ./script/bi-extract --list
2181 [--data-dir DIR] [--model-dir DIR]
2182  
2183 It prints a line for each extract it builds: the rows, the columns, the file
2184 and the time. Exit status is 0 on success, 1 when an extract could not be built
2185 (the others still are), and 2 on bad arguments. Run C<--all> from cron after
2186 the data files are refreshed.
2187  
2188 =head2 script/bi-alert
2189  
2190 Reports one or more saved analyses, and turns a crossed threshold into a
2191 non-zero exit status so cron can act on it:
2192  
2193 ./script/bi-alert --analysis ID [--analysis ID ...] \
2194 [--threshold MEASURE:OP:VALUE] \
2195 [--threshold ANALYSIS:MEASURE:OP:VALUE ...] \
2196 [--suppress DURATION] [--state-file PATH] [--recovery] \
2197 [--notify-cmd COMMAND] \
2198 [--data-dir DIR] [--model-dir DIR] [--dry-run]
2199  
2200 The digest goes to STDOUT and nothing leaves the machine unless
2201 C<--notify-cmd> says where: otherwise pipe it into mail, a webhook or a ticket
2202 creator yourself. Exit status is 0 when nothing is announced, 1 when a
2203 threshold is crossed and announced, 2 on error, and 3 when a crossing that was
2204 announced has ended and C<--recovery> was asked to announce that. C<--dry-run>
2205 reports without recording anything against the throttle and without running the
2206 delivery command.
2207  
2208 Both of those options may be repeated, so one run can watch several analyses
2209 and several measures:
2210  
2211 ./script/bi-alert --analysis abc123 --analysis def456 \
2212 --threshold count:gt:1000 \
2213 --threshold def456:sum:lt:50 --suppress 1h
2214  
2215 A threshold with no analysis prefix belongs to the first C<--analysis>; the
2216 C<ANALYSIS:MEASURE:OP:VALUE> form names another of those listed. Each analysis
2217 is reported in full, and each (analysis, threshold) pair is a check of its own:
2218 it prints its own line, keeps its own entry in the state file, and is throttled
2219 and recovered on its own schedule. One crossing never silences, recovers or
2220 delivers another. When a run announces both a crossing and a recovery it exits
2221 1, so a threshold that is still crossed is not reported as an all-clear.
2222  
2223 C<--suppress> throttles a threshold that stays crossed, so a condition that is
2224 still true on the next cron run is not reported as news again: it takes a
2225 duration (C<30s>, C<15m>, C<6h>, C<2d>, C<1w>, or a bare number of seconds), the
2226 first crossing is announced, and repeats inside the period are not. The
2227 throttle is one small JSON file, C<alerts-state.json> in the model directory
2228 unless C<--state-file> names elsewhere, keyed by analysis and threshold and
2229 written atomically. It is kept only when C<--suppress> is used or such a file
2230 already exists, so with no C<--suppress> the script is as stateless as it has
2231 always been and writes nothing.
2232  
2233 A run inside the period prints C<THRESHOLD SUPPRESSED> and exits 0: it is not
2234 announcing anything, so a cron wrapper keyed on the exit status stays quiet.
2235 When the crossing ends the run prints C<THRESHOLD CLEARED> once and drops its
2236 entry, which means the next crossing is announced immediately instead of
2237 waiting out a period that belonged to an incident that is over. A state file
2238 that cannot be read is reported and treated as empty, so a damaged file costs
2239 one repeated alert rather than silence, and C<--dry-run> records nothing, so a
2240 rehearsal cannot use up the alert a real run depends on.
2241  
2242 C<--recovery> announces that ending instead of merely reporting it: the run
2243 prints C<THRESHOLD RECOVERED> and exits 3, so a wrapper that mails on any
2244 non-zero status mails the all-clear as well as the alert, and a wrapper that
2245 should not is a wrapper that does not pass C<--recovery>. The recovery goes
2246 through the same state file, which is what makes it possible: an entry is the
2247 record of a crossing that was announced, so finding one is what tells the run
2248 that an incident has ended rather than that a threshold was never crossed. It
2249 is therefore said once per crossing and only for a crossing somebody was told
2250 about, which is also why C<--recovery> keeps that file even with no C<--suppress>
2251 to throttle. The recovery names both dates -- C<crossed since> and C<cleared>.
2252  
2253 C<--notify-cmd> delivers the announcement itself, so a cron line is one command
2254 rather than a shell wrapper around this one:
2255  
2256 ./script/bi-alert --analysis abc123 --threshold count:gt:1000 --suppress 1h \
2257 --recovery --notify-cmd 'mail -s "$BI_ALERT_SUBJECT" oncall@example.com'
2258  
2259 The command runs once per run, through the shell, and only when something is
2260 announced: a crossing (exit 1), or its end with C<--recovery> (exit 3). A
2261 suppressed repeat, an uncrossed threshold, an error and a C<--dry-run> run it
2262 not at all, and twenty checks still send one mail. The whole run's text arrives
2263 on standard input, and three variables say what it is -- C<BI_ALERT_EVENT>
2264 (C<crossed> if any crossing was announced, otherwise C<recovery>),
2265 C<BI_ALERT_SUBJECT> (the verdict line when one thing was announced, otherwise a
2266 count such as C<2 threshold crossings, 1 recovery>) and
2267 C<BI_ALERT_MESSAGE_FILE> (the same text in a file, for a command that has to
2268 post a body: C<curl -d @$BI_ALERT_MESSAGE_FILE ...>). A delivery that fails is
2269 reported on STDERR and leaves the exit status alone, but the announcements are
2270 not recorded, so the next run announces them again rather than the alert being
2271 lost for the rest of the period.
2272  
2273 C<script/database-bi> is the web application launcher: run with no arguments it
2274 starts the reloading development server, with the name of one database it does
2275 the same and shows that database, with a command word it runs that command, and C<hypnotoad script/database-bi> starts a preforking server (see
2276 L</SYNOPSIS>).
2277  
2278 =head1 COMMON PITFALLS
2279  
2280 =over 4
2281  
2282 =item B<The launcher's shebang must name perl, not morbo>
2283  
2284 morbo starts the application as C<perl script/database-bi daemon>, and perl
2285 hands a script whose C<#!> line does not mention perl to the interpreter that
2286 line names. With a C<morbo> shebang every worker therefore started another
2287 morbo: the chain grew a process a second, kept the listening port, and only
2288 stopped when the environment copied to each child was too large for C<exec>
2289 (F<Argument list too long>). The shebang is perl and the script starts the
2290 server itself when it is given no arguments.
2291  
2292 =item B<The launcher's last statement must be its call to start_app>
2293  
2294 C<hypnotoad> loads the launcher instead of running it, with no arguments, and
2295 takes the value of its last statement as the application. So the launcher must
2296 start the development server only when C<MOJO_APP_LOADER> is not set, and must
2297 not end in C<1;>. Otherwise C<hypnotoad script/database-bi> hangs in a
2298 development server or refuses to start.
2299  
2300 =item B<The configuration file is optional but must be valid Perl if present>
2301  
2302 C<database_bi.conf> is loaded by C<Mojolicious::Plugin::Config>, which
2303 evaluates it as a Perl data structure. If the file exists but contains a
2304 syntax error, the application will refuse to start. If the file does not
2305 exist, built-in defaults are used and no error occurs. The file must return
2306 a hashref:
2307  
2308 # database_bi.conf -- correct
2309 { data_dir => 'data', platform => 'web', language => 'en' }
2310  
2311 # WRONG -- missing braces
2312 data_dir => 'data'
2313  
2314 =item B<data_dir is relative to the application home directory, not the process cwd>
2315  
2316 Setting C<data_dir =E<gt> 'data'> looks for a folder called C<data/> in the
2317 same directory as the C<script/database-bi> launcher, regardless of where you
2318 run the server from. An absolute path works on any system:
2319  
2320 { data_dir => '/var/db/mydata' }
2321  
2322 =item B<The download_dir default is computed once at startup>
2323  
2324 When the application starts, it picks the export directory in this order:
2325 the first of C<export_dirs> (if set, as its real path), C<~/Downloads> (if it exists), then
2326 C<$HOME>, then the system temp directory.
2327 This value is fixed for the life of the process. Renaming or creating
2328 C<~/Downloads> after the server starts has no effect. To force a different
2329 default, set it before starting:
2330  
2331 { data_dir => 'data' } # and create ~/Downloads before starting the server
2332  
2333 =item B<Adding a new language requires a template directory, not just a config change>
2334  
2335 Setting C<language =E<gt> 'de'> in C<database_bi.conf> tells the controller
2336 to look for templates in C<templates/web/de/>. If that directory does not
2337 exist, the controller automatically falls back to the default language, and
2338 the same fallback applies when a browser asks for a language no directory
2339 serves. To add German support: (1) create C<templates/web/de/>, (2) copy and
2340 translate the C<.html.tt> files from C<templates/web/en/>, then (3) set the
2341 config.
2342  
2343 A directory may also name a region, as C<templates/web/en-us/> does: a browser
2344 whose Accept-Language asks for that region (C<en-US>) gets it, and every other
2345 request for the language (C<en-GB>, C<en-CA>, plain C<en>) gets
2346 C<templates/web/en/>. C<< language => 'en-us' >> makes it the default.
2347  
2348 The distribution ships C<en> (British English) and C<en-us> (American
2349 English). C<en-us> is not edited by hand: it is made from C<en> by
2350 C<author/make-en-us>, which respells the prose (colour, centre, summarise) and
2351 leaves the names the code reads alone. Run it after changing an C<en>
2352 template; C<t/en-us.t> fails until the two agree.
2353  
2354 The server's messages (errors, warnings, refusals) follow the same choice.
2355 L<CGI::Lingua> picks the language from the request's C<Accept-Language>, or,
2356 for the command-line scripts, from the locale (C<LANG=en_US.UTF-8>), and
2357 L<Database::BI::I18N> gives each message in it. A translation is a module,
2358 L<Database::BI::I18N::en_us> for American English, listing only the messages
2359 that read differently; C<t/i18n.t> fails when a British message needs an
2360 American spelling it does not list. The tutorial's text is still in British
2361 English.
2362  
2363 =item B<Supported data file extensions are: csv, db, sql, sqlite, sqlite3, xml, psv, tsv, xlsx, json>
2364  
2365 The application recognises C<.csv>, C<.db>, C<.sql>, C<.sqlite>, C<.sqlite3>,
2366 C<.xml>, C<.psv>, C<.tsv>, C<.xlsx> and C<.json> files. All three SQLite extensions
2367 (C<.sql>, C<.sqlite>, C<.sqlite3>) are treated identically -- C<inventory.sqlite>
2368 and C<inventory.sqlite3> are both opened as SQLite databases without renaming.
2369 Excel C<.xlsx> files are read by L<Database::Abstraction>, using
2370 C<Spreadsheet::ParseXLSX>.
2371  
2372 A C<.json> file is a table when it is a list of objects (each one a row), an
2373 object of objects (each key a row, shown in a column C<id>, or C<key> when the
2374 rows have an C<id> of their own), or an object holding lists of objects, as a
2375 report does: C<{"tool": "scan", "findings": [...], "hosts": [...]}>. Each
2376 such list is a table, as a SQLite file's tables are: a file with more than one
2377 opens on C</tables>, with a card for each, and other values beside the lists
2378 (a date, a count, an object of totals) are left out. A cell holding an object
2379 or a list shows as its JSON text, C<true> and C<false> as words and C<null> as
2380 an empty cell, and the columns are in the order the file first names them. A
2381 file of another format with the same name in the same folder is the one read.
2382  
2383 URLs will work.
2384 For example enter
2385 L<https://worldpopulationreview.com/country-rankings/immigration-by-country>
2386 into the C<Import from a web page> field on the dashboard.
2387  
2388 =item B<The open_table helper lowercases the table name>
2389  
2390 When the router matches C<GET /view/Sales> or C<GET /view/SALES>, the table
2391 name is lowercased to C<sales> before being passed to the helper. The data
2392 file on disc must therefore also be lowercase (C<sales.csv>, not
2393 C<Sales.csv>).
2394  
2395 =item B<Redis support requires the Redis or Redis::Fast module>
2396  
2397 Redis data source access is an optional feature. If neither C<Redis> nor
2398 C<Redis::Fast> is installed, entering a C<redis:...> spec returns a 404.
2399 To enable Redis support, install either module:
2400  
2401 cpanm Redis::Fast # recommended -- C bindings, faster
2402 cpanm Redis # pure-Perl fallback
2403  
2404 Redis keys must follow the C<tablename:entry> convention: each key is the
2405 table name, a colon, and an entry identifier; the value is a Redis hash
2406 whose fields become the row columns. For example:
2407  
2408 HSET players:row1 name Alice score 10
2409 HSET players:row2 name Bob score 20
2410  
2411 Opening C<redis:players|redis://localhost:6379> then displays a two-row table
2412 with columns C<entry>, C<name>, and C<score>.
2413  
2414 =back
2415  
2416 =head1 LIMITATIONS
2417  
2418 =over 4
2419  
2420 =item *
2421  
2422 The login is one shared password. There are no accounts, so every reader who
2423 knows it can do everything, and the log cannot say who did what.
2424  
2425 =item *
2426  
2427 Only read operations on data files are supported. Write-back (editing
2428 cell values in the browser and saving them to the data file) is not
2429 implemented.
2430  
2431 =item *
2432  
2433 A MariaDB table is read, never written, but the application does not enforce
2434 that on the server: give it an account with only C<SELECT>. Anyone who can reach
2435 the application can open any table that account can read.
2436  
2437 =item *
2438  
2439 Only a page of an unfiltered table is read with C<LIMIT>. A filter, Merge, Combine
2440 or an export reads the whole MariaDB table into memory, so a very large table is
2441 better opened through a view that narrows it.
2442  
2443 =item *
2444  
2445 A MariaDB table or view whose name holds anything but letters, digits and
2446 underscores cannot be opened. C</tables> shows it with a C<CREATE VIEW> statement
2447 that gives it a usable name.
2448  
2449 =item *
2450  
2451 A relative time range is resolved against the server's clock. An ISO
2452 timestamp, C<YYYY-MM-DDTHH:MM:SS> (a space instead of the C<T>, no seconds, a
2453 fraction of a second, and a trailing C<Z> or offset such as C<+01:00> are all
2454 accepted), is read as written: the zone is not applied, so
2455 C<2026-11-06T13:00:00Z> and C<2026-11-06T13:00:00+05:00> are the same time to
2456 a filter. A non-ISO timestamp is read as far as its C<YYYY-MM-DD> prefix, so
2457 C<last_24_hours> is exact only for ISO timestamps in the server's zone.
2458  
2459 =item *
2460  
2461 Multi-table left joins are performed in memory by L<Database::Join>. All
2462 component tables are fetched into RAM before the merge; this is not
2463 suitable for files that do not fit in the process's available memory.
2464  
2465 =item *
2466  
2467 Upload staging is managed automatically. On every server startup,
2468 C<Database::BI> evicts upload subdirectories whose modification time is
2469 older than 24 hours. The "Clear upload cache" button (C<POST
2470 /uploads/clear>) triggers an immediate full purge regardless of age.
2471  
2472 =item *
2473  
2474 C<Sub::Protected>/:Protected enforcement relies on the CHECK compilation
2475 phase. When a module is loaded dynamically at test time (e.g. via
2476 C<Test::Mojo->new(...)>), the CHECK phase has already passed and the
2477 "Too late to run CHECK block" warning is emitted -- the access
2478 restriction is not enforced in that test context. This does not affect
2479 production (morbo/hypnotoad) deployments where modules are compiled on
2480 startup. Unlike the former C<Sub::Private> approach, C<Sub::Protected>
2481 does not delete stash entries, so OO dispatch C<< $self->_method() >>
2482 works correctly in production without any special workarounds.
2483  
2484 =back
2485  
2486 =head1 ROADMAP
2487  
2488 Features planned for future releases (post-0.019.0). Items are ordered by
2489 priority. Items shipped before this release (XLSX export, the copy-link
2490 button, the column statistics panel, folder filter/join propagation, the
2491 aggregate/measure layer and the C<between> and C<in> filter operators) are no
2492 longer listed.
2493  
2494 =over 4
2495  
2496 =item * B<Multi-sheet XLSX> (Medium) -- C<_detect_file_info> reads only the
2497 first worksheet. A C<?sheet=> URL param with a sheet-name picker would
2498 expose the full workbook.
2499  
2500 =item * B<SSE streaming for large join results> (Low) -- The join pipeline
2501 blocks the HTTP response until all rows are assembled. Mojolicious supports
2502 server-sent events, which could progressively stream rows to a JS table
2503 renderer, giving visible progress on slow joins.
2504  
2505 =item * B<Boundary overlay on maps> (Low) -- C<HTML::OSM> can draw GeoJSON and
2506 choropleth layers. A C<gj=E<lt>pathE<gt>> parameter would overlay a local
2507 GeoJSON file, optionally shaded by an aggregated measure. This is the one
2508 Phase 6 item not implemented: it is optional in the phase spec, and no boundary
2509 data is shipped or downloaded.
2510  
2511 =back
2512  
2513 =cut
2514  
2515 # =============================================================================
2516 # KNOWN GAPS & ROADMAP
2517 # =============================================================================
2518 #
2519 # Items marked [NEW 0.010.0] were identified during the 0.010.0 gap analysis.
2520 # Items completed in 0.012.0:
2521 # A. Pagination -- done: /view, /open, /join, /combine, /export all paginate.
2522 # 1. dbi_source() proxy -- done: DataSource::dbi_source() delegating method added.
2523 # 2. limit/offset plumbing -- done: /join, /combine, /export thread pagination.
2524 # 3. order_by delegation -- done: ?sort=col&dir=asc|desc on /join and /combine.
2525 # 4. Filter pushdown -- done: _filter_specs_to_criteria translates f= specs to
2526 # SQL criteria for SQLite sources; _grouped_totals and _fetch_page both use it.
2527 # 5. Schema warnings UI -- done: every route that builds a join compares the
2528 # declared types at each step (DataSource::schema, _schema_type_warnings),
2529 # and the join page shows a banner. The chart/export pipeline records the
2530 # same warnings; /export returns them in the X-BI-Warnings header.
2531 # Database::Join's own warn_schema_type_mismatch cannot fire here, because
2532 # these joins always pass a collision_prefix for the right table.
2533 # 6. updated() in DataSource -- done: DataSource::updated() delegates to inner DA.
2534 #
2535 # ---- POST-RELEASE FEATURE ROADMAP ----
2536 #
2537 # Completed since 0.010.0, so no longer listed here: XLSX export (B), the
2538 # copy-link button on chart pages (C), the column statistics panel (D), and
2539 # folder-view filter/join propagation (F). The same list lives in the POD
2540 # ROADMAP above; keep the two in step.
2541 #
2542 # E. [NEW 0.010.0] Multi-sheet XLSX (Medium priority)
2543 # _detect_file_info reads only worksheet index 0. A ?sheet= URL param
2544 # with a sheet-name picker would expose the full workbook.
2545 #
2546 # H. [NEW 0.010.0] SSE streaming for large join results (Low priority)
2547 # The join pipeline blocks the HTTP response until all rows are assembled.
2548 # Mojolicious write_chunk / finish can progressively stream rows, giving
2549 # visible progress on slow multi-table joins.
2550 #
2551 # ---- OPEN INTEGRATION GAPS ----
2552 #
2553 # I. parallel => 1 for multi-step join chains
2554 # --------------------------------------------
2555 # When the user chains three or more j= join steps, Database::BI creates a
2556 # single Database::Join object with multiple secondary databases. Enabling
2557 # parallel => 1 on that object would issue the secondary DA fetches
2558 # concurrently when the threads module is available. However: DataSource is
2559 # not thread-safe (it holds a CHI cache handle and open file handles). Before
2560 # enabling parallel, DataSource must be audited for thread safety -- at minimum
2561 # the CHI driver must be confirmed re-entrant, or a separate DataSource clone
2562 # created per thread.
2563 # Short-term: do not enable parallel => 1; add a FIXME comment to the join
2564 # route noting why it is disabled. Longer-term: implement DataSource::clone()
2565 # that creates an independent handle per thread.
2566 #
2567 # J. per-table filters => on the join pipeline
2568 # --------------------------------------------
2569 # The /join route accepts f=col:op:val filters that are applied as post-join
2570 # in-memory filtering in the template. Database::Join 0.007.0's filters =>
2571 # constructor parameter applies per-database criteria at spill time on the
2572 # SQLite backend, which is far cheaper for large tables. When a filter column
2573 # is known to belong to a specific source table (i.e. it appears in
2574 # $join->columns() but also in one component DA's columns()), the join route
2575 # could partition filters by owning database and pass them as filters => to
2576 # the Database::Join constructor. Filters on the merged view (post-join
2577 # computed columns) must remain as template-side filtering.
2578 #
2579 # K. collision_prefix exposure in the join UI
2580 # --------------------------------------------
2581 # When two joined tables share a column name, Database::Join's default
2582 # last-table-wins behaviour silently discards one table's value. The join
2583 # UI (the "Merge data" panel) could offer a "rename duplicate columns from
2584 # right table" text field so users can configure collision_prefix without
2585 # writing code. The entered prefix would be passed as
2586 # collision_prefix => { 1 => $prefix } when constructing Database::Join.
2587 # =============================================================================
2588  
2589 # _table_cache_root($app, \%cache_conf) -> (root_dir => $dir) | ()
2590 #
2591 # A driver that keeps the table cache on disc (File, FastMmap) is given a
2592 # directory: cache => { root_dir } when the config names one, else the one
2593 # Database::BI::CacheDir chooses for this class (Object::Configure, CACHEDIR,
2594 # CACHE_DIR, cache_dir in this config, then <model_dir>/cache). CHI's own
2595 # default would be a folder under the system temp directory. The Memory
2596 # driver keeps nothing on disc and takes no directory.
2597 78 ▶sub _table_cache_root ($app, $cache_conf) {
Subroutine _table_cache_root: called CC=6 SCAR=31.0
2598 78 ▶ my $driver = $cache_conf->{driver} // 'Memory';
Condition50%1/2
$cache_conf->{"driver"} // "Memory"
l!l
078
MC/DC0%0/1
$cache_conf->{"driver"} // "Memory"
$cache_conf->{"driver"}
Truth table50%1/2
A
A= $cache_conf->{"driver"}
Aresult
11
00
2599  
2600 78 ▶ return () unless $DISC_CACHE_DRIVER{$driver};
Branch: 1 branch
BranchTrueFalseTotal
unless $DISC_CACHE_DRIVER{$driver} 78 0 78
2601 0 ▶ return (root_dir => $cache_conf->{root_dir}) if defined $cache_conf->{root_dir};
Branch: 1 branch
BranchTrueFalseTotal
if defined $cache_conf->{"root_dir"} 0 0 0
2602  
2603 0 require Database::BI::CacheDir;
2604  
2605 0 ▶ my $model_dir = Mojo::File->new($app->config('model_dir') // '.bi');
Condition0%0/2
$app->config("model_dir") // ".bi"
l!l
00
MC/DC0%0/1
$app->config("model_dir") // ".bi"
$app->config("model_dir")
Truth table0%0/2
A
A= $app->config("model_dir")
Aresult
11
00
2606 0 ▶ $model_dir = $app->home->child($model_dir->to_string) unless $model_dir->is_abs;
Branch: 1 branch
BranchTrueFalseTotal
unless $model_dir->is_abs 0 0 0
2607  
2608 0 return (root_dir => Database::BI::CacheDir::resolve(
2609 class => __PACKAGE__,
2610 config => $app->config,
2611 home => $app->home->to_string,
2612 default => $model_dir->child('cache')->to_string,
2613 ));
2614 }
2615  
2616 80 ▶sub startup ($self) {
Subroutine startup: called CC=26 SCAR=36.1
Pod documented
2617 80 $self->plugin('Config', {
2618 default => {
2619 data_dir => $DEFAULT_DATA_DIR,
2620 platform => $DEFAULT_PLATFORM,
2621 language => $DEFAULT_LANGUAGE,
2622 d3_url => $DEFAULT_D3_URL,
2623 }
2624 });
2625  
2626 # The key the map page signs the places it lists with (/geo/lookup checks
2627 # it). Mojolicious would use the moniker, which anyone can read; a config
2628 # secrets => [...] keeps lists valid across restarts, and without one each
2629 # start makes its own. This runs before hypnotoad forks, so every worker
2630 # shares it.
2631 # The sites that may frame /embed/. A mistake here is a security setting
2632 # that does not do what it says, so it stops the start rather than being
2633 # dropped quietly.
2634 80 _check_embed_frame_ancestors($self->config('embed_frame_ancestors'));
2635 80 _check_strict_transport_security($self->config('strict_transport_security'));
2636 80 _check_permissions_policy($self->config('permissions_policy'));
2637 80 _check_cross_origin_opener_policy($self->config('cross_origin_opener_policy'));
2638 80 _check_allowed_hosts($self->config('allowed_hosts'));
2639 80 _check_file_roots($self->config('file_roots'), $self->home);
2640 80 _check_url_private_hosts($self->config('url_private_hosts'));
2641 80 _check_map_bounds_ranks($self->config('map_bounds_ranks'));
2642 80 _check_share_secret($self->config('share_secret'));
2643 80 _check_export_dirs($self, $self->config('export_dirs'));
2644 80 ▶ croak 'ai_calls_per_hour: give a whole number of provider calls an hour, or 0 for no limit'
Branch: 1 branch
BranchTrueFalseTotal
if defined $self->config("ai_calls_per_hour") and not $self->config("ai_calls_per_hour") =~ /\A\d+\z/u 0 80 80
Condition33%1/3
defined $self->config("ai_calls_per_hour") and not $self->config("ai_calls_per_hour") =~ /\A\d+\z/u
!ll&&!rl&&r
8000
MC/DC0%0/2
defined $self->config("ai_calls_per_hour") and not $self->config("ai_calls_per_hour") =~ /\A\d+\z/u
defined $self->config("ai_calls_per_hour") not $self->config("ai_calls_per_hour") =~ /\A\d+\z/u
Truth table33%1/3
A and not(B)
A= defined $self->config("ai_calls_per_hour")B= not $self->config("ai_calls_per_hour") =~ /\A\d+\z/u
ABresult
0X0
100
111
2645 if defined $self->config('ai_calls_per_hour') && $self->config('ai_calls_per_hour') !~ /\A\d+\z/;
2646 80 _check_login($self->config('password_hash'), $self->config('login_expiration'));
2647 80 _require_export_dirs_with_password($self->config('password_hash'), $self->config('export_dirs'));
2648  
2649 # Only this machine can reach a server unless the configuration says
2650 # otherwise. Mojolicious's own default is every interface, which put an
2651 # application with no login on the local network. -l and MOJO_LISTEN still
2652 # win: the daemon and prefork commands read MOJO_LISTEN only when started,
2653 # after this, and -l replaces it.
2654 80 my @listen = _listen_locations($self->config('listen'));
2655 80 ▶ $ENV{MOJO_LISTEN} = join(',', @listen) unless length($ENV{MOJO_LISTEN} // '');
Branch: 1 branch
BranchTrueFalseTotal
unless length($ENV{"MOJO_LISTEN"} // "") 7 73 80
Condition100%2/2
$ENV{"MOJO_LISTEN"} // ""
l!l
737
MC/DC100%1/1
$ENV{"MOJO_LISTEN"} // ""
$ENV{"MOJO_LISTEN"}
Truth table100%2/2
A
A= $ENV{"MOJO_LISTEN"}
Aresult
11
00
2656 80 _default_hypnotoad_listen($self->config);
2657 80 _require_password_if_open($self, $ENV{MOJO_LISTEN}, $self->config->{hypnotoad});
2658  
2659 # script/database-bi password makes the password_hash line.
2660 78 push @{ $self->commands->namespaces }, 'Database::BI::Command';
2661  
2662 78 my $secrets = $self->config('secrets');
2663 0 ▶ $self->secrets((ref($secrets) eq 'ARRAY' && @{$secrets})
Branch: 1 branch
BranchTrueFalseTotal
ref $secrets eq 'ARRAY' && @{$secrets;} ? : 0 78 78
Condition33%1/3
ref $secrets eq "ARRAY" and @{$secrets;}
!ll&&!rl&&r
7800
MC/DC0%0/2
ref $secrets eq "ARRAY" and @{$secrets;}
ref $secrets eq "ARRAY" @{$secrets;}
Truth table33%1/3
A and B
A= ref $secrets eq "ARRAY"B= @{$secrets;}
ABresult
0X0
100
111
2664 ? $secrets : [ Mojo::Util::generate_secret() ]);
2665  
2666 # The login session (see "Logging in"). The cookie is signed with the
2667 # secrets, HttpOnly, SameSite=Lax, and Secure whenever the request came over
2668 # https; it lasts login_expiration seconds from the last request. With no
2669 # secrets => [...] each start makes a new key, so a restart logs everyone out.
2670 78 ▶ $self->sessions(Database::BI::Sessions->new(
Condition66%2/3
$self->config("login_expiration") // $DEFAULT_LOGIN_EXPIRATION
l!l&&r!l&&!r
0735
MC/DC50%1/2
$self->config("login_expiration") // $DEFAULT_LOGIN_EXPIRATION
$self->config("login_expiration") $DEFAULT_LOGIN_EXPIRATION
Truth table66%2/3
A // B
A= $self->config("login_expiration")B= $DEFAULT_LOGIN_EXPIRATION
ABresult
1X1
011
000
2671 cookie_name => 'database_bi',
2672 default_expiration => $self->config('login_expiration') // $DEFAULT_LOGIN_EXPIRATION,
2673 samesite => 'Lax',
2674 ));
2675  
2676 # Cap inbound request body size. When the limit fires, Mojolicious sets
2677 # req->is_limit_exceeded and still dispatches to the controller (with partial
2678 # content in the upload asset). The controller checks is_limit_exceeded and
2679 # returns 413 before writing to disc.
2680 78 $self->max_request_size($MAX_REQUEST_SIZE);
2681  
2682 # Default export/save directory: ~/Downloads when it exists, else HOME,
2683 # else system tmpdir. Resolved once per process and stored as a stash
2684 # default so every action can read $self->stash('download_dir').
2685 # With export_dirs, the first of them: the panel would open on a refusal.
2686 # Resolved as the export checks resolve it (_real_folders), so the panel
2687 # opens on the folder spelled as /api/dirs and the rest of the application
2688 # spell it (on Windows, child() joins with \, realpath gives /, and a
2689 # link is followed by hand).
2690 78 ▶ my $home = $ENV{HOME} // '';
Condition50%1/2
$ENV{"HOME"} // ""
l!l
780
MC/DC0%0/1
$ENV{"HOME"} // ""
$ENV{"HOME"}
Truth table50%1/2
A
A= $ENV{"HOME"}
Aresult
11
00
2691 78 ▶ my $dl_dir = ($home && -d "$home/Downloads") ? "$home/Downloads"
Branch: 1 branch
BranchTrueFalseTotal
$home && -d "$home/Downloads" ? : 0 78 78
Condition33%2/6
$home and -d "$home/Downloads"
!ll&&!rl&&r
0780
$home or "File::Spec"->tmpdir
l!l&&r!l&&!r
7800
MC/DC0%0/2
$home and -d "$home/Downloads"
$home -d "$home/Downloads"
MC/DC0%0/2
$home or "File::Spec"->tmpdir
$home "File::Spec"->tmpdir
Truth table33%1/3
A and B
A= $homeB= -d "$home/Downloads"
ABresult
0X0
100
111
Truth table33%1/3
A or B
A= $homeB= "File::Spec"->tmpdir
ABresult
1X1
011
000
2692 : ($home || File::Spec->tmpdir());
2693 78 my $exports = $self->config('export_dirs');
2694 0 ▶ if (ref($exports) eq 'ARRAY' && @{$exports}) {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $exports eq "ARRAY" and @{$exports;}) 0 78 78
Condition33%1/3
ref $exports eq "ARRAY" and @{$exports;}
!ll&&!rl&&r
7800
MC/DC0%0/2
ref $exports eq "ARRAY" and @{$exports;}
ref $exports eq "ARRAY" @{$exports;}
Truth table33%1/3
A and B
A= ref $exports eq "ARRAY"B= @{$exports;}
ABresult
0X0
100
111
2695 0 my $first = $exports->[0];
2696 0 ▶ my $dir = File::Spec->file_name_is_absolute($first) ? Mojo::File->new($first) : $self->home->child($first);
Branch: 1 branch
BranchTrueFalseTotal
'File::Spec'->file_name_is_absolute($first) ? : 0 0 0
2697 0 require Database::BI::Controller::Dashboard;
2698 0 ▶ $dl_dir = (-e "$dir" ? Database::BI::Controller::Dashboard::_resolved_path("$dir") : undef) // "$dir";
Branch: 1 branch
BranchTrueFalseTotal
-e "$dir" ? : 0 0 0
Condition0%0/2
(-e "$dir" ? Database::BI::Controller::Dashboard::_resolved_path("$dir") : undef) // "$dir"
l!l
00
MC/DC0%0/1
(-e "$dir" ? Database::BI::Controller::Dashboard::_resolved_path("$dir") : undef) // "$dir"
(-e "$dir" ? Database::BI::Controller::Dashboard::_resolved_path("$dir") : undef)
Truth table0%0/2
A
A= (-e "$dir" ? Database::BI::Controller::Dashboard::_resolved_path("$dir") : undef)
Aresult
11
00
2699 }
2700 78 $self->defaults(download_dir => $dl_dir);
2701  
2702 # Register the TT plugin as 'tt' (not the default 'tt2') so that template
2703 # files keep the .html.tt extension and handler => 'tt' in render() calls
2704 # resolves correctly via Template::Provider::Mojo. TT options must live
2705 # under the 'template' key; top-level keys are plugin options only.
2706 78 $self->plugin('TemplateToolkit', {
2707 name => 'tt',
2708 template => {
2709 POST_CHOMP => 1,
2710 TRIM => 1,
2711 },
2712 });
2713  
2714 # Response compression (gzip/Brotli), ETag/304 handling, and HTML
2715 # whitespace minification. Level 1 leaves <script>/<style> content
2716 # alone; level 2 runs aggressive JS minification and is not safe with
2717 # the inline JavaScript in the dashboard and home templates.
2718 # vary_by adds Accept-Language to the cache key so VWF language
2719 # detection stores one copy per language.
2720 78 my $page_cache_dir = $self->config->{page_cache_dir};
2721 78 my $page_chi;
2722 78 ▶ if ($page_cache_dir) {
Branch: 1 branch
BranchTrueFalseTotal
if ($page_cache_dir) 0 78 78
2723 # page_cache_dir turns the page cache on, and is where it goes when no
2724 # cache directory is set (Object::Configure, CACHEDIR, CACHE_DIR,
2725 # cache_dir in this config). The pages keep to their own namespace,
2726 # so they can share that directory with the other caches.
2727 0 my $default = Mojo::File->new($page_cache_dir);
2728 0 ▶ $default = $self->home->child($page_cache_dir) unless $default->is_abs;
Branch: 1 branch
BranchTrueFalseTotal
unless $default->is_abs 0 0 0
2729 0 require Database::BI::CacheDir;
2730 0 my $dir = Mojo::File->new(Database::BI::CacheDir::resolve(
2731 class => __PACKAGE__,
2732 config => $self->config,
2733 home => $self->home->to_string,
2734 default => $default->to_string,
2735 ));
2736 0 $dir->make_path;
2737 0 $page_chi = CHI->new(
2738 driver => 'File',
2739 root_dir => $dir->to_string,
2740 namespace => 'Database::BI::Pages',
2741 );
2742 }
2743 # No Last-Modified: the plugin's date is that of the code and templates, not
2744 # of the data, and with it and no Cache-Control a browser may reuse a reply
2745 # without asking. A panel that reopened after a save then listed what was
2746 # saved before it. The ETag alone makes every reuse a revalidation.
2747 $self->plugin('Buffer', {
2748 optimise_content => 1,
2749 last_modified => 0,
2750 vary_by => ['accept-language'],
2751 ($page_chi ? (
2752 cache => $page_chi,
2753 78 ▶ cache_duration => $self->config->{page_cache_duration} // '5 minutes',
Branch: 1 branch
BranchTrueFalseTotal
$page_chi ? : 0 78 78
Condition0%0/2
$self->config->{"page_cache_duration"} // "5 minutes"
l!l
00
MC/DC0%0/1
$self->config->{"page_cache_duration"} // "5 minutes"
$self->config->{"page_cache_duration"}
Truth table0%0/2
A
A= $self->config->{"page_cache_duration"}
Aresult
11
00
2754 ) : ()),
2755 });
2756  
2757 # The Content-Security-Policy nonce for this request. Every <script> the
2758 # templates write carries it, as do those HTML::D3 and HTML::OSM write
2759 # (given nonce => $c->csp_nonce), and the policy runs no inline script
2760 # without it. It is made before the action runs, so a template rendered
2761 # before anything asked for it still has one, and it is fresh for every
2762 # request: markup that reached a page through its data cannot know it.
2763 2184 ▶ $self->hook(before_dispatch => sub {
Subroutine __ANON__: called CC=1 SCAR=0.0
2764 2184 shift->stash(csp_nonce => _new_nonce());
2765 78 });
2766  
2767 # The language this request's messages are given in, chosen by CGI::Lingua
2768 # from its Accept-Language (Database::BI::I18N::choose), with the
2769 # configured language when it names none we have. It is set with local
2770 # around the whole dispatch, so a message raised anywhere while the request
2771 # is answered - a refusal in the hook below, a model's croak - is in the
2772 # reader's language, and the next request starts afresh. Every action
2773 # answers before it returns; one that answered later, from the event loop,
2774 # would need to set it again there.
2775 2184 ▶ $self->hook(around_dispatch => sub {
Subroutine __ANON__: called CC=1 SCAR=0.0
2776 2184 my ($next, $c) = @_;
2777 local $Database::BI::I18N::LANGUAGE = Database::BI::I18N::choose(
2778 accept => scalar $c->req->headers->accept_language,
2779 default => $c->app->config->{language},
2780 2184 );
2781 2184 return $next->();
2782 78 });
2783 # Refuse what another site's page asks of this one through the reader's
2784 # browser: a form posted from there, or a page whose name was made to
2785 # resolve to this machine (DNS rebinding). Nothing here asks who the
2786 # reader is, so this is what stands between another site and POST /export,
2787 # which writes files, and /open, which reads them. A refusal is 403, as
2788 # JSON for a write (the panels read JSON back) and as text otherwise.
2789 2184 ▶ $self->hook(before_dispatch => sub {
Subroutine __ANON__: called CC=3 SCAR=16.7
2790 2184 my $c = shift;
2791 2184 require Database::BI::Controller::Dashboard;
2792 2184 ▶ my $refusal = Database::BI::Controller::Dashboard::request_refusal($c) // return;
Condition50%1/2
Database::BI::Controller::Dashboard::request_refusal($c) // (return)
l!l
02184
MC/DC0%0/1
Database::BI::Controller::Dashboard::request_refusal($c) // (return)
Database::BI::Controller::Dashboard::request_refusal($c)
Truth table50%1/2
A
A= Database::BI::Controller::Dashboard::request_refusal($c)
Aresult
11
00
2793 0 $c->stash(buffer => 0);
2794 0 ▶ return $c->render(json => { error => $refusal }, status => 403)
Branch: 1 branch
BranchTrueFalseTotal
unless $c->req->method =~ /\A(?:GET|HEAD)\z/u 0 0 0
2795 if $c->req->method !~ /\A(?:GET|HEAD)\z/;
2796 0 $c->res->headers->content_type('text/plain; charset=utf-8');
2797 0 return $c->render(data => Mojo::Util::encode('UTF-8', $refusal), status => 403);
2798 78 });
2799 1229 ▶ $self->helper(csp_nonce => sub {
Subroutine __ANON__: called CC=2 SCAR=6.9
2800 1229 my $c = shift;
2801 1229 my $nonce = $c->stash('csp_nonce');
2802 1229 ▶ $c->stash(csp_nonce => ($nonce = _new_nonce())) unless defined $nonce;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $nonce 2 1227 1229
2803 1229 return $nonce;
2804 78 });
2805  
2806 # A Content-Security-Policy on every HTML reply. The pages draw cells from
2807 # files nobody here wrote, so the policy is the second line behind the
2808 # escaping: script runs only from this site, the hosts the configuration
2809 # names, and inline blocks that carry this request's nonce; style and
2810 # images come only from this site and those hosts; nothing a page runs may
2811 # send data to another server, and no other site may frame a page. Built
2812 # per reply from the config, as d3_url is, so a test can change it.
2813 #
2814 # Mojolicious runs after_dispatch hooks last registered first, so this runs
2815 # before Buffer's: a page Buffer keeps in its page cache is stored with the
2816 # header, and when it is served again Buffer puts back the header with the
2817 # body, so the nonce in each still matches the other. The header is left
2818 # alone when it is already there for that reason.
2819 2191 ▶ $self->hook(after_dispatch => sub {
Subroutine __ANON__: called CC=20 SCAR=38.3
2820 2191 my $c = shift;
2821  
2822 2191 my $headers = $c->res->headers;
2823  
2824 # On every reply, not only pages: a download opened in the browser, or
2825 # a JSON reply viewed there, can follow a link too.
2826 2191 ▶ $headers->header('Referrer-Policy' => $REFERRER_POLICY)
Branch: 1 branch
BranchTrueFalseTotal
unless defined $headers->header("Referrer-Policy") 2191 0 2191
2827 unless defined $headers->header('Referrer-Policy');
2828  
2829 # The browser features no page uses are turned off, so a script that
2830 # got in cannot ask the reader for the camera, a location or a payment.
2831 2191 ▶ if (defined(my $features = _permissions_policy($c->app->config))) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined(my $features = _permissions_policy($c->app->config))) 2191 0 2191
2832 2191 ▶ $headers->header('Permissions-Policy' => $features)
Branch: 1 branch
BranchTrueFalseTotal
unless defined $headers->header("Permissions-Policy") 2191 0 2191
2833 unless defined $headers->header('Permissions-Policy');
2834 }
2835  
2836 # Over https, the browser is told to come back only over https. Sent on
2837 # every such reply, as browsers read it from any of them.
2838 2191 ▶ if (defined(my $hsts = _strict_transport_security($c))) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined(my $hsts = _strict_transport_security($c))) 0 2191 2191
2839 0 ▶ $headers->header('Strict-Transport-Security' => $hsts)
Branch: 1 branch
BranchTrueFalseTotal
unless defined $headers->header("Strict-Transport-Security") 0 0 0
2840 unless defined $headers->header('Strict-Transport-Security');
2841 }
2842  
2843 # A reply is what its Content-Type says, on every reply: a browser that
2844 # guessed could take a CSV export or an uploaded file's text for HTML or
2845 # script, and run what a cell holds. Every route names its type, and
2846 # the static files have theirs from their extensions.
2847 2191 ▶ $headers->header('X-Content-Type-Options' => 'nosniff')
Branch: 1 branch
BranchTrueFalseTotal
unless defined $headers->header("X-Content-Type-Options") 2191 0 2191
2848 unless defined $headers->header('X-Content-Type-Options');
2849  
2850 2191 ▶ return unless ($headers->content_type // '') =~ m{\Atext/html\b}i;
Branch: 1 branch
BranchTrueFalseTotal
unless ($headers->content_type // "") =~ m[\Atext/html\b]iu 1236 955 2191
Condition100%2/2
$headers->content_type // ""
l!l
21865
MC/DC100%1/1
$headers->content_type // ""
$headers->content_type
Truth table100%2/2
A
A= $headers->content_type
Aresult
11
00
2851  
2852 # Framing is refused whatever the policy says. frame-ancestors does
2853 # that in the policy, but a report-only policy only reports it, a
2854 # policy of the deployment's own may leave it out, and '' sends none;
2855 # older browsers read only this header. Where both are sent, a
2856 # browser that knows frame-ancestors follows it and ignores this.
2857 955 ▶ $headers->header('X-Frame-Options' => 'SAMEORIGIN')
Branch: 1 branch
BranchTrueFalseTotal
unless defined $headers->header("X-Frame-Options") 947 8 955
2858 unless defined $headers->header('X-Frame-Options');
2859  
2860 # A page keeps its window to itself: another site that opened it, or
2861 # that it opened, cannot reach it through window.opener. For pages
2862 # only, as it is a property of the document a window shows.
2863 955 ▶ if (defined(my $coop = _cross_origin_opener_policy($c->app->config))) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined(my $coop = _cross_origin_opener_policy($c->app->config))) 955 0 955
2864 955 ▶ $headers->header('Cross-Origin-Opener-Policy' => $coop)
Branch: 1 branch
BranchTrueFalseTotal
unless defined $headers->header("Cross-Origin-Opener-Policy") 955 0 955
2865 unless defined $headers->header('Cross-Origin-Opener-Policy');
2866 }
2867  
2868 # /embed/ stashes the other sites embed_frame_ancestors allows.
2869 955 my $framers = $c->stash('csp_frame_ancestors');
2870 955 ▶ $framers = [] unless ref $framers eq 'ARRAY';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $framers eq "ARRAY" 955 0 955
2871  
2872 955 my $policy = _content_security_policy($c->app->config, $c->csp_nonce, $framers);
2873 955 my $report_only = $c->app->config('content_security_policy_report_only');
2874 955 ▶ my $name = $report_only ? 'Content-Security-Policy-Report-Only' : 'Content-Security-Policy';
Branch: 1 branch
BranchTrueFalseTotal
$report_only ? : 0 955 955
2875 955 ▶ $headers->header($name => $policy) if defined $policy && !defined $headers->header($name);
Branch: 1 branch
BranchTrueFalseTotal
if defined $policy and not defined $headers->header($name) 955 0 955
Condition33%1/3
defined $policy and not defined $headers->header($name)
!ll&&!rl&&r
00955
MC/DC0%0/2
defined $policy and not defined $headers->header($name)
defined $policy not defined $headers->header($name)
Truth table33%1/3
A and not(B)
A= defined $policyB= not defined $headers->header($name)
ABresult
0X0
100
111
2876  
2877 # Allowing other sites to frame /embed/ must never allow every site.
2878 # Unless an enforced policy above already says who may, a policy of
2879 # frame-ancestors alone is added: with report-only, no policy, or one
2880 # written out without it, the page would otherwise rely on
2881 # X-Frame-Options, which these browsers ignore once frame-ancestors is
2882 # anywhere. (Browsers that know only X-Frame-Options refuse the frame.)
2883 955 ▶ if (@{$framers}) {
Branch: 1 branch
BranchTrueFalseTotal
if (@{$framers;}) 0 955 955
2884 0 ▶ my $enforced = $report_only ? undef : $policy;
Branch: 1 branch
BranchTrueFalseTotal
$report_only ? : 0 0 0
2885 0 ▶ $headers->add('Content-Security-Policy' => _frame_ancestors_directive($framers))
Branch: 1 branch
BranchTrueFalseTotal
unless defined $enforced and $enforced =~ /(?:\A|;)\s*frame-ancestors\b/iu 0 0 0
Condition0%0/3
defined $enforced and $enforced =~ /(?:\A|;)\s*frame-ancestors\b/iu
!ll&&!rl&&r
000
MC/DC0%0/2
defined $enforced and $enforced =~ /(?:\A|;)\s*frame-ancestors\b/iu
defined $enforced $enforced =~ /(?:\A|;)\s*frame-ancestors\b/iu
Truth table0%0/3
A and B
A= defined $enforcedB= $enforced =~ /(?:\A|;)\s*frame-ancestors\b/iu
ABresult
0X0
100
111
2886 unless defined $enforced && $enforced =~ /(?:\A|;)\s*frame-ancestors\b/i;
2887 }
2888 78 });
2889  
2890 # A relative time range named in the URL (?range=last_30_days&rcol=when)
2891 # becomes the f= filters every data route already applies, so the table
2892 # view, every chart, the pivot, stats, insights, the exports and a saved
2893 # canvas all narrow to the same rows without any per-route code. It runs
2894 # before dispatch because the range has to be part of the request's filter
2895 # list by the time a route reads it, and a range that cannot be honoured
2896 # answers 400 here rather than filtering the wrong rows.
2897 2184 ▶ $self->hook(before_dispatch => sub {
Subroutine __ANON__: called CC=1 SCAR=0.0
2898 2184 my $c = shift;
2899  
2900 2184 require Database::BI::Controller::Dashboard;
2901 2184 return Database::BI::Controller::Dashboard::_time_range_request($c);
2902 78 });
2903  
2904 # A filter whose operator is not one the pipeline knows is refused here,
2905 # for every route, rather than keeping every row and saying nothing.
2906 2184 ▶ $self->hook(before_dispatch => sub {
Subroutine __ANON__: called CC=2 SCAR=6.9
2907 2184 my $c = shift;
2908 2184 ▶ return if $c->res->code; # already answered (a range refused above)
Branch: 1 branch
BranchTrueFalseTotal
if $c->res->code 217 1967 2184
2909  
2910 1967 require Database::BI::Controller::Dashboard;
2911 1967 return Database::BI::Controller::Dashboard::_filter_request($c);
2912 78 });
2913  
2914 # Cross-row columns (?cs=/by=/order=) are part of the view: the table page
2915 # draws a chip for each one and its panel edits them, and every chart panel
2916 # carries them to its route, which runs the same pass over the same rows.
2917 # It is a hook rather than four stash lines in each of the seven actions that
2918 # render the dashboard, so a new action cannot silently lose them.
2919 2184 ▶ $self->hook(before_dispatch => sub {
Subroutine __ANON__: called CC=1 SCAR=0.0
2920 2184 my $c = shift;
2921  
2922 2184 require Database::BI::Controller::Dashboard;
2923 2184 $c->stash(Database::BI::Controller::Dashboard::_cross_row_ui_state($c));
2924 78 });
2925  
2926 # The rest of the view a request carries -- the join type, the reader's
2927 # filters, the files stacked in with c= and de-duplication -- for the same
2928 # reason: the dashboard's panels pass it on through one macro, and every
2929 # chart page names it under its breadcrumb, so a chart never looks like
2930 # the whole file when it is drawn from part of it.
2931 2184 ▶ $self->hook(before_dispatch => sub {
Subroutine __ANON__: called CC=5 SCAR=16.1
2932 2184 my $c = shift;
2933  
2934 2184 ▶ my $jtype = $c->param('jtype') // '';
Condition100%2/2
$c->param("jtype") // ""
l!l
2571927
MC/DC100%1/1
$c->param("jtype") // ""
$c->param("jtype")
Truth table100%2/2
A
A= $c->param("jtype")
Aresult
11
00
2935 $c->stash(
2936 view_join_type => ($jtype =~ /\A(?:left|inner|outer)\z/ ? $jtype : ''),
2937 277 view_filters => [ grep { length } @{ $c->every_param('f') } ],
2938 265 ▶ view_combines => [ grep { /\A(?:path|table|url|redis|mariadb):/i } @{ $c->every_param('c') } ],
Branch: 1 branch
BranchTrueFalseTotal
$c->param('d') ? : 4 2180 2184
2939 view_dedup => ($c->param('d') ? 1 : 0),
2940 # lk=/nolk= turn a stored lookup on or off for this view, as name=value
2941 # pairs so the macro can write them back in the order they came.
2942 4368 view_lookups => [ map { my $k = $_; map { { name => $k, value => $_ } } grep { length } @{ $c->every_param($k) } } qw(lk nolk) ],
2943 # Names only: the rest of an entry is credentials.
2944 2184 ▶ mariadb_server_names => [ sort keys %{ $c->app->config('mariadb_servers') // {} } ],
Branch: 1 branch
BranchTrueFalseTotal
$jtype =~ /\A(?:left|inner|outer)\z/u ? : 2 2182 2184
Condition50%1/2
$c->app->config("mariadb_servers") // {}
l!l
02184
MC/DC0%0/1
$c->app->config("mariadb_servers") // {}
$c->app->config("mariadb_servers")
Truth table50%1/2
A
A= $c->app->config("mariadb_servers")
Aresult
11
00
2945 );
2946 78 });
2947  
2948 # Every page names the tutorial chapter that helps with it. The layout
2949 # turns this into a "Help: ..." link beside the main Tutorial link, so a
2950 # reader on the pie chart screen lands on the pie chart chapter. It lives
2951 # here rather than in every action: a new action is one entry in the action
2952 # map in Database::BI::Tutorial, and t/tutorial.t checks it is there.
2953 2186 ▶ $self->hook(before_render => sub {
Subroutine __ANON__: called CC=34 SCAR=40.8
2954 2186 my ($c, $args) = @_;
2955  
2956 # The language is the one the action already chose, if it got that far
2957 # (_resolve_template is protected, so it cannot be asked from here).
2958 118 ▶ my $page_template = sub {
Subroutine __ANON__: called CC=6 SCAR=19.9
2959 118 my ($name) = @_;
2960 118 my $conf = $c->app->config;
2961 118 ▶ my $platform = $conf->{platform} // 'web';
Condition50%1/2
$conf->{"platform"} // "web"
l!l
1180
MC/DC0%0/1
$conf->{"platform"} // "web"
$conf->{"platform"}
Truth table50%1/2
A
A= $conf->{"platform"}
Aresult
11
00
2962 118 ▶ my $language = $c->stash('language') // '';
Condition50%1/2
$c->stash("language") // ""
l!l
0118
MC/DC0%0/1
$c->stash("language") // ""
$c->stash("language")
Truth table50%1/2
A
A= $c->stash("language")
Aresult
11
00
2963 118 ▶ $language = $conf->{language} // 'en'
Branch: 1 branch
BranchTrueFalseTotal
unless $language =~ /\A[a-z]{2}(?:[-_][A-Za-z]{2})?\z/u and -d $c->app->home->child("templates/$platform/$language") 118 0 118
Condition40%2/5
$language =~ /\A[a-z]{2}(?:[-_][A-Za-z]{2})?\z/u and -d $c->app->home->child("templates/$platform/$language")
!ll&&!rl&&r
11800
$conf->{"language"} // "en"
l!l
1180
MC/DC0%0/2
$language =~ /\A[a-z]{2}(?:[-_][A-Za-z]{2})?\z/u and -d $c->app->home->child("templates/$platform/$language")
$language =~ /\A[a-z]{2}(?:[-_][A-Za-z]{2})?\z/u -d $c->app->home->child("templates/$platform/$language")
MC/DC0%0/1
$conf->{"language"} // "en"
$conf->{"language"}
Truth table33%1/3
A and B
A= $language =~ /\A[a-z]{2}(?:[-_][A-Za-z]{2})?\z/uB= -d $c->app->home->child("templates/$platform/$language")
ABresult
0X0
100
111
Truth table50%1/2
A
A= $conf->{"language"}
Aresult
11
00
2964 unless $language =~ /\A[a-z]{2}(?:[-_][A-Za-z]{2})?\z/
2965 && -d $c->app->home->child("templates/$platform/$language");
2966 118 $c->stash(language => $language);
2967 118 return "$platform/$language/$name";
2968 2186 };
2969  
2970 # A request refused in plain text is a dead end in a browser: no
2971 # navigation, and no way back but the Back button. A page the browser
2972 # navigated to -- a GET whose Accept names text/html, which fetch() and
2973 # scripts do not send -- gets the error page instead: the trail back to
2974 # the table, and the reason, escaped by the template. Everything else
2975 # keeps the plain text below.
2976 2186 ▶ my $status = $args->{status} // 0;
Condition100%2/2
$args->{"status"} // 0
l!l
10481138
MC/DC100%1/1
$args->{"status"} // 0
$args->{"status"}
Truth table100%2/2
A
A= $args->{"status"}
Aresult
11
00
2977 2186 ▶ if (defined $args->{text} && ($status == 400 || $status == 403)
Branch: 1 branch
BranchTrueFalseTotal
if (defined $args->{"text"} and $status == 400 || $status == 403 and $c->req->method eq "GET" and ($c->req->headers->accept // "") =~ m[\btext/html\b]u and not $c->req->url->path->to_string =~ m[\A/api/]u) 0 2186 2186
Condition76%13/17
defined $args->{"text"} and $status == 400 || $status == 403 and $c->req->method eq "GET" and ($c->req->headers->accept // "") =~ m[\btext/html\b]u and not $c->req->url->path->to_string =~ m[\A/api/]u
!ll&&!rl&&r
218600
defined $args->{"text"} and $status == 400 || $status == 403 and $c->req->method eq "GET" and ($c->req->headers->accept // "") =~ m[\btext/html\b]u
!ll&&!rl&&r
16015850
defined $args->{"text"} and $status == 400 || $status == 403 and $c->req->method eq "GET"
!ll&&!rl&&r
15983585
defined $args->{"text"} and $status == 400 || $status == 403
!ll&&!rl&&r
150791588
$status == 400 or $status == 403
l!l&&r!l&&!r
584491
$c->req->headers->accept // ""
l!l
0585
MC/DC0%0/6
defined $args->{"text"} and $status == 400 || $status == 403 and $c->req->method eq "GET" and ($c->req->headers->accept // "") =~ m[\btext/html\b]u and not $c->req->url->path->to_string =~ m[\A/api/]u
defined $args->{"text"} $status == 400 $status == 403 $c->req->method eq "GET" $c->req->headers->accept not $c->req->url->path->to_string =~ m[\A/api/]u
Truth table50%5/10
A and B or C and D and E and not(F)
A= defined $args->{"text"}B= $status == 400C= $status == 403D= $c->req->method eq "GET"E= $c->req->headers->acceptF= not $c->req->url->path->to_string =~ m[\A/api/]u
ABCDEFresult
0XXXXX0
100XXX0
11X0XX0
1010XX0
11X10X0
10110X0
11X1100
1011100
11X1111
1011111
2978 && $c->req->method eq 'GET'
2979 && ($c->req->headers->accept // '') =~ m{\btext/html\b}
2980 && $c->req->url->path->to_string !~ m{\A/api/}) {
2981 $c->stash(
2982 title => $status == 403 ? 'This is not available here' : 'This page cannot be drawn',
2983 error_msg => delete $args->{text},
2984 0 ▶ error_400 => 1,
Branch: 1 branch
BranchTrueFalseTotal
$status == 403 ? : 0 0 0
2985 );
2986 0 $args->{template} = $page_template->('not_found');
2987 0 $args->{handler} = 'tt';
2988 0 $args->{format} = 'html';
2989 }
2990  
2991 # Mojolicious sends render(text => ...) as text/html. The error replies
2992 # that use it repeat what was asked for (a column, a range), so as HTML a
2993 # crafted link would run script on this site. A template or JSON escapes
2994 # for itself; plain text only has to say that it is plain text.
2995 $args->{format} //= 'txt'
2996 2186 ▶ if defined $args->{text} && !defined $c->stash('format');
Branch: 1 branch
BranchTrueFalseTotal
if defined $args->{"text"} and not defined $c->stash("format") 679 1507 2186
Condition60%3/5
defined $args->{"text"} and not defined $c->stash("format")
!ll&&!rl&&r
15070679
$args->{"format"} //= "txt"
l!l
0679
MC/DC50%1/2
defined $args->{"text"} and not defined $c->stash("format")
defined $args->{"text"} not defined $c->stash("format")
MC/DC0%0/1
$args->{"format"} //= "txt"
$args->{"format"}
Truth table66%2/3
A and not(B)
A= defined $args->{"text"}B= not defined $c->stash("format")
ABresult
0X0
100
111
Truth table50%1/2
A
A= $args->{"format"}
Aresult
11
00
2997  
2998 # AI availability flag used by dashboard and insights templates.
2999 2186 ▶ $c->stash(ai_available => (length($c->app->config('ai_api_key') // '') ? 1 : 0))
Branch: 2 branches
BranchTrueFalseTotal
unless defined $c->stash("ai_available") 2186 0 2186
length($c->app->config('ai_api_key') // '') ? : 2 2184 2186
Condition100%2/2
$c->app->config("ai_api_key") // ""
l!l
22184
MC/DC100%1/1
$c->app->config("ai_api_key") // ""
$c->app->config("ai_api_key")
Truth table100%2/2
A
A= $c->app->config("ai_api_key")
Aresult
11
00
3000 unless defined $c->stash('ai_available');
3001  
3002 # The chart pages and canvases read this from the stash. Resolving it
3003 # per request rather than once at startup means a deployment (and a
3004 # test) can point D3 at a local copy by changing the config.
3005 2186 my $d3_url = $c->app->config('d3_url');
3006 2186 ▶ $c->stash(d3_url =>
Branch: 1 branch
BranchTrueFalseTotal
defined $d3_url && !ref($d3_url) && length $d3_url ? : 2179 7 2186
Condition66%4/6
defined $d3_url and not ref $d3_url and length $d3_url
!ll&&!rl&&r
702179
defined $d3_url and not ref $d3_url
!ll&&!rl&&r
702179
MC/DC33%1/3
defined $d3_url and not ref $d3_url and length $d3_url
defined $d3_url not ref $d3_url length $d3_url
Truth table50%2/4
A and not(B) and C
A= defined $d3_urlB= not ref $d3_urlC= length $d3_url
ABCresult
0XX0
10X0
1100
1111
3007 (defined $d3_url && !ref $d3_url && length $d3_url)
3008 ? $d3_url : $DEFAULT_D3_URL);
3009  
3010 # Time range filters on a date or time column, so the table page
3011 # offers it only when the rows drawn have one (seven actions render
3012 # that page; this is the one place they all pass). A range already
3013 # in force keeps its column, so it can be changed or removed even
3014 # when it leaves no rows to judge by.
3015 2186 ▶ if (($args->{template} // '') =~ m{/dashboard\z}) {
Branch: 1 branch
BranchTrueFalseTotal
if (($args->{"template"} // "") =~ m[/dashboard\z]u) 202 1984 2186
Condition100%2/2
$args->{"template"} // ""
l!l
9551231
MC/DC100%1/1
$args->{"template"} // ""
$args->{"template"}
Truth table100%2/2
A
A= $args->{"template"}
Aresult
11
00
3016 202 require Database::BI::Controller::Dashboard;
3017 202 ▶ my $columns = $args->{columns} // $c->stash('columns');
Condition33%1/3
$args->{"columns"} // $c->stash("columns")
l!l&&r!l&&!r
20200
MC/DC0%0/2
$args->{"columns"} // $c->stash("columns")
$args->{"columns"} $c->stash("columns")
Truth table33%1/3
A // B
A= $args->{"columns"}B= $c->stash("columns")
ABresult
1X1
011
000
3018 202 ▶ my $records = $args->{records} // $c->stash('records');
Condition33%1/3
$args->{"records"} // $c->stash("records")
l!l&&r!l&&!r
20200
MC/DC0%0/2
$args->{"records"} // $c->stash("records")
$args->{"records"} $c->stash("records")
Truth table33%1/3
A // B
A= $args->{"records"}B= $c->stash("records")
ABresult
1X1
011
000
3019 202 my @time = @{ Database::BI::Controller::Dashboard::_time_columns($columns, $records) };
3020 202 my $active = $c->stash('time_range');
3021 push @time, $active->{col}
3022 0 ▶ if ref($active) eq 'HASH' && !grep { $_ eq $active->{col} } @time;
Branch: 1 branch
BranchTrueFalseTotal
if ref $active eq "HASH" and not grep({$_ eq $active->{"col"};} @time) 0 202 202
Condition33%1/3
ref $active eq "HASH" and not grep({$_ eq $active->{"col"};} @time)
!ll&&!rl&&r
20200
MC/DC0%0/2
ref $active eq "HASH" and not grep({$_ eq $active->{"col"};} @time)
ref $active eq "HASH" not grep({$_ eq $active->{"col"};} @time)
Truth table33%1/3
A and not(B)
A= ref $active eq "HASH"B= not grep({$_ eq $active->{"col"};} @time)
ABresult
0X0
100
111
3023 202 $args->{time_columns} = \@time;
3024 }
3025  
3026 # Mojolicious's own 404 page has no navigation and no breadcrumb; an
3027 # HTML one is drawn from our not_found template instead. Its first
3028 # try is "not_found.<mode>", so that is the one replaced. JSON
3029 # replies and exceptions keep the defaults.
3030 2186 ▶ if (($args->{template} // '') =~ /\Anot_found\.\w+\z/
Branch: 1 branch
BranchTrueFalseTotal
if (($args->{"template"} // "") =~ /\Anot_found\.\w+\z/u and (($args->{"format"} // $c->stash("format")) // "html") eq "html") 118 2068 2186
Condition60%6/10
($args->{"template"} // "") =~ /\Anot_found\.\w+\z/u and (($args->{"format"} // $c->stash("format")) // "html") eq "html"
!ll&&!rl&&r
20680118
$args->{"template"} // ""
l!l
9551231
($args->{"format"} // $c->stash("format")) // "html"
l!l
1180
$args->{"format"} // $c->stash("format")
l!l&&r!l&&!r
11800
MC/DC33%1/3
($args->{"template"} // "") =~ /\Anot_found\.\w+\z/u and (($args->{"format"} // $c->stash("format")) // "html") eq "html"
$args->{"template"} $args->{"format"} $c->stash("format")
Truth table50%2/4
A and B // C
A= $args->{"template"}B= $args->{"format"}C= $c->stash("format")
ABCresult
0XX0
1000
11X1
1011
3031 && ($args->{format} // $c->stash('format') // 'html') eq 'html') {
3032 118 $args->{template} = $page_template->('not_found');
3033 118 $args->{handler} = 'tt';
3034 118 $c->stash(title => 'Page not found');
3035 }
3036  
3037 # The breadcrumb trail (layouts/breadcrumb.html.tt), worked out from the
3038 # request's own URL, so every page has one and a new page gets one
3039 # without code of its own.
3040 2186 ▶ if (!defined $c->stash('trail')) {
Branch: 1 branch
BranchTrueFalseTotal
unless (defined $c->stash("trail")) 2186 0 2186
3041 2186 require Database::BI::Controller::Dashboard;
3042 2186 $c->stash(trail => Database::BI::Controller::Dashboard::_breadcrumb_trail($c));
3043 }
3044  
3045 # The nav's Preferences link names this page with back=, so the
3046 # preferences page's trail leads back to it (Dashboard::_crumb_parent).
3047 2186 ▶ if (!defined $c->stash('preferences_url')) {
Branch: 1 branch
BranchTrueFalseTotal
unless (defined $c->stash("preferences_url")) 2186 0 2186
3048 2186 require Database::BI::Controller::Dashboard;
3049 2186 $c->stash(preferences_url => Database::BI::Controller::Dashboard::_preferences_url($c));
3050 }
3051  
3052 2186 ▶ return if defined $c->stash('tutorial_chapter');
Branch: 1 branch
BranchTrueFalseTotal
if defined $c->stash("tutorial_chapter") 0 2186 2186
3053  
3054 2186 my $chapter = Database::BI::Tutorial->chapter_for_action($c->stash('action'));
3055 2186 ▶ return unless $chapter;
Branch: 1 branch
BranchTrueFalseTotal
unless $chapter 776 1410 2186
3056  
3057 $c->stash(
3058 tutorial_chapter => $chapter->{id},
3059 tutorial_chapter_title => $chapter->{title},
3060 1410 );
3061 78 });
3062  
3063 # Factory helper: opens any named table from the configured data directory.
3064 # The directory=> option overrides the default so open_file and upload_file
3065 # can open tables from arbitrary filesystem paths.
3066 #
3067 # Joins are handled by Database::Join in the controller pipeline;
3068 # open_table returns a single DataSource per table.
3069 #
3070 # Resolved with the rule the controller's _data_dir_for applies: a relative
3071 # value is relative to the application home, an absolute one is used as
3072 # given. Mojo::File->child appends an absolute path instead of replacing
3073 # the base, so home->child($absolute) named a directory that cannot exist,
3074 # and /view - which opens its table without a directory of its own - read
3075 # from it and served an empty page.
3076 78 ▶ my $configured = $self->config->{data_dir} // 'data';
Condition50%1/2
$self->config->{"data_dir"} // "data"
l!l
780
MC/DC0%0/1
$self->config->{"data_dir"} // "data"
$self->config->{"data_dir"}
Truth table50%1/2
A
A= $self->config->{"data_dir"}
Aresult
11
00
3077 78 my $data_path = Mojo::File->new($configured);
3078 78 ▶ $data_path = $self->home->child($configured) unless $data_path->is_abs;
Branch: 1 branch
BranchTrueFalseTotal
unless $data_path->is_abs 77 1 78
3079 78 my $data_dir = $data_path->to_string;
3080  
3081 # In-process CHI cache: shared across all requests in the same worker process.
3082 # The Memory driver needs no external services and works out of the box.
3083 # Set global => 1 so the same namespace is reused across multiple open_table
3084 # calls within one process rather than creating separate isolated caches.
3085 78 ▶ my $cache_conf = $self->config->{cache} // {};
Condition50%1/2
$self->config->{"cache"} // {}
l!l
078
MC/DC0%0/1
$self->config->{"cache"} // {}
$self->config->{"cache"}
Truth table50%1/2
A
A= $self->config->{"cache"}
Aresult
11
00
3086 78 ▶ my $cache_ttl_url = $cache_conf->{ttl_url} // $DEFAULT_CACHE_TTL_URL;
Condition66%2/3
$cache_conf->{"ttl_url"} // $DEFAULT_CACHE_TTL_URL
l!l&&r!l&&!r
0735
MC/DC50%1/2
$cache_conf->{"ttl_url"} // $DEFAULT_CACHE_TTL_URL
$cache_conf->{"ttl_url"} $DEFAULT_CACHE_TTL_URL
Truth table66%2/3
A // B
A= $cache_conf->{"ttl_url"}B= $DEFAULT_CACHE_TTL_URL
ABresult
1X1
011
000
3087 my $chi = CHI->new(
3088 78 ▶ driver => $cache_conf->{driver} // 'Memory',
Condition50%1/2
$cache_conf->{"driver"} // "Memory"
l!l
078
MC/DC0%0/1
$cache_conf->{"driver"} // "Memory"
$cache_conf->{"driver"}
Truth table50%1/2
A
A= $cache_conf->{"driver"}
Aresult
11
00
3089 global => 1,
3090 namespace => 'Database::BI',
3091 _table_cache_root($self, $cache_conf),
3092 );
3093  
3094 1236 ▶ $self->helper(open_table => sub($c, $table, %opts) {
Subroutine __ANON__: called CC=12 SCAR=26.9
3095 # URL mode: fetch a remote HTML table directly via Database::Abstraction's
3096 # URL backend (LWP::UserAgent + HTML::TableExtract).
3097 1236 ▶ if (exists $opts{url}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $opts{"url"}) 2 1234 1236
3098 return Database::BI::Model::DataSource->new(
3099 url => $opts{url},
3100 cache => $chi,
3101 cache_ttl_url => $cache_ttl_url,
3102 private_hosts => $c->app->config('url_private_hosts') // [],
3103 exists $opts{html_table_index}
3104 ? (html_table_index => $opts{html_table_index})
3105 2 ▶ : (),
Branch: 1 branch
BranchTrueFalseTotal
exists $opts{'html_table_index'} ? : 2 0 2
Condition50%1/2
$c->app->config("url_private_hosts") // []
l!l
02
MC/DC0%0/1
$c->app->config("url_private_hosts") // []
$c->app->config("url_private_hosts")
Truth table50%1/2
A
A= $c->app->config("url_private_hosts")
Aresult
11
00
3106 );
3107 }
3108 # Redis mode: connect to a Redis server via Database::Abstraction's
3109 # Redis backend (Redis::Fast or Redis).
3110 1234 ▶ if (exists $opts{redis}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $opts{"redis"}) 0 1234 1234
3111 return Database::BI::Model::DataSource->new(
3112 table => $table,
3113 redis => $opts{redis},
3114 0 cache => $chi,
3115 cache_ttl_url => $cache_ttl_url,
3116 );
3117 }
3118 # MariaDB mode: connect to an entry of the mariadb_servers configuration.
3119 1234 ▶ if (exists $opts{mariadb}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $opts{"mariadb"}) 0 1234 1234
3120 return Database::BI::Model::DataSource->new(
3121 table => $table,
3122 server => $opts{mariadb},
3123 mariadb => ($c->app->config('mariadb_servers') // {})->{ $opts{mariadb} },
3124 0 ▶ );
Condition0%0/2
$c->app->config("mariadb_servers") // {}
l!l
00
MC/DC0%0/1
$c->app->config("mariadb_servers") // {}
$c->app->config("mariadb_servers")
Truth table0%0/2
A
A= $c->app->config("mariadb_servers")
Aresult
11
00
3125 }
3126 1234 ▶ my $dir = exists $opts{directory} ? $opts{directory} : $data_dir;
Branch: 1 branch
BranchTrueFalseTotal
exists $opts{'directory'} ? : 1154 80 1234
3127 Database::BI::Model::DataSource->new(
3128 directory => $dir,
3129 table => $table,
3130 cache => $chi,
3131 cache_ttl_url => $cache_ttl_url,
3132 exists $opts{host} ? (host => $opts{host}) : (),
3133 exists $opts{file_ext} ? (file_ext => $opts{file_ext}) : (),
3134 exists $opts{internal_table} ? (internal_table => $opts{internal_table}) : (),
3135 1234 ▶ exists $opts{column_order} ? (column_order => $opts{column_order}) : (),
Branch: 4 branches
BranchTrueFalseTotal
exists $opts{'host'} ? : 6 1228 1234
exists $opts{'file_ext'} ? : 6 1228 1234
exists $opts{'internal_table'} ? : 1 1233 1234
exists $opts{'column_order'} ? : 1 1233 1234
3136 );
3137 78 });
3138  
3139 78 my $r = $self->routes();
3140  
3141 # Reached without logging in: the login itself, and the share and embed
3142 # links, whose signed, expiring token is their own permission. Everything
3143 # else goes through require_login, which lets every request in when no
3144 # password_hash is set.
3145 78 $r->get('/login') ->to('Dashboard#login_form');
3146 78 $r->post('/login') ->to('Dashboard#login');
3147 78 $r->post('/logout') ->to('Dashboard#logout');
3148 78 $r->get('/share/:id')->to('Dashboard#share_view');
3149 78 $r->get('/embed/:id')->to('Dashboard#embed_view');
3150 78 $r = $r->under('/')->to('Dashboard#require_login');
3151  
3152 78 $r->get('/')->to('Dashboard#index');
3153 78 $r->get('/tutorial')->to('Dashboard#tutorial');
3154 78 $r->get('/preferences')->to('Dashboard#preferences');
3155 78 $r->get('/view/:table')->to('Dashboard#view');
3156 78 $r->get('/browse')->to('Dashboard#browse');
3157 78 $r->get('/open')->to('Dashboard#open_file');
3158 78 $r->get('/join')->to('Dashboard#join_tables');
3159 78 $r->get('/api/columns')->to('Dashboard#columns_api');
3160 78 $r->get('/api/range')->to('Dashboard#range_api');
3161 78 $r->get('/import')->to('Dashboard#import_url');
3162 78 $r->get('/combine')->to('Dashboard#combine_tables');
3163 78 $r->get('/export')->to('Dashboard#export_data');
3164 78 $r->post('/export')->to('Dashboard#export_write');
3165 78 $r->get('/api/dirs')->to('Dashboard#dirs_api');
3166 78 $r->get('/api/stat')->to('Dashboard#stat_api');
3167 78 $r->post('/upload')->to('Dashboard#upload_file');
3168 78 $r->post('/uploads/clear')->to('Dashboard#clear_uploads');
3169 78 $r->get('/graph')->to('Dashboard#graph_view');
3170 78 $r->get('/pie')->to('Dashboard#pie_view');
3171 78 $r->get('/heatmap')->to('Dashboard#heatmap_view');
3172 78 $r->get('/bar')->to('Dashboard#bar_view');
3173 78 $r->get('/folder')->to('Dashboard#folder_view');
3174 78 $r->get('/tables')->to('Dashboard#tables_view');
3175 78 $r->get('/pivot')->to('Dashboard#pivot_view');
3176 78 $r->get('/stats')->to('Dashboard#stats_view');
3177 78 $r->post('/calc/save') ->to('Dashboard#calc_save');
3178 78 $r->post('/calc/delete') ->to('Dashboard#calc_delete');
3179 78 $r->get('/calc/list') ->to('Dashboard#calc_list');
3180 78 $r->post('/groups/save') ->to('Dashboard#groups_save');
3181 78 $r->post('/groups/delete')->to('Dashboard#groups_delete');
3182 78 $r->get('/groups/list') ->to('Dashboard#groups_list');
3183 78 $r->post('/dates/save') ->to('Dashboard#dates_save');
3184 78 $r->post('/dates/delete') ->to('Dashboard#dates_delete');
3185 78 $r->get('/dates/list') ->to('Dashboard#dates_list');
3186 78 $r->post('/lookups/save') ->to('Dashboard#lookups_save');
3187 78 $r->post('/lookups/delete')->to('Dashboard#lookups_delete');
3188 78 $r->get('/lookups/list') ->to('Dashboard#lookups_list');
3189 78 $r->post('/extract/save') ->to('Dashboard#extract_save');
3190 78 $r->post('/extract/delete')->to('Dashboard#extract_delete');
3191 78 $r->get('/extract/list') ->to('Dashboard#extract_list');
3192 78 $r->post('/geo/save') ->to('Dashboard#geo_save');
3193 78 $r->post('/geo/delete') ->to('Dashboard#geo_delete');
3194 78 $r->post('/geo/lookup') ->to('Dashboard#geo_lookup');
3195 78 $r->post('/geo/boundary') ->to('Dashboard#geo_boundary');
3196 78 $r->get('/geo/list') ->to('Dashboard#geo_list');
3197 78 $r->get('/scatter') ->to('Dashboard#scatter_view');
3198 78 $r->get('/stacked') ->to('Dashboard#stacked_view');
3199 78 $r->get('/combo') ->to('Dashboard#combo_view');
3200 78 $r->get('/treemap') ->to('Dashboard#treemap_view');
3201 78 $r->get('/histogram') ->to('Dashboard#histogram_view');
3202 78 $r->get('/wordcloud') ->to('Dashboard#wordcloud_view');
3203 78 $r->get('/map') ->to('Dashboard#map_view');
3204 78 $r->get('/line') ->to('Dashboard#graph_view'); # alias for /graph
3205 78 $r->post('/api/ask') ->to('Dashboard#ask_api');
3206 78 $r->get('/insights') ->to('Dashboard#insights_view');
3207 78 $r->post('/api/summary') ->to('Dashboard#summary_api');
3208  
3209 # Saved analyses
3210 # /api/analyses/import must be registered BEFORE /api/analyses/:id so that the
3211 # literal segment "import" is matched before the :id placeholder captures it.
3212 78 $r->get('/a/:id') ->to('Dashboard#canvas_render');
3213 78 $r->get('/api/analyses') ->to('Dashboard#analyses_list');
3214 78 $r->post('/api/analyses') ->to('Dashboard#analyses_create');
3215 78 $r->post('/api/analyses/import') ->to('Dashboard#analyses_import');
3216 78 $r->get('/api/analyses/:id') ->to('Dashboard#analyses_get');
3217 78 $r->put('/api/analyses/:id') ->to('Dashboard#analyses_update');
3218 78 $r->patch('/api/analyses/:id') ->to('Dashboard#analyses_patch'); $r->delete('/api/analyses/:id') ->to('Dashboard#analyses_delete');
3219 78 $r->post('/api/analyses/:id/visuals')->to('Dashboard#analyses_add_visual');
3220 78 $r->patch('/api/analyses/:id/visuals/:visual')->to('Dashboard#analyses_update_visual');
3221 78 $r->delete('/api/analyses/:id/visuals/:visual')->to('Dashboard#analyses_delete_visual');
3222 78 $r->get('/api/analyses/:id/export') ->to('Dashboard#analyses_export');
3223  
3224 # extracts, catalog, sharing, embedding, per-visual downloads
3225 78 $r->get('/canvases') ->to('Dashboard#canvases_view');
3226 78 $r->get('/catalog') ->to('Dashboard#catalog_view');
3227 78 $r->get('/a/:id/visual/:n/:fmt') ->to('Dashboard#visual_download');
3228 78 $r->post('/api/analyses/:id/share') ->to('Dashboard#analyses_share');
3229  
3230 # Evict stale upload subdirectories on every startup so the cache cannot
3231 # grow unboundedly across server restarts. Only entries whose mtime is
3232 # older than $UPLOAD_MAX_AGE_S seconds are removed; recent uploads that
3233 # were made while the previous server process was running are preserved.
3234 78 _evict_old_uploads($self->_uploads_dir, $UPLOAD_MAX_AGE_S);
3235 }
3236  
3237 # _uploads_dir() -> Mojo::File
3238 #
3239 # Purpose: The one place that decides where POST /upload stages its files.
3240 # Entry: $self->config->{uploads_dir} may be unset (the default), a path
3241 # relative to the application home, or an absolute path.
3242 # Exit: A Mojo::File; the directory may not exist yet.
3243 #
3244 # upload_file, clear_uploads and the startup eviction all resolve the
3245 # directory through here, so a configured staging directory is written to and
3246 # purged as one. Resolving it per call rather than once in startup() is what
3247 # lets a caller - a test in particular - point one application instance at its
3248 # own directory without touching the shared <home>/.uploads that another
3249 # process may be clearing at the same moment.
3250 103 ▶sub _uploads_dir ($self) {
Subroutine _uploads_dir: called CC=5 SCAR=16.1
3251 103 my $dir = $self->config->{uploads_dir};
3252 103 ▶ return $self->home->child('.uploads')
Branch: 1 branch
BranchTrueFalseTotal
unless defined $dir and not ref $dir and length $dir 83 20 103
Condition100%6/6
defined $dir and not ref $dir and length $dir
!ll&&!rl&&r
82120
defined $dir and not ref $dir
!ll&&!rl&&r
80221
MC/DC100%3/3
defined $dir and not ref $dir and length $dir
defined $dir not ref $dir length $dir
Truth table100%4/4
A and not(B) and C
A= defined $dirB= not ref $dirC= length $dir
ABCresult
0XX0
10X0
1100
1111
3253 unless defined $dir && !ref $dir && length $dir;
3254  
3255 20 my $path = Mojo::File->new($dir);
3256 20 ▶ return $path->is_abs ? $path : $self->home->child($dir);
Branch: 1 branch
BranchTrueFalseTotal
$path->is_abs ? : 19 1 20
3257 }
3258  
3259 # _evict_old_uploads( $uploads_dir, $max_age_s ) -> void
3260 #
3261 # Purpose: Remove upload subdirectory trees whose mtime is older than $max_age_s
3262 # seconds. Runs at startup and is also called by clear_uploads (via the
3263 # Dashboard controller) when the user explicitly requests a purge.
3264 # Entry: $uploads_dir is a Mojo::File. $max_age_s is a positive integer.
3265 # Exit: Silent on success. Individual remove_tree failures are non-fatal.
3266 82 ▶sub _evict_old_uploads {
Subroutine _evict_old_uploads: called CC=2 SCAR=7.0
3267 82 my ($uploads_dir, $max_age_s) = @_;
3268 82 ▶ return unless -d $uploads_dir;
Branch: 1 branch
BranchTrueFalseTotal
unless -d $uploads_dir 77 5 82
3269  
3270 5 my $cutoff = time() - $max_age_s;
3271  
3272 9 ▶ $uploads_dir->list({ dir => 1 })->each(sub {
Subroutine __ANON__: called CC=5 SCAR=16.2
3273 9 my ($entry) = @_;
3274 9 ▶ my $mtime = (stat $entry)[9] // 0;
Condition50%1/2
(stat $entry)[9] // 0
l!l
90
MC/DC0%0/1
(stat $entry)[9] // 0
(stat $entry)[9]
Truth table50%1/2
A
A= (stat $entry)[9]
Aresult
11
00
3275 9 ▶ return if $mtime >= $cutoff; # still fresh -- keep it
Branch: 1 branch
BranchTrueFalseTotal
if $mtime >= $cutoff 5 4 9
3276 4 ▶ if (-d $entry) {
Branch: 2 branches
BranchTrueFalseTotal
if (-d $entry) { } 3 1 4
elsif (-f $entry) { } 1 0 1
3277 3 $entry->remove_tree;
3278 } elsif (-f $entry) {
3279 1 unlink $entry->to_string;
3280 }
3281 5 });
3282 }
3283  
3284 # _cross_origin_opener_policy( \%config ) -> $value | undef
3285 #
3286 # Purpose: The Cross-Origin-Opener-Policy header a page carries.
3287 # Entry: The config. cross_origin_opener_policy is one of %COOP_VALUES
3288 # ('' or 0 sends none); without it, $DEFAULT_COOP.
3289 # Exit: The value, or undef for none. One changed after startup to a value
3290 # not in the list is not sent.
3291 955 ▶sub _cross_origin_opener_policy {
Subroutine _cross_origin_opener_policy: called CC=7 SCAR=32.8
3292 955 my ($config) = @_;
3293 955 ▶ my $value = ($config // {})->{cross_origin_opener_policy};
Condition50%1/2
$config // {}
l!l
9550
MC/DC0%0/1
$config // {}
$config
Truth table50%1/2
A
A= $config
Aresult
11
00
3294 955 ▶ return $DEFAULT_COOP unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 955 0 955
3295 0 ▶ return if ref $value || !length($value) || $value eq '0';
Branch: 1 branch
BranchTrueFalseTotal
if ref $value or not length $value or $value eq 0 0 0 0
Condition0%0/6
ref $value or not length $value or $value eq 0
l!l&&r!l&&!r
000
ref $value or not length $value
l!l&&r!l&&!r
000
MC/DC0%0/3
ref $value or not length $value or $value eq 0
ref $value not length $value $value eq 0
Truth table0%0/4
A or not(B) or C
A= ref $valueB= not length $valueC= $value eq 0
ABCresult
1XX1
01X1
0011
0000
3296 0 ▶ return $COOP_VALUES{$value} ? $value : undef;
Branch: 1 branch
BranchTrueFalseTotal
$COOP_VALUES{$value} ? : 0 0 0
3297 }
3298  
3299 # _check_cross_origin_opener_policy( $value ) -> void
3300 #
3301 # Purpose: Refuse a cross_origin_opener_policy a browser would not read.
3302 # Entry: The config value.
3303 # Exit: Returns; croaks on anything but undef, '', 0 or a known value.
3304 80 ▶sub _check_cross_origin_opener_policy {
Subroutine _check_cross_origin_opener_policy: called CC=7 SCAR=34.4
3305 80 my ($value) = @_;
3306 80 ▶ return if !defined $value || (!ref $value && (!length($value) || $value eq '0' || $COOP_VALUES{$value}));
Branch: 1 branch
BranchTrueFalseTotal
if not defined $value or not ref $value and !length($value) || $value eq 0 || $COOP_VALUES{$value} 80 0 80
Condition8%1/12
not defined $value or not ref $value and !length($value) || $value eq 0 || $COOP_VALUES{$value}
l!l&&r!l&&!r
8000
not ref $value and !length($value) || $value eq 0 || $COOP_VALUES{$value}
!ll&&!rl&&r
000
not length $value or $value eq 0 or $COOP_VALUES{$value}
l!l&&r!l&&!r
000
not length $value or $value eq 0
l!l&&r!l&&!r
000
MC/DC0%0/5
not defined $value or not ref $value and !length($value) || $value eq 0 || $COOP_VALUES{$value}
not defined $value not ref $value not length $value $value eq 0 $COOP_VALUES{$value}
Truth table16%1/6
not(A) or not(B) and not(C) or D or E
A= not defined $valueB= not ref $valueC= not length $valueD= $value eq 0E= $COOP_VALUES{$value}
ABCDEresult
1XXXX1
011XX1
0101X1
010011
00XXX0
010000
3307 0 ▶ croak sprintf("cross_origin_opener_policy: '%s' is not one of %s, or 0 for none",
Branch: 1 branch
BranchTrueFalseTotal
ref $value ? : 0 0 0
3308 ref $value ? ref $value : $value, join(', ', sort keys %COOP_VALUES));
3309 }
3310  
3311 # _permissions_policy( \%config ) -> $value | undef
3312 #
3313 # Purpose: The Permissions-Policy header every reply carries.
3314 # Entry: The config. permissions_policy is the value to send, as written
3315 # ('' or 0 sends none); without it, $DEFAULT_PERMISSIONS_POLICY.
3316 # Exit: The value, or undef for none. One changed after startup to text
3317 # that is not printable ASCII is not sent (_check_permissions_policy
3318 # refuses it at startup), so it cannot carry a line break.
3319 2191 ▶sub _permissions_policy {
Subroutine _permissions_policy: called CC=7 SCAR=32.8
3320 2191 my ($config) = @_;
3321 2191 ▶ my $value = ($config // {})->{permissions_policy};
Condition50%1/2
$config // {}
l!l
21910
MC/DC0%0/1
$config // {}
$config
Truth table50%1/2
A
A= $config
Aresult
11
00
3322 2191 ▶ return $DEFAULT_PERMISSIONS_POLICY unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 2191 0 2191
3323 0 ▶ return if ref $value || !length($value) || $value eq '0';
Branch: 1 branch
BranchTrueFalseTotal
if ref $value or not length $value or $value eq 0 0 0 0
Condition0%0/6
ref $value or not length $value or $value eq 0
l!l&&r!l&&!r
000
ref $value or not length $value
l!l&&r!l&&!r
000
MC/DC0%0/3
ref $value or not length $value or $value eq 0
ref $value not length $value $value eq 0
Truth table0%0/4
A or not(B) or C
A= ref $valueB= not length $valueC= $value eq 0
ABCresult
1XX1
01X1
0011
0000
3324 0 ▶ return $value =~ /\A[\x20-\x7e]+\z/ ? $value : undef;
Branch: 1 branch
BranchTrueFalseTotal
$value =~ /\A[\x20-\x7e]+\z/u ? : 0 0 0
3325 }
3326  
3327 # _check_permissions_policy( $value ) -> void
3328 #
3329 # Purpose: Refuse a permissions_policy that cannot be a header value.
3330 # Entry: The config value.
3331 # Exit: Returns; croaks on a reference or on text that is not printable
3332 # ASCII. The features themselves are the browser's to judge.
3333 80 ▶sub _check_permissions_policy {
Subroutine _check_permissions_policy: called CC=4 SCAR=20.9
3334 80 my ($value) = @_;
3335 80 ▶ return if !defined $value || (!ref $value && $value =~ /\A(?:[\x20-\x7e]*)\z/);
Branch: 1 branch
BranchTrueFalseTotal
if not defined $value or not ref $value and $value =~ /\A(?:[\x20-\x7e]*)\z/u 80 0 80
Condition16%1/6
not defined $value or not ref $value and $value =~ /\A(?:[\x20-\x7e]*)\z/u
l!l&&r!l&&!r
8000
not ref $value and $value =~ /\A(?:[\x20-\x7e]*)\z/u
!ll&&!rl&&r
000
MC/DC0%0/3
not defined $value or not ref $value and $value =~ /\A(?:[\x20-\x7e]*)\z/u
not defined $value not ref $value $value =~ /\A(?:[\x20-\x7e]*)\z/u
Truth table25%1/4
not(A) or not(B) and C
A= not defined $valueB= not ref $valueC= $value =~ /\A(?:[\x20-\x7e]*)\z/u
ABCresult
1XX1
0111
00X0
0100
3336 0 croak 'permissions_policy must be a header value, such as "camera=(), geolocation=()", or 0 for none';
3337 }
3338  
3339 # _strict_transport_security( $c ) -> $value | undef
3340 #
3341 # Purpose: The Strict-Transport-Security header for this reply, if any.
3342 # Entry: The controller. Config strict_transport_security is the value to
3343 # send ('' or 0 sends none); without it, $DEFAULT_HSTS.
3344 # Exit: The value, or undef when none is to be sent.
3345 #
3346 # Only a request that came over https gets it: a browser ignores the header
3347 # over http, and a reply that came through a proxy is https only when
3348 # Mojolicious is told to trust the proxy (MOJO_REVERSE_PROXY=1, hypnotoad's
3349 # proxy => 1) and the proxy says X-Forwarded-Proto: https. Never for
3350 # localhost or an IP address: the header binds the host, not the port, so
3351 # from https://localhost:8443 it would send every other server on that
3352 # machine, morbo included, to https; and RFC 6797 has browsers ignore it for
3353 # an IP address. A value the configuration changed to after startup is sent
3354 # only if it is still well formed.
3355 2191 ▶sub _strict_transport_security {
Subroutine _strict_transport_security: called CC=14 SCAR=51.4
3356 2191 my ($c) = @_;
3357 2191 ▶ return unless $c->req->is_secure;
Branch: 1 branch
BranchTrueFalseTotal
unless $c->req->is_secure 2191 0 2191
3358  
3359 0 ▶ my $host = lc($c->req->url->to_abs->host // '');
Condition0%0/2
$c->req->url->to_abs->host // ""
l!l
00
MC/DC0%0/1
$c->req->url->to_abs->host // ""
$c->req->url->to_abs->host
Truth table0%0/2
A
A= $c->req->url->to_abs->host
Aresult
11
00
3360 0 ▶ return if $host eq '' || $host eq 'localhost' || $host =~ /\.localhost\z/
Branch: 1 branch
BranchTrueFalseTotal
if $host eq "" or $host eq "localhost" or $host =~ /\.localhost\z/u or $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/u or $host =~ /:/u or $host =~ /\A\[/u 0 0 0
Condition0%0/15
$host eq "" or $host eq "localhost" or $host =~ /\.localhost\z/u or $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/u or $host =~ /:/u or $host =~ /\A\[/u
l!l&&r!l&&!r
000
$host eq "" or $host eq "localhost" or $host =~ /\.localhost\z/u or $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/u or $host =~ /:/u
l!l&&r!l&&!r
000
$host eq "" or $host eq "localhost" or $host =~ /\.localhost\z/u or $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/u
l!l&&r!l&&!r
000
$host eq "" or $host eq "localhost" or $host =~ /\.localhost\z/u
l!l&&r!l&&!r
000
$host eq "" or $host eq "localhost"
l!l&&r!l&&!r
000
MC/DC0%0/6
$host eq "" or $host eq "localhost" or $host =~ /\.localhost\z/u or $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/u or $host =~ /:/u or $host =~ /\A\[/u
$host eq "" $host eq "localhost" $host =~ /\.localhost\z/u $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/u $host =~ /:/u $host =~ /\A\[/u
Truth table0%0/7
A or B or C or D or E or F
A= $host eq ""B= $host eq "localhost"C= $host =~ /\.localhost\z/uD= $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/uE= $host =~ /:/uF= $host =~ /\A\[/u
ABCDEFresult
1XXXXX1
01XXXX1
001XXX1
0001XX1
00001X1
0000011
0000000
3361 || $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/ || $host =~ /:/ || $host =~ /\A\[/;
3362  
3363 0 my $value = $c->app->config('strict_transport_security');
3364 0 ▶ return $DEFAULT_HSTS unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 0 0 0
3365 0 ▶ return if ref $value || !length($value) || $value eq '0';
Branch: 1 branch
BranchTrueFalseTotal
if ref $value or not length $value or $value eq 0 0 0 0
Condition0%0/6
ref $value or not length $value or $value eq 0
l!l&&r!l&&!r
000
ref $value or not length $value
l!l&&r!l&&!r
000
MC/DC0%0/3
ref $value or not length $value or $value eq 0
ref $value not length $value $value eq 0
Truth table0%0/4
A or not(B) or C
A= ref $valueB= not length $valueC= $value eq 0
ABCresult
1XX1
01X1
0011
0000
3366 0 ▶ return $value =~ $HSTS_RE ? $value : undef;
Branch: 1 branch
BranchTrueFalseTotal
$value =~ /$HSTS_RE/u ? : 0 0 0
3367 }
3368  
3369 # _check_strict_transport_security( $value ) -> void
3370 #
3371 # Purpose: Refuse a strict_transport_security that a browser would not read.
3372 # Entry: The config value: undef, '' or 0 (none), or a header value.
3373 # Exit: Returns; croaks on anything else.
3374 80 ▶sub _check_strict_transport_security {
Subroutine _check_strict_transport_security: called CC=8 SCAR=37.6
3375 80 my ($value) = @_;
3376 80 ▶ return if !defined $value || (!ref $value && (!length($value) || $value eq '0'));
Branch: 1 branch
BranchTrueFalseTotal
if not defined $value or not ref $value and !length($value) || $value eq 0 80 0 80
Condition11%1/9
not defined $value or not ref $value and !length($value) || $value eq 0
l!l&&r!l&&!r
8000
not ref $value and !length($value) || $value eq 0
!ll&&!rl&&r
000
not length $value or $value eq 0
l!l&&r!l&&!r
000
MC/DC0%0/4
not defined $value or not ref $value and !length($value) || $value eq 0
not defined $value not ref $value not length $value $value eq 0
Truth table20%1/5
not(A) or not(B) and not(C) or D
A= not defined $valueB= not ref $valueC= not length $valueD= $value eq 0
ABCDresult
1XXX1
011X1
01011
00XX0
01000
3377 0 ▶ return if !ref $value && $value =~ $HSTS_RE;
Branch: 1 branch
BranchTrueFalseTotal
if not ref $value and $value =~ /$HSTS_RE/u 0 0 0
Condition0%0/3
not ref $value and $value =~ /$HSTS_RE/u
!ll&&!rl&&r
000
MC/DC0%0/2
not ref $value and $value =~ /$HSTS_RE/u
not ref $value $value =~ /$HSTS_RE/u
Truth table0%0/3
not(A) and B
A= not ref $valueB= $value =~ /$HSTS_RE/u
ABresult
0X0
100
111
3378 0 ▶ croak sprintf("strict_transport_security: '%s' is not a header value such as 'max-age=31536000'"
Branch: 1 branch
BranchTrueFalseTotal
ref $value ? : 0 0 0
3379 . ' (optionally with includeSubDomains and preload), or 0 for none', ref $value ? ref $value : $value);
3380 }
3381  
3382 # _require_export_dirs_with_password( $hash, $export_dirs ) -> void
3383 #
3384 # Purpose: A server with a login is one other people use, so their exports
3385 # must be kept to folders of their own. Anywhere else, an export can
3386 # replace a .json or .csv another program obeys (a project's
3387 # package.json names commands npm runs) as the account running the
3388 # server.
3389 # Entry: Config password_hash and export_dirs (already checked).
3390 # Exit: Returns; croaks when there is a password and no export_dirs.
3391 80 ▶sub _require_export_dirs_with_password {
Subroutine _require_export_dirs_with_password: called CC=6 SCAR=29.7
3392 80 my ($hash, $dirs) = @_;
3393 80 ▶ return unless defined $hash && !ref $hash && length $hash;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $hash and not ref $hash and length $hash 80 0 80
Condition33%2/6
defined $hash and not ref $hash and length $hash
!ll&&!rl&&r
8000
defined $hash and not ref $hash
!ll&&!rl&&r
8000
MC/DC0%0/3
defined $hash and not ref $hash and length $hash
defined $hash not ref $hash length $hash
Truth table25%1/4
A and not(B) and C
A= defined $hashB= not ref $hashC= length $hash
ABCresult
0XX0
10X0
1100
1111
3394 0 ▶ return if ref($dirs) eq 'ARRAY' && @{$dirs};
Branch: 1 branch
BranchTrueFalseTotal
if ref $dirs eq "ARRAY" and @{$dirs;} 0 0 0
Condition0%0/3
ref $dirs eq "ARRAY" and @{$dirs;}
!ll&&!rl&&r
000
MC/DC0%0/2
ref $dirs eq "ARRAY" and @{$dirs;}
ref $dirs eq "ARRAY" @{$dirs;}
Truth table0%0/3
A and B
A= ref $dirs eq "ARRAY"B= @{$dirs;}
ABresult
0X0
100
111
3395 0 croak "export_dirs: with password_hash set, other people can save exports, so keep them to folders of their own: "
3396 . "add export_dirs => ['/srv/reports/exports'] to database_bi.conf (see \"Which folders it may use\" in perldoc Database::BI)";
3397 }
3398  
3399 # _check_login( $hash, $expiration ) -> void
3400 #
3401 # Purpose: Refuse a login configuration that would not do what it says.
3402 # Entry: Config password_hash (undef or '' for no login, else a hash made by
3403 # script/database-bi password) and login_expiration (undef, or
3404 # seconds, a whole number from 60).
3405 # Exit: Returns; croaks otherwise. A plain password in password_hash is
3406 # refused rather than hashed here, so it is never left in the file.
3407 80 ▶sub _check_login {
Subroutine _check_login: called CC=10 SCAR=39.0
3408 80 my ($hash, $expiration) = @_;
3409 80 ▶ if (defined $hash && (ref $hash || length $hash)) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $hash and ref $hash || length $hash) 0 80 80
Condition16%1/6
defined $hash and ref $hash || length $hash
!ll&&!rl&&r
8000
ref $hash or length $hash
l!l&&r!l&&!r
000
MC/DC0%0/3
defined $hash and ref $hash || length $hash
defined $hash ref $hash length $hash
Truth table25%1/4
A and B or C
A= defined $hashB= ref $hashC= length $hash
ABCresult
0XX0
1000
11X1
1011
3410 0 require Database::BI::Password;
3411 0 ▶ croak 'password_hash: not a hash made by "script/database-bi password" (put the line it prints in database_bi.conf, not the password itself)'
Branch: 1 branch
BranchTrueFalseTotal
unless Database::BI::Password::is_password_hash($hash) 0 0 0
3412 unless Database::BI::Password::is_password_hash($hash);
3413 }
3414 80 ▶ croak sprintf("login_expiration: '%s' is not a number of seconds from 60", ref($expiration) || $expiration)
Branch: 1 branch
BranchTrueFalseTotal
if defined $expiration and ref $expiration || !($expiration =~ /\A\d+\z/u) || $expiration < 60 0 80 80
Condition8%1/12
defined $expiration and ref $expiration || !($expiration =~ /\A\d+\z/u) || $expiration < 60
!ll&&!rl&&r
8000
ref $expiration or not $expiration =~ /\A\d+\z/u or $expiration < 60
l!l&&r!l&&!r
000
ref $expiration or not $expiration =~ /\A\d+\z/u
l!l&&r!l&&!r
000
ref $expiration || $expiration
l!l&&r!l&&!r
000
MC/DC0%0/4
defined $expiration and ref $expiration || !($expiration =~ /\A\d+\z/u) || $expiration < 60
defined $expiration ref $expiration not $expiration =~ /\A\d+\z/u $expiration < 60
MC/DC0%0/2
ref $expiration || $expiration
ref $expiration $expiration
Truth table20%1/5
A and B or not(C) or D
A= defined $expirationB= ref $expirationC= not $expiration =~ /\A\d+\z/uD= $expiration < 60
ABCDresult
0XXX0
10000
11XX1
101X1
10011
Truth table0%0/3
A || B
A= ref $expirationB= $expiration
ABresult
1X1
011
000
3415 if defined $expiration && (ref $expiration || $expiration !~ /\A\d+\z/ || $expiration < 60);
3416 80 return;
3417 }
3418  
3419 # _require_password_if_open( $app, $listen, $hypnotoad ) -> void
3420 #
3421 # Purpose: Refuse to start where other machines could reach the server with no
3422 # password: everything it can read and write would then be theirs.
3423 # Entry: The application, MOJO_LISTEN as startup left it, and the hypnotoad
3424 # settings (used instead under hypnotoad).
3425 # Exit: Returns when every location is loopback or a password_hash is set;
3426 # croaks otherwise. The defaults (127.0.0.1, no password) start.
3427 #
3428 # A bare "morbo script/database-bi" listens on every interface (morbo opens
3429 # the port before it loads the application, so config listen never reaches
3430 # it), and now stops here; the message says to give it -l.
3431 80 ▶sub _require_password_if_open {
Subroutine _require_password_if_open: called CC=13 SCAR=30.1
3432 80 my ($app, $listen, $hypnotoad) = @_;
3433 80 my $hash = $app->config('password_hash');
3434 80 ▶ return if defined $hash && length $hash;
Branch: 1 branch
BranchTrueFalseTotal
if defined $hash and length $hash 0 80 80
Condition33%1/3
defined $hash and length $hash
!ll&&!rl&&r
8000
MC/DC0%0/2
defined $hash and length $hash
defined $hash length $hash
Truth table33%1/3
A and B
A= defined $hashB= length $hash
ABresult
0X0
100
111
3435  
3436 # Under hypnotoad (which sets HYPNOTOAD_APP) its own listen applies, and
3437 # MOJO_LISTEN does not.
3438 80 ▶ my @places = split /,/, $listen // '';
Condition50%1/2
$listen // ""
l!l
800
MC/DC0%0/1
$listen // ""
$listen
Truth table50%1/2
A
A= $listen
Aresult
11
00
3439 0 @places = @{ $hypnotoad->{listen} }
3440 80 ▶ if $ENV{HYPNOTOAD_APP} && ref($hypnotoad) eq 'HASH' && ref($hypnotoad->{listen}) eq 'ARRAY';
Branch: 1 branch
BranchTrueFalseTotal
if $ENV{"HYPNOTOAD_APP"} and ref $hypnotoad eq "HASH" and ref $hypnotoad->{"listen"} eq "ARRAY" 0 80 80
Condition33%2/6
$ENV{"HYPNOTOAD_APP"} and ref $hypnotoad eq "HASH" and ref $hypnotoad->{"listen"} eq "ARRAY"
!ll&&!rl&&r
8000
$ENV{"HYPNOTOAD_APP"} and ref $hypnotoad eq "HASH"
!ll&&!rl&&r
8000
MC/DC0%0/3
$ENV{"HYPNOTOAD_APP"} and ref $hypnotoad eq "HASH" and ref $hypnotoad->{"listen"} eq "ARRAY"
$ENV{"HYPNOTOAD_APP"} ref $hypnotoad eq "HASH" ref $hypnotoad->{"listen"} eq "ARRAY"
Truth table25%1/4
A and B and C
A= $ENV{"HYPNOTOAD_APP"}B= ref $hypnotoad eq "HASH"C= ref $hypnotoad->{"listen"} eq "ARRAY"
ABCresult
0XX0
10X0
1100
1111
3441 80 for my $place (@places) {
3442 80 ▶ my $host = lc(Mojo::URL->new($place)->host // '');
Condition50%1/2
"Mojo::URL"->new($place)->host // ""
l!l
800
MC/DC0%0/1
"Mojo::URL"->new($place)->host // ""
"Mojo::URL"->new($place)->host
Truth table50%1/2
A
A= "Mojo::URL"->new($place)->host
Aresult
11
00
3443 80 ▶ next if $host eq 'localhost' || $host =~ /\A127\./ || $host eq '[::1]' || $host eq '::1';
Branch: 1 branch
BranchTrueFalseTotal
if $host eq "localhost" or $host =~ /\A127\./u or $host eq "[::1]" or $host eq "::1" 78 2 80
Condition66%6/9
$host eq "localhost" or $host =~ /\A127\./u or $host eq "[::1]" or $host eq "::1"
l!l&&r!l&&!r
7802
$host eq "localhost" or $host =~ /\A127\./u or $host eq "[::1]"
l!l&&r!l&&!r
7802
$host eq "localhost" or $host =~ /\A127\./u
l!l&&r!l&&!r
0782
MC/DC25%1/4
$host eq "localhost" or $host =~ /\A127\./u or $host eq "[::1]" or $host eq "::1"
$host eq "localhost" $host =~ /\A127\./u $host eq "[::1]" $host eq "::1"
Truth table40%2/5
A or B or C or D
A= $host eq "localhost"B= $host =~ /\A127\./uC= $host eq "[::1]"D= $host eq "::1"
ABCDresult
1XXX1
01XX1
001X1
00011
00000
3444 2 (my $shown = $place) =~ s/\?.*//;
3445 2 croak "listen: $shown can be reached from other machines, and there is no password. "
3446 . 'Set password_hash (make it with "script/database-bi password"), '
3447 . 'or listen on this machine only: listen => \'http://127.0.0.1:3000\' '
3448 . '(for hypnotoad, hypnotoad => { listen => [\'http://127.0.0.1:8080\'] }; for morbo, -l http://127.0.0.1:3000)';
3449 }
3450 78 return;
3451 }
3452  
3453 # _check_url_private_hosts( $value ) -> void
3454 #
3455 # Purpose: Refuse a url_private_hosts that is not a list of host names.
3456 # Entry: Config url_private_hosts: undef, or names ('reports.corp.example',
3457 # or '*.corp.example' for its subdomains) that a url: source or
3458 # /import may fetch although they resolve to a private address.
3459 # Exit: Returns; croaks otherwise. '*' alone is refused: it would turn the
3460 # check off for every name, which is what egress rules are for.
3461 80 ▶sub _check_url_private_hosts {
Subroutine _check_url_private_hosts: called CC=9 SCAR=41.5
3462 80 my ($value) = @_;
3463 80 ▶ return unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 80 0 80
3464 0 ▶ croak "url_private_hosts: give a list of host names, such as ['reports.corp.example']"
Branch: 1 branch
BranchTrueFalseTotal
unless ref $value eq "ARRAY" 0 0 0
3465 unless ref($value) eq 'ARRAY';
3466 0 for my $name (@{$value}) {
3467 0 ▶ croak sprintf("url_private_hosts: '%s' is not a host name such as 'reports.corp.example' or '*.corp.example'",
Branch: 1 branch
BranchTrueFalseTotal
unless defined $name and not ref $name and $name =~ /\A(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)+\z/u 0 0 0
Condition0%0/11
defined $name and not ref $name and $name =~ /\A(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)+\z/u
!ll&&!rl&&r
000
defined $name and not ref $name
!ll&&!rl&&r
000
(ref $name || $name) // "undef"
l!l
00
ref $name or $name
l!l&&r!l&&!r
000
MC/DC0%0/3
defined $name and not ref $name and $name =~ /\A(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)+\z/u
defined $name not ref $name $name =~ /\A(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)+\z/u
MC/DC0%0/2
(ref $name || $name) // "undef"
ref $name $name
Truth table0%0/4
A and not(B) and C
A= defined $nameB= not ref $nameC= $name =~ /\A(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)+\z/u
ABCresult
0XX0
10X0
1100
1111
Truth table0%0/3
A or B
A= ref $nameB= $name
ABresult
1X1
011
000
3468 ref($name) || $name // 'undef')
3469 unless defined $name && !ref $name
3470 && $name =~ /\A(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)+\z/;
3471 }
3472 0 return;
3473 }
3474  
3475 # _check_map_bounds_ranks( $value ) -> void
3476 #
3477 # Purpose: Refuse a map_bounds_ranks that would not choose the county outlines.
3478 # Entry: Config map_bounds_ranks: undef, or { country code => place rank },
3479 # such as { lk => 10 }: the rank of Nominatim's that is a county in
3480 # that country, over Boundary's own list.
3481 # Exit: Returns; croaks on anything but a hash of two-letter codes to 8, 10
3482 # or 12 (Boundary->new says which entry), since a mistyped one would
3483 # otherwise draw no outlines, or the wrong ones, without a word.
3484 80 ▶sub _check_map_bounds_ranks {
Subroutine _check_map_bounds_ranks: called CC=5 SCAR=29.7
3485 80 my ($value) = @_;
3486 80 ▶ return unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 80 0 80
3487 0 ▶ croak "map_bounds_ranks: give a hash of country codes to place ranks, such as { lk => 10 }"
Branch: 1 branch
BranchTrueFalseTotal
unless ref $value eq "HASH" 0 0 0
3488 unless ref($value) eq 'HASH';
3489 0 require Database::BI::Model::Boundary;
3490 0 my $ok = eval { Database::BI::Model::Boundary->check_ranks($value); 1 };
3491 0 ▶ return if $ok;
Branch: 1 branch
BranchTrueFalseTotal
if $ok 0 0 0
3492 0 ▶ (my $error = $@ // 'unknown error') =~ s/ at \S+ line \d+\.?\n?\z//;
Condition0%0/2
$@ // "unknown error"
l!l
00
MC/DC0%0/1
$@ // "unknown error"
$@
Truth table0%0/2
A
A= $@
Aresult
11
00
3493 0 croak "map_bounds_ranks: $error";
3494 }
3495  
3496 # _check_share_secret( $value ) -> void
3497 #
3498 # Purpose: Refuse a share_secret that would not keep share links private.
3499 # Entry: Config share_secret: undef or '' (a key derived from secrets), or
3500 # the key share and embed tokens are signed with.
3501 # Exit: Returns; croaks on a reference, fewer than 16 characters, or the
3502 # fixed string versions before 0.021.0 used when none was set, which
3503 # anyone can read in the source.
3504 80 ▶sub _check_share_secret {
Subroutine _check_share_secret: called CC=7 SCAR=34.7
3505 80 my ($value) = @_;
3506 80 ▶ return unless defined $value && (ref $value || length $value);
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value and ref $value || length $value 80 0 80
Condition16%1/6
defined $value and ref $value || length $value
!ll&&!rl&&r
8000
ref $value or length $value
l!l&&r!l&&!r
000
MC/DC0%0/3
defined $value and ref $value || length $value
defined $value ref $value length $value
Truth table25%1/4
A and B or C
A= defined $valueB= ref $valueC= length $value
ABCresult
0XX0
1000
11X1
1011
3507 0 ▶ croak 'share_secret: give a string of at least 16 characters, or leave it out' if ref $value;
Branch: 1 branch
BranchTrueFalseTotal
if ref $value 0 0 0
3508 0 ▶ croak 'share_secret: that is the default from the source, which anyone can read; choose another'
Branch: 1 branch
BranchTrueFalseTotal
if $value eq "BI_DEFAULT_INSECURE_SECRET" 0 0 0
3509 if $value eq 'BI_DEFAULT_INSECURE_SECRET';
3510 0 ▶ croak 'share_secret: give at least 16 characters, so a share link cannot be forged by guessing it'
Branch: 1 branch
BranchTrueFalseTotal
if length $value < 16 0 0 0
3511 if length($value) < 16;
3512 0 return;
3513 }
3514  
3515 # _check_file_roots( $value, $home ) -> void
3516 #
3517 # Purpose: Refuse a file_roots that would not fence what it says.
3518 # Entry: Config file_roots: undef or [] (the whole filesystem), or a list of
3519 # folders, each absolute or relative to the application home.
3520 # Exit: Returns; croaks on anything but a list of folders that exist, since
3521 # a mistyped root would otherwise leave readers with nothing, or a
3522 # list that is not one would leave them everything.
3523 80 ▶sub _check_file_roots {
Subroutine _check_file_roots: called CC=11 SCAR=46.0
3524 80 my ($value, $home) = @_;
3525 80 ▶ return unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 80 0 80
3526 0 ▶ croak "file_roots: give a list of folders, such as ['/srv/data']" unless ref($value) eq 'ARRAY';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $value eq "ARRAY" 0 0 0
3527 0 for my $root (@{$value}) {
3528 0 ▶ croak sprintf("file_roots: '%s' is not a folder name", ref($root) || $root // 'undef')
Branch: 1 branch
BranchTrueFalseTotal
unless defined $root and not ref $root and length $root 0 0 0
Condition0%0/11
defined $root and not ref $root and length $root
!ll&&!rl&&r
000
defined $root and not ref $root
!ll&&!rl&&r
000
(ref $root || $root) // "undef"
l!l
00
ref $root or $root
l!l&&r!l&&!r
000
MC/DC0%0/3
defined $root and not ref $root and length $root
defined $root not ref $root length $root
MC/DC0%0/2
(ref $root || $root) // "undef"
ref $root $root
Truth table0%0/4
A and not(B) and C
A= defined $rootB= not ref $rootC= length $root
ABCresult
0XX0
10X0
1100
1111
Truth table0%0/3
A or B
A= ref $rootB= $root
ABresult
1X1
011
000
3529 unless defined $root && !ref $root && length $root;
3530 0 ▶ my $dir = File::Spec->file_name_is_absolute($root) ? $root : $home->child($root)->to_string;
Branch: 1 branch
BranchTrueFalseTotal
'File::Spec'->file_name_is_absolute($root) ? : 0 0 0
3531 0 ▶ croak "file_roots: '$root' is not a folder that exists" unless -d $dir;
Branch: 1 branch
BranchTrueFalseTotal
unless -d $dir 0 0 0
3532 }
3533 0 return;
3534 }
3535  
3536 # _check_export_dirs( $app, $value ) -> void
3537 #
3538 # Purpose: Refuse an export_dirs that would not confine exports as it says.
3539 # Entry: Config export_dirs: undef or [] (anywhere file_roots allows), or a
3540 # list of folders, relative ones under the application home.
3541 # Exit: Returns; croaks on what is not such a list, a folder that does not
3542 # exist, or one outside file_roots, where nothing could be saved.
3543 80 ▶sub _check_export_dirs {
Subroutine _check_export_dirs: called CC=17 SCAR=55.6
3544 80 my ($app, $value) = @_;
3545 80 ▶ return unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 80 0 80
3546 0 ▶ croak "export_dirs: give a list of folders, such as ['/srv/exports']" unless ref($value) eq 'ARRAY';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $value eq "ARRAY" 0 0 0
3547 0 for my $dir (@{$value}) {
3548 0 ▶ croak sprintf("export_dirs: '%s' is not a folder name", ref($dir) || $dir // 'undef')
Branch: 1 branch
BranchTrueFalseTotal
unless defined $dir and not ref $dir and length $dir 0 0 0
Condition0%0/11
defined $dir and not ref $dir and length $dir
!ll&&!rl&&r
000
defined $dir and not ref $dir
!ll&&!rl&&r
000
(ref $dir || $dir) // "undef"
l!l
00
ref $dir or $dir
l!l&&r!l&&!r
000
MC/DC0%0/3
defined $dir and not ref $dir and length $dir
defined $dir not ref $dir length $dir
MC/DC0%0/2
(ref $dir || $dir) // "undef"
ref $dir $dir
Truth table0%0/4
A and not(B) and C
A= defined $dirB= not ref $dirC= length $dir
ABCresult
0XX0
10X0
1100
1111
Truth table0%0/3
A or B
A= ref $dirB= $dir
ABresult
1X1
011
000
3549 unless defined $dir && !ref $dir && length $dir;
3550 0 ▶ my $path = File::Spec->file_name_is_absolute($dir) ? $dir : $app->home->child($dir)->to_string;
Branch: 1 branch
BranchTrueFalseTotal
'File::Spec'->file_name_is_absolute($dir) ? : 0 0 0
3551 0 ▶ croak "export_dirs: '$dir' is not a folder that exists" unless -d $path;
Branch: 1 branch
BranchTrueFalseTotal
unless -d $path 0 0 0
3552 0 require Database::BI::Controller::Dashboard;
3553 0 my $roots = $app->config('file_roots');
3554 0 ▶ if (ref($roots) eq 'ARRAY' && @{$roots}) {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $roots eq "ARRAY" and @{$roots;}) 0 0 0
Condition0%0/3
ref $roots eq "ARRAY" and @{$roots;}
!ll&&!rl&&r
000
MC/DC0%0/2
ref $roots eq "ARRAY" and @{$roots;}
ref $roots eq "ARRAY" @{$roots;}
Truth table0%0/3
A and B
A= ref $roots eq "ARRAY"B= @{$roots;}
ABresult
0X0
100
111
3555 0 my @real = Database::BI::Controller::Dashboard::_real_folders($app->build_controller, $roots,
3556 Database::BI::Controller::Dashboard::_data_dir_for($app), $app->_uploads_dir);
3557 0 ▶ croak "export_dirs: '$dir' is outside file_roots, so nothing could be saved there"
Branch: 1 branch
BranchTrueFalseTotal
unless Database::BI::Controller::Dashboard::_inside_any($path, @real) 0 0 0
3558 unless Database::BI::Controller::Dashboard::_inside_any($path, @real);
3559 }
3560 croak "export_dirs: '$dir' is a dot-folder, where exports are never saved"
3561 0 ▶ if grep { /\A\./ && $_ ne '.' && $_ ne '..' } File::Spec->splitdir(Mojo::File->new($path)->realpath->to_string);
Branch: 1 branch
BranchTrueFalseTotal
if grep {/\A\./u and $_ ne "." and $_ ne "..";} "File::Spec"->splitdir("Mojo::File"->new($path)->realpath->to_string) 0 0 0
Condition0%0/6
/\A\./u && $_ ne '.' && $_ ne '..'
!ll&&!rl&&r
000
/\A\./u and $_ ne "."
!ll&&!rl&&r
000
MC/DC0%0/3
/\A\./u && $_ ne '.' && $_ ne '..'
/\A\./u $_ ne "." $_ ne '..'
Truth table0%0/4
A and B && C
A= /\A\./uB= $_ ne "."C= $_ ne '..'
ABCresult
0XX0
10X0
1100
1111
3562 }
3563 0 return;
3564 }
3565  
3566 # _check_allowed_hosts( $value ) -> void
3567 #
3568 # Purpose: Refuse an allowed_hosts the request check could not use.
3569 # Entry: Config allowed_hosts: undef, or a list of host names, each a name
3570 # such as 'bi.example.com', '*.example.com' for its subdomains, or
3571 # '*' for every name.
3572 # Exit: Returns; croaks on anything else, since a list that does not say
3573 # what was meant either locks everyone out or lets the wrong site in.
3574 80 ▶sub _check_allowed_hosts {
Subroutine _check_allowed_hosts: called CC=9 SCAR=41.5
3575 80 my ($value) = @_;
3576 80 ▶ return unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 80 0 80
3577 0 ▶ croak "allowed_hosts: give a list of host names, such as ['bi.example.com']"
Branch: 1 branch
BranchTrueFalseTotal
unless ref $value eq "ARRAY" 0 0 0
3578 unless ref($value) eq 'ARRAY';
3579 0 for my $name (@{$value}) {
3580 0 ▶ croak sprintf("allowed_hosts: '%s' is not a host name such as 'bi.example.com', '*.example.com' or '*'",
Branch: 1 branch
BranchTrueFalseTotal
unless defined $name and not ref $name and $name =~ /\A(?:\*|(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)*)\z/u 0 0 0
Condition0%0/11
defined $name and not ref $name and $name =~ /\A(?:\*|(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)*)\z/u
!ll&&!rl&&r
000
defined $name and not ref $name
!ll&&!rl&&r
000
(ref $name || $name) // "undef"
l!l
00
ref $name or $name
l!l&&r!l&&!r
000
MC/DC0%0/3
defined $name and not ref $name and $name =~ /\A(?:\*|(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)*)\z/u
defined $name not ref $name $name =~ /\A(?:\*|(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)*)\z/u
MC/DC0%0/2
(ref $name || $name) // "undef"
ref $name $name
Truth table0%0/4
A and not(B) and C
A= defined $nameB= not ref $nameC= $name =~ /\A(?:\*|(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)*)\z/u
ABCresult
0XX0
10X0
1100
1111
Truth table0%0/3
A or B
A= ref $nameB= $name
ABresult
1X1
011
000
3581 ref($name) || $name // 'undef')
3582 unless defined $name && !ref $name
3583 && $name =~ /\A(?:\*|(?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)*)\z/;
3584 }
3585 0 return;
3586 }
3587  
3588 # _listen_locations( $value ) -> @locations
3589 #
3590 # Purpose: Where a server started without -l or MOJO_LISTEN listens.
3591 # Entry: Config listen: undef, a location such as 'http://*:3000', several
3592 # separated by commas, or an arrayref of them.
3593 # Exit: The locations, $DEFAULT_LISTEN when none is given; croaks on
3594 # anything that is not a location, since a server that listens
3595 # somewhere other than intended is a security setting gone wrong.
3596 80 ▶sub _listen_locations {
Subroutine _listen_locations: called CC=11 SCAR=32.0
3597 80 my ($value) = @_;
3598 80 ▶ return ($DEFAULT_LISTEN) unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 78 2 80
3599  
3600 2 ▶ my @locations = ref($value) eq 'ARRAY' ? @{$value} : ref($value) ? ($value) : split(/,/, $value);
Branch: 2 branches
BranchTrueFalseTotal
ref $value eq 'ARRAY' ? : 2 0 2
ref $value ? : 0 0 0
3601 2 ▶ croak "listen: give one or more locations, such as 'http://127.0.0.1:3000' or 'http://*:3000'"
Branch: 1 branch
BranchTrueFalseTotal
unless @locations 0 2 2
3602 unless @locations;
3603 2 for my $location (@locations) {
3604 2 ▶ croak sprintf("listen: '%s' is not a location such as 'http://127.0.0.1:3000' or 'http://*:3000'",
Branch: 1 branch
BranchTrueFalseTotal
unless defined $location and not ref $location and $location =~ m[\Ahttps?://[^\s,]+\z]u 0 2 2
Condition18%2/11
defined $location and not ref $location and $location =~ m[\Ahttps?://[^\s,]+\z]u
!ll&&!rl&&r
002
defined $location and not ref $location
!ll&&!rl&&r
002
(ref $location || $location) // "undef"
l!l
00
ref $location or $location
l!l&&r!l&&!r
000
MC/DC0%0/3
defined $location and not ref $location and $location =~ m[\Ahttps?://[^\s,]+\z]u
defined $location not ref $location $location =~ m[\Ahttps?://[^\s,]+\z]u
MC/DC0%0/2
(ref $location || $location) // "undef"
ref $location $location
Truth table25%1/4
A and not(B) and C
A= defined $locationB= not ref $locationC= $location =~ m[\Ahttps?://[^\s,]+\z]u
ABCresult
0XX0
10X0
1100
1111
Truth table0%0/3
A or B
A= ref $locationB= $location
ABresult
1X1
011
000
3605 ref($location) || $location // 'undef')
3606 unless defined $location && !ref $location && $location =~ m{\Ahttps?://[^\s,]+\z};
3607 }
3608 2 return @locations;
3609 }
3610  
3611 # _default_hypnotoad_listen( $config ) -> void
3612 #
3613 # Purpose: Make hypnotoad listen on this machine only unless told otherwise.
3614 # Entry: The application's config hashref.
3615 # Exit: hypnotoad => { listen } is $DEFAULT_HYPNOTOAD_LISTEN when it was
3616 # unset; a hypnotoad entry that is not a hash is left for hypnotoad.
3617 #
3618 # Hypnotoad reads hypnotoad => { listen } after loading the application, and
3619 # ignores MOJO_LISTEN; without one it would listen on every interface.
3620 80 ▶sub _default_hypnotoad_listen {
Subroutine _default_hypnotoad_listen: called CC=4 SCAR=14.6
3621 80 my ($config) = @_;
3622 80 ▶ my $hypnotoad = $config->{hypnotoad} //= {};
Condition50%1/2
$config->{"hypnotoad"} //= {}
l!l
080
MC/DC0%0/1
$config->{"hypnotoad"} //= {}
$config->{"hypnotoad"}
Truth table50%1/2
A
A= $config->{"hypnotoad"}
Aresult
11
00
3623 80 ▶ return unless ref($hypnotoad) eq 'HASH';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $hypnotoad eq "HASH" 0 80 80
3624 80 ▶ $hypnotoad->{listen} //= [$DEFAULT_HYPNOTOAD_LISTEN];
Condition50%1/2
$hypnotoad->{"listen"} //= [$DEFAULT_HYPNOTOAD_LISTEN]
l!l
080
MC/DC0%0/1
$hypnotoad->{"listen"} //= [$DEFAULT_HYPNOTOAD_LISTEN]
$hypnotoad->{"listen"}
Truth table50%1/2
A
A= $hypnotoad->{"listen"}
Aresult
11
00
3625 80 return;
3626 }
3627  
3628 # _new_nonce() -> $nonce
3629 #
3630 # Purpose: A fresh Content-Security-Policy nonce.
3631 # Exit: 22 base64url characters, 132 random bits.
3632 #
3633 # generate_secret is base64url of 128 bytes from Crypt::URandom (a weaker
3634 # generator only when that is missing); the page writes the nonce on every
3635 # <script>, so a prefix keeps it short while still unguessable.
3636 2186 ▶sub _new_nonce {
Subroutine _new_nonce: called CC=1 SCAR=0.0
3637 2186 return substr(Mojo::Util::generate_secret(), 0, 22);
3638 }
3639  
3640 # _content_security_policy( \%config, $nonce, \@framers ) -> $policy | undef
3641 #
3642 # Purpose: The Content-Security-Policy header every HTML reply carries.
3643 # Entry: The application's config, the request's nonce (see csp_nonce), and
3644 # the other origins that may frame this reply (only /embed/ has any).
3645 # content_security_policy, when set to a string, is the whole policy,
3646 # sent as written but for {nonce}, which becomes the nonce; set to ''
3647 # or 0 it turns the header off. Otherwise the policy is built from
3648 # d3_url, the five leaflet_*_url keys, tile_url and tile_sources.
3649 # Exit: The header's value, or undef for none.
3650 #
3651 # script-src has no 'unsafe-inline': an inline script runs only if it carries
3652 # the nonce, so one that came in with a cell's text does not. style-src keeps
3653 # it, for the style attributes the templates write and the styles Leaflet and
3654 # D3 set, which is a much smaller risk. The hosts in script-src are what let a
3655 # <script src> run without a nonce of its own, as the canvas page's card
3656 # scripts load Leaflet.
3657 #
3658 # Leaflet's marker images are found beside its stylesheet, so the stylesheet's
3659 # host is allowed images too. unpkg.com stays in script-src even when the
3660 # Leaflet keys point elsewhere: HTML::OSM loads leaflet.heat from there, and
3661 # no key moves it. The pins themselves are data: URIs (_marker_icon in the controller), and a
3662 # chart export draws its copy through a data: or blob: image.
3663 955 ▶sub _content_security_policy {
Subroutine _content_security_policy: called CC=12 SCAR=31.6
3664 955 my ($config, $nonce, $framers) = @_;
3665 955 ▶ $config //= {};
Condition50%1/2
$config //= {}
l!l
9550
MC/DC0%0/1
$config //= {}
$config
Truth table50%1/2
A
A= $config
Aresult
11
00
3666 955 ▶ $nonce = undef unless defined $nonce && $nonce =~ $NONCE_RE;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $nonce and $nonce =~ /$NONCE_RE/u 0 955 955
Condition33%1/3
defined $nonce and $nonce =~ /$NONCE_RE/u
!ll&&!rl&&r
00955
MC/DC0%0/2
defined $nonce and $nonce =~ /$NONCE_RE/u
defined $nonce $nonce =~ /$NONCE_RE/u
Truth table33%1/3
A and B
A= defined $nonceB= $nonce =~ /$NONCE_RE/u
ABresult
0X0
100
111
3667  
3668 955 my $set = $config->{content_security_policy};
3669 955 ▶ if (defined $set && !ref $set) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $set and not ref $set) 0 955 955
Condition33%1/3
defined $set and not ref $set
!ll&&!rl&&r
95500
MC/DC0%0/2
defined $set and not ref $set
defined $set not ref $set
Truth table33%1/3
A and not(B)
A= defined $setB= not ref $set
ABresult
0X0
100
111
3670 0 ▶ return undef unless length($set) && $set ne '0';
Branch: 1 branch
BranchTrueFalseTotal
unless length $set and $set ne 0 0 0 0
Condition0%0/3
length $set and $set ne 0
!ll&&!rl&&r
000
MC/DC0%0/2
length $set and $set ne 0
length $set $set ne 0
Truth table0%0/3
A and B
A= length $setB= $set ne 0
ABresult
0X0
100
111
3671 0 ▶ $set =~ s/\{nonce\}/$nonce \/\/ ''/ge;
Condition0%0/2
$nonce // ""
l!l
00
MC/DC0%0/1
$nonce // ""
$nonce
Truth table0%0/2
A
A= $nonce
Aresult
11
00
3672 0 return $set;
3673 }
3674  
3675 6685 ▶ my $from = sub {
Subroutine __ANON__: called CC=4 SCAR=14.0
3676 6685 my ($key, $default) = @_;
3677 6685 my $url = $config->{$key};
3678 6685 ▶ $url = $default unless defined $url && !ref $url && length $url;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $url and not ref $url and length $url 5731 954 6685
Condition66%4/6
defined $url and not ref $url and length $url
!ll&&!rl&&r
57310954
defined $url and not ref $url
!ll&&!rl&&r
57310954
MC/DC33%1/3
defined $url and not ref $url and length $url
defined $url not ref $url length $url
Truth table50%2/4
A and not(B) and C
A= defined $urlB= not ref $urlC= length $url
ABCresult
0XX0
10X0
1100
1111
3679 6685 return _csp_source($url);
3680 955 };
3681  
3682 my @script = ($from->('d3_url', $DEFAULT_D3_URL),
3683 955 map { $from->($_, $DEFAULT_LEAFLET_URL) } qw(leaflet_js_url leaflet_cluster_js_url));
3684 955 push @script, _csp_source($DEFAULT_LEAFLET_URL);
3685 955 my @style = map { $from->($_, $DEFAULT_LEAFLET_URL) }
3686 qw(leaflet_css_url leaflet_cluster_css_url leaflet_cluster_default_css_url);
3687 955 my $sources = $config->{tile_sources};
3688 my @tiles = ($from->('tile_url', $DEFAULT_TILE_URL),
3689 ref $sources eq 'HASH'
3690 0 ▶ ? map { _csp_source(ref $_ eq 'HASH' ? $_->{url} : undef) } @{$sources}{sort keys %{$sources}}
Branch: 2 branches
BranchTrueFalseTotal
ref $sources eq 'HASH' ? : 0 955 955
ref $_ eq 'HASH' ? : 0 0 0
3691 : ());
3692  
3693 2865 ▶ my $list = sub {
Subroutine __ANON__: called CC=2 SCAR=7.0
3694 2865 my %seen;
3695 2865 ▶ return join ' ', grep { defined && !$seen{$_}++ } @_;
Condition66%2/3
defined $_ && !$seen{$_}++
!ll&&!rl&&r
0573011460
MC/DC50%1/2
defined $_ && !$seen{$_}++
defined $_ !$seen{$_}++
Truth table66%2/3
A && not(B)
A= defined $_B= !$seen{$_}++
ABresult
0X0
100
111
3696 955 };
3697  
3698 955 ▶ return join '; ',
Branch: 1 branch
BranchTrueFalseTotal
defined $nonce ? : 955 0 955
3699 "default-src 'self'",
3700 'script-src ' . $list->("'self'", (defined $nonce ? "'nonce-$nonce'" : ()), @script),
3701 'style-src ' . $list->("'self'", "'unsafe-inline'", @style),
3702 'img-src ' . $list->("'self'", 'data:', 'blob:', @tiles, @style),
3703 "font-src 'self' data:",
3704 "connect-src 'self'",
3705 "object-src 'none'",
3706 "base-uri 'self'",
3707 "form-action 'self'",
3708 _frame_ancestors_directive($framers);
3709 }
3710  
3711 # _frame_ancestors_directive( \@framers ) -> $directive
3712 #
3713 # Purpose: frame-ancestors for a reply: this site, and the origins given.
3714 955 ▶sub _frame_ancestors_directive {
Subroutine _frame_ancestors_directive: called CC=2 SCAR=7.1
3715 955 my ($framers) = @_;
3716 955 ▶ return join ' ', 'frame-ancestors', "'self'", @{ $framers // [] };
Condition50%1/2
$framers // []
l!l
9550
MC/DC0%0/1
$framers // []
$framers
Truth table50%1/2
A
A= $framers
Aresult
11
00
3717 }
3718  
3719 # _embed_frame_ancestors( \%config ) -> @origins
3720 #
3721 # Purpose: The other sites that may frame /embed/, from embed_frame_ancestors.
3722 # Entry: The config. The key is a list of origins, or one as a string.
3723 # Exit: Each as a CSP source ("https://intranet.example.com"), lower case,
3724 # without a trailing slash and without repeats. An entry that is not
3725 # an origin is left out (startup refuses one; see
3726 # _check_embed_frame_ancestors), so a list changed after startup
3727 # cannot put anything else into the header.
3728 31 ▶sub _embed_frame_ancestors {
Subroutine _embed_frame_ancestors: called CC=6 SCAR=24.8
3729 31 my ($config) = @_;
3730 31 ▶ my $value = ($config // {})->{embed_frame_ancestors};
Condition50%1/2
$config // {}
l!l
310
MC/DC0%0/1
$config // {}
$config
Truth table50%1/2
A
A= $config
Aresult
11
00
3731 0 ▶ my @list = ref $value eq 'ARRAY' ? @{$value} : defined $value && !ref $value ? ($value) : ();
Branch: 2 branches
BranchTrueFalseTotal
ref $value eq 'ARRAY' ? : 0 31 31
defined $value && !ref($value) ? : 0 31 31
Condition33%1/3
defined $value and not ref $value
!ll&&!rl&&r
3100
MC/DC0%0/2
defined $value and not ref $value
defined $value not ref $value
Truth table33%1/3
A and not(B)
A= defined $valueB= not ref $value
ABresult
0X0
100
111
3732 31 my %seen;
3733 0 ▶ return grep { defined && !$seen{$_}++ } map { _frame_origin($_) } @list;
Condition0%0/3
defined $_ && !$seen{$_}++
!ll&&!rl&&r
000
MC/DC0%0/2
defined $_ && !$seen{$_}++
defined $_ !$seen{$_}++
Truth table0%0/3
A && not(B)
A= defined $_B= !$seen{$_}++
ABresult
0X0
100
111
3734 }
3735  
3736 # _check_embed_frame_ancestors( $value ) -> void
3737 #
3738 # Purpose: Refuse an embed_frame_ancestors that does not name origins.
3739 # Entry: The config value: undef, an origin, or a list of them.
3740 # Exit: Returns; croaks naming the first entry that is not an origin.
3741 80 ▶sub _check_embed_frame_ancestors {
Subroutine _check_embed_frame_ancestors: called CC=8 SCAR=38.8
3742 80 my ($value) = @_;
3743 80 ▶ return unless defined $value;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $value 80 0 80
3744 0 ▶ croak 'embed_frame_ancestors must be a list of origins, such as [ \'https://intranet.example.com\' ]'
Branch: 1 branch
BranchTrueFalseTotal
if ref $value and ref $value ne "ARRAY" 0 0 0
Condition0%0/3
ref $value and ref $value ne "ARRAY"
!ll&&!rl&&r
000
MC/DC0%0/2
ref $value and ref $value ne "ARRAY"
ref $value ref $value ne "ARRAY"
Truth table0%0/3
A and B
A= ref $valueB= ref $value ne "ARRAY"
ABresult
0X0
100
111
3745 if ref $value && ref $value ne 'ARRAY';
3746 0 ▶ for my $entry (ref $value ? @{$value} : $value) {
Branch: 1 branch
BranchTrueFalseTotal
ref $value ? : 0 0 0
3747 0 ▶ next if defined _frame_origin($entry);
Branch: 1 branch
BranchTrueFalseTotal
if defined _frame_origin($entry) 0 0 0
3748 0 ▶ croak sprintf("embed_frame_ancestors: '%s' is not an origin such as https://intranet.example.com"
Condition0%0/2
$entry // "undef"
l!l
00
MC/DC0%0/1
$entry // "undef"
$entry
Truth table0%0/2
A
A= $entry
Aresult
11
00
3749 . ' (a scheme, a host and an optional port, with no path)', $entry // 'undef');
3750 }
3751 0 return;
3752 }
3753  
3754 # _frame_origin( $entry ) -> $source | undef
3755 #
3756 # Purpose: One embed_frame_ancestors entry as a CSP source, if it is an origin.
3757 # Entry: A string.
3758 # Exit: "scheme://host[:port]", lower case, or undef.
3759 #
3760 # Only http and https origins: a scheme on its own (https:), *, 'none' or
3761 # 'self' would let far more than one site in, or say something else entirely,
3762 # and a path would be ignored by the browser. A wildcard is allowed as the
3763 # first label only (https://*.example.com), which is all CSP allows.
3764 0 ▶sub _frame_origin {
Subroutine _frame_origin: not called CC=8 SCAR=42.8
3765 0 my ($entry) = @_;
3766 0 ▶ return unless defined $entry && !ref $entry;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $entry and not ref $entry 0 0 0
Condition0%0/3
defined $entry and not ref $entry
!ll&&!rl&&r
000
MC/DC0%0/2
defined $entry and not ref $entry
defined $entry not ref $entry
Truth table0%0/3
A and not(B)
A= defined $entryB= not ref $entry
ABresult
0X0
100
111
3767 0 ▶ my ($scheme, $host, $port) = $entry =~ m{
Branch: 1 branch
BranchTrueFalseTotal
() = $entry =~ m[ \A\s*(https?):// ((?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)*) (?::(\d{1,5}))? /?\s*\z ]iux or return 0 0 0
3768 \A\s*(https?)://
3769 ((?:\*\.)?[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?(?:\.[A-Za-z0-9](?:[A-Za-z0-9-]*[A-Za-z0-9])?)*)
3770 (?::(\d{1,5}))?
3771 /?\s*\z
3772 }xi or return;
3773 0 ▶ return if defined $port && ($port == 0 || $port > 65_535);
Branch: 1 branch
BranchTrueFalseTotal
if defined $port and $port == 0 || $port > 65535 0 0 0
Condition0%0/6
defined $port and $port == 0 || $port > 65535
!ll&&!rl&&r
000
$port == 0 or $port > 65535
l!l&&r!l&&!r
000
MC/DC0%0/3
defined $port and $port == 0 || $port > 65535
defined $port $port == 0 $port > 65535
Truth table0%0/4
A and B or C
A= defined $portB= $port == 0C= $port > 65535
ABCresult
0XX0
1000
11X1
1011
3774 0 ▶ return lc("$scheme://$host") . (defined $port ? ":$port" : '');
Branch: 1 branch
BranchTrueFalseTotal
defined $port ? : 0 0 0
3775 }
3776  
3777 # _csp_source( $url ) -> $source | undef
3778 #
3779 # Purpose: The CSP source expression that allows $url.
3780 # Entry: A URL from the config: absolute (http, https or scheme-relative), or
3781 # a path, which is this site.
3782 # Exit: "https://host[:port]", "'self'", or undef when it names nothing a
3783 # policy can allow.
3784 #
3785 # Only the origin is kept: a source with a path would allow that one file, and
3786 # tile URLs carry {z}/{x}/{y}. A placeholder in the first label of the host
3787 # ({s}.tile.openstreetmap.org) becomes CSP's wildcard; one anywhere else cannot
3788 # be written as a host, so the whole scheme is allowed rather than the tiles
3789 # broken.
3790 7640 ▶sub _csp_source {
Subroutine _csp_source: called CC=22 SCAR=39.7
3791 7640 my ($url) = @_;
3792 7640 ▶ return unless defined $url && !ref $url && length $url;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $url and not ref $url and length $url 0 7640 7640
Condition33%2/6
defined $url and not ref $url and length $url
!ll&&!rl&&r
007640
defined $url and not ref $url
!ll&&!rl&&r
007640
MC/DC0%0/3
defined $url and not ref $url and length $url
defined $url not ref $url length $url
Truth table25%1/4
A and not(B) and C
A= defined $urlB= not ref $urlC= length $url
ABCresult
0XX0
10X0
1100
1111
3793  
3794 7640 ▶ if ($url !~ m{\A(?:[A-Za-z][A-Za-z0-9+.-]*:)?//}) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($url =~ m[\A(?:[A-Za-z][A-Za-z0-9+.-]*:)?//]u) 0 7640 7640
3795 # A path on this site; any other scheme (data:, javascript:) is none.
3796 0 ▶ return $url =~ m{\A[A-Za-z][A-Za-z0-9+.-]*:} ? undef : "'self'";
Branch: 1 branch
BranchTrueFalseTotal
$url =~ /\A[A-Za-z][A-Za-z0-9+.-]*:/u ? : 0 0 0
3797 }
3798 7640 my ($scheme, $authority) = $url =~ m{\A(?:([A-Za-z][A-Za-z0-9+.-]*):)?//([^/?#]*)};
3799 7640 ▶ $scheme = lc($scheme // '');
Condition50%1/2
$scheme // ""
l!l
76400
MC/DC0%0/1
$scheme // ""
$scheme
Truth table50%1/2
A
A= $scheme
Aresult
11
00
3800 7640 ▶ return if length $scheme && $scheme !~ /\Ahttps?\z/;
Branch: 1 branch
BranchTrueFalseTotal
if length $scheme and not $scheme =~ /\Ahttps?\z/u 0 7640 7640
Condition33%1/3
length $scheme and not $scheme =~ /\Ahttps?\z/u
!ll&&!rl&&r
076400
MC/DC0%0/2
length $scheme and not $scheme =~ /\Ahttps?\z/u
length $scheme not $scheme =~ /\Ahttps?\z/u
Truth table33%1/3
A and not(B)
A= length $schemeB= not $scheme =~ /\Ahttps?\z/u
ABresult
0X0
100
111
3801 7640 ▶ my $prefix = length $scheme ? "$scheme://" : '';
Branch: 1 branch
BranchTrueFalseTotal
length $scheme ? : 7640 0 7640
3802 7640 ▶ my $whole = length $scheme ? "$scheme:" : undef;
Branch: 1 branch
BranchTrueFalseTotal
length $scheme ? : 7640 0 7640
3803  
3804 7640 $authority =~ s/\A.*@//;
3805 7640 ▶ my ($host, $port) = $authority =~ /\A([^:]+)(?::(.*))?\z/
Branch: 1 branch
BranchTrueFalseTotal
() = $authority =~ /\A([^:]+)(?::(.*))?\z/u or return $whole 7640 0 7640
3806 or return $whole;
3807 7640 my @labels = split /\./, $host, -1;
3808 7640 ▶ $labels[0] = '*' if @labels > 1 && $labels[0] =~ /\{/;
Branch: 1 branch
BranchTrueFalseTotal
if @labels > 1 and $labels[0] =~ /\{/u 955 6685 7640
Condition66%2/3
@labels > 1 and $labels[0] =~ /\{/u
!ll&&!rl&&r
06685955
MC/DC50%1/2
@labels > 1 and $labels[0] =~ /\{/u
@labels > 1 $labels[0] =~ /\{/u
Truth table66%2/3
A and B
A= @labels > 1B= $labels[0] =~ /\{/u
ABresult
0X0
100
111
3809 7640 for my $i (0 .. $#labels) {
3810 18145 ▶ next if $i == 0 && $labels[0] eq '*';
Branch: 1 branch
BranchTrueFalseTotal
if $i == 0 and $labels[0] eq "*" 955 17190 18145
Condition100%3/3
$i == 0 and $labels[0] eq "*"
!ll&&!rl&&r
105056685955
MC/DC100%2/2
$i == 0 and $labels[0] eq "*"
$i == 0 $labels[0] eq "*"
Truth table100%3/3
A and B
A= $i == 0B= $labels[0] eq "*"
ABresult
0X0
100
111
3811 17190 ▶ return $whole unless $labels[$i] =~ /\A[A-Za-z0-9-]+\z/;
Branch: 1 branch
BranchTrueFalseTotal
unless $labels[$i] =~ /\A[A-Za-z0-9-]+\z/u 0 17190 17190
3812 }
3813 7640 ▶ if (defined $port) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $port) 0 7640 7640
3814 0 ▶ return $whole unless $port =~ /\A(?:\d{1,5}|\{[^}]*\})\z/;
Branch: 1 branch
BranchTrueFalseTotal
unless $port =~ /\A(?:\d{1,5}|\{[^}]*\})\z/u 0 0 0
3815 0 ▶ $port = $port =~ /\{/ ? '*' : $port;
Branch: 1 branch
BranchTrueFalseTotal
$port =~ /\{/u ? : 0 0 0
3816 }
3817 7640 ▶ return $prefix . lc(join '.', @labels) . (defined $port ? ":$port" : '');
Branch: 1 branch
BranchTrueFalseTotal
defined $port ? : 0 7640 7640
3818 }
3819  
3820 =head1 SEE ALSO
3821  
3822 =over 4
3823  
3824 =item * L<Test Dashboard|https://nigelhorne.github.io/Database-BI/coverage/>
3825  
3826 =back
3827  
3828 =head1 REPOSITORY
3829  
3830 L<https://github.com/nigelhorne/Database-BI>
3831  
3832 =head1 SUPPORT
3833  
3834 This module is provided as-is without any warranty.
3835  
3836 =head1 AUTHOR
3837  
3838 Nigel Horne C<< <njh@nigelhorne.com> >>
3839  
3840 =head1 LICENCE AND COPYRIGHT
3841  
3842 Copyright 2026 Nigel Horne.
3843  
3844 Usage is subject to the GPL2 licence terms.
3845 If you use it,
3846 please let me know.
3847  
3848 =cut
3849  
3850 7 1;
« blib/lib/Database/BI/SafeUA.pm ↑ Summary blib/lib/Database/BI/Password.pm »