X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F01generate.t;h=5d68e1f0e0ab0ab5155343f5248583389c5b0da7;hb=970841131ca052eb8d4762dfd7a21205e24013aa;hp=f41f3147b34ec3c1494dfe7aa0c60714b15dbfe0;hpb=24c898dabc00b89c8c6ea2b65170a201272578fd;p=dbsrgits%2FSQL-Abstract.git diff --git a/t/01generate.t b/t/01generate.t index f41f314..5d68e1f 100644 --- a/t/01generate.t +++ b/t/01generate.t @@ -1,5 +1,3 @@ -#!/usr/bin/perl - use strict; use warnings; use Test::More; @@ -10,6 +8,15 @@ use SQL::Abstract::Test import => ['is_same_sql_bind']; use SQL::Abstract; +#### WARNING #### +# +# -nest has been undocumented on purpose, but is still supported for the +# foreseable future. Do not rip out the -nest tests before speaking to +# someone on the DBIC mailing list or in irc.perl.org#dbix-class +# +################# + + my @tests = ( { func => 'select', @@ -244,19 +251,15 @@ my @tests = ( }, { func => 'update', -# LDNOTE : removed the "-maybe", because we no longer admit unknown ops -# -# acked by RIBASUSHI -# args => ['fhole', {fpoles => 4}, [-maybe => {race => [-and => [qw(black white asian)]]}, args => ['fhole', {fpoles => 4}, [ { race => [qw/-or black white asian /] }, { -nest => { 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 = ? ) ) ) ) ) )' - . ' OR ( ( ( ( firsttime = ? ) OR ( firsttime IS NULL ) ) ) ) OR ( ( ( firstname NOT LIKE ? ) ) AND ( lastname IN ?, ?, ? ) ) )', + . ' 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 ?, ?, ? ) ) )', + . ' OR ( ( ( ( `firsttime` = ? ) OR ( `firsttime` IS NULL ) ) ) ) OR ( ( ( `firstname` NOT LIKE ? ) ) AND ( `lastname` IN( ?, ?, ? )) ) )', bind => [qw(4 black white asian yes candace jugs canyon towers)] }, { @@ -268,11 +271,6 @@ my @tests = ( }, { func => 'select', -# LDNOTE: modified test below because we agreed with MST that literal SQL -# should not automatically insert a '='; the user has to do it -# -# acked by MSTROUT -# args => ['test', '*', { a => \["to_date(?, 'MM/DD/YY')", '02/02/02']}], args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", '02/02/02']}], stmt => q{SELECT * FROM test WHERE ( a = to_date(?, 'MM/DD/YY') )}, stmt_q => q{SELECT * FROM `test` WHERE ( `a` = to_date(?, 'MM/DD/YY') )}, @@ -330,7 +328,7 @@ my @tests = ( stmt => 'INSERT INTO test (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)', stmt_q => 'INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES (?, ?, ?, ?, ?)', bind => [qw/1 2 3 4/, { answer => 42}], - warning_like => qr/HASH ref as bind value in insert is not supported/i, + warns => qr/HASH ref as bind value in insert is not supported/i, }, { func => 'update', @@ -396,25 +394,25 @@ my @tests = ( func => 'insert', new => {bindtype => 'columns'}, args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}], - exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, + throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, }, { func => 'update', new => {bindtype => 'columns'}, args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, {a => {'between', [1,2]}}], - exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, + throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, }, { func => 'select', new => {bindtype => 'columns'}, args => ['test', '*', { a => \["= to_date(?, 'MM/DD/YY')", '02/02/02']}], - exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, + throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, }, { func => 'select', new => {bindtype => 'columns'}, args => ['test', '*', { a => {'<' => \["to_date(?, 'MM/DD/YY')", '02/02/02']}, b => 8 }], - exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, + throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, }, { func => 'select', @@ -428,7 +426,7 @@ my @tests = ( func => 'select', new => {bindtype => 'columns'}, args => ['test', '*', { a => {-in => \["(SELECT d FROM to_date(?, 'MM/DD/YY') AS d)", '02/02/02']}, b => 8 }], - exception_like => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, + throws => qr/bindtype 'columns' selected, you need to pass: \[column_name => bind_value\]/, }, { func => 'insert', @@ -441,10 +439,10 @@ my @tests = ( { func => 'update', new => {bindtype => 'columns'}, - args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", [{dummy => 1} => '02/02/02']]}, {a => {'between', [1,2]}}], - stmt => 'UPDATE test SET a = ?, b = to_date(?, \'MM/DD/YY\') WHERE ( a BETWEEN ? AND ? )', - stmt_q => 'UPDATE `test` SET `a` = ?, `b` = to_date(?, \'MM/DD/YY\') WHERE ( `a` BETWEEN ? AND ? )', - bind => [[a => '1'], [{dummy => 1} => '02/02/02'], [a => '1'], [a => '2']], + args => ['test', {a => 1, b => \["to_date(?, 'MM/DD/YY')", [{dummy => 1} => '02/02/02']], c => { -lower => 'foo' }}, {a => {'between', [1,2]}}], + stmt => "UPDATE test SET a = ?, b = to_date(?, 'MM/DD/YY'), c = LOWER ? WHERE ( a BETWEEN ? AND ? )", + stmt_q => "UPDATE `test` SET `a` = ?, `b` = to_date(?, 'MM/DD/YY'), `c` = LOWER ? WHERE ( `a` BETWEEN ? AND ? )", + bind => [[a => '1'], [{dummy => 1} => '02/02/02'], [c => 'foo'], [a => '1'], [a => '2']], }, { func => 'select', @@ -529,17 +527,57 @@ my @tests = ( stmt_q => 'SELECT * FROM `test` WHERE ( `Y` = ( MAX( LENGTH( MIN ? ) ) ) )', bind => [[Y => 'x']], }, + { + func => 'select', + args => ['test', '*', { a => { -in => [] }, b => { -not_in => [] }, c => { -in => 42 } }], + stmt => 'SELECT * FROM test WHERE ( 0=1 AND 1=1 AND c IN ( ? ))', + stmt_q => 'SELECT * FROM `test` WHERE ( 0=1 AND 1=1 AND `c` IN ( ? ))', + bind => [ 42 ], + }, + { + func => 'select', + args => ['test', '*', { a => { -in => [] }, b => { -not_in => [] } }], + stmt => 'SELECT * FROM test WHERE ( 0=1 AND 1=1 )', + stmt_q => 'SELECT * FROM `test` WHERE ( 0=1 AND 1=1 )', + bind => [], + }, + { + throws => qr/ + \QSQL::Abstract before v1.75 used to generate incorrect SQL \E + \Qwhen the -IN operator was given an undef-containing list: \E + \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E + \Qversion of SQL::Abstract will emit the logically correct SQL \E + \Qinstead of raising this exception)\E + /x, + func => 'select', + args => ['test', '*', { a => { -in => [42, undef] }, b => { -not_in => [42, undef] } } ], + stmt => 'SELECT * FROM test WHERE ( ( a IN ( ? ) OR a IS NULL ) AND b NOT IN ( ? ) AND b IS NOT NULL )', + stmt_q => 'SELECT * FROM `test` WHERE ( ( `a` IN ( ? ) OR `a` IS NULL ) AND `b` NOT IN ( ? ) AND `b` IS NOT NULL )', + bind => [ 42, 42 ], + }, + { + throws => qr/ + \QSQL::Abstract before v1.75 used to generate incorrect SQL \E + \Qwhen the -IN operator was given an undef-containing list: \E + \Q!!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based \E + \Qversion of SQL::Abstract will emit the logically correct SQL \E + \Qinstead of raising this exception)\E + /x, + func => 'select', + args => ['test', '*', { a => { -in => [undef] }, b => { -not_in => [undef] } } ], + stmt => 'SELECT * FROM test WHERE ( a IS NULL AND b IS NOT NULL )', + stmt_q => 'SELECT * FROM `test` WHERE ( `a` IS NULL AND `b` IS NOT NULL )', + bind => [], + }, + { + func => 'select', + args => ['test', '*', { a => { -in => undef } }], + throws => qr/Argument passed to the 'IN' operator can not be undefined/, + }, ); - -plan tests => scalar(grep { !$_->{warning_like} } @tests) * 2 - + scalar(grep { $_->{warning_like} } @tests) * 4; - for my $t (@tests) { - local $"=', '; - my $new = $t->{new} || {}; - $new->{debug} = $ENV{DEBUG} || 0; for my $quoted (0, 1) { @@ -555,23 +593,18 @@ for my $t (@tests) { ($stmt, @bind) = $maker->$op (@ { $t->{args} } ); }; - if ($t->{exception_like}) { + if (my $e = $t->{throws}) { throws_ok( sub { $cref->() }, - $t->{exception_like}, - "throws the expected exception ($t->{exception_like})" + $e, ); - } else { - if ($t->{warning_like}) { - warning_like( - sub { $cref->() }, - $t->{warning_like}, - "issues the expected warning ($t->{warning_like})" - ); - } - else { - $cref->(); - } + } + else { + warnings_exist( + sub { $cref->() }, + $t->{warns} || [], + ); + is_same_sql_bind( $stmt, \@bind, @@ -581,3 +614,5 @@ for my $t (@tests) { } } } + +done_testing;