comment on

Hi,
I'm in the process of writing a module SQL::Builder::Select - It has a lot of potential for further development, but it's come far enough that I'm using it for real work.

If anybody would be willing to look over the code and make any suggestions, or point out problems, I'd much appreciate it.

first, here's the POD ...

NAME

Shop::Sql::Select - Create and return a SQL SELECT statement

SYNOPSIS

        use SQL::Builder::Select;


        my $select = new SQL::Builder::Select;
        or
        my $select = SQL::Builder::Select->new;

DESCRIPTION

An OO interface to build a (MySql) SQL SELECT statement, or a WHERE clause for use in a SELECT statement.

METHODS

Input Methods

These methods are used to input data to build the SQL statement.

`column()`

Accepts a column name or list of names.

Further calls are added to the end of the list.

        $select->column( $column );
        or
        $select->column( @column );

Failure to set column() before calling return() will croak (kill the program).

The return value of column() is the total number of arguments held in column()

`table()`

Accepts a table name or list of names.

Further calls are added to the end of the list.

        $select->table( $tablename );
        or
        $select->table( @tablename );

Failure to set table() before calling return() will croak (kill the program).

The return value of column() is the total number of arguments held in table()

`where()`

Accepts a single complete where statement (minus the leading 'WHERE ').

Further calls will be joined using the value of where_logic(). In this case, where_logic() should only hold 1 'AND' or 'OR' value, and all where() statements will be joined using this value.

        $select->where( $statement );

The return value of where() is the total number of arguments held in where()

`where_and()`

Accepts a where statement or list of statements.

Further calls are added to the end of the list.

        $select->where_and( $where );
        or
        $select->where_and( @where );

The return value of where_and() is the total number of arguments held in where_and()

`where_or()`

Accepts a where statement or list of statements.

Further calls are added to the end of the list.

        $select->where_or( $where );
        or
        $select->where_or( @where );

The return value of where_or() is the total number of arguments held in where_or()

`where_eq_and()`

Accepts a where value or list of values.

Each value is made into a '$value = ?' statement and added to the end of the where_and() list.

        $select->where_eq_and( $where );
        or
        $select->where_eq_and( @where );

The return value of where_eq_and() is the total number of arguments held in where_and()

`where_ne_and()`

Accepts a where value or list of values.

Each value is made into a '$value != ?' statement and added to the end of the where_and() list.

        $select->where_ne_and( $where );
        or
        $select->where_ne_and( @where );

The return value of where_ne_and() is the total number of arguments held in where_and()

`where_eq_or()`

Accepts a where value or list of values.

Each value is made into a '$value = ?' statement and added to the end of the where_or() list.

        $select->where_eq_or( $where );
        or
        $select->where_eq_or( @where );

The return value of where_eq_or() is the total number of arguments held in where_or()

`where_ne_or()`

Accepts a where value or list of values.

Each value is made into a '$value != ?' statement and added to the end of the where_or() list.

        $select->where_ne_or( $where );
        or
        $select->where_ne_or( @where );

The return value of where_ne_or() is the total number of arguments held in where_or()

`where_logic()`

Accepts a single logic statment in the form,
```
        $select->where_logic( $logic );
```
where $logic equals
```
        'AND' or 'OR'
```
If where_logic() is not set, it defaults to 'AND'

Of the methods where(), where_and(), where_or(), the first method in that list which has been set will be processed, and subsequent methods are ignored.
```
        where[0] AND||OR where1 ...
        or
        where_and[0] AND||OR where_and1 ...
        or
        where_or[0] AND||OR where_or1 ...
```

Accepts a single logic statement, in the form,


        $select->where_logic( $logic );


where $logic equals

        'w AND a'
        or
        'a OR o'

or any other combination of 2 functions seperated by a 'AND' or 'OR', where

        w = where()
        a = where_and()
        o = where_or()

The logic 'w AND a' would return the equivalent of

 ( where[0] AND where1 ... ) AND ( where_and[0] AND where_and1 ... ).


 The logic I<'w OR a'> would return the equivalent of

 ( where[0] OR where1 ... ) OR ( where_and[0] AND where_and1 ... ).

If where logic() is passed an argument that does not represent the other functions set, unexpected results will likely occur.

The limitations of where_logic() can be overcome by using the function return_where() to create part-statements to be passed to the where() method of another SQL::Builder::Select object.

Further calls to where_logic() replace the existing value.

`order_by()`

Accepts a column name or list of names.

