-- $Header: /cvsroot/mvnforum/mvnforum/sql/mvnForum_oracle9.sql,v 1.15 2005/01/27 09:08:03 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.15 $
-- $Date: 2005/01/27 09:08:03 $
-- Database : Oracle 9i or Oracle 10g
-- Driver   : oracle.jdbc.driver.OracleDriver
-- Url      : jdbc:oracle:thin:@<host>:1521:
--
-- Note: If your jdbc driver is before 8.1.7, you MUST upgrade jdbc driver 
--       to 8.1.7 or later (You dont have to upgrade Oracle database, just upgrade jdbc driver)
--       mvnForum will not run on Oracle's jdbc driver before 8.1.7
-- Download url : http://otn.oracle.com/software/tech/java/sqlj_jdbc/content.html
--
-- NOTE: You should use UTF8 for Oracle database, you set 
--   encoding with Database Configuration Assistant when you create database

-- Things should be considered when port this file to other database
-- AUTO_INCREMENT : sequence and trigger
-- LONGVARCHAR    : LONG VARCHAR
-- DATE           : DATE
-- TIMESTAMP      : TIMESTAMP
-- VARCHAR        : VARCHAR2
-- now()          : sysdate

-- Uncomment the following drop table command if you want to drop the tables
-- Note: drop tables will delete all the data in them.
-- Note: you should always backup your data before run the script

-- drop table mvnforumCategory;
-- drop table mvnforumForum;
-- drop table mvnforumGroupForum;
-- drop table mvnforumGroupPermission;
-- drop table mvnforumGroups;
-- drop table mvnforumMember;
-- drop table mvnforumMemberGroup;
-- drop table mvnforumMemberPermission;
-- drop table mvnforumMessageFolder;
-- drop table mvnforumPost;
-- drop table mvnforumThread;
-- drop table mvnforumWatch;
-- drop table mvnforumAttachment;
-- drop table mvnforumMemberForum;
-- drop table mvnforumFavoriteThread;
-- drop table mvnforumRank;
-- drop table mvnforumMessage;
-- drop table mvnforumMessageStatistics;
-- drop table mvnforumPmAttachment;
-- drop table mvnforumPmAttachMessage;

--
-- drop sequence
--
-- drop sequence mvnforumCategory_seq;
-- drop sequence mvnforumForum_seq;
-- drop sequence mvnforumGroups_seq;
-- drop sequence mvnforumMember_seq;
-- drop sequence mvnforumPost_seq;
-- drop sequence mvnforumThread_seq;
-- drop sequence mvnforumWatch_seq;
-- drop sequence mvnforumAttachment_seq;
-- drop sequence mvnforumRank_seq;
-- drop sequence mvnforumMessage_seq;
-- drop sequence mvnforumPmAttachment_seq;


--
-- create sequences
--
create sequence mvnforumCategory_seq;
create sequence mvnforumForum_seq;
create sequence mvnforumGroups_seq start with 6;
create sequence mvnforumMember_seq start with 2;
create sequence mvnforumPost_seq;
create sequence mvnforumThread_seq;
create sequence mvnforumWatch_seq;
create sequence mvnforumAttachment_seq;
create sequence mvnforumRank_seq;
create sequence mvnforumMessage_seq;
create sequence mvnforumPmAttachment_seq;


CREATE TABLE mvnforumCategory
(
   CategoryID                     INT                            not null,
   ParentCategoryID               INT                            not null,
   CategoryName                   VARCHAR2(250)                  not null,
   CategoryDesc                   LONG VARCHAR                   null,
   CategoryCreationDate           TIMESTAMP                      not null,
   CategoryModifiedDate           TIMESTAMP                      not null,
   CategoryOrder                  SMALLINT                       not null,
   CategoryOption                 INT                            not null,
   CategoryStatus                 INT                            not null,
   primary key (CategoryID),
   unique (CategoryName)
);

CREATE TABLE mvnforumForum
(
   ForumID                        INT                            not null,
   CategoryID                     INT                            not null,
   LastPostMemberName             VARCHAR2(30)                   null,
   ForumName                      VARCHAR2(250)                  not null,
   ForumDesc                      LONG VARCHAR                   null,
   ForumCreationDate              TIMESTAMP                      not null,
   ForumModifiedDate              TIMESTAMP                      not null,
   ForumLastPostDate              TIMESTAMP                      not null,
   ForumOrder                     SMALLINT                       not null,
   ForumType                      INT                            not null,
   ForumFormatOption              INT                            not null,
   ForumOption                    INT                            not null,
   ForumStatus                    INT                            not null,
   ForumModerationMode            INT                            not null,
   ForumPassword                  VARCHAR2(40)                   null,
   ForumThreadCount               INT                            not null,
   ForumPostCount                 INT                            not null,
   primary key (ForumID),
   unique (ForumName, CategoryID)
);

CREATE INDEX Forum_CatID_idx on mvnforumForum
(
   CategoryID
);

CREATE TABLE mvnforumGroupForum
(
   GroupID                        INT                            not null,
   ForumID                        INT                            not null,
   Permission                     INT                            not null,
   primary key (GroupID, ForumID, Permission)
);

CREATE INDEX GroupForum_1_idx on mvnforumGroupForum
(
   GroupID
);

CREATE INDEX GroupForum_2_idx on mvnforumGroupForum
(
   ForumID
);

CREATE TABLE mvnforumGroupPermission
(
   GroupID                        INT                            not null,
   Permission                     INT                            not null,
   primary key (GroupID, Permission)
);

CREATE INDEX GroupPermission_1_idx on mvnforumGroupPermission
(
   GroupID
);

CREATE TABLE mvnforumGroups
(
   GroupID                        INT                            not null,
   GroupOwnerID                   INT                            not null,
   GroupOwnerName                 VARCHAR2(30)                   null,
   GroupName                      VARCHAR2(250)                  not null,
   GroupDesc                      LONG VARCHAR                   null,
   GroupOption                    INT                            not null,
   GroupCreationDate              TIMESTAMP                      not null,
   GroupModifiedDate              TIMESTAMP                      not null,
   primary key (GroupID),
   unique (GroupName)
);

CREATE TABLE mvnforumMember
(
   MemberID                       INT                            not null,
   MemberName                     VARCHAR2(30)                   not null,
   MemberPassword                 VARCHAR2(200)                  not null,
   MemberFirstEmail               VARCHAR2(60)                   not null,
   MemberEmail                    VARCHAR2(60)                   not null,
   MemberEmailVisible             SMALLINT                       not null,
   MemberNameVisible              SMALLINT                       not null,
   MemberFirstIP                  VARCHAR2(20)                   not null,
   MemberLastIP                   VARCHAR2(20)                   not null,
   MemberViewCount                INT                            not null,
   MemberPostCount                INT                            not null,
   MemberCreationDate             TIMESTAMP                      not null,
   MemberModifiedDate             TIMESTAMP                      not null,
   MemberExpireDate               TIMESTAMP                      not null,
   MemberLastLogon                TIMESTAMP                      not null,
   MemberOption                   INT                            not null,
   MemberStatus                   INT                            not null,
   MemberActivateCode             VARCHAR2(40)                   null,
   MemberTempPassword             VARCHAR2(40)                   null,
   MemberMessageCount             INT                            not null,
   MemberMessageOption            INT                            not null,
   MemberPostsPerPage             SMALLINT                       not null,
   MemberWarnCount                SMALLINT                       not null,
   MemberVoteCount                INT                            not null,
   MemberVoteTotalStars           INT                            not null,
   MemberRewardPoints             INT                            not null,
   MemberTitle                    VARCHAR2(250)                  null,
   MemberTimeZone                 INT                            not null,
   MemberSignature                VARCHAR2(250)                  null,
   MemberAvatar                   VARCHAR2(200)                  null,
   MemberSkin                     VARCHAR2(70)                   null,
   MemberLanguage                 VARCHAR2(70)                   null,
   MemberFirstname                VARCHAR2(70)                   null,
   MemberLastname                 VARCHAR2(70)                   null,
   MemberGender                   SMALLINT                       not null,
   MemberBirthday                 DATE                           not null,
   MemberAddress                  VARCHAR2(150)                  null,
   MemberCity                     VARCHAR2(70)                   null,
   MemberState                    VARCHAR2(70)                   null,
   MemberCountry                  VARCHAR2(70)                   null,
   MemberPhone                    VARCHAR2(40)                   null,
   MemberMobile                   VARCHAR2(40)                   null,
   MemberFax                      VARCHAR2(40)                   null,
   MemberCareer                   VARCHAR2(50)                   null,
   MemberHomepage                 VARCHAR2(200)                  null,
   MemberYahoo                    VARCHAR2(70)                   null,
   MemberAol                      VARCHAR2(70)                   null,
   MemberIcq                      VARCHAR2(70)                   null,
   MemberMsn                      VARCHAR2(70)                   null,
   MemberCoolLink1                VARCHAR2(200)                  null,
   MemberCoolLink2                VARCHAR2(200)                  null,
   primary key (MemberID),
   unique (MemberEmail),
   unique (MemberName)
);

