sql_analysis
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
sql_analysis [2016/07/21 04:00] – [Presence and count of specific mail headers] daniel | sql_analysis [2018/04/20 09:47] – daniel | ||
---|---|---|---|
Line 67: | Line 67: | ||
</ | </ | ||
- | ====== 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 | + | select |
+ | 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); | ||
</ | </ | ||
- | ====== | + | The '' |
- | < | + | to the simple equality operator, for which '' |
- | select | + | |
+ | ====== Hierarchical view of tags | ||
+ | |||
+ | Output a list of tags sorted by hierarchy level and names, | ||
+ | with an indentation to represent the hierarchy. | ||
+ | |||
+ | < | ||
+ | WITH RECURSIVE tagr(a, | ||
+ | | ||
+ | array[row_number() over (order by name)] as a, | ||
+ | tag_id, | ||
+ | name, | ||
+ | 1 as level | ||
+ | from tags where parent_id is null | ||
+ | UNION ALL | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | FROM tags JOIN tagr ON tagr._tag_id=tags.parent_id | ||
+ | ) | ||
+ | select repeat(' | ||
</ | </ | ||
sql_analysis.txt · Last modified: 2019/01/24 17:55 by daniel