From 9380d01976726885bd993573aa649f2cb0992909 Mon Sep 17 00:00:00 2001 From: Diva Canto Date: Tue, 19 Feb 2013 07:26:40 -0800 Subject: First commit of Diva Groups. The Data bits went to OpenSim.Data core, the rest to Addons.Groups.dll. --- OpenSim/Data/MySQL/MySQLGroupsData.cs | 484 +++++++++++++++++++++ .../MySQL/Resources/os_groups_Store.migrations | 115 +++++ 2 files changed, 599 insertions(+) create mode 100644 OpenSim/Data/MySQL/MySQLGroupsData.cs create mode 100644 OpenSim/Data/MySQL/Resources/os_groups_Store.migrations (limited to 'OpenSim/Data/MySQL') diff --git a/OpenSim/Data/MySQL/MySQLGroupsData.cs b/OpenSim/Data/MySQL/MySQLGroupsData.cs new file mode 100644 index 0000000..2a1bd6c --- /dev/null +++ b/OpenSim/Data/MySQL/MySQLGroupsData.cs @@ -0,0 +1,484 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSimulator Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections; +using System.Collections.Generic; +using System.Reflection; + +using OpenSim.Framework; +using OpenSim.Data.MySQL; + +using OpenMetaverse; +using MySql.Data.MySqlClient; + +namespace OpenSim.Data.MySQL +{ + public class MySQLGroupsData : IGroupsData + { + private MySqlGroupsGroupsHandler m_Groups; + private MySqlGroupsMembershipHandler m_Membership; + private MySqlGroupsRolesHandler m_Roles; + private MySqlGroupsRoleMembershipHandler m_RoleMembership; + private MySqlGroupsInvitesHandler m_Invites; + private MySqlGroupsNoticesHandler m_Notices; + private MySqlGroupsPrincipalsHandler m_Principals; + + public MySQLGroupsData(string connectionString, string realm) + { + m_Groups = new MySqlGroupsGroupsHandler(connectionString, realm + "_groups", realm + "_Store"); + m_Membership = new MySqlGroupsMembershipHandler(connectionString, realm + "_membership"); + m_Roles = new MySqlGroupsRolesHandler(connectionString, realm + "_roles"); + m_RoleMembership = new MySqlGroupsRoleMembershipHandler(connectionString, realm + "_rolemembership"); + m_Invites = new MySqlGroupsInvitesHandler(connectionString, realm + "_invites"); + m_Notices = new MySqlGroupsNoticesHandler(connectionString, realm + "_notices"); + m_Principals = new MySqlGroupsPrincipalsHandler(connectionString, realm + "_principals"); + } + + #region groups table + public bool StoreGroup(GroupData data) + { + return m_Groups.Store(data); + } + + public GroupData RetrieveGroup(UUID groupID) + { + GroupData[] groups = m_Groups.Get("GroupID", groupID.ToString()); + if (groups.Length > 0) + return groups[0]; + + return null; + } + + public GroupData RetrieveGroup(string name) + { + GroupData[] groups = m_Groups.Get("Name", name); + if (groups.Length > 0) + return groups[0]; + + return null; + } + + public GroupData[] RetrieveGroups(string pattern) + { + if (string.IsNullOrEmpty(pattern)) + pattern = "1 ORDER BY Name LIMIT 100"; + else + pattern = string.Format("Name LIKE %{0}% ORDER BY Name LIMIT 100", pattern); + + return m_Groups.Get(pattern); + } + + public bool DeleteGroup(UUID groupID) + { + return m_Groups.Delete("GroupID", groupID.ToString()); + } + + public int GroupsCount() + { + return (int)m_Groups.GetCount("Location=\"\""); + } + + #endregion + + #region membership table + public MembershipData[] RetrieveMembers(UUID groupID) + { + return m_Membership.Get("GroupID", groupID.ToString()); + } + + public MembershipData RetrieveMember(UUID groupID, string pricipalID) + { + MembershipData[] m = m_Membership.Get(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), pricipalID }); + if (m != null && m.Length > 0) + return m[0]; + + return null; + } + + public MembershipData[] RetrieveMemberships(string pricipalID) + { + return m_Membership.Get("PrincipalID", pricipalID.ToString()); + } + + public bool StoreMember(MembershipData data) + { + return m_Membership.Store(data); + } + + public bool DeleteMember(UUID groupID, string pricipalID) + { + return m_Membership.Delete(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), pricipalID }); + } + + public int MemberCount(UUID groupID) + { + return (int)m_Membership.GetCount("GroupID", groupID.ToString()); + } + #endregion + + #region roles table + public bool StoreRole(RoleData data) + { + return m_Roles.Store(data); + } + + public RoleData RetrieveRole(UUID groupID, UUID roleID) + { + RoleData[] data = m_Roles.Get(new string[] { "GroupID", "RoleID" }, + new string[] { groupID.ToString(), roleID.ToString() }); + + if (data != null && data.Length > 0) + return data[0]; + + return null; + } + + public RoleData[] RetrieveRoles(UUID groupID) + { + //return m_Roles.RetrieveRoles(groupID); + return m_Roles.Get("GroupID", groupID.ToString()); + } + + public bool DeleteRole(UUID groupID, UUID roleID) + { + return m_Roles.Delete(new string[] { "GroupID", "RoleID" }, + new string[] { groupID.ToString(), roleID.ToString() }); + } + + public int RoleCount(UUID groupID) + { + return (int)m_Roles.GetCount("GroupID", groupID.ToString()); + } + + + #endregion + + #region rolememberhip table + public RoleMembershipData[] RetrieveRolesMembers(UUID groupID) + { + RoleMembershipData[] data = m_RoleMembership.Get("GroupID", groupID.ToString()); + + return data; + } + + public RoleMembershipData[] RetrieveRoleMembers(UUID groupID, UUID roleID) + { + RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID" }, + new string[] { groupID.ToString(), roleID.ToString() }); + + return data; + } + + public RoleMembershipData[] RetrieveMemberRoles(UUID groupID, string principalID) + { + RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), principalID.ToString() }); + + return data; + } + + public RoleMembershipData RetrieveRoleMember(UUID groupID, UUID roleID, string principalID) + { + RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID", "PrincipalID" }, + new string[] { groupID.ToString(), roleID.ToString(), principalID.ToString() }); + + if (data != null && data.Length > 0) + return data[0]; + + return null; + } + + public int RoleMemberCount(UUID groupID, UUID roleID) + { + return (int)m_RoleMembership.GetCount(new string[] { "GroupID", "RoleID" }, + new string[] { groupID.ToString(), roleID.ToString() }); + } + + public bool StoreRoleMember(RoleMembershipData data) + { + return m_RoleMembership.Store(data); + } + + public bool DeleteRoleMember(RoleMembershipData data) + { + return m_RoleMembership.Delete(new string[] { "GroupID", "RoleID", "PrincipalID"}, + new string[] { data.GroupID.ToString(), data.RoleID.ToString(), data.PrincipalID }); + } + + public bool DeleteMemberAllRoles(UUID groupID, string principalID) + { + return m_RoleMembership.Delete(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), principalID }); + } + + #endregion + + #region principals table + public bool StorePrincipal(PrincipalData data) + { + return m_Principals.Store(data); + } + + public PrincipalData RetrievePrincipal(string principalID) + { + PrincipalData[] p = m_Principals.Get("PrincipalID", principalID); + if (p != null && p.Length > 0) + return p[0]; + + return null; + } + + public bool DeletePrincipal(string principalID) + { + return m_Principals.Delete("PrincipalID", principalID); + } + #endregion + + #region invites table + + public bool StoreInvitation(InvitationData data) + { + return m_Invites.Store(data); + } + + public InvitationData RetrieveInvitation(UUID inviteID) + { + InvitationData[] invites = m_Invites.Get("InviteID", inviteID.ToString()); + + if (invites != null && invites.Length > 0) + return invites[0]; + + return null; + } + + public InvitationData RetrieveInvitation(UUID groupID, string principalID) + { + InvitationData[] invites = m_Invites.Get(new string[] { "GroupID", "PrincipalID" }, + new string[] { groupID.ToString(), principalID }); + + if (invites != null && invites.Length > 0) + return invites[0]; + + return null; + } + + public bool DeleteInvite(UUID inviteID) + { + return m_Invites.Delete("InviteID", inviteID.ToString()); + } + + public void DeleteOldInvites() + { + m_Invites.DeleteOld(); + } + + #endregion + + #region notices table + + public bool StoreNotice(NoticeData data) + { + return m_Notices.Store(data); + } + + public NoticeData RetrieveNotice(UUID noticeID) + { + NoticeData[] notices = m_Notices.Get("NoticeID", noticeID.ToString()); + + if (notices != null && notices.Length > 0) + return notices[0]; + + return null; + } + + public NoticeData[] RetrieveNotices(UUID groupID) + { + NoticeData[] notices = m_Notices.Get("GroupID", groupID.ToString()); + + return notices; + } + + public bool DeleteNotice(UUID noticeID) + { + return m_Notices.Delete("NoticeID", noticeID.ToString()); + } + + public void DeleteOldNotices() + { + m_Notices.DeleteOld(); + } + + #endregion + + #region combinations + public MembershipData RetrievePrincipalGroupMembership(string principalID, UUID groupID) + { + // TODO + return null; + } + public MembershipData[] RetrievePrincipalGroupMemberships(string principalID) + { + // TODO + return null; + } + + #endregion + } + + public class MySqlGroupsGroupsHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsGroupsHandler(string connectionString, string realm, string store) + : base(connectionString, realm, store) + { + } + + } + + public class MySqlGroupsMembershipHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsMembershipHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + } + + public class MySqlGroupsRolesHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsRolesHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + } + + public class MySqlGroupsRoleMembershipHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsRoleMembershipHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + } + + public class MySqlGroupsInvitesHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsInvitesHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + public void DeleteOld() + { + uint now = (uint)Util.UnixTimeSinceEpoch(); + + using (MySqlCommand cmd = new MySqlCommand()) + { + cmd.CommandText = String.Format("delete from {0} where TMStamp < ?tstamp", m_Realm); + cmd.Parameters.AddWithValue("?tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old + + ExecuteNonQuery(cmd); + } + + } + } + + public class MySqlGroupsNoticesHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsNoticesHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + + public void DeleteOld() + { + uint now = (uint)Util.UnixTimeSinceEpoch(); + + using (MySqlCommand cmd = new MySqlCommand()) + { + cmd.CommandText = String.Format("delete from {0} where TMStamp < ?tstamp", m_Realm); + cmd.Parameters.AddWithValue("?tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old + + ExecuteNonQuery(cmd); + } + + } + } + + public class MySqlGroupsPrincipalsHandler : MySQLGenericTableHandler + { + protected override Assembly Assembly + { + // WARNING! Moving migrations to this assembly!!! + get { return GetType().Assembly; } + } + + public MySqlGroupsPrincipalsHandler(string connectionString, string realm) + : base(connectionString, realm, string.Empty) + { + } + } +} diff --git a/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations b/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations new file mode 100644 index 0000000..9e6f1c1 --- /dev/null +++ b/OpenSim/Data/MySQL/Resources/os_groups_Store.migrations @@ -0,0 +1,115 @@ +:VERSION 1 # -------------------------- + +BEGIN; + +CREATE TABLE `os_groups_groups` ( + `GroupID` char(36) NOT NULL default '', + `Location` varchar(255) NOT NULL default '', + `Name` varchar(255) NOT NULL default '', + `Charter` text NOT NULL, + `InsigniaID` char(36) NOT NULL default '', + `FounderID` char(36) NOT NULL default '', + `MembershipFee` int(11) NOT NULL default '0', + `OpenEnrollment` varchar(255) NOT NULL default '', + `ShowInList` int(4) NOT NULL default '0', + `AllowPublish` int(4) NOT NULL default '0', + `MaturePublish` int(4) NOT NULL default '0', + `OwnerRoleID` char(36) NOT NULL default '', + PRIMARY KEY (`GroupID`), + UNIQUE KEY `Name` (`Name`), + FULLTEXT KEY `Name_2` (`Name`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_membership` ( + `GroupID`char(36) NOT NULL default '', + `PrincipalID` VARCHAR(255) NOT NULL default '', + `SelectedRoleID` char(36) NOT NULL default '', + `Contribution` int(11) NOT NULL default '0', + `ListInProfile` int(4) NOT NULL default '1', + `AcceptNotices` int(4) NOT NULL default '1', + `AccessToken` char(36) NOT NULL default '', + PRIMARY KEY (`GroupID`,`PrincipalID`), + KEY `PrincipalID` (`PrincipalID`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_roles` ( + `GroupID` char(36) NOT NULL default '', + `RoleID` char(36) NOT NULL default '', + `Name` varchar(255) NOT NULL default '', + `Description` varchar(255) NOT NULL default '', + `Title` varchar(255) NOT NULL default '', + `Powers` bigint(20) unsigned NOT NULL default '0', + PRIMARY KEY (`GroupID`,`RoleID`), + KEY `GroupID` (`GroupID`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_rolemembership` ( + `GroupID` char(36) NOT NULL default '', + `RoleID` char(36) NOT NULL default '', + `PrincipalID` VARCHAR(255) NOT NULL default '', + PRIMARY KEY (`GroupID`,`RoleID`,`PrincipalID`), + KEY `PrincipalID` (`PrincipalID`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_invites` ( + `InviteID` char(36) NOT NULL default '', + `GroupID` char(36) NOT NULL default '', + `RoleID` char(36) NOT NULL default '', + `PrincipalID` VARCHAR(255) NOT NULL default '', + `TMStamp` timestamp NOT NULL, + PRIMARY KEY (`InviteID`), + UNIQUE KEY `PrincipalGroup` (`GroupID`,`PrincipalID`) +) ENGINE=MyISAM; + + +CREATE TABLE `os_groups_notices` ( + `GroupID` char(36) NOT NULL default '', + `NoticeID` char(36) NOT NULL default '', + `TMStamp` int(10) unsigned NOT NULL default '0', + `FromName` varchar(255) NOT NULL default '', + `Subject` varchar(255) NOT NULL default '', + `Message` text NOT NULL, + `HasAttachment` int(4) NOT NULL default '0', + `AttachmentType` int(4) NOT NULL default '0', + `AttachmentName` varchar(128) NOT NULL default '', + `AttachmentItemID` char(36) NOT NULL default '', + `AttachmentOwnerID` varchar(255) NOT NULL default '', + PRIMARY KEY (`NoticeID`), + KEY `GroupID` (`GroupID`), + KEY `TMStamp` (`TMStamp`) +) ENGINE=MyISAM; + +CREATE TABLE `os_groups_principals` ( + `PrincipalID` VARCHAR(255) NOT NULL default '', + `ActiveGroupID` char(36) NOT NULL default '', + PRIMARY KEY (`PrincipalID`) +) ENGINE=MyISAM; + +COMMIT; + +:VERSION 2 # -------------------------- + +BEGIN; + +INSERT INTO `os_groups_groups` SELECT * from `diva_groups_groups`; +DROP TABLE `diva_groups_groups`; +INSERT INTO `os_groups_membership` SELECT * from `diva_groups_membership`; +DROP TABLE `diva_groups_membership`; +INSERT INTO `os_groups_roles` SELECT * from `diva_groups_roles`; +DROP TABLE `diva_groups_roles`; +INSERT INTO `os_groups_rolemembership` SELECT * from `diva_groups_rolemembership`; +DROP TABLE `diva_groups_rolemembership`; +INSERT INTO `os_groups_invites` SELECT * from `diva_groups_invites`; +DROP TABLE `diva_groups_invites`; +INSERT INTO `os_groups_notices` SELECT * from `diva_groups_notices`; +DROP TABLE `diva_groups_notices`; +INSERT INTO `os_groups_principals` SELECT * from `diva_groups_principals`; +DROP TABLE `diva_groups_principals`; + +DELETE FROM `migrations` WHERE name='diva_im_Store'; + +COMMIT; \ No newline at end of file -- cgit v1.1