-- //////////////////////////////-- // $RCSfile: jive_forums_postgresql_upgrade.sql,v $-- // $Revision: 1.3 $-- // $Date: 2002/12/15 18:47:56 $-- //////////////////////////////-- // Make sure you backup your database before running this upgrade script.-- // e.g. pg_dump -f jive2.dump jive2-- // Create new Jive 3 tablesCREATE TABLE jiveUserReward (  userID        INT8 NOT NULL,  rewardPoints  INT8 NOT NULL,  CONSTRAINT jiveUserReward_pk PRIMARY KEY (userID, rewardPoints));CREATE TABLE jiveUserRoster (  userID        INT8 NOT NULL,  subUserID     INT8 NOT NULL,  CONSTRAINT jiveUserRoster_pk PRIMARY KEY (userID, subUserID));CREATE TABLE jiveReadTracker (  userID            INT8 NOT NULL,  objectType        INT8 NOT NULL,  objectID          INT8 NOT NULL,  readDate          VARCHAR(15) NOT NULL,  CONSTRAINT jiveReadTracker_pk PRIMARY KEY (userID, objectType, objectID));-- // Rename 'modifiedDate' columns to 'modificationDate'.ALTER TABLE jiveForum RENAME modifiedDate TO modificationDate;ALTER TABLE jiveThread RENAME modifiedDate TO modificationDate;ALTER TABLE jiveMessage RENAME modifiedDate TO modificationDate;ALTER TABLE jiveUser RENAME modifiedDate TO modificationDate;ALTER TABLE jiveGroup RENAME modifiedDate TO modificationDate;ALTER TABLE jiveAttachment RENAME modifiedDate TO modificationDate;ALTER TABLE jiveCategory RENAME modifiedDate TO modificationDate;-- // Migrate the rewardPoint column out of the jiveUser tableINSERT INTO jiveUserReward (userID, rewardPoints)    SELECT userID, rewardPoints FROM jiveUser WHERE rewardPoints > 0;-- // Remove the rewardPoint column out of the jiveUser tableALTER 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, modificationDate     FROM jiveUser_temp;-- // Drop the temporary tableDROP TABLE jiveUser_temp;-- 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;-- // Migrate permission valuesCREATE TABLE jiveUserPerm_temp (  objectType       INT8 NOT NULL,  objectID         INT8 NOT NULL,  userID           INT8 NOT NULL,  permission       INT8 NOT NULL);CREATE INDEX jiveUserPerm_object_idx2 ON jiveUserPerm_temp (objectType, objectID);CREATE INDEX jiveUserPerm_userID_idx2 ON jiveUserPerm_temp (userID);-- // User system-level permsINSERT 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;-- // Reg user system-level permsINSERT 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;-- // Guest system-level permsINSERT 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;-- // User forum-level permsINSERT INTO jiveUserPerm_temp (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 jiveUserPerm_temp (objectType, objectID, userID, permission)    SELECT 0, forumID, 0, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=12;-- // Guest forum-level permsINSERT INTO jiveUserPerm_temp (objectType, objectID, userID, permission)    SELECT 0, forumID, -1, permission FROM jiveUserPerm WHERE forumID IS NOT NULL AND userType=11;-- // User category-level permsINSERT INTO jiveUserPerm_temp (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 jiveUserPerm_temp (objectType, objectID, userID, permission)    SELECT 14, categoryID, 0, permission FROM jiveUserPerm WHERE categoryID IS NOT NULL AND userType=12;-- // Guest category-level permsINSERT INTO jiveUserPerm_temp (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 jiveUserPerm tableCREATE TABLE jiveUserPerm (    objectType,    objectID,    userID,    permission) AS SELECT objectType, objectID, userID, permission FROM jiveUserPerm_temp;DROP TABLE jiveUserPerm_temp;-- // Group perms-- // Temp table to hold interim perm valuesCREATE TABLE jiveGroupPerm_temp (  objectType    INT8 NOT NULL,  objectID      INT8 NOT NULL,  groupID       INT8 NOT NULL,  permission    INT8 NOT NULL);CREATE INDEX jiveGroupPerm_object_idx2 ON jiveGroupPerm_temp (objectType, objectID);CREATE INDEX jiveGroupPerm_groupID_idx2 ON jiveGroupPerm_temp (groupID);-- // Group system-level permsINSERT INTO jiveGroupPerm_temp (objectType, objectID, groupID, permission)    SELECT 17, -1, groupID, permission FROM jiveGroupPerm WHERE forumID IS NULL AND categoryID IS NULL;-- // Group category-level permsINSERT INTO jiveGroupPerm_temp (objectType, objectID, groupID, permission)    SELECT 14, categoryID, groupID, permission FROM jiveGroupPerm WHERE categoryID IS NOT NULL;-- // Group forum-level permsINSERT INTO jiveGroupPerm_temp (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, 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;-- // Migrate watches-- // Temp table to hold interim watch valuesCREATE 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;-- // Indexes-- // Add a key on the password hash of jiveUserCREATE INDEX jiveUser_hash_idx ON jiveUser (passwordHash);-- // Remove foreign keys that are no longer needed-- // Not needed