jive_forums_oracle_upgrade.sql

jive3论坛开源 最新 有版主功能 jive3论坛开源 最新 有版主功能 jive3论坛开源 最新 有版主功能

REM /////////////////////////////////////////////////////////////////
REM // $RCSfile: jive_forums_oracle_upgrade.sql,v $
REM // $Revision: 1.9 $
REM // $Date: 2003/05/14 13:38:50 $
REM /////////////////////////////////////////////////////////////////


REM /////////////////////////////////////////////////////////////////
REM //                                                             //
REM //    YOU MUST READ THIS SECTION BEFORE RUNNING THIS SCRIPT    //
REM //                                                             //
REM /////////////////////////////////////////////////////////////////


REM // There are 2 things you *must* do before running this script.
REM //
REM //  1) Back up your data. This is always a very safe thing to do
REM //     and a good idea in general. If errors occur upgrading
REM //     your data, you can always fall back.
REM //     A common Oracle utility for this is 'exp' - please
REM //     consult your Oracle DBA or read the Oracle documentation
REM //     for this.
REM //
REM //  2) Edit this script to be specific to your environment. See
REM //     the "DATABASE CONNECTION STRING" section below for full
REM //     instructions (approx. line 85 of this file).
REM //
REM //  Other notes:
REM //
REM //   1) Depending on the size of your data this script may require
REM //      a large rollback segment allocation. Please consult with
REM //      your DBA about this before doing this upgrade.
REM //   2) Consult with your DBA about the values of the LONG and
REM //      LONGCHUNKSIZE below. Most DB's should be able to handle
REM //      the default values.


REM // Set the LONG and LONGCHUNKSIZE parameters big enough so we
REM // can read in jiveMessage.body contents correctly:

SET LONG 1024000
SET LONGCHUNKSIZE 1024000


REM // Upgrade the jiveMessage table

ALTER TABLE jiveMessage RENAME TO jiveMessage_temp;

CREATE TABLE jiveMessage (
  messageID         INTEGER NOT NULL,
  parentMessageID   INTEGER NULL,
  threadID          INTEGER NOT NULL,
  forumID           INTEGER NOT NULL,
  userID            INTEGER NULL,
  subject           VARCHAR2(255) NULL,
  body              LONG NULL,
  modValue          INTEGER NOT NULL,
  rewardPoints      INTEGER NOT NULL,
  creationDate      VARCHAR2(15) NOT NULL,
  modificationDate  VARCHAR2(15) NOT NULL,
  CONSTRAINT jiveMsg3_pk PRIMARY KEY (messageID)
);
CREATE INDEX jiveMsg3_forumID_idx ON jiveMessage (forumID ASC);
CREATE INDEX jiveMsg3_threadID_idx ON jiveMessage (threadID ASC);
CREATE INDEX jiveMsg3_userID_idx ON jiveMessage (userID ASC);
CREATE INDEX jiveMsg3_forumId_modVal_idx ON jiveMessage(forumID, modValue);
CREATE INDEX jiveMsg3_cDate_idx ON jiveMessage (creationDate ASC);
CREATE INDEX jiveMsg3_mDate_idx ON jiveMessage (modificationDate DESC);

REM // DATABASE CONNECTION STRING
REM //
REM // THE FOLLOWING TOKENS MUST BE REPLACED IN ORDER FOR THIS SCRIPT TO WORK
REM // CORRECTLY.
REM //
REM // TOKENS TO REPLACE: (REPLACE THE TOKEN AND THE BRACKET [] CHARACTERS)
REM //
REM // [USERNAME] = The user you use to connect to this schema, ie "scott".
REM // [PASSWORD] = The password for your user, ie "tiger"
REM // [SID]      = The database name (SID - service ID) for this database.
REM //
REM // After you replace the tokens, the "COPY" line should look something like:
REM //
REM // COPY FROM scott/tiger@ORCL -

COPY FROM [USERNAME]/[PASSWORD]@[SID] -
    INSERT jiveMessage (messageID, parentMessageID, threadID, forumID, userID, subject, body, -
                        modValue, rewardPoints, creationDate, modificationDate) -
    USING SELECT messageID, parentMessageID, threadID, forumID, userID, subject, body, -
                 modValue, rewardPoints, creationDate, modifiedDate -
          FROM jiveMessage_temp;

DROP TABLE jiveMessage_temp CASCADE CONSTRAINTS;

COMMIT;


REM // Create new Jive 3 tables

