aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLUserData.cs
diff options
context:
space:
mode:
authorMaster ScienceSim2010-02-04 13:19:30 -0800
committerJohn Hurliman2010-02-05 18:07:59 -0800
commite1b5c612472b9d1acf47383c0bf75b555daff2e6 (patch)
tree083896698038fbdad59c2bd3adeba9b290c5ce1b /OpenSim/Data/MySQL/MySQLUserData.cs
parentFixing an incorrect logging message in insertUserRow (diff)
downloadopensim-SC_OLD-e1b5c612472b9d1acf47383c0bf75b555daff2e6.zip
opensim-SC_OLD-e1b5c612472b9d1acf47383c0bf75b555daff2e6.tar.gz
opensim-SC_OLD-e1b5c612472b9d1acf47383c0bf75b555daff2e6.tar.bz2
opensim-SC_OLD-e1b5c612472b9d1acf47383c0bf75b555daff2e6.tar.xz
Updated MySQL connection management to use the MySQL connection pooling. This should accommodate various timeout problems that exist with the current connection pool code in a more general and standard way.
Diffstat (limited to 'OpenSim/Data/MySQL/MySQLUserData.cs')
-rw-r--r--OpenSim/Data/MySQL/MySQLUserData.cs676
1 files changed, 265 insertions, 411 deletions
diff --git a/OpenSim/Data/MySQL/MySQLUserData.cs b/OpenSim/Data/MySQL/MySQLUserData.cs
index 04f872f..2cf88b8 100644
--- a/OpenSim/Data/MySQL/MySQLUserData.cs
+++ b/OpenSim/Data/MySQL/MySQLUserData.cs
@@ -33,6 +33,7 @@ using System.Reflection;
33using System.Text.RegularExpressions; 33using System.Text.RegularExpressions;
34using System.Threading; 34using System.Threading;
35using log4net; 35using log4net;
36using MySql.Data.MySqlClient;
36using OpenMetaverse; 37using OpenMetaverse;
37using OpenSim.Framework; 38using OpenSim.Framework;
38 39
@@ -45,15 +46,9 @@ namespace OpenSim.Data.MySQL
45 { 46 {
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 47 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47 48
48 /// <summary> 49 private MySQLManager m_database;
49 /// Database manager for MySQL 50 private string m_connectionString;
50 /// </summary> 51 private object m_dbLock = new object();
51 public MySQLManager database;
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 52
58 public int m_maxConnections = 10; 53 public int m_maxConnections = 10;
59 public int m_lastConnect; 54 public int m_lastConnect;
@@ -63,7 +58,6 @@ namespace OpenSim.Data.MySQL
63 private string m_userFriendsTableName = "userfriends"; 58 private string m_userFriendsTableName = "userfriends";
64 private string m_appearanceTableName = "avatarappearance"; 59 private string m_appearanceTableName = "avatarappearance";
65 private string m_attachmentsTableName = "avatarattachments"; 60 private string m_attachmentsTableName = "avatarattachments";
66 private string m_connectString;
67 61
68 public override void Initialise() 62 public override void Initialise()
69 { 63 {
@@ -71,41 +65,6 @@ namespace OpenSim.Data.MySQL
71 throw new PluginNotInitialisedException(Name); 65 throw new PluginNotInitialisedException(Name);
72 } 66 }
73 67
74 public MySQLSuperManager GetLockedConnection(string why)
75 {
76 int lockedCons = 0;
77 while (true)
78 {
79 m_lastConnect++;
80
81 // Overflow protection
82 if (m_lastConnect == int.MaxValue)
83 m_lastConnect = 0;
84
85 MySQLSuperManager x = m_dbconnections[m_lastConnect%m_maxConnections];
86 if (!x.Locked)
87 {
88 x.GetLock();
89 x.Running = why;
90 return x;
91 }
92
93 lockedCons++;
94 if (lockedCons > m_maxConnections)
95 {
96 lockedCons = 0;
97 Thread.Sleep(1000); // Wait some time before searching them again.
98 m_log.Debug(
99 "WARNING: All threads are in use. Probable cause: Something didnt release a mutex properly, or high volume of requests inbound.");
100 m_log.Debug("Current connections-in-use dump:");
101 foreach (KeyValuePair<int, MySQLSuperManager> kvp in m_dbconnections)
102 {
103 m_log.Debug(kvp.Value.Running);
104 }
105 }
106 }
107 }
108
109 /// <summary> 68 /// <summary>
110 /// Initialise User Interface 69 /// Initialise User Interface
111 /// Loads and initialises the MySQL storage plugin 70 /// Loads and initialises the MySQL storage plugin
@@ -115,55 +74,18 @@ namespace OpenSim.Data.MySQL
115 /// <param name="connect">connect string.</param> 74 /// <param name="connect">connect string.</param>
116 public override void Initialise(string connect) 75 public override void Initialise(string connect)
117 { 76 {
118 if (connect == String.Empty) 77 m_connectionString = connect;
119 { 78 m_database = new MySQLManager(connect);
120 // TODO: actually do something with our connect string
121 // instead of loading the second config
122
123 m_log.Warn("Using obsoletely mysql_connection.ini, try using user_source connect string instead");
124 IniFile iniFile = new IniFile("mysql_connection.ini");
125 string settingHostname = iniFile.ParseFileReadValue("hostname");
126 string settingDatabase = iniFile.ParseFileReadValue("database");
127 string settingUsername = iniFile.ParseFileReadValue("username");
128 string settingPassword = iniFile.ParseFileReadValue("password");
129 string settingPooling = iniFile.ParseFileReadValue("pooling");
130 string settingPort = iniFile.ParseFileReadValue("port");
131
132 m_connectString = "Server=" + settingHostname + ";Port=" + settingPort + ";Database=" + settingDatabase +
133 ";User ID=" +
134 settingUsername + ";Password=" + settingPassword + ";Pooling=" + settingPooling + ";";
135
136 m_log.Info("Creating " + m_maxConnections + " DB connections...");
137 for (int i = 0; i < m_maxConnections; i++)
138 {
139 m_log.Info("Connecting to DB... [" + i + "]");
140 MySQLSuperManager msm = new MySQLSuperManager();
141 msm.Manager = new MySQLManager(m_connectString);
142 m_dbconnections.Add(i, msm);
143 }
144
145 database = new MySQLManager(m_connectString);
146 }
147 else
148 {
149 m_connectString = connect;
150 database = new MySQLManager(m_connectString);
151
152 m_log.Info("Creating " + m_maxConnections + " DB connections...");
153 for (int i = 0; i < m_maxConnections; i++)
154 {
155 m_log.Info("Connecting to DB... [" + i + "]");
156 MySQLSuperManager msm = new MySQLSuperManager();
157 msm.Manager = new MySQLManager(m_connectString);
158 m_dbconnections.Add(i, msm);
159 }
160 }
161 79
162 // This actually does the roll forward assembly stuff 80 // This actually does the roll forward assembly stuff
163 Assembly assem = GetType().Assembly; 81 Assembly assem = GetType().Assembly;
164 Migration m = new Migration(database.Connection, assem, "UserStore");
165 82
166 m.Update(); 83 using (MySql.Data.MySqlClient.MySqlConnection dbcon = new MySql.Data.MySqlClient.MySqlConnection(m_connectionString))
84 {
85 dbcon.Open();
86 Migration m = new Migration(dbcon, assem, "UserStore");
87 m.Update();
88 }
167 } 89 }
168 90
169 public override void Dispose() 91 public override void Dispose()
@@ -173,35 +95,32 @@ namespace OpenSim.Data.MySQL
173 // see IUserDataPlugin 95 // see IUserDataPlugin
174 public override UserProfileData GetUserByName(string user, string last) 96 public override UserProfileData GetUserByName(string user, string last)
175 { 97 {
176 MySQLSuperManager dbm = GetLockedConnection("GetUserByName");
177
178 try 98 try
179 { 99 {
180 Dictionary<string, object> param = new Dictionary<string, object>(); 100 Dictionary<string, object> param = new Dictionary<string, object>();
181 param["?first"] = user; 101 param["?first"] = user;
182 param["?second"] = last; 102 param["?second"] = last;
183 103
184 IDbCommand result = 104 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
185 dbm.Manager.Query( 105 {
186 "SELECT * FROM " + m_usersTableName + " WHERE username = ?first AND lastname = ?second", param); 106 dbcon.Open();
187 IDataReader reader = result.ExecuteReader();
188
189 UserProfileData row = dbm.Manager.readUserRow(reader);
190 107
191 reader.Dispose(); 108 using (IDbCommand result = m_database.Query(dbcon,
192 result.Dispose(); 109 "SELECT * FROM " + m_usersTableName + " WHERE username = ?first AND lastname = ?second", param))
193 return row; 110 {
111 using (IDataReader reader = result.ExecuteReader())
112 {
113 UserProfileData row = m_database.readUserRow(reader);
114 return row;
115 }
116 }
117 }
194 } 118 }
195 catch (Exception e) 119 catch (Exception e)
196 { 120 {
197 dbm.Manager.Reconnect(); 121 m_log.Error(e.Message, e);
198 m_log.Error(e.ToString());
199 return null; 122 return null;
200 } 123 }
201 finally
202 {
203 dbm.Release();
204 }
205 } 124 }
206 125
207 #region User Friends List Data 126 #region User Friends List Data
@@ -216,38 +135,38 @@ namespace OpenSim.Data.MySQL
216 param["?friendPerms"] = perms.ToString(); 135 param["?friendPerms"] = perms.ToString();
217 param["?datetimestamp"] = dtvalue.ToString(); 136 param["?datetimestamp"] = dtvalue.ToString();
218 137
219 MySQLSuperManager dbm = GetLockedConnection("AddNewUserFriend");
220
221 try 138 try
222 { 139 {
223 IDbCommand adder = 140 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
224 dbm.Manager.Query( 141 {
142 dbcon.Open();
143
144 using (IDbCommand adder = m_database.Query(dbcon,
225 "INSERT INTO `" + m_userFriendsTableName + "` " + 145 "INSERT INTO `" + m_userFriendsTableName + "` " +
226 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + 146 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
227 "VALUES " + 147 "VALUES " +
228 "(?ownerID,?friendID,?friendPerms,?datetimestamp)", 148 "(?ownerID,?friendID,?friendPerms,?datetimestamp)",
229 param); 149 param))
230 adder.ExecuteNonQuery(); 150 {
151 adder.ExecuteNonQuery();
152 }
231 153
232 adder = 154 using (IDbCommand adder = m_database.Query(dbcon,
233 dbm.Manager.Query(
234 "INSERT INTO `" + m_userFriendsTableName + "` " + 155 "INSERT INTO `" + m_userFriendsTableName + "` " +
235 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " + 156 "(`ownerID`,`friendID`,`friendPerms`,`datetimestamp`) " +
236 "VALUES " + 157 "VALUES " +
237 "(?friendID,?ownerID,?friendPerms,?datetimestamp)", 158 "(?friendID,?ownerID,?friendPerms,?datetimestamp)",
238 param); 159 param))
239 adder.ExecuteNonQuery(); 160 {
161 adder.ExecuteNonQuery();
162 }
163 }
240 } 164 }
241 catch (Exception e) 165 catch (Exception e)
242 { 166 {
243 dbm.Manager.Reconnect(); 167 m_log.Error(e.Message, e);
244 m_log.Error(e.ToString());
245 return; 168 return;
246 } 169 }
247 finally
248 {
249 dbm.Release();
250 }
251 } 170 }
252 171
253 public override void RemoveUserFriend(UUID friendlistowner, UUID friend) 172 public override void RemoveUserFriend(UUID friendlistowner, UUID friend)
@@ -256,32 +175,32 @@ namespace OpenSim.Data.MySQL
256 param["?ownerID"] = friendlistowner.ToString(); 175 param["?ownerID"] = friendlistowner.ToString();
257 param["?friendID"] = friend.ToString(); 176 param["?friendID"] = friend.ToString();
258 177
259 MySQLSuperManager dbm = GetLockedConnection("RemoveUserFriend");
260
261 try 178 try
262 { 179 {
263 IDbCommand updater = 180 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
264 dbm.Manager.Query( 181 {
265 "delete from " + m_userFriendsTableName + " where ownerID = ?ownerID and friendID = ?friendID", 182 dbcon.Open();
266 param);
267 updater.ExecuteNonQuery();
268 183
269 updater = 184 using (IDbCommand updater = m_database.Query(dbcon,
270 dbm.Manager.Query( 185 "delete from " + m_userFriendsTableName + " where ownerID = ?ownerID and friendID = ?friendID",
271 "delete from " + m_userFriendsTableName + " where ownerID = ?friendID and friendID = ?ownerID", 186 param))
272 param); 187 {
273 updater.ExecuteNonQuery(); 188 updater.ExecuteNonQuery();
189 }
190
191 using (IDbCommand updater = m_database.Query(dbcon,
192 "delete from " + m_userFriendsTableName + " where ownerID = ?friendID and friendID = ?ownerID",
193 param))
194 {
195 updater.ExecuteNonQuery();
196 }
197 }
274 } 198 }
275 catch (Exception e) 199 catch (Exception e)
276 { 200 {
277 dbm.Manager.Reconnect(); 201 m_log.Error(e.Message, e);
278 m_log.Error(e.ToString());
279 return; 202 return;
280 } 203 }
281 finally
282 {
283 dbm.Release();
284 }
285 } 204 }
286 205
287 public override void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms) 206 public override void UpdateUserFriendPerms(UUID friendlistowner, UUID friend, uint perms)
@@ -291,28 +210,27 @@ namespace OpenSim.Data.MySQL
291 param["?friendID"] = friend.ToString(); 210 param["?friendID"] = friend.ToString();
292 param["?friendPerms"] = perms.ToString(); 211 param["?friendPerms"] = perms.ToString();
293 212
294 MySQLSuperManager dbm = GetLockedConnection("UpdateUserFriendPerms");
295
296 try 213 try
297 { 214 {
298 IDbCommand updater = 215 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
299 dbm.Manager.Query( 216 {
300 "update " + m_userFriendsTableName + 217 dbcon.Open();
301 " SET friendPerms = ?friendPerms " + 218
302 "where ownerID = ?ownerID and friendID = ?friendID", 219 using (IDbCommand updater = m_database.Query(dbcon,
303 param); 220 "update " + m_userFriendsTableName +
304 updater.ExecuteNonQuery(); 221 " SET friendPerms = ?friendPerms " +
222 "where ownerID = ?ownerID and friendID = ?friendID",
223 param))
224 {
225 updater.ExecuteNonQuery();
226 }
227 }
305 } 228 }
306 catch (Exception e) 229 catch (Exception e)
307 { 230 {
308 dbm.Manager.Reconnect(); 231 m_log.Error(e.Message, e);
309 m_log.Error(e.ToString());
310 return; 232 return;
311 } 233 }
312 finally
313 {
314 dbm.Release();
315 }
316 } 234 }
317 235
318 public override List<FriendListItem> GetUserFriendList(UUID friendlistowner) 236 public override List<FriendListItem> GetUserFriendList(UUID friendlistowner)
@@ -322,87 +240,83 @@ namespace OpenSim.Data.MySQL
322 Dictionary<string, object> param = new Dictionary<string, object>(); 240 Dictionary<string, object> param = new Dictionary<string, object>();
323 param["?ownerID"] = friendlistowner.ToString(); 241 param["?ownerID"] = friendlistowner.ToString();
324 242
325 MySQLSuperManager dbm = GetLockedConnection("GetUserFriendList");
326
327 try 243 try
328 { 244 {
329 //Left Join userfriends to itself 245 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
330 IDbCommand result = 246 {
331 dbm.Manager.Query( 247 dbcon.Open();
248
249 //Left Join userfriends to itself
250 using (IDbCommand result = m_database.Query(dbcon,
332 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " + 251 "select a.ownerID,a.friendID,a.friendPerms,b.friendPerms as ownerperms from " +
333 m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" + 252 m_userFriendsTableName + " as a, " + m_userFriendsTableName + " as b" +
334 " where a.ownerID = ?ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID", 253 " where a.ownerID = ?ownerID and b.ownerID = a.friendID and b.friendID = a.ownerID",
335 param); 254 param))
336 IDataReader reader = result.ExecuteReader(); 255 {
337 256 using (IDataReader reader = result.ExecuteReader())
338 while (reader.Read()) 257 {
339 { 258 while (reader.Read())
340 FriendListItem fli = new FriendListItem(); 259 {
341 fli.FriendListOwner = new UUID((string) reader["ownerID"]); 260 FriendListItem fli = new FriendListItem();
342 fli.Friend = new UUID((string) reader["friendID"]); 261 fli.FriendListOwner = new UUID((string)reader["ownerID"]);
343 fli.FriendPerms = (uint) Convert.ToInt32(reader["friendPerms"]); 262 fli.Friend = new UUID((string)reader["friendID"]);
344 263 fli.FriendPerms = (uint)Convert.ToInt32(reader["friendPerms"]);
345 // This is not a real column in the database table, it's a joined column from the opposite record 264
346 fli.FriendListOwnerPerms = (uint) Convert.ToInt32(reader["ownerperms"]); 265 // This is not a real column in the database table, it's a joined column from the opposite record
347 266 fli.FriendListOwnerPerms = (uint)Convert.ToInt32(reader["ownerperms"]);
348 Lfli.Add(fli); 267
268 Lfli.Add(fli);
269 }
270 }
271 }
349 } 272 }
350
351 reader.Dispose();
352 result.Dispose();
353 } 273 }
354 catch (Exception e) 274 catch (Exception e)
355 { 275 {
356 dbm.Manager.Reconnect(); 276 m_log.Error(e.Message, e);
357 m_log.Error(e.ToString());
358 return Lfli; 277 return Lfli;
359 } 278 }
360 finally
361 {
362 dbm.Release();
363 }
364 279
365 return Lfli; 280 return Lfli;
366 } 281 }
367 282
368 override public Dictionary<UUID, FriendRegionInfo> GetFriendRegionInfos (List<UUID> uuids) 283 override public Dictionary<UUID, FriendRegionInfo> GetFriendRegionInfos (List<UUID> uuids)
369 { 284 {
370 MySQLSuperManager dbm = GetLockedConnection("GetFriendRegionInfos");
371 Dictionary<UUID, FriendRegionInfo> infos = new Dictionary<UUID,FriendRegionInfo>(); 285 Dictionary<UUID, FriendRegionInfo> infos = new Dictionary<UUID,FriendRegionInfo>();
372 286
373 try 287 try
374 { 288 {
375 foreach (UUID uuid in uuids) 289 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
376 { 290 {
377 Dictionary<string, object> param = new Dictionary<string, object>(); 291 dbcon.Open();
378 param["?uuid"] = uuid.ToString();
379 IDbCommand result =
380 dbm.Manager.Query("select agentOnline,currentHandle from " + m_agentsTableName +
381 " where UUID = ?uuid", param);
382 292
383 IDataReader reader = result.ExecuteReader(); 293 foreach (UUID uuid in uuids)
384 while (reader.Read())
385 { 294 {
386 FriendRegionInfo fri = new FriendRegionInfo(); 295 Dictionary<string, object> param = new Dictionary<string, object>();
387 fri.isOnline = (sbyte)reader["agentOnline"] != 0; 296 param["?uuid"] = uuid.ToString();
388 fri.regionHandle = (ulong)reader["currentHandle"]; 297
389 298 using (IDbCommand result = m_database.Query(dbcon, "select agentOnline,currentHandle from " + m_agentsTableName +
390 infos[uuid] = fri; 299 " where UUID = ?uuid", param))
300 {
301 using (IDataReader reader = result.ExecuteReader())
302 {
303 while (reader.Read())
304 {
305 FriendRegionInfo fri = new FriendRegionInfo();
306 fri.isOnline = (sbyte)reader["agentOnline"] != 0;
307 fri.regionHandle = (ulong)reader["currentHandle"];
308
309 infos[uuid] = fri;
310 }
311 }
312 }
391 } 313 }
392
393 reader.Dispose();
394 result.Dispose();
395 } 314 }
396 } 315 }
397 catch (Exception e) 316 catch (Exception e)
398 { 317 {
399 m_log.Warn("[MYSQL]: Got exception on trying to find friends regions:", e); 318 m_log.Warn("[MYSQL]: Got exception on trying to find friends regions:", e);
400 dbm.Manager.Reconnect(); 319 m_log.Error(e.Message, e);
401 m_log.Error(e.ToString());
402 }
403 finally
404 {
405 dbm.Release();
406 } 320 }
407 321
408 return infos; 322 return infos;
@@ -423,76 +337,73 @@ namespace OpenSim.Data.MySQL
423 Dictionary<string, object> param = new Dictionary<string, object>(); 337 Dictionary<string, object> param = new Dictionary<string, object>();
424 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%"; 338 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%";
425 param["?second"] = objAlphaNumericPattern.Replace(querysplit[1], String.Empty) + "%"; 339 param["?second"] = objAlphaNumericPattern.Replace(querysplit[1], String.Empty) + "%";
426 MySQLSuperManager dbm = GetLockedConnection("GeneratePickerResults");
427 340
428 try 341 try
429 { 342 {
430 IDbCommand result = 343 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
431 dbm.Manager.Query( 344 {
345 dbcon.Open();
346
347 using (IDbCommand result = m_database.Query(dbcon,
432 "SELECT UUID,username,lastname FROM " + m_usersTableName + 348 "SELECT UUID,username,lastname FROM " + m_usersTableName +
433 " WHERE username like ?first AND lastname like ?second LIMIT 100", 349 " WHERE username like ?first AND lastname like ?second LIMIT 100",
434 param); 350 param))
435 IDataReader reader = result.ExecuteReader(); 351 {
436 352 using (IDataReader reader = result.ExecuteReader())
437 while (reader.Read()) 353 {
438 { 354 while (reader.Read())
439 AvatarPickerAvatar user = new AvatarPickerAvatar(); 355 {
440 user.AvatarID = new UUID((string) reader["UUID"]); 356 AvatarPickerAvatar user = new AvatarPickerAvatar();
441 user.firstName = (string) reader["username"]; 357 user.AvatarID = new UUID((string)reader["UUID"]);
442 user.lastName = (string) reader["lastname"]; 358 user.firstName = (string)reader["username"];
443 returnlist.Add(user); 359 user.lastName = (string)reader["lastname"];
360 returnlist.Add(user);
361 }
362 }
363 }
444 } 364 }
445 reader.Dispose();
446 result.Dispose();
447 } 365 }
448 catch (Exception e) 366 catch (Exception e)
449 { 367 {
450 dbm.Manager.Reconnect(); 368 m_log.Error(e.Message, e);
451 m_log.Error(e.ToString());
452 return returnlist; 369 return returnlist;
453 } 370 }
454 finally
455 {
456 dbm.Release();
457 }
458 } 371 }
459 else 372 else
460 { 373 {
461 MySQLSuperManager dbm = GetLockedConnection("GeneratePickerResults");
462
463 try 374 try
464 { 375 {
465 Dictionary<string, object> param = new Dictionary<string, object>(); 376 Dictionary<string, object> param = new Dictionary<string, object>();
466 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%"; 377 param["?first"] = objAlphaNumericPattern.Replace(querysplit[0], String.Empty) + "%";
467 378
468 IDbCommand result = 379 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
469 dbm.Manager.Query( 380 {
381 dbcon.Open();
382
383 using (IDbCommand result = m_database.Query(dbcon,
470 "SELECT UUID,username,lastname FROM " + m_usersTableName + 384 "SELECT UUID,username,lastname FROM " + m_usersTableName +
471 " WHERE username like ?first OR lastname like ?first LIMIT 100", 385 " WHERE username like ?first OR lastname like ?first LIMIT 100",
472 param); 386 param))
473 IDataReader reader = result.ExecuteReader(); 387 {
474 388 using (IDataReader reader = result.ExecuteReader())
475 while (reader.Read()) 389 {
476 { 390 while (reader.Read())
477 AvatarPickerAvatar user = new AvatarPickerAvatar(); 391 {
478 user.AvatarID = new UUID((string) reader["UUID"]); 392 AvatarPickerAvatar user = new AvatarPickerAvatar();
479 user.firstName = (string) reader["username"]; 393 user.AvatarID = new UUID((string)reader["UUID"]);
480 user.lastName = (string) reader["lastname"]; 394 user.firstName = (string)reader["username"];
481 returnlist.Add(user); 395 user.lastName = (string)reader["lastname"];
396 returnlist.Add(user);
397 }
398 }
399 }
482 } 400 }
483 reader.Dispose();
484 result.Dispose();
485 } 401 }
486 catch (Exception e) 402 catch (Exception e)
487 { 403 {
488 dbm.Manager.Reconnect(); 404 m_log.Error(e.Message, e);
489 m_log.Error(e.ToString());
490 return returnlist; 405 return returnlist;
491 } 406 }
492 finally
493 {
494 dbm.Release();
495 }
496 } 407 }
497 return returnlist; 408 return returnlist;
498 } 409 }
@@ -504,32 +415,30 @@ namespace OpenSim.Data.MySQL
504 /// <returns>User profile data</returns> 415 /// <returns>User profile data</returns>
505 public override UserProfileData GetUserByUUID(UUID uuid) 416 public override UserProfileData GetUserByUUID(UUID uuid)
506 { 417 {
507 MySQLSuperManager dbm = GetLockedConnection("GetUserByUUID");
508 try 418 try
509 { 419 {
510 Dictionary<string, object> param = new Dictionary<string, object>(); 420 Dictionary<string, object> param = new Dictionary<string, object>();
511 param["?uuid"] = uuid.ToString(); 421 param["?uuid"] = uuid.ToString();
512 422
513 IDbCommand result = dbm.Manager.Query("SELECT * FROM " + m_usersTableName + " WHERE UUID = ?uuid", param); 423 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
514 IDataReader reader = result.ExecuteReader(); 424 {
515 425 dbcon.Open();
516 UserProfileData row = dbm.Manager.readUserRow(reader);
517
518 reader.Dispose();
519 result.Dispose();
520 426
521 return row; 427 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM " + m_usersTableName + " WHERE UUID = ?uuid", param))
428 {
429 using (IDataReader reader = result.ExecuteReader())
430 {
431 UserProfileData row = m_database.readUserRow(reader);
432 return row;
433 }
434 }
435 }
522 } 436 }
523 catch (Exception e) 437 catch (Exception e)
524 { 438 {
525 dbm.Manager.Reconnect(); 439 m_log.Error(e.Message, e);
526 m_log.Error(e.ToString());
527 return null; 440 return null;
528 } 441 }
529 finally
530 {
531 dbm.Release();
532 }
533 } 442 }
534 443
535 /// <summary> 444 /// <summary>
@@ -565,25 +474,18 @@ namespace OpenSim.Data.MySQL
565 param["?UUID"] = AgentID.ToString(); 474 param["?UUID"] = AgentID.ToString();
566 param["?webLoginKey"] = WebLoginKey.ToString(); 475 param["?webLoginKey"] = WebLoginKey.ToString();
567 476
568 MySQLSuperManager dbm = GetLockedConnection("StoreWebLoginKey");
569
570 try 477 try
571 { 478 {
572 dbm.Manager.ExecuteParameterizedSql( 479 m_database.ExecuteParameterizedSql(
573 "update " + m_usersTableName + " SET webLoginKey = ?webLoginKey " + 480 "update " + m_usersTableName + " SET webLoginKey = ?webLoginKey " +
574 "where UUID = ?UUID", 481 "where UUID = ?UUID",
575 param); 482 param);
576 } 483 }
577 catch (Exception e) 484 catch (Exception e)
578 { 485 {
579 dbm.Manager.Reconnect(); 486 m_log.Error(e.Message, e);
580 m_log.Error(e.ToString());
581 return; 487 return;
582 } 488 }
583 finally
584 {
585 dbm.Release();
586 }
587 } 489 }
588 490
589 /// <summary> 491 /// <summary>
@@ -593,34 +495,30 @@ namespace OpenSim.Data.MySQL
593 /// <returns>The users session</returns> 495 /// <returns>The users session</returns>
594 public override UserAgentData GetAgentByUUID(UUID uuid) 496 public override UserAgentData GetAgentByUUID(UUID uuid)
595 { 497 {
596 MySQLSuperManager dbm = GetLockedConnection("GetAgentByUUID");
597
598 try 498 try
599 { 499 {
600 Dictionary<string, object> param = new Dictionary<string, object>(); 500 Dictionary<string, object> param = new Dictionary<string, object>();
601 param["?uuid"] = uuid.ToString(); 501 param["?uuid"] = uuid.ToString();
602 502
603 IDbCommand result = dbm.Manager.Query("SELECT * FROM " + m_agentsTableName + " WHERE UUID = ?uuid", 503 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
604 param); 504 {
605 IDataReader reader = result.ExecuteReader(); 505 dbcon.Open();
606
607 UserAgentData row = dbm.Manager.readAgentRow(reader);
608
609 reader.Dispose();
610 result.Dispose();
611 506
612 return row; 507 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM " + m_agentsTableName + " WHERE UUID = ?uuid", param))
508 {
509 using (IDataReader reader = result.ExecuteReader())
510 {
511 UserAgentData row = m_database.readAgentRow(reader);
512 return row;
513 }
514 }
515 }
613 } 516 }
614 catch (Exception e) 517 catch (Exception e)
615 { 518 {
616 dbm.Manager.Reconnect(); 519 m_log.Error(e.Message, e);
617 m_log.Error(e.ToString());
618 return null; 520 return null;
619 } 521 }
620 finally
621 {
622 dbm.Release();
623 }
624 } 522 }
625 523
626 /// <summary> 524 /// <summary>
@@ -634,27 +532,22 @@ namespace OpenSim.Data.MySQL
634 { 532 {
635 return; 533 return;
636 } 534 }
637 MySQLSuperManager dbm = GetLockedConnection("AddNewUserProfile");
638 535
639 try 536 try
640 { 537 {
641 dbm.Manager.insertUserRow(user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt, 538 m_database.insertUserRow(
642 user.HomeRegion, user.HomeRegionID, user.HomeLocation.X, user.HomeLocation.Y, 539 user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt,
643 user.HomeLocation.Z, 540 user.HomeRegion, user.HomeRegionID, user.HomeLocation.X, user.HomeLocation.Y,
644 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, 541 user.HomeLocation.Z,
645 user.LastLogin, user.UserInventoryURI, user.UserAssetURI, 542 user.HomeLookAt.X, user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created,
646 user.CanDoMask, user.WantDoMask, 543 user.LastLogin, user.UserInventoryURI, user.UserAssetURI,
647 user.AboutText, user.FirstLifeAboutText, user.Image, 544 user.CanDoMask, user.WantDoMask,
648 user.FirstLifeImage, user.WebLoginKey, user.UserFlags, user.GodLevel, user.CustomType, user.Partner); 545 user.AboutText, user.FirstLifeAboutText, user.Image,
546 user.FirstLifeImage, user.WebLoginKey, user.UserFlags, user.GodLevel, user.CustomType, user.Partner);
649 } 547 }
650 catch (Exception e) 548 catch (Exception e)
651 { 549 {
652 dbm.Manager.Reconnect(); 550 m_log.Error(e.Message, e);
653 m_log.Error(e.ToString());
654 }
655 finally
656 {
657 dbm.Release();
658 } 551 }
659 } 552 }
660 553
@@ -668,19 +561,13 @@ namespace OpenSim.Data.MySQL
668 if (agent.ProfileID == zero || agent.SessionID == zero) 561 if (agent.ProfileID == zero || agent.SessionID == zero)
669 return; 562 return;
670 563
671 MySQLSuperManager dbm = GetLockedConnection("AddNewUserAgent");
672 try 564 try
673 { 565 {
674 dbm.Manager.insertAgentRow(agent); 566 m_database.insertAgentRow(agent);
675 } 567 }
676 catch (Exception e) 568 catch (Exception e)
677 { 569 {
678 dbm.Manager.Reconnect(); 570 m_log.Error(e.Message, e);
679 m_log.Error(e.ToString());
680 }
681 finally
682 {
683 dbm.Release();
684 } 571 }
685 } 572 }
686 573
@@ -690,24 +577,24 @@ namespace OpenSim.Data.MySQL
690 /// <param name="user">The profile data to use to update the DB</param> 577 /// <param name="user">The profile data to use to update the DB</param>
691 public override bool UpdateUserProfile(UserProfileData user) 578 public override bool UpdateUserProfile(UserProfileData user)
692 { 579 {
693 MySQLSuperManager dbm = GetLockedConnection("UpdateUserProfile");
694 try 580 try
695 { 581 {
696 dbm.Manager.updateUserRow(user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt, 582 m_database.updateUserRow(
697 user.HomeRegion, user.HomeRegionID, user.HomeLocation.X, user.HomeLocation.Y, 583 user.ID, user.FirstName, user.SurName, user.Email, user.PasswordHash, user.PasswordSalt,
698 user.HomeLocation.Z, user.HomeLookAt.X, 584 user.HomeRegion, user.HomeRegionID, user.HomeLocation.X, user.HomeLocation.Y,
699 user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, user.LastLogin, 585 user.HomeLocation.Z, user.HomeLookAt.X,
700 user.UserInventoryURI, 586 user.HomeLookAt.Y, user.HomeLookAt.Z, user.Created, user.LastLogin,
701 user.UserAssetURI, user.CanDoMask, user.WantDoMask, user.AboutText, 587 user.UserInventoryURI,
702 user.FirstLifeAboutText, user.Image, user.FirstLifeImage, user.WebLoginKey, 588 user.UserAssetURI, user.CanDoMask, user.WantDoMask, user.AboutText,
703 user.UserFlags, user.GodLevel, user.CustomType, user.Partner); 589 user.FirstLifeAboutText, user.Image, user.FirstLifeImage, user.WebLoginKey,
590 user.UserFlags, user.GodLevel, user.CustomType, user.Partner);
591
592 return true;
704 } 593 }
705 finally 594 catch
706 { 595 {
707 dbm.Release(); 596 return false;
708 } 597 }
709
710 return true;
711 } 598 }
712 599
713 /// <summary> 600 /// <summary>
@@ -742,41 +629,40 @@ namespace OpenSim.Data.MySQL
742 /// </summary> 629 /// </summary>
743 public override AvatarAppearance GetUserAppearance(UUID user) 630 public override AvatarAppearance GetUserAppearance(UUID user)
744 { 631 {
745 MySQLSuperManager dbm = GetLockedConnection("GetUserAppearance");
746 try 632 try
747 { 633 {
748 Dictionary<string, object> param = new Dictionary<string, object>(); 634 Dictionary<string, object> param = new Dictionary<string, object>();
749 param["?owner"] = user.ToString(); 635 param["?owner"] = user.ToString();
750 636
751 IDbCommand result = dbm.Manager.Query( 637 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
752 "SELECT * FROM " + m_appearanceTableName + " WHERE owner = ?owner", param);
753 IDataReader reader = result.ExecuteReader();
754
755 AvatarAppearance appearance = dbm.Manager.readAppearanceRow(reader);
756
757 reader.Dispose();
758 result.Dispose();
759
760 if (null == appearance)
761 { 638 {
762 m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString()); 639 dbcon.Open();
763 return null;
764 }
765
766 appearance.SetAttachments(GetUserAttachments(user));
767 640
768 return appearance; 641 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM " + m_appearanceTableName + " WHERE owner = ?owner", param))
642 {
643 using (IDataReader reader = result.ExecuteReader())
644 {
645 AvatarAppearance appearance = m_database.readAppearanceRow(reader);
646
647 if (appearance == null)
648 {
649 m_log.WarnFormat("[USER DB] No appearance found for user {0}", user.ToString());
650 return null;
651 }
652 else
653 {
654 appearance.SetAttachments(GetUserAttachments(user));
655 return appearance;
656 }
657 }
658 }
659 }
769 } 660 }
770 catch (Exception e) 661 catch (Exception e)
771 { 662 {
772 dbm.Manager.Reconnect(); 663 m_log.Error(e.Message, e);
773 m_log.Error(e.ToString());
774 return null; 664 return null;
775 } 665 }
776 finally
777 {
778 dbm.Release();
779 }
780 } 666 }
781 667
782 /// <summary> 668 /// <summary>
@@ -787,22 +673,16 @@ namespace OpenSim.Data.MySQL
787 // override 673 // override
788 public override void UpdateUserAppearance(UUID user, AvatarAppearance appearance) 674 public override void UpdateUserAppearance(UUID user, AvatarAppearance appearance)
789 { 675 {
790 MySQLSuperManager dbm = GetLockedConnection("UpdateUserAppearance");
791 try 676 try
792 { 677 {
793 appearance.Owner = user; 678 appearance.Owner = user;
794 dbm.Manager.insertAppearanceRow(appearance); 679 m_database.insertAppearanceRow(appearance);
795 680
796 UpdateUserAttachments(user, appearance.GetAttachments()); 681 UpdateUserAttachments(user, appearance.GetAttachments());
797 } 682 }
798 catch (Exception e) 683 catch (Exception e)
799 { 684 {
800 dbm.Manager.Reconnect(); 685 m_log.Error(e.Message, e);
801 m_log.Error(e.ToString());
802 }
803 finally
804 {
805 dbm.Release();
806 } 686 }
807 } 687 }
808 688
@@ -829,43 +709,33 @@ namespace OpenSim.Data.MySQL
829 Dictionary<string, object> param = new Dictionary<string, object>(); 709 Dictionary<string, object> param = new Dictionary<string, object>();
830 param["?uuid"] = agentID.ToString(); 710 param["?uuid"] = agentID.ToString();
831 711
832 MySQLSuperManager dbm = GetLockedConnection("GetUserAttachments");
833
834 try 712 try
835 { 713 {
836 IDbCommand result = dbm.Manager.Query( 714 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
837 "SELECT attachpoint, item, asset from " + m_attachmentsTableName + " WHERE UUID = ?uuid", param); 715 {
838 IDataReader reader = result.ExecuteReader(); 716 dbcon.Open();
839
840 Hashtable ret = dbm.Manager.readAttachments(reader);
841 717
842 reader.Dispose(); 718 using (IDbCommand result = m_database.Query(dbcon,
843 result.Dispose(); 719 "SELECT attachpoint, item, asset from " + m_attachmentsTableName + " WHERE UUID = ?uuid", param))
844 return ret; 720 {
721 using (IDataReader reader = result.ExecuteReader())
722 {
723 Hashtable ret = m_database.readAttachments(reader);
724 return ret;
725 }
726 }
727 }
845 } 728 }
846 catch (Exception e) 729 catch (Exception e)
847 { 730 {
848 dbm.Manager.Reconnect(); 731 m_log.Error(e.Message, e);
849 m_log.Error(e.ToString());
850 return null; 732 return null;
851 } 733 }
852 finally
853 {
854 dbm.Release();
855 }
856 } 734 }
857 735
858 public void UpdateUserAttachments(UUID agentID, Hashtable data) 736 public void UpdateUserAttachments(UUID agentID, Hashtable data)
859 { 737 {
860 MySQLSuperManager dbm = GetLockedConnection("UpdateUserAttachments"); 738 m_database.writeAttachments(agentID, data);
861 try
862 {
863 dbm.Manager.writeAttachments(agentID, data);
864 }
865 finally
866 {
867 dbm.Release();
868 }
869 } 739 }
870 740
871 public override void ResetAttachments(UUID userID) 741 public override void ResetAttachments(UUID userID)
@@ -873,19 +743,10 @@ namespace OpenSim.Data.MySQL
873 Dictionary<string, string> param = new Dictionary<string, string>(); 743 Dictionary<string, string> param = new Dictionary<string, string>();
874 param["?uuid"] = userID.ToString(); 744 param["?uuid"] = userID.ToString();
875 745
876 MySQLSuperManager dbm = GetLockedConnection("ResetAttachments"); 746 m_database.ExecuteParameterizedSql(
877 747 "UPDATE " + m_attachmentsTableName +
878 try 748 " SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = ?uuid",
879 { 749 param);
880 dbm.Manager.ExecuteParameterizedSql(
881 "UPDATE " + m_attachmentsTableName +
882 " SET asset = '00000000-0000-0000-0000-000000000000' WHERE UUID = ?uuid",
883 param);
884 }
885 finally
886 {
887 dbm.Release();
888 }
889 } 750 }
890 751
891 public override void LogoutUsers(UUID regionID) 752 public override void LogoutUsers(UUID regionID)
@@ -893,25 +754,18 @@ namespace OpenSim.Data.MySQL
893 Dictionary<string, string> param = new Dictionary<string, string>(); 754 Dictionary<string, string> param = new Dictionary<string, string>();
894 param["?regionID"] = regionID.ToString(); 755 param["?regionID"] = regionID.ToString();
895 756
896 MySQLSuperManager dbm = GetLockedConnection("LogoutUsers");
897
898 try 757 try
899 { 758 {
900 dbm.Manager.ExecuteParameterizedSql( 759 m_database.ExecuteParameterizedSql(
901 "update " + m_agentsTableName + " SET agentOnline = 0 " + 760 "update " + m_agentsTableName + " SET agentOnline = 0 " +
902 "where currentRegion = ?regionID", 761 "where currentRegion = ?regionID",
903 param); 762 param);
904 } 763 }
905 catch (Exception e) 764 catch (Exception e)
906 { 765 {
907 dbm.Manager.Reconnect(); 766 m_log.Error(e.Message, e);
908 m_log.Error(e.ToString());
909 return; 767 return;
910 } 768 }
911 finally
912 {
913 dbm.Release();
914 }
915 } 769 }
916 } 770 }
917} 771}