Further calls are added to the end of the list.

        $select->order_by( $column );
        or
        $select->order_by( @column );

The return value of order_by() is the total number of arguments held in order_by()

`limit_offset()`

Accepts an integer.

Further calls replace the existing value.

        $select->limit_offset( $offset );

`limit_value()`

Accepts an integer.

Further calls replace the existing value.

        $select->limit_value( $limit );

Output Method

This method is used to build and return the SQL statement.

`return()`

This builds the SQL statement and returns it.

        $sql = $select->return();

`return_where()`

This builds the SQL statement for all where() functions, and returns it.

        $sql = $select->return_where();

`debug()`

Boolean.

If true, then return() will print debugging data and then the complete statement to STDOUT then exit.

If ran under a web-server, will print text/plain header first.

0 or '' == false.

If no value is passed, returns 1 if set.

Now, here's the code

package SQL::Builder::Select;
use strict;
use warnings;
use Exporter;
use Carp;
use vars qw / $VERSION @ISA /;

$VERSION = 1.00;
@ISA = qw/ Exporter /;


sub new {
  my $class = shift;
  my $self = {};
  
  bless ($self, $class);
  return $self;
}


#####################################################################


sub column {
    my $class = shift;
    
    push @{$class->{column}}, @_;
    return scalar( @{$class->{column}} );
}


sub table {
    my $class = shift;
    
    push @{$class->{table}}, @_;
    return scalar( @{$class->{table}} );
}


#####################################################################


sub where {
    my $class = shift;
    
    push @{$class->{where}}, @_;
    return scalar( @{$class->{where}} );
}


sub where_and {
    my $class = shift;
    
    push @{$class->{where_and}}, @_;
    return scalar( @{$class->{where_and}} );
}


sub where_or {
    my $class = shift;
    
    push @{$class->{where_or}}, @_;
    return scalar( @{$class->{where_or}} );
}


sub where_eq_and {
    my $class = shift;
    
    push @{$class->{where_and}}, map { $_ . ' = ?' } @_;
    return scalar( @{$class->{where_and}} );
}


sub where_ne_and {
    my $class = shift;
    
    push @{$class->{where_and}}, map { $_ . ' != ?' } @_;
    return scalar( @{$class->{where_and}} );
}


sub where_eq_or {
    my $class = shift;
    
    push @{$class->{where_or}}, map { $_ . ' = ?' } @_;
    return scalar( @{$class->{where_or}} );
}


sub where_ne_or {
    my $class = shift;
    
    push @{$class->{where_or}}, map { $_ . ' != ?' } @_;
    return scalar( @{$class->{where_or}} );
}


sub where_logic {
    my $class = shift;
    
    $class->{where_logic} = shift;
}


#####################################################################


sub order_by {
    my $class = shift;
    
    push @{$class->{order_by}}, @_;
    return scalar( @{$class->{order_by}} );
}


#####################################################################


sub limit_offset {
    my $class = shift;
    
    $class->{limit_offset} = shift;
}


sub limit_value {
    my $class = shift;
    
    $class->{limit_value} = shift;
}


#####################################################################


sub return {
    my $class = shift;
    my $sql;
    
    unless (@{$class->{column}}) {
        croak "column() not defined in SQL::Builder::Select::return";
    }
    
    unless (@{$class->{table}}) {
        croak "table() not defined in SQL::Builder::Select::return";
    }
    
    
    $sql = 'SELECT '
                . join (', ', @{$class->{column}})
                . ' FROM '
                . join (', ', @{$class->{table}});
    
    push @{$class->{error}}, 'sql column table', $sql; ### DEBUG
    
    
    my $s = $class->return_where();
    
    if (defined $s) {
        $sql .= ' WHERE '
                    . $s;
        
        push @{$class->{error}}, 'return_where output', $s; ### DEBUG
    }
    
    
    if (defined $class->{order_by}) {
        $sql .= ' ORDER BY '
                    . join (', ', @{$class->{order_by}});
        
        push @{$class->{error}}, 'sql order by', $sql; ### DEBUG
    }
    
    
    if (defined $class->{limit_value}) {
        $sql .= ' LIMIT ';
        
        if (defined $class->{limit_offset}) {
            $sql .= $class->{limit_offset}
                        . ', ';
        }
        
        $sql .= $class->{limit_value};
        
        push @{$class->{error}}, 'sql limit', $sql; ### DEBUG
    }
    
    
    if (defined $class->{debug}) {
        if (defined $ENV{SERVER_NAME}) {
            print "Content-type: text/plain\n\n";
            $/ = '<br>';
        }
        
        if (defined ${$class->{error}}[0]) {
            print map { $_, $/ } @{$class->{error}};
            print $/;
        }
        print $sql;
        exit;
    }
    
    return $sql;
}


