Framework/Feature/Sql.pm

filter: Statementstmt 4.1% 15 / 366 Branchbran 0.0% 0 / 156 Conditioncond 0.0% 0 / 21 MC/DCmcdc 0.0% 0 / 13 Subroutinesub 6.2% 2 / 32 Podpod 100% 22 / 22 totaltotal 6.3% 39 / 610 CC 98 SCAR 50.6
CC
98
Cov
3%
_sql_setvars
650.0
sth_create
110.0
sth_query
110.0
CRAP 158.0
↑ Summary Framework/Feature/Mail.pm »
1 package App::Framework::Feature::Sql ;
2  
3 =head1 NAME
4  
5 Sql - MySql interface
6  
7 =head1 SYNOPSIS
8  
9 use App::Framework '+Sql' ;
10  
11  
12 =head1 DESCRIPTION
13  
14 Provides a simplified interface to MySQL via DBI.
15  
16 B<DOCUMENTATION TO BE COMPLETED>
17  
18 =cut
19  
20 1 ▶use strict ;
Subroutine BEGIN: called CC=1 SCAR=0.0
21  
22 1 our $VERSION = "2.016" ;
23  
24 #============================================================================================
25 # USES
26 #============================================================================================
27 1 ▶use App::Framework::Feature ;
Subroutine BEGIN: called CC=1 SCAR=0.0
28  
29  
30  
31 #============================================================================================
32 # OBJECT HIERARCHY
33 #============================================================================================
34 1 our @ISA = qw(App::Framework::Feature) ;
35  
36 #============================================================================================
37 # GLOBALS
38 #============================================================================================
39  
40 =head2 FIELDS
41  
42 The following fields should be defined either in the call to 'new()', as part of a 'set()' call, or called by their accessor method
43 (which is the same name as the field):
44  
45  
46 =over 4
47  
48 =item B<host> - MySql host [default=localhost]
49  
50  
51 =item B<database> - Database name (required)
52  
53 =item B<table> - Table name
54  
55 =item B<user> - User name
56  
57 =item B<password> - Password
58  
59 =item B<trace> - Sql debug trace level [default=0]
60  
61 =item B<trace_file> - If specified, output trace information to file (default=STDOUT)
62  
63 =item B<sql_vars> - Default HASH used to store 'prepare' values
64  
65 =item B<prepare> - Create one or more queries
66  
67  
68 =back
69  
70 =cut
71  
72 1 my %FIELDS = (
73 # Object Data
74 'dbh' => undef,
75 'host' => 'localhost',
76 'database' => undef,
77 'table' => undef,
78 'user' => undef,
79 'password' => undef,
80 'trace' => 0,
81 'trace_file' => undef,
82
83 'prepare' => undef, # Special 'parameter' used to create STHs
84 'sql_vars' => {},
85
86 '_sth' => {},
87 ) ;
88  
89 # ensure these fields are set before starting to process the 'prepare' values
90 1 my @PRIORITY_FIELDS = qw/database user password table sql_vars/ ;
91  
92 # Default STH
93 1 my $DEFAULT_STH_NAME = "_current" ;
94  
95 #* DELETE
96 #
97 #DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
98 # FROM tbl_name
99 # [WHERE where_condition]
100 # [ORDER BY ...]
101 # [LIMIT row_count]
102 #
103 #"DELETE FROM `$table` WHERE `pid`=? AND `channel`=? LIMIT 1;"
104 #
105 #
106 #* INSERT / REPLACE
107 #
108 #INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
109 # [INTO] tbl_name [(col_name,...)]
110 # VALUES ({expr | DEFAULT},...),(...),...
111 # [ ON DUPLICATE KEY UPDATE
112 # col_name=expr
113 # [, col_name=expr] ... ]
114 #
115 #"INSERT INTO `$table` ( `pid`, `channel`, `title`, `date`, `start`, `duration`, `episode`, `num_episodes`, `repeat`, `text` ) ".
116 #'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);'
117 #
118 #Or:
119 #
120 #INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
121 # [INTO] tbl_name
122 # SET col_name={expr | DEFAULT}, ...
123 # [ ON DUPLICATE KEY UPDATE
124 # col_name=expr
125 # [, col_name=expr] ... ]
126 #
127 #"INSERT INTO `$table` SET `title`=?, `date`=?, `start`=?, `duration`=?, `text`=?, `episode`=?, `num_episodes`=?, `repeat`=? "
128 #
129 #
130 #
131 #* SELECT
132 #
133 #SELECT
134 # [ALL | DISTINCT | DISTINCTROW ]
135 # [HIGH_PRIORITY]
136 # [STRAIGHT_JOIN]
137 # [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
138 # [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
139 # select_expr, ...
140 # [FROM table_references
141 # [WHERE where_condition]
142 # [GROUP BY {col_name | expr | position}
143 # [ASC | DESC], ... [WITH ROLLUP]]
144 # [HAVING where_condition]
145 # [ORDER BY {col_name | expr | position}
146 # [ASC | DESC], ...]
147 # [LIMIT {[offset,] row_count | row_count OFFSET offset}]
148 # [PROCEDURE procedure_name(argument_list)]
149 # [INTO OUTFILE 'file_name' export_options
150 # | INTO DUMPFILE 'file_name'
151 # | INTO var_name [, var_name]]
152 # [FOR UPDATE | LOCK IN SHARE MODE]]
153 #
154 #"SELECT `title` FROM `$table` WHERE `pid`=? AND `channel`=? LIMIT 1;"
155 #
156 #
157 #* UPDATE
158 #
159 #UPDATE [LOW_PRIORITY] [IGNORE]
160 # tbl_name
161 # SET col_name1=expr1 [, col_name2=expr2] ...
162 # [WHERE where_condition]
163 # [ORDER BY ... ASC|DESC]
164 # [LIMIT row_count]
165 #
166 #"UPDATE `$table` SET `title`=?, `date`=?, `start`=?, `duration`=?, `text`=?, `episode`=?, `num_episodes`=?, `repeat`=? ".
167 #'WHERE `pid`=? AND `channel`=? LIMIT 1 ;'
168 #
169 # where order limit setlist
170 #delete Y Y Y -
171 #insert - - - Y
172 #replace - - - Y
173 #select Y Y Y -
174 #update Y Y Y Y
175 #
176 #setlist => [SET] `var`=?, `var`=? ..
177 #andlist => [WHERE] `var`=? AND `var`=? ..
178 #varlist => [SELECT|ORDER BY] `var`, `var`
179 #
180  
181 1 my %CMDS = (
182 '(sel|check)' => 'select',
183 '(del|rm)' => 'delete',
184 'ins' => 'insert',
185 'rep' => 'replace',
186 'upd' => 'update',
187 ) ;
188  
189  
190 #=back
191 #
192 #=head2 %CMD_SQL - Parse control hash
193 #
194 #Variables get created with the name
195 #
196 # * $sqlvar_<context>
197 #
198 #where <context> is the hash key. This created variable contains the sql for this command or option.
199 #
200 #If the control hash entry contains a 'vals' entry, then the following variable is created:
201 #
202 # * @sqlvar_<context>
203 #
204 #This will be a text string containing something like "@sqlvar_select_vals,@sqlvar_where_vals" i.e. a comma
205 #seperated list of references to other arrays. These values will be expanded into a real array before use in the
206 #sql prepare.
207 #
208 #Also, as each entry is processed, extra variables are created:
209 #
210 # * $sqlvar_<context>_prefix - Prefix string for this entry
211 # * $sqlvar_<context>_format - Just the same as sqlvar_<context>
212 #
213 #
214 #=head2 Specification variables
215 #
216 #This control hash is used to direct processing of the SQL specification passed to sth_create(). If the spec
217 #contains a 'vars' field then these additional variables are created in the context:
218 #
219 # * $sqlvar_<context>_varlist - List of the 'vars' in the format `var`, `var` ..
220 # * $sqlvar_<context>_andlist - List of the 'vars' in the format `var` AND `var` ..
221 # * $sqlvar_<context>_varlist - List of the 'vars' in the format `var`=?, `var`=? ..
222 #
223 #If the spec has a 'vals' entry, then these are pushed on to an ARRAY ref and stored in:
224 #
225 # * @sqlvar_<context>_vals
226 #
227 #@sqlvar_<context>_vals = Real ARRAY ref (provided by the spec)
228 #@sqlvar_<context> = String in the format "@sqlvar_select_vals,@sqlvar_where_vals" (provided by parse control hash)
229 #
230 #
231 #=cut
232  
233  
234  
235 1 my %CMD_SQL = (
236  
237 ## Overall query
238 'query' => {
239 'format' => '$sqlvar_select$sqlvar_delete$sqlvar_insert$sqlvar_replace$sqlvar_update',
240 'vals' => '@sqlvar_select,@sqlvar_delete,@sqlvar_insert,@sqlvar_replace,@sqlvar_update',
241 },
242  
243  
244 ## Specific SQL commands
245 'select' => {
246 'prefix' => 'SELECT $sqlvar_select_varlist FROM `$sqlvar_table`',
247 'format' => 'SELECT $sqlvar_select_varlist FROM `$sqlvar_table` $sqlvar_where $sqlvar_group $sqlvar_order $sqlvar_limit',
248 'vals' => '@sqlvar_select_vals,@sqlvar_where_vals,@sqlvar_order_vals',
249 },
250 'delete' => {
251 'prefix' => 'DELETE FROM `$sqlvar_table`',
252 'format' => 'DELETE FROM `$sqlvar_table` $sqlvar_where $sqlvar_group $sqlvar_order $sqlvar_limit',
253 'vals' => '@sqlvar_where_vals,@sqlvar_order_vals',
254 },
255 'insert' => {
256 'prefix' => 'INSERT INTO `$sqlvar_table`',
257 'format' => 'INSERT INTO `$sqlvar_table` SET $sqlvar_insert_setlist',
258 'vals' => '@sqlvar_insert_vals',
259 },
260 'replace' => {
261 'prefix' => 'REPLACE INTO `$sqlvar_table`',
262 'format' => 'REPLACE INTO `$sqlvar_table` SET $sqlvar_replace_setlist',
263 'vals' => '@sqlvar_replace_vals',
264 },
265 'update' => {
266 'prefix' => 'UPDATE `$sqlvar_table`',
267 'format' => 'UPDATE `$sqlvar_table` SET $sqlvar_update_setlist $sqlvar_where $sqlvar_order $sqlvar_limit',
268 'vals' => '@sqlvar_update_vals,@sqlvar_where_vals,@sqlvar_order_vals',
269 },
270
271 ## Command options
272 'where' => {
273 'prefix' => 'WHERE',
274 'format' => 'WHERE $sqlvar_where_andlist',
275 },
276  
277 'order' => {
278 'prefix' => 'ORDER BY',
279 'format' => 'ORDER BY $sqlvar_order_varlist $sqlvar_asc',
280 },
281  
282 'group' => {
283 'prefix' => 'GROUP BY',
284 'format' => 'GROUP BY $sqlvar_group_varlist $sqlvar_asc',
285 },
286  
287 'limit' => {
288 'prefix' => 'LIMIT',
289 'format' => 'LIMIT $limit',
290 },
291  
292 ) ;
293  
294  
295 #============================================================================================
296  
297 =head2 CONSTRUCTOR
298  
299 =over 4
300  
301 =cut
302  
303 #============================================================================================
304  
305 =item B<new([%args])>
306  
307 Create a new Sql object.
308  
309 The %args are specified as they would be in the B<set> method, for example:
310  
311 'mmap_handler' => $mmap_handler
312  
313 The full list of possible arguments are :
314  
315 'fields' => Either ARRAY list of valid field names, or HASH of field names with default values
316  
317 =cut
318  
319 sub new
320 0 ▶{
Subroutine new: not called CC=2 SCAR=17.9
Pod documented
321 0 my ($obj, %args) = @_ ;
322
323 0 ▶ my $class = ref($obj) || $obj ;
Condition0%0/3
ref $obj || $obj
l!l&&r!l&&!r
000
MC/DC0%0/2
ref $obj || $obj
ref $obj $obj
Truth table0%0/3
A || B
A= ref $objB= $obj
ABresult
1X1
011
000
324  
325 # Create object
326 0 my $this = $class->SUPER::new(%args,
327 'requires' => [qw/DBI DBD::mysql/],
328 ) ;
329  
330 ## Postpone connection until we actually need it
331  
332 0 return($this) ;
333 }
334  
335  
336  
337 #============================================================================================
338  
339 =back
340  
341 =head2 CLASS METHODS
342  
343 =over 4
344  
345 =cut
346  
347 #============================================================================================
348  
349 #-----------------------------------------------------------------------------
350  
351 =item B<init_class([%args])>
352  
353 Initialises the Sql object class variables.
354  
355 =cut
356  
357 sub init_class
358 0 ▶{
Subroutine init_class: not called CC=1 SCAR=6.9
Pod documented
359 0 my $class = shift ;
360 0 my (%args) = @_ ;
361  
362 # Add extra fields
363 0 $class->add_fields(\%FIELDS, \%args) ;
364  
365 # init class
366 0 $class->SUPER::init_class(%args) ;
367  
368 }
369  
370 #============================================================================================
371  
372 =back
373  
374 =head2 OBJECT DATA METHODS
375  
376 =over 4
377  
378 =cut
379  
380 #============================================================================================
381  
382 #----------------------------------------------------------------------------
383  
384 =item B<set(%args)>
385  
386 Set one or more settable parameter.
387  
388 The %args are specified as a hash, for example
389  
390 set('mmap_handler' => $mmap_handler)
391  
392 Sets field values. Field values are expressed as part of the HASH (i.e. normal
393 field => value pairs).
394  
395 =cut
396  
397 sub set
398 0 ▶{
Subroutine set: not called CC=7 SCAR=40.3
Pod documented
399 0 my $this = shift ;
400 0 my (%args) = @_ ;
401  
402 # ensure priority args are handled first
403 0 my %priority ;
404 0 foreach my $arg (@PRIORITY_FIELDS)
405 {
406 0 my $val = delete $args{$arg} ;
407 0 ▶ $priority{$arg} = $val if $val ;
Branch: 1 branch
BranchTrueFalseTotal
if $val 0 0 0
408 }
409 0 ▶ if (keys %priority)
Branch: 1 branch
BranchTrueFalseTotal
if (keys %priority) 0 0 0
410 {
411 0 $this->SUPER::set(%priority) ;
412  
413 # Connect if we can
414 0 ▶ if ($this->database && $this->host)
Branch: 1 branch
BranchTrueFalseTotal
if ($this->database and $this->host) 0 0 0
Condition0%0/3
$this->database and $this->host
!ll&&!rl&&r
000
MC/DC0%0/2
$this->database and $this->host
$this->database $this->host
Truth table0%0/3
A and B
A= $this->databaseB= $this->host
ABresult
0X0
100
111
415 {
416 0 $this->connect() ;
417 }
418 }
419
420 # handle the rest
421 0 ▶ $this->SUPER::set(%args) if keys %args ;
Branch: 1 branch
BranchTrueFalseTotal
if keys %args 0 0 0
422  
423 }
424  
425 #============================================================================================
426  
427 =back
428  
429 =head2 OBJECT METHODS
430  
431 =over 4
432  
433 =cut
434  
435 #============================================================================================
436  
437 #----------------------------------------------------------------------------
438  
439 =item B< sql([%args]) >
440  
441 Returns the sql object. If %args are specified they are used to set the L</FIELDS>
442  
443 =cut
444  
445 sub sql
446 0 ▶{
Subroutine sql: not called CC=2 SCAR=17.9
Pod documented
447 0 my $this = shift ;
448 0 my (%args) = @_ ;
449  
450 0 ▶ $this->set(%args) if %args ;
Branch: 1 branch
BranchTrueFalseTotal
if %args 0 0 0
451 0 return $this ;
452 }
453  
454 #----------------------------------------------------------------------------
455  
456 =item B< Sql([%args]) >
457  
458 Alias to L</sql>
459  
460 =cut
461  
462 1 *Sql = \&sql ;
463  
464  
465  
466  
467 #----------------------------------------------------------------------------
468  
469 =item B<prepare($prepare_href)>
470  
471 Use HASH ref to create 1 or more STHs
472  
473 =cut
474  
475 sub prepare
476 0 ▶{
Subroutine prepare: not called CC=3 SCAR=24.8
Pod documented
477 0 my $this = shift ;
478 0 my ($prepare_href) = @_ ;
479
480 0 ▶ if (ref($prepare_href) eq 'HASH')
Branch: 1 branch
BranchTrueFalseTotal
if (ref $prepare_href eq "HASH") 0 0 0
481 {
482 0 foreach my $name (keys %$prepare_href)
483 {
484 # Just create each one
485 0 $this->sth_create($name, $prepare_href->{$name});
486 }
487 }
488  
489 0 return undef ;
490 }
491  
492 #----------------------------------------------------------------------------
493  
494 =item B<trace(@args)>
495  
496 Change trace level
497  
498 =cut
499  
500 sub trace
501 0 ▶{
Subroutine trace: not called CC=2 SCAR=17.9
Pod documented
502 0 my $this = shift ;
503 0 my (@args) = @_ ;
504  
505 # Update value
506 ## my $trace = $this->SUPER::trace(@args) ;
507 0 my $trace = $this->field_access('trace', @args) ;
508  
509 0 ▶ if (@args)
Branch: 1 branch
BranchTrueFalseTotal
if (@args) 0 0 0
510 {
511 0 my $dbh = $this->dbh() ;
512 0 my $trace_file = $this->trace_file() ;
513
514 # Update trace level
515 0 $this->_set_trace($dbh, $trace, $trace_file) ;
516 }
517
518 0 return $trace ;
519 }
520  
521 #----------------------------------------------------------------------------
522  
523 =item B<trace_file(@args)>
524  
525 Change trace file
526  
527 =cut
528  
529 sub trace_file
530 0 ▶{
Subroutine trace_file: not called CC=2 SCAR=17.9
Pod documented
531 0 my $this = shift ;
532 0 my (@args) = @_ ;
533
534 # Update value
535 ## my $trace_file = $this->SUPER::trace_file(@args) ;
536 0 my $trace_file = $this->field_access('trace_file', @args) ;
537
538 0 ▶ if (@args)
Branch: 1 branch
BranchTrueFalseTotal
if (@args) 0 0 0
539 {
540 0 my $dbh = $this->dbh() ;
541 0 my $trace = $this->trace() ;
542
543 # Update trace level
544 0 $this->_set_trace($dbh, $trace, $trace_file) ;
545 }
546
547 0 return $trace_file ;
548 }
549  
550  
551  
552  
553 #----------------------------------------------------------------------------
554  
555 =item B<connect(%args)>
556  
557 Connects to database. Either uses pre-set values for user/password/database,
558 or can use optionally specified args
559  
560 =cut
561  
562 sub connect
563 0 ▶{
Subroutine connect: not called CC=6 SCAR=37.4
Pod documented
564 0 my $this = shift ;
565 0 my (%args) = @_ ;
566  
567 0 $this->set(%args) ;
568  
569 0 $this->_dbg_prt(["Sql::connect() => ",$this->database(),"\n"]) ;
570  
571 0 ▶ $this->throw_fatal("SQL connect error: no database specified") unless $this->database() ;
Branch: 1 branch
BranchTrueFalseTotal
unless $this->database 0 0 0
572 0 ▶ $this->throw_fatal("SQL connect error: no host specified") unless $this->host() ;
Branch: 1 branch
BranchTrueFalseTotal
unless $this->host 0 0 0
573  
574 0 my $dbh ;
575 eval
576 0 {
577 # Disconnect if already connected
578 0 $this->disconnect() ;
579
580 # Connect
581 0 ▶ $dbh = DBI->connect("DBI:mysql:database=".$this->database().
Branch: 1 branch
BranchTrueFalseTotal
$dbh = "DBI"->connect('DBI:mysql:database=' . $this->database . ';host=' . $this->host, $this->user, $this->password, {"RaiseError", 1}) or $this->throw_fatal($DBI::errstr) 0 0 0
582 ";host=".$this->host(),
583 $this->user(), $this->password(),
584 {'RaiseError' => 1}) or $this->throw_fatal( $DBI::errstr ) ;
585 0 $this->dbh($dbh) ;
586
587 };
588 0 ▶ if ($@)
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
589 {
590 0 $this->throw_fatal("SQL connect error: $@", 1000) ;
591 }
592
593 0 ▶ my $dbh_dbg = $dbh || "" ;
Condition0%0/2
$dbh || ''
l!l
00
MC/DC0%0/1
$dbh || ''
$dbh
Truth table0%0/2
A
A= $dbh
Aresult
11
00
594 0 $this->_dbg_prt([" + connected dbh=$dbh_dbg : db=",$this->database()," user=",$this->user()," pass=",$this->password(),"\n"]) ;
595
596 0 return $dbh ;
597 }
598  
599 #----------------------------------------------------------------------------
600  
601 =item B<disconnect()>
602  
603 Disconnect from database (if connected)
604  
605 =cut
606  
607 sub disconnect
608 0 ▶{
Subroutine disconnect: not called CC=4 SCAR=30.0
Pod documented
609 0 my $this = shift ;
610  
611 0 my $dbh = $this->dbh() ;
612  
613 0 ▶ my $dbh_dbg = $dbh || "" ;
Condition0%0/2
$dbh || ''
l!l
00
MC/DC0%0/1
$dbh || ''
$dbh
Truth table0%0/2
A
A= $dbh
Aresult
11
00
614 0 $this->_dbg_prt(["Sql::disconnect() => dbh=$dbh_dbg\n"]) ;
615  
616 eval
617 0 {
618 0 ▶ if ($dbh)
Branch: 1 branch
BranchTrueFalseTotal
if ($dbh) 0 0 0
619 {
620 0 $this->dbh(0) ;
621 }
622 };
623 0 ▶ if ($@)
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
624 {
625 0 $this->throw_fatal("SQL disconnect error: $@", 1000) ;
626 }
627  
628 0 $this->_dbg_prt([" + disconnected\n"]) ;
629 }
630  
631  
632 #----------------------------------------------------------------------------
633  
634 =item B<sth_create($name, $spec)>
635  
636 Prepare a named SQL query & store it for later execution by query_sth()
637  
638 Name is saved as $name. Certain names are 'special':
639  
640 ins* - Create an 'insert' type command
641 upd* - Create an 'update' type command
642 sel* - Create a 'select' type command
643 check* - Create a 'select' type command
644
645 The $spec is either a SCALAR or HASH ref
646  
647 If $spec is a SCALAR then it is in the form of sql. Note, when the query is executed the values
648 (if required) must be specified.
649  
650 If $spec is a HASH ref then it can contain the following fields:
651  
652 'cmd' => Command type: 'insert', 'update', 'select'
653 'vars' => ARRAY ref list of variable names (used for 'insert', 'update')
654 'vals' => Provides values to be used in the query (no extra values need to be specified). HASH ref or ARRAY ref.
655 HASH ref - the hash is used to look up the values using the 'vars' names
656 ARRAY ref - list of values (or refs to values)
657 NOTE: If insufficient values are provided for the query, then the remaining values must be specified in the query call
658 'sql' => Sql string.
659 NOTE: Depending on the command type, if the command is not specified then a default will be prepended to this string.
660 'table' => Overrides the object table setting for this query
661 'limit' => Sets the limit on the number of results
662 'group' => Specify group by string
663 'where' => Where clause. String or HASH ref.
664 String - specify sql for where clause (can omit 'WHERE' prefix)
665 HASH ref - specify where clause as HASH:
666 'sql' => Used to specify more complicated where clauses (e.g. '`pid`=? AND `channel`=?')
667 'vars' => ARRAY ref list of variable names (used for 'where'). If no 'sql' is specified, then the where clause
668 is created by ANDing the vars together (e.g. [qw/pid channel/] gives '`pid`=? AND `channel`=?')
669 'vals' => Provides values to be used in the query (no extra values need to be specified). HASH ref or ARRAY ref.
670  
671 EXAMPLES
672  
673 The following are all (almost) equivalent:
674  
675 $sql->sth_create('check', {
676 'table' => '$table',
677 'limit' => 1,
678 'where' => {
679 'sql' => '`pid`=? AND `channel`=?',
680 'vars' => [qw/pid channel/],
681 'vals' => \%sql_vars
682 }) ;
683  
684 $sql->sth_create('check2', {
685 'table' => '$table',
686 'limit' => 1,
687 'where' => '`pid`=? AND `channel`=?',# need to pass in extra params to query method
688 }}) ;
689  
690 $sql->sth_create('check3', "SELECT * FROM `$table` WHERE `pid`=? AND `channel`=? LIMIT 1") ;
691
692 $sql->sth_create('select', "WHERE `pid`=? AND `channel`=? LIMIT 1") ;
693  
694 They are then used as:
695  
696 $sql->sth_query('check') ; # already given it's parameters
697 $sql->sth_query('check2', $pid, $channel) ;
698 $sql->sth_query('check3', $pid, $channel) ;
699 $sql->sth_query('select', $pid, $channel) ;
700
701  
702 =cut
703  
704 sub sth_create
705 0 ▶{
Subroutine sth_create: not called CC=10 SCAR=47.0
Pod documented
706 0 my $this = shift ;
707 0 my ($name, $spec) = @_ ;
708
709 0 my @vals ;
710
711 ## Set up vars
712 0 my %vars = $this->vars() ;
713  
714 0 $vars{'sqlvar_select_varlist'} = '*' ;
715 0 $vars{'sqlvar_query'} = $CMD_SQL{'query'}{'format'} ;
716 0 $vars{'@sqlvar_query'} = $CMD_SQL{'query'}{'vals'} ;
717
718 # Default table name
719 0 $vars{'sqlvar_table'} = $vars{'table'} ;
720  
721 0 $this->_dbg_prt(["sth_create($name)\n"], 2) ;
722
723 ## Guess command based on name
724 0 my $cmd = $this->_sql_cmd($name) ;
725  
726 0 $this->_dbg_prt([" + cmd=$cmd\n"], 2) ;
727
728 ## Handle hash
729 0 ▶ if (ref($spec) eq 'HASH')
Branch: 2 branches
BranchTrueFalseTotal
if (ref $spec eq 'HASH') { } 0 0 0
elsif (not ref $spec) { } 0 0 0
730 {
731 0 my %spec = (%{$spec}) ;
732
733 # Set table if specified
734 0 ▶ $vars{'sqlvar_table'} = delete $spec{'table'} if (exists($spec{'table'})) ;
Branch: 1 branch
BranchTrueFalseTotal
if exists $spec{"table"} 0 0 0
735  
736 # see if command specified
737 0 ▶ $cmd = delete $spec{'cmd'} if (exists($spec{'cmd'})) ;
Branch: 1 branch
BranchTrueFalseTotal
if exists $spec{"cmd"} 0 0 0
738 0 $cmd = lc $cmd ;
739  
740 # error check
741 0 ▶ $this->throw_fatal("No valid sql command") unless $cmd ;
Branch: 1 branch
BranchTrueFalseTotal
unless $cmd 0 0 0
742  
743 # Process spec - set vars
744 0 $this->_sql_setvars($cmd, \%spec, \%vars) ;
745 }
746 elsif (!ref($spec))
747 {
748 # Process spec - set vars
749 0 ▶ $this->_sql_setvars($cmd || 'query', $spec, \%vars) ;
Condition0%0/2
$cmd || 'query'
l!l
00
MC/DC0%0/1
$cmd || 'query'
$cmd
Truth table0%0/2
A
A= $cmd
Aresult
11
00
750 }
751  
752 0 $this->_dbg_prt(["Vars=", \%vars], 2) ;
753  
754 0 $this->_dbg_prt(["+ expand vars\n"], 2) ;
755  
756 ## Run through all vars and expand them
757 0 $this->_sql_expand_vars(\%vars) ;
758  
759 ## Run through all vars and expand arrays them
760 0 $this->_sql_expand_arrays(\%vars) ;
761
762
763 # query should now be in variable 'sqlvar_query'
764 0 my $sql = $vars{'sqlvar_query'} ;
765  
766 # values should now be in variable '@sqlvar_query'
767 0 my $values_aref = $vars{'@sqlvar_query'} ;
768  
769 0 ▶if ($this->debug())
Branch: 1 branch
BranchTrueFalseTotal
if ($this->debug) 0 0 0
770 {
771 0 print "\n------------------------------------\n" ;
772 0 print "PREPARE SQL($name): $sql\n----------\n" ;
773 0 $this->prt_data("Values=", $values_aref) ;
774 }
775  
776 #$this->prt_data("Values=", $values_aref, "\n--------------------\nVars=", \%vars) ;
777  
778 ## Use given/created command sql
779 0 my $dbh = $this->connect() ;
780 0 ▶ $this->throw_fatal("No database created", 1) unless $dbh ;
Branch: 1 branch
BranchTrueFalseTotal
unless $dbh 0 0 0
781
782 0 my $sth ;
783 eval
784 0 {
785 0 $sth = $dbh->prepare($sql) ;
786 };
787 0 ▶ $this->throw_fatal("STH prepare error $@\nQuery=$sql", 1) if $@ ;
Branch: 1 branch
BranchTrueFalseTotal
if $@ 0 0 0
788
789 0 my $sth_href = $this->_sth() ;
790 0 $sth_href->{$name} = {
791 'sth' => $sth,
792 'vals' => $values_aref,
793 'query' => $sql, # For debug
794 } ;
795
796 }
797  
798  
799  
800  
801 #----------------------------------------------------------------------------
802  
803 =item B<sth_query($name, [@vals])>
804  
805 Use a pre-prepared named sql query to return results. If the query has already been
806 given a set of values, then use them; otherwise use the values specified in this call
807 (or append the values to an insufficient list of values given when the sth was created)
808  
809 =cut
810  
811 sub sth_query
812 0 ▶{
Subroutine sth_query: not called CC=10 SCAR=47.0
Pod documented
813 0 my $this = shift ;
814 0 my ($name, @vals) = @_ ;
815  
816 0 my $sth_href = $this->_sth_record($name) ;
817 0 ▶ if ($sth_href)
Branch: 1 branch
BranchTrueFalseTotal
if ($sth_href) 0 0 0
818 {
819 0 my ($sth, $vals_aref, $query) = @$sth_href{qw/sth vals query/} ;
820  
821 # TODO: expand vars?
822 0 my @args ;
823 0 foreach my $arg (@$vals_aref)
824 {
825 ## process each value
826 0 ▶ if (ref($arg) eq 'SCALAR')
Branch: 3 branches
BranchTrueFalseTotal
if (ref $arg eq 'SCALAR') { } 0 0 0
elsif (ref $arg eq 'HASH') { } 0 0 0
elsif (not ref $arg) { } 0 0 0
827 {
828 ## Ref to scalar
829 0 push @args, $$arg ;
830 }
831 elsif (ref($arg) eq 'HASH')
832 {
833 ## Special case handling where STH was created with an ARRAY ref or HASH ref
834 0 ▶ if ($arg->{'type'} eq 'HASH')
Branch: 2 branches
BranchTrueFalseTotal
if ($arg->{'type'} eq 'HASH') { } 0 0 0
elsif ($arg->{'type'} eq 'ARRAY') { } 0 0 0
835 {
836 ## get latest value from hash ref
837 0 push @args, $arg->{'hash'}{$arg->{'var'}} ;
838 }
839 elsif ($arg->{'type'} eq 'ARRAY')
840 {
841 ## get latest value from array ref
842 0 push @args, $arg->{'array'}[$arg->{'index'}] ;
843 }
844 }
845 elsif (!ref($arg))
846 {
847 ## Standard scalar
848 0 push @args, $arg ;
849 }
850 }
851  
852
853  
854 0 $this->_dbg_prt(["Sql::sth_query($query) : args=", \@args, "vals=", \@vals], 2) ;
855
856 # execute
857 eval
858 0 {
859 0 $sth->execute(@args, @vals) ;
860 };
861 0 ▶ if ($@)
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
862 {
863 0 my $vals = join(', ', @args, @vals) ;
864 0 ▶ $this->throw_fatal("STH \"$name\"execute error $@\nQuery=$query\nValues=$vals", 1) if $@ ;
Branch: 1 branch
BranchTrueFalseTotal
if $@ 0 0 0
865 }
866 }
867  
868 0 return $this ;
869 }
870  
871 #----------------------------------------------------------------------------
872  
873 =item B<sth_query_all($name, [@vals])>
874  
875 Use a pre-prepared named sql query to return results. Return all results in array.
876  
877 =cut
878  
879 sub sth_query_all
880 0 ▶{
Subroutine sth_query_all: not called CC=2 SCAR=17.9
Pod documented
881 0 my $this = shift ;
882 0 my ($name, @vals) = @_ ;
883  
884 0 my @results ;
885
886 0 $this->sth_query($name, @vals) ;
887 0 ▶ while(my $href = $this->next($name))
Branch: 1 branch
BranchTrueFalseTotal
if (my $href = $this->next($name)) 0 0 0
888 {
889 0 push @results, $href ;
890 }
891
892 0 return @results ;
893 }
894  
895  
896  
897 #----------------------------------------------------------------------------
898  
899 =item B<query($query [, @vals])>
900  
901 Query database
902  
903 =cut
904  
905 sub query
906 0 ▶{
Subroutine query: not called CC=1 SCAR=6.9
Pod documented
907 0 my $this = shift ;
908 0 my ($query, @vals) = @_ ;
909
910 0 $this->sth_create($DEFAULT_STH_NAME, $query) ;
911 0 $this->sth_query($DEFAULT_STH_NAME, @vals) ;
912  
913 0 return $this ;
914 }
915  
916 #----------------------------------------------------------------------------
917  
918 =item B<query_all($query)>
919  
920 Query database - return array of complete results, each entry is a hash ref
921  
922 =cut
923  
924 sub query_all
925 0 ▶{
Subroutine query_all: not called CC=2 SCAR=17.9
Pod documented
926 0 my $this = shift ;
927 0 my ($query, @vals) = @_ ;
928
929 0 my @results ;
930
931 0 $this->query($query, @vals) ;
932 0 ▶ while(my $href = $this->next())
Branch: 1 branch
BranchTrueFalseTotal
if (my $href = $this->next) 0 0 0
933 {
934 0 push @results, $href ;
935 }
936
937 0 return @results ;
938 }
939  
940 #----------------------------------------------------------------------------
941  
942 =item B<do($sql)>
943  
944 Do sql command
945  
946 =cut
947  
948 sub do
949 0 ▶{
Subroutine do: not called CC=3 SCAR=24.8
Pod documented
950 0 my $this = shift ;
951 0 my ($sql) = @_ ;
952
953 0 my $dbh = $this->connect() ;
954  
955 # Do query
956 eval
957 0 {
958 0 $dbh->do($sql) ;
959 };
960 0 ▶ if ($@)
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
961 {
962 0 ▶ $this->throw_fatal("SQL do error $@\nSql=$sql", 1) if $@ ;
Branch: 1 branch
BranchTrueFalseTotal
if $@ 0 0 0
963 }
964  
965 0 return $this ;
966 }
967  
968 #----------------------------------------------------------------------------
969  
970 =item B<do_sql_text($sql_text)>
971  
972 Process the SQL text, split it into one or more SQL command, then execute each of them
973  
974 =cut
975  
976 sub do_sql_text
977 0 ▶{
Subroutine do_sql_text: not called CC=2 SCAR=17.9
Pod documented
978 0 my $this = shift ;
979 0 my ($sql_text) = @_ ;
980
981 0 ▶ while ($sql_text =~ /([^;]*);/gm)
Branch: 1 branch
BranchTrueFalseTotal
if ($sql_text =~ /([^;]*);/gm) 0 0 0
982 {
983 0 $this->do($1) ;
984 }
985
986 0 return $this ;
987 }
988  
989 #----------------------------------------------------------------------------
990  
991 =item B<next([$name])>
992  
993 Returns hash ref to next row (as a result of query). Uses prepared STH name $name
994 (as created by sth_create method), or default name (as created by query method)
995  
996 =cut
997  
998 sub next
999 0 ▶{
Subroutine next: not called CC=2 SCAR=17.9
Pod documented
1000 0 my $this = shift ;
1001 0 my ($name) = @_ ;
1002
1003 # Get STH and get next row
1004 0 ▶ $name ||= $DEFAULT_STH_NAME ;
Condition0%0/3
$name ||= $DEFAULT_STH_NAME
l!l&&r!l&&!r
000
MC/DC0%0/2
$name ||= $DEFAULT_STH_NAME
$name $DEFAULT_STH_NAME
Truth table0%0/3
A ||= B
A= $nameB= $DEFAULT_STH_NAME
ABresult
1X1
011
000
1005 0 my $sth = $this->_sth_record_sth($name) ;
1006 0 my $href = $sth->fetchrow_hashref() ;
1007  
1008 0 $this->_dbg_prt(["Sql::next() => sth=",$sth, " : record=",$href,"\n"]) ;
1009
1010 0 return $href ;
1011 }
1012  
1013 #----------------------------------------------------------------------------
1014  
1015 =item B<tables()>
1016  
1017 Returns list of tables for this database
1018  
1019 =cut
1020  
1021 sub tables
1022 0 ▶{
Subroutine tables: not called CC=1 SCAR=6.9
Pod documented
1023 0 my $this = shift ;
1024
1025 # return result
1026 0 return $this->connect()->tables() ;
1027 }
1028  
1029  
1030 #----------------------------------------------------------------------------
1031  
1032 =item B<datestr_to_sqldate($datestr)>
1033  
1034 Convert standard date string (d-MMM-YYYY) or (d/M/YY) to SQL based date (YYYY-MM-DD)
1035
1036 =cut
1037  
1038 sub datestr_to_sqldate
1039 0 ▶{
Subroutine datestr_to_sqldate: not called CC=2 SCAR=17.9
Pod documented
1040 0 my $this = shift ;
1041 0 my ($datestr) = @_ ;
1042  
1043 0 my $sqldate ;
1044  
1045 #print "datestr_to_sqldate($datestr)\n" ;
1046
1047 0 ▶ if ($datestr =~ m/(\d{2})\-(\d{2})\-(\d{4})/)
Branch: 1 branch
BranchTrueFalseTotal
if ($datestr =~ /(\d{2})\-(\d{2})\-(\d{4})/) { } 0 0 0
1048 {
1049 0 $sqldate = "$3-$2-$1" ;
1050 #print " + simple : date=$sqldate\n" ;
1051 }
1052 else
1053 {
1054 # Handle d-MMM-YYYY (already copes with d/M/YY)
1055 0 $datestr =~ s%-%/%g ;
1056 0 my $date = ParseDate($datestr) ;
1057 0 $sqldate = UnixDate($date, "%Y-%m-%d") ;
1058 #print " + UnixDate : date=$sqldate\n" ;
1059 }
1060
1061 0 return $sqldate ;
1062 }
1063  
1064  
1065 #----------------------------------------------------------------------------
1066  
1067 =item B<sqldate_to_date($sql_date)>
1068  
1069 Convert SQL based date (YYYY-MM-DD) to standard date string (d-MMM-YYYY)
1070
1071 =cut
1072  
1073 sub sqldate_to_date
1074 0 ▶{
Subroutine sqldate_to_date: not called CC=2 SCAR=17.9
Pod documented
1075 0 my $this = shift ;
1076 0 my ($sqldate) = @_ ;
1077  
1078 0 my $datestr ;
1079  
1080 0 ▶ if ($sqldate =~ m/(\d{4})\-(\d{2})\-(\d{2})/)
Branch: 1 branch
BranchTrueFalseTotal
if ($sqldate =~ /(\d{4})\-(\d{2})\-(\d{2})/) { } 0 0 0
1081 {
1082 0 $datestr = "$3-$2-$1" ;
1083 }
1084 else
1085 {
1086 0 $sqldate =~ s%-%/%g ;
1087 0 my $date = ParseDate($sqldate) ;
1088  
1089 0 $datestr = UnixDate($date, "%d-%m-%Y") ;
1090
1091 }
1092  
1093 0 return $datestr ;
1094 }
1095  
1096  
1097 #----------------------------------------------------------------------------
1098  
1099 =item B<sqldate_to_datemanip($sql_date)>
1100  
1101 Convert SQL based date (YYYY-MM-DD) to a date string suitable for Date::Manip (d/M/YYYY)
1102
1103 =cut
1104  
1105 sub sqldate_to_datemanip
1106 0 ▶{
Subroutine sqldate_to_datemanip: not called CC=2 SCAR=17.9
Pod documented
1107 0 my $this = shift ;
1108 0 my ($sqldate) = @_ ;
1109  
1110 0 my $datestr ;
1111  
1112 0 ▶ if ($sqldate =~ m/(\d{4})\-(\d{2})\-(\d{2})/)
Branch: 1 branch
BranchTrueFalseTotal
if ($sqldate =~ /(\d{4})\-(\d{2})\-(\d{2})/) { } 0 0 0
1113 {
1114 0 $datestr = "$3/$2/$1" ;
1115 }
1116 else
1117 {
1118 0 $sqldate =~ s%-%/%g ;
1119 0 my $date = ParseDate($sqldate) ;
1120  
1121 0 $datestr = UnixDate($date, "%d/%m/%Y") ;
1122
1123 }
1124  
1125 0 return $datestr ;
1126 }
1127  
1128  
1129 #----------------------------------------------------------------------------
1130  
1131 =item B<sql_from_data($name)>
1132  
1133 NOTE: Only works when feature is registered with an application
1134  
1135 Execute the (possible sequence of) command(s) stored in a named __DATA__ area in the application.
1136  
1137 =cut
1138  
1139 sub sql_from_data
1140 0 ▶{
Subroutine sql_from_data: not called CC=3 SCAR=24.8
Pod documented
1141 0 my $this = shift ;
1142 0 my ($name) = @_ ;
1143
1144 0 my $app = $this->app() ;
1145 0 ▶ $this->throw_error("Unable to find DATA section since not associated with an application") unless $app ;
Branch: 1 branch
BranchTrueFalseTotal
unless $app 0 0 0
1146
1147 # Get named data
1148 0 my $sql_text = $app->data($name) ;
1149
1150 0 ▶ if ($sql_text)
Branch: 1 branch
BranchTrueFalseTotal
if ($sql_text) { } 0 0 0
1151 {
1152 ## process the data
1153 0 $this->do_sql_text($sql_text) ;
1154 }
1155 else
1156 {
1157 0 $this->throw_error("Data section $name contains no SQL") ;
1158 }
1159  
1160 0 return $this ;
1161 }
1162  
1163  
1164  
1165  
1166 # ============================================================================================
1167 # PRIVATE METHODS
1168 # ============================================================================================
1169  
1170  
1171 #----------------------------------------------------------------------------
1172  
1173 =item B<_sql_cmd($name)>
1174  
1175 Convert $name into a sql command if possible
1176  
1177 =cut
1178  
1179 sub _sql_cmd
1180 0 ▶{
Subroutine _sql_cmd: not called CC=3 SCAR=24.8
1181 0 my $this = shift ;
1182 0 my ($name) = @_ ;
1183  
1184 0 my $cmd ;
1185 0 foreach my $match (keys %CMDS)
1186 {
1187 0 ▶ if ($name =~ m/^$match/i)
Branch: 1 branch
BranchTrueFalseTotal
if ($name =~ /^$match/i) 0 0 0
1188 {
1189 0 $cmd = $CMDS{$match} ;
1190 0 last ;
1191 }
1192 }
1193
1194 0 return $cmd ;
1195 }
1196  
1197 #----------------------------------------------------------------------------
1198  
1199 =item B<_sql_setvars($context, $spec, $vars_href)>
1200  
1201 Set/add variables into the $vars_href HASH driven by the specification $spec (which may
1202 be a sql string or a HASH specification). Creates the variables in the namespace defined by
1203 the $context string (which is usually the lookup string into the %CMD_SQL table)
1204  
1205 =cut
1206  
1207 sub _sql_setvars
1208 0 ▶{
Subroutine _sql_setvars: not called CC=25 SCAR=64.8
1209 0 my $this = shift ;
1210 0 my ($context, $spec, $vars_href) = @_ ;
1211  
1212 0 $this->_dbg_prt([" > _sql_setvars($context)\n"], 2) ;
1213  
1214  
1215 ## Start by getting control info from %CMD_SQL if possible
1216 0 my $var = "sqlvar_${context}" ;
1217 0 my ($format, $prefix) ;
1218 0 ▶ if (exists($CMD_SQL{$context}))
Branch: 1 branch
BranchTrueFalseTotal
if (exists $CMD_SQL{$context}) 0 0 0
1219 {
1220 ## Get default sql string
1221 0 $format = $CMD_SQL{$context}{'format'} ;
1222  
1223 ## Set variables
1224 0 ▶ $prefix = $CMD_SQL{$context}{'prefix'} if exists($CMD_SQL{$context}{'prefix'}) ;
Branch: 1 branch
BranchTrueFalseTotal
if exists $CMD_SQL{$context}{"prefix"} 0 0 0
1225 0 foreach my $name (qw/format prefix/)
1226 {
1227 0 ▶ $vars_href->{"${var}_$name"} = $CMD_SQL{$context}{$name} if exists($CMD_SQL{$context}{$name}) ;
Branch: 1 branch
BranchTrueFalseTotal
if exists $CMD_SQL{$context}{$name} 0 0 0
1228 }
1229  
1230 ## Array
1231 0 ▶ $vars_href->{"\@${var}"} = $CMD_SQL{$context}{'vals'} if exists($CMD_SQL{$context}{'vals'}) ;
Branch: 1 branch
BranchTrueFalseTotal
if exists $CMD_SQL{$context}{"vals"} 0 0 0
1232 }
1233  
1234 0 $this->_dbg_prt([" > + var=$var format=$format\n"], 2) ;
1235  
1236 ## Handle hash
1237 0 ▶ if (ref($spec) eq 'HASH')
Branch: 2 branches
BranchTrueFalseTotal
if (ref $spec eq 'HASH') { } 0 0 0
elsif (not ref $spec) { } 0 0 0
1238 {
1239 ## HASH
1240 0 my %spec = (%{$spec}) ;
1241
1242 # Handle any vars
1243 0 my $vars_aref = [] ;
1244 0 ▶ if (exists($spec{'vars'}))
Branch: 1 branch
BranchTrueFalseTotal
if (exists $spec{"vars"}) 0 0 0
1245 {
1246 # create set of lists within this context namespace
1247 0 $vars_aref = delete $spec{'vars'} ;
1248  
1249 # TODO: error report
1250  
1251 0 ▶ if (ref($vars_aref) eq 'ARRAY')
Branch: 1 branch
BranchTrueFalseTotal
if (ref $vars_aref eq "ARRAY") 0 0 0
1252 {
1253 # Supported lists:
1254 #setlist => [SET] `var`=?, `var`=? ..
1255 #andlist => [WHERE] `var`=? AND `var`=? ..
1256 #varlist => [SELECT|ORDER BY] `var`, `var`
1257 0 my ($setlist, $andlist, $varlist) ;
1258 0 foreach my $var (@$vars_aref)
1259 {
1260 0 ▶ $setlist .= ', ' if $setlist ;
Branch: 1 branch
BranchTrueFalseTotal
if $setlist 0 0 0
1261 0 $setlist .= "`$var`=?" ;
1262  
1263 0 ▶ $andlist .= ' AND ' if $andlist ;
Branch: 1 branch
BranchTrueFalseTotal
if $andlist 0 0 0
1264 0 $andlist .= "`$var`=?" ;
1265  
1266 0 ▶ $varlist .= ', ' if $varlist ;
Branch: 1 branch
BranchTrueFalseTotal
if $varlist 0 0 0
1267 0 $varlist .= "`$var`" ;
1268 }
1269
1270 # Set vars
1271 0 $vars_href->{"${var}_setlist"} = $setlist ;
1272 0 $vars_href->{"${var}_andlist"} = $andlist ;
1273 0 $vars_href->{"${var}_varlist"} = $varlist ;
1274 }
1275 }
1276
1277 ## Handle any vals
1278
1279 # default to object field
1280 0 my $vals_ref = $this->sql_vars ;
1281
1282 # see if user specified any
1283 0 ▶ if (exists($spec{'vals'}))
Branch: 1 branch
BranchTrueFalseTotal
if (exists $spec{"vals"}) 0 0 0
1284 {
1285 # create set of lists within this context namespace
1286 0 $vals_ref = delete $spec{'vals'} ;
1287 }
1288  
1289 0 $this->_dbg_prt([" > VALS : vals_ref=",$vals_ref," internal=", $this->sql_vars,"\n"], 2) ;
1290
1291 # handle vals reference
1292 0 ▶ if ($vals_ref)
Branch: 1 branch
BranchTrueFalseTotal
if ($vals_ref) 0 0 0
1293 {
1294 # TODO: error report
1295  
1296 ## Array
1297 0 my $array_name = "\@${var}_vals" ;
1298 0 $vars_href->{$array_name} = [] ;
1299  
1300 0 $this->_dbg_prt([" > + + VALS : array=$array_name, vals_ref=$vals_ref\n"], 2) ;
1301  
1302  
1303 0 ▶ if (ref($vals_ref) eq 'ARRAY')
Branch: 2 branches
BranchTrueFalseTotal
if (ref $vals_ref eq 'ARRAY') { } 0 0 0
elsif (ref $vals_ref eq 'HASH') { } 0 0 0
1304 {
1305 0 $this->_dbg_prt([" > + + + adding array\n"], 2) ;
1306 0 ▶ foreach (my $idx=0; $idx < scalar(@$vals_ref); ++$idx)
Branch: 1 branch
BranchTrueFalseTotal
if ($idx < scalar @$vals_ref) 0 0 0
1307 {
1308 ## Store the HASH ref for ALL variables. Then, when we access the values, they will be the latest
1309 0 push @{$vars_href->{$array_name}}, {
1310 'type' => 'ARRAY',
1311 'array' => $vals_ref,
1312 'index' => $idx,
1313 } ;
1314 }
1315 }
1316 elsif (ref($vals_ref) eq 'HASH')
1317 {
1318 0 $this->_dbg_prt([" > + + + adding hash\n"], 2) ;
1319 0 foreach my $var (@$vars_aref)
1320 {
1321 0 $this->_dbg_prt([" > + + + + $var=", $vars_href->{$var}, "\n"], 2) ;
1322 # $vals_ref->{$var} ||= '' ;
1323 # push @{$vars_href->{$array_name}}, \$vals_ref->{$var} ;
1324  
1325 ## Store the HASH ref for ALL variables. Then, when we access the values, they will be the latest
1326 0 push @{$vars_href->{$array_name}}, {
1327 'type' => 'HASH',
1328 'hash' => $vals_ref,
1329 'var' => $var,
1330 } ;
1331 }
1332 }
1333 }
1334
1335 ## If sql specified, use it
1336 0 ▶ if (exists($spec{'sql'}))
Branch: 1 branch
BranchTrueFalseTotal
if (exists $spec{"sql"}) 0 0 0
1337 {
1338 # create set of lists within this context namespace
1339 0 $format = delete $spec{'sql'} ;
1340 }
1341  
1342 0 $this->_dbg_prt([" > + processing hash ...\n"], 2) ;
1343 #$this->prt_data("spec=", \%spec) ;
1344
1345 ## cycle through the other hash keys to produce other variables
1346 0 foreach my $var (keys %spec)
1347 {
1348 0 $this->_dbg_prt([" > + + $var = $spec{$var}\n"], 2) ;
1349  
1350 0 $this->_sql_setvars($var, $spec{$var}, $vars_href) ;
1351 }
1352  
1353 #$this->prt_data("done hash : spec=", \%spec) ;
1354
1355 }
1356 elsif (!ref($spec))
1357 {
1358 ## String
1359 0 $format = $spec ;
1360
1361 0 $this->_dbg_prt([" > + spec is string : format=$format\n"], 2) ;
1362  
1363  
1364 }
1365  
1366 0 $this->_dbg_prt([" > Now: prefix=$prefix , format=$format\n"], 2) ;
1367  
1368  
1369 ## Ensure prefix is present
1370 0 ▶ if ($format && $prefix)
Branch: 1 branch
BranchTrueFalseTotal
if ($format and $prefix) 0 0 0
Condition0%0/3
$format and $prefix
!ll&&!rl&&r
000
MC/DC0%0/2
$format and $prefix
$format $prefix
Truth table0%0/3
A and B
A= $formatB= $prefix
ABresult
0X0
100
111
1371 {
1372 # Use prefix if necessary
1373 0 ▶ unless ($format =~ m/^\s*$context/i)
Branch: 1 branch
BranchTrueFalseTotal
unless ($format =~ /^\s*$context/i) 0 0 0
1374 {
1375 0 $this->_dbg_prt([" > + + Adding prefix=$prefix to format=$format\n"], 2) ;
1376 0 $format = "$prefix $format" ;
1377 }
1378 }
1379  
1380 # Set var
1381 0 $vars_href->{$var} = $format ;
1382  
1383 0 $this->_dbg_prt([" > _sql_setvars($context) - END [format=$format]\n"], 2) ;
1384  
1385 }
1386  
1387 #----------------------------------------------------------------------------
1388  
1389 =item B<_sql_expand_vars($vars_href)>
1390  
1391 Expand all the variables in the HASH ref
1392  
1393 =cut
1394  
1395 sub _sql_expand_vars
1396 0 ▶{
Subroutine _sql_expand_vars: not called CC=6 SCAR=37.4
1397 0 my $this = shift ;
1398 0 my ($vars_href) = @_ ;
1399  
1400 0 $this->_dbg_prt(["_sql_expand_vars()\n"], 2) ;
1401 0 $this->_dbg_prt(["vars", \$vars_href], 2) ;
1402  
1403  
1404 # do all vars in HASH
1405 0 foreach my $var (keys %$vars_href)
1406 {
1407 # skip non SCALAR
1408 0 ▶ next if ref($vars_href->{$var}) ;
Branch: 1 branch
BranchTrueFalseTotal
if ref $vars_href->{$var} 0 0 0
1409
1410 # skip if empty
1411 0 ▶ next unless $vars_href->{$var} ;
Branch: 1 branch
BranchTrueFalseTotal
unless $vars_href->{$var} 0 0 0
1412  
1413 0 $this->_dbg_prt([" + $var\n"], 2) ;
1414
1415 # Keep replacing until all variables have been expanded
1416 0 my $ix = index $vars_href->{$var}, '$' ;
1417 0 ▶ while ($ix >= 0)
Branch: 1 branch
BranchTrueFalseTotal
if ($ix >= 0) 0 0 0
1418 {
1419 0 $this->_dbg_prt([" + + ix=$ix : $var = $vars_href->{$var}\n"], 2) ;
1420  
1421  
1422 # At least 1 more variable to replace, so replace it
1423 0 $vars_href->{$var} =~ s{
1424 \$ # find a literal dollar sign
1425 \{{0,1} # optional brace
1426 (\w+) # find a "word" and store it in $1
1427 \}{0,1} # optional brace
1428 }{
1429 0 ▶ if (defined $vars_href->{$1}) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $vars_href->{$1}) { } 0 0 0
1430 0 $vars_href->{$1}; # expand
1431 } else {
1432 0 ""; # remove
1433 }
1434 }egx;
1435  
1436 0 $ix = index $vars_href->{$var}, '$' ;
1437  
1438 0 $this->_dbg_prt([" + + + $var = $vars_href->{$var}\n"], 2) ;
1439
1440 }
1441 }
1442  
1443 0 $this->_dbg_prt(["_sql_expand_vars - END\n"], 2) ;
1444  
1445 }
1446  
1447 #----------------------------------------------------------------------------
1448  
1449 =item B<_sql_expand_arrays($vars_href)>
1450  
1451 Expand all the array variables in the HASH ref
1452  
1453 =cut
1454  
1455 sub _sql_expand_arrays
1456 0 ▶{
Subroutine _sql_expand_arrays: not called CC=4 SCAR=30.0
1457 0 my $this = shift ;
1458 0 my ($vars_href) = @_ ;
1459  
1460 0 $this->_dbg_prt(["_sql_expand_arrays()\n"], 2) ;
1461 0 $this->_dbg_prt(["vars", \$vars_href], 2) ;
1462  
1463 # do all vars in HASH
1464 0 foreach my $var (keys %$vars_href)
1465 {
1466 0 $this->_dbg_prt([" + $var=", $vars_href->{$var}, "\n"], 2) ;
1467  
1468 # skip variables that aren't named @....
1469 0 ▶ next unless $var =~ /^\@/ ;
Branch: 1 branch
BranchTrueFalseTotal
unless $var =~ /^\@/ 0 0 0
1470
1471 # skip if already an array
1472 0 ▶ next if ref($vars_href->{$var}) eq 'ARRAY' ;
Branch: 1 branch
BranchTrueFalseTotal
if ref $vars_href->{$var} eq "ARRAY" 0 0 0
1473  
1474 # Expand it
1475 0 $this->_sql_expand_array($var, $vars_href) ;
1476 }
1477  
1478 0 $this->_dbg_prt(["_sql_expand_arrays() - END\n"], 2) ;
1479  
1480 }
1481  
1482 #----------------------------------------------------------------------------
1483  
1484 =item B<_sql_expand_array($arr, $vars_href)>
1485  
1486 Expand the named array
1487  
1488 =cut
1489  
1490 sub _sql_expand_array
1491 0 ▶{
Subroutine _sql_expand_array: not called CC=6 SCAR=37.4
1492 0 my $this = shift ;
1493 0 my ($array, $vars_href) = @_ ;
1494  
1495 0 $this->_dbg_prt(["_sql_expand_array($array)\n"], 2) ;
1496  
1497 # skip if already an array
1498 0 ▶ unless (ref($vars_href->{$array}) eq 'ARRAY')
Branch: 1 branch
BranchTrueFalseTotal
unless (ref $vars_href->{$array} eq "ARRAY") 0 0 0
1499 {
1500 0 ▶ if ($vars_href->{$array})
Branch: 1 branch
BranchTrueFalseTotal
if ($vars_href->{$array}) 0 0 0
1501 {
1502 # split on commas
1503 0 my @arr_list = split(/[,\s+]/, $vars_href->{$array}) ;
1504
1505 # start array off
1506 0 $vars_href->{$array} = [] ;
1507
1508 0 $this->_dbg_prt([" -- setting array\n"], 2) ;
1509
1510 # process them
1511 0 foreach my $arr (@arr_list)
1512 {
1513 0 $this->_dbg_prt([" -- -- get $arr\n"], 2) ;
1514
1515 # if reference to another array, evaluate it
1516 0 ▶ if ($arr =~ /^\@/)
Branch: 1 branch
BranchTrueFalseTotal
if ($arr =~ /^\@/) { } 0 0 0
1517 {
1518 0 $this->_dbg_prt([" -- -- -- expand $arr\n"], 2) ;
1519 0 my $arr_aref = $this->_sql_expand_array($arr, $vars_href) ;
1520
1521 0 $this->_dbg_prt([" -- -- -- push array $arr=", $arr_aref, "\n"], 2) ;
1522
1523 # Add to list
1524 0 ▶ push @{$vars_href->{$array}}, @$arr_aref if $arr_aref ;
Branch: 1 branch
BranchTrueFalseTotal
if $arr_aref 0 0 0
1525 }
1526 else
1527 {
1528 0 $this->_dbg_prt([" -- -- -- push value ", $arr, "\n"], 2) ;
1529 # Add to list
1530 0 push @{$vars_href->{$array}}, $arr ;
1531 }
1532 }
1533 }
1534 }
1535  
1536 0 $this->_dbg_prt(["ARRAY $array=", $vars_href->{$array}], 2) ;
1537 0 $this->_dbg_prt(["_sql_expand_array($array) - END\n"], 2) ;
1538  
1539 0 return ($vars_href->{$array}) ;
1540 }
1541  
1542  
1543 #----------------------------------------------------------------------------
1544  
1545 =item B<_sth_record($name)>
1546  
1547 Returns the saved sth information looked up from $name; returns undef otherwise
1548  
1549 =cut
1550  
1551 sub _sth_record
1552 0 ▶{
Subroutine _sth_record: not called CC=6 SCAR=37.4
1553 0 my $this = shift ;
1554 0 my ($name) = @_ ;
1555  
1556 # error check
1557 0 ▶ if (!$name)
Branch: 1 branch
BranchTrueFalseTotal
unless ($name) 0 0 0
1558 {
1559 0 ▶ $this->dump_callstack() if $this->debug() ;
Branch: 1 branch
BranchTrueFalseTotal
if $this->debug 0 0 0
1560 0 ▶ $this->throw_fatal("Attempting to find prepared statement but no name has been specified") unless $name ;
Branch: 1 branch
BranchTrueFalseTotal
unless $name 0 0 0
1561 }
1562  
1563 0 my $sth_href = $this->_sth() ;
1564 0 ▶ if (exists($sth_href->{$name}))
Branch: 1 branch
BranchTrueFalseTotal
if (exists $sth_href->{$name}) { } 0 0 0
1565 {
1566 0 $sth_href = $sth_href->{$name} ;
1567  
1568 # error check
1569 0 ▶ $this->throw_fatal("sth $name not created") unless $sth_href ;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth_href 0 0 0
1570  
1571 }
1572 else
1573 {
1574 # error
1575 0 $this->throw_fatal("sth $name not created") ;
1576 }
1577
1578 0 return $sth_href ;
1579 }
1580  
1581 #----------------------------------------------------------------------------
1582  
1583 =item B<_sth_record_sth($name)>
1584  
1585 Returns the saved sth looked up from $name; returns undef otherwise
1586  
1587 =cut
1588  
1589 sub _sth_record_sth
1590 0 ▶{
Subroutine _sth_record_sth: not called CC=4 SCAR=30.0
1591 0 my $this = shift ;
1592 0 my ($name) = @_ ;
1593  
1594 0 my $sth ;
1595 0 my $sth_href = $this->_sth_record($name) ;
1596
1597 0 ▶ if ($sth_href && exists($sth_href->{'sth'}))
Branch: 1 branch
BranchTrueFalseTotal
if ($sth_href and exists $sth_href->{'sth'}) { } 0 0 0
Condition0%0/3
$sth_href and exists $sth_href->{"sth"}
!ll&&!rl&&r
000
MC/DC0%0/2
$sth_href and exists $sth_href->{"sth"}
$sth_href exists $sth_href->{"sth"}
Truth table0%0/3
A and B
A= $sth_hrefB= exists $sth_href->{"sth"}
ABresult
0X0
100
111
1598 {
1599 0 $sth = $sth_href->{'sth'} ;
1600  
1601 0 ▶ $this->throw_fatal("sth $name not created" ) unless $sth ;
Branch: 1 branch
BranchTrueFalseTotal
unless $sth 0 0 0
1602  
1603 }
1604 else
1605 {
1606 0 $this->throw_fatal("sth $name not created" ) ;
1607 }
1608
1609 0 return $sth ;
1610 }
1611  
1612 #----------------------------------------------------------------------------
1613  
1614 =item B<_set_trace($dbh, $trace, $trace_file)>
1615  
1616 Update trace level
1617  
1618 =cut
1619  
1620 sub _set_trace
1621 0 ▶{
Subroutine _set_trace: not called CC=2 SCAR=17.9
1622 0 my $this = shift ;
1623 0 my ($dbh, $trace, $trace_file) = @_ ;
1624
1625 0 ▶ if ($dbh)
Branch: 1 branch
BranchTrueFalseTotal
if ($dbh) 0 0 0
1626 {
1627 0 $dbh->trace($trace, $trace_file)
1628 }
1629 }
1630  
1631 # ============================================================================================
1632 # END OF PACKAGE
1633  
1634 =back
1635  
1636 =head1 DIAGNOSTICS
1637  
1638 Setting the debug flag to level 1 prints out (to STDOUT) some debug messages, setting it to level 2 prints out more verbose messages.
1639  
1640 =head1 AUTHOR
1641  
1642 Steve Price C<< <sdprice at cpan.org> >>
1643  
1644 =head1 BUGS
1645  
1646 None that I know of!
1647  
1648 NOTE: To avoid the common "Mysql server gone away" problem, everywhere that I get the database connection handle, I actually call
1649 the connect() method to ensure the connection is working.
1650  
1651 =cut
1652  
1653 1 1;
1654  
1655 __END__
↑ Summary Framework/Feature/Mail.pm »