view release on metacpan or  search on metacpan

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

$stmt = ns();
$stmt->from( ['foo'] );
$stmt->group( [ { column => 'baz' }, { column => 'quux' }, ] );
is strip( $stmt->as_sql ), "FROM foo GROUP BY baz, quux", 'multiple group by';

$stmt = ns();
$stmt->from( ['foo'] );
$stmt->group(
    [   { column => 'baz',  desc => 'DESC' },
        { column => 'quux', desc => 'DESC' },
    ]
);
is strip( $stmt->as_sql ),
    "FROM foo GROUP BY baz DESC, quux DESC",
    'multiple group by with desc';

## Testing ORDER BY
$stmt = ns();
$stmt->from( ['foo'] );
$stmt->order( { column => 'baz', desc => 'DESC' } );
is strip( $stmt->as_sql ), "FROM foo ORDER BY baz DESC", 'single order by';

$stmt = ns();
$stmt->from( ['foo'] );
$stmt->order(
    [   { column => 'baz',  desc => 'DESC' },
        { column => 'quux', desc => 'ASC' },
    ]
);
is strip( $stmt->as_sql ),
    "FROM foo ORDER BY baz DESC, quux ASC",
    'multiple order by';

## Testing GROUP BY plus ORDER BY
$stmt = ns();
$stmt->from( ['foo'] );
$stmt->group( { column => 'quux' } );
$stmt->order( { column => 'baz', desc => 'DESC' } );
is strip( $stmt->as_sql ),
    "FROM foo GROUP BY quux ORDER BY baz DESC",
    'group by with order by';

## Testing LIMIT and OFFSET
$stmt = ns();
$stmt->from( ['foo'] );
$stmt->limit(5);
is strip( $stmt->as_sql ), "FROM foo LIMIT 5";
$stmt->offset(10);
is strip( $stmt->as_sql ), "FROM foo LIMIT 5 OFFSET 10";

{    ## Non-numerics should cause an error
    my $sql = eval { $stmt->limit("  15g"); strip( $stmt->as_sql ) };
    like $@, qr/Int/, "bogus limit causes as_sql assertion";
}

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

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

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

$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"
);