Since the PAUSE form is not responding for me, please forgive this manual
email...
Thanks,
Phil R Lawrence
prl -AT- cpan -DOT- org
Name of the module
SQL::Snippet
Module List Chapter
11) String Processing, etc.
Development Stage (Note: No implied timescales)
b -- beta
Support Level
d -- developer
Language Used
p -- perl
Interface Style
O -- object oriented
Public license
p -- Standard Perl
Description in Module List (44 chars limit)
Enables an OO Interface to Your RDBMS
Places where this module has been or will be discussed publicly
clpm, YAPC::NA 2002
Modules with similar functionality
SQL::QueryBuilder::Simple, Relations::Query
Rationale
OVERVIEW
SQL::Snippet enables an OO (Object Oriented) interface to your RDBMS (Relational
Database Management System). It does this by generating correct SQL based on
your OO requests. Actual RDBMS-specific info (table names, joins, subselects,
etc.) does not appear in your invocant script.
While other modules exist that will create SQL for you, they do so only insofar
as the invocant script passes RDBMS-specific info. SQL::Snippet is much simpler
to use. You tell it you want SQL for 'this' or 'that', and it takes care of
figuring out what tables and joins are involved.
APPLICATION
Reports and other scripts that use SQL often look like someone's first effort at
web programming with Perl; only instead of being littered with HTML they are
full of SQL: SQL that is duplicated in another script, and another, and
another... So if it's a good thing to get your HTML out of your Perl scripts
and into its own repository, why not do the same with your SQL?
SQL::Snippet will require you to create a Perl module full of, well, snippets of
SQL. For example, that snippet you use to limit a selection by gender. And
that other one you use to grab all the miscreants that have never ordered from
you even though they're still receiving your catalog. Wait, that's two
snippets: the 'catalog_recipient' population and the
'never_ordered_a_single_thing' limit, which creates a subset of the
'catalog_recipient' population.
The point is that, like most things, you can break those SQL statements down
into their parts and store them in one central module, instead of in individual
scripts. Then you can use SQL::Snippet to dynamically assemble these snippets
as needed. This can even be done in an ad hoc manner; you can let the user
build whatever question he likes of the RDBMS, and SQL::Snippet will take care
of turning it into SQL. Once you have modularized your SQL into snippets, all
you're left with is some easy OO syntax.
PARADIGM
There are four elements to the paradigm that SQL::Snippet uses to enable easy
interaction with your RDBMS.
1. pop - Populations
2. lim - Limits applicable to those populations
3. parm - Parameters needed to flesh out the pops and lims
4. methods - each of the above three items has various built in
methods associated with it. Additionally, objects
and methods are automatically generated from the
snippets in your Repository as needed. (See below.)
Populations
Population objects are real world things that are represented in your RDBMS.
The following are example of objects you might find in your RDBMS:
- people
- suppliers
- parts
- assemblies
- SKUs (Stock Keeping Units)
All of these are real world things. Information about them in your RDBMS may be
contained in one and only one table, or the information may be normalized
(split) between many different tables. In the latter case, the SQL necessary to
query your object of interest could get rather complicated, necessarily
including all of the relevant tables and joins.
SQL::Snippet abstracts the details of your RDBMS table structure into objects
that you can simply reference in your scripts; SQL::Snippet will fill in all the
details behind the scenes. For example:
# prints the canonical SQL statement needed to
# query info about assemblies from your RDBMS
print $snippet->pop->assemblies->query;
Limits
Limits are types of real world attributes that can apply to the population
objects. For example, gender is a type of attribute applicable to people, but
not to assemblies. Weight is a type of attribute applicable to both people and
assemblies. By indicating a limit for a given population, you can sharpen the
definition of that population. For example:
# apply the gender limit to our 'people' population
$snippet->pop->people->lim->new( 'gender' );
Note that neither we nor the user of the script has yet specified what gender to
limit the population by (Unknown, Female, etc.). We have only indicated that
the population should have a gender limit applied to it. To complete the job we
need to look at the next element of the SQL::Snippet paradigm.
Parameters
Parameters are the actual attributes used by population limits. In the above
example we specified a gender limit, so now we should specify the gender
parameter to be used by that limit. The gender limit requires only one
parameter, aptly named 'gender'.
# limit the people population to those with gender attribute of
# Female or Unknown
$snippet->pop->people->lim->gender->parm->gender( ['U','F'] );
Actually, we don't need to say:
$snippet->pop->people->lim->new( 'gender' );
before saying:
$snippet->pop->people->lim->gender->parm->gender( ['U','F'] );
The gender limit is autoinstantiated in the latter example. In fact, it's all
autoinstantiated, from pop to gender. You start your script with a simple
snippet object, and the rest autoinstantiates as needed.
Methods
In the above examples you see that pop, lim, and parm are autoinstantiated
objects furnished by SQL::Snippet. There are multiple methods associated with
each one. For example, we called the 'new' method on lim. But note that most
of the methods and objects we used were actually named by us, the *users* of
SQL::Snippet, not by me, the guy who wrote SQL::Snippet. For example, not only
did we use the autoinstantiated 'gender' object, we also called the 'gender'
method on parm (see the last example above). Note that we, the users of
SQL::Snippet did not have to actually code a 'gender' method somewhere -- far
from it. This method was AUTOLOADed for us. All we had to do was create a
repository with pop, lim, and parm snippets, and the rest was automatic. For
example, here is the 'gender' snippet from SQL::Snippet::ExampleRepository, an
example repository included with the distribution:
if ($parm eq 'gender') {
return (
name => $parm,
default => '',
msg => "\nBy default, gender will bear no impact " .
"on selection. Press ENTER to accept " .
"this default, or, to limit the selection " .
"by gender, type one or more gender codes " .
"from the following list: M (Male), " .
"F (Female), U (Unknown)",
delimiter => ',',
prompt => 'Gender',
list_check => [ 'M','F','U' ],
);
} elsif ($parm eq ...
When you reference 'gender' in your code, either as an object or method (as in
the above examples), SQL::Snippet will automatically create the needed object or
method using this snippet as a source of basic information. If you can follow
this easy pattern for creating snippets, you can use SQL::Snippet. SQL::Snippet
itself automatically creates the back end stuff needed to allow the use of
intuitive OO syntax.
HOW I USE IT
I use SQL::Snippet in a production system with seven basic populations that my
clients like to report on. I then have 31 different limits. Multiple limits
can, of course, be combined with a population. Not every limit is compatible
with all base populations, but most are, leaving the client with more possible
questions they can ask of the RDBMS than I have statistical chops to calculate.
:-)
To make it all work I have 47 different parameters. Some limits only require
one parameter (i.e. the gender limit only needs one parameter -- gender), but
some require more than one. Also, many lim snippets need access to extra parms
in order to correctly tie into the population. For example, if the gender
information in the database is stored in table not included in the chosen pop
snippet, the gender lim snippet will also need something like 'base_table' and
'linking_field' parms to correctly link itself in to the base population.
Most of the populations are actually pre-limited (i.e. the populations don't
refer to so basic an object as 'people', but rather to different kinds of
people). Thus most require more than one parameter and tend to have multiple
tables included in their definition.
My clients love their ability to ask anything they can think of from the RDBMS;
I love the ability to maintain the SQL snippets in one and only one place.
EXAMPLE SCRIPT 1
# specify the repository to be used. The repository is
# subclassed from SQL::Snippet.
use SQL::Snippet::ExampleRepository;
# If you don't specify the parm values needed to fill out
# your pops and lims, the user will be prompted automatically.
# I use Term::Interact to enable this in interaction in a
# command line environment.
use Term::Interact;
# We'll use DBI to execute the SQL we get from SQL::Snippet.
# Also, SQL::Snippet may use DBI to validate user input (if
# you so specify in the repository) and to quote parameters.
use DBI;
my $ti = Term::Interact->new;
my $dbh = DBI->connect( ... );
my $snippet = SQL::Snippet::ExampleRepository->new(
dbh => $dbh,
ui => $ti,
syntax => 'oracle'
);
###EXAMPLE 1###
# We need to specify what our SELECT statement will be.
# A future version will allow prompting of the user to
# build his own SELECT based on meta-data supplied by
# the snippets.
$snippet->pop->catalog_recipient->select( 'SELECT count(*)' );
# get the SQL necessary to pull a count of the catalog
# recipient population out of your RDBMS.
print $snippet->pop->catalog_recipient->query;
###EXAMPLE 2###
# instead of grabbing the whole population of catalog
# recipients, let's limit it to those who never placed
# an order.
$snippet->pop->catalog_recipient->lim->last_order->parm->last_order(
'null'
);
# our SELECT is still set for this pop from above...
print $snippet->pop->catalog_recipient->query;
###EXAMPLE 3###
# instead of forcing a last_order limit, let's let the user
# say whether or not he wants the limit, and if so what sort
# of limit. Possible inputs by the user might be:
# '> 1/1/2001' # with orders after 1/1/2001
# -or-
# 'null' # with no orders ever
# -or-
# '' # let's not limit, thank you
# set the limit for this pop
$snippet->pop->catalog_recipient->lim->new( 'last_order' );
# when generating the SQL, SQL::Snippet will notice we have
# requested a 'last_order' limit, but no parm value for
# 'last_order' has been set. The user will be prompted with
# whatever verbiage we have stored in the repository, and
# their input will be parsed by whatever logic we have in the
# repository. (See perldoc Term::Interact for the details
# of user prompting, and the source for SQL::Snippet::
# ExampleRepository for boilerplate and example Repository
# logic.
print $snippet->pop->catalog_recipient->query;
EXAMPLE SCRIPT 2
# this example script uses some more advanced functionality.
# see perldoc SQL::Snippet for full documentation.
use SQL::Snippet::ExampleRepository;
use Term::Interact;
use DBI;
my $ti = Term::Interact->new;
my $dbh = DBI->connect( ... );
my $snippet = SQL::Snippet::ExampleRepository->new(
dbh => $dbh,
ui => $ti,
syntax => 'oracle'
);
# all pops referenced from the current snippet will
# automatically have the zip limit applied.
$snippet->shared_lim->new( 'zip' );
# Since all pops will share the zip lim, let's set
# the zip parm value *once* the top level (instead
# of once for each pop at the pop->lim->parm level).
# Here, the value will be set via user interaction
# since we have passed in no value and none has
# been previously set.
$snippet->parm->zip->value;
$snippet->pop->pre_sale->select( 'SELECT SKU, count(SKU)' );
$snippet->pop->pre_sale->group_by( 'SKU' );
$snippet->pop->pre_sale->order_by( 'count(SKU)' );
$snippet->pop->sale->select( 'SELECT SKU, count(SKU)' );
$snippet->pop->sale->group_by( 'SKU' );
$snippet->pop->sale->order_by( 'count(SKU)' );
$snippet->pop->re_sale->select( 'SELECT SKU, count(SKU)' );
$snippet->pop->re_sale->group_by( 'SKU' );
$snippet->pop->re_sale->order_by( 'count(SKU)' );
my $pre_sale_hits_by_SKU = $dbh->selectall_arrayref(
$snippet->pop->pre_sale->query
);
my $sales_by_SKU = $dbh->selectall_arrayref(
$snippet->pop->sale->query
);
my $re_sales_by_SKU = $dbh->selectall_arrayref(
$snippet->pop->re_sale->query
);
print " ----SKU---- --COUNT--\n";
print "Pre-Sales Hits\n";
for (@$pre_sale_hits_by_SKU) {
print " $_->[0] $_->[1]\n";
}
print "Sales\n";
for (@$sales_by_SKU) {
print " $_->[0] $_->[1]\n";
}
print " Re-Sales\n";
for (@$re_sales_by_SKU) {
print " $_->[0] $_->[1]\n";
}
# print any notes the repository has associated with
# limits placed on the whole report.
print scalar($snippet->get_shared_lim_notes);
