jive_forums_db2_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) $
-- //

-- //
-- // Jive Forums DB2 upgrade script for DB2 v7 or better
-- //

-- // Create new tables

CREATE TABLE jiveAnnounce (
  announcementID        BIGINT          NOT NULL,
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT,
  userID                BIGINT          NOT NULL,
  subject               VARCHAR(255)    NOT NULL,
  body                  LONG VARCHAR    NOT NULL,
  startDate             CHAR(15)        NOT NULL,
  endDate               CHAR(15),
  CONSTRAINT jiveAn_pk PRIMARY KEY (announcementID)
);
CREATE INDEX jiveAn_sDt_idx ON jiveAnnounce (startDate);
CREATE INDEX jiveAn_eDt_idx ON jiveAnnounce (endDate);
CREATE INDEX jiveAn_ctr_idx ON jiveAnnounce (objectType, objectID);
CREATE INDEX jiveAn_usr_idx ON jiveAnnounce (userID);


CREATE TABLE jiveAnnounceProp (
  announcementID        BIGINT          NOT NULL,
  name                  VARCHAR(100)    NOT NULL,
  propValue             VARCHAR(2000)   NOT NULL,
  CONSTRAINT jiveAnProp_pk PRIMARY KEY (announcementID, name)
);


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


CREATE TABLE jivePMessageProp (
  pMessageID   BIGINT NOT NULL,
  name         VARCHAR(100) NOT NULL,
  propValue    LONG VARCHAR NOT NULL,
  CONSTRAINT jivePMsgProp_pk PRIMARY KEY  (pMessageID,name)
);


CREATE TABLE jivePMessageFldr (
  folderID        INTEGER NOT NULL,
  userID          BIGINT NOT NULL,
  name            VARCHAR(255),
  CONSTRAINT jivePMsg_f_pk PRIMARY KEY (folderID, userID)
);


CREATE TABLE jiveBatchWatch (
  userID                BIGINT          NOT NULL,
  frequency             VARCHAR(50)     NOT NULL,
  prevEmailDate         CHAR(15),
  CONSTRAINT jiveBWtch_pk PRIMARY KEY (userID)
);


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

CREATE TABLE jiveRating (
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT          NOT NULL,  
  userID                BIGINT,
  score                 INTEGER         NOT NULL
);
CREATE INDEX jiveRtg_uID_idx ON jiveRating (userID);
CREATE INDEX jiveRtg_oID_idx ON jiveRating (objectID, objectType);


CREATE TABLE jivePoll (
  pollID                BIGINT          NOT NULL,
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT          NOT NULL,
  userID                BIGINT          NOT NULL,
  name                  VARCHAR(255)    NOT NULL,
  description           VARCHAR(2000),
  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 jPoll_pk PRIMARY KEY (pollID)
);
CREATE INDEX jPoll_oID_idx ON jivePoll (objectID, objectType);
CREATE INDEX jPoll_cDate_idx ON jivePoll (creationDate);
CREATE INDEX jPoll_mDate_idx ON jivePoll (modificationDate);
CREATE INDEX jPoll_sDate_idx ON jivePoll (startDate);
CREATE INDEX jPoll_eDate_idx ON jivePoll (endDate);

CREATE TABLE jivePollOption (
  pollID                BIGINT          NOT NULL,
  optionIndex           INTEGER         NOT NULL,
  optionText            VARCHAR(255)    NOT NULL,
  CONSTRAINT jPollOpt_pk PRIMARY KEY (pollID, optionIndex)
);


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


CREATE TABLE jiveSearch (
  searchID              BIGINT          NOT NULL,
  searchType            INTEGER         NOT NULL,
  userID                BIGINT,
  query                 VARCHAR(1000)   NOT NULL,
  searchDuration        INTEGER         NOT NULL,
  numResults            INTEGER         NOT NULL,
  searchDate            CHAR(15)        NOT NULL,
  CONSTRAINT jiveSearch_pk PRIMARY KEY (searchID)
);
CREATE INDEX jiveSrch_uID_idx ON jiveSearch (userID);
CREATE INDEX jiveSrch_type_idx ON jiveSearch (searchType);


