jive_forums_oracle_date_conversion.sql

Jive Forums 论坛源码 v4.2.3

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jivePMessage!') AS msg FROM dual;
COMMIT;



/* -------------------------------------------------------------------------- */
/* jiveModeration conversion ------------------------------------------------ */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveModeration') AS msg FROM dual;

CREATE TABLE jiveModerationDateData (
    objectID,
    objectType,
    modDate
) AS SELECT objectID, objectType, CAST(modDate AS INTEGER)
    FROM jiveModeration;

ALTER TABLE jiveModeration DROP COLUMN modDate;

ALTER TABLE jiveModeration ADD modDate INTEGER NULL;

UPDATE jiveModeration c SET c.modDate = (SELECT d.modDate FROM jiveModerationDateData d  WHERE d.objectID = c.objectID AND d.objectType = c.objectType);

ALTER TABLE jiveModeration MODIFY (modDate NOT NULL);

DROP TABLE jiveModerationDateData;

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveModeration!') AS msg FROM dual;
COMMIT;


/* -------------------------------------------------------------------------- */
/* jiveBatchWatch conversion ------------------------------------------------ */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveBatchWatch') AS msg FROM dual;

CREATE TABLE jiveBatchWatchDateData (
    userID,
    prevEmailDate
) AS SELECT userID, CAST(prevEmailDate AS INTEGER)
    FROM jiveBatchWatch;

ALTER TABLE jiveBatchWatch DROP COLUMN prevEmailDate;

ALTER TABLE jiveBatchWatch ADD prevEmailDate INTEGER NULL;

UPDATE jiveBatchWatch c SET c.prevEmailDate = (SELECT d.prevEmailDate FROM jiveBatchWatchDateData d  WHERE d.userID = c.userID);

DROP TABLE jiveBatchWatchDateData;

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveBatchWatch!') AS msg FROM dual;
COMMIT;

/* -------------------------------------------------------------------------- */
/* jiveReward conversion ---------------------------------------------------- */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveReward') AS msg FROM dual;

CREATE TABLE jiveRewardDateData (
    userID,
    creationDate,
    rewardPoints,
    messageID,
    threadID
) AS SELECT userID, CAST(creationDate AS INTEGER), rewardPoints, messageID, threadID
    FROM jiveReward;

ALTER TABLE jiveReward DROP COLUMN creationDate;

ALTER TABLE jiveReward ADD creationDate INTEGER NULL;

UPDATE jiveReward c SET c.creationDate = (SELECT d.creationDate FROM jiveRewardDateData d
    WHERE d.userID = c.userID 
        AND d.rewardPoints = c.rewardPoints AND d.messageID = c.messageID
        AND d.threadID = c.threadID);

UPDATE jiveReward SET creationDate = 0 WHERE creationDate IS NULL;

ALTER TABLE jiveReward MODIFY (creationDate NOT NULL);

DROP TABLE jiveRewardDateData;

CREATE INDEX jiveReward_cDate_idx on jiveReward (creationDate ASC);

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveReward!') AS msg FROM dual;
COMMIT;



/* -------------------------------------------------------------------------- */
/* jiveReadTracker conversion ----------------------------------------------- */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveReadTracker') AS msg FROM dual;

CREATE TABLE jiveReadTrackerDateData (
    userID,
    objectType,
    objectID,
    readDate,
    CONSTRAINT jiveReadTrackerDateData_pk PRIMARY KEY (userID,objectType,objectID)
) AS SELECT userID, objectType, objectID, CAST(readDate AS INTEGER)
    FROM jiveReadTracker;

ALTER TABLE jiveReadTracker DROP COLUMN readDate;

ALTER TABLE jiveReadTracker ADD readDate INTEGER NULL;

UPDATE jiveReadTracker c SET c.readDate = (SELECT d.readDate FROM jiveReadTrackerDateData d  WHERE d.userID = c.userID AND d.objectType = c.objectType AND d.objectID = c.objectID);

