REM /////////////////////////////////////////////////////////////////
REM // $RCSfile$
REM // $Revision: 13304 $
REM // $Date: 2004-12-13 11:00:47 -0800 (Mon, 13 Dec 2004) $
REM /////////////////////////////////////////////////////////////////



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'.

DROP INDEX jiveMessage_mDate_idx;
ALTER TABLE jiveMessage RENAME COLUMN modifiedDate TO modificationDate;
CREATE INDEX jiveMessage_mDate_idx ON jiveMessage (modificationDate DESC);

ALTER TABLE jiveForum RENAME COLUMN modifiedDate TO modificationDate;

DROP INDEX jiveThread_mDate_idx;
ALTER TABLE jiveThread RENAME COLUMN modifiedDate TO modificationDate;
CREATE INDEX jiveThread_mDate_idx ON jiveThread (modificationDate DESC);


ALTER TABLE jiveUser RENAME COLUMN modifiedDate TO modificationDate;
ALTER TABLE jiveGroup RENAME COLUMN modifiedDate TO modificationDate;
ALTER TABLE jiveAttachment RENAME COLUMN modifiedDate TO modificationDate;
ALTER TABLE jiveCategory RENAME COLUMN modifiedDate TO modificationDate;


     
REM // 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 COLUMN rewardPoints;

COMMIT;

REM New index
CREATE INDEX jiveThread_fID_mV_idx ON jiveThread (forumID, modValue);

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 t_jiveWatch (
  userID          INTEGER NOT NULL,
  objectID        INTEGER NOT NULL,
  objectType      INTEGER NOT NULL,
  watchType       INTEGER NOT NULL,
  expirable       INTEGER NOT NULL
);

REM // Create copies of the jiveWatch table using the jive2 jiveWatch table def
CREATE TABLE jiveWatch0 (
  userID            INTEGER NOT NULL,
  categoryID        INTEGER NULL,
  forumID           INTEGER NULL,
  threadID          INTEGER NULL,
  watchType         INTEGER NOT NULL,
  expirable         INTEGER NOT NULL 
);
CREATE TABLE jiveWatch1 (
  userID            INTEGER NOT NULL,
  categoryID        INTEGER NULL,
  forumID           INTEGER NULL,
  threadID          INTEGER NULL,
  watchType         INTEGER NOT NULL,
  expirable         INTEGER NOT NULL 
);

REM // special queries to seqment watch data into 2 types - normal watches
REM // and email watches.
INSERT INTO jiveWatch0 (userID, categoryID, forumID, threadID, watchType, expirable)
    SELECT userID, NULL, forumID, threadID, watchType, expirable FROM jiveWatch
        WHERE watchType=0;
INSERT INTO jiveWatch1 (userID, categoryID, forumID, threadID, watchType, expirable)
    SELECT userID, NULL, forumID, threadID, watchType, expirable FROM jiveWatch
        WHERE watchType=1;

REM // Load the t_jiveWatch table with a dump from jiveWatch1 (all email-
REM // notified watches)
INSERT INTO t_jiveWatch (userID, objectID, objectType, watchType, expirable)
    SELECT userID, threadID, 1, 1, expirable FROM jiveWatch1;

REM // Load the t_jiveWatch table with a join of the jiveWatch0 and jiveWatch1
REM // tables. This represents the set of just normal watches that do not have
REM // 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, jiveWatch1 w1
            WHERE w0.threadID=w1.threadID(+) AND w0.userID=w1.userID(+)
                AND w1.threadID IS NULL;

REM // drop the old jiveWatch table and copies of it
DROP TABLE jiveWatch;
DROP TABLE jiveWatch0;
DROP TABLE jiveWatch1;

REM // create the new one:
CREATE TABLE jiveWatch (
  userID          INTEGER NOT NULL,
  objectID        INTEGER NOT NULL,
  objectType      INTEGER NOT NULL,
  watchType       INTEGER NOT NULL,
  expirable       INTEGER NOT NULL,
  CONSTRAINT jiveWatch_pk PRIMARY KEY (userID, objectID, objectType, watchType)
);
CREATE INDEX jiveWatch_userID_idx ON jiveWatch (userID);
CREATE INDEX jiveWatch_objectID_idx ON jiveWatch (objectID);
CREATE INDEX jiveWatch_objectType_idx ON jiveWatch (objectType);

REM // 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;

REM // drop the temp table
DROP TABLE t_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