Upgrade 4.0.17 clean.
[usit-rt.git] / lib / RT / Tickets_SQL.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
49package RT::Tickets;
50
51use strict;
52use warnings;
53
54
55use RT::SQL;
56
57# Import configuration data from the lexcial scope of __PACKAGE__ (or
58# at least where those two Subroutines are defined.)
59
403d7b0b 60our (%FIELD_METADATA, %LOWER_CASE_FIELDS, %dispatch, %can_bundle);
84fb5b46
MKG
61
62sub _InitSQL {
63 my $self = shift;
64
65 # Private Member Variables (which should get cleaned)
66 $self->{'_sql_transalias'} = undef;
67 $self->{'_sql_trattachalias'} = undef;
68 $self->{'_sql_cf_alias'} = undef;
69 $self->{'_sql_object_cfv_alias'} = undef;
70 $self->{'_sql_watcher_join_users_alias'} = undef;
71 $self->{'_sql_query'} = '';
72 $self->{'_sql_looking_at'} = {};
73}
74
75sub _SQLLimit {
76 my $self = shift;
77 my %args = (@_);
78 if ($args{'FIELD'} eq 'EffectiveId' &&
79 (!$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) ) {
80 $self->{'looking_at_effective_id'} = 1;
81 }
82
83 if ($args{'FIELD'} eq 'Type' &&
84 (!$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) ) {
85 $self->{'looking_at_type'} = 1;
86 }
87
88 # All SQL stuff goes into one SB subclause so we can deal with all
89 # the aggregation
90 $self->SUPER::Limit(%args,
91 SUBCLAUSE => 'ticketsql');
92}
93
94sub _SQLJoin {
95 # All SQL stuff goes into one SB subclause so we can deal with all
96 # the aggregation
97 my $this = shift;
98
99 $this->SUPER::Join(@_,
100 SUBCLAUSE => 'ticketsql');
101}
102
103# Helpers
104sub _OpenParen {
105 $_[0]->SUPER::_OpenParen( 'ticketsql' );
106}
107sub _CloseParen {
108 $_[0]->SUPER::_CloseParen( 'ticketsql' );
109}
110
111=head1 SQL Functions
112
113=cut
114
115=head2 Robert's Simple SQL Parser
116
117Documentation In Progress
118
119The Parser/Tokenizer is a relatively simple state machine that scans through a SQL WHERE clause type string extracting a token at a time (where a token is:
120
121 VALUE -> quoted string or number
122 AGGREGator -> AND or OR
123 KEYWORD -> quoted string or single word
124 OPerator -> =,!=,LIKE,etc..
125 PARENthesis -> open or close.
126
127And that stream of tokens is passed through the "machine" in order to build up a structure that looks like:
128
129 KEY OP VALUE
130 AND KEY OP VALUE
131 OR KEY OP VALUE
132
133That also deals with parenthesis for nesting. (The parentheses are
134just handed off the SearchBuilder)
135
136=cut
137
138sub _close_bundle {
139 my ($self, @bundle) = @_;
140 return unless @bundle;
141
142 if ( @bundle == 1 ) {
143 $bundle[0]->{'dispatch'}->(
144 $self,
145 $bundle[0]->{'key'},
146 $bundle[0]->{'op'},
147 $bundle[0]->{'val'},
148 SUBCLAUSE => '',
149 ENTRYAGGREGATOR => $bundle[0]->{ea},
150 SUBKEY => $bundle[0]->{subkey},
151 );
152 }
153 else {
154 my @args;
155 foreach my $chunk (@bundle) {
156 push @args, [
157 $chunk->{key},
158 $chunk->{op},
159 $chunk->{val},
160 SUBCLAUSE => '',
161 ENTRYAGGREGATOR => $chunk->{ea},
162 SUBKEY => $chunk->{subkey},
163 ];
164 }
165 $bundle[0]->{dispatch}->( $self, \@args );
166 }
167}
168
169sub _parser {
170 my ($self,$string) = @_;
171 my @bundle;
172 my $ea = '';
173
01e3b242
MKG
174 # Bundling of joins is implemented by dynamically tracking a parallel query
175 # tree in %sub_tree as the TicketSQL is parsed. Don't be fooled by
176 # _close_bundle(), @bundle, and %can_bundle; they are completely unused for
177 # quite a long time and removed in RT 4.2. For now they stay, a useless
178 # relic.
179 #
180 # Only positive, OR'd watcher conditions are bundled currently. Each key
181 # in %sub_tree is a watcher type (Requestor, Cc, AdminCc) or the generic
182 # "Watcher" for any watcher type. Owner is not bundled because it is
183 # denormalized into a Tickets column and doesn't need a join. AND'd
184 # conditions are not bundled since a record may have multiple watchers
185 # which independently match the conditions, thus necessitating two joins.
186 #
187 # The values of %sub_tree are arrayrefs made up of:
188 #
189 # * Open parentheses "(" pushed on by the OpenParen callback
190 # * Arrayrefs of bundled join aliases pushed on by the Condition callback
191 # * Entry aggregators (AND/OR) pushed on by the EntryAggregator callback
192 #
193 # The CloseParen callback takes care of backing off the query trees until
194 # outside of the just-closed parenthetical, thus restoring the tree state
195 # an equivalent of before the parenthetical was entered.
196 #
197 # The Condition callback handles starting a new subtree or extending an
198 # existing one, determining if bundling the current condition with any
199 # subtree is possible, and pruning any dangling entry aggregators from
200 # trees.
201 #
202
203 my %sub_tree;
204 my $depth = 0;
205
84fb5b46
MKG
206 my %callback;
207 $callback{'OpenParen'} = sub {
208 $self->_close_bundle(@bundle); @bundle = ();
01e3b242
MKG
209 $self->_OpenParen;
210 $depth++;
211 push @$_, '(' foreach values %sub_tree;
84fb5b46
MKG
212 };
213 $callback{'CloseParen'} = sub {
214 $self->_close_bundle(@bundle); @bundle = ();
215 $self->_CloseParen;
01e3b242
MKG
216 $depth--;
217 foreach my $list ( values %sub_tree ) {
218 if ( $list->[-1] eq '(' ) {
219 pop @$list;
220 pop @$list if $list->[-1] =~ /^(?:AND|OR)$/i;
221 }
222 else {
223 pop @$list while $list->[-2] ne '(';
224 $list->[-1] = pop @$list;
225 }
226 }
227 };
228 $callback{'EntryAggregator'} = sub {
229 $ea = $_[0] || '';
230 push @$_, $ea foreach grep @$_ && $_->[-1] ne '(', values %sub_tree;
84fb5b46 231 };
84fb5b46
MKG
232 $callback{'Condition'} = sub {
233 my ($key, $op, $value) = @_;
234
01e3b242
MKG
235 my ($negative_op, $null_op, $inv_op, $range_op)
236 = $self->ClassifySQLOperation( $op );
84fb5b46
MKG
237 # key has dot then it's compound variant and we have subkey
238 my $subkey = '';
239 ($key, $subkey) = ($1, $2) if $key =~ /^([^\.]+)\.(.+)$/;
240
241 # normalize key and get class (type)
242 my $class;
403d7b0b
MKG
243 if (exists $LOWER_CASE_FIELDS{lc $key}) {
244 $key = $LOWER_CASE_FIELDS{lc $key};
84fb5b46
MKG
245 $class = $FIELD_METADATA{$key}->[0];
246 }
247 die "Unknown field '$key' in '$string'" unless $class;
248
249 # replace __CurrentUser__ with id
250 $value = $self->CurrentUser->id if $value eq '__CurrentUser__';
251
252
253 unless( $dispatch{ $class } ) {
254 die "No dispatch method for class '$class'"
255 }
256 my $sub = $dispatch{ $class };
257
258 if ( $can_bundle{ $class }
259 && ( !@bundle
260 || ( $bundle[-1]->{dispatch} == $sub
261 && $bundle[-1]->{key} eq $key
262 && $bundle[-1]->{subkey} eq $subkey
263 )
264 )
265 )
266 {
267 push @bundle, {
268 dispatch => $sub,
269 key => $key,
270 op => $op,
271 val => $value,
272 ea => $ea,
273 subkey => $subkey,
274 };
275 }
276 else {
277 $self->_close_bundle(@bundle); @bundle = ();
01e3b242
MKG
278 my @res; my $bundle_with;
279 if ( $class eq 'WATCHERFIELD' && $key ne 'Owner' && !$negative_op && (!$null_op || $subkey) ) {
280 if ( !$sub_tree{$key} ) {
281 $sub_tree{$key} = [ ('(')x$depth, \@res ];
282 } else {
283 $bundle_with = $self->_check_bundling_possibility( $string, @{ $sub_tree{$key} } );
284 if ( $sub_tree{$key}[-1] eq '(' ) {
285 push @{ $sub_tree{$key} }, \@res;
286 }
287 }
288 }
289
290 # Remove our aggregator from subtrees where our condition didn't get added
291 pop @$_ foreach grep @$_ && $_->[-1] =~ /^(?:AND|OR)$/i, values %sub_tree;
292
293 # A reference to @res may be pushed onto $sub_tree{$key} from
294 # above, and we fill it here.
295 @res = $sub->( $self, $key, $op, $value,
84fb5b46
MKG
296 SUBCLAUSE => '', # don't need anymore
297 ENTRYAGGREGATOR => $ea,
298 SUBKEY => $subkey,
01e3b242 299 BUNDLE => $bundle_with,
84fb5b46
MKG
300 );
301 }
302 $self->{_sql_looking_at}{lc $key} = 1;
303 $ea = '';
304 };
305 RT::SQL::Parse($string, \%callback);
306 $self->_close_bundle(@bundle); @bundle = ();
307}
308
01e3b242
MKG
309sub _check_bundling_possibility {
310 my $self = shift;
311 my $string = shift;
312 my @list = reverse @_;
313 while (my $e = shift @list) {
314 next if $e eq '(';
315 if ( lc($e) eq 'and' ) {
316 return undef;
317 }
318 elsif ( lc($e) eq 'or' ) {
319 return shift @list;
320 }
321 else {
322 # should not happen
323 $RT::Logger->error(
324 "Joins optimization failed when parsing '$string'. It's bug in RT, contact Best Practical"
325 );
326 die "Internal error. Contact your system administrator.";
327 }
328 }
329 return undef;
330}
331
84fb5b46
MKG
332=head2 ClausesToSQL
333
334=cut
335
336sub ClausesToSQL {
337 my $self = shift;
338 my $clauses = shift;
339 my @sql;
340
341 for my $f (keys %{$clauses}) {
342 my $sql;
343 my $first = 1;
344
345 # Build SQL from the data hash
346 for my $data ( @{ $clauses->{$f} } ) {
347 $sql .= $data->[0] unless $first; $first=0; # ENTRYAGGREGATOR
348 $sql .= " '". $data->[2] . "' "; # FIELD
349 $sql .= $data->[3] . " "; # OPERATOR
350 $sql .= "'". $data->[4] . "' "; # VALUE
351 }
352
353 push @sql, " ( " . $sql . " ) ";
354 }
355
356 return join("AND",@sql);
357}
358
359=head2 FromSQL
360
361Convert a RT-SQL string into a set of SearchBuilder restrictions.
362
363Returns (1, 'Status message') on success and (0, 'Error Message') on
364failure.
365
366
367
368
369=cut
370
371sub FromSQL {
372 my ($self,$query) = @_;
373
374 {
375 # preserve first_row and show_rows across the CleanSlate
376 local ($self->{'first_row'}, $self->{'show_rows'});
377 $self->CleanSlate;
378 }
379 $self->_InitSQL();
380
381 return (1, $self->loc("No Query")) unless $query;
382
383 $self->{_sql_query} = $query;
384 eval { $self->_parser( $query ); };
385 if ( $@ ) {
01e3b242
MKG
386 my $error = "$@";
387 $RT::Logger->error("Couldn't parse query: $error");
388 return (0, $error);
84fb5b46
MKG
389 }
390
391 # We only want to look at EffectiveId's (mostly) for these searches.
392 unless ( exists $self->{_sql_looking_at}{'effectiveid'} ) {
393 #TODO, we shouldn't be hard #coding the tablename to main.
394 $self->SUPER::Limit( FIELD => 'EffectiveId',
395 VALUE => 'main.id',
396 ENTRYAGGREGATOR => 'AND',
397 QUOTEVALUE => 0,
398 );
399 }
400 # FIXME: Need to bring this logic back in
401
402 # if ($self->_isLimited && (! $self->{'looking_at_effective_id'})) {
403 # $self->SUPER::Limit( FIELD => 'EffectiveId',
404 # OPERATOR => '=',
405 # QUOTEVALUE => 0,
406 # VALUE => 'main.id'); #TODO, we shouldn't be hard coding the tablename to main.
407 # }
408 # --- This is hardcoded above. This comment block can probably go.
409 # Or, we need to reimplement the looking_at_effective_id toggle.
410
411 # Unless we've explicitly asked to look at a specific Type, we need
412 # to limit to it.
413 unless ( $self->{looking_at_type} ) {
414 $self->SUPER::Limit( FIELD => 'Type', VALUE => 'ticket' );
415 }
416
417 # We don't want deleted tickets unless 'allow_deleted_search' is set
418 unless( $self->{'allow_deleted_search'} ) {
419 $self->SUPER::Limit( FIELD => 'Status',
420 OPERATOR => '!=',
421 VALUE => 'deleted',
422 );
423 }
424
425 # set SB's dirty flag
426 $self->{'must_redo_search'} = 1;
427 $self->{'RecalcTicketLimits'} = 0;
428
429 return (1, $self->loc("Valid Query"));
430}
431
432=head2 Query
433
434Returns the query that this object was initialized with
435
436=cut
437
438sub Query {
439 return ($_[0]->{_sql_query});
440}
441
442{
443my %inv = (
444 '=' => '!=', '!=' => '=', '<>' => '=',
445 '>' => '<=', '<' => '>=', '>=' => '<', '<=' => '>',
446 'is' => 'IS NOT', 'is not' => 'IS',
447 'like' => 'NOT LIKE', 'not like' => 'LIKE',
448 'matches' => 'NOT MATCHES', 'not matches' => 'MATCHES',
449 'startswith' => 'NOT STARTSWITH', 'not startswith' => 'STARTSWITH',
450 'endswith' => 'NOT ENDSWITH', 'not endswith' => 'ENDSWITH',
451);
452
453my %range = map { $_ => 1 } qw(> >= < <=);
454
455sub ClassifySQLOperation {
456 my $self = shift;
457 my $op = shift;
458
459 my $is_negative = 0;
460 if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
461 $is_negative = 1;
462 }
463
464 my $is_null = 0;
465 if ( 'is not' eq lc($op) || 'is' eq lc($op) ) {
466 $is_null = 1;
467 }
468
469 return ($is_negative, $is_null, $inv{lc $op}, $range{lc $op});
470} }
471
4721;
473
474=pod
475
476=head2 Exceptions
477
478Most of the RT code does not use Exceptions (die/eval) but it is used
479in the TicketSQL code for simplicity and historical reasons. Lest you
480be worried that the dies will trigger user visible errors, all are
481trapped via evals.
482
48399% of the dies fall in subroutines called via FromSQL and then parse.
484(This includes all of the _FooLimit routines in Tickets_Overlay.pm.)
485The other 1% or so are via _ProcessRestrictions.
486
487All dies are trapped by eval {}s, and will be logged at the 'error'
488log level. The general failure mode is to not display any tickets.
489
490=head2 General Flow
491
492Legacy Layer:
493
494 Legacy LimitFoo routines build up a RestrictionsHash
495
496 _ProcessRestrictions converts the Restrictions to Clauses
497 ([key,op,val,rest]).
498
499 Clauses are converted to RT-SQL (TicketSQL)
500
501New RT-SQL Layer:
502
503 FromSQL calls the parser
504
505 The parser calls the _FooLimit routines to do DBIx::SearchBuilder
506 limits.
507
508And then the normal SearchBuilder/Ticket routines are used for
509display/navigation.
510
511=cut
512