|
|
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_sqlserver.sql,v 1.13 2005/01/27 09:08:03 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.13 $
-- $Date: 2005/01/27 09:08:03 $
-- Database : Sql Server
-- Driver : com.microsoft.jdbc.sqlserver.SQLServerDriver
-- Url : jdbc:microsoft:sqlserver://hostname:port;DatabaseName=<database>[;property=value...]
-- Download driver at : http://www.microsoft.com/sql/downloads/default.asp
-- Things should be considered when port this file to other database
-- AUTO_INCREMENT : IDENTITY
-- LONGVARCHAR : NTEXT
-- DATE : DATETIME
-- TIMESTAMP : DATETIME
-- VARCHAR : NVARCHAR
-- now() : GETDATE()
-- 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 IDENTITY,
ParentCategoryID INT not null,
CategoryName NVARCHAR(250) not null,
CategoryDesc NTEXT 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 mvnforumForum
(
ForumID INT not null IDENTITY,
CategoryID INT not null,
LastPostMemberName NVARCHAR(30) not null,
ForumName NVARCHAR(250) not null,
ForumDesc NTEXT 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 NVARCHAR(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 IDENTITY,
GroupOwnerID INT not null,
GroupOwnerName NVARCHAR(30) not null,
GroupName NVARCHAR(250) not null,
GroupDesc NTEXT not null,
GroupOption INT not null,
GroupCreationDate DATETIME not null,
GroupModifiedDate DATETIME not null,
primary key (GroupID),
unique (GroupName)
);
CREATE TABLE mvnforumMember
(
MemberID INT not null IDENTITY,
MemberName NVARCHAR(30) not null,
MemberPassword NVARCHAR(200) not null,
MemberFirstEmail NVARCHAR(60) not null,
MemberEmail NVARCHAR(60) not null,
MemberEmailVisible SMALLINT not null,
MemberNameVisible SMALLINT not null,
MemberFirstIP NVARCHAR(20) not null,
MemberLastIP NVARCHAR(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 NVARCHAR(40) not null,
MemberTempPassword NVARCHAR(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 NVARCHAR(250) not null,
MemberTimeZone INT not null,
MemberSignature NVARCHAR(250) not null,
MemberAvatar NVARCHAR(200) not null,
MemberSkin NVARCHAR(70) not null,
MemberLanguage NVARCHAR(70) not null,
MemberFirstname NVARCHAR(70) not null,
MemberLastname NVARCHAR(70) not null,
MemberGender SMALLINT not null,
MemberBirthday DATETIME not null,
MemberAddress NVARCHAR(150) not null,
MemberCity NVARCHAR(70) not null,
MemberState NVARCHAR(70) not null,
MemberCountry NVARCHAR(70) not null,
MemberPhone NVARCHAR(40) not null,
MemberMobile NVARCHAR(40) not null,
MemberFax NVARCHAR(40) not null,
MemberCareer NVARCHAR(50) not null,
MemberHomepage NVARCHAR(200) not null,
MemberYahoo NVARCHAR(70) not null,
MemberAol NVARCHAR(70) not null,
MemberIcq NVARCHAR(70) not null,
MemberMsn NVARCHAR(70) not null,
MemberCoolLink1 NVARCHAR(200) not null,
MemberCoolLink2 NVARCHAR(200) not null,
primary key (MemberID),
unique (MemberEmail),
unique (MemberName)
);
CREATE TABLE mvnforumMemberGroup
(
GroupID INT not null,
MemberID INT not null,
MemberName NVARCHAR(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 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 NVARCHAR(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 mvnforumPost
(
PostID INT not null IDENTITY,
ParentPostID INT not null,
ForumID INT not null,
ThreadID INT not null,
MemberID INT not null,
MemberName NVARCHAR(30) not null,
LastEditMemberName NVARCHAR(30) not null,
PostTopic NVARCHAR(250) not null,
PostBody NTEXT not null,
PostCreationDate DATETIME not null,
PostLastEditDate DATETIME not null,
PostCreationIP NVARCHAR(20) not null,
PostLastEditIP NVARCHAR(20) not null,
PostEditCount SMALLINT not null,
PostFormatOption INT not null,
PostOption INT not null,
PostStatus INT not null,
PostIcon NVARCHAR(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 IDENTITY,
ForumID INT not null,
MemberName NVARCHAR(30) not null,
LastPostMemberName NVARCHAR(30) not null,
ThreadTopic NVARCHAR(250) not null,
ThreadBody NTEXT 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 NVARCHAR(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 IDENTITY,
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 mvnforumAttachment
(
AttachID INT not null IDENTITY,
PostID INT not null,
MemberID INT not null,
AttachFilename NVARCHAR(250) not null,
AttachFileSize INT not null,
AttachMimeType NVARCHAR(70) not null,
AttachDesc NTEXT not null,
AttachCreationIP NVARCHAR(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 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 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 mvnforumRank
(
RankID INT not null IDENTITY,
RankMinPosts INT not null,
RankLevel INT not null,
RankTitle NVARCHAR(250) not null,
RankImage NVARCHAR(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 IDENTITY,
FolderName NVARCHAR(30) not null,
MemberID INT not null,
MessageSenderID INT not null,
MessageSenderName NVARCHAR(30) not null,
MessageToList NVARCHAR(250) not null,
MessageCcList NVARCHAR(250),
MessageBccList NVARCHAR(250),
MessageTopic NVARCHAR(250) not null,
MessageBody NTEXT not null,
MessageType INT not null,
MessageOption INT not null,
MessageStatus INT not null,
MessageReadStatus INT not null,
MessageNotify INT not null,
MessageIcon NVARCHAR(10) not null,
MessageAttachCount INT not null,
MessageIP NVARCHAR(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 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 mvnforumPmAttachment
(
PmAttachID INT not null IDENTITY,
MemberID INT not null,
PmAttachFilename NVARCHAR(250) not null,
PmAttachFileSize INT not null,
PmAttachMimeType NVARCHAR(70) not null,
PmAttachDesc NTEXT not null,
PmAttachCreationIP NVARCHAR(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 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
--
SET IDENTITY_INSERT mvnforumMember ON
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', GETDATE(), GETDATE(), GETDATE(), GETDATE(), '0', '0', '', '', '0', '0', '10', '0', '0', '0', '0', '', '0', '', '', '', '', '', '', '1', GETDATE(), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
SET IDENTITY_INSERT mvnforumMember OFF
--
-- data for table mvnforumMessageFolder
--
INSERT INTO mvnforumMessageFolder (FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate)
VALUES ('Inbox', '1', '0', 0, 0, 0, GETDATE(), GETDATE());
INSERT INTO mvnforumMessageFolder (FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate)
VALUES ('Sent', '1', '2', 0, 0, 0, GETDATE(), GETDATE());
--
-- data for table mvnforumGroups
--
SET IDENTITY_INSERT mvnforumGroups ON
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate)
VALUES ('1', '0', '', 'Guest', 'All anonymous users belong to this group.', '0', GETDATE(), GETDATE());
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate)
VALUES ('2', '0', '', 'Member', 'All registered users belong to this group.', '0', GETDATE(), GETDATE());
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate)
VALUES ('3', '1', 'admin', 'Admin', 'This group have SystemAdmin permission by default.', '0', GETDATE(), GETDATE());
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', GETDATE(), GETDATE());
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', GETDATE(), GETDATE());
SET IDENTITY_INSERT mvnforumGroups OFF
--
-- data for table mvnforumMemberGroup
--
INSERT INTO mvnforumMemberGroup (GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate)
VALUES ('3', '1', 'admin', '0', GETDATE(), GETDATE());
--
-- 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
