/** * Jive Forums Upgrade Script - Jive Forums 2.5 or 2.6 -> 3.0 * * $RCSFile$ * $Revision: 1.9.2.1 $ * $Date: 2003/06/05 21:25:54 $ *//* NEW TABLES *//* Create the roster table */CREATE TABLE jiveUserRoster (  userID        INT NOT NULL,  subUserID     INT NOT NULL,  CONSTRAINT roster_pk PRIMARY KEY (userID, subUserID)  );GO/* Create the read tracker table */CREATE TABLE jiveReadTracker (  userID            INT NOT NULL,  objectType        INT NOT NULL,  objectID          INT NOT NULL,  readDate          CHAR(15) NOT NULL,  CONSTRAINT readTracker_pk PRIMARY KEY (userID, objectType, objectID));GO/* Add additional new reward table */CREATE TABLE jiveUserReward (  userID        INT NOT NULL,  rewardPoints  INT NOT NULL,  CONSTRAINT userReward_pk PRIMARY KEY (userID, rewardPoints));GO/* ALTER TABLES *//* Change all 'modifiedDate' columns to 'modificationDate' */EXEC sp_rename 'jiveForum.modifiedDate', 'modificationDate', 'COLUMN'; EXEC sp_rename 'jiveThread.modifiedDate', 'modificationDate', 'COLUMN'; EXEC sp_rename 'jiveMessage.modifiedDate', 'modificationDate', 'COLUMN'; EXEC sp_rename 'jiveUser.modifiedDate', 'modificationDate', 'COLUMN'; EXEC sp_rename 'jiveGroup.modifiedDate', 'modificationDate', 'COLUMN'; EXEC sp_rename 'jiveAttachment.modifiedDate', 'modificationDate', 'COLUMN';EXEC sp_rename 'jiveCategory.modifiedDate', 'modificationDate', 'COLUMN';  GO/* Widen the perm columns to accept longs: *//* Not needed under SQL Server *//* The 'name' column in jiveForum is no longer unique: */ALTER TABLE jiveForum ALTER COLUMN name VARCHAR(255) NOT NULL;GO/* Migrate the rewardPoints column out of the jiveUser table */INSERT INTO jiveUserReward (userID, rewardPoints)SELECT userID, rewardPoints FROM jiveUser WHERE rewardPoints > 0;GOALTER TABLE jiveUser DROP COLUMN rewardPoints;GOCREATE INDEX jiveThread_fID_mV_idx ON jiveThread (forumID, modValue);CREATE INDEX jiveMessage_forumID_idx ON jiveMessage (forumID);GO/* 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;GO/* Temp table to hold interim perm values */CREATE TABLE t_jiveUserPerm (  objectType   INT NOT NULL,  objectID     INT NOT NULL,  userID       INT NOT NULL,  permission   INT NOT NULL);GO/* User system-level perms */INSERT 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;GO/* Reg user system-level perms */INSERT 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;GO/* Guest system-level perms */INSERT 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;GO/* User forum-level perms */INSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 0, forumID, userID, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=3;GO/* Reg user forum-level perms */INSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 0, forumID, 0, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=12;GO/* Guest forum-level perms */INSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 0, forumID, -1, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=11;GO/* User category-level perms */INSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 14, categoryID, userID, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=3;GO/* Reg user category-level perms */INSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 14, categoryID, 0, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=12;GO/* Guest category-level perms */INSERT INTO t_jiveUserPerm (objectType, objectID, userID, permission)    SELECT 14, categoryID, -1, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=11;GO/* Drop old jiveUserPerms table */ALTER TABLE jiveUserPerm DROP CONSTRAINT jiveUserPerm_forumID_fk;GOALTER TABLE jiveUserPerm DROP CONSTRAINT jiveUserPerm_userID_fk;GODROP TABLE jiveUserPerm;GO/* Create new jievUserPerm table */CREATE TABLE jiveUserPerm (  objectType    INT            NOT NULL,  objectID      INT            NOT NULL,  userID        INT            NOT NULL,  permission    INT            NOT NULL,);GOCREATE INDEX jiveUserPerm_object_idx ON jiveUserPerm (objectType, objectID);CREATE INDEX jiveUserPerm_userID_idx ON jiveUserPerm (userID);GO/* Dump data from temp db into the new one: */INSERT INTO jiveUserPerm (objectType, objectID, userID, permission)    SELECT objectType, objectID, userID, permission FROM t_jiveUserPerm;GO/* Drop the temporary perm table */DROP TABLE t_jiveUserPerm;GO/* Temp table to hold interim perm values */CREATE TABLE t_jiveGroupPerm (  objectType   INT NOT NULL,  objectID     INT NOT NULL,  groupID      INT NOT NULL,  permission   INT NOT NULL);GO/* User system-level perms */INSERT INTO t_jiveGroupPerm (objectType, objectID, groupID, permission)    SELECT 17, -1, groupID, permission FROM jiveGroupPerm WHERE forumID IS NULL AND categoryID IS NULL;GO/* User category-level perms */INSERT INTO t_jiveGroupPerm (objectType, objectID, groupID, permission)    SELECT 14, categoryID, groupID, permission FROM jiveGroupPerm WHERE categoryID IS NOT NULL;GO/* User forum-level perms */INSERT INTO t_jiveGroupPerm (objectType, objectID, groupID, permission)    SELECT 0, forumID, groupID, permission FROM jiveGroupPerm WHERE forumID IS NOT NULL;GO/* Drop old jiveUserPerms table */ALTER TABLE jiveGroupPerm DROP CONSTRAINT jiveGroupPerm_groupID_fk;GODROP TABLE jiveGroupPerm;GO/* Create new one: */CREATE TABLE jiveGroupPerm (  objecttype    INT            NOT NULL,  objectID      INT            NOT NULL,  groupID       INT            NOT NULL,  permission    INT            NOT NULL);GOCREATE INDEX jiveGroupPerm_object_idx ON jiveGroupPerm (objectType, objectID);CREATE INDEX jiveGroupPerm_groupID_idx ON jiveGroupPerm (groupID);GO/* Dump data from temp db into the new one: */INSERT INTO jiveGroupPerm (objectType, objectID, groupID, permission)    SELECT objectType, objectID, groupID, permission FROM t_jiveGroupPerm;GO/* Drop the temporary perm table */DROP TABLE t_jiveGroupPerm;GO/* Migrate the watch table *//* Temp table to hold interim watch values */CREATE TABLE t_jiveWatch (  userID          INT NOT NULL,  objectID        INT NOT NULL,  objectType      INT NOT NULL,  watchType       INT NOT NULL,  expirable       INT NOT NULL);GO/* Insert all watches as thread type of watches */INSERT INTO t_jiveWatch (userID, objectID, objectType, watchType, expirable)    SELECT userID, threadID, 1, watchType, expirable FROM jiveWatch;GO/* drop the old jiveWatch table */DROP TABLE jiveWatch;GO/* create the new one: */CREATE TABLE jiveWatch (  userID          INT NOT NULL,  objectType      INT NOT NULL,  objectID        INT NOT NULL,  watchType       INT NOT NULL,  expirable       INT NOT NULL,  CONSTRAINT jiveWatch_pk PRIMARY KEY (userID, objectID, objectType, watchType));GOCREATE INDEX jiveWatch_userID_idx ON jiveWatch (userID);CREATE INDEX jiveWatch_objectID_idx ON jiveWatch (objectID);CREATE INDEX jiveWatch_objectType_idx ON jiveWatch (objectType);GO/* 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;GO/* drop the temp table */DROP TABLE t_jiveWatch;GO/* KEYS *//* Add a key on the password hash of jiveUser */CREATE INDEX jiveUser_hash_idx ON jiveUser (passwordHash);GO/* Get rid of foreign keys that are no longer needed. */alter table jiveMessage	    drop constraint jiveMessage_parentMsgID_fk;alter table jiveMessage	    drop constraint jiveMessage_userID_fk;alter table jiveUserProp    drop constraint jiveUserProp_userID_fk;alter table jiveGroupProp   drop constraint jiveGroupProp_groupID_fk;alter table jiveGroupUser   drop constraint jiveGroupUser_groupID_fk;alter table jiveGroupUser   drop constraint jiveGroupUser_userID_fk;GO