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.mailboxAND 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
