From e1b5c612472b9d1acf47383c0bf75b555daff2e6 Mon Sep 17 00:00:00 2001 From: Master ScienceSim Date: Thu, 4 Feb 2010 13:19:30 -0800 Subject: 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. --- OpenSim/Data/MySQL/MySQLUserAccountData.cs | 156 +++++++++++++++-------------- 1 file changed, 80 insertions(+), 76 deletions(-) (limited to 'OpenSim/Data/MySQL/MySQLUserAccountData.cs') diff --git a/OpenSim/Data/MySQL/MySQLUserAccountData.cs b/OpenSim/Data/MySQL/MySQLUserAccountData.cs index d48144d..3cb0010 100644 --- a/OpenSim/Data/MySQL/MySQLUserAccountData.cs +++ b/OpenSim/Data/MySQL/MySQLUserAccountData.cs @@ -38,16 +38,21 @@ namespace OpenSim.Data.MySQL public class MySqlUserAccountData : MySqlFramework, IUserAccountData { private string m_Realm; - private List m_ColumnNames = null; -// private int m_LastExpire = 0; + private List m_ColumnNames; + // private string m_connectionString; public MySqlUserAccountData(string connectionString, string realm) : base(connectionString) { m_Realm = realm; + m_connectionString = connectionString; - Migration m = new Migration(m_Connection, GetType().Assembly, "UserStore"); - m.Update(); + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + Migration m = new Migration(dbcon, GetType().Assembly, "UserStore"); + m.Update(); + } } public List Query(UUID principalID, UUID scopeID, string query) @@ -64,49 +69,49 @@ namespace OpenSim.Data.MySQL if (scopeID != UUID.Zero) command += " and ScopeID = ?scopeID"; - MySqlCommand cmd = new MySqlCommand(command); + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand(command, dbcon); - cmd.Parameters.AddWithValue("?principalID", principalID.ToString()); - cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); + cmd.Parameters.AddWithValue("?principalID", principalID.ToString()); + cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); - IDataReader result = ExecuteReader(cmd); + IDataReader result = cmd.ExecuteReader(); - if (result.Read()) - { - ret.PrincipalID = principalID; - UUID scope; - UUID.TryParse(result["ScopeID"].ToString(), out scope); - ret.ScopeID = scope; - - if (m_ColumnNames == null) + if (result.Read()) { - m_ColumnNames = new List(); - - DataTable schemaTable = result.GetSchemaTable(); - foreach (DataRow row in schemaTable.Rows) - m_ColumnNames.Add(row["ColumnName"].ToString()); + ret.PrincipalID = principalID; + UUID scope; + UUID.TryParse(result["ScopeID"].ToString(), out scope); + ret.ScopeID = scope; + + if (m_ColumnNames == null) + { + m_ColumnNames = new List(); + + DataTable schemaTable = result.GetSchemaTable(); + foreach (DataRow row in schemaTable.Rows) + m_ColumnNames.Add(row["ColumnName"].ToString()); + } + + foreach (string s in m_ColumnNames) + { + if (s == "UUID") + continue; + if (s == "ScopeID") + continue; + + ret.Data[s] = result[s].ToString(); + } + + return ret; } - - foreach (string s in m_ColumnNames) + else { - if (s == "UUID") - continue; - if (s == "ScopeID") - continue; - - ret.Data[s] = result[s].ToString(); + return null; } - - result.Close(); - CloseReaderCommand(cmd); - - return ret; } - - result.Close(); - CloseReaderCommand(cmd); - - return null; } public bool Store(UserAccountData data) @@ -118,61 +123,60 @@ namespace OpenSim.Data.MySQL string[] fields = new List(data.Data.Keys).ToArray(); - MySqlCommand cmd = new MySqlCommand(); - - string update = "update `"+m_Realm+"` set "; - bool first = true; - foreach (string field in fields) + using (MySqlCommand cmd = new MySqlCommand()) { - if (!first) - update += ", "; - update += "`" + field + "` = ?"+field; - - first = false; - - cmd.Parameters.AddWithValue("?"+field, data.Data[field]); - } + string update = "update `" + m_Realm + "` set "; + bool first = true; + foreach (string field in fields) + { + if (!first) + update += ", "; + update += "`" + field + "` = ?" + field; - update += " where UUID = ?principalID"; + first = false; - if (data.ScopeID != UUID.Zero) - update += " and ScopeID = ?scopeID"; + cmd.Parameters.AddWithValue("?" + field, data.Data[field]); + } - cmd.CommandText = update; - cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString()); - cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString()); + update += " where UUID = ?principalID"; - if (ExecuteNonQuery(cmd) < 1) - { - string insert = "insert into `" + m_Realm + "` (`UUID`, `ScopeID`, `" + - String.Join("`, `", fields) + - "`) values (?principalID, ?scopeID, ?" + String.Join(", ?", fields) + ")"; + if (data.ScopeID != UUID.Zero) + update += " and ScopeID = ?scopeID"; - cmd.CommandText = insert; + cmd.CommandText = update; + cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString()); + cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString()); if (ExecuteNonQuery(cmd) < 1) { - cmd.Dispose(); - return false; + string insert = "insert into `" + m_Realm + "` (`UUID`, `ScopeID`, `" + + String.Join("`, `", fields) + + "`) values (?principalID, ?scopeID, ?" + String.Join(", ?", fields) + ")"; + + cmd.CommandText = insert; + + if (ExecuteNonQuery(cmd) < 1) + { + cmd.Dispose(); + return false; + } } } - cmd.Dispose(); - return true; } public bool SetDataItem(UUID principalID, string item, string value) { - MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + - "` set `" + item + "` = ?" + item + " where UUID = ?UUID"); - - - cmd.Parameters.AddWithValue("?"+item, value); - cmd.Parameters.AddWithValue("?UUID", principalID.ToString()); + using (MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + "` set `" + + item + "` = ?" + item + " where UUID = ?UUID")) + { + cmd.Parameters.AddWithValue("?" + item, value); + cmd.Parameters.AddWithValue("?UUID", principalID.ToString()); - if (ExecuteNonQuery(cmd) > 0) - return true; + if (ExecuteNonQuery(cmd) > 0) + return true; + } return false; } -- cgit v1.1