jive_forums_oracle_date_conversion.sql

Jive Forums 论坛源码 v4.2.3

/*
 * $RCSfile$
 * $Revision: 16738 $
 * $Date: 2005-06-29 14:09:19 -0700 (Wed, 29 Jun 2005) $
 *
 * Copyright (C) 1999-2005 Jive Software. All rights reserved. This software is the proprietary
 * information of Jive Software. Use is subject to license terms.
 *
 * Jive Forums Upgrade Script - Converts string dates to BIGINT's. Please read the
 * README.txt in this directory for more information.
 */

/*
 * This script converts JF 4.0 date columns (varchars) to JF 4.1 date columns (numbers).
 *
 * NOTE: You must run this script via the SQLPLUS utility because there is embedded PL/SQL used.
 *
 * Also note, you do not need to run this if you installed JF 4.0 as a *new* installation (not
 * an upgrade). If you are upgrading your system from a release prior to the 4.0 series,
 * make sure you run the other database upgrade scripts first. Again, please read the
 * README.txt in this directory for more information.
 */
 

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


/* -------------------------------------------------------------------------- */
/* jiveCategory conversion -------------------------------------------------- */
/* -------------------------------------------------------------------------- */

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

CREATE TABLE jiveCategoryDateData (
    categoryID,
    creationDate,
    modificationDate,
    CONSTRAINT jiveCategoryDateData_pk PRIMARY KEY (categoryID)
) AS SELECT categoryID, CAST(creationDate AS INTEGER), CAST(modificationDate AS INTEGER)
    FROM jiveCategory;

ALTER TABLE jiveCategory DROP COLUMN creationDate;
ALTER TABLE jiveCategory DROP COLUMN modificationDate;

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

UPDATE jiveCategory c SET c.creationDate = (SELECT d.creationDate FROM jiveCategoryDateData d  WHERE d.categoryID = c.categoryID);
UPDATE jiveCategory c SET c.modificationDate  = (SELECT d.modificationDate FROM jiveCategoryDateData d  WHERE d.categoryID = c.categoryID);

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

DROP TABLE jiveCategoryDateData;

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


/* -------------------------------------------------------------------------- */
/* jiveForum conversion ----------------------------------------------------- */
/* -------------------------------------------------------------------------- */

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

CREATE TABLE jiveForumDateData (
    forumID,
    creationDate,
    modificationDate,
    CONSTRAINT jiveForumDateData_pk PRIMARY KEY (forumID)
) AS SELECT forumID, CAST(creationDate AS INTEGER), CAST(modificationDate AS INTEGER)
    FROM jiveForum;

ALTER TABLE jiveForum DROP COLUMN creationDate;
ALTER TABLE jiveForum DROP COLUMN modificationDate;

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

UPDATE jiveForum c SET c.creationDate = (SELECT d.creationDate FROM jiveForumDateData d  WHERE d.forumID = c.forumID);
UPDATE jiveForum c SET c.modificationDate  = (SELECT d.modificationDate FROM jiveForumDateData d  WHERE d.forumID = c.forumID);

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

DROP TABLE jiveForumDateData;

CREATE INDEX jiveForum_cDate_idx ON jiveForum (creationDate);
CREATE INDEX jiveForum_mDate_idx ON jiveForum (modificationDate);

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


/* -------------------------------------------------------------------------- */
/* jiveThread conversion ---------------------------------------------------- */
/* -------------------------------------------------------------------------- */

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

CREATE TABLE jiveThreadDateData (
    threadID,
    creationDate,
    modificationDate,
    CONSTRAINT jiveThreadDateData_pk PRIMARY KEY (threadID)
) AS SELECT threadID, CAST(creationDate AS INTEGER), CAST(modificationDate AS INTEGER)
    FROM jiveThread;

ALTER TABLE jiveThread DROP COLUMN creationDate;
ALTER TABLE jiveThread DROP COLUMN modificationDate;

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

UPDATE jiveThread c SET c.creationDate = (SELECT d.creationDate FROM jiveThreadDateData d  WHERE d.threadID = c.threadID);
UPDATE jiveThread c SET c.modificationDate  = (SELECT d.modificationDate FROM jiveThreadDateData d  WHERE d.threadID = c.threadID);

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

DROP TABLE jiveThreadDateData;

CREATE INDEX jiveThread_cDate_idx ON jiveThread (creationDate ASC);
CREATE INDEX jiveThread_mDate_idx ON jiveThread (modificationDate DESC);

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


