1 # BEGIN BPS TAGGED BLOCK {{{
5 # This software is Copyright (c) 1996-2009 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., 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.
30 # CONTRIBUTION SUBMISSION POLICY:
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.)
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.
47 # END BPS TAGGED BLOCK }}}
53 # Import configuration data from the lexcial scope of __PACKAGE__ (or
54 # at least where those two Subroutines are defined.)
56 my %FIELD_METADATA = %{FIELDS()};
57 my %dispatch = %{dispatch()};
58 my %can_bundle = %{can_bundle()};
60 # Lower Case version of FIELDS, for case insensitivity
61 my %lcfields = map { ( lc($_) => $_ ) } (keys %FIELD_METADATA);
66 # How many of these do we actually still use?
68 # Private Member Variales (which should get cleaned)
69 $self->{'_sql_linksc'} = 0;
70 $self->{'_sql_watchersc'} = 0;
71 $self->{'_sql_keywordsc'} = 0;
72 $self->{'_sql_subclause'} = "a";
73 $self->{'_sql_first'} = 0;
74 $self->{'_sql_opstack'} = [''];
75 $self->{'_sql_linkalias'} = undef;
76 $self->{'_sql_transalias'} = undef;
77 $self->{'_sql_trattachalias'} = undef;
78 $self->{'_sql_object_cf_alias'} = undef;
79 $self->{'_sql_depth'} = 0;
80 $self->{'_sql_localdepth'} = 0;
81 $self->{'_sql_query'} = '';
82 $self->{'_sql_looking_at'} = {};
83 $self->{'_sql_columns_to_display'} = [];
90 if ($args{'FIELD'} eq 'EffectiveId' &&
91 (!$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) ) {
92 $self->{'looking_at_effective_id'} = 1;
95 if ($args{'FIELD'} eq 'Type' &&
96 (!$args{'ALIAS'} || $args{'ALIAS'} eq 'main' ) ) {
97 $self->{'looking_at_type'} = 1;
100 # All SQL stuff goes into one SB subclause so we can deal with all
102 $self->SUPER::Limit(%args,
103 SUBCLAUSE => 'ticketsql');
107 # All SQL stuff goes into one SB subclause so we can deal with all
111 $this->SUPER::Join(@_,
112 SUBCLAUSE => 'ticketsql');
117 $_[0]->SUPER::_OpenParen( 'ticketsql' );
120 $_[0]->SUPER::_CloseParen( 'ticketsql' );
127 =head2 Robert's Simple SQL Parser
129 Documentation In Progress
131 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:
133 VALUE -> quoted string or number
134 AGGREGator -> AND or OR
135 KEYWORD -> quoted string or single word
136 OPerator -> =,!=,LIKE,etc..
137 PARENthesis -> open or close.
139 And that stream of tokens is passed through the "machine" in order to build up a structure that looks like:
145 That also deals with parenthesis for nesting. (The parentheses are
146 just handed off the SearchBuilder)
150 use Regexp::Common qw /delimited/;
153 use constant VALUE => 1;
154 use constant AGGREG => 2;
155 use constant OP => 4;
156 use constant OPEN_PAREN => 8;
157 use constant CLOSE_PAREN => 16;
158 use constant KEYWORD => 32;
159 my @tokens = qw[VALUE AGGREG OP OPEN_PAREN CLOSE_PAREN KEYWORD];
161 my $re_aggreg = qr[(?i:AND|OR)];
162 my $re_delim = qr[$RE{delimited}{-delim=>qq{\'\"}}];
163 my $re_value = qr[$re_delim|\d+|NULL];
164 my $re_keyword = qr[$re_delim|(?:\{|\}|\w|\.)+];
165 my $re_op = qr[=|!=|>=|<=|>|<|(?i:IS NOT)|(?i:IS)|(?i:NOT LIKE)|(?i:LIKE)]; # long to short
166 my $re_open_paren = qr'\(';
167 my $re_close_paren = qr'\)';
171 my ($self, @bundle) = @_;
172 return unless @bundle;
174 $bundle[0]->{dispatch}->(
180 ENTRYAGGREGATOR => $bundle[0]->{ea},
181 SUBKEY => $bundle[0]->{subkey},
185 for my $chunk (@bundle) {
191 ENTRYAGGREGATOR => $chunk->{ea},
192 SUBKEY => $chunk->{subkey},
195 $bundle[0]->{dispatch}->(
202 my ($self,$string) = @_;
203 my $want = KEYWORD | OPEN_PAREN;
209 my ($ea,$key,$op,$value) = ("","","","");
211 # order of matches in the RE is important.. op should come early,
212 # because it has spaces in it. otherwise "NOT LIKE" might be parsed
213 # as a keyword or value.
230 # Highest priority is last
231 $current = OP if ($want & OP) && $val =~ /^$re_op$/io;
232 $current = VALUE if ($want & VALUE) && $val =~ /^$re_value$/io;
233 $current = KEYWORD if ($want & KEYWORD) && $val =~ /^$re_keyword$/io;
234 $current = AGGREG if ($want & AGGREG) && $val =~ /^$re_aggreg$/io;
235 $current = OPEN_PAREN if ($want & OPEN_PAREN) && $val =~ /^$re_open_paren$/io;
236 $current = CLOSE_PAREN if ($want & CLOSE_PAREN) && $val =~ /^$re_close_paren$/io;
239 unless ($current && $want & $current) {
241 # FIXME: I will only print out the highest $want value
242 die "Error near ->$val<- expecting a ", $tokens[((log $want)/(log 2))], " in $string\n";
247 #$RT::Logger->debug("We've just found a '$current' called '$val'");
249 # Parens are highest priority
250 if ($current & OPEN_PAREN) {
251 $self->_close_bundle(@bundle); @bundle = ();
255 $want = KEYWORD | OPEN_PAREN;
257 elsif ( $current & CLOSE_PAREN ) {
258 $self->_close_bundle(@bundle); @bundle = ();
262 $want = CLOSE_PAREN | AGGREG;
264 elsif ( $current & AGGREG ) {
266 $want = KEYWORD | OPEN_PAREN;
268 elsif ( $current & KEYWORD ) {
272 elsif ( $current & OP ) {
276 elsif ( $current & VALUE ) {
279 # Remove surrounding quotes from $key, $val
280 # (in future, simplify as for($key,$val) { action on $_ })
281 if ($key =~ /$re_delim/o) {
282 substr($key,0,1) = "";
283 substr($key,-1,1) = "";
285 if ($val =~ /$re_delim/o) {
286 substr($val,0,1) = "";
287 substr($val,-1,1) = "";
289 # Unescape escaped characters
290 $key =~ s!\\(.)!$1!g;
291 $val =~ s!\\(.)!$1!g;
292 # print "$ea Key=[$key] op=[$op] val=[$val]\n";
294 # replace __CurrentUser__ with id
295 $val = $self->CurrentUser->id if $val eq '__CurrentUser__';
299 if ($key =~ /^(.+?)\.(.+)$/) {
305 if (exists $lcfields{lc $key}) {
306 $key = $lcfields{lc $key};
307 $class = $FIELD_METADATA{$key}->[0];
309 # no longer have a default, since CF's are now a real class, not fallthrough
310 # fixme: "default class" is not Generic.
313 die "Unknown field: $key" unless $class;
315 $self->{_sql_localdepth} = 0;
316 die "No such dispatch method: $class"
317 unless exists $dispatch{$class};
318 my $sub = $dispatch{$class} || die;;
319 if ($can_bundle{$class} &&
321 ($bundle[-1]->{dispatch} == $sub &&
322 $bundle[-1]->{key} eq $key &&
323 $bundle[-1]->{subkey} eq $subkey)))
334 $self->_close_bundle(@bundle); @bundle = ();
340 SUBCLAUSE => "", # don't need anymore
341 ENTRYAGGREGATOR => $ea || "",
346 $self->{_sql_looking_at}{lc $key} = 1;
348 ($ea,$key,$op,$value) = ("","","","");
350 $want = CLOSE_PAREN | AGGREG;
358 $self->_close_bundle(@bundle); @bundle = ();
360 die "Incomplete query"
361 unless (($want | CLOSE_PAREN) || ($want | KEYWORD));
363 die "Incomplete Query"
364 unless ($last && ($last | CLOSE_PAREN) || ($last || VALUE));
366 # This will never happen, because the parser will complain
367 die "Mismatched parentheses"
382 for my $f (keys %{$clauses}) {
386 # Build SQL from the data hash
387 for my $data ( @{ $clauses->{$f} } ) {
388 $sql .= $data->[0] unless $first; $first=0; # ENTRYAGGREGATOR
389 $sql .= " '". $data->[2] . "' "; # FIELD
390 $sql .= $data->[3] . " "; # OPERATOR
391 $sql .= "'". $data->[4] . "' "; # VALUE
394 push @sql, " ( " . $sql . " ) ";
397 return join("AND",@sql);
402 Convert a RT-SQL string into a set of SearchBuilder restrictions.
404 Returns (1, 'Status message') on success and (0, 'Error Message') on
413 my $tix = RT::Tickets->new($RT::SystemUser);
415 my $query = "Status = 'open'";
416 my ($status, $msg) = $tix->FromSQL($query);
417 ok ($status, "correct query") or diag("error: $msg");
421 my (@created,%created);
422 my $string = 'subject/content SQL test';
424 my $t = RT::Ticket->new($RT::SystemUser);
425 ok( $t->Create(Queue => 'General', Subject => $string), "Ticket Created");
426 $created{ $t->Id }++; push @created, $t->Id;
430 my $Message = MIME::Entity->build(
431 Subject => 'this is my subject',
432 From => 'jesse@example.com',
436 my $t = RT::Ticket->new($RT::SystemUser);
437 ok( $t->Create( Queue => 'General',
438 Subject => 'another ticket',
440 MemberOf => $created[0]
444 $created{ $t->Id }++; push @created, $t->Id;
448 my $query = ("Subject LIKE '$string' OR Content LIKE '$string'");
449 my ($status, $msg) = $tix->FromSQL($query);
450 ok ($status, "correct query") or diag("error: $msg");
453 while (my $tick = $tix->Next) {
454 $count++ if $created{ $tick->id };
456 is ($count, scalar @created, "number of returned tickets same as entered");
460 my $query = "id = $created[0] OR MemberOf = $created[0]";
461 my ($status, $msg) = $tix->FromSQL($query);
462 ok ($status, "correct query") or diag("error: $msg");
465 while (my $tick = $tix->Next) {
466 $count++ if $created{ $tick->id };
468 is ($count, scalar @created, "number of returned tickets same as entered");
478 my ($self,$query) = @_;
481 # preserve first_row and show_rows across the CleanSlate
482 local($self->{'first_row'}, $self->{'show_rows'});
487 return (1,$self->loc("No Query")) unless $query;
489 $self->{_sql_query} = $query;
490 eval { $self->_parser( $query ); };
492 $RT::Logger->error( "Query error in <<$query>>:\n$@" );
495 # We only want to look at EffectiveId's (mostly) for these searches.
496 unless (exists $self->{_sql_looking_at}{'effectiveid'}) {
497 $self->SUPER::Limit( FIELD => 'EffectiveId',
498 ENTRYAGGREGATOR => 'AND',
502 ); #TODO, we shouldn't be hard #coding the tablename to main.
504 # FIXME: Need to bring this logic back in
506 # if ($self->_isLimited && (! $self->{'looking_at_effective_id'})) {
507 # $self->SUPER::Limit( FIELD => 'EffectiveId',
510 # VALUE => 'main.id'); #TODO, we shouldn't be hard coding the tablename to main.
512 # --- This is hardcoded above. This comment block can probably go.
513 # Or, we need to reimplement the looking_at_effective_id toggle.
515 # Unless we've explicitly asked to look at a specific Type, we need
517 unless ($self->{looking_at_type}) {
518 $self->SUPER::Limit( FIELD => 'Type', OPERATOR => '=', VALUE => 'ticket');
521 # We don't want deleted tickets unless 'allow_deleted_search' is set
522 unless( $self->{'allow_deleted_search'} ) {
523 $self->SUPER::Limit(FIELD => 'Status',
529 # set SB's dirty flag
530 $self->{'must_redo_search'} = 1;
531 $self->{'RecalcTicketLimits'} = 0;
533 return (1,$self->loc("Valid Query"));
539 Returns the query that this object was initialized with
545 return ($self->{_sql_query});
556 Most of the RT code does not use Exceptions (die/eval) but it is used
557 in the TicketSQL code for simplicity and historical reasons. Lest you
558 be worried that the dies will trigger user visible errors, all are
561 99% of the dies fall in subroutines called via FromSQL and then parse.
562 (This includes all of the _FooLimit routines in Tickets_Overlay.pm.)
563 The other 1% or so are via _ProcessRestrictions.
565 All dies are trapped by eval {}s, and will be logged at the 'error'
566 log level. The general failure mode is to not display any tickets.
572 Legacy LimitFoo routines build up a RestrictionsHash
574 _ProcessRestrictions converts the Restrictions to Clauses
577 Clauses are converted to RT-SQL (TicketSQL)
581 FromSQL calls the parser
583 The parser calls the _FooLimit routines to do DBIx::SearchBuilder
586 And then the normal SearchBuilder/Ticket routines are used for