/ 
SQL-Template-0.2.2
River stage zero No dependents
/ SQL::Template

NAME

SQL::Template - A new way to organize your database code

VERSION

Version 0.2.2

SYNOPSIS

use SQL::Template;

my $sql = SQL::Template->new(-filename=>"my-custom-sqls.xml");
my $dbh =DBI->connect("dbi:SQLite:dbname=example.sqlite","","");

#Simple record insert
$sql->do("insert_country", $dbh, {COUNTRY_ID=>'ES', NAME=>'SPAIN'} );

# fetch records
my $stmt = $sql->select_stmt("query_for_persons", $dbh, {NAME=>'JOHN'} );
while( my $hr = $stmt->fetchrow_hashref ) {
   print $hr->{NAME}, "\n";
}
$stmt->finish;


### file: my-custom-sqls.xml
<?xml version="1.0" encoding="iso-8859-1"?>

<st:sql>
<st:do name="insert_country" >
   INSERT INTO COUNTRY(COUNTRY_ID, NAME)
   VALUES( ${COUNTRY_ID}, ${NAME} )
</st:do>

<st:select name="query_for_persons" >
   SELECT * FROM PERSON 
   <st:if test="${NAME}" prepend="WHERE">
      NAME=${NAME}
   </st:if>
</st:select>	   

</st:sql>

DESCRIPTION

Imagine this situation: you know DBI and you like it, because you can make use of your SQL knowledge. But you are not happy having the SQL code into the Perl code. You can use other CPAN modules, which let us to abstract SQL code. But we want to write SQL code, we feel confortable with it.

This module decouples SQL sentences from Perl code, writting sentences in a XML file, that you can use in different parts of your code. SQL::Template allows dynamic test of expressions, and reuse of fragments.

The SQL handled sentences are SQL-inyection free; SQL::Template make use of parameter binding.

XML file

The XML file contains the SQL sentences that you will use with SQL::Template. This is more than a dictionary container, it allows us to build dinamyc SQL and reuse fragments.

General

The different parts are enclosed between <st:sql> and </st:sql>

<?xml version="1.0" encoding="iso-8859-1"?>
<st:sql>

<!-- file contents -->

</st:sql>

st:do

This command is used to make DDL sentences or INSERT, UPDATE and DELETE. For example:

<st:do name="update_named_1" >
   UPDATE AUTHOR SET NAME=${NAME}, FIRST_NAME=${FIRSTNAME, SQL_VARCHAR} 
   WHERE AUTHOR_ID=${ID}
</st:do>

This simple command shows us important things:

name

The name attribute is mandatory, and it will be used to link the Perl code with the SQL

parameters

Parameters tou pass with a HASH reference to SQL::Template are binding to the SQL. In the previous example, ${NAME} and ${FIRSTNAME, SQL_VARCHAR}. The fisrt is the simple use, where the parameter will be replaced (using DBI bind). The second one will be used if you need to indicate the data type.

st:select

If we need to make SELECT sentences, the command st:select will be used. This is a simple example:

<st:select name="query_for_author" >
   SELECT * FROM AUTHOR WHERE AUTHOR_ID=${ID}
</st:select>

Like the previous one, you can bind parameters with the ${variable} syntaxt

st:fragment

When we are writting SQL sentences, there are many of them similar, changing specific parts. I think that you can reuse SQL fragments in order to reduce the code you write, and to make the maintenance easier.

define a fragment
   <st:fragment name="filter_authors_with_A">
      AND NAME LIKE 'A%'
   </st:fragment>
use it
<st:select name="query_for_authors_with_A" >
   SELECT * FROM AUTHOR WHERE AUTHOR_ID=${ID}
   <st:include name="filter_authors_with_A"/>
</st:select>

Dynamic sentences

SQL::Template dynamic feature is simple and strong. It allow us to write comple SQL sentences that can be different depending on parameters values. For example:

   <st:select name="query_named_1" >
      SELECT * FROM AUTHOR
      WHERE YEAR=${YEAR}
      <st:if test="${GENDER} eq 'F'" prepend="AND">
			CITY != ${CITY}
      </st:if>
      <st:else>
         AGE > 18
      </st:else>		
   </st:select>

