5 use lib "$FindBin::Bin/../../lib";
6 use Test::SQL::Abstract::Util qw/
13 use SQL::Abstract::Compat;
15 use Test::More tests => 17;
16 use Test::Differences;
18 ok(my $visitor = SQL::Abstract::Compat->new);
21 my $foo_id = { -type => 'identifier', elements => [qw/foo/] };
22 my $bar_id = { -type => 'identifier', elements => [qw/bar/] };
24 my $foo_eq_1 = field_op_value($foo_id, '==', 1);
25 my $bar_eq_str = field_op_value($bar_id, '==', 'some str');
28 $visitor->recurse_where({ foo => 1 }),
35 $visitor->recurse_where({ foo => 1, bar => 'some str' }),
46 $visitor->recurse_where({ -or => { foo => 1, bar => 'some str' } }),
58 $visitor->recurse_where([ -and => { foo => 1, bar => 'some str' } ]),
66 "-and as first element of array";
70 $visitor->recurse_where([ -and => { foo => 1, bar => 'some str' }, { foo => 1} ]),
84 "-and as first element of array + hash";
87 $visitor->recurse_where({ foo => { '!=' => 'bar' } }),
88 field_op_value($foo_id, '!=', 'bar'),
89 "foo => { '!=' => 'bar' }";
92 $visitor->recurse_where({ foo => [ 1, 'bar' ] }),
97 field_op_value($foo_id, '==', 'bar'),
100 "foo => [ 1, 'bar' ]";
103 $visitor->recurse_where({ foo => { -in => [ 1, 'bar' ] } }),
108 { -type => 'value', value => 1 },
109 { -type => 'value', value => 'bar' },
112 "foo => { -in => [ 1, 'bar' ] }";
115 $visitor->recurse_where({ foo => { -not_in => [ 1, 'bar' ] } }),
120 { -type => 'value', value => 1 },
121 { -type => 'value', value => 'bar' },
124 "foo => { -not_in => [ 1, 'bar' ] }";
127 $visitor->recurse_where({ foo => { -in => [ ] } }),
134 "foo => { -in => [ ] }";
136 my $worker_eq = sub {
141 { -type => 'identifier', elements => ['worker'] },
142 { -type => 'value', value => $_[0] },
148 $visitor->recurse_where( {
150 worker => ['nwiger', 'rcwe', 'sfz'],
151 status => { '!=', 'completed' }
156 field_op_value(qw/requestor == inna/),
157 field_op_value(qw/status != completed/),
161 field_op_value(qw/worker == nwiger/),
162 field_op_value(qw/worker == rcwe/),
163 field_op_value(qw/worker == sfz/),
171 $visitor->convert('UPPER');
177 field_op_value( upper(mk_name('ticket')), '==', upper(mk_value(11))),
178 field_op_value( upper(mk_name('ticket')), '==', upper(mk_value(12))),
179 field_op_value( upper(mk_name('ticket')), '==', upper(mk_value(13))),
184 $visitor->select_ast(
185 'test', '*', [ { ticket => [11, 12, 13] } ]
188 columns => [ { -type => 'identifier', elements => ['*'] } ],
189 tablespec => { -type => 'identifier', elements => ['test'] },
190 where => $ticket_or_eq
192 "Complex AST with convert('UPPER')";
194 my $hostname_and_ticket = {
198 field_op_value( upper(mk_name('hostname')),
201 } qw/ntf avd bvd 123/ ]
208 $visitor->select_ast(
209 'test', '*', [ { ticket => [11, 12, 13],
210 hostname => { in => ['ntf', 'avd', 'bvd', '123'] }
215 columns => [ { -type => 'identifier', elements => ['*'] } ],
216 tablespec => { -type => 'identifier', elements => ['test'] },
217 where => $hostname_and_ticket
219 "Complex AST mixing arrays+hashes with convert('UPPER')";
225 upper(mk_name('tack')),
226 upper(mk_value('tick')),
227 upper(mk_value('tock')),
232 $visitor->select_ast(
233 'test', '*', [ { ticket => [11, 12, 13],
234 hostname => { in => ['ntf', 'avd', 'bvd', '123'] }
236 { tack => { between => [qw/tick tock/] } }
240 columns => [ { -type => 'identifier', elements => ['*'] } ],
241 tablespec => { -type => 'identifier', elements => ['test'] },
246 $hostname_and_ticket,
251 "Complex AST mixing [ {a => [1,2],b => 3}, { c => 4 }]";
257 { -type => 'expr', op => '==', args => [ upper(mk_name('a')), upper(mk_value($_)) ] }
265 { -type => 'expr', op => '!=', args => [ upper(mk_name('e')), upper(mk_value($_)) ] }
270 $visitor->select_ast(
271 'test', '*', [ { ticket => [11, 12, 13],
272 hostname => { in => ['ntf', 'avd', 'bvd', '123'] }
274 { tack => { between => [qw/tick tock/] } },
276 e => { '!=', [qw(f g)] },
281 columns => [ { -type => 'identifier', elements => ['*'] } ],
282 tablespec => { -type => 'identifier', elements => ['test'] },
287 $hostname_and_ticket,
289 { -type => 'expr', op => 'and', args => [ $a_or_eq, $e_ne ] }
293 "Complex AST mixing [ {a => [1,2],b => 3}, { c => 4 }, { d => [5,6,7], e => { '!=' => [8,9] } } ]";
297 $visitor->select_ast(
298 'test', '*', [ { ticket => [11, 12, 13],
299 hostname => { in => ['ntf', 'avd', 'bvd', '123'] } },
300 { tack => { between => [qw/tick tock/] } },
302 e => { '!=', [qw(f g)] },
303 q => { 'not in', [14..20] }
308 columns => [ { -type => 'identifier', elements => ['*'] } ],
309 tablespec => { -type => 'identifier', elements => ['test'] },
314 $hostname_and_ticket,
316 { -type => 'expr', op => 'and', args => [
323 map { upper(mk_value($_)) } 14..20
330 "Complex AST [ {a => [1,2],b => 3}, { c => 4 }, { d => [5,6,7], e => { '!=' => [8,9] }, q => {'not in' => [10,11] } } ]";
333 sub upper { expr(UPPER => @_) }
336 my ($op, @args) = @_;