NAME

CAM::SQLManager - Encapsulated SQL statements in XML

LICENSE

Copyright 2005 Clotho Advanced Media, Inc., <cpan@clotho.com>

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

SYNOPSIS

We recommend that you do NOT use CAM::SQLManager directly, but instead use the more convenient wrapper, CAM::SQLObject. In that case, you can skip to the bottom to learn about the XML files. If you do choose to use this directly, here's how it goes:

use CAM::SQLManager;
use DBI;

my $dbh = DBI->connect(blah blah);

CAM::SQLManager->setDBH($dbh);
CAM::SQLManager->setDirectory("/var/www/sqlcmds");

my $sql1 = CAM::SQLManager->new("user.xml");
my $sth = $sql1->query("search", username => "chris");

my $dbh2 = DBI->connect(blah blah);
my $sql2 = CAM::SQLManager->new(-dbh => $dbh2, -cmd => "product.xml", 
                                -dir => "/usr/share/sqlcmds");
my $result = $sql2->do("add", name => "vase", color => "red", price => "50.00");

my $sql3 = CAM::SQLManager->new("product.xml");
my @towels = $sql3->retrieveObjects("search", "ACME::Towel", [], prodtype => "%towel%");

my $sql4 = CAM::SQLManager->new("product.xml");
my $towel = ACME::Towel->new();
  [... fill/edit object ...]
$sql4->storeObject("insert", $towel);

Use these commands for testing the various SQL queries in a CGI script:

CAM::SQLManager->setDirectory("/var/www/sqlcmds");
CAM::SQLManager->setDBH($dbh);
CAM::SQLManager->setBenchmarking(1);  # optional
CAM::SQLManager->testCommands();

DESCRIPTION

This package implements SQL templates. This allows the programmer to increase the separation between the SQL RDBMS and the Perl programming logic of any project. This package has features that make it particularly useful in a web environment, as it is quite easy to write a CGI program to allow testing and evalutation of the SQL templates.

PORTING

As of v1.12, we have added support for non-Unix file systems via File::Spec. This is intended to enable Win32 usage of this module. As of v1.13, this is pretty well tested in production by the authors, so we think it should work fine for you.

FUNCTIONS

new [CMD,] [ARG => VALUE, ...]

Open and read an SQL template. Possible arguments (with example values) are:

-cmd => "user.xml"
-dir => "/some/sql/template/dir"
-dbh => $dbh   (should be a DBI object)

if -dir or -dbh are not specified, the global values are used (set by setDirectory and setDBH below).

The file <dir>/<cmd>.xml should exist.

getMgr CMD, CMD, ...

getMgr -dbh => DBH, CMD, CMD, ...

Like new() above, but caches the manager objects for later re-requests. Unlike new(), the database handle and SQL file directory must already be set. Use this function like:

CAM::SQLManager->getMgr("foo.xml");

If more than one command is specified, the first one that results in a real file is used.

getAllCmds

Search the SQL directory for all command files. This is mostly just useful for the testCommands() method.

setDirectory DIRECTORY

Set the global directory for this package. Use like this:

CAM::SQLManager->setDirectory("/var/lib/sql");

setDBH DBI-OBJECT

As a class method, this sets the global database handle for this package. Use like this:

CAM::SQLManager->setDBH($dbh);

As an object method, this sets the database handle for just that instance.

getDBH

Get the current database handle. If a handle is not specifically set for an instance, the global database handle is returned.

setBenchmarking 0|1

Specify whether to benchmark the SQL queries. The default is 0 (false). To retrieve the benchmarking data, use the statistics() method. Use like this:

CAM::SQLManager->setBenchmarking(1);

validateXML

Warning: this function relies on XML::Xerces. If XML::Xerces is not installed, this routine will always indicate that the document is invalid.

Test the integrity of the XML encapsulation of the SQL statement(s). Returns true of false to indicate success or failure. On failure, it sets $CAM::SQLManager::errstr with an error description. Succeeds automatically on a non-XML SQL file.

tableName

Returns the name of the SQL table, as specified in the XML file. If the XML file does not specify a table name, this returns the empty string.

keyName

Returns the name of the primary key SQL table, as specified in the XML file. If the XML file does not specify a key name, this returns the empty string.

query QUERYNAME [VAR => VALUE, ...]

Run a SELECT style query called <queryname>, substituting the parameter list into the SQL template. Returns an executed DBI statement handle object, or undef on failure.

if <queryname> is undefined or the empty string, the default query will be used. The default is either a query with no name, if one exists, or the first query in the query definition file. If a nonexistent query is requested, undef is returned.

do QUERYNAME [VAR => VALUE, ...]

Run a INSERT/UPDATE/DELETE style query, substituting the parameter list into the SQL template. Returns a scalar indicating the result of the statement (false for failure, number of rows affected on success).

QUERYNAME behaves as described in query().

getLastInsertID

After an insert statement into a table with an autoincremented primary key, this command returns the ID number that was auto-generated for the new row.

