X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F01generate.t;h=833b2e3f8c4fda3c6db3bbd385e4272fe373620a;hb=b9b5a0b15e324b820975e789abdacb0e4285f4b9;hp=3252ddfcc659779c9c5eb7371b10d850caca6daa;hpb=4049d0e3d30cc93f9546a7d6f4b337fa6a739701;p=dbsrgits%2FSQL-Abstract.git diff --git a/t/01generate.t b/t/01generate.t index 3252ddf..833b2e3 100644 --- a/t/01generate.t +++ b/t/01generate.t @@ -1,15 +1,22 @@ -#!/usr/bin/perl - use strict; use warnings; use Test::More; use Test::Warn; use Test::Exception; -use SQL::Abstract::Test import => ['is_same_sql_bind']; +use SQL::Abstract::Test import => [qw( is_same_sql_bind diag_where dumper )]; 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', @@ -225,6 +232,8 @@ my @tests = ( tasty => { '!=', [qw(yes YES)] }, -nest => [ face => [ -or => {'=', 'mr.happy'}, {'=', undef} ] ] }, ], + warns => qr/\QA multi-element arrayref as an argument to the inequality op '!=' is technically equivalent to an always-true 1=1/, + 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 ) ) ) )' @@ -244,17 +253,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 => [-and => [qw(black white asian)]]}, - {-nest => {firsttime => [-or => {'=','yes'}, undef]}}, - [ -and => {firstname => {-not_like => 'candace'}}, {lastname => {-in => [qw(jugs canyon towers)]}} ] ] ], + 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)] }, { @@ -266,11 +273,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') )}, @@ -328,7 +330,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', @@ -394,25 +396,30 @@ 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', + args => ['test', '*', { foo => { '>=' => [] }} ], + throws => qr/\Qoperator '>=' applied on an empty array (field 'foo')/, }, { func => 'select', @@ -426,7 +433,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', @@ -439,10 +446,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', @@ -527,17 +534,134 @@ 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/, + }, ); +# check is( not) => undef +for my $op (qw( is is_not), 'is not' ) { + (my $sop = uc $op) =~ s/_/ /gi; -plan tests => scalar(grep { !$_->{warning_like} } @tests) * 2 - + scalar(grep { $_->{warning_like} } @tests) * 4; + push @tests, { + func => 'where', + args => [{ a => { "$_$op" => undef } }], + stmt => "WHERE a $sop NULL", + stmt_q => "WHERE `a` $sop NULL", + bind => [], + } for ('', '-'); # with and without - +} -for my $t (@tests) { - local $"=', '; +# check single-element inequality ops for no warnings +for my $op ( qw(!= <>) ) { + for my $val (undef, 42) { + push @tests, { + func => 'where', + args => [ { x => { "$_$op" => [ $val ] } } ], + stmt => "WHERE x " . ($val ? "$op ?" : 'IS NOT NULL'), + stmt_q => "WHERE `x` " . ($val ? "$op ?" : 'IS NOT NULL'), + bind => [ $val || () ], + } for ('', '-'); # with and without - + } +} + +# check single-element not-like ops for no warnings, and NULL exception +# (the last two "is not X" are a weird syntax, but mebbe a dialect...) +for my $op (qw(not_like not_rlike), 'not like', 'not rlike', 'is not like','is not rlike') { + (my $sop = uc $op) =~ s/_/ /gi; + + for my $val (undef, 42) { + push @tests, { + func => 'where', + args => [ { x => { "$_$op" => [ $val ] } } ], + $val ? ( + stmt => "WHERE x $sop ?", + stmt_q => "WHERE `x` $sop ?", + bind => [ $val ], + ) : ( + stmt => "WHERE x IS NOT NULL", + stmt_q => "WHERE `x` IS NOT NULL", + bind => [], + warns => qr/\QSupplying an undefined argument to '$sop' is deprecated/, + ), + } for ('', '-'); # with and without - + } +} + +# check all multi-element inequality/not-like ops for warnings +for my $op ( qw(!= <> not_like not_rlike), 'not like', 'not rlike', 'is not like','is not rlike') { + (my $sop = uc $op) =~ s/_/ /gi; + + push @tests, { + func => 'where', + args => [ { x => { "$_$op" => [ 42, 69 ] } } ], + stmt => "WHERE x $sop ? OR x $sop ?", + stmt_q => "WHERE `x` $sop ? OR `x` $sop ?", + bind => [ 42, 69 ], + warns => qr/\QA multi-element arrayref as an argument to the inequality op '$sop' is technically equivalent to an always-true 1=1/, + } for ('', '-'); # with and without - +} +# check all like/not-like ops for empty-arrayref warnings +for my $op ( qw(like rlike not_like not_rlike), 'not like', 'not rlike', 'is like', 'is not like', 'is rlike', 'is not rlike') { + (my $sop = uc $op) =~ s/_/ /gi; + + push @tests, { + func => 'where', + args => [ { x => { "$_$op" => [] } } ], + stmt => ( $sop =~ /NOT/ ? "WHERE 1=1" : "WHERE 0=1" ), + stmt_q => ( $sop =~ /NOT/ ? "WHERE 1=1" : "WHERE 0=1" ), + bind => [], + warns => qr/\QSupplying an empty arrayref to '$sop' is deprecated/, + } for ('', '-'); # with and without - +} + +for my $t (@tests) { my $new = $t->{new} || {}; - $new->{debug} = $ENV{DEBUG} || 0; for my $quoted (0, 1) { @@ -553,23 +677,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, + ) || diag dumper ({ args => $t->{args}, result => $stmt }); + } + else { + warnings_exist( + sub { $cref->() }, + $t->{warns} || [], ); - } else { - if ($t->{warning_like}) { - warning_like( - sub { $cref->() }, - $t->{warning_like}, - "issues the expected warning ($t->{warning_like})" - ); - } - else { - $cref->(); - } + is_same_sql_bind( $stmt, \@bind, @@ -579,3 +698,5 @@ for my $t (@tests) { } } } + +done_testing;