onetforums_db2.sql

一个用JSP编程实现的论坛BBS

-- //
-- // Database script for onet forums 4.0
-- // DB2 script
-- // Creation Date:2006/4/25
-- //
CREATE TABLE onetUser (
	key_id BIGINT NOT NULL ,
	username VARCHAR (50) UNIQUE NOT NULL ,
	passwordEncrypted VARCHAR (50) NOT NULL ,
	email VARCHAR (255) NOT NULL ,
	emailVisible INTEGER NOT NULL ,
	activeValue INTEGER NOT NULL ,
	reminderQuestion VARCHAR (255),
	reminderAnswer VARCHAR (2000) ,
	creationTime BIGINT NOT NULL ,
	modificationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_us PRIMARY KEY(key_id)
);

CREATE TABLE onetUserProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue VARCHAR (2000) NOT NULL,
	CONSTRAINT onet_pk_uspr PRIMARY KEY(objectID,propName)
);

CREATE TABLE onetGroup (
	key_id BIGINT NOT NULL ,
	groupName VARCHAR (150) UNIQUE NOT NULL ,
	description VARCHAR (2000) ,
	creationTime BIGINT NOT NULL ,
	modificationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_gr PRIMARY KEY(key_id)
);

CREATE TABLE onetGroupProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue VARCHAR (2000) NOT NULL,
	CONSTRAINT onet_pk_grpr PRIMARY KEY(objectID,propName)
);

CREATE TABLE onetUserGroup (
	userID BIGINT NOT NULL ,
	groupID BIGINT NOT NULL,
	CONSTRAINT onet_pk_usgr PRIMARY KEY(userID,groupID)
);
ALTER TABLE onetUserGroup ADD CONSTRAINT onet_fk_ug_gr FOREIGN KEY(groupID) REFERENCES onetGroup (key_id);
ALTER TABLE onetUserGroup ADD CONSTRAINT onet_fk_ug_us FOREIGN KEY(userID) REFERENCES onetUser(key_id);

CREATE TABLE onetUserPerm (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	userID BIGINT NOT NULL ,
	permission INTEGER NOT NULL,
	CONSTRAINT onet_pk_uspe PRIMARY KEY(key_id)
);
CREATE UNIQUE INDEX onet_uk_uspe ON onetUserPerm (objectType,objectID,userID,permission);

CREATE TABLE onetGroupPerm (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	groupID BIGINT NOT NULL ,
	permission INTEGER NOT NULL,
	CONSTRAINT onet_pk_grpe PRIMARY KEY(key_id)
);
CREATE UNIQUE INDEX onet_uk_grpe ON onetGroupPerm(objectID,objectType,groupID,permission);

CREATE TABLE onetZone (
	key_id BIGINT NOT NULL ,
	parentID BIGINT NOT NULL ,
	zoneName VARCHAR (255) NOT NULL ,
	description VARCHAR (2000),
	indexValue INTEGER NOT NULL,
	modificationTime BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL ,
	CONSTRAINT onet_pk_zo PRIMARY KEY(key_id)
);
CREATE TABLE onetZoneProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue VARCHAR (2000) NOT NULL,
	CONSTRAINT onet_pk_zopr PRIMARY KEY(objectID,propName)
);
ALTER TABLE onetZoneProp ADD CONSTRAINT onet_fk_zp_zo FOREIGN KEY(objectID) REFERENCES onetZone(key_id);

CREATE TABLE onetForum (
	key_id BIGINT NOT NULL ,
	zoneID BIGINT NOT NULL ,
	forumName VARCHAR (255) NOT NULL ,
	description VARCHAR (2000) ,
	indexValue INTEGER NOT NULL,
	modificationtime BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL ,
	CONSTRAINT onet_pk_fo PRIMARY KEY(key_id)
);
ALTER TABLE onetForum ADD CONSTRAINT onet_fk_fo_zo FOREIGN KEY(zoneID) REFERENCES onetZone (key_id);

CREATE TABLE onetForumProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue VARCHAR (2000) NOT NULL,
	CONSTRAINT onet_pk_fop PRIMARY KEY(objectID,propName)
);
ALTER TABLE onetForumProp ADD CONSTRAINT onet_fk_fopr_fo FOREIGN KEY(objectID) REFERENCES onetForum (key_id);

CREATE TABLE onetForumThread (
	key_id BIGINT NOT NULL ,
	forumID BIGINT NOT NULL ,
	categoryID BIGINT NOT NULL ,
	rootID BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	modValue INTEGER NOT NULL ,
	delValue INTEGER NOT NULL ,
	msgCount INTEGER NOT NULL ,
	viewCount INTEGER NOT NULL ,
	modificationTime BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fot PRIMARY KEY(key_id)
);
ALTER TABLE onetForumThread ADD	CONSTRAINT onet_fk_foth_fo FOREIGN KEY(forumID) REFERENCES onetForum (key_id);

CREATE TABLE onetForumThreadProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue VARCHAR (2000) NOT NULL,
	CONSTRAINT onet_pk_fotp PRIMARY KEY(objectID,propName)
);
ALTER TABLE onetForumThreadProp ADD CONSTRAINT onet_fk_fotp_ft FOREIGN KEY(objectID) REFERENCES onetForumThread (key_id);

CREATE TABLE onetForumMessage (
	key_id BIGINT NOT NULL ,
	parentID BIGINT NOT NULL ,
	threadID BIGINT NOT NULL ,
	forumID BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	author VARCHAR(255) NOT NULL ,
	subject VARCHAR (255) NOT NULL ,
	body LONG VARCHAR ,
	modValue INTEGER NOT NULL ,
	delValue INTEGER NOT NULL ,
	viewCount INTEGER NOT NULL ,
	modificationTime BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fom PRIMARY KEY(key_id)
);
ALTER TABLE onetForumMessage ADD CONSTRAINT onet_fk_fome_fo FOREIGN KEY(forumID) REFERENCES onetForum (key_id);
ALTER TABLE onetForumMessage ADD CONSTRAINT onet_fk_fome_foth FOREIGN KEY(threadID) REFERENCES onetForumThread (key_id);

