jive_forums_db2_date_conversion.sql

Jive Forums 论坛源码 v4.2.3

--
-- Jive Forums Upgrade Script - Converts string dates to BIGINT's
--
-- $RCSfile$
-- $Revision: 16342 $
-- $Date: 2005-06-10 13:19:18 -0700 (Fri, 10 Jun 2005) $


-- jiveUser

DROP INDEX jU_cDate_idx;
DROP INDEX jU_hash_idx;
DROP INDEX jU_usernm_idx;
ALTER TABLE jiveUser DROP PRIMARY KEY;
RENAME TABLE jiveUser TO jiveUser_t;

CREATE TABLE jiveUser (
  userID            BIGINT              NOT NULL,
  username          VARCHAR(30)         UNIQUE NOT NULL,
  passwordHash      VARCHAR(32)         NOT NULL,
  name              VARCHAR(100),
  nameVisible       INTEGER             NOT NULL,
  email             VARCHAR(100)        NOT NULL,
  emailVisible      INTEGER             NOT NULL,
  creationDate      BIGINT              NOT NULL,
  modificationDate  BIGINT              NOT NULL,
  CONSTRAINT jiveUser_pk PRIMARY KEY (userID)
);
CREATE INDEX jU_cDate_idx on jiveUser (creationDate ASC);
CREATE INDEX jU_hash_idx on jiveUser (passwordHash);
CREATE INDEX jU_usernm_idx on jiveUser (username);

INSERT INTO jiveUser (
    userID,
    username,
    passwordHash,
    name,
    nameVisible,
    email,
    emailVisible,
    creationDate,
    modificationDate
) SELECT userID, username, passwordHash, name, nameVisible, email, emailVisible,
    CAST(creationDate AS BIGINT), CAST(modificationDate AS BIGINT)
    FROM jiveUser_t;

DROP TABLE jiveUser_t;


-- jiveGroup

DROP INDEX jG_cDate_idx;
ALTER TABLE jiveGroup DROP PRIMARY KEY;
RENAME TABLE jiveGroup TO jiveGroup_t;

CREATE TABLE jiveGroup (
  groupID               BIGINT          NOT NULL,
  name                  VARCHAR(100)    UNIQUE NOT NULL,
  description           VARCHAR(255),
  creationDate          BIGINT          NOT NULL,
  modificationDate      BIGINT          NOT NULL,
  CONSTRAINT jiveGroup_pk PRIMARY KEY (groupID)
);
CREATE INDEX jG_cDate_idx on jiveGroup (creationDate ASC);

INSERT INTO jiveGroup (
    groupID,
    name,
    description,
    creationDate,
    modificationDate
) SELECT groupID, name, description, CAST(creationDate AS BIGINT), CAST(modificationDate AS BIGINT)
    FROM jiveGroup_t;

DROP TABLE jiveGroup_t;


-- jiveCategory

DROP INDEX jC_lft_idx;
DROP INDEX jC_rgt_idx;
ALTER TABLE jiveCategory DROP PRIMARY KEY;
RENAME TABLE jiveCategory TO jiveCategory_t;

CREATE TABLE jiveCategory (
  categoryID            BIGINT          NOT NULL,
  name                  VARCHAR(255)    NOT NULL,
  description           VARCHAR(2000),
  creationDate          BIGINT          NOT NULL,
  modificationDate      BIGINT          NOT NULL,
  lft                   INTEGER         NOT NULL,
  rgt                   INTEGER         NOT NULL,
  CONSTRAINT jC_pk PRIMARY KEY (categoryID)
);
CREATE INDEX jC_lft_idx ON jiveCategory (lft);
CREATE INDEX jC_rgt_idx ON jiveCategory (rgt);

INSERT INTO jiveCategory (
  categoryID,
  name,
  description,
  creationDate,
  modificationDate,
  lft,
  rgt
) SELECT categoryID, name, description, CAST(creationDate AS BIGINT), CAST(modificationDate AS BIGINT),
    lft, rgt FROM jiveCategory_t;

DROP TABLE jiveCategory_t;


-- jiveForum

DROP INDEX jF_catID_idx;
DROP INDEX jF_forumIdxCtr_idx;
DROP INDEX jF_cDate_idx;
DROP INDEX jF_mDate_idx;
ALTER TABLE jiveForum DROP PRIMARY KEY;
ALTER TABLE jiveForumProp DROP FOREIGN KEY jFP_forumID_fk;
ALTER TABLE jiveThread DROP FOREIGN KEY jT_forumID_fk;
ALTER TABLE jiveMessage DROP FOREIGN KEY jM_forumID_fk;
RENAME TABLE jiveForum TO jiveForum_t;

