onetforums_mysql.sql

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

	body TEXT NOT NULL,
	readValue INTEGER NOT NULL,
	folderID BIGINT NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_pmsg PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_pmsg_ui ON onetPMessage (userID);
CREATE INDEX onet_ix_pmsg_si ON onetPMessage (senderID);
CREATE INDEX onet_ix_pmsg_ri ON onetPMessage (recipientID);
CREATE INDEX onet_ix_pmsg_rv ON onetPMessage (readValue);
CREATE INDEX onet_ix_pmsg_fl ON onetPMessage (folderID);
CREATE INDEX onet_ix_pmsg_ct ON onetPMessage (creationTime DESC);
CREATE INDEX onet_ix_pmsg_fxa ON onetPMessage (userID,folderID,creationTime DESC);

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 INDEX onet_ix_pmfl_ui ON onetPMessageFldr (userID);

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

CREATE TABLE onetPoll (
	key_id BIGINT NOT NULL,
	pollMode INTEGER NOT NULL,
	lifeTime INTEGER NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_poll PRIMARY KEY (key_id)
);

CREATE TABLE onetPollItem (
	key_id BIGINT NOT NULL,
	pollID BIGINT NOT NULL,
	itemName VARCHAR (255) NOT NULL,
	voteCount INTEGER NOT NULL,
	CONSTRAINT onet_pk_poit PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_poit_fxa ON onetPollItem (pollID,key_id);

CREATE TABLE onetVoteTrack (
	itemID BIGINT NOT NULL,
	userID BIGINT NOT NULL,
	voteTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_votak PRIMARY KEY (itemID,userID)
);
CREATE INDEX onet_ix_votak_it ON onetVoteTrack (itemID);

CREATE TABLE onetReadTrack (
	userID BIGINT NOT NULL,
	objectID BIGINT NOT NULL,
	objectType INTEGER NOT NULL,
	readTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_retr PRIMARY KEY (userID,objectID,objectType)
);
CREATE INDEX onet_ix_retr_rt ON onetReadTrack (readTime DESC);

CREATE TABLE onetSequence (
	key_id INTEGER NOT NULL,
	currentID BIGINT NOT NULL,
	CONSTRAINT onet_pk_sequ PRIMARY KEY (key_id)
);

CREATE TABLE onetStatusLevel (
	key_id BIGINT NOT NULL,
	name VARCHAR (150) UNIQUE NOT NULL,
	description TEXT NOT NULL,
	imagePath VARCHAR (255) NOT NULL,
	minPoints INTEGER NOT NULL,
	maxPoints INTEGER NOT NULL,
	groupID BIGINT NOT NULL,
	CONSTRAINT onet_pk_stle PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_sl_gi ON onetStatusLevel (groupID);
CREATE INDEX onet_ix_sl_fxa ON onetStatusLevel (groupID,minPoints DESC);

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

CREATE TABLE onetStatusLevelPoints(
    key_id BIGINT NOT NULL,
    points INTEGER NOT NULL,
    CONSTRAINT onet_pk_stlepo PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_slp_pi ON onetStatusLevelPoints (points DESC);

CREATE TABLE onetLayout (
	key_id BIGINT NOT NULL,
	pathId VARCHAR (150) NOT NULL,
	name VARCHAR (255) NOT NULL,
	description TEXT NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_lay PRIMARY KEY (key_id)
);

CREATE TABLE onetSkin (
	key_id BIGINT NOT NULL,
	layoutID BIGINT NOT NULL,
	pathId VARCHAR (150) NOT NULL,
	name VARCHAR (255) NOT NULL,
	description TEXT NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_sk PRIMARY KEY (key_id)
);
CREATE UNIQUE INDEX onet_uk_skin ON onetSkin (pathId,layoutID);
CREATE INDEX onet_ix_skin_ct ON onetSkin (creationTime DESC);

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

CREATE TABLE onetStyle (
	key_id BIGINT NOT NULL,
	skinID BIGINT NOT NULL,
	filename VARCHAR (150) NOT NULL,
	name VARCHAR (255) NOT NULL,
	description TEXT NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_st PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_sty_si ON onetStyle (skinID);
CREATE INDEX onet_ix_sty_ct ON onetStyle (creationTime DESC);

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

CREATE TABLE onetTask (
	key_id BIGINT NOT NULL,
	name VARCHAR (255) NOT NULL,
	receiverType INTEGER NOT NULL,
	sendType INTEGER NOT NULL,
	activeType INTEGER NOT NULL,
	activeValue INTEGER NOT NULL,
	currentSequence INTEGER NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_ta PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_tsk_ct ON onetTask (creationTime);

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

CREATE TABLE onetTaskDetail (
	key_id BIGINT UNIQUE NOT NULL,
	taskID BIGINT NOT NULL,
	sequenceValue INTEGER NOT NULL,
	startTime BIGINT NOT NULL,
	endTime BIGINT NOT NULL,
	stopValue INTEGER NOT NULL,
	lastUserID BIGINT NOT NULL,
	sentTotal INTEGER NOT NULL,
	CONSTRAINT onet_pk_tade PRIMARY KEY (taskID,sequenceValue)
);

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

CREATE TABLE onetWatch (
	userID BIGINT NOT NULL,
	objectID BIGINT NOT NULL,
	objectType INTEGER NOT NULL,
	watchType INTEGER NOT NULL,
	scheduled INTEGER NOT NULL,
	expirable INTEGER NOT NULL,
	CONSTRAINT onet_pk_watch PRIMARY KEY (userID,objectID,objectType)
);
CREATE INDEX onet_ix_wch_ui ON onetWatch (userID);
CREATE INDEX onet_ix_wch_fxa ON onetWatch (userID,objectType);
CREATE INDEX onet_ix_wch_fxb ON onetWatch (objectID,objectType);
CREATE INDEX onet_ix_wch_fxc ON onetWatch (objectType,expirable);
CREATE INDEX onet_ix_wch_fxd ON onetWatch (objectID,objectType,watchType,scheduled);

CREATE TABLE onetWatchScheduled (
	key_id BIGINT NOT NULL,
	intervals INTEGER NOT NULL,
	prevNotifiedTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_wasc PRIMARY KEY (key_id)
);

CREATE TABLE onetAlliance(
    key_id BIGINT NOT NULL,
    forumName VARCHAR (150) NOT NULL,
    description VARCHAR (255) NOT NULL,
    forumURL VARCHAR (255) NOT NULL,
    logoURL VARCHAR (255) NOT NULL,
    showMode INTEGER NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_al PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_alli_fx ON onetAlliance (showMode ASC,creationTime DESC);

CREATE TABLE onetAnnounce (
	key_id BIGINT NOT NULL,
	userID BIGINT NOT NULL,
	objectID BIGINT NOT NULL,
	objectType INTEGER NOT NULL,
	annType INTEGER NOT NULL,
	subject VARCHAR (255) NOT NULL,
	body TEXT NOT NULL,
	viewCount INTEGER NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_an PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_ann_fxa ON onetAnnounce (key_id,annType);
CREATE INDEX onet_ix_ann_fxb ON onetAnnounce (objectID,objectType,annType);
CREATE INDEX onet_ix_ann_fxc ON onetAnnounce (userID,annType);
CREATE INDEX onet_ix_ann_fxd ON onetAnnounce (objectID,objectType,annType,creationTime DESC);

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

CREATE TABLE onetForumEvent(
    key_id BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    eventSubject VARCHAR(255) NOT NULL,
    eventBody TEXT NOT NULL,
    eventType INTEGER NOT NULL,
    userID BIGINT NOT NULL,
    fromIP VARCHAR(50) NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_foev PRIMARY KEY (key_id)
);

CREATE TABLE onetPluginProp (
    objectID VARCHAR (50) NOT NULL,
	propName VARCHAR (150) NOT NULL,
	propValue TEXT NOT NULL,
	CONSTRAINT onet_pk_plp PRIMARY KEY (objectID,propName)
);

CREATE TABLE onetPointCategory(
    key_id BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    categoryName VARCHAR (255) NOT NULL,
    pointUnit VARCHAR (50) NOT NULL,
    settings VARCHAR (255) NOT NULL,
    showValue INTEGER NOT NULL,
    tradeValue INTEGER NOT NULL,
    modifyValue INTEGER NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_poic PRIMARY KEY (key_id,forumID)
);

CREATE TABLE onetPoints(
    userID BIGINT NOT NULL,
    categoryID BIGINT NOT NULL,
    messageValue INTEGER NOT NULL,
    operationValue INTEGER NOT NULL,
    onlineValue INTEGER NOT NULL,
    loginValue INTEGER NOT NULL,
    CONSTRAINT onet_pk_poin PRIMARY KEY (userID,categoryID)
);

CREATE TABLE onetPointsheet (
	key_id BIGINT NOT NULL,
	currentTopics INTEGER NOT NULL,
	removedTopics INTEGER NOT NULL,
	currentReplies INTEGER NOT NULL,
	removedReplies INTEGER NOT NULL,
	excCount INTEGER NOT NULL,
	loginCount INTEGER NOT NULL,
	onlineTime BIGINT NOT NULL,
	lastLogin BIGINT NOT NULL,
	CONSTRAINT onet_pk_pos PRIMARY KEY(key_id)
);
CREATE INDEX onet_ix_ptsh_ot ON onetPointsheet (onlineTime ASC);
CREATE INDEX onet_ix_ptsh_ll ON onetPointsheet (lastLogin DESC);

CREATE TABLE onetTrade (
    key_id BIGINT NOT NULL,
	userID BIGINT NOT NULL,
	orderNo VARCHAR (100) UNIQUE NOT NULL,
	orderGross  INTEGER NOT NULL,
	orderType INTEGER NOT NULL,
	orderStatus INTEGER NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_tr PRIMARY KEY (key_id)
);
CREATE INDEX onet_ix_trde_fxa ON onetTrade(userID,orderStatus,creationTime DESC);

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

CREATE TABLE onetAPerm(
    objectID BIGINT NOT NULL,
    objectType INTEGER NOT NULL,
    userID INTEGER NOT NULL,
    userType INTEGER NOT NULL,
    keyName VARCHAR (150) NOT NULL,
    keyValue VARCHAR (255) NOT NULL,
    CONSTRAINT onet_pk_aper PRIMARY KEY (objectID,objectType,userID,userType,keyName)
);
CREATE INDEX onet_ix_aper_fxa ON onetAPerm (objectID,objectType DESC,userID,userType);
CREATE INDEX onet_ix_aper_fxb ON onetAPerm (userID,userType);
CREATE INDEX onet_ix_aper_fxc ON onetAPerm (objectID,objectType);

insert into onetSequence values(1,2);
insert into onetSequence values(4,2);
insert into onetUser values(1,'admin','7a57a5a743894a0e','',0,0,'','',1119358440630,1119358440630);
insert into onetUserPerm values(1,-1,1,1,1);