#####################################################################


sub return_where {
    my $class = shift;        
            
    if (defined $class->{where_logic}) {
        my $logic = $class->{where_logic};
        
        if ($logic =~ /^and$/i) {
            $class->{where_logic} = 'AND';
            return $class->_where_and_or();
        }
        elsif ($logic =~ /^or$/i) {
            $class->{where_logic} = 'OR';
            return $class->_where_and_or();
        }
        else {
            my ($decision, @func);
            
            if ($logic =~ /and/i) {
                $decision = 'AND';
                @func = split( /and/i, $logic);
            }
            elsif ($logic =~ /or/i) {
                $decision = 'OR';
                @func = split( /or/i, $logic);
            }
            else {
                return;
            }
            
            unless (defined $func[1]) {
                return;
            }
            
            
            for ($func[0], $func[1]) {
                if ($_ =~ /w/i) {
                    push @{$class->{sql}}, $class->_join_values( 'wher
+e', $decision );
                }
                elsif ($_ =~ /a/i) {
                    push @{$class->{sql}}, $class->_join_values( 'wher
+e_and', 'AND' );
                }
                elsif ($_ =~ /o/i) {
                    push @{$class->{sql}}, $class->_join_values( 'wher
+e_or', 'OR' );
                }
                else {
                    return;
                }
            }
            
            return $class->_join_values( 'sql', $decision );
        }
    }
    else {
        $class->{where_logic} = 'AND';
        
        return $class->_where_and_or();
    }
    
    return;
}


#####################################################################


sub debug {
    my $class = shift;
    
    if (@_) {
        my $result = shift;
        
        if (defined $result && $result ne '' && $result != 0) {
            $class->{debug} = 1;
        }
        return 1;
    }
    elsif (defined $class->{debug} && $class->{debug} == 1) {
        return 1;
    }
    
    return;
}


#####################################################################
#####################################################################


sub _where_and_or {
    my $class = shift;
    my $logic = $class->{where_logic};
    
    if (defined ${$class->{where}}[0]) {
        
        return $class->_join_values( 'where', $logic );
    }
    elsif (defined ${$class->{where_and}}[0]) {
        
        return $class->_join_values( 'where_and', 'AND' );
    }
    elsif (defined ${$class->{where_or}}[0]) {
        
        return $class->_join_values( 'where_or', 'OR' );
    }
    
    return;
}

sub _join_values {
    my $class = shift;
    my $name     = shift;
    my $logic = shift if (defined ${$class->{$name}}[0]);
    
    if (defined ${$class->{$name}}[1]) {
        my $s = '('
                    . join(") $logic (", @{$class->{$name}})
                    . ')';
        
        return $s;
    }
    elsif (defined ${$class->{$name}}[0]) {
        return @{$class->{$name}}[0];
    }
    
    return;
}


1;
[download]

.. and here's a few examples of use

Example 1

use SQL::Builder::Select;

$select = new SQL::Builder::Select;

$select->column('col1', 'col2');
$select->table('table');

$select->order_by('col1');

$select->limit_value('10');
$select->limit_offset('10');

print $select->return();
[download]

Gives the output

SELECT col1, col2 FROM table ORDER BY col1 LIMIT 10, 10
[download]

Example 2

use SQL::Builder::Select;

$select = new SQL::Builder::Select;

$select->column('*');
$select->table('table');

$select->where_and('col1 = 1', 'col2 = 1');

print $select->return();
[download]

Gives the output

SELECT * FROM table WHERE (col1 = 1) AND (col2 = 1)
[download]

Example 3

use SQL::Builder::Select;

$select = new SQL::Builder::Select;

$select->where_eq_or('col1', 'col2');

$where = $select->return_where();


$select = new SQL::Builder::Select;

$select->column('*');
$select->table('table');

$select->where( $where );
$select->where_ne_or('col1', 'col2');
$select->where_logic('w AND o');

print $select->return();
[download]

Gives the output

SELECT * FROM table WHERE ((col1 = ?) OR (col2 = ?)) AND ((col1 != ?) 
+OR (col2 != ?))
[download]

In reply to sql builder module - feedback request by fireartist

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.