jive_forums_oracle_upgrade_2.2_to_3.0.sql

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

REM /////////////////////////////////////////////////////////////////
REM // $RCSfile: jive_forums_oracle_upgrade_2.2_to_3.0.sql,v $
REM // $Revision: 1.2 $
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 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.



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,