aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLRegionData.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/MySQLRegionData.cs
parentFixing an incorrect logging message in insertUserRow (diff)
downloadopensim-SC-e1b5c612472b9d1acf47383c0bf75b555daff2e6.zip
opensim-SC-e1b5c612472b9d1acf47383c0bf75b555daff2e6.tar.gz
opensim-SC-e1b5c612472b9d1acf47383c0bf75b555daff2e6.tar.bz2
opensim-SC-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 '')
-rw-r--r--OpenSim/Data/MySQL/MySQLRegionData.cs257
1 files changed, 133 insertions, 124 deletions
diff --git a/OpenSim/Data/MySQL/MySQLRegionData.cs b/OpenSim/Data/MySQL/MySQLRegionData.cs
index b0075e8..a1a08b1 100644
--- a/OpenSim/Data/MySQL/MySQLRegionData.cs
+++ b/OpenSim/Data/MySQL/MySQLRegionData.cs
@@ -38,16 +38,21 @@ namespace OpenSim.Data.MySQL
38 public class MySqlRegionData : MySqlFramework, IRegionData 38 public class MySqlRegionData : MySqlFramework, IRegionData
39 { 39 {
40 private string m_Realm; 40 private string m_Realm;
41 private List<string> m_ColumnNames = null; 41 private List<string> m_ColumnNames;
42// private int m_LastExpire = 0; 42 //private string m_connectionString;
43 43
44 public MySqlRegionData(string connectionString, string realm) 44 public MySqlRegionData(string connectionString, string realm)
45 : base(connectionString) 45 : base(connectionString)
46 { 46 {
47 m_Realm = realm; 47 m_Realm = realm;
48 m_connectionString = connectionString;
48 49
49 Migration m = new Migration(m_Connection, GetType().Assembly, "GridStore"); 50 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
50 m.Update(); 51 {
52 dbcon.Open();
53 Migration m = new Migration(dbcon, GetType().Assembly, "GridStore");
54 m.Update();
55 }
51 } 56 }
52 57
53 public List<RegionData> Get(string regionName, UUID scopeID) 58 public List<RegionData> Get(string regionName, UUID scopeID)
@@ -56,12 +61,13 @@ namespace OpenSim.Data.MySQL
56 if (scopeID != UUID.Zero) 61 if (scopeID != UUID.Zero)
57 command += " and ScopeID = ?scopeID"; 62 command += " and ScopeID = ?scopeID";
58 63
59 MySqlCommand cmd = new MySqlCommand(command); 64 using (MySqlCommand cmd = new MySqlCommand(command))
60 65 {
61 cmd.Parameters.AddWithValue("?regionName", regionName); 66 cmd.Parameters.AddWithValue("?regionName", regionName);
62 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); 67 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
63 68
64 return RunCommand(cmd); 69 return RunCommand(cmd);
70 }
65 } 71 }
66 72
67 public RegionData Get(int posX, int posY, UUID scopeID) 73 public RegionData Get(int posX, int posY, UUID scopeID)
@@ -70,17 +76,18 @@ namespace OpenSim.Data.MySQL
70 if (scopeID != UUID.Zero) 76 if (scopeID != UUID.Zero)
71 command += " and ScopeID = ?scopeID"; 77 command += " and ScopeID = ?scopeID";
72 78
73 MySqlCommand cmd = new MySqlCommand(command); 79 using (MySqlCommand cmd = new MySqlCommand(command))
74 80 {
75 cmd.Parameters.AddWithValue("?posX", posX.ToString()); 81 cmd.Parameters.AddWithValue("?posX", posX.ToString());
76 cmd.Parameters.AddWithValue("?posY", posY.ToString()); 82 cmd.Parameters.AddWithValue("?posY", posY.ToString());
77 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); 83 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
78 84
79 List<RegionData> ret = RunCommand(cmd); 85 List<RegionData> ret = RunCommand(cmd);
80 if (ret.Count == 0) 86 if (ret.Count == 0)
81 return null; 87 return null;
82 88
83 return ret[0]; 89 return ret[0];
90 }
84 } 91 }
85 92
86 public RegionData Get(UUID regionID, UUID scopeID) 93 public RegionData Get(UUID regionID, UUID scopeID)
@@ -89,16 +96,17 @@ namespace OpenSim.Data.MySQL
89 if (scopeID != UUID.Zero) 96 if (scopeID != UUID.Zero)
90 command += " and ScopeID = ?scopeID"; 97 command += " and ScopeID = ?scopeID";
91 98
92 MySqlCommand cmd = new MySqlCommand(command); 99 using (MySqlCommand cmd = new MySqlCommand(command))
93 100 {
94 cmd.Parameters.AddWithValue("?regionID", regionID.ToString()); 101 cmd.Parameters.AddWithValue("?regionID", regionID.ToString());
95 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); 102 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
96 103
97 List<RegionData> ret = RunCommand(cmd); 104 List<RegionData> ret = RunCommand(cmd);
98 if (ret.Count == 0) 105 if (ret.Count == 0)
99 return null; 106 return null;
100 107
101 return ret[0]; 108 return ret[0];
109 }
102 } 110 }
103 111
104 public List<RegionData> Get(int startX, int startY, int endX, int endY, UUID scopeID) 112 public List<RegionData> Get(int startX, int startY, int endX, int endY, UUID scopeID)
@@ -107,74 +115,79 @@ namespace OpenSim.Data.MySQL
107 if (scopeID != UUID.Zero) 115 if (scopeID != UUID.Zero)
108 command += " and ScopeID = ?scopeID"; 116 command += " and ScopeID = ?scopeID";
109 117
110 MySqlCommand cmd = new MySqlCommand(command); 118 using (MySqlCommand cmd = new MySqlCommand(command))
111 119 {
112 cmd.Parameters.AddWithValue("?startX", startX.ToString()); 120 cmd.Parameters.AddWithValue("?startX", startX.ToString());
113 cmd.Parameters.AddWithValue("?startY", startY.ToString()); 121 cmd.Parameters.AddWithValue("?startY", startY.ToString());
114 cmd.Parameters.AddWithValue("?endX", endX.ToString()); 122 cmd.Parameters.AddWithValue("?endX", endX.ToString());
115 cmd.Parameters.AddWithValue("?endY", endY.ToString()); 123 cmd.Parameters.AddWithValue("?endY", endY.ToString());
116 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); 124 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
117 125
118 return RunCommand(cmd); 126 return RunCommand(cmd);
127 }
119 } 128 }
120 129
121 public List<RegionData> RunCommand(MySqlCommand cmd) 130 public List<RegionData> RunCommand(MySqlCommand cmd)
122 { 131 {
123 List<RegionData> retList = new List<RegionData>(); 132 List<RegionData> retList = new List<RegionData>();
124 133
125 IDataReader result = ExecuteReader(cmd); 134 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
126
127 while (result.Read())
128 { 135 {
129 RegionData ret = new RegionData(); 136 dbcon.Open();
130 ret.Data = new Dictionary<string, object>(); 137 cmd.Connection = dbcon;
131 138
132 UUID regionID; 139 using (IDataReader result = cmd.ExecuteReader())
133 UUID.TryParse(result["uuid"].ToString(), out regionID);
134 ret.RegionID = regionID;
135 UUID scope;
136 UUID.TryParse(result["ScopeID"].ToString(), out scope);
137 ret.ScopeID = scope;
138 ret.RegionName = result["regionName"].ToString();
139 ret.posX = Convert.ToInt32(result["locX"]);
140 ret.posY = Convert.ToInt32(result["locY"]);
141 ret.sizeX = Convert.ToInt32(result["sizeX"]);
142 ret.sizeY = Convert.ToInt32(result["sizeY"]);
143
144 if (m_ColumnNames == null)
145 { 140 {
146 m_ColumnNames = new List<string>(); 141 while (result.Read())
142 {
143 RegionData ret = new RegionData();
144 ret.Data = new Dictionary<string, object>();
145
146 UUID regionID;
147 UUID.TryParse(result["uuid"].ToString(), out regionID);
148 ret.RegionID = regionID;
149 UUID scope;
150 UUID.TryParse(result["ScopeID"].ToString(), out scope);
151 ret.ScopeID = scope;
152 ret.RegionName = result["regionName"].ToString();
153 ret.posX = Convert.ToInt32(result["locX"]);
154 ret.posY = Convert.ToInt32(result["locY"]);
155 ret.sizeX = Convert.ToInt32(result["sizeX"]);
156 ret.sizeY = Convert.ToInt32(result["sizeY"]);
157
158 if (m_ColumnNames == null)
159 {
160 m_ColumnNames = new List<string>();
161
162 DataTable schemaTable = result.GetSchemaTable();
163 foreach (DataRow row in schemaTable.Rows)
164 {
165 if (row["ColumnName"] != null)
166 m_ColumnNames.Add(row["ColumnName"].ToString());
167 }
168 }
147 169
148 DataTable schemaTable = result.GetSchemaTable(); 170 foreach (string s in m_ColumnNames)
149 foreach (DataRow row in schemaTable.Rows)
150 { 171 {
151 if (row["ColumnName"] != null) 172 if (s == "uuid")
152 m_ColumnNames.Add(row["ColumnName"].ToString()); 173 continue;
174 if (s == "ScopeID")
175 continue;
176 if (s == "regionName")
177 continue;
178 if (s == "locX")
179 continue;
180 if (s == "locY")
181 continue;
182
183 ret.Data[s] = result[s].ToString();
153 } 184 }
154 }
155 185
156 foreach (string s in m_ColumnNames) 186 retList.Add(ret);
157 { 187 }
158 if (s == "uuid")
159 continue;
160 if (s == "ScopeID")
161 continue;
162 if (s == "regionName")
163 continue;
164 if (s == "locX")
165 continue;
166 if (s == "locY")
167 continue;
168
169 ret.Data[s] = result[s].ToString();
170 } 188 }
171
172 retList.Add(ret);
173 } 189 }
174 190
175 result.Close();
176 CloseReaderCommand(cmd);
177
178 return retList; 191 return retList;
179 } 192 }
180 193
@@ -201,76 +214,72 @@ namespace OpenSim.Data.MySQL
201 214
202 string[] fields = new List<string>(data.Data.Keys).ToArray(); 215 string[] fields = new List<string>(data.Data.Keys).ToArray();
203 216
204 MySqlCommand cmd = new MySqlCommand(); 217 using (MySqlCommand cmd = new MySqlCommand())
205
206 string update = "update `"+m_Realm+"` set locX=?posX, locY=?posY, sizeX=?sizeX, sizeY=?sizeY";
207 foreach (string field in fields)
208 { 218 {
209 update += ", "; 219 string update = "update `" + m_Realm + "` set locX=?posX, locY=?posY, sizeX=?sizeX, sizeY=?sizeY";
210 update += "`" + field + "` = ?"+field; 220 foreach (string field in fields)
211 221 {
212 cmd.Parameters.AddWithValue("?"+field, data.Data[field]); 222 update += ", ";
213 } 223 update += "`" + field + "` = ?" + field;
214
215 update += " where uuid = ?regionID";
216 224
217 if (data.ScopeID != UUID.Zero) 225 cmd.Parameters.AddWithValue("?" + field, data.Data[field]);
218 update += " and ScopeID = ?scopeID"; 226 }
219 227
220 cmd.CommandText = update; 228 update += " where uuid = ?regionID";
221 cmd.Parameters.AddWithValue("?regionID", data.RegionID.ToString());
222 cmd.Parameters.AddWithValue("?regionName", data.RegionName);
223 cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString());
224 cmd.Parameters.AddWithValue("?posX", data.posX.ToString());
225 cmd.Parameters.AddWithValue("?posY", data.posY.ToString());
226 cmd.Parameters.AddWithValue("?sizeX", data.sizeX.ToString());
227 cmd.Parameters.AddWithValue("?sizeY", data.sizeY.ToString());
228 229
229 if (ExecuteNonQuery(cmd) < 1) 230 if (data.ScopeID != UUID.Zero)
230 { 231 update += " and ScopeID = ?scopeID";
231 string insert = "insert into `" + m_Realm + "` (`uuid`, `ScopeID`, `locX`, `locY`, `sizeX`, `sizeY`, `regionName`, `" +
232 String.Join("`, `", fields) +
233 "`) values ( ?regionID, ?scopeID, ?posX, ?posY, ?sizeX, ?sizeY, ?regionName, ?" + String.Join(", ?", fields) + ")";
234 232
235 cmd.CommandText = insert; 233 cmd.CommandText = update;
234 cmd.Parameters.AddWithValue("?regionID", data.RegionID.ToString());
235 cmd.Parameters.AddWithValue("?regionName", data.RegionName);
236 cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString());
237 cmd.Parameters.AddWithValue("?posX", data.posX.ToString());
238 cmd.Parameters.AddWithValue("?posY", data.posY.ToString());
239 cmd.Parameters.AddWithValue("?sizeX", data.sizeX.ToString());
240 cmd.Parameters.AddWithValue("?sizeY", data.sizeY.ToString());
236 241
237 if (ExecuteNonQuery(cmd) < 1) 242 if (ExecuteNonQuery(cmd) < 1)
238 { 243 {
239 cmd.Dispose(); 244 string insert = "insert into `" + m_Realm + "` (`uuid`, `ScopeID`, `locX`, `locY`, `sizeX`, `sizeY`, `regionName`, `" +
240 return false; 245 String.Join("`, `", fields) +
246 "`) values ( ?regionID, ?scopeID, ?posX, ?posY, ?sizeX, ?sizeY, ?regionName, ?" + String.Join(", ?", fields) + ")";
247
248 cmd.CommandText = insert;
249
250 if (ExecuteNonQuery(cmd) < 1)
251 {
252 return false;
253 }
241 } 254 }
242 } 255 }
243 256
244 cmd.Dispose();
245
246 return true; 257 return true;
247 } 258 }
248 259
249 public bool SetDataItem(UUID regionID, string item, string value) 260 public bool SetDataItem(UUID regionID, string item, string value)
250 { 261 {
251 MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + 262 using (MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + "` set `" + item + "` = ?" + item + " where uuid = ?UUID"))
252 "` set `" + item + "` = ?" + item + " where uuid = ?UUID"); 263 {
253 264 cmd.Parameters.AddWithValue("?" + item, value);
254 265 cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
255 cmd.Parameters.AddWithValue("?"+item, value);
256 cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
257 266
258 if (ExecuteNonQuery(cmd) > 0) 267 if (ExecuteNonQuery(cmd) > 0)
259 return true; 268 return true;
269 }
260 270
261 return false; 271 return false;
262 } 272 }
263 273
264 public bool Delete(UUID regionID) 274 public bool Delete(UUID regionID)
265 { 275 {
266 MySqlCommand cmd = new MySqlCommand("delete from `" + m_Realm + 276 using (MySqlCommand cmd = new MySqlCommand("delete from `" + m_Realm + "` where uuid = ?UUID"))
267 "` where uuid = ?UUID"); 277 {
268 278 cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
269
270 cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
271 279
272 if (ExecuteNonQuery(cmd) > 0) 280 if (ExecuteNonQuery(cmd) > 0)
273 return true; 281 return true;
282 }
274 283
275 return false; 284 return false;
276 } 285 }
generated by cgit v1.1 at 2024-11-16 08:40:40 +0000