1#!/usr/bin/perl 2 3use strict; 4use warnings; 5use Test::More; 6use Test::Exception; 7use SQL::Abstract::Test import => ['is_same_sql_bind']; 8 9use Data::Dumper; 10use SQL::Abstract; 11 12my @in_between_tests = ( 13 { 14 where => { x => { -between => [1, 2] } }, 15 stmt => 'WHERE (x BETWEEN ? AND ?)', 16 bind => [qw/1 2/], 17 test => '-between with two placeholders', 18 }, 19 { 20 where => { x => { -between => [\"1", 2] } }, 21 stmt => 'WHERE (x BETWEEN 1 AND ?)', 22 bind => [qw/2/], 23 test => '-between with one literal sql arg and one placeholder', 24 }, 25 { 26 where => { x => { -between => [1, \"2"] } }, 27 stmt => 'WHERE (x BETWEEN ? AND 2)', 28 bind => [qw/1/], 29 test => '-between with one placeholder and one literal sql arg', 30 }, 31 { 32 where => { x => { -between => [\'current_date - 1', \'current_date - 0'] } }, 33 stmt => 'WHERE (x BETWEEN current_date - 1 AND current_date - 0)', 34 bind => [], 35 test => '-between with two literal sql arguments', 36 }, 37 { 38 where => { x => { -between => [ \['current_date - ?', 1], \['current_date - ?', 0] ] } }, 39 stmt => 'WHERE (x BETWEEN current_date - ? AND current_date - ?)', 40 bind => [1, 0], 41 test => '-between with two literal sql arguments with bind', 42 }, 43 { 44 where => { x => { -between => \['? AND ?', 1, 2] } }, 45 stmt => 'WHERE (x BETWEEN ? AND ?)', 46 bind => [1,2], 47 test => '-between with literal sql with placeholders (\["? AND ?", scalar, scalar])', 48 }, 49 { 50 where => { x => { -between => \["'something' AND ?", 2] } }, 51 stmt => "WHERE (x BETWEEN 'something' AND ?)", 52 bind => [2], 53 test => '-between with literal sql with one literal arg and one placeholder (\["\'something\' AND ?", scalar])', 54 }, 55 { 56 where => { x => { -between => \["? AND 'something'", 1] } }, 57 stmt => "WHERE (x BETWEEN ? AND 'something')", 58 bind => [1], 59 test => '-between with literal sql with one placeholder and one literal arg (\["? AND \'something\'", scalar])', 60 }, 61 { 62 where => { x => { -between => \"'this' AND 'that'" } }, 63 stmt => "WHERE (x BETWEEN 'this' AND 'that')", 64 bind => [], 65 test => '-between with literal sql with a literal (\"\'this\' AND \'that\'")', 66 }, 67 { 68 where => { 69 start0 => { -between => [ 1, 2 ] }, 70 start1 => { -between => \["? AND ?", 1, 2] }, 71 start2 => { -between => \"lower(x) AND upper(y)" }, 72 start3 => { -between => [ 73 \"lower(x)", 74 \["upper(?)", 'stuff' ], 75 ] }, 76 }, 77 stmt => "WHERE ( 78 ( start0 BETWEEN ? AND ? ) 79 AND ( start1 BETWEEN ? AND ? ) 80 AND ( start2 BETWEEN lower(x) AND upper(y) ) 81 AND ( start3 BETWEEN lower(x) AND upper(?) ) 82 )", 83 bind => [1, 2, 1, 2, 'stuff'], 84 test => '-between POD test', 85 }, 86 87 { 88 parenthesis_significant => 1, 89 where => { x => { -in => [ 1 .. 3] } }, 90 stmt => "WHERE ( x IN (?, ?, ?) )", 91 bind => [ 1 .. 3], 92 test => '-in with an array of scalars', 93 }, 94 { 95 parenthesis_significant => 1, 96 where => { x => { -in => [] } }, 97 stmt => "WHERE ( 0=1 )", 98 bind => [], 99 test => '-in with an empty array', 100 }, 101 { 102 parenthesis_significant => 1, 103 where => { x => { -in => \'( 1,2,lower(y) )' } }, 104 stmt => "WHERE ( x IN ( 1,2,lower(y) ) )", 105 bind => [], 106 test => '-in with a literal scalarref', 107 }, 108 { 109 parenthesis_significant => 1, 110 where => { x => { -in => \['( ( ?,?,lower(y) ) )', 1, 2] } }, 111 stmt => "WHERE ( x IN ( ?,?,lower(y) ) )", # note that outer parens are opened even though literal was requested (RIBASUSHI) 112 bind => [1, 2], 113 test => '-in with a literal arrayrefref', 114 }, 115 { 116 parenthesis_significant => 1, 117 where => { 118 customer => { -in => \[ 119 'SELECT cust_id FROM cust WHERE balance > ?', 120 2000, 121 ]}, 122 status => { -in => \'SELECT status_codes FROM states' }, 123 }, 124 stmt => " 125 WHERE (( 126 customer IN ( SELECT cust_id FROM cust WHERE balance > ? ) 127 AND status IN ( SELECT status_codes FROM states ) 128 )) 129 ", 130 bind => [2000], 131 test => '-in POD test', 132 }, 133); 134 135plan tests => @in_between_tests*4; 136 137for my $case (@in_between_tests) { 138 TODO: { 139 local $TODO = $case->{todo} if $case->{todo}; 140 local $SQL::Abstract::Test::parenthesis_significant = $case->{parenthesis_significant}; 141 142 local $Data::Dumper::Terse = 1; 143 144 lives_ok (sub { 145 146 my @w; 147 local $SIG{__WARN__} = sub { push @w, @_ }; 148 my $sql = SQL::Abstract->new ($case->{args} || {}); 149 lives_ok (sub { 150 my ($stmt, @bind) = $sql->where($case->{where}); 151 is_same_sql_bind( 152 $stmt, 153 \@bind, 154 $case->{stmt}, 155 $case->{bind}, 156 ) 157 || diag "Search term:\n" . Dumper $case->{where}; 158 }); 159 is (@w, 0, $case->{test} || 'No warnings within in-between tests') 160 || diag join "\n", 'Emitted warnings:', @w; 161 }, "$case->{test} doesn't die"); 162 } 163} 164