jive_forums_oracle_upgrade_3_2_to_4_0.sql

Jive Forums 论坛源码 v4.2.3

/*
 * $RCSfile$
 * $Revision: 16342 $
 * $Date: 2005-06-10 13:19:18 -0700 (Fri, 10 Jun 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 3.2 Oracle schema to the JF 4.0 schema.
 * For more information, please view the README.txt file in this directory.
 */

-- Create new tables

CREATE TABLE jiveAnnounce (
  announcementID        INTEGER         NOT NULL,
  objectType            INTEGER         NOT NULL,
  objectID              INTEGER,
  userID                INTEGER         NOT NULL,
  subject               VARCHAR2(255)   NOT NULL,
  body                  LONG            NOT NULL,
  startDate             CHAR(15)        NOT NULL,
  endDate               CHAR(15),
  CONSTRAINT jiveAnnounce_pk PRIMARY KEY (announcementID)
);
CREATE INDEX jiveAnnounce_sDate_idx ON jiveAnnounce (startDate);
CREATE INDEX jiveAnnounce_eDate_idx ON jiveAnnounce (endDate);
CREATE INDEX jiveAnnounce_container_idx ON jiveAnnounce (objectType, objectID);
CREATE INDEX jiveAnnounce_user_idx ON jiveAnnounce (userID);


CREATE TABLE jiveAnnounceProp (
  announcementID        INTEGER         NOT NULL,
  name                  VARCHAR2(100)   NOT NULL,
  propValue             VARCHAR2(4000)  NOT NULL,
  CONSTRAINT jiveAnnounceProp_pk PRIMARY KEY (announcementID, name)
);


CREATE TABLE jivePMessage (
  pMessageID            INTEGER NOT NULL,
  ownerID               INTEGER NOT NULL,
  senderID              INTEGER NULL,
  recipientID           INTEGER NULL,
  subject               VARCHAR2(255),
  body                  LONG NOT NULL,
  readStatus            INTEGER NOT NULL,
  folderID              INTEGER NOT NULL,
  pMessageDate          CHAR(15) NOT NULL,
  CONSTRAINT jivePMessage_pmID_pk PRIMARY KEY (pMessageID)
);
CREATE INDEX jivePMessage_rID_idx ON jivePMessage (recipientID);


CREATE TABLE jivePMessageProp (
  pMessageID   INTEGER NOT NULL,
  name         VARCHAR2(100) NOT NULL,
  propValue    LONG NOT NULL,
  CONSTRAINT jivePMP_pmID_name_idx PRIMARY KEY (pMessageID,name)
);


CREATE TABLE jivePMessageFldr (
  folderID        INTEGER NOT NULL,
  userID          INTEGER NOT NULL,
  name            VARCHAR2(255) NOT NULL,
  CONSTRAINT jivePF_pk PRIMARY KEY (folderID,userID)
);


CREATE TABLE jiveBatchWatch (
  userID                INTEGER         NOT NULL,
  frequency             VARCHAR2(50)    NOT NULL,
  prevEmailDate         CHAR(15)        NULL,
  CONSTRAINT jiveBatchWatch_pk PRIMARY KEY (userID)
);


CREATE TABLE jiveRatingType (
  score                 INTEGER         NOT NULL,
  description           VARCHAR2(255)   NOT NULL,
  CONSTRAINT jiveRatingType_pk PRIMARY KEY (score)
);


CREATE TABLE jiveRating (
  objectType            INTEGER         NOT NULL,
  objectID              INTEGER         NOT NULL,  
  userID                INTEGER         NULL,
  score                 INTEGER         NOT NULL
);
CREATE INDEX jiveRating_userID_idx ON jiveRating (userID);
CREATE INDEX jiveRating_oID_oType_idx ON jiveRating (objectID, objectType);


