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 doREM //     and a good idea in general. If errors occur upgradingREM //     your data, you can always fall back.REM //     A common Oracle utility for this is 'exp' - pleaseREM //     consult your Oracle DBA or read the Oracle documentationREM //     for this.REM //REM //  2) Edit this script to be specific to your environment. SeeREM //     the "DATABASE CONNECTION STRING" section below for fullREM //     instructions (approx. line 85 of this file).REM //REM //  Other notes:REM //REM //   1) Depending on the size of your data this script may requireREM //      a large rollback segment allocation. Please consult withREM //      your DBA about this before doing this upgrade.REM //   2) Consult with your DBA about the values of the LONG andREM //      LONGCHUNKSIZE below. Most DB's should be able to handleREM //      the default values.REM // Set the LONG and LONGCHUNKSIZE parameters big enough so weREM // can read in jiveMessage.body contents correctly:SET LONG 1024000SET LONGCHUNKSIZE 1024000REM // Upgrade the jiveMessage tableALTER 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 STRINGREM //REM // THE FOLLOWING TOKENS MUST BE REPLACED IN ORDER FOR THIS SCRIPT TO WORKREM // 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 tablesCREATE 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 nameREM // 2) Create the new table, use an embedded select statement from the old table to copy data.REM // 3) Drop the old tableALTER 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 tableINSERT 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 indexCREATE INDEX jiveThread_fID_mV_idx ON jiveThread (forumID, modValue);