-- $Header: /cvsroot/mvnforum/mvnforum/sql/upgrade/1_0_0_rc1_to_1_0_0_rc2/mvnForum_update_postgresql.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: PostgreSql



-- drop table mvnforumMemberForum;
-- drop table mvnforumFavoriteThread;
-- drop table mvnforumRank;

--
-- drop sequences
--
-- drop sequence mvnforumRank_seq;


--
-- create sequences
--
create sequence mvnforumRank_seq;


create table mvnforumMemberForum
(
   MemberID                       INT                            not null,
   ForumID                        INT                            not null,
   Permission                     INT                            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                       INT                            not null,
   ThreadID                       INT                            not null,
   ForumID                        INT                            not null,
   FavoriteCreationDate           TIMESTAMP                      not null,
   FavoriteType                   INT                            not null,
   FavoriteOption                 INT                            not null,
   FavoriteStatus                 INT                            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                         INT                            not null DEFAULT nextval('mvnforumRank_seq'),
   RankMinPosts                   INT                            not null,
   RankLevel                      INT                            not null,
   RankTitle                      VARCHAR(250)                   not null,
   RankImage                      VARCHAR(250)                   not null,
   RankType                       INT                            not null,
   RankOption                     INT                            not null,
   primary key (RankID),
   unique (RankMinPosts),
   unique (RankTitle)
);



--
-- 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);