CREATE TABLE mvnforumMemberGroup
(
   GroupID                        INT                            not null,
   MemberID                       INT                            not null,
   MemberName                     VARCHAR2(30)                   not null,
   Privilege                      INT                            not null,
   CreationDate                   TIMESTAMP                      not null,
   ModifiedDate                   TIMESTAMP                      not null,
   primary key (GroupID, MemberID)
);

CREATE INDEX MemberGroup_1_idx on mvnforumMemberGroup
(
   MemberID
);

CREATE INDEX MemberGroup_2_idx on mvnforumMemberGroup
(
   GroupID
);

CREATE TABLE mvnforumMemberPermission
(
   MemberID                       INT                            not null,
   Permission                     INT                            not null,
   primary key (MemberID, Permission)
);

CREATE INDEX MemberPermission_1_idx on mvnforumMemberPermission
(
   MemberID
);

CREATE TABLE mvnforumMessageFolder
(
   FolderName                     VARCHAR2(30)                   not null,
   MemberID                       INT                            not null,
   FolderOrder                    SMALLINT                       not null,
   FolderStatus                   INT                            not null,
   FolderOption                   INT                            not null,
   FolderType                     INT                            not null,
   FolderCreationDate             TIMESTAMP                      not null,
   FolderModifiedDate             TIMESTAMP                      not null,
   primary key (FolderName, MemberID)
);

CREATE INDEX MessageFolder_1_idx on mvnforumMessageFolder
(
   MemberID
);

CREATE TABLE mvnforumPost
(
   PostID                         INT                            not null,
   ParentPostID                   INT                            not null,
   ForumID                        INT                            not null,
   ThreadID                       INT                            not null,
   MemberID                       INT                            not null,
   MemberName                     VARCHAR2(30)                   not null,
   LastEditMemberName             VARCHAR2(30)                   null,
   PostTopic                      VARCHAR2(250)                  not null,
   PostBody                       LONG VARCHAR                   not null,
   PostCreationDate               TIMESTAMP                      not null,
   PostLastEditDate               TIMESTAMP                      not null,
   PostCreationIP                 VARCHAR2(20)                   not null,
   PostLastEditIP                 VARCHAR2(20)                   null,
   PostEditCount                  SMALLINT                       not null,
   PostFormatOption               INT                            not null,
   PostOption                     INT                            not null,
   PostStatus                     INT                            not null,
   PostIcon                       VARCHAR2(10)                   null,
   PostAttachCount                SMALLINT                       not null,
   primary key (PostID)
);

CREATE INDEX Post_1_idx on mvnforumPost
(
   ForumID
);

CREATE INDEX Post_2_idx on mvnforumPost
(
   ThreadID
);

CREATE INDEX Post_3_idx on mvnforumPost
(
   MemberID
);

CREATE INDEX Post_4_idx on mvnforumPost
(
   ParentPostID
);

CREATE TABLE mvnforumThread
(
   ThreadID                       INT                            not null,
   ForumID                        INT                            not null,
   MemberName                     VARCHAR2(30)                   not null,
   LastPostMemberName             VARCHAR2(30)                   not null,
   ThreadTopic                    VARCHAR2(250)                  not null,
   ThreadBody                     LONG VARCHAR                   not null,
   ThreadVoteCount                INT                            not null,
   ThreadVoteTotalStars           INT                            not null,
   ThreadCreationDate             TIMESTAMP                      not null,
   ThreadLastPostDate             TIMESTAMP                      not null,
   ThreadType                     INT                            not null,
   ThreadOption                   INT                            not null,
   ThreadStatus                   INT                            not null,
   ThreadHasPoll                  INT                            not null,
   ThreadViewCount                INT                            not null,
   ThreadReplyCount               INT                            not null,
   ThreadIcon                     VARCHAR2(10)                   null,
   ThreadDuration                 INT                            not null,
   ThreadAttachCount              INT                            not null,
   primary key (ThreadID)
);

CREATE INDEX Thread_1_idx on mvnforumThread
(
   ForumID
);

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                     LONG VARCHAR                   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 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,
   RankMinPosts                   INT                            not null,
   RankLevel                      INT                            not null,
   RankTitle                      VARCHAR2(250)                  not null,
   RankImage                      VARCHAR2(250)                  null,
   RankType                       INT                            not null,
   RankOption                     INT                            not null,
   primary key (RankID),
   unique (RankMinPosts),
   unique (RankTitle)
);

