jive_forums_mysql_upgrade_3_2_to_4_0.sql

Jive Forums 论坛源码 v4.2.3

#
# Jive Forums Upgrade Script - Jive Forums 3.2 -> 4.0
#
# $RCSFile: $
# $Revision: 16342 $
# $Date: 2005-06-10 13:19:18 -0700 (Fri, 10 Jun 2005) $


#
# NEW TABLES
#

CREATE TABLE jiveAnnounce (
  announcementID        BIGINT          NOT NULL,
  objectType            INT             NOT NULL,
  objectID              BIGINT          NULL,
  userID                BIGINT          NOT NULL,
  subject               VARCHAR(255)    NULL,
  body                  TEXT            NULL,
  startDate             CHAR(15)        NOT NULL,
  endDate               CHAR(15)        NULL,
  PRIMARY KEY (announcementID),
  INDEX jiveAnnounce_sDate_idx     (startDate),
  INDEX jiveAnnounce_eDate_idx     (endDate),
  INDEX jiveAnnounce_container_idx (objectType, objectID),
  INDEX jiveAnnounce_user_idx      (userID)
);

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

CREATE TABLE jivePMessage (
  pMessageID            BIGINT          NOT NULL,
  ownerID               BIGINT          NOT NULL,
  senderID              BIGINT          NULL,
  recipientID           BIGINT          NULL,
  subject               VARCHAR(255)    NULL,
  body                  TEXT            NULL,
  readStatus            INT             NOT NULL,
  folderID              INT             NOT NULL,
  pMessageDate          CHAR(15)        NOT NULL,
  PRIMARY KEY           (pMessageID),
  INDEX jivePMessage_recipient_idx  (recipientID)
);

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

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

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

CREATE TABLE jiveRatingType (
  score                 INT             NOT NULL,
  description           VARCHAR(255)    NOT NULL,
  PRIMARY KEY (score)
);


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


CREATE TABLE jivePoll (
  pollID                BIGINT          NOT NULL,
  objectType            INT             NOT NULL,
  objectID              BIGINT          NOT NULL,
  userID                BIGINT          NULL,
  name                  VARCHAR(255)    NOT NULL,
  description           TEXT            NULL,
  pollMode              INT             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,
  PRIMARY KEY (pollID),
  INDEX jivePoll_oID_oType_idx (objectID, objectType),
  INDEX jivePoll_cDate_idx     (creationDate),
  INDEX jivePoll_mDate_idx     (modificationDate),
  INDEX jivePoll_sDate_idx     (startDate),
  INDEX jivePoll_eDate_idx     (endDate)
);


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


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

CREATE TABLE jiveSearch (
  searchID              BIGINT          NOT NULL,
  searchType            INT             NOT NULL,
  userID                BIGINT          NULL,
  query                 TEXT            NOT NULL,
  searchDuration        INT             NOT NULL,
  numResults            INT             NOT NULL,
  searchDate            CHAR(15)        NOT NULL,
  PRIMARY KEY (searchID),
  INDEX jiveSearch_userID_idx (userID),
  INDEX jiveSearch_type_idx (searchType)
);


CREATE TABLE jiveSearchCriteria (
  searchID              BIGINT          NOT NULL,
  criteriaName          VARCHAR(100)    NOT NULL,
  criteriaValue         TEXT            NOT NULL,
  INDEX jiveSearchCrit_sID_idx (searchID)
);


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


CREATE TABLE jiveViewCount (
    objectType      INT NOT NULL,
    objectID        BIGINT NOT NULL,
    parentObjectID  BIGINT NULL,
    viewCount       INT DEFAULT 0,
    PRIMARY KEY     (objectType, objectID),
    INDEX jiveViewCount_vc_idx (viewCount),
    INDEX jiveViewCount_poID (parentObjectID)
);


CREATE TABLE jiveReadStat (
    readStatID      BIGINT NOT NULL,
    userID          BIGINT NULL,
    objectType      INT NOT NULL,
    objectID        BIGINT NOT NULL,
    creationDate    CHAR(15) NOT NULL,
    sessionID       BIGINT NOT NULL,
    PRIMARY KEY (readStatID),
    INDEX jiveReadStat_cd_idx (creationDate)
);


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


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


CREATE TABLE jiveNNTPReadStatSession (
    creationDate    DATETIME NOT NULL,
    endDate         DATETIME NULL,
    bytesReceived   BIGINT DEFAULT 0,
    bytesSent       BIGINT DEFAULT 0,
    IP              VARCHAR(16) NULL,
    country         VARCHAR(4) NULL,
    sessionID       BIGINT NOT NULL,
    INDEX jiveNNTPRSS_sID_idx (sessionID),
    INDEX jiveNNTPRSS_cd_idx (creationDate),
    INDEX jiveNNTPRSS_ed_idx (endDate)
);

CREATE TABLE jiveProperty (
    name        VARCHAR(100) NOT NULL,
    propValue   TEXT NOT NULL,
    PRIMARY KEY (name)
);

CREATE TABLE jiveAttachData (
  attachmentID       BIGINT   NOT NULL,
  attachmentData     LONGBLOB NOT NULL,
  PRIMARY KEY (attachmentID)
);

# 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 COLUMN permissionType INTEGER AFTER userID;
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 COLUMN permissionType INTEGER AFTER groupID;
UPDATE jiveGroupPerm SET permissionType = '1';
ALTER TABLE jiveGroupPerm MODIFY permissionType INTEGER NOT NULL;

# Add forumIndex column to the jiveMessage table
ALTER TABLE jiveMessage ADD COLUMN forumIndex INT NOT NULL DEFAULT 0;
ALTER TABLE jiveMessage DROP INDEX jiveMessage_forumID_idx;
CREATE INDEX jiveMessage_forum_idx ON jiveMessage(forumID, forumIndex);

# Add NNTP support to the jiveForum table.
ALTER TABLE jiveForum ADD COLUMN forumIndexCounter INT NOT NULL DEFAULT 0;
CREATE INDEX jiveForum_forumIndexCounter_idx ON jiveForum(forumIndexCounter);
ALTER TABLE jiveForum ADD COLUMN nntpName VARCHAR(255) NULL;
UPDATE jiveForum SET nntpName = forumID;
ALTER TABLE jiveForum MODIFY COLUMN nntpName VARCHAR(255) UNIQUE NOT NULL;
CREATE INDEX jiveForum_nntpName_idx ON jiveForum(nntpName);

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

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

# Update attachment table, add table for attachment data.
ALTER TABLE jiveAttachment DROP INDEX jiveAttachment_messageID_idx;
ALTER TABLE jiveAttachment CHANGE messageID objectID BIGINT NULL;
ALTER TABLE jiveAttachment ADD COLUMN objectType INT NULL;
UPDATE jiveAttachment SET objectType=2;
ALTER TABLE jiveAttachment MODIFY COLUMN objectType INT NOT NULL;
CREATE INDEX jiveAttachment_object_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';