aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MySQL')
-rw-r--r--OpenSim/Data/MySQL/MySQLXAssetData.cs169
-rw-r--r--OpenSim/Data/MySQL/Resources/XAssetStore.migrations30
2 files changed, 108 insertions, 91 deletions
diff --git a/OpenSim/Data/MySQL/MySQLXAssetData.cs b/OpenSim/Data/MySQL/MySQLXAssetData.cs
index d561c89..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 }
@@ -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
diff --git a/OpenSim/Data/MySQL/Resources/XAssetStore.migrations b/OpenSim/Data/MySQL/Resources/XAssetStore.migrations
index d3cca5e..0c49d0d 100644
--- a/OpenSim/Data/MySQL/Resources/XAssetStore.migrations
+++ b/OpenSim/Data/MySQL/Resources/XAssetStore.migrations
@@ -3,24 +3,24 @@
3 3
4BEGIN; 4BEGIN;
5 5
6CREATE TABLE `xassetsmeta` ( 6CREATE TABLE `XAssetsMeta` (
7 `id` char(36) NOT NULL, 7 `ID` char(36) NOT NULL,
8 `hash` binary(32) NOT NULL, 8 `Hash` binary(32) NOT NULL,
9 `name` varchar(64) NOT NULL, 9 `Name` varchar(64) NOT NULL,
10 `description` varchar(64) NOT NULL, 10 `Description` varchar(64) NOT NULL,
11 `asset_type` tinyint(4) NOT NULL, 11 `AssetType` tinyint(4) NOT NULL,
12 `local` tinyint(1) NOT NULL, 12 `Local` tinyint(1) NOT NULL,
13 `temporary` tinyint(1) NOT NULL, 13 `Temporary` tinyint(1) NOT NULL,
14 `create_time` int(11) NOT NULL, 14 `CreateTime` int(11) NOT NULL,
15 `access_time` int(11) NOT NULL, 15 `AccessTime` int(11) NOT NULL,
16 `asset_flags` int(11) NOT NULL, 16 `AssetFlags` int(11) NOT NULL,
17 `creator_id` varchar(128) NOT NULL, 17 `CreatorID` varchar(128) NOT NULL,
18 PRIMARY KEY (`id`) 18 PRIMARY KEY (`id`)
19) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Version 1'; 19) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Version 1';
20 20
21CREATE TABLE `xassetsdata` ( 21CREATE TABLE `XAssetsData` (
22 `hash` binary(32) NOT NULL, 22 `Hash` binary(32) NOT NULL,
23 `data` longblob NOT NULL, 23 `Data` longblob NOT NULL,
24 PRIMARY KEY (`hash`) 24 PRIMARY KEY (`hash`)
25) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Version 1'; 25) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Version 1';
26 26