aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MSSQL/MSSQLGridData.cs
diff options
context:
space:
mode:
authorCharles Krinke2008-07-17 13:43:31 +0000
committerCharles Krinke2008-07-17 13:43:31 +0000
commite8412dcd42f6478f70f587d534a92a948cd6dd93 (patch)
treee3f2d084da7f40bd453bf06fefa43241facc0f86 /OpenSim/Data/MSSQL/MSSQLGridData.cs
parentmorphing OSHttpHandler interface into an abstract base class. adding (diff)
downloadopensim-SC_OLD-e8412dcd42f6478f70f587d534a92a948cd6dd93.zip
opensim-SC_OLD-e8412dcd42f6478f70f587d534a92a948cd6dd93.tar.gz
opensim-SC_OLD-e8412dcd42f6478f70f587d534a92a948cd6dd93.tar.bz2
opensim-SC_OLD-e8412dcd42f6478f70f587d534a92a948cd6dd93.tar.xz
Mantis#1736. Thank you kindly, StrawberryFride for a patch that:
Many issues with handling connections in MSSQL, have rearchitected to ensure that connections are always opened and closed in a timely fashion & disposed of cleanly, and removed unnecessary lock statements. SQL Server performance seems to have improved considerably as a result, and various timeout errors seem to have been fixed.
Diffstat (limited to 'OpenSim/Data/MSSQL/MSSQLGridData.cs')
-rw-r--r--OpenSim/Data/MSSQL/MSSQLGridData.cs123
1 files changed, 52 insertions, 71 deletions
diff --git a/OpenSim/Data/MSSQL/MSSQLGridData.cs b/OpenSim/Data/MSSQL/MSSQLGridData.cs
index 1ed412b..0abd0d0 100644
--- a/OpenSim/Data/MSSQL/MSSQLGridData.cs
+++ b/OpenSim/Data/MSSQL/MSSQLGridData.cs
@@ -84,17 +84,17 @@ namespace OpenSim.Data.MSSQL
84 /// </summary> 84 /// </summary>
85 private void TestTables() 85 private void TestTables()
86 { 86 {
87 IDbCommand cmd = database.Query("SELECT TOP 1 * FROM "+m_regionsTableName, new Dictionary<string, string>()); 87 using (IDbCommand cmd = database.Query("SELECT TOP 1 * FROM " + m_regionsTableName, new Dictionary<string, string>()))
88
89 try
90 {
91 cmd.ExecuteNonQuery();
92 cmd.Dispose();
93 }
94 catch (Exception)
95 { 88 {
96 m_log.Info("[GRID DB]: MSSQL Database doesn't exist... creating"); 89 try
97 database.ExecuteResourceSql("Mssql-regions.sql"); 90 {
91 cmd.ExecuteNonQuery();
92 }
93 catch (Exception)
94 {
95 m_log.Info("[GRID DB]: MSSQL Database doesn't exist... creating");
96 database.ExecuteResourceSql("Mssql-regions.sql");
97 }
98 } 98 }
99 } 99 }
100 100
@@ -103,7 +103,7 @@ namespace OpenSim.Data.MSSQL
103 /// </summary> 103 /// </summary>
104 override public void Close() 104 override public void Close()
105 { 105 {
106 database.Close(); 106 // nothing to close
107 } 107 }
108 108
109 /// <summary> 109 /// <summary>
@@ -146,32 +146,22 @@ namespace OpenSim.Data.MSSQL
146 /// <returns>Sim profile</returns> 146 /// <returns>Sim profile</returns>
147 override public RegionProfileData GetProfileByHandle(ulong handle) 147 override public RegionProfileData GetProfileByHandle(ulong handle)
148 { 148 {
149 IDataReader reader = null; 149
150 Dictionary<string, string> param = new Dictionary<string, string>();
151 param["handle"] = handle.ToString();
152
150 try 153 try
151 { 154 {
152 if (database.getConnection().State == ConnectionState.Closed) 155 using (IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle", param))
156 using (IDataReader reader = result.ExecuteReader())
153 { 157 {
154 database.Reconnect(); 158 return database.getRegionRow(reader);
155 } 159 }
156 Dictionary<string, string> param = new Dictionary<string, string>();
157 param["handle"] = handle.ToString();
158 IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE regionHandle = @handle", param);
159 reader = result.ExecuteReader();
160
161 RegionProfileData row = database.getRegionRow(reader);
162 reader.Close();
163 result.Dispose();
164
165 return row;
166 } 160 }
167 catch (Exception) 161 catch
168 { 162 {
169 if (reader != null) 163 return null;
170 {
171 reader.Close();
172 }
173 } 164 }
174 return null;
175 } 165 }
176 166
177 /// <summary> 167 /// <summary>
@@ -183,14 +173,13 @@ namespace OpenSim.Data.MSSQL
183 { 173 {
184 Dictionary<string, string> param = new Dictionary<string, string>(); 174 Dictionary<string, string> param = new Dictionary<string, string>();
185 param["uuid"] = uuid.ToString(); 175 param["uuid"] = uuid.ToString();
186 IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid", param);
187 IDataReader reader = result.ExecuteReader();
188 176
189 RegionProfileData row = database.getRegionRow(reader); 177 using (IDbCommand result = database.Query("SELECT * FROM " + m_regionsTableName + " WHERE uuid = @uuid", param))
190 reader.Close(); 178 using (IDataReader reader = result.ExecuteReader())
191 result.Dispose(); 179 {
180 return database.getRegionRow(reader);
181 }
192 182
193 return row;
194 } 183 }
195 184
196 /// <summary> 185 /// <summary>
@@ -204,25 +193,19 @@ namespace OpenSim.Data.MSSQL
204 { 193 {
205 try 194 try
206 { 195 {
207 lock (database) 196 Dictionary<string, string> param = new Dictionary<string, string>();
197 // Add % because this is a like query.
198 param["?regionName"] = regionName + "%";
199 // Order by statement will return shorter matches first. Only returns one record or no record.
200 using (IDbCommand result = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like ?regionName order by regionName", param))
201 using (IDataReader reader = result.ExecuteReader())
208 { 202 {
209 Dictionary<string, string> param = new Dictionary<string, string>(); 203 return database.getRegionRow(reader);
210 // Add % because this is a like query.
211 param["?regionName"] = regionName + "%";
212 // Order by statement will return shorter matches first. Only returns one record or no record.
213 IDbCommand result = database.Query("SELECT top 1 * FROM " + m_regionsTableName + " WHERE regionName like ?regionName order by regionName", param);
214 IDataReader reader = result.ExecuteReader();
215
216 RegionProfileData row = database.getRegionRow(reader);
217 reader.Close();
218 result.Dispose();
219
220 return row;
221 } 204 }
205
222 } 206 }
223 catch (Exception e) 207 catch (Exception e)
224 { 208 {
225 database.Reconnect();
226 m_log.Error(e.ToString()); 209 m_log.Error(e.ToString());
227 return null; 210 return null;
228 } 211 }
@@ -324,12 +307,13 @@ namespace OpenSim.Data.MSSQL
324 307
325 try 308 try
326 { 309 {
327 IDbCommand result = database.Query(sql, parameters); 310 using (IDbCommand result = database.Query(sql, parameters))
311 {
328 312
329 if (result.ExecuteNonQuery() == 1) 313 if (result.ExecuteNonQuery() == 1)
330 returnval = true; 314 returnval = true;
331 315
332 result.Dispose(); 316 }
333 } 317 }
334 catch (Exception e) 318 catch (Exception e)
335 { 319 {
@@ -347,17 +331,15 @@ namespace OpenSim.Data.MSSQL
347 { 331 {
348 //Insert new region 332 //Insert new region
349 string sql = 333 string sql =
350 "INSERT INTO " + m_regionsTableName + " ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI], "; 334 "INSERT INTO " + m_regionsTableName + @" ([regionHandle], [regionName], [uuid], [regionRecvKey], [regionSecret], [regionSendKey], [regionDataURI],
351 sql += 335 [serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle],
352 "[serverIP], [serverPort], [serverURI], [locX], [locY], [locZ], [eastOverrideHandle], [westOverrideHandle], [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], "; 336 [southOverrideHandle], [northOverrideHandle], [regionAssetURI], [regionAssetRecvKey], [regionAssetSendKey],
353 sql += 337 [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort],
354 "[regionAssetSendKey], [regionUserURI], [regionUserRecvKey], [regionUserSendKey], [regionMapTexture], [serverHttpPort], [serverRemotingPort], [owner_uuid]) VALUES "; 338 [serverRemotingPort], [owner_uuid])
355 339 VALUES (@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI,
356 sql += "(@regionHandle, @regionName, @uuid, @regionRecvKey, @regionSecret, @regionSendKey, @regionDataURI, "; 340 @serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle,
357 sql += 341 @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, @regionAssetSendKey,
358 "@serverIP, @serverPort, @serverURI, @locX, @locY, @locZ, @eastOverrideHandle, @westOverrideHandle, @southOverrideHandle, @northOverrideHandle, @regionAssetURI, @regionAssetRecvKey, "; 342 @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid);";
359 sql +=
360 "@regionAssetSendKey, @regionUserURI, @regionUserRecvKey, @regionUserSendKey, @regionMapTexture, @serverHttpPort, @serverRemotingPort, @owner_uuid);";
361 343
362 Dictionary<string, string> parameters = new Dictionary<string, string>(); 344 Dictionary<string, string> parameters = new Dictionary<string, string>();
363 345
@@ -393,12 +375,11 @@ namespace OpenSim.Data.MSSQL
393 375
394 try 376 try
395 { 377 {
396 IDbCommand result = database.Query(sql, parameters); 378 using (IDbCommand result = database.Query(sql, parameters))
397 379 {
398 if (result.ExecuteNonQuery() == 1) 380 if (result.ExecuteNonQuery() == 1)
399 returnval = true; 381 returnval = true;
400 382 }
401 result.Dispose();
402 } 383 }
403 catch (Exception e) 384 catch (Exception e)
404 { 385 {