OpenSim
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Events Macros
MySQLXAssetData.cs
Go to the documentation of this file.
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 {
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 
56  private const int DaysBetweenAccessTimeUpdates = 30;
57 
58  private bool m_enableCompression = false;
59  private string m_connectionString;
60 
64  private HashAlgorithm hasher = new SHA256CryptoServiceProvider();
65 
66  #region IPlugin Members
67 
68  public string Version { get { return "1.0.0.0"; } }
69 
81  public void Initialise(string connect)
82  {
83  m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
84  m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
85  m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
86  m_log.ErrorFormat("[MYSQL XASSETDATA]: THIS PLUGIN IS STRICTLY EXPERIMENTAL.");
87  m_log.ErrorFormat("[MYSQL XASSETDATA]: DO NOT USE FOR ANY DATA THAT YOU DO NOT MIND LOSING.");
88  m_log.ErrorFormat("[MYSQL XASSETDATA]: DATABASE TABLES CAN CHANGE AT ANY TIME, CAUSING EXISTING DATA TO BE LOST.");
89  m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
90  m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
91  m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
92 
93  m_connectionString = connect;
94 
95  using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
96  {
97  dbcon.Open();
98  Migration m = new Migration(dbcon, Assembly, "XAssetStore");
99  m.Update();
100  }
101  }
102 
103  public void Initialise()
104  {
105  throw new NotImplementedException();
106  }
107 
108  public void Dispose() { }
109 
113  public string Name
114  {
115  get { return "MySQL XAsset storage engine"; }
116  }
117 
118  #endregion
119 
120  #region IAssetDataPlugin Members
121 
128  public AssetBase GetAsset(UUID assetID)
129  {
130 // m_log.DebugFormat("[MYSQL XASSET DATA]: Looking for asset {0}", assetID);
131 
132  AssetBase asset = null;
133 
134  using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
135  {
136  dbcon.Open();
137 
138  using (MySqlCommand cmd = new MySqlCommand(
139  "SELECT Name, Description, AccessTime, AssetType, Local, Temporary, AssetFlags, CreatorID, Data FROM XAssetsMeta JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ID=?ID",
140  dbcon))
141  {
142  cmd.Parameters.AddWithValue("?ID", assetID.ToString());
143 
144  try
145  {
146  using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
147  {
148  if (dbReader.Read())
149  {
150  asset = new AssetBase(assetID, (string)dbReader["Name"], (sbyte)dbReader["AssetType"], dbReader["CreatorID"].ToString());
151  asset.Data = (byte[])dbReader["Data"];
152  asset.Description = (string)dbReader["Description"];
153 
154  string local = dbReader["Local"].ToString();
155  if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
156  asset.Local = true;
157  else
158  asset.Local = false;
159 
160  asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]);
161  asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
162 
163  if (m_enableCompression)
164  {
165  using (GZipStream decompressionStream = new GZipStream(new MemoryStream(asset.Data), CompressionMode.Decompress))
166  {
167  MemoryStream outputStream = new MemoryStream();
168  WebUtil.CopyStream(decompressionStream, outputStream, int.MaxValue);
169 // int compressedLength = asset.Data.Length;
170  asset.Data = outputStream.ToArray();
171 
172 // m_log.DebugFormat(
173 // "[XASSET DB]: Decompressed {0} {1} to {2} bytes from {3}",
174 // asset.ID, asset.Name, asset.Data.Length, compressedLength);
175  }
176  }
177 
178  UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]);
179  }
180  }
181  }
182  catch (Exception e)
183  {
184  m_log.Error(string.Format("[MYSQL XASSET DATA]: Failure fetching asset {0}", assetID), e);
185  }
186  }
187  }
188 
189  return asset;
190  }
191 
197  public void StoreAsset(AssetBase asset)
198  {
199 // m_log.DebugFormat("[XASSETS DB]: Storing asset {0} {1}", asset.Name, asset.ID);
200 
201  using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
202  {
203  dbcon.Open();
204 
205  using (MySqlTransaction transaction = dbcon.BeginTransaction())
206  {
207  string assetName = asset.Name;
208  if (asset.Name.Length > AssetBase.MAX_ASSET_NAME)
209  {
210  assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME);
211  m_log.WarnFormat(
212  "[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
213  asset.Name, asset.ID, asset.Name.Length, assetName.Length);
214  }
215 
216  string assetDescription = asset.Description;
217  if (asset.Description.Length > AssetBase.MAX_ASSET_DESC)
218  {
219  assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC);
220  m_log.WarnFormat(
221  "[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
222  asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
223  }
224 
225  if (m_enableCompression)
226  {
227  MemoryStream outputStream = new MemoryStream();
228 
229  using (GZipStream compressionStream = new GZipStream(outputStream, CompressionMode.Compress, false))
230  {
231 // Console.WriteLine(WebUtil.CopyTo(new MemoryStream(asset.Data), compressionStream, int.MaxValue));
232  // We have to close the compression stream in order to make sure it writes everything out to the underlying memory output stream.
233  compressionStream.Close();
234  byte[] compressedData = outputStream.ToArray();
235  asset.Data = compressedData;
236  }
237  }
238 
239  byte[] hash = hasher.ComputeHash(asset.Data);
240 
241 // m_log.DebugFormat(
242 // "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}",
243 // asset.ID, asset.Name, hash, compressedData.Length);
244 
245  try
246  {
247  using (MySqlCommand cmd =
248  new MySqlCommand(
249  "replace INTO XAssetsMeta(ID, Hash, Name, Description, AssetType, Local, Temporary, CreateTime, AccessTime, AssetFlags, CreatorID)" +
250  "VALUES(?ID, ?Hash, ?Name, ?Description, ?AssetType, ?Local, ?Temporary, ?CreateTime, ?AccessTime, ?AssetFlags, ?CreatorID)",
251  dbcon))
252  {
253  // create unix epoch time
254  int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
255  cmd.Parameters.AddWithValue("?ID", asset.ID);
256  cmd.Parameters.AddWithValue("?Hash", hash);
257  cmd.Parameters.AddWithValue("?Name", assetName);
258  cmd.Parameters.AddWithValue("?Description", assetDescription);
259  cmd.Parameters.AddWithValue("?AssetType", asset.Type);
260  cmd.Parameters.AddWithValue("?Local", asset.Local);
261  cmd.Parameters.AddWithValue("?Temporary", asset.Temporary);
262  cmd.Parameters.AddWithValue("?CreateTime", now);
263  cmd.Parameters.AddWithValue("?AccessTime", now);
264  cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID);
265  cmd.Parameters.AddWithValue("?AssetFlags", (int)asset.Flags);
266  cmd.ExecuteNonQuery();
267  }
268  }
269  catch (Exception e)
270  {
271  m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset metadata {0} with name \"{1}\". Error: {2}",
272  asset.FullID, asset.Name, e.Message);
273 
274  transaction.Rollback();
275 
276  return;
277  }
278 
279  if (!ExistsData(dbcon, transaction, hash))
280  {
281  try
282  {
283  using (MySqlCommand cmd =
284  new MySqlCommand(
285  "INSERT INTO XAssetsData(Hash, Data) VALUES(?Hash, ?Data)",
286  dbcon))
287  {
288  cmd.Parameters.AddWithValue("?Hash", hash);
289  cmd.Parameters.AddWithValue("?Data", asset.Data);
290  cmd.ExecuteNonQuery();
291  }
292  }
293  catch (Exception e)
294  {
295  m_log.ErrorFormat("[XASSET DB]: MySQL failure creating asset data {0} with name \"{1}\". Error: {2}",
296  asset.FullID, asset.Name, e.Message);
297 
298  transaction.Rollback();
299 
300  return;
301  }
302  }
303 
304  transaction.Commit();
305  }
306  }
307  }
308 
318  private void UpdateAccessTime(AssetMetadata assetMetadata, int accessTime)
319  {
320  DateTime now = DateTime.UtcNow;
321 
322  if ((now - Utils.UnixTimeToDateTime(accessTime)).TotalDays < DaysBetweenAccessTimeUpdates)
323  return;
324 
325  using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
326  {
327  dbcon.Open();
328  MySqlCommand cmd =
329  new MySqlCommand("update XAssetsMeta set AccessTime=?AccessTime where ID=?ID", dbcon);
330 
331  try
332  {
333  using (cmd)
334  {
335  // create unix epoch time
336  cmd.Parameters.AddWithValue("?ID", assetMetadata.ID);
337  cmd.Parameters.AddWithValue("?AccessTime", (int)Utils.DateTimeToUnixTime(now));
338  cmd.ExecuteNonQuery();
339  }
340  }
341  catch (Exception)
342  {
343  m_log.ErrorFormat(
344  "[XASSET MYSQL DB]: Failure updating access_time for asset {0} with name {1}",
345  assetMetadata.ID, assetMetadata.Name);
346  }
347  }
348  }
349 
358  private bool ExistsData(MySqlConnection dbcon, MySqlTransaction transaction, byte[] hash)
359  {
360 // m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid);
361 
362  bool exists = false;
363 
364  using (MySqlCommand cmd = new MySqlCommand("SELECT Hash FROM XAssetsData WHERE Hash=?Hash", dbcon))
365  {
366  cmd.Parameters.AddWithValue("?Hash", hash);
367 
368  try
369  {
370  using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
371  {
372  if (dbReader.Read())
373  {
374 // m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid);
375  exists = true;
376  }
377  }
378  }
379  catch (Exception e)
380  {
381  m_log.ErrorFormat(
382  "[XASSETS DB]: MySql failure in ExistsData fetching hash {0}. Exception {1}{2}",
383  hash, e.Message, e.StackTrace);
384  }
385  }
386 
387  return exists;
388  }
389 
395  public bool[] AssetsExist(UUID[] uuids)
396  {
397  if (uuids.Length == 0)
398  return new bool[0];
399 
400  HashSet<UUID> exists = new HashSet<UUID>();
401 
402  string ids = "'" + string.Join("','", uuids) + "'";
403  string sql = string.Format("SELECT ID FROM assets WHERE ID IN ({0})", ids);
404 
405  using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
406  {
407  dbcon.Open();
408  using (MySqlCommand cmd = new MySqlCommand(sql, dbcon))
409  {
410  using (MySqlDataReader dbReader = cmd.ExecuteReader())
411  {
412  while (dbReader.Read())
413  {
414  UUID id = DBGuid.FromDB(dbReader["ID"]);
415  exists.Add(id);
416  }
417  }
418  }
419  }
420 
421  bool[] results = new bool[uuids.Length];
422  for (int i = 0; i < uuids.Length; i++)
423  results[i] = exists.Contains(uuids[i]);
424  return results;
425  }
426 
427 
436  public List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
437  {
438  List<AssetMetadata> retList = new List<AssetMetadata>(count);
439 
440  using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
441  {
442  dbcon.Open();
443  MySqlCommand cmd = new MySqlCommand("SELECT Name, Description, AccessTime, AssetType, Temporary, ID, AssetFlags, CreatorID FROM XAssetsMeta LIMIT ?start, ?count", dbcon);
444  cmd.Parameters.AddWithValue("?start", start);
445  cmd.Parameters.AddWithValue("?count", count);
446 
447  try
448  {
449  using (MySqlDataReader dbReader = cmd.ExecuteReader())
450  {
451  while (dbReader.Read())
452  {
453  AssetMetadata metadata = new AssetMetadata();
454  metadata.Name = (string)dbReader["Name"];
455  metadata.Description = (string)dbReader["Description"];
456  metadata.Type = (sbyte)dbReader["AssetType"];
457  metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct.
458  metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
459  metadata.FullID = DBGuid.FromDB(dbReader["ID"]);
460  metadata.CreatorID = dbReader["CreatorID"].ToString();
461 
462  // We'll ignore this for now - it appears unused!
463 // metadata.SHA1 = dbReader["hash"]);
464 
465  UpdateAccessTime(metadata, (int)dbReader["AccessTime"]);
466 
467  retList.Add(metadata);
468  }
469  }
470  }
471  catch (Exception e)
472  {
473  m_log.Error("[XASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString());
474  }
475  }
476 
477  return retList;
478  }
479 
480  public bool Delete(string id)
481  {
482 // m_log.DebugFormat("[XASSETS DB]: Deleting asset {0}", id);
483 
484  using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
485  {
486  dbcon.Open();
487 
488  using (MySqlCommand cmd = new MySqlCommand("delete from XAssetsMeta where ID=?ID", dbcon))
489  {
490  cmd.Parameters.AddWithValue("?ID", id);
491  cmd.ExecuteNonQuery();
492  }
493 
494  // TODO: How do we deal with data from deleted assets? Probably not easily reapable unless we
495  // keep a reference count (?)
496  }
497 
498  return true;
499  }
500 
501  #endregion
502  }
503 }
void Initialise()
Default-initialises the plugin
OpenSim.Server.Handlers.Simulation.Utils Utils
bool[] AssetsExist(UUID[] uuids)
Check if the assets exist in the database.
Ionic.Zlib.GZipStream GZipStream
Asset class. All Assets are reference by this class or a class derived from this class ...
Definition: AssetBase.cs:49
static readonly int MAX_ASSET_NAME
Definition: AssetBase.cs:53
static readonly int MAX_ASSET_DESC
Definition: AssetBase.cs:54
List< AssetMetadata > FetchAssetMetadataSet(int start, int count)
Returns a list of AssetMetadata objects. The list is a subset of the entire data set offset by start ...
void StoreAsset(AssetBase asset)
Create an asset in database, or update it if existing.
This interface exists to distinguish between the normal IAssetDataPlugin and the one used by XAssetSe...
Ionic.Zlib.CompressionMode CompressionMode
AssetBase GetAsset(UUID assetID)
Fetch Asset assetID from database