+ 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";
+ }
+
+ $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,
+ );
+ }
+
+ 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)