/*
-- $Header: /cvsroot/mvnforum/mvnforum/sql/mvnForum_interbase.sql,v 1.16 2005/01/29 11:41:12 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.16 $
-- $Date: 2005/01/29 11:41:12 $
-- Database : Interbase/Firebird
-- Driver   : org.firebirdsql.jdbc.FBDriver
-- Url      : jdbc:firebirdsql:<host>/3050:


-- Things should be considered when port this file to other database
-- AUTO_INCREMENT : generator and trigger
-- LONGVARCHAR    : domain, see notes at the end of the file.
-- DATE           : DATE
-- TIMESTAMP      : TIMESTAMP
-- VARCHAR        : VARCHAR
-- now()          : now
-- INT            : INTEGER

-- Run isql:(on Windows)
-- Create Data :: 
--   CREATE DATABASE [fileName] user [username:default='sysdba'] password [password:default='masterkey']
--   Ex : CREATE DATABASE 'D:\data\mvnforum.fdb' user 'sysdba' password 'masterkey'
-- Connect to database :: 
--   CONNECT         [fileName] user [username:default='sysdba'] password [password:default='masterkey']
--   Ex : CONNECT 'D:\data\mvnforum.fdb' user 'sysdba' password 'masterkey'
-- Now, We are logged in mvnforum database and can run SQL Script for the database:
     IN  [sql script file]
--   Ex : IN 'D:\mvnforum\sql\mvnforum_interbase.sql'

-- Finish
*/


/* 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 domain                                                               */
/*
drop domain text;
*/

/* Create generator                                                          */
create generator mvnforumCategory_seq;
create generator mvnforumForum_seq;
create generator mvnforumGroups_seq;
create generator mvnforumMember_seq;
create generator mvnforumPost_seq;
create generator mvnforumThread_seq;
create generator mvnforumWatch_seq;
create generator mvnforumAttachment_seq;
create generator mvnforumRank_seq;
create generator mvnforumMessage_seq;
create generator mvnforumPmAttachment_seq;

set generator mvnforumGroups_seq to 6;
set generator mvnforumMember_seq to 2;


/* It should be modeled after a clob, but then, JDBC code that expect  */
/* to use getString() could be broken.                                 */
create domain text as varchar(4096);

commit;

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

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

CREATE INDEX Forum_CatID_idx on mvnforumForum
(
   CategoryID
);

CREATE TABLE mvnforumGroupForum
(
   GroupID                        INTEGER                        not null,
   ForumID                        INTEGER                        not null,
   Permission                     INTEGER                        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                        INTEGER                        not null,
   Permission                     INTEGER                        not null,
   primary key (GroupID, Permission)
);

CREATE INDEX GroupPermission_1_idx on mvnforumGroupPermission
(
   GroupID
);

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

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

CREATE TABLE mvnforumMemberGroup
(
   GroupID                        INTEGER                        not null,
   MemberID                       INTEGER                        not null,
   MemberName                     VARCHAR(30)                    not null,
   Privilege                      INTEGER                        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                       INTEGER                        not null,
   Permission                     INTEGER                        not null,
   primary key (MemberID, Permission)
);

CREATE INDEX MemberPermission_1_idx on mvnforumMemberPermission
(
   MemberID
);

CREATE TABLE mvnforumMessageFolder
(
   FolderName                     VARCHAR(30)                    not null,
   MemberID                       INTEGER                        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                         INTEGER                        not null,
   ParentPostID                   INTEGER                        not null,
   ForumID                        INTEGER                        not null,
   ThreadID                       INTEGER                        not null,
   MemberID                       INTEGER                        not null,
   MemberName                     VARCHAR(30)                    not null,
   LastEditMemberName             VARCHAR(30)                    not null,
   PostTopic                      VARCHAR(250)                   not null,
   PostBody                       TEXT                           not null,
   PostCreationDate               TIMESTAMP                      not null,
   PostLastEditDate               TIMESTAMP                      not null,
   PostCreationIP                 VARCHAR(20)                    not null,
   PostLastEditIP                 VARCHAR(20)                    not null,
   PostEditCount                  SMALLINT                       not null,
   PostFormatOption               INTEGER                        not null,
   PostOption                     INTEGER                        not null,
   PostStatus                     INTEGER                        not null,
   PostIcon                       VARCHAR(10)                    not 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                       INTEGER                        not null,
   ForumID                        INTEGER                        not null,
   MemberName                     VARCHAR(30)                    not null,
   LastPostMemberName             VARCHAR(30)                    not null,
   ThreadTopic                    VARCHAR(250)                   not null,
   ThreadBody                     TEXT                           not null,
   ThreadVoteCount                INTEGER                        not null,
   ThreadVoteTotalStars           INTEGER                        not null,
   ThreadCreationDate             TIMESTAMP                      not null,
   ThreadLastPostDate             TIMESTAMP                      not null,
   ThreadType                     INTEGER                        not null,
   ThreadOption                   INTEGER                        not null,
   ThreadStatus                   INTEGER                        not null,
   ThreadHasPoll                  INTEGER                        not null,
   ThreadViewCount                INTEGER                        not null,
   ThreadReplyCount               INTEGER                        not null,
   ThreadIcon                     VARCHAR(10)                    ,
   ThreadDuration                 INTEGER                        not null,
   ThreadAttachCount              INT                            not null,
   primary key (ThreadID)
);

