]>
Commit | Line | Data |
---|---|---|
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 | package RT::Tickets; | |
50 | ||
51 | use strict; | |
52 | use warnings; | |
53 | ||
54 | ||
55 | use 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 | 60 | our (%FIELD_METADATA, %LOWER_CASE_FIELDS, %dispatch, %can_bundle); |
84fb5b46 MKG |
61 | |
62 | sub _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 | ||
75 | sub _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 | ||
94 | sub _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 | |
104 | sub _OpenParen { | |
105 | $_[0]->SUPER::_OpenParen( 'ticketsql' ); | |
106 | } | |
107 | sub _CloseParen { | |
108 | $_[0]->SUPER::_CloseParen( 'ticketsql' ); | |
109 | } | |
110 | ||
111 | =head1 SQL Functions | |
112 | ||
113 | =cut | |
114 | ||
115 | =head2 Robert's Simple SQL Parser | |
116 | ||
117 | Documentation In Progress | |
118 | ||
119 | The 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 | ||
127 | And 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 | ||
133 | That also deals with parenthesis for nesting. (The parentheses are | |
134 | just handed off the SearchBuilder) | |
135 | ||
136 | =cut | |
137 | ||
138 | sub _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 | ||
169 | sub _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 |
309 | sub _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 | ||
336 | sub 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 | ||
361 | Convert a RT-SQL string into a set of SearchBuilder restrictions. | |
362 | ||
363 | Returns (1, 'Status message') on success and (0, 'Error Message') on | |
364 | failure. | |
365 | ||
366 | ||
367 | ||
368 | ||
369 | =cut | |
370 | ||
371 | sub 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 | ||
434 | Returns the query that this object was initialized with | |
435 | ||
436 | =cut | |
437 | ||
438 | sub Query { | |
439 | return ($_[0]->{_sql_query}); | |
440 | } | |
441 | ||
442 | { | |
443 | my %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 | ||
453 | my %range = map { $_ => 1 } qw(> >= < <=); | |
454 | ||
455 | sub 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 | ||
472 | 1; | |
473 | ||
474 | =pod | |
475 | ||
476 | =head2 Exceptions | |
477 | ||
478 | Most of the RT code does not use Exceptions (die/eval) but it is used | |
479 | in the TicketSQL code for simplicity and historical reasons. Lest you | |
480 | be worried that the dies will trigger user visible errors, all are | |
481 | trapped via evals. | |
482 | ||
483 | 99% of the dies fall in subroutines called via FromSQL and then parse. | |
484 | (This includes all of the _FooLimit routines in Tickets_Overlay.pm.) | |
485 | The other 1% or so are via _ProcessRestrictions. | |
486 | ||
487 | All dies are trapped by eval {}s, and will be logged at the 'error' | |
488 | log level. The general failure mode is to not display any tickets. | |
489 | ||
490 | =head2 General Flow | |
491 | ||
492 | Legacy 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 | ||
501 | New RT-SQL Layer: | |
502 | ||
503 | FromSQL calls the parser | |
504 | ||
505 | The parser calls the _FooLimit routines to do DBIx::SearchBuilder | |
506 | limits. | |
507 | ||
508 | And then the normal SearchBuilder/Ticket routines are used for | |
509 | display/navigation. | |
510 | ||
511 | =cut | |
512 |