1
0

MySQLXAssetData.cs 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503
  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. using System;
  28. using System.Collections.Generic;
  29. using System.Data;
  30. using System.IO;
  31. using System.IO.Compression;
  32. using System.Reflection;
  33. using System.Security.Cryptography;
  34. using System.Text;
  35. using log4net;
  36. using MySql.Data.MySqlClient;
  37. using OpenMetaverse;
  38. using OpenSim.Framework;
  39. using OpenSim.Data;
  40. namespace OpenSim.Data.MySQL
  41. {
  42. public class MySQLXAssetData : IXAssetDataPlugin
  43. {
  44. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  45. protected virtual Assembly Assembly
  46. {
  47. get { return GetType().Assembly; }
  48. }
  49. /// <summary>
  50. /// Number of days that must pass before we update the access time on an asset when it has been fetched.
  51. /// </summary>
  52. private const int DaysBetweenAccessTimeUpdates = 30;
  53. private bool m_enableCompression = false;
  54. private string m_connectionString;
  55. /// <summary>
  56. /// We can reuse this for all hashing since all methods are single-threaded through m_dbBLock
  57. /// </summary>
  58. private HashAlgorithm hasher = new SHA256CryptoServiceProvider();
  59. #region IPlugin Members
  60. public string Version { get { return "1.0.0.0"; } }
  61. /// <summary>
  62. /// <para>Initialises Asset interface</para>
  63. /// <para>
  64. /// <list type="bullet">
  65. /// <item>Loads and initialises the MySQL storage plugin.</item>
  66. /// <item>Warns and uses the obsolete mysql_connection.ini if connect string is empty.</item>
  67. /// <item>Check for migration</item>
  68. /// </list>
  69. /// </para>
  70. /// </summary>
  71. /// <param name="connect">connect string</param>
  72. public void Initialise(string connect)
  73. {
  74. m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
  75. m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
  76. m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
  77. m_log.ErrorFormat("[MYSQL XASSETDATA]: THIS PLUGIN IS STRICTLY EXPERIMENTAL.");
  78. m_log.ErrorFormat("[MYSQL XASSETDATA]: DO NOT USE FOR ANY DATA THAT YOU DO NOT MIND LOSING.");
  79. m_log.ErrorFormat("[MYSQL XASSETDATA]: DATABASE TABLES CAN CHANGE AT ANY TIME, CAUSING EXISTING DATA TO BE LOST.");
  80. m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
  81. m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
  82. m_log.ErrorFormat("[MYSQL XASSETDATA]: ***********************************************************");
  83. m_connectionString = connect;
  84. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  85. {
  86. dbcon.Open();
  87. Migration m = new Migration(dbcon, Assembly, "XAssetStore");
  88. m.Update();
  89. }
  90. }
  91. public void Initialise()
  92. {
  93. throw new NotImplementedException();
  94. }
  95. public void Dispose() { }
  96. /// <summary>
  97. /// The name of this DB provider
  98. /// </summary>
  99. public string Name
  100. {
  101. get { return "MySQL XAsset storage engine"; }
  102. }
  103. #endregion
  104. #region IAssetDataPlugin Members
  105. /// <summary>
  106. /// Fetch Asset <paramref name="assetID"/> from database
  107. /// </summary>
  108. /// <param name="assetID">Asset UUID to fetch</param>
  109. /// <returns>Return the asset</returns>
  110. /// <remarks>On failure : throw an exception and attempt to reconnect to database</remarks>
  111. public AssetBase GetAsset(UUID assetID)
  112. {
  113. // m_log.DebugFormat("[MYSQL XASSET DATA]: Looking for asset {0}", assetID);
  114. AssetBase asset = null;
  115. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  116. {
  117. dbcon.Open();
  118. using (MySqlCommand cmd = new MySqlCommand(
  119. "SELECT Name, Description, AccessTime, AssetType, Local, Temporary, AssetFlags, CreatorID, Data FROM XAssetsMeta JOIN XAssetsData ON XAssetsMeta.Hash = XAssetsData.Hash WHERE ID=?ID",
  120. dbcon))
  121. {
  122. cmd.Parameters.AddWithValue("?ID", assetID.ToString());
  123. try
  124. {
  125. using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  126. {
  127. if (dbReader.Read())
  128. {
  129. asset = new AssetBase(assetID, (string)dbReader["Name"], (sbyte)dbReader["AssetType"], dbReader["CreatorID"].ToString());
  130. asset.Data = (byte[])dbReader["Data"];
  131. asset.Description = (string)dbReader["Description"];
  132. string local = dbReader["Local"].ToString();
  133. if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
  134. asset.Local = true;
  135. else
  136. asset.Local = false;
  137. asset.Temporary = Convert.ToBoolean(dbReader["Temporary"]);
  138. asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
  139. if (m_enableCompression)
  140. {
  141. using (GZipStream decompressionStream = new GZipStream(new MemoryStream(asset.Data), CompressionMode.Decompress))
  142. {
  143. MemoryStream outputStream = new MemoryStream();
  144. WebUtil.CopyStream(decompressionStream, outputStream, int.MaxValue);
  145. // int compressedLength = asset.Data.Length;
  146. asset.Data = outputStream.ToArray();
  147. // m_log.DebugFormat(
  148. // "[XASSET DB]: Decompressed {0} {1} to {2} bytes from {3}",
  149. // asset.ID, asset.Name, asset.Data.Length, compressedLength);
  150. }
  151. }
  152. UpdateAccessTime(asset.Metadata, (int)dbReader["AccessTime"]);
  153. }
  154. }
  155. }
  156. catch (Exception e)
  157. {
  158. m_log.Error(string.Format("[MYSQL XASSET DATA]: Failure fetching asset {0}", assetID), e);
  159. }
  160. }
  161. }
  162. return asset;
  163. }
  164. /// <summary>
  165. /// Create an asset in database, or update it if existing.
  166. /// </summary>
  167. /// <param name="asset">Asset UUID to create</param>
  168. /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
  169. public void StoreAsset(AssetBase asset)
  170. {
  171. // m_log.DebugFormat("[XASSETS DB]: Storing asset {0} {1}", asset.Name, asset.ID);
  172. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  173. {
  174. dbcon.Open();
  175. using (MySqlTransaction transaction = dbcon.BeginTransaction())
  176. {
  177. string assetName = asset.Name;
  178. if (asset.Name.Length > AssetBase.MAX_ASSET_NAME)
  179. {
  180. assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME);
  181. m_log.WarnFormat(
  182. "[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
  183. asset.Name, asset.ID, asset.Name.Length, assetName.Length);
  184. }
  185. string assetDescription = asset.Description;
  186. if (asset.Description.Length > AssetBase.MAX_ASSET_DESC)
  187. {
  188. assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC);
  189. m_log.WarnFormat(
  190. "[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
  191. asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
  192. }
  193. if (m_enableCompression)
  194. {
  195. MemoryStream outputStream = new MemoryStream();
  196. using (GZipStream compressionStream = new GZipStream(outputStream, CompressionMode.Compress, false))
  197. {
  198. // Console.WriteLine(WebUtil.CopyTo(new MemoryStream(asset.Data), compressionStream, int.MaxValue));
  199. // We have to close the compression stream in order to make sure it writes everything out to the underlying memory output stream.
  200. compressionStream.Close();
  201. byte[] compressedData = outputStream.ToArray();
  202. asset.Data = compressedData;
  203. }
  204. }
  205. byte[] hash = hasher.ComputeHash(asset.Data);
  206. // m_log.DebugFormat(
  207. // "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}",
  208. // asset.ID, asset.Name, hash, compressedData.Length);
  209. try
  210. {
  211. using (MySqlCommand cmd =
  212. new MySqlCommand(
  213. "replace INTO XAssetsMeta(ID, Hash, Name, Description, AssetType, Local, Temporary, CreateTime, AccessTime, AssetFlags, CreatorID)" +
  214. "VALUES(?ID, ?Hash, ?Name, ?Description, ?AssetType, ?Local, ?Temporary, ?CreateTime, ?AccessTime, ?AssetFlags, ?CreatorID)",
  215. dbcon))
  216. {
  217. // create unix epoch time
  218. int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
  219. cmd.Parameters.AddWithValue("?ID", asset.ID);
  220. cmd.Parameters.AddWithValue("?Hash", hash);
  221. cmd.Parameters.AddWithValue("?Name", assetName);
  222. cmd.Parameters.AddWithValue("?Description", assetDescription);
  223. cmd.Parameters.AddWithValue("?AssetType", asset.Type);
  224. cmd.Parameters.AddWithValue("?Local", asset.Local);
  225. cmd.Parameters.AddWithValue("?Temporary", asset.Temporary);
  226. cmd.Parameters.AddWithValue("?CreateTime", now);
  227. cmd.Parameters.AddWithValue("?AccessTime", now);
  228. cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID);
  229. cmd.Parameters.AddWithValue("?AssetFlags", (int)asset.Flags);
  230. cmd.ExecuteNonQuery();
  231. }
  232. }
  233. catch (Exception e)
  234. {
  235. m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset metadata {0} with name \"{1}\". Error: {2}",
  236. asset.FullID, asset.Name, e.Message);
  237. transaction.Rollback();
  238. return;
  239. }
  240. if (!ExistsData(dbcon, transaction, hash))
  241. {
  242. try
  243. {
  244. using (MySqlCommand cmd =
  245. new MySqlCommand(
  246. "INSERT INTO XAssetsData(Hash, Data) VALUES(?Hash, ?Data)",
  247. dbcon))
  248. {
  249. cmd.Parameters.AddWithValue("?Hash", hash);
  250. cmd.Parameters.AddWithValue("?Data", asset.Data);
  251. cmd.ExecuteNonQuery();
  252. }
  253. }
  254. catch (Exception e)
  255. {
  256. m_log.ErrorFormat("[XASSET DB]: MySQL failure creating asset data {0} with name \"{1}\". Error: {2}",
  257. asset.FullID, asset.Name, e.Message);
  258. transaction.Rollback();
  259. return;
  260. }
  261. }
  262. transaction.Commit();
  263. }
  264. }
  265. }
  266. /// <summary>
  267. /// Updates the access time of the asset if it was accessed above a given threshhold amount of time.
  268. /// </summary>
  269. /// <remarks>
  270. /// This gives us some insight into assets which haven't ben accessed for a long period. This is only done
  271. /// over the threshold time to avoid excessive database writes as assets are fetched.
  272. /// </remarks>
  273. /// <param name='asset'></param>
  274. /// <param name='accessTime'></param>
  275. private void UpdateAccessTime(AssetMetadata assetMetadata, int accessTime)
  276. {
  277. DateTime now = DateTime.UtcNow;
  278. if ((now - Utils.UnixTimeToDateTime(accessTime)).TotalDays < DaysBetweenAccessTimeUpdates)
  279. return;
  280. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  281. {
  282. dbcon.Open();
  283. MySqlCommand cmd =
  284. new MySqlCommand("update XAssetsMeta set AccessTime=?AccessTime where ID=?ID", dbcon);
  285. try
  286. {
  287. using (cmd)
  288. {
  289. // create unix epoch time
  290. cmd.Parameters.AddWithValue("?ID", assetMetadata.ID);
  291. cmd.Parameters.AddWithValue("?AccessTime", (int)Utils.DateTimeToUnixTime(now));
  292. cmd.ExecuteNonQuery();
  293. }
  294. }
  295. catch (Exception)
  296. {
  297. m_log.ErrorFormat(
  298. "[XASSET MYSQL DB]: Failure updating access_time for asset {0} with name {1}",
  299. assetMetadata.ID, assetMetadata.Name);
  300. }
  301. }
  302. }
  303. /// <summary>
  304. /// We assume we already have the m_dbLock.
  305. /// </summary>
  306. /// TODO: need to actually use the transaction.
  307. /// <param name="dbcon"></param>
  308. /// <param name="transaction"></param>
  309. /// <param name="hash"></param>
  310. /// <returns></returns>
  311. private bool ExistsData(MySqlConnection dbcon, MySqlTransaction transaction, byte[] hash)
  312. {
  313. // m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid);
  314. bool exists = false;
  315. using (MySqlCommand cmd = new MySqlCommand("SELECT Hash FROM XAssetsData WHERE Hash=?Hash", dbcon))
  316. {
  317. cmd.Parameters.AddWithValue("?Hash", hash);
  318. try
  319. {
  320. using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  321. {
  322. if (dbReader.Read())
  323. {
  324. // m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid);
  325. exists = true;
  326. }
  327. }
  328. }
  329. catch (Exception e)
  330. {
  331. m_log.ErrorFormat(
  332. "[XASSETS DB]: MySql failure in ExistsData fetching hash {0}. Exception {1}{2}",
  333. hash, e.Message, e.StackTrace);
  334. }
  335. }
  336. return exists;
  337. }
  338. /// <summary>
  339. /// Check if the assets exist in the database.
  340. /// </summary>
  341. /// <param name="uuids">The asset UUID's</param>
  342. /// <returns>For each asset: true if it exists, false otherwise</returns>
  343. public bool[] AssetsExist(UUID[] uuids)
  344. {
  345. if (uuids.Length == 0)
  346. return new bool[0];
  347. HashSet<UUID> exists = new HashSet<UUID>();
  348. string ids = "'" + string.Join("','", uuids) + "'";
  349. string sql = string.Format("SELECT ID FROM assets WHERE ID IN ({0})", ids);
  350. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  351. {
  352. dbcon.Open();
  353. using (MySqlCommand cmd = new MySqlCommand(sql, dbcon))
  354. {
  355. using (MySqlDataReader dbReader = cmd.ExecuteReader())
  356. {
  357. while (dbReader.Read())
  358. {
  359. UUID id = DBGuid.FromDB(dbReader["ID"]);
  360. exists.Add(id);
  361. }
  362. }
  363. }
  364. }
  365. bool[] results = new bool[uuids.Length];
  366. for (int i = 0; i < uuids.Length; i++)
  367. results[i] = exists.Contains(uuids[i]);
  368. return results;
  369. }
  370. /// <summary>
  371. /// Returns a list of AssetMetadata objects. The list is a subset of
  372. /// the entire data set offset by <paramref name="start" /> containing
  373. /// <paramref name="count" /> elements.
  374. /// </summary>
  375. /// <param name="start">The number of results to discard from the total data set.</param>
  376. /// <param name="count">The number of rows the returned list should contain.</param>
  377. /// <returns>A list of AssetMetadata objects.</returns>
  378. public List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
  379. {
  380. List<AssetMetadata> retList = new List<AssetMetadata>(count);
  381. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  382. {
  383. dbcon.Open();
  384. MySqlCommand cmd = new MySqlCommand("SELECT Name, Description, AccessTime, AssetType, Temporary, ID, AssetFlags, CreatorID FROM XAssetsMeta LIMIT ?start, ?count", dbcon);
  385. cmd.Parameters.AddWithValue("?start", start);
  386. cmd.Parameters.AddWithValue("?count", count);
  387. try
  388. {
  389. using (MySqlDataReader dbReader = cmd.ExecuteReader())
  390. {
  391. while (dbReader.Read())
  392. {
  393. AssetMetadata metadata = new AssetMetadata();
  394. metadata.Name = (string)dbReader["Name"];
  395. metadata.Description = (string)dbReader["Description"];
  396. metadata.Type = (sbyte)dbReader["AssetType"];
  397. metadata.Temporary = Convert.ToBoolean(dbReader["Temporary"]); // Not sure if this is correct.
  398. metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["AssetFlags"]);
  399. metadata.FullID = DBGuid.FromDB(dbReader["ID"]);
  400. metadata.CreatorID = dbReader["CreatorID"].ToString();
  401. // We'll ignore this for now - it appears unused!
  402. // metadata.SHA1 = dbReader["hash"]);
  403. UpdateAccessTime(metadata, (int)dbReader["AccessTime"]);
  404. retList.Add(metadata);
  405. }
  406. }
  407. }
  408. catch (Exception e)
  409. {
  410. m_log.Error("[XASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString());
  411. }
  412. }
  413. return retList;
  414. }
  415. public bool Delete(string id)
  416. {
  417. // m_log.DebugFormat("[XASSETS DB]: Deleting asset {0}", id);
  418. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  419. {
  420. dbcon.Open();
  421. using (MySqlCommand cmd = new MySqlCommand("delete from XAssetsMeta where ID=?ID", dbcon))
  422. {
  423. cmd.Parameters.AddWithValue("?ID", id);
  424. cmd.ExecuteNonQuery();
  425. }
  426. // TODO: How do we deal with data from deleted assets? Probably not easily reapable unless we
  427. // keep a reference count (?)
  428. }
  429. return true;
  430. }
  431. #endregion
  432. }
  433. }