SQL::Template - A new way to organize your database code
Version 0.2.2
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>
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.
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.
The different parts are enclosed between <st:sql> and </st:sql>
<st:sql>
</st:sql>
<?xml version="1.0" encoding="iso-8859-1"?> <st:sql> <!-- file contents --> </st:sql>
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:
The name attribute is mandatory, and it will be used to link the Perl code with the SQL
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.
${NAME}
${FIRSTNAME, SQL_VARCHAR}
If we need to make SELECT sentences, the command st:select will be used. This is a simple example:
st:select
<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
${variable}
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.
<st:fragment name="filter_authors_with_A"> AND NAME LIKE 'A%' </st:fragment>
<st:select name="query_for_authors_with_A" > SELECT * FROM AUTHOR WHERE AUTHOR_ID=${ID} <st:include name="filter_authors_with_A"/> </st:select>
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:
<st:if>
<st:do>
<st:fragment>
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
If the test expression returns "true", prepend this text to the SQL block enclosed by "st:if". It isn't mandatory.
The common "else" section in any "if" block. It isn't mandatory, and it will be used if the test expression returns false.
SQL::Template methods are written in a way that it's similar to DBI interface, so I hope you will be confortable with them.
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):
new()
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
If you prefer to build a string with XML-syntax, you can build a SQL::Template object in that way.
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:
The name of SQL sentence to use. This must match with a sentence in the XML file.
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.
When the SQL sentence needs parameters, you must provide them with a hash reference variable.
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;
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
fetchrow_array
finish
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
fetchrow_arrayref
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
fetchrow_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 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".
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.
execute
prz, <niceperl at gmail.com>
<niceperl at gmail.com>
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.
bug-sql-template at rt.cpan.org
You can find documentation for this module with the perldoc command.
perldoc SQL::Template
You can also look for information at:
RT: CPAN's request tracker
http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Template
AnnoCPAN: Annotated CPAN documentation
http://annocpan.org/dist/SQL-Template
CPAN Ratings
http://cpanratings.perl.org/d/SQL-Template
Search CPAN
http://search.cpan.org/dist/SQL-Map/
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.
To install SQL::Template, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Template
CPAN shell
perl -MCPAN -e shell install SQL::Template
For more information on module installation, please visit the detailed CPAN module installation guide.