| 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; |