As for the code you proposed, it seems more or less headed in the right direction, but maybe you're not going far enough in the amount of flexibility you're trying to achieve.

First off, you probably shouldn't be trying to handle the quotation marks around condition values in the "where" clause -- you'll have a problem when someone wants to look for a name like "O'Hara". Use "?" placeholders for the condition values, and pass the actual values as extra parameters when you execute the statement. (This will also make the process more secure.)

Also, it's risky to do a "select * from table" sort of query; it can happen that a column will be added to the table someday, or some other "event" will cause the database to return the columns in a different order from what the perl script was expecting. Ask for the specific columns you want, by name -- and if you don't know the column names, there are ways to find out what they are, and you should do that. (If you're using DBI to return each matching row into a hash, keyed by field name, then this is less of a problem.)

Finally, I'm not sure how much good it does you to generate the sql statement in a sub and then execute it somewhere else -- especially if you're using the "?" place-holders in the statement (as you should). It may be better for the sub to prepare and execute the statement as well.

Assuming you know how the table or RDB schema is defined, and can scope out the appropriate range of "options" for selection criteria, it's not that hard to set up a GUI or web interface to support on-the-fly creation of SQL for a "select" statement; your example, using a single table and conditions that are all conjoined by "and" is the easiest case -- consider that for each field, you need to present the name of the field (so the user knows what is being controlled), a pull-down menu to choose a suitable comparison operator, and a type-in box to provide a suitable value to test for.

• for the "name" and "department" fields, the "operator" choices could be '=', '!=', 'like', 'not like'
• for the "salary" field, the choices could be "=", "!=", "<", ">", "<=", ">=".

(Presumably, you could also provide "is null" and "is not null" for each field, either as separate choices on the "operator" menus, or by accepting the string "NULL" in the type-in box, with the "=" or "!=" operator.) It gets trickier if you're hoping to support joins on two or more tables, or you want to combine "and" and "or" conjunctions in a single query (which would require putting parens in the right places as you construct the statement).

Supposing that the user's specs are stored in a hash, keyed by field name, the code to create the sql statement could be something like this (which has not been tested, and would normally include some error checks at strategic points):

sub runquery {
    my ($dbh, $query, $conds) = @_;
    my @fields = keys %$conds;
    my @values = ();
    if ( @fields ) {
        $query .= " where ";
        $query .= join " and ", map { "$_ $$conds{$_}{op} ?" } @fields
+;
        @values = map { $$conds{$_}{val} } @fields;
    }
    my $sth = $dbh->prepare( $query );
    $sth->execute( @values );

    my $arref = $sth->fetchrow_arrayref; # (use your own favorite here
+)
    $sth->finish;
    return $arref;
}

# that sub would be used as follows:

my %qryconditions;  # to be set via GUI or CGI (or not)

# e.g. 
# = ( name =>   { op => '=', val => 'smith' },
#     salary => { op => '>', val => 50000 },
#     department => { op => 'not like', val => 'eng%' } )

# ... connect to DB (with $dbh as object handle), then:

# maybe there's a UI component to choose or create this as well:
my $qstr = "select name, salary, deparment from employees";

my $result_arref = runquery( $dbh, $qstr, \%qryconditions );
for my $row ( @$result_arref ) {
    print join( " ", @$row ), "\n";
}
[download]

update: fixed a missing "=" in the sub

I don't. I tend to work in highly normalized schemas, with "interesting" queries (3-6 table joins, some non-equi-joins, some outer joins). I could spend time trying to warp a framework into generating workable queries, or I could code them by hand. So far, I've gotten there quicker by coding them by hand. By approaching the problem from use cases, building specific queries, I avoid having to solve a bunch of otherwise general-case problems, and can spend the time instead writing specific unit tests.

The problem with programatically generated queries is edge cases--particularly vendor-specific ones. By the time you fight your way around one or two of them, you've eaten up more time than you would have spent hand-coding a few dozen queries.

How do Monks programatically construct SQL selects.

I don't.

Indeed. It's interesting how this particular question comes up again and again. Yes, SQL queries can be generated, but when you start using a normalized schema auto-generated queries tend to fall down. In order to get good, repeatable performance it is essential to write well behaved queries (i.e. queries where the JOIN and/or WHERE clauses use appropriate indexes) as a single badly written query can bring even very powerful hardware to its knees...

This is one of the reasons why I always try to use stored procedures where I can.

Michael

