Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

How do Monks programatically construct SQL selects

by Cody Pendant (Prior)
on Sep 03, 2003 at 02:19 UTC ( #288485=perlquestion: print w/ replies, xml ) Need Help??
Cody Pendant has asked for the wisdom of the Perl Monks concerning the following question:

I'm currently thinking about SQL selects and how they can be constructed, so that I can construct them from subroutines and make things more modular.

Something like

use strict; sub make_query { my @clauses = @_; my $count = 0; my $sql = "select * from employees \n"; foreach (@clauses) { if ($count > 0) { $sql .= ' AND ' } else { $sql .= ' WHERE ' } foreach ($_) { my @criteria = @{$_}; if ($criteria[2] !~ /^\d+$/) { $criteria[2] = "'$criteria[2]'"; } $sql .= " @criteria \n"; } $count++; } return $sql; } my @array = ( [ 'name', '=', 'smith' ], [ 'salary', '>', '50000' ], [ 'department', 'NOT LIKE', 'eng%' ] ); print make_query(@array); ### select * from employees ### WHERE name = 'smith' ### AND salary > 50000 ### AND department NOT LIKE 'eng%'

So am I missing something, going about it all wrong, making security holes for myself, working too hard, not working hard enough ... any thoughts?



($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss') =~y~b-v~a-z~s; print

Comment on How do Monks programatically construct SQL selects
Select or Download Code
Re: How do Monks programatically construct SQL selects
by antirice (Priest) on Sep 03, 2003 at 02:26 UTC
Re: How do Monks programatically construct SQL selects
by simonm (Vicar) on Sep 03, 2003 at 05:10 UTC
    There are a lot of existing solutions out there waiting to be reused. Cf DBIx-SQLEngine.
Re: How do Monks programatically construct SQL selects
by nite_man (Deacon) on Sep 03, 2003 at 06:28 UTC

    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.

    Maybe it will be good for you.

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

Re: How do Monks programatically construct SQL selects
by graff (Chancellor) on Sep 03, 2003 at 07:00 UTC
    I followed the CPAN links mentioned in the first two replies, and those look like they are certainly worth trying out. (I'll try them myself next chance I get -- but when I need to do "production code", I tend to be a little skittish about modules whose current version number starts with "0." -- maybe that's just shyness or modesty on the part of the module authors, but I do have to wonder...)

    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"; }
    Naturally, there are other ways to do this, and maybe the modules cited in earlier replies would make it all even easier. (Maybe you've noticed that I left out the handling of "is (not) null" conditions "as an exercise for the reader"; you could also consider offering options like an "order by" clause, etc.)

    update: fixed a missing "=" in the sub

Re: How do Monks programatically construct SQL selects
by dws (Chancellor) on Sep 03, 2003 at 07:58 UTC
    How do Monks programatically construct SQL selects.

    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

Re: How do Monks programatically construct SQL selects
by yosefm (Friar) on Sep 03, 2003 at 11:36 UTC
    So am I ... working too hard?

    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.

    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

Re: How do Monks programatically construct SQL selects
by clscott (Friar) on Sep 03, 2003 at 12:08 UTC

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

    The rest is shamelessly ripped from module the docs:

    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);

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

    --
    Clayton
Re: How do Monks programatically construct SQL selects
by dragonchild (Archbishop) on Sep 03, 2003 at 12:17 UTC
    Use one of the many execellent solutions on CPAN. This is not only the correct solution, in terms of bug-free and maintainable code, it's also, in most cases, the faster solution. There are at least 5-7 different solutions, from Class::DBI to various SQL dictionaries to all sorts of options.

    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
      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

Re: How do Monks programatically construct SQL selects
by glwtta (Hermit) on Sep 03, 2003 at 12:51 UTC
    I am reinventing this particular wheel right now. I am working on a "mart" type application; the idea is basically to have common cases (and not so common, but complicated ones) wrapped into their own classes; each provides a sql() method, which are collated together to create the final query.

    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.

Re: How do Monks programatically construct SQL selects
by Anonymous Monk on Sep 04, 2003 at 07:28 UTC
    I wrote SQL::Generator some time ago. But nobody uses it, except me. Its the approach to have the SQL syntax somehow functional to perl. Example:
    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;
    
    BE WARNED: The documentation isn't that deep. You can do a lot, but its not documented. The 'AUTOLOAD' feature lets you skip the oo-interface. BTW: Currently its very msql-centric. Good Luck, Murat
Re: How do Monks programatically construct SQL selects
by Roger (Parson) on Sep 05, 2003 at 05:25 UTC
    When working with SQL's, you probably want to keep your program as simple as possible when constructing SQL's. I often use HEREDOC's to construct SQL queries.

    Example:
    my $sql = <<EOF; SELECT a FROM b WHERE c ORDER BY d EOF


    Where the a, b, c, d can be variables or hardcoded values.

    This is certainly more readable than using a bunch of join's.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://288485]
Front-paged by thunders
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (5)
As of 2014-08-31 06:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (294 votes), past polls