File Coverage

blib/lib/SQL/Template.pm
Criterion Covered Total %
statement 13 15 86.6
branch n/a
condition n/a
subroutine 5 5 100.0
pod n/a
total 18 20 90.0


line stmt bran cond sub pod time code
1             package SQL::Template;
2              
3 2     2   35370 use warnings;
  2         3  
  2         66  
4 2     2   11 use strict;
  2         3  
  2         68  
5              
6 2     2   10 use Carp;
  2         8  
  2         153  
7 2     2   10505 use DBI qw(:sql_types);
  2         40985  
  2         1056  
8 2     2   1406 use SQL::Template::XMLBuilder;
  0            
  0            
9              
10             =head1 NAME
11              
12             SQL::Template - A new way to organize your database code
13              
14             =head1 VERSION
15              
16             Version 0.2.2
17              
18             =cut
19              
20             our $VERSION = '0.2.2';
21              
22             =head1 SYNOPSIS
23              
24             use SQL::Template;
25            
26             my $sql = SQL::Template->new(-filename=>"my-custom-sqls.xml");
27             my $dbh =DBI->connect("dbi:SQLite:dbname=example.sqlite","","");
28            
29             #Simple record insert
30             $sql->do("insert_country", $dbh, {COUNTRY_ID=>'ES', NAME=>'SPAIN'} );
31              
32             # fetch records
33             my $stmt = $sql->select_stmt("query_for_persons", $dbh, {NAME=>'JOHN'} );
34             while( my $hr = $stmt->fetchrow_hashref ) {
35             print $hr->{NAME}, "\n";
36             }
37             $stmt->finish;
38            
39            
40             ### file: my-custom-sqls.xml
41            
42            
43            
44            
45             INSERT INTO COUNTRY(COUNTRY_ID, NAME)
46             VALUES( ${COUNTRY_ID}, ${NAME} )
47            
48            
49            
50             SELECT * FROM PERSON
51            
52             NAME=${NAME}
53            
54            
55            
56            
57            
58             =cut
59              
60             =head1 DESCRIPTION
61              
62             Imagine this situation: you know DBI and you like it, because you can make use of
63             your SQL knowledge. But you are not happy having the SQL code into the Perl code.
64             You can use other CPAN modules, which let us to abstract SQL code. But we want to
65             write SQL code, we feel confortable with it.
66              
67             This module decouples SQL sentences from Perl code, writting sentences in a XML file,
68             that you can use in different parts of your code. SQL::Template allows dynamic test of
69             expressions, and reuse of fragments.
70              
71             The SQL handled sentences are SQL-inyection free; SQL::Template make use of parameter
72             binding.
73              
74             =head1 XML file
75              
76             The XML file contains the SQL sentences that you will use with SQL::Template. This is more
77             than a dictionary container, it allows us to build dinamyc SQL and reuse fragments.
78              
79             =head2 General
80              
81             The different parts are enclosed between C<< >> and C<< >>
82              
83            
84            
85              
86            
87              
88            
89              
90             =head2 st:do
91              
92             This command is used to make DDL sentences or INSERT, UPDATE and DELETE. For example:
93              
94            
95             UPDATE AUTHOR SET NAME=${NAME}, FIRST_NAME=${FIRSTNAME, SQL_VARCHAR}
96             WHERE AUTHOR_ID=${ID}
97            
98              
99             This simple command shows us important things:
100              
101             =over
102              
103             =item name
104              
105             The name attribute is mandatory, and it will be used to link the Perl code with the SQL
106              
107             =item parameters
108              
109             Parameters tou pass with a HASH reference to SQL::Template are binding to the SQL. In the
110             previous example, C<${NAME}> and C<${FIRSTNAME, SQL_VARCHAR}>. The fisrt is the simple use,
111             where the parameter will be replaced (using DBI bind). The second one will be used if you
112             need to indicate the data type.
113              
114             =back
115              
116              
117             =head2 st:select
118              
119             If we need to make SELECT sentences, the command C will be used. This is a simple
120             example:
121              
122            
123             SELECT * FROM AUTHOR WHERE AUTHOR_ID=${ID}
124            
125              
126             Like the previous one, you can bind parameters with the C<${variable}> syntaxt
127              
128              
129             =head2 st:fragment
130              
131             When we are writting SQL sentences, there are many of them similar, changing specific parts.
132             I think that you can reuse SQL fragments in order to reduce the code you write, and to make
133             the maintenance easier.
134              
135             =over 2
136              
137             =item define a fragment
138              
139            
140             AND NAME LIKE 'A%'
141            
142            
143             =item use it
144              
145            
146             SELECT * FROM AUTHOR WHERE AUTHOR_ID=${ID}
147            
148            
149              
150             =back
151              
152             =head2 Dynamic sentences
153              
154             SQL::Template dynamic feature is simple and strong. It allow us to write comple SQL
155             sentences that can be different depending on parameters values. For example:
156              
157            
158             SELECT * FROM AUTHOR
159             WHERE YEAR=${YEAR}
160            
161             CITY != ${CITY}
162            
163            
164             AGE > 18
165            
166            
167              
168             As you can see, C<< >> command is used to build dynamic SQL. The "if" command
169             can be used in C<< >> and C<< >>. It's composed by:
170              
171             =over 2
172              
173             =item test
174              
175             Any valid Perl expression, where you can bind the parameters. SQL::Templante will eval
176             this expression in order to calculate the result. Boolean "true" or "false" rules are the
177             same that Perl uses in boolean expressions
178              
179             =item prepend
180              
181             If the test expression returns "true", prepend this text to the SQL block enclosed by "st:if".
182             It isn't mandatory.
183              
184             =item
185              
186             The common "else" section in any "if" block. It isn't mandatory, and it will be used if
187             the test expression returns false.
188              
189             =back
190              
191             =head1 METHODS
192              
193             SQL::Template methods are written in a way that it's similar to DBI interface, so I hope
194             you will be confortable with them.
195              
196              
197             =head2 new ( option=>value )
198              
199             The C function takes a list of options and values, and returns
200             a new B object which can then be used to use SQL sentences.
201             The accepted options are (one of them is mandatory):
202              
203             =over
204              
205             =item -filename
206              
207             This determines the XML file which contains the SQL sentences. The object
208             creation phase involves parsing the file, so any error (like syntax) cause
209             an exception throw. If everything is fine, all commands searched are cached
210             in order to improve the performance
211              
212             =item -string
213              
214             If you prefer to build a string with XML-syntax, you can build a SQL::Template
215             object in that way.
216              
217             =back
218              
219             =cut
220              
221             #******************************************************************************
222              
223             sub new {
224             my ($class, %param) = @_;
225             my $builder = SQL::Template::XMLBuilder->new;
226            
227             if( $param{-filename} ) {
228             croak "XML config file not found [$param{-filename}]" unless(-e $param{-filename});
229             $builder->parse_file( $param{-filename} );
230             }
231             elsif( $param{-string} ) {
232             $builder->parse_string( $param{-string} );
233             }
234             else {
235             croak "XML config file not specified";
236             }
237            
238             my $self = {
239             COMMANDS => $builder->get_commands
240             };
241             return bless $self, $class;
242             }
243              
244             #******************************************************************************
245              
246             sub _prepare_and_bind {
247             my ($self, $name, $dbh, $params, $attrs) = @_;
248             my $command = $self->{COMMANDS}->{lc($name)};
249             croak "Command not found: $name" if(!$command);
250             my $sql = $command->sql($params);
251             my $bindings = $command->bindings($params);
252            
253             my @matches = $sql =~ m!(\$\{\s*\w+\s*\})!gx;
254             $sql =~ s!\$\{\s*\w+\s*\}!?!gx;
255            
256             my $stmt;
257             eval {
258             $stmt = $dbh->prepare($sql);
259             };
260             croak "${@}with SQL: $sql" if($@);
261            
262             if( $bindings ) {
263             my $pcount = 1;
264             foreach my $key( @matches ) {
265             if( ! exists($bindings->{$key}) ) {
266             croak "parameter not found: $key";
267             }
268             elsif( $bindings->{$key} and ('ARRAY' eq ref($bindings->{$key}) ) ) {
269             $stmt->bind_param($pcount++, $bindings->{$key}->[0], eval($bindings->{$key}->[1]) );
270             }
271             else {
272             #print "BIND: $key => ", $bindings->{$key}, "\n";
273             $stmt->bind_param($pcount++, $bindings->{$key});
274             }
275             }
276             }
277             return $stmt;
278             }
279              
280             #******************************************************************************
281              
282             =head2 select_stmt ( $name, $dbh, $params, $attrs )
283              
284             This method search in the command cache, and if it's found, SQL::Template
285             try to apply the params and execute in provided database handle. These are
286             the arguments:
287              
288             =over
289              
290             =item $name
291              
292             The name of SQL sentence to use. This must match with a sentence in the
293             XML file.
294              
295             =item $dbh
296              
297             The database handle to be used. Note tat SQL::Template doesn't establish a
298             connection with your DB, it only use the one you want.
299              
300             =item $params
301              
302             When the SQL sentence needs parameters, you must provide them with a hash
303             reference variable.
304              
305             =item $attrs
306              
307             Any aditional attribute you need to pass to the database driver, it will be used
308             in the DBI commands. Typically, you don't use this param.
309              
310             =back
311              
312             This methods use the following DBI functions: prepare, bind_param, execute. It
313             returns a DBI::st handle, you can fetch in the habitual way. For example:
314              
315             my $stmt = $sql->select_stmt("query_for_persons", $dbh, {NAME=>'JOHN'} );
316             while( my @row = $stmt->fetchrow_array ) {
317             print "@row\n";
318             }
319             $stmt->finish;
320              
321             =cut
322              
323             sub select_stmt {
324             my ($self, $name, $dbh, $params, $attrs) = @_;
325             my $stmt = $self->_prepare_and_bind($name, $dbh, $params, $attrs);
326             $stmt->execute;
327             return $stmt;
328             }
329              
330             =head2 selectrow_array ( $name, $dbh, $params, $attrs )
331              
332             This method interface is similar to the previous you have seen
333             in section L.
334             In this case, SQL::Template makes a call to DBI C
335             function and C the statement handle, returning an array
336             with the results
337              
338             =cut
339              
340             sub selectrow_array {
341             my ($self, $name, $dbh, $params, $attrs) = @_;
342             my $stmt = $self->select_stmt($name, $dbh, $params, $attrs);
343             my @row = $stmt->fetchrow_array;
344             $stmt->finish;
345             return @row;
346             }
347              
348             =head2 selectrow_arrayref ( $name, $dbh, $params, $attrs )
349              
350             This method interface is similar to the previous you have seen
351             in section L.
352             In this case, SQL::Template makes a call to DBI C
353             function and C the statement handle, returning an array reference
354             with the results
355              
356             =cut
357              
358             sub selectrow_arrayref {
359             my ($self, $name, $dbh, $params, $attrs) = @_;
360             my $stmt = $self->select_stmt($name, $dbh, $params, $attrs);
361             my $row = $stmt->fetchrow_arrayref;
362             $stmt->finish;
363             return $row;
364             }
365              
366             =head2 selectrow_hashref ( $name, $dbh, $params, $attrs )
367              
368             This method interface is similar to the previous you have seen
369             in section L.
370             In this case, SQL::Template makes a call to DBI C
371             function and C the statement handle, returning a hash reference
372             with the results
373              
374             =cut
375              
376             sub selectrow_hashref {
377             my ($self, $name, $dbh, $params, $attrs) = @_;
378             my $stmt = $self->select_stmt($name, $dbh, $params, $attrs);
379             my $href = $stmt->fetchrow_hashref;
380             $stmt->finish;
381             return $href;
382             }
383              
384              
385             =head2 selectall_arrayref
386              
387             This method combines "prepare", "execute" and "fetchall_arrayref" into a single call.
388             It returns a reference to an array containing a reference to an array (or hash, see below)
389             for each row of data fetched.
390             This method interface is similar to the previous you have seen
391             in section L.
392              
393             See DBI C method for more details.
394              
395             =cut
396              
397             sub selectall_arrayref {
398             my ($self, $name, $dbh, $params, $attrs) = @_;
399             my $stmt = $self->select_stmt($name, $dbh, $params, $attrs);
400             my $aref = $stmt->fetchall_arrayref;
401             $stmt->finish;
402             return $aref;
403             }
404              
405             =head2 selectall_hashref
406              
407             This method combines "prepare", "execute" and "fetchall_arrayref" into a single call.
408             It returns a reference to an array containing a reference to an hash
409             for each row of data fetched.
410             This method interface is similar to the previous you have seen
411             in section L.
412              
413             See DBI C method for more details.
414              
415             =cut
416              
417             sub selectall_hashref {
418             my ($self, $name, $dbh, $params, $attrs) = @_;
419             my $stmt = $self->select_stmt($name, $dbh, $params, $attrs);
420             my $href = $stmt->fetchall_hashref;
421             $stmt->finish;
422             return $href;
423             }
424              
425              
426             =head2 do ( $name, $dbh, $params, $attrs )
427              
428             This method interface is similar to the previous you have seen
429             in section L. The main use of this function is
430             to execute DDL commands and INSERT, UPDATE or DELETE commands.
431             In this case, SQL::Template makes a call to DBI C
432             function and returns its results to the caller.
433              
434             =cut
435              
436              
437             sub do {
438             my ($self, $name, $dbh, $params, $attrs) = @_;
439             my $stmt = $self->_prepare_and_bind($name, $dbh, $params, $attrs);
440             return $stmt->execute;
441             }
442              
443             #*************************************************************************
444              
445             =head1 AUTHOR
446              
447             prz, C<< >>
448              
449             =head1 BUGS
450              
451             Please report any bugs or feature requests to C, or through
452             the web interface at L.
453             I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.
454              
455             =head1 SUPPORT
456              
457             You can find documentation for this module with the perldoc command.
458              
459             perldoc SQL::Template
460              
461              
462             You can also look for information at:
463              
464             =over 4
465              
466             =item * RT: CPAN's request tracker
467              
468             L
469              
470             =item * AnnoCPAN: Annotated CPAN documentation
471              
472             L
473              
474             =item * CPAN Ratings
475              
476             L
477              
478             =item * Search CPAN
479              
480             L
481              
482             =back
483              
484              
485             =head1 ACKNOWLEDGEMENTS
486              
487              
488             =head1 COPYRIGHT & LICENSE
489              
490             Copyright 2009 prz.
491              
492             This program is free software; you can redistribute it and/or modify it
493             under the terms of either: the GNU General Public License as published
494             by the Free Software Foundation; or the Artistic License.
495              
496             See http://dev.perl.org/licenses/ for more information.
497              
498              
499             =cut
500              
501              
502             1;