Warning: This is specific to MySQL. I do not believe this function will work on other database platforms.

storeObject QUERYNAME, OBJECT

Save an object to backend storage, using the specified query. The object methods indicated in <bind> accessors will be called to fill in the SQL statement.

QUERYNAME behaves as described in query().

fillObject QUERYNAME, OBJECT

Run the specified query and fill in the object with the returned fields. The object should already exist and should have enough fields filled in to make the query return a unique object. If any command in the query returns zero or more than one rows, this request will fail.

QUERYNAME behaves as described in query().

retrieveObjects QUERYNAME, PACKAGE, NEW_ARGS [ARGUMENTS]

Run the specified query and return an array of objects of class PACKAGE. The objects will be created by calling PACKAGE->new(). Any extra arguments to this function will be passed as arguments to new(). The objects will be filled with the values from the rows returned by the query.

NEW_ARGS is an array reference of arguments passed to the 'new' function of PACKAGE.

QUERYNAME behaves as described in query().

statistics

Return a data structure of statistics for this package. The data structure looks like this:

$stats = {
   queries => <number>,
   time => <seconds>,
   cmds => {
      "sqlone.xml" => {
         queries => <number>,
         time => <seconds>,
         query => {
            "queryone" => {
               queries => <number>,
               time => <seconds>,
            },
            "querytwo" => {
               queries => <number>,
               time => <seconds>,
            },
         }
      },
      "sqltwo.xml" => {
         queries => <number>,
         time => <seconds>,
         query => {
            "queryone" => {
               queries => <number>,
               time => <seconds>,
            },
         }
      },
   },
};

The returned structure is a reference to live data so DO NOT alter it in any way! Treat it as read-only data.

statisticsHTML

This class method returns an HTML string that renders the statistics data in a human readable format.

toForm QUERYNAME

Return the body of an HTML form useful for testing and evaluting the SQL template. Use it something like this:

my $sql = CAM::SQLManager->new("somecommand");
print "<form action="$URL">";
print $sql->toForm();
print "<input type=submit>";
print "</form>";

fromForm [CGI object]

Accept input from an HTML form like the one output by toForm() and return HTML formatted output.

testCommands

testCommands CGIobject

A nearly complete CGI program to run tests on your library SQL commands. You may optionally pass it a CGI object, if you want it to work as part of a larger framework. Otherwise, the function instantiates it's own CGI object. Here is an complete CGI program using this function:

#!/usr/bin/perl
use CAM::SQLManager;
use DBI;
my $dbh = DBI->connect(blah blah);
CAM::SQLManager->setDBH($dbh);
CAM::SQLManager->setDirectory("/path/to/sql/library");
CAM::SQLManager->setBenchmarking(1);  # optional
CAM::SQLManager->testCommands();

XML STRUCTURE

The SQL commands should be encapsulated in one or more XML documents. The structure of this XML is specified in CAM-SQL.dtd. Here is an example XML SQL query:

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<!DOCTYPE sqlxml SYSTEM "CAM-SQL.dtd">
<sqlxml>
  <table name="user" primarykey="username"/>
  <query name="GetUser">
   <sql>
      select
        <retrieve key="firstName" mutator="setNickName"/>
        <retrieve key="lastName" mutator="setLastName"/>
        <retrieve key="date_add(birthdate, interval 65 year)" as="bday65"
                  mutator="setSixtyFifthBirthday"/>
      from user
      where 
        username = <bind key="username" accessor="getUserName"/>
      and
        password = <bind key="password" accessor="promptForPassword"/>
    </sql>
  </query>
  <query name="DeleteUser">
    <result key="result" mutator="setDeleteResult"/>
    <sql>
      delete from user
      where 
        username = <bind key="username" accessor="getUserName"/>
    </sql>
  </query>
  <query name="AddUser">
    <sql>
      insert into user
      set 
        username  = <bind key="username" accessor="getUserName"/>,
        password  = <bind key="password" accessor="getPassword"/>,
        firstName = <bind key="firstName" accessor="getNickName"/>,
        lastName  = <bind key="lastName" accessor="getLastName"/>,
        birthdate = <bind key="birthdate" accessor="getBirthDate"/>,
        city      = <bind key="city" accessor="getCity" default="Madison"/>,
        state     = <bind key="state" accessor="getState" default="WI"/>,
        zip       = <bind key="zip" accessor="getZipCode" default="53711"/>
    </sql>
  </query>
  <query name="GetUserAddresses">
   <sql>
      select
        <retrieve key="city" mutator="setCity"/>
        <retrieve key="state" mutator="setState"/>
        <retrieve key="zip" mutator="setZipCode"/>
      from user
      order by
        <replace key="orderby"/>
    </sql>
  </query>
</sqlxml>

AUTHOR

Clotho Advanced Media Inc., cpan@clotho.com

Primary developer: Chris Dolan

SEE ALSO

CAM::SQLObject