-- $Header: /cvsroot/mvnforum/mvnforum/sql/upgrade/1_0_0_rc3_to_1_0_0_rc4/mvnForum_update_JDBC.sql,v 1.4 2005/01/27 09:27:34 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.4 $
-- $Date: 2005/01/27 09:27:34 $
--
-- This script is used to upgrade mvnForum from RC2/RC3 to RC4
-- This script creates 3 new table : mvnforumMessage
--                                   mvnforumMessageStatistics
--                                   mvnforumPmAttachment
--                                   mvnforumPmAttachMessage
--
-- Database: Generic JDBC 



-- drop table mvnforumMessage;
-- drop table mvnforumMessageStatistics;
-- drop table mvnforumPmAttachment;
-- drop table mvnforumPmAttachMessage;


CREATE TABLE mvnforumMessage
(
   MessageID                      INT                            not null AUTO_INCREMENT,
   FolderName                     VARCHAR(30)                    not null,
   MemberID                       INT                            not null,
   MessageSenderID                INT                            not null,
   MessageSenderName              VARCHAR(30)                    not null,
   MessageToList                  VARCHAR(250)                   not null,
   MessageCcList                  VARCHAR(250),
   MessageBccList                 VARCHAR(250),
   MessageTopic                   VARCHAR(250)                   not null,
   MessageBody                    LONGVARCHAR                    not null,
   MessageType                    INT                            not null,
   MessageOption                  INT                            not null,
   MessageStatus                  INT                            not null,
   MessageReadStatus              INT                            not null,
   MessageNotify                  INT                            not null,
   MessageIcon                    VARCHAR(10)                    not null,
   MessageAttachCount             INT                            not null,
   MessageIP                      VARCHAR(20)                    not null,
   MessageCreationDate            TIMESTAMP                      not null,
   primary key (MessageID)
);

CREATE INDEX Message_1_idx on mvnforumMessage
(
   FolderName,
   MemberID
);

CREATE INDEX Message_2_idx on mvnforumMessage
(
   MessageSenderID
);

CREATE TABLE mvnforumMessageStatistics
(
   FromID                         INT                            not null,
   ToID                           INT                            not null,
   MessageCreationDate            TIMESTAMP                      not null,
   MessageAttachCount             INT                            not null,
   MessageType                    INT                            not null,
   MessageOption                  INT                            not null,
   MessageStatus                  INT                            not null
);

CREATE INDEX MessageStatistics_1_idx on mvnforumMessageStatistics
(
   FromID
);

CREATE INDEX MessageStatistics_2_idx on mvnforumMessageStatistics
(
   ToID
);

CREATE TABLE mvnforumPmAttachment
(
   PmAttachID                     INT                            not null AUTO_INCREMENT,
   MemberID                       INT                            not null,
   PmAttachFilename               VARCHAR(250)                   not null,
   PmAttachFileSize               INT                            not null,
   PmAttachMimeType               VARCHAR(70)                    not null,
   PmAttachDesc                   LONGVARCHAR                    not null,
   PmAttachCreationIP             VARCHAR(20)                    not null,
   PmAttachCreationDate           TIMESTAMP                      not null,
   PmAttachModifiedDate           TIMESTAMP                      not null,
   PmAttachDownloadCount          INT                            not null,
   PmAttachOption                 INT                            not null,
   PmAttachStatus                 INT                            not null,
   primary key (PmAttachID)
);

CREATE INDEX PmAttachment_1_idx on mvnforumPmAttachment
(
   MemberID
);

CREATE TABLE mvnforumPmAttachMessage
(
   MessageID                      INT                            not null,
   PmAttachID                     INT                            not null,
   RelationType                   INT                            not null,
   RelationOption                 INT                            not null,
   RelationStatus                 INT                            not null,
   primary key (MessageID, PmAttachID)
);

UPDATE mvnforumMessageFolder SET FolderOrder = 2 WHERE FolderName = 'Sent';

ALTER TABLE mvnforumMessageFolder ADD FolderStatus INT NOT NULL DEFAULT 0 AFTER FolderOrder;

ALTER TABLE mvnforumMessageFolder ADD FolderOption INT NOT NULL DEFAULT 0 AFTER FolderStatus;

ALTER TABLE mvnforumMessageFolder ADD FolderType INT NOT NULL DEFAULT 0 AFTER FolderOption;

ALTER TABLE mvnforumThread ADD ThreadAttachCount INT NOT NULL DEFAULT 0 ;

ALTER TABLE mvnforumAttachment CHANGE AttachDesc AttachDesc LONGVARCHAR NOT NULL ;

ALTER TABLE mvnforumMember CHANGE MemberPassword MemberPassword VARCHAR(200) NOT NULL ;

ALTER TABLE mvnforumMember ADD MemberExpireDate TIMESTAMP AFTER MemberModifiedDate ;
UPDATE mvnforumMember SET MemberExpireDate = MemberCreationDate ;