jive_forums_sqlserver2000_upgrade_4_0_to_4_1.sql

Jive Forums 论坛源码 v4.2.3

/*
 * $RCSfile$
 * $Revision: 14503 $
 * $Date: 2005-03-01 20:59:52 -0800 (Tue, 01 Mar 2005) $
 */

/* Create new tables */

CREATE TABLE jiveStatusLevel (
	statusLevelID	BIGINT		    NOT NULL,
	name			NVARCHAR(255)	UNIQUE NOT NULL,
	description		NTEXT        	NULL,
	imagePath		NVARCHAR(255)	NOT NULL,
    largeImagePath  NVARCHAR(255)    NULL,
	minPoints		INTEGER		    NOT NULL,
	maxPoints		INTEGER		    NOT NULL,
	groupID			BIGINT		    NOT NULL,
	CONSTRAINT jiveStatusLevel_pk PRIMARY KEY (statusLevelID)
);

CREATE TABLE jiveStatusLevelProp (
  statusLevelID     BIGINT          NOT NULL,
  name              NVARCHAR(100)   NOT NULL,
  propValue         NVARCHAR(3900)  NOT NULL,
  CONSTRAINT jiveStatusLevelProp_pk PRIMARY KEY (statusLevelID,name)
);

CREATE TABLE jiveAvatar (
	avatarID	BIGINT		NOT NULL,
	modValue	INTEGER		NOT NULL,
	ownerID		BIGINT		NULL,
	CONSTRAINT jiveAvatar_pk PRIMARY KEY (avatarID)
);
CREATE INDEX jiveAvatar_ownerID_idx ON jiveAvatar (ownerID);

CREATE TABLE jiveAvatarProp (
	avatarID	BIGINT		    NOT NULL,
	name		NVARCHAR(100)	NOT NULL,
	propValue	NVARCHAR(3900) 	NOT NULL,
	CONSTRAINT	jiveAvatarProp_pk PRIMARY KEY (avatarID, name)
);

CREATE TABLE jiveAvatarUser (
	avatarID	BIGINT		    NOT NULL,
	userID		BIGINT		    NOT NULL,
	CONSTRAINT jiveAvatarUser_pk PRIMARY KEY (avatarID,userID)
);

CREATE TABLE jiveQuestion (
	threadID		BIGINT              NOT NULL,
	forumID			BIGINT              NOT NULL,
	userID          BIGINT              NOT NULL,
	creationDate	BIGINT              NOT NULL,
	resolutionDate	BIGINT              NULL,
	resolutionState	INTEGER             NOT NULL,
	CONSTRAINT jiveQuestion_pk PRIMARY KEY (threadID)
);
CREATE INDEX jiveQuestion_cdate_idx ON jiveQuestion (creationDate);
CREATE INDEX jiveQuestion_rdate_idx ON jiveQuestion (resolutionDate);
CREATE INDEX jiveQuestion_state_idx ON jiveQuestion (resolutionState);

CREATE TABLE jiveQuestionProp (
	threadID	    BIGINT		        NOT NULL,
	name		    NVARCHAR(100)	    NOT NULL,
	propValue	    NVARCHAR(3900)     	NOT NULL,
	CONSTRAINT jiveQuestionProp_pk PRIMARY KEY (threadID, name)
);

CREATE TABLE jiveAnswer (
	threadID	    BIGINT              NOT NULL,
	messageID       BIGINT              NOT NULL,
	forumID         BIGINT              NOT NULL,
	userID          BIGINT              NULL,
	helpfulAnswer   INTEGER             NOT NULL,
	correctAnswer   INTEGER             NOT NULL,
	CONSTRAINT jiveAnswer_pk PRIMARY KEY (threadID, messageID)
);

ALTER TABLE jiveStatusLevelProp ADD CONSTRAINT jiveForumProp_statusLevelID_fk FOREIGN KEY (statusLevelID) REFERENCES jiveStatusLevel;
ALTER TABLE jiveAvatarProp ADD CONSTRAINT jiveAvatarProp_avatarID_fk FOREIGN KEY (avatarID) REFERENCES jiveAvatar;
ALTER TABLE jiveAvatarUser ADD CONSTRAINT jiveAvatarUser_avatarID_fk FOREIGN KEY (avatarID) REFERENCES jiveAvatar;
ALTER TABLE jiveQuestion ADD CONSTRAINT jiveQuest_tID_fk FOREIGN KEY (threadID) REFERENCES jiveThread;
ALTER TABLE jiveQuestionProp ADD CONSTRAINT jiveQuestP_tID_fk FOREIGN KEY (threadID) REFERENCES jiveThread;

/* Add new type IDs */

INSERT INTO jiveID (idType, id) VALUES (25, 1);
INSERT INTO jiveID (idType, id) VALUES (26, 1);
GO