Mailing List Archive

On Mon, 2003-06-16 at 12:36, lou wrote:

> I think using references constraints(foreign keys) will guarantee that the message is
> going to the right user in the right mailbox and things like unconnected message wont
> exist rather than undelivered, at least the undelivered might stay in the MTA's queue for
> second retry where the unconnected will be deleted.
>
> outs: mysql doesnt support it no portability :)
>
> Anyone seeing something wrong in that except the above?
>
>
> cheers
>

MySQL using INNODB tables supports foreign key constraints just fine.

And I believe that MySQL quietly ignores the foreign key keywords if the
database doesn't support them, so that you can use the same code on MyISAM
or InnoDB -- you just have be aware that under MyISAM funky things can
happen :-P

Aaron


On Mon, 16 Jun 2003, Ryan Butler wrote:

> On Mon, 2003-06-16 at 12:36, lou wrote:
>
> > I think using references constraints(foreign keys) will guarantee that the message is
> > going to the right user in the right mailbox and things like unconnected message wont
> > exist rather than undelivered, at least the undelivered might stay in the MTA's queue for
> > second retry where the unconnected will be deleted.
> >
> > outs: mysql doesnt support it no portability :)
> >
> > Anyone seeing something wrong in that except the above?
> >
> >
> > cheers
> >
>
> MySQL using INNODB tables supports foreign key constraints just fine.
>
>

Hello,

> CREATE SEQUENCE alias_idnr_seq;
> CREATE TABLE aliases (
> alias_idnr INT8 DEFAULT nextval('alias_idnr_seq'),
> ...
> -> deliver_to VARCHAR(250) REFERENCE users(user_idnr),
> ...
> PRIMARY KEY (alias_idnr)
> );


This would break all non-INBOX deliveries.


> CREATE TABLE mailboxes (
> mailbox_idnr INT8 DEFAULT nextval('mailbox_idnr_seq'),
>
> -> owner_idnr INT8 REFERENCE users(user_idnr),
> ....
> }

This may have implications with shared folders, but those
aren't implimented just yet either, so...


> CREATE TABLE messages (
> message_idnr INT8 DEFAULT nextval('message_idnr_seq'),
>
> -> mailbox_idnr INT8 REFERENCE mailboxes(mailbox_idnr),
> ...
> }

I've not looked at Aaron's recent filtering patch, but this
may have reprecussions there - I think at one time there was
a possibility of setting mailbox_idnr to 0 for pre-filtered
messages, and updating later.

Another note: along with all the foreign keys, which are
a good idea where appropriate/possible, quite a few of the
indexes that have appeared on the list can be dropped. A
primary key already gives you a unique index on the column,
and a foreign key gives you an index (non-unique) as well.



--
Jesse Norell
jesse (at) kci.net

In some email I received from lou <lou@0xffff.org> on Mon, 16 Jun 2003 18:36:45 +0100,
wrote:

> Another thought while i was going through dbmail pgsql scheme and dbmail-maintenance
> integrity check.

Okay,I'm sorry Roel didnt read the comment (nah bad editor i can hardly see anything
grey ;-( )

/* $Id: create_tables.pgsql,v 1.17 2003/03/17 16:04:09 roel Exp $
Todo:
- add foreign key constrains (will prevent inconsistence
- add / remove indexes depending on performance
*/

2 files attached
pgsql - add-foreignkeys.pgsql
mysql - same file should work. (only for InnoDB)

I just didnt notice it until I tried it on my database.

cheers

In some email I received from "Jesse Norell" <jesse@kci.net> on Mon, 16 Jun 2003 12:56:49
-0600 (MDT), wrote:

>
> Hello,
>
[...]
>
> This would break all non-INBOX deliveries.

Agree.

[...]
> This may have implications with shared folders, but those
> aren't implimented just yet either, so...

Please share:)
Somewhere i have a design for shared folders, I needed it fast so I manage to alter
WeDBmail to do so, i didnt get any implication with it.
(considering that i didnt have to go along the rfc standart, right?)

>
[...]
> I've not looked at Aaron's recent filtering patch, but this
> may have reprecussions there - I think at one time there was
> a possibility of setting mailbox_idnr to 0 for pre-filtered
> messages, and updating later.

