onetforums_sqlserver.sql

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

);

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

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 TABLE onetSequence (
	key_id INTEGER NOT NULL ,
	currentID BIGINT NOT NULL,
	CONSTRAINT onet_pk_se PRIMARY KEY(key_id)
);

CREATE TABLE onetStatusLevel (
	key_id BIGINT NOT NULL ,
	name NVARCHAR (150) UNIQUE NOT NULL ,
	description NTEXT ,
	imagePath NVARCHAR (255) NOT NULL ,
	minPoints INTEGER ,
	maxPoints INTEGER ,
	groupID BIGINT,
	CONSTRAINT onet_pk_stle PRIMARY KEY(key_id)
);

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

CREATE TABLE onetLayout (
	key_id BIGINT NOT NULL ,
	pathId NVARCHAR (150) NOT NULL ,
	name NVARCHAR (255) NOT NULL ,
	description NVARCHAR (2000) ,
	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 NVARCHAR (150) NOT NULL ,
	name NVARCHAR (255) NOT NULL ,
	description NVARCHAR (2000) ,
	creationtime BIGINT NOT NULL,
	CONSTRAINT onet_pk_sk PRIMARY KEY(key_id)
);
CREATE UNIQUE INDEX onet_uk_sk ON onetSkin(pathId,layoutID);

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

CREATE TABLE onetStyle (
	key_id BIGINT NOT NULL ,
	skinID BIGINT NOT NULL ,
	filename NVARCHAR (150) NOT NULL,
	name NVARCHAR (255) NOT NULL ,
	description NVARCHAR (2000) ,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_st PRIMARY KEY(key_id)
);

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

CREATE TABLE onetTask (
	key_id BIGINT NOT NULL ,
	name NVARCHAR (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 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 NVARCHAR (150) NOT NULL ,
	propValue NTEXT NOT NULL,
	CONSTRAINT onet_pk_tadepr PRIMARY KEY(objectID,propName)
);

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


CREATE TABLE onetVoteTrack (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	userID BIGINT NOT NULL ,
	voteTime BIGINT ,
	CONSTRAINT onet_pk_votr PRIMARY KEY(objectID,objectType,userID)
);

CREATE TABLE onetWatch (
	key_id BIGINT UNIQUE NOT NULL,
	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_wa PRIMARY KEY(userID,objectID,objectType)
);

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 NVARCHAR (150) NOT NULL,
    description NVARCHAR (255) NOT NULL,
    forumURL NVARCHAR (255) NOT NULL,
    logoURL NVARCHAR (255) NOT NULL,
    showMode INTEGER NOT NULL,
    creationTime BIGINT NOT NULL,
    CONSTRAINT onet_pk_al PRIMARY KEY (key_id)
);

CREATE TABLE onetAnnounce (
	key_id BIGINT NOT NULL ,
	objectID BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	objectType INTEGER NOT NULL ,
	annType INTEGER DEFAULT 1,
	subject NVARCHAR (255)  ,
	body NTEXT NOT NULL ,
	viewCount INTEGER NOT NULL,
	creationTime BIGINT NOT NULL,
	CONSTRAINT onet_pk_an PRIMARY KEY (key_id)
);

CREATE TABLE onetAnnounceProp (
	objectID BIGINT NOT NULL ,
	propName NVARCHAR (150) NOT NULL ,
	propValue NVARCHAR (2000) NOT NULL ,
	CONSTRAINT onet_pk_anp PRIMARY KEY( objectID,	propName)
);

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

