view release on metacpan or  search on metacpan

t/01.sql.t  view on Meta::CPAN

$stmt = ns();
$stmt->add_where( foo => { column => 'bar', op => '!=', value => 'bar' } );
is( strip( $stmt->as_sql_where ), "WHERE (bar != ?)" );
is( scalar @{ $stmt->bind },      1 );
is( $stmt->bind->[0],             'bar' );

$stmt = ns();
$stmt->add_where( foo => \'IS NOT NULL' );
is( strip( $stmt->as_sql_where ), "WHERE (foo IS NOT NULL)" );
is( scalar @{ $stmt->bind },      0 );

$stmt = ns();
$stmt->add_where( foo => 'bar' );
$stmt->add_where( baz => 'quux' );
is( strip( $stmt->as_sql_where ), "WHERE (foo = ?) AND (baz = ?)" );
is( scalar @{ $stmt->bind },      2 );
is( $stmt->bind->[0],             'bar' );
is( $stmt->bind->[1],             'quux' );

$stmt = ns();
$stmt->add_where(
    foo => [ { op => '>', value => 'bar' }, { op => '<', value => 'baz' } ] );
is( strip( $stmt->as_sql_where ), "WHERE ((foo > ?) OR (foo < ?))" );
is( scalar @{ $stmt->bind },      2 );
is( $stmt->bind->[0],             'bar' );
is( $stmt->bind->[1],             'baz' );

$stmt = ns();
$stmt->add_where(
    foo => [
        -and => { op => '>', value => 'bar' },
        { op => '<', value => 'baz' }
    ]
);
is( strip( $stmt->as_sql_where ), "WHERE ((foo > ?) AND (foo < ?))" );
is( scalar @{ $stmt->bind },      2 );
is( $stmt->bind->[0],             'bar' );
is( $stmt->bind->[1],             'baz' );

$stmt = ns();
$stmt->add_where( foo => [ -and => 'foo', 'bar', 'baz' ] );
is( strip( $stmt->as_sql_where ),
    "WHERE ((foo = ?) AND (foo = ?) AND (foo = ?))" );
is( scalar @{ $stmt->bind }, 3 );
is( $stmt->bind->[0],        'foo' );
is( $stmt->bind->[1],        'bar' );
is( $stmt->bind->[2],        'baz' );

## regression bug. modified parameters
my %terms = ( foo => [ -and => 'foo', 'bar', 'baz' ] );
$stmt = ns();
$stmt->add_where(%terms);
is( strip( $stmt->as_sql_where ),
    "WHERE ((foo = ?) AND (foo = ?) AND (foo = ?))" );
$stmt->add_where(%terms);
is( strip( $stmt->as_sql_where ),
    "WHERE ((foo = ?) AND (foo = ?) AND (foo = ?)) AND ((foo = ?) AND (foo = ?) AND (foo = ?))"
);

$stmt = ns();
$stmt->add_select( foo => 'foo' );
$stmt->add_select('bar');
$stmt->from( [qw( baz )] );
is( strip( $stmt->as_sql ), "SELECT foo, bar FROM baz" );

$stmt = ns();
$stmt->add_select( 'f.foo'    => 'foo' );
$stmt->add_select( 'COUNT(*)' => 'count' );
$stmt->from( [qw( baz )] );
is( strip( $stmt->as_sql ), "SELECT f.foo, COUNT(*) count FROM baz" );
my $map = $stmt->select_map;
is( scalar( keys %$map ), 2 );
is( $map->{'f.foo'},      'foo' );
is( $map->{'COUNT(*)'},   'count' );

# HAVING
$stmt = ns();
$stmt->add_select( foo        => 'foo' );
$stmt->add_select( 'COUNT(*)' => 'count' );
$stmt->from( [qw(baz)] );
$stmt->add_where( foo => 1 );
$stmt->group( { column => 'baz' } );
$stmt->order( { column => 'foo', desc => 'DESC' } );
$stmt->limit(2);
$stmt->add_having( count => 2 );

is( strip( $stmt->as_sql ),
    "SELECT foo, COUNT(*) count FROM baz WHERE (foo = ?) GROUP BY baz HAVING (COUNT(*) = ?) ORDER BY foo DESC LIMIT 2"
);