Querylet.pm

filter: Statementstmt 94.0% 94 / 100 Branchbran 100% 2 / 2 Conditioncond 100% 2 / 2 MC/DCmcdc 100% 1 / 1 Subroutinesub 92.0% 23 / 25 Podpod 100% 21 / 21 totaltotal 94.7% 143 / 151 CC 3 SCAR 2.6
CC
3
Cov
94%
once
3.0
set_output_method
2.0
column_headers
2.0
CRAP 1.3
« Querylet/Query.pm ↑ Summary Querylet/Input.pm »
1 15 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
2 15 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
3 package Querylet 0.402;
4 15 ▶use Filter::Simple;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 # ABSTRACT: simplified queries for the non-programmer
6  
7 #pod =head1 SYNOPSIS
8 #pod
9 #pod use Querylet;
10 #pod
11 #pod database: dbi:SQLite:dbname=wafers.db
12 #pod
13 #pod query:
14 #pod SELECT wafer_id, material, diameter, failurecode
15 #pod FROM grown_wafers
16 #pod WHERE reactor_id = 105
17 #pod AND product_type <> 'Calibration'
18 #pod
19 #pod add column surface_area:
20 #pod $value = $row->{diameter} * 3.14;
21 #pod
22 #pod add column cost:
23 #pod $value = $row->{surface_area} * 100 if $row->{material} eq 'GaAs';
24 #pod $value = $row->{surface_area} * 200 if $row->{material} eq 'InP';
25 #pod
26 #pod munge column failurecode:
27 #pod $value = 10 if $value == 3; # 3's have been reclassified
28 #pod
29 #pod munge all values:
30 #pod $value = '(null)' unless defined $value;
31 #pod
32 #pod output format: html
33 #pod
34 #pod =head1 DESCRIPTION
35 #pod
36 #pod Querylet provides a simple syntax for writing Perl-enhanced SQL queries with
37 #pod multiple output methods. It processes and renders a template SQL query, then
38 #pod processes the query results before returning them to the user.
39 #pod
40 #pod The results can be returned in various formats.
41 #pod
42 #pod =cut
43  
44 #pod =head1 SYNTAX
45 #pod
46 #pod The intent of Querylet is to provide a simple syntax for writing queries.
47 #pod Querylet will rewrite querylets from their simple form into complete Perl
48 #pod programs. The syntax described here is the "intended" and basic syntax, but
49 #pod savvy Perl hackers will realize that horrible things can be done by
50 #pod interspersing "real" Perl with querylet directives.
51 #pod
52 #pod I am afraid I really cannot suggest that course of action, sir.
53 #pod
54 #pod =head2 DIRECTIVES
55 #pod
56 #pod In the directives below, a BLOCK begins after the colon preceding it and ends
57 #pod at the next line with something unindented.
58 #pod
59 #pod =over 4
60 #pod
61 #pod =item C<database: VALUE>
62 #pod
63 #pod This directive provides information about the database to which to connect.
64 #pod Its syntax is likely to be better defined by the specific Querylet subclass
65 #pod you're using.
66 #pod
67 #pod =item C<output format: VALUE>
68 #pod
69 #pod This directive names a format to be used by the output renderer. The default
70 #pod value is "csv".
71 #pod
72 #pod =item C<output file: VALUE>
73 #pod
74 #pod This directive names a file to which the rendered output should be written. If
75 #pod not given, renderers will present output to the terminal, or otherwise
76 #pod interactively. If this doesn't make sense, an error should be thrown.
77 #pod
78 #pod =item C<query: BLOCK>
79 #pod
80 #pod query:
81 #pod SELECT customer.customerid, lastname, firstname, COUNT(*)
82 #pod FROM customers
83 #pod JOIN orders ON customer.customerid = orders.customerid
84 #pod GROUP BY customer.customerid, lastname, firstname
85 #pod
86 #pod This directive provides the query to be run by Querylet. The query can
87 #pod actually be a template, and will be rendered before running if (and only if)
88 #pod the C<munge query> directive occurs in the querylet. The query can include
89 #pod bind parameters -- that is, you can put a ? in place of a value, and later use
90 #pod C<query parameter> to replace the value. (See below.)
91 #pod
92 #pod It is important that every selected column have a name or alias.
93 #pod
94 #pod =item C<query parameter: BLOCK>
95 #pod
96 #pod This directive sets the value for the next bind parameter. You should have one
97 #pod (and only one) C<query parameter> directive for each "?" in your query.
98 #pod
99 #pod =item C<munge query: BLOCK>
100 #pod
101 #pod The directive informs Querylet that the given query is a template and must be
102 #pod rendered. The BLOCK must return a list of parameter names and values, which
103 #pod will be passed to the template toolkit to render the query.
104 #pod
105 #pod =item C<set option NAME: BLOCK>
106 #pod
107 #pod This sets the name option to the given value, and is used to set up options for
108 #pod plugins and I/O handlers. Leading and trailing space is stripped from the
109 #pod block.
110 #pod
111 #pod =item C<munge rows: BLOCK>
112 #pod
113 #pod This directive causes the given block of code to be run on every row. The row
114 #pod is made available to the block as C<$row>, a hashref.
115 #pod
116 #pod =item C<delete rows where: BLOCK>
117 #pod
118 #pod This directive will cause any row to be deleted where the given condition
119 #pod evaluates true. In that evaluation, C<$row> is available.
120 #pod
121 #pod =item C<munge all values: BLOCK>
122 #pod
123 #pod This directive causes the given block of code to be run on every value of every
124 #pod row. The row is made available to the block as C<$row>, a hashref. The value
125 #pod is available as C<$value>.
126 #pod
127 #pod =item C<munge column NAME: BLOCK>
128 #pod
129 #pod This directive causes the given block of code to be run on the named column in
130 #pod every row. The row is made available to the block as C<$row>, a hashref. The
131 #pod column value is available as C<$value>.
132 #pod
133 #pod =item C<add column NAME: BLOCK>
134 #pod
135 #pod This directive adds a column to the result set, evaluating the given block for
136 #pod each row. The row is made available as to the block as C<$row>, and the new
137 #pod column value is available as C<$value>.
138 #pod
139 #pod =item C<delete column NAME>
140 #pod
141 #pod This directive deletes the named column from the result set.
142 #pod
143 #pod =item C<delete columns where: BLOCK>
144 #pod
145 #pod This directive will cause any column to be deleted where the given condition
146 #pod evaluates true. In that evaluation, C<$column> is available, containing the
147 #pod column name; C<@values> contains all the values for that column.
148 #pod
149 #pod =item C<no output>
150 #pod
151 #pod This directive instructs the Querylet not to output its results.
152 #pod
153 #pod =back
154 #pod
155 #pod =head1 IMPLEMENTATION
156 #pod
157 #pod Querylet is a source filter, implemented as a class suitable for subclassing.
158 #pod It rewrites the querylet to use the Querylet::Query class to perform its work.
159 #pod
160 #pod =cut
161  
162 #pod =head2 METHODS
163 #pod
164 #pod =over 4
165 #pod
166 #pod =item init
167 #pod
168 #pod Querylet->init;
169 #pod
170 #pod The C<init> method is called to generate a header for the querylet, importing
171 #pod needed modules and creating the Query object. By default, the Query object is
172 #pod assigned to C<$q>.
173 #pod
174 #pod =cut
175  
176 15 ▶sub init { <<'END_CODE'
Subroutine init: called CC=1 SCAR=0.0
Pod documented
177 use strict;
178 use warnings;
179 use Querylet::Query;
180 my $q ||= new Querylet::Query;
181 END_CODE
182 15 }
183  
184 #pod =item set_dbh
185 #pod
186 #pod Querylet->set_dbh($text);
187 #pod
188 #pod This method returns Perl code to set the database handle to be used by the
189 #pod Query object. The default implementation will attempt to use $text as a DBI
190 #pod connect string to create a dbh.
191 #pod
192 #pod =cut
193  
194 14 ▶sub set_dbh { shift; <<"END_CODE"
Subroutine set_dbh: called CC=1 SCAR=0.0
Pod documented
195 use DBI;
196 my \$dbh = DBI->connect(q|$_[0]|);
197 \$q->set_dbh(\$dbh);
198 END_CODE
199 14 }
200  
201 #pod =item set_query
202 #pod
203 #pod Querylet->set_query($sql_template);
204 #pod
205 #pod This method returns Perl code to set the Query object's SQL query to the passed
206 #pod value.
207 #pod
208 #pod =cut
209  
210 14 ▶sub set_query { shift; "\$q->set_query(q{$_[0]});\n"; }
Subroutine set_query: called CC=1 SCAR=0.0
Pod documented
211  
212 #pod =item bind_next_param
213 #pod
214 #pod Querylet->bind_next_param($text)
215 #pod
216 #pod This method produces Perl code to push the given parameters onto the list of
217 #pod bind parameters for the query. (The text should evaluate to a list of
218 #pod parameters to push.)
219 #pod
220 #pod =cut
221  
222 6 ▶sub bind_next_param { shift; <<"END_CODE"
Subroutine bind_next_param: called CC=1 SCAR=0.0
Pod documented
223 {
224 my \$input = \$q->{input};
225 \$q->bind_more($_[0]);
226 }
227 END_CODE
228 6 }
229  
230 #pod =item set_query_vars
231 #pod
232 #pod Querylet->set_query_vars(%values);
233 #pod
234 #pod This method returns Perl code to set the template variables to be used to
235 #pod render the SQL query template.
236 #pod
237 #pod =cut
238  
239 3 ▶sub set_query_vars { shift; <<"END_CODE"
Subroutine set_query_vars: called CC=1 SCAR=0.0
Pod documented
240 {
241 my \$input = \$q->{input};
242 \$q->set_query_vars({$_[0]});
243 }
244 END_CODE
245 3 }
246  
247 #pod =item set_option
248 #pod
249 #pod Querylet->set_option($option, $value);
250 #pod
251 #pod This method returns Perl code to set the named query option to the given value.
252 #pod At present, this works by using the Querylet::Query scratchpad, but a more
253 #pod sophisticated method will probably be implemented. Someday.
254 #pod
255 #pod =cut
256  
257 3 ▶sub set_option { shift;
Subroutine set_option: called CC=1 SCAR=0.0
Pod documented
258 3 my ($option, $value) = @_;
259 3 $value =~ s/(^\s+|\s+$)//g;
260 3 "\$q->option(q{$option}, q{$value});\n"
261 }
262  
263 #pod =item input
264 #pod
265 #pod Querylet->input($parameter);
266 #pod
267 #pod This method returns code to instruct the Query object to get an input parameter
268 #pod with the given name.
269 #pod
270 #pod =cut
271  
272 2 ▶sub input { shift; "\$q->input(q{$_[0]});\n"; }
Subroutine input: called CC=1 SCAR=0.0
Pod documented
273  
274 #pod =item set_input_type
275 #pod
276 #pod Querylet->set_input_type($type);
277 #pod
278 #pod This method returns Perl code to set the input format.
279 #pod
280 #pod =cut
281  
282 2 ▶sub set_input_type { shift; "\$q->input_type(q{$_[0]});\n"; }
Subroutine set_input_type: called CC=1 SCAR=0.0
Pod documented
283  
284 #pod =item set_output_filename
285 #pod
286 #pod Querylet->set_output_filename($filename);
287 #pod
288 #pod This method returns Perl code to set the output filename.
289 #pod
290 #pod =cut
291  
292 5 ▶sub set_output_filename { shift; "\$q->output_filename(q{$_[0]});\n"; }
Subroutine set_output_filename: called CC=1 SCAR=0.0
Pod documented
293  
294 #pod =item set_output_method
295 #pod
296 #pod Querylet->set_output_method($type);
297 #pod
298 #pod This method returns Perl code to set the output method.
299 #pod
300 #pod =cut
301  
302 0 ▶sub set_output_method { shift; "\$q->write_type(q{$_[0]});\n"; }
Subroutine set_output_method: not called CC=1 SCAR=6.9
Pod documented
303  
304 #pod =item set_output_type
305 #pod
306 #pod Querylet->set_output_type($type);
307 #pod
308 #pod This method returns Perl code to set the output format.
309 #pod
310 #pod =cut
311  
312 7 ▶sub set_output_type { shift; "\$q->output_type(q{$_[0]});\n"; }
Subroutine set_output_type: called CC=1 SCAR=0.0
Pod documented
313  
314 #pod =item munge_rows
315 #pod
316 #pod Querylet->munge_rows($text);
317 #pod
318 #pod This method returns Perl code to execute the Perl given in C<$text> for every
319 #pod row in the result set, aliasing C<$row> to the row on each iteration.
320 #pod
321 #pod =cut
322  
323 5 ▶sub munge_rows { shift; <<"END_CODE";
Subroutine munge_rows: called CC=1 SCAR=0.0
Pod documented
324 foreach my \$row (\@{\$q->results}) {
325 $_[0]
326 }
327 END_CODE
328 }
329  
330 #pod =item delete_rows
331 #pod
332 #pod Querylet->delete_rows($text);
333 #pod
334 #pod This method returns Perl code to delete from the result set any row for which
335 #pod C<$text> evaluates true. The code iterates over every row in the result set,
336 #pod aliasing C<$row> to the row.
337 #pod
338 #pod =cut
339  
340 1 ▶sub delete_rows { shift; <<"END_CODE";
Subroutine delete_rows: called CC=1 SCAR=0.0
Pod documented
341 my \@new_results;
342 for my \$row (\@{\$q->results}) {
343 push \@new_results, \$row unless ($_[0]);
344 }
345 \$q->set_results([\@new_results]);
346 END_CODE
347 }
348  
349 #pod =item munge_col
350 #pod
351 #pod Querylet->munge_col($column, $text);
352 #pod
353 #pod This method returns Perl code to evaluate the Perl code given in C<$text> for
354 #pod each row, with the variables C<$row> and C<$value> aliased to the row and it's
355 #pod C<$column> value respectively.
356 #pod
357 #pod =cut
358  
359 1 ▶sub munge_col { shift; <<"END_CODE";
Subroutine munge_col: called CC=1 SCAR=0.0
Pod documented
360 foreach my \$row (\@{\$q->results}) {
361 foreach my \$value (\$row->{$_[0]}) {
362 $_[1]
363 }
364 }
365 END_CODE
366 }
367  
368 #pod =item add_col
369 #pod
370 #pod Querylet->add_col($column, $text);
371 #pod
372 #pod This method returns Perl code, adding a column with the given name. The Perl
373 #pod given in C<$text> is evaluated for each row, with the variables C<$row> and
374 #pod C<$value> aliased to the row and row column respectively.
375 #pod
376 #pod If a column with the given name already exists, a warning issue and the
377 #pod directive is ignored.
378 #pod
379 #pod =cut
380  
381 7 ▶sub add_col { shift; <<"END_CODE";
Subroutine add_col: called CC=1 SCAR=0.0
Pod documented
382 if (exists \$q->results->[0]->{$_[0]}) {
383 warn "column $_[0] already exists; ignoring directive\n";
384 } else {
385 push \@{\$q->columns}, '$_[0]';
386 foreach my \$row (\@{\$q->results}) {
387 for my \$value (\$row->{$_[0]}) {
388 $_[1]
389 }
390 }
391 }
392 END_CODE
393 }
394  
395 #pod =item delete_col
396 #pod
397 #pod Querylet->delete_col($column);
398 #pod
399 #pod This method returns Perl code, deleting the named column from the result set.
400 #pod
401 #pod =cut
402  
403 9 ▶sub delete_col { shift; <<"END_CODE";
Subroutine delete_col: called CC=1 SCAR=0.0
Pod documented
404 \$q->set_columns( [ grep { \$_ ne "$_[0]" } \@{\$q->columns} ] );
405 foreach my \$row (\@{\$q->results}) {
406 delete \$row->{$_[0]};
407 }
408 END_CODE
409 }
410  
411 #pod =item delete_cols
412 #pod
413 #pod Querylet->delete_cols($text);
414 #pod
415 #pod This method returns Perl code to delete from the result set any row for which
416 #pod C<$text> evaluates true. The code iterates over every column in the result
417 #pod set, creating C<@values>, which contains a copy of all the values in that
418 #pod columns, and C<$column>, which contains the name of the current column.
419 #pod
420 #pod =cut
421  
422 1 ▶sub delete_cols { my $class = shift; qq|
Subroutine delete_cols: called CC=1 SCAR=0.0
Pod documented
423 for my \$column (\@{\$q->columns}) {
424 my \@values;
425 push \@values, \$_->{\$column} for \@{\$q->results};
426 if ($_[0]) {
427 | . $class->delete_col('$column') . qq|
428 }
429 }
430 |
431  
432 }
433  
434 #pod =item column_headers
435 #pod
436 #pod Querylet->column_headers($text);
437 #pod
438 #pod This method returns Perl code to set up column headers. The C<$text> should be
439 #pod Perl code describing a hash of column-header pairs.
440 #pod
441 #pod =cut
442  
443 0 ▶sub column_headers { my $class = shift; "\$q->set_headers({ $_[0] });" }
Subroutine column_headers: not called CC=1 SCAR=6.9
Pod documented
444  
445 #pod =item munge_values
446 #pod
447 #pod Querylet->munge_values($text);
448 #pod
449 #pod This method returns Perl code to perform the code in C<$text> on every value in
450 #pod every row in the result set.
451 #pod
452 #pod =cut
453  
454 1 ▶sub munge_values { shift; <<"END_CODE";
Subroutine munge_values: called CC=1 SCAR=0.0
Pod documented
455 foreach my \$row (\@{\$q->results}) {
456 foreach my \$value (values \%\$row) {
457 $_[0]
458 }
459 }
460 END_CODE
461 }
462  
463 #pod =item output
464 #pod
465 #pod Querylet->output;
466 #pod
467 #pod This returns the Perl instructing the Query to output its results in the
468 #pod requested format, to the requested destination.
469 #pod
470 #pod =cut
471  
472 30 ▶sub output { shift; <<'END_CODE'
Subroutine output: called CC=1 SCAR=0.0
Pod documented
473 $q->write_output;
474 END_CODE
475 30 }
476  
477 #pod =back
478 #pod
479 #pod =head2 FUNCTIONS
480 #pod
481 #pod =over 4
482 #pod
483 #pod =item once
484 #pod
485 #pod once($id, $text);
486 #pod
487 #pod This is a little utility function, used to ensure that a bit of text is only
488 #pod included once. If it has been called before with the given C<$id>, an empty
489 #pod string is returned. Otherwise, C<$text> is returned.
490 #pod
491 #pod =cut
492  
493 15 my %ran;
494  
495 57 ▶sub once {
Subroutine once: called CC=3 SCAR=11.0
Pod documented
496 57 my ($id, $text) = @_;
497 57 ▶ return q{} if $ran{$id}++;
Branch: 1 branch
BranchTrueFalseTotal
if $ran{$id}++ 27 30 57
498 30 ▶ return $text || '';
Condition100%2/2
$text || ''
l!l
1812
MC/DC100%1/1
$text || ''
$text
Truth table100%2/2
A
A= $text
Aresult
11
00
499 }
500  
501 15 my $to_next = qr/(?=^\S|\Z)/sm;
502  
503 15 ▶FILTER {
Subroutine __ANON__: called CC=1 SCAR=0.0
504 15 my ($class) = @_;
505  
506 15 s/\r//g;
507 15 s/\A/"\n" . once('init',init)/egms;
508  
509 15 s/^ database:\s*([^\n]+)
510 14 / $class->set_dbh($1)
511 /egmsx;
512  
513 15 s/^ query:\s*(.+?)
514 $to_next
515 14 / $class->set_query($1)
516 /egmsx;
517  
518 15 s/^ query\s+parameter:\s*(.+?)
519 $to_next
520 6 / $class->bind_next_param($1);
521 /egmsx;
522  
523 15 s/^ munge\s+query:\s*(.+?)
524 $to_next
525 3 / $class->set_query_vars($1);
526 /egmsx;
527  
528 15 s/^ set\s+option\s+([\/A-Za-z0-9_]+):\s*(.+?)
529 $to_next
530 3 / $class->set_option($1,$2);
531 /egmsx;
532  
533 15 s/^ input:\s*([^\n]+)
534 2 / $class->input($1)
535 /egmsx;
536  
537 15 s/^ input\s+type:\s+(\w+)$
538 2 / $class->set_input_type($1);
539 /egmsx;
540  
541 15 s/^ munge\s+rows:\s*(.+?)
542 $to_next
543 5 / $class->munge_rows($1);
544 /egmsx;
545  
546 15 s/^ delete\s+rows\s+where:\s*(.+?)
547 $to_next
548 1 / $class->delete_rows($1);
549 /egmsx;
550  
551 15 s/^ munge\s+all\s+values:\s*(.+?)
552 $to_next
553 1 / $class->munge_values($1);
554 /egmsx;
555  
556 15 s/^ munge\s+column\s+(\w+):\s*(.+?)
557 $to_next
558 1 / $class->munge_col($1, $2);
559 /egmsx;
560  
561 15 s/^ add\s+column\s+(\w+):\s*(.+?)
562 $to_next
563 7 / $class->add_col($1, $2);
564 /egmsx;
565  
566 15 s/^ delete\s+column\s+(\w+)$
567 8 / $class->delete_col($1);
568 /egmsx;
569  
570 15 s/^ delete\s+columns\s+where:\s*(.+?)
571 $to_next
572 1 / $class->delete_cols($1);
573 /egmsx;
574  
575 15 s/^ column\s+headers?:\s*(.+?)
576 $to_next
577 0 / $class->column_headers($1);
578 /egmsx;
579  
580 15 s/^ output\s+format:\s+(\w+)$
581 7 / $class->set_output_type($1);
582 /egmsx;
583  
584 15 s/^ output\s+method:\s+(\w+)$
585 0 / $class->set_output_method($1);
586 /egmsx;
587  
588 15 s/^ output\s+file:\s+([_.A-Za-z0-9]+)$
589 5 / $class->set_output_filename($1);
590 /egmsx;
591  
592 15 s/^ no\s+output$
593 12 / once('output', q{})
594 /egmsx;
595  
596 15 s/\Z
597 30 /once('output',output)
598 /egmsx;
599 }
600  
601 #pod =back
602 #pod
603 #pod =head1 SEE ALSO
604 #pod
605 #pod L<Querylet::Query>
606 #pod
607 #pod =cut
608  
609 15 "I do endeavor to give satisfaction, sir.";
610  
611 __END__
« Querylet/Query.pm ↑ Summary Querylet/Input.pm »