SearchBuilder.pm 31.4 KB
Newer Older
1
# BEGIN BPS TAGGED BLOCK {{{
Jesse Vincent's avatar
Jesse Vincent committed
2
#
3
# COPYRIGHT:
Jesse Vincent's avatar
Jesse Vincent committed
4
#
5
# This software is Copyright (c) 1996-2014 Best Practical Solutions, LLC
Kevin Falcone's avatar
Kevin Falcone committed
6
#                                          <sales@bestpractical.com>
Jesse Vincent's avatar
Jesse Vincent committed
7
#
8
# (Except where explicitly superseded by other copyright notices)
Jesse Vincent's avatar
Jesse Vincent committed
9
10
#
#
11
# LICENSE:
Jesse Vincent's avatar
Jesse Vincent committed
12
#
13
14
15
# 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
16
# from www.gnu.org.
Jesse Vincent's avatar
Jesse Vincent committed
17
#
18
19
20
21
# 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.
Jesse Vincent's avatar
Jesse Vincent committed
22
#
23
24
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
Jesse Vincent's avatar
Jesse Vincent committed
25
26
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301 or visit their web page on the internet at
Ruslan Zakirov's avatar
Ruslan Zakirov committed
27
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
Jesse Vincent's avatar
Jesse Vincent committed
28
29
#
#
30
# CONTRIBUTION SUBMISSION POLICY:
Jesse Vincent's avatar
Jesse Vincent committed
31
#
32
33
34
35
36
# (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.)
Jesse Vincent's avatar
Jesse Vincent committed
37
#
38
39
40
41
42
43
44
45
# 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
# you are the copyright holder for those contributions and you grant
# Best Practical Solutions,  LLC a nonexclusive, worldwide, irrevocable,
# royalty-free, perpetual, license to use, copy, create derivative
# works based on those contributions, and sublicense and distribute
# those contributions and any derivatives thereof.
Jesse Vincent's avatar
Jesse Vincent committed
46
#
47
# END BPS TAGGED BLOCK }}}
48

Jesse Vincent's avatar
Jesse Vincent committed
49
50
=head1 NAME

Jesse Vincent's avatar
rt.2.1    
Jesse Vincent committed
51
  RT::SearchBuilder - a baseclass for RT collection objects
Jesse Vincent's avatar
Jesse Vincent committed
52
53
54
55
56
57
58
59

=head1 SYNOPSIS

=head1 DESCRIPTION


=head1 METHODS

Jesse Vincent's avatar
Jesse Vincent committed
60
61
62



Jesse Vincent's avatar
Jesse Vincent committed
63
64
=cut

Jesse Vincent's avatar
rt.2.1    
Jesse Vincent committed
65
package RT::SearchBuilder;
66

67
use strict;
Ruslan Zakirov's avatar
minor    
Ruslan Zakirov committed
68
use warnings;
69
use 5.010;
Ruslan Zakirov's avatar
minor    
Ruslan Zakirov committed
70
71

use base qw(DBIx::SearchBuilder RT::Base);
Jesse Vincent's avatar
Jesse Vincent committed
72

73
74
75
use RT::Base;
use DBIx::SearchBuilder "1.40";

76
77
use Scalar::Util qw/blessed/;

78
sub _Init  {
79
80
81
    my $self = shift;
    
    $self->{'user'} = shift;
82
    unless(defined($self->CurrentUser)) {
83
84
85
86
        use Carp;
        Carp::confess("$self was created without a CurrentUser");
        $RT::Logger->err("$self was created without a CurrentUser");
        return(0);
87
    }
88
    $self->SUPER::_Init( 'Handle' => $RT::Handle);
Jesse Vincent's avatar
Jesse Vincent committed
89
}
90

91
92
sub _Handle { return $RT::Handle }

Ruslan Zakirov's avatar
Ruslan Zakirov committed
93
94
95
sub CleanSlate {
    my $self = shift;
    $self->{'_sql_aliases'} = {};
96
97
    delete $self->{'handled_disabled_column'};
    delete $self->{'find_disabled_rows'};
Ruslan Zakirov's avatar
Ruslan Zakirov committed
98
99
100
    return $self->SUPER::CleanSlate(@_);
}

