PGSQLGroupsData.cs 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485
  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 OpenMetaverse;
  33. using log4net;
  34. using Npgsql;
  35. namespace OpenSim.Data.PGSQL
  36. {
  37. public class PGSQLGroupsData : IGroupsData
  38. {
  39. private PGSqlGroupsGroupsHandler m_Groups;
  40. private PGSqlGroupsMembershipHandler m_Membership;
  41. private PGSqlGroupsRolesHandler m_Roles;
  42. private PGSqlGroupsRoleMembershipHandler m_RoleMembership;
  43. private PGSqlGroupsInvitesHandler m_Invites;
  44. private PGSqlGroupsNoticesHandler m_Notices;
  45. private PGSqlGroupsPrincipalsHandler m_Principals;
  46. public PGSQLGroupsData(string connectionString, string realm)
  47. {
  48. m_Groups = new PGSqlGroupsGroupsHandler(connectionString, realm + "_groups", realm + "_Store");
  49. m_Membership = new PGSqlGroupsMembershipHandler(connectionString, realm + "_membership");
  50. m_Roles = new PGSqlGroupsRolesHandler(connectionString, realm + "_roles");
  51. m_RoleMembership = new PGSqlGroupsRoleMembershipHandler(connectionString, realm + "_rolemembership");
  52. m_Invites = new PGSqlGroupsInvitesHandler(connectionString, realm + "_invites");
  53. m_Notices = new PGSqlGroupsNoticesHandler(connectionString, realm + "_notices");
  54. m_Principals = new PGSqlGroupsPrincipalsHandler(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)) // True for where clause
  78. {
  79. pattern = "1";
  80. return m_Groups.Get(pattern);
  81. }
  82. else
  83. {
  84. pattern = " \"ShowInList\" = 1 AND lower(\"Name\") LIKE lower('%" + pattern + "%')";
  85. return m_Groups.Get(pattern, new NpgsqlParameter("pattern", pattern));
  86. }
  87. }
  88. public bool DeleteGroup(UUID groupID)
  89. {
  90. return m_Groups.Delete("GroupID", groupID.ToString());
  91. }
  92. public int GroupsCount()
  93. {
  94. return (int)m_Groups.GetCount(" \"Location\" = \"\"");
  95. }
  96. #endregion
  97. #region membership table
  98. public MembershipData[] RetrieveMembers(UUID groupID)
  99. {
  100. return m_Membership.Get("GroupID", groupID.ToString());
  101. }
  102. public MembershipData RetrieveMember(UUID groupID, string pricipalID)
  103. {
  104. MembershipData[] m = m_Membership.Get(new string[] { "GroupID", "PrincipalID" },
  105. new string[] { groupID.ToString(), pricipalID });
  106. if (m != null && m.Length > 0)
  107. return m[0];
  108. return null;
  109. }
  110. public MembershipData[] RetrieveMemberships(string pricipalID)
  111. {
  112. return m_Membership.Get("PrincipalID", pricipalID.ToString());
  113. }
  114. public bool StoreMember(MembershipData data)
  115. {
  116. return m_Membership.Store(data);
  117. }
  118. public bool DeleteMember(UUID groupID, string pricipalID)
  119. {
  120. return m_Membership.Delete(new string[] { "GroupID", "PrincipalID" },
  121. new string[] { groupID.ToString(), pricipalID });
  122. }
  123. public int MemberCount(UUID groupID)
  124. {
  125. return (int)m_Membership.GetCount("GroupID", groupID.ToString());
  126. }
  127. #endregion
  128. #region roles table
  129. public bool StoreRole(RoleData data)
  130. {
  131. return m_Roles.Store(data);
  132. }
  133. public RoleData RetrieveRole(UUID groupID, UUID roleID)
  134. {
  135. RoleData[] data = m_Roles.Get(new string[] { "GroupID", "RoleID" },
  136. new string[] { groupID.ToString(), roleID.ToString() });
  137. if (data != null && data.Length > 0)
  138. return data[0];
  139. return null;
  140. }
  141. public RoleData[] RetrieveRoles(UUID groupID)
  142. {
  143. //return m_Roles.RetrieveRoles(groupID);
  144. return m_Roles.Get("GroupID", groupID.ToString());
  145. }
  146. public bool DeleteRole(UUID groupID, UUID roleID)
  147. {
  148. return m_Roles.Delete(new string[] { "GroupID", "RoleID" },
  149. new string[] { groupID.ToString(), roleID.ToString() });
  150. }
  151. public int RoleCount(UUID groupID)
  152. {
  153. return (int)m_Roles.GetCount("GroupID", groupID.ToString());
  154. }
  155. #endregion
  156. #region rolememberhip table
  157. public RoleMembershipData[] RetrieveRolesMembers(UUID groupID)
  158. {
  159. RoleMembershipData[] data = m_RoleMembership.Get("GroupID", groupID.ToString());
  160. return data;
  161. }
  162. public RoleMembershipData[] RetrieveRoleMembers(UUID groupID, UUID roleID)
  163. {
  164. RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID" },
  165. new string[] { groupID.ToString(), roleID.ToString() });
  166. return data;
  167. }
  168. public RoleMembershipData[] RetrieveMemberRoles(UUID groupID, string principalID)
  169. {
  170. RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "PrincipalID" },
  171. new string[] { groupID.ToString(), principalID.ToString() });
  172. return data;
  173. }
  174. public RoleMembershipData RetrieveRoleMember(UUID groupID, UUID roleID, string principalID)
  175. {
  176. RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID", "PrincipalID" },
  177. new string[] { groupID.ToString(), roleID.ToString(), principalID.ToString() });
  178. if (data != null && data.Length > 0)
  179. return data[0];
  180. return null;
  181. }
  182. public int RoleMemberCount(UUID groupID, UUID roleID)
  183. {
  184. return (int)m_RoleMembership.GetCount(new string[] { "GroupID", "RoleID" },
  185. new string[] { groupID.ToString(), roleID.ToString() });
  186. }
  187. public bool StoreRoleMember(RoleMembershipData data)
  188. {
  189. return m_RoleMembership.Store(data);
  190. }
  191. public bool DeleteRoleMember(RoleMembershipData data)
  192. {
  193. return m_RoleMembership.Delete(new string[] { "GroupID", "RoleID", "PrincipalID"},
  194. new string[] { data.GroupID.ToString(), data.RoleID.ToString(), data.PrincipalID });
  195. }
  196. public bool DeleteMemberAllRoles(UUID groupID, string principalID)
  197. {
  198. return m_RoleMembership.Delete(new string[] { "GroupID", "PrincipalID" },
  199. new string[] { groupID.ToString(), principalID });
  200. }
  201. #endregion
  202. #region principals table
  203. public bool StorePrincipal(PrincipalData data)
  204. {
  205. return m_Principals.Store(data);
  206. }
  207. public PrincipalData RetrievePrincipal(string principalID)
  208. {
  209. PrincipalData[] p = m_Principals.Get("PrincipalID", principalID);
  210. if (p != null && p.Length > 0)
  211. return p[0];
  212. return null;
  213. }
  214. public bool DeletePrincipal(string principalID)
  215. {
  216. return m_Principals.Delete("PrincipalID", principalID);
  217. }
  218. #endregion
  219. #region invites table
  220. public bool StoreInvitation(InvitationData data)
  221. {
  222. return m_Invites.Store(data);
  223. }
  224. public InvitationData RetrieveInvitation(UUID inviteID)
  225. {
  226. InvitationData[] invites = m_Invites.Get("InviteID", inviteID.ToString());
  227. if (invites != null && invites.Length > 0)
  228. return invites[0];
  229. return null;
  230. }
  231. public InvitationData RetrieveInvitation(UUID groupID, string principalID)
  232. {
  233. InvitationData[] invites = m_Invites.Get(new string[] { "GroupID", "PrincipalID" },
  234. new string[] { groupID.ToString(), principalID });
  235. if (invites != null && invites.Length > 0)
  236. return invites[0];
  237. return null;
  238. }
  239. public bool DeleteInvite(UUID inviteID)
  240. {
  241. return m_Invites.Delete("InviteID", inviteID.ToString());
  242. }
  243. public void DeleteOldInvites()
  244. {
  245. m_Invites.DeleteOld();
  246. }
  247. #endregion
  248. #region notices table
  249. public bool StoreNotice(NoticeData data)
  250. {
  251. return m_Notices.Store(data);
  252. }
  253. public NoticeData RetrieveNotice(UUID noticeID)
  254. {
  255. NoticeData[] notices = m_Notices.Get("NoticeID", noticeID.ToString());
  256. if (notices != null && notices.Length > 0)
  257. return notices[0];
  258. return null;
  259. }
  260. public NoticeData[] RetrieveNotices(UUID groupID)
  261. {
  262. NoticeData[] notices = m_Notices.Get("GroupID", groupID.ToString());
  263. return notices;
  264. }
  265. public bool DeleteNotice(UUID noticeID)
  266. {
  267. return m_Notices.Delete("NoticeID", noticeID.ToString());
  268. }
  269. public void DeleteOldNotices()
  270. {
  271. m_Notices.DeleteOld();
  272. }
  273. #endregion
  274. #region combinations
  275. public MembershipData RetrievePrincipalGroupMembership(string principalID, UUID groupID)
  276. {
  277. // TODO
  278. return null;
  279. }
  280. public MembershipData[] RetrievePrincipalGroupMemberships(string principalID)
  281. {
  282. // TODO
  283. return null;
  284. }
  285. #endregion
  286. }
  287. public class PGSqlGroupsGroupsHandler : PGSQLGenericTableHandler<GroupData>
  288. {
  289. protected override Assembly Assembly
  290. {
  291. // WARNING! Moving migrations to this assembly!!!
  292. get { return GetType().Assembly; }
  293. }
  294. public PGSqlGroupsGroupsHandler(string connectionString, string realm, string store)
  295. : base(connectionString, realm, store)
  296. {
  297. }
  298. }
  299. public class PGSqlGroupsMembershipHandler : PGSQLGenericTableHandler<MembershipData>
  300. {
  301. protected override Assembly Assembly
  302. {
  303. // WARNING! Moving migrations to this assembly!!!
  304. get { return GetType().Assembly; }
  305. }
  306. public PGSqlGroupsMembershipHandler(string connectionString, string realm)
  307. : base(connectionString, realm, string.Empty)
  308. {
  309. }
  310. }
  311. public class PGSqlGroupsRolesHandler : PGSQLGenericTableHandler<RoleData>
  312. {
  313. protected override Assembly Assembly
  314. {
  315. // WARNING! Moving migrations to this assembly!!!
  316. get { return GetType().Assembly; }
  317. }
  318. public PGSqlGroupsRolesHandler(string connectionString, string realm)
  319. : base(connectionString, realm, string.Empty)
  320. {
  321. }
  322. }
  323. public class PGSqlGroupsRoleMembershipHandler : PGSQLGenericTableHandler<RoleMembershipData>
  324. {
  325. protected override Assembly Assembly
  326. {
  327. // WARNING! Moving migrations to this assembly!!!
  328. get { return GetType().Assembly; }
  329. }
  330. public PGSqlGroupsRoleMembershipHandler(string connectionString, string realm)
  331. : base(connectionString, realm, string.Empty)
  332. {
  333. }
  334. }
  335. public class PGSqlGroupsInvitesHandler : PGSQLGenericTableHandler<InvitationData>
  336. {
  337. protected override Assembly Assembly
  338. {
  339. // WARNING! Moving migrations to this assembly!!!
  340. get { return GetType().Assembly; }
  341. }
  342. public PGSqlGroupsInvitesHandler(string connectionString, string realm)
  343. : base(connectionString, realm, string.Empty)
  344. {
  345. }
  346. public void DeleteOld()
  347. {
  348. using (NpgsqlCommand cmd = new NpgsqlCommand())
  349. {
  350. cmd.CommandText = String.Format("delete from {0} where \"TMStamp\"::abstime::timestamp < now() - INTERVAL '2 week'", m_Realm);
  351. ExecuteNonQuery(cmd);
  352. }
  353. }
  354. }
  355. public class PGSqlGroupsNoticesHandler : PGSQLGenericTableHandler<NoticeData>
  356. {
  357. protected override Assembly Assembly
  358. {
  359. // WARNING! Moving migrations to this assembly!!!
  360. get { return GetType().Assembly; }
  361. }
  362. public PGSqlGroupsNoticesHandler(string connectionString, string realm)
  363. : base(connectionString, realm, string.Empty)
  364. {
  365. }
  366. public void DeleteOld()
  367. {
  368. using (NpgsqlCommand cmd = new NpgsqlCommand())
  369. {
  370. cmd.CommandText = String.Format("delete from {0} where \"TMStamp\"::abstime::timestamp < now() - INTERVAL '2 week'", m_Realm);
  371. ExecuteNonQuery(cmd);
  372. }
  373. }
  374. }
  375. public class PGSqlGroupsPrincipalsHandler : PGSQLGenericTableHandler<PrincipalData>
  376. {
  377. protected override Assembly Assembly
  378. {
  379. // WARNING! Moving migrations to this assembly!!!
  380. get { return GetType().Assembly; }
  381. }
  382. public PGSqlGroupsPrincipalsHandler(string connectionString, string realm)
  383. : base(connectionString, realm, string.Empty)
  384. {
  385. }
  386. }
  387. }