1
0

PGSQLGroupsData.cs 16 KB

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