Conway D. Perl Hacks / Hack 23. Build a SQL Library

Computers & Technology
Education & Reference
Business & Investing
Science & Math

Perl Hacks
Authors: Conway D.
Published year: 2004
Pages: 32-33/141

<< Previous page
Table of contents
Next page >>

Hack 23. Build a SQL Library

Store queries where non-programmers can maintain them.

Most serious programmers know the dangers of mixing their user interface code (HTML, GUI, text) with their business logic. When you have a designer making things pretty, it's too much work for any programmer to integrate change after change to font size , placement, and color .

If you have a DBA, the same goes for your SQL.

Why not keep your queries where they don't clutter up your code and where your DBA can modify and optimize them without worrying about a misplaced brace or semicolon breaking your software? If you use SQL::Library with a plain text file under version control, you can.

The Hack

Install SQL::Library from the CPAN. Extract all of the SQL from your code into one place [Hack #22], and then put it all in a plain text file in INI format:

[select_nodemethod_attributes]
SELECT    types.title     AS class,
          methods.title   AS method,
          nodemethod.code AS code
FROM      nodemethod
LEFT JOIN node            AS types
ON        types.node_id = nodemethod.supports_nodetype

The section title (the names in square brackets) is the name of the query and the rest is the SQL. Save the file (for example, nodemethods.sql ). Then from your code, create a SQL::Library object:

use SQL::Library;

my $library = SQL::Library->new({ lib => 'nodemethods.sql' });

Running the Hack

Whenever you need a query, retrieve it by name from the library:

my $sth = $dbh->prepare( $library->retr( 'select_nodemethod_attributes' ) );

From there, treat it as normal.

Hacking the Hack

This isn't very exciting until you get to more complex querieswhere the order of joins is important, where the exact nature of queries changes, or where there's lots of manipulation and editing going on. Being able to modify the SQL without touching the code is very handy.

For example, consider a reporting application. Choose a filename to hold the queries. Write a bit of code that processes the queries and feeds them to a library to produce graphs or spreadsheets. (The trick with NAME_lc in "Bind Database Columns " [Hack #25] is very useful here.) Then just loop through all of the queries in the library, preparing and executing them, and processing the results:

use SQL::Library;

my $library = SQL::Library->new({ lib => 'daily_reports.sql' });

for my $query ( $library->elements( ) )
{
    my $sth = $dbh->prepare( $query );
    my %columns;

    $sth->bind_columns( \@columns{ @{ $sth-> } } );
    $sth->execute( );

    process_report( \%columns );
}

Now whenever your users want another query, just write it and store it in the appropriate library file. You never have to touch the reporting program (as long as it can draw its pretty graphs correctly)and if you can teach your users to write their own queries, you can make your job that much easier.

Hack 24. Query Databases Dynamically Without SQL

Write Perl, not SQL.

SQL is a mini-language with its own tricks and traps. Embedded SQL is the bane of many programs, where readability and findability is a concern. Generated SQL isn't always the answer either, with all of the quoting rules and weird options.

In cases where you don't have a series of fully baked SQL statements you always runwhere query parameters and even result field names come from user requests , for examplelet SQL::Abstract do it for you.

The Hack

Create a new SQL::Abstract object, pass in some data, and go.

Suppose you have a reporting application with a nice interface that allows people to view any list of columns from a set of tables in any order with almost any constraint. Assuming a well- factored application, the model might have a method resembling:

use SQL::Abstract;

sub get_select_sth
{
    my ($self, $table, $columns, $where) = @_;

    my $sql           = SQL::Abstract->new( );
    my ($stmt, @bins) = $sql->select( $table, $columns, $where );
    my $sth           = $self->get_dbh( )->prepare( $stmt );

    $sth->execute( );
    return $sth;
}

$table is a string containing the name of the table (or view, preferably) to query, $columns is an array reference of names of columns to view, and $where is a hash reference associating columns to values or ranges.

If a user wants to query the users table for login_name , last_accessed_on , and email_address columns for all users whose signup_date is newer than 20050101 , the calling code might be equivalent to:

my $table   = 'users';
my $columns = [qw( login_name last_accessed_on email_address )];
my $where   = { signup_date => { '>=', '20050101' } };
my $sth     = $model->get_select_sth( $table, $columns, $where );

The returned $sth is a normal iterable DBI statement handle, suitable for passing to a templating system or other user interface view component. This is very useful for selecting only the interesting parts of a table or view.

Hacking the Hack

There's no reason you have to let users select the kind of information they want to view. Perhaps you have system administrators who should be able to see (and update) any non-key column in the users table, managers who should be able to see and update most personnel- related columns, and normal users who should only see demographic information.

You can use the same underlying model to fetch information from the databasejust add a layer over it to exclude requested columns that the particular user of the system shouldn't see. Assuming that you have an object representing the user type with a method that returns the allowed columns for a particular table, call restrict_columns( ) before get_select_sth( ) :

sub restrict_columns
{
    my ($self, $user, $table, $columns) = @_;
    my $user_columns                    = $user->get_columns_for( $table );
    return [ grep { exists $user_columns->{ $_ } } ] @$columns;
}

Instead of maintaining separate SQL queries for each type of user accessing the system, you can maintain a list somewhere of appropriate view and update columns for each type of user, reusing the query generator. If you keep the list of types and allowed columns in the database or in a configuration file somewhere, you have data-driven programming and an easy-to-maintain system.