clockwerk-opensim-stable – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 vero 1 /*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27  
28 using System;
29 using System.Collections.Generic;
30 using System.Data;
31 using System.IO;
32 using System.IO.Compression;
33 using System.Reflection;
34 using System.Security.Cryptography;
35 using System.Text;
36 using log4net;
37 using MySql.Data.MySqlClient;
38 using OpenMetaverse;
39 using OpenSim.Framework;
40 using OpenSim.Data;
41  
42 namespace OpenSim.Data.MySQL
43 {
44 public class MySQLXAssetData : IXAssetDataPlugin
45 {
46 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
47  
48 protected virtual Assembly Assembly
49 {
50 get { return GetType().Assembly; }
51 }
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  
58 private bool m_enableCompression = false;
59 private string m_connectionString;
60 private object m_dbLock = new object();
61  
62 /// <summary>
63 /// We can reuse this for all hashing since all methods are single-threaded through m_dbBLock
64 /// </summary>
65 private HashAlgorithm hasher = new SHA256CryptoServiceProvider();
66  
67 #region IPlugin Members
68  
69 public string Version { get { return "1.0.0.0"; } }
70  
71 /// <summary>
72 /// <para>Initialises Asset interface</para>
73 /// <para>
74 /// <list type="bullet">
75 /// <item>Loads and initialises the MySQL storage plugin.</item>
76 /// <item>Warns and uses the obsolete mysql_connection.ini if connect string is empty.</item>
77 /// <item>Check for migration</item>
78 /// </list>
79 /// </para>
80 /// </summary>
81 /// <param name="connect">connect string</param>
82 public void Initialise(string connect)
83 {
84 m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
85 m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
86 m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
87 m_log.ErrorFormat("[MYSQL XASSETDATA]: THIS PLUGIN IS STRICTLY EXPERIMENTAL.");
88 m_log.ErrorFormat("[MYSQL XASSETDATA]: DO NOT USE FOR ANY DATA THAT YOU DO NOT MIND LOSING.");
89 m_log.ErrorFormat("[MYSQL XASSETDATA]: DATABASE TABLES CAN CHANGE AT ANY TIME, CAUSING EXISTING DATA TO BE LOST.");
90 m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
91 m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
92 m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
93  
94 m_connectionString = connect;
95  
96 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
97 {
98 dbcon.Open();
99 Migration m = new Migration(dbcon, Assembly, "XAssetStore");
100 m.Update();
101 }
102 }
103  
104 public void Initialise()
105 {
106 throw new NotImplementedException();
107 }
108  
109 public void Dispose() { }
110  
111 /// <summary>
112 /// The name of this DB provider
113 /// </summary>
114 public string Name
115 {
116 get { return "MySQL XAsset storage engine"; }
117 }
118  
119 #endregion
120  
121 #region IAssetDataPlugin Members
122  
123 /// <summary>
124 /// Fetch Asset <paramref name="assetID"/> from database
125 /// </summary>
126 /// <param name="assetID">Asset UUID to fetch</param>
127 /// <returns>Return the asset</returns>
128 /// <remarks>On failure : throw an exception and attempt to reconnect to database</remarks>
129 public AssetBase GetAsset(UUID assetID)
130 {
131 // m_log.DebugFormat("[MYSQL XASSET DATA]: Looking for asset {0}", assetID);
132  
133 AssetBase asset = null;
134 lock (m_dbLock)
135 {
136 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
137 {
138 dbcon.Open();
139  
140 using (MySqlCommand cmd = new MySqlCommand(
141 "SELECT Name, Description, AccessTime, AssetType, Local, Temporary, AssetFlags, CreatorID, Data FROM XAssetsMeta JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ID=?ID",
142 dbcon))
143 {
144 cmd.Parameters.AddWithValue("?ID", assetID.ToString());
145  
146 try
147 {
148 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
149 {
150 if (dbReader.Read())
151 {
152 asset = new AssetBase(assetID, (string)dbReader["Name"], (sbyte)dbReader["AssetType"], dbReader["CreatorID"].ToString());
153 asset.Data = (byte[])dbReader["Data"];
154 asset.Description = (string)dbReader["Description"];
155  
156 string local = dbReader["Local"].ToString();
157 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
158 asset.Local = true;
159 else
160 asset.Local = false;
161  
162 asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]);
163 asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
164  
165 if (m_enableCompression)
166 {
167 using (GZipStream decompressionStream = new GZipStream(new MemoryStream(asset.Data), CompressionMode.Decompress))
168 {
169 MemoryStream outputStream = new MemoryStream();
170 WebUtil.CopyStream(decompressionStream, outputStream, int.MaxValue);
171 // int compressedLength = asset.Data.Length;
172 asset.Data = outputStream.ToArray();
173  
174 // m_log.DebugFormat(
175 // "[XASSET DB]: Decompressed {0} {1} to {2} bytes from {3}",
176 // asset.ID, asset.Name, asset.Data.Length, compressedLength);
177 }
178 }
179  
180 UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]);
181 }
182 }
183 }
184 catch (Exception e)
185 {
186 m_log.Error(string.Format("[MYSQL XASSET DATA]: Failure fetching asset {0}", assetID), e);
187 }
188 }
189 }
190 }
191  
192 return asset;
193 }
194  
195 /// <summary>
196 /// Create an asset in database, or update it if existing.
197 /// </summary>
198 /// <param name="asset">Asset UUID to create</param>
199 /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
200 public void StoreAsset(AssetBase asset)
201 {
202 // m_log.DebugFormat("[XASSETS DB]: Storing asset {0} {1}", asset.Name, asset.ID);
203  
204 lock (m_dbLock)
205 {
206 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
207 {
208 dbcon.Open();
209  
210 using (MySqlTransaction transaction = dbcon.BeginTransaction())
211 {
212 string assetName = asset.Name;
213 if (asset.Name.Length > 64)
214 {
215 assetName = asset.Name.Substring(0, 64);
216 m_log.WarnFormat(
217 "[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
218 asset.Name, asset.ID, asset.Name.Length, assetName.Length);
219 }
220  
221 string assetDescription = asset.Description;
222 if (asset.Description.Length > 64)
223 {
224 assetDescription = asset.Description.Substring(0, 64);
225 m_log.WarnFormat(
226 "[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
227 asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
228 }
229  
230 if (m_enableCompression)
231 {
232 MemoryStream outputStream = new MemoryStream();
233  
234 using (GZipStream compressionStream = new GZipStream(outputStream, CompressionMode.Compress, false))
235 {
236 // Console.WriteLine(WebUtil.CopyTo(new MemoryStream(asset.Data), compressionStream, int.MaxValue));
237 // We have to close the compression stream in order to make sure it writes everything out to the underlying memory output stream.
238 compressionStream.Close();
239 byte[] compressedData = outputStream.ToArray();
240 asset.Data = compressedData;
241 }
242 }
243  
244 byte[] hash = hasher.ComputeHash(asset.Data);
245  
246 // m_log.DebugFormat(
247 // "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}",
248 // asset.ID, asset.Name, hash, compressedData.Length);
249  
250 try
251 {
252 using (MySqlCommand cmd =
253 new MySqlCommand(
254 "replace INTO XAssetsMeta(ID, Hash, Name, Description, AssetType, Local, Temporary, CreateTime, AccessTime, AssetFlags, CreatorID)" +
255 "VALUES(?ID, ?Hash, ?Name, ?Description, ?AssetType, ?Local, ?Temporary, ?CreateTime, ?AccessTime, ?AssetFlags, ?CreatorID)",
256 dbcon))
257 {
258 // create unix epoch time
259 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
260 cmd.Parameters.AddWithValue("?ID", asset.ID);
261 cmd.Parameters.AddWithValue("?Hash", hash);
262 cmd.Parameters.AddWithValue("?Name", assetName);
263 cmd.Parameters.AddWithValue("?Description", assetDescription);
264 cmd.Parameters.AddWithValue("?AssetType", asset.Type);
265 cmd.Parameters.AddWithValue("?Local", asset.Local);
266 cmd.Parameters.AddWithValue("?Temporary", asset.Temporary);
267 cmd.Parameters.AddWithValue("?CreateTime", now);
268 cmd.Parameters.AddWithValue("?AccessTime", now);
269 cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID);
270 cmd.Parameters.AddWithValue("?AssetFlags", (int)asset.Flags);
271 cmd.ExecuteNonQuery();
272 }
273 }
274 catch (Exception e)
275 {
276 m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset metadata {0} with name \"{1}\". Error: {2}",
277 asset.FullID, asset.Name, e.Message);
278  
279 transaction.Rollback();
280  
281 return;
282 }
283  
284 if (!ExistsData(dbcon, transaction, hash))
285 {
286 try
287 {
288 using (MySqlCommand cmd =
289 new MySqlCommand(
290 "INSERT INTO XAssetsData(Hash, Data) VALUES(?Hash, ?Data)",
291 dbcon))
292 {
293 cmd.Parameters.AddWithValue("?Hash", hash);
294 cmd.Parameters.AddWithValue("?Data", asset.Data);
295 cmd.ExecuteNonQuery();
296 }
297 }
298 catch (Exception e)
299 {
300 m_log.ErrorFormat("[XASSET DB]: MySQL failure creating asset data {0} with name \"{1}\". Error: {2}",
301 asset.FullID, asset.Name, e.Message);
302  
303 transaction.Rollback();
304  
305 return;
306 }
307 }
308  
309 transaction.Commit();
310 }
311 }
312 }
313 }
314  
315 /// <summary>
316 /// Updates the access time of the asset if it was accessed above a given threshhold amount of time.
317 /// </summary>
318 /// <remarks>
319 /// This gives us some insight into assets which haven't ben accessed for a long period. This is only done
320 /// over the threshold time to avoid excessive database writes as assets are fetched.
321 /// </remarks>
322 /// <param name='asset'></param>
323 /// <param name='accessTime'></param>
324 private void UpdateAccessTime(AssetMetadata assetMetadata, int accessTime)
325 {
326 DateTime now = DateTime.UtcNow;
327  
328 if ((now - Utils.UnixTimeToDateTime(accessTime)).TotalDays < DaysBetweenAccessTimeUpdates)
329 return;
330  
331 lock (m_dbLock)
332 {
333 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
334 {
335 dbcon.Open();
336 MySqlCommand cmd =
337 new MySqlCommand("update XAssetsMeta set AccessTime=?AccessTime where ID=?ID", dbcon);
338  
339 try
340 {
341 using (cmd)
342 {
343 // create unix epoch time
344 cmd.Parameters.AddWithValue("?ID", assetMetadata.ID);
345 cmd.Parameters.AddWithValue("?AccessTime", (int)Utils.DateTimeToUnixTime(now));
346 cmd.ExecuteNonQuery();
347 }
348 }
349 catch (Exception e)
350 {
351 m_log.ErrorFormat(
352 "[XASSET MYSQL DB]: Failure updating access_time for asset {0} with name {1}",
353 assetMetadata.ID, assetMetadata.Name);
354 }
355 }
356 }
357 }
358  
359 /// <summary>
360 /// We assume we already have the m_dbLock.
361 /// </summary>
362 /// TODO: need to actually use the transaction.
363 /// <param name="dbcon"></param>
364 /// <param name="transaction"></param>
365 /// <param name="hash"></param>
366 /// <returns></returns>
367 private bool ExistsData(MySqlConnection dbcon, MySqlTransaction transaction, byte[] hash)
368 {
369 // m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid);
370  
371 bool exists = false;
372  
373 using (MySqlCommand cmd = new MySqlCommand("SELECT Hash FROM XAssetsData WHERE Hash=?Hash", dbcon))
374 {
375 cmd.Parameters.AddWithValue("?Hash", hash);
376  
377 try
378 {
379 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
380 {
381 if (dbReader.Read())
382 {
383 // m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid);
384 exists = true;
385 }
386 }
387 }
388 catch (Exception e)
389 {
390 m_log.ErrorFormat(
391 "[XASSETS DB]: MySql failure in ExistsData fetching hash {0}. Exception {1}{2}",
392 hash, e.Message, e.StackTrace);
393 }
394 }
395  
396 return exists;
397 }
398  
399 /// <summary>
400 /// Check if the asset exists in the database
401 /// </summary>
402 /// <param name="uuid">The asset UUID</param>
403 /// <returns>true if it exists, false otherwise.</returns>
404 public bool ExistsAsset(UUID uuid)
405 {
406 // m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid);
407  
408 bool assetExists = false;
409  
410 lock (m_dbLock)
411 {
412 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
413 {
414 dbcon.Open();
415 using (MySqlCommand cmd = new MySqlCommand("SELECT ID FROM XAssetsMeta WHERE ID=?ID", dbcon))
416 {
417 cmd.Parameters.AddWithValue("?ID", uuid.ToString());
418  
419 try
420 {
421 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
422 {
423 if (dbReader.Read())
424 {
425 // m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid);
426 assetExists = true;
427 }
428 }
429 }
430 catch (Exception e)
431 {
432 m_log.Error(string.Format("[XASSETS DB]: MySql failure fetching asset {0}", uuid), e);
433 }
434 }
435 }
436 }
437  
438 return assetExists;
439 }
440  
441  
442 /// <summary>
443 /// Returns a list of AssetMetadata objects. The list is a subset of
444 /// the entire data set offset by <paramref name="start" /> containing
445 /// <paramref name="count" /> elements.
446 /// </summary>
447 /// <param name="start">The number of results to discard from the total data set.</param>
448 /// <param name="count">The number of rows the returned list should contain.</param>
449 /// <returns>A list of AssetMetadata objects.</returns>
450 public List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
451 {
452 List<AssetMetadata> retList = new List<AssetMetadata>(count);
453  
454 lock (m_dbLock)
455 {
456 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
457 {
458 dbcon.Open();
459 MySqlCommand cmd = new MySqlCommand("SELECT Name, Description, AccessTime, AssetType, Temporary, ID, AssetFlags, CreatorID FROM XAssetsMeta LIMIT ?start, ?count", dbcon);
460 cmd.Parameters.AddWithValue("?start", start);
461 cmd.Parameters.AddWithValue("?count", count);
462  
463 try
464 {
465 using (MySqlDataReader dbReader = cmd.ExecuteReader())
466 {
467 while (dbReader.Read())
468 {
469 AssetMetadata metadata = new AssetMetadata();
470 metadata.Name = (string)dbReader["Name"];
471 metadata.Description = (string)dbReader["Description"];
472 metadata.Type = (sbyte)dbReader["AssetType"];
473 metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct.
474 metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
475 metadata.FullID = DBGuid.FromDB(dbReader["ID"]);
476 metadata.CreatorID = dbReader["CreatorID"].ToString();
477  
478 // We'll ignore this for now - it appears unused!
479 // metadata.SHA1 = dbReader["hash"]);
480  
481 UpdateAccessTime(metadata, (int)dbReader["AccessTime"]);
482  
483 retList.Add(metadata);
484 }
485 }
486 }
487 catch (Exception e)
488 {
489 m_log.Error("[XASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString());
490 }
491 }
492 }
493  
494 return retList;
495 }
496  
497 public bool Delete(string id)
498 {
499 // m_log.DebugFormat("[XASSETS DB]: Deleting asset {0}", id);
500  
501 lock (m_dbLock)
502 {
503 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
504 {
505 dbcon.Open();
506  
507 using (MySqlCommand cmd = new MySqlCommand("delete from XAssetsMeta where ID=?ID", dbcon))
508 {
509 cmd.Parameters.AddWithValue("?ID", id);
510 cmd.ExecuteNonQuery();
511 }
512  
513 // TODO: How do we deal with data from deleted assets? Probably not easily reapable unless we
514 // keep a reference count (?)
515 }
516 }
517  
518 return true;
519 }
520  
521 #endregion
522 }
523 }