jive_forums_oracle_upgrade.sql

jive的源码

REM //////////////////////////////
REM // $RCSfile: jive_forums_oracle_upgrade.sql,v $
REM // $Revision: 1.4 $
REM // $Date: 2002/12/15 18:47:56 $
REM //////////////////////////////

REM // Depending on the size of your database this script may require a large rollback
REM // segment allocation. Please consult with your DBA when running this script. It is
REM // recommended that you *back up your database* before running this script. Oracle
REM // has a built-in data dump utility called 'exp'. Use exp to do a dump of your current
REM // Jive database before running this script.
REM //
REM // Note, if you have problems running this script due to a very large dataset, consult the
REM // technique specified in this article:
REM // http://gethelp.devx.com/techtips/oracle_pro/10min/10min0502/10min0502.asp
REM // Also, please post your problem on the Jive Software support forums at:
REM // http://www.jivesoftware.com/jive/


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 jiveMessage RENAME TO jiveMessage_temp;
CREATE TABLE jiveMessage (
    messageID,
    parentMessageID,
    threadID,
    forumID,
    userID,
    subject,
    body,
    modValue,
    rewardPoints,
    creationDate,
    modificationDate
) AS SELECT messageID, parentMessageID, threadID, forumID, userID, subject, TO_LOB(body), modValue,
            rewardPoints, creationDate, modifiedDate
     FROM jiveMessage_temp;
DROP TABLE jiveMessage_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);
CREATE INDEX jiveMessage_forumID_idx ON jiveMessage (forumID ASC);

REM // Migrate the jiveUserPerm and jiveGroupPerm table changes. Update the perm values first then
REM // migrate the column names and object values for both tables:



update jiveUserPerm set permission=-1 where permission=1;
update jiveUserPerm set permission=-2 where permission=2;
update jiveUserPerm set permission=-3 where permission=3;
update jiveUserPerm set permission=-4 where permission=4;
update jiveUserPerm set permission=-5 where permission=5;
update jiveUserPerm set permission=-6 where permission=6;
update jiveUserPerm set permission=-7 where permission=7;
update jiveUserPerm set permission=-8 where permission=8;
update jiveUserPerm set permission=-9 where permission=9;
update jiveUserPerm set permission=-10 where permission=10;

update jiveUserPerm set permission=59 where permission=-1;
update jiveUserPerm set permission=8 where permission=-2;
update jiveUserPerm set permission=57 where permission=-3;
update jiveUserPerm set permission=58 where permission=-4;
update jiveUserPerm set permission=7 where permission=-5;
update jiveUserPerm set permission=2 where permission=-6;
update jiveUserPerm set permission=1 where permission=-7;
update jiveUserPerm set permission=6 where permission=-8;
update jiveUserPerm set permission=3 where permission=-9;
update jiveUserPerm set permission=9 where permission=-10;



update jiveGroupPerm set permission=-1 where permission=1;
update jiveGroupPerm set permission=-2 where permission=2;
update jiveGroupPerm set permission=-3 where permission=3;
update jiveGroupPerm set permission=-4 where permission=4;
update jiveGroupPerm set permission=-5 where permission=5;
update jiveGroupPerm set permission=-6 where permission=6;
update jiveGroupPerm set permission=-7 where permission=7;
update jiveGroupPerm set permission=-8 where permission=8;
update jiveGroupPerm set permission=-9 where permission=9;
update jiveGroupPerm set permission=-10 where permission=10;

update jiveGroupPerm set permission=59 where permission=-1;
update jiveGroupPerm set permission=8 where permission=-2;
update jiveGroupPerm set permission=57 where permission=-3;
update jiveGroupPerm set permission=58 where permission=-4;
update jiveGroupPerm set permission=7 where permission=-5;
update jiveGroupPerm set permission=2 where permission=-6;
update jiveGroupPerm set permission=1 where permission=-7;
update jiveGroupPerm set permission=6 where permission=-8;
update jiveGroupPerm set permission=3 where permission=-9;
update jiveGroupPerm set permission=9 where permission=-10;

REM // Migrate permission values