Class::DBI with Class::DBI::AbstractSearch.

antirice    
The first rule of Perl club is - use Perl
The ith rule of Perl club is - follow rule i - 1 for i > 1

Now, if you're determined to re-invent the wheel, I would suggest the following decomposition:

• Separate the actual DBI framework from the query creation. You already seem to have done this, but I would go further. Create a selector and a other-than-selector. Usually, you will use fetch() with selects and do() if you're not selecting. (Usually, but not always.)
• When creating the query, what sort of validation do you want to do on it? For example, it's quite reasonable to limit which tables a query can be built against, especially if you're building the query from some user-supplied data. The same goes for columns, joins (of all kinds), and limits.
• Speaking of joins, how do you want to handle outer joins? (Inner joins are what most of us consider to be standard joins.) For example, let's say you want to get a list of all the merchants in the database and, if any exist, all the unpaid bills that merchant owes you. That could look something like:

  SELECT merch.id,
         IS_NULL(bill.id, 'NONE')
    FROM merchants merch,
         merchant_unpaid bill
   WHERE merch.id = bill.merch_id(+)
   ORDER BY 1,2
  [download]

  Does your SQL-building methodology handle this? Class::DBI does ...
• Another question is how do your data structures map to your tables. In pathologically simple cases, they are the same. However, if you have any complexity whatsoever, a well-normalized database looks very different from the business logic data structures used in the application. I've seen cases where a data structure maps to a 60++ line sub-query across 5 tables. How are you planning on handling that? (Again, Class::DBI handles this for you ...)
• Yet another issue is performance. When dealing with a RDBMS, the bottleneck immediately shifts from your code to your SQL and how the database is tuned. Adding (or removing!) an index can speed up a cgi script's performance 90% or more! (Yes, I've seen this.) Adjusting the order of the WHERE clauses can contribute 30%-50% of a script's performance. And, there are innumerable other variables. (Again, Class::DBI provides for this ...)

And, I'm just referring to the one whose docs I've actually glanced at. Solutions suggested by other monks, IIRC, do even more than Class::DBI. So, why do you want to re-invent the wheel again?

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified

Try to look at module DBIx::SearchBuilder::Record, which implements a Perl interface for build of SQL queries, espessially this module is useful for serialize and deserialize objects to the database.

_ _ _ _ _ _
  M i c h a e l

I think you're making too much work for yourself, someone has already done it for you.
I highly recommend SQL:Abstract

This module can generate pretty complicated WHERE statements easily. For example, simple key=value pairs are taken to mean equality, and if you want to see if a field is within a set of values, you can use an arrayref. Let's say we wanted to SELECT some data based on your criteria:

use SQL::Abstract;
    my $sql = SQL::Abstract->new;
    
    my %where = (
       name => 'smith',
       salary => {'>', 50000},
       department => { 'not like', 'eng%' }
    );


    my($stmt, @bind) = $sql->select($table, *, \%where, \@order);
[download]

This module also handles insert, update and delete, as well as ORing and ANDing your conditional clauses

Yep. It's understandable that you need to construct an SQL query dynamically. I had to do the same thing when search parameters were given through an XML. But since this kind of Structured information is exactly what perl is for, there are ways to make your code do more with less. The code I use:

my $db_query = 'SELECT ';
$db_query .= join ', ', @select;
$db_query .= ' FROM ';
$db_query .= join ', ', @from;
$db_query .= ' WHERE ';
$db_query .= join ' AND ', @limits;
$db_query .= ';'; # Not mandatory, just for good order.
[download]

The way I did this, I had XML tags stating:

• What to show (push @select, foo)
• What table (push @from, bar)
• Search criterion, for example <above>30</above>, that was sent to the matching subroutine to construct the baz for push @limits, baz.
• Search criterion expected from CGI parameters - in which case I used CGI.pm's auto escaping to avoid the escaping problems (although it's not so smooth so test thoroughly).

Of course, your construction need are probably different, so customize happily. What I really wanted to say was use map, join, reduce, and other list functions instead of loop and concatenation.

As for modules, sometimes it's just quicker, and surely more fun (if you're not under a deadline) to do your own coding, and you always learn from it. Well, TIMTOWTDI...

PS - If you're expecting outside input throw in extra validation and taint checking code or you'll get errors from the DB or much worse.

$db_query .= ';'; # Not mandatory, just for good order.

Actualy not. I don't know what DBD you're using, but some databases don't like having a trailing semicolon when fed via DBI. It's generally better to leave it out.

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

