1
0

MySQLGroupsData.cs 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483
  1. /*
  2. * Copyright (c) Contributors, http://opensimulator.org/
  3. * See CONTRIBUTORS.TXT for a full list of copyright holders.
  4. *
  5. * Redistribution and use in source and binary forms, with or without
  6. * modification, are permitted provided that the following conditions are met:
  7. * * Redistributions of source code must retain the above copyright
  8. * notice, this list of conditions and the following disclaimer.
  9. * * Redistributions in binary form must reproduce the above copyright
  10. * notice, this list of conditions and the following disclaimer in the
  11. * documentation and/or other materials provided with the distribution.
  12. * * Neither the name of the OpenSimulator Project nor the
  13. * names of its contributors may be used to endorse or promote products
  14. * derived from this software without specific prior written permission.
  15. *
  16. * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
  17. * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  18. * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  19. * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
  20. * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
  21. * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  22. * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  23. * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  24. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  25. * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  26. */
  27. using System;
  28. using System.Collections;
  29. using System.Collections.Generic;
  30. using System.Reflection;
  31. using OpenSim.Framework;
  32. using OpenSim.Data.MySQL;
  33. using OpenMetaverse;
  34. using MySql.Data.MySqlClient;
  35. namespace OpenSim.Data.MySQL
  36. {
  37. public class MySQLGroupsData : IGroupsData
  38. {
  39. private MySqlGroupsGroupsHandler m_Groups;
  40. private MySqlGroupsMembershipHandler m_Membership;
  41. private MySqlGroupsRolesHandler m_Roles;
  42. private MySqlGroupsRoleMembershipHandler m_RoleMembership;
  43. private MySqlGroupsInvitesHandler m_Invites;
  44. private MySqlGroupsNoticesHandler m_Notices;
  45. private MySqlGroupsPrincipalsHandler m_Principals;
  46. public MySQLGroupsData(string connectionString, string realm)
  47. {
  48. m_Groups = new MySqlGroupsGroupsHandler(connectionString, realm + "_groups", realm + "_Store");
  49. m_Membership = new MySqlGroupsMembershipHandler(connectionString, realm + "_membership");
  50. m_Roles = new MySqlGroupsRolesHandler(connectionString, realm + "_roles");
  51. m_RoleMembership = new MySqlGroupsRoleMembershipHandler(connectionString, realm + "_rolemembership");
  52. m_Invites = new MySqlGroupsInvitesHandler(connectionString, realm + "_invites");
  53. m_Notices = new MySqlGroupsNoticesHandler(connectionString, realm + "_notices");
  54. m_Principals = new MySqlGroupsPrincipalsHandler(connectionString, realm + "_principals");
  55. }
  56. #region groups table
  57. public bool StoreGroup(GroupData data)
  58. {
  59. return m_Groups.Store(data);
  60. }
  61. public GroupData RetrieveGroup(UUID groupID)
  62. {
  63. GroupData[] groups = m_Groups.Get("GroupID", groupID.ToString());
  64. if (groups.Length > 0)
  65. return groups[0];
  66. return null;
  67. }
  68. public GroupData RetrieveGroup(string name)
  69. {
  70. GroupData[] groups = m_Groups.Get("Name", name);
  71. if (groups.Length > 0)
  72. return groups[0];
  73. return null;
  74. }
  75. public GroupData[] RetrieveGroups(string pattern)
  76. {
  77. if (string.IsNullOrEmpty(pattern))
  78. pattern = "1";
  79. else
  80. pattern = string.Format("Name LIKE '%{0}%'", MySqlHelper.EscapeString(pattern));
  81. return m_Groups.Get(string.Format("ShowInList=1 AND ({0}) ORDER BY Name LIMIT 100", pattern));
  82. }
  83. public bool DeleteGroup(UUID groupID)
  84. {
  85. return m_Groups.Delete("GroupID", groupID.ToString());
  86. }
  87. public int GroupsCount()
  88. {
  89. return (int)m_Groups.GetCount("Location=\"\"");
  90. }
  91. #endregion
  92. #region membership table
  93. public MembershipData[] RetrieveMembers(UUID groupID)
  94. {
  95. return m_Membership.Get("GroupID", groupID.ToString());
  96. }
  97. public MembershipData RetrieveMember(UUID groupID, string pricipalID)
  98. {
  99. MembershipData[] m = m_Membership.Get(new string[] { "GroupID", "PrincipalID" },
  100. new string[] { groupID.ToString(), pricipalID });
  101. if (m != null && m.Length > 0)
  102. return m[0];
  103. return null;
  104. }
  105. public MembershipData[] RetrieveMemberships(string pricipalID)
  106. {
  107. return m_Membership.Get("PrincipalID", pricipalID.ToString());
  108. }
  109. public bool StoreMember(MembershipData data)
  110. {
  111. return m_Membership.Store(data);
  112. }
  113. public bool DeleteMember(UUID groupID, string pricipalID)
  114. {
  115. return m_Membership.Delete(new string[] { "GroupID", "PrincipalID" },
  116. new string[] { groupID.ToString(), pricipalID });
  117. }
  118. public int MemberCount(UUID groupID)
  119. {
  120. return (int)m_Membership.GetCount("GroupID", groupID.ToString());
  121. }
  122. #endregion
  123. #region roles table
  124. public bool StoreRole(RoleData data)
  125. {
  126. return m_Roles.Store(data);
  127. }
  128. public RoleData RetrieveRole(UUID groupID, UUID roleID)
  129. {
  130. RoleData[] data = m_Roles.Get(new string[] { "GroupID", "RoleID" },
  131. new string[] { groupID.ToString(), roleID.ToString() });
  132. if (data != null && data.Length > 0)
  133. return data[0];
  134. return null;
  135. }
  136. public RoleData[] RetrieveRoles(UUID groupID)
  137. {
  138. //return m_Roles.RetrieveRoles(groupID);
  139. return m_Roles.Get("GroupID", groupID.ToString());
  140. }
  141. public bool DeleteRole(UUID groupID, UUID roleID)
  142. {
  143. return m_Roles.Delete(new string[] { "GroupID", "RoleID" },
  144. new string[] { groupID.ToString(), roleID.ToString() });
  145. }
  146. public int RoleCount(UUID groupID)
  147. {
  148. return (int)m_Roles.GetCount("GroupID", groupID.ToString());
  149. }
  150. #endregion
  151. #region rolememberhip table
  152. public RoleMembershipData[] RetrieveRolesMembers(UUID groupID)
  153. {
  154. RoleMembershipData[] data = m_RoleMembership.Get("GroupID", groupID.ToString());
  155. return data;
  156. }
  157. public RoleMembershipData[] RetrieveRoleMembers(UUID groupID, UUID roleID)
  158. {
  159. RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID" },
  160. new string[] { groupID.ToString(), roleID.ToString() });
  161. return data;
  162. }
  163. public RoleMembershipData[] RetrieveMemberRoles(UUID groupID, string principalID)
  164. {
  165. RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "PrincipalID" },
  166. new string[] { groupID.ToString(), principalID.ToString() });
  167. return data;
  168. }
  169. public RoleMembershipData RetrieveRoleMember(UUID groupID, UUID roleID, string principalID)
  170. {
  171. RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID", "PrincipalID" },
  172. new string[] { groupID.ToString(), roleID.ToString(), principalID.ToString() });
  173. if (data != null && data.Length > 0)
  174. return data[0];
  175. return null;
  176. }
  177. public int RoleMemberCount(UUID groupID, UUID roleID)
  178. {
  179. return (int)m_RoleMembership.GetCount(new string[] { "GroupID", "RoleID" },
  180. new string[] { groupID.ToString(), roleID.ToString() });
  181. }
  182. public bool StoreRoleMember(RoleMembershipData data)
  183. {
  184. return m_RoleMembership.Store(data);
  185. }
  186. public bool DeleteRoleMember(RoleMembershipData data)
  187. {
  188. return m_RoleMembership.Delete(new string[] { "GroupID", "RoleID", "PrincipalID"},
  189. new string[] { data.GroupID.ToString(), data.RoleID.ToString(), data.PrincipalID });
  190. }
  191. public bool DeleteMemberAllRoles(UUID groupID, string principalID)
  192. {
  193. return m_RoleMembership.Delete(new string[] { "GroupID", "PrincipalID" },
  194. new string[] { groupID.ToString(), principalID });
  195. }
  196. #endregion
  197. #region principals table
  198. public bool StorePrincipal(PrincipalData data)
  199. {
  200. return m_Principals.Store(data);
  201. }
  202. public PrincipalData RetrievePrincipal(string principalID)
  203. {
  204. PrincipalData[] p = m_Principals.Get("PrincipalID", principalID);
  205. if (p != null && p.Length > 0)
  206. return p[0];
  207. return null;
  208. }
  209. public bool DeletePrincipal(string principalID)
  210. {
  211. return m_Principals.Delete("PrincipalID", principalID);
  212. }
  213. #endregion
  214. #region invites table
  215. public bool StoreInvitation(InvitationData data)
  216. {
  217. return m_Invites.Store(data);
  218. }
  219. public InvitationData RetrieveInvitation(UUID inviteID)
  220. {
  221. InvitationData[] invites = m_Invites.Get("InviteID", inviteID.ToString());
  222. if (invites != null && invites.Length > 0)
  223. return invites[0];
  224. return null;
  225. }
  226. public InvitationData RetrieveInvitation(UUID groupID, string principalID)
  227. {
  228. InvitationData[] invites = m_Invites.Get(new string[] { "GroupID", "PrincipalID" },
  229. new string[] { groupID.ToString(), principalID });
  230. if (invites != null && invites.Length > 0)
  231. return invites[0];
  232. return null;
  233. }
  234. public bool DeleteInvite(UUID inviteID)
  235. {
  236. return m_Invites.Delete("InviteID", inviteID.ToString());
  237. }
  238. public void DeleteOldInvites()
  239. {
  240. m_Invites.DeleteOld();
  241. }
  242. #endregion
  243. #region notices table
  244. public bool StoreNotice(NoticeData data)
  245. {
  246. return m_Notices.Store(data);
  247. }
  248. public NoticeData RetrieveNotice(UUID noticeID)
  249. {
  250. NoticeData[] notices = m_Notices.Get("NoticeID", noticeID.ToString());
  251. if (notices != null && notices.Length > 0)
  252. return notices[0];
  253. return null;
  254. }
  255. public NoticeData[] RetrieveNotices(UUID groupID)
  256. {
  257. NoticeData[] notices = m_Notices.Get("GroupID", groupID.ToString());
  258. return notices;
  259. }
  260. public bool DeleteNotice(UUID noticeID)
  261. {
  262. return m_Notices.Delete("NoticeID", noticeID.ToString());
  263. }
  264. public void DeleteOldNotices()
  265. {
  266. m_Notices.DeleteOld();
  267. }
  268. #endregion
  269. #region combinations
  270. public MembershipData RetrievePrincipalGroupMembership(string principalID, UUID groupID)
  271. {
  272. // TODO
  273. return null;
  274. }
  275. public MembershipData[] RetrievePrincipalGroupMemberships(string principalID)
  276. {
  277. // TODO
  278. return null;
  279. }
  280. #endregion
  281. }
  282. public class MySqlGroupsGroupsHandler : MySQLGenericTableHandler<GroupData>
  283. {
  284. protected override Assembly Assembly
  285. {
  286. // WARNING! Moving migrations to this assembly!!!
  287. get { return GetType().Assembly; }
  288. }
  289. public MySqlGroupsGroupsHandler(string connectionString, string realm, string store)
  290. : base(connectionString, realm, store)
  291. {
  292. }
  293. }
  294. public class MySqlGroupsMembershipHandler : MySQLGenericTableHandler<MembershipData>
  295. {
  296. protected override Assembly Assembly
  297. {
  298. // WARNING! Moving migrations to this assembly!!!
  299. get { return GetType().Assembly; }
  300. }
  301. public MySqlGroupsMembershipHandler(string connectionString, string realm)
  302. : base(connectionString, realm, string.Empty)
  303. {
  304. }
  305. }
  306. public class MySqlGroupsRolesHandler : MySQLGenericTableHandler<RoleData>
  307. {
  308. protected override Assembly Assembly
  309. {
  310. // WARNING! Moving migrations to this assembly!!!
  311. get { return GetType().Assembly; }
  312. }
  313. public MySqlGroupsRolesHandler(string connectionString, string realm)
  314. : base(connectionString, realm, string.Empty)
  315. {
  316. }
  317. }
  318. public class MySqlGroupsRoleMembershipHandler : MySQLGenericTableHandler<RoleMembershipData>
  319. {
  320. protected override Assembly Assembly
  321. {
  322. // WARNING! Moving migrations to this assembly!!!
  323. get { return GetType().Assembly; }
  324. }
  325. public MySqlGroupsRoleMembershipHandler(string connectionString, string realm)
  326. : base(connectionString, realm, string.Empty)
  327. {
  328. }
  329. }
  330. public class MySqlGroupsInvitesHandler : MySQLGenericTableHandler<InvitationData>
  331. {
  332. protected override Assembly Assembly
  333. {
  334. // WARNING! Moving migrations to this assembly!!!
  335. get { return GetType().Assembly; }
  336. }
  337. public MySqlGroupsInvitesHandler(string connectionString, string realm)
  338. : base(connectionString, realm, string.Empty)
  339. {
  340. }
  341. public void DeleteOld()
  342. {
  343. uint now = (uint)Util.UnixTimeSinceEpoch();
  344. using (MySqlCommand cmd = new MySqlCommand())
  345. {
  346. cmd.CommandText = String.Format("delete from {0} where TMStamp < NOW() - INTERVAL 2 WEEK", m_Realm);
  347. ExecuteNonQuery(cmd);
  348. }
  349. }
  350. }
  351. public class MySqlGroupsNoticesHandler : MySQLGenericTableHandler<NoticeData>
  352. {
  353. protected override Assembly Assembly
  354. {
  355. // WARNING! Moving migrations to this assembly!!!
  356. get { return GetType().Assembly; }
  357. }
  358. public MySqlGroupsNoticesHandler(string connectionString, string realm)
  359. : base(connectionString, realm, string.Empty)
  360. {
  361. }
  362. public void DeleteOld()
  363. {
  364. uint now = (uint)Util.UnixTimeSinceEpoch();
  365. using (MySqlCommand cmd = new MySqlCommand())
  366. {
  367. cmd.CommandText = String.Format("delete from {0} where TMStamp < ?tstamp", m_Realm);
  368. cmd.Parameters.AddWithValue("?tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old
  369. ExecuteNonQuery(cmd);
  370. }
  371. }
  372. }
  373. public class MySqlGroupsPrincipalsHandler : MySQLGenericTableHandler<PrincipalData>
  374. {
  375. protected override Assembly Assembly
  376. {
  377. // WARNING! Moving migrations to this assembly!!!
  378. get { return GetType().Assembly; }
  379. }
  380. public MySqlGroupsPrincipalsHandler(string connectionString, string realm)
  381. : base(connectionString, realm, string.Empty)
  382. {
  383. }
  384. }
  385. }