X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=rt%2Flib%2FRT%2FSearchBuilder.pm;h=8b459679572412f05ba4f80e0948b4cc6e893707;hp=aa915d433edbb311d8c64db7ead6f71a2d4bf838;hb=7322f2afedcc2f427e997d1535a503613a83f088;hpb=620726f913bfa7f9347d2e3c20642185c5b8d96c diff --git a/rt/lib/RT/SearchBuilder.pm b/rt/lib/RT/SearchBuilder.pm index aa915d433..8b4596795 100644 --- a/rt/lib/RT/SearchBuilder.pm +++ b/rt/lib/RT/SearchBuilder.pm @@ -1,40 +1,40 @@ # BEGIN BPS TAGGED BLOCK {{{ -# +# # COPYRIGHT: -# -# This software is Copyright (c) 1996-2007 Best Practical Solutions, LLC -# -# +# +# This software is Copyright (c) 1996-2016 Best Practical Solutions, LLC +# +# # (Except where explicitly superseded by other copyright notices) -# -# +# +# # LICENSE: -# +# # This work is made available to you under the terms of Version 2 of # the GNU General Public License. A copy of that license should have # been provided with this software, but in any event can be snarfed # from www.gnu.org. -# +# # This work is distributed in the hope that it will be useful, but # WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU # General Public License for more details. -# +# # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA # 02110-1301 or visit their web page on the internet at -# http://www.gnu.org/copyleft/gpl.html. -# -# +# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html. +# +# # CONTRIBUTION SUBMISSION POLICY: -# +# # (The following paragraph is not intended to limit the rights granted # to you to modify and distribute this software under the terms of # the GNU General Public License and is only of importance to you if # you choose to contribute your changes and enhancements to the # community by submitting them to Best Practical Solutions, LLC.) -# +# # By intentionally submitting any modifications, corrections or # derivatives to this work, or any other work intended for use with # Request Tracker, to Best Practical Solutions, LLC, you confirm that @@ -43,8 +43,9 @@ # royalty-free, perpetual, license to use, copy, create derivative # works based on those contributions, and sublicense and distribute # those contributions and any derivatives thereof. -# +# # END BPS TAGGED BLOCK }}} + =head1 NAME RT::SearchBuilder - a baseclass for RT collection objects @@ -57,169 +58,179 @@ =head1 METHODS -=begin testing - -ok (require RT::SearchBuilder); - -=end testing =cut package RT::SearchBuilder; +use strict; +use warnings; +use 5.010; + +use base qw(DBIx::SearchBuilder RT::Base); + use RT::Base; use DBIx::SearchBuilder "1.50"; -use strict; -use vars qw(@ISA); -@ISA = qw(DBIx::SearchBuilder RT::Base); +use Scalar::Util qw/blessed/; -# {{{ sub _Init sub _Init { my $self = shift; $self->{'user'} = shift; unless(defined($self->CurrentUser)) { - use Carp; - Carp::confess("$self was created without a CurrentUser"); - $RT::Logger->err("$self was created without a CurrentUser"); - return(0); + use Carp; + Carp::confess("$self was created without a CurrentUser"); + $RT::Logger->err("$self was created without a CurrentUser"); + return(0); } $self->SUPER::_Init( 'Handle' => $RT::Handle); } -# }}} -# {{{ sub LimitToEnabled +sub _Handle { return $RT::Handle } -=head2 LimitToEnabled +sub CleanSlate { + my $self = shift; + $self->{'_sql_aliases'} = {}; + delete $self->{'handled_disabled_column'}; + delete $self->{'find_disabled_rows'}; + return $self->SUPER::CleanSlate(@_); +} -Only find items that haven\'t been disabled +sub Join { + my $self = shift; + my %args = @_; -=cut + $args{'DISTINCT'} = 1 if + !exists $args{'DISTINCT'} + && $args{'TABLE2'} && lc($args{'FIELD2'}||'') eq 'id'; -sub LimitToEnabled { - my $self = shift; - - $self->Limit( FIELD => 'Disabled', - VALUE => '0', - OPERATOR => '=' ); + return $self->SUPER::Join( %args ); } -# }}} -# {{{ sub LimitToDisabled +sub JoinTransactions { + my $self = shift; + my %args = ( New => 0, @_ ); -=head2 LimitToDeleted + return $self->{'_sql_aliases'}{'transactions'} + if !$args{'New'} && $self->{'_sql_aliases'}{'transactions'}; -Only find items that have been deleted. + my $alias = $self->Join( + ALIAS1 => 'main', + FIELD1 => 'id', + TABLE2 => 'Transactions', + FIELD2 => 'ObjectId', + ); -=cut + # NewItem is necessary here because of RT::Report::Tickets and RT::Report::Tickets::Entry + my $item = $self->NewItem; + my $object_type = $item->can('ObjectType') ? $item->ObjectType : ref $item; -sub LimitToDeleted { + $self->RT::SearchBuilder::Limit( + LEFTJOIN => $alias, + FIELD => 'ObjectType', + VALUE => $object_type, + ); + $self->{'_sql_aliases'}{'transactions'} = $alias + unless $args{'New'}; + + return $alias; +} + +sub _OrderByCF { my $self = shift; - - $self->{'find_disabled_rows'} = 1; - $self->Limit( FIELD => 'Disabled', - OPERATOR => '=', - VALUE => '1' - ); + my ($row, $cfkey, $cf) = @_; + + $cfkey .= ".ordering" if !blessed($cf) || ($cf->MaxValues||0) != 1; + my ($ocfvs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cf ); + # this is described in _LimitCustomField + $self->Limit( + ALIAS => $CFs, + FIELD => 'Name', + OPERATOR => 'IS NOT', + VALUE => 'NULL', + ENTRYAGGREGATOR => 'AND', + SUBCLAUSE => ".ordering", + ) if $CFs; + my $CFvs = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $ocfvs, + FIELD1 => 'CustomField', + TABLE2 => 'CustomFieldValues', + FIELD2 => 'CustomField', + ); + $self->Limit( + LEFTJOIN => $CFvs, + FIELD => 'Name', + QUOTEVALUE => 0, + VALUE => "$ocfvs.Content", + ENTRYAGGREGATOR => 'AND' + ); + + return { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' }, + { %$row, ALIAS => $ocfvs, FIELD => 'Content' }; } -# }}} -# {{{ sub LimitAttribute +sub OrderByCols { + my $self = shift; + my @sort; + for my $s (@_) { + next if defined $s->{FIELD} and $s->{FIELD} =~ /\W/; + $s->{FIELD} = $s->{FUNCTION} if $s->{FUNCTION}; + push @sort, $s; + } + return $self->SUPER::OrderByCols( @sort ); +} -=head2 LimitAttribute PARAMHASH +# If we're setting RowsPerPage or FirstRow, ensure we get a natural number or undef. +sub RowsPerPage { + my $self = shift; + return if @_ and defined $_[0] and $_[0] =~ /\D/; + return $self->SUPER::RowsPerPage(@_); +} -Takes NAME, OPERATOR and VALUE to find records that has the -matching Attribute. +sub FirstRow { + my $self = shift; + return if @_ and defined $_[0] and $_[0] =~ /\D/; + return $self->SUPER::FirstRow(@_); +} -If EMPTY is set, also select rows with an empty string as -Attribute's Content. +=head2 LimitToEnabled -If NULL is set, also select rows without the named Attribute. +Only find items that haven't been disabled =cut -my %Negate = qw( - = != - != = - > <= - < >= - >= < - <= > - LIKE NOT LIKE - NOT LIKE LIKE - IS IS NOT - IS NOT IS -); +sub LimitToEnabled { + my $self = shift; -sub LimitAttribute { - my ($self, %args) = @_; - my $clause = 'ALIAS'; - my $operator = ($args{OPERATOR} || '='); - - if ($args{NULL} and exists $args{VALUE}) { - $clause = 'LEFTJOIN'; - $operator = $Negate{$operator}; - } - elsif ($args{NEGATE}) { - $operator = $Negate{$operator}; - } - - my $alias = $self->Join( - TYPE => 'left', - ALIAS1 => $args{ALIAS} || 'main', - FIELD1 => 'id', - TABLE2 => 'Attributes', - FIELD2 => 'ObjectId' - ); + $self->{'handled_disabled_column'} = 1; + $self->Limit( FIELD => 'Disabled', VALUE => '0' ); +} - my $type = ref($self); - $type =~ s/(?:s|Collection)$//; # XXX - Hack! +=head2 LimitToDeleted - $self->Limit( - $clause => $alias, - FIELD => 'ObjectType', - OPERATOR => '=', - VALUE => $type, - ); - $self->Limit( - $clause => $alias, - FIELD => 'Name', - OPERATOR => '=', - VALUE => $args{NAME}, - ) if exists $args{NAME}; +Only find items that have been deleted. - return unless exists $args{VALUE}; +=cut - $self->Limit( - $clause => $alias, - FIELD => 'Content', - OPERATOR => $operator, - VALUE => $args{VALUE}, - ); +sub LimitToDeleted { + my $self = shift; - # Capture rows with the attribute defined as an empty string. - $self->Limit( - $clause => $alias, - FIELD => 'Content', - OPERATOR => '=', - VALUE => '', - ENTRYAGGREGATOR => $args{NULL} ? 'AND' : 'OR', - ) if $args{EMPTY}; - - # Capture rows without the attribute defined - $self->Limit( - %args, - ALIAS => $alias, - FIELD => 'id', - OPERATOR => ($args{NEGATE} ? 'IS NOT' : 'IS'), - VALUE => 'NULL', - ) if $args{NULL}; + $self->{'handled_disabled_column'} = $self->{'find_disabled_rows'} = 1; + $self->Limit( FIELD => 'Disabled', VALUE => '1' ); } -# }}} -# {{{ sub LimitCustomField +=head2 FindAllRows + +Find all matching rows, regardless of whether they are disabled or not + +=cut + +sub FindAllRows { + shift->{'find_disabled_rows'} = 1; +} =head2 LimitCustomField @@ -239,58 +250,605 @@ Takes a paramhash of key/value pairs with the following keys: sub _SingularClass { my $self = shift; - my $class = ref($self); + my $class = ref($self) || $self; $class =~ s/s$// or die "Cannot deduce SingularClass for $class"; return $class; } -sub LimitCustomField { +=head2 RecordClass + +Returns class name of records in this collection. This generic implementation +just strips trailing 's'. + +=cut + +sub RecordClass { + $_[0]->_SingularClass +} + +=head2 RegisterCustomFieldJoin + +Takes a pair of arguments, the first a class name and the second a callback +function. The class will be used to call +L. The callback will be called when +limiting a collection of the caller's class by a CF of the passed class's +lookup type. + +The callback is passed a single argument, the current collection object (C<$self>). + +An example from L: + + __PACKAGE__->RegisterCustomFieldJoin( + "RT::Transaction" => sub { $_[0]->JoinTransactions } + ); + +Returns true on success, undef on failure. + +=cut + +sub RegisterCustomFieldJoin { + my $class = shift; + my ($type, $callback) = @_; + + $type = $type->CustomFieldLookupType if $type; + + die "Unknown LookupType '$type'" + unless $type and grep { $_ eq $type } RT::CustomField->LookupTypes; + + die "Custom field join callbacks must be CODE references" + unless ref($callback) eq 'CODE'; + + warn "Another custom field join callback is already registered for '$type'" + if $class->_JOINS_FOR_LOOKUP_TYPES->{$type}; + + # Stash the callback on ourselves + $class->_JOINS_FOR_LOOKUP_TYPES->{ $type } = $callback; + + return 1; +} + +=head2 _JoinForLookupType + +Takes an L LookupType and joins this collection as +appropriate to reach the object records to which LookupType applies. The +object records will be of the class returned by +L. + +Returns the join alias suitable for further limiting against object +properties. + +Returns undef on failure. + +Used by L. + +=cut + +sub _JoinForLookupType { my $self = shift; - my %args = ( VALUE => undef, - CUSTOMFIELD => undef, - OPERATOR => '=', - @_ ); + my $type = shift or return; - my $alias = $self->Join( - TYPE => 'left', - ALIAS1 => 'main', - FIELD1 => 'id', - TABLE2 => 'ObjectCustomFieldValues', - FIELD2 => 'ObjectId' + # Convenience shortcut so that classes don't need to register a handler + # for their native lookup type + return "main" if $type eq $self->RecordClass->CustomFieldLookupType + and grep { $_ eq $type } RT::CustomField->LookupTypes; + + my $JOINS = $self->_JOINS_FOR_LOOKUP_TYPES; + return $JOINS->{$type}->($self) + if ref $JOINS->{$type} eq 'CODE'; + + return; +} + +sub _JOINS_FOR_LOOKUP_TYPES { + my $class = blessed($_[0]) || $_[0]; + state %JOINS; + return $JOINS{$class} ||= {}; +} + +=head2 _CustomFieldJoin + +Factor out the Join of custom fields so we can use it for sorting too + +=cut + +sub _CustomFieldJoin { + my ($self, $cfkey, $cf, $type) = @_; + $type ||= $self->RecordClass->CustomFieldLookupType; + + # Perform one Join per CustomField + if ( $self->{_sql_object_cfv_alias}{$cfkey} || + $self->{_sql_cf_alias}{$cfkey} ) + { + return ( $self->{_sql_object_cfv_alias}{$cfkey}, + $self->{_sql_cf_alias}{$cfkey} ); + } + + my $ObjectAlias = $self->_JoinForLookupType($type) + or die "We don't know how to join for LookupType $type"; + + my ($ocfvalias, $CFs); + if ( blessed($cf) ) { + $ocfvalias = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $ObjectAlias, + FIELD1 => 'id', + TABLE2 => 'ObjectCustomFieldValues', + FIELD2 => 'ObjectId', + $cf->SingleValue? (DISTINCT => 1) : (), + ); + $self->Limit( + LEFTJOIN => $ocfvalias, + FIELD => 'CustomField', + VALUE => $cf->id, + ENTRYAGGREGATOR => 'AND' + ); + } + else { + ($ocfvalias, $CFs) = $self->_CustomFieldJoinByName( $ObjectAlias, $cf, $type ); + $self->{_sql_cf_alias}{$cfkey} = $CFs; + $self->{_sql_object_cfv_alias}{$cfkey} = $ocfvalias; + } + $self->Limit( + LEFTJOIN => $ocfvalias, + FIELD => 'ObjectType', + VALUE => RT::CustomField->ObjectTypeFromLookupType($type), + ENTRYAGGREGATOR => 'AND' ); $self->Limit( - ALIAS => $alias, - FIELD => 'CustomField', - OPERATOR => '=', - VALUE => $args{'CUSTOMFIELD'}, - ) if ($args{'CUSTOMFIELD'}); + LEFTJOIN => $ocfvalias, + FIELD => 'Disabled', + OPERATOR => '=', + VALUE => '0', + ENTRYAGGREGATOR => 'AND' + ); + + return ($ocfvalias, $CFs); +} + +sub _CustomFieldJoinByName { + my $self = shift; + my ($ObjectAlias, $cf, $type) = @_; + my $ocfalias = $self->Join( + TYPE => 'LEFT', + EXPRESSION => q|'0'|, + TABLE2 => 'ObjectCustomFields', + FIELD2 => 'ObjectId', + ); + + my $CFs = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $ocfalias, + FIELD1 => 'CustomField', + TABLE2 => 'CustomFields', + FIELD2 => 'id', + ); $self->Limit( - ALIAS => $alias, - FIELD => 'ObjectType', - OPERATOR => '=', - VALUE => $self->_SingularClass, + LEFTJOIN => $CFs, + ENTRYAGGREGATOR => 'AND', + FIELD => 'LookupType', + VALUE => $type, ); $self->Limit( - ALIAS => $alias, - FIELD => 'Content', - OPERATOR => $args{'OPERATOR'}, - VALUE => $args{'VALUE'}, + LEFTJOIN => $CFs, + ENTRYAGGREGATOR => 'AND', + FIELD => 'Name', + CASESENSITIVE => 0, + VALUE => $cf, + ); + + my $ocfvalias = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $CFs, + FIELD1 => 'id', + TABLE2 => 'ObjectCustomFieldValues', + FIELD2 => 'CustomField', ); + $self->Limit( + LEFTJOIN => $ocfvalias, + FIELD => 'ObjectId', + VALUE => "$ObjectAlias.id", + QUOTEVALUE => 0, + ENTRYAGGREGATOR => 'AND', + ); + + return ($ocfvalias, $CFs, $ocfalias); } -# {{{ sub FindAllRows +sub LimitCustomField { + my $self = shift; + return $self->_LimitCustomField( @_ ); +} -=head2 FindAllRows +use Regexp::Common qw(RE_net_IPv4); +use Regexp::Common::net::CIDR; -Find all matching rows, regardless of whether they are disabled or not +sub _LimitCustomField { + my $self = shift; + my %args = ( VALUE => undef, + CUSTOMFIELD => undef, + OPERATOR => '=', + KEY => undef, + PREPARSE => 1, + @_ ); -=cut + my $op = delete $args{OPERATOR}; + my $value = delete $args{VALUE}; + my $ltype = delete $args{LOOKUPTYPE} || $self->RecordClass->CustomFieldLookupType; + my $cf = delete $args{CUSTOMFIELD}; + my $column = delete $args{COLUMN}; + my $cfkey = delete $args{KEY}; + if (blessed($cf) and $cf->id) { + $cfkey ||= $cf->id; + } elsif ($cf =~ /^\d+$/) { + # Intentionally load as the system user, so we can build better + # queries; this is necessary as we don't have a context object + # which might grant the user rights to see the CF. This object + # is only used to inspect the properties of the CF itself. + my $obj = RT::CustomField->new( RT->SystemUser ); + $obj->Load($cf); + if ($obj->id) { + $cf = $obj; + $cfkey ||= $cf->id; + } else { + $cfkey ||= "$ltype-$cf"; + } + } else { + $cfkey ||= "$ltype-$cf"; + } -sub FindAllRows { - shift->{'find_disabled_rows'} = 1; -} + $args{SUBCLAUSE} ||= "cf-$cfkey"; + + + my $fix_op = sub { + return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle'; + + my %args = @_; + return %args unless $args{'FIELD'} eq 'LargeContent'; + + my $op = $args{'OPERATOR'}; + if ( $op eq '=' ) { + $args{'OPERATOR'} = 'MATCHES'; + } + elsif ( $op eq '!=' ) { + $args{'OPERATOR'} = 'NOT MATCHES'; + } + elsif ( $op =~ /^[<>]=?$/ ) { + $args{'FUNCTION'} = "TO_CHAR( $args{'ALIAS'}.LargeContent )"; + } + return %args; + }; + + # Special Limit (we can exit early) + # IS NULL and IS NOT NULL checks + if ( $op =~ /^IS( NOT)?$/i ) { + my ($ocfvalias, $CFs) = $self->_CustomFieldJoin( $cfkey, $cf, $ltype ); + $self->_OpenParen( $args{SUBCLAUSE} ); + $self->Limit( + %args, + ALIAS => $ocfvalias, + FIELD => ($column || 'id'), + OPERATOR => $op, + VALUE => $value, + ); + # See below for an explanation of this limit + $self->Limit( + ALIAS => $CFs, + FIELD => 'Name', + OPERATOR => 'IS NOT', + VALUE => 'NULL', + ENTRYAGGREGATOR => 'AND', + SUBCLAUSE => $args{SUBCLAUSE}, + ) if $CFs; + $self->_CloseParen( $args{SUBCLAUSE} ); + return; + } + + ########## Content pre-parsing if we know things about the CF + if ( blessed($cf) and delete $args{PREPARSE} ) { + my $type = $cf->Type; + if ( $type eq 'IPAddress' ) { + my $parsed = RT::ObjectCustomFieldValue->ParseIP($value); + if ($parsed) { + $value = $parsed; + } else { + $RT::Logger->warn("$value is not a valid IPAddress"); + } + } elsif ( $type eq 'IPAddressRange' ) { + my ( $start_ip, $end_ip ) = + RT::ObjectCustomFieldValue->ParseIPRange($value); + if ( $start_ip && $end_ip ) { + if ( $op =~ /^<=?$/ ) { + $value = $start_ip; + } elsif ($op =~ /^>=?$/ ) { + $value = $end_ip; + } else { + $value = join '-', $start_ip, $end_ip; + } + } else { + $RT::Logger->warn("$value is not a valid IPAddressRange"); + } + + # Recurse if they want a range comparison + if ( $op !~ /^[<>]=?$/ ) { + my ($start_ip, $end_ip) = split /-/, $value; + $self->_OpenParen( $args{SUBCLAUSE} ); + # Ideally we would limit >= 000.000.000.000 and <= + # 255.255.255.255 so DB optimizers could use better + # estimations and scan less rows, but this breaks with IPv6. + if ( $op !~ /NOT|!=|<>/i ) { # positive equation + $self->_LimitCustomField( + %args, + OPERATOR => '<=', + VALUE => $end_ip, + LOOKUPTYPE => $ltype, + CUSTOMFIELD => $cf, + COLUMN => 'Content', + PREPARSE => 0, + ); + $self->_LimitCustomField( + %args, + OPERATOR => '>=', + VALUE => $start_ip, + LOOKUPTYPE => $ltype, + CUSTOMFIELD => $cf, + COLUMN => 'LargeContent', + ENTRYAGGREGATOR => 'AND', + PREPARSE => 0, + ); + } else { # negative equation + $self->_LimitCustomField( + %args, + OPERATOR => '>', + VALUE => $end_ip, + LOOKUPTYPE => $ltype, + CUSTOMFIELD => $cf, + COLUMN => 'Content', + PREPARSE => 0, + ); + $self->_LimitCustomField( + %args, + OPERATOR => '<', + VALUE => $start_ip, + LOOKUPTYPE => $ltype, + CUSTOMFIELD => $cf, + COLUMN => 'LargeContent', + ENTRYAGGREGATOR => 'OR', + PREPARSE => 0, + ); + } + $self->_CloseParen( $args{SUBCLAUSE} ); + return; + } + } elsif ( $type =~ /^Date(?:Time)?$/ ) { + my $date = RT::Date->new( $self->CurrentUser ); + $date->Set( Format => 'unknown', Value => $value ); + if ( $date->IsSet ) { + if ( + $type eq 'Date' + # Heuristics to determine if a date, and not + # a datetime, was entered: + || $value =~ /^\s*(?:today|tomorrow|yesterday)\s*$/i + || ( $value !~ /midnight|\d+:\d+:\d+/i + && $date->Time( Timezone => 'user' ) eq '00:00:00' ) + ) + { + $value = $date->Date( Timezone => 'user' ); + } else { + $value = $date->DateTime; + } + } else { + $RT::Logger->warn("$value is not a valid date string"); + } + + # Recurse if day equality is being checked on a datetime + if ( $type eq 'DateTime' and $op eq '=' && $value !~ /:/ ) { + my $date = RT::Date->new( $self->CurrentUser ); + $date->Set( Format => 'unknown', Value => $value ); + my $daystart = $date->ISO; + $date->AddDay; + my $dayend = $date->ISO; + + $self->_OpenParen( $args{SUBCLAUSE} ); + $self->_LimitCustomField( + %args, + OPERATOR => ">=", + VALUE => $daystart, + LOOKUPTYPE => $ltype, + CUSTOMFIELD => $cf, + COLUMN => 'Content', + ENTRYAGGREGATOR => 'AND', + PREPARSE => 0, + ); + + $self->_LimitCustomField( + %args, + OPERATOR => "<", + VALUE => $dayend, + LOOKUPTYPE => $ltype, + CUSTOMFIELD => $cf, + COLUMN => 'Content', + ENTRYAGGREGATOR => 'AND', + PREPARSE => 0, + ); + $self->_CloseParen( $args{SUBCLAUSE} ); + return; + } + } + } + + ########## Limits + + my $single_value = !blessed($cf) || $cf->SingleValue; + my $negative_op = ($op eq '!=' || $op =~ /\bNOT\b/i); + my $value_is_long = (length( Encode::encode( "UTF-8", $value)) > 255) ? 1 : 0; + + $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++ + if not $single_value and $op =~ /^(!?=|(NOT )?LIKE)$/i; + my ($ocfvalias, $CFs) = $self->_CustomFieldJoin( $cfkey, $cf, $ltype ); + + # A negative limit on a multi-value CF means _none_ of the values + # are the given value + if ( $negative_op and not $single_value ) { + # Reverse the limit we apply to the join, and check IS NULL + $op =~ s/!|NOT\s+//i; + + # Ideally we would check both Content and LargeContent here, as + # the positive searches do below -- however, we cannot place + # complex limits inside LEFTJOINs due to searchbuilder + # limitations. Guessing which to check based on the value's + # string length is sufficient for !=, but sadly insufficient for + # NOT LIKE checks, giving false positives. + $column ||= $value_is_long ? 'LargeContent' : 'Content'; + $self->Limit( $fix_op->( + LEFTJOIN => $ocfvalias, + ALIAS => $ocfvalias, + FIELD => $column, + OPERATOR => $op, + VALUE => $value, + CASESENSITIVE => 0, + ) ); + $self->Limit( + %args, + ALIAS => $ocfvalias, + FIELD => 'id', + OPERATOR => 'IS', + VALUE => 'NULL', + ); + return; + } + + # If column is defined, then we just search it that, with no magic + if ( $column ) { + $self->_OpenParen( $args{SUBCLAUSE} ); + $self->Limit( $fix_op->( + %args, + ALIAS => $ocfvalias, + FIELD => $column, + OPERATOR => $op, + VALUE => $value, + CASESENSITIVE => 0, + ) ); + $self->Limit( + ALIAS => $ocfvalias, + FIELD => $column, + OPERATOR => 'IS', + VALUE => 'NULL', + ENTRYAGGREGATOR => 'OR', + SUBCLAUSE => $args{SUBCLAUSE}, + ) if $negative_op; + $self->_CloseParen( $args{SUBCLAUSE} ); + return; + } + + $self->_OpenParen( $args{SUBCLAUSE} ); # For negative_op "OR it is null" clause + $self->_OpenParen( $args{SUBCLAUSE} ); # NAME IS NOT NULL clause + + $self->_OpenParen( $args{SUBCLAUSE} ); # Check Content / LargeContent + if ($value_is_long and $op eq "=") { + # Doesn't matter what Content contains, as it cannot match the + # too-long value; we just look in LargeContent, below. + } elsif ($value_is_long and $op =~ /^(!=|<>)$/) { + # If Content is non-null, that's a valid way to _not_ contain the too-long value. + $self->Limit( + %args, + ALIAS => $ocfvalias, + FIELD => 'Content', + OPERATOR => 'IS NOT', + VALUE => 'NULL', + ); + } else { + # Otherwise, go looking at the Content + $self->Limit( + %args, + ALIAS => $ocfvalias, + FIELD => 'Content', + OPERATOR => $op, + VALUE => $value, + CASESENSITIVE => 0, + ); + } -# {{{ sub Limit + if (!$value_is_long and $op eq "=") { + # Doesn't matter what LargeContent contains, as it cannot match + # the short value. + } elsif (!$value_is_long and $op =~ /^(!=|<>)$/) { + # If LargeContent is non-null, that's a valid way to _not_ + # contain the too-short value. + $self->Limit( + %args, + ALIAS => $ocfvalias, + FIELD => 'LargeContent', + OPERATOR => 'IS NOT', + VALUE => 'NULL', + ENTRYAGGREGATOR => 'OR', + ); + } else { + $self->_OpenParen( $args{SUBCLAUSE} ); # LargeContent check + $self->_OpenParen( $args{SUBCLAUSE} ); # Content is null? + $self->Limit( + ALIAS => $ocfvalias, + FIELD => 'Content', + OPERATOR => '=', + VALUE => '', + ENTRYAGGREGATOR => 'OR', + SUBCLAUSE => $args{SUBCLAUSE}, + ); + $self->Limit( + ALIAS => $ocfvalias, + FIELD => 'Content', + OPERATOR => 'IS', + VALUE => 'NULL', + ENTRYAGGREGATOR => 'OR', + SUBCLAUSE => $args{SUBCLAUSE}, + ); + $self->_CloseParen( $args{SUBCLAUSE} ); # Content is null? + $self->Limit( $fix_op->( + ALIAS => $ocfvalias, + FIELD => 'LargeContent', + OPERATOR => $op, + VALUE => $value, + ENTRYAGGREGATOR => 'AND', + SUBCLAUSE => $args{SUBCLAUSE}, + CASESENSITIVE => 0, + ) ); + $self->_CloseParen( $args{SUBCLAUSE} ); # LargeContent check + } + + $self->_CloseParen( $args{SUBCLAUSE} ); # Check Content/LargeContent + + # XXX: if we join via CustomFields table then + # because of order of left joins we get NULLs in + # CF table and then get nulls for those records + # in OCFVs table what result in wrong results + # as decifer method now tries to load a CF then + # we fall into this situation only when there + # are more than one CF with the name in the DB. + # the same thing applies to order by call. + # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if + # we want treat IS NULL as (not applies or has + # no value) + $self->Limit( + ALIAS => $CFs, + FIELD => 'Name', + OPERATOR => 'IS NOT', + VALUE => 'NULL', + ENTRYAGGREGATOR => 'AND', + SUBCLAUSE => $args{SUBCLAUSE}, + ) if $CFs; + $self->_CloseParen( $args{SUBCLAUSE} ); # Name IS NOT NULL clause + + # If we were looking for != or NOT LIKE, we need to include the + # possibility that the row had no value. + $self->Limit( + ALIAS => $ocfvalias, + FIELD => 'id', + OPERATOR => 'IS', + VALUE => 'NULL', + ENTRYAGGREGATOR => 'OR', + SUBCLAUSE => $args{SUBCLAUSE}, + ) if $negative_op; + $self->_CloseParen( $args{SUBCLAUSE} ); # negative_op clause +} =head2 Limit PARAMHASH @@ -298,19 +856,85 @@ This Limit sub calls SUPER::Limit, but defaults "CASESENSITIVE" to 1, thus making sure that by default lots of things don't do extra work trying to match lower(colname) agaist lc($val); +We also force VALUE to C when the OPERATOR is C or C. +This ensures that we don't pass invalid SQL to the database or allow SQL +injection attacks when we pass through user specified values. + =cut +my %check_case_sensitivity = ( + groups => { 'name' => 1, domain => 1 }, + queues => { 'name' => 1 }, + users => { 'name' => 1, emailaddress => 1 }, + customfields => { 'name' => 1 }, +); + +my %deprecated = ( + groups => { + type => 'Name', + }, + principals => { objectid => 'id' }, +); + sub Limit { my $self = shift; - my %args = ( CASESENSITIVE => 1, - @_ ); + my %ARGS = ( + OPERATOR => '=', + @_, + ); - return $self->SUPER::Limit(%args); -} + # We use the same regex here that DBIx::SearchBuilder uses to exclude + # values from quoting + if ( $ARGS{'OPERATOR'} =~ /IS/i ) { + # Don't pass anything but NULL for IS and IS NOT + $ARGS{'VALUE'} = 'NULL'; + } + + if (($ARGS{FIELD}||'') =~ /\W/ + or $ARGS{OPERATOR} !~ /^(=|<|>|!=|<>|<=|>= + |(NOT\s*)?LIKE + |(NOT\s*)?(STARTS|ENDS)WITH + |(NOT\s*)?MATCHES + |IS(\s*NOT)? + |(NOT\s*)?IN + |\@\@ + |AGAINST)$/ix) { + $RT::Logger->crit("Possible SQL injection attack: $ARGS{FIELD} $ARGS{OPERATOR}"); + %ARGS = ( + %ARGS, + FIELD => 'id', + OPERATOR => '<', + VALUE => '0', + ); + } -# }}} + my $table; + ($table) = $ARGS{'ALIAS'} && $ARGS{'ALIAS'} ne 'main' + ? ($ARGS{'ALIAS'} =~ /^(.*)_\d+$/) + : $self->Table + ; + + if ( $table and $ARGS{FIELD} and my $instead = $deprecated{ lc $table }{ lc $ARGS{'FIELD'} } ) { + RT->Deprecated( + Message => "$table.$ARGS{'FIELD'} column is deprecated", + Instead => $instead, Remove => '4.4' + ); + } -# {{{ sub ItemsOrderBy + unless ( exists $ARGS{CASESENSITIVE} or (exists $ARGS{QUOTEVALUE} and not $ARGS{QUOTEVALUE}) ) { + if ( $ARGS{FIELD} and $ARGS{'OPERATOR'} !~ /IS/i + && $table && $check_case_sensitivity{ lc $table }{ lc $ARGS{'FIELD'} } + ) { + RT->Logger->warning( + "Case sensitive search by $table.$ARGS{'FIELD'}" + ." at ". (caller)[1] . " line ". (caller)[2] + ); + } + $ARGS{'CASESENSITIVE'} = 1; + } + + return $self->SUPER::Limit( %ARGS ); +} =head2 ItemsOrderBy @@ -325,65 +949,104 @@ sub ItemsOrderBy { my $self = shift; my $items = shift; - if ($self->NewItem()->_Accessible('SortOrder','read')) { + if ($self->RecordClass->_Accessible('SortOrder','read')) { $items = [ sort { $a->SortOrder <=> $b->SortOrder } @{$items} ]; } - elsif ($self->NewItem()->_Accessible('Name','read')) { + elsif ($self->RecordClass->_Accessible('Name','read')) { $items = [ sort { lc($a->Name) cmp lc($b->Name) } @{$items} ]; } return $items; } -# }}} - -# {{{ sub ItemsArrayRef - =head2 ItemsArrayRef Return this object's ItemsArray, in the order that ItemsOrderBy sorts it. -=begin testing +=cut -use_ok(RT::Queues); -ok(my $queues = RT::Queues->new($RT::SystemUser), 'Created a queues object'); -ok( $queues->UnLimit(),'Unlimited the result set of the queues object'); -my $items = $queues->ItemsArrayRef(); -my @items = @{$items}; +sub ItemsArrayRef { + my $self = shift; + return $self->ItemsOrderBy($self->SUPER::ItemsArrayRef()); +} -ok($queues->NewItem->_Accessible('Name','read')); -my @sorted = sort {lc($a->Name) cmp lc($b->Name)} @items; -ok (@sorted, "We have an array of queues, sorted". join(',',map {$_->Name} @sorted)); +# make sure that Disabled rows never get seen unless +# we're explicitly trying to see them. -ok (@items, "We have an array of queues, raw". join(',',map {$_->Name} @items)); -my @sorted_ids = map {$_->id } @sorted; -my @items_ids = map {$_->id } @items; +sub _DoSearch { + my $self = shift; -is ($#sorted, $#items); -is ($sorted[0]->Name, $items[0]->Name); -is ($sorted[-1]->Name, $items[-1]->Name); -is_deeply(\@items_ids, \@sorted_ids, "ItemsArrayRef sorts alphabetically by name");; + if ( $self->{'with_disabled_column'} + && !$self->{'handled_disabled_column'} + && !$self->{'find_disabled_rows'} + ) { + $self->LimitToEnabled; + } + return $self->SUPER::_DoSearch(@_); +} +sub _DoCount { + my $self = shift; + if ( $self->{'with_disabled_column'} + && !$self->{'handled_disabled_column'} + && !$self->{'find_disabled_rows'} + ) { + $self->LimitToEnabled; + } + return $self->SUPER::_DoCount(@_); +} -=end testing +=head2 ColumnMapClassName + +ColumnMap needs a Collection name to load the correct list display. +Depluralization is hard, so provide an easy way to correct the naive +algorithm that this code uses. =cut -sub ItemsArrayRef { +sub ColumnMapClassName { + my $self = shift; + my $Class = $self->_SingularClass; + $Class =~ s/:/_/g; + return $Class; +} + +=head2 NewItem + +Returns a new item based on L using the current user. + +=cut + +sub NewItem { my $self = shift; - my @items; - - return $self->ItemsOrderBy($self->SUPER::ItemsArrayRef()); + return $self->RecordClass->new($self->CurrentUser); } -# }}} +=head2 NotSetDateToNullFunction -eval "require RT::SearchBuilder_Vendor"; -die $@ if ($@ && $@ !~ qr{^Can't locate RT/SearchBuilder_Vendor.pm}); -eval "require RT::SearchBuilder_Local"; -die $@ if ($@ && $@ !~ qr{^Can't locate RT/SearchBuilder_Local.pm}); +Takes a paramhash with an optional FIELD key whose value is the name of a date +column. If no FIELD is provided, a literal C placeholder is used so the +caller can fill in the field later. -1; +Returns a SQL function which evaluates to C if the FIELD is set to the +Unix epoch; otherwise it evaluates to FIELD. This is useful because RT +currently stores unset dates as a Unix epoch timestamp instead of NULL, but +NULLs are often more desireable. +=cut +sub NotSetDateToNullFunction { + my $self = shift; + my %args = ( FIELD => undef, @_ ); + + my $res = "CASE WHEN ? BETWEEN '1969-12-31 11:59:59' AND '1970-01-01 12:00:01' THEN NULL ELSE ? END"; + if ( $args{FIELD} ) { + $res = $self->CombineFunctionWithField( %args, FUNCTION => $res ); + } + return $res; +} + +RT::Base->_ImportOverlays(); + +1;