sql query to retrieve mails store path

[banou]

Hello everybody,

I just want to know if a sql query to retrieve mails store path can be written.
Inputs data are email address and mail's id.
Output data will be a path like /opt/zimbra/store/0/1/msg/0/260-100.msg.

Underlined question hidden behind my need is : how mail's store is organized ?

Thanks you for any help.

[czaveri]

Hi banou,

This is a brief summary of how, I believe, E-mails are stored in Zimbra: (You can use this to develop your MySQL query)

All E-mails stored in /opt/zimbra/store
All Indexes in /opt/zimbra/index
In LDAP you will find e-mail address
Corresponding to it, is an "account id"
This id can be found in MySQL's "zimbra" database, "mailbox" table
corresponding to it you will find "id"
append the id to the term "mailbox" to get something like "mailbox1", "mailbox2", etc...
That is the name of database in which data for user is kept
Let's say, for some user@domain.com it is mailbox5
so you: use mailbox5
The mail_item table in "mailbox5" will contain all information you want.
The messages are named: index_id-mod_content.msg where index_id & mod_content are provided in this table ...
there's much more that you can learn once you study the mail_item table...

HTH,

[marcmac]

It's a bit more complicated than that, since you've got to keep track of the volume that the message is on, too. This is a Q&D that I put together for 3.1.x, but it'll probably work with 4.x, too. Run it as the zimbra user.

#!/usr/bin/perl

# OK, there's 2 MAILBOX_*_BITS values in the VOLUME table.
# Take the mailbox ID, right-shift it by MAILBOX_BITS, and take the lowest MAILBOX_GROUP_BITS of the result.
# That's your mailbox hash.
# Take the message ID, right-shift it by FILE_BITS, and take the lowest FILE_GROUP_BITS of the result.  That's your msgid hash.
# I think.
# <mbx-hash>/<mbx-id>/msg/<msgid-hash>/<msgid>-<mod_content>.msg

my ($fbits, $fgbits, $mbits, $mgbits, $basepath) = split (' ',`echo "select file_bits, file_group_bits, mailbox_bits, mailbox_group_bits, path from volume where type='1'" | mysql -N zimbra`);

chomp $basepath;

my $mbmask = sprintf "1" x $mgbits;
my $fmask = sprintf "1" x $fgbits;

foreach (`echo "select id, account_id, comment from mailbox" | mysql -N zimbra`) {
    chomp;
    my $path = "$basepath/";
    my ($id, $aid, $nm) = (split);
    my $mbhash = $id >> $mbits;
    $mbhash &= $mbmask;
    $path .= $mbhash."/".$id."/msg/";
    foreach my $msgstuff (`echo "select id,mod_content from mail_item where blob_digest is not null;" | mysql -N mailbox${id}`) {
        chomp $msgstuff;
        if ($msgstuff eq "") {next;}
        my ($msgid, $modContent) = split (' ',$msgstuff);
        my $msghash = $msgid >> $fbits;
        $msghash &= $fmask;
        my $nm = $msgid;
        if ($modContent) {$nm .= "-$modContent";}
        my $npath = $path.$msghash."/".$nm.".msg";
        print $npath."\n";
    }
}

[banou]

it'll probably work with 4.x

You're right : this perl script works perfectly. I'm going to use it.
Thank you very much.

[czaveri]

HI marcmac,

Thanks!

This is a very useful script. Clears many doubts. :-)

Regards,

[czaveri]

Hi marcmac,

Just want to clarify my doubts. The script works perfect without sprintf, too. Do we really need them?

If instead of:

my $mbmask = sprintf "1" x $mgbits;
my $fmask = sprintf "1" x $fgbits;

I use:

my $mbmask = "1" x $mgbits;
my $fmask = "1" x $fgbits;

I am not a very good programmer and just trying to understand the code.

[czaveri]

1) Is this convention fixed and reliable?
type=1 "always" implies "store"
type=10 "always" implies "index"

Reference: MySQL database 'zimbra', table 'volume'.

2) Can there be more than 1 path? If there are too many messages, for example?

Reference: MySQL database 'zimbra', table 'volume'.

3) What do the following fields in mail_item denote?

| parent_id    | int(10) unsigned    | YES  | MUL | NULL    |       |
| folder_id    | int(10) unsigned    | YES  | MUL | NULL    |       |
| imap_id      | int(10) unsigned    | YES  |     | NULL    |       |
| index_id     | int(10) unsigned    | YES  | MUL | NULL    |       |
| blob_digest  | varchar(28)         | YES  |     | NULL    |       |
| flags        | int(11)             |      | MUL | 0       |       |

I only have a vague idea but not an authoritative answer... can someone help?

Thanks and regards,

[marcmac]

1) Is this convention fixed and reliable?
type=1 "always" implies "store"
type=10 "always" implies "index"

Reference: MySQL database 'zimbra', table 'volume'.

Nope - there's no guarantee that ANY of this will be consistent from version to version.

[czaveri]

Thanks for replying, marcmac,

I am not thinking if there is any guarantee from version to version. I was only thinking about the current 4.0.0 & and 4.0.1 releases.

[czaveri]

I have too many such questions ... don't really want to bother with silly things.

Better yet, I will checkout svn.

Thanks,