/*
-- This script is used to upgrade mvnForum from beta3 to rc1
-- This sql script creates 2 new table : mvnforumWatch and mvnforumAttachment
--
-- Database: Interbase/Firebird
*/

/*
drop table mvnforumWatch;
drop table mvnforumAttachment;
*/

/* Create generator                                                          */
create generator mvnforumWatch_seq;
create generator mvnforumAttachment_seq;

commit;


create table mvnforumWatch
(
   WatchID                        INTEGER                        not null,
   MemberID                       INTEGER                        not null,
   CategoryID                     INTEGER                        not null,
   ForumID                        INTEGER                        not null,
   ThreadID                       INTEGER                        not null,
   WatchType                      INTEGER                        not null,
   WatchOption                    INTEGER                        not null,
   WatchStatus                    INTEGER                        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                       INTEGER                        not null,
   PostID                         INTEGER                        not null,
   MemberID                       INTEGER                        not null,
   AttachFilename                 VARCHAR(250)                   not null,
   AttachFileSize                 INTEGER                        not null,
   AttachMimeType                 VARCHAR(70)                    not null,
   AttachDesc                     VARCHAR(250)                   not null,
   AttachCreationIP               VARCHAR(20)                    not null,
   AttachCreationDate             TIMESTAMP                      not null,
   AttachModifiedDate             TIMESTAMP                      not null,
   AttachDownloadCount            INTEGER                        not null,
   AttachOption                   INTEGER                        not null,
   AttachStatus                   INTEGER                        not null,
   primary key (AttachID)
);

create index Attachment_PostID_idx on mvnforumAttachment
(
   PostID
);

create index Attachment_MemberID_idx on mvnforumAttachment
(
   MemberID
);


/* Change sentence finalizer to '!!'                                         */
set term ^;

create trigger mvnforumWatch_trig_autoinc for mvnforumWatch 
active before insert position 1
as
begin
  if (new.WatchID is null) then
     new.WatchID = gen_id(mvnforumWatch_seq, 1);
end
^

create trigger mvnforumAttachment_trig_autoinc for mvnforumAttachment 
active before insert position 1
as
begin
  if (new.AttachID is null) then
     new.AttachID = gen_id(mvnforumAttachment_seq, 1);
end
^

/* Return sentence finalizer to ';'                                          */
set term ;^

commit;