modificationDate) AS SELECT attachmentID, messageID, fileName, fileSize, contentType, creationDate, modifiedDate     FROM jiveAttachment_temp;DROP TABLE jiveAttachment_temp CASCADE CONSTRAINTS;COMMIT;ALTER TABLE jiveCategory RENAME TO jiveCategory_temp;CREATE TABLE jiveCategory (    categoryID,    name,    description,    creationDate,    modificationDate,    lft,    rgt) AS SELECT categoryID, name, description, creationDate, modifiedDate, lft, rgt     FROM jiveCategory_temp;DROP TABLE jiveCategory_temp CASCADE CONSTRAINTS;COMMIT;REM New indexesCREATE INDEX jiveThread_fID_mV_idx ON jiveThread (forumID, modValue);REM // Migrate the jiveUserPerm and jiveGroupPerm table changes. Update the perm values first thenREM // 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 valuesCREATE 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 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;REM // 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;REM // 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;REM // 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;REM // 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;REM // 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;REM // 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;REM // 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;REM // 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;COMMIT;    REM // Drop old jiveUserPerms tableDROP TABLE jiveUserPerm;REM // Create new jievUserPerm tableCREATE TABLE jiveUserPerm (    objectType,    objectID,    userID,    permission) AS SELECT objectType, objectID, userID, permission FROM jiveUserPerm_temp;DROP TABLE jiveUserPerm_temp;COMMIT;REM // Group permsREM // Temp table to hold interim perm valuesCREATE 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 permsINSERT 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 permsINSERT INTO jiveGroupPerm_temp (objectType, objectID, groupID, permission)    SELECT 14, categoryID, groupID, permission FROM jiveGroupPerm WHERE categoryID IS NOT NULL;REM // Group forum-level permsINSERT INTO jiveGroupPerm_temp (objectType, objectID, groupID, permission)    SELECT 0, forumID, groupID, permission FROM jiveGroupPerm WHERE forumID IS NOT NULL;REM // Drop old jiveUserPerms tableDROP 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 watchesREM // Temp table to hold interim watch valuesCREATE 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 defCREATE 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 watchesREM // 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 jiveWatch1REM // tables. This represents the set of just normal watches that do not haveREM // 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 itDROP 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 tableDROP TABLE t_jiveWatch;COMMIT;REM // IndexesREM // Add a key on the password hash of jiveUserCREATE INDEX jiveUser_hash_idx ON jiveUser (passwordHash);REM // Remove foreign keys that are no longer neededREM // Not needed