[Dbmail-dev] Suggested schema changes (Was: Some comments)

[Hans Kristian Rosbach]

> I too am not familiar with the 2.0 schema yet.. And I do think that
> some of the stuff that has changed since 1.0 were totally useless.
> I'm going to get digging to see wether this is true or not.
> 
> I might also propose a whole new schema, but I do not expect it to
> be used by others than me.

Ok, I've looked it over again..

I see no use for the dbmail_physmessage table. It can as far as I can
see be merged into dbmail_messages with only very minor fixes.

Advantages:
-Simplified schema
-Decreased storage size for database
-Increased speed

Disadvantages:
-none?

I also found an index that was not needed due to duplicates in a
multi-column index. (dbmail_messageblks_physmessage_idx)

I added indexing on deleted_flag and internal_date. This is useful
for webmail atleast.

And why do we have answered_flag, deleted_flag and so on in
dbmail_mailboxes?

I also think we lack some more indexes, but I have to do some
research before I put it in the diff.

Please take a look and comment.

-=Dead2=-

--- create_tables.pgsql 2004-09-06 16:05:57.000000000 +0200
+++ create_tables.new.pgsql     2004-11-01 11:41:33.935758496 +0100
@@ -51,12 +51,6 @@
    mailbox_idnr INT8 DEFAULT nextval('dbmail_mailbox_idnr_seq'),
    owner_idnr INT8 REFERENCES dbmail_users(user_idnr) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
-   seen_flag INT2 DEFAULT '0' NOT NULL,
-   answered_flag INT2 DEFAULT '0' NOT NULL,
-   deleted_flag INT2 DEFAULT '0' NOT NULL,
-   flagged_flag INT2 DEFAULT '0' NOT NULL,
-   recent_flag INT2 DEFAULT '0' NOT NULL,
-   draft_flag INT2 DEFAULT '0' NOT NULL,
    no_inferiors INT2 DEFAULT '0' NOT NULL,
    no_select INT2 DEFAULT '0' NOT NULL,
    permission INT2 DEFAULT '2' NOT NULL,
@@ -90,22 +84,11 @@
     PRIMARY KEY (user_id, mailbox_id)
 );
 
-CREATE SEQUENCE dbmail_physmessage_id_seq;
-CREATE TABLE dbmail_physmessage (
-   id INT8 DEFAULT nextval('dbmail_physmessage_id_seq'),
-   messagesize INT8 DEFAULT '0' NOT NULL,   
-   rfcsize INT8 DEFAULT '0' NOT NULL,
-   internal_date TIMESTAMP WITHOUT TIME ZONE,
-   PRIMARY KEY(id)
-);
-
 CREATE SEQUENCE dbmail_message_idnr_seq;
 CREATE TABLE dbmail_messages (
    message_idnr INT8 DEFAULT nextval('dbmail_message_idnr_seq'),
    mailbox_idnr INT8 REFERENCES dbmail_mailboxes(mailbox_idnr)
        ON DELETE CASCADE,
-   physmessage_id INT8 REFERENCES dbmail_physmessage(id)
-       ON DELETE CASCADE,
    seen_flag INT2 DEFAULT '0' NOT NULL,
    answered_flag INT2 DEFAULT '0' NOT NULL,
    deleted_flag INT2 DEFAULT '0' NOT NULL,
@@ -114,31 +97,32 @@
    draft_flag INT2 DEFAULT '0' NOT NULL,
    unique_id varchar(70) NOT NULL,
    status INT2 DEFAULT '0' NOT NULL,
+   messagesize INT8 DEFAULT '0' NOT NULL,   
+   rfcsize INT8 DEFAULT '0' NOT NULL,
+   internal_date TIMESTAMP WITHOUT TIME ZONE,
    PRIMARY KEY (message_idnr)
 );
 CREATE INDEX dbmail_messages_mailbox_idx ON dbmail_messages(mailbox_idnr);
-CREATE INDEX dbmail_messages_physmessage_idx 
-       ON dbmail_messages(physmessage_id);
 CREATE INDEX dbmail_messages_seen_flag_idx ON dbmail_messages(seen_flag);
+CREATE INDEX dbmail_messages_deleted_flag_idx ON dbmail_messages(deleted_flag);
 CREATE INDEX dbmail_messages_unique_id_idx ON dbmail_messages(unique_id);
 CREATE INDEX dbmail_messages_status_idx ON dbmail_messages(status);
+CREATE INDEX dbmail_messages_internal_date_idx ON 
dbmail_messages(internal_date);
 CREATE INDEX dbmail_messages_status_notdeleted_idx 
        ON dbmail_messages(status) WHERE status < '2';
 
 CREATE SEQUENCE dbmail_messageblk_idnr_seq;
 CREATE TABLE dbmail_messageblks (
    messageblk_idnr INT8 DEFAULT nextval('dbmail_messageblk_idnr_seq'),
-   physmessage_id INT8 REFERENCES dbmail_physmessage(id)
+   message_id INT8 REFERENCES dbmail_messages(message_idnr)
        ON DELETE CASCADE,
    messageblk TEXT NOT NULL,
    blocksize INT8 DEFAULT '0' NOT NULL,
    is_header INT2 DEFAULT '0' NOT NULL,
    PRIMARY KEY (messageblk_idnr)
 );
-CREATE INDEX dbmail_messageblks_physmessage_idx 
-       ON dbmail_messageblks(physmessage_id);
-CREATE INDEX dbmail_messageblks_physmessage_is_header_idx 
-       ON dbmail_messageblks(physmessage_id, is_header);
+CREATE INDEX dbmail_messageblks_message_is_header_idx 
+       ON dbmail_messageblks(message_id, is_header);
 
 CREATE TABLE dbmail_auto_notifications (
    user_idnr INT8 REFERENCES dbmail_users(user_idnr) ON DELETE CASCADE,