Jonathan Feally wrote:
> In the IMAP SORT, we are trying to get all the message_idnr's in a
> mailbox sorted by the criteria. 100% of the message_idnr's need to be
> returned.
Nope. Sort supports all the same search criteria as IMAP SEARCH.
>
> This leaves us with
> SELECT message_idnr FROM dbmail_messages m
> We can omit joining dbmail_physmessage if we don't need the size or
> internal_date.
we can omit joining physmessage because the header table already joins
with messages on physmessage_id
> JOIN dbmail_phymessage p ON p.id=m.phymessageid
> Now we need to know the headername_id for the header to be
> sorted/searched on
> JOIN dbmail_headername hnxxx ON hnxxx.headername=lower('XxX')
That's crappy sql. For search I prefer
SELECT message_idnr FROM dbmail_messages m
JOIN dbmail_header h USING (physmessage_id)
JOIN dbmail_headername n ON h.headername_id = n.id
JOIN dbmail_headervalue v ON h.headervalue_id = v.id
WHERE
m.mailbox_idnr=2 AND m.status IN (0,1)
AND n.headername = lower('TO') AND v.headervalue LIKE '%paul%'
ORDER BY message_idnr;
So basically the only variants are the headername to search in (or
datefield for the SENT-date related searches), the headervalue to match
on, and the mailbox to look into.
For sort, we simply expand the ORDER BY clause.
> Now we have to attach our headervalue pointer to each message row -
> if no matching header, then we need to left join so we get nulls.
No, no nulls.
> When adding additional headers for sorting or searching, each row from
> the dbmail_headername, dbmail_header, and dbmail_headervalue need to be
> joined in using a different table short name - thus the appending of the
> header we want to the h, hn, and hv shortnames.
That's the yucky part, that is not needed.
I just pushed a cleanup that should at least put us back to where we
were before the headertable change in terms of correctness. There are
very likely still some issues with search as Michael reported, but
hopefully no new issues.
--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
