[ 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.
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();
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 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.
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');
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');
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);
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.
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 |
Copyright Perl Training Australia. Contact us at contact@perltraining.com.au