CREATE TABLE jiveForum (
  forumID               BIGINT          NOT NULL,
  name                  VARCHAR(255)    NOT NULL,
  nntpName              VARCHAR(255)    UNIQUE NOT NULL,
  description           VARCHAR(2000),
  modDefaultThreadVal   INTEGER         NOT NULL,
  modDefaultMsgVal      INTEGER         NOT NULL,
  creationDate          BIGINT          NOT NULL,
  modificationDate      BIGINT          NOT NULL,
  categoryID            BIGINT          NOT NULL,
  categoryIndex         INTEGER         NOT NULL,
  forumIndexCounter     INTEGER         NOT NULL,
  CONSTRAINT jiveForum_pk PRIMARY KEY (forumID)
);
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);

INSERT INTO jiveForum (
  forumID,
  name,
  nntpName,
  description,
  modDefaultThreadVal,
  modDefaultMsgVal,
  creationDate,
  modificationDate,
  categoryID,
  categoryIndex,
  forumIndexCounter
) SELECT forumID, name, nntpName, description, modDefaultThreadVal, modDefaultMsgVal,
    CAST(creationDate AS BIGINT), CAST(modificationDate AS BIGINT), categoryID, categoryIndex,
    forumIndexCounter FROM jiveForum_t;

DROP TABLE jiveForum_t;
ALTER TABLE jiveForumProp ADD CONSTRAINT jFP_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;


-- jiveThread

DROP INDEX jT_forumID_idx;
DROP INDEX jT_modValue_idx;
DROP INDEX jT_cDate_idx;
DROP INDEX jT_mDate_idx;
DROP INDEX jT_fID_mV_idx;
ALTER TABLE jiveThread DROP PRIMARY KEY;
ALTER TABLE jiveThreadProp DROP FOREIGN KEY jTP_threadID_fk;
ALTER TABLE jiveMessage DROP FOREIGN KEY jM_threadID_fk;
ALTER TABLE jiveQuestion DROP FOREIGN KEY jiveQuest_tID_fk;
ALTER TABLE jiveQuestionProp DROP FOREIGN KEY jiveQuestP_tID_fk;
RENAME TABLE jiveThread TO jiveThread_t;

CREATE TABLE jiveThread (
  threadID              BIGINT          NOT NULL,
  forumID               BIGINT          NOT NULL,
  rootMessageID         BIGINT          NOT NULL,
  modValue              INTEGER         NOT NULL,
  rewardPoints          INTEGER         NOT NULL,
  creationDate          BIGINT          NOT NULL,
  modificationDate      BIGINT          NOT NULL,
  CONSTRAINT jiveThread_pk PRIMARY KEY (threadID)
);
CREATE INDEX jT_forumID_idx ON jiveThread (forumID);
CREATE INDEX jT_modValue_idx ON jiveThread (modValue);
CREATE INDEX jT_cDate_idx ON jiveThread (creationDate ASC);
CREATE INDEX jT_mDate_idx ON jiveThread (modificationDate DESC);
CREATE INDEX jT_fID_mV_idx ON jiveThread (forumID, modValue);

INSERT INTO jiveThread (
  threadID,
  modificationDate,
  creationDate,
  rewardPoints,
  modValue,
  rootMessageID,
  forumID
) SELECT threadID, CAST(modificationDate AS BIGINT), CAST(creationDate AS BIGINT),
    rewardPoints, modValue, rootMessageID, forumID FROM jiveThread_t;

DROP TABLE jiveThread_t;
ALTER TABLE jiveThread ADD CONSTRAINT jT_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;
ALTER TABLE jiveThreadProp ADD CONSTRAINT jTP_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread;
ALTER TABLE jiveQuestion ADD CONSTRAINT jiveQuest_tID_fk FOREIGN KEY (threadID) REFERENCES jiveThread;
ALTER TABLE jiveQuestionProp ADD CONSTRAINT jiveQuestP_tID_fk FOREIGN KEY (threadID) REFERENCES jiveThread;


-- jiveMessage

DROP INDEX jM_forum_idx;
DROP INDEX jM_threadID_idx;
DROP INDEX jM_userID_idx;
DROP INDEX jM_forumId_mV_idx;
DROP INDEX jM_cDate_idx;
DROP INDEX jM_mDate_idx;
ALTER TABLE jiveMessage DROP PRIMARY KEY;
ALTER TABLE jiveMessageProp DROP FOREIGN KEY jMP_msgID_fk;
RENAME TABLE jiveMessage TO jiveMessage_t;