101
102
103
104
105
106
107
108
109
110
111
sub Join {
    my $self = shift;
    my %args = @_;

    $args{'DISTINCT'} = 1 if
        !exists $args{'DISTINCT'}
        && $args{'TABLE2'} && lc($args{'FIELD2'}||'') eq 'id';

    return $self->SUPER::Join( %args );
}

Ruslan Zakirov's avatar
Ruslan Zakirov committed
112
113
114
115
116
117
118
119
120
121
122
123
124
sub JoinTransactions {
    my $self = shift;
    my %args = ( New => 0, @_ );

    return $self->{'_sql_aliases'}{'transactions'}
        if !$args{'New'} && $self->{'_sql_aliases'}{'transactions'};

    my $alias = $self->Join(
        ALIAS1 => 'main',
        FIELD1 => 'id',
        TABLE2 => 'Transactions',
        FIELD2 => 'ObjectId',
    );
125

126
    # NewItem is necessary here because of RT::Report::Tickets and RT::Report::Tickets::Entry
127
128
129
    my $item = $self->NewItem;
    my $object_type = $item->can('ObjectType') ? $item->ObjectType : ref $item;

130
    $self->RT::SearchBuilder::Limit(
Ruslan Zakirov's avatar
Ruslan Zakirov committed
131
132
        LEFTJOIN => $alias,
        FIELD    => 'ObjectType',
133
        VALUE    => $object_type,
Ruslan Zakirov's avatar
Ruslan Zakirov committed
134
135
136
137
138
139
140
    );
    $self->{'_sql_aliases'}{'transactions'} = $alias
        unless $args{'New'};

    return $alias;
}

141
142
sub _OrderByCF {
    my $self = shift;
143
    my ($row, $cfkey, $cf) = @_;
144
145
146
147
148
149
150
151
152
153

    $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',
154
        SUBCLAUSE  => ".ordering",
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
    ) 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' };
}

175
176
177
178
179
180
181
182
183
184
185
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 );
}

186
187
188
189
190
191
192
193
194
195
196
197
198
# 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(@_);
}

sub FirstRow {
    my $self = shift;
    return if @_ and defined $_[0] and $_[0] =~ /\D/;
    return $self->SUPER::FirstRow(@_);
}

199
200
=head2 LimitToEnabled

Ruslan Zakirov's avatar
minor    
Ruslan Zakirov committed
201
Only find items that haven't been disabled
202
203
204
205
206

=cut

sub LimitToEnabled {
    my $self = shift;
207
208
209

    $self->{'handled_disabled_column'} = 1;
    $self->Limit( FIELD => 'Disabled', VALUE => '0' );
210
211
212
213
214
215
216
217
218
219
}

=head2 LimitToDeleted

Only find items that have been deleted.

=cut

sub LimitToDeleted {
    my $self = shift;
220
221
222

    $self->{'handled_disabled_column'} = $self->{'find_disabled_rows'} = 1;
    $self->Limit( FIELD => 'Disabled', VALUE => '1' );
223
}
224

Ruslan Zakirov's avatar
Ruslan Zakirov committed
225
226
227
228
229
230
231
232
233
234
=head2 FindAllRows

Find all matching rows, regardless of whether they are disabled or not

=cut

sub FindAllRows {
    shift->{'find_disabled_rows'} = 1;
}

235
236
237
238
239
240
=head2 LimitCustomField

Takes a paramhash of key/value pairs with the following keys:

=over 4

241
=item CUSTOMFIELD - CustomField id. Optional
242
243
244
245
246
247
248
249
250
251
252

=item OPERATOR - The usual Limit operators

=item VALUE - The value to compare against

=back

=cut

sub _SingularClass {
    my $self = shift;
253
    my $class = ref($self) || $self;
254
255
256
257
    $class =~ s/s$// or die "Cannot deduce SingularClass for $class";
    return $class;
}

