[usit-rt.git] / lib / RT / Tickets_SQL.pm

# BEGIN BPS TAGGED BLOCK {{{
#
# COPYRIGHT:
#
# This software is Copyright (c) 1996-2013 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, %LOWER_CASE_FIELDS, %dispatch, %can_bundle);

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 = '';

    # Bundling of joins is implemented by dynamically tracking a parallel query
    # tree in %sub_tree as the TicketSQL is parsed.  Don't be fooled by
    # _close_bundle(), @bundle, and %can_bundle; they are completely unused for
    # quite a long time and removed in RT 4.2.  For now they stay, a useless
    # relic.
    #
    # Only positive, OR'd watcher conditions are bundled currently.  Each key
    # in %sub_tree is a watcher type (Requestor, Cc, AdminCc) or the generic
    # "Watcher" for any watcher type.  Owner is not bundled because it is
    # denormalized into a Tickets column and doesn't need a join.  AND'd
    # conditions are not bundled since a record may have multiple watchers
    # which independently match the conditions, thus necessitating two joins.
    #
    # The values of %sub_tree are arrayrefs made up of:
    #
    #   * Open parentheses "(" pushed on by the OpenParen callback
    #   * Arrayrefs of bundled join aliases pushed on by the Condition callback
    #   * Entry aggregators (AND/OR) pushed on by the EntryAggregator callback
    #
    # The CloseParen callback takes care of backing off the query trees until
    # outside of the just-closed parenthetical, thus restoring the tree state
    # an equivalent of before the parenthetical was entered.
    #
    # The Condition callback handles starting a new subtree or extending an
    # existing one, determining if bundling the current condition with any
    # subtree is possible, and pruning any dangling entry aggregators from
    # trees.
    #

    my %sub_tree;
    my $depth = 0;

    my %callback;
    $callback{'OpenParen'} = sub {
      $self->_close_bundle(@bundle); @bundle = ();
      $self->_OpenParen;
      $depth++;
      push @$_, '(' foreach values %sub_tree;
    };
    $callback{'CloseParen'} = sub {
      $self->_close_bundle(@bundle); @bundle = ();
      $self->_CloseParen;
      $depth--;
      foreach my $list ( values %sub_tree ) {
          if ( $list->[-1] eq '(' ) {
              pop @$list;
              pop @$list if $list->[-1] =~ /^(?:AND|OR)$/i;
          }
          else {
              pop @$list while $list->[-2] ne '(';
              $list->[-1] = pop @$list;
          }
      }
    };
    $callback{'EntryAggregator'} = sub {
      $ea = $_[0] || '';
      push @$_, $ea foreach grep @$_ && $_->[-1] ne '(', values %sub_tree;
    };
    $callback{'Condition'} = sub {
        my ($key, $op, $value) = @_;

        my ($negative_op, $null_op, $inv_op, $range_op)
            = $self->ClassifySQLOperation( $op );
        # 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 $LOWER_CASE_FIELDS{lc $key}) {
            $key = $LOWER_CASE_FIELDS{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 = ();
            my @res; my $bundle_with;
            if ( $class eq 'WATCHERFIELD' && $key ne 'Owner' && !$negative_op && (!$null_op || $subkey) ) {
                if ( !$sub_tree{$key} ) {
                  $sub_tree{$key} = [ ('(')x$depth, \@res ];
                } else {
                  $bundle_with = $self->_check_bundling_possibility( $string, @{ $sub_tree{$key} } );
                  if ( $sub_tree{$key}[-1] eq '(' ) {
                        push @{ $sub_tree{$key} }, \@res;
                  }
                }
            }

            # Remove our aggregator from subtrees where our condition didn't get added
            pop @$_ foreach grep @$_ && $_->[-1] =~ /^(?:AND|OR)$/i, values %sub_tree;

            # A reference to @res may be pushed onto $sub_tree{$key} from
            # above, and we fill it here.
            @res = $sub->( $self, $key, $op, $value,
                    SUBCLAUSE       => '',  # don't need anymore
                    ENTRYAGGREGATOR => $ea,
                    SUBKEY          => $subkey,
                    BUNDLE          => $bundle_with,
                  );
        }
        $self->{_sql_looking_at}{lc $key} = 1;
        $ea = '';
    };
    RT::SQL::Parse($string, \%callback);
    $self->_close_bundle(@bundle); @bundle = ();
}

sub _check_bundling_possibility {
    my $self = shift;
    my $string = shift;
    my @list = reverse @_;
    while (my $e = shift @list) {
        next if $e eq '(';
        if ( lc($e) eq 'and' ) {
            return undef;
        }
        elsif ( lc($e) eq 'or' ) {
            return shift @list;
        }
        else {
            # should not happen
            $RT::Logger->error(
                "Joins optimization failed when parsing '$string'. It's bug in RT, contact Best Practical"
            );
            die "Internal error. Contact your system administrator.";
        }
    }
    return undef;
}

=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 ( $@ ) {
        my $error = "$@";
        $RT::Logger->error("Couldn't parse query: $error");
        return (0, $error);
    }

    # 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
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)
01e3b242 MKG	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}) {
403d7b0b MKG	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