ALTER TABLE jiveReadTracker MODIFY (readDate NOT NULL);

DROP TABLE jiveReadTrackerDateData;

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveReadTracker!') AS msg FROM dual;
COMMIT;


/* -------------------------------------------------------------------------- */
/* jiveAttachment conversion ------------------------------------------------ */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveAttachment') AS msg FROM dual;

CREATE TABLE jiveAttachmentDateData (
    attachmentID,
    creationDate,
    modificationDate,
    CONSTRAINT jiveAttachmentDateData_pk PRIMARY KEY (attachmentID)
) AS SELECT attachmentID, CAST(creationDate AS INTEGER), CAST(modificationDate AS INTEGER)
    FROM jiveAttachment;

ALTER TABLE jiveAttachment DROP COLUMN creationDate;
ALTER TABLE jiveAttachment DROP COLUMN modificationDate;

ALTER TABLE jiveAttachment ADD creationDate INTEGER NULL;
ALTER TABLE jiveAttachment ADD modificationDate INTEGER NULL;

UPDATE jiveAttachment c SET c.creationDate = (SELECT d.creationDate FROM jiveAttachmentDateData d  WHERE d.attachmentID = c.attachmentID);
UPDATE jiveAttachment c SET c.modificationDate  = (SELECT d.modificationDate FROM jiveAttachmentDateData d  WHERE d.attachmentID = c.attachmentID);

ALTER TABLE jiveAttachment MODIFY (creationDate NOT NULL);
ALTER TABLE jiveAttachment MODIFY (modificationDate NOT NULL);

DROP TABLE jiveAttachmentDateData;

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveAttachment!') AS msg FROM dual;
COMMIT;



/* -------------------------------------------------------------------------- */
/* jivePoll conversion ------------------------------------------------------ */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jivePoll') AS msg FROM dual;

CREATE TABLE jivePollDateData (
    pollID,
    creationDate,
    modificationDate,
    startDate,
    endDate,
    expireDate,
    CONSTRAINT jivePollDateData_pk PRIMARY KEY (pollID)
) AS SELECT pollID, CAST(creationDate AS INTEGER), CAST(modificationDate AS INTEGER),
        CAST(startDate AS INTEGER), CAST(endDate AS INTEGER), CAST(expireDate AS INTEGER)
    FROM jivePoll;

ALTER TABLE jivePoll DROP COLUMN creationDate;
ALTER TABLE jivePoll DROP COLUMN modificationDate;
ALTER TABLE jivePoll DROP COLUMN startDate;
ALTER TABLE jivePoll DROP COLUMN endDate;
ALTER TABLE jivePoll DROP COLUMN expireDate;

ALTER TABLE jivePoll ADD creationDate INTEGER NULL;
ALTER TABLE jivePoll ADD modificationDate INTEGER NULL;
ALTER TABLE jivePoll ADD startDate INTEGER NULL;
ALTER TABLE jivePoll ADD endDate INTEGER NULL;
ALTER TABLE jivePoll ADD expireDate INTEGER NULL;

UPDATE jivePoll c SET c.creationDate = (SELECT d.creationDate FROM jivePollDateData d  WHERE d.pollID = c.pollID);
UPDATE jivePoll c SET c.modificationDate  = (SELECT d.modificationDate FROM jivePollDateData d  WHERE d.pollID = c.pollID);
UPDATE jivePoll c SET c.startDate  = (SELECT d.startDate FROM jivePollDateData d  WHERE d.pollID = c.pollID);
UPDATE jivePoll c SET c.endDate  = (SELECT d.endDate FROM jivePollDateData d  WHERE d.pollID = c.pollID);
UPDATE jivePoll c SET c.expireDate  = (SELECT d.expireDate FROM jivePollDateData d  WHERE d.pollID = c.pollID);

