File Coverage

blib/lib/SQL/Abstract/Complete.pm

Criterion	Covered	Total	%
statement	82	89	92.1
branch	58	78	74.3
condition	14	21	66.6
subroutine	9	9	100.0
pod	2	2	100.0
total	165	199	82.9

package SQL::Abstract::Complete;
# ABSTRACT: Generate complete SQL from Perl data structures

use strict;
use warnings;

use SQL::Abstract 1.5;
use Storable 'dclone';

use vars '@ISA';
@ISA = 'SQL::Abstract';

our $VERSION = '1.06'; # VERSION

sub new {
    my $self = shift;
    $self = $self->SUPER::new(@_);
    $self->{'part_join'} ||= ' ';
    return $self;
}

sub _wipe_space {
    return join( '', map {
        s/\s{2,}/ /g;
        s/^\s+|\s+$//g;
        s/\s+,/,/g;
        $_;
    } @_ );
}

sub _sqlcase {
    return ( $_[0]->{'case'} ) ? $_[1] : uc( ( defined( $_[1] ) ) ? $_[1] : '' );
}

sub select {
    my ( $self, $tables, $columns, $where, $meta ) = @_;
    $columns = ['*'] unless ( $columns and @{$columns} > 0 );
    $tables  = dclone($tables) if ( ref $tables );

    my $columns_sql = $self->_sqlcase('select') . ' ' . _wipe_space(
        ( ref($columns) eq 'SCALAR' ) ? ${$columns}             :
        ( not ref($columns)         ) ? $self->_quote($columns) :
        join( ', ', map {
            ( ref($_) eq 'SCALAR' ) ? ${$_}             :
            ( not ref($_)         ) ? $self->_quote($_) :
            join( ' AS ', map { $self->_quote($_) } ( ref($_) eq 'HASH' ) ? %{$_} : @{$_} );
        } @{$columns} )
    );

    my $core_table;
    my $tables_sql = join(
        $self->{'part_join'},
        map { _wipe_space( join( ' ',
                $self->_sqlcase( shift( @{$_} ) ),
                grep { defined } @{$_} )
            ) } (
            ( ref($tables) eq 'SCALAR' ) ? [ undef, ${$tables}              ] :
            ( not ref($tables)         ) ? [ 'from', $self->_quote($tables) ] :
            map {
                ( ref($_) eq 'SCALAR' ) ? [ undef, ${$_}              ] :
                ( not ref($_)         ) ? [ 'from', $self->_quote($_) ] :
                do {
                    my @parts     = ( ref($_) eq 'HASH' )            ? %{$_}              : @{$_};
                    my $join_type = ( ref( $parts[0] ) eq 'SCALAR' ) ? ${ shift(@parts) } : 'join';

                    my $join_on =
                        ( ref( $parts[-1] ) eq 'SCALAR' )              ? ${ pop(@parts) } :
                        ( ref( $parts[-1] ) eq 'HASH' and @parts > 1 ) ? do {
                            my $join_def = pop(@parts);
                            $join_type = $join_def->{'join'} . ' join' if ( $join_def->{'join'} );

                            ( ref( $join_def->{'using'} || $join_def->{'on'} ) eq 'SCALAR' )
                                ? ${ $join_def->{'using'} || $join_def->{'on'} } :
                            ( $join_def->{'using'} )
                                ? $self->_sqlcase('using') . '(' . $join_def->{'using'} . ')' :
                            ( $join_def->{'on'} )
                                ? $self->_sqlcase('on') . ' ' . $join_def->{'on'} : '';
                        } :
                        ( not ref( $parts[-1] ) and @parts > 1 )
                            ? $self->_sqlcase('using') . '(' . $self->_quote( pop(@parts) ) . ')'
                            : '';

                    my $table_def = shift(@parts);
                    $table_def    =
                        ( not ref($table_def) )         ? $table_def    :
                        ( ref($table_def) eq 'SCALAR' ) ? ${$table_def} :
                        do {
                            $table_def     = [ %{$table_def} ] if ( ref($table_def) eq 'HASH' );
                            my $table_name = shift( @{$table_def} );

                            unless ($core_table) {
                                $core_table = $table_name;
                                $join_type  = 'from';
                            }

                            $self->_quote($table_name) . ( ( @{$table_def} ) ? ' ' . join(
                                ' ',
                                $self->_sqlcase('as'),
                                map { $self->_quote($_) } @{$table_def},
                            ) : '' );
                        };

                    [ $join_type, $table_def, $join_on ];
                };
            } ( ( ref($tables) ) ? @{$tables} : $tables )
        )
    );

    my ( $where_sql, @bind ) = $self->where($where);

    my ( $offset, $rows ) = ( $meta->{'offset'} || 0, $meta->{'rows'} || 0 );
    if ( $meta->{'limit'} ) {
        ( $offset, $rows ) = ( ref( $meta->{'limit'} ) eq 'ARRAY' )
            ? @{ $meta->{'limit'} }
            : ( 0, $meta->{'limit'} );
    }
    $offset = ( $meta->{'page'} - 1 ) * $rows if ( $meta->{'page'} );

    my $sql = join(
        $self->{'part_join'},
        grep { defined and $_ } (
            $columns_sql,
            $tables_sql,
            _wipe_space($where_sql),
            ( ( $meta->{'group_by'} ) ? do {
                (
                    my $group_by = scalar( $self->_order_by( $meta->{'group_by'} ) )
                ) =~ s/\s*ORDER BY/GROUP BY/;
                _wipe_space($group_by);
            } : undef ),
            ( ( $meta->{'having'} ) ? do {
                my ( $having, @having_bind ) = $self->where( $meta->{'having'} );
                $having =~ s/\s*WHERE/HAVING/;
                push( @bind, @having_bind ) if ( scalar(@having_bind) );
                _wipe_space($having);
            } : undef ),
            ( ( $meta->{'order_by'} ) ? _wipe_space( $self->_order_by( $meta->{'order_by'} ) ) : undef ),
            ( $offset or $rows )
                ? $self->_sqlcase('limit') . " $rows " . $self->_sqlcase('offset')  . " $offset"
                : undef,
        ),
    );

    $sql =~ s/^\s+|\s+$//g;
    return ( wantarray() ) ? ( $sql, @bind ) : $sql;
}

1;

__END__