Rt2Mbox

Rt2Mbox

This script connects to the RT database and exports all of the mail into mbox format. An mbox file is created for each queue in the current directory. It does a little bit of subject tweaking to try to encourage good threading habits, and the results imported into Mail.app very nicely. This script also extremely fast, since it doesn't use the RT API.

A to how to use it, well just run it and it will tell you how. :-)

Prerequisites:

• DBI
• Your DBD (DBD::mysql, DBD::Pg, etc.).
• Mail::Box perl-module
• POSIX (comes with Perl)
• Getopt::Long (comes with Perl)

Caveat:

• I've tested this with my PostgreSQL database and added code so that it should also work with MySQL, but all other databases might have to tweak the $epoch variable.

#!/usr/bin/perl -w
 
 use strict;
 use DBI;
 use Getopt::Long qw(:config auto_help auto_version);
 use POSIX qw(asctime);
 our $VERSION = '0.01';
 
 GetOptions(
     'dsn|d=s'          => \my $dsn,
     'username|u=s'     => \my $username,
     'password|p=s'     => \my $password,
     'rtname|n=s'       => \my $rtname,
     'no-autoreplies|x' => \my $noauto,
 )  or require Pod::Usage && Pod::Usage::pod2usage(2);
 
 require Pod::Usage && Pod::Usage::pod2usage(1) unless $dsn && $rtname;
 
 my $dbh = DBI->connect(
     $dsn,
     $username,
     $password,
     { RaiseError => 1 }
 );
 
 END {
     $dbh->disconnect if $dbh;
 }
 
 my $qsel = $dbh->prepare(q{
     SELECT id, name, correspondaddress, commentaddress
       FROM queues
      ORDER BY name
 });
 
 
 my $exclude = $noauto ? "\n       AND users.id <> 1" : '';
 my $epoch   = $dsn =~ /^dbi:Pg/
     ? 'EXTRACT( EPOCH FROM attachments.created )'
     : 'UNIX_TIMESTAMP( attachments.created )';
 
 my $msel = $dbh->prepare(qq{
     SELECT tickets.id, transactions.id, transactions.type, attachments.id,
            parent, users.emailaddress,
            contenttype, COALESCE( contentencoding, '' ),
            $epoch,
            headers, content, tickets.subject
       FROM tickets, transactions, attachments, users
      WHERE tickets.queue = ?
        AND tickets.id = transactions.objectid
        AND transactions.objecttype = ?
        AND transactions.id = attachments.transactionid
        AND transactions.creator = users.id$exclude
      ORDER BY transactions.id, attachments.id, parent
 });
 
 $qsel->execute;
 $qsel->bind_columns( \my ( $qid, $qname, $correspond, $comment ) );
 
 while ($qsel->fetch) {
     # Normalize the name.
     $qname =~ s{[/.]}{}g;
 
     # Open the mbox file.
     open my $mbox, '>', "$qname.mbox" or die "Cannot open $qname.mbox: $!\n";
     $msel->execute( $qid, 'RT::Ticket' );
     $msel->bind_columns(\my ( $tid, $txid, $ttype, $aid, $parent, $email, $type, $encoding, $time, $headers, $body, $subject));
 
     my @parents;
     my @bounds;
     while ($msel->fetch) {
         if (!$parent) {
             # Top level of a message. Close the previous message.
             @parents = ();
             while (my $bound = pop @bounds) {
                 print $mbox "--$bound--\n\n";
             }
 
             # Start the new message.
             ( my $asctime = asctime( gmtime $time ) ) =~ s/\s+$//ms;
             $email ||= $ttype =~ /comment/i ? $comment : $correspond;
             print $mbox "From $email $asctime\n";
 
             # Make sure that the body ends in a blank line.
             $body =~ s/(\n{0,2})?$/\n\n/ if $body;
 
             # Adjust headers. They always end with a single newline.
             $headers .= "RT-Transaction-Id: $txid\n";
             if ( $headers !~ /^From:/mi ) {
                 $headers .= "From: $email\n";
             }
             if ( $headers !~ /^Subject:/mi ) {
                 $headers .= "Subject: [$rtname #$tid] $subject\n";
             } else {
                 $headers =~ s/^(Subject:\s*(?:(?:Re|Fwd):\s*)?)/$1\[$rtname #$tid] /mi
                     unless $headers =~ /^Subject:\s*(?:(?:Re|Fwd):\s*)?[[]\Q$rtname\E/mi
             }
 
         } else {
             # Close any parents.
             while (@parents && $parents[-1] != $parent) {
                 pop @parents;
                 my $bound = pop @bounds;
                 print $mbox "--$bound--\n\n";
             }
         }
 
         # Add the Attachment ID to the headers, so simplify backtracking.
         $headers .= "RT-Attachment-Id: $aid\n";
 
         # Handle this part.
         if ($type =~ m{^multipart/}) {
             # Part is multipart. Push the parent ID onto the stack.
             push @parents, $aid;
 
             # Determine the bounary.
             my $bound;
             if ($headers =~ qr{\bboundary=(.+)}mi) {
                 # Retain the original boundary.
                 ($bound = $1) =~ s/^"//;
                 $bound =~ s/"$//;
             } else {
                 # Just invent a boundary.
                 $bound = join '', ('a'..'z', 'A'..'Z', 0..9)[ map { rand 62 } 0..10];
             }
 
             # Output the part.
             print $mbox "--$bounds[-1]\n" if @bounds;
             print $mbox "$headers\n";
             print $mbox $body if $body;
 
             # Push the boundary onto the stack.
             push @bounds, $bound;
         } else {
             # Is a standalone part.
             print $mbox "--$bounds[-1]\n" if @bounds;
             print $mbox "$headers\n";
             print $mbox $body if $body;
         }
     }
     close $mbox or die "Cannot close $qname.mbox: $!\n";
 }
 
 1;
 __END__
 
 =head1 Name
 
 rt2mbox - Export RT messages to mbox files, one for each queue
 
 =head1 Synopsis
 
 =begin comment
 
 Fake-out Pod::Usage
 
 =head1 SYNOPSIS
 
 =end comment
 
   rt2mbox --dsn dbi:Pgdbname=rt3 --username rt_user --password secret
 
 =head1 Options
 
   -d --dsn DSN            RT database DSN to which DBI can connect. Required.
   -n --rtname   NAME      RT name, usually domain name. Required.
   -u --username USERNAME  RT database username.
   -p --password PASSWORD  RT database password.
   -x --no-autoreplies     Do not export autoreply messages.
 
 =head1 Author
 
 David E. Wheeler <david@kineticode.com>
 
 =head1 Copyright and License
 
 Copyright (c) 2008 David E. Wheeler. All Rights Reserved.
 
 This module is free software; you can redistribute it and/or modify it under
 the same terms as Perl itself.
 
 =cut
 
 

User Notes To get this to work for mysql in rt 3.8.2 I had to change all the references to tables in the script to start with capitals eg "From queues" changed to From Queues otherwise I got errors saying the tables didn't exist.