I understand, but we can still force ID 0 for temporary messages or
messages which are being filtered? and we keep consistency with the other objects.

I'll look further in the code to see if there are any other implications.

> Another note: along with all the foreign keys, which are
> a good idea where appropriate/possible, quite a few of the
> indexes that have appeared on the list can be dropped. A
> primary key already gives you a unique index on the column,
> and a foreign key gives you an index (non-unique) as well.

Correct. That's sounds like a call from db scheme cleaning ;)

cheers

In some email I received from Aaron Stone <aaron@engr.Paly.NET> on Mon, 16 Jun 2003
11:46:45 -0700 (PDT), wrote:

> And I believe that MySQL quietly ignores the foreign key keywords if the
> database doesn't support them, so that you can use the same code on MyISAM
> or InnoDB -- you just have be aware that under MyISAM funky things can
> happen :-P

Yes, it can be worse, looks like I'm cursed to have InnoDB and not able to add constraints
;).

if someone would like to check the add-constraints.pgsql on mysql database,
all i get is Can't create table './dbmailfk/aliases.frm' (errno: 150).


cheers

In some email I received from lou <lou@0xffff.org> on Mon, 16 Jun 2003 19:58:57 +0100,
wrote:

somewhere in the middle
> -- messageblks -> messages constraint
> ALTER TABLE messageblks ADD CONSTRAINT messageblk_idnr_fk FOREIGN KEY (messageblk_idnr)
> REFERENCES messages(message_idnr) MATCH FULL;

should be

> -- messageblks -> messages constraint
> ALTER TABLE messageblks ADD CONSTRAINT messageblk_idnr_fk FOREIGN KEY (message_idnr)
> REFERENCES messages(message_idnr) MATCH FULL;

cheers

Hmm, actually, there's a pretty big chunk of code in my patch that allows
delivery to a non-existant mailbox and creates it in the process. The way
I set it up, the mailbox is looked up first with a simple select and if it
does not exist, an attempt is made to create the mailbox. The code sort of
"double falls back" so that if it can successfully create the box and
retrieve the mailbox_id, the message is delivered and if that fails, the
message is bounced (or delayed? don't recall...).

The interaction with this database schema is to add another check that
requires the mailbox to exist before the message insertion can succeed,
and that's fine because if the message insertion fails the message is
bounced to the MTA and can be retried later.

About my use of 0 as the "magic id" for the "dbmail built-in account" I
think that might need to change to 1 because it actually took two tries to
force an id of 0 in an auto_increment column. If we get rid of
auto_increment and use our own unique primary keys, then this isn't a
problem anymore. In any case, I very much like the idea of requiring a
built in account, basically a postmaster account, because in my
experience, every mail system needs some way of collecting the junk that's
bouncing around and sticking it someplace!

Aaron


On Mon, 16 Jun 2003, lou wrote:

> In some email I received from "Jesse Norell" <jesse@kci.net> on Mon, 16 Jun 2003 12:56:49
> -0600 (MDT), wrote:
>
> >
> > Hello,
> >
> [...]
> >
> > This would break all non-INBOX deliveries.
>
> Agree.
>
> [...]
> > This may have implications with shared folders, but those
> > aren't implimented just yet either, so...
>
> Please share:)
> Somewhere i have a design for shared folders, I needed it fast so I manage to alter
> WeDBmail to do so, i didnt get any implication with it.
> (considering that i didnt have to go along the rfc standart, right?)
>
> >
> [...]
> > I've not looked at Aaron's recent filtering patch, but this
> > may have reprecussions there - I think at one time there was
> > a possibility of setting mailbox_idnr to 0 for pre-filtered
> > messages, and updating later.
>
> I understand, but we can still force ID 0 for temporary messages or
> messages which are being filtered? and we keep consistency with the other objects.
>
> I'll look further in the code to see if there are any other implications.
>
> > Another note: along with all the foreign keys, which are
> > a good idea where appropriate/possible, quite a few of the
> > indexes that have appeared on the list can be dropped. A
> > primary key already gives you a unique index on the column,
> > and a foreign key gives you an index (non-unique) as well.
>
> Correct. That's sounds like a call from db scheme cleaning ;)
>
> cheers
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>