migration from groups v2

From jOpenSimWiki
Jump to: navigation, search

If you used OpenSimulators build in Groups V2 before and want to move to jOpenSim groups, you might want to transfer the already stored data from the Robust database to the jOpenSim/Joomla database.

The following query can help you with this. BUT NOTE: always a good idea is to make a backup before manipulating data ;)

A word to the sql and how it works:

The tables of opensim and jOpenSim must reside on the same server, but not necessarily in the same database. You need to run the sql with a user that has access to both databases! Attachments from group notices are NOT migrated with this SQL since the way, they are stored are quite different. If you dont need to migrate group notices, just remove the block starting with "# groups_notices -> opensim_groupnotice"


You just need to alter the first 3 lines to your DB names (and Joomla prefix) ... the SQL will take care of the rest

SET @opensimdb = "ROBUSTDB";
SET @joomladb	= "JOOMLADB";
SET @joomlaprefix = "jos_";

# groups_groups -> opensim_groups
SET @query = CONCAT("INSERT INTO ",@joomladb,".",@joomlaprefix,"opensim_group SELECT GroupID,Name,Charter,InsigniaID,FounderID,MembershipFee,OpenEnrollment,ShowInList,AllowPublish,MaturePublish,OwnerRoleID FROM ",@opensimdb,".`os_groups_groups`;");
PREPARE stmt FROM @query;
EXECUTE stmt;

# groups_principals -> opensim_group_active
SET @query = CONCAT("INSERT INTO ",@joomladb,".",@joomlaprefix,"opensim_groupactive SELECT PrincipalID,ActiveGroupID FROM ",@opensimdb,".`os_groups_principals`;");
PREPARE stmt FROM @query;
EXECUTE stmt;

# groups_invites -> opensim_groupinvite
SET @query = CONCAT("INSERT INTO ",@joomladb,".",@joomlaprefix,"opensim_groupinvite SELECT InviteID,GroupID,RoleID,PrincipalID,TMStamp FROM ",@opensimdb,".`os_groups_invites`;");
PREPARE stmt FROM @query;
EXECUTE stmt;

# groups_membership -> opensim_groupmembership
SET @query = CONCAT("INSERT INTO ",@joomladb,".",@joomlaprefix,"opensim_groupmembership SELECT GroupID,PrincipalID,SelectedRoleID,Contribution,ListInProfile,AcceptNotices FROM ",@opensimdb,".`os_groups_membership`;");
PREPARE stmt FROM @query;
EXECUTE stmt;

# groups_notices -> opensim_groupnotice
SET @query = CONCAT("INSERT INTO ",@joomladb,".",@joomlaprefix,"opensim_groupnotice SELECT GroupID,NoticeID,TMStamp,FromName,Subject,Message,NULL FROM ",@opensimdb,".`os_groups_notices`;");
PREPARE stmt FROM @query;
EXECUTE stmt;

# groups_roles -> opensim_grouprole
SET @query = CONCAT("INSERT INTO ",@joomladb,".",@joomlaprefix,"opensim_grouprole SELECT GroupID,RoleID,Name,Description,Title,Powers FROM ",@opensimdb,".`os_groups_roles`;");
PREPARE stmt FROM @query;
EXECUTE stmt;

# groups_rolemembership -> opensim_grouprolemembership
SET @query = CONCAT("INSERT INTO ",@joomladb,".",@joomlaprefix,"opensim_grouprolemembership SELECT GroupID,RoleID,PrincipalID FROM ",@opensimdb,".`os_groups_rolemembership`;");
PREPARE stmt FROM @query;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;