diff options
Diffstat (limited to 'OpenSim/Data/MySQL/MySQLXAssetData.cs')
-rw-r--r-- | OpenSim/Data/MySQL/MySQLXAssetData.cs | 169 |
1 files changed, 93 insertions, 76 deletions
diff --git a/OpenSim/Data/MySQL/MySQLXAssetData.cs b/OpenSim/Data/MySQL/MySQLXAssetData.cs index c2282c8..8c93825 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 | } |
@@ -242,23 +249,23 @@ namespace OpenSim.Data.MySQL | |||
242 | { | 249 | { |
243 | using (MySqlCommand cmd = | 250 | using (MySqlCommand cmd = |
244 | new MySqlCommand( | 251 | new MySqlCommand( |
245 | "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)" + |
246 | "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)", |
247 | dbcon)) | 254 | dbcon)) |
248 | { | 255 | { |
249 | // create unix epoch time | 256 | // create unix epoch time |
250 | int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); | 257 | int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); |
251 | cmd.Parameters.AddWithValue("?id", asset.ID); | 258 | cmd.Parameters.AddWithValue("?ID", asset.ID); |
252 | cmd.Parameters.AddWithValue("?hash", hash); | 259 | cmd.Parameters.AddWithValue("?Hash", hash); |
253 | cmd.Parameters.AddWithValue("?name", assetName); | 260 | cmd.Parameters.AddWithValue("?Name", assetName); |
254 | cmd.Parameters.AddWithValue("?description", assetDescription); | 261 | cmd.Parameters.AddWithValue("?Description", assetDescription); |
255 | cmd.Parameters.AddWithValue("?asset_type", asset.Type); | 262 | cmd.Parameters.AddWithValue("?AssetType", asset.Type); |
256 | cmd.Parameters.AddWithValue("?local", asset.Local); | 263 | cmd.Parameters.AddWithValue("?Local", asset.Local); |
257 | cmd.Parameters.AddWithValue("?temporary", asset.Temporary); | 264 | cmd.Parameters.AddWithValue("?Temporary", asset.Temporary); |
258 | cmd.Parameters.AddWithValue("?create_time", now); | 265 | cmd.Parameters.AddWithValue("?CreateTime", now); |
259 | cmd.Parameters.AddWithValue("?access_time", now); | 266 | cmd.Parameters.AddWithValue("?AccessTime", now); |
260 | cmd.Parameters.AddWithValue("?creator_id", asset.Metadata.CreatorID); | 267 | cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID); |
261 | cmd.Parameters.AddWithValue("?asset_flags", (int)asset.Flags); | 268 | cmd.Parameters.AddWithValue("?AssetFlags", (int)asset.Flags); |
262 | cmd.ExecuteNonQuery(); | 269 | cmd.ExecuteNonQuery(); |
263 | } | 270 | } |
264 | } | 271 | } |
@@ -278,11 +285,11 @@ namespace OpenSim.Data.MySQL | |||
278 | { | 285 | { |
279 | using (MySqlCommand cmd = | 286 | using (MySqlCommand cmd = |
280 | new MySqlCommand( | 287 | new MySqlCommand( |
281 | "INSERT INTO xassetsdata(hash, data) VALUES(?hash, ?data)", | 288 | "INSERT INTO XAssetsData(Hash, Data) VALUES(?Hash, ?Data)", |
282 | dbcon)) | 289 | dbcon)) |
283 | { | 290 | { |
284 | cmd.Parameters.AddWithValue("?hash", hash); | 291 | cmd.Parameters.AddWithValue("?Hash", hash); |
285 | cmd.Parameters.AddWithValue("?data", asset.Data); | 292 | cmd.Parameters.AddWithValue("?Data", asset.Data); |
286 | cmd.ExecuteNonQuery(); | 293 | cmd.ExecuteNonQuery(); |
287 | } | 294 | } |
288 | } | 295 | } |
@@ -303,41 +310,49 @@ namespace OpenSim.Data.MySQL | |||
303 | } | 310 | } |
304 | } | 311 | } |
305 | 312 | ||
306 | // private void UpdateAccessTime(AssetBase asset) | 313 | /// <summary> |
307 | // { | 314 | /// Updates the access time of the asset if it was accessed above a given threshhold amount of time. |
308 | // lock (m_dbLock) | 315 | /// </summary> |
309 | // { | 316 | /// <remarks> |
310 | // 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 |
311 | // { | 318 | /// over the threshold time to avoid excessive database writes as assets are fetched. |
312 | // dbcon.Open(); | 319 | /// </remarks> |
313 | // MySqlCommand cmd = | 320 | /// <param name='asset'></param> |
314 | // new MySqlCommand("update assets set access_time=?access_time where id=?id", | 321 | /// <param name='accessTime'></param> |
315 | // dbcon); | 322 | private void UpdateAccessTime(AssetMetadata assetMetadata, int accessTime) |
316 | // | 323 | { |
317 | // // need to ensure we dispose | 324 | DateTime now = DateTime.UtcNow; |
318 | // try | 325 | |
319 | // { | 326 | if ((now - Utils.UnixTimeToDateTime(accessTime)).TotalDays < DaysBetweenAccessTimeUpdates) |
320 | // using (cmd) | 327 | return; |
321 | // { | 328 | |
322 | // // create unix epoch time | 329 | lock (m_dbLock) |
323 | // int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); | 330 | { |
324 | // cmd.Parameters.AddWithValue("?id", asset.ID); | 331 | using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) |
325 | // cmd.Parameters.AddWithValue("?access_time", now); | 332 | { |
326 | // cmd.ExecuteNonQuery(); | 333 | dbcon.Open(); |
327 | // cmd.Dispose(); | 334 | MySqlCommand cmd = |
328 | // } | 335 | new MySqlCommand("update XAssetsMeta set AccessTime=?AccessTime where ID=?ID", dbcon); |
329 | // } | 336 | |
330 | // catch (Exception e) | 337 | try |
331 | // { | 338 | { |
332 | // m_log.ErrorFormat( | 339 | using (cmd) |
333 | // "[ASSETS DB]: " + | 340 | { |
334 | // "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString() | 341 | // create unix epoch time |
335 | // + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name); | 342 | cmd.Parameters.AddWithValue("?ID", assetMetadata.ID); |
336 | // } | 343 | cmd.Parameters.AddWithValue("?AccessTime", (int)Utils.DateTimeToUnixTime(now)); |
337 | // } | 344 | cmd.ExecuteNonQuery(); |
338 | // } | 345 | } |
339 | // | 346 | } |
340 | // } | 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 | } | ||
341 | 356 | ||
342 | /// <summary> | 357 | /// <summary> |
343 | /// We assume we already have the m_dbLock. | 358 | /// We assume we already have the m_dbLock. |
@@ -353,9 +368,9 @@ namespace OpenSim.Data.MySQL | |||
353 | 368 | ||
354 | bool exists = false; | 369 | bool exists = false; |
355 | 370 | ||
356 | 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)) |
357 | { | 372 | { |
358 | cmd.Parameters.AddWithValue("?hash", hash); | 373 | cmd.Parameters.AddWithValue("?Hash", hash); |
359 | 374 | ||
360 | try | 375 | try |
361 | { | 376 | { |
@@ -395,9 +410,9 @@ namespace OpenSim.Data.MySQL | |||
395 | using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) | 410 | using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) |
396 | { | 411 | { |
397 | dbcon.Open(); | 412 | dbcon.Open(); |
398 | 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)) |
399 | { | 414 | { |
400 | cmd.Parameters.AddWithValue("?id", uuid.ToString()); | 415 | cmd.Parameters.AddWithValue("?ID", uuid.ToString()); |
401 | 416 | ||
402 | try | 417 | try |
403 | { | 418 | { |
@@ -412,8 +427,7 @@ namespace OpenSim.Data.MySQL | |||
412 | } | 427 | } |
413 | catch (Exception e) | 428 | catch (Exception e) |
414 | { | 429 | { |
415 | m_log.ErrorFormat( | 430 | m_log.Error(string.Format("[XASSETS DB]: MySql failure fetching asset {0}", uuid), e); |
416 | "[XASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid); | ||
417 | } | 431 | } |
418 | } | 432 | } |
419 | } | 433 | } |
@@ -422,6 +436,7 @@ namespace OpenSim.Data.MySQL | |||
422 | return assetExists; | 436 | return assetExists; |
423 | } | 437 | } |
424 | 438 | ||
439 | |||
425 | /// <summary> | 440 | /// <summary> |
426 | /// 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 |
427 | /// the entire data set offset by <paramref name="start" /> containing | 442 | /// the entire data set offset by <paramref name="start" /> containing |
@@ -439,7 +454,7 @@ namespace OpenSim.Data.MySQL | |||
439 | using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) | 454 | using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) |
440 | { | 455 | { |
441 | dbcon.Open(); | 456 | dbcon.Open(); |
442 | 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); |
443 | cmd.Parameters.AddWithValue("?start", start); | 458 | cmd.Parameters.AddWithValue("?start", start); |
444 | cmd.Parameters.AddWithValue("?count", count); | 459 | cmd.Parameters.AddWithValue("?count", count); |
445 | 460 | ||
@@ -450,17 +465,19 @@ namespace OpenSim.Data.MySQL | |||
450 | while (dbReader.Read()) | 465 | while (dbReader.Read()) |
451 | { | 466 | { |
452 | AssetMetadata metadata = new AssetMetadata(); | 467 | AssetMetadata metadata = new AssetMetadata(); |
453 | metadata.Name = (string)dbReader["name"]; | 468 | metadata.Name = (string)dbReader["Name"]; |
454 | metadata.Description = (string)dbReader["description"]; | 469 | metadata.Description = (string)dbReader["Description"]; |
455 | metadata.Type = (sbyte)dbReader["asset_type"]; | 470 | metadata.Type = (sbyte)dbReader["AssetType"]; |
456 | 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. |
457 | metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]); | 472 | metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]); |
458 | metadata.FullID = DBGuid.FromDB(dbReader["id"]); | 473 | metadata.FullID = DBGuid.FromDB(dbReader["ID"]); |
459 | metadata.CreatorID = dbReader["creator_id"].ToString(); | 474 | metadata.CreatorID = dbReader["CreatorID"].ToString(); |
460 | 475 | ||
461 | // We'll ignore this for now - it appears unused! | 476 | // We'll ignore this for now - it appears unused! |
462 | // metadata.SHA1 = dbReader["hash"]); | 477 | // metadata.SHA1 = dbReader["hash"]); |
463 | 478 | ||
479 | UpdateAccessTime(metadata, (int)dbReader["AccessTime"]); | ||
480 | |||
464 | retList.Add(metadata); | 481 | retList.Add(metadata); |
465 | } | 482 | } |
466 | } | 483 | } |
@@ -485,9 +502,9 @@ namespace OpenSim.Data.MySQL | |||
485 | { | 502 | { |
486 | dbcon.Open(); | 503 | dbcon.Open(); |
487 | 504 | ||
488 | 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)) |
489 | { | 506 | { |
490 | cmd.Parameters.AddWithValue("?id", id); | 507 | cmd.Parameters.AddWithValue("?ID", id); |
491 | cmd.ExecuteNonQuery(); | 508 | cmd.ExecuteNonQuery(); |
492 | } | 509 | } |
493 | 510 | ||