jive_forums_db2_date_conversion.sql

Jive Forums 论坛源码 v4.2.3

  userID                BIGINT,
  modDate               BIGINT          NOT NULL,
  modValue              INTEGER         NOT NULL
);
CREATE INDEX jMo_objectID_idx ON jiveModeration (objectID ASC);
CREATE INDEX jMo_objectType_idx on jiveModeration (objectType);
CREATE INDEX jMo_userID_idx ON jiveModeration (userID ASC);

INSERT INTO jiveModeration (
    objectType,
    objectID,    
    userID,
    modDate,
    modValue
) SELECT objectType, objectID, userID, CAST(modDate AS BIGINT), modValue FROM jiveModeration_t;

DROP TABLE jiveModeration_t;


-- jiveBatchWatch

ALTER TABLE jiveBatchWatch DROP PRIMARY KEY;
RENAME TABLE jiveBatchWatch TO jiveBatchWatch_t;

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

INSERT INTO jiveBatchWatch (
  userID,
  frequency,
  prevEmailDate
) SELECT userID, frequency, CAST(prevEmailDate AS BIGINT) FROM jiveBatchWatch_t;

DROP TABLE jiveBatchWatch_t;


-- jiveReward

DROP INDEX jR_userID_idx;
DROP INDEX jR_creatDate_idx;
DROP INDEX jR_messageID_idx;
DROP INDEX jR_threadID_idx;
RENAME TABLE jiveReward TO jiveReward_t;

CREATE TABLE jiveReward (
  userID                 BIGINT         NOT NULL,
  creationDate           BIGINT         NOT NULL,
  rewardPoints           INTEGER        NOT NULL,
  messageID              BIGINT,
  threadID               BIGINT
);
CREATE INDEX jR_userID_idx ON jiveReward (userID ASC);
CREATE INDEX jR_creatDate_idx ON jiveReward (creationDate);
CREATE INDEX jR_messageID_idx ON jiveReward (messageID ASC);
CREATE INDEX jR_threadID_idx ON jiveReward (threadID ASC);

INSERT INTO jiveReward (
  userID,
  creationDate,
  rewardPoints,
  messageID,
  threadID
) SELECT userID, CAST(creationDate AS BIGINT), rewardPoints, messageID, threadID FROM jiveReward_t;

DROP TABLE jiveReward_t;


-- jiveReadTracker

ALTER TABLE jiveReadTracker DROP PRIMARY KEY;
RENAME TABLE jiveReadTracker TO jiveReadTracker_t;

CREATE TABLE jiveReadTracker (
  userID                 BIGINT         NOT NULL,
  objectType             INTEGER        NOT NULL,
  objectID               BIGINT         NOT NULL,
  readDate               BIGINT         NOT NULL,
  CONSTRAINT jRT_pk PRIMARY KEY (userID, objectType, objectID)
);

INSERT INTO jiveReadTracker (
  userID,
  objectType,
  objectID,
  readDate
) SELECT userID, objectType, objectID, CAST(readDate AS BIGINT) FROM jiveReadTracker_t;

DROP TABLE jiveReadTracker_t;


-- jiveAttachment

DROP INDEX jA_objID_idx;
ALTER TABLE jiveAttachment DROP PRIMARY KEY;
RENAME TABLE jiveAttachment TO jiveAttachment_t;

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

INSERT INTO jiveAttachment (
  attachmentID,
  objectType,
  objectID,
  fileName,
  fileSize,
  contentType,
  creationDate,
  modificationDate
) SELECT attachmentID, objectType, objectID, fileName, fileSize, contentType,
    CAST(creationDate AS BIGINT), CAST(modificationDate AS BIGINT) FROM jiveAttachment_t;

DROP TABLE jiveAttachment_t;


-- jivePoll

DROP INDEX jPoll_oID_idx;
DROP INDEX jPoll_cDate_idx;
DROP INDEX jPoll_mDate_idx;
DROP INDEX jPoll_sDate_idx;
DROP INDEX jPoll_eDate_idx;
ALTER TABLE jivePoll DROP PRIMARY KEY;
ALTER TABLE jivePollOption DROP FOREIGN KEY jivePllOpt_pID_fk;
ALTER TABLE jivePollVote DROP FOREIGN KEY jivePllVte_pID_fk;
RENAME TABLE jivePoll TO jivePoll_t;

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

