/*
-- $Header: /cvsroot/mvnforum/mvnforum/sql/upgrade/1_0_0_rc1_to_1_0_0_rc2/mvnForum_update_interbase.sql,v 1.2 2003/10/26 19:16:25 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.2 $
-- $Date: 2003/10/26 19:16:25 $
--
-- This script is used to upgrade mvnForum from RC1 to RC2
-- This script creates 3 new table : mvnforumMemberForum
--                                   mvnforumFavoriteThread
--                                   mvnforumRank
--
-- Database: Interbase
*/

/*
drop table mvnforumMemberForum;
drop table mvnforumFavoriteThread;
drop table mvnforumRank;
*/

create generator mvnforumRank_seq;

commit;


create table mvnforumMemberForum
(
   MemberID                       INTEGER                        not null,
   ForumID                        INTEGER                        not null,
   Permission                     INTEGER                        not null,
   primary key (MemberID, ForumID, Permission)
);

create index MemberForum_1_idx on mvnforumMemberForum
(
   MemberID
);

create index MemberForum_2_idx on mvnforumMemberForum
(
   ForumID
);

create table mvnforumFavoriteThread
(
   MemberID                       INTEGER                        not null,
   ThreadID                       INTEGER                        not null,
   ForumID                        INTEGER                        not null,
   FavoriteCreationDate           TIMESTAMP                      not null,
   FavoriteType                   INTEGER                        not null,
   FavoriteOption                 INTEGER                        not null,
   FavoriteStatus                 INTEGER                        not null,
   primary key (MemberID, ThreadID)
);

create index FavorThread_1_idx on mvnforumFavoriteThread
(
   MemberID
);

create index FavorThread_2_idx on mvnforumFavoriteThread
(
   ThreadID
);

create table mvnforumRank
(
   RankID                         INTEGER                        not null,
   RankMinPosts                   INTEGER                        not null,
   RankLevel                      INTEGER                        not null,
   RankTitle                      VARCHAR(250)                   not null,
   RankImage                      VARCHAR(250)                   not null,
   RankType                       INTEGER                        not null,
   RankOption                     INTEGER                        not null,
   primary key (RankID),
   unique (RankMinPosts),
   unique (RankTitle)
);

commit;

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

create trigger mvnforumRank_trig_autoinc for mvnforumRank 
active before insert position 1
as
begin
  if (new.RankID is null) then
     new.RankID = gen_id(mvnforumRank_seq, 1);
end
^

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

commit;



/* data for table mvnforumRank                                               */
INSERT INTO mvnforumRank (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption) 
                  VALUES (0, 0, 'Stranger',                   '',        0,        0);
INSERT INTO mvnforumRank (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption) 
                  VALUES (20, 0, 'Newbie',                    '',        0,        0);
INSERT INTO mvnforumRank (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption) 
                  VALUES (50, 0, 'Member',                    '',        0,        0);
INSERT INTO mvnforumRank (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption) 
                  VALUES (100, 0, 'Advanced Member',          '',        0,        0);

commit;