Re: [Dbmail] Upgrading from RC3 to 1.1

[Paul J Stevens]

I've been preparing an mysql upgrade batch to upgrade a client from i
think rc2 or rc3.

I'm not sure. The source on the system is cvs and was last updated
december (rc4), but the active build dates to jan 21 2002 and I haven't
been around here that long.

The basic principle applies of course, and the sql-batch attached should
help you tune it to your own needs.

regards,

Paul


alter table aliases change client_id client_idnr bigint(21) DEFAULT '0' NOT 
NULL;
alter table aliases drop index alias_idnr;
alter table aliases add index alias (alias);

rename table mailbox to mailboxes;
alter table mailboxes change mailboxidnr mailbox_idnr bigint(21) DEFAULT '0' 
NOT NULL auto_increment;
alter table mailboxes change owneridnr owner_idnr bigint(21) DEFAULT '0' NOT 
NULL;

rename table message to messages;

alter table messages change messageidnr message_idnr bigint(21) DEFAULT '0' NOT 
NULL auto_increment;
alter table messages change mailboxidnr mailbox_idnr bigint(21) DEFAULT '0' NOT 
NULL;
alter table messages add rfcsize bigint(21) DEFAULT '0' NOT NULL;
alter table messages drop index messageidnr_2;
alter table messages add unique message_idnr_2 (message_idnr);
alter table messages drop index messageidnr;
alter table messages add index message_idnr (message_idnr);


rename table messageblk to messageblks;

alter table messageblks change messageblknr messageblk_idnr bigint(21) DEFAULT 
'0' NOT NULL auto_increment;
alter table messageblks change messageidnr message_idnr bigint(21) DEFAULT '0' 
NOT NULL;
alter table messageblks drop index messageblknr_2;
alter table messageblks add unique key messageblk_idnr_2 (messageblk_idnr);
alter table messageblks drop index messageblknr;
alter table messageblks add index messageblk_idnr (messageblk_idnr);


rename table user to users;

alter table users change useridnr user_idnr bigint(21) DEFAULT '0' NOT NULL 
auto_increment;
alter table users change clientid client_idnr bigint(21) DEFAULT '0' NOT NULL;
alter table users add encryption_type varchar(20) DEFAULT '' NOT NULL;
alter table users add last_login DATETIME DEFAULT '1979-11-03 22:05:58' NOT 
NULL;
alter table users add UNIQUE userid_2 (userid);
alter table users drop index useridnr;
alter table users add index user_idnr(user_idnr,userid);

CREATE TABLE auto_notifications (
   auto_notify_idnr bigint(21) default '0' not null auto_increment,
   user_idnr bigint(21) DEFAULT '0' NOT NULL,
   notify_address VARCHAR(100),
   PRIMARY KEY (auto_notify_idnr)
);

CREATE TABLE auto_replies (
   auto_reply_idnr bigint(21) DEFAULT '0' NOT NULL auto_increment,
   user_idnr bigint(21) DEFAULT '0' NOT NULL,
   reply_body mediumtext,
   PRIMARY KEY (auto_reply_idnr)
);

CREATE TABLE tmpmessage (
       message_idnr bigint(21) DEFAULT '0' NOT NULL auto_increment,
       mailbox_idnr int(21) DEFAULT '0' NOT NULL,
       messagesize bigint(21) DEFAULT '0' NOT NULL,
       seen_flag tinyint(1) default '0' not null,
       answered_flag tinyint(1) default '0' not null,
       deleted_flag tinyint(1) default '0' not null,
       flagged_flag tinyint(1) default '0' not null,
       recent_flag tinyint(1) default '0' not null,
       draft_flag tinyint(1) default '0' not null,
       unique_id varchar(70) NOT NULL,
       internal_date datetime default '0' not null,
       status tinyint(3) unsigned zerofill default '000' not null,

       PRIMARY KEY (message_idnr),
       KEY message_idnr (message_idnr),
       UNIQUE message_idnr_2 (message_idnr)
);

CREATE TABLE tmpmessageblk (
       messageblk_idnr bigint(21) DEFAULT '0' NOT NULL auto_increment,
       message_idnr bigint(21) DEFAULT '0' NOT NULL,
       messageblk longtext NOT NULL,
       blocksize bigint(21) DEFAULT '0' NOT NULL,

       PRIMARY KEY (messageblk_idnr),
       KEY messageblk_idnr (messageblk_idnr),
       KEY msg_index (message_idnr),
       UNIQUE messageblk_idnr_2 (messageblk_idnr)
);