[RFC] SQL::QueryManager
From: Michael B (mbarn_at_cidr.jhmi.edu)
Date: 11/13/04
- Previous message: Sisyphus: "Re: [OT] Compiler and OS considerations"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: 12 Nov 2004 16:20:22 -0800
This is an RFC for the proposed module SQL::QueryManager
NAME
SQL::QueryManager - An Object-Oriented DBM::Deep based SQL Query
Manager
SYNOPSIS
use SQL::QueryManager;
$qm = SQL::QueryManager->new(
-v => 1,
-sql_query_file => '/path/to/file.db'
);
# Add a query
$qm->add_query( 'name_query',
qq[
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)
);
$sth->execute();
while ( @array = $sth->fetchrow_array() ) {
#...do something
}
}
DESCRIPTION
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')
calls.
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
CIDR, JHU SOM
Baltimore, Md.
- Previous message: Sisyphus: "Re: [OT] Compiler and OS considerations"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
