X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F01generate.t;h=2746b600861a44ea89206f41e3e8caf5e57eba6d;hb=46be4313ffbb0b347d7336608e115d8a941df4f3;hp=5855f8044236ff55ee973a0cab7f5dcb72c18440;hpb=6e9a377bd3915ce41ee6a7d5e8db20928f852379;p=dbsrgits%2FSQL-Abstract.git diff --git a/t/01generate.t b/t/01generate.t index 5855f80..2746b60 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', @@ -62,20 +69,6 @@ my @tests = ( }, { func => 'select', - args => [[qw/test1 test2/], '*', { 'test1.a' => { 'In', ['boom', 'bang'] } }], - stmt => 'SELECT * FROM test1, test2 WHERE ( test1.a IN ( ?, ? ) )', - stmt_q => 'SELECT * FROM `test1`, `test2` WHERE ( `test1`.`a` IN ( ?, ? ) )', - bind => ['boom', 'bang'] - }, - { - func => 'select', - args => ['test', '*', { a => { 'between', ['boom', 'bang'] } }], - stmt => 'SELECT * FROM test WHERE ( a BETWEEN ? AND ? )', - stmt_q => 'SELECT * FROM `test` WHERE ( `a` BETWEEN ? AND ? )', - bind => ['boom', 'bang'] - }, - { - func => 'select', args => ['test', '*', { a => { '!=', 'boom' } }], stmt => 'SELECT * FROM test WHERE ( a != ? )', stmt_q => 'SELECT * FROM `test` WHERE ( `a` != ? )', @@ -120,6 +113,13 @@ my @tests = ( bind => ['nwiger'] }, { + func => 'select', + args => [[\'test1', 'test2'], '*', { 'test1.a' => 'boom' } ], + stmt => 'SELECT * FROM test1, test2 WHERE ( test1.a = ? )', + stmt_q => 'SELECT * FROM test1, `test2` WHERE ( `test1`.`a` = ? )', + bind => ['boom'] + }, + { func => 'insert', args => ['test', {a => 1, b => 2, c => 3, d => 4, e => 5}], stmt => 'INSERT INTO test (a, b, c, d, e) VALUES (?, ?, ?, ?, ?)', @@ -225,6 +225,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,10 +246,6 @@ 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] } }, @@ -268,11 +266,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 +323,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 +389,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', @@ -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', @@ -529,23 +527,152 @@ my @tests = ( stmt_q => 'SELECT * FROM `test` WHERE ( `Y` = ( MAX( LENGTH( MIN ? ) ) ) )', bind => [[Y => 'x']], }, + { + func => 'select', + args => ['test', '*', { a => { '=' => undef }, b => { -is => undef }, c => { -like => undef } }], + stmt => 'SELECT * FROM test WHERE ( a IS NULL AND b IS NULL AND c IS NULL )', + stmt_q => 'SELECT * FROM `test` WHERE ( `a` IS NULL AND `b` IS NULL AND `c` IS NULL )', + bind => [], + warns => qr/\QSupplying an undefined argument to 'LIKE' is deprecated/, + }, + { + func => 'select', + args => ['test', '*', { a => { '!=' => undef }, b => { -is_not => undef }, c => { -not_like => undef } }], + stmt => 'SELECT * FROM test WHERE ( a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL )', + stmt_q => 'SELECT * FROM `test` WHERE ( `a` IS NOT NULL AND `b` IS NOT NULL AND `c` IS NOT NULL )', + bind => [], + warns => qr/\QSupplying an undefined argument to 'NOT LIKE' is deprecated/, + }, + { + func => 'select', + args => ['test', '*', { a => { IS => undef }, b => { LIKE => undef } }], + stmt => 'SELECT * FROM test WHERE ( a IS NULL AND b IS NULL )', + stmt_q => 'SELECT * FROM `test` WHERE ( `a` IS NULL AND `b` IS NULL )', + bind => [], + warns => qr/\QSupplying an undefined argument to 'LIKE' is deprecated/, + }, + { + func => 'select', + args => ['test', '*', { a => { 'IS NOT' => undef }, b => { 'NOT LIKE' => undef } }], + stmt => 'SELECT * FROM test WHERE ( a IS NOT NULL AND b IS NOT NULL )', + stmt_q => 'SELECT * FROM `test` WHERE ( `a` IS NOT NULL AND `b` IS NOT NULL )', + bind => [], + warns => qr/\QSupplying an undefined argument to 'NOT LIKE' is deprecated/, + }, + { + func => 'select', + args => ['`test``table`', ['`test``column`']], + stmt => 'SELECT `test``column` FROM `test``table`', + stmt_q => 'SELECT ```test````column``` FROM ```test````table```', + bind => [], + }, + { + func => 'select', + args => ['`test\\`table`', ['`test`\\column`']], + stmt => 'SELECT `test`\column` FROM `test\`table`', + stmt_q => 'SELECT `\`test\`\\\\column\`` FROM `\`test\\\\\`table\``', + esc => '\\', + bind => [], + }, ); +# check is( not) => undef +for my $op ( qw(not 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; + $sop = 'IS NOT' if $sop eq 'NOT'; -for my $t (@tests) { - local $"=', '; + for my $uc (0, 1) { + for my $prefix ('', '-') { + push @tests, { + func => 'where', + args => [{ a => { ($prefix . ($uc ? uc $op : lc $op) ) => undef } }], + stmt => "WHERE a $sop NULL", + stmt_q => "WHERE `a` $sop NULL", + bind => [], + }; + } + } +} +# 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) { - my $maker = SQL::Abstract->new(%$new, $quoted - ? (quote_char => '`', name_sep => '.') - : () + my $maker = SQL::Abstract->new( + %$new, + ($quoted ? ( + quote_char => '`', + name_sep => '.', + ( $t->{esc} ? ( + escape_char => $t->{esc}, + ) : ()) + ) : ()) ); my($stmt, @bind); @@ -555,23 +682,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})" - ); - } else { - if ($t->{warning_like}) { - warning_like( - sub { $cref->() }, - $t->{warning_like}, - "issues the expected warning ($t->{warning_like})" - ); - } - else { - $cref->(); - } + $e, + ) || diag dumper ({ args => $t->{args}, result => $stmt }); + } + else { + warnings_like( + sub { $cref->() }, + $t->{warns} || [], + ) || diag dumper ({ args => $t->{args}, result => $stmt }); + is_same_sql_bind( $stmt, \@bind, @@ -581,3 +703,5 @@ for my $t (@tests) { } } } + +done_testing;