5 use lib "$FindBin::Bin/../../lib";
8 use SQL::Abstract::Compat;
10 use Test::More tests => 17;
11 use Test::Differences;
13 ok(my $visitor = SQL::Abstract::Compat->new);
16 my $foo_id = { -type => 'name', args => [qw/foo/] };
17 my $bar_id = { -type => 'name', args => [qw/bar/] };
19 my $foo_eq_1 = field_op_value($foo_id, '==', 1);
20 my $bar_eq_str = field_op_value($bar_id, '==', 'some str');
23 $visitor->recurse_where({ foo => 1 }),
30 $visitor->recurse_where({ foo => 1, bar => 'some str' }),
41 $visitor->recurse_where({ -or => { foo => 1, bar => 'some str' } }),
53 $visitor->recurse_where([ -and => { foo => 1, bar => 'some str' } ]),
61 "-and as first element of array";
65 $visitor->recurse_where([ -and => { foo => 1, bar => 'some str' }, { foo => 1} ]),
79 "-and as first element of array + hash";
82 $visitor->recurse_where({ foo => { '!=' => 'bar' } }),
83 field_op_value($foo_id, '!=', 'bar'),
84 "foo => { '!=' => 'bar' }";
87 $visitor->recurse_where({ foo => [ 1, 'bar' ] }),
92 field_op_value($foo_id, '==', 'bar'),
95 "foo => [ 1, 'bar' ]";
98 $visitor->recurse_where({ foo => { -in => [ 1, 'bar' ] } }),
103 { -type => 'value', value => 1 },
104 { -type => 'value', value => 'bar' },
107 "foo => { -in => [ 1, 'bar' ] }";
110 $visitor->recurse_where({ foo => { -not_in => [ 1, 'bar' ] } }),
115 { -type => 'value', value => 1 },
116 { -type => 'value', value => 'bar' },
119 "foo => { -not_in => [ 1, 'bar' ] }";
122 $visitor->recurse_where({ foo => { -in => [ ] } }),
129 "foo => { -in => [ ] }";
131 my $worker_eq = sub {
136 { -type => 'name', args => ['worker'] },
137 { -type => 'value', value => $_[0] },
143 $visitor->recurse_where( {
145 worker => ['nwiger', 'rcwe', 'sfz'],
146 status => { '!=', 'completed' }
151 field_op_value(qw/requestor == inna/),
152 field_op_value(qw/status != completed/),
156 field_op_value(qw/worker == nwiger/),
157 field_op_value(qw/worker == rcwe/),
158 field_op_value(qw/worker == sfz/),
166 $visitor->convert('UPPER');
172 field_op_value( upper(mk_name('ticket')), '==', upper(mk_value(11))),
173 field_op_value( upper(mk_name('ticket')), '==', upper(mk_value(12))),
174 field_op_value( upper(mk_name('ticket')), '==', upper(mk_value(13))),
179 $visitor->select_ast(
180 'test', '*', [ { ticket => [11, 12, 13] } ]
183 columns => [ { -type => 'name', args => ['*'] } ],
184 tablespec => { -type => 'name', args => ['test'] },
185 where => $ticket_or_eq
187 "Complex AST with convert('UPPER')";
189 my $hostname_and_ticket = {
193 field_op_value( upper(mk_name('hostname')),
196 } qw/ntf avd bvd 123/ ]
203 $visitor->select_ast(
204 'test', '*', [ { ticket => [11, 12, 13],
205 hostname => { in => ['ntf', 'avd', 'bvd', '123'] }
210 columns => [ { -type => 'name', args => ['*'] } ],
211 tablespec => { -type => 'name', args => ['test'] },
212 where => $hostname_and_ticket
214 "Complex AST mixing arrays+hashes with convert('UPPER')";
220 upper(mk_name('tack')),
221 upper(mk_value('tick')),
222 upper(mk_value('tock')),
227 $visitor->select_ast(
228 'test', '*', [ { ticket => [11, 12, 13],
229 hostname => { in => ['ntf', 'avd', 'bvd', '123'] }
231 { tack => { between => [qw/tick tock/] } }
235 columns => [ { -type => 'name', args => ['*'] } ],
236 tablespec => { -type => 'name', args => ['test'] },
241 $hostname_and_ticket,
246 "Complex AST mixing [ {a => [1,2],b => 3}, { c => 4 }]";
252 { -type => 'expr', op => '==', args => [ upper(mk_name('a')), upper(mk_value($_)) ] }
260 { -type => 'expr', op => '!=', args => [ upper(mk_name('e')), upper(mk_value($_)) ] }
265 $visitor->select_ast(
266 'test', '*', [ { ticket => [11, 12, 13],
267 hostname => { in => ['ntf', 'avd', 'bvd', '123'] }
269 { tack => { between => [qw/tick tock/] } },
271 e => { '!=', [qw(f g)] },
276 columns => [ { -type => 'name', args => ['*'] } ],
277 tablespec => { -type => 'name', args => ['test'] },
282 $hostname_and_ticket,
284 { -type => 'expr', op => 'and', args => [ $a_or_eq, $e_ne ] }
288 "Complex AST mixing [ {a => [1,2],b => 3}, { c => 4 }, { d => [5,6,7], e => { '!=' => [8,9] } } ]";
292 $visitor->select_ast(
293 'test', '*', [ { ticket => [11, 12, 13],
294 hostname => { in => ['ntf', 'avd', 'bvd', '123'] } },
295 { tack => { between => [qw/tick tock/] } },
297 e => { '!=', [qw(f g)] },
298 q => { 'not in', [14..20] }
303 columns => [ { -type => 'name', args => ['*'] } ],
304 tablespec => { -type => 'name', args => ['test'] },
309 $hostname_and_ticket,
311 { -type => 'expr', op => 'and', args => [
318 map { upper(mk_value($_)) } 14..20
325 "Complex AST [ {a => [1,2],b => 3}, { c => 4 }, { d => [5,6,7], e => { '!=' => [8,9] }, q => {'not in' => [10,11] } } ]";
328 my ($field, $op, $value) = @_;
330 $field = ref $field eq 'HASH'
332 : ref $field eq 'ARRAY'
333 ? { -type => 'name', args => $field }
334 : { -type => 'name', args => [$field] };
336 my @value = ref $value eq 'HASH'
338 : ref $value eq 'ARRAY'
340 : { -type => 'value', value => $value };
352 sub upper { expr(UPPER => @_) }
355 my ($op, @args) = @_;
366 $field = ref $field eq 'HASH'
368 : ref $field eq 'ARRAY'
369 ? { -type => 'name', args => $field }
370 : { -type => 'name', args => [$field] };
375 return { -type => 'value', value => $_[0] }