sql builder module - feedback request

fireartist has asked for the wisdom of the Perl Monks concerning the following question:

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]

Comment on sql builder module - feedback request Select or Download Code

Replies are listed 'Best First'.
Re: sql builder module - feedback request by blokhead (Monsignor) on Nov 21, 2002 at 17:18 UTC
At a first rough glance it looks like you are doing fine. I have questions about the interface, though. It seems like a lot of wordage just to make a simple query. Have you considered letting the method calls return the object? `# instead of this $select = new SQL::Builder::Select; $select->column('col1', 'col2'); $select->table('table'); $select->order_by('col1'); $select->limit_value('10'); $select->limit_offset('10'); ########## # you could do this: $select = new SQL::Builder::Select; $select->column('col1','col2') ->table('table') ->order_by('col1') ->limit_value('10') ->limit_offset('10');` [download] It may be something worth considering, although I'm sure you have your reasons for having the interface like it is. I've tried doing similar automagic query generation like this before, and in general it can get very ugly no matter how hard you try, with stuff like this: `where_ne_or`, `where_gt_or`, etc... You have to find your own balance between simplicity of interface and the domain of queries which you want to be able to represent. One other thing I've noticed is that I don't see any provision for column aggregates like `count()` and `max()` and friends, in fact any SQL functions! Right now your module is a powerful logic combinator (just ANDs and ORs). But think about whether (and how) you want to represent queries like `select count() from polygons where sin(angle1)>(widthheight)` [download] By all means, you don't have to have provisions in your module to make these queries, but it might be beneficial to think about more common queries that are more than just AND/OR combinations. Update: Come to think of it, you have a lot of similar code in the `where_*` methods -- you may want to consider using `AUTOLOAD` for these. blokhead	[reply] [d/l] [select]
Re: Re: sql builder module - feedback request by fireartist (Chaplain) on Nov 22, 2002 at 09:57 UTC
Have you considered letting the method calls return the object? Hmm, what does that mean? google.com/search?q="return the method" site:www.perldoc.com A quick read of the Data::Dumper source later; Wow, thanks, that works great! There is a lot more I would like to implement, but at the moment I'm constrained by the specific requirements of the project I'm doing at work, at what functionality the module needs for that. - Unfortunately, it'll very much be a 'add it as it's required' approach. I will be looking further at your other suggestions though, thanks! For now however, I've changed it so that doing, `use strict; use warnings; use SQL::Builder::Select; my $select = new SQL::Builder::Select; $select->column( '' ) ->table( 'main' ) ->where_ne_or( 'col1' ) ->where_eq_and( 'col1', 'col2' ) ->where_logic( 'a and o') ->order_by( 'col1' ) ->limit_value( 10 ) ->limit_offset( 20 ) ->debug( 1 ); print $select->return();` [download] gives the output* `DEBUG: logic broken up 'a ', 'AND', ' o' DEBUG: joining AND - 'col1 = ?', 'col2 = ?' DEBUG: no need to join 'col1 != ?' DEBUG: joining AND - '(col1 = ?) AND (col2 = ?)', 'col1 != ?' DEBUG: sql SELECT * FROM main WHERE ((col1 = ?) AND (col2 = ?)) AND (col1 != +?) ORDER BY col1 LIMIT 20, 10` [download]	[reply] [d/l] [select]
Re: sql builder module - feedback request by princepawn (Parson) on Nov 21, 2002 at 19:07 UTC
Please tell us the advantages of this over: DBIx::Recordset Alzabo DBIx::SearchBuilder Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality	[reply]
Re: Re: sql builder module - feedback request by fireartist (Chaplain) on Nov 22, 2002 at 09:03 UTC
You forgot SQL::Generator ;) The 'features' I'm most interested in are 1. dynamically creating `where` clauses with the module; not creating it myself with logic in the script and then passing the thing to the module, 2. being able to pass variables (such as column names) as a scalar or list, repeatedly to build up a bigger list. - Again, not using logic within the script to create an array or hash to pass to the module. DBIx::SearchBuilder - The POD contains 2 instances of the word 'where' - both are in deprecated methods. I can't even be bothered reading the docs further. Alzabo - While this does look interesting (I'd never come across it before) - a read of the Alzabo::SQLMaker and Alzabo::SQLMaker::MySQL docs doesn't reveal any indication of the kind of functionality I specified above. DBIx::RecordSet - Again, it looks like a great module, but it unfortunately doesn't appear to meet my requirements. SQL::Generator - As above. - I looked at the source of this before I started work on my own, and I think it's very nicely written but unfortunately doesn't have the kind of dynamic '`where`' generation I'm after. I'm aware that my attempt has a long way to go - particularly regarding blokhead's comments above, and I would certainly use any module that provided the functionality I'm after, if only I could find one.	[reply] [d/l] [select]