onetforums_mysql.sql

一款基于J2EE轻量级架构的社区系统

/**
  * Database script for onet forums 4.1
  * MYSQL script
  * Creation Date:2006/7/25
  */
CREATE TABLE onetUser (
	key_id BIGINT NOT NULL,
	username VARCHAR (50) UNIQUE NOT NULL,
	passwordEncrypted VARCHAR (16) NOT NULL,
	email VARCHAR (255) NOT NULL,
	emailVisible INTEGER NOT NULL,
	activeValue INTEGER NOT NULL,
	reminderQuestion VARCHAR (255) NOT NULL,
	reminderAnswer TEXT NOT NULL,
	modificationTime BIGINT NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_user PRIMARY KEY (key_id)
);     
CREATE INDEX onet_ix_usr_em ON onetUser (email);
CREATE INDEX onet_ix_usr_av ON onetUser (activeValue);
CREATE INDEX onet_ix_usr_ct ON onetUser (creationTime DESC);
CREATE INDEX onet_ix_usr_fxa ON onetUser (username,passwordEncrypted);

CREATE TABLE onetUserProp (
	objectID BIGINT NOT NULL,
	propName VARCHAR (150) NOT NULL,
	propValue TEXT 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 TEXT NOT NULL,
	modificationTime BIGINT NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_gr PRIMARY KEY (key_id)
);

CREATE TABLE onetGroupProp (
	objectID BIGINT NOT NULL,
	propName VARCHAR (150) NOT NULL,
	propValue TEXT 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)
);

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_uprm ON onetUserPerm (objectID,objectType,userID,permission);
CREATE INDEX onet_ix_uprm_fxa ON onetUserPerm (objectID,objectType);
CREATE INDEX onet_ix_uprm_fxb ON onetUserPerm (objectID,objectType,permission);
CREATE INDEX onet_ix_uprm_fxc ON onetUserPerm (objectType,userID,permission);
CREATE INDEX onet_ix_uprm_fxd ON onetUserPerm (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_gprm ON onetGroupPerm (objectID,objectType,groupID,permission);
CREATE INDEX onet_ix_gprm_fxa ON onetGroupPerm (objectID,objectType);
CREATE INDEX onet_ix_gprm_fxb ON onetGroupPerm (objectID,objectType,permission);
CREATE INDEX onet_ix_gprm_fxc ON onetGroupPerm (objectType,groupID,permission);
CREATE INDEX onet_ix_gprm_fxd ON onetGroupPerm (groupID,permission);

CREATE TABLE onetProperty (
	propName VARCHAR (150) NOT NULL,
	propValue TEXT NOT NULL,
	CONSTRAINT onet_pk_pr PRIMARY KEY (propName)
);

CREATE TABLE onetZone (
	key_id BIGINT NOT NULL,
	parentID BIGINT NOT NULL,
	zoneName VARCHAR (255) NOT NULL,
	description TEXT NOT NULL,
	indexValue INTEGER NOT NULL,
	modificationTime BIGINT NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_zo PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_zone_pi ON onetZone (parentID);
CREATE INDEX onet_ix_zone_iv ON onetZone (indexValue);
CREATE INDEX onet_ix_zone_mt ON onetZone (modificationTime DESC);
CREATE INDEX onet_ix_zone_ct ON onetZone (creationTime DESC);

CREATE TABLE onetZoneProp (
	objectID BIGINT NOT NULL,
	propName VARCHAR (150) NOT NULL,
	propValue TEXT NOT NULL,
	CONSTRAINT onet_pk_zopr PRIMARY KEY (objectID,propName)
);

CREATE TABLE onetForum (
	key_id BIGINT NOT NULL,
	zoneID BIGINT NOT NULL,
	forumName VARCHAR (255) NOT NULL,
	description TEXT NOT NULL,
	indexValue INTEGER NOT NULL,
	modificationtime BIGINT NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fo PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_forum_zi ON onetForum (zoneID);
CREATE INDEX onet_ix_forum_iv ON onetForum (indexValue);
CREATE INDEX onet_ix_forum_mt ON onetForum (modificationtime DESC);
CREATE INDEX onet_ix_fforum_ct ON onetForum (creationTime DESC);

CREATE TABLE onetForumProp (
	objectID BIGINT NOT NULL,
	propName VARCHAR (150) NOT NULL,
	propValue TEXT NOT NULL,
	CONSTRAINT onet_pk_fop PRIMARY KEY (objectID,propName)
);

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)
);
CREATE INDEX onet_ix_foth_fi ON onetForumThread (forumID);
CREATE INDEX onet_ix_foth_ui ON onetForumThread (userID);
CREATE INDEX onet_ix_foth_mc ON onetForumThread (msgCount);
CREATE INDEX onet_ix_foth_vc ON onetForumThread (viewCount DESC);
CREATE INDEX onet_ix_foth_mt ON onetForumThread (modificationTime DESC);
CREATE INDEX onet_ix_foth_ct ON onetForumThread (creationTime DESC);
CREATE INDEX onet_ix_foth_fxa ON onetForumThread (forumID,categoryID);
CREATE INDEX onet_ix_foth_fxb ON onetForumThread (forumID,modValue,delValue);
CREATE INDEX onet_ix_foth_fxc ON onetForumThread (forumID,key_id);
CREATE INDEX onet_ix_foth_fxd ON onetForumThread (forumID,modValue,delValue,modificationTime DESC);
CREATE INDEX onet_ix_foth_fxe ON onetForumThread (forumID,userID,modValue,delValue);

CREATE TABLE onetForumThreadProp (
	objectID BIGINT NOT NULL,
	propName VARCHAR (150) NOT NULL,
	propValue TEXT NOT NULL,
	CONSTRAINT onet_pk_fotp PRIMARY KEY (objectID,propName)
);

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 TEXT NOT NULL,
	modValue INTEGER NOT NULL,
	delValue INTEGER NOT NULL,
	viewCount INTEGER NOT NULL,
	tface VARCHAR(20) NOT NULL,
	ip VARCHAR(30) NOT NULL,
	modificationTime BIGINT NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_fom PRIMARY KEY(key_id)
);
CREATE INDEX onet_ix_fome_mt ON onetForumMessage (modificationTime DESC);
CREATE INDEX onet_ix_fome_ct ON onetForumMessage (creationTime DESC);
CREATE INDEX onet_ix_fome_fxa ON onetForumMessage (parentID,threadID,forumID,userID,modValue,delValue);
CREATE INDEX onet_ix_fome_fi ON onetForumMessage (forumID);
CREATE INDEX onet_ix_fome_th ON onetForumMessage (threadID);
CREATE INDEX onet_ix_fome_fxb ON onetForumMessage (parentID,threadID,modValue);
CREATE INDEX onet_ix_fome_fxc ON onetForumMessage (threadID,parentID,delValue,modValue,key_id);
CREATE INDEX onet_ix_fome_fxd ON onetForumMessage (threadID,modValue,creationTime DESC);
CREATE INDEX onet_ix_fome_fxe ON onetForumMessage (userID,modValue,delValue,key_id DESC);
CREATE INDEX onet_ix_fome_fxf ON onetForumMessage (forumID,modValue,delValue,modificationTime DESC);
CREATE INDEX onet_ix_fome_fxg ON onetForumMessage (forumID,modValue,delValue,creationTime DESC);

