maildb-cvs Mailing List for maildb

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

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();
}

2002	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct (22)	Nov (1)	Dec (1)
2003	Jan	Feb	Mar	Apr	May (7)	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2004	Jan	Feb	Mar	Apr	May (4)	Jun	Jul	Aug	Sep	Oct	Nov	Dec

maildb-cvs Mailing List for maildb

maildb-cvs — CVS activity for the MailDB project