Manitou-Mail Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
==== Sample SQL queries ====

See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns.

=== Messages with a private note attached ===
<code sql>
SELECT mail_id FROM notes
</code>

=== The last 20 messages that have a private note ===
<code sql>
SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20
</code>

=== Messages with a private note which has been added or modified during the last 7 days ===
<code sql>
SELECT mail_id FROM notes WHERE last_changed>=now()-'7 days'::interval
</code>

=== Messages with one or more pictures attached ===
<code sql>
select mail_id from attachments where content_type like 'image/%'
</code>

=== Outgoing messages that have been composed less than one month ago ===
<code sql>
SELECT m.mail_id FROM mail m WHERE msg_date>=now()-'1 month'::interval AND status&256=256
</code>

=== Messages with many recipients in To field ===
(example: more than 20 recipients)
<code sql>
SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id having count(*)>20
</code>

=== Messages with many recipients in To/Cc field ===
(example: more than 20)
<code sql>
SELECT mail_id FROM mail_addresses WHERE addr_type in (2,3) GROUP BY mail_id having count(*)>20
</code>

=== Messages with any sender or recipient from a given domain ===
<code sql>
select a.mail_id from mail_addresses a join addresses using(addr_id) where email_addr like '%@example.com'
</code>

=== Messages with attachments bigger than a given size ===
(example: bigger than 1Mb)
<code sql>
select mail_id from attachments where content_size > 1024*1024
</code>

----

See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns.