vulture at netvulture
Aug 5, 2009, 7:35 PM
Post #3 of 3
(743 views)
Permalink
|
OK - 2nd try forgot the
WHERE msg.status < 2
If you don't include the msg.status < 2, then you will be counting all messages, whether deleted or not.
status=2 deleted (expunge done), status=3 deleted waiting for purge run to actually be deleted.
Well,
If you are looking for the used space of the user in general, then
dbmail_users.curmail_size is the column you want.
SELECT curmail_size FROM dbmail_users WHERE userid="theuser";
But I think you want to get a per mailbox (folder) look, so:
SELECT users.userid user, mbx.name mailbox, COUNT(msg.message_idnr)
message_count, SUM(phys.messagesize) size FROM dbmail_mailboxes mbx JOIN
dbmail_users users ON (mbx.owner_idnr=users.user_idnr) JOIN
dbmail_messages msg ON (mbx.mailbox_idnr=msg.mailbox_idnr) JOIN
dbmail_physmessage phys ON (phys.id=msg.physmessage_id) WHERE msg.status < 2 GROUP BY
mbx.mailbox_idnr ORDER BY users.userid, mbx.name;
That will give you all users, all mailboxes, order by user then mailbox.
If you want just a paticular userid and maybe just one mailbox of
theirs, then add to the where clause
AND users.userid="theuser"
AND users.userid="theuser" AND mbx.name="INBOX"
-Jon
Simon wrote:
> Hi There,
>
> We have a external client management app that we would like to be able
> to calculate the size of a dbmail mailbox with a SQL query. Can anyone
> give me some pointers here to get this right please?
>
> Thanks
>
> Simon
>
> --
> Scanned for viruses and dangerous content by *MailScanner*
> <http://www.mailscanner.info/>
> ------------------------------------------------------------------------
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
--
Scanned for viruses and dangerous content by MailScanner
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
|
