aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL
diff options
context:
space:
mode:
authorDiva Canto2013-02-19 07:26:40 -0800
committerDiva Canto2013-02-19 07:26:40 -0800
commit9380d01976726885bd993573aa649f2cb0992909 (patch)
treee34fd4278fe75f1b01e3f16346bc83f15a16b383 /OpenSim/Data/MySQL
parentOffline IM: moved the Data and MySQL bits to the corresponding places in core... (diff)
downloadopensim-SC_OLD-9380d01976726885bd993573aa649f2cb0992909.zip
opensim-SC_OLD-9380d01976726885bd993573aa649f2cb0992909.tar.gz
opensim-SC_OLD-9380d01976726885bd993573aa649f2cb0992909.tar.bz2
opensim-SC_OLD-9380d01976726885bd993573aa649f2cb0992909.tar.xz
First commit of Diva Groups. The Data bits went to OpenSim.Data core, the rest to Addons.Groups.dll.
Diffstat (limited to 'OpenSim/Data/MySQL')
-rw-r--r--OpenSim/Data/MySQL/MySQLGroupsData.cs484
-rw-r--r--OpenSim/Data/MySQL/Resources/os_groups_Store.migrations115
2 files changed, 599 insertions, 0 deletions
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 @@
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
28using System;
29using System.Collections;
30using System.Collections.Generic;
31using System.Reflection;
32
33using OpenSim.Framework;
34using OpenSim.Data.MySQL;
35
36using OpenMetaverse;
37using MySql.Data.MySqlClient;
38
39namespace OpenSim.Data.MySQL
40{
41 public class MySQLGroupsData : IGroupsData
42 {
43 private MySqlGroupsGroupsHandler m_Groups;
44 private MySqlGroupsMembershipHandler m_Membership;
45 private MySqlGroupsRolesHandler m_Roles;
46 private MySqlGroupsRoleMembershipHandler m_RoleMembership;
47 private MySqlGroupsInvitesHandler m_Invites;
48 private MySqlGroupsNoticesHandler m_Notices;
49 private MySqlGroupsPrincipalsHandler m_Principals;
50
51 public MySQLGroupsData(string connectionString, string realm)
52 {
53 m_Groups = new MySqlGroupsGroupsHandler(connectionString, realm + "_groups", realm + "_Store");
54 m_Membership = new MySqlGroupsMembershipHandler(connectionString, realm + "_membership");
55 m_Roles = new MySqlGroupsRolesHandler(connectionString, realm + "_roles");
56 m_RoleMembership = new MySqlGroupsRoleMembershipHandler(connectionString, realm + "_rolemembership");
57 m_Invites = new MySqlGroupsInvitesHandler(connectionString, realm + "_invites");
58 m_Notices = new MySqlGroupsNoticesHandler(connectionString, realm + "_notices");
59 m_Principals = new MySqlGroupsPrincipalsHandler(connectionString, realm + "_principals");
60 }
61
62 #region groups table
63 public bool StoreGroup(GroupData data)
64 {
65 return m_Groups.Store(data);
66 }
67
68 public GroupData RetrieveGroup(UUID groupID)
69 {
70 GroupData[] groups = m_Groups.Get("GroupID", groupID.ToString());
71 if (groups.Length > 0)
72 return groups[0];
73
74 return null;
75 }
76
77 public GroupData RetrieveGroup(string name)
78 {
79 GroupData[] groups = m_Groups.Get("Name", name);
80 if (groups.Length > 0)
81 return groups[0];
82
83 return null;
84 }
85
86 public GroupData[] RetrieveGroups(string pattern)
87 {
88 if (string.IsNullOrEmpty(pattern))
89 pattern = "1 ORDER BY Name LIMIT 100";
90 else
91 pattern = string.Format("Name LIKE %{0}% ORDER BY Name LIMIT 100", pattern);
92
93 return m_Groups.Get(pattern);
94 }
95
96 public bool DeleteGroup(UUID groupID)
97 {
98 return m_Groups.Delete("GroupID", groupID.ToString());
99 }
100
101 public int GroupsCount()
102 {
103 return (int)m_Groups.GetCount("Location=\"\"");
104 }
105
106 #endregion
107
108 #region membership table
109 public MembershipData[] RetrieveMembers(UUID groupID)
110 {
111 return m_Membership.Get("GroupID", groupID.ToString());
112 }
113
114 public MembershipData RetrieveMember(UUID groupID, string pricipalID)
115 {
116 MembershipData[] m = m_Membership.Get(new string[] { "GroupID", "PrincipalID" },
117 new string[] { groupID.ToString(), pricipalID });
118 if (m != null && m.Length > 0)
119 return m[0];
120
121 return null;
122 }
123
124 public MembershipData[] RetrieveMemberships(string pricipalID)
125 {
126 return m_Membership.Get("PrincipalID", pricipalID.ToString());
127 }
128
129 public bool StoreMember(MembershipData data)
130 {
131 return m_Membership.Store(data);
132 }
133
134 public bool DeleteMember(UUID groupID, string pricipalID)
135 {
136 return m_Membership.Delete(new string[] { "GroupID", "PrincipalID" },
137 new string[] { groupID.ToString(), pricipalID });
138 }
139
140 public int MemberCount(UUID groupID)
141 {
142 return (int)m_Membership.GetCount("GroupID", groupID.ToString());
143 }
144 #endregion
145
146 #region roles table
147 public bool StoreRole(RoleData data)
148 {
149 return m_Roles.Store(data);
150 }
151
152 public RoleData RetrieveRole(UUID groupID, UUID roleID)
153 {
154 RoleData[] data = m_Roles.Get(new string[] { "GroupID", "RoleID" },
155 new string[] { groupID.ToString(), roleID.ToString() });
156
157 if (data != null && data.Length > 0)
158 return data[0];
159
160 return null;
161 }
162
163 public RoleData[] RetrieveRoles(UUID groupID)
164 {
165 //return m_Roles.RetrieveRoles(groupID);
166 return m_Roles.Get("GroupID", groupID.ToString());
167 }
168
169 public bool DeleteRole(UUID groupID, UUID roleID)
170 {
171 return m_Roles.Delete(new string[] { "GroupID", "RoleID" },
172 new string[] { groupID.ToString(), roleID.ToString() });
173 }
174
175 public int RoleCount(UUID groupID)
176 {
177 return (int)m_Roles.GetCount("GroupID", groupID.ToString());
178 }
179
180
181 #endregion
182
183 #region rolememberhip table
184 public RoleMembershipData[] RetrieveRolesMembers(UUID groupID)
185 {
186 RoleMembershipData[] data = m_RoleMembership.Get("GroupID", groupID.ToString());
187
188 return data;
189 }
190
191 public RoleMembershipData[] RetrieveRoleMembers(UUID groupID, UUID roleID)
192 {
193 RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID" },
194 new string[] { groupID.ToString(), roleID.ToString() });
195
196 return data;
197 }
198
199 public RoleMembershipData[] RetrieveMemberRoles(UUID groupID, string principalID)
200 {
201 RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "PrincipalID" },
202 new string[] { groupID.ToString(), principalID.ToString() });
203
204 return data;
205 }
206
207 public RoleMembershipData RetrieveRoleMember(UUID groupID, UUID roleID, string principalID)
208 {
209 RoleMembershipData[] data = m_RoleMembership.Get(new string[] { "GroupID", "RoleID", "PrincipalID" },
210 new string[] { groupID.ToString(), roleID.ToString(), principalID.ToString() });
211
212 if (data != null && data.Length > 0)
213 return data[0];
214
215 return null;
216 }
217
218 public int RoleMemberCount(UUID groupID, UUID roleID)
219 {
220 return (int)m_RoleMembership.GetCount(new string[] { "GroupID", "RoleID" },
221 new string[] { groupID.ToString(), roleID.ToString() });
222 }
223
224 public bool StoreRoleMember(RoleMembershipData data)
225 {
226 return m_RoleMembership.Store(data);
227 }
228
229 public bool DeleteRoleMember(RoleMembershipData data)
230 {
231 return m_RoleMembership.Delete(new string[] { "GroupID", "RoleID", "PrincipalID"},
232 new string[] { data.GroupID.ToString(), data.RoleID.ToString(), data.PrincipalID });
233 }
234
235 public bool DeleteMemberAllRoles(UUID groupID, string principalID)
236 {
237 return m_RoleMembership.Delete(new string[] { "GroupID", "PrincipalID" },
238 new string[] { groupID.ToString(), principalID });
239 }
240
241 #endregion
242
243 #region principals table
244 public bool StorePrincipal(PrincipalData data)
245 {
246 return m_Principals.Store(data);
247 }
248
249 public PrincipalData RetrievePrincipal(string principalID)
250 {
251 PrincipalData[] p = m_Principals.Get("PrincipalID", principalID);
252 if (p != null && p.Length > 0)
253 return p[0];
254
255 return null;
256 }
257
258 public bool DeletePrincipal(string principalID)
259 {
260 return m_Principals.Delete("PrincipalID", principalID);
261 }
262 #endregion
263
264 #region invites table
265
266 public bool StoreInvitation(InvitationData data)
267 {
268 return m_Invites.Store(data);
269 }
270
271 public InvitationData RetrieveInvitation(UUID inviteID)
272 {
273 InvitationData[] invites = m_Invites.Get("InviteID", inviteID.ToString());
274
275 if (invites != null && invites.Length > 0)
276 return invites[0];
277
278 return null;
279 }
280
281 public InvitationData RetrieveInvitation(UUID groupID, string principalID)
282 {
283 InvitationData[] invites = m_Invites.Get(new string[] { "GroupID", "PrincipalID" },
284 new string[] { groupID.ToString(), principalID });
285
286 if (invites != null && invites.Length > 0)
287 return invites[0];
288
289 return null;
290 }
291
292 public bool DeleteInvite(UUID inviteID)
293 {
294 return m_Invites.Delete("InviteID", inviteID.ToString());
295 }
296
297 public void DeleteOldInvites()
298 {
299 m_Invites.DeleteOld();
300 }
301
302 #endregion
303
304 #region notices table
305
306 public bool StoreNotice(NoticeData data)
307 {
308 return m_Notices.Store(data);
309 }
310
311 public NoticeData RetrieveNotice(UUID noticeID)
312 {
313 NoticeData[] notices = m_Notices.Get("NoticeID", noticeID.ToString());
314
315 if (notices != null && notices.Length > 0)
316 return notices[0];
317
318 return null;
319 }
320
321 public NoticeData[] RetrieveNotices(UUID groupID)
322 {
323 NoticeData[] notices = m_Notices.Get("GroupID", groupID.ToString());
324
325 return notices;
326 }
327
328 public bool DeleteNotice(UUID noticeID)
329 {
330 return m_Notices.Delete("NoticeID", noticeID.ToString());
331 }
332
333 public void DeleteOldNotices()
334 {
335 m_Notices.DeleteOld();
336 }
337
338 #endregion
339
340 #region combinations
341 public MembershipData RetrievePrincipalGroupMembership(string principalID, UUID groupID)
342 {
343 // TODO
344 return null;
345 }
346 public MembershipData[] RetrievePrincipalGroupMemberships(string principalID)
347 {
348 // TODO
349 return null;
350 }
351
352 #endregion
353 }
354
355 public class MySqlGroupsGroupsHandler : MySQLGenericTableHandler<GroupData>
356 {
357 protected override Assembly Assembly
358 {
359 // WARNING! Moving migrations to this assembly!!!
360 get { return GetType().Assembly; }
361 }
362
363 public MySqlGroupsGroupsHandler(string connectionString, string realm, string store)
364 : base(connectionString, realm, store)
365 {
366 }
367
368 }
369
370 public class MySqlGroupsMembershipHandler : MySQLGenericTableHandler<MembershipData>
371 {
372 protected override Assembly Assembly
373 {
374 // WARNING! Moving migrations to this assembly!!!
375 get { return GetType().Assembly; }
376 }
377
378 public MySqlGroupsMembershipHandler(string connectionString, string realm)
379 : base(connectionString, realm, string.Empty)
380 {
381 }
382
383 }
384
385 public class MySqlGroupsRolesHandler : MySQLGenericTableHandler<RoleData>
386 {
387 protected override Assembly Assembly
388 {
389 // WARNING! Moving migrations to this assembly!!!
390 get { return GetType().Assembly; }
391 }
392
393 public MySqlGroupsRolesHandler(string connectionString, string realm)
394 : base(connectionString, realm, string.Empty)
395 {
396 }
397
398 }
399
400 public class MySqlGroupsRoleMembershipHandler : MySQLGenericTableHandler<RoleMembershipData>
401 {
402 protected override Assembly Assembly
403 {
404 // WARNING! Moving migrations to this assembly!!!
405 get { return GetType().Assembly; }
406 }
407
408 public MySqlGroupsRoleMembershipHandler(string connectionString, string realm)
409 : base(connectionString, realm, string.Empty)
410 {
411 }
412
413 }
414
415 public class MySqlGroupsInvitesHandler : MySQLGenericTableHandler<InvitationData>
416 {
417 protected override Assembly Assembly
418 {
419 // WARNING! Moving migrations to this assembly!!!
420 get { return GetType().Assembly; }
421 }
422
423 public MySqlGroupsInvitesHandler(string connectionString, string realm)
424 : base(connectionString, realm, string.Empty)
425 {
426 }
427
428 public void DeleteOld()
429 {
430 uint now = (uint)Util.UnixTimeSinceEpoch();
431
432 using (MySqlCommand cmd = new MySqlCommand())
433 {
434 cmd.CommandText = String.Format("delete from {0} where TMStamp < ?tstamp", m_Realm);
435 cmd.Parameters.AddWithValue("?tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old
436
437 ExecuteNonQuery(cmd);
438 }
439
440 }
441 }
442
443 public class MySqlGroupsNoticesHandler : MySQLGenericTableHandler<NoticeData>
444 {
445 protected override Assembly Assembly
446 {
447 // WARNING! Moving migrations to this assembly!!!
448 get { return GetType().Assembly; }
449 }
450
451 public MySqlGroupsNoticesHandler(string connectionString, string realm)
452 : base(connectionString, realm, string.Empty)
453 {
454 }
455
456 public void DeleteOld()
457 {
458 uint now = (uint)Util.UnixTimeSinceEpoch();
459
460 using (MySqlCommand cmd = new MySqlCommand())
461 {
462 cmd.CommandText = String.Format("delete from {0} where TMStamp < ?tstamp", m_Realm);
463 cmd.Parameters.AddWithValue("?tstamp", now - 14 * 24 * 60 * 60); // > 2 weeks old
464
465 ExecuteNonQuery(cmd);
466 }
467
468 }
469 }
470
471 public class MySqlGroupsPrincipalsHandler : MySQLGenericTableHandler<PrincipalData>
472 {
473 protected override Assembly Assembly
474 {
475 // WARNING! Moving migrations to this assembly!!!
476 get { return GetType().Assembly; }
477 }
478
479 public MySqlGroupsPrincipalsHandler(string connectionString, string realm)
480 : base(connectionString, realm, string.Empty)
481 {
482 }
483 }
484}
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 @@
1:VERSION 1 # --------------------------
2
3BEGIN;
4
5CREATE TABLE `os_groups_groups` (
6 `GroupID` char(36) NOT NULL default '',
7 `Location` varchar(255) NOT NULL default '',
8 `Name` varchar(255) NOT NULL default '',
9 `Charter` text NOT NULL,
10 `InsigniaID` char(36) NOT NULL default '',
11 `FounderID` char(36) NOT NULL default '',
12 `MembershipFee` int(11) NOT NULL default '0',
13 `OpenEnrollment` varchar(255) NOT NULL default '',
14 `ShowInList` int(4) NOT NULL default '0',
15 `AllowPublish` int(4) NOT NULL default '0',
16 `MaturePublish` int(4) NOT NULL default '0',
17 `OwnerRoleID` char(36) NOT NULL default '',
18 PRIMARY KEY (`GroupID`),
19 UNIQUE KEY `Name` (`Name`),
20 FULLTEXT KEY `Name_2` (`Name`)
21) ENGINE=MyISAM;
22
23
24CREATE TABLE `os_groups_membership` (
25 `GroupID`char(36) NOT NULL default '',
26 `PrincipalID` VARCHAR(255) NOT NULL default '',
27 `SelectedRoleID` char(36) NOT NULL default '',
28 `Contribution` int(11) NOT NULL default '0',
29 `ListInProfile` int(4) NOT NULL default '1',
30 `AcceptNotices` int(4) NOT NULL default '1',
31 `AccessToken` char(36) NOT NULL default '',
32 PRIMARY KEY (`GroupID`,`PrincipalID`),
33 KEY `PrincipalID` (`PrincipalID`)
34) ENGINE=MyISAM;
35
36
37CREATE TABLE `os_groups_roles` (
38 `GroupID` char(36) NOT NULL default '',
39 `RoleID` char(36) NOT NULL default '',
40 `Name` varchar(255) NOT NULL default '',
41 `Description` varchar(255) NOT NULL default '',
42 `Title` varchar(255) NOT NULL default '',
43 `Powers` bigint(20) unsigned NOT NULL default '0',
44 PRIMARY KEY (`GroupID`,`RoleID`),
45 KEY `GroupID` (`GroupID`)
46) ENGINE=MyISAM;
47
48
49CREATE TABLE `os_groups_rolemembership` (
50 `GroupID` char(36) NOT NULL default '',
51 `RoleID` char(36) NOT NULL default '',
52 `PrincipalID` VARCHAR(255) NOT NULL default '',
53 PRIMARY KEY (`GroupID`,`RoleID`,`PrincipalID`),
54 KEY `PrincipalID` (`PrincipalID`)
55) ENGINE=MyISAM;
56
57
58CREATE TABLE `os_groups_invites` (
59 `InviteID` char(36) NOT NULL default '',
60 `GroupID` char(36) NOT NULL default '',
61 `RoleID` char(36) NOT NULL default '',
62 `PrincipalID` VARCHAR(255) NOT NULL default '',
63 `TMStamp` timestamp NOT NULL,
64 PRIMARY KEY (`InviteID`),
65 UNIQUE KEY `PrincipalGroup` (`GroupID`,`PrincipalID`)
66) ENGINE=MyISAM;
67
68
69CREATE TABLE `os_groups_notices` (
70 `GroupID` char(36) NOT NULL default '',
71 `NoticeID` char(36) NOT NULL default '',
72 `TMStamp` int(10) unsigned NOT NULL default '0',
73 `FromName` varchar(255) NOT NULL default '',
74 `Subject` varchar(255) NOT NULL default '',
75 `Message` text NOT NULL,
76 `HasAttachment` int(4) NOT NULL default '0',
77 `AttachmentType` int(4) NOT NULL default '0',
78 `AttachmentName` varchar(128) NOT NULL default '',
79 `AttachmentItemID` char(36) NOT NULL default '',
80 `AttachmentOwnerID` varchar(255) NOT NULL default '',
81 PRIMARY KEY (`NoticeID`),
82 KEY `GroupID` (`GroupID`),
83 KEY `TMStamp` (`TMStamp`)
84) ENGINE=MyISAM;
85
86CREATE TABLE `os_groups_principals` (
87 `PrincipalID` VARCHAR(255) NOT NULL default '',
88 `ActiveGroupID` char(36) NOT NULL default '',
89 PRIMARY KEY (`PrincipalID`)
90) ENGINE=MyISAM;
91
92COMMIT;
93
94:VERSION 2 # --------------------------
95
96BEGIN;
97
98INSERT INTO `os_groups_groups` SELECT * from `diva_groups_groups`;
99DROP TABLE `diva_groups_groups`;
100INSERT INTO `os_groups_membership` SELECT * from `diva_groups_membership`;
101DROP TABLE `diva_groups_membership`;
102INSERT INTO `os_groups_roles` SELECT * from `diva_groups_roles`;
103DROP TABLE `diva_groups_roles`;
104INSERT INTO `os_groups_rolemembership` SELECT * from `diva_groups_rolemembership`;
105DROP TABLE `diva_groups_rolemembership`;
106INSERT INTO `os_groups_invites` SELECT * from `diva_groups_invites`;
107DROP TABLE `diva_groups_invites`;
108INSERT INTO `os_groups_notices` SELECT * from `diva_groups_notices`;
109DROP TABLE `diva_groups_notices`;
110INSERT INTO `os_groups_principals` SELECT * from `diva_groups_principals`;
111DROP TABLE `diva_groups_principals`;
112
113DELETE FROM `migrations` WHERE name='diva_im_Store';
114
115COMMIT; \ No newline at end of file