Re: [Dbmail] SQL suggestion
On Wed, 15 Nov 2006 21:47:49 -0500
Tom Allison [EMAIL PROTECTED] wrote:
Believe me, I'm not trying to be an expert here, so if I'm wrong I
could benefit from some explaining why... but...
instead of:
SELECT user_idnr FROM dbmail_users WHERE lower(userid) =
lower('[EMAIL PROTECTED]')
Wouldn't it make more sense to create these entries with a INSERT
INTO .. lower() so that you can take advantage of a userid index?
dbmail_users_name_idx UNIQUE, btree (userid)
dbmail= explain SELECT user_idnr FROM dbmail_users WHERE
lower(userid) = lower('[EMAIL PROTECTED]');
QUERY PLAN
--
Seq Scan on dbmail_users (cost=0.00..195.09 rows=1 width=8)
Filter: (lower((userid)::text) = '[EMAIL PROTECTED]'::text)
(2 rows)
dbmail= explain SELECT user_idnr FROM dbmail_users WHERE userid =
'[EMAIL PROTECTED]';
QUERY PLAN
-
Bitmap Heap Scan on dbmail_users (cost=57.00..61.01 rows=1 width=8)
Recheck Cond: ((userid)::text = '[EMAIL PROTECTED]'::text)
- Bitmap Index Scan on dbmail_users_name_idx (cost=0.00..57.00
rows=1 width=0)
Index Cond: ((userid)::text = '[EMAIL PROTECTED]'::text)
(4 rows)
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
In postgres, it's possible to create an index based on a function:
CREATE INDEX lower_userid ON dbmail_users (lower(userid));
At that point the query would take about the same amount of time and
userids can stay in different cases.
Jason
signature.asc
Description: PGP signature
Re: [Dbmail] Using DBMail in an existing web app
On Sat, 21 Oct 2006 22:55:31 +0200 Paul J Stevens [EMAIL PROTECTED] wrote: Jason Chu wrote: I believe my other two concerns about keeping emails and extra data/fields associated with emails still stand though. Jason, What you want is to associate meta-data with a physmessage and a user, not with a message in folder. That way you'd avoid the problems posed by copy. consider this: create table dbmail_annotation ( id bigint not null autoincrement, user_idnr bigint not null, physmessage_id bigint not null, message_partchar(16) not null default '', annotation text not null, primary key (id), unique (physmessage_id, id), foreign_key (physmessage_id) references dbmail_physmesssage(id) on update cascade on delete cascade ); this could be expanded with a ACL setup that would allow people to share access to an annotation with other users, like dbmail_acl does for mailboxes. Thanks Paul! That's exactly what I needed. I've spent a couple days thinking about this and playing around with it and I'm pretty sure we can pull it off. This will work way better than using a filesystem based mail store! Jason signature.asc Description: PGP signature
[Dbmail] Using DBMail in an existing web app
We're looking into using dbmail in our existing web app (http://oprius.com). Because our app is a CRM type of thing, we want to associate emails with other rows in the database (think associating emails with contacts). We're using Postgres as our backend. I've done a bunch of searching on the user and dev mailing list related to authentication as well as associating extra data to emails. I have a couple comments and a couple of questions. This is more of a brainstorm than anything else, so please feel free to pipe up. We use a different table setup for authentication. I know that I can write my own authentication functions and base them off of authsql.c to support this. We want to be able to archive all emails even if the user deletes them from the IMAP server, because we don't want the users to lose conversation history even if they're deleters. It'd be nice if these emails were just stored in the same tables as existing emails and just never shown by the IMAP server. I've noticed that dbmail doesn't delete emails until the maintenance script is run. I don't know exactly how this works though, could someone explain it? We might be able to use that... My biggest concern is, if contacts and extra data is associated with an email, what happens when the email is moved into a different folder? I know that IMAP doesn't have a move command, so we lose the dbmail_message row/id. Because there's a slight possibility that two people using our system could get the same email, we can't depend on the dbmail_physmessage being unique per user. Has anyone thought of how dbmail should act if an app developer wants to add extra fields/data related to an email? Jason signature.asc Description: PGP signature
Re: [Dbmail] Using DBMail in an existing web app
On Tue, 17 Oct 2006 21:12:23 -0700 Jason Chu [EMAIL PROTECTED] wrote: We're looking into using dbmail in our existing web app (http://oprius.com). Because our app is a CRM type of thing, we want to associate emails with other rows in the database (think associating emails with contacts). Ok, I was looking at the old 2.0 code. I just downloaded the 2.1 code and noticed a bunch of things have changed. Auth is still the same, just in a different place. I believe my other two concerns about keeping emails and extra data/fields associated with emails still stand though. Jason signature.asc Description: PGP signature