258
259
260
261
262
263
264
265
266
267
268
=head2 RecordClass

Returns class name of records in this collection. This generic implementation
just strips trailing 's'.

=cut

sub RecordClass {
    $_[0]->_SingularClass
}

269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
=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<RT::Record/CustomFieldLookupType>.  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<RT::Tickets>:

    __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<RT::CustomField> 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<RT::CustomField/ObjectTypeFromLookupType>.

Returns the join alias suitable for further limiting against object
properties.

Returns undef on failure.

Used by L</_CustomFieldJoin>.

=cut

sub _JoinForLookupType {
    my $self = shift;
    my $type = shift or return;

    # 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} ||= {};
}

348
=head2 _CustomFieldJoin
349

350
351
352
353
354
Factor out the Join of custom fields so we can use it for sorting too

=cut

sub _CustomFieldJoin {
355
356
357
    my ($self, $cfkey, $cf, $type) = @_;
    $type ||= $self->RecordClass->CustomFieldLookupType;

358
359
360
361
362
363
364
365
    # 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} );
    }

366
367
368
    my $ObjectAlias = $self->_JoinForLookupType($type)
        or die "We don't know how to join for LookupType $type";

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
375
376
            FIELD1 => 'id',
            TABLE2 => 'ObjectCustomFieldValues',
            FIELD2 => 'ObjectId',
377
            $cf->SingleValue? (DISTINCT => 1) : (),
378
379
        );
        $self->Limit(
380
            LEFTJOIN        => $ocfvalias,
381
            FIELD           => 'CustomField',
382
            VALUE           => $cf->id,
383
384
385
386
            ENTRYAGGREGATOR => 'AND'
        );
    }
    else {
387
        ($ocfvalias, $CFs) = $self->_CustomFieldJoinByName( $ObjectAlias, $cf, $type );
388
389
        $self->{_sql_cf_alias}{$cfkey} = $CFs;
        $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
400
401
402
403
404
        FIELD           => 'Disabled',
        OPERATOR        => '=',
        VALUE           => '0',
        ENTRYAGGREGATOR => 'AND'
    );

405
    return ($ocfvalias, $CFs);
406
407
}

408
409
sub _CustomFieldJoinByName {
    my $self = shift;
410
    my ($ObjectAlias, $cf, $type) = @_;
411
412
413
414
415
416
417
418
419
420
421
422
423
424
    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',
    );
425
    $self->Limit(
426
427
428
        LEFTJOIN        => $CFs,
        ENTRYAGGREGATOR => 'AND',
        FIELD           => 'LookupType',
429
        VALUE           => $type,
430
431
    );
    $self->Limit(
432
433
434
        LEFTJOIN        => $CFs,
        ENTRYAGGREGATOR => 'AND',
        FIELD           => 'Name',
435
        CASESENSITIVE   => 0,
436
437
438
439
440
441
442
443
444
        VALUE           => $cf,
    );

    my $ocfvalias = $self->Join(
        TYPE   => 'LEFT',
        ALIAS1 => $CFs,
        FIELD1 => 'id',
        TABLE2 => 'ObjectCustomFieldValues',
        FIELD2 => 'CustomField',
445
    );
446
    $self->Limit(
447
448
        LEFTJOIN        => $ocfvalias,
        FIELD           => 'ObjectId',
449
        VALUE           => "$ObjectAlias.id",
450
451
        QUOTEVALUE      => 0,
        ENTRYAGGREGATOR => 'AND',
452
    );
453
454
455
456

    return ($ocfvalias, $CFs, $ocfalias);
}

457
458
sub LimitCustomField {
    my $self = shift;
459
    return $self->_LimitCustomField( @_ );
460
}
461

462
463
464
465
466
use Regexp::Common qw(RE_net_IPv4);
use Regexp::Common::net::CIDR;

sub _LimitCustomField {
    my $self = shift;
467
468
469
470
    my %args = ( VALUE        => undef,
                 CUSTOMFIELD  => undef,
                 OPERATOR     => '=',
                 KEY          => undef,
471
                 PREPARSE     => 1,
472
                 @_ );
473

474
475
    my $op     = delete $args{OPERATOR};
    my $value  = delete $args{VALUE};
476
    my $ltype  = delete $args{LOOKUPTYPE} || $self->RecordClass->CustomFieldLookupType;
477
478
479
480
481
    my $cf     = delete $args{CUSTOMFIELD};
    my $column = delete $args{COLUMN};
    my $cfkey  = delete $args{KEY};
    if (blessed($cf) and $cf->id) {
        $cfkey ||= $cf->id;
482
    } elsif ($cf =~ /^\d+$/) {
483
484
485
486
487
        # 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 );
488
489
490
491
492
        $obj->Load($cf);
        if ($obj->id) {
            $cf = $obj;
            $cfkey ||= $cf->id;
        } else {
493
            $cfkey ||= "$ltype-$cf";
494
        }
495
    } else {
496
        $cfkey ||= "$ltype-$cf";
497
    }
498

499
500
    $args{SUBCLAUSE} ||= "cf-$cfkey";

501
502
503
504
505
506

    my $fix_op = sub {
        return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle';

        my %args = @_;
        return %args unless $args{'FIELD'} eq 'LargeContent';
Alex Vandiver's avatar
Alex Vandiver committed
507

508
509
510
511
512
513
514
515
516
517
518
519
520
        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;
    };

521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
    # 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;
    }

Alex Vandiver's avatar
Alex Vandiver committed
546
    ########## Content pre-parsing if we know things about the CF
547
    if ( blessed($cf) and delete $args{PREPARSE} ) {
548
549
        my $type = $cf->Type;
        if ( $type eq 'IPAddress' ) {
Alex Vandiver's avatar
Alex Vandiver committed
550
551
552
553
554
555
            my $parsed = RT::ObjectCustomFieldValue->ParseIP($value);
            if ($parsed) {
                $value = $parsed;
            } else {
                $RT::Logger->warn("$value is not a valid IPAddress");
            }
556
        } elsif ( $type eq 'IPAddressRange' ) {
Alex Vandiver's avatar
Alex Vandiver committed
557
558
559
560
            my ( $start_ip, $end_ip ) =
              RT::ObjectCustomFieldValue->ParseIPRange($value);
            if ( $start_ip && $end_ip ) {
                if ( $op =~ /^<=?$/ ) {
561
                    $value = $start_ip;
Alex Vandiver's avatar
Alex Vandiver committed
562
                } elsif ($op =~ /^>=?$/ ) {
563
                    $value = $end_ip;
Alex Vandiver's avatar
Alex Vandiver committed
564
565
                } else {
                    $value = join '-', $start_ip, $end_ip;
566
                }
Alex Vandiver's avatar
Alex Vandiver committed
567
568
            } else {
                $RT::Logger->warn("$value is not a valid IPAddressRange");
569
            }
570
571
572
573
574
575
576
577
578
579
580
581
582

            # 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,
583
                        LOOKUPTYPE  => $ltype,
584
585
                        CUSTOMFIELD => $cf,
                        COLUMN      => 'Content',
586
                        PREPARSE    => 0,
587
588
589
590
591
                    );
                    $self->_LimitCustomField(
                        %args,
                        OPERATOR    => '>=',
                        VALUE       => $start_ip,
592
                        LOOKUPTYPE  => $ltype,
593
594
595
                        CUSTOMFIELD => $cf,
                        COLUMN      => 'LargeContent',
                        ENTRYAGGREGATOR => 'AND',
596
                        PREPARSE    => 0,
597
598
599
600
601
602
                    );
                } else { # negative equation
                    $self->_LimitCustomField(
                        %args,
                        OPERATOR    => '>',
                        VALUE       => $end_ip,
603
                        LOOKUPTYPE  => $ltype,
604
605
                        CUSTOMFIELD => $cf,
                        COLUMN      => 'Content',
606
                        PREPARSE    => 0,
607
608
609
610
611
                    );
                    $self->_LimitCustomField(
                        %args,
                        OPERATOR    => '<',
                        VALUE       => $start_ip,
612
                        LOOKUPTYPE  => $ltype,
613
614
615
                        CUSTOMFIELD => $cf,
                        COLUMN      => 'LargeContent',
                        ENTRYAGGREGATOR => 'OR',
616
                        PREPARSE    => 0,
617
618
619
620
621
                    );
                }
                $self->_CloseParen( $args{SUBCLAUSE} );
                return;
            }
622
        } elsif ( $type =~ /^Date(?:Time)?$/ ) {
Alex Vandiver's avatar
Alex Vandiver committed
623
624
            my $date = RT::Date->new( $self->CurrentUser );
            $date->Set( Format => 'unknown', Value => $value );
625
            if ( $date->IsSet ) {
Alex Vandiver's avatar
Alex Vandiver committed
626
                if (
627
                       $type eq 'Date'
Alex Vandiver's avatar
Alex Vandiver committed
628
629
630
631
632
633
634
635
636
637
638
639
640
                           # 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");
641
            }
642
643

            # Recurse if day equality is being checked on a datetime
644
            if ( $type eq 'DateTime' and $op eq '=' && $value !~ /:/ ) {
645
646
647
648
649
650
651
652
653
654
655
                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,
656
                    LOOKUPTYPE      => $ltype,
657
658
659
                    CUSTOMFIELD     => $cf,
                    COLUMN          => 'Content',
                    ENTRYAGGREGATOR => 'AND',
660
                    PREPARSE        => 0,
661
662
663
664
665
666
                );

                $self->_LimitCustomField(
                    %args,
                    OPERATOR        => "<",
                    VALUE           => $dayend,
667
                    LOOKUPTYPE      => $ltype,
668
669
670
                    CUSTOMFIELD     => $cf,
                    COLUMN          => 'Content',
                    ENTRYAGGREGATOR => 'AND',
671
                    PREPARSE        => 0,
672
673
674
675
                );
                $self->_CloseParen( $args{SUBCLAUSE} );
                return;
            }
676
677
678
        }
    }

Alex Vandiver's avatar
Alex Vandiver committed
679
    ########## Limits
680
681
682

    my $single_value = !blessed($cf) || $cf->SingleValue;
    my $negative_op = ($op eq '!=' || $op =~ /\bNOT\b/i);
683
    my $value_is_long = (length( Encode::encode( "UTF-8", $value)) > 255) ? 1 : 0;
684

685
686
    $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++
        if not $single_value and $op =~ /^(!?=|(NOT )?LIKE)$/i;
687
    my ($ocfvalias, $CFs) = $self->_CustomFieldJoin( $cfkey, $cf, $ltype );
688

689
690
691
692
693
694
    # 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;

695
696
697
698
699
700
701
        # 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';
702
703
704
        $self->Limit( $fix_op->(
            LEFTJOIN   => $ocfvalias,
            ALIAS      => $ocfvalias,
705
            FIELD      => $column,
706
707
708
709
            OPERATOR   => $op,
            VALUE      => $value,
            CASESENSITIVE => 0,
        ) );
710
711
712
713
714
715
716
        $self->Limit(
            %args,
            ALIAS      => $ocfvalias,
            FIELD      => 'id',
            OPERATOR   => 'IS',
            VALUE      => 'NULL',
        );
717
718
        return;
    }
719

720
    # If column is defined, then we just search it that, with no magic
721
    if ( $column ) {
722
        $self->_OpenParen( $args{SUBCLAUSE} );
723
724
725
726
727
728
729
730
        $self->Limit( $fix_op->(
            %args,
            ALIAS      => $ocfvalias,
            FIELD      => $column,
            OPERATOR   => $op,
            VALUE      => $value,
            CASESENSITIVE => 0,
        ) );
731
732
733
734
735
736
737
738
739
        $self->Limit(
            ALIAS           => $ocfvalias,
            FIELD           => $column,
            OPERATOR        => 'IS',
            VALUE           => 'NULL',
            ENTRYAGGREGATOR => 'OR',
            SUBCLAUSE       => $args{SUBCLAUSE},
        ) if $negative_op;
        $self->_CloseParen( $args{SUBCLAUSE} );
740
        return;
741
    }