CREATE INDEX Thread_1_idx on mvnforumThread
(
   ForumID
);

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

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

CREATE TABLE mvnforumMessage
(
   MessageID                      INTEGER                        not null,
   FolderName                     VARCHAR(30)                    not null,
   MemberID                       INTEGER                        not null,
   MessageSenderID                INTEGER                        not null,
   MessageSenderName              VARCHAR(30)                    not null,
   MessageToList                  VARCHAR(250)                   not null,
   MessageCcList                  VARCHAR(250),
   MessageBccList                 VARCHAR(250),
   MessageTopic                   VARCHAR(250)                   not null,
   MessageBody                    TEXT                           not null,
   MessageType                    INTEGER                        not null,
   MessageOption                  INTEGER                        not null,
   MessageStatus                  INTEGER                        not null,
   MessageReadStatus              INTEGER                        not null,
   MessageNotify                  INTEGER                        not null,
   MessageIcon                    VARCHAR(10)                    not null,
   MessageAttachCount             INTEGER                        not null,
   MessageIP                      VARCHAR(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                         INTEGER                            not null,
   ToID                           INTEGER                            not null,
   MessageCreationDate            TIMESTAMP                          not null,
   MessageAttachCount             INTEGER                            not null,
   MessageType                    INTEGER                            not null,
   MessageOption                  INTEGER                            not null,
   MessageStatus                  INTEGER                            not null
);

CREATE INDEX MessageStatistics_1_idx on mvnforumMessageStatistics
(
   FromID
);

CREATE INDEX MessageStatistics_2_idx on mvnforumMessageStatistics
(
   ToID
);

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

CREATE INDEX PmAttachment_1_idx on mvnforumPmAttachment
(
   MemberID
);

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

commit;

/* 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',             'now',              'now',              'now',            'now',           '0',          '0',          '',                 '',                 '0',                '0',                 '10',               '0',             '0',             '0',                  '0',                '',          '0',            '',              '',           '',         '',             'Admin',         'Admin',        '1',          'now',          '',            '',         '',          '',            '',          '',           '',        '',           '',             '',          '',        '',        '',        '',              '');


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


/* 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',         'now',             'now');
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('2',     '0',          '',             'Member',  'All registered users belong to this group.',             '0',         'now',             'now');
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc,                                                GroupOption, GroupCreationDate, GroupModifiedDate)
                    VALUES ('3',     '1',          'admin',        'Admin',   'This group have SystemAdmin permission by default.',     '0',         'now',             'now');
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',         'now',             'now');
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',   'now',             'now');


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



/* 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');


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

create trigger mvnforumCategory_trig_autoinc for mvnforumCategory
active before insert position 1
as
begin
  if (new.CategoryID is null) then
     new.CategoryID = gen_id(mvnforumCategory_seq, 1);
end
^

create trigger mvnforumForum_trig_autoinc for mvnforumForum
active before insert position 1
as
begin
  if (new.ForumID is null) then
     new.ForumID = gen_id(mvnforumForum_seq, 1);
end
^

create trigger mvnforumGroups_trig_autoinc for mvnforumGroups
active before insert position 1
as
begin
  if (new.GroupID is null) then
     new.GroupID = gen_id(mvnforumGroups_seq, 1);
end
^

create trigger mvnforumMember_trig_autoinc for mvnforumMember
active before insert position 1
as
begin
  if (new.MemberID is null) then
     new.MemberID = gen_id(mvnforumMember_seq, 1);
end
^

create trigger mvnforumPost_trig_autoinc for mvnforumPost
active before insert position 1
as
begin
  if (new.PostID is null) then
     new.PostID = gen_id(mvnforumPost_seq, 1);
end
^

create trigger mvnforumThread_trig_autoinc for mvnforumThread
active before insert position 1
as
begin
  if (new.ThreadID is null) then
     new.ThreadID = gen_id(mvnforumThread_seq, 1);
end
^

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
^

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
^

create trigger mvnforumMessage_trig_autoinc for mvnforumMessage 
active before insert position 1
as
begin
  if (new.MessageID is null) then
     new.MessageID = gen_id(mvnforumMessage_seq, 1);
end
^

create trigger mvnforumPmAttach_trig_autoinc for mvnforumPmAttachment 
active before insert position 1
as
begin
  if (new.PmAttachID is null) then
     new.PmAttachID = gen_id(mvnforumPmAttachment_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;