jive_forums_oracle_upgrade_4_0_to_4_1.sql

Jive Forums 论坛源码 v4.2.3

/*
 * $RCSfile$
 * $Revision: 14870 $
 * $Date: 2005-04-05 00:33:39 -0700 (Tue, 05 Apr 2005) $
 *
 * Copyright (C) 1999-2005 Jive Software. All rights reserved. This software is the proprietary
 * information of Jive Software. Use is subject to license terms.
 */

/*
 * This script upgrades the JF 4.0 Oracle schema to the JF 4.1 schema.
 * For more information, please view the README.txt file in this directory.
 */

-- Create new tables

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

CREATE TABLE jiveStatusLevelProp (
  statusLevelID     INTEGER          NOT NULL,
  name              VARCHAR2(100)    NOT NULL,
  propValue         VARCHAR2(4000)   NOT NULL,
  CONSTRAINT jiveStatusLevelProp_pk PRIMARY KEY (statusLevelID,name)
);

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

CREATE TABLE jiveAvatarProp (
	avatarID	INTEGER		NOT NULL,
	name		VARCHAR2(100)	NOT NULL,
	propValue	VARCHAR2(4000)	NOT NULL,
	CONSTRAINT	jiveAvatarProp_pk PRIMARY KEY (avatarID, name)
);

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

CREATE TABLE jiveQuestion (
    threadID        INTEGER NOT NULL,
    forumID         INTEGER NOT NULL,
    userID          INTEGER NOT NULL,
    creationDate    INTEGER NOT NULL,
    resolutionDate  INTEGER 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        INTEGER             NOT NULL,
    name            VARCHAR2(100)       NOT NULL,
    propValue       VARCHAR2(4000)      NOT NULL,
    CONSTRAINT jiveQuestionProp_pk PRIMARY KEY (threadID, name)
);

CREATE TABLE jiveAnswer (
    threadID       INTEGER NOT NULL,
    messageID      INTEGER NOT NULL,
    forumID        INTEGER NOT NULL,
    userID         INTEGER 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 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 jiveQuestion_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE jiveQuestionProp ADD CONSTRAINT jiveQuestionProp_threadID_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);