MySQLAssetData.cs 15 KB

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