CREATE TABLE mvnforumMessage
(
   MessageID                      INT                            not null,
   FolderName                     VARCHAR2(30)                   not null,
   MemberID                       INT                            not null,
   MessageSenderID                INT                            not null,
   MessageSenderName              VARCHAR2(30)                   not null,
   MessageToList                  VARCHAR2(250)                  not null,
   MessageCcList                  VARCHAR2(250)                  null,
   MessageBccList                 VARCHAR2(250)                  null,
   MessageTopic                   VARCHAR2(250)                  not null,
   MessageBody                    LONG VARCHAR                   not null,
   MessageType                    INT                            not null,
   MessageOption                  INT                            not null,
   MessageStatus                  INT                            not null,
   MessageReadStatus              INT                            not null,
   MessageNotify                  INT                            not null,
   MessageIcon                    VARCHAR2(10)                   null,
   MessageAttachCount             INT                            not null,
   MessageIP                      VARCHAR2(20)                   not null,
   MessageCreationDate            TIMESTAMP                      not null,
   primary key (MessageID)
);

CREATE INDEX Message_1_idx on mvnforumMessage
(
   FolderName,
   MemberID
);

CREATE INDEX Message_2_idx on mvnforumMessage
(
   MessageSenderID
);

CREATE TABLE mvnforumMessageStatistics
(
   FromID                         INT                            not null,
   ToID                           INT                            not null,
   MessageCreationDate            TIMESTAMP                      not null,
   MessageAttachCount             INT                            not null,
   MessageType                    INT                            not null,
   MessageOption                  INT                            not null,
   MessageStatus                  INT                            not null
);

CREATE INDEX MessageStatistics_1_idx on mvnforumMessageStatistics
(
   FromID
);

CREATE INDEX MessageStatistics_2_idx on mvnforumMessageStatistics
(
   ToID
);

CREATE TABLE mvnforumPmAttachment
(
   PmAttachID                     INT                            not null,
   MemberID                       INT                            not null,
   PmAttachFilename               VARCHAR2(250)                  not null,
   PmAttachFileSize               INT                            not null,
   PmAttachMimeType               VARCHAR2(70)                   not null,
   PmAttachDesc                   LONG VARCHAR                   null,
   PmAttachCreationIP             VARCHAR2(20)                   not null,
   PmAttachCreationDate           TIMESTAMP                      not null,
   PmAttachModifiedDate           TIMESTAMP                      not null,
   PmAttachDownloadCount          INT                            not null,
   PmAttachOption                 INT                            not null,
   PmAttachStatus                 INT                            not null,
   primary key (PmAttachID)
);

CREATE INDEX PmAttachment_1_idx on mvnforumPmAttachment
(
   MemberID
);

CREATE TABLE mvnforumPmAttachMessage
(
   MessageID                      INT                            not null,
   PmAttachID                     INT                            not null,
   RelationType                   INT                            not null,
   RelationOption                 INT                            not null,
   RelationStatus                 INT                            not null,
   primary key (MessageID, PmAttachID)
);



--
-- data for table mvnforumMember
--
INSERT INTO mvnforumMember (MemberID, MemberName, MemberPassword, MemberFirstEmail, MemberEmail, MemberEmailVisible, MemberNameVisible, MemberFirstIP, MemberLastIP, MemberViewCount, MemberPostCount, MemberCreationDate, MemberModifiedDate, MemberExpireDate, MemberLastLogon, MemberOption, MemberStatus, MemberActivateCode, MemberTempPassword, MemberMessageCount, MemberMessageOption, MemberPostsPerPage, MemberWarnCount, MemberVoteCount, MemberVoteTotalStars, MemberRewardPoints, MemberTitle, MemberTimeZone, MemberSignature, MemberAvatar, MemberSkin, MemberLanguage, MemberFirstname, MemberLastname, MemberGender, MemberBirthday, MemberAddress, MemberCity, MemberState, MemberCountry, MemberPhone, MemberMobile, MemberFax, MemberCareer, MemberHomepage, MemberYahoo, MemberAol, MemberIcq, MemberMsn, MemberCoolLink1, MemberCoolLink2)
	            VALUES ('1', 'admin', 'ISMvKXpXpadDiUoOSoAfww==', 'admin@yourdomain.com', 'admin@yourdomain.com', '0', '1',         '127.0.0.1',   '127.0.0.1',  '0',             '0',             sysdate,            sysdate,            sysdate,          sysdate,         '0',          '0',          '',                 '',                 '0',                '0',                 '10',               '0',             '0',             '0',                  '0',                '',          '0',            '',              '',           '',         '',             'Admin',         'Admin',        '1',          sysdate,        '',            '',         '',          '',            '',          '',           '',        '',           '',             '',          '',        '',        '',        '',              '');


