fixed pod.

[dbsrgits/DBIx-Class.git] / t / 07abstract.t

use Test::More;\r
\r
plan tests => 56;\r
\r
use DBIx::Class::SQL::Abstract;\r
\r
# Make sure to test the examples, since having them break is somewhat\r
# embarrassing. :-(\r
\r
my @handle_tests = (\r
    {\r
        where => {\r
            requestor => 'inna',\r
            worker => ['nwiger', 'rcwe', 'sfz'],\r
            status => { '!=', 'completed' }\r
        },\r
        stmt => "( requestor = ? AND status != ? AND ( ( worker = ? ) OR"\r
              . " ( worker = ? ) OR ( worker = ? ) ) )",\r
        bind => [qw/inna completed nwiger rcwe sfz/],\r
    },\r
\r
    {\r
        where  => {\r
            user   => 'nwiger',\r
            status => 'completed'\r
        },\r
        stmt => "( status = ? AND user = ? )",\r
        bind => [qw/completed nwiger/],\r
    },\r
\r
    {\r
        where  => {\r
            user   => 'nwiger',\r
            status => { '!=', 'completed' }\r
        },\r
        stmt => "( status != ? AND user = ? )",\r
        bind => [qw/completed nwiger/],\r
    },\r
\r
    {\r
        where  => {\r
            status   => 'completed',\r
            reportid => { 'in', [567, 2335, 2] }\r
        },\r
        stmt => "( reportid IN ( ?, ?, ? ) AND status = ? )",\r
        bind => [qw/567 2335 2 completed/],\r
    },\r
\r
    {\r
        where  => {\r
            status   => 'completed',\r
            reportid => { 'not in', [567, 2335, 2] }\r
        },\r
        stmt => "( reportid NOT IN ( ?, ?, ? ) AND status = ? )",\r
        bind => [qw/567 2335 2 completed/],\r
    },\r
\r
    {\r
        where  => {\r
            status   => 'completed',\r
            completion_date => { 'between', ['2002-10-01', '2003-02-06'] },\r
        },\r
        stmt => "( completion_date BETWEEN ? AND ? AND status = ? )",\r
        bind => [qw/2002-10-01 2003-02-06 completed/],\r
    },\r
\r
    {\r
        where => [\r
            {\r
                user   => 'nwiger',\r
                status => { 'in', ['pending', 'dispatched'] },\r
            },\r
            {\r
                user   => 'robot',\r
                status => 'unassigned',\r
            },\r
        ],\r
        stmt => "( ( status IN ( ?, ? ) AND user = ? ) OR ( status = ? AND user = ? ) )",\r
        bind => [qw/pending dispatched nwiger unassigned robot/],\r
    },\r
\r
    {\r
        where => {  \r
            priority  => [ {'>', 3}, {'<', 1} ],\r
            requestor => \'is not null',\r
        },\r
        stmt => "( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor is not null )",\r
        bind => [qw/3 1/],\r
    },\r
\r
    {\r
        where => {  \r
            priority  => [ {'>', 3}, {'<', 1} ],\r
            requestor => { '!=', undef }, \r
        },\r
        stmt => "( ( ( priority > ? ) OR ( priority < ? ) ) AND requestor IS NOT NULL )",\r
        bind => [qw/3 1/],\r
    },\r
\r
    {\r
        where => {  \r
            priority  => { 'between', [1, 3] },\r
            requestor => { 'like', undef }, \r
        },\r
        stmt => "( priority BETWEEN ? AND ? AND requestor IS NULL )",\r
        bind => [qw/1 3/],\r
    },\r
\r
\r
    {\r
        where => {  \r
            id  => 1,\r
	    num => {\r
	     '<=' => 20,\r
	     '>'  => 10,\r
	    },\r
        },\r
        stmt => "( id = ? AND num <= ? AND num > ? )",\r
        bind => [qw/1 20 10/],\r
    },\r
\r
    {\r
        where => { foo => {-not_like => [7,8,9]},\r
                   fum => {'like' => [qw/a b/]},\r
                   nix => {'between' => [100,200] },\r
                   nox => {'not between' => [150,160] },\r
                   wix => {'in' => [qw/zz yy/]},\r
                   wux => {'not_in'  => [qw/30 40/]}\r
                 },\r
        stmt => "( ( ( 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 ( ?, ? ) )",\r
        bind => [7,8,9,'a','b',100,200,150,160,'zz','yy','30','40'],\r
    },\r
    \r
    # a couple of the more complex tests from S::A 01generate.t that test -nest, etc.\r
    {\r
        where => { name => {'like', '%smith%', -not_in => ['Nate','Jim','Bob','Sally']},\r
                                     -nest => [ -or => [ -and => [age => { -between => [20,30] }, age => {'!=', 25} ],\r
                                                         yob => {'<', 1976} ] ] },\r
        stmt => "( ( ( ( ( ( ( age BETWEEN ? AND ? ) AND ( age != ? ) ) ) OR ( yob < ? ) ) ) ) AND name NOT IN ( ?, ?, ?, ? ) AND name LIKE ? )",\r
        bind => [qw(20 30 25 1976 Nate Jim Bob Sally %smith%)],\r
    },\r
    \r
    {\r
        where => [-maybe => {race => [-and => [qw(black white asian)]]},\r
                                                          {-nest => {firsttime => [-or => {'=','yes'}, undef]}},\r
                                                          [ -and => {firstname => {-not_like => 'candace'}}, {lastname => {-in => [qw(jugs canyon towers)]}} ] ],\r
        stmt => "( ( ( ( ( ( ( race = ? ) OR ( race = ? ) OR ( race = ? ) ) ) ) ) ) OR ( ( ( ( firsttime = ? ) OR ( firsttime IS NULL ) ) ) ) OR ( ( ( firstname NOT LIKE ? ) ) AND ( lastname IN ( ?, ?, ? ) ) ) )",\r
        bind => [qw(black white asian yes candace jugs canyon towers)],\r
    }\r
);\r
\r
for (@handle_tests) {\r
    local $" = ', '; \r
\r
    # run twice\r
    for (my $i=0; $i < 2; $i++) {\r
        my($stmt, @bind) = DBIx::Class::SQL::Abstract->_cond_resolve($_->{where}, {});\r
\r
        is($stmt, $_->{stmt}, 'SQL ok');\r
        cmp_ok(@bind, '==', @{$_->{bind}}, 'bind vars ok');\r
    }\r
}\r
\r
\r