[rt-users] where is my message content?

[timuel]

Hey...

I`ve been given the task (from up above...) to write a script that regularly
fetches tickets/emails out of a certain queue, forward them to a non-RT address,
and mark the ticket as resolved with a note saying that it`s been forwarded
appropriately.

So far I`m fine with an external DBI/DBD::MySQL perl script that grabs the
ticket number of any new or open ticket in a given queue. I now need to reliably
grab the message headers and content out of the RT database. Honestly, I like RT
alot but I the database looks a bit messy _to_me_. The most unique columns that
I could find to join "Ticket" and "Attachments" on (so that I can grab the
attachments.headers and attachments.content with only the ticket number) are the
"Subject" and "Created" columns which is very very inefficient. The basic idea
is kinda like this:

$ticketid = "select ID from Tickets where Queue = 'whatever' and (status = 'new'
|| status = 'open');";
$email = "select Headers,Content from Tickets,Attachments where Tickets.Subject
= Attachments.Subject and Tickets.Created = Attachments.Created and Tickets.id =
'$ticketid';";

Can anyone think of a better way to do this? Grab an new/open ticket out of a
particular queue, mark it resolved and forward along the attachments.headers and
attachment.content? Also, i`ve noticed a few instances where the message body
isn`t in attachments.content, would somebody please enlighten me as to where my
ticket content is and exactly how everything is tied together so that RT can
display everything so impeccably yet I can`t get this seemingly simple script to
function correctly?

As always, any insight at all would be greatly appreciated.

Thank You.

--
timuel