As you can see, <st:if> command is used to build dynamic SQL. The "if" command can be used in <st:do> and <st:fragment>. It's composed by:

test

Any valid Perl expression, where you can bind the parameters. SQL::Templante will eval this expression in order to calculate the result. Boolean "true" or "false" rules are the same that Perl uses in boolean expressions

prepend

If the test expression returns "true", prepend this text to the SQL block enclosed by "st:if". It isn't mandatory.

<st:else>

The common "else" section in any "if" block. It isn't mandatory, and it will be used if the test expression returns false.

METHODS

SQL::Template methods are written in a way that it's similar to DBI interface, so I hope you will be confortable with them.

new ( option=>value )

The new() function takes a list of options and values, and returns a new SQL::Template object which can then be used to use SQL sentences. The accepted options are (one of them is mandatory):

-filename

This determines the XML file which contains the SQL sentences. The object creation phase involves parsing the file, so any error (like syntax) cause an exception throw. If everything is fine, all commands searched are cached in order to improve the performance

-string

If you prefer to build a string with XML-syntax, you can build a SQL::Template object in that way.

select_stmt ( $name, $dbh, $params, $attrs )

This method search in the command cache, and if it's found, SQL::Template try to apply the params and execute in provided database handle. These are the arguments:

$name

The name of SQL sentence to use. This must match with a sentence in the XML file.

$dbh

The database handle to be used. Note tat SQL::Template doesn't establish a connection with your DB, it only use the one you want.

$params

When the SQL sentence needs parameters, you must provide them with a hash reference variable.

$attrs

Any aditional attribute you need to pass to the database driver, it will be used in the DBI commands. Typically, you don't use this param.

This methods use the following DBI functions: prepare, bind_param, execute. It returns a DBI::st handle, you can fetch in the habitual way. For example:

my $stmt = $sql->select_stmt("query_for_persons", $dbh, {NAME=>'JOHN'} );
while( my @row = $stmt->fetchrow_array ) {
   print "@row\n";
}
$stmt->finish;

selectrow_array ( $name, $dbh, $params, $attrs )

This method interface is similar to the previous you have seen in section "select_stmt". In this case, SQL::Template makes a call to DBI fetchrow_array function and finish the statement handle, returning an array with the results

selectrow_arrayref ( $name, $dbh, $params, $attrs )

This method interface is similar to the previous you have seen in section "selectrow_array". In this case, SQL::Template makes a call to DBI fetchrow_arrayref function and finish the statement handle, returning an array reference with the results

selectrow_hashref ( $name, $dbh, $params, $attrs )

This method interface is similar to the previous you have seen in section "selectrow_array". In this case, SQL::Template makes a call to DBI fetchrow_hashref function and finish the statement handle, returning a hash reference with the results

selectall_arrayref

This method combines "prepare", "execute" and "fetchall_arrayref" into a single call. It returns a reference to an array containing a reference to an array (or hash, see below) for each row of data fetched. This method interface is similar to the previous you have seen in section "selectrow_array".

See DBI selectall_hashref method for more details.

selectall_hashref

This method combines "prepare", "execute" and "fetchall_arrayref" into a single call. It returns a reference to an array containing a reference to an hash for each row of data fetched. This method interface is similar to the previous you have seen in section "selectrow_array".

See DBI selectall_hashref method for more details.

do ( $name, $dbh, $params, $attrs )

This method interface is similar to the previous you have seen in section "select_stmt". The main use of this function is to execute DDL commands and INSERT, UPDATE or DELETE commands. In this case, SQL::Template makes a call to DBI execute function and returns its results to the caller.

AUTHOR

prz, <niceperl at gmail.com>

BUGS

Please report any bugs or feature requests to bug-sql-template at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=SQL-Template. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc SQL::Template

You can also look for information at:

ACKNOWLEDGEMENTS

COPYRIGHT & LICENSE

Copyright 2009 prz.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.