ALTER TABLE jivePoll MODIFY (creationDate NOT NULL);
ALTER TABLE jivePoll MODIFY (modificationDate NOT NULL);
ALTER TABLE jivePoll MODIFY (startDate NOT NULL);
ALTER TABLE jivePoll MODIFY (endDate NOT NULL);
ALTER TABLE jivePoll MODIFY (expireDate NOT NULL);

DROP TABLE jivePollDateData;

CREATE INDEX jivePoll_cDate_idx ON jivePoll (creationDate ASC);
CREATE INDEX jivePoll_mDate_idx ON jivePoll (modificationDate DESC);
CREATE INDEX jivePoll_sDate_idx ON jivePoll (startDate DESC);
CREATE INDEX jivePoll_eDate_idx ON jivePoll (endDate DESC);

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jivePoll!') AS msg FROM dual;
COMMIT;



/* -------------------------------------------------------------------------- */
/* jivePollVote conversion -------------------------------------------------- */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jivePollVote') AS msg FROM dual;

CREATE TABLE jivePollVoteDateData (
    pollID,
    voteDate
) AS SELECT pollID, CAST(voteDate AS INTEGER)
    FROM jivePollVote;

ALTER TABLE jivePollVote DROP COLUMN voteDate;

ALTER TABLE jivePollVote ADD voteDate INTEGER NULL;

UPDATE jivePollVote c SET c.voteDate = (SELECT d.voteDate FROM jivePollVoteDateData d  WHERE d.pollID = c.pollID);

ALTER TABLE jivePollVote MODIFY (voteDate NOT NULL);

DROP TABLE jivePollVoteDateData;

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jivePollVote!') AS msg FROM dual;
COMMIT;



/* -------------------------------------------------------------------------- */
/* jiveSearch conversion ---------------------------------------------------- */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveSearch') AS msg FROM dual;

CREATE TABLE jiveSearchDateData (
    searchID,
    searchDate,
    CONSTRAINT jiveSearchDateData_pk PRIMARY KEY (searchID)
) AS SELECT searchID, CAST(searchDate AS INTEGER)
    FROM jiveSearch;

ALTER TABLE jiveSearch DROP COLUMN searchDate;

ALTER TABLE jiveSearch ADD searchDate INTEGER NULL;

UPDATE jiveSearch c SET c.searchDate = (SELECT d.searchDate FROM jiveSearchDateData d  WHERE d.searchID = c.searchID);

ALTER TABLE jiveSearch MODIFY (searchDate NOT NULL);

DROP TABLE jiveSearchDateData;

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveSearch!') AS msg FROM dual;
COMMIT;


/* -------------------------------------------------------------------------- */
/* jiveSearchClick conversion ----------------------------------------------- */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveSearchClick') AS msg FROM dual;

CREATE TABLE jiveSearchClickDateData (
    searchID,
    messageID,
    clickDate,
    CONSTRAINT jiveSearchClickDateData_pk PRIMARY KEY (searchID, messageID, clickDate)
) AS SELECT searchID, messageID, CAST(clickDate AS INTEGER)
    FROM jiveSearchClick;

ALTER TABLE jiveSearchClick DROP PRIMARY KEY;
ALTER TABLE jiveSearchClick DROP COLUMN clickDate;

ALTER TABLE jiveSearchClick ADD clickDate INTEGER NULL;

UPDATE jiveSearchClick c SET c.clickDate = (SELECT d.clickDate FROM jiveSearchClickDateData d  WHERE d.searchID = c.searchID AND d.messageID = c.messageID AND c.clickDate = d.clickDate);

ALTER TABLE jiveSearchClick MODIFY (clickDate NOT NULL);

DROP TABLE jiveSearchClickDateData;

ALTER TABLE jiveSearchClick ADD CONSTRAINT jiveSearchClick_pk PRIMARY KEY (searchID, messageID, clickDate);

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveSearchClick!') AS msg FROM dual;
COMMIT;



