lib/Database/BI/Controller/Dashboard.pm

Index Next ➡
Structural Coverage (Approximate)

TER1 (Statement): 99.69%
TER2 (Branch): 87.93%
TER3 (LCSAJ): 100.0% (29/29)
Approximate LCSAJ segments: 233

LCSAJ Legend

● Covered — this LCSAJ path was executed during testing.

● Not covered — this LCSAJ path was never executed. These are the paths to focus on.

Multiple dots on a line indicate that multiple control-flow paths begin at that line. Hovering over any dot shows: 

        start → end → jump

Uncovered paths show [NOT COVERED] in the tooltip.

Mutant Testing Legend

Survived (tests missed this) Killed (tests detected this) No mutation 
    1: package Database::BI::Controller::Dashboard;
    2: 
    3: our $VERSION = '0.002.0';
    4: 
    5: use Mojo::Base 'Mojolicious::Controller', -strict, -signatures;
    6: 
    7: use Carp		qw(croak carp);
    8: use Mojo::File;
    9: use Mojo::JSON		qw(encode_json);
   10: use Mojo::Util		qw(url_escape encode);
   11: use File::Temp		qw(tempfile tempdir);
   12: use Readonly;
   13: use Socket		qw(inet_aton);
   14: use Sub::Protected;
   15: 
   16: # ---------------------------------------------------------------------------
   17: # Constants
   18: # ---------------------------------------------------------------------------
   19: 
   20: # File extensions that Database::Abstraction can probe, in probe order.
   21: # Note: D::A uses ".sql" for SQLite -- NOT ".sqlite".
   22: Readonly my @SUPPORTED_EXT => qw( csv db sql xml psv );
   23: # Use \z (absolute end-of-string) not $ (which permits a trailing \n before \z).
   24: # A query param decoded from "file.csv%0A" has basename "sales.csv\n"; without \z
   25: # that passes the extension guard and reaches realpath with an embedded newline.
   26: Readonly my $EXT_RE        => do { my $p = join '|', @SUPPORTED_EXT; qr/\.(?:$p)\z/i };
   27: 
   28: # Table name safe-identifier pattern -- must match DataSource's TABLE_NAME_RE
   29: # exactly: first character must be a letter or underscore (not a digit), so
   30: # that every table name the controller accepts can also be opened by DataSource
   31: # without an unhandled croak.
   32: Readonly my $TABLE_NAME_RE => qr/\A[A-Za-z_][A-Za-z0-9_]*\z/;
   33: 
   34: # URL spec pattern shared by _open_spec and _spec_to_url.
   35: # qr{} delimiter avoids the \/ escaping noise required by the // form.
   36: # /s: makes . match \n so a percent-decoded newline inside a URL does not
   37: # silently truncate the captured URL before the \z end-of-string anchor.
   38: Readonly my $URL_SPEC_RE   => qr{\Aurl:(https?://.+)\z}si;
   39: 
   40: # ---------------------------------------------------------------------------
   41: # I18N message dictionary for all user-visible strings in this controller.
   42: # To plug in a Locale::Maketext backend, extend _i18n() below.
   43: # ---------------------------------------------------------------------------
   44: 
   45: Readonly my %MESSAGES => (
   46: 	error_table_invalid    => 'Invalid table name "%s"',
   47: 	error_table_open       => 'Could not open table "%s": %s',
   48: 	error_file_open        => 'Could not open "%s": %s',
   49: 	error_no_path          => 'No path specified',
   50: 	error_not_found        => 'File or directory not found: %s',
   51: 	error_dir_not_found    => 'Directory not found: %s',
   52: 	error_write_failed     => 'Write failed: %s',
   53: 	error_ext_required     => 'Use a .csv or .sql filename extension',
   54: 	error_upload_none      => 'No file received',
   55: 	error_upload_ext       => 'Unsupported file type. Accepted: CSV, PSV, XML, SQLite (.sql)',
   56: 	error_upload_too_large => 'File too large (maximum %s MiB)',
   57: 	error_path_required    => '"path" parameter is required',
   58: 	error_url_required     => 'Please enter a URL',
   59: 	error_url_invalid      => '"%s" is not a valid http:// or https:// URL',
   60: 	error_url_fetch        => 'Could not load HTML table from "%s": %s',
   61: 	error_url_ssrf         => '"%s" resolves to a private or reserved address and cannot be fetched',
   62: );
   63: 
   64: # Maximum accepted upload body size.  Enforced both here (application layer)
   65: # and via Mojolicious max_request_size (transport layer) set in startup().
   66: # Must match $MAX_REQUEST_SIZE in BI.pm.
   67: Readonly my $MAX_UPLOAD_MIB   => 50;
   68: Readonly my $MAX_UPLOAD_BYTES => $MAX_UPLOAD_MIB * 1_048_576;
   69: 
   70: # ---------------------------------------------------------------------------
   71: # Protected helpers
   72: # ---------------------------------------------------------------------------
   73: 
   74: # _i18n($self, $key, @sprintf_args) -> $string
   75: #
   76: # Purpose: Look up a user-visible string from %MESSAGES and apply sprintf
   77: #          for positional arguments.  The entry point for future i18n
   78: #          backend integration (e.g. Locale::Maketext).
   79: # Entry:   $key must be a key in %MESSAGES; @args are sprintf positionals.
   80: # Exit:    Returns the formatted string, or a fallback containing $key.
   81: sub _i18n :Protected ($self, $key, @args) {

  Mutants (Total: 2, Killed: 2, Survived: 0)
   82: 	my $tmpl = $MESSAGES{$key} // return "Internal error: unknown message key '$key'";
   83: 	return @args ? sprintf($tmpl, @args) : $tmpl;
   84: }
   85: 
   86: # _is_safe_url($url) -> bool
   87: #
   88: # Purpose: SSRF guard.  Blocks the most dangerous classes of Server-Side Request
   89: #          Forgery targets: loopback aliases (localhost, 127/8) and literal
   90: #          private/link-local/CGNAT IPv4 addresses in the URL host component.
   91: #
   92: # Design rationale for scope:
   93: #   Resolving hostnames via DNS and checking the result is ineffective: a
   94: #   separate DNS lookup happens at LWP connect time (TOCTOU / DNS rebinding
   95: #   window).  The authoritative defence against hostname-based SSRF is a
   96: #   network-layer egress firewall.  This function handles the Perl-layer
   97: #   interception for the most common patterns that operators cannot easily
   98: #   filter at the network level: bare loopback aliases and literal private IPs
   99: #   hard-coded by an attacker.
  100: #
  101: # Blocked targets:
  102: #   localhost / 127.0.0.0/8 / 0.0.0.0 / ::1  -- loopback aliases
  103: #   10.0.0.0/8    -- RFC 1918 private (literal IP)
  104: #   172.16.0.0/12 -- RFC 1918 private (literal IP)
  105: #   192.168.0.0/16-- RFC 1918 private (literal IP)
  106: #   169.254.0.0/16-- link-local; AWS/GCP/Azure metadata endpoint (literal IP)
  107: #   100.64.0.0/10 -- CGNAT / Tailscale shared space (literal IP)
  108: #
  109: # Hostname-based targets (e.g. http://internal.corp.example.com/) are allowed
  110: # at this layer; block them with egress firewall rules instead.
  111: sub _is_safe_url {

  Mutants (Total: 2, Killed: 2, Survived: 0)
  112: 	my ($url) = @_;
  113: 	return 0 unless $url =~ m{\Ahttps?://([^/:?\[\]#]+)}i;
  114: 	my $host = lc $1;
  115: 

  Mutants (Total: 2, Killed: 2, Survived: 0)
  116: 	# Block well-known loopback aliases.
  117: 	return 0 if $host eq 'localhost'
  118: 	          || $host =~ /\A127\./
  119: 	          || $host eq '0.0.0.0'
  120: 	          || $host eq '::1';
  121: 
  122: 	# For literal IPv4 addresses only: check private/link-local/CGNAT ranges.
  123: 	# Skipping DNS for hostname targets avoids a live network call in tests and

  Mutants (Total: 2, Killed: 2, Survived: 0)
  124: 	# removes the TOCTOU window that makes DNS-resolved checks illusory anyway.
  125: 	return 1 unless $host =~ /\A\d{1,3}(?:\.\d{1,3}){3}\z/;
  126: 
  127: 	my $packed = inet_aton($host) or return 1;
  128: 	my $n = unpack 'N', $packed;

  Mutants (Total: 3, Killed: 3, Survived: 0)
  129: 

  Mutants (Total: 3, Killed: 3, Survived: 0)
  130: 	return 0 if ($n & 0xFF000000) == 0x0A000000;	# 10/8

  Mutants (Total: 3, Killed: 3, Survived: 0)
  131: 	return 0 if ($n & 0xFFF00000) == 0xAC100000;	# 172.16/12

  Mutants (Total: 3, Killed: 3, Survived: 0)
  132: 	return 0 if ($n & 0xFFFF0000) == 0xC0A80000;	# 192.168/16

  Mutants (Total: 3, Killed: 3, Survived: 0)
  133: 	return 0 if ($n & 0xFFFF0000) == 0xA9FE0000;	# 169.254/16 (link-local / metadata)

  Mutants (Total: 2, Killed: 2, Survived: 0)
  134: 	return 0 if ($n & 0xFFC00000) == 0x64400000;	# 100.64/10 (CGNAT)
  135: 	return 1;
  136: }
  137: 
  138: # _resolve_template($self) -> ($platform, $language)
  139: #
  140: # Purpose: Read platform and language from config, then resolve the Accept-Language
  141: #          header to a language code -- falling back to the config default when
  142: #          no template directory exists for the resolved language.
  143: # Exit:    Returns ($platform, $language) -- both guaranteed non-empty strings.
  144: sub _resolve_template :Protected ($self) {
  145: 	my $conf     = $self->app->config;
  146: 	my $platform = $conf->{platform} // 'web';
  147: 	my $default  = $conf->{language} // 'en';
  148: 	my $language = $self->_resolve_language($default);
  149: 	return ($platform, $language);
  150: }
  151: 
  152: # _resolve_language($self, $default) -> $language_code
  153: #
  154: # Purpose: Extract the first two-letter language code from the Accept-Language
  155: #          request header, then validate that a template directory exists for
  156: #          that language.  Falls back to $default when the header is absent,
  157: #          unparseable, or points to a non-existent template directory.
  158: # Entry:   $default is a non-empty string (e.g. 'en').
  159: # Exit:    Returns a two-letter ISO 639-1 language code string.
  160: # Side Effects: Filesystem stat for the template directory.
  161: sub _resolve_language :Protected ($self, $default) {
●162 → 174 → 179  162: 	my $accept = $self->req->headers->accept_language // '';
  163: 	my ($lang) = $accept =~ /
  164: 		\b
  165: 		( [a-z]{2} )          # ISO 639-1 primary language subtag (exactly 2 lowercase letters)
  166: 		(?: - [A-Z]{2} )?     # optional ISO 3166-1 region subtag: hyphen + 2 uppercase letters
  167: 		\b
  168: 	/x;
  169: 	$lang //= $default;
  170: 
  171: 	# Only use the resolved language if templates actually exist for it;
  172: 	# prevents a 500 error when a browser sends e.g. Accept-Language: de

  Mutants (Total: 1, Killed: 1, Survived: 0)
  173: 	# but no templates/web/de/ directory is present.
  174: 	if ($lang ne $default) {
  175: 		my $platform = $self->app->config->{platform} // 'web';
  176: 		my $dir = $self->app->home->child("templates/$platform/$lang");
  177: 		$lang   = $default unless -d $dir;

  Mutants (Total: 2, Killed: 2, Survived: 0)
  178: 	}
  179: 	return $lang;
  180: }
  181: 
  182: # _scan_data_dir($self) -> \@table_list
  183: #
  184: # Purpose: Return an arrayref of { name, file } hashrefs for every supported
  185: #          data file in the configured data_dir.  Used by the index action and
  186: #          by the join panel "available tables" dropdown.
  187: # Entry:   None.
  188: # Exit:    Returns [] when data_dir does not exist or contains no supported files.
  189: # Side Effects: Filesystem directory read.
  190: #
  191: # Optimisation: single-pass map replaces a two-pass grep+map chain, and calls
  192: # basename() only once per entry (the original chain called it twice: once in
  193: # grep to check the extension and again in map to extract the stem).
  194: # ->to_array avoids the extra flat-list expansion that ->each produced.
  195: sub _scan_data_dir :Protected ($self) {
  196: 	my $dir = $self->app->home->child($self->app->config->{data_dir} // 'data');

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

  197: 	return [] unless -d $dir;
  198: 	return $dir->list->map(sub {
  199: 		my $base = $_->basename;
  200: 		return unless $base =~ $EXT_RE;
  201: 		(my $name = $base) =~ s/\.[^.]+\z//;
  202: 		{ name => $name, file => $base }
  203: 	})->to_array;
  204: }
  205: 
  206: # _open_spec($self, $spec) -> ($DataSource, $label) or ()
  207: #
  208: # Purpose: Parse a "table:name" or "path:/abs/path" spec, verify the underlying
  209: #          file exists (Database::Abstraction creates empty objects silently for
  210: #          missing files, so we must check ourselves), and return a live
  211: #          DataSource object and a display label.
  212: # Entry:   $spec is a non-empty string.
  213: # Exit:    Returns ($datasource, $label) on success; an empty list on any failure
  214: #          (invalid spec format, file missing, DataSource init error).
  215: # Side Effects: Filesystem stat; DataSource construction (reads file header).

  Mutants (Total: 1, Killed: 1, Survived: 0)
  216: sub _open_spec :Protected ($self, $spec) {
●217 → 217 → 239  217: 	if ($spec =~ /\Atable:([A-Za-z0-9_]+)\z/) {
  218: 		my $table    = lc $1;
  219: 		my $data_dir = $self->app->home->child($self->app->config->{data_dir} // 'data');
  220: 		return () unless grep { -f $data_dir->child("$table.$_") } @SUPPORTED_EXT;
  221: 		my $src = eval { $self->open_table($table, directory => $data_dir->to_string) };
  222: 		return ($src, $table) if $src && !$@;
  223: 	}
  224: 	elsif ($spec =~ /\Apath:(.+)\z/) {

  Mutants (Total: 1, Killed: 1, Survived: 0)
  225: 		my $file = eval { Mojo::File->new($1)->realpath };
  226: 		if (defined $file && -f $file && $file->basename =~ $EXT_RE) {
  227: 			my $dir = $file->dirname->to_string;
  228: 			(my $table = $file->basename) =~ s/\.[^.]+\z//;
  229: 			my $src = eval { $self->open_table(lc $table, directory => $dir) };
  230: 			return ($src, $file->basename) if $src && !$@;
  231: 		}
  232: 	}
  233: 	elsif ($spec =~ $URL_SPEC_RE) {
  234: 		my $url = $1;
  235: 		return () unless _is_safe_url($url);
  236: 		my $src = eval { $self->open_table('', url => $url) };
  237: 		return ($src, $src->table_name) if $src && !$@;
  238: 	}
  239: 	return ();
  240: }
  241: 
  242: # _spec_to_url($self, $spec) -> $url_string
  243: #
  244: # Purpose: Convert a table/path spec back to a browseable URL (used for the
  245: #          back-link on join and export views).
  246: # Entry:   $spec is a "table:name" or "path:/abs" string.
  247: # Exit:    Returns a URL string beginning with '/'.

  Mutants (Total: 2, Killed: 2, Survived: 0)
  248: sub _spec_to_url :Protected ($self, $spec) {

  Mutants (Total: 2, Killed: 2, Survived: 0)
  249: 	return "/view/$1"                         if $spec =~ /\Atable:([A-Za-z0-9_]+)\z/;

  Mutants (Total: 2, Killed: 2, Survived: 0)
  250: 	return '/open?path=' . url_escape($1)     if $spec =~ /\Apath:(.+)\z/;

  Mutants (Total: 2, Killed: 2, Survived: 0)
  251: 	return '/import?url=' . url_escape($1)    if $spec =~ $URL_SPEC_RE;
  252: 	return '/';
  253: }
  254: 
  255: # _get_columns($source, $records) -> @column_names
  256: #
  257: # Purpose: Return an ordered column list from a DataSource object.
  258: #          For CSV/PSV the DataSource stores the original file-header order.
  259: #          For SQLite/XML where no file-header order is available, the fallback
  260: #          is: id_column first (only if it actually appears in the data), then
  261: #          the remaining columns sorted alphabetically.
  262: # Entry:   $source is a DataSource; $records is an arrayref of hashrefs (may be empty).
  263: # Exit:    Returns a flat list of column-name strings (may be empty).
  264: #
  265: # Reduction: columns() is a pure accessor -- cache once to avoid a redundant
  266: # method dispatch on the second reference.
  267: sub _get_columns {
  268: 	my ($source, $records) = @_;

  Mutants (Total: 2, Killed: 2, Survived: 0)
  269: 	my $cols = $source->columns;
  270: 	return @$cols if $cols;
  271: 	return () unless $records->[0];
  272: 	my %all = map { $_ => 1 } keys %{ $records->[0] };
  273: 	my $c   = $source->id_column;
  274: 	my $id  = ($c && $all{$c}) ? $c : (sort keys %all)[0];
  275: 	delete $all{$id};
  276: 	return ($id, sort keys %all);
  277: }
  278: 
  279: # _apply_filter_spec($records, $spec) -> \@filtered_records
  280: #
  281: # Purpose: Apply one "col:op:val" filter to an arrayref of record hashrefs and
  282: #          return a new (possibly shorter) arrayref.  The original is not mutated.
  283: #          Value may contain colons: "col:op:val:with:colons" -- split limit 3
  284: #          puts everything after the second colon into $val.
  285: #          Unknown operators fall through to 1 (all rows pass) so new operators
  286: #          added in future do not break existing callers.
  287: # Entry:   $records is an arrayref of hashrefs; $spec is a colon-delimited string.
  288: # Exit:    Returns filtered arrayref (same reference if spec is invalid/unparseable).
  289: #
  290: # Domain Constraints (operator names):
  291: #   Operator names are CASE-SENSITIVE.  Only the exact lowercase forms below are
  292: #   recognised; an uppercase or mixed-case name (e.g. "EQ", "Lt") falls through
  293: #   to the default `1` branch and all rows pass unfiltered.
  294: #   Valid operators:
  295: #     String (value comparison is case-insensitive via lc()):
  296: #       eq, ne, contains, starts
  297: #     Numeric (Perl <, <=, >, >= coercion):
  298: #       lt (strict), le (inclusive), gt (strict), ge (inclusive)
  299: #     Unary (val is ignored):
  300: #       empty (cell eq ''), notempty (cell ne '')
  301: #   Boundary rules for numeric operators:
  302: #     le/ge include the boundary value; lt/gt exclude it.
  303: #   contains/starts with empty val ('') match every record (index always 0).
  304: #
  305: # Reduction: split(/:/, $spec, 3) always produces at least one defined element,
  306: # so "defined $col" is structurally guaranteed true and is removed.
  307: # "$_->{$col} // ''" replaces the equivalent ternary form.
  308: #
  309: # Optimisation: $lval = lc($val) is computed once before the grep.  Without this
  310: # precompute, every case-insensitive operator (eq, ne, contains, starts) would
  311: # call lc($val) O(N) times for N records -- a pure O(N) waste since $val is
  312: # constant within a single filter application.
  313: sub _apply_filter_spec {
  314: 	my ($records, $spec) = @_;

  Mutants (Total: 2, Killed: 2, Survived: 0)
  315: 	my ($col, $op, $val) = split /:/, $spec, 3;
  316: 	return $records unless length($col // '') && defined $op && length $op;
  317: 	$val //= '';
  318: 	my $lval = lc $val;	# precompute once; avoids O(N) redundant lc() inside grep
  319: 	return [grep {
  320: 		my $cell = $_->{$col} // '';
  321: 		$op eq 'eq'       ? lc($cell) eq $lval                :

  Mutants (Total: 1, Killed: 1, Survived: 0)
  322: 		$op eq 'ne'       ? lc($cell) ne $lval                :

  Mutants (Total: 1, Killed: 1, Survived: 0)
  323: 		$op eq 'contains' ? index(lc($cell), $lval) != -1     :

  Mutants (Total: 3, Killed: 3, Survived: 0)
  324: 		$op eq 'starts'   ? index(lc($cell), $lval) == 0      :

  Mutants (Total: 3, Killed: 3, Survived: 0)
  325: 		$op eq 'lt'       ? $cell <  $val                     :

  Mutants (Total: 3, Killed: 3, Survived: 0)
  326: 		$op eq 'le'       ? $cell <= $val                     :

  Mutants (Total: 3, Killed: 3, Survived: 0)
  327: 		$op eq 'gt'       ? $cell >  $val                     :
  328: 		$op eq 'ge'       ? $cell >= $val                     :
  329: 		$op eq 'empty'    ? $cell eq ''                        :
  330: 		$op eq 'notempty' ? $cell ne ''                        :
  331: 		1
  332: 	} @$records];
  333: }
  334: 
  335: # _apply_filters($self, $records) -> ($filtered_records, \@raw_specs, $json_string)
  336: #
  337: # Purpose: Read all "f=" query params, apply them in order via _apply_filter_spec,
  338: #          and return: the filtered arrayref, the raw spec strings (for building
  339: #          export URLs), and a script-safe JSON string for pre-populating the UI.
  340: # Entry:   $records is an arrayref of hashrefs.
  341: # Exit:    Three-element list; never croaks.  $json_string has '</' escaped to
  342: #          '<\/' so it is safe to embed directly in a <script> block.
  343: #
  344: # Reduction: the original code had two sequential loops over @specs -- one to
  345: # apply filters, one to parse them for JSON.  By Modus Ponens, _apply_filter_spec
  346: # returns $records unchanged for a malformed spec (same guard condition), so
  347: # skipping the call for malformed specs is equivalent.  Merging into one loop
  348: # eliminates a full O(n) pass and the redundant split() on every spec.
  349: sub _apply_filters :Protected ($self, $records) {
●350 → 352 → 358  350: 	my @specs  = @{ $self->every_param('f') // [] };
  351: 	my @parsed;
  352: 	for my $s (@specs) {
  353: 		my ($col, $op, $val) = split /:/, $s, 3;
  354: 		next unless length($col // '') && defined $op && length $op;
  355: 		push @parsed, { col => $col, op => $op, val => $val // '' };
  356: 		$records = _apply_filter_spec($records, $s);
  357: 	}
  358: 	my $json = encode_json(\@parsed);
  359: 	$json =~ s{</}{<\\/}g;
  360: 	return ($records, \@specs, $json);
  361: }
  362: 
  363: # _left_join($left_recs, $left_cols, $left_key,
  364: #            $right_recs, $right_cols, $right_key, $right_label)
  365: #   -> (\@merged_records, \@merged_columns)
  366: #
  367: # Purpose: Perform a single in-memory left join.  Every left row is kept.
  368: #          Right-table columns are appended for rows that match on the join key.
  369: #          Unmatched rows receive undef for right-table columns.
  370: #          If a right column name collides with a left column (other than the
  371: #          join key itself), the right column is prefixed with "$right_label.".
  372: # Entry:   All arrayref args non-undef; key strings non-empty; $right_label is
  373: #          a display label used for collision-prefix (not for SQL quoting).
  374: # Exit:    Returns (\@merged, \@column_list).
  375: # Side Effects: None; allocates new record hashrefs.
  376: sub _left_join {
●377 → 382 → 388  377: 	my ($left_recs, $left_cols, $left_key,
  378: 	    $right_recs, $right_cols, $right_key, $right_label) = @_;
  379: 
  380: 	# Build a lookup hash from join key to first matching right row.
  381: 	my %right_idx;
  382: 	for my $row (@$right_recs) {
  383: 		my $k = $row->{$right_key} // '';
  384: 		$right_idx{$k} //= $row;
  385: 	}
  386: 
  387: 	# Map right column names: drop the join key (redundant), prefix collisions.
●388 → 390 → 400  388: 	my %left_set = map { $_ => 1 } @$left_cols;
  389: 	my (@add_cols, %col_map);
  390: 	for my $col (grep { $_ ne $right_key } @$right_cols) {
  391: 		my $out = $left_set{$col} ? "${right_label}.${col}" : $col;
  392: 		$col_map{$col} = $out;
  393: 		push @add_cols, $out;
  394: 	}
  395: 
  396: 	# Precompute [$right_col, $mapped_col] pairs once before the merge loop.
  397: 	# Without this, "grep { $_ ne $right_key } @$right_cols" would run on
  398: 	# every left row -- O(N_left * R) grep iterations for R right columns.
  399: 	# Precomputing reduces that to a single O(R) pass.
●400 → 404 → 412  400: 	my @rcols = map { [$_, $col_map{$_}] }
  401: 	            grep { $_ ne $right_key } @$right_cols;
  402: 
  403: 	my @merged;
  404: 	for my $left_row (@$left_recs) {
  405: 		my $k         = $left_row->{$left_key} // '';
  406: 		my $right_row = $right_idx{$k} // {};
  407: 		my %row       = %$left_row;
  408: 		$row{ $_->[1] } = $right_row->{ $_->[0] } for @rcols;
  409: 		push @merged, \%row;
  410: 	}
  411: 
  412: 	return (\@merged, [@$left_cols, @add_cols]);
  413: }
  414: 
  415: # _combine_tables(\@sources) -> (\@merged_records, \@merged_columns)
  416: #
  417: # Purpose: Perform a vertical stack (UNION ALL equivalent) of two or more
  418: #          record sets.  The merged column list is the union of all source
  419: #          column lists: the first source's columns appear first (in their
  420: #          original order), then each subsequent source contributes any new
  421: #          column names it introduces, in order.  Where a source does not

  Mutants (Total: 2, Killed: 2, Survived: 0)
  422: #          have a particular column, its rows receive an empty string for
  423: #          that column.
  424: # Entry:   $sources is an arrayref of [$records_aref, $columns_aref] pairs.
  425: #          At least one element is required; a single-element call returns
  426: #          that element's data unchanged (no allocation beyond the column copy).
  427: # Exit:    Returns (\@merged_records, \@merged_columns).
  428: # Side Effects: None; allocates new record hashrefs (original rows are not mutated).
  429: sub _combine_tables {
●430 → 435 → 441  430: 	my ($sources) = @_;
  431: 
  432: 	# Build the unified column order: first source's columns first, then any
  433: 	# new columns introduced by each subsequent source in their file order.
  434: 	my (@all_cols, %seen);
  435: 	for my $src (@$sources) {
  436: 		for my $col (@{ $src->[1] }) {
  437: 			push @all_cols, $col unless $seen{$col}++;

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

  438: 		}
  439: 	}
  440: 
●441 → 442 → 453  441: 	my @merged;
  442: 	for my $src (@$sources) {
  443: 		my ($recs, $cols) = @$src;
  444: 		my %has_col = map { $_ => 1 } @$cols;
  445: 		for my $row (@$recs) {
  446: 			my %new_row;
  447: 			$new_row{$_} = $has_col{$_} ? ($row->{$_} // '') : ''
  448: 				for @all_cols;
  449: 			push @merged, \%new_row;
  450: 		}
  451: 	}
  452: 
  453: 	return (\@merged, \@all_cols);

  Mutants (Total: 1, Killed: 1, Survived: 0)
  454: }
  455: 
  456: # _csv_row(@fields) -> $csv_line_with_crlf
  457: #

  Mutants (Total: 1, Killed: 1, Survived: 0)
  458: # Purpose: Format one row as an RFC 4180 CSV line.  Fields containing commas,
  459: #          double-quotes, or newlines are enclosed in double-quotes; embedded
  460: #          double-quotes are doubled.  The line ends with CRLF.
  461: # Entry:   @fields may contain undef (treated as empty string).
  462: # Exit:    Returns a string ending with "\r\n".
  463: sub _csv_row {
  464: 	return join(',', map {
  465: 		my $f = $_ // '';
  466: 		$f =~ /[,"\r\n]/ ? do { (my $q = $f) =~ s/"/""/g; qq{"$q"} } : $f;
  467: 	} @_) . "\r\n";
  468: }
  469: 
  470: # _build_export_url($self, $left_spec, \@join_specs, \@filter_specs) -> $url_string
  471: #
  472: # Purpose: Assemble a /export?l=...&j=...&f=... URL string for the current
  473: #          logical view.  The caller must apply | html in TT to escape & as &amp;.
  474: # Entry:   All args non-undef; spec arrays may be empty.
  475: # Exit:    Returns a relative URL string beginning with '/export'.
  476: sub _build_export_url :Protected ($self, $left_spec, $join_specs, $filter_specs, $combine_specs = undef) {
  477: 	my $u = '/export?l=' . url_escape($left_spec);
  478: 	$u .= '&j=' . url_escape($_) for @$join_specs;
  479: 	$u .= '&c=' . url_escape($_) for @{ $combine_specs // [] };
  480: 	$u .= '&f=' . url_escape($_) for @$filter_specs;
  481: 	return $u;
  482: }
  483: 
  484: # _list_dir($self, $dir, $want_files) -> { dirs => [...], files => [...] }
  485: #
  486: # Purpose: Shared directory-listing kernel used by browse (which needs files)
  487: #          and dirs_api (which needs only subdirectories).  Hidden entries
  488: #          (names starting with ".") are excluded.  Entries are sorted
  489: #          case-insensitively.
  490: # Entry:   $dir is a Mojo::File pointing to an existing readable directory.
  491: #          $want_files: if true, populate 'files' with entries matching $EXT_RE.
  492: # Exit:    Returns a hashref with 'dirs' and 'files' arrayrefs (files is always
  493: #          present but empty when $want_files is false).
  494: # Side Effects: Filesystem directory read.
  495: sub _list_dir :Protected ($self, $dir, $want_files) {
●496 → 497 → 510  496: 	my (@dirs, @files);

  Mutants (Total: 1, Killed: 1, Survived: 0)
  497: 	if (opendir my $dh, $dir->to_string) {
  498: 		while (my $entry = readdir $dh) {
  499: 			next if $entry eq '.' || $entry eq '..' || $entry =~ /\A\./;
  500: 			my $f = $dir->child($entry);
  501: 			if (-d $f) {
  502: 				push @dirs,  { name => $entry, path => $f->to_string };
  503: 			}
  504: 			elsif ($want_files && $entry =~ $EXT_RE) {
  505: 				push @files, { name => $entry, path => $f->to_string };
  506: 			}

					

					Mutants (Total: 1, Killed: 0, Survived: 1)
					

  507: 		}
  508: 		closedir $dh;
  509: 	}
  510: 	return {
  511: 		dirs  => [ sort { lc($a->{name}) cmp lc($b->{name}) } @dirs  ],
  512: 		files => [ sort { lc($a->{name}) cmp lc($b->{name}) } @files ],
  513: 	};
  514: }
  515: 
  516: # _write_sqlite_db($self, $records, $columns) -> $raw_bytes
  517: #

  Mutants (Total: 1, Killed: 1, Survived: 0)
  518: # Purpose: Write $records to an in-process SQLite database (single table named
  519: #          "data"; every column declared TEXT) and return the raw file bytes.
  520: #          The temporary file is created, written, read, and then unlinked in
  521: #          one synchronous sequence.  Used by both _render_sqlite (HTTP download)
  522: #          and export_write (filesystem write) to eliminate code duplication.
  523: # Entry:   $records is an arrayref of hashrefs; $columns is an arrayref of names.
  524: # Exit:    Returns scalar binary string.  Croaks on any DBI error.
  525: # Side Effects: Creates and unlinks one temporary file under system tmpdir.

  Mutants (Total: 2, Killed: 2, Survived: 0)
  526: sub _write_sqlite_db :Protected ($self, $records, $columns) {
  527: 	# D~ fix: an empty column list produces "CREATE TABLE data ()" which is
  528: 	# invalid SQLite syntax.  Croak before touching the filesystem.
●529 → 540 → 547  529: 	croak 'Cannot export SQLite: result set has no columns'
  530: 		unless @$columns;
  531: 
  532: 	require DBI;
  533: 	my ($tmp_fh, $tmpfile) = tempfile(SUFFIX => '.db', UNLINK => 0);
  534: 	close $tmp_fh;
  535: 
  536: 	my $dbh = eval { DBI->connect("dbi:SQLite:dbname=$tmpfile", '', '', {
  537: 		RaiseError => 1, AutoCommit => 1,
  538: 	}) };
  539: 	# Guard both the eval-caught die ($@) and the undef-without-die edge case.
  540: 	if ($@ || !$dbh) {
  541: 		unlink $tmpfile;
  542: 		croak "DBI connect failed: $@";
  543: 	}
  544: 
  545: 	# O~ fix: wrap all DBI work in eval so any mid-flight exception still
  546: 	# triggers cleanup (disconnect + unlink) before the error propagates.
●547 → 561 → 567  547: 	eval {
  548: 		my @quoted = map { (my $c = $_) =~ s/"/""/g; qq{"$c"} } @$columns;
  549: 		$dbh->do('CREATE TABLE "data" (' . join(', ', map { "$_ TEXT" } @quoted) . ')');
  550: 		if (@$records) {
  551: 			my $ph  = join(', ', ('?') x scalar @$columns);
  552: 			my $sth = $dbh->prepare(
  553: 				'INSERT INTO "data" (' . join(', ', @quoted) . ") VALUES ($ph)"
  554: 			);
  555: 			for my $row (@$records) {
  556: 				$sth->execute(map { $row->{$_} } @$columns);
  557: 			}
  558: 		}
  559: 		$dbh->disconnect;
  560: 	};
  561: 	if (my $err = $@) {
  562: 		eval { $dbh->disconnect };	# best-effort; may already be disconnected
  563: 		unlink $tmpfile;
  564: 		croak $err;
  565: 	}
  566: 
  567: 	my $data = Mojo::File->new($tmpfile)->slurp;
  568: 	unlink $tmpfile;
  569: 	return $data;
  570: }
  571: 
  572: # _run_export_pipeline($self) -> ($records, \@columns, $left_label) or ()
  573: #
  574: # Purpose: Shared join+filter pipeline executed by both export_data (GET, download)
  575: #          and export_write (POST, filesystem write).  Opens the left table, applies
  576: #          all join steps, then applies all filter specs.
  577: # Entry:   Reads "l=", "j=" (repeatable), and "f=" (repeatable) query/body params.
  578: # Exit:    On success: ($filtered_arrayref, \@column_names, $left_label_string).
  579: #          On failure: empty list (left table not found / fetch error).
  580: # Side Effects: Opens data files; may emit carp warnings for empty result sets.
  581: sub _run_export_pipeline :Protected ($self) {
●582 → 593 → 614  582: 	my $left_spec = $self->param('l') // '';
  583: 	my ($left_src, $left_label) = $self->_open_spec($left_spec);
  584: 	return () unless $left_src;
  585: 
  586: 	my $left_recs = eval { $left_src->fetch_all };
  587: 	return () if $@;
  588: 	$left_recs //= [];
  589: 
  590: 	my @columns = _get_columns($left_src, $left_recs);
  591: 	my $records  = $left_recs;
  592: 
  593: 	for my $jspec (@{ $self->every_param('j') }) {
  594: 		my ($right_spec, $left_key, $right_key) = split /\|/, $jspec, 3;
  595: 		next unless defined $right_spec && defined $left_key && defined $right_key;
  596: 		my %col_set = map { $_ => 1 } @columns;
  597: 		next unless $col_set{$left_key};

  Mutants (Total: 2, Killed: 2, Survived: 0)
  598: 		my ($right_src, $right_label) = $self->_open_spec($right_spec);
  599: 		next unless $right_src;
  600: 		my $right_recs = eval { $right_src->fetch_all } // [];
  601: 		next if $@;
  602: 		my @right_cols = _get_columns($right_src, $right_recs);
  603: 		my %right_set  = map { $_ => 1 } @right_cols;
  604: 		next unless $right_set{$right_key};
  605: 		($records, my $new_cols) = _left_join(
  606: 			$records, \@columns, $left_key,
  607: 			$right_recs, \@right_cols, $right_key, $right_label,
  608: 		);
  609: 		@columns = @$new_cols;
  610: 	}
  611: 
  612: 	# Combine (vertical stack) with any c= sources.  Runs after joins so a join
  613: 	# result can itself be stacked with another table in a single pipeline.
●614 → 615 → 631  614: 	my @cspecs = @{ $self->every_param('c') // [] };
  615: 	if (@cspecs) {
  616: 		my @sources = ([$records, \@columns]);
  617: 		for my $cspec (@cspecs) {
  618: 			my ($csrc) = $self->_open_spec($cspec);
  619: 			next unless $csrc;
  620: 			my $crecs = eval { $csrc->fetch_all } // [];
  621: 			next if $@;
  622: 			my @ccols = _get_columns($csrc, $crecs);
  623: 			push @sources, [$crecs, \@ccols];
  624: 		}
  625: 		if (@sources > 1) {
  626: 			($records, my $new_cols) = _combine_tables(\@sources);
  627: 			@columns = @$new_cols;
  628: 		}
  629: 	}
  630: 
●631 → 631 → 635  631: 	for my $s (@{ $self->every_param('f') }) {
  632: 		$records = _apply_filter_spec($records, $s);
  633: 	}
  634: 
  635: 	return ($records, \@columns, $left_label);
  636: }
  637: 
  638: # _serialize_csv($records, \@columns) -> $utf8_string
  639: #
  640: # Purpose: Build a complete RFC 4180 CSV document (header + data rows) from
  641: #          $records.  Shared by _render_csv (HTTP download) and the csv branch
  642: #          of export_write (filesystem write) to eliminate duplicated logic.
  643: # Entry:   $records is an arrayref of hashrefs; $columns is an arrayref of names.
  644: # Exit:    Returns a UTF-8 Perl string ending with CRLF.
  645: #
  646: # Optimisation: rows are collected in @lines and joined in one operation.
  647: # The previous pattern ($out .= _csv_row(...) per row) triggers O(N) string
  648: # reallocations; joining a pre-built array is a single O(total_bytes) allocation.
  649: # For exports of thousands of rows the difference is measurable.
  650: #
  651: # XS note: for very large exports, replacing _csv_row with Text::CSV_XS
  652: # (available from CPAN) would further reduce serialisation time -- its C-level
  653: # implementation is roughly 5-10x faster than the pure-Perl version here.
  654: sub _serialize_csv {
●655 → 657 → 660  655: 	my ($records, $columns) = @_;
  656: 	my @lines = _csv_row(@$columns);
  657: 	for my $row (@$records) {
  658: 		push @lines, _csv_row(map { $row->{$_} } @$columns);
  659: 	}
  660: 	return encode('UTF-8', join('', @lines));
  661: }
  662: 
  663: # _render_csv($self, $records, \@columns, $name) -> void (renders response)
  664: #
  665: # Purpose: Serialise $records to RFC 4180 CSV and emit as a UTF-8 download.
  666: # Entry:   $name is a safe filename stem (alphanumeric/underscore).
  667: # Exit:    Renders the Mojolicious response; does not return a meaningful value.
  668: # Side Effects: Writes HTTP response headers and body.
  669: sub _render_csv :Protected ($self, $records, $columns, $name) {
  670: 	$self->res->headers->content_type('text/csv; charset=UTF-8');
  671: 	$self->res->headers->content_disposition(qq{attachment; filename="${name}.csv"});
  672: 	$self->render(data => _serialize_csv($records, $columns));
  673: }
  674: 
  675: # _render_sqlite($self, $records, \@columns, $name) -> void (renders response)
  676: #
  677: # Purpose: Write $records to a SQLite database and emit it as a binary download.
  678: # Entry:   $name is a safe filename stem.
  679: # Exit:    Renders the Mojolicious response.
  680: # Side Effects: Creates and unlinks a temporary file; writes HTTP response.
  681: sub _render_sqlite :Protected ($self, $records, $columns, $name) {
  682: 	my $data = $self->_write_sqlite_db($records, $columns);
  683: 	$self->res->headers->content_type('application/vnd.sqlite3');
  684: 	$self->res->headers->content_disposition(qq{attachment; filename="${name}.db"});
  685: 	$self->render(data => $data);
  686: }
  687: 
  688: # ---------------------------------------------------------------------------
  689: # Public actions
  690: # ---------------------------------------------------------------------------
  691: 
  692: =head1 ACTIONS
  693: 
  694: =head2 index
  695: 
  696: C<GET /> -- Scan C<data_dir> and present a card grid of available tables.
  697: 
  698: =head3 API SPECIFICATION
  699: 
  700: =head4 INPUT
  701: 
  702: None (reads C<data_dir> from application config).
  703: 
  704: =head4 OUTPUT
  705: 
  706: Renders C<home.html.tt> with:
  707: 
  708:   tables     ARRAYREF of { name => $stem, file => $basename }
  709:   title      'Choose a Database'
  710: 
  711: =head3 MESSAGES
  712: 
  713: None produced by this action; any file-scan errors are silently ignored
  714: (empty directory yields an empty card grid).
  715: 
  716: =head3 FORMAL SPECIFICATION
  717: 
  718:   index == lambda self .
  719:     let dir  = resolve self.app.config.data_dir in
  720:     let tbls = { b | b in dir /\ basename(b) =~ EXT_RE } in
  721:     post render(home, tables: map(stem, tbls))
  722: 
  723: =head3 EXAMPLE
  724: 
  725:   GET / -> 200 text/html containing a card for each file in data/
  726: 
  727: =cut
  728: 
  729: sub index ($self) {
  730: 	my ($platform, $language) = $self->_resolve_template;
  731: 
  732: 	$self->render(
  733: 		template => "$platform/$language/home",
  734: 		handler  => 'tt',
  735: 		format   => 'html',
  736: 		tables   => $self->_scan_data_dir,
  737: 		title    => 'Choose a Database',
  738: 	);
  739: }
  740: 
  741: =head2 view
  742: 
  743: C<GET /view/:table> -- Open and display the chosen table from C<data_dir>.
  744: 
  745: =head3 API SPECIFICATION
  746: 
  747: =head4 INPUT

  Mutants (Total: 1, Killed: 1, Survived: 0)
  748: 

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

  749:   :table   string   Table name (alphanumeric + underscore).  Returns 404 on
  750:                     other characters.
  751:   ?f=      string   (repeatable) Filter spec: "col:op:val".
  752: 
  753: =head4 DOMAIN CONSTRAINTS: :table

  Mutants (Total: 1, Killed: 1, Survived: 0)
  754: 

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

  755: C<:table> is validated against C<\A[A-Za-z_][A-Za-z0-9_]*\z> before C<open_table>
  756: is ever called.  The first character must be a letter (A-Z, a-z) or an
  757: underscore; subsequent characters may also include digits (0-9).
  758: 
  759: =over 4
  760: 
  761: =item Valid partition
  762: 
  763: C<sales> (letters only), C<_temp> (underscore-start), C<report_2024>
  764: (mixed).  A name that is valid but has no backing file returns 200 with
  765: an error message -- NOT 404.
  766: 
  767: =item Invalid partition
  768: 
  769: C<1sales> (digit-start, 404), C<my.data> (dot, 404), C<my-data>
  770: (hyphen, 404), non-ASCII characters (404).
  771: 
  772: =item Boundary values
  773: 
  774: C<a> (single letter, valid), C<_> (single underscore, valid), C<1>
  775: (single digit, 404), C<a1> (letter then digit, valid), C<1a> (digit
  776: then letter, 404).
  777: 
  778: =back
  779: 
  780: =head4 OUTPUT
  781: 
  782: On success renders C<dashboard.html.tt> with table data.
  783: On error re-renders C<home.html.tt> with an C<error> stash variable.
  784: 
  785: =head3 MESSAGES
  786: 
  787:   error_table_open   -- DataSource initialisation or fetch_all threw.
  788: 
  789: =head3 FORMAL SPECIFICATION
  790: 
  791:   view == lambda self .
  792:     pre  self.stash('table') =~ TABLE_NAME_RE
  793:     let src     = open_table(table) in
  794:     let records = src.fetch_all()   in
  795:     let cols    = get_columns(src, records) in
  796:     post render(dashboard, records: filter(records, f_params))
  797: 
  798: =head3 EXAMPLE
  799: 
  800:   GET /view/sales      -> 200 HTML table of all sales rows
  801:   GET /view/sales?f=region:eq:North -> shows only North region rows
  802:   GET /view/../etc     -> 404
  803: 
  804: =cut
  805: 
  806: sub view ($self) {
●807 → 810 → 814  807: 	my ($platform, $language) = $self->_resolve_template;
  808: 	my $table = $self->stash('table');
  809: 
  810: 	unless (defined $table && $table =~ $TABLE_NAME_RE) {
  811: 		return $self->reply->not_found;
  812: 	}
  813: 
●814 → 816 → 827  814: 	my ($source, $records);
  815: 	eval { $source = $self->open_table($table); $records = $source->fetch_all };
  816: 	if ($@) {
  817: 		return $self->render(
  818: 			template => "$platform/$language/home",
  819: 			handler  => 'tt',
  820: 			format   => 'html',
  821: 			tables   => [],
  822: 			title    => 'Choose a Database',
  823: 			error    => $self->_i18n('error_table_open', $table, $@),
  824: 		);
  825: 	}
  826: 
  827: 	my @columns = _get_columns($source, $records);
  828: 	my ($filtered, $filter_specs, $filters_json) = $self->_apply_filters($records);
  829: 
  830: 	$self->render(
  831: 		template         => "$platform/$language/dashboard",

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

  832: 		handler          => 'tt',
  833: 		format           => 'html',
  834: 		records          => $filtered,
  835: 		columns          => \@columns,
  836: 		table            => $table,
  837: 		title            => ucfirst($table),
  838: 		left_spec        => "table:$table",
  839: 		combine_specs    => [],
  840: 		current_joins    => [],
  841: 		available_tables => $self->_scan_data_dir,
  842: 		join_summaries   => [],
  843: 		filter_specs     => $filter_specs,
  844: 		filters_json     => $filters_json,
  845: 		export_url       => $self->_build_export_url("table:$table", [], $filter_specs),
  846: 	);
  847: }
  848: 
  849: =head2 browse
  850: 
  851: C<GET /browse> -- Navigate the filesystem and pick a data file.
  852: 
  853: =head3 API SPECIFICATION
  854: 
  855: =head4 INPUT
  856: 
  857:   ?path=   string   Absolute filesystem path to browse (default: $HOME).
  858:                     Returns 404 when the path does not exist or is not a
  859:                     directory.
  860: 
  861: =head4 OUTPUT
  862: 
  863: Renders C<browse.html.tt> with:
  864: 
  865:   current_path   string
  866:   parent_href    string or undef (undef at filesystem root)
  867:   crumbs         ARRAYREF of { name, href }
  868:   dirs           ARRAYREF of { name, href }   -- subdirectories, sorted
  869:   files          ARRAYREF of { name, href }   -- supported data files, sorted
  870: 
  871: =head3 MESSAGES
  872: 
  873: None; errors render as 404.
  874: 
  875: =head3 FORMAL SPECIFICATION
  876: 
  877:   browse == lambda self .
  878:     let dir = realpath(param('path') // HOME) in
  879:     pre  is_dir(dir)
  880:     post render(browse, dirs: subdirs(dir), files: supported_files(dir))
  881: 
  882: =head3 EXAMPLE
  883: 
  884:   GET /browse                            -> 200, lists $HOME
  885:   GET /browse?path=/tmp                  -> 200, lists /tmp
  886:   GET /browse?path=/nonexistent/xyz      -> 404
  887: 
  888: =cut
  889: 
  890: sub browse ($self) {
  891: 	my ($platform, $language) = $self->_resolve_template;
  892: 
  893: 	my $raw_path = $self->param('path') // $ENV{HOME} // '/';
  894: 	my $dir      = eval { Mojo::File->new($raw_path)->realpath };
  895: 	return $self->reply->not_found unless defined $dir && -d $dir;
  896: 
  897: 	my $listing = $self->_list_dir($dir, 1);
  898: 
  899: 	# Add browse hrefs to file entries (dirs already have path; browse needs href).
  900: 	my @dirs = map {
  901: 		+{ name => $_->{name}, href => '/browse?path=' . url_escape($_->{path}) }
  902: 	} @{ $listing->{dirs} };
  903: 
  904: 	my @files = map {
  905: 		+{ name => $_->{name}, href => '/open?path=' . url_escape($_->{path}) }
  906: 	} @{ $listing->{files} };
  907: 
  908: 	# Build breadcrumb trail from filesystem root down to $dir.
  909: 	my @crumbs;
  910: 	{
  911: 		my $f = $dir;
  912: 		while (1) {
  913: 			my $name = $f->basename;
  914: 			$name    = '/' unless length $name;
  915: 			unshift @crumbs, { name => $name, href => '/browse?path=' . url_escape($f->to_string) };
  916: 			my $parent = $f->dirname;
  917: 			last if $parent->to_string eq $f->to_string;
  918: 			$f = $parent;
  919: 		}
  920: 	}
  921: 
  922: 	my $parent      = $dir->dirname;
  923: 	my $parent_href = $parent->to_string ne $dir->to_string
  924: 		? '/browse?path=' . url_escape($parent->to_string)
  925: 		: undef;
  926: 
  927: 	$self->render(
  928: 		template     => "$platform/$language/browse",
  929: 		handler      => 'tt',
  930: 		format       => 'html',
  931: 		title        => 'Browse Files',
  932: 		current_path => $dir->to_string,
  933: 		parent_href  => $parent_href,
  934: 		crumbs       => \@crumbs,
  935: 		dirs         => \@dirs,
  936: 		files        => \@files,
  937: 	);
  938: }
  939: 
  940: =head2 open_file
  941: 
  942: C<GET /open> -- Open a supported data file from any absolute filesystem path.
  943: 
  944: =head3 API SPECIFICATION
  945: 
  946: =head4 INPUT
  947: 
  948:   ?path=   string   Absolute path to the data file.  Returns 404 when missing,
  949:                     not a regular file, or the extension is not in SUPPORTED_EXT.
  950:   ?f=      string   (repeatable) Filter spec: "col:op:val".
  951: 
  952: =head4 DOMAIN CONSTRAINTS: ?path=

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

  953: 
  954: =over 4
  955: 

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

  956: =item Extension filter (C<EXT_RE>)
  957: 
  958: The basename must match C<\.(?:csv|db|sql|xml|psv)\z> (case-insensitive).
  959: The C<\z> anchor (absolute end-of-string) means a URL-encoded trailing
  960: newline (e.g. C<file.csv%0A> decoded to C<file.csv\n>) does NOT pass --
  961: the C<\n> falls after the C<\z> boundary and the extension check fails.
  962: 
  963: =item Valid partition
  964: 
  965: C</data/sales.csv> (lowercase extension), C</tmp/REPORT.CSV> (uppercase
  966: extension, /i matches), any C<.db>, C<.sql>, C<.xml>, C<.psv> regular
  967: file.

  Mutants (Total: 1, Killed: 1, Survived: 0)
  968: 

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

  969: =item Invalid partition
  970: 
  971: Absent C<?path=> (404), non-existent file (404), directory instead of
  972: file (404), unsupported extension such as C<.txt> (404), empty string
  973: (404), path containing a C<%0A> (newline) suffix (404).
  974: 
  975: =item Double-extension filenames
  976: 
  977: A file like C<file.php.csv> passes C<EXT_RE> (last segment is C<.csv>)
  978: but produces table stem C<file.php> which fails C<DataSource>'s
  979: C<TABLE_NAME_RE>.  The C<open_table> call is inside C<eval>, so the
  980: croak is caught and the action returns 200 with a friendly error, not
  981: a 500.
  982: 
  983: =back
  984: 
  985: =head4 OUTPUT
  986: 
  987: On success renders C<dashboard.html.tt> with C<back_url> pointing to the
  988: directory's C</browse> page, and C<file_path> set to the resolved absolute
  989: path (used by the template to record the file in C<localStorage>).
  990: On error re-renders C<home.html.tt> with C<error>, C<back_url>, C<back_label>.
  991: 
  992: =head3 MESSAGES
  993: 
  994:   error_file_open   -- DataSource threw during initialisation or fetch_all.
  995: 
  996: =head3 FORMAL SPECIFICATION
  997: 
  998:   open_file == lambda self .
  999:     let file = realpath(param('path')) in
 1000:     pre  is_file(file) /\ basename(file) =~ EXT_RE
 1001:     let src = open_table(stem(file), dir=dirname(file)) in
 1002:     post render(dashboard, file_path: file.to_string)
 1003: 
 1004: =head3 EXAMPLE
 1005: 
 1006:   GET /open?path=/data/archive/sales.csv   -> 200, table view
 1007:   GET /open                                -> 404
 1008:   GET /open?path=/etc/passwd               -> 404
 1009: 
 1010: =cut
 1011: 
 1012: sub open_file ($self) {
●1013 → 1031 → 1044 1013: 	my ($platform, $language) = $self->_resolve_template;
 1014: 	my $file_path = $self->param('path');
 1015: 
 1016: 	return $self->reply->not_found unless defined $file_path;
 1017: 
 1018: 	my $file = eval { Mojo::File->new($file_path)->realpath };
 1019: 	return $self->reply->not_found
 1020: 		unless defined $file && -f $file && $file->basename =~ $EXT_RE;
 1021: 
 1022: 	my $dir      = $file->dirname;
 1023: 	(my $table   = $file->basename) =~ s/\.[^.]+\z//;
 1024: 	$table       = lc $table;
 1025: 	my $back     = '/browse?path=' . url_escape($dir->to_string);
 1026: 	my $filename = $file->basename;
 1027: 	my $lspec    = 'path:' . $file->to_string;
 1028: 
 1029: 	my ($source, $records);
 1030: 	eval { $source = $self->open_table($table, directory => $dir->to_string); $records = $source->fetch_all };
 1031: 	if ($@) {
 1032: 		return $self->render(
 1033: 			template   => "$platform/$language/home",
 1034: 			handler    => 'tt',
 1035: 			format     => 'html',
 1036: 			tables     => [],
 1037: 			title      => 'Error',
 1038: 			error      => $self->_i18n('error_file_open', $filename, $@),
 1039: 			back_url   => $back,
 1040: 			back_label => 'Back to browser',
 1041: 		);
 1042: 	}
 1043: 
 1044: 	my @columns  = _get_columns($source, $records);
 1045: 	my ($filtered, $filter_specs, $filters_json) = $self->_apply_filters($records);
 1046: 
 1047: 	$self->render(
 1048: 		template         => "$platform/$language/dashboard",
 1049: 		handler          => 'tt',
 1050: 		format           => 'html',
 1051: 		records          => $filtered,
 1052: 		columns          => \@columns,
 1053: 		table            => $table,
 1054: 		title            => $filename,
 1055: 		back_url         => $back,
 1056: 		back_label       => 'Back to browser',
 1057: 		file_path        => $file->to_string,
 1058: 		left_spec        => $lspec,
 1059: 		combine_specs    => [],

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1060: 		current_joins    => [],

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1061: 		available_tables => $self->_scan_data_dir,
 1062: 		join_summaries   => [],

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1063: 		filter_specs     => $filter_specs,
 1064: 		filters_json     => $filters_json,
 1065: 		export_url       => $self->_build_export_url($lspec, [], $filter_specs),
 1066: 	);

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1067: }
 1068: 
 1069: =head2 import_url

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1070: 
 1071: C<GET /import> -- Fetch a remote HTML page, extract the first (or selected)
 1072: table, and render it as a sortable data grid.
 1073: 
 1074: =head3 API SPECIFICATION
 1075: 
 1076: =head4 INPUT
 1077: 
 1078:   ?url=          string   Required.  Full http:// or https:// URL of the page
 1079:                           that contains the HTML table.  Returns home with an
 1080:                           error message if the scheme is wrong, the fetch fails,
 1081:                           or no table is found at the specified index.
 1082:   ?table_index=  integer  Zero-based index of the C<< <table> >> to use when
 1083:                           the page contains more than one table (default: 0).
 1084:   ?f=            string   (repeatable) Filter spec: "col:op:val".
 1085: 
 1086: =head4 OUTPUT
 1087: 
 1088: On success renders C<dashboard.html.tt> with the same stash shape as C<open_file>,
 1089: plus C<source_url> (the original URL) for C<localStorage> tracking.
 1090: 
 1091: On error re-renders C<home.html.tt> with an C<error> stash variable.
 1092: 
 1093: =head3 MESSAGES
 1094: 
 1095:   error_url_required  -- empty or missing C<url> param
 1096:   error_url_invalid   -- URL does not begin with http:// or https://
 1097:   error_url_fetch     -- LWP fetch failure or no table found at the index
 1098: 
 1099: =head3 EXAMPLE
 1100: 
 1101:   GET /import?url=https://matrix.perl-magpie.org/dist/Database-Abstraction/
 1102:   GET /import?url=https://example.com/data.html&table_index=2
 1103: 
 1104: =cut
 1105: 
 1106: sub import_url ($self) {
 1107: 	my ($platform, $language) = $self->_resolve_template;
 1108: 
 1109: 	my $url = $self->param('url') // '';
 1110: 	my $idx = $self->param('table_index') // 0;
 1111: 	$idx    = 0 unless $idx =~ /\A[0-9]+\z/;
 1112: 
 1113: 	my $err_home = sub ($key, @args) {
 1114: 		$self->render(
 1115: 			template => "$platform/$language/home",
 1116: 			handler  => 'tt',
 1117: 			format   => 'html',
 1118: 			tables   => $self->_scan_data_dir,
 1119: 			title    => 'Choose a Database',
 1120: 			error    => $self->_i18n($key, @args),
 1121: 		);
 1122: 	};
 1123: 
 1124: 	return $err_home->('error_url_required') unless length $url;
 1125: 	return $err_home->('error_url_invalid', $url)
 1126: 		unless $url =~ m{\Ahttps?://}i;
 1127: 	return $err_home->('error_url_ssrf', $url)
 1128: 		unless _is_safe_url($url);
 1129: 
 1130: 	my $source = eval { $self->open_table('', url => $url, html_table_index => $idx) };
 1131: 	return $err_home->('error_url_fetch', $url, $@ // 'unknown error') if $@ || !$source;
 1132: 
 1133: 	my $records = eval { $source->fetch_all };
 1134: 	return $err_home->('error_url_fetch', $url, $@ // 'empty result') if $@;
 1135: 
 1136: 	my $label   = $source->table_name;
 1137: 	my $lspec   = "url:$url";
 1138: 	my @columns = _get_columns($source, $records);
 1139: 	my ($filtered, $filter_specs, $filters_json) = $self->_apply_filters($records);
 1140: 
 1141: 	$self->render(

  Mutants (Total: 1, Killed: 1, Survived: 0)
 1142: 		template         => "$platform/$language/dashboard",
 1143: 		handler          => 'tt',

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1144: 		format           => 'html',
 1145: 		records          => $filtered,
 1146: 		columns          => \@columns,
 1147: 		table            => $label,
 1148: 		title            => $label,
 1149: 		source_url       => $url,

  Mutants (Total: 1, Killed: 1, Survived: 0)
 1150: 		back_url         => '/',
 1151: 		back_label       => 'Choose another database',
 1152: 		left_spec        => $lspec,
 1153: 		combine_specs    => [],
 1154: 		current_joins    => [],
 1155: 		available_tables => $self->_scan_data_dir,
 1156: 		join_summaries   => [],

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1157: 		filter_specs     => $filter_specs,
 1158: 		filters_json     => $filters_json,
 1159: 		export_url       => $self->_build_export_url($lspec, [], $filter_specs),
 1160: 	);
 1161: }
 1162: 
 1163: =head2 columns_api
 1164: 
 1165: C<GET /api/columns> -- Return column names for a table or file as JSON.
 1166: 
 1167: Used by the join UI to populate the right-key dropdown without a page reload.
 1168: 
 1169: =head3 API SPECIFICATION
 1170: 
 1171: =head4 INPUT
 1172: 
 1173:   ?table=  string   Table name from C<data_dir>.
 1174:   ?path=   string   Absolute path to a data file.
 1175: 
 1176: One of C<table> or C<path> must be present; if both are, C<table> takes
 1177: precedence.
 1178: 
 1179: =head4 OUTPUT
 1180: 
 1181:   200 application/json   { "columns": ["col1", "col2", ...] }
 1182:   404 application/json   { "error": "not found" }
 1183: 
 1184: =head3 MESSAGES
 1185: 
 1186: None produced by this action directly; internal errors yield a 404.
 1187: 
 1188: =head3 FORMAL SPECIFICATION
 1189: 
 1190:   columns_api == lambda self .
 1191:     let src = open_spec(table_param or path_param) in
 1192:     pre  src /= undef
 1193:     post render_json({ columns: get_columns(src) })
 1194: 
 1195: =head3 EXAMPLE
 1196: 
 1197:   GET /api/columns?table=sales  -> {"columns":["product","region","amount"]}
 1198:   GET /api/columns?table=noexist -> 404
 1199: 
 1200: =cut
 1201: 
 1202: sub columns_api ($self) {
●1203 → 1207 → 1222 1203: 	my $table_name = $self->param('table');
 1204: 	my $path       = $self->param('path');
 1205: 
 1206: 	my $source;
 1207: 	if (defined $table_name && $table_name =~ $TABLE_NAME_RE) {
 1208: 		my $data_dir = $self->app->home->child($self->app->config->{data_dir} // 'data');
 1209: 		return $self->render(json => { error => 'not found' }, status => 404)
 1210: 			unless grep { -f $data_dir->child(lc($table_name) . ".$_") } @SUPPORTED_EXT;
 1211: 		$source = eval { $self->open_table(lc $table_name, directory => $data_dir->to_string) };
 1212: 	}
 1213: 	elsif (defined $path) {
 1214: 		my $file = eval { Mojo::File->new($path)->realpath };
 1215: 		if (defined $file && -f $file && $file->basename =~ $EXT_RE) {
 1216: 			my $dir = $file->dirname->to_string;
 1217: 			(my $tbl = $file->basename) =~ s/\.[^.]+\z//;
 1218: 			$source = eval { $self->open_table(lc $tbl, directory => $dir) };
 1219: 		}
 1220: 	}
 1221: 

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1222: 	return $self->render(json => { error => 'not found' }, status => 404) unless $source;
 1223: 
 1224: 	# Use _get_columns to ensure consistent ordering with the view action.

  Mutants (Total: 1, Killed: 1, Survived: 0)
 1225: 	my $recs = ($source->columns ? [] : eval { $source->fetch_all } // []);

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1226: 	my @cols = _get_columns($source, $recs);
 1227: 
 1228: 	$self->render(json => { columns => \@cols });
 1229: }
 1230: 
 1231: =head2 join_tables
 1232: 
 1233: C<GET /join> -- Perform one or more left joins and render the merged table.
 1234: 
 1235: =head3 API SPECIFICATION
 1236: 
 1237: =head4 INPUT
 1238: 
 1239:   ?l=    string   Required. Left table spec: "table:name" or "path:/abs/path".
 1240:                   Returns 404 if unresolvable.
 1241:   ?j=    string   (repeatable) Join step: "<right-spec>|<left-key>|<right-key>".
 1242:                   Invalid or non-existent steps are silently skipped.
 1243:   ?f=    string   (repeatable) Result filter: "col:op:val".
 1244: 
 1245: =head4 OUTPUT
 1246: 
 1247: Renders C<dashboard.html.tt> with the merged/filtered result set.
 1248: 
 1249: =head3 MESSAGES
 1250: 
 1251:   error_table_open   -- Left table fetch threw (re-renders home with error).
 1252: 
 1253: =head3 FORMAL SPECIFICATION
 1254: 
 1255:   join_tables == lambda self .
 1256:     let left = open_spec(param('l')) in
 1257:     pre  left /= undef
 1258:     let joined  = fold(_left_join, left, param_list('j')) in
 1259:     let filtered = fold(_apply_filter_spec, joined, param_list('f')) in
 1260:     post render(dashboard, records: filtered)
 1261: 
 1262: =head3 PSEUDOCODE
 1263: 
 1264:   1. Parse and open left table spec; 404 on failure.
 1265:   2. For each j= param (in order):
 1266:        a. Parse right-spec, left-key, right-key from "|"-split.
 1267:        b. Verify left-key exists in current column list.
 1268:        c. Open right table; skip step on any error.
 1269:        d. Verify right-key exists in right column list.
 1270:        e. Call _left_join; update records and columns.
 1271:   3. Apply all f= filters via _apply_filter_spec.
 1272:   4. Build stable table key for localStorage ("join:left:right1:...").
 1273:   5. Render dashboard.
 1274: 
 1275: =head3 EXAMPLE
 1276: 
 1277:   GET /join?l=table:sales&j=table:products|product|name
 1278:     -> merged left join of sales and products on product/name columns
 1279: 
 1280: =cut
 1281: 
 1282: sub join_tables ($self) {
●1283 → 1290 → 1300 1283: 	my ($platform, $language) = $self->_resolve_template;
 1284: 
 1285: 	my $left_spec = $self->param('l') // '';
 1286: 	my ($left_src, $left_label) = $self->_open_spec($left_spec);
 1287: 	return $self->reply->not_found unless $left_src;
 1288: 
 1289: 	my $left_recs = eval { $left_src->fetch_all };
 1290: 	if ($@) {
 1291: 		return $self->render(
 1292: 			template => "$platform/$language/home",
 1293: 			handler  => 'tt',
 1294: 			format   => 'html',
 1295: 			tables   => [],
 1296: 			title    => 'Error',
 1297: 			error    => $self->_i18n('error_table_open', $left_label, $@),
 1298: 		);
 1299: 	}
●1300 → 1307 → 1332 1300: 	$left_recs //= [];
 1301: 
 1302: 	my @left_cols = _get_columns($left_src, $left_recs);
 1303: 	my @join_specs = @{ $self->every_param('j') };
 1304: 
 1305: 	my ($records, @columns, @summaries) = ($left_recs, @left_cols);
 1306: 
 1307: 	for my $jspec (@join_specs) {
 1308: 		my ($right_spec, $left_key, $right_key) = split /\|/, $jspec, 3;
 1309: 		next unless defined $right_spec && defined $left_key && defined $right_key;
 1310: 
 1311: 		# O(1) hash-set probe instead of O(C) grep for each join step.
 1312: 		my %col_set = map { $_ => 1 } @columns;
 1313: 		next unless $col_set{$left_key};
 1314: 
 1315: 		my ($right_src, $right_label) = $self->_open_spec($right_spec);
 1316: 		next unless $right_src;
 1317: 
 1318: 		my $right_recs = eval { $right_src->fetch_all } // [];
 1319: 		next if $@;
 1320: 		my @right_cols = _get_columns($right_src, $right_recs);
 1321: 		my %right_set  = map { $_ => 1 } @right_cols;
 1322: 		next unless $right_set{$right_key};
 1323: 
 1324: 		($records, my $new_cols) = _left_join(
 1325: 			$records, \@columns, $left_key,
 1326: 			$right_recs, \@right_cols, $right_key, $right_label,
 1327: 		);
 1328: 		@columns = @$new_cols;
 1329: 		push @summaries, { label => $right_label, left_key => $left_key, right_key => $right_key };
 1330: 	}
 1331: 
 1332: 	my ($filtered, $filter_specs, $filters_json) = $self->_apply_filters($records);
 1333: 
 1334: 	my $title     = $left_label;
 1335: 	$title       .= ' + ' . join(' + ', map { $_->{label} } @summaries) if @summaries;
 1336: 	my $table_key = 'join:' . lc($left_label);
 1337: 	$table_key   .= ':' . lc($_->{label}) for @summaries;
 1338: 
 1339: 	$self->render(
 1340: 		template         => "$platform/$language/dashboard",
 1341: 		handler          => 'tt',
 1342: 		format           => 'html',
 1343: 		records          => $filtered,
 1344: 		columns          => \@columns,
 1345: 		table            => $table_key,
 1346: 		title            => $title,
 1347: 		back_url         => $self->_spec_to_url($left_spec),
 1348: 		back_label       => "Back to $left_label",
 1349: 		left_spec        => $left_spec,
 1350: 		combine_specs    => [],
 1351: 		current_joins    => \@join_specs,

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1352: 		available_tables => $self->_scan_data_dir,
 1353: 		join_summaries   => \@summaries,
 1354: 		filter_specs     => $filter_specs,
 1355: 		filters_json     => $filters_json,
 1356: 		export_url       => $self->_build_export_url($left_spec, \@join_specs, $filter_specs),
 1357: 	);

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1358: }
 1359: 
 1360: =head2 combine_tables
 1361: 
 1362: C<GET /combine> -- Stack rows from two or more tables vertically into a single
 1363: unified view.
 1364: 
 1365: Unlike C</join> (which appends columns from a matching row in a second table),
 1366: C</combine> appends the I<rows> from a second table beneath the rows of the
 1367: first.  All columns from all sources appear as headers; where a row has no
 1368: value for a particular column (because that column did not exist in its source
 1369: file) the cell is left blank.
 1370: 
 1371: This is equivalent to a SQL C<UNION ALL> across heterogeneous schemas.
 1372: 
 1373: =head3 API SPECIFICATION
 1374: 
 1375: =head4 INPUT
 1376: 
 1377:   ?l=    string   (required) Left table spec: "table:name" or "path:/abs/path".
 1378:                   Returns 404 if unresolvable.
 1379:   ?c=    string   (repeatable) Additional table spec to stack beneath the
 1380:                   current result.  Invalid or non-existent specs are silently
 1381:                   skipped.
 1382:   ?f=    string   (repeatable) Result filter: "col:op:val".
 1383: 
 1384: =head4 OUTPUT
 1385: 
 1386: Renders C<dashboard.html.tt> with the combined result set.
 1387: 
 1388: =head3 MESSAGES
 1389: 
 1390:   error_table_open   -- Left table fetch threw (re-renders home with error).
 1391: 
 1392: =head3 FORMAL SPECIFICATION
 1393: 
 1394:   combine_tables == lambda self .
 1395:     let left = open_spec(param('l')) in
 1396:     pre  left /= undef
 1397:     let sources  = [left] ++ map(open_spec, param_list('c')) in
 1398:     let combined = _combine_tables(sources) in
 1399:     let filtered = fold(_apply_filter_spec, combined, param_list('f')) in
 1400:     post render(dashboard, records: filtered)
 1401: 
 1402: =head3 EXAMPLE
 1403: 
 1404:   GET /combine?l=table:cats&c=table:dogs
 1405:     -> unified view: all cat and dog rows; Species/Name/Color/Breed/Eye Color
 1406:        appear for both; Environment (cats-only) and Sex/Fixed (dogs-only)
 1407:        are blank where the source file lacks that column.
 1408: 
 1409: =cut
 1410: 
 1411: sub combine_tables ($self) {
●1412 → 1419 → 1429 1412: 	my ($platform, $language) = $self->_resolve_template;
 1413: 
 1414: 	my $left_spec = $self->param('l') // '';
 1415: 	my ($left_src, $left_label) = $self->_open_spec($left_spec);
 1416: 	return $self->reply->not_found unless $left_src;
 1417: 
 1418: 	my $left_recs = eval { $left_src->fetch_all };
 1419: 	if ($@) {
 1420: 		return $self->render(
 1421: 			template => "$platform/$language/home",
 1422: 			handler  => 'tt',
 1423: 			format   => 'html',
 1424: 			tables   => [],
 1425: 			title    => 'Error',
 1426: 			error    => $self->_i18n('error_table_open', $left_label, $@),
 1427: 		);
 1428: 	}
●1429 → 1437 → 1447 1429: 	$left_recs //= [];
 1430: 
 1431: 	my @left_cols     = _get_columns($left_src, $left_recs);
 1432: 	my @combine_specs = @{ $self->every_param('c') };
 1433: 
 1434: 	my @sources   = ([$left_recs, \@left_cols]);
 1435: 	my @summaries;
 1436: 

  Mutants (Total: 1, Killed: 1, Survived: 0)
 1437: 	for my $cspec (@combine_specs) {

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1438: 		my ($csrc, $clabel) = $self->_open_spec($cspec);
 1439: 		next unless $csrc;
 1440: 		my $crecs = eval { $csrc->fetch_all } // [];
 1441: 		next if $@;
 1442: 		my @ccols = _get_columns($csrc, $crecs);
 1443: 		push @sources,   [$crecs, \@ccols];
 1444: 		push @summaries, { label => $clabel };
 1445: 	}
 1446: 

  Mutants (Total: 1, Killed: 1, Survived: 0)
 1447: 	my ($records, $cols_ref) = @sources > 1
 1448: 		? _combine_tables(\@sources)
 1449: 		: ($left_recs, \@left_cols);

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1450: 	my @columns = @$cols_ref;
 1451: 
 1452: 	my ($filtered, $filter_specs, $filters_json) = $self->_apply_filters($records);
 1453: 
 1454: 	my $title     = $left_label;
 1455: 	$title       .= ' + ' . join(' + ', map { $_->{label} } @summaries) if @summaries;
 1456: 	my $table_key = 'combine:' . lc($left_label);

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1457: 	$table_key   .= ':' . lc($_->{label}) for @summaries;
 1458: 
 1459: 	$self->render(
 1460: 		template         => "$platform/$language/dashboard",
 1461: 		handler          => 'tt',

					

					Mutants (Total: 1, Killed: 0, Survived: 1)
					

 1462: 		format           => 'html',
 1463: 		records          => $filtered,
 1464: 		columns          => \@columns,
 1465: 		table            => $table_key,
 1466: 		title            => $title,
 1467: 		back_url         => $self->_spec_to_url($left_spec),
 1468: 		back_label       => "Back to $left_label",

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1469: 		left_spec        => $left_spec,
 1470: 		combine_specs    => \@combine_specs,
 1471: 		current_joins    => [],
 1472: 		available_tables => $self->_scan_data_dir,
 1473: 		join_summaries   => \@summaries,
 1474: 		filter_specs     => $filter_specs,
 1475: 		filters_json     => $filters_json,
 1476: 		export_url       => $self->_build_export_url($left_spec, [], $filter_specs, \@combine_specs),
 1477: 	);
 1478: }
 1479: 
 1480: =head2 export_data
 1481: 
 1482: C<GET /export> -- Stream the current logical view as a browser file download.
 1483: 
 1484: =head3 API SPECIFICATION
 1485: 
 1486: =head4 INPUT
 1487: 
 1488:   ?l=        string   (required) Left table spec.
 1489:   ?j=        string   (repeatable) Join steps.
 1490:   ?f=        string   (repeatable) Filter specs.
 1491:   ?format=   string   "csv" (default) or "sqlite".
 1492: 
 1493: =head4 DOMAIN CONSTRAINTS: ?format=
 1494: 
 1495: The comparison is C<$format eq 'sqlite'> -- case-sensitive, exact match.
 1496: 
 1497: =over 4
 1498: 
 1499: =item Valid partitions
 1500: 
 1501: C<csv> (explicit CSV), C<sqlite> (exact lowercase, SQLite binary),
 1502: absent/undef (defaults to CSV).
 1503: 
 1504: =item Invalid partitions (all fall back to CSV)
 1505: 
 1506: C<SQLITE> (uppercase, not equal to C<'sqlite'>), C<Sqlite> (mixed
 1507: case), C<sqlit> (truncated), C<sqlite1> (extra character), C<json>
 1508: (unknown format).
 1509: 
 1510: =back
 1511: 
 1512: =head4 OUTPUT
 1513: 
 1514:   200 text/csv                  or application/vnd.sqlite3
 1515:   404 application/json          { "error": "Table not found" }
 1516: 
 1517: =head3 MESSAGES
 1518: 
 1519: None beyond the 404 response.

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1520: 
 1521: =head3 FORMAL SPECIFICATION
 1522: 
 1523:   export_data == lambda self .
 1524:     let (recs, cols, label) = _run_export_pipeline() in
 1525:     pre  recs /= undef
 1526:     post if format = 'sqlite' then render_sqlite(recs, cols)
 1527:          else render_csv(recs, cols)
 1528: 
 1529: =head3 EXAMPLE
 1530: 
 1531:   GET /export?l=table:sales&format=csv     -> downloads sales.csv
 1532:   GET /export?l=table:sales&format=sqlite  -> downloads sales.db
 1533: 
 1534: =cut
 1535: 
 1536: sub export_data ($self) {
 1537: 	my ($records, $columns, $left_label) = $self->_run_export_pipeline;
 1538: 	return $self->reply->not_found unless $records;
 1539: 
 1540: 	my $format = $self->param('format') // 'csv';
 1541: 	$format    = 'csv' unless $format eq 'sqlite';
 1542: 	(my $safe_name = lc $left_label) =~ s/[^a-z0-9_]+/_/g;
 1543: 
 1544: 	return $format eq 'sqlite'
 1545: 		? $self->_render_sqlite($records, $columns, $safe_name)
 1546: 		: $self->_render_csv($records, $columns, $safe_name);
 1547: }
 1548: 
 1549: =head2 export_write
 1550: 
 1551: C<POST /export> -- Write the current logical view to a chosen filesystem path.
 1552: 
 1553: =head3 API SPECIFICATION
 1554: 
 1555: =head4 INPUT
 1556: 
 1557:   l=         string   (required) Left table spec.
 1558:   j=         string   (repeatable) Join steps.
 1559:   f=         string   (repeatable) Filter specs.
 1560:   dir=       string   Target directory (must exist; resolved via realpath).
 1561:   filename=  string   Output filename including extension.  Extension determines
 1562:                       format: C<.csv> -> RFC 4180 CSV; C<.sql> -> SQLite.
 1563: 
 1564: =head4 DOMAIN CONSTRAINTS: filename=
 1565: 
 1566: The extension check uses C</\.csv\z/i> (CSV) or C</\.sql\z/i> (SQLite):
 1567: the C</i> flag makes matching case-insensitive, so C<.CSV> and C<.SQL>
 1568: are accepted alongside lowercase forms.  Everything else returns 415.
 1569: 
 1570: Path separator characters (C</> and C<\>) in C<filename> are stripped
 1571: first via C<m{([^/\\]+)\z}> -- only the basename is kept, preventing
 1572: directory traversal.
 1573: 
 1574: =over 4
 1575: 
 1576: =item Valid partitions
 1577: 
 1578: C<report.csv>, C<report.sql>, C<REPORT.CSV>, C<report.SQL>.  A
 1579: single-character stem (C<a.csv>) is also valid.
 1580: 
 1581: =item Invalid partitions (415)
 1582: 
 1583: C<report.txt>, C<report.json>, C<report> (no extension), empty string.
 1584: 
 1585: =back

  Mutants (Total: 1, Killed: 1, Survived: 0)
 1586: 

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1587: =head4 OUTPUT
 1588: 
 1589:   200 application/json   { "saved": "/abs/path/to/file" }
 1590:   404 application/json   { "error": "..." }  -- bad dir or table not found
 1591:   415 application/json   { "error": "..." }  -- unsupported extension
 1592:   500 application/json   { "error": "..." }  -- write failure
 1593: 
 1594: =head3 MESSAGES
 1595: 

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1596:   error_dir_not_found   -- realpath of dir failed or result is not a directory.
 1597:   error_ext_required    -- filename extension is not .csv or .sql.
 1598:   error_write_failed    -- DBI or filesystem write threw.
 1599: 
 1600: =head3 FORMAL SPECIFICATION
 1601: 
 1602:   export_write == lambda self .
 1603:     let dir  = realpath(param('dir'))      in
 1604:     let file = dir / strip_path(param('filename')) in
 1605:     pre  is_dir(dir) /\ ext(file) in {csv, sql}
 1606:     let (recs, cols, _) = _run_export_pipeline() in
 1607:     pre  recs /= undef
 1608:     post spurt(file, serialise(recs, cols))
 1609:          /\ render_json({ saved: file.to_string })
 1610: 
 1611: =head3 EXAMPLE
 1612: 
 1613:   POST /export  l=table:sales dir=/home/user/exports filename=report.csv
 1614:     -> { "saved": "/home/user/exports/report.csv" }
 1615: 
 1616: =cut
 1617: 
 1618: sub export_write ($self) {
●1619 → 1623 → 1628 1619: 	my $dir      = $self->param('dir')      // '';
 1620: 	my $filename = $self->param('filename') // '';
 1621: 
 1622: 	my $dest_dir = eval { Mojo::File->new($dir)->realpath };
 1623: 	unless (defined $dest_dir && -d $dest_dir) {
 1624: 		return $self->render(
 1625: 			json   => { error => $self->_i18n('error_dir_not_found', $dir) },
 1626: 			status => 404,
 1627: 		);
●1628 → 1633 → 1642 1628: 	}
 1629: 
 1630: 	# Strip any path separators the browser might include.
 1631: 	($filename) = $filename =~ m{([^/\\]+)\z};
 1632: 	my $format;
 1633: 	if    ($filename && $filename =~ /\.csv\z/i) { $format = 'csv';    }
 1634: 	elsif ($filename && $filename =~ /\.sql\z/i) { $format = 'sqlite'; }
 1635: 	else {
 1636: 		return $self->render(
 1637: 			json   => { error => $self->_i18n('error_ext_required') },
 1638: 			status => 415,
 1639: 		);
 1640: 	}
 1641: 
 1642: 	my ($records, $columns, $left_label) = $self->_run_export_pipeline;
 1643: 	return $self->render(json => { error => 'Table not found' }, status => 404)
 1644: 		unless $records;
 1645: 
 1646: 	my $dest = $dest_dir->child($filename);
 1647: 	eval {
 1648: 		if ($format eq 'csv') {
 1649: 			$dest->spurt(_serialize_csv($records, $columns));
 1650: 		}
 1651: 		else {
 1652: 			$dest->spurt($self->_write_sqlite_db($records, $columns));

  Mutants (Total: 1, Killed: 1, Survived: 0)
 1653: 		}

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1654: 	};
 1655: 	return $self->render(
 1656: 		json   => { error => $self->_i18n('error_write_failed', $@) },
 1657: 		status => 500,
 1658: 	) if $@;
 1659: 
 1660: 	$self->render(json => { saved => $dest->to_string });
 1661: }
 1662: 
 1663: =head2 dirs_api
 1664: 

  Mutants (Total: 4, Killed: 4, Survived: 0)
 1665: C<GET /api/dirs> -- Return a JSON directory listing for the export panel.

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1666: 
 1667: =head3 API SPECIFICATION
 1668: 
 1669: =head4 INPUT
 1670: 
 1671:   ?path=   string   Directory to list (default: $HOME).  Returns 404 when not
 1672:                     a directory.  Hidden entries (names starting with ".") are
 1673:                     excluded.
 1674: 

  Mutants (Total: 1, Killed: 1, Survived: 0)
 1675: =head4 OUTPUT

					

					Mutants (Total: 2, Killed: 0, Survived: 2)
					

 1676: 
 1677:   200 application/json
 1678:     {
 1679:       "path":   "/abs/path",
 1680:       "parent": "/abs/parent" or null (at filesystem root),
 1681:       "dirs":   [ { "name": "alpha", "path": "/abs/path/alpha" }, ... ]
 1682:     }
 1683: 
 1684:   404 application/json   { "error": "Not a directory" }
 1685: 
 1686: =head3 MESSAGES
 1687: 
 1688: None beyond the 404 response.
 1689: 
 1690: =head3 FORMAL SPECIFICATION
 1691: 
 1692:   dirs_api == lambda self .
 1693:     let dir = realpath(param('path') // HOME) in
 1694:     pre  is_dir(dir)
 1695:     post render_json({ path: dir, parent: parent(dir), dirs: subdirs(dir) })
 1696: 
 1697: =head3 EXAMPLE
 1698: 
 1699:   GET /api/dirs?path=/home/user  -> {"path":"/home/user","parent":"/home","dirs":[...]}
 1700: 
 1701: =cut
 1702: 
 1703: sub dirs_api ($self) {
 1704: 	my $raw = $self->param('path') // $ENV{HOME} // '/';
 1705: 	my $dir = eval { Mojo::File->new($raw)->realpath };
 1706: 	return $self->render(json => { error => 'Not a directory' }, status => 404)
 1707: 		unless defined $dir && -d $dir;
 1708: 
 1709: 	my $listing = $self->_list_dir($dir, 0);
 1710: 	my $parent  = $dir->dirname;
 1711: 
 1712: 	$self->render(json => {
 1713: 		path   => $dir->to_string,
 1714: 		parent => ($parent->to_string ne $dir->to_string ? $parent->to_string : undef),
 1715: 		dirs   => $listing->{dirs},
 1716: 	});
 1717: }
 1718: 
 1719: =head2 stat_api
 1720: 
 1721: C<GET /api/stat> -- Return filesystem metadata for a file path.
 1722: 
 1723: Used by the home page tooltip to show modification time and size for recently
 1724: opened files without a page reload.
 1725: 
 1726: =head3 API SPECIFICATION
 1727: 
 1728: =head4 INPUT
 1729: 
 1730:   ?path=   string   Absolute path to query.  Returns HTTP 400 when absent.
 1731: 
 1732: =head4 OUTPUT
 1733: 
 1734:   200 application/json   (file exists)
 1735:     { "exists": true, "path": "/resolved/path", "mtime": 1700000000, "size": 4096 }
 1736: 
 1737:   200 application/json   (file does not exist)
 1738:     { "exists": false, "path": "/original/path" }
 1739: 
 1740:   400 application/json   { "error": "\"path\" parameter is required" }
 1741: 
 1742: C<mtime> is Unix epoch seconds.  C<size> is bytes.  A missing/unresolvable
 1743: path returns HTTP 200 with C<exists: false> (not 404) so the UI can
 1744: distinguish "file was deleted" from a request error.
 1745: 
 1746: =head3 MESSAGES
 1747: 
 1748:   error_path_required   -- the "path" query parameter was not supplied.
 1749: 
 1750: =head3 FORMAL SPECIFICATION
 1751: 
 1752:   stat_api == lambda self .
 1753:     let path = param('path') in
 1754:     pre  path /= undef
 1755:     let file = realpath(path) in
 1756:     post if is_file(file) then
 1757:            render_json({ exists: true, mtime: mtime(file), size: size(file) })
 1758:          else render_json({ exists: false })
 1759: 
 1760: =head3 EXAMPLE
 1761: 
 1762:   GET /api/stat?path=/data/sales.csv
 1763:     -> { "exists": true, "path": "/data/sales.csv", "mtime": 1700000000, "size": 1234 }
 1764: 
 1765:   GET /api/stat?path=/deleted.csv
 1766:     -> { "exists": false, "path": "/deleted.csv" }
 1767: 
 1768: =cut
 1769: 
 1770: sub stat_api ($self) {
●1771 → 1772 → 1779 1771: 	my $path = $self->param('path');
 1772: 	unless (defined $path && length $path) {
 1773: 		return $self->render(
 1774: 			json   => { error => $self->_i18n('error_path_required') },
 1775: 			status => 400,
 1776: 		);
 1777: 	}
 1778: 
 1779: 	my $file = eval { Mojo::File->new($path)->realpath };
 1780: 	# Restrict to files with a supported data extension so stat_api cannot be
 1781: 	# used as a filesystem oracle to probe /etc/shadow, /root/.ssh/, etc.
 1782: 	return $self->render(json => { exists => \0, path => $path })
 1783: 		unless defined $file && -f $file && $file->basename =~ $EXT_RE;
 1784: 
 1785: 	my @s = stat $file->to_string;
 1786: 	$self->render(json => {
 1787: 		exists => \1,
 1788: 		path   => $file->to_string,
 1789: 		mtime  => $s[9],
 1790: 		size   => $s[7],
 1791: 	});
 1792: }
 1793: 
 1794: =head2 upload_file
 1795: 
 1796: C<POST /upload> -- Accept a drag-and-dropped data file and return a redirect URL.
 1797: 
 1798: The file is saved under its original filename in a managed subdirectory of
 1799: C<< <app_home>/.uploads/ >>.  The subdirectory name is randomised so that
 1800: concurrent uploads of files with the same name do not collide.
 1801: 
 1802: =head3 API SPECIFICATION
 1803: 
 1804: =head4 INPUT
 1805: 
 1806: Multipart form upload, field name: C<file>.
 1807: 
 1808:   file   upload   Supported extensions: csv, db, sql, xml, psv.
 1809: 
 1810: =head4 OUTPUT
 1811: 
 1812:   200 application/json   { "url": "/open?path=/abs/path/file.csv", "path": "/abs/path/file.csv" }
 1813:   400 application/json   { "error": "No file received" }
 1814:   415 application/json   { "error": "Unsupported file type. Accepted: ..." }
 1815: 
 1816: =head3 MESSAGES
 1817: 
 1818:   error_upload_none   -- no file was received in the multipart upload.
 1819:   error_upload_ext    -- the file's extension is not in the supported list.
 1820: 
 1821: =head3 FORMAL SPECIFICATION
 1822: 
 1823:   upload_file == lambda self .
 1824:     let upload = req.upload('file') in
 1825:     pre  upload /= undef /\ basename(upload.filename) =~ EXT_RE
 1826:     let dest = home/.uploads/<random>/<filename> in
 1827:     post upload.move_to(dest)
 1828:          /\ render_json({ url: '/open?path=' ++ url_escape(dest), path: dest })
 1829: 
 1830: =head3 EXAMPLE
 1831: 
 1832:   POST /upload  (multipart: file=@sales.csv)
 1833:     -> { "url": "/open?path=%2F...%2Fsales.csv", "path": "/.../.uploads/abc123/sales.csv" }
 1834: 
 1835: =cut
 1836: 
 1837: sub upload_file ($self) {
●1838 → 1839 → 1851 1838: 	my $upload = $self->req->upload('file');
 1839: 	unless ($upload && $upload->filename) {
 1840: 		return $self->render(
 1841: 			json   => { error => $self->_i18n('error_upload_none') },
 1842: 			status => 400,
 1843: 		);
 1844: 	}
 1845: 
 1846: 	# Enforce upload size limit.  max_request_size in startup() sets the
 1847: 	# transport-layer cap, but Mojolicious still calls the controller when the
 1848: 	# limit fires (with req->is_limit_exceeded true and partial content in the
 1849: 	# upload asset).  Check is_limit_exceeded first; fall through to an asset
 1850: 	# size check as a belt-and-braces guard for any bytes that slipped through.
●1851 → 1851 → 1856 1851: 	if ($self->req->is_limit_exceeded || $upload->size > $MAX_UPLOAD_BYTES) {
 1852: 		return $self->render(
 1853: 			json   => { error => $self->_i18n('error_upload_too_large', $MAX_UPLOAD_MIB) },
 1854: 			status => 413,
 1855: 		);
●1856 → 1861 → 1872 1856: 	}
 1857: 
 1858: 	# /s so .* matches \n; a percent-decoded newline inside a browser filename
 1859: 	# must not silently truncate the directory-strip, leaving "dir\ncomponent" in.
 1860: 	(my $filename = $upload->filename) =~ s{.*[/\\]}{}s;
 1861: 	unless ($filename && $filename =~ $EXT_RE) {
 1862: 		return $self->render(
 1863: 			json   => { error => $self->_i18n('error_upload_ext') },
 1864: 			status => 415,
 1865: 		);
 1866: 	}
 1867: 
 1868: 	# Store uploads in <app_home>/.uploads/<random_subdir>/<original_filename>.
 1869: 	# Using the original filename is essential: Database::Abstraction derives
 1870: 	# the table name from the file stem, so "sales.csv" must stay "sales.csv".
 1871: 	# The random subdirectory prevents collisions from concurrent same-name uploads.
 1872: 	my $uploads_base = $self->app->home->child('.uploads');
 1873: 	$uploads_base->make_path unless -d $uploads_base;
 1874: 	my $sub_dir = tempdir(DIR => $uploads_base->to_string, CLEANUP => 0);
 1875: 	my $dest    = Mojo::File->new($sub_dir)->child($filename)->to_string;
 1876: 	$upload->move_to($dest);
 1877: 
 1878: 	$self->render(json => {
 1879: 		url  => '/open?path=' . url_escape($dest),
 1880: 		path => $dest,
 1881: 	});
 1882: }
 1883: 
 1884: 1;
 1885: 
 1886: __END__
 1887: 
 1888: =head1 NAME
 1889: 
 1890: Database::BI::Controller::Dashboard - Home picker, filesystem browser, table
 1891: viewer, left-join engine, result filter, export, and file upload
 1892: 
 1893: =head1 SYNOPSIS
 1894: 
 1895: All routes in C<Database::BI> are handled by this controller.  You do not
 1896: call its methods directly -- Mojolicious dispatches HTTP requests to them
 1897: automatically.  The examples below show browser URLs and their curl
 1898: equivalents.
 1899: 
 1900: B<Open the home page and see all tables in the data directory:>
 1901: 
 1902:   # Browser
 1903:   http://localhost:3000/
 1904: 
 1905:   # curl
 1906:   curl http://localhost:3000/
 1907: 
 1908: B<View a single table (file: data/sales.csv):>
 1909: 
 1910:   # Browser
 1911:   http://localhost:3000/view/sales
 1912: 
 1913:   # curl
 1914:   curl http://localhost:3000/view/sales
 1915: 
 1916: B<Filter rows -- show only rows where region equals "North":>
 1917: 
 1918:   # Browser (add ?f=col:op:val to any view URL)
 1919:   http://localhost:3000/view/sales?f=region:eq:North
 1920: 
 1921:   # Multiple filters -- "North" AND amount greater than 100:
 1922:   http://localhost:3000/view/sales?f=region:eq:North&f=amount:gt:100
 1923: 
 1924:   # curl
 1925:   curl 'http://localhost:3000/view/sales?f=region:eq:North'
 1926: 
 1927: B<Join two tables -- left-join sales with products on the product column:>
 1928: 
 1929:   # Browser
 1930:   http://localhost:3000/join?l=table:sales&j=table:products|product|name
 1931: 
 1932:   # The j= parameter is: right-table-spec | left-key | right-key
 1933:   # You can chain multiple joins:
 1934:   http://localhost:3000/join?l=table:sales&j=table:products|product|name&j=table:regions|region|id
 1935: 
 1936: B<Open a file anywhere on the filesystem (not just in data/):>
 1937: 
 1938:   http://localhost:3000/open?path=/home/user/reports/q3.csv
 1939: 
 1940: B<Download the current view as a CSV file:>
 1941: 
 1942:   # Uses the same l=, j=, f= parameters as /join
 1943:   http://localhost:3000/export?l=table:sales&format=csv
 1944: 
 1945:   # Download as a SQLite database file instead:
 1946:   http://localhost:3000/export?l=table:sales&format=sqlite
 1947: 
 1948:   # Download a filtered + joined result:
 1949:   http://localhost:3000/export?l=table:sales&j=table:products|product|name&f=region:eq:North&format=csv
 1950: 
 1951: B<Save the current view to a file on the server (instead of downloading):>
 1952: 
 1953:   # POST with form fields; format is inferred from the filename extension
 1954:   curl -X POST http://localhost:3000/export \
 1955:        -F l=table:sales \
 1956:        -F dir=/home/user/exports \
 1957:        -F filename=report.csv
 1958: 
 1959:   # Save as SQLite:
 1960:   curl -X POST http://localhost:3000/export \
 1961:        -F l=table:sales \
 1962:        -F dir=/home/user/exports \
 1963:        -F filename=report.sql
 1964: 
 1965: B<Get the column list for a table (used by the join UI):>
 1966: 
 1967:   curl http://localhost:3000/api/columns?table=sales
 1968:   # Returns: {"columns":["product","region","amount","date"]}
 1969: 
 1970: B<Check when a file was last modified (used by the tooltip on the home page):>
 1971: 
 1972:   curl 'http://localhost:3000/api/stat?path=/data/sales.csv'
 1973:   # Returns: {"exists":true,"path":"/data/sales.csv","mtime":1700000000,"size":1234}
 1974: 
 1975: B<Browse the filesystem to find a data file:>
 1976: 
 1977:   http://localhost:3000/browse
 1978:   http://localhost:3000/browse?path=/home/user/data
 1979: 
 1980: B<Upload a data file by dropping it onto the page (multipart form POST):>
 1981: 
 1982:   curl -X POST http://localhost:3000/upload \
 1983:        -F file=@/home/user/data/sales.csv
 1984:   # Returns: {"url":"/open?path=/.../.uploads/.../sales.csv","path":"/.../.uploads/.../sales.csv"}
 1985: 
 1986: =head1 DESCRIPTION
 1987: 
 1988: All user-facing routes in C<Database::BI> are handled by this controller.
 1989: See the individual action POD above for per-endpoint documentation.
 1990: 
 1991: =head2 Filter operators
 1992: 
 1993: The C<f=col:op:val> filter spec supports:
 1994: 
 1995:   eq        case-insensitive string equality
 1996:   ne        case-insensitive string inequality
 1997:   contains  case-insensitive substring match
 1998:   starts    case-insensitive prefix match
 1999:   lt        numeric less-than
 2000:   le        numeric less-than-or-equal
 2001:   gt        numeric greater-than
 2002:   ge        numeric greater-than-or-equal
 2003:   empty     cell is undef or empty string (val ignored)
 2004:   notempty  cell is defined and non-empty (val ignored)
 2005: 
 2006: The colon separator is split with a limit of 3, so values may themselves
 2007: contain colons (e.g. C<f=sale_date:eq:2025-01-15>).
 2008: 
 2009: =head1 COMMON PITFALLS
 2010: 
 2011: These are the most common mistakes when working with this controller.
 2012: 
 2013: =over 4
 2014: 
 2015: =item B<SQLite files must use the .sql extension, not .sqlite>
 2016: 
 2017: C<Database::Abstraction> (the data-reading library) probes for a file called
 2018: C<tablename.sql> when it wants to open a SQLite database.  It does B<not>
 2019: look for C<.sqlite> or C<.db3>.  If your file is called C<inventory.sqlite>,
 2020: rename it to C<inventory.sql> or it will not appear in the file browser and
 2021: will return 404 when opened.
 2022: 
 2023: =item B<Filter values that contain a colon still work>
 2024: 
 2025: A date like C<2025-01-15> contains hyphens, not colons, so it is fine.  But
 2026: if your value itself contains a colon (for example, a time like C<14:30:00>),
 2027: the filter still works because the C<col:op:val> spec is split on the B<first
 2028: two> colons only -- the rest of the string becomes the value.
 2029: 
 2030:   # This correctly matches "14:30:00" in the start_time column:
 2031:   ?f=start_time:eq:14:30:00
 2032: 
 2033: =item B<Left join keeps only the FIRST matching right-table row>
 2034: 
 2035: When the right table has two rows with the same join key, only the first one
 2036: (in file order) is used.  The second is silently ignored.  If you need all
 2037: matches, consider pre-processing your data so join keys are unique.
 2038: 
 2039: =item B<Export format comes from the filename extension, not a Content-Type header>
 2040: 
 2041: When using C<POST /export> to save a file to disk, the format (CSV or SQLite)
 2042: is determined by the extension of the C<filename> parameter.  C<.csv> produces
 2043: a CSV file; C<.sql> produces a SQLite database.  Any other extension returns
 2044: HTTP 415 (Unsupported Media Type).  The C<Content-Type> request header is
 2045: ignored entirely.
 2046: 
 2047: =item B<Template Toolkit variables starting with underscore are silently dropped>
 2048: 
 2049: If you add a stash variable with a name starting with C<_> (for example,
 2050: C<_tmp> or C<_result>), Template Toolkit will silently produce an empty string
 2051: when the template tries to read it.  This is a TT quirk when C<TRIM =E<gt> 1>
 2052: is active.  Always use names that start with a letter.
 2053: 
 2054: =item B<_apply_filter_spec always passes all records through for unknown operators>
 2055: 
 2056: If you pass an operator that is not in the supported list (for example C<regex>
 2057: or C<like>), the filter is treated as a no-op and B<all rows are returned>.  No
 2058: error is produced.  This is intentional so that future operators can be added
 2059: without breaking existing clients that read a wider response.
 2060: 
 2061: =item B<Uploading a file does not clean up automatically>
 2062: 
 2063: Files uploaded via C<POST /upload> are stored in C<.uploads/> under the
 2064: application's home directory and are B<never deleted automatically>.  They
 2065: accumulate until you manually remove the C<.uploads/> directory.  This is
 2066: intentional for a single-user local tool, but you should be aware of it on
 2067: long-running servers.
 2068: 
 2069: =item B<Open C<data/> tables by name; open other files by absolute path>
 2070: 
 2071: The C<view> action (C<GET /view/:table>) only looks inside C<data_dir>.
 2072: To open a file from anywhere else on the filesystem, use C<open_file>
 2073: (C<GET /open?path=/abs/path>).  The two routes use different URL schemes and
 2074: are not interchangeable.
 2075: 
 2076: =back
 2077: 
 2078: =head1 LIMITATIONS
 2079: 
 2080: =over 4
 2081: 
 2082: =item *
 2083: 
 2084: The in-memory left join in C<_left_join> holds both the left and right result
 2085: sets in RAM simultaneously.  For files with millions of rows, replace the
 2086: C<open_table> helper in C<Database::BI> with a C<Database::Join> backend
 2087: without changing this controller.
 2088: 
 2089: =item *
 2090: 
 2091: C<_resolve_language> extracts only the primary language subtag from the first
 2092: C<Accept-Language> tag (e.g. C<de> from C<de-DE,de;q=0.9,en;q=0.8>).
 2093: Quality weights and multiple alternatives are not ranked.
 2094: 
 2095: =item *
 2096: 
 2097: No GeoIP-based language resolution is implemented.  The language is resolved
 2098: from the HTTP C<Accept-Language> header only.
 2099: 
 2100: =item *
 2101: 
 2102: C<Sub::Protected> enforcement requires the CHECK compilation phase; when modules
 2103: are loaded dynamically at test time the "Too late to run CHECK block" warning
 2104: is emitted and the restriction is not enforced in that context.
 2105: 
 2106: =back
 2107: 
 2108: =head1 AUTHOR
 2109: 
 2110: Nigel Horne C<< <njh@nigelhorne.com> >>
 2111: 
 2112: =head1 LICENCE AND COPYRIGHT
 2113: 
 2114: Copyright 2026 Nigel Horne.  Usage is subject to the GPL2 licence terms.
 2115: 
 2116: =cut