blib/lib/DBIx/TableHash.pm

filter: Statementstmt 5.5% 18 / 327 Branchbran 0.0% 0 / 180 Conditioncond 0.0% 0 / 163 MC/DCmcdc 0.0% 0 / 97 Subroutinesub 16.1% 5 / 31 Podpod 0.0% 0 / 17 totaltotal 2.8% 23 / 815 CC 152 SCAR 53.6
CC
152
Cov
3%
initialize
650.0
SearchWithSingleJoin
380.0
FETCH
240.0
CRAP 212.1
↑ Summary
1 #!/usr/bin/perl
2 ## Emacs: -*- tab-width: 4; -*-
3  
4 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
5  
6 package DBIx::TableHash;
7  
8 1 ▶use vars qw($VERSION); $VERSION = '1.04';
Subroutine BEGIN: called CC=1 SCAR=0.0
9  
10 =pod
11  
12 =head1 NAME
13  
14 DBIx::TableHash - Tie a hash to a mysql table + SQL utils
15  
16 =head1 SYNOPSIS
17  
18 use DBIx::TableHash;
19 my $DBHash = DBIx::TableHash->create_or_die
20 (my $Params =
21 {
22 DBIDriver => 'mysql',
23 Database => 'mydatabase',
24 HostName => 'localhost',
25 Port => undef,
26 Login => '',
27 Password => '',
28  
29 TableName => 'SalesPeople',
30 KeyField => 'FullName',
31  
32 ## For multi-key lookup:
33 FixedKeys => {AreaCode => 415,
34 StatusCode => 'Active',
35 RecordType => 'Primary'},
36
37 ## To retrieve a single value:
38 ValueField => 'PhoneNumber',
39  
40 ## ... or for "multi-value" retrieval...
41 ValueField => undef,
42  
43 ## ... optionally specifying...
44 RetrieveFields => [qw(Title Territory Quota)],
45
46 ## For caching:
47 CacheMode => 'CacheBeforeIterate'
48 ## or...
49 CacheMode => 'CacheOneTime'
50 ## or...
51 CacheMode => 'CacheNone'
52 }
53 );
54  
55 my %DBHash; tie(%DBHash, 'DBIx::TableHash', $Params);
56  
57 my $DBHash = DBIx::TableHash->create($Params) or die "Help!";
58 my $DBHash = DBIx::TableHash->create_or_die($Params);
59  
60 my $DBHash = DBIx::TableHash->create_copy($Params) or die "Help!";
61 my $DBHash = DBIx::TableHash->create_copy_or_die($Params);
62  
63 =head1 OVERVIEW
64  
65 All parameters are passed via a single anonymous hash.
66  
67 All parameters are optional, but you'll almost always need to specify
68 Database, TableName, and KeyField.
69  
70 Omitting ValueField puts the hash in "multi-value" mode, where you
71 store/retrieve a hash of fields/values instead of a single value. In
72 "multi-value" mode all fields in each record are retrieved on every
73 fetch; RetrieveFields limits fields retrieved to a specified list.
74  
75 Specifying FixedKeys puts the hash in "multi-key" mode, in which only
76 a subset of the database table, corresopnding to records that match
77 the spec in FixedKeys, is operated on.
78  
79 Cache modes reduce querying, but lose synchronization and hog memory.
80  
81 The object is designed to be easy subclass. Try making a subclass
82 that sets defaults for all or most of the parameters, so the caller
83 doesn't have to supply any at instantiation time.
84  
85 "create_copy" methods efficiently create and return potentially huge
86 untied hash "snapshot" of the same data that would have been retrieved
87 by the corresponding tied hash.
88  
89  
90 =head1 DETAILS
91  
92 The DBHash object is designed to tie a hash to a table or a subset of
93 records in a table in a DBI database (only tested with mysql in the
94 current version, but expected to work with any vendor).
95  
96 If the table only has a single KeyField, which this modules assumes to
97 be a unique key field in the table, then the hash keys are stored and
98 retrieved in that field, and the values are saved in and returned from
99 the ValueField. Records are automatically created and deleted as the
100 hash is used like any other hash. (If the table is read-only, be sure
101 not to try to store into the tied hash!)
102  
103 To access only a subset of the records in a table, you may specify
104 hash of "FixedKeys", which is a hash mapping OTHER field names to
105 fixed values that those fields must have for all lookups, updates, and
106 inserts done via the hash interface. This sets up a virtual hash
107 corresponding to a subset of the table where N key fields are fixed at
108 given values and a different key field may vary.
109  
110 There are several ways to use this module.
111  
112 Quick and dirty mode (single-key, single-value) using tie:
113  
114 use DBIx::TableHash;
115 my %PhoneNumbers;
116 tie (%PhoneNumbers, 'DBIx::TableHash',
117 {Database => 'mydatabase',
118 TableName => 'SalesPeople',
119 KeyField => 'FullName',
120 ValueField => 'PhoneNumber'})
121 or die "Failed to connect to database";
122  
123 Then you can use %PhoneNumbers like any hash mapping FullName to
124 PhoneNumber. Any retrieval of data results in corresponding SQL
125 queries being made to the database. Modifying the hash modifies the
126 database.
127  
128  
129 Even quicker mode using create():
130  
131 For convenience, you can use the create() class method to do the tying
132 for you. It creates an anonymous hash, ties it and, returns it. It
133 takes the same parameters as new() and tie().
134  
135 use DBIx::TableHash;
136 my $PhoneNumbers = DBIx::TableHash->create(......)
137 or die "Failed to connect to database";
138  
139 Quicker still using create_or_die():
140  
141 use DBIx::TableHash;
142 my $PhoneNumbers = DBIx::TableHash->create_or_die(......);
143  
144 create() carps and returns undef if it can't connect to the database.
145  
146 create_or_die() croaks (dies) your program, with the same error
147 message as create() would have given.
148  
149 Normally, create() will carp() (warn) you with an error message upon
150 failure, and return undef.
151  
152 If you would have handled your error by saying "or die", and ar
153 comfortable with create's error message rather than your own, then
154 create_or_die() is for you.
155  
156 Using one of the create() methods instead of new() and tie() works
157 with all of the different modes discussed below, and all parameters
158 are the same either way.
159  
160  
161 Cooler subclassing mode:
162  
163 You can create a simple subclass that provides default parmas in an
164 initialize method so they don't have to be provided by the caller ...
165  
166 ### MyCompany/SalesPhoneHash.pm:
167  
168 #!/usr/bin/perl
169  
170 use strict;
171  
172 package MyCompany::SalesPhoneHash;
173 use vars qw(@ISA);
174 use DBIx::TableHash;
175 @ISA = qw(DBIx::TableHash);
176  
177 sub initialize
178 {
179 my $this = shift;
180  
181 $this->{Database} ||= 'mydatabase'; ## Name of database to connect to.
182 $this->{TableName} ||= 'SalesPeople'; ## Table in which to store the data
183 $this->{KeyField} ||= 'FullName'; ## Name of the key field
184 $this->{ValueField} ||= 'PhoneNumber'; ## Name of the value field.
185  
186 done:
187 return($this->SUPER::initialize());
188 }
189 1;
190  
191 Then to use the object, your script merely does:
192  
193 use MyCompany::SalesPhoneHash;
194 my %PhoneNumbers = MyCompany::SalesPhoneHash->create_or_die();
195  
196 Of course, when instantiating a subclass, if you wish you can still
197 override any parameters you wish, as long as the initialize() method
198 in the subclass uses ||= rather than = to set defaults for any
199 unspecified parameters.
200  
201  
202 Multi-key mode:
203  
204 You may also use the "FixedKeys" parameter to specify a hash of some
205 additional key fields and their fixed values that must match exactly
206 for any records that are retrieved, deleted, or created by the tied
207 object, effectively allowing the hash to operate on only a subset of
208 the data in the database. This is typically helpful in a multi-keyed
209 table where, for the purposes of your script, all key values should be
210 fixed except one (and that one is the hash key).
211  
212 use DBIx::TableHash;
213 my $PhoneNumbers =
214 DBIx::TableHash->
215 create_or_die(
216 {Database => 'mydatabase',
217 TableName => 'SalesPeople',
218 KeyField => 'FullName',
219 ValueField => 'PhoneNumbers',
220 FixedKeys =>
221 {AreaCode => 415,
222 StatusCode => 'Active',
223 RecordType => 'Primary'}});
224  
225  
226 Multi-value mode:
227  
228 If instead of getting and setting a single value, you'd like to get or
229 set a hash of all fields in the record, simply don't specify
230 ValueField, and the object will use "multi-value" mode, where an
231 entire record, as a hash, is gotten or set on each fetch or store.
232 Feel free to combine this mode with multi-key mode.
233  
234 When storing a record in multi-value mode, if the record already
235 exists, only the specified fields are overwritten. If it did not
236 already exist, then only the specified fields will be written and the
237 others will be NULL or defaulted according to the table schema.
238  
239 When storing a record in multi-value mode, you can't change the values
240 of the primary key field or any other key field specified in FixedKeys
241 (if any), since that would mess up the whole point of this module
242 which is to leave the main key and fixed keys fixed while mucking with
243 the other values in the record. Any changed values in key fields are
244 simply ignored.
245  
246 use DBIx::TableHash;
247 my $SalesPeopleTable =
248 DBIx::TableHash->
249 create_or_die(
250 {Database => 'mydatabase',
251 TableName => 'SalesPeople',
252 KeyField => 'FullName'});
253  
254 my $SalesPersonFullName = "Joe Jones";
255 my $EntireRecord = $SalesPeopleTable->{$SalesPersonFullName};
256  
257 When fetching records in multi-value mode, you can limit the list of
258 returned fields to a subset of all available fields in case there
259 might be some very big ones that you don't want to waste bandwidth
260 getting. Just set the RetrieveFields parameter to an anonymous list
261 of the fields you care to retrieve. (This setting does not limit
262 the fields you can SET, just the ones that get retrieved.)
263  
264 use DBIx::TableHash;
265 my $SalesPeopleTable =
266 DBIx::TableHash->
267 create_or_die(
268 {Database => 'mydatabase',
269 TableName => 'SalesPeople',
270 KeyField => 'FullName',
271 RetrieveFields=> [qw(Territory Quota)]});
272  
273 Warning:
274  
275 In multi-value mode, you might expect that this:
276  
277 $Hash->{$MyKey}->{FieldX} = 'foo';
278  
279 would set the value of the FieldX field in the appropriate record in
280 the database. IT DOES NOT.
281  
282 This is because the anonymous hash returned by $Hash->{$MyKey} is not
283 in any way tied back to the database. You'd have to retrieve the
284 record hash, change any value in it, and then set $Hash->{$MyKey} back
285 to it.
286  
287 Making the above syntax work with a multi-valued tied hash to set a
288 value in the database is a possible future enhancement under
289 consideration by the author. Let me know if you would like to have
290 that work.
291  
292 In the meanwhile, here's how you do could do it:
293  
294  
295 (my $Record = $Hash->{$MyKey})->{FieldX} = 'foo';
296 $Hash->{$MyKey}->{FieldX} = $Record;
297  
298 WARNING: If you use the above approach to update a record in
299 multi-value mode, beware that there's potentially a race condition in
300 the above code if someone else updates the same record after you've
301 copied it but before you've modified and set it. So use this
302 technique with caution and understanding. If in doubt, don't use this
303 module and instead use an SQL query to update the record in a single
304 transaction. Only you know the usage patterns of your database, the
305 concurrency issues, and the criticality of errors.
306  
307  
308 Caching modes:
309  
310 The object has several ways it can cache data to help minimize the
311 number of SQL queries to the database, at the expense of potentially
312 dramatically increased memory usage. The following cache parameters
313 can be specified to enable caching:
314  
315 CacheMode => 'CacheBeforeIterate'
316  
317 CacheMode => 'CacheOneTime'
318  
319 To disable caching, specify:
320  
321 CacheMode => 'CacheNone'
322  
323 (You can also assign undef to CacheMode, but you'll get warnings.)
324  
325 Normally, every time you fetch a value from the hash, it makes an SQL
326 query to the database. This, of course, is the intended and normal
327 mode of operation.
328  
329 Unfortunately, in Perl, just calling values(%Hash) or each(%Hash) or
330 even copying the hash with {%Hash} results in a separate FETCH, and
331 consequently, a separate SQL query made by this module, for each item.
332 This could result in thousands of queries just to fetch all the values
333 from a thousand-item table in the database.
334  
335 However, often you want to iterate over all the elements of a hash
336 without it having to go back to the database and issue another query
337 for each item that you retrieve.
338  
339 Using the 'CacheBeforeIterate' mode, all keys and values are cached
340 upon each call to FIRSTKEYS (i.e. at the start of any iteration or
341 enumeration). Then, any subsequent calls to FETCH data from the hash
342 retrieve it from the cache instead of doing an SQL query. STORING or
343 DELETING any items from the hash results in them being stored and
344 deleted from both the database and the cache.
345  
346 Using the CacheOneTime mode, the full cache is built at object
347 instantiation and time never fully rebuilt. In fact, its contents
348 never change unless you make alterations by using it to store into
349 and/or delete from the database.
350  
351 CACHE WARNING: With both caching modes, of course, you must be
352 comfortable with the fact that the data being retrieved is a
353 "snapshot" of the database and consequently will not reflect updates
354 done by other parties during the lifetime of the object; it will only
355 reflect updates that you make by storing or deleting values from it.
356 If other people are using the database simultaneously, your cache and
357 the actual data could "drift" out of agreement. This is mainly
358 dangerous to you, not others, unless you then go make updates to the
359 data based on potentially outdated values.
360  
361  
362 All modes may be combined...
363  
364 All modes and parameters are orthogonal, so any combination of
365 parameters may be specified, with the exception that the
366 RetrieveFields parameter is only meaningful when ValueField is not
367 unspecified.
368  
369 With subclassing, you may create objects that pre-specify any
370 parameters, even those that affect the major modes of operation. For
371 example, you may combine the subclassing technique and the multi-key
372 mode to make an object that accesses only the appropriate subset of a
373 multi-keyed table without requiring any parameters to be supplied by
374 the caller.
375  
376  
377 Getting a COPY of the data instead of a tied hash:
378  
379 What if you just want a big copy -- a snapshot -- of the data in the
380 table, in a regular old hash that's no longer tied to the database at
381 all? (Memory constraints be damned!)
382  
383 Just use the create_copy() or create_copy_or_die() methods. They work
384 just like create() and create_or_die(), but instead of returning a
385 tied object, they just return a potentially huge hash containing a
386 copy of all the data.
387  
388 In other words:
389  
390 create_copy() is equivalent to: {%{create() || {} }}
391 create_copy_or_die() is equivalent to: {%{create_or_die()}}
392  
393 ... but the _copy methods are more efficient because internally, a
394 caching mode is used to minimize the queries to the database and
395 generate the hash as efficiently as possible.
396  
397 In all other respects, create_copy() and create_copy_or_die() perform
398 exactly like their non-copying namesakes, taking all the same
399 parameters, except CacheMode which is not relevant when making a
400 static copy.
401  
402 Please remember that the object returned by the _copy methods is no
403 longer tied to the database.
404  
405  
406 =head1 PARAMETER SUMMARY
407  
408 The full list of recognized parameters is:
409  
410 DBI Parameters
411  
412 Param Default Description
413 ------------------------------------------------------------------------
414 DBIDriver 'mysql' Name of DBI driver to try to use (only
415 mysql has currently been tested by the
416 author).
417  
418 HostName 'localhost' Host name containing the database and table;
419  
420 Port undef Port number if different from the standard.
421
422 Login '' Login to use when connecting, if any.
423  
424 Password '' Password to use when connecting, if any.
425  
426 SQL Parameters
427  
428 Param Default Description
429 ------------------------------------------------------------------------
430 Database '' Name of database to connect to.
431  
432 TableName '' Table to connect to.
433  
434 KeyField '' Name of field in which lookup key is found.
435  
436 ValueField '' Name of field to pull value from.
437 If empty or undef, then a
438 multi-value hash is used both for
439 saving and retrieving. This is
440 called "multi-value mode".
441  
442 Module Parameters
443  
444 Param Default Description
445 ------------------------------------------------------------------------
446 FixedKeys {} If supplied, gives names and
447 fixed, hardcoded values that other
448 keys in the table must have; this
449 effectively limits the scope of
450 the tied hash from operating over
451 the entire table to operating over
452 just the subset of records that
453 match the values in FixedKeys.
454 This is called "multi-key mode".
455  
456 RetrieveFields [] In multi-value mode, limits the
457 fields that are retrieved; default
458 is all fields in the record.
459  
460 =head1 SUPPORT
461  
462 I am unable to provide any technical support for this module. The
463 whole reason I had to make it was that I was way too busy (lazy?) to
464 write all that SQL code...
465  
466 But you are encouraged to send patches, bug warnings, updates, thanks,
467 or suggestions for improvements to the author as listed below.
468  
469 Just be aware that I may not have time to respond. Please be sure to
470 put the name of this module somewhere in the Subject line.
471  
472 The code is a pretty simple tied hash implementation, so you're on
473 your own to debug it. If you're having trouble debugging via the
474 "tie" interface, try instantiating an object directly (or retrieving
475 it when you tie (see perltie)) and calling its methods individually.
476 Use the debugger or Data::Dumper to dump intermediate values at key
477 points, or whatever it takes. Use your database server logs if you
478 want to see what SQL code is getting generated. Or contribute a
479 debugging mode to this module which prints out or logs the SQL
480 statements before executing them.
481  
482 =head1 BUGS/GOTCHAS
483  
484 Problem: If you iterate or enumerate the hash, all keys get pulled in
485 from the database and stay stored in memory for the lifetime of the
486 object. FIRSTKEY, which is called every time you do a keys(), each()
487 or any full iteration or enumeration over the tied hash (such as
488 copying it) retrieves and hangs on to a full list of all keys in
489 KeyField. If the keys are long or there are lots of them, this could
490 be a memory problem. (Don't confuse this with CacheMode in which BOTH
491 keys AND values are stored in memory.)
492  
493 Solutions:
494  
495 1) Don't iterate or enumerate. Just fetch and store.
496 2) Only iterate or enumerate on short tables.
497 3) LValue or RValue hash slices should be safe to do.
498  
499  
500 =head1 INSTALLATION
501  
502 Using CPAN module:
503  
504 perl -MCPAN -e 'install DBIx::TableHash'
505  
506 Or manually:
507  
508 tar xzvf DBIx-TableHash*gz
509 cd DBIx-TableHash-?.??
510 perl Makefile.PL
511 make
512 make test
513 make install
514  
515 =head1 SEE ALSO
516  
517 The DBIx::TableHash home page:
518  
519 http://christhorman.com/projects/perl/DBIx-TableHash/
520  
521 The implementation in TableHash.pm.
522  
523 The perlref and perltie manual pages.
524  
525 The mysql home page:
526  
527 http://mysql.com/
528  
529 =head1 THANKS
530  
531 Thanks to Mark Leighton Fisher <fisherm@tce.com> for providing a patch
532 to fix -w support (change in standard "none" setting of CacheMode from
533 undef to CacheNone).
534  
535 =head1 AUTHOR
536  
537 Chris Thorman <chthorman@cpan.org>
538  
539 Copyright (c) 1995-2002 Chris Thorman. All rights reserved.
540  
541 This program is free software; you can redistribute it and/or modify
542 it under the same terms as Perl itself.
543  
544 =cut
545  
546  
547 1 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
548 1 ▶use Carp;
Subroutine BEGIN: called CC=1 SCAR=0.0
549  
550 ## We require Data::Dumper not only because it's my favorite debugging
551 ## tool and nobody should be without it, but also because it's helpful
552 ## in generating error messages when lots of complex data structures
553 ## are being slung around.
554  
555 1 ▶use Data::Dumper; local $Data::Dumper::Deepcopy = 1;
Subroutine BEGIN: called CC=1 SCAR=0.0
556  
557  
558 ############ CLASS HELPER METHODS
559  
560 ### create
561 ### create_or_die
562  
563 ### These are class methods, provided for convenience, that take the
564 ### same parameters as new/tie, do does the tying for you and return
565 ### an anonymous hash. Why make the end-user do the messy tying?
566  
567 ### create carps on failure.
568 ### create_or_die croaks on failure.
569  
570 sub create
571 0 ▶{
Subroutine create: not called CC=4 SCAR=30.0
Pod undocumented
572 0 my $Class = shift;
573 0 my ($Params, $OrDie) = @_;
574
575 0 my $ThisHash = {};
576
577 0 ▶ &{$OrDie?\&confess:\&carp}("Failed to create a new $Class with params " . &Dumper($Params) ), goto done
Branch: 2 branches
BranchTrueFalseTotal
unless my $ThisObject = tie(%$ThisHash, $Class, $Params) 0 0 0
$OrDie ? : 0 0 0
578 unless
579 my $ThisObject = tie (%$ThisHash, $Class, $Params); ## Calls new()
580
581 ## Return undef if failure or the tied anonymous hash if success.
582 0 ▶ return($ThisObject && $ThisHash);
Condition0%0/3
$ThisObject && $ThisHash
!ll&&!rl&&r
000
MC/DC0%0/2
$ThisObject && $ThisHash
$ThisObject $ThisHash
Truth table0%0/3
A && B
A= $ThisObjectB= $ThisHash
ABresult
0X0
100
111
583 }
584  
585 sub create_or_die
586 0 ▶{
Subroutine create_or_die: not called CC=1 SCAR=6.9
Pod undocumented
587 0 my $Class = shift;
588 0 my ($Params) = @_;
589  
590 0 return($Class->create($Params, 'OrDie'));
591 }
592  
593 sub create_copy
594 0 ▶{
Subroutine create_copy: not called CC=3 SCAR=24.8
Pod undocumented
595 0 my $Class = shift;
596 0 my ($Params, $OrDie) = @_;
597  
598 ## Force the new object into "CacheOneTime" mode, which caches all
599 ## data immediately.
600  
601 0 ▶ my $TiedHash = $Class->create({%{$Params || {}}, CacheMode => 'CacheOneTime'}, $OrDie);
Condition0%0/2
$Params || {}
l!l
00
MC/DC0%0/1
$Params || {}
$Params
Truth table0%0/2
A
A= $Params
Aresult
11
00
602  
603 ## Then return the cached hash and abandon the original object.
604 0 ▶ return($TiedHash && (tied(%$TiedHash))->{_CacheHash});
Condition0%0/3
$TiedHash && tied(%$TiedHash)->{'_CacheHash'}
!ll&&!rl&&r
000
MC/DC0%0/2
$TiedHash && tied(%$TiedHash)->{'_CacheHash'}
$TiedHash tied(%$TiedHash)->{'_CacheHash'}
Truth table0%0/3
A && B
A= $TiedHashB= tied(%$TiedHash)->{'_CacheHash'}
ABresult
0X0
100
111
605 }
606  
607 sub create_copy_or_die
608 0 ▶{
Subroutine create_copy_or_die: not called CC=1 SCAR=6.9
Pod undocumented
609 0 my $Class = shift;
610 0 my ($Params) = @_;
611  
612 0 return($Class->create_copy($Params, 'OrDie'));
613 }
614  
615  
616 ############### CONSTRUCTOR
617  
618 ### new
619  
620 ### Probably you won't need to override this when subclassing.
621 ### Instead, override the initialize() method.
622  
623 sub new
624 0 ▶{
Subroutine new: not called CC=5 SCAR=34.0
Pod undocumented
625 ## First arg to new is always either class name or a template
626 ## object. This allows $obj->new() or CLASS->new().
627  
628 ## Single additional argument to new is an optional anonymous hash
629 ## of parameters. See the initialize method, below, for a list of
630 ## parameters that can be passed (and will be defaulted for you if
631 ## not passed).
632  
633 0 my $ClassOrObj = shift;
634 0 my ($Params) = @_;
635  
636 0 ▶ my $class = ref($ClassOrObj) || $ClassOrObj;
Condition0%0/3
ref $ClassOrObj || $ClassOrObj
l!l&&r!l&&!r
000
MC/DC0%0/2
ref $ClassOrObj || $ClassOrObj
ref $ClassOrObj $ClassOrObj
Truth table0%0/3
A || B
A= ref $ClassOrObjB= $ClassOrObj
ABresult
1X1
011
000
637  
638 ## Shallow-copy all params from template object and/or optional
639 ## $Params hash into new hash.
640  
641 0 ▶ my $this = {%{(ref($ClassOrObj) ? $ClassOrObj : {})},
Branch: 1 branch
BranchTrueFalseTotal
ref $ClassOrObj ? : 0 0 0
642 0 ▶ %{(ref($Params ) eq 'HASH' ? $Params : {})}};
Branch: 1 branch
BranchTrueFalseTotal
ref $Params eq 'HASH' ? : 0 0 0
643  
644 0 bless $this, $class;
645 0 ▶ return ($this->initialize() ? $this : undef);
Branch: 1 branch
BranchTrueFalseTotal
$this->initialize ? : 0 0 0
646 }
647  
648 ### initialize
649  
650 ### This method defaults any public and/or internal parameters, does
651 ### some precalculations, and otherwise initializes the object. If
652 ### you override this method, please remember to call
653 ### $this->SUPER::initialize() when you're finished with your own
654 ### initializations.
655  
656 sub initialize
657 0 ▶{
Subroutine initialize: not called CC=25 SCAR=64.8
Pod undocumented
658 0 my $this = shift;
659 0 my $Class = ref($this);
660  
661 0 my $Success;
662  
663 ## These parameters are used by various routines; the defaults
664 ## here are frequently overridden by instantiation or by
665 ## subclasses (either by passing a hash of params in the new()
666 ## method, or by overriding this initialize method in a subclass
667 ## and setting or defaulting some or all of them before running
668 ## the code below by calling SUPER::initialize().
669  
670 ## Make the most reasonable defaults for all parameters.
671  
672 0 ▶ $this->{DBIDriver} ||= 'mysql'; ## Name of DBI driver to try to use
Condition0%0/2
$this->{"DBIDriver"} ||= "mysql"
l!l
00
MC/DC0%0/1
$this->{"DBIDriver"} ||= "mysql"
$this->{"DBIDriver"}
Truth table0%0/2
A
A= $this->{"DBIDriver"}
Aresult
11
00
673 ## (only mysql is currently tested or supported).
674  
675 0 ▶ $this->{HostName} ||= 'localhost'; ## Host name containing the database and table;
Condition0%0/2
$this->{"HostName"} ||= "localhost"
l!l
00
MC/DC0%0/1
$this->{"HostName"} ||= "localhost"
$this->{"HostName"}
Truth table0%0/2
A
A= $this->{"HostName"}
Aresult
11
00
676 0 ▶ $this->{Port} ||= undef; ## Port number if different from the standard.
Condition0%0/2
$this->{"Port"} ||= undef
l!l
00
MC/DC0%0/1
$this->{"Port"} ||= undef
$this->{"Port"}
Truth table0%0/2
A
A= $this->{"Port"}
Aresult
11
00
677  
678 0 ▶ $this->{Database} ||= ''; ## Name of database to connect to.
Condition0%0/2
$this->{"Database"} ||= ""
l!l
00
MC/DC0%0/1
$this->{"Database"} ||= ""
$this->{"Database"}
Truth table0%0/2
A
A= $this->{"Database"}
Aresult
11
00
679 0 ▶ $this->{Login} ||= ''; ## Login to use when connecting, if any.
Condition0%0/2
$this->{"Login"} ||= ""
l!l
00
MC/DC0%0/1
$this->{"Login"} ||= ""
$this->{"Login"}
Truth table0%0/2
A
A= $this->{"Login"}
Aresult
11
00
680 0 ▶ $this->{Password} ||= ''; ## Password to use when connecting, if any.
Condition0%0/2
$this->{"Password"} ||= ""
l!l
00
MC/DC0%0/1
$this->{"Password"} ||= ""
$this->{"Password"}
Truth table0%0/2
A
A= $this->{"Password"}
Aresult
11
00
681  
682 0 ▶ $this->{TableName} ||= ''; ## Table to connect to.
Condition0%0/2
$this->{"TableName"} ||= ""
l!l
00
MC/DC0%0/1
$this->{"TableName"} ||= ""
$this->{"TableName"}
Truth table0%0/2
A
A= $this->{"TableName"}
Aresult
11
00
683  
684 0 ▶ $this->{KeyField} ||= ''; ## Name of field in which lookup keys are found.
Condition0%0/2
$this->{"KeyField"} ||= ""
l!l
00
MC/DC0%0/1
$this->{"KeyField"} ||= ""
$this->{"KeyField"}
Truth table0%0/2
A
A= $this->{"KeyField"}
Aresult
11
00
685 0 ▶ $this->{ValueField} ||= ''; ## Name of field in which value keys are found.
Condition0%0/2
$this->{"ValueField"} ||= ""
l!l
00
MC/DC0%0/1
$this->{"ValueField"} ||= ""
$this->{"ValueField"}
Truth table0%0/2
A
A= $this->{"ValueField"}
Aresult
11
00
686  
687 0 ▶ $this->{CacheMode} ||= "CacheNone"; ## Cache mode; none by default.
Condition0%0/2
$this->{"CacheMode"} ||= "CacheNone"
l!l
00
MC/DC0%0/1
$this->{"CacheMode"} ||= "CacheNone"
$this->{"CacheMode"}
Truth table0%0/2
A
A= $this->{"CacheMode"}
Aresult
11
00
688  
689 0 ▶ $this->{FixedKeys} = {} unless ref($this->{FixedKeys} ) eq 'HASH'; ## Ensure a hash ref
Branch: 1 branch
BranchTrueFalseTotal
unless ref $this->{"FixedKeys"} eq "HASH" 0 0 0
690 0 ▶ $this->{RetrieveFields} = [] unless ref($this->{RetrieveFields}) eq 'ARRAY'; ## Ensure an array ref
Branch: 1 branch
BranchTrueFalseTotal
unless ref $this->{"RetrieveFields"} eq "ARRAY" 0 0 0
691  
692 ## This module can't do anything unless at least these three
693 ## fields have been specified by this point:
694  
695 0 ▶ carp("Must specify Database to connect to for $Class"), goto done unless $this->{Database};
Branch: 1 branch
BranchTrueFalseTotal
unless $this->{"Database"} 0 0 0
696 0 ▶ carp("Must specify TableName for $Class, database: $this->{Database}"), goto done unless $this->{TableName};
Branch: 1 branch
BranchTrueFalseTotal
unless $this->{"TableName"} 0 0 0
697 0 ▶ carp("Must specify KeyField for $Class / table: $this->{TableName}"), goto done unless $this->{KeyField};
Branch: 1 branch
BranchTrueFalseTotal
unless $this->{"KeyField"} 0 0 0
698  
699  
700 ## Make sure that all table names and field names seem to be valid
701 ## and not attempts to screw with our SQL statements. (I.e. they
702 ## must consist only of word characters.)
703  
704 0 ▶ foreach (
Branch: 1 branch
BranchTrueFalseTotal
$this->{'ValueField'} ? : 0 0 0
705 $this->{TableName},
706 $this->{KeyField},
707 ($this->{ValueField} ? $this->{ValueField} : ()),
708 0 keys %{$this->{FixedKeys}},
709 0 @{$this->{RetrieveFields}},
710 )
711 {
712 0 ▶ carp("Invalid table or field name: $_"), goto done unless /^\w+$/;
Branch: 1 branch
BranchTrueFalseTotal
unless /^\w+$/ 0 0 0
713 }
714
715 ## Connect to the database or fail trying; initializes $this->{_dbh};
716 0 ▶ goto done unless $this->Connect();
Branch: 1 branch
BranchTrueFalseTotal
unless $this->Connect 0 0 0
717  
718 0 my $dbh = $this->{_dbh};
719  
720 ## Calculate the "where clause" and lists of keys and values
721 ## needed by the SQL statements to support the FixedKeys featue.
722  
723 $this->{FixedKeyValsWhereClause} =
724 0 (keys %{$this->{FixedKeys}}
725 ? join(" AND ", ('', (map
726 0 {"$_ = " . $dbh->quote($this->{FixedKeys}->{$_})}
727 0 ▶ keys %{$this->{FixedKeys}})))
Branch: 1 branch
BranchTrueFalseTotal
keys %{$$this{'FixedKeys'};} ? : 0 0 0
728 : '');
729
730 $this->{FixedKeyValsKeyList } =
731 0 (keys %{$this->{FixedKeys}}
732 0 ▶ ? join(", ", ('', ( keys %{$this->{FixedKeys}})))
Branch: 1 branch
BranchTrueFalseTotal
keys %{$$this{'FixedKeys'};} ? : 0 0 0
733 : '');
734
735 $this->{FixedKeyValsValueList} =
736 0 (keys %{$this->{FixedKeys}}
737 0 ▶ ? join(", ", ('', (map {$dbh->quote($_)} values %{$this->{FixedKeys}})))
Branch: 1 branch
BranchTrueFalseTotal
keys %{$$this{'FixedKeys'};} ? : 0 0 0
738 : '');
739
740 ## Calculate a list of fields to be returned if in multi-value
741 ## mode. This list is either specified in the RetrieveFields
742 ## parameter, or we use "*", meaning all fields, including keys.
743  
744 $this->{RetrieveMultiFieldList} =
745 0 (@{$this->{RetrieveFields}}
746 0 ▶ ? join(", ", @{$this->{RetrieveFields}})
Branch: 1 branch
BranchTrueFalseTotal
@{$this->{'RetrieveFields'};} ? : 0 0 0
747 : '*');
748  
749 ## If we're in CacheOneTime mode, we cache all the data now by
750 ## calling FIRSTKEY; in this mode it will never be cached or
751 ## retrieved again unless a STORE is done (and then only that
752 ## record).
753  
754 0 ▶ $this->FIRSTKEY() if ($this->{CacheMode} eq 'CacheOneTime');
Branch: 1 branch
BranchTrueFalseTotal
if $this->{"CacheMode"} eq "CacheOneTime" 0 0 0
755  
756 0 $Success = 1;
757
758 0 done:
759 return($Success);
760 }
761  
762 ########## HASH METHODS... these allow this object to be tied to a hash
763  
764 sub TIEHASH
765 0 ▶{
Subroutine TIEHASH: not called CC=1 SCAR=6.9
766 0 my $self = shift;
767 0 return($self->new(@_));
768 }
769  
770 ### FETCH
771  
772 ### Normal mode of operation is to fetch a single value from the hash.
773  
774 ### $All mode is for internal use when caching and means to get all
775 ### values (for all keys) into an anonymous list.
776  
777 ### $NoCache mode is for internal use by STORE when it FETCHes values
778 ### back from the database in order to replenish any cached values for
779 ### records it has just modified.
780  
781 sub FETCH
782 0 ▶{
Subroutine FETCH: not called CC=15 SCAR=54.8
783 0 my $this = shift;
784 0 my ($KeyName, $All, $NoCache) = @_;
785  
786 0 my $dbh = $this->{_dbh};
787 0 my $sth;
788
789 my $ReturnValue;
790  
791 ## On a regular query, look in the cache before trying the query.
792 0 ▶ if ($this->{_CacheHash} && !$All && !$NoCache)
Branch: 1 branch
BranchTrueFalseTotal
if ($this->{"_CacheHash"} and not $All and not $NoCache) 0 0 0
Condition0%0/6
$this->{"_CacheHash"} and not $All and not $NoCache
!ll&&!rl&&r
000
$this->{"_CacheHash"} and not $All
!ll&&!rl&&r
000
MC/DC0%0/3
$this->{"_CacheHash"} and not $All and not $NoCache
$this->{"_CacheHash"} not $All not $NoCache
Truth table0%0/4
A and not(B) and not(C)
A= $this->{"_CacheHash"}B= not $AllC= not $NoCache
ABCresult
0XX0
10X0
1100
1111
793 {
794 0 $ReturnValue = $this->{_CacheHash}->{$KeyName};
795 0 goto done;
796 }
797  
798 ## Ask for one or all entries...
799  
800 0 ▶ my $WhereClause = ($All ? "1" : "$this->{KeyField} = '$KeyName'");
Branch: 1 branch
BranchTrueFalseTotal
$All ? : 0 0 0
801  
802 0 ▶ my $SelectClause = $this->{ValueField} || $this->{RetrieveMultiFieldList} || '*';
Condition0%0/5
$this->{'ValueField'} || $this->{'RetrieveMultiFieldList'} || '*'
l!l
00
$this->{"ValueField"} or $this->{"RetrieveMultiFieldList"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$this->{'ValueField'} || $this->{'RetrieveMultiFieldList'} || '*'
$this->{"ValueField"} $this->{"RetrieveMultiFieldList"}
Truth table0%0/3
A or B
A= $this->{"ValueField"}B= $this->{"RetrieveMultiFieldList"}
ABresult
1X1
011
000
803
804 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) searching for entry \"$KeyName\" in table $this->{TableName}"),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare(qq[SELECT $SelectClause FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $WhereClause $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}]) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare(qq[SELECT $SelectClause FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $WhereClause $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}]) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare(qq[SELECT $SelectClause FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $WhereClause $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}]) and $sth->execute
$sth = $dbh->prepare(qq[SELECT $SelectClause FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $WhereClause $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}]) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare(qq[SELECT $SelectClause FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $WhereClause $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}])B= $sth->execute
ABresult
0X0
100
111
805 goto done
806 unless
807 (($sth = $dbh->prepare(qq{SELECT $SelectClause FROM $this->{TableName}
808 WHERE $WhereClause $this->{FixedKeyValsWhereClause} ORDER BY $this->{KeyField}})) &&
809 $sth->execute());
810  
811 ## Retrieve one or all of the rows, as needed.
812  
813 0 my $RowValues = [];
814 0 ▶ while ($All || (@$RowValues < 1))
Branch: 1 branch
BranchTrueFalseTotal
if ($All or @$RowValues < 1) 0 0 0
Condition0%0/3
$All or @$RowValues < 1
l!l&&r!l&&!r
000
MC/DC0%0/2
$All or @$RowValues < 1
$All @$RowValues < 1
Truth table0%0/3
A or B
A= $AllB= @$RowValues < 1
ABresult
1X1
011
000
815 {
816 0 my ($ResultHash) = ($sth->fetchrow_hashref());
817
818 0 ▶ last unless $ResultHash; ## If in all mode, we stop the loop
Branch: 1 branch
BranchTrueFalseTotal
unless $ResultHash 0 0 0
819  
820 0 $this->FixUpRetrieval($ResultHash);
821
822 ## Return either a single value, or a hash of all values if we're
823 ## in multi-value mode.
824
825 my $ThisValue = ($this->{ValueField}
826 ? $ResultHash->{$this->{ValueField}}
827 0 ▶ : $ResultHash);
Branch: 1 branch
BranchTrueFalseTotal
$this->{'ValueField'} ? : 0 0 0
828
829 0 push @$RowValues, $ThisValue;
830 }
831  
832 ## Return the first and only row retrieved, unless in All mode, in
833 ## which case return an array of all rows retrieved.
834  
835 0 ▶ $ReturnValue = ($All ? $RowValues : $RowValues->[0]);
Branch: 1 branch
BranchTrueFalseTotal
$All ? : 0 0 0
836  
837 0 ▶ done:
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
838 $sth->finish() if $sth;
839 0 return($ReturnValue);
840 }
841  
842 sub STORE
843 0 ▶{
Subroutine STORE: not called CC=15 SCAR=54.8
844 0 my $this = shift;
845 0 my ($KeyName, $Value) = @_;
846 0 my $Success = 0;
847  
848 0 my $dbh = $this->{_dbh};
849 0 my $sth;
850 0 my $TablesLocked = 0;
851
852 ## Prepare a hash mapping field names to values to be stored.
853 0 my ($ValuesHash) = $this->PrepToStore($Value);
854  
855 ## Optimization: if we're called in multi-key mode and asked to
856 ## store an empty record, we don't. This would typically happen
857 ## if someone tried to dereference a hashref from a failed lookup;
858 ## perl tries to make it spring into existence by storing an empty
859 ## value there, and we don't need to do that.
860  
861 0 ▶ goto done if ((!$this->{ValueField}) && !keys(%$ValuesHash));
Branch: 1 branch
BranchTrueFalseTotal
unless $this->{"ValueField"} or keys %$ValuesHash 0 0 0
Condition0%0/3
$this->{"ValueField"} or keys %$ValuesHash
l!l&&r!l&&!r
000
MC/DC0%0/2
$this->{"ValueField"} or keys %$ValuesHash
$this->{"ValueField"} keys %$ValuesHash
Truth table0%0/3
A or B
A= $this->{"ValueField"}B= keys %$ValuesHash
ABresult
1X1
011
000
862  
863 ## If there are still no non-key fields to store, we carp and
864 ## refuse.
865  
866 0 ▶ carp("No non-key fields supplied to store: " . &Dumper($Value)), goto done unless keys(%$ValuesHash);
Branch: 1 branch
BranchTrueFalseTotal
unless keys %$ValuesHash 0 0 0
867  
868  
869 ## Lock the tables so nobody messes with our update.
870 0 ▶ carp("Failed ($dbh->errstr) while locking $this->{TableName} table " .
Branch: 1 branch
BranchTrueFalseTotal
unless $dbh->do(qq[LOCK TABLES $this->{"TableName"} WRITE]) 0 0 0
871 "creating index entry for $KeyName"), goto done
872 unless
873 $dbh->do(qq{LOCK TABLES $this->{TableName} WRITE});
874 0 $TablesLocked = 1;
875
876 ## First see whether this index entry already exists.
877 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) searching for entry \"$KeyName\""),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
$sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}])B= $sth->execute
ABresult
0X0
100
111
878 goto done
879 unless
880 (($sth = $dbh->prepare(qq{SELECT $this->{KeyField} FROM $this->{TableName}
881 WHERE $this->{KeyField} = '$KeyName' $this->{FixedKeyValsWhereClause}})) &&
882 $sth->execute());
883 0 my ($Existing) = $sth->fetchrow_array();
884
885  
886 ## Then, either insert or update as appropriate.
887 0 ▶ if ($Existing)
Branch: 1 branch
BranchTrueFalseTotal
if ($Existing) { } 0 0 0
888 {
889 ## Exists, so replace it.
890  
891 0 my $UpdateFieldsEqualValues = join(", ", map {"$_ = " . $dbh->quote($ValuesHash->{$_})} keys %$ValuesHash);
892  
893 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) updating existing $this->{TableName} entry for key \"$KeyName\""), goto done
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare(qq[UPDATE $this->{"TableName"} SET $UpdateFieldsEqualValues \n\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare(qq[UPDATE $this->{"TableName"} SET $UpdateFieldsEqualValues \n\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare(qq[UPDATE $this->{"TableName"} SET $UpdateFieldsEqualValues \n\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
$sth = $dbh->prepare(qq[UPDATE $this->{"TableName"} SET $UpdateFieldsEqualValues \n\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare(qq[UPDATE $this->{"TableName"} SET $UpdateFieldsEqualValues \n\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}])B= $sth->execute
ABresult
0X0
100
111
894 unless
895 (($sth = $dbh->prepare
896 (qq{UPDATE $this->{TableName} SET $UpdateFieldsEqualValues
897 WHERE $this->{KeyField} = '$KeyName' $this->{FixedKeyValsWhereClause}})) &&
898 $sth->execute());
899 }
900 else
901 {
902 ## Does not exist, so insert it.
903  
904 0 my $InsertFieldNames = join(", ", keys %$ValuesHash);
905 0 my $InsertValueNames = join(", ", map {$dbh->quote($ValuesHash->{$_})} keys %$ValuesHash);
906 0 my $Length = length ($InsertValueNames);
907  
908 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) creating new \"$KeyName\" entry (length < $Length)) for table $this->{TableName}"),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare(qq[INSERT INTO $this->{"TableName"} ($this->{"KeyField"}, $InsertFieldNames $this->{"FixedKeyValsKeyList"}) \n\t\t\t\t\t\t\t\t\t\t\t VALUES ('${KeyName}', $InsertValueNames $this->{"FixedKeyValsValueList"})]) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare(qq[INSERT INTO $this->{"TableName"} ($this->{"KeyField"}, $InsertFieldNames $this->{"FixedKeyValsKeyList"}) \n\t\t\t\t\t\t\t\t\t\t\t VALUES ('${KeyName}', $InsertValueNames $this->{"FixedKeyValsValueList"})]) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare(qq[INSERT INTO $this->{"TableName"} ($this->{"KeyField"}, $InsertFieldNames $this->{"FixedKeyValsKeyList"}) \n\t\t\t\t\t\t\t\t\t\t\t VALUES ('${KeyName}', $InsertValueNames $this->{"FixedKeyValsValueList"})]) and $sth->execute
$sth = $dbh->prepare(qq[INSERT INTO $this->{"TableName"} ($this->{"KeyField"}, $InsertFieldNames $this->{"FixedKeyValsKeyList"}) \n\t\t\t\t\t\t\t\t\t\t\t VALUES ('${KeyName}', $InsertValueNames $this->{"FixedKeyValsValueList"})]) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare(qq[INSERT INTO $this->{"TableName"} ($this->{"KeyField"}, $InsertFieldNames $this->{"FixedKeyValsKeyList"}) \n\t\t\t\t\t\t\t\t\t\t\t VALUES ('${KeyName}', $InsertValueNames $this->{"FixedKeyValsValueList"})])B= $sth->execute
ABresult
0X0
100
111
909 goto done
910 unless
911 (($sth = $dbh->prepare(qq{INSERT INTO $this->{TableName} ($this->{KeyField}, $InsertFieldNames $this->{FixedKeyValsKeyList})
912 VALUES ('$KeyName', $InsertValueNames $this->{FixedKeyValsValueList})})) &&
913 $sth->execute());
914 }
915  
916 ## Now fetch the value back out of the database into the cache, if appropriate.
917 0 ▶ $this->{_CacheHash}->{$KeyName} = $this->FETCH($KeyName, !'All', 'NoCache') if $this->{_CacheHash};
Branch: 1 branch
BranchTrueFalseTotal
if $this->{"_CacheHash"} 0 0 0
918  
919 0 $Success = 1;
920  
921 0 ▶ done:
Branch: 1 branch
BranchTrueFalseTotal
if $TablesLocked 0 0 0
922 $dbh->do(qq{UNLOCK TABLES}) if $TablesLocked;
923 0 ▶ $sth->finish() if $sth;
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
924 0 return($Success);
925 }
926  
927 sub EXISTS
928 0 ▶{
Subroutine EXISTS: not called CC=5 SCAR=34.0
929 0 my $this = shift;
930 0 my ($KeyName) = @_;
931  
932 0 my $dbh = $this->{_dbh};
933 0 my $sth;
934
935 ## Check the cache if available.
936 0 ▶ return(exists($this->{_CacheHash}->{$KeyName})) if $this->{_CacheHash};
Branch: 1 branch
BranchTrueFalseTotal
if $this->{"_CacheHash"} 0 0 0
937  
938 ## First see whether this index entry already exists.
939  
940 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) searching for entry \"$KeyName\""),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
$sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}])B= $sth->execute
ABresult
0X0
100
111
941 goto done
942 unless
943 (($sth = $dbh->prepare(qq{SELECT $this->{KeyField} FROM $this->{TableName}
944 WHERE $this->{KeyField} = '$KeyName' $this->{FixedKeyValsWhereClause}})) &&
945 $sth->execute());
946 0 my ($Result) = $sth->fetchrow_array();
947
948 0 ▶ done:
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
949 $sth->finish() if $sth;
950 0 return(!!$Result);
951 }
952  
953 sub DELETE
954 0 ▶{
Subroutine DELETE: not called CC=5 SCAR=34.0
955 0 my $this = shift;
956 0 my ($KeyName) = @_;
957  
958 0 my $dbh = $this->{_dbh};
959 0 my $sth;
960  
961 ## First retrieve any existing entry so it can be returned to the
962 ## caller before being deleted.
963  
964 0 my $DeletedVal = $this->FETCH($KeyName);
965  
966 ## Delete from the database
967 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) deleting entry \"$KeyName\" from $this->{TableName}"),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
$sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}]) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE $this->{"KeyField"} = '${KeyName}' $this->{"FixedKeyValsWhereClause"}])B= $sth->execute
ABresult
0X0
100
111
968 pgoto done
969 unless
970 (($sth = $dbh->prepare(qq{DELETE FROM $this->{TableName}
971 WHERE $this->{KeyField} = '$KeyName' $this->{FixedKeyValsWhereClause}})) &&
972 $sth->execute());
973
974 ## Delete from the cache
975 0 ▶ delete $this->{_CacheHash}->{$KeyName} if $this->{_CacheHash};
Branch: 1 branch
BranchTrueFalseTotal
if $this->{"_CacheHash"} 0 0 0
976  
977 0 ▶ done:
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
978 $sth->finish() if $sth;
979 0 return($DeletedVal);
980 }
981  
982 sub CLEAR
983 0 ▶{
Subroutine CLEAR: not called CC=4 SCAR=30.0
984 0 my $this = shift;
985  
986 0 my $dbh = $this->{_dbh};
987 0 my $sth;
988
989 0 $this->{_CacheHash} = undef;
990
991 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) clearing all from \"$this->{TableName}\""),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"}]) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"}]) and $sth->execute
$sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"}]) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare(qq[DELETE FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"}])B= $sth->execute
ABresult
0X0
100
111
992 goto done
993 unless
994 (($sth = $dbh->prepare(qq{DELETE FROM $this->{TableName}
995 WHERE 1 $this->{FixedKeyValsWhereClause}})) &&
996 $sth->execute());
997
998 0 ▶ done:
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
999 $sth->finish() if $sth;
1000 }
1001  
1002 ### FIRSTKEY
1003  
1004 ### Gets and hangs on to a full list of all keys in KeyField. If
1005 ### they're long or there are lots of them, this could be a problem.
1006  
1007 ### To Do: Consider in the future only updating the query to get the
1008 ### {_Keys} list on FIRSTKEY only if the mod date of the table has not
1009 ### changed since the Keys were last gotten. (Can you check the mod
1010 ### date of a table in msyql? Don't think so, but if you could....)
1011  
1012 sub FIRSTKEY
1013 0 ▶{
Subroutine FIRSTKEY: not called CC=12 SCAR=50.5
1014 0 my $this = shift;
1015  
1016 0 my $dbh = $this->{_dbh};
1017 0 my $sth;
1018
1019 0 my $TablesLocked = 0;
1020  
1021 0 $this->{_KeyNum} = 0; ## Reset the key counter to zero.
1022  
1023 ## If we're in CacheOneTime mode, we're done. We never
1024 ## recalculate the keys or the values using a database query.
1025  
1026 0 ▶ goto done if ($this->{_CacheHash} && ($this->{CacheMode} eq 'CacheOneTime'));
Branch: 1 branch
BranchTrueFalseTotal
if $this->{"_CacheHash"} and $this->{"CacheMode"} eq "CacheOneTime" 0 0 0
Condition0%0/3
$this->{"_CacheHash"} and $this->{"CacheMode"} eq "CacheOneTime"
!ll&&!rl&&r
000
MC/DC0%0/2
$this->{"_CacheHash"} and $this->{"CacheMode"} eq "CacheOneTime"
$this->{"_CacheHash"} $this->{"CacheMode"} eq "CacheOneTime"
Truth table0%0/3
A and B
A= $this->{"_CacheHash"}B= $this->{"CacheMode"} eq "CacheOneTime"
ABresult
0X0
100
111
1027  
1028  
1029 ## If in (any of the) cache mode(s), we need to lock for read so
1030 ## the query we do here to get the keys matches the big FETCH
1031 ## we're about to do to get the corresponding values. Sure
1032 ## wouldn't want them to stop corresponding, or our cache would be
1033 ## full of junk.
1034  
1035 0 ▶ if (defined($this->{CacheMode}) && $this->{CacheMode} ne "CacheNone")
Branch: 1 branch
BranchTrueFalseTotal
if (defined $this->{"CacheMode"} and $this->{"CacheMode"} ne "CacheNone") 0 0 0
Condition0%0/3
defined $this->{"CacheMode"} and $this->{"CacheMode"} ne "CacheNone"
!ll&&!rl&&r
000
MC/DC0%0/2
defined $this->{"CacheMode"} and $this->{"CacheMode"} ne "CacheNone"
defined $this->{"CacheMode"} $this->{"CacheMode"} ne "CacheNone"
Truth table0%0/3
A and B
A= defined $this->{"CacheMode"}B= $this->{"CacheMode"} ne "CacheNone"
ABresult
0X0
100
111
1036 {
1037 0 ▶ carp("Failed ($dbh->errstr) while locking $this->{TableName} table for caching"),
Branch: 1 branch
BranchTrueFalseTotal
unless $dbh->do(qq[LOCK TABLES $this->{"TableName"} READ]) 0 0 0
1038 goto done
1039 unless
1040 $dbh->do(qq{LOCK TABLES $this->{TableName} READ});
1041 0 $TablesLocked = 1;
1042 }
1043  
1044 ## Ready to get all the keys. Empty out the list and then get it from the database.
1045  
1046 0 $this->{_Keys} = [];
1047  
1048 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) searching for first entry"),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}]) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}]) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}]) and $sth->execute
$sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}]) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare(qq[SELECT $this->{"KeyField"} FROM $this->{"TableName"} \n\t\t\t\t\t\t\t\t\t WHERE 1 $this->{"FixedKeyValsWhereClause"} ORDER BY $this->{"KeyField"}])B= $sth->execute
ABresult
0X0
100
111
1049 goto done
1050 unless
1051 (($sth = $dbh->prepare(qq{SELECT $this->{KeyField} FROM $this->{TableName}
1052 WHERE 1 $this->{FixedKeyValsWhereClause} ORDER BY $this->{KeyField}})) &&
1053 $sth->execute());
1054 0 $this->{_Keys} = [map {($sth->fetchrow_array())[0]} (1..$sth->rows())];
1055
1056 ## If in (any of the) cache mode(s), we now need to cache all
1057 ## values as well as all keys. We do this by calling the standard
1058 ## FETCH method, but in our special "All" mode. It retrieves all
1059 ## values at once with just a single query and returns them in a
1060 ## list whose elements can be indexed by _KeyNum, just as the
1061 ## _Keys list is. Then we make a _CacheHash object mapping all
1062 ## the keys in _Keys to the retrieved values.
1063  
1064 0 ▶ if (defined($this->{CacheMode}) && $this->{CacheMode} ne "CacheNone")
Branch: 1 branch
BranchTrueFalseTotal
if (defined $this->{"CacheMode"} and $this->{"CacheMode"} ne "CacheNone") 0 0 0
Condition0%0/3
defined $this->{"CacheMode"} and $this->{"CacheMode"} ne "CacheNone"
!ll&&!rl&&r
000
MC/DC0%0/2
defined $this->{"CacheMode"} and $this->{"CacheMode"} ne "CacheNone"
defined $this->{"CacheMode"} $this->{"CacheMode"} ne "CacheNone"
Truth table0%0/3
A and B
A= defined $this->{"CacheMode"}B= $this->{"CacheMode"} ne "CacheNone"
ABresult
0X0
100
111
1065 {
1066 0 $this->{_CacheHash} = {};
1067 0 @{$this->{_CacheHash}}{@{$this->{_Keys}}} = @{$this->FETCH(undef, 'All')};
1068 }
1069  
1070 done:
1071 0 ▶ $dbh->do(qq{UNLOCK TABLES}) if $TablesLocked;
Branch: 1 branch
BranchTrueFalseTotal
if $TablesLocked 0 0 0
1072 0 ▶ $sth->finish() if $sth;
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
1073 0 return($this->{_Keys}->[$this->{_KeyNum}]);
1074 }
1075  
1076 sub NEXTKEY
1077 0 ▶{
Subroutine NEXTKEY: not called CC=1 SCAR=6.9
1078 0 my $this = shift;
1079 0 return($this->{_Keys}->[++$this->{_KeyNum}]);
1080 }
1081  
1082 sub DESTROY
1083 0 ▶{
Subroutine DESTROY: not called CC=1 SCAR=6.9
1084 0 my $this = shift;
1085  
1086 0 done:
1087 $this->Disconnect();
1088 }
1089  
1090 ############ OTHER INTERNAL, OVERRIDABLE METHODS...
1091  
1092  
1093 ### PrepToStore
1094  
1095 ### Constructs a hash mapping field name to Value for all values that
1096 ### can/should be stored in response to a storage request.
1097  
1098 ### This method could be subclassed if the values are to be somehow
1099 ### encoded or otherwise manipulated before storage (e.g. serialized,
1100 ### encrypted, etc), in which case FixUpRetrieval should also be
1101 ### subclassed.
1102  
1103 sub PrepToStore
1104 0 ▶{
Subroutine PrepToStore: not called CC=3 SCAR=24.8
Pod undocumented
1105 0 my $this = shift;
1106 0 my ($Value) = @_;
1107  
1108 0 my $dbh = $this->{_dbh};
1109
1110 ## In single-value mode, we're just storing one value, $Value, into one field.
1111  
1112 ## In multi-value mode, we're presumably given a hash of key-value
1113 ## pairs. We COPY it so we can non-destructively delete any key
1114 ## fields for safety before trying to store.
1115  
1116 my $ValuesHash = ($this->{ValueField}
1117 0 ▶ ? {$this->{ValueField} => $Value}
Branch: 2 branches
BranchTrueFalseTotal
$this->{'ValueField'} ? : 0 0 0
ref $Value eq 'HASH' ? : 0 0 0
1118 : ((ref($Value) eq 'HASH')
1119 ? {%$Value}
1120 : {})); ## Should not happen; return empty hash for safety.
1121
1122 ## Remove any key fields; don't want to be setting those.
1123 0 delete @$ValuesHash{$this->{KeyField}, keys %{$this->{FixedKeys}}};
1124  
1125 ## Any field/value pairs remaining in $ValuesHash at this point
1126 ## are what will get stored into the appropriate record.
1127  
1128 0 done:
1129 return($ValuesHash);
1130 }
1131  
1132 ### FixUpRetrieval
1133  
1134 ### This is called after values have been retrieved from the database
1135 ### but before they are returned to the user.
1136  
1137 ### Here's where a subclassed method could decrypt, decode,
1138 ### deserialize, /validate, etc. any values that were stored, before
1139 ### they are returned to the user. In the base class, there's nothing
1140 ### to do because we just allow all fields to be returned as strings.
1141  
1142 sub FixUpRetrieval
1143 0 ▶{
Subroutine FixUpRetrieval: not called CC=1 SCAR=6.9
Pod undocumented
1144 0 my $this = shift;
1145 0 my ($ValuesHash) = @_;
1146  
1147 0 done:
1148 return(1);
1149 }
1150  
1151  
1152  
1153 ### Connect
1154 ### Disconnect
1155  
1156 ### ... to and from the database.
1157  
1158 sub Connect
1159 0 ▶{
Subroutine Connect: not called CC=4 SCAR=30.0
Pod undocumented
1160 0 my $this = shift;
1161 0 my $Success = 0;
1162
1163 carp("Could not open database \"$this->{Database}\". Please contact administrator.\n"),
1164 goto done
1165 unless
1166 ($this->{_dbh} = DBI->connect(
1167 join (":",
1168 'DBI',
1169 $this->{DBIDriver},
1170 $this->{Database},
1171 ($this->{HostName} ? $this->{HostName} : ()),
1172 ($this->{Port} ? $this->{Port} : ()),
1173 ),
1174 0 ▶ $this->{Login}, $this->{Password}));
Branch: 3 branches
BranchTrueFalseTotal
unless $this->{"_dbh"} = "DBI"->connect(join(":", "DBI", $this->{"DBIDriver"}, $this->{"Database"}, $this->{"HostName"} ? $this->{"HostName"} : (), $this->{"Port"} ? $this->{"Port"} : ()), $this->{"Login"}, $this->{"Password"}) 0 0 0
$this->{'HostName'} ? : 0 0 0
$this->{'Port'} ? : 0 0 0
1175 0 $Success = 1;
1176 0 done:
1177 return($Success);
1178 }
1179  
1180 sub Disconnect
1181 0 ▶{
Subroutine Disconnect: not called CC=2 SCAR=17.9
Pod undocumented
1182 0 my $this = shift;
1183  
1184 0 ▶ $this->{_dbh}->disconnect() if $this->{_dbh};
Branch: 1 branch
BranchTrueFalseTotal
if $this->{"_dbh"} 0 0 0
1185 }
1186  
1187  
1188 ############# General-purpose SQL table object utility methods; these
1189 ############# can be used in situations where the tie() interface is
1190 ############# not used.
1191  
1192  
1193 sub InsertRecordIntoTable
1194 0 ▶{
Subroutine InsertRecordIntoTable: not called CC=9 SCAR=45.0
Pod undocumented
1195 0 my $this = shift;
1196 0 my ($Fields, $Replace, $TableName) = @_;
1197 0 my ($Success) = (0);
1198  
1199 0 my $dbh = $this->{_dbh};
1200 0 my $sth;
1201  
1202 0 ▶ $TableName ||= $this->{TableName};
Condition0%0/3
$TableName ||= $this->{"TableName"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$TableName ||= $this->{"TableName"}
$TableName $this->{"TableName"}
Truth table0%0/3
A ||= B
A= $TableNameB= $this->{"TableName"}
ABresult
1X1
011
000
1203  
1204 0 ▶ goto done unless defined($TableName) && length($TableName);
Branch: 1 branch
BranchTrueFalseTotal
unless defined $TableName and length $TableName 0 0 0
Condition0%0/3
defined $TableName and length $TableName
!ll&&!rl&&r
000
MC/DC0%0/2
defined $TableName and length $TableName
defined $TableName length $TableName
Truth table0%0/3
A and B
A= defined $TableNameB= length $TableName
ABresult
0X0
100
111
1205 0 ▶ goto done unless defined($Fields) && $Fields && keys %$Fields;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $Fields and $Fields and keys %$Fields 0 0 0
Condition0%0/6
defined $Fields and $Fields and keys %$Fields
!ll&&!rl&&r
000
defined $Fields and $Fields
!ll&&!rl&&r
000
MC/DC0%0/3
defined $Fields and $Fields and keys %$Fields
defined $Fields $Fields keys %$Fields
Truth table0%0/4
A and B and C
A= defined $FieldsB= $FieldsC= keys %$Fields
ABCresult
0XX0
10X0
1100
1111
1206  
1207 0 my $FieldsList = join(", ", keys %$Fields);
1208 0 my $ValuesList = join(", ", map {$dbh->quote($_)} values %$Fields);
1209  
1210 0 ▶ my $ReplaceOrInsert = ($Replace ? 'replace' : 'insert');
Branch: 1 branch
BranchTrueFalseTotal
$Replace ? : 0 0 0
1211  
1212 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) creating new entry in table $TableName (@{[%$Fields]})"),
Branch: 1 branch
BranchTrueFalseTotal
unless $dbh->do("$ReplaceOrInsert into $TableName ($FieldsList) values ($ValuesList)") 0 0 0
1213 goto done
1214 unless
1215 ($dbh->do
1216 (qq{$ReplaceOrInsert into $TableName ($FieldsList) values ($ValuesList)}));
1217  
1218 0 $Success = 1;
1219  
1220 0 done:
1221 return($Success);
1222 }
1223  
1224 ### ReplaceOrInsertIntoTable
1225  
1226 ### Same as the above, but does a "replace into" instead of "insert
1227 ### into". Most of the time, this is what is wanted.
1228  
1229 sub ReplaceOrInsertIntoTable
1230 0 ▶{
Subroutine ReplaceOrInsertIntoTable: not called CC=1 SCAR=6.9
Pod undocumented
1231 0 my $this = shift;
1232 0 my ($Fields, $TableName) = @_;
1233 0 return($this->InsertRecordIntoTable($Fields, 'Replace', $TableName));
1234 }
1235  
1236 sub UpdateFieldsInRecord
1237 0 ▶{
Subroutine UpdateFieldsInRecord: not called CC=11 SCAR=48.8
Pod undocumented
1238 0 my $this = shift;
1239 0 my ($SearchFields, $ReplaceFields, $TableName) = @_;
1240 0 my ($Success) = (0);
1241  
1242 0 my $dbh = $this->{_dbh};
1243 0 my $sth;
1244  
1245 0 ▶ $TableName ||= $this->{TableName};
Condition0%0/3
$TableName ||= $this->{"TableName"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$TableName ||= $this->{"TableName"}
$TableName $this->{"TableName"}
Truth table0%0/3
A ||= B
A= $TableNameB= $this->{"TableName"}
ABresult
1X1
011
000
1246  
1247 0 ▶ goto done unless defined($TableName) && length($TableName);
Branch: 1 branch
BranchTrueFalseTotal
unless defined $TableName and length $TableName 0 0 0
Condition0%0/3
defined $TableName and length $TableName
!ll&&!rl&&r
000
MC/DC0%0/2
defined $TableName and length $TableName
defined $TableName length $TableName
Truth table0%0/3
A and B
A= defined $TableNameB= length $TableName
ABresult
0X0
100
111
1248 0 ▶ goto done unless defined($SearchFields) && $SearchFields && keys %$SearchFields;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $SearchFields and $SearchFields and keys %$SearchFields 0 0 0
Condition0%0/6
defined $SearchFields and $SearchFields and keys %$SearchFields
!ll&&!rl&&r
000
defined $SearchFields and $SearchFields
!ll&&!rl&&r
000
MC/DC0%0/3
defined $SearchFields and $SearchFields and keys %$SearchFields
defined $SearchFields $SearchFields keys %$SearchFields
Truth table0%0/4
A and B and C
A= defined $SearchFieldsB= $SearchFieldsC= keys %$SearchFields
ABCresult
0XX0
10X0
1100
1111
1249 0 ▶ goto done unless defined($ReplaceFields) && $ReplaceFields && keys %$ReplaceFields;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $ReplaceFields and $ReplaceFields and keys %$ReplaceFields 0 0 0
Condition0%0/6
defined $ReplaceFields and $ReplaceFields and keys %$ReplaceFields
!ll&&!rl&&r
000
defined $ReplaceFields and $ReplaceFields
!ll&&!rl&&r
000
MC/DC0%0/3
defined $ReplaceFields and $ReplaceFields and keys %$ReplaceFields
defined $ReplaceFields $ReplaceFields keys %$ReplaceFields
Truth table0%0/4
A and B and C
A= defined $ReplaceFieldsB= $ReplaceFieldsC= keys %$ReplaceFields
ABCresult
0XX0
10X0
1100
1111
1250
1251 0 my $WhereClause = join(' AND ', (map {"$_ = @{[$dbh->quote($SearchFields ->{$_})]}"} keys %$SearchFields ));
1252 0 my $SetClause = join(', ' , (map {"$_ = @{[$dbh->quote($ReplaceFields->{$_})]}"} keys %$ReplaceFields));
1253
1254 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) updating fields (@{[keys %$ReplaceFields]}) in table $TableName"),
Branch: 1 branch
BranchTrueFalseTotal
unless $dbh->do("update $TableName set $SetClause where $WhereClause") 0 0 0
1255 goto done
1256 unless
1257 ($dbh->do
1258 (qq{update $TableName set $SetClause where $WhereClause}));
1259
1260 0 $Success = 1;
1261 0 done:
1262 return($Success);
1263 }
1264  
1265 sub DoCustomQuery
1266 0 ▶{
Subroutine DoCustomQuery: not called CC=10 SCAR=47.0
Pod undocumented
1267 0 my $this = shift;
1268 0 my ($SearchSpec, $TableName, $QueryString) = @_;
1269 0 my ($FoundItems) = [];
1270  
1271 0 my $dbh = $this->{_dbh};
1272 0 my $sth;
1273  
1274 0 ▶ $SearchSpec ||= {};
Condition0%0/2
$SearchSpec ||= {}
l!l
00
MC/DC0%0/1
$SearchSpec ||= {}
$SearchSpec
Truth table0%0/2
A
A= $SearchSpec
Aresult
11
00
1275 0 ▶ $TableName ||= $this->{TableName};
Condition0%0/3
$TableName ||= $this->{"TableName"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$TableName ||= $this->{"TableName"}
$TableName $this->{"TableName"}
Truth table0%0/3
A ||= B
A= $TableNameB= $this->{"TableName"}
ABresult
1X1
011
000
1276  
1277 0 ▶ goto done unless defined($TableName) && length($TableName);
Branch: 1 branch
BranchTrueFalseTotal
unless defined $TableName and length $TableName 0 0 0
Condition0%0/3
defined $TableName and length $TableName
!ll&&!rl&&r
000
MC/DC0%0/2
defined $TableName and length $TableName
defined $TableName length $TableName
Truth table0%0/3
A and B
A= defined $TableNameB= length $TableName
ABresult
0X0
100
111
1278 0 ▶ goto done unless $QueryString;
Branch: 1 branch
BranchTrueFalseTotal
unless $QueryString 0 0 0
1279  
1280 ## We convert pseudo-variables inside the QueryString (notated as
1281 ## $VarName), by looking up their values in $SearchSpec, and
1282 ## dbh-quoting it.
1283  
1284 ## Example: 'select * from People where LastName = $LastName'
1285
1286 0 $QueryString =~ s{\$(\w+)}{$dbh->quote($SearchSpec->{$1})}ges;
1287
1288 0 ▶ carp("Fatal error executing \"$QueryString\""),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare($QueryString) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare($QueryString) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare($QueryString) and $sth->execute
$sth = $dbh->prepare($QueryString) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare($QueryString)B= $sth->execute
ABresult
0X0
100
111
1289 goto done
1290 unless
1291 (($sth = $dbh->prepare
1292 ($QueryString)) &&
1293 ($sth->execute()));
1294
1295 0 my $Fields;
1296 0 ▶ while ($Fields = $sth->fetchrow_hashref())
Branch: 1 branch
BranchTrueFalseTotal
if ($Fields = $sth->fetchrow_hashref) 0 0 0
1297 {
1298 0 push @$FoundItems, $Fields;
1299 }
1300
1301 done:
1302 0 ▶ $sth->finish() if $sth;
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
1303 ## die &Dumper($FoundItems);
1304 0 return($FoundItems);
1305 }
1306 sub GetMatchingRecordsFromTable
1307 0 ▶{
Subroutine GetMatchingRecordsFromTable: not called CC=10 SCAR=47.0
Pod undocumented
1308 0 my $this = shift;
1309 0 my ($SearchSpec, $TableName) = (@_);
1310 0 my ($FoundItems) = [];
1311  
1312 0 my $dbh = $this->{_dbh};
1313 0 my $sth;
1314  
1315 0 ▶ $SearchSpec ||= {};
Condition0%0/2
$SearchSpec ||= {}
l!l
00
MC/DC0%0/1
$SearchSpec ||= {}
$SearchSpec
Truth table0%0/2
A
A= $SearchSpec
Aresult
11
00
1316 0 ▶ $TableName ||= $this->{TableName};
Condition0%0/3
$TableName ||= $this->{"TableName"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$TableName ||= $this->{"TableName"}
$TableName $this->{"TableName"}
Truth table0%0/3
A ||= B
A= $TableNameB= $this->{"TableName"}
ABresult
1X1
011
000
1317  
1318 0 ▶ goto done unless defined($TableName) && length($TableName);
Branch: 1 branch
BranchTrueFalseTotal
unless defined $TableName and length $TableName 0 0 0
Condition0%0/3
defined $TableName and length $TableName
!ll&&!rl&&r
000
MC/DC0%0/2
defined $TableName and length $TableName
defined $TableName length $TableName
Truth table0%0/3
A and B
A= defined $TableNameB= length $TableName
ABresult
0X0
100
111
1319
1320 my $WhereClause = (keys %$SearchSpec ?
1321 0 ▶ "where " . join (' AND ', (map {"$_ = @{[$dbh->quote($SearchSpec->{$_})]}"}
Branch: 1 branch
BranchTrueFalseTotal
keys %$SearchSpec ? : 0 0 0
1322 keys %$SearchSpec)) :
1323 '');
1324
1325 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) finding field from \"$TableName\" $WhereClause"),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare("select * from $TableName $WhereClause") and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare("select * from $TableName $WhereClause") and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare("select * from $TableName $WhereClause") and $sth->execute
$sth = $dbh->prepare("select * from $TableName $WhereClause") $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare("select * from $TableName $WhereClause")B= $sth->execute
ABresult
0X0
100
111
1326 goto done
1327 unless
1328 (($sth = $dbh->prepare
1329 (qq{select * from $TableName $WhereClause})) &&
1330 ($sth->execute()));
1331
1332 0 my $Fields;
1333 0 ▶ while ($Fields = $sth->fetchrow_hashref())
Branch: 1 branch
BranchTrueFalseTotal
if ($Fields = $sth->fetchrow_hashref) 0 0 0
1334 {
1335 0 push @$FoundItems, $Fields;
1336 }
1337
1338 done:
1339 0 ▶ $sth->finish() if $sth;
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
1340 0 return($FoundItems);
1341 }
1342  
1343 sub DeleteMatchingRecordsFromTable
1344 0 ▶{
Subroutine DeleteMatchingRecordsFromTable: not called CC=9 SCAR=45.0
Pod undocumented
1345 0 my $this = shift;
1346 0 my ($SearchSpec, $TableName) = @_;
1347 0 my ($Success);
1348  
1349 0 my $dbh = $this->{_dbh};
1350 0 my $sth;
1351  
1352 0 ▶ $SearchSpec ||= {};
Condition0%0/2
$SearchSpec ||= {}
l!l
00
MC/DC0%0/1
$SearchSpec ||= {}
$SearchSpec
Truth table0%0/2
A
A= $SearchSpec
Aresult
11
00
1353 0 ▶ $TableName ||= $this->{TableName};
Condition0%0/3
$TableName ||= $this->{"TableName"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$TableName ||= $this->{"TableName"}
$TableName $this->{"TableName"}
Truth table0%0/3
A ||= B
A= $TableNameB= $this->{"TableName"}
ABresult
1X1
011
000
1354  
1355 0 ▶ goto done unless defined($TableName) && length($TableName);
Branch: 1 branch
BranchTrueFalseTotal
unless defined $TableName and length $TableName 0 0 0
Condition0%0/3
defined $TableName and length $TableName
!ll&&!rl&&r
000
MC/DC0%0/2
defined $TableName and length $TableName
defined $TableName length $TableName
Truth table0%0/3
A and B
A= defined $TableNameB= length $TableName
ABresult
0X0
100
111
1356
1357 my $WhereClause = (keys %$SearchSpec ?
1358 0 ▶ "where " . join (' AND ', (map {"$_ = @{[$dbh->quote($SearchSpec->{$_})]}"}
Branch: 1 branch
BranchTrueFalseTotal
keys %$SearchSpec ? : 0 0 0
1359 keys %$SearchSpec)) :
1360 '');
1361
1362 0 ▶ carp("Fatal error (@{[$dbh->errstr]}) finding field from \"$TableName\" $WhereClause"),
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare("delete from $TableName $WhereClause") and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare("delete from $TableName $WhereClause") and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare("delete from $TableName $WhereClause") and $sth->execute
$sth = $dbh->prepare("delete from $TableName $WhereClause") $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare("delete from $TableName $WhereClause")B= $sth->execute
ABresult
0X0
100
111
1363 goto done
1364 unless
1365 (($sth = $dbh->prepare
1366 (qq{delete from $TableName $WhereClause})) &&
1367 ($sth->execute()));
1368
1369 0 $Success = 1;
1370 0 ▶ done:
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
1371 $sth->finish() if $sth;
1372 0 return($Success);
1373 }
1374  
1375 sub SearchWithSingleJoin
1376 0 ▶{
Subroutine SearchWithSingleJoin: not called CC=19 SCAR=59.4
Pod undocumented
1377 0 my $this = shift;
1378 0 my ($TableName, $SearchSpec, $JoinTable, $JoinFields, $JoinRetrieveFields) = (@_);
1379 0 my ($FoundItems) = [];
1380  
1381 0 my $dbh = $this->{_dbh};
1382 0 my $sth;
1383  
1384 0 my $ErrorMessage = "";
1385  
1386 0 ▶ goto done unless defined($TableName) && length($TableName);
Branch: 1 branch
BranchTrueFalseTotal
unless defined $TableName and length $TableName 0 0 0
Condition0%0/3
defined $TableName and length $TableName
!ll&&!rl&&r
000
MC/DC0%0/2
defined $TableName and length $TableName
defined $TableName length $TableName
Truth table0%0/3
A and B
A= defined $TableNameB= length $TableName
ABresult
0X0
100
111
1387  
1388 0 ▶ my $TableSpec = ($JoinTable ? "$TableName." : '');
Branch: 1 branch
BranchTrueFalseTotal
$JoinTable ? : 0 0 0
1389  
1390 0 ▶ my $JoinClauses = [($JoinTable && $JoinFields && @{$JoinFields ||= []} ?
Condition0%0/2
$JoinFields ||= []
l!l
00
MC/DC0%0/1
$JoinFields ||= []
$JoinFields
Truth table0%0/2
A
A= $JoinFields
Aresult
11
00
1391 0 ▶ (map {"$TableName.$_ = $JoinTable.$_"} @$JoinFields) :
Branch: 1 branch
BranchTrueFalseTotal
$JoinTable && $JoinFields && @{$JoinFields ||= [];} ? : 0 0 0
Condition0%0/6
$JoinTable and $JoinFields and @{$JoinFields ||= [];}
!ll&&!rl&&r
000
$JoinTable and $JoinFields
!ll&&!rl&&r
000
MC/DC0%0/3
$JoinTable and $JoinFields and @{$JoinFields ||= [];}
$JoinTable $JoinFields @{$JoinFields ||= [];}
Truth table0%0/4
A and B and C
A= $JoinTableB= $JoinFieldsC= @{$JoinFields ||= [];}
ABCresult
0XX0
10X0
1100
1111
1392 ())];
1393  
1394 my $WhereClause = ((keys %$SearchSpec) || (@$JoinClauses) ?
1395 0 ▶ "where " . join (' AND ', (@$JoinClauses, (map {"$TableSpec$_ @{[$SearchSpec->{$_} =~ s/^([=<>]+)// ? $1 : '=']} @{[$dbh->quote($SearchSpec->{$_})]}"}
Branch: 2 branches
BranchTrueFalseTotal
keys %$SearchSpec || @$JoinClauses ? : 0 0 0
$SearchSpec->{$_} =~ s/^([=<>]+)// ? : 0 0 0
Condition0%0/3
keys %$SearchSpec or @$JoinClauses
l!l&&r!l&&!r
000
MC/DC0%0/2
keys %$SearchSpec or @$JoinClauses
keys %$SearchSpec @$JoinClauses
Truth table0%0/3
A or B
A= keys %$SearchSpecB= @$JoinClauses
ABresult
1X1
011
000
1396 keys %$SearchSpec))) :
1397 '');
1398  
1399 0 ▶ my $SelectSpec = ($JoinTable ? join(", ", "$TableName.*", map {"$JoinTable.$_"} @{$JoinRetrieveFields || []}) : "*");
Branch: 1 branch
BranchTrueFalseTotal
$JoinTable ? : 0 0 0
Condition0%0/2
$JoinRetrieveFields || []
l!l
00
MC/DC0%0/1
$JoinRetrieveFields || []
$JoinRetrieveFields
Truth table0%0/2
A
A= $JoinRetrieveFields
Aresult
11
00
1400  
1401 0 ▶ my $FromSpec = ($JoinTable ? "$TableName, $JoinTable" : "$TableName");
Branch: 1 branch
BranchTrueFalseTotal
$JoinTable ? : 0 0 0
1402  
1403 0 ▶ $TableName = "$TableName, $JoinTable" if $JoinTable;
Branch: 1 branch
BranchTrueFalseTotal
if $JoinTable 0 0 0
1404  
1405 0 my $Query = qq{select $SelectSpec from $FromSpec $WhereClause};
1406  
1407 0 ▶ $ErrorMessage = "Fatal error (@{[$dbh->errstr]}) finding field from $FromSpec $WhereClause",
Branch: 1 branch
BranchTrueFalseTotal
unless $sth = $dbh->prepare($Query) and $sth->execute 0 0 0
Condition0%0/3
$sth = $dbh->prepare($Query) and $sth->execute
!ll&&!rl&&r
000
MC/DC0%0/2
$sth = $dbh->prepare($Query) and $sth->execute
$sth = $dbh->prepare($Query) $sth->execute
Truth table0%0/3
A and B
A= $sth = $dbh->prepare($Query)B= $sth->execute
ABresult
0X0
100
111
1408 goto done
1409 unless
1410 (($sth = $dbh->prepare
1411 ($Query)) &&
1412 ($sth->execute()));
1413  
1414 0 my $FoundRecord;
1415 0 ▶ while ($FoundRecord = $sth->fetchrow_hashref())
Branch: 1 branch
BranchTrueFalseTotal
if ($FoundRecord = $sth->fetchrow_hashref) 0 0 0
1416 {
1417 0 push @$FoundItems, $FoundRecord;
1418 }
1419  
1420 done:
1421 0 ▶ $sth->finish() if $sth;
Branch: 1 branch
BranchTrueFalseTotal
if $sth 0 0 0
1422 0 return($FoundItems, $ErrorMessage);
1423 }
1424 1 1;
↑ Summary