|
|
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_SAPDB.sql,v 1.18 2005/01/27 09:08:03 minhnn Exp $
-- $Author: minhnn $
-- $Revision: 1.18 $
-- $Date: 2005/01/27 09:08:03 $
-- Database : SAPDB Database
-- Driver : com.sap.dbtech.jdbc.DriverSapDB
-- Url : jdbc:sapdb://host/DB_Name
-- Thanks theKM for port to this SAPDB script
--
-- Note: JDBC driver (sapdbc.jar) is typically found in the $SAPDB_HOME/dep/misc
-- directory of your installed SAPDB software.
-- For setup unicode with SAPDB: http://www.sapdb.org/pdf/unicode_eng.pdf
--
-- Things should be considered when port this file to other database
-- AUTO_INCREMENT : INT DEFAULT SERIAL
-- LONGVARCHAR : LONG
-- DATE : DATE
-- TIMESTAMP : TIMESTAMP
-- VARCHAR : VARCHAR
-- now() : timestamp (for time) or sydate (for date)
--
--
-- Note: SAPDB 7.4 also required the unique constraints on the tables to be
-- explicitly named for those tables with more than one unique constraint.
-- implemented here has having a unique name on all of them to err on the
-- side of completeness.
-- 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 DEFAULT SERIAL not null,
ParentCategoryID INT not null,
CategoryName VARCHAR(250) not null,
CategoryDesc LONG 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),
constraint constraint_category_name unique (CategoryName)
);
CREATE TABLE mvnforumForum
(
ForumID INT DEFAULT SERIAL not null,
CategoryID INT not null,
LastPostMemberName VARCHAR(30) not null,
ForumName VARCHAR(250) not null,
ForumDesc LONG 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),
constraint constraint_forum_name_id 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 DEFAULT SERIAL not null,
GroupOwnerID INT not null,
GroupOwnerName VARCHAR(30) not null,
GroupName VARCHAR(250) not null,
GroupDesc LONG not null,
GroupOption INT not null,
GroupCreationDate TIMESTAMP not null,
GroupModifiedDate TIMESTAMP not null,
primary key (GroupID),
constraint constraint_groups_name unique (GroupName)
);
CREATE TABLE mvnforumMember
(
MemberID INT DEFAULT SERIAL not null,
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),
constraint constraint_member_email unique (MemberEmail),
constraint constraint_member_name 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 DEFAULT SERIAL not null,
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 LONG 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 DEFAULT SERIAL not null,
ForumID INT not null,
MemberName VARCHAR(30) not null,
LastPostMemberName VARCHAR(30) not null,
ThreadTopic VARCHAR(250) not null,
ThreadBody LONG 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 DEFAULT SERIAL not null,
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),
constraint constraint_watch_member_cat 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 DEFAULT SERIAL not null,
PostID INT not null,
MemberID INT not null,
AttachFilename VARCHAR(250) not null,
AttachFileSize INT not null,
AttachMimeType VARCHAR(70) not null,
AttachDesc LONG 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 DEFAULT SERIAL not null,
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),
constraint constraint_rank_minposts unique (RankMinPosts),
constraint constraint_rank_title unique (RankTitle)
);
CREATE TABLE mvnforumMessage
(
MessageID INT DEFAULT SERIAL not null,
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 LONG 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 DEFAULT SERIAL not null,
MemberID INT not null,
PmAttachFilename VARCHAR(250) not null,
PmAttachFileSize INT not null,
PmAttachMimeType VARCHAR(70) not null,
PmAttachDesc LONG 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', timestamp, timestamp, timestamp, timestamp, '0', '0', '', '', '0', '0', '10', '0', '0', '0', '0', '', '0', '', '', '', '', '', '', '1', '1900-01-01', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
--
-- data for table mvnforumMessageFolder
--
INSERT INTO mvnforumMessageFolder (FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate)
VALUES ('Inbox', '1', '0', 0, 0, 0, timestamp, timestamp);
INSERT INTO mvnforumMessageFolder (FolderName, MemberID, FolderOrder, FolderStatus, FolderOption, FolderType, FolderCreationDate, FolderModifiedDate)
VALUES ('Sent', '1', '2', 0, 0, 0, timestamp, timestamp);
--
-- 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', timestamp, timestamp);
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate)
VALUES ('2', '0', '', 'Member', 'All registered users belong to this group.', '0', timestamp, timestamp);
INSERT INTO mvnforumGroups (GroupID, GroupOwnerID, GroupOwnerName, GroupName, GroupDesc, GroupOption, GroupCreationDate, GroupModifiedDate)
VALUES ('3', '1', 'admin', 'Admin', 'This group have SystemAdmin permission by default.', '0', timestamp, timestamp);
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', timestamp, timestamp);
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', timestamp, timestamp);
--
-- data for table mvnforumMemberGroup
--
INSERT INTO mvnforumMemberGroup (GroupID, MemberID, MemberName, Privilege, CreationDate, ModifiedDate)
VALUES ('3', '1', 'admin', '0', timestamp, timestamp);
--
-- 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
