NAME

SQL::Snippet - Conatraint-based OO Interface to RDBMS

SYNOPSIS

    use SQL::Snippet::ExampleRepository;

    my $snippet = SQL::Snippet::ExampleRepository->new( @args );

    # auto-instantiate the population and limit it
    $snippet->pop->pop_name->lim->new( 'lim_name' );

    # assign select clause
    $snippet->pop_name->select( $select_clause );

    # get sql statement suitable for handing off to DBI
    my $sql = $snippet->pop->pop_name->query;

DESCRIPTION

SQL::Snippet has two major benefits:

1) Ease of system maintenence: all SQL is removed from individual perl scripts and boiled down into unique elements. These elements are named after the real-world objects they represent and are stored in one central repository/module. When SQL adjustments or additions are needed, all changes are made in this one central repository, instead of within many individual scripts.

2) Ease of data access: In response to requests made in easy OO syntax, SQL::Snippet combines snippets from the repository on the fly to create canonical SQL. Thus programmers need not be concerned about tables and joins and other RDBMS complexities when writing a scriptrequiring database interaction. Further, creating ad-hoc drill-down reports for end users becomes a simple exercise.

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       =>
          "By 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',
      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.

EXAMPLES

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( ... );
  $ti->dbh( $dbh );
  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);

METHODS

Snippet Methods

  # $snippet->method;

new

Snippet object constructor. Accepts parameters via passed in key value pairs. Ex:

  my $snippet = SQL::Snippet::ExampleRepository->new(
    dbh => $dbh,
    ui  => $ui,
  );

Parameters defaults at construction: interact => 1 sql_syntax => 'Oracle'

interact

Boolean parameter accessor/mutator (boolean):

  # set
  $snippet->interact( 0 );

  #get
  my $interact = $snippet->interact;

dbh

Database handle parameter accessor/mutator:

  # set
  $snippet->dbh( $dbh );

  #get
  my $dbh = $snippet->dbh;

sql_syntax

String parameter accessor/mutator:

  # set
  $snippet->sql_syntax( 'oracle' );

  #get
  my $sql_syntax = $snippet->sql_syntax;

ui

User interface object parameter accessor/mutator:

  # set
  $snippet->ui( $ui );

  #get
  my $ui = $snippet->ui;

get_shared_lim_notes

Accessor to retrieve the note parameters from any shared limits.

  # get notes in array form
  my @notes = $snippet->get_shared_lim_notes;

  # get notes pre-joined with "\n"
  my $notes = $snippet->get_shared_lim_notes;

Pop Methods

  # $snippet->pop->method;

new

Constructs a new pop snippet. Requires name of the pop snippet to be passed in. Also accepts key=>value parameters (see pop snippet accessor/mutator methods for a list) that will override any defaults from the repository.

  $snippet->pop->new(
    $pop_name,
    select => 'SELECT foo',
  );

list

Returns a list of those pop objects currently instantiated.

  my @pops = $snippet->pop->list;

remove

Eliminates the specified pop object.

  $snippet->pop->remove( 'foo' );

Pop Snippet Methods

  # $snippet->pop->pop_name->method;

create_select

[UNIMPLEMENTED] Will prompt the user to construct a select statement using pop meta-information.

select

string parameter accessor/mutator:

  # set
  $snippet->pop->foo->select( $sql );

  #get
  my $sql = $snippet->pop->foo->select;

selectable

aref parameter accessor/mutator:

The 'selectable' parm value is a aref with meta-information about the snippet: what fields are selectable (and what are their aliases) because of the inclusion of this snippet.

  my @selectable = (
    'field_1',
    [ 'field_2' => 'filed_2_alias' ],
  )

  # set
  $snippet->pop->foo->selectable( \@selectable );

  #get
  my $selectable_aref = $snippet->pop->foo->selectable;

prompt_parm

aref parameter accessor/mutator:

