jive_forums_hsqldb_upgrade.sql

jive3论坛开源 最新 有版主功能 jive3论坛开源 最新 有版主功能 jive3论坛开源 最新 有版主功能

//////////////////////////////////
// $RCSfile: jive_forums_hsqldb_upgrade.sql,v $
// $Revision: 1.3 $
// $Date: 2003/01/16 03:06:22 $
//////////////////////////////////

//
// Note, this script assumes HSQLDB 1.7.1. If you are not running this
// version, please upgrade first.
//

// Create new tables

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

CREATE TABLE jiveReadTracker (
  userID            BIGINT NOT NULL,
  objectType        INTEGER NOT NULL,  
  objectID          BIGINT NOT NULL,
  readDate          VARCHAR(15) NOT NULL,
  PRIMARY KEY       (userID, objectType, objectID)
);

CREATE TABLE jiveUserReward (
  userID        BIGINT NOT NULL,
  rewardPoints  INTEGER NOT NULL,
  CONSTRAINT jiveUserReward_pk PRIMARY KEY (userID, rewardPoints)
);

// Change all 'modifiedDate' columns to 'modificationDate' in all affected tables

ALTER TABLE jiveForum RENAME TO jiveForum_temp;
CREATE TABLE jiveForum (
  forumID               BIGINT NOT NULL,
  name                  VARCHAR(255) NOT NULL,
  description           VARCHAR(4000) NULL,
  modDefaultThreadVal   INTEGER NOT NULL,
  modMinThreadVal       INTEGER NOT NULL,
  modDefaultMsgVal      INTEGER NOT NULL,
  modMinMsgVal          INTEGER NOT NULL,
  creationDate          VARCHAR(15) NOT NULL,
  modificationDate      VARCHAR(15) NOT NULL,
  categoryID            BIGINT NOT NULL,
  categoryIndex         INTEGER NOT NULL,
  CONSTRAINT jiveForum_pk PRIMARY KEY (forumID)
);
CREATE INDEX jiveForum_cat_idx2 ON jiveForum (categoryID);
INSERT INTO jiveForum(forumID,name,description,modDefaultThreadVal,modMinThreadVal,
        modDefaultMsgVal,modMinMsgVal,creationDate,modificationDate,categoryID,categoryIndex)
    SELECT forumID,name,description,modDefaultThreadVal,modMinThreadVal,modDefaultMsgVal,
            modMinMsgVal,creationDate,modifiedDate,categoryID,categoryIndex
    FROM jiveForum_temp;
DROP TABLE jiveForum_temp;


ALTER TABLE jiveThread RENAME TO jiveThread_temp;
CREATE TABLE jiveThread (
  threadID            BIGINT NOT NULL,
  forumID             BIGINT NOT NULL,
  rootMessageID       BIGINT NOT NULL,
  modValue            INTEGER NOT NULL,
  rewardPoints        INTEGER NOT NULL,
  creationDate        VARCHAR(15) NOT NULL,
  modificationDate    VARCHAR(15) NOT NULL,
  CONSTRAINT jiveThread_pk PRIMARY KEY (threadID)
);
CREATE INDEX jiveThread_forumID_idx2 ON jiveThread (forumID);
CREATE INDEX jiveThread_modValue_idx2 ON jiveThread (modValue);
CREATE INDEX jiveThread_cDate_idx2 ON jiveThread (creationDate);
CREATE INDEX jiveThread_mDate_idx2 ON jiveThread (modificationDate);
CREATE INDEX jiveThread_fID_mV_idx2 ON jiveThread (forumID, modValue);
INSERT INTO jiveThread(threadID,forumID,rootMessageID,modValue,rewardPoints,creationDate,
        modificationDate)
    SELECT threadID,forumID,rootMessageID,modValue,rewardPoints,creationDate,modifiedDate
    FROM jiveThread_temp;
DROP TABLE jiveThread_temp;


ALTER TABLE jiveMessage RENAME TO jiveMessage_temp;
CREATE TABLE jiveMessage (
  messageID         BIGINT NOT NULL,
  parentMessageID   BIGINT NULL,
  threadID          BIGINT NOT NULL,
  forumID           BIGINT NOT NULL,
  userID            BIGINT NULL,
  subject           VARCHAR(255) NULL,  
  body              LONGVARCHAR NULL,
  modValue          INTEGER NOT NULL,
  rewardPoints      INTEGER NOT NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modificationDate  VARCHAR(15) NOT NULL,
  CONSTRAINT jiveMessage_pk PRIMARY KEY (messageID)
);
CREATE INDEX jiveMessage_forumID_idx2 ON jiveMessage (forumID);
CREATE INDEX jiveMessage_threadID_idx2 ON jiveMessage (threadID);
CREATE INDEX jiveMessage_userID_idx2 ON jiveMessage (userID);
CREATE INDEX jiveMessage_forumId_modVal_idx2 ON jiveMessage(forumID, modValue);
CREATE INDEX jiveMessage_cDate_idx2 ON jiveMessage (creationDate);
CREATE INDEX jiveMessage_mDate_idx2 ON jiveMessage (modificationDate);
INSERT INTO jiveMessage(messageID,parentMessageID,threadID,forumID,userID,subject,body,modValue,
        rewardPoints,creationDate,modificationDate)
    SELECT messageID,parentMessageID,threadID,forumID,userID,subject,body,modValue,rewardPoints,
            creationDate,modifiedDate
    FROM jiveMessage_temp;
