test for between

[dbsrgits/Data-Query.git] / t / expr-helpers.t

use strictures 1;
use Test::More qw(no_plan);

use Devel::Dwarn;
use Data::Query::Renderer::SQL::Naive;
use Data::Query::ExprHelpers;

my $rend = Data::Query::Renderer::SQL::Naive->new({ quote_chars => [ "'" ] });

sub binding { map perl_scalar_value($_), @_ }

sub dq_sql_is {
  my $expr = shift;
#::Dwarn($expr); return;
  my $rendered = $rend->render($expr);
  is_deeply($rendered, @_);
}

dq_sql_is
  Select([ Identifier('*') ], Identifier('foo')),
  ['SELECT * FROM foo'],
  'simple select';

{
  # should these both be allowed, and result in the same SQL?

  dq_sql_is
    Join(
      Select([ Identifier('*') ], Identifier('foo')),
      Identifier('bar'),
      perl_operator('==', Identifier('foo', 'x'), Identifier('bar', 'y')),
    ),
    ['SELECT * FROM foo JOIN bar ON foo.x = bar.y'],
    'join on with dots (Join Select)';

  dq_sql_is
    Select(
      [ Identifier('*') ],
      Join(
        Identifier('foo'),
        Identifier('bar'),
        perl_operator('==', Identifier('foo', 'x'), Identifier('bar', 'y')),
      ),
    ),
    ['SELECT * FROM foo JOIN bar ON foo.x = bar.y'],
    'join on with dots (Select Join)';
}

dq_sql_is
  Where(
    perl_operator(
      '==',
      Identifier('x'),
      Literal('SQL', '?', [ binding(1) ],),
    ),
    Select([ Identifier('*') ], Identifier('foo')),
  ),
  ['SELECT * FROM foo WHERE x = ?', binding(1)],
  'simple select with where and bind';

dq_sql_is
  Group (
    [ Identifier('x') ],
    Select([ Identifier('*') ], Identifier('foo')),
  ),
  ['SELECT * FROM foo GROUP BY x'],
  'simple group by';

dq_sql_is
  Select(
    [ Identifier('*') ],
    Join(
      Identifier('foo'),
      Identifier('bar'),
      perl_operator('==', Identifier('foo', 'x'), Identifier('bar', 'y')),
      'left outer',
    ),
  ),
  ['SELECT * FROM foo LEFT OUTER JOIN bar ON foo.x = bar.y'],
  'left outer join';

dq_sql_is
  Select(
    [ Operator({ 'SQL.Naive' => 'apply' }, [ Identifier('COUNT'), Identifier('*') ]) ],
    Identifier('foo'),
  ),
  ['SELECT COUNT( * ) FROM foo'],
  'count';

dq_sql_is
  Where(
    Operator({ 'SQL.Naive' => 'BETWEEN' }, [
      Identifier('x'),
      map Literal('SQL', '?', [ binding($_) ]), (1,2),
    ]),
    Select([ Identifier('*') ], Identifier('foo')),
  ),
  [ 'SELECT * FROM foo WHERE ( x BETWEEN ? AND ? )', map binding($_), 1, 2, ],
  'between';