X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=rt%2Flib%2FRT%2FTickets_Overlay_SQL.pm;h=4531a16908db839a7f95ec4c74bc23c462e03fc3;hp=4ba19e606985b9cff09a4ab69db674405e1be312;hb=ef20b2b6b1feb47ad02b5ff7525f1a0fd11d0fa4;hpb=c582e92888b4a5553e1b4e5214cf35217e4a0cf0 diff --git a/rt/lib/RT/Tickets_Overlay_SQL.pm b/rt/lib/RT/Tickets_Overlay_SQL.pm index 4ba19e606..4531a1690 100644 --- a/rt/lib/RT/Tickets_Overlay_SQL.pm +++ b/rt/lib/RT/Tickets_Overlay_SQL.pm @@ -1,8 +1,14 @@ -# BEGIN LICENSE BLOCK +# BEGIN BPS TAGGED BLOCK {{{ # -# Copyright (c) 1996-2003 Jesse Vincent +# COPYRIGHT: +# +# This software is Copyright (c) 1996-2007 Best Practical Solutions, LLC +# # -# (Except where explictly superceded by other copyright notices) +# (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 @@ -14,27 +20,45 @@ # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU # General Public License for more details. # -# Unless otherwise specified, all modifications, corrections or -# extensions to this work which alter its source code become the -# property of Best Practical Solutions, LLC when submitted for -# inclusion in the work. +# 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/copyleft/gpl.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.) # -# END LICENSE BLOCK +# 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::Tickets; - # Import configuration data from the lexcial scope of __PACKAGE__ (or # at least where those two Subroutines are defined.) -my %FIELDS = %{FIELDS()}; +my %FIELD_METADATA = %{FIELDS()}; my %dispatch = %{dispatch()}; my %can_bundle = %{can_bundle()}; # Lower Case version of FIELDS, for case insensitivity -my %lcfields = map { ( lc($_) => $_ ) } (keys %FIELDS); +my %lcfields = map { ( lc($_) => $_ ) } (keys %FIELD_METADATA); sub _InitSQL { my $self = shift; @@ -51,7 +75,7 @@ sub _InitSQL { $self->{'_sql_linkalias'} = undef; $self->{'_sql_transalias'} = undef; $self->{'_sql_trattachalias'} = undef; - $self->{'_sql_keywordalias'} = undef; + $self->{'_sql_object_cf_alias'} = undef; $self->{'_sql_depth'} = 0; $self->{'_sql_localdepth'} = 0; $self->{'_sql_query'} = ''; @@ -61,11 +85,21 @@ sub _InitSQL { } 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 - my $this = shift; - - $this->SUPER::Limit(@_, + $self->SUPER::Limit(%args, SUBCLAUSE => 'ticketsql'); } @@ -90,14 +124,6 @@ sub _CloseParen { =cut -sub _match { - # Case insensitive equality - my ($y,$x) = @_; - return 1 if $x =~ /^$y$/i; - # return 1 if ((lc $x) eq (lc $y)); # Why isnt this equiv? - return 0; -} - =head2 Robert's Simple SQL Parser Documentation In Progress @@ -127,15 +153,18 @@ use Regexp::Common qw /delimited/; use constant VALUE => 1; use constant AGGREG => 2; use constant OP => 4; -use constant PAREN => 8; -use constant KEYWORD => 16; -my @tokens = qw[VALUE AGGREG OP PAREN KEYWORD]; +use constant OPEN_PAREN => 8; +use constant CLOSE_PAREN => 16; +use constant KEYWORD => 32; +my @tokens = qw[VALUE AGGREG OP OPEN_PAREN CLOSE_PAREN KEYWORD]; my $re_aggreg = qr[(?i:AND|OR)]; -my $re_value = qr[$RE{delimited}{-delim=>qq{\'\"}}|\d+]; -my $re_keyword = qr[$RE{delimited}{-delim=>qq{\'\"}}|(?:\{|\}|\w|\.)+]; +my $re_delim = qr[$RE{delimited}{-delim=>qq{\'\"}}]; +my $re_value = qr[$re_delim|\d+|NULL]; +my $re_keyword = qr[$re_delim|(?:\{|\}|\w|\.)+]; my $re_op = qr[=|!=|>=|<=|>|<|(?i:IS NOT)|(?i:IS)|(?i:NOT LIKE)|(?i:LIKE)]; # long to short -my $re_paren = qr'\(|\)'; +my $re_open_paren = qr'\('; +my $re_close_paren = qr'\)'; sub _close_bundle { @@ -171,7 +200,7 @@ sub _close_bundle sub _parser { my ($self,$string) = @_; - my $want = KEYWORD | PAREN; + my $want = KEYWORD | OPEN_PAREN; my $last = undef; my $depth = 0; @@ -192,17 +221,19 @@ sub _parser { |$re_op |$re_keyword |$re_value - |$re_paren - )/igx ) { + |$re_open_paren + |$re_close_paren + )/iogx ) { my $val = $1; my $current = 0; # Highest priority is last - $current = OP if _match($re_op,$val) ; - $current = VALUE if _match($re_value,$val); - $current = KEYWORD if _match($re_keyword,$val) && ($want & KEYWORD); - $current = AGGREG if _match($re_aggreg,$val); - $current = PAREN if _match($re_paren,$val); + $current = OP if ($want & OP) && $val =~ /^$re_op$/io; + $current = VALUE if ($want & VALUE) && $val =~ /^$re_value$/io; + $current = KEYWORD if ($want & KEYWORD) && $val =~ /^$re_keyword$/io; + $current = AGGREG if ($want & AGGREG) && $val =~ /^$re_aggreg$/io; + $current = OPEN_PAREN if ($want & OPEN_PAREN) && $val =~ /^$re_open_paren$/io; + $current = CLOSE_PAREN if ($want & CLOSE_PAREN) && $val =~ /^$re_close_paren$/io; unless ($current && $want & $current) { @@ -216,24 +247,23 @@ sub _parser { #$RT::Logger->debug("We've just found a '$current' called '$val'"); # Parens are highest priority - if ($current & PAREN) { - if ($val eq "(") { - $self->_close_bundle(@bundle); @bundle = (); - $depth++; - $self->_OpenParen; - - } else { - $self->_close_bundle(@bundle); @bundle = (); - $depth--; - $self->_CloseParen; - } + if ($current & OPEN_PAREN) { + $self->_close_bundle(@bundle); @bundle = (); + $depth++; + $self->_OpenParen; - $want = KEYWORD | PAREN | AGGREG; + $want = KEYWORD | OPEN_PAREN; } + elsif ( $current & CLOSE_PAREN ) { + $self->_close_bundle(@bundle); @bundle = (); + $depth--; + $self->_CloseParen; + $want = CLOSE_PAREN | AGGREG; + } elsif ( $current & AGGREG ) { $ea = $val; - $want = KEYWORD | PAREN; + $want = KEYWORD | OPEN_PAREN; } elsif ( $current & KEYWORD ) { $key = $val; @@ -248,21 +278,21 @@ sub _parser { # Remove surrounding quotes from $key, $val # (in future, simplify as for($key,$val) { action on $_ }) - if ($key =~ /$RE{delimited}{-delim=>qq{\'\"}}/) { + if ($key =~ /$re_delim/o) { substr($key,0,1) = ""; substr($key,-1,1) = ""; } - if ($val =~ /$RE{delimited}{-delim=>qq{\'\"}}/) { + if ($val =~ /$re_delim/o) { substr($val,0,1) = ""; substr($val,-1,1) = ""; } # Unescape escaped characters - $key =~ s!\\(.)!$1!g; - $val =~ s!\\(.)!$1!g; + $key =~ s!\\(.)!$1!g; + $val =~ s!\\(.)!$1!g; # print "$ea Key=[$key] op=[$op] val=[$val]\n"; - my $subkey; + my $subkey = ''; if ($key =~ /^(.+?)\.(.+)$/) { $key = $1; $subkey = $2; @@ -271,7 +301,7 @@ sub _parser { my $class; if (exists $lcfields{lc $key}) { $key = $lcfields{lc $key}; - $class = $FIELDS{$key}->[0]; + $class = $FIELD_METADATA{$key}->[0]; } # no longer have a default, since CF's are now a real class, not fallthrough # fixme: "default class" is not Generic. @@ -314,7 +344,7 @@ sub _parser { ($ea,$key,$op,$value) = ("","","",""); - $want = PAREN | AGGREG; + $want = CLOSE_PAREN | AGGREG; } else { die "I'm lost"; } @@ -325,10 +355,10 @@ sub _parser { $self->_close_bundle(@bundle); @bundle = (); die "Incomplete query" - unless (($want | PAREN) || ($want | KEYWORD)); + unless (($want | CLOSE_PAREN) || ($want | KEYWORD)); die "Incomplete Query" - unless ($last && ($last | PAREN) || ($last || VALUE)); + unless ($last && ($last | CLOSE_PAREN) || ($last || VALUE)); # This will never happen, because the parser will complain die "Mismatched parentheses" @@ -351,11 +381,11 @@ sub ClausesToSQL { my $first = 1; # Build SQL from the data hash - for my $data ( @{ $clauses->{$f} } ) { - $sql .= $data->[0] unless $first; $first=0; - $sql .= " '". $data->[2] . "' "; - $sql .= $data->[3] . " "; - $sql .= "'". $data->[4] . "' "; + 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 . " ) "; @@ -375,64 +405,66 @@ failure. =begin testing use RT::Tickets; - - +use strict; my $tix = RT::Tickets->new($RT::SystemUser); +{ + my $query = "Status = 'open'"; + my ($status, $msg) = $tix->FromSQL($query); + ok ($status, "correct query") or diag("error: $msg"); +} -my $query = "Status = 'open'"; -my ($id, $msg) = $tix->FromSQL($query); - -ok ($id, $msg); - - -my (@ids, @expectedids); - -my $t = RT::Ticket->new($RT::SystemUser); +my (@created,%created); my $string = 'subject/content SQL test'; -ok( $t->Create(Queue => 'General', Subject => $string), "Ticket Created"); - -push @ids, $t->Id; - -my $Message = MIME::Entity->build( - Subject => 'this is my subject', - From => 'jesse@example.com', - Data => [ $string ], - ); - -ok( $t->Create(Queue => 'General', Subject => 'another ticket', MIMEObj => $Message, MemberOf => $ids[0]), "Ticket Created"); - -push @ids, $t->Id; - -$query = ("Subject LIKE '$string' OR Content LIKE '$string'"); - -my ($id, $msg) = $tix->FromSQL($query); - -ok ($id, $msg); - -is ($tix->Count, scalar @ids, "number of returned tickets same as entered"); - -while (my $tick = $tix->Next) { - push @expectedids, $tick->Id; +{ + my $t = RT::Ticket->new($RT::SystemUser); + ok( $t->Create(Queue => 'General', Subject => $string), "Ticket Created"); + $created{ $t->Id }++; push @created, $t->Id; } -ok (eq_array(\@ids, \@expectedids), "returned expected tickets"); - -$query = ("id = $ids[0] OR MemberOf = $ids[0]"); +{ + my $Message = MIME::Entity->build( + Subject => 'this is my subject', + From => 'jesse@example.com', + Data => [ $string ], + ); + + my $t = RT::Ticket->new($RT::SystemUser); + ok( $t->Create( Queue => 'General', + Subject => 'another ticket', + MIMEObj => $Message, + MemberOf => $created[0] + ), + "Ticket Created" + ); + $created{ $t->Id }++; push @created, $t->Id; +} -my ($id, $msg) = $tix->FromSQL($query); +{ + my $query = ("Subject LIKE '$string' OR Content LIKE '$string'"); + my ($status, $msg) = $tix->FromSQL($query); + ok ($status, "correct query") or diag("error: $msg"); -ok ($id, $msg); + my $count = 0; + while (my $tick = $tix->Next) { + $count++ if $created{ $tick->id }; + } + is ($count, scalar @created, "number of returned tickets same as entered"); +} -is ($tix->Count, scalar @ids, "number of returned tickets same as entered"); +{ + my $query = "id = $created[0] OR MemberOf = $created[0]"; + my ($status, $msg) = $tix->FromSQL($query); + ok ($status, "correct query") or diag("error: $msg"); -@expectedids = (); -while (my $tick = $tix->Next) { - push @expectedids, $tick->Id; + my $count = 0; + while (my $tick = $tix->Next) { + $count++ if $created{ $tick->id }; + } + is ($count, scalar @created, "number of returned tickets same as entered"); } -ok (eq_array(\@ids, \@expectedids), "returned expected tickets"); =end testing @@ -442,7 +474,6 @@ ok (eq_array(\@ids, \@expectedids), "returned expected tickets"); sub FromSQL { my ($self,$query) = @_; - $self->CleanSlate; { # preserve first_row and show_rows across the CleanSlate local($self->{'first_row'}, $self->{'show_rows'}); @@ -455,7 +486,7 @@ sub FromSQL { $self->{_sql_query} = $query; eval { $self->_parser( $query ); }; if ($@) { - $RT::Logger->error( $@ ); + $RT::Logger->error( "Query error in <<$query>>:\n$@" ); return(0,$@); } # We only want to look at EffectiveId's (mostly) for these searches. @@ -484,8 +515,12 @@ sub FromSQL { $self->SUPER::Limit( FIELD => 'Type', OPERATOR => '=', VALUE => 'ticket'); } - # We never ever want to show deleted tickets - $self->SUPER::Limit(FIELD => 'Status' , OPERATOR => '!=', VALUE => 'deleted'); + # 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