alvinalexander.com | career | drupal | java | mac | mysql | perl | scala | uml | unix  

What this is

This file is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.

Other links

The source code


# $Header: /cvsroot/mvnforum/mvnforum/sql/mvnForum_mysql.sql,v 1.19 2005/01/28 10:07:09 minhnn Exp $
# $Author: minhnn $
# $Revision: 1.19 $
# $Date: 2005/01/28 10:07:09 $
# Database : MySql
# Driver   : com.mysql.jdbc.Driver or org.gjt.mm.mysql.Driver
# Url      : jdbc:mysql://localhost/<database>?useUnicode=true&characterEncoding=utf-8
# Url for MySql 4.1 or later : jdbc:mysql://localhost/mvnforum?useServerPrepStmts=false
# MySql 4.1.x or later should read this: http://dev.mysql.com/doc/connector/j/en/cj-jdbc-upgrading-issues.html

# NOTE: you can run sql script in mysql, the command look like this
# cd C:\mysql\bin
# mysql -uroot -Dmvnforum < C:\mvnforum\sql\mvnForum_mvnforum.sql

# NOTE: how to grant permission when have "Access denied"
# GRANT ALL PRIVILEGES ON [dbname].* to '[user]'@'[hostname]' identified by '[password]'

# 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 if exists mvnforumCategory;
# drop table if exists mvnforumForum;
# drop table if exists mvnforumGroupForum;
# drop table if exists mvnforumGroupPermission;
# drop table if exists mvnforumGroups;
# drop table if exists mvnforumMember;
# drop table if exists mvnforumMemberGroup;
# drop table if exists mvnforumMemberPermission;
# drop table if exists mvnforumMessageFolder;
# drop table if exists mvnforumPost;
# drop table if exists mvnforumThread;
# drop table if exists mvnforumWatch;
# drop table if exists mvnforumAttachment;
# drop table if exists mvnforumMemberForum;
# drop table if exists mvnforumFavoriteThread;
# drop table if exists mvnforumRank;
# drop table if exists mvnforumMessage;
# drop table if exists mvnforumMessageStatistics;
# drop table if exists mvnforumPmAttachment;
# drop table if exists mvnforumPmAttachMessage;


CREATE TABLE if not exists mvnforumCategory
(
   CategoryID                     INT                            not null AUTO_INCREMENT,
   ParentCategoryID               INT                            not null,
   CategoryName                   VARCHAR(250)                   not null,
   CategoryDesc                   TEXT                           not null,
   CategoryCreationDate           DATETIME                       not null,
   CategoryModifiedDate           DATETIME                       not null,
   CategoryOrder                  SMALLINT                       not null,
   CategoryOption                 INT                            not null,
   CategoryStatus                 INT                            not null,
   primary key (CategoryID),
   unique (CategoryName)
);

CREATE TABLE if not exists mvnforumForum
(
   ForumID                        INT                            not null AUTO_INCREMENT,
   CategoryID                     INT                            not null,
   LastPostMemberName             VARCHAR(30)                    not null,
   ForumName                      VARCHAR(250)                   not null,
   ForumDesc                      TEXT                           not null,
   ForumCreationDate              DATETIME                       not null,
   ForumModifiedDate              DATETIME                       not null,
   ForumLastPostDate              DATETIME                       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 if not exists 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 if not exists mvnforumGroupPermission
(
   GroupID                        INT                            not null,
   Permission                     INT                            not null,
   primary key (GroupID, Permission)
);

CREATE INDEX GroupPermission_1_idx on mvnforumGroupPermission
(
   GroupID
);

CREATE TABLE if not exists mvnforumGroups
(
   GroupID                        INT                            not null AUTO_INCREMENT,
   GroupOwnerID                   INT                            not null,
   GroupOwnerName                 VARCHAR(30)                    not null,
   GroupName                      VARCHAR(250)                   not null,
   GroupDesc                      TEXT                           not null,
   GroupOption                    INT                            not null,
   GroupCreationDate              DATETIME                       not null,
   GroupModifiedDate              DATETIME                       not null,
   primary key (GroupID),
   unique (GroupName)
);

CREATE TABLE if not exists 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             BIT                            not null,
   MemberNameVisible              BIT                            not null,
   MemberFirstIP                  VARCHAR(20)                    not null,
   MemberLastIP                   VARCHAR(20)                    not null,
   MemberViewCount                INT                            not null,
   MemberPostCount                INT                            not null,
   MemberCreationDate             DATETIME                       not null,
   MemberModifiedDate             DATETIME                       not null,
   MemberExpireDate               DATETIME                       not null,
   MemberLastLogon                DATETIME                       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                 INT                            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                   BIT                            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 if not exists mvnforumMemberGroup
(
   GroupID                        INT                            not null,
   MemberID                       INT                            not null,
   MemberName                     VARCHAR(30)                    not null,
   Privilege                      INT                            not null,
   CreationDate                   DATETIME                       not null,
   ModifiedDate                   DATETIME                       not null,
   primary key (GroupID, MemberID)
);

CREATE INDEX MemberGroup_1_idx on mvnforumMemberGroup
(
   MemberID
);

CREATE INDEX MemberGroup_2_idx on mvnforumMemberGroup
(
   GroupID
);

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

CREATE INDEX MemberPermission_1_idx on mvnforumMemberPermission
(
   MemberID
);

CREATE TABLE if not exists 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             DATETIME                       not null,
   FolderModifiedDate             DATETIME                       not null,
   primary key (FolderName, MemberID)
);

CREATE INDEX MessageFolder_1_idx on mvnforumMessageFolder
(
   MemberID
);

CREATE TABLE if not exists 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                       TEXT                           not null,
   PostCreationDate               DATETIME                       not null,
   PostLastEditDate               DATETIME                       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 if not exists 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                     TEXT                           not null,
   ThreadVoteCount                INT                            not null,
   ThreadVoteTotalStars           INT                            not null,
   ThreadCreationDate             DATETIME                       not null,
   ThreadLastPostDate             DATETIME                       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 if not exists 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              DATETIME                       not null,
   WatchLastSentDate              DATETIME                       not null,
   WatchEndDate                   DATETIME                       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 if not exists 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                     TEXT                           not null,
   AttachCreationIP               VARCHAR(20)                    not null,
   AttachCreationDate             DATETIME                       not null,
   AttachModifiedDate             DATETIME                       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 if not exists 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 if not exists mvnforumFavoriteThread
(
   MemberID                       INT                            not null,
   ThreadID                       INT                            not null,
   ForumID                        INT                            not null,
   FavoriteCreationDate           DATETIME                       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 if not exists 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 if not exists 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                    TEXT                           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            DATETIME                       not null,
   primary key (MessageID)
);

CREATE INDEX Message_1_idx on mvnforumMessage
(
   FolderName,
   MemberID
);

CREATE INDEX Message_2_idx on mvnforumMessage
(
   MessageSenderID
);

CREATE TABLE if not exists mvnforumMessageStatistics
(
   FromID                         INT                            not null,
   ToID                           INT                            not null,
   MessageCreationDate            DATETIME                       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 if not exists 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                   TEXT                           not null,
   PmAttachCreationIP             VARCHAR(20)                    not null,
   PmAttachCreationDate           DATETIME                       not null,
   PmAttachModifiedDate           DATETIME                       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 if not exists 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);

... this post is sponsored by my books ...

#1 New Release!

FP Best Seller

 

new blog posts

 

Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.

A percentage of advertising revenue from
pages under the /java/jwarehouse URI on this website is
paid back to open source projects.