aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLXAssetData.cs
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--OpenSim/Data/MySQL/MySQLXAssetData.cs177
1 files changed, 99 insertions, 78 deletions
diff --git a/OpenSim/Data/MySQL/MySQLXAssetData.cs b/OpenSim/Data/MySQL/MySQLXAssetData.cs
index 9a50373..15ac921 100644
--- a/OpenSim/Data/MySQL/MySQLXAssetData.cs
+++ b/OpenSim/Data/MySQL/MySQLXAssetData.cs
@@ -50,6 +50,11 @@ namespace OpenSim.Data.MySQL
50 get { return GetType().Assembly; } 50 get { return GetType().Assembly; }
51 } 51 }
52 52
53 /// <summary>
54 /// Number of days that must pass before we update the access time on an asset when it has been fetched.
55 /// </summary>
56 private const int DaysBetweenAccessTimeUpdates = 30;
57
53 private bool m_enableCompression = false; 58 private bool m_enableCompression = false;
54 private string m_connectionString; 59 private string m_connectionString;
55 private object m_dbLock = new object(); 60 private object m_dbLock = new object();
@@ -133,10 +138,10 @@ namespace OpenSim.Data.MySQL
133 dbcon.Open(); 138 dbcon.Open();
134 139
135 using (MySqlCommand cmd = new MySqlCommand( 140 using (MySqlCommand cmd = new MySqlCommand(
136 "SELECT name, description, asset_type, local, temporary, asset_flags, creator_id, data FROM xassetsmeta JOIN xassetsdata ON xassetsmeta.hash = xassetsdata.hash WHERE id=?id", 141 "SELECT Name, Description, AccessTime, AssetType, Local, Temporary, AssetFlags, CreatorID, Data FROM XAssetsMeta JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ID=?ID",
137 dbcon)) 142 dbcon))
138 { 143 {
139 cmd.Parameters.AddWithValue("?id", assetID.ToString()); 144 cmd.Parameters.AddWithValue("?ID", assetID.ToString());
140 145
141 try 146 try
142 { 147 {
@@ -144,18 +149,18 @@ namespace OpenSim.Data.MySQL
144 { 149 {
145 if (dbReader.Read()) 150 if (dbReader.Read())
146 { 151 {
147 asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["asset_type"], dbReader["creator_id"].ToString()); 152 asset = new AssetBase(assetID, (string)dbReader["Name"], (sbyte)dbReader["AssetType"], dbReader["CreatorID"].ToString());
148 asset.Data = (byte[])dbReader["data"]; 153 asset.Data = (byte[])dbReader["Data"];
149 asset.Description = (string)dbReader["description"]; 154 asset.Description = (string)dbReader["Description"];
150 155
151 string local = dbReader["local"].ToString(); 156 string local = dbReader["Local"].ToString();
152 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) 157 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
153 asset.Local = true; 158 asset.Local = true;
154 else 159 else
155 asset.Local = false; 160 asset.Local = false;
156 161
157 asset.Temporary = Convert.ToBoolean(dbReader["temporary"]); 162 asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]);
158 asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]); 163 asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
159 164
160 if (m_enableCompression) 165 if (m_enableCompression)
161 { 166 {
@@ -171,12 +176,14 @@ namespace OpenSim.Data.MySQL
171 // asset.ID, asset.Name, asset.Data.Length, compressedLength); 176 // asset.ID, asset.Name, asset.Data.Length, compressedLength);
172 } 177 }
173 } 178 }
179
180 UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]);
174 } 181 }
175 } 182 }
176 } 183 }
177 catch (Exception e) 184 catch (Exception e)
178 { 185 {
179 m_log.Error("[MYSQL XASSET DATA]: MySql failure fetching asset " + assetID + ": " + e.Message); 186 m_log.Error(string.Format("[MYSQL XASSET DATA]: Failure fetching asset {0}", assetID), e);
180 } 187 }
181 } 188 }
182 } 189 }
@@ -204,14 +211,18 @@ namespace OpenSim.Data.MySQL
204 if (asset.Name.Length > 64) 211 if (asset.Name.Length > 64)
205 { 212 {
206 assetName = asset.Name.Substring(0, 64); 213 assetName = asset.Name.Substring(0, 64);
207 m_log.Warn("[XASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add"); 214 m_log.WarnFormat(
215 "[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
216 asset.Name, asset.ID, asset.Name.Length, assetName.Length);
208 } 217 }
209 218
210 string assetDescription = asset.Description; 219 string assetDescription = asset.Description;
211 if (asset.Description.Length > 64) 220 if (asset.Description.Length > 64)
212 { 221 {
213 assetDescription = asset.Description.Substring(0, 64); 222 assetDescription = asset.Description.Substring(0, 64);
214 m_log.Warn("[XASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add"); 223 m_log.WarnFormat(
224 "[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
225 asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
215 } 226 }
216 227
217 if (m_enableCompression) 228 if (m_enableCompression)
@@ -238,23 +249,23 @@ namespace OpenSim.Data.MySQL
238 { 249 {
239 using (MySqlCommand cmd = 250 using (MySqlCommand cmd =
240 new MySqlCommand( 251 new MySqlCommand(
241 "replace INTO xassetsmeta(id, hash, name, description, asset_type, local, temporary, create_time, access_time, asset_flags, creator_id)" + 252 "replace INTO XAssetsMeta(ID, Hash, Name, Description, AssetType, Local, Temporary, CreateTime, AccessTime, AssetFlags, CreatorID)" +
242 "VALUES(?id, ?hash, ?name, ?description, ?asset_type, ?local, ?temporary, ?create_time, ?access_time, ?asset_flags, ?creator_id)", 253 "VALUES(?ID, ?Hash, ?Name, ?Description, ?AssetType, ?Local, ?Temporary, ?CreateTime, ?AccessTime, ?AssetFlags, ?CreatorID)",
243 dbcon)) 254 dbcon))
244 { 255 {
245 // create unix epoch time 256 // create unix epoch time
246 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); 257 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
247 cmd.Parameters.AddWithValue("?id", asset.ID); 258 cmd.Parameters.AddWithValue("?ID", asset.ID);
248 cmd.Parameters.AddWithValue("?hash", hash); 259 cmd.Parameters.AddWithValue("?Hash", hash);
249 cmd.Parameters.AddWithValue("?name", assetName); 260 cmd.Parameters.AddWithValue("?Name", assetName);
250 cmd.Parameters.AddWithValue("?description", assetDescription); 261 cmd.Parameters.AddWithValue("?Description", assetDescription);
251 cmd.Parameters.AddWithValue("?asset_type", asset.Type); 262 cmd.Parameters.AddWithValue("?AssetType", asset.Type);
252 cmd.Parameters.AddWithValue("?local", asset.Local); 263 cmd.Parameters.AddWithValue("?Local", asset.Local);
253 cmd.Parameters.AddWithValue("?temporary", asset.Temporary); 264 cmd.Parameters.AddWithValue("?Temporary", asset.Temporary);
254 cmd.Parameters.AddWithValue("?create_time", now); 265 cmd.Parameters.AddWithValue("?CreateTime", now);
255 cmd.Parameters.AddWithValue("?access_time", now); 266 cmd.Parameters.AddWithValue("?AccessTime", now);
256 cmd.Parameters.AddWithValue("?creator_id", asset.Metadata.CreatorID); 267 cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID);
257 cmd.Parameters.AddWithValue("?asset_flags", (int)asset.Flags); 268 cmd.Parameters.AddWithValue("?AssetFlags", (int)asset.Flags);
258 cmd.ExecuteNonQuery(); 269 cmd.ExecuteNonQuery();
259 } 270 }
260 } 271 }
@@ -274,11 +285,11 @@ namespace OpenSim.Data.MySQL
274 { 285 {
275 using (MySqlCommand cmd = 286 using (MySqlCommand cmd =
276 new MySqlCommand( 287 new MySqlCommand(
277 "INSERT INTO xassetsdata(hash, data) VALUES(?hash, ?data)", 288 "INSERT INTO XAssetsData(Hash, Data) VALUES(?Hash, ?Data)",
278 dbcon)) 289 dbcon))
279 { 290 {
280 cmd.Parameters.AddWithValue("?hash", hash); 291 cmd.Parameters.AddWithValue("?Hash", hash);
281 cmd.Parameters.AddWithValue("?data", asset.Data); 292 cmd.Parameters.AddWithValue("?Data", asset.Data);
282 cmd.ExecuteNonQuery(); 293 cmd.ExecuteNonQuery();
283 } 294 }
284 } 295 }
@@ -299,41 +310,49 @@ namespace OpenSim.Data.MySQL
299 } 310 }
300 } 311 }
301 312
302// private void UpdateAccessTime(AssetBase asset) 313 /// <summary>
303// { 314 /// Updates the access time of the asset if it was accessed above a given threshhold amount of time.
304// lock (m_dbLock) 315 /// </summary>
305// { 316 /// <remarks>
306// using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) 317 /// This gives us some insight into assets which haven't ben accessed for a long period. This is only done
307// { 318 /// over the threshold time to avoid excessive database writes as assets are fetched.
308// dbcon.Open(); 319 /// </remarks>
309// MySqlCommand cmd = 320 /// <param name='asset'></param>
310// new MySqlCommand("update assets set access_time=?access_time where id=?id", 321 /// <param name='accessTime'></param>
311// dbcon); 322 private void UpdateAccessTime(AssetMetadata assetMetadata, int accessTime)
312// 323 {
313// // need to ensure we dispose 324 DateTime now = DateTime.UtcNow;
314// try 325
315// { 326 if ((now - Utils.UnixTimeToDateTime(accessTime)).TotalDays < DaysBetweenAccessTimeUpdates)
316// using (cmd) 327 return;
317// { 328
318// // create unix epoch time 329 lock (m_dbLock)
319// int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); 330 {
320// cmd.Parameters.AddWithValue("?id", asset.ID); 331 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
321// cmd.Parameters.AddWithValue("?access_time", now); 332 {
322// cmd.ExecuteNonQuery(); 333 dbcon.Open();
323// cmd.Dispose(); 334 MySqlCommand cmd =
324// } 335 new MySqlCommand("update XAssetsMeta set AccessTime=?AccessTime where ID=?ID", dbcon);
325// } 336
326// catch (Exception e) 337 try
327// { 338 {
328// m_log.ErrorFormat( 339 using (cmd)
329// "[ASSETS DB]: " + 340 {
330// "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString() 341 // create unix epoch time
331// + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name); 342 cmd.Parameters.AddWithValue("?ID", assetMetadata.ID);
332// } 343 cmd.Parameters.AddWithValue("?AccessTime", (int)Utils.DateTimeToUnixTime(now));
333// } 344 cmd.ExecuteNonQuery();
334// } 345 }
335// 346 }
336// } 347 catch (Exception e)
348 {
349 m_log.ErrorFormat(
350 "[XASSET MYSQL DB]: Failure updating access_time for asset {0} with name {1}",
351 assetMetadata.ID, assetMetadata.Name);
352 }
353 }
354 }
355 }
337 356
338 /// <summary> 357 /// <summary>
339 /// We assume we already have the m_dbLock. 358 /// We assume we already have the m_dbLock.
@@ -349,9 +368,9 @@ namespace OpenSim.Data.MySQL
349 368
350 bool exists = false; 369 bool exists = false;
351 370
352 using (MySqlCommand cmd = new MySqlCommand("SELECT hash FROM xassetsdata WHERE hash=?hash", dbcon)) 371 using (MySqlCommand cmd = new MySqlCommand("SELECT Hash FROM XAssetsData WHERE Hash=?Hash", dbcon))
353 { 372 {
354 cmd.Parameters.AddWithValue("?hash", hash); 373 cmd.Parameters.AddWithValue("?Hash", hash);
355 374
356 try 375 try
357 { 376 {
@@ -391,9 +410,9 @@ namespace OpenSim.Data.MySQL
391 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) 410 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
392 { 411 {
393 dbcon.Open(); 412 dbcon.Open();
394 using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM xassetsmeta WHERE id=?id", dbcon)) 413 using (MySqlCommand cmd = new MySqlCommand("SELECT ID FROM XAssetsMeta WHERE ID=?ID", dbcon))
395 { 414 {
396 cmd.Parameters.AddWithValue("?id", uuid.ToString()); 415 cmd.Parameters.AddWithValue("?ID", uuid.ToString());
397 416
398 try 417 try
399 { 418 {
@@ -408,8 +427,7 @@ namespace OpenSim.Data.MySQL
408 } 427 }
409 catch (Exception e) 428 catch (Exception e)
410 { 429 {
411 m_log.ErrorFormat( 430 m_log.Error(string.Format("[XASSETS DB]: MySql failure fetching asset {0}", uuid), e);
412 "[XASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid);
413 } 431 }
414 } 432 }
415 } 433 }
@@ -418,6 +436,7 @@ namespace OpenSim.Data.MySQL
418 return assetExists; 436 return assetExists;
419 } 437 }
420 438
439
421 /// <summary> 440 /// <summary>
422 /// Returns a list of AssetMetadata objects. The list is a subset of 441 /// Returns a list of AssetMetadata objects. The list is a subset of
423 /// the entire data set offset by <paramref name="start" /> containing 442 /// the entire data set offset by <paramref name="start" /> containing
@@ -435,7 +454,7 @@ namespace OpenSim.Data.MySQL
435 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) 454 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
436 { 455 {
437 dbcon.Open(); 456 dbcon.Open();
438 MySqlCommand cmd = new MySqlCommand("SELECT name,description,asset_type,temporary,id,asset_flags,creator_id FROM xassetsmeta LIMIT ?start, ?count", dbcon); 457 MySqlCommand cmd = new MySqlCommand("SELECT Name, Description, AccessTime, AssetType, Temporary, ID, AssetFlags, CreatorID FROM XAssetsMeta LIMIT ?start, ?count", dbcon);
439 cmd.Parameters.AddWithValue("?start", start); 458 cmd.Parameters.AddWithValue("?start", start);
440 cmd.Parameters.AddWithValue("?count", count); 459 cmd.Parameters.AddWithValue("?count", count);
441 460
@@ -446,17 +465,19 @@ namespace OpenSim.Data.MySQL
446 while (dbReader.Read()) 465 while (dbReader.Read())
447 { 466 {
448 AssetMetadata metadata = new AssetMetadata(); 467 AssetMetadata metadata = new AssetMetadata();
449 metadata.Name = (string)dbReader["name"]; 468 metadata.Name = (string)dbReader["Name"];
450 metadata.Description = (string)dbReader["description"]; 469 metadata.Description = (string)dbReader["Description"];
451 metadata.Type = (sbyte)dbReader["asset_type"]; 470 metadata.Type = (sbyte)dbReader["AssetType"];
452 metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct. 471 metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct.
453 metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]); 472 metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
454 metadata.FullID = DBGuid.FromDB(dbReader["id"]); 473 metadata.FullID = DBGuid.FromDB(dbReader["ID"]);
455 metadata.CreatorID = dbReader["creator_id"].ToString(); 474 metadata.CreatorID = dbReader["CreatorID"].ToString();
456 475
457 // We'll ignore this for now - it appears unused! 476 // We'll ignore this for now - it appears unused!
458// metadata.SHA1 = dbReader["hash"]); 477// metadata.SHA1 = dbReader["hash"]);
459 478
479 UpdateAccessTime(metadata, (int)dbReader["AccessTime"]);
480
460 retList.Add(metadata); 481 retList.Add(metadata);
461 } 482 }
462 } 483 }
@@ -481,9 +502,9 @@ namespace OpenSim.Data.MySQL
481 { 502 {
482 dbcon.Open(); 503 dbcon.Open();
483 504
484 using (MySqlCommand cmd = new MySqlCommand("delete from xassetsmeta where id=?id", dbcon)) 505 using (MySqlCommand cmd = new MySqlCommand("delete from XAssetsMeta where ID=?ID", dbcon))
485 { 506 {
486 cmd.Parameters.AddWithValue("?id", id); 507 cmd.Parameters.AddWithValue("?ID", id);
487 cmd.ExecuteNonQuery(); 508 cmd.ExecuteNonQuery();
488 } 509 }
489 510