-- $Header: /cvsroot/mvnforum/mvnforum/sql/upgrade/1_0_0_rc3_to_1_0_0_rc4/mvnforum_update_sqlserver.sql,v 1.5 2005/01/27 09:27:34 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.5 $
-- $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: SQL Server



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


CREATE TABLE mvnforumMessage
(
   MessageID                      INT                            not null IDENTITY,
   FolderName                     NVARCHAR(30)                   not null,
   MemberID                       INT                            not null,
   MessageSenderID                INT                            not null,
   MessageSenderName              NVARCHAR(30)                   not null,
   MessageToList                  NVARCHAR(250)                  not null,
   MessageCcList                  NVARCHAR(250),
   MessageBccList                 NVARCHAR(250),
   MessageTopic                   NVARCHAR(250)                  not null,
   MessageBody                    NTEXT                          not null,
   MessageType                    INT                            not null,
   MessageOption                  INT                            not null,
   MessageStatus                  INT                            not null,
   MessageReadStatus              INT                            not null,
   MessageNotify                  INT                            not null,
   MessageIcon                    NVARCHAR(10)                   not null,
   MessageAttachCount             INT                            not null,
   MessageIP                      NVARCHAR(20)                   not null,
   MessageCreationDate            DATETIME                       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            DATETIME                       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 IDENTITY,
   MemberID                       INT                            not null,
   PmAttachFilename               NVARCHAR(250)                  not null,
   PmAttachFileSize               INT                            not null,
   PmAttachMimeType               NVARCHAR(70)                   not null,
   PmAttachDesc                   NTEXT                          not null,
   PmAttachCreationIP             NVARCHAR(20)                   not null,
   PmAttachCreationDate           DATETIME                       not null,
   PmAttachModifiedDate           DATETIME                       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;

ALTER TABLE mvnforumMessageFolder ADD FolderOption INT NOT NULL DEFAULT 0;

ALTER TABLE mvnforumMessageFolder ADD FolderType INT NOT NULL DEFAULT 0;

ALTER TABLE mvnforumThread ADD ThreadAttachCount INT NOT NULL DEFAULT 0;

-- Note: When the AttachDesc's date type was NTEXT, comment this query to upgrade
-- Because the altered column cannot be a column with a text, image, ntext, or timestamp data type.
ALTER TABLE mvnforumAttachment ALTER COLUMN AttachDesc NTEXT NOT NULL;

ALTER TABLE mvnforumMember ALTER COLUMN MemberPassword NVARCHAR(200) NOT NULL;

ALTER TABLE mvnforumMember ADD MemberExpireDate DATETIME;
UPDATE mvnforumMember SET MemberExpireDate = MemberCreationDate;

