aboutsummaryrefslogtreecommitdiffstatshomepage
diff options
context:
space:
mode:
authorMaster ScienceSim2010-02-04 13:19:30 -0800
committerJohn Hurliman2010-02-05 18:07:59 -0800
commite1b5c612472b9d1acf47383c0bf75b555daff2e6 (patch)
tree083896698038fbdad59c2bd3adeba9b290c5ce1b
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
-rw-r--r--OpenSim/Data/MySQL/MySQLAssetData.cs355
-rw-r--r--OpenSim/Data/MySQL/MySQLAuthenticationData.cs71
-rw-r--r--OpenSim/Data/MySQL/MySQLEstateData.cs440
-rw-r--r--OpenSim/Data/MySQL/MySQLFramework.cs62
-rw-r--r--OpenSim/Data/MySQL/MySQLGenericTableHandler.cs215
-rw-r--r--OpenSim/Data/MySQL/MySQLGridData.cs338
-rw-r--r--OpenSim/Data/MySQL/MySQLInventoryData.cs847
-rw-r--r--OpenSim/Data/MySQL/MySQLLegacyRegionData.cs924
-rw-r--r--OpenSim/Data/MySQL/MySQLLogData.cs16
-rw-r--r--OpenSim/Data/MySQL/MySQLManager.cs428
-rw-r--r--OpenSim/Data/MySQL/MySQLRegionData.cs257
-rw-r--r--OpenSim/Data/MySQL/MySQLUserAccountData.cs156
-rw-r--r--OpenSim/Data/MySQL/MySQLUserData.cs676
-rw-r--r--OpenSim/Data/MySQL/MySQLXInventoryData.cs65
-rw-r--r--OpenSim/Data/MySQL/Tests/MySQLGridTest.cs9
15 files changed, 2230 insertions, 2629 deletions
diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs
index 6a4ccd7..666c22f 100644
--- a/OpenSim/Data/MySQL/MySQLAssetData.cs
+++ b/OpenSim/Data/MySQL/MySQLAssetData.cs
@@ -43,10 +43,13 @@ namespace OpenSim.Data.MySQL
43 { 43 {
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45 45
46 private MySQLManager _dbConnection; 46 private string m_connectionString;
47 private object m_dbLock = new object();
47 48
48 #region IPlugin Members 49 #region IPlugin Members
49 50
51 public override string Version { get { return "1.0.0.0"; } }
52
50 /// <summary> 53 /// <summary>
51 /// <para>Initialises Asset interface</para> 54 /// <para>Initialises Asset interface</para>
52 /// <para> 55 /// <para>
@@ -58,63 +61,29 @@ namespace OpenSim.Data.MySQL
58 /// </para> 61 /// </para>
59 /// </summary> 62 /// </summary>
60 /// <param name="connect">connect string</param> 63 /// <param name="connect">connect string</param>
61 override public void Initialise(string connect) 64 public override void Initialise(string connect)
62 { 65 {
63 // TODO: This will let you pass in the connect string in 66 m_connectionString = connect;
64 // the config, though someone will need to write that.
65 if (connect == String.Empty)
66 {
67 // This is old seperate config file
68 m_log.Warn("no connect string, using old mysql_connection.ini instead");
69 Initialise();
70 }
71 else
72 {
73 _dbConnection = new MySQLManager(connect);
74 }
75 67
76 // This actually does the roll forward assembly stuff 68 // This actually does the roll forward assembly stuff
77 Assembly assem = GetType().Assembly; 69 Assembly assem = GetType().Assembly;
78 Migration m = new Migration(_dbConnection.Connection, assem, "AssetStore");
79 70
80 m.Update(); 71 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
72 {
73 dbcon.Open();
74 Migration m = new Migration(dbcon, assem, "AssetStore");
75 m.Update();
76 }
81 } 77 }
82 78
83 /// <summary>
84 /// <para>Initialises Asset interface</para>
85 /// <para>
86 /// <list type="bullet">
87 /// <item>Loads and initialises the MySQL storage plugin</item>
88 /// <item>uses the obsolete mysql_connection.ini</item>
89 /// </list>
90 /// </para>
91 /// </summary>
92 /// <remarks>DEPRECATED and shouldn't be used</remarks>
93 public override void Initialise() 79 public override void Initialise()
94 { 80 {
95 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); 81 throw new NotImplementedException();
96 string hostname = GridDataMySqlFile.ParseFileReadValue("hostname");
97 string database = GridDataMySqlFile.ParseFileReadValue("database");
98 string username = GridDataMySqlFile.ParseFileReadValue("username");
99 string password = GridDataMySqlFile.ParseFileReadValue("password");
100 string pooling = GridDataMySqlFile.ParseFileReadValue("pooling");
101 string port = GridDataMySqlFile.ParseFileReadValue("port");
102
103 _dbConnection = new MySQLManager(hostname, database, username, password, pooling, port);
104
105 } 82 }
106 83
107 public override void Dispose() { } 84 public override void Dispose() { }
108 85
109 /// <summary> 86 /// <summary>
110 /// Database provider version
111 /// </summary>
112 override public string Version
113 {
114 get { return _dbConnection.getVersion(); }
115 }
116
117 /// <summary>
118 /// The name of this DB provider 87 /// The name of this DB provider
119 /// </summary> 88 /// </summary>
120 override public string Name 89 override public string Name
@@ -135,46 +104,43 @@ namespace OpenSim.Data.MySQL
135 override public AssetBase GetAsset(UUID assetID) 104 override public AssetBase GetAsset(UUID assetID)
136 { 105 {
137 AssetBase asset = null; 106 AssetBase asset = null;
138 lock (_dbConnection) 107 lock (m_dbLock)
139 { 108 {
140 _dbConnection.CheckConnection(); 109 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
110 {
111 dbcon.Open();
141 112
142 MySqlCommand cmd = 113 using (MySqlCommand cmd = new MySqlCommand(
143 new MySqlCommand(
144 "SELECT name, description, assetType, local, temporary, data FROM assets WHERE id=?id", 114 "SELECT name, description, assetType, local, temporary, data FROM assets WHERE id=?id",
145 _dbConnection.Connection); 115 dbcon))
146 cmd.Parameters.AddWithValue("?id", assetID.ToString());
147
148 try
149 {
150 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
151 { 116 {
152 if (dbReader.Read()) 117 cmd.Parameters.AddWithValue("?id", assetID.ToString());
153 {
154 asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"]);
155 asset.Data = (byte[]) dbReader["data"];
156 asset.Description = (string) dbReader["description"];
157 118
158 string local = dbReader["local"].ToString(); 119 try
159 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) 120 {
160 asset.Local = true; 121 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
161 else 122 {
162 asset.Local = false; 123 if (dbReader.Read())
163 124 {
164 asset.Temporary = Convert.ToBoolean(dbReader["temporary"]); 125 asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"]);
126 asset.Data = (byte[])dbReader["data"];
127 asset.Description = (string)dbReader["description"];
128
129 string local = dbReader["local"].ToString();
130 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
131 asset.Local = true;
132 else
133 asset.Local = false;
134
135 asset.Temporary = Convert.ToBoolean(dbReader["temporary"]);
136 }
137 }
138 }
139 catch (Exception e)
140 {
141 m_log.Error("[ASSETS DB]: MySql failure fetching asset " + assetID + ": " + e.Message);
165 } 142 }
166 dbReader.Close();
167 cmd.Dispose();
168 } 143 }
169 if (asset != null)
170 UpdateAccessTime(asset);
171 }
172 catch (Exception e)
173 {
174 m_log.ErrorFormat(
175 "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString()
176 + Environment.NewLine + "Reconnecting", assetID);
177 _dbConnection.Reconnect();
178 } 144 }
179 } 145 }
180 return asset; 146 return asset;
@@ -187,55 +153,57 @@ namespace OpenSim.Data.MySQL
187 /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks> 153 /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
188 override public void StoreAsset(AssetBase asset) 154 override public void StoreAsset(AssetBase asset)
189 { 155 {
190 lock (_dbConnection) 156 lock (m_dbLock)
191 { 157 {
192 _dbConnection.CheckConnection(); 158 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
159 {
160 dbcon.Open();
193 161
194 MySqlCommand cmd = 162 MySqlCommand cmd =
195 new MySqlCommand( 163 new MySqlCommand(
196 "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, data)" + 164 "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, data)" +
197 "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?data)", 165 "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?data)",
198 _dbConnection.Connection); 166 dbcon);
199 167
200 string assetName = asset.Name; 168 string assetName = asset.Name;
201 if (asset.Name.Length > 64) 169 if (asset.Name.Length > 64)
202 {
203 assetName = asset.Name.Substring(0, 64);
204 m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add");
205 }
206
207 string assetDescription = asset.Description;
208 if (asset.Description.Length > 64)
209 {
210 assetDescription = asset.Description.Substring(0, 64);
211 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add");
212 }
213
214 // need to ensure we dispose
215 try
216 {
217 using (cmd)
218 { 170 {
219 // create unix epoch time 171 assetName = asset.Name.Substring(0, 64);
220 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); 172 m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add");
221 cmd.Parameters.AddWithValue("?id", asset.ID); 173 }
222 cmd.Parameters.AddWithValue("?name", assetName); 174
223 cmd.Parameters.AddWithValue("?description", assetDescription); 175 string assetDescription = asset.Description;
224 cmd.Parameters.AddWithValue("?assetType", asset.Type); 176 if (asset.Description.Length > 64)
225 cmd.Parameters.AddWithValue("?local", asset.Local); 177 {
226 cmd.Parameters.AddWithValue("?temporary", asset.Temporary); 178 assetDescription = asset.Description.Substring(0, 64);
227 cmd.Parameters.AddWithValue("?create_time", now); 179 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add");
228 cmd.Parameters.AddWithValue("?access_time", now); 180 }
229 cmd.Parameters.AddWithValue("?data", asset.Data); 181
230 cmd.ExecuteNonQuery(); 182 // need to ensure we dispose
231 cmd.Dispose(); 183 try
184 {
185 using (cmd)
186 {
187 // create unix epoch time
188 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
189 cmd.Parameters.AddWithValue("?id", asset.ID);
190 cmd.Parameters.AddWithValue("?name", assetName);
191 cmd.Parameters.AddWithValue("?description", assetDescription);
192 cmd.Parameters.AddWithValue("?assetType", asset.Type);
193 cmd.Parameters.AddWithValue("?local", asset.Local);
194 cmd.Parameters.AddWithValue("?temporary", asset.Temporary);
195 cmd.Parameters.AddWithValue("?create_time", now);
196 cmd.Parameters.AddWithValue("?access_time", now);
197 cmd.Parameters.AddWithValue("?data", asset.Data);
198 cmd.ExecuteNonQuery();
199 cmd.Dispose();
200 }
201 }
202 catch (Exception e)
203 {
204 m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Error: {2}",
205 asset.FullID, asset.Name, e.Message);
232 } 206 }
233 }
234 catch (Exception e)
235 {
236 m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Attempting reconnect. Error: {2}",
237 asset.FullID, asset.Name, e.Message);
238 _dbConnection.Reconnect();
239 } 207 }
240 } 208 }
241 } 209 }
@@ -245,35 +213,36 @@ namespace OpenSim.Data.MySQL
245 // Writing to the database every time Get() is called on an asset is killing us. Seriously. -jph 213 // Writing to the database every time Get() is called on an asset is killing us. Seriously. -jph
246 return; 214 return;
247 215
248 lock (_dbConnection) 216 lock (m_dbLock)
249 { 217 {
250 _dbConnection.CheckConnection(); 218 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
251
252 MySqlCommand cmd =
253 new MySqlCommand("update assets set access_time=?access_time where id=?id",
254 _dbConnection.Connection);
255
256 // need to ensure we dispose
257 try
258 { 219 {
259 using (cmd) 220 dbcon.Open();
221 MySqlCommand cmd =
222 new MySqlCommand("update assets set access_time=?access_time where id=?id",
223 dbcon);
224
225 // need to ensure we dispose
226 try
227 {
228 using (cmd)
229 {
230 // create unix epoch time
231 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
232 cmd.Parameters.AddWithValue("?id", asset.ID);
233 cmd.Parameters.AddWithValue("?access_time", now);
234 cmd.ExecuteNonQuery();
235 cmd.Dispose();
236 }
237 }
238 catch (Exception e)
260 { 239 {
261 // create unix epoch time 240 m_log.ErrorFormat(
262 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); 241 "[ASSETS DB]: " +
263 cmd.Parameters.AddWithValue("?id", asset.ID); 242 "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString()
264 cmd.Parameters.AddWithValue("?access_time", now); 243 + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name);
265 cmd.ExecuteNonQuery();
266 cmd.Dispose();
267 } 244 }
268 } 245 }
269 catch (Exception e)
270 {
271 m_log.ErrorFormat(
272 "[ASSETS DB]: " +
273 "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString()
274 + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name);
275 _dbConnection.Reconnect();
276 }
277 } 246 }
278 247
279 } 248 }
@@ -287,37 +256,30 @@ namespace OpenSim.Data.MySQL
287 { 256 {
288 bool assetExists = false; 257 bool assetExists = false;
289 258
290 lock (_dbConnection) 259 lock (m_dbLock)
291 { 260 {
292 _dbConnection.CheckConnection(); 261 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
293
294 MySqlCommand cmd =
295 new MySqlCommand(
296 "SELECT id FROM assets WHERE id=?id",
297 _dbConnection.Connection);
298
299 cmd.Parameters.AddWithValue("?id", uuid.ToString());
300
301 try
302 { 262 {
303 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 263 dbcon.Open();
264 using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM assets WHERE id=?id", dbcon))
304 { 265 {
305 if (dbReader.Read()) 266 cmd.Parameters.AddWithValue("?id", uuid.ToString());
267
268 try
306 { 269 {
307 assetExists = true; 270 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
271 {
272 if (dbReader.Read())
273 assetExists = true;
274 }
275 }
276 catch (Exception e)
277 {
278 m_log.ErrorFormat(
279 "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid);
308 } 280 }
309
310 dbReader.Close();
311 cmd.Dispose();
312 } 281 }
313 } 282 }
314 catch (Exception e)
315 {
316 m_log.ErrorFormat(
317 "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString()
318 + Environment.NewLine + "Attempting reconnection", uuid);
319 _dbConnection.Reconnect();
320 }
321 } 283 }
322 284
323 return assetExists; 285 return assetExists;
@@ -335,38 +297,39 @@ namespace OpenSim.Data.MySQL
335 { 297 {
336 List<AssetMetadata> retList = new List<AssetMetadata>(count); 298 List<AssetMetadata> retList = new List<AssetMetadata>(count);
337 299
338 lock (_dbConnection) 300 lock (m_dbLock)
339 { 301 {
340 _dbConnection.CheckConnection(); 302 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
341
342 MySqlCommand cmd = new MySqlCommand("SELECT name,description,assetType,temporary,id FROM assets LIMIT ?start, ?count", _dbConnection.Connection);
343 cmd.Parameters.AddWithValue("?start", start);
344 cmd.Parameters.AddWithValue("?count", count);
345
346 try
347 { 303 {
348 using (MySqlDataReader dbReader = cmd.ExecuteReader()) 304 dbcon.Open();
305 MySqlCommand cmd = new MySqlCommand("SELECT name,description,assetType,temporary,id FROM assets LIMIT ?start, ?count", dbcon);
306 cmd.Parameters.AddWithValue("?start", start);
307 cmd.Parameters.AddWithValue("?count", count);
308
309 try
349 { 310 {
350 while (dbReader.Read()) 311 using (MySqlDataReader dbReader = cmd.ExecuteReader())
351 { 312 {
352 AssetMetadata metadata = new AssetMetadata(); 313 while (dbReader.Read())
353 metadata.Name = (string) dbReader["name"]; 314 {
354 metadata.Description = (string) dbReader["description"]; 315 AssetMetadata metadata = new AssetMetadata();
355 metadata.Type = (sbyte) dbReader["assetType"]; 316 metadata.Name = (string)dbReader["name"];
356 metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct. 317 metadata.Description = (string)dbReader["description"];
357 metadata.FullID = new UUID((string) dbReader["id"]); 318 metadata.Type = (sbyte)dbReader["assetType"];
358 319 metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct.
359 // Current SHA1s are not stored/computed. 320 metadata.FullID = new UUID((string)dbReader["id"]);
360 metadata.SHA1 = new byte[] {}; 321
361 322 // Current SHA1s are not stored/computed.
362 retList.Add(metadata); 323 metadata.SHA1 = new byte[] { };
324
325 retList.Add(metadata);
326 }
363 } 327 }
364 } 328 }
365 } 329 catch (Exception e)
366 catch (Exception e) 330 {
367 { 331 m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString());
368 m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString() + Environment.NewLine + "Attempting reconnection"); 332 }
369 _dbConnection.Reconnect();
370 } 333 }
371 } 334 }
372 335
@@ -374,7 +337,5 @@ namespace OpenSim.Data.MySQL
374 } 337 }
375 338
376 #endregion 339 #endregion
377
378
379 } 340 }
380} 341}
diff --git a/OpenSim/Data/MySQL/MySQLAuthenticationData.cs b/OpenSim/Data/MySQL/MySQLAuthenticationData.cs
index e508b52..5056aee 100644
--- a/OpenSim/Data/MySQL/MySQLAuthenticationData.cs
+++ b/OpenSim/Data/MySQL/MySQLAuthenticationData.cs
@@ -38,16 +38,22 @@ namespace OpenSim.Data.MySQL
38 public class MySqlAuthenticationData : MySqlFramework, IAuthenticationData 38 public class MySqlAuthenticationData : MySqlFramework, IAuthenticationData
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 int m_LastExpire;
43 // private string m_connectionString;
43 44
44 public MySqlAuthenticationData(string connectionString, string realm) 45 public MySqlAuthenticationData(string connectionString, string realm)
45 : base(connectionString) 46 : base(connectionString)
46 { 47 {
47 m_Realm = realm; 48 m_Realm = realm;
49 m_connectionString = connectionString;
48 50
49 Migration m = new Migration(m_Connection, GetType().Assembly, "AuthStore"); 51 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
50 m.Update(); 52 {
53 dbcon.Open();
54 Migration m = new Migration(dbcon, GetType().Assembly, "AuthStore");
55 m.Update();
56 }
51 } 57 }
52 58
53 public AuthenticationData Get(UUID principalID) 59 public AuthenticationData Get(UUID principalID)
@@ -55,45 +61,42 @@ namespace OpenSim.Data.MySQL
55 AuthenticationData ret = new AuthenticationData(); 61 AuthenticationData ret = new AuthenticationData();
56 ret.Data = new Dictionary<string, object>(); 62 ret.Data = new Dictionary<string, object>();
57 63
58 MySqlCommand cmd = new MySqlCommand( 64 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
59 "select * from `"+m_Realm+"` where UUID = ?principalID"
60 );
61
62 cmd.Parameters.AddWithValue("?principalID", principalID.ToString());
63
64 IDataReader result = ExecuteReader(cmd);
65
66 if (result.Read())
67 { 65 {
68 ret.PrincipalID = principalID; 66 dbcon.Open();
67 MySqlCommand cmd = new MySqlCommand("select * from `" + m_Realm + "` where UUID = ?principalID", dbcon);
68 cmd.Parameters.AddWithValue("?principalID", principalID.ToString());
69 69
70 if (m_ColumnNames == null) 70 IDataReader result = cmd.ExecuteReader();
71 {
72 m_ColumnNames = new List<string>();
73 71
74 DataTable schemaTable = result.GetSchemaTable(); 72 if (result.Read())
75 foreach (DataRow row in schemaTable.Rows)
76 m_ColumnNames.Add(row["ColumnName"].ToString());
77 }
78
79 foreach (string s in m_ColumnNames)
80 { 73 {
81 if (s == "UUID") 74 ret.PrincipalID = principalID;
82 continue;
83 75
84 ret.Data[s] = result[s].ToString(); 76 if (m_ColumnNames == null)
85 } 77 {
78 m_ColumnNames = new List<string>();
86 79
87 result.Close(); 80 DataTable schemaTable = result.GetSchemaTable();
88 CloseReaderCommand(cmd); 81 foreach (DataRow row in schemaTable.Rows)
82 m_ColumnNames.Add(row["ColumnName"].ToString());
83 }
89 84
90 return ret; 85 foreach (string s in m_ColumnNames)
91 } 86 {
87 if (s == "UUID")
88 continue;
92 89
93 result.Close(); 90 ret.Data[s] = result[s].ToString();
94 CloseReaderCommand(cmd); 91 }
95 92
96 return null; 93 return ret;
94 }
95 else
96 {
97 return null;
98 }
99 }
97 } 100 }
98 101
99 public bool Store(AuthenticationData data) 102 public bool Store(AuthenticationData data)
diff --git a/OpenSim/Data/MySQL/MySQLEstateData.cs b/OpenSim/Data/MySQL/MySQLEstateData.cs
index e8694fc..2eae2d8 100644
--- a/OpenSim/Data/MySQL/MySQLEstateData.cs
+++ b/OpenSim/Data/MySQL/MySQLEstateData.cs
@@ -44,7 +44,6 @@ namespace OpenSim.Data.MySQL
44 44
45 private const string m_waitTimeoutSelect = "select @@wait_timeout"; 45 private const string m_waitTimeoutSelect = "select @@wait_timeout";
46 46
47 private MySqlConnection m_connection;
48 private string m_connectionString; 47 private string m_connectionString;
49 private long m_waitTimeout; 48 private long m_waitTimeout;
50 private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond; 49 private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond;
@@ -67,24 +66,26 @@ namespace OpenSim.Data.MySQL
67 m_log.Debug("Exception: password not found in connection string\n" + e.ToString()); 66 m_log.Debug("Exception: password not found in connection string\n" + e.ToString());
68 } 67 }
69 68
70 m_connection = new MySqlConnection(m_connectionString);
71 m_connection.Open();
72
73 GetWaitTimeout(); 69 GetWaitTimeout();
74 70
75 Assembly assem = GetType().Assembly; 71 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
76 Migration m = new Migration(m_connection, assem, "EstateStore"); 72 {
77 m.Update(); 73 dbcon.Open();
74
75 Assembly assem = GetType().Assembly;
76 Migration m = new Migration(dbcon, assem, "EstateStore");
77 m.Update();
78 78
79 Type t = typeof(EstateSettings); 79 Type t = typeof(EstateSettings);
80 m_Fields = t.GetFields(BindingFlags.NonPublic | 80 m_Fields = t.GetFields(BindingFlags.NonPublic |
81 BindingFlags.Instance | 81 BindingFlags.Instance |
82 BindingFlags.DeclaredOnly); 82 BindingFlags.DeclaredOnly);
83 83
84 foreach (FieldInfo f in m_Fields) 84 foreach (FieldInfo f in m_Fields)
85 { 85 {
86 if (f.Name.Substring(0, 2) == "m_") 86 if (f.Name.Substring(0, 2) == "m_")
87 m_FieldMap[f.Name.Substring(2)] = f; 87 m_FieldMap[f.Name.Substring(2)] = f;
88 }
88 } 89 }
89 } 90 }
90 91
@@ -95,47 +96,29 @@ namespace OpenSim.Data.MySQL
95 96
96 protected void GetWaitTimeout() 97 protected void GetWaitTimeout()
97 { 98 {
98 MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, 99 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
99 m_connection);
100
101 using (MySqlDataReader dbReader =
102 cmd.ExecuteReader(CommandBehavior.SingleRow))
103 { 100 {
104 if (dbReader.Read()) 101 dbcon.Open();
102
103 using (MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon))
105 { 104 {
106 m_waitTimeout 105 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
107 = Convert.ToInt32(dbReader["@@wait_timeout"]) * 106 {
108 TimeSpan.TicksPerSecond + m_waitTimeoutLeeway; 107 if (dbReader.Read())
108 {
109 m_waitTimeout
110 = Convert.ToInt32(dbReader["@@wait_timeout"]) *
111 TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
112 }
113 }
109 } 114 }
110 115
111 dbReader.Close(); 116 m_lastConnectionUse = DateTime.Now.Ticks;
112 cmd.Dispose();
113 }
114
115 m_lastConnectionUse = DateTime.Now.Ticks;
116
117 m_log.DebugFormat(
118 "[REGION DB]: Connection wait timeout {0} seconds",
119 m_waitTimeout / TimeSpan.TicksPerSecond);
120 }
121
122 protected void CheckConnection()
123 {
124 long timeNow = DateTime.Now.Ticks;
125 if (timeNow - m_lastConnectionUse > m_waitTimeout ||
126 m_connection.State != ConnectionState.Open)
127 {
128 m_log.DebugFormat("[REGION DB]: Database connection has gone away - reconnecting");
129 117
130 lock (m_connection) 118 m_log.DebugFormat(
131 { 119 "[REGION DB]: Connection wait timeout {0} seconds",
132 m_connection.Close(); 120 m_waitTimeout / TimeSpan.TicksPerSecond);
133 m_connection = new MySqlConnection(m_connectionString);
134 m_connection.Open();
135 }
136 } 121 }
137
138 m_lastConnectionUse = timeNow;
139 } 122 }
140 123
141 public EstateSettings LoadEstateSettings(UUID regionID) 124 public EstateSettings LoadEstateSettings(UUID regionID)
@@ -143,114 +126,111 @@ namespace OpenSim.Data.MySQL
143 EstateSettings es = new EstateSettings(); 126 EstateSettings es = new EstateSettings();
144 es.OnSave += StoreEstateSettings; 127 es.OnSave += StoreEstateSettings;
145 128
146 string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = ?RegionID"; 129 string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) +
130 " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = ?RegionID";
147 131
148 CheckConnection(); 132 bool migration = true;
149 133
150 MySqlCommand cmd = m_connection.CreateCommand(); 134 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
151
152 cmd.CommandText = sql;
153 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
154
155 IDataReader r = cmd.ExecuteReader();
156
157 if (r.Read())
158 { 135 {
159 foreach (string name in FieldList) 136 dbcon.Open();
137
138 using (MySqlCommand cmd = dbcon.CreateCommand())
160 { 139 {
161 if (m_FieldMap[name].GetValue(es) is bool) 140 cmd.CommandText = sql;
162 { 141 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
163 int v = Convert.ToInt32(r[name]);
164 if (v != 0)
165 m_FieldMap[name].SetValue(es, true);
166 else
167 m_FieldMap[name].SetValue(es, false);
168 }
169 else if (m_FieldMap[name].GetValue(es) is UUID)
170 {
171 UUID uuid = UUID.Zero;
172 142
173 UUID.TryParse(r[name].ToString(), out uuid); 143 using (IDataReader r = cmd.ExecuteReader())
174 m_FieldMap[name].SetValue(es, uuid);
175 }
176 else
177 { 144 {
178 m_FieldMap[name].SetValue(es, r[name]); 145 if (r.Read())
146 {
147 migration = false;
148
149 foreach (string name in FieldList)
150 {
151 if (m_FieldMap[name].GetValue(es) is bool)
152 {
153 int v = Convert.ToInt32(r[name]);
154 if (v != 0)
155 m_FieldMap[name].SetValue(es, true);
156 else
157 m_FieldMap[name].SetValue(es, false);
158 }
159 else if (m_FieldMap[name].GetValue(es) is UUID)
160 {
161 UUID uuid = UUID.Zero;
162
163 UUID.TryParse(r[name].ToString(), out uuid);
164 m_FieldMap[name].SetValue(es, uuid);
165 }
166 else
167 {
168 m_FieldMap[name].SetValue(es, r[name]);
169 }
170 }
171 }
179 } 172 }
180 } 173 }
181 r.Close();
182 }
183 else
184 {
185 // Migration case
186 //
187 r.Close();
188
189 List<string> names = new List<string>(FieldList);
190 174
191 names.Remove("EstateID"); 175 if (migration)
176 {
177 // Migration case
178 List<string> names = new List<string>(FieldList);
192 179
193 sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")"; 180 names.Remove("EstateID");
194 181
195 cmd.CommandText = sql; 182 sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")";
196 cmd.Parameters.Clear();
197 183
198 foreach (string name in FieldList) 184 using (MySqlCommand cmd = dbcon.CreateCommand())
199 {
200 if (m_FieldMap[name].GetValue(es) is bool)
201 {
202 if ((bool)m_FieldMap[name].GetValue(es))
203 cmd.Parameters.AddWithValue("?" + name, "1");
204 else
205 cmd.Parameters.AddWithValue("?" + name, "0");
206 }
207 else
208 { 185 {
209 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString()); 186 cmd.CommandText = sql;
187 cmd.Parameters.Clear();
188
189 foreach (string name in FieldList)
190 {
191 if (m_FieldMap[name].GetValue(es) is bool)
192 {
193 if ((bool)m_FieldMap[name].GetValue(es))
194 cmd.Parameters.AddWithValue("?" + name, "1");
195 else
196 cmd.Parameters.AddWithValue("?" + name, "0");
197 }
198 else
199 {
200 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
201 }
202 }
203
204 cmd.ExecuteNonQuery();
205
206 cmd.CommandText = "select LAST_INSERT_ID() as id";
207 cmd.Parameters.Clear();
208
209 using (IDataReader r = cmd.ExecuteReader())
210 {
211 r.Read();
212 es.EstateID = Convert.ToUInt32(r["id"]);
213 }
214
215 cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
216 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
217 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
218
219 // This will throw on dupe key
220 try { cmd.ExecuteNonQuery(); }
221 catch (Exception) { }
222
223 // Munge and transfer the ban list
224 cmd.Parameters.Clear();
225 cmd.CommandText = "insert into estateban select " + es.EstateID.ToString() + ", bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = ?UUID";
226 cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
227
228 try { cmd.ExecuteNonQuery(); }
229 catch (Exception) { }
230
231 es.Save();
210 } 232 }
211 } 233 }
212
213 cmd.ExecuteNonQuery();
214
215 cmd.CommandText = "select LAST_INSERT_ID() as id";
216 cmd.Parameters.Clear();
217
218 r = cmd.ExecuteReader();
219
220 r.Read();
221
222 es.EstateID = Convert.ToUInt32(r["id"]);
223
224 r.Close();
225
226 cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
227 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
228 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
229
230 // This will throw on dupe key
231 try
232 {
233 cmd.ExecuteNonQuery();
234 }
235 catch (Exception)
236 {
237 }
238
239 // Munge and transfer the ban list
240 //
241 cmd.Parameters.Clear();
242 cmd.CommandText = "insert into estateban select " + es.EstateID.ToString() + ", bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = ?UUID";
243 cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
244
245 try
246 {
247 cmd.ExecuteNonQuery();
248 }
249 catch (Exception)
250 {
251 }
252
253 es.Save();
254 } 234 }
255 235
256 LoadBanList(es); 236 LoadBanList(es);
@@ -265,29 +245,33 @@ namespace OpenSim.Data.MySQL
265 { 245 {
266 string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")"; 246 string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")";
267 247
268 CheckConnection(); 248 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
249 {
250 dbcon.Open();
269 251
270 MySqlCommand cmd = m_connection.CreateCommand(); 252 using (MySqlCommand cmd = dbcon.CreateCommand())
253 {
254 cmd.CommandText = sql;
271 255
272 cmd.CommandText = sql; 256 foreach (string name in FieldList)
257 {
258 if (m_FieldMap[name].GetValue(es) is bool)
259 {
260 if ((bool)m_FieldMap[name].GetValue(es))
261 cmd.Parameters.AddWithValue("?" + name, "1");
262 else
263 cmd.Parameters.AddWithValue("?" + name, "0");
264 }
265 else
266 {
267 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
268 }
269 }
273 270
274 foreach (string name in FieldList) 271 cmd.ExecuteNonQuery();
275 {
276 if (m_FieldMap[name].GetValue(es) is bool)
277 {
278 if ((bool)m_FieldMap[name].GetValue(es))
279 cmd.Parameters.AddWithValue("?" + name, "1");
280 else
281 cmd.Parameters.AddWithValue("?" + name, "0");
282 }
283 else
284 {
285 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
286 } 272 }
287 } 273 }
288 274
289 cmd.ExecuteNonQuery();
290
291 SaveBanList(es); 275 SaveBanList(es);
292 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers); 276 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
293 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess); 277 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
@@ -298,77 +282,89 @@ namespace OpenSim.Data.MySQL
298 { 282 {
299 es.ClearBans(); 283 es.ClearBans();
300 284
301 CheckConnection(); 285 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
302
303 MySqlCommand cmd = m_connection.CreateCommand();
304
305 cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
306 cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
307
308 IDataReader r = cmd.ExecuteReader();
309
310 while (r.Read())
311 { 286 {
312 EstateBan eb = new EstateBan(); 287 dbcon.Open();
313 288
314 UUID uuid = new UUID(); 289 using (MySqlCommand cmd = dbcon.CreateCommand())
315 UUID.TryParse(r["bannedUUID"].ToString(), out uuid); 290 {
291 cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
292 cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
316 293
317 eb.BannedUserID = uuid; 294 using (IDataReader r = cmd.ExecuteReader())
318 eb.BannedHostAddress = "0.0.0.0"; 295 {
319 eb.BannedHostIPMask = "0.0.0.0"; 296 while (r.Read())
320 es.AddBan(eb); 297 {
298 EstateBan eb = new EstateBan();
299
300 UUID uuid = new UUID();
301 UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
302
303 eb.BannedUserID = uuid;
304 eb.BannedHostAddress = "0.0.0.0";
305 eb.BannedHostIPMask = "0.0.0.0";
306 es.AddBan(eb);
307 }
308 }
309 }
321 } 310 }
322 r.Close();
323 } 311 }
324 312
325 private void SaveBanList(EstateSettings es) 313 private void SaveBanList(EstateSettings es)
326 { 314 {
327 CheckConnection(); 315 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
328 316 {
329 MySqlCommand cmd = m_connection.CreateCommand(); 317 dbcon.Open();
330 318
331 cmd.CommandText = "delete from estateban where EstateID = ?EstateID"; 319 using (MySqlCommand cmd = dbcon.CreateCommand())
332 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString()); 320 {
321 cmd.CommandText = "delete from estateban where EstateID = ?EstateID";
322 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
333 323
334 cmd.ExecuteNonQuery(); 324 cmd.ExecuteNonQuery();
335 325
336 cmd.Parameters.Clear(); 326 cmd.Parameters.Clear();
337 327
338 cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )"; 328 cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )";
339 329
340 foreach (EstateBan b in es.EstateBans) 330 foreach (EstateBan b in es.EstateBans)
341 { 331 {
342 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString()); 332 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
343 cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString()); 333 cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString());
344 334
345 cmd.ExecuteNonQuery(); 335 cmd.ExecuteNonQuery();
346 cmd.Parameters.Clear(); 336 cmd.Parameters.Clear();
337 }
338 }
347 } 339 }
348 } 340 }
349 341
350 void SaveUUIDList(uint EstateID, string table, UUID[] data) 342 void SaveUUIDList(uint EstateID, string table, UUID[] data)
351 { 343 {
352 CheckConnection(); 344 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
353 345 {
354 MySqlCommand cmd = m_connection.CreateCommand(); 346 dbcon.Open();
355 347
356 cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID"; 348 using (MySqlCommand cmd = dbcon.CreateCommand())
357 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString()); 349 {
350 cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID";
351 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
358 352
359 cmd.ExecuteNonQuery(); 353 cmd.ExecuteNonQuery();
360 354
361 cmd.Parameters.Clear(); 355 cmd.Parameters.Clear();
362 356
363 cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )"; 357 cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )";
364 358
365 foreach (UUID uuid in data) 359 foreach (UUID uuid in data)
366 { 360 {
367 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString()); 361 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
368 cmd.Parameters.AddWithValue("?uuid", uuid.ToString()); 362 cmd.Parameters.AddWithValue("?uuid", uuid.ToString());
369 363
370 cmd.ExecuteNonQuery(); 364 cmd.ExecuteNonQuery();
371 cmd.Parameters.Clear(); 365 cmd.Parameters.Clear();
366 }
367 }
372 } 368 }
373 } 369 }
374 370
@@ -376,25 +372,29 @@ namespace OpenSim.Data.MySQL
376 { 372 {
377 List<UUID> uuids = new List<UUID>(); 373 List<UUID> uuids = new List<UUID>();
378 374
379 CheckConnection(); 375 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
380 376 {
381 MySqlCommand cmd = m_connection.CreateCommand(); 377 dbcon.Open();
382
383 cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
384 cmd.Parameters.AddWithValue("?EstateID", EstateID);
385 378
386 IDataReader r = cmd.ExecuteReader(); 379 using (MySqlCommand cmd = dbcon.CreateCommand())
380 {
381 cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
382 cmd.Parameters.AddWithValue("?EstateID", EstateID);
387 383
388 while (r.Read()) 384 using (IDataReader r = cmd.ExecuteReader())
389 { 385 {
390 // EstateBan eb = new EstateBan(); 386 while (r.Read())
387 {
388 // EstateBan eb = new EstateBan();
391 389
392 UUID uuid = new UUID(); 390 UUID uuid = new UUID();
393 UUID.TryParse(r["uuid"].ToString(), out uuid); 391 UUID.TryParse(r["uuid"].ToString(), out uuid);
394 392
395 uuids.Add(uuid); 393 uuids.Add(uuid);
394 }
395 }
396 }
396 } 397 }
397 r.Close();
398 398
399 return uuids.ToArray(); 399 return uuids.ToArray();
400 } 400 }
diff --git a/OpenSim/Data/MySQL/MySQLFramework.cs b/OpenSim/Data/MySQL/MySQLFramework.cs
index fca0ca5..3fdcf1e 100644
--- a/OpenSim/Data/MySQL/MySQLFramework.cs
+++ b/OpenSim/Data/MySQL/MySQLFramework.cs
@@ -40,12 +40,16 @@ namespace OpenSim.Data.MySQL
40 /// </summary> 40 /// </summary>
41 public class MySqlFramework 41 public class MySqlFramework
42 { 42 {
43 protected MySqlConnection m_Connection; 43 private static readonly log4net.ILog m_log =
44 log4net.LogManager.GetLogger(
45 System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
46
47 protected string m_connectionString;
48 protected object m_dbLock = new object();
44 49
45 protected MySqlFramework(string connectionString) 50 protected MySqlFramework(string connectionString)
46 { 51 {
47 m_Connection = new MySqlConnection(connectionString); 52 m_connectionString = connectionString;
48 m_Connection.Open();
49 } 53 }
50 54
51 ////////////////////////////////////////////////////////////// 55 //////////////////////////////////////////////////////////////
@@ -55,64 +59,24 @@ namespace OpenSim.Data.MySQL
55 // 59 //
56 protected int ExecuteNonQuery(MySqlCommand cmd) 60 protected int ExecuteNonQuery(MySqlCommand cmd)
57 { 61 {
58 lock (m_Connection) 62 lock (m_dbLock)
59 { 63 {
60 cmd.Connection = m_Connection; 64 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
61
62 bool errorSeen = false;
63
64 while (true)
65 { 65 {
66 dbcon.Open();
67 cmd.Connection = dbcon;
68
66 try 69 try
67 { 70 {
68 return cmd.ExecuteNonQuery(); 71 return cmd.ExecuteNonQuery();
69 } 72 }
70 catch (MySqlException e)
71 {
72 if (errorSeen)
73 throw;
74
75 // This is "Server has gone away" and "Server lost"
76 //
77 if (e.Number == 2006 || e.Number == 2013)
78 {
79 errorSeen = true;
80
81 m_Connection.Close();
82 MySqlConnection newConnection =
83 (MySqlConnection)((ICloneable)m_Connection).Clone();
84 m_Connection.Dispose();
85 m_Connection = newConnection;
86 m_Connection.Open();
87
88 cmd.Connection = m_Connection;
89 }
90 else
91 throw;
92 }
93 catch (Exception e) 73 catch (Exception e)
94 { 74 {
75 m_log.Error(e.Message, e);
95 return 0; 76 return 0;
96 } 77 }
97 } 78 }
98 } 79 }
99 } 80 }
100
101 protected IDataReader ExecuteReader(MySqlCommand cmd)
102 {
103 MySqlConnection newConnection =
104 (MySqlConnection)((ICloneable)m_Connection).Clone();
105 newConnection.Open();
106
107 cmd.Connection = newConnection;
108 return cmd.ExecuteReader();
109 }
110
111 protected void CloseReaderCommand(MySqlCommand cmd)
112 {
113 cmd.Connection.Close();
114 cmd.Connection.Dispose();
115 cmd.Dispose();
116 }
117 } 81 }
118} 82}
diff --git a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs
index fdb98eb..698bf52 100644
--- a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs
+++ b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs
@@ -54,12 +54,16 @@ namespace OpenSim.Data.MySQL
54 string realm, string storeName) : base(connectionString) 54 string realm, string storeName) : base(connectionString)
55 { 55 {
56 m_Realm = realm; 56 m_Realm = realm;
57 m_connectionString = connectionString;
58
57 if (storeName != String.Empty) 59 if (storeName != String.Empty)
58 { 60 {
59 Assembly assem = GetType().Assembly; 61 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
60 62 {
61 Migration m = new Migration(m_Connection, assem, storeName); 63 dbcon.Open();
62 m.Update(); 64 Migration m = new Migration(dbcon, GetType().Assembly, storeName);
65 m.Update();
66 }
63 } 67 }
64 68
65 Type t = typeof(T); 69 Type t = typeof(T);
@@ -107,147 +111,160 @@ namespace OpenSim.Data.MySQL
107 111
108 List<string> terms = new List<string>(); 112 List<string> terms = new List<string>();
109 113
110 MySqlCommand cmd = new MySqlCommand(); 114 using (MySqlCommand cmd = new MySqlCommand())
111
112 for (int i = 0 ; i < fields.Length ; i++)
113 { 115 {
114 cmd.Parameters.AddWithValue(fields[i], keys[i]); 116 for (int i = 0 ; i < fields.Length ; i++)
115 terms.Add("`" + fields[i] + "` = ?" + fields[i]); 117 {
116 } 118 cmd.Parameters.AddWithValue(fields[i], keys[i]);
117 119 terms.Add("`" + fields[i] + "` = ?" + fields[i]);
118 string where = String.Join(" and ", terms.ToArray()); 120 }
119 121
120 string query = String.Format("select * from {0} where {1}", 122 string where = String.Join(" and ", terms.ToArray());
121 m_Realm, where);
122 123
123 cmd.CommandText = query; 124 string query = String.Format("select * from {0} where {1}",
125 m_Realm, where);
124 126
125 return DoQuery(cmd); 127 cmd.CommandText = query;
128
129 return DoQuery(cmd);
130 }
126 } 131 }
127 132
128 protected T[] DoQuery(MySqlCommand cmd) 133 protected T[] DoQuery(MySqlCommand cmd)
129 { 134 {
130 IDataReader reader = ExecuteReader(cmd);
131 if (reader == null)
132 return new T[0];
133
134 CheckColumnNames(reader);
135
136 List<T> result = new List<T>(); 135 List<T> result = new List<T>();
137 136
138 while (reader.Read()) 137 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
139 { 138 {
140 T row = new T(); 139 dbcon.Open();
140 cmd.Connection = dbcon;
141 141
142 foreach (string name in m_Fields.Keys) 142 using (IDataReader reader = cmd.ExecuteReader())
143 { 143 {
144 if (m_Fields[name].GetValue(row) is bool) 144 if (reader == null)
145 { 145 return new T[0];
146 int v = Convert.ToInt32(reader[name]);
147 m_Fields[name].SetValue(row, v != 0 ? true : false);
148 }
149 else if (m_Fields[name].GetValue(row) is UUID)
150 {
151 UUID uuid = UUID.Zero;
152 146
153 UUID.TryParse(reader[name].ToString(), out uuid); 147 CheckColumnNames(reader);
154 m_Fields[name].SetValue(row, uuid);
155 }
156 else if (m_Fields[name].GetValue(row) is int)
157 {
158 int v = Convert.ToInt32(reader[name]);
159 m_Fields[name].SetValue(row, v);
160 }
161 else
162 {
163 m_Fields[name].SetValue(row, reader[name]);
164 }
165 }
166
167 if (m_DataField != null)
168 {
169 Dictionary<string, string> data =
170 new Dictionary<string, string>();
171 148
172 foreach (string col in m_ColumnNames) 149 while (reader.Read())
173 { 150 {
174 data[col] = reader[col].ToString(); 151 T row = new T();
175 if (data[col] == null) 152
176 data[col] = String.Empty; 153 foreach (string name in m_Fields.Keys)
154 {
155 if (m_Fields[name].GetValue(row) is bool)
156 {
157 int v = Convert.ToInt32(reader[name]);
158 m_Fields[name].SetValue(row, v != 0 ? true : false);
159 }
160 else if (m_Fields[name].GetValue(row) is UUID)
161 {
162 UUID uuid = UUID.Zero;
163
164 UUID.TryParse(reader[name].ToString(), out uuid);
165 m_Fields[name].SetValue(row, uuid);
166 }
167 else if (m_Fields[name].GetValue(row) is int)
168 {
169 int v = Convert.ToInt32(reader[name]);
170 m_Fields[name].SetValue(row, v);
171 }
172 else
173 {
174 m_Fields[name].SetValue(row, reader[name]);
175 }
176 }
177
178 if (m_DataField != null)
179 {
180 Dictionary<string, string> data =
181 new Dictionary<string, string>();
182
183 foreach (string col in m_ColumnNames)
184 {
185 data[col] = reader[col].ToString();
186 if (data[col] == null)
187 data[col] = String.Empty;
188 }
189
190 m_DataField.SetValue(row, data);
191 }
192
193 result.Add(row);
177 } 194 }
178
179 m_DataField.SetValue(row, data);
180 } 195 }
181
182 result.Add(row);
183 } 196 }
184 197
185 CloseReaderCommand(cmd);
186
187 return result.ToArray(); 198 return result.ToArray();
188 } 199 }
189 200
190 public T[] Get(string where) 201 public T[] Get(string where)
191 { 202 {
192 MySqlCommand cmd = new MySqlCommand(); 203 using (MySqlCommand cmd = new MySqlCommand())
193 204 {
194 string query = String.Format("select * from {0} where {1}", 205
195 m_Realm, where); 206 string query = String.Format("select * from {0} where {1}",
196 207 m_Realm, where);
197 cmd.CommandText = query; 208
198 209 cmd.CommandText = query;
199 return DoQuery(cmd); 210
211 return DoQuery(cmd);
212 }
200 } 213 }
201 214
202 public bool Store(T row) 215 public bool Store(T row)
203 { 216 {
204 MySqlCommand cmd = new MySqlCommand(); 217 using (MySqlCommand cmd = new MySqlCommand())
218 {
205 219
206 string query = ""; 220 string query = "";
207 List<String> names = new List<String>(); 221 List<String> names = new List<String>();
208 List<String> values = new List<String>(); 222 List<String> values = new List<String>();
209 223
210 foreach (FieldInfo fi in m_Fields.Values) 224 foreach (FieldInfo fi in m_Fields.Values)
211 { 225 {
212 names.Add(fi.Name); 226 names.Add(fi.Name);
213 values.Add("?" + fi.Name); 227 values.Add("?" + fi.Name);
214 cmd.Parameters.AddWithValue(fi.Name, fi.GetValue(row).ToString()); 228 cmd.Parameters.AddWithValue(fi.Name, fi.GetValue(row).ToString());
215 } 229 }
216 230
217 if (m_DataField != null) 231 if (m_DataField != null)
218 { 232 {
219 Dictionary<string, string> data = 233 Dictionary<string, string> data =
220 (Dictionary<string, string>)m_DataField.GetValue(row); 234 (Dictionary<string, string>)m_DataField.GetValue(row);
221 235
222 foreach (KeyValuePair<string, string> kvp in data) 236 foreach (KeyValuePair<string, string> kvp in data)
223 { 237 {
224 names.Add(kvp.Key); 238 names.Add(kvp.Key);
225 values.Add("?" + kvp.Key); 239 values.Add("?" + kvp.Key);
226 cmd.Parameters.AddWithValue("?" + kvp.Key, kvp.Value); 240 cmd.Parameters.AddWithValue("?" + kvp.Key, kvp.Value);
241 }
227 } 242 }
228 }
229 243
230 query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")"; 244 query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")";
231 245
232 cmd.CommandText = query; 246 cmd.CommandText = query;
233 247
234 if (ExecuteNonQuery(cmd) > 0) 248 if (ExecuteNonQuery(cmd) > 0)
235 return true; 249 return true;
236 250
237 return false; 251 return false;
252 }
238 } 253 }
239 254
240 public bool Delete(string field, string val) 255 public bool Delete(string field, string val)
241 { 256 {
242 MySqlCommand cmd = new MySqlCommand(); 257 using (MySqlCommand cmd = new MySqlCommand())
258 {
243 259
244 cmd.CommandText = String.Format("delete from {0} where `{1}` = ?{1}", m_Realm, field); 260 cmd.CommandText = String.Format("delete from {0} where `{1}` = ?{1}", m_Realm, field);
245 cmd.Parameters.AddWithValue(field, val); 261 cmd.Parameters.AddWithValue(field, val);
246 262
247 if (ExecuteNonQuery(cmd) > 0) 263 if (ExecuteNonQuery(cmd) > 0)
248 return true; 264 return true;
249 265
250 return false; 266 return false;
267 }
251 } 268 }
252 } 269 }
253} 270}
diff --git a/OpenSim/Data/MySQL/MySQLGridData.cs b/OpenSim/Data/MySQL/MySQLGridData.cs
index 1ec2609..f4e7b85 100644
--- a/OpenSim/Data/MySQL/MySQLGridData.cs
+++ b/OpenSim/Data/MySQL/MySQLGridData.cs
@@ -31,6 +31,7 @@ using System.Data;
31using System.Reflection; 31using System.Reflection;
32using System.Threading; 32using System.Threading;
33using log4net; 33using log4net;
34using MySql.Data.MySqlClient;
34using OpenMetaverse; 35using OpenMetaverse;
35using OpenSim.Framework; 36using OpenSim.Framework;
36 37
@@ -43,49 +44,9 @@ namespace OpenSim.Data.MySQL
43 { 44 {
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 45 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45 46
46 /// <summary> 47 private MySQLManager m_database;
47 /// MySQL Database Manager 48 private object m_dbLock = new object();
48 /// </summary> 49 private string m_connectionString;
49 private MySQLManager database;
50
51
52 /// <summary>
53 /// Better DB manager. Swap-in replacement too.
54 /// </summary>
55 public Dictionary<int, MySQLSuperManager> m_dbconnections = new Dictionary<int, MySQLSuperManager>();
56
57 public int m_maxConnections = 10;
58 public int m_lastConnect;
59
60 public MySQLSuperManager GetLockedConnection()
61 {
62 int lockedCons = 0;
63 while (true)
64 {
65 m_lastConnect++;
66
67 // Overflow protection
68 if (m_lastConnect == int.MaxValue)
69 m_lastConnect = 0;
70
71 MySQLSuperManager x = m_dbconnections[m_lastConnect % m_maxConnections];
72 if (!x.Locked)
73 {
74 x.GetLock();
75 return x;
76 }
77
78 lockedCons++;
79 if (lockedCons > m_maxConnections)
80 {
81 lockedCons = 0;
82 Thread.Sleep(1000); // Wait some time before searching them again.
83 m_log.Debug(
84 "WARNING: All threads are in use. Probable cause: Something didnt release a mutex properly, or high volume of requests inbound.");
85 }
86 }
87 }
88
89 50
90 override public void Initialise() 51 override public void Initialise()
91 { 52 {
@@ -106,49 +67,17 @@ namespace OpenSim.Data.MySQL
106 /// <param name="connect">connect string.</param> 67 /// <param name="connect">connect string.</param>
107 override public void Initialise(string connect) 68 override public void Initialise(string connect)
108 { 69 {
109 if (connect != String.Empty) 70 m_connectionString = connect;
110 { 71 m_database = new MySQLManager(connect);
111 database = new MySQLManager(connect);
112 72
113 m_log.Info("Creating " + m_maxConnections + " DB connections..."); 73 // This actually does the roll forward assembly stuff
114 for (int i = 0; i < m_maxConnections; i++) 74 Assembly assem = GetType().Assembly;
115 {
116 m_log.Info("Connecting to DB... [" + i + "]");
117 MySQLSuperManager msm = new MySQLSuperManager();
118 msm.Manager = new MySQLManager(connect);
119 m_dbconnections.Add(i, msm);
120 }
121 75
122 } 76 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
123 else
124 { 77 {
125 m_log.Warn("Using deprecated mysql_connection.ini. Please update database_connect in GridServer_Config.xml and we'll use that instead"); 78 Migration m = new Migration(dbcon, assem, "GridStore");
126 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); 79 m.Update();
127 string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
128 string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
129 string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
130 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
131 string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
132 string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
133
134 database = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
135 settingPooling, settingPort);
136
137 m_log.Info("Creating " + m_maxConnections + " DB connections...");
138 for (int i = 0; i < m_maxConnections; i++)
139 {
140 m_log.Info("Connecting to DB... [" + i + "]");
141 MySQLSuperManager msm = new MySQLSuperManager();
142 msm.Manager = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
143 settingPooling, settingPort);
144 m_dbconnections.Add(i, msm);
145 }
146 } 80 }
147
148 // This actually does the roll forward assembly stuff
149 Assembly assem = GetType().Assembly;
150 Migration m = new Migration(database.Connection, assem, "GridStore");
151 m.Update();
152 } 81 }
153 82
154 /// <summary> 83 /// <summary>
@@ -156,7 +85,6 @@ namespace OpenSim.Data.MySQL
156 /// </summary> 85 /// </summary>
157 override public void Dispose() 86 override public void Dispose()
158 { 87 {
159 database.Close();
160 } 88 }
161 89
162 /// <summary> 90 /// <summary>
@@ -187,8 +115,6 @@ namespace OpenSim.Data.MySQL
187 /// <returns>Array of sim profiles</returns> 115 /// <returns>Array of sim profiles</returns>
188 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax) 116 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
189 { 117 {
190 MySQLSuperManager dbm = GetLockedConnection();
191
192 try 118 try
193 { 119 {
194 Dictionary<string, object> param = new Dictionary<string, object>(); 120 Dictionary<string, object> param = new Dictionary<string, object>();
@@ -197,35 +123,33 @@ namespace OpenSim.Data.MySQL
197 param["?xmax"] = xmax.ToString(); 123 param["?xmax"] = xmax.ToString();
198 param["?ymax"] = ymax.ToString(); 124 param["?ymax"] = ymax.ToString();
199 125
200 IDbCommand result = 126 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
201 dbm.Manager.Query( 127 {
202 "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax", 128 dbcon.Open();
203 param); 129
204 IDataReader reader = result.ExecuteReader(); 130 using (IDbCommand result = m_database.Query(dbcon,
131 "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax",
132 param))
133 {
134 using (IDataReader reader = result.ExecuteReader())
135 {
136 RegionProfileData row;
205 137
206 RegionProfileData row; 138 List<RegionProfileData> rows = new List<RegionProfileData>();
207 139
208 List<RegionProfileData> rows = new List<RegionProfileData>(); 140 while ((row = m_database.readSimRow(reader)) != null)
141 rows.Add(row);
209 142
210 while ((row = dbm.Manager.readSimRow(reader)) != null) 143 return rows.ToArray();
211 { 144 }
212 rows.Add(row); 145 }
213 } 146 }
214 reader.Close();
215 result.Dispose();
216
217 return rows.ToArray();
218 } 147 }
219 catch (Exception e) 148 catch (Exception e)
220 { 149 {
221 dbm.Manager.Reconnect(); 150 m_log.Error(e.Message, e);
222 m_log.Error(e.ToString());
223 return null; 151 return null;
224 } 152 }
225 finally
226 {
227 dbm.Release();
228 }
229 } 153 }
230 154
231 /// <summary> 155 /// <summary>
@@ -236,42 +160,38 @@ namespace OpenSim.Data.MySQL
236 /// <returns>A list of sim profiles</returns> 160 /// <returns>A list of sim profiles</returns>
237 override public List<RegionProfileData> GetRegionsByName(string namePrefix, uint maxNum) 161 override public List<RegionProfileData> GetRegionsByName(string namePrefix, uint maxNum)
238 { 162 {
239 MySQLSuperManager dbm = GetLockedConnection();
240
241 try 163 try
242 { 164 {
243 Dictionary<string, object> param = new Dictionary<string, object>(); 165 Dictionary<string, object> param = new Dictionary<string, object>();
244 param["?name"] = namePrefix + "%"; 166 param["?name"] = namePrefix + "%";
245 167
246 IDbCommand result = 168 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
247 dbm.Manager.Query( 169 {
170 dbcon.Open();
171
172 using (IDbCommand result = m_database.Query(dbcon,
248 "SELECT * FROM regions WHERE regionName LIKE ?name", 173 "SELECT * FROM regions WHERE regionName LIKE ?name",
249 param); 174 param))
250 IDataReader reader = result.ExecuteReader(); 175 {
176 using (IDataReader reader = result.ExecuteReader())
177 {
178 RegionProfileData row;
251 179
252 RegionProfileData row; 180 List<RegionProfileData> rows = new List<RegionProfileData>();
253 181
254 List<RegionProfileData> rows = new List<RegionProfileData>(); 182 while (rows.Count < maxNum && (row = m_database.readSimRow(reader)) != null)
183 rows.Add(row);
255 184
256 while (rows.Count < maxNum && (row = dbm.Manager.readSimRow(reader)) != null) 185 return rows;
257 { 186 }
258 rows.Add(row); 187 }
259 } 188 }
260 reader.Close();
261 result.Dispose();
262
263 return rows;
264 } 189 }
265 catch (Exception e) 190 catch (Exception e)
266 { 191 {
267 dbm.Manager.Reconnect(); 192 m_log.Error(e.Message, e);
268 m_log.Error(e.ToString());
269 return null; 193 return null;
270 } 194 }
271 finally
272 {
273 dbm.Release();
274 }
275 } 195 }
276 196
277 /// <summary> 197 /// <summary>
@@ -281,32 +201,30 @@ namespace OpenSim.Data.MySQL
281 /// <returns>Sim profile</returns> 201 /// <returns>Sim profile</returns>
282 override public RegionProfileData GetProfileByHandle(ulong handle) 202 override public RegionProfileData GetProfileByHandle(ulong handle)
283 { 203 {
284 MySQLSuperManager dbm = GetLockedConnection();
285
286 try 204 try
287 { 205 {
288 Dictionary<string, object> param = new Dictionary<string, object>(); 206 Dictionary<string, object> param = new Dictionary<string, object>();
289 param["?handle"] = handle.ToString(); 207 param["?handle"] = handle.ToString();
290
291 IDbCommand result = dbm.Manager.Query("SELECT * FROM regions WHERE regionHandle = ?handle", param);
292 IDataReader reader = result.ExecuteReader();
293 208
294 RegionProfileData row = dbm.Manager.readSimRow(reader); 209 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
295 reader.Close(); 210 {
296 result.Dispose(); 211 dbcon.Open();
297 212
298 return row; 213 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM regions WHERE regionHandle = ?handle", param))
214 {
215 using (IDataReader reader = result.ExecuteReader())
216 {
217 RegionProfileData row = m_database.readSimRow(reader);
218 return row;
219 }
220 }
299 } 221 }
222 }
300 catch (Exception e) 223 catch (Exception e)
301 { 224 {
302 dbm.Manager.Reconnect(); 225 m_log.Error(e.Message, e);
303 m_log.Error(e.ToString());
304 return null; 226 return null;
305 } 227 }
306 finally
307 {
308 dbm.Release();
309 }
310 } 228 }
311 229
312 /// <summary> 230 /// <summary>
@@ -316,30 +234,29 @@ namespace OpenSim.Data.MySQL
316 /// <returns>The sim profile</returns> 234 /// <returns>The sim profile</returns>
317 override public RegionProfileData GetProfileByUUID(UUID uuid) 235 override public RegionProfileData GetProfileByUUID(UUID uuid)
318 { 236 {
319 MySQLSuperManager dbm = GetLockedConnection();
320
321 try 237 try
322 { 238 {
323 Dictionary<string, object> param = new Dictionary<string, object>(); 239 Dictionary<string, object> param = new Dictionary<string, object>();
324 param["?uuid"] = uuid.ToString(); 240 param["?uuid"] = uuid.ToString();
325 241
326 IDbCommand result = dbm.Manager.Query("SELECT * FROM regions WHERE uuid = ?uuid", param); 242 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
327 IDataReader reader = result.ExecuteReader(); 243 {
328 244 dbcon.Open();
329 RegionProfileData row = dbm.Manager.readSimRow(reader);
330 reader.Close();
331 result.Dispose();
332 245
333 return row; 246 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM regions WHERE uuid = ?uuid", param))
247 {
248 using (IDataReader reader = result.ExecuteReader())
249 {
250 RegionProfileData row = m_database.readSimRow(reader);
251 return row;
252 }
253 }
334 } 254 }
255 }
335 catch (Exception e) 256 catch (Exception e)
336 { 257 {
337 dbm.Manager.Reconnect(); 258 m_log.Error(e.Message, e);
338 m_log.Error(e.ToString());
339 return null; 259 return null;
340 } finally
341 {
342 dbm.Release();
343 } 260 }
344 } 261 }
345 262
@@ -351,37 +268,36 @@ namespace OpenSim.Data.MySQL
351 { 268 {
352 if (regionName.Length > 2) 269 if (regionName.Length > 2)
353 { 270 {
354 MySQLSuperManager dbm = GetLockedConnection();
355
356 try 271 try
357 { 272 {
358 Dictionary<string, object> param = new Dictionary<string, object>(); 273 Dictionary<string, object> param = new Dictionary<string, object>();
359 // Add % because this is a like query. 274 // Add % because this is a like query.
360 param["?regionName"] = regionName + "%"; 275 param["?regionName"] = regionName + "%";
361 // Order by statement will return shorter matches first. Only returns one record or no record.
362 IDbCommand result =
363 dbm.Manager.Query(
364 "SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1",
365 param);
366 IDataReader reader = result.ExecuteReader();
367 276
368 RegionProfileData row = dbm.Manager.readSimRow(reader); 277 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
369 reader.Close(); 278 {
370 result.Dispose(); 279 dbcon.Open();
371 280
372 return row; 281 // Order by statement will return shorter matches first. Only returns one record or no record.
282 using (IDbCommand result = m_database.Query(dbcon,
283 "SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1",
284 param))
285 {
286 using (IDataReader reader = result.ExecuteReader())
287 {
288 RegionProfileData row = m_database.readSimRow(reader);
289 return row;
290 }
291 }
292 }
373 } 293 }
374 catch (Exception e) 294 catch (Exception e)
375 { 295 {
376 dbm.Manager.Reconnect(); 296 m_log.Error(e.Message, e);
377 m_log.Error(e.ToString());
378 return null; 297 return null;
379 } 298 }
380 finally
381 {
382 dbm.Release();
383 }
384 } 299 }
300
385 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters"); 301 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
386 return null; 302 return null;
387 } 303 }
@@ -393,17 +309,16 @@ namespace OpenSim.Data.MySQL
393 /// <returns>Successful?</returns> 309 /// <returns>Successful?</returns>
394 override public DataResponse StoreProfile(RegionProfileData profile) 310 override public DataResponse StoreProfile(RegionProfileData profile)
395 { 311 {
396 MySQLSuperManager dbm = GetLockedConnection(); 312 try
397 try { 313 {
398 if (dbm.Manager.insertRegion(profile)) 314 if (m_database.insertRegion(profile))
399 {
400 return DataResponse.RESPONSE_OK; 315 return DataResponse.RESPONSE_OK;
401 } 316 else
402 return DataResponse.RESPONSE_ERROR; 317 return DataResponse.RESPONSE_ERROR;
403 } 318 }
404 finally 319 catch
405 { 320 {
406 dbm.Release(); 321 return DataResponse.RESPONSE_ERROR;
407 } 322 }
408 } 323 }
409 324
@@ -415,18 +330,16 @@ namespace OpenSim.Data.MySQL
415 //public DataResponse DeleteProfile(RegionProfileData profile) 330 //public DataResponse DeleteProfile(RegionProfileData profile)
416 override public DataResponse DeleteProfile(string uuid) 331 override public DataResponse DeleteProfile(string uuid)
417 { 332 {
418 MySQLSuperManager dbm = GetLockedConnection(); 333 try
419 334 {
420 335 if (m_database.deleteRegion(uuid))
421 try {
422 if (dbm.Manager.deleteRegion(uuid))
423 {
424 return DataResponse.RESPONSE_OK; 336 return DataResponse.RESPONSE_OK;
425 } 337 else
426 return DataResponse.RESPONSE_ERROR; 338 return DataResponse.RESPONSE_ERROR;
427 } finally 339 }
340 catch
428 { 341 {
429 dbm.Release(); 342 return DataResponse.RESPONSE_ERROR;
430 } 343 }
431 } 344 }
432 345
@@ -477,33 +390,32 @@ namespace OpenSim.Data.MySQL
477 /// <returns></returns> 390 /// <returns></returns>
478 override public ReservationData GetReservationAtPoint(uint x, uint y) 391 override public ReservationData GetReservationAtPoint(uint x, uint y)
479 { 392 {
480 MySQLSuperManager dbm = GetLockedConnection();
481
482 try 393 try
483 { 394 {
484 Dictionary<string, object> param = new Dictionary<string, object>(); 395 Dictionary<string, object> param = new Dictionary<string, object>();
485 param["?x"] = x.ToString(); 396 param["?x"] = x.ToString();
486 param["?y"] = y.ToString(); 397 param["?y"] = y.ToString();
487 IDbCommand result = 398
488 dbm.Manager.Query( 399 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
489 "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y", 400 {
490 param); 401 dbcon.Open();
491 IDataReader reader = result.ExecuteReader(); 402
492 403 using (IDbCommand result = m_database.Query(dbcon,
493 ReservationData row = dbm.Manager.readReservationRow(reader); 404 "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y",
494 reader.Close(); 405 param))
495 result.Dispose(); 406 {
496 407 using (IDataReader reader = result.ExecuteReader())
497 return row; 408 {
409 ReservationData row = m_database.readReservationRow(reader);
410 return row;
411 }
412 }
413 }
498 } 414 }
499 catch (Exception e) 415 catch (Exception e)
500 { 416 {
501 dbm.Manager.Reconnect(); 417 m_log.Error(e.Message, e);
502 m_log.Error(e.ToString());
503 return null; 418 return null;
504 } finally
505 {
506 dbm.Release();
507 } 419 }
508 } 420 }
509 } 421 }
diff --git a/OpenSim/Data/MySQL/MySQLInventoryData.cs b/OpenSim/Data/MySQL/MySQLInventoryData.cs
index 4b71e39..192deb2 100644
--- a/OpenSim/Data/MySQL/MySQLInventoryData.cs
+++ b/OpenSim/Data/MySQL/MySQLInventoryData.cs
@@ -26,7 +26,6 @@
26 */ 26 */
27 27
28using System; 28using System;
29using System.IO;
30using System.Collections.Generic; 29using System.Collections.Generic;
31using System.Reflection; 30using System.Reflection;
32using log4net; 31using log4net;
@@ -44,14 +43,10 @@ namespace OpenSim.Data.MySQL
44 private static readonly ILog m_log 43 private static readonly ILog m_log
45 = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 44 = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
46 45
47 /// <summary> 46 private string m_connectionString;
48 /// The database manager 47 private object m_dbLock = new object();
49 /// </summary>
50 private MySQLManager database;
51 48
52 private bool rollbackStore = false; 49 public string Version { get { return "1.0.0.0"; } }
53 private bool opengridmode = false;
54 private string rollbackDir = "";
55 50
56 public void Initialise() 51 public void Initialise()
57 { 52 {
@@ -72,37 +67,17 @@ namespace OpenSim.Data.MySQL
72 /// <param name="connect">connect string</param> 67 /// <param name="connect">connect string</param>
73 public void Initialise(string connect) 68 public void Initialise(string connect)
74 { 69 {
75 if (connect != String.Empty) 70 m_connectionString = connect;
76 {
77 database = new MySQLManager(connect);
78 }
79 else
80 {
81 m_log.Warn("Reverting to deprecated mysql_connection.ini file for connection info");
82 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
83 string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
84 string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
85 string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
86 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
87 string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
88 string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
89
90 rollbackDir = GridDataMySqlFile.ParseFileReadValue("rollbackdir");
91 rollbackStore = GridDataMySqlFile.ParseFileReadValue("rollback") == "true";
92 opengridmode = GridDataMySqlFile.ParseFileReadValue("opengridmode") == "true";
93
94 if (rollbackStore)
95 m_log.Warn("[MysqlInventory] Enabling rollback mode in: " + rollbackDir);
96
97 database =
98 new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword, settingPooling,
99 settingPort);
100 }
101 71
102 // This actually does the roll forward assembly stuff 72 // This actually does the roll forward assembly stuff
103 Assembly assem = GetType().Assembly; 73 Assembly assem = GetType().Assembly;
104 Migration m = new Migration(database.Connection, assem, "InventoryStore"); 74
105 m.Update(); 75 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
76 {
77 dbcon.Open();
78 Migration m = new Migration(dbcon, assem, "InventoryStore");
79 m.Update();
80 }
106 } 81 }
107 82
108 /// <summary> 83 /// <summary>
@@ -124,15 +99,6 @@ namespace OpenSim.Data.MySQL
124 } 99 }
125 100
126 /// <summary> 101 /// <summary>
127 /// Returns the version of this DB provider
128 /// </summary>
129 /// <returns>A string containing the DB provider version</returns>
130 public string Version
131 {
132 get { return database.getVersion(); }
133 }
134
135 /// <summary>
136 /// Returns a list of items in a specified folder 102 /// Returns a list of items in a specified folder
137 /// </summary> 103 /// </summary>
138 /// <param name="folderID">The folder to search</param> 104 /// <param name="folderID">The folder to search</param>
@@ -141,36 +107,37 @@ namespace OpenSim.Data.MySQL
141 { 107 {
142 try 108 try
143 { 109 {
144 lock (database) 110 lock (m_dbLock)
145 { 111 {
146 List<InventoryItemBase> items = new List<InventoryItemBase>(); 112 List<InventoryItemBase> items = new List<InventoryItemBase>();
147 113
148 database.CheckConnection(); 114 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
149
150 MySqlCommand result =
151 new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid",
152 database.Connection);
153 result.Parameters.AddWithValue("?uuid", folderID.ToString());
154 MySqlDataReader reader = result.ExecuteReader();
155
156 while (reader.Read())
157 { 115 {
158 // A null item (because something went wrong) breaks everything in the folder 116 dbcon.Open();
159 InventoryItemBase item = readInventoryItem(reader);
160 if (item != null)
161 items.Add(item);
162 }
163 117
164 reader.Close(); 118 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid", dbcon))
165 result.Dispose(); 119 {
120 result.Parameters.AddWithValue("?uuid", folderID.ToString());
121
122 using (MySqlDataReader reader = result.ExecuteReader())
123 {
124 while (reader.Read())
125 {
126 // A null item (because something went wrong) breaks everything in the folder
127 InventoryItemBase item = readInventoryItem(reader);
128 if (item != null)
129 items.Add(item);
130 }
166 131
167 return items; 132 return items;
133 }
134 }
135 }
168 } 136 }
169 } 137 }
170 catch (Exception e) 138 catch (Exception e)
171 { 139 {
172 database.Reconnect(); 140 m_log.Error(e.Message, e);
173 m_log.Error(e.ToString());
174 return null; 141 return null;
175 } 142 }
176 } 143 }
@@ -184,33 +151,33 @@ namespace OpenSim.Data.MySQL
184 { 151 {
185 try 152 try
186 { 153 {
187 lock (database) 154 lock (m_dbLock)
188 { 155 {
189 database.CheckConnection(); 156 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
190 157 {
191 MySqlCommand result = 158 dbcon.Open();
192 new MySqlCommand(
193 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid",
194 database.Connection);
195 result.Parameters.AddWithValue("?uuid", user.ToString());
196 result.Parameters.AddWithValue("?zero", UUID.Zero.ToString());
197 MySqlDataReader reader = result.ExecuteReader();
198
199 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
200 while (reader.Read())
201 items.Add(readInventoryFolder(reader));
202 159
160 using (MySqlCommand result = new MySqlCommand(
161 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", dbcon))
162 {
163 result.Parameters.AddWithValue("?uuid", user.ToString());
164 result.Parameters.AddWithValue("?zero", UUID.Zero.ToString());
203 165
204 reader.Close(); 166 using (MySqlDataReader reader = result.ExecuteReader())
205 result.Dispose(); 167 {
168 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
169 while (reader.Read())
170 items.Add(readInventoryFolder(reader));
206 171
207 return items; 172 return items;
173 }
174 }
175 }
208 } 176 }
209 } 177 }
210 catch (Exception e) 178 catch (Exception e)
211 { 179 {
212 database.Reconnect(); 180 m_log.Error(e.Message, e);
213 m_log.Error(e.ToString());
214 return null; 181 return null;
215 } 182 }
216 } 183 }
@@ -225,46 +192,44 @@ namespace OpenSim.Data.MySQL
225 { 192 {
226 try 193 try
227 { 194 {
228 lock (database) 195 lock (m_dbLock)
229 { 196 {
230 database.CheckConnection(); 197 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
198 {
199 dbcon.Open();
231 200
232 MySqlCommand result = 201 using (MySqlCommand result = new MySqlCommand(
233 new MySqlCommand( 202 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", dbcon))
234 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", 203 {
235 database.Connection); 204 result.Parameters.AddWithValue("?uuid", user.ToString());
236 result.Parameters.AddWithValue("?uuid", user.ToString()); 205 result.Parameters.AddWithValue("?zero", UUID.Zero.ToString());
237 result.Parameters.AddWithValue("?zero", UUID.Zero.ToString());
238 206
239 MySqlDataReader reader = result.ExecuteReader(); 207 using (MySqlDataReader reader = result.ExecuteReader())
208 {
209 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
210 while (reader.Read())
211 items.Add(readInventoryFolder(reader));
240 212
241 List<InventoryFolderBase> items = new List<InventoryFolderBase>(); 213 InventoryFolderBase rootFolder = null;
242 while (reader.Read())
243 items.Add(readInventoryFolder(reader));
244 214
245 InventoryFolderBase rootFolder = null; 215 // There should only ever be one root folder for a user. However, if there's more
216 // than one we'll simply use the first one rather than failing. It would be even
217 // nicer to print some message to this effect, but this feels like it's too low a
218 // to put such a message out, and it's too minor right now to spare the time to
219 // suitably refactor.
220 if (items.Count > 0)
221 rootFolder = items[0];
246 222
247 // There should only ever be one root folder for a user. However, if there's more 223 return rootFolder;
248 // than one we'll simply use the first one rather than failing. It would be even 224 }
249 // nicer to print some message to this effect, but this feels like it's too low a 225 }
250 // to put such a message out, and it's too minor right now to spare the time to
251 // suitably refactor.
252 if (items.Count > 0)
253 {
254 rootFolder = items[0];
255 } 226 }
256
257 reader.Close();
258 result.Dispose();
259
260 return rootFolder;
261 } 227 }
262 } 228 }
263 catch (Exception e) 229 catch (Exception e)
264 { 230 {
265 database.Reconnect(); 231 m_log.Error(e.Message, e);
266 m_log.Error(e.ToString()); 232 return null;
267 throw;
268 } 233 }
269 } 234 }
270 235
@@ -279,31 +244,31 @@ namespace OpenSim.Data.MySQL
279 { 244 {
280 try 245 try
281 { 246 {
282 lock (database) 247 lock (m_dbLock)
283 { 248 {
284 database.CheckConnection(); 249 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
285 250 {
286 MySqlCommand result = 251 dbcon.Open();
287 new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid",
288 database.Connection);
289 result.Parameters.AddWithValue("?uuid", parentID.ToString());
290 MySqlDataReader reader = result.ExecuteReader();
291 252
292 List<InventoryFolderBase> items = new List<InventoryFolderBase>(); 253 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid", dbcon))
254 {
255 result.Parameters.AddWithValue("?uuid", parentID.ToString());
256 using (MySqlDataReader reader = result.ExecuteReader())
257 {
258 List<InventoryFolderBase> items = new List<InventoryFolderBase>();
293 259
294 while (reader.Read()) 260 while (reader.Read())
295 items.Add(readInventoryFolder(reader)); 261 items.Add(readInventoryFolder(reader));
296 262
297 reader.Close(); 263 return items;
298 result.Dispose(); 264 }
299 265 }
300 return items; 266 }
301 } 267 }
302 } 268 }
303 catch (Exception e) 269 catch (Exception e)
304 { 270 {
305 database.Reconnect(); 271 m_log.Error(e.Message, e);
306 m_log.Error(e.ToString());
307 return null; 272 return null;
308 } 273 }
309 } 274 }
@@ -378,29 +343,31 @@ namespace OpenSim.Data.MySQL
378 { 343 {
379 try 344 try
380 { 345 {
381 lock (database) 346 lock (m_dbLock)
382 { 347 {
383 database.CheckConnection(); 348 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
384 349 {
385 MySqlCommand result = 350 dbcon.Open();
386 new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", database.Connection);
387 result.Parameters.AddWithValue("?uuid", itemID.ToString());
388 MySqlDataReader reader = result.ExecuteReader();
389 351
390 InventoryItemBase item = null; 352 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", dbcon))
391 if (reader.Read()) 353 {
392 item = readInventoryItem(reader); 354 result.Parameters.AddWithValue("?uuid", itemID.ToString());
393 355
394 reader.Close(); 356 using (MySqlDataReader reader = result.ExecuteReader())
395 result.Dispose(); 357 {
358 InventoryItemBase item = null;
359 if (reader.Read())
360 item = readInventoryItem(reader);
396 361
397 return item; 362 return item;
363 }
364 }
365 }
398 } 366 }
399 } 367 }
400 catch (Exception e) 368 catch (Exception e)
401 { 369 {
402 database.Reconnect(); 370 m_log.Error(e.Message, e);
403 m_log.Error(e.ToString());
404 } 371 }
405 return null; 372 return null;
406 } 373 }
@@ -425,7 +392,7 @@ namespace OpenSim.Data.MySQL
425 } 392 }
426 catch (Exception e) 393 catch (Exception e)
427 { 394 {
428 m_log.Error(e.ToString()); 395 m_log.Error(e.Message, e);
429 } 396 }
430 397
431 return null; 398 return null;
@@ -441,151 +408,35 @@ namespace OpenSim.Data.MySQL
441 { 408 {
442 try 409 try
443 { 410 {
444 lock (database) 411 lock (m_dbLock)
445 { 412 {
446 database.CheckConnection(); 413 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
414 {
415 dbcon.Open();
447 416
448 MySqlCommand result = 417 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", dbcon))
449 new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", database.Connection); 418 {
450 result.Parameters.AddWithValue("?uuid", folderID.ToString()); 419 result.Parameters.AddWithValue("?uuid", folderID.ToString());
451 MySqlDataReader reader = result.ExecuteReader();
452 420
453 InventoryFolderBase folder = null; 421 using (MySqlDataReader reader = result.ExecuteReader())
454 if (reader.Read()) 422 {
455 folder = readInventoryFolder(reader); 423 InventoryFolderBase folder = null;
456 reader.Close(); 424 if (reader.Read())
457 result.Dispose(); 425 folder = readInventoryFolder(reader);
458 426
459 return folder; 427 return folder;
428 }
429 }
430 }
460 } 431 }
461 } 432 }
462 catch (Exception e) 433 catch (Exception e)
463 { 434 {
464 database.Reconnect(); 435 m_log.Error(e.Message, e);
465 m_log.Error(e.ToString());
466 return null; 436 return null;
467 } 437 }
468 } 438 }
469 439
470 #region Inventory Rollback-via-.sql Support
471 /// <summary>
472 /// Not a good SQL escape function, but it'll do the job (if mutilate the data.)
473 /// Someone may want to write something better here.
474 /// </summary>
475 /// <param name="str"></param>
476 /// <returns></returns>
477 private static string cheapSQLescape(string str)
478 {
479 str = str.Replace("\\", "");
480 str = str.Replace("'", "");
481 str = str.Replace("\"", "");
482 return "'" + str + "'";
483 }
484
485 private static string InventoryItemToSql(InventoryItemBase item)
486 {
487 string sql =
488 "REPLACE /*! INVITEM AT ***$SUBS$*** */ INTO inventoryitems (inventoryID, assetID, assetType, parentFolderID, avatarID, inventoryName"
489 + ", inventoryDescription, inventoryNextPermissions, inventoryCurrentPermissions, invType"
490 + ", creatorID, inventoryBasePermissions, inventoryEveryOnePermissions, inventoryGroupPermissions, salePrice, saleType"
491 + ", creationDate, groupID, groupOwned, flags) VALUES ";
492 sql +=
493 "(?inventoryID, ?assetID, ?assetType, ?parentFolderID, ?avatarID, ?inventoryName, ?inventoryDescription"
494 + ", ?inventoryNextPermissions, ?inventoryCurrentPermissions, ?invType, ?creatorID"
495 + ", ?inventoryBasePermissions, ?inventoryEveryOnePermissions, ?inventoryGroupPermissions, ?salePrice, ?saleType, ?creationDate"
496 + ", ?groupID, ?groupOwned, ?flags);\r\n";
497
498 string itemName = item.Name;
499 string itemDesc = item.Description;
500
501 sql = sql.Replace("$SUBS$", Util.UnixTimeSinceEpoch().ToString());
502
503 sql = sql.Replace("?inventoryID", cheapSQLescape(item.ID.ToString()));
504 sql = sql.Replace("?assetID", cheapSQLescape(item.AssetID.ToString()));
505 sql = sql.Replace("?assetType", cheapSQLescape(item.AssetType.ToString()));
506 sql = sql.Replace("?parentFolderID", cheapSQLescape(item.Folder.ToString()));
507 sql = sql.Replace("?avatarID", cheapSQLescape(item.Owner.ToString()));
508 sql = sql.Replace("?inventoryName", cheapSQLescape(itemName));
509 sql = sql.Replace("?inventoryDescription", cheapSQLescape(itemDesc));
510 sql = sql.Replace("?inventoryNextPermissions", cheapSQLescape(item.NextPermissions.ToString()));
511 sql = sql.Replace("?inventoryCurrentPermissions", cheapSQLescape(item.CurrentPermissions.ToString()));
512 sql = sql.Replace("?invType", cheapSQLescape(item.InvType.ToString()));
513 sql = sql.Replace("?creatorID", cheapSQLescape(item.CreatorId));
514 sql = sql.Replace("?inventoryBasePermissions", cheapSQLescape(item.BasePermissions.ToString()));
515 sql = sql.Replace("?inventoryEveryOnePermissions", cheapSQLescape(item.EveryOnePermissions.ToString()));
516 sql = sql.Replace("?inventoryGroupPermissions", cheapSQLescape(item.GroupPermissions.ToString()));
517 sql = sql.Replace("?salePrice", cheapSQLescape(item.SalePrice.ToString()));
518 sql = sql.Replace("?saleType", cheapSQLescape(unchecked((sbyte)item.SaleType).ToString()));
519 sql = sql.Replace("?creationDate", cheapSQLescape(item.CreationDate.ToString()));
520 sql = sql.Replace("?groupID", cheapSQLescape(item.GroupID.ToString()));
521 sql = sql.Replace("?groupOwned", cheapSQLescape(item.GroupOwned.ToString()));
522 sql = sql.Replace("?flags", cheapSQLescape(item.Flags.ToString()));
523
524 return sql;
525 }
526
527 private static string InventoryFolderToSql(InventoryFolderBase folder)
528 {
529 string sql =
530 "REPLACE /*! INVFOLDER AT ***$SUBS$*** */ INTO inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version) VALUES ";
531 sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName, ?type, ?version);\r\n";
532
533 string folderName = folder.Name;
534
535 sql = sql.Replace("$SUBS$", Util.UnixTimeSinceEpoch().ToString());
536
537 sql = sql.Replace("?folderID", cheapSQLescape(folder.ID.ToString()));
538 sql = sql.Replace("?agentID", cheapSQLescape(folder.Owner.ToString()));
539 sql = sql.Replace("?parentFolderID", cheapSQLescape(folder.ParentID.ToString()));
540 sql = sql.Replace("?folderName", cheapSQLescape(folderName));
541 sql = sql.Replace("?type", cheapSQLescape(folder.Type.ToString()));
542 sql = sql.Replace("?version", cheapSQLescape(folder.Version.ToString()));
543
544 return sql;
545 }
546
547 private static string getRollbackFolderDate()
548 {
549 return DateTime.UtcNow.Year.ToString() + "-" + DateTime.UtcNow.Month.ToString() + "-" +
550 DateTime.UtcNow.Day.ToString();
551 }
552
553 private void StoreRollbackItem(UUID ItemID)
554 {
555 if (rollbackStore == true)
556 {
557 string todaysPath = RollbackGetTodaysPath();
558
559 InventoryItemBase imb = getInventoryItem(ItemID);
560 string sql = InventoryItemToSql(imb);
561 File.AppendAllText(Path.Combine(todaysPath, imb.Owner.ToString()), sql);
562 }
563 }
564
565 private void StoreRollbackFolder(UUID FolderID)
566 {
567 if (rollbackStore == true)
568 {
569 string todaysPath = RollbackGetTodaysPath();
570
571 InventoryFolderBase ifb = getInventoryFolder(FolderID);
572 string sql = InventoryFolderToSql(ifb);
573 File.AppendAllText(Path.Combine(todaysPath, ifb.Owner.ToString()), sql);
574 }
575 }
576
577 private string RollbackGetTodaysPath()
578 {
579 if (!Directory.Exists(rollbackDir))
580 Directory.CreateDirectory(rollbackDir);
581
582 string todaysPath = Path.Combine(rollbackDir, getRollbackFolderDate());
583 if (!Directory.Exists(todaysPath))
584 Directory.CreateDirectory(todaysPath);
585 return todaysPath;
586 }
587 #endregion
588
589 /// <summary> 440 /// <summary>
590 /// Adds a specified item to the database 441 /// Adds a specified item to the database
591 /// </summary> 442 /// </summary>
@@ -619,46 +470,48 @@ namespace OpenSim.Data.MySQL
619 470
620 try 471 try
621 { 472 {
622 database.CheckConnection(); 473 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
623
624 MySqlCommand result = new MySqlCommand(sql, database.Connection);
625 result.Parameters.AddWithValue("?inventoryID", item.ID.ToString());
626 result.Parameters.AddWithValue("?assetID", item.AssetID.ToString());
627 result.Parameters.AddWithValue("?assetType", item.AssetType.ToString());
628 result.Parameters.AddWithValue("?parentFolderID", item.Folder.ToString());
629 result.Parameters.AddWithValue("?avatarID", item.Owner.ToString());
630 result.Parameters.AddWithValue("?inventoryName", itemName);
631 result.Parameters.AddWithValue("?inventoryDescription", itemDesc);
632 result.Parameters.AddWithValue("?inventoryNextPermissions", item.NextPermissions.ToString());
633 result.Parameters.AddWithValue("?inventoryCurrentPermissions",
634 item.CurrentPermissions.ToString());
635 result.Parameters.AddWithValue("?invType", item.InvType);
636 result.Parameters.AddWithValue("?creatorID", item.CreatorId);
637 result.Parameters.AddWithValue("?inventoryBasePermissions", item.BasePermissions);
638 result.Parameters.AddWithValue("?inventoryEveryOnePermissions", item.EveryOnePermissions);
639 result.Parameters.AddWithValue("?inventoryGroupPermissions", item.GroupPermissions);
640 result.Parameters.AddWithValue("?salePrice", item.SalePrice);
641 result.Parameters.AddWithValue("?saleType", unchecked((sbyte)item.SaleType));
642 result.Parameters.AddWithValue("?creationDate", item.CreationDate);
643 result.Parameters.AddWithValue("?groupID", item.GroupID);
644 result.Parameters.AddWithValue("?groupOwned", item.GroupOwned);
645 result.Parameters.AddWithValue("?flags", item.Flags);
646
647 lock (database)
648 { 474 {
649 result.ExecuteNonQuery(); 475 dbcon.Open();
650 } 476
477 MySqlCommand result = new MySqlCommand(sql, dbcon);
478 result.Parameters.AddWithValue("?inventoryID", item.ID.ToString());
479 result.Parameters.AddWithValue("?assetID", item.AssetID.ToString());
480 result.Parameters.AddWithValue("?assetType", item.AssetType.ToString());
481 result.Parameters.AddWithValue("?parentFolderID", item.Folder.ToString());
482 result.Parameters.AddWithValue("?avatarID", item.Owner.ToString());
483 result.Parameters.AddWithValue("?inventoryName", itemName);
484 result.Parameters.AddWithValue("?inventoryDescription", itemDesc);
485 result.Parameters.AddWithValue("?inventoryNextPermissions", item.NextPermissions.ToString());
486 result.Parameters.AddWithValue("?inventoryCurrentPermissions",
487 item.CurrentPermissions.ToString());
488 result.Parameters.AddWithValue("?invType", item.InvType);
489 result.Parameters.AddWithValue("?creatorID", item.CreatorId);
490 result.Parameters.AddWithValue("?inventoryBasePermissions", item.BasePermissions);
491 result.Parameters.AddWithValue("?inventoryEveryOnePermissions", item.EveryOnePermissions);
492 result.Parameters.AddWithValue("?inventoryGroupPermissions", item.GroupPermissions);
493 result.Parameters.AddWithValue("?salePrice", item.SalePrice);
494 result.Parameters.AddWithValue("?saleType", unchecked((sbyte)item.SaleType));
495 result.Parameters.AddWithValue("?creationDate", item.CreationDate);
496 result.Parameters.AddWithValue("?groupID", item.GroupID);
497 result.Parameters.AddWithValue("?groupOwned", item.GroupOwned);
498 result.Parameters.AddWithValue("?flags", item.Flags);
499
500 lock (m_dbLock)
501 {
502 result.ExecuteNonQuery();
503 }
651 504
652 result.Dispose(); 505 result.Dispose();
653 506
654 result = new MySqlCommand("update inventoryfolders set version=version+1 where folderID = ?folderID", database.Connection); 507 result = new MySqlCommand("update inventoryfolders set version=version+1 where folderID = ?folderID", dbcon);
655 result.Parameters.AddWithValue("?folderID", item.Folder.ToString 508 result.Parameters.AddWithValue("?folderID", item.Folder.ToString());
656()); 509 lock (m_dbLock)
657 lock (database) 510 {
658 { 511 result.ExecuteNonQuery();
659 result.ExecuteNonQuery(); 512 }
513 result.Dispose();
660 } 514 }
661 result.Dispose();
662 } 515 }
663 catch (MySqlException e) 516 catch (MySqlException e)
664 { 517 {
@@ -672,8 +525,6 @@ namespace OpenSim.Data.MySQL
672 /// <param name="item">Inventory item to update</param> 525 /// <param name="item">Inventory item to update</param>
673 public void updateInventoryItem(InventoryItemBase item) 526 public void updateInventoryItem(InventoryItemBase item)
674 { 527 {
675 StoreRollbackItem(item.ID);
676
677 addInventoryItem(item); 528 addInventoryItem(item);
678 } 529 }
679 530
@@ -683,25 +534,24 @@ namespace OpenSim.Data.MySQL
683 /// <param name="item">The inventory item UUID to delete</param> 534 /// <param name="item">The inventory item UUID to delete</param>
684 public void deleteInventoryItem(UUID itemID) 535 public void deleteInventoryItem(UUID itemID)
685 { 536 {
686 StoreRollbackItem(itemID);
687
688 try 537 try
689 { 538 {
690 database.CheckConnection(); 539 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
540 {
541 dbcon.Open();
691 542
692 MySqlCommand cmd = 543 MySqlCommand cmd = new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", dbcon);
693 new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", database.Connection); 544 cmd.Parameters.AddWithValue("?uuid", itemID.ToString());
694 cmd.Parameters.AddWithValue("?uuid", itemID.ToString());
695 545
696 lock (database) 546 lock (m_dbLock)
697 { 547 {
698 cmd.ExecuteNonQuery(); 548 cmd.ExecuteNonQuery();
549 }
699 } 550 }
700 } 551 }
701 catch (MySqlException e) 552 catch (MySqlException e)
702 { 553 {
703 database.Reconnect(); 554 m_log.Error(e.Message, e);
704 m_log.Error(e.ToString());
705 } 555 }
706 } 556 }
707 557
@@ -732,26 +582,29 @@ namespace OpenSim.Data.MySQL
732 m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length + " to " + folderName.Length + " characters on add folder"); 582 m_log.Warn("[INVENTORY DB]: Name field truncated from " + folder.Name.Length + " to " + folderName.Length + " characters on add folder");
733 } 583 }
734 584
735 database.CheckConnection(); 585 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
586 {
587 dbcon.Open();
736 588
737 MySqlCommand cmd = new MySqlCommand(sql, database.Connection); 589 MySqlCommand cmd = new MySqlCommand(sql, dbcon);
738 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString()); 590 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString());
739 cmd.Parameters.AddWithValue("?agentID", folder.Owner.ToString()); 591 cmd.Parameters.AddWithValue("?agentID", folder.Owner.ToString());
740 cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString()); 592 cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString());
741 cmd.Parameters.AddWithValue("?folderName", folderName); 593 cmd.Parameters.AddWithValue("?folderName", folderName);
742 cmd.Parameters.AddWithValue("?type", folder.Type); 594 cmd.Parameters.AddWithValue("?type", folder.Type);
743 cmd.Parameters.AddWithValue("?version", folder.Version); 595 cmd.Parameters.AddWithValue("?version", folder.Version);
744 596
745 try 597 try
746 {
747 lock (database)
748 { 598 {
749 cmd.ExecuteNonQuery(); 599 lock (m_dbLock)
600 {
601 cmd.ExecuteNonQuery();
602 }
603 }
604 catch (Exception e)
605 {
606 m_log.Error(e.ToString());
750 } 607 }
751 }
752 catch (Exception e)
753 {
754 m_log.Error(e.ToString());
755 } 608 }
756 } 609 }
757 610
@@ -761,7 +614,6 @@ namespace OpenSim.Data.MySQL
761 /// <param name="folder">Folder to update</param> 614 /// <param name="folder">Folder to update</param>
762 public void updateInventoryFolder(InventoryFolderBase folder) 615 public void updateInventoryFolder(InventoryFolderBase folder)
763 { 616 {
764 StoreRollbackFolder(folder.ID);
765 addInventoryFolder(folder); 617 addInventoryFolder(folder);
766 } 618 }
767 619
@@ -772,27 +624,28 @@ namespace OpenSim.Data.MySQL
772 /// <remarks>UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID</remarks> 624 /// <remarks>UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID</remarks>
773 public void moveInventoryFolder(InventoryFolderBase folder) 625 public void moveInventoryFolder(InventoryFolderBase folder)
774 { 626 {
775 StoreRollbackFolder(folder.ID);
776
777 string sql = 627 string sql =
778 "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID"; 628 "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID";
779 629
780 database.CheckConnection(); 630 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
631 {
632 dbcon.Open();
781 633
782 MySqlCommand cmd = new MySqlCommand(sql, database.Connection); 634 MySqlCommand cmd = new MySqlCommand(sql, dbcon);
783 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString()); 635 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString());
784 cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString()); 636 cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString());
785 637
786 try 638 try
787 {
788 lock (database)
789 { 639 {
790 cmd.ExecuteNonQuery(); 640 lock (m_dbLock)
641 {
642 cmd.ExecuteNonQuery();
643 }
644 }
645 catch (Exception e)
646 {
647 m_log.Error(e.ToString());
791 } 648 }
792 }
793 catch (Exception e)
794 {
795 m_log.Error(e.ToString());
796 } 649 }
797 } 650 }
798 651
@@ -836,95 +689,102 @@ namespace OpenSim.Data.MySQL
836 try 689 try
837 { 690 {
838 List<InventoryFolderBase> folders = new List<InventoryFolderBase>(); 691 List<InventoryFolderBase> folders = new List<InventoryFolderBase>();
839 Dictionary<UUID, List<InventoryFolderBase>> hashtable 692 Dictionary<UUID, List<InventoryFolderBase>> hashtable = new Dictionary<UUID, List<InventoryFolderBase>>(); ;
840 = new Dictionary<UUID, List<InventoryFolderBase>>(); ;
841 List<InventoryFolderBase> parentFolder = new List<InventoryFolderBase>(); 693 List<InventoryFolderBase> parentFolder = new List<InventoryFolderBase>();
842 lock (database) 694 bool buildResultsFromHashTable = false;
843 {
844 MySqlCommand result;
845 MySqlDataReader reader;
846 bool buildResultsFromHashTable = false;
847
848 database.CheckConnection();
849
850 /* Fetch the parent folder from the database to determine the agent ID, and if
851 * we're querying the root of the inventory folder tree */
852 result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid",
853 database.Connection);
854 result.Parameters.AddWithValue("?uuid", parentID.ToString());
855 reader = result.ExecuteReader();
856 while (reader.Read()) // Should be at most 1 result
857 parentFolder.Add(readInventoryFolder(reader));
858 reader.Close();
859 result.Dispose();
860 695
861 if (parentFolder.Count >= 1) // No result means parent folder does not exist 696 lock (m_dbLock)
697 {
698 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
862 { 699 {
863 if (parentFolder[0].ParentID == UUID.Zero) // We are querying the root folder 700 dbcon.Open();
701
702 /* Fetch the parent folder from the database to determine the agent ID, and if
703 * we're querying the root of the inventory folder tree */
704 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", dbcon))
864 { 705 {
865 /* Get all of the agent's folders from the database, put them in a list and return it */ 706 result.Parameters.AddWithValue("?uuid", parentID.ToString());
866 result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE agentID = ?uuid", 707
867 database.Connection); 708 using (MySqlDataReader reader = result.ExecuteReader())
868 result.Parameters.AddWithValue("?uuid", parentFolder[0].Owner.ToString());
869 reader = result.ExecuteReader();
870 while (reader.Read())
871 { 709 {
872 InventoryFolderBase curFolder = readInventoryFolder(reader); 710 // Should be at most 1 result
873 if (curFolder.ID != parentID) // Do not need to add the root node of the tree to the list 711 while (reader.Read())
874 folders.Add(curFolder); 712 parentFolder.Add(readInventoryFolder(reader));
875 } 713 }
876 reader.Close(); 714 }
877 result.Dispose(); 715
878 } // if we are querying the root folder 716 if (parentFolder.Count >= 1) // No result means parent folder does not exist
879 else // else we are querying a subtree of the inventory folder tree
880 { 717 {
881 /* Get all of the agent's folders from the database, put them all in a hash table 718 if (parentFolder[0].ParentID == UUID.Zero) // We are querying the root folder
882 * indexed by their parent ID */
883 result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE agentID = ?uuid",
884 database.Connection);
885 result.Parameters.AddWithValue("?uuid", parentFolder[0].Owner.ToString());
886 reader = result.ExecuteReader();
887 while (reader.Read())
888 { 719 {
889 InventoryFolderBase curFolder = readInventoryFolder(reader); 720 /* Get all of the agent's folders from the database, put them in a list and return it */
890 if (hashtable.ContainsKey(curFolder.ParentID)) // Current folder already has a sibling 721 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE agentID = ?uuid", dbcon))
891 hashtable[curFolder.ParentID].Add(curFolder); // append to sibling list
892 else // else current folder has no known (yet) siblings
893 { 722 {
894 List<InventoryFolderBase> siblingList = new List<InventoryFolderBase>(); 723 result.Parameters.AddWithValue("?uuid", parentFolder[0].Owner.ToString());
895 siblingList.Add(curFolder); 724
896 // Current folder has no known (yet) siblings 725 using (MySqlDataReader reader = result.ExecuteReader())
897 hashtable.Add(curFolder.ParentID, siblingList); 726 {
727 while (reader.Read())
728 {
729 InventoryFolderBase curFolder = readInventoryFolder(reader);
730 if (curFolder.ID != parentID) // Do not need to add the root node of the tree to the list
731 folders.Add(curFolder);
732 }
733 }
734 }
735 } // if we are querying the root folder
736 else // else we are querying a subtree of the inventory folder tree
737 {
738 /* Get all of the agent's folders from the database, put them all in a hash table
739 * indexed by their parent ID */
740 using (MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE agentID = ?uuid", dbcon))
741 {
742 result.Parameters.AddWithValue("?uuid", parentFolder[0].Owner.ToString());
743
744 using (MySqlDataReader reader = result.ExecuteReader())
745 {
746 while (reader.Read())
747 {
748 InventoryFolderBase curFolder = readInventoryFolder(reader);
749 if (hashtable.ContainsKey(curFolder.ParentID)) // Current folder already has a sibling
750 hashtable[curFolder.ParentID].Add(curFolder); // append to sibling list
751 else // else current folder has no known (yet) siblings
752 {
753 List<InventoryFolderBase> siblingList = new List<InventoryFolderBase>();
754 siblingList.Add(curFolder);
755 // Current folder has no known (yet) siblings
756 hashtable.Add(curFolder.ParentID, siblingList);
757 }
758 } // while more items to read from the database
759 }
898 } 760 }
899 } // while more items to read from the database
900 reader.Close();
901 result.Dispose();
902 761
903 // Set flag so we know we need to build the results from the hash table after 762 // Set flag so we know we need to build the results from the hash table after
904 // we unlock the database 763 // we unlock the database
905 buildResultsFromHashTable = true; 764 buildResultsFromHashTable = true;
906 765
907 } // else we are querying a subtree of the inventory folder tree 766 } // else we are querying a subtree of the inventory folder tree
908 } // if folder parentID exists 767 } // if folder parentID exists
909 768
910 if (buildResultsFromHashTable) 769 if (buildResultsFromHashTable)
911 { 770 {
912 /* We have all of the user's folders stored in a hash table indexed by their parent ID 771 /* We have all of the user's folders stored in a hash table indexed by their parent ID
913 * and we need to return the requested subtree. We will build the requested subtree 772 * and we need to return the requested subtree. We will build the requested subtree
914 * by performing a breadth-first-search on the hash table */ 773 * by performing a breadth-first-search on the hash table */
915 if (hashtable.ContainsKey(parentID)) 774 if (hashtable.ContainsKey(parentID))
916 folders.AddRange(hashtable[parentID]); 775 folders.AddRange(hashtable[parentID]);
917 for (int i = 0; i < folders.Count; i++) // **Note: folders.Count is *not* static 776 for (int i = 0; i < folders.Count; i++) // **Note: folders.Count is *not* static
918 if (hashtable.ContainsKey(folders[i].ID)) 777 if (hashtable.ContainsKey(folders[i].ID))
919 folders.AddRange(hashtable[folders[i].ID]); 778 folders.AddRange(hashtable[folders[i].ID]);
779 }
920 } 780 }
921 } // lock (database) 781 } // lock (database)
782
922 return folders; 783 return folders;
923 } 784 }
924 catch (Exception e) 785 catch (Exception e)
925 { 786 {
926 database.Reconnect(); 787 m_log.Error(e.Message, e);
927 m_log.Error(e.ToString());
928 return null; 788 return null;
929 } 789 }
930 } 790 }
@@ -935,25 +795,24 @@ namespace OpenSim.Data.MySQL
935 /// <param name="folderID">the folder UUID</param> 795 /// <param name="folderID">the folder UUID</param>
936 protected void deleteOneFolder(UUID folderID) 796 protected void deleteOneFolder(UUID folderID)
937 { 797 {
938 StoreRollbackFolder(folderID);
939
940 try 798 try
941 { 799 {
942 database.CheckConnection(); 800 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
801 {
802 dbcon.Open();
943 803
944 MySqlCommand cmd = 804 using (MySqlCommand cmd = new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid", dbcon))
945 new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid", database.Connection); 805 {
946 cmd.Parameters.AddWithValue("?uuid", folderID.ToString()); 806 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
947 807
948 lock (database) 808 lock (m_dbLock)
949 { 809 cmd.ExecuteNonQuery();
950 cmd.ExecuteNonQuery(); 810 }
951 } 811 }
952 } 812 }
953 catch (MySqlException e) 813 catch (MySqlException e)
954 { 814 {
955 database.Reconnect(); 815 m_log.Error(e.Message, e);
956 m_log.Error(e.ToString());
957 } 816 }
958 } 817 }
959 818
@@ -963,30 +822,23 @@ namespace OpenSim.Data.MySQL
963 /// <param name="folderID">the folder UUID</param> 822 /// <param name="folderID">the folder UUID</param>
964 protected void deleteItemsInFolder(UUID folderID) 823 protected void deleteItemsInFolder(UUID folderID)
965 { 824 {
966 if (rollbackStore)
967 {
968 foreach (InventoryItemBase itemBase in getInventoryInFolder(folderID))
969 {
970 StoreRollbackItem(itemBase.ID);
971 }
972 }
973
974 try 825 try
975 { 826 {
976 database.CheckConnection(); 827 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
828 {
829 dbcon.Open();
977 830
978 MySqlCommand cmd = 831 using (MySqlCommand cmd = new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", dbcon))
979 new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", database.Connection); 832 {
980 cmd.Parameters.AddWithValue("?uuid", folderID.ToString()); 833 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
981 834
982 lock (database) 835 lock (m_dbLock)
983 { 836 cmd.ExecuteNonQuery();
984 cmd.ExecuteNonQuery(); 837 }
985 } 838 }
986 } 839 }
987 catch (MySqlException e) 840 catch (MySqlException e)
988 { 841 {
989 database.Reconnect();
990 m_log.Error(e.ToString()); 842 m_log.Error(e.ToString());
991 } 843 }
992 } 844 }
@@ -999,80 +851,53 @@ namespace OpenSim.Data.MySQL
999 { 851 {
1000 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID); 852 List<InventoryFolderBase> subFolders = getFolderHierarchy(folderID);
1001 853
1002 // Dont delete in OGM - makes for easier restores if someone sends a malcious command. (just restore the folder entry) 854 //Delete all sub-folders
1003 if (opengridmode == false) 855 foreach (InventoryFolderBase f in subFolders)
1004 { 856 {
1005 //Delete all sub-folders 857 deleteOneFolder(f.ID);
1006 foreach (InventoryFolderBase f in subFolders) 858 deleteItemsInFolder(f.ID);
1007 {
1008 StoreRollbackFolder(f.ID);
1009 deleteOneFolder(f.ID);
1010
1011 if (rollbackStore)
1012 {
1013 foreach (InventoryItemBase itemBase in getInventoryInFolder(f.ID))
1014 {
1015 StoreRollbackItem(itemBase.ID);
1016 }
1017 }
1018 deleteItemsInFolder(f.ID);
1019 }
1020 } 859 }
1021 860
1022 StoreRollbackFolder(folderID);
1023 //Delete the actual row 861 //Delete the actual row
1024 deleteOneFolder(folderID); 862 deleteOneFolder(folderID);
1025 863 deleteItemsInFolder(folderID);
1026 // Just delete the folder context in OGM
1027 if (opengridmode == false)
1028 {
1029 if (rollbackStore)
1030 {
1031 foreach (InventoryItemBase itemBase in getInventoryInFolder(folderID))
1032 {
1033 StoreRollbackItem(itemBase.ID);
1034 }
1035 }
1036 deleteItemsInFolder(folderID);
1037 }
1038 } 864 }
1039 865
1040 public List<InventoryItemBase> fetchActiveGestures(UUID avatarID) 866 public List<InventoryItemBase> fetchActiveGestures(UUID avatarID)
1041 { 867 {
1042 MySqlDataReader result = null; 868 lock (m_dbLock)
1043 MySqlCommand sqlCmd = null;
1044 lock (database)
1045 { 869 {
1046 try 870 try
1047 { 871 {
1048 database.CheckConnection(); 872 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
1049 sqlCmd = new MySqlCommand(
1050 "SELECT * FROM inventoryitems WHERE avatarId = ?uuid AND assetType = ?type and flags = 1",
1051 database.Connection);
1052 sqlCmd.Parameters.AddWithValue("?uuid", avatarID.ToString());
1053 sqlCmd.Parameters.AddWithValue("?type", (int)AssetType.Gesture);
1054 result = sqlCmd.ExecuteReader();
1055
1056 List<InventoryItemBase> list = new List<InventoryItemBase>();
1057 while (result.Read())
1058 { 873 {
1059 InventoryItemBase item = readInventoryItem(result); 874 dbcon.Open();
1060 if (item != null) 875
1061 list.Add(item); 876 using (MySqlCommand sqlCmd = new MySqlCommand(
877 "SELECT * FROM inventoryitems WHERE avatarId = ?uuid AND assetType = ?type and flags = 1", dbcon))
878 {
879 sqlCmd.Parameters.AddWithValue("?uuid", avatarID.ToString());
880 sqlCmd.Parameters.AddWithValue("?type", (int)AssetType.Gesture);
881
882 using (MySqlDataReader result = sqlCmd.ExecuteReader())
883 {
884 List<InventoryItemBase> list = new List<InventoryItemBase>();
885 while (result.Read())
886 {
887 InventoryItemBase item = readInventoryItem(result);
888 if (item != null)
889 list.Add(item);
890 }
891 return list;
892 }
893 }
1062 } 894 }
1063 return list;
1064 } 895 }
1065 catch (Exception e) 896 catch (Exception e)
1066 { 897 {
1067 database.Reconnect(); 898 m_log.Error(e.Message, e);
1068 m_log.Error(e.ToString());
1069 return null; 899 return null;
1070 } 900 }
1071 finally
1072 {
1073 if (result != null) result.Close();
1074 if (sqlCmd != null) sqlCmd.Dispose();
1075 }
1076 } 901 }
1077 } 902 }
1078 } 903 }
diff --git a/OpenSim/Data/MySQL/MySQLLegacyRegionData.cs b/OpenSim/Data/MySQL/MySQLLegacyRegionData.cs
index 9a4a4bb..a06eec3 100644
--- a/OpenSim/Data/MySQL/MySQLLegacyRegionData.cs
+++ b/OpenSim/Data/MySQL/MySQLLegacyRegionData.cs
@@ -48,75 +48,54 @@ namespace OpenSim.Data.MySQL
48 { 48 {
49 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 49 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
50 50
51 private string m_ConnectionString; 51 private string m_connectionString;
52 52 private object m_dbLock = new object();
53 private MySqlConnection m_Connection = null;
54 53
55 public void Initialise(string connectionString) 54 public void Initialise(string connectionString)
56 { 55 {
57 m_ConnectionString = connectionString; 56 m_connectionString = connectionString;
58 57
59 m_Connection = new MySqlConnection(m_ConnectionString); 58 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
59 {
60 dbcon.Open();
60 61
61 m_Connection.Open(); 62 // Apply new Migrations
63 //
64 Assembly assem = GetType().Assembly;
65 Migration m = new Migration(dbcon, assem, "RegionStore");
66 m.Update();
62 67
63 // Apply new Migrations 68 // Clean dropped attachments
64 // 69 //
65 Assembly assem = GetType().Assembly; 70 try
66 Migration m = new Migration(m_Connection, assem, "RegionStore"); 71 {
67 m.Update(); 72 using (MySqlCommand cmd = dbcon.CreateCommand())
68 73 {
69 // NOTE: This is a very slow query that times out on regions with a lot of prims. 74 cmd.CommandText = "delete from prims, primshapes using prims " +
70 // I'm told that it is no longer relevant so it's commented out now, but if it 75 "left join primshapes on prims.uuid = primshapes.uuid " +
71 // is relevant it should be added as a console command instead of part of the 76 "where PCode = 9 and State <> 0";
72 // startup phase 77 ExecuteNonQuery(cmd);
73 // Clean dropped attachments 78 }
74 // 79 }
75 //try 80 catch (MySqlException ex)
76 //{ 81 {
77 // using (MySqlCommand cmd = m_Connection.CreateCommand()) 82 m_log.Error("[REGION DB]: Error cleaning up dropped attachments: " + ex.Message);
78 // { 83 }
79 // cmd.CommandText = "delete from prims, primshapes using prims " + 84 }
80 // "left join primshapes on prims.uuid = primshapes.uuid " +
81 // "where PCode = 9 and State <> 0";
82 // ExecuteNonQuery(cmd);
83 // }
84 //}
85 //catch (MySqlException ex)
86 //{
87 // m_log.Error("[REGION DB]: Error cleaning up dropped attachments: " + ex.Message);
88 //}
89 } 85 }
90 86
91 private IDataReader ExecuteReader(MySqlCommand c) 87 private IDataReader ExecuteReader(MySqlCommand c)
92 { 88 {
93 IDataReader r = null; 89 IDataReader r = null;
94 bool errorSeen = false;
95 90
96 while (true) 91 try
97 { 92 {
98 try 93 r = c.ExecuteReader();
99 { 94 }
100 r = c.ExecuteReader(); 95 catch (Exception e)
101 } 96 {
102 catch (Exception) 97 m_log.Error("[REGION DB]: MySQL error in ExecuteReader: " + e.Message);
103 { 98 throw;
104 Thread.Sleep(500);
105
106 m_Connection.Close();
107 m_Connection = (MySqlConnection) ((ICloneable)m_Connection).Clone();
108 m_Connection.Open();
109 c.Connection = m_Connection;
110
111 if (!errorSeen)
112 {
113 errorSeen = true;
114 continue;
115 }
116 throw;
117 }
118
119 break;
120 } 99 }
121 100
122 return r; 101 return r;
@@ -124,32 +103,14 @@ namespace OpenSim.Data.MySQL
124 103
125 private void ExecuteNonQuery(MySqlCommand c) 104 private void ExecuteNonQuery(MySqlCommand c)
126 { 105 {
127 bool errorSeen = false; 106 try
128
129 while (true)
130 { 107 {
131 try 108 c.ExecuteNonQuery();
132 { 109 }
133 c.ExecuteNonQuery(); 110 catch (Exception e)
134 } 111 {
135 catch (Exception) 112 m_log.Error("[REGION DB]: MySQL error in ExecuteNonQuery: " + e.Message);
136 { 113 throw;
137 Thread.Sleep(500);
138
139 m_Connection.Close();
140 m_Connection = (MySqlConnection) ((ICloneable)m_Connection).Clone();
141 m_Connection.Open();
142 c.Connection = m_Connection;
143
144 if (!errorSeen)
145 {
146 errorSeen = true;
147 continue;
148 }
149 throw;
150 }
151
152 break;
153 } 114 }
154 } 115 }
155 116
@@ -166,115 +127,119 @@ namespace OpenSim.Data.MySQL
166 if ((flags & (uint)PrimFlags.TemporaryOnRez) != 0) 127 if ((flags & (uint)PrimFlags.TemporaryOnRez) != 0)
167 return; 128 return;
168 129
169 lock (m_Connection) 130 lock (m_dbLock)
170 { 131 {
171 MySqlCommand cmd = m_Connection.CreateCommand(); 132 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
172
173 foreach (SceneObjectPart prim in obj.Children.Values)
174 { 133 {
175 cmd.Parameters.Clear(); 134 dbcon.Open();
176 135 MySqlCommand cmd = dbcon.CreateCommand();
177 cmd.CommandText = "replace into prims ("+ 136
178 "UUID, CreationDate, "+ 137 foreach (SceneObjectPart prim in obj.Children.Values)
179 "Name, Text, Description, "+ 138 {
180 "SitName, TouchName, ObjectFlags, "+ 139 cmd.Parameters.Clear();
181 "OwnerMask, NextOwnerMask, GroupMask, "+ 140
182 "EveryoneMask, BaseMask, PositionX, "+ 141 cmd.CommandText = "replace into prims (" +
183 "PositionY, PositionZ, GroupPositionX, "+ 142 "UUID, CreationDate, " +
184 "GroupPositionY, GroupPositionZ, VelocityX, "+ 143 "Name, Text, Description, " +
185 "VelocityY, VelocityZ, AngularVelocityX, "+ 144 "SitName, TouchName, ObjectFlags, " +
186 "AngularVelocityY, AngularVelocityZ, "+ 145 "OwnerMask, NextOwnerMask, GroupMask, " +
187 "AccelerationX, AccelerationY, "+ 146 "EveryoneMask, BaseMask, PositionX, " +
188 "AccelerationZ, RotationX, "+ 147 "PositionY, PositionZ, GroupPositionX, " +
189 "RotationY, RotationZ, "+ 148 "GroupPositionY, GroupPositionZ, VelocityX, " +
190 "RotationW, SitTargetOffsetX, "+ 149 "VelocityY, VelocityZ, AngularVelocityX, " +
191 "SitTargetOffsetY, SitTargetOffsetZ, "+ 150 "AngularVelocityY, AngularVelocityZ, " +
192 "SitTargetOrientW, SitTargetOrientX, "+ 151 "AccelerationX, AccelerationY, " +
193 "SitTargetOrientY, SitTargetOrientZ, "+ 152 "AccelerationZ, RotationX, " +
194 "RegionUUID, CreatorID, "+ 153 "RotationY, RotationZ, " +
195 "OwnerID, GroupID, "+ 154 "RotationW, SitTargetOffsetX, " +
196 "LastOwnerID, SceneGroupID, "+ 155 "SitTargetOffsetY, SitTargetOffsetZ, " +
197 "PayPrice, PayButton1, "+ 156 "SitTargetOrientW, SitTargetOrientX, " +
198 "PayButton2, PayButton3, "+ 157 "SitTargetOrientY, SitTargetOrientZ, " +
199 "PayButton4, LoopedSound, "+ 158 "RegionUUID, CreatorID, " +
200 "LoopedSoundGain, TextureAnimation, "+ 159 "OwnerID, GroupID, " +
201 "OmegaX, OmegaY, OmegaZ, "+ 160 "LastOwnerID, SceneGroupID, " +
202 "CameraEyeOffsetX, CameraEyeOffsetY, "+ 161 "PayPrice, PayButton1, " +
203 "CameraEyeOffsetZ, CameraAtOffsetX, "+ 162 "PayButton2, PayButton3, " +
204 "CameraAtOffsetY, CameraAtOffsetZ, "+ 163 "PayButton4, LoopedSound, " +
205 "ForceMouselook, ScriptAccessPin, "+ 164 "LoopedSoundGain, TextureAnimation, " +
206 "AllowedDrop, DieAtEdge, "+ 165 "OmegaX, OmegaY, OmegaZ, " +
207 "SalePrice, SaleType, "+ 166 "CameraEyeOffsetX, CameraEyeOffsetY, " +
208 "ColorR, ColorG, ColorB, ColorA, "+ 167 "CameraEyeOffsetZ, CameraAtOffsetX, " +
209 "ParticleSystem, ClickAction, Material, "+ 168 "CameraAtOffsetY, CameraAtOffsetZ, " +
210 "CollisionSound, CollisionSoundVolume, "+ 169 "ForceMouselook, ScriptAccessPin, " +
211 "PassTouches, "+ 170 "AllowedDrop, DieAtEdge, " +
212 "LinkNumber) values (" + "?UUID, "+ 171 "SalePrice, SaleType, " +
213 "?CreationDate, ?Name, ?Text, "+ 172 "ColorR, ColorG, ColorB, ColorA, " +
214 "?Description, ?SitName, ?TouchName, "+ 173 "ParticleSystem, ClickAction, Material, " +
215 "?ObjectFlags, ?OwnerMask, ?NextOwnerMask, "+ 174 "CollisionSound, CollisionSoundVolume, " +
216 "?GroupMask, ?EveryoneMask, ?BaseMask, "+ 175 "PassTouches, " +
217 "?PositionX, ?PositionY, ?PositionZ, "+ 176 "LinkNumber) values (" + "?UUID, " +
218 "?GroupPositionX, ?GroupPositionY, "+ 177 "?CreationDate, ?Name, ?Text, " +
219 "?GroupPositionZ, ?VelocityX, "+ 178 "?Description, ?SitName, ?TouchName, " +
220 "?VelocityY, ?VelocityZ, ?AngularVelocityX, "+ 179 "?ObjectFlags, ?OwnerMask, ?NextOwnerMask, " +
221 "?AngularVelocityY, ?AngularVelocityZ, "+ 180 "?GroupMask, ?EveryoneMask, ?BaseMask, " +
222 "?AccelerationX, ?AccelerationY, "+ 181 "?PositionX, ?PositionY, ?PositionZ, " +
223 "?AccelerationZ, ?RotationX, "+ 182 "?GroupPositionX, ?GroupPositionY, " +
224 "?RotationY, ?RotationZ, "+ 183 "?GroupPositionZ, ?VelocityX, " +
225 "?RotationW, ?SitTargetOffsetX, "+ 184 "?VelocityY, ?VelocityZ, ?AngularVelocityX, " +
226 "?SitTargetOffsetY, ?SitTargetOffsetZ, "+ 185 "?AngularVelocityY, ?AngularVelocityZ, " +
227 "?SitTargetOrientW, ?SitTargetOrientX, "+ 186 "?AccelerationX, ?AccelerationY, " +
228 "?SitTargetOrientY, ?SitTargetOrientZ, "+ 187 "?AccelerationZ, ?RotationX, " +
229 "?RegionUUID, ?CreatorID, ?OwnerID, "+ 188 "?RotationY, ?RotationZ, " +
230 "?GroupID, ?LastOwnerID, ?SceneGroupID, "+ 189 "?RotationW, ?SitTargetOffsetX, " +
231 "?PayPrice, ?PayButton1, ?PayButton2, "+ 190 "?SitTargetOffsetY, ?SitTargetOffsetZ, " +
232 "?PayButton3, ?PayButton4, ?LoopedSound, "+ 191 "?SitTargetOrientW, ?SitTargetOrientX, " +
233 "?LoopedSoundGain, ?TextureAnimation, "+ 192 "?SitTargetOrientY, ?SitTargetOrientZ, " +
234 "?OmegaX, ?OmegaY, ?OmegaZ, "+ 193 "?RegionUUID, ?CreatorID, ?OwnerID, " +
235 "?CameraEyeOffsetX, ?CameraEyeOffsetY, "+ 194 "?GroupID, ?LastOwnerID, ?SceneGroupID, " +
236 "?CameraEyeOffsetZ, ?CameraAtOffsetX, "+ 195 "?PayPrice, ?PayButton1, ?PayButton2, " +
237 "?CameraAtOffsetY, ?CameraAtOffsetZ, "+ 196 "?PayButton3, ?PayButton4, ?LoopedSound, " +
238 "?ForceMouselook, ?ScriptAccessPin, "+ 197 "?LoopedSoundGain, ?TextureAnimation, " +
239 "?AllowedDrop, ?DieAtEdge, ?SalePrice, "+ 198 "?OmegaX, ?OmegaY, ?OmegaZ, " +
240 "?SaleType, ?ColorR, ?ColorG, "+ 199 "?CameraEyeOffsetX, ?CameraEyeOffsetY, " +
241 "?ColorB, ?ColorA, ?ParticleSystem, "+ 200 "?CameraEyeOffsetZ, ?CameraAtOffsetX, " +
242 "?ClickAction, ?Material, ?CollisionSound, "+ 201 "?CameraAtOffsetY, ?CameraAtOffsetZ, " +
243 "?CollisionSoundVolume, ?PassTouches, ?LinkNumber)"; 202 "?ForceMouselook, ?ScriptAccessPin, " +
244 203 "?AllowedDrop, ?DieAtEdge, ?SalePrice, " +
245 FillPrimCommand(cmd, prim, obj.UUID, regionUUID); 204 "?SaleType, ?ColorR, ?ColorG, " +
246 205 "?ColorB, ?ColorA, ?ParticleSystem, " +
247 ExecuteNonQuery(cmd); 206 "?ClickAction, ?Material, ?CollisionSound, " +
248 207 "?CollisionSoundVolume, ?PassTouches, ?LinkNumber)";
249 cmd.Parameters.Clear(); 208
250 209 FillPrimCommand(cmd, prim, obj.UUID, regionUUID);
251 cmd.CommandText = "replace into primshapes ("+ 210
252 "UUID, Shape, ScaleX, ScaleY, "+ 211 ExecuteNonQuery(cmd);
253 "ScaleZ, PCode, PathBegin, PathEnd, "+ 212
254 "PathScaleX, PathScaleY, PathShearX, "+ 213 cmd.Parameters.Clear();
255 "PathShearY, PathSkew, PathCurve, "+ 214
256 "PathRadiusOffset, PathRevolutions, "+ 215 cmd.CommandText = "replace into primshapes (" +
257 "PathTaperX, PathTaperY, PathTwist, "+ 216 "UUID, Shape, ScaleX, ScaleY, " +
258 "PathTwistBegin, ProfileBegin, ProfileEnd, "+ 217 "ScaleZ, PCode, PathBegin, PathEnd, " +
259 "ProfileCurve, ProfileHollow, Texture, "+ 218 "PathScaleX, PathScaleY, PathShearX, " +
260 "ExtraParams, State) values (?UUID, "+ 219 "PathShearY, PathSkew, PathCurve, " +
261 "?Shape, ?ScaleX, ?ScaleY, ?ScaleZ, "+ 220 "PathRadiusOffset, PathRevolutions, " +
262 "?PCode, ?PathBegin, ?PathEnd, "+ 221 "PathTaperX, PathTaperY, PathTwist, " +
263 "?PathScaleX, ?PathScaleY, "+ 222 "PathTwistBegin, ProfileBegin, ProfileEnd, " +
264 "?PathShearX, ?PathShearY, "+ 223 "ProfileCurve, ProfileHollow, Texture, " +
265 "?PathSkew, ?PathCurve, ?PathRadiusOffset, "+ 224 "ExtraParams, State) values (?UUID, " +
266 "?PathRevolutions, ?PathTaperX, "+ 225 "?Shape, ?ScaleX, ?ScaleY, ?ScaleZ, " +
267 "?PathTaperY, ?PathTwist, "+ 226 "?PCode, ?PathBegin, ?PathEnd, " +
268 "?PathTwistBegin, ?ProfileBegin, "+ 227 "?PathScaleX, ?PathScaleY, " +
269 "?ProfileEnd, ?ProfileCurve, "+ 228 "?PathShearX, ?PathShearY, " +
270 "?ProfileHollow, ?Texture, ?ExtraParams, "+ 229 "?PathSkew, ?PathCurve, ?PathRadiusOffset, " +
271 "?State)"; 230 "?PathRevolutions, ?PathTaperX, " +
272 231 "?PathTaperY, ?PathTwist, " +
273 FillShapeCommand(cmd, prim); 232 "?PathTwistBegin, ?ProfileBegin, " +
274 233 "?ProfileEnd, ?ProfileCurve, " +
275 ExecuteNonQuery(cmd); 234 "?ProfileHollow, ?Texture, ?ExtraParams, " +
235 "?State)";
236
237 FillShapeCommand(cmd, prim);
238
239 ExecuteNonQuery(cmd);
240 }
241 cmd.Dispose();
276 } 242 }
277 cmd.Dispose();
278 } 243 }
279 } 244 }
280 245
@@ -290,22 +255,27 @@ namespace OpenSim.Data.MySQL
290 // cause the loss of a prim, but is cleaner. 255 // cause the loss of a prim, but is cleaner.
291 // It's also faster because it uses the primary key. 256 // It's also faster because it uses the primary key.
292 // 257 //
293 lock (m_Connection) 258 lock (m_dbLock)
294 { 259 {
295 using (MySqlCommand cmd = m_Connection.CreateCommand()) 260 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
296 { 261 {
297 cmd.CommandText = "select UUID from prims where SceneGroupID= ?UUID"; 262 dbcon.Open();
298 cmd.Parameters.AddWithValue("UUID", obj.ToString());
299 263
300 using (IDataReader reader = ExecuteReader(cmd)) 264 using (MySqlCommand cmd = dbcon.CreateCommand())
301 { 265 {
302 while (reader.Read()) 266 cmd.CommandText = "select UUID from prims where SceneGroupID= ?UUID";
303 uuids.Add(new UUID(reader["UUID"].ToString())); 267 cmd.Parameters.AddWithValue("UUID", obj.ToString());
304 } 268
269 using (IDataReader reader = ExecuteReader(cmd))
270 {
271 while (reader.Read())
272 uuids.Add(new UUID(reader["UUID"].ToString()));
273 }
305 274
306 // delete the main prims 275 // delete the main prims
307 cmd.CommandText = "delete from prims where SceneGroupID= ?UUID"; 276 cmd.CommandText = "delete from prims where SceneGroupID= ?UUID";
308 ExecuteNonQuery(cmd); 277 ExecuteNonQuery(cmd);
278 }
309 } 279 }
310 } 280 }
311 281
@@ -326,14 +296,19 @@ namespace OpenSim.Data.MySQL
326 /// <param name="uuid">the Item UUID</param> 296 /// <param name="uuid">the Item UUID</param>
327 private void RemoveItems(UUID uuid) 297 private void RemoveItems(UUID uuid)
328 { 298 {
329 lock (m_Connection) 299 lock (m_dbLock)
330 { 300 {
331 using (MySqlCommand cmd = m_Connection.CreateCommand()) 301 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
332 { 302 {
333 cmd.CommandText = "delete from primitems where PrimID = ?PrimID"; 303 dbcon.Open();
334 cmd.Parameters.AddWithValue("PrimID", uuid.ToString()); 304
305 using (MySqlCommand cmd = dbcon.CreateCommand())
306 {
307 cmd.CommandText = "delete from primitems where PrimID = ?PrimID";
308 cmd.Parameters.AddWithValue("PrimID", uuid.ToString());
335 309
336 ExecuteNonQuery(cmd); 310 ExecuteNonQuery(cmd);
311 }
337 } 312 }
338 } 313 }
339 } 314 }
@@ -345,29 +320,33 @@ namespace OpenSim.Data.MySQL
345 /// <param name="uuids">the list of UUIDs</param> 320 /// <param name="uuids">the list of UUIDs</param>
346 private void RemoveShapes(List<UUID> uuids) 321 private void RemoveShapes(List<UUID> uuids)
347 { 322 {
348 lock (m_Connection) 323 lock (m_dbLock)
349 { 324 {
350 string sql = "delete from primshapes where "; 325 string sql = "delete from primshapes where ";
351 326 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
352 using (MySqlCommand cmd = m_Connection.CreateCommand())
353 { 327 {
354 for (int i = 0; i < uuids.Count; i++) 328 dbcon.Open();
329
330 using (MySqlCommand cmd = dbcon.CreateCommand())
355 { 331 {
356 if ((i + 1) == uuids.Count) 332 for (int i = 0; i < uuids.Count; i++)
357 {// end of the list
358 sql += "(UUID = ?UUID" + i + ")";
359 }
360 else
361 { 333 {
362 sql += "(UUID = ?UUID" + i + ") or "; 334 if ((i + 1) == uuids.Count)
335 {// end of the list
336 sql += "(UUID = ?UUID" + i + ")";
337 }
338 else
339 {
340 sql += "(UUID = ?UUID" + i + ") or ";
341 }
363 } 342 }
364 } 343 cmd.CommandText = sql;
365 cmd.CommandText = sql;
366 344
367 for (int i = 0; i < uuids.Count; i++) 345 for (int i = 0; i < uuids.Count; i++)
368 cmd.Parameters.AddWithValue("UUID" + i, uuids[i].ToString()); 346 cmd.Parameters.AddWithValue("UUID" + i, uuids[i].ToString());
369 347
370 ExecuteNonQuery(cmd); 348 ExecuteNonQuery(cmd);
349 }
371 } 350 }
372 } 351 }
373 } 352 }
@@ -379,30 +358,34 @@ namespace OpenSim.Data.MySQL
379 /// <param name="uuids">the list of UUIDs</param> 358 /// <param name="uuids">the list of UUIDs</param>
380 private void RemoveItems(List<UUID> uuids) 359 private void RemoveItems(List<UUID> uuids)
381 { 360 {
382 lock (m_Connection) 361 lock (m_dbLock)
383 { 362 {
384 string sql = "delete from primitems where "; 363 string sql = "delete from primitems where ";
385 364 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
386 using (MySqlCommand cmd = m_Connection.CreateCommand())
387 { 365 {
388 for (int i = 0; i < uuids.Count; i++) 366 dbcon.Open();
367
368 using (MySqlCommand cmd = dbcon.CreateCommand())
389 { 369 {
390 if ((i + 1) == uuids.Count) 370 for (int i = 0; i < uuids.Count; i++)
391 { 371 {
392 // end of the list 372 if ((i + 1) == uuids.Count)
393 sql += "(PrimID = ?PrimID" + i + ")"; 373 {
394 } 374 // end of the list
395 else 375 sql += "(PrimID = ?PrimID" + i + ")";
396 { 376 }
397 sql += "(PrimID = ?PrimID" + i + ") or "; 377 else
378 {
379 sql += "(PrimID = ?PrimID" + i + ") or ";
380 }
398 } 381 }
399 } 382 cmd.CommandText = sql;
400 cmd.CommandText = sql;
401 383
402 for (int i = 0; i < uuids.Count; i++) 384 for (int i = 0; i < uuids.Count; i++)
403 cmd.Parameters.AddWithValue("PrimID" + i, uuids[i].ToString()); 385 cmd.Parameters.AddWithValue("PrimID" + i, uuids[i].ToString());
404 386
405 ExecuteNonQuery(cmd); 387 ExecuteNonQuery(cmd);
388 }
406 } 389 }
407 } 390 }
408 } 391 }
@@ -417,33 +400,38 @@ namespace OpenSim.Data.MySQL
417 400
418 #region Prim Loading 401 #region Prim Loading
419 402
420 lock (m_Connection) 403 lock (m_dbLock)
421 { 404 {
422 using (MySqlCommand cmd = m_Connection.CreateCommand()) 405 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
423 { 406 {
424 cmd.CommandText = 407 dbcon.Open();
425 "SELECT * FROM prims LEFT JOIN primshapes ON prims.UUID = primshapes.UUID WHERE RegionUUID = ?RegionUUID";
426 cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString());
427 408
428 using (IDataReader reader = ExecuteReader(cmd)) 409 using (MySqlCommand cmd = dbcon.CreateCommand())
429 { 410 {
430 while (reader.Read()) 411 cmd.CommandText =
412 "SELECT * FROM prims LEFT JOIN primshapes ON prims.UUID = primshapes.UUID WHERE RegionUUID = ?RegionUUID";
413 cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString());
414
415 using (IDataReader reader = ExecuteReader(cmd))
431 { 416 {
432 SceneObjectPart prim = BuildPrim(reader); 417 while (reader.Read())
433 if (reader["Shape"] is DBNull) 418 {
434 prim.Shape = PrimitiveBaseShape.Default; 419 SceneObjectPart prim = BuildPrim(reader);
435 else 420 if (reader["Shape"] is DBNull)
436 prim.Shape = BuildShape(reader); 421 prim.Shape = PrimitiveBaseShape.Default;
422 else
423 prim.Shape = BuildShape(reader);
437 424
438 UUID parentID = new UUID(reader["SceneGroupID"].ToString()); 425 UUID parentID = new UUID(reader["SceneGroupID"].ToString());
439 if (parentID != prim.UUID) 426 if (parentID != prim.UUID)
440 prim.ParentUUID = parentID; 427 prim.ParentUUID = parentID;
441 428
442 prims[prim.UUID] = prim; 429 prims[prim.UUID] = prim;
443 430
444 ++count; 431 ++count;
445 if (count % ROWS_PER_QUERY == 0) 432 if (count % ROWS_PER_QUERY == 0)
446 m_log.Debug("[REGION DB]: Loaded " + count + " prims..."); 433 m_log.Debug("[REGION DB]: Loaded " + count + " prims...");
434 }
447 } 435 }
448 } 436 }
449 } 437 }
@@ -497,20 +485,25 @@ namespace OpenSim.Data.MySQL
497 // list from DB of all prims which have items and 485 // list from DB of all prims which have items and
498 // LoadItems only on those 486 // LoadItems only on those
499 List<SceneObjectPart> primsWithInventory = new List<SceneObjectPart>(); 487 List<SceneObjectPart> primsWithInventory = new List<SceneObjectPart>();
500 lock (m_Connection) 488 lock (m_dbLock)
501 { 489 {
502 using (MySqlCommand itemCmd = m_Connection.CreateCommand()) 490 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
503 { 491 {
504 itemCmd.CommandText = "SELECT DISTINCT primID FROM primitems"; 492 dbcon.Open();
505 using (IDataReader itemReader = ExecuteReader(itemCmd)) 493
494 using (MySqlCommand itemCmd = dbcon.CreateCommand())
506 { 495 {
507 while (itemReader.Read()) 496 itemCmd.CommandText = "SELECT DISTINCT primID FROM primitems";
497 using (IDataReader itemReader = ExecuteReader(itemCmd))
508 { 498 {
509 if (!(itemReader["primID"] is DBNull)) 499 while (itemReader.Read())
510 { 500 {
511 UUID primID = new UUID(itemReader["primID"].ToString()); 501 if (!(itemReader["primID"] is DBNull))
512 if (prims.ContainsKey(primID)) 502 {
513 primsWithInventory.Add(prims[primID]); 503 UUID primID = new UUID(itemReader["primID"].ToString());
504 if (prims.ContainsKey(primID))
505 primsWithInventory.Add(prims[primID]);
506 }
514 } 507 }
515 } 508 }
516 } 509 }
@@ -535,23 +528,28 @@ namespace OpenSim.Data.MySQL
535 /// <param name="prim">The prim</param> 528 /// <param name="prim">The prim</param>
536 private void LoadItems(SceneObjectPart prim) 529 private void LoadItems(SceneObjectPart prim)
537 { 530 {
538 lock (m_Connection) 531 lock (m_dbLock)
539 { 532 {
540 List<TaskInventoryItem> inventory = new List<TaskInventoryItem>(); 533 List<TaskInventoryItem> inventory = new List<TaskInventoryItem>();
541 534
542 using (MySqlCommand cmd = m_Connection.CreateCommand()) 535 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
543 { 536 {
544 cmd.CommandText = "select * from primitems where PrimID = ?PrimID"; 537 dbcon.Open();
545 cmd.Parameters.AddWithValue("PrimID", prim.UUID.ToString());
546 538
547 using (IDataReader reader = ExecuteReader(cmd)) 539 using (MySqlCommand cmd = dbcon.CreateCommand())
548 { 540 {
549 while (reader.Read()) 541 cmd.CommandText = "select * from primitems where PrimID = ?PrimID";
542 cmd.Parameters.AddWithValue("PrimID", prim.UUID.ToString());
543
544 using (IDataReader reader = ExecuteReader(cmd))
550 { 545 {
551 TaskInventoryItem item = BuildItem(reader); 546 while (reader.Read())
547 {
548 TaskInventoryItem item = BuildItem(reader);
552 549
553 item.ParentID = prim.UUID; // Values in database are often wrong 550 item.ParentID = prim.UUID; // Values in database are often wrong
554 inventory.Add(item); 551 inventory.Add(item);
552 }
555 } 553 }
556 } 554 }
557 } 555 }
@@ -564,22 +562,27 @@ namespace OpenSim.Data.MySQL
564 { 562 {
565 m_log.Info("[REGION DB]: Storing terrain"); 563 m_log.Info("[REGION DB]: Storing terrain");
566 564
567 lock (m_Connection) 565 lock (m_dbLock)
568 { 566 {
569 using (MySqlCommand cmd = m_Connection.CreateCommand()) 567 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
570 { 568 {
571 cmd.CommandText = "delete from terrain where RegionUUID = ?RegionUUID"; 569 dbcon.Open();
572 cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString());
573 570
574 ExecuteNonQuery(cmd); 571 using (MySqlCommand cmd = dbcon.CreateCommand())
572 {
573 cmd.CommandText = "delete from terrain where RegionUUID = ?RegionUUID";
574 cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString());
575 575
576 cmd.CommandText = "insert into terrain (RegionUUID, " + 576 ExecuteNonQuery(cmd);
577 "Revision, Heightfield) values (?RegionUUID, " +
578 "1, ?Heightfield)";
579 577
580 cmd.Parameters.AddWithValue("Heightfield", SerializeTerrain(ter)); 578 cmd.CommandText = "insert into terrain (RegionUUID, " +
579 "Revision, Heightfield) values (?RegionUUID, " +
580 "1, ?Heightfield)";
581 581
582 ExecuteNonQuery(cmd); 582 cmd.Parameters.AddWithValue("Heightfield", SerializeTerrain(ter));
583
584 ExecuteNonQuery(cmd);
585 }
583 } 586 }
584 } 587 }
585 } 588 }
@@ -588,38 +591,43 @@ namespace OpenSim.Data.MySQL
588 { 591 {
589 double[,] terrain = null; 592 double[,] terrain = null;
590 593
591 lock (m_Connection) 594 lock (m_dbLock)
592 { 595 {
593 using (MySqlCommand cmd = m_Connection.CreateCommand()) 596 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
594 { 597 {
595 cmd.CommandText = "select RegionUUID, Revision, Heightfield " + 598 dbcon.Open();
596 "from terrain where RegionUUID = ?RegionUUID " +
597 "order by Revision desc limit 1";
598 cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString());
599 599
600 using (IDataReader reader = ExecuteReader(cmd)) 600 using (MySqlCommand cmd = dbcon.CreateCommand())
601 { 601 {
602 while (reader.Read()) 602 cmd.CommandText = "select RegionUUID, Revision, Heightfield " +
603 "from terrain where RegionUUID = ?RegionUUID " +
604 "order by Revision desc limit 1";
605 cmd.Parameters.AddWithValue("RegionUUID", regionID.ToString());
606
607 using (IDataReader reader = ExecuteReader(cmd))
603 { 608 {
604 int rev = Convert.ToInt32(reader["Revision"]); 609 while (reader.Read())
610 {
611 int rev = Convert.ToInt32(reader["Revision"]);
605 612
606 terrain = new double[(int)Constants.RegionSize, (int)Constants.RegionSize]; 613 terrain = new double[(int)Constants.RegionSize, (int)Constants.RegionSize];
607 terrain.Initialize(); 614 terrain.Initialize();
608 615
609 using (MemoryStream mstr = new MemoryStream((byte[])reader["Heightfield"])) 616 using (MemoryStream mstr = new MemoryStream((byte[])reader["Heightfield"]))
610 {
611 using (BinaryReader br = new BinaryReader(mstr))
612 { 617 {
613 for (int x = 0; x < (int)Constants.RegionSize; x++) 618 using (BinaryReader br = new BinaryReader(mstr))
614 { 619 {
615 for (int y = 0; y < (int)Constants.RegionSize; y++) 620 for (int x = 0; x < (int)Constants.RegionSize; x++)
616 { 621 {
617 terrain[x, y] = br.ReadDouble(); 622 for (int y = 0; y < (int)Constants.RegionSize; y++)
623 {
624 terrain[x, y] = br.ReadDouble();
625 }
618 } 626 }
619 } 627 }
620 }
621 628
622 m_log.InfoFormat("[REGION DB]: Loaded terrain revision r{0}", rev); 629 m_log.InfoFormat("[REGION DB]: Loaded terrain revision r{0}", rev);
630 }
623 } 631 }
624 } 632 }
625 } 633 }
@@ -631,63 +639,73 @@ namespace OpenSim.Data.MySQL
631 639
632 public void RemoveLandObject(UUID globalID) 640 public void RemoveLandObject(UUID globalID)
633 { 641 {
634 lock (m_Connection) 642 lock (m_dbLock)
635 { 643 {
636 using (MySqlCommand cmd = m_Connection.CreateCommand()) 644 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
637 { 645 {
638 cmd.CommandText = "delete from land where UUID = ?UUID"; 646 dbcon.Open();
639 cmd.Parameters.AddWithValue("UUID", globalID.ToString()); 647
648 using (MySqlCommand cmd = dbcon.CreateCommand())
649 {
650 cmd.CommandText = "delete from land where UUID = ?UUID";
651 cmd.Parameters.AddWithValue("UUID", globalID.ToString());
640 652
641 ExecuteNonQuery(cmd); 653 ExecuteNonQuery(cmd);
654 }
642 } 655 }
643 } 656 }
644 } 657 }
645 658
646 public void StoreLandObject(ILandObject parcel) 659 public void StoreLandObject(ILandObject parcel)
647 { 660 {
648 lock (m_Connection) 661 lock (m_dbLock)
649 { 662 {
650 using (MySqlCommand cmd = m_Connection.CreateCommand()) 663 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
651 { 664 {
652 cmd.CommandText = "replace into land (UUID, RegionUUID, " + 665 dbcon.Open();
653 "LocalLandID, Bitmap, Name, Description, " + 666
654 "OwnerUUID, IsGroupOwned, Area, AuctionID, " + 667 using (MySqlCommand cmd = dbcon.CreateCommand())
655 "Category, ClaimDate, ClaimPrice, GroupUUID, " +
656 "SalePrice, LandStatus, LandFlags, LandingType, " +
657 "MediaAutoScale, MediaTextureUUID, MediaURL, " +
658 "MusicURL, PassHours, PassPrice, SnapshotUUID, " +
659 "UserLocationX, UserLocationY, UserLocationZ, " +
660 "UserLookAtX, UserLookAtY, UserLookAtZ, " +
661 "AuthbuyerID, OtherCleanTime, Dwell) values (" +
662 "?UUID, ?RegionUUID, " +
663 "?LocalLandID, ?Bitmap, ?Name, ?Description, " +
664 "?OwnerUUID, ?IsGroupOwned, ?Area, ?AuctionID, " +
665 "?Category, ?ClaimDate, ?ClaimPrice, ?GroupUUID, " +
666 "?SalePrice, ?LandStatus, ?LandFlags, ?LandingType, " +
667 "?MediaAutoScale, ?MediaTextureUUID, ?MediaURL, " +
668 "?MusicURL, ?PassHours, ?PassPrice, ?SnapshotUUID, " +
669 "?UserLocationX, ?UserLocationY, ?UserLocationZ, " +
670 "?UserLookAtX, ?UserLookAtY, ?UserLookAtZ, " +
671 "?AuthbuyerID, ?OtherCleanTime, ?Dwell)";
672
673 FillLandCommand(cmd, parcel.LandData, parcel.RegionUUID);
674
675 ExecuteNonQuery(cmd);
676
677 cmd.CommandText = "delete from landaccesslist where LandUUID = ?UUID";
678
679 ExecuteNonQuery(cmd);
680
681 cmd.Parameters.Clear();
682 cmd.CommandText = "insert into landaccesslist (LandUUID, " +
683 "AccessUUID, Flags) values (?LandUUID, ?AccessUUID, " +
684 "?Flags)";
685
686 foreach (ParcelManager.ParcelAccessEntry entry in parcel.LandData.ParcelAccessList)
687 { 668 {
688 FillLandAccessCommand(cmd, entry, parcel.LandData.GlobalID); 669 cmd.CommandText = "replace into land (UUID, RegionUUID, " +
670 "LocalLandID, Bitmap, Name, Description, " +
671 "OwnerUUID, IsGroupOwned, Area, AuctionID, " +
672 "Category, ClaimDate, ClaimPrice, GroupUUID, " +
673 "SalePrice, LandStatus, LandFlags, LandingType, " +
674 "MediaAutoScale, MediaTextureUUID, MediaURL, " +
675 "MusicURL, PassHours, PassPrice, SnapshotUUID, " +
676 "UserLocationX, UserLocationY, UserLocationZ, " +
677 "UserLookAtX, UserLookAtY, UserLookAtZ, " +
678 "AuthbuyerID, OtherCleanTime, Dwell) values (" +
679 "?UUID, ?RegionUUID, " +
680 "?LocalLandID, ?Bitmap, ?Name, ?Description, " +
681 "?OwnerUUID, ?IsGroupOwned, ?Area, ?AuctionID, " +
682 "?Category, ?ClaimDate, ?ClaimPrice, ?GroupUUID, " +
683 "?SalePrice, ?LandStatus, ?LandFlags, ?LandingType, " +
684 "?MediaAutoScale, ?MediaTextureUUID, ?MediaURL, " +
685 "?MusicURL, ?PassHours, ?PassPrice, ?SnapshotUUID, " +
686 "?UserLocationX, ?UserLocationY, ?UserLocationZ, " +
687 "?UserLookAtX, ?UserLookAtY, ?UserLookAtZ, " +
688 "?AuthbuyerID, ?OtherCleanTime, ?Dwell)";
689
690 FillLandCommand(cmd, parcel.LandData, parcel.RegionUUID);
691
689 ExecuteNonQuery(cmd); 692 ExecuteNonQuery(cmd);
693
694 cmd.CommandText = "delete from landaccesslist where LandUUID = ?UUID";
695
696 ExecuteNonQuery(cmd);
697
690 cmd.Parameters.Clear(); 698 cmd.Parameters.Clear();
699 cmd.CommandText = "insert into landaccesslist (LandUUID, " +
700 "AccessUUID, Flags) values (?LandUUID, ?AccessUUID, " +
701 "?Flags)";
702
703 foreach (ParcelManager.ParcelAccessEntry entry in parcel.LandData.ParcelAccessList)
704 {
705 FillLandAccessCommand(cmd, entry, parcel.LandData.GlobalID);
706 ExecuteNonQuery(cmd);
707 cmd.Parameters.Clear();
708 }
691 } 709 }
692 } 710 }
693 } 711 }
@@ -697,27 +715,32 @@ namespace OpenSim.Data.MySQL
697 { 715 {
698 RegionSettings rs = null; 716 RegionSettings rs = null;
699 717
700 lock (m_Connection) 718 lock (m_dbLock)
701 { 719 {
702 using (MySqlCommand cmd = m_Connection.CreateCommand()) 720 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
703 { 721 {
704 cmd.CommandText = "select * from regionsettings where regionUUID = ?RegionUUID"; 722 dbcon.Open();
705 cmd.Parameters.AddWithValue("regionUUID", regionUUID);
706 723
707 using (IDataReader reader = ExecuteReader(cmd)) 724 using (MySqlCommand cmd = dbcon.CreateCommand())
708 { 725 {
709 if (reader.Read()) 726 cmd.CommandText = "select * from regionsettings where regionUUID = ?RegionUUID";
710 { 727 cmd.Parameters.AddWithValue("regionUUID", regionUUID);
711 rs = BuildRegionSettings(reader); 728
712 rs.OnSave += StoreRegionSettings; 729 using (IDataReader reader = ExecuteReader(cmd))
713 }
714 else
715 { 730 {
716 rs = new RegionSettings(); 731 if (reader.Read())
717 rs.RegionUUID = regionUUID; 732 {
718 rs.OnSave += StoreRegionSettings; 733 rs = BuildRegionSettings(reader);
734 rs.OnSave += StoreRegionSettings;
735 }
736 else
737 {
738 rs = new RegionSettings();
739 rs.RegionUUID = regionUUID;
740 rs.OnSave += StoreRegionSettings;
719 741
720 StoreRegionSettings(rs); 742 StoreRegionSettings(rs);
743 }
721 } 744 }
722 } 745 }
723 } 746 }
@@ -728,46 +751,51 @@ namespace OpenSim.Data.MySQL
728 751
729 public void StoreRegionSettings(RegionSettings rs) 752 public void StoreRegionSettings(RegionSettings rs)
730 { 753 {
731 lock (m_Connection) 754 lock (m_dbLock)
732 { 755 {
733 using (MySqlCommand cmd = m_Connection.CreateCommand()) 756 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
734 { 757 {
735 cmd.CommandText = "replace into regionsettings (regionUUID, " + 758 dbcon.Open();
736 "block_terraform, block_fly, allow_damage, " + 759
737 "restrict_pushing, allow_land_resell, " + 760 using (MySqlCommand cmd = dbcon.CreateCommand())
738 "allow_land_join_divide, block_show_in_search, " + 761 {
739 "agent_limit, object_bonus, maturity, " + 762 cmd.CommandText = "replace into regionsettings (regionUUID, " +
740 "disable_scripts, disable_collisions, " + 763 "block_terraform, block_fly, allow_damage, " +
741 "disable_physics, terrain_texture_1, " + 764 "restrict_pushing, allow_land_resell, " +
742 "terrain_texture_2, terrain_texture_3, " + 765 "allow_land_join_divide, block_show_in_search, " +
743 "terrain_texture_4, elevation_1_nw, " + 766 "agent_limit, object_bonus, maturity, " +
744 "elevation_2_nw, elevation_1_ne, " + 767 "disable_scripts, disable_collisions, " +
745 "elevation_2_ne, elevation_1_se, " + 768 "disable_physics, terrain_texture_1, " +
746 "elevation_2_se, elevation_1_sw, " + 769 "terrain_texture_2, terrain_texture_3, " +
747 "elevation_2_sw, water_height, " + 770 "terrain_texture_4, elevation_1_nw, " +
748 "terrain_raise_limit, terrain_lower_limit, " + 771 "elevation_2_nw, elevation_1_ne, " +
749 "use_estate_sun, fixed_sun, sun_position, " + 772 "elevation_2_ne, elevation_1_se, " +
750 "covenant, Sandbox, sunvectorx, sunvectory, " + 773 "elevation_2_se, elevation_1_sw, " +
751 "sunvectorz, loaded_creation_datetime, " + 774 "elevation_2_sw, water_height, " +
752 "loaded_creation_id) values (?RegionUUID, ?BlockTerraform, " + 775 "terrain_raise_limit, terrain_lower_limit, " +
753 "?BlockFly, ?AllowDamage, ?RestrictPushing, " + 776 "use_estate_sun, fixed_sun, sun_position, " +
754 "?AllowLandResell, ?AllowLandJoinDivide, " + 777 "covenant, Sandbox, sunvectorx, sunvectory, " +
755 "?BlockShowInSearch, ?AgentLimit, ?ObjectBonus, " + 778 "sunvectorz, loaded_creation_datetime, " +
756 "?Maturity, ?DisableScripts, ?DisableCollisions, " + 779 "loaded_creation_id) values (?RegionUUID, ?BlockTerraform, " +
757 "?DisablePhysics, ?TerrainTexture1, " + 780 "?BlockFly, ?AllowDamage, ?RestrictPushing, " +
758 "?TerrainTexture2, ?TerrainTexture3, " + 781 "?AllowLandResell, ?AllowLandJoinDivide, " +
759 "?TerrainTexture4, ?Elevation1NW, ?Elevation2NW, " + 782 "?BlockShowInSearch, ?AgentLimit, ?ObjectBonus, " +
760 "?Elevation1NE, ?Elevation2NE, ?Elevation1SE, " + 783 "?Maturity, ?DisableScripts, ?DisableCollisions, " +
761 "?Elevation2SE, ?Elevation1SW, ?Elevation2SW, " + 784 "?DisablePhysics, ?TerrainTexture1, " +
762 "?WaterHeight, ?TerrainRaiseLimit, " + 785 "?TerrainTexture2, ?TerrainTexture3, " +
763 "?TerrainLowerLimit, ?UseEstateSun, ?FixedSun, " + 786 "?TerrainTexture4, ?Elevation1NW, ?Elevation2NW, " +
764 "?SunPosition, ?Covenant, ?Sandbox, " + 787 "?Elevation1NE, ?Elevation2NE, ?Elevation1SE, " +
765 "?SunVectorX, ?SunVectorY, ?SunVectorZ, " + 788 "?Elevation2SE, ?Elevation1SW, ?Elevation2SW, " +
766 "?LoadedCreationDateTime, ?LoadedCreationID)"; 789 "?WaterHeight, ?TerrainRaiseLimit, " +
767 790 "?TerrainLowerLimit, ?UseEstateSun, ?FixedSun, " +
768 FillRegionSettingsCommand(cmd, rs); 791 "?SunPosition, ?Covenant, ?Sandbox, " +
769 792 "?SunVectorX, ?SunVectorY, ?SunVectorZ, " +
770 ExecuteNonQuery(cmd); 793 "?LoadedCreationDateTime, ?LoadedCreationID)";
794
795 FillRegionSettingsCommand(cmd, rs);
796
797 ExecuteNonQuery(cmd);
798 }
771 } 799 }
772 } 800 }
773 } 801 }
@@ -776,36 +804,41 @@ namespace OpenSim.Data.MySQL
776 { 804 {
777 List<LandData> landData = new List<LandData>(); 805 List<LandData> landData = new List<LandData>();
778 806
779 lock (m_Connection) 807 lock (m_dbLock)
780 { 808 {
781 using (MySqlCommand cmd = m_Connection.CreateCommand()) 809 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
782 { 810 {
783 cmd.CommandText = "select * from land where RegionUUID = ?RegionUUID"; 811 dbcon.Open();
784 cmd.Parameters.AddWithValue("RegionUUID", regionUUID.ToString());
785 812
786 using (IDataReader reader = ExecuteReader(cmd)) 813 using (MySqlCommand cmd = dbcon.CreateCommand())
787 { 814 {
788 while (reader.Read()) 815 cmd.CommandText = "select * from land where RegionUUID = ?RegionUUID";
816 cmd.Parameters.AddWithValue("RegionUUID", regionUUID.ToString());
817
818 using (IDataReader reader = ExecuteReader(cmd))
789 { 819 {
790 LandData newLand = BuildLandData(reader); 820 while (reader.Read())
791 landData.Add(newLand); 821 {
822 LandData newLand = BuildLandData(reader);
823 landData.Add(newLand);
824 }
792 } 825 }
793 } 826 }
794 }
795 827
796 using (MySqlCommand cmd = m_Connection.CreateCommand()) 828 using (MySqlCommand cmd = dbcon.CreateCommand())
797 {
798 foreach (LandData land in landData)
799 { 829 {
800 cmd.Parameters.Clear(); 830 foreach (LandData land in landData)
801 cmd.CommandText = "select * from landaccesslist where LandUUID = ?LandUUID";
802 cmd.Parameters.AddWithValue("LandUUID", land.GlobalID.ToString());
803
804 using (IDataReader reader = ExecuteReader(cmd))
805 { 831 {
806 while (reader.Read()) 832 cmd.Parameters.Clear();
833 cmd.CommandText = "select * from landaccesslist where LandUUID = ?LandUUID";
834 cmd.Parameters.AddWithValue("LandUUID", land.GlobalID.ToString());
835
836 using (IDataReader reader = ExecuteReader(cmd))
807 { 837 {
808 land.ParcelAccessList.Add(BuildLandAccessData(reader)); 838 while (reader.Read())
839 {
840 land.ParcelAccessList.Add(BuildLandAccessData(reader));
841 }
809 } 842 }
810 } 843 }
811 } 844 }
@@ -1513,41 +1546,46 @@ namespace OpenSim.Data.MySQL
1513 1546
1514 public void StorePrimInventory(UUID primID, ICollection<TaskInventoryItem> items) 1547 public void StorePrimInventory(UUID primID, ICollection<TaskInventoryItem> items)
1515 { 1548 {
1516 lock (m_Connection) 1549 lock (m_dbLock)
1517 { 1550 {
1518 RemoveItems(primID); 1551 RemoveItems(primID);
1519 1552
1520 MySqlCommand cmd = m_Connection.CreateCommand(); 1553 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
1521
1522 if (items.Count == 0)
1523 return;
1524
1525 cmd.CommandText = "insert into primitems ("+
1526 "invType, assetType, name, "+
1527 "description, creationDate, nextPermissions, "+
1528 "currentPermissions, basePermissions, "+
1529 "everyonePermissions, groupPermissions, "+
1530 "flags, itemID, primID, assetID, "+
1531 "parentFolderID, creatorID, ownerID, "+
1532 "groupID, lastOwnerID) values (?invType, "+
1533 "?assetType, ?name, ?description, "+
1534 "?creationDate, ?nextPermissions, "+
1535 "?currentPermissions, ?basePermissions, "+
1536 "?everyonePermissions, ?groupPermissions, "+
1537 "?flags, ?itemID, ?primID, ?assetID, "+
1538 "?parentFolderID, ?creatorID, ?ownerID, "+
1539 "?groupID, ?lastOwnerID)";
1540
1541 foreach (TaskInventoryItem item in items)
1542 { 1554 {
1543 cmd.Parameters.Clear(); 1555 dbcon.Open();
1556
1557 MySqlCommand cmd = dbcon.CreateCommand();
1558
1559 if (items.Count == 0)
1560 return;
1561
1562 cmd.CommandText = "insert into primitems (" +
1563 "invType, assetType, name, " +
1564 "description, creationDate, nextPermissions, " +
1565 "currentPermissions, basePermissions, " +
1566 "everyonePermissions, groupPermissions, " +
1567 "flags, itemID, primID, assetID, " +
1568 "parentFolderID, creatorID, ownerID, " +
1569 "groupID, lastOwnerID) values (?invType, " +
1570 "?assetType, ?name, ?description, " +
1571 "?creationDate, ?nextPermissions, " +
1572 "?currentPermissions, ?basePermissions, " +
1573 "?everyonePermissions, ?groupPermissions, " +
1574 "?flags, ?itemID, ?primID, ?assetID, " +
1575 "?parentFolderID, ?creatorID, ?ownerID, " +
1576 "?groupID, ?lastOwnerID)";
1577
1578 foreach (TaskInventoryItem item in items)
1579 {
1580 cmd.Parameters.Clear();
1581
1582 FillItemCommand(cmd, item);
1544 1583
1545 FillItemCommand(cmd, item); 1584 ExecuteNonQuery(cmd);
1585 }
1546 1586
1547 ExecuteNonQuery(cmd); 1587 cmd.Dispose();
1548 } 1588 }
1549
1550 cmd.Dispose();
1551 } 1589 }
1552 } 1590 }
1553 } 1591 }
diff --git a/OpenSim/Data/MySQL/MySQLLogData.cs b/OpenSim/Data/MySQL/MySQLLogData.cs
index 8f67eeb..304883c 100644
--- a/OpenSim/Data/MySQL/MySQLLogData.cs
+++ b/OpenSim/Data/MySQL/MySQLLogData.cs
@@ -79,14 +79,19 @@ namespace OpenSim.Data.MySQL
79 79
80 // This actually does the roll forward assembly stuff 80 // This actually does the roll forward assembly stuff
81 Assembly assem = GetType().Assembly; 81 Assembly assem = GetType().Assembly;
82 Migration m = new Migration(database.Connection, assem, "LogStore");
83 82
84 // TODO: After rev 6000, remove this. People should have 83 using (MySql.Data.MySqlClient.MySqlConnection dbcon = new MySql.Data.MySqlClient.MySqlConnection(connect))
85 // been rolled onto the new migration code by then. 84 {
86 TestTables(m); 85 dbcon.Open();
86
87 Migration m = new Migration(dbcon, assem, "LogStore");
87 88
88 m.Update(); 89 // TODO: After rev 6000, remove this. People should have
90 // been rolled onto the new migration code by then.
91 TestTables(m);
89 92
93 m.Update();
94 }
90 } 95 }
91 96
92 /// <summary></summary> 97 /// <summary></summary>
@@ -128,7 +133,6 @@ namespace OpenSim.Data.MySQL
128 } 133 }
129 catch 134 catch
130 { 135 {
131 database.Reconnect();
132 } 136 }
133 } 137 }
134 138
diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs
index 243394e..ace2027 100644
--- a/OpenSim/Data/MySQL/MySQLManager.cs
+++ b/OpenSim/Data/MySQL/MySQLManager.cs
@@ -46,15 +46,12 @@ namespace OpenSim.Data.MySQL
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47 47
48 /// <summary> 48 /// <summary>
49 /// The database connection object
50 /// </summary>
51 private MySqlConnection dbcon;
52
53 /// <summary>
54 /// Connection string for ADO.net 49 /// Connection string for ADO.net
55 /// </summary> 50 /// </summary>
56 private string connectionString; 51 private string connectionString;
57 52
53 private object m_dbLock = new object();
54
58 private const string m_waitTimeoutSelect = "select @@wait_timeout"; 55 private const string m_waitTimeoutSelect = "select @@wait_timeout";
59 56
60 /// <summary> 57 /// <summary>
@@ -109,11 +106,11 @@ namespace OpenSim.Data.MySQL
109 try 106 try
110 { 107 {
111 connectionString = connect; 108 connectionString = connect;
112 dbcon = new MySqlConnection(connectionString); 109 //dbcon = new MySqlConnection(connectionString);
113 110
114 try 111 try
115 { 112 {
116 dbcon.Open(); 113 //dbcon.Open();
117 } 114 }
118 catch(Exception e) 115 catch(Exception e)
119 { 116 {
@@ -134,18 +131,21 @@ namespace OpenSim.Data.MySQL
134 /// </summary> 131 /// </summary>
135 protected void GetWaitTimeout() 132 protected void GetWaitTimeout()
136 { 133 {
137 MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon); 134 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
138
139 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
140 { 135 {
141 if (dbReader.Read()) 136 dbcon.Open();
137
138 using (MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon))
142 { 139 {
143 m_waitTimeout 140 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
144 = Convert.ToInt32(dbReader["@@wait_timeout"]) * TimeSpan.TicksPerSecond + m_waitTimeoutLeeway; 141 {
142 if (dbReader.Read())
143 {
144 m_waitTimeout
145 = Convert.ToInt32(dbReader["@@wait_timeout"]) * TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
146 }
147 }
145 } 148 }
146
147 dbReader.Close();
148 cmd.Dispose();
149 } 149 }
150 150
151 m_lastConnectionUse = DateTime.Now.Ticks; 151 m_lastConnectionUse = DateTime.Now.Ticks;
@@ -154,66 +154,9 @@ namespace OpenSim.Data.MySQL
154 "[REGION DB]: Connection wait timeout {0} seconds", m_waitTimeout / TimeSpan.TicksPerSecond); 154 "[REGION DB]: Connection wait timeout {0} seconds", m_waitTimeout / TimeSpan.TicksPerSecond);
155 } 155 }
156 156
157 /// <summary> 157 public string ConnectionString
158 /// Should be called before any db operation. This checks to see if the connection has not timed out
159 /// </summary>
160 public void CheckConnection()
161 { 158 {
162 //m_log.Debug("[REGION DB]: Checking connection"); 159 get { return connectionString; }
163
164 long timeNow = DateTime.Now.Ticks;
165 if (timeNow - m_lastConnectionUse > m_waitTimeout || dbcon.State != ConnectionState.Open)
166 {
167 m_log.DebugFormat("[REGION DB]: Database connection has gone away - reconnecting");
168 Reconnect();
169 }
170
171 // Strictly, we should set this after the actual db operation. But it's more convenient to set here rather
172 // than require the code to call another method - the timeout leeway should be large enough to cover the
173 // inaccuracy.
174 m_lastConnectionUse = timeNow;
175 }
176
177 /// <summary>
178 /// Get the connection being used
179 /// </summary>
180 /// <returns>MySqlConnection Object</returns>
181 public MySqlConnection Connection
182 {
183 get { return dbcon; }
184 }
185
186 /// <summary>
187 /// Shuts down the database connection
188 /// </summary>
189 public void Close()
190 {
191 dbcon.Close();
192 dbcon = null;
193 }
194
195 /// <summary>
196 /// Reconnects to the database
197 /// </summary>
198 public void Reconnect()
199 {
200 m_log.Info("[REGION DB] Reconnecting database");
201
202 lock (dbcon)
203 {
204 try
205 {
206 // Close the DB connection
207 dbcon.Close();
208 // Try reopen it
209 dbcon = new MySqlConnection(connectionString);
210 dbcon.Open();
211 }
212 catch (Exception e)
213 {
214 m_log.Error("Unable to reconnect to database " + e.ToString());
215 }
216 }
217 } 160 }
218 161
219 /// <summary> 162 /// <summary>
@@ -264,9 +207,13 @@ namespace OpenSim.Data.MySQL
264 /// <param name="name">name of embedded resource</param> 207 /// <param name="name">name of embedded resource</param>
265 public void ExecuteResourceSql(string name) 208 public void ExecuteResourceSql(string name)
266 { 209 {
267 CheckConnection(); 210 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
268 MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon); 211 {
269 cmd.ExecuteNonQuery(); 212 dbcon.Open();
213
214 MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon);
215 cmd.ExecuteNonQuery();
216 }
270 } 217 }
271 218
272 /// <summary> 219 /// <summary>
@@ -275,22 +222,29 @@ namespace OpenSim.Data.MySQL
275 /// <param name="sql">sql string to execute</param> 222 /// <param name="sql">sql string to execute</param>
276 public void ExecuteSql(string sql) 223 public void ExecuteSql(string sql)
277 { 224 {
278 CheckConnection(); 225 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
279 MySqlCommand cmd = new MySqlCommand(sql, dbcon); 226 {
280 cmd.ExecuteNonQuery(); 227 dbcon.Open();
228
229 MySqlCommand cmd = new MySqlCommand(sql, dbcon);
230 cmd.ExecuteNonQuery();
231 }
281 } 232 }
282 233
283 public void ExecuteParameterizedSql(string sql, Dictionary<string, string> parameters) 234 public void ExecuteParameterizedSql(string sql, Dictionary<string, string> parameters)
284 { 235 {
285 CheckConnection(); 236 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
286
287 MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand();
288 cmd.CommandText = sql;
289 foreach (KeyValuePair<string, string> param in parameters)
290 { 237 {
291 cmd.Parameters.AddWithValue(param.Key, param.Value); 238 dbcon.Open();
239
240 MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand();
241 cmd.CommandText = sql;
242 foreach (KeyValuePair<string, string> param in parameters)
243 {
244 cmd.Parameters.AddWithValue(param.Key, param.Value);
245 }
246 cmd.ExecuteNonQuery();
292 } 247 }
293 cmd.ExecuteNonQuery();
294 } 248 }
295 249
296 /// <summary> 250 /// <summary>
@@ -299,35 +253,37 @@ namespace OpenSim.Data.MySQL
299 /// <param name="tableList"></param> 253 /// <param name="tableList"></param>
300 public void GetTableVersion(Dictionary<string, string> tableList) 254 public void GetTableVersion(Dictionary<string, string> tableList)
301 { 255 {
302 lock (dbcon) 256 lock (m_dbLock)
303 { 257 {
304 CheckConnection(); 258 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
305
306 MySqlCommand tablesCmd =
307 new MySqlCommand(
308 "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname",
309 dbcon);
310 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
311
312 using (MySqlDataReader tables = tablesCmd.ExecuteReader())
313 { 259 {
314 while (tables.Read()) 260 dbcon.Open();
261
262 using (MySqlCommand tablesCmd = new MySqlCommand(
263 "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", dbcon))
315 { 264 {
316 try 265 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
266
267 using (MySqlDataReader tables = tablesCmd.ExecuteReader())
317 { 268 {
318 string tableName = (string) tables["TABLE_NAME"]; 269 while (tables.Read())
319 string comment = (string) tables["TABLE_COMMENT"];
320 if (tableList.ContainsKey(tableName))
321 { 270 {
322 tableList[tableName] = comment; 271 try
272 {
273 string tableName = (string)tables["TABLE_NAME"];
274 string comment = (string)tables["TABLE_COMMENT"];
275 if (tableList.ContainsKey(tableName))
276 {
277 tableList[tableName] = comment;
278 }
279 }
280 catch (Exception e)
281 {
282 m_log.Error(e.Message, e);
283 }
323 } 284 }
324 } 285 }
325 catch (Exception e)
326 {
327 m_log.Error(e.ToString());
328 }
329 } 286 }
330 tables.Close();
331 } 287 }
332 } 288 }
333 } 289 }
@@ -337,28 +293,27 @@ namespace OpenSim.Data.MySQL
337 /// <summary> 293 /// <summary>
338 /// Runs a query with protection against SQL Injection by using parameterised input. 294 /// Runs a query with protection against SQL Injection by using parameterised input.
339 /// </summary> 295 /// </summary>
296 /// <param name="dbcon">Database connection</param>
340 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param> 297 /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
341 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param> 298 /// <param name="parameters">The parameters - index so that @y is indexed as 'y'</param>
342 /// <returns>A MySQL DB Command</returns> 299 /// <returns>A MySQL DB Command</returns>
343 public IDbCommand Query(string sql, Dictionary<string, object> parameters) 300 public IDbCommand Query(MySqlConnection dbcon, string sql, Dictionary<string, object> parameters)
344 { 301 {
345 try 302 try
346 { 303 {
347 CheckConnection(); // Not sure if this one is necessary 304 MySqlCommand dbcommand = (MySqlCommand)dbcon.CreateCommand();
348
349 MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
350 dbcommand.CommandText = sql; 305 dbcommand.CommandText = sql;
351 foreach (KeyValuePair<string, object> param in parameters) 306 foreach (KeyValuePair<string, object> param in parameters)
352 { 307 {
353 dbcommand.Parameters.AddWithValue(param.Key, param.Value); 308 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
354 } 309 }
355 310
356 return (IDbCommand) dbcommand; 311 return (IDbCommand)dbcommand;
357 } 312 }
358 catch (Exception e) 313 catch (Exception e)
359 { 314 {
360 // Return null if it fails. 315 // Return null if it fails.
361 m_log.Error("Failed during Query generation: " + e.ToString()); 316 m_log.Error("Failed during Query generation: " + e.Message, e);
362 return null; 317 return null;
363 } 318 }
364 } 319 }
@@ -694,8 +649,6 @@ namespace OpenSim.Data.MySQL
694 ret.Add(attachpoint, item); 649 ret.Add(attachpoint, item);
695 } 650 }
696 651
697 r.Close();
698
699 return ret; 652 return ret;
700 } 653 }
701 654
@@ -727,12 +680,17 @@ namespace OpenSim.Data.MySQL
727 680
728 try 681 try
729 { 682 {
730 IDbCommand result = Query(sql, parameters); 683 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
684 {
685 dbcon.Open();
686
687 IDbCommand result = Query(dbcon, sql, parameters);
731 688
732 if (result.ExecuteNonQuery() == 1) 689 if (result.ExecuteNonQuery() == 1)
733 returnval = true; 690 returnval = true;
734 691
735 result.Dispose(); 692 result.Dispose();
693 }
736 } 694 }
737 catch (Exception e) 695 catch (Exception e)
738 { 696 {
@@ -828,12 +786,17 @@ namespace OpenSim.Data.MySQL
828 786
829 try 787 try
830 { 788 {
831 IDbCommand result = Query(sql, parameters); 789 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
790 {
791 dbcon.Open();
832 792
833 if (result.ExecuteNonQuery() == 1) 793 IDbCommand result = Query(dbcon, sql, parameters);
834 returnval = true;
835 794
836 result.Dispose(); 795 if (result.ExecuteNonQuery() == 1)
796 returnval = true;
797
798 result.Dispose();
799 }
837 } 800 }
838 catch (Exception e) 801 catch (Exception e)
839 { 802 {
@@ -927,12 +890,17 @@ namespace OpenSim.Data.MySQL
927 bool returnval = false; 890 bool returnval = false;
928 try 891 try
929 { 892 {
930 IDbCommand result = Query(sql, parameters); 893 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
894 {
895 dbcon.Open();
931 896
932 if (result.ExecuteNonQuery() == 1) 897 IDbCommand result = Query(dbcon, sql, parameters);
933 returnval = true;
934 898
935 result.Dispose(); 899 if (result.ExecuteNonQuery() == 1)
900 returnval = true;
901
902 result.Dispose();
903 }
936 } 904 }
937 catch (Exception e) 905 catch (Exception e)
938 { 906 {
@@ -1030,18 +998,23 @@ namespace OpenSim.Data.MySQL
1030 998
1031 try 999 try
1032 { 1000 {
1033 IDbCommand result = Query(sql, parameters); 1001 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1034
1035 // int x;
1036 // if ((x = result.ExecuteNonQuery()) > 0)
1037 // {
1038 // returnval = true;
1039 // }
1040 if (result.ExecuteNonQuery() > 0)
1041 { 1002 {
1042 returnval = true; 1003 dbcon.Open();
1004
1005 IDbCommand result = Query(dbcon, sql, parameters);
1006
1007 // int x;
1008 // if ((x = result.ExecuteNonQuery()) > 0)
1009 // {
1010 // returnval = true;
1011 // }
1012 if (result.ExecuteNonQuery() > 0)
1013 {
1014 returnval = true;
1015 }
1016 result.Dispose();
1043 } 1017 }
1044 result.Dispose();
1045 } 1018 }
1046 catch (Exception e) 1019 catch (Exception e)
1047 { 1020 {
@@ -1070,18 +1043,23 @@ namespace OpenSim.Data.MySQL
1070 { 1043 {
1071 parameters["?uuid"] = uuid; 1044 parameters["?uuid"] = uuid;
1072 1045
1073 IDbCommand result = Query(sql, parameters); 1046 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1074
1075 // int x;
1076 // if ((x = result.ExecuteNonQuery()) > 0)
1077 // {
1078 // returnval = true;
1079 // }
1080 if (result.ExecuteNonQuery() > 0)
1081 { 1047 {
1082 returnval = true; 1048 dbcon.Open();
1049
1050 IDbCommand result = Query(dbcon, sql, parameters);
1051
1052 // int x;
1053 // if ((x = result.ExecuteNonQuery()) > 0)
1054 // {
1055 // returnval = true;
1056 // }
1057 if (result.ExecuteNonQuery() > 0)
1058 {
1059 returnval = true;
1060 }
1061 result.Dispose();
1083 } 1062 }
1084 result.Dispose();
1085 } 1063 }
1086 catch (Exception e) 1064 catch (Exception e)
1087 { 1065 {
@@ -1122,18 +1100,23 @@ namespace OpenSim.Data.MySQL
1122 1100
1123 try 1101 try
1124 { 1102 {
1125 IDbCommand result = Query(sql, parameters); 1103 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1126
1127 // int x;
1128 // if ((x = result.ExecuteNonQuery()) > 0)
1129 // {
1130 // returnval = true;
1131 // }
1132 if (result.ExecuteNonQuery() > 0)
1133 { 1104 {
1134 returnval = true; 1105 dbcon.Open();
1106
1107 IDbCommand result = Query(dbcon, sql, parameters);
1108
1109 // int x;
1110 // if ((x = result.ExecuteNonQuery()) > 0)
1111 // {
1112 // returnval = true;
1113 // }
1114 if (result.ExecuteNonQuery() > 0)
1115 {
1116 returnval = true;
1117 }
1118 result.Dispose();
1135 } 1119 }
1136 result.Dispose();
1137 } 1120 }
1138 catch (Exception e) 1121 catch (Exception e)
1139 { 1122 {
@@ -1167,45 +1150,51 @@ namespace OpenSim.Data.MySQL
1167 bool returnval = false; 1150 bool returnval = false;
1168 1151
1169 // we want to send in byte data, which means we can't just pass down strings 1152 // we want to send in byte data, which means we can't just pass down strings
1170 try { 1153 try
1171 MySqlCommand cmd = (MySqlCommand) dbcon.CreateCommand(); 1154 {
1172 cmd.CommandText = sql; 1155 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1173 cmd.Parameters.AddWithValue("?owner", appearance.Owner.ToString()); 1156 {
1174 cmd.Parameters.AddWithValue("?serial", appearance.Serial); 1157 dbcon.Open();
1175 cmd.Parameters.AddWithValue("?visual_params", appearance.VisualParams); 1158
1176 cmd.Parameters.AddWithValue("?texture", appearance.Texture.GetBytes()); 1159 using (MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand())
1177 cmd.Parameters.AddWithValue("?avatar_height", appearance.AvatarHeight); 1160 {
1178 cmd.Parameters.AddWithValue("?body_item", appearance.BodyItem.ToString()); 1161 cmd.CommandText = sql;
1179 cmd.Parameters.AddWithValue("?body_asset", appearance.BodyAsset.ToString()); 1162 cmd.Parameters.AddWithValue("?owner", appearance.Owner.ToString());
1180 cmd.Parameters.AddWithValue("?skin_item", appearance.SkinItem.ToString()); 1163 cmd.Parameters.AddWithValue("?serial", appearance.Serial);
1181 cmd.Parameters.AddWithValue("?skin_asset", appearance.SkinAsset.ToString()); 1164 cmd.Parameters.AddWithValue("?visual_params", appearance.VisualParams);
1182 cmd.Parameters.AddWithValue("?hair_item", appearance.HairItem.ToString()); 1165 cmd.Parameters.AddWithValue("?texture", appearance.Texture.GetBytes());
1183 cmd.Parameters.AddWithValue("?hair_asset", appearance.HairAsset.ToString()); 1166 cmd.Parameters.AddWithValue("?avatar_height", appearance.AvatarHeight);
1184 cmd.Parameters.AddWithValue("?eyes_item", appearance.EyesItem.ToString()); 1167 cmd.Parameters.AddWithValue("?body_item", appearance.BodyItem.ToString());
1185 cmd.Parameters.AddWithValue("?eyes_asset", appearance.EyesAsset.ToString()); 1168 cmd.Parameters.AddWithValue("?body_asset", appearance.BodyAsset.ToString());
1186 cmd.Parameters.AddWithValue("?shirt_item", appearance.ShirtItem.ToString()); 1169 cmd.Parameters.AddWithValue("?skin_item", appearance.SkinItem.ToString());
1187 cmd.Parameters.AddWithValue("?shirt_asset", appearance.ShirtAsset.ToString()); 1170 cmd.Parameters.AddWithValue("?skin_asset", appearance.SkinAsset.ToString());
1188 cmd.Parameters.AddWithValue("?pants_item", appearance.PantsItem.ToString()); 1171 cmd.Parameters.AddWithValue("?hair_item", appearance.HairItem.ToString());
1189 cmd.Parameters.AddWithValue("?pants_asset", appearance.PantsAsset.ToString()); 1172 cmd.Parameters.AddWithValue("?hair_asset", appearance.HairAsset.ToString());
1190 cmd.Parameters.AddWithValue("?shoes_item", appearance.ShoesItem.ToString()); 1173 cmd.Parameters.AddWithValue("?eyes_item", appearance.EyesItem.ToString());
1191 cmd.Parameters.AddWithValue("?shoes_asset", appearance.ShoesAsset.ToString()); 1174 cmd.Parameters.AddWithValue("?eyes_asset", appearance.EyesAsset.ToString());
1192 cmd.Parameters.AddWithValue("?socks_item", appearance.SocksItem.ToString()); 1175 cmd.Parameters.AddWithValue("?shirt_item", appearance.ShirtItem.ToString());
1193 cmd.Parameters.AddWithValue("?socks_asset", appearance.SocksAsset.ToString()); 1176 cmd.Parameters.AddWithValue("?shirt_asset", appearance.ShirtAsset.ToString());
1194 cmd.Parameters.AddWithValue("?jacket_item", appearance.JacketItem.ToString()); 1177 cmd.Parameters.AddWithValue("?pants_item", appearance.PantsItem.ToString());
1195 cmd.Parameters.AddWithValue("?jacket_asset", appearance.JacketAsset.ToString()); 1178 cmd.Parameters.AddWithValue("?pants_asset", appearance.PantsAsset.ToString());
1196 cmd.Parameters.AddWithValue("?gloves_item", appearance.GlovesItem.ToString()); 1179 cmd.Parameters.AddWithValue("?shoes_item", appearance.ShoesItem.ToString());
1197 cmd.Parameters.AddWithValue("?gloves_asset", appearance.GlovesAsset.ToString()); 1180 cmd.Parameters.AddWithValue("?shoes_asset", appearance.ShoesAsset.ToString());
1198 cmd.Parameters.AddWithValue("?undershirt_item", appearance.UnderShirtItem.ToString()); 1181 cmd.Parameters.AddWithValue("?socks_item", appearance.SocksItem.ToString());
1199 cmd.Parameters.AddWithValue("?undershirt_asset", appearance.UnderShirtAsset.ToString()); 1182 cmd.Parameters.AddWithValue("?socks_asset", appearance.SocksAsset.ToString());
1200 cmd.Parameters.AddWithValue("?underpants_item", appearance.UnderPantsItem.ToString()); 1183 cmd.Parameters.AddWithValue("?jacket_item", appearance.JacketItem.ToString());
1201 cmd.Parameters.AddWithValue("?underpants_asset", appearance.UnderPantsAsset.ToString()); 1184 cmd.Parameters.AddWithValue("?jacket_asset", appearance.JacketAsset.ToString());
1202 cmd.Parameters.AddWithValue("?skirt_item", appearance.SkirtItem.ToString()); 1185 cmd.Parameters.AddWithValue("?gloves_item", appearance.GlovesItem.ToString());
1203 cmd.Parameters.AddWithValue("?skirt_asset", appearance.SkirtAsset.ToString()); 1186 cmd.Parameters.AddWithValue("?gloves_asset", appearance.GlovesAsset.ToString());
1204 1187 cmd.Parameters.AddWithValue("?undershirt_item", appearance.UnderShirtItem.ToString());
1205 if (cmd.ExecuteNonQuery() > 0) 1188 cmd.Parameters.AddWithValue("?undershirt_asset", appearance.UnderShirtAsset.ToString());
1206 returnval = true; 1189 cmd.Parameters.AddWithValue("?underpants_item", appearance.UnderPantsItem.ToString());
1207 1190 cmd.Parameters.AddWithValue("?underpants_asset", appearance.UnderPantsAsset.ToString());
1208 cmd.Dispose(); 1191 cmd.Parameters.AddWithValue("?skirt_item", appearance.SkirtItem.ToString());
1192 cmd.Parameters.AddWithValue("?skirt_asset", appearance.SkirtAsset.ToString());
1193
1194 if (cmd.ExecuteNonQuery() > 0)
1195 returnval = true;
1196 }
1197 }
1209 } 1198 }
1210 catch (Exception e) 1199 catch (Exception e)
1211 { 1200 {
@@ -1221,33 +1210,38 @@ namespace OpenSim.Data.MySQL
1221 { 1210 {
1222 string sql = "delete from avatarattachments where UUID = ?uuid"; 1211 string sql = "delete from avatarattachments where UUID = ?uuid";
1223 1212
1224 MySqlCommand cmd = (MySqlCommand) dbcon.CreateCommand(); 1213 using (MySqlConnection dbcon = new MySqlConnection(connectionString))
1225 cmd.CommandText = sql; 1214 {
1226 cmd.Parameters.AddWithValue("?uuid", agentID.ToString()); 1215 dbcon.Open();
1216
1217 MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand();
1218 cmd.CommandText = sql;
1219 cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
1227 1220
1228 cmd.ExecuteNonQuery(); 1221 cmd.ExecuteNonQuery();
1229 1222
1230 if (data == null) 1223 if (data == null)
1231 return; 1224 return;
1232 1225
1233 sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (?uuid, ?attachpoint, ?item, ?asset)"; 1226 sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (?uuid, ?attachpoint, ?item, ?asset)";
1234 1227
1235 cmd = (MySqlCommand) dbcon.CreateCommand(); 1228 cmd = (MySqlCommand)dbcon.CreateCommand();
1236 cmd.CommandText = sql; 1229 cmd.CommandText = sql;
1237 1230
1238 foreach (DictionaryEntry e in data) 1231 foreach (DictionaryEntry e in data)
1239 { 1232 {
1240 int attachpoint = Convert.ToInt32(e.Key); 1233 int attachpoint = Convert.ToInt32(e.Key);
1241 1234
1242 Hashtable item = (Hashtable)e.Value; 1235 Hashtable item = (Hashtable)e.Value;
1243 1236
1244 cmd.Parameters.Clear(); 1237 cmd.Parameters.Clear();
1245 cmd.Parameters.AddWithValue("?uuid", agentID.ToString()); 1238 cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
1246 cmd.Parameters.AddWithValue("?attachpoint", attachpoint); 1239 cmd.Parameters.AddWithValue("?attachpoint", attachpoint);
1247 cmd.Parameters.AddWithValue("?item", item["item"]); 1240 cmd.Parameters.AddWithValue("?item", item["item"]);
1248 cmd.Parameters.AddWithValue("?asset", item["asset"]); 1241 cmd.Parameters.AddWithValue("?asset", item["asset"]);
1249 1242
1250 cmd.ExecuteNonQuery(); 1243 cmd.ExecuteNonQuery();
1244 }
1251 } 1245 }
1252 } 1246 }
1253 } 1247 }
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 }
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
38 public class MySqlUserAccountData : MySqlFramework, IUserAccountData 38 public class MySqlUserAccountData : MySqlFramework, IUserAccountData
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 MySqlUserAccountData(string connectionString, string realm) 44 public MySqlUserAccountData(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, "UserStore"); 50 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
50 m.Update(); 51 {
52 dbcon.Open();
53 Migration m = new Migration(dbcon, GetType().Assembly, "UserStore");
54 m.Update();
55 }
51 } 56 }
52 57
53 public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query) 58 public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
@@ -64,49 +69,49 @@ namespace OpenSim.Data.MySQL
64 if (scopeID != UUID.Zero) 69 if (scopeID != UUID.Zero)
65 command += " and ScopeID = ?scopeID"; 70 command += " and ScopeID = ?scopeID";
66 71
67 MySqlCommand cmd = new MySqlCommand(command); 72 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
73 {
74 dbcon.Open();
75 MySqlCommand cmd = new MySqlCommand(command, dbcon);
68 76
69 cmd.Parameters.AddWithValue("?principalID", principalID.ToString()); 77 cmd.Parameters.AddWithValue("?principalID", principalID.ToString());
70 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); 78 cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
71 79
72 IDataReader result = ExecuteReader(cmd); 80 IDataReader result = cmd.ExecuteReader();
73 81
74 if (result.Read()) 82 if (result.Read())
75 {
76 ret.PrincipalID = principalID;
77 UUID scope;
78 UUID.TryParse(result["ScopeID"].ToString(), out scope);
79 ret.ScopeID = scope;
80
81 if (m_ColumnNames == null)
82 { 83 {
83 m_ColumnNames = new List<string>(); 84 ret.PrincipalID = principalID;
84 85 UUID scope;
85 DataTable schemaTable = result.GetSchemaTable(); 86 UUID.TryParse(result["ScopeID"].ToString(), out scope);
86 foreach (DataRow row in schemaTable.Rows) 87 ret.ScopeID = scope;
87 m_ColumnNames.Add(row["ColumnName"].ToString()); 88
89 if (m_ColumnNames == null)
90 {
91 m_ColumnNames = new List<string>();
92
93 DataTable schemaTable = result.GetSchemaTable();
94 foreach (DataRow row in schemaTable.Rows)
95 m_ColumnNames.Add(row["ColumnName"].ToString());
96 }
97
98 foreach (string s in m_ColumnNames)
99 {
100 if (s == "UUID")
101 continue;
102 if (s == "ScopeID")
103 continue;
104
105 ret.Data[s] = result[s].ToString();
106 }
107
108 return ret;
88 } 109 }
89 110 else
90 foreach (string s in m_ColumnNames)
91 { 111 {
92 if (s == "UUID") 112 return null;
93 continue;
94 if (s == "ScopeID")
95 continue;
96
97 ret.Data[s] = result[s].ToString();
98 } 113 }
99
100 result.Close();
101 CloseReaderCommand(cmd);
102
103 return ret;
104 } 114 }
105
106 result.Close();
107 CloseReaderCommand(cmd);
108
109 return null;
110 } 115 }
111 116
112 public bool Store(UserAccountData data) 117 public bool Store(UserAccountData data)
@@ -118,61 +123,60 @@ namespace OpenSim.Data.MySQL
118 123
119 string[] fields = new List<string>(data.Data.Keys).ToArray(); 124 string[] fields = new List<string>(data.Data.Keys).ToArray();
120 125
121 MySqlCommand cmd = new MySqlCommand(); 126 using (MySqlCommand cmd = new MySqlCommand())
122
123 string update = "update `"+m_Realm+"` set ";
124 bool first = true;
125 foreach (string field in fields)
126 { 127 {
127 if (!first) 128 string update = "update `" + m_Realm + "` set ";
128 update += ", "; 129 bool first = true;
129 update += "`" + field + "` = ?"+field; 130 foreach (string field in fields)
130 131 {
131 first = false; 132 if (!first)
132 133 update += ", ";
133 cmd.Parameters.AddWithValue("?"+field, data.Data[field]); 134 update += "`" + field + "` = ?" + field;
134 }
135 135
136 update += " where UUID = ?principalID"; 136 first = false;
137 137
138 if (data.ScopeID != UUID.Zero) 138 cmd.Parameters.AddWithValue("?" + field, data.Data[field]);
139 update += " and ScopeID = ?scopeID"; 139 }
140 140
141 cmd.CommandText = update; 141 update += " where UUID = ?principalID";
142 cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString());
143 cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString());
144 142
145 if (ExecuteNonQuery(cmd) < 1) 143 if (data.ScopeID != UUID.Zero)
146 { 144 update += " and ScopeID = ?scopeID";
147 string insert = "insert into `" + m_Realm + "` (`UUID`, `ScopeID`, `" +
148 String.Join("`, `", fields) +
149 "`) values (?principalID, ?scopeID, ?" + String.Join(", ?", fields) + ")";
150 145
151 cmd.CommandText = insert; 146 cmd.CommandText = update;
147 cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString());
148 cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString());
152 149
153 if (ExecuteNonQuery(cmd) < 1) 150 if (ExecuteNonQuery(cmd) < 1)
154 { 151 {
155 cmd.Dispose(); 152 string insert = "insert into `" + m_Realm + "` (`UUID`, `ScopeID`, `" +
156 return false; 153 String.Join("`, `", fields) +
154 "`) values (?principalID, ?scopeID, ?" + String.Join(", ?", fields) + ")";
155
156 cmd.CommandText = insert;
157
158 if (ExecuteNonQuery(cmd) < 1)
159 {
160 cmd.Dispose();
161 return false;
162 }
157 } 163 }
158 } 164 }
159 165
160 cmd.Dispose();
161
162 return true; 166 return true;
163 } 167 }
164 168
165 public bool SetDataItem(UUID principalID, string item, string value) 169 public bool SetDataItem(UUID principalID, string item, string value)
166 { 170 {
167 MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + 171 using (MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + "` set `" +
168 "` set `" + item + "` = ?" + item + " where UUID = ?UUID"); 172 item + "` = ?" + item + " where UUID = ?UUID"))
169 173 {
170 174 cmd.Parameters.AddWithValue("?" + item, value);
171 cmd.Parameters.AddWithValue("?"+item, value); 175 cmd.Parameters.AddWithValue("?UUID", principalID.ToString());
172 cmd.Parameters.AddWithValue("?UUID", principalID.ToString());
173 176
174 if (ExecuteNonQuery(cmd) > 0) 177 if (ExecuteNonQuery(cmd) > 0)
175 return true; 178 return true;
179 }
176 180
177 return false; 181 return false;
178 } 182 }
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}
diff --git a/OpenSim/Data/MySQL/MySQLXInventoryData.cs b/OpenSim/Data/MySQL/MySQLXInventoryData.cs
index 0eebc9c..b5866cb 100644
--- a/OpenSim/Data/MySQL/MySQLXInventoryData.cs
+++ b/OpenSim/Data/MySQL/MySQLXInventoryData.cs
@@ -110,47 +110,58 @@ namespace OpenSim.Data.MySQL
110 110
111 public bool MoveItem(string id, string newParent) 111 public bool MoveItem(string id, string newParent)
112 { 112 {
113 MySqlCommand cmd = new MySqlCommand(); 113 using (MySqlCommand cmd = new MySqlCommand())
114 {
114 115
115 cmd.CommandText = String.Format("update {0} set parentFolderID = ?ParentFolderID where inventoryID = ?InventoryID", m_Realm); 116 cmd.CommandText = String.Format("update {0} set parentFolderID = ?ParentFolderID where inventoryID = ?InventoryID", m_Realm);
116 cmd.Parameters.AddWithValue("?ParentFolderID", newParent); 117 cmd.Parameters.AddWithValue("?ParentFolderID", newParent);
117 cmd.Parameters.AddWithValue("?InventoryID", id); 118 cmd.Parameters.AddWithValue("?InventoryID", id);
118 119
119 return ExecuteNonQuery(cmd) == 0 ? false : true; 120 return ExecuteNonQuery(cmd) == 0 ? false : true;
121 }
120 } 122 }
121 123
122 public XInventoryItem[] GetActiveGestures(UUID principalID) 124 public XInventoryItem[] GetActiveGestures(UUID principalID)
123 { 125 {
124 MySqlCommand cmd = new MySqlCommand(); 126 using (MySqlCommand cmd = new MySqlCommand())
125 cmd.CommandText = String.Format("select * from inventoryitems where avatarId = ?uuid and assetType = ?type and flags = 1", m_Realm); 127 {
128 cmd.CommandText = String.Format("select * from inventoryitems where avatarId = ?uuid and assetType = ?type and flags = 1", m_Realm);
126 129
127 cmd.Parameters.AddWithValue("?uuid", principalID.ToString()); 130 cmd.Parameters.AddWithValue("?uuid", principalID.ToString());
128 cmd.Parameters.AddWithValue("?type", (int)AssetType.Gesture); 131 cmd.Parameters.AddWithValue("?type", (int)AssetType.Gesture);
129 132
130 return DoQuery(cmd); 133 return DoQuery(cmd);
134 }
131 } 135 }
132 136
133 public int GetAssetPermissions(UUID principalID, UUID assetID) 137 public int GetAssetPermissions(UUID principalID, UUID assetID)
134 { 138 {
135 MySqlCommand cmd = new MySqlCommand(); 139 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
136
137 cmd.CommandText = String.Format("select bit_or(inventoryCurrentPermissions) as inventoryCurrentPermissions from inventoryitems where avatarID = ?PrincipalID and assetID = ?AssetID group by assetID", m_Realm);
138 cmd.Parameters.AddWithValue("?PrincipalID", principalID.ToString());
139 cmd.Parameters.AddWithValue("?AssetID", assetID.ToString());
140
141 IDataReader reader = ExecuteReader(cmd);
142
143 int perms = 0;
144
145 if (reader.Read())
146 { 140 {
147 perms = Convert.ToInt32(reader["inventoryCurrentPermissions"]); 141 dbcon.Open();
142
143 using (MySqlCommand cmd = new MySqlCommand())
144 {
145 cmd.Connection = dbcon;
146
147 cmd.CommandText = String.Format("select bit_or(inventoryCurrentPermissions) as inventoryCurrentPermissions from inventoryitems where avatarID = ?PrincipalID and assetID = ?AssetID group by assetID", m_Realm);
148 cmd.Parameters.AddWithValue("?PrincipalID", principalID.ToString());
149 cmd.Parameters.AddWithValue("?AssetID", assetID.ToString());
150
151 using (IDataReader reader = cmd.ExecuteReader())
152 {
153
154 int perms = 0;
155
156 if (reader.Read())
157 {
158 perms = Convert.ToInt32(reader["inventoryCurrentPermissions"]);
159 }
160
161 return perms;
162 }
163 }
148 } 164 }
149
150 reader.Close();
151 CloseReaderCommand(cmd);
152
153 return perms;
154 } 165 }
155 } 166 }
156} 167}
diff --git a/OpenSim/Data/MySQL/Tests/MySQLGridTest.cs b/OpenSim/Data/MySQL/Tests/MySQLGridTest.cs
index d1d5c2a..8272316 100644
--- a/OpenSim/Data/MySQL/Tests/MySQLGridTest.cs
+++ b/OpenSim/Data/MySQL/Tests/MySQLGridTest.cs
@@ -31,6 +31,7 @@ using OpenSim.Data.Tests;
31using log4net; 31using log4net;
32using System.Reflection; 32using System.Reflection;
33using OpenSim.Tests.Common; 33using OpenSim.Tests.Common;
34using MySql.Data.MySqlClient;
34 35
35namespace OpenSim.Data.MySQL.Tests 36namespace OpenSim.Data.MySQL.Tests
36{ 37{
@@ -65,9 +66,13 @@ namespace OpenSim.Data.MySQL.Tests
65 66
66 // This actually does the roll forward assembly stuff 67 // This actually does the roll forward assembly stuff
67 Assembly assem = GetType().Assembly; 68 Assembly assem = GetType().Assembly;
68 Migration m = new Migration(database.Connection, assem, "GridStore");
69 69
70 m.Update(); 70 using (MySqlConnection dbcon = new MySqlConnection(connect))
71 {
72 dbcon.Open();
73 Migration m = new Migration(dbcon, assem, "AssetStore");
74 m.Update();
75 }
71 } 76 }
72 77
73 [TestFixtureTearDown] 78 [TestFixtureTearDown]
generated by cgit v1.1 at 2024-12-13 08:54:09 +0000