CREATE TABLE jivePoll (
  pollID                INTEGER         NOT NULL,
  objectType            INTEGER         NOT NULL,
  objectID              INTEGER         NOT NULL,
  userID                INTEGER         NULL,
  name                  VARCHAR2(255)   NOT NULL,
  description           VARCHAR2(4000)  NULL,
  pollMode              INTEGER         NOT NULL,
  creationDate          CHAR(15)        NOT NULL,
  modificationDate      CHAR(15)        NOT NULL,
  startDate             CHAR(15)        NOT NULL,
  endDate               CHAR(15)        NOT NULL,
  expireDate            CHAR(15)        NOT NULL,
  CONSTRAINT jivePoll_pk PRIMARY KEY (pollID)
);
CREATE INDEX jivePoll_oID_oType_idx ON jivePoll (objectID, objectType);
CREATE INDEX jivePoll_cDate_idx ON jivePoll (creationDate ASC);
CREATE INDEX jivePoll_mDate_idx ON jivePoll (modificationDate DESC);
CREATE INDEX jivePoll_sDate_idx ON jivePoll (startDate DESC);
CREATE INDEX jivePoll_eDate_idx ON jivePoll (endDate DESC);


CREATE TABLE jivePollOption (
  pollID                INTEGER         NOT NULL,
  optionIndex           INTEGER         NOT NULL,
  optionText            VARCHAR2(255)   NOT NULL,
  CONSTRAINT jivePollOption_pk PRIMARY KEY (pollID, optionIndex)
);


CREATE TABLE jivePollVote (
  pollID                INTEGER         NOT NULL,
  userID                INTEGER         NULL,
  guestID               VARCHAR2(255)   NULL,
  optionIndex           INTEGER         NOT NULL,
  voteDate              CHAR(15)        NOT NULL
);
CREATE INDEX jivePollVote_pollID_idx ON jivePollVote (pollID);


CREATE TABLE jiveSearch (
  searchID              INTEGER         NOT NULL,
  searchType            INTEGER         NOT NULL,
  userID                INTEGER         NULL,
  query                 VARCHAR2(1000)  NOT NULL,
  searchDuration        INTEGER         NOT NULL,
  numResults            INTEGER         NOT NULL,
  searchDate            CHAR(15)        NOT NULL,
  CONSTRAINT jiveSearch_pk PRIMARY KEY (searchID)
);
CREATE INDEX jiveSearch_userID_idx ON jiveSearch (userID);
CREATE INDEX jiveSearch_type_idx ON jiveSearch (searchType);


CREATE TABLE jiveSearchCriteria (
  searchID              INTEGER         NOT NULL,
  criteriaName          VARCHAR2(100)   NOT NULL,
  criteriaValue         VARCHAR2(4000)  NOT NULL
);
CREATE INDEX jiveSearchCrit_sID_idx ON jiveSearchCriteria (searchID);


CREATE TABLE jiveSearchClick (
  searchID              INTEGER         NOT NULL,
  messageID             INTEGER         NOT NULL,
  clickDate             CHAR(15)        NOT NULL,
  CONSTRAINT jiveSearchClick_pk PRIMARY KEY (searchID, messageID, clickDate)
);


CREATE TABLE jiveAttachData (
  attachmentID       INTEGER  NOT NULL,
  attachmentData     BLOB     NOT NULL,
  CONSTRAINT jiveAttachData_pk PRIMARY KEY (attachmentID)
);


CREATE TABLE jiveViewCount (
    objectType      INTEGER    NOT NULL,
    objectID        INTEGER    NOT NULL,
    parentObjectID  INTEGER    NULL,
    viewCount       INTEGER    DEFAULT 0,
    CONSTRAINT jiveViewCount_pk PRIMARY KEY (objectType, objectID)
);
CREATE INDEX jiveViewCount_vc_idx ON jiveViewCount (viewCount);
CREATE INDEX jiveViewCount_po_idx ON jiveViewCount (parentObjectID);


