User Tools

Site Tools


sample_user_queries

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
sample_user_queries [2014/07/22 15:15]
daniel
sample_user_queries [2014/08/04 13:23] (current)
daniel
Line 1: Line 1:
 ==== Sample SQL queries ==== ==== Sample SQL queries ====
  
-Messages ​that have a private note attached ​to them:+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> <code sql>
 SELECT mail_id FROM notes SELECT mail_id FROM notes
 </​code>​ </​code>​
  
-The last 20 messages that have a private note:+=== The last 20 messages that have a private note ===
 <code sql> <code sql>
 SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20 SELECT mail_id FROM notes ORDER BY mail_id DESC LIMIT 20
 </​code>​ </​code>​
  
-Messages ​that have a private note which has been added or modified during the last 7 days:+=== Messages ​with a private note which has been added or modified during the last 7 days ===
 <code sql> <code sql>
 SELECT mail_id FROM notes WHERE last_changed>​=now()-'​7 days'::​interval SELECT mail_id FROM notes WHERE last_changed>​=now()-'​7 days'::​interval
 </​code>​ </​code>​
  
-Messages ​that have one or more pictures attached+=== Messages ​with one or more pictures attached ​===
 <code sql> <code sql>
 select mail_id from attachments where content_type like '​image/​%'​ select mail_id from attachments where content_type like '​image/​%'​
 </​code>​ </​code>​
  
-Outgoing messages that have been composed less than one month ago+=== Outgoing messages that have been composed less than one month ago ===
 <code sql> <code sql>
 SELECT m.mail_id FROM mail m WHERE msg_date>​=now()-'​1 month'::​interval AND status&​256=256 SELECT m.mail_id FROM mail m WHERE msg_date>​=now()-'​1 month'::​interval AND status&​256=256
 </​code>​ </​code>​
  
-Messages ​that have a lot of recipients in the //To// field (example: more than 20)+=== Messages ​with many recipients in To field === 
 +(example: more than 20 recipients)
 <code sql> <code sql>
 SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id having count(*)>​20 SELECT mail_id FROM mail_addresses WHERE addr_type=2 GROUP BY mail_id having count(*)>​20
 </​code>​ </​code>​
  
-Messages ​that have a lot of recipients in the //To// or //Cc// fields ​(example: more than 20)+=== Messages ​with many recipients in To/Cc field === 
 +(example: more than 20)
 <code sql> <code sql>
 SELECT mail_id FROM mail_addresses WHERE addr_type in (2,3) GROUP BY mail_id having count(*)>​20 SELECT mail_id FROM mail_addresses WHERE addr_type in (2,3) GROUP BY mail_id having count(*)>​20
 </​code>​ </​code>​
  
-Messages ​for which any sender or recipient ​is from a given domain+=== Messages ​with any sender or recipient from a given domain ​===
 <code sql> <code sql>
 select a.mail_id from mail_addresses a join addresses using(addr_id) where email_addr like '​%@example.com'​ select a.mail_id from mail_addresses a join addresses using(addr_id) where email_addr like '​%@example.com'​
 </​code>​ </​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>​
  
 ---- ----
sample_user_queries.1406034923.txt.gz · Last modified: 2014/07/22 15:15 by daniel