742
743
744
745
746

    $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
747
748
749
750
751
752
753
754
755
756
757
758
759
760
    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
761
762
763
764
765
766
767
768
        $self->Limit(
            %args,
            ALIAS    => $ocfvalias,
            FIELD    => 'Content',
            OPERATOR => $op,
            VALUE    => $value,
            CASESENSITIVE => 0,
        );
769
    }
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785

    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 {
786
787
        $self->_OpenParen( $args{SUBCLAUSE} ); # LargeContent check
        $self->_OpenParen( $args{SUBCLAUSE} ); # Content is null?
788
        $self->Limit(
789
790
791
792
            ALIAS           => $ocfvalias,
            FIELD           => 'Content',
            OPERATOR        => '=',
            VALUE           => '',
793
            ENTRYAGGREGATOR => 'OR',
794
795
796
797
798
799
            SUBCLAUSE       => $args{SUBCLAUSE},
        );
        $self->Limit(
            ALIAS           => $ocfvalias,
            FIELD           => 'Content',
            OPERATOR        => 'IS',
800
            VALUE           => 'NULL',
801
            ENTRYAGGREGATOR => 'OR',
802
            SUBCLAUSE       => $args{SUBCLAUSE},
803
        );
804
        $self->_CloseParen( $args{SUBCLAUSE} ); # Content is null?
805
806
807
808
809
810
811
812
813
        $self->Limit( $fix_op->(
            ALIAS           => $ocfvalias,
            FIELD           => 'LargeContent',
            OPERATOR        => $op,
            VALUE           => $value,
            ENTRYAGGREGATOR => 'AND',
            SUBCLAUSE       => $args{SUBCLAUSE},
            CASESENSITIVE => 0,
        ) );
814
        $self->_CloseParen( $args{SUBCLAUSE} ); # LargeContent check
815
    }
816
817

    $self->_CloseParen( $args{SUBCLAUSE} ); # Check Content/LargeContent
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837

    # 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;
838
839
840
841
842
843
    $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,
844
        FIELD           => 'id',
845
846
847
848
849
850
        OPERATOR        => 'IS',
        VALUE           => 'NULL',
        ENTRYAGGREGATOR => 'OR',
        SUBCLAUSE       => $args{SUBCLAUSE},
    ) if $negative_op;
    $self->_CloseParen( $args{SUBCLAUSE} ); # negative_op clause
851
}
852

853
854
=head2 Limit PARAMHASH

855
This Limit sub calls SUPER::Limit, but defaults "CASESENSITIVE" to 1, thus
856
857
858
making sure that by default lots of things don't do extra work trying to 
match lower(colname) agaist lc($val);

859
860
861
862
We also force VALUE to C<NULL> when the OPERATOR is C<IS> or C<IS NOT>.
This ensures that we don't pass invalid SQL to the database or allow SQL
injection attacks when we pass through user specified values.

863
864
=cut

865
my %check_case_sensitivity = (
866
    groups => { 'name' => 1, domain => 1 },
867
    queues => { 'name' => 1 },
868
    users => { 'name' => 1, emailaddress => 1 },
869
    customfields => { 'name' => 1 },
870
871
);

Ruslan Zakirov's avatar
Ruslan Zakirov committed
872
873
874
875
my %deprecated = (
    groups => {
        type => 'Name',
    },
Ruslan Zakirov's avatar
Ruslan Zakirov committed
876
    principals => { objectid => 'id' },
Ruslan Zakirov's avatar
Ruslan Zakirov committed
877
878
);

