NAME

SQL::DB::Expr - Build SQL expressions with Perl

SYNOPSIS

  use SQL::DB::Expr;

  my $t1 = SQL::DB::Expr->new(
    _txt => 'table1',
  );

  my $col1 = SQL::DB::Expr->new(
    _txt => 'col1',
    _btype => 'SQL_INTEGER',
  );

  my $col2 = SQL::DB::Expr->new(
    _txt => 'col2',
  );

  my $expr = expr(
    select_distinct => [
        $col1,
        sql_min( $col2 )->as( 'min_col2' ),
    ],
    from      => $t1,
    left_join => $t1,
    where     => ( $col1 > 13 ) .AND.  $col2->not_between( 3, 24 ),
    group_by  => $col1,
    order_by  => $col1->asc,
    limit     => 1,
    offset    => 13,
  );

  print "$expr\n";
  # SELECT DISTINCT
  #     col1,
  #     MIN(col2) AS 'min_col2',
  # FROM
  #     t1
  # LEFT JOIN
  #     t1 AS t2
  # WHERE
  #     (col1 > ? AND (col2 NOT BETWEEN ? AND ?))
  # GROUP BY
  #     col1
  # ORDER BY
  #     col1 ASC
  # LIMIT
  #     1
  # OFFSET
  #     13


  use Data::Dumper;
  print Dumper( $expr->_bvalues );
  # [ 13, 3, 24 ]

  print Dumper( $expr->_btypes );
  # [ 'SQL_INTEGER', undef, undef ]

DESCRIPTION

SQL::DB::Expr provides an object class that supports mapping Perl syntax to SQL text, values and types suitable for passing to DBI methods. This class is the basis for SQL::DB and is not generally used directly.

CONSTRUCTOR

    new(
        _txt     => $_txt,
        _btype   => $bind_type,
        _bvalues => [ @_bvalues ],
        _btypes  => [ @_btypes ],
        _multi   => $_multi,
        _op      => $op,
    );

ATTRIBUTES

_txt => Scalar

The SQL representation of the expression suitable for passing to DBI methods such as do(), selectall_arrayref(), prepare(), etc.

_btype => Scalar

The DBD bind_type suitable for passing to a DBI statement-handle bind_param() method.

_bvalues => ARRAYREF

The values of the expression suitable for passing to a DBI statement-handle bind_param() method.

_btypes => ARRAYREF

The bind types of the expression _bvalues suitable for passing to a DBI statement handle bind_param() method.

_multi => Bool

Whether the expression requires surrounding brackets.

_op => Scalar

The operator of the expression in the case that it is binary.

METHODS

BUILD

A subroutine called during an object's instantiation phase.

_clone

Makes a deep copy of the object.

_as_string

Returns a string representation of the expression, providing surrounding brackets for '_multi' expressions.

_bvalues_sql

Returns a string representation of the bind values as an SQL comment.

_expr_not

Maps '!' to 'NOT'.

_expr_eq

Maps '==' to "==".

_expr_ne

Maps '!=' to "!=".

_expr_bitand

Maps '&' to "&";

_expr_bitor

Maps '|' to "|";

_expr_lt

Maps '<' to "<";

_expr_gt

Maps '>' to ">";

_expr_lte

Maps '<=' to "<=";

_expr_gte

Maps '>=' to ">=";

_expr_add

Maps '+' to "+";

_expr_sub

Maps '-' to "-";

_expr_mult

Maps '*' to "*";

_expr_divide

Maps '/' to "/";

is_null

Maps to "IS NULL".

is_not_null

Maps to "IS NOT NULL".

in(@values)

Maps to "IN (@VALUES)"

not_in( @values )

Maps to "NOT IN ( @VALUES )".

between( $a, $b )

Maps to "BETWEEN $a AND $b".

not_between( $a, $b )

Maps to "NOT BETWEEN $a AND $b".

as( $as )

Maps to "AS $as".

like( $like )

Maps to "LIKE $like".

asc

Maps to "ASC".

desc

Maps to "DESC".

CLASS FUNCTIONS

The following functions are exported on demand.

_bexpr( $item [,$bind_type] )

Return $item if it is already an expression, or a new SQL::DB::Expr object otherwise. The optional $bind_type specifies the DBI bind_type.

_expr_binary( $op, $e1, $e2, $swap )

A method for building binary operator expressions.

_expr_join( $separator, @expressions )

Does the same as Perl's 'join' built-in. but for SQL::DB::Expr objects. See BUGS below for why this is needed.

_bexpr_join( $separator, @expressions )

Does the same as Perl's 'join' built-in but for SQL::DB::Expr objects, but using _bexpr on each item of @expressions. See BUGS below for why this is needed.

AND, OR

These subroutines let you write SQL logical expressions in Perl using string concatenation:

    ( $e1 .AND. $e2 ) .OR. ( $e3 .AND. $e4 )

Note that due to operator precedence, expressions either side of .AND. or .OR. should be bracketed if they are not already single expression objects.

Things are implemented this way due to Perl not allowing the overloading of the 'and' and 'or' built-ins.

BUGS

Using SQL::DB::Expr objects with the Perl "join" command does not work as expected, apparently because join does not trigger either the '.' or '.=' overload methods. The work around is to use the _expr_join subroutine.

SEE ALSO

SQL::DB, DBI

AUTHOR

Mark Lawrence <nomad@null.net>

COPYRIGHT AND LICENSE

Copyright (C) 2007-2011 Mark Lawrence <nomad@null.net>

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License, or (at your option) any later version.