sql_analysis
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
sql_analysis [2011/11/30 13:11] daniel |
sql_analysis [2019/01/24 18:55] (current) daniel [Presence and count of specific mail headers] |
||
|---|---|---|---|
| Line 8: | Line 8: | ||
| FROM header, (values ('X-Priority'), ('Importance'), ('Precedence'), ('Priority'), ('X-MSMail-Priority'), ('X-MS-Priority')) as h(field) | 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 | 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> | </code> | ||
| Line 48: | Line 48: | ||
| X-Priority: Normal | 5 | X-Priority: Normal | 5 | ||
| </code> | </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 ====== | ====== 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. | 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> | </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> | <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> | </code> | ||
| + | |||
| + | The ''IS NOT DISTINCT'' comparator behaves as expected when ''bodytext'' or ''bodyhtml'' is NULL, as opposed | ||
| + | to the simple equality operator, for which ''NULL=NULL'' is false | ||
| + | |||
| + | ====== 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, | ||
| + | 1 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> | ||
| + | |||
sql_analysis.1322655072.txt.gz · Last modified: 2011/11/30 13:11 by daniel
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
