https://manitou-mail.org/wiki/ 2026-04-16T02:37:54+00:00 https://manitou-mail.org/wiki/ https://manitou-mail.org/wiki/lib/exe/fetch.php/wiki:dokuwiki-128.png text/html 2021-05-15T12:21:57+00:00 daniel (daniel@undisclosed.example.com) https://manitou-mail.org/wiki/doku.php/adv_sample_user_queries_plus?rev=1621081317&do=diff Advanced examples of SQL queries Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag: SELECT m.mail_id FROM mail m JOIN mail_addresses ma ON (ma.mail_id=m.mail_id) JOIN addresses USING(addr_id) WHERE status&256<>0 AND email_addr like '%@example.org' AND not exists (select 1 from mail_tags mt JOIN (select tag_id FROM tags WHERE name='Sample Tag') t ON (t.tag_id=mt.tag) WHERE mt.mail_id=m.mail_id) ; text/html 2019-01-24T17:55:47+00:00 daniel (daniel@undisclosed.example.com) https://manitou-mail.org/wiki/doku.php/sql_analysis?rev=1548352547&do=diff Presence and count of specific mail headers This query extracts, for each of the header fields from a fixed list, the number of occurrences of all distinct values of the field. SELECT split_part(substr(lines, 1+position(E'\n'||field in lines), 200), E'\n', 1) as ct, count(*) FROM header, (values ('X-Priority'), ('Importance'), ('Precedence'), ('Priority'), ('X-MSMail-Priority'), ('X-MS-Priority')) as h(field) WHERE position(E'\n'||field IN lines)>0 group by 1 text/html 2018-04-20T11:17:29+00:00 daniel (daniel@undisclosed.example.com) https://manitou-mail.org/wiki/doku.php/support_functions?rev=1524223049&do=diff tag_path(int) Extract the tag name with its hierarchy. This recursive function takes the ID of a tag and returns its full name, including its hierarchy. CREATE OR REPLACE FUNCTION tag_path(in_tag_id INTEGER) RETURNS text AS $$ DECLARE r text; id INTEGER; BEGIN IF in_tag_id IS NULL THEN RETURN null; END IF; SELECT name, parent_id INTO r,id FROM tags WHERE tag_id=in_tag_id; IF (id IS NULL) THEN RETURN r; ELSE RETURN tag_path(id)||'->'||coalesce(r,''); END IF; END;… text/html 2017-10-18T14:55:57+00:00 daniel (daniel@undisclosed.example.com) https://manitou-mail.org/wiki/doku.php/start?rev=1508338557&do=diff Manitou-Mail Wiki Welcome to the Wiki for Manitou-Mail, a database-driven mail user agent. manitou-mdx manitou-mdx is a Perl program that is responsible for importing incoming mail into the database, and exporting outgoing mail to a Mail Transfert Agent. MDX stands for Mail Database e text/html 2016-05-16T12:27:32+00:00 daniel (daniel@undisclosed.example.com) https://manitou-mail.org/wiki/doku.php/inverted_word_index?rev=1463401652&do=diff Context See also Implementation of the inverted word index for how the word index is stored in the database, and the definition of tables: * words * non_indexable_words * inverted_word_index Function The word index can be queried in SQL. Here is the code of a PL/pgSQL function that retrieves the IDs of messages containing a set of words. The words are given as an array of text. The function is already in the Manitou-Mail database for versions 1.1.0 and above text/html 2015-11-18T04:58:31+00:00 daniel (daniel@undisclosed.example.com) https://manitou-mail.org/wiki/doku.php/sql_basic_stats?rev=1447822711&do=diff Number of messages for a given month (including days with no message). Example for July, 2015 SELECT day::date, coalesce(cnt,0) as cnt FROM (select date_trunc('day',msg_date) as day, count(*) as cnt from mail where msg_date>='2015-07-01'::timestamptz and msg_date<'2015-07-01'::timestamptz+interval '1 month' -- and other optional conditions group by 1 ) as list RIGHT JOIN generate_series('2015-07-01'::timestamptz, '2015-07-01'::timestamptz+interval '1 mo… text/html 2015-05-19T15:27:05+00:00 daniel (daniel@undisclosed.example.com) https://manitou-mail.org/wiki/doku.php/access_rights?rev=1432049225&do=diff Schema access rights Access rights management is left to the administrator by way of self-written SQL commands, as is the creation of database users. This page gives some basic guidelines on how to manage a database with multiple users in a typical scenario: text/html 2014-09-01T21:01:34+00:00 daniel (daniel@undisclosed.example.com) https://manitou-mail.org/wiki/doku.php/sql_analysis_dedup_attachments?rev=1409605294&do=diff Attachments deduplication Attachments are stored into 3 tables: attachments, attachment_contents and the pg_largeobject system table. At import time, the SHA1 fingerprint of attachments are computed and compared to contents already in the database. When a match is found, a new reference is created in attachment_contents to the already existing OID (large object reference) instead of importing again the same contents.