Writing less SQL with SQL::Abstract

[ Perl tips index ]
[ Subscribe to Perl tips ]

If you can't work with an Object Relational Model (ORM) then you may find yourself writing SQL directly. SQL is an expressive and heavily used language for working with relational databases, however for a software developer it does present some problems. Very often we may have information contained in a data structure that we wish to use as the basis of a query, but transforming that data into good SQL can be challenging.

This task is particularly difficult when the data may contain a variable number of fields and constraints, and the job of managing the SQL generation is often left to the individual developer. Hand generating is not only tedious, it can also be error prone.

Using SQL::Abstract

The SQL::Abstract module is available from the CPAN. It is uses an object-oriented interface for controlling options and generating SQL. Creating a SQL::Abstract object is straightforward:

        use SQL::Abstract;
        
        my $sql = SQL::Abstract->new();

Insert statements

Using SQL::Abstract to generate an insert statement is simple to use and understand. Provided with a hash of key/value pairs, SQL::Abstract will generate a corresponding insert statement. For example, using the following code:

        my %record = (
                firstname => 'Buffy',
                lastname  => 'Summers',
                address   => '1630 Revello Drive',
                city      => 'Sunnydale',
                state     => 'California',
                position  => 'Slayer',
                wage      => 50000
        );

        my ($stmt, @bind) = $sql->insert('staff', \%record);

would generate:

        $stmt = "INSERT INTO staff
                        (firstname, lastname, address, city,
                        state, position, wage)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        @bind = ('Buffy','Summers','1630 Revello Drive',
                'Sunnydale','California','Slayer',50000);

These variables can now be used directly with DBI:

        my $sth = $dbh->prepare($stmt);
        $sth->execute(@bind);

        # Alternatively:

        $dbh->do($stmt,undef,@bind);

Being able to turn a hash into SQL can save a large amount of time both writing SQL and selecting bind values in the correct order. It also produces a more flexible program, as we can accommodate schema changes just by altering the data structure passed to SQL::Abstract.

We can also pass SQL::Abstract an array reference when generating insert queries. This produces an insert statement without column names:

        my @record = (undef, 'Buffy', 'Summers', '1630 Revello Drive',
                      'Sunnydale', 'California', 'Slayer', 50000);
        my ($stmt, @bind) = $sql->insert('staff', \@record);

        # This produces:

        $stmt = "INSERT INTO staff VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        @bind = (undef, 'Buffy', 'Summers', '1630 Revello Drive',
                'Sunnydale', 'California', 'Slayer', 50000);

although this relies on us always inserting our values in the right order.

Select statements

Select statements are much more complex than simple inserts, as they almost always have a complicated where clause that needs to be considered. SQL::Abstract allows the generation of very detailed where statements, however we'll keep it simple, here.

The basic select generator is used by calling

        my ($stmt, @bind) = $sql->select($table, $fields, $where, $order);

Only the table and fields are required. The fields may be a single field, "*" to select all fields, or an array reference of fields to retrieve.

The where statement is based upon the contents of the hash reference provided. The two most commonly used formats are those of simple equality (represented by a simple key/value pair), and checking for the existence in a set (by using an arrayref as the value). The following example demonstrates both, and finds all Slayers, Watchers, and Software Engineers living in Sunnydale.

        my @fields = qw(firstname lastname);
        my %where = (
                city     => 'Sunnydale',
                position => ['Slayer','Watcher','Software Engineer'],
        );
                
        my ($stmt, @bind) = $sql->select('staff', \@fields, \%where);

        # This generates the following:
        $stmt = 'SELECT firstname, lastname FROM staff 
                WHERE city = ? 
                AND (position = ? OR position = ? OR position = ?)';

        @bind = ('Sunnydale','Slayer','Watcher','Software Engineer');

It should be noted that when an array reference is used as a value, the alternatives are ORed together, however the entire statement is joined using AND.

SQL::Abstract also makes it possible to perform more complex operations than simple equality, although the syntax also becomes correspondingly more complex. To do so, we must use a hashref of comparison => value pairs. The following code demonstrates this to search for the same set of staff members, but only those with a wage of less than 100,000.

        my @fields = qw(firstname lastname);
        my %where = (
                city     => 'Sunnydale',
                position => ['Slayer','Watcher','Software Engineer'],
                wage     => { '<' => 100000 },
        );

        my ($stmt, @bind) = $sql->select('staff',\@fields,\%where);

        # This generates the following:

        $stmt = 'SELECT firstname, lastname FROM staff 
                WHERE city = ? 
                AND (position = ? OR position = ? OR position = ?) 
                AND wage < ?';

        @bind = ('Sunnydale','Slayer','Watcher','Software Engineer', 100000);