CREATE TABLE onetForumMessageProp (
	objectID BIGINT NOT NULL,
	propName VARCHAR (150) NOT NULL,
	propValue TEXT 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);

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)
);
CREATE INDEX onet_ix_ftt_fxa ON onetForumThreadTop (objectID,objectType,creationTime DESC);

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

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

CREATE TABLE onetAttachData (
	attachID BIGINT NOT NULL,
	attachData LONGBLOB NOT NULL,
	CONSTRAINT onet_pk_atda PRIMARY KEY (attachID)
);

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)
);
CREATE INDEX onet_ix_focat_fx ON onetForumCategory (forumID,indexValue,creationTime DESC);

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)
);
CREATE INDEX onet_ix_foau_fi ON onetForumAuthorized (forumID);

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

CREATE TABLE onetAvatarProp (
	objectID BIGINT NOT NULL,
	propName VARCHAR (150) NOT NULL,
	propValue TEXT 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 INDEX onet_ix_avusr_au ON onetAvatarUser (userID,avatarID);

CREATE TABLE onetFaction (
	key_id BIGINT NOT NULL,
	name VARCHAR (255) NOT NULL,
	description TEXT NOT NULL,
	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 INDEX onet_ix_fri_ct ON onetFriend (creationTime DESC);
CREATE INDEX onet_ix_fri_fxa ON onetFriend (userID,memberID,groupID);

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 INDEX onet_ix_frgp_ui ON onetFriendGroup (userID);

CREATE TABLE onetPMessage (
	key_id BIGINT NOT NULL,
	userID BIGINT NOT NULL,
	senderID BIGINT NOT NULL,
	recipientID BIGINT NOT NULL,
	subject VARCHAR(255) NOT NULL,