CREATE TABLE jiveSearchCriteria (
  searchID              BIGINT          NOT NULL,
  criteriaName          VARCHAR(100)    NOT NULL,
  criteriaValue         VARCHAR(2000)   NOT NULL
);
CREATE INDEX jiveSrchCr_sID_idx ON jiveSearchCriteria (searchID);


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


CREATE TABLE jiveAttachData (
  attachmentID          BIGINT          NOT NULL,
  attachmentData        BLOB (1000K)    NOT NULL,
  CONSTRAINT jiveAttchDta_pk PRIMARY KEY (attachmentID)
);


CREATE TABLE jiveViewCount (
    objectType      INTEGER   NOT NULL,
    objectID        BIGINT    NOT NULL,
    parentObjectID  BIGINT,
    viewCount       INTEGER   DEFAULT 0,
    CONSTRAINT jiveVCount_pk PRIMARY KEY (objectType, objectID)
);
CREATE INDEX jiveVCnt_vc_idx ON jiveViewCount (viewCount);
CREATE INDEX jiveVCnt_po_idx ON jiveViewCount (parentObjectID);


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


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


CREATE TABLE jiveHTTPReadStatSession (
    referrer     VARCHAR(255),
    userAgent    VARCHAR(255),
    IP           VARCHAR(16),
    country      VARCHAR(4),
    bytesSent    BIGINT DEFAULT 0,
    sessionID    BIGINT NOT NULL
);
CREATE INDEX jiveHRSS_ID_idx ON jiveHTTPReadStatSession (sessionID);
CREATE INDEX jiveHRSS_bs_idx ON jiveHTTPReadStatSession (bytesSent);


CREATE TABLE jiveNNTPReadStatSession (
    creationDate    CHAR(15) NOT NULL,
    endDate         CHAR(15),
    bytesReceived   BIGINT DEFAULT 0,
    bytesSent       BIGINT DEFAULT 0,
    IP              VARCHAR(16),
    country         VARCHAR(4),
    sessionID       BIGINT NOT NULL
);
CREATE INDEX jiveNRSS_ID_idx ON jiveNNTPReadStatSession (sessionID);
CREATE INDEX jiveNRSS_cd_idx ON jiveNNTPReadStatSession (creationDate);
CREATE INDEX jiveNRSS_ed_idx ON jiveNNTPReadStatSession (endDate);


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


-- // Add constraints

ALTER TABLE jiveRating ADD CONSTRAINT jiveRtg_score_fk FOREIGN KEY (score) REFERENCES jiveRatingType;
ALTER TABLE jivePollOption ADD CONSTRAINT jivePllOpt_pID_fk FOREIGN KEY (pollID) REFERENCES jivePoll;
ALTER TABLE jivePollVote ADD CONSTRAINT jivePllVte_pID_fk FOREIGN KEY (pollID) REFERENCES jivePoll;
ALTER TABLE jiveSearchCriteria ADD CONSTRAINT jiveSrchCr_sID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch;
ALTER TABLE jiveSearchClick ADD CONSTRAINT jiveSrchCl_sID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch;
ALTER TABLE jiveAttachData ADD CONSTRAINT jAttData_aID_fk FOREIGN KEY (attachmentID) REFERENCES jiveAttachment;
ALTER TABLE jiveAttachmentProp ADD CONSTRAINT jAttPrp_aID_fk FOREIGN KEY (attachmentID) REFERENCES jiveAttachment; 

-- // 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 NOT NULL WITH DEFAULT 1;

-- // Add in the new permissionType column to the jiveGroupPerm table
ALTER TABLE jiveGroupPerm ADD permissionType INTEGER NOT NULL WITH DEFAULT 1;

