summaryrefslogtreecommitdiff
path: root/rt/lib/RT/Tickets_SQL.pm
blob: ec1bb4997ba1774c19bd408b6cb7a27063b5617a (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
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
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
# BEGIN BPS TAGGED BLOCK {{{
#
# COPYRIGHT:
#
# This software is Copyright (c) 1996-2012 Best Practical Solutions, LLC
#                                          <sales@bestpractical.com>
#
# (Except where explicitly superseded by other copyright notices)
#
#
# LICENSE:
#
# 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
# from www.gnu.org.
#
# 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.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301 or visit their web page on the internet at
# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
#
#
# CONTRIBUTION SUBMISSION POLICY:
#
# (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.)
#
# 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.
#
# END BPS TAGGED BLOCK }}}

package RT::Tickets;

use strict;
use warnings;


use RT::SQL;

# Import configuration data from the lexcial scope of __PACKAGE__ (or
# at least where those two Subroutines are defined.)

our (%FIELD_METADATA, %dispatch, %can_bundle);

# Lower Case version of FIELDS, for case insensitivity
my %lcfields = map { ( lc($_) => $_ ) } (keys %FIELD_METADATA);

sub _InitSQL {
  my $self = shift;

  # Private Member Variables (which should get cleaned)
  $self->{'_sql_transalias'}    = undef;
  $self->{'_sql_trattachalias'} = undef;
  $self->{'_sql_cf_alias'}  = undef;
  $self->{'_sql_object_cfv_alias'}  = undef;
  $self->{'_sql_watcher_join_users_alias'} = undef;
  $self->{'_sql_query'}         = '';
  $self->{'_sql_looking_at'}    = {};
}

sub _SQLLimit {
  my $self = shift;
    my %args = (@_);
    if ($args{'FIELD'} eq 'EffectiveId' &&
         (!$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) ) {
        $self->{'looking_at_effective_id'} = 1;
    }      
    
    if ($args{'FIELD'} eq 'Type' &&
         (!$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) ) {
        $self->{'looking_at_type'} = 1;
    }

  # All SQL stuff goes into one SB subclause so we can deal with all
  # the aggregation
  $self->SUPER::Limit(%args,
                      SUBCLAUSE => 'ticketsql');
}

sub _SQLJoin {
  # All SQL stuff goes into one SB subclause so we can deal with all
  # the aggregation
  my $this = shift;

  $this->SUPER::Join(@_,
		     SUBCLAUSE => 'ticketsql');
}

# Helpers
sub _OpenParen {
  $_[0]->SUPER::_OpenParen( 'ticketsql' );
}
sub _CloseParen {
  $_[0]->SUPER::_CloseParen( 'ticketsql' );
}

=head1 SQL Functions

=cut

=head2 Robert's Simple SQL Parser

Documentation In Progress

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:

  VALUE -> quoted string or number
  AGGREGator -> AND or OR
  KEYWORD -> quoted string or single word
  OPerator -> =,!=,LIKE,etc..
  PARENthesis -> open or close.

And that stream of tokens is passed through the "machine" in order to build up a structure that looks like:

       KEY OP VALUE
  AND  KEY OP VALUE
  OR   KEY OP VALUE

That also deals with parenthesis for nesting.  (The parentheses are
just handed off the SearchBuilder)

=cut

sub _close_bundle {
    my ($self, @bundle) = @_;
    return unless @bundle;

    if ( @bundle == 1 ) {
        $bundle[0]->{'dispatch'}->(
            $self,
            $bundle[0]->{'key'},
            $bundle[0]->{'op'},
            $bundle[0]->{'val'},
            SUBCLAUSE       => '',
            ENTRYAGGREGATOR => $bundle[0]->{ea},
            SUBKEY          => $bundle[0]->{subkey},
        );
    }
    else {
        my @args;
        foreach my $chunk (@bundle) {
            push @args, [
                $chunk->{key},
                $chunk->{op},
                $chunk->{val},
                SUBCLAUSE       => '',
                ENTRYAGGREGATOR => $chunk->{ea},
                SUBKEY          => $chunk->{subkey},
            ];
        }
        $bundle[0]->{dispatch}->( $self, \@args );
    }
}

sub _parser {
    my ($self,$string) = @_;
    my @bundle;
    my $ea = '';

    my %callback;
    $callback{'OpenParen'} = sub {
      $self->_close_bundle(@bundle); @bundle = ();
      $self->_OpenParen
    };
    $callback{'CloseParen'} = sub {
      $self->_close_bundle(@bundle); @bundle = ();
      $self->_CloseParen;
    };
    $callback{'EntryAggregator'} = sub { $ea = $_[0] || '' };
    $callback{'Condition'} = sub {
        my ($key, $op, $value) = @_;

        # key has dot then it's compound variant and we have subkey
        my $subkey = '';
        ($key, $subkey) = ($1, $2) if $key =~ /^([^\.]+)\.(.+)$/;

        # normalize key and get class (type)
        my $class;
        if (exists $lcfields{lc $key}) {
            $key = $lcfields{lc $key};
            $class = $FIELD_METADATA{$key}->[0];
        }
        die "Unknown field '$key' in '$string'" unless $class;

        # replace __CurrentUser__ with id
        $value = $self->CurrentUser->id if $value eq '__CurrentUser__';


        unless( $dispatch{ $class } ) {
            die "No dispatch method for class '$class'"
        }
        my $sub = $dispatch{ $class };

        if ( $can_bundle{ $class }
             && ( !@bundle
                  || ( $bundle[-1]->{dispatch}  == $sub
                       && $bundle[-1]->{key}    eq $key
                       && $bundle[-1]->{subkey} eq $subkey
                     )
                )
           )
        {
            push @bundle, {
                dispatch => $sub,
                key      => $key,
                op       => $op,
                val      => $value,
                ea       => $ea,
                subkey   => $subkey,
            };
        }
        else {
            $self->_close_bundle(@bundle); @bundle = ();
            $sub->( $self, $key, $op, $value,
                    SUBCLAUSE       => '',  # don't need anymore
                    ENTRYAGGREGATOR => $ea,
                    SUBKEY          => $subkey,
                  );
        }
        $self->{_sql_looking_at}{lc $key} = 1;
        $ea = '';
    };
    RT::SQL::Parse($string, \%callback);
    $self->_close_bundle(@bundle); @bundle = ();
}

=head2 ClausesToSQL

=cut

sub ClausesToSQL {
  my $self = shift;
  my $clauses = shift;
  my @sql;

  for my $f (keys %{$clauses}) {
    my $sql;
    my $first = 1;

    # Build SQL from the data hash
    for my $data ( @{ $clauses->{$f} } ) {
      $sql .= $data->[0] unless $first; $first=0; # ENTRYAGGREGATOR
      $sql .= " '". $data->[2] . "' ";            # FIELD
      $sql .= $data->[3] . " ";                   # OPERATOR
      $sql .= "'". $data->[4] . "' ";             # VALUE
    }

    push @sql, " ( " . $sql . " ) ";
  }

  return join("AND",@sql);
}

=head2 FromSQL

Convert a RT-SQL string into a set of SearchBuilder restrictions.

Returns (1, 'Status message') on success and (0, 'Error Message') on
failure.




=cut

sub FromSQL {
    my ($self,$query) = @_;

    {
        # preserve first_row and show_rows across the CleanSlate
        local ($self->{'first_row'}, $self->{'show_rows'});
        $self->CleanSlate;
    }
    $self->_InitSQL();

    return (1, $self->loc("No Query")) unless $query;

    $self->{_sql_query} = $query;
    eval { $self->_parser( $query ); };
    if ( $@ ) {
        $RT::Logger->error( $@ );
        return (0, $@);
    }

    # We only want to look at EffectiveId's (mostly) for these searches.
    unless ( exists $self->{_sql_looking_at}{'effectiveid'} ) {
        #TODO, we shouldn't be hard #coding the tablename to main.
        $self->SUPER::Limit( FIELD           => 'EffectiveId',
                             VALUE           => 'main.id',
                             ENTRYAGGREGATOR => 'AND',
                             QUOTEVALUE      => 0,
                           );
    }
    # FIXME: Need to bring this logic back in

    #      if ($self->_isLimited && (! $self->{'looking_at_effective_id'})) {
    #         $self->SUPER::Limit( FIELD => 'EffectiveId',
    #               OPERATOR => '=',
    #               QUOTEVALUE => 0,
    #               VALUE => 'main.id');   #TODO, we shouldn't be hard coding the tablename to main.
    #       }
    # --- This is hardcoded above.  This comment block can probably go.
    # Or, we need to reimplement the looking_at_effective_id toggle.

    # Unless we've explicitly asked to look at a specific Type, we need
    # to limit to it.
    unless ( $self->{looking_at_type} ) {
        $self->SUPER::Limit( FIELD => 'Type', VALUE => 'ticket' );
    }

    # We don't want deleted tickets unless 'allow_deleted_search' is set
    unless( $self->{'allow_deleted_search'} ) {
        $self->SUPER::Limit( FIELD    => 'Status',
                             OPERATOR => '!=',
                             VALUE => 'deleted',
                           );
    }

    # set SB's dirty flag
    $self->{'must_redo_search'} = 1;
    $self->{'RecalcTicketLimits'} = 0;                                           

    return (1, $self->loc("Valid Query"));
}

=head2 Query

Returns the query that this object was initialized with

=cut

sub Query {
    return ($_[0]->{_sql_query});
}

{
my %inv = (
    '=' => '!=', '!=' => '=', '<>' => '=',
    '>' => '<=', '<' => '>=', '>=' => '<', '<=' => '>',
    'is' => 'IS NOT', 'is not' => 'IS',
    'like' => 'NOT LIKE', 'not like' => 'LIKE',
    'matches' => 'NOT MATCHES', 'not matches' => 'MATCHES',
    'startswith' => 'NOT STARTSWITH', 'not startswith' => 'STARTSWITH',
    'endswith' => 'NOT ENDSWITH', 'not endswith' => 'ENDSWITH',
);

my %range = map { $_ => 1 } qw(> >= < <=);

sub ClassifySQLOperation {
    my $self = shift;
    my $op = shift;

    my $is_negative = 0;
    if ( $op eq '!=' || $op =~ /\bNOT\b/i ) {
        $is_negative = 1;
    }

    my $is_null = 0;
    if ( 'is not' eq lc($op) || 'is' eq lc($op) ) {
        $is_null = 1;
    }

    return ($is_negative, $is_null, $inv{lc $op}, $range{lc $op});
} }

1;

=pod

=head2 Exceptions

Most of the RT code does not use Exceptions (die/eval) but it is used
in the TicketSQL code for simplicity and historical reasons.  Lest you
be worried that the dies will trigger user visible errors, all are
trapped via evals.

99% of the dies fall in subroutines called via FromSQL and then parse.
(This includes all of the _FooLimit routines in Tickets_Overlay.pm.)
The other 1% or so are via _ProcessRestrictions.

All dies are trapped by eval {}s, and will be logged at the 'error'
log level.  The general failure mode is to not display any tickets.

=head2 General Flow

Legacy Layer:

   Legacy LimitFoo routines build up a RestrictionsHash

   _ProcessRestrictions converts the Restrictions to Clauses
   ([key,op,val,rest]).

   Clauses are converted to RT-SQL (TicketSQL)

New RT-SQL Layer:

   FromSQL calls the parser

   The parser calls the _FooLimit routines to do DBIx::SearchBuilder
   limits.

And then the normal SearchBuilder/Ticket routines are used for
display/navigation.

=cut