879
sub Limit {
880
    my $self = shift;
881
882
883
    my %ARGS = (
        OPERATOR => '=',
        @_,
884
885
886
887
    );

    # We use the same regex here that DBIx::SearchBuilder uses to exclude
    # values from quoting
888
    if ( $ARGS{'OPERATOR'} =~ /IS/i ) {
889
        # Don't pass anything but NULL for IS and IS NOT
890
        $ARGS{'VALUE'} = 'NULL';
891
892
    }

893
    if (($ARGS{FIELD}||'') =~ /\W/
894
895
896
897
898
          or $ARGS{OPERATOR} !~ /^(=|<|>|!=|<>|<=|>=
                                  |(NOT\s*)?LIKE
                                  |(NOT\s*)?(STARTS|ENDS)WITH
                                  |(NOT\s*)?MATCHES
                                  |IS(\s*NOT)?
899
                                  |(NOT\s*)?IN
900
901
                                  |\@\@)$/ix) {
        $RT::Logger->crit("Possible SQL injection attack: $ARGS{FIELD} $ARGS{OPERATOR}");
902
        %ARGS = (
903
904
905
906
907
908
            %ARGS,
            FIELD    => 'id',
            OPERATOR => '<',
            VALUE    => '0',
        );
    }
Ruslan Zakirov's avatar
Ruslan Zakirov committed
909
910
911
912
913
914
915

    my $table;
    ($table) = $ARGS{'ALIAS'} && $ARGS{'ALIAS'} ne 'main'
        ? ($ARGS{'ALIAS'} =~ /^(.*)_\d+$/)
        : $self->Table
    ;

916
    if ( $table and $ARGS{FIELD} and my $instead = $deprecated{ lc $table }{ lc $ARGS{'FIELD'} } ) {
Ruslan Zakirov's avatar
Ruslan Zakirov committed
917
918
919
920
921
922
        RT->Deprecated(
            Message => "$table.$ARGS{'FIELD'} column is deprecated",
            Instead => $instead, Remove => '4.4'
        );
    }

923
    unless ( exists $ARGS{CASESENSITIVE} or (exists $ARGS{QUOTEVALUE} and not $ARGS{QUOTEVALUE}) ) {
924
        if ( $ARGS{FIELD} and $ARGS{'OPERATOR'} !~ /IS/i
925
926
            && $table && $check_case_sensitivity{ lc $table }{ lc $ARGS{'FIELD'} }
        ) {
927
928
929
930
931
932
933
934
            RT->Logger->warning(
                "Case sensitive search by $table.$ARGS{'FIELD'}"
                ." at ". (caller)[1] . " line ". (caller)[2]
            );
        }
        $ARGS{'CASESENSITIVE'} = 1;
    }

935
    return $self->SUPER::Limit( %ARGS );
936
937
}

Kevin Riggle's avatar
Kevin Riggle committed
938
=head2 ItemsOrderBy
939
940
941

If it has a SortOrder attribute, sort the array by SortOrder.
Otherwise, if it has a "Name" attribute, sort alphabetically by Name
942
943
944
945
946
947
948
949
950
Otherwise, just give up and return it in the order it came from the
db.

=cut

sub ItemsOrderBy {
    my $self = shift;
    my $items = shift;
  
951
    if ($self->RecordClass->_Accessible('SortOrder','read')) {
952
953
        $items = [ sort { $a->SortOrder <=> $b->SortOrder } @{$items} ];
    }
954
    elsif ($self->RecordClass->_Accessible('Name','read')) {
955
956
        $items = [ sort { lc($a->Name) cmp lc($b->Name) } @{$items} ];
    }
957

958
959
    return $items;
}
960

Kevin Riggle's avatar
Kevin Riggle committed
961
=head2 ItemsArrayRef
962
963
964

Return this object's ItemsArray, in the order that ItemsOrderBy sorts
it.
965

966
967
=cut

968
969
sub ItemsArrayRef {
    my $self = shift;
970
    return $self->ItemsOrderBy($self->SUPER::ItemsArrayRef());
971
972
}

973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
# make sure that Disabled rows never get seen unless
# we're explicitly trying to see them.

sub _DoSearch {
    my $self = shift;

    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(@_);
}

999
1000
=head2 ColumnMapClassName