-- $Header: /cvsroot/mvnforum/mvnforum/sql/upgrade/1_0_0_rc3_to_1_0_0_rc4/mvnForum_update_oracle9.sql,v 1.6 2005/02/04 05:24:33 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.6 $
-- $Date: 2005/02/04 05:24:33 $
--
-- This script is used to upgrade mvnForum from RC2/RC3 to RC4
-- This script creates 3 new table : mvnforumMessage
--                                   mvnforumMessageStatistics
--                                   mvnforumPmAttachment
--                                   mvnforumPmAttachMessage
--
-- Database: Oracle 9i or Oracle 10g 
-- Note: in ALTER TABLE "DEFAULT 0" must come before "NOT NULL" 



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

--
-- drop sequence
--
-- drop sequence mvnforumMessage_seq;
-- drop sequence mvnforumPmAttachment_seq;


--
-- create sequences
--
create sequence mvnforumMessage_seq;
create sequence mvnforumPmAttachment_seq;


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

create or replace trigger mvnforumMessage_trig_autoinc
before insert on mvnforumMessage
for each row
begin
  if (:new.MessageID is null) then 
    select mvnforumMessage_seq.nextval into :new.MessageID from dual;
  end if;
end;
/

create or replace trigger mvnforumPmAttach_trig_autoinc
before insert on mvnforumPmAttachment
for each row
begin
  if (:new.PmAttachID is null) then 
    select mvnforumPmAttachment_seq.nextval into :new.PmAttachID from dual;
  end if;
end;
/

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

ALTER TABLE mvnforumMessageFolder ADD FolderStatus INT DEFAULT 0 NOT NULL ;

ALTER TABLE mvnforumMessageFolder ADD FolderOption INT DEFAULT 0 NOT NULL ;

ALTER TABLE mvnforumMessageFolder ADD FolderType INT DEFAULT 0 NOT NULL ;

ALTER TABLE mvnforumThread ADD ThreadAttachCount INT DEFAULT 0 NOT NULL ;

ALTER TABLE mvnforumAttachment MODIFY AttachDesc LONG VARCHAR ;

ALTER TABLE mvnforumMember MODIFY MemberPassword VARCHAR2(200) ;

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

commit;