/* -------------------------------------------------------------------------- */
/* jiveMessage conversion --------------------------------------------------- */
/* -------------------------------------------------------------------------- */

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


declare

    stmt        varchar2(200);
    c           number;
    d           number;
    id_tab      dbms_sql.Number_Table;
    create_tab  dbms_sql.Number_Table;
    modify_tab  dbms_sql.Number_Table;

    indx        number := 10000;

begin

    c := dbms_sql.open_cursor;
    dbms_sql.parse(c, 'SELECT messageID, creationDate, modificationDate from jiveMessage order by 1', dbms_sql.native);

    dbms_sql.define_array(c, 1, id_tab, 10000, indx);
    dbms_sql.define_array(c, 2, create_tab, 10000, indx);
    dbms_sql.define_array(c, 3, modify_tab, 10000, indx);

    d := dbms_sql.execute(c);

    loop
        d := dbms_sql.fetch_rows(c);
        dbms_sql.column_value(c, 1, id_tab);
        dbms_sql.column_value(c, 2, create_tab);
        dbms_sql.column_value(c, 3, modify_tab);

        exit when d != 10000;
    end loop;

    execute immediate 'ALTER TABLE jiveMessage DROP COLUMN creationDate';
    execute immediate 'ALTER TABLE jiveMessage DROP COLUMN modificationDate';

    execute immediate 'ALTER TABLE jiveMessage ADD creationDate INTEGER NULL';
    execute immediate 'ALTER TABLE jiveMessage ADD modificationDate INTEGER NULL';

    stmt := 'UPDATE jiveMessage set creationDate = :bnd1, modificationDate = :bnd2 where messageID = :bnd3';
    dbms_sql.parse(c, stmt, dbms_sql.native);
    dbms_sql.bind_array(c, 'bnd1', create_tab);
    dbms_sql.bind_array(c, 'bnd2', modify_tab);
    dbms_sql.bind_array(c, 'bnd3', id_tab);
    d := dbms_sql.execute(c);

    dbms_sql.close_cursor(c);

exception when others then

    if dbms_sql.is_open(c) then
        dbms_sql.close_cursor(c);
    end if;
    raise;

end;
/

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

CREATE INDEX jiveMessage_cDate_idx ON jiveMessage (creationDate ASC);
CREATE INDEX jiveMessage_mDate_idx ON jiveMessage (modificationDate DESC);

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

/* Alternate jiveMessage upgrade -- use this incase the jiveMessage table code above fails. */
/*
CREATE TABLE jiveMessageDateData (
    messageID,
    creationDate,
    modificationDate,
    CONSTRAINT jiveMessageDateData_pk PRIMARY KEY (messageID)
) AS SELECT messageID, CAST(creationDate AS INTEGER), CAST(modificationDate AS INTEGER)
    FROM jiveMessage;

ALTER TABLE jiveMessage DROP COLUMN creationDate;
ALTER TABLE jiveMessage DROP COLUMN modificationDate;

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

UPDATE jiveMessage c SET c.creationDate = (SELECT d.creationDate FROM jiveMessageDateData d  WHERE d.messageID = c.messageID);
UPDATE jiveMessage c SET c.modificationDate  = (SELECT d.modificationDate FROM jiveMessageDateData d  WHERE d.messageID = c.messageID);

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

DROP TABLE jiveMessageDateData;

CREATE INDEX jiveMessage_cDate_idx ON jiveMessage (creationDate ASC);
CREATE INDEX jiveMessage_mDate_idx ON jiveMessage (modificationDate DESC);
*/



/* -------------------------------------------------------------------------- */
/* jiveUser conversion ------------------------------------------------------ */
/* -------------------------------------------------------------------------- */

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

declare

    stmt        varchar2(200);
    c           number;
    d           number;
    id_tab      dbms_sql.Number_Table;
    create_tab  dbms_sql.Number_Table;
    modify_tab  dbms_sql.Number_Table;

    indx        number := 10000;

