aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL
diff options
context:
space:
mode:
authorAdam Frisby2008-08-30 13:38:46 +0000
committerAdam Frisby2008-08-30 13:38:46 +0000
commit0a5280edb513bc2556da6f784b2abf661133637c (patch)
tree4cb977395365025fb9e722edf8da2fed3ade6887 /OpenSim/Data/MySQL
parent* Ditto, UserServer/Main.cs (diff)
downloadopensim-SC-0a5280edb513bc2556da6f784b2abf661133637c.zip
opensim-SC-0a5280edb513bc2556da6f784b2abf661133637c.tar.gz
opensim-SC-0a5280edb513bc2556da6f784b2abf661133637c.tar.bz2
opensim-SC-0a5280edb513bc2556da6f784b2abf661133637c.tar.xz
* Added new "SuperManager" class for MySQL connections, for allowing multiple concurrent MySQL threads.
* Implemented SuperManager inside of UserData. This means the userserver when running on MySQL will use 10 connections (+1 system connection) to handle requests, preventing the previous mire of locking resulting in singlethreadedness. * This requires testing and grids relying on stability should not upgrade to this revision until it's been properly tested.
Diffstat (limited to '')
-rw-r--r--OpenSim/Data/MySQL/MySQLSuperManager.cs27
-rw-r--r--OpenSim/Data/MySQL/MySQLUserData.cs588
2 files changed, 379 insertions, 236 deletions
diff --git a/OpenSim/Data/MySQL/MySQLSuperManager.cs b/OpenSim/Data/MySQL/MySQLSuperManager.cs
new file mode 100644
index 0000000..848a0bd
--- /dev/null
+++ b/OpenSim/Data/MySQL/MySQLSuperManager.cs
@@ -0,0 +1,27 @@
1using System;
2using System.Collections.Generic;
3using System.Text;
4using System.Threading;
5
6namespace OpenSim.Data.MySQL
7{
8 class MySQLSuperManager
9 {
10 public bool Locked;
11 private Mutex m_lock;
12 public MySQLManager Manager;
13
14 public void GetLock()
15 {
16 Locked = true;
17 m_lock.WaitOne();
18 }
19
20 public void Release()
21 {
22 m_lock.ReleaseMutex();
23 Locked = false;
24 }
25
26 }
27}
diff --git a/OpenSim/Data/MySQL/MySQLUserData.cs b/OpenSim/Data/MySQL/MySQLUserData.cs
index e04d36a..664203a 100644
--- a/OpenSim/Data/MySQL/MySQLUserData.cs
+++ b/OpenSim/Data/MySQL/MySQLUserData.cs
@@ -34,7 +34,6 @@ using System.Text.RegularExpressions;
34using libsecondlife; 34using libsecondlife;
35using log4net; 35using log4net;
36using OpenSim.Framework; 36using OpenSim.Framework;
37using OpenSim.Data.Base;
38using MySql.Data.MySqlClient; 37using MySql.Data.MySqlClient;
39 38
40namespace OpenSim.Data.MySQL 39namespace OpenSim.Data.MySQL
@@ -51,6 +50,14 @@ namespace OpenSim.Data.MySQL
51 /// </summary> 50 /// </summary>
52 public MySQLManager database; 51 public MySQLManager database;
53 52
53 /// <summary>
54 /// Better DB manager. Swap-in replacement too.
55 /// </summary>
56 public Dictionary<int, MySQLSuperManager> m_dbconnections = new Dictionary<int, MySQLSuperManager>();
57
58 public int m_maxConnections = 10;
59 public int m_lastConnect;
60
54 private string m_agentsTableName; 61 private string m_agentsTableName;
55 private string m_usersTableName; 62 private string m_usersTableName;
56 private string m_userFriendsTableName; 63 private string m_userFriendsTableName;
@@ -60,7 +67,28 @@ namespace OpenSim.Data.MySQL
60 public override void Initialise() 67 public override void Initialise()
61 { 68 {
62 m_log.Info("[MySQLUserData]: " + Name + " cannot be default-initialized!"); 69 m_log.Info("[MySQLUserData]: " + Name + " cannot be default-initialized!");
63 throw new PluginNotInitialisedException (Name); 70 throw new PluginNotInitialisedException(Name);
71 }
72
73 public MySQLSuperManager GetLockedConnection()
74 {
75 while (true)
76 {
77 m_lastConnect++;
78 MySQLSuperManager x = m_dbconnections[m_lastConnect%m_maxConnections];
79 if (!x.Locked)
80 {
81 x.GetLock();
82 return x;
83 }
84 if (m_lastConnect > m_maxConnections)
85 {
86 m_lastConnect = 0;
87 System.Threading.Thread.Sleep(1000); // Wait some time before searching them again.
88 m_log.Debug(
89 "WARNING: All threads are in use. Probable cause: Something didnt release a mutex properly, or high volume of requests inbound.");
90 }
91 }
64 } 92 }
65 93
66 /// <summary> 94 /// <summary>
@@ -70,9 +98,10 @@ namespace OpenSim.Data.MySQL
70 /// Checks for migration 98 /// Checks for migration
71 /// </summary> 99 /// </summary>
72 /// <param name="connect">connect string.</param> 100 /// <param name="connect">connect string.</param>
73 override public void Initialise(string connect) 101 public override void Initialise(string connect)
74 { 102 {
75 if (connect == String.Empty) { 103 if (connect == String.Empty)
104 {
76 // TODO: actually do something with our connect string 105 // TODO: actually do something with our connect string
77 // instead of loading the second config 106 // instead of loading the second config
78 107
@@ -103,8 +132,18 @@ namespace OpenSim.Data.MySQL
103 m_agentsTableName = "agents"; 132 m_agentsTableName = "agents";
104 } 133 }
105 134
106 m_connectString = "Server=" + settingHostname + ";Port=" + settingPort + ";Database=" + settingDatabase + ";User ID=" + 135 m_connectString = "Server=" + settingHostname + ";Port=" + settingPort + ";Database=" + settingDatabase +
107 settingUsername + ";Password=" + settingPassword + ";Pooling=" + settingPooling + ";"; 136 ";User ID=" +
137 settingUsername + ";Password=" + settingPassword + ";Pooling=" + settingPooling + ";";
138
139 m_log.Info("Creating " + m_maxConnections + " DB connections...");
140 for (int i = 0; i < m_maxConnections; i++)
141 {
142 m_log.Info("Connecting to DB... [" + i + "]");
143 MySQLSuperManager msm = new MySQLSuperManager();
144 msm.Manager = new MySQLManager(m_connectString);
145 m_dbconnections.Add(i, msm);
146 }
108 147
109 database = new MySQLManager(m_connectString); 148 database = new MySQLManager(m_connectString);
110 } 149 }
@@ -124,42 +163,50 @@ namespace OpenSim.Data.MySQL
124 m.Update(); 163 m.Update();
125 } 164 }
126 165
127 public override void Dispose () { } 166 public override void Dispose()
167 {
168 }
128 169
129 // see IUserDataPlugin 170 // see IUserDataPlugin
130 override public UserProfileData GetUserByName(string user, string last) 171 public override UserProfileData GetUserByName(string user, string last)
131 { 172 {
173 MySQLSuperManager dbm = GetLockedConnection();
174
132 try 175 try
133 { 176 {
134 lock (database) 177 Dictionary<string, string> param = new Dictionary<string, string>();
135 { 178 param["?first"] = user;
136 Dictionary<string, string> param = new Dictionary<string, string>(); 179 param["?second"] = last;
137 param["?first"] = user;
138 param["?second"] = last;
139 180
140 IDbCommand result = 181 IDbCommand result =
141 database.Query("SELECT * FROM " + m_usersTableName + " WHERE username = ?first AND lastname = ?second", param); 182 dbm.Manager.Query(
142 IDataReader reader = result.ExecuteReader(); 183 "SELECT * FROM " + m_usersTableName + " WHERE username = ?first AND lastname = ?second", param);
184 IDataReader reader = result.ExecuteReader();
143 185
144 UserProfileData row = database.readUserRow(reader); 186 UserProfileData row = dbm.Manager.readUserRow(reader);
145 187
146 reader.Dispose(); 188 reader.Dispose();
147 result.Dispose(); 189 result.Dispose();
148 return row; 190 return row;
149 }
150 } 191 }
151 catch (Exception e) 192 catch (Exception e)
152 { 193 {
153 database.Reconnect(); 194 dbm.Manager.Reconnect();
154 m_log.Error(e.ToString()); 195 m_log.Error(e.ToString());
155 return null; 196 return null;
156 } 197 }
198 finally
199 {
200 dbm.Release();
201 }
157 } 202 }
158 203
159 #region User Friends List Data 204 #region User Friends List Data
160 205
161 override public void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms) 206 public override void AddNewUserFriend(LLUUID friendlistowner, LLUUID friend, uint perms)
162 { 207 {
208 MySQLSuperManager dbm = GetLockedConnection();
209
163 int dtvalue = Util.UnixTimeSinceEpoch(); 210 int dtvalue = Util.UnixTimeSinceEpoch();
164 211
165 Dictionary<string, string> param = new Dictionary<string, string>(); 212 Dictionary<string, string> param = new Dictionary<string, string>();
@@ -170,68 +217,74 @@ namespace OpenSim.Data.MySQL
170 217
171 try 218 try
172 { 219 {
173 lock (database) 220 IDbCommand adder =
174 { 221 dbm.Manager.Query(
175 IDbCommand adder =
176 database.Query(
177 "INSERT INTO `" + m_userFriendsTableName + "` " + 222 "INSERT INTO `" + m_userFriendsTableName + "` " +
178 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + 223 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
179 "VALUES " + 224 "VALUES " +
180 "(?ownerID,?friendID,?friendPerms,?datetimestamp)", 225 "(?ownerID,?friendID,?friendPerms,?datetimestamp)",
181 param); 226 param);
182 adder.ExecuteNonQuery(); 227 adder.ExecuteNonQuery();
183 228
184 adder = 229 adder =
185 database.Query( 230 dbm.Manager.Query(
186 "INSERT INTO `" + m_userFriendsTableName + "` " + 231 "INSERT INTO `" + m_userFriendsTableName + "` " +
187 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + 232 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
188 "VALUES " + 233 "VALUES " +
189 "(?friendID,?ownerID,?friendPerms,?datetimestamp)", 234 "(?friendID,?ownerID,?friendPerms,?datetimestamp)",
190 param); 235 param);
191 adder.ExecuteNonQuery(); 236 adder.ExecuteNonQuery();
192 }
193 } 237 }
194 catch (Exception e) 238 catch (Exception e)
195 { 239 {
196 database.Reconnect(); 240 dbm.Manager.Reconnect();
197 m_log.Error(e.ToString()); 241 m_log.Error(e.ToString());
198 return; 242 return;
199 } 243 }
244 finally
245 {
246 dbm.Release();
247 }
200 } 248 }
201 249
202 override public void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend) 250 public override void RemoveUserFriend(LLUUID friendlistowner, LLUUID friend)
203 { 251 {
252 MySQLSuperManager dbm = GetLockedConnection();
253
204 Dictionary<string, string> param = new Dictionary<string, string>(); 254 Dictionary<string, string> param = new Dictionary<string, string>();
205 param["?ownerID"] = friendlistowner.UUID.ToString(); 255 param["?ownerID"] = friendlistowner.UUID.ToString();
206 param["?friendID"] = friend.UUID.ToString(); 256 param["?friendID"] = friend.UUID.ToString();
207 257
208 try 258 try
209 { 259 {
210 lock (database) 260 IDbCommand updater =
211 { 261 dbm.Manager.Query(
212 IDbCommand updater = 262 "delete from " + m_userFriendsTableName + " where ownerID = ?ownerID and friendID = ?friendID",
213 database.Query( 263 param);
214 "delete from " + m_userFriendsTableName + " where ownerID = ?ownerID and friendID = ?friendID", 264 updater.ExecuteNonQuery();
215 param); 265
216 updater.ExecuteNonQuery(); 266 updater =
217 267 dbm.Manager.Query(
218 updater = 268 "delete from " + m_userFriendsTableName + " where ownerID = ?friendID and friendID = ?ownerID",
219 database.Query( 269 param);
220 "delete from " + m_userFriendsTableName + " where ownerID = ?friendID and friendID = ?ownerID", 270 updater.ExecuteNonQuery();
221 param);
222 updater.ExecuteNonQuery();
223 }
224 } 271 }
225 catch (Exception e) 272 catch (Exception e)
226 { 273 {
227 database.Reconnect(); 274 dbm.Manager.Reconnect();
228 m_log.Error(e.ToString()); 275 m_log.Error(e.ToString());
229 return; 276 return;
230 } 277 }
278 finally
279 {
280 dbm.Release();
281 }
231 } 282 }
232 283
233 override public void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms) 284 public override void UpdateUserFriendPerms(LLUUID friendlistowner, LLUUID friend, uint perms)
234 { 285 {
286 MySQLSuperManager dbm = GetLockedConnection();
287
235 Dictionary<string, string> param = new Dictionary<string, string>(); 288 Dictionary<string, string> param = new Dictionary<string, string>();
236 param["?ownerID"] = friendlistowner.UUID.ToString(); 289 param["?ownerID"] = friendlistowner.UUID.ToString();
237 param["?friendID"] = friend.UUID.ToString(); 290 param["?friendID"] = friend.UUID.ToString();
@@ -239,27 +292,29 @@ namespace OpenSim.Data.MySQL
239 292
240 try 293 try
241 { 294 {
242 lock (database) 295 IDbCommand updater =
243 { 296 dbm.Manager.Query(
244 IDbCommand updater = 297 "update " + m_userFriendsTableName +
245 database.Query( 298 " SET friendPerms = ?friendPerms " +
246 "update " + m_userFriendsTableName + 299 "where ownerID = ?ownerID and friendID = ?friendID",
247 " SET friendPerms = ?friendPerms " + 300 param);
248 "where ownerID = ?ownerID and friendID = ?friendID", 301 updater.ExecuteNonQuery();
249 param);
250 updater.ExecuteNonQuery();
251 }
252 } 302 }
253 catch (Exception e) 303 catch (Exception e)
254 { 304 {
255 database.Reconnect(); 305 dbm.Manager.Reconnect();
256 m_log.Error(e.ToString()); 306 m_log.Error(e.ToString());
257 return; 307 return;
258 } 308 }
309 finally
310 {
311 dbm.Release();
312 }
259 } 313 }
260 314
261 override public List<FriendListItem> GetUserFriendList(LLUUID friendlistowner) 315 public override List<FriendListItem> GetUserFriendList(LLUUID friendlistowner)
262 { 316 {
317 MySQLSuperManager dbm = GetLockedConnection();
263 List<FriendListItem> Lfli = new List<FriendListItem>(); 318 List<FriendListItem> Lfli = new List<FriendListItem>();
264 319
265 Dictionary<string, string> param = new Dictionary<string, string>(); 320 Dictionary<string, string> param = new Dictionary<string, string>();
@@ -267,52 +322,56 @@ namespace OpenSim.Data.MySQL
267 322
268 try 323 try
269 { 324 {
270 lock (database) 325 //Left Join userfriends to itself
326 IDbCommand result =
327 dbm.Manager.Query(
328 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " +
329 m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" +
330 " where a.ownerID = ?ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID",
331 param);
332 IDataReader reader = result.ExecuteReader();
333
334 while (reader.Read())
271 { 335 {
272 //Left Join userfriends to itself 336 FriendListItem fli = new FriendListItem();
273 IDbCommand result = 337 fli.FriendListOwner = new LLUUID((string) reader["ownerID"]);
274 database.Query( 338 fli.Friend = new LLUUID((string) reader["friendID"]);
275 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" + 339 fli.FriendPerms = (uint) Convert.ToInt32(reader["friendPerms"]);
276 " where a.ownerID = ?ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID",
277 param);
278 IDataReader reader = result.ExecuteReader();
279
280 while (reader.Read())
281 {
282 FriendListItem fli = new FriendListItem();
283 fli.FriendListOwner = new LLUUID((string)reader["ownerID"]);
284 fli.Friend = new LLUUID((string)reader["friendID"]);
285 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
286 340
287 // This is not a real column in the database table, it's a joined column from the opposite record 341 // This is not a real column in the database table, it's a joined column from the opposite record
288 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]); 342 fli.FriendListOwnerPerms = (uint) Convert.ToInt32(reader["ownerperms"]);
289 343
290 Lfli.Add(fli); 344 Lfli.Add(fli);
291 }
292
293 reader.Dispose();
294 result.Dispose();
295 } 345 }
346
347 reader.Dispose();
348 result.Dispose();
296 } 349 }
297 catch (Exception e) 350 catch (Exception e)
298 { 351 {
299 database.Reconnect(); 352 dbm.Manager.Reconnect();
300 m_log.Error(e.ToString()); 353 m_log.Error(e.ToString());
301 return Lfli; 354 return Lfli;
302 } 355 }
356 finally
357 {
358 dbm.Release();
359 }
303 360
304 return Lfli; 361 return Lfli;
305 } 362 }
306 363
307 #endregion 364 #endregion
308 365
309 override public void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid, ulong regionhandle) 366 public override void UpdateUserCurrentRegion(LLUUID avatarid, LLUUID regionuuid, ulong regionhandle)
310 { 367 {
311 //m_log.Info("[USER DB]: Stub UpdateUserCUrrentRegion called"); 368 //m_log.Info("[USER DB]: Stub UpdateUserCUrrentRegion called");
312 } 369 }
313 370
314 override public List<AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query) 371 public override List<AvatarPickerAvatar> GeneratePickerResults(LLUUID queryID, string query)
315 { 372 {
373 MySQLSuperManager dbm = GetLockedConnection();
374
316 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>(); 375 List<AvatarPickerAvatar> returnlist = new List<AvatarPickerAvatar>();
317 376
318 Regex objAlphaNumericPattern = new Regex("[^a-zA-Z0-9]"); 377 Regex objAlphaNumericPattern = new Regex("[^a-zA-Z0-9]");
@@ -326,66 +385,70 @@ namespace OpenSim.Data.MySQL
326 param["?second"] = objAlphaNumericPattern.Replace(querysplit[1], String.Empty) + "%"; 385 param["?second"] = objAlphaNumericPattern.Replace(querysplit[1], String.Empty) + "%";
327 try 386 try
328 { 387 {
329 lock (database) 388 IDbCommand result =
389 dbm.Manager.Query(
390 "SELECT UUID,username,lastname FROM " + m_usersTableName +
391 " WHERE username like ?first AND lastname like ?second LIMIT 100",
392 param);
393 IDataReader reader = result.ExecuteReader();
394
395 while (reader.Read())
330 { 396 {
331 IDbCommand result = 397 AvatarPickerAvatar user = new AvatarPickerAvatar();
332 database.Query( 398 user.AvatarID = new LLUUID((string) reader["UUID"]);
333 "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first AND lastname like ?second LIMIT 100", 399 user.firstName = (string) reader["username"];
334 param); 400 user.lastName = (string) reader["lastname"];
335 IDataReader reader = result.ExecuteReader(); 401 returnlist.Add(user);
336
337 while (reader.Read())
338 {
339 AvatarPickerAvatar user = new AvatarPickerAvatar();
340 user.AvatarID = new LLUUID((string) reader["UUID"]);
341 user.firstName = (string) reader["username"];
342 user.lastName = (string) reader["lastname"];
343 returnlist.Add(user);
344 }
345 reader.Dispose();
346 result.Dispose();
347 } 402 }
403 reader.Dispose();
404 result.Dispose();
348 } 405 }
349 catch (Exception e) 406 catch (Exception e)
350 { 407 {
351 database.Reconnect(); 408 dbm.Manager.Reconnect();
352 m_log.Error(e.ToString()); 409 m_log.Error(e.ToString());
353 return returnlist; 410 return returnlist;
354 } 411 }
412 finally
413 {
414 dbm.Release();
415 }
355 } 416 }
356 else if (querysplit.Length == 1) 417 else if (querysplit.Length == 1)
357 { 418 {
358 try 419 try
359 { 420 {
360 lock (database) 421 Dictionary<string, string> param = new Dictionary<string, string>();
422 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%";
423
424 IDbCommand result =
425 dbm.Manager.Query(
426 "SELECT UUID,username,lastname FROM " + m_usersTableName +
427 " WHERE username like ?first OR lastname like ?first LIMIT 100",
428 param);
429 IDataReader reader = result.ExecuteReader();
430
431 while (reader.Read())
361 { 432 {
362 Dictionary<string, string> param = new Dictionary<string, string>(); 433 AvatarPickerAvatar user = new AvatarPickerAvatar();
363 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%"; 434 user.AvatarID = new LLUUID((string) reader["UUID"]);
364 435 user.firstName = (string) reader["username"];
365 IDbCommand result = 436 user.lastName = (string) reader["lastname"];
366 database.Query( 437 returnlist.Add(user);
367 "SELECT UUID,username,lastname FROM " + m_usersTableName + " WHERE username like ?first OR lastname like ?first LIMIT 100",
368 param);
369 IDataReader reader = result.ExecuteReader();
370
371 while (reader.Read())
372 {
373 AvatarPickerAvatar user = new AvatarPickerAvatar();
374 user.AvatarID = new LLUUID((string) reader["UUID"]);
375 user.firstName = (string) reader["username"];
376 user.lastName = (string) reader["lastname"];
377 returnlist.Add(user);
378 }
379 reader.Dispose();
380 result.Dispose();
381 } 438 }
439 reader.Dispose();
440 result.Dispose();
382 } 441 }
383 catch (Exception e) 442 catch (Exception e)
384 { 443 {
385 database.Reconnect(); 444 dbm.Manager.Reconnect();
386 m_log.Error(e.ToString()); 445 m_log.Error(e.ToString());
387 return returnlist; 446 return returnlist;
388 } 447 }
448 finally
449 {
450 dbm.Release();
451 }
389 } 452 }
390 return returnlist; 453 return returnlist;
391 } 454 }
@@ -395,32 +458,34 @@ namespace OpenSim.Data.MySQL
395 /// </summary> 458 /// </summary>
396 /// <param name="uuid">User UUID</param> 459 /// <param name="uuid">User UUID</param>
397 /// <returns>User profile data</returns> 460 /// <returns>User profile data</returns>
398 override public UserProfileData GetUserByUUID(LLUUID uuid) 461 public override UserProfileData GetUserByUUID(LLUUID uuid)
399 { 462 {
463 MySQLSuperManager dbm = GetLockedConnection();
400 try 464 try
401 { 465 {
402 lock (database) 466 Dictionary<string, string> param = new Dictionary<string, string>();
403 { 467 param["?uuid"] = uuid.ToString();
404 Dictionary<string, string> param = new Dictionary<string, string>();
405 param["?uuid"] = uuid.ToString();
406 468
407 IDbCommand result = database.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = ?uuid", param); 469 IDbCommand result = dbm.Manager.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = ?uuid", param);
408 IDataReader reader = result.ExecuteReader(); 470 IDataReader reader = result.ExecuteReader();
409 471
410 UserProfileData row = database.readUserRow(reader); 472 UserProfileData row = dbm.Manager.readUserRow(reader);
411 473
412 reader.Dispose(); 474 reader.Dispose();
413 result.Dispose(); 475 result.Dispose();
414 476
415 return row; 477 return row;
416 }
417 } 478 }
418 catch (Exception e) 479 catch (Exception e)
419 { 480 {
420 database.Reconnect(); 481 dbm.Manager.Reconnect();
421 m_log.Error(e.ToString()); 482 m_log.Error(e.ToString());
422 return null; 483 return null;
423 } 484 }
485 finally
486 {
487 dbm.Release();
488 }
424 } 489 }
425 490
426 /// <summary> 491 /// <summary>
@@ -428,7 +493,7 @@ namespace OpenSim.Data.MySQL
428 /// </summary> 493 /// </summary>
429 /// <param name="name">The account name : "Username Lastname"</param> 494 /// <param name="name">The account name : "Username Lastname"</param>
430 /// <returns>The users session</returns> 495 /// <returns>The users session</returns>
431 override public UserAgentData GetAgentByName(string name) 496 public override UserAgentData GetAgentByName(string name)
432 { 497 {
433 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]); 498 return GetAgentByName(name.Split(' ')[0], name.Split(' ')[1]);
434 } 499 }
@@ -439,7 +504,7 @@ namespace OpenSim.Data.MySQL
439 /// <param name="user">First part of the users account name</param> 504 /// <param name="user">First part of the users account name</param>
440 /// <param name="last">Second part of the users account name</param> 505 /// <param name="last">Second part of the users account name</param>
441 /// <returns>The users session</returns> 506 /// <returns>The users session</returns>
442 override public UserAgentData GetAgentByName(string user, string last) 507 public override UserAgentData GetAgentByName(string user, string last)
443 { 508 {
444 UserProfileData profile = GetUserByName(user, last); 509 UserProfileData profile = GetUserByName(user, last);
445 return GetAgentByUUID(profile.ID); 510 return GetAgentByUUID(profile.ID);
@@ -450,30 +515,33 @@ namespace OpenSim.Data.MySQL
450 /// <param name="AgentID"></param> 515 /// <param name="AgentID"></param>
451 /// <param name="WebLoginKey"></param> 516 /// <param name="WebLoginKey"></param>
452 /// <remarks>is it still used ?</remarks> 517 /// <remarks>is it still used ?</remarks>
453 override public void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey) 518 public override void StoreWebLoginKey(LLUUID AgentID, LLUUID WebLoginKey)
454 { 519 {
520 MySQLSuperManager dbm = GetLockedConnection();
521
455 Dictionary<string, string> param = new Dictionary<string, string>(); 522 Dictionary<string, string> param = new Dictionary<string, string>();
456 param["?UUID"] = AgentID.UUID.ToString(); 523 param["?UUID"] = AgentID.UUID.ToString();
457 param["?webLoginKey"] = WebLoginKey.UUID.ToString(); 524 param["?webLoginKey"] = WebLoginKey.UUID.ToString();
458 525
459 try 526 try
460 { 527 {
461 lock (database) 528 IDbCommand updater =
462 { 529 dbm.Manager.Query(
463 IDbCommand updater = 530 "update " + m_usersTableName + " SET webLoginKey = ?webLoginKey " +
464 database.Query( 531 "where UUID = ?UUID",
465 "update " + m_usersTableName + " SET webLoginKey = ?webLoginKey " + 532 param);
466 "where UUID = ?UUID", 533 updater.ExecuteNonQuery();
467 param);
468 updater.ExecuteNonQuery();
469 }
470 } 534 }
471 catch (Exception e) 535 catch (Exception e)
472 { 536 {
473 database.Reconnect(); 537 dbm.Manager.Reconnect();
474 m_log.Error(e.ToString()); 538 m_log.Error(e.ToString());
475 return; 539 return;
476 } 540 }
541 finally
542 {
543 dbm.Release();
544 }
477 } 545 }
478 546
479 /// <summary> 547 /// <summary>
@@ -481,94 +549,111 @@ namespace OpenSim.Data.MySQL
481 /// </summary> 549 /// </summary>
482 /// <param name="uuid">The accounts UUID</param> 550 /// <param name="uuid">The accounts UUID</param>
483 /// <returns>The users session</returns> 551 /// <returns>The users session</returns>
484 override public UserAgentData GetAgentByUUID(LLUUID uuid) 552 public override UserAgentData GetAgentByUUID(LLUUID uuid)
485 { 553 {
554 MySQLSuperManager dbm = GetLockedConnection();
555
486 try 556 try
487 { 557 {
488 lock (database) 558 Dictionary<string, string> param = new Dictionary<string, string>();
489 { 559 param["?uuid"] = uuid.ToString();
490 Dictionary<string, string> param = new Dictionary<string, string>();
491 param["?uuid"] = uuid.ToString();
492 560
493 IDbCommand result = database.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = ?uuid", param); 561 IDbCommand result = dbm.Manager.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = ?uuid",
494 IDataReader reader = result.ExecuteReader(); 562 param);
563 IDataReader reader = result.ExecuteReader();
495 564
496 UserAgentData row = database.readAgentRow(reader); 565 UserAgentData row = dbm.Manager.readAgentRow(reader);
497 566
498 reader.Dispose(); 567 reader.Dispose();
499 result.Dispose(); 568 result.Dispose();
500 569
501 return row; 570 return row;
502 }
503 } 571 }
504 catch (Exception e) 572 catch (Exception e)
505 { 573 {
506 database.Reconnect(); 574 dbm.Manager.Reconnect();
507 m_log.Error(e.ToString()); 575 m_log.Error(e.ToString());
508 return null; 576 return null;
509 } 577 }
578 finally
579 {
580 dbm.Release();
581 }
510 } 582 }
511 583
512 /// <summary> 584 /// <summary>
513 /// Creates a new users profile 585 /// Creates a new users profile
514 /// </summary> 586 /// </summary>
515 /// <param name="user">The user profile to create</param> 587 /// <param name="user">The user profile to create</param>
516 override public void AddNewUserProfile(UserProfileData user) 588 public override void AddNewUserProfile(UserProfileData user)
517 { 589 {
590 MySQLSuperManager dbm = GetLockedConnection();
591
518 try 592 try
519 { 593 {
520 lock (database) 594 dbm.Manager.insertUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt,
521 { 595 user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y,
522 database.insertUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt, 596 user.HomeLocation.Z,
523 user.HomeRegion, user.HomeLocation.X, user.HomeLocation.Y, 597 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created,
524 user.HomeLocation.Z, 598 user.LastLogin, user.UserInventoryURI, user.UserAssetURI,
525 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, 599 user.CanDoMask, user.WantDoMask,
526 user.LastLogin, user.UserInventoryURI, user.UserAssetURI, 600 user.AboutText, user.FirstLifeAboutText, user.Image,
527 user.CanDoMask, user.WantDoMask, 601 user.FirstLifeImage, user.WebLoginKey);
528 user.AboutText, user.FirstLifeAboutText, user.Image,
529 user.FirstLifeImage, user.WebLoginKey);
530 }
531 } 602 }
532 catch (Exception e) 603 catch (Exception e)
533 { 604 {
534 database.Reconnect(); 605 dbm.Manager.Reconnect();
535 m_log.Error(e.ToString()); 606 m_log.Error(e.ToString());
536 } 607 }
608 finally
609 {
610 dbm.Release();
611 }
537 } 612 }
538 613
539 /// <summary> 614 /// <summary>
540 /// Creates a new agent 615 /// Creates a new agent
541 /// </summary> 616 /// </summary>
542 /// <param name="agent">The agent to create</param> 617 /// <param name="agent">The agent to create</param>
543 override public void AddNewUserAgent(UserAgentData agent) 618 public override void AddNewUserAgent(UserAgentData agent)
544 { 619 {
620 MySQLSuperManager dbm = GetLockedConnection();
545 try 621 try
546 { 622 {
547 lock (database) 623 dbm.Manager.insertAgentRow(agent);
548 {
549 database.insertAgentRow(agent);
550 }
551 } 624 }
552 catch (Exception e) 625 catch (Exception e)
553 { 626 {
554 database.Reconnect(); 627 dbm.Manager.Reconnect();
555 m_log.Error(e.ToString()); 628 m_log.Error(e.ToString());
556 } 629 }
630 finally
631 {
632 dbm.Release();
633 }
557 } 634 }
558 635
559 /// <summary> 636 /// <summary>
560 /// Updates a user profile stored in the DB 637 /// Updates a user profile stored in the DB
561 /// </summary> 638 /// </summary>
562 /// <param name="user">The profile data to use to update the DB</param> 639 /// <param name="user">The profile data to use to update the DB</param>
563 override public bool UpdateUserProfile(UserProfileData user) 640 public override bool UpdateUserProfile(UserProfileData user)
564 { 641 {
565 lock (database) 642 MySQLSuperManager dbm = GetLockedConnection();
643 try
644 {
645 dbm.Manager.updateUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt,
646 user.HomeRegion, user.HomeRegionID, user.HomeLocation.X, user.HomeLocation.Y,
647 user.HomeLocation.Z, user.HomeLookAt.X,
648 user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, user.LastLogin,
649 user.UserInventoryURI,
650 user.UserAssetURI, user.CanDoMask, user.WantDoMask, user.AboutText,
651 user.FirstLifeAboutText, user.Image, user.FirstLifeImage, user.WebLoginKey,
652 user.UserFlags, user.GodLevel, user.CustomType, user.Partner);
653 }
654 finally
566 { 655 {
567 database.updateUserRow(user.ID, user.FirstName, user.SurName, user.PasswordHash, user.PasswordSalt, 656 dbm.Release();
568 user.HomeRegion, user.HomeRegionID, user.HomeLocation.X, user.HomeLocation.Y, user.HomeLocation.Z, user.HomeLookAt.X,
569 user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, user.LastLogin, user.UserInventoryURI,
570 user.UserAssetURI, user.CanDoMask, user.WantDoMask, user.AboutText,
571 user.FirstLifeAboutText, user.Image, user.FirstLifeImage, user.WebLoginKey, user.UserFlags, user.GodLevel, user.CustomType, user.Partner);
572 } 657 }
573 658
574 return true; 659 return true;
@@ -581,7 +666,7 @@ namespace OpenSim.Data.MySQL
581 /// <param name="to">The receivers account ID</param> 666 /// <param name="to">The receivers account ID</param>
582 /// <param name="amount">The amount to transfer</param> 667 /// <param name="amount">The amount to transfer</param>
583 /// <returns>Success?</returns> 668 /// <returns>Success?</returns>
584 override public bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount) 669 public override bool MoneyTransferRequest(LLUUID from, LLUUID to, uint amount)
585 { 670 {
586 return false; 671 return false;
587 } 672 }
@@ -594,7 +679,7 @@ namespace OpenSim.Data.MySQL
594 /// <param name="to">The receivers account ID</param> 679 /// <param name="to">The receivers account ID</param>
595 /// <param name="item">The item to transfer</param> 680 /// <param name="item">The item to transfer</param>
596 /// <returns>Success?</returns> 681 /// <returns>Success?</returns>
597 override public bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item) 682 public override bool InventoryTransferRequest(LLUUID from, LLUUID to, LLUUID item)
598 { 683 {
599 return false; 684 return false;
600 } 685 }
@@ -604,33 +689,37 @@ namespace OpenSim.Data.MySQL
604 /// TODO: stubs for now to get us to a compiling state gently 689 /// TODO: stubs for now to get us to a compiling state gently
605 /// override 690 /// override
606 /// </summary> 691 /// </summary>
607 override public AvatarAppearance GetUserAppearance(LLUUID user) 692 public override AvatarAppearance GetUserAppearance(LLUUID user)
608 { 693 {
609 try { 694 MySQLSuperManager dbm = GetLockedConnection();
610 lock (database) 695 try
611 { 696 {
612 Dictionary<string, string> param = new Dictionary<string, string>(); 697 Dictionary<string, string> param = new Dictionary<string, string>();
613 param["?owner"] = user.ToString(); 698 param["?owner"] = user.ToString();
614 699
615 IDbCommand result = database.Query("SELECT * FROM " + m_appearanceTableName + " WHERE owner = ?owner", param); 700 IDbCommand result = dbm.Manager.Query(
616 IDataReader reader = result.ExecuteReader(); 701 "SELECT * FROM " + m_appearanceTableName + " WHERE owner = ?owner", param);
702 IDataReader reader = result.ExecuteReader();
617 703
618 AvatarAppearance appearance = database.readAppearanceRow(reader); 704 AvatarAppearance appearance = dbm.Manager.readAppearanceRow(reader);
619 705
620 reader.Dispose(); 706 reader.Dispose();
621 result.Dispose(); 707 result.Dispose();
622 708
623 appearance.SetAttachments(GetUserAttachments(user)); 709 appearance.SetAttachments(GetUserAttachments(user));
624 710
625 return appearance; 711 return appearance;
626 }
627 } 712 }
628 catch (Exception e) 713 catch (Exception e)
629 { 714 {
630 database.Reconnect(); 715 dbm.Manager.Reconnect();
631 m_log.Error(e.ToString()); 716 m_log.Error(e.ToString());
632 return null; 717 return null;
633 } 718 }
719 finally
720 {
721 dbm.Release();
722 }
634 } 723 }
635 724
636 /// <summary> 725 /// <summary>
@@ -639,70 +728,97 @@ namespace OpenSim.Data.MySQL
639 /// <param name="user">The user UUID</param> 728 /// <param name="user">The user UUID</param>
640 /// <param name="appearance">The avatar appearance</param> 729 /// <param name="appearance">The avatar appearance</param>
641 // override 730 // override
642 override public void UpdateUserAppearance(LLUUID user, AvatarAppearance appearance) 731 public override void UpdateUserAppearance(LLUUID user, AvatarAppearance appearance)
643 { 732 {
733 MySQLSuperManager dbm = GetLockedConnection();
644 try 734 try
645 { 735 {
646 lock (database) 736 appearance.Owner = user;
647 { 737 dbm.Manager.insertAppearanceRow(appearance);
648 appearance.Owner = user;
649 database.insertAppearanceRow(appearance);
650 738
651 UpdateUserAttachments(user, appearance.GetAttachments()); 739 UpdateUserAttachments(user, appearance.GetAttachments());
652 }
653 } 740 }
654 catch (Exception e) 741 catch (Exception e)
655 { 742 {
656 database.Reconnect(); 743 dbm.Manager.Reconnect();
657 m_log.Error(e.ToString()); 744 m_log.Error(e.ToString());
658 } 745 }
746 finally
747 {
748 dbm.Release();
749 }
659 } 750 }
660 751
661 /// <summary> 752 /// <summary>
662 /// Database provider name 753 /// Database provider name
663 /// </summary> 754 /// </summary>
664 /// <returns>Provider name</returns> 755 /// <returns>Provider name</returns>
665 override public string Name 756 public override string Name
666 { 757 {
667 get {return "MySQL Userdata Interface";} 758 get { return "MySQL Userdata Interface"; }
668 } 759 }
669 760
670 /// <summary> 761 /// <summary>
671 /// Database provider version 762 /// Database provider version
672 /// </summary> 763 /// </summary>
673 /// <returns>provider version</returns> 764 /// <returns>provider version</returns>
674 override public string Version 765 public override string Version
675 { 766 {
676 get {return "0.1";} 767 get { return "0.1"; }
677 } 768 }
678 769
679 public Hashtable GetUserAttachments(LLUUID agentID) 770 public Hashtable GetUserAttachments(LLUUID agentID)
680 { 771 {
681 MySqlCommand cmd = (MySqlCommand) (database.Connection.CreateCommand()); 772 MySQLSuperManager dbm = GetLockedConnection();
682 cmd.CommandText = "select attachpoint, item, asset from avatarattachments where UUID = ?uuid";
683 cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
684 773
685 IDataReader r = cmd.ExecuteReader(); 774 try
775 {
776 MySqlCommand cmd = dbm.Manager.Connection.CreateCommand();
777 cmd.CommandText = "select attachpoint, item, asset from avatarattachments where UUID = ?uuid";
778 cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
686 779
687 Hashtable ret = database.readAttachments(r); 780 IDataReader r = cmd.ExecuteReader();
688 781
689 r.Close(); 782 Hashtable ret = dbm.Manager.readAttachments(r);
690 783
691 return ret; 784 r.Close();
785 return ret;
786 }
787 finally
788 {
789 dbm.Release();
790 }
692 } 791 }
693 792
694 public void UpdateUserAttachments(LLUUID agentID, Hashtable data) 793 public void UpdateUserAttachments(LLUUID agentID, Hashtable data)
695 { 794 {
696 database.writeAttachments(agentID, data); 795 MySQLSuperManager dbm = GetLockedConnection();
796 try
797 {
798 dbm.Manager.writeAttachments(agentID, data);
799 }
800 finally
801 {
802 dbm.Release();
803 }
697 } 804 }
698 805
699 override public void ResetAttachments(LLUUID userID) 806 public override void ResetAttachments(LLUUID userID)
700 { 807 {
701 MySqlCommand cmd = (MySqlCommand) (database.Connection.CreateCommand()); 808 MySQLSuperManager dbm = GetLockedConnection();
702 cmd.CommandText = "update avatarattachments set asset = '00000000-0000-0000-0000-000000000000' where UUID = ?uuid"; 809 try
703 cmd.Parameters.AddWithValue("?uuid", userID.ToString()); 810 {
811 MySqlCommand cmd = dbm.Manager.Connection.CreateCommand();
812 cmd.CommandText =
813 "update avatarattachments set asset = '00000000-0000-0000-0000-000000000000' where UUID = ?uuid";
814 cmd.Parameters.AddWithValue("?uuid", userID.ToString());
704 815
705 cmd.ExecuteNonQuery(); 816 cmd.ExecuteNonQuery();
817 }
818 finally
819 {
820 dbm.Release();
821 }
706 } 822 }
707 } 823 }
708} 824} \ No newline at end of file