Mailing List Archive: 2.0 branch slowly stabilising

2.0 branch slowly stabilising

Oct 8, 2003, 6:05 AM

Post #1 of 14 (2551 views)

Hi all,

The 2.0 branch of dbmail is becoming "stable". A lot of code has been
changed, almost all of it in the database layer.

* merging db code
As I mentioned about1 1/2, 2 months ago, the database code has been
merged together, leaving only 2 (MySQL and PostgreSQL) database
specific .c files of about 300 lines of C-code each. Implementing code
for additional database
systems (Oracle, DB2, Firebird, SAPdb etc) should be quite trivial.

* extra table
There is an extra table in the database (physmessage, for 'physical
message', as opposed to virtual message) which links messageblocks and
messages. This gives us great speed up with regard to copying messages.
In the old database scheme, copying a message meant copying all
messageblocks, which is slow. Now, only a new entry in the messages
table has to be made, which points to the same physmessage record.
This speed up was needed because copying occurs quite frequently; IMAP
has no MOVE command, so a move is done by a COPY followed by a delete.
This occurs often, for instance when a client does not really delete,
but moves a message to the Trash folder

The messages tables has been changed like this: it holds an extra
physmessage_id field. The messagesize, rfcsize and internal_date fields
have
been removed.

the physmessage table holds: messagesize, rfcsize and internal_date

the messageblks table now holds a physmessage_id instead of a
message_idnr

* extra column in users table
The users table now holds the column "currmail_size", which holds the
current
mailsize of a user. This prevents the calculation of the used quotum
every
time this figure is needed. currmail_size is now calculated every time
the
size of a user's mailbox changes.

I've done quite a lot of testing, using the MySQL backend. PostgreSQL
should be
tested further.
DBMail 2.0 is now ready to be tested by more people to squash all
remaining bugs.

Please pull the latest revision from cvs* and test it!

cheers,
Ilja

*
use: "cvs -d :pserver:cvs@lightning.fastxs.net:/cvsroot-dbmail co -r
dbmail_2_0 dbmail" to get dbmail 2.0

Re: 2.0 branch slowly stabilising [ In reply to ]

Magnus.Sundberg at dican

Oct 8, 2003, 7:07 AM

Post #2 of 14 (2511 views)

Permalink

Ilja Booij wrote:
> Hi all,
>
> The 2.0 branch of dbmail is becoming "stable". A lot of code has been
> changed, almost all of it in the database layer.
>
> * merging db code
> As I mentioned about1 1/2, 2 months ago, the database code has been
> merged together, leaving only 2 (MySQL and PostgreSQL) database specific
> .c files of about 300 lines of C-code each. Implementing code for
> additional database
> systems (Oracle, DB2, Firebird, SAPdb etc) should be quite trivial.
GREAT!
>
> * extra table
> There is an extra table in the database (physmessage, for 'physical
> message', as opposed to virtual message) which links messageblocks and
> messages. This gives us great speed up with regard to copying messages.
> In the old database scheme, copying a message meant copying all
> messageblocks, which is slow. Now, only a new entry in the messages
> table has to be made, which points to the same physmessage record.
> This speed up was needed because copying occurs quite frequently; IMAP
> has no MOVE command, so a move is done by a COPY followed by a delete.
> This occurs often, for instance when a client does not really delete,
> but moves a message to the Trash folder
>
> The messages tables has been changed like this: it holds an extra
> physmessage_id field. The messagesize, rfcsize and internal_date fields
> have
> been removed.
>
> the physmessage table holds: messagesize, rfcsize and internal_date
>
> the messageblks table now holds a physmessage_id instead of a message_idnr
>
This is probably a stupid question, but:
I do not understand the database structure now, can someone
please clarify the operation.
You have several database definitions, where you state FOREIGN
KEY ... REFERENCES ... ON DELETE CASCADE.
This is in the innodb part.
Well suppoe I do a move from folder A to folder B.
The record in the messages table is copied to also reference
folder B.
Next the message record that references folder A is deleted,
Well everything works all right, because the physmessage does not
contain a foreign key reference.
But what happens if an ordinary user choses to actually delete a
message. How do you know that it is not referenced anywhere else?
How do you delete messageblks records?

I remember some discussion we had about compressing the content
by having just one unique message block earlier, this should be
somewhat feasible to the body part of the message.

/Magnus

Re: 2.0 branch slowly stabilising [ In reply to ]

ilja at ic-s

Oct 8, 2003, 7:14 AM

Post #3 of 14 (2515 views)

Permalink

On Wednesday, Oct 8, 2003, at 16:07 Europe/Amsterdam, Magnus Sundberg
wrote:

> Ilja Booij wrote:
>> * extra table
>> There is an extra table in the database (physmessage, for 'physical
>> message', as opposed to virtual message) which links messageblocks
>> and messages. This gives us great speed up with regard to copying
>> messages. In the old database scheme, copying a message meant copying
>> all messageblocks, which is slow. Now, only a new entry in the
>> messages table has to be made, which points to the same physmessage
>> record.
>> This speed up was needed because copying occurs quite frequently;
>> IMAP has no MOVE command, so a move is done by a COPY followed by a
>> delete. This occurs often, for instance when a client does not really
>> delete, but moves a message to the Trash folder
>> The messages tables has been changed like this: it holds an extra
>> physmessage_id field. The messagesize, rfcsize and internal_date
>> fields have
>> been removed.
>> the physmessage table holds: messagesize, rfcsize and internal_date
>> the messageblks table now holds a physmessage_id instead of a
>> message_idnr
> This is probably a stupid question, but:
> I do not understand the database structure now, can someone please
> clarify the operation.
> You have several database definitions, where you state FOREIGN KEY ...
> REFERENCES ... ON DELETE CASCADE.
> This is in the innodb part.
> Well suppoe I do a move from folder A to folder B.
> The record in the messages table is copied to also reference folder B.
> Next the message record that references folder A is deleted,
> Well everything works all right, because the physmessage does not
> contain a foreign key reference.
> But what happens if an ordinary user choses to actually delete a
> message. How do you know that it is not referenced anywhere else?
> How do you delete messageblks records?

When a message is deleted only the record in the messages table is
removed. Then, it is checked whether there are any messages referencing
the same
physmessage record. If not, the physmessage and messageblks can be
deleted.

cheers,
ilja

Re: 2.0 branch slowly stabilising [ In reply to ]

paul at nfg

Oct 8, 2003, 11:24 AM

Post #4 of 14 (2519 views)

Permalink

Ilja,

I've just build and uploaded experimental debian packages for dbmail-2

deb http://debian.nfgd.net/debian experimental/

packages:

dbmail-mysql
dbmail-pgsql
dbmail-mysql-ldap
dbmail-pgsql-ldap

Maybe a silly idea; Would it be feasable to implement the database and
auth layers as plugins or shared libs allowing runtime selection through
the config file.

As more auth layers and database backends will be supported the
permutations of possible builds will explode, making it virtually
impossible for me to build a full set of debian packages. I'm sure
people working on packages for other distros will hit the same problem.

Ilja Booij wrote:
> Hi all,
>
> The 2.0 branch of dbmail is becoming "stable". A lot of code has been
> changed, almost all of it in the database layer.
>
> * merging db code
> As I mentioned about1 1/2, 2 months ago, the database code has been
> merged together, leaving only 2 (MySQL and PostgreSQL) database specific
> .c files of about 300 lines of C-code each. Implementing code for
> additional database
> systems (Oracle, DB2, Firebird, SAPdb etc) should be quite trivial.
>
> * extra table
> There is an extra table in the database (physmessage, for 'physical
> message', as opposed to virtual message) which links messageblocks and
> messages. This gives us great speed up with regard to copying messages.
> In the old database scheme, copying a message meant copying all
> messageblocks, which is slow. Now, only a new entry in the messages
> table has to be made, which points to the same physmessage record.
> This speed up was needed because copying occurs quite frequently; IMAP
> has no MOVE command, so a move is done by a COPY followed by a delete.
> This occurs often, for instance when a client does not really delete,
> but moves a message to the Trash folder
>
> The messages tables has been changed like this: it holds an extra
> physmessage_id field. The messagesize, rfcsize and internal_date fields
> have
> been removed.
>
> the physmessage table holds: messagesize, rfcsize and internal_date
>
> the messageblks table now holds a physmessage_id instead of a message_idnr
>
> * extra column in users table
> The users table now holds the column "currmail_size", which holds the
> current
> mailsize of a user. This prevents the calculation of the used quotum every
> time this figure is needed. currmail_size is now calculated every time the
> size of a user's mailbox changes.
>
> I've done quite a lot of testing, using the MySQL backend. PostgreSQL
> should be
> tested further.
> DBMail 2.0 is now ready to be tested by more people to squash all
> remaining bugs.
>
> Please pull the latest revision from cvs* and test it!
>
> cheers,
> Ilja
>
> *
> use: "cvs -d :pserver:cvs@lightning.fastxs.net:/cvsroot-dbmail co -r
> dbmail_2_0 dbmail" to get dbmail 2.0
>
>
>
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>

--
________________________________________________________________
Paul Stevens mailto:paul@nfg.nl
NET FACILITIES GROUP PGP: finger paul@nfg.nl
The Netherlands________________________________http://www.nfg.nl

Re: 2.0 branch slowly stabilising [ In reply to ]

matthew at zeut

Oct 8, 2003, 7:50 PM

Post #5 of 14 (2523 views)

Permalink

On Wed, 2003-10-08 at 09:05, Ilja Booij wrote:
> * extra table
> There is an extra table in the database (physmessage, for 'physical
> message', as opposed to virtual message) which links messageblocks and
> messages. This gives us great speed up with regard to copying messages.

This will also reduce the number of insert and delete statements sent to
the database, which (at least with postgresql) will help keep the files
smaller and reduce the number of vacuums required.

> The messages tables has been changed like this: it holds an extra
> physmessage_id field. The messagesize, rfcsize and internal_date fields
> have
> been removed.
>
> the physmessage table holds: messagesize, rfcsize and internal_date
>
> the messageblks table now holds a physmessage_id instead of a
> message_idnr

This is also good as it reduces the size of what needs to be updated
(inserted / deleted).

> * extra column in users table
> The users table now holds the column "currmail_size", which holds the
> current
> mailsize of a user. This prevents the calculation of the used quotum
> every
> time this figure is needed. currmail_size is now calculated every time
> the
> size of a user's mailbox changes.

cool.

> I've done quite a lot of testing, using the MySQL backend. PostgreSQL
> should be
> tested further.
> DBMail 2.0 is now ready to be tested by more people to squash all
> remaining bugs.

I look forward to testing the PostgreSQL backend. I'll let you know if
I find anything.

Re: 2.0 branch slowly stabilising [ In reply to ]

matthew at zeut

Oct 8, 2003, 8:02 PM

Post #6 of 14 (2519 views)

Permalink

On Wed, 2003-10-08 at 09:05, Ilja Booij wrote:
> Hi all,
>
> The 2.0 branch of dbmail is becoming "stable". A lot of code has been
> changed, almost all of it in the database layer.

Quick question, are there scripts available to change the database from
a 1.1 -> 2.0?

Re: 2.0 branch slowly stabilising [ In reply to ]

Magnus.Sundberg at dican

Oct 9, 2003, 12:52 AM

Post #7 of 14 (2519 views)

Permalink

Paul J Stevens wrote:
> Ilja,
>
>
> I've just build and uploaded experimental debian packages for dbmail-2
>
> deb http://debian.nfgd.net/debian experimental/
>
> packages:
>
> dbmail-mysql
> dbmail-pgsql
> dbmail-mysql-ldap
> dbmail-pgsql-ldap
>
>
> Maybe a silly idea; Would it be feasable to implement the database and
> auth layers as plugins or shared libs allowing runtime selection through
> the config file.
>
> As more auth layers and database backends will be supported the
> permutations of possible builds will explode, making it virtually
> impossible for me to build a full set of debian packages. I'm sure
> people working on packages for other distros will hit the same problem.
>

I beleive your idea is great, far from silly, it is probably the
only long term solution.

/Magnus

Re: 2.0 branch slowly stabilising [ In reply to ]

ilja at ic-s

Oct 9, 2003, 12:54 AM

Post #8 of 14 (2521 views)

Permalink

On Thursday, Oct 9, 2003, at 05:02 Europe/Amsterdam, Matthew T.
O'Connor wrote:

> On Wed, 2003-10-08 at 09:05, Ilja Booij wrote:
>> Hi all,
>>
>> The 2.0 branch of dbmail is becoming "stable". A lot of code has been
>> changed, almost all of it in the database layer.
>
> Quick question, are there scripts available to change the database from
> a 1.1 -> 2.0?
not yet. I'll be creating those very shortly (today or tomorrow)

Ilja

Re: 2.0 branch slowly stabilising [ In reply to ]

ilja at ic-s

Oct 9, 2003, 12:59 AM

Post #9 of 14 (2526 views)

Permalink

On Wednesday, Oct 8, 2003, at 20:24 Europe/Amsterdam, Paul J Stevens
wrote:
> Ilja,
>
>
> I've just build and uploaded experimental debian packages for dbmail-2
>
> deb http://debian.nfgd.net/debian experimental/
>
> packages:
>
> dbmail-mysql
> dbmail-pgsql
> dbmail-mysql-ldap
> dbmail-pgsql-ldap
great
> Maybe a silly idea; Would it be feasable to implement the database and
> auth layers as plugins or shared libs allowing runtime selection
> through the config file.
>
> As more auth layers and database backends will be supported the
> permutations of possible builds will explode, making it virtually
> impossible for me to build a full set of debian packages. I'm sure
> people working on packages for other distros will hit the same > problem.
you're completely right. Choosing the db and auth backends should be
done using
the configuration file.

I have some experience in writing/loading plugins
(using dlopen()), but if anybody here is a plugin guru, please lead the
way ;)

Ilja

Re: 2.0 branch slowly stabilising [ In reply to ]

xndbmail at xerus

Oct 9, 2003, 10:38 AM

Post #10 of 14 (2520 views)

Permalink

On Wed, Oct 08, 2003 at 08:24:32PM +0200, Paul J Stevens wrote:
> I've just build and uploaded experimental debian packages for dbmail-2
>
> deb http://debian.nfgd.net/debian experimental/
>
> packages:
>
> dbmail-mysql
> dbmail-pgsql
> dbmail-mysql-ldap
> dbmail-pgsql-ldap

Thanks. Do you have any plans to maintain woody packages of dbmail-2?

xn

Re: 2.0 branch slowly stabilising [ In reply to ]

xndbmail at xerus

Oct 9, 2003, 12:07 PM

Post #11 of 14 (2523 views)

Permalink

On Wed, Oct 08, 2003 at 03:05:45PM +0200, Ilja Booij wrote:
> * extra table
> There is an extra table in the database (physmessage, for 'physical
> message', as opposed to virtual message) which links messageblocks and
> messages. This gives us great speed up with regard to copying messages.
> In the old database scheme, copying a message meant copying all
> messageblocks, which is slow. Now, only a new entry in the messages
> table has to be made, which points to the same physmessage record.
> This speed up was needed because copying occurs quite frequently; IMAP
> has no MOVE command, so a move is done by a COPY followed by a delete.
> This occurs often, for instance when a client does not really delete,
> but moves a message to the Trash folder
>
> The messages tables has been changed like this: it holds an extra
> physmessage_id field. The messagesize, rfcsize and internal_date fields
> have
> been removed.
>
> the physmessage table holds: messagesize, rfcsize and internal_date
>
> the messageblks table now holds a physmessage_id instead of a
> message_idnr

So does delete now just delete a record from messages, then check to see
if any other messages refer to the physmessage, and if not delete the
physmessage and associated messageblks?
Are there any implications to having copy actually link rather than
create another copy? IMAP doesn't have any concept of editing, right?
So editing message creates a new message?

I noticed you changed the column naming scheme with physmessage. It
probably makes sense to make the column names consistent prior to the
2.0 release. (I prefer the singular table names, 'id' for primary
key, and tablename_id for foreign key nomenclature used for the new
phymessage table.)

xn

Re: 2.0 branch slowly stabilising [ In reply to ]

xndbmail at xerus

Oct 9, 2003, 2:14 PM

Post #12 of 14 (2524 views)

Permalink

On Wed, Oct 08, 2003 at 11:02:51PM -0400, Matthew T. O'Connor wrote:
> On Wed, 2003-10-08 at 09:05, Ilja Booij wrote:
> > Hi all,
> >
> > The 2.0 branch of dbmail is becoming "stable". A lot of code has been
> > changed, almost all of it in the database layer.
>
> Quick question, are there scripts available to change the database from
> a 1.1 -> 2.0?

I've attached one I've written for mysql/innodb. You have to change the
constraint id for messageblks as returned by SHOW CREATE TABLE
messageblks.

Backup before using this. Do not use on a production server. No
warranty. It may kill your cat.

xn

Re: 2.0 branch slowly stabilising [ In reply to ]

paul at nfg

Oct 10, 2003, 12:39 AM

Post #13 of 14 (2525 views)

Permalink

Maybe, maybe. Depends on the timeline for dbmail-2. I won't backport
until its released anyway.

Christian G. Warden wrote:
> On Wed, Oct 08, 2003 at 08:24:32PM +0200, Paul J Stevens wrote:
>
>>I've just build and uploaded experimental debian packages for dbmail-2
>>
>>deb http://debian.nfgd.net/debian experimental/
>>
>>packages:
>>
>>dbmail-mysql
>>dbmail-pgsql
>>dbmail-mysql-ldap
>>dbmail-pgsql-ldap
>
>
> Thanks. Do you have any plans to maintain woody packages of dbmail-2?
>
> xn
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>

--
________________________________________________________________
Paul Stevens mailto:paul@nfg.nl
NET FACILITIES GROUP PGP: finger paul@nfg.nl
The Netherlands________________________________http://www.nfg.nl

Re: 2.0 branch slowly stabilising [ In reply to ]

ilja at ic-s

Oct 10, 2003, 12:48 AM

Post #14 of 14 (2528 views)

Permalink

On Thursday, Oct 9, 2003, at 21:07 Europe/Amsterdam, Christian G.
Warden wrote:
> So does delete now just delete a record from messages, then check to
> see
> if any other messages refer to the physmessage, and if not delete the
> physmessage and associated messageblks?
That's right
> Are there any implications to having copy actually link rather than
> create another copy? IMAP doesn't have any concept of editing, right?
> So editing message creates a new message?
Right too
>
> I noticed you changed the column naming scheme with physmessage. It
> probably makes sense to make the column names consistent prior to the
> 2.0 release. (I prefer the singular table names, 'id' for primary
> key, and tablename_id for foreign key nomenclature used for the new
> phymessage table.)
I agree.

hmm, short answers.. early morning, need coffee...

cheers,
Ilja

Mailing List Archive

Mailing List Archive

Attached Files: