Re: [Dbmail-dev] 2.0 branch slowly stabilising

[Christian G. Warden]

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

DROP TABLE IF EXISTS physmessage;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE
        physmessage
(
        id BIGINT(21) NOT NULL PRIMARY KEY AUTO_INCREMENT,
        messagesize BIGINT(21) NOT NULL,
        rfcsize BIGINT(21) NOT NULL,
        internal_date DATETIME NOT NULL
);

INSERT INTO
        physmessage
(
        id,
        messagesize,
        rfcsize,
        internal_date
)
SELECT
        message_idnr,
        messagesize,
        rfcsize,
        internal_date
FROM
        messages; 

ALTER TABLE
        messages
ADD
        physmessage_id BIGINT(21) NOT NULL,
ADD INDEX
        (physmessage_id),
ADD FOREIGN KEY
        (physmessage_id) REFERENCES physmessage(id) ON DELETE CASCADE,
DROP
        messagesize,
DROP
        rfcsize,
DROP
        internal_date;

UPDATE
        messages
SET
        physmessage_id = message_idnr;

ALTER TABLE
        messageblks
DROP FOREIGN KEY
        0_2014, ###### CHANGE ME ######
CHANGE
        message_idnr physmessage_id BIGINT(21) NOT NULL;

ALTER TABLE
        messageblks
ADD FOREIGN KEY
        (physmessage_id) REFERENCES physmessage (id) ON DELETE CASCADE;

CREATE TEMPORARY TABLE
        tmp_users
SELECT
        u.*,
        sum(p.messagesize) AS curmail_size
FROM
        users u
LEFT JOIN mailboxes b ON b.owner_idnr = u.user_idnr
LEFT JOIN messages m ON m.mailbox_idnr = b.mailbox_idnr
LEFT JOIN physmessage p ON m.physmessage_id = p.id
GROUP BY
        u.user_idnr;

ALTER TABLE
        users
ADD
        curmail_size BIGINT(21) NOT NULL;

BEGIN;
DELETE FROM users;
INSERT INTO users SELECT * FROM tmp_users;
COMMIT;
DROP TABLE tmp_users;

SET FOREIGN_KEY_CHECKS=1;