#
# Jive Forums Upgrade Script - Jive Forums 2.5 or 2.6 -> 3.0
#
# $RCSFile$
# $Revision: 11859 $
# $Date: 2004-09-21 12:30:18 -0700 (Tue, 21 Sep 2004) $


#
# NEW TABLES
#

# Create the roster table

CREATE TABLE jiveUserRoster (
  userID        BIGINT NOT NULL,
  subUserID     BIGINT NOT NULL,
  PRIMARY KEY   (userID, subUserID)
);

# Create the read tracker table

CREATE 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 table

CREATE 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 table

INSERT INTO jiveUserReward (userID, rewardPoints)
    SELECT userID, rewardPoints FROM jiveUser WHERE rewardPoints > 0;

ALTER TABLE jiveUser DROP rewardPoints;

# New indexes
CREATE 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 values
CREATE TABLE t_jiveUserPerm (
  objectType   INT NOT NULL,
  objectID     BIGINT NOT NULL,
  userID       BIGINT NOT NULL,
  permission   INT NOT NULL
);

# 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;
# 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;
# 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;

# 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;
# 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;
# 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;

# 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;
# 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;
# 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;

# Drop old jiveUserPerms table
DROP TABLE jiveUserPerm;

# Create new jievUserPerm table
CREATE 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 table
DROP TABLE t_jiveUserPerm;

# Temp table to hold interim perm values
CREATE TABLE t_jiveGroupPerm (
  objectType   INT NOT NULL,
  objectID     BIGINT NOT NULL,
  groupID      BIGINT NOT NULL,
  permission   INT NOT NULL
);

# Group 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;

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

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

# Drop old jiveUserPerms table
DROP 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 table
DROP TABLE t_jiveGroupPerm;

# Migrate the watch table

# Temp table to hold interim watch values
CREATE 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 def
CREATE 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 it
DROP 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 table
DROP 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 jiveUser
ALTER TABLE jiveUser ADD INDEX jiveUser_hash_idx (passwordHash);

# Get rid of foreign keys pointing into the user and group tables
# NONE FOR MYSQL