t/02where.t

   1 use strict;
   2 use warnings;
   3 use Test::More;
   4 use Test::Exception;
   5 use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where) ];
   6
   7 use SQL::Abstract;
   8
   9 my $not_stringifiable = bless {}, 'SQLA::NotStringifiable';
  10
  11 my @handle_tests = (
  12     {
  13         where => {
  14             requestor => 'inna',
  15             worker => ['nwiger', 'rcwe', 'sfz'],
  16             status => { '!=', 'completed' }
  17         },
  18         order => [],
  19         stmt => " WHERE ( requestor = ? AND status != ? AND ( ( worker = ? ) OR"
  20               . " ( worker = ? ) OR ( worker = ? ) ) )",
  21         bind => [qw/inna completed nwiger rcwe sfz/],
  22     },
  23
  24     {
  25         where  => [
  26             status => 'completed',
  27             user   => 'nwiger',
  28         ],
  29         stmt => " WHERE ( status = ? OR user = ? )",
  30         bind => [qw/completed nwiger/],
  31     },
  32
  33     {
  34         where  => {
  35             user   => 'nwiger',
  36             status => 'completed'
  37         },
  38         order => [qw/ticket/],
  39         stmt => " WHERE ( status = ? AND user = ? ) ORDER BY ticket",
  40         bind => [qw/completed nwiger/],
  41     },
  42
  43     {
  44         where  => {
  45             user   => 'nwiger',
  46             status => { '!=', 'completed' }
  47         },
  48         order => [qw/ticket/],
  49         stmt => " WHERE ( status != ? AND user = ? ) ORDER BY ticket",
  50         bind => [qw/completed nwiger/],
  51     },
  52
  53     {
  54         where  => {
  55             status   => 'completed',
  56             reportid => { 'in', [567, 2335, 2] }
  57         },
  58         order => [],
  59         stmt => " WHERE ( reportid IN ( ?, ?, ? ) AND status = ? )",
  60         bind => [qw/567 2335 2 completed/],
  61     },
  62
  63     {
  64         where  => {
  65             status   => 'completed',
  66             reportid => { 'not in', [567, 2335, 2] }
  67         },
  68         order => [],
  69         stmt => " WHERE ( reportid NOT IN ( ?, ?, ? ) AND status = ? )",
  70         bind => [qw/567 2335 2 completed/],
  71     },
  72
  73     {
  74         where  => {
  75             status   => 'completed',
  76             completion_date => { 'between', ['2002-10-01', '2003-02-06'] },
  77         },
  78         order => \'ticket, requestor',
  79         stmt => "WHERE ( ( completion_date BETWEEN ? AND ? ) AND status = ? ) ORDER BY ticket, requestor",
  80         bind => [qw/2002-10-01 2003-02-06 completed/],
  81     },
  82
  83     {
  84         where => [
  85             {
  86                 user   => 'nwiger',
  87                 status => { 'in', ['pending', 'dispatched'] },
  88             },
  89             {
  90                 user   => 'robot',
  91                 status => 'unassigned',
  92             },
  93         ],
  94         order => [],
  95         stmt => " WHERE ( ( status IN ( ?, ? ) AND user = ? ) OR ( status = ? AND user = ? ) )",
  96         bind => [qw/pending dispatched nwiger unassigned robot/],
  97     },
  98
  99     {
 100         where => {
 101             priority  => [ {'>', 3}, {'<', 1} ],
 102             requestor => \'is not null',
 103         },
 104         order => 'priority',
 105         stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor is not null ) ORDER BY priority",
 106         bind => [qw/3 1/],
 107     },
 108
 109     {
 110         where => {
 111             requestor => { '!=', ['-and', undef, ''] },
 112         },
 113         stmt => " WHERE ( requestor IS NOT NULL AND requestor != ? )",
 114         bind => [''],
 115     },
 116
 117     {
 118         where => {
 119             priority  => [ {'>', 3}, {'<', 1} ],
 120             requestor => { '!=', undef },
 121         },
 122         order => [qw/a b c d e f g/],
 123         stmt => " WHERE ( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor IS NOT NULL )"
 124               . " ORDER BY a, b, c, d, e, f, g",
 125         bind => [qw/3 1/],
 126     },
 127
 128     {
 129         where => {
 130             priority  => { 'between', [1, 3] },
 131             requestor => { 'like', undef },
 132         },
 133         order => \'requestor, ticket',
 134         stmt => " WHERE ( ( priority BETWEEN ? AND ? ) AND requestor IS NULL ) ORDER BY requestor, ticket",
 135         bind => [qw/1 3/],
 136     },
 137
 138
 139     {
 140         where => {
 141           id  => 1,
 142           num => {
 143            '<=' => 20,
 144            '>'  => 10,
 145           },
 146         },
 147         stmt => " WHERE ( id = ? AND ( num <= ? AND num > ? ) )",
 148         bind => [qw/1 20 10/],
 149     },
 150
 151     {
 152         where => { foo => {-not_like => [7,8,9]},
 153                    fum => {'like' => [qw/a b/]},
 154                    nix => {'between' => [100,200] },
 155                    nox => {'not between' => [150,160] },
 156                    wix => {'in' => [qw/zz yy/]},
 157                    wux => {'not_in'  => [qw/30 40/]}
 158                  },
 159         stmt => " WHERE ( ( ( foo NOT LIKE ? ) OR ( foo NOT LIKE ? ) OR ( foo NOT LIKE ? ) ) AND ( ( fum LIKE ? ) OR ( fum LIKE ? ) ) AND ( nix BETWEEN ? AND ? ) AND ( nox NOT BETWEEN ? AND ? ) AND wix IN ( ?, ? ) AND wux NOT IN ( ?, ? ) )",
 160         bind => [7,8,9,'a','b',100,200,150,160,'zz','yy','30','40'],
 161     },
 162
 163     {
 164         where => {
 165             bar => {'!=' => []},
 166         },
 167         stmt => " WHERE ( 1=1 )",
 168         bind => [],
 169     },
 170
 171     {
 172         where => {
 173             id  => [],
 174         },
 175         stmt => " WHERE ( 0=1 )",
 176         bind => [],
 177     },
 178
 179
 180     {
 181         where => {
 182             foo => \["IN (?, ?)", 22, 33],
 183             bar => [-and =>  \["> ?", 44], \["< ?", 55] ],
 184         },
 185         stmt => " WHERE ( (bar > ? AND bar < ?) AND foo IN (?, ?) )",
 186         bind => [44, 55, 22, 33],
 187     },
 188
 189     {
 190         where => {
 191           -and => [
 192             user => 'nwiger',
 193             [
 194               -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
 195               -or => { workhrs => {'<', 50}, geo => 'EURO' },
 196             ],
 197           ],
 198         },
 199         stmt => "WHERE ( user = ? AND (
 200                ( workhrs > ? AND geo = ? )
 201             OR ( geo = ? OR workhrs < ? )
 202           ) )",
 203         bind => [qw/nwiger 20 ASIA EURO 50/],
 204     },
 205
 206    {
 207        where => { -and => [{}, { 'me.id' => '1'}] },
 208        stmt => " WHERE ( ( me.id = ? ) )",
 209        bind => [ 1 ],
 210    },
 211
 212    {
 213        where => { foo => $not_stringifiable, },
 214        stmt => " WHERE ( foo = ? )",
 215        bind => [ $not_stringifiable ],
 216    },
 217
 218    {
 219        where => \[ 'foo = ?','bar' ],
 220        stmt => " WHERE (foo = ?)",
 221        bind => [ "bar" ],
 222    },
 223
 224    {
 225        where => [ \[ 'foo = ?','bar' ] ],
 226        stmt => " WHERE (foo = ?)",
 227        bind => [ "bar" ],
 228    },
 229
 230    {
 231        where => { -bool => \'function(x)' },
 232        stmt => " WHERE function(x)",
 233        bind => [],
 234    },
 235
 236    {
 237        where => { -bool => 'foo' },
 238        stmt => " WHERE foo",
 239        bind => [],
 240    },
 241
 242    {
 243        where => { -and => [-bool => 'foo', -bool => 'bar'] },
 244        stmt => " WHERE foo AND bar",
 245        bind => [],
 246    },
 247
 248    {
 249        where => { -or => [-bool => 'foo', -bool => 'bar'] },
 250        stmt => " WHERE foo OR bar",
 251        bind => [],
 252    },
 253
 254    {
 255        where => { -not_bool => \'function(x)' },
 256        stmt => " WHERE NOT function(x)",
 257        bind => [],
 258    },
 259
 260    {
 261        where => { -not_bool => 'foo' },
 262        stmt => " WHERE NOT foo",
 263        bind => [],
 264    },
 265
 266    {
 267        where => { -and => [-not_bool => 'foo', -not_bool => 'bar'] },
 268        stmt => " WHERE (NOT foo) AND (NOT bar)",
 269        bind => [],
 270    },
 271
 272    {
 273        where => { -or => [-not_bool => 'foo', -not_bool => 'bar'] },
 274        stmt => " WHERE (NOT foo) OR (NOT bar)",
 275        bind => [],
 276    },
 277
 278    {
 279        where => { -bool => \['function(?)', 20]  },
 280        stmt => " WHERE function(?)",
 281        bind => [20],
 282    },
 283
 284    {
 285        where => { -not_bool => \['function(?)', 20]  },
 286        stmt => " WHERE NOT function(?)",
 287        bind => [20],
 288    },
 289
 290    {
 291        where => { -bool => { a => 1, b => 2}  },
 292        stmt => " WHERE a = ? AND b = ?",
 293        bind => [1, 2],
 294    },
 295
 296    {
 297        where => { -bool => [ a => 1, b => 2] },
 298        stmt => " WHERE a = ? OR b = ?",
 299        bind => [1, 2],
 300    },
 301
 302    {
 303        where => { -not_bool => { a => 1, b => 2}  },
 304        stmt => " WHERE NOT (a = ? AND b = ?)",
 305        bind => [1, 2],
 306    },
 307
 308    {
 309        where => { -not_bool => [ a => 1, b => 2] },
 310        stmt => " WHERE NOT ( a = ? OR b = ? )",
 311        bind => [1, 2],
 312    },
 313
 314 # Op against internal function
 315    {
 316        where => { bool1 => { '=' => { -not_bool => 'bool2' } } },
 317        stmt => " WHERE ( bool1 = (NOT bool2) )",
 318        bind => [],
 319    },
 320    {
 321        where => { -not_bool => { -not_bool => { -not_bool => 'bool2' } } },
 322        stmt => " WHERE ( NOT ( NOT ( NOT bool2 ) ) )",
 323        bind => [],
 324    },
 325
 326 # Op against random functions (these two are oracle-specific)
 327    {
 328        where => { timestamp => { '!=' => { -trunc => { -year => \'sysdate' } } } },
 329        stmt => " WHERE ( timestamp != TRUNC (YEAR sysdate) )",
 330        bind => [],
 331    },
 332    {
 333        where => { timestamp => { '>=' => { -to_date => '2009-12-21 00:00:00' } } },
 334        stmt => " WHERE ( timestamp >= TO_DATE ? )",
 335        bind => ['2009-12-21 00:00:00'],
 336    },
 337
 338 # Legacy function specs
 339    {
 340        where => { ip => {'<<=' => '127.0.0.1/32' } },
 341        stmt => "WHERE ( ip <<= ? )",
 342        bind => ['127.0.0.1/32'],
 343    },
 344    {
 345        where => { foo => { 'GLOB' => '*str*' } },
 346        stmt => " WHERE foo GLOB ? ",
 347        bind => [ '*str*' ],
 348    },
 349    {
 350        where => { foo => { 'REGEXP' => 'bar|baz' } },
 351        stmt => " WHERE foo REGEXP ? ",
 352        bind => [ 'bar|baz' ],
 353    },
 354
 355 # Tests for -not
 356 # Basic tests only
 357     {
 358         where => { -not => { a => 1 } },
 359         stmt  => " WHERE ( (NOT a = ?) ) ",
 360         bind => [ 1 ],
 361     },
 362     {
 363         where => { a => 1, -not => { b => 2 } },
 364         stmt  => " WHERE ( ( (NOT b = ?) AND a = ? ) ) ",
 365         bind => [ 2, 1 ],
 366     },
 367     {
 368         where => { -not => { a => 1, b => 2, c => 3 } },
 369         stmt  => " WHERE ( (NOT ( a = ? AND b = ? AND c = ? )) ) ",
 370         bind => [ 1, 2, 3 ],
 371     },
 372     {
 373         where => { -not => [ a => 1, b => 2, c => 3 ] },
 374         stmt  => " WHERE ( (NOT ( a = ? OR b = ? OR c = ? )) ) ",
 375         bind => [ 1, 2, 3 ],
 376     },
 377     {
 378         where => { -not => { c => 3, -not => { b => 2, -not => { a => 1 } } } },
 379         stmt  => " WHERE ( (NOT ( (NOT ( (NOT a = ?) AND b = ? )) AND c = ? )) ) ",
 380         bind => [ 1, 2, 3 ],
 381     },
 382     {
 383         where => { -not => { -bool => 'c', -not => { -not_bool => 'b', -not => { a => 1 } } } },
 384         stmt  => " WHERE ( (NOT ( c AND (NOT ( (NOT a = ?) AND (NOT b) )) )) ) ",
 385         bind => [ 1 ],
 386     },
 387 );
 388
 389 for my $case (@handle_tests) {
 390     my $sql = SQL::Abstract->new;
 391     my ($stmt, @bind) = $sql->where($case->{where}, $case->{order});
 392     is_same_sql_bind($stmt, \@bind, $case->{stmt}, $case->{bind})
 393       || diag_where ( $case->{where} );
 394 }
 395
 396 dies_ok {
 397     my $sql = SQL::Abstract->new;
 398     $sql->where({ foo => { '>=' => [] }},);
 399 };
 400
 401 done_testing;