StoredHash.pm

filter: Statementstmt 31.5% 155 / 491 Branchbran 21.5% 50 / 232 Conditioncond 14.5% 7 / 48 MC/DCmcdc 3.3% 1 / 30 Subroutinesub 46.5% 20 / 43 Podpod 36.6% 11 / 30 totaltotal 27.9% 244 / 874 CC 136 SCAR 40.4
CC
136
Cov
27%
insert
161.9
loadchildren
132.0
qexecute
110.0
CRAP 56.7
« StoredHash/LD.pm ↑ Summary StoredHash/ARS.pm »
1 =head1 NAME
2  
3 StoredHash - Minimalistic, yet fairly complete DBI Persister with a definite NoSQL feel to it
4  
5 =head1 SYNOPSIS
6  
7 use StoredHash;
8 use DBI;
9 use Data::Dumper;
10
11 my $dbh = DBI->connect("dbi:SQLite:dbname=/tmp/zoo.db");
12 # Lightweight demonstration of StoredHash in action (with SQLite)
13 $dbh->do("CREATE TABLE animals (speciesid INTEGER NOT NULL PRIMARY KEY, name CHAR(16), limbcnt INTEGER, family CHAR(16))");
14 my $shp = StoredHash->new('table' => 'animals', 'pkey' => ['speciesid'],
15 'autoid' => 1, 'dbh' => $dbh, 'debug' => 0);
16 # Hash object to be stored
17 my $monkey = {'name' => 'Common Monkey', 'limbcnt' => 5, 'family' => 'mammal',};
18
19 # Happens to return numeric id (because of auto-increment pkey / autoid)
20 my $spid = $shp->insert($monkey);
21 print("Created by: id=$spid\n");
22 # Load entry
23 my $ent = $shp->load([$spid]);
24 print("Fetched (by $spid): ".Dumper($ent)."\n");
25 # Get: {'name' => 'Common Monkey', 'speciesid' => 469, 'limbcnt' => 5,'family' => 'mammal',}
26 # Fix error in entry (don't count tail to be limb)
27 $ent->{'limbcnt'} = 4;
28 # Update (with some redundant attributes that do not change)
29 print("Update $ent->{'speciesid'}\n");
30 $shp->update($ent, [$ent->{'speciesid'}]);
31 # Could reduce / optimize change to bare minimum:
32 my %change = ('limbcnt' => 4);
33 print("Reduce property value on $spid\n");
34 $shp->update(\%change, [$spid]);
35 # Later ... (species dies extinct ?)
36 #$shp->delete([$spid]);
37
38 # Test if we need to insert / update (based on presence in DB)
39 my $id = 5987;
40 my $invals = {'name' => 'Crow', 'limbcnt' => 4, 'family' => 'birds'};
41 print("Test Presence of Animal '$id'\n");
42 if ($shp->exists([$id])) {$shp->update($invals, [$id]);}
43 else {$shp->insert($invals);}
44
45 ##### Easy loading of sets / collections
46 # Load all the animals
47 my $animarr = $shp->loadset();
48 print("All Animals: ".Dumper($animarr)."\n");
49 # Load only mammals (by filter)
50 my $mammarr = $shp->loadset({'family' => 'mammal'});
51 print("Mammals: ".Dumper($mammarr)."\n");
52  
53 =head1 DESCRIPTION
54  
55 Allow DB Persistence operations (insert(), load(), update(), delete(),
56 exists()) on a plain old hash (unblessed or blessed) without writing
57 classes, persistence code or SQL.
58  
59 Optionally StoredHash allows your classes to inherit peristence capability from StoredHash allowing your objects
60 to call StoredHash persistence methoda via object directly.
61  
62  
63 =head1 GENERAL INFO ON StoredHash PERSISTENCE
64  
65 =over 4
66  
67 =item * Connection is stored in persister. Thus there is no need to pass it as parameter to persister methods.
68  
69 =item * Composite keys are supported by StoredHash. Because of this id values are passed in array. Id values must
70 be ordered the same as their attribute names suring construction (as passed in 'pkey' construction parameter).
71  
72 =item * Some persistence methods support 'attrs' parameter. This means "partial attributes" or "only these attributes" whatever the direction is
73 persistence operation is. Examples: load(): load only these attributes, update(): update only these attributes, etc.
74  
75 =item * StoredHash is not validating the hash keys / attribute (or 'attrs' parameter above) against these attributes
76 actually existing in DB schema. Caller of persistence methods is responsible validating the "fit" of hash to a schema.
77  
78 =back
79  
80 Above principles are consistent across persistence methods. These details will be not repeated in method documentation.
81  
82 =cut
83  
84 # Maintain a good NoSQL feel with your SQL database.
85 # ... a good nonrelation relationship
86  
87 # Author: olli.hollmen@gmail.com
88 # License: Perl License
89  
90 # StoredHash needs an OO instance of persister to function.
91  
92 # TODO: Because insert, update (the vals we want to pers.) are instance specific
93 # Possibly return an object or bare hash from preparation of ins/upd/del
94 # With
95 # - query
96 # - vals (to pass to exec)
97 # - attr (needed ?)
98 # - Assigned ID ?
99 # Make this object w. meth execute() ???? getid()
100 # http://www.nntp.perl.org/group/perl.dbi.dev/2010/03/msg5887.html
101 # ANSI X3.135 and ISO/IEC 9075
102 # ftp://sqlstandards.org/SC32/SQL_Registry/
103  
104 # TODO: Change/Add pkey => idattr @pkv => @idv
105 # Support Mappings (before storage as separate op ?)
106 package StoredHash;
107 5 ▶use Scalar::Util ('reftype'); #
Subroutine BEGIN: called CC=1 SCAR=0.0
108 5 ▶use Data::Dumper;
Subroutine BEGIN: called CC=1 SCAR=0.0
109 5 our $hardval = 0; # 1= Call $dbh->do() 2=Return query
110 5 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
111 5 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
112 5 our $VERSION = '0.031';
113 # Module extraction config
114 #our $mecfg = {};
115 # Instance attributes (create accessors ?)
116 # Allow 'attr' to act as attr filter
117 5 my @opta = ('dbh', 'table','pkey','autoid','autoprobe','simu','errstr',
118 'seqname','debug',); #
119 # TODO: Support sequence for Oracle / Postgres
120 # seq_emp.NEXTVAL
121 5 my $bkmeta = {
122 #'mysql' => {'iq' => "SELECT LAST_INSERT_ID()",},
123 #'Sybase' => {'iq' => "SELECT \@\@identity",},
124 'Oracle' => {
125 #'iq' => "SELECT \@\@identity",
126 'sv' => '%s.NEXTVAL',}, # AS adid SET NOCOUNT OFF
127 # Postgres ???
128 };
129 # Tentative class-level / static structures:
130 # persister cache: $shpcache = {}; # Keyed by 'table'
131 # query cached: $qcache = {}; # Allow prepared and params ? plain K-V or HoH ?
132 #
133 =head1 METHODS
134  
135 =head2 $shp = StoredHash->new(%opts);
136  
137 Create new instance of StoredHash Persister.
138  
139 Keyword parameters in %opts:
140  
141 =over 4
142  
143 =item * 'pkey' - array (ref) to reflect the identifying attrtibute(s) of
144 entry (e.g. single attr for numeric sequential ids, multiple for composite key)
145  
146 =item * 'dbh' - DBI connection to database (optional). Not passing 'dbh' makes
147 methods insert/update/load/delete return the SQL query only (as a string)
148  
149 =back
150  
151 =cut
152 3 ▶sub new {
Subroutine new: called CC=3 SCAR=11.5
Pod documented
153 3 my ($class, %opt) = @_;
154 3 my $self = {};
155
156 # Generate where by pkey OR use where
157 #if ($opt{'where'}) {}
158 # Moved for early bless
159 3 bless($self, $class);
160 # For Child loading / temp use
161 0 ▶ if ($opt{'loose'}) {goto PASSPKEY;}
Branch: 1 branch
BranchTrueFalseTotal
if ($opt{"loose"}) 0 3 3
162 3 ▶ if ($opt{'pkey'}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($opt{'pkey'}) { } 3 0 3
163 3 $self->{'pkey'} = $opt{'pkey'};
164 # TODO: Do NOT cache WHERE id ...
165 3 $self->{'where'} = whereid($self); # \%opt # join('AND', map({" $_ = ?";} pkeys(\%opt));
166 }
167 0 else {die("Need pkey info");}
168 PASSPKEY:
169 # Validate seq. (Need additional params to note call for seq?)
170 #if ($opt{'autoid'} eq 'seq') {
171 # #$c{'seqcall'};
172 #}
173 # Filter options to self
174 3 @$self{@opta} = @opt{@opta};
175
176 3 return($self);
177 }
178  
179 =head2 $shp->errstr($v)
180  
181 Access error string that method may leave to object.
182 Notice that many methods throw exception (by die()) with
183 error message rather than leave it within object.
184  
185 =cut
186 0 ▶sub errstr {
Subroutine errstr: not called CC=2 SCAR=17.9
Pod documented
187 0 my ($p, $v) = @_;
188 0 ▶ if ($v) {$p->{'errstr'} = $v;}
Branch: 1 branch
BranchTrueFalseTotal
if ($v) 0 0 0
189 0 $p->{'errstr'};
190 }
191  
192 # Internal method for executing query $q by filling placeholders with
193 # values passed in @$vals.
194 # Optional $rett (usually not passed) can force a special return type
195 # Some supported return force tags:
196 #=item * 'count' - number of entries counted with count(*) query
197 #=item * 'sth' - return statement handle ($sth), which will be used outside.
198 #=item * 'hash' - return a hash entry (first entry of resultset)
199 #=item * 'aoh' - return array of hashes reflecting result set.
200 # By default (no $rett) returns the ($ok)value from $sth->execute().
201 # Also by default statement statement handle gets properly closed
202 # (If requested return type was $sth, the caller should take care of
203 # calling $sth->finish()
204 0 ▶sub qexecute {
Subroutine qexecute: not called CC=10 SCAR=47.0
Pod undocumented
205 0 my ($p, $q, $vals, $rett) = @_;
206 0 my $dbh = $p->{'dbh'};
207 0 my $sth; # Keep here to have avail in callbacks below
208 0 ▶ if (!$dbh || $p->{'simu'}) { #
Branch: 1 branch
BranchTrueFalseTotal
if (not $dbh or $p->{"simu"}) 0 0 0
Condition0%0/3
not $dbh or $p->{"simu"}
l!l&&r!l&&!r
000
MC/DC0%0/2
not $dbh or $p->{"simu"}
not $dbh $p->{"simu"}
Truth table0%0/3
not(A) or B
A= not $dbhB= $p->{"simu"}
ABresult
1X1
011
000
209 0 local $Data::Dumper::Terse = 1;
210 0 local $Data::Dumper::Indent = 0;
211 0 print(STDERR "SQL($p->{'table'}): $q\nPlaceholder Vals:".Dumper($vals)."\n");
212 0 return(0);
213 }
214 # Special Return value generators
215 # These should also close the statement (if that is not returned)
216 my $rets = {
217 0 ▶ 'count' => sub {my @a = $sth->fetchrow_array();$sth->finish();$a[0];},
Subroutine __ANON__: not called CC=1 SCAR=6.9
218 0 ▶ 'sth' => sub {return($sth);},
Subroutine __ANON__: not called CC=1 SCAR=6.9
219 0 ▶ 'hash' => sub {my $h = $sth->fetchrow_hashref();$sth->finish();$h;},
Subroutine __ANON__: not called CC=1 SCAR=6.9
220 0 ▶ 'aoh' => sub {my $arr = $sth->fetchall_arrayref({});$sth->finish();$arr;},
Subroutine __ANON__: not called CC=1 SCAR=6.9
221 0 };
222 # $p->{'errstr'} =return(0);
223 0 ▶ if (!$dbh) {die("SHP: No Connection !");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($dbh) 0 0 0
224 0 ▶ if ($p->{'debug'} && $vals) {print("Full Q: $q w. ".scalar(@$vals)." values\n");}
Branch: 1 branch
BranchTrueFalseTotal
if ($p->{"debug"} and $vals) 0 0 0
Condition0%0/3
$p->{"debug"} and $vals
!ll&&!rl&&r
000
MC/DC0%0/2
$p->{"debug"} and $vals
$p->{"debug"} $vals
Truth table0%0/3
A and B
A= $p->{"debug"}B= $vals
ABresult
0X0
100
111
225 # Prepare cached ?
226 0 $sth = $dbh->prepare_cached($q); # print("CACHED\n");
227 0 ▶ if (!$sth) {die("Query ($q) Not prepared (".$dbh->errstr().")\n");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($sth) 0 0 0
228 0 my $ok = $sth->execute(@$vals);
229 0 ▶ if (!$ok) {die("Failed to execute\n - Query: $q\n - Vals: ".Dumper($vals)."\n - Message:\n".$sth->errstr()."");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($ok) 0 0 0
230 # Special return processing.
231 # TODO: Suppress Use of uninitialized value $rett in hash element
232 0 ▶ if (!$rett) {$rett = '';}
Branch: 1 branch
BranchTrueFalseTotal
unless ($rett) 0 0 0
233 0 ▶ if (my $rcb = $rets->{$rett}) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $rcb = $rets->{$rett}) 0 0 0
234 #print("Special return by $rett ($rcb)\n");
235 0 return($rcb->());
236 }
237 # Done with statement
238 DWS:
239 0 $sth->finish();
240 0 return($ok);
241 }
242  
243 ###################################################
244 # Make this the "best-possible" fallback quote when quote() method from driver (via connection)
245 # is not available. The surrounding quotes are included in the quoted string.
246 # This aims to be SQL compliant as much as possible
247 # Need looks like number (will fail sometimes) ?
248 0 ▶sub quote {
Subroutine quote: not called CC=1 SCAR=6.9
Pod undocumented
249 0 my ($s) = @_;
250 0 $s =~ s/\'/\'\'/g;
251 0 $s =~ s/\n/\\n/g;
252 0 "'".$s."'";
253 }
254  
255 =head2 $shp->insert($e)
256  
257 Store entry %$e (hash) inserting it as a new entry to a database.
258  
259 Returns an array of ID values for the entry that got stored (array
260 of one element for numeric primary key, multiple for composite key).
261  
262 =cut
263 # (ref to)
264 #Connection has been passed previously in construction of persister.
265 #The table / schema to store to is either the one passed at
266 #construction or derived from perl "blessing" of entry ($e).
267 2 ▶sub insert {
Subroutine insert: called CC=21 SCAR=50.9
Pod documented
268 2 my ($p, $e, %c) = @_;
269 2 local $Data::Dumper::Terse = 1;local $Data::Dumper::Indent = 0;
270 # No enforced internal validation
271 2 eval {$p->validate();};
272 0 ▶ if ($@) {die("Persister validation error: $@");} # $p->{'errstr'} = $@;return(1);
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 2 2
273 0 ▶ if (reftype($e) ne 'HASH') {die("Entry needs to be HASH");} # return(2);
Branch: 1 branch
BranchTrueFalseTotal
if (builtin::reftype($e) ne "HASH") 0 2 2
274 # Explicit attributes. Do not check for ref-valued attributes here.
275 # (with an idea that caller must know what it is passing).
276 2 ▶ if (my $ats = $c{'attrs'}) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $ats = $c{"attrs"}) 0 2 2
277 0 ▶ if (ref($ats) ne 'ARRAY') {die("Passed 'attrs' must be an array");}
Branch: 1 branch
BranchTrueFalseTotal
if (ref $ats ne "ARRAY") 0 0 0
278 0 %$e = map({($_, $e->{$_});} @$ats); # Reconfig $e content.
279 }
280 # Possibly also test for references (ds branching ?) eliminating them too
281 # In case some ARE found, make a copy, eliminate refs and mark copy as $e
282 2 ▶ if (grep({ref($e->{$_});} keys(%$e))) {
Branch: 1 branch
BranchTrueFalseTotal
if (grep {ref $e->{$_};} keys %$e) 0 2 2
283 # Consider array serialization policy here or as a step before ?
284 0 ▶ my %ec = map({ref($e->{$_}) ? () : ($_, $e->{$_});} keys(%$e));
Branch: 1 branch
BranchTrueFalseTotal
ref $e->{$_} ? : 0 0 0
285 0 $e = \%ec;
286 }
287 # Extract attrs and values (for non-ref attrs)
288 2 my @ea = sort (keys(%$e));
289 2 my @ev = @$e{@ea}; # map()
290 # To Support sequence we MUST precalc placeholders here.
291 # In case of Sequence should place sequence call ...
292 2 my @pha = map({'?';} @ea);
293 # Sequence - Add sequenced ID allocation ???
294 # $p->{'seqname'}
295 2 ▶ if ($p->{'autoid'} && ($p->{'autoid'} eq 'seq')) {
Branch: 1 branch
BranchTrueFalseTotal
if ($p->{"autoid"} and $p->{"autoid"} eq "seq") 0 2 2
Condition33%1/3
$p->{"autoid"} and $p->{"autoid"} eq "seq"
!ll&&!rl&&r
200
MC/DC0%0/2
$p->{"autoid"} and $p->{"autoid"} eq "seq"
$p->{"autoid"} $p->{"autoid"} eq "seq"
Truth table33%1/3
A and B
A= $p->{"autoid"}B= $p->{"autoid"} eq "seq"
ABresult
0X0
100
111
296 0 my $bkt = 'Oracle';
297 0 my @pka = pkeys($p);
298 0 ▶ if (@pka > 1) {die("Error: Multiple (composite) pkeys for sequenced ID");}
Branch: 1 branch
BranchTrueFalseTotal
if (@pka > 1) 0 0 0
299 # Add Sequence id attibute AND sequence call (unshift to front ?)
300 0 push(@ea, @pka); # $p->{'pkey'}->[0]
301 # Lookup Sequence Syntax (as printf format)for paticular DB Backend
302 # Fixed INSERT Below to NOT have placeholder for sequence (placeholders calc'd above)
303 # I case of sequence the counts of VALS vs @pha will be unbalanced (off by 1)
304 0 push(@pha, sprintf("$bkmeta->{$bkt}->{'sv'}", $p->{'seqname'}) ); #
305 #DEBUG:print("FMT: $bkmeta->{$bkt}->{'sv'} / $p->{'seqname'}\n");
306 }
307 2 ▶ if ($StoredHash::hardval) {
Branch: 1 branch
BranchTrueFalseTotal
if ($StoredHash::hardval) 0 2 2
308  
309 #my $quote = $p->{'dbh'} ? ref($p->{'dbh'}).'::quote' : \&quote;
310 #DEBUG:print("QUOTE = $quote\n\n\n\n");
311 #OLD:my $quote = $p->{'dbh'}->can('quote') ? \&$p->{'dbh'}->quote : \&sqlvalesc;
312 0 ▶ my $quoter = $p->{'dbh'}->can('quote') ? sub {$p->{'dbh'}->quote($_[0]);} : \&quote;
Subroutine __ANON__: not called CC=1 SCAR=6.9
Branch: 1 branch
BranchTrueFalseTotal
$p->{'dbh'}->can('quote') ? : 0 0 0
313 @pha = map({
314 #$p->{'dbh'}->quote($e->{$_});
315 0 $quoter->($e->{$_}); # OLD: Embed $dbh as $_[0]
316 } @ea);
317 }
318 2 my $qp = "INSERT INTO $p->{'table'} (".join(',',@ea).") VALUES (".join(',', @pha).")";
319 # For now $StoredHash::hardval will always return query for the very efficient $dbh->do()
320 # to execute the query.
321 2 ▶ if (my $hv = $StoredHash::hardval) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $hv = $StoredHash::hardval) 0 2 2
322 0 ▶ if ($hv == 2) {return($qp);} # Return INSERT ... as-is
Branch: 2 branches
BranchTrueFalseTotal
if ($hv == 2) { } 0 0 0
elsif ($hv == 1) { } 0 0 0
323 elsif ($hv == 1) {
324 0 my $ok = $p->{'dbh'}->do($qp);
325 0 ▶ if (!$ok) {die("Failed do on hard query: $qp");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($ok) 0 0 0
326 # Proceed to autoid
327 }
328 }
329 #DEBUG:print(Dumper($p));
330 0 ▶ if ($p->{'debug'}) {print(STDERR "Ins.vals: ".Dumper(\@ev)."\n");}
Branch: 1 branch
BranchTrueFalseTotal
if ($p->{"debug"}) 0 2 2
331 2 ▶ if (!$p->{'dbh'}) {return($qp);} # No conn. - return SQL
Branch: 1 branch
BranchTrueFalseTotal
unless ($p->{"dbh"}) 2 0 2
332
333 0 my $okid;
334 0 ▶ if (!$StoredHash::hardval) {$okid = $p->qexecute($qp, \@ev);}
Branch: 1 branch
BranchTrueFalseTotal
unless ($StoredHash::hardval) 0 0 0
335
336 # Auto-id - either AUTO_INC style or Sequence (works for seq. too ?)
337 0 ▶ if ($p->{'autoid'}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($p->{'autoid'}) { } 0 0 0
338 0 my @pka = pkeys($p);
339 0 ▶ if (@pka != 1) {die(scalar(@pka)." Keys for Autoid");}
Branch: 1 branch
BranchTrueFalseTotal
if (@pka != 1) 0 0 0
340 0 my $id = $p->fetchautoid();
341 #$e->{$pka[0]} = $id;
342 0 return(($id));
343 }
344 # Seq ?
345 #elsif () {}
346 # $p->pkeyvals($e); # wantarray ?
347 else {
348 0 my @pka = pkeys($p);
349 0 return(@$e{@pka}); # wantarray ? @$e{@pka} : [@$e{@pka}];
350 }
351 }
352  
353 =head2 $shp->update($e, $ids, %opts);
354  
355 Update an existing entry by ID(s) ($ids) in the database with values in hash %$e.
356  
357 Return true for success, false for failure (direct $ok values from underlying
358 $sth->execute() for 'autoid' => 1 ),
359  
360 =cut
361 # Provide protection for AUTO-ID (to not be changed) ?
362 #For flexibility the $idvals may be hash or array (reference) with
363 #hash containing (all) id keys and id values or alternatively array
364 #containing id values IN THE SAME ORDER as keys were passed during
365 #construction (with idattr/pkey parameter).
366  
367 2 ▶sub update {
Subroutine update: called CC=18 SCAR=43.7
Pod documented
368 2 my ($p, $e, $idvals, %c) = @_;
369 2 local $Data::Dumper::Terse = 1;local $Data::Dumper::Indent = 0;
370 2 my @pka; # To be visible to closure
371 # Extract ID Values from hash OR array
372 # TODO: Loosen requirement for hash to describe pk-attributes ?
373 my $idvgens = {
374 0 ▶ 'HASH' => sub {@$idvals{@pka};},
Subroutine __ANON__: not called CC=1 SCAR=6.9
375 2 ▶ 'ARRAY' => sub {return(@$idvals);},
Subroutine __ANON__: called CC=1 SCAR=0.0
376 #'' => sub {[$idvals];}
377 2 };
378 # No mandatory (internal) validation ?
379 #eval {$p->validate();};if ($@) {$p->{'errstr'} = $@;return(1);}
380 2 @pka = pkeys($p); # PKs from Persister
381 0 ▶ if (reftype($e) ne 'HASH') {die("Entry not passed as hash");} # {$p->{'errstr'} = "Entry needs to be hash";return(2);}
Branch: 1 branch
BranchTrueFalseTotal
if (builtin::reftype($e) ne "HASH") 0 2 2
382 # Probe the type of $idvals
383 2 my $idrt = reftype($idvals);
384 0 ▶ if ($p->{'debug'}) {print("Got IDs:".Dumper($idvals)." as '$idrt'\n");}
Branch: 1 branch
BranchTrueFalseTotal
if ($p->{"debug"}) 0 2 2
385 #my @idv;
386 2 my @pkv; # PK Values
387 # Handle kw params for bulk updates ? Example: 'w' => {...} if (!$idrt {$widstr = wherefilter();}
388 2 ▶ if (my $idg = $idvgens->{$idrt}) {@pkv = $idg->();}
Branch: 1 branch
BranchTrueFalseTotal
if (my $idg = $idvgens->{$idrt}) { } 2 0 2
389 #VERYOLD:if ($idrt ne 'HASH') {$p->{'errstr'} = "ID needs to be hash";return(3);}
390 0 else {die("Need IDs as HASH or ARRAY (reference, got '$idrt')");}
391 #my ($cnt_a, $cnt_v) = (scalar(@pka), scalar(@pkv));
392 0 ▶ if (@pkv != @pka) {die("Number of ID keys and ID values (".scalar(@pka).'/'.scalar(@pkv).") not matching for update ($p->{'table'})");}
Branch: 1 branch
BranchTrueFalseTotal
if (@pkv != @pka) 0 2 2
393 #OLDSIMPLE: my @ea = sort(keys(%$e));
394 2 my @ea;
395 # Leave to caller to check: Verify that we DO NOT HAVE pkeys in set (?)
396 #my $drive = 0;
397 # Comply to explicit attributes passed as 'attrs'
398 2 ▶ if (ref($c{'attrs'}) eq 'ARRAY') {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $c{'attrs'} eq 'ARRAY') { } 0 2 2
399 #print("DRIVE ATTRIBUTES: ");
400 0 @ea = @{$c{'attrs'}};
401 #$drive = 1;
402 }
403 # Use natural attributes from entry.
404 2 else {@ea = sort(keys(%$e));}
405 #print("DRIVE ATTRIBUTES = $drive: @ea\n");exit(1);
406 #my @pkv = @$idh{@pka}; # $idvals, Does not work for hash
407 # Check for undef/empty ID comps
408 2 ▶ if (my @badid = $p->invalidids(@pkv)) {
Branch: 1 branch
BranchTrueFalseTotal
if (my(@badid) = $p->invalidids(@pkv)) 0 2 2
409 0 $p->{'errstr'} = "Bad ID Values found (@badid)";return(4);
410 }
411 2 ▶ my $widstr = whereid($p, $StoredHash::hardval ? (\@pkv) : () );
Branch: 1 branch
BranchTrueFalseTotal
$StoredHash::hardval ? : 0 2 2
412 # Persistent object type
413 2 my $pot = $p->{'table'};
414 0 ▶ if (!$pot) {die("No table for update");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($pot) 0 2 2
415 #
416 2 my $qp = "UPDATE $pot SET ".join(',', map({" $_ = ?";} @ea)).
417 " WHERE $widstr";
418 2 my $dbh = $p->{'dbh'};
419
420 2 ▶ if (my $hv = $StoredHash::hardval) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $hv = $StoredHash::hardval) 0 2 2
421 #my $quote = $p->{'dbh'} ? ref($p->{'dbh'}).'::quote' : \&quote;
422 #TODO:
423 0 ▶ my $quoter = $p->{'dbh'}->can('quote') ? sub {$p->{'dbh'}->quote($_[0]);} : \&quote;
Subroutine __ANON__: not called CC=1 SCAR=6.9
Branch: 1 branch
BranchTrueFalseTotal
$p->{'dbh'}->can('quote') ? : 0 0 0
424 my $set = join(',', map({
425 #" $_ = ".$dbh->quote($e->{$_});
426 0 " $_ = ".$quoter->($e->{$_});
427 } @ea) );
428 0 $qp = "UPDATE $pot SET $set WHERE $widstr"; # hard values embedded by whereid()
429 0 ▶ if (!@pkv) {die("No ID:s for hardval=$StoredHash::hardval");}
Branch: 1 branch
BranchTrueFalseTotal
unless (@pkv) 0 0 0
430 0 ▶ if ($hv == 2) {return($qp);}
Branch: 2 branches
BranchTrueFalseTotal
if ($hv == 2) { } 0 0 0
elsif ($hv == 1) { } 0 0 0
431 #elsif ($hv == 1) {return $dbh->do($qp);}
432 elsif ($hv == 1) {
433 0 my $ok = eval {$dbh->do($qp);};
434 0 ▶ if (!$ok) {die("Error DO(SQL: $qp): ".$@);}
Branch: 1 branch
BranchTrueFalseTotal
unless ($ok) 0 0 0
435 0 return($ok);
436 }
437 }
438 # Combine Entry attr values and primary key values
439 2 my $allv = [@$e{@ea}, @pkv];
440 0 ▶ if ($p->{'debug'}) {print("Update allvals: ".Dumper($allv)."\n");}
Branch: 1 branch
BranchTrueFalseTotal
if ($p->{"debug"}) 0 2 2
441 2 ▶ if (!$p->{'dbh'}) {return($qp);}
Branch: 1 branch
BranchTrueFalseTotal
unless ($p->{"dbh"}) 2 0 2
442 0 my $ok;
443 0 eval {
444 0 $ok = $p->qexecute($qp, $allv);
445 };
446 0 ▶ if ($@) {die("Error Executing: ".$p->{'dbh'}->errstr()."\n");}
Branch: 1 branch
BranchTrueFalseTotal
if ($@) 0 0 0
447 # Check all natural IDs (separate case for composite ?)
448 #if (!$p->{'autoid'}) {
449 #
450 #}
451 0 return($ok);
452 }
453  
454 =head2 $shp->delete($ids) OR $shp->delete($filter)
455  
456 Delete an entry from database by passing one of the following:
457  
458 =over 4
459  
460 =item * $ids - array with ID(s) for entry to be deleted (the usual use-case)
461  
462 =item * $filter - a hash with a where filter condition to delete by.
463  
464 =back
465  
466 Note that passing $filter haphazardly can cause massive destruction. Try to stick with passing $ids.
467  
468 =cut
469  
470 # (filter) ... containing (all) primary key(s) and their values)
471 #=item * array @$e - One or many primary key values for entry to be deleted
472 #The recommended use is case "array" as it is most versatile and most
473 #consistent with other API methods.
474  
475 2 ▶sub delete {
Subroutine delete: called CC=10 SCAR=30.1
Pod documented
476 2 my ($p, $e) = @_;
477 #if (!ref($p->{'pkey'})) {die("PKA Not Known");}
478 #eval {$p->validate();};if ($@) {$p->{'errstr'} = $@;return(1);}
479 #my @pka = @{$p->{'pkey'}};
480 2 my @pka = pkeys($p);
481 0 ▶ if (!$e) {die("Must have ID or filter for delete()\n");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($e) 0 2 2
482
483 2 my $rt = reftype($e); # Allows blessed
484 2 my $pkc = $p->pkeycnt();
485 2 my @pkv;my $wstr;
486 # $e Scalar, must have 1 pkey. Allow this forgiving behaviour for now.
487 0 ▶ if (!$rt && ($pkc == 1)) {$e = [$e];$rt = 'ARRAY';} # OLD: {@pkv = $e;}
Branch: 1 branch
BranchTrueFalseTotal
if (not $rt and $pkc == 1) 0 2 2
Condition33%1/3
not $rt and $pkc == 1
!ll&&!rl&&r
200
MC/DC0%0/2
not $rt and $pkc == 1
not $rt $pkc == 1
Truth table33%1/3
not(A) and B
A= not $rtB= $pkc == 1
ABresult
0X0
100
111
488 # Hash - OLD: extract primary keys @pkv = @$e{@pka};
489 # NEW: treat as filter
490 2 ▶ if ($rt eq 'HASH') {
Branch: 2 branches
BranchTrueFalseTotal
if ($rt eq 'HASH') { } 2 0 2
elsif ($rt eq 'ARRAY' and $pkc == scalar @$e) { } 0 0 0
491 0 ▶ if (!%$e) {die("Will not delete by empty filter (HASH) !");}
Branch: 1 branch
BranchTrueFalseTotal
unless (%$e) 0 2 2
492 # TODO: Share filter-case with load(), count()
493 #my @ks = sort(keys(%$e));
494 2 my @ks = grep({!ref($e->{$_})} keys(%$e));
495 2 @pkv = @$e{@ks}; # In this context @vs => @pkv - Not really vals for primary keys, but filter
496 #NOT:$wstr = wherefilter($e);
497 2 $wstr = join(' AND ', map({"$_ = ?";} @ks));
498 } #
499 # Array (of pk values) - check count matches
500 ▶ elsif (($rt eq 'ARRAY') && ($pkc == scalar(@$e))) {
Condition0%0/3
$rt eq "ARRAY" and $pkc == scalar @$e
!ll&&!rl&&r
000
MC/DC0%0/2
$rt eq "ARRAY" and $pkc == scalar @$e
$rt eq "ARRAY" $pkc == scalar @$e
Truth table0%0/3
A and B
A= $rt eq "ARRAY"B= $pkc == scalar @$e
ABresult
0X0
100
111
501 0 @pkv = @$e;
502 0 $wstr = whereid($p);
503 }
504 0 else {die("No way to delete (without ARRAY for IDs or HASH for filter)\n");}
505 #NOTNEEDED:#my %pkh;@pkh{@pka} = @pkv;
506 #my $wstr = join(' AND ', map({"$_ = ?";} @pka));
507 0 ▶ if (!$wstr) {die("Not proceding to delete with empty filter !");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($wstr) 0 2 2
508 2 my $qp = "DELETE FROM $p->{'table'} WHERE $wstr";
509 2 ▶ if (!$p->{'dbh'}) {return($qp);}
Branch: 1 branch
BranchTrueFalseTotal
unless ($p->{"dbh"}) 2 0 2
510 0 $p->qexecute($qp, \@pkv);
511 }
512 #my $dbh = $p->{'dbh'};
513 #my $sth = $dbh->prepare($qp);
514 #if (!$sth) {print("Not prepared\n");}
515 #$sth->execute(@pkv);
516  
517 =head2 $shp->exists($ids)
518  
519 Test if an entry exists in the DB table with ID values passed in @$ids (array).
520 Returns 1 (entry exists) or 0 (does not exist) under normal conditions.
521  
522 =cut
523 2 ▶sub exists {
Subroutine exists: called CC=3 SCAR=11.8
Pod documented
524 2 my ($p, $ids) = @_;
525 2 ▶ my $whereid = $p->{'where'} ? $p->{'where'} : whereid($p);
Branch: 1 branch
BranchTrueFalseTotal
$p->{'where'} ? : 2 0 2
526 2 my $qp = "SELECT COUNT(*) FROM $p->{'table'} WHERE $whereid";
527 2 ▶ if (!$p->{'dbh'}) {return($qp);}
Branch: 1 branch
BranchTrueFalseTotal
unless ($p->{"dbh"}) 2 0 2
528 0 $p->qexecute($qp, $ids, 'count');
529 }
530  
531 =head2 $shp->load($ids)
532  
533 Load entry from DB table by its IDs passed in @$ids (array,
534 single id typical sequece autoid pkey, multiple for composite primary key).
535  
536 Entry will be loaded from single table passed at construction
537 (never as result of join from multiple tables).
538 Return entry as a hash (ref).
539  
540 =cut
541 2 ▶sub load {
Subroutine load: called CC=6 SCAR=29.4
Pod documented
542 2 my ($p, $ids) = @_;
543 2 ▶ my $whereid = $p->{'where'} ? $p->{'where'} : whereid($p);
Branch: 1 branch
BranchTrueFalseTotal
$p->{'where'} ? : 2 0 2
544 # Allow loading unique entry generic filter
545 2 ▶ if (reftype($ids) eq 'HASH') {
Branch: 1 branch
BranchTrueFalseTotal
if (builtin::reftype($ids) eq "HASH") 0 2 2
546 #$whereid = wherefilter($ids);
547 0 my @ks = grep({!ref($ids->{$_})} keys(%$ids));
548 0 my @vs = @$ids{@ks};
549 0 $whereid = join(' AND ', map({"$_ = ?";} @ks));
550 0 $ids = \@vs;
551 # Need hard (unique values) for Certain DBs
552 # TODO: Move to neater abstraction for $StoredHash::hardval
553 0 ▶ if (my $hv = $StoredHash::hardval && $p->{'dbh'}) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $hv = $StoredHash::hardval && $p->{"dbh"}) 0 0 0
Condition0%0/3
$StoredHash::hardval && $p->{'dbh'}
!ll&&!rl&&r
000
MC/DC0%0/2
$StoredHash::hardval && $p->{'dbh'}
$StoredHash::hardval $p->{'dbh'}
Truth table0%0/3
A && B
A= $StoredHash::hardvalB= $p->{'dbh'}
ABresult
0X0
100
111
554 0 my $i = -1;
555 0 my $dbh = $p->{'dbh'};
556 0 $whereid = join(' AND ', map({$i++;"$_ = ".$dbh->quote($vs[$i]);} @ks));
557 0 $ids = undef;
558 }
559 }
560 2 my $qp = "SELECT * FROM $p->{'table'} WHERE $whereid";
561 2 ▶ if (!$p->{'dbh'}) {return($qp);}
Branch: 1 branch
BranchTrueFalseTotal
unless ($p->{"dbh"}) 2 0 2
562 0 $p->qexecute($qp, $ids, 'hash');
563 #if (my $c = $p->{'class'}) {return(bless($h, $c));}
564 }
565  
566 =head2 $shp->loadset($filter, $sort, %opts);
567  
568 Load a set of Entries from persistent storage.
569 Optionally provide simple "where filter hash" ($filter), whose key-value criteria
570 is ANDed together to form the filter. Allow attibutes (in $sort, arrayref) to define sorting for entry set.
571 Allow %opts to contain 'attrs' (arrayref) to explicitly to define ettributes to load for each entry.
572 Return set / collection of entries as array of hashes (AoH).
573  
574 =cut
575 0 ▶sub loadset {
Subroutine loadset: not called CC=7 SCAR=40.3
Pod documented
576 0 my ($p, $h, $sort, %c) = @_; # filter, sortby
577 0 my $w = '';
578 0 my $s = '';
579 # if (@_ = 2 && ref($_[1]) eq 'HASH') {}
580 0 ▶ if ($h) {
Branch: 1 branch
BranchTrueFalseTotal
if ($h) 0 0 0
581 0 my $vals = []; # Parameteric values
582 0 my $wf = wherefilter($h); # 'vals' => $vals
583 0 ▶ if (!$wf) {die("Empty Filter !");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($wf) 0 0 0
584 0 $w = " WHERE $wf";
585 }
586 # TODO: How to trigger DESC sorting (Something in %c OR first or last elem of $sort) ?
587 0 ▶ if (ref($sort) && @$sort) {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $sort and @$sort) 0 0 0
Condition0%0/3
ref $sort and @$sort
!ll&&!rl&&r
000
MC/DC0%0/2
ref $sort and @$sort
ref $sort @$sort
Truth table0%0/3
A and B
A= ref $sortB= @$sort
ABresult
0X0
100
111
588 0 my $stype = ''; # Default in SQL:
589 #if ($sort->[0] eq '') {}
590 0 $s = ' ORDER BY '.join(',', @$sort);
591 }
592 0 ▶ if ($p->{'debug'}) {print("Loading set by '$w'\n");}
Branch: 1 branch
BranchTrueFalseTotal
if ($p->{"debug"}) 0 0 0
593 0 my $fldstr = '*';
594 0 ▶ if (ref($c{'attrs'}) eq 'ARRAY') {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $c{"attrs"} eq "ARRAY") 0 0 0
595 0 $fldstr = join(',', @{$c{'attrs'}});
596 }
597 0 my $qp = "SELECT $fldstr FROM $p->{'table'} $w $s";
598 # Clean up query by (?):
599 0 $qp =~ s/\s+$//;
600 0 $p->qexecute($qp, undef, 'aoh');
601 }
602 =head2 $shp->cols(%opts)
603  
604 Sample Column names from (current) DB table.
605 Return (ref to) array with field names in it.
606  
607 %opts may contain KW parameter 'full' to get full DBI column_info() structure (See DBI for details).
608  
609 =cut
610 0 ▶sub cols {
Subroutine cols: not called CC=3 SCAR=24.8
Pod undocumented
611 0 my ($p, %c) = @_;
612
613 # Alternative for full table schema info
614 # TODO: 'fullinfo' => 1 or 'meta'
615 0 ▶ if ($c{'full'}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($c{"full"}) 0 0 0
616 0 my $dbh = $p->{'dbh'};
617 0 my $sth = $dbh->column_info(undef, undef, $p->{'table'}, '%');
618 0 my $fullinfo = $sth->fetchall_arrayref({});
619 0 $sth->finish();
620 0 return($fullinfo);
621 }
622 # Likely Most portable way of quering cols
623 0 my $qp = "SELECT * FROM $p->{'table'} WHERE 1 = 0";
624 0 my $sth = $p->qexecute($qp, undef, 'sth');
625 0 my $cols = $sth->{'NAME'};
626 0 ▶ if (@_ == 1) {$sth->finish();return($cols);}
Branch: 1 branch
BranchTrueFalseTotal
if (@_ == 1) 0 0 0
627 #elsif (@_ == 2) {$rett = $_[1];};
628 #if ($rett ne 'meta') {return(undef);}
629 0 return(undef);
630 }
631  
632 # TODO: Load "tree" of entries rooted at an entry / entries (?)
633 # Returns a set (array) of entries or single (root entry if
634 # option $c{'fsingle'} - force single - is set.
635 0 ▶sub loadtree {
Subroutine loadtree: not called CC=3 SCAR=24.8
Pod undocumented
636 0 my ($p, %c) = @_;
637 0 my $chts = $c{'ctypes'};
638 0 my $w = $c{'w'};
639 0 my $fsingle = $c{'fsingle'}; # singleroot, uniroot
640 0 my $arr = loadset($p, $w);
641 0 for my $e (@$arr) {my $err = loadchildern($p, $e, %c);}
642 # Choose return type
643 0 ▶ if ($fsingle) {return($arr->[0]);}
Branch: 1 branch
BranchTrueFalseTotal
if ($fsingle) 0 0 0
644 0 return($arr);
645 }
646  
647 # TODO: Load Instances of child object types for entry.
648 # Child types are defined in 'ctypes' array(ref) in options.
649 # Array 'ctypes' may be one of the following
650 #=item * Plain child type names (array of scalars), the rest is guessed
651 #=item * Array of child type definition hashes with hashes defining following:
652 # =item * table - The table / objectspace of child type
653 # =item * parkey - Parent id field in child ("foreign key" field in rel DBs)
654 # =item * memname - Mamber name to place the child collection into in parent entry
655 #=item * Array of arrays with inner arrays containing 'table','parkey','memname' in
656 # that order(!), (see above for meanings)
657 # Return 0 for no errors
658  
659 # TODO: Maintain persister cache with rudimentary relational info.
660 0 ▶sub loadchildren {
Subroutine loadchildren: not called CC=11 SCAR=48.8
Pod undocumented
661 0 my ($p, $e, %c) = @_;
662 0 my $chts = $c{'ctypes'};
663 0 ▶ if (!$chts) {die("No Child types indicated");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($chts) 0 0 0
664 0 ▶ if (ref($chts) ne 'ARRAY') {die("Child types not ARRAY");}
Branch: 1 branch
BranchTrueFalseTotal
if (ref $chts ne "ARRAY") 0 0 0
665 0 my @ids = pkeyvals($p, $e);
666 0 ▶ if (@ids > 1) {die("Loading not supported for composite keys");}
Branch: 1 branch
BranchTrueFalseTotal
if (@ids > 1) 0 0 0
667 0 my $dbh = $p->{'dbh'};
668 0 my $debug = $p->{'debug'};
669 0 for (@$chts) {
670 #my $ct = $_;
671 0 my $cfilter;
672 # Use or create a complete hash ?
673 0 my $cinfo = makecinfo($p, $_);
674 0 ▶ if ($debug) {print(Dumper($cinfo));}
Branch: 1 branch
BranchTrueFalseTotal
if ($debug) 0 0 0
675 # Load type by created filter
676 0 my ($ct, $park, $memn) = @$cinfo{'table','parkey','memname',};
677 0 ▶ if (!$park) {}
Branch: 1 branch
BranchTrueFalseTotal
unless ($park) 0 0 0
678 # Create where by parkey info
679 #$cfilter = {$park => $ids[0]}; # What is par key - assume same as parent
680 0 ▶ if (@$park != @ids) {die("Par and child key counts mismatch");}
Branch: 1 branch
BranchTrueFalseTotal
if (@$park != @ids) 0 0 0
681 0 @$cfilter{@$park} = @ids;
682 #my $cfilter =
683 # Take a shortcut by not providing pkey
684 0 my $shc = StoredHash->new('table' => $ct, 'pkey' => [],
685 'dbh' => $dbh, 'loose' => 1, 'debug' => $debug);
686 0 my $carr = $shc->loadset($cfilter);
687 0 ▶ if (!$carr || !@$carr) {next;}
Branch: 1 branch
BranchTrueFalseTotal
unless ($carr and @$carr) 0 0 0
Condition0%0/3
$carr and @$carr
!ll&&!rl&&r
000
MC/DC0%0/2
$carr and @$carr
$carr @$carr
Truth table0%0/3
A and B
A= $carrB= @$carr
ABresult
0X0
100
111
688 #if ($debug) {print("Got Children".Dumper($arr));}
689 0 $e->{$memn} = $carr;
690 # Blessing
691 0 ▶ if (my $bto = $cinfo->{'blessto'}) {map({bless($_, $bto);} @$carr);}
Branch: 1 branch
BranchTrueFalseTotal
if (my $bto = $cinfo->{"blessto"}) 0 0 0
692 # Circular Ref from child to parent ?
693 #if (my $pla = $cinfo->{'parlinkattr'}) {map({$_->{$pla} = $e;} @$carr);}
694 }
695 # Autobless Children ?
696 0 return(0);
697 }
698  
699  
700 # Internal method for using or making up Child relationship information
701 # for loading related entities.
702 0 ▶sub makecinfo {
Subroutine makecinfo: not called CC=8 SCAR=42.8
Pod undocumented
703 0 my ($p, $cv) = @_;
704 # Support array with: 'table','parkey','memname'
705 0 ▶ if (ref($cv) eq 'ARRAY') {
Branch: 3 branches
BranchTrueFalseTotal
if (ref $cv eq 'ARRAY') { } 0 0 0
elsif (ref $cv eq 'HASH') { } 0 0 0
elsif (ref $cv ne '') { } 0 0 0
706 0 my $cinfo;
707 0 ▶ if (@$cv != 3) {die("Need table, parkey, memname in array");}
Branch: 1 branch
BranchTrueFalseTotal
if (@$cv != 3) 0 0 0
708 0 @$cinfo{'table','parkey','memname'} = @$cv;
709 0 return($cinfo);
710 }
711 # Assume all is there (could validate and provide missing)
712 elsif (ref($cv) eq 'HASH') {
713 0 my @a = ('table','parkey','memname');
714 # Try guess parkey ?
715 0 ▶ if (!$cv->{'parkey'}) {$cv->{'parkey'} = [pkeys($p)];}
Branch: 1 branch
BranchTrueFalseTotal
unless ($cv->{"parkey"}) 0 0 0
716 0 ▶ for (@a) {if (!$cv->{$_}) {die("Missing '$_' in cinfo");}}
Branch: 1 branch
BranchTrueFalseTotal
unless ($cv->{$_}) 0 0 0
717 0 return($cv);
718 }
719 0 elsif (ref($cv) ne '') {die("child type Not scalar (or hash)");}
720 ################## Make up
721 0 my $ctab = $cv;
722 0 my $memname = $ctab; # Default memname to child type name (Plus 's') ?
723 # Guess by parent
724 0 my $parkey = [pkeys($p)];
725 0 my $cinfo = {'table' => $ctab, 'parkey' => $parkey, 'memname' => $ctab,};
726 0 return($cinfo);
727 }
728  
729 ###################################################################
730  
731 # Internal Persister validator for the absolutely mandatory properties of
732 # persister object itself.
733 # Doesn't not validate entry
734 2 ▶sub validate {
Subroutine validate: called CC=3 SCAR=13.3
Pod undocumented
735 2 my ($p) = @_;
736 0 ▶ if (ref($p->{'pkey'}) ne 'ARRAY') {die("PK Attributes Not Known\n");}
Branch: 1 branch
BranchTrueFalseTotal
if (ref $p->{"pkey"} ne "ARRAY") 0 2 2
737 # Allow table to come from blessing (so NOT required)
738 #if (!$p->{'table'}) {die("No Table\n");}
739 0 ▶ if ($p->{'simu'}) {return;}
Branch: 1 branch
BranchTrueFalseTotal
if ($p->{"simu"}) 0 2 2
740 # Do NOT Require conenction
741 #if (!ref($p->{'dbh'})) {die("NO dbh to act on\n");} # ne 'DBI'
742
743 }
744  
745 #=head2 @pka = $shp->pkeys()
746 #
747 # Internal method for returning array of id keys (Real array, not ref).
748 #
749 #=cut
750 11 ▶sub pkeys {
Subroutine pkeys: called CC=3 SCAR=13.1
Pod undocumented
751 11 my ($p) = @_;
752 11 my $prt = reftype($p);
753 11 ▶ if ($prt ne 'HASH') {
Branch: 1 branch
BranchTrueFalseTotal
if ($prt ne "HASH") 0 11 11
754 0 $|=1;
755 0 print(STDERR Dumper([caller(1)]));
756 0 die("StoredHash Not a HASH (is '$p'/'$prt')");
757 }
758 # Excessive validation ?
759 0 ▶ if (ref($p->{'pkey'}) ne 'ARRAY') {die("Primary keys not in an array");}
Branch: 1 branch
BranchTrueFalseTotal
if (ref $p->{"pkey"} ne "ARRAY") 0 11 11
760 #return($p->{'pkey'});
761 11 return(@{$p->{'pkey'}});
762 }
763  
764 =head2 $shp->count($filter)
765  
766 Get Count of all or a filtered set of entries (by optional $filter) in table.
767 Return (scalar) count of entries.
768  
769 =cut
770 4 ▶sub count {
Subroutine count: called CC=5 SCAR=17.0
Pod documented
771 4 my ($p, $fh) = @_; # $fh - Filter Hash
772 4 my $qc = "SELECT COUNT(*) FROM $p->{'table'}";
773 # TODO: See filter case for load(), delete()
774 # Use it and replace 2nd param of qexecute w. params
775 4 ▶ if (ref($fh) eq 'HASH' && keys(%$fh)) {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $fh eq "HASH" and keys %$fh) 2 2 4
Condition66%2/3
ref $fh eq "HASH" and keys %$fh
!ll&&!rl&&r
202
MC/DC50%1/2
ref $fh eq "HASH" and keys %$fh
ref $fh eq "HASH" keys %$fh
Truth table66%2/3
A and B
A= ref $fh eq "HASH"B= keys %$fh
ABresult
0X0
100
111
776 2 my $w = wherefilter($fh); # my ($w, $vals) = wherefilter_para($fh);
777 2 $qc .= " WHERE $w";
778 }
779 0 ▶ if ($p->{'debug'}) {print("Count Query:$qc\n");}
Branch: 1 branch
BranchTrueFalseTotal
if ($p->{"debug"}) 0 4 4
780 4 ▶ if (!$p->{'dbh'}) {return($qc);}
Branch: 1 branch
BranchTrueFalseTotal
unless ($p->{"dbh"}) 4 0 4
781 0 $p->qexecute($qc, undef, 'count'); # $vals
782 }
783  
784 =head1 INTERNAL METHODS
785  
786 These methods you should not need working on the high level. However for the curious they are outlined here.
787  
788 =head2 @pkv = $shp->pkeyvals($e)
789  
790 Return Primary key values (as "real" array, not ref to one) from hash %$e.
791 undef values are produced for non-existing keys.
792 Mostly used for internal operations (and possibly debugging).
793  
794 =cut
795 0 ▶sub pkeyvals {
Subroutine pkeyvals: not called CC=1 SCAR=6.9
Pod documented
796 0 my ($p, $e) = @_;
797 0 my @pkeys = pkeys($p);
798 0 @$e{@pkeys};
799 }
800  
801 # TODO: Implement pulling last id from sequence
802 0 ▶sub fetchautoid {
Subroutine fetchautoid: not called CC=3 SCAR=24.8
Pod undocumented
803 0 my ($p) = @_;
804 0 my $dbh;
805 #$dbh->{'Driver'}; # Need to test ?
806 #DEV:print("AUTOID FETCH TO BE IMPLEMENTED\n");return(69);
807 0 my $pot = $p->{'table'};
808 0 ▶ if (!$pot) {die("No table for fetching auto-ID");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($pot) 0 0 0
809 0 ▶ if (!($dbh = $p->{'dbh'})) {die("No Connection for fetching ID");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($dbh = $p->{"dbh"}) 0 0 0
810 0 $dbh->last_insert_id(undef, undef, $pot, undef);
811 }
812  
813 2 ▶sub pkeycnt {
Subroutine pkeycnt: called CC=1 SCAR=0.0
Pod undocumented
814 2 my ($p) = @_;
815 #if (ref($p->{'pkey'}) ne 'ARRAY') {die("Primary keys not in an array");}
816 #scalar(@{$p->{'pkey'}});
817 2 my @pkeys = pkeys($p);
818 2 scalar(@pkeys);
819 }
820  
821 # Internal method for checking for empty or undefined ID values.
822 # In all reasonable databases and apps these are not valid values.
823 2 ▶sub invalidids {
Subroutine invalidids: called CC=2 SCAR=7.4
Pod undocumented
824 2 my ($p, @idv) = @_;
825 2 ▶ my @badid = grep({!defined($_) || $_ eq '';} @idv);
Condition33%1/3
!defined($_) || $_ eq ''
l!l&&r!l&&!r
003
MC/DC0%0/2
!defined($_) || $_ eq ''
!defined($_) $_ eq ''
Truth table33%1/3
not(A) || B
A= !defined($_)B= $_ eq ''
ABresult
1X1
011
000
826 2 return(@badid);
827 }
828  
829 =head2 $shp->whereid($pkvals);
830  
831 Generate SQL WHERE Clause for update() (or delete() or load() or exists()) based on primary keys of current (table) type.
832 Return WHERE clause with id-attribute(s) and placeholder(s) (idkey = ?, ...), without the WHERE keyword.
833 Mostly called for internal operations. You should not need this.
834  
835 =cut
836 5 ▶sub whereid {
Subroutine whereid: called CC=6 SCAR=27.1
Pod documented
837 5 my ($p, $pkval) = @_;
838 # # Allow IDs to be hash OR array ?? Not because hash would req. to store order
839 5 my @pka = pkeys($p);
840 0 ▶ if (@pka < 1) {die("No Pkeys to create where ID clause");}
Branch: 1 branch
BranchTrueFalseTotal
if (@pka < 1) 0 5 5
841 # my $wstr =
842 5 ▶ if ($pkval && (ref($pkval) eq 'ARRAY') && (@$pkval == @pka) ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($pkval and ref $pkval eq "ARRAY" and @$pkval == @pka) 0 5 5
Condition33%2/6
$pkval and ref $pkval eq "ARRAY" and @$pkval == @pka
!ll&&!rl&&r
500
$pkval and ref $pkval eq "ARRAY"
!ll&&!rl&&r
500
MC/DC0%0/3
$pkval and ref $pkval eq "ARRAY" and @$pkval == @pka
$pkval ref $pkval eq "ARRAY" @$pkval == @pka
Truth table25%1/4
A and B and C
A= $pkvalB= ref $pkval eq "ARRAY"C= @$pkval == @pka
ABCresult
0XX0
10X0
1100
1111
843 # TODO: Mock DBI
844 0 my $dbh = $p->{'dbh'};
845 # Can't use string ("DBI::db::quote") as a subroutine ref while "strict refs" in use
846 my $quote = $p->{'dbh'} ?
847 #ref($p->{'dbh'}).'::quote'
848 0 ▶ sub {$p->{'dbh'}->quote($_[0]);}
Subroutine __ANON__: not called CC=1 SCAR=6.9
849 0 ▶ : \&quote;
Branch: 1 branch
BranchTrueFalseTotal
$p->{'dbh'} ? : 0 0 0
850 0 my $i = -1;
851 #no strict 'refs';
852 my $wif = join(' AND ', map({
853 0 $i++;"$_ = ".$quote->($pkval->[$i]);
854 } @pka));
855 0 return $wif; # OLD (nostrict) $wid
856 }
857 5 return join(' AND ', map({"$_ = ?";} @pka));
858 }
859  
860 # Internal fallback method to escape (string) value. Prefer using $dbh->quote() if $dbh is
861 # handle available and the associated DBD Driver supports it.
862 # The first parameter to this is an unused dummy parameter to match call of $dbh->quote($str).
863 # This does not place surrounding quotes on the value returned.
864 # Return value properly escaped.
865 # TODO: Cover all scenarios
866 2 ▶sub sqlvalesc {
Subroutine sqlvalesc: called CC=1 SCAR=0.0
Pod undocumented
867 2 my ($foo, $v) = @_;
868 #$v =~ s/'/\\'/g; # $str =~ s/'/''/g;
869 2 $v =~ s/\'/\'\'/g;
870 2 $v =~ s/\n/\\n/g;
871 2 $v;
872 }
873  
874 # TODO: Create list for WHERE IN Clause based on some assumptions
875 0 ▶sub invalues {
Subroutine invalues: not called CC=3 SCAR=24.8
Pod undocumented
876 0 my ($vals) = @_;
877 # Assume array ref validated outside
878 0 ▶ if (ref($vals) ne 'ARRAY') {die("Not an array for invals");}
Branch: 1 branch
BranchTrueFalseTotal
if (ref $vals ne "ARRAY") 0 0 0
879 # Escape within Quotes ?
880 join(',', map({
881 0 ▶ if (/^\d+$/) {$_;}
Branch: 1 branch
BranchTrueFalseTotal
if (/^\d+$/) { } 0 0 0
882 else {
883 0 my $v = sqlvalesc(undef, $_);
884 0 "'$v'";
885 }
886 } @$vals));
887 }
888  
889 0 ▶sub rangefilter {
Subroutine rangefilter: not called CC=8 SCAR=42.8
Pod undocumented
890 0 my ($attr, $v) = @_;
891 0 ▶ if (ref($v) ne 'ARRAY') {die("Need value range as ARRAY of 2 elems");}
Branch: 1 branch
BranchTrueFalseTotal
if (ref $v ne "ARRAY") 0 0 0
892 # Or just even and sort, grab 2 at the time ?
893 0 ▶ if (@$v != 2) {die("Range cannot be formed - need exactly 2 elements");}
Branch: 1 branch
BranchTrueFalseTotal
if (@$v != 2) 0 0 0
894 0 ▶ if (!defined($v->[0]) || !defined($v->[1]) ) {die("Missing either of the values ($v->[0], $v->[0])");}
Branch: 1 branch
BranchTrueFalseTotal
unless (defined $v->[0] and defined $v->[1]) 0 0 0
Condition0%0/3
defined $v->[0] and defined $v->[1]
!ll&&!rl&&r
000
MC/DC0%0/2
defined $v->[0] and defined $v->[1]
defined $v->[0] defined $v->[1]
Truth table0%0/3
A and B
A= defined $v->[0]B= defined $v->[1]
ABresult
0X0
100
111
895 # Auto-arrange ??? Test for both being numbers
896 0 ▶ my @nums = map({Scalar::Util::looks_like_number($_) ? (1) : ();} @$v);
Branch: 1 branch
BranchTrueFalseTotal
&Scalar::Util::looks_like_number($_) ? : 0 0 0
897 0 ▶ if (@nums == 2) {
Branch: 1 branch
BranchTrueFalseTotal
if (@nums == 2) 0 0 0
898 0 ▶ if ($v->[1] < $v->[0]) {$v = [$v->[1],$v->[0]];}
Branch: 1 branch
BranchTrueFalseTotal
if ($v->[1] < $v->[0]) 0 0 0
899 }
900 # Detect need to escape (time vs. number)
901 0 return "($attr >= $v->[0]) AND ($attr <= $v->[1])";
902 #return " $attr BETWEEN $v->[0] AND $v->[1]";
903 }
904  
905 #=head2 StoredHash::wherefilter($e,%c);
906 #
907 # Generate simple WHERE filter by hash %$e. The keys are assumed to be attributes
908 # of DB and values are embedded as values into SQL (as opposed to using placeholers).
909 # To be perfect in escaping per attribute type info would be needed.
910 # For now we do best effort heuristics (attr val \d+ is assumed
911 # to be a numeric field in SQL, however 000002345 could actually
912 # be content of a char/text/varchar field).
913 # Return WHERE filter clause without WHERE keyword.
914 2 ▶sub wherefilter {
Subroutine wherefilter: called CC=8 SCAR=31.0
Pod undocumented
915 2 my ($e, %c) = @_;
916 2 my $w = '';
917 2 my $fop = ' AND ';
918 #my $rnga = $c{'rnga'}; # Range attributes
919 0 ▶ if (ref($e) ne 'HASH') {die("No hash for filter generation");}
Branch: 1 branch
BranchTrueFalseTotal
if (ref $e ne "HASH") 0 2 2
920 # Ensure deterministic order
921 2 my @keys = sort keys(%$e);
922 2 my @qc; # Query Components
923 # Assume hard values, treat everything as string (?)
924 # TODO: forcestr ?
925 @qc = map({
926 2 my $v = $e->{$_};
927 #my $rv = ref($v);
928 #if ($rnga->{$_} && ($rv eq 'ARRAY') && (@$v == 2)) {rangefilter($_, $v);}
929 # For now, assume IN - clause
930 0 ▶ if (ref($v) eq 'ARRAY') {" $_ IN (".invalues($v).") ";}
Branch: 3 branches
BranchTrueFalseTotal
if (ref $v eq 'ARRAY') { } 0 2 2
elsif ($v =~ /%/) { } 0 2 2
elsif ($v =~ /^\d+$/) { } 0 2 2
931 # SQL Wildcard
932 0 elsif ($v =~ /%/) {"$_ LIKE '$v'";}
933 # Detect numeric (likely numeric, not perfect)
934 # TODO: Explicit param to
935 0 elsif ($v =~ /^\d+$/) {"$_ = $v";}
936 # Assume string
937 2 else {"$_ = '".sqlvalesc(undef, $v)."'";}
938
939 } @keys);
940 # Create PARAMETRIC query
941 2 ▶ if (ref $c{'vals'} eq 'ARRAY') {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $c{"vals"} eq "ARRAY") 0 2 2
942 0 my @vals = ();
943 map({
944 0 my $v = $e->{$_};
945 0 ▶ if (ref($v) eq 'ARRAY') {push(@vals, @$v);" $_ IN (".join(',', map({"?";} @$v)).") ";}
Branch: 2 branches
BranchTrueFalseTotal
if (ref $v eq 'ARRAY') { } 0 0 0
elsif ($v =~ /%/) { } 0 0 0
946 0 elsif ($v =~ /%/) {push(@vals, $v);"$_ LIKE ?";}
947 0 else {push(@vals, $v);"$_ = ?";}
948 } @keys);
949 0 push(@{$c{'vals'}}, @vals);
950 }
951 2 return(join($fop, @qc)); # join by AND
952 }
953  
954 #=head2 my ($where, $para) = wherefilter_para($e);
955 # Where filter for parametric query (for load(), delete() count())
956 # Return WHERE clause (without 'WHERE') and parametric values.
957 # Throw exception on empty %$e or ... (empty filter)
958 # Caller should not simply check the count of keys in hash as ref
959 # valued key-pairs are skipped here.
960 0 ▶sub wherefilter_para {
Subroutine wherefilter_para: not called CC=3 SCAR=24.8
Pod undocumented
961 0 my ($e) = @_;
962 0 ▶ if (!$e || !%$e) {die("Will not generate filter by no HASH / empty HASH !");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($e and %$e) 0 0 0
Condition0%0/3
$e and %$e
!ll&&!rl&&r
000
MC/DC0%0/2
$e and %$e
$e %$e
Truth table0%0/3
A and B
A= $eB= %$e
ABresult
0X0
100
111
963 0 my @ks = grep({!ref($e->{$_})} keys(%$e));
964 0 my @vs = @$e{@ks}; # In this context @vs => @pkv - Not really vals for primary keys, but filter
965 0 my $wstr = join(' AND ', map({"$_ = ?";} @ks));
966 #if (!$wstr || $wstr =~ /^\s*$/) {die("Will not generate empty filter clause");}
967 0 return($wstr, \@vs);
968 }
969  
970 # Internal: Serialize all values (singles,multi) from a hash to an array
971 # based on sorted key order. Multi-valued keys (with value being array reference)
972 # add multiple items.
973 2 ▶sub allentvals {
Subroutine allentvals: called CC=3 SCAR=13.5
Pod undocumented
974 2 my ($h) = @_;
975 map({
976 0 ▶ if (ref($h->{$_}) eq 'HASH') {();}
Branch: 2 branches
BranchTrueFalseTotal
if (ref $h->{$_} eq 'HASH') { } 0 6 6
elsif (ref $h->{$_} eq 'ARRAY') { } 0 6 6
977 0 elsif (ref($h->{$_}) eq 'ARRAY') {@{$h->{$_}};}
978 6 else {($h->{$_});}
979 } sort(keys(%$h)));
980 }
981  
982  
983 # TODO: Move to util ?
984 #=head2 $p->dbtabinfo(%opts) OR StoredHash::dbtabinfo($dbh, %opts);
985 # Covenience method for $dbh->table_info()
986 # Options:
987 #=item * tabonly - Filter out all DB Objects where TABLE_TYPE is not 'TABLE'
988 #
989 # Return AoH where each of inner hashes are info for single table. Property names are in
990 # standard DBI table_info() format (see perldoc DBI).
991 0 ▶sub dbtabinfo {
Subroutine dbtabinfo: not called CC=6 SCAR=37.4
Pod undocumented
992 0 my (%c) = @_;
993 0 my ($p, $pdbh);
994 0 my $rt = reftype($_[0]);
995 0 ▶ if ($rt eq 'StoredHash') {$p = shift();%c = @_;}
Branch: 1 branch
BranchTrueFalseTotal
if ($rt eq 'StoredHash') { } 0 0 0
996 # elsif ($rt eq '')
997 0 else {$pdbh = shift();%c = @_;}
998 0 ▶ my $dbh = $pdbh || $p->{'dbh'} || $c{'dbh'};
Condition0%0/6
$pdbh || $p->{'dbh'} || $c{'dbh'}
l!l&&r!l&&!r
000
$pdbh or $p->{"dbh"}
l!l&&r!l&&!r
000
MC/DC0%0/3
$pdbh || $p->{'dbh'} || $c{'dbh'}
$pdbh $p->{"dbh"} $c{'dbh'}
Truth table0%0/4
A or B || C
A= $pdbhB= $p->{"dbh"}C= $c{'dbh'}
ABCresult
1XX1
01X1
0011
0000
999 0 ▶ if (!$dbh) {die("No Connection for table info");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($dbh) 0 0 0
1000 0 my $sth = $dbh->table_info();
1001 0 my $tabinfo = $sth->fetchall_arrayref({}); # AoH
1002 0 $sth->finish();
1003 # Replace with $c{'all'} - Get all database objects (like views, indices ...)
1004 0 ▶ if ($c{'tabonly'}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($c{"tabonly"}) 0 0 0
1005 0 @$tabinfo = grep({$_->{'TABLE_TYPE'} eq 'TABLE';} @$tabinfo);
1006 }
1007
1008 0 return($tabinfo);
1009 }
1010 # Experimental wrapper to query attributes
1011 # TODO: dbattrinfo($dbh, $tn);
1012 0 ▶sub dbattrinfo {
Subroutine dbattrinfo: not called CC=3 SCAR=24.8
Pod undocumented
1013 0 my ($dbh, $tn) = @_;
1014 #my ($p, $pdbh);
1015 #my $rt = reftype($_[0]);
1016 #if ($rt eq 'StoredHash') {$p = shift();%c = @_;}
1017 #elsif ($rt eq '') {$pdbh = shift();%c = @_;}
1018 #my $dbh = $pdbh || $p->{'dbh'} || $c{'dbh'};
1019 0 ▶ if (!$dbh) {die("No Connection for attribute info");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($dbh) 0 0 0
1020 0 ▶ if (!$tn) {die("No table name for attribute info");}
Branch: 1 branch
BranchTrueFalseTotal
unless ($tn) 0 0 0
1021 0 my $sth = $dbh->column_info(undef, undef, $tn, '%');
1022 0 my $arr = $sth->fetchall_arrayref({}); # AoH
1023 0 $sth->finish();
1024 0 return($arr);
1025 }
1026 5 1;
« StoredHash/LD.pm ↑ Summary StoredHash/ARS.pm »