clockwerk-opensim – 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  
61 /// <summary>
62 /// We can reuse this for all hashing since all methods are single-threaded through m_dbBLock
63 /// </summary>
64 private HashAlgorithm hasher = new SHA256CryptoServiceProvider();
65  
66 #region IPlugin Members
67  
68 public string Version { get { return "1.0.0.0"; } }
69  
70 /// <summary>
71 /// <para>Initialises Asset interface</para>
72 /// <para>
73 /// <list type="bullet">
74 /// <item>Loads and initialises the MySQL storage plugin.</item>
75 /// <item>Warns and uses the obsolete mysql_connection.ini if connect string is empty.</item>
76 /// <item>Check for migration</item>
77 /// </list>
78 /// </para>
79 /// </summary>
80 /// <param name="connect">connect string</param>
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  
110 /// <summary>
111 /// The name of this DB provider
112 /// </summary>
113 public string Name
114 {
115 get { return "MySQL XAsset storage engine"; }
116 }
117  
118 #endregion
119  
120 #region IAssetDataPlugin Members
121  
122 /// <summary>
123 /// Fetch Asset <paramref name="assetID"/> from database
124 /// </summary>
125 /// <param name="assetID">Asset UUID to fetch</param>
126 /// <returns>Return the asset</returns>
127 /// <remarks>On failure : throw an exception and attempt to reconnect to database</remarks>
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  
192 /// <summary>
193 /// Create an asset in database, or update it if existing.
194 /// </summary>
195 /// <param name="asset">Asset UUID to create</param>
196 /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
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  
309 /// <summary>
310 /// Updates the access time of the asset if it was accessed above a given threshhold amount of time.
311 /// </summary>
312 /// <remarks>
313 /// This gives us some insight into assets which haven't ben accessed for a long period. This is only done
314 /// over the threshold time to avoid excessive database writes as assets are fetched.
315 /// </remarks>
316 /// <param name='asset'></param>
317 /// <param name='accessTime'></param>
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  
350 /// <summary>
351 /// We assume we already have the m_dbLock.
352 /// </summary>
353 /// TODO: need to actually use the transaction.
354 /// <param name="dbcon"></param>
355 /// <param name="transaction"></param>
356 /// <param name="hash"></param>
357 /// <returns></returns>
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  
390 /// <summary>
391 /// Check if the assets exist in the database.
392 /// </summary>
393 /// <param name="uuids">The asset UUID's</param>
394 /// <returns>For each asset: true if it exists, false otherwise</returns>
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  
428 /// <summary>
429 /// Returns a list of AssetMetadata objects. The list is a subset of
430 /// the entire data set offset by <paramref name="start" /> containing
431 /// <paramref name="count" /> elements.
432 /// </summary>
433 /// <param name="start">The number of results to discard from the total data set.</param>
434 /// <param name="count">The number of rows the returned list should contain.</param>
435 /// <returns>A list of AssetMetadata objects.</returns>
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 }