|
|
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);
|
The search page
Other source code files at this package level
Click here to learn more about this project
