/*
-- $Header: /cvsroot/mvnforum/mvnforum/sql/upgrade/1_0_0_rc3_to_1_0_0_rc4/mvnForum_update_interbase.sql,v 1.5 2005/01/29 11:39:50 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.5 $
-- $Date: 2005/01/29 11:39:50 $
--
-- This script is used to upgrade mvnForum from RC2/RC3 to RC4
-- This script creates 3 new table : mvnforumMessage
--                                   mvnforumMessageStatistics
--                                   mvnforumPmAttachment
--                                   mvnforumPmAttachMessage
--
-- Database: Interbase/Firebird
*/

/*
drop table mvnforumMessage;
drop table mvnforumMessageStatistics;
drop table mvnforumPmAttachment;
drop table mvnforumPmAttachMessage;
*/

create generator mvnforumMessage_seq;
create generator mvnforumPmAttachment_seq;

CREATE TABLE mvnforumMessage
(
   MessageID                      INTEGER                        not null,
   FolderName                     VARCHAR(30)                    not null,
   MemberID                       INTEGER                        not null,
   MessageSenderID                INTEGER                        not null,
   MessageSenderName              VARCHAR(30)                    not null,
   MessageToList                  VARCHAR(250)                   not null,
   MessageCcList                  VARCHAR(250),
   MessageBccList                 VARCHAR(250),
   MessageTopic                   VARCHAR(250)                   not null,
   MessageBody                    TEXT                           not null,
   MessageType                    INTEGER                        not null,
   MessageOption                  INTEGER                        not null,
   MessageStatus                  INTEGER                        not null,
   MessageReadStatus              INTEGER                        not null,
   MessageNotify                  INTEGER                        not null,
   MessageIcon                    VARCHAR(10)                    not null,
   MessageAttachCount             INTEGER                        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                         INTEGER                            not null,
   ToID                           INTEGER                            not null,
   MessageCreationDate            TIMESTAMP                          not null,
   MessageAttachCount             INTEGER                            not null,
   MessageType                    INTEGER                            not null,
   MessageOption                  INTEGER                            not null,
   MessageStatus                  INTEGER                            not null
);

CREATE INDEX MessageStatistics_1_idx on mvnforumMessageStatistics
(
   FromID
);

CREATE INDEX MessageStatistics_2_idx on mvnforumMessageStatistics
(
   ToID
);

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

CREATE INDEX PmAttachment_1_idx on mvnforumPmAttachment
(
   MemberID
);

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

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

ALTER TABLE mvnforumMessageFolder ADD FolderStatus INTEGER DEFAULT 0 NOT NULL;

ALTER TABLE mvnforumMessageFolder ADD FolderOption INTEGER DEFAULT 0 NOT NULL;

ALTER TABLE mvnforumMessageFolder ADD FolderType INTEGER DEFAULT 0 NOT NULL;

ALTER TABLE mvnforumThread ADD ThreadAttachCount INTEGER DEFAULT 0 NOT NULL;

ALTER TABLE mvnforumAttachment ALTER AttachDesc TYPE TEXT ;

ALTER TABLE mvnforumMember ALTER MemberPassword TYPE VARCHAR(200) ;

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

/* Create triggers */

set term ^;
create trigger mvnforumMessage_trig_autoinc for mvnforumMessage 
active before insert position 1
as
begin
  if (new.MessageID is null) then
     new.MessageID = gen_id(mvnforumMessage_seq, 1);
end
^

create trigger mvnforumPmAttach_trig_autoinc for mvnforumPmAttachment 
active before insert position 1
as
begin
  if (new.PmAttachID is null) then
     new.PmAttachID = gen_id(mvnforumPmAttachment_seq, 1);
end
^
/* Return sentence finalizer to ';'                                          */
set term ;^

commit;