CREATE TABLE jiveUserPerm_temp (
  objectType       INTEGER NOT NULL,
  objectID         INTEGER NOT NULL,
  userID           INTEGER NOT NULL,
  permission       INTEGER NOT NULL
);
CREATE INDEX jiveUserPerm_object_idx2 ON jiveUserPerm_temp (objectType, objectID);
CREATE INDEX jiveUserPerm_userID_idx2 ON jiveUserPerm_temp (userID ASC);

REM // User system-level perms
INSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)
    SELECT 17, -1, userID, permission FROM jiveUserPerm WHERE forumID IS NULL AND categoryID IS NULL AND userType=3;
REM // Reg user system-level perms
INSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)
    SELECT 17, -1, 0, permission FROM jiveUserPerm WHERE forumID IS NULL AND categoryID IS NULL AND userType=12;
REM // Guest system-level perms
INSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)
    SELECT 17, -1, -1, permission FROM jiveUserPerm WHERE forumID IS NULL AND categoryID IS NULL AND userType=11;

REM // User forum-level perms
INSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)
    SELECT 0, forumID, userID, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=3;
REM // Reg user forum-level perms
INSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)
    SELECT 0, forumID, 0, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=12;
REM // Guest forum-level perms
INSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)
    SELECT 0, forumID, -1, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=11;

REM // User category-level perms
INSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)
    SELECT 14, categoryID, userID, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=3;
REM // Reg user category-level perms
INSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)
    SELECT 14, categoryID, 0, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=12;
REM // Guest category-level perms
INSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)
    SELECT 14, categoryID, -1, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=11;

COMMIT;
    
REM // Drop old jiveUserPerms table
DROP TABLE jiveUserPerm;

REM // Create new jievUserPerm table
CREATE TABLE jiveUserPerm (
    objectType,
    objectID,
    userID,
    permission
) AS SELECT objectType, objectID, userID, permission FROM jiveUserPerm_temp;
DROP TABLE jiveUserPerm_temp;

COMMIT;

REM // Group perms

REM // Temp table to hold interim perm values
CREATE TABLE jiveGroupPerm_temp (
  objectType    INTEGER NOT NULL,
  objectID      INTEGER NOT NULL,
  groupID       INTEGER NOT NULL,
  permission    INTEGER NOT NULL
);
CREATE INDEX jiveGroupPerm_object_idx2 ON jiveGroupPerm_temp (objectType, objectID);
CREATE INDEX jiveGroupPerm_groupID_idx2 ON jiveGroupPerm_temp (groupID ASC);

REM // Group system-level perms
INSERT INTO jiveGroupPerm_temp (objectType, objectID, groupID, permission)
    SELECT 17, -1, groupID, permission FROM jiveGroupPerm WHERE forumID IS NULL AND categoryID IS NULL;

REM // Group category-level perms
INSERT INTO jiveGroupPerm_temp (objectType, objectID, groupID, permission)
    SELECT 14, categoryID, groupID, permission FROM jiveGroupPerm WHERE categoryID IS NOT NULL;

REM // Group forum-level perms
INSERT INTO jiveGroupPerm_temp (objectType, objectID, groupID, permission)
    SELECT 0, forumID, groupID, permission FROM jiveGroupPerm WHERE forumID IS NOT NULL;

REM // Drop old jiveUserPerms table
DROP TABLE jiveGroupPerm;

REM // Create new one, load it, drop old one:
CREATE TABLE jiveGroupPerm (
  objectType,
  objectID,
  groupID,
  permission
) AS SELECT objectType, objectID, groupID, permission FROM jiveGroupPerm_temp;
DROP TABLE jiveGroupPerm_temp;


REM // Migrate watches

REM // Temp table to hold interim watch values
CREATE TABLE jiveWatch_temp (
  userID,
  objectID,
  objectType,
  watchType,
  expirable
) AS SELECT userID, threadID, 1, watchType, expirable FROM jiveWatch;
DROP TABLE jiveWatch;
ALTER TABLE jiveWatch_temp RENAME TO jiveWatch;

COMMIT;

REM // Indexes

REM // Add a key on the password hash of jiveUser
CREATE INDEX jiveUser_hash_idx ON jiveUser (passwordHash);

REM // Remove foreign keys that are no longer needed

REM // Not needed