jive_forums_postgresql_upgrade_4_0_to_4_1.sql

Jive Forums 论坛源码 v4.2.3

-- //////////////////////////////
-- // $RCSfile$
-- // $Revision: 16342 $
-- // $Date: 2005-06-10 13:19:18 -0700 (Fri, 10 Jun 2005) $
-- //////////////////////////////

-- Add new tables

CREATE TABLE jiveStatusLevel (
	statusLevelID	BIGINT		    NOT NULL,
	name			VARCHAR(255)	UNIQUE NOT NULL,
	description		VARCHAR(4000) 	NULL,
	imagePath		VARCHAR(255)	NOT NULL,
    largeImagePath  VARCHAR(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              VARCHAR(100)    NOT NULL,
  propValue         VARCHAR(4000)   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		VARCHAR(100)	NOT NULL,
	propValue	VARCHAR(4000)	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	INT 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		VARCHAR(100)	    NOT NULL,
	propValue	TEXT            	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  INT NOT NULL,
	correctAnswer  INT NOT NULL,
	CONSTRAINT jiveAnswer_pk PRIMARY KEY (threadID, messageID)
);

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

-- Add new type ID's

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