File Coverage

blib/lib/SQL/Abstract/Plugin/CaseExpr.pm
Criterion Covered Total %
statement 36 36 100.0
branch 5 8 62.5
condition n/a
subroutine 7 7 100.0
pod 0 1 0.0
total 48 52 92.3


line stmt bran cond sub pod time code
1             package SQL::Abstract::Plugin::CaseExpr;
2 4     4   17094 use feature qw/signatures postderef/;
  4         13  
  4         346  
3              
4             our $VERSION = '0.01';
5 4     4   32 use Moo;
  4         7  
  4         26  
6             with 'SQL::Abstract::Role::Plugin';
7 4     4   1344 use List::Util qw/pairmap/;
  4         14  
  4         276  
8              
9 4     4   32 no warnings 'experimental::signatures';
  4         18  
  4         1565  
10              
11 9     9 0 6325 sub register_extensions ($self, $sqla) {
  9         25  
  9         20  
  9         14  
12              
13 2         6 $sqla->expander(
14 2     2   6 case => sub ($sqla, $name, $value) {
  2         661  
  2         5  
  2         4  
15             # if the user passed in the double array-ref, then we assume it's already expanded
16 2 100       66 return { -case => $value } if ref $value->[0] eq 'ARRAY';
17 1         4 my $else;
18 1         5 my @conditions = $value->@*;
19 1 50       7 $else = pop @conditions unless @conditions * %2;
20             return {
21             -case => [
22 1 50       39 [ map +($sqla->expand_expr($_->{if}, -ident), $sqla->expand_expr($_->{then}, -value)), @conditions ],
23             $else ? $sqla->expand_expr($else, -value) : ()
24             ]
25             };
26             }
27 9         206 );
28 2         5 $sqla->renderer(
29 2     2   4 case => sub ($sqla, $name, $value) {
  2         1531  
  2         3  
  2         5  
30 2         6 my $else = $value->[1];
31             $sqla->join_query_parts(
32             ' ',
33             { -keyword => 'CASE' },
34 2 50       38 (pairmap { ({ -keyword => 'WHEN' }, $a, { -keyword => 'THEN' }, $b) } $value->[0]->@*),
  2         32  
35             $else ? ({ -keyword => 'ELSE' }, $else) : (),
36             { -keyword => 'END' }
37             );
38             }
39 9         1011 );
40              
41             }
42              
43             1;
44              
45             =encoding utf8
46              
47             =head1 NAME
48              
49             SQL::Abstract::Plugin::CaseExpr - Case Expression support for SQLA2!
50              
51             =head1 SYNOPSIS
52              
53             # pass this to anything that SQLA will render
54             # arrayref b/c order matters
55             { -case => [
56             # if/then is a bit more familiar than WHEN/THEN
57             {
58             if => { sales => { '>' => 9000 } },
59             # scalars default to bind params
60             then => 'Scouter Breaking'
61             },
62             {
63             if => { sales => { '>' => 0 } },
64             then => 'At least something'
65             },
66             # if the final node does not contain if, it's the ELSE clause
67             'How did this happen?'
68             ]}
69             # CASE WHEN sales > 9000 THEN ? WHEN sales > 0 THEN ? ELSE ? END
70             # [ 'Scouter Breaking', 'At least something', 'How did this happen?' ]
71              
72             =head1 DESCRIPTION
73              
74             This is a work in progress to support CASE expressions in SQLA2
75              
76             B
77              
78             =head2 Using with DBIx::Class
79              
80             In order to use this with DBIx::Class, you simply need to apply the DBIC-SQLA2 plugin, and
81             then your SQLMaker will support this syntax!
82              
83             =head2 New Syntax
84              
85             =head3 -case node
86              
87             The entry point for the new handling is the -case node. This takes an arrayref of hashrefs which represent the branches of the conditional tree, and optionally a final entry as the default clause.
88              
89             The hashrefs must have the following two keys:
90              
91             =over 4
92              
93             =item if
94              
95             The condition to be checked against. It is processed like a WHERE clause.
96              
97             =item then
98              
99             The value to be returned if this condition is true. Scalars here default to -value, which means they are taken as bind parameters
100              
101             =back
102              
103             =cut