## Jive Forums Upgrade Script - Jive Forums 2.5 or 2.6 -> 3.0## $RCSFile$# $Revision: 1.7 $# $Date: 2003/04/03 20:18:12 $## NEW TABLES## Create the roster tableCREATE TABLE jiveUserRoster (  userID        BIGINT NOT NULL,  subUserID     BIGINT NOT NULL,  PRIMARY KEY   (userID, subUserID));# Create the read tracker tableCREATE TABLE jiveReadTracker (  userID            BIGINT NOT NULL,  objectType        INT NOT NULL,  objectID          BIGINT NOT NULL,  readDate          VARCHAR(15) NOT NULL,  PRIMARY KEY       (userID, objectType, objectID));# Add additional new reward tableCREATE TABLE jiveUserReward (  userID        BIGINT NOT NULL,  rewardPoints  INT NOT NULL,  PRIMARY KEY   (userID, rewardPoints));## ALTER TABLES## Change all 'modifiedDate' columns to 'modificationDate'ALTER TABLE jiveForum CHANGE modifiedDate modificationDate VARCHAR(15) NOT NULL;ALTER TABLE jiveThread CHANGE modifiedDate modificationDate VARCHAR(15) NOT NULL;ALTER TABLE jiveMessage CHANGE modifiedDate modificationDate VARCHAR(15) NOT NULL;ALTER TABLE jiveUser CHANGE modifiedDate modificationDate VARCHAR(15) NOT NULL;ALTER TABLE jiveGroup CHANGE modifiedDate modificationDate VARCHAR(15) NOT NULL;ALTER TABLE jiveAttachment CHANGE modifiedDate modificationDate VARCHAR(15) NOT NULL;ALTER TABLE jiveCategory CHANGE modifiedDate modificationDate VARCHAR(15) NOT NULL;# The 'name' column in jiveForum is no longer unique, so redefine it as such.ALTER TABLE jiveForum MODIFY name VARCHAR(255) NOT NULL;# Migrate the rewardPoint column out of the jiveUser tableINSERT INTO jiveUserReward (userID, rewardPoints)    SELECT userID, rewardPoints FROM jiveUser WHERE rewardPoints > 0;ALTER TABLE jiveUser DROP rewardPoints;# New indexesCREATE INDEX jiveThread_fID_mV_idx ON jiveThread (forumID, modValue);CREATE INDEX jiveMessage_forumID_modVal_idx ON jiveMessage (forumID, modValue);# Migrate the jiveUserPerm and jiveGroupPerm table changes. Update the perm values first then# 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;# Temp table to hold interim perm valuesCREATE TABLE t_jiveUserPerm (  objectType   INT NOT NULL,  objectID     BIGINT NOT NULL,  userID       BIGINT NOT NULL,  permission   INT NOT NULL);# User system-level permsINSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 17, -1, userID, permission FROM jiveUserPerm WHERE forumID IS NULL AND categoryID IS NULL AND userType=3;# Reg user system-level permsINSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 17, -1, 0, permission FROM jiveUserPerm WHERE forumID IS NULL AND categoryID IS NULL AND userType=12;# Guest system-level permsINSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 17, -1, -1, permission FROM jiveUserPerm WHERE forumID IS NULL AND categoryID IS NULL AND userType=11;# User forum-level permsINSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 0, forumID, userID, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=3;# Reg user forum-level permsINSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 0, forumID, 0, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=12;# Guest forum-level permsINSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 0, forumID, -1, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=11;# User category-level permsINSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 14, categoryID, userID, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=3;# Reg user category-level permsINSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 14, categoryID, 0, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=12;# Guest category-level permsINSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 14, categoryID, -1, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=11;# Drop old jiveUserPerms tableDROP TABLE jiveUserPerm;# Create new jievUserPerm tableCREATE TABLE jiveUserPerm (    objectType  INT NOT NULL,    objectID    BIGINT NOT NULL,    userID      BIGINT NOT NULL,    permission  INT NOT NULL,    INDEX jiveUserPerm_object_idx (objectType, objectID),    INDEX jiveUserPerm_userID_idx (userID));# Dump data from temp db into the new one:INSERT INTO jiveUserPerm (objectType, objectID, userID, permission)    SELECT objectType, objectID, userID, permission FROM t_jiveUserPerm;# Drop the temporary perm tableDROP TABLE t_jiveUserPerm;# Temp table to hold interim perm valuesCREATE TABLE t_jiveGroupPerm (  objectType   INT NOT NULL,  objectID     BIGINT NOT NULL,  groupID      BIGINT NOT NULL,  permission   INT NOT NULL);# Group system-level permsINSERT INTO t_jiveGroupPerm (objectType, objectID, groupID, permission)    SELECT 17, -1, groupID, permission FROM jiveGroupPerm WHERE forumID IS NULL AND categoryID IS NULL;# Group category-level permsINSERT INTO t_jiveGroupPerm (objectType, objectID, groupID, permission)    SELECT 14, categoryID, groupID, permission FROM jiveGroupPerm WHERE categoryID IS NOT NULL;# Group forum-level permsINSERT INTO t_jiveGroupPerm (objectType, objectID, groupID, permission)    SELECT 0, forumID, groupID, permission FROM jiveGroupPerm WHERE forumID IS NOT NULL;# Drop old jiveUserPerms tableDROP TABLE jiveGroupPerm;# Create new one:CREATE TABLE jiveGroupPerm (  objectType   INT NOT NULL,  objectID     BIGINT NOT NULL,  groupID      BIGINT NOT NULL,  permission   INT NOT NULL,  INDEX jiveGroupPerm_object_idx (objectType, objectID),  INDEX jiveGroupPerm_groupID_idx  (groupID));# Dump data from temp db into the new one:INSERT INTO jiveGroupPerm (objectType, objectID, groupID, permission)    SELECT objectType, objectID, groupID, permission FROM t_jiveGroupPerm;# Drop the temporary perm tableDROP TABLE t_jiveGroupPerm;# Migrate the watch table# Temp table to hold interim watch valuesCREATE TABLE t_jiveWatch (  userID          BIGINT NOT NULL,  objectID        BIGINT NOT NULL,  objectType      BIGINT NOT NULL,  watchType       INT NOT NULL,  expirable       INT NOT NULL);# Create copies of the jiveWatch table using the jive2 jiveWatch table defCREATE TABLE jiveWatch0 (  userID            BIGINT NOT NULL,  forumID           BIGINT NULL,  threadID          BIGINT NULL,  watchType         BIGINT NOT NULL,  expirable         BIGINT NOT NULL);CREATE TABLE jiveWatch1 (  userID            BIGINT NOT NULL,  forumID           BIGINT NULL,  threadID          BIGINT NULL,  watchType         BIGINT NOT NULL,  expirable         BIGINT NOT NULL);# special queries to seqment watch data into 2 types - normal watches# and email watches.INSERT INTO jiveWatch0 (userID, forumID, threadID, watchType, expirable)    SELECT userID, forumID, threadID, watchType, expirable FROM jiveWatch        WHERE watchType=0;INSERT INTO jiveWatch1 (userID, forumID, threadID, watchType, expirable)    SELECT userID, forumID, threadID, watchType, expirable FROM jiveWatch        WHERE watchType=1;# Load the t_jiveWatch table with a dump from jiveWatch1 (all email-# notified watches)INSERT INTO t_jiveWatch (userID, objectID, objectType, watchType, expirable)    SELECT userID, threadID, 1, 1, expirable FROM jiveWatch1;# Load the t_jiveWatch table with a join of the jiveWatch0 and jiveWatch1# tables. This represents the set of just normal watches that do not have# corresponding email watches.INSERT INTO t_jiveWatch (userID, objectID, objectType, watchType, expirable)    SELECT w0.userID, w0.threadID, 1 objectType, 0 watchType, w0.expirable        FROM jiveWatch0 w0 LEFT JOIN jiveWatch1 w1 USING (threadID, userID)            WHERE w1.threadID IS NULL GROUP BY w0.threadID;# drop the old jiveWatch table and copies of itDROP TABLE jiveWatch;DROP TABLE jiveWatch0;DROP TABLE jiveWatch1;# create the new one:CREATE TABLE jiveWatch (  userID          BIGINT NOT NULL,  objectID        BIGINT NOT NULL,  objectType      BIGINT NOT NULL,  watchType       INT NOT NULL,  expirable       INT NOT NULL,  PRIMARY KEY (userID, objectID, objectType, watchType),  INDEX jiveWatch_userID_idx (userID),  INDEX jiveWatch_objectID_idx (objectID),  INDEX jiveWatch_objectType_idx (objectType));# dump data from the temp file to the new one:INSERT INTO jiveWatch (userID, objectID, objectType, watchType, expirable)    SELECT userID, objectID, objectType, watchType, expirable FROM t_jiveWatch;# drop the temp tableDROP TABLE t_jiveWatch;## KEYS## Remove the unique key on a forum name:ALTER TABLE jiveForum DROP INDEX name;# Add a key on the password hash of jiveUserALTER TABLE jiveUser ADD INDEX jiveUser_hash_idx (passwordHash);# Get rid of foreign keys pointing into the user and group tables# NONE FOR MYSQL