New index on groupmembers table

Joby_Walker · June 22, 2007, 7:03pm

I’ve noticed some significant improvement by adding the following index:

CREATE INDEX groupmembers1 on groupmembers (groupid, memberid);

Currently the groupmembers table is not indexed (apart from the PK), and
there are several operations that make use of the groupmembers table and
not the cachedgroupmembers table. For example (in the stock RT) on
every page load RT checks to see if the currentuser is Privileged.

Joby Walker
C&C SSG, University of Washington

Viktor · June 22, 2007, 8:05pm

Hello Joby,

do you use RTx::Shredder extension? It adds some useful indexes, like
“shredder_gm1” UNIQUE, btree (memberid, groupid)
in PostgreSQL.
RTx::Shredder is a must, please be sure to try it!-----Original Message-----
From: Joby Walker joby@u.washington.edu
To: rt-devel@lists.bestpractical.com
Date: Fri, 22 Jun 2007 12:03:30 -0700
Subject: [Rt-devel] New index on groupmembers table

I’ve noticed some significant improvement by adding the following index:

CREATE INDEX groupmembers1 on groupmembers (groupid, memberid);

Currently the groupmembers table is not indexed (apart from the PK), and
there are several operations that make use of the groupmembers table and
not the cachedgroupmembers table. For example (in the stock RT) on
every page load RT checks to see if the currentuser is Privileged.

–

Joby Walker
C&C SSG, University of Washington

List info: The rt-devel Archives

Joby_Walker · June 22, 2007, 8:08pm

We haven’t updated RTx::Shredder in quite a while…I’ll check out the
most recent version.

Joby Walker
C&C SSG, University of Washington

Viktor wrote:

Jesse_Vincent · July 3, 2007, 4:32pm

I’ve noticed some significant improvement by adding the following
index:

CREATE INDEX groupmembers1 on groupmembers (groupid, memberid);

Currently the groupmembers table is not indexed (apart from the
PK), and
there are several operations that make use of the groupmembers
table and
not the cachedgroupmembers table. For example (in the stock RT) on
every page load RT checks to see if the currentuser is Privileged.

Thanks. I’ve created a ticket to investigate adding this for RT 3.8.

Best,
Jesse

PGP.sig (186 Bytes)

Torsten_Brumm · July 4, 2007, 6:36am

Hi Jesse, Joby,

just found at an older rt 3.6.0 installation, this index is already there:

TorstenVon: rt-devel-bounces@lists.bestpractical.com [mailto:rt-devel-bounces@lists.bestpractical.com] Im Auftrag von Jesse Vincent
Gesendet: Dienstag, 3. Juli 2007 18:32
An: Joby Walker
Cc: rt-devel@lists.bestpractical.com
Betreff: Re: [Rt-devel] New index on groupmembers table

I’ve noticed some significant improvement by adding the following
index:

CREATE INDEX groupmembers1 on groupmembers (groupid, memberid);

Currently the groupmembers table is not indexed (apart from the
PK), and
there are several operations that make use of the groupmembers
table and
not the cachedgroupmembers table. For example (in the stock RT) on
every page load RT checks to see if the currentuser is Privileged.

Thanks. I’ve created a ticket to investigate adding this for RT 3.8.

Best,
Jesse

–

Joby Walker
C&C SSG, University of Washington

List info: lists.bestpractical.com Mailing Lists
rt-devel

Jesse_Vincent · July 4, 2007, 10:52am

Hi Jesse, Joby,

just found at an older rt 3.6.0 installation, this index is already
there:

CREATE UNIQUE INDEX GroupMembers1 on GroupMembers (GroupId, MemberId);

Indeed it is.
Thanks,
Jesse

PGP.sig (186 Bytes)

Anders_Ekstrand · August 28, 2007, 2:35pm

Hi all,

I was up to using a scrip to automatically set my custom field “Response
Time” to a proper value, when sending the first response to a customer
in a ticket.

I found nothing on the RT Wiki that I could use, so I installed
Business::Hours and Set::IntSpan and tried the following scrip on my RT
3.6.5RC2 installation:

use Business::Hours;

my $BusinessHours = Business::Hours->new();
my $Ticket = $self->TicketObj;
my $cfResponseTime = new RT::CustomField( $RT::SystemUser );
my $responseTime = $Ticket->FirstCustomFieldValue( ‘Response Time’ );

