NAME

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

VERSION

0.19_9. Development release.

SYNOPSIS

  use SQL::DB::Expr;

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

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

  my $expr = ($col1->is_not_null .AND.
    $col2->between(1,2))->as('alias');

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,
        _multi   => $_multi,
    );

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.

_multi => Bool

Whether the expression requires surrounding brackets.

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.

_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.

_quote( $value [,$bind_type] ) -> SQL::DB::Quote

Returns an object which is recognized by SQL::DB as something which should be quoted before being prepared.

_bval( $value [,$bind_type] ) -> SQL::DB::BindValue

Returns an object which is recognized by SQL::DB as something which should be bound (via a placeholder) before being prepared.

_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.

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.