[maildb-cvs] cvs commit: maildb/libmaildb/db/mysql/doc import_mbox.pl index_cat.pl

[Jeff S. <jsq...@us...>]

jsquyres    04/05/30 04:58:18

  Added:       libmaildb/db/mysql/doc import_mbox.pl index_cat.pl
  Log:
  Two prototype scripts (require perl CPAN module Mail::Box) to experiment
  with the database schema.

Revision  Changes    Path
1.1                  maildb/libmaildb/db/mysql/doc/import_mbox.pl

Index: import_mbox.pl
===================================================================
#!/usr/bin/env perl

use strict;
use Mail::Box::Manager;
use Mail::Address;
use Data::Dumper;
use File::Temp qw/tempfile/;
use File::Basename qw/basename/;
use File::Path;

# Use "require" because fink puts things in odd places

my $mac_dir = "/sw/lib/perl5/5.8.1/darwin-thread-multi-2level";
push(@INC, $mac_dir)
    if (-d $mac_dir);
use DBI;

# Sanity check

die "Nothing to do!" 
    if ($#ARGV < 0);

# Globals

my $dsn = "DBI:mysql:database=maildb;host=localhost";
my $dbh;
my $db_name = "maildb";
my $maildb_uid;
my $maildb_username;
my $cat_id;
my $inbox_cat_id;
my $config;
my $empty;
my $want_debug = 0;
my $total_imported_folders = 0;
my $total_imported_messages = 0;
my $total_imported_messages_incl_embedded = 0;

my $msg_dir_default = "/var/spool/maildb";
my $max_short_body_length_default = 1048570;

my $FLAG_VALID = 0x1;
my $FLAG_HAS_CHILDREN = 0x2;
my $FLAG_DELETED = 0x4;
my $FLAG_DRAFT = 0x8;
my $FLAG_FLAGGED = 0x16;
my $FLAG_NEW = 0x32;
my $FLAG_REPLIED = 0x64;
my $FLAG_SEEN = 0x128;

# Do everything

connect_to_db();
set_default_config();
check_user_in_db();
make_inbox_cat();
mkdir_msg_dir();
close_db();

import_folders();

print "Stats:
Total imported folders:                       $total_imported_folders
Total imported messages:                      $total_imported_messages
Total imported messages (including embedded): $total_imported_messages_incl_embedded\n";

exit(0);

#############################################################################

#
# debugging print
#
sub debug {
    my ($str) = @_;
    print $str
        if ($want_debug);
}

#############################################################################

#
# Connect to the db
#
sub connect_to_db {
    $dbh = DBI->connect($dsn, "maildb", "maildb");
    $empty = $dbh->quote("");
}

#############################################################################

#
# Execute an SQL query and return the results
#
my $sql_results;
sub sql_select {
    my ($sql) = @_;

    # Check to ensure that the previous results were freed

    $sql_results->finish()
        if ($sql_results);

    # Now prepare and execute the new query

#    debug "Running SQL: $sql\n";
    $sql_results = $dbh->prepare($sql);
    if (!$sql_results) {
        die "Unable to prepare SQL: $sql: " . $dbh->errstr . "\n";
    }
    if (!$sql_results->execute) {
        die "Unable to execute SQL: $sql: " . $dbh->errstr . "\n";
    }

    # Done.  Return the handle to the new results.

    $sql_results;
}

sub sql_do {
    my ($sql) = @_;

    # Check to ensure that the previous results were freed

    $sql_results->finish()
        if ($sql_results);

    # Now prepare and execute the new query

#    debug "Running SQL: $sql\n";
    if (!$dbh->do($sql)) {
        die "Unable to execute SQL: $sql: " . $dbh->errstr . "\n";
    }
}

#############################################################################

sub set_default_config {
    add_config_line("msg_dir", $msg_dir_default,
                    "Location of long messages");
    add_config_line("max_short_body_length", $max_short_body_length_default,
                    "Max length in bytes of short body");
}

#############################################################################

sub add_config_line {
    my ($key, $value, $desc) = @_;

    my $sql;
    my $results;

    $sql = "SELECT cf_value FROM config WHERE cf_key=" . $dbh->quote($key);
    $results = sql_select($sql);

    # If we didn't find it, add it

    if ($results->rows == 0) {
        $results->finish();
        $sql = "INSERT INTO config VALUES (NULL, 0, " .
            $dbh->quote($key) . ", " .
            $dbh->quote($value) . ", ". 
            $dbh->quote($desc) . ")";
        sql_do($sql);

        $config->{$key} = $value;
        debug "Added default config: $key = $value\n";
    }

    # If we did find it, save the found value

    else {
        my $ref = $results->fetchrow_arrayref;
        $config->{$key} = @$ref[0];
        $results->finish();
        debug "Found config: $key = $config->{$key}\n";
    }
}

#############################################################################

#
# Check that the user is in the db
#
sub check_user_in_db {
    my ($name, $passwd, $uid, $gid,
        $quota, $comment, $gcos, $dir, $shell, $expire) = getpwuid($<);
    debug "Username: $name\n";
    $maildb_username = $name;

    # Look and see if the username is in the db

    my $results = sql_select("SELECT u_id FROM users WHERE u_username=" .
                          $dbh->quote($name));
    my $found = $results->rows();

    # Add if if it's not

    if ($found == 0) {
        $results->finish();
        sql_do("INSERT INTO users VALUES (NULL, " .
               $dbh->quote($name) . ", " . $dbh->quote($gcos) . ")");
        debug "Added user to maildb\n";
        $maildb_uid = $dbh->{'mysql_insertid'};
    } elsif ($found == 1) {
        debug "User already in maildb\n";
        while (my $ref = $results->fetchrow_arrayref) {
            $maildb_uid = @$ref[0];
        }
        $results->finish();
    } else {
        die "Problem!  User in DB multiple times!\n";
    }

    debug "User has maildb UID: $maildb_uid\n";

    # All done

    endpwent();
}

#############################################################################

#
# Make sure the special "INBOX" category exists
#
sub make_inbox_cat {
    # See if it already exists

    my $results = sql_select("SELECT ca_id FROM cats WHERE ca_fullname='INBOX' AND ca_u_id=$maildb_uid");
    my $found = $results->rows();

    # If it doesn't create it

    if ($found == 0) {
        $results->finish();
        sql_do("INSERT INTO cats VALUES (NULL, $maildb_uid, 'INBOX', 'INBOX', 'INBOX, root of all other categories', NULL)");
        $inbox_cat_id = $dbh->{'mysql_insertid'};
        debug "Created INBOX\n";
    } elsif ($found == 1) {
        my $ref = $results->fetchrow_arrayref;
        $inbox_cat_id = @$ref[0];
        debug "Found INBOX category already in database\n";
    } else {
        die "Problem!  INBOX in DB multiple times!\n";
    }
}

#############################################################################

sub mkdir_msg_dir {
    return if (-d $config->{msg_dir});
    mkpath([$config->{msg_dir}], 0, 0777);
    if (! -d $config->{msg_dir}) {
        die "Could not make message dir: $config->{msg_dir}\n";
    }
}

#############################################################################

#
# Read in the folders
#
sub import_folders {
    my $mgr = Mail::Box::Manager->new;
    debug "Reading in folders...\n";
    foreach my $argv (@ARGV) {
        print "- Importing folder: $argv\n";
        my $folder = $mgr->open(folder => $argv);
        print "  Read " . $folder->name . " / " . 
            $folder->messages . " messages\n";
        
        # Put all categories into the database
        
        connect_to_db();
        make_cat($argv);
        
        # Iterate over all the messages, insertting them into the database

        my $count = 1;
        print "Importing message: ";
        STDOUT->autoflush(1);
        foreach my $msg ($folder->messages) {
            # Skip UW internal mbox messages
            
            next 
                if $msg->subject eq
                "DON'T DELETE THIS MESSAGE -- FOLDER INTERNAL DATA";

            # Otherwise, import it 

            import_message($msg, 0);
            print "$count ";
            ++$count;
            ++$total_imported_messages;
        }
        print "\n";

        # All done with this folder
        
        $folder->close();
        close_db();
        ++$total_imported_folders;
    }
    $mgr->close();
}

#############################################################################

#
# Import a single message; invoked from multiple places, including
# recusrively.  Creates entry in the msg_ids table and then imports
# the header, the body, and then finally makes an entry in the
# msg_owners table to complet the import.
#
sub import_message {
    my ($msg, $parent_id) = @_;

    debug "=============================================================\n";

    my Mail::Message::Head $head = $msg->head;
    my Mail::Message::Body $body = $msg->body;

    # --- Create an entry in the messages table for this message ---
    # This is the message ID for all the other SQL inserts to
    # cross-reference.

    my $sql = "INSERT INTO messages VALUES (NULL, $maildb_uid, NULL, 0)";
    sql_do($sql);
    my $msg_id = $dbh->{'mysql_insertid'};

    # --- Import the quick search data ---

    import_quick_search($msg, $msg_id, $parent_id);
    
    # --- Process the header ---
    # Note that attachment_id of 0 means the main message

    import_header($head, $msg_id, 0);

    # --- Process the body ---
    # Note that attachment_id of 0 means the main message

    my $has_children = import_body($msg, $body, $msg_id, 0);

    # --- Put this message in a category ---

    $sql = "INSERT DELAYED INTO msg_cats VALUES (NULL, $msg_id, $cat_id, $empty, $empty)";
    sql_do($sql);

    # Update the messages table to set the final flags on this
    # message, and make it visible to all other clients.

    my $flag_val = $FLAG_VALID + 
        ($has_children ? $FLAG_HAS_CHILDREN : 0);

    my $flags = $msg->labels;
    debug Dumper($flags);
    $flag_val += $FLAG_DELETED if ($msg->isDeleted);
    $flag_val += $FLAG_DRAFT if ($flags->{draft});
    $flag_val += $FLAG_FLAGGED if ($flags->{flagged});
    $flag_val += $FLAG_NEW if ($flags->{new});
    $flag_val += $FLAG_REPLIED if ($flags->{replied});
    $flag_val += $FLAG_SEEN if ($flags->{seen});
#    printf("Flag value: %x\n", $flag_val);

    $sql = "UPDATE messages SET msg_flags=$flag_val WHERE msg_id=$msg_id";
    sql_do($sql);

    ++$total_imported_messages_incl_embedded;
}

#############################################################################

sub import_quick_search {
    my ($msg, $msg_id, $parent_id) = @_;

    # Snarf some special header lines that Mail::Box knows about
            
    my $data;
    my Mail::Message::Head $head = $msg->head;

    foreach my $addr ($msg->to) {
        if ($data->{to}) {
            $data->{to} .= ", " . $addr->format;
        } else {
            $data->{to} = $addr->format;
        }
    }

    foreach my $addr ($msg->cc) {
        if ($data->{cc}) {
            $data->{cc} .= ", " . $addr->format;
        } else {
            $data->{cc} = $addr->format;
        }
    }

    foreach my $addr ($msg->bcc) {
        if ($data->{bcc}) {
            $data->{bcc} .= ", " . $addr->format;
        } else {
            $data->{cc} = $addr->format;
        }
    }

    foreach my $addr ($msg->from) {
        if ($data->{from}) {
            $data->{from} .= ", " . $addr->format;
        } else {
            $data->{from} = $addr->format;
        }
    }

    foreach my $addr ($msg->sender) {
        if ($data->{sender}) {
            $data->{sender} .= ", " . $addr->format;
        } else {
            $data->{sender} = $addr->format;
        }
    }

    $data->{subject} = $msg->subject;
    $data->{date} = $msg->timestamp;

    # Scan through all header lines, building up the rest of the
    # cached information that we need.  We know that this will always
    # be a Mail::Message::Head::Complete because we're reading mbox
    # files.

    foreach my Mail::Message::Field $field ($head->orderedFields) {
        my $name = $field->name();
        my $body = $field->unfoldedBody();

        if ($name eq "in-reply-to") {
            $data->{in_reply_to} = $body;
        } elsif ($name eq "reply-to") {
            $data->{reply_to} = $body;
        } elsif ($name eq "references") {
            $data->{references} = $body;
        } elsif ($name eq "message-id") {
            $data->{message_id} = $body;
        }
    }

    # We must have a message ID string from the header, but it's
    # possible that we don't (don't know if this is compliant with
    # RFC2822, but GNU mailman embedded messages do not have them)

    if (!$data->{message_id}) {
        $data->{message_id} = "<maildb-import-fake-id-" .
            int(rand(9999999)) . ">";
    }
    
    # Query the mime type ID

    $data->{multipart_boundary} = 
        find_mime_boundary($head->get("content-type"));

    # Believe it or not, it seems that MySQL has a problem with
    # uniqueness for non-unique text indices (!)  if there are spaces
    # at the end of a field.  Use the neat "+?" minimal
    # match/non-greedy regexp operator (see perlretut(1)).

    foreach my $key (keys %$data) {

        # Don't strip the prefix whitespace from the subject

        if ($key =~ /subject/i) {
            $data->{$key} =~ s/(.+?)[ \t]*$/$1/;
        } else {
            $data->{$key} =~ s/^[ \t]*(.+?)[ \t]*$/$1/;
        }
    }

    # Build up the SQL string to add the message into the
    # msg_quick_search table.

    my $sql = "INSERT INTO msg_quick_search VALUES (NULL, $msg_id, ";
    $sql .= $dbh->quote($data->{message_id}) . ", ";
    $sql .= $dbh->quote($data->{to}) . ", ";
    $sql .= $dbh->quote($data->{cc}) . ", ";
    $sql .= $dbh->quote($data->{bcc}) . ", ";
    $sql .= $dbh->quote($data->{subject}) . ", ";
    $sql .= $dbh->quote($data->{date}) . ", ";
    $sql .= $dbh->quote($data->{from}) . ", ";
    $sql .= $dbh->quote($data->{sender}) . ", ";
    $sql .= $dbh->quote($data->{in_reply_to}) . ", ";
    $sql .= $dbh->quote($data->{reply_to}) . ", ";
    $sql .= $dbh->quote($data->{references}) . ", ";
    $sql .= $dbh->quote($data->{multipart_boundary}) . ")";
    
    sql_do($sql);
}

#############################################################################

#
# Do the normal header import.  This may be called by other places
# (e.g., when importing a message body part, because they have
# headers, too), so it's a subroutine by itself.
#
sub import_header {
    my ($head, $msg_id, $part_id) = @_;

    # Insert all the fields into msg_hdrs
    
    my $count = 0;
    foreach my Mail::Message::Field $field ($head->orderedFields) {
        my $sql =
            "INSERT DELAYED INTO msg_hdrs VALUES (NULL, $msg_id, $part_id, " .
            $dbh->quote($field->Name) . ", " .
            $dbh->quote($field->unfoldedBody) . ", $count)";
        sql_do($sql);
        
        debug "Added header line: " . $field->Name .
            " $count, $part_id\n";
        ++$count;
    }
}

#############################################################################

#
# Import a body into the database.  This is potentially a recursive
# process, since a body may be nested, single, or multipart, and any
# one of those may contain another body.
#
sub import_body {
    my ($msg, $body, $msg_id, $count) = @_;
    my $has_children = 0;
    debug "Import body: ID $msg_id, count $count\n"; 

    # If this is a nested type, then just recruse into it

    if ($body->isNested) {
        debug "*** NESTED BODY\n";
        import_message($body->nested, $msg_id);
        $has_children = 1;
    }

    # If this is a multipart, import each part (including the preamble
    # and epilogue) as a separate attachment

    elsif ($body->isMultipart) {
        debug "*** MULTIPART BODY\n";

        # Preamble (header has already been imported)

        debug "*** Multipart preamble\n";
        $has_children += import_body($msg, $body->preamble, $msg_id, $count++)
            if ($body->preamble);

        # Attachments (import header lines as well)

        foreach my $part ($body->parts) {
            debug "*** Multipart part\n";
            import_header($part->head, $msg_id, $count);
            $has_children += import_body($msg, $part->body, $msg_id, $count++);
        }

        # Epilogue (has no header)

        debug "*** Multipart epilogue\n";
        $has_children += import_body($msg, $body->epilogue, $msg_id, $count++)
            if ($body->epilogue);
    }

    # Otherwise, this is a single-part message; just import it

    else {
        debug "*** SIMPLE BODY\n";
        import_simple_body($body, $msg_id, $count++);
    }

    # Return whether we have children or not

    $has_children;
}
        
#############################################################################

#
# Import a simple body into the database
#
sub import_simple_body {
    my ($body, $msg_id, $count) = @_;

    # If it's short, put it in the database.  

    my $sql = "INSERT DELAYED INTO msg_parts VALUES (NULL, $msg_id, $count, ";
    debug "Simple body size: " . $body->size . "\n";
    debug "Max short body: $config->{max_short_body_length}\n";

#    debug "Importing body: ". $body . "\n";
    my $saved = 0;
    my $quoted;
    if ($body->size <= $config->{max_short_body_length}) {
        $quoted = $dbh->quote($body);
        if (length($quoted) <= $config->{max_short_body_length}) {
            $sql .= $dbh->quote($body) . ", $empty)";
            $saved = 1;
        }
    }

    # If it's long, put it in the filesystem.

    if (0 == $saved) {
        my ($fh, $filename) = get_msg_filename();
        $sql .= "$empty, " . $dbh->quote($filename) . ")";
        print { $fh } $body;
        close($fh);
    }

    sql_do($sql);
    debug "Imported short body: ID $msg_id, count $count\n"; 
}

#############################################################################

#
# Make a random filename under a user's directory
#
sub get_msg_filename {
    my $dir = "$config->{msg_dir}/$maildb_username";

    # First, make a directory just for this user

    if (! -d $dir) {
        if (! mkpath([$dir], 0, 0700)) {
            die "Couldn't make maildb dir: $dir";
        }
    }
    
    # Now make a two-level directory structure, based on random
    # letters

    my $i = 0;
    my ($fh, $filename);
    while ($i < 10) {
        my $first = random_char();
        my $second = random_char();
        my $destdir = "$dir/$first/$second";
        if (! -d $destdir) {
            if (! mkpath([$destdir], 0, 0700)) {
                die "Couldn't make maildb dir: $destdir";
            }
        }

        # Try to make a tempfile in there

        ($fh, $filename) = tempfile(DIR => $destdir);
        last if ($fh);

        # Otherwise, loop around and try a different directory;
        # assumedly this one was full

        ++$i;
    }

    if ($i >= 10) {
        die "Unable to make destination file for large message";
    }

    debug "Made message file: $filename\n";
    return ($fh, $filename);
}

#############################################################################

#
# Make a random char
#
sub random_char {
    my $c = int(rand(26));
    if (int(rand(2)) == 1) {
        return chr($c + ord('A'));
    } else {
        return chr($c + ord('a'));
    }
}

#############################################################################

#
# Find the boundary line in the Content-type line
#
sub find_mime_boundary {
    my ($ct_line) = @_;

    # Look up the boundary line.  If it exists, it'll be somewhere on
    # the Content-Type line, and it may or may not be surrounded by
    # quotes.

    my $boundary = $ct_line;
    if ($boundary =~ /boundary=/i) {
        $boundary =~ s/^.*boundary=([^;]+).*$/$1/i;
        $boundary =~ s/^[\"\'](.+)[\"\']$/$1/;
    } else {
        $boundary = "";
    }
    debug "Found MIME/multipart boundary: $boundary\n";

    # All done

    $boundary;
}

#############################################################################

#
# Make a database category if it does not already exist (and all of
# its parents, analogous to "mkdir -p").
#
sub make_cat {
    my ($cat) = @_;
    my (@parts) = split ("/", $cat);

    debug "------------------------------\nmaking cat: $cat\n";

    my $full;
    my $parent_id = 0;
    foreach my $part (@parts) {
        if ($full)  {
            $full .= "/$part";
        } else {
            $full = $part;
        }
        debug "Part: $part\n";

        # Search for that name with $parent_id and $maildb_uid

        debug "Searching for category: $full\n";
        my $results = sql_select("SELECT ca_id FROM cats WHERE ca_fullname=" .
                              $dbh->quote($full) . 
                              " AND ca_u_id=$maildb_uid AND ca_parent=$parent_id");

        # If it was found, save the ID in the parent for the next
        # round

        if ($results->rows == 1) {
            my $ref = $results->fetchrow_arrayref;
            $cat_id = $parent_id = @$ref[0];
            debug "Found parent category: $part / ID $parent_id\n";
            $results->finish();
        }

        # Otherwise, it was not found, so create it and set its parent

        else {
            $results->finish();
            my $sql = "INSERT INTO cats VALUES (NULL, $maildb_uid, " .
                $dbh->quote($part) . ", " .
                $dbh->quote($full) . ", " .
                $dbh->quote("import from mbox: $full") . ", ";
            if ($parent_id) {
                $sql .= " $parent_id)";
                sql_do($sql);
                debug "Insertted $part with parent ID $parent_id\n";
            } else {
                $sql .= " 0)";
                sql_do($sql);
                debug "Insertted $part with no parent ID\n";
            }

            # Get the ID of this newly-created cat so that it can be
            # the parent next round

            $cat_id = $parent_id = $dbh->{'mysql_insertid'};
        }
    }

    debug "made cat ($cat): $full, ID $cat_id\n";
}

#############################################################################

#
# Close the db
#
sub close_db {
    $dbh->disconnect();
}




1.1                  maildb/libmaildb/db/mysql/doc/index_cat.pl

Index: index_cat.pl
===================================================================
#!/usr/bin/env perl

use strict;
use Data::Dumper;
use File::Basename qw/basename/;

# Use "require" because fink puts things in odd places

push(@INC, "/sw/lib/perl5/5.8.1/darwin-thread-multi-2level");
use DBI;

# Globals

my $dsn = "DBI:mysql:database=maildb;host=localhost";
my $dbh;
my $db_name = "maildb";
my $maildb_uid;
my $maildb_username;
my $cat_id;
my $config;
my $empty;
my $want_debug = 0;

my $FLAG_VALID = 0x1;
my $FLAG_HAS_CHILDREN = 0x2;
my $FLAG_DELETED = 0x4;
my $FLAG_DRAFT = 0x8;
my $FLAG_FLAGGED = 0x16;
my $FLAG_NEW = 0x32;
my $FLAG_REPLIED = 0x64;
my $FLAG_SEEN = 0x128;

# Do everything

connect_to_db();
get_user_id();
index_cat();
close_db();

exit(0);

#############################################################################

#
# debugging print
#
sub debug {
    my ($str) = @_;
    print $str
        if ($want_debug);
}

#############################################################################

#
# Connect to the db
#
sub connect_to_db {
    $dbh = DBI->connect($dsn, "maildb", "maildb");
    $empty = $dbh->quote("");
}

#############################################################################

#
# Execute an SQL query and return the results
#
my $sql_results;
sub execute_sql {
    my ($sql) = @_;

    # Check to ensure that the previous results were freed

    $sql_results->finish()
        if ($sql_results);

    # Now prepare and execute the new query

#    debug "Running SQL: $sql\n";
    $sql_results = $dbh->prepare($sql);
    if (!$sql_results) {
        die "Unable to prepare SQL: $sql: " . $dbh->errstr . "\n";
    }
    if (!$sql_results->execute) {
        die "Unable to execute SQL: $sql: " . $dbh->errstr . "\n";
    }

    # Done.  Return the handle to the new results.

    $sql_results;
}

#############################################################################

#
# Get the user's ID in the maildb database
#
sub get_user_id {
    my ($name, $passwd, $uid, $gid,
        $quota, $comment, $gcos, $dir, $shell, $expire) = getpwuid($<);
    debug "Username: $name\n";
    $maildb_username = $name;

    # Look and see if the username is in the db

    my $results = execute_sql("SELECT u_id FROM users WHERE u_username=" .
                          $dbh->quote($name));
    my $found = $results->rows();

    # Add if if it's not

    if ($found == 0) {
        die "User not in database.  Sorry.\n";
    } elsif ($found == 1) {
        debug "User already in maildb\n";
        while (my $ref = $results->fetchrow_arrayref) {
            $maildb_uid = @$ref[0];
        }
        $results->finish();
    } else {
        die "Problem!  User in DB multiple times!\n";
    }

    debug "User has maildb UID: $maildb_uid\n";

    # All done

    endpwent();
}

#############################################################################

#
# List all the sub-categories and messages in a category
#
sub index_cat {

    push(@ARGV, "")
        if ($#ARGV == -1);

    # First, find the requested category

    foreach my $cat (@ARGV) {
        my $cat_handle = find_cat($cat);

        if ($cat_handle) {
            list_sub_cats($cat_handle);
            list_messages($cat_handle);
        }
    }
}

#############################################################################

#
# Find a category
#
sub find_cat {
    my ($cat) = @_;
    my $cat_handle;

    my $sql = "SELECT ca_id, ca_fullname, ca_desc, ca_parent FROM cats WHERE ca_u_id=$maildb_uid ";

    # Are we looking for a specific category?

    if ($cat) {
        $sql .= "and ca_fullname=" . $dbh->quote($cat);

        my $results = execute_sql($sql);
        if ($results->rows == 1) {
            my $ref = $results->fetchrow_arrayref;
            $cat_handle = {
                id => @$ref[0],
                fullname => @$ref[1],
                desc => @$ref[2],
                parent => @$ref[3],
            };
        } elsif ($results->rows == 0) {
            die "Could not find category '$cat'";
        } else {
            print "Found more than one category named '$cat'!\n";
            while (my $ref = $results->fetchrow_arrayref) {
                print "Fullname: @$ref[1], id @$ref[0]\n";
            }
            die "Cannot continue\n";
        }
        $results->finish();
    } else {
        $sql .= "and ca_parent=0";
        $cat_handle = {
            id => 0,
            fullname => "root",
            desc => "Unnamed root category",
            parent => 0,
        };
    }

    # All done, to include the possibility of not finding it

    $cat_handle;
}

#############################################################################

#
# List all the sub-categories off the category
#
sub list_sub_cats {
    my ($cat_handle) = @_;

    my $sql = "SELECT ca_id, ca_fullname, ca_desc, ca_parent FROM cats WHERE ca_u_id=$maildb_uid AND ca_parent=$cat_handle->{id}";

    my $results = execute_sql($sql);
    if ($results->rows > 0) {
        print "Found " . $results->rows . " sub-categories\n";
        while (my $ref = $results->fetchrow_arrayref) {
            print "Sub-category: @$ref[1]\n          --> @$ref[2]\n";
        }
    } else {
        print "No sub-categories\n";
    }
    $results->finish()
        if ($results);
}

#############################################################################

#
# List all the messages in the category
#
sub list_messages {
    my ($cat_handle) = @_;

# msg_owners : mu_m_id -> msg_ids : m_id
# msg_hdrs : mh_m_id <- msg_ids : m_id
# msg_attach: ma_m_id <- msg_ids : m_id
#
# select * from msg_owners,msg_hdrs where msg_owners.mu_m_id=msg_hdrs.mh_m_id and mu_u_id=$maildb_uid and mu_ca_id=$cat_handle->{id}

    # Count how many messages were found in this category

    my $sql = "
SELECT count(*) 
  FROM messages, msg_cats 
 WHERE messages.msg_u_id = $maildb_uid 
   AND messages.msg_flags & $FLAG_VALID > 0
   AND messages.msg_id = msg_cats.mc_msg_id
   AND msg_cats.mc_ca_id = $cat_handle->{id}";
    my $results = execute_sql($sql);
    my $ref = $results->fetchrow_arrayref;
    print "Found " . @$ref[0] . " messages\n";
    $results->finish();

    # Count how many message headers were found in this category, and
    # get all the header lines

    my $sql = "
  SELECT msg_id, mh_part_id, mh_key, mh_value
    FROM messages, msg_cats, msg_hdrs, msg_parts
   WHERE messages.msg_u_id = $maildb_uid
     AND messages.msg_flags & $FLAG_VALID > 0
     AND messages.msg_id = msg_hdrs.mh_msg_id
     AND messages.msg_id = msg_cats.mc_msg_id
     AND messages.msg_id = msg_parts.mp_msg_id
     AND msg_cats.mc_ca_id = $cat_handle->{id}
ORDER BY messages.msg_id, msg_hdrs.mh_part_id, msg_hdrs.mh_sort_order";
    my $results = execute_sql($sql);

    if ($results->rows > 0) {
        print "Found " . $results->rows . " header lines\n";
        my $prev_msg_id = -1;
        my $prev_part_id = -1;

        while (my $ref = $results->fetchrow_arrayref) {
            my $msg_id = @$ref[0];
            my $part_id = @$ref[1];
            my $key = @$ref[2];
            my $value = @$ref[3];

            if ($prev_msg_id != $msg_id) {
                print "=============================================\n";
                $prev_msg_id = $msg_id;
                $prev_part_id = 0;
            } elsif ($prev_part_id != $part_id) {
                print "---------------------------------------------\n";
            }
            print "$msg_id: $key: $value\n";
        }
    } else {
        print "No messages in this category\n";
    }
    $results->finish()
        if ($results);
}

#############################################################################

#
# Close the db
#
sub close_db {
    $dbh->disconnect();
}