CREATE TABLE jiveUserReward (
  userID        INTEGER NOT NULL,
  rewardPoints  INTEGER NOT NULL,
  CONSTRAINT jiveUserReward_pk PRIMARY KEY (userID, rewardPoints)
);


CREATE TABLE jiveUserRoster (
  userID        INTEGER NOT NULL,
  subUserID     INTEGER NOT NULL,
  CONSTRAINT jiveUserRoster_pk PRIMARY KEY (userID, subUserID)
);


CREATE TABLE jiveReadTracker (
  userID            INTEGER NOT NULL,
  objectType        INTEGER NOT NULL,
  objectID          INTEGER NOT NULL,
  readDate          VARCHAR(15) NOT NULL,
  CONSTRAINT jiveReadTracker_pk PRIMARY KEY (userID, objectType, objectID)
);


REM // Rename 'modifiedDate' columns to 'modificationDate'. Do this via the following steps:
REM // 1) Rename the table name
REM // 2) Create the new table, use an embedded select statement from the old table to copy data.
REM // 3) Drop the old table

ALTER TABLE jiveForum RENAME TO jiveForum_temp;
CREATE TABLE jiveForum (
    forumID,
    name,
    description,
    modDefaultThreadVal,
    modMinThreadVal,
    modDefaultMsgVal,
    modMinMsgVal,
    creationDate,
    modificationDate,
    categoryID,
    categoryIndex
) AS SELECT forumID, name, description, modDefaultThreadVal, modMinThreadVal, modDefaultMsgVal,
            modMinMsgVal, creationDate, modifiedDate, categoryID, categoryIndex
     FROM jiveForum_temp;
DROP TABLE jiveForum_temp CASCADE CONSTRAINTS;

COMMIT;

ALTER TABLE jiveThread RENAME TO jiveThread_temp;
CREATE TABLE jiveThread (
    threadID,
    forumID,
    rootMessageID,
    modValue,
    rewardPoints,
    creationDate,
    modificationDate
) AS SELECT threadID, forumID, rootMessageID, modValue, rewardPoints, creationDate, modifiedDate
     FROM jiveThread_temp;
DROP TABLE jiveThread_temp CASCADE CONSTRAINTS;

COMMIT;

ALTER TABLE jiveUser RENAME TO jiveUser_temp;
CREATE TABLE jiveUser (
    userID,
    username,
    passwordHash,
    name,
    nameVisible,
    email,
    emailVisible,
    creationDate,
    modificationDate
) AS SELECT userID, username, passwordHash, name, nameVisible, email, emailVisible,
            creationDate, modifiedDate
     FROM jiveUser_temp;
     
REM // Migrate the rewardPoint column out of the jiveUser table

INSERT INTO jiveUserReward (userID, rewardPoints) 
    SELECT userID, rewardPoints FROM jiveUser_temp WHERE rewardPoints > 0;

REM // Drop the temporary table
    
DROP TABLE jiveUser_temp CASCADE CONSTRAINTS;

COMMIT;

ALTER TABLE jiveGroup RENAME TO jiveGroup_temp;
CREATE TABLE jiveGroup (
    groupID,
    name,
    description,
    creationDate,
    modificationDate
) AS SELECT groupID, name, description, creationDate, modifiedDate
     FROM jiveGroup_temp;
DROP TABLE jiveGroup_temp CASCADE CONSTRAINTS;

COMMIT;

ALTER TABLE jiveAttachment RENAME TO jiveAttachment_temp;
CREATE TABLE jiveAttachment (
    attachmentID,
    messageID,
    fileName,
    fileSize,
    contentType,
    creationDate,
    modificationDate
) AS SELECT attachmentID, messageID, fileName, fileSize, contentType, creationDate, modifiedDate
     FROM jiveAttachment_temp;
DROP TABLE jiveAttachment_temp CASCADE CONSTRAINTS;

COMMIT;

ALTER TABLE jiveCategory RENAME TO jiveCategory_temp;
CREATE TABLE jiveCategory (
    categoryID,
    name,
    description,
    creationDate,
    modificationDate,
    lft,
    rgt
) AS SELECT categoryID, name, description, creationDate, modifiedDate, lft, rgt
     FROM jiveCategory_temp;
DROP TABLE jiveCategory_temp CASCADE CONSTRAINTS;

COMMIT;

REM New index
CREATE INDEX jiveThread_fID_mV_idx ON jiveThread (forumID, modValue);