Template.pm

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