-- This script is used to upgrade mvnForum from beta3 to rc1
-- This sql script creates 2 new table : mvnforumWatch and mvnforumAttachment
--
-- Database: Oracle 9i



-- drop table mvnforumWatch;
-- drop table mvnforumAttachment;

--
-- drop sequence
--
-- drop sequence mvnforumWatch_seq;
-- drop sequence mvnforumAttachment_seq;


create table mvnforumWatch
(
   WatchID                        INT                            not null,
   MemberID                       INT                            not null,
   CategoryID                     INT                            not null,
   ForumID                        INT                            not null,
   ThreadID                       INT                            not null,
   WatchType                      INT                            not null,
   WatchOption                    INT                            not null,
   WatchStatus                    INT                            not null,
   WatchCreationDate              TIMESTAMP                      not null,
   WatchLastSentDate              TIMESTAMP                      not null,
   WatchEndDate                   TIMESTAMP                      not null,
   primary key (WatchID),
   unique (MemberID, CategoryID, ForumID, ThreadID)
);

create index Watch_MemberID_idx on mvnforumWatch
(
   MemberID
);

create index Watch_CategoryID_idx on mvnforumWatch
(
   CategoryID
);

create index Watch_ForumID_idx on mvnforumWatch
(
   ForumID
);

create index Watch_ThreadID_idx on mvnforumWatch
(
   ThreadID
);

create table mvnforumAttachment
(
   AttachID                       INT                            not null,
   PostID                         INT                            not null,
   MemberID                       INT                            not null,
   AttachFilename                 VARCHAR2(250)                  null,
   AttachFileSize                 INT                            not null,
   AttachMimeType                 VARCHAR2(70)                   null,
   AttachDesc                     VARCHAR2(250)                  null,
   AttachCreationIP               VARCHAR2(20)                   not null,
   AttachCreationDate             TIMESTAMP                      not null,
   AttachModifiedDate             TIMESTAMP                      not null,
   AttachDownloadCount            INT                            not null,
   AttachOption                   INT                            not null,
   AttachStatus                   INT                            not null,
   primary key (AttachID)
);

create index Attachment_PostID_idx on mvnforumAttachment
(
   PostID
);

create index Attachment_MemberID_idx on mvnforumAttachment
(
   MemberID
);

create sequence mvnforumWatch_seq;

create or replace trigger mvnforumWatch_trig_autoinc
before insert on mvnforumWatch
for each row
begin
  if (:new.WatchID is null) then 
    select mvnforumWatch_seq.nextval into :new.WatchID from dual;
  end if;
end;
/

create sequence mvnforumAttachment_seq;

create or replace trigger mvnforumAttach_trig_autoinc
before insert on mvnforumAttachment
for each row
begin
  if (:new.AttachID is null) then 
    select mvnforumAttachment_seq.nextval into :new.AttachID from dual;
  end if;
end;
/

commit;