/* -------------------------------------------------------------------------- */
/* jiveReadStat conversion -------------------------------------------------- */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveReadStat') AS msg FROM dual;

CREATE TABLE jiveReadStatDateData (
    readStatID,
    creationDate,
    CONSTRAINT jiveReadStatDateData_pk PRIMARY KEY (readStatID)
) AS SELECT readStatID, CAST(creationDate AS INTEGER)
    FROM jiveReadStat;

ALTER TABLE jiveReadStat DROP COLUMN creationDate;

ALTER TABLE jiveReadStat ADD creationDate INTEGER NULL;

UPDATE jiveReadStat c SET c.creationDate = (SELECT d.creationDate FROM jiveReadStatDateData d  WHERE d.readStatID = c.readStatID);

ALTER TABLE jiveReadStat MODIFY (creationDate NOT NULL);

DROP TABLE jiveReadStatDateData;

CREATE INDEX jiveReadStat_cd_idx ON jiveReadStat (creationDate);

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveReadStat!') AS msg FROM dual;
COMMIT;



/* -------------------------------------------------------------------------- */
/* jiveReadStatSession conversion ------------------------------------------- */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveReadStatSession') AS msg FROM dual;

CREATE TABLE jiveReadStatSessionDateData (
    sessionID,
    creationDate,
    CONSTRAINT jiveReadStatSessionDateData_pk PRIMARY KEY (sessionID)
) AS SELECT sessionID, CAST(creationDate AS INTEGER)
    FROM jiveReadStatSession;

ALTER TABLE jiveReadStatSession DROP COLUMN creationDate;

ALTER TABLE jiveReadStatSession ADD creationDate INTEGER NULL;

UPDATE jiveReadStatSession c SET c.creationDate = (SELECT d.creationDate FROM jiveReadStatSessionDateData d  WHERE d.sessionID = c.sessionID);

ALTER TABLE jiveReadStatSession MODIFY (creationDate NOT NULL);

DROP TABLE jiveReadStatSessionDateData;

CREATE INDEX jiveRSS_cd_idx ON jiveReadStatSession (creationDate);

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveReadStatSession!') AS msg FROM dual;
COMMIT;


/* -------------------------------------------------------------------------- */
/* jiveNNTPReadStatSession conversion --------------------------------------- */
/* -------------------------------------------------------------------------- */

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Migrating dates in jiveNNTPReadStatSession') AS msg FROM dual;

CREATE TABLE jiveNNTPRSSDateData (
    sessionID,
    creationDate,
    endDate,
    CONSTRAINT jiveNNTPRSSDateData_pk PRIMARY KEY (sessionID)
) AS SELECT sessionID, CAST(creationDate AS INTEGER), CAST(endDate AS INTEGER)
    FROM jiveNNTPReadStatSession;

ALTER TABLE jiveNNTPReadStatSession DROP COLUMN creationDate;
ALTER TABLE jiveNNTPReadStatSession DROP COLUMN endDate;

ALTER TABLE jiveNNTPReadStatSession ADD creationDate INTEGER NULL;
ALTER TABLE jiveNNTPReadStatSession ADD endDate INTEGER NULL;

UPDATE jiveNNTPReadStatSession c SET c.creationDate = (SELECT d.creationDate FROM jiveNNTPRSSDateData d  WHERE d.sessionID = c.sessionID);
UPDATE jiveNNTPReadStatSession c SET c.endDate = (SELECT d.endDate FROM jiveNNTPRSSDateData d  WHERE d.sessionID = c.sessionID);

ALTER TABLE jiveNNTPReadStatSession MODIFY (creationDate NOT NULL);

DROP TABLE jiveNNTPRSSDateData;

SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' Finished converting jiveNNTPReadStatSession!') AS msg FROM dual;
COMMIT;


/* Overall end message ------------------------------------------------------ */
SELECT (to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') || ' *** ... ending date conversion.') AS msg FROM dual;