Re: mailing list archiver chewing patches

On Thu, Jan 14, 2010 at 03:08:22PM +0100, Matteo Beccati wrote:
> Il 14/01/2010 14:39, Dimitri Fontaine ha scritto:
> >Matteo Beccati<php@beccati.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 :)

This is probably better written as:

WITH RECURSIVE t (
    mailbox,
    uid,
    date,
    subject,
    sender,
    has_attachments,
    "path"
)
AS (
    SELECT
        mailbox,
        uid,
        date,
        subject,
        sender,
        has_attachments,
        ARRAY[uid]
    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,
        t."path" || a.uid,
    FROM
        t JOIN arc_messages a
        ON (
            a.mailbox = t.mailbox AND
            t.uid = a.parent_uid
        )
)
SELECT *
FROM t
ORDER BY "path";

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate