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