Putting 4.2.0 on top of 4.0.17
[usit-rt.git] / lib / RT / SearchBuilder.pm
CommitLineData
84fb5b46
MKG
1# BEGIN BPS TAGGED BLOCK {{{
2#
3# COPYRIGHT:
4#
403d7b0b 5# This software is Copyright (c) 1996-2013 Best Practical Solutions, LLC
84fb5b46
MKG
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
84fb5b46
MKG
67use strict;
68use warnings;
af59614d 69use 5.010;
84fb5b46
MKG
70
71use base qw(DBIx::SearchBuilder RT::Base);
72
af59614d
MKG
73use RT::Base;
74use DBIx::SearchBuilder "1.40";
75
76use Scalar::Util qw/blessed/;
77
84fb5b46
MKG
78sub _Init {
79 my $self = shift;
80
81 $self->{'user'} = shift;
82 unless(defined($self->CurrentUser)) {
af59614d
MKG
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);
84fb5b46
MKG
87 }
88 $self->SUPER::_Init( 'Handle' => $RT::Handle);
89}
90
01e3b242
MKG
91sub _Handle { return $RT::Handle }
92
84fb5b46
MKG
93sub CleanSlate {
94 my $self = shift;
95 $self->{'_sql_aliases'} = {};
01e3b242
MKG
96 delete $self->{'handled_disabled_column'};
97 delete $self->{'find_disabled_rows'};
84fb5b46
MKG
98 return $self->SUPER::CleanSlate(@_);
99}
100
af59614d
MKG
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
84fb5b46
MKG
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 );
b5747ff2 125
af59614d 126 # NewItem is necessary here because of RT::Report::Tickets and RT::Report::Tickets::Entry
b5747ff2
MKG
127 my $item = $self->NewItem;
128 my $object_type = $item->can('ObjectType') ? $item->ObjectType : ref $item;
129
84fb5b46
MKG
130 $self->RT::SearchBuilder::Limit(
131 LEFTJOIN => $alias,
132 FIELD => 'ObjectType',
b5747ff2 133 VALUE => $object_type,
84fb5b46
MKG
134 );
135 $self->{'_sql_aliases'}{'transactions'} = $alias
136 unless $args{'New'};
137
138 return $alias;
139}
140
af59614d
MKG
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
84fb5b46
MKG
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;
af59614d 253 my $class = ref($self) || $self;
84fb5b46
MKG
254 $class =~ s/s$// or die "Cannot deduce SingularClass for $class";
255 return $class;
256}
257
af59614d
MKG
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 {
84fb5b46 327 my $self = shift;
af59614d 328 my $type = shift or return;
84fb5b46 329
af59614d
MKG
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->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'
84fb5b46
MKG
396 );
397 $self->Limit(
af59614d
MKG
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 );
84fb5b46 425 $self->Limit(
af59614d
MKG
426 LEFTJOIN => $CFs,
427 ENTRYAGGREGATOR => 'AND',
428 FIELD => 'LookupType',
429 VALUE => $type,
84fb5b46
MKG
430 );
431 $self->Limit(
af59614d
MKG
432 LEFTJOIN => $CFs,
433 ENTRYAGGREGATOR => 'AND',
434 FIELD => 'Name',
435 CASESENSITIVE => 0,
436 VALUE => $cf,
437 );
438
439 my $ocfvalias = $self->Join(
440 TYPE => 'LEFT',
441 ALIAS1 => $CFs,
442 FIELD1 => 'id',
443 TABLE2 => 'ObjectCustomFieldValues',
444 FIELD2 => 'CustomField',
dab09ea8
MKG
445 );
446 $self->Limit(
af59614d
MKG
447 LEFTJOIN => $ocfvalias,
448 FIELD => 'ObjectId',
449 VALUE => "$ObjectAlias.id",
450 QUOTEVALUE => 0,
451 ENTRYAGGREGATOR => 'AND',
84fb5b46 452 );
af59614d
MKG
453
454 return ($ocfvalias, $CFs, $ocfalias);
455}
456
457sub LimitCustomField {
458 my $self = shift;
459 return $self->_LimitCustomField( @_ );
460}
461
462use Regexp::Common qw(RE_net_IPv4);
463use Regexp::Common::net::CIDR;
464
465sub _LimitCustomField {
466 my $self = shift;
467 my %args = ( VALUE => undef,
468 CUSTOMFIELD => undef,
469 OPERATOR => '=',
470 KEY => undef,
471 PREPARSE => 1,
472 @_ );
473
474 my $op = delete $args{OPERATOR};
475 my $value = delete $args{VALUE};
476 my $ltype = delete $args{LOOKUPTYPE} || $self->RecordClass->CustomFieldLookupType;
477 my $cf = delete $args{CUSTOMFIELD};
478 my $column = delete $args{COLUMN};
479 my $cfkey = delete $args{KEY};
480 if (blessed($cf) and $cf->id) {
481 $cfkey ||= $cf->id;
482 } elsif ($cf =~ /^\d+$/) {
483 # Intentionally load as the system user, so we can build better
484 # queries; this is necessary as we don't have a context object
485 # which might grant the user rights to see the CF. This object
486 # is only used to inspect the properties of the CF itself.
487 my $obj = RT::CustomField->new( RT->SystemUser );
488 $obj->Load($cf);
489 if ($obj->id) {
490 $cf = $obj;
491 $cfkey ||= $cf->id;
492 } else {
493 $cfkey ||= "$ltype-$cf";
494 }
495 } else {
496 $cfkey ||= "$ltype-$cf";
497 }
498
499 $args{SUBCLAUSE} ||= "cf-$cfkey";
500
501
502 my $fix_op = sub {
503 return @_ unless RT->Config->Get('DatabaseType') eq 'Oracle';
504
505 my %args = @_;
506 return %args unless $args{'FIELD'} eq 'LargeContent';
507
508 my $op = $args{'OPERATOR'};
509 if ( $op eq '=' ) {
510 $args{'OPERATOR'} = 'MATCHES';
511 }
512 elsif ( $op eq '!=' ) {
513 $args{'OPERATOR'} = 'NOT MATCHES';
514 }
515 elsif ( $op =~ /^[<>]=?$/ ) {
516 $args{'FUNCTION'} = "TO_CHAR( $args{'ALIAS'}.LargeContent )";
517 }
518 return %args;
519 };
520
521 ########## Content pre-parsing if we know things about the CF
522 if ( blessed($cf) and delete $args{PREPARSE} ) {
523 my $type = $cf->Type;
524 if ( $type eq 'IPAddress' ) {
525 my $parsed = RT::ObjectCustomFieldValue->ParseIP($value);
526 if ($parsed) {
527 $value = $parsed;
528 } else {
529 $RT::Logger->warn("$value is not a valid IPAddress");
530 }
531 } elsif ( $type eq 'IPAddressRange' ) {
532 if ( $value =~ /^\s*$RE{net}{CIDR}{IPv4}{-keep}\s*$/o ) {
533 # convert incomplete 192.168/24 to 192.168.0.0/24 format
534 $value =
535 join( '.', map $_ || 0, ( split /\./, $1 )[ 0 .. 3 ] ) . "/$2"
536 || $value;
537 }
538
539 my ( $start_ip, $end_ip ) =
540 RT::ObjectCustomFieldValue->ParseIPRange($value);
541 if ( $start_ip && $end_ip ) {
542 if ( $op =~ /^<=?$/ ) {
543 $value = $start_ip;
544 } elsif ($op =~ /^>=?$/ ) {
545 $value = $end_ip;
546 } else {
547 $value = join '-', $start_ip, $end_ip;
548 }
549 } else {
550 $RT::Logger->warn("$value is not a valid IPAddressRange");
551 }
552
553 # Recurse if they want a range comparison
554 if ( $op !~ /^[<>]=?$/ ) {
555 my ($start_ip, $end_ip) = split /-/, $value;
556 $self->_OpenParen( $args{SUBCLAUSE} );
557 # Ideally we would limit >= 000.000.000.000 and <=
558 # 255.255.255.255 so DB optimizers could use better
559 # estimations and scan less rows, but this breaks with IPv6.
560 if ( $op !~ /NOT|!=|<>/i ) { # positive equation
561 $self->_LimitCustomField(
562 %args,
563 OPERATOR => '<=',
564 VALUE => $end_ip,
565 LOOKUPTYPE => $ltype,
566 CUSTOMFIELD => $cf,
567 COLUMN => 'Content',
568 PREPARSE => 0,
569 );
570 $self->_LimitCustomField(
571 %args,
572 OPERATOR => '>=',
573 VALUE => $start_ip,
574 LOOKUPTYPE => $ltype,
575 CUSTOMFIELD => $cf,
576 COLUMN => 'LargeContent',
577 ENTRYAGGREGATOR => 'AND',
578 PREPARSE => 0,
579 );
580 } else { # negative equation
581 $self->_LimitCustomField(
582 %args,
583 OPERATOR => '>',
584 VALUE => $end_ip,
585 LOOKUPTYPE => $ltype,
586 CUSTOMFIELD => $cf,
587 COLUMN => 'Content',
588 PREPARSE => 0,
589 );
590 $self->_LimitCustomField(
591 %args,
592 OPERATOR => '<',
593 VALUE => $start_ip,
594 LOOKUPTYPE => $ltype,
595 CUSTOMFIELD => $cf,
596 COLUMN => 'LargeContent',
597 ENTRYAGGREGATOR => 'OR',
598 PREPARSE => 0,
599 );
600 }
601 $self->_CloseParen( $args{SUBCLAUSE} );
602 return;
603 }
604 } elsif ( $type =~ /^Date(?:Time)?$/ ) {
605 my $date = RT::Date->new( $self->CurrentUser );
606 $date->Set( Format => 'unknown', Value => $value );
607 if ( $date->Unix ) {
608 if (
609 $type eq 'Date'
610 # Heuristics to determine if a date, and not
611 # a datetime, was entered:
612 || $value =~ /^\s*(?:today|tomorrow|yesterday)\s*$/i
613 || ( $value !~ /midnight|\d+:\d+:\d+/i
614 && $date->Time( Timezone => 'user' ) eq '00:00:00' )
615 )
616 {
617 $value = $date->Date( Timezone => 'user' );
618 } else {
619 $value = $date->DateTime;
620 }
621 } else {
622 $RT::Logger->warn("$value is not a valid date string");
623 }
624
625 # Recurse if day equality is being checked on a datetime
626 if ( $type eq 'DateTime' and $op eq '=' && $value !~ /:/ ) {
627 my $date = RT::Date->new( $self->CurrentUser );
628 $date->Set( Format => 'unknown', Value => $value );
629 my $daystart = $date->ISO;
630 $date->AddDay;
631 my $dayend = $date->ISO;
632
633 $self->_OpenParen( $args{SUBCLAUSE} );
634 $self->_LimitCustomField(
635 %args,
636 OPERATOR => ">=",
637 VALUE => $daystart,
638 LOOKUPTYPE => $ltype,
639 CUSTOMFIELD => $cf,
640 COLUMN => 'Content',
641 ENTRYAGGREGATOR => 'AND',
642 PREPARSE => 0,
643 );
644
645 $self->_LimitCustomField(
646 %args,
647 OPERATOR => "<",
648 VALUE => $dayend,
649 LOOKUPTYPE => $ltype,
650 CUSTOMFIELD => $cf,
651 COLUMN => 'Content',
652 ENTRYAGGREGATOR => 'AND',
653 PREPARSE => 0,
654 );
655 $self->_CloseParen( $args{SUBCLAUSE} );
656 return;
657 }
658 }
659 }
660
661 ########## Limits
662 # IS NULL and IS NOT NULL checks
663 if ( $op =~ /^IS( NOT)?$/i ) {
664 my ($ocfvalias, $CFs) = $self->_CustomFieldJoin( $cfkey, $cf, $ltype );
665 $self->_OpenParen( $args{SUBCLAUSE} );
666 $self->Limit(
667 %args,
668 ALIAS => $ocfvalias,
669 FIELD => ($column || 'id'),
670 OPERATOR => $op,
671 VALUE => $value,
672 );
673 # See below for an explanation of this limit
674 $self->Limit(
675 ALIAS => $CFs,
676 FIELD => 'Name',
677 OPERATOR => 'IS NOT',
678 VALUE => 'NULL',
679 ENTRYAGGREGATOR => 'AND',
680 SUBCLAUSE => $args{SUBCLAUSE},
681 ) if $CFs;
682 $self->_CloseParen( $args{SUBCLAUSE} );
683 return;
684 }
685
686 my $single_value = !blessed($cf) || $cf->SingleValue;
687 my $negative_op = ($op eq '!=' || $op =~ /\bNOT\b/i);
688 my $value_is_long = (length( Encode::encode_utf8($value)) > 255) ? 1 : 0;
689
690 $cfkey .= '.'. $self->{'_sql_multiple_cfs_index'}++
691 if not $single_value and $op =~ /^(!?=|(NOT )?LIKE)$/i;
692 my ($ocfvalias, $CFs) = $self->_CustomFieldJoin( $cfkey, $cf, $ltype );
693
694 # A negative limit on a multi-value CF means _none_ of the values
695 # are the given value
696 if ( $negative_op and not $single_value ) {
697 # Reverse the limit we apply to the join, and check IS NULL
698 $op =~ s/!|NOT\s+//i;
699
700 # Ideally we would check both Content and LargeContent here, as
701 # the positive searches do below -- however, we cannot place
702 # complex limits inside LEFTJOINs due to searchbuilder
703 # limitations. Guessing which to check based on the value's
704 # string length is sufficient for !=, but sadly insufficient for
705 # NOT LIKE checks, giving false positives.
706 $column ||= $value_is_long ? 'LargeContent' : 'Content';
707 $self->Limit( $fix_op->(
708 LEFTJOIN => $ocfvalias,
709 ALIAS => $ocfvalias,
710 FIELD => $column,
711 OPERATOR => $op,
712 VALUE => $value,
713 CASESENSITIVE => 0,
714 ) );
715 $self->Limit(
716 %args,
717 ALIAS => $ocfvalias,
718 FIELD => 'id',
719 OPERATOR => 'IS',
720 VALUE => 'NULL',
721 );
722 return;
723 }
724
725 # If column is defined, then we just search it that, with no magic
726 if ( $column ) {
727 $self->_OpenParen( $args{SUBCLAUSE} );
728 $self->Limit( $fix_op->(
729 %args,
730 ALIAS => $ocfvalias,
731 FIELD => $column,
732 OPERATOR => $op,
733 VALUE => $value,
734 CASESENSITIVE => 0,
735 ) );
736 $self->Limit(
737 ALIAS => $ocfvalias,
738 FIELD => $column,
739 OPERATOR => 'IS',
740 VALUE => 'NULL',
741 ENTRYAGGREGATOR => 'OR',
742 SUBCLAUSE => $args{SUBCLAUSE},
743 ) if $negative_op;
744 $self->_CloseParen( $args{SUBCLAUSE} );
745 return;
746 }
747
748 $self->_OpenParen( $args{SUBCLAUSE} ); # For negative_op "OR it is null" clause
749 $self->_OpenParen( $args{SUBCLAUSE} ); # NAME IS NOT NULL clause
750
751 $self->_OpenParen( $args{SUBCLAUSE} ); # Check Content / LargeContent
752 if ($value_is_long and $op eq "=") {
753 # Doesn't matter what Content contains, as it cannot match the
754 # too-long value; we just look in LargeContent, below.
755 } elsif ($value_is_long and $op =~ /^(!=|<>)$/) {
756 # If Content is non-null, that's a valid way to _not_ contain the too-long value.
757 $self->Limit(
758 %args,
759 ALIAS => $ocfvalias,
760 FIELD => 'Content',
761 OPERATOR => 'IS NOT',
762 VALUE => 'NULL',
763 );
764 } else {
765 # Otherwise, go looking at the Content
766 $self->Limit(
767 %args,
768 ALIAS => $ocfvalias,
769 FIELD => 'Content',
770 OPERATOR => $op,
771 VALUE => $value,
772 CASESENSITIVE => 0,
773 );
774 }
775
776 if (!$value_is_long and $op eq "=") {
777 # Doesn't matter what LargeContent contains, as it cannot match
778 # the short value.
779 } elsif (!$value_is_long and $op =~ /^(!=|<>)$/) {
780 # If LargeContent is non-null, that's a valid way to _not_
781 # contain the too-short value.
782 $self->Limit(
783 %args,
784 ALIAS => $ocfvalias,
785 FIELD => 'LargeContent',
786 OPERATOR => 'IS NOT',
787 VALUE => 'NULL',
788 ENTRYAGGREGATOR => 'OR',
789 );
790 } else {
791 $self->_OpenParen( $args{SUBCLAUSE} ); # LargeContent check
792 $self->_OpenParen( $args{SUBCLAUSE} ); # Content is null?
793 $self->Limit(
794 ALIAS => $ocfvalias,
795 FIELD => 'Content',
796 OPERATOR => '=',
797 VALUE => '',
798 ENTRYAGGREGATOR => 'OR',
799 SUBCLAUSE => $args{SUBCLAUSE},
800 );
801 $self->Limit(
802 ALIAS => $ocfvalias,
803 FIELD => 'Content',
804 OPERATOR => 'IS',
805 VALUE => 'NULL',
806 ENTRYAGGREGATOR => 'OR',
807 SUBCLAUSE => $args{SUBCLAUSE},
808 );
809 $self->_CloseParen( $args{SUBCLAUSE} ); # Content is null?
810 $self->Limit( $fix_op->(
811 ALIAS => $ocfvalias,
812 FIELD => 'LargeContent',
813 OPERATOR => $op,
814 VALUE => $value,
815 ENTRYAGGREGATOR => 'AND',
816 SUBCLAUSE => $args{SUBCLAUSE},
817 CASESENSITIVE => 0,
818 ) );
819 $self->_CloseParen( $args{SUBCLAUSE} ); # LargeContent check
820 }
821
822 $self->_CloseParen( $args{SUBCLAUSE} ); # Check Content/LargeContent
823
824 # XXX: if we join via CustomFields table then
825 # because of order of left joins we get NULLs in
826 # CF table and then get nulls for those records
827 # in OCFVs table what result in wrong results
828 # as decifer method now tries to load a CF then
829 # we fall into this situation only when there
830 # are more than one CF with the name in the DB.
831 # the same thing applies to order by call.
832 # TODO: reorder joins T <- OCFVs <- CFs <- OCFs if
833 # we want treat IS NULL as (not applies or has
834 # no value)
835 $self->Limit(
836 ALIAS => $CFs,
837 FIELD => 'Name',
838 OPERATOR => 'IS NOT',
839 VALUE => 'NULL',
840 ENTRYAGGREGATOR => 'AND',
841 SUBCLAUSE => $args{SUBCLAUSE},
842 ) if $CFs;
843 $self->_CloseParen( $args{SUBCLAUSE} ); # Name IS NOT NULL clause
844
845 # If we were looking for != or NOT LIKE, we need to include the
846 # possibility that the row had no value.
847 $self->Limit(
848 ALIAS => $ocfvalias,
849 FIELD => 'id',
850 OPERATOR => 'IS',
851 VALUE => 'NULL',
852 ENTRYAGGREGATOR => 'OR',
853 SUBCLAUSE => $args{SUBCLAUSE},
854 ) if $negative_op;
855 $self->_CloseParen( $args{SUBCLAUSE} ); # negative_op clause
84fb5b46
MKG
856}
857
858=head2 Limit PARAMHASH
859
860This Limit sub calls SUPER::Limit, but defaults "CASESENSITIVE" to 1, thus
861making sure that by default lots of things don't do extra work trying to
862match lower(colname) agaist lc($val);
863
864We also force VALUE to C<NULL> when the OPERATOR is C<IS> or C<IS NOT>.
865This ensures that we don't pass invalid SQL to the database or allow SQL
866injection attacks when we pass through user specified values.
867
868=cut
869
af59614d
MKG
870my %check_case_sensitivity = (
871 groups => { 'name' => 1, domain => 1 },
872 queues => { 'name' => 1 },
873 users => { 'name' => 1, emailaddress => 1 },
874 customfields => { 'name' => 1 },
875);
876
877my %deprecated = (
878 groups => {
879 type => 'Name',
880 },
881 principals => { objectid => 'id' },
882);
883
84fb5b46
MKG
884sub Limit {
885 my $self = shift;
886 my %ARGS = (
84fb5b46
MKG
887 OPERATOR => '=',
888 @_,
889 );
890
891 # We use the same regex here that DBIx::SearchBuilder uses to exclude
892 # values from quoting
893 if ( $ARGS{'OPERATOR'} =~ /IS/i ) {
894 # Don't pass anything but NULL for IS and IS NOT
895 $ARGS{'VALUE'} = 'NULL';
896 }
897
af59614d 898 if (($ARGS{FIELD}||'') =~ /\W/
84fb5b46
MKG
899 or $ARGS{OPERATOR} !~ /^(=|<|>|!=|<>|<=|>=
900 |(NOT\s*)?LIKE
901 |(NOT\s*)?(STARTS|ENDS)WITH
902 |(NOT\s*)?MATCHES
903 |IS(\s*NOT)?
5b0d0914 904 |(NOT\s*)?IN
84fb5b46
MKG
905 |\@\@)$/ix) {
906 $RT::Logger->crit("Possible SQL injection attack: $ARGS{FIELD} $ARGS{OPERATOR}");
af59614d 907 %ARGS = (
84fb5b46
MKG
908 %ARGS,
909 FIELD => 'id',
910 OPERATOR => '<',
911 VALUE => '0',
912 );
84fb5b46 913 }
af59614d
MKG
914
915 my $table;
916 ($table) = $ARGS{'ALIAS'} && $ARGS{'ALIAS'} ne 'main'
917 ? ($ARGS{'ALIAS'} =~ /^(.*)_\d+$/)
918 : $self->Table
919 ;
920
921 if ( $table and $ARGS{FIELD} and my $instead = $deprecated{ lc $table }{ lc $ARGS{'FIELD'} } ) {
922 RT->Deprecated(
923 Message => "$table.$ARGS{'FIELD'} column is deprecated",
924 Instead => $instead, Remove => '4.4'
925 );
926 }
927
928 unless ( exists $ARGS{CASESENSITIVE} or (exists $ARGS{QUOTEVALUE} and not $ARGS{QUOTEVALUE}) ) {
929 if ( $ARGS{FIELD} and $ARGS{'OPERATOR'} !~ /IS/i
930 && $table && $check_case_sensitivity{ lc $table }{ lc $ARGS{'FIELD'} }
931 ) {
932 RT->Logger->warning(
933 "Case sensitive search by $table.$ARGS{'FIELD'}"
934 ." at ". (caller)[1] . " line ". (caller)[2]
935 );
936 }
937 $ARGS{'CASESENSITIVE'} = 1;
938 }
939
940 return $self->SUPER::Limit( %ARGS );
84fb5b46
MKG
941}
942
943=head2 ItemsOrderBy
944
945If it has a SortOrder attribute, sort the array by SortOrder.
946Otherwise, if it has a "Name" attribute, sort alphabetically by Name
947Otherwise, just give up and return it in the order it came from the
948db.
949
950=cut
951
952sub ItemsOrderBy {
953 my $self = shift;
954 my $items = shift;
955
af59614d 956 if ($self->RecordClass->_Accessible('SortOrder','read')) {
84fb5b46
MKG
957 $items = [ sort { $a->SortOrder <=> $b->SortOrder } @{$items} ];
958 }
af59614d 959 elsif ($self->RecordClass->_Accessible('Name','read')) {
84fb5b46
MKG
960 $items = [ sort { lc($a->Name) cmp lc($b->Name) } @{$items} ];
961 }
962
963 return $items;
964}
965
966=head2 ItemsArrayRef
967
968Return this object's ItemsArray, in the order that ItemsOrderBy sorts
969it.
970
971=cut
972
973sub ItemsArrayRef {
974 my $self = shift;
975 return $self->ItemsOrderBy($self->SUPER::ItemsArrayRef());
976}
977
978# make sure that Disabled rows never get seen unless
979# we're explicitly trying to see them.
980
981sub _DoSearch {
982 my $self = shift;
983
984 if ( $self->{'with_disabled_column'}
985 && !$self->{'handled_disabled_column'}
986 && !$self->{'find_disabled_rows'}
987 ) {
988 $self->LimitToEnabled;
989 }
990 return $self->SUPER::_DoSearch(@_);
991}
992sub _DoCount {
993 my $self = shift;
994
995 if ( $self->{'with_disabled_column'}
996 && !$self->{'handled_disabled_column'}
997 && !$self->{'find_disabled_rows'}
998 ) {
999 $self->LimitToEnabled;
1000 }
1001 return $self->SUPER::_DoCount(@_);
1002}
1003
1004=head2 ColumnMapClassName
1005
1006ColumnMap needs a Collection name to load the correct list display.
1007Depluralization is hard, so provide an easy way to correct the naive
1008algorithm that this code uses.
1009
1010=cut
1011
1012sub ColumnMapClassName {
af59614d
MKG
1013 my $self = shift;
1014 my $Class = $self->_SingularClass;
1015 $Class =~ s/:/_/g;
84fb5b46
MKG
1016 return $Class;
1017}
1018
af59614d
MKG
1019=head2 NewItem
1020
1021Returns a new item based on L</RecordClass> using the current user.
1022
1023=cut
1024
1025sub NewItem {
1026 my $self = shift;
1027 return $self->RecordClass->new($self->CurrentUser);
1028}
1029
1030=head2 NotSetDateToNullFunction
1031
1032Takes a paramhash with an optional FIELD key whose value is the name of a date
1033column. If no FIELD is provided, a literal C<?> placeholder is used so the
1034caller can fill in the field later.
1035
1036Returns a SQL function which evaluates to C<NULL> if the FIELD is set to the
1037Unix epoch; otherwise it evaluates to FIELD. This is useful because RT
1038currently stores unset dates as a Unix epoch timestamp instead of NULL, but
1039NULLs are often more desireable.
1040
1041=cut
1042
1043sub NotSetDateToNullFunction {
1044 my $self = shift;
1045 my %args = ( FIELD => undef, @_ );
1046
1047 my $res = "CASE WHEN ? BETWEEN '1969-12-31 11:59:59' AND '1970-01-01 12:00:01' THEN NULL ELSE ? END";
1048 if ( $args{FIELD} ) {
1049 $res = $self->CombineFunctionWithField( %args, FUNCTION => $res );
1050 }
1051 return $res;
1052}
1053
84fb5b46
MKG
1054RT::Base->_ImportOverlays();
1055
10561;