MySQLAssetData.cs 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369
  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.Data;
  29. using System.Reflection;
  30. using System.Collections.Generic;
  31. using log4net;
  32. using MySql.Data.MySqlClient;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. using OpenSim.Data;
  36. namespace OpenSim.Data.MySQL
  37. {
  38. /// <summary>
  39. /// A MySQL Interface for the Asset Server
  40. /// </summary>
  41. public class MySQLAssetData : AssetDataBase
  42. {
  43. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  44. private string m_connectionString;
  45. private object m_dbLock = new object();
  46. protected virtual Assembly Assembly
  47. {
  48. get { return GetType().Assembly; }
  49. }
  50. #region IPlugin Members
  51. public override string Version { get { return "1.0.0.0"; } }
  52. /// <summary>
  53. /// <para>Initialises Asset interface</para>
  54. /// <para>
  55. /// <list type="bullet">
  56. /// <item>Loads and initialises the MySQL storage plugin.</item>
  57. /// <item>Warns and uses the obsolete mysql_connection.ini if connect string is empty.</item>
  58. /// <item>Check for migration</item>
  59. /// </list>
  60. /// </para>
  61. /// </summary>
  62. /// <param name="connect">connect string</param>
  63. public override void Initialise(string connect)
  64. {
  65. m_connectionString = connect;
  66. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  67. {
  68. dbcon.Open();
  69. Migration m = new Migration(dbcon, Assembly, "AssetStore");
  70. m.Update();
  71. }
  72. }
  73. public override void Initialise()
  74. {
  75. throw new NotImplementedException();
  76. }
  77. public override void Dispose() { }
  78. /// <summary>
  79. /// The name of this DB provider
  80. /// </summary>
  81. override public string Name
  82. {
  83. get { return "MySQL Asset storage engine"; }
  84. }
  85. #endregion
  86. #region IAssetDataPlugin Members
  87. /// <summary>
  88. /// Fetch Asset <paramref name="assetID"/> from database
  89. /// </summary>
  90. /// <param name="assetID">Asset UUID to fetch</param>
  91. /// <returns>Return the asset</returns>
  92. /// <remarks>On failure : throw an exception and attempt to reconnect to database</remarks>
  93. override public AssetBase GetAsset(UUID assetID)
  94. {
  95. AssetBase asset = null;
  96. lock (m_dbLock)
  97. {
  98. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  99. {
  100. dbcon.Open();
  101. using (MySqlCommand cmd = new MySqlCommand(
  102. "SELECT name, description, assetType, local, temporary, asset_flags, CreatorID, data FROM assets WHERE id=?id",
  103. dbcon))
  104. {
  105. cmd.Parameters.AddWithValue("?id", assetID.ToString());
  106. try
  107. {
  108. using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  109. {
  110. if (dbReader.Read())
  111. {
  112. asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"], dbReader["CreatorID"].ToString());
  113. asset.Data = (byte[])dbReader["data"];
  114. asset.Description = (string)dbReader["description"];
  115. string local = dbReader["local"].ToString();
  116. if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
  117. asset.Local = true;
  118. else
  119. asset.Local = false;
  120. asset.Temporary = Convert.ToBoolean(dbReader["temporary"]);
  121. asset.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
  122. }
  123. }
  124. }
  125. catch (Exception e)
  126. {
  127. m_log.Error("[ASSETS DB]: MySql failure fetching asset " + assetID + ": " + e.Message);
  128. }
  129. }
  130. }
  131. }
  132. return asset;
  133. }
  134. /// <summary>
  135. /// Create an asset in database, or update it if existing.
  136. /// </summary>
  137. /// <param name="asset">Asset UUID to create</param>
  138. /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
  139. override public void StoreAsset(AssetBase asset)
  140. {
  141. lock (m_dbLock)
  142. {
  143. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  144. {
  145. dbcon.Open();
  146. MySqlCommand cmd =
  147. new MySqlCommand(
  148. "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, asset_flags, CreatorID, data)" +
  149. "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?asset_flags, ?CreatorID, ?data)",
  150. dbcon);
  151. string assetName = asset.Name;
  152. if (asset.Name.Length > 64)
  153. {
  154. assetName = asset.Name.Substring(0, 64);
  155. m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add");
  156. }
  157. string assetDescription = asset.Description;
  158. if (asset.Description.Length > 64)
  159. {
  160. assetDescription = asset.Description.Substring(0, 64);
  161. m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add");
  162. }
  163. // need to ensure we dispose
  164. try
  165. {
  166. using (cmd)
  167. {
  168. // create unix epoch time
  169. int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
  170. cmd.Parameters.AddWithValue("?id", asset.ID);
  171. cmd.Parameters.AddWithValue("?name", assetName);
  172. cmd.Parameters.AddWithValue("?description", assetDescription);
  173. cmd.Parameters.AddWithValue("?assetType", asset.Type);
  174. cmd.Parameters.AddWithValue("?local", asset.Local);
  175. cmd.Parameters.AddWithValue("?temporary", asset.Temporary);
  176. cmd.Parameters.AddWithValue("?create_time", now);
  177. cmd.Parameters.AddWithValue("?access_time", now);
  178. cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID);
  179. cmd.Parameters.AddWithValue("?asset_flags", (int)asset.Flags);
  180. cmd.Parameters.AddWithValue("?data", asset.Data);
  181. cmd.ExecuteNonQuery();
  182. cmd.Dispose();
  183. }
  184. }
  185. catch (Exception e)
  186. {
  187. m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Error: {2}",
  188. asset.FullID, asset.Name, e.Message);
  189. }
  190. }
  191. }
  192. }
  193. private void UpdateAccessTime(AssetBase asset)
  194. {
  195. lock (m_dbLock)
  196. {
  197. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  198. {
  199. dbcon.Open();
  200. MySqlCommand cmd =
  201. new MySqlCommand("update assets set access_time=?access_time where id=?id",
  202. dbcon);
  203. // need to ensure we dispose
  204. try
  205. {
  206. using (cmd)
  207. {
  208. // create unix epoch time
  209. int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
  210. cmd.Parameters.AddWithValue("?id", asset.ID);
  211. cmd.Parameters.AddWithValue("?access_time", now);
  212. cmd.ExecuteNonQuery();
  213. cmd.Dispose();
  214. }
  215. }
  216. catch (Exception e)
  217. {
  218. m_log.ErrorFormat(
  219. "[ASSETS DB]: " +
  220. "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString()
  221. + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name);
  222. }
  223. }
  224. }
  225. }
  226. /// <summary>
  227. /// Check if the asset exists in the database
  228. /// </summary>
  229. /// <param name="uuid">The asset UUID</param>
  230. /// <returns>true if it exists, false otherwise.</returns>
  231. override public bool ExistsAsset(UUID uuid)
  232. {
  233. // m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid);
  234. bool assetExists = false;
  235. lock (m_dbLock)
  236. {
  237. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  238. {
  239. dbcon.Open();
  240. using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM assets WHERE id=?id", dbcon))
  241. {
  242. cmd.Parameters.AddWithValue("?id", uuid.ToString());
  243. try
  244. {
  245. using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  246. {
  247. if (dbReader.Read())
  248. {
  249. // m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid);
  250. assetExists = true;
  251. }
  252. }
  253. }
  254. catch (Exception e)
  255. {
  256. m_log.ErrorFormat(
  257. "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid);
  258. }
  259. }
  260. }
  261. }
  262. return assetExists;
  263. }
  264. /// <summary>
  265. /// Returns a list of AssetMetadata objects. The list is a subset of
  266. /// the entire data set offset by <paramref name="start" /> containing
  267. /// <paramref name="count" /> elements.
  268. /// </summary>
  269. /// <param name="start">The number of results to discard from the total data set.</param>
  270. /// <param name="count">The number of rows the returned list should contain.</param>
  271. /// <returns>A list of AssetMetadata objects.</returns>
  272. public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
  273. {
  274. List<AssetMetadata> retList = new List<AssetMetadata>(count);
  275. lock (m_dbLock)
  276. {
  277. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  278. {
  279. dbcon.Open();
  280. MySqlCommand cmd = new MySqlCommand("SELECT name,description,assetType,temporary,id,asset_flags,CreatorID FROM assets LIMIT ?start, ?count", dbcon);
  281. cmd.Parameters.AddWithValue("?start", start);
  282. cmd.Parameters.AddWithValue("?count", count);
  283. try
  284. {
  285. using (MySqlDataReader dbReader = cmd.ExecuteReader())
  286. {
  287. while (dbReader.Read())
  288. {
  289. AssetMetadata metadata = new AssetMetadata();
  290. metadata.Name = (string)dbReader["name"];
  291. metadata.Description = (string)dbReader["description"];
  292. metadata.Type = (sbyte)dbReader["assetType"];
  293. metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct.
  294. metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
  295. metadata.FullID = DBGuid.FromDB(dbReader["id"]);
  296. metadata.CreatorID = dbReader["CreatorID"].ToString();
  297. // Current SHA1s are not stored/computed.
  298. metadata.SHA1 = new byte[] { };
  299. retList.Add(metadata);
  300. }
  301. }
  302. }
  303. catch (Exception e)
  304. {
  305. m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString());
  306. }
  307. }
  308. }
  309. return retList;
  310. }
  311. public override bool Delete(string id)
  312. {
  313. lock (m_dbLock)
  314. {
  315. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  316. {
  317. dbcon.Open();
  318. MySqlCommand cmd = new MySqlCommand("delete from assets where id=?id", dbcon);
  319. cmd.Parameters.AddWithValue("?id", id);
  320. cmd.ExecuteNonQuery();
  321. cmd.Dispose();
  322. }
  323. }
  324. return true;
  325. }
  326. #endregion
  327. }
  328. }