1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2005 Best Practical Solutions, LLC
6 # <jesse@bestpractical.com>
8 # (Except where explicitly superseded by other copyright notices)
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
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.
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., 675 Mass Ave, Cambridge, MA 02139, USA.
28 # CONTRIBUTION SUBMISSION POLICY:
30 # (The following paragraph is not intended to limit the rights granted
31 # to you to modify and distribute this software under the terms of
32 # the GNU General Public License and is only of importance to you if
33 # you choose to contribute your changes and enhancements to the
34 # community by submitting them to Best Practical Solutions, LLC.)
36 # By intentionally submitting any modifications, corrections or
37 # derivatives to this work, or any other work intended for use with
38 # Request Tracker, to Best Practical Solutions, LLC, you confirm that
39 # you are the copyright holder for those contributions and you grant
40 # Best Practical Solutions, LLC a nonexclusive, worldwide, irrevocable,
41 # royalty-free, perpetual, license to use, copy, create derivative
42 # works based on those contributions, and sublicense and distribute
43 # those contributions and any derivatives thereof.
45 # END BPS TAGGED BLOCK }}}
51 # Import configuration data from the lexcial scope of __PACKAGE__ (or
52 # at least where those two Subroutines are defined.)
54 my %FIELDS = %{FIELDS()};
55 my %dispatch = %{dispatch()};
56 my %can_bundle = %{can_bundle()};
58 # Lower Case version of FIELDS, for case insensitivity
59 my %lcfields = map { ( lc($_) => $_ ) } (keys %FIELDS);
64 # How many of these do we actually still use?
66 # Private Member Variales (which should get cleaned)
67 $self->{'_sql_linksc'} = 0;
68 $self->{'_sql_watchersc'} = 0;
69 $self->{'_sql_keywordsc'} = 0;
70 $self->{'_sql_subclause'} = "a";
71 $self->{'_sql_first'} = 0;
72 $self->{'_sql_opstack'} = [''];
73 $self->{'_sql_linkalias'} = undef;
74 $self->{'_sql_transalias'} = undef;
75 $self->{'_sql_trattachalias'} = undef;
76 $self->{'_sql_object_cf_alias'} = undef;
77 $self->{'_sql_depth'} = 0;
78 $self->{'_sql_localdepth'} = 0;
79 $self->{'_sql_query'} = '';
80 $self->{'_sql_looking_at'} = {};
81 $self->{'_sql_columns_to_display'} = [];
88 if ($args{'FIELD'} eq 'EffectiveId' &&
89 (!$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) ) {
90 $self->{'looking_at_effective_id'} = 1;
93 if ($args{'FIELD'} eq 'Type' &&
94 (!$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) ) {
95 $self->{'looking_at_type'} = 1;
98 # All SQL stuff goes into one SB subclause so we can deal with all
100 $self->SUPER::Limit(%args,
101 SUBCLAUSE => 'ticketsql');
105 # All SQL stuff goes into one SB subclause so we can deal with all
109 $this->SUPER::Join(@_,
110 SUBCLAUSE => 'ticketsql');
115 $_[0]->SUPER::_OpenParen( 'ticketsql' );
118 $_[0]->SUPER::_CloseParen( 'ticketsql' );
125 =head2 Robert's Simple SQL Parser
127 Documentation In Progress
129 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:
131 VALUE -> quoted string or number
132 AGGREGator -> AND or OR
133 KEYWORD -> quoted string or single word
134 OPerator -> =,!=,LIKE,etc..
135 PARENthesis -> open or close.
137 And that stream of tokens is passed through the "machine" in order to build up a structure that looks like:
143 That also deals with parenthesis for nesting. (The parentheses are
144 just handed off the SearchBuilder)
148 use Regexp::Common qw /delimited/;
151 use constant VALUE => 1;
152 use constant AGGREG => 2;
153 use constant OP => 4;
154 use constant OPEN_PAREN => 8;
155 use constant CLOSE_PAREN => 16;
156 use constant KEYWORD => 32;
157 my @tokens = qw[VALUE AGGREG OP OPEN_PAREN CLOSE_PAREN KEYWORD];
159 my $re_aggreg = qr[(?i:AND|OR)];
160 my $re_delim = qr[$RE{delimited}{-delim=>qq{\'\"}}];
161 my $re_value = qr[$re_delim|\d+|NULL];
162 my $re_keyword = qr[$re_delim|(?:\{|\}|\w|\.)+];
163 my $re_op = qr[=|!=|>=|<=|>|<|(?i:IS NOT)|(?i:IS)|(?i:NOT LIKE)|(?i:LIKE)]; # long to short
164 my $re_open_paren = qr'\(';
165 my $re_close_paren = qr'\)';
169 my ($self, @bundle) = @_;
170 return unless @bundle;
172 $bundle[0]->{dispatch}->(
178 ENTRYAGGREGATOR => $bundle[0]->{ea},
179 SUBKEY => $bundle[0]->{subkey},
183 for my $chunk (@bundle) {
189 ENTRYAGGREGATOR => $chunk->{ea},
190 SUBKEY => $chunk->{subkey},
193 $bundle[0]->{dispatch}->(
200 my ($self,$string) = @_;
201 my $want = KEYWORD | OPEN_PAREN;
207 my ($ea,$key,$op,$value) = ("","","","");
209 # order of matches in the RE is important.. op should come early,
210 # because it has spaces in it. otherwise "NOT LIKE" might be parsed
211 # as a keyword or value.
228 # Highest priority is last
229 $current = OP if ($want & OP) && $val =~ /^$re_op$/io;
230 $current = VALUE if ($want & VALUE) && $val =~ /^$re_value$/io;
231 $current = KEYWORD if ($want & KEYWORD) && $val =~ /^$re_keyword$/io;
232 $current = AGGREG if ($want & AGGREG) && $val =~ /^$re_aggreg$/io;
233 $current = OPEN_PAREN if ($want & OPEN_PAREN) && $val =~ /^$re_open_paren$/io;
234 $current = CLOSE_PAREN if ($want & CLOSE_PAREN) && $val =~ /^$re_close_paren$/io;
237 unless ($current && $want & $current) {
239 # FIXME: I will only print out the highest $want value
240 die "Error near ->$val<- expecting a ", $tokens[((log $want)/(log 2))], " in $string\n";
245 #$RT::Logger->debug("We've just found a '$current' called '$val'");
247 # Parens are highest priority
248 if ($current & OPEN_PAREN) {
249 $self->_close_bundle(@bundle); @bundle = ();
253 $want = KEYWORD | OPEN_PAREN;
255 elsif ( $current & CLOSE_PAREN ) {
256 $self->_close_bundle(@bundle); @bundle = ();
260 $want = CLOSE_PAREN | AGGREG;
262 elsif ( $current & AGGREG ) {
264 $want = KEYWORD | OPEN_PAREN;
266 elsif ( $current & KEYWORD ) {
270 elsif ( $current & OP ) {
274 elsif ( $current & VALUE ) {
277 # Remove surrounding quotes from $key, $val
278 # (in future, simplify as for($key,$val) { action on $_ })
279 if ($key =~ /$re_delim/o) {
280 substr($key,0,1) = "";
281 substr($key,-1,1) = "";
283 if ($val =~ /$re_delim/o) {
284 substr($val,0,1) = "";
285 substr($val,-1,1) = "";
287 # Unescape escaped characters
288 $key =~ s!\\(.)!$1!g;
289 $val =~ s!\\(.)!$1!g;
290 # print "$ea Key=[$key] op=[$op] val=[$val]\n";
294 if ($key =~ /^(.+?)\.(.+)$/) {
300 if (exists $lcfields{lc $key}) {
301 $key = $lcfields{lc $key};
302 $class = $FIELDS{$key}->[0];
304 # no longer have a default, since CF's are now a real class, not fallthrough
305 # fixme: "default class" is not Generic.
308 die "Unknown field: $key" unless $class;
310 $self->{_sql_localdepth} = 0;
311 die "No such dispatch method: $class"
312 unless exists $dispatch{$class};
313 my $sub = $dispatch{$class} || die;;
314 if ($can_bundle{$class} &&
316 ($bundle[-1]->{dispatch} == $sub &&
317 $bundle[-1]->{key} eq $key &&
318 $bundle[-1]->{subkey} eq $subkey)))
329 $self->_close_bundle(@bundle); @bundle = ();
335 SUBCLAUSE => "", # don't need anymore
336 ENTRYAGGREGATOR => $ea || "",
341 $self->{_sql_looking_at}{lc $key} = 1;
343 ($ea,$key,$op,$value) = ("","","","");
345 $want = CLOSE_PAREN | AGGREG;
353 $self->_close_bundle(@bundle); @bundle = ();
355 die "Incomplete query"
356 unless (($want | CLOSE_PAREN) || ($want | KEYWORD));
358 die "Incomplete Query"
359 unless ($last && ($last | CLOSE_PAREN) || ($last || VALUE));
361 # This will never happen, because the parser will complain
362 die "Mismatched parentheses"
377 for my $f (keys %{$clauses}) {
381 # Build SQL from the data hash
382 for my $data ( @{ $clauses->{$f} } ) {
383 $sql .= $data->[0] unless $first; $first=0; # ENTRYAGGREGATOR
384 $sql .= " '". $data->[2] . "' "; # FIELD
385 $sql .= $data->[3] . " "; # OPERATOR
386 $sql .= "'". $data->[4] . "' "; # VALUE
389 push @sql, " ( " . $sql . " ) ";
392 return join("AND",@sql);
397 Convert a RT-SQL string into a set of SearchBuilder restrictions.
399 Returns (1, 'Status message') on success and (0, 'Error Message') on
409 my $tix = RT::Tickets->new($RT::SystemUser);
411 my $query = "Status = 'open'";
412 my ($id, $msg) = $tix->FromSQL($query);
417 my (@ids, @expectedids);
419 my $t = RT::Ticket->new($RT::SystemUser);
421 my $string = 'subject/content SQL test';
422 ok( $t->Create(Queue => 'General', Subject => $string), "Ticket Created");
426 my $Message = MIME::Entity->build(
427 Subject => 'this is my subject',
428 From => 'jesse@example.com',
432 ok( $t->Create(Queue => 'General', Subject => 'another ticket', MIMEObj => $Message, MemberOf => $ids[0]), "Ticket Created");
436 $query = ("Subject LIKE '$string' OR Content LIKE '$string'");
438 my ($id, $msg) = $tix->FromSQL($query);
443 is ($tix->Count, scalar @ids, "number of returned tickets same as entered");
444 while (my $tick = $tix->Next) {
445 push @expectedids, $tick->Id;
447 ok (eq_array(\@ids, \@expectedids), "returned expected tickets");
449 $query = ("id = $ids[0] OR MemberOf = $ids[0]");
451 my ($id, $msg) = $tix->FromSQL($query);
455 is ($tix->Count, scalar @ids, "number of returned tickets same as entered");
458 while (my $tick = $tix->Next) {
459 push @expectedids, $tick->Id;
462 ok (eq_array(\@ids, \@expectedids), "returned expected tickets");
470 my ($self,$query) = @_;
473 # preserve first_row and show_rows across the CleanSlate
474 local($self->{'first_row'}, $self->{'show_rows'});
479 return (1,$self->loc("No Query")) unless $query;
481 $self->{_sql_query} = $query;
482 eval { $self->_parser( $query ); };
484 $RT::Logger->error( "Query error in <<$query>>:\n$@" );
487 # We only want to look at EffectiveId's (mostly) for these searches.
488 unless (exists $self->{_sql_looking_at}{'effectiveid'}) {
489 $self->SUPER::Limit( FIELD => 'EffectiveId',
490 ENTRYAGGREGATOR => 'AND',
494 ); #TODO, we shouldn't be hard #coding the tablename to main.
496 # FIXME: Need to bring this logic back in
498 # if ($self->_isLimited && (! $self->{'looking_at_effective_id'})) {
499 # $self->SUPER::Limit( FIELD => 'EffectiveId',
502 # VALUE => 'main.id'); #TODO, we shouldn't be hard coding the tablename to main.
504 # --- This is hardcoded above. This comment block can probably go.
505 # Or, we need to reimplement the looking_at_effective_id toggle.
507 # Unless we've explicitly asked to look at a specific Type, we need
509 unless ($self->{looking_at_type}) {
510 $self->SUPER::Limit( FIELD => 'Type', OPERATOR => '=', VALUE => 'ticket');
513 # We don't want deleted tickets unless 'allow_deleted_search' is set
514 unless( $self->{'allow_deleted_search'} ) {
515 $self->SUPER::Limit(FIELD => 'Status',
521 # set SB's dirty flag
522 $self->{'must_redo_search'} = 1;
523 $self->{'RecalcTicketLimits'} = 0;
525 return (1,$self->loc("Valid Query"));
531 Returns the query that this object was initialized with
537 return ($self->{_sql_query});
548 Most of the RT code does not use Exceptions (die/eval) but it is used
549 in the TicketSQL code for simplicity and historical reasons. Lest you
550 be worried that the dies will trigger user visible errors, all are
553 99% of the dies fall in subroutines called via FromSQL and then parse.
554 (This includes all of the _FooLimit routines in Tickets_Overlay.pm.)
555 The other 1% or so are via _ProcessRestrictions.
557 All dies are trapped by eval {}s, and will be logged at the 'error'
558 log level. The general failure mode is to not display any tickets.
564 Legacy LimitFoo routines build up a RestrictionsHash
566 _ProcessRestrictions converts the Restrictions to Clauses
569 Clauses are converted to RT-SQL (TicketSQL)
573 FromSQL calls the parser
575 The parser calls the _FooLimit routines to do DBIx::SearchBuilder
578 And then the normal SearchBuilder/Ticket routines are used for