Re: mailing list archiver chewing patches

Il 14/01/2010 14:39, Dimitri Fontaine ha scritto:
> Matteo Beccati<php(at)beccati(dot)com> writes:
>> I've extended AOX with a trigger that takes care of filling a separate table
>> that's used to display the index pages. The new table also stores threading
>> information (standard headers + Exchange headers support) and whether or not
>> the email has attachments.
>>
>> Please check the updated PoC: http://archives.beccati.org/
>
> Looks pretty good, even if some thread are still separated (this one for
> example), and the ordering looks strange.

This one is separated as the first one is not in the archive yet, thus
to the system there are multiple parent messages. It shouldn't happen
with full archives. About sorting, here's the query I've used (my first
try with CTEs incidentally):

WITH RECURSIVE t (mailbox, uid, date, subject, sender, has_attachments,
parent_uid, idx, depth) AS (
SELECT mailbox, uid, date, subject, sender, has_attachments,
parent_uid, uid::text, 1
FROM arc_messages
WHERE parent_uid IS NULL AND mailbox = 15
UNION ALL
SELECT a.mailbox, a.uid, a.date, a.subject, a.sender,
a.has_attachments, a.parent_uid, t.idx || '.' || a.uid::text, t.depth + 1
FROM t JOIN arc_messages a USING (mailbox)
WHERE t.uid = a.parent_uid
) SELECT * FROM t ORDER BY idx

Any improvements to sorting are welcome :)

Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/