CREATE TABLE onetPluginProp (
    key_id NVARCHAR (50) NOT NULL ,
	propName NVARCHAR (150) NOT NULL ,
	propValue NTEXT NOT NULL,
	CONSTRAINT onet_pk_plp PRIMARY KEY(key_id,propName)
);
CREATE TABLE onetPointCategory(
    key_id BIGINT NOT NULL,
    forumID BIGINT NOT NULL,
    categoryName NVARCHAR (255) NOT NULL,
    pointUnit NVARCHAR (50) NOT NULL,
    settings NVARCHAR (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 TABLE onetTrade (
    key_id BIGINT NOT NULL ,
	userID BIGINT NOT NULL ,
	orderNo NVARCHAR (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 TABLE onetTradeProp (
	objectID BIGINT NOT NULL ,
	propName NVARCHAR (150) NOT NULL ,
	propValue NTEXT NOT NULL,
	CONSTRAINT onet_pk_trp PRIMARY KEY(objectID,propName)
);

CREATE TABLE onetSynchronize (
    key_id NVARCHAR (255) NOT NULL,
	keyName NVARCHAR (2000) NOT NULL ,
	keyValue NTEXT NOT NULL ,
	fromIP NVARCHAR (50) NOT NULL ,
	creationTime BIGINT NOT NULL ,
	CONSTRAINT onet_pk_sy PRIMARY KEY(key_id)
);

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

CREATE TABLE onetAPerm(
    objectID BIGINT NOT NULL,
    objectType INTEGER NOT NULL,
    userID INTEGER NOT NULL,
    userType INTEGER NOT NULL,
    keyName NVARCHAR (150) NOT NULL,
    keyValue NVARCHAR (255) NOT NULL,
    CONSTRAINT onet_pk_aper PRIMARY KEY(objectID,objectType,userID,userType,keyName)
);

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);
ALTER TABLE onetZoneProp ADD CONSTRAINT onet_fk_zp_zo FOREIGN KEY(objectID) REFERENCES onetZone(key_id);
ALTER TABLE onetForum ADD CONSTRAINT onet_fk_fo_zo FOREIGN KEY(zoneID) REFERENCES onetZone (key_id);
ALTER TABLE onetForumProp ADD CONSTRAINT onet_fk_fopr_fo FOREIGN KEY(objectID) REFERENCES onetForum (key_id);
ALTER TABLE onetForumThread ADD	CONSTRAINT onet_fk_foth_fo FOREIGN KEY(forumID) REFERENCES onetForum (key_id);
ALTER TABLE onetForumThreadProp ADD CONSTRAINT onet_fk_fotp_ft FOREIGN KEY(objectID) REFERENCES onetForumThread (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);
ALTER TABLE onetForumThreadExc ADD CONSTRAINT onet_fk_fte_ft FOREIGN KEY(key_id) REFERENCES onetForumThread (key_id);
ALTER TABLE onetForumThreadTop ADD CONSTRAINT onet_fk_fott_ft FOREIGN KEY(key_id) REFERENCES onetForumThread (key_id);
ALTER TABLE onetAttachData ADD	CONSTRAINT onet_fk_atda_at FOREIGN KEY(attachID) REFERENCES onetAttachment (key_id);
ALTER TABLE onetForumCategory ADD CONSTRAINT onet_fk_fc_fo FOREIGN KEY (forumID) REFERENCES onetforum(key_id);
ALTER TABLE onetForumAuthorized ADD CONSTRAINT onet_fk_fau_fo FOREIGN KEY (forumID) REFERENCES onetforum(key_id);
ALTER TABLE onetPoll ADD CONSTRAINT onet_fk_po_ft FOREIGN KEY(key_id) REFERENCES onetForumThread (key_id);
ALTER TABLE onetPollItem ADD CONSTRAINT onet_fk_poit_po FOREIGN KEY(pollID) REFERENCES onetPoll (key_id);
ALTER TABLE onetSkin ADD CONSTRAINT onet_fk_sk_lay FOREIGN KEY(layoutID) REFERENCES onetLayout(key_id);
ALTER TABLE onetStyle ADD CONSTRAINT onet_fk_st_sk FOREIGN KEY(skinID) REFERENCES onetSkin(key_id);
ALTER TABLE onetTradeProp ADD CONSTRAINT onet_fk_tp_tr FOREIGN KEY(objectID) REFERENCES onetTrade (key_id);

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);