Differences

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

--- sql_analysis [2012/01/28 02:55] – daniel
+++ sql_analysis [2019/01/24 17:55] (current) – [Presence and count of specific mail headers] daniel
@@ Line 8: / Line 8: @@
 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 split_part(substr(lines, 1+position(E'\n'||field in lines), 200), E'\n', 1)
+group by 1
 </code>
@@ Line 48: / Line 48: @@
  X-Priority: Normal        |     5
 </code>
+This alternative implementation uses regular expressions and differs in that it doesn't limit header values to 200 characters or any other fixed length.
+<code sql>
+select FIELD||':'||arr[1], count(*)
+ from (select FIELD, regexp_matches(lines, '(?:^|\n)' || FIELD || ':\s*([^\n]*)', 'gi') as arr
+       from header,
+         (VALUES ('X-Priority'), ('Importance'), ('Precedence'), ('Priority'),
+             ('X-MSMail-Priority'), ('X-MS-Priority')) AS h(FIELD)
+             where strpos(lines,FIELD)>0) l
+GROUP BY 1 ORDER BY 1
+</code>
+In this version, the '' strpos(lines,FIELD)>0 '' condition is not essential: it's introduced only as a first-pass filter to eliminate the headers that don't contain anywhere any of the searched fields.
 ====== Duplicate messages ======
 This query finds each message that share the exact same headers than another message with a lower mail_id, which means that it's a duplicate.
@@ Line 54: / Line 67: @@
 </code>
-====== Messages with specific attachment types ======
+A stricter version, comparing the md5 hashes of bodies (text and html parts) in addition to the headers:
-To retrieve all messages containing pdf files or any image file:
 <code sql>
-select distinct mail_id FROM attachments WHERE content_type='application/pdf' OR content_type like 'image/%';
+select b2.mail_id from body b1, body b2, header h1, header h2
+ where b1.mail_id < b2.mail_id
+ and h1.mail_id = b1.mail_id
+ and h2.mail_id = b2.mail_id
+ and md5(h1.lines) = md5(h2.lines)
+ and md5(b1.bodytext) is not distinct from md5(b2.bodytext)
+ and md5(b1.bodyhtml) is not distinct from md5(b2.bodyhtml);
 </code>
-====== Messages sent or received today  ======
+The ''IS NOT DISTINCT'' comparator behaves as expected when ''bodytext'' or ''bodyhtml'' is NULL, as opposed
-<code>
+to the simple equality operator, for which ''NULL=NULL'' is false
-select mail_id from mail where msg_date>=date_trunc('day',now());
+====== Hierarchical view of tags  ======
+Output a list of tags sorted by hierarchy level and names,
+with an indentation to represent the hierarchy.
+<code sql>
+WITH RECURSIVE tagr(a,_tag_id,name,level) as (
+ select
+  array[row_number() over (order by name)] as a,
+  tag_id,
+  name,
+as level
+ from tags where parent_id is null
+UNION ALL
+ select
+   tagr.a || row_number() over (order by tags.name),
+   tag_id,
+   tags.name,
+   tagr.level+1
+ FROM tags JOIN tagr ON tagr._tag_id=tags.parent_id
+)
+select repeat('   ', level-1) || name from tagr order by a;
 </code>