blib/lib/Database/BI/Ask.pm

filter: Statementstmt 100% 144 / 144 Branchbran 98.4% 63 / 64 Conditioncond 96.0% 49 / 51 MC/DCmcdc 93.3% 28 / 30 Subroutinesub 100% 14 / 14 Podpod 100% 2 / 2 totaltotal 98.3% 300 / 305 CC 58 SCAR 23.2
CC
58
Cov
99%
_parse_visual
19.0
ask
12.0
_build_url
9.1
CRAP 10.1
« blib/lib/Database/BI/Summary.pm ↑ Summary blib/lib/Database/BI/I18N.pm »
1 package Database::BI::Ask;
2  
3 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5  
6 1 our $VERSION = '0.023.1';
7  
8 1 ▶use Carp qw(croak);
Subroutine BEGIN: called CC=1 SCAR=0.0
9 1 ▶use Database::BI::Util qw(urlencode);
Subroutine BEGIN: called CC=1 SCAR=0.0
10 1 ▶use Readonly;
Subroutine BEGIN: called CC=1 SCAR=0.0
11 1 ▶use Scalar::Util qw(looks_like_number);
Subroutine BEGIN: called CC=1 SCAR=0.0
12  
13 1 Readonly my %SUPPORTED_KINDS => map { $_ => 1 }
14 qw( bar pie line graph scatter heatmap stacked combo treemap histogram map table );
15  
16 1 Readonly my %KIND_ROUTE => (
17 pie => 'pie',
18 bar => 'bar',
19 line => 'graph',
20 graph => 'graph',
21 scatter => 'scatter',
22 heatmap => 'heatmap',
23 stacked => 'stacked',
24 combo => 'combo',
25 treemap => 'treemap',
26 histogram => 'histogram',
27 map => 'map',
28 table => '',
29 );
30  
31 1 Readonly my @MEASURE_FUNCTIONS => qw( sum avg min max count count_distinct );
32  
33 # ---------------------------------------------------------------------------
34 # ask(%args) -> { spec, url, valid => 1 } | { error => 'code_or_code:detail' }
35 #
36 # args:
37 # question -- natural language question (required)
38 # spec_key -- l= spec, e.g. "table:sales" (required for URL construction)
39 # label -- human-readable dataset name
40 # columns -- arrayref of { name, type } hashrefs (from infer_column_types)
41 # ai_config -- hashref { provider, api_key, model }
42 # ---------------------------------------------------------------------------
43 9 ▶sub ask {
Subroutine ask: called CC=12 SCAR=24.8
Pod documented
44 9 my (%args) = @_;
45  
46 9 ▶ my $question = $args{question} // '';
Condition100%2/2
$args{"question"} // ""
l!l
81
MC/DC100%1/1
$args{"question"} // ""
$args{"question"}
Truth table100%2/2
A
A= $args{"question"}
Aresult
11
00
47 9 ▶ my $spec_key = $args{spec_key} // '';
Condition100%2/2
$args{"spec_key"} // ""
l!l
54
MC/DC100%1/1
$args{"spec_key"} // ""
$args{"spec_key"}
Truth table100%2/2
A
A= $args{"spec_key"}
Aresult
11
00
48 9 ▶ my $label = $args{label} // 'dataset';
Condition100%2/2
$args{"label"} // "dataset"
l!l
27
MC/DC100%1/1
$args{"label"} // "dataset"
$args{"label"}
Truth table100%2/2
A
A= $args{"label"}
Aresult
11
00
49 9 ▶ my $columns = $args{columns} // [];
Condition100%2/2
$args{"columns"} // []
l!l
54
MC/DC100%1/1
$args{"columns"} // []
$args{"columns"}
Truth table100%2/2
A
A= $args{"columns"}
Aresult
11
00
50 9 ▶ my $ai_config = $args{ai_config} // {};
Condition100%2/2
$args{"ai_config"} // {}
l!l
63
MC/DC100%1/1
$args{"ai_config"} // {}
$args{"ai_config"}
Truth table100%2/2
A
A= $args{"ai_config"}
Aresult
11
00
51  
52 9 ▶ return { error => 'ask_no_question' }
Branch: 1 branch
BranchTrueFalseTotal
unless length $question 2 7 9
53 unless length $question;
54  
55 return { error => 'ask_unavailable' }
56 7 ▶ unless length($ai_config->{api_key} // '');
Branch: 1 branch
BranchTrueFalseTotal
unless length($$ai_config{"api_key"} // "") 2 5 7
Condition100%2/2
$$ai_config{"api_key"} // ""
l!l
61
MC/DC100%1/1
$$ai_config{"api_key"} // ""
$$ai_config{"api_key"}
Truth table100%2/2
A
A= $$ai_config{"api_key"}
Aresult
11
00
57  
58 5 my $raw = eval { _call_llm($question, $label, $columns, $ai_config) };
59 5 ▶ return { error => 'ask_provider_error' } if $@;
Branch: 1 branch
BranchTrueFalseTotal
if $@ 1 4 5
60  
61 4 my $visual = eval { _parse_visual($raw) };
62 4 ▶ return { error => 'ask_unparseable' } unless ref $visual eq 'HASH';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $visual eq "HASH" 1 3 4
63  
64 3 my %col_set = map { $_->{name} => 1 } @$columns;
65 3 my $val_err = _validate_encode($visual, \%col_set);
66 3 ▶ return { error => "ask_invalid_spec:$val_err" } if defined $val_err;
Branch: 1 branch
BranchTrueFalseTotal
if defined $val_err 1 2 3
67  
68 2 my $url = _build_url($visual, $spec_key);
69 2 my $spec = _build_spec($visual, $label, $spec_key);
70  
71 2 return { spec => $spec, url => $url, valid => 1 };
72 }
73  
74 # ---------------------------------------------------------------------------
75 # infer_column_types($records, $columns_aref) -> [{ name, type }, ...]
76 #
77 # Type is one of: 'number', 'date', 'string'
78 # ---------------------------------------------------------------------------
79 8 ▶sub infer_column_types {
Subroutine infer_column_types: called CC=5 SCAR=16.1
Pod documented
80 8 my ($records, $columns) = @_;
81 8 ▶ return [] unless ref $records eq 'ARRAY' && ref $columns eq 'ARRAY';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $records eq "ARRAY" and ref $columns eq "ARRAY" 3 5 8
Condition100%3/3
ref $records eq "ARRAY" and ref $columns eq "ARRAY"
!ll&&!rl&&r
215
MC/DC100%2/2
ref $records eq "ARRAY" and ref $columns eq "ARRAY"
ref $records eq "ARRAY" ref $columns eq "ARRAY"
Truth table100%3/3
A and B
A= ref $records eq "ARRAY"B= ref $columns eq "ARRAY"
ABresult
0X0
100
111
82  
83 5 my @result;
84 5 for my $col (@$columns) {
85 12 ▶ my @vals = grep { defined $_ && length $_ } map { $_->{$col} } @$records;
Condition100%3/3
defined $_ && length $_
!ll&&!rl&&r
2140
MC/DC100%2/2
defined $_ && length $_
defined $_ length $_
Truth table100%3/3
A && B
A= defined $_B= length $_
ABresult
0X0
100
111
86 12 push @result, { name => $col, type => _infer_type(\@vals) };
87 }
88 5 return \@result;
89 }
90  
91 # ---------------------------------------------------------------------------
92 # Internal helpers
93 # ---------------------------------------------------------------------------
94  
95 29 ▶sub _infer_type {
Subroutine _infer_type: called CC=5 SCAR=16.1
96 29 my ($vals) = @_;
97 29 ▶ return 'string' unless @$vals;
Branch: 1 branch
BranchTrueFalseTotal
unless @$vals 4 25 29
98  
99 25 my $n = scalar @$vals;
100 25 my $numeric = grep { looks_like_number($_) } @$vals;
101 25 ▶ return 'number' if $numeric / $n > 0.5;
Branch: 1 branch
BranchTrueFalseTotal
if $numeric / $n > "0.5" 8 17 25
102  
103 my $date = grep {
104 17 ▶ /\A\d{4}-\d{2}-\d{2}\z/ || /\A\d{1,2}\/\d{1,2}\/\d{4}\z/
Condition100%3/3
/\A\d{4}-\d{2}-\d{2}\z/ || m[\A\d{1,2}/\d{1,2}/\d{4}\z]
l!l&&r!l&&!r
14639
MC/DC100%2/2
/\A\d{4}-\d{2}-\d{2}\z/ || m[\A\d{1,2}/\d{1,2}/\d{4}\z]
/\A\d{4}-\d{2}-\d{2}\z/ m[\A\d{1,2}/\d{1,2}/\d{4}\z]
Truth table100%3/3
A || B
A= /\A\d{4}-\d{2}-\d{2}\z/B= m[\A\d{1,2}/\d{1,2}/\d{4}\z]
ABresult
1X1
011
000
105 } @$vals;
106 17 ▶ return 'date' if $date / $n > 0.5;
Branch: 1 branch
BranchTrueFalseTotal
if $date / $n > "0.5" 6 11 17
107  
108 11 return 'string';
109 }
110  
111 6 ▶sub _call_llm {
Subroutine _call_llm: called CC=5 SCAR=16.1
112 6 my ($question, $label, $columns, $ai_config) = @_;
113  
114 6 require LWP::UserAgent;
115 6 require HTTP::Request;
116 6 require JSON::PP; # core since Perl 5.013009; MIN_PERL_VERSION is 5.028000
117  
118 6 my $col_lines = join("\n", map { "- $_->{name} ($_->{type})" } @$columns);
119 6 my $kinds_str = join(', ', sort keys %SUPPORTED_KINDS);
120 6 my $mfns_str = join(', ', @MEASURE_FUNCTIONS);
121  
122 6 my $prompt = <<"PROMPT";
123 You are a data analysis assistant. Given a dataset and a question, return ONLY a JSON object describing one visualization. Do not include any explanation, markdown, or surrounding text -- just the JSON object.
124  
125 Dataset: "$label"
126 Columns:
127 $col_lines
128  
129 Available chart kinds: $kinds_str
130 Available measure functions: $mfns_str
131  
132 Encode key rules per kind:
133 - pie: cat (required), val (optional)
134 - bar: cat (required), val (optional)
135 - line/graph: x (required), y (required), series (optional)
136 - scatter: x (required), y (required), series (optional), size (optional)
137 - heatmap: x (required), y (required), val (optional)
138 - stacked: cat (required), series (required), val (optional)
139 - combo: cat (required), bar_val (required), line_val (required)
140 - treemap: group (required), size (optional)
141 - histogram: col (required)
142 - map: lat+lon (required pair) or loc (required)
143 - table: no encode keys
144  
145 Question: "$question"
146  
147 Return ONLY valid JSON with this exact structure (no other text):
148 {"kind":"<kind>","title":"<short descriptive title>","encode":{"<key>":"<column name>"}}
149  
150 Use only column names from the list above. Choose the most informative chart kind for the question.
151 PROMPT
152  
153 my $body = JSON::PP::encode_json({
154 6 ▶ model => ($ai_config->{model} // 'claude-haiku-5-5'),
Condition100%2/2
$$ai_config{"model"} // "claude-haiku-5-5"
l!l
15
MC/DC100%1/1
$$ai_config{"model"} // "claude-haiku-5-5"
$$ai_config{"model"}
Truth table100%2/2
A
A= $$ai_config{"model"}
Aresult
11
00
155 max_tokens => 256,
156 messages => [{ role => 'user', content => $prompt }],
157 });
158  
159 6 my $ua = LWP::UserAgent->new(timeout => 30);
160 my $req = HTTP::Request->new(
161 'POST',
162 'https://api.anthropic.com/v1/messages',
163 [
164 'Content-Type' => 'application/json',
165 'x-api-key' => $ai_config->{api_key},
166 6 'anthropic-version' => '2023-06-01',
167 ],
168 $body,
169 );
170  
171 6 my $resp = $ua->request($req);
172 6 ▶ croak sprintf('Provider error: %s', $resp->status_line) unless $resp->is_success;
Branch: 1 branch
BranchTrueFalseTotal
unless $resp->is_success 1 5 6
173  
174 5 my $data = JSON::PP::decode_json($resp->decoded_content);
175 4 my $text = eval { $data->{content}[0]{text} };
176 4 ▶ croak 'No text in provider response' unless defined $text && length $text;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $text and length $text 2 2 4
Condition100%3/3
defined $text and length $text
!ll&&!rl&&r
112
MC/DC100%2/2
defined $text and length $text
defined $text length $text
Truth table100%3/3
A and B
A= defined $textB= length $text
ABresult
0X0
100
111
177 2 return $text;
178 }
179  
180 46 ▶sub _parse_visual {
Subroutine _parse_visual: called CC=19 SCAR=29.4
181 46 my ($text) = @_;
182 46 require JSON::PP;
183  
184 # The reply has already been decoded from the wire, so it is characters, and
185 # decode_json (which takes UTF-8 bytes) would refuse any non-ASCII title.
186 46 my $json = JSON::PP->new;
187  
188 # Try the full text first (clean LLM response).
189 46 my $v = eval { $json->decode($text) };
190  
191 # Fall back: LLM added preamble/suffix text around the JSON object.
192 # A bracket-depth counter locates the first complete balanced {..} block.
193 # This is safer than a greedy /\{.*\}/s regex, which captures from the
194 # first '{' all the way to the LAST '}' in the string -- if the LLM
195 # produced multiple JSON-like constructs the greedy form spans them all
196 # and returns unparseable input. The depth counter handles nested objects
197 # and stops exactly at the matching brace. A brace inside a string is text,
198 # so strings are stepped over, with a backslash escaping what follows it.
199 46 ▶ if (!ref $v) {
Branch: 1 branch
BranchTrueFalseTotal
unless (ref $v) 25 21 46
200 25 my $start = index($text, '{');
201 25 ▶ if ($start >= 0) {
Branch: 1 branch
BranchTrueFalseTotal
if ($start >= 0) 18 7 25
202 18 my ($depth, $end, $in_string, $escaped) = (0, -1, 0, 0);
203 18 for my $i ($start .. length($text) - 1) {
204 5586 my $ch = substr($text, $i, 1);
205 5586 ▶ if ($in_string) {
Branch: 1 branch
BranchTrueFalseTotal
if ($in_string) 402 5184 5586
206 402 ▶ if ($escaped) { $escaped = 0 }
Branch: 3 branches
BranchTrueFalseTotal
if ($escaped) { } 2 400 402
elsif ($ch eq '\\') { } 2 398 400
elsif ($ch eq '"') { } 77 321 398
207 2 elsif ($ch eq '\\') { $escaped = 1 }
208 77 elsif ($ch eq '"') { $in_string = 0 }
209 402 next;
210 }
211 5184 ▶ if ($ch eq '"') { $in_string = 1; next }
Branch: 1 branch
BranchTrueFalseTotal
if ($ch eq "\"") 77 5107 5184
212 5107 ▶ $depth++ if $ch eq '{';
Branch: 1 branch
BranchTrueFalseTotal
if $ch eq "{" 5024 83 5107
213 5107 ▶ if ($ch eq '}') {
Branch: 1 branch
BranchTrueFalseTotal
if ($ch eq "}") 20 5087 5107
214 20 $depth--;
215 20 ▶ if ($depth == 0) { $end = $i; last }
Branch: 1 branch
BranchTrueFalseTotal
if ($depth == 0) 13 7 20
216 }
217 }
218 18 ▶ if ($end >= 0) {
Branch: 1 branch
BranchTrueFalseTotal
if ($end >= 0) 13 5 18
219 13 $v = eval { $json->decode(substr($text, $start, $end - $start + 1)) };
220 }
221 }
222 }
223  
224 46 ▶ return undef unless ref $v eq 'HASH';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $v eq "HASH" 13 33 46
225 33 ▶ return undef unless defined $v->{kind} && $SUPPORTED_KINDS{ $v->{kind} };
Branch: 1 branch
BranchTrueFalseTotal
unless defined $$v{"kind"} and $SUPPORTED_KINDS{$$v{"kind"}} 6 27 33
Condition100%3/3
defined $$v{"kind"} and $SUPPORTED_KINDS{$$v{"kind"}}
!ll&&!rl&&r
2427
MC/DC100%2/2
defined $$v{"kind"} and $SUPPORTED_KINDS{$$v{"kind"}}
defined $$v{"kind"} $SUPPORTED_KINDS{$$v{"kind"}}
Truth table100%3/3
A and B
A= defined $$v{"kind"}B= $SUPPORTED_KINDS{$$v{"kind"}}
ABresult
0X0
100
111
226 27 ▶ return undef if defined $v->{encode} && ref $v->{encode} ne 'HASH';
Branch: 1 branch
BranchTrueFalseTotal
if defined $$v{"encode"} and ref $$v{"encode"} ne "HASH" 4 23 27
Condition100%3/3
defined $$v{"encode"} and ref $$v{"encode"} ne "HASH"
!ll&&!rl&&r
11124
MC/DC100%2/2
defined $$v{"encode"} and ref $$v{"encode"} ne "HASH"
defined $$v{"encode"} ref $$v{"encode"} ne "HASH"
Truth table100%3/3
A and B
A= defined $$v{"encode"}B= ref $$v{"encode"} ne "HASH"
ABresult
0X0
100
111
227 23 ▶ $v->{encode} //= {};
Condition100%2/2
$$v{"encode"} //= {}
l!l
1211
MC/DC100%1/1
$$v{"encode"} //= {}
$$v{"encode"}
Truth table100%2/2
A
A= $$v{"encode"}
Aresult
11
00
228 23 return $v;
229 }
230  
231 16 ▶sub _validate_encode {
Subroutine _validate_encode: called CC=6 SCAR=17.9
232 16 my ($visual, $col_set) = @_;
233 16 my $encode = $visual->{encode};
234 16 ▶ return undef unless %$encode;
Branch: 1 branch
BranchTrueFalseTotal
unless %$encode 2 14 16
235  
236 14 for my $key (sort keys %$encode) {
237 20 my $col = $encode->{$key};
238 20 ▶ next unless defined $col && length $col;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $col and length $col 4 16 20
Condition100%3/3
defined $col and length $col
!ll&&!rl&&r
2216
MC/DC100%2/2
defined $col and length $col
defined $col length $col
Truth table100%3/3
A and B
A= defined $colB= length $col
ABresult
0X0
100
111
239 16 ▶ return $col unless $col_set->{$col};
Branch: 1 branch
BranchTrueFalseTotal
unless $$col_set{$col} 7 9 16
240 }
241 7 return undef;
242 }
243  
244 22 ▶sub _build_url {
Subroutine _build_url: called CC=9 SCAR=22.0
245 22 my ($visual, $spec_key) = @_;
246 22 my $kind = $visual->{kind};
247 22 ▶ my $encode = $visual->{encode} // {};
Condition100%2/2
$$visual{"encode"} // {}
l!l
184
MC/DC100%1/1
$$visual{"encode"} // {}
$$visual{"encode"}
Truth table100%2/2
A
A= $$visual{"encode"}
Aresult
11
00
248  
249 22 ▶ if ($kind eq 'table') {
Branch: 1 branch
BranchTrueFalseTotal
if ($kind eq "table") 6 16 22
250 6 ▶ if ($spec_key =~ /\Atable:(.+)\z/) {
Branch: 1 branch
BranchTrueFalseTotal
if ($spec_key =~ /\Atable:(.+)\z/) 4 2 6
251 4 return '/view/' . urlencode($1);
252 }
253 2 (my $path = $spec_key) =~ s/\Apath://;
254 2 require Database::BI::Model::DataSource;
255 2 my ($file, $internal) = Database::BI::Model::DataSource::split_path($path);
256 2 ▶ return '/open?path=' . urlencode($file) . (defined $internal ? '&it=' . urlencode($internal) : '');
Branch: 1 branch
BranchTrueFalseTotal
defined $internal ? : 0 2 2
257 }
258  
259 16 ▶ my $route = $KIND_ROUTE{$kind} // $kind;
Condition33%1/3
$KIND_ROUTE{$kind} // $kind
l!l&&r!l&&!r
1600
MC/DC0%0/2
$KIND_ROUTE{$kind} // $kind
$KIND_ROUTE{$kind} $kind
Truth table33%1/3
A // B
A= $KIND_ROUTE{$kind}B= $kind
ABresult
1X1
011
000
260 16 my @params = ('l=' . urlencode($spec_key));
261 16 for my $key (sort keys %$encode) {
262 24 my $val = $encode->{$key};
263 24 ▶ next unless defined $val && length $val;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $val and length $val 4 20 24
Condition100%3/3
defined $val and length $val
!ll&&!rl&&r
2220
MC/DC100%2/2
defined $val and length $val
defined $val length $val
Truth table100%3/3
A and B
A= defined $valB= length $val
ABresult
0X0
100
111
264 20 push @params, "$key=" . urlencode($val);
265 }
266 16 return "/$route?" . join('&', @params);
267 }
268  
269 13 ▶sub _build_spec {
Subroutine _build_spec: called CC=4 SCAR=13.9
270 13 my ($visual, $label, $spec_key) = @_;
271 return {
272 name => ($visual->{title} || "$label analysis"),
273 datasets => [{ spec => $spec_key }],
274 version => 1,
275 visuals => [{
276 kind => $visual->{kind},
277 title => ($visual->{title} // ''),
278 13 ▶ encode => ($visual->{encode} // {}),
Condition100%6/6
$$visual{'title'} || "$label analysis"
l!l
85
$$visual{"title"} // ""
l!l
94
$$visual{"encode"} // {}
l!l
103
MC/DC100%1/1
$$visual{'title'} || "$label analysis"
$$visual{'title'}
MC/DC100%1/1
$$visual{"title"} // ""
$$visual{"title"}
MC/DC100%1/1
$$visual{"encode"} // {}
$$visual{"encode"}
Truth table100%2/2
A
A= $$visual{'title'}
Aresult
11
00
Truth table100%2/2
A
A= $$visual{"title"}
Aresult
11
00
Truth table100%2/2
A
A= $$visual{"encode"}
Aresult
11
00
279 options => {},
280 }],
281 };
282 }
283  
284  
285 1 1;
286  
287 __END__
« blib/lib/Database/BI/Summary.pm ↑ Summary blib/lib/Database/BI/I18N.pm »