INSERT INTO jivePoll (
  pollID,
  objectType,
  objectID,
  userID,
  name,
  description,
  pollMode,
  creationDate,
  modificationDate,
  startDate,
  endDate,
  expireDate
) SELECT pollID, objectType, objectID, userID, name, description, pollMode, CAST(creationDate AS BIGINT),
  CAST(modificationDate AS BIGINT), CAST(startDate AS BIGINT), CAST(endDate AS BIGINT),
  CAST(expireDate AS BIGINT) FROM jivePoll_t;

DROP TABLE jivePoll_t;
ALTER TABLE jivePollOption ADD CONSTRAINT jivePllOpt_pID_fk FOREIGN KEY (pollID) REFERENCES jivePoll;


-- jivePollVote

DROP INDEX jivePV_pID_idx;
RENAME TABLE jivePollVote TO jivePollVote_t;

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

INSERT INTO jivePollVote (
  pollID,
  userID,
  guestID,
  optionIndex,
  voteDate
) SELECT pollID, userID, guestID, optionIndex, CAST(voteDate AS BIGINT) FROM jivePollVote_t;

DROP TABLE jivePollVote_t;
ALTER TABLE jivePollVote ADD CONSTRAINT jivePllVte_pID_fk FOREIGN KEY (pollID) REFERENCES jivePoll;

-- jiveSearch

DROP INDEX jiveSrch_uID_idx;
DROP INDEX jiveSrch_type_idx;
ALTER TABLE jiveSearch DROP PRIMARY KEY;
ALTER TABLE jiveSearchCriteria DROP FOREIGN KEY jiveSrchCr_sID_fk;
ALTER TABLE jiveSearchClick DROP FOREIGN KEY jiveSrchCl_sID_fk;
RENAME TABLE jiveSearch TO jiveSearch_t;

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

INSERT INTO jiveSearch (
  searchID,
  searchType,
  userID,
  query,
  searchDuration,
  numResults,
  searchDate
) SELECT searchID, searchType, userID, query, searchDuration, numResults, CAST(searchDate AS BIGINT)
    FROM jiveSearch_t;

DROP TABLE jiveSearch_t;
ALTER TABLE jiveSearchCriteria ADD CONSTRAINT jiveSrchCr_sID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch;


-- jiveSearchClick

ALTER TABLE jiveSearchClick DROP PRIMARY KEY;
RENAME TABLE jiveSearchClick TO jiveSearchClick_t;

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

INSERT INTO jiveSearchClick (
  searchID,
  messageID,
  clickDate
) SELECT searchID, messageID, CAST(clickDate AS BIGINT) FROM jiveSearchClick_t;

DROP TABLE jiveSearchClick_t;
ALTER TABLE jiveSearchClick ADD CONSTRAINT jiveSrchCl_sID_fk FOREIGN KEY (searchID) REFERENCES jiveSearch;


-- jiveReadStat

DROP INDEX jiveRStat_cd_idx;
ALTER TABLE jiveReadStat DROP PRIMARY KEY;
RENAME TABLE jiveReadStat TO jiveReadStat_t;

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

INSERT INTO jiveReadStat (
  readStatID,
  userID,
  objectType,
  objectID,
  creationDate,
  sessionID
) SELECT readStatID, userID, objectType, objectID, CAST(creationDate AS BIGINT), sessionID
    FROM jiveReadStat_t;

DROP TABLE jiveReadStat_t;


-- jiveReadStatSession

DROP INDEX jiveRSS_cd_idx;
ALTER TABLE jiveReadStatSession DROP PRIMARY KEY;
RENAME TABLE jiveReadStatSession TO jiveReadStatSession_t;

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

INSERT INTO jiveReadStatSession (
    sessionID,
    visitorID,
    creationDate
) SELECT sessionID, visitorID, CAST(creationDate AS BIGINT) FROM jiveReadStatSession_t;

DROP TABLE jiveReadStatSession_t;


-- jiveNNTPReadStatSession

DROP INDEX jiveNRSS_ID_idx;
DROP INDEX jiveNRSS_cd_idx;
DROP INDEX jiveNRSS_ed_idx;
RENAME TABLE jiveNNTPReadStatSession TO jiveNNTPReadStatSession_t;

CREATE TABLE jiveNNTPReadStatSession (
    creationDate        BIGINT          NOT NULL,
    endDate             BIGINT,
    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);

INSERT INTO jiveNNTPReadStatSession (
  creationDate,
  endDate,
  bytesReceived,
  bytesSent,
  IP,
  country,
  sessionID
) SELECT CAST(creationDate AS BIGINT), CAST(endDate AS BIGINT), bytesReceived, bytesSent, IP,
    country, sessionID FROM jiveNNTPReadStatSession_t;

DROP TABLE jiveNNTPReadStatSession_t;