REM /////////////////////////////////////////////////////////////////
REM // $RCSfile$
REM // $Revision: 13292 $
REM // $Date: 2004-12-13 09:18:45 -0800 (Mon, 13 Dec 2004) $
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 135 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 // Do 2.2 -> 2.5 first.


CREATE TABLE jiveAttachmentProp (
  attachmentID  INTEGER NOT NULL,
  name          VARCHAR2(100) NOT NULL,
  propValue     VARCHAR2(4000) NOT NULL,
  CONSTRAINT jiveAttachmentProp_pk PRIMARY KEY   (attachmentID,name)
);

CREATE TABLE jiveCategory (
  categoryID        INTEGER NOT NULL,
  name              VARCHAR2(255) NOT NULL,
  description       VARCHAR2(4000) NULL,
  creationDate      VARCHAR2(15) NOT NULL,
  modifiedDate      VARCHAR2(15) NOT NULL,
  lft               INTEGER NOT NULL,
  rgt               INTEGER NOT NULL,
  CONSTRAINT jiveCategory_pk PRIMARY KEY (categoryID)
);
CREATE INDEX jiveCategory_lft_idx ON jiveCategory (lft);
CREATE INDEX jiveCategory_rgt_idx ON jiveCategory (rgt);

CREATE TABLE jiveCategoryProp (
  categoryID        INTEGER NOT NULL,
  name              VARCHAR2(100) NOT NULL,
  propValue         VARCHAR2(4000) NOT NULL,
  CONSTRAINT jiveCategoryProp_pk PRIMARY KEY (categoryID,name)
);

INSERT INTO jiveCategory VALUES(1, 'root', '', '0', '0', 1, 2);

INSERT INTO jiveID(idType, id) VALUES(14, 2);

ALTER TABLE jiveForum ADD (categoryID INTEGER DEFAULT 1);
ALTER TABLE jiveForum ADD (categoryIndex INTEGER DEFAULT 0);
CREATE INDEX jiveForum_cat_idx ON jiveForum (categoryID);

ALTER TABLE jiveUserPerm ADD (categoryID INTEGER NULL);
CREATE INDEX jiveUserPerm_cat_idx ON jiveUserPerm (categoryID);

ALTER TABLE jiveGroupPerm ADD (categoryID INTEGER NULL);
CREATE INDEX jiveGroupPerm_cat_idx ON jiveGroupPerm (categoryID);


REM // Do 2.5 -> 3.0 next.

ALTER TABLE jiveMessage RENAME COLUMN modifiedDate TO modificationDate;


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 indexes
CREATE INDEX jiveThread_fID_mV_idx ON jiveThread (forumID, modValue);

REM // Migrate the jiveUserPerm and jiveGroupPerm table changes. Update the perm values first then