begin

    c := dbms_sql.open_cursor;
    dbms_sql.parse(c, 'SELECT userID, creationDate, modificationDate from jiveUser order by 1', dbms_sql.native);

    dbms_sql.define_array(c, 1, id_tab, 10000, indx);
    dbms_sql.define_array(c, 2, create_tab, 10000, indx);
    dbms_sql.define_array(c, 3, modify_tab, 10000, indx);

    d := dbms_sql.execute(c);

    loop
        d := dbms_sql.fetch_rows(c);
        dbms_sql.column_value(c, 1, id_tab);
        dbms_sql.column_value(c, 2, create_tab);
        dbms_sql.column_value(c, 3, modify_tab);

        exit when d != 10000;
    end loop;

    execute immediate 'ALTER TABLE jiveUser DROP COLUMN creationDate';
    execute immediate 'ALTER TABLE jiveUser DROP COLUMN modificationDate';

    execute immediate 'ALTER TABLE jiveUser ADD creationDate INTEGER NULL';
    execute immediate 'ALTER TABLE jiveUser ADD modificationDate INTEGER NULL';

    stmt := 'UPDATE jiveUser set creationDate = :bnd1, modificationDate = :bnd2 where userID = :bnd3';
    dbms_sql.parse(c, stmt, dbms_sql.native);
    dbms_sql.bind_array(c, 'bnd1', create_tab);
    dbms_sql.bind_array(c, 'bnd2', modify_tab);
    dbms_sql.bind_array(c, 'bnd3', id_tab);
    d := dbms_sql.execute(c);

    dbms_sql.close_cursor(c);

exception when others then

    if dbms_sql.is_open(c) then
        dbms_sql.close_cursor(c);
    end if;
    raise;

end;
/

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

CREATE INDEX jiveUser_cDate_idx on jiveUser (creationDate ASC);

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

/* Alternate jiveUser upgrade -- use this incase the jiveUser table code above fails. */
/*
CREATE TABLE jiveUserDateData (
    userID,
    creationDate,
    modificationDate,
    CONSTRAINT jiveUserDateData_pk PRIMARY KEY (userID)
) AS SELECT userID, CAST(creationDate AS INTEGER), CAST(modificationDate AS INTEGER)
    FROM jiveUser;

ALTER TABLE jiveUser DROP COLUMN creationDate;
ALTER TABLE jiveUser DROP COLUMN modificationDate;

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

UPDATE jiveUser c SET c.creationDate = (SELECT d.creationDate FROM jiveUserDateData d  WHERE d.userID = c.userID);
UPDATE jiveUser c SET c.modificationDate  = (SELECT d.modificationDate FROM jiveUserDateData d  WHERE d.userID = c.userID);

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

DROP TABLE jiveUserDateData;

CREATE INDEX jiveUser_cDate_idx ON jiveUser (creationDate ASC);
CREATE INDEX jiveUser_mDate_idx ON jiveUser (modificationDate DESC);
*/


/* -------------------------------------------------------------------------- */
/* jiveGroup conversion ----------------------------------------------------- */
/* -------------------------------------------------------------------------- */

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

CREATE TABLE jiveGroupDateData (
    groupID,
    creationDate,
    modificationDate,
    CONSTRAINT jiveGroupDateData_pk PRIMARY KEY (groupID)
) AS SELECT groupID, CAST(creationDate AS INTEGER), CAST(modificationDate AS INTEGER)
    FROM jiveGroup;

ALTER TABLE jiveGroup DROP COLUMN creationDate;
ALTER TABLE jiveGroup DROP COLUMN modificationDate;

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

UPDATE jiveGroup c SET c.creationDate = (SELECT d.creationDate FROM jiveGroupDateData d  WHERE d.groupID = c.groupID);
UPDATE jiveGroup c SET c.modificationDate  = (SELECT d.modificationDate FROM jiveGroupDateData d  WHERE d.groupID = c.groupID);

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

DROP TABLE jiveGroupDateData;

CREATE INDEX jiveGroup_cDate_idx on jiveGroup (creationDate ASC);

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


/* -------------------------------------------------------------------------- */
/* jivePMessage conversion -------------------------------------------------- */
/* -------------------------------------------------------------------------- */

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

CREATE TABLE jivePMessageDateData (
    pMessageID,
    pMessageDate,
    CONSTRAINT jivePMessageDateData_pk PRIMARY KEY (pMessageID)
) AS SELECT pMessageID, CAST(pMessageDate AS INTEGER)
    FROM jivePMessage;

ALTER TABLE jivePMessage DROP COLUMN pMessageDate;

ALTER TABLE jivePMessage ADD pMessageDate INTEGER NULL;

UPDATE jivePMessage c SET c.pMessageDate = (SELECT d.pMessageDate FROM jivePMessageDateData d  WHERE d.pMessageID = c.pMessageID);

ALTER TABLE jivePMessage MODIFY (pMessageDate NOT NULL);

DROP TABLE jivePMessageDateData;