Being able to convert data structures into complex where statements is particularly useful when writing search interfaces, which is traditionally a difficult task to do with DBI alone.

Order by

Ordering clauses can also be quite complex. This can be just a regular scalar (the column name to order by), a hash of { -desc => 'col' } or { -asc => 'col' } or an array of the previous. For example:

        my @fields = qw(firstname lastname);
        my %where = (
                city     => 'Sunnydale',
                position => ['Slayer','Watcher','Software Engineer'],
        );
        my @order = (
                wage,
                { -desc => position },
        );
                
        my ($stmt, @bind) = $sql->select('staff', \@fields, \%where, \@order);

        # This generates the following:
        $stmt = 'SELECT firstname, lastname FROM staff 
                WHERE city = ? 
                AND (position = ? OR position = ? OR position = ?)
                ORDER BY wage, position DESC';

        @bind = ('Sunnydale','Slayer','Watcher','Software Engineer');

where

SQL::Abstract also provides a where method, which only generates a where clause. This is particularly useful when you have an otherwise static query but a potentially variable WHERE clause. It's used in the same way as other methods, except $stmt only contains the where clause.

        my @order =  ('position');
        my %where = (
                lastname  => 'Summers',
        );

        my ($stmt, @bind) = $sql->where(\%where, \@order);

        # This generates the following:

        $stmt = 'WHERE ( lastname = ? ) ORDER BY position';

        @bind = ('Summers');

Other statements

SQL::Abstract also supports update and delete statements, using the same principles as those in select and insert shown above. The syntax is simply:

        my %fieldvals = (
                address   => 'Room 214, Stevenson Hall, '.
                             'University of California',
                position  => 'Slayer + Student',
        );
        my %where = (
                firstname => 'Buffy',
                lastname  => 'Summers',
        );

        my ($stmt, @bind) = $sql->update($table,\%fieldvals,\%where);

        my ($stmt, @bind) = $sql->delete($table,\%where);

Performance

When performing similar queries repeatedly, it can be inefficient to regenerate the SQL every time, particularly if we're performing a lot of work. One example where regenerating SQL is loading a large amount of data from a file; the SQL will remain the same, but the data records will change each time. This is particularly important as DBI allows a single statement handle to be used for multiple queries, saving a large amount of processing time.

SQL::Abstract provides a special method called values that simply returns the list of bind values that would be generated by any other SQL::Abstract method call.

The following code example generates the SQL code and statement handle only once, and then uses a call to $sql->values to prepare the data for insertion each time:

        my @data_set = (
                {
                        firstname => 'Buffy',
                        lastname  => 'Summers',
                        address   => '1630 Revello Drive',
                        city      => 'Sunnydale',
                        state     => 'California',
                        position  => 'Slayer',
                        wage      => 50000
                },
                {
                        firstname => 'Willow',
                        lastname  => 'Rosenberg',
                        address   => 'Room 214, Stevenson Hall',
                        city      => 'Sunnydale',
                        state     => 'California',
                        position  => 'Witch',
                        wage      => 50000
                },
                # etc
        );
                        
        my ($sth, $stmt);

        foreach my $hash_ref (@data_set) {
                $stmt ||= $sql->insert('staff',$hash_ref);
                $sth  ||= $dbh->prepare($stmt);
                $sth->execute($sql->values($hash_ref));
        }

Of course, if the absolute maximum speed is needed, it's still faster to hand-craft the SQL and data preparation, however the above technique provides a significant gain without much extra effort.

Conclusion

The SQL::Abstract module allows Perl data structures to be converted into SQL, saving the developer from having to write SQL by hand. Using SQL::Abstract is particularly useful when writing search interfaces, when dealing with data from a variety of sources, or when working with existing Perl code that already returns data in structure that SQL::Abstract can process.

More information on SQL::Abstract can be found at perldoc SQL::Abstract.

[ Perl tips index ]
[ Subscribe to Perl tips ]


This Perl tip and associated text is copyright Perl Training Australia. You may freely distribute this text so long as it is distributed in full with this Copyright noticed attached.

If you have any questions please don't hesitate to contact us:

Email: contact@perltraining.com.au
Phone: 03 9354 6001 (Australia)
International: +61 3 9354 6001

Valid XHTML 1.0 Valid CSS