File Coverage

blib/lib/SQL/Abstract/Plugin/WindowFunctions.pm
Criterion Covered Total %
statement 122 128 95.3
branch 13 22 59.0
condition 0 3 0.0
subroutine 16 16 100.0
pod 0 1 0.0
total 151 170 88.8


line stmt bran cond sub pod time code
1             package SQL::Abstract::Plugin::WindowFunctions;
2 4     4   66536 use feature qw/signatures postderef/;
  4         16  
  4         402  
3              
4             our $VERSION = '0.01';
5 4     4   33 use Moo;
  4         9  
  4         23  
6             with 'SQL::Abstract::Role::Plugin';
7              
8 4     4   1388 use List::Util qw/first pairmap/;
  4         12  
  4         332  
9              
10 4     4   29 no warnings 'experimental::signatures';
  4         30  
  4         5703  
11              
12 9     9 0 21028 sub register_extensions ($self, $sqla) {
  9         36  
  9         20  
  9         21  
13             # TODO - the ordering from +ExtraClauses doesn't look right, at least acc to the PG docs
14 9         27 $sqla->clauses_of(
15 9     9   18 'select' => sub ($sqla, @clauses) {
  9         125  
  9         31  
16 9         78 my $setop = first { $clauses[$_] eq 'setop' } 0 .. $#clauses;
  45         84  
17             # remove setop, b/c it's in the wrong place
18 9         39 splice @clauses, $setop, 1;
19              
20 9         49 my $idx = first { $clauses[$_] eq 'having' } 0 .. $#clauses;
  54         83  
21 9         50 splice @clauses, $idx + 1, 0, 'window', 'setop';
22 9         51 return @clauses;
23             }
24 9         73 );
25              
26 1         2 $sqla->clause_expander(
27 1     1   2 'select.window' => sub ($sqla, $name, $value) {
  1         582  
  1         3  
  1         2  
28             # if the first thing is not a scalar, then we assume the user is handling passing
29             # through the proper -name/-definition hashref
30 1 50       4 return $value if ref $value->[0];
31             return +(
32 1         11 window => [ pairmap { +{ -name => $a, -definition => $sqla->expand_expr({ -window => $b }) } } $value->@* ]
  1         6  
33             );
34             },
35 9         282 );
36 1         2 $sqla->clause_renderer(
37 1     1   2 'select.window' => sub ($sqla, $name, $value, @tings) {
  1         222  
  1         3  
  1         3  
  1         3  
38             # we handle the commas ourselves rather than using -op => [ ',', ...] b/c that won't
39             # take our rendered list as a node (unless we have ANOTHER node called window_clause that we render)
40             my @name_defs
41 1         9 = map +({ -ident => $_->{-name} }, { -keyword => 'AS' }, '(', $_->{-definition}, ')', ','),
42             $value->@*;
43 1         2 pop @name_defs; # remove the last comma
44 1         6 $sqla->join_query_parts(' ', { -keyword => 'window' }, @name_defs);
45             },
46 9         658 );
47              
48 3         8 $sqla->expanders(
49 3     3   6 agg => sub ($sqla, $name, $value) {
  3         849  
  3         8  
  3         6  
50 3         6 my %parts;
51             # we must make a clone b/c we actually mutate the user's args otherwise (:gasp:)
52 3         15 my $clone = { $value->%* };
53 3         24 $parts{$_} = delete $clone->{"-$_"} for qw/func filter over/;
54              
55             # if they decided to pass a named func rather than -func, then transform it here
56 3 50       16 unless ($parts{func}) {
57 3         10 my ($name) = keys $clone->%*;
58 3         11 $parts{func} = [ $name =~ s/^-//r ];
59 3         26 my $args = $value->{$name};
60 3 50       12 if (ref $args eq 'ARRAY') {
61 3         14 push $parts{func}->@*, $args->@*;
62             } else {
63 0         0 push $parts{func}->@*, $args;
64             }
65             }
66              
67 3         70 my @expanded = map $sqla->expand_expr({ "-$_" => $parts{$_} }), grep $parts{$_}, qw/func filter over/;
68 3         425 return { -agg => \@expanded };
69              
70             },
71 1     1   7 filter => sub ($sqla, $name, $value) {
  1         766  
  1         4  
  1         3  
  1         50  
72             # NOTE - we have to manually provide the default of -value, b/c we're in the
73             # SELECT clause which defaults scalar RHS to -ident
74 1         9 return { -filter => $sqla->expand_expr($value, -value) };
75             },
76 2     2   3 over => sub ($sqla, $name, $value) {
  2         506  
  2         5  
  2         3  
  2         5  
77             # if it's a string, we'll just render it straight as the name of a window
78 2 100       5 if (!ref $value) {
79 1         10 return { -over => { -ident => $value } };
80             }
81 1         5 return { -over => $sqla->expand_expr({ -window => $value }) };
82             },
83 2     2   4 window => sub ($sqla, $name, $value) {
  2         129  
  2         4  
  2         4  
  2         2  
84             # 4 opts: base, order_by, partition_by, frame
85 2 50       16 if (ref $value eq 'ARRAY') {
86 0         0 return { -window => $value };
87             }
88 2         6 my %expanded;
89 2         9 my %is_list = map +($_ => 1), qw/partition_by/;
90             (undef, $expanded{order_by}) = $sqla->_expand_select_clause_order_by('select.order_by', $value->{order_by})
91 2 50       19 if $value->{order_by};
92 2         485 for my $part (qw/base partition_by frame/) {
93 6 50       29 next unless $value->{$part};
94 0         0 my $prepared;
95 0 0       0 $prepared = $sqla->expand_expr({ -list => $value->{$part} }, -ident) if $is_list{$part};
96 0   0     0 $prepared ||= $sqla->expand_expr({ -ident => $value->{$part} }, -ident);
97 0         0 $expanded{$part} = $prepared;
98             }
99 2         26 return { -window => \%expanded };
100             }
101 9         778 );
102 1         2 $sqla->renderers(
103 1     1   2 filter => sub ($sqla, $name, $value) {
  1         198  
  1         3  
  1         2  
104 1         5 $sqla->join_query_parts(' ', { -keyword => 'filter' }, '(', { -keyword => 'where' }, $value, ')');
105             },
106 3     3   4 agg => sub ($sqla, $name, $value) {
  3         740  
  3         7  
  3         4  
  3         7  
107 3         9 $sqla->join_query_parts(' ', $value->@*);
108             },
109 2     2   3 over => sub ($sqla, $name, $value) {
  2         270  
  2         4  
  2         3  
  2         5  
110 2 100       11 return $sqla->join_query_parts(' ', { -keyword => 'over' }, $value) if $value->{-ident};
111 1         5 return $sqla->join_query_parts(' ', { -keyword => 'over' }, '(', $value, ')');
112             },
113 2     2   4 window => sub ($sqla, $name, $value) {
  2         151  
  2         4  
  2         4  
  2         3  
114 2         3 my @parts;
115 2         11 my %has_keyword = map +($_ => 1), qw/order_by partition_by/;
116 2         5 for my $part (qw/base partition_by order_by frame/) {
117 8 100       29 next unless $value->{$part};
118 2 50       13 push @parts, { -keyword => $part =~ s/_/ /r } if $has_keyword{$part};
119 2         5 push @parts, $value->{$part};
120             }
121 2         5 $sqla->join_query_parts(' ', @parts);
122             }
123 9         1148 );
124              
125             }
126              
127             1;
128              
129             =encoding utf8
130              
131             =head1 NAME
132              
133             SQL::Abstract::Plugin::WindowFunctions - Window Function support for SQLA2!
134              
135             =head1 SYNOPSIS
136              
137             # pass this to an SQLA select list
138             { -agg => {
139             row_number => [],
140             -over => {
141             order_by => { -desc => 'age' },
142             partition_by => [ 'name', 'job' ]
143             },
144             -filter => { employed => 1, salary => { '>' => 9000 } }
145             }
146             }
147             # row_number() FILTER (WHERE employed = ? AND salary > ?) OVER (PARTITION BY name, job ORDER BY age DESC)
148             # [1, 9000]
149            
150             # You can use a window name in the -over definition
151             # to pass in a window clause in DBIC (this is a thing, you know), you need to use a bang override
152             $rs->search(undef, {
153             columns => [{ # just the shortest way to specify select columns
154             # note the hashref-ref; this is how we enable SQLA2 handling for select columns
155             that_count => \{ -agg => {
156             count => ['*'],
157             -over => 'some_complex_window'
158             }, -as => 'that_count' }
159             }],
160             '!window' => [
161             parent_window => {
162             order_by => [qw/column1 column2/, {-asc => 'staircase'}],
163             },
164             some_complex_window => {
165             base => 'parent_window',
166             partition_by => ['things', 'stuff'],
167             frame => 'rows between 1 preceding and 7 following',
168             }
169             ]
170             })
171             # SELECT count(*) OVER some_complex_window AS that_count
172             # FROM rs me
173             # WINDOW parent_window AS (ORDER BY column1, columns2, staircase ASC)
174             # some_complex_window AS (parent_window PARTITION BY things, stuff ASC rows between 1 preceding and 7 following)
175             #
176              
177             =head1 DESCRIPTION
178              
179             This is a work in progress to support advanced window (and aggregate) functions in SQLA2.
180              
181             B
182              
183             =head2 Using with DBIx::Class
184              
185             In order to use this with DBIx::Class, you simply need to apply the DBIC-SQLA2 plugin, and
186             then your SQLMaker will support this syntax!
187              
188             Just some notes: in order to use the new -agg node in a select list in DBIC, you must pass
189             it as a hashref-ref in order to activate the SQLA2 handling.
190              
191             In order to pass in a window clause, you set it as an RS attribute prefixed with a '!' so
192             that it gets rendered.
193              
194             =head2 New Syntax
195              
196             =head3 -agg node
197              
198             The main entry point for the new handling is the -agg node. This takes two possible options, -filter and -over. The remaining key in the hash is assumed to be the name of the function being called.
199              
200             =head3 -filter node
201              
202             This is what generates the FILTER clause for the function call. It parses the arguments passed in as if they were being passed to a WHERE clause for the query.
203              
204             =head3 -over node
205              
206             This node handles the definition of the actual window. It takes a hashref of 0-4 named keys, or a plain string.
207              
208             In the event that you pass a string, it renders as the name of a window from the WINDOW clause (see below for more details).
209              
210             If it's a hashref, then the following keys are processed:
211              
212             =over 4
213              
214             =item base
215              
216             This is the parent window. It is a named window from the WINDOW clause, and you can define modifications in this window. Make sure to check if your DB actually supports this, and under what circumstances.
217              
218             =item order_by
219              
220             This is the order_by for the window. It gets parsed like the ORDER BY clause of a SELECT statment, meaning that you can use the special ordering form { -desc => 'column_name' }.
221              
222             =item partition_by
223              
224             This defines the "grouping" for your window function. It is parsed as any other list of columns names, so you should have roughly infinite power here.
225              
226             =item frame
227              
228             This defines the frame for the window. The syntax is so specific that there are no helpers, the string you pass here gets rendered directly. This may change in the future, of course.
229              
230             =back
231              
232             =head3 WINDOW clauses
233              
234             As shown in the synopsis, you define windows in the WINDOW clause of a SELECT by passing an array (b/c order matters) of pairs of name/window definition. You can be more explicit and pass an array of hashrefs with the keys -name and -definition.
235              
236             The definition is processed as an -over node, so see above for details.
237              
238              
239             =cut