jive_forums_sqlserver2000_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) $
 */

/* Create new tables */

CREATE TABLE jiveAnnounce (
  announcementID        BIGINT          NOT NULL,
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT          NULL,
  userID                BIGINT          NOT NULL,
  subject               NVARCHAR(255)   NOT NULL,
  body                  NTEXT           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        BIGINT          NOT NULL,
  name                  NVARCHAR(100)   NOT NULL,
  propValue             NVARCHAR(3900)  NOT NULL,
  CONSTRAINT jiveAnnounceProp_pk PRIMARY KEY (announcementID, name)
);


CREATE TABLE jivePMessage (
  pMessageID            BIGINT          NOT NULL,
  ownerID               BIGINT          NOT NULL,
  senderID              BIGINT          NULL,
  recipientID           BIGINT          NULL,
  subject               NVARCHAR(255)   NULL,
  body                  NTEXT           NULL,
  readStatus            INTEGER         NOT NULL,
  folderID              BIGINT          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            BIGINT          NOT NULL,
  name                  NVARCHAR(100)   NOT NULL,
  propValue             NVARCHAR(3900)  NOT NULL,
  CONSTRAINT jivePMP_pmID_name_idx PRIMARY KEY (pMessageID, name)
);


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


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


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


CREATE TABLE jiveRating (
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT          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                BIGINT          NOT NULL,
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT          NOT NULL,
  userID                BIGINT          NOT NULL,
  name                  NVARCHAR(255)   NOT NULL,
  description           NVARCHAR(3000)  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 TABLE jivePollOption (
  pollID                BIGINT          NOT NULL,
  optionIndex           INTEGER         NOT NULL,
  optionText            NVARCHAR(255)   NOT NULL,
  CONSTRAINT jivePollOption_pk PRIMARY KEY (pollID, optionIndex)
);


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


CREATE TABLE jiveSearch (
  searchID              BIGINT          NOT NULL,
  searchType            INTEGER         NOT NULL,
  userID                BIGINT          NULL,
  query                 NTEXT           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              BIGINT          NOT NULL,
  criteriaName          NVARCHAR(100)   NOT NULL,
  criteriaValue         NVARCHAR(3900)  NOT NULL
);
CREATE INDEX jiveSearchCrit_sID_idx ON jiveSearchCriteria (searchID);


CREATE TABLE jiveSearchClick (
  searchID              BIGINT          NOT NULL,
  messageID             BIGINT          NOT NULL,
  clickDate             CHAR(15)        NOT NULL,
  CONSTRAINT jiveSearchClick_pk PRIMARY KEY (searchID, messageID, clickDate)
);
/* No foreign key on messageID since we need the messageID to stay even if the message no longer exists */

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

CREATE TABLE jiveAttachData (
  attachmentID          BIGINT          NOT NULL,
  attachmentData        IMAGE           NOT NULL,
  CONSTRAINT jiveAttachData_pk PRIMARY KEY (attachmentID)
);

CREATE TABLE jiveViewCount (
    objectType          INTEGER         NOT NULL,
    objectID            BIGINT          NOT NULL,
    parentObjectID      BIGINT          NULL,
    viewCount           INTEGER,
    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          BIGINT          NOT NULL,
    userID              BIGINT          NULL,
    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 jiveReadStat_cd_idx ON jiveReadStat (creationDate);


CREATE TABLE jiveReadStatSession (
    sessionID           BIGINT          NOT NULL,
    visitorID           VARCHAR(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            VARCHAR(255)    NULL,
    userAgent           VARCHAR(255)    NULL,
    IP                  VARCHAR(16)     NULL,
    country             VARCHAR(4)      NULL,
    bytesSent           BIGINT,
    sessionID           BIGINT          NOT NULL
);
CREATE INDEX jiveHRSS_sID_idx ON jiveHTTPReadStatSession (sessionID);
CREATE INDEX jiveHRSS_bs_idx ON jiveHTTPReadStatSession (bytesSent);


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


/* Add constraints */
ALTER TABLE jiveRating ADD CONSTRAINT jiveRating_score_fk FOREIGN KEY (score) REFERENCES jiveRatingType;
ALTER TABLE jivePollOption ADD CONSTRAINT jivePollOption_pollID_fk FOREIGN KEY (pollID) REFERENCES jivePoll;
ALTER TABLE jivePollVote ADD CONSTRAINT jivePollVote_pollID_fk FOREIGN KEY (pollID) REFERENCES jivePoll;
ALTER TABLE jiveSearchCriteria ADD CONSTRAINT jiveSearchCriteria_sID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch;
ALTER TABLE jiveSearchClick ADD CONSTRAINT jiveSearchClick_searchID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch;
ALTER TABLE jiveNNTPReadStatSession ADD CONSTRAINT jiveNNTPReadStatSession_sID_fk FOREIGN KEY (sessionID) REFERENCES jiveReadStatSession;
ALTER TABLE jiveHTTPReadStatSession ADD CONSTRAINT jiveHTTPReadStatSession_sID_fk FOREIGN KEY (sessionID) REFERENCES jiveReadStatSession;
ALTER TABLE jiveAttachData ADD CONSTRAINT jiveAttachData_aID_fk FOREIGN KEY (attachmentID) REFERENCES jiveAttachment;
ALTER TABLE jiveAttachmentProp ADD CONSTRAINT jiveAttachmentProp_aID_fk FOREIGN KEY (attachmentID) REFERENCES jiveAttachment; 
GO

/* 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);
GO

/* Add in the new permissionType column to the jiveUserPerm table */
ALTER TABLE jiveUserPerm ADD permissionType INTEGER;
GO

/* Insert default value */
UPDATE jiveUserPerm SET permissionType = '1';
GO

/* Make column not null */
ALTER TABLE jiveUserPerm ALTER COLUMN permissionType INTEGER NOT NULL;
GO

/* Add in the new permissionType column to the jiveGroupPerm table */
ALTER TABLE jiveGroupPerm ADD permissionType INTEGER;
GO

/* Insert default value */
UPDATE jiveGroupPerm SET permissionType = '1';
GO

/* Make column not null */
ALTER TABLE jiveGroupPerm ALTER COLUMN permissionType INTEGER NOT NULL;
GO

/* Add forumIndex column to the jiveMessage table */
ALTER TABLE jiveMessage ADD forumIndex INTEGER;
GO

/* Insert default value */
UPDATE jiveMessage SET forumIndex = '0';
GO

/* Make column not null */
ALTER TABLE jiveMessage ALTER COLUMN forumIndex INTEGER NOT NULL;
GO

/* Update index */
DROP INDEX jiveMessage.jiveMessage_forumID_idx;
CREATE INDEX jiveMessage_forum_idx ON jiveMessage(forumID ASC, forumIndex);
GO

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

/* 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 NVARCHAR(255) NULL;
GO
UPDATE jiveForum SET nntpName = forumID;
ALTER TABLE jiveForum ALTER COLUMN nntpName NVARCHAR(255) NOT NULL;
GO
CREATE UNIQUE INDEX nntpUniqueIdx ON jiveForum (nntpName);
GO

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

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

/* Upgrade the jiveAttachment table */
DROP INDEX jiveAttachment.jiveAttachment_messageID_idx;
EXEC sp_rename 'jiveAttachment.messageID', 'objectID', 'COLUMN';
ALTER TABLE jiveAttachment ADD objectType INTEGER NULL;
GO
UPDATE jiveAttachment set objectType='2';
ALTER TABLE jiveAttachment ALTER COLUMN objectType INTEGER NOT NULL;
CREATE INDEX jiveAttach_object_idx_new ON jiveAttachment (objectType, objectID);
ALTER TABLE jiveAttachment DROP CONSTRAINT jiveAttachment_msgID_fk;
GO

/* Upgrade some property names */

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

GO