Examples of queries

Mail retrieval

These queries return lists of mail_id so they can be input inside the user interface in the SQL field of the Selection/New query dialog, and then saved to appear in the "Quick selection" panel for later reuse.

• Messages that contain an attached C source file

  SELECT DISTINCT mail_id FROM attachments WHERE filename like '%.c'
  

• Messages that have big attachments (bigger than 1Mb)

  SELECT mail_id,content_size FROM attachments WHERE content_size>1e6
  

• Messages that contain pictures

  SELECT DISTINCT mail_id FROM attachments WHERE content_type like 'image/%'
  

• Messages coming from any account of a particular mail domain (thedomain.com)

  SELECT mail_id FROM mail_addresses ma, addresses a
   WHERE a.email_addr like '%@thedomain.com'
     AND ma.addr_id=a.addr_id
     AND ma.addr_type=1
  

• Messages sent (by anyone) to any account that has 'Mary' in her name

  SELECT DISTINCT mail_id FROM mail_addresses ma, addresses a
   WHERE a.name like '%Mary%'
     AND ma.addr_id=a.addr_id
     AND ma.addr_type=2
  

• Messages sent (from our manitou database) to any account that has 'Mary' in her name

  SELECT DISTINCT m.mail_id FROM mail_addresses ma, addresses a, mail m
   WHERE a.name like '%Mary%'
     AND ma.addr_id=a.addr_id
     AND ma.addr_type=2
     AND m.mail_id=ma.mail_id
     AND m.status&128=128
  

• Messages sent (by mymail@mail.com) to any account that has 'Mary' in her name

  SELECT DISTINCT ma1.mail_id
  FROM mail_addresses ma1, addresses a1, addresses a2, mail_addresses ma2
   WHERE a1.name like '%Mary%'
     AND ma1.addr_id=a1.addr_id
     AND ma1.addr_type=2
     AND ma2.mail_id=ma1.mail_id
     AND a2.addr_id=ma2.addr_id
     AND a2.email_addr='mymail@mail.com'
     AND ma2.addr_type=1
  

• Messages that contain the exact string: I'm The Searched String'

  SELECT mail_id FROM body WHERE strpos(bodytext,'I\'m The Searched String')>0
  

Usage statistics

• Messages that are waiting to be processed for more than 7 days

  SELECT mail_id FROM mail WHERE status&32=0
   AND msg_date>=now()-interval '7 days'
  

• The top ten of senders and their numbers of messages

  SELECT email_addr,count(*) AS cnt
   FROM mail_addresses ma, addresses a
   WHERE ma.addr_id=a.addr_id
     AND ma.addr_type=1
   GROUP BY email_addr
   ORDER BY cnt DESC
   LIMIT 10
  

• The top ten of receivers (To or Cc) and their numbers of messages

  SELECT email_addr,count(*) AS cnt
   FROM mail_addresses ma, addresses a
   WHERE ma.addr_id=a.addr_id
     AND ma.addr_type in (2,3)
   GROUP BY email_addr
   ORDER BY cnt DESC
   LIMIT 10