1# BEGIN BPS TAGGED BLOCK {{{ 2# 3# COPYRIGHT: 4# 5# This software is Copyright (c) 1996-2021 Best Practical Solutions, LLC 6# <sales@bestpractical.com> 7# 8# (Except where explicitly superseded by other copyright notices) 9# 10# 11# LICENSE: 12# 13# This work is made available to you under the terms of Version 2 of 14# the GNU General Public License. A copy of that license should have 15# been provided with this software, but in any event can be snarfed 16# from www.gnu.org. 17# 18# This work is distributed in the hope that it will be useful, but 19# WITHOUT ANY WARRANTY; without even the implied warranty of 20# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 21# General Public License for more details. 22# 23# You should have received a copy of the GNU General Public License 24# along with this program; if not, write to the Free Software 25# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 26# 02110-1301 or visit their web page on the internet at 27# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html. 28# 29# 30# CONTRIBUTION SUBMISSION POLICY: 31# 32# (The following paragraph is not intended to limit the rights granted 33# to you to modify and distribute this software under the terms of 34# the GNU General Public License and is only of importance to you if 35# you choose to contribute your changes and enhancements to the 36# community by submitting them to Best Practical Solutions, LLC.) 37# 38# By intentionally submitting any modifications, corrections or 39# derivatives to this work, or any other work intended for use with 40# Request Tracker, to Best Practical Solutions, LLC, you confirm that 41# you are the copyright holder for those contributions and you grant 42# Best Practical Solutions, LLC a nonexclusive, worldwide, irrevocable, 43# royalty-free, perpetual, license to use, copy, create derivative 44# works based on those contributions, and sublicense and distribute 45# those contributions and any derivatives thereof. 46# 47# END BPS TAGGED BLOCK }}} 48 49=head1 NAME 50 51 RT::SearchBuilder - a baseclass for RT collection objects 52 53=head1 SYNOPSIS 54 55=head1 DESCRIPTION 56 57 58=head1 METHODS 59 60 61 62 63=cut 64 65package RT::SearchBuilder; 66 67use strict; 68use warnings; 69use 5.010; 70 71use base qw(DBIx::SearchBuilder RT::Base); 72 73use RT::Base; 74use DBIx::SearchBuilder "1.40"; 75 76use Scalar::Util qw/blessed/; 77 78sub _Init { 79 my $self = shift; 80 81 $self->{'user'} = shift; 82 unless(defined($self->CurrentUser)) { 83 use Carp; 84 Carp::confess("$self was created without a CurrentUser"); 85 $RT::Logger->err("$self was created without a CurrentUser"); 86 return(0); 87 } 88 $self->SUPER::_Init( 'Handle' => $RT::Handle); 89} 90 91sub _Handle { return $RT::Handle } 92 93sub CleanSlate { 94 my $self = shift; 95 $self->{'_sql_aliases'} = {}; 96 delete $self->{'handled_disabled_column'}; 97 delete $self->{'find_disabled_rows'}; 98 return $self->SUPER::CleanSlate(@_); 99} 100 101sub Join { 102 my $self = shift; 103 my %args = @_; 104 105 $args{'DISTINCT'} = 1 if 106 !exists $args{'DISTINCT'} 107 && $args{'TABLE2'} && lc($args{'FIELD2'}||'') eq 'id'; 108 109 return $self->SUPER::Join( %args ); 110} 111 112sub JoinTransactions { 113 my $self = shift; 114 my %args = ( New => 0, @_ ); 115 116 return $self->{'_sql_aliases'}{'transactions'} 117 if !$args{'New'} && $self->{'_sql_aliases'}{'transactions'}; 118 119 my $alias = $self->Join( 120 ALIAS1 => 'main', 121 FIELD1 => 'id', 122 TABLE2 => 'Transactions', 123 FIELD2 => 'ObjectId', 124 ); 125 126 # NewItem is necessary here because of RT::Report::Tickets and RT::Report::Tickets::Entry 127 my $item = $self->NewItem; 128 my $object_type = $item->can('ObjectType') ? $item->ObjectType : ref $item; 129 130 $self->RT::SearchBuilder::Limit( 131 LEFTJOIN => $alias, 132 FIELD => 'ObjectType', 133 VALUE => $object_type, 134 ); 135 $self->{'_sql_aliases'}{'transactions'} = $alias 136 unless $args{'New'}; 137 138 return $alias; 139} 140 141sub _OrderByCF { 142 my $self = shift; 143 my ($row, $cfkey, $cf) = @_; 144 145 $cfkey .= ".ordering" if !blessed($cf) || ($cf->MaxValues||0) != 1; 146 my ($ocfvs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cf ); 147 # this is described in _LimitCustomField 148 $self->Limit( 149 ALIAS => $CFs, 150 FIELD => 'Name', 151 OPERATOR => 'IS NOT', 152 VALUE => 'NULL', 153 ENTRYAGGREGATOR => 'AND', 154 SUBCLAUSE => ".ordering", 155 ) if $CFs; 156 my $CFvs = $self->Join( 157 TYPE => 'LEFT', 158 ALIAS1 => $ocfvs, 159 FIELD1 => 'CustomField', 160 TABLE2 => 'CustomFieldValues', 161 FIELD2 => 'CustomField', 162 ); 163 $self->Limit( 164 LEFTJOIN => $CFvs, 165 FIELD => 'Name', 166 QUOTEVALUE => 0, 167 VALUE => "$ocfvs.Content", 168 ENTRYAGGREGATOR => 'AND' 169 ); 170 171 return { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' }, 172 { %$row, ALIAS => $ocfvs, FIELD => 'Content' }; 173} 174 175sub OrderByCols { 176 my $self = shift; 177 my @sort; 178 for my $s (@_) { 179 next if defined $s->{FIELD} and $s->{FIELD} =~ /\W/; 180 $s->{FIELD} = $s->{FUNCTION} if $s->{FUNCTION}; 181 push @sort, $s; 182 } 183 return $self->SUPER::OrderByCols( @sort ); 184} 185 186# If we're setting RowsPerPage or FirstRow, ensure we get a natural number or undef. 187sub RowsPerPage { 188 my $self = shift; 189 return if @_ and defined $_[0] and $_[0] =~ /\D/; 190 return $self->SUPER::RowsPerPage(@_); 191} 192 193sub FirstRow { 194 my $self = shift; 195 return if @_ and defined $_[0] and $_[0] =~ /\D/; 196 return $self->SUPER::FirstRow(@_); 197} 198 199=head2 LimitToEnabled 200 201Only find items that haven't been disabled 202 203=cut 204 205sub LimitToEnabled { 206 my $self = shift; 207 208 $self->{'handled_disabled_column'} = 1; 209 $self->Limit( FIELD => 'Disabled', VALUE => '0' ); 210} 211 212=head2 LimitToDeleted 213 214Only find items that have been deleted. 215 216=cut 217 218sub LimitToDeleted { 219 my $self = shift; 220 221 $self->{'handled_disabled_column'} = $self->{'find_disabled_rows'} = 1; 222 $self->Limit( FIELD => 'Disabled', VALUE => '1' ); 223} 224 225=head2 FindAllRows 226 227Find all matching rows, regardless of whether they are disabled or not 228 229=cut 230 231sub FindAllRows { 232 shift->{'find_disabled_rows'} = 1; 233} 234 235=head2 LimitCustomField 236 237Takes a paramhash of key/value pairs with the following keys: 238 239=over 4 240 241=item CUSTOMFIELD - CustomField id. Optional 242 243=item OPERATOR - The usual Limit operators 244 245=item VALUE - The value to compare against 246 247=back 248 249=cut 250 251sub _SingularClass { 252 my $self = shift; 253 my $class = ref($self) || $self; 254 $class =~ s/s$// or die "Cannot deduce SingularClass for $class"; 255 return $class; 256} 257 258=head2 RecordClass 259 260Returns class name of records in this collection. This generic implementation 261just strips trailing 's'. 262 263=cut 264 265sub RecordClass { 266 $_[0]->_SingularClass 267} 268 269=head2 RegisterCustomFieldJoin 270 271Takes a pair of arguments, the first a class name and the second a callback 272function. The class will be used to call 273L<RT::Record/CustomFieldLookupType>. The callback will be called when 274limiting a collection of the caller's class by a CF of the passed class's 275lookup type. 276 277The callback is passed a single argument, the current collection object (C<$self>). 278 279An example from L<RT::Tickets>: 280 281 __PACKAGE__->RegisterCustomFieldJoin( 282 "RT::Transaction" => sub { $_[0]->JoinTransactions } 283 ); 284 285Returns true on success, undef on failure. 286 287=cut 288 289sub RegisterCustomFieldJoin { 290 my $class = shift; 291 my ($type, $callback) = @_; 292 293 $type = $type->CustomFieldLookupType if $type; 294 295 die "Unknown LookupType '$type'" 296 unless $type and grep { $_ eq $type } RT::CustomField->LookupTypes; 297 298 die "Custom field join callbacks must be CODE references" 299 unless ref($callback) eq 'CODE'; 300 301 warn "Another custom field join callback is already registered for '$type'" 302 if $class->_JOINS_FOR_LOOKUP_TYPES->{$type}; 303 304 # Stash the callback on ourselves 305 $class->_JOINS_FOR_LOOKUP_TYPES->{ $type } = $callback; 306 307 return 1; 308} 309 310=head2 _JoinForLookupType 311 312Takes an L<RT::CustomField> LookupType and joins this collection as 313appropriate to reach the object records to which LookupType applies. The 314object records will be of the class returned by 315L<RT::CustomField/ObjectTypeFromLookupType>. 316 317Returns the join alias suitable for further limiting against object 318properties. 319 320Returns undef on failure. 321 322Used by L</_CustomFieldJoin>. 323 324=cut 325 326sub _JoinForLookupType { 327 my $self = shift; 328 my $type = shift or return; 329 330 # Convenience shortcut so that classes don't need to register a handler 331 # for their native lookup type 332 return "main" if $type eq $self->RecordClass->CustomFieldLookupType 333 and grep { $_ eq $type } RT::CustomField->LookupTypes; 334 335 my $JOINS = $self->_JOINS_FOR_LOOKUP_TYPES; 336 return $JOINS->{$type}->($self) 337 if ref $JOINS->{$type} eq 'CODE'; 338 339 return; 340} 341 342sub _JOINS_FOR_LOOKUP_TYPES { 343 my $class = blessed($_[0]) || $_[0]; 344 state %JOINS; 345 return $JOINS{$class} ||= {}; 346} 347 348=head2 _CustomFieldJoin 349 350Factor out the Join of custom fields so we can use it for sorting too 351 352=cut 353 354sub _CustomFieldJoin { 355 my ($self, $cfkey, $cf, $type) = @_; 356 $type ||= $self->RecordClass->CustomFieldLookupType; 357 358 # Perform one Join per CustomField 359 if ( $self->{_sql_object_cfv_alias}{$cfkey} || 360 $self->{_sql_cf_alias}{$cfkey} ) 361 { 362 return ( $self->{_sql_object_cfv_alias}{$cfkey}, 363 $self->{_sql_cf_alias}{$cfkey} ); 364 } 365 366 my $ObjectAlias = $self->_JoinForLookupType($type) 367 or die "We don't know how to join for LookupType $type"; 368 369 my ($ocfvalias, $CFs); 370 if ( blessed($cf) ) { 371 $ocfvalias = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join( 372 TYPE => 'LEFT', 373 ALIAS1 => $ObjectAlias, 374 FIELD1 => 'id', 375 TABLE2 => 'ObjectCustomFieldValues', 376 FIELD2 => 'ObjectId', 377 $cf->SingleValue? (DISTINCT => 1) : (), 378 ); 379 $self->Limit( 380 LEFTJOIN => $ocfvalias, 381 FIELD => 'CustomField', 382 VALUE => $cf->Disabled ? 0 : $cf->id, 383 ENTRYAGGREGATOR => 'AND' 384 ); 385 } 386 else { 387 ($ocfvalias, $CFs) = $self->_CustomFieldJoinByName( $ObjectAlias, $cf, $type ); 388 $self->{_sql_cf_alias}{$cfkey} = $CFs; 389 $self->{_sql_object_cfv_alias}{$cfkey} = $ocfvalias; 390 } 391 $self->Limit( 392 LEFTJOIN => $ocfvalias, 393 FIELD => 'ObjectType', 394 VALUE => RT::CustomField->ObjectTypeFromLookupType($type), 395 ENTRYAGGREGATOR => 'AND' 396 ); 397 $self->Limit( 398 LEFTJOIN => $ocfvalias, 399 FIELD => 'Disabled', 400 OPERATOR => '=', 401 VALUE => '0', 402 ENTRYAGGREGATOR => 'AND' 403 ); 404 405 return ($ocfvalias, $CFs); 406} 407 408sub _CustomFieldJoinByName { 409 my $self = shift; 410 my ($ObjectAlias, $cf, $type) = @_; 411 my $ocfalias = $self->Join( 412 TYPE => 'LEFT', 413 EXPRESSION => q|'0'|, 414 TABLE2 => 'ObjectCustomFields', 415 FIELD2 => 'ObjectId', 416 ); 417 418 my $CFs = $self->Join( 419 TYPE => 'LEFT', 420 ALIAS1 => $ocfalias, 421 FIELD1 => 'CustomField', 422 TABLE2 => 'CustomFields', 423 FIELD2 => 'id', 424 ); 425 $self->Limit( 426 LEFTJOIN => $CFs, 427 ENTRYAGGREGATOR => 'AND', 428 FIELD => 'LookupType', 429 VALUE => $type, 430 ); 431 $self->Limit( 432 LEFTJOIN => $CFs, 433 ENTRYAGGREGATOR => 'AND', 434 FIELD => 'Name', 435 CASESENSITIVE => 0, 436 VALUE => $cf, 437 ); 438 $self->Limit( 439 LEFTJOIN => $CFs, 440 ENTRYAGGREGATOR => 'AND', 441 FIELD => 'Disabled', 442 VALUE => 0, 443 ); 444 445 my $ocfvalias = $self->Join( 446 TYPE => 'LEFT', 447 ALIAS1 => $CFs, 448 FIELD1 => 'id', 449 TABLE2 => 'ObjectCustomFieldValues', 450 FIELD2 => 'CustomField', 451 ); 452 $self->Limit( 453 LEFTJOIN => $ocfvalias, 454 FIELD => 'ObjectId', 455 VALUE => "$ObjectAlias.id", 456 QUOTEVALUE => 0, 457 ENTRYAGGREGATOR => 'AND', 458 ); 459 460 return ($ocfvalias, $CFs, $ocfalias); 461} 462 463sub LimitCustomField { 464 my $self = shift; 465 return $self->_LimitCustomField( @_ ); 466} 467 468use Regexp::Common qw(RE_net_IPv4); 469use Regexp::Common::net::CIDR; 470 471sub _LimitCustomField { 472 my $self = shift; 473 my %args = ( VALUE => undef, 474 CUSTOMFIELD => undef, 475 OPERATOR => '=', 476 KEY => undef, 477 PREPARSE => 1, 478 QUOTEVALUE => 1, 479 @_ ); 480 481 my $op = delete $args{OPERATOR}; 482 my $value = delete $args{VALUE}; 483 my $ltype = delete $args{LOOKUPTYPE} || $self->RecordClass->CustomFieldLookupType; 484 my $cf = delete $args{CUSTOMFIELD}; 485 my $column = delete $args{COLUMN}; 486 my $cfkey = delete $args{KEY}; 487 if (blessed($cf) and $cf->id) { 488 $cfkey ||= $cf->id; 489 490 # Make sure we can really see $cf 491 unless ( $cf->CurrentUserHasRight('SeeCustomField') ) { 492 my $obj = RT::CustomField->new( RT->SystemUser ); 493 $obj->Load( $cf->id ); 494 $cf = $obj; 495 } 496 } elsif ($cf =~ /^\d+$/) { 497 # Intentionally load as the system user, so we can build better 498 # queries; this is necessary as we don't have a context object 499 # which might grant the user rights to see the CF. This object 500 # is only used to inspect the properties of the CF itself. 501 my $obj = RT::CustomField->new( RT->SystemUser ); 502 $obj->Load($cf); 503 if ($obj->id) { 504 $cf = $obj; 505 $cfkey ||= $cf->id; 506 } else { 507 $cfkey ||= "$ltype-$cf"; 508 } 509 } else { 510 # Resolve CF by name for better queries, like the above block. 511 my $cfs = RT::CustomFields->new( RT->SystemUser ); 512 $cfs->LimitToLookupType($ltype); 513 $cfs->Limit( 514 FIELD => 'Name', 515 VALUE => $cf, 516 CASESENSITIVE => 0, 517 ); 518 if ( $cfs->Count == 1 ) { 519 $cf = $cfs->Next; 520 $cfkey ||= $cf->id; 521 } 522 else { 523 $cfkey ||= "$ltype-$cf"; 524 } 525 } 526 527 $args{SUBCLAUSE} ||= "cf-$cfkey"; 528 529 530 my $fix_op = sub { 531 return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle'; 532 533 my %args = @_; 534 return %args unless $args{'FIELD'} eq 'LargeContent'; 535 536 my $op = $args{'OPERATOR'}; 537 if ( $op eq '=' ) { 538 $args{'OPERATOR'} = 'MATCHES'; 539 } 540 elsif ( $op eq '!=' ) { 541 $args{'OPERATOR'} = 'NOT MATCHES'; 542 } 543 elsif ( $op =~ /^[<>]=?$/ ) { 544 $args{'FUNCTION'} = "TO_CHAR( $args{'ALIAS'}.LargeContent )"; 545 } 546 return %args; 547 }; 548 549 # Special Limit (we can exit early) 550 # IS NULL and IS NOT NULL checks 551 if ( $op =~ /^IS( NOT)?$/i ) { 552 my ($ocfvalias, $CFs) = $self->_CustomFieldJoin( $cfkey, $cf, $ltype ); 553 $self->_OpenParen( $args{SUBCLAUSE} ); 554 $self->Limit( 555 %args, 556 ALIAS => $ocfvalias, 557 FIELD => ($column || 'id'), 558 OPERATOR => $op, 559 VALUE => $value, 560 ); 561 # See below for an explanation of this limit 562 $self->Limit( 563 ALIAS => $CFs, 564 FIELD => 'Name', 565 OPERATOR => 'IS NOT', 566 VALUE => 'NULL', 567 ENTRYAGGREGATOR => 'AND', 568 SUBCLAUSE => $args{SUBCLAUSE}, 569 ) if $CFs; 570 $self->_CloseParen( $args{SUBCLAUSE} ); 571 return; 572 } 573 574 ########## Content pre-parsing if we know things about the CF 575 if ( blessed($cf) and delete $args{PREPARSE} ) { 576 my $type = $cf->Type; 577 578 if ( !$args{QUOTEVALUE} ) { 579 my ( $class, $field ); 580 581 # e.g. Users_3.Name 582 if ( $value =~ /^(\w+?)(?:_\d+)?\.(\w+)$/ ) { 583 my $table = $1; 584 $field = $2; 585 $class = $table =~ /main/i ? 'RT::Tickets' : "RT::$table"; 586 } 587 else { 588 $class = ref $self; 589 $field = $value; 590 } 591 592 if ( $class->can('RecordClass') 593 and ( my $record_class = $class->RecordClass ) ) 594 { 595 if ( my $meta = $record_class->_ClassAccessible->{$field} ) { 596 if ( RT->Config->Get('DatabaseType') eq 'Pg' ) { 597 if ( $meta->{is_numeric} || $meta->{type} eq 'datetime' ) { 598 $value = "CAST($value AS VARCHAR)"; 599 } 600 } 601 elsif ( RT->Config->Get('DatabaseType') eq 'Oracle' ) { 602 if ( $meta->{is_numeric} ) { 603 $value = "TO_CHAR($value)"; 604 } 605 elsif ( $type eq 'datetime' ) { 606 $value = "TO_CHAR($value, 'YYYY-MM-DD HH24:MI:SS')"; 607 } 608 } 609 } 610 } 611 612 if ( $type eq 'Date' ) { 613 $value = "SUBSTR($value, 1, 10)"; 614 } 615 } 616 elsif ( $type eq 'IPAddress' ) { 617 my $parsed = RT::ObjectCustomFieldValue->ParseIP($value); 618 if ($parsed) { 619 $value = $parsed; 620 } else { 621 $RT::Logger->warn("$value is not a valid IPAddress"); 622 } 623 } elsif ( $type eq 'IPAddressRange' ) { 624 my ( $start_ip, $end_ip ) = 625 RT::ObjectCustomFieldValue->ParseIPRange($value); 626 if ( $start_ip && $end_ip ) { 627 if ( $op =~ /^<=?$/ ) { 628 $value = $start_ip; 629 } elsif ($op =~ /^>=?$/ ) { 630 $value = $end_ip; 631 } else { 632 $value = join '-', $start_ip, $end_ip; 633 } 634 } else { 635 $RT::Logger->warn("$value is not a valid IPAddressRange"); 636 } 637 638 # Recurse if they want a range comparison 639 if ( $op !~ /^[<>]=?$/ ) { 640 my ($start_ip, $end_ip) = split /-/, $value; 641 $self->_OpenParen( $args{SUBCLAUSE} ); 642 # Ideally we would limit >= 000.000.000.000 and <= 643 # 255.255.255.255 so DB optimizers could use better 644 # estimations and scan less rows, but this breaks with IPv6. 645 if ( $op !~ /NOT|!=|<>/i ) { # positive equation 646 $self->_LimitCustomField( 647 %args, 648 OPERATOR => '<=', 649 VALUE => $end_ip, 650 LOOKUPTYPE => $ltype, 651 CUSTOMFIELD => $cf, 652 COLUMN => 'Content', 653 PREPARSE => 0, 654 ); 655 $self->_LimitCustomField( 656 %args, 657 OPERATOR => '>=', 658 VALUE => $start_ip, 659 LOOKUPTYPE => $ltype, 660 CUSTOMFIELD => $cf, 661 COLUMN => 'LargeContent', 662 ENTRYAGGREGATOR => 'AND', 663 PREPARSE => 0, 664 ); 665 } else { # negative equation 666 $self->_LimitCustomField( 667 %args, 668 OPERATOR => '>', 669 VALUE => $end_ip, 670 LOOKUPTYPE => $ltype, 671 CUSTOMFIELD => $cf, 672 COLUMN => 'Content', 673 PREPARSE => 0, 674 ); 675 $self->_LimitCustomField( 676 %args, 677 OPERATOR => '<', 678 VALUE => $start_ip, 679 LOOKUPTYPE => $ltype, 680 CUSTOMFIELD => $cf, 681 COLUMN => 'LargeContent', 682 ENTRYAGGREGATOR => 'OR', 683 PREPARSE => 0, 684 ); 685 } 686 $self->_CloseParen( $args{SUBCLAUSE} ); 687 return; 688 } 689 } elsif ( $type =~ /^Date(?:Time)?$/ ) { 690 my $date = RT::Date->new( $self->CurrentUser ); 691 $date->Set( Format => 'unknown', Value => $value ); 692 if ( $date->IsSet ) { 693 if ( 694 $type eq 'Date' 695 # Heuristics to determine if a date, and not 696 # a datetime, was entered: 697 || $value =~ /^\s*(?:today|tomorrow|yesterday)\s*$/i 698 || ( $value !~ /midnight|\d+:\d+:\d+/i 699 && $date->Time( Timezone => 'user' ) eq '00:00:00' ) 700 ) 701 { 702 $value = $date->Date( Timezone => 'user' ); 703 } else { 704 $value = $date->DateTime; 705 } 706 } else { 707 $RT::Logger->warn("$value is not a valid date string"); 708 } 709 710 # Recurse if day equality is being checked on a datetime 711 if ( $type eq 'DateTime' and $op eq '=' && $value !~ /:/ ) { 712 my $date = RT::Date->new( $self->CurrentUser ); 713 $date->Set( Format => 'unknown', Value => $value ); 714 my $daystart = $date->ISO; 715 $date->AddDay; 716 my $dayend = $date->ISO; 717 718 $self->_OpenParen( $args{SUBCLAUSE} ); 719 $self->_LimitCustomField( 720 %args, 721 OPERATOR => ">=", 722 VALUE => $daystart, 723 LOOKUPTYPE => $ltype, 724 CUSTOMFIELD => $cf, 725 COLUMN => 'Content', 726 ENTRYAGGREGATOR => 'AND', 727 PREPARSE => 0, 728 ); 729 730 $self->_LimitCustomField( 731 %args, 732 OPERATOR => "<", 733 VALUE => $dayend, 734 LOOKUPTYPE => $ltype, 735 CUSTOMFIELD => $cf, 736 COLUMN => 'Content', 737 ENTRYAGGREGATOR => 'AND', 738 PREPARSE => 0, 739 ); 740 $self->_CloseParen( $args{SUBCLAUSE} ); 741 return; 742 } 743 } 744 } 745 746 ########## Limits 747 748 my $single_value = !blessed($cf) || $cf->SingleValue; 749 my $negative_op = ($op eq '!=' || $op =~ /\bNOT\b/i); 750 my $value_is_long = (length( Encode::encode( "UTF-8", $value)) > 255) ? 1 : 0; 751 752 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++ 753 if not $single_value and $op =~ /^(!?=|(NOT )?LIKE)$/i; 754 my ($ocfvalias, $CFs) = $self->_CustomFieldJoin( $cfkey, $cf, $ltype ); 755 756 # A negative limit on a multi-value CF means _none_ of the values 757 # are the given value 758 if ( $negative_op and not $single_value ) { 759 # Reverse the limit we apply to the join, and check IS NULL 760 $op =~ s/!|NOT\s+//i; 761 762 # Ideally we would check both Content and LargeContent here, as 763 # the positive searches do below -- however, we cannot place 764 # complex limits inside LEFTJOINs due to searchbuilder 765 # limitations. Guessing which to check based on the value's 766 # string length is sufficient for !=, but sadly insufficient for 767 # NOT LIKE checks, giving false positives. 768 $column ||= $value_is_long ? 'LargeContent' : 'Content'; 769 $self->Limit( $fix_op->( 770 LEFTJOIN => $ocfvalias, 771 ALIAS => $ocfvalias, 772 FIELD => $column, 773 OPERATOR => $op, 774 VALUE => $value, 775 CASESENSITIVE => 0, 776 QUOTEVALUE => $args{QUOTEVALUE}, 777 ) ); 778 $self->Limit( 779 %args, 780 ALIAS => $ocfvalias, 781 FIELD => 'id', 782 OPERATOR => 'IS', 783 VALUE => 'NULL', 784 ); 785 return; 786 } 787 788 # If column is defined, then we just search it that, with no magic 789 if ( $column ) { 790 $self->_OpenParen( $args{SUBCLAUSE} ); 791 $self->Limit( $fix_op->( 792 %args, 793 ALIAS => $ocfvalias, 794 FIELD => $column, 795 OPERATOR => $op, 796 VALUE => $value, 797 CASESENSITIVE => 0, 798 ) ); 799 $self->Limit( 800 ALIAS => $ocfvalias, 801 FIELD => $column, 802 OPERATOR => 'IS', 803 VALUE => 'NULL', 804 ENTRYAGGREGATOR => 'OR', 805 SUBCLAUSE => $args{SUBCLAUSE}, 806 ) if $negative_op; 807 $self->_CloseParen( $args{SUBCLAUSE} ); 808 return; 809 } 810 811 $self->_OpenParen( $args{SUBCLAUSE} ); # For negative_op "OR it is null" clause 812 $self->_OpenParen( $args{SUBCLAUSE} ); # NAME IS NOT NULL clause 813 814 $self->_OpenParen( $args{SUBCLAUSE} ); # Check Content / LargeContent 815 if ($value_is_long and $op eq "=") { 816 # Doesn't matter what Content contains, as it cannot match the 817 # too-long value; we just look in LargeContent, below. 818 } elsif ($value_is_long and $op =~ /^(!=|<>)$/) { 819 # If Content is non-null, that's a valid way to _not_ contain the too-long value. 820 $self->Limit( 821 %args, 822 ALIAS => $ocfvalias, 823 FIELD => 'Content', 824 OPERATOR => 'IS NOT', 825 VALUE => 'NULL', 826 ); 827 } else { 828 # Otherwise, go looking at the Content 829 $self->Limit( 830 %args, 831 ALIAS => $ocfvalias, 832 FIELD => 'Content', 833 OPERATOR => $op, 834 VALUE => $value, 835 CASESENSITIVE => 0, 836 ); 837 } 838 839 if (!$value_is_long and $op eq "=") { 840 # Doesn't matter what LargeContent contains, as it cannot match 841 # the short value. 842 } elsif (!$value_is_long and $op =~ /^(!=|<>)$/) { 843 # If LargeContent is non-null, that's a valid way to _not_ 844 # contain the too-short value. 845 $self->Limit( 846 %args, 847 ALIAS => $ocfvalias, 848 FIELD => 'LargeContent', 849 OPERATOR => 'IS NOT', 850 VALUE => 'NULL', 851 ENTRYAGGREGATOR => 'OR', 852 ); 853 } else { 854 $self->_OpenParen( $args{SUBCLAUSE} ); # LargeContent check 855 $self->_OpenParen( $args{SUBCLAUSE} ); # Content is null? 856 $self->Limit( 857 ALIAS => $ocfvalias, 858 FIELD => 'Content', 859 OPERATOR => '=', 860 VALUE => '', 861 ENTRYAGGREGATOR => 'OR', 862 SUBCLAUSE => $args{SUBCLAUSE}, 863 ); 864 $self->Limit( 865 ALIAS => $ocfvalias, 866 FIELD => 'Content', 867 OPERATOR => 'IS', 868 VALUE => 'NULL', 869 ENTRYAGGREGATOR => 'OR', 870 SUBCLAUSE => $args{SUBCLAUSE}, 871 ); 872 $self->_CloseParen( $args{SUBCLAUSE} ); # Content is null? 873 $self->Limit( $fix_op->( 874 ALIAS => $ocfvalias, 875 FIELD => 'LargeContent', 876 OPERATOR => $op, 877 VALUE => $value, 878 ENTRYAGGREGATOR => 'AND', 879 SUBCLAUSE => $args{SUBCLAUSE}, 880 CASESENSITIVE => 0, 881 QUOTEVALUE => $args{QUOTEVALUE}, 882 ) ); 883 $self->_CloseParen( $args{SUBCLAUSE} ); # LargeContent check 884 } 885 886 $self->_CloseParen( $args{SUBCLAUSE} ); # Check Content/LargeContent 887 888 # XXX: if we join via CustomFields table then 889 # because of order of left joins we get NULLs in 890 # CF table and then get nulls for those records 891 # in OCFVs table what result in wrong results 892 # as decifer method now tries to load a CF then 893 # we fall into this situation only when there 894 # are more than one CF with the name in the DB. 895 # the same thing applies to order by call. 896 # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if 897 # we want treat IS NULL as (not applies or has 898 # no value) 899 $self->Limit( 900 ALIAS => $CFs, 901 FIELD => 'Name', 902 OPERATOR => 'IS NOT', 903 VALUE => 'NULL', 904 ENTRYAGGREGATOR => 'AND', 905 SUBCLAUSE => $args{SUBCLAUSE}, 906 ) if $CFs; 907 $self->_CloseParen( $args{SUBCLAUSE} ); # Name IS NOT NULL clause 908 909 # If we were looking for != or NOT LIKE, we need to include the 910 # possibility that the row had no value. 911 $self->Limit( 912 ALIAS => $ocfvalias, 913 FIELD => 'id', 914 OPERATOR => 'IS', 915 VALUE => 'NULL', 916 ENTRYAGGREGATOR => 'OR', 917 SUBCLAUSE => $args{SUBCLAUSE}, 918 ) if $negative_op; 919 $self->_CloseParen( $args{SUBCLAUSE} ); # negative_op clause 920} 921 922=head2 Limit PARAMHASH 923 924This Limit sub calls SUPER::Limit, but defaults "CASESENSITIVE" to 1, thus 925making sure that by default lots of things don't do extra work trying to 926match lower(colname) agaist lc($val); 927 928We also force VALUE to C<NULL> when the OPERATOR is C<IS> or C<IS NOT>. 929This ensures that we don't pass invalid SQL to the database or allow SQL 930injection attacks when we pass through user specified values. 931 932=cut 933 934my %check_case_sensitivity = ( 935 groups => { 'name' => 1, domain => 1 }, 936 queues => { 'name' => 1 }, 937 users => { 'name' => 1, emailaddress => 1 }, 938 customfields => { 'name' => 1 }, 939); 940 941my %deprecated = ( 942); 943 944sub Limit { 945 my $self = shift; 946 my %ARGS = ( 947 OPERATOR => '=', 948 @_, 949 ); 950 951 # We use the same regex here that DBIx::SearchBuilder uses to exclude 952 # values from quoting 953 if ( $ARGS{'OPERATOR'} =~ /IS/i ) { 954 # Don't pass anything but NULL for IS and IS NOT 955 $ARGS{'VALUE'} = 'NULL'; 956 } 957 958 if (($ARGS{FIELD}||'') =~ /\W/ 959 or $ARGS{OPERATOR} !~ /^((?:SHALLOW\s*)?(?:=|<|>|!=|<>|<=|>= 960 |(NOT\s*)?LIKE 961 |(NOT\s*)?(STARTS|ENDS)WITH 962 |(NOT\s*)?MATCHES 963 |IS(\s*NOT)? 964 |(NOT\s*)?IN 965 |\@\@ 966 |AGAINST))$/ix) { 967 $RT::Logger->crit("Possible SQL injection attack: $ARGS{FIELD} $ARGS{OPERATOR}"); 968 %ARGS = ( 969 %ARGS, 970 FIELD => 'id', 971 OPERATOR => '<', 972 VALUE => '0', 973 ); 974 } 975 976 my $table; 977 ($table) = $ARGS{'ALIAS'} && $ARGS{'ALIAS'} ne 'main' 978 ? ($ARGS{'ALIAS'} =~ /^(.*)_\d+$/) 979 : $self->Table 980 ; 981 982 if ( $table and $ARGS{FIELD} and my $instead = $deprecated{ lc $table }{ lc $ARGS{'FIELD'} } ) { 983 RT->Deprecated( 984 Message => "$table.$ARGS{'FIELD'} column is deprecated", 985 Instead => $instead, Remove => '4.6' 986 ); 987 } 988 989 unless ( exists $ARGS{CASESENSITIVE} or (exists $ARGS{QUOTEVALUE} and not $ARGS{QUOTEVALUE}) ) { 990 if ( $ARGS{FIELD} and $ARGS{'OPERATOR'} !~ /IS/i 991 && $table && $check_case_sensitivity{ lc $table }{ lc $ARGS{'FIELD'} } 992 ) { 993 RT->Logger->warning( 994 "Case sensitive search by $table.$ARGS{'FIELD'}" 995 ." at ". (caller)[1] . " line ". (caller)[2] 996 ); 997 } 998 $ARGS{'CASESENSITIVE'} = 1; 999 } 1000 1001 return $self->SUPER::Limit( %ARGS ); 1002} 1003 1004=head2 ItemsOrderBy 1005 1006If it has a SortOrder attribute, sort the array by SortOrder. 1007Otherwise, if it has a "Name" attribute, sort alphabetically by Name 1008Otherwise, just give up and return it in the order it came from the 1009db. 1010 1011=cut 1012 1013sub ItemsOrderBy { 1014 my $self = shift; 1015 my $items = shift; 1016 1017 if ($self->RecordClass->_Accessible('SortOrder','read')) { 1018 $items = [ sort { $a->SortOrder <=> $b->SortOrder } @{$items} ]; 1019 } 1020 elsif ($self->RecordClass->_Accessible('Name','read')) { 1021 $items = [ sort { lc($a->Name) cmp lc($b->Name) } @{$items} ]; 1022 } 1023 1024 return $items; 1025} 1026 1027=head2 ItemsArrayRef 1028 1029Return this object's ItemsArray, in the order that ItemsOrderBy sorts 1030it. 1031 1032=cut 1033 1034sub ItemsArrayRef { 1035 my $self = shift; 1036 return $self->ItemsOrderBy($self->SUPER::ItemsArrayRef()); 1037} 1038 1039# make sure that Disabled rows never get seen unless 1040# we're explicitly trying to see them. 1041 1042sub _DoSearch { 1043 my $self = shift; 1044 1045 if ( $self->{'with_disabled_column'} 1046 && !$self->{'handled_disabled_column'} 1047 && !$self->{'find_disabled_rows'} 1048 ) { 1049 $self->LimitToEnabled; 1050 } 1051 return $self->SUPER::_DoSearch(@_); 1052} 1053sub _DoCount { 1054 my $self = shift; 1055 1056 if ( $self->{'with_disabled_column'} 1057 && !$self->{'handled_disabled_column'} 1058 && !$self->{'find_disabled_rows'} 1059 ) { 1060 $self->LimitToEnabled; 1061 } 1062 return $self->SUPER::_DoCount(@_); 1063} 1064 1065=head2 ColumnMapClassName 1066 1067ColumnMap needs a Collection name to load the correct list display. 1068Depluralization is hard, so provide an easy way to correct the naive 1069algorithm that this code uses. 1070 1071=cut 1072 1073sub ColumnMapClassName { 1074 my $self = shift; 1075 my $Class = $self->_SingularClass; 1076 $Class =~ s/:/_/g; 1077 return $Class; 1078} 1079 1080=head2 NewItem 1081 1082Returns a new item based on L</RecordClass> using the current user. 1083 1084=cut 1085 1086sub NewItem { 1087 my $self = shift; 1088 return $self->RecordClass->new($self->CurrentUser); 1089} 1090 1091=head2 NotSetDateToNullFunction 1092 1093Takes a paramhash with an optional FIELD key whose value is the name of a date 1094column. If no FIELD is provided, a literal C<?> placeholder is used so the 1095caller can fill in the field later. 1096 1097Returns a SQL function which evaluates to C<NULL> if the FIELD is set to the 1098Unix epoch; otherwise it evaluates to FIELD. This is useful because RT 1099currently stores unset dates as a Unix epoch timestamp instead of NULL, but 1100NULLs are often more desireable. 1101 1102=cut 1103 1104sub NotSetDateToNullFunction { 1105 my $self = shift; 1106 my %args = ( FIELD => undef, @_ ); 1107 1108 my $res = "CASE WHEN ? BETWEEN '1969-12-31 11:59:59' AND '1970-01-01 12:00:01' THEN NULL ELSE ? END"; 1109 if ( $args{FIELD} ) { 1110 $res = $self->CombineFunctionWithField( %args, FUNCTION => $res ); 1111 } 1112 return $res; 1113} 1114 1115sub DistinctFieldValues { 1116 my $self = shift; 1117 my %args = ( 1118 Field => undef, 1119 Order => undef, 1120 Max => undef, 1121 decode_utf8 => 1, 1122 @_%2 ? (Field => @_) : (@_) 1123 ); 1124 1125 my @values = $self->SUPER::DistinctFieldValues( %args ); 1126 1127 foreach my $value ( @values ) { 1128 if ( $args{'decode_utf8'} ) { 1129 if ( !utf8::is_utf8( $value ) ) { # mysql/sqlite 1130 utf8::decode( $value ); 1131 } 1132 } 1133 else { 1134 if ( utf8::is_utf8( $value ) ) { 1135 utf8::encode( $value ); 1136 } 1137 } 1138 } 1139 return @values; 1140} 1141 1142RT::Base->_ImportOverlays(); 1143 11441; 1145