The 'prompt_parm' parm value is an aref with meta-information about the snippet: parms that will be required because of the inclusion of this snippet.

  my @prompt_parm = (
    'bar_parm',
    'foo_parm' => {   # override default parm attributes
                      prompt => 'Custom Prompt: ',
                  },
  )

  # set
  $snippet->pop->foo->prompt_parm( \@prompt_parm );

  #get
  my $prompt_parm_aref = $snippet->pop->foo->prompt_parm;

table

aref parameter accessor/mutator:

  # set
  $snippet->pop->foo->table( [ 'bar.foo', 'bar.baz' ] );

  #get
  my $table = $snippet->pop->foo->table;

sql

aref parameter accessor/mutator:

  # set
  $snippet->pop->foo->sql(
    [
        "and foo.id = baz.id",
        "and foo.thing in ($string_of_quoted_n_delimited_things)",
    ],

  #get
  my $sql = $snippet->pop->foo->sql;

group_by

[UNIMPLEMENTED] aref parameter accessor/mutator:

  # set
  $snippet->pop->foo->group_by( [ 'foo.id', 'baz.id' ] );

  #get
  my $group_by = $snippet->pop->foo->group_by;

order_by

[UNIMPLEMENTED] aref parameter accessor/mutator:

  # set
  $snippet->pop->foo->order_by( [ 'foo.id', 'baz.id' ] );

  #get
  my $group_by = $snippet->pop->foo->order_by;

having

[UNIMPLEMENTED] aref parameter accessor/mutator:

  # set
  $snippet->pop->foo->having(
    [
        "and foo.id = baz.id",
        "and foo.thing in ($string_of_quoted_n_delimited_things)",
    ],

  #get
  my $having = $snippet->pop->foo->having;

desc

[UNIMPLEMENTED] string parameter accessor/mutator. The 'desc' parm value is a string with meta-information about the snippet

  # set
  $snippet->pop->foo->desc( $str );

  #get
  my $desc = $snippet->pop->foo->desc;

query

Returns the SQL for a pop and all of its associated lims (including shared_lims) and parms.

  my $sql = $snippet->pop->foo->query;

Lim Methods

  # $snippet->shared_lim->method;
  # $snippet->pop->pop_name->lim->method;

new

Constructs a new lim snippet, either attached to a particular pop snippet or as a shared lim at the snippet level. (These are identical in structure except that shared lims do not accaept any parm information.) Requires name of the lim snippet to be passed in. Also accepts key=>value parameters (see lim snippet accessor/mutator methods for a list) that will override any defaults from the repository.

  $snippet->pop->pop_name->lim->new(
    $lim_name,
  );

list

Returns a list of those lim objects currently instantiated.

  my @pops = $snippet->shared_lim->list;

remove

Eliminates the specified pop object.

  $snippet->pop->foo_pop->remove( 'bar_lim' );

Lim Snippet Methods

  # $snippet->pop->pop_name->lim->lim_name->method;
  # $snippet->shared_lim->lim_name->method;

selectable

aref parameter accessor/mutator:

The 'selectable' parm value is a aref with meta-information about the snippet: what fields are selectable (and what are their aliases) because of the inclusion of this snippet.

  my @selectable = (
    'field_1',
    [ 'field_2' => 'filed_2_alias' ],
  )

  # set
  $snippet->pop->pop_name->lim->lim_name->selectable(
    \@selectable
  );

  #get
  my $selectable_aref = $snippet->pop->pop_name
                          ->lim->lim_name->selectable;

prompt_parm

aref parameter accessor/mutator:

The 'prompt_parm' parm value is an aref with meta-information about the snippet: parms that will be required because of the inclusion of this snippet.

  my @prompt_parm = (
    'bar_parm',
    'foo_parm' => {   # override default parm attributes
                      prompt => 'Custom Prompt: ',
                  },
  )


  # set
  $snippet->pop->pop_name
    ->lim->lim_name->prompt_parm( \@prompt_parm );

  # get
  my $prompt_parm_aref = $snippet->pop->pop_name
                           ->lim->lim_name->prompt_parm;

table

aref parameter accessor/mutator:

  # set
  $snippet->shared_lim->lim_name->table(
    [ 'bar.foo', 'bar.baz' ]
  );

  #get will return either an aref or a scalar, depending
  # on which was stored as the value for table
  my $table = $snippet->shared_lim->lim_name->table;

sql

aref parameter accessor/mutator:

  # set
  $snippet->pop->pop_name->lim->lim_name->sql(
    [
        "and foo.id = baz.id",
        "and foo.thing in ($string_of_quoted_n_delimited_things)",
    ],

  # get
  my $sql = $snippet->pop->pop_name->lim->lim_name->sql;

desc

[UNIMPLEMENTED] string parameter accessor/mutator. The 'desc' parm value is a string with meta-information about the snippet

  # set
  $snippet->shared_lim->lim_name->desc( $str );

  #get
  my $desc = $snippet->shared_lim->lim_name->desc;

note

String parameter accessor/mutator. The 'note' is useful for explaining the inclusion of a limit. For example, the snippet level get_shared_lim method will return all the notes of those lims instantiated as shered_lim s.

  # set
  $snippet->pop->pop_name->lim_lim_name->note( $str );

  #get
  my $note = $snippet->pop_name->lim_lim_name->note;

Parm Methods

  # $snippet->parm->method;
  # $snippet->pop->pop_name->parm->method;
  # $snippet->pop->pop_name->lim->lim_name->parm->method;

new

Constructs a new parm snippet. Requires name of the parm snippet to be passed in. Also accepts key=>value parameters (see parm snippet accessor/mutator methods for a list) that will override any defaults from the repository. Note that when the query method is called on a population, any parameter values required by that population and it's limits that are not defined at the pop or pop->lim level will be sought at the snippet level. SHould they not be found there the user will be prompted and a snippet level parm object will be instantiated.

  $snippet->parm->new(
    $lim_name,
  );

list

Returns a list of those parm objects currently instantiated.

  my @pops = $snippet->pop->pop_name->list;

remove

Eliminates the specified pop object.

  $snippet->pop->pop_name->lim->lim_name->parm->remove( 'bar_lim' );

Parm Snippet Methods

  # $snippet->parm->parm_name->method;
  # $snippet->pop->pop_name->parm->parm_name->method;
  # $snippet->pop->pop_name->lim->lim_name->parm->parm_name->method;

Note that all the parameters from Term::Interact (i.e., name, type, allow_null, check) are available as parameters for your parm snippets. I won't recount them here; see perldoc Term::Interact for the details. Other parameters available for your parm snippet:

value

string or aref accessor/mutator method. Operates on the current value of the parm snippet.

  # set
  $snippet->parm->parm_name->value( 'foo' );

  #get
  my $value = $snippet->parm->parm_name->value;

label

[UNIMPLEMENTED] string accessor/mutator method. Operates on the meta-value 'label' parameter. I see this as useful when auto-generating an HTML user interface.

desc

[UNIMPLEMENTED] string accessor/mutator method. Operates on the meta-value 'desc' parameter. I see this as useful when auto-generating an HTML user interface.

AUTHOR

SQL::Snippet by Phil R Lawrence.

SUPPORT

Support is available by emailing the author directly: prl ~AT~ cpan ~DOT~ org

COPYRIGHT

The SQL::Snippet module is Copyright (c) 2002 Phil R Lawrence. All rights reserved.

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

NOTE

This module was developed while I was in the employ of Lehigh University. They kindly allowed me to have ownership of the work with the understanding that I would release it to open source. :-)

SEE ALSO

DBI, Term::Interact

cpanm SQL::Snippet::Parm

perl -MCPAN -e shell
install SQL::Snippet::Parm