CREATE TABLE onetForumMessageProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue VARCHAR (2000) NOT NULL,
	CONSTRAINT onet_pk_fomp PRIMARY KEY(objectID,propName)
);

CREATE TABLE onetForumThreadExc (
	key_id BIGINT NOT NULL ,
	forumID BIGINT NOT NULL,
	CONSTRAINT onet_pk_fote PRIMARY KEY(key_id)
);
CREATE INDEX onet_ix_fote ON onetForumThreadExc(forumID);
ALTER TABLE onetForumThreadExc ADD CONSTRAINT onet_fk_fte_ft FOREIGN KEY(key_id) REFERENCES onetForumThread (key_id);

CREATE TABLE onetForumThreadTop (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fott PRIMARY KEY(key_id)
);
ALTER TABLE onetForumThreadTop ADD CONSTRAINT onet_fk_fott_ft FOREIGN KEY(key_id) REFERENCES onetForumThread (key_id);

CREATE TABLE onetAttachment (
	key_id BIGINT NOT NULL ,
	objectID BIGINT ,
	objectType INTEGER NOT NULL ,
	fileName VARCHAR (255) NOT NULL ,
	fileSize BIGINT NOT NULL ,
	fileSpecs VARCHAR (50) ,
	contentType VARCHAR (50) NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_at PRIMARY KEY(key_id)
);

CREATE TABLE onetAttachmentProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue VARCHAR (2000) NOT NULL,
	CONSTRAINT onet_pk_atp PRIMARY KEY(objectID,propName)
);

CREATE TABLE onetAttachData (
	attachID BIGINT NOT NULL ,
	attachData BLOB NOT NULL ,
	CONSTRAINT onet_pk_atda PRIMARY KEY(attachID)
);
ALTER TABLE onetAttachData ADD	CONSTRAINT onet_fk_atda_at FOREIGN KEY(attachID) REFERENCES onetAttachment (key_id);

CREATE TABLE onetForumCategory(
    key_id BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    cateName VARCHAR (255) NOT NULL,
    cateDescription VARCHAR (255) NOT NULL,
    cateImage VARCHAR(255) NOT NULL,
    indexValue INTEGER NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_foc PRIMARY KEY(key_id)
);
ALTER TABLE onetForumCategory ADD CONSTRAINT onet_fk_fc_fo FOREIGN KEY (forumID) REFERENCES onetforum(key_id);

CREATE TABLE onetForumAuthorized(
    key_id BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    paidCount INTEGER NOT NULL,
    lastPaidTime BIGINT NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_foa PRIMARY KEY(key_id)
);
ALTER TABLE onetForumAuthorized ADD CONSTRAINT onet_fk_fau_fo FOREIGN KEY (forumID) REFERENCES onetforum(key_id);

CREATE TABLE onetAvatar (
	key_id BIGINT NOT NULL ,
	ownerID BIGINT ,
	CONSTRAINT onet_pk_av PRIMARY KEY(key_id)
);

CREATE TABLE onetAvatarProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue VARCHAR (2000) NOT NULL,
	CONSTRAINT onet_pk_avp PRIMARY KEY(objectID,propName)
);

CREATE TABLE onetAvatarUser (
	userID BIGINT NOT NULL ,
	avatarID BIGINT NOT NULL,
	CONSTRAINT onet_pk_avu PRIMARY KEY(userID)
);

CREATE TABLE onetFaction (
	key_id BIGINT NOT NULL ,
	name VARCHAR (255) NOT NULL ,
	description VARCHAR (2000) ,
	CONSTRAINT onet_pk_fac PRIMARY KEY(key_id)
);

CREATE TABLE onetFactionUser (
	factionID BIGINT NOT NULL ,
	userID BIGINT NOT NULL,
	CONSTRAINT onet_pk_faus PRIMARY KEY(factionID,userID)
);

CREATE TABLE onetFavorite (
	key_id BIGINT UNIQUE NOT NULL ,
	userID BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL,
	CONSTRAINT onet_pk_fav PRIMARY KEY(userID,objectID,objectType)
);

CREATE TABLE onetFriend (
	key_id BIGINT UNIQUE NOT NULL ,
	userID BIGINT NOT NULL ,
	memberID BIGINT NOT NULL ,
	groupID BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fr PRIMARY KEY(userID,memberID)
);

CREATE TABLE onetFriendGroup (
	key_id BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	name VARCHAR (255) NOT NULL,
	CONSTRAINT onet_pk_frg PRIMARY KEY(key_id)
);

CREATE TABLE onetPMessage (
	key_id BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	senderID BIGINT NOT NULL ,
	recipientID BIGINT NOT NULL ,
	subject VARCHAR(50) NOT NULL ,
	body LONG VARCHAR ,
	readValue INTEGER NOT NULL ,
	folderID BIGINT NOT NULL ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_pm PRIMARY KEY(key_id)
);

CREATE TABLE onetPMessageFldr (
	key_id BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	name VARCHAR (255) NOT NULL,
	CONSTRAINT onet_pk_pmfl PRIMARY KEY(key_id)
);

CREATE TABLE onetPMessageProp (
	objectID BIGINT NOT NULL ,
	propName VARCHAR (150) NOT NULL ,
	propValue VARCHAR (2000) NOT NULL,
	CONSTRAINT onet_pk_pmpr PRIMARY KEY(objectID,propName)
);