CREATE TABLE jiveMessage (
  messageID             BIGINT          NOT NULL,
  parentMessageID       BIGINT,
  threadID              BIGINT          NOT NULL,
  forumID               BIGINT          NOT NULL,
  forumIndex            BIGINT          NOT NULL,
  userID                BIGINT,
  subject               VARCHAR(255),
  body                  LONG VARCHAR,
  modValue              INTEGER         NOT NULL,
  rewardPoints          INTEGER         NOT NULL,
  creationDate          BIGINT          NOT NULL,
  modificationDate      BIGINT          NOT NULL,
  CONSTRAINT jiveMessage_pk PRIMARY KEY (messageID)
);
CREATE INDEX jM_forum_idx ON jiveMessage (forumID, forumIndex);
CREATE INDEX jM_threadID_idx ON jiveMessage (threadID ASC);
CREATE INDEX jM_userID_idx ON jiveMessage (userID ASC);
CREATE INDEX jM_forumId_mV_idx ON jiveMessage(forumID, modValue);
CREATE INDEX jM_cDate_idx ON jiveMessage (creationDate ASC);
CREATE INDEX jM_mDate_idx ON jiveMessage (modificationDate DESC);

INSERT INTO jiveMessage (
 messageID,
 parentMessageID,
 threadID,
 forumID,
 forumIndex,
 userID,
 subject,
 body,
 modValue,
 rewardPoints,
 creationDate,
 modificationDate
) SELECT messageID, parentMessageID, threadID, forumID, forumIndex, userID, subject, body, modValue,
    rewardPoints, CAST(creationDate AS BIGINT), CAST(modificationDate AS BIGINT) FROM jiveMessage_t;

DROP TABLE jiveMessage_t;
ALTER TABLE jiveMessage ADD CONSTRAINT jM_threadID_fk FOREIGN KEY (threadID) REFERENCES jiveThread;
ALTER TABLE jiveMessage ADD CONSTRAINT jM_forumID_fk FOREIGN KEY (forumID) REFERENCES jiveForum;
ALTER TABLE jiveMessageProp ADD CONSTRAINT jMP_msgID_fk FOREIGN KEY (messageID) REFERENCES jiveMessage;


-- jiveAnnounce

DROP INDEX jiveAn_sDt_idx;
DROP INDEX jiveAn_eDt_idx;
DROP INDEX jiveAn_ctr_idx;
DROP INDEX jiveAn_usr_idx;
ALTER TABLE jiveAnnounce DROP PRIMARY KEY;
RENAME TABLE jiveAnnounce TO jiveAnnounce_t;

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             BIGINT          NOT NULL,
  endDate               BIGINT,
  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);

INSERT INTO jiveAnnounce (
  announcementID,
  objectType,
  objectID,
  userID,
  subject,
  body,
  startDate,
  endDate
) SELECT announcementID, objectType, objectID, userID, subject, body, CAST(startDate AS BIGINT),
    CAST(endDate AS BIGINT) FROM jiveAnnounce_t;

DROP TABLE jiveAnnounce_t;


-- jivePMessage

DROP INDEX jivePMsg_r_idx;
ALTER TABLE jivePMessage DROP PRIMARY KEY;
RENAME TABLE jivePMessage TO jivePMessage_t;

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          BIGINT          NOT NULL,
  CONSTRAINT jivePMsg_pk PRIMARY KEY (pMessageID)
);
CREATE INDEX jivePMsg_r_idx  ON jivePMessage (recipientID);

INSERT INTO jivePMessage (
  pMessageID,
  ownerID,
  senderID,
  recipientID,
  subject,
  body,
  readStatus,
  folderID,
  pMessageDate
) SELECT pMessageID, ownerID, senderID, recipientID, subject, body, readStatus, folderID,
  CAST(pMessageDate AS BIGINT) FROM jivePMessage_t;

DROP TABLE jivePMessage_t;


-- jiveModeration

DROP INDEX jMo_objectID_idx;
DROP INDEX jMo_objectType_idx;
DROP INDEX jMo_userID_idx;
RENAME TABLE jiveModeration TO jiveModeration_t;

CREATE TABLE jiveModeration (
  objectType            INTEGER         NOT NULL,
  objectID              BIGINT          NOT NULL,