CREATE TABLE jiveReadStat (
    readStatID      INTEGER NOT NULL,
    userID          INTEGER NULL,
    objectType      INTEGER NOT NULL,
    objectID        INTEGER NOT NULL,
    creationDate    CHAR(15) NOT NULL,
    sessionID       INTEGER NOT NULL,
    CONSTRAINT jiveReadStat_pk PRIMARY KEY (readStatID)
);
CREATE INDEX jiveReadStat_cd_idx ON jiveReadStat (creationDate);


CREATE TABLE jiveReadStatSession (
    sessionID       INTEGER NOT NULL,
    visitorID       VARCHAR2(32) NULL,
    creationDate    CHAR(15) NOT NULL,
    CONSTRAINT jiveRSS_pk PRIMARY KEY (sessionID)
);
CREATE INDEX jiveRSS_cd_idx ON jiveReadStatSession (creationDate);


CREATE TABLE jiveHTTPReadStatSession (
    referrer        VARCHAR2(255) NULL,
    userAgent       VARCHAR2(255) NULL,
    IP              VARCHAR2(16) NULL,
    country         VARCHAR2(4) NULL,
    bytesSent       INTEGER DEFAULT 0,
    sessionID       INTEGER NOT NULL
);
CREATE INDEX jiveHRSS_sID_idx ON jiveHTTPReadStatSession (sessionID);
CREATE INDEX jiveHRSS_bs_idx ON jiveHTTPReadStatSession (bytesSent);
ALTER TABLE jiveHTTPReadStatSession ADD CONSTRAINT jiveHTTPReadStatSession_sID_fk FOREIGN KEY (sessionID) REFERENCES jiveReadStatSession INITIALLY DEFERRED DEFERRABLE;


CREATE TABLE jiveNNTPReadStatSession (
    creationDate    CHAR(15) NOT NULL,
    endDate         CHAR(15) NULL,
    bytesReceived   INTEGER DEFAULT 0,
    bytesSent       INTEGER DEFAULT 0,
    IP              VARCHAR2(16) NULL,
    country         VARCHAR2(4) NULL,
    sessionID       INTEGER NOT NULL
);
CREATE INDEX jiveNRSS_cd_idx ON jiveNNTPReadStatSession (creationDate);
CREATE INDEX jiveNRSS_ed_idx ON jiveNNTPReadStatSession (endDate);
ALTER TABLE jiveNNTPReadStatSession ADD CONSTRAINT jiveNNTPReadStatSession_sID_fk FOREIGN KEY (sessionID) REFERENCES jiveReadStatSession INITIALLY DEFERRED DEFERRABLE;


CREATE TABLE jiveProperty (
    name        VARCHAR2(100) NOT NULL,
    propValue   VARCHAR2(4000) NOT NULL,
    CONSTRAINT jiveProperty_pk PRIMARY KEY (name)
);


-- Add constraints

ALTER TABLE jiveRating ADD CONSTRAINT jiveRating_score_fk FOREIGN KEY (score) REFERENCES jiveRatingType INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jivePollOption ADD CONSTRAINT jivePollOption_pollID_fk FOREIGN KEY (pollID) REFERENCES jivePoll INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jivePollVote ADD CONSTRAINT jivePollVote_pollID_fk FOREIGN KEY (pollID) REFERENCES jivePoll INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveSearchCriteria ADD CONSTRAINT jiveSearchCriteria_sID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveSearchClick ADD CONSTRAINT jiveSearchClick_searchID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveAttachData ADD CONSTRAINT jiveAttachData_aID_fk FOREIGN KEY (attachmentID) REFERENCES jiveAttachment INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE jiveAttachmentProp ADD CONSTRAINT jiveAttachmentProp_aID_fk FOREIGN KEY (attachmentID) REFERENCES jiveAttachment INITIALLY DEFERRED DEFERRABLE; 

-- Add new types

