X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=rt%2Flib%2FRT%2FTickets_Overlay.pm;h=2d89ca2db8788265e9f239094c74f3ce96bcf433;hp=1c31f3ffdea4da13232e3cc4133fcb8def6e95f4;hb=ef20b2b6b1feb47ad02b5ff7525f1a0fd11d0fa4;hpb=a513c0bef534d05f03c1242831b6f3be19b97dae diff --git a/rt/lib/RT/Tickets_Overlay.pm b/rt/lib/RT/Tickets_Overlay.pm index 1c31f3ffd..2d89ca2db 100644 --- a/rt/lib/RT/Tickets_Overlay.pm +++ b/rt/lib/RT/Tickets_Overlay.pm @@ -1,38 +1,40 @@ # BEGIN BPS TAGGED BLOCK {{{ -# +# # COPYRIGHT: -# -# This software is Copyright (c) 1996-2005 Best Practical Solutions, LLC +# +# This software is Copyright (c) 1996-2007 Best Practical Solutions, LLC # -# +# # (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., 675 Mass Ave, Cambridge, MA 02139, USA. -# -# +# 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.) -# +# # 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 @@ -41,7 +43,7 @@ # 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 }}} # Major Changes: @@ -85,20 +87,18 @@ ok( $testtickets->Count == 0 ); package RT::Tickets; use strict; - -package RT::Tickets; - no warnings qw(redefine); -use vars qw(@SORTFIELDS); + use RT::CustomFields; +use DBIx::SearchBuilder::Unique; # Configuration Tables: -# FIELDS is a mapping of searchable Field name, to Type, and other +# FIELD_METADATA is a mapping of searchable Field name, to Type, and other # metadata. -my %FIELDS = ( - Status => ['ENUM'], +my %FIELD_METADATA = ( + Status => [ 'ENUM', ], Queue => [ 'ENUM' => 'Queue', ], Type => [ 'ENUM', ], Creator => [ 'ENUM' => 'User', ], @@ -111,6 +111,7 @@ my %FIELDS = ( Priority => [ 'INT', ], TimeLeft => [ 'INT', ], TimeWorked => [ 'INT', ], + TimeEstimated => [ 'INT', ], MemberOf => [ 'LINK' => To => 'MemberOf', ], DependsOn => [ 'LINK' => To => 'DependsOn', ], RefersTo => [ 'LINK' => To => 'RefersTo', ], @@ -134,9 +135,10 @@ my %FIELDS = ( Requestors => [ 'WATCHERFIELD' => 'Requestor', ], Cc => [ 'WATCHERFIELD' => 'Cc', ], AdminCc => [ 'WATCHERFIELD' => 'AdminCc', ], - Watcher => ['WATCHERFIELD'], + Watcher => [ 'WATCHERFIELD', ], LinkedTo => [ 'LINKFIELD', ], CustomFieldValue => [ 'CUSTOMFIELD', ], + CustomField => [ 'CUSTOMFIELD', ], CF => [ 'CUSTOMFIELD', ], Updated => [ 'TRANSDATE', ], RequestorGroup => [ 'MEMBERSHIPFIELD' => 'Requestor', ], @@ -159,7 +161,7 @@ my %dispatch = ( LINKFIELD => \&_LinkFieldLimit, CUSTOMFIELD => \&_CustomFieldLimit, ); -my %can_bundle = ( WATCHERFIELD => "yes", ); +my %can_bundle = (); # WATCHERFIELD => "yes", ); # Default EntryAggregator per type # if you specify OP, you must specify all valid OPs @@ -200,7 +202,7 @@ my %DefaultEA = ( # Helper functions for passing the above lexically scoped tables above # into Tickets_Overlay_SQL. -sub FIELDS { return \%FIELDS } +sub FIELDS { return \%FIELD_METADATA } sub dispatch { return \%dispatch } sub can_bundle { return \%can_bundle } @@ -209,7 +211,7 @@ require RT::Tickets_Overlay_SQL; # {{{ sub SortFields -@SORTFIELDS = qw(id Status +our @SORTFIELDS = qw(id Status Queue Subject Owner Created Due Starts Started Told @@ -230,6 +232,22 @@ sub SortFields { # BEGIN SQL STUFF ********************************* + +sub CleanSlate { + my $self = shift; + $self->SUPER::CleanSlate( @_ ); + delete $self->{$_} foreach qw( + _sql_cf_alias + _sql_group_members_aliases + _sql_object_cfv_alias + _sql_role_group_aliases + _sql_transalias + _sql_trattachalias + _sql_u_watchers_alias_for_sort + _sql_u_watchers_aliases + ); +} + =head1 Limit Helper Routines These routines are the targets of a dispatch table depending on the @@ -271,8 +289,8 @@ sub _EnumLimit { unless $op eq "=" or $op eq "!="; - my $meta = $FIELDS{$field}; - if ( defined $meta->[1] ) { + my $meta = $FIELD_METADATA{$field}; + if ( defined $meta->[1] && defined $value && $value !~ /^\d+$/ ) { my $class = "RT::" . $meta->[1]; my $o = $class->new( $sb->CurrentUser ); $o->Load($value); @@ -315,26 +333,24 @@ sub _IntLimit { Handle fields which deal with links between tickets. (MemberOf, DependsOn) Meta Data: - 1: Direction (From,To) - 2: Link Type (MemberOf, DependsOn,RefersTo) + 1: Direction (From, To) + 2: Link Type (MemberOf, DependsOn, RefersTo) =cut sub _LinkLimit { my ( $sb, $field, $op, $value, @rest ) = @_; - my $meta = $FIELDS{$field}; - die "Invalid Operator $op for $field" unless $op =~ /^(=|!=|IS)/io; - + my $meta = $FIELD_METADATA{$field}; die "Incorrect Metadata for $field" - unless ( defined $meta->[1] and defined $meta->[2] ); + unless defined $meta->[1] && defined $meta->[2]; + + die "Invalid Operator $op for $field" unless $op =~ /^(=|!=|IS|IS NOT)$/io; my $direction = $meta->[1]; my $matchfield; my $linkfield; - my $is_local = 1; - my $is_null = 0; if ( $direction eq 'To' ) { $matchfield = "Target"; $linkfield = "Base"; @@ -349,84 +365,105 @@ sub _LinkLimit { die "Invalid link direction '$meta->[1]' for $field\n"; } - if ( $op eq '=' || $op =~ /^is/oi ) { - if ( $value eq '' || $value =~ /^null$/io ) { - $is_null = 1; - } - elsif ( $value =~ /\D/o ) { - $is_local = 0; - } - else { - $is_local = 1; - } + my ($is_local, $is_null) = (1, 0); + if ( !$value || $value =~ /^null$/io ) { + $is_null = 1; + $op = ($op =~ /^(=|IS)$/)? 'IS': 'IS NOT'; + } + elsif ( $value =~ /\D/o ) { + $is_local = 0; + } + $matchfield = "Local$matchfield" if $is_local; + + my $is_negative = 0; + if ( $op eq '!=' ) { + $is_negative = 1; + $op = '='; } #For doing a left join to find "unlinked tickets" we want to generate a query that looks like this # SELECT main.* FROM Tickets main # LEFT JOIN Links Links_1 ON ( (Links_1.Type = 'MemberOf') # AND(main.id = Links_1.LocalTarget)) -# WHERE ((main.EffectiveId = main.id)) -# AND ((main.Status != 'deleted')) -# AND (Links_1.LocalBase IS NULL); +# WHERE Links_1.LocalBase IS NULL; if ($is_null) { my $linkalias = $sb->Join( - TYPE => 'left', + TYPE => 'LEFT', ALIAS1 => 'main', FIELD1 => 'id', TABLE2 => 'Links', FIELD2 => 'Local' . $linkfield ); - $sb->SUPER::Limit( LEFTJOIN => $linkalias, FIELD => 'Type', OPERATOR => '=', VALUE => $meta->[2], + ); + $sb->_SQLLimit( @rest, + ALIAS => $linkalias, + FIELD => $matchfield, + OPERATOR => $op, + VALUE => 'NULL', + QUOTEVALUE => 0, + ); + } + elsif ( $is_negative ) { + my $linkalias = $sb->Join( + TYPE => 'LEFT', + ALIAS1 => 'main', + FIELD1 => 'id', + TABLE2 => 'Links', + FIELD2 => 'Local' . $linkfield + ); + $sb->SUPER::Limit( + LEFTJOIN => $linkalias, + FIELD => 'Type', + OPERATOR => '=', + VALUE => $meta->[2], + ); + $sb->SUPER::Limit( + LEFTJOIN => $linkalias, + FIELD => $matchfield, + OPERATOR => $op, + VALUE => $value, ); - $sb->_SQLLimit( - ALIAS => $linkalias, - ENTRYAGGREGATOR => 'AND', - FIELD => ( $is_local ? "Local$matchfield" : $matchfield ), + @rest, + ALIAS => $linkalias, + FIELD => $matchfield, OPERATOR => 'IS', VALUE => 'NULL', - QUOTEVALUE => '0', + QUOTEVALUE => 0, ); - } else { - - $sb->{_sql_linkalias} = $sb->NewAlias('Links') - unless defined $sb->{_sql_linkalias}; - + my $linkalias = $sb->NewAlias('Links'); $sb->_OpenParen(); - $sb->_SQLLimit( - ALIAS => $sb->{_sql_linkalias}, + @rest, + ALIAS => $linkalias, FIELD => 'Type', OPERATOR => '=', VALUE => $meta->[2], - @rest, ); - $sb->_SQLLimit( - ALIAS => $sb->{_sql_linkalias}, + ALIAS => $linkalias, + FIELD => 'Local' . $linkfield, + OPERATOR => '=', + VALUE => 'main.id', + QUOTEVALUE => 0, ENTRYAGGREGATOR => 'AND', - FIELD => ( $is_local ? "Local$matchfield" : $matchfield ), - OPERATOR => '=', - VALUE => $value, ); - - #If we're searching on target, join the base to ticket.id - $sb->_SQLJoin( - ALIAS1 => 'main', - FIELD1 => $sb->{'primary_key'}, - ALIAS2 => $sb->{_sql_linkalias}, - FIELD2 => 'Local' . $linkfield + $sb->_SQLLimit( + ALIAS => $linkalias, + FIELD => $matchfield, + OPERATOR => $op, + VALUE => $value, + ENTRYAGGREGATOR => 'AND', ); - $sb->_CloseParen(); } } @@ -446,15 +483,12 @@ sub _DateLimit { die "Invalid Date Op: $op" unless $op =~ /^(=|>|<|>=|<=)$/; - my $meta = $FIELDS{$field}; + my $meta = $FIELD_METADATA{$field}; die "Incorrect Meta Data for $field" unless ( defined $meta->[1] ); - use POSIX 'strftime'; - my $date = RT::Date->new( $sb->CurrentUser ); $date->Set( Format => 'unknown', Value => $value ); - my $time = $date->Unix; if ( $op eq "=" ) { @@ -462,10 +496,10 @@ sub _DateLimit { # particular single day. in the database, we need to check for > # and < the edges of that day. - my $daystart = strftime( "%Y-%m-%d %H:%M", - gmtime( $time - ( $time % 86400 ) ) ); - my $dayend = strftime( "%Y-%m-%d %H:%M", - gmtime( $time + ( 86399 - $time % 86400 ) ) ); + $date->SetToMidnight( Timezone => 'server' ); + my $daystart = $date->ISO; + $date->AddDay; + my $dayend = $date->ISO; $sb->_OpenParen; @@ -488,11 +522,10 @@ sub _DateLimit { } else { - $value = strftime( "%Y-%m-%d %H:%M", gmtime($time) ); $sb->_SQLLimit( FIELD => $meta->[1], OPERATOR => $op, - VALUE => $value, + VALUE => $date->ISO, @rest, ); } @@ -540,12 +573,23 @@ sub _TransDateLimit { # See the comments for TransLimit, they apply here too - $sb->{_sql_transalias} = $sb->NewAlias('Transactions') - unless defined $sb->{_sql_transalias}; + unless ( $sb->{_sql_transalias} ) { + $sb->{_sql_transalias} = $sb->Join( + ALIAS1 => 'main', + FIELD1 => 'id', + TABLE2 => 'Transactions', + FIELD2 => 'ObjectId', + ); + $sb->SUPER::Limit( + ALIAS => $sb->{_sql_transalias}, + FIELD => 'ObjectType', + VALUE => 'RT::Ticket', + ENTRYAGGREGATOR => 'AND', + ); + } my $date = RT::Date->new( $sb->CurrentUser ); $date->Set( Format => 'unknown', Value => $value ); - my $time = $date->Unix; $sb->_OpenParen; if ( $op eq "=" ) { @@ -554,10 +598,10 @@ sub _TransDateLimit { # particular single day. in the database, we need to check for > # and < the edges of that day. - my $daystart = strftime( "%Y-%m-%d %H:%M", - gmtime( $time - ( $time % 86400 ) ) ); - my $dayend = strftime( "%Y-%m-%d %H:%M", - gmtime( $time + ( 86399 - $time % 86400 ) ) ); + $date->SetToMidnight( Timezone => 'server' ); + my $daystart = $date->ISO; + $date->AddDay; + my $dayend = $date->ISO; $sb->_SQLLimit( ALIAS => $sb->{_sql_transalias}, @@ -587,26 +631,12 @@ sub _TransDateLimit { ALIAS => $sb->{_sql_transalias}, FIELD => 'Created', OPERATOR => $op, - VALUE => $value, + VALUE => $date->ISO, CASESENSITIVE => 0, @rest ); } - # Join Transactions to Tickets - $sb->_SQLJoin( - ALIAS1 => 'main', - FIELD1 => $sb->{'primary_key'}, # UGH! - ALIAS2 => $sb->{_sql_transalias}, - FIELD2 => 'ObjectId' - ); - - $sb->SUPER::Limit( - ALIAS => $sb->{_sql_transalias}, - FIELD => 'ObjectType', - VALUE => 'RT::Ticket' - ); - $sb->_CloseParen; } @@ -655,44 +685,63 @@ sub _TransLimit { my ( $self, $field, $op, $value, @rest ) = @_; - $self->{_sql_transalias} = $self->NewAlias('Transactions') - unless defined $self->{_sql_transalias}; - $self->{_sql_trattachalias} = $self->NewAlias('Attachments') - unless defined $self->{_sql_trattachalias}; + unless ( $self->{_sql_transalias} ) { + $self->{_sql_transalias} = $self->Join( + ALIAS1 => 'main', + FIELD1 => 'id', + TABLE2 => 'Transactions', + FIELD2 => 'ObjectId', + ); + $self->SUPER::Limit( + ALIAS => $self->{_sql_transalias}, + FIELD => 'ObjectType', + VALUE => 'RT::Ticket', + ENTRYAGGREGATOR => 'AND', + ); + } + unless ( defined $self->{_sql_trattachalias} ) { + $self->{_sql_trattachalias} = $self->_SQLJoin( + TYPE => 'LEFT', # not all txns have an attachment + ALIAS1 => $self->{_sql_transalias}, + FIELD1 => 'id', + TABLE2 => 'Attachments', + FIELD2 => 'TransactionId', + ); + } $self->_OpenParen; #Search for the right field - $self->_SQLLimit( - ALIAS => $self->{_sql_trattachalias}, - FIELD => $field, - OPERATOR => $op, - VALUE => $value, - CASESENSITIVE => 0, - @rest - ); - - $self->_SQLJoin( - ALIAS1 => $self->{_sql_trattachalias}, - FIELD1 => 'TransactionId', - ALIAS2 => $self->{_sql_transalias}, - FIELD2 => 'id' - ); - - # Join Transactions to Tickets - $self->_SQLJoin( - ALIAS1 => 'main', - FIELD1 => $self->{'primary_key'}, # Why not use "id" here? - ALIAS2 => $self->{_sql_transalias}, - FIELD2 => 'ObjectId' - ); - - $self->SUPER::Limit( - ALIAS => $self->{_sql_transalias}, - FIELD => 'ObjectType', - VALUE => 'RT::Ticket', - ENTRYAGGREGATOR => 'AND' - ); + if ($field eq 'Content' and $RT::DontSearchFileAttachments) { + $self->_SQLLimit( + ALIAS => $self->{_sql_trattachalias}, + FIELD => 'Filename', + OPERATOR => 'IS', + VALUE => 'NULL', + SUBCLAUSE => 'contentquery', + ENTRYAGGREGATOR => 'AND', + ); + $self->_SQLLimit( + ALIAS => $self->{_sql_trattachalias}, + FIELD => $field, + OPERATOR => $op, + VALUE => $value, + CASESENSITIVE => 0, + @rest, + ENTRYAGGREGATOR => 'AND', + SUBCLAUSE => 'contentquery', + ); + } else { + $self->_SQLLimit( + ALIAS => $self->{_sql_trattachalias}, + FIELD => $field, + OPERATOR => $op, + VALUE => $value, + CASESENSITIVE => 0, + ENTRYAGGREGATOR => 'AND', + @rest + ); + } $self->_CloseParen; @@ -781,136 +830,227 @@ sub _WatcherLimit { my $value = shift; my %rest = (@_); - # Find out what sort of watcher we're looking for - my $fieldname; - if ( ref $field ) { - $fieldname = $field->[0]->[0]; - } - else { - $fieldname = $field; - $field = [ [ $field, $op, $value, %rest ] ]; # gross hack - } - my $meta = $FIELDS{$fieldname}; - my $type = ( defined $meta->[1] ? $meta->[1] : undef ); + my $meta = $FIELD_METADATA{ $field }; + my $type = $meta->[1] || ''; # Owner was ENUM field, so "Owner = 'xxx'" allowed user to # search by id and Name at the same time, this is workaround # to preserve backward compatibility - if ( $fieldname eq 'Owner' ) { - my $flag = 0; - for my $chunk ( splice @$field ) { - my ( $f, $op, $value, %rest ) = @$chunk; - if ( !$rest{SUBKEY} && $op =~ /^!?=$/ ) { - $self->_OpenParen unless $flag++; - my $o = RT::User->new( $self->CurrentUser ); - $o->Load($value); - $value = $o->Id; - $self->_SQLLimit( - FIELD => 'Owner', - OPERATOR => $op, - VALUE => $value, - %rest, - ); - } - else { - push @$field, $chunk; - } - } - $self->_CloseParen if $flag; - return unless @$field; + if ( $field eq 'Owner' && !$rest{SUBKEY} && $op =~ /^!?=$/ ) { + my $o = RT::User->new( $self->CurrentUser ); + $o->Load( $value ); + $self->_SQLLimit( + FIELD => 'Owner', + OPERATOR => $op, + VALUE => $o->Id, + %rest, + ); + return; } + $rest{SUBKEY} ||= 'EmailAddress'; - my $users = $self->_WatcherJoin($type); + my $groups = $self->_RoleGroupsJoin( Type => $type ); - # If we're looking for multiple watchers of a given type, - # TicketSQL will be handing it to us as an array of clauses in - # $field $self->_OpenParen; - for my $chunk (@$field) { - ( $field, $op, $value, %rest ) = @$chunk; - $rest{SUBKEY} ||= 'EmailAddress'; - - my $re_negative_op = qr[!=|NOT LIKE]; - $self->_OpenParen if $op =~ /$re_negative_op/; - + if ( $op =~ /^IS(?: NOT)?$/ ) { + my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups ); + $self->SUPER::Limit( + LEFTJOIN => $group_members, + FIELD => 'GroupId', + OPERATOR => '!=', + VALUE => "$group_members.MemberId", + QUOTEVALUE => 0, + ); $self->_SQLLimit( - ALIAS => $users, - FIELD => $rest{SUBKEY}, + ALIAS => $group_members, + FIELD => 'GroupId', + OPERATOR => $op, VALUE => $value, + %rest, + ); + } + elsif ( $op =~ /^!=$|^NOT\s+/i ) { + # reverse op + $op =~ s/!|NOT\s+//i; + + # XXX: we have no way to build correct "Watcher.X != 'Y'" when condition + # "X = 'Y'" matches more then one user so we try to fetch two records and + # do the right thing when there is only one exist and semi-working solution + # otherwise. + my $users_obj = RT::Users->new( $self->CurrentUser ); + $users_obj->Limit( + FIELD => $rest{SUBKEY}, OPERATOR => $op, - CASESENSITIVE => 0, - %rest + VALUE => $value, ); - - if ( $op =~ /$re_negative_op/ ) { + $users_obj->OrderBy; + $users_obj->RowsPerPage(2); + my @users = @{ $users_obj->ItemsArrayRef }; + + my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups ); + if ( @users <= 1 ) { + my $uid = 0; + $uid = $users[0]->id if @users; + $self->SUPER::Limit( + LEFTJOIN => $group_members, + ALIAS => $group_members, + FIELD => 'MemberId', + VALUE => $uid, + ); $self->_SQLLimit( - ALIAS => $users, - FIELD => $rest{SUBKEY}, + %rest, + ALIAS => $group_members, + FIELD => 'id', OPERATOR => 'IS', VALUE => 'NULL', - ENTRYAGGREGATOR => 'OR', ); - $self->_CloseParen; + } else { + $self->SUPER::Limit( + LEFTJOIN => $group_members, + FIELD => 'GroupId', + OPERATOR => '!=', + VALUE => "$group_members.MemberId", + QUOTEVALUE => 0, + ); + my $users = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $group_members, + FIELD1 => 'MemberId', + TABLE2 => 'Users', + FIELD2 => 'id', + ); + $self->SUPER::Limit( + LEFTJOIN => $users, + ALIAS => $users, + FIELD => $rest{SUBKEY}, + OPERATOR => $op, + VALUE => $value, + CASESENSITIVE => 0, + ); + $self->_SQLLimit( + %rest, + ALIAS => $users, + FIELD => 'id', + OPERATOR => 'IS', + VALUE => 'NULL', + ); + } + } else { + my $group_members = $self->_GroupMembersJoin( + GroupsAlias => $groups, + New => 0, + ); + + my $users = $self->{'_sql_u_watchers_aliases'}{$group_members}; + unless ( $users ) { + $users = $self->{'_sql_u_watchers_aliases'}{$group_members} = + $self->NewAlias('Users'); + $self->SUPER::Limit( + LEFTJOIN => $group_members, + ALIAS => $group_members, + FIELD => 'MemberId', + VALUE => "$users.id", + QUOTEVALUE => 0, + ); } + + $self->_SQLLimit( + ALIAS => $users, + FIELD => $rest{SUBKEY}, + VALUE => $value, + OPERATOR => $op, + CASESENSITIVE => 0, + %rest, + ); + $self->_SQLLimit( + ENTRYAGGREGATOR => 'AND', + ALIAS => $group_members, + FIELD => 'id', + OPERATOR => 'IS NOT', + VALUE => 'NULL', + ); } $self->_CloseParen; } -=head2 _WatcherJoin - -Helper function which provides joins to a watchers table both for limits -and for ordering. - -=cut - -sub _WatcherJoin { +sub _RoleGroupsJoin { my $self = shift; - my $type = shift; - - # we cache joins chain per watcher type - # if we limit by requestor then we shouldn't join requestors again - # for sort or limit on other requestors - if ( $self->{'_watcher_join_users_alias'}{ $type || 'any' } ) { - return $self->{'_watcher_join_users_alias'}{ $type || 'any' }; - } - -# we always have watcher groups for ticket -# this join should be NORMAL -# XXX: if we change this from Join to NewAlias+Limit -# then Pg will complain because SB build wrong query. -# Query looks like "FROM (Tickets LEFT JOIN CGM ON(Groups.id = CGM.GroupId)), Groups" -# Pg doesn't like that fact that it doesn't know about Groups table yet when -# join CGM table into Tickets. Problem is in Join method which doesn't use -# ALIAS1 argument when build braces. + my %args = (New => 0, Type => '', @_); + return $self->{'_sql_role_group_aliases'}{ $args{'Type'} } + if $self->{'_sql_role_group_aliases'}{ $args{'Type'} } && !$args{'New'}; + + # XXX: this has been fixed in DBIx::SB-1.48 + # XXX: if we change this from Join to NewAlias+Limit + # then Pg and mysql 5.x will complain because SB build wrong query. + # Query looks like "FROM (Tickets LEFT JOIN CGM ON(Groups.id = CGM.GroupId)), Groups" + # Pg doesn't like that fact that it doesn't know about Groups table yet when + # join CGM table into Tickets. Problem is in Join method which doesn't use + # ALIAS1 argument when build braces. + + # we always have watcher groups for ticket, so we use INNER join my $groups = $self->Join( ALIAS1 => 'main', FIELD1 => 'id', TABLE2 => 'Groups', FIELD2 => 'Instance', - ENTRYAGGREGATOR => 'AND' + ENTRYAGGREGATOR => 'AND', ); $self->SUPER::Limit( + LEFTJOIN => $groups, ALIAS => $groups, FIELD => 'Domain', VALUE => 'RT::Ticket-Role', - ENTRYAGGREGATOR => 'AND' ); $self->SUPER::Limit( + LEFTJOIN => $groups, ALIAS => $groups, FIELD => 'Type', - VALUE => $type, - ENTRYAGGREGATOR => 'AND' - ) - if ($type); + VALUE => $args{'Type'}, + ) if $args{'Type'}; - my $groupmembers = $self->Join( - TYPE => 'LEFT', - ALIAS1 => $groups, - FIELD1 => 'id', - TABLE2 => 'CachedGroupMembers', - FIELD2 => 'GroupId' + $self->{'_sql_role_group_aliases'}{ $args{'Type'} } = $groups + unless $args{'New'}; + + return $groups; +} + +sub _GroupMembersJoin { + my $self = shift; + my %args = (New => 1, GroupsAlias => undef, @_); + + return $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } + if $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } + && !$args{'New'}; + + my $alias = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $args{'GroupsAlias'}, + FIELD1 => 'id', + TABLE2 => 'CachedGroupMembers', + FIELD2 => 'GroupId', + ENTRYAGGREGATOR => 'AND', ); + $self->{'_sql_group_members_aliases'}{ $args{'GroupsAlias'} } = $alias + unless $args{'New'}; + + return $alias; +} + +=head2 _WatcherJoin + +Helper function which provides joins to a watchers table both for limits +and for ordering. + +=cut + +sub _WatcherJoin { + my $self = shift; + my $type = shift || ''; + + + my $groups = $self->_RoleGroupsJoin( Type => $type ); + my $group_members = $self->_GroupMembersJoin( GroupsAlias => $groups ); # XXX: work around, we must hide groups that # are members of the role group we search in, # otherwise them result in wrong NULLs in Users @@ -918,21 +1058,21 @@ sub _WatcherJoin { # RT doesn't allow to add groups as members of the # ticket roles, so we just hide entries in CGM table # with MemberId == GroupId from results - my $groupmembers = $self->SUPER::Limit( - LEFTJOIN => $groupmembers, + $self->SUPER::Limit( + LEFTJOIN => $group_members, FIELD => 'GroupId', OPERATOR => '!=', - VALUE => "$groupmembers.MemberId", + VALUE => "$group_members.MemberId", QUOTEVALUE => 0, ); my $users = $self->Join( - TYPE => 'LEFT', - ALIAS1 => $groupmembers, - FIELD1 => 'MemberId', - TABLE2 => 'Users', - FIELD2 => 'id' + TYPE => 'LEFT', + ALIAS1 => $group_members, + FIELD1 => 'MemberId', + TABLE2 => 'Users', + FIELD2 => 'id', ); - return $self->{'_watcher_join_users_alias'}{ $type || 'any' } = $users; + return ($groups, $group_members, $users); } =head2 _WatcherMembershipLimit @@ -1028,7 +1168,7 @@ sub _WatcherMembershipLimit { # }}} # If we care about which sort of watcher - my $meta = $FIELDS{$field}; + my $meta = $FIELD_METADATA{$field}; my $type = ( defined $meta->[1] ? $meta->[1] : undef ); if ($type) { @@ -1141,122 +1281,175 @@ sub _LinkFieldLimit { } } -=head2 KeywordLimit -Limit based on Keywords +=head2 _CustomFieldDecipher -Meta Data: - none +Try and turn a CF descriptor into (cfid, cfname) object pair. =cut -sub _CustomFieldLimit { - my ( $self, $_field, $op, $value, @rest ) = @_; - - my %rest = @rest; - my $field = $rest{SUBKEY} || die "No field specified"; - - # For our sanity, we can only limit on one queue at a time +sub _CustomFieldDecipher { + my ($self, $field) = @_; + my $queue = 0; - if ( $field =~ /^(.+?)\.{(.+)}$/ ) { - $queue = $1; - $field = $2; + ($queue, $field) = ($1, $2); } $field = $1 if $field =~ /^{(.+)}$/; # trim { } - # If we're trying to find custom fields that don't match something, we - # want tickets where the custom field has no value at all. Note that - # we explicitly don't include the "IS NULL" case, since we would - # otherwise end up with a redundant clause. - - my $null_columns_ok; - if ( ( $op =~ /^NOT LIKE$/i ) or ( $op eq '!=' ) ) { - $null_columns_ok = 1; - } - - my $cfid = 0; - if ($queue) { - + my $cfid; + if ( $queue ) { my $q = RT::Queue->new( $self->CurrentUser ); - $q->Load($queue) if ($queue); + $q->Load( $queue ) if $queue; my $cf; if ( $q->id ) { - $cf = $q->CustomField($field); + # $queue = $q->Name; # should we normalize the queue? + $cf = $q->CustomField( $field ); } else { $cf = RT::CustomField->new( $self->CurrentUser ); - $cf->LoadByNameAndQueue( Queue => '0', Name => $field ); + $cf->LoadByNameAndQueue( Queue => 0, Name => $field ); } - - $cfid = $cf->id; - + $cfid = $cf->id if $cf; } + + return ($queue, $field, $cfid); + +} + +=head2 _CustomFieldJoin - my $TicketCFs; - my $cfkey = $cfid ? $cfid : "$queue.$field"; +Factor out the Join of custom fields so we can use it for sorting too + +=cut +sub _CustomFieldJoin { + my ($self, $cfkey, $cfid, $field) = @_; # Perform one Join per CustomField - if ( $self->{_sql_object_cf_alias}{$cfkey} ) { - $TicketCFs = $self->{_sql_object_cf_alias}{$cfkey}; + if ( $self->{_sql_object_cfv_alias}{$cfkey} || + $self->{_sql_cf_alias}{$cfkey} ) + { + return ( $self->{_sql_object_cfv_alias}{$cfkey}, + $self->{_sql_cf_alias}{$cfkey} ); } - else { - if ($cfid) { - $TicketCFs = $self->{_sql_object_cf_alias}{$cfkey} = $self->Join( - TYPE => 'left', - ALIAS1 => 'main', - FIELD1 => 'id', - TABLE2 => 'ObjectCustomFieldValues', - FIELD2 => 'ObjectId', - ); - $self->SUPER::Limit( - LEFTJOIN => $TicketCFs, - FIELD => 'CustomField', - VALUE => $cfid, - ENTRYAGGREGATOR => 'AND' - ); - } - else { - my $cfalias = $self->Join( - TYPE => 'left', - EXPRESSION => "'$field'", - TABLE2 => 'CustomFields', - FIELD2 => 'Name', - ); - $TicketCFs = $self->{_sql_object_cf_alias}{$cfkey} = $self->Join( - TYPE => 'left', - ALIAS1 => $cfalias, - FIELD1 => 'id', - TABLE2 => 'ObjectCustomFieldValues', - FIELD2 => 'CustomField', - ); - $self->SUPER::Limit( - LEFTJOIN => $TicketCFs, - FIELD => 'ObjectId', - VALUE => 'main.id', - QUOTEVALUE => 0, - ENTRYAGGREGATOR => 'AND', - ); - } + my ($TicketCFs, $CFs); + if ( $cfid ) { + $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join( + TYPE => 'left', + ALIAS1 => 'main', + FIELD1 => 'id', + TABLE2 => 'ObjectCustomFieldValues', + FIELD2 => 'ObjectId', + ); $self->SUPER::Limit( - LEFTJOIN => $TicketCFs, - FIELD => 'ObjectType', - VALUE => ref( $self->NewItem ) - , # we want a single item, not a collection + LEFTJOIN => $TicketCFs, + FIELD => 'CustomField', + VALUE => $cfid, ENTRYAGGREGATOR => 'AND' ); + } + else { + my $ocfalias = $self->Join( + TYPE => 'LEFT', + FIELD1 => 'Queue', + TABLE2 => 'ObjectCustomFields', + FIELD2 => 'ObjectId', + ); + $self->SUPER::Limit( - LEFTJOIN => $TicketCFs, - FIELD => 'Disabled', - OPERATOR => '=', + LEFTJOIN => $ocfalias, + ENTRYAGGREGATOR => 'OR', + FIELD => 'ObjectId', VALUE => '0', - ENTRYAGGREGATOR => 'AND' + ); + + $CFs = $self->{_sql_cf_alias}{$cfkey} = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $ocfalias, + FIELD1 => 'CustomField', + TABLE2 => 'CustomFields', + FIELD2 => 'id', + ); + + $TicketCFs = $self->{_sql_object_cfv_alias}{$cfkey} = $self->Join( + TYPE => 'left', + ALIAS1 => $CFs, + FIELD1 => 'id', + TABLE2 => 'ObjectCustomFieldValues', + FIELD2 => 'CustomField', + ); + $self->SUPER::Limit( + LEFTJOIN => $TicketCFs, + FIELD => 'ObjectId', + VALUE => 'main.id', + QUOTEVALUE => 0, + ENTRYAGGREGATOR => 'AND', + ); + } + $self->SUPER::Limit( + LEFTJOIN => $TicketCFs, + FIELD => 'ObjectType', + VALUE => 'RT::Ticket', + ENTRYAGGREGATOR => 'AND' + ); + $self->SUPER::Limit( + LEFTJOIN => $TicketCFs, + FIELD => 'Disabled', + OPERATOR => '=', + VALUE => '0', + ENTRYAGGREGATOR => 'AND' + ); + + return ($TicketCFs, $CFs); +} + +=head2 _CustomFieldLimit + +Limit based on CustomFields + +Meta Data: + none + +=cut + +sub _CustomFieldLimit { + my ( $self, $_field, $op, $value, @rest ) = @_; + + my %rest = @rest; + my $field = $rest{SUBKEY} || die "No field specified"; + + # For our sanity, we can only limit on one queue at a time + + my ($queue, $cfid); + ($queue, $field, $cfid ) = $self->_CustomFieldDecipher( $field ); + +# If we're trying to find custom fields that don't match something, we +# want tickets where the custom field has no value at all. Note that +# we explicitly don't include the "IS NULL" case, since we would +# otherwise end up with a redundant clause. + + my $null_columns_ok; + if ( ( $op =~ /^NOT LIKE$/i ) or ( $op eq '!=' ) ) { + $null_columns_ok = 1; + } + + my $cfkey = $cfid ? $cfid : "$queue.$field"; + my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field ); + + $self->_OpenParen; + + if ( $CFs ) { + $self->SUPER::Limit( + ALIAS => $CFs, + FIELD => 'Name', + VALUE => $field, + ENTRYAGGREGATOR => 'AND', ); } - $self->_OpenParen if ($null_columns_ok); + $self->_OpenParen if $null_columns_ok; $self->_SQLLimit( ALIAS => $TicketCFs, @@ -1276,8 +1469,10 @@ sub _CustomFieldLimit { QUOTEVALUE => 0, ENTRYAGGREGATOR => 'OR', ); + $self->_CloseParen; } - $self->_CloseParen if ($null_columns_ok); + + $self->_CloseParen; } @@ -1285,6 +1480,99 @@ sub _CustomFieldLimit { # End of SQL Stuff ------------------------------------------------- +# {{{ Allow sorting on watchers + +=head2 OrderByCols ARRAY + +A modified version of the OrderBy method which automatically joins where +C is set to the name of a watcher type. + +=cut + +sub OrderByCols { + my $self = shift; + my @args = @_; + my $clause; + my @res = (); + my $order = 0; + + foreach my $row (@args) { + if ( $row->{ALIAS} || $row->{FIELD} !~ /\./ ) { + push @res, $row; + next; + } + my ( $field, $subkey ) = split /\./, $row->{FIELD}, 2; + my $meta = $self->FIELDS->{$field}; + if ( $meta->[0] eq 'WATCHERFIELD' ) { + # cache alias as we want to use one alias per watcher type for sorting + my $users = $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] }; + unless ( $users ) { + $self->{_sql_u_watchers_alias_for_sort}{ $meta->[1] } + = $users = ( $self->_WatcherJoin( $meta->[1] ) )[2]; + } + push @res, { %$row, ALIAS => $users, FIELD => $subkey }; + } elsif ( $meta->[0] eq 'CUSTOMFIELD' ) { + my ($queue, $field, $cfid ) = $self->_CustomFieldDecipher( $subkey ); + my $cfkey = $cfid ? $cfid : "$queue.$field"; + my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field ); + unless ($cfid) { + # For those cases where we are doing a join against the + # CF name, and don't have a CFid, use Unique to make sure + # we don't show duplicate tickets. NOTE: I'm pretty sure + # this will stay mixed in for the life of the + # class/package, and not just for the life of the object. + # Potential performance issue. + require DBIx::SearchBuilder::Unique; + DBIx::SearchBuilder::Unique->import; + } + my $CFvs = $self->Join( + TYPE => 'left', + ALIAS1 => $TicketCFs, + FIELD1 => 'CustomField', + TABLE2 => 'CustomFieldValues', + FIELD2 => 'CustomField', + ); + $self->SUPER::Limit( + LEFTJOIN => $CFvs, + FIELD => 'Name', + QUOTEVALUE => 0, + VALUE => $TicketCFs . ".Content", + ENTRYAGGREGATOR => 'AND' + ); + + push @res, { %$row, ALIAS => $CFvs, FIELD => 'SortOrder' }; + push @res, { %$row, ALIAS => $TicketCFs, FIELD => 'Content' }; + } elsif ( $field eq "Custom" && $subkey eq "Ownership") { + # PAW logic is "reversed" + my $order = "ASC"; + if (exists $row->{ORDER} ) { + my $o = $row->{ORDER}; + delete $row->{ORDER}; + $order = "DESC" if $o =~ /asc/i; + } + + # Unowned + # Else + + # Ticket.Owner 1 0 0 + my $ownerId = $self->CurrentUser->Id; + push @res, { %$row, FIELD => "Owner=$ownerId", ORDER => $order } ; + + # Unowned Tickets 0 1 0 + my $nobodyId = $RT::Nobody->Id; + push @res, { %$row, FIELD => "Owner=$nobodyId", ORDER => $order } ; + + push @res, { %$row, FIELD => "Priority", ORDER => $order } ; + } + else { + push @res, $row; + } + } + return $self->SUPER::OrderByCols(@res); +} + +# }}} + # {{{ Limit the result set based on content # {{{ sub Limit @@ -1313,7 +1601,7 @@ sub Limit { my $index = $self->_NextIndex; -#make the TicketRestrictions hash the equivalent of whatever we just passed in; +# make the TicketRestrictions hash the equivalent of whatever we just passed in; %{ $self->{'TicketRestrictions'}{$index} } = %args; @@ -1413,12 +1701,11 @@ sub LimitQueue { @_ ); - #TODO VALUE should also take queue names and queue objects - #TODO FIXME why are we canonicalizing to name, not id, robrt? - if ( $args{VALUE} =~ /^\d+$/ ) { + #TODO VALUE should also take queue objects + if ( defined $args{'VALUE'} && $args{'VALUE'} !~ /^\d+$/ ) { my $queue = new RT::Queue( $self->CurrentUser ); $queue->Load( $args{'VALUE'} ); - $args{VALUE} = $queue->Name; + $args{'VALUE'} = $queue->Id; } # What if they pass in an Id? Check for isNum() and convert to @@ -1428,10 +1715,10 @@ sub LimitQueue { $self->Limit( FIELD => 'Queue', - VALUE => $args{VALUE}, + VALUE => $args{'VALUE'}, OPERATOR => $args{'OPERATOR'}, DESCRIPTION => join( - ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{VALUE}, + ' ', $self->loc('Queue'), $args{'OPERATOR'}, $args{'VALUE'}, ), ); @@ -1911,9 +2198,10 @@ TARGET is the id or URI of the TARGET of the link sub LimitLinkedTo { my $self = shift; my %args = ( - TICKET => undef, - TARGET => undef, - TYPE => undef, + TICKET => undef, + TARGET => undef, + TYPE => undef, + OPERATOR => '=', @_ ); @@ -1927,6 +2215,7 @@ sub LimitLinkedTo { $self->loc( $args{'TYPE'} ), ( $args{'TARGET'} || $args{'TICKET'} ) ), + OPERATOR => $args{'OPERATOR'}, ); } @@ -1949,9 +2238,10 @@ BASE is the id or URI of the BASE of the link sub LimitLinkedFrom { my $self = shift; my %args = ( - BASE => undef, - TICKET => undef, - TYPE => undef, + BASE => undef, + TICKET => undef, + TYPE => undef, + OPERATOR => '=', @_ ); @@ -1973,6 +2263,7 @@ sub LimitLinkedFrom { $self->loc( $args{'TYPE'} ), ( $args{'BASE'} || $args{'TICKET'} ) ), + OPERATOR => $args{'OPERATOR'}, ); } @@ -1982,11 +2273,11 @@ sub LimitLinkedFrom { sub LimitMemberOf { my $self = shift; my $ticket_id = shift; - $self->LimitLinkedTo( - TARGET => "$ticket_id", + return $self->LimitLinkedTo( + @_, + TARGET => $ticket_id, TYPE => 'MemberOf', ); - } # }}} @@ -1995,7 +2286,8 @@ sub LimitMemberOf { sub LimitHasMember { my $self = shift; my $ticket_id = shift; - $self->LimitLinkedFrom( + return $self->LimitLinkedFrom( + @_, BASE => "$ticket_id", TYPE => 'HasMember', ); @@ -2009,8 +2301,9 @@ sub LimitHasMember { sub LimitDependsOn { my $self = shift; my $ticket_id = shift; - $self->LimitLinkedTo( - TARGET => "$ticket_id", + return $self->LimitLinkedTo( + @_, + TARGET => $ticket_id, TYPE => 'DependsOn', ); @@ -2023,8 +2316,9 @@ sub LimitDependsOn { sub LimitDependedOnBy { my $self = shift; my $ticket_id = shift; - $self->LimitLinkedFrom( - BASE => "$ticket_id", + return $self->LimitLinkedFrom( + @_, + BASE => $ticket_id, TYPE => 'DependentOn', ); @@ -2037,8 +2331,9 @@ sub LimitDependedOnBy { sub LimitRefersTo { my $self = shift; my $ticket_id = shift; - $self->LimitLinkedTo( - TARGET => "$ticket_id", + return $self->LimitLinkedTo( + @_, + TARGET => $ticket_id, TYPE => 'RefersTo', ); @@ -2051,11 +2346,11 @@ sub LimitRefersTo { sub LimitReferredToBy { my $self = shift; my $ticket_id = shift; - $self->LimitLinkedFrom( - BASE => "$ticket_id", + return $self->LimitLinkedFrom( + @_, + BASE => $ticket_id, TYPE => 'ReferredToBy', ); - } # }}} @@ -2522,9 +2817,9 @@ sub _RestrictionsToClauses { #use Data::Dumper; #print Dumper($restriction),"\n"; - # We need to reimplement the subclause aggregation that SearchBuilder does. - # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main', - # Then SB AND's the different Subclauses together. + # We need to reimplement the subclause aggregation that SearchBuilder does. + # Default Subclause is ALIAS.FIELD, and default ALIAS is 'main', + # Then SB AND's the different Subclauses together. # So, we want to group things into Subclauses, convert them to # SQL, and then join them with the appropriate DefaultEA. @@ -2547,10 +2842,10 @@ sub _RestrictionsToClauses { } die "I don't know about $field yet" - unless ( exists $FIELDS{$realfield} - or $restriction->{CUSTOMFIELD} ); + unless ( exists $FIELD_METADATA{$realfield} + or $restriction->{CUSTOMFIELD} ); - my $type = $FIELDS{$realfield}->[0]; + my $type = $FIELD_METADATA{$realfield}->[0]; my $op = $restriction->{'OPERATOR'}; my $value = (