RWDE/DB/Items.pm untested

filter: Statementstmt 0.0% 0 / 239 Branchbran 0.0% 0 / 56 Conditioncond 0.0% 0 / 27 MC/DCmcdc 0.0% 0 / 18 Subroutinesub 0.0% 0 / 20 Podpod 0.0% 0 / 14 totaltotal 0.0% 0 / 374 CC 29 SCAR 39.7
CC
29
Cov
0%
generate_where
156.0
generate_ordering
56.0
fetch_by_id_set
30.0
CRAP 52.8
1 package RWDE::DB::Items;
2  
3 use strict;
4 use warnings;
5  
6 use Error qw(:try);
7  
8 use RWDE::AbstractFactory;
9 use RWDE::Exceptions;
10  
11 use vars qw($VERSION);
12 $VERSION = sprintf "%d", q$Revision: 508 $ =~ /(\d+)/;
13  
14 =pod
15  
16 =head2 fetch
17  
18 routine to fetch object data using a custom query
19 This function uses dynamic run time determination of class types, fields.
20  
21 =cut
22  
23 sub fetch {
24 my ($self, $params) = @_;
25  
26 # dynamically pull in the name of the table where our object is mapped
27 my $table = $self->get_static({ value => '_table' });
28  
29 my @fieldnames = @{ $self->get_static({ value => '_fieldnames' }) };
30  
31 my ($where, $query_params) = $self->generate_where($params);
32  
33 my $ordering = $self->generate_ordering($params);
34  
35 local ($") = ","; #";
36 my $result = $self->issue_query(
37 {
38 complete_query => "SELECT @fieldnames FROM $table $where $ordering",
39 query_params => $query_params
40 }
41 );
42  
43 my @items;
44 foreach my $row (@{$result}) {
45 push(@items, $self->fill_new({ row => $row }));
46 }
47  
48 return \@items;
49 }
50  
51 sub fetch_by_id_set {
52 my ($self, $params) = @_;
53  
54 $self->check_params({ required => ['id_set'], supplied => $params });
55  
56 return
57 unless (@{ $$params{id_set} } > 0);
58  
59 # Use the supplied identity field
60 # or get the default one for the object
61 my $id = $$params{'id'};
62  
63 $id = $self->get_static({ value => '_id' })
64 unless defined $id;
65  
66 my @ids = @{ $$params{id_set} };
67  
68 local ($") = ","; #"
69 my $query = " $id IN (@ids) ";
70  
71 if (defined $$params{query}) {
72 $$params{query} = $$params{query} . ' AND ' . $query;
73 }
74 else {
75 $$params{query} = $query;
76 }
77  
78 #FIXME make sure we have a query_params hash -- is this necessary?
79 $$params{query_params} = defined($$params{query_params}) ? $$params{query_params} : undef;
80  
81 return $self->fetch($params);
82 }
83  
84 =head2 fetch_single
85  
86 Get the single result from the database
87  
88 =cut
89  
90 sub fetch_single {
91 my ($self, $params) = @_;
92  
93 my $select = $$params{select}
94 or throw RWDE::DevelException({ info => 'No target specified for fetch_single' });
95  
96 my $table;
97 try {
98 $table = $self->get_static({ value => '_table' });
99 }
100 catch Error {
101  
102 };
103  
104 my ($where, $query_params) = $self->generate_where($params);
105  
106 my $ordering = $self->generate_ordering($params);
107  
108 my $complete_query;
109 if (defined $table) {
110 $complete_query = "SELECT $select FROM $table $where $ordering";
111 }
112 else {
113 $complete_query = "SELECT $select";
114 }
115  
116 my $result = $self->issue_query({ complete_query => $complete_query, query_params => $query_params, deny_update => 1 });
117  
118 return $$result[0][0];
119 }
120  
121 sub fetch_ids {
122 my ($self, $params) = @_;
123  
124 my (@ids, $table, $term);
125  
126 #use the supplied id or get the primary key
127 my $id = $$params{'id'} ? $$params{'id'} : $self->get_static({ value => '_id' });
128  
129 # dynamically pull in the name of the table where our object is mapped
130 $table = $self->get_static({ value => '_table' });
131  
132 my ($where, $query_params) = $self->generate_where($params);
133 my $ordering = $self->generate_ordering($params);
134  
135 my $complete_query = "SELECT $id FROM $table $where $ordering";
136  
137 my $result = $self->issue_query({ complete_query => $complete_query, query_params => $query_params });
138  
139 foreach my $row (@{$result}) {
140 push(@ids, shift @{$row});
141 }
142  
143 return \@ids;
144 }
145  
146 =pod
147 =head2 fetch_join($params)
148  
149 This function uses dynamic run time determination of class types, fields.
150 The first type passed is used for basis for join, the table it is joining with
151 has to have the id of the first one embedded. It creates associations via
152 identity fields. The objects need to have an accessor method for the
153 associated type, so that it can work even if it is not populated through
154 the Items class.
155  
156 =cut
157  
158 sub fetch_join {
159 my ($self, $params) = @_;
160  
161 $self->check_params({ required => [ 'jointype', 'joinkey' ], supplied => $params });
162  
163 my ($fields, @fieldnames, $term, $table, $jointerm, @queryfieldnames);
164 my ($joinfields, @joinfieldnames, $joinkey, $jointable, $jointable_key, @queryjoinfieldnames);
165  
166 my @items;
167 my $jointype = $$params{'jointype'};
168  
169 # dynamically require the object type we are going to try and use
170 $term = RWDE::AbstractFactory->instantiate({ class => $self });
171 $jointerm = RWDE::AbstractFactory->instantiate({ class => $jointype });
172  
173 # dynamically pull in the name of the table where our object is mapped
174 $table = $term->{_table};
175 $jointable = $jointerm->{_table};
176  
177 #typically both items will share the same name to join on
178 $joinkey = $$params{joinkey};
179  
180 #special exceptions if the 2 items to be joined do not share the same key
181 $jointable_key = $$params{jointable_key};
182  
183 # dynamically pull in the list of fieldnames we will be dealing with for that table
184 @fieldnames = @{ $term->{_fieldnames} };
185 @joinfieldnames = @{ $jointerm->{_fieldnames} };
186  
187 @queryfieldnames = @{ $term->{_fieldnames} };
188 @queryjoinfieldnames = @{ $jointerm->{_fieldnames} };
189  
190 # expand out the list of potential fieldnames for a database query
191 foreach my $f (@queryfieldnames) {
192 $f = $table . "." . $f . " AS " . $f;
193 }
194  
195 foreach my $f (@queryjoinfieldnames) {
196 $f = $jointable . "." . $f . " AS " . $f;
197 }
198  
199 $fields .= join(',', @queryfieldnames);
200 $joinfields .= join(',', @queryjoinfieldnames);
201  
202 my ($where, $query_params) = $self->generate_where($params);
203 my $ordering = $self->generate_ordering($params);
204  
205 my $join_on = (defined($jointable_key)) ? "$table.$joinkey = $jointable.$jointable_key" : "$table.$joinkey = $jointable.$joinkey";
206 my $complete_query = "SELECT $fields, $joinfields FROM $table JOIN $jointable ON ($join_on) $where $ordering";
207  
208 my $result = $self->issue_query({ complete_query => $complete_query, query_params => $query_params });
209  
210 foreach my $row (@{$result}) {
211 my $item = $self->fill_new({ row => $row });
212 my $joinitem = $jointype->fill_new({ row => $row });
213  
214 $item->{$jointype} = ($joinitem);
215 push(@items, $item);
216 }
217  
218 return \@items;
219 }
220  
221 =pod
222 =head2 join_count_query($params)
223  
224 =cut
225  
226 sub join_count_query {
227 my ($self, $params) = @_;
228  
229 $self->check_params({ required => [ 'jointype', 'joinkey' ], supplied => $params });
230  
231 my ($fields, @fieldnames, $term, $table, $jointerm, @queryfieldnames);
232 my ($joinfields, @joinfieldnames, $joinkey, $jointable, $jointable_key, @queryjoinfieldnames);
233  
234 my @items;
235  
236 # dynamically require the object type we are going to try and use
237 $term = RWDE::AbstractFactory->instantiate({ class => $self });
238 $jointerm = RWDE::AbstractFactory->instantiate({ class => $$params{'jointype'} });
239  
240 # dynamically pull in the name of the table where our object is mapped
241 $table = $term->{_table};
242 $jointable = $jointerm->{_table};
243  
244 $joinkey = $$params{joinkey};
245  
246 #special exceptions if the 2 items to be joined do not share the same key
247 $jointable_key = $$params{jointable_key};
248  
249 my ($where, $query_params) = $self->generate_where($params);
250 my $join_on = (defined($jointable_key)) ? "$table.$joinkey = $jointable.$jointable_key" : "$table.$joinkey = $jointable.$joinkey";
251 my $complete_query = "SELECT COUNT(*) FROM $table JOIN $jointable ON ($join_on) $where";
252 my $result = $self->issue_query({ complete_query => $complete_query, query_params => $query_params });
253  
254 return $$result[0][0];
255 }
256  
257 =head2 count_query
258  
259 routine to perform a count based on a custom query
260  
261 =cut
262  
263 sub count_query {
264 my ($self, $params) = @_;
265  
266 if (!defined($$params{count})) {
267 $$params{select} = 'COUNT(*)';
268 }
269 else {
270 $$params{select} = 'COUNT('.$$params{count}.')';
271 }
272  
273 # make sure that we don't accidentally pass in anything which would break the count query
274 return $self->fetch_single({ query => $$params{query}, query_params => $$params{query_params}, select => $$params{select} });
275 }
276
277 # Allow Record classes to optimize lookups: i.e. split tables
278 sub optimize_query{}
279  
280 sub issue_query {
281 my ($self, $params) = @_;
282  
283 $self->check_params({ required => ['complete_query'], supplied => $params });
284  
285 my $dbh = $self->get_dbh();
286  
287 my $query = $$params{complete_query};
288  
289 #if we are in a transaction (passively invoked by get_dbh) - then insert a "FOR UPDATE" for this call
290 if (!$$params{deny_update} && (RWDE::DB::DbRegistry->has_transaction({ db => $self->get_db() }))) {
291 $query .= ' FOR UPDATE';
292 }
293  
294 $self->optimize_query({ query => \$query, query_params => $$params{query_params} });
295  
296 my $sth = $dbh->prepare($query)
297 or throw RWDE::DevelException({ info => 'Failure to prepare query.' });
298  
299 if (defined($$params{query_params}) and scalar @{ $$params{query_params} } > 0) {
300 $sth->execute(@{ $$params{query_params} })
301 or throw RWDE::DevelException({ info => 'Failure to execute query:' . $dbh->errstr() });
302 }
303 else {
304 $sth->execute()
305 or throw RWDE::DevelException({ info => 'Failure to execute query:' . $dbh->errstr() });
306 }
307  
308 return $sth->fetchall_arrayref;
309 }
310  
311 sub fetch_count {
312 my ($self, $params) = @_;
313  
314 my $terms_ref = $self->fetch($params);
315 my $count = $self->count_query($params);
316  
317 return ($terms_ref, $count);
318 }
319  
320 sub fetch_join_count {
321 my ($self, $params) = @_;
322  
323 my $result = $self->fetch_join($params);
324 my $result_count = $self->join_count_query($params);
325  
326 return ($result, $result_count);
327 }
328  
329 # Return objects in a hash structure indexed by term's id
330 sub fetch_hash {
331 my ($self, $params) = @_;
332  
333 my $term_hash = {};
334  
335 foreach my $term (@{ $self->fetch($params) }) {
336 $$term_hash{ $term->get_id } = $term;
337 }
338  
339 return $term_hash;
340 }
341  
342 sub generate_ordering {
343 my ($self, $params) = @_;
344  
345 my $ordering = '';
346  
347 # Handle paging if necessary
348 if ($$params{order}) {
349 $ordering .= " ORDER BY $$params{order} ";
350  
351 #ascending is the default unless this is declared
352 if (defined($$params{ordering})) {
353 $ordering .= $$params{ordering};
354 }
355 }
356  
357 if (defined $$params{startidx}) {
358 my $startidx = $$params{startidx};
359 $startidx = 0 if ($startidx < 0);
360 $ordering .= " OFFSET $startidx ";
361 }
362  
363 if (defined $$params{maxreturn}) {
364 my $maxreturn = $$params{maxreturn};
365 $maxreturn += 0;
366 $maxreturn = 50 if ($maxreturn > 200 or $maxreturn < 1);
367 $ordering .= " LIMIT $maxreturn ";
368 }
369  
370 return $ordering;
371 }
372  
373 sub generate_where {
374 my ($self, $params) = @_;
375  
376 my $where = '';
377 my @query_params;
378  
379 #either we have an SQL query passed with params
380 if (defined($$params{query}) || defined $$params{query_params}) {
381 $where = $$params{query};
382  
383 if (defined $$params{query_params}) {
384 @query_params = @{ $$params{query_params} };
385 }
386  
387 }
388  
389 #or we have a bunch of search_fields we should sift through
390 #and generate a query
391 elsif (defined($$params{search_fields})) {
392 my @queryparts; # parts for the "WHERE clause"
393  
394 foreach my $field (@{ $$params{search_fields} }) {
395 next
396 if not defined $$params{$field};
397  
398 if (ref $$params{$field} eq 'ARRAY') {
399  
400 #TODO add placeholders instead of explicit quoting
401 push @queryparts, "$field IN ('" . join("','", @{ $$params{$field} }) . "')";
402 }
403 elsif ($field =~ m/^(.*)\_not/o) {
404 push(@queryparts, "$1 <> ?");
405 push(@query_params, $$params{$field});
406 }
407 elsif ($field =~ m/^(.*)\_start/o) {
408 push(@queryparts, "$1 >= ?");
409 push(@query_params, $$params{$field});
410 }
411 elsif ($field =~ m/^(.*)\_end/o) {
412 push(@queryparts, "$1 <= ?");
413 push(@query_params, $$params{$field});
414 }
415 elsif ($$params{$field} =~ m/%/) {
416 push(@queryparts, "$field LIKE ?");
417 push(@query_params, $$params{$field});
418 }
419 else {
420 push(@queryparts, "$field = ?");
421 push(@query_params, $$params{$field});
422 }
423 } #end foreach field
424  
425 if (scalar @queryparts > 0) {
426 $where = join ' AND ', @queryparts;
427 }
428 }
429  
430 if (defined $where and $where ne '') {
431 $where = " WHERE $where";
432 }
433  
434 return ($where, \@query_params);
435 }
436  
437 1;