From: Peter Rabbitson Date: Tue, 5 May 2009 12:16:38 +0000 (+0000) Subject: Switch some tests to explicit parenthesis comparison X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Abstract.git;a=commitdiff_plain;h=refs%2Fheads%2Fabandoned%2Fdeprecate_nest Switch some tests to explicit parenthesis comparison --- diff --git a/t/01generate.t b/t/01generate.t index 9606a8c..b10489b 100644 --- a/t/01generate.t +++ b/t/01generate.t @@ -240,10 +240,11 @@ my @tests = ( { func => 'select', args => ['Yo Momma', '*', { user => 'nwiger', - -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ] }], - stmt => 'SELECT * FROM Yo Momma WHERE ( ( ( workhrs > ? ) OR ( geo = ? ) ) AND user = ? )', - stmt_q => 'SELECT * FROM `Yo Momma` WHERE ( ( ( `workhrs` > ? ) OR ( `geo` = ? ) ) AND `user` = ? )', + -paren => [ workhrs => {'>', 20}, geo => 'ASIA' ] }], + stmt => 'SELECT * FROM Yo Momma WHERE ( ( ( workhrs > ? OR geo = ? ) AND user = ? ) )', + stmt_q => 'SELECT * FROM `Yo Momma` WHERE ( ( ( `workhrs` > ? OR `geo` = ? ) AND `user` = ? ) )', bind => [qw(20 ASIA nwiger)], + keep_paren => 1, }, #28 { @@ -251,25 +252,27 @@ my @tests = ( args => ['taco_punches', { one => 2, three => 4 }, { bland => [ -and => {'!=', 'yes'}, {'!=', 'YES'} ], tasty => { '!=', [qw(yes YES)] }, - -nest => [ face => [ -or => {'=', 'mr.happy'}, {'=', undef} ] ] }, + -paren => [ face => [ -or => {'=', 'mr.happy'}, {'=', undef} ] ] }, ], stmt => 'UPDATE taco_punches SET one = ?, three = ? WHERE ( ( ( ( ( face = ? ) OR ( face IS NULL ) ) ) )' . ' AND ( ( bland != ? ) AND ( bland != ? ) ) AND ( ( tasty != ? ) OR ( tasty != ? ) ) )', stmt_q => 'UPDATE `taco_punches` SET `one` = ?, `three` = ? WHERE ( ( ( ( ( `face` = ? ) OR ( `face` IS NULL ) ) ) )' . ' AND ( ( `bland` != ? ) AND ( `bland` != ? ) ) AND ( ( `tasty` != ? ) OR ( `tasty` != ? ) ) )', bind => [qw(2 4 mr.happy yes YES yes YES)], + keep_paren => 1, }, #29 { func => 'select', args => ['jeff', '*', { name => {'like', '%smith%', -not_in => ['Nate','Jim','Bob','Sally']}, - -nest => [ -or => [ -and => [age => { -between => [20,30] }, age => {'!=', 25} ], + -paren => [ -or => [ -and => [age => { -between => [20,30] }, age => {'!=', 25} ], yob => {'<', 1976} ] ] } ], - stmt => 'SELECT * FROM jeff WHERE ( ( ( ( ( ( ( age BETWEEN ? AND ? ) AND ( age != ? ) ) ) OR ( yob < ? ) ) ) )' - . ' AND name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? )', - stmt_q => 'SELECT * FROM `jeff` WHERE ( ( ( ( ( ( ( `age` BETWEEN ? AND ? ) AND ( `age` != ? ) ) ) OR ( `yob` < ? ) ) ) )' - . ' AND `name` NOT IN ( ?, ?, ?, ? ) AND `name` LIKE ? )', - bind => [qw(20 30 25 1976 Nate Jim Bob Sally %smith%)] + stmt => 'SELECT * FROM jeff WHERE ( ( ( ( ( age BETWEEN ? AND ? ) AND age != ? ) OR yob < ? )' + . ' AND ( name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? ) ) )', + stmt_q => 'SELECT * FROM `jeff` WHERE ( ( ( ( ( `age` BETWEEN ? AND ? ) AND `age` != ?) OR `yob` < ? )' + . ' AND ( `name` NOT IN ( ?, ?, ?, ? ) AND `name` LIKE ? ) ) )', + bind => [qw(20 30 25 1976 Nate Jim Bob Sally %smith%)], + keep_paren => 1, }, #30 { @@ -279,13 +282,15 @@ my @tests = ( # acked by RIBASUSHI # args => ['fhole', {fpoles => 4}, [-maybe => {race => [-and => [qw(black white asian)]]}, args => ['fhole', {fpoles => 4}, [ {race => [-and => [qw(black white asian)]]}, - {-nest => {firsttime => [-or => {'=','yes'}, undef]}}, + {-paren => {firsttime => [-or => {'=','yes'}, undef]}}, [ -and => {firstname => {-not_like => 'candace'}}, {lastname => {-in => [qw(jugs canyon towers)]}} ] ] ], - stmt => 'UPDATE fhole SET fpoles = ? WHERE ( ( ( ( ( ( ( race = ? ) OR ( race = ? ) OR ( race = ? ) ) ) ) ) )' + stmt => 'UPDATE fhole SET fpoles = ? WHERE ( ( ( ( ( ( race = ? OR ( race = ? ) OR ( race = ? ) ) ) ) ) )' . ' OR ( ( ( ( firsttime = ? ) OR ( firsttime IS NULL ) ) ) ) OR ( ( ( firstname NOT LIKE ? ) ) AND ( lastname IN ( ?, ?, ? ) ) ) )', stmt_q => 'UPDATE `fhole` SET `fpoles` = ? WHERE ( ( ( ( ( ( ( `race` = ? ) OR ( `race` = ? ) OR ( `race` = ? ) ) ) ) ) )' . ' OR ( ( ( ( `firsttime` = ? ) OR ( `firsttime` IS NULL ) ) ) ) OR ( ( ( `firstname` NOT LIKE ? ) ) AND ( `lastname` IN ( ?, ?, ? ) ) ) )', - bind => [qw(4 black white asian yes candace jugs canyon towers)] + bind => [qw(4 black white asian yes candace jugs canyon towers)], + keep_paren => 1, + }, #31 { @@ -556,6 +561,8 @@ for (@tests) { my $new = $_->{new} || {}; $new->{debug} = $ENV{DEBUG} || 0; + local $SQL::Abstract::Test::parenthesis_significant = $_->{keep_paren}; + # test without quoting labels { my $sql = SQL::Abstract->new(%$new); diff --git a/t/07subqueries.t b/t/07subqueries.t index d0564aa..c86251f 100644 --- a/t/07subqueries.t +++ b/t/07subqueries.t @@ -5,6 +5,7 @@ use warnings; use Test::More; use SQL::Abstract::Test import => ['is_same_sql_bind']; +$SQL::Abstract::Test::parenthesis_significant = 1; use SQL::Abstract; @@ -21,7 +22,7 @@ $where = { }; push @tests, { where => $where, - stmt => " WHERE ( bar IN (SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?) AND foo = ? )", + stmt => " WHERE ( ( bar IN (SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?) AND foo = ? ) )", bind => [100, "foo%", 1234], }; @@ -35,7 +36,7 @@ $where = { }; push @tests, { where => $where, - stmt => " WHERE ( bar > ALL (SELECT c1 FROM t1 WHERE ( c2 < ? AND c3 LIKE ? )) AND foo = ? )", + stmt => " WHERE ( ( bar > ALL (SELECT c1 FROM t1 WHERE ( c2 < ? AND c3 LIKE ? )) AND foo = ? ) )", bind => [100, "foo%", 1234], }; @@ -44,17 +45,17 @@ push @tests, { = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"}); $where = { foo => 1234, - -nest => \["EXISTS ($sub_stmt)" => @sub_bind], + -paren => \["EXISTS ($sub_stmt)" => @sub_bind], }; push @tests, { where => $where, - stmt => " WHERE ( EXISTS (SELECT * FROM t1 WHERE ( c1 = ? AND c2 > t0.c0 )) AND foo = ? )", + stmt => " WHERE ( ( EXISTS (SELECT * FROM t1 WHERE ( c1 = ? AND c2 > t0.c0 )) AND foo = ? ) )", bind => [1, 1234], }; #4 $where = { - -nest => \["MATCH (col1, col2) AGAINST (?)" => "apples"], + -paren => \["MATCH (col1, col2) AGAINST (?)" => "apples"], }; push @tests, { where => $where, @@ -69,11 +70,11 @@ push @tests, { $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause $where = { lname => {-like => '%son%'}, - -nest => \["NOT ( $sub_stmt )" => @sub_bind], + -paren => \["NOT ( $sub_stmt )" => @sub_bind], }; push @tests, { where => $where, - stmt => " WHERE ( NOT ( ( ( ( age < ? ) OR ( age > ? ) ) ) ) AND lname LIKE ? )", + stmt => " WHERE ( ( NOT ( ( ( ( age < ? ) OR ( age > ? ) ) ) ) AND lname LIKE ? ) )", bind => [10, 20, '%son%'], }; @@ -86,7 +87,7 @@ $where = { }; push @tests, { where => $where, - stmt => " WHERE ( bar IN (SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?) AND foo = ? )", + stmt => " WHERE ( ( bar IN (SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?) AND foo = ? ) )", bind => [100, "foo%", 1234], }; @@ -98,8 +99,3 @@ for (@tests) { my($stmt, @bind) = $sql->where($_->{where}, $_->{order}); is_same_sql_bind($stmt, \@bind, $_->{stmt}, $_->{bind}); } - - - - -