jive_forums_sqlserver_upgrade_2.2_to_3.0.sql

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

/* 2.2 -> 2.5 */




CREATE TABLE jiveAttachmentProp (
  attachmentID  INT NOT NULL,
  name          VARCHAR(100) NOT NULL,
  propValue     VARCHAR(4000) NOT NULL,
  CONSTRAINT jiveAttachmentProp_pk PRIMARY KEY   (attachmentID,name)
);

CREATE TABLE jiveCategory (
  categoryID        INT NOT NULL,
  name              VARCHAR(255) NOT NULL,
  description       VARCHAR(4000) NULL,
  creationDate      VARCHAR(15) NOT NULL,
  modifiedDate      VARCHAR(15) NOT NULL,
  lft               INT NOT NULL,
  rgt               INT NOT NULL,
  CONSTRAINT jiveCategory_pk PRIMARY KEY (categoryID)
);
CREATE INDEX jiveCategory_lft_idx ON jiveCategory (lft);
CREATE INDEX jiveCategory_rgt_idx ON jiveCategory (rgt);

CREATE TABLE jiveCategoryProp (
  categoryID        INT NOT NULL,
  name              VARCHAR(100) NOT NULL,
  propValue         VARCHAR(4000) NOT NULL,
  CONSTRAINT jiveCategoryProp_pk PRIMARY KEY (categoryID,name)
);

INSERT INTO jiveCategory VALUES(1, 'root', '', '0', '0', 1, 2);

INSERT INTO jiveID(idType, id) VALUES(14, 2);

ALTER TABLE jiveForum ADD categoryID INT DEFAULT 1;
ALTER TABLE jiveForum ADD categoryIndex INT DEFAULT 0;
CREATE INDEX jiveForum_cat_idx ON jiveForum (categoryID);

ALTER TABLE jiveUserPerm ADD categoryID INT NULL;
CREATE INDEX jiveUserPerm_cat_idx ON jiveUserPerm (categoryID);

ALTER TABLE jiveGroupPerm ADD categoryID INT NULL;
CREATE INDEX jiveGroupPerm_cat_idx ON jiveGroupPerm (categoryID);





/* 2.5 -> 3.0 */






/* NEW TABLES */

/* Create the roster table */

CREATE TABLE jiveUserRoster (
  userID        INT NOT NULL,
  subUserID     INT NOT NULL,
  CONSTRAINT roster_pk PRIMARY KEY (userID, subUserID)  
);
GO


/* Create the read tracker table */

CREATE TABLE jiveReadTracker (
  userID            INT NOT NULL,
  objectType        INT NOT NULL,
  objectID          INT NOT NULL,
  readDate          CHAR(15) NOT NULL,
  CONSTRAINT readTracker_pk PRIMARY KEY (userID, objectType, objectID)
);
GO

/* Add additional new reward table */

CREATE TABLE jiveUserReward (
  userID        INT NOT NULL,
  rewardPoints  INT NOT NULL,
  CONSTRAINT userReward_pk PRIMARY KEY (userID, rewardPoints)
);
GO

/* ALTER TABLES */

/* Change all 'modifiedDate' columns to 'modificationDate' */

EXEC sp_rename 'jiveForum.modifiedDate', 'modificationDate', 'COLUMN'; 
EXEC sp_rename 'jiveThread.modifiedDate', 'modificationDate', 'COLUMN'; 
EXEC sp_rename 'jiveMessage.modifiedDate', 'modificationDate', 'COLUMN'; 
EXEC sp_rename 'jiveUser.modifiedDate', 'modificationDate', 'COLUMN'; 
EXEC sp_rename 'jiveGroup.modifiedDate', 'modificationDate', 'COLUMN'; 
EXEC sp_rename 'jiveAttachment.modifiedDate', 'modificationDate', 'COLUMN';
EXEC sp_rename 'jiveCategory.modifiedDate', 'modificationDate', 'COLUMN';  
GO

/* Widen the perm columns to accept longs: */

/* Not needed under SQL Server */

/* The 'name' column in jiveForum is no longer unique: */

ALTER TABLE jiveForum ALTER COLUMN name VARCHAR(255) NOT NULL;
GO

/* Migrate the rewardPoints column out of the jiveUser table */

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

ALTER TABLE jiveUser DROP COLUMN rewardPoints;
GO

CREATE INDEX jiveThread_fID_mV_idx ON jiveThread (forumID, modValue);
CREATE INDEX jiveMessage_forumID_idx ON jiveMessage (forumID);
GO

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


GO

/* Temp table to hold interim perm values */
CREATE TABLE t_jiveUserPerm (
  objectType   INT NOT NULL,
  objectID     INT NOT NULL,
  userID       INT NOT NULL,
  permission   INT NOT NULL
);
GO

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

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

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

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

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

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

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

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

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

/* Drop old jiveUserPerms table */
ALTER TABLE jiveUserPerm DROP CONSTRAINT jiveUserPerm_forumID_fk;
GO
ALTER TABLE jiveUserPerm DROP CONSTRAINT jiveUserPerm_userID_fk;
GO
DROP TABLE jiveUserPerm;
GO

/* Create new jievUserPerm table */
CREATE TABLE jiveUserPerm (
  objectType    INT            NOT NULL,
  objectID      INT            NOT NULL,
  userID        INT            NOT NULL,
  permission    INT            NOT NULL,
);
GO

CREATE INDEX jiveUserPerm_object_idx ON jiveUserPerm (objectType, objectID);
CREATE INDEX jiveUserPerm_userID_idx ON jiveUserPerm (userID);
GO

/* Dump data from temp db into the new one: */
INSERT INTO jiveUserPerm (objectType, objectID, userID, permission)
    SELECT objectType, objectID, userID, permission FROM t_jiveUserPerm;
GO

/* Drop the temporary perm table */
DROP TABLE t_jiveUserPerm;
GO


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

/* User 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;
GO

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

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


/* Drop old jiveUserPerms table */
ALTER TABLE jiveGroupPerm DROP CONSTRAINT jiveGroupPerm_groupID_fk;
GO
DROP TABLE jiveGroupPerm;
GO

/* Create new one: */
CREATE TABLE jiveGroupPerm (
  objecttype    INT            NOT NULL,
  objectID      INT            NOT NULL,
  groupID       INT            NOT NULL,
  permission    INT            NOT NULL
);
GO

CREATE INDEX jiveGroupPerm_object_idx ON jiveGroupPerm (objectType, objectID);
CREATE INDEX jiveGroupPerm_groupID_idx ON jiveGroupPerm (groupID);
GO


/* Dump data from temp db into the new one: */
INSERT INTO jiveGroupPerm (objectType, objectID, groupID, permission)
    SELECT objectType, objectID, groupID, permission FROM t_jiveGroupPerm;
GO

/* Drop the temporary perm table */
DROP TABLE t_jiveGroupPerm;
GO

/* Migrate the watch table */

/* Temp table to hold interim watch values */
CREATE TABLE t_jiveWatch (
  userID          INT NOT NULL,
  objectID        INT NOT NULL,
  objectType      INT NOT NULL,
  watchType       INT NOT NULL,
  expirable       INT NOT NULL
);
GO

/* Insert all watches as thread type of watches */
INSERT INTO t_jiveWatch (userID, objectID, objectType, watchType, expirable)
    SELECT userID, threadID, 1, watchType, expirable FROM jiveWatch;
GO

/* drop the old jiveWatch table */
DROP TABLE jiveWatch;
GO

/* create the new one: */
CREATE TABLE jiveWatch (
  userID          INT NOT NULL,
  objectType      INT NOT NULL,
  objectID        INT NOT NULL,
  watchType       INT NOT NULL,
  expirable       INT NOT NULL,
  CONSTRAINT jiveWatch_pk PRIMARY KEY (userID, objectID, objectType, watchType)
);
GO

CREATE INDEX jiveWatch_userID_idx ON jiveWatch (userID);
CREATE INDEX jiveWatch_objectID_idx ON jiveWatch (objectID);
CREATE INDEX jiveWatch_objectType_idx ON jiveWatch (objectType);
GO

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

/* drop the temp table */
DROP TABLE t_jiveWatch;
GO


/* KEYS */

/* Add a key on the password hash of jiveUser */
CREATE INDEX jiveUser_hash_idx ON jiveUser (passwordHash);
GO

/* Get rid of foreign keys that are no longer needed. */

alter table jiveMessage	    drop constraint jiveMessage_parentMsgID_fk;
alter table jiveMessage	    drop constraint jiveMessage_userID_fk;
alter table jiveUserProp    drop constraint jiveUserProp_userID_fk;
alter table jiveGroupProp   drop constraint jiveGroupProp_groupID_fk;
alter table jiveGroupUser   drop constraint jiveGroupUser_groupID_fk;
alter table jiveGroupUser   drop constraint jiveGroupUser_userID_fk;
GO