[RFC] SQL::QueryManager

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

This is an RFC for the proposed module SQL::QueryManager


SQL::QueryManager - An Object-Oriented DBM::Deep based SQL Query


use SQL::QueryManager;

$qm = SQL::QueryManager->new(
    -v => 1,
    -sql_query_file => '/path/to/file.db'

# Add a query
$qm->add_query( 'name_query',
      SELECT firstname, lastname
      FROM names
      WHERE lastname = 'XXX'

# Get a query
$query = $qm->get_query( 'query_name' );

# Get query and set bind variable(s)...
foreach $last_name (@last_names) {
    $query = prepare_dynamic_query( 'name_query', 'XXX', $last_name );

# 'Inline' query retrieval and database call using DBI
foreach $last_name (@last_names) {
    $sth = $dbh->prepare(
        $qm->prepare_dynamic_query('name_query', 'XXX', $last_name)
    while ( @array = $sth->fetchrow_array() ) {
        #...do something


This module is designed to allow for storage and retrieval of SQL
queries in a central repository.  It uses an implementation of Joseph
Huckaby's DBM::Deep, a pure Perl multi-level hash/array DBM to store
and retrieve SQL queries. This allows for easy programmatic retrieval
of common queries without having to cut and paste or (worst of all)
re-code the queries every time you need them. Support is also provided
for dynamic queries where bind variable(s) in the 'WHERE' clause
is/are unknown.  This module is useful for grouping database queries
by project and/or function.  It is most useful in large projects with
somewhat complex queries.  It is designed so that no limitations apply
on what databases it can interact with. If there is a DBD::(database)
module, this software will work with it, as the SQL is supplied by the
user.  It was
written to be a container to help manage SQL queries in a central
location, eliminating the need to hunt for the particular .sql file
needed, which can become unruly in a project with hundreds or
thousands of separate queries. Of
course, one still needs to keep track of the query names, but this is
easily accomplished with a central population script that contains all
of the project's queries inside of the $qm->add_query('name','query')

I wrote it to fill the niche between full-blown SQL Query-Generation
modules and
managing thousands of .sql files by hand.  The former was too complex
for my
needs and the latter was a frightening prospect.

Michael Barnhart mbarn_at_cidr_dot_jhmi_dot_edu

Baltimore, Md.

Site Timeline