[rt-users] Where are comments and correspondence held?

[Tim Cutts]

On 6 Nov 2012, at 06:29, Robert Wysocki <robert.wysocki at contium.pl> wrote:

> 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.

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.