--
-- data for table mvnforumMessageFolder
--
INSERT INTO mvnforumMessageFolder (FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate)
                           VALUES ('Inbox',    '1',      '0',         0,            0,            0,          sysdate,            sysdate);
INSERT INTO mvnforumMessageFolder (FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate)
                           VALUES ('Sent',     '1',      '2',         0,            0,            0,          sysdate,            sysdate);


--
-- data for table mvnforumGroups
--
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('1',     '0',          '',             'Guest',   'All anonymous users belong to this group.',              '0',         sysdate,             sysdate);
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('2',     '0',          '',             'Member',  'All registered users belong to this group.',             '0',         sysdate,             sysdate);
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('3',     '1',          'admin',        'Admin',   'This group have SystemAdmin permission by default.',     '0',         sysdate,             sysdate);
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('4',     '1',          'admin',        'Forum Admin', 'This group have ForumAdmin permission by default.',  '0',         sysdate,             sysdate);
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('5',     '1',          'admin',        'Forum Moderator', 'This group have ForumModerator permission by default.','0',   sysdate,             sysdate);


--
-- data for table mvnforumMemberGroup
--
INSERT INTO mvnforumMemberGroup (GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate)
                         VALUES ('3',     '1',      'admin',    '0',       sysdate,      sysdate);



--
-- data for table mvnforumMemberPermission
--
INSERT INTO mvnforumMemberPermission (MemberID, Permission) 
                              VALUES ('1',      '100');


--
-- data for table mvnforumGroupPermission
--
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('1',     '109');
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('2',     '110');
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('3',     '100');
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('4',     '105');
INSERT INTO mvnforumGroupPermission (GroupID, Permission) 
                             VALUES ('5',     '106');

create or replace trigger mvnforumCategory_trig_autoinc
before insert on mvnforumCategory
for each row
begin
  if (:new.CategoryID is null) then 
    select mvnforumCategory_seq.nextval into :new.CategoryID from dual;
  end if;
end;
/

create or replace trigger mvnforumForum_trig_autoinc
before insert on mvnforumForum
for each row
begin
  if (:new.ForumID is null) then 
    select mvnforumForum_seq.nextval into :new.ForumID from dual;
  end if;
end;
/

create or replace trigger mvnforumGroups_trig_autoinc
before insert on mvnforumGroups
for each row
begin
  if (:new.GroupID is null) then 
    select mvnforumGroups_seq.nextval into :new.GroupID from dual;
  end if;
end;
/

create or replace trigger mvnforumMember_trig_autoinc
before insert on mvnforumMember
for each row
begin
  if (:new.MemberID is null) then 
    select mvnforumMember_seq.nextval into :new.MemberID from dual;
  end if;
end;
/

create or replace trigger mvnforumPost_trig_autoinc
before insert on mvnforumPost
for each row
begin
  if (:new.PostID is null) then 
    select mvnforumPost_seq.nextval into :new.PostID from dual;
  end if;
end;
/

create or replace trigger mvnforumThread_trig_autoinc
before insert on mvnforumThread
for each row
begin
  if (:new.ThreadID is null) then 
    select mvnforumThread_seq.nextval into :new.ThreadID from dual;
  end if;
end;
/

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

create or replace trigger mvnforumRank_trig_autoinc
before insert on mvnforumRank
for each row
begin
  if (:new.RankID is null) then 
    select mvnforumRank_seq.nextval into :new.RankID from dual;
  end if;
end;
/

create or replace trigger mvnforumMessage_trig_autoinc
before insert on mvnforumMessage
for each row
begin
  if (:new.MessageID is null) then 
    select mvnforumMessage_seq.nextval into :new.MessageID from dual;
  end if;
end;
/

create or replace trigger mvnforumPmAttach_trig_autoinc
before insert on mvnforumPmAttachment
for each row
begin
  if (:new.PmAttachID is null) then 
    select mvnforumPmAttachment_seq.nextval into :new.PmAttachID from dual;
  end if;
end;
/

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