|
|
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_postgresql.sql,v 1.17 2005/01/27 09:08:03 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.17 $
-- $Date: 2005/01/27 09:08:03 $
-- Database : PostgreSql
-- Driver : org.postgresql.Driver
-- Url : jdbc:postgresql://host:port/database
-- Note: you should use jdbc driver version 7.3 or later
-- You could download latest version here http://jdbc.postgresql.org/download.html
-- Setup unicode with postgreSQL: http://developer.postgresql.org/docs/postgres/multibyte.html
-- Short tutorial (assume the database name is mvnforum) :
--
-- $ createdb mvnforum
-- $ psql mvnforum
-- mvnforum=> \i mvnForum_postgresql.sql
--
-- Things should be considered when port this file to other database
-- AUTO_INCREMENT : DEFAULT nextval('table_seq')
-- LONGVARCHAR : TEXT
-- DATE : DATE
-- TIMESTAMP : TIMESTAMP
-- VARCHAR : VARCHAR
-- now() : now()
--
-- Note : CREATE TABLE / PRIMARY KEY will create implicit index
-- CREATE TABLE / UNIQUE will create implicit index
-- 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 sequences
--
-- drop sequence mvnforumCategory_seq;
-- drop sequence mvnforumForum_seq;
-- drop sequence mvnforumGroups_seq;
-- drop sequence mvnforumMember_seq;
-- drop sequence mvnforumPost_seq;
-- drop sequence mvnforumThread_seq;
-- drop sequence mvnforumWatch_seq;
-- drop sequence mvnforumAttachment_seq;
-- drop sequence mvnforumRank_seq;
-- drop sequence mvnforumMessage_seq;
-- drop sequence mvnforumPmAttachment_seq;
--
-- create sequences
--
create sequence mvnforumCategory_seq;
create sequence mvnforumForum_seq;
create sequence mvnforumGroups_seq START 6;
create sequence mvnforumMember_seq START 2;
create sequence mvnforumPost_seq;
create sequence mvnforumThread_seq;
create sequence mvnforumWatch_seq;
create sequence mvnforumAttachment_seq;
create sequence mvnforumRank_seq;
create sequence mvnforumMessage_seq;
create sequence mvnforumPmAttachment_seq;
CREATE TABLE mvnforumCategory
(
CategoryID INT not null DEFAULT nextval('mvnforumCategory_seq'),
ParentCategoryID INT not null,
CategoryName VARCHAR(250) not null,
CategoryDesc TEXT 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 DEFAULT nextval('mvnforumForum_seq'),
CategoryID INT 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 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 DEFAULT nextval('mvnforumGroups_seq'),
GroupOwnerID INT not null,
GroupOwnerName VARCHAR(30) not null,
GroupName VARCHAR(250) not null,
GroupDesc TEXT 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 DEFAULT nextval('mvnforumMember_seq'),
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 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 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 DEFAULT nextval('mvnforumPost_seq'),
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 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 DEFAULT nextval('mvnforumThread_seq'),
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 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 DEFAULT nextval('mvnforumWatch_seq'),
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 DEFAULT nextval('mvnforumAttachment_seq'),
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 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 DEFAULT nextval('mvnforumRank_seq'),
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 DEFAULT nextval('mvnforumMessage_seq'),
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 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 DEFAULT nextval('mvnforumPmAttachment_seq'),
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 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);
|
The search page
Other source code files at this package level
Click here to learn more about this project
