
-- $Header: /cvsroot/mvnforum/mvnforum/sql/mvnForum_JDBC.sql,v 1.15 2006/03/29 19:14:36 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.15 $
-- $Date: 2006/03/29 19:14:36 $
-- Database : JDBC Database
-- Driver   :
-- Url      :

-- This file is a jdbc general script file for mvnForum, it should be used to port to other databases

-- Things should be considered when port this file to other database
-- AUTO_INCREMENT : auto crement feature
-- LONGVARCHAR    : 
-- DATE           : 
-- TIMESTAMP      : 
-- VARCHAR        : 
-- BLOB           : 
-- now()          : 

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


CREATE TABLE mvnforumCategory
(
   CategoryID                     INT                            not null AUTO_INCREMENT,
   ParentCategoryID               INT                            not null,
   CategoryName                   VARCHAR(250)                   not null,
   CategoryDesc                   LONGVARCHAR                    not 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 AUTO_INCREMENT,
   CategoryID                     INT                            not null,
   LastPostMemberName             VARCHAR(30)                    not null,
   ForumName                      VARCHAR(250)                   not null,
   ForumDesc                      LONGVARCHAR                    not 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                  VARCHAR(40)                    not 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 AUTO_INCREMENT,
   GroupOwnerID                   INT                            not null,
   GroupOwnerName                 VARCHAR(30)                    not null,
   GroupName                      VARCHAR(250)                   not null,
   GroupDesc                      LONGVARCHAR                    not 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 AUTO_INCREMENT,
   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                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             VARCHAR(40)                    not null,
   MemberTempPassword             VARCHAR(40)                    not 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                    VARCHAR(250)                   not null,
   MemberTimeZone                 FLOAT                          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                        INT                            not null,
   MemberID                       INT                            not null,
   MemberName                     VARCHAR(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                     VARCHAR(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 AUTO_INCREMENT,
   ParentPostID                   INT                            not null,
   ForumID                        INT                            not null,
   ThreadID                       INT                            not null,
   MemberID                       INT                            not null,
   MemberName                     VARCHAR(30)                    not null,
   LastEditMemberName             VARCHAR(30)                    not null,
   PostTopic                      VARCHAR(250)                   not null,
   PostBody                       LONGVARCHAR                    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               INT                            not null,
   PostOption                     INT                            not null,
   PostStatus                     INT                            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                       INT                            not null AUTO_INCREMENT,
   ForumID                        INT                            not null,
   MemberName                     VARCHAR(30)                    not null,
   LastPostMemberName             VARCHAR(30)                    not null,
   ThreadTopic                    VARCHAR(250)                   not null,
   ThreadBody                     LONGVARCHAR                    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                     VARCHAR(10)                    not 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 AUTO_INCREMENT,
   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 AUTO_INCREMENT,
   PostID                         INT                            not null,
   MemberID                       INT                            not null,
   AttachFilename                 VARCHAR(250)                   not null,
   AttachFileSize                 INT                            not null,
   AttachMimeType                 VARCHAR(70)                    not null,
   AttachDesc                     LONGVARCHAR                    not null,
   AttachCreationIP               VARCHAR(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 AUTO_INCREMENT,
   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)
);

CREATE TABLE mvnforumMessage
(
   MessageID                      INT                            not null AUTO_INCREMENT,
   FolderName                     VARCHAR(30)                    not null,
   MemberID                       INT                            not null,
   MessageSenderID                INT                            not null,
   MessageSenderName              VARCHAR(30)                    not null,
   MessageToList                  VARCHAR(250)                   not null,
   MessageCcList                  VARCHAR(250),
   MessageBccList                 VARCHAR(250),
   MessageTopic                   VARCHAR(250)                   not null,
   MessageBody                    LONGVARCHAR                    not null,
   MessageType                    INT                            not null,
   MessageOption                  INT                            not null,
   MessageStatus                  INT                            not null,
   MessageReadStatus              INT                            not null,
   MessageNotify                  INT                            not null,
   MessageIcon                    VARCHAR(10)                    not null,
   MessageAttachCount             INT                            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                         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 AUTO_INCREMENT,
   MemberID                       INT                            not null,
   PmAttachFilename               VARCHAR(250)                   not null,
   PmAttachFileSize               INT                            not null,
   PmAttachMimeType               VARCHAR(70)                    not null,
   PmAttachDesc                   LONGVARCHAR                    not null,
   PmAttachCreationIP             VARCHAR(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',             now(),              now(),              now(),            now(),           '0',          '0',          '',                 '',                 '0',                '0',                 '10',               '0',             '0',             '0',                  '0',                '',          '0',            '',              '',           '',         '',             '',              '',             '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');

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

