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



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


create table mvnforumWatch
(
   WatchID                        INT                            not null IDENTITY,
   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              DATETIME                       not null,
   WatchLastSentDate              DATETIME                       not null,
   WatchEndDate                   DATETIME                       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 IDENTITY,
   PostID                         INT                            not null,
   MemberID                       INT                            not null,
   AttachFilename                 NVARCHAR(250)                  not null,
   AttachFileSize                 INT                            not null,
   AttachMimeType                 NVARCHAR(70)                   not null,
   AttachDesc                     NVARCHAR(250)                  not null,
   AttachCreationIP               NVARCHAR(20)                   not null,
   AttachCreationDate             DATETIME                       not null,
   AttachModifiedDate             DATETIME                       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
);