if ( !$responseTime ) {

my $createdTime = $Ticket->CreatedObj->Unix;
my $responseTime = $BusinessHours->between( $createdTime, time() );

$cfResponseTime->LoadByNameAndQueue ( Name=>‘Response Time’, Queue=>0
);
$Ticket->AddCustomFieldValue( Field => $cfResponseTime, Value =>
$responseTime );
}

return(1);

Unfortunately, the “between” method only returns -1 for me…

Anybody that has a hint regarding what I could have missed here?

Best Regards - Anders Ekstrand

Richard_Fojta · August 28, 2007, 3:52pm

Hello,
documentation says

between START, END

Returns the number of business seconds between START and END Both
Start and End should be specified in Seconds since the Epoch

Returns -1 if Start or End is outside the calculated business hours

See Business::Hours - Calculate business hours in a time period - metacpan.org
and use method business_hours. Documenation is copied beneath.

Set the business_hours.
for this Business::Hours object. Takes a hash of the form :

{ 0 => { Name => ‘Sunday’, Start => ‘HH:MM’, End => ‘HH:MM’},

1 => { Name => 'Monday',
        Start => 'HH:MM',
           End => 'HH:MM'},
....

6 => { Name => 'Saturday',
        Start => 'HH:MM',
           End => 'HH:MM'},
};

Start and end times are of the form HH:MM.  Valid times are
from 00:00 to 23:59.  If your hours are from 9am to 6pm, use
Start => '9:00', End => '18:00'.  A given day MUST have a start
and end time OR may declare both Start and End to be undef, if
there are no valid hours on that day.

Note that the ending time is really "what is the first minute we're closed.
If you specifiy an "End" of 18:00, that means that at 6pm, you are closed.
The last business second was 17:59:59. \

Greetings.
Richard FojtaOn 8/28/07, Anders Ekstrand Anders.Ekstrand@drutt.com wrote:

Hi all,

I was up to using a scrip to automatically set my custom field “Response
Time” to a proper value, when sending the first response to a customer
in a ticket.

I found nothing on the RT Wiki that I could use, so I installed
Business::Hours and Set::IntSpan and tried the following scrip on my RT
3.6.5RC2 installation:

use Business::Hours;

my $BusinessHours = Business::Hours->new();
my $Ticket = $self->TicketObj;
my $cfResponseTime = new RT::CustomField( $RT::SystemUser );
my $responseTime = $Ticket->FirstCustomFieldValue( ‘Response Time’ );

if ( !$responseTime ) {

my $createdTime = $Ticket->CreatedObj->Unix;
my $responseTime = $BusinessHours->between( $createdTime, time() );

$cfResponseTime->LoadByNameAndQueue ( Name=>‘Response Time’, Queue=>0
);
$Ticket->AddCustomFieldValue( Field => $cfResponseTime, Value =>
$responseTime );
}

return(1);

Unfortunately, the “between” method only returns -1 for me…

Anybody that has a hint regarding what I could have missed here?

Best Regards - Anders Ekstrand

List info: The rt-devel Archives

Anders_Ekstrand · August 30, 2007, 9:58am

Hi,

and many thanks for your reply.

I still can’t get it to work, unfortunately.

I tried it as simple as possible, and it came down to a stand-alone perl
script as below:

— cut here ------ cut here ------ cut here ------ cut here —
use Business::Hours;

my $BusinessHours = Business::Hours->new();

$BusinessHours->business_hours(
0 => { Name => ‘Sunday’, Start => ‘7:00’, End => ‘16:00’
},
1 => { Name => ‘Monday’, Start => ‘9:00’, End => ‘18:00’
},
2 => { Name => ‘Tuesday’, Start => ‘9:00’, End => ‘18:00’
},
3 => { Name => ‘Wednesday’, Start => ‘9:00’, End => ‘18:00’
},
4 => { Name => ‘Thursday’, Start => ‘9:00’, End => ‘18:00’
},
5 => { Name => ‘Friday’, Start => ‘9:00’, End => ‘18:00’
},
6 => { Name => ‘Saturday’, Start => ‘8:00’, End => ‘17:00’ }
);

print $BusinessHours->between( time + 3600, time + 7200 );
print “\n”;
— cut here ------ cut here ------ cut here ------ cut here —

This one also returns -1…

I added a few debug lines into the ‘between’ sub in ‘Hours.pm’, to see
what happened:

— cut here ------ cut here ------ cut here ------ cut here —
sub between {
my $self = shift;
my $start = shift;
my $end = shift;

if ($start < $self->{'start'}) {
  print "DEBUG1: start=".$start.",

self->start=“.$self->{‘start’}.”\n";
return (-1);
}
if ($end > $self->{‘end’}) {
print “DEBUG2: end=”.$end.“, self->end=”.$self->{‘end’}.“\n”;
return(-1);
}

my $period = Set::IntSpan->new($start."-".$end);
my $intersection = intersect $period $self->{'calculated'};

return cardinality $intersection;

}
— cut here ------ cut here ------ cut here ------ cut here —

The output then is:
DEBUG2: end=1188474497, self->end=
-1

I also get the same result if not defining my business hours hash at
all, which should make Business::Hours to use the default value.

It seems like the issue lies deeper

Best Regards - Anders

-----Original Message-----
From: Richard Fojta [mailto:rfojta@gmail.com]
Sent: den 28 augusti 2007 17:53
To: Anders Ekstrand
Cc: rt-devel@lists.bestpractical.com
Subject: Re: [Rt-devel] Using Business::Hours to set the CF Response
Time

Hello,
documentation says

between START, END

Returns the number of business seconds between START and END Both
Start and End should be specified in Seconds since the Epoch

Returns -1 if Start or End is outside the calculated business hours

See Business::Hours - Calculate business hours in a time period - metacpan.org
and use method business_hours. Documenation is copied beneath.

Set the business_hours.
for this Business::Hours object. Takes a hash of the form :

{ 0 => { Name => ‘Sunday’, Start => ‘HH:MM’, End => ‘HH:MM’},
1 => { Name => 'Monday',
        Start => 'HH:MM',
           End => 'HH:MM'},
....

6 => { Name => 'Saturday',
        Start => 'HH:MM',
           End => 'HH:MM'},
};

Start and end times are of the form HH:MM.  Valid times are
from 00:00 to 23:59.  If your hours are from 9am to 6pm, use
Start => '9:00', End => '18:00'.  A given day MUST have a start
and end time OR may declare both Start and End to be undef, if
there are no valid hours on that day.

Note that the ending time is really "what is the first minute

we’re

closed.
If you specifiy an “End” of 18:00, that means that at 6pm, you are
closed.
The last business second was 17:59:59. \

Greetings.
Richard Fojta

Hi all,

I was up to using a scrip to automatically set my custom field
“Response
Time” to a proper value, when sending the first response to a
customer
in a ticket.

I found nothing on the RT Wiki that I could use, so I installed
Business::Hours and Set::IntSpan and tried the following scrip on my
RT
3.6.5RC2 installation:

use Business::Hours;

my $BusinessHours = Business::Hours->new();
my $Ticket = $self->TicketObj;
my $cfResponseTime = new RT::CustomField( $RT::SystemUser );
my $responseTime = $Ticket->FirstCustomFieldValue( ‘Response Time’
);

if ( !$responseTime ) {

my $createdTime = $Ticket->CreatedObj->Unix;
my $responseTime = $BusinessHours->between( $createdTime, time()
);

Anders_Ekstrand · August 30, 2007, 11:48am

Hi again,

I finally found a working solution, including two ugly workarounds:

use Business::Hours;

my $BusinessHours = Business::Hours->new();
my $Ticket = $self->TicketObj;
my $cfResponseTime = new RT::CustomField( $RT::SystemUser );
my $responseTime = $Ticket->FirstCustomFieldValue( ‘Response Time’ );
my $oneWeek = 3600 * 24 * 7;

if ( !$responseTime ) {

my $createdTime = $Ticket->CreatedObj->Unix;

This call is made to populate Business::Hour’s array of business

days.
$BusinessHours->add_seconds( time, $oneWeek );

The addition of 1 week is made because Business:Hours doesn’t handle

times in the past.
my $passedBusinessMinutes = int( $BusinessHours->between(
$Ticket->CreatedObj->Unix + $oneWeek, time + $oneWeek ) / 60 );

$cfResponseTime->LoadByNameAndQueue ( Name=>‘Response Time’, Queue=>0
);
$Ticket->AddCustomFieldValue( Field => $cfResponseTime, Value =>
$passedBusinessMinutes );
}

return(1);

Best Regards - Anders