Zimbra sql schema and mail store?

[mberardi]

I want to write a script to automatically train spam assassin. I plan on using read messages in the inbox as ham and the contents of the junk folder as spam.

I've figured out how to get user/folder read/unread status out of mysql I think:

zimbra.mailbox.id corresponds to mailboxx db and zimbra/store/0/x/msg
and mailboxx.mail_item.folder_id corresponds to the imap folders
and unread is boolean

what I'm missing is how to infer the directory under zimbra/store/ and the directory under zimbra/store/x/x/msg/

[marcmac]

Wouldn't it be easier to start with the zmtrainsa script, and use fetchmail to only grab read messages from the folder? Or, move all read messages to a secondary folder and grab them from that?

Much more straightforward than grabbing the data from sql.

[mberardi]

needing the password for fetchmail is a limitation I can't work around.

I don't think this is particullarly difficult, all I need to know is where the 0,1... comes from after store and msg.

unless there is a way to grant fetchmail imap access without passwords?

[KevinH]

Are you trying to use fetchmail for each user's account? Why not just use the built in support of Junk and Not Junk buttons that copies the messages to special SPAM and HAM system accounts.

Problem with zmtrainsa

[mberardi]

that sounds good, I didn't realise that existed, however, since the "not junk" button is only in the spam folder, and practically nothing is being marked spam now, it will take a long time to get the necessary ham.

does that get triggered when a user just moves their spam to the junk folder? I have had little luck convincing anyone to use the web interface.

[KevinH]

Yes those copies are trigger on the web UI only.

[mberardi]

anyway, I wrote my script, it is 50 lines, it messily finds those directories I couldn't figure out how to pull from the database by checking if its zero and incrementing from there. then feeds the messages to sa-learn.

[bburtin]

Here's a snippet of Perl that calculates the mailbox group directory, based on the mailbox id.

sub getMailboxGroupDir($) {
    # Assume that the volume bits settings are hardcoded for now
    my ($mailboxId) = @_;
    my $groupDir = $mailboxId >> 12;
    $groupDir &= 0xFF;
    return $groupDir;
}

[mberardi]

looks good, thanks