INSERT INTO jiveID (idType, id) VALUES (18, 1);
INSERT INTO jiveID (idType, id) VALUES (19, 1);
INSERT INTO jiveID (idType, id) VALUES (20, 1);
INSERT INTO jiveID (idType, id) VALUES (22, 1);
INSERT INTO jiveID (idType, id) VALUES (23, 1);
INSERT INTO jiveID (idType, id) VALUES (24, 1);
INSERT INTO jiveID (idType, id) VALUES (201, 1);
INSERT INTO jiveID (idType, id) VALUES (202, 1);

-- Add in the new permissionType column to the jiveUserPerm table
ALTER TABLE jiveUserPerm ADD permissionType INTEGER;
UPDATE jiveUserPerm SET permissionType = '1';
ALTER TABLE jiveUserPerm MODIFY permissionType INTEGER NOT NULL;

-- Add in the new permissionType column to the jiveGroupPerm table
ALTER TABLE jiveGroupPerm ADD permissionType INTEGER;
UPDATE jiveGroupPerm SET permissionType = '1';
ALTER TABLE jiveGroupPerm MODIFY permissionType INTEGER NOT NULL;

-- Add forumIndex column to the jiveMessage table
ALTER TABLE jiveMessage ADD forumIndex INTEGER;
UPDATE jiveMessage SET forumIndex = '0';
ALTER TABLE jiveMessage MODIFY forumIndex INTEGER NOT NULL;

-- Drop columns from jiveForum that are no longer used.
ALTER TABLE jiveForum DROP COLUMN modMinThreadVal;
ALTER TABLE jiveForum DROP COLUMN modMinMsgVal;

-- Update index
DROP INDEX jiveMessage_forumID_idx;
CREATE INDEX jiveMessage_forum_idx ON jiveMessage(forumID ASC, forumIndex);

-- Add nntpName column to jiveForum - this is a multi-step process since we need to
-- add the column then set unique values in it then add a unique non-null contraint.
ALTER TABLE jiveForum ADD nntpName VARCHAR2(255) NULL;
UPDATE jiveForum SET nntpName = forumID;
CREATE UNIQUE INDEX nntpUniqueIdx ON jiveForum (nntpName);
ALTER TABLE jiveForum MODIFY (nntpName CONSTRAINT nn_nntpName NOT NULL);

-- Add forumIndexCounter column to the jiveForum table
ALTER TABLE jiveForum ADD forumIndexCounter INTEGER;
UPDATE jiveForum SET forumIndexCounter = '0';
ALTER TABLE jiveForum MODIFY forumIndexCounter INTEGER NOT NULL;
CREATE INDEX jiveForum_forumIdxCtr_idx ON jiveForum (forumIndexCounter);

-- Add index to the jiveWatch table
CREATE INDEX jiveWatch_combo_idx ON jiveWatch (objectType, objectID, watchType);

-- Rename colunmns in the jiveAttachment table
CREATE TABLE jiveAttachmentTemp (
    attachmentID,
    objectID,
    CONSTRAINT jiveAttachmentTemp_pk PRIMARY KEY (attachmentID)
)
AS SELECT attachmentID, messageID FROM jiveAttachment;
ALTER TABLE jiveAttachment DROP CONSTRAINT jiveAttachment_msgID_fk;
ALTER TABLE jiveAttachment ADD objectID INTEGER NULL;

UPDATE jiveAttachment a SET a.objectID = (SELECT t.objectID FROM jiveAttachmentTemp t WHERE a.attachmentID=t.attachmentID);
DROP INDEX jiveAttachment_messageID_idx;
ALTER TABLE jiveAttachment DROP COLUMN messageID;
ALTER TABLE jiveAttachment ADD objectType INTEGER NULL;
UPDATE jiveAttachment SET objectType = 2;

ALTER TABLE jiveAttachment MODIFY objectType INTEGER NOT NULL;
CREATE INDEX jiveAttachment_object_idx ON jiveAttachment (objectType, objectID);
DROP TABLE jiveAttachmentTemp;

COMMIT;

-- Upgrade some property names

UPDATE jiveThreadProp set name='jive.locked' where name='locked';
UPDATE jiveThreadProp set name='jive.archived' where name='archived';


COMMIT;