Note: All code is untested, unless otherwise stated

I use Class::DBI to store these components in a database: as Fields, Filters, Joins and Groups (purely an organizational thing). You can get some cute optimizations with this, for example if a field is selected from another table it usually is inserted as a subselected, but if a filter is added on that same table, a join (inner) is automatically included and the field uses that as well.

The real advantage of this for me is that it provides methods to quickly construct html forms for these queries (coupled with Template::Toolkit a Filter "knows" how to draw itself), after which I can just give the parsed CGI.pm query object to the SQL thing and get back the complete SQL statement. Makes working with sessions trivial as well.

I am not sure how many people would find this sort of thing useful, but in this case it's been working pretty well so far.

I am reinventing this particular wheel right now.

Actually, you're not. You're using components (like Class::DBI and Template::ToolKit) to create a custom application. The particular wheel in question is supplied, in your case, by Class::DBI. The OP was discussing how to code the functionality provided by Class::DBI, which you are intelligently using. :-)

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

my $sql = <<EOF;
  SELECT a
  FROM b
  WHERE c
  ORDER BY d
EOF
[download]

DESCRIPTION
  A very simple example. It instanciates the generator and creates some
  example output, which could be simply piped to an mysql database for
  testing (be careful if tables/database name is existing. I guess not !).

CODE
  my $table = 'sql_statement_construct_generator_table';

  my $database = 'sql_statement_construct_generator_db';

  my %types = ( row1 => 'VARCHAR(10) AUTO_INCREMENT PRIMARY KEY',

          row2 => 'INTEGER',

          row3 => 'VARCHAR(20)'
  );

  my %columns = ( row1 => '1', row2 => '2', row3 => '3' );

  my %alias = ( row1 => 'Name', row2 => 'Age', row3 => 'SocialID' );

          my $sql = new SQL::Generator(

                  LANG => 'MYSQL',

                  post => ";\n",

                  history => 1,

                  autoprint => 0,

                  prettyprint => 0

          ) or die 'constructor failed';

          $sql->CREATE( DATABASE => $database );

          $sql->USE( DATABASE => $database );

          $sql->CREATE( COLS => \%types, TABLE => $table );

          $sql->DESCRIBE( TABLE => $table );

          $sql->INSERT(

                  COLS =>  keys %columns ,

                  VALUES =>  values %columns ,

                  INTO => $table

                  );

          foreach (keys %columns) { $columns{$_}++ }

          $sql->INSERT( SET => \%columns , INTO => $table );

          foreach (keys %columns) { $columns{$_}++ }

          $sql->REPLACE(

                  COLS =>  keys %columns ,

                  VALUES =>  values %columns ,

                  INTO => $table,

                  );

          $sql->SELECT( ROWS => '*', FROM => $table );

          $sql->SELECT( ROWS =>  keys %types , FROM => $table );

          $sql->SELECT(

                  ROWS =>  \%alias,

                  FROM => $table,

                  WHERE => 'row1 = 1 AND row3 = 3'

                  );

          $sql->DROP( TABLE => $table );

          $sql->DROP( DATABASE => $database );

          # evocate an errormsg

          print "\nDumping sql script:\n\n";

          for( $sql->HISTORY() )
          {
                  printf "%s", $_;
          }

OUTPUT

CREATE DATABASE sql_statement_construct_db; 

USE sql_statement_construct_db; 

CREATE TABLE sql_statement_construct_table (
 row1 VARCHAR(10) AUTO_INCREMENT PRIMARY KEY, 
 row2 INTEGER, 
 row3 VARCHAR(20) 
); 

DESCRIBE sql_statement_construct_table; 

INSERT INTO sql_statement_construct_table ( row1, row2, row3 ) VALUES( 1, 2, 3 );

INSERT INTO sql_statement_construct_table SET row1='2', row2='3',
row3='4'; 

REPLACE INTO sql_statement_construct_table ( row1, row2, row3
) VALUES( 3, 4, 5 ); 

SELECT * FROM sql_statement_construct_table; 

SELECT row1, row2, row3 FROM sql_statement_construct_table; 

SELECT row1 AS 'Name', row2 AS 'Age', row3 AS 'SocialID' FROM sql_statement_construct_table WHERE row1 = 1 AND row3 = 3; 

DROP TABLE sql_statement_construct_table; 

DROP DATABASE sql_statement_construct_db;