t/02where.t

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