-- // Add forumIndex column to the jiveMessage table
ALTER TABLE jiveMessage ADD forumIndex INTEGER NOT NULL WITH DEFAULT 0;

-- // Drop unused columns from jiveForum. Start by renaming the jiveForum table and
-- // recreate the table using the new definition.

CREATE TABLE jiveForum_temp (
  forumID              BIGINT NOT NULL,
  name                 VARCHAR(255) NOT NULL,
  description          VARCHAR(2000),
  modDefaultThreadVal  INTEGER NOT NULL,
  modDefaultMsgVal     INTEGER NOT NULL,
  creationDate         CHAR(15) NOT NULL,
  modificationDate     CHAR(15) NOT NULL,
  categoryID           BIGINT NOT NULL,
  categoryIndex        INTEGER NOT NULL,
  CONSTRAINT jiveForum_pk PRIMARY KEY (forumID)
);

INSERT INTO jiveForum_temp (
    forumID,
    name,
    description,
    modDefaultThreadVal,
    modDefaultMsgVal,
    creationDate,
    modificationDate,
    categoryID,
    categoryIndex
)
    SELECT forumID, name, description, modDefaultThreadVal, modDefaultMsgVal,
             creationDate, modificationDate, categoryID, categoryIndex FROM jiveForum;


DROP TABLE jiveForum;

RENAME TABLE jiveForum_temp TO jiveForum;

ALTER TABLE  jiveForum ADD COLUMN  nntpName VARCHAR(255) NOT NULL Default '0';

UPDATE jiveForum SET nntpName = char(forumID);

ALTER TABLE jiveForum ADD CONSTRAINT KEY_nntpName UNIQUE( nntpName );

ALTER TABLE  jiveForum ADD COLUMN  forumIndexCounter INTEGER NOT NULL DEFAULT  0;

CREATE INDEX jF_catID_idx ON jiveForum (categoryID);
CREATE INDEX jF_forumIdxCtr_idx ON jiveForum (forumIndexCounter);
CREATE INDEX jF_cDate_idx ON jiveForum (creationDate);
CREATE INDEX jF_mDate_idx ON jiveForum (modificationDate);

ALTER TABLE jiveForumProp ADD CONSTRAINT jFP_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;
ALTER TABLE jiveThread ADD CONSTRAINT jT_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;
ALTER TABLE jiveMessage ADD CONSTRAINT jM_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;


-- // Update indexes

-- // DROP INDEX jM_forum_idx;
CREATE INDEX jM_forum_idx ON jiveMessage(forumID ASC, forumIndex);

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


-- // Update the jive attachment table and its indexes

-- // Drop this index as it's replaced by a compound one now
DROP INDEX jA_msgID_idx;

ALTER TABLE jiveAttachment DROP FOREIGN KEY jA_msgID_fk;

RENAME TABLE jiveAttachment TO jiveAttachment_temp;

CREATE TABLE jiveAttachment (
  attachmentID      BIGINT NOT NULL,
  objectID          BIGINT,
  fileName          VARCHAR(255) NOT NULL,
  fileSize          INTEGER NOT NULL,
  contentType       VARCHAR(50) NOT NULL,
  creationDate      CHAR(15) NOT NULL,
  modificationDate  CHAR(15) NOT NULL,
  CONSTRAINT jA_pk PRIMARY KEY (attachmentID)
);

INSERT INTO jiveAttachment (
    attachmentID,
    objectID,
    fileName,
    fileSize,
    contentType,
    creationDate,
    modificationDate
) SELECT attachmentID, messageID, fileName, fileSize, contentType, creationDate, modificationDate
    FROM jiveAttachment_temp;

DROP TABLE jiveAttachment_temp;

ALTER TABLE jiveAttachment ADD COLUMN objectType INTEGER NOT NULL DEFAULT  2;

CREATE INDEX jA_objID_idx ON jiveAttachment (objectType, objectID);

-- // Upgrade some property names


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