Where are comments and correspondence held?

RT Users
1

Running RT 4.0.6. Curious where the content of comments and/or *
correspondence* resides in the database?

Max
Max McGrath
Network Administrator
Carthage College
262-552-5512
mmcgrath@carthage.edu

2

Running RT 4.0.6. Curious where the content of comments and/or
correspondence resides in the database?

In the transactions table, as I recall.

3

Dnia 2012-11-05, pon o godzinie 17:16 -0500, Jeff Blaine pisze:> On 11/5/2012 4:54 PM, Max McGrath wrote:

Running RT 4.0.6. Curious where the content of comments and/or
correspondence resides in the database?

In the transactions table, as I recall.

Actually in the attachments tabel.

Regards,
Robert Wysocki
administrator systemów linuksowych
Contium S.A., http://www.contium.pl

4

Dnia 2012-11-05, pon o godzinie 17:16 -0500, Jeff Blaine pisze:

Running RT 4.0.6. Curious where the content of comments and/or
correspondence resides in the database?

In the transactions table, as I recall.

Actually in the attachments tabel.

Yep - each Ticket has 1…n transactions in the Transactions table, and each Transaction has 0…n attachments in the Attachments table. The content of each comment or correspondence resides in the Attachments table. It’s often encoded, so it’s not always trivial to extract the content with simple SQL. For example, the following naïve query gets the attachment rows for a ticket:

SELECT a.* FROM Tickets t, Transactions tx, Attachments a
WHERE t.id = 294774
AND tx.ObjectId = t.id
AND tx.ObjectType = ‘RT::Ticket’
AND tx.id = a.TransactionId;

But generally you don’t want to do that, and instead you want to use the perl API to get at them:

my $attachments = RT::Attachments->new(RT->SystemUser);
$attachments->LimitByTicket(294774);
$attachments->LimitNotEmpty;

while (my $a = $attachments->Next) {
print $a->Content, “\n\n”;
}

Regards,

Tim

The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

5

But generally you don’t want to do that, and instead you want to use the
perl API to get at them:

my $attachments = RT::Attachments->new(RT->SystemUser);
$attachments->LimitByTicket(294774);
$attachments->LimitNotEmpty;

And if you just want Comments, say, you can do

my $attachments = RT::Attachments->new(RT->SystemUser);
$attachments->LimitByTicket(294774);
$attachments->Limit(
  ALIAS           => $attachments->TransactionAlias,
  FIELD           => 'Type',
  OPERATOR        => '=',
  VALUE           => 'Comment',
  ENTRYAGGREGATOR => 'OR',
  CASESENSITIVE   => 1
);

while (my $attachment = $attachments->Next)
{

(If there’s an easier way to do that, please let me know)

http://www.linkedin.com/in/paultomblin
http://careers.stackoverflow.com/ptomblin

6

Thanks all!

Very helpful!
Max McGrath
Network Administrator
Carthage College
262-552-5512
mmcgrath@carthage.eduOn Tue, Nov 6, 2012 at 6:24 AM, Paul Tomblin ptomblin@xcski.com wrote:

On Tue, Nov 6, 2012 at 4:58 AM, Tim Cutts tjrc@sanger.ac.uk wrote:

But generally you don’t want to do that, and instead you want to use the
perl API to get at them:

my $attachments = RT::Attachments->new(RT->SystemUser);
$attachments->LimitByTicket(294774);
$attachments->LimitNotEmpty;

And if you just want Comments, say, you can do

my $attachments = RT::Attachments->new(RT->SystemUser);
$attachments->LimitByTicket(294774);
$attachments->Limit(
   ALIAS           => $attachments->TransactionAlias,
  FIELD           => 'Type',
  OPERATOR        => '=',
  VALUE           => 'Comment',
  ENTRYAGGREGATOR => 'OR',
  CASESENSITIVE   => 1
);

while (my $attachment = $attachments->Next)
{

(If there’s an easier way to do that, please let me know)

–
http://www.linkedin.com/in/paultomblin
http://careers.stackoverflow.com/ptomblin

We’re hiring! Careers — Best Practical Solutions