Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: sql query to retrieve mails store path

  1. #1
    Join Date
    Sep 2006
    Posts
    3
    Rep Power
    9

    Default sql query to retrieve mails store path

    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.

  2. #2
    Join Date
    Feb 2006
    Location
    Pune, India
    Posts
    294
    Rep Power
    9

    Default

    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,
    Regards,

    Chintan Zaveri
    (Yet another ZIMBRAN!)

    "Dhundhne par Bhagwan bhi ..."

  3. #3
    Join Date
    Sep 2005
    Posts
    2,103
    Rep Power
    14

    Default From SQL to the store path

    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.

    Code:
    #!/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";
        }
    }
    Bugzilla - Wiki - Downloads - Before posting... Search!

  4. #4
    Join Date
    Sep 2006
    Posts
    3
    Rep Power
    9

    Smile you're right !

    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.

  5. #5
    Join Date
    Feb 2006
    Location
    Pune, India
    Posts
    294
    Rep Power
    9

    Default

    HI marcmac,

    Thanks!

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

    Regards,
    Regards,

    Chintan Zaveri
    (Yet another ZIMBRAN!)

    "Dhundhne par Bhagwan bhi ..."

  6. #6
    Join Date
    Feb 2006
    Location
    Pune, India
    Posts
    294
    Rep Power
    9

    Default

    Hi marcmac,

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

    If instead of:
    Code:
    my $mbmask = sprintf "1" x $mgbits;
    my $fmask = sprintf "1" x $fgbits;
    I use:
    Code:
    my $mbmask = "1" x $mgbits;
    my $fmask = "1" x $fgbits;
    I am not a very good programmer and just trying to understand the code.
    Regards,

    Chintan Zaveri
    (Yet another ZIMBRAN!)

    "Dhundhne par Bhagwan bhi ..."

  7. #7
    Join Date
    Feb 2006
    Location
    Pune, India
    Posts
    294
    Rep Power
    9

    Default Some more questions ...

    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?

    Code:
    | 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,
    Regards,

    Chintan Zaveri
    (Yet another ZIMBRAN!)

    "Dhundhne par Bhagwan bhi ..."

  8. #8
    Join Date
    Sep 2005
    Posts
    2,103
    Rep Power
    14

    Default

    Quote Originally Posted by czaveri
    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.
    Bugzilla - Wiki - Downloads - Before posting... Search!

  9. #9
    Join Date
    Feb 2006
    Location
    Pune, India
    Posts
    294
    Rep Power
    9

    Default

    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.
    Regards,

    Chintan Zaveri
    (Yet another ZIMBRAN!)

    "Dhundhne par Bhagwan bhi ..."

  10. #10
    Join Date
    Feb 2006
    Location
    Pune, India
    Posts
    294
    Rep Power
    9

    Default

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

    Better yet, I will checkout svn.

    Thanks,
    Regards,

    Chintan Zaveri
    (Yet another ZIMBRAN!)

    "Dhundhne par Bhagwan bhi ..."

Similar Threads

  1. query regarding memory where the mails are stored
    By darshan in forum Developers
    Replies: 0
    Last Post: 03-22-2007, 11:41 PM
  2. Lotus migration
    By babou in forum Migration
    Replies: 15
    Last Post: 03-05-2007, 09:33 PM
  3. Moving Mails from one store to another
    By chh in forum Administrators
    Replies: 3
    Last Post: 11-08-2006, 06:17 AM
  4. Zimbra sql schema and mail store?
    By mberardi in forum Developers
    Replies: 8
    Last Post: 02-09-2006, 09:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •