jive_forums_db2_upgrade_4_0_to_4_1.sql

Jive Forums 论坛源码 v4.2.3

-- //
-- // $RCSfile$
-- // $Revision: 14353 $
-- // $Date: 2005-02-14 08:29:19 -0800 (Mon, 14 Feb 2005) $
-- //

-- //
-- // Jive Forums 4.0->4.1 DB2 upgrade script (DB2v7 or better)
-- //

-- // Create new tables

CREATE TABLE jiveStatusLevel (
	statusLevelID	BIGINT		    NOT NULL,
	name			VARCHAR(255)	UNIQUE NOT NULL,
	description		VARCHAR(2000),
	imagePath		VARCHAR(255)	NOT NULL,
    largeImagePath  VARCHAR(255),
	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              VARCHAR(100)    NOT NULL,
  propValue         VARCHAR(2000)   NOT NULL,
  CONSTRAINT jiveStsLvlProp_pk PRIMARY KEY (statusLevelID,name)
);

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

CREATE TABLE jiveAvatarProp (
	avatarID	    BIGINT		    NOT NULL,
	name		    VARCHAR(100)	NOT NULL,
	propValue	    VARCHAR(2000)	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,
	resolutionState	INTEGER         NOT NULL,
	CONSTRAINT jiveQuestion_pk PRIMARY KEY (threadID)
);
CREATE INDEX jiveQuestion_cdate_idx (creationDate);
CREATE INDEX jiveQuestion_rdate_idx (resolutionDate);
CREATE INDEX jiveQuestion_state_idx (resolutionState);

CREATE TABLE jiveQuestionProp (
	threadID	    BIGINT		    NOT NULL,
	name		    VARCHAR(100)	NOT NULL,
	propValue	    VARCHAR(2000)   NOT NULL,
	CONSTRAINT jiveQuestionP_pk PRIMARY KEY (threadID, name)
);

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

ALTER TABLE jiveStatusLevelProp ADD CONSTRAINT jiveFrmPrp_sID_fk FOREIGN KEY (statusLevelID) REFERENCES jiveStatusLevel;
ALTER TABLE jiveAvatarProp ADD CONSTRAINT jiveAtrPrp_ID_fk FOREIGN KEY (avatarID) REFERENCES jiveAvatar;
ALTER TABLE jiveAvatarUser ADD CONSTRAINT jiveAtrUsr_ID_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 ID types


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