onetforums_oracle.sql

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

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

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

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

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

CREATE TABLE onetUserGroup (
	userID INTEGER NOT NULL ,
	groupID INTEGER 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 INTEGER NOT NULL ,
	objectID INTEGER NOT NULL ,
	objectType INTEGER NOT NULL ,
	userID INTEGER 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 INTEGER NOT NULL ,
	objectID INTEGER NOT NULL ,
	objectType INTEGER NOT NULL ,
	groupID INTEGER 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 INTEGER NOT NULL ,
	parentID INTEGER NOT NULL ,
	zoneName VARCHAR2 (255) NOT NULL ,
	description VARCHAR2 (2000),
	indexValue INTEGER NOT NULL,
	modificationTime INTEGER NOT NULL ,
	creationTime INTEGER NOT NULL ,
	CONSTRAINT onet_pk_zo PRIMARY KEY(key_id)
);
CREATE TABLE onetZoneProp (
	objectID INTEGER NOT NULL ,
	propName VARCHAR2 (150) NOT NULL ,
	propValue VARCHAR2 (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 INTEGER NOT NULL ,
	zoneID INTEGER NOT NULL ,
	forumName VARCHAR2 (255) NOT NULL ,
	description VARCHAR2 (2000) ,
	indexValue INTEGER NOT NULL,
	modificationtime INTEGER NOT NULL ,
	creationTime INTEGER 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 INTEGER NOT NULL ,
	propName VARCHAR2 (150) NOT NULL ,
	propValue VARCHAR2 (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 INTEGER NOT NULL ,
	forumID INTEGER NOT NULL ,
	categoryID INTEGER NOT NULL ,
	rootID INTEGER NOT NULL ,
	userID INTEGER NOT NULL ,
	modValue INTEGER NOT NULL ,
	delValue INTEGER NOT NULL ,
	msgCount INTEGER NOT NULL ,
	viewCount INTEGER NOT NULL ,
	modificationTime INTEGER NOT NULL ,
	creationTime INTEGER 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 INTEGER NOT NULL ,
	propName VARCHAR2 (150) NOT NULL ,
	propValue VARCHAR2 (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 INTEGER NOT NULL ,
	parentID INTEGER NOT NULL ,
	threadID INTEGER NOT NULL ,
	forumID INTEGER NOT NULL ,
	userID INTEGER NOT NULL ,
	author VARCHAR2(255) NOT NULL ,
	subject VARCHAR2 (255) NOT NULL ,
	body LONG ,
	modValue INTEGER NOT NULL ,
	delValue INTEGER NOT NULL ,
	viewCount INTEGER NOT NULL ,
	modificationTime INTEGER NOT NULL ,
	creationTime INTEGER 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 INTEGER NOT NULL ,
	propName VARCHAR2 (150) NOT NULL ,
	propValue VARCHAR2 (2000) NOT NULL,
	CONSTRAINT onet_pk_fomp PRIMARY KEY(objectID,propName)
);

CREATE TABLE onetForumThreadExc (
	key_id INTEGER NOT NULL ,
	forumID INTEGER 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 INTEGER NOT NULL ,
	objectID INTEGER NOT NULL ,
	objectType INTEGER NOT NULL ,
	creationTime INTEGER 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 INTEGER NOT NULL ,
	objectID INTEGER ,
	objectType INTEGER NOT NULL ,
	fileName VARCHAR2 (255) NOT NULL ,
	fileSize INTEGER NOT NULL ,
	fileSpecs VARCHAR2 (50) ,
	contentType VARCHAR2 (50) NOT NULL ,
	creationTime INTEGER NOT NULL,
	CONSTRAINT onet_pk_at PRIMARY KEY(key_id)
);

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

CREATE TABLE onetAttachData (
	attachID INTEGER 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 INTEGER NOT NULL,
    forumID INTEGER NOT NULL,
    cateName VARCHAR2 (255) NOT NULL,
    cateDescription VARCHAR2 (255) NOT NULL,
    cateImage VARCHAR2(255) NOT NULL,
    indexValue INTEGER NOT NULL,
    creationTime INTEGER 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 INTEGER NOT NULL,
    forumID INTEGER NOT NULL,
    paidCount INTEGER NOT NULL,
    lastPaidTime INTEGER NOT NULL,
    creationTime INTEGER 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 INTEGER NOT NULL ,
	ownerID INTEGER ,
	CONSTRAINT onet_pk_av PRIMARY KEY(key_id)
);

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

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

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

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

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

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

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

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

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

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

CREATE TABLE onetPoll (