aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLUserData.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MySQL/MySQLUserData.cs')
-rw-r--r--OpenSim/Data/MySQL/MySQLUserData.cs643
1 files changed, 643 insertions, 0 deletions
diff --git a/OpenSim/Data/MySQL/MySQLUserData.cs b/OpenSim/Data/MySQL/MySQLUserData.cs
new file mode 100644
index 0000000..fd640ec
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLUserData.cs
@@ -0,0 +1,643 @@
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 OpenSim 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.Generic;
30using System.Data;
31using System.Text.RegularExpressions;
32using libsecondlife;
33using OpenSim.Framework.Console;
34
35namespace OpenSim.Framework.Data.MySQL
36{
37 /// <summary>
38 /// A database interface class to a user profile storage system
39 /// </summary>
40 internal class MySQLUserData : UserDataBase
41 {
42 private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
43
44 /// <summary>
45 /// Database manager for MySQL
46 /// </summary>
47 public MySQLManager database;
48
49 private string m_agentsTableName;
50 private string m_usersTableName;
51 private string m_userFriendsTableName;
52
53 /// <summary>
54 /// Loads and initialises the MySQL storage plugin
55 /// </summary>
56 override public void Initialise()
57 {
58 // Load from an INI file connection details
59 // TODO: move this to XML? Yes, PLEASE!
60
61 IniFile iniFile = new IniFile("mysql_connection.ini");
62 string settingHostname = iniFile.ParseFileReadValue("hostname");
63 string settingDatabase = iniFile.ParseFileReadValue("database");
64 string settingUsername = iniFile.ParseFileReadValue("username");
65 string settingPassword = iniFile.ParseFileReadValue("password");
66 string settingPooling = iniFile.ParseFileReadValue("pooling");
67 string settingPort = iniFile.ParseFileReadValue("port");
68
69 m_usersTableName = iniFile.ParseFileReadValue("userstablename");
70 if( m_usersTableName == null )
71 {
72 m_usersTableName = "users";
73 }
74
75 m_userFriendsTableName = iniFile.ParseFileReadValue("userfriendstablename");
76 if (m_userFriendsTableName == null)
77 {
78 m_userFriendsTableName = "userfriends";
79 }
80
81 m_agentsTableName = iniFile.ParseFileReadValue("agentstablename");
82 if (m_agentsTableName == null)
83 {
84 m_agentsTableName = "agents";
85 }
86
87 database =
88 new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling,
89 settingPort);
90
91 TestTables();
92 }
93
94 #region Test and initialization code
95
96 /// <summary>
97 /// Ensure that the user related tables exists and are at the latest version
98 /// </summary>
99 private void TestTables()
100 {
101 Dictionary<string, string> tableList = new Dictionary<string, string>();
102
103 tableList[m_agentsTableName] = null;
104 tableList[m_usersTableName] = null;
105 tableList[m_userFriendsTableName] = null;
106 database.GetTableVersion(tableList);
107
108 UpgradeAgentsTable(tableList[m_agentsTableName]);
109 UpgradeUsersTable(tableList[m_usersTableName]);
110 UpgradeFriendsTable(tableList[m_userFriendsTableName]);
111 }
112
113 /// <summary>
114 /// Create or upgrade the table if necessary
115 /// </summary>
116 /// <param name="oldVersion">A null indicates that the table does not
117 /// currently exist</param>
118 private void UpgradeAgentsTable(string oldVersion)
119 {
120 // null as the version, indicates that the table didn't exist
121 if (oldVersion == null)
122 {
123 database.ExecuteResourceSql("CreateAgentsTable.sql");
124 return;
125 }
126 }
127
128 /// <summary>
129 /// Create or upgrade the table if necessary
130 /// </summary>
131 /// <param name="oldVersion">A null indicates that the table does not
132 /// currently exist</param>
133 private void UpgradeUsersTable(string oldVersion)
134 {
135 // null as the version, indicates that the table didn't exist
136 if (oldVersion == null)
137 {
138 database.ExecuteResourceSql("CreateUsersTable.sql");
139 return;
140 }
141 else if (oldVersion.Contains("Rev. 1"))
142 {
143 database.ExecuteResourceSql("UpgradeUsersTableToVersion2.sql");
144 return;
145 }
146 //m_log.Info("[DB]: DBVers:" + oldVersion);
147 }
148
149 /// <summary>
150 /// Create or upgrade the table if necessary
151 /// </summary>
152 /// <param name="oldVersion">A null indicates that the table does not
153 /// currently exist</param>
154 private void UpgradeFriendsTable(string oldVersion)
155 {
156 // null as the version, indicates that the table didn't exist
157 if (oldVersion == null)
158 {
159 database.ExecuteResourceSql("CreateUserFriendsTable.sql");
160 return;
161 }
162 }
163
164 #endregion
165
166 // see IUserData
167 override public UserProfileData GetUserByName(string user, string last)
168 {
169 try
170 {
171 lock (database)
172 {
173 Dictionary<string, string> param = new Dictionary<string, string>();
174 param["?first"] = user;
175 param["?second"] = last;
176
177 IDbCommand result =
178 database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = ?first AND lastname = ?second", param);
179 IDataReader reader = result.ExecuteReader();
180
181 UserProfileData row = database.readUserRow(reader);
182
183 reader.Close();
184 result.Dispose();
185 return row;
186 }
187 }
188 catch (Exception e)
189 {
190 database.Reconnect();
191 m_log.Error(e.ToString());
192 return null;
193 }
194 }
195
196 #region User Friends List Data
197
198 override public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms)
199 {
200 int dtvalue = Util.UnixTimeSinceEpoch();
201
202 Dictionary<string, string> param = new Dictionary<string, string>();
203 param["?ownerID"] = friendlistowner.UUID.ToString();
204 param["?friendID"] = friend.UUID.ToString();
205 param["?friendPerms"] = perms.ToString();
206 param["?datetimestamp"] = dtvalue.ToString();
207
208 try
209 {
210 lock (database)
211 {
212 IDbCommand adder =
213 database.Query(
214 "INSERT INTO `" + m_userFriendsTableName + "` " +
215 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
216 "VALUES " +
217 "(?ownerID,?friendID,?friendPerms,?datetimestamp)",
218 param);
219 adder.ExecuteNonQuery();
220
221 adder =
222 database.Query(
223 "INSERT INTO `" + m_userFriendsTableName + "` " +
224 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
225 "VALUES " +
226 "(?friendID,?ownerID,?friendPerms,?datetimestamp)",
227 param);
228 adder.ExecuteNonQuery();
229 }
230 }
231 catch (Exception e)
232 {
233 database.Reconnect();
234 m_log.Error(e.ToString());
235 return;
236 }
237 }
238
239 override public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend)
240 {
241 Dictionary<string, string> param = new Dictionary<string, string>();
242 param["?ownerID"] = friendlistowner.UUID.ToString();
243 param["?friendID"] = friend.UUID.ToString();
244
245 try
246 {
247 lock (database)
248 {
249 IDbCommand updater =
250 database.Query(
251 "delete from " + m_userFriendsTableName + " where ownerID = ?ownerID and friendID = ?friendID",
252 param);
253 updater.ExecuteNonQuery();
254
255 updater =
256 database.Query(
257 "delete from " + m_userFriendsTableName + " where ownerID = ?friendID and friendID = ?ownerID",
258 param);
259 updater.ExecuteNonQuery();
260 }
261 }
262 catch (Exception e)
263 {
264 database.Reconnect();
265 m_log.Error(e.ToString());
266 return;
267 }
268 }
269
270 override public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms)
271 {
272 Dictionary<string, string> param = new Dictionary<string, string>();
273 param["?ownerID"] = friendlistowner.UUID.ToString();
274 param["?friendID"] = friend.UUID.ToString();
275 param["?friendPerms"] = perms.ToString();
276
277 try
278 {
279 lock (database)
280 {
281 IDbCommand updater =
282 database.Query(
283 "update " + m_userFriendsTableName +
284 " SET friendPerms = ?friendPerms " +
285 "where ownerID = ?ownerID and friendID = ?friendID",
286 param);
287 updater.ExecuteNonQuery();
288 }
289 }
290 catch (Exception e)
291 {
292 database.Reconnect();
293 m_log.Error(e.ToString());
294 return;
295 }
296 }
297
298 override public List<FriendListItem> GetUserFriendList(LLUUID friendlistowner)
299 {
300 List<FriendListItem> Lfli = new List<FriendListItem>();
301
302 Dictionary<string, string> param = new Dictionary<string, string>();
303 param["?ownerID"] = friendlistowner.UUID.ToString();
304
305 try
306 {
307 lock (database)
308 {
309 //Left Join userfriends to itself
310 IDbCommand result =
311 database.Query(
312 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" +
313 " where a.ownerID = ?ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID",
314 param);
315 IDataReader reader = result.ExecuteReader();
316
317 while (reader.Read())
318 {
319 FriendListItem fli = new FriendListItem();
320 fli.FriendListOwner = new LLUUID((string)reader["ownerID"]);
321 fli.Friend = new LLUUID((string)reader["friendID"]);
322 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
323
324 // This is not a real column in the database table, it's a joined column from the opposite record
325 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
326
327 Lfli.Add(fli);
328 }
329 reader.Close();
330 result.Dispose();
331 }
332 }
333 catch (Exception e)
334 {
335 database.Reconnect();
336 m_log.Error(e.ToString());
337 return Lfli;
338 }
339
340 return Lfli;
341 }
342
343 #endregion
344
345 override public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid)
346 {
347 m_log.Info("[USER]: Stub UpdateUserCUrrentRegion called");
348 }
349
350 override public List<Framework.AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
351 {
352 List<Framework.AvatarPickerAvatar> returnlist = new List<Framework.AvatarPickerAvatar>();
353
354 Regex objAlphaNumericPattern = new Regex("[^a-zA-Z0-9]");
355
356 string[] querysplit;
357 querysplit = query.Split(' ');
358 if (querysplit.Length == 2)
359 {
360 Dictionary<string, string> param = new Dictionary<string, string>();
361 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%";
362 param["?second"] = objAlphaNumericPattern.Replace(querysplit[1], String.Empty) + "%";
363 try
364 {
365 lock (database)
366 {
367 IDbCommand result =
368 database.Query(
369 "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first AND lastname like ?second LIMIT 100",
370 param);
371 IDataReader reader = result.ExecuteReader();
372
373 while (reader.Read())
374 {
375 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
376 user.AvatarID = new LLUUID((string) reader["UUID"]);
377 user.firstName = (string) reader["username"];
378 user.lastName = (string) reader["lastname"];
379 returnlist.Add(user);
380 }
381 reader.Close();
382 result.Dispose();
383 }
384 }
385 catch (Exception e)
386 {
387 database.Reconnect();
388 m_log.Error(e.ToString());
389 return returnlist;
390 }
391 }
392 else if (querysplit.Length == 1)
393 {
394 try
395 {
396 lock (database)
397 {
398 Dictionary<string, string> param = new Dictionary<string, string>();
399 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%";
400
401 IDbCommand result =
402 database.Query(
403 "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first OR lastname like ?first LIMIT 100",
404 param);
405 IDataReader reader = result.ExecuteReader();
406
407 while (reader.Read())
408 {
409 Framework.AvatarPickerAvatar user = new Framework.AvatarPickerAvatar();
410 user.AvatarID = new LLUUID((string) reader["UUID"]);
411 user.firstName = (string) reader["username"];
412 user.lastName = (string) reader["lastname"];
413 returnlist.Add(user);
414 }
415 reader.Close();
416 result.Dispose();
417 }
418 }
419 catch (Exception e)
420 {
421 database.Reconnect();
422 m_log.Error(e.ToString());
423 return returnlist;
424 }
425 }
426 return returnlist;
427 }
428
429 // see IUserData
430 override public UserProfileData GetUserByUUID(LLUUID uuid)
431 {
432 try
433 {
434 lock (database)
435 {
436 Dictionary<string, string> param = new Dictionary<string, string>();
437 param["?uuid"] = uuid.ToString();
438
439 IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = ?uuid", param);
440 IDataReader reader = result.ExecuteReader();
441
442 UserProfileData row = database.readUserRow(reader);
443
444 reader.Close();
445 result.Dispose();
446
447 return row;
448 }
449 }
450 catch (Exception e)
451 {
452 database.Reconnect();
453 m_log.Error(e.ToString());
454 return null;
455 }
456 }
457
458 /// <summary>
459 /// Returns a user session searching by name
460 /// </summary>
461 /// <param name="name">The account name</param>
462 /// <returns>The users session</returns>
463 override public UserAgentData GetAgentByName(string name)
464 {
465 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
466 }
467
468 /// <summary>
469 /// Returns a user session by account name
470 /// </summary>
471 /// <param name="user">First part of the users account name</param>
472 /// <param name="last">Second part of the users account name</param>
473 /// <returns>The users session</returns>
474 override public UserAgentData GetAgentByName(string user, string last)
475 {
476 UserProfileData profile = GetUserByName(user, last);
477 return GetAgentByUUID(profile.UUID);
478 }
479
480 override public void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey)
481 {
482 Dictionary<string, string> param = new Dictionary<string, string>();
483 param["?UUID"] = AgentID.UUID.ToString();
484 param["?webLoginKey"] = WebLoginKey.UUID.ToString();
485
486 try
487 {
488 lock (database)
489 {
490 IDbCommand updater =
491 database.Query(
492 "update " + m_usersTableName + " SET webLoginKey = ?webLoginKey " +
493 "where UUID = ?UUID",
494 param);
495 updater.ExecuteNonQuery();
496 }
497 }
498 catch (Exception e)
499 {
500 database.Reconnect();
501 m_log.Error(e.ToString());
502 return;
503 }
504 }
505
506 /// <summary>
507 /// Returns an agent session by account UUID
508 /// </summary>
509 /// <param name="uuid">The accounts UUID</param>
510 /// <returns>The users session</returns>
511 override public UserAgentData GetAgentByUUID(LLUUID uuid)
512 {
513 try
514 {
515 lock (database)
516 {
517 Dictionary<string, string> param = new Dictionary<string, string>();
518 param["?uuid"] = uuid.ToString();
519
520 IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = ?uuid", param);
521 IDataReader reader = result.ExecuteReader();
522
523 UserAgentData row = database.readAgentRow(reader);
524
525 reader.Close();
526 result.Dispose();
527
528 return row;
529 }
530 }
531 catch (Exception e)
532 {
533 database.Reconnect();
534 m_log.Error(e.ToString());
535 return null;
536 }
537 }
538
539 /// <summary>
540 /// Creates a new users profile
541 /// </summary>
542 /// <param name="user">The user profile to create</param>
543 override public void AddNewUserProfile(UserProfileData user)
544 {
545 try
546 {
547 lock (database)
548 {
549 database.insertUserRow(user.UUID, user.username, user.surname, user.passwordHash, user.passwordSalt,
550 user.homeRegion, user.homeLocation.X, user.homeLocation.Y,
551 user.homeLocation.Z,
552 user.homeLookAt.X, user.homeLookAt.Y, user.homeLookAt.Z, user.created,
553 user.lastLogin, user.userInventoryURI, user.userAssetURI,
554 user.profileCanDoMask, user.profileWantDoMask,
555 user.profileAboutText, user.profileFirstText, user.profileImage,
556 user.profileFirstImage, user.webLoginKey);
557 }
558 }
559 catch (Exception e)
560 {
561 database.Reconnect();
562 m_log.Error(e.ToString());
563 }
564 }
565
566 /// <summary>
567 /// Creates a new agent
568 /// </summary>
569 /// <param name="agent">The agent to create</param>
570 override public void AddNewUserAgent(UserAgentData agent)
571 {
572 try
573 {
574 lock (database)
575 {
576 database.insertAgentRow(agent);
577 }
578 }
579 catch (Exception e)
580 {
581 database.Reconnect();
582 m_log.Error(e.ToString());
583 }
584 }
585
586 /// <summary>
587 /// Updates a user profile stored in the DB
588 /// </summary>
589 /// <param name="user">The profile data to use to update the DB</param>
590 override public bool UpdateUserProfile(UserProfileData user)
591 {
592 database.updateUserRow(user.UUID, user.username, user.surname, user.passwordHash, user.passwordSalt,
593 user.homeRegion, user.homeLocation.X, user.homeLocation.Y, user.homeLocation.Z, user.homeLookAt.X,
594 user.homeLookAt.Y, user.homeLookAt.Z, user.created, user.lastLogin, user.userInventoryURI,
595 user.userAssetURI, user.profileCanDoMask, user.profileWantDoMask, user.profileAboutText,
596 user.profileFirstText, user.profileImage, user.profileFirstImage, user.webLoginKey);
597 return true;
598 }
599
600 /// <summary>
601 /// Performs a money transfer request between two accounts
602 /// </summary>
603 /// <param name="from">The senders account ID</param>
604 /// <param name="to">The receivers account ID</param>
605 /// <param name="amount">The amount to transfer</param>
606 /// <returns>Success?</returns>
607 override public bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount)
608 {
609 return false;
610 }
611
612 /// <summary>
613 /// Performs an inventory transfer request between two accounts
614 /// </summary>
615 /// <remarks>TODO: Move to inventory server</remarks>
616 /// <param name="from">The senders account ID</param>
617 /// <param name="to">The receivers account ID</param>
618 /// <param name="item">The item to transfer</param>
619 /// <returns>Success?</returns>
620 override public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item)
621 {
622 return false;
623 }
624
625 /// <summary>
626 /// Database provider name
627 /// </summary>
628 /// <returns>Provider name</returns>
629 override public string getName()
630 {
631 return "MySQL Userdata Interface";
632 }
633
634 /// <summary>
635 /// Database provider version
636 /// </summary>
637 /// <returns>provider version</returns>
638 override public string GetVersion()
639 {
640 return "0.1";
641 }
642 }
643}