1
0

MySQLAssetData.cs 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386
  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(
  128. string.Format("[ASSETS DB]: MySql failure fetching asset {0}. Exception ", assetID), e);
  129. }
  130. }
  131. }
  132. }
  133. return asset;
  134. }
  135. /// <summary>
  136. /// Create an asset in database, or update it if existing.
  137. /// </summary>
  138. /// <param name="asset">Asset UUID to create</param>
  139. /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
  140. override public void StoreAsset(AssetBase asset)
  141. {
  142. lock (m_dbLock)
  143. {
  144. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  145. {
  146. dbcon.Open();
  147. using (MySqlCommand cmd =
  148. new MySqlCommand(
  149. "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, asset_flags, CreatorID, data)" +
  150. "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?asset_flags, ?CreatorID, ?data)",
  151. dbcon))
  152. {
  153. string assetName = asset.Name;
  154. if (asset.Name.Length > 64)
  155. {
  156. assetName = asset.Name.Substring(0, 64);
  157. m_log.WarnFormat(
  158. "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
  159. asset.Name, asset.ID, asset.Name.Length, assetName.Length);
  160. }
  161. string assetDescription = asset.Description;
  162. if (asset.Description.Length > 64)
  163. {
  164. assetDescription = asset.Description.Substring(0, 64);
  165. m_log.WarnFormat(
  166. "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
  167. asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
  168. }
  169. try
  170. {
  171. using (cmd)
  172. {
  173. // create unix epoch time
  174. int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
  175. cmd.Parameters.AddWithValue("?id", asset.ID);
  176. cmd.Parameters.AddWithValue("?name", assetName);
  177. cmd.Parameters.AddWithValue("?description", assetDescription);
  178. cmd.Parameters.AddWithValue("?assetType", asset.Type);
  179. cmd.Parameters.AddWithValue("?local", asset.Local);
  180. cmd.Parameters.AddWithValue("?temporary", asset.Temporary);
  181. cmd.Parameters.AddWithValue("?create_time", now);
  182. cmd.Parameters.AddWithValue("?access_time", now);
  183. cmd.Parameters.AddWithValue("?CreatorID", asset.Metadata.CreatorID);
  184. cmd.Parameters.AddWithValue("?asset_flags", (int)asset.Flags);
  185. cmd.Parameters.AddWithValue("?data", asset.Data);
  186. cmd.ExecuteNonQuery();
  187. }
  188. }
  189. catch (Exception e)
  190. {
  191. m_log.Error(
  192. string.Format(
  193. "[ASSET DB]: MySQL failure creating asset {0} with name {1}. Exception ",
  194. asset.FullID, asset.Name)
  195. , e);
  196. }
  197. }
  198. }
  199. }
  200. }
  201. private void UpdateAccessTime(AssetBase asset)
  202. {
  203. lock (m_dbLock)
  204. {
  205. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  206. {
  207. dbcon.Open();
  208. using (MySqlCommand cmd
  209. = new MySqlCommand("update assets set access_time=?access_time where id=?id", dbcon))
  210. {
  211. try
  212. {
  213. using (cmd)
  214. {
  215. // create unix epoch time
  216. int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
  217. cmd.Parameters.AddWithValue("?id", asset.ID);
  218. cmd.Parameters.AddWithValue("?access_time", now);
  219. cmd.ExecuteNonQuery();
  220. }
  221. }
  222. catch (Exception e)
  223. {
  224. m_log.Error(
  225. string.Format(
  226. "[ASSETS DB]: Failure updating access_time for asset {0} with name {1}. Exception ",
  227. asset.FullID, asset.Name),
  228. e);
  229. }
  230. }
  231. }
  232. }
  233. }
  234. /// <summary>
  235. /// Check if the asset exists in the database
  236. /// </summary>
  237. /// <param name="uuid">The asset UUID</param>
  238. /// <returns>true if it exists, false otherwise.</returns>
  239. override public bool ExistsAsset(UUID uuid)
  240. {
  241. // m_log.DebugFormat("[ASSETS DB]: Checking for asset {0}", uuid);
  242. bool assetExists = false;
  243. lock (m_dbLock)
  244. {
  245. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  246. {
  247. dbcon.Open();
  248. using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM assets WHERE id=?id", dbcon))
  249. {
  250. cmd.Parameters.AddWithValue("?id", uuid.ToString());
  251. try
  252. {
  253. using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
  254. {
  255. if (dbReader.Read())
  256. {
  257. // m_log.DebugFormat("[ASSETS DB]: Found asset {0}", uuid);
  258. assetExists = true;
  259. }
  260. }
  261. }
  262. catch (Exception e)
  263. {
  264. m_log.Error(
  265. string.Format("[ASSETS DB]: MySql failure fetching asset {0}. Exception ", uuid), e);
  266. }
  267. }
  268. }
  269. }
  270. return assetExists;
  271. }
  272. /// <summary>
  273. /// Returns a list of AssetMetadata objects. The list is a subset of
  274. /// the entire data set offset by <paramref name="start" /> containing
  275. /// <paramref name="count" /> elements.
  276. /// </summary>
  277. /// <param name="start">The number of results to discard from the total data set.</param>
  278. /// <param name="count">The number of rows the returned list should contain.</param>
  279. /// <returns>A list of AssetMetadata objects.</returns>
  280. public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
  281. {
  282. List<AssetMetadata> retList = new List<AssetMetadata>(count);
  283. lock (m_dbLock)
  284. {
  285. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  286. {
  287. dbcon.Open();
  288. using (MySqlCommand cmd
  289. = new MySqlCommand(
  290. "SELECT name,description,assetType,temporary,id,asset_flags,CreatorID FROM assets LIMIT ?start, ?count",
  291. dbcon))
  292. {
  293. cmd.Parameters.AddWithValue("?start", start);
  294. cmd.Parameters.AddWithValue("?count", count);
  295. try
  296. {
  297. using (MySqlDataReader dbReader = cmd.ExecuteReader())
  298. {
  299. while (dbReader.Read())
  300. {
  301. AssetMetadata metadata = new AssetMetadata();
  302. metadata.Name = (string)dbReader["name"];
  303. metadata.Description = (string)dbReader["description"];
  304. metadata.Type = (sbyte)dbReader["assetType"];
  305. metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct.
  306. metadata.Flags = (AssetFlags)Convert.ToInt32(dbReader["asset_flags"]);
  307. metadata.FullID = DBGuid.FromDB(dbReader["id"]);
  308. metadata.CreatorID = dbReader["CreatorID"].ToString();
  309. // Current SHA1s are not stored/computed.
  310. metadata.SHA1 = new byte[] { };
  311. retList.Add(metadata);
  312. }
  313. }
  314. }
  315. catch (Exception e)
  316. {
  317. m_log.Error(
  318. string.Format(
  319. "[ASSETS DB]: MySql failure fetching asset set from {0}, count {1}. Exception ",
  320. start, count),
  321. e);
  322. }
  323. }
  324. }
  325. }
  326. return retList;
  327. }
  328. public override bool Delete(string id)
  329. {
  330. lock (m_dbLock)
  331. {
  332. using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
  333. {
  334. dbcon.Open();
  335. using (MySqlCommand cmd = new MySqlCommand("delete from assets where id=?id", dbcon))
  336. {
  337. cmd.Parameters.AddWithValue("?id", id);
  338. cmd.ExecuteNonQuery();
  339. }
  340. }
  341. }
  342. return true;
  343. }
  344. #endregion
  345. }
  346. }