DROP TABLE jiveMessage_temp;


ALTER TABLE jiveUser RENAME TO jiveUser_temp;
CREATE TABLE jiveUser (
  userID            BIGINT NOT NULL,
  username          VARCHAR(30) NOT NULL,
  passwordHash      VARCHAR(32) NOT NULL,
  name              VARCHAR(100) NULL,
  nameVisible       INTEGER NOT NULL,
  email             VARCHAR(100) NOT NULL,
  emailVisible      INTEGER NOT NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modificationDate  VARCHAR(15) NOT NULL,
  CONSTRAINT jiveUser_pk PRIMARY KEY (userID)
);
CREATE INDEX jiveUser_cDate_idx2 on jiveUser (creationDate);
INSERT INTO jiveUser(userID,username,passwordHash,name,nameVisible,email,emailVisible,
        creationDate,modificationDate)
    SELECT userID,username,passwordHash,name,nameVisible,email,emailVisible,creationDate,
            modifiedDate
    FROM jiveUser_temp;
DROP TABLE jiveUser_temp;


ALTER TABLE jiveGroup RENAME TO jiveGroup_temp;
CREATE TABLE jiveGroup (
  groupID           BIGINT NOT NULL,
  name              VARCHAR(100) NOT NULL,
  description       VARCHAR(255) NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modificationDate  VARCHAR(15) NOT NULL,
  CONSTRAINT jiveGroup_pk PRIMARY KEY (groupID)
);
CREATE INDEX jiveGroup_cDate_idx2 on jiveGroup (creationDate);
INSERT INTO jiveGroup(groupID,name,description,creationDate,modificationDate)
    SELECT groupID,name,description,creationDate,modifiedDate
    FROM jiveGroup_temp;
DROP TABLE jiveGroup_temp;


ALTER TABLE jiveAttachment RENAME TO jiveAttachment_temp;
CREATE TABLE jiveAttachment (
  attachmentID	    BIGINT NOT NULL,
  messageID         BIGINT NOT NULL,
  fileName          VARCHAR(255) NOT NULL,
  fileSize          INTEGER NOT NULL,
  contentType       VARCHAR(50) NOT NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modificationDate  VARCHAR(15) NOT NULL,
  CONSTRAINT jiveAttachment_pk PRIMARY KEY (attachmentID)
);
CREATE INDEX jiveAttachment_messageID_idx2 ON jiveAttachment (messageID);
INSERT INTO jiveAttachment(attachmentID,messageID,fileName,fileSize,contentType,creationDate,
        modificationDate)
    SELECT attachmentID,messageID,fileName,fileSize,contentType,creationDate,modifiedDate
    FROM jiveAttachment_temp;
DROP TABLE jiveAttachment_temp;


ALTER TABLE jiveCategory RENAME TO jiveCategory_temp;
CREATE TABLE jiveCategory (
  categoryID        BIGINT NOT NULL,
  name              VARCHAR(255) NOT NULL,
  description       VARCHAR(4000) NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modificationDate  VARCHAR(15) NOT NULL,
  lft               INTEGER NOT NULL,
  rgt               INTEGER NOT NULL,
  CONSTRAINT jiveCategory_pk PRIMARY KEY (categoryID)
);
CREATE INDEX jiveCategory_lft_idx2 ON jiveCategory (lft);
CREATE INDEX jiveCategory_rgt_idx2 ON jiveCategory (rgt);
INSERT INTO jiveCategory(categoryID,name,description,creationDate,modificationDate,lft,rgt)
    SELECT categoryID,name,description,creationDate,modifiedDate,lft,rgt
    FROM jiveCategory_temp;
DROP TABLE jiveCategory_temp;


// 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 values

CREATE TABLE jiveUserPerm_temp (
  objectType       INTEGER NOT NULL,
  objectID         BIGINT NOT NULL,
  userID           BIGINT 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);

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

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

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

// Drop older table
DROP TABLE jiveUserPerm;
    
// Rename the temp table
ALTER TABLE jiveUserPerm_temp RENAME TO jiveUserPerm;



// Migrate group perms

// Temp table to hold interim perm values
CREATE TABLE jiveGroupPerm_temp (
  objectType    INTEGER NOT NULL,
  objectID      BIGINT NOT NULL,
  groupID       BIGINT 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);

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

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

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

// Drop the older table
DROP TABLE jiveGroupPerm;
    
// Rename the temp table
ALTER TABLE jiveGroupPerm_temp RENAME TO jiveGroupPerm;



// Migrate watches

// Rename the old table
ALTER TABLE jiveWatch RENAME TO jiveWatch_temp;
// Temp table to hold interim watch values
CREATE TABLE jiveWatch (
  userID          BIGINT NOT NULL,
  objectType      INTEGER NOT NULL,
  objectID        BIGINT NOT NULL,
  watchType       INTEGER NOT NULL,
  expirable       INTEGER NOT NULL,
  CONSTRAINT jiveWatch_pk PRIMARY KEY (userID, objectID, objectType, watchType)
);
CREATE INDEX jiveWatch_userID_idx2 ON jiveWatch (userID);
CREATE INDEX jiveWatch_objectID_idx2 ON jiveWatch (objectID);
CREATE INDEX jiveWatch_objectType_idx2 ON jiveWatch (objectType);

INSERT INTO jiveWatch(userID,objectType,objectID,watchType,expirable)
    SELECT userID,1,threadID,watchType,expirable
    FROM jiveWatch_temp;
DROP TABLE jiveWatch_temp;