in reply to sql builder module - feedback request

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)>(width*height)

[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

Replies are listed 'Best First'.
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]

In Section Seekers of Perl Wisdom