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<p...@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 <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers