SQL queries

The user interface provides the Query Selection dialog box in order to select messages matching criteria such as sender, date, tags, subject, etc.

Internally, these criteria are used to build SQL queries. However, since the criteria offered by the user interface cannot cover every possible user need, Manitou-Mail allows user-defined SQL queries.

These user queries must return a list of mail_id. The mail_id is the primary key of the MAIL table, meaning that it is the value that uniquely identifies each message. A user query will be combined with other criteria if submitted, and used as a subquery inside a SQL IN () construct.

Examples of queries:

• Messages that contain attached pictures:
  SELECT mail_id FROM attachments WHERE content_type like 'image/%'
• Incoming messages that are not archived and not tagged:
  SELECT m.mail_id FROM mail m LEFT OUTER JOIN mail_tags mt ON mt.mail_id=m.mail_id WHERE mt.mail_id is null AND m.status&(32+128)=0
• Incoming messages that are not archived and not tagged as 'spam' (they may have been assigned other tags)
  SELECT m.mail_id FROM mail m LEFT OUTER JOIN (select mail_id from mail_tags where tag=(select tag_id from tags where name='spam')) AS mt ON mt.mail_id=m.mail_id WHERE mt.mail_id is null AND m.status&(32+128)=0
• Faster variant of the previous query (here we use the mail_status table, which keeps track of only the statuses of non-archived messages)
  SELECT m.mail_id FROM mail_status m LEFT OUTER JOIN (select mail_id from mail_tags where tag=(select tag_id from tags where name='spam')) AS mt ON mt.mail_id=m.mail_id WHERE mt.mail_id is null AND m.status&(32+128)=0
• Recent outgoing messages:
  SELECT mail_id FROM mail WHERE msg_date>=now()-'1 month'::interval AND status&256=256
• All messages tagged simultaneously with tags 'red tag' and 'blue tag'
  SELECT m1.mail_id FROM mail_tags m1, mail_tags m2, tags t1, tags t2 WHERE m1.mail_id=m2.mail_id AND m1.tag=t1.tag_id AND m2.tag=t2.tag_id AND t1.name='red tag' AND t2.name='blue tag'